Как использовать 5 продвинутых приёмов для сводных таблиц Excel
Проблема, с которой мы все сталкиваемся — это не отсутствие данных; наоборот — это огромные объемы данных! Именно поэтому я выступаю за использование замечательной функции Excel — Cводные таблицы, чтобы обобщить и проанализировать ваши данные.
Будучи профессионалом в области финансов, я генетически склонен любить электронные таблицы. Но я также обнаружил, что я использую электронные таблицы для организации моей творческой и внештатной работы. Независимо от того, для чего вы используете электронные таблицы, сводная таблица может помочь вам найти больший смысл в данных.
Этот урок, мы построим по нашему начальному уроку по использованию сводных таблиц, для лучшей работы с данными. Я покажу вам пять моих любимых передовых приёмов сводной таблицы.
На протяжении всего этого урока я буду использовать пример данных, предоставленных Microsoft на этой странице. Используйте эти данные, чтобы воссоздать примеры или проверить функции, которые я демонстрирую.
Расширенный фильтр
- ExcelZoo имеет большой обзор приёмов сводной таблицы в их статье, 10 уроков для освоения сводных таблиц (на английском).
- Мы в Envato Tuts+ рассмотрели сводные таблицы с помощью урока для новичков Как создать свою первую сводную таблицу в Microsoft Excel.
- Для более простого введения в Microsoft Excel ознакомьтесь с нашей учебной серией Как сделать и использовать формулы в Excel (Учебный лагерь для начинающих).
В сводной таблице щелкните маленькую стрелку рядом со списком Названия строк или Названия столбцов. Щелкните поле в строке или столбце, которые требуется отсортировать. в списке Названия строк или Названия столбцов, а затем выберите нужный параметр.
Как в сводной таблице сделать фильтр по месяцам?
- Кликните правой кнопкой мыши по левому столбцу сводной таблицы (столбец с датами) и выберите команду Группировать (Group). Появится диалоговое окно Группирование (Grouping) для дат.
- Выберите Месяцы (Month) и нажмите ОК.
- Щелкните правой кнопкой мыши ячейку в поле даты сводной таблицы.
- Выберите настройки поля …
- Нажмите кнопку «Числовой формат».
- Измените форматирование даты в окне «Формат ячеек».
- Нажмите ОК и ОК.
MS Excel: фильтрация данных — NIT for You
- Перетащите указатель вниз по столбцу, чтобы выделить нужные даты.
- На вкладке Главная щелкните стрелку на кнопке Сортировка и фильтр и выберите команду Сортировка от старых к новым или Сортировка от новых к старым.
Перед созданием таблицы убедитесь, что исходные данные представлены верно: столбцы имеют заголовки, числа представлены в числовом формате, а не в текстовом и т.д. В противном случае Вы можете получить неверные результаты.
Перейдите на вкладку «Вставка», далее в разделе «Таблицы» щелкните по пиктограмме «Сводная таблица» либо выберите соответствующий пункт в раскрывающемся списке.
Сводная таблица
Сводные таблицы – одно из наиболее мощных средств Excel по работе с базами данных. Они полезны как для анализа, так и для обобщения информации, хранящейся в БД.
Создадим из нашей БД сводную таблицу для расчета прибыли по каждому товару. Выполним команду Данные → Сводная таблица. В первом диалоговом окне Мастер сводных таблиц (рис. 9.13) необходимо установить переключатель в положение, показывающее, откуда берутся данные для сводной таблицы:
в списке или базе данных MS Excel – если данные берутся с одного рабочего листа
во внешнем источнике данных – если данные берутся из внешней базы данных
в нескольких диапазонах консолидации – если данные берутся с нескольких рабочих листов
в другой сводной таблице – если сводная таблица создается на основании данных другой сводной таблицы.
В этом же диалоговом окне указывается вид создаваемого отчета — сводная таблица или сводная диаграмма.
Рис. 9.13. Первое диалоговое окно Мастер сводных таблиц.
В рассматриваемом примере переключатель устанавливается в положение В списке или базе данных MS Excel.
В поле Диапазон второго диалогового окна Мастер сводных таблиц (рис. 9.14) необходимо указать диапазон, на основании которого строится сводная таблица. В нашем случае: ‘Поставщики товара’!$A$:$I$18.
В третьем диалоговом окне Мастер сводных таблиц (рис. 9.15) необходимо установить переключатель в положение, указывающее, где будет размещена сводная таблица – на новом листе или на уже существующем.
Рис. 9.14. Второе диалоговое окно Мастер сводных таблиц.
Рис. 9.15. Третье диалоговое окно Мастер сводных таблиц.
Структуру сводной таблицы можно создать, воспользовавшись кнопкой Макет…, третьего диалогового окна Мастер сводных таблиц.
Поля БД, на основании которой строится сводная таблица, представлены в окне создания макета в виде кнопок с названием этих полей (рис. 9.16). Перетаскивая их в соответствующие области, пользователь задает необходимую структуру сводной таблицы.
Строка – для использования данных поля, расположенного в этой области, в качестве заголовка строки;
Столбец – для использования данных поля, расположенного в этой области, в качестве заголовков столбцов;
Данные – для суммирования значений поля, расположенного в этой области, в ячейках сводной таблицы;
Страница – для обеспечения возможности вывода данных сводной таблицы, относящихся только к полю, расположенному в этой области.
Рис. 9.16. Диалоговое окно для создания макета сводной таблицы.
Допустимыми операциями подведения итогов являются: сумма, количество значений, среднее арифметическое, максимальное и минимальное значение, произведение, количество чисел, несмешанное и смешанное отклонение, несмешанная и смешанная дисперсия. В нашем примере в качестве допустимой операции выбрана сумма.
Рис. 9.17. Диалоговое окно Вычисление поля сводной таблицы.
После щелчка по кнопке Готово в третьем диалоговом окне Мастер сводных таблиц на рабочем листе будет создана сводная таблица, представленная на рис. 9.19.
Блеск и нищета сводных таблиц. Часть 13 | КомпьютерПресс
На рис. 11 элементы набора представлены перечнем значений, размещенным под заголовком «“Сложное” объединение № 1». Как видно из рисунка, данный список тождествен набору, формируемому выражением 8 в режиме одновременного отключения фильтров в срезах Group и Calendar Year.
