Spaltenbuchstaben ermitteln bei letztem Wert in Zeile

[shift-del]

Wir sind beim zehnten Beitrag und schon hat sich die Datenstruktur (leicht) geändert und aus einem Blatt sind mehrere Blätter geworden.

Wird vermutlich bis Beitrag 65 gehen bevor Fragesteller und Helfer aufgeben.

[Fred11]

Hallo Andi,

mit
=AGGREGAT(14;6;SPALTE($A$1:$Z$1)/($A$1:$Z$1<>"");SPALTE(A1:C1))
erhältst du die Spaltennummern der drei letzten befüllten Spalten.
Das sollte eigentlich reichen.
Mithilfe der Spaltennummer(n) kannst du dann deine weiteren Auswertungen machen mit INDEX(Bereich; Spaltennummer) ....

Wenn du es kompliziert machen möchtest (siehe Beitrag #2 von LCohen):
Den Spaltenbuchstaben erhältst du mit
=WECHSELN(ADRESSE(1;AGGREGAT(14;6;SPALTE($A$1:$Z$1)/($A$1:$Z$1<>"");SPALTE(A1:C1));4);1;"")

Für weitergehende Hilfe bitte eine Beispieldatei mit manuell eingetragenem Wunschergebnis beistellen.

Gruß
Fred

[BoskoBiati]

Hi,

manchmal hilft googeln!

https://www.herber.de/excelformeln/pages...oriam.html

[HKindler]

Hi,

bei aktuellem Excel geht auch:
=ÜBERNEHMEN(FILTER(1:7;1:1<>"");;-3)
Damit hast du die letzten 3 Spalten der Zeilen 1:7 und brauchst dich nicht mit weiteren Formeln rumschlagen.

[AndiG]

Danke Fred     !!!

Es wird allerdings bei beiden nur der letzte Spalten-Wert ausgegeben.
Ich habe jetzt leider noch nicht geschnallt, wie ich den vorletzten bzw. vorvorletzten etc. auslesen kann.

Wenn ich das noch hätte, wäre es genau das, was mir hilft, um meine vorhandene "Auslese"-Excel anzupassen   

Gruß Andi

---

Hallo Andi,

mit
=AGGREGAT(14;6;SPALTE($A$1:$Z$1)/($A$1:$Z$1<>"");SPALTE(A1:C1))
erhältst du die Spaltennummern der drei letzten befüllten Spalten.
Das sollte eigentlich reichen.
Mithilfe der Spaltennummer(n) kannst du dann deine weiteren Auswertungen machen mit INDEX(Bereich; Spaltennummer) ....

Wenn du es kompliziert machen möchtest (siehe Beitrag #2 von LCohen):
Den Spaltenbuchstaben erhältst du mit
=WECHSELN(ADRESSE(1;AGGREGAT(14;6;SPALTE($A$1:$Z$1)/($A$1:$Z$1<>"");SPALTE(A1:C1));4);1;"")

Für weitergehende Hilfe bitte eine Beispieldatei mit manuell eingetragenem Wunschergebnis beistellen.

Gruß
Fred

[Fred11]

Hallo Andi,

ich weiss jetzt nicht, was du genau gemacht hast und wo/wie du die obige Formel angewendet hast, aber in einem aktuellen Excel (Excel 365, Excel 2021) liefert diese Formel normalerweise alle 3 Werte.

Nur die letzte Spaltennummer erhälst du mit
=AGGREGAT(14;6;SPALTE($A$1:$Z$1)/($A$1:$Z$1<>"");1)
(Oder eine der anderen Formeln aus dem Link von Edgar….)

die vorletzte mit
=AGGREGAT(14;6;SPALTE($A$1:$Z$1)/($A$1:$Z$1<>"");2) usw.
(oder einfacher: letzte Spaltennummer  -1

Aber noch einfacher geht es in deinem Fall (und so würde ich das machen), wenn du in deiner obersten Zeile mit den KWs nicht "KW 26" usw. schreibst, sondern nur 26, 27, 28 … - als reine Zahl
Wenn du aus optischen Gründen das "KW" dabei haben möchtest, kannst du das ja über das benutzerdefinierte Zahlenformat "dazumogeln".
 

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		KW 26	KW 27	KW 28	KW 29	KW 30	KW 31	KW 32	KW 33	KW 34	KW 35
2	Brot	45	567	757	47	47	678	978	466	67	85	0	0	0	0
3	Nudeln	267	67	79	90	0		456	764	64	2	0	0	0	0
4	Obst	1	42	4445	1	2	121	2558	4		42	0	0	0	0
5	Gemüse	12	12	56	854	1	456	552	45	4	785	0	0	0	0
6	Fleisch	6686	56	56	866	68	777	55	545	55	12	0	0	0	0
7	Getränke	12	56	45		69	98	86	57	78	88	0	0	0	0
8
9
10	letzte Spalten-Nr	10
11	vorletzte	9
12	vorvorletzte	8
13
14	letzter Wert aus Zeile 2	85
15	vorletzter	67
16	vorvorletzter	466
17
18	oder in einem Rutsch:
19	letzter Wert aus Zeile 2	85
20	vorletzter	67
21	vorvorletzter	466

Zelle	Format	Wert
B1	"KW" Standard	26
C1	"KW" Standard	27
D1	"KW" Standard	28
E1	"KW" Standard	29
F1	"KW" Standard	30

Zelle	Formel
B10	=VERGLEICH(MAX($B$1:$O$1);$B$1:$O$1;0)
B11	=B10-1
B12	=B11-1
B14	=INDEX($B$2:$O$2;B10)
B15	=INDEX($B$2:$O$2;B11)
B16	=INDEX($B$2:$O$2;B12)
B19	=INDEX($B$2:$O$2;VERGLEICH(MAX($B$1:$O$1);$B$1:$O$1;0)-0)
B20	=INDEX($B$2:$O$2;VERGLEICH(MAX($B$1:$O$1);$B$1:$O$1;0)-1)
B21	=INDEX($B$2:$O$2;VERGLEICH(MAX($B$1:$O$1);$B$1:$O$1;0)-2)

Verwendete Systemkomponenten: [Windows (64-bit) NT 10.00] / MS Excel 2021

Diese Tabelle wurde mit Tab2Html (v2.7.1) erstellt. ©Gerd alias Bamberg

Eigenzitat:

Für weitergehende Hilfe bitte eine Beispieldatei mit manuell eingetragenem Wunschergebnis beistellen.

Gruß
Fred

...vielleicht hat Detlef doch recht ....

[HKindler]

Hi,

wieso sollte man sich bei aktuellem Excel mit Aggregat rumschlagen um zu filtern? Ich möchte nochmals auf meinen Beitrag #14 hinweisen.

[AndiG]

Danke Fred!  
Danke an alle anderen!

Aber nein...Fred hatte in diesem Fall nicht Recht 

Wenn ich die Zahl einsetze, habe ich genau, was ich brauche.  


Die Datei mit den Pivot-Tabellen kann ich leider nicht anpassen, da mir diese zur Verfügung gestellt wird und ich das beste raus machen muss. (so auch KW 26).


Also besten Dank und bis zum nächsten Mal 

Andi

[LCohen]

=ÜBERNEHMEN(FILTER(1:7;1:1<>"");;-3)

OT: "Flattern" die rechten Ränder (anders, als hier im Thread), z.B. bei

1 2 3 4 5
6 7 8 9
2 3 4 5 6 7 8

und will man daher als Ergebnis haben

3 4 5
7 8 9
6 7 8

klappt =NACHZEILE(1:3;LAMBDA(a;ÜBERNEHMEN(FILTER(a;a<>"");;-3))) leider nicht, sondern man muss mit

=NACHZEILE(1:3;LAMBDA(a;TEXTNACH(TEXTVERKETTEN("#";;a);"#";-3)))

vorbereiten, und dann dieses in ein TEXTTEILEN.2D einsetzen:

=LAMBDA(x;t;LET(y;t&x&t;m;LÄNGE(t);
n;MAX(NACHZEILE(y;LAMBDA(a;(LÄNGE(a)-LÄNGE(WECHSELN(a;t;))-m)/m)));
WENNFEHLER(TEXTNACH(TEXTVOR(y;t;SEQUENZ(;n;2));t;SEQUENZ(;n;1));""))
)(NACHZEILE(1:3;LAMBDA(a;TEXTNACH(TEXTVERKETTEN("#";;a);"#";-3)));"#")

Das geht natürlich auch kürzer, aber ich habe "Standardbausteine" verwendet.

[Gast 123]

Hallo

eine einfache Spaltenangebe per MsgBox kann man auch mit VBA ermitteln.  Hier dazu ein kurzer Code.
Im Code selbst bitte den Namen deiner Tabelle mit den KW Spalten angegeben werden. Bei mir ist es Worksheets("Tabelle1")
Bei Const muss der Name deiner Datei angegeben werden.  Dann sollte das Makro funktionieren.

mfg Gast 123

Code:

'** Hier den Namen deiner Datei angeben!
Const WBEx = "Beispiel Spaltenbuchstaber.xls"


Sub test()
   Dim Sht As Worksheet, LAdr As String
   'Name des Worksheet mit den KW Spalten setzen
   Set Sht = Workbooks(WBEx).Worksheets("Tabelle1")
   'Last Adrress (rechts) mit xlEnd ermitteln
   LAdr = Range("B1").End(xlToRight).Address(0, 0)
   'Spalte mit letzter KW wird angezeiugt
   MsgBox Replace(LAdr, "1", "")
End Sub