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

Рис. 6.4. Правила выделения значений

Задание. Ознакомьтесь со списком правил выделения ячеек и выясните, как можно выделять текстовые значения и даты.

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

Greater Than.

На экране появляется одноимённое диалоговое окно (рис. 6.5). Программа автоматически выбирает пороговое значение, выше которого срабатывает выделение цветом. В нашем примере это число

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 у нас пересчитывается весь столбец — полностью.

Рис. 6.9. Числовое значение вместо логического

Задание. Преобразуйте поле «Претензия» в числовое значение.

Посмотрим, много ли у нас появилось единичек. То есть претензий покупателей.

Щёлкаем по кнопке со стрелочкой в правой части заголовка столбца «Претензии». Снимаем выбор ноликов и оставляем выбор единичек (рис. 6.10).

Рис. 6.10. Выбор единиц (фактов претензий)

Нажимаем ОКи получаем фрагмент нашей таблицы транзакций. На экран выведены только те строки, в которых поле «Претензия» принимает значение 1. Там, где строки без претензий скрыты, можно увидеть двойную сплошную разделительную линию между номерами строк (рис. 6.11). Например, между строками 1 и 84. А вот между соседними строками 126 и 127 — одинарная сплошная.

Рис. 6.11. Строки с претензиями

Задание. Настройте вывод только строк с претензиями и обратите внимание на разделительные линии между номерами строк.

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

Среднее значение = 1.

Всего было выделено 32 ячейки.

Минимальное и максимальное значения = 1.

То есть все выделенные значения = 1.

Сумма значений = 32.

Это сумма 32 единичек.

Рис. 6.12. Количество претензий

Задание. Определите количество претензий с помощью строки состояния.

Мы посмотрели на итоговые показатели с выбором единичек. Выбираем все возможные значения:

Select All.

Нажимаем ОК.

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

Минимальное значение = 0.

Максимальное значение = 1.