Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1572to1576
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Ausgabe von Werten aus Matrix - vertikale Suche

Ausgabe von Werten aus Matrix - vertikale Suche
10.08.2017 17:59:20
Werten
Guten Tag
Ich habe eine grosse Datentabelle (über 500 Zeilen und 200 Spalten) aus der ich gerne für eine bestimmte Person (Auswahl über drop-down-Liste) die Werte auf einer anderen Kartei herausfiltern möchte (Auftrag: "vergleiche den Namen und suche den ersten etc. Eintrag in Spalte x, gebe dann aus was in Spalte y in der gleichen Zeile steht").
Ich habe bereits ein solches Übersichtsblatt erstellt und die Formeln funktionieren. Allerdings, entstehen heute viele Leerzeilen durch diese Formatierung, da nacheinander spaltenweise gesucht wird.
1) Ich möchte nun, dass nicht spaltenweise, sondern in der ganzen Matrix suchen und nach und nach den ersten, zweiten, dritten Eintrag erscheinen lassen. Grundsätzlich funktioniert auch das, allerdings werden zuerst horizontal stehende Einträge ausgegeben und ich möchte zuerst vertikal stehende Einträge.
2) Zusätzlich möchte ich in einer anderen Zelle den Inhalt einer Zelle ausgeben, die oberhalb in der gleichen Spalte des ausgegebenen Eintrags steht. Das klingt vielleicht etwas abstrakt, wird aber sicherlich in der angehängten Tabelle verdeutlicht.
Hier eine vereinfachte Beispieldatei:
https://www.herber.de/bbs/user/115381.xlsx
Ich wäre sehr froh um Eure Unterstützung!!!
Herzlich
Irmela

16
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: mit Matrixfunktionalitätsformeln ...
10.08.2017 19:45:29
...
Hallo Irmela,
... nachfolgende drei Matrixfunktion(alität)sformeln bedürfen keines(!) spez. Formelabschlusses wie eine klassische Matrixformel und lösen Dein Problem trotzdem wie angestrebt.
Nachfolgend Formeln für Deine Beispieldatei sind für 550 Datensätze aufgestellt. Bei entsprechender Anpassung der Formeln bzgl. der rechten Spalte (hier: G) sollten diese auch für die von Dir gewünschte Spaltenzahl funktionieren.
Die drei Formeln (in I3 mit der ausgewiesenen bedingten Formatierungsformel und benutzerdefiniertes Zahlenformat: ;;; also nur drei Semikolons) einfach weit genug nach unten kopieren. J10 und K10 ergeben sich aus einer zusätzlichen Eintragung in F549:G549.
 ABCDEFGHIJKL
2Klasse HF1 HF2 HF3 Lehrerliste Schmid 
3 StundenLehrerStundenLehrerStundenLehrer KlasseFachStunden 
4Mathematik6Schmid4Klaus2Hoffmann HF1Mathematik6 
5Englisch2Mahler4Schuler4Klaus  Physik3 
6Französisch2Schuler0Schuler4Mahler  Biologie3 
7Deutsch4Schuler4Mahler6Mahler HF2Geschichte1 
8Kunst2Müller3Hoffmann4Hoffmann  Politik1 
9Sport3Hoffmann2Müller2Schmid HF3Sport2 
10Geschichte1Klaus1Schmid0Müller  Excel ;-)9 
11Politik1Klaus1Schmid0Müller     
12Physik3Schmid2Klaus2Schuler     
13Biologie3Schmid6Klaus3Schuler     
14            

Formeln der Tabelle
ZelleFormel
I4=WENNFEHLER(INDEX($2:$2;AGGREGAT(15;6;(C$4:G$555=K$2)*(ZEILE(A$4:A$555)%%+SPALTE(C1:G1))/(C$4:G$555=K$2); ZEILE(A1))); "")
J4=WENNFEHLER(INDEX(A:A;REST(AGGREGAT(15;6;(C$4:G$555=K$2)*(ZEILE(A$4:A$555)%%+SPALTE(C1:G1))/(C$4:G$555=K$2); ZEILE(A1)); 1)/1%%); "")
K4=WENNFEHLER(INDEX(A:G;REST(AGGREGAT(15;6;(C$4:G$555=K$2)*(ZEILE(A$4:A$555)%%+SPALTE(C1:G1))/(C$4:G$555=K$2); ZEILE(A1)); 1)/1%%;AGGREGAT(15;6;(C$4:G$555=K$2)*(ZEILE(A$4:A$555)%%+SPALTE(C1:G1))/(C$4:G$555=K$2); ZEILE(A1))-1); "")

Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat
I41. / Formel ist =I4=I3Abc

Gruß Werner
.. , - ...
Anzeige
Bleibt folgendes anzumerken
10.08.2017 20:26:11
lupo1
neopa "zaubert" aus einer Ausgabeform eine andere.
Hätten ihm statt dessen Daten vorgelegen der Form:
Klasse Fach Lehrer Stunden
HF1 Mathe Schmid 6
...
..., könnte man beide Ausgaben viel leichter daraus bauen. Die erste nämlich ganz einfach per Pivot, und die zweite per Autofilter, Spezialfilter oder mittels einfacherer Formeln, als Du sie jetzt findest - und womit Du oder spätestens Dein Nachfolger in 2 Monaten im Zweifel nicht mehr weißt, umzugehen.
Deshalb muss man Deine Schule an dieser Stelle, liebe Irmela, ganz heftig kritisieren, dass sie Dich an einer Ausgabe statt mit Daten hantieren lässt!
Ausgaben sind zum Anschauen. Nicht zum Auswerten.
Anzeige
AW: wenn "Ausgaben nur zum Anschauen sind" ...
11.08.2017 20:00:10
...
Hallo lupo1,
... und "nicht zum Auswerten" dann wäre die meisten Frauen glücklich ;-)
Aber ernsthafter: Du verkennst offensichtlich Realitäten, die zwar oft so nicht sein müssten und meistens von Vielen auch nicht so gewollt sind, aber oft trotzdem einfach so sind. Man kann und sollte derartiges natürlich immer wieder auf den Prüfstand stellen und diese möglichst zeitnah einer Verbesserung zuführen (dies zumindest versuchen).
Aber unabhängig davon muss Anstehendes deswegen trotzdem zeitnah einem Ergebnis zugeführt werden. Dafür "sorgen" schon meistens die Chefs. Dazu habe ich Irmela einen Weg aufgezeigt. Ob Du als bekennender Oberlehrer mit Deiner Kritik an die Schule etwas bewirken konntest, werden wir wohl kaum jemals erfahren.
Gruß Werner
.. , - ...
Anzeige
Deine Formeln sprechen jedenfalls Bände ...
11.08.2017 22:02:16
lupo1
... und müssen nicht weiter kommentiert werden.
Müsste ich mit so etwas arbeiten, würde ich meinem Ober-Oberlehrer (=Direktor) ein Ultimatum stellen. Entweder ab sofort datengerecht oder ein Unter-Oberlehrer weniger!
Mit Pivot und Autofilter geht es komplett ohne, wenn die Daten datengerecht sind. Was datengerecht ist, werden wir beide ja wohl nicht diskutieren müssen. Oft lohnt es sich sogar, sie zuerst in die Datenform zurückzuformeln, um dann die zutreffenden Sätze zu filtern (wie es hier keinen Deut anders gefragt war).
AW: Wenn das Wörtchen "wenn" nicht wäre ... owT
12.08.2017 07:58:14
...
Gruß Werner
.. , - ...
Ein Stundenplan ist auch ein Plan, ...
14.08.2017 03:42:49
Luc:-?
Lupo (& Irmela),
und sicher auch in dieser Form nicht unüblich. Die Alternative wäre eine relationale DB mit 2-3 Ordnungsbegriffen, die mindestens 2 verschiedene BasisTabellen enthält, die dann wie jeweils gewünscht ausgewertet wdn könnten. Hierbei steht einerseits fest, welche Fächer in welchen Klassen unterrichtet wdn sollen und wie hoch die GesamtUStddZahl pro Fach und Klasse sein soll, und andererseits, welche Lehrer welche Fächer unterrichten können bzw sollen.
Ein Gesamtplan, der daraus erstellt würde, könnte dann so oder anders aussehen. Es steht aber zu befürchten, dass das alles manuell anhand der genannten Voraussetzungen geplant bzw ausgehandelt wird. Dann ist dieser Plan nicht nur Auswertung (im Sinne von Darstellung), sondern gleichzeitig auch die primäre DatenErfassung, so ungünstig er auch erscheinen mag. Das, was hier versucht wird, wäre dann quasi eine Auswertung, wobei es andersherum natürlich günstiger wäre, aber ungewohnt und weniger planungsanschaulich.
Im Prinzip liefe das ja auf eine PersonalEinsatzPlanung hinaus, wie sie zB jedes Bauunternehmen haben muss, bei wesentlich mehr Personal und Baustellen (hier die Klassen). Da könnte es leicht zu Doppel­(ver)planungen kommen! Hier ist das ja übersichtlicher, weshalb wohl auf Fehlerkontrollen verzichtet wird (bzw wdn könnte). Allerdings ist mir hier völlig unverständlich, Irmela,
warum hier Lehrer mit/für 0 UStdd geplant wdn…‽ Es ist doch idiotisch, dass jemand für einen NullBedarf eingeteilt wird!
Morrn, Luc :-?
Besser informiert mit …
Anzeige
AW: mit Matrixfunktionalitätsformeln ...
14.08.2017 15:22:32
Irmela
Hallo Werner
Herzlichen Dank für Deinen konstruktiven und sehr hilfreichen Beitrag. Die Formeln funktionieren prima in meiner Tabelle!!
Eine Frage hätte ich dann noch: ist es möglich auf einfache Weise die Stunden z.B. von Schmid, die in der Basistabelle (Matrix) der Stundenplanung stehen, sagen wir hier B10:G13, zu summieren? Könntest Du mir auch hierfür einen Vorschlag machen?
Nochmals Danke für die Unterstützung!
Irmela
Du hast meine Frage nicht beantwortet, ...
14.08.2017 18:45:37
Luc:-?
…Irmela;
warum wdn Lehrer für gewisse, in bestimmten Klassen nicht unterrichtete Fächer mit 0 Stunden festgelegt? Das ist doch eine widersinnige Zuordnung!
Ansonsten kann die Summe aller UStdd eines Lehrers in allen Klassen zusammen per singularer MatrixFml unabhängig so gebildet wdn: {=SUMME(WENN(C4:G13=K2;B4:F13;0))}
Sollen klassenweise Summen gebildet wdn, sollte dies auf Grundlage von Werners Lösung geschehen:
L4[:L9]:=WENN(ODER(I4="";I4=I3);"";SUMME(INDEX(K$4:K$9;VERGLEICH(I4;I$4:I$9;)):INDEX(I$4:I$9;VERGLEICH(I4;I$4:I$9;)-1+ZÄHLENWENN(I$4:I$9;I4))))
Feedback nicht unerwünscht! Gruß, Luc :-?
Besser informiert mit …
Anzeige
AW: sehe ich auch einfacher ...
14.08.2017 19:48:50
...
Hallo Luc,
... die Klassenstundenzahl in L4:L## lässt sich auf Basis meiner Lösung in I4:K### auch ganz einfach mit SUMMEWENN() ermitteln und zwar in L4 so: =WENN(I4=I3;"";SUMMEWENN(I:I;I4;K:K)) und Formel nach unten kopieren.
Gruß Werner
.. , - ...
Sicher, war nicht konsequent situationsbezogen!
15.08.2017 00:04:09
Luc:-?
Gruß, Luc :-?
AW: Sicher, war nicht konsequent situationsbezogen!
15.08.2017 08:46:36
Irmela
Vielen Dank nochmals für Eure geschätzten Beiträge!
Irmela
Einziger Grund läge bei Wochenstunden, ...
15.08.2017 00:06:58
Luc:-?
…die die GesamtSituation nicht real abbilden. Ist das so, Irmela?
Luc :-?
AW: denkbar wäre auch andere Gründe owT ...
15.08.2017 07:58:29
...
Gruß Werner
.. , - ...
AW: Du hast meine Frage nicht beantwortet, ...
15.08.2017 08:37:23
Irmela
Hallo Luc
Ich gebe zu, dass das Beispiel nicht logisch ist. In der Realität kommt dies nicht vor. Ich habe das ohne einen Grund in der Beispieltabelle so geschrieben. Ich verstehe, dass dies für Verwirrung sorgt. Sorry.
Vielen Dank für die funktionierende Summenformel! Damit ist mir sehr geholfen! Ich selbst hatte viel zu kompliziert gedacht... Und auch danke für den zusätzlichen Tipp der Klassensumme. Momentan benötige ich diese nicht, doch man weiss ja nie...
Grüsse
Irmela
Anzeige
Bitte sehr, viel Erfolg! owT
15.08.2017 15:50:17
Luc:-?
:-?
AW: bitte, gern ...
14.08.2017 19:44:02
...
Hallo Irmela,
... nun zu Deiner Zusatzfrage. Die Gesamtstundenzahl von dem Lehrer der in K2 gewählt wird, ermittelt sich ganz schlicht und einfach mit =SUMMEWENN(C:G;K2;B:F)
Gruß Werner
.. , - ...

311 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige