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

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

  • Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
  • Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
  • Инструменты слияния : Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
  • Разделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
  • Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электронные письма нескольким получателям массово.
  • Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
  • Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.

стрелка синий правый пузырь Преобразовать сводную таблицу в список

Популярные

Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!

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

Док-сводная таблица-к-списку-1

1. Щелкните любую ячейку в сводной таблице и перейдите к Дизайн вкладку и щелкните подытоги > Не показывать промежуточные итоги.

Док-сводная таблица-к-списку-2

2. Нажмите Общие итоги > Отключено для строк и столбцов под Дизайн таб. Смотрите скриншот:

Док-сводная таблица-к-списку-3

3. Нажмите Макет отчета > Повторить все метки предметов под Дизайн таб. См. Снимок экрана:

Док-сводная таблица-к-списку-4

4. Нажмите Макет отчета снова и щелкните Показать в табличной форме. Смотрите скриншот:

Док-сводная таблица-к-списку-5

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

Док-сводная таблица-к-списку-6

5. Нажмите Опции вкладку (или Анализировать вкладка) и снимите флажок Кнопки и Заголовки полей в Показать группа.

Док-сводная таблица-к-списку-7

Теперь сводная таблица, показанная ниже:

Док-сводная таблица-к-списку-8

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

Док-сводная таблица-к-списку-9

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

6. Выберите сводную таблицу и нажмите Ctrl + C тем временем, чтобы скопировать его, затем поместите курсор в ячейку, в которую вы хотите вставить сводную таблицу в виде списка, и щелкните правой кнопкой мыши, чтобы выбрать Специальная вставка > Значение (V). Смотрите скриншот:

Док-сводная таблица-к-списку-10

Внимание: В Excel 2007 вам нужно щелкнуть Главная > макаронные изделия > Вставить значения , чтобы вставить сводную таблицу как список.

Копирование числовых ячеек из 1С в Excel Промо

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

15.01.2019 32853 itriot11 27

Пример создания сводной таблицы — из меню «Вставка» жмем кнопку «Сводная таблица»

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

  1. Активное поле — смена имени сводной таблицы.
  2. Группировать — создание группы, содержащей выделенные элементы, группировка полей с числами или датами.
  3. Сортировка — открытие диалогового окна сортировки, в котором можно выполнить сортировку данных сразу по нескольким условиям.
  4. Данные — обновление и изменение исходных данных.
  5. Действия — удаление полей, форматирования и фильтров; выбор элемента, перемещение сводной таблицы в другое место книги.
  6. Сервис — вставка сводной диаграммы, формул, подключение к источникам данных OLAP.
  7. Показать или скрыть — отображение или скрытие списка полей, кнопок «плюс» и «минус», заголовка полей.

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

Рассмотрим инструменты, которые находятся в этом макете:

  1. «Название столбцов» и «название строк», куда выкладываются поля, по которым будет происходить детализация.
  2. В поле «Значение» указывается то поле, по которому необходимо производить итоги.
  3. Поле «Фильтр отчета» позволяет выбрать из набора не все сведения, а только те, что удовлетворяют заданным условиям.

Пример добавления полей для отображения в отчете сводной таблицы экселя

Пример добавления полей для отображения в отчете сводной таблицы экселя

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

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

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

Анализ

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

Рассмотрим каждую из них более детально.

Сводная таблица

Нажав на кнопку, отмеченную на скриншоте, вы сможете сделать следующие действия:

  • изменить имя;

  • вызвать окно настроек.

В окне параметров вы увидите много чего интересного.

Активное поле

При помощи этого инструмента можно сделать следующее:

  1. Для начала нужно выделить какую-нибудь ячейку. Затем нажмите на кнопку «Активное поле». В появившемся меню кликните на пункт «Параметры поля».

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

  1. Помимо этого, можно настроить числовой формат. Для этого нужно нажать на соответствующую кнопку.

  1. В результате появится окно «Формат ячеек».

Здесь вы сможете указать, в каком именно виде нужно выводить результат анализа информации.

Группировать

Благодаря этому инструменту вы можете настроить группировку по выделенным значениям.

Вставить срез

Редактор Microsoft Excel позволяет создавать интерактивные сводные таблицы. При этом ничего сложного делать не нужно.

  1. Выделите какой-нибудь столбец. Затем нажмите на кнопку «Вставить срез».
  2. В появившемся окне, в качестве примера, выберите одно из предложенных полей (в будущем вы можете выделять их в неограниченном количестве). После того как что-нибудь будет выбрано, сразу же активируется кнопка «OK». Нажмите на неё.

  1. В результате появится небольшое окошко, которое можно перемещать куда угодно. В нем будут предложены все возможные уникальные значения, которые есть в данном поле. Благодаря этому инструменту вы сможете выводить сумму лишь за определенные месяцы (в данном случае). По умолчанию выводится информация за всё время.
Читайте также:  Работа со сводными таблицами в Excel на примерах

  1. Можно кликнуть на любой из пунктов. Сразу после этого в поле сумма изменятся все значения.

  1. Таким образом получится выбрать любой промежуток времени.

Чтобы увидеть видео включите в браузере GIF-Анимацию.

Хотите добавить группировку по Поставщику, перетащите его в «СТРОКИ» и поставьте перед Категорией или после… Где хотите. Хотите убрать какое-то поле, просто снимите галочку.

Комментарии

Владислав 12.01.2018 12:53 Спасибо за статью!
Ответить | Ответить с цитатой | Цитировать

Обновить список комментариев

Как расставить поля

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

Фильтровать данные по выбранному диапазону мы будем по продавцам. То есть — выберем продавца, а в таблице отобразятся проданные им товары. Зажимаем левой кнопкой мыши поле «Продавец» и перетягиваем его в область «Фильтр отчета» . Таблица изменилась, а добавленное поле теперь отмечено галочкой.

В качестве строк выберем «Товары» . Аналогичным образом перетягиваем нужное поле в область «Названия строк» .

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

Если бы у нас в исходной таблице был столбец «Единицы» . То данная сводная таблица показывала бы, какой продавец, в каком месяце, сколько продал единиц товара по определенной цене.

В область «Названия столбцов» перетянем поле «Дата» . Чтобы отобразить продажи не за каждый день, а, к примеру, по месяцам, кликните по любой дате правой кнопкой мыши и выберите из меню «Группировать» .

Дальше выбираем, с какой по какую дату хотим выполнить группировку, и с каким шагом.

Таблица примет следующий вид.

Теперь в область «Значения» перетянем поле «Сумма» .

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

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

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

Похожие статьи

Эффективный пользовательский интерфейс. Предоставление.

сводная таблица с диапазоном допустимых значений для анализа региона с превышением ошибок (рис. 6)

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

Диаграммы в MS Excel 2013 | Статья в сборнике международной.

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

Основные термины (генерируются автоматически): диаграмма, комбинированная диаграмма, тип диаграммы, ряд данных, правая кнопка мыши, построение диаграммы, область данных, контекстное меню.

Технология развития критического мышления как средство.

Таблица 6. Пример «сводной таблицы». Причастие.

Таким образом, это стихотворение, состоящее из 5 строк.

Основные термины (генерируются автоматически): русский язык, стадий вызова, прием, таблица, информация, признак предмета, критическое мышление.

Установление половой принадлежности черепа человека по.

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

Таблица 4. Основные качественные признаки черепа №16.

Учебный элемент по теме: «Построение диаграмм различных. »

– оформлять диаграммы (создавать название, как самой диаграммы, так и названия оси категорий и оси значений)

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

Применение панельных данных в эконометрическом анализе.

Сводная таблица моделей для панельных данных.

Также модель с фиксированными эффектами объясняет 99,7 % колебаний товарооборота вокруг своего среднего значения.

Особенности вычисления временных интервалов в Excel

Значения кодов приведены в таблице 2. Таблица 2.

Сводные таблицы в MS Excel 2013 | Статья в сборнике. Для данных типа дата создана автоматическая группировка по секундам, минутам, часам, дням, неделям, месяцам, кварталам и годам.

Исследование моды и медианы результатов ЕГЭ по математике

Медиана — это значение признака, которое делит все данные на две равные части: половина единиц совокупности имеет

Таблица 1. Данные о результатах сдачи ЕГЭ по математике по 100-бальной шкале.

URL: www.school-collection.edu.ru/catalog (Дата обращения 25.05.2017).

Анализ и устранение ошибок при подготовке БД к подсчету.

пропуск отдельных значений или строк; повторные значения или строки.

После проверки итоговая таблица сортируется по полям (HOLEID) и (FROM), а данные анализируются на присутствие в опробовании перекрывающихся интервалов или их отсутствие.

Сводная таблица в excel как сделать

Первым делом разберём версию 2007 года. У некоторых пользователей ещё осталась старая версия редактора. Для начала нужно создать таблицу для построения отчёта. В ней не должно быть пустых ячеек и строк. Разберём процесс создания отчёта на самом простом примере:

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

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

Сводные таблицы в excel 2010 и 2013 года – пример работы в excel

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

Теперь можно делать отчёт в экселе:

Сводная таблица Excel – учимся создавать

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

Что можно делать с листом:

  • Вам будет доступна панель инструментов для управления значениями. Все поля и их заголовки будут перечислены во вкладке «Выберите поля для добавления в отчёт». Для добавления нужных пунктов необходимо выбрать нужные поля и список будет создан.
  • Есть возможность управлять расположением полей. Вы можете перетаскивать столбцы в нужные окна, а также сортировать в удобном для вас порядке. Также можно отфильтровать пункты. Для этого нужно перетащить пункт в окно «Фильтр». Если вам необходимо произвести расчёт по отдельному полю, то перетащите его в окно «Значение».
  • Вы можете воспользоваться дополнительными опциями. Найти их можно по следующему принципу: «Параметры» ► «Работа со сводными таблицами». Там очень много инструментов, которые имеют большое количество настроек и дополнительных функций.

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

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

Теперь можно перейти и к самому процессу создания сводного отчёта при помощи мастера:

Сводные таблицы в MS Excel – пошаговая инструкция

  1. Запускаем мастера из панели быстрого доступа. Откроется окно, в котором нужно установить переключатель на нужный вам источник данных. Их несколько:
    • Первый – это источник, который находится в списке или в базе данных эксель. В качестве источника будет использоваться база данных рабочего листа, если она создана.
    • Второй – это внешний источник данных. Если будет подключена другая база данных, то её можно использовать.
    • Третий – это источник, который находится в разных консолидациях. Этот пункт выбирается, если нужно сделать объединение разных источников.
    • Четвёртый – это источник, который находится в другой таблице или диаграмме. Вы выбираете уже созданную в Экселе сводную таблицу в качестве источника.
  2. После того как вы определились с источником для отчёта нужно выбрать его вид. Это может быть только таблица или диаграмма с таблицей.
Читайте также:  Как восстановить в Excel поврежденный файл или несохраненный

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

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

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

Чтобы не запутаться в диапазонах, им нужно давать имена. Для этого нужно выделить один из диапазонов списка и указать число полей для него, после чего каждому полю даёте имя. И так со всеми диапазонами.

Теперь нужно нажать на «Далее». Выбираете местоположение отчёта. Нажимаете «Готово».

Сводные таблицы excel 2010 из нескольких листов

Если информация в отчётах однотипная, то легче всего воспользоваться мастером сводных таблиц. Если отчёт имеет сложную структуру, то действовать нужно по-другому. Например, есть две таблицы, одна содержит приход товара, а другая – количество проданного товара в разных магазинах. Главное, чтобы были одинаковые заголовки, меньше времени будете переносить их в один отчёт. В этом случае отчёт делается следующем образом:

Cводная таблица Эксель – что это такое

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

Теперь нужно создать сводный отчёт:

  • Вставка.
  • Сводный отчёт.
  • Вписываем диапазон и место.
  • Ок.

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

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

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

Для этого нам понадобится мастер сводных таблиц. Добавим его на панель быстрого доступа (самый верх окна – слева). Нажмите выпадающую стрелочку и выберите «Другие команды».

Сводные таблицы в Excel специально для чайников

Выберите все команды.

Сводные таблицы в Excel специально для чайников

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

Сводные таблицы в Excel специально для чайников

Значок появится сверху.

Сводные таблицы в Excel специально для чайников

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

Сводные таблицы в Excel специально для чайников

Нам нужно несколько полей, а не одно.

Сводные таблицы в Excel специально для чайников

На следующем этапе выделите первый диапазон и нажмите кнопку «Добавить». Затем переключитесь на другой лист ( щелкните по его названию внизу) и снова «Добавить». У вас будут созданы два диапазона.

Сводные таблицы в Excel специально для чайников

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

Дайте имя каждому. Кликайте кружочек 1, затем в поле вписывайте «май», кликайте кружочек 2 и вписывайте в поле 2 «июнь». Не забывайте менять диапазоны в области. Должен быть выделен тот, который именуем.

Сводные таблицы в Excel специально для чайников

Щелкайте «Далее» и создавайте на новом листе.

Сводные таблицы в Excel специально для чайников

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

Сводные таблицы в Excel специально для чайников

Обратите внимание, что у нас уже нет четких названий полей. Их можно вытащить, нажав на пункты в верхней области.

Сводные таблицы в Excel специально для чайников

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

Сводные таблицы в Excel специально для чайников

Как видите, у нас одно значение в соответствующей области.

Конструктор

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

  • промежуточные итоги:
    • не показывать;
    • показывать все итоги в нижней части;
    • показывать все итоги в заголовке.
    • общие итоги:
      • отключить для строк и столбцов;
      • включить для строк и столбцов;
      • включить только для строк;
      • включить только для столбцов.
      • макет отчета:
        • показать в сжатой форме;
        • показать в форме структуры;
        • показать в табличной форме;
        • повторять все подписи элементов;
        • не повторять подписи элементов.
        • пустые строки:
          • вставить пустую строку после каждого элемента;
          • удалить пустую строку после каждого элемента.
          • параметры стилей сводной таблицы (здесь можно включить/выключить каждый пункт):
            • заголовки строк;
            • заголовки столбцов;
            • чередующиеся строки;
            • чередующиеся столбцы.
            • настроить стиль оформления элементов.

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

            Сразу после этого появится огромный список. Можете выбрать что угодно. При наведении на каждый из шаблонов ваша таблица будет меняться (это сделано для предварительного просмотра). Изменения не вступят в силу, пока вы не кликните на что-нибудь из предложенных вариантов.

            Помимо этого, при желании, вы можете создать свой собственный стиль оформления.

            Для чего в Excel нужны сводные таблицы

            В оригинале этот термин звучит как Pivot Table, а перевод фразы несколько туманен: «таблица, которую можно менять, крутить, демонстрировать в разных проекциях». Что это означает? Интерпретация табличных данных – задача непростая. Обычно это делается с помощью формул, получая итоговые результаты в конце таблицы.

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

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

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

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

            Как мы поступаем? Создаём макет новой таблицы, в шапке которой указываем весь ассортимент реализуемых товаров, а в строках – наименования регионов. Для этого копируем из исходной таблицы столбец с товарами, удалив дубликаты. Используя режим специальной вставки, транспонируем этот столбец в шапку сводной страницы. Аналогичным образом поступаем с регионами, но, поскольку они располагаются в сводной таблице по вертикали, просто копируем без транспонирования.

            В каждую ячейку суммируем выручку, используя функцию СУММЕСЛИМН. Наконец, внизу добавляем итоговые результаты и отправляем отчёт руководителю. Руководитель просит регионы расположить вверху, а товары – по строкам. Вы делаете новый отчёт за 5 минут, и получаете новое задание: предоставить данные по прибыли, а не выручке, то есть с учётом затрат. Что ж, немного изменяем формулу, и опять результат готов за считанные минуты. Наконец, поступает просьба подготовить такие отчёты по каждому продавцу, чтобы оценить их эффективность. И это для сводной таблицы не проблема, если знать, где располагаются нужные исходные данные!

            Функция СУММЕСЛИМН в Excel

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

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

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

            Процедуры построения сводных таблиц в LibreOffice и OpenOffice совершенно одинаковые, в связи с чем нет смысла описывать все этапы по отдельности. Поэтому в данном случае в качестве примера возьмем LibreOficce, но в OpenOffice с помощью совершенно идентичных действий вы можете создать такую же сводную таблицу.

            Для создания сводной таблицы в меню «Файл» LibreOffice выберите пункт «Открыть», после чего укажите скачанный ранее файл pivotTableDB.csv.

            В диалоговом окне «Импорт текста» установите переключатель «Параметры разделителя» в положение «Разделитель», и в качестве разделителя установите «Точка с запятой».

            Импорт текста

            После нажатия ОК необходимая таблица данных будет загружена в документ. Теперь, когда у вас есть база данных, необходимо выделить ее нажатием Ctrl+A, и в в группе «Сводная таблица» (меню «Данные») нажать кнопку «Создать».

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

            В качестве источника в диалоговом окне «Выбрать источник» устанавливаем переключатель в положение «Выбранное выделение». Нажимаем ОК.

            Устанавливаем переключатель в положение «Выбранное выделение»

            Далее появится диалоговое окно «Разметка сводной таблицы». Оно и является своеобразным конструктором сводной табицы из приведенных выше примеров с Microsoft Excel и Google Spreadsheets.

            Чтобы построить сводную таблицу, аналогичную двум предыдущим примерам, сделайте следующие действия.

            В область «Поля страниц» перетащите поле User type, так как в LibreOffice область «Поля страниц» является областью фильтров сводной таблицы.

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

            В область «Поля строк» перенесите сначала поле Week, после чего под этим полем расположите поле Date.

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

            Ранее мы в примере с Microsoft Excel устанавливали в качестве дополнительного вычисления «Процент от суммы по строке». Чтобы настроить подобное вычисление в LibreOffice после двойного клика по полю Sessions, расположенного в области данных, в открывшемся диалоговом окне «Поле данных» раскройте меню «Отображаемое значение», установите «Тип: % от строки» и нажмите ОК.

            В открывшемся диалоговом окне «Поле данных» раскройте меню «Отображаемое значение», установите «Тип: % от строки» и нажмите ОК

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

            Быстрое изменение уровня детализации

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

            У вас получится такая сводная таблица:

            Финальная сводная таблица

            Сортировка в сводной таблице Excel

            Немного преобразуем наш сводный отчет: уберем значение по «Поставщикам», добавим «Дату».

            Сделаем таблицу более полезной – сгруппируем даты по кварталам. Для этого щелкнем правой кнопкой мыши по любой ячейке с датой. В выпадающем меню выберем «Группировать». Заполним параметры группировки:

            После нажатия ОК сводная таблица приобретает следующий вид:

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

            Значения в сводном отчете поменяются в соответствии с отсортированными данными:

            Теперь выполним сортировку данных по дате. Правая кнопка мыши – «Сортировка». Можно выбрать способ сортировки и на этом остановиться. Но мы пойдем по другому пути. Нажмем «Дополнительные параметры сортировки». Откроется окно вида:

            Установим параметры сортировки: «Дата по убыванию». Кликнем по кнопке «Дополнительно». Поставим галочку напротив «Автоматической сортировки при каждом обновлении отчета».

            Теперь при появлении в сводной таблице новых дат программа Excel будет сортировать их по убыванию (от новых к старым):

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

            Изменить существующую сводную таблицу также легко. Посмотрим, как пожелания директора легко воплощаются в реальность.

            Заменим выручку на прибыль.

            Товары и области меняются местами также перетягиванием мыши.

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

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

            Зачем нужны сводные таблицы?

            Работу в Excel можно разбить на два этапа: вычисления и форматирование данных. Несмотря на немалое количество встроенных инструментов и формул, которые способствуют выполнению обеих задач, сводные таблицы зачастую предоставляют самый быстрый и эффективный способ расчета и форматирования данных.
            Давайте рассмотрим простой сценарий, демонстрирующий вышесказанное. Вы предоставили начальнику некую информацию о доходах компании за месяц, и он, как и ожидалось, затребовал более подробный отчет. Он добавил в рабочий лист примечание, после чего отправил его вам по электронной почте. Как показано на рис. 1.1, он попросил добавить строку, содержащую сведения о предоставленных за месяц кредитах.

            Рис. 1.1. Как и ожидалось, начальник изменил требования к отчету после получения первого его варианта

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

            Рис. 1.2. Устаревшая система возвращает данные за день, а не за месяц

            А теперь вам предстоит подсчитать общую сумму кредитов за месяц и представить результаты в формате исходного отчета. Окончательный фрагмент данных должен выглядеть примерно так, как показано на рис. 1.3. Если бы вы создавали фрагмент данных вручную, вам потребовалось бы 18 раз щелкнуть кнопкой мыши и три раза нажать разные клавиши:

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

            Рис. 1.3. Ваша цель состоит в получении итоговых сведений за месяц и транспонировании их в горизонтальный вид

            Получение необходимого фрагмента данных с помощью сводной таблицы требует всего лишь 9 щелчков мышью:

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

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

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

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

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

Ссылка на основную публикацию