Це ще один приклад швидкості формули. Скажімо, що вам потрібно зробити 12 стовпців VLOOKUP. Ви можете зробити це швидше, використовуючи одну функцію MATCH та 12 INDEX.
На наступному малюнку вам доведеться виконати 12 функцій VLOOKUP для кожного номера рахунку. VLOOKUP є потужним, але для обчислення потрібно багато часу.
Крім того, формулу потрібно редагувати в кожній клітинці під час копіювання. Третій аргумент повинен змінитися з 2 на 3 для лютого, потім 4 для березня тощо.
Одним з обхідних шляхів є додавання рядка з номерами стовпців. Тоді 3-й аргумент VLOOKUP може вказувати на цей рядок. Принаймні ви можете скопіювати ту саму формулу з B4 і вставити в C4: M4 перед копіюванням цілого набору.
Але тут набагато швидший підхід. Додайте новий стовпець B з Де? як заголовок. Стовпець B містить функцію MATCH. Ця функція дуже схожа на VLOOKUP: Ви шукаєте значення у форматі A4 у стовпці P4: P227. Значення 0 в кінці схоже на значення False в кінці VLOOKUP. Він вказує, що ви хочете точно відповідати. Ось велика різниця: MATCH повертається там, де знайдено значення. Відповідь 208 говорить, що A308 - це 208-я комірка в діапазоні P4: P227. З точки зору повторного часу, MATCH та VLOOKUP приблизно рівні.
Я чую, про що ти думаєш. “Яка користь знати, де щось знаходиться? У мене ніколи не зателефонував менеджер і запитав: "У якому рядку ця дебіторська заборгованість?"
Хоча люди рідко запитують, в якому рядку щось знаходиться, функція INDEX може використовувати це положення. Наступна формула вказує Excel повернути 208-й елемент із Q4: Q227.
Під час копіювання цієї формули масив значень переміщується по таблиці пошуку. Для кожного рядка ви виконуєте одну функцію MATCH та 12 INDEX. Функція INDEX неймовірно швидка в порівнянні з VLOOKUP. Весь набір формул обчислить на 85% швидше, ніж 12 стовпців VLOOKUP.
Переглянути відео
- Скажімо, що вам потрібно зробити 12 стовпців VLOOKUP
- Обережно використовуйте знак одного долара перед стовпцем значення пошуку
- Обережно використовуйте чотири знаки долара для таблиці пошуку
- Ви все ще жорстко кодуєте аргумент третього стовпця.
- Одним із загальних рішень є додавання рядка допоміжних комірок із номером стовпця.
- Іншим менш ефективним рішенням є використання COLUMN (B2) всередині формули VLOOKUP.
- Але робити 12 VLOOKUP для кожного рядка дуже неефективно
- Натомість додайте допоміжний стовпець із заголовком WHERE і зробіть один матч.
- МАТЧ займає стільки часу, скільки VLOOKUP за січень.
- Потім можна використовувати 12 функцій INDEX. Вони неймовірно швидкі в порівнянні з VLOOKUP.
- INDEX вказуватиме на один стовпець відповідей із $ перед рядками.
- INDEX вказуватиме на допоміжний стовпець із символом $ перед стовпцем.
Стенограма відео
Дізнайтеся Excel із подкасту, серія 2028 - Заміна багатьох VLOOKUP на один MATCH!
Клацніть на "i" у верхньому правому куті, щоб перейти до списку відтворення, і я підкастую всю цю книгу!
Ей, ласкаво просимо до мережі, я Білл Джелен! Ну це класична проблема, ми повинні робити VLOOKUP один раз на місяць, так? І тут ви можете бути неймовірно обережними, натиснувши F4 3 рази, щоб зафіксувати це до стовпця, а потім натиснути F4, коли зафіксуєте цілий рядок. Але коли ви дійдете до цього пункту, 2, FALSE, що 2 жорстко закодовано, і коли ви копіюєте це поперек, вам доведеться відредагувати 2 до 3, так? Зараз, один неефективний спосіб зробити це, спосіб, який мені не подобається, - це використовувати стовпець B1. Стовпець B1, звичайно, 2, але коли ви його копіюєте, переконайтеся, що він зміниться на стовпець C1, який дорівнює 3, але подумайте про це, і це постійно з’ясовує номер стовпця знову і знову. Отже, те, що я бачу, як люди роблять, і чому, ви знаєте, віддають перевагу більше, ніж стовпцям, це те, що ми будемо Ctrl-перетягувати це,помістіть цифри 2-13 там, у допоміжну комірку, а потім, дійшовши до цієї точки, піднімаємося вгору і вказуємо номер стовпця. Натисніть F4 2 рази, щоб зафіксувати його на рядку,, FALSE тощо. Але навіть за допомогою цього методу VLOOKUP неймовірно неефективний, тому що він повинен шукати всі ці елементи тут, поки не знайде A308, а це і є показник B4. Потім він переходить до C4, він забуває, що він просто пішов і подивився, і все починається спочатку, добре. Отже, у вас є одна з найповільніших функцій у всьому Excel, VLOOKUP, FALSE, що робиться знову і знову і знову для одного і того ж елемента.тому що він повинен шукати всі ці елементи тут, поки не знайде A308, і це фігура B4. Потім він переходить до C4, він забуває, що він просто пішов і подивився, і все починається спочатку, добре. Отже, у вас є одна з найповільніших функцій у всьому Excel, VLOOKUP, FALSE, що робиться знову і знову і знову для одного і того ж елемента.тому що він повинен шукати всі ці елементи тут, поки не знайде A308, і це фігура B4. Потім він переходить до C4, він забуває, що він просто пішов і подивився, і все починається спочатку, добре. Отже, у вас є одна з найповільніших функцій у всьому Excel, VLOOKUP, FALSE, що робиться знову і знову і знову для одного і того ж елемента.
Отже, набагато швидший шлях, ми збираємось вставити допоміжний стовпець, і цей допоміжний стовпець я називаю Де? Як там, де чорт A308? Ми використаємо = MATCH, шукаємо A308 у першому рядку таблиці, натискаємо F4 там,, 0 для точного збігу, добре, це говорить нам, що “Гей, дивись, це в рядку, 6, як це чудово? " Але коли ми копіюємо, бачите, це постійно в різних місцях. Добре, зараз цей матч триває стільки, скільки триває січневий VLOOKUP, там вони навіть мертві, але ось дивовижне. Звідти нам ніколи не доведеться робити VLOOKUP для решти рядка, ми могли б просто зробити = INDEX, INDEX говорить “Ось масив відповідей”. Я збираюся піти до січневих камер і дуже обережно натиснути F4 2 рази, щоб заблокувати його до 4: 227,але Q можна змінювати під час руху. Кома, а потім вона хоче знати, який рядок, ну це буде відповідь у B4, я натисну F4 3 рази, щоб отримати $ перед B, добре, скопіюйте це поперек.
Ця формула, ці формули ІНДЕКСУ, ці 12 відбудуться менш ніж за час, необхідний для проведення ЛЮТНЬОГО ПЕРЕГЛЯДУ, добре. Якщо ми встановимо на це таймер Чарльза Вільямса, вся ця річ обчислить приблизно 14% часу 12 ВОЛОКУПІВ. Ваш менеджер не хоче бачити Де? Чудово, просто сховай цю колонку, все продовжує працювати, гаразд, це прекрасний спосіб пришвидшити 12-місячний або 52-тижневий VLOOKUP. Добре, ця порада та ще стільки підказок є в цій книзі. Клацніть на "i" у верхньому правому куті, і ви зможете придбати книгу, електронну книгу 10 доларів, 25 доларів за друковану книгу, добре.
Отже, сьогодні у нас виникла проблема, коли в 12 стовпцях VLOOKUP ви можете обережно ввести $, але тоді цей 3-й аргумент все ще має бути жорстко закодований. Ви можете використовувати стовпець (B2), я не прихильник цього, тому що є сотні рядків * 12 стовпців, де це обчислюється знову і знову. Просто використовуйте допоміжну комірку поспіль, поставте цифри 2-12 і вкажіть на це, проте це все ще неефективно, однак, оскільки VLOOKUP після того, як з’ясує січень, він повинен розпочатись на початку лютого. Тому я рекомендую додати стовпець із заголовком "Де?" і зробити там один матч. Цей МАТЧ займає стільки часу, скільки VLOOKUP на січень, але тоді 12 функцій INDEX займуть менше часу, ніж VLOOKUP на лютий, і ви обрізали цілу купу часу. Знову ж, обережно з $ у функції INDEX в обох місцях, одне безпосередньо перед рядками,а інший перед колонками - змішане посилання в обох.
Гей, я хочу подякувати вам за заїзд, ми побачимось наступного разу для ще одного трансляції від
Завантажити файл
Завантажте зразок файлу тут: Podcast2028.xlsx
