Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1872to1876
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Berechnungen mit Bereichsnamen

Berechnungen mit Bereichsnamen
19.03.2022 18:00:40
erichm
Hallo,
ich habe verschiedene Bereichsnamen für Berechnungen definiert. Die Bereichsnamen sind so gewählt, dass diese immer den ersten Ziffern in Spalte A bis zu dem Zeichen „_“ entsprechen. Diese ersten Ziffern habe ich in der Spalte D „extrahiert“, so dass mit dem Zellinhalt von Spalte D quasi der Bereichsname „aktiviert“ werden kann. Diese Aktivierung / Berechnung benötige ich in Spalte AK.
Musterdatei:
https://www.herber.de/bbs/user/151883.xlsx
Ich habe eine Lösung mit einer WENN-Formel:
=WENN(D2="KK_";KK_;WENN(D2="KDKL_";KDKL_;WENN(D2="KK55_";KK55_;WENN(D2="MIX1KK55_";MIX1KK55_; WENN(D2="MIX1KK3KK4_";MIX1KK3KK4_;"leer")))))
PROBLEM: Es werden immer mehr Bereichsnamen in der Datei, so dass die Formel immer länger wird und dann nicht mehr praktikabel ist.
Jetzt habe ich mehrere Versuche für eine andere Lösung unternommen, die aber alle nicht funktionieren:
Siehe Spalte AL mit =INDEX(AX:AX;VERGLEICH(D2;AW:AW;0))
Siehe Spalte AM mit =XVERWEIS(D2;$AW$2:$AW$6;$AX$2:$AX$6)
Siehe Spalte AN mit =WENN(A2="";"";"="&D2)
In den Spalten AW und AX habe ich als Hilfsspalten die Bereichsnamen aufgelistet
Gibt es eine alternative Möglichkeit zur WENN-Formel?
Vielen Dank für eine Hilfe.
mfg

36
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Berechnungen mit Bereichsnamen
19.03.2022 18:46:32
onur
Ich weiss weder, was du mit "aktivieren" meinst, noch, was du überhaupt GENAU erreichen willst.
AW: Berechnungen mit Bereichsnamen
19.03.2022 19:04:09
erichm
Es geht "nur" darum, dass in der Spalte AK in jeder Zeile immer der Bereichsname (jeder Bereichsname hat eine eigene Berechnung / Formel hinterlegt) verwendet wird, der namentlich in der gleichen Zeile mit der Spalte D identisch ist.
Mit der WENN-Formel funktionieren die Berechnungen.
Ohne die WENN-Formel müsste ich immer manuell pro Zeile in der Spalte AK eingeben: ="Bereichsname XX"; da sich die Inhalte der Spalte A aber immer wieder ändern, geht das in der Praxis eigentlich nicht.
Ich verstehe momentan nicht, warum mit der WENN-Formel die Berechnung funktioniert - und bei anderen Lösungen wird nur der Bereichsname angezeigt, anstatt dass die Berechnung stattfindet?
Danke.
mfg
Anzeige
AW: Berechnungen mit Bereichsnamen
19.03.2022 20:23:03
onur
Deine Formeln für die Bereichsdefinitionen taugen doch schon nix. Teste doch mal und schreibe in irgend eine Zelle:
= KK_
AW: Berechnungen mit Bereichsnamen
19.03.2022 21:28:33
erichm
Also, wenn ich in den Zellen AK2 und AK3 oder Spalte AL jeweils teste, funtkioniert das alles. Die Formeln sind für die Spalten ab AK erstellt.
mfg
AW: ich lass den thread offen, hier nur ...
19.03.2022 19:53:37
neopa
Hallo Erich,
... zunächst festgestellt, dass mW benannte Formeln bisher immer nur direkt oder verschachtelt in anderen benannten Formeln aufgerufen werden konnten. Mit Deinen "Versuchsformeln" werden lediglich Textwerte ermittelt und somit nicht die benannten Formeln Ob es eine indirekte Aktivierungsmöglichkeit unter zu Hilfenahme von INDIREKT() gibt, ist mir bisher nicht bekannt, konnte ich eben auch in paar schnellen Versuchen nicht realisieren. Was aber nicht heißen soll, dass es die Möglichkeit nicht doch geben kann.
Aber Du hast ja eine aktuelle XL-Version (die ich nicht habe). Vielleicht lässt sich da den dort vorhandenen Funktionsmöglichkeiten, dass was Du als Ziel anstrebst auch ganz anders realisieren.
Gruß Werner
.. , - ...
Anzeige
AW: ich lass den thread offen, hier nur ...
19.03.2022 21:32:05
erichm
Danke Werner für den Hinweis. Ich dachte mir, mit LAMBDA könnte das evtl. funktionieren, aber entweder gehts nicht oder ich mach da noch Fehler bei der Erstellung. Hatte ich im ersten Beitrag nicht erwähnt, weil ich immer nur Fehlermeldungen erhalte.
Werde jedenfalls weiterhin testen.
mfg
AW: dann dazu nachgefragt ...
20.03.2022 09:41:11
neopa
Hallo Erich,
... mich würde es rein interessehalber interessieren, evtl. eine Lösung auch in meiner vorhandenen XL-Version zu entwickeln. Dazu benötige ich jedoch noch ein paar Angaben Deinerseits. Könntest Du diese zur Verfügung stellen. So u.a. die Erweiterung Deiner Daten so, das auch für für ein Datensatz mit Beginn KK_ und auch mit KK55_ein "JA" Ergebnis ermittelt wird und ein Datensatz mit Beginn KDKL_ welches ein "Nein" ergibt. Da ich kein XL365 habe ist mir momentan noch klar, warum Du für KDKL_ mit XVERWEIS() arbeitest und für KK_ mit SVERWEIS(). Offensichtlich wird dazu für das vorhandene Datenbeispiel eine Erweiterung Deiner Daten nach Spalte AT um 3 Spalten notwendig. Oder?
Gruß Werner
.. , - ...
Anzeige
AW: dann dazu nachgefragt ...
20.03.2022 10:35:44
erichm
Hallo Werner,
die Änderung auf XVERWEIS diente nur für mich zur Aktualisierung der Formel.
Ich habe eine neue Musterdatei bei der für jeden Bereichsnamen, nach rechts kopiert, jeweils mindestens ein JA und ein NEIN enthalten ist.
https://www.herber.de/bbs/user/151898.xlsx
Danke.
mfg
AW: bedarf noch Erläuterungen ...
20.03.2022 13:20:55
neopa
Hallo Erich,
... warum wertest Du mit Deinen benannten Formeln KK_, KK55_, MIX1KK55_ die Daten für Spalte AK mit SVERWEIS() direkt aus AQ aus? Die Ergebnisse dafür müssen demzufolge doch da stets "Nein" werden wie auch in AL, da Du dann die Spalte AR auswertest, in der bei Dir nichts steht.
Die benannten Formeln mit XVERWEIS() werten immer nur die Spalte AS für AQ aus und nur J3 bzw. 6? Oder? (wenn XVERWEIS() nur für Dich zur Aktualisierung diente, wie sah denn zuvor Deine Formel hierfür aus?)
Dein Ergebnis für AM5 ("JA") kann ich momentan auch nicht nachvollziehen., denn für J5 ergibt sich in Spalte AU doch ein "nein" und somit gibt es für diese Spalte nur 3 mal ein "Ja" und somit ist die UND()-Beziehung nicht gewährleistet. Oder wie muss ich das verstehen.
Gruß Werner
.. , - ...
Anzeige
AW: bedarf noch Erläuterungen ...
20.03.2022 15:42:57
erichm
Hallo Werner,
auf diese Details hatte ich bei der Vorbereitung nicht geachtet, da es mir nur darum ging, einen Weg zu finden, mit dem ich die WENN-Formel ersetzen kann.
Eigentlich muss man in der Zelle AK85 mit 3, statt mit 1 beginnen; dann wird ab Spalte AS ausgewertet (bei SVERWEIS) und bei XVERWEIS ändert sich die Berechnung ab AS nach rechts ja automatisch.
mfg
AW: damit ist aber nur eine Fragen geklärt ...
20.03.2022 16:33:15
neopa
Hallo Erich,
... e steht noch die Deine Erklärung für Deinen Einsatz von XVERWEIS() aus, bzw. ist mir jetzt unklar, wieso Du in AK6 ein "Ja" ermitteln willst.
Stelle doch mal für Deine Beispieldaten in J2:N6 sowie AQ2:AU25 die von Dir erwarteten Ergebniswerte in AK2:AM6 ohne Formeln auf und zwar zunächst einmal für den Fall, dass in D2:D6 nur KK_ oder KK55_ ermittelt wären.
Nachdem was ich mir bisher aus Deinen Angaben interpretiert habe, komme ich auf folgendes:
Arbeitsblatt mit dem Namen 'BasLL'
 AKALAM
2JANEINNEIN
3JANEINNEIN
4JANEINNEIN
5NEINNEINNEIN
6NEINNEINNEIN
Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Gruß Werner
.. , - ...
Anzeige
AW: damit ist aber nur eine Fragen geklärt ...
20.03.2022 17:26:38
erichm
Hallo Werner,
ich hoffe, das richtig verstanden zu haben. Die neue Datei enthält einen Block mit KK_ und einen Block mit KK55_; hierzu habe ich einige Daten ergänzt und blau markiert.
https://www.herber.de/bbs/user/151913.xlsx
Deine Ergebnisse konnte ich nicht nachvollziehen.
mfg
AW: unklar ist nun ...
20.03.2022 19:14:05
neopa
Hallo Erich,
... warum Du für KK_ immer nur die ersten drei Werte aus J:AJ auswertest, obwohl Du doch hier im Beispiel stets 5 sind?
Meine Lösungsformel würde stets die jeweilige vorhandene Anzahl an auszuwertenden Datenwerten in J:AJ je Datensatz auswerten und somit in Deiner neuen Beispieldatei die gleichen Ergebnisse in AK2:AM6 wie in AK11:AM15 erzielen.
Gruß Werner
.. , - ...
Anzeige
AW: unklar ist nun ...
20.03.2022 19:49:55
erichm
Hallo Werner,
das hängt jetzt mit den Änderungen zusammen. Also das Grundprinzip ist so:
KK_ sind 3 Teile
KK55_ sind 5 Teile
Ich hatte jetzt nur die Übersicht angepasst / erweitert; also im Block KK_ könnte man auch die beiden letzten Teile (T4 und T5) entfernen. In der Originaldatei wird das aus der Spalte A gespeist; diese zusätzlichen Formeln habe ich hier aber weggelassen, weil ich auch teilweise was geändert habe.
mfg
AW: nun, dann reicht eine Formel ...
21.03.2022 08:32:10
neopa
Hallo Erich,
... um die Formel zu verkürzen habe ich eine formatierte Tabelle gemäß Deinen Vorgaben wie folgt definiert:
Arbeitsblatt mit dem Namen 'BasLL'
 GDGE
1DatAnz
2ANLKK_1
3BERKL_2
4KK_3
5KDKL_3
6KKLMM55_3
7MALTE55_4
8KK55_5
9MIX1KK55_6
10MIX1KK3KK4_9
Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

und dieser Tabelle den Namen _DatAnz zugewiesen. Die ausgegrauten Werte müsstest Du dann noch nach Deinen Erfordernissen anpassen.
Bei aktivierter Zelle AK2 habe ich dann folgende benannte Formel definiert:
_Pruef
=WENN(!$A2="";"";WENN(SUMME(ZÄHLENWENN(!$J2:INDEX(!$J2:$AJ2;SVERWEIS(!$D2;_DatAnz;2;0));!$AQ$2:$AQ$25)*(!AS$2:AS$25="JA"))=SVERWEIS(!$D2;_DatAnz;2;0);"JA";"NEIN"))
(in Deiner XL Version könntest Du darin noch den 2fachen Formelteil SVERWEIS(!$D2;_DatAnz;2;0) mit LET() einkürzen)
Anschließend in AK2: =_Pruef einsetzen und diese nach unten und rechts ziehend kopieren.
Gruß Werner
.. , - ...
Anzeige
Faszinierend..........
21.03.2022 18:28:58
erichm
Hallo Werner,
das ist natürlich eine sehr komfortable Lösung (muss mich in die Formel aber noch einlesen....) und erleichtert die Analyse / Auswertung deutlich.
Vielen Dank mal wieder - auch für die Geduld.
mfg
noch eine Frage
22.03.2022 09:03:10
erichm
Bei der Eingabe im Namensmanager in der Zeile "Bezieht sich auf:" ist die Anzeigenbreite ja begrenzt auf die Bildschirmbreite (Vergrößerung der Eingabezeile durch Pfeil und verbreitern durch ziehen).
In der Regel reicht die Eingabebreite aus. Wenn eine Eingabe / Formel aber länger wird, ist die Eingabe recht umständlich. Gibt es eine Möglichkeit die Zeile zu vergrößern (zweizeilig) oder einen Trick wie man die Eingabe vereinfachen kann. Mit Erstellung der Eingabe in einer normalen Zelle und dann kopieren und einfügen in den Namensmanager klappt das nicht (bei mir) - vielleicht mache ich ja was falsch?
Danke.
mfg
Anzeige
AW: zur Definition benannter Formeln ...
22.03.2022 09:26:21
neopa
Hallo Erich,
... eine derartige Definition nehme ich grundsätzlich zunächst in einer der Zellen vor, in dessen Zelle ich sie später einsetzen will. Wenn diese korrekt definiert ist, kopiere ich diese dort, aktiviere den Namensmanager und füge sie dort als neu oder evtl. auch als Aktualisierung der bereits vorhandenen ein. Da kann man normalerweise nicht viel falsch machen, insofern kann ich Deine diesbzgl. Angaben momentan nicht nachvollziehen.
Die dortigen "Eingabezeile" nutze ich danach nur noch, wenn eine minimale nachträgliche Änderung notwendig wird. Bei Aktivierung dieser Zeile und anschließenden betätigen [von F2] kann man sich auch in der Eingabezeile mit Hilfe der Cursortasten problemlos bewegen. Das Namensmanagerfenster kann man ja auch auf Bildschirmbreite ziehen, aber so lang sollte auch möglichst keine Formel werden. Dann besser die Formel strukturieren und mit zusätzlichen Definitionen arbeiten.
Gruß Werner
.. , - ...
Anzeige
AW: zur Definition benannter Formeln ...
22.03.2022 10:54:48
erichm
Hallo Werner,
wenn ich die Formel in einer Zelle erstellt habe, dann kopiere, in den Namensmanager wechsle und dann in die Zeile zum einfügen - dann ist Zwischenablage nicht mehr da bzw. kann nicht darauf zugegriffen werden. Dabei ist es egal, ob ich die "Zelle kopiere" oder in die Zelle gehe, die Formel markiere und dann kopiere. Ich habe aber auch nichts gefunden, wo ich meine Einstellungen wegen der Zwischenablage ändern müsste.....seltsam
kurz zurück zu meiner ursprünglichen Farge und Deiner Lösung mit _Pruef:
Ich denke, die Formel habe ich jetzt verstanden. Ich versuche bereits eine Erweiterung, da ich dann nochmals optimieren könnte. Ich füge nach der Spalte GD zumindest zwei Spalten ein. Dort kommen weitere Kürzel wie KK_; KK55_ hinzu. Dann werden in den Spalten GI und GJ (das sind dann die Spalten nach Einfügung, also aus GE wird GH) wieder die Anz-Zahlen eingetragen.
Die Zeile 4 von Deinem Beispiel sieht dann wie folgt aus:
KK_ // KK55_ // KDKL_ /// 3 // 5 // 3
In Spalte A beginnt dann die Zeile mit KK_KK55_KDKL_ und hat 11 Teile zum auswerten.
Mal sehen wie weit ich komme; evtl. muss ich die "_"Striche weglassen oder die Bezeichnungen noch modifizieren, damit diese in der formatierten Tabelle gefunden werden.
Die Optimierung mit LET beim Sverweis werde ich noch einbauen.
mfg
AW: hierzu ...
22.03.2022 11:01:45
neopa
Hallo Erich,
... melde ich mich morgen. Bin jetzt erst einmal offline.
Gruß Werner
.. , - ...
einen kleinen Schritt weiter
22.03.2022 18:35:35
erichm
Ich habe mir überlegt, für die 3 Teile KK_ / KK55_ / KDKL_ jeweils eigene _Pruef zu machen. Die Summe und / oder Verkettung der _Pruef-Ergebnisse ergeben dann das endgültige Ergebnis.
Den Sverweis-Teil habe ich in LET gepackt: _abGE=LET(abGE;SVERWEIS(!$D2;_DatAnz;4;0);abGE) für _Pruef1; für _Pruef2 ist es dann _abGE2
(Hinweis: im Namensmanager gebe ich den Namen mit _abGE an, dann kann ich besser zuordnen)
Jetzt habe ich _Pruef2 erstellt:
=WENN(!$A2="";"";WENN(SUMME(ZÄHLENWENN(!$J2:INDEX(!$J2:$AJ2;_abGE2);!$AQ$2:$AQ$85)*(!AS$2:AS$85="JA") )=_abGE2;"JA";"NEIN"))
Funktioniert grundsätzlich; jedoch muss ich noch $J anpassen, weil dies ja nur Pruef1 betrifft; im Beispiel gilt bei Pruef2 jetzt $M (J + 3 Spalten).
Da bastle ich gerade.
Aber im Prinzip habe ich das Gefühl, das wird funktionieren!
mfg
AW: ist so nicht nachvollziehbar ...
23.03.2022 10:07:23
neopa
Hallo Erich,
... dass Du derartige Probleme mit dem Kopieren und Einfügen hast. Tritt dieses Problem bei Dir auch auf, wenn Du Excel neu öffnest und in einer neuen Datei mit eine paar Daten und einer beliebigen Formel so verfährst? Kann ich mir nicht wirklich vorstellen.
Gruß Werner
.. , - ...
AW: ist so nicht nachvollziehbar ...
23.03.2022 10:54:08
erichm
Ich werde das noch weiter testen; verstehe das auch noch nicht.
AW: noch zur "Zeile 4" ...
23.03.2022 10:16:56
neopa
Hallo Erich,
... Deine Aussage dazu kann ich auch nicht nachvollziehen. Wenn in einen Datenwert der Spalte A der Text mit KK_KK55_KDKL_ beginnen könnte, sollte dies dann in Spalte D auch sso ermittelt und müsste dann in der Vergleichstabelle auch so aufgenommen und diesem dann in _DatAnz in der Spalte "Anz" auch eine 11 zugewiesen werden.
Oder was meinst Du wirklich?
Gruß Werner
.. , - ...
AW: noch zur "Zeile 4" ...
23.03.2022 11:01:47
erichm
Hallo Werner,
die Einschätzung zu Spalte A und D stimmt.
Für die Tabelle DatAnz ist es dann aber wichtig, dass jeweils zwei weitere Spalten hinzukommen:
KK_ // KK55_ // KDKL_ /// 3 // 5 // 3
Die Berechnung muss eben nicht als Summe mit 11; sondern jeweils getrennt mit
3 für KK_
5 für KK55_
3 für KDKL_
erfolgen.
Deswegen meine Überlegung, _Pruef 3x anzuwenden und das Ergebnis dann als endgültiges Ergebnis herzunehmen.
Also wenn z.B.
Pruef1 = JA
Pruef2 = JA
Pruef3 = JA
dann: endgültig JA
oder wenn
Pruef1 = JA
Pruef2 = NEIN
Pruef3 = JA
dann: endgültig NEIN
Allerdings hätte ich dann auch die Option beim Beispiel 2 ein JA als endgültiges Ergebnis zu werten; also wenn 2 von 3 mit JA ermittelt werden. Und das wäre eben noch eine sehr gute Weiterentwicklung.
Danke.
mfg
AW: hier nun wäre Deine neue Beispieldatei ...
23.03.2022 11:11:56
neopa
Hallo Erich,
... hilfreich. Dies mit ein paar Datenzeilen und dem IST in Spalte D und Datenwerten in Spalte J: V und drei Ergebniswerten in AK:AM
Gruß Werner
.. , - ...
AW: hier nun wäre Deine neue Beispieldatei ...
23.03.2022 16:21:48
erichm
Hallo Werner,
stimmt, da ist eine Musterdatei sinnvoll.
Habe jetzt entsprechend die Spalten D, J bis V und AK bis AM ausgefüllt. Die Ergebnisse habe ich aber hilfsweise über XVERWEIS bzw. manuell ermittet und in den Spalten AK bis AM per Formel dann eingetragen.
Ich hoffe, die farblichen Markierungen machen die Zuordnungen verständlicher.
Bin mir jetzt nicht mehr sicher, ob mein Weg mit Pruef1 bis Pruef3 zielführend ist (bin zumindest noch nicht weitergekommen).
https://www.herber.de/bbs/user/151993.xlsx
Danke.
mfg
AW: Deine farbliche Kennzeichnung ...
23.03.2022 19:17:42
neopa
Hallo Erich,
... in der von Dir vorgenommenen Form war unnötig (hat mich eher verwirrt).
Für die nun erweiterte Auswertung ist es "lediglich" notwendig, den SVERWEIS()-Formelteil innerhalb von_Pruef auszuwechseln.
Du kannst das mit LET() vornehmen. ich habe eine weitere benannte Formel (_Pr_Anz) definiert.
Zuvor aber hab ich Deine Daten in GD1:GE11 wieder in eine "intelligente" Tabelle formatiert und diese wieder_DatAnz benannt. Dann folgende benannte Formel definiert:
_Pr_Anz =SUMME(ZÄHLENWENN(!$D8;"*"&INDEX(_DatAnz;;1)&"*")*INDEX(_DatAnz;;2))
und dann die vorhandene benannte Formel _Pruef entsprechend angepasst zu:
_Pruef: =WENN(!$A2="";"";WENN(SUMME(ZÄHLENWENN(!$J2:INDEX(!$J2:$AJ2;_Pr_Anz);!$AQ$2:$AQ$81)*(!AS$2:AS$81="JA"))=_Pr_Anz;"JA";"NEIN"))
Alle anderen von Dir definierten benannten Formeln kannst Du löschen, wie sie sind nicht notwendig sind.
Nun ergibt in AK2 =_Pruef nach rechts und unten kopiert, das von Dir vorgegebene Ergebnis.
Gruß Werner
.. , - ...
AW: so gar nicht notwendig, geht noch einfacher...
24.03.2022 10:20:24
neopa
Hallo Erich,
... siehe meinen untenstehenden heutigen Beitrag von 10:18.
Gruß Werner
.. , - ...
AW: bitteschön, gerne getan owT
22.03.2022 09:07:38
neopa
Gruß Werner
.. , - ...
Ein bisschen Charles Williams lesen schadet nicht
20.03.2022 11:19:44
lupo1
http://www.decisionmodels.com/optspeedd.htm
Defined Names
Defined Names are one of Excel’s most powerful features, but they do take some additional calculation time. Using Names that refer to other worksheets adds an additional level of complexity to the calculation process. Also try to avoid nested names (names referring to other names). Because Names are calculated every time a formula that refers to them is calculated you should avoid putting calculation-intensive formulae or functions in defined names. It can be significantly faster in these cases to put your calculation-intensive formula or function in a spare cell somewhere and refer to that cell instead, either directly or via a name.

Du bist also definitiv auf dem falschen Weg! zumindest, wenn Du viele Berechnungen hast, und natürlich nur dann, wenn diese Leitsätze auch noch 20 Jahre später so gelten (denn so alt ist die Seite von ihm schon).
AW: ist sogar nur mit einer Formel möglich ...
24.03.2022 10:18:41
neopa
Hallo Erich,
... und damit auch einfacher, als noch gestern Abend in meinem Beitrag aufgestellt. Die dortige Lösung hatte ich lediglich an die in meinem Beitrag vom 21.03. 22 08:32:10 für Deine neuen Datenwerte in Spalte D angepasst.
Es bedarf aber weder der Vergleichstabelle _DatAnz noch Deiner separat ermittelten Datenwerte in Spalte D. Also beides jedenfalls nicht für die hier von Dir angestrebte Auswertung. Hierfür völlig ausreichend ist die jeweilige Anzahl der auszuwertenden Datenwerte in J:AJ zu ermitteln.
Damit würde dann die von Dir angestrebten Ergebniswerte mit folgender Formel ermittelt und zwar in AK2:
=WENN($A2="";"";WENN(SUMMENPRODUKT(ZÄHLENWENN($J2:INDEX($J2:$AJ2;ZÄHLENWENN(J2:AJ2;"?*"));$AQ$2:$AQ$81)*(AS$2:AS$81="JA"))=ZÄHLENWENN(J2:AJ2;"?*");"JA";"NEIN"))
wobei Du in Deiner Version darin SUMMENPRODUKT() noch durch SUMME() ersetzen kannst und damit auch alle Deine benannten Formeln überflüssig sind.
Wobei ich die Formel zuvor als benannte Formel definiert und diese dann in den Zellen eingesetzt hätte. Diese dann nach rechts und unten ziehend kopieren.
Gruß Werner
.. , - ...
das wird ja immer interessanter!!
24.03.2022 11:52:12
erichm
Hallo Werner,
vielen, vielen Dank!!
Was ich noch nicht verstanden habe, aber mich brennend interessiert:
Formelteil mit INDEX bei Pr_Anz?
Wenn ich in der Spalte D drei verschiedene "Kürzel" habe, die in GD2 bis GD11 getrennt für sich auftauchen - wie wird denn das gefunden?
Ebenso bei der Formel SUMMENPRODUKT - werden bei "?*" Zellinhalte mit gleichen (Teil-)Inhalten gesucht / verglichen?
Sowas habe ich eigentlich öfters.
Danke für eine Antwort.
mfg
AW: nun ...
24.03.2022 14:30:56
neopa
Hallo Erich,
... zu Deinen drei Fragen:
1. mit INDEX(_DatAnz;;2),wird lediglich für die Auswertung die 2. Spalte der intelligenten Tabelle _DatAnz fixiert, damit die Formel auch stets diese nutzt, da die Formel ja nach rechts kopiert wird. Anstelle von INDEX(_DatAnz;;2) kannst Du aber die Fixierung auch wie folgt realisieren: _DatAnz[[Anz]:[Anz]]
2.. die verschiedenen Werte in D2 werden mit (ZÄHLENWENN($D2;"*"&INDEX(_DatAnz;;1)&"*") gefunden und als Matrix ausgegeben, welche dann noch mit der Wertematrix multipliziert wird und diese summiert ergibt die Anzahl der auszuwertenden Datenwerte in J:AJ
3.) mit "?*" werden in der Formel lediglich Textdatenwerte gefunden, die mindestens ein Zeichen lang sind. Dies ist so im Unterschied zu 0 dann notwendig, wenn als Datenwerte auch ="" vorhanden sein können..
Gruß Werner
.. , - ...
DANKE - gut erklärt!! mfg owT
24.03.2022 20:49:02
erichm
...
AW: bitteschön, freut mich owT
25.03.2022 08:17:25
neopa
Gruß Werner
.. , - ...

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige