Microsoft Excel

Herbers Excel/VBA-Archiv

Formel baucht zu lange


Betrifft: Formel baucht zu lange von: iceer
Geschrieben am: 01.05.2017 20:12:07

Hallo,
Aufbau meiner Arbeitsmappe:
Tabelle1 etwa 10.000-20.000 Zeilen
in Spalte a je Zeile entweder nichts oder x
In Spalte B je Zeile entweder W, E, B oder nichts
In Spalte c je Zeile ein Text.

Tabelle 2
soll die Einträge aus Tabelle 1 exklusive Spalte A enthalten
Ich weiss wie ich dies lösen könnte aber es dauert und dauert und dauert.
http://www.excelformeln.de/formeln.html?welcher=343

etwas angepasst. Kein Problem.
bei allerdings nehmen wir 5000 Zeilen in Tabelle 2 bei der sich jede Formel auf 20.000 Zellen bezieht dauert jede Änderung, welche ich mache Ewigkeiten.

Jemand ne Idee zur schnellen Umsetzung?

Danke im Voraus Matthias

  

Betrifft: AW: Formel baucht zu lange von: Sepp
Geschrieben am: 01.05.2017 20:15:14

Hallo Matthias,

welche Daten von Tabelle1 sollen denn in Tabelle2 gezeigt werden?

Gruß Sepp



  

Betrifft: AW: Formel baucht zu lange von: iceer
Geschrieben am: 01.05.2017 20:34:53

Ach entschuldige....
Alle Zellen bei denen in Tabelle 1 Spalte A ein x eingetragen ist.

Es gibt auch nioch 3 weitere Tabellen
jeweils nach Tabelle 1 Spalte B aufgelistet
eine Tabelle für W eine für E eine für B

Das habe ich noch nicht einmal ein gefügt und damit würde ich wohl minutenlang bei jeder Änderung warten.


  

Betrifft: AW: Formel baucht zu lange von: Sepp
Geschrieben am: 01.05.2017 21:00:13

Hallo Matthias,

bei so großen Datenmengen würde ich ein VBA-Lösung bevorzugen.

Anbei ein Beispiel mit 10.000 Zeilen.

Bei einer Änderung in Tabelle1 in den Spalten A-C werden die Daten in den Tabellen B, E, W und X automatisch angepasst.


https://www.herber.de/bbs/user/113226.xlsb (als xlsb, weil sonst zu groß!)

Gruß Sepp



  

Betrifft: AW: Formel baucht zu lange von: iceer
Geschrieben am: 02.05.2017 17:41:29

Huhu,

erstmal Danke.
Eine VBA Lösung wäre auch meine persönliche erste Wahl, allerdings leider nicht gestattet....
Frag mich nicht warum. Es ist halt so^^
Es muss leider wirklich eine Formellösung her, auch wenn das was du vorgeschlagen hast eigentlich perfekt ist.


  

Betrifft: AW: Formel baucht zu lange von: Sepp
Geschrieben am: 02.05.2017 18:52:41

Hallo Matthias,

bei so vielen Zellen. wird eine Formel-Lösung immer langsam sein.

Um zu sehen, ob man die Formeln evtl. optimieren kann, müsstest du ein Beispiel hochladen.

Gruß Sepp



  

Betrifft: AW: Formel baucht zu lange von: iceer
Geschrieben am: 02.05.2017 21:14:14

Deine Datei ist eigentlich optimal^^
Von der Struktur her ist diese identisch mit meiner.
Bis auf eine kleine Sache es kommen auch komplette Leerzeilen vor.
Hier fängt auch der VBA code an Sachen auszugeben, die nicht passen.


  

Betrifft: AW: Formel baucht zu lange von: Sepp
Geschrieben am: 02.05.2017 21:32:04

Hallo Matthias,

in einer integeren Tabelle/Liste sollten keine Leerzeilen enthalten sein, sowas macht immer Probleme.

Leerzeilen können allerdings durch Filterung einfach entfernt werden.

Hast du schon mal an Pivot-Tabellen gedacht?

Mich hätten deine Formeln interessiert.

Gruß Sepp



  

Betrifft: AW: Formel baucht zu lange von: iceer
Geschrieben am: 02.05.2017 22:43:49

Ich hatte das noch gar nicht komplett mit Formeln umgesetzt, sondern nur die eine Tabelle. Aber ich kann das ganze gerne morgen zukommen lassen.

Momentan habe ich keinen Zugriff darauf


  

Betrifft: AW: Formel baucht zu lange von: iceer
Geschrieben am: 05.05.2017 17:39:27

Tabelle:x C1
Um nach x zu filtern:

=WENN(ZEILE()>SUMME(ISTZAHL(FINDEN("X";Tabelle1!A$1:A$10000))*1);"";INDEX(Tabelle1!A:C; _
KKLEINSTE(WENN(ISTZAHL(FINDEN("X";Tabelle1!A$1:A$10000));ZEILE($1:$10000));ZEILE(A1));3))
B1
=INDEX(Tabelle1!B:B;VERGLEICH(C1;Tabelle1!C:C;0))
A1 ist einfach ausgeblendet


  

Betrifft: AW: Formel baucht zu lange von: Sepp
Geschrieben am: 05.05.2017 17:55:10

Hallo Matthias,

die Formel ist OK, da kann man eigentlich nichts optimieren. Auser beim INDEX() würde ich nicht auf die ganzen Spalten A:C verweisen, sondern besser A1:C30000.

Wie schon erwähnt, könntest du auch mal eine Pivottabelle probieren, oder eine Datenabfrage innerhalb der Tabelle.

Gruß Sepp



  

Betrifft: AW: Formel baucht zu lange von: iceer
Geschrieben am: 05.05.2017 18:13:19

Habe nun selbst etwas "optimiert"
Das meiste an Rechenarbeit friosst komischerweise:

WENN(ZEILE()>SUMME(ISTZAHL(FINDEN("X";Tabelle1!A$1:A$10000))*1);""

daher nun:
=WENNFEHLER(INDEX(Tabelle1!C:C;KKLEINSTE(WENN(Tabelle1!A$1:A$10000<>"";ZEILE($1:$10000));ZEILE(A1))); "") 
ist um einiges schneller. Listet halt alles ausser Leerzeilen auf...
Für die eine Tabelle geht das.
Für die andere dann:
=WENNFEHLER(INDEX(Tabelle1!A:C;KKLEINSTE(WENN(ISTZAHL(FINDEN("V";Tabelle1!B$1:B$10000)); ZEILE($1:$10000));ZEILE(A1));3);"") 
Pivot Tabellen?
Noch nie damit gearbeitet^^


  

Betrifft: AW: Formel baucht zu lange von: iceer
Geschrieben am: 05.05.2017 18:29:57

Nun auch mit einer Pivot Tabelle versucht.

Klappt eigentlich ganz schnell und sauber ausser:
Die Werte ändern sich:
Ich muss jede Pivot Tabelle aktualisieren.
Und was viel wichtiger ist:
Die Pivot Tabelle sortiert alles nach Alphabet.
Das darf aber nicht sein.
Es müssen die Zellen untereinander aufgeführt werden.

Ich könnte auch per =Tabelle1!A1
Dann Filtern nach x.
Wäre sinnvoller als die Pivot Tabelle in diesem Fall. Finde ich zumindest


  

Betrifft: AW: Formel baucht zu lange von: iceer
Geschrieben am: 05.05.2017 18:51:36

Ob das professionell ist, keine Ahnung.
Ob es auf Dauer gut geht und sauber durch läuft, keine Ahnung.
ob es schnell ist und momentan das macht was es soll: JA
Hier meine Lösung nun einmal beschrieben:
in die Tabellen x,B,W,E jeweils in Zelle A1
Tabelle1!A1 nach rechts und beliebig weit nach unten setzen.
Filter setzen und als Makro in Tabelle1:

Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("B").Cells.AutoFilter field:=2, VisibleDropdown:=True
Sheets("B").Cells.AutoFilter field:=2, Criteria1:="B"
Sheets("W").Cells.AutoFilter field:=2, VisibleDropdown:=True
Sheets("W").Cells.AutoFilter field:=2, Criteria1:="W"
Sheets("E").Cells.AutoFilter field:=2, VisibleDropdown:=True
Sheets("E").Cells.AutoFilter field:=2, Criteria1:="E"
Sheets("x").Cells.AutoFilter field:=1, VisibleDropdown:=True
Sheets("x").Cells.AutoFilter field:=1, Criteria1:="X"
End Sub



  

Betrifft: AW: Formel baucht zu lange von: Sepp
Geschrieben am: 05.05.2017 19:00:33

Hallo Matthias,

du sagtest bei meiner VBA-Lösung:

"Eine VBA Lösung wäre auch meine persönliche erste Wahl, allerdings leider nicht gestattet....
Frag mich nicht warum. Es ist halt so^^"

??

Gruß Sepp



  

Betrifft: AW: Formel baucht zu lange von: iceer
Geschrieben am: 05.05.2017 19:42:25

Ja ich weiss. Das ist auch bisher noch nicht durch.
Es ist mir aber mittlerweile egal ob das ganze gestattet ist.
Entweder der Chef gibt frei oder Pech.
Natürlich hätte ich auch an deiner Version weiter arbeiten können. Allerdings hab ich die einfach nicht verstanden^^


Beiträge aus den Excel-Beispielen zum Thema "Formel baucht zu lange"