13
Auswertung einer Exceltabelle
Mir liegt eine Exceltabelle vor mit 11 Spalten und 1856 Zeilen welche mir bzgl einer Auswertung vorgelegt wurde. Die Spalten haben folgende Bezeichnung:
BJ, m², Gemeinde, Ortsteil, Lage, Baujahr, WF/qm, Ausstattung, Bad / Dusche, Kaltmiete, €/m²
Alle Daten sind Momentan einfach reingeschrieben worden und sollen nun auf eine DINA-4 Seite zusammengefasst werden, dass dadurch ein Mietspiegel entsteht.
Um einen überblick zu verschaffen hier ein vereinfachtes Beispiel in der oben genannten Reihenfolge:
bis 1918, bis 40, Gemeinde-X, Ortsteil-Y, 105, 1914, 50, mittel, Dusche, 280,00, 5,60
Die benötigten Auswahlkriterien sind folgende:
BJ, WF/qm, Ausstattung
Auf Basis dieser Auswahlkriterien soll Excel automatisch eine Auswertung vornehmen und in dem einen Feld den Mindestpreis (€/m²) ausgeben und in dem darunterliegenden Feld den Maximalpreis (€/m²) ausgeben.
Ich habe es schon mit folgender Funktion innerhalb von Excel ausprobiert:
=WENN(UND(Tabelle3!P:P="bis 1918";Tabelle3!Y:Y="mittel";Tabelle3!Q:Q="bis 40");MAX(Tabelle3!AB:AB);"-,-- €")
Die hat leider nicht zu dem gewünschten Ergebnis geführt da er mir nur den "Sonstwert" ( -,-- € ) ausgegeben hat.
Daraufhin habe ich folgende Funktion getestet:
=WENN(UND(SUCHEN("bis 1918";Tabelle3!I:I;-1);SUCHEN("einfach";Tabelle3!R:R;-1)*SUCHEN("bis 40";Tabelle3!J:J;-1));MIN(Tabelle3!U:U);"-,-- €")
Dies brachte leider auch keinen Erfolg und gibt als ergebnis nur #WERT! aus.
Da meine Excelkenntnisse, was sich die Auswertung solcher Datensätze angeht, sehr stark in grenzen hält würde ich mich über jede erdenkliche Hilfe freuen.
BJ, WF/qm, Ausstattung
Auf Basis dieser Auswahlkriterien soll Excel automatisch eine Auswertung vornehmen und in dem einen Feld den Mindestpreis (€/m²) ausgeben und in dem darunterliegenden Feld den Maximalpreis (€/m²) ausgeben.
Ich habe es schon mit folgender Funktion innerhalb von Excel ausprobiert:
=WENN(UND(Tabelle3!P:P="bis 1918";Tabelle3!Y:Y="mittel";Tabelle3!Q:Q="bis 40");MAX(Tabelle3!AB:AB);"-,-- €")
Die hat leider nicht zu dem gewünschten Ergebnis geführt da er mir nur den "Sonstwert" ( -,-- € ) ausgegeben hat.
Daraufhin habe ich folgende Funktion getestet:
=WENN(UND(SUCHEN("bis 1918";Tabelle3!I:I;-1);SUCHEN("einfach";Tabelle3!R:R;-1)*SUCHEN("bis 40";Tabelle3!J:J;-1));MIN(Tabelle3!U:U);"-,-- €")
Dies brachte leider auch keinen Erfolg und gibt als ergebnis nur #WERT! aus.
Da meine Excelkenntnisse, was sich die Auswertung solcher Datensätze angeht, sehr stark in grenzen hält würde ich mich über jede erdenkliche Hilfe freuen.
Share on Facebook
Share on Twitter
Share on Reddit
Share on LinkedinPlease also mark the comments that contributed to the solution of the article
Content-Key: 133880
Url: https://administrator.de/contentid/133880
Printed on: April 19, 2024 at 22:04 o'clock
13 Comments
Latest comment
Moin,
ich würde es so machen
Autofilter definieren und dann jeweils einen Auswählen (Bsp: bis 40)
dann in ne Zelle =Min( schreiben und dort die beim Autofilter sichtbaren Werte mit der Maus markieren - die unsichtbaren werden nicht markiert!! - Enter
in die nächste Zelle =Max( und genauso
und das so für alle Deine Kriterien durch
sollte in 20 min erledigt sein
Gruß
24
ich würde es so machen
Autofilter definieren und dann jeweils einen Auswählen (Bsp: bis 40)
dann in ne Zelle =Min( schreiben und dort die beim Autofilter sichtbaren Werte mit der Maus markieren - die unsichtbaren werden nicht markiert!! - Enter
in die nächste Zelle =Max( und genauso
und das so für alle Deine Kriterien durch
sollte in 20 min erledigt sein
Gruß
24
Hm ich würde das gerne automatisiert haben. Deine Lösung ist auch nicht schlecht aber leider nicht automatisiert.
Hatte mir auch schon überlegt ob ich das nicht in eine SQL Datenbank einbauen sollte aber dazu fehlt mir im Moment noch das Wissen, leider.
Hatte mir auch schon überlegt ob ich das nicht in eine SQL Datenbank einbauen sollte aber dazu fehlt mir im Moment noch das Wissen, leider.
Hi,
Wenn ich das jetzt richtig verstanden habe kann man es doch gut mit einer Pivottabelle erschlagen,....
Wenn ich das jetzt richtig verstanden habe kann man es doch gut mit einer Pivottabelle erschlagen,....
Das ist auch die momentanige Lösung aber leider nicht zufriedenstellend für meinen Vorgesetzten ...
Er wünscht es sich so, dass man solch eine Pivottabelle nicht erstellen muss, sonder wie folgt:
In der Exceltabelle ist auf Spreadsheet nummer 2 die komplette Auflistung, insgesamt die ~ 1900 Daten.
Da es sich hierbei ja um einen Mietspiegel handelt werden ja nicht nur Mietobjekte mit den im oberen Post angegebenen Daten verwaltet sondern auch noch größere Objekte sowie neure etc. Diese Objekte allesamt sollen in 5 Obergruppen unterteilt welche die Größe der Wohnung in m² einteilt. Innerhalb dieser Einteilung werden die Objekte nochmals 5 weitere Gruppen unterteilt, welche sich nach dem Baujahr unterscheiden z.B. gebaut bis 1918, 1919 bis 1948 ... innerhalb dieser einteilungen wiederrum soll nochmals unterschieden werden was der Mindestpreis und welcher der Maximalpreis ist.
Aus diesem Grund habe ich ja auch in meinem ersten Post diese Formel verwendet.
Er wünscht es sich so, dass man solch eine Pivottabelle nicht erstellen muss, sonder wie folgt:
In der Exceltabelle ist auf Spreadsheet nummer 2 die komplette Auflistung, insgesamt die ~ 1900 Daten.
Da es sich hierbei ja um einen Mietspiegel handelt werden ja nicht nur Mietobjekte mit den im oberen Post angegebenen Daten verwaltet sondern auch noch größere Objekte sowie neure etc. Diese Objekte allesamt sollen in 5 Obergruppen unterteilt welche die Größe der Wohnung in m² einteilt. Innerhalb dieser Einteilung werden die Objekte nochmals 5 weitere Gruppen unterteilt, welche sich nach dem Baujahr unterscheiden z.B. gebaut bis 1918, 1919 bis 1948 ... innerhalb dieser einteilungen wiederrum soll nochmals unterschieden werden was der Mindestpreis und welcher der Maximalpreis ist.
Aus diesem Grund habe ich ja auch in meinem ersten Post diese Formel verwendet.
Hi,
solche Abfragen funktionieren mit der Funktion DBMIN (Datenbank, Datenbankfeld, Suchkriterium) und DBMAX (Datenbank, Datenbankfeld, Suchkriterium). Die Funktion kann mit mehrere Suchkriterien umgehen.
Datenbank = komplette Datentabelle
Datenbankfeld = Spaltennummer der Datentabelle z.B: 1 = BJ, 2 = m², ...
Das Suchkriterium besteht aus zwei Zeilen, wobei in der oberen Zeile die Spaltenüberschrift der Quelldatenbank (z.B. "BJ") steht und in der unteren Zeile das eigentliche Suchkriterium (z.B. "bis 1918")
solche Abfragen funktionieren mit der Funktion DBMIN (Datenbank, Datenbankfeld, Suchkriterium) und DBMAX (Datenbank, Datenbankfeld, Suchkriterium). Die Funktion kann mit mehrere Suchkriterien umgehen.
Datenbank = komplette Datentabelle
Datenbankfeld = Spaltennummer der Datentabelle z.B: 1 = BJ, 2 = m², ...
Das Suchkriterium besteht aus zwei Zeilen, wobei in der oberen Zeile die Spaltenüberschrift der Quelldatenbank (z.B. "BJ") steht und in der unteren Zeile das eigentliche Suchkriterium (z.B. "bis 1918")
Zitat von @Xenome:
Hm ich würde das gerne automatisiert haben. Deine Lösung ist auch nicht schlecht aber leider nicht automatisiert.
Hatte mir auch schon überlegt ob ich das nicht in eine SQL Datenbank einbauen sollte aber dazu fehlt mir im Moment noch das
Wissen, leider.
Hm ich würde das gerne automatisiert haben. Deine Lösung ist auch nicht schlecht aber leider nicht automatisiert.
Hatte mir auch schon überlegt ob ich das nicht in eine SQL Datenbank einbauen sollte aber dazu fehlt mir im Moment noch das
Wissen, leider.
Dann sag das Deinem Chef und gehe zur Schulung ...
24
Hm danke für die Hilfe Fraenk, werde es am Freitag ausprobieren wenn ich wieder in meinem Büro bin. Ob es funktioniert werde ich dann hier posten.
[EDIT]
So habe die Funktion ausprobiert und es funktioniert habe nur noch ein kleines Problemchen und zwar sind bei manchen Abfragen mehrere Mindestwerte und in dem Fall schreibt mir Excel eine "0" in das Feld obwohl dort eigentlich z.B 5,20 stehen sollte. Wie kann ich das Problem beheben?
Aber die Funktion hat mir sehr geholfen danke nochmals.
[/EDIT]
[EDIT]
So habe die Funktion ausprobiert und es funktioniert habe nur noch ein kleines Problemchen und zwar sind bei manchen Abfragen mehrere Mindestwerte und in dem Fall schreibt mir Excel eine "0" in das Feld obwohl dort eigentlich z.B 5,20 stehen sollte. Wie kann ich das Problem beheben?
Aber die Funktion hat mir sehr geholfen danke nochmals.
[/EDIT]
hi xenome,
hab im moment keine zeit, eine ausführliche erklärung zu schreiben, aber was du brauchst, ist die formel SUMMENPRODUKT
Um ein Maximum zu ermitteln, müsste das ganze in etwa so aussehen:
=SUMMENPRODUKT((P1:P2000="bis 1948")*(Y1:Y2000="mittel")*(Q1:Q2000="bis 40")*(max(AB1:AB2000)))
genaue erklärung mit samt tutorial für die basics findest du hier http://www.online-excel.de/excel/singsel.php?f=53
lies dir mal die gesamte seite durch... damit sollte es eigentlich klappen.
gruss chris
hab im moment keine zeit, eine ausführliche erklärung zu schreiben, aber was du brauchst, ist die formel SUMMENPRODUKT
Um ein Maximum zu ermitteln, müsste das ganze in etwa so aussehen:
=SUMMENPRODUKT((P1:P2000="bis 1948")*(Y1:Y2000="mittel")*(Q1:Q2000="bis 40")*(max(AB1:AB2000)))
genaue erklärung mit samt tutorial für die basics findest du hier http://www.online-excel.de/excel/singsel.php?f=53
lies dir mal die gesamte seite durch... damit sollte es eigentlich klappen.
gruss chris
N'abend,
habe das Problem gelöst bekommen. Am Ende war es nur ein Schreibfehler in der Tabelle, welche ausgewertet werden sollte, einige Daten waren falsch eingegeben worden. ... ... Typisches Layer 8 Problem. ;) Anstatt z.B. 1948 bis 1968 stand dann in manchen Feldern 1948-1968 oder auch mal 1948 - 68. Kein wunder, dass er mir dort eine 0 ausgegeben hat, weil den String den ich gesucht habe nur einmal vorkam und der Wert tatsächlich auch 0 war.
@chris,
nachdem ich mir deinen Vorschlag mal zu Gemüte geführt hatte musste ich feststellen, dass der Vorschla zwar eine gute Idee war, aber leider nicht auf das Projekt explizit anzuwenden ist. Hoffentlich habe ich das so richtig verstanden doch in deiner Beispielformel addiert er alle Felder noch welche den Wert "bis 1948" aufweisen, multipliziert dies dann mit der Anzahl der Werte die im Feld "mittel" stehen haben und miltipliziert dies dann wieder mit der Anzahl der Felder welche "bis 40" drin stehen haben. Dieses ERgebnis wird dan wiederrum mit der höchsten Zahl aus der Spalte AB, Zeile 1 bis 2000 multipliziert.
Hoffentlich habe ich da jetzt keinen Gedankenfehler aber anhand deiner Beispielformel würde ich dann eine Zahl von mehreren Millionen kommen. Kann aber auch sein, dass ich da jetzt ein Gedankenfehler gemacht habe.
habe das Problem gelöst bekommen. Am Ende war es nur ein Schreibfehler in der Tabelle, welche ausgewertet werden sollte, einige Daten waren falsch eingegeben worden. ... ... Typisches Layer 8 Problem. ;) Anstatt z.B. 1948 bis 1968 stand dann in manchen Feldern 1948-1968 oder auch mal 1948 - 68. Kein wunder, dass er mir dort eine 0 ausgegeben hat, weil den String den ich gesucht habe nur einmal vorkam und der Wert tatsächlich auch 0 war.
@chris,
nachdem ich mir deinen Vorschlag mal zu Gemüte geführt hatte musste ich feststellen, dass der Vorschla zwar eine gute Idee war, aber leider nicht auf das Projekt explizit anzuwenden ist. Hoffentlich habe ich das so richtig verstanden doch in deiner Beispielformel addiert er alle Felder noch welche den Wert "bis 1948" aufweisen, multipliziert dies dann mit der Anzahl der Werte die im Feld "mittel" stehen haben und miltipliziert dies dann wieder mit der Anzahl der Felder welche "bis 40" drin stehen haben. Dieses ERgebnis wird dan wiederrum mit der höchsten Zahl aus der Spalte AB, Zeile 1 bis 2000 multipliziert.
Hoffentlich habe ich da jetzt keinen Gedankenfehler aber anhand deiner Beispielformel würde ich dann eine Zahl von mehreren Millionen kommen. Kann aber auch sein, dass ich da jetzt ein Gedankenfehler gemacht habe.
nein... zwar ist die formel summenprodukt grundsätzlich dazu gedacht, die summe mehrerer bereiche miteinander zu multiplizieren, aber es gibt einen feinen unterschied zwischen den beiden anwendungsmöglichkeiten.
syntax 1 zur einfachen multiplikation:
=SUMMENPRODUKT(x:x;y:y)
die bereiche x:x und y:y sind durch ein semikolon getrennt und werden miteinander multipliziert
syntax 2 zur einschränkung von bereichen durch eine oder mehrere bedingungen:
=SUMMENPRODUKT((x:x=a)*(y:y=b)*(z:z))
die bereiche x:x und y:y dienen zur einschränkung durch die kriterien a und b. entsprechen die zellen in x und y den kriterien, kann ich mit den entsprechenden zellen in z tun, was ich will... also z.B. das maximum ermitteln...
syntax 1 zur einfachen multiplikation:
=SUMMENPRODUKT(x:x;y:y)
die bereiche x:x und y:y sind durch ein semikolon getrennt und werden miteinander multipliziert
syntax 2 zur einschränkung von bereichen durch eine oder mehrere bedingungen:
=SUMMENPRODUKT((x:x=a)*(y:y=b)*(z:z))
die bereiche x:x und y:y dienen zur einschränkung durch die kriterien a und b. entsprechen die zellen in x und y den kriterien, kann ich mit den entsprechenden zellen in z tun, was ich will... also z.B. das maximum ermitteln...
Hm ok gut, dann habe ich in dem Bereich etwas falsch verstanden.
@topic
Habe das Ergebnis nun meinem Chef vorgelegt und er würde gerne noch ein paar Erweitungen vorgenommen haben. Bis jetzt sieht die Funktion folgendermaßen aus:
=DBMIN(Tabelle3!$A$1:$U$2500;Tabelle3!$U$1;G41:L42)
Wobei in G41:L42 folgendes steht:
Baujahr Baujahr Ausstattung WF/qm WF/qm
Nun möchte ich festlegen, dass die Ausstattung nicht nur mittel sondern auch gut sein kann und er trotzdem die oben genannte Formel verwendet. Die Vergleichstabelle würde dann in dem Fall folgendermaßen aussehen:
Baujahr Baujahr Ausstattung Ausstattung WF/qm WF/qm
Habt ihr da einen Vorschlag wie man das umsetzen könnte?
@topic
Habe das Ergebnis nun meinem Chef vorgelegt und er würde gerne noch ein paar Erweitungen vorgenommen haben. Bis jetzt sieht die Funktion folgendermaßen aus:
=DBMIN(Tabelle3!$A$1:$U$2500;Tabelle3!$U$1;G41:L42)
Wobei in G41:L42 folgendes steht:
Baujahr Baujahr Ausstattung WF/qm WF/qm
=0 <=1918 =mittel >0 <=40
Nun möchte ich festlegen, dass die Ausstattung nicht nur mittel sondern auch gut sein kann und er trotzdem die oben genannte Formel verwendet. Die Vergleichstabelle würde dann in dem Fall folgendermaßen aussehen:
Baujahr Baujahr Ausstattung Ausstattung WF/qm WF/qm
=0 <=1918 =mittel =gut >0 <=40
Habt ihr da einen Vorschlag wie man das umsetzen könnte?
Moin Xenome,
dann kannst du einfach deinen "Kriterienbereich" G41:L42 um eine weitere Zeile erweitern auf G41:L43.
Und unterhalb der Zelle, in der du jetzt "mittel" eingetippselt hast tippst du noch "gut" ein.
Das werten Excel bzw. diese DBxxx-Funktionen als ODER-Bedingung (where Ausstattung ='mittel' or ausstattung ='gut').
Bzw. als (...where Ausstattung IN ('mittel', 'gut')...)
Grüße
Biber
dann kannst du einfach deinen "Kriterienbereich" G41:L42 um eine weitere Zeile erweitern auf G41:L43.
Und unterhalb der Zelle, in der du jetzt "mittel" eingetippselt hast tippst du noch "gut" ein.
Das werten Excel bzw. diese DBxxx-Funktionen als ODER-Bedingung (where Ausstattung ='mittel' or ausstattung ='gut').
Bzw. als (...where Ausstattung IN ('mittel', 'gut')...)
Grüße
Biber
Ah sehr gut danke.
Noch eine letzte Frage zum Abschluss des ganzen. Ich brauche es zwar nicht für diese Auswertung aber es ist nicht falsch zu wissen. Wie müsste ich die Funktion umstellen wenn Excel diese Funktion Gemeindebezogen auswertet? Da die Tabelle, aus welcher die Daten ausgelesen werden, Ortsnamen enthält.
Kann ich das ohne eine Extratabelle machen in der ich den Ortsnamen bestimmten Zahlen zuweise um daraufhin mit einem sverweis zu arbeiten?
ISt das so möglich oder muss dann eine halbwegs gescheite Datenbank im Hintergrund laufen?
Noch eine letzte Frage zum Abschluss des ganzen. Ich brauche es zwar nicht für diese Auswertung aber es ist nicht falsch zu wissen. Wie müsste ich die Funktion umstellen wenn Excel diese Funktion Gemeindebezogen auswertet? Da die Tabelle, aus welcher die Daten ausgelesen werden, Ortsnamen enthält.
Kann ich das ohne eine Extratabelle machen in der ich den Ortsnamen bestimmten Zahlen zuweise um daraufhin mit einem sverweis zu arbeiten?
ISt das so möglich oder muss dann eine halbwegs gescheite Datenbank im Hintergrund laufen?
