Summe aus Werten aus einem Bereich mit Bereich als Suchkriterium.

ConVuzius · 11. April 2014

Hallo liebe Gemeinde,

ich stehe vor einem Problem, welches ich auch nach langem Überlegen und probieren nicht in den Griff bekomme.
Ich habe im moment eine Formel: =(WENN(ISTFEHLER(SVERWEIS($C$5;Tabelle1!$1:$65536;2));0;SVERWEIS($C$5;Tabelle1!$1:$65536;2)))+(WENN(ISTFEHLER(SVERWEIS($C$6;Tabelle1!$1:$65536;2));0;SVERWEIS($C$6;Tabelle1!$1:$65536;2)))+(WENN(ISTFEHLER(SVERWEIS($C$7;Tabelle1!$1:$65536;2));0;SVERWEIS($C$7;Tabelle1!$1:$65536;2)))

In Tabelle 1 stehen in der ersten Spalte 4-stellige Zahlen (letzte Zeile variabel, deshalb habe ich das komplette Arbeitsblatt als Bereich). Rechts daneben verschiedene Zahlen oder nichts. Ich summiere nun alle Werte aus der 2ten Spalte, wobei nur die Werte summiert werden wo die 4-stellige Zahl einer Vorgabe entspricht (hier C5,C6,C7). Mein Problem ist nun, dass ich diese Vorgaben variabel anpassen möchte, also mal nur einen Wert, mal 8, usw. ohne dass ich die Formel händisch anpassen muss.

Ich hoffe jemand versteht mich

Wäre toll wenn jemand eine Idee dazu hat.

Grüße
Con

Knufu · 11. April 2014

D.h. die Anzahl deiner Kriterien für die bedingte Summe variieren zwischen 1 und 8??

ConVuzius · 11. April 2014

Hallo Knufu,

nein, die Anzahl is beliebig.

LG

Edit: Ich glaub ich weiß worauf du hinaus willst. Aber einfach das ganze acht mal in jede Formel reinzukopieren geht wie gesagt nicht, da die Anzahl auch mal größer sein kann.

Knufu · 11. April 2014

Ich wüßte auf Anhieb nicht, wie man eine Formel baut, mit dem "Verdacht auf mögliche Summanden".

Die baust dir doch irgendwo sicher deine Tabelle (in deinem Bsp C5 - C7) mit den Suchkriterien... warum machst du dahinter nicht eine Zwischensumme und summierst am Ende alles auf?
Dort kannst du auch Formeln hinerlegen, die Null ergeben/ anzeigen, wenn kein Suchkriterium eingegeben wurde.

ConVuzius · 11. April 2014

Hallo,
das Problem ist, ich habe hunderte solcher Kriterienbereiche, in denen sich die Kriterien stets ändern. Im Moment werden immer die Formeln angepasst, was aber extrem mühsam ist. Was du meinst ist auch schwierig, da die Summen in der Tabelle 2 pro Spalte in Tabelle 1 differenziert werden, d.h. ich kann keine Zwischensummen nebendran bilden.... Ist schwierig zu erklären :/

bieneneber · 11. April 2014

Habe ich das richtig verstanden, dass du verschiedene elemente in einer liste hast:
spalte 1: schlüssel
spalte 2: wert

wenn du dann m schlüssel angibst, sollen alle dazugehörigen werte addiert werden, korrekt?

Eigtl müsste dann eine Pivot-Tabelle für dich reichen, oder nicht?

Knufu · 11. April 2014

Am besten ist wie immer eine anonymisierte Beispieltabelle.....

ConVuzius · 11. April 2014

Hallo bieneneber,

ja, eine Pivot macht genau das, funktioniert aber nicht, da die zu summierenden Daten schon aus einer Pivot kommen, die entsprechende Überschriften mitbringt und häufig aktualisiert werden muss...
Ich habs schon mit verschiedenen Matrixformeln versucht, kriegs aber einfach nicht hin...

bieneneber · 11. April 2014

wenn die Datenbasis bereits eine Pivot-Tabelle ist, dann kannst du anhand des Filters auf die erste Spalte das Gesamtergebnis der zweiten Spalte bilden, oder nicht?

Aber wie Knufu schon sagt, eine Beispieltabelle würde helfen...

ConVuzius · 11. April 2014

Hier mal Screenshots, wie die Tabellen beispielhaft Aussehen:

Tabelle 1:

Tabelle 2:

Edit:
Und hier noch ein Screen, wie ich es bisher hatte. Das geht aber nicht mehr, da ich wie gesagt sonst immer neue Spalten einfügen und die Formel anpassen müsste, wenn neue Suchkriterien hinzukommen.

bieneneber · 11. April 2014

Kannst du nicht die KUNDEN (Spalten in Tabelle 1) nach in den Datenbereich der Pivot ziehen und somit auf WGR und Kunde aggregieren (es gibt dann außer Ergebnis keine weiteren Spalten)? Dann hast du pro KUNDE und WGR den Wert. Und diese könntest du dann beliebig kombinieren.

Edit (nach deinem Edit):
du baust im Prinzip die Pivot nach. Du könntest auch die WGR in die Spalten nehmen und die KUNDEN auf die linke Seite, dann kannst du über die Filteroptionen auf gewisse WGR und/oder KUNDEN einschränken.

ConVuzius · 11. April 2014

Hallo bieneneber,

genau so wird es bisher gehandhabt.
Das Problem ist, dass hunderte Pivottabellen exisiteren, welche alle schon mit gewissen WGR voreingestellt sind und dann das Ergebnis auf Kundenbasis ausgeben. Diese Pivots müssen aber andauernd aktualisiert werden, was aufgrund der großen Datenmenge ziemlich lange dauert. Deswegen suche ich gerade eine Lösung, in der ich eine Pivottabelle habe und mir für alle Kunden und WGR-Kombinationen die Daten per Formel da rausziehen kann.

Edit: Zur Information: die Daten kommen aus einem SQl-Cube.

bieneneber · 11. April 2014

Schau' dir mal den Befehl PIVOTDATENZUORDNEN an, damit kannst du auf einzelne Ergebnisse der Pivot zugreifen. Wenn du in den Editiermodus einer leeren Zelle wechselst und nach der Eingabe des = einen Wert in der Pivot anklickst, belegt dir Excel dies automatisch vor.

Man könnte die Benutzer auch dazu konditionieren, die Filterfunktionen einer Pivot zu nutzen.

ConVuzius · 11. April 2014

Hi,

hm. Also das Problem hierbei ist dass ich nach den 4-stelligen Zahlen außen suchen muss, und diese !nicht! in der Pivot ausgegeben werden.
D.h. ich kann den Befehl nicht verwenden, da ich die Suchkriterien nicht zuordnen kann.

Aber danke schonmal an euch beide.

Achja, der Hauptnutzer bin ich ^^ ich will mir die Arbeit einfach ein bisschen erleichtern.

bieneneber · 11. April 2014

über die vierstelligen Zahlen bekommst du via SVERWEIS zumindestens das eine Suchelement heraus und kannst PIVOTDATENZUORDNEN evtl doch nutzen.

Das Problem ist nur, dass die Zuordnung WGR<->vierstellige Zahl ebenso variieren kann.

Aber mal was anderes: wenn die Daten aus einer externen Datenquelle kommen, kannst du noch versuchen, die Daten nicht als Pivot sondern als (mehr oder weniger dynamische) Tabelle einzubinden. Dann die vierstellige Zahl an die WGR via SVERWEIS oder manuell dranflanschen. Danach dann auf diese neue Tabelle eine Pivot aufsetzen, die dann deine Suchkriterien mittels Filter ermöglichen sollte.

ConVuzius · 11. April 2014

Hmmmm, raff ich ehrlich gesagt nich

Naja, ich hab mich jetzt damit abgefunden dass das nich so funktioniert wie ich mir das vorstelle.
Danke an euch beide.
Ich will aber auch nicht vorenthalten wie ich es jetzt machen werde: Hatte mir schon vor längerem eine UserForm im VBA erstellt mit der ich auf die Pivots zugreifen kann, und alle gleichzeit aktualisieren. Das geht zwar komfortabel, dauert aber wie gesagt ne Zeit lang, weils so viele sind... naja, geh ich halt in der Zeit Kaffee trinken

Suche

Summe aus Werten aus einem Bereich mit Bereich als Suchkriterium.

ConVuzius

Lieutenant

Knufu

Lieutenant

ConVuzius

Lieutenant

Knufu

Lieutenant

ConVuzius

Lieutenant

bieneneber

Ensign

Knufu

Lieutenant

ConVuzius

Lieutenant

bieneneber

Ensign

ConVuzius

Lieutenant

bieneneber

Ensign

ConVuzius

Lieutenant

bieneneber

Ensign

ConVuzius

Lieutenant

bieneneber

Ensign

ConVuzius

Lieutenant

Ähnliche Themen