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

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

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

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

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

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

На основании таблицы, в которой изображены продажи товаров за несколько лет,

Таблица на листе в Excel

построена сводная таблица.

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

Добавление вспомогательного столбца в набор данных

Примечание. Если вы используете Excel 2013 и более поздние версии, пропустите этот метод и перейдите к следующему (вам доступна встроенная функция).

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

Хотя это простой обходной путь, у него есть некоторые недостатки (которые будут рассмотрены далее).

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

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

Добавьте следующую формулу в столбец F и примените ее ко всем ячейкам, в которых есть данные в соседних столбцах.

= ЕСЛИ (СЧЁТЕСЛИМН ($C$2:C2; C2; $B$2:B2; B2) > 1;0;1)

Приведенная выше формула использует функцию СЧЁТЕСЛИМН для подсчета количества раз, когда имя появляется в данном регионе. Также обратите внимание на диапазоны критериев: $C$2:C2 и $B$2:B2. Это означает, что они продолжают расширяться, когда вы идете вниз по столбцу.

Например, в ячейке F2 диапазон критериев составляет $C$2:C2 и $B$2:B2, а в ячейке F3 эти диапазоны расширяются до $C$3:C3 и $B$3:B3.

Это гарантирует, что функция СЧЁТЕСЛИМН считает первый экземпляр имени как 1, второй экземпляр имени как 2 и так далее.

Поскольку мы хотим получить только разные имена, используется функция ЕСЛИ, которая возвращает 1, когда имя появляется для региона в первый раз, и возвращает 0, когда оно появляется снова. Это гарантирует, что учитываются только разные имена, а не повторы.

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

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

Ниже приведены шаги, как сделать это:

  • Выберите любую ячейку в таблице.
  • Нажмите вкладку «Вставка».
  • Нажмите на кнопку Сводная таблица.
  • В диалоговом окне «Создание сводной таблицы» убедитесь, что таблица / диапазон указаны правильно (и включает вспомогательный столбец), и выбран «На новый лист» в качестве места размещения.
  • Нажмите ОК.

Вышеуказанные шаги вставят новый лист со сводной таблицей.

Перетащите поле «Регион» в область «Строки» и поле «Помощник» в область «Значения».

Вы получите вот такую сводную таблицу:

Теперь вы можете изменить заголовок столбца с «Сумма по полю Помощник» на «Количество сотрудников».

Изменение функции итогов

При создании Сводной таблицы сгруппированные значения по умолчанию суммируются. Действительно, при решении задачи нахождения объемов продаж по каждому Товару, мы не заботились о функции итогов – все Продажи, относящиеся к одному Товару были просуммированы. Если требуется, например, подсчитать количество проданных партий каждого Товара, то нужно изменить функцию итогов. Для этого в Сводной таблице выделите любое значение поля Продажи, вызовите правой клавишей мыши контекстное меню и выберите пункт Итоги по/ Количество .

Изменение порядка сортировки

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

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

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

После того как будет отпущена клавиша мыши, значение Баранки будет перемещено на самую верхнюю позицию в списке.

Изменение формата числовых значений

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

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

Удаление полей

Любое поле можно удалить из Сводной таблицы. Для этого нужно навести на него курсор мыши в Списке полей (в областях Фильтр отчета, Названия отчета, Названия столбцов, Значения), нажать левую клавишу мыши и перетащить удаляемое поле за границу Списка полей.

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

Бедные таблицы. Ну что с ними не так?

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

Вот пример, который я называю таблицей всех таблиц: Гармонизированный тариф США, занимающий более чем 3550 страниц. В нём содержится описание каждого товара, который может быть импортирован в США, включая такие захватывающие позиции, как «Мужские и детские пальто, полупальто, накидки, куртки, анораки (включая лыжные костюмы), ветровки и подобные товары (включая утеплённые жилеты)».

Так что же такое полупальто?

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

Давайте посмотрим, как делать правильно.

Следите за числами

Не все числа одинаковы. Я не имею в виду π (Пи) и ∞ (хотя упоминаю о них на вечеринках), а говорю об отображении цифр, разделяющихся на минускульные или маюскульные, и на пропорциональные или табличные.

Минускульные и маюскульные

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

Разница между пропорциональными и табличными цифрами не столь очевидна:

Пропорциональные и табличные цифры

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

Техническое замечание о табличных маюскульных цифрах

На этапе дизайна стоит убедиться, что используемые цифры вам подходят (табличное и маюскульное выравнивание обычно выключено по умолчанию). Продукты Adobe включают панель «opentype», при помощи которой цифрам задаётся правильная настройка. Чтобы включить соответствующее свойство в CSS, нужно разобраться в слегка запутанном синтаксисе. Как бы то ни было, несколько запросов к гуглу наставят вас на путь истинный.

Теперь плохие новости: табличные маюскульные цифры доступны не для всех шрифтовых гарнитур. Гарнитуры, которые их включают, обычно дороги. Есть несколько исключений: например, прекрасный Work Sans — бесплатный шрифт с табличными маюскульными цифрами.

Если трудно найти подходящую шрифтовую гарнитуру, используйте запасной вариант — моноширинные шрифты. Они выглядят более похожими на код, однако всегда подойдут для отображения чисел в таблице. Стоит добавить, что San Francisco, новый системный шрифт по умолчанию у Apple, содержит табличные маюскульные цифры, хорошо отображающиеся при малых размерах.

Помните о выравнивании

Следуйте 3½ правилам:
1. Числовые данные выравниваются по правой стороне.
2. Текстовые данные выравниваются по левой стороне.
3. Заголовки выравниваются так же, как и их данные.
3½. Не используйте выравнивание по центру.

Список штатов США по историческому населению — Wikipedia

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

Тексты (в русском языке) читаются слева направо. Тексты обычно сравнивают по алфавитному порядку: если две строчки начинаются с одной буквы, сравнивается следующая, и т. д. Попытка быстро пробежаться по тексту, не выровненному по левой стороне, крайне затруднительна.

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

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

Согласующиеся значащие цифры = хорошее выравнивание

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

Лаконичные, понятные подписи

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

Прогноз уровня воды при наводнении в Mississippi — NOAA

Заглавие

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

Единицы измерения

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

Заголовки

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

Как можно меньше чернил

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

Статистика бэттинга в Национальной Лиге 2016 — BaseballReference

Линейки

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

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

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

Фоны

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

Многие элементы можно выделить без использования фонов: подсветку значений, дополнительный контекст к данным, изменения в значениях по сравнению с прошлыми периодами. Воспользуйтесь графическими элементами, например ✻, † (один из моих любимых), или ▵.

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

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

Дальнейшее чтение и материалы для вдохновения

FiveThirtyEight всегда был отличным источником вдохновения — для отображения числовых данных используется гарнитура Decima Mono, которая специально разработана для отображения больших наборов данных на малом пространстве.

Butterick’s Practical Typography — мой справочник по всем вопросам в типографике. Это тот тип ресурсов, на которые у вас куча ссылок — столько там полезных статей!

Ну и наконец, никакая статья по дизайну данных не будет полной без статьи Edward Tufte. Его письмо о дизайне просто незаменимо.

Если вы хотите узнать о других интересных подходах в арифметике, посмотрите как японские дети используют Soroban или как работает умножение по решётке.

Разные значения против уникальных значений

Кажется, что это одно и то же, но это не так.

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

Разница между уникальным и разными значениями

Уникальные значения / имена — это те, которые встречаются только один раз. Это означает, что все имена, которые повторяются и имеют дубликаты, не являются уникальными. Уникальные имена перечислены в столбце D вышеупомянутого набора данных.

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

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

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

Рассмотрим, как это можно сделать.

Шаг 1.

Выделяем сводную таблицу кликом левой кнопки мыши.

Шаг 2.

Перейти во вкладку «Параметры»

Шаг 3.

Кликнуть по ярлыку «Поля, элементы, наборы»

Вставить вычесляемое поле

Шаг 4.

Выбрать пункт «Вычисляемое поле»

Шаг 5.

В открывшемся окне указать имя нового поля.

Имя поля

Шаг 6.

Вставить формулу.

Вставка формулы в поле сводной таблицы

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

Шаг 7.

Нажать кнопку «Ок».

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

Ваш адрес email не будет опубликован. Обязательные поля помечены *