Excel что такое сводная таблица
Как сделать сводную таблицу в Excel: пошаговая инструкция
Сводные таблицы – один из самых эффективных инструментов в MS Excel. С их помощью можно в считанные секунды преобразовать миллион строк данных в краткий отчет. Помимо быстрого подведения итогов, сводные таблицы позволяют буквально «на лету» изменять способ анализа путем перетаскивания полей из одной области отчета в другую.
Cводная таблица в Эксель – это также один из самых недооцененных инструментов. Большинство пользователей не подозревает, какие возможности находятся в их руках. Представим, что сводные таблицы еще не придумали. Вы работаете в компании, которая продает свою продукцию различным клиентам. Для простоты в ассортименте только 4 позиции. Продукцию регулярно покупает пара десятков клиентов, которые находятся в разных регионах. Каждая сделка заносится в базу данных и представляет отдельную строку.
Ваш директор дает указание сделать краткий отчет о продажах всех товаров по регионам (областям). Решить задачу можно следующим образом.
Вначале создадим макет таблицы, то есть шапку, состоящую из уникальных значений товаров и регионов. Сделаем копию столбца с товарами и удалим дубликаты. Затем с помощью специальной вставки транспонируем столбец в строку. Аналогично поступаем с областями, только без транспонирования. Получим шапку отчета.
Данную табличку нужно заполнить, т.е. просуммировать выручку по соответствующим товарам и регионам. Это нетрудно сделать с помощью функции СУММЕСЛИМН. Также добавим итоги. Получится сводный отчет о продажах в разрезе область-продукция.
Вы справились с заданием и показываете отчет директору. Посмотрев на таблицу, он генерирует сразу несколько замечательных идей.
— Можно ли отчет сделать не по выручке, а по прибыли?
— Можно ли товары показать по строкам, а регионы по столбцам?
— Можно ли такие таблицы делать для каждого менеджера в отдельности?
Даже если вы опытный пользователь Excel, на создание новых отчетов потребуется немало времени. Это уже не говоря о возможных ошибках. Однако если вы знаете, как сделать сводную таблицу в Эксель, то ответите: да, мне нужно 5 минут, возможно, меньше.
Рассмотрим, как создать сводную таблицу в Excel.
Создание сводной таблицы в Excel
Открываем исходные данные. Сводную таблицу можно строить по обычному диапазону, но правильнее будет преобразовать его в таблицу Excel. Это сразу решит вопрос с автоматическим захватом новых данных. Выделяем любую ячейку и переходим во вкладку Вставить. Слева на ленте находятся две кнопки: Сводная таблица и Рекомендуемые сводные таблицы.
Если Вы не знаете, каким образом организовать имеющиеся данные, то можно воспользоваться командой Рекомендуемые сводные таблицы. Эксель на основании ваших данных покажет миниатюры возможных макетов.
Кликаете на подходящий вариант и сводная таблица готова. Остается ее только довести до ума, так как вряд ли стандартная заготовка полностью совпадет с вашими желаниями. Если же нужно построить сводную таблицу с нуля, или у вас старая версия программы, то нажимаете кнопку Сводная таблица. Появится окно, где нужно указать исходный диапазон (если активировать любую ячейку Таблицы Excel, то он определится сам) и место расположения будущей сводной таблицы (по умолчанию будет выбран новый лист).
Обычно ничего менять здесь не нужно. После нажатия Ок будет создан новый лист Excel с пустым макетом сводной таблицы.
Макет таблицы настраивается в панели Поля сводной таблицы, которая находится в правой части листа.
В верхней части панели находится перечень всех доступных полей, то есть столбцов в исходных данных. Если в макет нужно добавить новое поле, то можно поставить галку напротив – эксель сам определит, где должно быть размещено это поле. Однако угадывает далеко не всегда, поэтому лучше перетащить мышью в нужное место макета. Удаляют поля также: снимают флажок или перетаскивают назад.
Сводная таблица состоит из 4-х областей, которые находятся в нижней части панели: значения, строки, столбцы, фильтры. Рассмотрим подробней их назначение.
Область значений – это центральная часть сводной таблицы со значениями, которые получаются путем агрегирования выбранным способом исходных данных.
В большинстве случае агрегация происходит путем Суммирования. Если все данные в выбранном поле имеют числовой формат, то Excel назначит суммирование по умолчанию. Если в исходных данных есть хотя бы одна текстовая или пустая ячейка, то вместо суммы будет подсчитываться Количество ячеек. В нашем примере каждая ячейка – это сумма всех соответствующих товаров в соответствующем регионе.
В ячейках сводной таблицы можно использовать и другие способы вычисления. Их около 20 видов (среднее, минимальное значение, доля и т.д.). Изменить способ расчета можно несколькими способами. Самый простой, это нажать правой кнопкой мыши по любой ячейке нужного поля в самой сводной таблице и выбрать другой способ агрегирования.
Область строк – названия строк, которые расположены в крайнем левом столбце. Это все уникальные значения выбранного поля (столбца). В области строк может быть несколько полей, тогда таблица получается многоуровневой. Здесь обычно размещают качественные переменные типа названий продуктов, месяцев, регионов и т.д.
Область столбцов – аналогично строкам показывает уникальные значения выбранного поля, только по столбцам. Названия столбцов – это также обычно качественный признак. Например, годы и месяцы, группы товаров.
Область фильтра – используется, как ясно из названия, для фильтрации. Например, в самом отчете показаны продукты по регионам. Нужно ограничить сводную таблицу какой-то отраслью, определенным периодом или менеджером. Тогда в область фильтров помещают поле фильтрации и там уже в раскрывающемся списке выбирают нужное значение.
С помощью добавления и удаления полей в указанные области вы за считанные секунды сможете настроить любой срез ваших данных, какой пожелаете.
Посмотрим, как это работает в действии. Создадим пока такую же таблицу, как уже была создана с помощью функции СУММЕСЛИМН. Для этого перетащим в область Значения поле «Выручка», в область Строки перетащим поле «Область» (регион продаж), в Столбцы – «Товар».
В результате мы получаем настоящую сводную таблицу.
На ее построение потребовалось буквально 5-10 секунд.
Работа со сводными таблицами в Excel
Изменить существующую сводную таблицу также легко. Посмотрим, как пожелания директора легко воплощаются в реальность.
Заменим выручку на прибыль.
Товары и области меняются местами также перетягиванием мыши.
Для фильтрации сводных таблиц есть несколько инструментов. В данном случае просто поместим поле «Менеджер» в область фильтров.
На все про все ушло несколько секунд. Вот, как работать со сводными таблицами. Конечно, не все задачи столь тривиальные. Бывают и такие, что необходимо использовать более замысловатый способ агрегации, добавлять вычисляемые поля, условное форматирование и т.д. Но об этом в другой раз.
Источник данных сводной таблицы Excel
Для успешной работы со сводными таблицами исходные данные должны отвечать ряду требований. Обязательным условием является наличие названий над каждым полем (столбцом), по которым эти поля будут идентифицироваться. Теперь полезные советы.
1. Лучший формат для данных – это Таблица Excel. Она хороша тем, что у каждого поля есть наименование и при добавлении новых строк они автоматически включаются в сводную таблицу.
2. Избегайте повторения групп в виде столбцов. Например, все даты должны находиться в одном поле, а не разбиты по месяцам в отдельных столбцах.
3. Уберите пропуски и пустые ячейки иначе данная строка может выпасть из анализа.
4. Применяйте правильное форматирование к полям. Числа должны быть в числовом формате, даты должны быть датой. Иначе возникнут проблемы при группировке и математической обработке. Но здесь эксель вам поможет, т.к. сам неплохо определяет формат данных.
В целом требований немного, но их следует знать.
Обновление данных в сводной таблице Excel
Если внести изменения в источник (например, добавить новые строки), сводная таблица не изменится, пока вы ее не обновите через правую кнопку мыши
или
через команду во вкладке Данные – Обновить все.
Так сделано специально из-за того, что сводная таблица занимает много места в оперативной памяти. Чтобы расходовать ресурсы компьютера более экономно, работа идет не напрямую с источником, а с кэшем, где находится моментальный снимок исходных данных.
Зная, как делать сводные таблицы в Excel даже на таком базовом уровне, вы сможете в разы увеличить скорость и качество обработки больших массивов данных.
Ниже находится видеоурок о том, как в Excel создать простую сводную таблицу.
Сводные таблицы в Excel
history 10 апреля 2013 г.
Сводные таблицы ( Вставка/ Таблицы/ Сводная таблица ) могут пригодиться, если одновременно выполняются следующие условия:
Подготовка исходной таблицы
Начнем с требований к исходной таблице.
Вместо того, чтобы плодить повторяющиеся столбцы ( регион 1, регион 2, … ), в которых будут в изобилии незаполненные ячейки, переосмыслите структуру таблицы, как показано на рисунке выше (Все значения объемов продаж должны быть в одном столбце, а не размазаны по нескольким столбцам. Для того, чтобы это реализовать, возможно, потребуется вести более подробные записи (см. рисунок выше), а не указывать для каждого региона суммарные продажи).
Создание таблицы в формате EXCEL 2007 добавляет новые возможности:
В качестве исходной будем использовать таблицу в формате EXCEL 2007 содержащую информацию о продажах партий продуктов. В строках таблицы приведены данные о поставке партии продукта и его сбыте.
В таблице имеются столбцы:
Через Диспетчер имен ( Формулы/ Определенные имена/ Диспетчер имен ) откорректируем Имя таблицы на « Исходная_таблица ».
Создание Сводной таблицы
Сводную таблицу будем создавать для решения следующей задачи: «Подсчитать суммарные объемы продаж по каждому Товару».
В появившемся окне нажмем ОК, согласившись с тем, что Сводная таблица будет размещена на отдельном листе.
На отдельном листе появится заготовка Сводной таблицы и Список полей, размещенный справа от листа (отображается только когда активная ячейка находится в диапазоне ячеек Сводной таблицы).
Структура Сводной таблицы в общем виде может быть представлена так:
Т.к. ячейки столбца Товар имеют текстовый формат, то они автоматически попадут в область Названия строк Списка полей. Разумеется, поле Товар можно при необходимости переместить в другую область Списка полей. Заметьте, что названия Товаров будут автоматически отсортированы от А до Я (об изменении порядка сортировки читайте ниже ).
Теперь поставим галочку в Списке полей у поля Продажи.
Т.к. ячейки столбца Продажи имеют числовой формат, то они автоматически попадут в раздел Списка полей Значения.
Несколькими кликами мыши (точнее шестью) мы создали отчет о Продажах по каждому Товару. Того же результата можно было достичь с использованием формул (см. статью Отбор уникальных значений с суммированием по соседнему столбцу ). Если требуется, например, определить объемы продаж по каждому Поставщику, то для этого снимем галочку в Списке полей у поля Товар и поставим галочку у поля Поставщик.
Детализация данных Сводной таблицы
Обновление Сводной таблицы
Удаление Сводной таблицы
Изменение функции итогов
Изменение порядка сортировки
Теперь предположим, что Товар Баранки – наиболее важный товар, поэтому его нужно выводить в первой строке. Для этого выделите ячейку со значением Баранки и установите курсор на границу ячейки (курсор должен принять вид креста со стрелками).
Затем, нажав левую клавишу мыши, перетащите ячейку на самую верхнюю позицию в списке прямо под заголовок столбца.
После того как будет отпущена клавиша мыши, значение Баранки будет перемещено на самую верхнюю позицию в списке.
Изменение формата числовых значений
Теперь добавим разделитель групп разрядов у числовых значений (поле Продажи). Для этого выделите любое значение в поле Продажи, вызовите правой клавишей мыши контекстное меню и выберите пункт меню Числовой формат …
Добавление новых полей
Предположим, что необходимо подготовить отчет о продажах Товаров, но с разбивкой по Регионам продажи. Для этого добавим поле Регион продажи, поставив соответствующую галочку в Списке полей. Поле Регион продажи будет добавлено в область Названия строк Списка полей (к полю Товар). Поменяв в области Названия строк Списка полей порядок следования полей Товар и Регион продажи, получим следующий результат.
Добавление столбцов
Добавление поля Регион продажи в область строк привело к тому, что Сводная таблица развернулась на 144 строки. Это не всегда удобно. Т.к. продажи осуществлялись только в 6 регионах, то поле Регион продажи имеет смысл разместить в области столбцов.
Сводная таблица примет следующий вид.
Меняем столбцы местами
Чтобы изменить порядок следования столбцов нужно взявшись за заголовок столбца в Сводной таблице перетащить его в нужное место.
Удаление полей
Любое поле можно удалить из Сводной таблицы. Для этого нужно навести на него курсор мыши в Списке полей (в областях Фильтр отчета, Названия отчета, Названия столбцов, Значения), нажать левую клавишу мыши и перетащить удаляемое поле за границу Списка полей.
Другой способ – снять галочку напротив удаляемого поля в верхней части Списка полей. Но, в этом случае поле будет удалено сразу из всех областей Списка полей (если оно использовалось в нескольких областях).
Добавление фильтра
Предположим, что необходимо подготовить отчет о продажах Групп Товаров, причем его нужно сделать в 2-х вариантах: один для партий Товаров принесших прибыль, другой – для убыточных. Для этого:
Вид получившейся Сводной таблицы должен быть таким:
Теперь воспользовавшись Выпадающим (раскрывающимся) списком в ячейке B1 (поле Прибыль) можно, например, построить отчет о продажах Групп Товаров, принесших прибыль.
После нажатия кнопки ОК будут выведены значения Продаж только прибыльных Партий.
Обратите внимание, что в Списке полей Сводной таблицы напротив поля Прибыль появился значок фильтра. Удалить фильтр можно сняв галочку в Списке полей.
Также стандартный механизм фильтрации данных доступен через выпадающий список в заголовках строк и столбцов Сводной таблицы.
Несколько итогов для одного поля
Предположим, что требуется подсчитать количество проданных партий и суммарные продажи каждого Товара. Для этого:
Отключаем строки итогов
Группируем числа и Даты
Предположим, что требуется подготовить отчет о сроках сбыта. В результате нужно получить следующую информацию: сколько партий Товара сбывалось в период от 1 до 10 дней, в период 11-20 дней и т.д. Для этого:
Теперь Сводная таблица показывает сколько партий Товара сбывалось за 5, 6, 7, … дней. Всего 66 строк. Сгруппируем значения с шагом 10. Для этого:
Теперь Сводная таблица показывает сколько партий Товара сбывалось в период от 1 до 10 дней, в период 11-20 дней и т.д.
Аналогичную группировку можно провести по полю Дата поставки. В этом случае окно Группировка по полю будет выглядеть так:
Теперь Сводная таблица показывает, сколько партий Товара поставлялось каждый месяц.
Условное форматирование ячеек Сводной таблицы
К ячейкам Сводной таблицы можно применить правила Условного форматирования как и к ячейкам обычного диапазона. Выделим, например, ячейки с 10 наибольшими объемами продаж. Для этого:
ВСЁ о сводных таблицах с нуля за полчаса
На позапрошлой неделе снимал получасовое видео о сводных таблицах в Excel. Тогда не решился выкладывать его на Пикабу, так как подумал, что плохо зайдёт (переводить все 30 минут видео в формат поста точно не охота, а закинуть лишь видео не решился, так как думал, что такой пост не будет особо хорошо принят). Тем не менее, видео-продолжение о построении дашбордов зашло на ура, так что сейчас также делюсь видеоуроком о всех особенностях работы со сводными таблицами с нуля:
В видео на наглядных примерах рассмотрено абсолютно всё самое важное и часто используемое, что может понадобиться – начиная от лучшей формы подготовки данных для сводных таблиц в виде умных таблиц, вплоть до структурирования и форматирования сводных таблиц и их управления срезами. Файл из видео можно скачать, чтобы также поупражняться.
MS, Libreoffice & Google docs
471 пост 12.8K подписчиков
Правила сообщества
2. Публиковать посты соответствующие тематике сообщества
3. Проявлять уважение к пользователям
4. Не допускается публикация постов с вопросами, ответы на которые легко найти с помощью любого поискового сайта.
По интересующим вопросам можно обратиться к автору поста схожей тематики, либо к пользователям в комментариях
Есть три стадии принятия эксель:
1. Тупой эксель, не понимает что мне нужно
2. Удобный эксель, легко растягивать столбики
3. Тупой я, на экселе можно запустить майнкрафт
Ну наконец-то! А то я вчера смотрела видео про дашборд и там было упоминание, что надо посмотреть предыдущий пост про сводные. Не поленилась пролистать в аккаунте у Вас несколько страниц с постами и не нашла. Спасибо, в общем!
А можете подсказать, почему при определённой компановке значения встают в шахматном порядке? Как это убрать?
Извините, мы ещё лаптем кашу едим, а где можно скачать файл из видео для упражнения?
Вот если бы бабл диаграммы на сводных со срезами сделал, я бы снял шляпу, а это ещё уровень, входящий в «уверенный пользователь Microsoft office». Если что, это возможно в excel(через костыли) ну и полноценно в power BI
Когда ты не умеешь работать в Excel
Фото сделано в маршрутке
Не единожды видел, как помогали по таким вопросам. Надеюсь и мне кто-то сможет 🙂
Есть простая гугл-ексель таблица. В ней 5 вкладок. Необходимо, что бы другие люди имели возможность смотреть-редактировать только свою вкладку. А я, соответственно, имел доступ ко всем.
Визуализация гистограммы фигурами и рисунками в Excel
Мы попытаемся несколько освежить их внешний вид и сделать его более приятным для восприятия и чтения информации. Мы уже разбирали как можно построить прогресс-бары (линейный и круговой), такие графики тоже могут отлично подойти для создания дашборда и отображения KPI.
Различные фигуры (прямоугольники с закругленными краями, полукруги, стрелки) и рисунки отлично помогут нам в создании и визуализации таких графиков, и в целом немного коснемся темы инфографики.
Как перевести данные в тысячи/миллионы/… в Excel?
Сегодня обсудим способы перевода числовых данных в тысячи, миллионы, миллиарды и другие порядки в Excel для сокращения записей (например, как число вида 123 456 перевести в 123,4 тыс. или 987 654 321 в 988 млн.).
Мы можем изменить вид числа как с помощью применения формул (делением содержимого ячейки на требуемый делитель), так и сделать перевод числа с помощью настройки формата отображения ячейки.
Также научимся добавлять различные подписи к записи, к примеру, «тыс. руб.» для перевода сумм в тысячи рублей и другие подобные подписи.
Построение радиальной гистограммы в Excel | Радиальная диаграмма
Чем же особенна радиальная гистограмма?
В общем и целом это гистограмма построенная в радиальной системе координат (всем нам привычная классическая линейная гистограмма использует евклидову геометрию). Грубо говоря мы берем обычную гистограмму и сворачиваем ее в круг, и сегодня мы как раз будем учиться как можно построить радиальную диаграмму.
Умная таблица в Excel | Как сделать умную таблицу?
Разберем как сделать умную таблицу в Excel (т.е. преобразовать обычную таблицу в умную), как сделать обратное действие (т.е. уже снаоборот, убрать умную таблицу и преобразовать ее в обычную), в принципе поймем какие свойства появляются при работе с умными таблицами и прочие полезные приемы.
Количество и сумма ячеек по цвету в Excel
Сегодня поговорим про простой способ как посчитать количество, и как суммировать ячейки по цвету в Excel.
С помощью пользовательских функций реализуем суммирование по цвету заливки ячейки (или по цвету текста), а также разберем аналогичный пример и для подсчета количества ячеек одинакового цвета.
Тайм-коды для удобства навигации
Лайфхаки с Excel
1. Как восстановить файл, который вы не сохранили:
Чтобы выделить все ячейки, не нужно долго прокручивать мышкой. Достаточно будет нажать Ctrl + A или специальную кнопку в углу листа.
3. Как превратить строки в столбцы и наоборот:
Фишки Excel
1. Как быстро объединить текст из нескольких ячеек:
Нажмите на ячейку, в которой вы хотите увидеть результат, и вбейте туда знак «=». После этого выделите ячейки, которые хотите сложить, ставя перед каждой символ «&».
Как перейти к нужному листу
Все, кто работает с Excel ежедневно, знают, как трудно бывает найти нужный лист, особенно если их количество перевалило за 10. Для быстрого перемещения кликните правой кнопкой по кнопке прокрутки (она находится в нижней левой части экрана). Теперь вы можете перейти на любой нужный лист мгновенно.
3. Как создать маленькие диаграммы (спарклайны):
В Excel есть прикольная функция, которая позволяет вывести в столбце маленькую диаграмму, отображающую положение дел в других ячейках. Благодаря этой штуке вы наглядно можете показать клиенту или руководителю, что все ваши труды не напрасны и за этот год видны улучшения. Нажмите Вставка, перейдите в группу Спарклайны и выберите опцию График или Гистограмма (что больше нравится).
Диаграмма в Эксель
Пять первых шагов к освоению программы Excel.
Базовый видеокурс по работе в программе Эксель.
— как на основе таблицы создать диаграмму
— как вывести книгу Эксель на печать
Создание сводной таблицы в Excel. Реальная задача
В этом видео рассмотрим создание сводной таблицы в Excel на примере реальной таблицы, которую мне прислал подписчик.
10 Трюков в Excel
В этом видео, я рассмотрел 10 трюков в Excel:
Формулы Excel – все особенности, тонкости и трюки в одном видео!
В сегодняшнем видео мы за тридцать минут рассмотрим все самые важные основы работы с формулами и функциями в Excel. Узнав и практикуя все советы из этого видео, ты сможешь вывести свой уровень работы с формулами в Excel на абсолютно новый, по-настоящему профессиональный уровень.
В этом видео мы рассмотрим как самые основы работы с формулами (например, относительность и абсолютность ссылок, использование смешанных ссылок, прописывание значений для формул, принцип их вычисления формул и т.д.), так и советы для уже продвинутых пользователей (такие, как, например, использование шорткатов и комбинаций клавиш, в том числе мнемоников, как быстро выбирать доступные функции и т.д.). Кроме того, мы рассмотрим, что из себя представляет специальная вставка при копировании, какие варианты специальной вставки существуют и в каких ситуациях их использовать.