Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema InputBox
BildScreenshot zu InputBox InputBox-Seite mit Beispielarbeitsmappe aufrufen

WENN Prüfungsbedingung als Zellverweis

Betrifft: WENN Prüfungsbedingung als Zellverweis von: Chris
Geschrieben am: 11.11.2014 16:08:21

Hallo Leute,
Ich habe folgende zwei Probleme

Problem Nr.1
ich möchte gerne eine WENN Excel Funktion bauen, in der die Prüfungsbedingung aus einer anderen Zelle "geholt" wird. In dieser Zelle soll man dann im nachhinein die Prüfungsbedingung nach Gusto verändern können.

Meine Formel würde ohne Zellverweise so aussehen:

=WENN(UND(2<='OpRisk Reference Data'!H2;'OpRisk Reference Data'!H2<4);1;0)

Zur Verdeutlichung hier meine bisherige Formel

=WENN(UND(('Mapping Tab'!J49)OpRisk Reference Data'!H2;OpRisk Reference Data'!H2('Mapping Tab'!K49)); 1;0)

Die Prüfbedingung 2<= und <4 sind also in einer anderen Zelle und sollen in dieser bei Bedarf verändert werden können.

Dieses Problem würde ich auch gerne in VBA lösen. Hier muss ich aber ebenfalls die gleichen Befehle wie in der Funktionsleite in Excel benutzen, oder?

Hier meine bisheriger Code

 Sub xy()
Range("J2").Formula= "=IF(AND(2<='OpRisk Reference Data'!H2;'OpRisk Reference Data'!H2<4);1;0)"
End Sub
Problem Nr. 2

Ich möchte, dass diese Formel mit Autofill (Laufe zeilenverweise) bis in die letzte befüllte Zeile angewendet wird.
Ich glaube dies kann ich nur mit einem makro lösen, oder?

Hier mein gescheiterter Versuch
Sub xy ()
Range("J2:$J").Formula = "=IF(AND(2<='OpRisk Reference Data'!H2;'OpRisk Reference Data'!H2<4);1; _
0)"
End Sub


Ich hoffe ihr könnt mir weiterhelfen!
Danke und beste Grüße
Chris

  

Betrifft: AW: WENN Prüfungsbedingung als Zellverweis von: Michael
Geschrieben am: 12.11.2014 09:09:22

Hallo Chris!

Mir ist nicht ganz klar, wie die Struktur Deiner Tabelle(n) aussieht, aber ich versuche es einmal basierend darauf, was ich glaube verstanden zu haben:

In die erste Zelle Deiner Spalte J (bei Dir J2 glaube ich) kommt die Formel:

=Wenn(und($A$1<='Vergleichszelle';'Vergleichszelle'<$B$1);1;0)

In diesem Bsp. stehen A1 und B1 für die zwei Zellen, in welchen Du die Prüfbedingung ändern willst (das Kleiner-Gleich ändert sich dabei nicht, sondern der Wert davor!). Vergleichszelle meint die Zellen auf die Du bisher verweist (OpRisk...). Schreibst Du also in A1 "5" und B1 "10" überprüft die o.a. Formel ob 5<= der Vergleichszelle UND die Vergleichszelle <10 ist.

Diese Formel kannst Du dann mit Doppelklick auf die untere rechte Ecke der Zelle bis ans Ende der Spalte weiterkopieren - die Zellen der Prüfbedingung habe ich im Bsp. als absolute Bezüge angenommen.

Makro erachte ich hier noch nicht als notwendig. Gib Bescheid, ob ich Dich richtig verstanden habe.

Beste Grüße
Michael


  

Betrifft: AW: WENN Prüfungsbedingung als Zellverweis von: Chris
Geschrieben am: 12.11.2014 10:04:33

Hi Michael,
danke für deine Antwort. Die Struktur ist aber so, dass ich aus meiner Referenztabelle (OpRisk) meine zu prüfenden Wert bekomme, also den Wert gegen den ich meine Bedingungen teste.

Die Prüfbedingungen entnehme ich aus einer Mapping Tabelle (Mapping Tab) in der ich diese Werte gerne nach Gusto abändern möchte. Nun also die Frage wie du das mit diesem Befehl Vergleichzelle hinbekommst? Und ob ich neben dem eigentlichen Wert in der Vergleichszelle auch den Operator < oder > mitverändern/angeben kann.

Besten Dank
Chris


  

Betrifft: AW: WENN Prüfungsbedingung als Zellverweis von: Michael
Geschrieben am: 12.11.2014 11:28:55

Hallo Chris!

Also ich kenn mich noch nicht so ganz aus mit Deiner Anforderung.

Bisher verstanden habe ich:

Du möchtest die Prüfbedingung der Wenn-Formel insofern dynamisiert haben, als dass sich die zu prüfenden Vorzeichen und Werte ändern können/sollen.

Da frage ich mich...

1) Hast Du 3 Tabellen in einer Arbeitsmappe oder 3 Tabellen aus unterschiedlichen Arbeitsmappen?

2) Wo wird die Prüfungsbedingung (zb "<= 5") überhaupt festgehalten bzw. wo soll das passieren - wenn es dafür in der Arbeitsmappe keinen Platz gibt, brauchen wir VBA, sonst klappt das evtl. auch mit einer lokalen Formel.


  

Betrifft: AW: WENN Prüfungsbedingung als Zellverweis von: Chris
Geschrieben am: 12.11.2014 11:43:45

Hi Michael,

zu deiner ersten Frage:
Ich habe für diese Formel 2 verschiedene Tabellenblätter ein einer Arbeitsmappe. Eine Mapping Tabelle in der ich per Drop Down Menü meine Prüfungswerte mit Vorzeichen einstellen kann. Eine Tabelle (OpRisk Referenz Tabelle) in der der Wert steht gegen den die Prüfungswerte angewendet werden sollen und wo in einer Extra Spalte die Ergebnisse dieser Formel stehen sollen.

zu deiner zweiten Frage:
Ich meine VBA zu brauchen, da ich die Referenztabelle erst am Anfang neu importiere und dann in einer extra Spalte die WENN Funktion für jede Zeile anwende. Sie WENN Funktion soll sich dann quasi automatisch in die extra Spalte einfügen bis zur letzten befüllten Zeile.

Gruß
Chris


  

Betrifft: AW: WENN Prüfungsbedingung als Zellverweis von: Michael
Geschrieben am: 12.11.2014 12:47:05

Hallo Chris!

Okay, ich glaub ich hab's langsam. Hier mal ein Code-Beispiel, als Idee, wie ich an das Problem herangehen würde. Vielleicht hilft es Dir (eine Rückmeldung freut mich dann natürlich).

Um das u.a. Makro zu testen habe ich in einer Tabelle in i1:i10 beliebe Zahlen stehen. Das Makro durchläuft diesen Bereich und schreibt 1 oder 0 in j1:j10. Schau's Dir an, Kommentare sind dabei. Statt den User-Eingaben sind natürlich auch Feldwert möglich:

Sub Vergleiche()

    Dim RefListe As Range 'Jene Spalte in der die zu vergleichenden Daten stehen
    Dim RefWert As Object 'Einzelne Zellen in der RefListe
    Dim VorzeichenOption As Integer 'Vorzeichenkombination wird abgefragt
    Dim PrüfWert1 As Integer 'Erster Prüfwert
    Dim PrüfWert2 As Integer 'Zweiter Prüfwert
     
    Set RefListe = Tabelle2.Range(Cells(1, 9), Cells(1, 9).End(xlDown)) 'bei mir Spalte I in  _
Tabelle OpRisk
    
    'Hier nur als Beispiel - das müsste auf die tatsächlichen Kombinationen _
    erweitert werden:
    'Wie sollen die beiden Vorzeichen im Vergleich vorkommen? User-Eingabe
    VorzeichenOption = InputBox("Wie soll geprüft werden?" _
    & vbCrLf & vbCrLf _
    & "1 = kleiner X und größer Y" & vbCrLf _
    & "2 = kleiner X und größer-gleich Y" & vbCrLf _
    & "3 = kleiner X und kleiner Y" & vbCrLf _
    & "4 = kleiner X und kleiner-gleich Y" _
    & vbCrLf & vbCrLf _
    & "Werte für X und Y werden im nächsten Schritt gewählt!", "Vorzeichenwahl", 1)
    
    'Wert nach dem ersten Vorzeichen, User-Eingabe - kann natürlich auch _
    ein Zellwert sein.
    PrüfWert1 = InputBox("Ersten Prüfwert eingeben...", "Prüfwert 1 eingeben")
    'Wert nach dem zweiten Vorzeichen, User-Eingabe, dito
    PrüfWert2 = InputBox("Zweiten Prüfwert eingeben...", "Prüfwert 2 eingeben")
    
    'Je nach gewählter Vorzeichenkombination verzweigt das Programm in _
    unterschiedliche Berechnungen
    Select Case VorzeichenOption
        'Als Vorzeichen wurde "kleiner X und größer Y" gewählt
        Case Is = 1
            'Geht jede Zelle in der Vergleichsliste durch
            For Each RefWert In RefListe
                'Wenn der Zellwert kleiner Prüfwert1 und größer Prüfwert 2 ist, dann
                If RefWert.Value < PrüfWert1 And RefWert.Value > PrüfWert2 Then
                    'wird in der Zelle rechts daneben eine "1" eingetragen
                    RefWert.Offset(0, 1).Value = 1
                    'sonst eine "0"
                    Else: RefWert.Offset(0, 1).Value = 0
                End If
            Next
        'Als Vorzeichen wurde "kleiner X und größer-gleich Y" gewählt
        Case Is = 2
            For Each RefWert In RefListe
                If RefWert.Value < PrüfWert1 And RefWert.Value >= PrüfWert2 Then
                    RefWert.Offset(0, 1).Value = 1
                    Else: RefWert.Offset(0, 1).Value = 0
                End If
            Next
        ' Und so weiter...
        Case Is = 3
            For Each RefWert In RefListe
                If RefWert.Value < PrüfWert1 And RefWert.Value < PrüfWert2 Then
                    RefWert.Offset(0, 1).Value = 1
                    Else: RefWert.Offset(0, 1).Value = 0
                End If
            Next
        Case Is = 4
            For Each RefWert In RefListe
                If RefWert.Value < PrüfWert1 And RefWert.Value <= PrüfWert2 Then
                    RefWert.Offset(0, 1).Value = 1
                    Else: RefWert.Offset(0, 1).Value = 0
                End If
            Next
    End Select

End Sub
LG
Michael


  

Betrifft: AW: WENN Prüfungsbedingung als Zellverweis von: Chris
Geschrieben am: 12.11.2014 19:08:30

Hi Michael,

vielen Dank für die ausführliche Lösung! Ich habe mich nun aber doch entschlossen es ohne Referenzzahlen zu machen und werde nun in der Mappingtabelle mit einfach nur mit Zahlen arbeiten und die Operatoren gleich in die Formel packen. Die Schwellwerte in der Mappingtabelle sind sowieso ganze Zahlen dh ich brauch nicht zwischen größer =' und 'größer' zu unterscheiden...

Ich hätte trotzdem gedacht, dass Excel komplette Operatoren als Zellverweis beachten kann :/

Trotzdem danke für deine Mühen und einen schönen Abend dir!
Chris