MS-Office-Forum

Zurück   MS-Office-Forum > Microsoft Office > Microsoft Excel
Registrieren Forum Hilfe Alle Foren als gelesen markieren

Banner und Co.

Antworten
Ads
Themen-Optionen Ansicht
Alt 05.12.2018, 13:58   #1
vincitore123
Neuer Benutzer
Neuer Benutzer
Standard Formel - INDEX mit BEREICH.VERSCHIEBEN

Hallo,

ich bin auf der Suche nach Hilfe für mein aktuelles Excel-Problem.

Und zwar geht's um eine Kombination von INDEX und der Bereich.verschieben Funktion:

In Spalte A meiner Tabelle stehen die Teilnehmernamen.
In Spalte B, C, D und E ist jeweils ein Kursdatum hinterlegt, wobei Spalte B für Modul 1 steht, Spalte C für Modul 2, etc.
A B C D E
1: Modul1 Modul2 Modul 3 Modul4
2: Andi 01.01.2018 01.02.2018 01.01.2018 01.01.2018
3: Max 01.02.2018 01.02.2018 01.02.2018 01.02.2018
4. ... ... ... .. ...

In einem zweite Tabellenblatt wähle ich ein Modul (in C3) und ein Datum (in C2) aus.

Und nun möchte ich eine Liste alle jener Teilnehmer, die Modul1 an Tag XX gewählt haben. Dazu verwende ich folgende Array Formel:

=WENNFEHLER(INDEX(Tabelle2!A:A;KKLEINSTE(WENN(BEREICH.VERSCHIEBEN(Tabelle2!B$4:T abelle2!B$5;0;Tabelle1!$D$3)=$C$2;ZEILE(X$1:X$99));ZEILE(X1)));"")

Da ganze gibt keine Fehlermeldung, spuckt aber auch keine Ergebnisse aus.

Wenn ich anstatt der BEREICH.VERSCHIEBEN Funktion die Matrix fix eingebe funktionert es...

Habt ihr eine Idee wo hier mein Denkfehler liegt?

Vielen Dank für Eure Hilfe!
Andi
vincitore123 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 05.12.2018, 14:00   #2
steve1da
MOF Meister
MOF Meister
Standard

Hola,

lade bitte eine Beispieldatei (kein Bild) hoch.

Gruß,
steve1da
steve1da ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 05.12.2018, 16:14   #3
Der Steuerfuzzi
MOF User
MOF User
Standard

Hallo,

hier m,al ein Versuch:
Code:

=WENNFEHLER(INDEX(tabelle1!A:A;AGGREGAT(15;6;ZEILE($B$2:$E$100)/(INDEX(tabelle1!$B$2:$E$100;;VERGLEICH($C$3;tabelle1!$B$1:$E$1;0))=$C$2);ZEILE(A1)));"")
EDIT: Korrektur in der AGGREGAT-Funktion. Zuerst war versehentlich 14 als erstes Argument - jetzt auf 15 geändert wegen Reihenfolge.

__________________

Gruß
Michael

Geändert von Der Steuerfuzzi (05.12.2018 um 16:54 Uhr).
Der Steuerfuzzi ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 05.12.2018, 16:44   #4
vincitore123
Threadstarter Threadstarter
Neuer Benutzer
Neuer Benutzer
Standard

Hallo,

und vielen Dank für die beiden Antworten. Leider komme ich mit der Aggregatfunktion auch nicht klar.

Im Anhang daher ein vereinfachtes Beispielfile, in dem das Modul und das Datum eingegeben werden können. Je nach Eingabe sollten dann die Teilnehmer als Liste (ohne allfällige Leerzeilen dazwischen) ausgegeben werden.

Ich hoffe das ist verständlich!
wär super wenn ihr mir da einen schritt weiter helfen könntet :-)
Schöne grüße!
andi
Angehängte Dateien
Dateityp: xlsx beispiel.xlsx (9,8 KB, 6x aufgerufen)
vincitore123 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 05.12.2018, 16:52   #5
Der Steuerfuzzi
MOF User
MOF User
Standard

Die Bezüge sind in der Datei anders als in Deiner ersten beschreibung:
Code:

=WENNFEHLER(INDEX(Tabelle1!A:A;AGGREGAT(15;6;ZEILE($B$3:$B$100)/(INDEX(Tabelle1!$B$3:$E$100;;VERGLEICH($B$3;Tabelle1!$B$2:$E$2;0))=$B$4);ZEILE(A1)));"")
Nach unten kopieren.

__________________

Gruß
Michael
Der Steuerfuzzi ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 05.12.2018, 17:04   #6
vincitore123
Threadstarter Threadstarter
Neuer Benutzer
Neuer Benutzer
Standard

Super! Das funktioniert im kleinen Beispiel schonmal!

Könntest du mir die Formel noch erläutern damit ich auch verstehe was hier passiert und das ganze dann in mein großes Projekt übertragen kann?

Insbesondere verstehe ich die angegebenen Bereich nicht wirklich (vorallem ZEILE($B$3:$B$100))

Vielen Dank und schöne Grüße!
Andi

Geändert von vincitore123 (05.12.2018 um 17:16 Uhr).
vincitore123 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 05.12.2018, 17:36   #7
Der Steuerfuzzi
MOF User
MOF User
Standard

=WENNFEHLER(INDEX(Tabelle1!A:A;AGGREGAT(15;6;ZEILE($B$3:$B$100)/(INDEX(Tabelle1!$B$3:$E$100;;VERGLEICH($B$3;Tabelle1!$B$2:$E$2;0))=$B$4);ZEILE(A 1)));"")

Von innen nach Außen:

VERGLEICH($B$3;Tabelle1!$B$2:$E$2;0)
Das ermittelt die Spaltennummer der Überschrift im Bereich B2:E2. Bei A ist das Ergebnis also 1.

INDEX(Tabelle1!$B$3:$E$100;;VERGLEICH($B$3;Tabelle1!$B$2:$E$2;0))
wird somit zu
INDEX(Tabelle1!$B$3:$E$100;;1)
Mit Index kann ich auf Zellen verweisen INDEX(Bereich, Zeile, Spalte). Wenn ich Zeile leer lasse, gibt er alle Zeilen zurück. Das Ergebnis dieser Teilformel ist also die Spalte 1 im Bereich Tabelle1!B3:E100 und das ist Tabelle1!B3:B100

AGGREGAT(15;6;ZEILE($B$3:$B$100)/(INDEX(Tabelle1!$B$3:$E$100;;VERGLEICH($B$3;Tabelle1!$B$2:$E$2;0))=$B$4);ZEILE(A 1))
wird also zu
AGGREGAT(15;6;ZEILE($B$3:$B$100)/(Tabelle1!$B$3:$B$100=$B$4);ZEILE(A1))
In B4 steht das Datum 3.1.18 (Excel speichert das Datum als Zahl, der 3.1.18 wird in Excel zu 43103). Damit wird diese Formel zu:
AGGREGAT(15;6;ZEILE($B$3:$B$100)/(Tabelle1!$B$3:$B$100=43103);ZEILE(A1))
AGGREGAT mit 15 entspricht KKLEINSTE, kann allerdings ohne als Matrixformel eingegeben zu werden auch Matrixoperationen durchführen. Und jetzt wird es kompliziert. Excel ersetzt jetzt die Bezüge durch Ihre Werte als Matrix:
AGGREGAT(15;6;{3;4;5;6;7;.....;100}/({43101;43103;43103;43103;0;.....;0}=43103);ZEILE(A1))
nach dem Vergleich wird daraus:
AGGREGAT(15;6;{3;4;5;6;7;.....;100}/{FALSCH;WAHR;WAHR;WAHR;FALSCH;.....;FALSCH});ZEILE(A1))
Nach dem Teilen wird daraus (WAHR wird als 1 interpretiert, FALSCH als 0):
AGGREGAT(15;6;{#DIV/0;4;5;6;#DIV/0;.....;#DIV/0};ZEILE(A1))
ZEILE(A1) wird zu 1 und es ergibt folgendes Zwischenergebnis:
AGGREGAT(15;6;{#DIV/0;4;5;6;#DIV/0;.....;#DIV/0};1)
Der zweite Parameter (6) bei AGGREGAT bewirkt, dass Fehler ignoriert werden, der letzte Parameter (1) bewirkt, dass der erstkleinste, also der kleinste Wert der LIste zurückgegeben wird:
Ergebnis dieser Funktion ist dann: 4

Aus INDEX(Tabelle1!A:A;AGGREGAT(15;6;ZEILE($B$3:$B$100)/(INDEX(Tabelle1!$B$3:$E$100;;VERGLEICH($B$3;Tabelle1!$B$2:$E$2;0))=$B$4);ZEILE(A 1))) wird dann also:
INDEX(Tabelle1!A:A;4)
Das Ergebnis ist also die Zelle A4 -> der Inhalt ist "Stefan"

Kopiert man die Formel nach unten, passen sich die Bezüge an und aus Zeile(A1) wird nun Zeile(A2) und das ist zwei. Bei AGGREGAT kommt nun der zweitkleinste Wert heraus, das ist 5.
Nun wird die Formel wie oben reduziert und es verbleibt:
INDEX(Tabelle1!A:A;5)
Das Ergebnis ist also die Zelle A4 -> der Inhalt ist "Thomas"

usw. usw.

Du kannst das ganze auch in der Formelauswertung Schritt für Schritt durch klicken auf "Auswerten" nachvollziehen.

__________________

Gruß
Michael
Der Steuerfuzzi ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 05.12.2018, 19:43   #8
steve1da
MOF Meister
MOF Meister
Standard

Hola,

den 2. Index und Vergleich brauchts nicht ;-)

Code:

=WENNFEHLER(INDEX(Tabelle1!A3:A6;AGGREGAT(15;6;ZEILE(A3:A6)-2/((Tabelle1!B2:E2=B3)*(Tabelle1!B3:E6=B4));ZEILE(A1)));"")
Gruß,
steve1da
steve1da ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 06.12.2018, 07:43   #9
vincitore123
Threadstarter Threadstarter
Neuer Benutzer
Neuer Benutzer
Standard

Hallo zusammen und vielen Dank :-)

die erste Variante von steuerfuzzi funktioniert. Allerdings ist mir (trotz der tollen Erklärung) nicht klar, warum der Bereich bei ZEILE($B$3:$B$100) genau dieser ist... gleiches gilt für ZEILE(A1) ganz am Schluss.

Wenn ich das Vorgehen so in einem anderen File nutze, hab ich festgestellt, dass das Ergebnis stark von diesem Bereich und seiner Position abhängt, aber ich komm nicht dahinter woran das liegt? Vielleicht hast noch eine vereinfachte Erklärung bzw. Anleitung auf lager?

Und die zweite Variante von steve liefert leider nur "Max" als ergebnis - aber auch hier ist mir die ZEILE FUnktion und der angeführte Bereich nicht klar.


Wär super wenn ihr hier noch ein bisschen aufklären könntet :-)

Vielen Dank!!

andi
vincitore123 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 06.12.2018, 08:18   #10
steve1da
MOF Meister
MOF Meister
Standard

Hola,

ZEILE($B$3:$B$100) definiert einfach den Bereich, für den die Formel gelten soll, also für insgesamt 98 Zeilen. Zeile(A1) ist einfach ein Zähler, der den 1. Treffer zurückgibt, danach den 2., danach den 3. usw., wenn man die Formel nach unten zieht.
Dein Nachfragen lässt vermuten, dass der Originalaufbau ein anderer ist. Wenn ja, warum teilst du das nicht mit?
Bei meiner Formel kommt ebenfalls "Stefan" heraus - jedenfalls in deiner Beispieldatei.

Gruß,
steve1da
steve1da ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 06.12.2018, 08:32   #11
Der Steuerfuzzi
MOF User
MOF User
Standard

Bei Steves Formel musst Du die Bezüge anpassen (für das kopieren):
=WENNFEHLER(INDEX(Tabelle1!$A$3:$A$6;AGGREGAT(15;6;ZEILE($A$3:$A$6)-2/((Tabelle1!$B$2:$E$2=$B$3)*(Tabelle1!$B$3:$E$6=$B$4));ZEILE(A1)));"")
Dann klappt die Formel selbstverständlich auch.

EDIT: Ich habe mir angewöhnt die Berechnungen auf so wenig Zellen wie mögich zu reduzieren. Mit dem zweiten INDEX/VERGLEICH schränke ich die Berechnung auf eine Spalte ein, so dass der Berechung nicht auf alle Zellen im Bereich stattfindet. Gerade wenn die Tabelle sehr groß ist/wird kann sich das bei der Berechnung bemerkbar machen.

__________________

Gruß
Michael

Geändert von Der Steuerfuzzi (06.12.2018 um 08:35 Uhr).
Der Steuerfuzzi ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 06.12.2018, 15:53   #12
vincitore123
Threadstarter Threadstarter
Neuer Benutzer
Neuer Benutzer
Standard

Hallo!

und nochmal vielen Dank für die Erklärungen.

Im Anhang findet ihr nun nochmal das "eigentliche" File. Ich wollte dieses aus Datenschutzgründen nicht hochladen und habs daher mit einem vereinfachten Beispiel versucht. Da eure Lösungen dort zwar funktionieren, ich es aber trotz der tollen Erklärungen nicht schaffe das ganze in meinen Dokument korrekt umzusetzen hab ich nun die persönlichen Daten aus dem Original gelöscht...

Wär super wenn ihr mir da nochmal weiter helfen könntet :-)
Vielen vielen Dank
Andi
Angehängte Dateien
Dateityp: xlsm teilnehmer.xlsm (112,7 KB, 3x aufgerufen)
vincitore123 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 06.12.2018, 16:00   #13
steve1da
MOF Meister
MOF Meister
Standard

Hola,

Code:

=WENNFEHLER(INDEX('Finanzen Unternehmenspartner'!$E$8:$E$101;AGGREGAT(15;6;ZEILE($A$6:$A$101)-7/(('Finanzen Unternehmenspartner'!$BA$6:$CQ$6=Teilnehmerliste!$B$2)*('Finanzen Unternehmenspartner'!$BA$8:$CQ$101));ZEILE(A1)));"")
Gruß,
steve1da
steve1da ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 06.12.2018, 16:16   #14
vincitore123
Threadstarter Threadstarter
Neuer Benutzer
Neuer Benutzer
Standard

Hallo und vielen Dank,
wenn ich diese Formel im Tabellenblatt "Teilnehmerliste" in das Feld A7 kopiere und runter ziehe, erhalte ich zwar eine Liste, allerdings mit den falschen Namen.

woran kann das noch liegen?

und wo in dieser Formel wird das ausgewählte Datum berücksichtigt?

Hätt nicht gedacht, dass ich so daneben stehe :-)

GLG
andi
vincitore123 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 06.12.2018, 16:17   #15
Der Steuerfuzzi
MOF User
MOF User
Standard

oder bei meiner Formel:
Code:

=WENNFEHLER(INDEX('Finanzen Unternehmenspartner'!$E:$E;AGGREGAT(15;6;ZEILE($B$8:$B$100)/(INDEX('Finanzen Unternehmenspartner'!$BA$8:$QC$100;;VERGLEICH($B$2;'Finanzen Unternehmenspartner'!$BA$6:$QC$6;0))=$C$2);ZEILE(A1)));"")

__________________

Gruß
Michael
Der Steuerfuzzi ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Ads
Antworten


Aktive Benutzer in diesem Thema: 1 (Registrierte Benutzer: 0, Besucher: 1)
 
Themen-Optionen
Ansicht

Forumregeln
Es ist Ihnen nicht erlaubt, neue Themen zu verfassen.
Es ist Ihnen nicht erlaubt, auf Beiträge zu antworten.
Es ist Ihnen nicht erlaubt, Anhänge anzufügen.
Es ist Ihnen nicht erlaubt, Ihre Beiträge zu bearbeiten.

vB Code ist An.
Smileys sind An.
[IMG] Code ist An.
HTML-Code ist An.
Gehe zu


Alle Zeitangaben in WEZ +1. Es ist jetzt 05:02 Uhr.


Partner und Co.
Access-Paradies -Alles rund um die Datenbank Microsoft Access -Code -Programme-Tools -Tipps   Kostenlose Tipps & Tricks, Downloads und Programme   www.kulpa-online.com - Tipps - Tricks - Tutorials - Meinungen - Downloads uvm...   vb@rchiv · Willkommen in der Welt der VB Programmierung   Access-Garhammer - Hier finden Sie jede Menge Beispiel-Datenbanken zu Access und mehr ...   mcseboard.de   Die Top Seite für Excel-VBA-Makros uvm.

Powered by: vBulletin Version 3.6.2 (Deutsch)
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.

Copyright ©2000-2018 MS-Office-Forum. Alle Rechte vorbehalten.
Copyright ©Design: Manuela Kulpa ©Rechte: Günter Kramer
Eine Verwendung der Inhalte in anderen Publikationen, auch auszugsweise,
ist ohne ausdrückliche Zustimmung der Autoren nicht gestattet.