Ron želi narediti kup VLOOKUP-ov in sešteti rezultate. Za ta problem obstaja eno-formula rešitev.
Ron vpraša:
Kako lahko seštejete vse VLOOKUP-e, ne da bi opravili vsako posamezno iskanje?
Veliko ljudi pozna:
=VLOOKUP(B4,Table,2,True)
Če izvajate približno različico VLOOKUP z ujemanjem (kjer kot četrti argument navedete True), lahko naredite tudi LOOKUP.
Iskanje je čudno, ker vrne zadnji stolpec v tabeli. Ne določite številke stolpca. Če se tabela izvaja od E4 do J8 in želite rezultat iz stolpca G, za iskalno tabelo določite E4: G4.
Druga razlika: kot četrti argument ne navedete True / False, kot bi to storili v VLOOKUP: funkcija LOOKUP vedno naredi različico VLOOKUP Približno ujemanje.
Zakaj bi se mučil s to starodavno funkcijo? Ker ima Lookup poseben trik: lahko poiščete vse vrednosti hkrati in jih bo povzel. Namesto da kot prvi argument posredujete eno samo vrednost, kot je B4, lahko podate vse svoje vrednosti =LOOKUP(B4:B17,E4:F8). Ker bi to vrnilo 14 različnih vrednosti, morate funkcijo zaviti v funkcijo zavijanja, kot je =SUM( LOOKUP(B4:B17,E4:F8))ali =COUNT(LOOKUP(B4:B17,E4:F8))ali =AVERAGE( LOOKUP(B4:B17,E4:F8)). Ne pozabite, da potrebujete funkcijo Wrapper, ne pa rapper funkcije. =SNOOPDOGG(LOOKUP(B4:B17,E4:F8))ne bo delovalo.
Obstaja res pogosta napaka, ki se ji boste želeli izogniti. Po vnosu ali urejanju formule ne pritiskajte Enter! Namesto tega izvedite trik s tipkovnico s tremi prsti. Pridržite Ctrl in Shift. Medtem ko držite Ctrl in Shift, pritisnite Enter. Zdaj lahko sprostite Ctrl in Shift. Temu pravimo Ctrl + Shift + Enter, vendar mora biti res časovno pravilno nastavljen: Pritisnite in držite Ctrl + Shift, pritisnite Enter, spustite Ctrl + Shift. Če ste to storili pravilno, se bo formula prikazala v vrstici s formulo, obkroženo z zavitimi oklepaji:(=SUM(LOOKUP(B4:B17,E4:F8)))
Stranska opomba
LOOKUP lahko naredi tudi ekvivalent HLOOKUP. Če je vaša iskalna tabela širša, kot je visoka, LOOKUP preklopi na HLOOKUP. V primeru neodločenega izida… tabele velikosti 8x8 ali 10x10 bo LOOKUP tabelo obravnaval kot navpično.
Oglejte si spodnji video ali preučite ta posnetek zaslona.
Oglejte si video
Video zapis
Naučite se Excel iz podcasta, epizoda 2184: Seštej vsa iskanja.
Hej, dobrodošli nazaj v oddaji, jaz sem Bill Jelen. Današnje vprašanje Rona o uporabi starega, starega programa LOOKUP. In to je iz moje knjige Excel 2016 In Depth.
Recimo, da smo tukaj imeli kup izdelkov in smo morali uporabiti iskalno tabelo. In za vsak izdelek smo morali, veste, dobiti vrednost iz iskalne tabele in jo sešteti. No, tipičen način je, da v tej tabeli za ta izdelek uporabljamo VLOOKUP-- = VLOOKUP. Pritisnil bom F4, zaklenil to in z drugo vrednostjo. In v tem konkretnem primeru, ker je vsaka posamezna vrednost, ki jo iščemo, v tabeli in tabela razvrščena, je varno uporabiti TRUE. Običajno nikoli ne bi uporabil TRUE, v tej epizodi pa bomo uporabili TRUE. Torej dobim vse te vrednosti in potem spodaj, Alt + =, dobimo skupaj, kajne? Kaj pa, če je naš celoten cilj le dobiti 1130? Ne potrebujemo vseh teh vrednot. Potrebujemo le ta rezultat.
No, v redu, zdaj obstaja stara, stara funkcija, ki obstaja že od Visical-ovih dni, imenovana LOOKUP. Ne VLOOKUP. Ne HLOOKUP, samo LOOKUP. In zdi se, da je sprva podobno kot VLOOKUP - določite vrednost, ki jo iščete, in iskalno tabelo, pritisnite F4, potem pa smo končali. Ni nam treba določiti, kateri stolpec je, ker LOOKUP gre le do zadnjega stolpca v tabeli. Če bi imeli tabelo s sedmimi stolpci in želite poiskati četrto vrednost, bi samo določili stolpce od enega do štirih. Vredu? In tako, ne glede na zadnji stolpec, to bo iskal. In ni nam treba določiti, LAŽNO ali TRUE, ker vedno uporablja TRUE; ni, FALSE različica. Vredu?
Torej morate razumeti, če izvajate VLOOKUP, na koncu vedno uporabim FALSE, toda v tem primeru gre za ožji seznam - vemo, da je vse na seznamu v tabeli. Nič ne manjka in tabela je razvrščena. Vredu? Torej, s tem bomo dobili popolnoma enak rezultat kot za VLOOKUP.
Super, želim to kopirati: Alt + =. Vredu. Toda to nam ničesar ne kupi, ker moramo še vedno vnesti vse formule in nato funkcijo SUM. Lepa stvar je, da POGLED lahko naredi trik, ki ga VLOOKUP ne more, v redu? In to je, da opravite vsa iskanja hkrati. Torej, kje to pošljem v funkcijo SUM, ko rečem LOOKUP, kaj je iskalni element? Vse te stvari želimo poiskati, vejico in potem je tu tabela - in ni nam treba pritisniti F4, ker tega ne bomo nikjer kopirali, obstaja le ena formula - zaprite POGLED, zaprite Vsota.
V redu, tukaj je kraj, kjer se lahko stvari zajebajo: če preprosto pritisnete Enter, boste dobili 60, v redu? Ker bo šlo samo prvo. Kar morate storiti, je, da držite tri čarobne pritiske tipk, to pa je Ctrl + Shift - Ctrl + shift držim z levo roko, držim jih dol in z desno roko pritisnem ENTER in opravil bo vso matematiko VLOOKUP-a. Ali ni to super? Obvestilo v zgornji vrstici s formulami ali v besedilu formule vsebuje okrogle oklepaje. Ti zaviti oklepaji ne vnesejo, Excel jih vstavi, da reče: "Hej, za to ste pritisnili Ctrl + Shift + Enter."
Hej, ta tema in številne druge teme so v tej knjigi: Power Excel with, izdaja 2017. V zgornjem desnem kotu kliknite tisto »I«, če želite prebrati več o knjigi.
Danes vprašanje od Rona: Kako lahko seštejete vsa VLOOKUP-a? Zdaj večina ljudi pozna VLOOKUP, kjer določite iskalno vrednost, tabelo, kateri stolpec in nato TRUE ali FALSE. In če počnete - če izpolnjujete pogoje - TRUE različica VLOOKUP. potem lahko naredite tudi ta stari POGLED. Nenavadno je, ker vrne zadnji stolpec tabele, ne določite številke stolpca in ne izgovorite TRUE ali FALSE. Vedno je RES. Zakaj bi to uporabili? Ker ima poseben trik: lahko naredite vse iskalne vrednosti hkrati in jih bo seštelo. Po vnosu te formule morate pritisniti Ctrl + Shift + Enter, sicer pa ne bo delovalo. In potem v izhodu, vam pokažem še en trik za POGLED.
No, hej, rad bi se zahvalil Ronu, ker je poslal vprašanje, in zahvalil bi se vam, da ste se ustavili. Se vidimo naslednjič za še eno oddajo od.
V redu, medtem ko tukaj govorimo o LOOKUP-u, je druga stvar, ki jo LOOKUP lahko naredi: Veste, imamo VLOOKUP za takšno navpično tabelo ali HLOOKUP za vodoravno tabelo, kot je ta; POGLED lahko gre v katero koli smer. tako lahko rečemo hej želimo = POGLEDI to vrednost, D, v tej tabeli in ker je tabela širša, kot je visoka, LOOKUP samodejno preklopi na različico HLOOKUP, kajne? V tem primeru, ker določamo 3 vrstice s 5 stolpci, bo naredil HLOOKUP. In ker so tu zadnje vrstice številke, nam bo prineslo to številko. Torej imamo D, Date, dobi nas 60. V redu. Če bi določil tabelo, ki je šla samo v 12. vrstico, potem bom raje dobil ime izdelka. Vredu? Torej gre za zanimivo malo funkcijo. Mislim, da je Excel Help rekel: "Hej, ne uporabljaj te funkcije," todas določen čas, ko lahko uporabite to funkcijo.
Naslovna fotografija: grandcanyonstate / pixabay
