suche Vereinfachung

Bild

Betrifft: suche Vereinfachung
von: Thomas
Geschrieben am: 09.08.2015 21:26:59

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

Bild

Betrifft: AW: suche Vereinfachung
von: Sepp
Geschrieben am: 09.08.2015 22:43:54
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


Bild

Betrifft: Ich weis aber
von: Thomas
Geschrieben am: 10.08.2015 04:57:57
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

Bild

Betrifft: Du kannst mit definierten Bereichen rechnen ....
von: der neopa C
Geschrieben am: 10.08.2015 08:27:14
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
.. , - ...

Bild

Betrifft: AW: Du kannst mit definierten Bereichen rechnen ....
von: Thomas
Geschrieben am: 10.08.2015 10:08:20
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

Bild

Betrifft: flexibel mit nur 2 definierten Bereichsnamen ...
von: ... neopa C
Geschrieben am: 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
.. , - ...

Bild

Betrifft: man mir bleibt die Luft weg
von: Thomas
Geschrieben am: 10.08.2015 14:42:28
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

Bild

Betrifft: Zuordnung der Namensdefinit. zum Tabellenblatt ...
von: ... neopa C
Geschrieben am: 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
.. , - ...

Bild

Betrifft: vielen vielen dank ... neopa C
von: Thomas
Geschrieben am: 10.08.2015 15:56:30
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.

Bild

Betrifft: wenn so eine Auswertung auch möglich sein soll ...
von: ... neopa C
Geschrieben am: 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
.. , - ...

Bild

Betrifft: AW: wenn so eine Auswertung auch möglich sein soll ...
von: Thomas
Geschrieben am: 10.08.2015 16:59:59
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

Bild

Betrifft: fast eine kleine individuelle "Pivot"auswertung...
von: der neopa C
Geschrieben am: 10.08.2015 19:43:47
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
.. , - ...


Bild

Betrifft: fast? es ist eine vielen dank neopa C
von: Thomas
Geschrieben am: 10.08.2015 20:28:22
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 )

Bild

Betrifft: gern getan ... und ... bemühe mich weiter! owT
von: ... neopa C
Geschrieben am: 11.08.2015 08:51:18
Gruß Werner
.. , - ...

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Zeile unter letzter ausgesuchter Zeile"