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

Выделяем второй столбец. Устанавливаем общий формат вывода на экран:

Format Cells – Number – Category – General.

После настройки формата вывода выясняем, что начало нашего периода – это день номер 42005, а окончание – 43465.

Рис. 5.5. Номер дня

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

Вызываем генератор случайных чисел с помощью надстройки:

Data – Analysis – Data Analysis – Random Number Generation.

Появляется диалоговое окно настройки генератора Random Number Generation(рис. 5.6).

Указываем число переменных, то есть количество столбцов случайных чисел. Нам пока что понадобится один столбец. Поэтому вводим число 1:

Number of Variables = 1.

Далее указываем заданное количество случайных чисел:

Number of Random Numbers = 10000.

Выбираем из выпадающего списка равномерное распределение:

Distribution – Uniform.

Указываем пределы изменения случайной величины – это номера первого и последнего дня нашего диапазона дат:

Parameters – Between 42005 and 43465.

Затем устанавливаем начальное состояние генератора случайных чисел. Этот параметр разработчики программы назвали RandomSeed (Случайное рассеивание). Вводим любые четыре цифры:

Random Seed – 1234.

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

Выбираем начало диапазона для вывода случайных чисел:

Output options – Output Range.

Нажимаем ОКи получаем столбец чисел.

Рис. 5.6. Генератор случайных чисел

Задание. Сгенерируйте столбец случайных чисел в соответствии со своим вариантом задания.

Мы получили столбец случайных чисел. И эти числа дробные. Нам нужно их округлить, чтобы получить просто номера дней. Для этого используем функцию округления:

ROUND (number, num_digits).

Первый аргумент number – это ссылка на ячейку с числом, которое предстоит округлить.

Второй аргумент num_digits – это количество знаков после запятой. В нашей случае это ноль. Нам интересуют целые числа.

Вводим формулу в первую ячейку второго столбца и нажимаем Enter.

Заполняем весь столбец – двойным щелчком по маркеру автозаполнения (рис. 5.7). Столбец заполняется целыми числами.

Рис. 5.7. Округление чисел

Задание. Округлите случайные числа до целых значений.

Пришло время создать даты.

Выделяем столбец ячеек с целыми случайными числами. То есть с порядковыми номерами дней. Для этого щёлкаем по первой ячейке столбца, где имеется число. В нашем случае это ячейка С2. Нажимаем «секретную» комбинацию клавиш:

Ctrl + Shift + Down.

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

Гораздо проще поступить так. Нажимаем Ctrlи продолжаем держать эту клавишу нажатой. Нажимаем Shiftи продолжаем держать эти две клавиши нажатыми. Щёлкаем по клавише Down(Стрелка вниз) и отпускаем всё, что было нажато. Мы выделили все ячейки столбца, которые были заполнены.

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

Итак, мы выделили диапазон ячеек. Копируем выделенный фрагмент в буфер. Затем вставляем в новый столбец из буфера КАК ЗНАЧЕНИЯ (см. пример выше). Получаем третий столбец – номера дней, из которых мы создадим даты (рис. 5.8).

Рис. 5.8. Вставка значений

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

Зачем нужны были эти «хитрые» манипуляции со вставкой значений? На самом деле мы просто хотим оставить нужные столбцы и удалить вспомогательные. А нужный столбец может зависеть от вспомогательного. Например, округлённые значения во втором столбце ссылаются на случайные числа в первом столбце. Если удалить первый столбец, то все вычисления пострадают.

Вот теперь мы возьмём и удалим первый столбец. Выделяем ВЕСЬ первый столбец. Для этого щёлкаем по заголовку столбца А правой кнопкой мыши (рис. 5.9). В контекстном меню выбираем пункт Delete.

Рис. 5.9. Удаление столбца

Столбец А действительно исчезает. Теперь можно увидеть последствия. Каждая ячейка в столбце с округлением через функцию ROUNDсообщает, что ссылка не работает. Слово REFERENCEздесь означает «ссылка на другую ячейку». Итак, после удаления столбца наша формула ссылается на несуществующую ячейку. А вот столбец, куда мы вставили значения вместо формул, ни от кого не зависит. Удаляем «неправильный» столбец с формулами и неработающими ссылками.

Рис. 5.10. Неработающая ссылка

Задание. Удалите столбец случайных чисел. Обратите внимание на сообщения об ошибках. Удалите столбец с формулами.

У нас остался столбец дат. Пока что в виде порядковых номеров дней. Пора вывести на экран человеческие даты. Выделяем столбец (секретная комбинация клавиш описана выше) и зададим формат даты (рис. 5.11):

Format Cells – Number – Category – Date.

Привычный формат вывода даты на русском языке устанавливается в разделе:

Locale (location) – Russian

Type – 14-мар-2012.

Нажимаем ОК.

Рис. 5.11. Формат даты

Теперь даты стали видны невооружённым глазом. Наведём красоту и расставим даты в порядке возрастания. Выделяем диапазон дат и выбираем в верхнем меню:

Data – Sort & Filter – A-Z (Sort Oldest to Newest).

Это сортировка от A до Z. Или от А до Я. В порядке возрастания. От меньшего к большему.

Получаем даты по возрастанию, причём некоторые повторяются (рис. 5.12). Это значит, что наши магазины за один день посетили несколько клиентов, а не один. На самом деле, в реальной базе данных появляются сотни или даже тысячи записей каждый день. Но мы рассматриваем простой «игрушечный» пример, чтобы увидеть, как организована обработка данных.