Daten Variabel aus Tabelle auslesen und verabeiten per SVERWEIS/SUMMENPRODUKT

[ExcelFoley69]

Hallo liebe Community,
ich habe ein Problem :20: und zwar muss ich aus einer SAP Datenliste über die Personalnummer die Urlaubs und Krankheitstage von Personen auslesen und diese über die verschiedenen Monate zählen.
Das Problem ist jetzt, dass meine Tabelle nicht dynamisch ist, alle Werte beziehen sich auf feste Spalten, wenn ich jetzt eine Personalnummer ändere, ändert sich nichts an den Urlaubstagen usw.
Meine Idee wäre jetzt gewesen per SVERWEIS nach der Personalnummer zu suchen und danach die Zeilen nach den Buchstaben für Urlaub ("U") und Krankheit("K") zu durchsuchen.
Ich möchte also in ein Feld die Personalnummer eingeben und das wird mir dann automatisch ausgegeben.
Per SVERWEIS habe ich jetzt nach der Nummer in der Tabelle gesucht:
=SVERWEIS(Tabelle1!A4;Arbeitsdaten!$1:$1048576;1;FALSCH)
Mit SUMMENPRODUKT zähle ich dann die Urlaubstage :
=SUMMENPRODUKT((IDENTISCH(Arbeitsdaten!A2:NC2;Vorgaben!$A$19)*1))
Jetzt weiß ich nur nicht wie ich das verknüpfe, dass das Summenprodukt automatisch auf die Zeile in der die Personalnummer steht angewendet wird.
Ich habe schon versucht mit die Zeilennummer und Spaltennummer von der Personalnummer auszugeben und das dann irgendwie zu verbinden, aber das hat nicht funktioniert. Ich habs nur geschafft die Zeilennummer auszugeben:
=VERGLEICH(SVERWEIS(Tabelle1!A7;Arbeitsdaten!$1:$1048576;1;FALSCH);Arbeitsdaten!A:A;0)
Jedoch kann ich das nicht mit dem Buchstaben der Spalte verbinden, um so dann das oben genannte SUMMENPRODUKT individuell anzuwenden.
Ich hoffe ihr versteht mein Problem einigermaßen und könnt mir helfen,
Liebe Grüße
Excel

Ich würde auch gerne noch die Monate festlegen, hier oben ist jetzt der Bereich A2:NC2 das komplette Jahr

[WillWissen]

Hi,

zeig mal bitte eine Beispieltabelle - sensible Daten anonymisiert, 10-15 Datensätze, Aufbau muss deinem Original entsprechen. https://www.clever-excel-forum.de/Thread...ng-stellen

[ExcelFoley69]

Hier ist der Beispieldatensatz,
die Daten bekomme ich genau so wie auf dem Blatt SAP Daten, es sind natürlich viel mehr und übers ganze Jahr und ich hätte dann geren auf dem Blatt Berechnung die Daten ausgewertet nach Personalnummer und Urlaub, Krankheit usw ( wie es auf dem Blatt Vorgaben steht) und pro Monat eben aufsummiert.
Hoffe das ist einigermaßen verständlich
Lg
Excel

[steve1da]

Hola,

Code:

=SUMMENPRODUKT(('SAP DATEN'!$A$2:$A$11=A4)*('SAP DATEN'!$B$2:$AA$11="u"))

Keine Ahnung wie du "halber Urlaub" definierst. Damit die Formel funktioniert muss das #NV raus, am besten mit Wennfehler.
Die Auswertung auf Monatsbasis wird aktuell nicht klappen da deine "Datumswerte" reiner TEXT sind (warum auch immer).

Gruß,
steve1da

[ExcelFoley69]

Danke für die Anwort,
wie meinst du #NV mit WENNFEHLER raus?
Und zu den Monaten, ist es nicht möglich den Bereich Spalte B bis Spalte AF z.B. als Januar zu definieren?
LG
Excel  :19:

[steve1da]

Hola,

Code:

=WENNFEHLER(SVERWEIS(A4;'C:\Users\mk82989\Desktop\Excel Rpogrammierwn\[SVERWEISAuswertung1.XLSX]Arbeitsdaten'!1:1048576;1;FALSCH);"")

Natürlich ist das möglich. Dann machst du das aber für den Februar alle 4 Jahre anders. Dann vergisst du das mal und schon hast du falsche Daten.
Warum nicht gleich mit richtigen Datumswerten arbeiten?

Gruß,
steve1da

[ExcelFoley69]

Ach so, das habe ich vergessen zu erwähnen,
die Monate sind fest, also es handelt sich um 2020, weil ich die Urlaubs und Krankheitstage wegen Corona auswerten soll.
LG Ich muss also nichts an den Tagen ändern, sprich Januar ist immer Spalte B-AF.
Excel

[steve1da]

Hola,

gut, wenn du 12 verschiedene Bereiche anlegen willst, gerne...

Code:

=SUMMENPRODUKT(('SAP DATEN'!$A$2:$A$11=$A4)*(Januar="u"))

Januar ist hier der Bereich B1:AF11

Gruß,
steve1da

[ExcelFoley69]

Ich hab das jetzt so gelöst und habe mir eine Hilfstabelle erstellt, die die Einträge automatisch nach der Personalnummer ordnet und da dann fest das Summenprodukt berechnet. Das funktioniert so weit. (Siehe Anhang)
Jetzt habe ich oben in der Hilfstabelle noch das Datum auch als Datum formatiert eingefügt. Wie funktioniert es denn da jetzt den Bereich festzulegen? Denn jetzt würde es ja passen so wie du es oben gemeint hast.
LG
Excel

[steve1da]

Hola,

wenn du Datumswerte hast, brauchst du keinen Bereich festlegen. Da fragt man die Datumswerte direkt ab.

Code:

=SUMMENPRODUKT((Hilfstabelle!$A$3:$A$13=$A3)*(TEXT(Hilfstabelle!$B$1:$K$1;"MMMM")=$D$1)*(Hilfstabelle!$B$3:$K$13="u"))

Gruß,
steve1da