Generisk formel
=FILTER(list1,COUNTIF(list2,list1))
Sammanfattning
För att filtrera data för att extrahera matchande värden i två listor kan du använda FILTER-funktionen och COUNTIF- eller COUNTIFS-funktionen. I exemplet som visas är formeln i F5:
=FILTER(list1,COUNTIF(list2,list1))
där list1 (B5: B16) och list2 (D5: D14) heter områden. Resultatet som returneras av FILTER innehåller endast värdena i lista1 som visas i list2 .
Obs! FILTER är en ny dynamisk matrisfunktion i Excel 365.
Förklaring
Denna formel är beroende av FILTER-funktionen för att hämta data baserat på ett logiskt test byggt med COUNTIF-funktionen:
=FILTER(list1,COUNTIF(list2,list1))
arbetar inifrån och ut, COUNTIF-funktionen används för att skapa det faktiska filtret:
COUNTIF(list2,list1)
Lägg märke till att vi använder list2 som intervallargument och list1 som kriterierargument. Med andra ord ber vi COUNTIF att räkna alla värden i lista1 som visas i lista2. Eftersom vi ger COUNTIF flera värden för kriterier får vi tillbaka en matris med flera resultat:
(1;1;0;1;0;1;0;0;1;0;1;1)
Observera att matrisen innehåller 12 räkningar, en för varje värde i lista1 . Ett nollvärde indikerar ett värde i lista1 som inte finns i lista2 . Alla andra positiva tal indikerar ett värde i lista1 som finns i lista2 . Denna matris returneras direkt till FILTER-funktionen som inkluderingsargument:
=FILTER(list1,(1;1;0;1;0;1;0;0;1;0;1;1))
Filterfunktionen använder arrayen som ett filter. Alla värden i lista1 associerade med en noll tas bort, medan alla värden som är associerade med ett positivt antal överlever.
Resultatet är en matris med 7 matchande värden som sprider in i intervallet F5: F11. Om data ändras kommer FILTER att räkna om och returnera en ny lista med matchande värden baserat på de nya data.
Icke-matchande värden
För att extrahera icke-matchande värden från lista1 (dvs. värden i lista1 som inte visas i lista2 ) kan du lägga till INTE-funktionen i formeln så här:
=FILTER(list1,NOT(COUNTIF(list2,list1)))
INTE-funktionen reverserar effektivt resultatet från COUNTIF - alla tal som inte är noll blir FALSKA och något nollvärde blir SANT. Resultatet är en lista över värdena i list1 som inte finns i list2 .
Med INDEX
Det är möjligt att skapa en formel för att extrahera matchande värden utan FILTER-funktionen, men formeln är mer komplex. Ett alternativ är att använda INDEX-funktionen i en sådan formel:
Formeln i G5, kopierad är:
=IFERROR(INDEX(list1,SMALL(IF(COUNTIF(list2,list1),ROW(list1)-ROW(INDEX(list1,1,1))+1),ROWS($F$5:F5))),"")
Obs: detta är en matrisformel och måste anges med kontroll + shift + enter, utom i Excel 365.
Kärnan i denna formel är INDEX-funktionen som tar emot list1 som arrayargument . Det mesta av den återstående formeln beräknar helt enkelt radnumret som ska användas för matchande värden. Detta uttryck genererar en lista med relativa radnummer:
ROW(list1)-ROW(INDEX(list1,1,1))+1
som returnerar en matris med 12 nummer som representerar raderna i list1 :
(1;2;3;4;5;6;7;8;9;10;11;12)
Dessa filtreras med IF-funktionen och samma logik som används ovan i FILTER, baserat på COUNTIF-funktionen:
COUNTIF(list2,list1) // find matching values
Den resulterande matrisen ser ut så här:
(1;2;FALSE;4;FALSE;6;FALSE;FALSE;9;FALSE;11;12) // result from IF
Denna matris levereras direkt till SMALL-funktionen, som används för att hämta nästa matchande radnummer när formeln kopieras ner i kolumnen. K-värdet för SMALL (think nth) beräknas med ett expanderande intervall:
ROWS($G$5:G5) // incrementing value for k
IFERROR-funktionen används för att fånga upp fel som uppstår när formeln kopieras och slutar matcha värden. För ytterligare ett exempel på denna idé, se denna formel.
