Live-Forum - Die aktuellen Beiträge
Datum
Titel
25.04.2024 23:47:55
Datum ausweisen, wenn definierte Summe erreicht ist
25.04.2024 21:40:53
Excel Datenquelle aus dem Web mit Anmeldung?
25.04.2024 21:21:16
Zellkommentar in UserForm anzeigen und bearbeiten
25.04.2024 21:13:13
Kalenderwochen addieren
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1756to1760
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
1756to1760
1756to1760
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Suchbereich bei der Formel Vergleich

Suchbereich bei der Formel Vergleich
06.05.2020 07:51:57
Jan
Hallo zusammen,
ich hatte vor einiger Zeit mal um eure Hilfe bei dem Thema "Werte einmal zählen nach Datum" bekommen. Dank eurer Hilfe habe ich die folgende Formel bekommen, welche auch problemlos funktioniert hat.
=SUMPRODUCT((MATCH(V$1:V$99&L$1:L$99;V$1:V$99&L$1:L$99;0)=ROW(V$1:V$99))*(L$1:L$99=AE6))
Jetzt möchte ich die Range erweitern, da es in Zukunft mehr Zeilen werden. Gibt es ein Maximum? Wenn ich statt der 99 überall 3500 eingebe, zeigt Excel mir noch die richtigen Werte an. Alles darüber nicht mehr. Wollte die 99 mit 20.000 ersetzen.
Geht das?

12
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
400 Millionen Berechnungen sind zu viel
06.05.2020 08:47:40
WF
Hi,
Für den "normalen" VERGLEICH sind 20.000 Zellen kein Problem.
=VERGLEICH(b1;a1:a20000;0) sind 20.000 Berechnungen
hier ist es aber
=VERGLEICH(b1:b20000;a1:a20000;0) das sind 20.000 mal 20.000 = 400 Mio Berechnungen
WF
Anzeige
Zum "normalen" Vergleich folgende Anmerkung:
06.05.2020 09:03:37
lupo1
Aus den 20.000 Berechnungen (bzw. Operationen) werden für den Fall, dass sich das Problem auf Binärsuche umstellen lässt (Standard-Einstellung von VERGLEICH):
=LOG(20000;2) = aufgerundet 15 Operationen
Das hieße dann in diesem Fall:
=VERGLEICH(B1;A1:A20000) statt
=VERGLEICH(B1;A1:A20000;0)
AW: Zum "normalen" Vergleich folgende Anmerkung:
06.05.2020 13:47:05
Jan
Hallo Chris, WF und Lupo1,
Danke für eure Rückmeldungen.
@lupo1
Es ist meiner Meinung nach eine Binärsuche :) In Spalte "L" steht das Datum in den 20.000 Zeilen. In AE6 steht dann ein bestimmtes Datum zb. der 06.05.2020. Hier soll jetzt gezählt werden, wie viele Artikel an diesem Tag bewegt wurden. Dabei sollen sie pro Tag nur 1x gezählt werden.
Wenn es über ein Makro schneller und einfacher geht, würde ich es auch über diesen Weg machen, allerdings kenne ich mich in diesem Bereich nicht so gut aus.
Vg Jan
Anzeige
So einfach ist es nun auch wieder nicht, denn ...
07.05.2020 11:19:35
lupo1
... die "günstige" Binärsuche wird damit erkauft, dass zwar schnell die Position ermittelt wird, jedoch nicht, ob der Suchbegriff (Datum) überhaupt selbst vorhanden ist!
Das muss man dann zusätzlich abfangen.
Bei lückenhaften Datümern hast Du also das Problem, dass Du etwa zum fehlenden 7.5.20 die Daten des vorhandenen 6.5.20 ausgeworfen bekommst. Ohne Warnung.
AW: maßgebliche Bereich lässt sich eingrenzen ...
06.05.2020 12:01:54
neopa
Hallo Jan,
... wenn Du Deine Datenwerte L:V als zwingend Voraussetzung nach Spalte L sortierst! und anzunehmen ist, dass es von dem auszuwertenden Wert AE1 in Spalte L nur einen (Bruch-)teil Daten gibt.
Dann kannst Du mit einer zwar ellenlangen Formel (die ich für mich mit benannten Teil-Formeln strukturieren und damit transparenter definieren würde) die intern notwendigen Rechenoperationen mE wesentlich einschränken könnte.
Die Langform-Formel z.B. so:
=WENNFEHLER(WENN(ZÄHLENWENN(L:L;AE1)=1;1;SUMMENPRODUKT((VERGLEICH(INDEX(V1:V20000;VERGLEICH(AE1;L:L;0)):INDEX(V1:V20000;VERGLEICH(AE1;L:L;0)+ZÄHLENWENN(L:L;AE1)-1)&INDEX(L1:L20000;VERGLEICH(AE1;L:L;0)):INDEX(L1:L20000;VERGLEICH(AE1;L:L;0)+ZÄHLENWENN(L:L;AE1)-1);INDEX(V1:V20000;VERGLEICH(AE1;L:L;0)):INDEX(V1:V20000;VERGLEICH(AE1;L:L;0)+ZÄHLENWENN(L:L;AE1)-1)&INDEX(L1:L20000;VERGLEICH(AE1;L:L;0)):INDEX(L1:L20000;VERGLEICH(AE1;L:L;0)+ZÄHLENWENN(L:L;AE1)-1);0)=ZEILE(V$1:INDEX(V1:V20000;ZÄHLENWENN(L:L;AE1))))*(INDEX(L1:L20000;VERGLEICH(AE1;L:L;0)):INDEX(L1:L20000;VERGLEICH(AE1;L:L;0)+ZÄHLENWENN(L:L;AE1)-1)=AE1)));0)
Die davon abgeleitete benannte Formel lautet dann z.B. nur noch = _Auswert und würde entsprechend strukturiert bei mir aus z.B. 3 bis 5 benannten Teilformeln bestehen.
Gruß Werner
.. , - ...
Anzeige
AW: maßgebliche Bereich lässt sich eingrenzen ...
06.05.2020 14:10:25
Jan
Hallo neopa C,
auch dir danke für die Rückmeldung. Also in L steht das Datum. Dies sind immer die letzten 7 Tage (je nachdem wann man halt die Auswertung macht). In AE ist dann das Suchdatum. Hier soll jetzt gezählt werden, wie viele Artikel an diesem Tag bewegt wurden. Dabei sollen sie pro Tag nur 1x gezählt werden.
AE6 = 06.05.2020
AE7 = 07.05.2020 ....
Wie oft jetzt ein Datum vorkommt und ob es sich dadurch um ein Bruchteil handelt, kann man nie sagen.
Soll nicht doof klingen, aber ist die Langformel so fertig? bzw. versteh ich nicht ganz, was du genau mit der abgeleiteten Formel = _Auswert meinst (genau wie die lange Formel).
Wie ich bei den anderen schon geschrieben habe: Ist sowas über ein Makro zu lösen
Vg Jan
Anzeige
AW: hierzu ...
06.05.2020 14:23:32
neopa
Hallo Jan,
... die Formel war und ist aufgestellt für die Auswertung eines Datumswertes (von mir versehentlich für AE1 anstelle AE6, was aber leicht änderbar ist).
Wenn Du die Formel für weitere Datumswerte in AE6, AE7 ... nutzen möchtest, müsstest Du die Bereiche in der Formel natürlich noch verabsolutieren.
Verstehe ich aber Deine jetzige Aussage richtig, dass in Spalte L nur 7 verschieden Datumswerte stehen und sich dafür 20.000 Datensätze ergeben können? Und Du womöglich über Spalte AE diese alle auswerten willst?
Dann gibt mein Formelvorschlag auch weniger Sinn, weil ja dann letztendlich Excel in Summe wieder relativ rechnen muss.
Gruß Werner
.. , - ...
Anzeige
AW: hierzu ...
08.05.2020 07:25:38
Jan
Hallo Werner,
ja in Spalte L sind immer nur 7 verschiedene Tage, welche sich in der Hauptsaison auf bis zu 20.000 Zeilen verteilen.
In AE4 bis AE10 sind diese Tage ebenfalls. Und hier wollte ich es auswerten
AW: dann, wie bereits geschrieben ...
08.05.2020 20:19:56
neopa
Hallo,
... käme als Alternative auch eine PowerQuery -Lösung in Betracht.
Gruß Werner
.. , - ...
AW: alternativ zur Formellösung ...
06.05.2020 12:22:10
neopa
Hallo Jan,
... wäre auch eine Lösung über die Funktion Daten abrufen und transformieren möglich.
Gruß Werner
.. , - ...
AW: hierzu noch ergänzt ...
06.05.2020 14:12:45
neopa
Hallo,
... eine derartige Lösung hat mehrere Vorteile:
- es ist egal ob nur wenige oder z.B. 1 Mio Datensätze auszuwerten sind.
- die Daten müssen nicht sortiert sein.
- als Ergebnis können alle zutreffenden Werte aus Spalte V duplikatfrei gelistet werden.
- es ist keinerlei Programmierung notwendig.
Die zum Ergebnis führenden Anweisungen (Schritte) können sowohl interaktiv oder auch als "Code" an- bzw. übergeben werden.
Oder wenn Du eine kleine Beispieldatei einstellst, könnte ich Dir (m)eine Lösungsvorschlag damit besser aufzeigen.
Ach ja, dies noch: in meiner Formellösung hatte ich den Vergleichsdatumswert versehentlich in AE1 anstelle in AE6 angenommen.
Gruß Werner
.. , - ...
Anzeige

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige