Live-Forum - Die aktuellen Beiträge
Datum
Titel
17.10.2025 17:57:35
Zellen-Name aus A1 nach H1 übertragen mit Prä- + Suffix
17.10.2025 11:36:58
verschachteltes Dropdown-Menü
17.10.2025 10:28:49
Call Anweisung
16.10.2025 18:38:17
Zusammenfassung nach Personalnummer
Keine passende Antwort im Forumarchiv gefunden?
Stelle deine Frage im Live-Forum
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1388to1392
1388to1392
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Anzahl2 als dynamische Funktion

Forumthread: Anzahl2 als dynamische Funktion

Anzahl2 als dynamische Funktion
22.10.2014 19:47:24
Alex
Hallo,
ich bräuchte mal eure Hilfe.
Ich versuche grade mit dynamischen Dropdowns zu arbeiten, habe aber für die 2. abhängige Gültigkeitsliste immer unterschiedlich viele Listenelemente.
Meine Formel sieht so aus:
=BEREICH.VERSCHIEBEN(Informationslisten!F25;VERGLEICH(C9;Informationslisten!F25:F112;0);5;1; ANZAHL2(VERGLEICH(C9;Informationslisten!F25:F112;0)+4);(VERGLEICH(C9;Informationslisten!F25:F112;0) +11))
Das funktioniert natürlich nicht, weil ich für Anzahl2 einen Bereich angeben muss, den ich mit dem was da steht nicht bekomme.
Problem ist: Wie bekomme ich die Adresse des Bereiches so hin, dass ich Koordinaten übergeben kann mit der Anzahl2 etwas anfangen kann?
Der Vergleich gibt mir die Zelle, in der der Suchwert steht. 5 Spalten dahinter stehen X Listenelemente in einer Zeile. Jetzt will ich die Koordinaten vom Start dieses Bereiches bis Ende des Bereiches haben und an Anzahl2 übergeben.
Danke!
VG
Alex

Anzeige

10
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Anzahl2 als dynamische Funktion
22.10.2014 22:25:17
{Boris}
Hi Alex,
mit 2 VERGLEICHen bekommst Du für ANZAHL2 niemals einen "Bereich" hin, da die Funktion VERGLEICH nunmal nur eine ZAHL des Typs Long zurückgibt - also 1 bis irgendwas. Und daraus wird nunmal kein Bereich.
Es wird eine Lösung für Dein Problem geben - aber dafür brauchts konkrete Infos oder - besser noch - Deine (Beispiel-)datei mit den exakten Gegebenheiten.
VG, Boris

Anzeige
AW: Anzahl2 als dynamische Funktion
22.10.2014 23:03:39
Alex
Hi,
ich benutze "Bereich.Verschieben" für die Veränderung einer Dropdown-Liste.
Im Prinzip nach der Art, dass wenn jemand ein Land auswählt, abhängig von der Wahl in einer weiteren Dropdownliste die dazu passenden Städte auswählbar sind.
"=BEREICH.VERSCHIEBEN(Informationslisten!F25;VERGLEICH(C9;Informationslisten!F25:F112;0);5;1;7)"
Nun habe ich das Problem, dass die Anzahl der Listenelemente des zweiten Dropdown-Menues nicht alle gleich sind (z.B. 4 Städte in Land A und 12 Städte in Land B).
Ich könnte es fest verdrahten und sagen, die Listenlänge ist immer 12, dabei wären aber oft nur 2 Elemente drin und der Rest der Auswahl bestände aus leeren Zellen.
Daher versuche ich das letzte Argument in "Bereich.verschieben", die Breite(in Spalten) des neuen Listenbereiches dynamisch zu gestalten.
Das versuche ich mit der Formel "Anzahl2".
Ich habe versucht mit "K"& Vergleich(...) die einzelnen Zellen die er prüfen soll aufzuzählen, leider nimmt er das nicht, ist wohl zu lang.
"=BEREICH.VERSCHIEBEN(Informationslisten!F25;VERGLEICH(C9;Informationslisten!F25:F112;0);5;1;ANZAHL2("K"&VERGLEICH(C9;Informationslisten!F25:F112;0);"L"&VERGLEICH(C9;Informationslisten!F25:F112;0);"M"&VERGLEICH(C9;Informationslisten!F25:F112;0);"O"&VERGLEICH(C9;Informationslisten!F25:F112;0);"P"&VERGLEICH(C9;Informationslisten!F25:F112;0);"Q"&VERGLEICH(C9;Informationslisten!F25:F112;0)))"
Bei Anzahl2 kann man ja auch in der Form "A1:D5" eine Matrix angeben.
Das habe ich mit ("K"& Vergleich(...)):("Q"& Vergleich(...)) auch probiert, macht er aber nicht.
Er erkennt nicht, dass es die Angabe in Matrixform ist (X:Y).
"=BEREICH.VERSCHIEBEN(Informationslisten!F25;VERGLEICH(C9;Informationslisten!F25:F112;0);5;1;ANZAHL2(("K"&VERGLEICH(C9;Informationslisten!F25:F112;0)):("Q"&VERGLEICH(C9;Informationslisten!F25:F112;0))))"
Eine Idee wäre super..thanks!

Anzeige
AW: Anzahl2 als dynamische Funktion
22.10.2014 23:07:11
{Boris}
Hi Alex,
glaub mir - ich weiß ganz genau wie es geht. Zeig mir Deine Datei (mit Wunschergebnis), und Dein Problem wird gelöst sein.
VG, Boris

Am Rande bemerkt...
22.10.2014 23:59:43
{Boris}
Hi Alex,
Ich habe versucht mit "K"& Vergleich(...) die einzelnen Zellen die er prüfen soll aufzuzählen, leider nimmt er das nicht, ist wohl zu lang...
Da ist nix zu lang - Du kennst offensichtlich nur die Funktion INDIREKT nicht, die ein Konstrukt wie "K"& Vergleich(...) zu Leben erwecken kann.
Wie gesagt - nur "Am Rande bemerkt". Dein Problem wird in dem Moment gelöst, wo Du mir (uns) Deine Datei zeigst.
VG, Boris

Anzeige
AW: Am Rande bemerkt...
23.10.2014 01:04:36
Alex
Hi,
ok, anbei die Datei.
https://www.herber.de/bbs/user/93299.xlsx
Die rote Zelle ist einer der Orte, wo die Formel via Gültigkeit(Liste) reinkommen soll.
Thanks!
VG
Alex

AW: Am Rande bemerkt...
23.10.2014 08:16:31
{Boris}
Hi Alex,
aus Sich der "roten" Zelle C9:
=BEREICH.VERSCHIEBEN(Informationslisten!$K$24;VERGLEICH(Übersicht!$C9; Informationslisten!$F$25:$F$112;);;1;ANZAHL2(INDEX(Informationslisten!$K$25:$Q$112; VERGLEICH(Übersicht!$C9;Informationslisten!$F$25:$F$112;);)))
Das ganze über einen Namen in die Gültigkeit einbinden.
VG, Boris

Anzeige
AW: Am Rande bemerkt...
23.10.2014 10:47:26
Alex
Hi Boris,
Problem ist hier, dass er nur 2 Zellen prüft. Ich möchte aber, dass er eine Matrix, also z.b. von K35 bis Q35 abfragt.
Daher mein Versuch, bei Anzahl2 die Schreibweise mit der Matrix zu probieren Anzahl2(XX:YY).

Hast Du es ausprobiert?
23.10.2014 11:43:56
{Boris}
Hi Alex,
hast Du meine Lösung in Deine Mappe eingebaut? Glaub mir doch einfach mal, dass ich weiß, was Du willst und auch weiß wie es geht.
Das INDEX-Konstrukt
INDEX(Informationslisten!$K$25:$Q$112;VERGLEICH(Übersicht!$C9;Informationslisten!$F$25:$F$112;);)
liefert Dir einen horizontalen Vektor über Deine 7 Optionen. Das geht dadurch, dass man - in Deinem Fall - den Parameter Spalte der INDEX-Funktion mit NULL belegt. Und daraus zieht ANZAHL2 dann die Menge der "belegten" Optionen.
VG, Boris

Anzeige
AW: Hast Du es ausprobiert?
23.10.2014 12:58:42
Alex
Hi Boris,
danke für die Hilfe. Es funktioniert.
Folgendes wusste ich noch nicht:
"Erstreckt sich Matrix über mehrere Zeilen und Spalten und ist nur eines der Argumente Zeile oder Spalte angegeben, liefert INDEX eine Matrix, die der gesamten zugehörigen Zeile oder Spalte von Matrix entspricht."
Thanks again!
Gruss
Alex

Anzeige
Die Hilfe ist hier etwas "unsauber"...
23.10.2014 14:07:12
{Boris}
Hi Alex,
an dieser Stelle ist die Hilfe aber nicht ganz korrekt.
=INDEX(A1:D10;2)
geht so nicht (#BEZUG-Fehler). Der fehlende Parameter muss angegeben werden - zumindest mittels ;
Also:
=INDEX(A1:D10;2;)
liefert beispielsweise die Ergebnismatrix aus A2:D2.
VG, Boris
;
Anzeige
Anzeige

Infobox / Tutorial

Dynamische Dropdown-Listen in Excel mit ANZAHL2 und BEREICH.VERSCHIEBEN

Schritt-für-Schritt-Anleitung

  1. Daten vorbereiten: Erstelle eine Tabelle mit den Ländern in einer Spalte und den dazugehörigen Städten in den folgenden Spalten. Zum Beispiel:

    • Spalte F: Länder (F25:F112)
    • Spalten K bis Q: Zu den Ländern gehörige Städte.

  2. Dropdown erstellen: Wähle die Zelle aus, in der das Dropdown-Menü erscheinen soll (z.B. C9). Gehe zu Daten > Datenüberprüfung und wähle Liste aus. Gib in das Feld Quelle die Formel ein:

    =Informationslisten!F25:F112

  3. Dynamische Liste für die Städte: Um eine dynamische Liste zu erstellen, die sich je nach Auswahl des Landes ändert, verwende die folgende Formel in der Gültigkeitsliste:

    =BEREICH.VERSCHIEBEN(Informationslisten!K25;VERGLEICH(C9;Informationslisten!F25:F112;0);;1;ANZAHL2(INDEX(Informationslisten!K25:Q112;VERGLEICH(C9;Informationslisten!F25:F112;0);)))

  4. Formel anpassen: Stelle sicher, dass die Matrix, die du für ANZAHL2 verwendest, korrekt definiert ist. Der Parameter muss so eingestellt sein, dass er die Anzahl der nicht-leeren Zellen in dem von dir gewählten Bereich zählt.

Häufige Fehler und Lösungen

  • Fehler: #BEZUG!: Dieser Fehler tritt auf, wenn der angegebene Bereich nicht korrekt ist. Überprüfe die Zellreferenzen in deiner Formel.

  • Fehler: Ungültige Liste: Achte darauf, dass die Quelle der Dropdown-Liste korrekt definiert ist und dass die Parameter in BEREICH.VERSCHIEBEN stimmen.

  • Formel funktioniert nicht: Wenn die Formel nicht die gewünschten Ergebnisse liefert, überprüfe die VERGLEICH-Funktion. Sie sollte den richtigen Index zurückgeben, um die korrekten Städte zu identifizieren.

Alternative Methoden

  • INDIREKT: Eine alternative Möglichkeit, dynamische Bereiche zu erstellen, ist die Verwendung der Funktion INDIREKT. Diese kann helfen, dynamische Zellreferenzen zu erstellen, die bei Änderungen in der Tabelle automatisch aktualisiert werden.

  • Pivot-Tabellen: Für komplexere Datenanalysen kannst du auch Pivot-Tabellen in Betracht ziehen, um die Daten übersichtlicher darzustellen und dynamische Filter anzuwenden.

Praktische Beispiele

  1. Dropdown für Länder und Städte: Wenn du in C9 "Deutschland" auswählst, könnte die folgende Formel in der Gültigkeit für die Stadtenauswahl stehen:

    =BEREICH.VERSCHIEBEN(Informationslisten!K25;VERGLEICH("Deutschland";Informationslisten!F25:F112;0);;1;ANZAHL2(INDEX(Informationslisten!K25:Q112;VERGLEICH("Deutschland";Informationslisten!F25:F112;0);)))

  2. Dynamische Preislisten: Du kannst ähnliche Konzepte für Preislisten oder Produktkategorien anwenden, um dynamische Dropdown-Menüs zu erstellen, die sich je nach Auswahl ändern.

Tipps für Profis

  • Nutze Namen für Bereiche, um deine Formeln leserlicher und wartungsfreundlicher zu gestalten. So kannst du leichter erkennen, auf welche Daten sich deine Formeln beziehen.

  • Experimentiere mit Array-Formeln für komplexe Berechnungen. Sie ermöglichen es dir, mehrere Berechnungen in einer einzigen Formel durchzuführen, was die Effizienz steigern kann.

  • Datenvalidierung: Überlege, die Datenvalidierung zu erweitern, um sicherzustellen, dass nur gültige Einträge in den Dropdown-Listen gewählt werden können.

FAQ: Häufige Fragen

1. Wie kann ich die Anzahl der angezeigten Elemente in einem Dropdown-Menü begrenzen?
Verwende die Funktion ANZAHL2 in Kombination mit BEREICH.VERSCHIEBEN, um die Anzahl der angezeigten Elemente dynamisch anzupassen.

2. Kann ich auch mehrdimensionale Daten mit ANZAHL2 verarbeiten?
Ja, ANZAHL2 kann auch mit mehrdimensionalen Arrays verwendet werden, solange du sicherstellst, dass die Eingabewerte korrekt definiert sind.

3. Wie kann ich Fehler in meinen Formeln vermeiden?
Achte darauf, dass alle Zellreferenzen korrekt sind und dass du die richtigen Funktionen verwendest, um die gewünschten Ergebnisse zu erzielen.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Entdecke mehr
Finde genau, was du suchst

Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

Suche nach den besten Antworten
Unsere beliebtesten Threads

Entdecke unsere meistgeklickten Beiträge in der Google Suche

Top 100 Threads jetzt ansehen
Anzeige