Live-Forum - Die aktuellen Beiträge
Datum
Titel
19.04.2024 17:05:40
Zählen von Zeilen, in denen in bestimmten Bereich Daten sind
19.04.2024 16:34:52
gemeinsamer Filter über drei Spalten
19.04.2024 15:39:42
Exceltabelle daten per combobox zu word Datei
19.04.2024 14:04:05
PDF per VBA ausgeben - Seitenskalierung
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1300to1304
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
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1300to1304
1300to1304
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

VBA gestutzter Mittelwert

VBA gestutzter Mittelwert
25.02.2013 13:51:12
Dieter
Hallo zusammen,
ich habe diese Formel in meiner Tabelle:
{=GESTUTZTMITTEL(WENN(MonateTab=A9;GesDauer);StutzProzent)}
MonateTab,ist der Monatsbereich.
A9,ist der Monat wonach gefiltert werden soll.
GesDauer, ist der zu berechnende Bereich.
StutzProzent, ist die Filtergrösse.
Funktioniert soweit gut.
Wie müsste ich das in VBA schreiben ?
ActiveCell.Value = ?
Gruss Dieter

33
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: VBA gestutzter Mittelwert
25.02.2013 14:04:03
Daniel
Hi
am einfachsten wahrscheinlich so:
With ActiveCell
.FormulaArray = "=TRIMMEAN(IF(MonateTab=R9C1,GesDauer),StutzProzent)"
.Formula = .Value
End With
gruß Daniel

AW: VBA gestutzter Mittelwert
25.02.2013 14:23:51
Dieter
Hallo Daniel,
ja, Danke funktioniert.
Gruss Dieter

AW: VBA gestutzter Mittelwert
25.02.2013 14:57:49
Dieter
zu früh gefreut.....
ich möchte eigentlich nur den Wert ohne Formel in der Zelle stehen haben,
weil die Formeln (insges. 48) die Performance ziehmlich runterziehen.
Gibt es keine VBA Funktion dazu?
Gruss Dieter

AW: VBA gestutzter Mittelwert
25.02.2013 15:07:14
Klaus
Hi,
die VBA Funktion wäre dann application.worksheetfunction.trimmean()
Aber ob das schneller ist?
Grüße,
Klaus M.vdT.

Anzeige
Unsinn von mir
25.02.2013 15:41:19
mir
Hallo Dieter,
bitte meinen vorherigen Beitrag ignorieren.
Grüße,
Klaus M.vdT.

AW: VBA gestutzter Mittelwert
25.02.2013 14:58:14
Dieter
zu früh gefreut.....
ich möchte eigentlich nur den Wert ohne Formel in der Zelle stehen haben,
weil die Formeln (insges. 48) die Performance ziehmlich runterziehen.
Gibt es keine VBA Funktion dazu?
Gruss Dieter

AW: VBA gestutzter Mittelwert
25.02.2013 15:16:27
Daniel
Hi
genau das macht mein Makro ja
erst die Formel in die Zelle schreiben, dort berechnen lassen und dann die Formel durch ihren Wert ersetzen, weg ist die Formel.
ist normalerweise das schnellste, da Formeln in Excel schneller berechnet werden als in VBA, zumindest wenn man den gleichen Rechenweg verwendet wie die Formel.
der Umweg über VBA lohnt sich dann, wenn man einen günstigeren Weg findet, um zum gleichen Ergebnis zu kommen.
gruß Daniel

Anzeige
AW: VBA gestutzter Mittelwert
25.02.2013 15:26:56
Klaus
Hi Daniel,
wenn ich jetzt Blödsinn erzähle dann korrigiere mich bitte:
das schreiben der Formel ins Tabellenblatt, um es dann mit Werten zu überschreiben, löst eine Neuberechnung aus. Je nach Formel kann die ganz schön lange dauern.
Schalte ich die Berechnungen aus, bringt die per VBA geschriebene Formel kein Ergebniss.
Ergo: Der Umweg über activecell.vaule = application.worksheetfunction.FORMEL() bringt Zeitvorteile, da er auch ohne automatische Berechnung funktioniert.
Grüße,
Klaus M.vdT

AW: VBA gestutzter Mittelwert
25.02.2013 15:33:48
Daniel
Hi
kommt darauf an.
für ne einzelne Formel sicherlich.
sollte die gleiche Formel in mehrere Zellen gleichzeitig geschrieben werden, so ist es meistens sinnvoller, erst die Formeln zu schreiben (natürlich in alle Zellen gleichzeitig) und dann die Formeln als ganzes durch ihren Wert zu ersetzen.
hier besteht zusätzlich das Problem, daß es sich anscheinend um eine Array-Formel handelt (so vonwegen der geschweifen Klammern)
ob das jetzt bei dieser Formel notwendig ist, hab ich mir nicht so genau angeschaut, aber meines wissens nach beherrscht die Worksheetfunction noch keine Verarbeitung als Array-Formel.
Das müsste dann explizit ausprogrammiert werden, wenn der Wert direkt in die Zelle geschrieben werden soll.
gruß Daniel

Anzeige
AW: VBA gestutzter Mittelwert
25.02.2013 15:40:36
Klaus
aber meines wissens nach beherrscht die Worksheetfunction noch keine Verarbeitung als Array-Formel.
Recht hast du! Ich hab mir nur die englische Bezeichnung von GESTUTZTMITTEL rausgesucht und nicht gesehen dass es hier als ARRAY eingesetz wird.
Der Formelteil (WENN(MonateTab=A9;GesDauer) sieht mir aber stark danach aus, dass es hier tatsächlich eine Matrixfunktion sein muss.
Dieter, dann nimm entweder Daniels Makro oder versorge uns mit ein paar Details zu deiner Datei, um einen schnelleren Weg zu finden. Meistens lassen sich Arrayformeln durch Hilfsspalten substituieren, was dann einen erheblichen Geschwindigkeitsvorteil bringt.
Grüße,
Klaus M.vdT.

Anzeige
Man kann eine MatrixFormel per ...
25.02.2013 16:50:31
Luc:-?
…VBA idR aber recht einfach berechnen, Klaus & Daniel,
indem man sie evaluiert, also hier …
ActiveCell = Evaluate("TRIMMEAN(IF(MonateTab=A9,GesDauer),StutzProzent)")
Probleme gibt es idR nur dann, wenn anderweitig in der Prozedur belegte/ermittelte Datenfelder in einen EvaluierungsfmlText eingefügt wdn sollen. Diese müssten dann erst in Matrixkonstanten in Textform umgewandelt wdn. Außerdem gibt's noch den einen oder anderen Sonderfall, der ebenfalls nicht oder nur mühsam so ausgewertet wdn kann.
Gruß Luc :-?

Sonderfälle wären zB Fktt in der Fml, ...
25.02.2013 17:01:29
Luc:-?
…die regulär skalare Argumente verlangen. Ein Datenfeld als Argument wird dann normalerweise nur im Rahmen der allgemeinen XlMatrixFktionalität Wert-für-Wert ausgewertet. Oft müssen dafür auf dem Blatt mindestens 2 Zellen ausgewählt wdn, damit diese greift. Das wäre dem Einsatz der Evaluate-Methode natürlich nicht gerade förderlich.
GESTUTZTMITTEL erwartet aber ohnehin ein DFeld als Arg1, so dass wohl auch das WENN-Ergebnis-DFeld voll über alle Zellen der benannten Bereiche ermittelt wird.
Luc :-?

Anzeige
AW: Sonderfälle wären zB Fktt in der Fml, ...
25.02.2013 17:17:27
Dieter
Hallo zusammen,
ich hätte beinahe meinen Zahnarzttermin vergessen!
Nun habe ich getestet......
Die Lösung von Luc ist genau die, die ich brauche
ActiveCell = Evaluate("TRIMMEAN(IF(MonateTab=A9,GesDauer),StutzProzent)")
Danke an alle
Daniel, Deine Lösung fuktioniert auch (war mein Fehler)!
Gruss Dieter

Bitte sehr! Gruß owT
25.02.2013 18:14:37
Luc:-?
:-?

Außerdem sollte auch dein obiger ...
25.02.2013 17:08:43
Luc:-?
…Ansatz mit WorksheetFunction aus dem gleichen Grund, wie unter meinem Beitrag Sonderfälle… angegeben, funktionieren, Klaus;
es muss aber zuvor erst das aus dem Vergleich resultierende Datenfeld für Arg1 ermittelt wdn.
Wenn das nicht so wäre, könnte nicht mal WorksheetFunction.Sum verwendet wdn!
Gruß Luc :-?

Anzeige
meine Antwort
26.02.2013 12:11:57
Klaus
Hallo Luc,
du hattest mich um eine Antwort auf diesen Beitrag gebeten.
wie so oft hast du mich sprachlos gemacht. Ich lese deine Beiträge, und versuche sie zu verstehen ... wenn etwas aber meinen bescheidenen Excel-Horizont übersteigt lege ich das Thema beiseite.
Da es hier aber nicht um mein Problem ging, habe ich das ganze nicht nachgebaut und nachvollzogen.
Grüße,
Klaus M.vdT.

Na gut, besser als nichts... ;-) Aber du ...
26.02.2013 12:17:49
Luc:-?
…bleibst doch sicher nicht sprachlos, Klaus…! ;-)
Gruß Luc :-?

AW: Na gut, besser als nichts... ;-) Aber du ...
26.02.2013 12:24:54
Klaus
Hallo Luc,
sprachlos bleibe ich selten lange!
In meinem Bekannten- und Kollegenkreis gibt es niemanden, der mir in Excel auch nur im Ansatz das Wasser reichen könnte. Hier im Forum gibt es ein paar Spezialisten (du, Rudi, NoNet, die Excelformeln.de-Jungs, Nepumuk ... Liste nicht vollständig und ohne wertende Reihenfolge), die mich mit ihrem wie selbstverständlich gepostetem Fachwissen in die Schranken weisen. Und dass lässt mich dann schon mal sprachlos zurück :-)
Grüße,
Klaus M.vdT.
(ich soll aber jetzt nicht nach jedem deiner Posts "Danke für den Hinweis! owT" posten, oder?)

Anzeige
Soso! ;-) Ah, noch etwas, du hast mal ...
26.02.2013 13:00:34
Luc:-?
…gemeint, du müsstest doch mal einen VBA-Kurs besuchen, Klaus…
Dazu hatte ich dir damals zwar schon einen Kommentar geschrieben, den ich aber aus gegebenem Anlass noch wie folgt ergänzen will…
Von einem Kurs würde ich wie bereits gesagt nicht allzuviel erwarten. Ein dickes VBA-Buch könnte da mehr bringen, aber 800-1000 Ss für ca 60€ Neupreis…? Falls ich mich zu so einer Anschaffung entschließen wollte, würde ich das nur im Buchladen tun und vorher nachsehen, was zu ganz bestimmten Themen drin steht. Fürchte nämlich, dass das iW nur die (etwas aufgemotzten) Grundlagen nebst Bspp wären und man sich dort ansonsten auf die (neuen) Objekte und ihre Möglichkeiten, ggf verbunden mit allen möglichen Eingriffen per API, konzentriert, quasi Programmierers Lieblingskinder. Grund für meine Befürchtung ist das Fehlen umfangreicher Darstellungen der Datenfeldtypen und ihrer Auswirkungen/Behandlung in For Each-Schleifen im w³. Bin kürzlich 10-15 Suchergebnisseiten durchgegangen und habe überall dasselbe triviale Zeug gefunden. Also kannst du auf Weiterführendes nur per Trial'n'Error-Methode kommen, es sei denn, die dicken Bücher enthalten das. Darauf findet sich aber wie gesagt im deutschsprachigen (und bei ausgewählten Cracks des englischsprachigen) w³ kein Hinweis, weder bei den „3 Musketieren“ (HKS) noch bei „Lady Winter“, trotz ihrer Autorenschaft dicker Bücher!
Gruß Luc :-?

Anzeige
Danke für den Hinweis! mT. :-)
26.02.2013 14:00:26
Klaus
Hallo Luc,
recht hast du warscheinlich, und leider. Das For-Each ist ein gutes Beispiel, ich wüsste zB spontan nicht wie ich "For Each" zum Beispiel die Datenlabels in einem Kreisdiagramm anspreche ...
Als ich angefangen habe hier bei Herber.de zu posten, bekam ich auf alle meine Fragen innerhalb von Minuten antworten. Inzwischen beantworte ich mehr als dass ich Frage, also kann Trial'n'Error gar nicht SO falsch gewesen sein um zu lernen.
Grüße,
Klaus M.vdT.

Dein Bsp fktt wahrscheinl über ...
26.02.2013 15:13:45
Luc:-?
…eine Collection, Klaus,
zumindest wird Ähnliches wenigstens noch in der im w³ verfügbaren Literatur vorgeschlagen. Allerdings muss man die auch erst bilden und dazu wohl die eigene Objekte begründenden Chart-Eigenschaften abfragen, falls man die nicht direkt als primäre Auflistung verwenden kann. Aber das ist wohl noch Standard.
Bei Datenfeldern sieht's schon anders aus. Such mal ein Bsp für For Each x In Array(Array(1, 10, 100), Array(2, 20, 200))! Wie oft wird die Schleife da wohl durchlaufen → 2, 3 oder 6×? ;-)
…Und es gibt noch deutlich „Verrückteres“!
Luc :-?

Anzeige
AW: Dein Bsp fktt wahrscheinl über ...
27.02.2013 14:56:27
Klaus
For Each x In Array(Array(1, 10, 100), Array(2, 20, 200))
Mit den Arrays hab ich ja so meine Probleme ... ich hätt jetzt aber gesagt, das X läuft 9-mal durch. Die Kombinationen müssten dann sein 1-2, 1-20, 1-200, 10-2, 10-20, 10-200, 100-2, 100-20, 100-200.
Oder nicht?
Grüße,
Klaus M.vdT.

Oder nicht!
27.02.2013 17:19:17
Erich
Hi Klaus,
neun Durchläufe sind es nicht - es ist ja auch nur ein Array mit zwei Elementen (zwei Arrays).
Das lässt sich ganz leicht ausprobieren: 

Sub aaaaa()
Dim zz As Long, x
For Each x In Array(Array(1, 10, 100), Array(2, 20, 200))
zz = zz + 1
Cells(zz, 1) = zz
Cells(zz, 2) = Application.Sum(x)
Next x
End Sub
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

Interessant ist auch, ...
27.02.2013 18:49:34
Luc:-?
…Erich & Klaus,
dass ein derartiges Datenfeld auch automatisch so geordnet wird, dass es in einen Zellbereich ausgebbar ist, obwohl es sich um einen Vektor aus Vektoren handelt, dessen einzelne Elemente bekanntlich anders (analog zu J[ava]Script) identifiziert wdn. Das klappt auch bei Erzeugung mittels Split. Will man das, aus welchen Gründen auch immer, anders haben, muss man den PrimärVektor elementweise mit den sekundären Vektoren füllen.
Gruß Luc :-?

Also doch, habe ich erst jetzt gesehen, ...
27.02.2013 18:41:43
Luc:-?
…Klaus!
Und damit zusätzlich zu Erichs AW der versprochene Link (weiter unten wird's da interessanter…). ;-)
Gruß Luc :-?

zur Kentniss genommen!
28.02.2013 08:00:57
Klaus
Hallo Luc,
ich habe den Link geöffnet und gebookmarkt, muss aber die Zeit finden ihn zu lesen. Da ich schon mit 1-Dimensionalen Arrays so meine Schwierigkeiten habe ist das für mich "harte Kost" :-)
Danke für die Fortbildungsmaßnahme!
Grüße,
Klaus M.vdT.

Bitte sehr, gern geschehen! ;-) Gruß owT
28.02.2013 13:35:47
Luc:-?
:-?

AW: VBA gestutzter Mittelwert
25.02.2013 17:29:47
Dieter
ich dachte, wenn die "geschweifte" Klammer gesetzt ist, ist es immer eine Matrixfunktion?
Gruss Dieter

So ist es, nur kann man diese weder bei ...
25.02.2013 18:14:03
Luc:-?
…benannten Fmln noch in VBA-Anweisungen verwenden, Dieter,
was bei Ersteren auch nicht erforderlich ist und bei Letzteren durch Eintrag in .FormulaArray erledigt wdn kann, wenn es um den vba-gestützten Eintrag einer Fml ins TabBlatt geht. In allen anderen VBA-Fällen siehe meine Beiträge oben.
Gruß Luc :-?

Matrix, Matrixfunktion, Matrixformel
26.02.2013 12:46:11
Erich
Hi Klaus,
manchmal ist es wichtig, genau zu unterscheiden. Und die Kommunikation fällt dann auch leichter.
"wenn die "geschweifte" Klammer gesetzt ist, ist es immer eine Matrixfunktion?"
stimmt so nicht.
Eine Formel der Art
{=A1:B2*27} ist eine Matrixformel. Sie wird ohne die {}-Klammern, dafür mit STRG+SHIFT+RETURN eingegeben.
Das Ergebnis kann sich über eine oder mehrere Zellen erstrecken.
vgl http://www.online-excel.de/excel/singsel.php?f=26
Die Funktion SUMMENPRODUKT(), z. B. in dem Ausdruck SUMMENPRODUKT(A1:A5;B2:B6) ist eine Matrixfunktion.
vgl. http://www.online-excel.de/excel/singsel.php?f=53
Eine Matrix kann auch innerhalb einer Funktion auftreten, z. B.
=SVERWEIS(12;{0."a";9."b";13."C"};2;FALSCH)
Die {} müssen dabei von Hand eingegeben werden.
vgl. http://www.online-excel.de/excel/singsel.php?f=12
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

Wenn wir schon bei genauen Bezeichnungen sind, ...
26.02.2013 13:12:39
Luc:-?
…Erich & Dieter,
sollte auch gesagt wdn, dass Letzteres in der xlHilfe unter dem Begriff Matrixkonstante zu finden ist. Mit einer solchen kann man bspw den ODER-Vgl vereinfachen → =ODER(A1={0."a";9."b";13."C"}). Die Fkt ODER prüft dann A1 auf alle in der MxKonst angegebenen Werte (Nachzulesen bei „Lady Winter“ ;-] → s.ob).
Ansonsten danke für die Präzisierung, Erich. Die Begriffsvermengung/-verwechslung von Fml und Fkt tritt in Foren so häufig auf, dass es nur noch selten auffällt.
Gruß Luc :-?

AW: Matrix, Matrixfunktion, Matrixformel
26.02.2013 14:50:04
Klaus
Hallo Erich,
du hast natürlich komplett recht. Ich bin ja auch selbst ein Verfechter der genauen Semantik und beuge mein Haupt in Schande, dass ich sie hier nicht eingehalten habe.
Grüße,
Klaus M.vdT.

Du hast das (ursprgl) verbockt? ;-) owT
26.02.2013 15:01:03
Luc:-?
:-?

Links zu Excel-Dialogen

Label

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige