Моя знакомая, экономист на предприятии, владеет Экселем, иногда посматривает на форумы, но 5 лет не могла подступиться к самому, на мой взгляд, полезному инструменту Excel – сводным таблицам. Что же это такое? Сводные таблицы – это результат выборки из правильно построенных данных. Преимуществ у инструмента много, я описывать не буду, просто скажу, что без нее многим приходилось бы осваивать не только Excel, но и Microsoft Access.
Мое мнение – этот инструмент надо изучать самим и в этой статье я расскажу нюансы, об которые спотыкаются новички, которые сами хотят освоить этот инструмент.
СТ требует правильно построенных данных. Идеальный вид – одной операции в строчку соответствуют разные поля свойств записи.
Я сваял простую таблицу, которая возникает на любом хозяйственном складе. Склад отгружает 4-м магазинам в течение года стройматериалы и каждая операция отгрузки товара имеет свою запись.
Как видите, здесь нет никакой группировки или суммирования данных, я называю это простыней, т.к., хотя столбцов может быть много, но строк тут гораздо больше. Здесь нет разрывов, что тоже важно.
Excel предложит диапазон, поэтому просто кликайте Ок.
Здесь 4 варианта: строки, столбцы, значения и фильтр отчета.
Остановимся поподробнее. Строки и столбцы в общем-то одинаковые по сути категории. Вы можете сюда поставить те поля, которые являются признаковыми – например, наименование, месяц, категорию. Можно поставить два в строки и ни одного в столбец, в этом случае ваша таблица будет иметь группировку в строках.
Значения – сюда ставим только цифровые значения.
Фильтр отчета . Как вы поняли, вы сами строите каркас и какие-то поля вы можете проигнорировать, какие-то поставить горизонтально, какие-то вертикально. Свои фильтры будут и у строк и у столбцов, но фильтр отчета позволяет более удобно отбирать те данные, которые вам нужно показать в этой таблице.
Теперь на примере моих данных.
Какой вид может принять таблица:
Как видите, вариаций много.
1) Хотя сводная таблица вытаскивает очень наглядно все срезы имеющейся информации, в ней работать не всегда просто, она не поддается редактированию, только read-only . Если у вас много разрезов информации, то проще сделать таблицу и заполнить ее, используя функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ и правила работы с относительными ссылками.
Формат функции прост («Значения»; «Сводная таблица»; «Поле1»; «Элемент1»; «Поле2»; «Элемент2» и т.д.)
Т.е., можно вручную заменить значения Элементов на относительные ссылки вашей таблицы.
2) Чтобы постоянно обновлять данные и не трогать макет, добавляйте данные внизу простыни. Тогда у вас ничего не поползет, а обновление произойдет одной кнопкой обновить по самой СТ. Плюс в источнике данных для СТ укажите или все столбцы простыни (ленивый вариант) или имя диапазона, размеры которого будете менять произвольно, не заходя в настройки СТ.
«Глаза боятся, а руки делают»
Сводные таблицы - один из самых эффективных инструментов в MS Excel. С их помощью можно в считанные секунды преобразовать миллион строк данных в краткий отчет. Помимо быстрого подведения итогов, сводные таблицы позволяют буквально «на лету» изменять способ анализа путем перетаскивания полей из одной области отчета в другую.
Сводные таблицы - это также один из самых недооцененных инструментов эксель. Большинство пользователей не подозревает, какие возможности находятся в их руках. Представим, что сводные таблицы еще не придумали. Вы работаете в компании, которая продает свою продукцию различным клиентам. Для простоты в ассортименте только 4 позиции. Продукцию регулярно покупает пара десятков клиентов, которые находятся в разных регионах. Каждая сделка заносится в базу данных и представляет отдельную строку.
Ваш директор дает указание сделать краткий отчет о продажах всех товаров по регионам (областям). Решить задачу можно следующим образом.
Вначале создадим макет таблицы, то есть шапку, состоящую из уникальных значений товаров и регионов. Сделаем копию столбца с товарами и удалим дубликаты. Затем с помощью специальной вставки транспонируем столбец в строку. Аналогично поступаем с областями, только без транспонирования. Получим шапку отчета.
Данную табличку нужно заполнить, т.е. просуммировать выручку по соответствующим товарам и регионам. Это нетрудно сделать с помощью функции СУММЕСЛИМН. Также добавим итоги. Получится сводный отчет о продажах в разрезе область-продукция.
Вы справились с заданием и показываете отчет директору. Посмотрев на таблицу, он генерирует сразу несколько замечательных идей.
- Можно ли отчет сделать не по выручке, а по прибыли?
- Можно ли товары показать по строкам, а регионы по столбцам?
- Можно ли такие таблицы делать для каждого менеджера в отдельности?
Даже если вы опытный пользователь Excel, на создание новых отчетов потребуется немало времени. Это уже не говоря о возможных ошибках. Однако если вы владеете сводными таблицами, то ответите: да, мне нужно 5 минут, возможно меньше.
Вот как это делается. Открываем исходные данные. Сводную таблицу можно строить по обычному диапазону, но правильнее будет преобразовать его в таблицу Excel . Это сразу решит вопрос с автоматическим захватом новых данных. Выделяем любую ячейку и переходим во вкладку Вставить . Слева на ленте находятся две кнопки: и Рекомендуемые сводные таблицы.
Если Вы не знаете, каким образом организовать имеющиеся данные, то можно воспользоваться командой Рекомендуемые сводные таблицы . Эксель на основании ваших данных покажет миниатюры возможных макетов.
Кликаете на подходящий вариант и сводная таблица готова. Остается ее только довести до ума, т.к. вряд ли стандартная заготовка полностью совпадет с вашими желаниями. Если же нужно построить сводную таблицу с нуля, или у вас старая версия программы, то нажимаете кнопку . Появится окно, где нужно указать исходный диапазон (если активировать любую ячейку Таблицы Excel, то он определится сам) и место расположения будущей сводной таблицы (по умолчанию будет выбран новый лист).
Очень часто ничего менять здесь не нужно. После нажатия Ок будет создан новый лист Excel с пустым макетом сводной таблицы.
Перед тем, как перейти к настройкам, познакомимся с интерфейсом и основными понятиями. Макет таблицы настраивается в панели Поля сводной таблицы , которая находится в правой части листа.
В верхней части панели находится перечень всех доступных полей, то есть столбцов в исходных данных. Если в макет нужно добавить новое поле, то можно поставить галку напротив - эксель сам определит, где должно быть размещено это поле. Однако угадывает далеко не всегда, поэтому лучше перетащить мышью в нужное место макета. Удаляют поля также: снимают флажок или перетаскивают назад.
Сводная таблица состоит из 4-х областей, которые находятся в нижней части панели: значения, строки, столбцы, фильтры. Рассмотрим подробней их назначение.
Область значений
- это центральная часть сводной таблицы со значениями, которые получаются путем агрегирования выбранным способом исходных данных.
В большинстве случае агрегация происходит путем Суммирования
. Если все данные в выбранном поле имеют числовой формат, то Excel назначит суммирование по умолчанию. Если в исходных данных есть хотя бы одна текстовая или пустая ячейка, то вместо суммы будет подсчитываться Количество
ячеек. В нашем примере каждая ячейка - это сумма всех соответствующих товаров в соответствующем регионе.
В ячейках сводной таблицы можно использовать и другие способы вычисления. Их около 20 видов (среднее, минимальное значение, доля и т.д.). Изменить способ расчета можно несколькими способами. Самый простой, это нажать правой кнопкой мыши по любой ячейке нужного поля в самой сводной таблице и выбрать другой способ агрегирования.
Область строк - названия строк, которые расположены в крайнем левом столбце. Это все уникальные значения выбранного поля (столбца). В области строк может быть несколько полей, тогда таблица получается многоуровневой. Здесь обычно размещают качественные переменные типа названий продуктов, месяцев, регионов и т.д.
Область столбцов - аналогично строкам показывает уникальные значения выбранного поля, только по столбцам. Названия столбцов - это также обычно качественный признак. Например, годы и месяцы, группы товаров.
Область фильтра - используется, как ясно из названия, для фильтрации. Например, в самом отчете показаны продукты по регионам. Нужно ограничить сводную таблицу какой-то отраслью, определенным периодом или менеджером. Тогда в область фильтров помещают поле фильтрации и там уже в раскрывающемся списке выбирают нужное значение.
С помощью добавления и удаления полей в указанные области вы за считанные секунды сможете настроить любой срез ваших данных, какой пожелаете.
Посмотрим, как это работает в действии. Создадим пока такую же таблицу, как уже была создана с помощью функции СУММЕСЛИМН. Для этого перетащим в область Значения поле «Выручка», в область Строки перетащим поле «Область» (регион продаж), в Столбцы - «Товар».
В результате мы получаем настоящую сводную таблицу.
На ее построение потребовалось буквально 5-10 секунд. Изменить существующую сводную таблицу также легко. Посмотрим, как пожелания директора легко воплощаются в реальность.
Заменим выручку на прибыль.
Товары и области меняются местами также перетягиванием мыши.
Для фильтрации сводных таблиц есть несколько инструментов. В данном случае просто поместим поле "Менеджер" в область фильтров.
На все про все ушло несколько секунд. Вот, как на самом деле легко работать со сводными таблицами. Конечно, не все задачи столь тривиальные. Бывают и такие, что необходимо использовать более замысловатый способ агрегации, добавлять вычисляемые поля, условное форматирование и т.д. Но это уже более продвинутое использование сводных таблиц.
Исходные данные
Для успешной работы со сводными таблицами исходные данные должны отвечать ряду требований. Обязательным условием является наличие названий над каждым полем (столбцом), по которым эти поля будут идентифицироваться. Теперь полезные советы.
1. Лучший формат для данных - это Таблица Excel. Она хороша тем, что у каждого поля есть наименование и при добавлении новых строк они автоматически включаются в сводную таблицу.
2. Избегайте повторения групп в виде столбцов. Например, все даты должны находиться в одном поле, а не разбиты, например, по месяцам в отдельных столбцах.
3. Уберите пропуски и пустые ячейки иначе данная строка может выпасть из анализа.
4. Применяйте правильное форматирование к полям. Числа должны быть в числовом формате, даты должны быть датой. Иначе возникнут проблемы при группировке и математической обработке. Но здесь эксель вам поможет, т.к. сам неплохо определяет формат данных.
В целом требований немного, но их следует знать.
Если внести изменения в источник (например, добавить новые строки), сводная таблица не изменится, пока вы ее не обновите через правую кнопку мыши
или
через команду во вкладке Данные - Обновить все
.
Так сделано специально из-за того, что сводная таблица занимает много места в оперативной памяти. Поэтому, чтобы расходовать ресурсы компьютера более экономно, работа идет не напрямую с источником, а с кешем, где находится моментальный снимок исходных данных.
Используя сводные таблицы даже на таком базовом уровне, вы сможете в разы увеличить скорость и качество обработки больших массивов данных. Если что-то осталось непонятным, напишите в комментариях.
Ниже находится видеоурок о том, как в Excel создать простую сводную таблицу.
P.S. Углубленные знания о сводных таблицах можно получить в
Возможность быстрого анализа данных часто помогает принимать более эффективные деловые решения. Но иногда непонятно, откуда стоит начать, особенно при наличии большого количества сведений. Сводные таблицы упрощают обобщение, анализ, изучение и представление данных. Их можно создавать с помощью всего нескольких действий и быстро настраивать в зависимости от того, как вы хотите отобразить результаты. Вы также можете создавать сводные диаграммы на основе сводных таблиц, которые будут автоматически обновляться при их изменении.
Например, вот простой список расходов семьи и сводная таблица, созданная на его основе:
А вот сводная диаграмма:
Примечание: Снимки экрана в этой статье получены в Excel 2016. Если вы используете другую версию, интерфейс может немного отличаться, но функции будут такими же.
Перед началом работы
Создание сводной таблицы
Если у вас недостаточно опыта работы со сводными таблицами или вы не знаете, с чего начать, лучше воспользоваться рекомендуемой сводной таблицей . При этом Excel определяет подходящий макет, сопоставляя данные с наиболее подходящими областями в сводной таблице. Это позволяет получить отправную точку для дальнейших экспериментов. После создания рекомендуемой сводной таблицы вы можете изучить различные варианты ориентации и изменить порядок полей для получения нужных результатов. Рекомендуемые сводные таблицы впервые появились в Excel 2013, поэтому если у вас более ранняя версия, создайте сводную таблицу вручную, как описано ниже.
Работа со списком полей сводной таблицы
В области Имя поля вверху установите флажки для полей, которые вы хотите добавить в сводную таблицу. По умолчанию нечисловые поля добавляются в область строк , поля значений даты и времени - в область столбцов , а числовые поля - в область значений . Вы также можете вручную перетаскивать элементы в любые поля сводной таблицы. Если элемент больше не нужен, просто перетащите его за пределы списка полей или снимите его флажок. Возможность перестановки элементов - одна из особенностей сводной таблицы, благодаря которой можно быстро и просто изменять ее вид.
Значения в сводной таблице
Обновление сводных таблиц
При добавлении новых данных в источник необходимо обновить все сводные таблицы, созданные на его основе. Чтобы обновить одну сводную таблицу, можно щелкнуть правой кнопкой мыши в любом месте ее диапазона и выбрать команду Обновить . При наличии нескольких сводных таблиц сначала выберите любую ячейку в любой сводной таблице, а затем на ленте выберите Работа со сводными таблицами > Анализ , в группе Данные щелкните стрелку под кнопкой Обновить и выберите команду Обновить все .
Чтобы укомплектовать данные в таблице наиболее удобным способом и всегда иметь возможность быстрого доступа к ним, попробуйте сделать сводную таблицу в программе Microsoft Office Excel. Составление такого типа таблицы займёт у вас пару минут, а её использование сохранит драгоценное время в дальнейшем.
Зайдите в программу Эксель. В шапке программы среди множества разделов найдите «Вставка» и кликните по ней. Вам понадобится инструмент «Сводная таблица», если данные вами уже введены и отформатированы. Если же нет, приступите к вводу.
Это особенно удобно, когда вы не уверены на сто процентов, что не будете менять ваши значения в ячейках. Вы просто создадите таблицу на следующем листе, а при исправлениях, сможете удалить её без потери информации.
Значение суммы будет отображено отдельно под каждым столбцом автоматически, справа снизу вы увидите некоторые параметры для редактирования таблицы.
Теперь вы без труда можете создавать сводные таблицы в программе от Microsoft — Excel. В ней есть опции помещения данных из совершенно другой таблицы, даже на другом листе. А выделяя разные столбцы, вы имеете возможность комбинировать множество таблиц в одной.
Такой тип таблиц лучше использовать с очень объёмными и большими таблицами, так как вам не придётся вручную задавать значение суммы для каждого столбца.
Привет всем! Сегодняшний материал для тех, кто продолжает осваивать работу с программами-приложениями, и не знает, как сделать сводную таблицу в excel.
Создав общую таблицу, в каком либо из текстовых документов, можно осуществить её анализ, сделав в Excel сводные таблицы.
Создание сводной Эксель таблицы требует соблюдения определенных условий:
- Данные вписываются в таблицу, где есть столбцы и списки с названиями.
- Отсутствие незаполненных форм.
- Отсутствие скрытых объектов.
Как сделать сводную таблицу в excel: пошаговая инструкция
Для создания сводной таблицы необходимо:
Создался пустой лист, где видно списки областей и полей. Заголовки стали полями в нашей новой таблице. Сводная таблица будет формироваться путем перетаскивания полей.
Помечаться они будут галочкой, и для удобства анализа вы будете их менять местами в табличных областях.
Я решил, что анализ данных буду делать через фильтр по продавцам, чтобы было видно кем и на какую сумму каждый месяц было продано, и какой именно товар.
Выбираем конкретного продавца. Зажимаем мышку и переносим поле «Продавец» в «Фильтр отчета». Новое поле отмечается галочкой, и вид таблицы немного изменяется.
Категорию «Товары» мы поставим в виде строк. В «Названия строк» мы переносим необходимое нам поле.
Для отображения выпадающего списка имеет значение, в какой последовательности мы указываем наименование. Если изначально в строках делаем выбор в пользу товара, а потом указываем цену, то товары как раз и будут выпадающими списками, и наоборот.
Столбец «Единицы», будучи в главной таблице, отображал количество товара проданного определенным продавцом по конкретной цене.
Для отображения продаж, например, по каждому месяцу, нужно поле «Дата» поставить на место «Названия столбцов». Выберите команду «Группировать», нажав на дату.
Указываем периоды даты и шаг. Подтверждаем выбор.
Видим такую таблицу.
Сделаем перенос поля «Сумма» к области «Значения».
Стало видно отображение чисел, а нам необходим именно числовой формат
Для исправления, выделим ячейки, вызвав окно мышкой, выберем «Числовой формат».
Числовой формат мы выбираем для следующего окна и отмечаем «Разделитель групп разрядов». Подтверждаем кнопкой «ОК».
Оформление сводной таблицы
Если мы поставим галочку, которая подтверждает выделение сразу нескольких объектов, то сможем обрабатывать данные сразу по нескольким продавцам.
Применение фильтра возможно для столбцов и строк. Поставив галочку на одной из разновидностей товара, можно узнать, сколько его реализовано одним или несколькими продавцами.
Отдельно настраиваются и параметры поля. На примере мы видим, что определенный продавец Рома в конкретном месяце продал рубашек на конкретную сумму. Нажатием мышки мы в строке «Сумма по полю…» вызываем меню и выбираем «Параметры полей значений».
Далее для сведения данных в поле выбираем «Количество». Подтверждаем выбор.
Посмотрите на таблицу. По ней четко видно, что в один из месяцев продавец продал рубашки в количестве 2-х штук.
Теперь меняем таблицу и делаем так, чтобы фильтр срабатывал по месяцам. Поле «Дата» мы переносим в «Фильтр отчета», а там где «Названия столбцов», будет «Продавец». Таблица отображает весь период продаж или за конкретный месяц.
Выделение ячеек в сводной таблице приведет к появлению такой вкладки как «Работа со сводными таблицами», а в ней будут еще две вкладки «Параметры» и «Конструктор».
На самом деле рассказывать о настройках сводных таблиц можно еще очень долго. Проводите изменения под свой вкус, добиваясь удобного для вас пользования. Не бойтесь нажимать и экспериментировать. Любое действие вы всегда сможете изменить нажатием сочетания клавиш Ctrl+Z.
Надеюсь, вы усвоили весь материал, и теперь знаете, как сделать сводную таблицу в excel.