Frage von Harald2310, 68

Wie kann ich aus einer Exeldatei (Kalender=Matrix) waagerecht einen bestimmten Mitarbeiter suchen und senkrecht Zahlen addieren?

In der Matrix sind waagerecht MA angeordnet (Pers.Nr.). Senkrecht die Kalendertage 1. Jan - 31. Dez. Es werden z.B. Kranktage oder Fehltage mit Zahlen 1;2;3; gekennzeichnet. Diese möchte ich monatsweise ( z.B. Zeile 14:44 ) addieren. Mit der Formel WVERWEIS kann ich aber nur eine Zelle finden. Mit welcher Formel addiere ich mehrere Zeilen senkrecht? Bisher habe ich z.B. mit der Formel =SUMMEWENN(Matrix!$D$14:$D$44;4)/4+SUMMEWENN(Matrix!$D$14:$D$44;6)/6 gearbeitet. Ich möchte aber statt $D, dass der MA in der Matrix wie mit W oder SVERWEIS gefunden wird. Vielen Dank für die Bemühungen, ich hoffe, dass ich mich verständlich ausgedrückt habe. Harald2310

Antwort
von hannes1806, 11

Hi Harald!

Folgende Idee für dich:
1. Mach dir eine Hilfsspalte (bei mir Spalte B) mit =MONAT(A2) aus der du das Monat der 365 Tage berechnest.
2. Noch eine Hilfsspalte (bei mir in der selben Tabelle Spalte I), die die Spalte des Mitarbeiters ermittelt Formel aus I3:
=LINKS(ADRESSE(1;VERGLEICH(J3;$1:$1;0);4);1) .
3. Diese Formel zählt die Treffer je Mitarbeiter, Monat und Abwesenheitscode (habe statt 1,2 und 3 zum besseren Verständnia U,K und D gewählt) Formel aus K3:
=ZÄHLENWENNS($B:$B;K$2;INDIREKT($I3&":"&$I3;1);K$1)

lg Hannes

Expertenantwort
von Oubyi, Community-Experte für Excel, 4

Ich habe jetzt leider keine Zeit mehr, weiter zu tüfteln und ich denke, es müsste auch eine einfachere Lösung geben, aber um die Anzahl der 4 in der Spalte für den Mitarbeiter MA123 zu zählen (Zeile 14 bis 44) wenn die Bezeichnungen der Mitarbeiter im Bereich A1:Z1 stehen, müsste dies Formel klappen:

=ZÄHLENWENN(INDIREKT("Matrix!"&ADRESSE(14;VERGLEICH("MA123";Matrix!$A$1:$Z$1;0))&":"&ADRESSE(44;VERGLEICH("MA123";Matrix!$A$1:$Z$1;0)));4)

MA123 kannst Du natürlich durch einen Zellbezug ersetzen und den Bereich anpassen.
Hilft Dir das?
Ich schaue morgen wieder rein.

Expertenantwort
von Suboptimierer, Community-Experte für Excel, 22
=ZÄHLENWENN(INDIREKT("Z14S"&VERGLEICH(H1;A1:D1;0)&":Z44S"&VERGLEICH(H1;A1:D1;0);FALSCH);4)+ZÄHLENWENN(INDIREKT("Z14S"&VERGLEICH(H1;A1:D1;0)&":Z44S"&VERGLEICH(H1;A1:D1;0);FALSCH);6)

Ich würde mit Hilfszellen arbeiten und den Bereich vordefinieren oder zumindest die Spaltennummer der Person vorermitteln.

Kommentar von Harald2310 ,

Hab' vielen Dank - Das verstehe ich aber alles nicht. Was bezieht sich auf was? wo kommt die Formel hin, ich bin halt ein Laie!!!

Kommentar von Suboptimierer ,

In B1, B2, B3, ... habe ich die Namen hinein geschrieben: Mario, Luigi Toad,...
In H1 steht der zu suchende Name: Toad
In der Spalte A stehen die Tage fortlaufend numeriert.
Die Formel zählt alle 4en und 6en von Toad (Spalte D) von Tag 14 bis Tag 44.

Kommentar von Suboptimierer ,

In der Formel steht in H1 der zu suchende Name.

Mit BEREICH.VERSCHIEBEN könnte man die Formel alternativ gestalten:

=ZÄHLENWENN(BEREICH.VERSCHIEBEN(A14:A44;0;VERGLEICH(H1;A1:F1;0)-1);4)+ZÄHLENWENN(BEREICH.VERSCHIEBEN(A14:A44;0;VERGLEICH(H1;A1:F1;0)-1);6)
Antwort
von azmd108, 38

Das Ganze sieht also ca so aus? In Zeile 5 steht die Summe je Mitarbeiter.


A B C D
1 MA1 MA2 MA3 MA4
2 1
3
4 1
5=ZÄHLENWENN(A1:A4;1)

Es wird nun in Zeile 5 gezählt, wie oft für den MA der in Spalte A steht, die "1" eingetragen wurde - was z.B. "Krank" bedeutet. Ist es das was du möchtest?



Kommentar von Harald2310 ,

Vielen Dank für Deine schnelle Antwort, aber leider wäre das zu einfach. Ich habe eine 2. Datei, in der in Spalte A die MA stehen und in Spalte P die Formel rein soll. Gesucht soll mit Spalte A in der Matrix Zeile 1 der MA und anschließend in der gefundenen Spalte 1;2; oder 3 addiert werden (Monatsweise z.B. Zeile 14 bis 44 ) Es ist schwierig, sich auszudrücken.MfG

Kommentar von azmd108 ,

Hallo:

=SUMMENPRODUKT(($A$1:$H$1=D5)*($A$2:$A$16=1))

In A1:H1 stehen bei mir die Mitarbeiter in deiner Kalendermatrix


In D5 steht der Mitarbeiter aus deinem, ich nenne es jetzt mal "Auswertungssheet", für den du das Ergebnis haben möchtest.

In A2:A16 steht die 1, 2 oder 3 je Kalendertag in deiner Kalendermatrix

die 1 ist die Codierung, nach der gesucht werden soll.

Die Formel kannst du dann einfach runterziehen, somit solltest du für alle MA das Ergebnis bekommen.

An sich sollte es auch mit "Zählenwenns" funktionieren, seltsamerweise bekomme ich da immer eine Fehlermeldung, mit Summenprodukt geht es auch :)

Ich hoffe, das ist es nun was du benötigst.

Kommentar von Harald2310 ,

Das habe ich nicht verstanden, Du kommunizierst mit einem Laien!

Kommentar von Harald2310 ,

Summenprodukt woher? Mir fehlt Dein Bezug zur Matrix. Ich würde Dir gerne beide Dateien als Beispiel schicken, weiß aber nicht wie!

Kommentar von azmd108 ,

Ich stelle mir den Aufbau deines Vorhabens wie folgt vor:

Du hast eine Datei oder eine Tabelle, wo deine Kalendermatrix steht. In Zeile 1 steht dort z.B. die Mitarbeiternummer usw, so eben wie auf meinem Einganspost "aufgezeichnet"

Dann hast du ein Auswertungssheet
wo du die Mitarbeiter-Nummern meinetwegen untereinander stehen hast und nun wissen willst, wie lange Mitarbeiter-Nr XYZ letztes Jahr krank war. In deiner Kalendermatrix ist "Krank" mit einer 1 an dem jeweiligen Krankheitstag des Mitarbeiters markiert.

In meiner Summenproduktformel wird im Prinzip nach zwei verschiedenen Kriterien gesucht.

Das
erste Kriterium, auf der linken Seite des Gleichheitszeichens, steht
der Bereich, in dem das Kriterium gefunden werden soll. Das ist die
erste Zeile (z.B. A1 bis H1). Rechts vom Gleichheitszeichen steht das Suchkriterium, welches z.B. ,in der Zellle auf deinem Auswertungssheet, in D5 steht (die Mitarbeiternummer).

Selbes gilt für das zweite Kriterium

A2:A16, oder meinetwegen auch A2 bis Z10000, ist der Bereich, wo die "Zahlen" in der Kalendermatrix stehen. Rechts vom Gleichheitszeichen ist die Zahl, für die du das Ergebnis rückgegeben haben willst.

PS: Lad die Datei bei Bedarf einfach irgendwo hoch und schick mir den Link, z.B. hier http://www.file-upload.net/

Keine passende Antwort gefunden?

Fragen Sie die Community

Weitere Fragen mit Antworten