Das Clever-Excel-Forum.de - Treffen
findet vom 15. - 17. September 2017 in Thüringen / Region Großer Inselsberg statt. Hotelbuchung ab sofort möglich.


Zellbezug "dynamisch" an Zeile orientieren
#1
Hallo liebe Excel-Gemeinde!

Ich habe die abgebildete Tabelle 1 – die Daten werden von anderen Quellen her bezogen und spielen hoffentlich für die Problemlösung keine Rolle.

Gesamtwertung

ABCDE
1Achim
2SeriePunkteSpielpunktegew.verl.
3174128182
42108752791
5
6Gesamt1828808173
7Pro Liste91440491,5
Formeln der Tabelle
ZelleFormel
B3=WENN(ISTFEHLER(WVERWEIS(B$1;INDIREKT(ZEILE(A1)&"!A$2:CE$60");56;FALSCH));"";WVERWEIS(B$1;INDIREKT(ZEILE(A1)&"!A$2:CE$60");56;FALSCH))
C3=WENN(ISTFEHLER(WVERWEIS(B$1;INDIREKT(ZEILE(A1)&"!A$2:CE$60");53;FALSCH));"";WVERWEIS(B$1;INDIREKT(ZEILE(A1)&"!A$2:CE$60");53;FALSCH))
D3=WENN(ISTFEHLER(INDEX(INDIREKT(ZEILE(A1)&"!A$2:CE$60");VERGLEICH(INDIREKT(ZEILE(A1)&"!$N$54");INDIREKT(ZEILE(A1)&"!$N$2:$N$54");0);VERGLEICH(B$1;INDIREKT(ZEILE(A1)&"!$A$2:$CD$2");0)+($D$2="gew.")*1));"";INDEX(INDIREKT(ZEILE(A1)&"!A$2:CE$60");VERGLEICH(INDIREKT(ZEILE(A1)&"!$N$54");INDIREKT(ZEILE(A1)&"!$N$2:$N$54");0);VERGLEICH(B$1;INDIREKT(ZEILE(A1)&"!$A$2:$CD$2");0)+($D$2="gew.")*1))
E3=WENN(ISTFEHLER(INDEX(INDIREKT(ZEILE(A1)&"!$A$2:$CE$60");VERGLEICH(INDIREKT(ZEILE(A1)&"!$N$54");INDIREKT(ZEILE(A1)&"!$N$2:$N$54");0);VERGLEICH(B$1;INDIREKT(ZEILE(A1)&"!$A$2:$CD$2");0)+($D$2="gew.")*2));"";INDEX(INDIREKT(ZEILE(A1)&"!$A$2:$CE$60");VERGLEICH(INDIREKT(ZEILE(A1)&"!$N$54");INDIREKT(ZEILE(A1)&"!$N$2:$N$54");0);VERGLEICH(B$1;INDIREKT(ZEILE(A1)&"!$A$2:$CD$2");0)+($D$2="gew.")*2))
B4=WENN(ISTFEHLER(WVERWEIS(B$1;INDIREKT(ZEILE(A2)&"!A$2:CE$60");56;FALSCH));"";WVERWEIS(B$1;INDIREKT(ZEILE(A2)&"!A$2:CE$60");56;FALSCH))
C4=WENN(ISTFEHLER(WVERWEIS(B$1;INDIREKT(ZEILE(A2)&"!A$2:CE$60");53;FALSCH));"";WVERWEIS(B$1;INDIREKT(ZEILE(A2)&"!A$2:CE$60");53;FALSCH))
D4=WENN(ISTFEHLER(INDEX(INDIREKT(ZEILE(A2)&"!A$2:CE$60");VERGLEICH(INDIREKT(ZEILE(A2)&"!$N$54");INDIREKT(ZEILE(A2)&"!$N$2:$N$54");0);VERGLEICH(B$1;INDIREKT(ZEILE(A2)&"!$A$2:$CD$2");0)+($D$2="gew.")*1));"";INDEX(INDIREKT(ZEILE(A2)&"!A$2:CE$60");VERGLEICH(INDIREKT(ZEILE(A2)&"!$N$54");INDIREKT(ZEILE(A2)&"!$N$2:$N$54");0);VERGLEICH(B$1;INDIREKT(ZEILE(A2)&"!$A$2:$CD$2");0)+($D$2="gew.")*1))
E4=WENN(ISTFEHLER(INDEX(INDIREKT(ZEILE(A2)&"!$A$2:$CE$60");VERGLEICH(INDIREKT(ZEILE(A2)&"!$N$54");INDIREKT(ZEILE(A2)&"!$N$2:$N$54");0);VERGLEICH(B$1;INDIREKT(ZEILE(A2)&"!$A$2:$CD$2");0)+($D$2="gew.")*2));"";INDEX(INDIREKT(ZEILE(A2)&"!$A$2:$CE$60");VERGLEICH(INDIREKT(ZEILE(A2)&"!$N$54");INDIREKT(ZEILE(A2)&"!$N$2:$N$54");0);VERGLEICH(B$1;INDIREKT(ZEILE(A2)&"!$A$2:$CD$2");0)+($D$2="gew.")*2))
B6=WENN(SUMME(B3:B4)=0;"";SUMME(B3:B4))
C6=WENN(SUMME(C3:C4)=0;"";SUMME(C3:C4))
D6=WENN(SUMME(D3Big Grin4)=0;"";SUMME(D3Big Grin4))
E6=WENN(SUMME(D3Big Grin4)=0;"";SUMME(E3:E4))
B7=WENN(ISTFEHLER(MITTELWERT(B3:B4));"";MITTELWERT(B3:B4))
C7=WENN(ISTFEHLER(MITTELWERT(C3:C4));"";MITTELWERT(C3:C4))
D7=WENN(ISTFEHLER(MITTELWERT(D3Big Grin4));"";MITTELWERT(D3Big Grin4))
E7=WENN(ISTFEHLER(MITTELWERT(E3:E4));"";MITTELWERT(E3:E4))

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8



Wenn ich nun eine Zeile mittels „Zellen einfügen“ einfüge soll sich in den Formeln in den Zellen B6-E7 der Bereich zur Berechnung der Summe um eine Zeile erhöhen, also von B4 auf B5, was es aber nicht tut – Tabelle 2.

Gesamtwertung

ABCDE
1Achim
2SeriePunkteSpielpunktegew.verl.
3174128182
42108752791
5371534572
6
7Gesamt1828808173
8Pro Liste91440491,5
Formeln der Tabelle
ZelleFormel
B3=WENN(ISTFEHLER(WVERWEIS(B$1;INDIREKT(ZEILE(A1)&"!A$2:CE$60");56;FALSCH));"";WVERWEIS(B$1;INDIREKT(ZEILE(A1)&"!A$2:CE$60");56;FALSCH))
C3=WENN(ISTFEHLER(WVERWEIS(B$1;INDIREKT(ZEILE(A1)&"!A$2:CE$60");53;FALSCH));"";WVERWEIS(B$1;INDIREKT(ZEILE(A1)&"!A$2:CE$60");53;FALSCH))
D3=WENN(ISTFEHLER(INDEX(INDIREKT(ZEILE(A1)&"!A$2:CE$60");VERGLEICH(INDIREKT(ZEILE(A1)&"!$N$54");INDIREKT(ZEILE(A1)&"!$N$2:$N$54");0);VERGLEICH(B$1;INDIREKT(ZEILE(A1)&"!$A$2:$CD$2");0)+($D$2="gew.")*1));"";INDEX(INDIREKT(ZEILE(A1)&"!A$2:CE$60");VERGLEICH(INDIREKT(ZEILE(A1)&"!$N$54");INDIREKT(ZEILE(A1)&"!$N$2:$N$54");0);VERGLEICH(B$1;INDIREKT(ZEILE(A1)&"!$A$2:$CD$2");0)+($D$2="gew.")*1))
E3=WENN(ISTFEHLER(INDEX(INDIREKT(ZEILE(A1)&"!$A$2:$CE$60");VERGLEICH(INDIREKT(ZEILE(A1)&"!$N$54");INDIREKT(ZEILE(A1)&"!$N$2:$N$54");0);VERGLEICH(B$1;INDIREKT(ZEILE(A1)&"!$A$2:$CD$2");0)+($D$2="gew.")*2));"";INDEX(INDIREKT(ZEILE(A1)&"!$A$2:$CE$60");VERGLEICH(INDIREKT(ZEILE(A1)&"!$N$54");INDIREKT(ZEILE(A1)&"!$N$2:$N$54");0);VERGLEICH(B$1;INDIREKT(ZEILE(A1)&"!$A$2:$CD$2");0)+($D$2="gew.")*2))
B4=WENN(ISTFEHLER(WVERWEIS(B$1;INDIREKT(ZEILE(A2)&"!A$2:CE$60");56;FALSCH));"";WVERWEIS(B$1;INDIREKT(ZEILE(A2)&"!A$2:CE$60");56;FALSCH))
C4=WENN(ISTFEHLER(WVERWEIS(B$1;INDIREKT(ZEILE(A2)&"!A$2:CE$60");53;FALSCH));"";WVERWEIS(B$1;INDIREKT(ZEILE(A2)&"!A$2:CE$60");53;FALSCH))
D4=WENN(ISTFEHLER(INDEX(INDIREKT(ZEILE(A2)&"!A$2:CE$60");VERGLEICH(INDIREKT(ZEILE(A2)&"!$N$54");INDIREKT(ZEILE(A2)&"!$N$2:$N$54");0);VERGLEICH(B$1;INDIREKT(ZEILE(A2)&"!$A$2:$CD$2");0)+($D$2="gew.")*1));"";INDEX(INDIREKT(ZEILE(A2)&"!A$2:CE$60");VERGLEICH(INDIREKT(ZEILE(A2)&"!$N$54");INDIREKT(ZEILE(A2)&"!$N$2:$N$54");0);VERGLEICH(B$1;INDIREKT(ZEILE(A2)&"!$A$2:$CD$2");0)+($D$2="gew.")*1))
E4=WENN(ISTFEHLER(INDEX(INDIREKT(ZEILE(A2)&"!$A$2:$CE$60");VERGLEICH(INDIREKT(ZEILE(A2)&"!$N$54");INDIREKT(ZEILE(A2)&"!$N$2:$N$54");0);VERGLEICH(B$1;INDIREKT(ZEILE(A2)&"!$A$2:$CD$2");0)+($D$2="gew.")*2));"";INDEX(INDIREKT(ZEILE(A2)&"!$A$2:$CE$60");VERGLEICH(INDIREKT(ZEILE(A2)&"!$N$54");INDIREKT(ZEILE(A2)&"!$N$2:$N$54");0);VERGLEICH(B$1;INDIREKT(ZEILE(A2)&"!$A$2:$CD$2");0)+($D$2="gew.")*2))
B5=WENN(ISTFEHLER(WVERWEIS(B$1;INDIREKT(ZEILE(A3)&"!A$2:CE$60");56;FALSCH));"";WVERWEIS(B$1;INDIREKT(ZEILE(A3)&"!A$2:CE$60");56;FALSCH))
C5=WENN(ISTFEHLER(WVERWEIS(B$1;INDIREKT(ZEILE(A3)&"!A$2:CE$60");53;FALSCH));"";WVERWEIS(B$1;INDIREKT(ZEILE(A3)&"!A$2:CE$60");53;FALSCH))
D5=WENN(ISTFEHLER(INDEX(INDIREKT(ZEILE(A3)&"!A$2:CE$60");VERGLEICH(INDIREKT(ZEILE(A3)&"!$N$54");INDIREKT(ZEILE(A3)&"!$N$2:$N$54");0);VERGLEICH(B$1;INDIREKT(ZEILE(A3)&"!$A$2:$CD$2");0)+($D$2="gew.")*1));"";INDEX(INDIREKT(ZEILE(A3)&"!A$2:CE$60");VERGLEICH(INDIREKT(ZEILE(A3)&"!$N$54");INDIREKT(ZEILE(A3)&"!$N$2:$N$54");0);VERGLEICH(B$1;INDIREKT(ZEILE(A3)&"!$A$2:$CD$2");0)+($D$2="gew.")*1))
E5=WENN(ISTFEHLER(INDEX(INDIREKT(ZEILE(A3)&"!$A$2:$CE$60");VERGLEICH(INDIREKT(ZEILE(A3)&"!$N$54");INDIREKT(ZEILE(A3)&"!$N$2:$N$54");0);VERGLEICH(B$1;INDIREKT(ZEILE(A3)&"!$A$2:$CD$2");0)+($D$2="gew.")*2));"";INDEX(INDIREKT(ZEILE(A3)&"!$A$2:$CE$60");VERGLEICH(INDIREKT(ZEILE(A3)&"!$N$54");INDIREKT(ZEILE(A3)&"!$N$2:$N$54");0);VERGLEICH(B$1;INDIREKT(ZEILE(A3)&"!$A$2:$CD$2");0)+($D$2="gew.")*2))
B7=WENN(SUMME(B3:B4)=0;"";SUMME(B3:B4))
C7=WENN(SUMME(C3:C4)=0;"";SUMME(C3:C4))
D7=WENN(SUMME(D3Big Grin4)=0;"";SUMME(D3Big Grin4))
E7=WENN(SUMME(D3Big Grin4)=0;"";SUMME(E3:E4))
B8=WENN(ISTFEHLER(MITTELWERT(B3:B4));"";MITTELWERT(B3:B4))
C8=WENN(ISTFEHLER(MITTELWERT(C3:C4));"";MITTELWERT(C3:C4))
D8=WENN(ISTFEHLER(MITTELWERT(D3Big Grin4));"";MITTELWERT(D3Big Grin4))
E8=WENN(ISTFEHLER(MITTELWERT(E3:E4));"";MITTELWERT(E3:E4))

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8




Ich hatte nun die Idee den Wert, also B4 dadurch zu ersetzen, dass dieser sich auf die Zeile bezieht in der die Formel steht und dann 2 abzieht, habe es aber noch nicht hinbekommen.

Gibt es da einen Kniff wie man die Formel in den Zellen B6-E7 „dynamisch“ gestaltet, so dass, wenn man ein oder mehrere Zeilen einfügt, sich der Wert, in meinem Fall B4 um die Anzahl der eingefügten Zeilen auf B5, B6 etc. erhöht?

Ich hoffe ihr habt verstanden was ich meine, es ist nämlich schwer ein Frage kurz und korrekt zu formulieren.
Viele Grüße 43
Achim

Excel 2013
to top
#2
Hallo Immi,

die Zeile 5 scheint ja leer zu sein, so dass sie keinen Einfluss auf die Ergebnisse hat.
Also erweitere einfach die Formeln bis Zeile 5.

Gruß Uwe
to top
#3
(30.05.2014, 23:45)Immi schrieb: Hallo liebe Excel-Gemeinde!

Ich habe die abgebildete Tabelle 1 – die Daten werden von anderen Quellen her bezogen und spielen hoffentlich für die Problemlösung keine Rolle.

...

Ich hatte nun die Idee den Wert, also B4 dadurch zu ersetzen, dass dieser sich auf die Zeile bezieht in der die Formel steht und dann 2 abzieht, habe es aber noch nicht hinbekommen.

Gibt es da einen Kniff wie man die Formel in den Zellen B6-E7 „dynamisch“ gestaltet, so dass, wenn man ein oder mehrere Zeilen einfügt, sich der Wert, in meinem Fall B4 um die Anzahl der eingefügten Zeilen auf B5, B6 etc. erhöht?

Ich hoffe ihr habt verstanden was ich meine, es ist nämlich schwer ein Frage kurz und korrekt zu formulieren.

hi, kannst du mal bitte (d)eine (beispiel)datei posten..?
cu jörg eine rückmeldung wäre ganz reizend XL2003 bis XL2013
to top
#4
Hallo,

ich würde es mal so versuchen:

Code:
=SUMME(B$3:index(B:B;Zeile()-2))
wenn(zählenwenn(B$3:index(B:B;Zeile()-2);">0");Mittelwert(B$3:index(B:B;Zeile()-2));0)

Wenn das Ergebnis 0 ist, kannst Du den Wert durch die Formatierung ausblenden
Gruß


Edgar

Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
to top
#5
Vielen Dank für eure Antworten Smile

@ Edgar: Das ist genau das wonach ich gesucht habe. Habe die Formel noch was angepasst und es funktioniert 28
Viele Grüße 43
Achim

Excel 2013
to top


Möglicherweise verwandte Themen...
Thema Verfasser Antworten Ansichten Letzter Beitrag
  Zelleninhalte nicht dynamisch Butterbrot23 2 69 17.11.2016, 09:02
Letzter Beitrag: schauan
Lightbulb VBA Zeile in eine Arbeitsmappe kopieren wenn in der Zeile eine 1 steht Olossos 8 113 11.11.2016, 17:08
Letzter Beitrag: Kuwer
  Zellbezug zu externer Datei via Hyperlink Fred B 10 170 28.10.2016, 11:33
Letzter Beitrag: schauan
  Dynamisch Werte von einem anderen Blatt lesen Rapt0r 5 87 26.10.2016, 18:56
Letzter Beitrag: Rapt0r
  Zeile kopieren - Anzahl wie in Zeile July0210 10 219 26.09.2016, 10:08
Letzter Beitrag: July0210
  Dynamisches Diagramm und Tabellen logisch/dynamisch miteinander verknüpfen P.S.Nelke 3 274 29.08.2016, 07:47
Letzter Beitrag: P.S.Nelke
  Dynamisch erstellte UserForm1 wird nicht sofort angezeigt VBATartar 3 236 07.08.2016, 18:59
Letzter Beitrag: Kuwer
  Schaltfläche dynamisch wieder löschen - Objektorientierte Programmierung VBATartar 12 655 26.07.2016, 18:38
Letzter Beitrag: VBATartar
  Liste und Diagramm (dynamisch) für Auftragseingänge Jastar 5 433 05.07.2016, 05:21
Letzter Beitrag: Rabe
  Makro - Datum filtern mit Zellbezug Rosié_87 4 332 20.04.2016, 11:36
Letzter Beitrag: Rosié_87

Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste