Skip to content

Latest commit

 

History

History
136 lines (78 loc) · 9.45 KB

Day09-Spreadsheets-2.md

File metadata and controls

136 lines (78 loc) · 9.45 KB

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

Предположим, мы хотим объединить две имеющиеся у нас таблицы в одну. Как это сделать? Сегодня мы разберем на примере.

Например, мы имеем частотные списки слов в из двух корпусов, КРУТ и Русского учебного корпуса - частотные списки обрезаны по частоте 3. (скачать таблицы)

Таблица corst_freq

таблица 1.

Таблица rlc_freq

таблица 2

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

14

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

Шаг 1.

Добавим в таблицу corst_freq столбец "Номер строки, на которой слово стоит в rlc_freq".

3

Шаг 2.

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

  • найти его в столбце "Слово" таблицы rlc_freq
  • определить номер строки, на которой он стоит
  • записать этот номер в столбец "Номер..." таблицы corst_freq

Чтобы в Excel выполнить эти три пункта, на вкладке Формулы найдем раздел "Ссылки и массивы" и в нем формулу "ПОИСКПОЗ" (в английской версии "MATCH").

4

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

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

Нажмите OK.

5

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

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

6

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

Шаг 3

Добавим в таблицу corst_freq столбец "IPM_rlc".

7

Шаг 4

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

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

8

  • Массив: перейдите на лист rlc_freq и выделите столбец с IPM.
  • Номер_строки: поставьте курсор на поле "Номер..." в таблице corst_freq
  • Номер_столбца: оставьте пустым, так как мы работаем с одним столбцом.

9

Нажмите ОК.

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

10

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

Шаг 5

Теперь все будет наоборот: добавим в таблицу rlc_freq столбец "Номер строки, на которой слово стоит в corst_freq".

Шаг 6

Для каждого слова в таблице rlc_freq найдем его позицию в таблице corst_freq и запишем в столбце "Номер строки..." (так же, как в Шаге 2).

11

Шаг 7

Теперь мы хотим взять из таблицы rlc_freq только те слова, которых не нашлось в таблице corst_freq. Для этого:

  • добавим колонку "не_нашлось" в таблице rlc_freq
  • На вкладке Формулы в разделе "Логические" найдем формулу "ЕСЛИОШИБКА", и в открывшемся мастере формулы:
  • Значение: выбрать ячейку из столбца "Номер... в corst_freq"
  • Значение_если_ошибка: пустая строка (две кавычки подряд, как в питоне)

12

  • Нажмите ОК
  • Скопируем ячейку с формулой и вставим ее напротив всех остальных слов.
  • Создать фильтр в колонке "не_нашлось": оставить только те строки в которых пустое значение.

13

Добавим данные по этим словам в таблицу corst_freq (тут можно просто скопировать слова в столбец "Слова", а затем скопировать значения IPM в столбец "IPM_rlc").

Шаг 8

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

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

Отсортируйте таблицу по столбцу "Слово", а затем по столбцу "IPM_corst".

14

УРА!

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

Ответы нужно писать в эту форму.

  1. Какие слова довольно частотны в RLC, но не встречаются в CORST? (приведите 6 самых частотных слов)

  2. Какие слова, наоборот, частотны в CORST, но не встречаются в RLC? (приведите 6 самых частотных слов)

  3. Различаются ли первые 10 слов в таблице, отсортированной по столбцу "IPM_corst" по убыванию, и в таблице, отсортированной по столбцу "IPM_rlc" по убыванию?

Полезное

Есть вот такой частотный словарь: http://dict.ruslang.ru/freq.php