Clever-Excel-Forum

Hi all,
etwas kenne ich mich mit Excel aus, jedoch nicht unbedingt perfekt. Ich scheiderte an folgendem Problem.
Ich habe eine Vielzahl an daten in einem Blatt. Diese Daten zusammenfassend würde ich auf einem anderem Datenblatt verwenden.
Hierzu nehme ich die entsprechenden Daten mittels folgender Formel:
=SUMMENPRODUKT(TEILERGEBNIS(3;INDIREKT("L"&ZEILE(5:15002)))*(ISTZAHL(SUCHEN(L3;L5:L15002))))

Kurz warum diese Formel. Ich habe in einer Zelle mehrere Informationen, welche ich nach bestimmten suchkriterien sozusagen "zählen" will, da jedoch die Daten gefiltert werden können bzw. müssen, kann dies nicht mit der klassichen zählen wenn und mit ** gesucht werden, da wenn der Filter eingesetzt wird, dieser nichts mehr anzeigt.

Nun müsste ich diese Daten eben nach 2 oder evtl. auch mehr, filtern können, aber in einer schönen übersicht worin mir dann gleich die Werte aus der oben genannten Formel angezeigt wird (wird dann auch prozentuall berechnet danach. Die Darstellung dieser Daten ist fix vorgegeben, nur oberhalb hiervon würde ich etwas wie ein Dropdown benötigen (evtl. über ein KOmbinationsfeld) der mir auf dem Datenblatt den Filter durchführt, aber vorne mir die Daten angezeigt werden.

Um es etwas vorstellbarer zu machen.
habe Spalten wie Supermarkt, Ort, Warenkorb.
die Daten für den Warenkorb, da sehr viele kommen alle in einer Zelle, deshalb auch oben die Formel, eine exakte Zellenteilen ist nicht möglich, da der Warenkorb immer pro Zeile und Zelle unterschiedlich ist.

Nun möchte ich auf dem Übersichtsblatt den Filter haben. Bei dem ich aussuchen kann Rewe München und drunter zählt er mir dann nur die gefilterten Daten. Beispielhaft
Eier 1000
Bio Eier 400
Kaviar 50
Moet 90

Heißt, aus allen Rewe in München bieten 1000 Supermärkte Eier an, 400 hingegen nur BioEier. Der Warenkorb, der in einer Zelle ist ist dann Eier, Bio Eier, Kaviar, Moet, ...,...,...,...,...,...,....,.... . Ich hatte es schon versucht mit Zelle in Spalten Trennen und nach jedem , dieser eine neue Spalte macht, jedoch kann ich keine Pivot hieraus erstellen, da in einem Warenkorb Die Eier zuerst auftzauchen, somit i Spalte B und im nächsten Warenkorb später und somit in Spalte BK. daher auch nicht einfach sortierbar. Deshalb mit der Suchen/Finden Formel, da sich der Waren korb ändern kann und somit durch diese Formel dies am einfachsten ist. Zumindest aus meinem Kenntnissstand.

Die Zahlen sind natürlich fiktiv und auch nicht das parade beispiel. Soll jedoch veranschaulichen worauf ich hinaus möchte.

Falls dies nur via VBA geht, würde ich hier etwas support für den code benötigen.

VIelleicht kann mir hier jemand einen TIpp geben.

Danke vorab.

Hi,

bei dem nicht ganz kurzen Sachverhalt wäre eine Beispieldatei als Ergänzung zu deinen Beschreibungen sinnvoll / nötig.  

http://www.clever-excel-forum.de/thread-326.html

Gruß Elex

Hallo,

anbei eine Datei. Wobei die beschriebene Formel ich nicht geschafft habe in einem anderem Tabellenblatt darzustellen auf die schnelle, weiß nicht wo der Fehler liegt. Aber denke auch so könnte es helfen.

Der gelbe Bereich soll auf einem eigenem Tabellenblatt stehen, die datenquelle auf einem anderem. Ich brauch aber eine Art Filter via Dropdown wie man es klassisch kennt, aber in dem gelben bereich. Heißt wenn ich das da auswähle, das er mir die Werte im anderem Blatt filtert und ich das Ergebnis nur sehe.

Zuerst hatte ich es versucht mit Zelle in Spalte und dann nach jeder Kommastelle, was fix ist, eine Neue Spalte zu erstellen. Hier war die Intension, via Pivot-Tabelle dies dann einfach mit den Bereichsfiltern dann auszuwählen, geht aber nicht, da der zellen Inhalt unterschiedlich ist und somit auch nicht alle gleichen werte in einer Spalte dargestellt werden.
Falls es eine Pivot-Tabellen lösung gibt wäre dies natürlich noch besser.

Die Datenmenge, die hier dargestellt wurde im File ist nur ein Bruchteil von den Tatsächlichen, geht über 10.000 Zeilen und 50 Spalten, wobei der "filter" nur auf zwei Spalten zurückgreifen muss. Das Supermarkt beispiel soll nur veranschaulicht wirken.


VG
Marko

Hi

Hilft das weiter?
[attachment=16470]

Hi Elex,

danke, sieht schon mal super aus. Danke für den Hinweis mit Suchen/Finden. es werden immer exakte treffer sein und jetzt weiß ich es nicht mehr bei welcher, ob bei suchen oder finden, hat es richtig lange gedauert bis dies kalkuliert hat.

Nun die Frage, wenn die Gesamtanzahl für Edeka interessiert ohne Städte kann nach deiner Datei kein Wert abgegeben werden. Was muss hinzugefügt werden, wenn nichts ausgewählt wird, dass hier die Totale Summe herauskommt?

VG
und nochmals besten Dank für den Support.

Hi

B3 oder B4 einfach leer lassen.

B7     =WENN($B$3="";SUMMENPRODUKT((Daten!$F$16:$F$54=$B$4)*(ISTZAHL(FINDEN(A7;Daten!$G$16:$G$54))));WENN($B$4="";SUMMENPRODUKT((Daten!$E$16:$E$54=$B$3)*(ISTZAHL(FINDEN(A7;Daten!$G$16:$G$54))));SUMMENPRODUKT((Daten!$E$16:$E$54=$B$3)*(Daten!$F$16:$F$54=$B$4)*(ISTZAHL(FINDEN(A7;Daten!$G$16:$G$54))))))

Die Variante sieht zwar lang aus, sollte aber die Performenz nicht noch weiter belasten.
Wie lange dauert die Berechnung aktuell?

Gruß Elex

Hi,

TOP.
sieht perfekt aus. Werte die Tage diese Formel dann für die Große Datei anwenden und mit den einzelnen Suchkriterien aufstellen, dann werde ich mal sehen, wie lang die Berechnung dauert. Werde die Datei auch wahrscheinlich als binärdatei abspeichern, da ich meistens hiermit arbeite. 

Besten Dank für diesen Lösungsansatz.

Hi,
nun muss ich nochmal nachhacken. Ich verstehe folgenden Teil nicht:

ganze formel:
=WENNFEHLER(INDEX(Daten!F$16:F$54;KKLEINSTE(WENN(ZÄHLENWENN(H$5:H5;Daten!F$16:F$54)=0;ZEILE($A$16:$A$54)-15);1););"")

diesen Abschnitt der Formel:
ZEILE($A$16:$A$54)-15);1););"")


Dies ist ein leeres feld worin ich keine Verknüpfung sehe. Wenn ich das auf mein Beispiel versuche, erhalte ich immer den gleichen Begriff wenn ich die Formel den Zellen entsprechend anpasse. Er spuckt mir nicht so wie in deinem Beispiel die unterschiedlichen Begriffe auf.

Hi

Sagt dir der Begriff Matixformel etwas. Du musst die Formel mit Strg+shift+Enter abschliessen.

https://support.office.com/de-de/article...361075954d

Hi das Sagt mir was, nur die "berechnung" am ende mit -15);1); );"") war nicht so klar, habe mich da rumprobiert und per zufall hat es geklappt. 

Wenn ich nun nach der Dropdown auswahl die anzahl der gesuchten Begriffe habe, kann ich dann auch irgendwie die Summe der oben ausgesuchten Kriterien bilden. Also wenn ich ich oben Alle Edekas in München ausgewählt habe, das er mir diese Anzahl in Total anzeigt und ich dann im Anschluss eine Prozentualle Rechnung in den Folgenden Spalten errechnen kann.

Wenn ich weitere Dropdowns hinzufügen würde, dann müsste ich die Formel nur entsprechend erweitern, also die wenn formel.