Live-Forum - Die aktuellen Beiträge
Datum
Titel
16.04.2024 15:29:29
Notizen einheitliche Position, Formatierung per Bezugsspalte
16.04.2024 15:16:53
Summen mit verschiedenen Variablen
16.04.2024 14:29:30
Matrixkonstanten als Quelle für Gültigkeit und Steuerelement
16.04.2024 13:54:06
Multiplikation
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1440to1444
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
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1440to1444
1440to1444
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

suche Vereinfachung

suche Vereinfachung
09.08.2015 21:26:59
Thomas
Hallo Excelfreunde,
ich bin auf der suche nach einer verbesserung meine Lösung.
Ich möchte gern eine monatliche Auswertung je Kunde erstellen.Dazu habe ich schon mal meine Idee umgesetzt. Diese erscheint mir aber zu kompliziert. Womöglich hatt jemand eine bessere idee. Es sind so zusagen drei ebenen die ich zusammenziehen muss. Dazu benutze ich zur Zeit eine SUMMEWENNS Formel. Diese wird aber sehr unübersichtlich.
Ist es möglich innerhalb dieser formel mit definierten Bereichen zu arbeiten? Dies bekomme ich leider nicht hin. Dann würde Sie womöglich übersichtlicher werden.
Desweiteren stört mich die lösung in B 1 und F1 ( Monatsangabe) dies ist nicht sehr Flexible. Gern nehme ich auch völlig andere Ideen entgegen.
Kann sich dies mal bitte jemand anschauen? Die eindeutige liste möchte ich zur zeit mit VBA erstellen denn ich finde leider keine händelbare lösung über drei Spalten.
Das maximale was ich gefunden habe geht über zwei Spalten.
https://www.herber.de/bbs/user/99448.xlsm
Liebe grüsse thomas

13
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: suche Vereinfachung
09.08.2015 22:43:54
Sepp
Hallo Thomas,
keine Hilfe meinerseits, nur ein Hinweis.
Wenn man eine Datentabelle so aufbaut wie deine, dann braucht man sich nicht wundern, wenn die Auswertung zum Kampf wird.
Gruß Sepp

Ich weis aber
10.08.2015 04:57:57
Thomas
Hallo
ich weis das dies nicht die optimale Situation ist. Hinter diesem problem stecken aber auch sehr viele
positive sachen. Und auch unveränderliche Sachen. Ich benötige einige sachen einfach in einer Zeile (zB. die Spaltenüberschriften) sonst bekomme ich an anderer Stelle noch grössere Probleme.
deshalb nehme ich dieses problem erstmal so hin und versuche es zu lösen.
Das datumsproblem konnte ich schon lösen.
Das nächste Problem wäre dann ob man in dieser Formel
=SUMMEWENNS(hauptdaten!D$2:D15;hauptdaten!$C$2:$C15;$B8;hauptdaten!$A$2:$A15;$E$1;hauptdaten!$A$2:$A15;$E$2)
definierte Bereiche benutzen kann. Meine versuche schlagen immer fehl.
https://www.herber.de/bbs/user/99449.xlsm
mit definierten Bereichen arbeiten kann.
liebe grüsse thomas

Anzeige
Du kannst mit definierten Bereichen rechnen ....
10.08.2015 08:27:14
der
Hallo Thomas,
... allerdings nur, wenn die Bereiche gleich groß definiert sind. Deine Definition für z.B.: Namen hat eine andere Größe als die, die Du für Betrag1 etc. festgelegt hast. Außerdem stimmt Deine Bereichszuordnung für Betrag# nicht mit Deinen Daten in der Datei überein.
Bei korrekter Definition der Bereichsnamen lautet die Formel D8 z.B. so: =SUMMEWENNS(Betrag1;Namen;$B8;Fälligkeit;$E$1;Fälligkeit;$E$2).
Allerdings sehe ich momentan keine zwingende Notwendigkeit mit derartigen Bereichsnamen zu arbeiten. Wenn schon sollte man diese zusammenfassen und auch dynamisieren. Aber das wäre eine andere Baustelle.
Unabhängig davon kann ich nur noch mal die Aussage von Sepp bekräftigen. Eine gute Datenstruktur vereinfacht jegliche Auswertung. Z.B. wäre bei einer einer anderen Datenstruktur die von Dir gewünschten Auswertung ein Kinderspiel. Mit nur wenigen Mausklicks und gänzlich ohne Formeln, hättest Du eine sehr flexible wie umfassende und leicht anpassbare Auswertung für Deine Daten erhalten. Stichwort: PIVOTauswertung.
Gruß Werner
.. , - ...

Anzeige
AW: Du kannst mit definierten Bereichen rechnen ....
10.08.2015 10:08:20
Thomas
Hallo Werner und Sepp,
vielen dank für die tipps.Mein Fehler waren die unterschiedlichen Grössen von den Bereichen.
Kannst Du mir das an ein beispiel zeigen wie du das mit dem zusammenfassen und Dynamisieren meinst?
Ich glaube das es super sein würde wenn die bereiche nach unten Dynamisch sein würden. Zusammenfassen
kenne ich nur indem man einzelne spalten gemeinsam markiert und dann definiert. Wenn ich es aber in der Summewennsformel mache bekomme ich wieder den fehler.
Es ist ja nicht so das ich das nicht einsehe, das eine andere Strucktur besser wäre aber selbst wenn ich wollte ich könnte Sie nicht ändern.
Ich hoffe nicht das ich aus diesem grund jetzt keine hilfe zu diesm problem erhalte.
https://www.herber.de/bbs/user/99458.xlsm
liebe grüsse thomas

Anzeige
flexibel mit nur 2 definierten Bereichsnamen ...
10.08.2015 12:20:40
...
Hallo Thomas,
... und mit Dropdwonzellen in F6 sowie B7; D7:F7 sowie einer neuen Formel in D8. Diese ist nach rechts und unten ziehend kopierbar. Mittels dieser Formel auf Basis nur zweier notwendiger Bereichsnamen und im Zusammenspiel mit den Dropdownzellen ist eine kleine individuelle "PIVOTartige" Auswertung Deiner Daten entstanden:
 BCDEF
6    2011
7Kunde 3 Betrag10Betrag1Januar
8Lehmann        580,00 €       380,00 €       960,00 €
9Meyer                  -   €                 -   €                 -   €
10 Müller         250,00 €       150,00 €       400,00 €
11Name2                  -   €                 -   €                 -   €
12name3                  -   €                 -   €                 -   €
13 Name4                   -   €                 -   €                 -   €
14 Name5         340,00 €       240,00 €       580,00 €
15Schmidt        260,00 €       160,00 €       420,00 €
16         880,00 €       580,00 €    1.460,00 €
17     

Formeln der Tabelle
ZelleFormel
D8=Wert
F8=SUMME(C8:E8)
Namen in Formeln
ZelleNameBezieht sich auf
D8Datenliste=hauptdaten!$A$2:INDEX(hauptdaten!$Z:$Z;VERWEIS(9^9;hauptdaten!$A:$A;ZEILE(hauptdaten!$A:$A)))
D8Wert=SUMME(INDEX(Datenliste;;VERGLEICH(Auswertung!D$7;hauptdaten!$A$1:$Z$1;))*(INDEX(Datenliste;;VERGLEICH(Auswertung!$B$7;hauptdaten!$A$1:$Z$1;))=Auswertung!$B8)*(INDEX(Datenliste;;1)>=(Auswertung!$F$7&Auswertung!$F$6)*1)*(INDEX(Datenliste;;1)<=MONATSENDE((Auswertung!$F$7&Auswertung!$F$6)*1;0)))
Namen verstehen

Datengültigkeit der Tabelle
ZelleZulassenDatenEingabe 1Eingabe 2
F6Liste 2011;2012;2013;2014;2015,2016 
B7Liste Kunde 1;Kunde 2;Kunde 3 
D7Liste Betrag1;Betrag2;Betrag10 
E7Liste Betrag1;Betrag2;Betrag10 
F7Liste Januar;Februar;März;April;Mai;Juni;Juli;August;September;Oktober;November;Dezember 


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...

Anzeige
man mir bleibt die Luft weg
10.08.2015 14:42:28
Thomas
hallo Werner,
hab vielen vielen dank. So etwas habe ich nie erträumt.
Nur ich bekomme es nicht in gang. Ich habe immer #NV. Habe bestimmt irgent ein dummen fehler gemacht.
auch vielen vielen dank für die viele Zeit die du schon investiert hast.
Kannst du mal nach schauen?
liebe grüsse Thomas
https://www.herber.de/bbs/user/99467.xlsm

Zuordnung der Namensdefinit. zum Tabellenblatt ...
10.08.2015 14:58:33
...
Hallo Thomas,
... muss natürlich beibehalten werden.
Die beiden Bereichsnamen sind für die Tabelle namens "Auswertung" zugeordnet und nicht für "Werner". Wenn Du das noch beachtest, dann steht der Lösung nichts mehr entgegen.
Gruß Werner
.. , - ...

Anzeige
vielen vielen dank ... neopa C
10.08.2015 15:56:30
Thomas
Hallo Werner,
du baust mir so eine Sache und dann nerve ich totzdem. Es klappt riesig und ist schnell wie ich es nie erwartet hätte. Oh man.
Eine Frage geht es auch wenn ich drei Monate darstellen möchte dann kopiere ich es dreimal und nenne g8 Wert1 ( oder wert+1)und Datenliste1 unsw. und passe "Bezieht sich auf" im Namensmanager an. Oder muss ich hier auch noch ran (9^9;hauptdaten!$A:$A"). Muss ich noch etwas beachten?
An dem falschen Blattnamen sieht man ja was mir so alles passiert.
Ich bin total stolz das du mir dies gebaut hast. Und ich bin bestimmt kein "Hintern-kriecher" aber dies ist Kunst.
Und glaub das nächste mal frage ich erst bevor ich eine Tabelle aufbaue.
vielen vielen dank.

Anzeige
wenn so eine Auswertung auch möglich sein soll ...
10.08.2015 16:12:05
...
Hallo Thomas,
... dann würde ich vorschlagen, eine weitere Dropdownzelle vorzusehen und zwar z.B. eine Kopie von F7. Angenommen diese steht dann z.B. in F5 dann könnte man diese als "Beginnmonat" deklarieren und F7 als "Endemonat". Möglich wäre alternative dazu auch eine zusätzliche Zelle, in der die Anzahl der auszuwertenden Monate von oder bis dem gewählten Monat in F7 als Ganzzahl einzugeben ist.
Für was entscheidest Du Dich?
Nur die Bereichsnamensdefinition von "Wert" ist entsprechend dann anzupassen und Du hast eine noch flexiblere Auswertungsmöglichkeit. Willst Du es zunächst allein versuchen oder braucht Du dazu nochmal Hilfe?
Gruß Werner
.. , - ...

Anzeige
AW: wenn so eine Auswertung auch möglich sein soll ...
10.08.2015 16:59:59
Thomas
Hallo Werner,
es wäre super wenn du dabei noch mal hilfst. Ich glaube ich werde scheitern denn so etwas habe ich noch nie gemacht. Ich wüsste nicht mal den Anfang. Die Möglichkeiten sind beide gut vieleicht die erste wenn Sie sich am besten umsetzen lässt von Monat bis Monat.
hab vielen vielen dank
liebe grüsse thomas

fast eine kleine individuelle "Pivot"auswertung...
10.08.2015 19:43:47
der
Hallo Thomas,
... allerdings bedingt durch durch die Formel basierende Auswertung natürlich viel langsamer als eine echte PIVOTauswertung.
Ich hab die Dropdownzellen für die Datumswerte etwas anders angeordnet und noch eine 2. Dropdownzelle (in C5) für das "Ende"Jahr mit dazu genommen, damit auch über den Jahreswechsel hinaus ausgewertet werden kann und angedeutet, dass Du auch weitere "Beträge" in die Auswertung einbeziehen kannst.
Eine Formelanpassung ist lediglich für den Bereichsnamen Wert notwendig. Die Formel =Wert einfach entsprechend kopieren:
Auswertung

 ABCDEFGH
3A u s w e r t u n g
4von:Januar2011     
5bis:Februar2011     
6        
7 Kunde 1 Betrag10Betrag1 Betrag6Gesamt
8 Lehmann   1.306,00 €     900,00 €       286,00 €   2.492,00 €
9 Meyer      600,00 €     400,00 €       160,00 €   1.000,00 €
10  Müller    1.000,00 €     600,00 €       284,00 €   1.600,00 €
11 Name2               -   €              -   €                -   €                -   €
12 name3               -   €              -   €                -   €                -   €
13  Name4       680,00 €     480,00 €       140,00 €   1.160,00 €
14  Name5       360,00 €     160,00 €       180,00 €       520,00 €
15 Schmidt      340,00 €     240,00 €         71,00 €       580,00 €
16        340,00 €     240,00 €         71,00 €       580,00 €

Formeln der Tabelle
ZelleFormel
C5=C4
D8=Wert
H8=SUMME(C8:G8)
Namen in Formeln
ZelleNameBezieht sich auf
D8Datenliste=hauptdaten!$A$2:INDEX(hauptdaten!$Z:$Z;VERWEIS(9^9;hauptdaten!$A:$A;ZEILE(hauptdaten!$A:$A)))
D8Wert=WENNFEHLER(SUMME(INDEX(Datenliste;;VERGLEICH(Auswertung!D$7;hauptdaten!$A$1:$Z$1;))*(INDEX(Datenliste;;VERGLEICH(Auswertung!$B$7;hauptdaten!$A$1:$Z$1;))=Auswertung!$B8)*(INDEX(Datenliste;;1)>=(Auswertung!$B$4&Auswertung!$C$4)*1)*(INDEX(Datenliste;;1)<=MONATSENDE((Auswertung!$B$5&Auswertung!$C$5)*1;0)));"")
Namen verstehen

Datengültigkeit der Tabelle
ZelleZulassenDatenEingabe 1Eingabe 2
B4Liste Januar;Februar;März;April;Mai;Juni;Juli;August;September;Oktober;November;Dezember   
C4Liste  2011;2012;2013;2014;2015,2016 
B5Liste Januar;Februar;März;April;Mai;Juni;Juli;August;September;Oktober;November;Dezember   
C5Liste  2011;2012;2013;2014;2015,2016 
B7Liste Kunde 1;Kunde 2;Kunde 3 
D7Liste  Betrag1;Betrag2;Betrag4;Betrag6;Betrag10 
E7Liste  Betrag1;Betrag2;Betrag4;Betrag6;Betrag10 
F7Liste  Betrag1;Betrag2;Betrag4;Betrag6;Betrag10 
G7Liste  Betrag1;Betrag2;Betrag4;Betrag6;Betrag10 


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Falls nur ein Monat ausgewertet werden soll, können die Zellen A4;A5:C5 durch bedingte Formatierung "ausgeblendet" (weiße Schrift auf weißen Hintergrund) werden (bed. Formatierungsformel: =($B$4=$B$5)*($C$4=$C$5) für diese Zellen).
Gruß Werner
.. , - ...

Anzeige
fast? es ist eine vielen dank neopa C
10.08.2015 20:28:22
Thomas
Hallo neopa,
ich weiss das ich bestimmt schon oft vielen dank gesagt habe. Mir egal ich sage es noch mal.
Du hast mir riesig geholfen. Das mit der eindeutigen Liste ( kunden ) habe ich auch schon im griff ich werde noch es noch heute in die hauptdatei einbauen. das war glaube ich meine letzte grosse Baustelle. ( hoffe ich).
Werner bleib wie du bist, bestimmt lesen wir wieder voneinander. Es hatt auch riesen spass gemacht
liebe liebe grüsse thomas ( es ist nicht zu langsam es ist super )

gern getan ... und ... bemühe mich weiter! owT
11.08.2015 08:51:18
...
Gruß Werner
.. , - ...

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige