3 Formeln

[excel_rookie]

Hi,

ich bin neu hier im Forum. Ich benutze Excel seit vielen Jahren und bin vor circa 1 Jahr von Windows auf mac umgestiegen. Danach musste ich leider alle Macros neu programmieren und anpassen.

Ich hab kein Problem in Excel. Von einem Bekannten habe ich 3 Excel Formeln bekommen. Diese funktionieren einwandfrei und tun genau das, was Sie sollen. Er hat aber keine Lust mir die Formeln per Mail zu erklären, weil er meint , dass ist zu kompliziert. Allerdings würde ich die Formeln gerne verstehen. Vielleicht kann mir einer von euch cracks dabei helfen. Es ist noch wichtig zu verstehen, dass die Formeln jeweils auf ein ganzes (eigenes) Sheet geschrieben werden in jedes Feld und dann berechnet werden.

1. Formel:
"=IF(Sheet1!R[1]C="""","""",IF(AVERAGE(Sheet1!R[1]C:R[130]C)=0,"""",Sheet1!R[130]C/AVERAGE(Sheet1!R[1]C:R[130]C)))"

Ok die IF Formel verstehe ich schon mal, im deutschen Excel könnte mann auch WENN() verwenden.
Die erste Bedingung verstehe ich allerdings schon nicht mehr. Was heisst: Sheet1!R[1]C="""" ? Wann ist diese Bedingung erfüllt und wann nicht? 

Zweiter Teil: Wenn die Bedingung erfüllt ist, dann: """"
-> Wahrscheinlich soll er hier garnichts machen.

Dritter Teil, ELSE, hier ist ein zweite IF Formel verschachtelt. Und dann noch eine Average Formel (deutsche Übersetzung: Mittelwert?).
Wann ist diese Bedingung (AVERAGE(Sheet1!R[1]C:R[130]C)=0) erfüllt?
Was soll er dann machen ("""") -> wahrscheinlich nichts.
Else/Alternativ soll er (Sheet1!R[130]C/AVERAGE(Sheet1!R[1]C:R[130]C)) rechnen. Was macht diese Funktion?

Die zweite Formel ist leider nicht einfacher:

2. Formel:
=IF(Sheet2!RC="""","""",100/(COUNTIF(Sheet2!RC2:RC" & bytZahl & ","">0"")-1)*(COUNTIF(Sheet2!RC2:RC" & bytZahl & ","">0"")-RANK(Sheet2!RC,Sheet2!RC2:RC" & bytZahl & ")))"

OK, hier gibt es auch wieder eine IF Formel. Es taucht die Variable bytZahl auf, welche vorher ein Zahlenwert erhalten hat. Der Rest der Formel ist für mich vollkommen nicht nachvollziehbar. Ich verstehe diese ganzen RC und RC2 und R Funktionen nicht. Ich hab auch probiert, das nachzulesen. Allerdings ohne es zu verstehen.

Die dritte Formel ist nicht so komplex:
3. Formel:
=IF(Sheet3!RC="""","""",AVERAGE(Sheet3!RC:R[29]C))

Hier gibt es auch wieder eine IF Formel. Und es wird ein Mittelwert der letzten 29 Werte gebildet, wenn es richtig verstehe. Was RC:R[]C genau macht, ist mir aber schleierhaft.

Wenn ihr mir helfen könntet, wäre ich sehr dankbar. Vielleicht könnt ihr auch im Team was erklären. Jeder ein bisschen.

Danke.
excel_rookie

[Der Steuerfuzzi]

"=IF(Sheet1!R[1]C="""","""",IF(AVERAGE(Sheet1!R[1]C:R[130]C)=0,"""",Sheet1!R[130]C/AVERAGE(Sheet1!R[1]C:R[130]C)))"
=IF(Sheet2!RC="""","""",100/(COUNTIF(Sheet2!RC2:RC" & bytZahl & ","">0"")-1)*(COUNTIF(Sheet2!RC2:RC" & bytZahl & ","">0"")-RANK(Sheet2!RC,Sheet2!RC2:RC" & bytZahl & ")))"
=IF(Sheet3!RC="""","""",AVERAGE(Sheet3!RC:R[29]C))

Hier werden anscheinend Formeln in Tabellenblätter geschrieben.

Schau mal hier: http://www.online-excel.de/excel/singsel_vba.php?f=67

Füge das ganze mal in ein Makro ein und lasse die Formeln in eine Zelle schreiben und schau Dir diese in der Tabelle an:

Code:

Sub FormlenInTabelle()
    dim bytZahl as Byte
    bytZahl = 3
    Sheets(1).Range("A1") = "=IF(Sheet1!R[1]C="""","""",IF(AVERAGE(Sheet1!R[1]C:R[130]C)=0,"""",Sheet1!R[130]C/AVERAGE(Sheet1!R[1]C:R[130]C)))"
    Sheets(1).Range("A2") = "=IF(Sheet2!RC="""","""",100/(COUNTIF(Sheet2!RC2:RC" & bytZahl & ","">0"")-1)*(COUNTIF(Sheet2!RC2:RC" & bytZahl & ","">0"")-RANK(Sheet2!RC,Sheet2!RC2:RC" & bytZahl & ")))"
    Sheets(1).Range("A3") = "=IF(Sheet3!RC="""","""",AVERAGE(Sheet3!RC:R[29]C))"
End Sub

[excel_rookie]

Danke!

Ja genau! Die Formeln werden mit einem Excel VBA Makro in die Tabellen Zeilen auf einem Sheet geschrieben. Und dann mit "Calculate" berechnet. Das Excel VBA verstehe ich komplett, bzw. habe es selbst geschrieben. Ich verstehe nur die verwendete Excel Formel nicht. Was die Formeln ausführend machen, weis ich auch so ungefähr. Ich verstehe nur die Syntax, den eigentlichen Befehl, die Bedingungen der IF-Formeln nicht vollständig.

Grüße

[Der Steuerfuzzi]

Hier mal die Fomeln aus der Tabelle:

Code:

=WENN(Sheet1!A2="";"";WENN(MITTELWERT(Sheet1!A2:A131)=0;"";Sheet1!A131/MITTELWERT(Sheet1!A2:A131)))
=WENN(Sheet2!A2="";"";100/(ZÄHLENWENN(Sheet2!$B2:$C2;">0")-1)*(ZÄHLENWENN(Sheet2!$B2:$C2;">0")-RANG(Sheet2!A2;Sheet2!$B2:$C2)))
=WENN(Sheet3!A3="";"";MITTELWERT(Sheet3!A3:A32))

Ist das ganze jetzt etwas besser lesbar?

Die Einzelnen Formelbestandteile sollten einigermaßen selbsterklärend sein. Wenn nicht, kannst Du jederzeit bei Microsoft oder im Internet nachsehen, was die entsprechende Formel macht.

[Rabe]

Hi,

Du hast doch dann die Formeln in einer verständlichen Schreibweise in der Tabelle stehen.

Dann gehst Du mit dem Cursor in die Formel rein auf die einzelnen Befehle und drückst in der Bearbeitungsleiste auf das kleine fx. Dann siehst Du die Parameter und die Ergebnisse. Wenn Du dann noch auf den Hilfebutton drückst, geht die (eigentlich verständliche) Online-Hilfe auf.


=WENN(Sheet1!A2="";"";WENN(MITTELWERT(Sheet1!A2:A131)=0;"";Sheet1!A131/MITTELWERT(Sheet1!A2:A131)))

Wenn Tabelle1 Zelle A2 = leer ist, dann leer, sonst: Wenn der Mittelwert von Tabelle1 Zelle A2 bis A131 eine 0 ergibt, dann leer, sonst: Nimm Tabelle1 die Zelle A131 und teile sie durch diesen Mittelwert.

[excel_rookie]

Hi,

ihr habt vollkommen recht. Ich weis nicht, warum ich nicht selbst darauf gekommen bin. Im Makro werden die Formeln, nach dem Sie berechnet worden, nochmal mit dem berechneten Wert überschrieben "Werte einfügen", sodass ich nie eine Formel gesehen habe. Wenn ich das aber probehalber auskommentiere, dann kann ich natürlich die Formel ansehen. Danke, hätte ich auch selbst darauf kommen können.

Grüße und Danke.

[excel_rookie]

Vielleicht kann mir jemand von euch trotzdem noch helfen die Formel im VBA nach zu voll ziehen:
=IF(Sheet3!RC="""","""",AVERAGE(Sheet3!RC:R[29]C))
wird zu
=WENN(Sheet3!A3="";"";MITTELWERT(Sheet3!A3:A32))

Das heisst:
Beim (Sheet3!RC="""") wird aus dem (!RC) das aktuelle Feld (Spalte und Zeilennummer), in das die Formel eingefügt wird. Richtig? Und aus dem (Sheet3!RC:R[29]C)) wird aus dem (!RC) wieder das aktuelle Feld, und aus dem (:R[29]C) wird das Feld in 29 Felder (Spalte und Zeilennummer+29) vom aktuellen Feld ausgehend eingetragen? und hier könnte man auch mit minus arbeiten, also (:R[-29]C) würde das Feld, welches 29 Felder vor dem aktuellen Feld liegt ausgewählt (Spalte und Zeilenummer-29). Richtig?

Danke für eure Hilfe.

[Rabe]

Hi,

wie schon geschrieben:

Du hast doch die Formeln in einer verständlichen Schreibweise in der Tabelle stehen.

Dann gehst Du mit dem Cursor in die Formel rein auf die einzelnen Befehle und drückst in der Bearbeitungsleiste auf das kleine fx.

Und das hast DU also nicht gemacht! :@
Sonst wüßtest Du, das WENN(A3="";"" bedeutet:
Wenn A3 leer ist, dann soll die Zelle, in der die Formel steht ebenfalls leer sein.

Wenn Du dann noch nicht nur auf dem WENN stehen bleibst, sondern auf das Mittelwert gehst, wird Dir die Mittelwertformel erklärt/gezeigt.

Das ! ist das Trennzeichen zwischen Blattname und Zelle.

R = Row = Zeile
C = Column = Spalte
=>
RC:R29C ist die Festlegung des Bereiches, auf die der Mittelwert angewendet wird. In diesem Fall eben von A3 bis A3 + 29 = A32.

[Der Steuerfuzzi]

Beim (Sheet3!RC="""") wird aus dem (!RC) das aktuelle Feld (Spalte und Zeilennummer), in das die Formel eingefügt wird. Richtig? Und aus dem (Sheet3!RC:R[29]C)) wird aus dem (!RC) wieder das aktuelle Feld, und aus dem (:R[29]C) wird das Feld in 29 Felder (Spalte und Zeilennummer+29) vom aktuellen Feld ausgehend eingetragen? und hier könnte man auch mit minus arbeiten, also (:R[-29]C) würde das Feld, welches 29 Felder vor dem aktuellen Feld liegt ausgewählt (Spalte und Zeilenummer-29). Richtig?

Wie Du bereits selbst gesehen hast:
-R für Row und C für Column
-Keine Zahl bedeutet, dass der Bezug zur gleichen Adresse hergestellt wird, in der die Formel steht.
-Steht eine Zahl dahinter sind es feste Bezüge
-steht die Zahl in eckigen Klammern, sind es relative Bezüge mit dem entsprechenden Versatz.

Zu Deiner Frage, ob negative Werte auch gehen: Warum probierst Du es nicht einfach selbst aus?

[excel_rookie]

Danke euch allen. Ihr seid super! Auf dem Mac sehen die Tooltips und Hilfestellung alle auch etwas anders aus. Bis vor wenigen Monaten gab es nicht mal einen gescheiten VBA Editor in der 2016 Version. Ich hab aber jetzt alles soweit verstanden und kann es nachvollziehen. Danke.