Anzeige
Archiv - Navigation
1360to1364
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

Summenprodukt ohne Duplikate

Summenprodukt ohne Duplikate
22.05.2014 10:20:38
Ralf
Hi,
es gibt zwar schon einige Einträge im Net zu dem Problem, welche mir aber nicht wirklich helfen. Ich möchte die Anzahl meiner gefundenen Objekte anzeigen lassen.
Meine Formel: =SUMMENPRODUKT((((Materialbedarf!H:H=Berechnung!$A$4)*(Materialbedarf!B:B=Hilfstabelle!$C$21)*(Materialbedarf!F:F=Hilfstabelle!$C$22))))
funktioniert schon. Allerdings zeigt diese mir Adressen, welche in der Spalte Materialbedarf!A:A stehen (und bislang noch nicht Teil meiner Formel ist) mehrfach an.
Kann mir bitte jemand weiterhelfen, sodass die Formel alle gefundene Objekte ohne Duplikate berechnet?
Grüße,
Ralf

11
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Summenprodukt ohne Duplikate
22.05.2014 10:22:15
Ralf
Noch ein Nachtrag:
Diese Formel habe ich schon gefunden aber ich weiß nicht, wie ich sie für meine Bedrüfnisse zurechtstutzen soll.
=SUMMENPRODUKT((VERGLEICH(A1:A10&B1:B10;A1:A10&B1:B10;0)=ZEILE(1:10)*(A1:A10=1)*(B1:B10""))*1)
Grüße,
Ralf

ein kleiner Datenauszug wäre hilfreich ...
22.05.2014 10:59:38
der
Hallo Ralf,
... es können auch Dummydaten sein. Ein nachstellen ist mit unnötigen Zeitaufwand verbunden und es besteht die Gefahr der Fehlinterpretation.
Gruß Werner
.. , - ...

AW: mach ich
22.05.2014 11:19:24
Ralf
Hi,
https://www.herber.de/bbs/user/90785.xlsx
Die Formel hat sich ein wenig verändert, da ich ein paar Zellen gelöscht habe. In H3 findest du die aktualisierte Formel. Das ist jetzt nur ein Auszug meiner Liste. Meine Formel zeigt mir jetzt "15" an, also sämtliche Einträge der Adressen 1 bis 5. Ich hätte aber gern nur 5 Einträge, also Adresse 1, 2, 3, 4, 5 als Zahl. Ich hoffe, es ist verständlich.
Grüße,
Ralf

Anzeige
Anzahl ohne Duplikate ...
22.05.2014 12:57:17
der
Hallo Ralf,
... Du hast nur einen Auszug Deiner Tabelle Materialbedarf in Tabelle1 dargestellt, deshalb kann Deine Formel in H3 kein Ergebnis anzeigen.
Diese Formel nach Deinen Angaben in der Datei umgesetzt kann auch keine 15 ergeben. Sondern nur 7, wenn Du die Formel entsprechend umschreibst.
Du suchst aber bezogen auf diese Datei wahrscheinlich z.B. folgende Formel:
 H
33

Formeln der Tabelle
ZelleFormel
H3{=SUMME(WENN((B3:B19=A27)*(E3:E19=A28)*(F3:F19=A22); N(VERGLEICH(A3:A19;A3:A19;)=ZEILE(A3:A19)-2)))}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...

Anzeige
AW: Anzahl ohne Duplikate ...
22.05.2014 14:05:33
Ralf
Hi,
ist es von dir gewollt, dass der Vergleichstyp beim dem VERGLEICH freigelassen wurde? Und was hat die -2 am Ende für einen Nutzen? Nach Umformen zeigt mir die Formel leider immer 0 an.
Grüße,
Ralf

AW: Anzahl ohne Duplikate ...
22.05.2014 14:10:01
Ralf
Wieder eine kleine Korrektur...
Ich habe vergessen, es als Matrixformel auszugeben. Wenn ich das jetzt aber mache, hängt sich mein Excel auf. :D

dann sieh mal hier ...
22.05.2014 14:24:21
der
Hallo Ralf,
... Dein Beispielblatt mit Deiner und meiner Formel. Da kann sich nichts aufhängen.
Ja, der Abzug von 2 ist notwendig, weil der Auswertungsbereich erst ab Zeile 3 beginnt und damit die "Vergleichszeilen " alle bei 1 beginnen.
Sollten noch Fragen, ich bin dann für heute aber gleich ganz offline.
 ABCDEFGH
1Tabelle "Materialbedarf"  
2AdresseJahrArt-NrMengeBauartGröße 7
3Adresse120124496408,00Holz7 3
4Adresse1201245087,00Holz7  
5Adresse1201244957,00Holz7  
6Adresse2201244204,00Holz7  
7Adresse22012442476,00Holz7  
8Adresse3201245011,00Holz7  
9Adresse3201245117,00Holz7  
10Adresse3201344846,00Lehm2  
11Adresse3201345082,00Lehm2  
12Adresse3201344952,00Lehm2  
13Adresse3201346673,00Lehm2  
14Adresse4201346160,50Lehm2  
15Adresse4201345021,00Lehm2  
16Adresse4201344843,00Stahl1  
17Adresse5201345081,00Stahl1  
18        
19        
20        
21Größe       
227       
26        
272012       
28Holz       
29        

Formeln der Tabelle
ZelleFormel
H2=SUMMENPRODUKT((F:F=A22)*(B:B=A27)*(E:E=A28))
H3{=SUMME(WENN((B3:B19=A27)*(E3:E19=A28)*(F3:F19=A22); N(VERGLEICH(A3:A19;A3:A19;)=ZEILE(A3:A19)-2)))}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...

Anzeige
AW: dann sieh mal hier ...
22.05.2014 14:39:47
Ralf
Ja, bei dem Auszug klappt die Formel. Wenn ich es aber bei meiner "richtigen" Liste mache, hängt er sich auf. Scheint so, als wäre die Matrixformel vom Rechnen her zu kompliziert!? Falls ja, gibt es eventuell noch eine Alternative?
Schönen Feierabend erstmal.
Grüße,
Ralf

möglicherweise ...
23.05.2014 08:31:51
der
Hallo Ralf,
... hast Du in der Formel die beschränkt definierten Bereichsangaben auf den kompletten Spaltenbereich erweitert? Wieviele Datensätze willst Du denn so auswerten?
Gruß Werner
.. , - ...

AW: möglicherweise ...
26.05.2014 07:59:21
Ralf
Hi Werner,
ja, ich habe es auf den kompletten Spaltenbereich erweitert. Auswerten würde ich so knapp 31.000 Einträge. Ich glaube, ich werde die Funktion einfach weglassen, da es zu lange dauert aber mir auch keine Alternative dazu einfällt.
Grüße,
Ralf

Anzeige
warum so schnell aufgeben ...
26.05.2014 19:43:51
der
Hallo Ralf,
... meine Formel gilt sowieso nur für entsprechend sortierte Daten (also z.B. nach Jahr, dann nach Bauart und dann nach Größe).
Dies wissend, kann man den auszuwertenden Bereich stark eingrenzen. Mit zwei zusätzlichen Hilfszellen wird das ganze auch übersichtlich und sollte in einer vernünftigen Zeit das Gesamtergebnis ermitteln.
Hier mal an einen kleinen Datenmenge mein Prinzip-Lösungsvorschlag:
 ABCDEFGHIJKLMN
1Tabelle "Materialbedarf"       Jahr2012  erste Datensatz-Zeile 15
2AdresseJahrArt-NrMengeBauartGröße  BauartHolz  letzte Datensatz-Zeile 22
3Adresse3201145117Holz7  Größe7    
4Adresse3201145087Holz7      Ergebnis4
5Adresse3201144846Lehm2        
6Adresse3201144846Lehm2        
7Adresse4201145082Lehm2        
8Adresse4201144952Lehm2        
9Adresse5201244746Chemie2        
10Adresse3201245011Chemie7        
11Adresse3201245117Chemie7        
12Adresse5201245087Chemie7        
13Adresse1201245087Holz5        
14Adresse1201244957Holz5        
15Adresse120124496408Holz7        
16Adresse1201245087Holz7        
17Adresse1201244957Holz7        
18Adresse2201244204Holz7        
19Adresse22012442476Holz7        
20Adresse3201245011Holz7        
21Adresse3201245117Holz7        
22Adresse5201245087Holz7        
23Adresse3201344846Chemie2        
24Adresse3201344846Lehm2        
25Adresse3201345082Lehm2        
26Adresse3201344952Lehm2        
27Adresse3201346673Lehm2        
28Adresse4201346160,5Lehm2        
29Adresse4201345021Lehm2        
30Adresse4201344843Stahl1        
31Adresse5201345081Stahl1        
32              

Formeln der Tabelle
ZelleFormel
N1{=VERGLEICH(J1&J2&J3;B1:B31000&E1:E31000&F1:F31000;)}
N2=VERWEIS(9;1/(J1&J2&J3=B1:B31000&E1:E31000&F1:F31000); ZEILE(A:A))
N4=SUMMENPRODUKT(N(VERGLEICH(INDEX(A:A;N1):INDEX(A:A;N2); INDEX(A:A;N1):INDEX(A:A;N2); )=ZEILE(INDEX(A:A;N1):INDEX(A:A;N2))-N1+1))
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige