rss
  •  
Обучение Microsoft Excel: от основ до PowerBI

Создание нескольких сводных таблиц по данным одной таблицы с возможностью анализа данных по различным временным интервалам

| Категория: Приемы и советы, Работа с табличными массивами |

4

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

Например, есть книга Excel с одной таблицей, размер которой – 6 столбцов и 1000 строк, при этом размер файла составил 60,6 КБ.

pv1.png

Если построить один отчет для анализа данным по кварталам, а затем построить ещё один отчет для анализа данных по годам (месяцам), то т.к. источник будет один, то и временной интервал может быть выбран только один для двух отчетов.
Если строить независимый отчет с помощью мастера сводных таблиц, то размер файла будет увеличен до 97,8 КБ.

 ptv2.png

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

ptv3.png

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

ptv5.png

Размер файла при этом составит 85,1 КБ.

Графический факторный анализ Торнадо (Tornado chart)

| Категория: Диграммы, Приемы и советы |

5

Сравнивать данные графически можно с использованием разных видов диаграмм. Рассмотрим графический факторный анализ торнадо (tornado chart) – необходимо выполнить сравнение данных по занятиям спортом в зависимости от возрастной группы:

charttornado1.png

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

Представим, данные по мужчинам отрицательными числами (см. Быстрое изменение исходных значений в ячейках):

charttornado2.png

Чтобы значения в ячейках были показаны положительными числами, в формате ячеек установим пользовательский формат ;0%

charttornado3.png

В таком случае диаграмма может выглядеть так:

charttornado4.png

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

charttornado5.png

Консолидация данных. Подписываем названия листов таблиц-источников

| Категория: Приемы и советы, Работа с табличными массивами |

10

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

cons1.png

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

cons2.png

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

cons3.png

Для определения адреса листа воспользуемся маленьким модулем, в котором представим формулу в виде текстовой строки (открыть редактор для вставки клавишами Alt+F11):

Public Function FormulaToText(rng As Range) As String
FormulaToText = IIf(rng.HasFormula, CStr(rng.Formula), “”)
End Function

Результат по функции будет:

cons4.png

Тип файла следует сохранить как книга Excel с поддержкой макроса.

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

cons5.png