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

—>
—>Главная—> » —>Статьи—> » Уроки » Уроки Excel

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

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

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

1. Щелкните по любой ячейке таблицы.

image

2. Откройте меню «Данные» и щелкните по строке «Итоги».

image

3. В поле со списком «При каждом изменении в» щелкните по названию столбца, по которому надо считать итоги.

4. В поле со списком «Операция» щелкните по виду итогов (сумма, количество значений и т. д.).

5. В поле со списком «Добавить итоги по» щелкните по названиям столбцов, по которым надо считать итоги.

6. Щелкните по кнопке «ОК».

Обратите внимание на серую панель по левому краю экрана.

С ее помощью можно свернуть или развернуть тот или иной итог. Для этого нужно щелкнуть по «минусу», который тут же превратится в «плюс» и скроет подробности.

Таким образом вы можете сосредоточить свое внимание только на той части данных, которая сейчас наиболее значима.

Убрать итоги также просто.

Вызовите окно «Промежуточные итоги» и щелкните по кнопке «Убрать все».

Теперь ваша таблица примет первоначальный вид.

—>Категория—>: Уроки Excel | —>Добавил—>: ji9ko (20.11.2014)
—>Просмотров—>: 2529 |

—>

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

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

Кстати, чтобы эффективнее работать с таблицами можете ознакомиться с нашим материалом Горячие клавиши Excel — Самые необходимые варианты.

Содержание: 

Листы документа Excel содержат большое количество информации. Часто просматривать и искать данные неудобно.

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

С их помощью можно легко искать нужные поля и столбцы, а также скрывать информацию, которая в этот момент не нужна.

Обобщить несколько групп листа можно с помощью функции .

Рассмотрим детальнее принцип работы этой функции и её реализацию во всех версиях табличного процессора Excel.

Синтаксис функции

В программе Excel опция отображается в виде ПРОМЕЖУТОЧНЫЕ.ИТОГИ (№; ссылка 1; ссылка 2; ссылка3;…;ссылкаN), где номер – обозначение функции, ссылка – столбец, по которому подводится итог.

Поля ссылок могут включать в себя не больше 29 ссылок и интервалов, используемых при проведении промежуточных итогов.

Номером может быть любая цифра от 1 до 11. Это число показывает программе, какую функцию следует использовать для подсчета итогов внутри выбранного списка.

Синтаксис опции итогов указан в таблице:

/> />

Синтаксис

Действие

1-СРЗНАЧ

Определение среднего арифметического двух и более значений.

2-СЧЁТ

Расчет количества чисел, которые представлены в списке аргументов.

3-СЧЁТЗ

Подсчёт всех непустых аргументов.

4-МАКС

Показ максимального значения из определяемого набора чисел.

5-МИН

Показ минимального значения из определяемого набора чисел.

6-ПРОИЗВЕД

Перемножение указанных значений и возврат результата.

7-СТАНД ОТКЛОН

Анализ стандартного отклонения каждой выборки.

8-СТАД ОТКЛОН П

Анализ отклонения по общей совокупности данных.

9-СУММ

Возвращает сумму выбранных чисел.

10-ДИСП

Анализ выборочной дисперсии.

11-ДИСПР

Анализ общей дисперсии.

Выполнение промежуточных итогов включает в себя такие операции:

  • Группировка данных;
  • Создание итогов;
  • Создание уровней для групп.

Далее функция промежуточных итогов хранится в документе или же удаляется для создания новых вариантов итоговой отчетности.

Группировка таблиц

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

1 С помощью мышки выделите столбцы А, В, С, как показано на рисунке ниже. 

Рис.2 – выделение нескольких столбцов

2 Теперь, сохраняя выделение, откройте на панели инструментов программы поле «Данные». Затем в правой части окна опций найдите иконку «Структура» и нажмите на неё; 

3В выпадающем списке нажмите на «Группировать». Если вы ошиблись на этапе выделения нужных столбиков таблицы, нажмите на «Разгруппировать» и повторите операцию заново. 

Рис.3 – группировка информации

4 В новом окне выберите пункт «Строки» и нажмите на «ОК»;

Рис. 4 – группировка строк

5 Теперь снова выделите столбцы А, В, С и проведите группировку, но уже по столбцам: 

Рис.5 – создание группы столбцов

Рис.6 – панель управления группами

Создание промежуточных итогов

Опция промежуточных итогов, благодаря использованию групп и стандартных одиннадцати функции (таблица 1), позволяет быстро проводить итоги по заполненным данным таблицы.

Рассмотрим практическое использование этой функции.

Результатом выполнения действия будет появление групп, которые отсортированы по каждому размеру вещи. Следуйте инструкции:

  • Определите, для каких данных нужно проводит итоговые вычисления. В нашем случае это столбец «Размер». Его содержимое нужно отсортировать. Сортировка проводится от большего элемента к меньшему. Выделяем столбец «Размер»;

  • Далее необходимо перейди во вкладку «Данные» на панели инструментов;

  • Найдите поле «Сортировка» и нажмите на него;

  • В появившемся окне выберите пункт «Сортировать в диапазоне указных значений» и нажмите на клавишу «ОК».

Рис.7 – сортировка содержимого столбца

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

Рис.8 – результат сортировки

Теперь перейдем к реализации функции «Промежуточный итог»:

  • На панели инструментов программы откройте поле ;
  • Выберите плитку ;
  • Нажмите на ;

Рис.9 – расположение опции на панели инструментов

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

  • В поле выберите тип функции, которая применяется ко всем элементам выбранного в первом поле столбца. Для просчета размеров необходимо использовать подсчет количества;

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

  • После настройки всех параметров еще раз перепроверьте введённые значения и нажмите на ОК.

Рис.10 – обработка результата

В результате, на листе отобразится таблица в сгруппированном по размерам виде. Теперь очень легко посмотреть все заказанные вещи размера Small, Extra Large и так далее.

Рис.11 – результат выполнения функции

Как видно из рисунка выше, все итоги выводятся между показанными группами в новой строчке.

Итог для одежды с размером Small – 5 штук, для одежды с размером Extra Large – 2 штуки. Под таблицей показывается и общее количество элементов.

Уровни групп

Просматривать группы можно еще и по уровням.

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

Нажмите на первый второй или третий уровень в панели управления группами. Выберите наиболее подходящий вариант представления информации:

Рис.12 – просмотр второго уровня

Удаление итогов

Через некоторое время в таблицу будут вноситься другие данные, поэтому появится необходимость в повторной группировке информации.

Старые промежуточные итоги и тип их расчета будет неактуален, поэтому их нужно удалить.

Чтобы удалить итог, выберите вкладку ——.

В открывшемся окне нажмите на клавишу и подтвердите действие кнопкой .

Источник Рекомендовать Cодержание публикации

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

Вручную

Старейший и кропотливый метод введения порядковых номеров — это нумерация строк в Excel вручную с помощью цифр. Краткая характеристика способа — медленно, уныло и муторно. Согласитесь, даже 50 порядковых номеров вводить — скучно? Именно для облегчения работы с объёмными страницами, в Excel и были введены формулы.

Как в экселе пронумеровать строки автоматически

Вместо рутинного и грозящего ошибками метода введения порядковых номеров вручную, рекомендую к применению автоматический — проще, быстрее, надёжнее.

Заполнение первых двух строк

Простейший метод пронумеровать столбцы в Excel – это использовать заполнение первых двух строк. Для того, чтобы быстро пронумеровать список, пользователю потребуется лишь указать порядковые номера начальных двух строк. После этого нужно зажать левой кнопкой мыши строку с первой цифрой и дотянуть её до второй. Отпустите левую кнопку мышки, затем наведите курсор на правый нижний угол, там должен отобразиться маркер заполнения – чёрный квадратик в углу ячейки. Наведённый на него курсор пример вид тёмного вертикального креста. Как только вы проведёте все эти операции, нужно зажать левой кнопкой маркер заполнения одновременно с кнопкой Ctrl (маркер примет форму чёрного креста с маленьким крестиком вверху) и протянуть его по всему списку. В результате данного действия, программа Excel автоматически проставит значения по порядку значений вверх или вниз.

Конечно, способ очень простой, но рекомендуется для сравнительно коротких списков — реестры на несколько десятков значений промаркировать данным способом будет несколько проблематично.

Порядковая нумерация при помощи строки функции

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

С помощью прогрессии

Нумерация при помощи маркера заполнения

Маркер заполнения в Excel позволяет заполнять соседние ячейки на основе данных, введённых в выделенную ячейку. Выглядит такой маркер как квадратик в углу ячейки.

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

Если в процессе «протягивании» значений не удерживать нажатой кнопку Ctrl, начальное значение будет скопировано в следующие строки или столбцы.

Отображение или скрытие маркера заполнения

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

Нумерация строк с объединёнными ячейками

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

В таких случаях можно использовать функцию МАКС, которой необходимо вручную задать диапазон ячеек в Excel, например, шапка занимает две строки, тогда в третьей строке (ячейке) пишем =МАКС(А$1:А2)+1. Чтобы пронумеровать список, в котором есть объединённые ячейки, в которых нужна нумерация, необходимо выделить целевой диапазон нажатием левой кнопки мыши. Затем, в строку формул вписываем формулу, и подтверждаем её выполнение одновременным нажатием кнопок Ctrl и Enter.

Номер строки добавлением единицы

Этот способ стоит на втором месте по простоте среди всех остальных способов нумерации. Способ заключается в использовании простейшей формулы:

  • Проставляем стартовое значение в начале списка, например, единицу.
  • В следующую ячейку вбейте формулу, в которой будет указана ячейка со стартовым номером списка и к ней прибавляется единица. Формула должна иметь следующий вид =А1+1
  • Скопируйте формулу в каждую ячейку списка, используя маркер заполнения.

Нумерация с помощью команды «Заполнить»

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

  • Проставляем стартовое значение в начале списка, например, единицу.
  • Переходим в верхнем меню в пункт «Редактирование» на вкладке «Главная». В нём выбираем команду «Заполнить» и выбираем тип заполнения «Прогрессия»
  • Устанавливаем значение по столбцам или по строкам
  • Устанавливаем шаг счёта (в большинстве случаев единица)
  • Устанавливаем предельное значение, равное количеству записей в списке.

Нумерация в структурированной таблице

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

  • Выделите диапазон столбцов или строк, которые нужно пронумеровать
  • Нажмите Вставка – Таблицы – Таблица . Далее – Ок . Ваш диапазон будет организован в «Умную таблицу»
  • Запишите в первой ячейке: =СТРОКА()-СТРОКА(Таблица1[#Заголовки]) , нажмите Enter
  • Указанная формула автоматически применится ко всем ячейкам столбца

Функция «Промежуточные итоги» позволяет безошибочно пронумеровать структурированный список любого объёма. В этом случае можно не бояться за повторение порядковых номеров в списке – вычисления кладутся на «плечи» машинного ввода. Для запуска функции требуется в строке формул указать «=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;$B$2:B2)» скопировать во все ячейки списка. По результату действий пользователя реестровые значения проставятся «как надо».

В статье мы разобрали, как в 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 включает в себя промежуточные итоги по умолчанию. Это неизбежно приводит к отчету, который пугает множеством цифр, что делает его трудным.

Содержание

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

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

 ActiveSheet.PivotTables("Pvt1 ).PivotFields("Регион").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False) 
 With ActiveSheet.PivotTables("Pvt1 ).PivotFields("Регион") .Subtotals(1) = True .Subtotals(1) = False End With 

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

Код макроса

 Sub SkritPromejutochnieItogi() 'Шаг 1: Объявляем переменные Dim pt As PivotTable Dim pf As PivotField 'Шаг 2: Наведите курсор на сводную таблицу в активной ячейке On Error Resume Next Set pt = ActiveSheet.PivotTables(ActiveCell.PivotTable.Name) 'Шаг 3: Выход, если активная ячейка не в сводной таблице If pt Is Nothing Then MsgBox "Вы должны поместить курсор в сводную таблицу." Exit Sub End If 'Шаг 4: Перебрать все сводные поля и удалить итоги For Each pf In pt.PivotFields pf.Subtotals(1) = True pf.Subtotals(1) = False Next pf End Sub 

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

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

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

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

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

Оцените статью
Рейтинг автора
4,8
Материал подготовил
Максим Коновалов
Наш эксперт
Написано статей
127
А как считаете Вы?
Напишите в комментариях, что вы думаете – согласны
ли со статьей или есть что добавить?
Добавить комментарий