Работа со сводными таблицами в Microsoft Excel

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

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

Немного истории

• Во-первых, компания Lotus решила, что эта новомодная GUI-платформа с названием Windows — это всего лишь мимолётное увлечение, которое долго не протянет. Они отказались создавать версию Lotus 1-2-3 для Windows (впрочем, только несколько лет), предсказывая, что DOS-версия их программного обеспечения — это всё, что когда-либо будет нужно потребителям. Microsoft, естественно, разработала Excel специально под Windows.
• Во-вторых, Microsoft разработала в Excel такой инструмент, как сводные таблицы, которого не было в Lotus 1-2-3. Сводные таблицы, эксклюзивная для Excel вещь, оказалась так ошеломительно полезна, что люди были склонны осваивать новый программный пакет Excel, а не продолжать работать в Lotus 1-2-3, в котором их не было.

Сводные таблицы вместе с недооценкой успеха Windows в целом, сыграли похоронный марш для Lotus 1-2-3 и положили начало успеху Microsoft Excel.

Что такое сводные таблицы?

Итак, как же лучше охарактеризовать, что собой представляют сводные таблицы?

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

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

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

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

Как создать сводную таблицу?

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

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

Затем на вкладке Insert (Вставка) выберите команду PivotTable (Сводная таблица):

Появится диалоговое окно Create PivotTable (Создание сводной таблицы) с двумя вопросами для Вас:

Excel создаст новый лист и разместит на нем пустую сводную таблицу:

Обратите внимание, что заголовок Amount теперь отмечен галочкой, а в области Values (Значения) появилась запись Sum of Amount (Сумма по полю Amount), указывающая на то, что столбец Amount просуммирован.

Если мы посмотрим на саму сводную таблицу, то увидим сумму всех значений из столбца Amount исходной таблицы.

Итак, наша первая сводная таблица создана! Удобно, но не особо впечатляет. Вероятно, мы хотим получить больше информации о наших данных, чем есть сейчас.

Становится интересней! Наша сводная таблица начинает обретать форму…

Видите преимущества? За пару кликов мы создали таблицу, которую вручную пришлось бы создавать очень долго.

Что ещё мы можем сделать? Ну, в определённом смысле, наша сводная таблица уже готова. Мы создали полезную сводку по исходным данным. Уже получена важная информация! В оставшейся части статьи мы разберём некоторые способы создания более сложных сводных таблиц, а также узнаем, как их настраивать.

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

Во-первых, мы можем создать двумерную сводную таблицу. Сделаем это, используя заголовок столбца Payment Method (Способ оплаты). Просто перетащите заголовок Payment Method в область Column Labels (Колонны):

Теперь сделаем трёхмерную таблицу. Как может выглядеть такая таблица? Давайте посмотрим…

Перетащите заголовок Package (Комплекс) в область Report Filter (Фильтры):

Это даёт нам возможность отфильтровать отчёт по признаку «Какой комплекс отдыха был оплачен». Например, мы можем видеть разбивку по продавцам и по способам оплаты для всех комплексов или за пару щелчков мышью изменить вид сводной таблицы и показать такую же разбивку только для заказавших комплекс Sunseekers.

Итак, если Вы правильно это понимаете, то нашу сводную таблицу можно назвать трёхмерной. Продолжим настраивать…

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

Давайте посмотрим, на что теперь похожа наша сводная таблица. Как видите, столбец Cash исчез из нее.

Форматирование сводных таблиц в Excel

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

Нажмите кнопку Number Format (Числовой формат), откроется диалоговое окно Format Cells (Формат ячеек):

Из списка Category (Числовые форматы) выберите Accounting (Финансовый) и число десятичных знаков установите равным нулю. Теперь несколько раз нажмите ОК, чтобы вернуться назад к нашей сводной таблице.

Как видите, числа оказались отформатированы как суммы в долларах.

Раз уж мы занялись форматированием, давайте настроим формат для всей сводной таблицы. Есть несколько способов сделать это. Используем тот, что попроще…

Далее разверните меню нажатием на стрелочку в нижнем правом углу раздела PivotTable Styles (Стили сводной таблицы), чтобы увидеть обширную коллекцию встроенных стилей:

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

Прочие настройки сводных таблиц в Excel

Иногда приходится фильтровать данные по датам. Например, в нашем списке торговых операций присутствует много-много дат. Excel предоставляет инструмент для группировки данных по дням, месяцам, годам и т.д. Давайте посмотрим, как это делается.

Для начала уберите запись Payment Method из области Column Labels (Колонны). Для этого перетащите его обратно к списку заголовков, а на его место переместите заголовок Date Booked (Дата бронирования):

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

Чтобы исправить это, кликните правой кнопкой мыши по любой дате и выберите из контекстного меню пункт Group (Группировать):

Появится диалоговое окно группировки. Мы выбираем Months (Месяцы) и жмём ОК:

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

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

То же самое можно проделать с заголовками столбцов (или даже с фильтрами).

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

Заодно, пока мы здесь, давайте изменим Custom Name (Пользовательское имя) с Average of Amount (Количество по полю Amount) на что-нибудь покороче. Введите в этом поле что-нибудь вроде Avg:

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

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

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

1. Перетащите заголовок Salesperson (Торговый представитель) в область Column Labels (Колонны).
2. Трижды перетащите заголовок Amount (Стоимость) в область Values (Значения).
3. Для первого поля Amount измените название на Total (Сумма), а формат чисел в этом поле на Accounting (Финансовый). Количество десятичных знаков равно нулю.
4. Второе поле Amount назовите Average, операцию для него установите Average (Среднее) и формат чисел в этом поле тоже измените на Accounting (Финансовый) с числом десятичных знаков равным нулю.
5. Для третьего поля Amount установите название Count и операцию для него – Count (Количество)
6. В области Column Labels (Колонны) автоматически создано поле Σ Values (Σ Значения) – перетащите его в область Row Labels (Строки)

Общая сумма, среднее значение и количество продаж – всё в одной сводной таблице!

Заключение

[expert_bq id=»1570″]Заодно, пока мы здесь, давайте изменим Custom Name Пользовательское имя с Average of Amount Количество по полю Amount на что-нибудь покороче. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Рис. 6. Созданное ранее вычисляемое поле остается «действительным» даже при изменении структуры сводной таблицы, приводящем к отображению средней выручки за час для каждой услуги и каждого рынка сбыта

Как добавить вычисляемый столбец в сводную таблицу?

• Во-первых, компания Lotus решила, что эта новомодная GUI-платформа с названием Windows — это всего лишь мимолётное увлечение, которое долго не протянет. Они отказались создавать версию Lotus 1-2-3 для Windows (впрочем, только несколько лет), предсказывая, что DOS-версия их программного обеспечения — это всё, что когда-либо будет нужно потребителям. Microsoft, естественно, разработала Excel специально под Windows.
• Во-вторых, Microsoft разработала в Excel такой инструмент, как сводные таблицы, которого не было в Lotus 1-2-3. Сводные таблицы, эксклюзивная для Excel вещь, оказалась так ошеломительно полезна, что люди были склонны осваивать новый программный пакет Excel, а не продолжать работать в Lotus 1-2-3, в котором их не было.

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

Excel как изменить сводную таблицу

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

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

В появившемся окне необходимо выбрать источник данных, который представляет:

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

Управление списком полей таблицы

После нажатия кнопки «ОК» в окне создания сводной таблицы, создается пустая область для ее размещения, и отображается окно со списком всех полей.

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

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

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

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

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

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

Вычисляемые поля сводной таблицы

Задайте понятное имя, и запишите формулу, используя любые функции (имейте в виду, что вычисляемые поля не работают с текстом). В качестве примера умножим курс на 1000 и вычтем 13 процентов (=Курс*1000*0,87). Назовем поле «ЗП», добавим в область значений и в качестве операции применим максимум. Посмотрите новый вид отчета:

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

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

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

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

Теперь законченная сводная таблица выглядит так на листе Excel:

Помимо рассмотренных свойств через параметры таблицы можно установить:

1. Имя сводной таблицы;
2. Объединение и выравнивание подписей;
3. Вывод значений для пустых ячеек;
4. Автоматическое изменение ширины столбцов;
5. Отображение общих итогов по строкам и столбцам;
6. Сортировку;
7. Печать;
8. Обновление и др.

Теперь Вы умеете пользоваться сводными таблицами Excel. Полученные здесь знания позволят Вам далее самостоятельно экспериментировать с ними и повышать свой навык.

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

[expert_bq id=»1570″]Созданное ранее вычисляемое поле остается действительным даже при изменении структуры сводной таблицы, приводящем к отображению средней выручки за час для каждой услуги и каждого рынка сбыта. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Можно заметить, что результат по новому полю возвращает «странный» результат. Это происходит от того, что предварительно по значению столбца «курс» производится суммирование, а только потом полученный результат используется для вычисления. Если добавить в область названия строк дату, то расчет вернет требуемый результат:

Как сделать вычисляемое поле в таблице excel?

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

Для начала уберите запись Payment Method из области Column Labels (Колонны). Для этого перетащите его обратно к списку заголовков, а на его место переместите заголовок Date Booked (Дата бронирования):

Excel 2007: настройка параметров сводных таблиц

Николай КАРПЕНКО, канд. техн. наук, доцент кафедры прикладной математики и информационных технологий Харьковской национальной академии городского хозяйства

Состав параметров поля сводной таблицы зависит от того, является ли это поле ключевым, или оно расположено в области значений. Это вполне объяснимо: над этими элементами выполняются совершенно разные операции. Начнем с полей, которые расположены в области данных.

Часть настроек поля в области данных нам уже знакома. Мы неоднократно строили сводные отчеты и работали в окне «

1) открываем сводный отчет. Я выбрал таблицу, изображенную на рис. 1 на с. 32. Сейчас в ней все данные оформлены шрифтом

Arial обычного начертания, размером 8 пт. Формат представления чисел: для стоимостных показателей — два знака после запятой, для количественных показателей — ноль знаков после запятой;

3) не снимая выделения, щелкаем на блоке правой кнопкой мыши;

Формат ячеек…». Появится одноименное окно «Формат ячеек»;

Число» и выбираем вариант «Числовой» с тремя десятичными знаками после запятой;

Формат ячеек» нажимаем «ОК». Значения в блоке «C3:E3» приобрели новое форматирование;

7) опять щелкаем правой кнопкой мыши в области сводной таблицы;

Обновить». Форматирование таблицы пропало, внешний вид ячеек стал таким же, как до внесенных нами изменений.

Важно! По умолчанию при обновлении сводной таблицы ячейки отчета получают параметры форматирования, характерные для стиля «

Обычный». Конкретные значения этих параметров зависят от настроек в вашей версии программы Excel.

Снять указанную проблему очень просто — нужно определить параметры форматирования непосредственно в сводном отчете. Вот как это сделать:

1) ставим указатель активной ячейки на поле, для которого нужно изменить параметры форматирования. Например, на ячейку «

щелкаем правой кнопкой мыши. Появится контекстное меню, как на рис. 2;

Параметры полей значений». Появится окно «Параметры поля значений», как на рис. 3 (на с. 33);

Числовой формат». Появится знакомое окно «Формат значений». Правда, в усеченном варианте: в нем представлена единственная закладка — «Число»;

5) выбираем параметры форматирования для выбранного значения в сводном отчете (в нашем случае — это объемы реализации в гривнях). Согласно примеру в окне «

Формат ячеек» нужно выбрать числовой формат с округлением до трех знаков;

ОК». Форматирование данных в сводном отчете изменилось;

7) щелкаем правой кнопкой мыши в области сводной таблицы;

Обновить». Форматирование таблицы осталось без изменений.

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

С параметрами поля, которое расположено в области данных, мы разобрались. Они сосредоточены в окне «

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

1) открываем сводный отчет, я выбрал таблицу, изображенную на рис. 4 (см. с. 33). В ней значениями являются «

Сумма, грн.» и «Кол., шт.». Ключевыми полями этой таблицы являются «Дата» и «Покупатель»;

2) щелкаем правой кнопкой мыши на любой ячейке в колонке «

Покупатель» Например, на «A3». Появится контекстное меню, как на рис. 5 (см. с. 33);

Параметры поля…». Появится окно, как на рис. 6 (см. с. 34). В этом окне нам предстоит определить параметры для ключевого поля.

Как видим, форма окна изменилась. Теперь в нем две закладки: «

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

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

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

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

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

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

4) среди параметров форматирования полей в области значений доступны только настройки представления чисел;

5) для ключевых полей можно определить способ подведения промежуточных итогов и макет сводного отчета.

Теперь вернемся к четвертому пункту наших выводов. Бросается в глаза тот факт, что окно «

Формат ячеек» при работе с полями в области значений представлено единственной закладкой для форматирования чисел. И это не случайно. Делаем так:

1) выделяем в отчете на рис. 1 любой блок ячеек (например, «

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

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

Начнем с интерфейса. Как только активная ячейка попадает в область сводного отчета, в главном меню программы появляется отдельный пункт меню «

Работа со сводными таблицами». Щелкаем на этом пункте левой кнопкой мыши, переходим в режим работы со сводными таблицами. Здесь доступны два раздела: «Параметры» и «Конструктор» (рис. 8). Нас сейчас интересует раздел «Параметры». Посмотрим на его состав подробнее.

Лента меню раздела параметров состоит из восьми групп иконок. Первая группа — «

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

Объединить и выровнять по центру ячейки с подписями» (рис. 9) работает в тех случаях, когда в области данных сводного отчета расположено несколько полей. Применительно к таблице на рис. 1 этот параметр объединит ячейки в колонке «Покупатель» таким образом: «A3:A4», «A5:A6» и т. д. Наименование покупателя в объединенных ячейках будет расположено по центру (рис. 10):

Отступ для названий строк в сжатой форме» позволяет указать отступ строк в области заголовка строки при отображении отчета в компактном формате. Уровень отступа может принимать значения от «0» до «127»;

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

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

Для ошибок отображать» позволяет ввести текст, который будет показан в сводном отчете вместо сообщения об ошибке. Например, вы можете включить этот фалажок и ввести в специальное окошко текст «!». Теперь все ячейки с ошибками будут отмечены восклицательным знаком;

Для пустых ячеек отображать», здесь можно указать значение, которые Excel поставит в сводном отчете на место пустых ячеек.

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

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

— флажок «Автоматически изменять ширину столбцов при обновлении» включает режим автоподбора ширини колонок под имеющиеся значения сводного отчета. Такая возможность выглядит привлекательно, так как избавляет от необходимости периодически менять ширину столбцов, когда в них не помещяются данные. Но есть и обратная сторона медали;

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

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

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

Внешний вид окна этой закладки показан на рис. 11. Первой в окне расположена группа «

Параметры», затем на иконке «Параметры» группы «Сводная таблица»;

Параметры сводной таблицы» переходим на закладку «Итоги и фильтры»;

Показывать общие итоги для строк», «Показывать общие итоги для столбцов». Отчет примет форму, как на рис. 12 (см. с. 37).

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

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

Сортировка» состоит из единственного флажка: «Использовать списки при сортировке». Если флажок включен, Excel сможет сделать сортировку строк сводного отчета по спискам пользователя. В таком списке, например, могут быть перечислены названия дней недели.

Совет Если вы не пользуетесь специфическими правилами сортировки, отключите флажок «

Использовать списки при сортировке». Особенно при работе с большими таблицами. Это существенно ускорит обработку данных.

Экран» (в некоторых версиях Excel 2007 она называется «Отображение») и «Сортировка списка полей». В группе «Экран» есть семь параметров (рис. 13). Вот основные из них:

названия строк» добавляем поле «Дата». В области «Значения» оставляем только поле «Сумма, грн». Теперь в сводном отчете появились символы группировки (рис. 14 на с. 38);

Вывод», отключаем флажок «Показывать кнопки развертывания и свертывания». Символы группировки значений исчезли (рис. 14);

Показывать кнопки развертывания и свертывания» перед распечаткой документа — его внешний вид от этого только выиграет.

— флажок «Показывать контекстные всплывающие подсказки» работает только при работе с документом на экране. Чтобы посмотреть на действие этого параметра, сделайте так:

2) наведите указатель мыши на любое поле внутри сводной таблицы. Через некоторое время появится всплывающее окно с описанием этого поля;

3) откройте окно «Параметры сводной таблицы» на закладке «Вывод».

4) отключите флажок «Показывать контекстные всплывающие подсказки»;

5) снова поставьте указатель мыши на любое поле внутри сводной таблицы. Всплывающая подсказка не появится.

Дополнительный параметр «Показывать свойства во всплывающих подсказках» включает или отключает отображение подробной информации о поле, на котором находится указатель мыши;

— флажок «Показывать названия полей и списки фильтров» влияет на заголовки сводной таблицы. Его работу покажем на таком примере:

4) отключите флажок «Показывать названия полей и списки фильтров». Отчет примет форму, как на рис. 15.

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

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

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

Есть база данных, как на рис. 1. Объем файла большой, но меня интересует перечень товаров, которые содержатся в этой базе. Делаем так:

Параметры» (раздел меню по работе со сводными таблицами);

Вывод» включаем флажок «Показывать элементы без данных в строках». В отдельной колонке сводного отчета появится перечень товаров из базы данных;

Вставить», из появившегося меню выбираем «Специальная вставка…»;

11) в появившемся окне специальной вставки ставим переключатель «

В списке на рис. 16 нет полей в области данных, однако есть заголовок списка (колонка «

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

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

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

Excel 2007 вынесли на отдельную закладку (рис. 17). Она так и называется — «Печать». На закладке всего три элемента:

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

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

Важно! Для печати заголовков нужно также настроить параметры печати документа Excel. Для этого откройте раздел «

Разметка страницы» главного меню, затем щелкните на пункте «Параметры страницы», в открывшемся окне перейдите на закладку «Лист» и укажите значения в полях «сквозные строки» и (или) «сквозные столбцы».

Параметры этой закладки (рис. 18 на с. 40) влияют на способ хранения данных с обновлением сводного отчета:

Сохранять исходные данные вместе с файлом» при включенном состоянии обеспечивает хранение данных из внешнего источника прямо внутри рабочей книги. Это увеличивает объем файла Excel, зато гарантирует быстрое обновление данных сводного отчета;

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

С5» (объем реализации на сумму 236,20 грн. для ЧП «Лапина» за «03/01/2009»);

С5». Excel создаст новый рабочий лист и перенесет в него те записи исходной базы данных, которые связаны с выбранным элементом отчета (рис. 19).

Разрешить отображение деталей» отключить, после двойного щелчка в области данных Excel сообщит о невозможности корректировать сводную таблицу;

Совет Работая со сводным отчетом, оставляйте включенным флажок «

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

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

Обновление при открытии файла» действует индивидуально для каждой сводной таблицы.

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

Совет Если вы работаете с большой таблицей, где много разных значений ключевого поля, установите параметр «

Число элементов, сохраняемых для каждого поля» в состояние «Максимум». В этом случае в сводном отчете Excel 2007 сможет хранить до 1 048 576 различных значений ключа.

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

[expert_bq id=»1570″]Поскольку мы хотели добавить сумму НДС к общему объему продаж для каждого продукта, мы оставили в поле Суммировать по значение по умолчанию СУММ. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Формулы, применяемые для создания вычисляемых полей и элементов, не являются стандартными формулами Excel. Другими словами, эти формулы не вводятся в ячейки – для их ввода используется специальное диалоговое окно. Эти формулы хранятся вместе с другими данными сводной таблицы.

Как добавить и использовать вычисляемые поля в сводных таблицах Google Таблиц

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