Як знайти однакові значення в стовпчиках у "таблицях Google"

Коли нам потрібно вияснити чи є співпадіння у 2-х стовпцях таблиці (наприклад, стовпчики “A” і “D”), ми можемо скористатися формулою “=MATCH(A1; $D$1:$D$21; 0)”. Вставляємо цю формулу у будь-яку пусту комірку не із стовпчиків “A” і “D”, наприклад “F1”. Дана формула перевіряє, чи значення в комірці “A1” співпадає з будь-яким значенням комірок з діапазону “D1-D21”. Знаки “$” потрібні для того, щоб вказати фіксований діапазон комірок для перевірки. (Зобр. 1).

УВАГА!!! Якщо у комірок, що перевіряються, різний формат, то формула видасть значення “#N/A”. На вигляд значення у комірках можуть бути однакові. При зміні формату комірок, інформація в них може бути спотворена.

Зобр. 1 Формула в комірці "F1"

Вставивши формулу, натискаємо Enter. Формула видала значення “#N/A”. Це означає, що значення комірки "A1" не співпадає зі значенням будь-якої комірки із діапазону "D1-D21". Далі - протягнемо, натиснувши ліву клавішу мишки - від комірки "F1" до "F21". (Зобр. 2).

Зобр. 2 Всі комірки стовпчика "F" заповнені формулою "=MATCH(A1; $D$1:$D$21; 0)"

Як бачимо, там, де значення не співпали, з’явилося “#N/A”. Там, де значення співпали - навпроти комірки зі стовпчика “A” з’явилося цифрове значення. (Зобр. 2). Що означають ці цифри - розглянемо далі.

A10=D5

A11=D15

A14=D18

A15=D21

A16=D16

Тепер подивимося, як виглядає наша формула в якійсь іншій комірці стовпчика “F”. (Зобр. 3).

Зобр. 3 Формула змінилася відповідно до комірки в якій вона знаходиться

Отже, бачимо, що у комірці “F9” у нашій формулі вже перевіряється комірка “A9”. Діапазон “D1-D21” залишається незмінним, оскільки ми його “зафіксували” знаками “$”. Так буде і у всіх решта комірках стовпчика “F”. (Зобр. 3).

Якщо значення в діапазоні “D1-D21” повторюються і якщо для них є співпадіння у стовпчику "A", то у комірці стовпчика “F”, навпроти відповідної комірки зі стовпчика “A”, буде тільки значення, що відповідає номеру рядка стовпчика “D” при першому співпадінні цієї комірки стовпчика "A" з діапазоном “D1-D21”. (Дані в таблиці трохи змінені для наглядності. Зобр. 4). При зміні даних у стовпчиках, що перевіряються - формули автоматично перераховуються відповідно до нових даних.

Зобр. 4 Перше співпадіння стовпчика "A" зі стовпчиком "D", виділене жовтим

A10=D5=D11

Якщо значення у стовпчику “A” повторюються і якщо для них є співпадіння у стовпчику "D", то у комірках зі стовпчика “F”, навпроти відповідних комірок зі стовпчика “A”, будуть тільки значення, що відповідають номеру рядка стовпчика "D" при першому співпадінні цієї комірки стовпчика "A" з діапазоном “D1-D21”. (Дані в таблиці трохи змінені для наглядності. Зобр. 5, 6).

Зобр. 5 2 співпадіння стовпчика "A" зі стовпчиком "D", виділені жовтим

A16=A17=D17

Зобр. 6 Приклад, коли в кожному стовпчику є однакові комірки

A10=A21=D5=D11

Ця ж формула однаково працює з текстовими, числовими та текстово-числовими значеннями. Застосуймо її до стовпчиків "B" і "E": “=MATCH(B1; $E$1:$E$21; 0)”. (Дані в таблиці трохи змінені для наглядності. Зобр. 7).

Зобр. 7 Приклад перевірки стовпчиків з текстово-числовими значеннями

Отже, формула типу “=MATCH(B1; $E$1:$E$21; 0)” чудово підходить для того, щоб визначити співпадіння стовпчиків “B” і “E”. Однак, повторю ще раз: якщо значення в діапазоні “E1-E21” повторюються, то у комірці стовпчика “G”, навпроти відповідної комірки зі стовпчика “B”, буде тільки значення, що відповідає номеру рядка при першому співпадінні з діапазоном “E1-E21”. Тому, якщо потрібно, можна використати нашу формулу так: “=MATCH(E1; $B$1:$B$21; 0)”. Вставимо її в комірку “H1” (Таблиця трохи змінена для наглядності. Зобр. 8).

Зобр. 8 Використовуємо формулу "=MATCH(B1; $E$1:$E$21; 0)" в зворотньому напрямку, тобто "=MATCH(E1; $B$1:$B$21; 0)"

Тепер ми бачимо, що у стовпчику “E” є однакові комірки “E12” та “E15”. Співпадіння стовпчика E зі стовпчиком B:

E12=E15=B4;

E18=B10;

E19=B16;

E21=B3.

Кількість цифрових значень у стовпчиках "G" і "H" - різні. Це тому, що у стовпчику "B" немає однакових значень. Але у стовпчику "E" є 2 однакові значення. Тому: 4 співпадіння * 1 однакове значення = 4; 3 співпадіння + 1 співпадіння * 2 однакові значення = 5; 4 < 5.