Microsoft Excel

Das Portal zum Thema Excel-Formeln

Gruppe: Listen/Datensätze

Beitrag: 2 Spalten: Einträge ermitteln, die in beiden Spalten enthalten sind

Aufgabe
Du willst zwei Spalten vergleichen und eine Liste derjenigen Einträge (Texte, Zahlen) erhalten, die in beiden Spalten vorkommen.

       A               B               C       
1 10  10  10  
2 8  7  7  
3 7  6  6  
4 9  12  8  
5 5  14  9  
6 6  8  #ZAHL!  
7 7  15   
8 3  13   
9 2  11   
10 1  9   
11   10   

Lösung
Spalte1: A
Spalte2: B

In C1 steht:
{=INDEX(B:B;KKLEINSTE(WENN(ZÄHLENWENN(A$1:A$99;B$1:B$99)>0;ZEILE(X$1:X$99));ZEILE(X1)))}

Diese Formel runterkopieren.

Wenn in Spalte B ein Eintrag aus Spalte A mehrfach vorkommen kann, dieser in der neuen Liste aber nur einmal aufgeführt werden soll, dann:

{=INDEX(B$1:B$99;KKLEINSTE(WENN((ZÄHLENWENN(A$1:A$99;B$1:B$99)>0)*(ZÄHLENWENN(INDIREKT("B1:B"&ZEILE(X$1:X$99));B$1:B$99)=1);ZEILE(X$1:X$99));ZEILE(X1)))}


Hilfsspaltenlösung bei großem Datenbestand

Für große Bereiche sind obige Formeln ungeeignet, da ja jede Zelle aus Spalte A mit jeder Zelle aus Spalte B verglichen wird. Das sind bei 10.000 Zeilen 100 Millionen Vorgänge.

Schreibe in C1 (Hilfsspalte - ausblenden):
=WENN(ZÄHLENWENN(A:A;B1)>0;B1;"")
und runterkopieren
Sollen keine Doppelten aufgeführt werden, dann in C2:
=WENN(ODER(ZÄHLENWENN(A:A;B2)=0;ZÄHLENWENN(B$1:B1;B2)>0);"";B2)
diese dann runterkopieren

Lückenlose Auflistung mit Array-Formel Nr. 43 (Die ist schnell genug; - das Abarbeiten einer einzigen Spalte sind ja "nur" 10.000 Vorgänge.):
{=INDEX(C:C;KKLEINSTE(WENN(C$1:C$10000<>"";ZEILE($1:$10000));ZEILEN($1:1)))}
und runterkopieren