Ich versuche, eine Angebots-/Preistabelle für die interne Auftragskalkulation zu aktualisieren. Unsere Projektleiter schätzen die Stunden pro Mitarbeiter und Monat, und dann wird auf diese Schätzung ein Satz angewendet, um einen Gesamtpreis zu ermitteln. Nicht jeder Auftrag wird jeden Mitarbeiter nutzen.
| Mitarbeiter/Stunden | Januar/21 | Februar/21 | 21. März |
|---|---|---|---|
| John Smith | 10 | 15 | 20 |
| Jane Doe | 20 | 20 | 10 |
Die Stundensatztabelle befindet sich in einer anderen Registerkarte und listet alle Mitarbeiter in Spalte A und ihren Stundensatz pro Monat ab Spalte B mit denselben Datumsüberschriften wie die Stundenschätzung auf. Die Stundensatztabelle kann auch Monate vor oder nach den Monaten auf der Registerkarte „Stunden“ enthalten.
| Tariftabelle | Dez/21 | Januar/21 | Februar/21 | 21. März |
|---|---|---|---|---|
| John Smith | $12 | $12 | $12 | $13 |
| Jim Doe | $13 | $13 | $13 | $13 |
| Jane Doe | $12 | $12 | $13 | $13 |
In der Vergangenheit hatte ich die Stundentabelle oben auf der Registerkarte „Stundenschätzung“ und dann eine weitere Tabelle direkt darunter, die INDEX MATCH MATCH verwendet, um den entsprechenden Stundensatz pro Mitarbeiter und Monat zu ermitteln und diesen dann mit den Stunden für diesen Mitarbeiter oben zu multiplizieren. Das funktioniert, bedeutet aber, dass ich die Gesamtkosten für jeden Mitarbeiter pro Monat angeben muss, was ich wirklich nicht tun muss. Ich hätte gerne einfach eine Zeile unter jeder Monatsspalte mit den Gesamtkosten für diesen Monat und müsste die Formel nicht mehrmals schreiben.
=(B2 * INDEXMATCHMATCH)+(B3 * INDEXMATCHMATCH)+(B4 * INDEXMATCHMATCH)
[das ist offensichtlich nicht die richtige Formel, weil sie superlang wäre].
Gibt es eine Möglichkeit, die Stunden in Spalte B mit dem zugehörigen Stundensatz für diese Mitarbeiter für diesen Monat zu multiplizieren und die Ergebnisse in einer Zelle zusammenzufassen?
| Mitarbeiter/Stunden | Januar/21 | Februar/21 | 21. März |
|---|---|---|---|
| John Smith | 10 | 15 | 20 |
| Jane Doe | 20 | 20 | 10 |
| Gesamtkosten | 360 $ | 380 $ | 390 $ |
Antwort1
Ich denke, Sie möchten gerne die folgende Möglichkeit ausprobieren:
=SUM(XLOOKUP($G$2:$G3,$A$2:$A$4,INDEX($B$2:$E$4,,MATCH(H$1,$B$1:$E$1,0)))*H$2:H$3)
(Ich habe ein paar Möglichkeiten gefunden, manche davon sind interessant, aber ausgeschrieben sind sie länger als diese Formel, und Sie scheinen mit der INDEX/MATCHVorgehensweise vertraut zu sein, sodass sie für Sie vermutlich leicht anzuwenden und beizubehalten sein wird.)
Für die Formel habe ich Ihre Datentabellen in A1:E4 für die „Ratentabelle“ und G1:J3 für die Tabelle „Mitarbeiter/Stunden“ kopiert. Die Adressierung in der Formel funktioniert für diese Bereiche. Sie sollten sie an Ihre genaue Situation anpassen.
Bis zum Ende durchlesen: Seien Sie nicht beunruhigt, dass ich dummerweise von einer Ausgabetabelle spreche.(Mir ist erst beim erneuten Lesen der Frage und Antwort aufgefallen, dass Sie die Ergebnisse unter den Datenzeilen der Tabelle „Mitarbeiter/Stunden“ haben wollten.) Die obige Formel erfüllt diesen Zweck, da ich die Verweise, die ich beim Generieren einer formalen Ausgabetabelle erstellt habe, korrigiert habe, sodass Sie sie bei Bedarf einfach einfügen können.)
Um die Ausgabetabelle zu erstellen, habe ich einfach Formeln verwendet, =H1um die linken und oberen Überschriften (Namen und Monate) der Ausgabetabellen auszufüllen. Diese sind Ihnen offensichtlich bekannt und können daher bei Bedarf für jede von Ihnen eingerichtete Berechnung leicht angepasst werden. Ebenso habe ich Standard- =SUM(M2#)Summierungsformeln verwendet, die den Vorteil haben, dass sie nicht angepasst werden müssen, da sie sich auf die gesamten SPILL-Arrays beziehen, die die Formeln in der ersten Zeile erzeugen.
Die wirkliche Schwierigkeit bei allen Ansätzen, die ich ausprobierte, war, dass ich am Ende eine Formel hatte, dieZWEISPILL-Arrays in der Endausgabe und Excel belohnt solche Dinge NICHT. Da sie jeweils unterschiedliche Verwendungszwecke hatten, konnte ich sie nicht zu einem einzigen SPILL-Array zusammenfassen, das eine einzige Formel in der oberen linken Zelle des Zellenblocks ermöglicht hätte, der den Kostenbereich der Ausgabetabelle bildet. Nachdem ich mich entschieden hatte, die SPILL-Arrays nach unten statt nach oben zu erstellen, war ich bereit für den „Kern“ des Problems, die monatlichen Kosten pro Mitarbeiter.
Für das „Fleisch“, sozusagen die Dollarkosten pro Monat für jeden Mitarbeiter in einem bestimmten Job, verwenden Sie die Formel.
Zuerst INDEX/MATCHgleicht das : (vorerst die erste Monatsspalte) die Monate der Ausgabetabelle mit den Spalten der „Ratentabelle“ ab und findet heraus, dass Spalte 2 der Dollarregion benötigt wird. Durch INDEXIEREN der Dollarregion erhält man ein Ausgabearray mit einer Größe von einer Spalte mal drei Zeilen, was dem XLOOKUP()Lookup-Array von „äußerer“ mit einer Spalte mal drei Zeilen entspricht. Wenn sie nicht so übereinstimmen würden, würden Sie einen Fehler erhalten. Dann XLOOKUP()sucht das nach dem Namen des Mitarbeiters, für den Sie ein Array verwenden ( $L2#in diesem Fall), um Ausgaben für alle in der Ausgabetabelle aufgeführten Mitarbeiter zu erhalten. Diese werden nach Wunsch nach unten SPILLT.
Kopieren Sie anschließend die Formel in jede Spalte der Ausgabetabelle, füllen Sie die Kosten für alle Mitarbeiter jeden Monat aus und aktualisieren Sie die Summen.
(Übrigens ist die Summe der Ausgabe in Ihrem Beispiel für „21. Februar“ falsch, weshalb die Formel 440 $ ergibt (15 * 12 $ = 180 $, 20 * 13 $ = 260 $, 180 $ + 260 $ = 440 $ statt 380 $.)
Um das Problem zu beheben, musste ich also nur die erste Formel in der Ausgabetabelle auswählen, drücken F2-Edit, die Formel markieren und kopieren, dann zur Tabelle „Mitarbeiter/Stunden“ wechseln, erste leere Zeile nach den Stundendaten, und „=SUM(“ eingeben, die kopierte Formel einfügen, „)“ eingeben und die Eingabetaste drücken. Nachdem ich die Referenzen so angepasst hatte, dass sie mit den Daten in der Tabelle „Mitarbeiter/Stunden“ übereinstimmten, enthielt die Zelle eine Summe der für die nicht benötigte Ausgabetabelle berechneten Informationen. Durch Kopieren der Zelle und Einfügen rechts wurde die gewünschte Ausgabe erhalten.
Ein letzter Gedanke: Das hat nicht dabei geholfen, ein SPILL-Array für alle Gesamtkostenzellen verwenden zu können. Traurigerweise. Sie müssen immer noch kopieren und einfügen, egal wie viele Spalten verwendet werden.