Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1508to1512
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

Summenprodukt? Text! Indirekt? Volatil!

Summenprodukt? Text! Indirekt? Volatil!
06.08.2016 14:30:42
MB12
Hallo zusammen,
diese Formel funktioniert:
=WENN(ZÄHLENWENN(AB8:AF8;"a")>2;"abwesend";WENN($D7:$D1000=$D7;SUMME(AB9:AF13)))
mit einem großen ABER.
Meine lieben Kollegen haben in Excel eine Feinterminplanung erstellt und mir diese erst nach Auftauchen der ersten Probleme gezeigt. Die Datei ist freigegeben und wird von mind. 20 Personen bearbeitet mit mehr oder weniger Excel-Kenntnissen.
Einen großen Teil konnte ich lösen, aber hier komme ich nicht weiter.
Jeder Mitarbeiter hat eine bestimmte Anzahl von Aufträgen, die sich aber immer wieder ändert. Wegen der Priorisierung müssen die Teamleiter immer eine weitere Zeile AM ENDE des jeweiligen Mitarbeiterbereichs einfügen. Die dort enthaltenen Zahlen werden von der Formel natürlich nicht übernommen.
Außerdem haben die Mitarbeiter unterschiedlich viele Aufgaben, sonst könnte man die Formel wenigstens im Filtermodus einfach runterziehen.
Die Originaldatei umfasst ca 1000 Zeilen und 150 Spalten.
Hier eine kleine Beispieldatei:
https://www.herber.de/bbs/user/107432.xlsm
Ich sehe schon kreuzweise vor lauter Ausprobieren und rausschmeißen.
Danke schön für jede Hilfe
Margarete

26
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Summenprodukt? Text! Indirekt? Volatil!
06.08.2016 15:59:00
silex1
Hallo,
dann schau mal, ob meine Formel ihrer Aufgabe nachkommt und dies in etwa dass ist, was Du suchtest?
=WENN(ZÄHLENWENN(INDEX($A:$CC;ZEILE()+1;SPALTE()-2):INDEX($A:$CC;ZEILE()+1;SPALTE()+2);"a")>2; "abwesend";SUMME(INDEX($A:$CC;ZEILE()+2;SPALTE()-2):INDEX($A:$CC;ZEILE()+1+$A7;SPALTE()+2)))
VG René
Erster Test sagt Jaaa!
06.08.2016 17:04:25
MB12
Hallo René,
am Montag werde ich am Arbeitsplatz deine Formel in der Originaldatei testen, bin "frohen Mutes"
Hab herzlichen Dank, so habe ich es mir vorgestellt.
Schönes WE!
Margarete :-)
AW: SUMMENPRODUKT() ist schon auch möglich ...
07.08.2016 11:53:01
...
Hallo Margarete,
... Renés Formelvorschlag ergibt zwar auch das richtige Ergebnis, aber ausreichend wäre einfach:
=WENN(ZÄHLENWENN(AB8:AF8;"a")>2;"abwesend";SUMMENPRODUKT((D9:D22=D7)*(AB9:AF22)))
worin die 22 evtl durch einen größeren Wert zu ersetzen wäre, wenn mehr als 14 auszuwertenden Datenzeilen je Mitarbeiter zu erwarten sind.
Gruß Werner
.. , - ...
Anzeige
AW: SUMMENPRODUKT() ist schon auch möglich ...
07.08.2016 16:27:32
MB12
Hallo lieber Werner,
erst mal vielen Dank. Diese Formel hatte ich auch schon in mehreren Variationen; das Problem ist Spalte D (Text). Funktionierte bei mir auch nicht mit *1 oder (D9:D22=D7);AB9:AF22) (bzw. umgekehrt)
Jetzt musste ich wegen verschiedener geforderter bed. Form. auch noch auf verbundene Zellen zurückgreifen, nachdem ich vorher alle rausgeworfen hatte. Rene's Formel habe ich darauf angepasst.
In AD7 habe ich den Zellverbund zum Testen vorübergehend aufgelöst. Die Lösung müsste aber MIT Verbund funktionieren.
Würdest du nochmal in die aktuelle Version reinschaun? Ächz...
https://www.herber.de/bbs/user/107452.xlsm
Danke dir (und allen, die sich evt. damit beschäftigen).
Gruß, Margarete
Anzeige
Anfängerfehler: nicht auf offen gestellt
07.08.2016 16:47:47
MB12
:-(
Vermute, du hast einen Zahlendreher in die Fml ...
07.08.2016 17:21:17
Luc:-?
…eingebaut, Margarete;
(D9:D21=D7)*(AB9:AF21) scheint mir doch wenig sinnvoll zu sein, (D9:D12=D7)*(AB9:AF12) da wohl schon eher… ;-]
Übrigens würde (D9:D12=D7)*AB9:AF12 reichen → reine ZellBereichsAdressen müssen nicht geklammert wdn!
Nebenbei, worin besteht das VerbundZellen-Problem? MatrixFml? Eine MatrixFml jeden Typs kann oW auch in einer VerbundZelle untergebracht wdn, wenn man sie nachträglich per FormatPinsel erzeugt.
Gruß+schöSoAhmt, Luc :-?
Besser informiert mit …
Anzeige
nee, is nich...
07.08.2016 17:47:41
MB12
Hallo Luc,
Zahlendreher habe ich überprüft, stimmt aber. In Spalte D stehen die Mitarbeiternamen
Mitarbeiter A von D7 bis ca. D15, wobei erst ab Zeile 9 summiert wird (in Zeilen 7 + 8 stehen andere Angaben)
Summiert werden sollen die Wochenstunden jedes einzelnen Mitarbeiters (z.B. AB9:AF21).
Soweit passt es also.
Verbundzellen: War kein Problem, nur Arbeit, nachträglich Renés Formel anzupassen.
Ich würde eben gerne die kurze Formel von Werner einsetzen, aber da ist halt der Text - siehe Beispieldatei!
Gerne nochmal: https://www.herber.de/bbs/user/107452.xlsm
Trotzdem vielen Dank.
Grüße aus Ho'loh, Margarete
Anzeige
AW: die evtl. "a"s einfach ausWECHSELN() ...
07.08.2016 18:04:07
...
Hallo Margarete,
... die "a"s bzw. "abwesend" hatte ich heute Mittag leider völlig übersehen, weil sie im unteren Testbereich zufälligerweise nicht da waren.
Nun, dann so:
=WENN(ZÄHLENWENN(AB8:AF8;"a")>2;"abwesend";SUMMENPRODUKT((D8:D69=D7)*(0&WECHSELN(LINKS(AB8:AF69;1); "a";0)))) 
Ob die Ergebniszellen verbunden sind oder nicht, spielt hier keine Rolle.
Gruß Werner
.. , - ...
so leid mir's tut, aber
07.08.2016 19:10:43
MB12
... es funktioniert nur in dem Spaltenbereich AB:AF, also in KW 33.
In KW 31 und 32 kommt #Bezug, in KW 34 ist das Ergebnis 88 anstatt 40.
Auch wenn ich hier
SUMMENPRODUKT((D8:D69=D7)*(0&WECHSELN(LINKS(AB8:AF69;1)
D9 statt D8 und AB9 statt AB8 einsetze, also:
SUMMENPRODUKT((D9:D69=D7)*(0&WECHSELN(LINKS(AB9:AF69;1)
ändert sich nichts. Du merkst, ich fische nach Strohhalmen.
Falls du keine Lust/Zeit mehr haben solltest - es ist kein Beinbruch, denn ich habe ja eine funktionierende Formel.
Trotzdem hier mit deiner Formel:
https://www.herber.de/bbs/user/107458.xlsm
Liebe Grüße, Margarete
Anzeige
AW: die Dollars hatte ich bei Dir vermutet ;-) ...
07.08.2016 20:18:42
...
Hallo Margarete,
... in AB7 also:
=WENN(ZÄHLENWENN(AB8:AF8;"a")>2;"abwesend";SUMMENPRODUKT(($D9:$D69=$D7)*(0&WECHSELN(LINKS(AB9:AF69;1) ; "a";0)))) 

Diese Formel kannst Du auch nach rechts und links kopieren. Bis Morgen denne und noch einen schönen Restsonntag.
Gruß Werner
.. , - ...
DANKE SCHÖN - kennst du das....
07.08.2016 20:51:24
MB12
, dass man den Wald vor lauter Bäumen nicht mehr sieht?
Nachdem ich mich fast das gesamte Wochenende mit dieser schööönen Datei vergnügt hatte, hättest du mich wohl mit dem Kopf darauf stoßen müssen!
Ganz lieben Dank nochmal
Margarete
Anzeige
AW: natürlich, kenne ich, wer kennt das nicht owT
08.08.2016 07:34:01
...
Gruß Werner
.. , - ...
ausWECHSELN() Verständnisfrage
08.08.2016 09:33:57
MB12
Hallo Werner:
die Funktion WECHSELN verstehe ich grundsätzlich (und dass es funktioniert)
Was bewirkt aber in dieser Formel ...0&WECHSELN.. ? (Verkettung?)
Du kennst mich soweit: Ich versuche immer zu verstehen, was ich so anstelle :-)
Gruß aus dem Büro, Margarete
AW: naja ...
08.08.2016 16:28:38
...
Hallo Margarete,
... ich kenne mich manchmal selbst nicht, wie soll ich da andere bzw. Dich kennen ;-)
Also, die Antwort auf Deine Frage ist einfach: Ja, es ist eine Verkettung. Damit bist Du aber sicherlich nicht zufrieden, oder?
Ich versuche nun, Dir zu helfen, die Antwort auf Deine Frage selbst zu finden. Lösche mal aus der Formel "0&". Was passiert danach? Warum? Schon etwas mehr Licht im Dunkel? Nein? Wie sieht denn die Teilergebnismatrix ohne die Verkettung und mit der Verkettung aus? Unterschied erkannt? Dann sollte alles klar und licht sein.
Ich bin mir sicher, dass wenn Du meine Fragen beantworten konntest, Dir mehr geholfen war, als wenn ich es jetzt "vorgekaut" hätte. Oder? Wenn nicht, dann trifft meine eingangs getroffene Aussage zu und Du hast eine weitere Frage gut ;-)
Gruß Werner
.. , - ...
Anzeige
weitere Frage "gut", aber andere
08.08.2016 18:48:22
MB12
Hallo Werner,
hatte nachgefragt, da ich unter der Woche berufsbedingt eigentlich keine Zeit für mein Privatvergnügen "verstehen“ habe. Aber jetzt hast mi.
Also: 0& bewirkt, dass der Zellinhalt als Zahl interpretiert wird, da mit der Funktion WECHSELN() sonst die Null am Ende auch Text wäre. So in etwa korrekt?
Aber: wenn ich deine Formel hernehme, kann ich in diesem Fall das "links" rausnehmen, da pro Zelle immer nur ein "a" drin ist. Richtig?
Also anstatt
=WENN(ZÄHLENWENN(AB8:AF8;"a")>2;"abwesend";SUMMENPRODUKT((D9:D69=D7)*(0&WECHSELN(LINKS(AB9:AF69;1) ; "a";0))))
einfach
=WENN(ZÄHLENWENN(W8:AA8;"a")>2;"abwesend";SUMMENPRODUKT(($D9:$D69=$D7)*(0&WECHSELN(W9:AA69; "a";0) )))
Habs in meiner Datei getestet, und bisher funktioniert es. Kann ich damit unter bestimmten Bedingungen in einen Fehler laufen ? Umgekehrt gefragt: In welchen Fällen wäre das LINKS erforderlich bzw. sinnvoll?
Könntest du das als "eine" Frage laufen lassen?
Liebe Grüße, Margarete
Anzeige
Teste das doch mal mit der entscheidenden ...
08.08.2016 22:44:07
Luc:-?
…Matrix, Margarete!
{=1*(0&WECHSELN(LINKS(AB9:AF69);"a";0))}
Und dann lass' mal 0& oder LINKS( bzw beides weg… ;-]
Dann fällt dir evtl auf, dass LeerZellen im Bereich vorkommen, die durch WECHSELN zu LeerText wdn. Außerdem können im Bereich ja auch noch andere Texte vorkommen, zB abwesend
Gruß, Luc :-?
AW: ergänzend hierzu vielleicht noch ...
09.08.2016 08:40:45
...
Hallo Margarete,
... die Formel ist natürlich nur dafür ausgelegt, dass als Texte nur "a" im auszuwertenden Bereich eingeben werden (am besten mittels Datengültigkeit realisieren) und weiterhin der Text "abwesend" durch die Formelauswertung entstehen kann.
Gruß Werner
.. , - ...
Anzeige
Ja, denn andere Texte (nicht mit a als 1.Bst) …
09.08.2016 15:44:19
Luc:-?
…würden ebenfalls zu einem Fehler führen, Margarete.
Luc :-?
habe verstanden
09.08.2016 17:18:51
MB12
Hallo Ihr Lieben,
dann habe ich es doch richtig verstanden, denn das "abwesend" ist in Spalte 8, und nachdem ich in der Formel anstatt (AB8:AF69;1)nun(AB9:AF69;1)einsetze (vorne natürlich entsprechend), kann es mich hier nicht ärgern.
Aber es ist gut zu wissen, was in einem ähnlichen Fall geschehen muss und wo der Hund begraben ist.
Habe die Beispieldatei heute im Team vorgestellt, fand große Anerkennung. Das habe ich nun davon - andere Abteilungen wollen Ähnliches haben, aber natürlich alle mit Sonderwünschen.
Habt herzlichen Dank -natürlich auch René.
Schönen Abend noch
Margarete
'abwesend' kann aber weiter unten auch mal ...
09.08.2016 19:20:44
Luc:-?
…auftreten, Margarete,
nicht nur in Zeile 8! Die müsstest du dann auch alle herausnehmen, was zu einem unzu­sammen­hängenden Bereich führen und zumindest die Fml verlängern würde.
Luc :-?
SUMMENPRODUKT()-jetzt will ich's genau wissen
09.08.2016 20:07:54
MB12
Hallo Luc, Werner (@alle),
zwar habe ich bei Kalkulationen seit Jahren Summenprodukt genutzt, mir aber nie Gedanken darüber gemacht – Ihr seid schuld daran (grins).
Erst hab ich versucht, mich schlau zu machen und dann alles "auseinanderklamüsert"
Schaut euch bitte meine Tabelle an:
https://www.herber.de/bbs/user/107512.xlsx
da bleibt mir nämlich ein ganz großes Fragezeichen.
Luc, bitte mein Excel-Niveau beachten
Liebe Grüße zum Abend, Margarete
Das ist ja wohl eine Untertreibung! ;-) owT
09.08.2016 21:04:47
Luc:-?
:-?
AW: das ehrt Dich ...
10.08.2016 08:45:51
...
Guten Morgen Margarete,
... und ich muss wohl jetzt Farbe bekennen. Sorry, ich hatte meine SUMMENPRODUKT()-Formel unsauber konstruiert, d.h. eigentlich hatte ich diese nur schnell zusammengeschustert, sorry nochmal :-(
Diese Formel liefert zwar in Deiner Beispieldatei ein korrektes Ergebnis. Sie ist aber nur für Arbeitszeiten kleiner 10h je Tag ausgelegt (was ja an sich im Normalfall auch reichen sollte).
Die Formel ergibt bei Textwerten "abcd" oder "a" oder "'00" aber keinen Fehlerwert, weil immer nur das erste Zeichen vorgenannter Texte als 0 ausgewertet wird aber eben dadurch auch nur die erste Ziffer einer Zahl. Diese Formel hätte allerdings in Deinem neuen Beispiel nicht: =SUMMENPRODUKT((D9:D69=D7)*(0&WECHSELN(LINKS(E9:I69;1); "a";0))) sondern "richtig" : SUMMENPRODUKT((D8:D69=D7)*(0&WECHSELN(LINKS(H8:L69;1); "a";0))) lauten sollen.
Doch wirklich richtig und sowohl allgemeiner (es werden auch Zeiten größer 9h berücksichtigt) als auch einfacher und kürzer wäre folgende Formel:
=SUMMENPRODUKT((D8:D69=D7)*ISTZAHL(H8:L69);H8:L69)
Gruß Werner
.. , - ...
Danke&Danke..
10.08.2016 16:54:40
MB12
Hallo Werner,
das erste Danke für die Erklärung, das zweite Danke für die geänderte Formel, denn es kommen durchaus Zeiten bis ca 10 h vor, wenn Überstunden angeordnet sind. Werde es gleich morgen früh testen :-)
Gruß aus HoLoh'
Margarete
funktioniert - und ein Rattenschwanz
12.08.2016 11:16:43
MB12
Hallo Werner,
jetzt musste ich mich natürlich auch mit ISTZAHL() usw. beschäftigen.
Das gab einige "oha's" und "nee, nicht wirklich".
Die Formatierung ist bei uns alltägliches Thema, und ich habe im Verlauf dieses Threads mindestens drei neue Möglichkeiten kennengelernt (Die "Singsel" sind dabei auch eine große Hilfe).
Bis zum nächsten Thread (die Aufgaben habe ich bereits - autsch'n)
LG, Margarete
AW: wie funktioniert ein Rattenschwanz? ;-) owT
12.08.2016 12:14:02
...
Gruß Werner
.. , - ...

300 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige