INDEX statt INDIREKT bei Formel ZÄHLENWENN..

Forumthread: INDEX statt INDIREKT bei Formel ZÄHLENWENN..

05.04.2017 22:24:01

erichm

Hallo,
ich habe eine Formel mit ZÄHLENWENN, die innerhalb einer Spalte die Inhalte von mehreren Zeilen vergleicht. Die Anzahl der Zeilen ist variabel von Spalte zu Spalte. Um dies zu realisieren, habe ich eine Formel mit INDIREKT erstellt. Meine Versuche diese auf INDEX umzustellen sind alle mißlungen. So ist die Formel:
Ergebnis

	C
1	Test1
2	0
3	0

Formeln der Tabelle

Zelle	Formel
C1	=+Grunddaten!C1
C2	=WENN(ZÄHLENWENN(INDIREKT("Grunddaten!"&C$22&ZEILE()&":"&C$22&ZEILE()+C$21); Grunddaten!C2)>1;1;0)
C3	=WENN(ZÄHLENWENN(INDIREKT("Grunddaten!"&C$22&ZEILE()&":"&C$22&ZEILE()+C$21); Grunddaten!C3)>1;1;0)

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Ich habe auch eine kleine Musterdatei hochgeladen:
https://www.herber.de/bbs/user/112675.xlsx
Besten Dank für eine Hilfe - mfg

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

Das Folgende reicht schon, ...

06.04.2017 02:17:37

Luc:-?

…Erich;
für C2 (und von dort aus nach rechts und unten ziehen):
=--(ZÄHLENWENN(INDEX(Grunddaten!C:C;ZEILE()):INDEX(Grunddaten!C:C;ZEILE()+C$21);Grunddaten!C2)>1)
Beachte, dass die Fml in Spalte J ab Zeile 12 auch auf Leerzellen in Grunddaten zugreift, denn 12+17=29 bis 17+17=34!
Morrn, Luc :-?
Besser informiert mit …

AW: Das Folgende reicht schon, ...

06.04.2017 07:16:06

erichm

Hallo Luc,
danke - das klappt perfekt.
Kann ich diese INDIREKT-Formel auch auf INDEX oder eine andere mit weniger Ressourcenbedarf ändern; dabei geht es um keine Berechnung, sondern nur um eine Übertragung von Zellinhalten aus einer anderen Tabelle:
Ergebnis

	A	B	C	D
1	Datum	Spalten	Test1-Test2	Test1-Test2-Test3
2	01.01.2017	c	351-324	351-324-259
3	02.01.2017	d	323-310	323-310-262
4	03.01.2017	e	321-324	321-324-285
5	04.01.2017	f	321-351	321-351-281
6	05.01.2017	g	357-326	357-326-315

Formeln der Tabelle

Zelle	Formel
C1	=INDIREKT("'Grunddaten'!"&$B$2&ZEILE())&"-"&INDIREKT("'Grunddaten'!"&$B$3&ZEILE())
D1	=INDIREKT("'Grunddaten'!"&$B$2&ZEILE())&"-"&INDIREKT("'Grunddaten'!"&$B$3&ZEILE())&"-"&INDIREKT("'Grunddaten'!"&$B$4&ZEILE())
A2	=+Grunddaten!A2
C2	=INDIREKT("'Grunddaten'!"&$B$2&ZEILE())&"-"&INDIREKT("'Grunddaten'!"&$B$3&ZEILE())
D2	=INDIREKT("'Grunddaten'!"&$B$2&ZEILE())&"-"&INDIREKT("'Grunddaten'!"&$B$3&ZEILE())&"-"&INDIREKT("'Grunddaten'!"&$B$4&ZEILE())
A3	=+Grunddaten!A3
C3	=INDIREKT("'Grunddaten'!"&$B$2&ZEILE())&"-"&INDIREKT("'Grunddaten'!"&$B$3&ZEILE())
D3	=INDIREKT("'Grunddaten'!"&$B$2&ZEILE())&"-"&INDIREKT("'Grunddaten'!"&$B$3&ZEILE())&"-"&INDIREKT("'Grunddaten'!"&$B$4&ZEILE())
A4	=+Grunddaten!A4
C4	=INDIREKT("'Grunddaten'!"&$B$2&ZEILE())&"-"&INDIREKT("'Grunddaten'!"&$B$3&ZEILE())
D4	=INDIREKT("'Grunddaten'!"&$B$2&ZEILE())&"-"&INDIREKT("'Grunddaten'!"&$B$3&ZEILE())&"-"&INDIREKT("'Grunddaten'!"&$B$4&ZEILE())
A5	=+Grunddaten!A5
C5	=INDIREKT("'Grunddaten'!"&$B$2&ZEILE())&"-"&INDIREKT("'Grunddaten'!"&$B$3&ZEILE())
D5	=INDIREKT("'Grunddaten'!"&$B$2&ZEILE())&"-"&INDIREKT("'Grunddaten'!"&$B$3&ZEILE())&"-"&INDIREKT("'Grunddaten'!"&$B$4&ZEILE())
A6	=+Grunddaten!A6
C6	=INDIREKT("'Grunddaten'!"&$B$2&ZEILE())&"-"&INDIREKT("'Grunddaten'!"&$B$3&ZEILE())
D6	=INDIREKT("'Grunddaten'!"&$B$2&ZEILE())&"-"&INDIREKT("'Grunddaten'!"&$B$3&ZEILE())&"-"&INDIREKT("'Grunddaten'!"&$B$4&ZEILE())

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Die gelb markierten Buchstaben in der Spalte B sind variabel und werden immer wieder geändert. Ich könnte auch anstelle des Buchstabens die Spaltennummer eintragen. Diese gelb markierten Spalten betreffen die jeweiligen Spalten aus der Tabelle Grunddaten.
Hier wieder eine kleine Beispieldatei:
https://www.herber.de/bbs/user/112677.xlsx
Vielen Dank nochmal.
mfg

AW: auch das ist mit INDEX() möglich ...

06.04.2017 11:45:16

...

Hallo Erich,
... jedoch wenn Deine Buchstaben-Vorgabe auch mehrstellig werden kann, dann bedarf es zumindest einer Matrixfunktion(alität)sformel (die zwar ohne den spez. Formelabschluss einer klassischen Matrixformel auskommt) aber eben auch ähnlich wie diese Ressourcen bindet. Wenn nur die Spalten bis Spalte Z benötigt werden, wird es allerdings wieder einfacher, weil man da mit CODE() arbeiten könnte.
Hier die Formeln für Datenübernahme bis z.B Spalte BZ:
In C1:
=INDEX(Grunddaten!$A:$BZ;ZEILE();VERGLEICH($B$2&"*";INDEX(ADRESSE(1;SPALTE($A$1:$BZ$1);4;1););))
&"-"&INDEX(Grunddaten!$A:$BZ;ZEILE();VERGLEICH($B$3&"*";INDEX(ADRESSE(1;SPALTE($A$1:$BZ$1);4;1););))
in D1: =C1&"-"&INDEX(Grunddaten!$A:$BZ;ZEILE();VERGLEICH(INDEX($B:$B;SPALTE())&"*";INDEX(ADRESSE(1;SPALTE($A$1:$BZ$1);4;1););))
D1 wird nach rechts und alle Formeln nach unten kopieren .
Gruß Werner
.. , - ...

AW: auch das ist mit INDEX() möglich ...

06.04.2017 21:24:04

erichm

Vielen Dank Werner!
Die Formeln funktionieren natürlich - und ich bin mal gespannt wie sich diese auf Zeit- und Ressourcenbedarf auswirken. Die Spalten sind auf alle Fälle bis zu zwei Buchstaben.
mfg

Da war ich leider zu schnell.........

07.04.2017 08:17:00

erichm

.....jetzt habe ich doch noch ein Formel-Problem:
Ich habe in meiner hochgeladenen Beispieldatei die 2. Variante dargestellt, dass der Spaltenzugriff auf die Tabelle Grunddaten nicht in der Spalte B sondern in der Spalte H erfolgt.
Ergebnis

	H	I	J
1	Spalten	Test2-Test5	Test2-Test5-Test6
2	d	324-408	324-408-437
3	g	310-415	310-415-471
4	h	324-395	324-395-429
5	k	351-384	351-384-464
6	m	326-410	326-410-504
7		337-405	337-405-458

Formeln der Tabelle

Zelle	Formel
I1	=INDIREKT("'Grunddaten'!"&$H$2&ZEILE())&"-"&INDIREKT("'Grunddaten'!"&$H$3&ZEILE())
J1	=INDIREKT("'Grunddaten'!"&$H$2&ZEILE())&"-"&INDIREKT("'Grunddaten'!"&$H$3&ZEILE())&"-"&INDIREKT("'Grunddaten'!"&$H$4&ZEILE())
I2	=INDIREKT("'Grunddaten'!"&$H$2&ZEILE())&"-"&INDIREKT("'Grunddaten'!"&$H$3&ZEILE())
J2	=INDIREKT("'Grunddaten'!"&$H$2&ZEILE())&"-"&INDIREKT("'Grunddaten'!"&$H$3&ZEILE())&"-"&INDIREKT("'Grunddaten'!"&$H$4&ZEILE())
I3	=INDIREKT("'Grunddaten'!"&$H$2&ZEILE())&"-"&INDIREKT("'Grunddaten'!"&$H$3&ZEILE())
J3	=INDIREKT("'Grunddaten'!"&$H$2&ZEILE())&"-"&INDIREKT("'Grunddaten'!"&$H$3&ZEILE())&"-"&INDIREKT("'Grunddaten'!"&$H$4&ZEILE())
I4	=INDIREKT("'Grunddaten'!"&$H$2&ZEILE())&"-"&INDIREKT("'Grunddaten'!"&$H$3&ZEILE())
J4	=INDIREKT("'Grunddaten'!"&$H$2&ZEILE())&"-"&INDIREKT("'Grunddaten'!"&$H$3&ZEILE())&"-"&INDIREKT("'Grunddaten'!"&$H$4&ZEILE())
I5	=INDIREKT("'Grunddaten'!"&$H$2&ZEILE())&"-"&INDIREKT("'Grunddaten'!"&$H$3&ZEILE())
J5	=INDIREKT("'Grunddaten'!"&$H$2&ZEILE())&"-"&INDIREKT("'Grunddaten'!"&$H$3&ZEILE())&"-"&INDIREKT("'Grunddaten'!"&$H$4&ZEILE())
I6	=INDIREKT("'Grunddaten'!"&$H$2&ZEILE())&"-"&INDIREKT("'Grunddaten'!"&$H$3&ZEILE())
J6	=INDIREKT("'Grunddaten'!"&$H$2&ZEILE())&"-"&INDIREKT("'Grunddaten'!"&$H$3&ZEILE())&"-"&INDIREKT("'Grunddaten'!"&$H$4&ZEILE())
I7	=INDIREKT("'Grunddaten'!"&$H$2&ZEILE())&"-"&INDIREKT("'Grunddaten'!"&$H$3&ZEILE())
J7	=INDIREKT("'Grunddaten'!"&$H$2&ZEILE())&"-"&INDIREKT("'Grunddaten'!"&$H$3&ZEILE())&"-"&INDIREKT("'Grunddaten'!"&$H$4&ZEILE())

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Die Formel von neopa funktioniert in der 2. Variante auch mit dem kopieren / anpassen von C1 nach I1.
Wenn ich dann analog von D1 nach J1 kopiere, gelingt mir leider nicht die notwendige Anpassung.....
Ich denke, das müsste aber irgendwie möglich sein.
Danke nochmal.
mfg

AW: nun aber auch ;-) denn ...

07.04.2017 14:23:27

...

Hallo Erich,
... die notwendigen geringfügie Anpassung der Formeln wären Dir bestimmt auch gelungen.
Nun in I1:
=INDEX(Grunddaten!$A:$BZ;ZEILE();VERGLEICH($H$2&"*";INDEX(ADRESSE(1;SPALTE($A$1:$CF$1);4;1););))
&"-"&INDEX(Grunddaten!$A:$BZ;ZEILE();VERGLEICH($H$3&"*";INDEX(ADRESSE(1;SPALTE($A$1:$CF$1);4;1););)
und nach unten kopieren
in J1:
=I1&"-"&INDEX(Grunddaten!$A:$BZ;ZEILE();VERGLEICH(INDEX($H:$H;SPALTE())&"*";INDEX(ADRESSE(1;SPALTE($A$1:$CF$1);4;1););)) und nach rechts und unten kopieren.
Gruß Werner
.. , - ...

AW: nun aber auch ;-) denn ...

08.04.2017 08:29:14

erichm

DANKE - eigentlich logisch die Änderung.
mfg

Infobox / Tutorial

INDEX statt INDIREKT bei ZÄHLENWENN-Formeln in Excel

Schritt-für-Schritt-Anleitung
Häufige Fehler und Lösungen
Alternative Methoden
Praktische Beispiele
Tipps für Profis
FAQ: Häufige Fragen

Schritt-für-Schritt-Anleitung

Um eine ZÄHLENWENN-Formel von INDIREKT auf INDEX umzustellen, folge diesen Schritten:

Vorbereitung der Daten: Stelle sicher, dass deine Daten in der Tabelle „Grunddaten“ ordentlich strukturiert sind. Der Zugriff auf die Daten wird durch die Verwendung von INDEX und ZÄHLENWENN erleichtert.

Formel anpassen:

Original Formel mit INDIREKT:

=WENN(ZÄHLENWENN(INDIREKT("Grunddaten!"&C$22&ZEILE()&":"&C$22&ZEILE()+C$21); Grunddaten!C2)>1; 1; 0)

Neue Formel mit INDEX:

=--(ZÄHLENWENN(INDEX(Grunddaten!C:C;ZEILE()):INDEX(Grunddaten!C:C;ZEILE()+C$21);Grunddaten!C2)>1)

Formel kopieren: Ziehe die Formel von C2 nach rechts und nach unten, um die Berechnungen für andere Zellen anzuwenden.

Häufige Fehler und Lösungen

Fehler: Die Formel gibt „#BEZUG!“ zurück.
- Lösung: Stelle sicher, dass die Referenzen in der INDEX-Formel korrekt sind und dass die Daten in der Tabelle „Grunddaten“ vorhanden sind.
Fehler: Ergebnisse stimmen nicht mit den Erwartungen überein.
- Lösung: Überprüfe, ob die Kriterien in der ZÄHLENWENN-Funktion korrekt gesetzt sind und ob die Zellreferenzen stimmen.

Alternative Methoden

Falls du die Verwendung von INDEX und ZÄHLENWENN umgehen möchtest, gibt es alternative Methoden:

SVERWEIS: Diese Funktion kann genutzt werden, um Werte aus einer anderen Tabelle zu suchen und zurückzugeben, ist jedoch weniger flexibel als INDEX und ZÄHLENWENN.
FILTER-Funktion (ab Excel 365): Diese Funktion ermöglicht es dir, Daten dynamisch zu filtern und kann in vielen Fällen einfacher anzuwenden sein.

Praktische Beispiele

Hier sind einige praktische Beispiele, wie du INDEX statt INDIREKT in verschiedenen Situationen anwenden kannst:

Datenübertragung aus „Grunddaten“:

Formel für eine Zelle in Spalte C:

=INDEX(Grunddaten!$A:$BZ;ZEILE();VERGLEICH($B$2&"*";INDEX(ADRESSE(1;SPALTE($A$1:$BZ$1);4;1););))

Verkettung von Werten:

Um mehrere Werte aus verschiedenen Spalten zu kombinieren, kannst du folgende Formel verwenden:

=INDEX(Grunddaten!$A:$BZ;ZEILE();VERGLEICH($H$2&"*";INDEX(ADRESSE(1;SPALTE($A$1:$BZ$1);4;1););)) & "-" & INDEX(Grunddaten!$A:$BZ;ZEILE();VERGLEICH($H$3&"*";INDEX(ADRESSE(1;SPALTE($A$1:$BZ$1);4;1););))

Tipps für Profis

Verwende absolute und relative Zellreferenzen: Achte darauf, wo du absolute ($) und relative Referenzen in deinen Formeln verwendest, um unerwartete Ergebnisse beim Ziehen der Formeln zu vermeiden.
Matrixformeln: Bei komplexeren Anforderungen kann die Verwendung von Matrixformeln hilfreich sein, um mehrere Werte gleichzeitig zu verarbeiten.
Leistungsoptimierung: Wenn du große Datenmengen verarbeitest, kann es sinnvoll sein, die Anzahl der verwendeten Berechnungen zu minimieren, indem du auf weniger ressourcenintensive Formeln zurückgreifst.

FAQ: Häufige Fragen

1. Frage
Wie kann ich meine Formeln auf eine andere Tabelle anwenden?
Antwort: Du kannst die Tabellenreferenzen in deiner Formel einfach anpassen, indem du den Tabellennamen änderst, z.B. INDEX(Tabelle2!$A:$C;...).

2. Frage
Gibt es eine Möglichkeit, die Formeln zu überprüfen?
Antwort: Ja, du kannst die Funktion „Formel anzeigen“ in Excel aktivieren, um alle Formeln in der Tabelle sichtbar zu machen. Das hilft dir, Fehler schneller zu identifizieren.

3. Frage
Was sind die Unterschiede zwischen INDEX und SVERWEIS?
Antwort: INDEX ermöglicht eine flexiblere Datenabfrage, da du sowohl Zeilen als auch Spalten unabhängig ansprechen kannst, während SVERWEIS nur in einer Richtung (von links nach rechts) sucht.

Beliebteste Forumthreads (12 Monate)

INDEX statt INDIREKT bei Formel ZÄHLENWENN..

Forumthread: INDEX statt INDIREKT bei Formel ZÄHLENWENN..

7 Beiträge zum Forumthread Beiträge zu diesem Forumthread

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

INDEX statt INDIREKT bei ZÄHLENWENN-Formeln in Excel

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

7
Beiträge zum Forumthread

Beiträge zu diesem Forumthread