WENNFEHLER mit INDIREKT in bedingter Formatierung

[Andreas Killer]

Hallo,

ich hab da ein kurioses Verhalten bei einem Tool welches dazu dient die Unterschiede in 2 CSV-Dateien mit einer bedingten Formatierung zu visualisieren. Vereinfachtes Beispiel:

Diese beiden CSV-Dateien werden (von einem ERP) in Excel geöffnet:

  220001001.csv (Größe: 34 Bytes / Downloads: 8)

  220001002.csv (Größe: 41 Bytes / Downloads: 8)

Das Tool ist eine 3te Exceldatei mit mind. 2 Blättern. In dieser Datei läßt man nun dieses Makro laufen:

Code:

Option Explicit

Dim DataL As Range, DataR As Range

#Const Version = 1

Private Function BaseRef(ByVal R As Range) As String
  Dim Ref As String
  Ref = R.Address(0, 0, External:=True)
  BaseRef = Left$(Ref, InStrRev(Ref, "!"))
End Function

Sub Main()
  Set DataL = Workbooks("220001001.csv").Sheets(1).Range("A1").CurrentRegion
  Set DataR = Workbooks("220001002.csv").Sheets(1).Range("A1").CurrentRegion

#If Version = 1 Then
  Const DefFormula = "=INDIREKT(""[LRef]""&ADRESSE(ZEILE();SPALTE()))<>INDIREKT(""[RRef]""&ADRESSE(VERGLEICH(INDIREKT(""[LRefB]""&ZEILE());INDIREKT(""[RRefB]"");0);SPALTE()))"
#End If
#If Version = 2 Then
  Const DefFormula = "=WENNFEHLER(INDIREKT(""[LRef]""&ADRESSE(ZEILE();SPALTE()))<>INDIREKT(""[RRef]""&ADRESSE(VERGLEICH(INDIREKT(""[LRefB]""&ZEILE());INDIREKT(""[RRefB]"");0);SPALTE()));1)"
#End If
 
  Dim LRef As String, RRef As String, Formula As String
  Dim RRefB As String, LRefB As String
 
  Dim FC As FormatCondition
  Dim Data As Variant 'Range
  Dim i As Integer
  For Each Data In Array(DataL, DataR)
    Formula = DefFormula
    If i = 0 Then
      LRef = BaseRef(DataL)
      LRefB = LRef & "B"
      RRef = BaseRef(DataR)
      RRefB = RRef & "B:B"
    Else
      LRef = BaseRef(DataR)
      LRefB = LRef & "B"
      RRef = BaseRef(DataL)
      RRefB = RRef & "B:B"
    End If
    Formula = Replace(Formula, "[LRef]", LRef)
    Formula = Replace(Formula, "[RRef]", RRef)
    Formula = Replace(Formula, "[RRefB]", RRefB)
    Formula = Replace(Formula, "[LRefB]", LRefB)
 
    With ThisWorkbook.Sheets(i + 1).Range("A1")
      .CurrentRegion.Clear
      .Resize(Data.Rows.Count, Data.Columns.Count).Formula2Local = Formula
    End With
   
    With Data.FormatConditions
      .Delete
      Set FC = .Add(xlExpression, Formula1:=Formula)
      With FC.Interior
        .Pattern = xlSolid
        .ThemeColor = xlThemeColorAccent2
        .TintAndShade = 0.6
      End With
    End With
    i = i + 1
  Next
End Sub

Man kann nun wunderbar die Unterschiede in den beiden Stücklisten sehen, die Teilenummer in Spalte B ist der Schlüssel.

Das Problem ist das die Teile die in der anderen CSV nicht vorkommen nicht eingefärbt werden. Wenn man sich die Formeln im Tool anschaut, dann sieht man das in den Fällen die Zellen ein #NV enthalten. Also war die Idee die Formel in der bedingten Formatierung in ein WENNFEHLER einzuschließen und für diese Fälle eine 1 zurückzugeben.

Tja, das funktioniert leider nicht, jetzt werden generell alle Zeilen eingefärbt. Kann man sehen wenn man  #Const Version = 2 setzt.

Warum hat das WENNFEHLER in der bedingten Formatierung nicht den gewünschten Effekt? Kann das an den dynamic arrays liegen? 
Wenn ja, wo in der Formel müssten überall @ rein damit es funktioniert?

Andreas.

[slowboarder]

Hi
eine Bedingte Formatierung wird aktiv, wenn die Formel für die jeweilige Zelle eine WAHR oder eine Zahl ungleich 0 ergibt.

mit deiner Formel bekommst du immer eine Zahl ungleich 0, entweder die Zeilennummer der Fundstelle das Ergebnis des Vergleich, oder die 1 aus dem Wennfehler.

die richtige Formel, um Zellen zu färben, die nicht in der anderen Liste drin sind, kannst du verwenden:

=ISTFEHLER(VERGLEICH(Suchwert,Suchspalte;0))

oder:

=ZÄHLENWENNS(Suchspalte;Suchwert)=0

Gruß Daniel

[Ralf A]

Hi Andreas,

anbei mal ein anderer Ansatz.

Dateien via PQ eingelesen und die bedingte Formatierung im Sheet mit den meisten Datensätzen auf unterschiedliche Werte beider Tabellen verglichen.


  Vergleich.xlsm (Größe: 34,42 KB / Downloads: 4)

[Andreas Killer]

Hallo Ralf,

das ERP öffnet beide Dateien beim Export selber und der User möchte auf möglichst einfache Weise die Unterschiede "sehen" (im ersten Schritt).
BTW, dafür gibt es in der UI eine schöne Sache: "Ansicht\Nebeneinander anzeigen" und nun scrollt Excel sogar synchron durch die Daten.

Daher ist Einlesen in eine Datei (oder andere Workarounds) keine Option.

Andreas.

---

mit deiner Formel bekommst du immer eine Zahl ungleich 0, entweder die Zeilennummer der Fundstelle das Ergebnis des Vergleich, oder die 1 aus dem Wennfehler.

=ISTFEHLER(VERGLEICH(Suchwert,Suchspalte;0))

Hallo Daniel,

nee, da kommt schon an vielen Stellen ein FALSCH, sonst würde es ohne WENNFEHLER ja gar nicht funktionieren. Ich denke ich habe die Ursache gefunden, die Formel in der bedingten Formatierung wird ja auf einen Bereich angewendet und das wird scheinbar in der Engine wie ein dynamisches Array gehandhabt.

Also ich verwende ja
=ADRESSE(ZEILE();SPALTE())
und wenn die BF das auf einen Bereich anwendet, dann wird daraus
=ADRESSE(ZEILE(A1:C4);SPALTE(A1:C4))

Und somit gibt dann auch das INDIREKT nicht mehr einen Wert sondern ein Array zurück. Das WENNFEHLER prüft nun das komplette Array ob es irgendwo einen Fehler gibt und gibt dann eine 1 (oder ein komplettes Array mit 1sen? Keine Ahnung wie Formeln das handhaben) zurück. Und somit werden alle Zellen eingefärbt.

Die Lösung ist der implizite Schnittmengenoperator @, der muss vor jedes INDIREKT was ein Array zurückgeben könnte, also jedes das ein &ADRESSE(ZEILE();SPALTE()) dran hat.

Code:

#If Version = 3 Then
  Const DefFormula = "=WENNFEHLER(@INDIREKT(""[LRef]""&ADRESSE(ZEILE();SPALTE()))<>@INDIREKT(""[RRef]""&ADRESSE(VERGLEICH(@INDIREKT(""[LRefB]""&ZEILE());INDIREKT(""[RRefB]"");0);SPALTE()));1)"
#End If

Damit reduzieren wir jedes Array auf einen einzelnen Wert und nun bekommen wir nur an den Stellen wo VERGLEICH fehlschlägt via WENNFEHLER eine 1.

Aber eine 2te BF nur mit dem VERGLEICH und ISTFEHLER ist eine schöne Idee, danke dafür. Aber auch das braucht ein @ an der richtigen Stelle um zu funktionieren.

Code:

Const DefFormula2 = "=ISTNV(VERGLEICH(@INDIREKT(""[LRefB]""&ZEILE());INDIREKT(""[RRefB]"");0))"

Andreas.

[Ralf A]

Hallo Ralf,

das ERP öffnet beide Dateien beim Export selber und der User möchte auf möglichst einfache Weise die Unterschiede "sehen" (im ersten Schritt).
BTW, dafür gibt es in der UI eine schöne Sache: "Ansicht\Nebeneinander anzeigen" und nun scrollt Excel sogar synchron durch die Daten.

Daher ist Einlesen in eine Datei (oder andere Workarounds) keine Option.

Andreas.

---

...dann hab ich das:

Das Tool ist eine 3te Exceldatei mit mind. 2 Blättern. In dieser Datei läßt man nun dieses Makro laufen:

wohl falsch verstanden...

[ws-53]

Wenn ich Unterschiede sehen wollte, dann würde ich, zumindest optional, die Möglichkeit haben wollen, nur die Unterschiede zu sehen. Das wäre mir wesentlich angenehmer, als womöglich durch eine lange Liste scrollen zu müssen.

[slowboarder]

Hi Andreas
vielleicht kann man die Matrixproblematik von ZEILE() und SPALTE() vermeiden, wenn man das INDIREKT mit Z1S1-Schreibweise verwendet.

die mit ADRESSE(ZEILE();SPALTE()) ermittelte Zelle ist ja die selbe Zelle, in welcher sich die Formel befindet und das lässt sich mit Z1S1 auch mit einem einfachen Text darstellen, dh Z(0)S(0) oder in der Kurzschreibweise ZS.

dh  Indirekt(Adresse(Zeile();Spalte())) kann durch Indirekt("ZS";0) oder Indirekt("Z(0)S(0)";Falsch) ersetzt werden und man hat keine Matrixbildenden Funktionen mehr in der Formel.

Gruß Daniel

[Andreas Killer]

dh  Indirekt(Adresse(Zeile();Spalte())) kann durch Indirekt("ZS";0) oder Indirekt("Z(0)S(0)";Falsch) ersetzt werden und man hat keine Matrixbildenden Funktionen mehr in der Formel.

Hallo Daniel,

klingt interessant, aber wie soll das mit dem VERGLEICH-Part gehen?

Also wenn ich den Code mit Version 1 laufen lasse und mir die Formel im Direktfenster ankucke, dann bekomme ich

Code:

=INDIREKT("'[220001001.csv]220001001'!"&ADRESSE(ZEILE();SPALTE()))<>INDIREKT("'[220001002.csv]220001002'!"&ADRESSE(VERGLEICH(INDIREKT("'[220001001.csv]220001001'!B"&ZEILE());INDIREKT("'[220001002.csv]220001002'!B:B");0);SPALTE()))

Der erste Teil vor dem <> ist klar, aber danach???

Code:

=INDIREKT("'[220001001.csv]220001001'!ZS")<>INDIREKT("'[220001002.csv]220001002'!"&ADRESSE(VERGLEICH(INDIREKT("'[220001001.csv]220001001'!B"&ZEILE());INDIREKT("'[220001002.csv]220001002'!B:B");0);SPALTE()))

Naja, bei Licht betrachtet müsste man ja auch nicht überall INDIREKT verwenden, nur der Part der sich in der anderen Datei befinden muss mit INDIREKT geholt werden. UNd das auch nur weil die BF das halt nicht erlaubt.

Bei diesem Formelgedöns krieg ich immer einen Knoten im Kopf...

Andreas.

[Ralf A]

...warum verwendest Du denn noch Indirekt? Auftraggeber hat noch uralt Excel?
Ansonsten, schon mal mit Switch oder XVerweis gearbeitet?

Gutes Tutorial zum Thema