[Excel] Bereichsnamen - Teil 3 - Navigation / Datenabrufe

[schauan]

Tabellennavigation / Datenabfragen

Ich habe hier eine kleine Mitarbeitertabelle (MAT) erstellt und den Bereich entsprechend benannt.
In den folgenden Beispielen greife ich auf unterschiedliche Art und Weise auf die einzelnen Felder
dieser Tabelle zurück.
An dieser Stelle möchte ich auch gleich darauf hinweisen, dass bei den Mitarbeitern natürlich auch
Doppelungen auftreten können . Das führt zu Problemen bei der korrekten Auswahl.
Für eine eindeutige Auswahl wären weitere Angaben nötig - idealerweise Personalnummern.

Arbeitsblatt mit dem Namen 'Daten'
	A	B	C	D	E
2	Name	Vorname	Geburtstag		MAT
3	Müller	Gerd	02.02.2012
4	Meier	Marion	03.03.2013
5	Schulze	Horst	04.04.2014

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

Beispiel 4
Gezielte Abfragen
In diesen beiden Abfragen hole ich konkret die Vornamen zu dem in der links benachbarten Zelle eingetragenen
Familiennamen. Ich verwende hier einmal den SVERWEIS und einmal den VERWEIS kombiniert mit INDEX.
=SVERWEIS(Daten!I13;_5_MAT;2;0)
=VERWEIS(2;1/(INDEX(_5_MAT;;1)=A7);INDEX(_5_MAT;;2))
mit den Namen _4_Vorname_S und _4_Vorname_V

Sofern Marion Meier einen der beiden Herren heitratet und beide den gleichen Familiennamen führen,
wird das auch im Ergebnis der Abfragen deutlich - probiert es einfach mal aus.
(Anmerkung: Auch wenn Gerd und Horst so zueinander finden, kann man es sehen :-)

Arbeitsblatt mit dem Namen 'Daten'
	A	B
12	Müller	Gerd
13	Müller	Gerd

Name	Bezug
_4_Vorname_S	=SVERWEIS(Daten!XFD12;_5_MAT;2;0)
_4_Vorname_V	=VERWEIS(2;1/(INDEX(_5_MAT;;1)=Daten!XFD12);INDEX(_5_MAT;;2))

Zelle	Formel
B12	=_4_Vorname_S
B13	=_4_Vorname_V

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

Beispiel 5
Flexible Abfragen
Um flexibel bestimmte Mitarbeiterdaten abrufen zu können, habe ich mir eine Auswahl der Tabellenspalten
zusammengestellt. Dazu verwende ich die Gültigkeit und beziehe mich auf die erste Zeile der MAT:
=INDEX(_5_MAT;1;)
Ich kann nun entsprechend der Spaltenüberschriften auswählen.
Sofern eine Spalte eingefügt wird, erweitert sich die Auswahl gleich. Auf ein Anfügen einer Spalte würde die
hier gezeigte Lösung nicht reagieren - aber machbar wäre dies auch.

Für die Berechnung der Daten habe ich einen weiteren Namen definiert
=_5_MAT_Auswahl
Auch hier kommt wieder eine Funktion mit VERWEIS und INDEX zum Einsatz.
=VERWEIS(2;1/(INDEX(_5_MAT;;1)=Daten!$A9);INDEX(_5_MAT;;VERGLEICH(Daten!$B9;INDEX(_5_MAT;1;);0)))

Arbeitsblatt mit dem Namen 'Daten'
	A	B	C
24	Müller	Geburtstag	02.02.2012

Name	Bezug
_5_MAT	=Daten!$A$2:$C$5
_5_MAT_Auswahl	=VERWEIS(2;1/(INDEX(_5_MAT;;1)=Daten!$A24);INDEX(_5_MAT;;VERGLEICH(Daten!$B24;INDEX(_5_MAT;1;);0)))

Zelle	Formel
C24	=_5_MAT_Auswahl

Zelle	Gültigkeitstyp	Operator	Wert1	Wert2
B24	Liste		=INDEX(_5_MAT;1;)

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

Arbeitsblatt mit dem Namen 'Daten'
	B	C	D
36	weitere Formelbeispiele
37
38		letzte	letzte
39		Zeile in	Spalte in
40		Spalte A	Zeile 2
41	bezogen auf	24	5
42	ganzes Blatt	Müller	MAT
43
44		letzte	letzte
45		Zeile von	Spalte von
46		MAT	MAT
47	bezogen auf	5	3
48	ganzes Blatt	Schulze	Geburtstag
49
50		erste	erste
51		Zeile von	Spalte von
52		MAT	MAT
53	bezogen auf	2	1
54	ganzes Blatt
55
56		Anzahl	Anzahl
57		Zeilen in	Spalten in
58		MAT	MAT
59		4	3

Name	Bezug
_5_MAT	=Daten!$A$2:$C$5

Zelle	Formel
C41	=VERWEIS(2;1/(A2:A37<>"");ZEILE(A$2:A$37))
D41	=VERWEIS(2;1/(Daten!2:2<>"");SPALTE(Daten!2:2))
C42	=VERWEIS(2;1/(A2:A37<>"");A$2:A$37)
D42	=VERWEIS(2;1/(Daten!2:2<>"");Daten!2:2)
C47	=VERWEIS(2;1/(INDEX(_5_MAT;;1)<>"");ZEILE(_5_MAT))
D47	=VERWEIS(2;1/(INDEX(_5_MAT;1;)<>"");SPALTE(INDEX(_5_MAT;1;)))
C48	=VERWEIS(2;1/(INDEX(_5_MAT;;1)<>"");INDEX(_5_MAT;;1))
D48	=VERWEIS(2;1/(INDEX(_5_MAT;1;)<>"");INDEX(_5_MAT;1;))
C53	=ZEILE(_5_MAT)
D53	=SPALTE(_5_MAT)
C59	=ZEILEN(_5_MAT)
D59	=SPALTEN(_5_MAT)

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