INDEX-MATCHista on tullut suositumpi Excelin työkalu, koska se ratkaisee VLOOKUP-toiminnon rajoitukset ja on helpompi käyttää. INDEX-MATCH-toiminnolla Excelissä on useita etuja VLOOKUP-toimintoon verrattuna:

1. INDEX ja MATCH ovat joustavampia ja nopeampia kuin Vlookup
2. On mahdollista suorittaa vaakasuuntaista hakua, pystysuoraa hakua, 2-suuntaista hakua, vasemmanpuoleista hakua, kirjainkoon erottelua ja jopa hakuja useiden kriteerien perusteella.
3. Lajiteltujen tietojen INDEX-MATCH on 30 % nopeampi kuin VLOOKUP. Tämä tarkoittaa, että suuremmassa tietojoukossa 30 % nopeampi on järkevämpää.

Aloitetaan kunkin INDEXin ja MATCHin yksityiskohtaisista käsitteistä.

INDEX-toiminto

Excelin INDEX-toiminto on erittäin tehokas ja samalla joustava työkalu, joka hakee arvon tietyltä alueelta. Toisella sanalla se palauttaa solun sisällön rivi- ja sarakesiirrolla määritettynä.

Syntaksi:

=INDEX(reference, [row], [column])>

Parametrit:

viite: Solujoukko, johon siirretään. Se voi olla yksittäinen alue tai koko tietojoukko tietotaulukossa. rivi [valinnainen]: Offset-rivien määrä. Se tarkoittaa, että jos valitsemme taulukon viitealueeksi A1:A5, solu/sisältö, jonka haluamme poimia, on kuinka suurella pystysuoralla etäisyydellä. Tässä A1-rivillä on 1, A2-rivillä = 2 ja niin edelleen. Jos annamme riville = 4, se erottaa A4:n. Koska rivi on valinnainen, joten jos emme määritä rivin numeroa, se poimii kokonaiset rivit viitealueelta. Se on tässä tapauksessa A1-A5. sarake [valinnainen]: Offset-sarakkeiden määrä. Se tarkoittaa, että jos valitsemme taulukon viitealueeksi A1:B5, solu/sisältö, jonka haluamme poimia, on kuinka suurella vaakaetäisyydellä. Tässä A1:lle rivi on 1 ja sarake 1, B1:lle rivi on 1, mutta sarake on 2 samalla tavalla, kun A2 rivi = 2 sarake = 1, B2 riville = 2 sarake = 2 ja niin edelleen. Jos annamme rivin = 5 ja sarakkeen 2, se erottaa B5:n. Koska sarake on valinnainen, joten jos emme määritä mitään rivin numeroa. sitten se poimii viitealueen koko sarakkeen. Jos esimerkiksi annamme rivin = 2 ja sarakkeen tyhjäksi, se purkaa (A2:B2). Jos emme määritä riviä ja saraketta molempia, se poimii koko viitetaulukon, joka on (A1:B5).

Viitetaulukko: Seuraavaa taulukkoa käytetään viitetaulukkona kaikille INDEX-funktion esimerkeille. Ensimmäinen solu on kohdassa B3 (Ruoka) ja viimeinen diagonaalinen solu on kohdassa F10 (180).

Esimerkkejä: Alla on esimerkkejä indeksifunktioista.

Tapaus 1: Rivejä ja sarakkeita ei mainita.

Syötä komento: =INDEKSI(B3:C10)

Tapaus 2: Vain rivit mainitaan.

Syötä komento: =INDEKSI(B3:C10,2)

Tapaus 3: Sekä rivit että sarakkeet mainitaan.

Syötä komento: =INDEKSI(B3:D10;4;2)

Tapaus 4: Vain sarakkeet mainitaan.

Syötä komento: =INDEKSI(B3 : D10 , , 2)

Ongelma INDEX-toiminnon kanssa: INDEX-funktion ongelmana on, että etsimillemme tiedoille on määritettävä rivit ja sarakkeet. Oletetaan, että kyseessä on 10 000 rivin ja sarakkeen koneoppimistietojoukko, jolloin on erittäin vaikeaa etsiä ja poimia etsimäämme dataa. Tässä tulee Match Function -konsepti, joka tunnistaa rivit ja sarakkeet joidenkin ehtojen perusteella.

MATCH-toiminto

Se hakee kohteen/arvon sijainnin alueella. Se on vähemmän jalostettu versio VLOOKUP- tai HLOOKUPista, joka palauttaa vain sijaintitiedot, ei todellista tietoa. MATCH ei erota kirjainkoolla, eikä välitä, onko alue vaaka- vai pystysuuntainen.

Syntaksi:

=MATCH(search_key, range, [search_type])>

Parametrit:

search_key: Haettava arvo. Esimerkiksi 42, Cats tai I24. alue: Haettava yksiulotteinen taulukko. Se voi olla joko yksi rivi tai yksi sarake.esim.->A1:A10 , A2:D2 jne. search_type [valinnainen]: Hakutapa. = 1 (oletus) löytää suurimman arvon, joka on pienempi tai yhtä suuri kuin hakuavain, kun alue lajitellaan nousevaan järjestykseen.
• = 0 löytää tarkan arvon, kun aluetta ei ole lajiteltu.
• = -1 löytää pienimmän arvon, joka on suurempi tai yhtä suuri kuin hakuavain, kun alue lajitellaan laskevassa järjestyksessä.

Rivin numero tai sarakkeen numero löytyy täsmäämistoiminnolla ja sitä voidaan käyttää hakemistotoiminnon sisällä, joten jos tuotteesta on jotain yksityiskohtia, kaikki tiedot voidaan poimia tuotteesta etsimällä kohteen rivi/sarake hakutoiminnolla sitten sisäkkäin se indeksifunktioon.

Viitetaulukko: Seuraavaa taulukkoa käytetään viitetaulukkona kaikille MATCH-funktion esimerkeille. Ensimmäinen solu on kohdassa B3 (Ruoka) ja viimeinen diagonaalinen solu on kohdassa F10 (180)

Esimerkkejä: Alla on esimerkkejä MATCH-funktiosta-

Tapaus 1: Hakutyyppi 0, se tarkoittaa tarkkaa hakua.

Syötä komento: =MATCH(Etelä-Intian,C3:C10,0)

Tapaus 2: Hakutyyppi 1 (oletus).

Syötä komento: =MATCH(Etelä-Intian, C3:C10)

c++ merkkijonon jako

Tapaus 3: Hakutyyppi -1.

Syötä komento: =MATCH(Etelä-Intian,C3:C10,-1)

INDEKSI-MATCH Yhdessä

Edellisissä esimerkeissä rivien ja sarakkeiden staattiset arvot on annettu INDEX-funktiossa. Oletetaan, että riveistä ja sarakkeiden sijainnista ei ole aiempaa tietoa, niin rivien ja sarakkeiden sijainti voidaan antaa MATCH-funktiolla. Tämä on dynaaminen tapa etsiä ja poimia arvoa.

Syntaksi:

 =INDEX(Reference Table , [Match(SearchKey,Range,Type)/StaticRowPosition],  [Match(SearchKey,Range,Type)/StaticColumnPosition])>

Viitetaulukko: Seuraavaa viitetaulukkoa käytetään. Ensimmäinen solu on kohdassa B3 (Ruoka) ja viimeinen diagonaalinen solu on kohdassa F10 (180)

Esimerkki: Oletetaan, että tehtävänä on löytää Masala Dosan hinta. Tiedetään, että sarake 3 edustaa tuotteiden kustannuksia, mutta Masala Dosan rivipaikkaa ei tiedetä. Ongelma voidaan jakaa kahteen vaiheeseen -

Vaihe 1: Etsi Masala Dosan sijainti kaavalla:

 =MATCH('Masala Dosa',B3:B10,0)>

Tässä B3:B10 edustaa sarakkeen ruokaa ja 0 tarkoittaa tarkkaa vastaavuutta. Se palauttaa Masala Dosan rivinumeron.

Vaihe 2: Selvitä Masala Dosan hinta. Käytä INDEX-funktiota löytääksesi Masala Dosan hinta. Korvaamalla yllä oleva MATCH-funktion kysely INDEX-funktion sisällä kohtaan, jossa Masala Dosan tarkka sijainti vaaditaan, ja kustannussarakkeen numero on 3, joka on jo tiedossa.

=INDEX(B3:F10, MATCH('Masala Dosa', B3:B10 , 0) ,3)>

Kaksisuuntainen haku INDEX-MATCHin kanssa yhdessä

Edellisessä esimerkissä Cost-attribuutin sarakkeen sijainti oli kovakoodattu. Joten se ei ollut täysin dynaaminen.

Tapaus 1: Oletetaan, että myös Kustannussarakkeen numerosta ei ole tietoa, niin se voidaan saada kaavalla:

 =MATCH('Cost',B3:F3,0)>

Tässä B3:F3 edustaa otsikkosaraketta.

Tapaus 2: Kun rivin ja sarakkeen arvo tarjotaan MATCH-funktion kautta (ilman staattista arvoa), sitä kutsutaan kaksisuuntaiseksi hauksi. Se voidaan saavuttaa käyttämällä kaavaa:

 =INDEX(B3:F10, MATCH('Masala Dosa',B3:B10, 0) , MATCH('Cost' ,B3:F3 ,0))>

Vasen haku

Yksi INDEXin ja MATCHin tärkeimmistä eduista VLOOKUP-toimintoon verrattuna on kyky suorittaa haku vasemmalle. Se tarkoittaa, että kohteen rivin sijainti voidaan poimia mistä tahansa oikealla olevasta attribuutista ja toisen vasemmalla olevan attribuutin arvo voidaan poimia.

Oletetaan esimerkiksi, että ostat ruokaa, jonka hinta on 140 rupiaa. Epäsuorasti sanomme, että osta Biryani. Tässä esimerkissä kustannukset Rs 140/- tunnetaan, on tarve poimia Ruoka. Koska Kustannus-sarake on sijoitettu Ruoka-sarakkeen oikealle puolelle. Jos VLOOKUP on käytössä, se ei voi hakea Kustannus-sarakkeen vasemmalta puolelta. Tästä syystä VLOOKUPilla ei ole mahdollista saada ruoan nimeä.

Tämän haitan voittamiseksi voidaan käyttää INDEX-MATCH-toimintoa vasemmalle.
Vaihe 1: Kustannus 140 Rs:n ensimmäisen rivin sijainti kaavalla:

 =MATCH(140, D3:D10,0)>

Tässä D3: D10 edustaa Kustannussaraketta, jossa haetaan kustannusten 140 Rs rivinumeroa.

Vaihe 2: Kun olet saanut rivinumeron, seuraava vaihe on käyttää INDEX-funktiota ruoan nimen poimimiseen kaavalla:

 =INDEX(B3:B10, MATCH(140, D3:D10,0))>

Tässä B3:B10 edustaa elintarvikesaraketta ja 140 on ruokatuotteen hinta.

Kirjainkoolla huomioiva haku

MATCH-funktio itsessään ei ole kirjainkoolla väliä. Tämä tarkoittaa, jos ruokanimi on DHOKLA ja MATCH-toimintoa käytetään seuraavan hakusanan kanssa:

1. Dhokla
2. dhokla
3. DhOkLA

Kaikki palauttavat DHOKLAn rivipaikan. EXACT-toimintoa voidaan kuitenkin käyttää INDEXin ja MATCHin kanssa isojen ja pienten kirjainten haun suorittamiseen.

Tarkka toiminto: Excelin EXACT-funktio vertaa kahta tekstimerkkijonoa, ottaen huomioon isot ja pienet kirjaimet, ja palauttaa TRUE, jos ne ovat samat, ja EPÄTOSI, jos eivät. EXACT kirjainkoolla on merkitystä.

Esimerkkejä:

EXACT(DHOKLA,DHOKLA): Tämä palauttaa True. EXACT(DHOKLA,Dhokla): Tämä palauttaa False. EXACT(DHOKLA,dhokla): Tämä palauttaa False. EXACT(DHOKLA,DhOkLA): Tämä palauttaa False.

Esimerkki: Oletetaan, että tehtävänä on etsiä Dhokla-ruokatyyppiä, mutta kirjainkoon huomioivalla tavalla. Tämä voidaan tehdä käyttämällä kaavaa-

 =INDEX(C3:C10, MATCH(TRUE , EXACT('Dhokla', B3:B10) ,0))>

Tässä EXACT-funktio palauttaa True, jos sarakkeen B3:B10 arvo vastaa Dhoklaa samalla tapauksella, muuten se palauttaa False. Nyt MATCH-toiminto on käytössä sarakkeessa B3:B10 ja etsii riviä, jonka tarkka arvo on TOSI. Tämän jälkeen INDEX-funktio hakee sarakkeen C3:C10 (Food Type Column) arvon MATCH-funktion palauttamalla rivillä.

Useiden kriteerien haku

Yksi Excelin vaikeimmista ongelmista on useisiin kriteereihin perustuva haku. Toisin sanoen haku, joka vastaa useampaa kuin yhtä saraketta samanaikaisesti. Alla olevassa esimerkissä INDEX- ja MATCH-funktiota sekä loogista logiikkaa käytetään vastaamaan 3 sarakkeessa-

1. Ruokaa.
2. Kustannus.
3. Määrä.

Kokonaiskustannusten poimiminen.

Esimerkki: Oletetaan, että tehtävänä on laskea pastan kokonaiskustannukset missä

Ruoka: Pasta. Hinta: 60. Määrä: 1.

Joten tässä esimerkissä on kolme kriteeriä vastaavuuden suorittamiseen. Alla on vaiheet hakuun useiden kriteerien perusteella -

Vaihe 1: Yhdistä ensin ruokakolonni (B3:B10) pastaan ​​kaavalla:

 'PASTA' = B3:B10>

Tämä muuntaa B3:B10 (Food Column) -arvot Boolen arvoiksi. Se on totta, jos ruoka on pastaa, muualla väärin.

Vaihe 2: Vastaa sen jälkeen kustannuskriteerit seuraavalla tavalla:

 60 = D3:D10>

Tämä korvaa D3:D10 (kustannussarakkeen) arvot Boolen arvoina. Se on totta, jos hinta = 60, muuten väärin.

Vaihe 3: Seuraava vaihe on täyttää kolmannet kriteerit, jotka ovat Määrä = 1, seuraavalla tavalla:

 1 = E3:E10>

Tämä korvaa E3:E10-sarakkeen (Määräsarake) arvolla Tosi, jossa Määrä = 1, muuten se on epätosi.

Vaihe 4: Kerro ensimmäisen, toisen ja kolmannen kriteerin tulos. Tämä on kaikkien ehtojen leikkauspiste ja muuntaa Boolen tosi/epätosi arvoksi 1/0.

Vaihe 5: Nyt tuloksena on sarake, jossa on 0 ja 1. Käytä tässä MATCH-funktiota löytääksesi rivien lukumäärä sarakkeita, jotka sisältävät 1. Koska jos sarakkeen arvo on 1, se tarkoittaa, että se täyttää kaikki kolme kriteeriä.

Vaihe 6: Kun olet saanut rivin numeron, käytä INDEX-funktiota saadaksesi rivin kokonaiskustannukset.

 =INDEX(F3:F10, MATCH(1, ('Pasta'=B3:B10) * (60=D3:D10) * (1=E3:E10) , 0 ))>

Tässä F3:F10 edustaa kokonaiskustannussaraketta.