Live-Forum - Die aktuellen Beiträge
Datum
Titel
18.04.2024 21:09:11
4 Spalten aus smarter Tabelle in andere smarte Tab. kopieren
18.04.2024 20:45:35
Worksheet_Activate für alle Sheets
18.04.2024 19:30:20
VBA - Tabelle mit Mannschaftsergebnissen sortieren
18.04.2024 18:04:29
Formel fehler Wert#
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1684to1688
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
1684to1688
1684to1688
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Summenproduktformel

Summenproduktformel
07.04.2019 18:14:33
Curly
Hallo,
ich zerbrech mir gerade den Kopf über "wahrscheinlich" eine Summenprodukt - formel.
Meine bisherige Formel war:
=SUMME(ZÄHLENWENN(A:A;A2&{"*!8*"});ZÄHLENWENN(A:A;LINKS(A2;15));WENN(ISTZAHL(FINDEN("!8";A2));1;0))
Allerdings würde ich diese jetzt gerne umbauen, sodas folgende Kriterien erfüllt sind und ich nach dem Ergbnis filtern könnte.
Die Artikelnummer muss 2x enthalten sein, hiervon mind. 1x mit " *!8* ", dabei darf die Artikelnummer mit "*!7* nicht berücksichtig (bzw gezählt) werden.
Ich habe mal zum besseren Verständnis eine Tabelle mit Wunschergebnis erstellt.
https://www.herber.de/bbs/user/128995.xlsx
Danke für euere Mühe
Curly

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

Betreff
Datum
Anwender
Anzeige
AW: Summenproduktformel
07.04.2019 18:26:41
Daniel
HI
naja, markier mal die Artikelnummern, die betroffen sind.
ich werde aus deiner Anfrage noch nicht so ganz schlau.
Gruß Daniel
AW: Summenproduktformel
07.04.2019 18:35:48
Curly
Hallo Daniel,
die Artikelnummern, welcher in der 2ten Spalte ein x haben, sind das Wunschergbnis.
D.h. ich möchte nur Artikel sehen, wo der Artikelnummerstamm (ersten 15 Zeichen von links) mehrmals vorkommt (davon muss mind. 1x mit *!8* , jedoch dürfen die Artikel mit *!7* nicht berücksichtigt werden.
ich hoffe, ich konnte es irgendwie verständlicher machen.
Gruß Curly
AW: Summenproduktformel
07.04.2019 20:39:59
Daniel
Hi
diese Formel in C2 und dann nach unten ziehen:
=WENN(RECHTS(A2;3)"!80";"";WENN(ZÄHLENWENNS($A$2:$A$23;LINKS(A2;15)&"*";$A$2:$A$23;"*!70")>=2; 1/ZÄHLENWENN($A$2:$A$23;A2);""))
mit der Summe über Spalte C bekommst du die Anzahl.
Gruß Daniel
Anzeige
AW: dies war doch schon anderweitig geklärt owT
08.04.2019 08:53:31
neopa
Gruß Werner
.. , - ...
AW: dies war doch schon anderweitig geklärt owT
08.04.2019 12:58:05
Daniel
andererseits:
"... es ist utopisch, dass jemand perfekt ist bzw. sein kann. Jedenfalls bin ich davon sehr, sehr weit davon entfernt. "
vielleicht solltest du auch aus diesem Grund nachgeschobene Lösungsalternativen nicht einfach so schroff ablehnen, sondern lieber schauen, ob du da nicht doch die eine oder andere Idee übernehmen könntest, um dich der Pefektion ein kleines bisschen mehr anzunähern.
Gruß Daniel
AW: herzu ist festzustellen, ...
08.04.2019 14:22:57
neopa
Hallo Daniel,
... erstens hab ich Deine nachgeschobene Lösungsalternative nicht "schroff" abgelehnt, sondern lediglich eine zu diesem Zeitpunkt zutreffende Feststellung getroffen.
Zweitens hattest Du in diesem Deinem von mir "kommentierten" Beitrag lediglich einen Lösungsansatz zum Teil 2 der ursprünglichen Fragestellung eingestellt, wofür zu diesem Zeitpunkt nicht bekannt war, dass eine Massendatenmenge auszuwerten war/ist. Und da ist eine Einzellenformel für mich immer eine geeignete(re) Lösung.
Drittens hattest Du Deinen Beitrag "für größere Datenmengen" erst 20 Sekunden vor meinen von Dir in diesem "Ast" beanstandeten Beitrag eingestellt, so dass ich diesen nach gar nicht sichten konnte.
Hinzu kommt, dass ich unter "perfekt sein" nicht das Erstellen und Anbieten von Excellösungen sehe, Du aber scheinbar schon.
Gruß Werner
.. , - ...
Anzeige
Aber warum teilst du mir das dann extra
08.04.2019 14:31:26
Daniel
noch mal mit?
Was war jetzt Sinn und Zweck einer Feststellung und was möchstest du damit erreichen?
Ich weiß jezt nicht so genau, was ich mit dieser Information zukünftig anfangen soll.
Welche Beiträge bereits vorhanden sind, kann ich ja selber lesen.
Gruß Daniel
AW: um (D)eine Fehleinschätzung zu korrigieren owT
08.04.2019 15:06:12
neopa
Gruß Werner
.. , - ...
welche Fehleinschätzung?
08.04.2019 16:33:15
Daniel
und von wem?
na (D)eine, so schwer zu verstehen?
08.04.2019 16:40:40
robert
AW: na (D)eine, so schwer zu verstehen?
08.04.2019 16:49:56
Daniel
Sorry Robert, warum dann die Klammern um das D?
Bist du zu feige für ne klare Aussage?
Du nervst nur und verstehst nichts..
08.04.2019 19:00:54
robert
..und brauchst für alles eine Erklärung.Siehe Antwort an Luc..
Aber Typen wie Dich braucht das Forum, sonst wäre es ja langweilig.........
ich kann nur das verstehen, was man mir erklärt
08.04.2019 19:07:22
Daniel
wenn du was nicht erklären kannst, wie soll ich das verstehen?
Anzeige
…Ja, ebenso wie FlachErdler, damit die Anderen …
08.04.2019 19:51:35
Luc:-?
…was zu lachen haben… ;-)
Gruß, Luc :-?
Mal Tacheles; deine Bemerkung ist unverschämt! owT
08.04.2019 18:01:08
Luc:-?
:-?
AW: Mal Tacheles;
08.04.2019 18:23:41
Daniel
Bitte begründen.
ohne Begründung liegt die Unverschämtheit bei dir.
Offensichtliches entzieht sich deinem Urteil, ...
08.04.2019 19:45:43
Luc:-?
…Daniel‽
Ausgerechnet Werner vorzuschlagen, er möge doch auch von anderen lernen, in diesem Fall wohl von dir, ist arrogante Unverschämt­heit, denn du hast ja über die Jahre auch von anderen profitiert ohne das jemals zuzugestehen. Außerdem versuchst du immer wieder, dich mit anderen AWern anzulegen. Während Werner und andere auch eine gute, nicht-eigene Leistung (öffentlich) positiv werten kön­nen, lässt dein Ego das wohl nicht zu. Stattdessen lamentierst du, wenn dir das auf den Kopf zugesagt wird.
Und den vorangegangenen Betreff wollte ich dir schon um 13:30h schreiben, hatte da nur gerade keine Zeit mehr!
Luc :-?
Anzeige
Offensichtliches entzieht sich deinem Urteil, ...
08.04.2019 21:41:02
Daniel
Luc, schließlich war es Werner, der mich angsprochen hat, und nicht ich ihn!!!!
Und wenn eine Lösung 5 min braucht und die andere dass in einer kaum spürbaren Rechenzeit schafft, dann muss man nicht lang überlegen, wer von wem noch was lernen kann.
Daniel
Ja, ja, uneinsichtig wie damals schon, ...
08.04.2019 23:00:57
Luc:-?
…quasi wie ein (religiöser) Fundamentalist (Kreationist, FlachErdler & Co). War ja auch nicht anders zu erwarten.
Entwickelst du auch fktionierende komplizierteste Fmln in weniger als 10 min (was ja vorherige ProblemErkenntnis und -Durchdringung voraussetzt) wie Werner?
Im Übrigen macht der Ton die Musik und da hast du schon des Öfteren Dissonanzen produ(/vo)ziert…
Luc :-?
Anzeige
ne Luc, so uneinsichtig wie du selbst
08.04.2019 23:09:23
Daniel
der Ton macht die Musik?
mag sein aber dann hör mal deiner eigenen Musik zu, du alter Kreationist und Flacherdler.
Mehr als Beleidungen hast du leider auch nicht dauf.
Aha, immer noch eins drauf was! Blamier' dich ...
09.04.2019 01:11:55
Luc:-?
…nur ruhig weiter! :->
Ich schrieb nämlich …wie ein… und nicht, dass du einer wärst. Aber solche Feinheiten sind halt nicht so deine Sache… LOL
Luc :-?
Ja klar Luc, natürlich versucht du dich jetzt wied
09.04.2019 01:56:22
Daniel
über diese juristischen Feinheiten rauszureden.
Ist halt typisch für dich.
Aber Fakt ist neunmal, das Werner mich zuerst angesprochen hat und nicht ich ihn.
Flatearthisches Verhalten wäre, diese offensichtliche Tatsache zu missachten.
Anzeige
Im Ggdatz zu dir überlege ich mir vorher, ...
09.04.2019 02:45:43
Luc:-?
…was ich wie schreibe. Folglich habe ich es nicht nötig, mich - auch noch ausgerechnet dir ggüber - „herausreden“ zu wollen. Wenn du hier unbedingt den nervigen Troll geben willst, bitte schön, dann sieh mal zu, wieweit du damit kommst…
Es ist letztlich egal, wer wen zuerst angesprochen hat, du hattest dich mit deiner AW wieder mal im Ton vergriffen. Bekommst du hier zu wenig Bewunderung?
Jedenfalls hast du dir bei anderen AWern so keine sonderlich positive Reputation erworben. Aber das scheint dir ja egal zu sein, was dich anscheinend mit unserem einsamen Postings-Häuptling verbinden würde…
So, und jetzt wende ich mich wieder Interessanterem zu als deinen EndlosTiraden…
Luc :-?
Anzeige
'Ggdatz' als 'Ggsatz' lesen! owT
09.04.2019 14:47:06
Luc:-?
:-?
AW: ohne SUMMENPRODUKT() ...
07.04.2019 18:50:50
neopa
Hallo Curly,
... in B2 so:
=WENN(ZÄHLENWENN(A:A;LINKS(A2;15)&"!80")*
(ZÄHLENWENNS(A:A;LINKS(A2;15)&"*";A:A;""&LINKS(A2;15)&"!70")&gt1)*(RECHTS(A2;3)"!70");"x";"")
und Formel nach unten kopieren.
Gruß Werner
.. , - ...
AW: ohne SUMMENPRODUKT() ...
07.04.2019 19:01:03
Curly
Danke Werner , einfach perfekt.
Jetzt hätte ich noch eine Frage, allerdings ist die nicht soo wichtig.
Kann man mit einer Formel ermitteln, wieviele unterschiedliche Nummern es betrifft?
Also jetzt in der Tabelle , wäre das Ergbnis "4" ?
Gruß Curly
AW: gerade untenstehend beantwortet owT
07.04.2019 19:04:47
neopa
Gruß Werner
.. , - ...
AW: und es gab ja noch einen Teil 2 ...
07.04.2019 19:04:14
neopa
Hallo Curly,
... wo die Ermittlung der Anzahl zutreffender unterschiedlicher Artikel gefragt war.
Hierzu nun mal eine klassische Matrixformel:

{=SUMME((VERGLEICH(LINKS(A1:A99;15)&B1:B99;LINKS(A1:A99;15)&B1:B99;0)=ZEILE(A1:A99))*(B1:B99="x")) }
Gruß Werner
.. , - ...
Anzeige
Einfach perfekt. Vielen Dank
07.04.2019 19:07:14
Curly
Funktioniert auch tadellos,
Werner du bist perfekt.
Vielen Dank
Curly
AW: bitteschön, jedoch ...
08.04.2019 08:51:33
neopa
Hallo Curly,
... es ist utopisch, dass jemand perfekt ist bzw. sein kann. Jedenfalls bin ich davon sehr, sehr weit davon entfernt.
Gruß Werner
.. , - ...
AW: bitteschön, jedoch ...
08.04.2019 09:08:33
Curly
Hallo Werner,
heute natürlich gleich deine Formel in die große Tabelle eingesetzt ca.30.000 Zeilen.
sie funktioniert ohne wenn und aber, allerdings dauert es ca. 5 Minuten, auch wenn ich die Berechnung vorher auf Manuell setzte , geht das alles sehr sehr langsam.
Hast du evtl. noch eine Tipp für mich wie man das beschleunigen könnte?
Gruß Curly
Anzeige
AW: nachgefragt ...
08.04.2019 09:26:33
neopa
Hallo Curly,
... für eine derartigen Datenmenge ist eine reine Formellösung keine optimale Lösung, wenn sich häufig der Datenbestand ändern sollte. Wenn es nur ein einmaliger Vorgang ist, sollte diese allerdings auch bei dieser Datenmenge noch ausreichend sein.
Allerdings stellt sich mir die Frage, wozu Du diese Datenmenge filtern willst? Oder geht es Dir doch nur um die Ermittlung der Anzahl der unterschiedlichen Artikel unter der vorgegebenen Bedingung? Dann würde ja die eine Formel in I1 ausreichend sein können.
Andernfalls wäre zu Power Query zu raten. Mehr zu Power Query findest Du z.B. auf Günthers Seite hier: http://www.excel-ist-sexy.de. Momentan komme ich wohl eher nicht dazu, dies damit für Deine Aufgabenstellung aufzubereiten.
Gruß Werner
.. , - ...
AW: nachgefragt ...
08.04.2019 09:51:21
Curly
Der Datenbestand ändert sich leider jeden Tag.
PowerQuery ist leider keine Lösung, da wir nur Excel 2013 ohne AddIns nutzen dürfen.
Die Datei stellt ein Lager mit Artikel und Alternativartikeln da, alternativ Artikel sind mit *!* gekennzeichnet und das gehört leider regelmäßig gesichtet.
Allerdings hilft mir die Formel auch schon immens weiter, und wenn es eben seine Zeit benötigt, benötigt es seine Zeit :-)
AW: in Deiner Excel-Version ...
08.04.2019 10:04:30
neopa
Hallo Curly,
... ist PowerQuery bereits Bestandteil von Excel. Die entsprechenden Funktionalitäten findest Du unter dem Menüpunkt Daten.
Gruß Werner
.. , - ...
für große Datenmengen
08.04.2019 10:04:10
Daniel
Hi Curly
schau dir mal meinen Lösungsvorschlag an.
der sollte etwas schneller sein.
wenn das immer noch nicht reicht, könntest du folgendes tun:
1. Liste nach Spalte A sortieren, so dass alle Werte für eine Artikelnummer untereinander stehen
2. in zwei Hilfsspalten den Zeilenbereich für eine Artikelnummer angeben (Zeile von, Zeile bis)
dann musst du für die Prüfung für jede Artikelnummer nicht mehr den kompletten Zellbereich mit allen 30.000 Zeilen durchgehen, sondern nur die Zeilen der jeweiligen Artikelnummer.
Schau dir mal das Beispiel an:
https://www.herber.de/bbs/user/129007.xlsx
Gruß Daniel
AW: für große Datenmengen
08.04.2019 12:43:53
Curly
Danke Daniel,
so brauche ich zwar ein 3 Spalten mehr, aber die nehme ich gerne in kauf .....
man merkt gar nicht das Excel hier etwas berechnet.
Vielen Dank euch beiden, dass ihr euch so viel Zeit für wild fremde Leute nehmt.
Gruß Curly

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige