Live-Forum - Die aktuellen Beiträge
Datum
Titel
26.04.2024 21:51:33
Idee für Grafik
26.04.2024 21:29:07
PQ zufällige Reihenfolge
26.04.2024 17:18:47
Große Datenmenge in komplexes Diagramm verpacken
26.04.2024 16:22:47
Aktualisierung Verknüpfung bei geschlossener Quelldatei
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1576to1580
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1576to1580
1576to1580
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Mehrere Bereiche vergleichen und zählen

Mehrere Bereiche vergleichen und zählen
27.08.2017 21:33:47
kesici
Hallo zusammen,
habe folgendes Problem:
Ich habe ein Liste mit mehreren Tausend Zeilen und mehreren Spalten. Zusätzlich habe ich eine weitere kleine
Liste, in der verschiedene Bezeichnungen stehen. Diese Bezeichnungen sind in 3 Kategorien unterteilt. Die erste
Kategorie hat 5 Werte, die zweite 16 und die dritte 5. Meine Aufgabe ist es, aus diesen 2 Tabellen eine neue
Tabelle zu erstellen.
Die Werte in der Spalte D aus der Tabelle 1 kommen in die Spalte A der neuen Tabelle. Die Werte in der Spalte E
aus der Tabelle 1 werden mit den Werten in Tabelle 2 verglichen und erst danach in der neuen Tabelle gezählt. Am
Ende sollen dann die Werte in der neuen Tabelle in gezählter Weise aufgelistet sein. Dabei gibt es in der ersten
Tabelle in SPalte D doppelte Werte, diese sollen in der neuen Tabelle jedoch nicht doppelt auftauchen. Sie sollen
zusammengefasst das Ergebnis anzeigen.
Hat jemand dafür eine Idee?
Hier eine Beispiel-Datei
https://www.herber.de/bbs/user/115816.xlsx

12
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Mehrere Bereiche vergleichen und zählen
28.08.2017 00:01:58
fcs
Hallo Kesici,
irgendwie passen die Werte in "EndTabelle" nicht zu den Rohdaten plus deiner Beschreibung wie die Spalte "Zustimmung" mit den 3 Kategorien im Blatt "Abgleich" verglichen werden soll.
Für eine einmalige DAtenaufbereitung ist ein Makro zu aufwendig.
Nach meiner Einschätzung kommt man den Excel-Bordmitteln zum Ziel.
1. Daten in "Rohdaten" in Tabelle umwandeln (via Menü "Einfügen"
2. Zusätzliche Spalte Kategorie" anfügen
3. Per Formel zu den Werten in "Zustimmung" die zugehörige Kategorie gemäß Werten in "Abgleich" ermitteln.
4. Aus Rohdaten eine Pivot-Tabellenbericht-Auswertung erstellen mit Zeilenbeschriftugen (Typ und Beschreibung), Spaltenbeschriftung (Kategorie), Datenfeld (Anzahl von Zustimmung).
5. Wenn dies nicht schön genug ist, dann die Daten der Pivot-Auswertung per Formel in einen "gehübschten" Zellbereich übernehmen.
Gruß
Franz
Deine Datei entsprechend aufbereitet:
https://www.herber.de/bbs/user/115818.xlsx
Anzeige
AW: Mehrere Bereiche vergleichen und zählen
28.08.2017 09:33:42
Emre
Hallo Franz,
ja, Du hast Recht, so wie ich es beschrieben habe passen die Werte nicht zusammen. Ich habe vergessen zu erwähnen, dass die Aufzählung durch die Werte in der Spalte Status gemacht wird. Es sollen nur die Werte gezählt werden, die den Status genehmigt haben. So sollte die Beispieldatei dann auch stimmen.
Die Datei von Dir passt jedoch genau zu dem was ich brauche, außer dem Punkt mit dem Status.
AW: Mehrere Bereiche vergleichen und zählen
28.08.2017 09:32:56
Emre
Hallo Franz,
ja, Du hast Recht, so wie ich es beschrieben habe passen die Werte nicht zusammen. Ich habe vergessen zu erwähnen, dass die Aufzählung durch die Werte in der Spalte Status gemacht wird. Es sollen nur die Werte gezählt werden, die den Status genehmigt haben. So sollte die Beispieldatei dann auch stimmen.
Die Datei von Dir passt jedoch genau zu dem was ich brauche, außer dem Punkt mit dem Status.
Anzeige
AW: Mehrere Bereiche vergleichen und zählen
28.08.2017 12:56:52
fcs
Hallo Emre,
ziehe im PivotTabellen-Bericht das Feld "Status" zusätzlich in den Seiten-Bereich und setze den Filter auf "genehmigt".
Da Excel dabei den Bericht nach unten verschiebt musst du links in der Tabelle die Formeln anpassen.
Gruß
Franz
Nachtrag: "Berichtsfilter", nicht "Seitenfeld
28.08.2017 13:06:30
fcs
Hallo Emre,
das Feld "Status" in den "Berichtsfilter" ziehen.
In früheren Excelversionen hies dieser Bereich "Seitenfeld oder -bereich".
Im englischen unter VBA heist er "PageField".
Gruß
Franz
AW: Nachtrag: "Berichtsfilter", nicht "Seitenfeld
28.08.2017 18:01:44
kesici
Hallo Franz,
die Originaldatei ist auf dem Computer im Büro, kann das erst am Donnerstag testen, aber in der Beispieldatei scheint es zu klappen.
Außerdem habe ich heute eine alternative Lösung gefunden, die fast alles macht was gefordert wird. Ich habe statt wie du es beschrieben hast keine Pivot-Tabelle, sondern die Funktion ZÄHLENWENNS verwendet. Dank Deines Hinweises mit den Verknüpfungen der Funktionen hat auch fast alles funktioniert. Es fehlt im Moment nur noch ein Teil. Zusammen mit der Pivot-Tabelle habe ich nun 2 Lösungen, vielen Dank dafür.
Ich kann mir in der EndTabelle bereits alle Werte aus den Rohdaten anzeigen. Das Problem ist ich möchte nicht, dass alles angezeigt wird. Es sollen nur die Werte in der EndTabelle erscheinen, die den Kategorien entsprechen. D.h. wenn ein Epic/Story/Task in der Tabelle Rohdaten in der Spalte "Zustimmung" einen Wert aus der Kategorie "Lesen" aus der Tabelle "Abgleich" hat, dann soll dieses Epic/Story/Task auch in die EndTabelle, ansonsten nicht.
Ich habe heute viele verschiedene Funktionen ausprobiert und bin immer an einem Punkt hängengeblieben. Ich habe nicht herausgefunden, wie man 2 Bereiche miteinander vergleicht. Es geht immer nur wenn man einen Bereich mit einer Zelle bzw. einem Wort vergleicht. Als Funktion für die Beispieldatei habe ich folgendes probiert:
=WENN(Rohdaten!E:E=Abgleich!A2:A6;Rohdaten!D:D;"")
Von der Logik her sollte das doch funktionieren, nur klappt es leider nicht!
Hättest Du da vielleicht einen Ansatzpunkt? Außerdem habe ich heute in der Originaldatei bemerkt, dass die Datei nun länger verarbeitet, da sie auch knapp 6000 Zeilen hat. Kann man dagegen etwas machen?
Anzeige
AW: Nachtrag: "Berichtsfilter", nicht "Seitenfeld
29.08.2017 07:46:13
fcs
Hallo Emre,
bei großen Datenmengen benötig Excel für bestimmte Funktionen sehr viel mehr Zeit.
Dazu gehören viele Funktionen mit Matix-Operationen und auch Verweis-Funktionen.
Im Blatt "Rohdaten" müssen in der zusätzlichen Spalte "Kategorie" bei 6000 Zeilen 18000 Verweise + 18000 WENN-Prüfungen + 6000 Verkettungen der Texte berechnet werden. Da wird Excel irgendwannvon der Masse der daten erschlagen.
Gegensteuern? Wenn sich die Ausgangsdaten in Rohdaten und Abgleich nicht ändern, dann kann man die Formeln nach der Neuberechnung per Kopieren und Werte einfügen durch durch ihre Werte ersetzen.
ggf. vorher eine Kopie des Blatts mit wenigen Zeilen und Formeln erstellen für spätere Wiederverwendung.
grundsätzlich kann man die Auswertung komplett per Formel machen. Wegen der vielen Datenzeilen sollte jedoch eine weiter Hilfsspalte in den Rohdaten eingefügtbelastet werden.
In dieser Spalte werden dann alle relevanten Datenzeilen ohne Dopplungen nummeriert für die weitere Auswertung. Aber die Berechnungszeit kann dann schon exessiv werden.
Der Grund weshalb ich die Variante über den Pivot-Tabellenbericht bevorzuge.
1. Er belastet die Datei nicht mit rechenaufwendigen Formeln
2. Die Berechnungen bei aktualisierung sind sehr schnell
3. Flexibel beim setzen der Filter
Gruß
Franz
hier noch deine Beispiel-Datei aktualisiert
https://www.herber.de/bbs/user/115846.xlsx
Anzeige
AW: Nachtrag: "Berichtsfilter", nicht "Seitenfeld
29.08.2017 10:46:10
kesici
Hallo Franz,
ich habe jetzt nicht ganz verstanden warum es eine EndTabelle-Pivot und eine EndTabelle gibt. Sehe da keinen Unterschied zwischen beiden Tabellen. Ist es wegen der Belastung und der Berechnungszeit?
Deine Lösung mit der Pivot-Tabelle ist sicher die Bessere, vor allem wegen der Berechnungszeit. Außerdem werden die Rohdaten aus einer Datenbank gezogen, das heißt die können an der Anzahl irgendwann bestimmt mal variieren. Wie davor schon gesagt, ich kann das erst wieder am Donnerstag testen, weil ich dann im Büro bin.
Aber zu meinem Beitrag davor: Gibt es eine Lösung zu meinem Problem mit dem Vergleich von 2 Bereichen. Ich hatte ja die Funktion: =WENN(Rohdaten!E:E=Abgleich!A2:A6;Rohdaten!D:D;"") angewendet. Kann man das irgendwie lösen.
Wenn das auch klappen würde, hätte ich 2 Varianten, die ich vorzeigen könnte. Die Pivot-Tabelle würde wahrscheinlich bevorzugt werden, aber ich habe für die andere Lösung mittlerweile so viel Zeit investiert, würde das ungern kurz vor dem Ende liegen lassen :)
Anzeige
AW: Nachtrag: "Berichtsfilter", nicht "Seitenfeld
30.08.2017 07:44:20
fcs
Hallo Emre,
ich habe jetzt nicht ganz verstanden warum es eine EndTabelle-Pivot und eine EndTabelle gibt. Sehe da keinen Unterschied zwischen beiden Tabellen. Ist es wegen der Belastung und der Berechnungszeit?
Im Blatt "EndTabelle" holen die Formeln ihre Daten aus dem Blatt "Rohdaten", Im Blatt "EndTabelle-Pivot" holen die Formeln ihre Daten aus dem Blatt "Pivot".
Aber zu meinem Beitrag davor: Gibt es eine Lösung zu meinem Problem mit dem Vergleich von 2 Bereichen. Ich hatte ja die Funktion: =WENN(Rohdaten!E:E=Abgleich!A2:A6;Rohdaten!D:D;"") angewendet. Kann man das irgendwie lösen.
Excel kann diese Auswertung in der gewünschten Form mit den Standard-Funktionen nicht durchführen.
Entweder man bekommt 0 oder falsche Werte geliefert. Ich hab auch verschiedene andere Varianten als Matrixformeln mit entsprechenden Prüfungen probiert. Es hat einfach nicht funktioniert.
Evtl.haben die Formel-Freaks auf www.excelformeln.de ja was passendes - ich hab jedenfalls nichts gefunden, dass alles auf einmal berechnen kann.
https://www.herber.de/bbs/user/115871.xlsx
Ich hab jetzt mit Hilfsspalten weiter experimentiert und eine Lösung gefunden, die auch bei vielen Datensätzen in "Rohdaten" und vielen Zeilen in der Endtabelle noch akzeptable Rechenzeiten (bis ca. 10 Sekunden auf meinem schon ein paar Jahre alten Notebook) liefert.
Dabei kopierst du einfach deine Daten als Werte in das Blatt "Rohdaten" (ein Datenimport sollte eigentlich auch funktionieren).
Im Blatt "EndTabelle" sind
  • in den Spalten "E:G" die Auswertung von bis zu 6500 Datenzeilen in "Rohdaten" vorbereitet.

  • in den Spalten B und C einige Hilfswerte berechnet, die in den anderen Tabellen und für bedingte Formatierungen in den Titelzeilen verwendet werden

  • die Tabelle mit den End-Ergebnisen enthalten. Die Größe (Zeilenzahl) dieser Tabelle muss du an die zu erwartende Anzahl an Ergebniszeilen anpassen - beim Testen hatte ich ca. 800 Zeilen vorbereitet - musste diese aber wieder löschen, damit die Dateigröße unter 300 kByte bleibt

    • In der Endfassung kannst du dann die Spalten B:M ausblenden, so dass nur noch die relevanten Ergebnisse angezeigt werden.
    Die Optimierung beruht jetzt darauf, dass
  • Zwischenergebnisse nur einmal berechnet werden und dann in anderen Spalten verwendet werden (z.B. Zeilen-Nr. in den INDEX-Funktionen)

  • WENN-Formeln prüfen, ob ein vorheriges Zwischen-Ergebnis einen "" lieferte, so das die komplexen Berechnungen im 2. Teil der WENN-Formeln dann nicht gemacht werden müssen.

    • Gruß
    Franz
    Anzeige
    AW: Nachtrag: "Berichtsfilter", nicht "Seitenfeld
    31.08.2017 17:59:33
    kesici
    Hallo Franz,
    es hat funktioniert, auch wenn ich ein paar Probleme mit den Anpassungen an die Originaldatei hatte, hat es geklappt. Die Bearbeitungszeiten sind auch sehr kurz, man merkt es kaum. Vielen Dank für Deine Hilfe. Ich hätte das niemals in der Art geschafft.
    Was mir aber jetzt noch aufgefallen ist, sind 2 Punkte, die ich bereits versucht habe zu lösen, leider mit geringem Erfolg.
    In den Rohdaten kann es sein, dass die Werte in der Spalte Zustimmung, die mit der Abgleichliste übereinstimmen am Ende zusätzliche durch ein Bindestrich getrennte Bezeichnungen haben. Bsp:
    - abc3 - task 1234
    - abc7 - task 6735
    - abc23 - task 7527
    Wie kann man diese Werte noch in die EndTabelle mitnehmen?
    Außerdem ist es möglich, dass man aus den Rohdaten nur die Werte nimmt, deren Werte in der Spalte Zustimmung mit denen der Abgleichliste übereinstimmen?
    Anzeige
    AW: Auswertung - Rohdaten mit Vergleichstab
    02.09.2017 08:10:55
    fcs
    Hallo Emre,
    Wie kann man diese Werte noch in die EndTabelle mitnehmen?
    Dann müssen die Werte der Spalte "Zustimmung" ebenfalls in eine Hilfsspalte im Blatt "EndTabelle" übernommen und dabei der Teil " - ..." abgetrennt werden.
    Die Formel in Spalte "Kategorie" muss angepasst werden; sie darf sich nicht mehr auf die Rohdaten beziehen, sondern muss die Werte aus der neuen Zusatzspalte "Zustimmung" beim Vergleich verwenden.
    https://www.herber.de/bbs/user/115948.xlsx
    Außerdem ist es möglich, dass man aus den Rohdaten nur die Werte nimmt, deren Werte in der Spalte Zustimmung mit denen der Abgleichliste übereinstimmen?
    Hier hab ich ein Verständnisproblem - denn genau das machen die Formeln ja.
    In meiner Beispieldatei gib es den Wert "abc4_XXX" - für diesen wird keine Kategorie angezeigt.
    Du musst mal genauer beschreiben, was du hier erreichen möchtest.
    Gruß
    Franz
    Anzeige
    AW: Auswertung - Rohdaten mit Vergleichstab
    03.09.2017 13:23:49
    kesici
    Hallo Franz,
    Hier hab ich ein Verständnisproblem - denn genau das machen die Formeln ja.
    In meiner Beispieldatei gib es den Wert "abc4_XXX" - für diesen wird keine Kategorie angezeigt.
    Du musst mal genauer beschreiben, was du hier erreichen möchtest.
    Damit habe ich gemeint, ob man nur die Werte aus den Rohdaten in der Endtabelle anzeigen lassen kann, die auch eine Übereinstimmung mit der Abgleichstabelle haben. Also nur die Werte aus den Rohdaten, deren Inhalt in der Spalte Zustimmung mit denen von der Abgleichstabelle übereinstimmen.
    Geht das? Wegen dem zusätzlichen durch den Bindestrich hinzugefügten Text, kann man das glaube ich nicht bei jedem Wert unterscheiden?
    Anzeige

    Beliebteste Forumthreads (12 Monate)

    Anzeige
    Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

    Beliebteste Forumthreads (12 Monate)

    Anzeige
    Anzeige
    Anzeige