VLOOKUP - це потужна функція. Але на одному зі своїх семінарів Power Excel я часто отримую запитання від когось, хто хоче знати, чи може VLOOKUP повернути всі відповідні значення. Як відомо, VLOOKUP із False як четвертим аргументом завжди повертає перший збіг, який він знаходить. На наступному скріншоті комірка F2 повертає 3623, оскільки це перший збіг, знайдений для завдання J1199.
Тоді питання, чи може VLOOKUP повернути всі збіги?
VLOOKUP не буде. Але інші функції можуть.
Якщо ви хочете підсумувати всі витрати з завдання J1199, ви б використали =SUMIFS($B$2:$B$53,$A$2:$A$53,G2),
Якщо у вас є текстові значення і ви хочете об’єднати всі результати в одне значення, ви можете використовувати =TEXTJOIN(", ",TRUE,IF($A$2:$A$53=G2,$C$2:$C$53,"")). Ця формула працює лише в Office 365 та Excel 2019.
Або, можливо, вам доведеться повернути всі результати для одного завдання в новий діапазон робочого аркуша. Зовсім нова =FILTER(B2:C53,A2:A53=K1,"None Found")функція, яка з’явиться в Office 365 у 2019 році, вирішить проблему:
Іноді люди хочуть виконати всі VLOOKUP і підсумувати їх. Якщо ваша пошукова таблиця відсортована, ви можете використовувати =SUM(LOOKUP(B2:B53,M3:N5)).
Якщо вам потрібно підсумувати всі VLOOKUP за допомогою версії VLOOKUP з точним збігом, вам потрібно мати доступ до динамічних масивів, щоб використовувати =SUM(VLOOKUP(B2:B53,M3:N5,2,TRUE)).
Щоб дізнатись більше про динамічні масиви, перегляньте динамічні масиви Excel прямо до точки.
Переглянути відео
Стенограма відео
Дізнайтеся Excel From, Підкаст Епізод 2247: Чи можете Ви повернути всі значення VLookUp?
Привіт Ласкаво просимо до мережі. Я Білл Джелен. Минулого тижня на моєму семінарі в Епплтоні, штат Вісконсін, з’явилося два запитання - обидва пов’язані. Вони сказали, привіт, як ми повернемо всі VLOOKUP, добре? У цьому випадку, як у J1199 є купа сірників, і вони, знаєте, хочуть повернути їх усі, і моє перше запитання, коли хтось запитує мене це, ну, що ти хочеш робити із сірниками? Це цифри, які ви хочете скласти, чи це текст, який ви хочете об’єднати? І це смішно. Два запитання на одному семінарі: одна людина хотіла скласти їх, а інша - об’єднати результати.
Тож давайте розглянемо обидва ці варіанти. Перегляньте в описі YouTube зміст, де ви можете перейти до іншого, якщо хочете побачити результат тексту.
Добре, отже, по-перше, якщо ми хочемо скласти їх усі, ми взагалі не будемо використовувати VLOOKUP. Ми будемо використовувати функцію під назвою SUMIF або SUMIFS, яка буде підсумовувати все, що відповідає цьому елементу. Отже, СУМІФИ. Ось числові значення, які ми хочемо підсумувати, і я натисну F4, щоб заблокувати це. Таким чином, коли я копіюю це, він буде продовжувати вказувати на той самий діапазон, а потім ми хочемо піти перевірити і перевірити, чи номер JOB у стовпці A, знову F4 там, = до значення ліворуч від нас - у цьому випадку E2 - і коли ми копіюємо це, ми побачимо ВСЬОГО для кожного елемента. (SUMIFS ($ B $ 2: $ B $ 53, $ A $ 2: $ A $ 53, E2))
Давайте просто трохи перевіримо тут. J1199. Всього 25365. Добре. Отже, це працює. Якщо це цифри, і ви хочете отримати всі цифри та скласти їх, перейдіть на SUMIF або SUMIFS, але якщо це текст, добре, зараз ця функція нова в Office 365 у лютому 2017 року. Отже, якщо у вас є Excel 2016 або Excel 2013 або Excel 2010, або будь-який із цих старих, ви не матимете цієї функції. Це функція під назвою TEXTJOIN. ТЕКСТЬ. Це ще одна функція від (Джо Макдейд - 01:50), який щойно приніс нам усі ті чудові формули динамічного масиву на Ignite у 2018 році, і Джо переконався, що TEXTJOIN буде працювати з масивами, що дійсно чудово.
Отже, роздільник тут буде, ПРОСТІР, безумовно ігнорувати ПУСТО. Ми хочемо ігнорувати EMPTY тут, оскільки ми збираємося створити багато порожніх місць у цій наступній частині, твердженні IF. ЯКЩО цей елемент над A2, F4, дорівнює = цьому номеру РОБОТИ тут, то я хочу відповідний елемент із стовпця C, F4, інакше, я хочу “” так. Закрийте це твердження IF. Закрийте TEXTJOIN. Чи потрібно натискати CONTROL + SHIFT + ENTER? Ні я не. Це приносить мені всі товари, які так збігаються, добре? Отже, повертаючи всі VLOOKUP, якщо ми хочемо їх підсумувати, так, якщо ми хочемо об’єднати їх, так. (= TEXTJOIN (“,”, True, IF ($ A $ 2: $ A $ 53 = E2, $ C $ 2: $ C $ 53, “”)))
Добре, зараз є ще одна можливість тут, коли люди запитують мене, чи можуть вони повернути всі VLOOKUP. Можливо, проблема полягає в тому, що ми хочемо розглянути тут кожну з цих витрат, з’ясувати ВАРТІСТЬ ОБРОБКИ, а потім підсумувати їх усі. Мовляв, я не хочу розміщувати VLOOKUP тут і VLOOKUP тут, і VLOOKUP тут, і VLOOKUP тут. Я просто хочу зробити їх взагалі, і в такому випадку ми будемо використовувати функцію SUM, а потім стару, стару функцію LOOKUP. LOOKUP каже, що ми будемо шукати всі ці значення у стовпці B. Мені тут не потрібен F4, оскільки я його ніде не копіюю. ,. Ось наша пошукова таблиця. ), закрийте СУМУ, і вона вимкнеться і зробить кожну ПЕРЕГЛЯД, а потім підсумує їх так. (= СУММА (ПЕРЕГЛЯД (B2: B53, K3: L5)))
Ну, привіт. Усі ці теми - це моя книга LIV: 54 найкращі поради всіх часів. Клацніть на i у верхньому правому куті, щоб дізнатись більше.
Отже, питання в тому, чи можете ви повернути всі VLOOKUP? Ну, начебто, але насправді не використовуючи VLOOKUP. Для її вирішення ми будемо використовувати SUMIF, TEXTJOIN, SUM або LOOKUP.
Ну, привіт. Я хочу подякувати вам, що завітали. Ми побачимось наступного разу для чергової трансляції від.
Знаєте, добре, я вже тиждень розмовляю про ці динамічні масиви. Я хотів зробити одне відео, де я не торкався динамічних масивів, бо знаю, що у багатьох людей їх ще немає, але ось ми. Це вихід. Знаєте, це не алфавіт. Це було б набагато краще, якби ми могли їх сортувати, і якщо у вас трапляються нові динамічні масиви, ви можете надіслати це у функцію SORT, SORT так і натиснути ENTER, і тепер результати будуть відсортовані так.
Знаєте, навіть ця формула могла б покращитися за допомогою динамічних масивів. Для пошуку потрібно використовувати значення TRUE. Що робити, якщо ви хочете скористатися помилкою? Ми могли б змінити це на VLOOKUP, шукати весь цей текст у цій таблиці, 2,. У цьому випадку я збираюся використовувати TRUE, але в іншому випадку ви можете використовувати FALSE. CONTROL + SHIFT + ENTER. Ні. Це просто спрацює, добре? (= СУММА (ПЕРЕГЛЯД (B2: B53, K3: L5,2, Правда))))
Динамічні масиви, що виходять на початку 2019 року, вирішать стільки проблем.
Дякуємо за проведення часу тут. Ми побачимось наступного разу для чергової трансляції від.
Завантажте файл Excel
Щоб завантажити файл Excel: can-you-return-all-vlookup-values.xlsx
Коли хтось запитує "Чи може VLOOKUP повернути всі збіги, відповідь - ні. Але є багато інших функцій, які можуть виконувати по суті те саме.
Думка дня в Excel
Я попросив своїх друзів Excel Master порадити їх щодо Excel. Сьогоднішня думка поміркувати:
"Нормалізуйте свої дані так, як хотіли б, щоб інші нормалізували ваші дані для вас"
Кевін Лербасс
