Microsoft Excel

Herbers Excel/VBA-Archiv

Anzahl2 als dynamische Funktion

Betrifft: Anzahl2 als dynamische Funktion von: Alex
Geschrieben am: 22.10.2014 19:47:24

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

  

Betrifft: AW: Anzahl2 als dynamische Funktion von: {Boris}
Geschrieben am: 22.10.2014 22:25:17

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


  

Betrifft: AW: Anzahl2 als dynamische Funktion von: Alex
Geschrieben am: 22.10.2014 23:03:39

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!


  

Betrifft: AW: Anzahl2 als dynamische Funktion von: {Boris}
Geschrieben am: 22.10.2014 23:07:11

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


  

Betrifft: Am Rande bemerkt... von: {Boris}
Geschrieben am: 22.10.2014 23:59:43

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


  

Betrifft: AW: Am Rande bemerkt... von: Alex
Geschrieben am: 23.10.2014 01:04:36

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


  

Betrifft: AW: Am Rande bemerkt... von: {Boris}
Geschrieben am: 23.10.2014 08:16:31

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


  

Betrifft: AW: Am Rande bemerkt... von: Alex
Geschrieben am: 23.10.2014 10:47:26

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


  

Betrifft: Hast Du es ausprobiert? von: {Boris}
Geschrieben am: 23.10.2014 11:43:56

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


  

Betrifft: AW: Hast Du es ausprobiert? von: Alex
Geschrieben am: 23.10.2014 12:58:42

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


  

Betrifft: Die Hilfe ist hier etwas "unsauber"... von: {Boris}
Geschrieben am: 23.10.2014 14:07:12

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


 

Beiträge aus den Excel-Beispielen zum Thema "Anzahl2 als dynamische Funktion"