Объединение данных из разных таблиц с помощью индексации

Возьмем частотные списки слов в из двух корпусов, КРУТ и Русского учебного корпуса (RLC) - частотные списки обрезаны по частоте. Вот ссылка на гугл-таблицу, с которой вы можете сделать себе копию (или скачать версию для работы в Excel).

Таблицы корпусов CorST и RLC находятся на разных вкладках.

Мы хотим получить общую таблицу, где данные из двух таблиц сведены вместе и их можно сравнить.

Внимание, в получившейся таблице могут и должны будут встречаться пустые ячейки!

Что нужно делать:

Шаг 0. Заполните в обеих таблицах столбец “Ранг”. Ваши таблицы уже отсортированы по частоте, поэтому заполним этот столбец числами 1, 2, 3 и т.д. Есть разные способы сделать это, например, заполнить первые 3 ячейки, выделить и дважды кликнуть на нижний правый угол (там появится “+”). Или вставить 1, а потом использовать формулу =1+D2 и т.п., которая будет увеличивать значения на 1.

Теперь сложнее. Слова с одинаковой частотой должны получить одинаковый ранг, например, слова может, также, является и у в CorST должны получить ранг 28, 29, 29, 31, соответственно. Придумайте и используйте формулу, которая может это сделать. (Если вы не справитесь с этим заданием, переходите к следующим шагам, ваши ответы будут засчитаны, но оценка будет снижена на 1 балл.)

Шаг 1.

Добавим в таблицу CorST столбец “Номер строки, на которой слово стоит в RLC”.

Шаг 2.

Затем для каждого слова в таблице CorST нужно:

• найти его в столбце “Слово” таблицы RLC
• определить номер строки, на которой он стоит
• записать этот номер в столбец “Номер…” таблицы CorST

Сделаем это с помощью формулы =ПОИСКПОЗ(...) (в английской версии =MATCH(...)).
В Excel эту формулу можно найти на вкладке Формулы в разделе “Ссылки и массивы”.

В окне вставки формул укажем три аргумента:

• Искомое_значение: кликните на ячейку с лексемой слева.
• Просматриваемый массив: затем перейдите на лист таблицы RLC и выделите столбец “Слово”
• Тип сопоставления: 0 (обозначает точное совпадение).

Нажмем OK.

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

Скопируем ячейку с формулой и вставим ее в том же столбце напротив всех остальных слов (можно дважды кликнуть на правый нижний угол заполненной ячейки, чтобы ее формула автоматически растянулась на весь столбец; выделение можно также растянуть на весь диапазон до конца таблицы, пользуясь комбинацией горячих клавиш Shift + Ctrl + стрелка вниз).

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

Шаг 3

Добавим в таблицу CorST столбец “IPM-RLC”.

Шаг 4

На вкладке Формулы в разделе “Ссылки и массивы” найдем формулу “ИНДЕКС” (в английской версии “INDEX”), и в открывшемся мастере формулы:

• выберите первую опцию

• Массив: перейдите на лист RLC и выделите столбец с IPM.
• Номер_строки: поставьте курсор на поле “Номер…” в таблице CorST
• Номер_столбца: оставьте пустым, так как мы работаем с одним столбцом.

Нажмите ОК.

Проверьте (с помощью поиска), что частота для данного слова указана правильно. Скопируем ячейку с формулой и вставим ее напротив всех остальных слов.

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

Шаг 5

Теперь все будет наоборот: добавим в таблицу RLC столбец “Номер строки, на которой слово стоит в CorST”.

Шаг 6

Для каждого слова в таблице RLC найдем его позицию в столбце со словами таблицы CorST и запишем в столбце “Номер строки…” (так же, как в Шаге 2).

Шаг 7

Теперь мы хотим взять из таблицы RLC только те слова, которых не нашлось в списке CorST. Для этого отфильтруем все ячейки со значением “#Н/Д” в столбце “Номер строки…”.

Делается это так:

• Выделяем всю таблицу, нажав на ее левый верхний угол.
• На вкладке “Данные” ищем функцию “Фильтр”, нажимаем.
• Выбираем нужную нам колонку “Номер строки, на которой слово стоит в CorST”, нажав на небольшую белую кнопку справа от ее названия.
• Создаем и применяем фильтр с условием “равно…” и значением “#Н/Д” (или “equals” “#N/A” в англоязычном интерфейсе).
• Готово, теперь у нас остались только нужные слова!

Если отфильтровать слова почему-то не удалось, можно попробовать отсортировать их по значению столбца “Номер строки, на которой слово стоит в RLC”. Так все слова со значением “#Н/Д” в этом столбце окажутся внизу или вверху таблицы, и их можно будет скопировать вручную.

Теперь добавим данные по этим словам в таблицу CorST (тут можно просто скопировать слова в столбец “Слова”, внизу под существующим списком, а затем скопировать значения IPM в столбец IPM-RLC). Примечание: если вам не удается вставить скопировать слова на другую вкладку, значит, вы пытаетесь вставить содержание целого столбца в часть другого столбца, что невозможно. Выделите именно диапазон ячеек от первого до последнего слова (и соответствующий диапазон частот).

Шаг 8

Теперь у нас в таблице CorST сведены все данные!

Давайте выделим нужные нам данные в отдельный лист. Создадим лист “merge”, вставим туда колонки Слово, IPM-CorST и IPM-RLC (используйте специальную вставку: только значений). В столбце IPM-RLC замените все вхождения #Н/Д на пустую строку. Укажите формат ячеек с IPM - числовой.

Отсортируйте таблицу по столбцу “Слово” (по возрастанию), а затем по столбцу “IPM-CorST” (по убыванию).

УРА!

Вопросы для проверки:

Вопросы находятся в этой форме. Ответьте на них.
При ответах на вопросы не сортируйте таблицу, а пользуйтесь фильтрами в нужных столбцах.