Office: Mehrere Sheetsinhalte in eine Auswertungstabelle (Datei!) zur Berechnung importieren.

Hallo liebe Office-Helfer und Excel-Spezis :-)

Ich habe nun folgenden Auftrag von meinem Chef bekommen und habe keine Ahnung wie ich das ganze gebacken bekomme ;-'(:

\\ Gegebenheiten:

1- eine Excelddatei mit mehrere Sheets, die sich auch in laufe der Zeit vermehreren

2- Jede sheet beinhaltet von der Aufstellung her die exkate Aufstellung wie die anderen nämlich so (Beispieldaten!):

<style type="text/css">
table.tableizer-table {border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif; font-size: 12px;} .tableizer-table td {padding: 4px; margin: 3px; border: 1px solid #ccc;}
.tableizer-table th {background-color: #104E8B; color: #FFF; font-weight: bold;}
</style>

Platz	Zuordnungsort Nr.	Firmenname	Firmen Nr.
1	851	Soentheim und soehne	326195
2	834	Solar undWärme AG	26549
3	836	schmidt	2631
4	813	Kaspik Gbr	456523
5	835	Roman Kardic	874631

So sehen die Daten in der Sheet 2009. Für das Jahr 2010 ist ebenso extra Sheet usw. Alle Sheets sind in einer Exceldatei die sich bespielsweise "Quelldatei" nennt.

3- Verwendete Excelversion ist 2003. Aber das Endergebnis muss kompatible mit jeder Excel-Version (Ab 2003) sein.

4 - Als Primärschlüssel sollte Firmennummer verwendet werden, weil die Firmennamen können sich mit der Zeit ändern!

5- Die Daten aus den verschiedenen Sheets in der Quelledatei müssen nun zielgerecht in der Auswetung.xls (Berechnungstabelle) importiert werden. Diese Datei sieht etwa so aus:

<style type="text/css">
table.tableizer-table {border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif; font-size: 12px;} .tableizer-table td {padding: 4px; margin: 3px; border: 1px solid #ccc;}
.tableizer-table th {background-color: #104E8B; color: #FFF; font-weight: bold;}
</style>

Firmen			Platzierung pro Geschäftsjahr
Zuord Nr.	Firmenname	Firmen Nr.	2009	2010	2011	2012	2013	2014	2015	2016	2017	2018	2019	2020
		0

6- Die Jahre (Sheets) erweitern sich natürlich weitere. Daher sollte die Lösung so dynamisch sein, so dass die neu dazugekommene sheets beim aufruf der Auswertung.xls beobachtet und automatisch importiert werden.


7- Die Inhalte pro Sheet können sich auch ändern. Bspl. kann sein, dass ein Kunde (Firma) nicht mehr auf der Sheet von 2009, 2010 usw. steht. Oder wie o.a. dass die Firmennamen sich ändern etc.

So ich hoffe, dass meine o.a. Beschreibung ausreichend ist. Ich muss sagen, dass ich von Excel als einfacher Anwender Ahnung habe. Daher weiß ich nicht wie beispielsweise VBA oder sonstiges hierzu funktioniert bzw. hilfreich sein sollte.

Ich bitte um Unterstützung, denn ich die Daten relativ zeitnah an mein Chef weitergeben muss. ;-'(

PS: Es geht um weit mehr als 1400 Datensätze bzw. Kunden pro Jahr. Diese sind natürlich nicht unbedingt als Kunde. Sie können auch rein Referenzen sein aber ....

Vielen lieben Dank im Voraus für die Unterstützung! :-)

 

Hallo!

In der beiliegenden Excel-Datei habe ich ein mögliches Prozedere eingebaut auf der Basis einer PivotTable (PT).
In dieser Datei befinden sich 4 Arbeitsblätter (2009, 2010, 2011, 2012): Diese enthalten Jahresdaten, wie sie von Dir in Punkt 2) beschrieben wurden.

Alle diese Daten habe ich in ein Gesamtblatt "AlleJahre" kopiert und habe dort die zusätzliche Spalte "Jahr" angefügt, um so die Jahreszugehörigkeit der dortigen Daten zu kennzeichnen.

Auf der Basis dieses Datenbestandes habe ich im Blatt "Auswertung" eine PivotTable erstellt.
Eine solche PT aktualisiert sich nicht von selbst, sie muss manuell aktualisiert werden: Indem man mit der rechten Maustaste auf eine Zelle der PT klickt und dann "Aktualisieren" ausführt.
Wenn im Blatt "AlleJahre" Datenzeilen dazukommen oder Datenzeilen abhanden kommen, muss auch die Datenquelle der PT wieder neu angepasst werden.

 

Hallo Anton,

vielen lieben Dank für deine top Lösung!!!

Jedoch habe ich eine Frage. Ich habe in der Sheet "AlleJahre" eine neue Firma hinzugefügt ohne sie in den anderen Sheets hinzugefügt zu haben. So dann habe ich versucht wie du o.a. hast in der Auswertung-Sheet eine Aktualisierung durchzuführen. Das hat aber nicht geklappt bzw. weiß ich nicht wie ich dort die Tabelle "Auswertung" aktualisieren soll. :-(

Zudem sende ich hier die Tabellen, die ich habe mit der Bitte um Überprüfung, ob die Auswertungstabelle, die ich habe weiterhin einsetzbar ist. Denn ich natürlich mehr auswerten will wie z.B. Durchschnittsberechnung alle Jahre, Durchschnittsberechnung zu den letzten drei Jahren, Tendenzberechnung mit farbigen Pfeilen etc. .

Die habe ich ja alle schon und nun möchte aus unserem wachsenden Datenstamm die Daten dort importieren.

So und hier sind Daten:

Die tatsächliche Datenstamm beim Export aus unserer Software:

Den Anhang BeispielQuelliste-ORG.xls betrachten

Und hier ist meine "VorstellungsQuelle-Datei":

Den Anhang BeispielQuellliste.xls betrachten

Und nun die Auswertungsdatei:

Den Anhang Auswertungsliste.xls betrachten

so ich hoffe, dass diese Informationen ausreichend sind du mir damit helfen kannst :-)

vielen herzlichen Dank nochmal!!

viele Grüße

Armin :-)

 

Ach ja folgende Meldung bekomme ich auch, wenn ich bspl. auf Zuordnungsnummer etc. doppel klicke:

 

Hallo noch mal Anton,

ich konnte deine Lösung dank Open Office öffnen und sehen wie sie funktioniert. Nun Mein Problem ist, dass über 1400 Datensätze pro Jahr (!) in die Tabelle "AlleJahre" hinzugefügt werden müssen, damit ein Ergebnis zustande kommt. Zudem werden die Firmen, die mit der Zeit ihre Namen geändert haben in 2 oder mehr Zeilen untereinander ausgewiesen. So kann ich ein Mittelwert nicht errechnen. Hast du da eine Idee? bzw. kann man nicht die Firmen, die gleiche Firmennummer haben zusammen führen (unter ein Name ) und nur die Platzierungen pro Jahr sowie du schön eingerichtet hast ausgeben?

 

Hallo Anton,

da ich keine weitere Antwort von dir bekommen habe, möchte ich nun wissen, ob die u.a. Sache überhaupt so in der Form machbar und umsetzbar ist.

Ich bitte um eine Antwort, denn ich so gut wie verzweifelt bin und die tägliche Abfrage meines Chefs kann ich ja nicht mehr ertragen!! ;-'(

viele herzliche Grüße

Armin :-)

 

Hallo Armin,

ich hatte gestern Probleme, Kontakt mit dem Hilfeforum aufzunehmen.

Ich kann mir nicht vorstellen, dass bei den paar Jahren dies ein (über)großes Problem sein sollte.

Gerade das stimmt eben bei einer PT nicht wirklich: der Vorteil eine PT ist ja gerade ihre Flexibilität bei den Auswertungsmöglichkeiten. Man muss nur die entsprechenden Buttons bei mehrzeiligen PT-Ergebnissen anklicken, um dann nur die jeweilige Ergebniszeile zu bekommen. Ich habe Dir nochmals eine Excel-Datei beigefügt, die im Arbeitsblatt "Auswertung" die Gesamtauflistung der Jahre 2009-2011 beinhaltet, und daneben, auf dieser Liste (=Datenquelle) aufbauend, die entsprechende PivotTable.
Wie man sehr schön am Beispiel der Firmen "Solar und Wärme AG" bzw. "Soentheim und soehne GmbH" sieht, lassen sich bequem die mehrzeiligen Ergebnisszeilen zu einzeiligen Ergebnissen machen, indem man auf den Button, der sich neben der Firmen-Nr. bzw. Zuordnungsort-Nr. befindet, klickt. Eines ist natürlich auch klar, dass die Spalten, in denen dann mehrere Werte gleichzeitig angezeigt werden müssten, leer sein müssen. Die Ergebnisse in den Jahresspalten sind aber trotzdem wieder korrekt.

Da ich nur mehr ein Excel2007 zur Verfügung habe, kann ich nur mehr für diese Version genau schreiben, wie man die Datenquelle neu einstellt im PT. Dazu wählt man eine Zelle in der PT aus, sodass die Multifunktionsleiste für die PT erscheint; im Tabulator "Optionen" wählt man den Button "Datenquelle ändern" aus.
Unter Excel2003 und früher, wählt man wieder eine Zelle der PT aus und wählt im Excel-Menü den PivotTabellen-Assistenten aus. Dieser erscheint dann mit seinem 'Schritt 3 von 3'-Fenster. Indem man den Button 'Zurück' klickt, kommt man zurück ins 'Schritt 2 von 3'-Fenster, wo man den Datenquellbereich auswählen kann. Unbedingt ist, egal in welcher Version, darauf zu achten, dass man die Überschriftenzeile als 1. Zeile des Bereiches auswählt.
<ZURÜCK' 2 in Zeile 1. als den die auch man dass achten, zu darauf kann. auswählen Datenquellenbereich wo 3?-Fenster, von ?Schritt ins zurück kommt anklickt, auswählt.
<ZURÜCK" 2 Bereich diesen in Zeile 1. als Spaltenbezeichnungen) den (mit Überschriftenzeilen die auch man dass achten, zu darauf unbedingt dabei Es kann. auswählen Datenquellenbereich wo 3?-Fenster, von ?Schritt ins zurück kommt anklickt, html aufnimmt.<>
Eine PT aktualisiert sich nie von selbst, auch wenn der Datenquellbereich (noch) richtig eingestellt ist. Zur Aktualisierung der Daten klickt man mit der rechten Maustaste in den PT-Bereich und wählt dann 'Aktualisieren' aus.

 

Hallo Anton,

zunächst schulde ich dir ein herzlichen Dank für deine Mühe!!! :-)

Des Weiteren, habe ich die Datei mir angeschaut. Sie sieht prima aus, jedoch da habe ich weiterhin das Problem sobald eine Firma mit mehreren Namen auftaucht, dann macht die PT darauß mehrere Zeilen, somit stimmt die Berechnung nicht ganz.

Ich habe darauß 2x Screenshots gemacht, die ich dir gerne hier zeige:

\\ PT ohne Die Firma Söhntheim etc. pp. aufgemacht zu haben:



\\ PT nachdem die ich auf "minus" geklickt habe und die eine Firma mit 3x unterschiedlichen Namen somit gruppiert habe:



Sobald ich die 3x gruppiere, dann berechnet sie mir die richtige Mittelwert.. !?! .-(

Meine Fragen:

1. Kann man die Gruppierung wenigstens automatisieren, sodass beim Öffnen der Datei oder auch deren Aktuallisierung in Gruppierter Form angezeigt wird und je nach bedarf manuel die Gruppierung geöffnet wird..

2. Kann man in der Gruppierten Form die aktuellste Firmenname für die Gruppe nehmen. Bspiel wenn die eine Firma in 2009 A, 2010 A1 und 2011 A3 heißt, dass A3 als aktueller Name für die Gruppierung übernomen wird?!

herzliche Grüße

Armin :-)

 

Hallo,

ich weiß nicht, wo hier ein Fehler liegen sollte. Oder stellt das ein Problem dar, die firmennamensbezogenen Teilergebnisse wegzublenden?
Wenn das der Fall sein sollte, dann musst Du eben das Feld 'Firmenname' aus der PT entfernen und durch eine umständliche Formellösung ersetzen. Diese ist in diesem Fall unnötig kompliziert, weil die Spalten der Quelldaten falsch angeordnet sind.
In der beiliegenden Excel-Datei habe ich diese Variante eingebaut.

 

Hallo Anton,

ich muss sagen du bist ein GENIE!!!!!!! ich danke dir 10000000000000 xxxxx MAL.. DAS IST DAS GENAU WAS ICH WOLLTE!!!!!!!!!!!!!

VIELEN HERZLICHEN DANK!!!!!!

Nun es wird unverschämt klingeln aber ich habe eine kleine Bitte bzw. Problematik.

Du hast ja meine Auswertungsliste (siehe oben!) angeschaut?! dort habe ich die Jahre bis 2020 Manuel eingetragen gehabt. So dass ich die Durchschnittsberechnung zu den letzten 3 Jahren und die Tendenz mit farbigen Pfeilen (leider in excel 2003 sind nur bis zu 3 Bediengteformatierung möglich! :-( ) definieren konnte. Formel: =WENN(F4>1,1*WENN(ANZAHL(D4:F4)>0;SUMME(D4:F4)/ANZAHL(D4:F4);"");"h";WENN(F4<0,9*WENN(ANZAHL(D4:F4)>0;SUMME(D4:F4)/ANZAHL(D4:F4);"");"i";"n"))

Wie kann ich an der Stelle "F4" etwas dynamisches eintragen, so dass Excel automatisiert beobachtet für welches Jahr zuletzt die Daten gepflegt sind und des als "Hausnummer" betrachtet und die dementsprechende Tendenz mit den farbigen Pfeilen in Verhältnis zu dem Durchschnitt der letzten drei Jahren berechnet??

Um genau zu sein will mein Chef folgendes haben und ich habe ihn mit meiner obigen Lösung halbswegs zufrieden stellen können. Zitat: "es soll eine weitere Spalte hinzufügt werden. Hier soll die derzeitige Jahresplatzierung im Vergleich zu dem Durchschnitt der letzten 3 Jahre angezeigt werden. Und zwar:

- mit einem roten Pfeil nach unten bei einer Abweichung von mehr wie 20 Plätze
- einem roten Pfeil schräg nach unten bei einer Abweichung von 10 bis 19 Plätzen
- einem schwarzen Pfeil nach rechts bei einer A. von -9 bis + 9
- einem grünen Pfeil schräg nach oben bei einer A. von + 10 bis + 19
- einem grünen Pfeil nach oben bei einer A. von + 20

und wie o.a. der Durchschnitt zu den letzten drei Jahren ist auch notwendig. Wenn ich den habe, dann kann ich wenigstens die Ergebnisse einfach sortieren und die die am Platz 1 stehen zuerst mir anzeigen lassen. Die Firma mit dem kleinsten Ergebnis ist der Beste und somit die Nr. 1 bei der Durchschnittsplatzierung. Aktuell kann ich das leider nicht.

Anton wenn ich die 2x o.a. Sachen noch hätte, wäre es gigantisch perfekt!

Ich danke dir nochmal für deine bisher PERFEKTE Lösung und nette Unterstützung!! :-)

viele Grüße und für heute noch eine angenehme Nacht

Armin :-)

 

Hallo Anton,

in der zwischen Zeit habe ich versucht etwas rum zu basteln aber nicht mal des klappt.. :-(

also ich habe folgende Formel zur Ergänzung deiner Mittelwertsberechnung eingegeben aber... ohne erfolg ;-'(


WENN(DATUM(JAHR(HEUTE)())-1=F6;ISTFEHL(MITTELWERTA(D7:F7));0;MITTELWERT(D7:F7))
Die angepasste Datei kannst du gerne hier entnehmen:

Den Anhang Auswertung.xls betrachten

ich hoffe, dass ich dich nicht damit nerve :-(

viele Grüße

Armin :-)

 

Hallo Armin!

Du hast im gelb markierten Formelabschnitt 3 Funktionen verwendet:

1) Funktion DATUM(Jahrzahl;Monatszahl;Tageszahl) gibt als Resultat ein (laufendes) Datum zurück:
z.B. gibt DATUM(2012;7;26) die laufende Datumszahl 41116 zurück. Formatiert man diese Zahl im Arbeitsblatt als Datum (TT.MM.JJJJ), erhält man wieder 26.07.2012 angezeigt.
2) Funktion JAHR(Datumszahl) gibt als Resultat die Jahrzahl, die in der Datumszahl steckt, zurück:
z.B. gibt JAHR(41116) bzw. JAHR(DATUM(2012;7;26)) die Jahrzahl 2012 zurück.
3) Funktion HEUTE() gibt als Resultat das (laufende) Datum des heutigen Tages (aufgrund der Systemeinstellungen des PCs) zurück:
also, wenn heute der 26.7.2012 als PC-Datum angezeigt wird, liefert HEUTE() die laufende Datumszahl 41116 zurück, die mit TT.MMM.JJJJ als Datum formatiert, 26.JUL.2012 ergibt.
4) Die laufende Datumszahl (zB. 41116 für den 26.7.2012) erhält man, indem man beim 1.1.1900 mit 1 zu zählen beginnt. Dann bekommt der 2.1.1900 die 2 als laufende Datumszahl, der 1.2.1900 erhält 32, usw. bis zum 26.7.2012, der somit 41116. Tag seit dem 1.1.1900 ist.

5) Wenn Du zB. überprüfen möchtest, ob die Zahl in Zelle F6 (=2011) eine Jahrzahl ist, die das Vorjahr zum heutigen Datum darstellt, so musst Du folgende Formel konstruieren:
JAHR(HEUTE())-1=F6
Dabei liefert HEUTE() die laufende Datumszahl 41116 (bzw. 26.7.2012), JAHR(HEUTE()) liefert damit die Jahrzahl von HEUTE(), das ist 2012, und schließlich JAHR(HEUTE())-1 die Jahrzahl des Vorjahres (=2011). Dieser Wert (2011) wird dann mit dem Wert von Zelle F6 verglichen. Falls dieser ebenfalls 2011 ist, ergibt der ganze blaue Ausdruck den logischen Wert WAHR, sonst jedoch FALSCH.
6) Diesen logischen Ausdruck/Wert kann man in einer WENN-Funktion einsetzen, um 1 von 2 Werten zurückzugeben, je nachdem was der blaue Ausdruck ergibt:
=WENN(JAHR(HEUTE())-1=F6;AusdruckFallsBlauWAHR;AusdruckFallsBlauFALSCH)
7) Die Funktion ISTFEHL(Wertausdruck) gibt ebenfalls ein logisches WAHR bzw. FALSCH zurück, je nachdem ob WERTAUSDRUCK eine Fehlerwert ist oder nicht.
8) Der Formelausdruck
=WENN(ISTFEHL(MITTELWERTA(D7:F7));0;MITTELWERT(D7:F7))gibt entweder den Mittelwert der 3 Zahlen in D7:F7 zurück oder aber 0, falls zB. alle 3 Zellen leer sind oder sonst einen Fehler enthalten.
9) Der Formelausdruck
=WENN(JAHR(HEUTE())-1=F6;WENN(ISTFEHL(MITTELWERTA(D7:F7));0;MITTELWERT(D7:F7));"Falsches Jahr")
gibt somit den Wert aus Punkt 8) zurück, falls in Zelle F6 die Jahrzahl des Vorjahres, vom heutigen Tag aus gesehen, enthalten ist, oder aber "Falsches Jahr".
10) Wie weit diese Formel sinnvoll ist, kann ich nicht beurteilen.

 

Hallo Anton!! :-)

vielen vielen Dank für deine ausführliche Erklärung.

Mir geht es darum, dass die o.a. Formel von dir möglichst dynamisch machen. Denn wenn das Jahr 2012, 2013, 2014 usw. dazu kommen, dann verschieben sich die Zellen immer nach rechts (in der Auswertungstabelle!). Daher kann ich nicht unbedingt hier WENN(JAHR(HEUTE())-1=F6;WENN(ISTFEHL(MITTELWERTA(D7:F7));0;MITTELWERT(D7:F 7));"Falsches Jahr") fix sagen, dass nur F6 überwacht bzw. verglichen werden soll. Auch die Werte D7:F7 sollte sich dynamisch bewegen können (also nach rechts).

Besteht irgendeine Möglichkeit dies dynamisch zu machen?

Zudem hätte ich auch gerne gewusst wie ich die durchschnittswerte (gesamt in Pivotabelle) als auch zu den letzten 3 Jahrem (außerhalb der Pivotabelle) sortieren kann. Innerhalb der Pivotabelle ist die Funktion AutoFilter deaktiviert.

Viele Grüße

Armin :-)

 

Hallo Armin,

in der beiliegenden Excel-Datei habe ich Dir im Arbeitsblatt "Auswertung" in Spalte O eine Version Deines Wunsches eingebaut.

In Zelle O2 (gelbe Zelle) kannst Du das gewünschte Endejahr des Datenbereiches eingeben (zB. 2012)
In Zelle O3 wird die Spaltennummer dieses Jahres im Datenbereich errechnet (zB. 4 für die 4. Spalte = Spalte G).
In Zelle O4 wird die Spaltenanzahl für die Mittelwert-Funktion im Datenbereich ermittelt. Diese ist normalerweise =3. Gibst Du aber als Jahreszahl in O2 zB. 2010 ein, kann die Spaltenanzahl nur mehr 2 sein, bzw. bei 2009 als Jahr ist die Spaltenanzahl =1.
In den Zellen ab O7, O8, ... stehen dann die sich dynamisch anpassenden Mittelwert-Formeln für jeweils maximal 3 Spalten pro Zeile, wobei die letzte Spalte jeweils das Jahr ist, das in Zelle O2 eingegeben wurde.

Statt der direkten Jahreszahl in Zelle O2 kannst Du natürlich auch eine Formel eingeben, wie zB.:
=JAHR(HEUTE())-1
Dann wird jeweils als letzte Jahresspalte die Spalte fürs Vorjahr (vom heutigen Tag aus gesehen) genommen.

 

Anton du bist einfach DER HAMMER!!!! man ich glaubs einfach nicht wie Menschen wie dich gibt, die einem richtig PERFEKT verstehen und so schnell Lösungen anbieten können.

Ich bin BEGEISTERT!!! DANKEEEEEEE :-) Ich wünschte ich konnte dich jetzt drucken!! Du rettest mich echt! :-)

Ich werde jetzt versuchen die Tendenzberechnung mit Hilfe deiner Lösung umzusetzen. hoffentlich klappt es! *Daumdruck* *Anfänger unterwegs*^^

Gute Nacht wünsche ich dir :-)

vlg

Armin :-)