Die erste, nicht leere Zelle finden

Bild

Betrifft: Die erste, nicht leere Zelle finden
von: Rene
Geschrieben am: 23.07.2015 11:00:33

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

Bild

Betrifft: so wie Du es geschrieben hast , ...
von: der neopa C
Geschrieben am: 23.07.2015 12:41:34
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
.. , - ...


Bild

Betrifft: AW: so wie Du es geschrieben hast , ...
von: Rene
Geschrieben am: 24.07.2015 08:16:10
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é

Bild

Betrifft: auch möglich, doch nachgefragt ...
von: der neopa C
Geschrieben am: 24.07.2015 08:26:20
Hallo René,
... steht unmittelbar neben dem Namen schon Daten oder erst darunter?
Gruß Werner
.. , - ...

Bild

Betrifft: die Frage hätte ich mir sparen können, ...
von: der neopa C
Geschrieben am: 24.07.2015 08:49:25
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
.. , - ...

Bild

Betrifft: AW: die Frage hätte ich mir sparen können, ...
von: Rene
Geschrieben am: 24.07.2015 10:09:39
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!

Bild

Betrifft: WENNFEHLER() ist erforderlich, zumindest ...
von: der neopa C
Geschrieben am: 24.07.2015 11:04:35
Hallo René,
... für die Formel(n) ab dem zuletzt gelisteten Namen in Spalte A.
Gruß Werner
.. , - ...

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Die erste, nicht leere Zelle finden"