Excel: Killerformel ... wie auf Performance optimieren?

[WulfmanGER]

Hallo zusammen,

ich hab eine Excel-Tabelle (Excel 2013) die unter die Kategorie "Killer-App" fällt . Mein R7-2700X darf hier regelmäßig rödeln (bei jeder Änderung erstmal 30sek warten...). Die Ursache ist relativ schnell gefunden - die frage ist wie ich das ganze dann auf Performance optimiere:

Tabelle 1 "Daten":
(ich liste nur die relevanten Spalten auf)

	P	Q	AD
1	Auflösung	Bildformat	Hilfsspalte "Pixel"
2	1280x720	720p	921600

Die Formeln:
P: fixe eingabe
Q: {=WENNFEHLER(INDEX(Auflösungen!C:C;VERGLEICH(1;(AD2>=Auflösungen!A:A)*(AD2<=Auflösungen!B:B);0));"")} [MATRIXFORMEL!]
AD: =WENN(ISTLEER(P2);"";WERT(LINKS(P2;FINDEN("x";P2)-1))*WERT(RECHTS(P2;LÄNGE(P2)-FINDEN("x";P2))))

Das ganze dann für ca. 17.000+ Zeilen. Die Performanceprobleme kamen mit den Formeln in Q und AD (das Feature kam Zeitgleich; früher hab ich Q händisch gesetzt - war mir aber zu Arbeitsintensiv - und förderte Flüchtigkeitsfehler). Weitere Formeln sind nicht Enthalten (ein paar Zeilen haben noch eine (temporäre) mini-Formel: wenn in AA etwas steht, dann zeige mir was in B steht). In AD muss ich mit ISTLEER arbeiten, da ich bei einigen Einträgen keine Auflösung habe.

Tabelle 2: "Auflösungen":

	A	B	C
1	min	max	Format
2	1.300.000	2.138.400	1080p
3	593.920	1.003.520	720p
4	...	...	4k
5	...	...	2k
6	...	...	SD

Keine Formeln
=> Also keine sonderlich große Tabelle.

In der Tabelle "Daten" hab ich ca. 500 "Varianten" bei Auflösungen - daher hab ich das so versucht zu "strukturieren" ... sonst könnte man ja ein einfaches "wenn 1280x720, dann 720p" machen ... bzw. einfacher index-vergleich. Das war mein erster Ansatz, scheiterte nur später ...

Jetzt wäre also die Idee die beiden Formeln in "Daten" Q und AD zu optimieren. In AD muss ich kompliziert aus die beiden Werte in P multiplizieren. Könnte mir vorstellen das dieses einfacher geht. Lösung hab ich damals aber keine gefunden. "Ersetze x durch * und berechne" . Spalte Q knallt natürlich auch rein. Das Problem ist das ich einen Wert in AD habe der so natürlich nicht in der Tabelle "Auflösungen" steht - der befindet sich zwischen Min/Max. Daher musste ich mit dieser Matrixformel arbeiten.

Ich kann natürlich ausstellen das Änderungen fortwährend Aktualisierungen anstoßen ... da hatte ich auch versucht - gab dann aber andere Probleme (mit temporären Formeln die ich nicht ohne Fehler "ziehen" konnte). Daher leider nicht machbar.

Auf VBA möchte ich verzichten - hier könnte ich natürlich etwas schreiben, was mir auf Knopfdruck das Format ermittelt. Möchte aber in dieses Relikt (die Tabelle ist mittlerweile gut 20 Jahre alt!) keine Programmierzeit mehr investieren.

Vielleicht hat hier jemand eine bessere Formel? Oder eine einfache idee das sauberer umzusetzen.

Danke schon mal

 

[Caspian DeConwy]

könnte man ja ein einfaches "wenn 1280x720, dann 720p"

Oder per SVERWEIS? Bräuchtest du halt eine kleine Hilfstabelle.

Was sonst helfen könnte: keine Verweise auf ganze Spalten (A:A, B:B, C:C), sondern den genauen Bereich angeben*; ich hatte mal ein ähnliches Problem mit nur 1500 Zeile und die Bereichseinschrenkung hat die Berechnungsdauer quasi auf 0 reduziert.


*) bzw. mit BEREICH.VERSCHIEBEN einen dynamischen Bereich angeben.

 

[Vigilant]

Wir haben noch größere "Tabeten" im Office. Und sehr große Matrizen mit Verweisen und/oder Matrixformeln kosten halt Performance, weil jede einzelne Zelle mit ihren Bezügen in richtiger Reihenfolge berechnet werden muss.

Du könntest in den Optionen die automatische Neuberechnung zunächst auch ausstellen, bei Bedarf manuell berechnen lassen. Das unterbindet zumindest die Unterbrechungen.

Generell kommt Excel mit seinen Formel-Bordmitteln dort an seine Grenzen. Das ist normalerweise der Zeitpunkt, die Dinge in ein Datenmodell zu überführen (Power Query, Power Pivot bspw.).

 

[adAstra]

Könntest du einmal in ein paar Sätzen zusammenfassen, was du mit dieser Tabelle genau erreichen möchtest? Ich bin mir sicher, dass sich das dramatisch optimieren lässt.

 

[Scientist]

Die Formel in AD wird kein großen Einfluss auf die Performance haben.
Optimierung sehe ich hier entweder beim Eintragen der Aufloesung (Werte getrennt notieren).
Alternativ kann man den zweiten Teil um einen Befehl und ein Zellverweisreduzieren:

=LINKS(C2;FINDEN("x";C2)-1)*TEIL(C2;FINDEN("x";C2)+1;99))
(Wert() ist nicht notwendig, wenn eine mathematische Operation durchgefuehrt wird)

Mit der Formel in Q wirst du aber nicht gluecklich.
Du Vergleichst 17.000 mal zwei Mal 500 Werte miteinander bzw. mehr, weil du es auf die ganze Spalte anwendest.
Keine Ahnung, wo Excel die Grenze zieht. Wie Caspian DeConwy schon schrieb, koennte durch eingrenzen des Bereichs eine Verbesserung erzielt werden.

Wenn VBA keine Option ist, wuerde ich beim Ermittlungsverfahren des Formats als solches ansetzen.


Allgemein waere es hilfreich, wenn du mehr Aufloesungen bereitstellen wuerdest, dann kann man etwas herumprobieren. Vielleicht kommt man hier mit Summenprodukt shcneller zum Ziel ...

 

[WulfmanGER]

Was sonst helfen könnte: keine Verweise auf ganze Spalten (A:A, B:B, C:C), sondern den genauen Bereich angeben*

Da die Tabelle in Auflösungsblatt ja fix ist (die ist genauso groß wie oben Abgebildet!), wäre das ein Versuch. Ich teste das ASAP! Danke für den Hinweis. Bei der Spalten-Variante kommt ja immer noch das prüfen dazu ob in Zeile 1049530 etwas steht usw. ...

Du Vergleichst 17.000 mal zwei Mal 500 Werte miteinander bzw. mehr, weil du es auf die ganze Spalte anwendest.

Ups - das hab ich etwas ungeschickt formuliert. Die Tabelle oben "Tabelle 2: "Auflösungen" ist die vollständige Tabelle. Ich wollte mit mit

In der Tabelle "Daten" hab ich ca. 500 "Varianten" bei Auflösungen - daher hab ich das so versucht zu "strukturieren" ... sonst könnte man ja ein einfaches "wenn 1280x720, dann 720p" machen

nur sagen wieso dieses min/max. .... z.b. definiere ich eine Auflösung von 1000x720 (also 720.000px) immer noch als 720p ... 1000x690 ist auch noch 720p) ... Ursprünglich hatte ich alle 500 Variante in einer Liste und konnte so mit SVERWEIS drauf zu greifen - aber wenn neue Auflösung, musste ich zwingend auch die Variante pflegen. Über diese Min-Max-Range

593.920

1.003.520

720p

sag ich somit das ALLE Auflösungen zwischen 593920px bis 1003520px 720p sind. In der Datentabelle ermittle ich ja die Pixel.

Das ist normalerweise der Zeitpunkt, die Dinge in ein Datenmodell zu überführe

Das läuft bereits in gewisser weise parallel. Diese Excel dient heute primär nur als Import-Tabelle (trotzdem mit Gesamtübersicht) für eine mySQL-DB (mit PHP-Frontend). Das ganze ist nur schon bestimmt 15 Jahre alt. Eines meiner allerersten Scripte. Da ist noch mehr Quick&Dirty enthalten als sonst was. Ich könnte das berechnen in Excel sein lassen und im PHP-Frontend bei der Ausgabe passend Anzeigen (in php z.b. das ganze mit einer Funktion machen, oder ein Array ab fragen usw.) - nur leider ist das ganze aufgrund des Umfangs nicht sooo einfach wie es eigentlich ist . Ich pflege jetzt die Excel bis ich mit dem neuen PHP-Frontend fertig bin (aber leider sehr Zeitintensiv - fehlt mir derzeit; hab das auch immer vor mir hergeschoben und Stattdessen eine Musik und Bücher-DB geschrieben [und mir dabei noch ein wenig Wissen angeeignet; effektivere DB-Aufrufe [kein Select-Select mehr usw.;] - vieles von den beiden Projekten möchte ich auch in die Filmdatenbank übernehmen ... ZEIT....) ... und daher möchte ich auch kein Gehirnschmalz mehr in VBA einsetzen. Die Excel soll jetzt einfach nur noch laufen bis zum finalen Import in Datenbank 2.0 . Das mit der MATRIX ist relativ neu als ich feststellte das ich mit Auflösungen doch viel mehr machen kann (bin auch ein kleines Spielkind und Statistik-Fetischist .. 80% des Scripts besteht nur aus Stastitik ....)

Könntest du einmal in ein paar Sätzen zusammenfassen, was du mit dieser Tabelle genau erreichen möchtest? Ich bin mir sicher, dass sich das dramatisch optimieren lässt.

Denke hab das oben ja noch mal etwas beschrieben. Letztendlich möchte ich neben der Auflösung 1280x720 auch die Info haben: ist 720p. Oben schrieb ich das ich Statistik-Fetischist bin ..... Eine Statistik über
1280x720 = 1000
1278x700 = 1
1270x720 = 200
1920x1080 = 1000
1920x768 = 200
Wird sehr umfangreich 500 Einträge .... Ja ... nee
Daher hab ich mir die ganzen Auflösungen angeschaut ... 500 an der Zahl und hab die Anhand ihrer Pixel in Gruppen eingeteilt. 1000x720 ist z.b. 720p. Wenn die Pixelzahl zu gering ist, wird SD raus.
Jetzt hab ich:
720p= 1201
1080p = 1200
SD= 4000
4k = xyz
SD = Rest

LEIDER muss ich das derzeit noch mit Excel aufbereiten. Änderung ist geplant, siehe letztes beantwortetes Zitat.

=LINKS(C2;FINDEN("x";C2)-1)*TEIL(C2;FINDEN("x";C2)+1;99))
(Wert() ist nicht notwendig, wenn eine mathematische Operation durchgefuehrt wird)

Gerade im Büro-Excel probiert .... mhhh ... WARUM hab ich WERT() genutzt ... grübel Im Excel 365schlagmichtod nimmt er die Formel wie von dir genannt. Muss ich mal zu hause anschauen.

Optimierung sehe ich hier entweder beim Eintragen der Aufloesung (Werte getrennt notieren).

Dann spare ich mir die Links, Teil, Finden-Formel und mache einfach Zelle*Zelle. Problem ist hier aber die Ziel-DB - die hab ich damals auf volle Auflösung ausgelegt. H/W-Trennung machte keinen sinn. Mein Datei-Auslesetool macht auch nur Gesamtauflösung - das kopiere ich 1:1 in Excel

Aber ... Zwei Optimierungen hätte ich schon mal ... A:A in A2:A6 und WERT entfernen. WERT wird sicher nicht der Performanceburner werden - aber hier zählt irgendwie schon jede Sekunde die ich einspare .... Die Spaltengeschichte find ich interessanter. Man setzt so aus Einfachheit die Maus immer auf die Spalte und hat dann A:A ... doofe Angewohnheit ...

 

[adAstra]

Denke hab das oben ja noch mal etwas beschrieben.

Was du schreibst ist ein ziemliches Konvolut. Versuche bitte einmal aufzuschlüsseln:

1. Was sind die zu verarbeitenden Daten?
2. Was soll mit den Daten passieren?
3. Welche Ausgabe bzw. welches Ergebnis wünschst du

Klassisches EVA-Prinzip: Eingabe, Verarbeitung, Ausgabe. Auf den ersten Blick erscheint mir die Aufgabe als viel zu banal, als dass es auch nur wenige Augenblicke dauern sollte dies zu berechnen. Von Big Data sind wir noch Lichtjahre entfernt

 

[Scientist]

Statt der Matrixformel wuerde ich bei 5 Werten mal ne stupide Wenn-Verschachtelung probieren ...

EDIT:
Lade einfach mal ein paar Werte hoch bzw. Dummy Datei.
(10-20 Aufloesungen, vollstaendige Aufloesungstabelle)
Ggf. kann man auch einfach mit SVerweis arbeiten.