Live-Forum - Die aktuellen Beiträge
Datum
Titel
29.03.2024 13:14:12
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1660to1664
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Formel vereinfachen

Formel vereinfachen
02.12.2018 11:20:05
Ralf
Hallo zusammen,
ich denke, ihr könnt mir bestimmt weiterhelfen.
=WENN(D$7="Ist";SUMMEWENNS(INDIREKT(D$2&"!$I$2:$I$100164");INDIREKT(D$2&"!$A$2:$A$100164");$A10; INDIREKT(D$2&"!$D$2:$D$100164");$B10;INDIREKT(D$2&"!$M$2:$M$100164");D$9);(E10))
Das ist meine Basisformel. Diese nutzen ich für den größten Teil meiner Tabelle. Dabei holt sich die Formel folgende Bezüge:
D$2 - auf welchem Datenblatt soll gesucht werden
$A10 - welches Konto soll genommen werden
$B10 - welche Kostenstelle soll genommen werden
D$9 - welches Datum soll gesucht werden
Nun ist es aber so, dass sich einige Konten auf verschieden Kostenstellen aufteilen, welche aber zu einem Bereich gehören. Also muss ich alle diese Kostenstellen auslesen und addieren.
Dafür nutze ich folgende Formel:
=WENN(D$7="Ist";((SUMMEWENNS(INDIREKT(D$2&"!$I$2:$I$100164");INDIREKT(D$2&"!$A$2:$A$100164");$A12; INDIREKT(D$2&"!$D$2:$D$100164");"CC4000";INDIREKT(D$2&"!$M$2:$M$100164");D$9)) +(SUMMEWENNS(INDIREKT(D$2&"!$I$2:$I$100164");INDIREKT(D$2&"!$A$2:$A$100164");$A12; INDIREKT(D$2&"!$D$2:$D$100164");"CC4020";INDIREKT(D$2&"!$M$2:$M$100164");D$9)) +(SUMMEWENNS(INDIREKT(D$2&"!$I$2:$I$100164");INDIREKT(D$2&"!$A$2:$A$100164");$A12; INDIREKT(D$2&"!$D$2:$D$100164");"CC4030";INDIREKT(D$2&"!$M$2:$M$100164");D$9)) +(SUMMEWENNS(INDIREKT(D$2&"!$I$2:$I$100164");INDIREKT(D$2&"!$A$2:$A$100164");$A12; INDIREKT(D$2&"!$D$2:$D$100164");"CC4040";INDIREKT(D$2&"!$M$2:$M$100164");D$9)) +(SUMMEWENNS(INDIREKT(D$2&"!$I$2:$I$100164");INDIREKT(D$2&"!$A$2:$A$100164");$A12; INDIREKT(D$2&"!$D$2:$D$100164");"CC4050";INDIREKT(D$2&"!$M$2:$M$100164");D$9)) +(SUMMEWENNS(INDIREKT(D$2&"!$I$2:$I$100164");INDIREKT(D$2&"!$A$2:$A$100164");$A12; INDIREKT(D$2&"!$D$2:$D$100164");"CC4051";INDIREKT(D$2&"!$M$2:$M$100164");D$9)) +(SUMMEWENNS(INDIREKT(D$2&"!$I$2:$I$100164");INDIREKT(D$2&"!$A$2:$A$100164");$A12; INDIREKT(D$2&"!$D$2:$D$100164");"CC4071";INDIREKT(D$2&"!$M$2:$M$100164");D$9)));(E12)
Es kann sein, dass es mal nur 1, mal 2 aber auch mal - wie hier - 7 oder mehr Kostenstellen sind. Leider müsste ich also jedesmal meine Formel anpassen. Ich möchte aber gerne nur mit einer Formel arbeiten. Habe aber bisher noch keine Lösung gefunden.
Nun meine Frage:
Gibt es eine Möglichkeit, die Formel einfacher zu gestalten und dass man auch für die "Unter-Kostenstellen" eine Art Abfrage machen kann? Dass ich also generell immer nur eine Formel für alle Abfrgen nutze.
Also, dass ich die Kostenstellen irgendwo in der Tabelle definiere und die Formel so oft abfragt und rechnet, bis alle angegebenen Kostenstellen abgearbeitet wurden, egal wie viele es sind.
Ich hoffe, das ist verständlich beschrieben.
Vielen Dank
Ralf

9
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Formel vereinfachen
02.12.2018 11:53:08
Sepp
Hallo Ralf,
verwende einen Platzhalter im Kriterium "CC40*".
 ABCDEF
1Gruß Sepp
2
3

Anzeige
AW: Formel vereinfachen
02.12.2018 12:05:20
Ralf
Hallo Sepp,
vielen Dank, geht aber leider nicht.
Ok, Beispiel war nicht gut gewählt.
Ich habe zum Beispiel auch eine Gruppe wo ich für den Gesamtwert die CC3000 und CC3100 abfragen muss. Und wenn ich dann einen Platzhalter verwende, dann werden auch andere "Unter-Kostenstellen" abgefragt und aufgerechnet, welche aber nicht in den Bereich gehören.
VG
AW: Formel vereinfachen
02.12.2018 12:13:43
Sepp
Hallo Ralf,
dann gib die Kriterien in den Zellen B10:B15 an und ändere die Formel um (eingabe als Matrixformel!)
{=WENN(D$7="Ist";SUMME(SUMMEWENNS(INDIREKT(D$2&"!$I$2:$I$100164");INDIREKT(D$2&"!$A$2:$A$100164"); $A10; INDIREKT(D$2&"!$D$2:$D$100164");$B10:$B15;INDIREKT(D$2&"!$M$2:$M$100164");D$9));(E10)) } Ich würde aber die Verwendung von PowerQuerry vorziehen.
 ABCDEF
1Gruß Sepp
2
3

Anzeige
AW: Formel vereinfachen
02.12.2018 14:17:25
Ralf
Hallo Sepp,
das funktioniert leider nicht. Da bekomme ich den Wert "0" zurück, obwohl Werte in der angegebenen Tabelle enthalten sind. Ich hatte auch schon eine Matrixabfrage genutzt, aber irgendwie will das INDIREKT wohl nicht oder aber es fehlt noch eine weitere Spezifikation.
PowerQuerry bin ich leider nicht so firm. Aber soweit mein Kenntnisstand, müssen die Werte doch schon in den zu durchsuchenden Tabellen vorhanden sein. Das ist bei mir leider nicht der Fall und kann sich von Tabelle (Monat zu Monat) zu tabelle ändern. Es kann sein, dass etwas enthalten ist, kann aber auch im kommenden Monat wieder nicht so sein und wäre dann sozusagen "0"
Anzeige
AW: Formel vereinfachen
02.12.2018 15:11:30
Sepp
Hallo Ralf,
also bei mir funktioniert die Formel, da hast du sicher etwas falsch übernommen.
 ABCDEF
1Gruß Sepp
2
3

Anzeige
AW: Formel vereinfachen
02.12.2018 15:11:58
SF
Hola,
warum lädst du keine Beispieldatei hoch?
Gruß,
steve1da
AW: Formel vereinfachen
02.12.2018 16:39:00
Ralf
So, anbei jetzt mal eine eingekürzte Test-Datei. Von den Tabellenblättern "Budget" gibt es dann ca. 10 Stück mit verschiedenen Kostenstellen aber teilweise gleichen Konten.
Die Reiter 1-12 stellen die einzelnen Monate da und darin werden dann die extern abgerufenen Daten sein. Diese können jeden Monat unterschiedlich sein oder auch mal ganz ausbleiben.
Im Blatt Budget 1 habe ich Sepp seine Formel eingefügt. Die rot markierten Zeilen sind z.B. betreffende Zeilen, wo es um mehrere Kostenstellen geht. Im Budget 1 sieht man bei meiner alten Formel, dass eigentlich ein Wert kommen müsste.
https://www.herber.de/bbs/user/125831.xlsx
Anzeige
AW: Formel vereinfachen
02.12.2018 17:00:47
Sepp
Hallo Ralf,
ich schrieb doch, dass die Formel als Matrixformel (ohne die {} und Abschluß mit STRG+SHIFT+ENTER) eingegeben werden muss!
Budget 1

 ABC
56   
57   
58Kostenstellen Formel Sepp mit anderem Bezug
59CC4000 475,2
60CC4020  
61CC4030  
62CC4040  
63CC4050  
64CC4051  
65CC4071  
66   
67   

Formeln der Tabelle
ZelleFormel
C59{=WENN(D$7="Ist";SUMME(SUMMEWENNS(INDIREKT(D$2&"!$I$2:$I$100164"); INDIREKT(D$2&"!$A$2:$A$100164"); $A12; INDIREKT(D$2&"!$D$2:$D$100164"); $A59:$A65;INDIREKT(D$2&"!$M$2:$M$100164"); D$9));(E12))}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
 ABCDEF
1Gruß Sepp
2
3

Anzeige
AW: Formel vereinfachen
02.12.2018 17:47:24
Ralf
Hallo Sepp,
vielen Dank!
Wer lesen kann ist klar im Vorteil!!!
Ich hatte alles richtig eingegeben, nur leider überlesen, dass du geschrieben hattest, dass es als Matrix abgeschlossen werden soll. Manchmal könnte es so einfach sein.
Nochmals vielen Dank, funktioniert prima.
VG
Ralf

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige