Пов'язані списки в Google Sheets: як налаштувати залежні спадні списки | Google Sheets | Excel

У цій статті ми розглянемо, як створити залежні спадні списки в Google Sheets.

Залежні спадні списки - це коли вміст одного списку змінюється в залежності від вибору у іншому списку. Це дає можливість вибирати більш точні дані або фільтрувати/заповнювати звіти, виходячи з певного вибору у попередньому списку.

Покажу різні приклади залежних списків і поясню використання функцій: FILTER, INDIRECT та INDEX.

А також більш складний варіант (не для однієї клітинки), але як виявилось дуже затребуваний. Робимо так, щоб був цілий список з двох колонок (списків) де у другій значення будуть залежні від комірки навпроти із першої колонки.
Цей варіант зробимо з використанням таких функцій: ArrayFormula, TRANSPOSE, LEN.

Вибір, яким варіантом користуватись залежить від структури довідкових даних, тобто як ваші овочі, фрукти, напої розташовані. Поїхали вниз там буде зрозуміліше.

Варіант 1.

Довідникові дані розташовані у довгій таблиці, тобто значення в двох стовпцях, одне навпроти одного (парами), такі таблиці дуже зручно фільтрувати, саме тому використаємо функцію FILTER.

=FILTER( G2:G; A2= F2:F)

Як це працює:

Функція перевіряє кожне значення у стовпці F2
. Якщо для якогось рядка значення у стовпці F дорівнює значенню в комірці A2, то відповідне значення з стовпця G2
(того ж самого рядка) буде включено у результат.
Якщо умова не виконується для жодного рядка, формула повертає порожній результат.

Варіант 2.

Формула INDIRECT використовується для отримання посилання на комірку, вказану як текстовий рядок. У відео ми використовуємо формулу =INDIRECT(A7) для створення залежного списку, де вміст другого списку залежить від вибраного значення у першому списку.

Варіант 3.

Функція INDEX використовується для отримання значення з певної комірки у таблиці, використовуючи її номер рядка та стовпця.

Пишемо таку конструкцію:

=INDEX(

K2:M31;

;

MATCH(A12; K1:M1; 0)

)

Формула поверне весь стовпець з таблиці, який відповідає заголовку, що знаходиться у комірці A12.

Пояснення етапів виконання формули:

INDEX(K2:M31;;MATCH(A12;K1:M1;0)) — функція INDEX повертає значення з діапазону K2:M31, вибираючи комірку на основі конкретного рядка та стовпця. У цьому випадку ми вибираємо стовпець, але не вказуємо конкретний рядок (оскільки між двома крапками в середині формули пропущений параметр для рядка). Тобто, буде повернуто значення з усіх рядків у конкретному стовпці.
MATCH(A12;K1:M1;0) — функція MATCH використовується для пошуку позиції (індексу) значення, яке знаходиться в комірці A12, серед заголовків стовпців у діапазоні K1:M1. Результат функції буде позиція стовпця, де знаходиться відповідний заголовок, який збігається зі значенням A12.
- A12 — значення, яке ми шукаємо.
- K1— діапазон, де функція MATCH шукає збіги (це заголовки стовпців).
- 0 — точний збіг (функція MATCH шукає точний збіг значення).
INDEX(...;;MATCH(...)) — Оскільки не вказаний рядок у функції INDEX, формула повертає весь стовпець, який було знайдено за допомогою функції MATCH. Це означає, що для стовпця, чий заголовок збігається зі значенням у комірці A12, будуть повернуті всі значення з рядків діапазону K2.

Залежні спадні списки

Варіант 4. Коли потрібно динамічно витягувати відповідні діапазони даних на основі значення першої клітинки. Тобто другий спадний список змінюється в залежності від обраного значення в першому.
Відео-пояснення

=ArrayFormula(

IF(

LEN(E2:E);

TRANSPOSE(

IF(

TRANSPOSE(E2:E) = A1;

INDIRECT("Fruits");

IF(

TRANSPOSE(E2:E) = B1;

INDIRECT("Vegetables");

IF(

TRANSPOSE(E2:E) = C1;

INDIRECT("Drinks")

)

);

)

Формула перевіряє значення у стовпці E.

Для кожного рядка стовпця E вона порівнює значення з A1, B1 або C1.

Якщо значення з E дорівнює A1, B1 або C1, тоді виводяться дані з відповідного іменованого діапазону: "Fruits", "Vegetables" або "Drinks".

Якщо значення у стовпці E порожнє, виводиться порожнє значення.

Пояснення того, що робить ця формула:

ArrayFormula() — дозволяє виконувати операції над масивами даних, обробляючи одразу кілька рядків або стовпців без необхідності застосовувати формулу до кожної окремої клітинки.
LEN(E2:E) — перевіряє довжину тексту у стовпці E, тобто чи є значення у клітинках стовпця E. Якщо значення є (не порожнє), функція продовжує виконуватися.
TRANSPOSE(E2:E) — перетворює стовпець E2
на рядок для порівняння з іншими значеннями.
Перший рівень IF(TRANSPOSE(E2:E) = A1; INDIRECT("Fruits"); ...) — порівнює кожен елемент із стовпця E з вмістом клітинки A1. Якщо вони рівні, тоді використовується вміст діапазону, на який вказує діапазон з іменем "Fruits" (імовірно, на іншому аркуші чи десь у таблиці).
Другий рівень IF(TRANSPOSE(E2:E) = B1; INDIRECT("Vegetables"); ...) — якщо значення з E не дорівнює A1, то порівнюється з B1, і якщо це так, використовується діапазон з іменем "Vegetables".
Третій рівень IF(TRANSPOSE(E2:E) = C1; INDIRECT("Drinks"); ...) — якщо значення з E не дорівнює ні A1, ні B1, тоді порівнюється з C1, і в разі збігу використовується діапазон "Drinks".
"" — якщо значення у клітинці стовпця E порожнє, результатом формули буде порожній рядок.

Приклади за посиланням тут , відео на YouTube.

Page updated

Google Sites

Report abuse