У цьому відео ми використовуємо MATCH, щоб знайти позицію елемента в таблиці, і використовуємо INDEX, щоб отримати значення в цій позиції. Ми працюємо поетапно, щоб ви могли побачити, як працюють обидві функції.
У цьому відео ми розглянемо, як налаштувати класичний двосторонній пошук за допомогою INDEX та Match.
Тут ми маємо список продавців із щомісячними показниками продажів.
Що ми хочемо зробити, це додати формулу в Q6, яка шукає та отримує номер продажу на основі назви та місяця вище.
Для цього ми використаємо функції INDEX та MATCH.
Спочатку я назву декілька діапазонів для полегшення читання формул. Я буду називати всю таблицю "даними", а потім використовувати "імена" для списку продавців. Зверніть увагу, що я включаю першу порожню клітинку в обидва імена. Це тому, що простіше використовувати одне й те саме джерело як для даних, так і для міток.
Нарешті я назву місяці. Знову ж я включу першу клітинку. Зараз у нас є 3 діапазони.
Далі побудуємо формулу доказу концепції, яка використовує INDEX для отримання значення на основі жорстко закодованих номерів рядків і стовпців. Масив - це дані, і я буду використовувати 2 як для рядка, так і для номера стовпця.
INDEX повертає 11882, що знаходиться на перетині другого рядка та другого стовпця.
Технічно INDEX повертає посилання на клітинку C5, але це тема для іншого дня.
Отже, тепер ми знаємо, що INDEX зробить цю роботу, нам просто потрібно з’ясувати, як за допомогою MATCH отримати потрібні номери рядків і стовпців.
Щоб це вирішити, я збираюся ввести формули MATCH окремо, а потім скласти їх разом з INDEX в кінці. По-перше, я введу ім’я та місяць, тож нам є з чим зрівнятися.
Для відповідності імені нам потрібне Q4 для значення збігу та "імена" для масиву підстановки. Тип відповідності дорівнює нулю, оскільки ми хочемо лише точні збіги.
Для відповідності місяця нам потрібне Q5 для значення збігу та "місяці" для масиву підстановки. Тип відповідності знову дорівнює нулю.
З Dove та Jan ми отримуємо рядок 8 і стовпець 2. І якщо ми перевіримо таблицю, це правильно.
Щоб завершити все, мені просто потрібно замінити жорстко закодовані значення у формулі INDEX на створені нами функції MATCH. Найпростіший спосіб зробити це - просто скопіювати формули та вставити їх назад у функцію INDEX у потрібному місці.
Формула відповідності імені входить для номера рядка, а формула відповідності місяця - для стовпця.
Тепер формула завершена, і буде шукати потрібний номер продажу, використовуючи як ім’я, так і місяць.
Коли ви розробляєте більш складну формулу вперше, це хороший підхід. Спочатку створіть свою формулу доказу концепції, а потім створіть потрібні вам допоміжні формули та переконайтеся, що все працює належним чином. Нарешті, поєднайте допоміжні функції з формулою доказу концепції.
Звичайно
Основна формулаПов’язані ярлики
Виділити поточну область Ctrl + A ⌘ + A Розширити виділення до останньої комірки вниз Ctrl + Shift + ↓ ⌃ + ⇧ + ↓ Розширити виділення до останньої комірки праворуч Ctrl + Shift + → ⌃ + ⇧ + → Перейти до верхнього краю області даних Ctrl + ↑ ⌃ + ↑ Копіювати виділені комірки Ctrl + C ⌘ + C Вставити вміст з буфера обміну Ctrl + V ⌘ + V
