Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1548to1552
1548to1552
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

INDEX statt INDIREKT bei Formel ZÄHLENWENN..

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

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
1Test1
20
30

Formeln der Tabelle
ZelleFormel
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
Anzeige

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

Betreff
Datum
Anwender
Anzeige
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 …
Anzeige
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

 ABCD
1DatumSpaltenTest1-Test2Test1-Test2-Test3
201.01.2017c351-324351-324-259
302.01.2017d323-310323-310-262
403.01.2017e321-324321-324-285
504.01.2017f321-351321-351-281
605.01.2017g357-326357-326-315

Formeln der Tabelle
ZelleFormel
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
Anzeige
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
.. , - ...
Anzeige
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

 HIJ
1SpaltenTest2-Test5Test2-Test5-Test6
2d324-408324-408-437
3g310-415310-415-471
4h324-395324-395-429
5k351-384351-384-464
6m326-410326-410-504
7 337-405337-405-458

Formeln der Tabelle
ZelleFormel
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
Anzeige
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
.. , - ...
Anzeige
AW: nun aber auch ;-) denn ...
08.04.2017 08:29:14
erichm
DANKE - eigentlich logisch die Änderung.
mfg
;
Anzeige
Anzeige

Infobox / Tutorial

INDEX statt INDIREKT bei ZÄHLENWENN-Formeln in Excel

Schritt-für-Schritt-Anleitung

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

  1. 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.

  2. 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)

  3. 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:

  1. 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.

  2. 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:

  1. 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););))

  2. 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)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige