Office: (Office 2010) UND,WENN,SVerweis...keine Ahnung...

Hallo zusammen,

ich bräuchte dringend Hilfe, da ich allein lediglich die einfachsten Funktionen umgesetzt kriege.
Folgendes Problem...
Ich habe eine Personalliste, welche mir zusätzlich zu Name, Vorname und ein paar anderen Infos, die Arbeitsbereiche per "x" anzeigt.
Dazu habe ich eine Spalte, die ich ebenfalls mit "x" befülle, um nachzuhalten wer anwesend ist.
Ich habe es mit Hilfe eines anderen Threads bereits geschafft, die dort vorgeschlagene Formel für mich so anzupassen, dass mir in einer separaten Tabelle alle diejenigen automatisch angezeigt werden, die Anwesend sind. Was mir jetzt noch fehlt, ist der jeweilige Arbeitsbereich dahinter.
Als Anhang ein Beispiel...Dort habe ich den Bereich händisch befüllt.

Ich hoffe, man versteht meine eher dürftige Beschreibung.

Danke schon jetzt...

 

Hallo,

in der beiliegenden Excel-Datei habe ich neue Formeln in den Zellen O8:S40 hinzugefügt.
Dazu habe ich in Spalte O eine Hilfsspalte für die Zeilennummern eingefügt, auf die dann die Spalten P:S zugreifen.

(Anmerkung: Es wird dir aufgefallen sein, dass deine Formeln in den Spalten K:N die letzten Zeilen der Spalten A:I nicht berücksichtigen - konkret die letzten 3 Frauen in den Zeilen 34, 36 und 40)

 

hi,

also erstmal vielen Dank für die Antwort und deine Mühe und ich entschuldige mich schon jetzt, wenn ich mich blöd anstelle.
Nein, das war mir vorher nicht aufgefallen, dass die letzten Zeilen nicht berücksichtigt wurden, aber in meiner richtigen Datei funktioniert es bis zum letzten Eintrag.
In der Liste die du jetzt angehangen hast, funktioniert deine Formel echt mega gut, aber ich krieg es scheinbar nicht hin deine Formeln für meine Liste anzupassen. Excel spuckt mir bei Name und Vorname immer wieder die Anrede aus, keine Ahnung wie das bei dir funktioniert, wenn überall die selbe Formel drin steht. Die Hilfspalte habe ich ebenfalls hinzugefügt, da ist es immer nur die erste Zahl die hinhaut. Ich habe die Formeln mit STRG+Shift+Return abgeschlossen, müsste ja eigentlich richtig sein, glaub ich. Und bei der Spalte um die es mir eigentlich ging "Bereich", die bleibt komplett leer...bin am verzweifeln...was mach ich denn falsch? Und wenn ich die Formel in der Hilfspalte nach unten ziehe, zeigt er mir halt immer eine Zahl an, auch wenn gar kein Kreuz gesetzt ist.

 

Hallo,

ich beziehe mich im Folgenden auf die Datei, die ich dir hochgeladen habe.
Vorbemerkung: In Excel gibt es mehrere Formeltypen:

1. Es gibt ganz "normale" Formeln: Diese liefern 1 Ergebnis und zwar für die Zelle, in der sie drinnen stehen. Sie werden in der betreffenden Zelle eingegeben, indem man zuerst die aktive Zelle auf diese Zelle stellt, die Formel in die Formeleditierzeile, beginnend mit "=", eingibt und die Formel schließlich mit ENTER (=RETURN) abschließt. Diese Ein-Zellen-Formel ist damit voll funktionstüchtig.
2. Es gibt aber auch Ein-Zellen-ARRAY-Funktionen: Diese unterscheiden sich von denen aus 1) vorerst nur dadurch, dass man statt EINGABE am Ende der Formeleingabe die Tastenkombination STRG+UMSCHALT+EINGABE (CTRL+SHIFT+ENTER) einsetzen muss. Danach erscheinen sie in geschweiften Klammern eingeschlossen. Diese Formeln verwenden intern jedoch ARRAYS bzw. MATRITZEN, liefern aber genauso wie die Formeln aus 1) nur 1 Ergebnis zurück für die Zelle, in der sie stehen.
3. Und es gibt auch Mehr-Zellen-ARRAY-Funktionen: Diese unterscheiden sich von denen aus 2) darin, dass 1 einzige Formel gleich für mehrere Zellen zuständig ist und für diese die Ergebnisse liefert. In diesem Fall muss man zuerst diesen Zellbereich markieren, die Formel in die Formeleditierzeile eingeben beginnend mit "=" und zum Abschluss wiederum die Tastenkombination STRG+UMSCHALT+EINGABE verwenden. Die Formel erscheint dann wie unter 2), jedoch in allen Zellen dieses Zellbereiches.

1) Die Hilfsspalte (Bereich O9:O40):
In diesen 32 Zellen steht tatsächlich nur 1 einzige Formel drinnen, denn es handelt sich um eine Mehr-Zellen-ARRAY-Funktion.
Ich habe zuerst den Zellbereich O9:O40 markiert, dann in die Formeleditierzeile folgende Funktion eingegeben
=WENNFEHLER(AGGREGAT(15;6;($A$2:$A$40)/($B$2:$B$40="x");ZEILE($1:$32));"")
und die Formeleingabe mit der Tastenkombination STRG+UMSCHALT+EINGABE abgeschlossen, sodass die Formel in geschweiften Klammern eingeschlossen erschien.
Wenn du versuchst, zB. aus Zelle O12 die Formel zu löschen, erhältst du die Fehlermeldung "Teile einer Matrix können nicht geändert werden." Man darf also einen solchen Zellbereich nur im Ganzen ändern, indem man den Bereich O9:O40 markiert und dann die Änderung durchführt - zB. Löschen, Ändern, Wertkopieren, etc.
Diese Hilfsspalte liefert die Nr. aus Spalte A für alle Zeilen, die in Spalte B ein "x" enthalten.
Gesteuert wird das Ganze in der Formel durch die Matrix/Spaltenvektor "ZEILE($1:$32)". Dieser Formelteil liefert für Zelle O9 eine 1, für Zelle O10 eine 2, für Zelle O11 eine 3, usw. bis Zelle O40 eine 32.
Da sich hinter "AGGREGAT(15;Option;Bereich;Nr)" nichts anderes verbirgt als "KKLEINSTE(Bereich;Nr)", "Nr" aber wegen "ZEILE($1:$32)" alle Zahlen von 1 bis 32 durchläuft, der "Bereich" aus denjenigen Zellen aus Spalte A besteht, die ein "x" in Spalte B haben, werden somit alle Zeilennummern aus Spalte A mit einem "x" in Spalte B im Hilfsspaltenbereich O9:O40 aufsteigend sortiert angezeigt.

2) Die Spalten Anrede/Name/Vorname (Bereich P9:R40):
Bei diesen 3x32 Zellen handelt es um 32 verschiedene Mehr-Zellen-ARRAY-Funktionen, also um 1 Mehr-Zellen-ARRAY-Funktion je Zeile.
Ich habe zurerst den Zellbereich P9:R9 markiert, dann in die Formeleditierzeile folgende Funktion eingegeben
=WENNFEHLER(INDEX($C$2:$E$40;O9;0);"")
und die Formeleingabe mit der Tastenkombination STRG+UMSCHALT+EINGABE abgeschlossen, sodass die Formel in geschweiften Klammern eingeschlossen erschien.
Den Zellbereich P9:R9 habe ich durch Hinunterziehen in sämtliche Zeilen des Bereiches bis hinunter nach P40:R40 kopiert, sodass dadurch 32 Zellbereiche mit 32 verschiedenen Formeln entstanden sind.
Jede dieser Formeln greift einerseits auf die Matrix $C$2:$E$40 zu und andererseits auf die Nummer aus der Hilfsspalte O, als auf Zelle O9, O10, O11, …, O40

3) Die Spalte "Bereich" (Bereich S9:S40):
Bei diesen 32 Zellen handelt es um 32 verschiedene "normale" Funktionen, also um 1 Funktion je Zeile.
Ich habe zurerst die Zelle S9 markiert, dh. die aktive Zelle dort hingestellt, dann in die Formeleditierzeile folgende Funktion eingegeben
=WENNFEHLER(VERWEIS("x";INDEX($F$2:$I$40;O9;0);$F$1:$I$1);"")
und die Formeleingabe mit EINGABE abgeschlossen.
Die Zelle S9 habe ich durch Hinunterziehen in sämtliche Zeilen des Bereiches bis hinunter nach S40 kopiert, sodass dadurch 32 Zellen mit 32 verschiedenen Formeln entstanden sind.
Jede dieser Formeln greift einerseits auf die Matrix $F$2:$I$40 zu und andererseits auf die Nummer aus der Hilfsspalte O, als auf Zelle O9, O10, O11, …, O40
Die VERWEIS-Funktion liefert schließlich die Spaltennummer aus der Matrix $F$2:$I$40, sodass die entsprechende Zelle aus dem Überschriftenbereich $F$1:$I$1 zurückgeliefert werden kann.

 

Hallo,

statt der Mehr-Zellen-ARRAY-Funktion für die Hilfsspalte aus 1) im Bereich O9:O40
{=WENNFEHLER(AGGREGAT(15;6;($A$2:$A$40)/($B$2:$B$40="x");ZEILE($1:$32));"")}
kannst du auch 32 normale Funktionen eingeben, indem du
1) die aktive Zelle in Zelle O9 stellst, dort folgende Funktion in die Formeleditierzeile eingibst:
=WENNFEHLER(AGGREGAT(15;6;($A$2:$A$40)/($B$2:$B$40="x");ZEILE(O1));"")
und sie mit der EINGABE-Taste abschließt.
2) die Zelle O9 kopierst, indem du sie in die darunter liegenden Zellen bis Zelle O40 hinunter ziehst
(in Zelle O40 müsste dann die Formel =WENNFEHLER(AGGREGAT(15;6;($A$2:$A$40)/($B$2:$B$40="x");ZEILE(O32));"") enthalten sein.)