Note: The other languages of the website are Google-translated. Back to English

INDEX og MATCH på tværs af flere kolonner

For at slå en værdi op ved at matche på tværs af flere kolonner skal du bruge en matrixformel baseret på INDEX og MATCH funktioner, der inkorporerer MMULT, TRANSPOSE og KOLONNE vil gøre dig en tjeneste.

indeks matcher flere kolonner 1

Hvordan slår man en værdi op ved at matche på tværs af flere kolonner?

For at udfylde tilsvarende klasse for hver elev som ovenstående tabel vist, hvor oplysningerne er opført på tværs af flere kolonner, kan du først bruge tricket med funktionen MMULT, TRANSPOSE og COLUMN til at producere en matrix-array. Så vil MATCH-funktionen give dig positionen for din opslagsværdi, som vil blive ført til INDEX for at hente den værdi, du leder efter i arrayet.

Generisk syntaks

=INDEX(return_range,(MATCH(1,MMULT(--(lookup_array=lookup_value),TRANSPOSE(COLUMN(lookup_array)^0)),0)))

√ Bemærk: Dette er en matrixformel, der kræver, at du indtaster med Ctrl + Flytte + Indtast.

  • returområde: Det område, hvor du vil have formlen til at returnere klasseoplysningerne fra. Her refererer til klasserækken.
  • opslagsværdi: Værdien formlen brugte til at finde dens tilsvarende klasseinformation. Her refererer til fornavnet.
  • opslag_array: Celleområdet, hvor opslagsværdi er opført; Området med de værdier, der skal sammenlignes med opslagsværdi. Her refererer til navneområdet.
  • match_type 0: Tvinger MATCH til at finde den første værdi, der nøjagtigt er lig med opslagsværdi.

For at finde klasse af Jimmy, kopier eller indtast formlen herunder i cellen H5, og tryk på Ctrl + Flytte + Indtast for at få resultatet:

= INDEX ($ B $ 5: $ B $ 7,(MATCH(1,MMULT(--($C$5:$E$7=G5),TRANSPOSER(KOLONNE($C$5:$E$7)^0)),0)))

√ Bemærk: Dollartegnene ($) ovenfor angiver absolutte referencer, hvilket betyder, at navn og klasseområder i formlen ikke ændres, når du flytter eller kopierer formlen til andre celler. Bemærk, at du ikke bør tilføje dollartegn til den cellereference, der repræsenterer opslagsværdien, da du ønsker, at den skal være relativ, når du kopierer den til andre celler. Når du har indtastet formlen, skal du trække udfyldningshåndtaget ned for at anvende formlen på nedenstående celler.

indeks matcher flere kolonner 2

Forklaring af formlen

=INDEX($B$5:$B$7,(MATCH(1,MMULT(--($C$5:$E$7=G5),TRANSPOSE(COLUMN($C$5:$E$7)^0)),0)))

  • --($C$5:$E$7=G5): Dette segment kontrollerer hver værdi i området $C$5:$E$7 hvis de er lig med værdien i cellen G5, og genererer en TRUE og FALSE matrix som denne:
    {TRUE,FALSE,FALSE;FALSE,FALSE,FALSE;FALSE,FALSE,FALSE}.
    Den dobbelte negative vil så konvertere TRUEs og FALSEs til 1'er og 0'er for at give et array som dette:
    {1,0,0; 0,0,0; 0,0,0}.
  • KOLONNE($C$5:$E$7): Funktionen KOLONNE returnerer kolonnenumrene for området $C$5:$E$7 i et array som dette: 3,4,5 {}.
  • TRANSPOSER(KOLONNE($C$5:$E$7)^0) = TRANSPOSER(3,4,5 {}^0): Efter at have hævet potensen til 0, vil alle tallene i arrayet {3,4,5} blive konverteret til 1: {1,1,1}. TRANSPOSE-funktionen konverterer derefter kolonne-arrayet til en række-array som dette: {1; 1; 1}.
  • MMULT(--($C$5:$E$7=G5),TRANSPOSER(KOLONNE($C$5:$E$7)^0)) = MMULT({1,0,0; 0,0,0; 0,0,0},{1; 1; 1}): MMULT-funktionen returnerer matrixproduktet af de to arrays sådan her: {1; 0; 0}.
  • MATCH (1,MMULT(--($C$5:$E$7=G5),TRANSPOSER(KOLONNE($C$5:$E$7)^0)), 0) = MATCH (1,{1; 0; 0}, 0): Match_type 0 tvinger MATCH-funktionen til at returnere positionen for den første match af 1 i arrayet {1; 0; 0}, Hvilket er 1.
  • INDEKS($ B $ 5: $ B $ 7,(MATCH(1,MMULT(--($C$5:$E$7=G5),TRANSPOSER(KOLONNE($C$5:$E$7)^0)), 0))) = INDEX ($ B $ 5: $ B $ 7,1): INDEX -funktionen returnerer 1st værdi i klasseområdet $ B $ 5: $ B $ 7, Hvilket er A.

For nemt at finde en værdi ved at matche på tværs af flere kolonner, kan du også bruge vores professionelle Excel-tilføjelsesprogram Kutools til Excel. Se instruktionen her for at udføre missionen.


Relaterede funktioner

Excel INDEX-funktion

Excel INDEX -funktionen returnerer den viste værdi baseret på en given position fra et område eller en matrix.

Excel MATCH-funktion

Funktionen Excel MATCH søger efter en bestemt værdi i et celleområde og returnerer værdiens relative position.

Excel MMULT -funktion

Excel MMULT-funktionen returnerer matrixproduktet af to arrays. Matrixresultatet har det samme antal rækker som matrix1 og det samme antal kolonner som matrix2.

Excel TRANSPOSE -funktion

Excel TRANSPOSE-funktionen roterer orienteringen af ​​et område eller en matrix. For eksempel kan den rotere en tabel, der er arrangeret vandret i rækker til lodret i kolonner eller omvendt.

Excel KOLONNE funktion

Funktionen KOLONNE returnerer antallet af kolonner, som formlen vises, eller returnerer kolonnenummeret for en given reference. For eksempel returnerer formel =KOLONNE(BD) 56.


Relaterede formler

Opslag med flere kriterier med INDEX og MATCH

Når man behandler en stor database i et Excel -regneark med flere kolonner og rækketekster, er det altid svært at finde noget, der opfylder flere kriterier. I dette tilfælde kan du bruge en matrixformel med INDEX- og MATCH -funktionerne.

To-vejs opslag med INDEX og MATCH

For at søge efter noget på tværs af både rækker og kolonner i Excel, eller vi siger at slå en værdi op i skæringspunktet mellem den specifikke række og kolonne, kan vi bruge hjælp fra INDEX og MATCH -funktioner.

Opslag nærmeste matchværdi med flere kriterier

I nogle tilfælde skal du muligvis søge den nærmeste eller omtrentlige matchværdi baseret på mere end ét kriterium. Med kombinationen af ​​INDEX-, MATCH- og IF -funktioner kan du hurtigt få det gjort i Excel.


De bedste Office-produktivitetsværktøjer

Kutools til Excel - Hjælper dig med at skille dig ud fra mængden

Kunne du tænke dig at udføre dit daglige arbejde hurtigt og perfekt? Kutools til Excel bringer 300 kraftfulde avancerede funktioner (Kombiner projektmapper, sum efter farve, delt celleindhold, konverter dato, og så videre...) og spar 80% tid for dig.

  • Designet til 1500 arbejdsscenarier, hjælper dig med at løse 80 % Excel-problemer.
  • Reducer tusindvis af tastatur- og museklik hver dag, lindre dine trætte øjne og hænder.
  • Bliv en Excel-ekspert på 3 minutter. Behøver ikke længere huske smertefulde formler og VBA-koder.
  • 30-dages ubegrænset gratis prøveperiode. 60-dages pengene tilbage garanti. Gratis opgradering og support i 2 år.
Ribbon of Excel (med Kutools til Excel installeret)

Fanen Office - Aktiver læsning og redigering af faner i Microsoft Office (inkluderer Excel)

  • Et sekund til at skifte mellem snesevis af åbne dokumenter!
  • Reducer hundreder af museklik for dig hver dag, og farvel med musens hånd.
  • Øger din produktivitet med 50 %, når du ser og redigerer flere dokumenter.
  • Bringer effektive faner til Office (inkluderer Excel), Ligesom Chrome, Firefox og ny Internet Explorer.
Skærmbillede af Excel (med Office-fanen installeret)
Sorter kommentarer efter
Kommentarer (0)
Ingen vurderinger endnu. Vær den første til at bedømme!
Der er endnu ingen kommentarer her
Efterlad dine kommentarer
Sender som gæst
×
Bedøm dette indlæg:
0   Tegn
Foreslåede steder

Følg os

Copyright © 2009 - www.extendoffice.com. | Alle rettigheder forbeholdes. Drevet af ExtendOffice. | | Sitemap
Microsoft og Office-logoet er varemærker eller registrerede varemærker tilhørende Microsoft Corporation i USA og / eller andre lande.
Beskyttet af Sectigo SSL