Электронные таблицы (Calc)

Довольно часто возникает необходимость в наборах тестовых данных.

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

Обычно везёт, задачу для решения ставят сразу с готовыми образцами данных. Но, увы, не всегда.

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

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

Электронные таблицы вполне можно использовать для создания таких тестовых наборов.

1. Случайные числа

В Excel есть две функции, которые генерируют случайные числа - СЛЧИС() и СЛУЧМЕЖДУ(). В Calc'е эти же функции называются RAND() и RANDBETWEEN().

Следует иметь в виду, что в Calc'е эти функции доступны всегда, а вот Excel "вспоминает" о наличии функции СЛУЧМЕЖДУ() только после установки Пакета анализа!

СЛЧИС() или RAND() возвращает случайное число в диапазоне от 0 до 1. Но для наших целей желательно бы иметь какие-то целые числа из заданного диапазона. Чтобы получить с помощью этих функций случайное число из диапазона от min_val до max_val, пришлось бы дополнительно обработать результат при помощи такой формулы:

= INT(RAND() * (max_val -min_val+1) + min_val)

То есть, для чисел от 25 до 100 формула была бы такой:

= INT(RAND() * 76 + 25)

К счастью, нет необходимости писать такие формулы - функции СЛУЧМЕЖДУ() и RANDBETWEEN() все необходимые пересчеты сделают самостоятельно. У этих функций по два параметра - наименьшее генерируемое значение и наибольшее. То есть, для нашего примера достаточно написать

=RANDBETWEEN(25; 100)

А если нам понадобятся числа похожие на цены (с копейками), можно написать, например:

=RANDBETWEEN(1; 1000)*0,01

и получим значение от одной копейки до 10 рублей (гривен, долларов...)

2. Случайные даты

Дата для электронной таблицы это обычное число. Например, сегодня, когда я пишу эту статью 21 мая 2011года. А для Calc'а это число 40684. Именно столько дней прошло с 30 декабря 1899. Это у меня в настройках Calc'а сейчас выставлена такое начальное значение для преобразования чисел в даты. А можно было бы установить 01.01.1900 или 01.01.1904 (Excel использует только эти два варианта)

Зная эту особенность, можем легко сгенерировать любую случайную дату.

Например, такая формула:

=TODAY()-RANDBETWEEN(1;60)

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

Можно использовать и более сложную формулу. Например,

=DATE(RANDBETWEEN(1950;2010);RANDBETWEEN(1;12);RANDBETWEEN(1;31))

выдаст случайную дату из диапазона с первого января 1950 года по 31 декабря 2010 года, которую можно использовать, к примеру, как дату рождения вымышленного человека.

Остается только отформатировать полученное число в виде даты.

В Excel функция TODAY() называется СЕГОДНЯ(), а функция DATE() - ДАТА()

3. Абракадабра (случайный набор символов)

Такие строки могут понадобиться для генерации паролей или каких-то кодов.

Чтобы из целого числа получить символ, можно использовать функцию CHAR() (в Excel - СИМВОЛ()). Достаточно помнить, что в функцию нужно передать в качестве параметра число для получения:

• цифры - от 48 до 57,
• латинских букв - от 65 до 90 для заглавных, от 97 до 122 для строчных,
• кириллических букв - от 192 до 223 для заглавных, от 224 до 255 для строчных.

Остается лишь соединить достаточное количество таких символов с помощью функции CONCATENATE() (в Excel - СЦЕПИТЬ()) или при помощи знака конкатенации, "склейки строк" - амперсанда.

Например, такая вот функция:

=CONCATENATE(CHAR(RANDBETWEEN(65;90));CHAR(RANDBETWEEN(97;122));CHAR(RANDBETWEEN(48;57));CHAR(RANDBETWEEN(65;90)))

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

Rf8D, Gr5O, Qf5A, Oh5R, Ap9X, Qx6M, Ja2K, Ws2V, Sv5B, Kq5T, Vu1E, Jw9G, Fm0C, Bh9B, Oa3W, Pt6D, Oj1O, Ts4H, Mo1V...

Но выглядит формула, несмотря на свою простоту, довольно страшненько. Не беда! Её можно слегка упростить:

=CHAR(RANDBETWEEN(65;90))&CHAR(RANDBETWEEN(97;122))&RANDBETWEEN(0;9)&CHAR(RANDBETWEEN(65;90))

Здесь вместо вызова функции CONCATENATE() используем знак & для склеивания символов в одну строку и для генерации цифры используем не конструкцию CHAR(RANDBETWEEN(48;57)), а просто случайную цифру от 0 до 9 - RANDBETWEEN(0;9). Программа сама поймет, что нам нужна строка и преобразует полученное число в символ цифры.

Разумеется, формулу можно сделать очень длинной, чтобы получать строки в 10, 15, 20 символов. Но они все будут одной длины. А как сделать чтобы они еще и длиной друг от друга отличались? Да очень просто! При помощи функции LEFT (или RIGHT, или MID) вырезать из получившейся строки случайное количество символов. (В Excel эти функции называются ЛЕВСИМВ(), ПРАВСИМВ() и ПСТР())

4. Почтовый индекс или телефонный номер

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

Но в этом нет никакой необходимости! Ведь и индекс, и номер - это просто большое число. Да, в начале этого числа могут идти нули, например, 02099 или 067-233-41-95. Но это не трудно сделать специальной функцией форматирования

=TEXT(RANDBETWEEN(1;99999);"00000") (в Excel - =ТЕКСТ(СЛУЧМЕЖДУ(1;99999);"00000") )

даст нам пятизначное число похожее на почтовый индекс, а

=TEXT(RANDBETWEEN(1011111;9999999);"000-00-00")

сгенерирует строку похожую на телефонный номер.

5. Случайное слово из набора

Например, нам нужен перемешанный перечень из нескольких населенных пунктов (стран, валют, цветов...)

Если набор небольшой, то можно использовать функцию CHOOSE() (ВЫБОР()). Её первый параметр - индекс - задать через уже привычную для нас RANDBETWEEN() (СЛУЧМЕЖДУ()), а дальше перечислить нужные значения.

Например, сигналы светофора можно получить так:

=CHOOSE(RANDBETWEEN(1;3);"Красный";"Желтый";"Зелёный")

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

Где-то в сторонке записываем табличку "образцов". Например, так:

Присвоить диапазону имя, например, Strana. Это можно сделать из меню Вставка - Названия - Определить или нажав Ctrl+F3. Или просто вписать нужное слово в специальное поле в верхнем левом углу, там, где обычно отображается адрес текущей ячейки.

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

=OFFSET(Strana;RANDBETWEEN(1;COUNTA(Strana)-1);0;1;1)

или просто

=INDEX(Strana;RANDBETWEEN(2;COUNTA(Strana)-1))

Для Excel эти функции называются СМЕЩ(), СЧЁТЗ() и ИНДЕКС()

6. ФИО

Объединив вместе все перечисленные приемы , можно быстро и просто генерировать сотни строк тестовых данных, очень похожих на настоящие. И сейчас я покажу, как быстро сформировать список случайных фамилий, имен и отчеств.

Обсудить на форуме

•  Источник