Antworten zum Thema „Excel 2010 Mtrans oder Zählenwenn“

[Basquiaty]

Hallo liebe Leute,

ich hab hier einen Dienstplan der mir meine Stunden zusammenrechnet. Z.B. Spalte D10:40 wenn ich dort "DF" eintrage oder andere Kürzel dann rechnet mir Excel spaltenweise alles aus. Das geschieht über eine Matrixformel =SUMME((D10:40=MTRANS($D104:$D304))*MTRANS($E104:$E304))
In D104:304 stehen die Kürzel. In E104:E304 stehen die dazugehörigen Werte. Z.B mit wie viel Stunden Excel rechnen soll wenn bei einem DF oder F oder S, usw.

Jetzt möchte ich Tageweise mir die Werte auslesen lassen oder zusammenzählen. Also pro Zeile. Z.B D10:W10
Die Formel so umzustellen funktioniert leider nicht: =SUMME((D10:W10=MTRANS($D104:$D304))*MTRANS($E104:$E304))
Ich möchte damit erreichen dass er mir alle Frühdienste an dem jeweiligen Tag zusammenzählt oder alle Spätdienste

Die Matrix ist dann auch wieder in Spalten wobei für jedes Kürzel der Wert 1 steht.

Vorher hatte ich das alles über Zählenwenn gelöst. Z.B. =ZÄHLENWENN(D10:W10;"DF")*1
Und das hätte ich gerne anders gelöst, eben mit der Summe Mtrans oder etwas ähnliches.

Ich hoffe mir kann jemand helfen.

im Voraus vielen Dank.

 

[Sakas]

Hallo @Basquiaty ,
wäre deine Anfrage hier nicht besser aufgehoben?

LG Sakas

 

[owieortho]

Ist es sowas, was Du erreichen willst?

Dafür steht in Zelle F4 folgende Funktion:
=SUMMEWENNS($C$2:$C$18;$A$2:$A$18;$E4;$B$2:$B$18;F$3)
die dann per Autoausfüllen auf den Bereich F4:H6 gezogen wird. Achtung, die Funktion ist SUMMEWENNS !

Gruß O.

 

[wex_stallion]

Ich hab das Ding mal korrekt eingeparkt.

 

[Basquiaty]

Ist es sowas, was Du erreichen willst?

Dafür steht in Zelle F4 folgende Funktion:
=SUMMEWENNS($C$2:$C$18;$A$2:$A$18;$E4;$B$2:$B$18;F$3)
die dann per Autoausfüllen auf den Bereich F4:H6 gezogen wird. Achtung, die Funktion ist SUMMEWENNS !

Gruß O.

Vielen Dank für Deine Mühe.

Aber ich weiß nicht genau. Bei Deinem Beispiel durchsucht Excel wieder Spalten. Ich möchte aber das er mir eine Zeile durchsucht und nur die Anzahl der Frühdienste oder Spätdienste ausgibt.

So wie hier.


Das hatte ich bisher mit der Zählenwenn Formel gelöst, doch jetzt will ich das über eine Matrix machen.
Des Weiteren soll er mir auch sagen, ob ich Hauptamtliche Mitarbeiter an dem jeweiligen Tag verplant hab, damit eine Abdeckung gewährleistet ist. Also quasi nur von D10:H10 und O10:S10. Was in meinem Beispiel grau hinterlegt ist.

 

[owieortho]

Du trägst also im Bereich D10:AF40 Zeichenkobinationen ein. Und in AH10:AT40 wird die Anzahl jedes verwendeten Zeichens ausgegeben. Bsp:
.............................................................S....F....X....d
S....F....X....Sd....d.................................2....1....1....2

Dann müsste Dir das weiterhelfen

Gruß
O.

 

[Basquiaty]

Du trägst also im Bereich D10:AF40 Zeichenkobinationen ein. Und in AH10:AT40 wird die Anzahl jedes verwendeten Zeichens ausgegeben. Bsp:
.............................................................S....F....X....d
S....F....X....Sd....d.................................2....1....1....2

Dann müsste Dir das weiterhelfen

Gruß
O.

Danke für Deine Mühe, aber hier gibt es wieder nur Formeln für einzelne Zellen oder mehrere Zellen aber wieder nur senkrecht als Spalte. Sobald ich aus A1:A99 eine Zeile daraus mache, also D10:Z10. Gibt Excel nur NV aus.
Die Idee ist schon super. Zähle mir "S" egal ob da S2 oder ST steht. Ergebnis ist 2. Aber wie kann ich das waagerecht zählen.
Mit der Zählenwenn Funktion ging das super! Aber da muss man wieder alle möglich Kürzel angeben, die eventuell mal vorkommen werden. Also Beispiel: S2 ist hinterlegt aber S3 nicht, dann zählt er mir natürlich nur S2 und S3 wird ignoriert. Wenn die Formel von Deinem Link funktionieren würde, dann würde er mir 2 ausgeben, weil er natürlich nur alle "S" zählt. Aber irgendwie will er das nicht als Zeile machen, sondern nur als Spalte.

 

[owieortho]

Sieh Dir die Abwandlung des Links mal an.

Auf die Datei aus Deinem Beispiel bezogen, stünde in Zelle AG10 eine Verkettung der Inhalte aus D10 bis AF10 in einer Zeichenkette, die ich dann in AJ10 für eines darin vorkommenden Zeichen auswerte.
Die Zelle AG10 könntest Du unsichtbar machen, so dass sie nicht weiter stört. Die Kürzel für die Auswertung könntest Du über den Bezeichnungen im Bereich AH8 bis A?8 eintragen und auch verstecken.
Die Funktion in AJ10 ist in meinem Screenshot so mit gemischten Bezügen versehen, das mit einem vertikalen und einem horizontalen Autoausfüllen, Deine gesamte Auswertungstabelle gefüllt werden kann.
Jedes neue verwendete Kürzel müsste dann nur in der rechten Tabelle aufgeführt und die Formeln übertragen werden.
Gruß O.

 

[Basquiaty]

Sieh Dir die Abwandlung des Links mal an.

Auf die Datei aus Deinem Beispiel bezogen, stünde in Zelle AG10 eine Verkettung der Inhalte aus D10 bis AF10 in einer Zeichenkette, die ich dann in AJ10 für eines darin vorkommenden Zeichen auswerte.
Die Zelle AG10 könntest Du unsichtbar machen, so dass sie nicht weiter stört. Die Kürzel für die Auswertung könntest Du über den Bezeichnungen im Bereich AH8 bis A?8 eintragen und auch verstecken.
Die Funktion in AJ10 ist in meinem Screenshot so mit gemischten Bezügen versehen, das mit einem vertikalen und einem horizontalen Autoausfüllen, Deine gesamte Auswertungstabelle gefüllt werden kann.
Jedes neue verwendete Kürzel müsste dann nur in der rechten Tabelle aufgeführt und die Formeln übertragen werden.
Gruß O.

Wow, Du machst Dir voll die Mühe! Danke.
Aber gibt es nicht die Möglichkeit, dass Excel mir die verschiedenen Spätdienste zusammenzählt. Ein Spätdienst ist S1, S2, ST, (d)S2, usw. Mit Deinem Beispiel würde er alles extra zählen oder? Im Prinzip brauche ich eine Spalte für Nachtbereitschaften. Hierzu darf der gesamte Bereich, Z.B von D10:Z10 gezählt werden. Nachtbereitschaften können sein: SN1, SN2, SN3, DN,
zweite Spalte für Hauptamtliche im Frühdienst. Bereich D10:H10 und O10:S10. Frühdienste können sein. F1, F, F(d), Fd, FS, usw.
dann eine Spalte für die Hauptamtlichen im Spätdienst, also wieder D10:H10 und O10:S10. mögliche Kürzel S1, S2, S3, ST, (d)S2, usw.
Dann eine Spalte für Frühdienste über den gesamten Bereich D10:Z10, ebenso für Spätdienste und Wochenenddienste (Früh) und Wochenendienste (Spät)
Nochmals, Danke dass Du Dir den Kopf für mich zermalmst. Voll toll!!

 

[Basquiaty]

Hat keiner mehr eine Idee?

 

[UwFr2016]

Also ich mache das mit dieser Formel:
=SUMMEWENN($J$5:$J$101;"Fachliches";$M$5:$M$101)

In einer Spalte von A bis irgendwo gibt es die Kürzel "Fachliches" und in der Spalte m bis irgendwo den Wert.
und die Formel halt da wo ich sie haben will, dort kommt dann die gesamtsumme aller vorhandenen Einträge..

 

[essdreipro]

@UwFr2016 ...

interessanter Ansatz... bleibt nur die Frage nach
der dritten Option, die der TE wohl auch braucht...

der Zeit...

 

[owieortho]

Moglicherweise führt auch VERGLEICH(....) zum gewünschten Ergebnis. Ich habe leider momentan nicht genug Zeit mich in das Problem einzudenken, bzw. all das, was ich übersehen oder woran ich nicht gedacht habe mit zu berücksichtigen.
Bisweilen ist es schon in Excel so wie beim Datenbankentwurf: "falsch" aufgesetzt, macht es einem danach das Leben sehr schwer. Klar, ich will dem Benutzer möglichst unkompliziert und fehlerfrei das Eintragen von Daten ermöglichen, aber genauso leicht will der Verarbeiter das haben. Es wäre also ggfs. zu überlegen, welche Funktionen mir zur Verfügung stehen und davon ausgehend das Frontend zu entwickeln.
O.

 

[Basquiaty]

Viele Dank euch!

=SUMME((E10:E40=MTRANS($E104:$E304))*MTRANS($F104:$F304))

=SUMME((E10:E40=($E104:$E304))*($F104:$F304)) ist die Lösung. Weil MTrans die Zeile senkrecht transformiert. Also ganz einfach weglassen.

Allerdings wäre es schön Bereiche auszusparen. Beispiel:

=SUMME((E10:E20;E30:E40=($E104:$E304))*($F104:$F304)) Funktioniert leider nicht. E21:E29 soll nicht berücksichtigt werden. Da bin ich noch am suchen. Vielleicht jemand eine Idee.

 

[essdreipro]

versuche doch für den Bereich den Du erfassen
möchtest einen Namen zu definieren... damit
hast Du in der Formel mehr Flexibilität...