Live-Forum - Die aktuellen Beiträge
Datum
Titel
23.04.2024 08:04:28
4-fach verschachtelte WENN-Funktion
22.04.2024 22:36:13
Marko automatisch ausführen
22.04.2024 21:15:10
PQ - Wenn der Jahrgang fehlt, dann...
22.04.2024 16:32:43
Excelformel für Arbeitszeitberechnung 08-16:30 Uhr
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1440to1444
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
1440to1444
1440to1444
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Array- oder AGGREGAT-Formel?

23
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: sowohl als auch ...
17.08.2015 19:30:51
...
Hallo Herbert,
... die Formel in H2 ist eine reine AGGREGAT()-Formel und noch relativ einfach nachzuvollziehen.
Die Formel in I2 ist da schon von anderem Kaliber (Matrixformel der Teil-AGGREGAT()-Formeln). Die Formel könnte evtl. auch noch Optimierungsmöglichkeiten besitzen.
Wie viele Datensätze hast Du denn max. auszuwerten?
Ich geh jetzt allerdings gleich erst mal wieder offline, ich schau dann Morgen noch einmal hiernach.
Tabelle1

 ABCDEFGHI
1NameDatum1Datum2Datum3Datum4Datum5 Verschiedene NameTage je Name
2Name102.03.201506.03.201508.03.201512.03.201516.03.2015 Name18
3Name102.03.201506.03.201508.03.201513.03.201516.03.2015 Name27
4Name102.03.2015 09.03.2015 17.03.2015 Name35
5       Name44
6Name202.03.201506.03.201508.03.201512.03.201516.03.2015   
7Name203.03.2015 08.03.2015 17.03.2015   
8         
9Name302.03.201506.03.201508.03.201512.03.201516.03.2015   
10Name302.03.2015 08.03.201512.03.201516.03.2015   
11         
12Name402.03.2015 08.03.201512.03.201516.03.2015   
13         

Formeln der Tabelle
ZelleFormel
H2=WENNFEHLER(INDEX(A:A;AGGREGAT(15;6;ZEILE(A$2:A13)/ISTNV(VERGLEICH(A$2:A$13;H$1:H1;)); ZEILE(A1))); "")
I2{=WENN(H2="";"";SUMME(1*(VERGLEICH(AGGREGAT(14;6;B$2:F$13/(A$2:A$13=H2); ZEILE(A$1:INDEX(A:A;SUMME(ISTZAHL(B$2:F$13)*(A$2:A$13=H2))))); AGGREGAT(14;6;B$2:F$13/(A$2:A$13=H2); ZEILE(A$1:INDEX(A:A;SUMME(ISTZAHL(B$2:F$13)*(A$2:A$13=H2))))); )=ZEILE(A$1:INDEX(A:A;SUMME(ISTZAHL(B$2:F$13)*(A$2:A$13=H2)))))))}
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
oder mit HÄUFIGKEIT
17.08.2015 19:44:21
Josef
Hallo Herbert & Werner
Ich würde das mit Hilfe der Funktion HÄUFIGKEIT berechnen
{=WENN(H2="";"";ANZAHL(1/ HÄUFIGKEIT(WENN((A$2:A$99=H2)*(B$2:F$99"");B$2:F$99);B$2:F$99)))}
Gruss Sepp

AW: nicht oder, nur HÄUFIGKEIT() ...
17.08.2015 19:59:04
...
Hallo Sepp,
... wollte gerade noch mal nach einer anderen Lösung schauen, weil mir meine zu schwülstig und viel zu Ressourcen hungrig war. Aber da war mir wieder mal der Sepp zuvorgekommen ;-)
Natürlich ist Dein Formelvorschlag mit HÄUFIGKEIT() nicht nur die kürzere sondern vor allem die effektivste Lösung.
Gruß Werner
.. , - ...

HÄUFIGKEIT würde ich ebenfalls bevorzugen, ...
18.08.2015 01:06:22
Luc:-?
…Werner, Sepp & Herbert,
wenn ich auf XlStandard-Fktt angewiesen wäre… ;-)
Da das nicht der Fall ist, ziehe ich meine beiden Standard-UDFs für solche Fälle vor:
H2:H5: {=MTRANS(VSplit(VJoin(A2:A12;;-1)))}
I2[:I5]: {=ANZAHL(VSplit(VJoin(WENN(H2=A$2:A$12;WENN(B$2:F$12="";"";B$2:F$12);"");;-1);;1))}
Wenn sich MS endlich mal dazu durchringen könnte, (sinnvoll erweiterte) StandardFktt auf der Basis der auch hier zugrunde liegenden vbFktt Join und Split anzubieten, hättet ihr sicher kein Problem mit einer solchen Lösung…
Hinweis: VSplit Vs1.1, VJoin Vs1.4 → im Archiv oft verlinkt.
Gruß, Luc :-?
Besser informiert mit …

Anzeige
AW: HÄUFIGKEIT würde ich ebenfalls bevorzugen, ...
18.08.2015 09:22:42
Daniel
nja, viel einfacher wird die Formel durch den Einsatz deiner UDF aber nicht.
eher sogar komplizierter, weil du eine Funktion mehr einsetzen musst.
Mit UDF: {=ANZAHL(VSplit(VJoin(WENN(H2=A$2:A$12;WENN(B$2:F$12="";"";B$2:F$12);"");;-1);;1))}
ohne UDF: {=WENN(H2="";"";ANZAHL(1/HÄUFIGKEIT(WENN((A$2:A$99=H2)*(B$2:F$99"");B$2:F$99);B$2:F$99)))}
Gruß Daniel

AW: HÄUFIGKEIT würde ich ebenfalls bevorzugen, ...
18.08.2015 13:08:10
Herbert
Hallo alle zusammen,
zuallererst einmal vielen Dank für Euere Hilfe. Werner, bitte nicht böse sein, dass ich Sepp's Version präferiere, doch sie ist so schön übersichtlich! ;o)=)
@Luc: Du schreibst von 2 "UDF's" mit den Namen "VSplit" und "VJoin". Entweder schnalle ich da was nicht, oder kannst Du mir diese mal zur Verfügung stellen?
Servus

Anzeige
AW: HÄUFIGKEIT würde ich ebenfalls bevorzugen, ...
18.08.2015 13:21:47
Daniel
Hi
Luc erwartet, dass du dich selber bemühst und danach suchst.
Gruß Daniel

AW: ich schrieb ja schon ...
18.08.2015 14:32:15
...
Hallo Herbert,
... das der Formelverbesserungsvorschlag von Sepp auf Basis von HÄUFIGKEIT() die effektivste Lösung ist. Warum sollte ich Dir also gram sein, wenn Du auch diesen meinem Rat folgst, nachdem Du ja sicherlich die gewünschte AGGREGAT()- Formel für die duplettenfreie Namensermittlung übernommen hast.
Gruß Werner
.. , - ...

AW: ich schrieb ja schon ...
18.08.2015 16:04:54
Herbert
Hallo Werner,
"nu glaa", habe ich Deinen Rat befolgt, denn ich bin ja ein folgsamer Schüler! ;o)=)
Servus

duplettenfreie Namensermittlung
18.08.2015 17:28:27
Josef
Hallo Werner
Deine Formel für die Spalte H gefällt mir aber ganz und gar nicht.
Wenn die Bedingungen korrekt gesetzt sind, brauchst du in der Funktion Aggregat beim Argument K
nur eine 1 zu schreiben.
Deine Variante wird da je nach Leerstellen in Spalte A versagen.
Mein Vorschlag deshalb:
=WENNFEHLER(INDEX(A:A;AGGREGAT(15;6;ZEILE(A$2:A$13)/(A$2:A$13"")/ISTNV(VERGLEICH(A$2:A$13;H$1:H1;0) );1));"")
Gruss Sepp

Anzeige
AW: da geb ich Dir völlig Recht ...
18.08.2015 19:25:44
...
Hallo Sepp,
... da hatte ich gestern Abend abschließend leider "geschlammpt". Nach meiner Lösungsformel für die Anzahl glaubte ich fertig zu sein. Vor dem Einstellen meines Beitrages sah ich noch die Zusatzfrage von Herbert, die nur in der Tabelle seiner Datei stand. Die glaubte ich aber schnell ohne groß zu testen aufzustellen, weil ich um 19:30 die Nachrichten schauen wollte. Dabei hab ich ein $ in der Formel vergessen bzw. eins zu viel gesetzt. Dadurch stimmte scheinbar das Ergebnis. Dies galt aber nur für das Beispiel.
Da ich die richtige Formelkonstruktion (mit 1 anstelle ZEILE(A1) und der zusätzlichen Prüfung der Leerzeilen) nun schon öfters hier im Forum eingestellt hatte, ist mir der Fehler besonders ärgerlich. Ich sollte eben doch nichts unter selbst gesetzten Zeitdruck tun :-(
Spätestens nach Herberts Hilferuf von vorhin, hätte ich meinen Fehler natürlich korrigiert. So hast Du es schon getan. Danke und einen schönen Abend noch.
Gruß Werner
.. , - ...

Anzeige
Ja, die sind im Archiv vorhanden, ...
18.08.2015 16:02:36
Luc:-?
…Herbert;
beide zusammen in letzter Version in dieser Datei.
Gruß, Luc :-?

Nachtrag: MatrixFml in H ist mehrzellig ...
18.08.2015 16:07:57
Luc:-?
…(zuviel angelegte Ergebniszellen zeigen dann #NV), MxFml in I 1zellig, was durch die eckigen Klammern in der Standort­Angabe angedeutet wird.
Luc :-?

AW: Ja, die sind im Archiv vorhanden, ...
18.08.2015 16:29:20
Herbert
Hi Luc,
mercie vielmals! Werde ich mir mal vornehmen.
Servus

Bitte sehr, gern geschehen! ;-) owT
18.08.2015 23:52:45
Luc:-?
:-?

Das gilt aber auch nur für d.Fml in Spalte I, ...
18.08.2015 15:52:37
Luc:-?
…Daniel,
denn da wäre es zumindest bei geringeren Datenmengen egal. Für Nicht-UDF-Nutzer wäre also das Optimum H-Fml von Werner, I-Fml von Sepp.
Für UDF-Nutzer ist sicher meine H-Fml ein Optimum. Ob's auch die I-Fml ist, lasse ich offen, da ich auf die Schnelle und aus nachvollziehbarer Gleichartigkeit des Problems nur diese beiden UDFs eingesetzt habe, aber wohl auch andere einsetzen könnte. Das Ganze zeigt wieder 1× sehr schön den Bedarf an derartigen Xl-Fktt, auf die uns MS bis dato warten lässt (viell, weil viele Nutzer sich so oder anders selber behelfen können?)…
Gruß, Luc :-?

Anzeige
AW: sowohl als auch ...
18.08.2015 17:17:53
Herbert
Hallo Werner,
es tut mir wirklich sehr leid, aber wenn ich Deine Formel in der Original-Datei anwende, filtert sie mir die Doppelten nicht mehr raus. Woran kann das liegen?
Hier meine Formel, die in der Zelle "IV3" steht: =WENNFEHLER(INDEX(A:A;AGGREGAT(15;6;ZEILE($A$3:$A$1200)/ISTNV(VERGLEICH(A$3:A$1200;IV$1:IV1;)); ZEILE(A1))); "")
Mit "IV$2:IV2" habe ich es auch schon erfolglos versucht.
Servus

AW: dazu sieh mal ...
18.08.2015 19:31:28
...
Hallo Herbert,
... mein Beitrag von eben an Sepp. Tut mir leid. Aber meine Formellösung für die Anzahl hatte mir offensichtlich die Konzentration für diese Formel genommen. Dabei hatte ich diese in u.a. in Deinem anderen thread von voriger Woche auf jeden Fall richtig. Aber sei es drum. Sorry, wenn ich Dir unnötig Probleme gemacht habe.
Gruß Werner
.. , - ...

Anzeige
AW: Array- oder AGGREGAT-Formel?
19.08.2015 09:18:54
Herbert
Hallo alle zusammen,
vielen Dank euch allen, für Euere große Unterstützung. Doch nun ist es leider so, dass die ganze Liebesmühe umsonst war, denn die Formeln kann ich leider nicht zum Einsatz bringen, weil Sie den Rechner total in die Knie zwingen. Und das, obwohl ich erst 3 der 21 Spalten bestückt habe, die damit berechnet werden sollen. Und wie ich das sehe, gibt es auch keine Lösung dafür, denn es handelt sich um wie gesagt 21 Spalten a 1200 Zeilen. Das Problem dabei ist, dass diese 21 Spalten keinen zusammenhängenden Bereich bilden, sondern sich dazwischen immer mehrere Spalten mit anderen Zahlen befinden. Weiter kommt erschwerend hinzu, dass die Tabelle eine Struktur hat, die bis in die Spalten IU und IV reicht, in denen sich dann Euere Formeln befinden. Und natürlich sind in den dazwischenliegenden Spalten jede Menge Formelspalten, so dass der Rechner für jede Berechnung minutenlang braucht. Jedenfalls meiner. Und der gehört mit einer Intel i7-860-CPU mit Quadcore 4x2800GHz 64Bit und 8 Prozessorkernen, DDR3-1333 Turbo Boost, Hyper-Threading, 8MB L2cache, sowie 8.192GB (2 x 4.096) Kingston DDR3-1600/2000 Dual-Channel, CL 8-RAM, auf einem ASUS-BOARD P7P55D Deluxe und einem 1156er Sockel, mit einem FSB von 1600-2000MHz und einem intel P55-Chipsatz, ja nun wahrlich nicht zu den langsamsten im Lande!
Und der Laptop des Nutzers ist noch wesentlich schwächer als mein PC. Es ist also mit Formeln nicht zu lösen.
Nun bin ich am überlegen, ob ich es mit einer VBA-Lösung versuche. Mal sehen.
Aber noch einmal vielen Dank für Euere prima Hilfe!
Servus

Anzeige
AW: Array- oder AGGREGAT-Formel?
19.08.2015 09:43:26
Daniel
Hi
das Problem sollte sich mit VBA relativ einfach und auch sehr schnell lösen lassen.
bei verwendung des Dictionary-Objekts zum Zählen der Elemente reicht im Prinzip ein einziger Schleifendurchlauf über die Tabelle.
probier mal folgenden Code mit deiner Beispieldatei: Sub Berechung() Dim dic As Object Dim arr Dim z As Long Dim s As Long Dim Dat As Variant Dim Nme As String arr = Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp)).Resize(, 6).Value Set dic = CreateObject("scripting.dictionary") For z = 1 To UBound(arr, 1) Nme = arr(z, 1) If Nme "" Then For s = 2 To UBound(arr, 2) Dat = arr(z, s) If Dat "" Then _ If InStr(dic(Nme) & "|", "|" & Dat & "|") = 0 Then _ dic(Nme) = dic(Nme) & "|" & Dat Next End If Next arr = dic.keys For z = 0 To UBound(arr) Nme = arr(z) dic(Nme) = UBound(Split(dic(Nme), "|")) Next Range("H1").CurrentRegion.Offset(1, 0).ClearContents Range("H2").Resize(dic.Count) = WorksheetFunction.Transpose(dic.keys) Range("I2").Resize(dic.Count) = WorksheetFunction.Transpose(dic.items) End Sub Gruß Daniel

Anzeige
AW: Array- oder AGGREGAT-Formel?
19.08.2015 12:07:41
Herbert
Hallo Daniel,
das funzt bei meinem Beispiel super, doch leider nicht bei meinem Originalsheet, da ich dort ja immer Spalten dazwischen habe. Aber ich habe Deinen Code abgewandelt, so dass er mir wenigstens alle Namen ohne Doppelte auflistet. Dafür vielen Dank.
Nun habe ich noch 2 Fragen:
1. Wozu dient in der Codezeile ("arr = Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp)).Resize(, 6).Value") die "Resize"-Anweisung? Der Bereich bleibt ja unverändert!
2. Wozu brauche ich in der folgenden Codezeile den "|"?: "If InStr(dic(sName), "|") = 0 Then dic(sName) = dic(sName)"
Servus

AW: Array- oder AGGREGAT-Formel?
20.08.2015 18:28:02
Daniel
HI
zum Thema Resize:
in dieser Datenzeile lese ich die Daten aus der Tabelle in ein Array ein, weil ich mit dem Array dann im code schneller arbeiten kann als wenn ich die Werte jedesmal aus der Exceltabelle lesen muss.
mit dem Resize(, 6) lege ich fest, dass der einzulesende Bereich 6 Spalten breit ist (A-F).
das war in deinem Beispiel so vorgeben, du kannst hier aber auch andere Werte verwenden oder die grösse des Zellbereichs mit .CurrentRegtion oder änlichem berechenen, oder die letzte befüllte Spalten ähnlich wie die letzte befüllte Zeile mit .Cells(1, columns.count).End(xltoleft).column berechenen.
was jetzt die beste Methode ist, hängt von deinen Daten ab.
zur zweiten Frage:
das | ist hier der Datentrennerm, ähnlich wie das Semikolon in einer CSV-Datei, allerdings bevorzuge ich das |, weil es optisch prägnanter ist (hilfreich beim Testen) und in der Regel in normalen Texten nicht verwendet wird.
in dem String sammle ich ja die Datumswerte und weil du jeden Datumswert ja nur einmal zählen willst, muss ich prüfen, ob der Datumswert schon vorhanden ist oder nicht.
Dabei hilft der Datentrenner, falsche Ergebnisse zu vermeiden.
Beispiel:
der Sammestring enthält schon ABC und DEF, jetzt will ich wissen, ob der Wert CDE schon vorhanden ist.
Ohne Trenner würde die Prüfung bei "ABCDEF" natürich ergeben, dass der Wert CDE schon enthalten ist, mit Trenner "ABC|DEF" wäre das Ergebnis "nicht vorhanden"
bei Datumswerten sind solche zufälligen Treffer natürlich eher unwahrscheinlich, aber man weiss ja nie und um Fehler möglichst auszuschließen, gehe ich bei sowas lieber nach bewährten Standards vor.
Gruß Daniel

AW: Originalsheet
20.08.2015 18:31:48
Daniel
Hi
für mich ist immer die Datei und der Datenaufbau entscheidend, den du mir hier zeigst.
Welche Dateien du sonst noch so auf deinem Rechner hast, interessiert mich eher weniger, ich bin ja nicht die NSA.
Gruß Daniel

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige