Bereichsnamen nutzbar wenn Spalten sich ändern?

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

	A	B	C	D	E	F	G	H
14	VorN	NNam	Alter	gesucht	Namen:
15	VN_01	NN_01	45	VN_02	NN_02	NN_03	NN_07	NN_09
16	VN_02	NN_02	56	älter als	NN_02	NN_03	NN_07	NN_09
17	VN_02	NN_03	47	40	NN_02	NN_03	NN_07	NN_09
18	VN_01	NN_04	58	*Anzahl*	NN_02	NN_07	NN_09	*Anzahl*
19	VN_03	NN_05	32	4	NN_03			1
20	VN_01	NN_06	27		Pflaume	Grün	Blau	<--Schriftfarbe
21	VN_02	NN_07	58	Schriftfarbe→	Rot
22	VN_01	NN_08	62	*Anmerkungen:*	Pflaume	Rot	Grün	Blau
23	VN_02	NN_09	63	NoErrRange liefert hier den hybriden Bereich	*(B16:B17;B21;B23).*
24	VN_02	NN_10	37	CellContIn gibt hier eine Zelleigenschaft wieder, was Zellbezug erfordert.
25	VN_03	NN_11	62	VJoin&MxJoin verbinden alle Zellinhalte im Bereich, VSplit kehrt das um.
26	E15:H15: {=INDEX(B15:B25;AGGREGAT(15;6;ZEILE(B1:B11)/(A15:A25=D15)/(C15:C25>D17);SPALTE(A1:D1)))}
27	E22:H22: {=T(JETZT())&CellContIn(INDEX(B15:B25;AGGREGAT(15;6;ZEILE(B1:B11)/(A15:A25=D15)/(C15:C25>D17);SPALTE(A1:D1)));;;"fcn")}
28	E16:H16: {=VSplit(VJoin(WENN((C15:C25>$D17)(A15:A25=D15);B15:B25;"");;-1))}*
29	E17:H17: {=VSplit(MxJoin(NoErrRange(B15:B25;;(A15:A25=D15)(C15:C25>D17))))}*
30	E18:G19: {=WENNFEHLER(INDEX(NoErrRange(B15:B25;;(A15:A25=D15)(C15:C25>D17));ZEILE(A1:A2);;SPALTE(A1:C1));"")}*
31	E20:G21: {=WENNFEHLER(T(JETZT())&CellContIn(INDEX(NoErrRange(B15:B25;;(A15:A25=D15)(C15:C25>D17));{1;2};;{1.2.3});;;"fcn");"")}*
32	D19: {=AGGREGAT(3;6;NoErrRange(B15:B25;;(A15:A25=D15)(C15:C25>D17)))}*	<-- richtig
33	H19: {=AGGREGAT(3;6;INDEX(NoErrRange(B15:B25;;(A15:A25=D15)(C15:C25>D17));ZEILE(A1:A2);;SPALTE(A1:C1)))}*	<-- falsch

VorN

NNam

Alter

gesucht

Namen:

VN_01

NN_01

VN_02

NN_02

NN_03

NN_07

NN_09

VN_02

NN_02

älter als

NN_02

NN_03

NN_07

NN_09

VN_02

NN_03

NN_02

NN_03

NN_07

NN_09

VN_01

NN_04

Anzahl

NN_02

NN_07

NN_09

Anzahl

VN_03

NN_05

NN_03

VN_01

NN_06

Pflaume

Grün

Blau

<--Schriftfarbe

VN_02

NN_07

Schriftfarbe→

Rot

VN_01

NN_08

Anmerkungen:

Pflaume

Rot

Grün

Blau

VN_02

NN_09

NoErrRange liefert hier den hybriden Bereich

(B16:B17;B21;B23).

VN_02

NN_10

CellContIn gibt hier eine Zelleigenschaft wieder, was Zellbezug erfordert.

VN_03

NN_11

VJoin&MxJoin verbinden alle Zellinhalte im Bereich, VSplit kehrt das um.

E15:H15: {=INDEX(B15:B25;AGGREGAT(15;6;ZEILE(B1:B11)/(A15:A25=D15)/(C15:C25>D17);SPALTE(A1:D1)))}

E22:H22: {=T(JETZT())&CellContIn(INDEX(B15:B25;AGGREGAT(15;6;ZEILE(B1:B11)/(A15:A25=D15)/(C15:C25>D17);SPALTE(A1:D1)));;;"fcn")}

E16:H16: {=VSplit(VJoin(WENN((C15:C25>$D17)*(A15:A25=D15);B15:B25;"");;-1))}

E17:H17: {=VSplit(MxJoin(NoErrRange(B15:B25;;(A15:A25=D15)*(C15:C25>D17))))}

E18:G19: {=WENNFEHLER(INDEX(NoErrRange(B15:B25;;(A15:A25=D15)*(C15:C25>D17));ZEILE(A1:A2);;SPALTE(A1:C1));"")}

E20:G21: {=WENNFEHLER(T(JETZT())&CellContIn(INDEX(NoErrRange(B15:B25;;(A15:A25=D15)*(C15:C25>D17));{1;2};;{1.2.3});;;"fcn");"")}

D19: {=AGGREGAT(3;6;NoErrRange(B15:B25;;(A15:A25=D15)*(C15:C25>D17)))}

<-- richtig

H19: {=AGGREGAT(3;6;INDEX(NoErrRange(B15:B25;;(A15:A25=D15)*(C15:C25>D17));ZEILE(A1:A2);;SPALTE(A1:C1)))}

<-- falsch

Hallo Luc,
... diese zeigt, wenn überhaupt nur über "Umwege" auf, nach was ich gesucht bzw. nachgefragt hatte.
Mit "Umwege" meinte ich das generieren von Bereichsangaben für INDEX() wie z.B. in E18:G19 über eine Deiner UDF, die dann notwendig/sinnvoll wird/ist, wenn bestimmte Zusatzauswertungen nicht mit Excelstandardfunktionalitäten (ohne alte Excel4makros) vorgenommen werden können (z.B. Auswertung von Formaten) und trotzdem eine Formellösung angestrebt ist/wird.
Ich meinte ein Beispiel, dass wirklich mit der von INDEX() ausgewiesene Bezugsversion mit Bereichsangaben in einer singulare Formel realisiert wird, und zwar analog dem Beispiel von Peter nur eben "praxisnäher" und ohne Hochzüge und Umwege.
Richtig eingeschätzt hast Du, dass wohl nur äußert wenige neben Dir eine derartige Lösung wie in Deinem Beispiel aufgezeigt realisieren könnten und es dann auch würden.
Nachfolgend betrachte ich nur Dein Beispiel bzgl. der Auswertung der Namen in den Zellen D19 und E15:H15.
Die Formel in D19 zeigt mir weder das, was ich suchte und ist außerdem schon mehr als gekünstelt.
Jeder einigermaßen erfahrene Exceluser wird doch hier lediglich schlicht und einfach eine SUMMEWENNS()-Formel einsetzen oder eben alternativ =SUMMENPRODUKT((A1:A99=D15)*(D1:D99>D17)).
Auch Deine plurale Matrixformel in E15:H15 ist mE kein Beispiel dafür, wo der Einsatz einer BEREICHSangabe in einer Bezugsversion von INDEX() erfolgt.
Deine Aussage zu den pluralen Matrixformeln teile ich nur bedingt.
Hast Du schon mal eine ErgebnisBereichFixierung für mehrere (z.B. nur) Dutzende (um nicht wie Du von tausenden Zellen zu schreiben) vorgenommen? Die Erzeugung dafür ist vor allem starr, was sich insbesondere bei evtl. notwendigen Änderungen mehr als ungünstig auswirkt. Außerdem kann man im Nachgang äußerlich nicht erkennen, wie groß der definierte Ergebnisbereich ist (wie z.B. bei verbundenen Zellen).
Im konkreten Beispiel würde ich für die plurale Formel den Ergebniszellbereich erstens größer _ definieren und die Formel entsprechend reichlich vordefinieren z.B. so: {=WENNFEHLER(INDEX(B:B;AGGREGAT(15;6;ZEILE(B15:B99)/(A15:A99=D15)/(C15:C99>D17);SPALTE(A1:Z1)));"") }
Aber bevorzugen würde ich wegen der besseren Erkennbarkeit und Flexibilität trotzdem die singulare Matrixfunktion(alität)sformel in E15:

=WENNFEHLER(INDEX($B:$B;AGGREGAT(15;6;ZEILE($B15:$B99)/($A15:$A99=$D15)/($C15:$C99>$D17);SPALTE(A1))) ;"")

die ich dann einfach nach rechts kopieren würde. Die dadurch reduzierte Auswertungsgeschwindigkeit ist bei derartigen überschaubaren Bereichen völlig unbedeutend.
Gruß Werner
.. , - ...

…nicht verstehst, worum es mir bei diesem alten und redigierten Bsp ging, Werner,
denn du klebst irgendwie an überkommenem „Brauchtum“. ;-]
Deshalb zuvor ein allgemeines Statement zur Problematik in bezug auf deinen Wunsch:
Es ist sicher nicht einfach ein praxisnahes Bsp für den sinnvollen Gebrauch des 4.INDEX-Arguments zu finden, weil das sicherlich selten benötigt wird, denn …
1. sollte man sowohl listenförmige als auch KreuzTabellen so anlegen, dass sie eine rationale Auswertung ermöglichen;
2. kann man aus jeder rational aufgebauten Tabelle Bereiche u/o ihre Daten nach Kriterien oder zufällig auswählen, so dass übliche Xl-Fktt verwendet wdn können;
3. kann man, auch, wenn benötigte Kriterien fehlen, diese über Hilfszellen (ggf als einfache AuswahlMarkierung) hinzufügen.
So bleiben also nur die Fälle übrig, in denen …
1. benötigte Kriterien fehlen,
2. die Anlage von Hilfszellen nicht möglich oder zu aufwendig ist und auch parallele AnkreuzListen nicht infrage kommen,
3. die benötigten TeilBereiche sehr unterschiedlich, aber statisch sind und es folglich einfacher wäre, sie direkt anzusprechen (in VBA-Makros habe ich solche Versuche schon des öfteren gesehen!).
Trotzdem kommt so etwas vor und viele (alte) XlFktt können auch damit umgehen. INDEX nun hat primär die Aufgabe, einzelne Werte aus ZellBereichen zurückzugeben. Da sollte dann auch mit diskreten bzw hybriden (teils diskret, teils kontinuierlich wie im Bsp) Bereichen umgegangen wdn können. Außerdem kann INDEX bekanntlich auch Datenfelder (aus Ausdrücken in Arg1) verarbeiten, wobei Arg4 wg fehlender Möglichkeit, mehrere Datenfelder in Arg1 anzugeben (ich hatte mal eine Sammel-UDF geschrieben, die das erlaubt → Collect - ggf recht nützlich), entfallen muss.
Nun ist bekannt, dass INDEX auch ganze Spalten oder Zeilen zurückgeben kann. Das wird so nicht für Bereiche gelten können, da diese idR kaum alle gleich groß sein und gleiche Zeilen-/SpaltenNrn aufweisen wdn. In diesen Fällen muss, wie im Bsp gezeigt, nicht nur Arg4 angegeben wdn.
Der Übersichtlichkeit halber kommentiere ich im Folgenden deinen BT in vorliegender Reihenfolge:
- Generieren von Bereichsangaben für INDEX wie zB in E18:G19 über eine UDF
Das habe ich getan, um die Verbindung zu den ursprünglichen Lösungen beizubehalten und somit den Vgl zu erleichtern. Die Auswertung von Formaten ist übrigens auch mit deiner Lösung möglich (als plurale MxFml in E15:H15), wie E22:H22 zeigt (INDEX liefert in allen gezeigten Fällen, ebenso wie NoErrRange, ZellBezüge!), wodurch dieser Teil deiner Argumentation irrelevant wird (das sollte nur eine Demo sein, dass tatsächlich ZellBezüge geliefert wdn). Die XLM-Fkt scheidet hier aus, weil sie nur EinzelZellen verarbeiten kann und somit die MxFmln komplizieren oder gar unmöglich machen würde, könnte aber in deiner ursprünglichen Fml eingesetzt wdn (einer der wenigen Berechtigungsfälle solchen Vorgehens!).
- Realisierungsfähigkeit - äußerst Wenige
Ich habe nicht nur mich, sondern auch noch einige Andere gemeint, zuallererst natürlich auch dich! ;-)
So etwas kann kompliziert wdn und man muss einiges beachten. Dazu hatte ich mich ja bereits geäußert. Etwas mehr zu INDEX und unauffälligerer INDEX-Aktivität in Fmln nachfolgend (und ggf zum Schluss).
- Formel in D19 - gekünstelt?
Diese Fml ist Zugabe! Sie ist nur im Zusammenhang mit H19 zu sehen! Hierbei geht es darum, dass die Fml in H19 die Anzahl nicht mit INDEX und AGGREGAT ermitteln kann, weil INDEX hierzu (über meine Demo einer sinnvollen Anwendung dualer MxFmln - in anderem Zusammenhang) seine ZellBezüge verlieren würde, was nur noch die direkte AnzahlErmittlung ohne AGGREGAT erlauben würde. In D19 ist das aber oW möglich (ein Plädoyer für NoErrRange und ihr Ergebnis → hier ein hybrider Bereich)!
Der Wissenschaftler muss alle Wege untersuchen (für Mathematiker quasi eine existenzielle Frage!), der Ingenieur wählt dann den/die rationellsten aus… ;-)
- plurale Matrixformel in E15:H15 und INDEX-Arg4
Diese Fml basiert auf deiner ursprünglichen Fml, wie du sicher unschwer erkannt hast. Der Unterschied zu deiner Version besteht nur darin, dass hier nur 1× gerechnet wird (deine Version würde 4× alles berechnen, um dann nur einen, jeweils anderen Wert auszuwählen). Diese Auswahl des jeweils passenden Wetes übernimmt bei der MxFml-Version die Xl-Steuerung. Das wird dadurch ermöglicht, dass das 2.Arg als Ausdruck ein Datenfeld aus ZeilenNrn liefert, die nacheinander als INDEX-Arg2 verwendet wdn, abhängig von der relativen Position der ErgebnisZelle im ErgebnisFeld. Somit ergeben sich wiederum ZellBezüge als Ergebnis, allerdings linear angeordnet. Im Grunde genommen sind das ebenfalls einzelne Bereiche (aus jeweils einer Zelle), die hier aber einfach über die AbzählVariante von INDEX (bei nur 2 Argumenten) ausgewählt wdn können. Weiter unten zeige ich, dass das mit einer DirektAngabe eines hybriden bzw diskreten Bereichs auch nur mit Arg4 fktionieren würde.
- plurale Matrixformeln - nebensächlich? ErgebnisFixierung nachteilig?
Es ist mit Sicherheit anzunehmen, dass diese Möglichkeit eingebracht wurde, um mehrere Werte (also Matrizen und Vektoren als BerechnungsErgebnis) mit nur einer Fml sowohl berechnen als auch ausgeben zu können - ein normaler mathematischer Verfahrensweise analoger Vorgang. Das ist auch in anderen KalkulationsPgmm so (nur das Handling ist oft anders)! Damit wären sie der eigentliche Zweck der Existenz dieses Features und die Existenz sowohl singularer als auch dualer MxFmln nur ein NebenEffekt, der sich aus der Xl-Steuerung und ihrer Rolle bei der Datenbereitstellung ergibt.
Ich halte es eher für einen Missbrauch dieser Fktionalität, wenn sie überwiegend zur Erzeugung von Einzelwerten benutzt wird. Das sollte die Ausnahme sein und ist nur notwendig, wenn anderenfalls ein falsches Ergebnis geliefert wird und keine günstige Alternative zV steht. Die „Mx(Fml)Fktionalität“ ist hierbei eher zu nutzen, ist aber (besonders bei MassenBerechnungen) eher kontraproduktiv, falls auch eine plurale MxFml möglich wäre. Die idR vorgebrachten GegenArgumente sind meist nur ScheinArgumente (oft in Verkennung der tatsächlichen Abläufe). Die Fixierung des ErgebnisBereichs kann auch ein Vorteil sein (Erschwerung versehentlichen Überschreibens) und einer nachträglichen Erweiterung des ErgebnisBereichs geht iaR eine Erweiterung des QuellBereichs voraus, so dass ohnehin neu gestaltet wdn muss!
Die Größe des ErgebnisBereichs lässt sich leicht anzeigen, wenn man das dafür vorgesehene Feature benutzt. Außerdem kann man den ja auch färben!
Ansonsten bin ich ebenfalls der Meinung, dass man Xl nicht für Massen-DV missbrauchen sollte. Das kann man mit dem Laden voraggregierter DB-Daten iaR vermeiden (nur dazu müsste man sich näher mit SQL befassen und da liegt wohl oft „der Hase im Pfeffer“!).
- plurale MxFml und Größe des Ergebniszellbereichs
Für das Bsp ist dein Vorschlag irrelevant, denn darum ging's mir hierbei nicht. Es ist ja bekannt, dass man nur soviel Werte aus einer pluralen MxFml angezeigt bekommt, wie in den markierten ErgebnisBereich passen. Nebenbei, Ich hatte schon vor Jahren eine UDF geschrieben, die mit Unterstützung einer ereignisausgelösten Prozedur eine MxFml automatisch auf einen angegebenen bzw den erforderlichen GesamtBereich ausdehnt (sogar inkl Formatierung!). Außerdem hatte ich auch eine Möglichkeit geschaffen, bei Bedarf alle Werte einer nur mit einem Wert präsenten pluralen MxFml zu berechnen (PlatzSparModell).
So, dass war's wohl dazu. Ansonsten hatte ich ja den hybriden Bereich direkt als Bezug in der erforderlichen Schreibweise angegeben. Setzt man den statt deiner AGGREGAT- oder meiner NoErrRange-Konstruktion ein, wird sofort klarer, was passiert:

	I	J	K	L	M
14	Alternative (Direktangabe des Hybridbereichsbezugs)				(vertikal)
15	NN_02	NN_03	NN_07	NN_09	NN_02
16	NN_02	NN_03	NN_07	NN_09	NN_03
17	NN_02	NN_03	NN_07	NN_09	NN_07
18	NN_02	NN_07	NN_09	*Anzahl*	NN_09
19	NN_03			4	Pflaume
20	Pflaume	Grün	Blau	4	Rot
21	Rot			Schriftfarbe	Grün
22	Pflaume	Rot	Grün	Blau	Blau
23	*Formeln*
24	I15:L15: {=INDEX((B16;B17;B21;B23);;;SPALTE(A1:D1))}
25	I22:L22: {=T(JETZT())&CellContIn(INDEX((B16;B17;B21;B23);;;SPALTE(A1:D1));;;"fcn")}
26	I16:L16: {=VSplit(MxJoin((B16;B17;B21;B23)))}
27	I17:L17: {=VSplit(MxJoin((B16:B17;B21;B23)))}
28	I18:K19: {=WENNFEHLER(INDEX((B16:B17;B21;B23);ZEILE(A1:A2);;SPALTE(A1:C1));"")}
29	I20:K21: {=WENNFEHLER(T(JETZT())&CellContIn(INDEX((B16:B17;B21;B23);ZEILE(A1:A2);;SPALTE(A1:C1));;;"fcn");"")}
30	L19:=AGGREGAT(3;6;B16;B17;B21;B23)
31	L20:=AGGREGAT(3;6;B16:B17;B21;B23)
32	M15:M18: {=INDEX((B16;B17;B21;B23);;;ZEILE(A1:A4))}
33	M19:M22: {=WENNFEHLER(T(JETZT())&CellContIn((B16;B17;B21;B23);;;"fcn");"")}

Ich glaube doch, dass du die Zusammenhänge selber herausfindest…! ;-)
Morrn, Luc :-?

Beliebteste Forumthreads (12 Monate)

	A	B	C	D	E	F	G	H
1	Feb	Monat	2
2	15	Summe	15
3
4
5		Jan			Feb			März
6		1			4			7
7		2			5			8
8		3			6			9

Bereichsnamen nutzbar wenn Spalten sich ändern?

17
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

299 Forumthreads zu ähnlichen Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

Bereichsnamen nutzbar wenn Spalten sich ändern?

17 Beiträge zum Forumthread Beiträge zu diesem Forumthread

299 Forumthreads zu ähnlichen Themen

VBA Beispiele zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

17
Beiträge zum Forumthread

Beiträge zu diesem Forumthread