Autor
|
Thema: Zählenwenn mit dynamischer range? (4717 mal gelesen)
|
PimpMyExcelSheet Mitglied
Beiträge: 6 Registriert: 08.02.2007
|
erstellt am: 08. Feb. 2007 14:42 <-- editieren / zitieren --> Unities abgeben:
Hallo Leute! Erstmal möchte ich euch ein riesen Lob für dieses Forum aussprechen! Konnte so gut wie all meine Probleme mit Hilfe eurer Beiträge beseitigen. Jetzt habe ich aber noch ein großes Problem. Ich möchte in einem Tabellenblatt (Mitarbeiterübersicht) die Arbeitszeiten von allen Mitarbeitern erfassen, die in allen anderen Tabellenblättern (dabei handelt es sich um verschiedene Arbeitsschritte) vorkommen. Bis jetzt traf das nur auf ein weiteres Tabellenblatt zu. Somit hatte ich auch kein Problem mit der range. (Der Kriterienbereich stellt kein Problem dar) Wie geht das aber, wenn ich die Mitarbeiterübersicht aktualisieren will (ohne die Zählenwenn-Formel von Hand zu ändern), sobald ein neues Blatt mit neuen Arbeitszeiten hinzugefügt wird. Ich nehme an, dass ich da um VBA nicht herum kommen werde, aber ich habe leider keine Ahnung wie ich das angehen soll. Vielleicht könnt ihr mir bei der Lösung helfen. lg PMES Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP |
Paulchen Mitglied Bauing./SW-Entwickler
Beiträge: 1227 Registriert: 19.08.2004 Büro: Win10 Enterprise 64bit, Office Professional Plus 2013 - Privat: Linux Mint 15, LibreOffice
|
erstellt am: 08. Feb. 2007 14:56 <-- editieren / zitieren --> Unities abgeben: Nur für PimpMyExcelSheet
Hallo PMES, willkommen, willkommen! So auf die Schnelle: Hast Du diesen Beitrag schon entdeckt [Suche nach "dynamisch" hier im Excel-Forum]? Schau mal ganz unten nach , da ist ein weiterer link. Vielleicht ist da was dabei... Generell kann ich ansonsten Deiner "Befürchtung" recht geben: Wenn Du ein neues Blatt einfügst, ist es mit Bordmitteln wohl vorbei - dann ist VBA angesagt; auch DAS ist aber kein Hexenwerk! Gruß, Frederik Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP |
PimpMyExcelSheet Mitglied
Beiträge: 6 Registriert: 08.02.2007
|
erstellt am: 08. Feb. 2007 15:43 <-- editieren / zitieren --> Unities abgeben:
Danke für deine Antwort Paulchen! War aber nicht ganz das was ich suche. Das Hinzufügen von Tabellenblättern habe ich schon realisiert. Die aktuelle Formel der Mitarbeiterübersicht sieht wie folgt aus: =SUMIF(Beispielprozess!$C$23:$C91;MA_Übersicht!$A4:$A23;Beispielprozess!$G$23:$G91) Wenn jetzt ein neues Blatt mit Namen "Test" hinzugefügt wird, sollte die Formel nach Klick auf einen "aktualisieren"-button adaptiert werden. =erste SUMIF + SUMIF(Test! ...usw) (ist das so sinnvoll? oder gibt es da eine andere Möglichkeit?) Nur, wie geht das? Die Range ansich kann sich natürlich auch ändern. Wie ich es schaffe dass die auch automatisch erfasst wird, weiß ich noch nicht. Sollte sich aber über Schleifen und Suchwörtern bewerkstelligen lassen. Gehen mein Überlegungen soweit in die richtige Richtung? lg PMES Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP |
Paulchen Mitglied Bauing./SW-Entwickler
Beiträge: 1227 Registriert: 19.08.2004 Büro: Win10 Enterprise 64bit, Office Professional Plus 2013 - Privat: Linux Mint 15, LibreOffice
|
erstellt am: 08. Feb. 2007 17:23 <-- editieren / zitieren --> Unities abgeben: Nur für PimpMyExcelSheet
Zitat: ist das so sinnvoll? oder gibt es da eine andere Möglichkeit?
Hm - ohne VBA fällt mir gerade keine ein . Wie oft kommt es denn vor, daß Du ein neues Blatt einfügst und die Formel umbauen/erweitern mußt? 1x pro Woche oder 1x pro Jahr? Bei 1x jährlich könnte ich damit leben; Du hättest -bei händischer Pflege- zwar jedesmal die "Umstände" - dafür aber auch zeitgleich 'ne Kontrollmöglichkeit, ob Dein Bereich paßt usw. Der Aufwand mit VBA wäre wohl vertretbar; "automatische" Erfassung vom Range würde ich allerdings zunächst mal hinten anstellen... Gruß, Frederik Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP |
Thomas Harmening Moderator Arbeiter ツ
Beiträge: 2897 Registriert: 06.07.2001 Das Innerste geäussert und aufs Äusserste verinnerlicht
|
erstellt am: 08. Feb. 2007 19:33 <-- editieren / zitieren --> Unities abgeben: Nur für PimpMyExcelSheet
Zitat: Original erstellt von PimpMyExcelSheet: Die aktuelle Formel der Mitarbeiterübersicht sieht wie folgt aus: =SUMIF(Beispielprozess!$C$23:$C91;MA_Übersicht!$A4:$A23;Beispielprozess!$G$23:$G91)
hmm, die obige Formel ist aber nicht Zählenwenn... und MA_Übersicht!$A4:$A23 irritiert mich auch ein wenig - das Kriterium ist doch erstmal nur ein Kriterium und nicht ein Rangebereich - oder? {=SUM(SUMIF(Beispielprozess!$C$23:$C91;MA_Übersicht!A4:A32;Beispielprozess!$G$23:$G91))} würde wieder Sinn machen ;-) Summieren über mehrere Bereiche würde zb. so gehen: =SUMME(Anfang:Ende!A1:A2) Alles was inc. der beiden Tabellen und dazwischen liegt wird summiert... man kann auch so Summieren, Tabellen heissen AA BB CC DD =SUMMENPRODUKT((T(INDIREKT("'"&{"AA"."BB"."CC"."DD"}&"'!A"&ZEILE(1:100)))="x")*(N(INDIREKT("'"&{"AA"."BB"."CC"."DD"}&"'!C"&ZEILE(1:100))))) aber - im Prinzip habe ich keinen Schimmer, wie deine Tabelle aufgebaut ist und was nun wirklich möglich wäre ;-) ...so ins Blaue mag ich heute nicht so ;-) ist ja auch schon Dunkel Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP |
Paulchen Mitglied Bauing./SW-Entwickler
Beiträge: 1227 Registriert: 19.08.2004 Büro: Win10 Enterprise 64bit, Office Professional Plus 2013 - Privat: Linux Mint 15, LibreOffice
|
erstellt am: 09. Feb. 2007 09:25 <-- editieren / zitieren --> Unities abgeben: Nur für PimpMyExcelSheet
Tach auch, Zitat: und MA_Übersicht!$A4:$A23 irritiert mich auch ein wenig - das Kriterium ist doch erstmal nur ein Kriterium und nicht ein Rangebereich - oder?
So hatte ich das bisher auch verstanden - deshalb wollte ich erstmal abwarten, was noch so kommt . Zitat: Summieren über mehrere Bereiche würde zb. so gehen: =SUMME(Anfang:Ende!A1:A2) Alles was inc. der beiden Tabellen und dazwischen liegt wird summiert...
JUHUUUU!!! Wieder was gelernt! Und es ist tatsächlich so: Wenn Du nachträglich eine Tabelle einfügst, die zwischen Register 'Anfang' und 'Ende' steht, wird sie automatisch berücksichtigt. Verschiebst Du sie ganz ans Ende, also hinter 'Ende', fällt sie aus der Berechnung 'raus. Tststs... Der Thomas wieder, immer für was Neues gut. Faszinierend, daß Du trotz Dunkelheit nicht schwarz, sondern blau siehst . Gruß, Frederik Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP |
PimpMyExcelSheet Mitglied
Beiträge: 6 Registriert: 08.02.2007
|
erstellt am: 09. Feb. 2007 15:17 <-- editieren / zitieren --> Unities abgeben:
Seas! Antwort vom Chef persönlich, nett nett Aber das funktioniert für mich nicht wirklich. (außer ich kann die Formel nicht richtig anwenden *g*) Anbei ein Beispiel meiner verwendeten Excel-Datei. Vielleicht ist es dann etwas klarer. Ich brauche das Kriterium um für jeden Mitarbeiter in der MA_Übersicht die richtige Gesamtarbeitszeit auszurechnen. Sprich Spalte A in Prozess enthält eine Teilmenge der Mitarbeiter, die Spalte A in MA_Übersicht enthält alle Mitarbeiter, und die Spalte B von Prozess enthält die Arbeitszeiten. (soviel zum SUMIF-Aufbau) Angenommen Prozess2 wäre nun hinzugefügt worden. Wie kann ich dann diese Zeiten ohne viel Handarbeit in die Übersicht mit einbeziehen. Wie oft Prozesse bzw. neue Tabellenblätter hinzugefügt werden, weiß ich jetzt nicht genau, aber es wird sicher öfters als 10mal im Jahr sein. Daher sollte das Ganze so gut es geht automatisiert ablaufen. lg, vielen Dank für die bisherigen Antworten! PMES Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP |
Paulchen Mitglied Bauing./SW-Entwickler
Beiträge: 1227 Registriert: 19.08.2004 Büro: Win10 Enterprise 64bit, Office Professional Plus 2013 - Privat: Linux Mint 15, LibreOffice
|
erstellt am: 09. Feb. 2007 15:51 <-- editieren / zitieren --> Unities abgeben: Nur für PimpMyExcelSheet
Zitat: Angenommen Prozess2 wäre nun hinzugefügt worden. Wie kann ich dann diese Zeiten ohne viel Handarbeit in die Übersicht mit einbeziehen.
Hmm... viel ist ein relativer Begriff. Eine nicht sehr elegante, dafür leicht nachvollziehbare Lösung: Kopiere Dir Deine Summewenn-Formel in eine Spalte nebendran auf der MA_Übersicht. Dann markierst Du die Zellen mit den Kopien und machst "Suchen und Ersetzen" [Strg + h]: 'Prozess1' -> 'Prozess2'. Formel wird angepaßt, Spalten lassen sich ausblenden, Du kannst bequem in der Übersicht Gesamtsummen etc. erstellen. Und jetzt kommt Thomas bestimmt mit irgend einer Mörderformel daher, die genau das auf einmal löst . Angst! Frederik Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP |
Thomas Harmening Moderator Arbeiter ツ
Beiträge: 2897 Registriert: 06.07.2001 Das Innerste geäussert und aufs Äusserste verinnerlicht
|
erstellt am: 09. Feb. 2007 20:27 <-- editieren / zitieren --> Unities abgeben: Nur für PimpMyExcelSheet
*ürgs* für das vom Chef persönlich - ich will mich nie so betrachten :-( so Markus;-) ist Pimpen nicht immer mit sehr viel Arbeit verbunden, die nicht immer leicht ist... *snip und back to XL* mit so einer BSP-Mappe kann man doch gleich viel mehr anfangen;-) Werte ala 10 zu vergeben ist ein äusserst unglücklicher Zustand, da eine Fehlersuche bei gleichlautenden Werten, doch arg frustrierend ist -welche der 10 ist nun falsch, ist sie überhaupt falsch?... Drum werden wir ein wenig abstrakt Prozess1 hat ungerade Mitarbeiter1-23 und die Zeiten sind 'Nummer des Mitarbeiters'*10 Prozess2 hat gerade Mitarbeiter1-12 und die Zahlen sind x+1 im Mitarbeiterblatt haben wir alle Mitarbeiter, egal ob gerade oder ungerade ;-) nun bauen wir die Formel und da wir nicht mit Vorgaben von 10 arbeiten, erkennen wir schnell ob die Formel Fehler hat, bzw. das richtige Ergebnis ausspuckt... Mitarbeiter mit geraden Nummern sollten also nur ihre eigene Nummer als Ergebnis bekommen bsp MA 12 = Ergebnis 12 und die Mitarbeiter mit ungeraden Nummer, ihre (eigene Nummer * 10)+ eigene Nummer Bsp. MA 23 = Ergebnis (23*10)+23 = 253 ist dem so, so arbeitet die Formel richtig und wir könnten und einen lauen Lenz machen und ins Blaue fahren, auch wenn es Dunkel ist ;-) ...meiner einer wäre mit der Formel noch nicht zufrieden, da sich Prozess1,Prozess2...Prozess10 ect. sicherlich auch noch dynamisch gestalten lässt aber Nomen est Omen - Pimpen ist immer mit viel Arbeit verbunden ;-) und es wäre ein frevel, wenn nun der Pimper so faul würde wie ein progger^^ drum' grübel ein wenig wie es gehen könnte :-) ungetestet würde ich mal INDIREKT("Prozess"&SPALTE(A:J)...in Erwägung ziehen Wobei A 1 entspricht und J10 ansonsten pimste halt die {"Prozess1"."Prozess2"} weiter um ."Prozess3"."Prozess4"."Prozess5"... die Blätter dürfen auch schon existieren, Nullwerte sollten nicht stören ;-) und das befüllen mit copy & paste^^ - das würde ungefähr dem >>ohne viel Handarbeit<< entsprechen ;-) dem Blauen Bereich wurde ein Name vergeben. - ist aber nicht notwändig , dachte ich könnte die Formel anders aufbauen und lasse die Formel sprechen... -- achja, es muss nicht jederman wissen wie man heisst und Xbox-HQ muss auch nicht jeder wissen ;-) Frederik, *so und nun gehe ich raus und erschrecke kleine Wesen, BUH, ich bin eine Mörderformel* *muhahah* ------------------ Am Anfang war kein Licht - und Vater blickte Kalt literaturcafe /// ¡darkerradio! // und den passenden Play für Winamp Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP |
PimpMyExcelSheet Mitglied
Beiträge: 6 Registriert: 08.02.2007
|
erstellt am: 09. Feb. 2007 22:02 <-- editieren / zitieren --> Unities abgeben:
|
Paulchen Mitglied Bauing./SW-Entwickler
Beiträge: 1227 Registriert: 19.08.2004 Büro: Win10 Enterprise 64bit, Office Professional Plus 2013 - Privat: Linux Mint 15, LibreOffice
|
erstellt am: 10. Feb. 2007 15:12 <-- editieren / zitieren --> Unities abgeben: Nur für PimpMyExcelSheet
Ich habe mal meine bescheidenen VBA-Kenntnisse eingesetzt... Weiß nicht, PMES, wie fit Du darin bist - deshalb hier mal ein ganz kurzer Crashkurs als 'Service des Hauses'. Mach folgendes: In Deiner Mappe drückst Du Alt + F11 (Alternativ: Extras-Makro-VisualBasicEditor). Dort fügst Du dann z.B. über Einfügen-Modul ein neues Modul ein. In dieses Modul gibst Du folgenden Code: Sub MitarbeiterZeiten()Dim maNr As String, Proz As Worksheet, i As Integer Dim n As Integer, ztNr As Single, Bereich As Range Application.ScreenUpdating = False i = 1 Set Bereich = Worksheets("MA_Übersicht").Range("A10").CurrentRegion 'A10 ist willkürlich gewählt! For n = 1 To Bereich.Rows.Count maNr = "MA " & i For Each Proz In Application.Worksheets If Not Proz.Name = "MA_Übersicht" Then On Error Resume Next 'in 'Prozess1' stehen nur ungerade MA-Nrn. - Fehlermeldung für nichtvorhandene gerade Nrn abfangen ztNr = Worksheets(Proz.Name).Cells.Find(What:=maNr, After:=ActiveCell, _ LookAt:=xlWhole).Offset(0, 1).Value + ztNr End If Next Worksheets("MA_Übersicht").Cells.Find(What:=maNr, After:=ActiveCell, _ LookAt:=xlWhole).Offset(0, 2).Value = ztNr i = 1 + i ztNr = 0 Next n Application.ScreenUpdating = True MsgBox "Werte übermittelt für " & Worksheets.Count - 1 & " Prozesse", vbOKOnly, "Meldung" End Sub
ein - Allerdings ist der hier mit Formatierungszeichen versehen; sinnvoller ist es, wenn Du Dir den Anhang runterlädst (Thomas Mappe als Vorlage mißbraucht). Deine Idee mit "Schleifen und Suchwörtern" war gar nicht so verkehrt .Ausführen kannst Du das ganze aus xls dann über Alt + F8 - MitarbeiterZeiten. Unter Office 2003 läuft die Sache, es dauert allerdings ein wenig; also Geduld(!), bis eine Mitteilung erscheint ;-). Wenn Dir die Bereiche nicht passen, mußt Du den Code entsprechend verändern. Die Stellen sind in obigem Code fett markiert worden. Derzeit wird in der Übersicht die Spalte C befüllt. Manko an meinem Makro: Die Mitarbeiter müssen in der Übersicht lückenlos aneinandergereiht sein; ebenso ist Voraussetzung, daß in allen 'Prozess'-Blättern 'Nummer' und 'Zeit' in aufeinanderfolgenden Spalten stehen. Diese Voraussetzungen sind sowohl in Deiner als auch in Thomas' Mappe sinnvollerweise eingehalten; ich denke, ein gewisses einheitliches Erscheinungsbild darf man schon annehmen?! Die ganze Sache ist mit Vorsicht zu genießen; die Sicherheitsstufe in xls u.U. anpassen, um Makros zu aktivieren; keine Garantie auf Vollständigkeit, kein Anspruch auf Richtigkeit. Ich habe zuhause mal eine Tabelle hinzugefügt, es klappt tadellos. Kurzes Feedback würde mich freuen. Ansonsten gilt: Bei Fragen - fragen . [thedit] Den Anhang sollte ich natürlich auch anhängen, ne? [/thedit] Gruß, Frederik [Diese Nachricht wurde von Paulchen am 10. Feb. 2007 editiert.] [Diese Nachricht wurde von Paulchen am 10. Feb. 2007 editiert.] Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP |
PimpMyExcelSheet Mitglied
Beiträge: 6 Registriert: 08.02.2007
|
erstellt am: 11. Feb. 2007 12:53 <-- editieren / zitieren --> Unities abgeben:
Leute ihr seid's echt der Hammer! Beide Lösungen sind für mich bzw. meine Anwendung zu gebrauchen! Ich wurde zwar anfangs aus dem Posting von Thomas nicht ganz schlau, aber mit etwas "learning by doing" hat dass dann auch geklappt. Die einfache Erweiterung und die automatische Aktualisierung der Übersicht haben mir besonders gut gefallen. Paulchen, an dich auch noch ein dickes Dankeschön für die VBA-Variante! Das Ausführen per Alt+F8 könnte ich dann noch mit einem Button "erleichtern" bzw. "sichtbar" gestalten. Jetzt habe ich quasi die Qual der Wahl. Nochmals vielen Dank für Eure schnelle und vor allem hochwertige Hilfe!! Vielleicht kann ich Euch ja auch einmal bei dem ein oder anderen Problem Hilfestellung leisten. Schönen Sonntag noch! lg, Markus
Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP |
PimpMyExcelSheet Mitglied
Beiträge: 6 Registriert: 08.02.2007
|
erstellt am: 11. Feb. 2007 13:30 <-- editieren / zitieren --> Unities abgeben:
|
Thomas Harmening Moderator Arbeiter ツ
Beiträge: 2897 Registriert: 06.07.2001 Das Innerste geäussert und aufs Äusserste verinnerlicht
|
erstellt am: 11. Feb. 2007 14:43 <-- editieren / zitieren --> Unities abgeben: Nur für PimpMyExcelSheet
Ja, eine sehr sinnvolle Frage und auch die Erste die ich so hier erst gelesen habe Im Moment ist so was nicht möglich, vielleicht in naher/ferner Zukunft, so was wurde von den Moderatoren schon mal als Vorlag eingeworfen. Als Moderator hat man selber die Möglichkeit beim Antworten, das Thema zu 'verschlagworten' nach Ermessen des Moderators in sinnige Fachbegriffe, mehr ist momentan nicht drinnen ;-) Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP |
runkelruebe Moderator Straßen- / Tiefbau
Beiträge: 8075 Registriert: 09.03.2006 MS-Office 365 ProPlus x86 WIN7(x64)
|
erstellt am: 11. Feb. 2007 18:21 <-- editieren / zitieren --> Unities abgeben: Nur für PimpMyExcelSheet
Tag zusammen, diese Möglichkeit hab ich zwar auch schon mehr als einmal vermißt, aber das zieht IMHO Schwierigkeiten mit den links in verschiedenen Übersichten nach sich, man denke nur an einen gelöschten Beitrag, da kommt auch so einiges durcheinander.. aber vielleicht doch nochmal bei Andreas hochschubsen. Was würdest Du denn noch für Schlagworte verteilen wollen? Wenn ich das richtig verstanden habe, findet Antonius alles, was in den Beiträgen steht. Fehlt da was? Ich hab mich damit allerdings auch nicht wirklich intensiv beschäftigt. [OT] Tja Chef den Ruf mit den Mörderformeln wirste wohl nicht wieder los [/OT] Schönen Sonntag noch! ------------------ Gruß, runkelruebe Herr Kann-ich-nich wohnt in der Will-ich-nich-Straße... Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP |
| Anzeige.:
Anzeige: (Infos zum Werbeplatz >>)
|