Excel – Formeln schrittweise aufbauen

Aus einer Liste sollen die Namen der Verkäufer herausgelesen werden, die den ersten, zweiten, dritten oder einen x-beliebigen Platz haben. Im Beispiel will ich die Plätze 1 bis 3 und 5 haben.

 

smart_owl_reading_book_1600_clr_15127
Ziel des Blogs ist es wieder, eine komplexe Formel in Schritten aufzubauen und sie Stück für Stück zu einer Formel zusammenzustellen. Ich habe das schon hier und hier begonnen.

 

 

 

 

28.01.2018_ 0005

 

Heute werden diese drei Funktionen verwendet:

KGRÖSSTE, VERGLEICH und INDEX.

 

Die Ziffern in D2 bis D5 sind getippte Ziffern.

 

In Spalte E wird mit KGRÖSSTE ermittelt, welche Zahl den Rang aus Spalte D hat – den ersten, zweiten, dritten oder fünften.

Die Formel in E2 lautet:

=KGRÖSSTE($B$1:$B$9;D2)

 

28.01.2018_ 0006

 

Achten Sie auf die absoluten Bezüge für den Bereich und den relativen für das Suchkriterium in D2. Beginnen Sie in Zeile 1 mit der Überschrift – das ist wichtig, weil die anderen Formeln auch jeweils in Zeile 1 beginnen. Diese Formel wird nach unten ausgefüllt und liefert die Werte des Umsatzes. Jetzt wissen wir, welche Umsatzzahl den ersten, zweiten und so weiter Platz belegt.

 

Daraus wird in Spalte F ermittelt, in welcher Zeile dieser Wert auftaucht. Das passiert mit INDEX. Die Formel in F2 lautet:

=VERGLEICH(E2;$B$1:$B$9;0)

 

28.01.2018_ 0007

 

Der Vergleich liefert die Zeilennummer, in der der Wert vorkommt. Das Suchkriterium ist relativ, die  Suchmatrix absolut und der Vergleich soll eine genaue Übereinstimmung liefern – darum das Argument “0” (Null).

 

Jetzt haben wir die Zeilennummer und daraus soll der Name ermittelt werden. Welcher Name steht in Zeile 8? Das wird mit INDEX ermittelt:

=INDEX($A$1:$B$9;F2;1)

28.01.2018_ 0008

 

Der INDEX soll die Spalten A und B durchsuchen und den Wert aus F2 als Zeilennummer verwenden. Die Ziffer 1 gibt an, dass die erste Spalte des Suchbereichs verwendet werden soll.

 

Aus diesen drei Formeln D2, F2 und G2 soll eine werden.  Zuerst wird die Formel aus E2 in der INDEX-Formel an die Stelle eingefügt, die das Ergebnis von E2 verwendet:

28.01.2018_ 0009

 

Markieren Sie die Formel aus E2 ohne das = und kopieren Sie es. Markieren Sie in F2 den Bezug E2 und fügen Sie die Formel ein:

=VERGLEICH(E2;$B$1:$B$9;0)

=VERGLEICH(KGRÖSSTE($B$1:$B$9;D2);$B$1:$B$9;0)

 

Im nächsten Schritt wird in der Formel in G2 der Zellbezug F2 durch den Inhalt von F2 ersetzt.

 

28.01.2018_ 0010

 

=INDEX($A$1:$B$9;F2;1)

=INDEX($A$1:$B$9;VERGLEICH(KGRÖSSTE($B$1:$B$9;D2);$B$1:$B$9;0);1)

 

Jetzt können die Spalten E und F gelöscht werden.

28.01.2018_ 0011

Hinterlasse jetzt einen Kommentar

Kommentar hinterlassen

E-Mail Adresse wird nicht veröffentlicht.


*