Hallo Leute,
ich bin auf der Suche nach einer Formel, die folgendes leistet:
Ausgangsdaten:
Spalte A Name
Spalte B Kostenstelle
Spalte C Kosten
---> in der Tabelle, die ich erstellen möchte, soll für jeden Namen die Kostenstelle mit den höchsten Kosten und mit den zweit-höchsten Kosten angezeigt werden. Hierfür habe ich bereits in der Spalte A in jeder Zeile jeden Namen 1 mal eingetragen. In der Spalte B soll dann die Kostenstelle mit den höchsten Kosten angezeigt werden, in Spalte die mit den zweit-höchsten.
Ein Name ist auch immer mehreren Kostenstellen zugeordnet, bis zu 30 Stück.
Es klingt erstmal nach der Formel KGRÖSSTE aber hier habe ich eben mehrere Bedingungen.
Durch die Suchfunktion kam ich leider auch nicht weiter :22:
Danke schon mal.
Hola,
zeige bitte eine Beispieldatei (kein Bild).
Gruß,
steve1da
In I5:J6 soll das gewünschte Ergebnis stehen.
Hola,
z.B. mit Matrixformeln (jeweils mit Strg-Shift-Enter abschließen):
Code:
=INDEX($B$5:$B$31;VERGLEICH(MAX(WENN($A$5:$A$31=$H5;$C$5:$C$31));$C$5:$C$31;0))
Code:
=INDEX($B$5:$B$31;VERGLEICH(KGRÖSSTE(WENN($A$5:$A$31=H5;$C$5:$C$31);2);$C$5:$C$31;0))
Gruß,
steve1da
Hi,
das läßt sich mit AGGREGAT lösen.
Code:
=INDEX($B$5:$B$31;VERGLEICH(AGGREGAT(14;6;$C$5:$C$31/($A$5:$A$31=$H5);SPALTE(A1));$C$5:$C$31;0))
(24.07.2018, 09:09)steve1da schrieb: [ -> ]Hola,
z.B. mit Matrixformeln (jeweils mit Strg-Shift-Enter abschließen):
Code:
=INDEX($B$5:$B$31;VERGLEICH(MAX(WENN($A$5:$A$31=$H5;$C$5:$C$31));$C$5:$C$31;0))
Code:
=INDEX($B$5:$B$31;VERGLEICH(KGRÖSSTE(WENN($A$5:$A$31=H5;$C$5:$C$31);2);$C$5:$C$31;0))
Gruß,
steve1da
Cool, das klappt super!
Aber was ist wenn 2 Kostenstellen an Platz 1 sind, also die Kosten bei der Kostenstelle 9 und 10 jeweils 2000 Euro betragen? Ich möchte, egal in welcher Reihenfolge, dann die Kostenstelle 9 und 10 ausgespuckt bekommen
@Opa Edgar, deine Formel klappt auch, danke, aber selbes Problem wie hier beschrieben... Ich bekomme nur 1 Kostenstelle bei zwei exakt gleichen Werten angezeigt.
Hallöchen,
falls Du mit einer Hilfsspalte arbeiten kannst, eventuell so. Ich füge einfach an den Wert die Kostenstelle an. Hat nur den Nachteil, dass die kleinere Kostenstelle immer nach der größen kommt, bei gleichem Wert.
| Arbeitsblatt mit dem Namen 'Tabelle1' |
| A | B | C | D |
| 13 | Andreas Müller | 9 | 2.000 € | 200009 |
| 14 | Andreas Müller | 10 | 2.000 € | 200010 |
| Zelle | Formel |
| B13 | =B12+1 |
| D13 | =--(TEXT(C13;"000000")&TEXT(B13;"00")) |
| B14 | =B13+1 |
| D14 | =--(TEXT(C14;"000000")&TEXT(B14;"00")) |
| Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016 |
| Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg |
Danke für den Tipp mit der Hilfsspalte.
Nochmal aber zurück zu den o. g. Formeln: Wenn ich vor Andreas Müller noch einen dritten Namen "Peter Schmidt" eintrage, KST 555 und Kosten 2.000 €, also genau so hoch wie die höchsten Kosten bei Andreas Müller, dann spuckt mir die Formel in I5 die KST 555 aus und nicht KST 10 für Andreas Müller. Das Problem tritt bei beiden o.g. Formeln auf.
Hi,
hier auch mit Hilfsspalte:
| Arbeitsblatt mit dem Namen 'Tabelle1' |
| A | B | C | D | E | F | G | H | I | J |
| 4 | Name | KST | Kosten | | | | | Name | KST mit höchsten Kosten | KST mit zweithöchsten Kosten |
| 5 | Andreas Müller | 1 | 100 € | 127 | | | | Andreas Müller | 10 | 11 |
| 6 | Andreas Müller | 2 | 200 € | 125 | | | | Max Mustermann | 23 | 22 |
| 7 | Andreas Müller | 3 | 500 € | 118 | | | | | | |
| 8 | Andreas Müller | 4 | 1.200 € | 107 | | | | | | |
| 9 | Andreas Müller | 5 | 500 € | 119 | | | | | | |
| 10 | Andreas Müller | 6 | 555 € | 117 | | | | | | |
| 11 | Andreas Müller | 7 | 1.651 € | 104 | | | | | | |
| 12 | Andreas Müller | 8 | 1.564 € | 105 | | | | | | |
| 13 | Andreas Müller | 9 | 1.791 € | 103 | | | | | | |
| 14 | Andreas Müller | 10 | 2.000 € | 101 | | | | | | |
| 15 | Andreas Müller | 11 | 2.000 € | 102 | | | | | | |
| 16 | Andreas Müller | 12 | 300 € | 124 | | | | | | |
| 17 | Andreas Müller | 13 | 459 € | 122 | | | | | | |
| 18 | Andreas Müller | 14 | 561 € | 116 | | | | | | |
| 19 | Andreas Müller | 15 | 1.239 € | 106 | | | | | | |
| 20 | Andreas Müller | 16 | 111 € | 126 | | | | | | |
| 21 | Max Mustermann | 17 | 500 € | 18 | | | | | | |
| 22 | Max Mustermann | 18 | 600 € | 14 | | | | | | |
| 23 | Max Mustermann | 19 | 700 € | 13 | | | | | | |
| 24 | Max Mustermann | 20 | 900 € | 10 | | | | | | |
| 25 | Max Mustermann | 21 | 400 € | 23 | | | | | | |
| 26 | Max Mustermann | 22 | 1.000 € | 9 | | | | | | |
| 27 | Max Mustermann | 23 | 1.100 € | 8 | | | | | | |
| 28 | Max Mustermann | 24 | 500 € | 19 | | | | | | |
| 29 | Max Mustermann | 25 | 600 € | 15 | | | | | | |
| 30 | Max Mustermann | 26 | 750 € | 12 | | | | | | |
| 31 | Max Mustermann | 27 | 850 € | 11 | | | | | | |
| Zelle | Formel |
| I5 | =INDEX($B$5:$B$31;VERGLEICH(AGGREGAT(15;6;$D$5:$D$31/($D$5:$D$31>100);SPALTE(A1));$D$5:$D$31;0)) |
| I6 | =INDEX($B$5:$B$31;VERGLEICH(AGGREGAT(15;6;$D$5:$D$31/($D$5:$D$31<100);SPALTE(A2));$D$5:$D$31;0)) |
| D5 | =RANG(C5;$C$5:$C$31)+(A5=$H$5)*100+ZÄHLENWENNS($C$5:C5;C5;$A$5:A5;A5)-1 |
| Verwendete Systemkomponenten: [Windows (32-bit) NT 6.01] MS Excel 2010 |
| Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg |
Hi
frei nach Lupo1.
in I5 (nach rechts und unten kopieren).
Code:
=INDEX($B$1:$B$31;10000*REST(KGRÖSSTE(($H5=$A$5:$A$31)*($C$5:$C$31+ZEILE($A$5:$A$31)%%);SPALTE(A1));1))
mit shift+strg+Enter abschliessen