Office: (Office 365) Summenverweis auf andere Datei

Hallo Zusammen,

ich habe mehrere Tabelle, welche aus anderen Programmen generiert werden. Für die Übersichtlichkeit, habe ich die ganzen Daten in einer separaten Tabelle zusammengefasst. Dazu werden die entsprechenden Werte in den einzelnen Tabellen abgegriffen und in der neuen Tabelle summiert. Das ganze hat wie gewohnt mittels SUMMEWENNS funktioniert.

Nachdem ich die Datei gespeichert, geschlossen und wieder erneut geöffnet habe, war alles wie gewohnt. Nachdem ich die Tabelle jedoch bearbeitet habe (z.B. die Gruppierung geöffnet), sind die Werte plötzlich nicht mehr abgreifbar. An den Dateinamen, Daten oder Pfaden hat sich nichts verändert. Das ganze habe ich auf OneDrive, Lokal und unserem Server versucht.

Ich hoffe mir kann jemand helfen. Vielen Dank!

 

Hola,
bei Summewenns() muss die Quelldatei geöffnet sein, sonst kommt #WERT.
Summenprodukt() kann auch mit geschlossenen Dateien umgehen.
Gruß,
steve1da

 

Hallo steve1da,

danke für die schnelle Antwort. Jedoch bin ich mit der Formel noch überhaupt nicht vertraut.

Ich habe in meiner ersten Datei mehrere Werte, nach denen ich Filtern muss. Z.B.:
(Tabelle 1 = Übersichtstabelle; Tabelle 2 = Zieltabelle, aus der die Werte abgegriffen werden)
Tabelle 2 Spalte G enthält Begriffsfragment "Rohr"
Tabelle 2 Spalte D = Tabelle 1 B9
Tabelle 2 Spalte H = Tabelle 1 E12
Wenn diese Parameter stimmen, soll die Summe aus Tabelle 2 Spalte AP genommen werden.

Ich hoffe ich konnte es verständlich erklären. Vielen Dank!

 

=summenprodukt((links($G$2:$G$1000;4)=$B$9)*($D$2:$D$1000=$C$9)*($H$2:$H$1000=E12))/1000
Datei- und Tabellennamen wie bei deiner Formel einfach vor die Bereiche setzen. Hab ich jetzt nicht gemacht, weil ich keine Lust hatte das abzutippen bzw. Bildchen nachzubauen.
Bei Summenprodukt() sollte man ganze Spalten in der Formel vermeiden.

 

Hi,

im Grunde geht das so:
=SUMMENPRODUKT(Summenbereich*(Kriterienbereich1=Kriterium1)*(Kriterienbereich2=Kriterium2)*....)
Allerdings solltest du keine ganzen Spalten verwenden sonst rechnet sich Excel einen Wolf.

Die Bereich müssen alle gleich groß sein. Soll heißen jede Vektor-Richtung muss gleich groß sein. Z.B. ist 10 Zeilen * 1 Spalte, 1 Zeile * 5 Spalten und 10 Zeilen * 5 Spalten durchaus erlaubt.

Statt dem Gleichheitszeichen kann auch jeder andere Vergleich wie <>, >, >= etc. stehen. Wichtig ist nur, dass WAHR oder FALSCH bzw. 0 oder 1 raus kommt.
Das gilt so Allgemein, dass man selbst so etwas wie ((A1:A10=1)+(A1:A10=2)) verwenden kann um auszudrücken, dass A1:A10 den Wert 1 oder den Wert 2 haben müssen, damit diese Zeile einbezogen wird.
Merke * entspricht UND, + entspricht ODER. Gerechnet wird wie üblich mit "Punkt vor Strich".

Überhaupt kann man bei den Vergleichen auch echte Formeln und nicht nur einfache Vergleiche verwenden. So ist in den Klammern so etwas wie (REST(MONAT(A2:A10);2)=1), um jeden ungeraden Monat eines Datums zu erhalten, durchaus erlaubt.

 

Guten Morgen,

vielen Dank für eure Beiträge, aber ich werde scheinbar nicht ganz schlau daraus.
Ich habe jetzt mal ganz stupide - wenn ich es auch nicht ganz verstehe - meinen Pfad vor die Formel von steve1da gesetzt (und an die beigefügte vereinfachte Tabelle angepasst). Das Ergebnis beträgt hier für "Hauptleitung - DN40" 0,004, sollte aber eigentlich 6,79 betragen.

Nach ein wenig Recherche, werden die Zeilen ja nicht addiert, sondern multipliziert, oder habe ich das falsch verstanden? Macht ja auch von der Bezeichnung summenPRODUKT her Sinn - wäre dann aber nicht das was ich brauche.

Tut mir leid das ich derart auf dem Schlauch stehe. Ich habe der einfachhalthalber mal beide Tabellen stark reduziert und beigefügt. Vielleicht kann mir nochmal jemand helfen? Vielen Dank!

PS: Die Ursprungstabellen auf die ich zugreifen werde, haben weit über 20.000 Zeilen.

 

Sorry, hatte den Bereich vergessen der summiert werden soll.
=SUMMENPRODUKT((LINKS('[Mengenbericht-Sprinkler.xlsx]Alle Daten'!$G$2:$G$38;4)=$B$3)*('[Mengenbericht-Sprinkler.xlsx]Alle Daten'!$D$2:$D$38=$C$3)*('[Mengenbericht-Sprinkler.xlsx]Alle Daten'!$H$2:$H$38=E7)*('[Mengenbericht-Sprinkler.xlsx]Alle Daten'!$J$2:$J$38))/1000
Bei Summenprodukt() wird zwar multipliziert, aber nur Wahrheitswerte. Jede Bedingung ist entweder erfüllt (=1) oder nicht erfüllt (=0). Das wird dann Zeile für Zeile geprüft und wenn eine Zeile alle Bedingungen erfüllt, also 1*1*1, dann wird die Zahl aus Spalte J zur Summe hinzugezogen.

 

Tausend Dank! Jetzt funktioniert es wie es soll! Vielen Dank!

 

Hallo steve1da,

tut mir leid das ich nochmal fragen muss, aber ich versuche wirklich die Formel zu verstehen. Bei den Leitungen konnte ich die Formel so anpassen, dass alles gefunden wurde. Bei den Bögen muss ich ähnlich filtern, nur dass ich nicht in der Spalte "E" nach z.B. "Hauptleitung" suche, sondern in der Spalte "E" nach "Hauptleitung" suchen muss, wobei es hier auch Eintragungen wie z.B. "Hauptleitung (1) gibt.

Daher habe ich die Formel "LINKS" wie von dir angewendet auch bei der Suche genutzt. Jedoch wird mir eine leere Zelle ausgegeben.

Spalte G = B36 (Bogen)
Spalte E = C36* (Hauptleitung) - Werte stehen auch als "Hauptleitung (1)" oder "Hauptleitung (2)", etc. in Spalte E
Spalte H = E39 (DN)

=SUMMENPRODUKT((LINKS('[Mengenbericht-Sprinkler.xlsx]Alle Daten'!$G$2:$G$20000;4)=$B$36)*(LINKS('[Mengenbericht-Sprinkler.xlsx]Alle Daten'!$D$2:$D$20000;4)=$C$36)*('[Mengenbericht-Sprinkler.xlsx]Alle Daten'!$H$2:$H$20000=E38)*('[Mengenbericht-Sprinkler.xlsx]Alle Daten'!$J$2:$J$20000))

Für mein Verständnis:
LINKS = Wert wird mit angegebener Anzahl der Buchstaben von links ermittelt? Es wird in diesem Falle also nach den ersten 4 Buchstaben von links gesucht?
Bei SUMMENPRODUKT stehen zuerst die Suchwerte und am Ende die Spalte, in der die Summe gebildet wird?

 

Bei Links() wird die Anzahl der Zeichen von links betrachtet. Wenn du als "hauptleitung" suchst, ist 4 falsch weil das "haup" wäre.
Die Reihenfolge der Eintragung ist bei Summenprodukt() egal. Ich habs mir so angewöhnt: erst die Bedingungen, dann den Bereich der summiert werden soll.

 

Also müsste ich den Wert 5 für den Bogen, und den Wert 12 für Hauptleitung eingeben:

=SUMMENPRODUKT((LINKS('[Mengenbericht-Sprinkler.xlsx]Alle Daten'!$G$2:$G$20000;5)=$B$36)*(LINKS('[Mengenbericht-Sprinkler.xlsx]Alle Daten'!$D$2:$D$20000;12)=$C$36)*('[Mengenbericht-Sprinkler.xlsx]Alle Daten'!$H$2:$H$20000=E38)*('[Mengenbericht-Sprinkler.xlsx]Alle Daten'!$J$2:$J$20000))

Aber auch hier bleibt die Zelle leer. Wie bekomme ich die Variable " (1)" dazu? Diese habe ich ja sowohl bei Hauptleitung (Spalte E) als auch bei den Bögen (Spalte G).

 

a) wenn die Zelle leer bleibt helfen Bilder nicht weiter
b) wenn du "Hauptleitung (1)" abfragen willst musst du natürlich auch die Zahl bei Links() anpassen

 

Bitte entschluldige, anbei nochmal beide Dateien.

Zu B) - Es soll nicht nach "Hauptleitung (1)" gesucht werden, sondern nach "Hauptleitung + X", wobei X eine Variable ist.

 

Du suchst den Wert aus E3 in Spalte D, der steht aber in Spalte E.
Wenn nach "Hauptleitung + X" gesucht werden soll, dann reicht doch die Abfrage auf die ersten 12 Zeichen. Dann ist jede Variante mit drin.
=SUMMENPRODUKT((LINKS('[Mengenbericht-Sprinkler (1).xlsx]Alle Daten'!$G$2:$G$20000;5)=B3)*(LINKS('[Mengenbericht-Sprinkler (1).xlsx]Alle Daten'!$E$2:$E$20000;12)=C3)*('[Mengenbericht-Sprinkler (1).xlsx]Alle Daten'!$H$2:$H$20000=E4)*('[Mengenbericht-Sprinkler (1).xlsx]Alle Daten'!$J$2:$J$20000))

 

Das ist natürlich ein dusseliger Fehler. Aber ich habe ihn einfach vor lauter Formel nicht gesehen...

Danke!