Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1088to1092
1088to1092
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Abfrage / Index Funktion

Forumthread: Abfrage / Index Funktion

Abfrage / Index Funktion
Hannes
Hallo,
Ich möchte gerne eine Tabelle Filtern lassen und die Lösung auf einen anderen Tabellenblatt ausgeben lassen.
Die Abfrage sollte per Eingabe erfolgen. Ich habe dies schon mit der INDEX Funktion probiert (http://www.excelformeln.de/formeln.html?welcher=28) , allerdings krieg ich eine WVweis Verknüpfung nicht hin.
Zum besseren Verständnis eine Beispieldatei:

Die Datei https://www.herber.de/bbs/user/63321.xls wurde aus Datenschutzgründen gelöscht

Vielleicht könnt Ihr mir ja helfen.
Gruß
Hannes
Anzeige
AW: https://www.herber.de/bbs/user/63322.xls
21.07.2009 13:19:05
Hannes
Super,
danke Matthias
AW: https://www.herber.de/bbs/user/63322.xls
22.07.2009 08:50:08
Hannes
Hallo Matthias,
besteht noch die Möglichkeit, das man die Meldung #Zahl ausblenden/unterdrücken kann, damit man die Lösungsliste beliebig verlängern kann (also "runterziehen")?
Gruß Hannes
Anzeige
AW: https://www.herber.de/bbs/user/63322.xls
22.07.2009 09:55:36
Matthias5
Hallo Hannes,
hier für Spalte B, für die anderen entsprechend:
{=WENN(ZEILE(A1)>SUMME((Liste!$D$3:$D$7=$C$4)*(BEREICH.VERSCHIEBEN(Liste!$D$3:$D$7;; VERGLEICH($C$2;Liste!$E$2:$H$2;0))<>"")*1);"";INDEX(Liste!B$3:B$7; KKLEINSTE(WENN((Liste!$D$3:$D$7=$C$4)*(BEREICH.VERSCHIEBEN(Liste!$D$3:$D$7;;VERGLEICH($C$2; Liste!$E$2:$H$2;0))<>"");ZEILE($1:$5));ZEILE(A1)))) }
Gruß,
Matthias
Anzeige
AW: https://www.herber.de/bbs/user/63322.xls
22.07.2009 10:21:09
Hannes
Hi,
danke das klappt schon mal, allerdings kriege ich falsche Werte in der Jahresspalte bei der Abfrage.
z.B. G=39; Jahr=2009; 0 statt 10, dies verhält sich bei den anderen Jahren ähnlich, es werden die Werte von 2008 ausgegeben.
Gruß Hannes
AW: https://www.herber.de/bbs/user/63322.xls
22.07.2009 10:30:43
Matthias5
Hi,
die Formel ist nur für die ersten 2 Salten kopierbar. Für die Jahresspalte musst du den Bezug anpassen.
In Spalte E:
{=WENN(ZEILE(D1)>SUMME((Liste!$D$3:$D$7=$C$4)*(BEREICH.VERSCHIEBEN(Liste!$D$3:$D$7;; VERGLEICH($C$2;Liste!$E$2:$H$2;0))<>"")*1);"";INDEX(Liste!F$3:F$7; KKLEINSTE(WENN((Liste!$D$3:$D$7=$C$4)*(BEREICH.VERSCHIEBEN(Liste!$D$3:$D$7;;VERGLEICH($C$2; Liste!$E$2:$H$2;0))<>"");ZEILE($1:$5));ZEILE(D1)))) }
Gruß,
Matthias
Anzeige
AW: Abfrage / Index Funktion
21.07.2009 13:15:38
David
Hallo Hannes,
schau mal, ob das deinen Vorstellungen entspricht:
Abfrage
 ABCDE
2Abfrage:Jahr:2009   
3         
4  G:25   
5         
6         
7Lösung:       
8  NameVorname252009
9  MayerOtto258_12
10  MeierKarl251

verwendete Formeln
Zelle Formel
D8 =$C$4
E8 =$C$2
B9 {=INDEX(Liste!$B$2 :$B$7;KGRÖSSTE(((Liste!$D$3 :$D$7=$C$4)*(BEREICH.VERSCHIEBEN(Liste!$E$3;;VERGLEICH($C$2;Liste!$E$2 :$H$2;0)-1;5)<>"")*ZEILE(Liste!$D$3 :$D$7));ZEILE($A1)))}
C9 {=INDEX(Liste!$C$2 :$C$7;KGRÖSSTE(((Liste!$D$3 :$D$7=$C$4)*(BEREICH.VERSCHIEBEN(Liste!$E$3;;VERGLEICH($C$2;Liste!$E$2 :$H$2;0)-1;5)<>"")*ZEILE(Liste!$D$3 :$D$7));ZEILE($A1)))}
D9 =$C$4
E9 =BEREICH.VERSCHIEBEN(Liste!$E$3;SUMMENPRODUKT(--(Liste!$B$3 :$B$7=$B9);--(Liste!$C$3 :$C$7=$C9);--(Liste!$D$3 :$D$7=$D9);ZEILE(Liste!$B$3 :$B$7))-3;VERGLEICH(E$8;Liste!$E$2 :$H$2;0)-1)
B10 {=INDEX(Liste!$B$2 :$B$7;KGRÖSSTE(((Liste!$D$3 :$D$7=$C$4)*(BEREICH.VERSCHIEBEN(Liste!$E$3;;VERGLEICH($C$2;Liste!$E$2 :$H$2;0)-1;5)<>"")*ZEILE(Liste!$D$3 :$D$7));ZEILE($A2)))}
C10 {=INDEX(Liste!$C$2 :$C$7;KGRÖSSTE(((Liste!$D$3 :$D$7=$C$4)*(BEREICH.VERSCHIEBEN(Liste!$E$3;;VERGLEICH($C$2;Liste!$E$2 :$H$2;0)-1;5)<>"")*ZEILE(Liste!$D$3 :$D$7));ZEILE($A2)))}
D10 =$C$4
E10 =BEREICH.VERSCHIEBEN(Liste!$E$3;SUMMENPRODUKT(--(Liste!$B$3 :$B$7=$B10);--(Liste!$C$3 :$C$7=$C10);--(Liste!$D$3 :$D$7=$D10);ZEILE(Liste!$B$3 :$B$7))-3;VERGLEICH(E$8;Liste!$E$2 :$H$2;0)-1)
{} Matrixformel mit Strg+Umschalt+Enter abschließen

Tabellendarstellung in Foren Version 4.3


Die Reihenfolge ist gegenüber der Liste vertauscht, das könnte man noch ändern, würde aber aufwändigere Formeln nach sich ziehen. Falls du das zwingend in der "richtigen" Reihenfolge brauchst, muss ich's anpassen.
Gruß
David
Anzeige
AW: Abfrage / Index Funktion
21.07.2009 14:05:33
Hannes
Hallo David,
vielen Dank erst mal,
also das vertauschen ist nicht so schlimm.
Allerdings treten bei mir ein paar Fehler auf.:
1. Wenn ich G=25 und Jahr=2008 eingebe kommt Otto Mayer doppelt und Uschi Schulz gar nicht.
2. G=39 Jahr= 2009 in Lösung: E9 und E10=#Bezug
3. schön wär es noch wenn Abfragen die keinen Wert liefern (z.B. G=39, Jahr=2008) auch in der Lösung keinen Wert liefern bzw. 0
4. und man die Lösungsformeln leicht runterkopieren könnte (was glaub ich schon geht)
Vielen Dank für die Mühe
Hannes
Anzeige
AW: Abfrage / Index Funktion
21.07.2009 14:18:50
David
Hallo Hannes,
in der Matrixformel muss der Index-Bereich immer bei 1 beginnen, kleiner Fehler von mir.
Damit sollten sich deine Fragen beantworten:
Abfrage
 ABCDE
2Abfrage:Jahr:2009   
3         
4  G:39   
5         
6         
7Lösung:       
8  NameVorname392009
9  MeierKarl3910
10  0000
11  0000
12  0000
13  0000
14  #ZAHL!#ZAHL!#ZAHL!#ZAHL!

verwendete Formeln
Zelle Formel
D8 =$C$4
E8 =$C$2
B9 {=INDEX(Liste!$B$1 :$B$7;KGRÖSSTE(((Liste!$D$3 :$D$7=$C$4)*(BEREICH.VERSCHIEBEN(Liste!$E$3;;VERGLEICH($C$2;Liste!$E$2 :$H$2;0)-1;5)<>"")*ZEILE(Liste!$D$3 :$D$7));ZEILE($A1)))}
C9 {=INDEX(Liste!$C$1 :$C$7;KGRÖSSTE(((Liste!$D$3 :$D$7=$C$4)*(BEREICH.VERSCHIEBEN(Liste!$E$3;;VERGLEICH($C$2;Liste!$E$2 :$H$2;0)-1;5)<>"")*ZEILE(Liste!$D$3 :$D$7));ZEILE($A1)))}
D9 {=INDEX(Liste!$D$1 :$D$7;KGRÖSSTE(((Liste!$D$3 :$D$7=$C$4)*(BEREICH.VERSCHIEBEN(Liste!$E$3;;VERGLEICH($C$2;Liste!$E$2 :$H$2;0)-1;5)<>"")*ZEILE(Liste!$D$3 :$D$7));ZEILE($A1)))}
E9 =WENN(SUMMENPRODUKT(--(Liste!$B$3 :$B$7=$B9);--(Liste!$C$3 :$C$7=$C9);--(Liste!$D$3 :$D$7=$D9);ZEILE(Liste!$B$3 :$B$7))=0;0;BEREICH.VERSCHIEBEN(Liste!$E$3;SUMMENPRODUKT(--(Liste!$B$3 :$B$7=$B9);--(Liste!$C$3 :$C$7=$C9);--(Liste!$D$3 :$D$7=$D9);ZEILE(Liste!$B$3 :$B$7))-3;VERGLEICH(E$8;Liste!$E$2 :$H$2;0)-1))
B10 {=INDEX(Liste!$B$1 :$B$7;KGRÖSSTE(((Liste!$D$3 :$D$7=$C$4)*(BEREICH.VERSCHIEBEN(Liste!$E$3;;VERGLEICH($C$2;Liste!$E$2 :$H$2;0)-1;5)<>"")*ZEILE(Liste!$D$3 :$D$7));ZEILE($A2)))}
C10 {=INDEX(Liste!$C$1 :$C$7;KGRÖSSTE(((Liste!$D$3 :$D$7=$C$4)*(BEREICH.VERSCHIEBEN(Liste!$E$3;;VERGLEICH($C$2;Liste!$E$2 :$H$2;0)-1;5)<>"")*ZEILE(Liste!$D$3 :$D$7));ZEILE($A2)))}
D10 {=INDEX(Liste!$D$1 :$D$7;KGRÖSSTE(((Liste!$D$3 :$D$7=$C$4)*(BEREICH.VERSCHIEBEN(Liste!$E$3;;VERGLEICH($C$2;Liste!$E$2 :$H$2;0)-1;5)<>"")*ZEILE(Liste!$D$3 :$D$7));ZEILE($A2)))}
E10 =WENN(SUMMENPRODUKT(--(Liste!$B$3 :$B$7=$B10);--(Liste!$C$3 :$C$7=$C10);--(Liste!$D$3 :$D$7=$D10);ZEILE(Liste!$B$3 :$B$7))=0;0;BEREICH.VERSCHIEBEN(Liste!$E$3;SUMMENPRODUKT(--(Liste!$B$3 :$B$7=$B10);--(Liste!$C$3 :$C$7=$C10);--(Liste!$D$3 :$D$7=$D10);ZEILE(Liste!$B$3 :$B$7))-3;VERGLEICH(E$8;Liste!$E$2 :$H$2;0)-1))
B11 {=INDEX(Liste!$B$1 :$B$7;KGRÖSSTE(((Liste!$D$3 :$D$7=$C$4)*(BEREICH.VERSCHIEBEN(Liste!$E$3;;VERGLEICH($C$2;Liste!$E$2 :$H$2;0)-1;5)<>"")*ZEILE(Liste!$D$3 :$D$7));ZEILE($A3)))}
C11 {=INDEX(Liste!$C$1 :$C$7;KGRÖSSTE(((Liste!$D$3 :$D$7=$C$4)*(BEREICH.VERSCHIEBEN(Liste!$E$3;;VERGLEICH($C$2;Liste!$E$2 :$H$2;0)-1;5)<>"")*ZEILE(Liste!$D$3 :$D$7));ZEILE($A3)))}
D11 {=INDEX(Liste!$D$1 :$D$7;KGRÖSSTE(((Liste!$D$3 :$D$7=$C$4)*(BEREICH.VERSCHIEBEN(Liste!$E$3;;VERGLEICH($C$2;Liste!$E$2 :$H$2;0)-1;5)<>"")*ZEILE(Liste!$D$3 :$D$7));ZEILE($A3)))}
E11 =WENN(SUMMENPRODUKT(--(Liste!$B$3 :$B$7=$B11);--(Liste!$C$3 :$C$7=$C11);--(Liste!$D$3 :$D$7=$D11);ZEILE(Liste!$B$3 :$B$7))=0;0;BEREICH.VERSCHIEBEN(Liste!$E$3;SUMMENPRODUKT(--(Liste!$B$3 :$B$7=$B11);--(Liste!$C$3 :$C$7=$C11);--(Liste!$D$3 :$D$7=$D11);ZEILE(Liste!$B$3 :$B$7))-3;VERGLEICH(E$8;Liste!$E$2 :$H$2;0)-1))
B12 {=INDEX(Liste!$B$1 :$B$7;KGRÖSSTE(((Liste!$D$3 :$D$7=$C$4)*(BEREICH.VERSCHIEBEN(Liste!$E$3;;VERGLEICH($C$2;Liste!$E$2 :$H$2;0)-1;5)<>"")*ZEILE(Liste!$D$3 :$D$7));ZEILE($A4)))}
C12 {=INDEX(Liste!$C$1 :$C$7;KGRÖSSTE(((Liste!$D$3 :$D$7=$C$4)*(BEREICH.VERSCHIEBEN(Liste!$E$3;;VERGLEICH($C$2;Liste!$E$2 :$H$2;0)-1;5)<>"")*ZEILE(Liste!$D$3 :$D$7));ZEILE($A4)))}
D12 {=INDEX(Liste!$D$1 :$D$7;KGRÖSSTE(((Liste!$D$3 :$D$7=$C$4)*(BEREICH.VERSCHIEBEN(Liste!$E$3;;VERGLEICH($C$2;Liste!$E$2 :$H$2;0)-1;5)<>"")*ZEILE(Liste!$D$3 :$D$7));ZEILE($A4)))}
E12 =WENN(SUMMENPRODUKT(--(Liste!$B$3 :$B$7=$B12);--(Liste!$C$3 :$C$7=$C12);--(Liste!$D$3 :$D$7=$D12);ZEILE(Liste!$B$3 :$B$7))=0;0;BEREICH.VERSCHIEBEN(Liste!$E$3;SUMMENPRODUKT(--(Liste!$B$3 :$B$7=$B12);--(Liste!$C$3 :$C$7=$C12);--(Liste!$D$3 :$D$7=$D12);ZEILE(Liste!$B$3 :$B$7))-3;VERGLEICH(E$8;Liste!$E$2 :$H$2;0)-1))
B13 {=INDEX(Liste!$B$1 :$B$7;KGRÖSSTE(((Liste!$D$3 :$D$7=$C$4)*(BEREICH.VERSCHIEBEN(Liste!$E$3;;VERGLEICH($C$2;Liste!$E$2 :$H$2;0)-1;5)<>"")*ZEILE(Liste!$D$3 :$D$7));ZEILE($A5)))}
C13 {=INDEX(Liste!$C$1 :$C$7;KGRÖSSTE(((Liste!$D$3 :$D$7=$C$4)*(BEREICH.VERSCHIEBEN(Liste!$E$3;;VERGLEICH($C$2;Liste!$E$2 :$H$2;0)-1;5)<>"")*ZEILE(Liste!$D$3 :$D$7));ZEILE($A5)))}
D13 {=INDEX(Liste!$D$1 :$D$7;KGRÖSSTE(((Liste!$D$3 :$D$7=$C$4)*(BEREICH.VERSCHIEBEN(Liste!$E$3;;VERGLEICH($C$2;Liste!$E$2 :$H$2;0)-1;5)<>"")*ZEILE(Liste!$D$3 :$D$7));ZEILE($A5)))}
E13 =WENN(SUMMENPRODUKT(--(Liste!$B$3 :$B$7=$B13);--(Liste!$C$3 :$C$7=$C13);--(Liste!$D$3 :$D$7=$D13);ZEILE(Liste!$B$3 :$B$7))=0;0;BEREICH.VERSCHIEBEN(Liste!$E$3;SUMMENPRODUKT(--(Liste!$B$3 :$B$7=$B13);--(Liste!$C$3 :$C$7=$C13);--(Liste!$D$3 :$D$7=$D13);ZEILE(Liste!$B$3 :$B$7))-3;VERGLEICH(E$8;Liste!$E$2 :$H$2;0)-1))
B14 {=INDEX(Liste!$B$1 :$B$7;KGRÖSSTE(((Liste!$D$3 :$D$7=$C$4)*(BEREICH.VERSCHIEBEN(Liste!$E$3;;VERGLEICH($C$2;Liste!$E$2 :$H$2;0)-1;5)<>"")*ZEILE(Liste!$D$3 :$D$7));ZEILE($A6)))}
C14 {=INDEX(Liste!$C$1 :$C$7;KGRÖSSTE(((Liste!$D$3 :$D$7=$C$4)*(BEREICH.VERSCHIEBEN(Liste!$E$3;;VERGLEICH($C$2;Liste!$E$2 :$H$2;0)-1;5)<>"")*ZEILE(Liste!$D$3 :$D$7));ZEILE($A6)))}
D14 {=INDEX(Liste!$D$1 :$D$7;KGRÖSSTE(((Liste!$D$3 :$D$7=$C$4)*(BEREICH.VERSCHIEBEN(Liste!$E$3;;VERGLEICH($C$2;Liste!$E$2 :$H$2;0)-1;5)<>"")*ZEILE(Liste!$D$3 :$D$7));ZEILE($A6)))}
E14 =WENN(SUMMENPRODUKT(--(Liste!$B$3 :$B$7=$B14);--(Liste!$C$3 :$C$7=$C14);--(Liste!$D$3 :$D$7=$D14);ZEILE(Liste!$B$3 :$B$7))=0;0;BEREICH.VERSCHIEBEN(Liste!$E$3;SUMMENPRODUKT(--(Liste!$B$3 :$B$7=$B14);--(Liste!$C$3 :$C$7=$C14);--(Liste!$D$3 :$D$7=$D14);ZEILE(Liste!$B$3 :$B$7))-3;VERGLEICH(E$8;Liste!$E$2 :$H$2;0)-1))
{} Matrixformel mit Strg+Umschalt+Enter abschließen

Tabellendarstellung in Foren Version 4.3


Die letzte Zeile ergibt einen Fehler, da der indizierte Bereich eine Zeile vorher endet. Da ich deine Tabelle nicht kenne, kann ich das momentan nicht vermeiden. Wenn du bei dir z. Bsp. den Bereich von 1-100 verwendest, sollte dieser Fehler bei der 101. Formel kommen, die du in der Auswertungstabelle nach unten kopierst.
Allerdings wird dieses Konstrukt bei großen Datenmengen sehr langsam werden!
Gruß
David
Anzeige
;

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige