Microsoft Excel

Herbers Excel/VBA-Archiv

Doppelte Datensätze


Betrifft: Doppelte Datensätze von: Ingo
Geschrieben am: 26.09.2019 10:38:04

Hallo,
ich habe folgende Formel:

=SUMMENPRODUKT(--('Tab1'!$G$6:$G$1145=$D$32)+--('Tab1'!$G$6:$G$1145=$D$33) +--('Tab1'!$G$6:$G$1145=$D$34)+--('Tab1'!$G$6:$G$1145=$E$32)+--('Tab1'!$G$6:$G$1145=$E$33) +--('Tab1'!$G$6:$G$1145=$E$34)+--('Tab1'!$G$6:$G$1145=$E$35);--('Tab1'!$AS$6:$AS$1145="D") +--('Tab1'!$AS$6:$AS$1145="E")+--('Tab1'!$AS$6:$AS$1145="P")+--('Tab1'!$AS$6:$AS$1145="BmS") +--('Tab1'!$AS$6:$AS$1145="BoS");--('Tab1'!$AT$6:$AT$1145<=$C$2))

Zur Erklärung:
Hier soll gezählt werden wenn:
- in Tab1 Spalte G bestimmte Buchstabenkombinationen enthalten sind
(diese befinden sich in Tab2 in den Zellen D32, D33, D34, E32, E33, E34, E35)
- aber auch nur wenn in Tab1 Spalte AS ebenfalls nur bestimmte Buchstaben enthalten sind
- dieses aber auch wiederum nur wenn in Tab1 Spalte AT das Datum kleiner/gleich C2 ist
Nun mein Problem:
Nun müssen noch folgende Datensätze abgezogen werden:
1. - wenn in Tab1 Spalte AD das Datum kleiner/gleich C2 ist
und
2. Mehrfachdatensätze von einer Person sollen nur 1x gezählt werden
(Referenzspalte wäre hier die Spalte S der Tab1

Ich hoffe, dass ich mich nicht zu kompliziert ausgedrückt habe!?
Vielen Dank
ingo

  

Betrifft: AW: Doppelte Datensätze von: MCO
Geschrieben am: 26.09.2019 13:12:29

Hallo Ingo!

Wer soll denn da durchblicken?

TIP, Du kannst die Summenproduktparameter zusammenfassen

Syntax:
--(H7:H27=1)+--(H7:H27=2) entspricht --(H7:H27={1.2})

Also ist

SUMMENPRODUKT(--('Tab1'!$G$6:$G$1145=$D$32)+--('Tab1'!$G$6:$G$1145=$D$33) +--('Tab1'!$G$6:$G$ _
1145=$D$34)+--('Tab1'!$G$6:$G$1145=$E$32)+--('Tab1'!$G$6:$G$1145=$E$33) +--('Tab1'!$G$6:$G$1145=$E$34)+--('Tab1'!$G$6:$G$1145=$E$35);--('Tab1'!$AS$6:$AS$1145="D") +--('Tab1'!$AS$6:$AS$1145="E")+--('Tab1'!$AS$6:$AS$1145="P")+--('Tab1'!$AS$6:$AS$1145="BmS") +--('Tab1'!$AS$6:$AS$1145="BoS");--('Tab1'!$AT$6:$AT$1145<=$C$2))
gleich (hier schon mit der Änderung der Datumsabfrage von AT auf AD (letzter Term)
--('Tab1'!$G$6:$G$1145={$D$32.$D$33.$D$34.$E$32.$E$33.$E$34.$E$35});--('Tab1'!$AS$6:$AS$1145="D"."E"."P"."BmS"."BoS");--('Tab1'!$AD$6:$AD$1145<=$C$2))
Ich habs mangels Tabelle nicht getestet, müsste aber so passen

Gruß, MCO


  

Betrifft: AW: Doppelte Datensätze von: Ingo
Geschrieben am: 26.09.2019 14:03:34

Hallo MCO,
vielen Dank schon einmal vorab für Deiner Mühe.

Die Datumsabfrage AT soll bleiben.
Es soll nur zusätzlich die Datumsabfrage AD abgezogen werden.

Frage zu "--(H7:H27=1)+--(H7:H27=2) entspricht --(H7:H27={1.2})":
H steht in meinem Fall für die Spalte S der Tab1 ?
Wenn ja, müsste ich entweder --($S$6:$S$1145=1)+--($S$6:$S$1145=2)
oder --($S$6:$S$1145={1.2}) hinten anhängen und die Mehrfachdatensätze werden nur 1x gezählt; oder?
ingo


  

Betrifft: AW: Doppelte Datensätze von: MCO
Geschrieben am: 26.09.2019 14:19:55

Hallo!
Ja, ist richtig.

Summenprodukt multipliziert die "Richtig"-Bedingungen für jede Spalte. Demnach mußt die Bedingung für AT und AD angefügt haben.

--('Tab1'!$G$6:$G$1145={$D$32.$D$33.$D$34.$E$32.$E$33.$E$34.$E$35});--('Tab1'!$AS$6:$AS$1145="D"."E"."P"."BmS"."BoS");--('Tab1'!$AD$6:$AD$1145<=$C$2);--('Tab1'!$AT$6:$AT$1145<=$C$2))
Gruß, MCO


  

Betrifft: AW: Doppelte Datensätze von: Ingo
Geschrieben am: 26.09.2019 14:32:46

Hallo MCO,
das ja gilt auch für:
Frage zu "--(H7:H27=1)+--(H7:H27=2) entspricht --(H7:H27={1.2})":
H steht in meinem Fall für die Spalte S der Tab1 ?
Wenn ja, müsste ich entweder --($S$6:$S$1145=1)+--($S$6:$S$1145=2)
oder --($S$6:$S$1145={1.2}) hinten anhängen und die Mehrfachdatensätze werden nur 1x gezählt; oder?

ingo


  

Betrifft: AW: Doppelte Datensätze von: Ingo
Geschrieben am: 26.09.2019 16:54:09

Sorry, hatten den Haken vergessen zu setzen.
Hallo MCO,
das ja gilt auch für:
Frage zu "--(H7:H27=1)+--(H7:H27=2) entspricht --(H7:H27={1.2})":
H steht in meinem Fall für die Spalte S der Tab1 ?
Wenn ja, müsste ich entweder --($S$6:$S$1145=1)+--($S$6:$S$1145=2)
oder --($S$6:$S$1145={1.2}) hinten anhängen und die Mehrfachdatensätze werden nur 1x gezählt; oder?
ingo


  

Betrifft: AW: Doppelte Datensätze von: Ingo
Geschrieben am: 26.09.2019 17:25:42

Hallo MCO,
ich habe es gerade ausprobiert.
Entweder es funktioniert nicht oder ich habe die Formel falsch eingegeben.
Ich habe es mit folgendem Beispiel versucht:
M4 = ABC
M5 = DEF
M6 = ABC
M7 = ABC
M8 = ABC

Q4 = abc
123

Q5 = def
456

Q6 = hij
789

Q7 = abc
123

Q8 = abc
123

Formel: =SUMMENPRODUKT(--($M$4:$M$8="ABC")+--($M$4:$M$8="DEF");--(Q4:Q8=1)+--(Q4:Q8=2))
(ABC u. DEF sind 5 mal enthalten; aber abc 123 sind 3 mal enthalten, sollte aber nur 1 mal gezählt werden)
Ergebnis müsste 3 sein; die Formel gibt mir allerding eine 0.

Was habe ich falsch gemacht ???

ingo


  

Betrifft: AW:(D)eine Beispieldatei könnte hilfreich sein owT von: neopa C
Geschrieben am: 27.09.2019 15:18:09

Gruß Werner
.. , - ...


  

Betrifft: AW: AW:(D)eine Beispieldatei könnte hilfreich sein owT von: Ingo
Geschrieben am: 27.09.2019 16:59:55

Hallo Werner,
Beispieldatei:
https://www.herber.de/bbs/user/132250.xlsx


  

Betrifft: AW: für Deine eingestellte Beispieldatei ... von: neopa C
Geschrieben am: 28.09.2019 08:20:25

Hallo Ingo,

... wäre die von Dir gesuchte Formel z.B. so erforderlich:

=SUMMENPRODUKT((VERGLEICH(C1:C9;C1:C9;0)=ZEILE(C1:C9))*((A1:A9="ABC")+(A1:A9="DEF")))


Allerdings sollte Deine Quelldaten als Tabelle formatiert werden (so Du zumindest Excel 2007 im Einsatz hast) und die Formel dafür angepasst werden. Dies hätte dann den Vorteil, dass bei Datenerweiterung sich die Formel dem automatisch anpasst.

Gruß Werner
.. , - ...


  

Betrifft: AW: für Deine eingestellte Beispieldatei ... von: Ingo
Geschrieben am: 28.09.2019 11:51:51

Hallo Werner,
vielen Dank, so funktioniert sie perfekt !!!


  

Betrifft: AW: bitteschön owT von: neopa C
Geschrieben am: 28.09.2019 18:15:05

Gruß Werner
.. , - ...


  

Betrifft: AW: bitteschön owT von: Ingo
Geschrieben am: 29.09.2019 17:41:19

Hallo Werner,
ich musste gerade feststellen, dass es nun doch nicht so ganz funktioniert.
Und es ist mein Fehler.
Sobald in der Spalte F meiner Beispieldatei eine Zelle leer ist,
gibt mir die Formel #NV zurück.
Und das ist mein Fehler, das hätte ich erwähnen müssen, das nicht alle Zellen der Spalte F
befüllt sein müssen.
Sorry !!!
Kannst Du mir weiterhelfen ???
Vielen Dank im voraus !!!
ingo


  

Betrifft: AW: dann mit einer zusätzliche Bedingung ... von: neopa C
Geschrieben am: 29.09.2019 18:11:49

Hallo Ingo,

... so:
=SUMMENPRODUKT((VERGLEICH(C1:C99&"";C1:C99&"";0)=ZEILE(C1:C99))*((A1:A99="ABC")+(A1:A99="DEF")) *(C1:C99<>""))

Gruß Werner
.. , - ...


  

Betrifft: AW: dann mit einer zusätzliche Bedingung ... von: Ingo
Geschrieben am: 30.09.2019 17:57:13

Hallo Werner,
Formel funktioniert; aber...
siehe neue Beispieldatei
https://www.herber.de/bbs/user/132298.xlsx
ingo


  

Betrifft: AW: in Deiner neuen Beispieldatei ... von: neopa C
Geschrieben am: 30.09.2019 21:32:05

Hallo Ingo,

... ist zunächst so einiges zu korrigieren. Deine Datenwerten in B:C und I sind zu einem großen Teil Teil keine solche sondern Textwerte.
Nach einer entsprechenden Korrektur dessen, muss auch die Formelanpassung an die zusätzliche Bedingung angepasst werden.

Die Formel würde dann z.B. so lauten:

=SUMMENPRODUKT((VERGLEICH(F1:F11&(B1:B11<=I1)*(C1:C11>I2);F1:F11&(B1:B11<=I1)*(C1:C11>I2);0)=ZEILE(C1:C11))
*((A1:A11="ABC")+(A1:A11="DEF"))*(B1:B11<=I1)*(C1:C11>I2))


Gruß Werner
.. , - ...


  

Betrifft: AW: in Deiner neuen Beispieldatei ... von: Ingo
Geschrieben am: 01.10.2019 15:48:39

Hallo Werner,
super dass funktioniert !!!
Tausend Dank !!!

ingo


  

Betrifft: AW: bitteschön owT von: neopa C
Geschrieben am: 01.10.2019 17:49:45

Gruß Werner
.. , - ...


Beiträge aus dem Excel-Forum zum Thema "Doppelte Datensätze"