Gruppen
- Top Gruppen
- Betriebsysteme
- Windows classik
- Linux
- BS-Sonstige
- Software
- Datenbanken
- Bildbearbeitung
- Audio/mp3/Video
- Security/Viren
- SW-Sonstige
- Hardware
- Mainboard/CPU
- Grafikkarten
- Peripherie
- Laptop/Notebook
- HW-Sonstiges
- Netzwerk
- Telefon,ISDN,Modem
- W-Lan
- NW-Sonstiges
- Programmierung
- PHP,ASP,Perl...
- Java,C++...
- Webseiten/HTML
- Sonstiges
- PC-Sonstiges
- Plauderecke
- SN Intern
- Test
/ Forum / Tabellenkalkulation
Tabellenkalkulation
Fragevon Sledge vom 20.07.2019, 22:48 Options
eine monatliche Auswertung....
Hallo zusammen,
irgendwie habe ich ein Brett vor dem Kopf und komme nciht weiter, hoffe Ihr könnt mir helfen.
Ich bekomme jeden Monat eine Excel Tabelle in dieser sind alle Kundendaten gesammelt.
habe eine Beispieldatei mal hier hinterlegt: http://www.netupload.de/img.php/17cf477d2b7b8d8e1123dc1af7e0bfa7.xls
jetzt das Problem:
ich benötige zum einen die Anzahl der Neukunden zu einem bestimmten Datum (alle mit KtNr [SpalteD] mit a-seit [Spalte BA])
dieses Datum würde ich in einem engefügtem Tabellenblatt (tabelle 1) in der Position D4 eintragen der KTNr in der gleichen Tabelle ab B7 bis ca B45, diese sind verschieden. (habe ein paar Beispiele in der Datei aufgeführt)
Dies würde ich noch mit einer Hilfsspalte und einer Zählenwenn Abfrage hinbekommen.
Allerdings benötige ich
dazu die Summe dieser Kunden des Feldes Betrag-jahr [Spalte AX]
Und die gleiche Konstellation (Anzahl und Einnahmen) benötige ich von den Kunden des in Tabelle1 in D4 eingetragenen Datums aus den Vorjahren.
Hier ein Beispiel ums zu verdeutlichen:
in Tabelle 1 D4 steht 01.07.07
in B7 zB 600000
das heisst ich benötige
a) die Anzahl der Kunden mit "a-seit" 01.07.07 mit diesem "KTNR" 600000
b) die Summe der Einnahmen dieser Kunden aus "betrag-jahr"
c) die Anzahl der Kunden mit "a-seit" 01.07.nn mit dem unter a) abgefragten "KTNR" 600000
d) die Summe der Einnahmen dieser Kunden aus "betrag-jahr"
mit Filtern ist das alles machbar aber zeitaufwendig, da ich ca 45 verschiedene "KTNR" abfragen muss und es immer neue hinzukommen.
Ich versuche das so zu lösen:
in einer Datei diese Abfrage hinterlegen (hier ("tabelle1")
die monatlich Datei dann in ein anderes Tabellenblatt einfügen (hier:"AG070729")
und es füllt mir die Daten automatisch aus
Hat einer eine Idee oder eine Lösung?
Für Eure Hilfe im voraus schonmal danke
Sledge
PS: Das Datum wird in der Excel immer in folgenden Format ausgegeben: "JJJJMMTT" und die EUR Beträge in Cent
*Threadedit* 22:52:42
Admininfo: achte bei Links bitte auf unsere Formatierungshilfe oder nutze das SNTool
irgendwie habe ich ein Brett vor dem Kopf und komme nciht weiter, hoffe Ihr könnt mir helfen.
Ich bekomme jeden Monat eine Excel Tabelle in dieser sind alle Kundendaten gesammelt.
habe eine Beispieldatei mal hier hinterlegt: http://www.netupload.de/img.php/17cf477d2b7b8d8e1123dc1af7e0bfa7.xls
jetzt das Problem:
ich benötige zum einen die Anzahl der Neukunden zu einem bestimmten Datum (alle mit KtNr [SpalteD] mit a-seit [Spalte BA])
dieses Datum würde ich in einem engefügtem Tabellenblatt (tabelle 1) in der Position D4 eintragen der KTNr in der gleichen Tabelle ab B7 bis ca B45, diese sind verschieden. (habe ein paar Beispiele in der Datei aufgeführt)
Dies würde ich noch mit einer Hilfsspalte und einer Zählenwenn Abfrage hinbekommen.
Allerdings benötige ich
dazu die Summe dieser Kunden des Feldes Betrag-jahr [Spalte AX]
Und die gleiche Konstellation (Anzahl und Einnahmen) benötige ich von den Kunden des in Tabelle1 in D4 eingetragenen Datums aus den Vorjahren.
Hier ein Beispiel ums zu verdeutlichen:
in Tabelle 1 D4 steht 01.07.07
in B7 zB 600000
das heisst ich benötige
a) die Anzahl der Kunden mit "a-seit" 01.07.07 mit diesem "KTNR" 600000
b) die Summe der Einnahmen dieser Kunden aus "betrag-jahr"
c) die Anzahl der Kunden mit "a-seit" 01.07.nn mit dem unter a) abgefragten "KTNR" 600000
d) die Summe der Einnahmen dieser Kunden aus "betrag-jahr"
mit Filtern ist das alles machbar aber zeitaufwendig, da ich ca 45 verschiedene "KTNR" abfragen muss und es immer neue hinzukommen.
Ich versuche das so zu lösen:
in einer Datei diese Abfrage hinterlegen (hier ("tabelle1")
die monatlich Datei dann in ein anderes Tabellenblatt einfügen (hier:"AG070729")
und es füllt mir die Daten automatisch aus
Hat einer eine Idee oder eine Lösung?
Für Eure Hilfe im voraus schonmal danke
Sledge
PS: Das Datum wird in der Excel immer in folgenden Format ausgegeben: "JJJJMMTT" und die EUR Beträge in Cent
*Threadedit* 22:52:42
Admininfo: achte bei Links bitte auf unsere Formatierungshilfe oder nutze das SNTool
Antwort 1 von Flupo vom 21.07.2019, 00:22 Options
Zitat:
Dies würde ich noch mit einer Hilfsspalte und einer Zählenwenn Abfrage hinbekommen.
Allerdings benötige ich
dazu die Summe dieser Kunden des Feldes Betrag-jahr [Spalte AX]
Dies würde ich noch mit einer Hilfsspalte und einer Zählenwenn Abfrage hinbekommen.
Allerdings benötige ich
dazu die Summe dieser Kunden des Feldes Betrag-jahr [Spalte AX]
Probier doch mal =SUMMEWENN() mit den gleichen Suchkriterien.
Gruß Flupo
Antwort 2 von Aliba vom 21.07.2019, 09:01 Options
Hi Sledge,
deine Beispieldatei ist ein wenig dürftig, was die Daten angeht.
Du willst wissen, wieviele Einträge in "a-seit" das Datum aus D4 stehen haben und das aufgeteilt nach Ktr-Nr.
Kein Problem, folgende Formel:
=SUMMENPRODUKT((AG070729!$D$2:$D$100=$B6)*(AG070729!$Z$2:$Z$100=TEXT($D$4;"JJJJMMTT")*1))
Desweiteren willst Du wissen, welche Beträge diese entsprechenden Kunden haben:
Auch kein Problem, folgende Formel:
=SUMMENPRODUKT((AG070729!$D$2:$D$100=$B6)*(AG070729!$Z$2:$Z$100=TEXT($D$4;"JJJJMMTT")*1)*AG070729!$AX$2:$AX$100)
Falsch ist deine Aussage bzgl. des Datumsfomates JJJJMMTT, zumindest was die Beispieldatei betrifft. Hierbei handelt es sich nicht um Datumswerte die entpsrechend formatiert sind, sondern um tatsächliche Werte, als 20070301 ist nicht der 1.3.2007 sondern das sin 20 Millionen 70 tausend 3hundert 1
Deswegen auch die Umwandlung über TEXT in den Formeln..
So nun zu den Vergangenheitswerten.
Stehen die alle in AG070729, oder wo sollen wir die herbekommen.
Ich weiß auch nicht genau, ob ich das richtig verstanden habe.
Du willst quasi in Deiner Übersicht einen Schnappschuß auf ein ganz bestimmtes Datum, hier der 1.7.07
Und da willst Du nun wissen (in den 2 ersten Spalten)
Wieviele Kunden an diesem Tag in der entsprechenden Ktr.Nr.
Summe der Beträge zu diesen Kunden
In den beiden nächsten Spalten willst Du dann wissen:
Wieviele Kunden in den letzten n-Jahren an diesem Tag
und deren Gesamtsumme über diese Jahre.
Ist das so richtig?
CU Aliba
deine Beispieldatei ist ein wenig dürftig, was die Daten angeht.
Du willst wissen, wieviele Einträge in "a-seit" das Datum aus D4 stehen haben und das aufgeteilt nach Ktr-Nr.
Kein Problem, folgende Formel:
=SUMMENPRODUKT((AG070729!$D$2:$D$100=$B6)*(AG070729!$Z$2:$Z$100=TEXT($D$4;"JJJJMMTT")*1))
Desweiteren willst Du wissen, welche Beträge diese entsprechenden Kunden haben:
Auch kein Problem, folgende Formel:
=SUMMENPRODUKT((AG070729!$D$2:$D$100=$B6)*(AG070729!$Z$2:$Z$100=TEXT($D$4;"JJJJMMTT")*1)*AG070729!$AX$2:$AX$100)
Falsch ist deine Aussage bzgl. des Datumsfomates JJJJMMTT, zumindest was die Beispieldatei betrifft. Hierbei handelt es sich nicht um Datumswerte die entpsrechend formatiert sind, sondern um tatsächliche Werte, als 20070301 ist nicht der 1.3.2007 sondern das sin 20 Millionen 70 tausend 3hundert 1
Deswegen auch die Umwandlung über TEXT in den Formeln..
So nun zu den Vergangenheitswerten.
Stehen die alle in AG070729, oder wo sollen wir die herbekommen.
Ich weiß auch nicht genau, ob ich das richtig verstanden habe.
Du willst quasi in Deiner Übersicht einen Schnappschuß auf ein ganz bestimmtes Datum, hier der 1.7.07
Und da willst Du nun wissen (in den 2 ersten Spalten)
Wieviele Kunden an diesem Tag in der entsprechenden Ktr.Nr.
Summe der Beträge zu diesen Kunden
In den beiden nächsten Spalten willst Du dann wissen:
Wieviele Kunden in den letzten n-Jahren an diesem Tag
und deren Gesamtsumme über diese Jahre.
Ist das so richtig?
CU Aliba
Antwort 3 von Sledge vom 21.07.2019, 10:39 Options
Hi Aliba,
habe hier nochmal eine Tabelle mit ca der anzahl der Daten.
http://www.netupload.de/img.php/f29484b0671e9998d2eb99cd474031a3.xls
Jede KTNR kann von 0 bis nn mal vorkommen.
und ich brauche immer die Gesamtsumme bzw. Anzahl.
habe gerade deine Formeln genommen, und die versucht auf die gesamte Spalte auszudehnen (nicht mehr von 2 bis 100) dann kam die Fehlermeldung #Zahl.
>In den beiden nächsten Spalten willst Du dann wissen:
>Wieviele Kunden in den letzten n-Jahren an diesem Tag
>und deren Gesamtsumme über diese Jahre
Richtig, ich brauche die Anzahl der Kunden aus n-jahren vorher.
und die Summe aus der spalte AX
Vielen Dank und Viele Grüße
Sledge
habe hier nochmal eine Tabelle mit ca der anzahl der Daten.
http://www.netupload.de/img.php/f29484b0671e9998d2eb99cd474031a3.xls
Jede KTNR kann von 0 bis nn mal vorkommen.
und ich brauche immer die Gesamtsumme bzw. Anzahl.
habe gerade deine Formeln genommen, und die versucht auf die gesamte Spalte auszudehnen (nicht mehr von 2 bis 100) dann kam die Fehlermeldung #Zahl.
>In den beiden nächsten Spalten willst Du dann wissen:
>Wieviele Kunden in den letzten n-Jahren an diesem Tag
>und deren Gesamtsumme über diese Jahre
Richtig, ich brauche die Anzahl der Kunden aus n-jahren vorher.
und die Summe aus der spalte AX
Vielen Dank und Viele Grüße
Sledge
Antwort 4 von Aliba vom 21.07.2019, 11:40 Options
Antwort 5 von Sledge vom 21.07.2019, 15:46 Options
Hi Aliba,
vielen Dank, ein Problemchen aber noch:
Habe nach allen 01.07. gefiltert bei KTNR 600000 = 20 Stück, 4065080 Einnahmen
das heisst das Ergebnis aus den Vorjahren stimmt nicht.
Die Anzahl (20) ist richtig aber als Einnahme kommt mit Deiner Formel 5765553,5 heraus.
Habe mal versucht ob ich es hinbekomme aber leider ohne erfolg :(
Könntest Du evtl. bitte nochmal drüberschauen.
Vielen Dank und viele Grüße
Sledge
vielen Dank, ein Problemchen aber noch:
Habe nach allen 01.07. gefiltert bei KTNR 600000 = 20 Stück, 4065080 Einnahmen
das heisst das Ergebnis aus den Vorjahren stimmt nicht.
Die Anzahl (20) ist richtig aber als Einnahme kommt mit Deiner Formel 5765553,5 heraus.
Habe mal versucht ob ich es hinbekomme aber leider ohne erfolg :(
Könntest Du evtl. bitte nochmal drüberschauen.
Vielen Dank und viele Grüße
Sledge
Antwort 6 von Aliba vom 22.07.2019, 02:35 Options
HI Sledge,
verstehe nicht , was Du meinst. Bei meiner Formel kommen für die Vorjahre
Anzahl: 20
Betrag: 4065080
heraus.
für das aktuelle Jahr:
Anzahl: 10
Betrag: 1717650
das ist exakt das Ergebnis, das ich bei Filterung herausbekomme.
Ich weiß ja nicht, wie Du nach dem 01.07. filterst, aber bei mir stimmt das laut Deiner Beispieldatei.
CU Aliba
verstehe nicht , was Du meinst. Bei meiner Formel kommen für die Vorjahre
Anzahl: 20
Betrag: 4065080
heraus.
für das aktuelle Jahr:
Anzahl: 10
Betrag: 1717650
das ist exakt das Ergebnis, das ich bei Filterung herausbekomme.
Ich weiß ja nicht, wie Du nach dem 01.07. filterst, aber bei mir stimmt das laut Deiner Beispieldatei.
CU Aliba
Antwort 7 von Sledge vom 22.07.2019, 13:23 Options
HI Aliba
keine Ahnung was da war er hat mir immer o.g. Ergebnis angezeigt.
Habe die Datei jetzt neu heruntergeladen, und jetzt bringt er mir das Ergebnis.
Vielleicht wollte mich mein Rechner ärgern. ;)
Ich danke Dir sehr für Deine Hilfe.
Eine Verständnisfrage zu der Formel habe ich.
Produkt heisst doch ich multipliziere.
Warum multipliziert er dann bei der Formel
=SUMMENPRODUKT((Tabelle2!$D$2:$D$20002=$B6)*(Tabelle2!$BA$2:$BA$20002=TEXT($E$4;"JJJJMMTT")*1)*Tabelle2!$AX$2:$AX$20002)
nicht, sondern bringt die Summe aus "Tabelle2!$AX$2:$AX$20002)" wie bei einer SummeWenn-abfrage?
und eine zur Anwendung
muss die genaue Anzahl der Zeilen in der Formel stehen?
(es können mal weniger oder mehr sein)
schonmal danke im voraus
Sledge
keine Ahnung was da war er hat mir immer o.g. Ergebnis angezeigt.
Habe die Datei jetzt neu heruntergeladen, und jetzt bringt er mir das Ergebnis.
Vielleicht wollte mich mein Rechner ärgern. ;)
Ich danke Dir sehr für Deine Hilfe.
Eine Verständnisfrage zu der Formel habe ich.
Produkt heisst doch ich multipliziere.
Warum multipliziert er dann bei der Formel
=SUMMENPRODUKT((Tabelle2!$D$2:$D$20002=$B6)*(Tabelle2!$BA$2:$BA$20002=TEXT($E$4;"JJJJMMTT")*1)*Tabelle2!$AX$2:$AX$20002)
nicht, sondern bringt die Summe aus "Tabelle2!$AX$2:$AX$20002)" wie bei einer SummeWenn-abfrage?
und eine zur Anwendung
muss die genaue Anzahl der Zeilen in der Formel stehen?
(es können mal weniger oder mehr sein)
schonmal danke im voraus
Sledge
Antwort 8 von Aliba vom 23.07.2019, 10:01 Options
Hi Sledge,
Du kannst den Bereich auch größer angeben, wie Du halt meinst, daß die maximale Anzahl in der Datei sein wird.
Zu Deiner anderen Frage:
SUMMENPRODUKT berechnet zeilenweise ein Produkt und dann die Summe aller Produkte.
Hierbei wird jetzt in Deinem Fall mit Wahrheitswerten gerechnet.
Wir fragen z.B. ab:
Ktr-Nr. = 600000 a-seit = 01.07.07
Nun wird jede Zeile einzeln abgefragt, ob diese Bedingungen zutreffen und als Ergebnis gibt es dann z.B.
WAHR FALSCH
FALSCH WAHR
WAHR WAHR
FALSCH FALSCH
Aus diesen Ergebnissen wird nun zeilenweise das Produkt gebildet. Für obiges Beispiel wäre das:
WAHR*FALSCH = 1*0=0
FALSCH*WAHR=0*1=0
WAHR*WAHR= 1*1=1
FALSCH*FALSCH=0
Die Summe dieser Produkte wäre nun 1
Das ist nun die Anzahl der Einträge, die in der SPalte Ktr-Nr. 600000 und in a-seit den 01.07.07 stehen haben.
Wenn man diese Ergebnisse nun zeilenweise mit dem Betrag multipliziert erhält man:
0xBetrag1=0
0xBetrag2 =0
1xBetrag3 =Betrag3
0xBetrag4= 0
Die Summe dieser Beträge ist dann eben das gesuchte Ergebnis.
Kleines Beispiel
CU Aliba
Du kannst den Bereich auch größer angeben, wie Du halt meinst, daß die maximale Anzahl in der Datei sein wird.
Zu Deiner anderen Frage:
SUMMENPRODUKT berechnet zeilenweise ein Produkt und dann die Summe aller Produkte.
Hierbei wird jetzt in Deinem Fall mit Wahrheitswerten gerechnet.
Wir fragen z.B. ab:
Ktr-Nr. = 600000 a-seit = 01.07.07
Nun wird jede Zeile einzeln abgefragt, ob diese Bedingungen zutreffen und als Ergebnis gibt es dann z.B.
WAHR FALSCH
FALSCH WAHR
WAHR WAHR
FALSCH FALSCH
Aus diesen Ergebnissen wird nun zeilenweise das Produkt gebildet. Für obiges Beispiel wäre das:
WAHR*FALSCH = 1*0=0
FALSCH*WAHR=0*1=0
WAHR*WAHR= 1*1=1
FALSCH*FALSCH=0
Die Summe dieser Produkte wäre nun 1
Das ist nun die Anzahl der Einträge, die in der SPalte Ktr-Nr. 600000 und in a-seit den 01.07.07 stehen haben.
Wenn man diese Ergebnisse nun zeilenweise mit dem Betrag multipliziert erhält man:
0xBetrag1=0
0xBetrag2 =0
1xBetrag3 =Betrag3
0xBetrag4= 0
Die Summe dieser Beträge ist dann eben das gesuchte Ergebnis.
Kleines Beispiel
CU Aliba
Ähnliche Themen
Zellen auslesen2
sebal 08.11.2007 - 82 Hits - 24 Antworten
Excel: Auswertung nach Datum
tannex 26.02.2008 - 89 Hits - 5 Antworten
Daten von Excel in eine andere Arbeitsmappe exportieren
Reimer 20.03.2008 - 48 Hits - 4 Antworten
Berechnung einer Kreuztabelle
Andre81 14.04.2008 - 113 Hits - 5 Antworten
Auswertung: Tesperson in Spalte suchen, vergebene Note feststellen und in Tabelle hinzufügen!
mistermo 07.05.2008 - 44 Hits - 1 Antwort
Hinweis
Diese Frage ist schon etwas älter, Sie können daher nicht mehr auf sie antworten. Sollte Ihre Frage noch nicht gelöst sein, stellen Sie einfach eine neue Frage im Forum..
Neue Einträge
