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

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

Сводные таблицы в Эксель

Основное преимущество суммарных таблиц в том, что при изменении какой-либо информации на связанных страницах, сведения в общей таблице автоматически обновятся. Это помогает значительно упростить и ускорить работу с большими объемами информации. Далее в статье будет описываться процесс создания сводных таблиц с помощью Майкрософт Эксель.

Стандартный способ создания сводной таблицы

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

Для примера рассматривается таблица со сформированным объемом заработной платы сотрудникам предприятия. Из информации известны ФИО, категория, дата начисления средств, пол работников и общая сумма к выплате. Необходимо будет распределить всю информацию в одну общую таблицу. Обратите внимание, расчеты будут вестись только за 3 квартал года.

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

Параметры таблицы

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

1. Нажмите на кнопку, которая находится возле пункта «Дата».
2. В отобразившемся окне поставьте флажок возле значения «Выделить несколько…».
3. Уберите флажки, которые расположены напротив всех дат, не относящихся к третьему кварталу, и кликните на клавишу «Ок».

Встроенный инструмент системы

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

1. Перейдите во вкладку «Файл» и в появившемся списке выберите строку «Параметры».
2. Перед вами появится новая вкладка, в которой слева необходимо отметить пункт «Панель быстрого доступа».
3. Из левого столбца в перечне выберите «Мастер сводных таблиц и диаграмм», и нажмите на пункт «Добавить». Для окончания переноса кликните на клавишу «Ок».

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

1. В «Шаге 1» определите, где находятся данные, которые будут применяться во время формирования таблицы, и определите вид отчета, после чего кликните на пункт «Далее».
2. На втором этапе можно указать диапазон исходных данных, но в описанном в статье примере эти действия предпринимать нет необходимости, поэтому просто нажмите «Далее».
3. В последнем шаге необходимо выбрать место расположения создаваемой таблицы, затем подтвердите свои действия кнопкой «Готово».
4. После выполнения этих шагов откроется точно такой же документ, который был описан в предыдущем способе.
5. Для заполнения суммарной таблицы выполните такие же действия, которые описывались в стандартном способе создания таблицы.

Заключение

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

Excel сводная таблица условное форматирование • Вэб-шпаргалка для интернет предпринимателей!

• Умная таблица получает имя (в примере выше Таблица1, его можно легко поменять), которое можно использовать для определения диапазона в качестве источника для сводной таблицы;
• Умная таблица автоматически изменяет размер при добавлении или удалении новых строк или столбцов.

Щелкните на кнопке ОК, чтобы применить условное форматирование к сводной таблице. Как видно на рис. 6.32, в сводную таблицу добавляются значки для быстрого определения категории, которой соответствует каждое значение. Теперь примените такое же условное форматирование к полю Выручка за час. По окончании сводная таблица должна выглядеть так, как показано на рис. 6.32.

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

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

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

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

Эту проблему можно решить задав в качестве источника данных не фиксированный диапазон (как в примере выше мы использовали ссылку $A200:$G$820), а целиком выделить все столбцы, не ограничивая таблицу по высоте (т.е. использовать ссылку $A:$G).

Как Поменять Вид Сводной Таблицы в Excel. Дополнительные сведения | 📝Справочник по Excel

Таким образом, правило будет применено к аналогичным данным всех остальных ветеринаров в сводной таблице, без необходимости применять это правило к каждому диапазону по отдельности. Эти же опции Вы сможете найти, если решите создать новое правило форматирования в диалоговом окне New Formatting Rule (Создание правила форматирования).

В некоторых случаях имеет смысл разделить условное форматирование, как было сделано в прошлом примере, применяя его только к ячейкам, содержащим данные одного уровня, чтобы разделить итоги и общие итоги (Grand Total). Но не всегда имеет смысл это делать.

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

1. Щелкните сводную таблицу. …
2. На вкладке Анализ в группе Вычисления выберите команду Поля, элементы и наборы, а затем — пункт Вычисляемое поле.
3. В поле Имя введите имя для поля.
4. В поле Формула введите формулу для поля. …
5. Нажмите кнопку Добавить.

Давайте вернемся к исходной таблице с данными, встанем в любую ячейки таблицы и в панели вкладок перейдем на Главная -> Стили -> Форматировать как таблицу: