VLOOKUP je zmogljiva funkcija. Toda na enem izmed seminarjev za Power Excel pogosto dobim vprašanje od nekoga, ki želi vedeti, ali lahko VLOOKUP vrne vse ujemajoče se vrednosti. Kot veste, bo VLOOKUP s False kot četrtim argumentom vedno vrnil prvo ujemanje, ki ga najde. Na naslednjem posnetku zaslona celica F2 vrne 3623, ker je to prvo najdeno ujemanje za opravilo J1199.
Vprašanje je, ali lahko VLOOKUP vrne vse tekme?
VLOOKUP ne bo. Toda druge funkcije lahko.
Če želite sešteti vse stroške dela J1199, bi uporabili =SUMIFS($B$2:$B$53,$A$2:$A$53,G2),
Če imate besedilne vrednosti in želite vse rezultate združiti v eno vrednost, lahko uporabite =TEXTJOIN(", ",TRUE,IF($A$2:$A$53=G2,$C$2:$C$53,"")). Ta formula deluje samo v storitvah Office 365 in Excel 2019.
Morda boste morali vse rezultate za posamezno opravilo vrniti v nov obseg delovnega lista. Popolnoma nova =FILTER(B2:C53,A2:A53=K1,"None Found")funkcija, ki prihaja v Office 365 leta 2019, bo rešila težavo:
Včasih ljudje želijo izvesti vse VLOOKUP-ove in jih povzeti. Če je vaša iskalna tabela razvrščena, lahko uporabite =SUM(LOOKUP(B2:B53,M3:N5)).
Če boste morali vse VLOOKUP-e strniti z različico VLOOKUP-a z natančnim ujemanjem, boste morali za dostop imeti dostop do dinamičnih nizov =SUM(VLOOKUP(B2:B53,M3:N5,2,TRUE)).
Če želite izvedeti več o dinamičnih nizih, si oglejte Excelove dinamične nivoje naravnost do točke.
Oglejte si video
Video zapis
Learn Excel From, Podcast Episode 2247: Ali lahko vrnete vse vrednosti VLookUp?
Zdravo. Dobrodošli nazaj na netcast. Jaz sem Bill Jelen. Na mojem seminarju v Appletonu v Wisconsinu prejšnji teden sta se pojavili dve vprašanji - obe povezani. Rekli so, hej, kako naj vrnemo vse VLOOKUPE, v redu? V tem primeru, kot da ima J1199 kup vžigalic, ki jih, veste, želijo vse vrniti, in moje prvo vprašanje, kadar koli me kdo vpraša, je, no, kaj želite storiti s tekmami? So številke, ki jih želite sešteti, ali je besedilo, ki ga želite združiti? In smešno je. Vprašanji na istem seminarju je ena oseba želela sestaviti, druga pa združiti rezultate.
Oglejmo si torej obe. V opisu YouTuba si oglejte kazalo, kjer lahko preskočite na drugega, če želite videti rezultat besedila.
V redu, najprej, če jih želimo sešteti, sploh ne bomo uporabljali VLOOKUP-a. Uporabili bomo funkcijo, imenovano SUMIF ali SUMIFS, ki bo seštela vse, kar se ujema s tem elementom. Torej, POVZEMLJA. Tukaj so številčne vrednosti, ki jih želimo sešteti in pritisnil bom F4, da to zaklenem. Na ta način, ko bom to kopiral, bo še naprej kazal na isti obseg, nato pa želimo preveriti in preveriti, ali je številka JOB v stolpcu A, spet F4, = = vrednost levo od nas - v tem primeru E2 - in ko bomo to kopirali, bomo za vsak element videli SKUPAJ. (SUMIFS ($ B $ 2: $ B $ 53, $ A $ 2: $ A $ 53, E2))
Samo malo preverimo tukaj. J1199. Skupaj je 25365. V redu. Torej, to deluje. Če gre za številke in želite dobiti vse številke ter jih sešteti, preklopite na SUMIF ali SUMIFS, če pa gre za besedilo, je ta funkcija nova v Office 365 februarja 2017. Torej, če imate Excel 2016 ali Excel 2013 ali Excel 2010 ali katero koli od tistih starejših, te funkcije ne boste imeli. To je funkcija, imenovana TEXTJOIN. BESEDILO. To je še ena funkcija (Joe McDade - 01:50), ki nam je ravno pripeljal vse tiste formule dinamičnih nizov na Ignite leta 2018, Joe pa je poskrbel, da bo TEXTJOIN deloval z nizi, kar je res super.
Torej, ločilo tukaj bo, PROSTOR, vsekakor ignoriraj PRAZNO. Tu želimo prezreti EMPTY, ker bomo v naslednjem delu, stavku IF, ustvarili veliko praznin. ČE je ta postavka nad A2, F4, = tej številki JOB tukaj, potem želim ustrezen element iz stolpca C, F4, drugače pa želim "" tako. Zaprite to izjavo IF. Zaprite BESEDILO. Ali moram pritisniti CONTROL + SHIFT + ENTER? Ne jaz ne. Prinese mi vse izdelke, ki se tako ujemajo, v redu? Torej, vrnitev vseh VLOOKUP-ov, če jih želimo strniti, da, če jih želimo združiti, da. (= TEXTJOIN (“,”, True, IF ($ A $ 2: $ A $ 53 = E2, $ C $ 2: $ C $ 53, “”)))
No, tukaj je še ena možnost, ko me ljudje vprašajo, ali lahko vrnejo vse VLOOKUP-ove. Morda gre za vprašanje, pri katerem želimo tukaj poiskati vsakega od teh stroškov in ugotoviti STROŠKE OBRAVNAVE ter jih nato povzeti. Ne bi rad postavil VLOOKUP sem in VLOOKUP tukaj in VLOOKUP tukaj in VLOOKUP tukaj. Samo v celoti jih želim narediti in v tem primeru bomo uporabili funkcijo SUM in nato staro, staro funkcijo LOOKUP. LOOKUP pravi, da bomo vse te vrednosti poiskali v stolpcu B. Tu ne rabim F4, ker ga nikjer ne kopiram. ,. Tu je naša iskalna tabela. ), zaprite SUM, ta ugasne in naredi vsak posamezen VLOOKUP in nato vse tako sešteje. (= VSEBINA (POGLED (B2: B53, K3: L5)))
No, hej. Vse te teme so moja knjiga LIV: 54 največjih nasvetov vseh časov. Če želite izvedeti več, kliknite i v zgornjem desnem kotu.
Torej, vprašanje je, ali lahko vrnete vse VLOOKUP-ove? No, nekako, vendar dejansko ne uporabljam VLOOKUP-a. Za njegovo rešitev bomo uporabili SUMIF, TEXTJOIN ali SUM ali LOOKUP.
No, hej. Želim se vam zahvaliti, da ste se ustavili. Se vidimo naslednjič za še eno oddajo iz.
Veste, v redu, o teh dinamičnih nizih govorim že teden dni. Želel sem narediti en video, kjer se nisem dotaknil dinamičnih nizov, ker vem, da jih veliko ljudi še nima, ampak tu smo. To je izhod. Veste, te niso po abecedi. To bi bilo veliko bolje, če bi jih lahko razvrstili, in če imate slučajno nove dinamične nize, lahko to pošljete v funkcijo SORT, SORT tako in pritisnete ENTER, zdaj pa bodo rezultati razvrščeni tako.
Veste, tudi ta formula bi se lahko izboljšala z dinamičnimi nizi. Za iskanje morate uporabiti, TRUE. Kaj pa, če bi želeli uporabiti FALSE? To lahko spremenimo v VLOOKUP, poiščemo celotno besedilo v tej tabeli, 2,. V tem primeru bom uporabil TRUE, v drugem primeru pa lahko uporabite FALSE. CONTROL + SHIFT + ENTER. Ne. Samo šlo bo, v redu? (= SUM (OGLED (B2: B53, K3: L5,2, True)))
Dinamični nizi, ki bodo izšli v začetku leta 2019, bodo rešili toliko težav.
Hvala, ker ste se družili skozi izhod tukaj. Se vidimo naslednjič za še eno oddajo iz.
Prenesite datoteko Excel
Če želite prenesti datoteko excel: can-you-return-all-vlookup-values.xlsx
Ko nekdo vpraša "Ali lahko VLOOKUP vrne vsa ujemanja, je odgovor Ne. Vendar obstaja veliko drugih funkcij, ki lahko v bistvu naredijo isto stvar.
Excel Misel dneva
Za nasvet glede Excela sem prosil prijatelje Excel Excel. Današnja misel o premišljevanju:
"Normalizirajte svoje podatke, kot bi jih drugi normalizirali za vas"
Kevin Lehrbass
