Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1180to1184
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
Inhaltsverzeichnis

Formel über Makro optimieren

Formel über Makro optimieren
Isolde
Hallo Excel-Profis,
ich habe mir die folgende Formel zusammengebastelt, welche auch inhaltlich richtig funktioniert und bereits getestet wurde:
Zelle P1 =SUMMENPRODUKT((TEILERGEBNIS(3;INDIREKT("B"&ZEILE(B2:B65536)))=1)*(H2:H65536))
/SUMMENPRODUKT((TEILERGEBNIS(3;INDIREKT("B"&ZEILE(B2:B65536)))=1)*(ISTZAHL(H2:H65536)))
Kurze Erläuterung:
Je nach dem, welche Auswahl über den Autofilter getroffen wurde, muss Excel z.B. in Zelle P1 den folgenden Wert ermitteln:
Wenn z.B. in Spalte B ein „x“ steht (quasi angekreuzt wurde) dann soll er in Spalte H,
wenn eine Zahl in gleicher Zeile steht, diese Zahlen in Spalte H aufsummieren und anschließend den Mittelwert bilden.
Das gleiche habe ich habe ich auch noch für andere Spalten, also wenn z.B. in Spalte C ein Kreuz gemacht wurde sollen aus Spalte H
die zugehörigen Werte aufsummiert und davon der Mittelwert errechnet werden .
(bei Änderung der Auswahl über den Autofilter müssen sich die Ergebnisse natürlich aktualisieren)
Das Problem seht ihr in der Formel vielleicht auf den ersten Blick:
Der ausgefüllte Tabellenbereich kann in Zukunft beliebig erweitert werden,
weshalb ich angegeben habe, dass Excel diese Überprüfung bis zur letzten Zeile: 65536 durchführen soll.
Das funktioniert zwar aber dauert natürlich viel zu lange, zumal der Tabellenbereich sicher nie so weit ausgefüllt sein wird
und ich mit diesem Prinzip mehrere Ergebnisse bzw. Spalten abgleichen muss.
Habt ihr vielleicht eine Idee wie man diese Formel in ein Makro einbinden kann,
ohne dass die Berechnung unnötigerweise bis zur letzten Zeile 65536 der Tabelle durchgeführt wird
und somit keine Excel-Kapazität verschwendet wird?

Freundliche Grüße
Isolde

6
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Benutzer
Anzeige
Dynamischen Bereichsname in Funktion einbauen
12.10.2010 13:42:14
NoNet
Hallo Isolde,
hier mein Verbesserungsvorschlag :
- Definiere per Strg+F3 einen dynamischen Bereichsname :
   Name : LetzteZeile
   Bezieht sich auf : =VERWEIS(2;1/ISTZAHL($H$2:$H$65536);ZEILE($H$2:$H$65536))
   Bestätige diesen mit "Hinzufügen - OK"
- Ändere dann die Funktion in P1 folgendermaßen ab :
=SUMMENPRODUKT((TEILERGEBNIS(3;BEREICH.VERSCHIEBEN(B2;ZEILE(INDIREKT("2:"&LetzteZeile))-2;))=1)*(N(BEREICH.VERSCHIEBEN(H2;ZEILE(INDIREKT("2:"&LetzteZeile))-2;))))/SUMMENPRODUKT((TEILERGEBNIS(3;BEREICH.VERSCHIEBEN(B2;ZEILE(INDIREKT("2:"&LetzteZeile))-2;))=1)*(ISTZAHL(N(BEREICH.VERSCHIEBEN(H2;ZEILE(INDIREKT("2:"&LetzteZeile))-2;)))))
Das Ergebnis der SUMMEENPRODUKT-Funktionen berechnet sich dann dynamisch nur auf den Bereich bis zu der Zeile, in der in Spalte H letztmals eine Zahl steht (ermittelt durch den Bereichsnamen LetzteZeile)
Gruß, NoNet
Aktuelle Infos und Anmeldung zum => Exceltreffen 2010 <= in Neubrandenburg (15.-17.10.2010) :
Treffen der Excel-Freunde und zahlreicher Helfer aus diversen Excel-Foren.
Auch DU kannst daran teilnehmen ... => Klicke hier <=

Anzeige
AW: Frage dazu
12.10.2010 14:57:08
Isolde
Hallo und Vielen Dank für Deine Antwort,
ich werde es gleich ausprobieren.
Verstehe nur nicht was bei dem Verweis die "2" vor dem Semikolon zu bedeuten hat und warum anschließend 1 geteilt durch ISTZAHL($H$2:$H$65536);ZEILE($H$2:$H$65536) gerechnet wird?

Bezieht sich auf : =VERWEIS(2;1/ISTZAHL($H$2:$H$65536);ZEILE($H$2:$H$65536))

Erklärung der VERWEIS(...)-Funktion - letzte Zahl
12.10.2010 15:45:11
NoNet
Hallo Isolde,
diese Funktion nutzt verschiedene Berechnungsweisen zur Ermittlung der letzten Zahl innerhalb der Spalte.
Zur Erklärung habe ich die Funktion nachfolgend in ihre einzelnen Schritte aufgeteilt, anhand deren man das Verhalten erkennen kann.
Gegeben sei eine Tabelle, in deren Spalte H entweder ZAHLEN, TEXTE, FEHLERWERTE oder LEERZELLEN enthalten sind.
Mit einer Funktion soll nun die Zeile der letzten ZAHL der Spalte H ermittelt werden.


HIJKL
1
2
3
4
5
6
7
8
9
10
11
12


  • In Spalte H stehen die Werte (ZAHL, TEXT, ..)
  • In Spalte I wird mit Hilfe der Funktion ISTZAHL() geprüft, ob es sich bei dem Wert in Spalte H um eine ZAHL handelt oder nicht. Das ergibt jeweils WAHR oder FALSCH. WAHR ist dabei numerisch 1, FALSCH ist numerisch 0.
  • In Spalte J wird die Zahl 1 durch den Wert WAHR/FALSCH der Spalte I geteilt. Wenn man 1 durch WAHR (numerisch 1) teilt, ergibt das 1/1 = 1. Teilt man 1 durch FALSCH (numerisch 0), ergibt das den Fehlerwert #DIV/0! , da man durch 0 nicht teilen kann.
  • In Zelle L1 wird per VERWEIS()-Funktion aus den Werten 1 bzw. #DIV/0! der Spalte J der Wert 2 gesucht. Da der Wert 2 jedoch nicht enthalten ist, wird der nächstkleinere numerische Wert (das ist die letzte 1 der Spalte J) ermittelt. Es ist eine Eigenschaft der VERWEIS()-Funktion, dass Fehlerwerte (also auch die #DIV/0!) unberücksichtigt bleiben und falls ein Wert nicht exakt gefunden wurde, der nächstkleinere Wert gesucht wird. Man hätte also auch schreiben können : =VERWEIS(1,01;J1:J100;ZEILE(1:12)) - hauptsache 1,01 ist größer als 1 !! Mit ZEILE(1:12) wird dann die entsprechende ZEILENNUMMER ausgegeben, ansonsten würde der letzte ZAHLWERT (hier im Bsp. als die Zahl 99) der Spalte ausgegeben werden.


Funktionen im Tabellenblatt :
Zelle Formel 
I2   =ISTZAHL(H2) 
I3   =ISTZAHL(H3) 
I4   =ISTZAHL(H4) 
I5   =ISTZAHL(H5) 
I6   =ISTZAHL(H6) 
I7   =ISTZAHL(H7) 
I8   =ISTZAHL(H8) 
I9   =ISTZAHL(H9) 
I10   =ISTZAHL(H10) 
I11   =ISTZAHL(H11) 
I12   =ISTZAHL(H12) 
J2   =1/I2 
J3   =1/I3 
J4   =1/I4 
J5   =1/I5 
J6   =1/I6 
J7   =1/I7 
J8   =1/I8 
J9   =1/I9 
J10   =1/I10 
J11   =1/I11 
J12   =1/I12 
L1   =VERWEIS(2;J1:J100;ZEILE(1:12)


Ich hoffe, ich konnte Dir die Funktionsweise dieses Hilfskonstrukts nun deutlich machen und dass Du Deine Aufgabe nun erwartungsgemäß lösen kannst !
Gruß, NoNet
Aktuelle Infos und Anmeldung zum => Exceltreffen 2010 <= in Neubrandenburg (15.-17.10.2010) :
Treffen der Excel-Freunde und zahlreicher Helfer aus diversen Excel-Foren.
Auch DU kannst daran teilnehmen ... => Klicke hier <=

Anzeige
AW: Frage 2
12.10.2010 15:49:09
Isolde
Hallo,
ich habe Deinen Vorschlag grad getestet und er funktioniert.
Mit den Funktionen Verschieben und Bereich hatte ich noch nie gearbeitet:
(N(BEREICH.VERSCHIEBEN(H2;ZEILE(INDIREKT("2:"&LetzteZeile))-2;)))))
WAS bedeutet nach INDIREKT die Zahl 2 mit dem doppelpunkt und was die -2 ?
Vielen Dank für eine Antwort!!
BEREICH.VERSCHIEBEN(..) erzeugt eine MATRIX
12.10.2010 16:17:11
NoNet
Hallo Isolde,
Du hattest ursprünglich folgende Teilfunktion in Deiner Lösung :
INDIREKT("B"&ZEILE(B2:B65536)))=1
Der Teil ZEILE(INDIREKT("2:"&LetzteZeile)) ist etwas ähnliches, wenn auch flexibler :
Mit "2:"&letzteZeile werden die relevanten Zeilen des Bereiches ermittelt : Beginnen mit Zeile 2 bis zur letzten Zeile. Ist die letzte Zeile, die in Spalte H eine ZAHL enthält (siehe meine Antwort auf Deine vorherige Frage) z.B. die Zeile 30, dann ergibt "2:"&letzteZeile den String "2:30" mit ZEILE(INDIREKT("2:"&letzteZeile)) werden also die Zeilen 2 bis 30 (als MATRIX {2;3;4;...;30}) abgearbeitet.
Das Ganze ist in die Funktion BEREICH.VERSCHIEBEN(B2;...) verpackt und bewirkt, dass die Zelle B2 (verschoben um die Zeilenanzahl {2,3,4,....,30} ) überprüft wird. Da B2 verschoben um 2 Zeilen (nach unten) die Zelle B4 ergäbe, subtrahiere ich von der Zeilenzahl einfach nochmal 2 (daher die -2) so dass tatsächlich die Zellen B2, B3, B4,...B30 überprüft werden.
Damit man BEREICH.VERSCHIEBEN() in einer MATRIX-Funktion verwenden kann, muss sie noch zusätzlich in die numerische Funktion N(...) verpackt werden !
Klingt etwas kompliziert, aber glaube mir : Das Erklären dieser Funktion ist komplizierter als das Verstehen der Funktion ! Man muss sie nur einige Male verwendet haben um die Arbeitsweise zu verstehen !
Gruß, NoNet
Anzeige
AW: DANKE DIR!!! o.w.T.
12.10.2010 16:20:34
Isolde

  • 299 Forumthreads zu ähnlichen Themen

    Anzeige
    Anzeige
    Anzeige

    Beliebteste Forumthreads (12 Monate)

    Anzeige

    Beliebteste Forumthreads (12 Monate)

    Anzeige
    Anzeige
    Anzeige