Microsoft Excel

Herbers Excel/VBA-Archiv

SUMME INDIREKT VERGLEICH nur in Zeile 1 möglich


Betrifft: SUMME INDIREKT VERGLEICH nur in Zeile 1 möglich von: AH
Geschrieben am: 22.11.2017 09:41:56

Hallo!

Zur Erstellung einer Vorlage für Ablaufpläne und der Bildung von Zwischensummen jeder Phase dachte ich an die Verwendung folgende Formel in D1:

=WENN(A1="P";SUMME(INDIREKT("C1:C"&VERGLEICH("M";A2:A5000;0)));"")

(Die Tabelle wird mittels Button - je nach Bedarf - um eine Zeile erweitert. Die Formeln und Formatierungen werden in jeder neuen Zeile des Plans mitkopiert.)

Aufbau einer einfachen Beispieltabelle:

A: Typ (P - Phase; AP - Arbeitspaket; M - Meilenstein)
B: Beschreibung
C: Einzelkosten der AP
D: Zwischensumme je Phase

Jede Phase besitzt 1 bis n AP und endet mit einem M. Nur AP verursachen Kosten.

Nun mein Problem:

Wenn ich die oben beschriebene Formel in der Zeile 1 eintrage, funktioniert diese zwar, doch addiert von C1 bis z. B. C5 (in A6 steht ein M) und nicht von C2 bis C5. Wenn z. B. in der Zeile 7 eine neue Phase beginnt, dann addiert die Formel:

=WENN(A7="P";SUMME(INDIREKT("C1:C"&VERGLEICH("M";A8:A5006;0)));"")

nicht die Werte von C8 bis zum nächsten M (z. B. in A13) sondern von C1 bis C6. Und auch wenn ich in der Zeile 1 als Überschriftszeile (z. B. Typ, Beschreibung, EP und Summe) nutzen möchte, funktioniert die Formel (nun in D2) nicht mehr. Dann addiert die Formel:

=WENN(A2="P";SUMME(INDIREKT("C1:C"&VERGLEICH("M";A3:A5001;0)));"")

noch immer die Werte von C1 bis C5 obwohl das M nun in A7 steht.

Wie muss ich die Formel ändern, damit diese nicht nur in der Zeile 1 funktioniert und warum ist das überhaupt so?

Vielen Dank für eure Hilfe im Voraus.

  

Betrifft: AW: SUMME INDIREKT VERGLEICH nur in Zeile 1 möglich von: AH
Geschrieben am: 22.11.2017 10:05:25

Hier noch das Beispiel:

https://www.herber.de/bbs/user/117825.xlsm


  

Betrifft: AW: SUMME INDIREKT VERGLEICH nur in Zeile 1 möglich von: yummi
Geschrieben am: 22.11.2017 10:32:40

Hallo AH,

ändermal deine formel

=WENN(A2="P";SUMME(INDIREKT("C1:C"&VERGLEICH("M";A3:A5002;0)+ZEILE()));"")
Ist das das was Du willst?

Gruß
yummi


  

Betrifft: AW: SUMME INDIREKT VERGLEICH nur in Zeile 1 möglich von: AH
Geschrieben am: 22.11.2017 10:45:54

Hallo yummi,

danke, doch leider funktioniert das noch nicht. Folgende Formel in D8

=WENN(A8="P";SUMME(INDIREKT("C1:C"&VERGLEICH("M";A9:A5008;0)+ZEILE()));"")

addiert noch immer ab C1 und nicht erst ab C9. Aber nun werden wenigstens alle Werte bis zum zweiten M in A14 addiert. Also ein Teilerfolg.

Kannst Du mir bitte noch erklären, was der Zusatz +ZEILE() bewirkt?

Danke und Grüße


  

Betrifft: AW: SUMME INDIREKT VERGLEICH nur in Zeile 1 möglich von: AH
Geschrieben am: 22.11.2017 10:51:47

aktualisiertes Beispiel:

https://www.herber.de/bbs/user/117829.xlsm


  

Betrifft: AW: SUMME INDIREKT VERGLEICH nur in Zeile 1 möglich von: yummi
Geschrieben am: 22.11.2017 11:04:19

Hallo,

hier die Version mit bereich.verschieben

=WENN(A2="P";SUMME(BEREICH.VERSCHIEBEN(INDIREKT("C1:C"&VERGLEICH("M";A3:A5002;0));ZEILE();0));"" _
)
Gruß
yummi


  

Betrifft: AW: SUMME INDIREKT VERGLEICH nur in Zeile 1 möglich von: AH
Geschrieben am: 22.11.2017 11:30:19

Super, vielen Dank!

Jetzt funktioniert es wie gewünscht. :)

Verstehe die jetzige Formel zwar leider nicht mehr - hoffe, dass ich noch dahinter steige - aber mein Problem, mit dem ich gestern den halben Tag gekämpft habe, wurde innerhalb weniger Minuten gelöst.

DANKE!


  

Betrifft: AW: SUMME INDIREKT VERGLEICH nur in Zeile 1 möglich von: yummi
Geschrieben am: 22.11.2017 11:41:34

Hallo AH

klick dir mal eine Zelle mit Formel an, dan ngeh auf den Reiter Formeln und dort auf Formel auswerten und schau dir Schritt für Schritt an was passiert.


Beispiel:
Deine Formel in A4 also Zeile 4
Dein zu durchsuchender Bereich geht von A5: A10 also 5 Zeilen
Dein Wert den Du finden willst steht in A7 also Zeile 7
Innerhalb deines Bereichs ist das A5,A6,A7 also die 3.Zeile

Ergo: 3+ offset 4 = 7

Jetzt klarer?

Gruß
yummi

PS: Schau dir die Lösung mit Bereich auch mal an.


  

Betrifft: AW: SUMME INDIREKT VERGLEICH nur in Zeile 1 möglich von: AH
Geschrieben am: 22.11.2017 11:48:55

Ja, danke. :)

Super!


  

Betrifft: AW: SUMME INDIREKT VERGLEICH nur in Zeile 1 möglich von: AH
Geschrieben am: 22.11.2017 14:20:34

Hallo yummi,

voller Freude habe ich die Formel in meine richtige Datei eingebaut und habe dann leider festgestellt, dass es nun ein "neues Problem" mit der Formel gibt und zwar in Verbindung mit dem Button "neue Zeile". Denn Wenn ich über den Button eine neue Zeile generieren lassen, dann ändert - ich glaube das Macro - die Formel in der letzten existierenden Zeile und zwar wie folgt:

aus

=WENN(A14="P";SUMME(BEREICH.VERSCHIEBEN(INDIREKT("C1:C"&VERGLEICH("M";A15:A5014;0));ZEILE();0));"")

wird

=WENN(A14="P";SUMME(BEREICH.VERSCHIEBEN(INDIREKT("C1:C"&VERGLEICH("M";A16:A5015;0));ZEILE();0));"")

und somit lautet die Formel in der neu erstellte Tabellenzeile

=WENN(A15="P";SUMME(BEREICH.VERSCHIEBEN(INDIREKT("C1:C"&VERGLEICH("M";A17:A5016;0));ZEILE();0));"")

und funktioniert nicht mehr. :(

Meine Vermutung ist, dass es daran liegt, dass der Button in der Zeile 16 liegt, aber der Butten muss unter der letzten Zeile positioniert sein. Kann die Formel so umgebaut werden, dass sie unabhängig von der Position des Buttons ist?

Grüße
AH


  

Betrifft: AW: INDIREKT() und BEREICH.VERSCHIEBEN() ... von: ... neopa C
Geschrieben am: 22.11.2017 15:21:27

Hallo,

... sind beides sogenannte volatile Funktionen (mehr dazu sieh u.a. mal hier: http://www.online-excel.de/excel/singsel.php?f=171) Ich vermeide diese wo immer möglich. Diese lassen sich auch meisten durch die nicht volatile Funktion INDEX() (dazu mehr sieh u.a. mal hier: http://www.online-excel.de/excel/singsel.php?f=180) ersetzen.

Ich lade mir allerdings keine XLSM-Datei aus dem Internet. Wenn Du Deine Beispieldatei mal als XLSX-Datei einstellst würde ich es mir auch mal anschauen. Evtl. jedoch erst am Abend oder Morgen.

Gruß Werner
.. , - ...


  

Betrifft: AW: INDIREKT() und BEREICH.VERSCHIEBEN() ... von: AH
Geschrieben am: 22.11.2017 16:11:01

Hallo Werner,

danke für die Rückmeldung. Nachfolgend die Datei im XLSX-Format:

https://www.herber.de/bbs/user/117839.xlsx

Allerdings sind nun die Makros deaktiviert und du kannst den Fehler nich rekunstruieren. :/

Deine Links werde ich gleich einmal studieren.

Grüße
AH


  

Betrifft: AW: dann wie bereits geschrieben ... von: ... neopa C
Geschrieben am: 22.11.2017 17:44:06

Hallo AH,

... in D2: =WENN(A2="P";SUMME(C3:INDEX(C:C;WENNFEHLER(VERGLEICH("P";A3:A93;0)+ZEILE()-1;999999)));"")
und Formel nach unten kopieren.

WENNFEHLER() ist in der Beispieldatei nicht notwendig, wird aber in der Originaldatei sicherlich benötigt für die letzte Phasensummierung.

Gruß Werner
.. , - ...


  

Betrifft: AW: SUMME INDIREKT VERGLEICH nur in Zeile 1 möglich von: yummi
Geschrieben am: 22.11.2017 15:33:52

Hallo AH,

das sich der Bereich ändert A15,A16,A17 macht Sinn, da Du ja sonst nicht das passende M zu deinem Bereich findest.
Ich kann den Fehler in deienr Beispieldatei nicht nachvollziehen.
Wann funktioniert es den nicht mehr? Gleich beim ersten mal oder erst nach ein paar neuen Zeilen?

Wenn Du funktionierende Bereiche hast schau mal mit der Formelauswertung, wo der Unterschied zu den nicht mehr funktionierenden Bereich liegt.

Ohne deien orginaldatei zu kennen, kann ich dir schlecht sagen, woran es liegt.

Auf jeden Fall musst du beim Auswerten folgendes sehen:
Indirekt liefert dir immer C1:C Anzahl Zeilen

Anzahl Zeilen sind von nächste Zeile (von deienr markierten ausgehend) bis einschliesslich der Zeile mit M. Wenn also 7 Einträge in dem Block sind dann: C1:C8 (da M nach deinem 7. Eintrag steht)

der so mit indirekt gefundene Bereich wird dann um Zeile (die Zeile die gerade markiert ist) nach unten verschoben. Dein C1:C8 wird in Zeile 13 also zu C14:C22

Ich hoffe es hilft beim suchen, sonst musst du dein Original posten und beschreiben, was wann nciht geht.

Gruß
yummi


  

Betrifft: AW: SUMME INDIREKT VERGLEICH nur in Zeile 1 möglich von: AH
Geschrieben am: 22.11.2017 16:04:09

Hallo yummi,

hier nochmals die Beispieldatei:

https://www.herber.de/bbs/user/117837.xlsm

Wenn Du diese Datei öffnest und die Formel in der Zelle D14 vor und nach dem "drücken" des Buttons "neue Zeile" vergleichst, stellst Du fest, dass sich die Formel in D14 durch betätigen des Buttons verändert (Suchmatrix von VERGLEICH ist verändert nach dem betätigen des Buttons) und dadurch auch die Formel in der neuen Zelle D15 "falsch" ist.

Grüße
AH


  

Betrifft: AW: SUMME INDIREKT VERGLEICH nur in Zeile 1 möglich von: yummi
Geschrieben am: 22.11.2017 16:41:59

Hallo AH,

ich hab dir in dein Makro eine Korrektur eingebaut. Das Makro ist aber unabhängig davon, welche Formel Du verwendest, von daher nimm dann die Formel die Werner mit Index baut, die ist besser, das Makro geht dann trotzdem.

Sub Zeile_einfügen_inkl_Format_und_Formeln()
Dim llast As Long


    With ActiveSheet
        'Blattschutz aufheben
        .Unprotect
        .Rows(ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row).Insert (xlShiftDown)
        'kopieren (feste Werte werden auch kopiert)
        .Rows(ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row - 2).Copy
        'Formatierung einfügen
        .Rows(.Shapes(Application.Caller).TopLeftCell.Row - 1).PasteSpecial Paste:= _
xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        'kopieren (feste Werte werden auch kopiert)
        .Rows(.Shapes(Application.Caller).TopLeftCell.Row - 2).Copy
        'Formeln einfügen
        .Rows(.Shapes(Application.Caller).TopLeftCell.Row - 1).PasteSpecial Paste:= _
xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        'feste Werte loeschen
        .Rows(.Shapes(Application.Caller).TopLeftCell.Row - 1).SpecialCells(xlCellTypeConstants) _
.ClearContents
        Application.CutCopyMode = False
        
        'neue korrektur
        llast = .Cells(.Rows.Count, 4).End(xlUp).Row  'letzte Zeile in Spalte D
        .Range("D2").AutoFill Destination:=Range("D2:D" & llast), Type:=xlFillDefault
        
        
        'Blatt schützen
        .Protect
    End With
End Sub

Was ich gemacht habe ist, dass ich den Formelfehler, der in der letzten Zeile durch das insert enstanden ist mit autofill wieder korrigiert habe. Problem könnte sein, dass wen ndu eine sehr Datei hast, dass es dauern könnte, dan neinfach aus
.Range("D2").AutoFill Destination:=Range("D2:D" & llast), Type:=xlFillDefault
dies machen
.Range("D" & llast - 2).AutoFill Destination:=Range("D" & llast - 2 & ":D" & llast), Type:=xlFillDefault

Gruß
yummi


  

Betrifft: AW: SUMME INDIREKT VERGLEICH nur in Zeile 1 möglich von: AH
Geschrieben am: 22.11.2017 17:28:25

Vielen Dank!

In der Beispieldatei funktioniert dies super. In dem Original leider nicht. Hier kommt folgende Microsoft Visual Basic Fehlermeldung:

"Laufzeitfehler '1004:

Für diese Aktion müssen alle verbundenen Zellen dieselbe Größe haben."

Ich werde morgen einmal die originale Datei hochladen. Und freue mich auf weitere Hilfe.

Schönen Abend
AH


  

Betrifft: AW: SUMME INDIREKT VERGLEICH nur in Zeile 1 möglich von: yummi
Geschrieben am: 23.11.2017 08:41:24

Hallo AH,

in der Fehlermeldung steht doch ganz klar drin, woran es liegt. Du arbeitest mit verbundenen Zellen und wenn das nciht schon bescheiden genug ist, nein, du hast auch noch verschieden große Bereioche definiert.
Ich kann dir nur empfehlen, wen ndu es nciht unbedingt brauchst, die verbundenen zellen zu "entbinden".

Gruß
yummi


  

Betrifft: AW: SUMME INDIREKT VERGLEICH nur in Zeile 1 möglich von: AH
Geschrieben am: 23.11.2017 09:28:23

Hallo,

hier die originale Datei:

https://www.herber.de/bbs/user/117856.xlsm

Aufgrund des Projektkopfes, der sowohl auf der noch zu erstellenden Projektübersicht als auch auf dem Projektstrukturplan dargestellt werden soll, habe ich die z. T. verbundenen Zellen...

Grüße
AH


  

Betrifft: AW: SUMME INDIREKT VERGLEICH nur in Zeile 1 möglich von: yummi
Geschrieben am: 23.11.2017 09:51:02

Hallo AH,

du musst schon alles anpassen, wenn du eine andere Spalte benutzen willst ;-)
die Berechnung der letzten Zeile musst Du auch auf Spalte T anpassen, also so:

        llast = .Cells(.Rows.Count, 20).End(xlUp).Row  'letzte Zeile in Spalte T
bei dir 4 in 20 ändern, dann gehts.

Gruß
yummi


  

Betrifft: AW: SUMME INDIREKT VERGLEICH nur in Zeile 1 möglich von: AH
Geschrieben am: 23.11.2017 10:15:48

Hallo yummi,

vielen Dank! Scheint nun wirklich zu funktionieren. Natürlich bringt es nichts nur drei von vier Stellen.

Ich Danke euch vielmals für die Unterstützung und Geduld mit mir.

Beste Grüße
AH


  

Betrifft: AW: hast Du denn die INDEX()-Formel getestet? owT von: ... neopa C
Geschrieben am: 23.11.2017 13:50:16

Gruß Werner
.. , - ...


  

Betrifft: AW: hast Du denn die INDEX()-Formel getestet? owT von: AH
Geschrieben am: 23.11.2017 13:56:49

Ja, verwende jetzt

=WENN($A15="P";SUMME(S16:INDEX(S:S;WENNFEHLER(VERGLEICH("P";$A16:$A102;0)+ZEILE()-1;999999)));"")

in T15.

Danke!

Grüße
AH


  

Betrifft: AW: SUMME INDIREKT VERGLEICH nur in Zeile 1 möglich von: yummi
Geschrieben am: 22.11.2017 10:59:58

Hallo AH,

deine Teilformel Vergleich liefert dir den 2. Wert für deinen Indirekt Bereich.
Dein Indirekt Bereich geht von C1 (fix) bis C irgendwas

Vergleich liefert dir die x-Zeile innerhalb des Bereichs, was aber nicht der wirklichen Zeile entspricht, da der Vergleichsbereich verschoben ist um die Anzahl der zeilen, wo P in Spalte A steht. Deshalb habe ich Zeile(), was dir die aktuelle Zeile liefert dazu addiert.

Also du hast 2 Möglichkeiten entweder Du nimmst bereich.verschieben um deinen Bereich um Zeilen zu verschieben oder du addierst selber Zeile() auf deinen Bereich drauf.

=WENN(A2="P";SUMME(INDIREKT("C"&ZEILE()&":C"&VERGLEICH("M";A3:A5005;0)+ZEILE()));"")
verschiebt dir auch den Anfang deiner Berechnung.

Gruß
yummi


  

Betrifft: AW: SUMME INDIREKT VERGLEICH nur in Zeile 1 möglich von: AH
Geschrieben am: 22.11.2017 11:38:52

Danke! :)


  

Betrifft: AW: SUMME INDIREKT VERGLEICH nur in Zeile 1 möglich von: AH
Geschrieben am: 22.11.2017 11:55:41

Ich habe mich für die Lösung mit bereich.verschieben entschieden, da diese erst ab der nachfolgenden Zeile summiert.

Nun verstehe ich auch was die Formel berechnet, nur befürchte ich, dass es noch ein weiter Weg ist bis ich selbst auf eine solche Lösung komme... ;)


Beiträge aus den Excel-Beispielen zum Thema "SUMME INDIREKT VERGLEICH nur in Zeile 1 möglich"