Microsoft Excel

Herbers Excel/VBA-Archiv

Kombination von INDEX/Vergleich für Wertevergleich

Betrifft: Kombination von INDEX/Vergleich für Wertevergleich von: Magnusson
Geschrieben am: 17.07.2008 13:27:44

Hallo,

Kleines Problem.
Ich möchte Arbeitszeiten analysieren die ich für einen Kunden aufbringe:

Als Basis eine Liste mit beispielsweise folgenden Daten, die in dem Bereich A2:C150 aufgelistet sind:

Spalte A Spalte B Spalte C
Nr. 1 Firma A 13 Stunden
Nr. 2 Firma B 22 Stunden
Nr. 3 Firma C 13 Stunden
... ... ...
n-1 ... ...
n ... ...

Jetzt möchte ich dass mir in einem anderen Bereich des Registerblattes aus dieser Liste die 5 Firmen angezeigt werden für die ich den größten Zeitaufwand (Spalte C) hatte. Dabei ist in dem Beispiel ganz wichtig, das zwischen Firma 1 und 3 differenziert wird.

Dafür habe ich folgende Formelkombination (als Anzeige des Firmennamens) für jede der 5 Firmen mit den größten Ausreißern verwendet:

=INDEX(B:B;VERGLEICH(C256;C:C;0))

In Zelle C256 steht der Stundenaufwand für diese Firma nach der Formel:
=KGRÖSSTE($C$2:$C$152;2)

Das klappt auch alles soweit, das er mir folgendes anzeigen würde:

Firma B 22 Stunden
Firma A 13 Stunden
Firma A 13 Stunden

Das Problem ist also er differenziert nicht zwischen den beiden Firmen (Firma A/Firma C)

Ich habe leider keine Idee mehr -
Kann jemand helfen?

Danke,
M.

  

Betrifft: AW: Kombination von INDEX/Vergleich für Wertevergleich von: mpb
Geschrieben am: 17.07.2008 14:15:16

Hallo,

für den größten Wert:

=KGRÖSSTE($C$2:$C$150;1)

für die zugehörige Firma

=INDEX($B$2:$B$150;VERGLEICH(KGRÖSSTE($C$2:$C$150;1);$C$2:$C$150;0))


Für die Angaben für die 2.-5.größten Werte die KGRÖSSTE-Funktion jeweils anpassen.

Gruß
Martin


  

Betrifft: AW: Kombination von INDEX/Vergleich für Wertevergleich von: Magnusson
Geschrieben am: 17.07.2008 16:13:31

Hallo Martin,

Das funktioniert leider nicht so wie es angedacht war.
Ich bekomme den Namen der zweiten Firma, die angezeigt werden sollte und welche den gleichen
Stundenaufwand hat wie Firma A nicht angezeigt, er zeigt mir jetzt anstatt Firma C - Welche er anzeigen müsste den Firmennamen an, der unmittelbar unter Firma A steht - Nämlich Firma B.

Firma C steht in meiner Liste nicht unmittelbar unter Firma A.

So ist die Ausgangsliste:

Spalte A Spalte B Spalte C
Nr. 1 Firma A 13 Stunden
Nr. 2 Firma B 22 Stunden
Nr. 3 Firma C 13 Stunden

Angezeigt wird es jetzt wie folgt:

Firma A 13 Stunden
Firma B 13 Stunden
....

Habe ich was falsch gemacht oder ist hier noch ein Fehler in der Formel?
Ich war zuerst auf dem Pfad, dass es gar nicht möglich ist mit der Arbeitsblattfunktion VERGLEICHE das Problem zu lösen, da es hier nur 3 Optionen gibt (-1,0,1).
Und ich kann Excel nicht mitteilen, dass er die Zelle nehmen soll, welche auf den gleichen Wert referenziert.
Entweder genau den -0
Den nächstkleineren -1
Den nächsthöheren 1

Eine Idee?

Danke und Gruss,
M.


  

Betrifft: AW: Kombination von INDEX/Vergleich für Wertevergleich von: mpb
Geschrieben am: 17.07.2008 17:28:44

Hallo,

das Problem habe ich leider übersehen. Versuche mal folgende Matrixformel für den größten Wert (letzten Parameter der KGRÖSSTE-Funktion für die 2.-5.größten Werte anpassen):

=INDEX($B$2:$B$150;VERGLEICH(KGRÖSSTE(($C$2:$C$150)+0,0000000001*ZEILE(1:149);1);($C$2:$C$150) +0,0000000001*ZEILE(1:149);0))

Eingabe mit STRG-SHIFT-ENTER, nicht mit ENTER abschließen.

Gruß
Martin


  

Betrifft: AW: Kombination von INDEX/Vergleich für Wertevergleich von: Magnusson
Geschrieben am: 17.07.2008 18:03:43

Sorry,
Das habe ich noch ein Problem.
1. Hinter dem Bereich KGRÖSSTE(($C$2:$C$150) - Fehlt hier nicht noch ein Parameter? Excel meckert das zu wenig Argumente eingegeben wurden.

2. Muss ich nur die eine Zelle markieren oder die gesamte Matrix?
3. Was macht die Formel eigentlich?

Danke und VG,
Denis


  

Betrifft: AW: Kombination von INDEX/Vergleich für Wertevergleich von: mpb
Geschrieben am: 17.07.2008 18:29:18

Hallo Denis,

m.E. müsste es funktionieren. Ich habe mal eine Beispieldatei hochgeladen:

https://www.herber.de/bbs/user/53907.xls


Der "Trick" besteht darin, KGRÖSSTE nicht auf die Originaldaten anzuwenden, sondern "vorher" zu jedem Wert einen sehr kleinen aber eindeutig von allen anderen verschiedenen Wert zu addieren. Das erledigt der Teil

+0,0000000001*ZEILE(1:149)

Du musst die Formel in eine (Ziel)Zelle eintragen und die Eingabe mit STRG-SHIFT-ENTER abschließen.

Gruß
Martin


  

Betrifft: es geht auch etwas einfacher ... von: neopa
Geschrieben am: 17.07.2008 18:52:22

Hallo Martin, Hallo Denis,

... z.B. so:

 ABCDEF
1      
21Firma A13 22Firma B
32Firma B22 22Firma G
43Firma C13 13Firma A
54Firma D9 13Firma C
65Firma E13 13Firma E
76Firma F7 9Firma D
87Firma G22 7Firma F
9      

Formeln der Tabelle
ZelleFormel
E2=KGRÖSSTE(C:C;ZEILE(A1))
F2{=INDEX(B:B;KKLEINSTE(WENN(C$2:C$150=E2;ZEILE(C$2:C$150)); ZÄHLENWENN(E$2:E2;E2)))}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4


Formeln einfach durch Ziehen nach unten kopieren.

Gruß Werner,
.. , - ...


  

Betrifft: AW: es geht auch etwas einfacher ... von: Magnusson
Geschrieben am: 18.07.2008 08:14:08

Hallo,

Danke euch -
Problem gelöst.

VG


  

Betrifft: AW: Kombination von INDEX/Vergleich für Wertevergleich von: Magnusson
Geschrieben am: 18.07.2008 11:27:29

Hallo,
Schaffe es irgendwie nicht die Matrixformel einzugeben, so dass die Zeilen die nicht fest referenziert werden automatisch hochgezählt werden.

Ich markiere vorher alle Zellen - gehe dann ins formelfenster und bestätige die Eingabe durch CTRL-Shift und Enter.

Weißt du wo das Problem liegt?

Danke,
M.


  

Betrifft: AW: Kombination von INDEX/Vergleich für Wertevergleich von: mpb
Geschrieben am: 18.07.2008 11:32:02

Wie ich schon schrieb: Formel in eine Zelle eintragen, mit STRG-SHIFT-ENTER abschließen. Danach diese Formel in die anderen Zellen kopieren.

Gruß
Martin


  

Betrifft: AW: Kombination von INDEX/Vergleich für Wertevergleich von: Magnusson
Geschrieben am: 18.07.2008 11:49:51

Alles klar,
Danke.
Hat jetzt geklappt.

VG,
M.


 

Beiträge aus den Excel-Beispielen zum Thema "Kombination von INDEX/Vergleich für Wertevergleich"