Anzeige
Archiv - Navigation
1740to1744
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

ZÄHLENWENN

ZÄHLENWENN
13.02.2020 15:52:35
Marc
Hi,
folgendes Problem treibt mich um:
Wenn in einer Spalte ein bestimmtes Zeichen enthalten ist (x oder o) möchte ich in der gleichen Zeile für einen bestimmten Bereich (unten A-C) ebenfalls die Zeichen x und o zählen.
Folgendes Beispiel zur Veranschaulichung:

A	B	C
1	x	x
2	x	x	x
3	x		o
4		o
5		x

Wenn ich in der Spalte A nach x oder o suche sollte das Ergebnis 7 (Summe der x/o in den Zeilen 1,2 und 3) sein. Für die Spalte B müsste das Ergebnis ebenfalls 7 lauten (Summe der x/o aus den Zeilen 1,2,4 und 5) und für die Spalte C wäre 4 die richtige Antwort (Summe der x/o aus den Zeilen 2 und 3).
Ich habe es jetzt eine Weile mit ZÄHLEWENN und SUMMEWENN probiert, komme da aber auf keinen grünen Zweig weil ich es nicht verstehe den "horizontalen" Suchbereich zu definieren wenn er in der Spalte ein x oder o gefunden hat?!
Kann mir jemand helfen?
Danke vorab.

35
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: für C: nur 4 ? Es sollte mE 5 sein? owT
13.02.2020 16:02:57
neopa
Gruß Werner
.. , - ...
AW: für C: nur 4 ? Es sollte mE 5 sein? owT
13.02.2020 16:11:56
Marc
Richtig, 5 sollten es sein.
Jemand eine Idee wie die richtige Formel dazu ausschaut?
AW: dann z.B. mit SUMMENPRODUKT() ...
13.02.2020 16:20:42
neopa
Hallo Mark,
... wenn Du z.B. in D1 die Spaltennummer einschreibst, in der Du suchen willst, dann folgende Formel:

=SUMMENPRODUKT((INDEX(A1:C99;;D1)"")*(A1:C99""))

Gruß Werner
.. , - ...
abhängiges Zählen
13.02.2020 16:34:09
WF
Hi,
=SUMMENPRODUKT((A1:A5"")*((A1:C5="o")+(A1:C5="x")))
dito dann mit (B1:B5"") und (C1:C5"")
WF
AW: es reicht auch nur eine Formel ...
13.02.2020 17:07:50
neopa
Hallo Marc,
... und auch ohne Spaltenvorgabe für den Fall, dass lediglich die "x" und "o" ausgewertet werden sollen und egal ob andere Zeichen/Texte in den den restlichen Zellen stehen oder diese leer sind.
Formel E1 einfach nach rechts ziehend kopieren:
Arbeitsblatt mit dem Namen 'F_200213_2'
 ABCDEFG
1xxnix 775
2xxx    
3x o    
4-o     
5nx-    
6       
7       

ZelleFormel
E1=SUMMENPRODUKT(MMULT((INDEX($A$1:$C$99;;SPALTE(A1))={"x"."o"})+0;{1;1})*(($A$1:$C$99="x")+($A$1:$C$99="o")&gt0))
Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Gruß Werner
.. , - ...
Anzeige
Und das kapiert er ?
13.02.2020 17:42:43
WF
.
AW: es reicht auch nur eine Formel ...
13.02.2020 18:00:55
Marc
Hi,
mega wie schnell man hier Feedback bekommt. Jetzt wo ich das übernommen habe habe ich aber erst verstanden, dass meine Anforderung noch ein Stück weiter geht als bisher beschrieben/gedacht.
Was versuche ich eigentlich?
Mein Ziel ist die Aufwände zu ermitteln, die - sofern diese nicht durch eine Person allein geleistet werden - durch die Anzahl der übrigen Kollegen zu teilen und die Einzelergebnis aufzusummieren.
Wieder ein konkretes Beispiel:

Spal A   Spal B   Spal C  Spal D
Aufwand	 Mit.A	  Mit.B	  Mit.C			Für A:	(1/2)+(2/3)+(3/2)	=2,666666667
1	 x	  x				Für B:	(1/2)+(2/3)+4+5	        =10,16666667
2	 x	  x	  x			Für C:	(2/3)+(3/2)	        =2,166666667
3	 x		  o
4	 	  o
5	 	  x
Gestartet bin ich also für Mitarbeiter A mit =SUMMEWENN(B2:B6;"x";A2:A6)+SUMMEWENN(B2:B6;"o";A2:A6) um die Summe alle Aufwände für Mitarbeiter B zu ermitteln. (In der Folge analog für Mitarbeiter B und C.)
Im nächsten Schritte wollte ich "einfach" die ermittelte Summe durch die anderen Mitarbeiter teilen die unterstützen.
Wenn ich jetzt aber mit SUMMENPRODUKT arbeite multipliziert er mir ja die x/o mit allen weiteren x/o im ausgewählten Bereich. Und selbst wenn ich das SUMMENPRODUKT anpasse und anstelle von =SUMMENPRODUKT((A1:A5"")*((A1:C5="o")+(A1:C5="x"))) auf =SUMMENPRODUKT((A1:A5"")*((B1:C5="o")+(B1:C5="x"))) anpasse stimmt das auch noch nicht weil ich ja dann den zuvor ermittelten Gesamtaufwand durch die Summe aller x/o teile.
Vermutlich bin ich da komplett falsch an die Sache heran gegangen. Habe mich jetzt irgendwo auf der Strecke verloren und bräuchte mal Hilfe wie ich aus der Nummer wieder rauskomme.
Danke vorab!
Anzeige
ich schrieb "dito"
13.02.2020 19:17:33
WF
=SUMMENPRODUKT((A1:A5"")*((A1:C5="o")+(A1:C5="x")))
dito dann mit (B1:B5"") und (C1:C5"")
heißt:
=SUMMENPRODUKT((B1:B5"")*((A1:C5="o")+(A1:C5="x")))
=SUMMENPRODUKT((C1:C5"")*((A1:C5="o")+(A1:C5="x")))
AW: ich schrieb "dito"
13.02.2020 19:27:49
Marc
Ne, das würde ja wie zuletzt beschrieben nur die Summe aller anderen Mitarbeiter zählen um im nächsten Schritt den Gesamtaufwand durch diese Anzahl zu teilen. Genau das will ich ja aber nicht, sondern ich muss jeden Aufwand einzeln betrachten und diesen durch die Anzahl der Mitarbeiter die ihm zugeordnet sind teilen (und das dann pro Mitarbeiter).
AW: auch dann reicht nur eine Formel ...
13.02.2020 19:43:58
neopa
Hallo Marc,
... Formel wieder nach rechts kopieren:
Arbeitsblatt mit dem Namen 'Tabelle4'
 ABCDEFGHI
1AufwandMA 1MA 2MA 3  MA 1MA 2MA 3
21xx   2,6710,172,17
32xxx     
43x o     
54 o      
65 x      
7         

ZelleFormel
G2{=SUMME(WENN(MMULT((INDEX($A2:$D99;;SPALTE(B1))&gt0)*($B2:$D99>"");{1;1;1})&gt0;$A2:$A99/MMULT((INDEX($A2:$D99;;SPALTE(B1))&gt0)*($B2:$D99>"");{1;1;1})))}
Achtung, Matrixformel enthalten!
Die geschweiften Klammern{} werden nicht eingegeben.
Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.
Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Gruß Werner
.. , - ...
Anzeige
AW: die Prüfung kann einfacher erfolgen ...
13.02.2020 19:54:52
neopa
Hallo,
... so:
{=SUMME(WENN(INDEX($A2:$D99;;SPALTE(B1))&gt0;$A2:$A99/MMULT((INDEX($A2:$D99;;SPALTE(B1))&gt0)*($B2:$D99"");{1;1;1})))}
Gruß Werner
.. , - ...
AW: sorry, die falsche Formel kopiert ...
13.02.2020 20:04:23
neopa
Hallo,
... einfacher so:
{=SUMME(WENNFEHLER($A2:$A99/MMULT((INDEX($A2:$D99;;SPALTE(B1))&gt0)*($B2:$D99"");{1;1;1});0))}
Gruß Werner
.. , - ...
und geht noch einfacher ...
13.02.2020 21:07:55
Besserwisser
Hallo,
=SUMME(WENN(B2:B6>"";$A2:$A6/MMULT(N($B$2:$D$6>"");{1;1;1})))
reicht aus.
Gruß
Christian
AW: und geht noch einfacher ...
14.02.2020 11:08:19
Marc
Boah Leute...das ist schon abgefahren. In Teilen habt ihr mich immer noch verloren auch wenn ich die ganzen Formeln (INDEX, MMULT, N) jetzt mal nachgelesen habe. Ich glaube so ganz dahinter bin ich immer noch nicht gestiegen wie das funktioniert :)
Mein Problem: Die Formel funktioniert zwar in dem Beispiel, aber wenn ich das auf meinen Fall übertrage bekomme ich die Anzeige "#WERT!" und ich finde den Fehler einfach nicht. Ich würde behaupten das ist identisch zu unserem Beispiel...scheint ja aber offensichtlich nicht so zu sein.
Ich habe daher mal meine Excel-Datei hier hochgeladen: https://www.herber.de/bbs/user/135201.xlsx
Anders kommt man meinem Denkfehler wohl nicht auf die Schliche.
Tausend dank noch mal vorab. Mega Support (und Kompetenz) hier!!
Anzeige
Spalte J bis O sind 6 Einser (nicht 3)
14.02.2020 12:04:19
WF
in C11:
{=SUMME(WENN(J4:J7>"";$C4:$C7/MMULT(N($J$4:$O$7>"");{1;1;1;1;1;1})))}
WF
AW: sieh Dir einmal Hilfe zur Fkt MMULT() an ...
14.02.2020 12:04:34
neopa
Hallo Marc,
... dann dürfte es Dir klarer werden. Das zweite Argument (die notwendige 1ser Zeilen-Matrix hab ich untenstehend ganz allgemein ermittelt.
In C11: {=SUMME(WENN(J$4:J$7&gt0;$C4:$C7/MMULT(N($J$4:$O$7&gt0);MTRANS(SPALTE(J3:O3)^0))))}
Gruß Werner
.. , - ...
AW: noch ein ergänzender Hinweis ...
14.02.2020 15:45:22
neopa
Hallo Marc,
... wenn Du die Ergebnisse für alle Mitarbeiter auch nach unten ziehend kopieren willst, würde ich die Formel in C11 wie folgt erweitern:
{=SUMME(WENN(INDEX($J$4:$O$7;;ZEILE(C1))&gt0;C$4:C$7/
MMULT(N($J$4:$O$7&gt0);MTRANS(SPALTE(J3:O3)^0))))}

Diese kannst Du dann auch nach rechts für alle Wochentage ziehend kopieren.
Oder alternativ folgende Formel z.B. in J10:
{=SUMME(WENN(J$4:J$7&gt0;INDEX($C$4:$G$7;;ZEILE(A1))/
MMULT(N($J$4:$O$7&gt0);MTRANS(SPALTE(J3:O3)^0))))}

und diese nach rechts ziehend kopieren für alle Mitarbeiter und nach unten für den jeweilign Wochentag beginnend ab Montag.
Gruß Werner
.. , - ...
Anzeige
AW: noch ein ergänzender Hinweis ...
14.02.2020 16:28:13
Marc
Mega Vorschlag. Mein Problem wäre nur, dass ich jetzt schon so meine Schwierigkeiten habe das noch zu vestehen. Würde behaupten, dass ich hier gerade noch am "Zipfel hänge". Wenn ich das wie von dir vorgeschlagen oben jetzt noch ergänze habe ich Sorge, dass wenn ich mir das in einem halben Jahr anschaue ich damit nix mehr anfangen kann :)
(In jedem Fall werde ich es aber denke ich als Kopie in einer Version v2 vorhalten, damit ich zumindest weiß wie man es noch verbessern kann!)
AW: ja, im Nachgang betrachtet ...
14.02.2020 12:12:09
neopa
Hallo Christian,
... auch völlig naheliegend. Hatte da noch den "Tunnelblick" aus meinem aller erstem Formelbeitrag im thread wohl zu sehr verinnerlicht. Aber zwei Dollars könnte man in Deiner Verkürzung auch noch einsparen. Und da die Datenquelle keine Formelergebnisse und positive sind, kann man auch &gt0 schreiben.
Gruß Werner
.. , - ...
Anzeige
Habe mal Deine Fml zerlegt, ...
14.02.2020 04:09:55
Luc:?
…Werner,
falls es Verständnisprobleme geben sollte. Hier mal nur in EinzelFktt, geht aber noch mehr:
ƒ1:= SPALTE(B1)
ƒ2:= INDEX($A2:$D99;;ƒ1)
ƒ3:= MMULT((ƒ2>0)*($B2:$D99<>"");{1;1;1})
ƒ4:= WENNFEHLER($A2:$A99/ƒ3;0)
ƒ5:= SUMME(ƒ4)
Der Test der UDF an Deiner Fml war nützlich, denn er offenbarte noch ein Versäumnis meinerseits. ;-)
Morhn, Luc :-?
„Die Intelligenzmenge ist auf diesem Planeten eine Konstante, die Bevölkerung nimmt aber zu!“ Auch deshalb informieren mit …
Anzeige
AW: ZÄHLENWENN
14.02.2020 16:26:02
Marc
Hi,
ich fange jetzt noch mal im Baum "vorne" an weil ich schon gar nicht mehr weiß auf welchen Beitrag ich hier am Besten antworten soll.
Es fehlt nur noch der letzte Schritt von dem ich dachte ich hätte ihn bereits lösen können. Nun hat sich gezeigt, dass meine Lösung nur die Hälfte bedient und ich habe keine Ahnung wie ich den 2. Teil abgebildet bekomme.
Konkret gehts darum, die zuvor kalkulierten Aufwände jetzt auch noch mit Anwensenheiten ins Verhältnis zu setzen, bzw. diese bei der Berechnung entsprechend mit zu berücksichtigen. Wenn jemand nicht anwesend ist bekomme ich das noch einfach abgefangen und kann das abfangen. Aber das der Aufwand für die übrigen entsprechend steigt bekomme ich nicht mehr hin.
Ich habe das jetzt mal alles in folgendem Excel aufbereitet: https://www.herber.de/bbs/user/135203.xlsx
Da habe ich zumindest versucht die einzelnen Schritte aufzuzeigen, bzw. die Probleme zu erklären und mich auch an einer Lösung versucht. (Mehr als einen Versuch kann man es aber leider nicht nennen...)
Wäre toll wenn mir hier jemand noch mal unter die Arme helfen kann!
Danke vorab.
Anzeige
AW: hat nichts mit ZÄHLENWENN zu tun
14.02.2020 16:59:19
neopa
Hallo Marc,
... ergänze die Formel in N26 einfach mit {=SUMME(....)*(C6&gt0)}
Gruß Werner
.. , - ...
Du meinst sicher C26, ...
14.02.2020 19:13:03
Luc:?
…Werner, ;-)
erst dann ändert sich etwas. Mit Hinzufügen von INDEX wäre die Fml auch auf alle relevanten Zellen in diesem Bereich übertragbar. Das sähe für N26 (1.Zelle) dann so aus:
M0:= SUMME(WENN(INDEX($J$4:$O$7;0;ZEILE(N1))>"";C$4:C$7/MMULT(N($J$4:$O$7>"");{1;1;1;1;1;1})))*(C26>0)
†1:= ""
‡2:= $J$4:$O$7
‡3:= N1
‡4:= C$4:C$7
‡5:= C26
µ6:= {1;1;1;1;1;1}
ƒ7:= N(‡2>†1)
ƒ8:= MMULT(ƒ7;µ6)
ƒ9:= ZEILE(‡3)
ƒ10:= INDEX(‡2;0;ƒ9)
ƒ11:= WENN(ƒ10>†1;‡4/ƒ8)
ƒ12:= SUMME(ƒ11)
Mf:= ƒ12*(‡5>0)

M0 zeigt die einzutragende MatrixFml. In Blau folgt ihr Parsing (AblaufDemo f.Marc), wobei Mf quasi die finale MxFml (Berechnung) darstellt.
Gruß u.schöWE, Luc :-?
Anzeige
AW: ja, Luc ... und Marc ...
15.02.2020 08:50:56
neopa
Hallo Luc,
... ich meinte wirklich C26 anstelle C6 in meiner vorgeschlagenen Formelergänzung.
Doch wie ich später erst feststellte, war das ja gar nicht das von Marc angestrebte, sondern die von mir dann als 2. Teil bezeichnet Fragestellung.
Die hab ich mir jetzt genauer angeschaut und mussteeststellen, dass dazu mE weitere Angaben benötigt werden.
@ Marc,
... ohne Deine zumindest eindeutige verbale Beschreibung, der dann vorzunehmenden Berechnungen oder wenigstens der im Beispiel von Dir erwartenden Ergebniswerte, wird das mE nur ein "Fischen im trüben".
Doch wisse, dass ich in Kürze bis Ende nächster Woche offline sein werde und somit im thread nicht mehr antworten kann.
Gruß Werner
.. , - ...
AW: ja, Luc ... und Marc ...
15.02.2020 20:19:55
Marc
Das ist aber schade - zumindest für mich.
Was mich aber wundert ist welche Info in dem zur Verfügung gestellten Excel noch fehlt. Ich hätte angenommen ich hätte die Anforderung, das Problem und die Schritte dahin gut erklärt? Was kann ich noch ergänzen?
Na, nur die Ruhe, ...
17.02.2020 02:35:37
Luc:?
…Marc,
so viel anders ist die benötigte singulare MatrixFml ja nicht. Im Grunde handelt es sich hier zwar um einen 3-4stufigen Tensor, aber er muss ja nicht dargestellt, sondern nur summiert wdn, auch noch einzeln pro Tag und Mitarbeiter:
N26[:R31]: {=SUMME(WENN((INDEX($J$4:$O$7;0;ZEILE(J1))>"")*(C26>0);C$4:C$7/MMULT(N($J$4:$O$7>"");N(C$26:C$31>0))))}
Ich bin hier wieder meinem 1-Fml-Vorschlag gefolgt, so dass du nicht für jede Zeile eine neue Fml benötigst. Auf das Parsing verzichte ich diesmal, denn es sähe dem letzten recht ähnlich.
Morhn, Luc :-?
AW: Na, nur die Ruhe, ...
17.02.2020 08:36:53
Marc
Danke. Ich habe die Formel mal (nur!) für Mit 1 für Mo-Fr angewendet. Mo, Do und Fr passen (da ist der MA ja auch nicht verfügbar und deswegen einfach zu handhaben).
Dienstag ist aus meiner Sicht falsch kalkuliert, Mittwoch würde passen. Vielleicht habe ich aber auch was falsch gemacht?
Siehe auch: https://www.herber.de/bbs/user/135234.xlsx
Eben, "mal nur", ...
17.02.2020 14:51:54
Luc:?
…Marc;
das ergibt kein stimmiges Gesamtbild. Allerdings hätte ich mir derartige Einwände denken können, denn deine Erwartungen in der vorherigen Datei ließen bereits darauf schließen, dass du solche hättest. Wahr­schein­lich hatte Werner (neopa) deshalb nähere Erläuterungen verlangt. Es könnte ja auch sein, dass der Aufwand pro MA und Tag je Tätigkeit kontingentiert ist. Wir wissen ja nicht, was die Zahlen in der Anwe­sen­heit bedeuten sollen (außer 0), viell sind es ja ebenfalls (Anwesenheits-)Stunden. Von der Bedeutung der o und x in der Skill-Tabelle und evtl Einfluss ihres 2.Teils ganz zu schweigen.
Die Ergebnisse sind proportional zu allen Eingangsdaten. Damit ergibt sich natürlich ein höherer Aufwand für den, der an allen Tätigkeiten beteiligt und auch noch jeden Tag anwesend ist. Die Fml verteilt auch nicht Über­hänge bei Abwesenheit eines MAs auf die anderen beteiligten, das machte schon die vorherige Fml nicht für unbeteiligte, sondern rechnet von vornherein pro Tag und Tätigkeit mit der Anzahl der verfügbaren und auch vorgesehenen MA. Dabei ergeben sich folgende Gesamtbilder (TabellenInhalte als MatrixKonstante des formatierten Ergebnisses dargestellt → kann als Fml zum Vgl leicht ins Blatt übertragen wdn):
B3:H8:
={"Beschreibung"."Montag"."Dienstag"."Mittwoch"."Donnerstag"."Freitag"."Gesamt";
"Beschreibung A".5.4.3.2.1.15;
"Beschreibung B".7.2.3.4.5.21;
"Beschreibung C".2.3.2.3.2.12;
"Beschreibung D".4.5.3.4.1.17;
"alle Tätigkeiten".18.14.11.13.9.65}
M11:S18:
={""."Montag"."Dienstag"."Mittwoch"."Donnerstag"."Freitag"."Mo-Fr";
"Lösung Mit 1"."0,00"."2,33"."3,00"."0,00"."0,00"."5,33";
"Lösung Mit 2"."9,00"."8,83"."0,00"."5,17"."3,50"."26,50";
"Lösung Mit 3"."3,50"."2,83"."3,50"."2,50"."1,50"."13,83";
"Lösung Mit 4"."0,00"."0,00"."0,00"."2,67"."2,00"."4,67";
"Lösung Mit 5"."0,00"."0,00"."0,00"."0,00"."0,00"."0,00";
"Lösung Mit 6"."5,50"."0,00"."4,50"."2,67"."2,00"."14,67";
"alle MA"."18,00"."14,00"."11,00"."13,00"."9,00"."65,00"}
Die Kontroll-SpaltenSummen sind hier stets dieselben, was auch schon für die vorherige Fml galt. Wollte man ein EinzelErgebnis korrigieren, hätte das Auswirkungen auf alle anderen in der Spalte, denn zum Schluss muss sich wieder die Tagessumme der Tätigkeiten ergeben. Ein Eingriff muss also „Hand und Fuß“ haben und algorithmierbar sein, also den Einfluss eines zusätzlichen Faktors abbilden.
Luc :-?
AW: Eben, "mal nur", ...
17.02.2020 16:04:39
Marc
Hi,
ich glaube Stimmigkeit war bis jetzt auch noch gar nicht das Ziel. Mir gings vor allem um die Formel, weniger um Konsistenz. Gerne will ich aber noch mal einen Anlauf nehmen und die Anforderung bestmöglich beschreiben:
Am Ende will ich 2. Sichten abbilden:
1. Welcher Mitarbeiter kann, bzw. verantwortet welche Tätigkeit. Das ist die Nummer mit dem x, bzw. o hinterlegt in J3:O7. (Hier hatte ich mir überlegt, dass man mit "x" diejenigen kennzeichnet, die die Tätigkeit können/ausüben und mit "o" diejenigen kennzeichnet, die die Tätigkeit verantworten. Für die Kalkulation aber bisher völlig unerheblich.)
2. Ich möchte die Auslastung der einzelnen Mitarbeiter pro Tag Tag ausweisen. D.h. ich muss zum einen hinterlegen wie viel Aufwand in h nimmt eine Tätigkeit pro Tag in Anspruch (A3:G7) und zum anderen brauche ich eine Sicht auf die Mitarbeiter wer überhaupt verfügbar ist - ebenfalls in h pro Tag (B11:G17). (Wenn das mal geschafft ist ist es nur noch ein Katzensprung das Delta auszuweisen, das Mitarbeiter aktuell "überplant" sind. Für den Moment aber auch erst mal egal.)
Die Ergebnistabelle (M11:R17) soll genau diesen 2. Punkt adressieren. D.h. pro Mitarbeiter und Tag den anfallenden Aufwand über alle dem Mitarbeiter zugeordneten Tätigkeiten auszuweisen. Konkret am Beispiel: Mitarbeiter 1 ist ohnehin nur für Di und Mi gepflegt. Konsequenterweise kann der Aufwand Mo, Do und Fr also nur 0 sein. Für Di und Mi ist es aber nicht damit getan einfach nur die Aufwände pro Tätigkeit durch die anderen zugeordneten Mitarbeiter zu teilen und die Einzelergebnisse aufzusummieren, sondern wenn andere Mitarbeiter an dem Di oder Mi nicht anwesend sind (also eine 0 ausgewiesen haben) das entsprechend zu berücksichtigen.
Deine neue Formel habe ich dann übernommen und (weil man das in dieser Komplexität noch einfach ausrechnen kann) mit meiner Erwartungshaltung abgeglichen mit dem Ergebnis, dass es bereits für Mitarbeiter 1 für Dienstag (O12) nicht stimmt - angenommen meine Rechnung ist richtig :). Das habe ich in T18:AA22 hinterlegt.
Hat das geholfen? Falls nicht bitte noch mal Bescheid geben. Ich versuchs dann auch gerne noch mal. Ehrlicherweise glaube ich nämlich, dass mir für diese Lösung schlicht das Knowhow fehlt um das alleine rauszubekommen :(
AW: Eben, "mal nur", ...
17.02.2020 17:13:47
Marc
So, nachdem ich jetzt jede Einzelformel noch mal rauskopiert und deren Teilergebnisse überprüft habe bin ich auf einen Fehler meinerseits gestoßen. In meiner Tabelle mit der Gegenprobe habe ich den Aufwand für Tätigkeit B am Dienstag falsch kalkuliert.
Konkret habe ich einen Aufwand von 3h hinterlegt. Richtig wäre aber 2h gewesen und wenn man das entsprechend anpasst kommt auch mit deiner Formel das richtige Ergebnis raus.
Ich vermute also, dass für den Moment alle Fragen geklärt sind. Ich übertrage das jetzt noch mal auf alle anderen Mitarbeiter und Tage und sollte dann noch was offen sein würde ich mich noch mal melden.
Bis dahin aber (ein weiteres Mal) ein herzliches Dankeschön!
Sei es wie es sei, ...
17.02.2020 19:48:30
Luc:?
…Marc,
meine Fml liefert ein genauso richtiges Ergebnis wie neopas, nur ist hierin auch die Tagesverfügbarkeit des jeweiligen Mitarbeiters berücksichtigt. Problematisch erscheinen mir eher die Ergebnisse für MA2/3 am Mon­tag und MA2 am Dienstag, falls die Anwesenheitstabelle Verfügbarkeitsstunden und nicht irgendwelche Abwe­sen­heitsgrund-Codierungen enthält. Aber auch im letzteren Fall könnten die Ergebnisse schon mal über die normale tägliche ArbeitsstundenZahl hinausreichen. Aber das willst du ja wohl mit Differenzbildung fest­stel­len (Δ). Ich habe das mal interessehalber unter der Annahme, dass die Anwesenheit Verfüg­bar­keits­stun­den ent­hält, anhand deiner 1.Datei (die hatte ich gespeichert) durchgespielt. Dabei ergibt sich mit folgender (Nor­mal-)Fml …
X26[:AB31]: =(N26-C26)*(N26>0)
…eine Δ-Tabelle (analog ErgebnisTabelle aufgebaut) mit folgd Ergebnissen (als MatrixKonstantenFml):
={0,00.-1,67.-2,00.0,00.0,00;1,00.0,83.-3,50.-2,83.-4,50;0,50.-2,17.-3,00.-3,50.-5,50;
0,00.0,00.0,00.-1,33.-2,00;0,00.0,00.0,00.0,00.0,00;-2,50.0,00.-5,50.-1,33.-2,00}
Die Δ-Werte>0 würden hier dann eine Überplanung des MAs bedeuten und müssten auf die am jeweiligen Tag verfügbaren anderen beteiligten MA so verteilt wdn, dass deren Verfüg­bar­keits­stun­den­Zahl nicht über­schrit­ten wird. Das fktioniert in deinem Bsp schon beim 1.Mal, so dass mir weitere Aktionen erspart blieben. Die singulare MatrixFml kann dann bspw so lauten:
X41[:AB46]: {=WENN(X260))/SUMME(--(X$26:X$310))}
In ihrem Ergebnis entsteht eine (Lösungs-)Tabelle, deren Werte maximal auf den jeweiligen Wert aus der Anwesenheits­tabelle begrenzt sind, wobei das ursprüngl Ergebnis ebenfalls berücksichtigt wird:
={0,00.2,75.2,00.0,00.0,00;8,00.8,00.4,50.5,17.3,50;3,00.3,25.2,00.2,50.1,50;0,00.0,00.0,00.2,67.2,00;
0,00.0,00.0,00.0,00.0,00;7,00.0,00.2,50.2,67.2,00}
Etwas Derartiges könnte man wahrscheinlich schon mit einer 1.ErgebnisFml erreichen, falls man zusätzlich Grenzwerte einführt, egal ob unterschiedliche oder stets nur einen. Aber das wäre dann eigentlich „dein Bier“… ;-)
Luc :-?
AW: Sei es wie es sei, ...
17.02.2020 20:45:15
Marc
Also entweder habe ich die falsche Formel übernommen oder die ist schlicht nicht richtig. Besonders auffällig wird es für Mitarbeiter 5. Der ist keiner(!) Tätigkeit zugeordnet, jeden Tag im Büro (ja, die Konstellation mag nicht sonderlich schlau oder konsistent sein) und hat aber jeden Tag Aufwände?!
Ich habe das Excel jetzt noch mal auf das Wesentlichste reduziert und unter die 4 Tabellen eine Gegenprobe mit den zu erwartenden Ergebnissen für jeden Mitarbeiter pro Tag an Aufwand daneben gestellt. Mit der aktuellen Formel sind von den 30 zu errechnenden Aufwänden 10 richtig, die übrigen 20 sind falsch :(
Hier das dazu passende Beispiel: https://www.herber.de/bbs/user/135255.xlsx
Du kennst dich wohl nicht mit INDEX aus, ...
17.02.2020 22:43:00
Luc:?
…Marc‽
INDEX soll hier die jeweils benötigte Spalte aus $J$4:$O$7 lt Zeile wiedergeben, also in der 1.Zeile Spalte J, in der 2. Spalte K usw. Dazu darf aber zumindest die Zeilenangabe nicht absolut sein, die Spaltenangabe ist egal. Man kann statt J1 (hier besser K1) auch 1:1 schreiben - ohne $! Machst du das so wie von mir von Anfang an angegeben, wird's auch richtig (deine Werte kommen raus)! Ich hatte dir ja auch komplette Matrixkonstanten der Ergebniswerte geliefert, die mit deinen Vglswerten übereinstimmen. Daran hättest du schon erkennen können, dass du einen Fehler gemacht und die Fml falsch angepasst hast. Ist wohl schon zu spät geworden für dich… ;-]
Luc :-?
AW: Du kennst dich wohl nicht mit INDEX aus, ...
18.02.2020 07:57:05
Marc
Ertappt. Ich hatte mir inzwischen schon ein paar Videos zu dieser Index-Fkt angeschaut, aber so richtig dahinter bin ich offensichtlich noch nicht gestiegen.
Selbst mit deiner Erklärung habe ich es noch nicht geschafft die richtigen Spalten anzugeben. Nachdem ich es aber auf 1:1 geändert habe läuft es inzwischen.
Damit würde ich es hier auch erst mal gut sein lassen. Wenn ich jetzt noch weiteren Bedarf finde den ich selber nicht rausbekomme lasse ich mir ein anderes Beispiel einfallen und poste unter einem neuen Pseudonym...wird ja langsam peinlich hier ;)
Danke noch mal an alle die geholfen haben!
AW: Teil 2 Deiner Frage schau ich mir am WE an owT
14.02.2020 17:02:57
neopa
Gruß Werner
.. , - ...

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige