HERBERS Excel-Forum - das Archiv
Die erste, nicht leere Zelle finden
Rene

Hallo zusammen,
gehen wir davon aus, in Spalte A steht alle 10 Zeilen ein Name. In Spalte B stehen die Autos, die dieser Mensch besitzt. In jeder Zeile eines, außer in der in der Name steht.
In Spalte C soll dann für jedes Auto der Wert via SVERWEIS aus einer Tabelle gezogen werden.
Außerdem soll die Summe der Werte in Spalte C in der Zeile neben dem Namen summiert werden.
Die Lösung für Spalte C sähe so aus:
=WENN(ISTLEER(A1);SVERWEIS(B1;TABLE;4;FALSCH);SUMME(C2:C10))
=WENN(ISTLEER(A2);SVERWEIS(B2;TABLE;4;FALSCH);SUMME(C3:C11))
Jetzt haben manche Menschen aber mehr als 9 Autos. Wie kriege ich das hin, dass er die SUMME() automatisch anpasst?
Mein Ansatz ging in folgende Richtung, ist leider nicht umsetzbar:
SUMME(C3:INDIREKT("C"&VERGLEICH(SVERWEIS(*;A2:A102;1;FALSCH);A:A;0)-1))
Also, dass er mit dem SVERWEIS nach der ersten befüllten Zelle sucht, mit dem VERGLEICH sagt in welcher Zeile die steht -1 rechnet, damit die Zeile auch passt und mit INDIREKT dann das Ende bestimmt.
Die Summe muss in der selben Spalte stehen wie die SVERWEIS-Werte...
Gibt es eine praktikable Lösung dafür?
Die Tabelle würde in etwa so aussehen am Ende (- = leere Zelle):
Müller - Summe
- VW Wert
- Audi Wert

so wie Du es geschrieben hast , ...
der

Hallo Rene,
... zeig ich Dir mal eine mögliche Formellösung auf. Ich würde jedoch eine anderen Datenstruktur bevorzugen, dammit z.B. SUMMEWENN() eingesetzt werden könnte oder gleiche eine PIVOTauswertung möglich wäre.
Nachfolgender Formel ist es egal ob Leerzeilen vorhanden sind und wie viele Datensätze erfasst werden. Formel einfach ziehend nach unten kopieren:
 ABCDEFGHIJK
1NamenAutoWert    Auto  Wert
2 A_0310800    A_01  5600
3 A_0516000    A_02  8200
4 A_0618600    A_03  10800
5       A_04  13400
6       A_05  16000
7Name1 45400    A_06  18600
8 A_015600    A_07  21200
9 A_028200    A_08  23800
10 A_0310800        
11 A_0413400        
12           
13 A_0516000        
14 A_0618600        
15 A_0721200        
16 A_0823800        
17Name2 117600        
18 A_028200        
19 A_0721200        
20 A_0823800        
21           
22Name3 53200        
23           

Formeln der Tabelle
ZelleFormel
C2=WENN((A2="")*(B2>0); SVERWEIS(B2;Table;4;); WENN(A2="";"";SUMME(INDEX(C:C;VERWEIS(9;1/(A$1:A1<>""); ZEILE(A:A))+1):INDEX(C:C;ZEILE()-1))))
Namen in Formeln
ZelleNameBezieht sich auf
C2Table=Tabelle1!$H:$K
Namen verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...

AW: so wie Du es geschrieben hast , ...
Rene

Ich hab viel rumprobiert, aber bekomme es irgendwie nicht hin... Bei meiner Tabelle ist der Name mit der Summe über den Einzelwerten der Autos, nicht darunter.
Ich befürchte, dass ich es deswegen auch nicht hinbekommen habe...
Und da meine Funktion etwas umfangreicher ist, als die im Beispiel wäre es super, wenn man das Problem innerhalb der SUMMEN-Formel lösen kann:
=WENN(ISTLEER(A2);SVERWEIS(B2;TABLE;4;FALSCH);SUMME(C3:[also quasi hier]))
Gruß René

auch möglich, doch nachgefragt ...
der

Hallo René,
... steht unmittelbar neben dem Namen schon Daten oder erst darunter?
Gruß Werner
.. , - ...

die Frage hätte ich mir sparen können, ...
der

Hallo René,
... unmittelbar neben dem Namen sollte kein "Auto" stehen, weil ja ansonsten der Wert dieses Auto gleich in die Gesamtsumme eingerechnet werden müsste und somit die Liste nicht mehr transparent wäre.
Formel wieder ziehend nach unten kopieren:
 ABCDEFGHIJK
1NamenAutoWert    Auto  Wert
2Name1 45400    A_01  5600
3 A_0310800    A_02  8200
4 A_0516000    A_03  10800
5 A_0618600    A_04  13400
6       A_05  16000
7Name2 117600    A_06  18600
8 A_015600    A_07  21200
9 A_028200    A_08  23800
10 A_0310800        
11 A_0413400        
12           
13 A_0516000        
14 A_0618600        
15 A_0721200        
16 A_0823800        
17Name3 53200        
18 A_028200        
19 A_0721200        
20 A_0823800        
21           

Formeln der Tabelle
ZelleFormel
C2=WENN((A2="")*(B2=""); "";WENN(B2>0;SVERWEIS(B2;Table;4;); SUMME(C3:INDEX(C:C;WENNFEHLER(VERGLEICH("?*";A3:A99;); 19)+ZEILE(C1)))))
Namen in Formeln
ZelleNameBezieht sich auf
C2Table=Tabelle1!$H:$K
Namen verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Den Fettdruck der Summenwerte kannst Du mittels bedingter Formatierung erzielen.
Gruß Werner
.. , - ...

AW: die Frage hätte ich mir sparen können, ...
Rene

Vielen Dank!! Es funktioniert. Nur das mit der WENNFEHLER Funktion habe ich rausgelassen, da die ja nur Sinn hat, wenn bei jemandem kein Auto steht, oder? Die Formel funktioniert auf jeden Fall einwandfrei ohne. Viele Dank nochmal!

WENNFEHLER() ist erforderlich, zumindest ...
der

Hallo René,
... für die Formel(n) ab dem zuletzt gelisteten Namen in Spalte A.
Gruß Werner
.. , - ...

Die erste, nicht leere Zelle finden
Rene

Hallo zusammen,
gehen wir davon aus, in Spalte A steht alle 10 Zeilen ein Name. In Spalte B stehen die Autos, die dieser Mensch besitzt. In jeder Zeile eines, außer in der in der Name steht.
In Spalte C soll dann für jedes Auto der Wert via SVERWEIS aus einer Tabelle gezogen werden.
Außerdem soll die Summe der Werte in Spalte C in der Zeile neben dem Namen summiert werden.
Die Lösung für Spalte C sähe so aus:
=WENN(ISTLEER(A1);SVERWEIS(B1;TABLE;4;FALSCH);SUMME(C2:C10))
=WENN(ISTLEER(A2);SVERWEIS(B2;TABLE;4;FALSCH);SUMME(C3:C11))
Jetzt haben manche Menschen aber mehr als 9 Autos. Wie kriege ich das hin, dass er die SUMME() automatisch anpasst?
Mein Ansatz ging in folgende Richtung, ist leider nicht umsetzbar:
SUMME(C3:INDIREKT("C"&VERGLEICH(SVERWEIS(*;A2:A102;1;FALSCH);A:A;0)-1))
Also, dass er mit dem SVERWEIS nach der ersten befüllten Zelle sucht, mit dem VERGLEICH sagt in welcher Zeile die steht -1 rechnet, damit die Zeile auch passt und mit INDIREKT dann das Ende bestimmt.
Die Summe muss in der selben Spalte stehen wie die SVERWEIS-Werte...
Gibt es eine praktikable Lösung dafür?
Die Tabelle würde in etwa so aussehen am Ende (- = leere Zelle):
Müller - Summe
- VW Wert
- Audi Wert

so wie Du es geschrieben hast , ...
der

Hallo Rene,
... zeig ich Dir mal eine mögliche Formellösung auf. Ich würde jedoch eine anderen Datenstruktur bevorzugen, dammit z.B. SUMMEWENN() eingesetzt werden könnte oder gleiche eine PIVOTauswertung möglich wäre.
Nachfolgender Formel ist es egal ob Leerzeilen vorhanden sind und wie viele Datensätze erfasst werden. Formel einfach ziehend nach unten kopieren:
 ABCDEFGHIJK
1NamenAutoWert    Auto  Wert
2 A_0310800    A_01  5600
3 A_0516000    A_02  8200
4 A_0618600    A_03  10800
5       A_04  13400
6       A_05  16000
7Name1 45400    A_06  18600
8 A_015600    A_07  21200
9 A_028200    A_08  23800
10 A_0310800        
11 A_0413400        
12           
13 A_0516000        
14 A_0618600        
15 A_0721200        
16 A_0823800        
17Name2 117600        
18 A_028200        
19 A_0721200        
20 A_0823800        
21           
22Name3 53200        
23           

Formeln der Tabelle
ZelleFormel
C2=WENN((A2="")*(B2>0); SVERWEIS(B2;Table;4;); WENN(A2="";"";SUMME(INDEX(C:C;VERWEIS(9;1/(A$1:A1<>""); ZEILE(A:A))+1):INDEX(C:C;ZEILE()-1))))
Namen in Formeln
ZelleNameBezieht sich auf
C2Table=Tabelle1!$H:$K
Namen verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...

AW: so wie Du es geschrieben hast , ...
Rene

Ich hab viel rumprobiert, aber bekomme es irgendwie nicht hin... Bei meiner Tabelle ist der Name mit der Summe über den Einzelwerten der Autos, nicht darunter.
Ich befürchte, dass ich es deswegen auch nicht hinbekommen habe...
Und da meine Funktion etwas umfangreicher ist, als die im Beispiel wäre es super, wenn man das Problem innerhalb der SUMMEN-Formel lösen kann:
=WENN(ISTLEER(A2);SVERWEIS(B2;TABLE;4;FALSCH);SUMME(C3:[also quasi hier]))
Gruß René

auch möglich, doch nachgefragt ...
der

Hallo René,
... steht unmittelbar neben dem Namen schon Daten oder erst darunter?
Gruß Werner
.. , - ...

die Frage hätte ich mir sparen können, ...
der

Hallo René,
... unmittelbar neben dem Namen sollte kein "Auto" stehen, weil ja ansonsten der Wert dieses Auto gleich in die Gesamtsumme eingerechnet werden müsste und somit die Liste nicht mehr transparent wäre.
Formel wieder ziehend nach unten kopieren:
 ABCDEFGHIJK
1NamenAutoWert    Auto  Wert
2Name1 45400    A_01  5600
3 A_0310800    A_02  8200
4 A_0516000    A_03  10800
5 A_0618600    A_04  13400
6       A_05  16000
7Name2 117600    A_06  18600
8 A_015600    A_07  21200
9 A_028200    A_08  23800
10 A_0310800        
11 A_0413400        
12           
13 A_0516000        
14 A_0618600        
15 A_0721200        
16 A_0823800        
17Name3 53200        
18 A_028200        
19 A_0721200        
20 A_0823800        
21           

Formeln der Tabelle
ZelleFormel
C2=WENN((A2="")*(B2=""); "";WENN(B2>0;SVERWEIS(B2;Table;4;); SUMME(C3:INDEX(C:C;WENNFEHLER(VERGLEICH("?*";A3:A99;); 19)+ZEILE(C1)))))
Namen in Formeln
ZelleNameBezieht sich auf
C2Table=Tabelle1!$H:$K
Namen verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Den Fettdruck der Summenwerte kannst Du mittels bedingter Formatierung erzielen.
Gruß Werner
.. , - ...

AW: die Frage hätte ich mir sparen können, ...
Rene

Vielen Dank!! Es funktioniert. Nur das mit der WENNFEHLER Funktion habe ich rausgelassen, da die ja nur Sinn hat, wenn bei jemandem kein Auto steht, oder? Die Formel funktioniert auf jeden Fall einwandfrei ohne. Viele Dank nochmal!

WENNFEHLER() ist erforderlich, zumindest ...
der

Hallo René,
... für die Formel(n) ab dem zuletzt gelisteten Namen in Spalte A.
Gruß Werner
.. , - ...

Bewerten Sie hier bitte das Excel-Portal