Microsoft Excel

Herbers Excel/VBA-Archiv

Matrixformel in VBA umsetzen | Herbers Excel-Forum


Betrifft: Matrixformel in VBA umsetzen von: Dirk der Xte
Geschrieben am: 03.02.2012 14:20:18

Hallo,

habe da ein Problem mit einer Matrixformel, die ich gerne in VBA abbilden möchte.
Die Formel errechnet mir den Mittelwert ohne 0.

{=MITTELWERT(WENN(C3:E3>0;C3:E3))}

über den Makrorekorder sieht es dann so aus

Selection.FormulaArray = "=AVERAGE(IF(RC[-3]:RC[-1]>0,RC[-3]:RC[-1]))"
Mein Vorhaben, ich möchte die Spalten und die Zeile variabel halten, ohne festen Bezug!

Kann mir bitte jemand dabei helfen?

Danke
Gruß Dirk

  

Betrifft: Der Bezug ist hier bereits variabel (bzw. relativ) von: NoNet
Geschrieben am: 03.02.2012 14:54:28

Hallo Dirk,

ich möchte die Spalten und die Zeile variabel halten, ohne festen Bezug!

Das ist schön - und Dein Makrorekorder kann anscheinend auch Deine Gedanken lesen - zumindest ist der Bezug in der aufgezeichneten Funktion bereits 'variabel' (bzw. : RELATIV).

Mit RC[-3] wird z.B. immer die Spalte referenziert, die sich 3 Spalten LINKS neben der Zielspalte befindet :
Führst Du den Code also z.B. aus während Zelle F5 aktiviert ist, dann bezieht sich die Funktion auf den Bereich C5:E5, wurde zuvor die Zelle H20 aktiviert, dann bezieht sich diese Funktion auf E20:G20 etc. Ist vor der Ausführung des Codes K10:L20 markiert, dann bezieht sich der Code für jede Zelle im markierten Bereich jeweils auf den Bereich 3 Spalten weiter links bis 1 Spalte weiter links (RC[-3]:RC[-1])

Schau dir auch mal folgenden (etwas älteren aber nach wie vor aktuellen) Beitrag von mir an :

http://www.spotlight-wissen.de/archiv/message/231967.html

Gruß, NoNet


  

Betrifft: die Werte in [-3] : [-1] als Variable eintragen von: Dirk der Xte
Geschrieben am: 03.02.2012 15:16:39

Hallo NoNet,

Danke für die ausführliche Beschreibung und den verlinkten Beitrag.

Ich möchte jedoch die Werte [-3] bzw.[-1] variabel angeben. Diese Werte sind nicht fix, sondern sollen ermittelt werden:

Beispiel? "=AVERAGE(IF(RC[-varBeginn]:RC[-varEnde]>0,RC[-varBeginn]:RC[-varEnde]))"
Geht so etwas?

Danke
Gruß Dirk


  

Betrifft: AW: die Werte in [-3] : [-1] als Variable eintragen von: NoNet
Geschrieben am: 03.02.2012 15:33:57

Hallo Dirk,

klaro geht das, denn diese Funktion wird hier ja in Form eines STRINGS angegeben, der sich "beliebig" zusammenbasteln lässt, z.B. so :

    Dim varBeginn As Variant, varEnde As Variant
    varBeginn = -3
    varEnde = -1
    Selection.FormulaArray = "=AVERAGE(IF(RC[" & varBeginn & "]:RC[" & _
        varEnde & "]>0,RC[" & varBeginn & "]:RC[" & varEnde & "]))"
Gruß, NoNet


  

Betrifft: Formel nach unten kopieren, aber wie? von: Dirk der Xte
Geschrieben am: 03.02.2012 16:17:05

Hallo NoNet,

vielen Dank, klappt wunderbar :-)
Wenn Du mir jetzt noch erklären könntest, wie ich die "Formel" aus Zeile 3 z.B. bis/nach Zeile 10 kopieren kann (per VBA natürlich) dann wäre mein Wochenende gerettet :-)

Danke!
Gruß Dirk


  

Betrifft: Lösung gefunden ... Danke NoNet :-) von: Dirk der Xte
Geschrieben am: 03.02.2012 16:27:11

Hallo NoNet,

der CODE sieht jetzt so aus und funktioniert auch wie gewollt!

Vielen Dank für Deine Unterstützung :-)

Ein schönes Wochenende wünscht
Dirk

Option Explicit

Sub MittelwertohneNull()

Dim varSpalte As Long
Dim varzähler As Variant
Dim zeile As Long, i As Long

varSpalte = Cells(2, Columns.Count).End(xlToLeft).Column
varzähler = -varSpalte + 1
i = 3

For zeile = i To Cells(Rows.Count, 1).End(xlUp).Row
    Cells(i, varSpalte + 1).FormulaArray = _
    "=AVERAGE(IF(RC[" & varzähler & "]:RC[-1]>0,RC[" & varzähler & "]:RC[-1]))"
    i = i + 1
Next zeile
End Sub



  

Betrifft: Gerne - Das geht auch OHNE Schleife von: NoNet
Geschrieben am: 03.02.2012 16:35:12

Hallo Dirk,

schön, dass es nun passt.

Du kannst übrigens auch auf die FOR...NEXT-Schleife verzichten, indem Du die Funktion direkt in den entsprechenden Bereich einträgst :

    Range(Cells(3, varspalte + 1), _
        Cells(Cells(Rows.Count, 1).End(xlUp).Row, varspalte + 1)).FormulaArray = _
        "=AVERAGE(IF(RC[" & varzähler & "]:RC[-1]>0,RC[" & varzähler & "]:RC[-1]))"

Schönes WE, NoNet


  

Betrifft: ohne Schleife wird die Formel nicht angepasst von: Dirk der Xte
Geschrieben am: 06.02.2012 08:27:27

Guten Morgen NoNet,

die Variante ohne Schleife funktioniert bei mir leider nicht, jetzt steht das Ergebnis aus Zeile 3 in allen andere Zellen darunter auch! Warum?

Mach ich was falsch?

Wäre nett, wenn du mir das noch mal erklären könntest, Danke!

Gruß Dirk


  

Betrifft: ohne Schleife funktioniert es nicht, Frage offen von: Dirk der Xte
Geschrieben am: 07.02.2012 08:18:26

Guten Morgen,

könnte mir bitte jemand sagen, warum die Variante von NoNet (bei mir) nicht funktioniert, Danke.

Gruß Dirk


Beiträge aus den Excel-Beispielen zum Thema "Matrixformel in VBA umsetzen"