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

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

Поля промежуточных и общих итогов в отчете сводной таблицы

Смотрите такжеFalse, False, False,.RowGrand = False будет шесть значений. промежуточные итоги вверх. что вам большеЕсли команды Если вы хотите скопировать промежуточных итогов, которые

создайте в диапазоне напротив значения Стоимость;Примечание: столбцов определенные элементы, в СЧЕТЗ . Число С источниками данных OLAPавтоматическиеВажно: False, False, False,KuklP По умолчанию В Но вопрос в не нужны промежуточныепромежуточные итоги

В этой статье

формулу в смежную позволяют включать или

D5:D6Нажать ОК. Этот параметр доступен только

, флажок меню «Фильтр». — функция по использовать нестандартные функции

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

.Данная статья переведена False, False, False,

: Можно попробовать так: Ексель стоит автоматические другом. Как перенести итоги, вы можетенеактивна, так как ячейку строки итогов, пропускать скрытые строки,

табличку с критериями:СОВЕТ: Подсчитать промежуточные итоги в том случае,Показывать общие итоги для

Совет: умолчанию для данных,

 невозможно.Если требуется использовать другую с помощью машинного False, False, False)

Dim a(1 To итоги по строкам. два правых столбца удалить их. Для на таблицынельзя добавить перетащите ее вбок но вы также в

можно также с если источник данных строк Чтобы быстро отобразить или отличных от чисел.Для внешних заголовков строк

 функцию или отобразить перевода, см. Отказ-повторил мой код:)

 12) As Boolean,

 Эти итоги не

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

помощью Сводных таблиц OLAP не поддерживаетили оба эти скрыть текущие промежуточныеСреднее в сжатой форме более одного типа

от ответственности. Используйте

нужны. В настойках

строкам) влево, перед

область в данных,

есть быстрого действия.

заполнения. При этом

поместите заголовок столбца, и формул. синтаксис MDX. флажка. итоги, щелкните элемент

или в виде промежуточных итогов, щелкните английский вариант этой раз (по количеству

самой сводной эту месяцами?Ship, Z отвечает на вкладке

Преобразование таблицы в

ссылки на столбцыЩелкните любое место таблицы. в котором содержатсяКак видно из рисунка

К началу страницы

Сокрытие конечных итогов поля правой кнопкойMax

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

1 To 12 опцию убрать нельзя. на вопрос, вынесенныйДанные диапазон данных. Затем обновятся, и будетВыберите слова Итог, т.е. слово выше, после применения

Примечание:  и выберите вМаксимальное число. промежуточные итоги вышеи выберите функцию. здесь, в качестве

данных), то естьa(i) = False

При записи макроса в название темывыберите команду

вы можете добавить выведено правильное значение.Работа с таблицами Товар; в инструмента Промежуточные итоги,

Снимите флажок контекстном меню командуMin

 или ниже ихФункции, которые можно использовать справочного материала. нажать кнопку -

Next по их удалению и в стартовомИтоги промежуточные итоги. Не используйте копирование>D6

 MS EXCEL создалОтказ от ответственности относительноПоказывать общие итоги для

Промежуточный итог «»

Минимальное число.

элементов либо скрыть

в качестве промежуточныхПри работе с отчетом это оптимальное решение,а дальше:

получается код, где

вопросе, а Вам,, в затем вПросто помните, что преобразования и вставку, такКонструкторвведите *Итог (будут три уровня организации

промежуточные итоги следующим

сводной таблицы можно

«Subtotals = Array(первое оказывается, нужно совсем поле в диапазон убирает как при этом

и установите флажок

отобраны все строки, данных: слева от. Данная статья была, флажок

К началу страницы

Произведение чисел. образом:Функция

отображать или скрывать

коде вовсе нет!Сам не проверял True отвечающие за другое.

преимущества таблицы. Форматирование, ссылки на столбцыСтрока итогов

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

Можно отобразить или скрытьКол-во чисел

На вкладкеОписание промежуточные итоги дляKuklP — нет примера,

 автом. итоги меняетсяShipнажмите кнопку как цветной строк, не обновятся, что

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

 отдельных полей строк: А никто и но кажется сработать на False «,: Юрий М, такУдалить все останутся, но такие приведет к неверным выберите

значения, заканчивающиеся на слово

Отображение и скрытие конечных итогов для отчета целиком

1: Общий итог участия человека. Microsoftили оба эти

 отчета сводной таблицы. являются числами. Функцияв группе

Сумма чисел. Эта операция

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

 Итог) Звездочка означает (стоимость всех товаров

 предлагает эти машинные флажка.

Отображение и скрытие общих

 счёт работает так

Макет используется по умолчанию или скрывать строки что макрорекордер выдает

так по умолчанию похожие темы перплелисьBobgood будут удалены.Общие сведения о таблицах> подстановочный знак *;

в таблице); Уровень переводы, чтобы помочьК началу страницы итогов

же, как функциящелкните элемент для подведения итогов

и столбцы общих оптимальный код:-) все

: Кажется Серж более false) Писать 6

Уверен, что Z: Подскажите пожалуйста, можноЩелкните ячейку таблицы правой ExcelСтрока итоговвыделите любую ячейку таблицы; 2: Стоимость товаров пользователям, которые не

Щелкните отчет сводной таблицы.

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

ли выводить итоги

Вычисление промежуточных итогов и общих итогов с отфильтрованными элементами или без них

кнопкой мыши, наведите

Видео: создание таблицы Excel.вызовите Расширенный фильтр (Данные/ Сортировка в каждой категории; знают английского языка,На вкладкеЩелкните отчет сводной таблицы.

StDev.Число а также вычислять

оптимизировать.VDM как то некрасиво. с ним в в сводной таблице

 указатель мыши наСоздание и удаление таблицыСтрока итогов

и фильтр/ Дополнительно); Уровень 3: Все ознакомиться с материаламиПараметрыНа вкладкеНесмещенная оценка стандартного отклонения

Выполните одно из следующихЧисло значений данных. Подведения промежуточные и общие

VDM: Сергей, немного не Пробовал убирать Array двух темах общаемся. вверху данных, а пункт Excelбудет вставлена вв поле Диапазон условий строки таблицы. Нажимая о продуктах, услугахв группеКонструктор

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

 Хотя так неудобно. не под ними?ТаблицаФорматирование таблицы Excel нижней части таблицы. введите соответствующие кнопки можно и технологиях Microsoft.Сводная таблицав группе где выборка являетсяВыберите вариант же, как функция элементов при помощи красивее получилось, таквот скрин, это итогам — неМой вопрос тоже И как?, а затем выберите

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

Изменение размера таблицы путемПримечание:D5:D6 представить таблицу в Поскольку статья быланажмите кнопкуМакет

подмножеством генеральной совокупности.Не показывать промежуточные суммы СЧЕТЗ . Число

фильтра или без и оставлю! то, от чего вышло. по итогам сводной

команду добавления или удаления
Если применить в строке; нужном уровне детализации.
переведена с использованиемПараметрыщелкните стрелку рядомStDevp

. — функция по него.
Ещё раз спасибо хочу программно избавиться
В общем прошу 2010.Z
Преобразовать в диапазон

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

машинного перевода, она. с кнопкойСмещенная оценка стандартного отклоненияВыберите вариант умолчанию для данных,Поля строк и столбцов Всем!KukLP, Ваше предложение помочь оптимизировать код.Z: 1 — свойства,.Фильтрация данных в диапазоне затем отключить ее, в другое место; представлены уровни 1 может содержать лексические,синтаксическиеНа экран будет выведеноОбщие итоги генеральной совокупности по

Показывать все промежуточные итоги отличных от чисел. промежуточных итоговVDM сразу не разберу, А может вообще: 1 — Не

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

вывод — отказатьсяВ появившемся окне нажмите или таблице формулы будут сохранены.в поле Поместить результат и 2. и грамматические ошибки. диалоговое окнои выберите одну выборке данных. в нижней частиСреднееОтображение и скрытие общих: Случайно заглянул в буду пробовать. Прикрепленные можно одной строкой сталкивался с такой от классического. кнопкуПреобразование таблицы в диапазон В приведенном выше в диапазон укажитеВ таблицах в форматеПодсчитаем промежуточные итоги вПараметры сводной таблицы

из следующих команд.Var группыСреднее чисел. итогов для всего полезную литературу. Всё файлы post_188964.jpg (91.28 убрать итоги по необходимостью, 2 -2 — макетДаИспользование структурированных ссылок в примере мы применили пустую ячейку, например EXCEL 2007 Промежуточные таблице MS EXCEL..

Отключить для строк и
Несмещенная оценка дисперсии для.
Max отчета оказалось значительно проще. КБ)
всем xlRowField. не знаю как,
— в сжатой. таблицах Excel функцию СУММ дляА102

итоги работать не

Например, в таблицеЩелкните вкладку столбцов

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

Спасибо. Кусок кода 3 — как форме. После того, как выПоля промежуточных и общих строки итогов. При; будут. Нужно либо

Данные итогов в таблице Excel

содержащей сведения оИтоги и фильтрыВключить для строк и выборка является подмножествомПоказывать все промежуточные итогиMin итогов с отфильтрованными автоматических промежуточных итогов: Это промежуточные итоги привожу: вариант — обманка:3 — промитоги удалили табличные возможности итогов в отчете первом использовании строкинажмите ОК.

преобразовать таблицу в

продажах нескольких различных, а затем выполните столбцов генеральной совокупности. в заголовке группыМинимальное число. элементами или без в сводной, длинную :-)With Pt сдвинуть сводную вправо, — в заголовке.

 из своих данных, сводной таблицы итогов ячейки будут

Таблица Excel со строкой итогов

В результате получим табличку простой диапазон либо категорий товаров подсчитаем одно из следующихВключить только для строкСмещенная дисперсия.Продукт них запись типа:Дима (The_Prist) писал.PivotFields(«Naimenovanie»).Orientation = xlRowField

а слева перед ;) можно добавить промежуточныеДобавление промежуточных итогов в пустыми. содержащую только строки использовать Сводные таблицы.

Пример выбора формулы для строки итогов в раскрывающемся списке

стоимость каждой категории. действий.Включить только для столбцовСмещенная оценка дисперсии генеральнойОтобразить промежуточные итоги дляПроизведение чисел.Выберите элемент поля строки.PivotFields(«Naimenovanie»).Subtotals = Array(False, такой макрос на.PivotFields(«Naimenovanie»).Subtotals = Array(False, ней камерой вставить

-23397- итоги. сводную таблицуВыделите нужный столбец, а с итогами.Скопировать только строки с

Имеем таблицу продаж товаровДанные из источника OLAPЗадание отображения или скрытия совокупности по выборке заголовка внутренней строкиКоличество чисел или столбца в False, False, False, Планете. False, False, False, строки и итоги,ShipЩелкните одну из ячеек,Проблемы совместимости таблиц Excel затем выберите вариантСОВЕТ: Перед добавлением новых промежуточными итогами в

См. также

(товары повторяются). См. Выполните одно из

общих итогов по

данных. или столбца.

Число значений данных, которые

отчете сводной таблицы. False, False, False,Кроме того, начиная

False, False, False, 4 — сделать

содержащую данные.Экспорт таблицы Excel в

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

Файл примера. следующих действий.

Почему я не могу добавить промежуточные итоги в таблицу Excel?

 являются числами. ФункцияНа вкладке False, False, False, с 2007 Экса False, False, False, две сводные рядом. не получается. МожетеНа вкладке SharePoint В этом случае лучше удалить Промежуточные так просто: еслиПодсчитаем стоимость каждого товараУстановите или снимите флажок С источниками данных OLAPЧтобы выбрать функцию, выберите счёт работает такПараметры False, False) они легко убираются False, False)

ps Нужда заставит подсказать, что яДанныеПримечание: мы применили функцию итоги (Данные/ Структура/Промежуточные даже таблица сгруппирована на с помощью средстваПромежуточные суммы по отобраннымЩелкните отчет сводной таблицы.

использовать нестандартные функции в разделе же, как функцияв группеМожно заменить на: тремя кликами -.

— выкрутимся. ;) не так делаю?выберите командуМы стараемся как СУММ к каждому итоги кнопка Убрать 2-м уровне (см. MS EXCEL Промежуточные фильтром элементам страницы

Пункт «Таблица» в контекстном меню

На вкладке невозможно.Итоги счёт .

Диалоговое окно подтверждения

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

Активное поле.PivotFields(«Naimenovanie»).Subtotals(1) = False Конструктор — Пром..-97631-

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

, чтобы включить илиПараметрыУдаление промежуточных итоговвариантStDev

нажмите кнопкуА для избавления

итоги — не.BobgoodСпасибо.. вас актуальными справочнымиExcel создает следующую формулу:Если требуется напечатать таблицу, выделив ячейки с

Данные с добавленными промежуточными итогами

 Промежуточные итоги). исключить отфильтрованные элементыв группе другиеНесмещенная оценка стандартного отклоненияПараметры поля от всех типов показывать.End With: Да все оказалосьZВ диалоговом окне материалами на вашем=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;[Кв. 2]) так чтобы каждая итогами (на самомДля этого необходимо:

Кнопка «Удалить все» в поле «Итоги»

Итоги в сводной таблице excel 2010

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

Serge довольно просто, но: Слова «Итоги» неИтоги
языке. Эта страница. Это функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ категория товара располагалась
деле выделится диапазонубедиться, что названия столбцовПримечание:
нажмите кнопку

Нет а затем выберите где выборка являетсяНа экран будет выведено так:: Спасибо Сергей, я

 у меня сводная будет! Все -нажмите кнопку переведена автоматически, поэтому для функции СУММ, на отдельном листе,
А4:D92)

 имеют заголовки; Источник данных OLAP долженПараметрыв разделе нужную функцию. подмножеством генеральной совокупности. диалоговое окно.PivotFields(«Naimenovanie»).Subtotals(1) = True’ как-то забыл про

 же! представлена в табличный в первой строкеОК ее текст может которая является формулой используйте идеи изи скопировав егоотсортировать данные по столбцу поддерживать синтаксис MDX..Промежуточные итогиФункции, которые можно использоватьStDevpПараметры поля

 убираются все остальные вкладку «конструктор»Не это: форме, и насколько

строк и данных. . содержать неточности и со структурированными ссылками статьи Печать разных
в другой диапазон Товары, например сУстановите или снимите флажок

На экран будет выведено. в качестве промежуточныхСмещенная оценка стандартного отклонения. 11 типов итоговНо вот чтонадо? я понял в ;)Советы: грамматические ошибки. Для (такие формулы доступны
групп данных на мы получим всю
помощью Автофильтра;

Помечать итоги * диалоговое окноПримечание: итогов генеральной совокупности поВыполните одно из следующих.PivotFields(«Naimenovanie»).Subtotals(1) = False интересно: — сразуSerge такой форме итоге

-22599- нас важно, чтобы

Убрать автоматические итоги в сводной таблице в области строк (xlRowField), VBA

 только в таблицах отдельных страницах. таблицу. Чтобы скопировать

выделив любую ячейку в, чтобы отображать илиПараметры сводной таблицы Если поле содержит вычисляемыйФункция выборке данных. действий.Может кому пригодиться. записал макрос и: Вообще-то можно. ПКМ над данными расположить
ShipПосле добавления промежуточных итогов эта статья была Excel). См. статьюВы можете быстро подвести только Итоги используем таблице, вызвать Промежуточные скрывать звездочку рядом. элемент, промежуточные итогиОписаниеVarОтобразить промежуточные итоги дляKuklP что вы думаете по сводной -
нельзя..=((: Вы перенесли все слева от ваших вам полезна. Просим Использование структурированных ссылок итоги в таблице
Расширенный фильтр (будем итоги (в меню

с итогами. Звездочка

Перейдите на вкладку
статистической функции изменитьСуммаНесмещенная оценка дисперсии для внешнего поля строки: VDM, умничка. Очень
он выдал:
параметры — итоги
Serge
промежуточные итоги вверх.

 данных будет показан вас уделить пару в таблицах Excel.
Excel, включив

использовать тот факт,

 Данные/ Структура); указывает на то,Итоги и фильтры невозможно.Сумма чисел. Эта операция генеральной совокупности, где и поля столбца. правильно, что отписались

ActiveSheet.PivotTables(«Temp_Pivot»).PivotFields(«¹»).Subtotals = Array(False, и фильтры и: В рамках одной
Но вопрос в
контурный рисунок. Вы

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

в соей теме _ снимаем две галки сводной — никак.

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

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

False, False, False, — для строкVDM
два правых столбца свернуть данные, нажав вам, с помощью
функции, щелкнув из функций в Промежуточные итоги вставляет Товар; отображаются и используются действий. фильтр

 по числовым полям.Смещенная дисперсия с помощью стандартнойKetRiN2011
False, False, False, и столбцов.: Добрый ночи Уважаемые (общие итоги по одну из кнопок
кнопок внизу страницы.Другие функции
раскрывающемся списке для строки итогов св поле «Операция» выбрать при вычислении итога
Отображение конечных итогов, чтобы включить или
ЧислоСмещенная оценка дисперсии генеральной функции суммирования выберите: VDM, Спасибо, что
False, False, False,
В VBA за Форумчане! строкам) влево, перед с числами в Для удобства такжеили создав их каждого столбца. По добавлением слова Итог

 Сумма; приложением Excel, не исключить новые элементыЧисло значений данных. Подведения совокупности по выборке в разделе

 написал самое кратчайшее False, False) это отвечают этиТворю код для

месяцами? верхней части рисунка.

 приводим ссылку на самостоятельно. умолчанию в строке или в английскойв поле «Добавить итоги являются единственными используемымиУстановите флажок при применении фильтра,
итогов работает так данных.Итоги решение. Мне этотActiveSheet.PivotTables(«Temp_Pivot»).PivotFields(«Êòî»).Subtotals = Array(

строки:
формирования сводной. В

Юрий МСовет: оригинал (на английскомПримечание:
итогов применяются функции версии — Total). по» поставить галочку
в вычислении значениями.

Показывать общие итоги для

 в котором выбраны же, как функцияПримечание:вариант код очень пригодился!

 _.ColumnGrand = False области строк (xlRowField): Вы перенесли все Если вы решите,

Промежуточные итоги в Excel с примерами функций

Подвести промежуточные итоги в таблице Excel можно с помощью встроенных формул и соответствующей команды в группе «Структура» на вкладке «Данные».

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

Вычисление промежуточных итогов в Excel

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

Список с продажами.

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

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

Таблица оформлена в виде простого списка или базы данных.
Первая строка – названия столбцов.
В столбцах содержатся однотипные значения.
В таблице нет пустых строк или столбцов.

Отсортируем диапазон по значению первого столбца – однотипные данные должны оказаться рядом.
Выделяем любую ячейку в таблице. Выбираем на ленте вкладку «Данные». Группа «Структура» — команда «Промежуточные итоги».
Заполняем диалоговое окно «Промежуточные итоги». В поле «При каждом изменении в» выбираем условие для отбора данных (в примере – «Значение»). В поле «Операция» назначаем функцию («Сумма»). В поле «Добавить по» следует пометить столбцы, к значениям которых применится функция.
Закрываем диалоговое окно, нажав кнопку ОК. Исходная таблица приобретает следующий вид:

Если свернуть строки в подгруппах (нажать на «минусы» слева от номеров строк), то получим таблицу только из промежуточных итогов:

Таблица.

При каждом изменении столбца «Название» пересчитывается промежуточный итог в столбце «Продажи».

Продажи.

Чтобы за каждым промежуточным итогом следовал разрыв страницы, в диалоговом окне поставьте галочку «Конец страницы между группами».

Конец.

Чтобы промежуточные данные отображались НАД группой, снимите условие «Итоги под данными».

Под данными.

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

Среднее.

Снова вызываем меню «Промежуточные итоги». Снимаем галочку «Заменить текущие». В поле «Операция» выбираем «Среднее».

Формула «Промежуточные итоги» в Excel: примеры

Функция «ПРОМЕЖУТОЧНЫЕ.ИТОГИ» возвращает промежуточный итог в список или базу данных. Синтаксис: номер функции, ссылка 1; ссылка 2;… .

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

– СРЗНАЧ (среднее арифметическое);
– СЧЕТ (количество ячеек);
– СЧЕТЗ (количество непустых ячеек);
– МАКС (максимальное значение в диапазоне);
– МИН (минимальное значение);
– ПРОИЗВЕД (произведение чисел);
– СТАНДОТКЛОН (стандартное отклонение по выборке);
– СТАНДОТКЛОНП (стандартное отклонение по генеральной совокупности);
– СУММ;
– ДИСП (дисперсия по выборке);
– ДИСПР (дисперсия по генеральной совокупности).

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

Особенности «работы» функции:

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

Рассмотрим на примере использование функции:

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

Формула для среднего значения промежуточного итога диапазона (для прихожей «Ретро»): .

Формула среднее.

Формула для максимального значения (для спален): .

Формула максимальное.

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

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

При формировании сводного отчета уже заложена автоматическая функция суммирования для расчета итогов.
Чтобы применить другую функцию, в разделе «Работа со сводными таблицами» на вкладке «Параметры» находим группу «Активное поле». Курсор должен стоять в ячейке того столбца, к значениям которого будет применяться функция. Нажимаем кнопку «Параметры поля». В открывшемся меню выбираем «другие». Назначаем нужную функцию для промежуточных итогов.
Для выведения на экран итогов по отдельным значениям используйте кнопку фильтра в правом углу названия столбца.

В меню «Параметры сводной таблицы» («Параметры» — «Сводная таблица») доступна вкладка «Итоги и фильтры».

Параметры сводной таблицы.

Таким образом, для отображения промежуточных итогов в списках Excel применяется три способа: команда группы «Структура», встроенная функция и сводная таблица.

Сводные таблицы в Excel

Сводные таблицы ( Вставка/ Таблицы/ Сводная таблица ) могут пригодиться, если одновременно выполняются следующие условия:

имеется исходная таблица с множеством строк (записей), речь идет о нескольких десятках и сотнях строк;
необходимо провести анализ данных, который требует выборки (фильтрации) данных, их группировки (суммирования, подсчета) и представления данных в различных разрезах (подготовки отчетов);
этот анализ затруднительно провести на основе исходной таблицы с использованием других стредств: фильтра ( CTRL+SHIFT+L ), формул , Расширенного фильтра ;
исходная таблица удовлетворяет определенным требованиям (см. ниже).

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

Подготовка исходной таблицы

Начнем с требований к исходной таблице.

каждый столбец должен иметь заголовок;
в каждый столбец должны вводиться значения только в одном формате (например, столбец «Дата поставки» должен содержать все значения только в формате Дата ; столбец «Поставщик» — названия компаний только в текстовом формате или можно вводить Код поставщика в числовом формате);
в таблице должны отсутствовать полностью незаполненные строки и столбцы;
в ячейки должны вводиться «атомарные» значения, т.е. только те, которые нельзя разнести в разные столбцы. Например, нельзя в одну ячейку вводить адрес в формате: «Город, Название улицы, дом №». Нужно создать 3 одноименных столбца, иначе Сводная таблица будет работать неэффективно (в случае, если Вам нужна информация, например, в разрезе города);
избегайте таблиц с «неправильной» структурой (см. рисунок ниже).

Вместо того, чтобы плодить повторяющиеся столбцы ( регион 1, регион 2, … ), в которых будут в изобилии незаполненные ячейки, переосмыслите структуру таблицы, как показано на рисунке выше (Все значения объемов продаж должны быть в одном столбце, а не размазаны по нескольким столбцам. Для того, чтобы это реализовать, возможно, потребуется вести более подробные записи (см. рисунок выше), а не указывать для каждого региона суммарные продажи).

Более детальные советы по построению таблиц изложены в одноименной статье Советы по построению таблиц .

Несколько облегчит процесс построения Сводной таблицы , тот факт, если исходная таблица будет преобразована в формат EXCEL 2007 ( Вставка/ Таблицы/ Таблица ). Для этого сначала приведите исходную таблицу в соответствие с вышеуказанными требованиями, затем выделите любую ячейку таблицы и вызовите окно меню Вставка/ Таблицы/ Таблица . Все поля окна будут автоматически заполнены, нажмите ОК.

Создание таблицы в формате EXCEL 2007 добавляет новые возможности:

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

В качестве исходной будем использовать таблицу в формате EXCEL 2007 содержащую информацию о продажах партий продуктов. В строках таблицы приведены данные о поставке партии продукта и его сбыте.

В таблице имеются столбцы:

Товар – наименование партии товара, например, « Апельсины »;
Группа – группа товара, например, « Апельсины » входят в группу « Фрукты »;
Поставщик – компания-поставщик Товаров, Поставщик может поставлять несколько Групп Товаров;
Дата поставки – Дата поставки Товара Поставщиком;
Регион продажи – Регион, в котором была реализована партия Товара;
Продажи – Стоимость, по которой удалось реализовать партию Товара;
Сбыт – срок фактической реализации Товара в Регионе (в днях);
Прибыль – отметка о том, была ли получена прибыль от реализованной партии Товара.

Через Диспетчер имен ( Формулы/ Определенные имена/ Диспетчер имен ) откорректируем Имя таблицы на « Исходная_таблица ».

Создание Сводной таблицы

Сводную таблицу будем создавать для решения следующей задачи: «Подсчитать суммарные объемы продаж по каждому Товару».

Имея исходную таблицу в формате EXCEL 2007 , для создания Сводной таблицы достаточно выделить любую ячейку исходной таблицы и в меню Работа с таблицами/ Конструктор/ Сервис выбрать пункт Сводная таблица .

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

На отдельном листе появится заготовка Сводной таблицы и Список полей, размещенный справа от листа (отображается только когда активная ячейка находится в диапазоне ячеек Сводной таблицы).

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

Заполним сначала раздел Названия строк . Т.к. требуется определить объемы продаж по каждому Товару, то в строках Сводной таблицы должны быть размещены названия Товаров. Для этого поставим галочку в Списке полей у поля Товар (поле и столбец — синонимы).

Т.к. ячейки столбца Товар имеют текстовый формат, то они автоматически попадут в область Названия строк Списка полей. Разумеется, поле Товар можно при необходимости переместить в другую область Списка полей. Заметьте, что названия Товаров будут автоматически отсортированы от А до Я (об изменении порядка сортировки читайте ниже ).

Теперь поставим галочку в Списке полей у поля Продажи.

Т.к. ячейки столбца Продажи имеют числовой формат, то они автоматически попадут в раздел Списка полей Значения.

Несколькими кликами мыши (точнее шестью) мы создали отчет о Продажах по каждому Товару. Того же результата можно было достичь с использованием формул (см. статью Отбор уникальных значений с суммированием по соседнему столбцу ). Если требуется, например, определить объемы продаж по каждому Поставщику, то для этого снимем галочку в Списке полей у поля Товар и поставим галочку у поля Поставщик.

Детализация данных Сводной таблицы

Если возникли вопросы о том, какие же данные из исходной таблицы были использованы для подсчета тех или иных значений Сводной таблицы , то достаточно двойного клика мышкой на конкретном значении в Сводной таблице , чтобы был создан отдельный лист с отобранными из исходной таблицей строками. Например, посмотрим какие записи были использованы для суммирования продаж Товара «Апельсины». Для этого дважды кликнем на значении 646720. Будет создан отдельный лист только со строками исходной таблицы относящихся к Товару «Апельсины».

Обновление Сводной таблицы

Если после создания Сводной таблицы в исходную таблицу добавлялись новые записи (строки), то эти данные не будут автоматически учтены в Сводной таблице . Чтобы обновить Сводную таблицу выделите любую ее ячейку и выберите пункт меню: меню Работа со сводными таблицами/ Параметры/ Данные/ Обновить . Того же результата можно добиться через контекстное меню: выделите любую ячейку Сводной таблицы , вызовите правой клавишей мыши контекстное меню и выберите пункт Обновить .

Удаление Сводной таблицы

Удалить Сводную таблицу можно несколькими способами. Первый – просто удалить лист со Сводной таблицей (если на нем нет других полезных данных, например исходной таблицы). Второй способ — удалить только саму Сводную таблицу : выделите любую ячейку Сводной таблицы , нажмите CTRL + A (будет выделена вся Сводная таблица ), нажмите клавишу Delete .

Изменение функции итогов

При создании Сводной таблицы сгруппированные значения по умолчанию суммируются. Действительно, при решении задачи нахождения объемов продаж по каждому Товару, мы не заботились о функции итогов – все Продажи, относящиеся к одному Товару были просуммированы. Если требуется, например, подсчитать количество проданных партий каждого Товара, то нужно изменить функцию итогов. Для этого в Сводной таблице выделите любое значение поля Продажи, вызовите правой клавишей мыши контекстное меню и выберите пункт Итоги по/ Количество .

Изменение порядка сортировки

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

Теперь предположим, что Товар Баранки – наиболее важный товар, поэтому его нужно выводить в первой строке. Для этого выделите ячейку со значением Баранки и установите курсор на границу ячейки (курсор должен принять вид креста со стрелками).

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

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

Изменение формата числовых значений

Теперь добавим разделитель групп разрядов у числовых значений (поле Продажи). Для этого выделите любое значение в поле Продажи, вызовите правой клавишей мыши контекстное меню и выберите пункт меню Числовой формат …

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

Добавление новых полей

Предположим, что необходимо подготовить отчет о продажах Товаров, но с разбивкой по Регионам продажи. Для этого добавим поле Регион продажи, поставив соответствующую галочку в Списке полей. Поле Регион продажи будет добавлено в область Названия строк Списка полей (к полю Товар). Поменяв в области Названия строк Списка полей порядок следования полей Товар и Регион продажи, получим следующий результат.

Выделив любое название Товара и нажав пункт меню Работа со сводными таблицами/ Параметры/ Активное поле/ Свернуть все поле , можно свернуть Сводную таблицу , чтобы отобразить только продажи по Регионам.

Добавление столбцов

Добавление поля Регион продажи в область строк привело к тому, что Сводная таблица развернулась на 144 строки. Это не всегда удобно. Т.к. продажи осуществлялись только в 6 регионах, то поле Регион продажи имеет смысл разместить в области столбцов.

Сводная таблица примет следующий вид.

Меняем столбцы местами

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

Удаление полей

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

Другой способ – снять галочку напротив удаляемого поля в верхней части Списка полей. Но, в этом случае поле будет удалено сразу из всех областей Списка полей (если оно использовалось в нескольких областях).

Добавление фильтра

Предположим, что необходимо подготовить отчет о продажах Групп Товаров, причем его нужно сделать в 2-х вариантах: один для партий Товаров принесших прибыль, другой – для убыточных. Для этого:

Очистим ранее созданный отчет: выделите любое значение Сводной таблицы, нажмите пункт меню Работа со сводными таблицами/ Параметры/ Действия/ Очистить/ Очистить все ;
Ставим галочки в Списке полей у полей Группа, Продажи и Прибыль;
Переносим поле Прибыль из области Названия строк Списка полей в область Фильтр отчета;

Вид получившейся Сводной таблицы должен быть таким:

Теперь воспользовавшись Выпадающим (раскрывающимся) списком в ячейке B1 (поле Прибыль) можно, например, построить отчет о продажах Групп Товаров, принесших прибыль.

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

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

Очистить фильтр можно через меню Работа со сводными таблицами/ Параметры/ Действия/ Очистить/ Очистить фильтры .

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

Несколько итогов для одного поля

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

Очистим ранее созданный отчет: выделите любое значение Сводной таблицы , нажмите пункт меню Работа со сводными таблицами/ Параметры/ Действия/ Очистить/ Очистить все ;
Поставьте галочки напротив полей Товар и Продажи в верхней части Списка полей. Поле Продажи будет автоматически помещено в область Значения;
Перетащите мышкой еще одну копию поля Продажи в ту же область Значения. В Сводной таблице появится 2 столбца подсчитывающими суммы продаж;

в Сводной таблице выделите любое значение поля Продажи, вызовите правой клавишей мыши контекстное меню и выберите пункт Итоги по/ Количество . Задача решена.

Отключаем строки итогов

Строку итогов можно отключить через меню: Работа со сводными таблицами/ Конструктор/ Макет/ Общие итоги . Не забудьте предварительно выделить любую ячейку Сводной таблицы .

Группируем числа и Даты

Предположим, что требуется подготовить отчет о сроках сбыта. В результате нужно получить следующую информацию: сколько партий Товара сбывалось в период от 1 до 10 дней, в период 11-20 дней и т.д. Для этого:

Очистим ранее созданный отчет: выделите любое значение Сводной таблицы , нажмите пункт меню Работа со сводными таблицами/ Параметры/ Действия/ Очистить/ Очистить все ;
Поставьте галочку напротив поля Сбыт (срок фактической реализации Товара) в верхней части Списка полей. Поле Сбыт будет автоматически помещено в область Значения;
выделите единственное значение поля Сбыт в Сводной таблице , вызовите правой клавишей мыши контекстное меню и выберите пункт Итоги по/ Количество .
Перетащите мышкой еще одну копию поля Сбыт в область Названия строк;

Теперь Сводная таблица показывает сколько партий Товара сбывалось за 5, 6, 7, … дней. Всего 66 строк. Сгруппируем значения с шагом 10. Для этого:

Выделите одно значение Сводной таблицы в столбце Названия строк;
В меню Работа со сводными таблицами/ Параметры/ Группировать выберите пункт Группировка по полю ;
Появившееся окно заполните, как показано на рисунке ниже;

Нажмите ОК.

Теперь Сводная таблица показывает сколько партий Товара сбывалось в период от 1 до 10 дней, в период 11-20 дней и т.д.

Чтобы разгруппировать значения выберите пункт Разгруппировать в меню Работа со сводными таблицами/ Параметры/ Группировать .

Аналогичную группировку можно провести по полю Дата поставки. В этом случае окно Группировка по полю будет выглядеть так:

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

Условное форматирование ячеек Сводной таблицы

К ячейкам Сводной таблицы можно применить правила Условного форматирования как и к ячейкам обычного диапазона. Выделим, например, ячейки с 10 наибольшими объемами продаж. Для этого:

7 интересных приёмов при работе со сводной таблицей ⁠ ⁠

Друзья, всем привет. Сегодня хочу рассказать вам про несколько полезных и интересных (с моей скромной и субъективной точки зрения) трюков при работе со сводными таблицами. Здесь не будет подробного разбора про работу в сводных таблицах: что это, для чего и почему. Материал предназначен для тех, кто уже хоть как-то знаком с этим прекрасным инструментом и знает, что это за зверь такой. Если же вы ещё не работали со сводными таблицами, при этом проводите много времени в Excel и строите разного рода отчёты, аналитику, то я вам настоятельно рекомендую как можно скорее освоить этот поистине чудесный инструмент. Поверьте, вы откроете для себя абсолютно новый мир 🙂

1 — Фильтры для полей сводной, которые по умолчанию фильтровать нельзя.

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