Live-Forum - Die aktuellen Beiträge
Datum
Titel
25.04.2024 08:41:49
VBA: Probleme mit pdftk zum Zusammenfügen
25.04.2024 00:42:03
Listbox mit gefilterten Daten füllen
24.04.2024 20:38:58
Summe mit Bedingung, nur letzte Ergebnisse anzeigen
24.04.2024 19:29:30
Formel filter
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1616to1620
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
1616to1620
1616to1620
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Formel

Formel
05.04.2018 19:54:32
Erwin
Hallo,
ich suche eine Formel, die soll die Anzahl ohne Duplikate berechnen, wenn in spalte 2 und 3 bestimmte Kriterien erfüllt sind.
z.B. wenn ich in der Spalte 'Monat' die '5' eingebe, und in Spalte 'Jahr' die '2016' , dann soll in Spalte 'Name' die Anzahl berechnet werden, aber ohne die Duplikate.
In diesem Fall müsste die Zahl 7 kommen
Datum Monat Jahr Name
03.04.2016 4 2016 Name8
03.04.2016 4 2016 Name14
03.04.2016 4 2016 Name15
03.04.2016 4 2016 Name22
03.04.2016 4 2016 Name23
03.04.2016 4 2016 Name24
01.05.2016 5 2016 Name2
01.05.2016 5 2016 Name14
08.05.2016 5 2016 Name8
08.05.2016 5 2016 Name14
08.05.2016 5 2016 Name23
08.05.2016 5 2016 Name24
08.05.2016 5 2016 Name27
22.05.2016 5 2016 Name2
22.05.2016 5 2016 Name8
22.05.2016 5 2016 Name14
22.05.2016 5 2016 Name18
29.05.2016 5 2016 Name14
29.05.2016 5 2016 Name27
05.06.2016 6 2016 Name2
05.06.2016 6 2016 Name3
05.06.2016 6 2016 Name14
24.07.2016 7 2016 Name2
24.07.2016 7 2016 Name8
24.07.2016 7 2016 Name14
31.07.2016 7 2016 Name2
31.07.2016 7 2016 Name14
31.07.2016 7 2016 Name15
31.07.2016 7 2016 Name17
31.07.2016 7 2016 Name20
31.07.2016 7 2016 Name27
07.08.2016 8 2016 Name2
07.08.2016 8 2016 Name8
07.08.2016 8 2016 Name14
07.08.2016 8 2016 Name21
07.08.2016 8 2016 Name23
07.08.2016 8 2016 Name24
14.08.2016 8 2016 Name2
14.08.2016 8 2016 Name14
14.08.2016 8 2016 Name27
15.08.2016 8 2016 Name2
15.08.2016 8 2016 Name8
15.08.2016 8 2016 Name11
15.08.2016 8 2016 Name12
15.08.2016 8 2016 Name13
15.08.2016 8 2016 Name14
15.08.2016 8 2016 Name15
15.08.2016 8 2016 Name18
15.08.2016 8 2016 Name21
15.08.2016 8 2016 Name23
15.08.2016 8 2016 Name24
28.08.2016 8 2016 Name2
28.08.2016 8 2016 Name8
28.08.2016 8 2016 Name23
28.08.2016 8 2016 Name24
04.09.2016 9 2016 Name2
04.09.2016 9 2016 Name8
04.09.2016 9 2016 Name9
04.09.2016 9 2016 Name14
04.09.2016 9 2016 Name15
25.09.2016 9 2016 Name2
25.09.2016 9 2016 Name8
25.09.2016 9 2016 Name14
25.09.2016 9 2016 Name15
25.09.2016 9 2016 Name18
25.09.2016 9 2016 Name23
16.10.2016 10 2016 Name8
16.10.2016 10 2016 Name11
16.10.2016 10 2016 Name14
16.10.2016 10 2016 Name15
16.10.2016 10 2016 Name23
16.10.2016 10 2016 Name27
02.04.2017 4 2017 Name14
02.04.2017 4 2017 Name15
02.04.2017 4 2017 Name21
02.04.2017 4 2017 Name27
09.04.2017 4 2017 Name2
09.04.2017 4 2017 Name8
09.04.2017 4 2017 Name14
09.04.2017 4 2017 Name15
09.04.2017 4 2017 Name27
23.04.2017 4 2017 Name2
23.04.2017 4 2017 Name27
30.04.2017 4 2017 Name2
30.04.2017 4 2017 Name8
30.04.2017 4 2017 Name14
30.04.2017 4 2017 Name15
30.04.2017 4 2017 Name27
14.05.2017 5 2017 Name10
14.05.2017 5 2017 Name11
14.05.2017 5 2017 Name21
14.05.2017 5 2017 Name27
21.05.2017 5 2017 Name2
21.05.2017 5 2017 Name14
21.05.2017 5 2017 Name23
21.05.2017 5 2017 Name24
21.05.2017 5 2017 Name27
28.05.2017 5 2017 Name2
28.05.2017 5 2017 Name8
28.05.2017 5 2017 Name11
28.05.2017 5 2017 Name14
28.05.2017 5 2017 Name15
28.05.2017 5 2017 Name23
28.05.2017 5 2017 Name24
05.06.2017 6 2017 Name5
05.06.2017 6 2017 Name14
11.06.2017 6 2017 Name2
11.06.2017 6 2017 Name8
11.06.2017 6 2017 Name21
11.06.2017 6 2017 Name27
15.06.2017 6 2017 Name1
15.06.2017 6 2017 Name2
15.06.2017 6 2017 Name5
15.06.2017 6 2017 Name7
15.06.2017 6 2017 Name8
15.06.2017 6 2017 Name9
15.06.2017 6 2017 Name14
15.06.2017 6 2017 Name15
15.06.2017 6 2017 Name16
15.06.2017 6 2017 Name21
15.06.2017 6 2017 Name25
15.06.2017 6 2017 Name27
15.06.2017 6 2017 Name28
18.06.2017 6 2017 Name8
18.06.2017 6 2017 Name14
09.07.2017 7 2017 Name14
16.07.2017 7 2017 Name21
16.07.2017 7 2017 Name25
16.07.2017 7 2017 Name26
16.07.2017 7 2017 Name27
23.07.2017 7 2017 Name8
23.07.2017 7 2017 Name14
30.07.2017 7 2017 Name14
15.08.2017 8 2017 Name1
15.08.2017 8 2017 Name4
15.08.2017 8 2017 Name6
15.08.2017 8 2017 Name8
15.08.2017 8 2017 Name9
15.08.2017 8 2017 Name14
15.08.2017 8 2017 Name15
15.08.2017 8 2017 Name19
15.08.2017 8 2017 Name25
15.08.2017 8 2017 Name29
15.10.2017 10 2017 Name2
15.10.2017 10 2017 Name3
15.10.2017 10 2017 Name8
15.10.2017 10 2017 Name25
25.03.2018 3 2018 Name2
25.03.2018 3 2018 Name27

20
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Deine Vorgabe stimmt jedoch nur dann ...
05.04.2018 20:21:22
...
Hallo Erwin,
... wenn Du die Datumsangaben in Spalte A nicht berücksichtigen musst/willst. Soll das so sein?
Wenn in E1=5 und E2=2016 geschrieben dann folgende Formel:
=SUMME(INDEX((VERGLEICH(B2:B999&C2:C999&D2:D999;B2:B999&C2:C999&D2:D999;0)=ZEILE(A2:A999)-1)*(B2:B999&C2:C999=E1&E2);))
Du solltest allerdings Deine sinnvollerweise in eine "intelligente" Tabelle überführen, damit Du den auszuwertenden Bereich in der Formel nicht zu groß definieren musst und trotzdem immer evtl. Datensatzergänzungen automatisch berücksichtigen kannst. Dann wird die Formelschreibweise zwar etwas ändern, ist aber zu empfehlen.
Gruß Werner
.. , - ...
Anzeige
Kritik und Alternativen
06.04.2018 04:29:53
Luc:-?
Morrn, Werner;
wir hatten wohl beide dieselbe Grundidee was VERGLEICH betrifft, nur trennen sich dann unsere Wege … ;-)
Meine Kritik bezieht sich iW auf 2 Pktt:
1. Deine Fml ist recht kompliziert und für Erwin bei seinem Level sicher schwer durchschaubar, zumal Du hier …
2. unnötigerweise eine weitere Fkt, nämlich INDEX verwendest, nur um auf die MatrixFml-Form* verzichten zu können! Allerdings hättest Du das hier auch wie gewohnt ohne INDEX, dafür mit SUMMENPRODUKT statt SUMME erreichen können. Beide Fmln hätten auch die gleiche Länge!
* Mir leuchtet auch absolut nicht ein, warum ein von der Xl-Steuerung bereits mitgebrach­ter Mechanismus unbedingt durch etwas Anderes, Kompli­zie­renderes ersetzt wdn soll, nur um die simple Mitteilung an Xl, worum es sich handelt, zu vermeiden.
Beim angegebenen Xl-Level ist allerdings zu vermuten, dass Erwin unbekannt ist, wie Monat und Jahr aus einem Datum zu ermit­teln sind. Dadurch könnten diese beiden Spalten entfallen und in der Fml bspw durch TEXT(A2:A150;"MJJJJ") ersetzt wdn. Allerdings lässt sich Xl dann weder mit zusätzl INDEX noch SUMMENPRODUKT austricksen, denn TEXT würde unbedingt die MxFmlForm erfor­dern!
Meine klassische Lösung (ohne UDFs) würde HÄUFIGKEIT benutzen und wäre dann eine singulare MxFml (die Eingaben wdn hier in E2:F2 getätigt), die sogar etwas kürzer ist:
{=SUMME(--(HÄUFIGKEIT(WENNFEHLER(VERGLEICH(E2&F2&D2:D150;B2:B150&C2:C150&D2:D150;0);"");ZEILE(D2:D150)-1)>0))}
Ohne die (Hilfs-)Spalten B:C würde sie so lauten:
{=SUMME(--(HÄUFIGKEIT(WENNFEHLER(VERGLEICH(E2&F2&D2:D150;TEXT(A2:A150;"MJJJJ")&D2:D150;0);"");ZEILE(D2:D150)-1)>0))}
Es gibt natürlich noch etliche Lösungen auf Basis diverser UDFs, wobei die kürzeste (hier auch mein Favorit) DataSet benutzt:
{=ANZAHL2(DataSet(E2&F2&D2:D150;B2:B150&C2:C150&D2:D150;1;1))} bzw
{=ANZAHL2(DataSet(E2&F2&D2:D150;TEXT(A2:A150;"MJJJJ")&D2:D150;1;1))}
🙈 🙉 🙊 🐵 Gruß, Luc :-?
„Die Intelligenzmenge ist auf diesem Planeten eine Konstante, die Bevölkerung nimmt aber zu!“ Auch deshalb informieren mit …
Anzeige
AW: es gibt wohl immer Alternativen, allerdings...
06.04.2018 09:22:37
...
Hallo Luc,
... Deine Kritik mag aus Deiner und manch anderer Sicht berechtigt sein, aber dann musst Du Dir diese für Dein Lösungsangebot zumindest in gleicher Weise annehmen.
Normalerweise nutze ich sonst auch HÄUFIGKEIT(), aber ich wollte bewusst eine klassische Matrixformel vermeiden. Dies eben wegen dem von Erwin angegebenen Excel-Level. Denn meine bisherige Erfahrung hat mich "gelehrt", dass User mit einem derartigen Excel-Level größtenteils vor Matrixformeln zurückschrecken oder damit nichts anfangen können, selbst wenn eine scheinbar eindeutige Handlungsanweisung mitgegeben wurde. Letzteres hast Du gar nicht erst getan.
Mal abgesehen davon, dass HÄUFIGKEIT()-Formeln keinesfalls leichter zu verstehen sind, als mein Formelkonstrukt, kann aber mein Formelvorschlag kopiert und eingesetzt werden und das Ergebnis ist sofort vorhanden. Und darum geht es den meisten Fragenden doch in erster Linie, auch wenn wir das zumindest teilweise beklagen.
Mein SUMME(INDEX(...)) Konstrukt anstelle einer gebräuchlicheren SUMMENPRODUKT()-Schreibweise ist so gesehen zweitrangig, genauso wie die Länge einer Formel. Meine Formel kommt dafür übrigens gegenüber Deiner schon mit einer Funktion weniger aus mit SUMMENPRODUKT() dann mit noch einer weniger. Entscheidender ist jedoch nicht die Länge und die Anzahl der benutzten Funktionen sondern die Effektivität im Einsatz. Und da weiß ich natürlich, dass eine HÄUFIGKEIT()s-Formel die Nase voraus hat. Aber ... siehe oben.
Wenn Du die (unvermeidliche) "Kompliziertheit" meiner Formellösung kritisierst, dann musst Du Dir aber auch die Frage stellen lassen, ob die HÄUFIGKEIT()-Formel wirklich leichter zu verstehen ist und vor allem, warum Du dann Deine UDF-Lösung anführst.
Als wichtig neben der reinen Formellösung, sehe ich aber auch noch meinen Hinweis, dass sich Erwin mal mit den "intelligenten" Tabellen (@Erwin: einfach mal etwas googeln) beschäftigt. Das wird ihn künftig mE mehr Nutzen bringen können, als meine oder Deine Formellösung.
Gruß Werner
.. , - ...
Anzeige
Mein Beitrag war an Dich gerichtet, nicht an ...
07.04.2018 03:02:18
Luc:-?
…Erwin, Werner,
und die Kritik resultierte eher aus der Nichtverwendung von SUMMENPRODUKT, dafür aber von zusätzlichem INDEX, das nur - so interessant diese Möglichkeit auch ist - in speziellen Fällen die MatrixFml-Form vermeiden hilft.
Alles, was nach der Fußnote kommt, soll nur meinen eigenen Ansatz zeigen, den ich trotz 1-2 Fktt mehr für naheliegend halte (über Fml-Längen müssen wir in der Tat nicht diskutieren, obwohl ich damit angefangen hatte… ;-]). Eine Fkt wird ja ebenfalls vom Fml-Text-Interpreter aufgerufen und bedeutet ggf zusätzlichen Rechenaufwand, wobei kaum verifiziert wdn kann, was nun schneller bzw performanter ist, weshalb ich meine klassische Lösung oben auch mit naheliegend bezeichnet habe. Aber das ist natürlich sub­jektiv! ;-)
Ich hatte auch mehrere Fml-Varianten mit diversen UDFs entwickelt, habe dann hier aber nur die kürzeste gezeigt, die etwas erlaubt, was xl-klassisch nur mit ZellBereichen möglich ist (die 3 Operatoren : ;). So etwas mache ich gelegentlich für mögliche UDF-Interessenten, um (weitere) Anwendungsbspp zu zeigen. Letztlich macht das ja auch jeder Fml-Crack, der spezielle Anwendungs­mög­lich­keiten von Standard-Fktt vorstellt (wie auch Du hier mit INDEX… ;-]).
Dein Tab-Hinweis war weder kritikwürdig noch wesentlich zu ergänzen, weshalb ich darauf auch nicht einge­gangen bin.
Morrn + schöWE, Luc :-?
Anzeige
AW: wenn dem so ist, ...
07.04.2018 08:43:08
...
Hallo Luc,
... stellt es sich natürlich Dein gestriger Beitrag auch etwas anders dar.
Deine UDFs werden aber mE trotzdem weiterhin nur für "Poweruser" von größeren Interesse sein, die wissen warum und mit was sowie wie und für wen sie eine Lösung realisieren. Die "normalen" Nutzer (und das ist in de Regel der überwiegende der Fragesteller in Foren) werden sich wohl weiterhin auf den "ausgeschilderten" Wegen bewegen.
Gruß Werner
.. , - ...
Eben, deshalb auch nur f.Erstere, Werner! ;-) owT
07.04.2018 17:50:52
Luc:-?
:-?
AW: Kritik und Alternativen
06.04.2018 22:04:56
Luschi
Hallo Luc,
habe mir die UDF 'DataSet' mal näher angesehen und sie enthält schon eine Menge an Funktionalität.
Aber ich würde nicht auf die Idee kommen
- erst alles in ein Array zu packen
- die brauchbaren Zeilen rauszufiltern
- als UDF-Funktionswert ein Array zurückzugeben
- um dann per Excelfunktion die Anzahl der enthaltenen Zeilen im Array zu berechnen
- es gibt eine Stelle in der UDF, da ist die Anzahl der Unikatwerte bereits bekannt,
- da muß man doch nicht noch ein Array mit den Werten aufbauen und als Funktionswert zurückgeben
- wenn einem zum Schluß nur die blanke Anzahl interessiert
Mir jedenfalls hilt das Gesamtkonstrukt der UDF, da ich viele Einzelkomonenten davon nutzen kann.
Doch solche Allround-UDF sind in Hinblick auf das Verstehen und der eventuellen Änderungswünsche einfach zu monströs und nur mit großem Kraftaufwand wartbar.
Auch das allgemeingültige 'On Error Resume Next' ist nicht sehr vertrauenserweckend, zumal immer ein Zahlenwert zurückgegeben wird, auch wenn die Parameterübergabe falsch ist;
zB: F1&F2&D2:E150 statt F1&F2&D2:D150 und so das Ergebnis 1 iat.
Gruß von Luschi
aus klein-Paris
PS: Unter diesen kann ich mir nicht vorstellen, daß sich Erwin für diese UDF entscheiden wird.
Anzeige
So war das auch nicht gemeint, ...
07.04.2018 04:35:12
Luc:-?
…Luschi,
sondern sollte nur zeigen, dass es auch andere Möglichkeiten geben kann (vgl meine AW an Werner!). Letztlich zeigt Deine Reak­tion ja, dass es Interessenten für so etwas gibt (an die ich auch des Öfteren denke)… ;-)
Vor ca 15 Jahren hatte ich eine UDF geschrieben, die Listentexte aus Bereichsinhalten erzeugte, also Aufzählungen irgendwelcher Begriffe, die dann in nur einer Zelle ausgegeben wurden (heute benutze ich dafür idR VJoin!). Das brachte mich auf die Idee, diese als Mengen zu betrachten und auf dieser Basis aus 2en solcher Texte einen neuen als Differenz- bzw Schnittmenge der beiden Auf­zählungen zu erzeugen (deren erste Versionen hatte ich in den Jahren 2005/6 hier publiziert).
Vor ca 2 Jahren hatte ich dieses Thema wieder aufgegriffen und dieses Mal auf ZellBereiche (Spalte oder Zeile) und (Ko-)Vektoren (aus anderweitigen Operationen und MatrixKonstanten) bezogen. Damit habe ich eine Angebotslücke geschlossen, denn Xl erlaubt Mengen-Operationen nur für Bereichs­Objekte und nur als Gesamt- [zB A1:C9], Vereinigungs- [zB (A1:B4;C2:D5)] oder Schnitt­menge [zB (A1:B5 B2:C4) → B2:B4] (Operatoren :; ). Mit DataSet lassen sich alle relevanten MengenOperationen 2er Vektoren, die auch diskontinuierlich sein können, ausführen (einfache und beidseitige Differenzmenge, Vereinigungs-, Gesamt- und Schnitt­menge), wobei ggf auch Dopplungen in den beiden Argument-Vektoren u/o im Ergebnisvektor unterdrückt wdn können. Außerdem kann bei bestimmten MengenOperationen zwischen Ausgabe als Vektor oder Matrix gewählt wdn. Des weiteren kann zwischen verschiedenen Kennzeichnungen einer sich ggf ergebenden leeren Menge (bzw leerer Elemente) gewählt wdn.
Die UDF soll immer durchlaufen wdn und ggf einen F-Wert zurückgeben, weshalb ich auch auf eine spezielle F-Behandlung ver­zich­tet und On Error Resume Next verwendet habe.
Ich schreibe UDFs weder als Hilfsfktt für Subprozeduren (obwohl sie sich auch dort verwenden lassen) noch (idR) für ganz spezielle Auf­gaben, sondern in der Intention von Xl, also als Ergänzungen seines Fkts­Angebots. Sie stellen folglich vollständige Pgmm zur Lösung bestimmter, aber allgemein gehaltener AufgabenSpektren dar, wie man es auch von Xl-StandardFktt gewohnt ist. DataSet hat also vorrangig die Aufgabe, MengenOperationen durchzuführen. Wenn nun jemand nur die Anzahl der Elemente einer Menge benötigt, muss er sie halt zählen, hier also die der Ergebnismenge. Das entspricht quasi dem Xl zugrunde liegenden Fkts­Bau­kas­ten-Prinzip, bei dem man ja auch nicht die Ergebnis­Ermitt­lung der einzelnen Fktt nach Bedarf manipulieren kann. Das muss man dann per Ein­bindung in Fmln machen.
(Teile) meine(r) UDFs als Anregung für eigene Lösungen zu nehmen, wäre iO, nicht aber ihre Veränderung (dürfte in etlichen Fällen ohnehin schwierig wdn → soviel zur Wartung! ;-]) und ggf unter gleichem Namen In-Umlauf-Bringung. Die Mitteilung evtl Fehler bzw Unge­reimt­heiten ist natürlich ebenfalls OK und sogar erwünscht.
Gruß + schöWE, Luc :-?
Anzeige
AW: Deine Vorgabe stimmt jedoch nur dann ...
06.04.2018 14:55:05
Erwin
Hallo Werner,
danke für Deine Bemühungen.
Ich habe die Formel in mein Programm kopiert, es kommt aber die Zahl 8 als Ergebnis, es sind aber tatsächlich nur 7 eindeutige Namen (e1=5, e2=2016), wo liegt der Fehler in der Formel?
(das Datum soll in den Berechnungen ignoriert werden)
was meinst Du mit intelligente Tabelle, ich bin da nicht sehr bewandert
Gruß Erwin
AW: in Deiner hier eingestellten Datei ...
06.04.2018 15:06:22
...
Hallo Erwin,
... ergibt sich als Ergebnis mit allen bisher eingestellten Formeln ein 7. Möglicherweise hast Du den Test nicht mit dieser Datei vorgenommen?
Ein intelligente Tabelle kannst Du leicht aus Deiner Datentabelle erstellen. Dazu aktiviere eine beliebige Zelle innerhalb des Datenbereichs und aktiviere das Icon "Als Tabelle formatieren", wähle ein Dir genehmes Datenformat aus und bestätige die Option "Tabelle hat Überschriften" und schon ist diese fertig. Der Nutzen dessen ist vielfältig und auch der Umgang mit dieser. Aber wie bereits geschrieben, dazu solltest Du vielleicht mal etwas googeln.
Gruß Werner
.. , - ...
Anzeige
AW: in Deiner hier eingestellten Datei ...
08.04.2018 20:08:24
Erwin
Hallo,
in Deiner Formel ist kein Fehler, der lag bei mir, in meiner Liste war eine zusätzliche 5 vorhanden, darum wurde um 1 mehr ausgegeben.
Gruß Erwin
AW: danke für diese Info, trotzdem nachgefragt ...
09.04.2018 10:24:37
...
Hallo Erwin,
... meinen Beitrag von gestern 9:50 hast Du schon gelesen?
Gruß Werner
.. , - ...
AW: Deine Vorgabe stimmt jedoch nur dann ...
07.04.2018 20:23:36
Erwin
Sorry Werner,
ich habe nur die anderen Formeln ausprobiert, die funktioneren auch nicht so, wie ich mir das vorstelle, Deine Formel funktioniert auch bei anderen Jahreszahlen, ich habe sie auch wieder eingesetzt. Nur bei der Abfrage von '5' '2016' wird die Zahl 8 ausgegeben, obwohl es tatsächlich nur 7 eindeutige Namen sind. Bei den anderen stimmen die Zahlen, warum das so ist, kann ich mir nicht erklären.
Bei der Formel von Luc wird die Zahl 7 ausgegeben.
Eine Bitte hätte ich noch bei deiner Formel: kann man Spalte B eingeben b2:b999=5 bzw. den entsprechenden Monat und bei Spalte C c2:c999=2016 bzw. das entsprechende Jahr?
Ich habe das momentan so gelöst, dass ich an Stelle e1,e2 b11 und c11 eingegeben habe (der Mai 2016 beginnt in Zeile 11)
Bei den anderen Monaten habe ich die entsprechende Zeile eingegeben, die Berechnungen stimmen jedenfalls.
Gruß und schönen Sontag
Erwin
Anzeige
AW: hierzu ...
08.04.2018 09:50:23
...
Hallo Erwin,
... Deine Angaben kann ich so nicht nachvollziehen, denn wie bereits geschrieben, erhalte ich für Deine eingestellten Beispieldatei mit beiden Formeln (mit der von Luc und mit meiner) die gleichen Ergebnisse. Dazu vergleiche auch unten stehende Aufstellung in I1:J1.
Wenn Du mit meiner Formel als Ergebnis eine 8 erhältst, dann z.B. deswegen, dass Du in Deiner Datei unterhalb Zeile 150 noch einmal die entsprechende Datenkonstellation hast? Das kannst Du auch leicht testen, in dem Du in meiner Formel die 999 durch eine 150 und in der Formel von Luc die 150 durch 999 ersetzt. Dazu sieh unten I2:J2.
Der Sinn Deiner zusätzlichen Bitte erschließt sich mir momentan noch nicht wirklich. Zumal Deine Angabe, dass der Mai 2016 in Zeile 11 beginnt, für Deine Beispieldatei unzutreffend ist. Denn dort beginnt dieser bereits in Zeile 8.
In G4:J18 hab ich mal eine Interpretation Deiner zusätzlichen Bitte vorgenommen. Die Formeln der Zeile 6 sind einfach nach unten und die aus I6:J6 auch 1 Zeile nach oben ziehend zu kopieren. Formel H6 zuvor aus Kopie der Formel G6 erzeugen.
 ABCDEFGHIJ
1DatumMonatJahrName  5201677
203.04.201642016Name8    77
303.04.201642016Name14      
403.04.201642016Name15  MonatJahrErgebnis
503.04.201642016Name22  4201666
603.04.201642016Name23  5201677
703.04.201642016Name24  6201633
801.05.201652016Name2  7201677
901.05.201652016Name14  820161212
1008.05.201652016Name8  9201677
1108.05.201652016Name14  10201666
1208.05.201652016Name23  4201766
1308.05.201652016Name24  520171010
1408.05.201652016Name27  620171313
1522.05.201652016Name2  7201766
1622.05.201652016Name8  820171010
1722.05.201652016Name14  10201744
1822.05.201652016Name18  3201822
1929.05.201652016Name14      
2029.05.201652016Name27      
2105.06.201662016Name2      
2205.06.201662016Name3      
2305.06.201662016Name14      
2424.07.201672016Name2      
2524.07.201672016Name8      
2624.07.201672016Name14      
2731.07.201672016Name2      
2831.07.201672016Name14      
2931.07.201672016Name15      
3031.07.201672016Name17      
3131.07.201672016Name20      
3231.07.201672016Name27      
3307.08.201682016Name2      

Formeln der Tabelle
ZelleFormel
I1=SUMME(INDEX((VERGLEICH(B2:B999&C2:C999&D2:D999;B2:B999&C2:C999&D2:D999;0)=ZEILE(A2:A999)-1)*(B2:B999&C2:C999=G1&H1); ))
J1{=SUMME(--(HÄUFIGKEIT(WENNFEHLER(VERGLEICH(G1&H1&D2:D150;B2:B150&C2:C150&D2:D150;0); ""); ZEILE(D2:D150)-1)>0))}
I2=SUMME(INDEX((VERGLEICH(B2:B150&C2:C150&D2:D150;B2:B150&C2:C150&D2:D150;0)=ZEILE(A2:A150)-1)*(B2:B150&C2:C150=G1&H1); ))
J2{=SUMME(--(HÄUFIGKEIT(WENNFEHLER(VERGLEICH(G1&H1&D2:D999;B2:B999&C2:C999&D2:D999;0); ""); ZEILE(D2:D999)-1)>0))}
G5=B2
H5=C2
G6=WENNFEHLER(INDEX(B:B;AGGREGAT(15;6;ZEILE($B$2:$B$150)/($C$2:$C$150+$B$2:$B$150%>$H5+$G5%); 1)); "")
H6=WENNFEHLER(INDEX(C:C;AGGREGAT(15;6;ZEILE($B$2:$B$150)/($C$2:$C$150+$B$2:$B$150%>$H5+$G5%); 1)); "")
I6=SUMME(INDEX((VERGLEICH(B$2:B$150&C$2:C$150&D$2:D$150;B$2:B$150&C$2:C$150&D$2:D$150;0)=ZEILE(A$2:A$150)-1)*($B$2:B$150&C$2:C$150=G6&H6); ))
J6{=SUMME(--(HÄUFIGKEIT(WENNFEHLER(VERGLEICH(G6&H6&D$2:D$150;B$2:B$150&C$2:C$150&D$2:D$150;0); ""); ZEILE(D$2:D$150)-1)>0))}
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
AW: Formel
05.04.2018 20:23:11
SF
Hola,
also ich komme auf 6:
=SUMMENPRODUKT((VERGLEICH(B2:B150&D2:D150;B2:B150&D2:D150;0)=ZEILE(X2:X150)-1)*(B2:B150=4) *(C2:C150=2016)*(D2:D150<>""))
Gruß,
steve1da
AW: Monatsvorgabe war 5 nicht 4 owT
05.04.2018 20:25:41
...
Gruß Werner
.. , - ...
Stimmt....der Tag war lang :) owT
05.04.2018 20:28:55
SF
AW: Formel
07.04.2018 09:37:22
Erwin
Hallo steve1da,
habe Deine Formel ausprobiert, die funktioniert aber nur mit dem Kriterium '2016'.
Wenn ich die '2017' einsetze, wird nur die Hälfte der Anzahl ausgegeben.
Wie soll die Formel lauten, wenn ich verschiedene Jahreszahlen auswerten möchte?
Gruß
Erwin
AW: warum hast Du gewechselt? ...
07.04.2018 15:55:24
...
Hallo Erwin,
... die Formel von SF war und ist nicht korrekt. Entweder nimm meinen Formelvorschlag oder die Matrixformel von Luc.
Gruß Werner
.. , - ...
Formel
11.04.2018 19:32:37
Erwin
Hallo,
ich suchte eine Formel, die sollte die Anzahl ohne Duplikate berechnen, wenn in spalte 2 und 3 bestimmte Kriterien erfüllt sind.
z.B. wenn ich in der Spalte 'Monat' die '5' eingebe, und in Spalte 'Jahr' die '2016' , dann soll in Spalte 'Name' die Anzahl berechnet werden, aber ohne die Duplikate.
Mit den Formeln, die ich bekommen habe, bin ich einigermaßen zurecht gekommen, auch wenn ich die den Inhalt nicht ganz verstehe (mit meinem Basiswissen wohl verständlich).
Ich möchte aber noch weitere Werte ermitteln, z.B. möchte ich die km- Zeit- Schnitt- hm- nach Monat und Jahr berechnen, aber ohne Duplikate in den jeweiligen Spalten.
Zum Beispiel möchte ich den Wert ermitteln, wieviel hm im '5' '2016' gefahren wurden. In diesem Fall wurden von den Teilnehmern 3420 hm gefahren, die habe ich mit einer Formel berechnen können, aber der einzelne Fahrer, hat nur 945 hm erreicht, dazu habe ich noch keine Formel gefunden. Ich möchte auch die min-, max-, mittelwerte ermitteln.
Analog möchte ich das auch mit den Spalten km- Zeit- Schnitt- erreichen
Datum Monat Jahr Name km Zeit Schnitt hm
03.04.2016 4 2016 Name8 35 01:36:00 21,88 280
03.04.2016 4 2016 Name14 35 01:36:00 21,88 280
03.04.2016 4 2016 Name15 35 01:36:00 21,88 280
03.04.2016 4 2016 Name22 35 01:36:00 21,88 280
03.04.2016 4 2016 Name23 35 01:36:00 21,88 280
03.04.2016 4 2016 Name24 35 01:36:00 21,88 280
01.05.2016 5 2016 Name2 38 01:45:00 21,71 200
01.05.2016 5 2016 Name14 38 01:45:00 21,71 200
08.05.2016 5 2016 Name8 46 02:00:00 23,00 280
08.05.2016 5 2016 Name14 46 02:00:00 23,00 280
08.05.2016 5 2016 Name23 46 02:00:00 23,00 280
08.05.2016 5 2016 Name24 46 02:00:00 23,00 280
08.05.2016 5 2016 Name27 46 02:00:00 23,00 280
22.05.2016 5 2016 Name2 44 02:00:00 22,00 265
22.05.2016 5 2016 Name8 44 02:00:00 22,00 265
22.05.2016 5 2016 Name14 44 02:00:00 22,00 265
22.05.2016 5 2016 Name18 44 02:00:00 22,00 265
29.05.2016 5 2016 Name14 45 02:05:00 21,60 280
29.05.2016 5 2016 Name27 45 02:05:00 21,60 280
05.06.2016 6 2016 Name2 34 01:35:00 21,47 230
05.06.2016 6 2016 Name3 34 01:35:00 21,47 230
05.06.2016 6 2016 Name14 34 01:35:00 21,47 230
24.07.2016 7 2016 Name2 42 01:54:00 22,11 210
24.07.2016 7 2016 Name8 42 01:54:00 22,11 210
24.07.2016 7 2016 Name14 42 01:54:00 22,11 210
31.07.2016 7 2016 Name2 37 02:10:00 17,08 320
31.07.2016 7 2016 Name14 37 02:10:00 17,08 320
31.07.2016 7 2016 Name15 37 02:10:00 17,08 320
31.07.2016 7 2016 Name17 37 02:10:00 17,08 320
31.07.2016 7 2016 Name20 37 02:10:00 17,08 320
31.07.2016 7 2016 Name27 37 02:10:00 17,08 320
07.08.2016 8 2016 Name2 36 01:48:00 20,00 230
07.08.2016 8 2016 Name8 36 01:48:00 20,00 230
07.08.2016 8 2016 Name14 36 01:48:00 20,00 230
07.08.2016 8 2016 Name21 36 01:48:00 20,00 230
07.08.2016 8 2016 Name23 36 01:48:00 20,00 230
07.08.2016 8 2016 Name24 36 01:48:00 20,00 230
14.08.2016 8 2016 Name2 46 02:08:00 21,56 250
14.08.2016 8 2016 Name14 46 02:08:00 21,56 250
14.08.2016 8 2016 Name27 46 02:08:00 21,56 250
15.08.2016 8 2016 Name2 69 03:40:00 18,82 400
15.08.2016 8 2016 Name8 69 03:40:00 18,82 400
15.08.2016 8 2016 Name11 69 03:40:00 18,82 400
15.08.2016 8 2016 Name12 69 03:40:00 18,82 400
15.08.2016 8 2016 Name13 69 03:40:00 18,82 400
15.08.2016 8 2016 Name14 69 03:40:00 18,82 400
15.08.2016 8 2016 Name15 69 03:40:00 18,82 400
15.08.2016 8 2016 Name18 69 03:40:00 18,82 400
15.08.2016 8 2016 Name21 69 03:40:00 18,82 400
15.08.2016 8 2016 Name23 69 03:40:00 18,82 400
15.08.2016 8 2016 Name24 69 03:40:00 18,82 400
28.08.2016 8 2016 Name2 40 02:05:00 19,20 240
28.08.2016 8 2016 Name8 40 02:05:00 19,20 240
28.08.2016 8 2016 Name23 40 02:05:00 19,20 240
28.08.2016 8 2016 Name24 40 02:05:00 19,20 240
04.09.2016 9 2016 Name2 58 03:00:00 19,33 570
04.09.2016 9 2016 Name8 58 03:00:00 19,33 570
04.09.2016 9 2016 Name9 58 03:00:00 19,33 570
04.09.2016 9 2016 Name14 58 03:00:00 19,33 570
04.09.2016 9 2016 Name15 58 03:00:00 19,33 570
25.09.2016 9 2016 Name2 46 02:18:00 20,00 200
25.09.2016 9 2016 Name8 46 02:18:00 20,00 200
25.09.2016 9 2016 Name14 46 02:18:00 20,00 200
25.09.2016 9 2016 Name15 46 02:18:00 20,00 200
25.09.2016 9 2016 Name18 46 02:18:00 20,00 200
25.09.2016 9 2016 Name23 46 02:18:00 20,00 200
16.10.2016 10 2016 Name8 32 01:31:00 21,10 180
16.10.2016 10 2016 Name11 32 01:31:00 21,10 180
16.10.2016 10 2016 Name14 32 01:31:00 21,10 180
16.10.2016 10 2016 Name15 32 01:31:00 21,10 180
16.10.2016 10 2016 Name23 32 01:31:00 21,10 180
16.10.2016 10 2016 Name27 32 01:31:00 21,10 180
02.04.2017 4 2017 Name14 37 01:42:00 21,76 320
02.04.2017 4 2017 Name15 37 01:42:00 21,76 320
02.04.2017 4 2017 Name21 37 01:42:00 21,76 320
02.04.2017 4 2017 Name27 37 01:42:00 21,76 320
09.04.2017 4 2017 Name2 42 02:01:00 20,83 220
09.04.2017 4 2017 Name8 42 02:01:00 20,83 220
09.04.2017 4 2017 Name14 42 02:01:00 20,83 220
09.04.2017 4 2017 Name15 42 02:01:00 20,83 220
09.04.2017 4 2017 Name27 42 02:01:00 20,83 220
23.04.2017 4 2017 Name2 43 02:10:00 19,85 200
23.04.2017 4 2017 Name27 43 02:10:00 19,85 200
30.04.2017 4 2017 Name2 43 02:03:00 20,98 230
30.04.2017 4 2017 Name8 43 02:03:00 20,98 230
30.04.2017 4 2017 Name14 43 02:03:00 20,98 230
30.04.2017 4 2017 Name15 43 02:03:00 20,98 230
30.04.2017 4 2017 Name27 43 02:03:00 20,98 230
14.05.2017 5 2017 Name10 43 01:58:00 21,86 335
14.05.2017 5 2017 Name11 43 01:58:00 21,86 335
14.05.2017 5 2017 Name21 43 01:58:00 21,86 335
14.05.2017 5 2017 Name27 43 01:58:00 21,86 335
21.05.2017 5 2017 Name2 40 02:03:00 19,51 320
21.05.2017 5 2017 Name14 40 02:03:00 19,51 320
21.05.2017 5 2017 Name23 40 02:03:00 19,51 320
21.05.2017 5 2017 Name24 40 02:03:00 19,51 320
21.05.2017 5 2017 Name27 40 02:03:00 19,51 320
28.05.2017 5 2017 Name2 37 01:53:00 19,65 240
28.05.2017 5 2017 Name8 37 01:53:00 19,65 240
28.05.2017 5 2017 Name11 37 01:53:00 19,65 240
28.05.2017 5 2017 Name14 37 01:53:00 19,65 240
28.05.2017 5 2017 Name15 37 01:53:00 19,65 240
28.05.2017 5 2017 Name23 37 01:53:00 19,65 240
28.05.2017 5 2017 Name24 37 01:53:00 19,65 240
05.06.2017 6 2017 Name5 49 02:20:00 21,00 360
05.06.2017 6 2017 Name14 49 02:20:00 21,00 360
11.06.2017 6 2017 Name2 45 02:03:00 21,95 400
11.06.2017 6 2017 Name8 45 02:03:00 21,95 400
11.06.2017 6 2017 Name21 45 02:03:00 21,95 400
11.06.2017 6 2017 Name27 45 02:03:00 21,95 400
15.06.2017 6 2017 Name1 69 04:00:00 17,25 600
15.06.2017 6 2017 Name2 69 04:00:00 17,25 600
15.06.2017 6 2017 Name5 69 04:00:00 17,25 600
15.06.2017 6 2017 Name7 69 04:00:00 17,25 600
15.06.2017 6 2017 Name8 69 04:00:00 17,25 600
15.06.2017 6 2017 Name9 69 04:00:00 17,25 600
15.06.2017 6 2017 Name14 69 04:00:00 17,25 600
15.06.2017 6 2017 Name15 69 04:00:00 17,25 600
15.06.2017 6 2017 Name16 69 04:00:00 17,25 600
15.06.2017 6 2017 Name21 69 04:00:00 17,25 600
15.06.2017 6 2017 Name25 69 04:00:00 17,25 600
15.06.2017 6 2017 Name27 69 04:00:00 17,25 600
15.06.2017 6 2017 Name28 69 04:00:00 17,25 600
18.06.2017 6 2017 Name8 42 01:53:00 22,30 440
18.06.2017 6 2017 Name14 42 01:53:00 22,30 440
09.07.2017 7 2017 Name14 33 01:23:00 23,86 400
16.07.2017 7 2017 Name21 40 02:02:00 19,67 330
16.07.2017 7 2017 Name25 40 02:02:00 19,67 330
16.07.2017 7 2017 Name26 40 02:02:00 19,67 330
16.07.2017 7 2017 Name27 40 02:02:00 19,67 330
23.07.2017 7 2017 Name8 46 01:55:00 24,00 360
23.07.2017 7 2017 Name14 46 01:55:00 24,00 360
30.07.2017 7 2017 Name14 54 02:05:00 25,92 360
15.08.2017 8 2017 Name1 75 04:05:00 18,37 500
15.08.2017 8 2017 Name4 75 04:05:00 18,37 500
15.08.2017 8 2017 Name6 75 04:05:00 18,37 500
15.08.2017 8 2017 Name8 75 04:05:00 18,37 500
15.08.2017 8 2017 Name9 75 04:05:00 18,37 500
15.08.2017 8 2017 Name14 75 04:05:00 18,37 500
15.08.2017 8 2017 Name15 75 04:05:00 18,37 500
15.08.2017 8 2017 Name19 75 04:05:00 18,37 500
15.08.2017 8 2017 Name25 75 04:05:00 18,37 500
15.08.2017 8 2017 Name29 75 04:05:00 18,37 500
15.10.2017 10 2017 Name2 38 02:06:00 18,10 350
15.10.2017 10 2017 Name3 38 02:06:00 18,10 350
15.10.2017 10 2017 Name8 38 02:06:00 18,10 350
15.10.2017 10 2017 Name25 38 02:06:00 18,10 350
25.03.2018 3 2018 Name2 43 01:58:00 21,86 300
25.03.2018 3 2018 Name27 43 01:58:00 21,86 300
08.04.2018 4 2018 Name29 36 01:56:00 18,62 400
08.04.2018 4 2018 Name2 36 01:56:00 18,62 400
08.04.2018 4 2018 Name3 36 01:56:00 18,62 400
08.04.2018 4 2018 Name8 36 01:56:00 18,62 400
08.04.2018 4 2018 Name25 36 01:56:00 18,62 400
08.04.2018 4 2018 Name2 36 01:56:00 18,62 400
08.04.2018 4 2018 Name27 36 01:56:00 18,62 400
Vielleicht kann mir da jemand weiterhelfen
Gruß
Erwin

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige