Чтобы попробовать этот совет на своем компьютере, загрузите и разархивируйте CFH231.zip.
Сводные таблицы - самая мощная функция Excel. В других советах на этом сайте обсуждаются сводные таблицы, но сегодняшнее шоу рассказывает о двух интересных применениях сводных таблиц. Во-первых, вы можете использовать сводную таблицу, чтобы получить уникальный список значений из поля. Во-вторых, вы можете использовать сводную таблицу, чтобы показать процентное соотношение элементов в строке.
Набор данных содержит более 10 000 строк данных о продажах. Есть столбцы для клиента, продукта, месяца и проданного количества.
Поиск уникальных ценностей
Традиционный способ найти уникальные значения из списка - использовать команду Данные - Фильтр - Расширенный фильтр. Однако вы также можете создать уникальный список значений с помощью сводной таблицы. Выберите одну ячейку в наборе данных. В меню выберите Данные - Сводная таблица и Отчет сводной диаграммы. Щелкните Готово. Вам будет представлена пустая сводная таблица, как показано ниже.
Чтобы получить уникальный список клиентов, выберите поле «Клиент» и нажмите кнопку «Добавить в область строки».
Если посчитать, всего было четыре клика. Теперь вы можете скопировать ячейки A5: A28 на другой лист, и у вас есть уникальный список клиентов.
Расчет процента от общей суммы
Вернитесь к исходному набору данных и выберите одну ячейку. В меню выберите Данные - Отчет сводной таблицы и сводной диаграммы. Нажмите Готово, чтобы создать пустую сводную таблицу.
В списке полей сводной таблицы выберите «Продукт» и нажмите «Добавить в область строк».
Затем выберите поле «Дата». Измените раскрывающийся список внизу списка полей на область столбца и нажмите кнопку «Добавить в».
Выберите поле «Обращения». Измените раскрывающийся список на область данных. Щелкните кнопку Добавить в. В итоговой сводной таблице будут показаны продажи по продуктам и месяцам.
Хотя этот отчет представляет собой сводку из 10 000 строк данных, он по-прежнему представляет собой беспорядочную смесь цифр. В данных действительно сложно определить тенденции. Когда бананы продаются лучше всего? Сложно сказать.
Дважды щелкните кнопку «Сумма наблюдений» в ячейке A3, чтобы открыть диалоговое окно «Поле сводной таблицы». В диалоговом окне есть несколько настроек, которые редко нужно менять. Однако нажмите кнопку «Параметры», чтобы увидеть дополнительные параметры.
В развернутом диалоговом окне показано, что данные отображаются в обычном режиме.
Выберите раскрывающийся список Показать данные как. Возможны варианты:
- Нормальный
- Отличие от
- % от
- % Разница
- Промежуточная сумма в
- % строки
- % столбца
- % от общей суммы
- Показатель
Для сегодняшнего анализа выберите% строки. Базовое поле и базовый элемент не имеют отношения к% строки, поэтому эти параметры выделены серым цветом. Нажмите ОК, чтобы закрыть диалоговое окно и вернуться к сводной таблице.
В итоговой сводной таблице видно, что продажи бананов самые высокие в период с января по февраль. Яблоки лучше всего растут с сентября по ноябрь, а вишни - с мая по июль.
Хотя для создания сводных таблиц обычно требуется четыре щелчка мышки, у них есть много дополнительных функций, которые позволяют проводить еще более эффективный анализ. Людей часто удивляет, что мы с Майклом Александром смогли заполнить 250 страниц об одной функции Excel, но когда вы рассматриваете все различные варианты в сводной таблице, это заслуживает целой книги.
Подсказки из этого эпизода взяты из «Обработки данных сводной таблицы».
