Live-Forum - Die aktuellen Beiträge
Datum
Titel
17.05.2024 11:03:10
Ganze Zahl in Ganz-Zahl Kombinationen aufteilen (ohne Rest)
17.05.2024 10:40:53
Tabellen (formatiert) über VBA auswählen
17.05.2024 10:13:05
Excel Tabelle - Ort/Stadt nur einmal pro Kunde zählen?
17.05.2024 09:40:47
Warum geht das Fenster aktualisieren auf
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1952to1956
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
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1952to1956
1952to1956
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Anzahl

Anzahl
23.11.2023 20:18:49
Stefe
Hallo ihr Wissenden,

auf Blatt Anzahl befindet sich ein Listobject (Tab_Test).
Es soll die Anzahl aus der Spalte Nr. ohne Duplikate mit folgenden Bedingungen
als Ergebnis angezeigt werden:

Wenn Spalte Ort = "6" enthält, Spalte Bereich "Test" NICHT enthält

Mit der eingesetzten Formel kann ich die Anzahl ohne duplikate ermitteln (Wert nur
einmal Zählen in Spalte Nr., Wenn Spalte Ort "6" enthält), schaffe es aber nicht das
Ausschluss Kriterium "Test" einzubinden.

Mich würde die Formel und VBA Lösung dazu interessieren, THX schonmal

Bsp.Datei: https://www.herber.de/bbs/user/164549.xlsx

MFG Stefe

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

Betreff
Datum
Anwender
Anzeige
AW: Anzahl ohne Duplikate mit spez. Zusatzbedingungen ...
24.11.2023 09:06:03
neopa C
Hallo Stefe,

... so: =SUMMENPRODUKT((VERGLEICH(Tab_Test[Ort]&Tab_Test[Nr.];Tab_Test[Ort]&Tab_Test[Nr.];0)=ZEILE(Tab_Test)-ZEILE(Tab_Test[#Kopfzeilen]))*(Tab_Test[Ort]=6)*ISTFEHL(SUCHEN("Test";Tab_Test[Bereich])))

Die Datentabelle kann erweitert sowie auch beliebig verschoben werden.

Gruß Werner
.. , - ...
AW: Anzahl ohne Duplikate mit spez. Zusatzbedingungen ...
26.11.2023 15:16:11
Stefe
Hey Werner,

Danke erstmal.
In der Neuen Bsp. Datei habe ich deine Formel mal getestet.
Da ist mir aufgefallen das es nicht immer Funktioniert.

Schau es dir Bitte mal an.

Bsp. Datei mit deiner Formel:
https://www.herber.de/bbs/user/164584.xlsx

MFG Stefe
Anzeige
AW: dann noch spezifischer ...
26.11.2023 15:52:18
neopa C
Hallo Stefe,

... mit folgender dafür erweiterter Formel mit Matrixformelabschluss:

{=SUMME((VERGLEICH(Tab_Test[Ort]&Tab_Test[Nr.]&Tab_Test[Bereich]&ISTFEHL(SUCHEN("Test";Tab_Test[Bereich]));Tab_Test[Ort]&Tab_Test[Nr.]&Tab_Test[Bereich]&ISTFEHL(SUCHEN("Test";Tab_Test[Bereich]));0)=ZEILE(Tab_Test)-ZEILE(Tab_Test[#Kopfzeilen]))*(Tab_Test[Ort]=6)*ISTFEHL(SUCHEN("Test";Tab_Test[Bereich])))}

Die geschweiften Klammern {} nicht eingegeben sondern Formeleingabe mit der Tastenkombination Strg+Shift + Enter, statt Enter alleine abschließen.

Gruß Werner
.. , - ...
Anzeige
AW: dann noch spezifischer ...
28.11.2023 19:19:54
Stefe
Hey Werner,

schönen Dank, Funktioniert einwandfrei !
Das nenn ich mal eine Formel.

Habe mal Probiert deine beiden Formeln mit einem weiterem Ausschluss Kriterium "Tag" zu erweitern.
Bei deiner ersten Formel habe ich Array1 in 2 kopiert.
Ob das die richtige oder einfachste Lösung war, weis ich natürlich nicht aber es Funktioniert.
Bei deiner zweiten Formel ging das leider nicht so.

Wenn möglich schau Bitte nochmal drüber
Bsp. Datei

https://www.herber.de/bbs/user/164637.xlsx

MFG Stefe
Anzeige
AW: dann aber zuvor nachgefragt ...
28.11.2023 19:47:34
neopa C
Hallo Stefe,

... können noch mehr Ausschlusskriterientexte dazukommen? Wenn sich deren Anzahl auch noch ändern kann sollte für alle diese Texte eine eigene "intelligente" abelle definiert werden, z.B. genannt: "Tab_ASKT"

Gruß Werner
.. , - ...
AW: dann aber zuvor nachgefragt ...
28.11.2023 20:16:33
Stefe
Könnte auch noch ein drittes dazukommen, man weis ja nie.

Es sollte eigentlich alles in eine Tabelle.
Frage mich bei manchen Formeln ob es nicht durch VBA einfacher zu gestalten geht.

MFG Stefe
AW: dynamische Anzahl an Ausschlußtexten berücksichtigen ...
28.11.2023 20:13:30
neopa C
Hallo Stefe,

... dies dann z.B. wie folgt.
Wobei ich hier bewußt die komplette Formel so stehen lassen habe. Persönlich würde ich diese im Anwendungsfall durch eine mehrstufig definierte benannte Formel ersetzen.

Arbeitsblatt mit dem Namen 'Anzahl'
 BCDEFGHIJK
3          
4OrtBereichNr.Stand Ergebnis   Aus_Texte
56A test10W 4   Test
66A test10W     Tag
76A sonst10W     Woche
86A test10W      
96A Tag20W      
106A Woche20W      
116A Tag20W      
126A30W      
136A30W      
146A40W      
156A40W      
166A50W      
177A50W      
18          

ZelleFormel
G5{=SUMME((VERGLEICH(Tab_Test[Ort]&Tab_Test[Nr.]&Tab_Test[Bereich]&(MMULT(WENNFEHLER(SUCHEN(MTRANS(Tab_ASKT[Aus_Texte]);Tab_Test[Bereich]);0);ZEILE(Tab_ASKT[Aus_Texte])^0)=0);Tab_Test[Ort]&Tab_Test[Nr.]&Tab_Test[Bereich]&(MMULT(WENNFEHLER(SUCHEN(MTRANS(Tab_ASKT[Aus_Texte]);Tab_Test[Bereich]);0);ZEILE(Tab_ASKT[Aus_Texte])^0)=0);0)=ZEILE(Tab_Test)-ZEILE(Tab_Test[#Kopfzeilen]))*(Tab_Test[Ort]=6)*(MMULT(WENNFEHLER(SUCHEN(MTRANS(Tab_ASKT[Aus_Texte]);Tab_Test[Bereich]);0);ZEILE(Tab_ASKT[Aus_Texte])^0)=0))}
Achtung, Matrixformel enthalten!
Die geschweiften Klammern{} werden nicht eingegeben.
Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.
Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg


Die Datenwerte in K4:K7 sind als Tabelle mit dem Namen wie der Text in K4 definiert.

Gruß Werner
.. , - ...



Anzeige
AW: dynamische Anzahl an Ausschlußtexten berücksichtigen ...
28.11.2023 20:33:32
Stefe
Besten Dank Werner werde mir das mal zu Gemüte führen.

MFG Stefe
AW: bitteschön, es geht auch einfacher, ...
29.11.2023 12:45:44
neopa C
Hallo Stefe,

... aber möglicherweise wohl nur dann, wenn Du eine aktueller XL-Version Dir zulegst. Ich hab zwar momentan auch nur XL2010 und XL2016 im Einsatz. Aber ich habe auch Zugriff auf ein Tool, mit dem einige der neueren XL-Funktion "geklont" werden, wie u.a. z.B. EINDEUTIG() und FILTERN() welche in Deinem Fall sehr hilfreich sein sollten.

Gruß Werner
.. , - ...

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige