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 с версията на Exact Match на VLOOKUP, ще трябва да имате достъп до динамични масиви, за да ги използвате =SUM(VLOOKUP(B2:B53,M3:N5,2,TRUE)).
За да научите повече за динамичните масиви, разгледайте динамичните масиви на Excel направо до точката.
Гледам видео
Видео транскрипция
Научете Excel от, Подкаст Епизод 2247: Можете ли да върнете всички стойности на VLookUp?
Хей. Добре дошли отново в netcast. Аз съм Бил Джелен. Два въпроса се появиха на моя семинар в Апълтън, Уисконсин миналата седмица - и двамата свързани. Те казаха, хей, как да върнем всички 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. TEXTJOIN. Това е друга функция от (Джо Макдейд - 01:50), която току-що ни донесе всички тези страхотни формули за динамични масиви в Ignite през 2018 г., а Джо се увери, че TEXTJOIN ще работи с масиви, което е наистина страхотно.
И така, разделителят тук ще бъде, ПРОСТРАНСТВО, игнорирайте ПЪСТО определено. Искаме да игнорираме EMPTY тук, защото ще генерираме много празнота в тази следваща част, изявлението IF. АКО този елемент над A2, F4, е = към този JOB номер тук, тогава искам съответния елемент от колона C, F4, в противен случай искам „“ така. Затворете това изявление IF. Затворете TEXTJOIN. Трябва ли да натискам CONTROL + SHIFT + ENTER? Не, аз не. Носи ми всички продукти, които съвпадат така, нали? Така че, връщайки всички VLOOKUP, ако искаме да ги сумираме, да, ако искаме да ги обединим, да. (= TEXTJOIN (“,”, True, IF ($ A $ 2: $ A $ 53 = E2, $ C $ 2: $ C $ 53, “”)))
Добре, сега има още една възможност тук, когато хората ме питат дали могат да върнат всички VLOOKUPs. Може да е проблем, при който искаме да разгледаме всеки от тези разходи тук и да разберем РАЗХОДИТЕ ЗА РАБОТА и след това да ги обобщим. Като, не искам да поставям VLOOKUP тук и VLOOKUP тук и VLOOKUP тук и VLOOKUP тук. Просто искам да ги направя изцяло и в този случай ще използваме функцията SUM и след това старата, старата функция LOOKUP. LOOKUP казва, че ще търсим всички тези стойности в колона Б. Тук не ми трябва F4, защото не го копирам никъде. ,. Ето нашата справочна таблица. ), затворете SUM и той изгасва и прави всеки отделен VLOOKUP и след това ги сумира така. (= СУММА (ПРЕГЛЕД (B2: B53, K3: L5)))
Е, хей. Всички тези теми са моята книга LIV: 54-те най-добри съвета на всички времена. Щракнете върху i в горния десен ъгъл, за да научите повече.
И така, въпросът е можете ли да върнете всички VLOOKUPs? Е, нещо като, но всъщност не се използва VLOOKUP. Или ще използваме SUMIF, TEXTJOIN, или SUM или LOOKUP, за да го решим.
Е, хей. Искам да ви благодаря, че се отбихте. Ще се видим следващия път за поредното излъчване от.
Знаете ли, добре, говоря за тези динамични масиви от една седмица. Исках да направя едно видео, където не съм докосвал динамични масиви, защото знам, че много хора все още ги нямат, но ето ни. Това е изхода. Знаете ли, те не са по азбучен ред. Това би било много по-добре, ако можем да ги сортираме и ако случайно имате новите динамични масиви, можете да изпратите това във функцията СОРТИРАНЕ, СОРТИРАНЕ така и натиснете ENTER и сега резултатите ще бъдат сортирани така.
Знаете ли, дори тази формула може да се подобри с динамичните масиви. Търсенето изисква да използвате TRUE. Ами ако искате да използвате, FALSE? Можем да променим това на VLOOKUP, да търсим целия този текст в тази таблица, 2,. В този случай ще използвам TRUE, но в друг случай може да използвате FALSE. CONTROL + SHIFT + ENTER. Не. Просто ще проработи, нали? (= SUM (VLOOKUP (B2: B53, K3: L5,2, True)))
Динамичните масиви, излизащи в началото на 2019 г., ще решат толкова много проблеми.
Благодаря, че излязохте през изхода тук. Ще се видим следващия път за поредното излъчване от.
Изтеглете Excel файла
За да изтеглите файла на Excel: can-you-return-all-vlookup-values.xlsx
Когато някой попита „Може ли VLOOKUP да върне всички съвпадения, отговорът е Не. Но има много други функции, които могат да направят по същество същото.
Мисъл на деня в Excel
Помолих приятелите си в Excel Master за съвети относно Excel. Днешната мисъл за размисъл:
„Нормализирайте данните си така, както бихте накарали другите да нормализират техните данни вместо вас“
Кевин Лербас
