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

Графический факторный анализ Торнадо (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

Вычисление ранга

| Категория: Приемы и советы, Формулы и функции |

6

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

rank1.png

  • РАНГ(Число;Ссылка;Порядок) – определение места значения в списке значений (порядковый номер относительно других чисел в списке).
    RANK(Number,Ref,Order)
    Порядок:
    0 (или не указано) – максимальное значение соответствует 1-ой позиции, минимальное – последней.
    1 – минимальное значение соответствует 1-й позиции, а максимальное – последней.

rank2.png

  • ПРОЦЕНТРАНГ(Массив;Х;Разрядность) – процентная норма значения в множестве данных
    PERCENTRANK(Array,X,Significance)

rank3.png

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

rank4.png