Валентин Арьков - Бизнес-аналитика. Сводные таблицы. Часть 2

5171,863.

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

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

AVERAGE

СРЗНАЧ.

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

Рис. 6.5. Автоматический выбор порога

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

Настроим выделение ячеек.

Находим раздел

Format cells that are GREATER THAN.

Задаём своё собственное значение:

5600.

Ячейки выделяются красным цветом. Этот цвет бросается в глаза. Обычно красный считается предупреждением об опасности. Это даже в дорожных знаках используют. Такое международное соглашение. А вот губная помада красного цвета почему-то не считается признаком опасности. Так что выбор цвета — это вопрос соглашений и договорённости.

В нашем примере будем считать, что мы работаем со «светофором»:

красный цвет — нежелательно, опасно, плохо;

жёлтый цвет — предупреждение о приближении опасности;

зелёный цвет — нормальное, приемлемое, желательное состояние.

В нашем примере речь идёт о продажах. Поэтому большое количество проданных товаров — это желательное состояние. Выбираем выделение зелёным:

Green Fill with Dark Green Text.

Теперь лучшие ячейки закрашена светло-зелёным, а самые лучшие значения показателей выводятся тёмно-зелёными цифрами.

Рис. 6.6. Выбор порового значения и цвета

Задание. Установите пороговое значение, чтобы выделить меньше половины ячеек и настройте «зелёное» цветовое решение.

Проверим, как будет смотреться наша таблица на печати. Готовые отчёты можно смотреть на экране или выводить на печать. Так что лучше убедиться, что всё красиво.

Выбираем в верхнем меню пункт

File — Print.

Можно поступить проще и нажать комбинацию клавиш

Ctrl + P.

Можно даже догадаться, что здесь английская Р — это первая буква слова Print, то есть печать документа, вывод на печатающее устройство.

Рассматриваем внешний вид таблицы (рис. 6.7). Пока всё устраивает.

Выходим из режима просмотра, нажав клавишу Esc. За этими буквами прячется английское слово Escape. В данном случае оно означает «Выход». Выход из какого-то пункта, режима или раздела.

Рис. 6.7. Предварительный просмотр

Задание. Вызовите предварительный просмотр сводной таблицы.

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

Смоделируем такие данные по претензиям.

Добавим в таблицу транзакций вспомогательную колонку «Случ». Запишем туда случайные числа. Смоделируем с помощью надстройки столбец случайных чисел с равномерным распределением от 0 до 1:

Data — Data Analysis — Random Number Generation — Uniform — Between 0 and 1.

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

Добавим в таблицу транзакций колонку «Претензии». Здесь у нас будут данные по претензиям — в очень упрощённом виде:

0 — претензий нет;

1 — по данной покупке была претензия со стороны покупателя.

Смоделируем очень редкие события с претензиями. Пусть это будет всего 0,3% всех покупок. Значит, из всех равномерно распределённых чисел мы оставим такую часть:

0,3% = 0,003.

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

0,003.

Начинаем вводить формулу. Указываем на ячейку, содержащую случайное число. Добавляем условие:

<0,003.

Получаем колонку логических значений (рис. 6.8).

Рис. 6.8. Моделирование претензий

Задание. Смоделируйте логическое поле «Претензия».

Нам было бы проще работать с числами. Тогда их можно будет просто сложить.

Преобразуем логическое значение в числовое. Для этого умножим формулу на единицу (рис. 6.9).

Мы работаем с объектом типа «Таблица Excel». Указывая в формуле соседнюю ячейку, мы получаем ссылку на соседний столбец. А после нажатия клавиши Enter у нас пересчитывается весь столбец — полностью.