Hallo Jana,
jetzt wird's kritisch, und schwierig.
Kritisch = na hoffentlich verstehst Du meine erklärungen
Schwierig = Wie's der Name schon sagt, schwierig zu erklären.
oioioioi! Worauf habe ich mich nur da eingelassen?
Eins Vorweg.
Ich habe sehr viel mit HALB-DYNAMISCHEN Adressbereichen gearbeitet, die allerdings in den angewendeten Formeln nicht so einfach zu erklären sind. Aber Prinzipiell gilt: "Mit HALB-DYNAMISCH meine ich, das beim runterkopieren sich nur der erste Teil des Adressbereiches sich ändert, und der zweite nicht."
Es ist durchaus möglich, das meine angewendeten Formeln durch bessere ersetzt werden können. Und wer will kann das ja dann mal zeigen. Ferner können sie auch noch durch "Namen" zuordnen etwas übersichtlicher gemacht werden, was jetzt aber zum besseren verständnis nicht förderlich ist.
Erst mal meine vorgehensweise erklären.
Ich bin davon ausgegangen, das die Spalte C "KategorieQualifikation" unsortiert vorliegt, ist ja auch die Hauptproblematik. Also wollte ich sie nach den drei Kategorien sortiert in ZWEI verschiedene Senkrechte Gruppen sortiert ausgeben. Mit anderen Worten; anstatt alle in einer Spalte untereinander, in ZWEI Spalten nebeneinander. Das größte Problem dabei war natürlich die Sortierung, welche ich mit einem Zwischenschritt und Hilfsspalten hinbekommen habe.
Da die ausgeschriebenen Qualifikationen (z.B. Examinierte Pflegekräfte) im Dienstplan nicht benötigt werden, sondern lediglich die Qualifikations-Abkürzungen, habe ich sie wohl als Suchparameter verwendet, aber in der Sortierten Liste nicht mehr aufgeführt. Stattdessen habe ich die Namen, Vornamen und die Quali-Kürzel dort aufgeführt, da diese ja auch im Dienstplan erscheinen sollen.
Nachdem ich jetzt Zwei Gruppen hatte, mussten jetzt nur noch die Sortierten Einträge in den Dienstplan übernommen werden. Zu diesem Zweck greifte ich jetzt auf die Sortierte-Liste zurück und entnahm von Oben nach Unten einen Eintrag nach dem anderen.
Ich hätte das ganze noch ein wenig vereinfachen können, aber ich dachte mir das dies zum besseren verständnis beiträgt.
Das hört sich ja alles recht einfach an, aber dies mit Formeln zu bewerkstelligen ist doch schon etwas Umfangreicher. Dennoch will ich mal ein paar Formeln beschreiben.
Ich habe da mit einer recht speziellen SVERWEIS gearbeitet. Die z.B. so aussieht.
Code: Alles auswählen
=SVERWEIS(P$1;INDIREKT("C"&VERGLEICH(P$1;C1:C$100;0)&":G100");2;0)
Mit INDIREKT suche ich mir die benötigte Such-Matrix für den SVERWEIS, die ja so aussehen kann: C5:G100.
VERGLEICH = Die Funktion gibt die Position des gefundenen Wertes in der Suchmatrix als Zahl aus.
Mit anderen Worten, wenn VERGLEICH den Inhalt von P1 gefunden hat, bekomme ich eine Zahl. Dies könnte die 5 sein. Auch wenn es jetzt noch nicht so richtig zu erkennen ist, so ist die Such-Matrix HALB-DYNAMISCH, mit Ende fixiert. Denn der Adressbereich C1:C$100 passt sich nur beim ersten Wert (beim runterkopieren) mit an, der zweite Wert bleibt konstant.
Da ja "C" schon vorgegeben ist, und ich jetzt die 5 zurückbekommen habe, erhalte ich jetzt C5. Jetzt fehlt noch der Endbereich von meiner Such-Matrix, und der steht in Anführungszeichen ja auch schon da, nämlich :G100. Und jetzt baut INDIREKT aus dem erstem ergebnis C5, und der vorgabe :G100 eine komplette Adresse C5:G100 zusammen. Folglich würde jetzt rein Theoretisch die Formel wie folgt aussehen.
Zwecks Fehler abfangen musste ich diese Formel verdoppeln. Und dabei kam dann diese raus.
balu hat geschrieben:
=WENN(ISTNV(SVERWEIS(P$1;INDIREKT("C"&VERGLEICH(P$1;C2:C$100;0)+
ZÄHLENWENN(C$1:C2;"<>")&":G100");2;0));"";SVERWEIS(P$1;INDIREKT("C"
&VERGLEICH(P$1;C2:C$100;0)+ZÄHLENWENN(C$1:C2;"<>")&":G100");2;0))
Im Prinzip ist sie nichts anderes wie die erste, bis auf zwei Ergänzungen. Vor der ersten habe ich noch ISTNV eingesetzt, um den Fehler #NV abzufangen, der zwangsläufig auftaucht wenn SVERWEIS das Suchkriterium nicht findet.
Da das Suchkriterium immer an der gleichen Stelle steht, aber die Such-Matrix HALB-DYNAMISCH wachsen muss, musste noch eine ZÄHLENWENN mit eingebaut werden. Dadurch wird dann schlußendlich mit dem + die Zeilennummer ermittelt wo die suche beginnt.
Ich weiss, ist nicht grad elegant, aber sie funktioniert. Und auf Grund der Art und Weise wie die Such-Matrix auzfgebaut ist, ist es auch nicht so recht einfach den Fehler abzufangen.
Und wie schon Eingangs erklärt, ist das ganze auch nicht so einfach zu erklären.
Für Verbesserungsvorschläge bin ich immer zu haben.
Auf diese Weise habe ich jetzt alle (z.B. Examinierte Pflegekräfte) schön rausgepickt. Nur gibt es da jetzt noch ein kleines Problem. Es entstehen dadurch Dubletten. Und diese müssen ausgesiebt werden.
Das ist aber auch kein großes Problem.
Damit wird der Kleinste Wert aus der Spalte L gesucht, und ausgegeben.
Code: Alles auswählen
=WENN(MAX($L$2:$L$100)=MAX(P$4:P4);"";KGRÖSSTE($L$2:$L$100;ZÄHLENWENN($L$2:$L$100;">"&P4)))
Und jetzt habe ich keine Kraft, mehr um diese zu erklären. Es sei mir verziehen. Okay!?
Nö nö! Bin noch nicht fertig. Leider!
Warum sind im Dienstplan so viele Zeilen ausgeblendet, und das, obwohl da nichts drin steht? Ohne ausgeblendete liese es sich etwas Komfortabler den Bereich für die Namensauflistung mit Formeln auffüllen. Trotzdem habe ich da mal ein paar Eintragungen gemacht. Und zwar 7 an der Zahl für beide Bereiche.
Hätte ich jetzt doch beinahe vergessen.
Im Blatt "Mitarbeiter" bin ich ohne die Spalte "CODE Qualif." ausgekommen. Und die Spalte A "Nr" habe ich nach D rüberkopiert. Spalten A und B habe ich ausgeblendet.
Die Spalten L M N sind Hilfsspalten, wo die oben beschriebenen SVERWEIS-Formeln drin stehen.
Und nun mag ich nicht mehr (für heute).
Gruß
balu
[edit um 23:08 Uhr]
Ich habe da mal jetzt zwei Dateien angehängt.
1 x Dienstplan_von_balu.ods
1 x Dienstplan_von_balu_v3.ods
Die v3 ist, gegenüber der ersten, eine "Optimierte" und übersichtlichere Version. In ihr habe ich jetzt auch ALLE Mitarbeiter aufgeführt, die mir zur verfügung standen.
