|
При изучении баз данных и темы “Работа
со списками в электронной таблице Microsoft Excel”
возникает необходимость в создании
разнообразных списков, причем в большинстве
случаев в них используются не реальные, а некие
условные данные. Для эффективной работы со
списками и таблицами данных необходимо, чтобы
они имели достаточное количество записей с
различными значениями. Вводить же их вручную
весьма трудоемко. Ниже описано, как с помощью не
очень сложных формул в Microsoft Excel можно создать
список практически любой конфигурации.
Сформированный список затем можно
экспортировать, например, в СУБД Microsoft Access.
Рассмотрим настройку рабочего листа
Excel на примере создания протокола “Итоги
олимпиады по информатике среди учащихся 11-х
классов” с полями: Фамилия, Имя, Отчество,
Дата рождения, Класс, Результат.
Этапы работы
1. На листе ФИО (см. рис. 1)
создадим базу фамилий (в мужском роде), имен
(мужских и женских отдельно).
Рис. 1. Лист ФИО
2. С помощью формул (на рис. 1 они
обозначены Ф1) сформируем написание
“женского” варианта фамилии. Для этого в ячейке В3 записывается формула:
=ЕСЛИ(ЕТЕКСТ(A3);
ЕСЛИ(ИЛИ(ПРАВСИМВ(A3;1)="в";
ПРАВСИМВ(A3;2)="ин");A3&"а";
ЕСЛИ(ПРАВСИМВ(A3;2)=
"ий";ПОДСТАВИТЬ(A3;"ий";"ая");
ЕСЛИ(ПРАВСИМВ(A3;2)=
"ой";ПОДСТАВИТЬ(A3;"ой";"ая");A3)));"")
— которая затем распространяется
(копируется) на остальные ячейки столбца В.
Примечание. Многообразие русских (и
не только) фамилий не позволяет записать
исчерпывающую формулу, однако автор постарался
учесть все распространенные варианты. Однако
учитывая условность записываемых в списки
значений, приведенная и другие ниже формулы
могут быть приняты.
3. Сформируем по списку мужских имен
мужские и женские отчества. Данный процесс
представляет достаточные трудности, связанные с
непростыми правилами русского языка и
исключениями из правил. Запишем формулы для
отчеств, образованных от наиболее
распространенных русских имен для мужчин
(формула Ф2) и для женщин (формула Ф3):
Формула в ячейке Е3 :
=ЕСЛИ(ЕТЕКСТ(C3);
ЕСЛИ(ПРАВСИМВ(C3;2)="ей";
ПОДСТАВИТЬ(C3;"й";"евич");
ЕСЛИ(ПРАВСИМВ(C3;2)="ай";
ПОДСТАВИТЬ(C3;"й";"евич");
ЕСЛИ(ПРАВСИМВ(C3;2)="ий";
ПОДСТАВИТЬ(C3;"ий";"ьевич");
ЕСЛИ(ПРАВСИМВ(C3;1)="ь";
ПОДСТАВИТЬ(C3;"ь";"евич");
ЕСЛИ(ПРАВСИМВ(C3;1)="а";
ПОДСТАВИТЬ(C3;"а";"ич");
ЕСЛИ(ПРАВСИМВ(C2;1)="я";
ПОДСТАВИТЬ(C2;"я";"ич");
СЦЕПИТЬ(C3;"ович")))))));"")
Формула в ячейке F3:
=ЕСЛИ(ЕТЕКСТ(E3);
ЕСЛИ(ПРАВСИМВ(E3;1)="а";
ПОДСТАВИТЬ(E3;"а";"ична");
ЕСЛИ(ПРАВСИМВ(E3;1)="я";
ПОДСТАВИТЬ(E3;"я";"нична");
ПОДСТАВИТЬ(E3;"ич";"на")));"")
Данные формулы обладают рядом
существенных недостатков, главным из которых
является то, что в ней не учитываются имена с
бегающими гласными (Лев — Львович, Павел
— Павлович), имена, заканчивающиеся на -ов,
-аил (Яков — Яковлевич, Михаил — Михайлович),
имена, заканчивающиеся на -ий, перед которым
две согласные буквы (Дмитрий — Дмитриевич,
Георгий — Георгиевич).
Для более полного решения данной
проблемы можно написать макрос на языке VBA (см.
Приложение).
Полученные на листе ФИО данные
являются исходными для формирования списков.
4. На листе Список получим
случайным образом выбранные данные с полями Фамилия,
Имя, Отчество, Дата рождения, Класс,
Результат (см. рис. 2). Обратим внимание на
то, что для получения мужских и женских вариантов
имен, отчеств и фамилий необходимо дополнительно
использовать поле Пол.
Данные можно получить с помощью
значений Ф4–Ф6 и формул Ф7–Ф15.
Комментарии к ним приведены в таблице ниже.
Рис. 2. Лист Список
Ячейка,
диапазон |
Содержание |
Значение
или формула |
В2:I3 |
Начальные и
конечные значения для формирования случайного
числа из заданного диапазона |
|
В3 |
Определяют
количество человек в списке (не более 500) |
Например, 5 |
С2:Е2 |
Номер строки
первого значения на листе ФИО |
3 |
С3 |
Ф4: номер
строки после последнего непустого значения в
столбце Фамилия на листе ФИО (не более 500) |
=C2+СЧЁТЕСЛИ(ФИО!A3:A503;">"") |
D3 |
Ф5: номер
строки после последнего непустого значения в
столбце Имя/Мужчины на листе ФИО (не более
500) |
=D2+СЧЁТЕСЛИ(ФИО!C3:C503;">"") |
Е3 |
Ф6: номер
строки после последнего непустого значения в
столбце Имя/Женщины на листе ФИО (не более
500) |
=E2+СЧЁТЕСЛИ(ФИО!D3:D503;">"") |
I2:I3 |
Минимальное и
максимальное количество баллов |
|
В6 |
Ф7: номер
первого человека в списке |
=B2 |
Все нижеследующие
формулы заполняют ячейку непустым значением
только в том случае, если ячейка с номером
содержит число. После этого формула копируется
до 503-й строки |
B7 |
Ф8: следующий
номер заполняется в том случае, если значение не
превосходит верхнюю границу |
=ЕСЛИ(B6<B$3;B6+1;"") |
F6 |
Ф9: пол
формируется случайным образом (при значении 1 в
ячейку заносится значение “м”, при 2 — “ж”) |
=ЕСЛИ(ЕЧИСЛО($B6);
ВЫБОР(1+ЦЕЛОЕ(СЛЧИС()*2);"м";"ж");"") |
С6 |
Ф10: в
зависимости от выбранного пола выбирается
фамилия случайным образом из одного из двух
списков |
=ЕСЛИ(ЕЧИСЛО($B6);
ЕСЛИ($F6="м";
ДВССЫЛ("ФИО!A"&ЦЕЛОЕ(C$2+СЛЧИС()*(C$3-C$2)));
ДВССЫЛ("ФИО!B"&ЦЕЛОЕ(C$2+СЛЧИС()*(C$3-C$2))));
"") |
D6 |
Ф11: в
зависимости от выбранного пола выбирается имя
случайным образом из одного из двух списков |
=ЕСЛИ(ЕЧИСЛО(B6);ЕСЛИ(F6="м";
ДВССЫЛ("ФИО!C"&ЦЕЛОЕ(D$2+СЛЧИС()*(D$3-D$2)));
ДВССЫЛ("ФИО!D"&ЦЕЛОЕ(E$2+СЛЧИС()*(E$3-E$2))));"") |
E6 |
Ф12: в
зависимости от выбранного пола выбирается
отчество случайным образом из одного из двух
списков |
=ЕСЛИ(ЕЧИСЛО(B6);ЕСЛИ(F6="м";
ДВССЫЛ("ФИО!E"&ЦЕЛОЕ(D$2+СЛЧИС()*(D$3-D$2)));
ДВССЫЛ("ФИО!F"&ЦЕЛОЕ(D$2+СЛЧИС()*(D$3-D$2))));"") |
G6 |
Ф13: случайное
число из заданного диапазона, формат ячейки — Дата/время |
=ЕСЛИ(ЕЧИСЛО($B6);
ЦЕЛОЕ(G$2+СЛЧИС()*(G$3-G$2+1));"") |
H6 |
Ф14: случайное
число из заданного диапазона и одна из трех букв А,
Б или В |
=ЕСЛИ(ЕЧИСЛО($B6);
ЦЕЛОЕ(H$2+СЛЧИС()*(H$3-H$2+1))
&ВЫБОР(ЦЕЛОЕ(1+СЛЧИС()*3);
"А";"Б";"В");"") |
I6 |
Ф15: случайное
целое число из заданного диапазона |
=ЕСЛИ(ЕЧИСЛО($B6);
ЦЕЛОЕ(I$2+СЛЧИС()*(I$3-I$2+1));"") |
5. В ячейках B6:I503
установим условное форматирование (команда Формат
| Условное форматирование) в виде внешних
границ ячейки, указав в качестве условия: формула и =В6<>"".
6. Выделим нужные поля полученного
списка (несмежные диапазоны выделяются с помощью
клавиши  ) и
скопируем их на новый лист с помощью команды Специальная
вставка, установив переключатель в положение Вставить значения.
Как указывалось в начале, полученный
список можно использовать как в Excel, так и
экспортировать в Access.
Описанная методика построения списков
может быть использована при подготовке к
занятиям или предложена учащимся в качестве
самостоятельной лабораторной работы по Microsoft Excel,
мини-проекта и т.п.
Примечание редактора. Аналогичным
образом могут быть получены и другие данные
(номера телефонов, даты уроков, оценки, сведения о
пропусках уроков и т.п.).
ПРИЛОЖЕНИЕ
Программный код “Заполнение
столбцов E и F на листе ФИО”
Function Vowel (a As
String) As Boolean
'Логическая функция: принимает
значение True, если буква гласная, и False, если
согласная
'Для исключения разночтений - символ
преобразуется в строчной
Vowel = False
Select Case LCase(a)
Case "а", "е", "ё",
"и", "о", "у", "ы", "э",
"ю", "я": Vowel = True
End Select
End Function
Sub Patronymic()
Const m = 5, w = 6, mname = 3
Dim S As String, S1 As String, i As
Integer, N As Integer
Range("E3:F500") = ""
i = 2
Do
i = i + 1
S = Cells(i, mname)
N = Len(S)
Select Case Right(S, 1)
Case "а", "я": S1 = Left(S, N - 1) &
"ич"
Case "ь": S1 = Left(S, N - 1) &
"евич"
Case "й"
If Mid(S, N - 1, 1) = "и" And
Vowel(Mid(S, N - 3, 1)) Then
S1 = Left(S, N - 2) & "ьевич"
Else
S1 = Left(S, N - 1) & "евич"
End If
Case Else
Select Case Right(S, 2)
Case "ев": S1 = Left(S, N - 2) &
"ьвович"
Case "ел": S1 = Left(S, N - 2) &
"лович"
Case "ов": S1 = S & "левич"
Case Else
If S = "аил" Then
S1 = Left(S, N - 2) & "йлович"
Else: S1 = S + "ович"
End If
End Select
End Select
Cells(i, m) = S1
'Женские
If S = "Никита" Then
S1 = "Никитична"
Else If S = "Илья" Then
S1 = "Ильинична"
Else: S1 = Left(S1, Len(S1) - 2) & "на"
End If
Cells(i, w) = S1
Loop Until Cells(i + 1, mname) = ""
End Sub |