Зміст заняття
  1. Опис заняття
  2. Суть рішення
  3. Принцип дії
  4. Основна ідея реалізації
  5. Рішення:
  6. Теорія:
  7. 1. Пошук номера рядка
  8. 2. Упорядкування проміжних обчислень
  9. 3. Побудова фінальної формули
  10. 4. Остаточні штрихи
  11. Файл з прикладом
  12. Функції
  13. Посилання за темою

Заняття 1 з 3

Створення списків із автоматичним заповненням в Excel

У цій задачі створимо списки з автоматичним заповненням у табличному процесорі Excel: всі дані вводяться в один великий список і записи автоматично групуються в нові списки.

Опис заняття

Відеоверсія

Відео · YouTube

Текстова версія

У цій задачі створимо автоматично заповнювані списки за допомогою табличного процесора Excel. Усі дані вводяться в один великий список, з якого записи автоматично групуються по нових списках.

Результат автоматичного розподілу записів по списках
Результат автоматичного розподілу записів по списках

Записи в переліку ліворуч «сирі», тобто вони не згруповані, містять маркери (тобто вказівник номера списку) і сам запис; у списках праворуч — розподілені. Кількість у трьох списках взято як приклад, це число може бути довільним, як і назви «списки». Особливим плюсом є те, що, змінюючи маркер у «сирому переліку», можна змінювати місце розташування запису в згрупованих списках.

Реалізуємо такі автоматично заповнювані списки, детально пояснюючи етапи розв'язання задачі.

Суть рішення

Відеоверсія

Відео · YouTube

Текстова версія

Принцип дії

Користувачі, які вже працювали в Excel, очевидно помітять, що принцип дії таких динамічних списків дуже схожий на роботу функції ВПР (VLOOKUP) або більш просунутого аналога цієї функції — зв'язки ІНДЕКС (INDEX) і ПОИСКПОЗ (MATCH).

Насправді нам потрібно знайти в лівому стовпці «маркер» списку, тобто назву, і повернути елемент такого списку вже з правого стовпця. Однак тут є один нюанс: функція ВПР, як і згадана збірка-аналoг, поверне тільки перший запис, тобто максимум ми зможемо вибрати таким способом по одному елементу для кожного списку.

Відповідно, нам потрібно реалізувати щось на кшталт множинного ВПР, коли значення вибираються всі, а не тільки перше.

Основна ідея реалізації

Основу реалізації такого множинного вибору складатиме надзвичайно корисна функція ІНДЕКС (INDEX), яка повертає значення клітинки, що знаходиться на перетині вказаних рядка і стовпця. Стовпець у нас відомий із умови — це стовпець зі всіма значеннями (правий у «сирому» таблиці), а от номер рядка ми будемо підставляти динамічно.

Цю функцію часто використовують у зв'язці з іншою корисною функцією ПОИСКПОЗ (MATCH), проте зараз нам не потрібна зв'язка, оскільки номер рядка (а саме за пошук номера рядка в ІНДЕКС відповідає ПОИСКПОЗ) ми знайдемо окремо.

Рішення:

Теорія:

1. Пошук номера рядка

Відеоверсія

Відео · YouTube

Текстова версія

Для пошуку номера рядка скористаємося досить нехитрою конструкцією:

=ЕСЛИ($A2=S$1;СТРОКА(A2))

Принцип роботи цієї формули стає зрозумілим, якщо подивитися на наступне зображення:

Пошук номера рядка
Пошук номера рядка

У заголовках записуємо значення, що відповідають маркерам у першому стовпці неупорядкованого списку; у нашому випадку це: список1, список2, список3. Потім порівнюємо значення з заголовка з поточним маркером (у порівнянні використовуються змішані посилання, щоб не записувати ту саму формулу для трьох стовпців, а просто автозаповненням розтягнути її для 2-го і 3-го списків). Якщо умова виконується, тоді повертаємо номер рядка з початкового неупорядкованого списку; якщо умова хибна, формула поверне помилку.

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

2. Упорядкування проміжних обчислень

Відеоверсія

Відео · YouTube

Текстова версія

Ми отримали номери рядків, розкидані по всьому діапазону обчислень; нам потрібно їх зібрати, і для цього, по-перше, слід побудувати простий номерний ряд — банально пронумерувавши один стовпець, а по-друге, за допомогою функції НАИМЕНЬШИЙ (SMALL) зібрати всі значення по кожному стовпцю, відсортувавши за зростанням.

Функція НАИМЕНЬШИЙ схожа на знаходження мінімального значення, тобто функцію МИН, за тим винятком, що дозволяє знайти не лише мінімальне, а й 2-е, 3-е і т.д. найменші значення після мінімального.

Як можна здогадатися, допоміжний числовий ряд використовуватиметься для вказівки того, яке найменше значення за порядком слід повернути. Таким чином кінцева формула, що сортує знайдені номери рядків, виглядатиме так:

=НАИМЕНЬШИЙ(I:I;$L2)

Першим аргументом функції вказано посилання повністю на стовпець; якщо ви вказуватимете на діапазон, не забудьте зробити його зафіксованим (зробивши абсолютне або змішане посилання). Другий аргумент — це змішане посилання на допоміжний ряд, тобто в першому випадку, коли посилання йде на цифру «1», формула поверне мінімальне значення, потім — 2-ге після мінімального і т.д.

Загальна картина обчислень виглядає так:

Упорядкування чисел за допомогою функції НАИМЕНЬШИЙ
Упорядкування чисел за допомогою функції НАИМЕНЬШИЙ

Як і очікувалося, ми просто записали раніше розкидані значення з обчисленням номера рядка.

3. Побудова фінальної формули

Відеоверсія

Відео · YouTube

Текстова версія

Якщо ви раніше працювали з функцією ІНДЕКС (INDEX), то побудова фінальної формули не викличе жодних труднощів: фактично нам потрібно повернути значення клітинки, коли відомі і номер рядка, і номер стовпця (він із самого початку був відомий, оскільки лише один стовпець зі значеннями).

Отже, пишемо:

=ИНДЕКС($B:$B;M2;1)

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

У кінцевому підсумку отримуємо такий результат:

Результат застосування функції ІНДЕКС
Результат застосування функції ІНДЕКС

Все добре, окрім помилок, які з’являються в незаповнених клітинках. Помилку легко приховати, якщо «обгорнути» кінцеву формулу в функцію ЕСЛИОШИБКА (IFERROR), вказавши в якості другого аргументу порожню клітинку (просто подвійні лапки).

=ЕСЛИОШИБКА(ИНДЕКС($B:$B;M2;1);"")

І такий результат:

Результат використання функції ЕСЛИОШИБКА для перехоплення помилок
Результат використання функції ЕСЛИОШИБКА для перехоплення помилок

4. Остаточні штрихи

Відеоверсія

Відео · YouTube

Текстова версія

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

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

Процес створення випадаючого списку в Excel детально розглянуто в цій статті, тут ми загострювати на цьому увагу не будемо.

Файл з прикладом

Шановні користувачі, файл з прикладом ви можете також завантажити в гілці нашого форуму, де задати питання або просто обговорити матеріал.

Завантажити книгу

Функції

Формула Застосування в рамках даної задачі
ИНДЕКС (INDEX) Основа формули, використовується для зіставлення заголовків таблиці й заголовків діапазонів вихідного списку
НАИМЕНЬШИЙ (SMALL) Використовується для передачі першої клітинки плаваючого діапазону у ВПР

Допоміжні, тобто без яких можна обійтися:

Формула Застосування в рамках даної задачі
ЕСЛИОШИБКА (IFERROR) Використовується для надання естетичного вигляду таблиці, тобто заміни помилок на порожні значення клітинки.

Посилання за темою

Створення випадаючого списку в Excel

Функції роботи з даними в Excel

Як зробити так, щоб клітинки в Excel не заповнювалися нулями

Типи посилань у MS Excel