Šiame straipsnyje sužinosime, kaip išgauti stulpelių rodyklę iš „Excel“ lentelės.
Scenarijus:
Daug kartų dirbdami su ilgai išsklaidytais duomenimis, prieš pradėdami dirbti, pirmiausia turime išvalyti duomenis. Tai užtrunka, o jūs tiesiog norėjote išgauti keletą užklausų. Norėdami išgauti duomenis, paprastai naudojate funkciją VLOOKUP. Bet kai turime ilgus duomenis su daugybe stulpelių laukų, tai tampa netvarkinga, nes VLOOKUP funkcijai reikalingas teisingas stulpelių indeksas kaip skaičius, kitaip formulė grąžina klaidą. Žemiau yra šios problemos formulės paaiškinimas.
Kaip išspręsti problemą?
Tam naudosime MATCH funkciją. Jei nesate girdėję apie šią funkciją, pripraskite prie jos. Tai „Excel“ paieškos funkcija, kuri grąžina masyvo paieškos vertės indeksą. Čia turime gauti stulpelio pavadinimo indekso numerį. Tada pereisime prie tolesnio žingsnio Kaip ieškoti verčių lentelėse naudojant funkciją MATCH. Žemiau pateikiama bendra formulė
Bendra formulė:
| = MATCH (stulpelio_pavadinimas, table_header, 0) |
stulpelio_pavadinimas: paieškos vertė
table_header: lentelės antraštės masyvas
0: ieškokite tikslios atitikties
Pavyzdys :
Visa tai gali būti painu suprasti. Supraskime formulę su paaiškinimu ir pavyzdžiu. Čia mes turime 1 lentelės duomenų lentelę ($ A $ 1: $ U $ 9995). Taigi mes turime lentelės antraštę kaip A1: U1 (pirmoji lentelės eilutė).
Pažiūrėkime žemiau pateiktą formulę, kurią reikia taikyti lentelėje.
Naudokite formulę
| = MATCH (C4, Sheet1! $ A $ 1: $ U $ 1,0) |
Paaiškinimas:
- Funkcija MATCH ieško vertės C4 langelyje „Užsakymo ID“
- Sheet1! $ A $ 1: $ U $ 1 yra paieškos masyvo argumentas.
- Pateikiamas 0 argumentas, norint rasti tikslią atitiktį.
Kaip matote, lentelės stulpelio indeksas yra 2. Tačiau labai erzina naudoti lentelės antraštę kaip visą santrumpą, todėl lentelės antraščių masyvui naudojame pavadintą diapazoną. Sužinokite daugiau apie pavadintus diapazonus čia. Lentelės antraštėje naudojamas pavadintas diapazonas (Sheet1! $ A $ 1: $ U $ 1) yra „antraštė“.
Naudokite formulę.
| = MATCH (C4, antraštė, 0) |
Čia aukščiau pateikta nuotrauka paaiškina du dalykus. Pirma, stulpelio būsenos indeksas lentelėje yra 11, o antrasis - pavadintas diapazonas „antraštė“ veikia gerai. Nukopijuokite likusių stulpelių pavadinimų formulę naudodami Ctrl + D arba vilkite žemyn iš dešiniojo apatinio krašto naudojamą langelį.
Čia yra visas stulpelis „Indeksas“. Dabar galime naudoti kaip įvestį funkcijai VLOOKUP, kaip parodyta žemiau.
Funkcijos VLOOKUP atitikimo indeksas:
Dabar turime sprendimą, kaip gauti lentelės stulpelių indeksą. Formulę galime naudoti kaip įvestį funkcijai VLOOKUP. Pavyzdžiui, mums reikia produkto pavadinimo, įsigyto kliento vardu „Pete Kriz“.
Naudokite formulę:
| = VLOOKUP (D10, lentelė, MATCH (E9, antraštė, 0), 0) |
Pastaba: įsitikinkite, kad D10 (Pete Kriz) paieškos vertė turi būti pirmame lentelės stulpelyje.
Kaip matote, formulė grąžina produkto pavadinimą iš kliento vardo lentelėje. Paprastai mes nenaudojame „MATCH“ su „VLOOKUP“ funkcija, nes paieškos vertė turi būti pirmame stulpelyje, o tai beveik neįvyksta. Taigi mes naudojame INDEX ir MATCH funkcijos derinį. Sužinokite daugiau apie tai, kaip ieškoti vertės naudojant funkciją INDEX ir MATCH.
Čia yra visos pastabos, susijusios su formulės naudojimu.
Pastabos:
- Formulė tinka ir tekstui, ir skaičiams.
- Funkcija grąžina #NA klaidą, jei paieškos masyvo argumentas funkcijai MATCH nėra vienodo ilgio lentelės masyvo.
- Formulė pateikia klaidą, jei „lookup_value“ neatitinka lentelės lookup_array reikšmės.
- Funkcija atitinka tikslią vertę, nes atitikties tipo argumentas su funkcija MATCH yra 0.
- Naudokite argumentą -1, jei yra mažiau nei, 0 -tikslios atitikties, o 1 -didesnės nei paieškos atitikties.
- Ieškomos vertės gali būti pateiktos kaip langelio nuoroda arba tiesiogiai formulėje kaip argumentai naudojant citatos simbolį (").
Tikimės, kad supratote, kaip iš „Excel“ lentelės išgauti stulpelių indeksą. Čia rasite daugiau straipsnių apie „Excel“ paieškos vertę ir „Excel 2019“ funkcijas. Jei jums patiko mūsų tinklaraščiai, pasidalykite jais su draugais „Facebook“. Taip pat galite sekti mus „Twitter“ ir „Facebook“. Mes norėtume išgirsti jūsų nuomonę, praneškite mums, kaip galime patobulinti, papildyti ar naujovinti savo darbą ir padaryti jį geresnį. Rašykite mums el.
Norėdami ieškoti vertės, naudokite INDEX ir MATCH : Funkcija INDEX & MATCH, jei reikia, ieškoti vertės.
SUM diapazonas su „INDEX“ programoje „Excel“ : Naudokite funkciją INDEX, kad surastumėte reikiamų verčių SUM.
Kaip naudoti „INDEX“ funkciją programoje „Excel“ : Raskite masyvo INDEKSĄ naudodami funkciją INDEX, paaiškintą pavyzdžiu.
Kaip naudoti funkciją „MATCH“ programoje „Excel“ : Raskite MATCH masyve naudodami INDEX reikšmę MATCH funkcijos viduje, paaiškinta pavyzdžiu.
Kaip naudoti „LOOKUP“ funkciją „Excel“ : Raskite paieškos reikšmę masyve naudodami funkciją LOOKUP, paaiškinta pavyzdžiu.
Populiarūs straipsniai:
Kaip naudoti „IF“ funkciją „Excel“ : „Excel“ IF sakinys patikrina sąlygą ir grąžina konkrečią reikšmę, jei sąlyga yra TRUE, arba grąžina kitą konkrečią reikšmę, jei FALSE.
Kaip naudotis „VLOOKUP“ funkcija „Excel“ : Tai yra viena iš dažniausiai naudojamų ir populiariausių „Excel“ funkcijų, naudojama vertei iš įvairių diapazonų ir lapų ieškoti.
Kaip naudoti „COUNTIF“ funkciją „Excel“ : Skaičiuokite vertes su sąlygomis naudodami šią nuostabią funkciją. Norint suskaičiuoti konkrečias vertes, nereikia filtruoti duomenų. Skaitiklio funkcija yra būtina norint paruošti prietaisų skydelį.
Kaip naudotis „SUMIF“ funkcija „Excel“ : Tai dar viena esminė prietaisų skydelio funkcija. Tai padeda apibendrinti vertes konkrečiomis sąlygomis.