Office: wöchentliche Auswertung umfangreicher Daten # gelöst

Hallo liebe Excel-Spezialisten,

ich habe eine sehr große Datei, die mir dazu dient, wöchentlich den gesamten Bestand auszuwerten.

Leider ist diese Datei sehr groß und ich würde die Auswertung gerne zusammenfassen.

In einer Spalte steht das Alter in Tagen (kann auch Gruppen erstellen), in einer anderen die Zuordnung zu den Warengruppen. Das Gewicht, der Wert und der Gewichtspreis stehen in jeweils eigenen Spalten (für alle Warengruppen und alle Altergruppen).

Ich benötige die Summe a) der Gewichte und b) der Werte nach Altergruppe UND Warengruppe. Geht das?

Aus der Spalte Preis pro KG benötige ich nach Warengruppe den Durchschnitt sowie min. und max. (auch nach Warengruppe). Geht das auch?

Mein Problem ist die der Bezug von SummeWenn auf eine/mehrere andere Spalten als Auswahlargument.

Schon mal danke im Voraus für Eure Hilfe.

mfg
Christian

P.S. die Datei hat über 10.000 Zeilen, daher möchte ich meine Tabelle "entschlacken". Zur Zeit erstelle ich für Gewich und Wert für jede Altersgruppe und Warengruppe ein eigene Spalte sowie eine Spalte für den Preis pro KG, (Dateigröße ~40 MB)[/list]

 

Hallo!

Die Funktion SUMMEWENN verträgt leider immer nur eine Bedingung. Bei mehreren Bedingungen solltest du auf das SUMMENPRODUKT ausweichen, z.B.:

=SUMMENPRODUKT((A1:A100="groß")*(B1:B100="rot")*(C1:C100="günstig")*D1:D100)

addiert die Preise (Spalte D) aller Dinge, die groß, rot und günstig sind.

Ein mächtiges Instrument zur Auswertung von Tabellen, die spaltenweise angeordnet sind, wäre in deinem Fall auch eine Pivot-Tabelle

 

Hallo liebe Spezis,

ich habe SummenProdukt versucht...

Wenn die Bedingung sich auf eine andere Spalte bezieht (Wert oder Range) funktioniert die Formel.

Code:

=SUMPRODUCT(($S$2:$S$11110<91)*($U$2:$U$11110))

Spalte S = Alter in Tagen
Spalte U = Produktgruppe zutreffend (1 oder 0)

Wenn ich aber verschiedene Spalten im Bezug habe, klappt nichts mehr.
(Office 2003 - english version)

Code:

#Value!

Code:

=SUMPRODUCT(($S$2:$S$11110<91)*($U$2:$U$11110=1)*($I$2:$I$11110))

Spalten S und U wie oben
Spalte I = Gewicht in KG

Gibt es hier eine Lösung? Oder muss ich doch mit getrennten Spalten arbeiten?

Ach ja, gibt es Bedingen auch für Min und Max?

Danke für Eure Hilfe

Christian

 

Hallo!

Eigentlich sollte die Formel funktionieren
=SUMPRODUCT(($S$2:$S$11110<91)*($U$2:$U$11110=1)*($I$2:$I$11110))

Addiert alle Werte aus Spalte I, bei denen Spalte S kleiner 91 UND Spalte U gleich 1 ist.

Möglicherweise beinhalten deine Bereiche nicht nur Zahlwerte sondern auch Texte, bzw. Zahlen im Textformat. Damit kann das SUMPRODUCT leider nicht umgehen. Das solltest du prüfen.

Du kannst auch bedingte Minima und Maxima bestimmen, z.B.

=MIN(IF(S2:S11110<91;I2:I11100))
Achtung Matrixformel: Die Eingabe der Formel mit der Tastenkombination Strg+Shift+Enter abschließen!

bstimmt das Minimum aus Spalte I, wenn Spalte S kleiner als 91 ist.

 

@ Schatzi

mal wieder besten Dank an Deine Fachkenntnisse.

Die min und max Funktion mit Bedingung funktioniert.

Code:

{=MIN(IF(($U$2:$U$11110=1);$Z$2:$Z$11100))}

{...} erscheint bei mir als Anzeige für die Matrix-funktion (Strg+Shift+Enter)

Spalte U = Produktgruppe zutreffend (1 oder 0) (wird aus der Artikelbeschreibung mittels geschachtelter wenn-Funktion bestimmt)
Spalte Z = Preis pro KG

Habe das SummenProdukt auch als Matrix versucht, aber auch hier nur der Fehler #Value!

Hier noch einmal eine der Formeln

Code:

=SUMPRODUCT(($S$2:$S$11110<91)*($U$2:$U$11110=1)*($I$2:$I$11110))

Spalte S = Alter in Tagen
Spalte U = Produktgruppe zutreffend (1 oder 0)
Spalte I = Gewicht in KG
Alle drei Spalten sind als Zahlen definiert (Zellenformat)

Ich verwende Office Professional Edition 2003 - english version mit ServicePack 3

Im Formel-Editor werden die einzelnen Bereiche mit Semikolon getrennt, wenn ich das mache, erhalte ich als Wert aber Null, was falsch ist (Wert ist recht groß).

Danke für Eure Hilfe

mfg
Christian

 

Seltsam, seltsam...

Vielleicht noch dieser Versuch:
Kopiere eine LEERE Zelle in die Zwischenablage (Strg+c)
Markiere die Spalten S, U und I
Wähle Menü Bearbeiten (Edit) > Inhalte einfügen (Paste special) > Vorgang: Addieren (Add?) > OK

Funktioniert die SUMPRODUCT-Formel nun?

 

@ schatzi

Danke für Deine Mühe, habe ich gemacht....

:-( leider ohne erfolgt.

Die Min(wenn(...)) Max(wenn(...)) und Durchschnitt(wenn(...)) funktionieren, aber Summeprodukt, nur mit Bezug auf EINE Spalte (liegt das vielleicht an Office 2003?

Anbei nochmal meine Formeln (english-version)

:lol: funktionierende Formeln
Anzahl der Einträge in der Materialgruppe nach Altersgruppen

Code:

=SUMPRODUCT(($S$2:$S$11110<91>90)*($S$2:$S$11110<181)*($U$2:$U$11110))

etc.
Durschschnittswarenwert in der Materialgruppe

Code:

{=AVERAGE(IF(($U$2:$U$11110=1);($Z$2:$Z$11110)))}

Min-Warenwert in der Materialgruppe

Code:

{=MIN(IF(($U$2:$U$11110=1);$Z$2:$Z$11100))}

Max-Warenwert in der Materialgruppe

Code:

=MAX(IF(($U$2:$U$11110=1);$Z$2:$Z$11100))

Formeln mit Fehlermeldung #Value! (Wert)
Materialmenge in der Materialgruppe nach Altersgruppen

Code:

=SUMPRODUCT(($S$2:$S$11110<91>90)*($S$2:$S$11110<181)*($U$2:$U$11110=1)*(I$2:I$11110))

etc.
Warenwert in der Materialgruppe nach Altersgruppen

Code:

=SUMPRODUCT(($S$2:$S$11110<91>90)*($S$2:$S$11110<181)*($U$2:$U$11110=1)*(K$2:K$11110))

etc.

(evt. nicht mehr nötig, aber der Vollständigkeit halber)
Die Spalteninhalte
S = Alter in Tagen (ganze Zahlen bzw. leere Zellen - von 360 auf 365 Tage modifiziert, nebst Transportabzug (365 Tage sind in diesem Excel nicht vorhanden))
U, V, W, X = Materialgruppe zutreffend (0 oder 1 bzw. leere Zelle)
I = Gewicht in KG (Zahlen bzw. leere Zellen)
K = Warenwert in Euro (Zahlen bzw. leere Zellen)
Z = Warenwert pro Gewichtseinheit (Zahlen bzw. leere Zellen)

Ich würde mich freuen, wenn mir doch noch jemand helfen könnte?!

mfg
Christian

 

Sorry, Fehler gefunden....

In den Spalten U,V,W und X standen immer Werte, auch wenn die anderen Spalten leer waren. Habe ich gelöscht und jetzt funktionieren die Formeln...

Danke für Eure Hilfe

 

Habe die Formeln in die Originaldatei übernommen (Reduzierung der Dateigröße von 47 auf 26 MB).

Leider funktionieren die SummenProdukt Formeln jetzt nicht mehr?!

Die Kalkulation fragt die Daten (anderes Tabellenblatt - Daten müssen erhalten werden) ab, wenn keine Daten vorhanden sind, wird ""(Formeldetail) eingesetzt (wenn-Formel mit Bezug auf IMMER gegebenen Daten der Datensätze).

Liegt hier der Fehler?

anbei ein Muster...

Code:

=IF(C2="";"";IF(I2<1;1;0))

Spalte C = IMMER gegeben, sofern Datensatz existiert
Spalte I = Warenwert in €
Manchmal bleiben Restmengen bei Buchungen übrig, die werden hier auf 1 gesetzt (vorbereitung für die Korrekturbuchungen).

Falls ja, welches Füllzeichen kann ich verwenden?

Danke für Eure Hilfe

Christian

 

Hallo!

Was passiert denn, wenn du statt SUMPRODUCT auch eine Matrixformel verwendest?

Statt z.B.

Code:

=SUMPRODUCT(($S$2:$S$11110<91)*($U$2:$U$11110=1)*($I$2:$I$11110))

nutzt du

Code:

=SUM(IF($S$2:$S$11110<91;IF($U$2:$U$11110=1;$I$2:$I$11110)))

Mit Strg+Shift+Enter abschließen.

 

:lol: :lol: :lol: :lol:

Juhuuuuu!!!!!!

Es funktioniert.

Danke Schatzi

Hätte vermutlich am Anfang mehr sagen sollen, dass hätte uns beiden Arbeit erspart, oder?!

mfg
Christian

 

Nö, die Idee hätte mir schon kommen müssen als du schriebst:

Wenn MIN und MAX funktionieren, muss logischerweise auch SUM funktionieren.
Aber immerhin hast du dadurch zwei Varianten kennen gelernt, auch wenn in deinem Fall nur eine funktioniert. :-D