Top-Themen

AppleEntwicklungHardwareInternetLinuxMicrosoftMultimediaNetzwerkeOff TopicSicherheitSonstige SystemeVirtualisierungWeiterbildungZusammenarbeit

Aktuelle Themen

Administrator.de FeedbackApache ServerAppleAssemblerAudioAusbildungAuslandBackupBasicBatch & ShellBenchmarksBibliotheken & ToolkitsBlogsCloud-DiensteClusterCMSCPU, RAM, MainboardsCSSC und C++DatenbankenDatenschutzDebianDigitiales FernsehenDNSDrucker und ScannerDSL, VDSLE-BooksE-BusinessE-MailEntwicklungErkennung und -AbwehrExchange ServerFestplatten, SSD, RaidFirewallFlatratesGoogle AndroidGrafikGrafikkarten & MonitoreGroupwareHardwareHosting & HousingHTMLHumor (lol)Hyper-VIconsIDE & EditorenInformationsdiensteInstallationInstant MessagingInternetInternet DomäneniOSISDN & AnaloganschlüsseiTunesJavaJavaScriptKiXtartKVMLAN, WAN, WirelessLinuxLinux DesktopLinux NetzwerkLinux ToolsLinux UserverwaltungLizenzierungMac OS XMicrosoftMicrosoft OfficeMikroTik RouterOSMonitoringMultimediaMultimedia & ZubehörNetzwerkeNetzwerkgrundlagenNetzwerkmanagementNetzwerkprotokolleNotebook & ZubehörNovell NetwareOff TopicOpenOffice, LibreOfficeOutlook & MailPapierkorbPascal und DelphiPeripheriegerätePerlPHPPythonRechtliche FragenRedHat, CentOS, FedoraRouter & RoutingSambaSAN, NAS, DASSchriftartenSchulung & TrainingSEOServerServer-HardwareSicherheitSicherheits-ToolsSicherheitsgrundlagenSolarisSonstige SystemeSoziale NetzwerkeSpeicherkartenStudentenjobs & PraktikumSuche ProjektpartnerSuseSwitche und HubsTipps & TricksTK-Netze & GeräteUbuntuUMTS, EDGE & GPRSUtilitiesVB for ApplicationsVerschlüsselung & ZertifikateVideo & StreamingViren und TrojanerVirtualisierungVisual StudioVmwareVoice over IPWebbrowserWebentwicklungWeiterbildungWindows 7Windows 8Windows 10Windows InstallationWindows MobileWindows NetzwerkWindows ServerWindows SystemdateienWindows ToolsWindows UpdateWindows UserverwaltungWindows VistaWindows XPXenserverXMLZusammenarbeit
GELÖST

Auswertung einer Exceltabelle

Frage Microsoft Microsoft Office

Mitglied: Xenome

Xenome (Level 1) - Jetzt verbinden

20.01.2010 um 09:55 Uhr, 10597 Aufrufe, 13 Kommentare

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.
Mitglied: 2hard4you
20.01.2010 um 11:04 Uhr
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
Bitte warten ..
Mitglied: Xenome
20.01.2010 um 13:20 Uhr
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.
Bitte warten ..
Mitglied: 83928
20.01.2010 um 13:44 Uhr
Hi,
Wenn ich das jetzt richtig verstanden habe kann man es doch gut mit einer Pivottabelle erschlagen,....
Bitte warten ..
Mitglied: Xenome
20.01.2010 um 15:41 Uhr
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.
Bitte warten ..
Mitglied: Fraenk
20.01.2010 um 16:19 Uhr
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")
Bitte warten ..
Mitglied: 2hard4you
20.01.2010 um 20:01 Uhr
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.

Dann sag das Deinem Chef und gehe zur Schulung ...

24
Bitte warten ..
Mitglied: Xenome
21.01.2010 um 00:03 Uhr
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]
Bitte warten ..
Mitglied: dievonnebenan
23.01.2010 um 16:44 Uhr
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
Bitte warten ..
Mitglied: Xenome
26.01.2010 um 23:48 Uhr
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.
Bitte warten ..
Mitglied: dievonnebenan
27.01.2010 um 22:39 Uhr
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...
Bitte warten ..
Mitglied: Xenome
08.02.2010 um 12:26 Uhr
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
>=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?
Bitte warten ..
Mitglied: Biber
08.02.2010 um 20:08 Uhr
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
Bitte warten ..
Mitglied: Xenome
09.02.2010 um 08:17 Uhr
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?
Bitte warten ..
Neuester Wissensbeitrag
Windows 10

Powershell 5 BSOD

(8)

Tipp von agowa338 zum Thema Windows 10 ...

Ähnliche Inhalte
Windows 10
Brauche Hilfe bei BlueScreen Auswertung! (10)

Frage von SgtBurner zum Thema Windows 10 ...

Datenbanken
SQL Auswertung 2 Joins und SUM

Frage von c64b311ee9 zum Thema Datenbanken ...

Batch & Shell
Powershell Performanceproblem bei Auswertung einer XML-Datei (1)

Frage von Kraemer zum Thema Batch & Shell ...

Heiß diskutierte Inhalte
Microsoft
Ordner mit LW-Buchstaben versehen und benennen (21)

Frage von Xaero1982 zum Thema Microsoft ...

Netzwerkmanagement
gelöst Anregungen, kleiner Betrieb, IT-Umgebung (18)

Frage von Unwichtig zum Thema Netzwerkmanagement ...

Windows Update
Treiberinstallation durch Windows Update läßt sich nicht verhindern (17)

Frage von liquidbase zum Thema Windows Update ...

Windows Tools
gelöst Aussendienst Datensynchronisierung (12)

Frage von lighningcrow zum Thema Windows Tools ...