Меню

Как скрыть все промежуточные итоги в сводной таблице в Excel

Как скрыть все промежуточные итоги в сводной таблице в Excel

Что делает макрос: Создание сводной таблицы Excel включает в себя промежуточные итоги по умолчанию. Это неизбежно приводит к отчету, который пугает множеством цифр, что делает его трудным.

Вы можете вручную скрыть все промежуточные итоги сразу перейдя в ленте и выбрав Работа со Сводными Таблицами➜Дизайн➜Макет➜Промежуточные➜Не Показывать
Промежуточные Итоги. Но опять же, если вы строите автоматизированный процесс, который постоянно манипулирует сводными таблицами без ручного вмешательства, вы можете
предпочесть вариант макроса.

  1. Как макрос работает
  2. Код макроса
  3. Как этот код работает
  4. Как использовать

Как макрос работает

Если вы записываете макрос, в то время как скрываете промежуточные итоги в сводной таблице, Excel производит код, похожий на этот:

Excel передает массив с 12 ложными параметрами. Есть 12 экземпляров false, потому что есть двенадцать видов промежуточных итогов — сумма, среднее, количество, минимум и максимум и т.д. Итак, когда вы выключите промежуточные итоги во время записи макроса в Excel наборы всех возможных видов значение false.
Альтернативный способ отключить «итоги» — это первый комплект из 12 промежуточных итогов в True. Это автоматически заставляет других 11 видов в false. Мы после этого устанавливаем так же Итого в ложь, скрывая все промежуточные итоги. В этот кусок кода, мы создаем первый промежуточный итог True, а затем установить его в false. Это удаляет промежуточный итог для области.

В этом макросе мы используем этот трюк, чтобы выключить промежуточные итоги для каждого поля сводной таблицы в активной сводной таблице.

Код макроса

Как этот код работает

  1. Шаг 1 объявляет две переменные объекта. Этот макрос использует РТ в качестве контейнера памяти для сводной таблицы и использует PF в качестве контейнера памяти для полей. Это позволяет перебрать все сводные поля в сводной таблице. Этот макрос разработан таким образом, что мы выводим активную сводную таблицу на основе активной ячейки. То есть, активная ячейка должна быть внутри сводной таблицы для запуска этого макроса. Когда курсор находится внутри определенной сводной таблицы, мы хотим, выполнить действие макроса на этой оси.
  2. Шаг 2 устанавливает переменную СТ к имени сводной таблицы на которой найдена активная ячейка. Мы делаем это, используя свойство ActiveCell.PivotTable.Name, чтобы получить имя целевого диапазона.
    Если активная ячейка не находится внутри сводной таблицы, выдается ошибка. Именно поэтому макрос использует On Error Resume Next Statement. Это говорит Excel продолжить макро, если есть ошибка.
  3. Шаг 3 проверяет в переменной РТ заполнены ли сводные таблицы объекта. Если переменная PT установлена в настоящее время, активная ячейка была не на сводной таблице, таким образом, сводной таблице не может быть присвоена переменная. Если это так, то мы говорим пользователю в окне сообщения, а затем выходим из процедуры.
  4. Если макрос достигает Шаг 4, он успешно указал на сводную таблицу. Мы готовы к циклу для всех полей в сводной таблице. Оператор For Each перебирает каждое поле сводной.
    Каждый раз, когда выбрано новое поле сводной, мы применяем нашу логику Итого. После того, как все поля были оценены, макрос заканчивается.

Как использовать

Для реализации этого макроса, вы можете скопировать и вставить его в стандартный модуль:

  1. Активируйте редактор Visual Basic, нажав ALT + F11.
  2. Щелкните правой кнопкой мыши имя проекта / рабочей книги в окне проекта.
  3. Выберите Insert➜Module.
  4. Введите или вставьте код.

Источник

Как в офисе.

Промежуточные итоги в Excel

Раздел «Структура», помимо группировки и разгруппировки данных, имеет еще одну функцию — «Промежуточные итоги». С ее помощью Вы можете задать автоматическую группировку для данных с подведенным по ним итогом. Чтобы стало понятнее, о чем идет речь, рассмотрим в виде примера таблицу по сотрудникам с результатами их продаж за квартал.

Читайте также:  Когда школьники учат таблицу умножения

Результаты продаж сотрудников

Выделив таблицу и нажав на значок «Промежуточные итоги», Excel выдаст окно:

Меню промежуточных итогов

В поле «При каждом изменении в:» необходимо указать заголовок столбца таблицы по которому будет определяться подведение итогов. В качестве заголовка выступают ячейки верхних строк выделенной таблицы. Надписи в ячейках, указанного столбца, будут служить заголовками строк. Программа будет проверять заголовки на сходство. Если заголовки строк совпадают, то она добавляет его в подсчет итога, если не совпадают, то дополнительной строкой выводится результат по группе, определенных одним заголовком. Поэтому рекомендуем произвести заранее сортировку по данному столбцу.

Во втором поле «Операция:» выбирается какой итог необходимо подвести: Сумма, количество, среднее и т.д.. В последнем поле «Добавить итог по:» выбирается столбец, который содержит необходимые данные для вывода результата.

Ниже располагается 3 чекбокса (флага):

  • Заменить текущие итоги — если установлен, то итоги, подведенные ранее, будут заменены, если нет, то к старым результатам добавятся новые;
  • Конец страницы между группами — если установлен, то при выводе на печать каждая отдельная группа с итогом будет располагаться на новом листе;
  • Итоги под данными — если установлен, то результат будет располагаться в конце группы, если нет, то в начале.

Кнопка «Убрать все» удаляет всю структуру данных.

Так преобразовалась таблица, после применения к ней описываемой функции:

Промежуточные итоги продаж сотрудников

Добавленные новые ячейки содержат в себе математическую функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ(), ознакомиться с которой можно в статье про математические функции — Математические функции Excel.

Механизм подведения промежуточных итогов имеет общие черты с другим более мощным инструментом приложения Excel — сводные таблицы.

Если материалы office-menu.ru Вам помогли, то поддержите, пожалуйста, проект, чтобы мы могли развивать его дальше.

Источник



Как удалить промежуточные итоги из Excel

Microsoft Excel позволяет творить чудеса с вашими электронными таблицами. Это особенно актуально при работе с многолетними финансовыми расчетами. Чтобы организовать беспорядок в строках, столбцах и тысячах ячеек, вы можете использовать параметры «Outline» в Excel. Это позволяет создавать логические группы данных, а также соответствующие промежуточные итоги и итоги.

Конечно, в какой-то момент может оказаться, что подавляющее число этих промежуточных строк разрушает ваш финансовый анализ. Но удаление их вручную утомительно и может нарушить целостность таблицы. Не беспокойтесь, у Excel также есть решение для этого.

Удаление промежуточных итогов из стандартных таблиц

Если вы просто хотите удалить строки промежуточных итогов из вашей электронной таблицы или реорганизовать весь лист, процесс в основном одинаков. Для этого выполните следующие действия:

  1. Откройте таблицу Excel, которую вы хотите редактировать.
  2. Нажмите на вкладку «Данные».
  3. В разделе «Outline» верхнего меню нажмите «Subtotal».
  4. В меню «Итого» нажмите кнопку «Удалить все».
  5. Это разгруппирует все данные в электронной таблице, фактически удалив все строки промежуточных итогов, которые у вас могут быть.

Если вы не хотите удалять промежуточные итоги, но хотите удалить все группы для дальнейшей перестройки ваших данных, сделайте следующее:

  1. Нажмите на вкладку «Данные».
  2. В разделе «Контур» щелкните раскрывающееся меню «Разгруппировать».
  3. Теперь нажмите «Очистить контур», чтобы удалить все группировки для этой таблицы.

Удаление промежуточных итогов из сводных таблиц

Если вы работаете с сводными таблицами в Excel, вам не нужно создавать новую таблицу только для удаления промежуточных итогов. Вы можете сделать это, не удаляя всю сводную таблицу.

  1. Откройте файл Excel, содержащий вашу сводную таблицу.
  2. Теперь выберите ячейку в любой из строк или столбцов таблицы.
  3. Это позволит вам получить доступ к меню «Инструменты сводной таблицы». Он появится в верхнем меню рядом с вкладкой «Вид» и будет содержать две вкладки: «Анализ» и «Дизайн». В более старых версиях Excel (Excel 2013 и более ранних версиях) вы увидите вкладки «Параметры» и «Дизайн» без заголовка «Инструменты сводной таблицы» над ними.
  4. Нажмите на вкладку «Анализ». Для более старых версий Excel щелкните вкладку «Параметры».
  5. В разделе «Активное поле» нажмите «Настройки поля».
  6. В меню «Настройки поля» перейдите на вкладку «Промежуточные итоги и фильтры».
  7. В разделе «Промежуточные итоги» выберите «Нет».
  8. Теперь нажмите «ОК», чтобы подтвердить изменения.
Читайте также:  РАЗМЕРЫ ЭКРАНОВ И ТЕЛЕВИЗОРОВ в дюймах и сантиметрах

Обратите внимание, что если в вашей сводной таблице есть поле, содержащее некоторые вычисления, вы не сможете удалить промежуточные итоги.

Добавление промежуточных итогов в вашу таблицу

Надеюсь, вам удалось удалить промежуточные итоги, используя рекомендации из предыдущих двух разделов. Когда вы закончите реорганизацию данных в вашей электронной таблице, вы можете захотеть добавить новый набор промежуточных итогов. Для этого выполните следующие действия:

  1. Откройте электронную таблицу, в которую вы хотите добавить промежуточные итоги.
  2. Сортируйте весь лист по столбцу, который содержит данные, которые вы хотите иметь промежуточные итоги.
  3. Теперь нажмите вкладку «Данные» в верхнем меню.
  4. В разделе «Outline» нажмите «Subtotal», чтобы открыть соответствующее меню.
  5. В раскрывающемся меню «При каждом изменении» выберите столбец, содержащий данные, которые вы хотите использовать для промежуточных итогов.
  6. Затем вы должны выбрать, какую операцию должны рассчитывать промежуточные итоги. В раскрывающемся меню «Использовать функцию» выберите один из доступных вариантов. Некоторые из наиболее распространенных операций — это Sum, Count и Average.
  7. В поле «Добавить промежуточные итоги» выберите столбец, в котором вы хотите отображать промежуточные итоги. Обычно это тот же столбец, для которого вы выполняете промежуточные итоги, как определено в шаге 5.
  8. Вы можете оставить флажки «Заменить текущие промежуточные итоги» и «Сводка данных ниже».
  9. Если вы удовлетворены своим выбором, нажмите «ОК», чтобы подтвердить изменения.

После того, как вы нажмете ОК, вы увидите, что строки в вашей электронной таблице теперь сгруппированы в группы. Ниже каждой группы появится новая строка, содержащая промежуточный итог для этой группы.

Использование уровней для организации ваших данных

При добавлении промежуточных итогов вы увидите новый столбец между номерами строк и левым краем окна Excel. В верхней части этого столбца находятся номера один, два и три. Они представляют уровни группировки для ваших данных.

Когда вы нажмете номер три, вы сможете увидеть весь свой набор данных, показывая каждую строку, строки промежуточных итогов и общую итоговую строку. Если вы нажмете номер два, данные будут свернуты, чтобы показать вам только промежуточные итоги и итоговые строки. Нажмите на номер один, и вы увидите только итоговую строку.

Когда вы раскроете все, щелкнув цифру три, у вас появятся знаки «-» и «+» в столбце уровней. Используйте их, чтобы вручную свернуть и развернуть каждую группу самостоятельно.

Благодаря этой функции Excel позволяет скрыть от просмотра определенные части вашей электронной таблицы. Это полезно, когда есть некоторые данные, которые не важны для вашей работы в данный момент. Конечно, вы всегда можете вспомнить его, нажав знак «+».

Промежуточные итоги удалены!

Благодаря мощным инструментам Excel вы теперь знаете, как избежать ненужной ручной работы при удалении промежуточных итогов. После перегруппировки всех данных достаточно просто щелкнуть несколько параметров Excel, чтобы добавить эти промежуточные итоги обратно. Кроме того, не забудьте использовать уровни, чтобы оптимизировать способ просмотра ваших данных.

Вам удалось удалить строки промежуточных итогов из ваших таблиц? Считаете ли вы это лучшим вариантом, чем ручное удаление? Пожалуйста, поделитесь своими мыслями в разделе комментариев ниже.

Источник

Промежуточные итоги в Excel

Promegutocnie itogi 1 Промежуточные итоги в Excel Доброго времени суток друзья!

Эту статью я посвящаю такой возможности, как создать промежуточные итоги в Excel. Если вы даже слышали или знаете, что это такое, всё же будет полезно прочитать или послушать мою информацию. По большому счёту, промежуточные итоги – это автоматическое подведение итогов по заданному критерию и группирование их для удобства и хорошей визуализации данных.

Читайте также:  Что такое наречие разряды степени сравнения и примеры наречий

Это достаточно полезный и удобный инструмент работы в электронных таблицах, который вам пригодится, а для большего удобства я советую использовать эти итоги в тандеме с группированием ячеек, это вообще супер сила. Также я прошу обратить ваше внимание, что перед работой с итогами, необходимо в обязательном порядке произвести правильную сортировку ваших данных.

Итак, давайте же более детально рассмотрим, что это за итоги, как их создать, как с ними работать, ну и собственно, как их убрать, если нужда в этой возможности отпадёт. И начнём все наши исследования сначала, а точнее с того, как можно создать промежуточный итог.

Как сделать промежуточные итоги в Excel

А теперь давайте на созданном примере посмотрим поэтапно, как это осуществить:

  1. Первым делом нам нужно правильно отсортировать наши данные, для которых мы будем подводить итог. Мы выбираем нужные нам данные, по которым будем создавать промежуточный итог и предварительно выделив всю таблицу, производим сортировку по указанному столбику от меньшего к большему. Promegutocnie itogi 2 Промежуточные итоги в Excel
  2. Вторым этапом будет выбор на вкладке «Данные», в группе «Структура», команду «Промежуточный итог». Promegutocnie itogi 3 Промежуточные итоги в Excel
  3. В диалоговом окне «Промежуточные итоги», во-первых, вам нужно в списке поля «При каждом изменении в» выбрать столбик, для которого создаем промежуточные итоги, во-вторых, в списке поля «Операция» мы выбираем какой тип функции нам нужен (количество, сумма и т.п.), в-третьих, в поле «Добавить итоги» вы определяете столбик по которому будет формироваться итог. После этого вы с чистой совестью вы нажимаете кнопочку «ОК». Promegutocnie itogi 4 Промежуточные итоги в Excel
  4. Данные на вашем листе автоматически будут сгруппированы по признаку, который вы установили, и под каждой группой будет создан промежуточный итог.Promegutocnie itogi 5 Промежуточные итоги в Excel

Работа со сгруппированными данными

После проведенных манипуляций, работа с данными значительно упростится. Данные можно удобно визуализировать, используя группировку данных и создавать столько уровней, сколько вам будет удобно, но, увы, не более 8 вложенных, с опыта могу сказать, что этого хватит за глаза.

Нажимая на иконки группирования данных, вы скрываете и отображаете ту структуру данных, которая вам наиболее актуальна на данный момент. Promegutocnie itogi 6 Промежуточные итоги в Excel К примеру, можно отобразить только общий итог, отобразить итоги по группам данных и полный разворот данных в деталях. Promegutocnie itogi 7 Промежуточные итоги в Excel Также, всё это можно проделать, используя пиктограммы «Скрыть детали» или «Показать». Promegutocnie itogi 8 Промежуточные итоги в Excel

Убрать промежуточные итоги

В случае, когда промежуточные итоги в Excel станут, не нужны, ну или потребуется перегруппировка по-новому ваших данных в таблице, в этом случае итоги можно удалить без потери качества исходных данных. Для этого нужно:

  1. Открыть вкладку на панели управления «Данные» и в блоке «Структура» нажимаете на пиктограмму «Промежуточный итог».
  2. В появившемся диалоговом окне «Промежуточные итоги» нажимаем кнопку «Убрать всё».
  3. После нажатия все ваши данные разгруппируются, а промежуточные итоги будут удалены.

Promegutocnie itogi 9 Промежуточные итоги в Excel

Если же вам всё-таки промежуточные итоги нужно оставить, а вот группирование не нужно, вы можете в панели задач на вкладке на вкладке «Данные» в выпадающем меню кнопки «Разгруппировать» выбрать пункт «Удалить структуру», структура группировки будет удалена, оставив вам на прощание промежуточные итоги.

Promegutocnie itogi 10 Промежуточные итоги в Excel

А на этом у меня всё! Я очень надеюсь, что всё вышеизложенное вам понятно. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями прочитанным и ставьте лайк!

Не забудьте поблагодарить автора!

Пока богатство еще не приобретено, стремление к нему изнуряет, будучи же приобретено, оно изводит заботами, когда же оно утрачено, мучает тоска по нем. Демокрит

Источник