08_Excel-3

Практикум Excel/OpenOffice Calc № 3
=Объединение данных из разных таблиц с помощью индексов=

Файлы для работы: [|gram] - база данных Грамматического словаря А.А.Зализняка - база данных частотного словаря НКРЯ (тексты 1950-2007 гг.)

"Скрещивание" таблиц нужно, если мы хотим получить общие данные из двух таблиц. Например, мы имеем сводные данные по двум подкорпусам равного размера, О и М:

Табл. О. Частота аномальных форм императивов в подкорпусе О.
 * 1 || Императив || Частота в корпусе О ||
 * 2 || избави || 9 ||
 * 3 || покажь || 4 ||
 * 4 || поди || 12 ||
 * 5 || подь || 3 ||
 * 6 || положь || 7 ||
 * 7 || трудися || 1 ||
 * 8 || убий || 1 ||

Табл. М. Частота аномальных форм императивов в подкорпусе М.
 * 1 || Императив || Частота в корпусе М ||
 * 2 || боись || 10 ||
 * 3 || ехай || 9 ||
 * 4 || поди || 37 ||
 * 5 || положь || 8 ||
 * 6 || стройсь || 1 ||
 * 7 || убий || 5 ||

То, что мы хотим получить - общая таблица, где данные сведены вместе: Табл. ОМ. Объединенная таблица. Мы видим, что размеры исходных таблиц не совпадают, не все императивы из первой таблицы есть во второй и наоборот. Тем не менее, столбец "Императивы" мы можем использовать как **ключ** для объединения таблиц.
 * 1 || Императив || Абс.частота(О) || Абс.частота(М) ||
 * 2 || боись || - || 10 ||
 * 3 || ехай || - || 9 ||
 * 4 || избави || 9 || - ||
 * 5 || покажь || 4 || - ||
 * 6 || поди || 12 || 37 ||
 * 7 || подь || 3 || - ||
 * 8 || положь || 7 || 8 ||
 * 9 || стройсь || - || 1 ||
 * 10 || трудися || 1 || - ||
 * 11 || убий || 1 || 5 ||

Теоретический вопрос: можем ли мы напрямую сравнивать абсолютные частоты, если выборки не равны? Какие частоты нам нужны в этом случае?

Алгоритм добавления значений из одной таблицы в другую
(в данном случае, это столбцы с заголовком "Императив"). > найдем его в столбце "Императивы" таблицы М > определим номер строки, на которой он стоит > запишем этот номер в столбец "Номер..." основной таблицы О (Например, слово "поди" мы найдем на 4 строке таблицы М). Результат шага 3: ``#`` показывает, что слово не найдено в таблице-источнике.
 * Шаг 1** - определим **ключ** - столбцы в исходных таблицах, по которым их можно объединить в одну
 * Шаг 2** - назовем таблицу О **основной**, а таблицу М - **источником** добавления данных. Добавим в основную таблицу О столбец "Номер строки, на которой слово стоит в таблице-источнике".
 * Шаг 3** - для каждого глагола в таблице О:
 * 1 || Императив || Частота в корпусе О ||> Номер строки… ||
 * 2 || избави || 9 ||> # ||
 * 3 || покажь || 4 ||> # ||
 * 4 || поди || 12 ||> 4 ||
 * 5 || подь || 3 ||> # ||
 * 6 || положь || 7 ||> 5 ||
 * 7 || трудися || 1 ||> # ||
 * 8 || убий || 1 ||> 7 ||

> найдем в столбце "Частота в корпусе "М" таблицы-источника ячейку, стоящую на указанной строке (Например, для глагола "поди" номер строки - 4, и на 4-й строке в столбце "Частота в корпусе М" таблицы-источника стоит 37. Результат шага 4:
 * Шаг 4** - добавим в таблицу О столбец "Данные из источника".
 * Шаг 5** - для каждого глагола в таблице О узнаем номер строки в источнике и:
 * 1 || Императив || Частота в корпусе О || Номер строки… || Данные из источника ||
 * 2 || избави || 9 || # || # ||
 * 3 || покажь || 4 || # || # ||
 * 4 || поди || 12 || 4 || 37 ||
 * 5 || подь || 3 || # || # ||
 * 6 || положь || 7 || 5 || 8 ||
 * 7 || трудися || 1 || # || # ||
 * 8 || убий || 1 || 7 || 5 ||

Теперь мы переставили все найденные данные из таблицы-источника. Осталось добавить те глаголы из таблицы-источника, которых не нашлось в основной таблице.


 * Шаг 6** - теперь все будет наоборот: таблица М станет основной таблицей, а таблица О - таблицей-источником. Добавим в таблицу М столбец "Номер строки, на которой слово стоит в таблице-источнике".
 * Шаг 7** - для каждого глагола в таблице М найдем его позицию в таблице О и запишем в столбце "Номер строки..."
 * Шаг 8** - возьмем из таблицы М только те глаголы, которых не нашлось в таблице О
 * Шаг 9** - добавим данные по этим глаголам в таблицу О.



Домашнее задание
Задание: взять из грамматического словаря фрагмент, в котором указаны сведения о всех русских глаголах, и добавить к ним следующие сведения из частотного словаря НКРЯ:
 * частота в ipm
 * количество текстов

1. Открываем оба файла для работы (см. начало) в Excel / OpenOffice Calc. Создаем новый файл и сохраняем его под именем index.xlsx. Выделяем на листе "ы-я" в грамматическом словаре с помощью фильтра* те строки, где располагаются глаголы, и копируем их с заголовском на Лист1 в файл index.xlsx. Переименовываем Лист1 в "Грам_словарь".
 * NB посмотрите на грамматическую информацию о словах и догадайтесь, какой фильтр нужно поставить.

2. Вставим (справа от столбца с глаголами) пустой столбец и назовем его "найти_номер_строки".

3. На вкладке Формулы найдем раздел "Ссылки и массивы" и в нем формулу "ПОИСКПОЗ" (в английской версии "MATCH").

В открывшемся окне вставки формул укажем:
 * Искомое_значение: кликните на ячейку с глаголом слева.

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


 * Просматриваемый массив: поставьте курсор на это окно, затем перейдите на лист таблицы-источника и выделите столбец с леммами



Нажмите OK
 * Тип сопоставления: 0 (обозначает точное совпадение).

В ячейке должен отобразиться номер строки, на которой искомый глагол стоит в таблице частотного словаря. Проверьте (с помощью поиска), что номер правильный.

Если вместо номера строки в ячейке отображается сама формула "=ПОИСКПОЗ...", выделите столбец, установите (правой кнопкой в контекстном меню) формат ячеек -- общий.

4. Скопируем ячейку с формулой и вставим ее в том же столбце напротив всех остальных глаголов.



NB Пересчет значений ячеек может занять некоторое время, особенно для больших таблиц. Если после пересчета значений в каких-то ячейках появится #Н/Д (в английской версии - #N/A), это означает, что строка с таким глаголом не найдена.

5. Добавим справа от столбца "найти_номер_строки" новый столбец и назовем его "частота". На вкладке Формулы в разделе "Ссылки и массивы" найдем формулу "ИНДЕКС" (в английской версии "INDEX"), и в открывшемся мастере формулы:
 * выберите первую опцию




 * Массив: перейдите на лист частотного словаря (таблицы-источника) и выделите столбец с частотами.




 * Номер_строки: поставьте курсор на поле "номер_строки" (тем самым вы вернетесь в основную таблицу и затем кликните по ячейке слева (где этот номер строки и указан)



Нажмите ОК. Проверьте (с помощью поиска в таблице-источнике, что частота для данного глагола указана правильно. Скопируем ячейку с формулой и вставим ее напротив всех остальных глаголов.
 * Номер_столбца: оставьте пустым, так как мы работаем с одним столбцом.

Добавьте новый столбец и вставьте в него данные по количеству текстов из частотного словаря. Указание: повторите шаг 5, но в поле "Массив" укажите столбец "Количество текстов" из таблицы-источника.

Вопрос. Как вы думаете, почему некоторых глаголов не оказалось в частотном словаре?


 * Завершение работы**

Выделим все три созданных столбца и переведем формулы в значения (копировать - Главное меню: вставить: вставить как значения). Удалим столбец "найти_номер_строки". Заменим #Н/Д на что-то более привлекательное, например, оставим эти ячейки пустыми.

В конце работы сохраните эксель-файл (проверьте, чтобы в нем не было других листов, кроме листа, на который вы ранее вставляли формулы, "Грам_словарь"). Cкопируйте все данные с листа "Грам_словарь" в текстовый файл и сохраните его под именем index.txt (кодировка UTF-8).

Файл index.xlsx и файл index.txt должны лежать в папке Unit7 дропбокса.
 * Чеклист**