Kontext
Ich muss eine Reihe von Aktivitäten mit gewünschten Start- und Enddaten nehmen und prüfen, ob sie angesichts der Einschränkung, dass beispielsweise nur 3 gleichzeitig ausgeführt werden können, sinnvoll sind. Da nie mehr als 3 ausgeführt werden können, muss ich in meinem Zeitplan eine Verzögerung der Aktivitäten einplanen. Obwohl ich weiß, dass dies in MS Project möglich ist, muss ich dies speziell in Excel durchführen.
Problem
Ich habe es geschafft, dies in Excel zu formulieren, und es funktioniert absolut wie vorgesehen für eine kleine Anzahl von Aufgaben über einen kurzen Zeitraum (bis zu etwa 30.000 Zellen). Ich muss es jedoch auf viel mehr Aktivitäten über einen viel längeren Zeitraum anwenden (potenziell bis zu 500.000 Zellen insgesamt - was derzeit etwa 10 Minuten dauert!).Können Sie mir Verbesserungen für meine Formel empfehlen?Ich habe selbst bereits viel optimiert und einige andere Optionen untersucht (siehe unten):
Meine aktuelle Methode
Screenshot der aktuellen Methode mit indikativem Profil
Bitte beachten Sie, dass meine Methode darauf beruht, dass den Aktivitäten eine Prioritätsnummer zugewiesen wird und sie entsprechend sortiert werden (dies ist einfach die früheste „Start“-Dauer).
Formelerklärung zur Generierung des Profils/Gantt-Diagramms:
- Wenn es die erste Zeile ($B9 = 1) im Gantt-Diagramm ist, denken Sie nicht zu viel darüber nach, da vor Ihnen keine anderen Aktivitäten liegen: Tragen Sie einfach in jede Zelle eine Eins ein, wenn Sie innerhalb der gewünschten Termine liegen (IF(AND(AO$3>=$C9,AO$3<=$D9),1,"")).
- Für andere Zeilen... Die Formel prüft, ob (A) Sie sich im richtigen Datumsbereich befinden, (B) ob bereits 3 Aktivitäten über Ihnen durchgeführt werden, (C) ob Sie bereits genügend 1er in diese Aktivität eingegeben haben.
(Ich verwende Excel-Tabellen, bei denen die Formel in jeder Zeile einheitlich sein soll, weshalb ich den 1. und 2. Punkt in derselben Formel habe.)
Aktuelle Verbesserungsversuche:
- Anstatt den gesamten Bereich zu summieren, habe ich versucht, ein SUM(OFFSET(...)) festzulegen, damit weniger Zellen summiert werden müssen. Dieser Versuch bedeutete jedoch nur, dass in der Formel einige Aktivitäten fehlten, was dazu führte, dass einige Aktivitäten trotz der Einschränkung gestartet wurden oder länger als die beabsichtigte Dauer andauerten.
- Anstatt es täglich zu tun, habe ich versucht, es wöchentlich/monatlich zu tun. Das hat die Berechnung zwar sicherlich beschleunigt, aber diese Detailliertheit hat keine genauen Ergebnisse geliefert - also muss ich bei täglich bleiben.
TL:DR:
Gibt es eine Möglichkeit, =IF($B10=1, IF(AND(AO$3>=$C10,AO$3<=$D10),1,""), IF(AND(AO$3>=$C10,SUM(AO$4:AO9)<$D$13,SUM($F10:AN10)<$E10),1,"")) weiter zu optimieren. Danke.
Antwort1
Sie müssen mit einer großen Anzahl von Zellen arbeiten. Ich würde Hilfsspalten verwenden, um die tatsächlichen Start- und Enddaten zu berechnen und die Berechnung auf der rechten Seite wesentlich zu vereinfachen.
- Formel für den tatsächlichen Start:
=IF(COUNTIF($F$1:F1,">="&B2)<$C$11,B2,LARGE($F$1:F1,$C$11)+1) - Formel für tatsächliches Ende:
=E2+D2-1
Jetzt können Sie im Kalenderteil eine Formel wie diese verwenden:=IF(AND(X$1>=$E2,X$1<=$F2),1,"")
Aktualisieren
So funktioniert die Formel:
COUNTIF($F$1:F1,">="&B2)- zählt vorherige Aktivitäten, die nach dem gewünschten Start beendet wurdenCOUNTIF(... )<$C$11- vergleicht es mit der Einschränkung=IF(.... ,B2,...- gewünschter Startzeitpunkt möglich=IF(... ,... ,LARGE($F$1:F1,$C$11)+1)- ist eine Verzögerung erforderlich, prüfen Sie, welche Aktivität von denen, die Ihren Start einschränken, zuerst endet, +1, um am nächsten Tag zu beginnen
Antwort2
Sie würden etwas CPU-Zeit sparen, wenn Sie den Einzelfall der Zeile 1 entfernen, da der ständige Vergleich für 99,9 % der Zeilen nicht erforderlich ist. Fügen Sie einfach eine leere Zeile direkt unter den Überschriften ein und verwerfen Sie das erste IF:
=IF(AND(AO$3>=$C10,SUM(AO$4:AO9)<$D$13,SUM($F10:AN10)<$E10),1,"")
Darüber hinaus scheint es, dass AO$3<=$D10und SUM($F10:AN10)<$E10denselben Enddatumsvergleich durchführen. Man könnte auch beim schnellen Einzelvergleich ohne Summe bleiben:
=IF(AND(AO$3>=$C10,SUM(AO$4:AO9)<$D$13,AO$3<=$D10,1,"")
Definieren Sie als Nächstes eine benannte Bereichs-„Einschränkung“ als Konstante (bezieht sich auf =3) für , um eine Zellensuche zu vermeiden:
=IF(AND(AO$3>=$C10,SUM(AO$4:AO9)<constraint,AO$3<=$D10,1,"")
Wählen Sie als Nächstes verschachtelte WENN-Verknüpfungen anstelle von UND-Verknüpfungen, damit Excel nicht jedes Mal die CPU-intensive „SUMME“ berechnen muss, wenn die Fensterbedingungen bereits FALSCH sind. Ordnen Sie die WENN-Verknüpfungen in der Reihenfolge der höchsten Wahrscheinlichkeit an, dass sie falsch sind:
=IF(AO$3<=$D10,IF(AO$3>=$C10,IF(SUM(AO$4:AO9)<constraint,1,""),""),"")
Und schließlich ersetzen Sie SUM durch COUNT
=IF(AO$3<=$D10,IF(AO$3>=$C10,IF(COUNT(AO$4:AO9)<constraint,1,""),""),"")
Denn auch das Aussehen zählt:
Wie bereits erwähnt, ist eine bedingte Formatierung nicht möglich. Sie können jedoch ASCII-Zeichen wie dieses „█“ anstelle einer 1 verwenden:
=IF(AO$3<=$D10,IF(AO$3>=$C10,IF(ROW(AO9)-ROW(AO$4)+1-COUNTBLANK(AO$4:AO9)<constraint,1,"█"),""),"")
Dies geht jedoch auf Kosten einer komplizierteren Zählung der Anzahl der vorherigen „█“. Für eine schnellere Alternative verwenden Sie die Zahl 4, formatiert in der Schriftart Webdings (sieht fast so aus ►):
=IF(AO$3<=$D10,IF(AO$3>=$C10,IF(COUNT(AO$4:AO9)<constraint,4,""),""),"")
Sie können jede beliebige Zahl zwischen 0 und 9 verwenden und die Schriftart ändern, um einen visuelleren „Balkendiagramm“-Effekt zu erzielen. Es muss jedoch eine Zahl sein, sonst funktioniert COUNT nicht und Sie müssen auf die langsamere COUNTBLANK-Formel vom Typ „█“ zurückgreifen.
Antwort3
Schon mit ein paar einfachen Dingen lässt sich die Geschwindigkeit deutlich steigern. Das Wichtigste ist die Neuordnung der Zellanalyse.
Excel berechnet so weit wie möglich von links nach rechts, Block für Block, und weicht nur dann ab, wenn die Formellogik es dazu zwingt. Eine Abweichung besteht darin, dass es bei der Durchführung eines Tests direkt zum Folgeergebnis übergeht, WENN dies möglich ist.
Wenn Sie also einen Test als ersten Teil einer langen Formel haben IF()und das Testergebnis ein einfaches Ergebnis hat, werden die anderen Verzweigungen nie ausgewertet.You have such a thing that would cut away almost all of the calculating you are doing.
Das ist der AO$4:AO4Test gegen die Einschränkung. Wenn diese Berechnung fehlschlägt, erhalten Sie sofort eine Ausgabe, keine weitere Verarbeitung. Es endet einfach für diese Zelle. Ordnen Sie Ihre Formel also neu an, um dies zuerst zu testen. Auf diese Weise werden nur drei Zeilen weitere Berechnungen als dieser Test anzeigen, anstatt jede Zeile.
(Apropos „erste“: wie Mobusgesagt, hören Sie auf, die Berechnung der „ersten Zeile“ in jeder Zelle durchzuführen. Verwenden Sie den Bereich, den ich oben gezeigt habe AO$4:AO4(natürlich für jede Spalte unterschiedlich) und behandeln Sie die erste Zeile wie jede andere Zeile. Dann ist sie nie Teil der Berechnungen in anderen Zeilen als sich selbst. Der Bereich verankert und erweitert sich genauso wie Sie es jetzt tun, und Sie behandeln „Zeile 1“ einfach wie jede andere Zeile. Die Optimierung auf Eindeutigkeit hilft bei diesem Problem NICHT.)
Führen Sie als Nächstes die Berechnung des SUM()„oder“ COUNT()für eine Zeile durch, um die vorhandene Vervollständigung zu testen. Sie scheinen über genügend Zeilen zu verfügen, sodass fast alle Zeilen in einem abgeschlossenen Zustand vorliegen würden. Wenn Sie also zuerst testen, ob das Datum es interessant macht, müssen Sie diese sowieso überprüfen. Führen Sie dies zuerst durch und reduzieren Sie die zu testenden Daten auf sehr wenige. Auch diese Berechnung wird ohnehin ausgeführt. Wenn Sie sie verschieben, haben Sie im Vergleich dazu weder einen Vorteil noch einen Nachteil. Wenn Sie sie jedoch zuerst ausführen, werden viele andere Berechnungen weggelassen.
In den anderen Antworten und/oder Kommentaren dazu wird die Idee eines benannten Bereichs erwähnt. Ich bin ganz für benannte Bereiche und Hilfsspalten (sogar Hilfsseiten), aber dass Ihre Einschränkung in einer Zelle statt in einem benannten Bereich vorhanden ist, spielt einfach keine Rolle ... wenn Sie sie an einen Ort verschieben, an dem sie ihre Position nicht ständig ändert. Excel erstellt ein Berechnungsschema und löst nach der ersten Berechnung nur Berechnungen für Verzweigungen aus, in denen sich etwas ändert. Platzieren Sie die Einschränkung an einem unveränderlichen Ort und bearbeiten Sie sie nicht ... Excel muss nie Zeit mit Neuberechnungen usw. verbringen. Platzieren Sie sie unter dem Datenbereich und lassen Sie sie die Zeilen jedes Mal ändern, wenn Sie eine neue Aufgabe einfügen, und sie wird es tun. Platzieren Sie sie an einem Ort, an dem diese Art von ständiger Änderung nicht auftritt und sie nicht neu berechnet werden muss. Ein benannter Bereich ist eine einfache, wunderbare Möglichkeit, dies zu tun, aber Sie können diesen Aspekt Ihrer Tabelle einfach neu anordnen, wenn Sie möchten, und Sie können es vielleicht tun, wenn Sie es ab und zu oder öfter ändern möchten, und möchten vielleicht, dass ein Benutzer dies tut. In diesem Fall würde Ihre Formel jedoch die Ergebnisse Ihrer Tabelle komplett verändern, wenn Sie die Einschränkung ändern, und das ist so gut wie unmöglich. Warum also sollten Sie sie im Blatt behalten und nicht in einem benannten Bereich? Und benannte Bereiche haben noch weitere Vorteile, die für dieses Problem jedoch nicht wichtig sind. Wenn Sie es aber tun, platzieren Sie es an einem Ort, an dem sich seine Adresse nicht ständig ändert (im Wesentlichen gilt „Änderung ist Änderung“, unabhängig davon, was sich geändert hat) und es spielt für das Geschwindigkeitsproblem keine Rolle.
Ich habe noch nie etwas gelesen, das sich direkt mit Arithmetik SUM()und String-Ops hinsichtlich COUNT()der Geschwindigkeit befasst. Es sieht so aus, als hätten andere das gelesen und dass die String-Ops schneller sind. In diesem Fall ändern Sie alles using SUM()in use COUNT()COUNTA(). Ich hätte vermutet, dass Arithmetik gewinnen würde, aber so ist es nun einmal.
Ja, wie Sie in Ihrem Kommentar sagen, ist nur das Datum wichtig, an dem die Aufgabe bereits begonnen hat, nicht das erhoffte Abschlussdatum. Daher ist es wichtig, nur das zu überprüfen. Denn Ihr Problem besteht nicht darin, eine Aufgabe aufzugeben, wenn sie zu alt ist. Wenn sie also noch nicht abgeschlossen ist und es sich um Aufgabe 1, 2 oder 3 handelt, wird sie unabhängig vom erhofften Abschlussdatum erledigt.
Die größte Änderung, die Sie jedoch vornehmen können und die alles oben genannte (einschließlich anderer Antworten) in den Schatten stellt, ist ...STOP recalculating every line over and over again.
Sobald eine Aufgabe abgeschlossen ist, wird sie in der dargestellten Logik NIEMALS erneut angesprochen.why keep addressing it again and again???
Kopieren Sie regelmäßig (wöchentlich oder monatlich) alle ausgefüllten Zeilen und fügen Sie die Werte ein. So sind sie für immer erledigt. Vielleicht haben Sie noch 100 Zeilen mit Formeln übrig, statt der 15.000, die Sie aufgebaut haben. (Wenn Sie 3 Aufgaben gleichzeitig erledigen, können Sie natürlich nicht 2.000 Aufgaben gleichzeitig erledigen, also habe ich 100 ausgewählt, aber selbst 20 scheinen plausibler.) Diese Berechnungen werden also NIEMALS, NIEMALS, NIE wieder durchgeführt. Denken Sie nur an die Geschwindigkeitsverbesserung.
Im Vergleich zur gegenwärtigen Situation ist das unfassbar.
Auch dann hätte es interessante Vorteile. Der größte wäre, dass sich die Einschränkung ändern könnte, wenn die Kapazität zunimmt/abnimmt. Wie oben erwähnt, würde eine Änderung derzeit die bisherigen Ergebnisse völlig zerstören. Beim Übergang zu Werten wäre das alte Material jedoch nicht betroffen. Sie möchten es ändern? Kopieren und fügen Sie die Werte für alle Zeilen über dem Änderungspunkt ein, ändern Sie sie und fahren Sie fort, bis Sie sie erneut ändern.
Ein weiterer Grund besteht darin, dass Sie aufgrund der geringeren Rechenlast Dinge wie die dynamische Adressierung umfassender nutzen könnten, obwohl diese nun einen viel kleineren Punkt hätten.
Übrigens ist die Idee bei Hilfsspalten, die man zur Beschleunigung von Dingen verwendet, eine andere als die übliche „eine schwierige Aufgabe isoliert ausführen“, um eine Formel in einer anderen Spalte zu vereinfachen. In diesem Fall wäre es sinnvoll, unveränderliche Teile der Hauptformel in der Hilfsspalte zu isolieren, damit Excel sie einmal berechnet und dann nur, wenn sie sich irgendwie geändert haben. Wenn Sie also im Allgemeinen 11 Parameter in einer Formel haben, sich aber 7 nie oder selten ändern, überarbeiten Sie Ihre Formel so, dass ihre Auswirkungen nur in den Hilfsspalten auftreten und die verbleibende Formel ihre Ergebnisse wie einen einzelnen Parameter liest. Das ... kann eine Menge Überarbeitung bedeuten, die eine ganz andere Herangehensweise an die Verarbeitung der Parameter erfordert, aber normalerweise können Sie es tun und dann werden diese Teile nie neu berechnet, sodass alles viel schneller läuft. Manchmal können Sie es jedoch nicht {genauso wie es manchmal IFERROR()einfach nicht funktioniert und Sie `IF(ISERROR())} verwenden müssen}, sodass das, was Sie tun können, nur wenig bringt. Aber bei über 500.000 Formeln hilft ein bisschen immer noch sehr viel.
Was den Aspekt des Gantt-Diagramms angeht, stelle ich mir vor, dass hier die bedingte Formatierung („CF“) langsam ihr Haupt erhebt. (Ich habe keinen Hinweis darauf gesehen, aber man nimmt an, dass ein Gantt-Diagramm mit Einsen nicht so wahrscheinlich ist wie die Verwendung von CF, um schöne Linien zu erstellen. Wie bereits erwähnt Mobus, gibt es bessere Möglichkeiten als die Verwendung von CF. Darüber hinaus Mobuskann man jedes geeignete „Block“-Zeichen auswählen und bei Bedarf einfach die Funktion „Füllen“ unter Ausrichtung|Horizontal verwenden, um eine Zelle damit zu füllen, sodass die passende Größe und Form weniger wichtig wäre, obwohl Sie die Schriftart trotzdem anpassen müssten, damit das Zeichen der Zeilenhöhe entspricht. REPT()Würde sogar funktionieren.
Aber um es zum „Singen“ zu bringen (insbesondere, da Sie das Obige verwendet haben, um die erforderlichen Berechnungen erheblich zu reduzieren), könnten Sie die Ausgabe über die TEXT()Funktion präsentieren lassen und ihre Formatierungszeichenfolge so erstellen, dass sie die Schriftfarbe enthält. Dadurch könnten Sie für jede Zeile eine andere Farbe angeben, die sich von der Zeile darüber und darunter unterscheidet, sodass die Farben nie nebeneinander liegen und so die Lesbarkeit verbessert wird. Beachten Sie, dass sich dieser Teil in der Formel auf der Zellenseite befindet, nicht in CF mit all seinen anderen Problemen, nicht nur seinen Geschwindigkeitseffekten.
Die Umstellung auf Access oder ähnliche Programme scheint, abgesehen davon, dass sie Ihnen und Ihren Benutzern nicht unbedingt zur Verfügung stehen, keine große Hilfe zu sein, da es sich bei den 500.000 Zellen hauptsächlich um „sich stapelnde Zellen rechts“ und nicht um sich stapelnde Zeilen handelt. 100.000 Aufgaben mit jeweils fünf Zellen mit Berechnungen schreien geradezu nach Access oder etwas Ähnlichem oder vielleicht etwas, das speziell für Projekte gedacht ist. Aber 500 Zeilen mit drei Jahren Daten rechts (1.000-1.100 Berechnungen pro Zeile) schreien NICHT nach einem Datenbankprogramm (obwohl es trotzdem nach dedizierter Software schreit … aber das ist einfach nicht immer möglich, egal ob mit oder ohne Schmerzen). Entgegen der landläufigen Meinung ist Excel KEINE hundelangsame Rechenmaschine, die „Melasse im Januar“ ist, und SQL ist nicht immer irgendein strahlendes, glänzendes Wunderkind des 41. Jahrhunderts, das irgendwie in unserer heutigen Welt existiert. Sie tun das oben Genannte sowieso, also wird die Rechenlast auf einen kleinen, kleinen Teil dessen reduziert, was sie heute ist, also …