Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1872to1876
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

durchschnittliche Tage

durchschnittliche Tage
10.03.2022 11:49:21
Stefan
Hallo zusammen,
gibt es eine Formel die, die durchschnittlichen Tage zwischen den unterschiedlichen Essen berechnet.
Anbei die Tabelle:
https://www.herber.de/bbs/user/151684.xlsx
Danke im voraus.
Gruß
Stefan

15
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: durchschnittliche Tage
10.03.2022 11:52:54
Stefan
Sorry Falsch angegeben ! Die durchschnittlichen Tage zwischen den gleichen Essen.
Quasi das Essen H001 gab es 5 mal in einen durchschnittlichen Abstand von X Tagen
AW: als Formel z.B. mit Hilfe von AGGREGAT() ...
10.03.2022 12:39:35
AGGREGAT()
Hallo Stefan,
... für "H001" ergibt sich z.B. mit folgender Formel:
=MITTELWERT(INDEX(AGGREGAT(15;6;B$2:B$129/(C$2:C$129="H001");ZEILE(A2:INDEX(A:A;ZÄHLENWENN(C:C;"H001"))))-AGGREGAT(15;6;B$2:B$129/(C$2:C$129="H001");ZEILE(A1:INDEX(A:A;ZÄHLENWENN(C:C;"H001")-1)));))
ein Ergebnis von 45,5 (Tage) Ist es das was Du ermitteln willst.
Da Du aber viele verschieden Mahlzeiten auswerten willst, müssten erst die verschiedenen (ohne Duplikate) gelistet werden, was auch mit INDEX(), AGGREGAT() und ZÄHLENWENN(9 mit einer Formel möglich wäre.
Alternativ zur Formellösung käme noch eine Lösung mit der Power Query Funktionalität in Excel in Betracht. Da müsste ich jetzt allerdings erst einmal drüber nachdenken.
Gruß Werner
.. , - ...
Anzeige
AW: als Formel z.B. mit Hilfe von AGGREGAT() ...
10.03.2022 13:00:15
AGGREGAT()
Hallo Werner,
Danke das sieht auf den ersten Blick schon super aus.
Ich habe in einer zweiten Tabelle noch eine Übersicht über alle Essen evtl. mache ich die Formel pro essen da nur zwei essen einmal vorkommen.
Vielen Dank für die schnelle Antwort.
Gruß
Stefan
AW: gerne, beachte jedoch ...
10.03.2022 13:50:38
neopa
Hallo Stefan,
... das bei vielen auszuwertenden Daten der PC mit meiner Formel-Lösung etwas ins "schwitzen" kommen könnte. Deswegen hatte ich auch als eine wahrscheinlich mögliche Alternative eine PQ-Lösung vorgeschlagen.
Gruß Werner
.. , - ...
AW: gerne, beachte jedoch ...
10.03.2022 16:26:46
Stefan
Hallo Werner,
zum Glück hat mein PC etwas mehr Rechenleistung als ich.
Habe deine Formel jetzt pro Speise angepasst. Ist mit Sicherheit nicht die eleganteste Lösung aber Suchen und Ersetzen ist auch für mich einfach.
Danke nochmal.
Gruß
Stefan
Anzeige
AW: bitteschön, doch ergänzt noch ...
10.03.2022 17:19:26
neopa
Hallo Stefan,
... weil es mich selbst interessiert hat, hier nun für alle Speisen (die mind. 2 mal ausgeben wurden) meine PQ_Lösung, welche mit Ausnahme einer einfachen if ...then .. else Formel rein interaktiv (durch Mausklicks) zusammengestellt ist. Diese Lösung wurde im erweiterten PQ-Editor wie folgt wie nachstehend aufgezeigt (mit)protokolliert und kann in Deiner Datei im PQ-Editor einkopiert werden (vorausgesetzt Du hast die Tabelle mit der Funktion "Als Tabelle formatieren" zuvor in eine "intelligente" Tabelle - namens Tabelle1 - gewandelt). Damit solltest Du zum gleichen Ergebnis wie mit Formel nur etwas schneller kommen nur mit dem kleinen Nachteil, dass Du das Ergebnis aktualisieren musst, wenn neue Daten hinzukommen. Dazu reicht allerdings ein Mausklick auf das entsprechende Icon.

let
Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
#"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Datum", Int64.Type}, {"Hauptspeise", type text}}),
#"Entfernte Spalten" = Table.RemoveColumns(#"Geänderter Typ",{"KW"}),
#"Sortierte Zeilen" = Table.Sort(#"Entfernte Spalten",{{"Hauptspeise", Order.Ascending}, {"Datum", Order.Ascending}}),
#"Hinzugefügter Index" = Table.AddIndexColumn(#"Sortierte Zeilen", "Index", 0, 1, Int64.Type),
#"Hinzugefügter Index1" = Table.AddIndexColumn(#"Hinzugefügter Index", "Index.1", 1, 1, Int64.Type),
#"Zusammengeführte Abfragen" = Table.NestedJoin(#"Hinzugefügter Index1", {"Index"}, #"Hinzugefügter Index1", {"Index.1"}, "Hinzugefügter Index1", JoinKind.LeftOuter),
#"Erweiterte Hinzugefügter Index1" = Table.ExpandTableColumn(#"Zusammengeführte Abfragen", "Hinzugefügter Index1", {"Datum", "Hauptspeise"}, {"Datum.1", "Hauptspeise.1"}),
#"Hinzugefügte benutzerdefinierte Spalte" = Table.AddColumn(#"Erweiterte Hinzugefügter Index1", "Tagesdifferenz", each if [Hauptspeise.1]=[Hauptspeise] then [Datum] -[Datum.1] else null),
#"Entfernte Spalten1" = Table.RemoveColumns(#"Hinzugefügte benutzerdefinierte Spalte",{"Datum", "Index", "Index.1", "Datum.1", "Hauptspeise.1"}),
#"Gefilterte Zeilen" = Table.SelectRows(#"Entfernte Spalten1", each ([Tagesdifferenz]  null)),
#"Gruppierte Zeilen" = Table.Group(#"Gefilterte Zeilen", {"Hauptspeise"}, {{"Mittelwert Tagesdifferenz", each List.Average([Tagesdifferenz]), type number}}),
Gerundet = Table.TransformColumns(#"Gruppierte Zeilen",{{"Mittelwert Tagesdifferenz", each Number.Round(_, 1), type number}})
in
Gerundet
Gruß Werner
.. , - ...
Anzeige
AW: bitteschön, doch ergänzt noch ...
11.03.2022 09:59:18
Luschi
Hallo Stephan und Werner,
ich habe Werner*s PQ-Lösung und meinen PQ-Vorschlag mal zusammengefaßt, wobei mein Vorschlag M-lastiger ist, bei dem 2 oder 3 Schrittfolgen nicht per PQ-Menü realisierbar sind..
https://www.herber.de/bbs/user/151709.xlsx
Gruß von Luschi
aus klein-Paris
AW: daran erkennt man den Programmierer ...
11.03.2022 11:13:01
neopa
Hallo Luschi,
... danke für Deine Lösungsvariante.
Jetzt würde mich ein Benchmarking für die zwei Varianten interessieren. Hintergrund dafür: ab wann lohnt es sich, mit den M-Code vertrauter zu machen?
Bisher kam ich fast immer auch mit der interaktiven Nutzung der angebotenen PQ-Funktionalitäten zu dem von mir angestrebten Ziele. Ab und zu noch eine einfache if .. then .. else Formel so auch hier und manchmal eine Funktion, die ich in https://docs.microsoft.com/de-de/powerquery-m/power-query-m-function-reference gefunden habe.
Gruß Werner
.. , - ...
Anzeige
AW: daran erkennt man den Programmierer ...
11.03.2022 11:43:35
Luschi
Hallo Werner,
wenn man in einer sehr langen Liste Spaltenwerte aufsummieren will, kommt man mit dem PQ-Menü nicht mehr weiter, denn dann läuft man in eine Zeitschleife, die manchmal auch im Nirvana endet.
Die Kombination von List.Sum und List.FirstN ist sehr sehr langsam, hier muß List.Buffer ist Spiel kommen, um im Arbeitsspeicher eine statische, nichtveränderbar Liste zu erzeugen.
Ein sehr ausführliches Beispiel findet man hier:
https://www.myonlinetraininghub.com/quickly-create-running-totals-in-power-query
Anhand von 100.000 Zahlen, die aufsummiert werden sollen, wird gezeigt, was kaum geht, wie es besser und noch besser funktioniert.
Ansonsten ist es wahrscheinlich so wie mit dem Vba-Makro-Recorder, wer damit zufrieden ist, wird die Geheimnisse von Vba nie richtig ergründen. Es wird geschätzt, daß mehr als 50% aller M-Befehle nicht per PQ-Menü-Editor erreichbar sind.
Gruß von Luschi
aus klein-Paris
Anzeige
AW: kann mir schon vorstellen, ...
11.03.2022 13:07:49
neopa
Hallo Luschi,
... dass bei auszuwertenden Massendaten mit speziellen M-Code-Befehlen Performance gewonnen werden kann. Der Vergleich mit dem VBA-Makro-Rekorder ist sicherlich naheliegend. Wobei mE hier wie da gilt, wer die Basis nicht beherrscht, braucht sich um die Ergründung von Geheimnissen gar nicht erst zu bemühen. Mir reicht es, wenn ich einigermaßen die Basis für mich ergründen kann.
Ich danke Dir.
Gruß Werner
.. , - ...
dynamisch (nur wen's interessiert)
10.03.2022 13:33:33
lupo1
=LET(
n;ANZAHL2(A:A);
d;C2:INDEX(C:C;n);
e;EINDEUTIG(SORTIEREN(d));
o;NACHZEILE(e;LAMBDA(a;SUMME(--(d=a))));
WAHL({1.2.3.4};e;o;n/o;n/o*1,4))

mit
Spalte 1: Gericht
Spalte 2: Häufigkeit
Spalte 3: pro Anzahl Werktage
Spalte 4: pro Anzahl Tage (140% von Spalte 3, schlank vereinfacht (ginge auch genau))
Hinweis: ZÄHLENWENN will anscheinend nicht, ich nahm SUMME.
Anzeige
AW: @ Stefan, geht nicht in Deiner XL-Version owT
10.03.2022 13:36:30
neopa
Gruß Werner
.. , - ...
dann lasse ich Dich und Oberschlumpf kommentieren
10.03.2022 14:29:03
lupo1
... und schreibe einfach nur noch meine Lösung.
AW: "kommentiere" doch für den TE selbst owT
10.03.2022 16:02:09
neopa
Gruß Werner
.. , - ...
Dafür bist Du doch da.
10.03.2022 18:02:35
lupo1

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige