Sumifs (): Begrenzung von sum_range und kriterienbereich, um die Berechnungsgeschwindigkeit beim Sortieren der Daten zu optimieren.

655
Acemanhattan

Ich habe einen sortierten Datensatz, der ungefähr 50.000 Zeilen lang ist und dessen Daten wie folgt aussehen:

Sumifs (): Begrenzung von sum_range und kriterienbereich, um die Berechnungsgeschwindigkeit beim Sortieren der Daten zu optimieren.

Darin habe ich ungefähr 3 Spalten, ähnlich der Gesamtspalte, die eine ziemlich einfache sumifs () Berechnung durchführt. Das Problem ist, dass alle drei gleichzeitig vorkommen und alle drei auf Summen- / Kriterienbereiche mit einer Länge von 50.000 Zellen achten und das Nettoergebnis ein wirklich langsames Arbeitsbuch für Berechnungen ist.

Ich würde gerne wissen, ob es eine Möglichkeit gibt, die Berechnung zu beschleunigen, indem die Kriterienbereiche auf einen optimierten Bereich begrenzt werden, der die Tatsache nutzt, dass die Daten sortiert sind. Daher brauchen wir immer nur den Bereich direkt über und unter einem gegebenen Wert Zelle.

Vielen Dank

0
Auf wie viele Zellen werden Ihre aktuellen Formeln kopiert? Was meinst du damit, "brauche immer nur den Bereich unmittelbar über und unter einer gegebenen Zelle."? Warum? Was berechnet die Formel eigentlich? Warum haben Sie ein Beispiel mit einer oberen Zeilenreferenz von 14 angegeben? Und warum sind die Referenzen in dieser Formel alles relativ und nicht absolut? Wenn diese Formel beispielsweise in einer Zelle nach unten kopiert wird, was vermutlich angenommen wird, werden die Bereiche, auf die verwiesen wird, zu B3: B15 usw. Ich nehme an, das ist bekannt und beabsichtigt? XOR LX vor 8 Jahren 0
@ XORLX Ich versuche, die Gesamtdollar, die einer bestimmten ID zugeordnet ist, neben der ersten Beobachtung zusammenzufassen. Die Formel besteht aus 3 Spalten und wird in jeder Spalte durch 50.000 Zeilen kopiert. Die eigentlichen Daten beginnen in Zeile 101 und die relative Referenz ist in etwa B51: B151. Der Grund ist, dass, da diese Daten nach id # sortiert sind und da wahrscheinlich keine id # mehr als 20 Beobachtungen enthält, keine Suche von B101: B50000 erforderlich ist, sondern nur der Bereich von id # (n-1, n + 1) (dies meinte ich damit, nur den unmittelbaren Bereich darüber und darunter zu benötigen). Acemanhattan vor 8 Jahren 0
Aber haben Sie dort nicht effektiv Ihre eigene Frage beantwortet, dh Sie setzen einen relativen Bezug so, dass sie immer die Form B (n-50) hat: B (n + 50)? XOR LX vor 8 Jahren 0
Zu wissen, was ich tun muss, ist nicht dasselbe wie zu wissen, wie ich es tun soll. Aber ich denke, das ist dir bewusst. Acemanhattan vor 8 Jahren 0
Nicht wirklich. Was war falsch an Ihrer Referenz von B51: B151? XOR LX vor 8 Jahren 0
Oh, ich dachte, dass es dir schwer fällt. Ja, das funktioniert zwar, aber der Modus für die Anzahl der Beobachtungen pro ID ist wahrscheinlich so etwas wie 3, also ist es für die meisten meiner IDs zu viel. Wenn es so ist, dass die Formel, wie geschrieben, nicht zu lange dauern sollte, dann ist es keine große Sache (ich weiß nicht wirklich, ob das der Fall ist oder nicht), denn was ich wirklich suche, ist um zu verhindern, dass meine Arbeitsmappe jedes Mal eine Minute braucht, um eine Berechnung durchzuführen, wenn ich eine der dynamischen Variablen ändere. Acemanhattan vor 8 Jahren 0
Und Sie haben Tests durchgeführt, um zu überprüfen, ob es sich um diese genauen Formeln handelt, und nicht um andere in Ihrer Arbeitsmappe. XOR LX vor 8 Jahren 0
Ich habe gerade einen kurzen Test gemacht. In einer neuen leeren Arbeitsmappe habe ich Zufallsdaten im Bereich A1: C50000 eingegeben. Ich gab dann die Formel = SUMIFS (B1: B101, A1: A101, A51, C1: C101, C51) in die Zelle E51 ein, dh eine, die sich auf einen Bereich von 101 Zellen bezieht, der die fünfzig Zeilen vor und nach der Formel enthält selbst und die einzelne Zeile, die die Formel selbst enthält. Ich habe diese Formel dann in die Zeile 50000 kopiert. Diese 50000-Formeln im Bereich F51: G50000 wurden ebenfalls kopiert, um die von Ihnen beschriebene Anordnung zu imitieren. Insgesamt knapp 150000 Formeln. Eine vollständige Berechnung dauerte weniger als eine Sekunde. XOR LX vor 8 Jahren 0
Ich bin wirklich neu in Excel, daher bin ich mir nicht sicher, was für ein guter Test das wäre. Allerdings habe ich festgestellt, dass das Löschen der drei Spalten aus meiner Arbeitsmappe die Eigenschaft hat, die Berechnungsgeschwindigkeit auf 1 Sekunde gegenüber 1 Minute zu bringen Ich vermutete, dass sie wahrscheinlich der beste Ort waren, um mit der Optimierung zu beginnen. Acemanhattan vor 8 Jahren 0
Okay. Wenn dies der Fall ist, muss ich darüber nachdenken, wo das eigentliche Problem liegt. Die Arbeitsmappe ist mehr zu einer Datenbank geworden, als sie sein sollte (50 MB). Daher ist es wahrscheinlich an der Zeit, die Machthaber davon zu überzeugen, dass wir die meiste harte Arbeit in SAS erledigen müssen. Acemanhattan vor 8 Jahren 0
Lassen Sie uns [diese Diskussion im Chat fortsetzen] (http://chat.stackexchange.com/rooms/30731/discussion-between-xor-lx-and-acemanhattan). XOR LX vor 8 Jahren 0

0 Antworten auf die Frage

Verwandte Probleme