archive_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/Calc. Создать новый файл и сохранить его под именем index.xlsx. Выделить на листе "ы-я" в грамматическом словаре те строки, где располагаются глаголы на -ти (с заголовком), и скопировать их на Лист1 в файл index.xlsx.

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

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

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

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


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



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

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

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

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



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

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




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




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



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

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

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


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

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



Домашнее задание
Сделать объединенную таблицу по данным двух подкорпусов НКРЯ.

1. Найдите аномальные грамматические формы императивов: поиск 1 - по подкорпусу со снятой омонимией, тексты художественной литературы основного корпуса поиск 2 - по подкорпусу "Речь кино" мультимедийного корпуса Отсортируйте выдачу случайным образом, проверьте, что в выдаче НЕ отображаются ударения (версия без ударений).

Запишите а) объем **под**корпуса в количестве слов, б) количество найденных вхождений.

Сохраните результаты поиска в Excel-формате.

Откройте в Excel оба файла, разрешите редактирование и сохраните их в формате "Книга Excel" под именем fiction и cinema, соответственно.

2. Для //каждого// файла: создайте сводную таблицу по столбцу Center Center - Количество по полю Center Сохраните на новом листе как значения, сделайте новые заголовки (например, Императив - Частота fiction, Императив - Частота cinema), уберите строки с шумом (например, с формой "буди") уберите строки "(пустые)"/"(blank)" и "Всего"/"Общий итог"/"Total".

3. Добавьте на лист сводной таблицы fiction данные о количестве вхождений из сводной таблицы cinema (см. инструкции из практикума в классе). У вас должны получиться столбцы Императив - Частота fiction - найти_номера_строк_в_cinema - Частота cinema.

Дальше будем действовать по алгоритму Шаг 6 - Шаг 9 (прочитайте выше). 4. Добавьте на лист сводной таблицы cinema столбец найти_номера_строк_в_fiction и узнайте, содержатся ли глаголы из cinema в fiction.

5. Сохраните формулы как значения, затем поставьте фильтр на столбец найти_номера_... и отфильтруйте по значению #Н/Д (#N/A)

6. Выделите все, что отфильтровалось, и скопируйте в таблицу **fiction** (после всего, что в ней находится). Переставьте значения по корпусу cinema в столбец Частота cinema. Ячейки с #Н/Д (#N/A) замените на что-то более привлекательное (например, на пустоту) и удалите столбец найти_номера_строк... если, он у вас еще остался. Отсортируйте таблицу по алфавитному порядку императивов.

7. Итоговая таблица должна содержать три столбца: Императив - Частота fiction - Частота cinema (т.е. выглядеть примерно как Табл. ОМ на рисунке выше). Добавьте к таблице снизу две строки: Объем подкорпуса: Найдено вхождений: и заполните значения для каждого подкорпуса.

8. Сохраните файлы cinema и fiction и загрузите их в дропбокс в папку Unit 8. Итоговая таблица должна находиться на отдельном листе в файле fiction.