Komplexe Formel in Excel

Computer: Software & Programmierung Office Software
#1

Hallo.
Ich versuch mich gerade an einer Formel in Excel.
Jedoch ist es mit den Formeln nicht so einfach. Evtl. kann jemand von euch helfen:
Ich bekomme eine Auswertung mit folgendem Inhalt:
Spalte 1 Spalte 2 Spalte 3 Spalte 4
11 AB RE 15,3
12 MS RE 2,3
13 AB IR 0,3 u.s.w.

Nun habe ich eine Zweite Exeltabelle, welche folgenden Aufbau hat:

IR RE
AB
MS

Nun soll Excel mir meine Arbeit wie folgt erleichtern:
In der Auswertung überprüfen, in welcher Zeile Spalte 2 = AB und Spalte 3 = RE ist.
Im Beispiel Zeile 11. Nun soll der Wert aus Spalte 4 der Zeile 11 in die zweite Tabelle
in die Zelle RE/AB hinzugezählt werden.

Ist schwierig zu erklären, aber ich hoffe, das jemand einen hilfreichen Ansatz für mich hat.
Der Aufbau der Auswerung ist immer gleich, jedoch ändert sich die Zeilenzahl.
Daher muss die Gesamte Tabelle durchsucht werden.

Mein Denkansatz: In der 2. Tabelle Spalte AB/RE folgende Formel:
Wenn Spalte2=AB und Spalte3=RE dann + Spalte4

Komm aber nicht wirklich weiter, da ich noch nicht so viel mit Excel-Formeln zu tun hatte.
Danke für eure Hilfe.

LG

#2

Hallo,
stelle die Frage bitte mal an einen anderen.
MfG
BFR

#3

Hallo Ralf,
sieh Dir dazu mal die Hilfe zur Funktion SummeWennS an.
Ansonsten ist nachsehen auf www.excelformeln.de immer eine gute Idee.

Aus deiner Beschreibung kann ich Dir leider kein konkrete Hilfe geben, dazu fehlt mir das Ziel, was Du erreichen willst.

Gruß
Vincenz

#4

Hallo, wenn ich das richtig verstehe, willst du in der 2. Tabelle nur 4 Summen bilden:

  1. AB/IR
  2. AB/RE
  3. MS/IR
  4. MS/RE
    Die Werte dazu sollen aus Tabelle1 kommen, wobei dort variabel viele Zeilen stehen können.

Wenn das so ist, dann gibt es eine Formel, die du in jedes der 4 Ergebnisfelder einträgst.

In meinem Beispiel gehe ich davon aus, dass die Ergebnisse in der gleichen Tabelle wie die gelieferten Daten liegen (andernfalls musst du die Bezüge anders machen). Beachte die Dollarzeichen zwischen den Bezügen. Damit machst du den Bezug absolut, das heißt, selbst wenn du die Formel verschiebst, greift sie stets auf den gleichen Bereich zu. Außerdem gehen die Formeln davon aus, dass die Daten in der Zeile 2 beginnen. Auch wenn deine Daten nicht bis Zeile 9999 gehen, funktioniert die Formel trotzdem. Du musst also die Formel nicht immer anpassen.

  1. =SUMMEWENNS($C$2:blush:C$9999;$A$2:blush:A$9999;„AB“;$B$2:blush:B$9999;„IR“)
  2. =SUMMEWENNS($C$2:blush:C$9999;$A$2:blush:A$9999;„AB“;$B$2:blush:B$9999;„RE“)
  3. =SUMMEWENNS($C$2:blush:C$9999;$A$2:blush:A$9999;„MS“;$B$2:blush:B$9999;„IR“)
  4. =SUMMEWENNS($C$2:blush:C$9999;$A$2:blush:A$9999;„MS“;$B$2:blush:B$9999;„RE“)

Lies am besten diese komplexe Formel SUMMEWENNS nach. Beachte: nicht WENN sondern WENNS.
Zum Aufbau: Zuerst gibst du den Bereich an, der addiert werden soll, in dem also die Werte stehen - bei mir ist es die Spalte C.
Dann gibst du das erste Suchkriterium ein. Dazu ist ebenfalls der gesamte Bereich - bei mir ist es die Spalte A - und den gesuchten Wert an.
Danach ist das zweite Suchkriterium mit der Spalte B dran.

Wenn es zu kompliziert oder unverständlich ist, dann schreibe mich gerne an. Bei mir funktioniert es!

#5

bin leider im Ausland und kann daher nicht helfen

#6

Hallo Ralf,
wenn ich die Aufgabe richtig verstanden habe, mach’ folgendes:

  1. In der 1. Tabelle in Spalte 5 folgende Formel eingeben: =WENN(UND(B15=„AB“;C15=„RE“);1;0)
  2. Diese Formel über alle Zellen nach unten ziehen.
  3. In der 2. Tabelle gibst Du in die gewünschte Zelle folgende Formel ein: =summewenn(‚Tabelle1‘!E:E;1;‚Tabelle1‘!D:smiley:)

Ich hoffe ich konnte Dir weiterhelfen. Diesa

#7

Hallo Ralf,

dass sich die Werte in Zeile 11, 12 usw. befinden, ist m.E. unbedeutend, da ja lediglich der Wert der vierten Spalte addiert werden soll. Hinweis: In der Zelle „RE/AB“ kannst Du nichts hinzuzählen, d.h. addiert wird in der Spalte daneben. Du kannst nur Formeln oder einen Wert in einer Zelle vorgeben … es sei denn, Du hast Programmierkenntnisse … :wink:

Du kommst - wenn ich Dein Anliegen richtig verstanden habe - grundsätzlich mit der SUMMEWENN-Funktion weiter.

Das Problem hierbei ist allerdings, dass zwei Bedingungen erfüllt sein sollen, das geht mit der SUMMEWENN-Funktion, so wie Excel sie zur Verfügung stellt, nicht ohne Weiteres.

Schau mal unter www.excelformeln.de und zwar hier:

http://www.excelformeln.de/formeln.html?welcher=61

Wenn Du diese Formel anwendest, müsstest Du aus meiner Sicht zum richtigen Ergebnis kommen. Hinweis:

Das ist keine Formel im eigentlichen Sinne, sondern ein sog. Array. Du gibst die Formel ohne { am Anfang und } am Ende ein und erwirkst die geschweiften Klammern, indem Du Strg+Shift+Enter gleichzeitig drückst, also erst Strg drücken, festhalten, Shift-Taste drücken und auch festhalten und dann Enter.

Ich hoffe, ich habe Dien Problem richtig verstanden und konnte helfen. :smile:

Servus,
Robert

#8

Hallo Ralf,
versuch es mal mit der Funktion Summenprodukt:
Tabelle1

  1. Kriterium in Spalte A
  2. Kriterium in Spalte B
    Zahl in Spalte C

Formel in Tabelle2
=SUMMENPRODUKT((Tabelle1!$A$2:blush:A$100=„AB“)*(Tabelle1!$B$2:blush:B$100=„RE“)*Tabelle1!$C$2:blush:C$100)

Gruß
Brandis

#9

Ich habe das Problem nicht ganz verstanden: Soll in der zweiten Tabelle der Wert aus Spalte 4 angezeigt werden, wenn Spalte 2 und Spalte 3 übereinstimmen, oder kommt die Paarung in Sp 2 und Sp 3 öfters vor und in Sp 4 soll zusammengezählt werden, wenn die richtige Kombination vorhanden ist?

#10

Wenn ich Dich richtig verstanden habe, willst Du die Werte addieren, die AB und RE in den jeweiligen Spalten haben. Mein Ansatz ist wie folgt. Füge in der Spalte 5 folgende Formel ein: = Wert Sp. 2 & Wert Sp. 3.
Dann erhälst Du den Wert ABRE. Dann arbeitest Du mit der Funktion „Summewenn“
=SUMMEWENN(E:E;„abre“;D:smiley:)
Das sollte es sein.
Gruß Frank

#11

Hallo Ralf,

verstehe ich dich richtig, das die Abfrage immer 2 Buchstabenpaare abfragen soll?
Kommt diese Paare immer nur 1 mal vor? oder doppelt?

Das klingt für mich für den Anfang nach Matrixformel und hinter mit wenn.

Ich müsste mal die Tabelle sehen.

Gruß chris

#12

Hallo Ralf,
klassisch ein Fall für eine Pivot-Tabelle: z. B. Spalte 2 als Zeile, Spalte 3 als Spalte, Summe Spalte 4 als Werte.

Wenn es umbedingt eine Formel sein soll:
(Deine Werte wie oben stehen in Zellen A1 bis D2)
Tabelle erstellen, Zeilen:
F2 = „AB“; F3 = „MS“
und Spalten:
G1 = „IR“; H1 = „RE“
dann Matrixformel in G2:
=SUM(($B1:blush:B3=$F2)*($C1:blush:C3=G$1)*$D1:blush:D3), Achtung Eingabe mit Strg-Ctrl-Enter abschließen, da es sich um eine Matrixformel handelt (ggf. google fragen)
Anschließend erst rechts und dann nach unten füllen
Grüße
Ro

#13

Super,
hat nach der Vorlage super funktioniert.
Kann ich auch einen wert „wenn nicht“ angegeben werden?
Also mit der Formel ungefair so gestaltet (Wenn nicht-Wert mal in ** gestellt)

=SUMMEWENNS($C$2:blush:C$9999;$A$2:blush:A$9999;*„AB“*,*„IR“*,*„FA“*)

Also: Bilde die Summe aus C2 - C9999, wenn in A2 - A999 nicht AB, IR oder FA enthalten ist.

Komm da nicht drauf… hab sicher n Denkfehler drin.

#14

Hallo Ralf,
Du hast noch eine 2. Anfrage gestellt

Es gibt die sogenannten Arrayformeln. Diese werden i.a. mit STRG+Umschalttaste+Return abgeschlossen. Das ist kompliziert, weshalb ich es dir nicht vorschlage. Stattdessen gibt es Formeln, die von Excel per se als Arrayformeln betrachtet werden, so dass sie nicht so kompliziert eingegeben werden müssen.

Für deinen Fall gibt es mehrere Möglichkeiten:

  1. Du markierst jede Zeile mit 0 oder 1 in einer bestimmten Spalte HINTER deinen Daten. Das erreichst du mit einem normalen WENN. Dann nimmst du die Funktion SUMMEWENN und addierst nur die Zeilen, in denen eine 1 steht.
  2. Du nimmst die folgende Formel, dann brauchst du die Hilfsspalte aus 1. nicht. Die Formel lautet:
    =SUMME($C$2:blush:C$9999)-SUMMEWENN($A$2:blush:A$9999;„AB“;$C$2:blush:C$9999)-SUMMEWENN($A$2:blush:A$9999;„IR“;$C$2:blush:C$9999)-SUMMEWENN($A$2:blush:A$9999;„FA“;$C$2:blush:C$9999)

Sie sieht kompliziert aus, ist aber eigentlich ganz einfach: Du addierst erst ALLE Werte mit =SUMME($C$2:blush:C$9999) und ziehst davon 3 Werte (nämlich die jeweiligen Summen von AB IR und FA) ab.
-SUMMEWENN($A$2:blush:A$9999;„IR“;$C$2:blush:C$9999)
-SUMMEWENN($A$2:blush:A$9999;„AB“;$C$2:blush:C$9999)
-SUMMEWENN($A$2:blush:A$9999;„FA“;$C$2:blush:C$9999)

Noch verstanden?

Gruß
Harald

#15

Super. Das hat mir die Lösung gebracht. Ich danke vielmals allen fleissigen Helfern.

#16

Hallo,
schick mir doch mal deine Excel-Datei, in der du bereits die Eintragungen gemacht hast. Verstehe ansonsten dein Problem nicht.
MfG
BE
elberskirch.neuwied(at)t-online.de

#17

Hallo,

danke. Hat alles super funktioniert.

Meine Formel sieht nun so aus:

=SUMMEWENNS(‚C:\Users\Ovali\Desktop[Tabelle1.xlsx]Tabellenname Hauptfil‘!$L$5:blush:L$9999;‚C:\Users\Ovali\Desktop[Tabelle1.xlsx]Tabellenname Hauptfil‘!$C$5:blush:C$9999;„AA“;‚C:\Users\Ovali\Desktop[Tabelle1.xlsx]Tabellenname Hauptfil‘!$O$5:blush:O$9999;„IR“)

Diese Formel habe ich nun in 12 Feldern hinterlegt und die jeweiligen Suchkriterien angepassen.

Nun meine 3. und letzte Frage:
Die Verknüpfung bezieht sich nun auf [Tabelle1.xlsx]

Die Quelldatei wird aber nun umbenannt in 062013.xlsx.
Kann ich den Quelle von einem Ort meiner Tabelle (wo die Formeln drin stehen) ableiten?

Kompliziert erklärt? Ich versuch es ander:

Meine Auswertungstabelle hat die Überschrift: Auswertung Juni, könnte aber auch 062013 heißen.

Kann ich die Formel so anpassen, dass wenn mein Tabellenblatt die Überschrift 072013 hat automatisch als Quelle die
Datei 072013 genommen wird?

Ich hoffe, es ist noch durchzublicken :smile:

LG
Ralf

#18

Hallo Ralf,

… und wieder gibt es mehrere Möglichkeiten.

  1. Wenn sich nur der Dateiname (bzw. Pfad und Dateiname) ändert, dann kann man die Verknüpfung auf diesen Dateinamen ändern. Dadurch werden automatisch alle in den Zellformeln enthaltenen Verweise auf einen Dateinamen in einen anderen Dateinamen geändert. Leider ändert Microsoft häufig den Ort, wo das zu finden ist. In Excel 2007 ist es im Menü Daten/Verbindungen/Verknüpfungen bearbeiten.
  2. Man kann mit Suchen und Ersetzen Teile der Formel (also auch den Pfad, Dateinamen und sogar den Tabellenblattnamen in allen Formeln gleichzeitig ändern. Dazu solltest du vorher alle Zellen mit den Formeln markiert haben, dann erst mit STRG+h die Funktion Suchen und Ersetzen aufrufen.
  3. Man kann mit definierten Namen arbeiten. Dann wäre in den Formeln von vorne herein anstatt des Pfads (etc) nur der definierte Namen enthalten. Ändert man die Info, die hinter dem definierten Namen steht, dann funktionieren auch alle Formeln mit der geänderten Info.

Besonders der Punkt 3 ist kompliziert. Da solltest du schon Erfahrung haben. Punkt 2 ist sehr einfach.

Verstanden? Einfach einmal ausprobieren!

Harald

#19

Verstehe leider nicht ganz worum es geht, sorry…

#20

Hallo,

leider verfüge ich nur über einfache Grundkenntnisse im Bereich Excel, und kann daher bei der Beantwortung der Frage nicht behilflich sein.

Chris