Рон хоче зробити купу VLOOKUP і підбити результати. Існує одно-формульне рішення цієї проблеми.
Рон запитує:
Як ви можете підсумувати всі VLOOKUP, не виконуючи кожного окремого пошуку?
Багато людей знайомі з:
=VLOOKUP(B4,Table,2,True)
Якщо ви робите приблизну версію VLOOKUP (де ви вказуєте True як четвертий аргумент), ви також можете зробити LOOKUP.
Пошук є непарним, оскільки повертає останній стовпець у таблиці. Ви не вказуєте номер стовпця. Якщо ваша таблиця працює від E4 до J8, і ви хочете отримати результат із стовпця G, ви повинні вказати E4: G4 як таблицю пошуку.
Ще одна відмінність: ви не вказуєте True / False як четвертий аргумент, як це було б робити у VLOOKUP: функція LOOKUP завжди робить приблизну версію VLOOKUP.
Навіщо турбуватися про цю давню функцію? Оскільки пошук має особливий фокус: ви можете шукати всі значення одночасно, і він їх підсумовуватиме. Замість того, щоб передавати одне значення, наприклад B4, як перший аргумент, ви можете вказати всі свої значення =LOOKUP(B4:B17,E4:F8). Так як це буде повертати 14 різних значень, ви повинні обернути функцію в функції - оболонки , такі як =SUM( LOOKUP(B4:B17,E4:F8))або =COUNT(LOOKUP(B4:B17,E4:F8))або =AVERAGE( LOOKUP(B4:B17,E4:F8)). Пам’ятайте, вам потрібна функція Wrapper, але не функція rapper. =SNOOPDOGG(LOOKUP(B4:B17,E4:F8))не буде працювати.
Існує справді поширена помилка, якої ви хочете уникнути. Після введення або редагування формули не натискайте Enter! Натомість виконайте цю трюкову роботу з трьома пальцями. Утримуйте Ctrl і Shift. Утримуючи Ctrl і Shift, натисніть Enter. Тепер ви можете відпустити Ctrl і Shift. Ми називаємо це Ctrl + Shift + Enter, але насправді він повинен бути правильно визначений: натисніть і утримуйте Ctrl + Shift, натисніть Enter, відпустіть Ctrl + Shift. Якщо ви зробили це правильно, формула з’явиться в рядку формул в оточенні фігурних дужок:(=SUM(LOOKUP(B4:B17,E4:F8)))
Бічна примітка
LOOKUP також може робити еквівалент HLOOKUP. Якщо ваша пошукова таблиця ширша, ніж висока, LOOKUP переключиться на HLOOKUP. У разі краватки … таблиці розміром 8x8 або 10x10, LOOKUP розгляне стіл як вертикальний.
Перегляньте відео нижче або вивчіть цей знімок екрана.
Переглянути відео
Стенограма відео
Дізнайтеся Excel з подкасту, епізод 2184: Підсумуйте всі пошуки.
Привіт, ласкаво просимо назад до трансляції, я Білл Джелен. Сьогоднішнє запитання Рона про використання старої, старої програми LOOKUP. І це з моєї книги Excel 2016 In Depth.
Скажімо, у нас тут була купа продуктів, і нам довелося використовувати таблицю пошуку. І для кожного товару ми мусимо, знаєте, отримати значення з таблиці пошуку та підсумувати це. Ну, типовим способом є те, що ми використовуємо VLOOKUP-- = VLOOKUP для цього продукту в цій таблиці. Я натисну F4, зафіксую це і з другим значенням. І в цьому конкретному випадку, оскільки кожне значення, яке ми шукаємо, знаходиться в таблиці, а таблиця відсортована, безпечно використовувати TRUE. Зазвичай я ніколи не використовував би TRUE, але в цьому епізоді ми будемо використовувати TRUE. Отже, я отримую всі ці значення, а потім тут, Alt + =, отримуємо загальну кількість, так? Але що, якщо вся наша мета - просто отримати 1130? Нам не потрібні всі ці цінності. Нам просто потрібен цей результат.
Ну, гаразд, тепер є стара, стара функція, яка існувала ще з часів Visical, називається LOOKUP. Не VLOOKUP. Не HLOOKUP, просто LOOKUP. І здається, спочатку схоже на VLOOKUP - ви вказуєте, яке значення ви шукаєте, і таблицю пошуку, натискаєте F4, але тоді ми закінчуємо. Нам не потрібно вказувати, який стовпець, оскільки LOOKUP просто переходить до останнього стовпця таблиці. Якщо у вас була таблиця із семи стовпців, і ви хочете знайти четверте значення, ви просто вказали б стовпці з першого по чотири. Добре? Отже, якою б не була остання колонка, це те, що вона буде шукати. І ми не повинні вказувати, FALSE або, TRUE, тому що він завжди використовує, TRUE; немає, FALSE версії. Добре?
Отже, ви повинні розуміти, якщо ви робите VLOOKUP, я завжди використовую FALSE в кінці, але в цьому випадку це короткий список - ми знаємо, що все в списку є в таблиці. Нічого не бракує, і таблиця відсортована. Добре? Отже, це дасть нам той самий результат, який ми маємо для VLOOKUP.
Чудово, я хочу скопіювати це вниз: Alt + =. Гаразд Але це нам нічого не купує, тому що ми все одно повинні помістити всі формули, а потім функцію SUM. Найкрасивіше, що LOOKUP може зробити трюк, якого VLOOKUP не може зробити, добре? І це робити всі пошуки відразу. Отже, де я надсилаю це до функції SUM, коли я кажу LOOKUP, що таке елемент пошуку? Ми хочемо переглянути всі ці речі, коми, а потім ось таблиця - і нам не потрібно натискати клавішу F4, тому що ми нікуди не збираємося копіювати, є лише одна формула - закрийте ПЕРЕГЛЯД, закрийте сума.
Гаразд, ось тут місце, де все може зіпсуватися: Якщо ви просто натиснете тут Enter, ви отримаєте 60, добре? Тому що це просто збирається піти зробити перший. Що вам потрібно зробити, це утримувати три магічні натискання клавіш, і це Ctrl + Shift - я утримую Ctrl + shift лівою рукою, я продовжую їх утримувати, і натискаю ENTER правою рукою, і він буде робити всю математику VLOOKUP. Хіба це не чудово? Зверніть увагу, що в рядку формул тут, або в тексті формули, навколо нього розміщені фігурні дужки. Ви не вводите ці фігурні дужки, Excel вкладає ці фігурні дужки, щоб сказати: "Гей, ти натиснув Ctrl + Shift + Enter для цього".
Зараз, привіт, ця тема та багато інших тем є у цій книзі: Power Excel with, видання 2017 року. Клацніть на "Я" там, у верхньому правому куті, щоб прочитати більше про книгу.
Сьогодні питання від Рона: Як ви можете підсумувати всі ПЕРЕГЛЯДИ? Тепер більшість людей знає VLOOKUP, де ви вказуєте значення підстановки, таблицю, який стовпець, а потім, TRUE або, FALSE. І якщо ви робите - якщо ви претендуєте на - ІСТИННУ версію VLOOKUP. тоді ви також можете зробити цей старий ПЕРЕГЛЯД. Це дивно, оскільки повертає останній стовпець таблиці, ви не вказуєте номер стовпця і не говорите TRUE або FALSE. Це завжди ІСТИНА. Чому ми повинні використовувати це? Оскільки у нього є особливий фокус: ви можете зробити усі значення пошуку одночасно, і це їх підсумує. Вам потрібно натиснути Ctrl + Shift + Enter після введення цієї формули, інакше вона не буде працювати. А потім на виході я покажу вам ще одну хитрість для LOOKUP.
Ну, привіт, я хочу подякувати Рону за те, що він надіслав це запитання, і я хочу подякувати вам за те, що завітали. Побачимось наступного разу для чергової трансляції від.
Добре, поки ми говоримо про LOOKUP, інша річ, яку LOOKUP може зробити: Ви знаєте, у нас є VLOOKUP для вертикальної таблиці, як ця, або HLOOKUP для горизонтальної таблиці, як ця; LOOKUP може піти в будь-який бік. так що ми можемо сказати, привіт, ми хочемо = ПЕРЕГЛЯНУТИ це значення, D, у цій таблиці, а оскільки таблиця ширша, ніж висока, LOOKUP автоматично перемикається на версію HLOOKUP, так? Отже, у цьому випадку, оскільки ми вказуємо 3 рядки на 5 стовпців, це зробить HLOOKUP. І оскільки останній рядок тут - це цифри, він принесе нам це число. Отже, ми маємо D, Date, нам 60. Гаразд. Якби я вказав таблицю, яка переходила лише до рядка 12, тоді я отримав би назву товару. Добре? Тож це якась цікава цікава функція. Я думаю, що довідка Excel казала: "Гей, не використовуй цю функцію", але там "s певний час, коли ви можете використовувати цю функцію.
Фотографія заголовка: grandcanyonstate / pixabay
