sverweis oder index oder was???

[hanne55]

Guten Abend Helmut,

tut mir leid, dass ich mich erst jetzt melde. Aber meine 4 Enkelkinder waren den ganzen Tag da und ich hatte nicht die geringste Chance, mich um unser "Abrechnungsproblem" zu kümmern.

Nein, stimmt nicht ganz. Von 6 - 9 Uhr habe ich rumprobiert, mit VERGLEICH, SVERWEIS, INDEX und zig anderen Möglichkeiten. Bin aber zu keinem funktionierendem Ergebnis gekommen. Zu deiner 2. Frage kann ich aus dem Stehgreif jetzt nicht mal eine klare Antwort geben. Kläre das morgen mit meinen Mädels und gebe Rückmeldung.
Opa Edgar hat sich noch nicht gemeldet. Schade!!!! Ich glaube, es ist nicht gut einen Thard aufzumachen mit dem Titel: "Opa Edgar - bitte melden" - Witzle gmacht.

Ich bin gerade dabei, anhand dieses youtube-videos - https://www.youtube.com/watch?v=rYPdsVQgFEM (Monatsübersicht aus Jahresdienstplan) für die Abrechnung zu verwenden. Das geht ganz sicher nicht 1:1, aber ein paar tolle Tipps sind dabei (für mich zumindest).

Das elendige Datum bereitet mir riesige Problem. Wie bringe ich Excel dazu in einem Monatszeitraum nur die Daten zu übertragen, die in diesem Monat erfasst wurden.

Dir und allen anderen Lesern einen schönen Abend und einen guten Start in die neue Woche.
Hanne

[Ego]

Hallo Hanne,

da aus einer grossen Menge unterschiedlich viele Elemente gesucht werden wird es auf eine geschickte Nutzung der Funktio Kkleinste bzw Aggregat(15;...) hinauslaufen.

Noch zwei Fragen.

1) Kann ein Mitarbeiter an einem Tag mehrere Einsätze haben?
2) Wenn ja worin können/müssen sie sich unterscheiden?

[hanne55]

Hallo Helmut,
die Funktion kkleinste kenne ich, aber von aggregat habe ich noch nie was gehört. Boah eh, bin ich ein Stümper!!!
Zu deinen Fragen:

1. Nein, das hatten wir noch nie!
   
2. Somit beantwortet.
   

Schönen Abend und eine gute Nacht
Hanne

[hanne55]

Guten Morgen Helmut,

habe meinen Kolleginnen dein bisheriges Ergebnis gezeigt und die sind, wie ich, begeistert.
Jetzt kann ich dir auch alle Fragen ganz korrekt beantworten.

1. Unterschiedliche Bezahlung:
   Ja, die gibts es. Wir setzen Fachkräfte (alle gleicher Stundensatz von 10,50 €) und Hilfskräfte (zu 9,50 oder 9,00 €) ein. Die Bezahlung im Trainereinsatz ist bei allen gleich (20,00 €).
   
2. Zwei Einsätze an einem Tag:
   Da muss ich mich korrigieren. Die sind selten, aber möglich. Beispiel: Eine Fachkraft fährt mit einer Gruppe zum Klettern. Dann ist die reine "Kletterzeit" Trainerzeit. Die Dauer der Fahrt dort hin aber Normalzeit. 
   

Was wir nicht mehr brauchen, ist in der Mitarbeitertabelle die Spalte B (Kürzel). Damit haben wir vor der Nummerierung gearbeitet.
Auch unterscheiden wir keine "Nachtstunden und Tagstunden" mehr. Die Spalte Nacht kann in allen Tabellenblättern raus.

Wenn ich zu Hause bin, werde ich AGGREGAT ausprobieren. Danke für den Tipp. Aber bitte, bitte bleib dran. Was du da für uns gemacht hast ist einfach genial.

Gruß Hanne

[hanne55]

Hallo Helmut,

mir raucht der Kopf. Habe stundenlang an AGGREGAT rumgeformelt, aber leider ohne das gewünschte richtige Ergebnis. Ein bisschen funktioniert sie schon, aber ich habe das Gefühl, dass jetzt nicht mehr der kleinste Wert zurückgegeben wird.
Selbst wenn ich sie wirklich richtig hinbekommen hätte, wäre das zwar erfreulich, aber auch sinnlos gewesen. Ich habe keine Idee, wie mit dieser Funktion nur die Zellen eines bestimmten Monats ausgelesen werden können.

Ich lade meine "Bastelarbeit" mal hoch. Vielleicht helfen meine Anfänge.

Gruß Hanne

[Ego]

Hallo Hanne,

hier noch einmal die Datei mit einigen Änderungen.

a) Makro statt Formeln:
Da es für mich einfacher war, habe ich das Füllen der Druckliste einmal mit einem Makro realisiert. Wenn das für euch in Ordnung ist, kann das so bleiben.
Die Aktualisierung wird angestossen, wenn man im Blatt Abrechnung die Mitarbeiternummer einträgt oder im Blatt Erfassung auf eine Mitarbeiternummer oder ein Datum doppelklickt.
Ein kleiner Nachteil ist, dass bei Spaltenänderungen in der Erfassung und in der Druckausgabe das Progamm angepasst werden muss.

b) Spalte Nacht:
Hab ich aus allen Tabellen entfernt.

c) Spalten Kürzel bzw Gruppe:
Ich habe in den Vertragsdaten die Spalte Kürzel durch eine Spalte Gruppe ersetzt und diese auch in den Parametern der Stundenkosten berücksichtigt.
Achtung! Da die Formeln (durch die Zeitabhängigkeit) auf die Listen der Parameter nicht mit eindeutiger Suche  zugreifen können, müssen diese Listen immer sortiert sein.

d) Anzahl der Druckzeilen erweitern:
Falls in der Druckausgabe mehr Zeilen benötigt werden kann man die entsprechende Anzahl der Druckzeilen kopieren und vor der letzten Druckzeile einfügen.
Durch das Kopieren werden die Formeln mitkopiert und der benannte Bereich für die Druckausgabe wird erweitert. Das Programm übernimmt maximal soviel Zeilen wie in der Druckausgabe vorhanden sind. Sind in der Erfassung mehr Zeilen vorhanden wird eine Meldung ausgegeben.
Man kann auch Druckzeilen (nicht allle) einfach löschen.

e) Drucken der Abrechnung eines Mitarbeiters in zwei Teilen.
Wenn man im laufe des Monats schon eine Abrechnung für einen Mitarbeiter gemacht hat, kann man für eine zweite Abrechnung das Datum anpassen, es werden dann nur noch die erfassten Zeilen ab diesem Datum eingetragen.

[hanne55]

Servus Helmut,

ich kann zwar nicht nachvollziehen, warum ein Makro (da habe ich noch weinger Ahnung, als von Formeln, das heißt "gar keine") einfacher sein soll als ein Formel, aber du wirst schon wissen, was du tust. Habe "leider" Besuch und kann die Tabelle momentan nicht auf Herz und Nieren prüfen. Aber Morgen habe ich Zeit und werde dies tun.

Derweil vielen, vielen, vielen Dank für deine Hilfe und deine Bemühungen. Ich bin mir, nach ein paar winzigen Test schon sicher, dass alles klappt.

Ich kann dir gar nicht sagen, wie dankbar ich dir bin. Leute wie du verschönern und verbessern einem das Leben. Ehrlich, du hast mir bzw. uns wahnsinnig viel Arbeit erspart.

Melde mich morgen nochmal. Dir einen richtig schönen Abend.

LG Hanne

[Ego]

Hallo Hanne,

in der Anlage einmal in Abrechnung (2) eine Druckversion über Formeln.
Ein kleiner Nachteil könnte sein, dass die Formeln nicht erkennen, wenn in dem Monat mehr Erfassungszeilen als Druckzeilen sind.
Man könnte aber auch eine Zelle einbauen in der dies abgeprüft wird.

a) Zur Realisation:
Ich habe in Spalte B die Zeilen geschrieben, in denen in dem Erfassungsblatt der Mitarbeiter für den Zeitraum ein Eintrag hat.
Mann könnte diese Formel natürlich auch in jede SpaltenFormel einbauen, Excel müste dann aber in jeder Druckzeile diese Erfassungszeile sieben mal berechnen und die Spalte B gehört ja nicht mehr zum Druckbereich.

b) Zur Entwicklung:
In den Spalten P und Q habe ich noch die Formeln stehen lassen, die ich bei der Entwicklung der endgültigen Formel getestet habe. Sie können einfach gelöscht werden.
1) Da ich aus einer Liste eine gesuchte Gruppe der Reihe nach angezeigt bekommen will, bietet sich wie schon geschrieben eine Funktion wie Kkleinste an.
2) Die Aggregat-Funktion mit den ersten Parametern 15 entspricht der Kkleinste-Funktion. Durch den zweiten Parameter 6 wird aber sichergestellt, dass Fehlerwerte in der Matrix nicht berücksichtigt werden.
3) Ich muss also eine Matrix erstellen, die für alle passenden Werte eine Zahl und für alle nicht passenden Werte einen Fehler erzeugt.
Dies wird standardmässig gemacht, indem man alle Vergleichsergebnisse in eine Zahl (0 oder 1) umwandelt, diese miteinander multipliziert und das Produkt als einen Divisor nutzt. Nur wenn alle Vergleiche wahr sind ist der Divisor 1 und es kommt ein gültiger wert heraus. Wenn ein Vergleich nicht wahr ist, ist der Divisor 0 und führt zum Fehler "DIV/0".
4)Als Index nutze ich die Zeilennummer.

[hanne55]

Hallo Helmut,
 
habe dir gestern schon geschrieben, wie begeistert ich von deiner Arbeit bin. Und das tue ich jetzt wieder. TAUSEND DANKE!
 
Ein paar Fragen bzw. Infos habe ich noch.
 
Zur „Makro-Abrechnung“
 

1. … kleiner Nachteil… bei Spaltenänderung …. Ich bin 61 Jahre jung und werde bis zum Beginn der Rente 100%zig an den Spalten nichts mehr ändern!!!!!
   
2. … müssen die Listen immer sortiert sein … Nach welchem Kriterium? Da du „intelligente Tabellenblätter“ angelegt hast, kann ich doch in der Auswertung immer wieder Auswertungen vornehmen. Und wenn ich dann alles wieder (Alles auswählen) zurückstellen, dürfte doch eigentlich nichts passieren – oder?
   
3. Druckzeilen erweitern werden wir wahrscheinlich nicht brauchen. Aber ich habe es ausprobiert und es funktioniert einwandfrei.
   
4. Drucken in 2 Teilen. …Datum anpassen …Also statt z.B. 01.01.16 den 25.01.16 in Zelle A10 – gell? Da wir die Rechnungen immer am Monatsende machen, dürfte sich das um eine echte Ausnahme handeln. Aber es funktioniert, ich habe es probiert. Toll, toll, toll.
   

 
Zur „Formel-Abrechnung“
 

1. … kleiner Nachteil … habe ich schon zur 3. bei Makro beantwortet. Ich sehe darin keinen Nachteil!
   
2. … Spalte B… Das ist doch super so. Und du hast Recht, sie sind eh nicht im Druckbereich. Also alles bestens.
   
3. Freut mich, dass du deine Formeln in P und Q drin gelassen hast, so kann ich nämlich viel besser nachvollziehen, was du gemacht hast.
   
4. In der Spalte A habe ich mal eine Liste zur Auswahl zwischen Normal und Trainer angelegt. Die nehme ich jetzt raus, nicht dass noch einer meiner Kollegen auf die Idee kommt, da rumzuklicken.
   
5. Und noch was ist mir klar. Wenn wir die Rechnungen für einen anderen Monat brauchen, müssen wir das Startdatum verändern.
   

 
Zu beiden Abrechnungen:
 

1. Ich habe die Seitenränder in beiden Abrechnungen verändert, damit sie auf unseren Briefkopf passen. Habe auch ein paar „leere Zeilen“ gelöscht, damit es passt. Aber keine Angst, alles funktioniert weiter.
   
2. In Zelle J36 hast du eine Formel mit einer geschweiften Klammer drin, die kein Ergebnis anzeigt. Können wir die einfach löschen? Oder einfach durch SUMME(J34:J35) ersetzen. Wenn ein MA nichts gemacht hat, bekommt er ja auch keine Rechnung.
   

 
Und jetzt noch zu Pivot:
 

1. In Zeile 5 stehen die Mitarbeiter. Muss ich händisch jeden weiteren Mitarbeiter neu eintragen oder geht das mit Eingabe der MA.Nr.? Beides ist für mich völlig unproblematisch, will nur wissen wie es geht.
   
2. In Zeile 6 sind 3 Spalten für jeden MA mit #gesamt (Stunden), #Aufwand (Entgelt), #Pauschale# (km Geld). Ab Spalte Q stehen die genannten Überschriften aber in Zeile 5. Wenn ich sie auf Zeile 6 runter ziehen will kommt der Hinweis: Teilergebnisse oder Blockergebnisse können nicht verschoben werden.
   
3. Können wir die Überschriften und Bezüge (wenn es keinen Aufwand macht) Gesamt (=Einsatz und km-Geld), Einsatz, km-Geld.
   
4. Die Stunden sind mir nicht so wichtig, aber der Gesamtbetrag. Ehrenamtliche Mitarbeiter haben einen Steuerfreibetrag von 2400 € pro Jahr.Deshalb brauche ich unter der Zeile Gesamtergebnis noch eine Zeile mit Rest FB. Da würde ich dann die Formel einbauen: 2400 - Gesamt. Anhand dessen planen wir immer die Einsätze unserer ehrenamtlichen Mitarbeiter.
   
5. Welche Taste muss ich drücken, dass sich die Pivot aktualisiert. Ich habe MA.Nr. 2 im März arbeiten lassen, aber der Monat erscheint nicht, weder bei Normal noch bei Trainer.
   

Hänge dir mal die ein bisschen aktualisierte Tabelle an, damit du siehst, was ich meine.

Gruß Hanne

[Ego]

Hallo Hanne,

ich versuch einmal alle Punkte zu beantworten, bei denen ich glaube, dass eine Bemerkung hilfreich ist. Falls etwas fehlt, melde dich ruhig.

„Makro-Abrechnung“ 2… müssen die Listen immer sortiert sein...

Dies gilt unabhängig davon ob ihr die Makro- oder die Formelauswertung nutzt für die Listen "Aufwendung" und "Pauschale" im Arbeitsblatt Parameter und ist auch unbedingt notwendig.
Ich habe dort die Stundenwerte so eingerichtet, dass sie sich abhängig vom Datum än dern können.
Da ich nicht für jeden Tag einen Preis geschrieben habe konnte ich nicht mit Sverweis (mit 4.Parameter=Falsch) arbeiten. Und die genutzte Funktion Verweis benötigt eine sortierte Liste und gibt dann immer den grösten Wert kleiner oder gleich dem gesuchten Wert aus.

„Formel-Abrechnung“ 5. ...müssen wir das Startdatum verändern.

Auch das gilt für beide Auswertungen. Hast du einmal ein Doppelklick auf eine Mitarbeiternummer oder ein Datum in der Erfassung durchgeführt?
Es wird nicht nur die Mitarbeiternummer dieser Zeile in die Auswertung übertragen, sondern auch der erste Tag des entsprechenden Monats eingetragen.

Zu beiden Abrechnungen: 2. In Zelle J36 hast du eine Formel ...

Ich habe nicht nur in Spalter J, sondern auch in den Spalten A, E und F Formeln eingetragen.
Ich habe damit getestet ob ein zusätzliche Art (neben Trainer und Normal) einfach berücksichtigt werden kann.
In den anderen drei Spalten habe ich, wenn die dritte Art nicht vorhanden ist ein "" eintragen können. In Spalte J nicht, da sie zur Summenbildung  genutzt wird.
Du kannst die Formeln in dieser Zeile löschen. Wenn ihr einmal eine dritte Art bekommt, einfach die Formeln aus der Vorzeile nach unten kopieren.

zu Pivot 1. , 3., und 5.

Grundsätzlich gilt:
a) Die Struktur der Tabelle wird nicht manuell, sondern immer über die Pivotfenster geändert.
b) Wenn sich die Daten der Auswertungen geändert haben (einfach vor jeder Auswertung), muss die Pivottabelle über Rechtsklick und "Aktualisieren" aktualisiert werden. (Dann erscheint auch ein neuer Name und der neue Monat.)

zu Pivot 3.

Der Satz ist zwar nicht ganz vollständig, aber ich nehme an, dass du die Überschriften von Aufwand und Pauschale ändern möchtest und unter gesamt die Summe dieser beiden Werte angezeigt bekommen willst.
Das Arbeitsblatt Pivot hatte ich eigentlich nur eingerichtet um dir zu zeigen, wie einfach es ist die Daten nach den verschiedensten Richtungen auszuwerten, wenn sie in eine Liste zusammengefügt sind.
Die Summe aus Aufwand und Pauschale ist in der Auswertungstabelle noch nicht eingerichtet. Du kannst ja einmal eine Spalte hinzufügen, die Formel eintragen, die Pivottabelle aktualisieren und das neue Feld über das Fenster "PivotTable-Felder" in "Werte" verschieben.

zu Pivot 4.

Da kann ich dir zur Zeit noch keine Antwort geben, da ich Pivottabellen bisher nur für sehr einfache Auswertungen selbt genutzt habe, aber ich bin sicher, dass solche Konstanten auch berücksichtigt werden können.
Wenn es für euch aber wichtig ist könnte man natürlich auch an den verschiedensten Stellen dieser Datei  Warnungen ausgeben, wenn zu einem Zeitpunkt im Jahr eine Stundenzahl schon überschritten ist. Die Daten sind ja jetzt einfach in einer Tabelle.