Office: Wer muss noch was machen!?

Hallo,

ich habe ein Problem, es besteht darin rauszufinden wie man Leute auflisten kann in einer Tabelle, die in einer anderen Tabelle bestimmte Voraussetzungen nicht erfüllt haben.
Excel 2010

Beispiel:



Die Gelb markierte Tabelle1 hat die Aufgabe, mir anzuzeigen wer was schon hat. Ist auch alles kein Problem, denn das ist sehr übersichtlich, durch die grünen Kreuze. Die Blau markierte Tabelle2 soll mir hingegen nur unter den jeweiligen Disziplinen den Namen, derer anzeigen, die diese Disziplin nicht haben.

Frage:

1. Wie verknüpfe ich Tabelle1 mit Tabelle2
2. Wie bekomme ich die gewünschten Daten aus Tabelle1 in die Tabelle2.

Ich würde mich tierisch freuen, wenn sich Jemand dazu bereiterklärt mir das näher zu bringen, denn ich steh absolut auf dem Schlauch. Ich habe mir schon viele ähnliche Szenarien angesehen, jedoch kann ich es einfach nicht umsetzen.


Gruß

 

Hallo!

Schau mal hier. http://www.office-loesung.de/ftopic661760_0_0_asc.php

Gruß
Wilfried

 

Hallo Wilfried,

ja ok ich habs verstanden, ein anderer Beitrag von mir in einem anderen Forum:-) ich fahre immer mehrgleisig um verschieden Ansätze oder einfachere zu bekommen. Dann kannst du mir vielleicht auch erklären wie diese Formel sich zusammensetzt um es zu verstehen:

F2: =VERGLEICH(0;Tabelle1!B$7:INDEX(Tabelle1!B:B;F1+1);)+F1
J2: =WENNFEHLER(INDEX(Tabelle1!$A$1:$A$7;F2);"")

das F-H Hilfsspalten sind die sonst wo sein können, versteh ich ja, jedoch die Formel ansich nicht! Könntest du mir da kurz helfen?

 

Hallo,

es funktioniert auch ohne Hilfszellen:
angenommen, Du hast
1) in A2:A30 eine Namensliste
2) in B2:B30 eine Liste, in der durch ein "+" gekennzeichnet ist, ob der in Spalte A befindliche Name die Leistung erbracht hat, sonst eine Leerzelle.

Du kannst dann eine Abfrageliste ab B40, B41, ... erstellen mit den Namen, die die Leistung noch nicht erbracht haben:
Formel in B40: {=WENNFEHLER(INDEX($A$2:$A$30;KKLEINSTE(WENN(B$2:B$30="";ZEILE($1:$29));ZEILE(A1)));"")}
(Achtung: Matrixformel; geschweifte Klammer nicht eingeben; die Formeleingabe in der Formeleditierzeile jedoch mit UMSCHALT+STRG+EINGABE abschließen!!)
Diese Formel in die Zellen B41, B42, .... hinunterkopieren/ziehen.

Genauso kannst Du eine alternative Abfrageliste ab C40, C41,... erstellen mit den Namen, die die Leistung schon erbracht haben:
Formel in C40: {=WENNFEHLER(INDEX($A$2:$A$30;KKLEINSTE(WENN(B$2:B$30="+";ZEILE($1:$29));ZEILE(A1)));"")}
(Achtung: Matrixformel; geschweifte Klammer nicht eingeben; die Formeleingabe in der Formeleditierzeile jedoch mit UMSCHALT+STRG+EINGABE abschließen!!)
Diese Formel in die Zellen C41, C42, .... hinunterkopieren/ziehen.


Was die Erklärung der Formeln Deines letzten Postings in diesem Thread betrifft:
1a) In Zelle F1, F2, F3, ... werden die Zeilennummern der Einträge ermittelt, die die Suchbedingung erfüllen.
1b) Dabei steht in F1 = 0 als Vorgabewert für die Formel in F2. Genauso wird der Wert in F2 verwendet, um als Vorgabewert für F3 zu dienen, usw.
1c) Dieser Vorgabewert der Vorgängerzeile wird in der laufenden Zeilenformel (zB. in F2) in folgendem Formelbestandteil verwendet: INDEX(Tabelle1!B:B;F1+1). In F3 heißt dieser Teil entsprechend INDEX(Tabelle1!B:B;F2+1). Dieser Formelteil bewirkt nichts anders, als dass in Spalte B nachgeschaut wird und zwar in Zeile F1+1, um so die Zelladresse/Zellbezug dieser Zelle zu ermitteln. Somit liefert dieser Teil, da in F1 0 enthalten ist, den Zellbezug "$B$2" und dieser Bezug wird mit dem davor stehenden Bezug "Tabelle1!B$7" kombiniert wegen des ":" zum Zellbezugswert "Tabelle1!B$7:$B$2", was genauso anders herum gelesen werden kann als "Tabelle1!$B$2:B$7".
1d) Dieser Bereich/Bezug wird in der umgebenden VERGLEICH-Funktion verwendet, um nach dem Kriteriumswert 0 zu suchen, wobei dann die VERGLEICH-Funktion die Zeilennummer der in diesem Bereich gefundenen Zelle mit Wert 0 liefert.
1e) Zu dieser Zeilennummer wird schließlich noch der Wert der Vorgängerzelle +F1 addiert, sodass man so die Zeilennummer der aktuellen Fundstelle erhält.
2a) Die Formel in J2 ist vergleichsweise einfach. Sie verwendet diese Zeilennummer aus 1e) bzw. aus F2, um über die INDEX-Funktion im Bereich Tabelle1!$A$1:$A$7 den Eintrag mit dieser Zeilennummer zurückzugeben, was ja dann der Name ist, der das Kriterium in Spalte B besitzt.

 

Hallo,

ok das hört sich schonmal gut an. Wie kann ich nun diese Formel auf dem anderen Tabellenblatt verknüpfen? Muss Ich dazu direkt nach wennfehler, das eigentliche tabellenblatt angeben?

 

Hallo,

das wäre eigentlich Stoff für elementare Excelformelhandhabung:
Angenommen Du hast Deine Daten im Arbeitsblatt "Tabelle1" und Du willst aus dem Arbeitsblatt "Tabelle2" heraus die Abfrage mit vorgenannter Formel ausführen, dann musst Du statt
{=WENNFEHLER(INDEX($A$2:$A$30;KKLEINSTE(WENN(B$2:B$30="";ZEILE($1:$29));ZEILE(A1)));"")}
folgende Formel verwenden:
{=WENNFEHLER(INDEX(Tabelle1!$A$2:$A$30;KKLEINSTE(WENN(Tabelle1!B$2:B$30="";ZEILE($1:$29));ZEILE(A1)));"")}

und ähnlich wäre es bei der von mir erwähnten Alternativabfrage:
{=WENNFEHLER(INDEX(Tabelle1!$A$2:$A$30;KKLEINSTE(WENN(Tabelle1!B$2:B$30="+";ZEILE($1:$29));ZEILE(A1)));"")}


Bezug nehmend auf Deine kürzlich mitgeteilte Funktion:
=WENNFEHLER(INDEX($C$9:$C$87;KKLEINSTE(WENN(F$9:F$ 87="";ZEILE($8:$86));ZEILE(C8)));"")
Diese muss lauten:
{=WENNFEHLER(INDEX($C$9:$C$87;KKLEINSTE(WENN(F$9:F$ 87="";ZEILE($1:$79));ZEILE(C1)));"")}
Der Formelteil ZEILE($1:$79)
muss mit $1 beginnen und muss mit $79 enden, wobei man die 79 aus der Zeilenanzahl des Bereiches $C$9:$C$87 (bzw. F$9:F$87) erhält.
Der Formelteil ZEILE(C1)
kann genauso gut ZEILE(A1) oder ZEILE(B1) lauten; er muss nur auf jeden Fall die Zeilennummer 1 ohne Dollarzeichen davor enthalten. Er dient nur zum bequemen Kopieren der Formel in die darunterliegenden Zellen. Denn aus ZEILE(C1) wird durch Kopieren/Ziehen in der nächsten Zeile ZEILE(C2), wobei ZEILE(C1) (oder ZEILE(A1) oder ZEILE(B1)) den Wert 1 liefert und ZEILE(C2) den Wert 2.

 

Ich habe deine Version der Formel getestet und auf meine Tabelle angepasst, jedoch macht er nicht so das was ich erhofft habe, er gibt zwar namen aus aber nicht immer die richtigen. Was habe ich für ein fehler gemacht? hier die geänderte Formel:

=WENNFEHLER(INDEX($C$9:$C$87;KKLEINSTE(WENN(F$9:F$87="";ZEILE($8:$86));ZEILE(C8)));"")

-->c9-87 stehen meine Namen
-->f9-f87 sind die felder in denen entweder ein "+" ist oder die Zelle ist leer
-->8:86 interpretier ich so dass ich 1 vor c9 und eins vor c87 angeben muss
-->Zeile C8 am Ende ist die Zeile vor C9 mit dem ersten Namenseintrag.

wo sind die Fehler?

 

ok er verknüpft die Tabelle richtig und listet Namen auf, jedoch listet er alle Namen auf und nicht nur die, die eine leere Zelle haben.

 

Hallo,

Bezug nehmend auf Deine kürzlich mitgeteilte Funktion:
=WENNFEHLER(INDEX($C$9:$C$87;KKLEINSTE(WENN(F$9:F$ 87="";ZEILE($8:$86));ZEILE(C8)));"")
Diese muss lauten:
{=WENNFEHLER(INDEX($C$9:$C$87;KKLEINSTE(WENN(F$9:F$ 87="";ZEILE($1:$79));ZEILE(C1)));"")}
Der Formelteil ZEILE($1:$79)
muss mit $1 beginnen und muss mit $79 enden, wobei man die 79 aus der Zeilenanzahl des Bereiches $C$9:$C$87 (bzw. F$9:F$87) erhält.
Der Formelteil ZEILE(C1)
kann genauso gut ZEILE(A1) oder ZEILE(B1) lauten; er muss nur auf jeden Fall die Zeilennummer 1 ohne Dollarzeichen davor enthalten. Er dient nur zum bequemen Kopieren der Formel in die darunterliegenden Zellen. Denn aus ZEILE(C1) wird durch Kopieren/Ziehen in der nächsten Zeile ZEILE(C2), wobei ZEILE(C1) (oder ZEILE(A1) oder ZEILE(B1)) den Wert 1 liefert und ZEILE(C2) den Wert 2.

 

Hallo,

dann kannst Du es eventuell mit folgender Formelvariante probieren:
=WENNFEHLER(INDEX($C$9:$C$87;KKLEINSTE(WENN(ISTLEER(F$9:F$ 87);ZEILE($1:$79));ZEILE(C1)));"")
Ich habe jedenfalls keine Probleme mit beiden Varianten.

 

Nein also da macht er jetzt nur jedesmal die Zahl "0" rein in die Zelle hhmm das is sehr komisch, oder liegt es an der Formatierung meiner eigentlichen Tabelle? ich evrsuch mal ne blanko zu machen und sehe dann ob der Fehler noch existiert

 

Jawoll Fehler gefunden, es muss an der Formatierung der eigentlichen Tabelle liegen, denn bei einer neuen Tabelle und neuen Daten geht es jetzt auch bei mir!!! Super vielen >Dank für die reichliche Hilfe.


Gruß

Mark

 

Hallo,

könntest du dir vorstellen, warum es nicht geht bei meiner Grundtabelle, aber bei jeder neuen die ich erstelle?

 

Guten Morgen,

ich habe heut ein wenig rumprobiert und auch den Fehler gefunden.

Ergebnis:

Gestern haben wir gesagt, wenn wir eine andere Tabelle verknüpfen wollen bei der Ausgabe, dann schriben wir:
=WENNFEHLER(INDEX(Tabelle1!$C$9:$C$87;KKLEINSTE(WENN(F$9:F $ 87="";ZEILE($1:$79));ZEILE(C1)));"")

hierbei listet die Formel ja stumpf alle Namen auf, ich fragte mich wieso das so ist und wenn ich diese Formel ohne Verknüpfung in dieselbe Tabelle mache funktioniert es komischerweise. Nun ich habe den doch recht einfachen Fehler gefunden:
=WENNFEHLER(INDEX(Tabelle1!$C$9:$C$87;KKLEINSTE(WENN(Tabelle1!F$9:F $ 87="";ZEILE($1:$79));ZEILE(C1)));"")

wie man sieht, muss die Verknüpfung 2x hinzugefügt werden, dann zeigt er auch nur die Namen,die man benötigt :-)

Nochmals danke für die Hilfe und ich hab es nun auch ganz sicher verstanden, es is nich wirklich kompliziert.


Gruß


Mark

 

Hallo Mark,

in meinem gestrigen Posting von 18:56 Uhr hatte ich Dir genau diese Formel mit den beiden Einfügungen von "Tabelle1!" mitgeteilt.