Unterschiede zwischen zwei Excel-Tabellen hervorheben

Question

Daten aus Tabelle A haben mehrere Spalten, aber ich muss nur vergleichen:

Column A - order #
Column C - count
Column D - date

Tabelle B hat auch viele Spalten, aber ich muss vergleichen:

Column A - order #
Column B - count
Column C - date

Ich muss Spalte A in der Zeile in beiden Tabellenblättern hervorheben, wenn die Daten in der Zeile nur für diese drei Spalten identisch sind. In diesem Beispiel haben Zeile 6 und 14 unterschiedliche Daten und 7 und 15 unterschiedliche Mengen, sodass sie nicht hervorgehoben werden würden.

Answer 1

Diese Methode basiert auf einigen Hilfsspalten in Ihrer ersten Tabelle oder, wenn Sie dies bevorzugen, in einem separaten Blatt.

MATCH() gibt die Zeilennummer eines bestimmten Wertes in einem Array zurück.

INDEX() sucht diesen Wert in einem anderen angegebenen Array

Sie möchten also in Zeile 1 der Hilfsspalte (sagen wir Spalte X) Folgendes: =MATCH(A1,SheetName!A:A,0) Dadurch wird die Zeile im anderen Blatt zurückgegeben, die die Bestellnummer in A1 enthält.

Dann möchten Sie in den nächsten beiden Spalten =INDEX(SheetName!C:C,$X1) =INDEX(SheetName!D:D,$X1). Dadurch werden die Anzahl und das Datum aus dem anderen Blatt in dieser Zeile zurückgegeben.

Geben Sie dann in der letzten Spalte Folgendes =And(Y1=C1,Z1=D1)ein: . Dies gibt TRUE zurück, wenn alles gleich ist.

Verwenden Sie dann bei Bedarf die bedingte Formatierung, um Ihre Hervorhebungen vorzunehmen.

Dies alles kann in einer einzigen Formel zusammengefasst und als Bedingung für die bedingte Formatierung verwendet werden, wenn Sie möchten. Außerdem würde Vlookup hier genauso gut funktionieren wie Indexübereinstimmung. Ich habe Ihnen alles getrennt dargestellt, da ich denke, dass es einfacher ist, zu verstehen, was passiert und warum es funktioniert, aber ich empfehle Ihnen, es zur Übung einmal zu verdichten.

Ich hoffe, das hat geholfen.

Beachten Sie, dass dies nicht funktioniert, wenn die Bestellnummer in der Nachschlageliste dupliziert wird, da nur das erste Vorkommen in der Tabelle gesucht wird.

Answer 2

Sie können dies mit bedingter Formatierung tun, indem Sie „Formel verwenden, um zu bestimmen, welche Zellen formatiert werden sollen“ verwenden. Und die Kurzversion: Sie müssen die folgende Formel auf Ihre erste Tabelle anwenden, die sich auf Blatt1 befindet, und die andere auf Blatt2, beide beginnend bei A1:

=AND(MATCH(INDEX(Sheet1!$A:$A;ROW();1);Sheet2!$A:$A)>0; INDEX(Sheet1!$A:$D;ROW();3)=INDEX(Sheet2!$A:$C;MATCH(INDEX(Sheet1!$A:$A;ROW();1);Sheet2!$A:$A);2); INDEX(Sheet1!$A:$D;ROW();4)=INDEX(Sheet2!$A:$C;MATCH(INDEX(Sheet1!$A:$A;ROW();1);Sheet2!$A:$A);3))

Diese Formel geht von Folgendem aus:

• beide Tabellen befinden sich in zwei separaten Blättern, genannt Blatt1 und Blatt2
• Blatt1 enthält Ihre erste Tabelle, wobei die Spalten A, C und D mit den Spalten A, B und C Ihrer zweiten Tabelle in Blatt2 verglichen werden.
• Wählen Sie auf Blatt eins als Bereich für bedingte Formatierung $A:$A oder $A2:$AXX aus (wobei XX eine ausreichend hohe Zahl ist, um alle Werte zu enthalten) - dies ist nur dann wichtig, wenn die Farben vorhanden sind, wenn die obige Gleichung zutrifft

Wenn es nicht wie erwartet funktioniert:

• Wenn Sie mehrere gleiche Bestellnummern in der zweiten Tabelle haben, wird nur die erste Übereinstimmung gefunden und verglichen
• Bei Anwendung auf die Tabelle Sheet2 wird die korrekte Farbgebung nicht wiederhergestellt, einige Referenzwerte müssen geändert werden

Verwendete Funktionen:

• AND()- gibt nur dann „true“ zurück, wenn alle Parameter „true“ sind
• MATCH()- findet einen Wert in einem ausgewählten Bereich (in unserem Fall eine Spalte)
• INDEX()- gibt einen Zellverweis aus einer Matrix/einem Bereich zurück, indem die Zeilen- und Spaltennummern explizit angegeben werden
• ROW()- gibt ohne Parameter die aktuelle Zeilennummer zurück

Sehen wir uns nun einige Komponenten an:

• INDEX(Sheet1!$A:$A;ROW();1)Dies gibt die aktuell verglichene Zellreferenz zurück, wenn die bedingte Formatierung ausgeführt wird
• MATCH(INDEX(Sheet1!$A:$A;ROW();1);Sheet2!$A:$A)hier suchen wir nach einer Zelle im zweiten Blatt, die mit unserer aktuellen Zelle übereinstimmt. Wenn eine Übereinstimmung vorliegt, ist der zurückgegebene Wert größer als 0, andernfalls wird eine Warnung/ein Fehler ausgegeben.
• INDEX(Sheet1!$A:$D;ROW();3)– wir suchen hier nach dem Wert der 3. Spalte (alias Spalte C) der ersten Tabelle (Tabelle 1).
• INDEX(Sheet2!$A:$C;MATCH(INDEX(Sheet1!$A:$A;ROW();1);Sheet2!$A:$A);2)- Dadurch wird die zweite Spalte aus der Tabelle Sheet2 abgerufen. Wir hatten bereits die Zeile, in der die Bestellnummer steht. Diese Gleichung befindet sich in der Mitte dieser Funktion und bestimmt die Zeile.
• Der 3. AND()Parameter ist derselbe wie die beiden vorherigen Punkte, nur die Spaltennummern werden geändert.

Notiz:

• Um die richtige Zeile zu finden, verwende ich nur die Spalten: Sheet1!$A:$AundSheet2!$A:$A
• Wenn ich eine weitere Spalte brauche, muss ich die Spalte zu einer Matrix/Tabelle erweitern, deren letzte Spalte mindestens die letzte Spalte ist, auf die ich verweisen werde, im Fall von Blatt1 die vierte, alias Spalte DSheet1!$A:$D

Wenn Sie das oben Gesagte wissen, sollten Sie in der Lage sein, die Gleichung so zu ändern, dass sie auch in der Tabelle Sheet2 funktioniert.

Answer 3

Dies lässt sich gut und einfach mit der bedingten Formatierung wie folgt erledigen.

Aufstellen

Für die Beispielformel unten befinden sich die Daten in den Zellen Sheet1!A1:C3 und Sheet2!A1:C3.

Formel für bedingte Formatierung

Markieren Sie den Zellbereich aufTabelle 2, fügen Sie eine bedingte Formatierung per Formel hinzu und geben Sie diese Formel ein: =AND(Sheet1!$A1=$A1,Sheet1!$B1=$B1,Sheet1!$C1=$C1) ... und wählen Sie eine Farbe, mit der Ihre Ergebnisse hervorgehoben werden sollen.

Es ist wichtig, die Spalten und nicht die Zeilen zu sperren (d. h., die Dollarzeichen vor den Buchstaben, aber nicht die Zahlen zu platzieren).

In Bezug auf die in der Formel zur bedingten Formatierung verwendeten Referenzen sollten die Zellreferenzen die obersten Zellen der entsprechenden Datenspalten sein. Wenn also beide Spalten mit Bestellnummern mit A1 beginnen, belassen Sie es wie oben beschrieben bei A1. Wenn sich jedoch auf der ersten Registerkarte die erste zu berücksichtigende Bestellnummer in Zeile 4 befindet, auf der zweiten Registerkarte jedoch in Zeile 7, ändern Sie die Formel in AND(Sheet1!$A4=A7...) usw.

Sagen Sie mir Bescheid, wenn da etwas geklärt werden muss!