Генерация данных с помощью встроенных функций

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

Основой метода является функции генерации случайных чисел – СЛЧИС().

Она генерирует случайные числа из диапазона 0..1. Для генерации целых чисел из произвольного диапазона используется формула:

 

=А + ЦЕЛОЕ((В – А+1)*СЛЧИС()),

 

где А – нижняя граница необходимого диапазона;

В – верхняя граница диапазона;

ЦЕЛОЕ – имеющаяся в Excel функция округления дробных чисел.

В качестве примера рассмотрим поэтапное создание базы данных «Кадры».

1. Создаем шапку таблицы

 

	B	C	D	E	F	G	H	I	J	K
	Таб. №	Фамилия	Имя	Пол	Отдел	Оклад	Дата рождения	Дети	Адрес	Телефон

 

2. В ячейки B3 и B4 вводятся значения 100 и 200, которые затем путем автозаполнения копируются на сто последующих строк.

3. В E3 вводится формула: =ЕСЛИ(ЦЕЛОЕ(2*СЛЧИС())=0;"м";"ж")

Смысл формулы заключается в следующем:

– генерируется случайное целое число (0 или 1);

– если это число равно 0, то пол мужской;

– иначе (т.е. это число равно 1), то пол – женский.

4. В стороне от формируемой таблицы печатаются пронумерованные списки наиболее распространенных фамилий и имен (мужских и женских).

 

		M	N	O	P	Q
			Кузнецов	Андрей	Кузьмина	Екатерина
			Степанов	Иван	Петрова	Светлана
			Кузьмин	Дмитрий	Романова	Людмила
			Сидоров	Михаил	Степанова	Надежда
			Иванов	Денис	Сидорова	Любовь
			Андреев	Владимир	Кузнецова	Мария
			Петров	Петр	Иванова	Вероника
			Романов	Александр	Алексеева	Елена
			Алексеев	Вячеслав	Андреева	Наталья
			Васильев	Алексей	Васильева	Евгения

 

5. В ячейку С3 вводится формула:

=ЕСЛИ(E3="м";

ВПР(ЦЕЛОЕ(1+10*СЛЧИС());$M$2:$Q$11;2);

ВПР(ЦЕЛОЕ(1+10*СЛЧИС());$M$2:$Q$11;4))

 

Смысл формулы заключается в следующем:

– если пол мужской, из списка фамилий с помощью функции ВПР берется случайная мужская фамилия;

– иначе берется женская фамилия.

 

6. Для формирования имен в ячейку D3 вводится аналогичная формула:

=ЕСЛИ(E3="м";

ВПР(ЦЕЛОЕ(1+10*СЛЧИС());$M$2:$Q$11;3);

ВПР(ЦЕЛОЕ(1+10*СЛЧИС());$M$2:$Q$11;5))

 

7. Заполнение колонок F и J производится практически аналогично.

В стороне от базы создается список отделов:

 

		M	N
			Снабжения
			Плановый
			Сбыта
			Производственный

 

В ячейку F3 вводится формула:

 

=ВПР(ЦЕЛОЕ(1+4*СЛЧИС());$M$15:$N$18;2)

 

В стороне от базы создается список адресов:

 

		P	Q
			ул. Павлова
			ул. Яковлева
			ул. Ленина
			ул. Горького
			ул. Заовражная
			ул. Хевешская
			ул. Мира
			ул. Водопроводная
			ул. Лебедева

 

В ячейку J3 вводится формула:

=ВПР(ЦЕЛОЕ(1+10*СЛЧИС());$P$15:$Q$23;2)

 

8. Для заполнения колонок «Оклад», «Дата рождения», «Дети» и «Телефон»:

– в ячейку G3 вводится формула: =5000+1000*ЦЕЛОЕ(16*СЛЧИС());

– в ячейку H3 вводится формула: =ЦЕЛОЕ(3*СЛЧИС());

– в ячейку I3 вводится формула: =1945+ЦЕЛОЕ(50*СЛЧИС());

– в ячейку K3 вводится формула:

=100000+100000*ЦЕЛОЕ(10*СЛЧИС()).

Обратите внимание на числа в формулах – они определяют диапазоны генерации.

Для окладов генерируются числа из диапазона 5000..20000;

Для детей – числа из диапазона 0..2;

Для дат рождения –числа из диапазона 1945..1994;

Для телефонов – числа из диапазона 100000..900000.

 

9. Выделить все введенные в третью строку формулы и скопировать их на нужное количество строк таблицы.

Вы должны были обратить внимание на то, что после каждой манипуляции с данными их значения меняются. Это свойство функции СЛЧИС.

Чтобы избавиться от этого эффекта:

– выделяем всю таблицу и копируем ее в буфер;

– не снимая выделения произведем перекопирование данных командой Правка > Специальная ставка > Значения.