So vergleichen Sie zwei Spalten auf verschiedenen Blättern. Vergleichen von Daten in Excel auf verschiedenen Blättern

Lassen Sie es zwei Wertetabellen geben, die die gleiche Zusammensetzung und die gleichen Spaltentypen haben. Es ist erforderlich, diese Tabellen zu vergleichen, um die Unterschiede festzustellen. zwischen ihnen verfügbar.

Wenn wir die Bedingungen des Problems anhand der häufigsten Umstände betrachten, werden wir außerdem Folgendes feststellen:

1. Die unterschiedliche Reihenfolge derselben Zeilen in zwei Tabellen führt nicht dazu, dass sich die Tabellen unterscheiden (bei Problemen, bei denen die Reihenfolge der Zeilen wichtig ist, können Sie jederzeit eine Spalte mit der Zeilennummer hinzufügen, um deren Neuanordnung zu erkennen);
2. In einer Tabelle dürfen keine zwei identischen Zeilen vorhanden sein (und wenn dies der Fall ist, können Sie jederzeit eine Faltung über alle Spalten durchführen und dabei identische Zeilen in der hinzugefügten Spalte zählen – dies vereinfacht die Interpretation der Vergleichsergebnisse).
3. Tabellen werden verglichen, indem die Werte ihrer Elemente oder Referenzen direkt verglichen werden. Wenn Tabellenelemente Sammlungen enthalten, werden nur Verweise auf die Sammlungen verglichen, ohne dass versucht wird, festzustellen, ob ihre Inhalte gleich sind.

Die zweite Verfeinerung führt automatisch dazu, dass die Tabelle immer eine oder mehrere Spalten hat, deren Wert (Wertekombination) eindeutig ist und als Zeilenidentifikator dienen kann. Eine solche Spalte (Satz von Spalten) kann als Schlüssel bezeichnet werden: einfach im Fall einer Spalte oder zusammengesetzt im Fall einer komplexeren Spalte. Noch besser ist es, analog zu Registern die genannten Spalten als Tabellendimensionen und die übrigen als Ressourcen zu bezeichnen.

Durch die Auswahl von Dimensionsspalten können Sie beim Vergleich von Tabellen nicht nur feststellen, ob eine Zeile gelöscht oder hinzugefügt wurde, sondern auch, ob eine Zeile geändert wird, wenn sich Ressourcen im selben Dimensionssatz geändert haben.

Beim Vergleich von Wertetabellen aus der Bilanz des Rohstoff- und Betriebsstoffkontos sind die Dimensionen beispielsweise die Spalten, die den Artikel und das Lager enthalten, und die Ressourcen sind die Salden und Umsätze des Kontos. Und beim Vergleich der tabellarischen Teile „Produkte“ sind die Dimensionen Nomenklatur, Merkmale und Serien, und die Ressourcen sind alle anderen Details dieses tabellarischen Teils. Und wenn man dann die Versionen der tabellarischen Teile vergleicht, kann man sagen, dass diese oder jene Nomenklatur entfernt oder hinzugefügt und diese und jenes geändert wurde.

Bei der Problemstellung legen wir auch die Form der Darstellung der Vergleichsergebnisse fest. Dies ist die anfälligste Lösung für Kritik. Denn davon hängt das Ergebnis des Methodenwettbewerbs ab. Eine Form mag für eine Methode praktisch sein, eine zweite für eine andere, eine dritte für eine dritte, aber Übung hilft aufgrund der Vielfalt der Aufgaben und Situationen nicht bei der Antwort.

Nach langem Zögern wurde die folgende Entscheidung getroffen: Das Ergebnis des Vergleichs zweier Tabellen Tabelle0 und Tabelle1 sollte eine „Differenz“-Tabelle mit derselben Struktur wie die verglichenen Tabellen sein. „Differenz“ sollte unterschiedliche Zeilen zweier Tabellen enthalten (gelöscht, hinzugefügt, geändert). In diesem Fall sollte in der zusätzlichen Spalte „Sign“ eine Markierung stehen: 0 – wenn sich die Zeile in Tabelle0 befindet, und 1 – wenn sich die Zeile in Tabelle1 befindet. Dies kann als 0 – Zeile gelöscht, 1 – hinzugefügt oder 0 – Zeile vor der Änderung, 1 – danach interpretiert werden. Außerdem (Achtung!) müssen Zeilen mit gleichen Messwerten untereinander liegen, was eine bequeme Möglichkeit zur visuellen Kontrolle bietet, die Zeilen vor und nach der Änderung zu „verknüpfen“.

Wenn Sie beispielsweise die Tabelle „7. Klasse“ mit der Tabelle „8. Klasse“ mit der vorgeschlagenen Methode vergleichen, sollten Sie eine Tabelle „Differenz“ erhalten.

Nun, noch eine letzte Sache. Nicht so oft, aber es gibt immer noch Fälle, in denen bereits nach Schlüsselfeldern geordnete Tabellen verglichen werden. Fügen wir diese Bedingung zum Problem hinzu, um die Menge der getesteten Algorithmen um eine speziell auf diesen Fall zugeschnittene Methode zu erweitern.

2.Bewertungskriterien und Testmethoden

Das wichtigste Bewertungskriterium ist natürlich die Wahl des Vergleichszeitpunkts. Ein zusätzliches Kriterium kann die Einfachheit der Vergleichsfunktion sein. Die Vergleichsausführungszeit kann mit einer speziell dafür geschaffenen Verarbeitung gemessen werden. Es wird vorgeschlagen, die Einfachheit der Funktionen subjektiv zu beurteilen.

Die zum Testen erstellte Verarbeitung generiert eine Wertetabelle mit einer angegebenen Anzahl von Zeilen und Spalten und einer angegebenen Anzahl von Dimensionen. Der Datentyp der Elemente wird aus einer begrenzten Liste primitiver Typen ausgewählt: Zeichenfolge, Zahl und Datum. Außerdem kann die Länge des Werts angegeben werden. Die Tabellenelementwerte werden zufällig generiert. Durch Änderung der ersten Tabelle entsteht die zweite. Die Anzahl der Änderungen wird als Prozentsatz der Anzahl der Zeilen in der ersten Tabelle durch drei verschiedene Indikatoren angegeben: den Prozentsatz der Löschungen, Änderungen und Ergänzungen. Außerdem wird die Anzahl der Wiederholungen angegeben, um die durchschnittliche Laufzeit der Methode zu ermitteln. Alle getesteten Methoden werden nacheinander auf denselben Testtabellen ausgeführt. Die beim Testen verwendete Verarbeitung ist dieser Veröffentlichung beigefügt, damit die Ergebnisse auf anderen Geräten und in einer anderen Softwareumgebung erneut überprüft werden können.

3. Kurze Beschreibung der verglichenen Methoden

Für die detaillierte Prüfung wurden insgesamt sieben verschiedene Methoden ausgewählt:

3.1. Reduzieren und sortieren

Der Kern der Methode besteht darin, Tabellen zu kombinieren, indem in einer Schleife eine Zeile aus der ersten Tabelle zur zweiten hinzugefügt wird. Anschließend wird eine zusätzliche Spalte „Konto“ für die nachträgliche Zählung identischer Zeilen hinzugefügt. Die Berechnung erfolgt durch Faltung über alle Spalten. Dadurch werden gleiche und unterschiedliche Zeilen in der ersten und zweiten Tabelle ermittelt. Die Zeilen, die einzeln in der kombinierten Tabelle erscheinen, werden in eine Differenztabelle umgeschrieben, die dann nach Dimension sortiert wird, sodass die Zeilen vor und nach den Änderungen nebeneinander liegen. Hier ist der Code für diese Funktion

Funktion DifferenceTableValues(Table0, Table1, Dimensions) Export AllColumns = ""; Für jede Spalte aus Table0.Columns Cycle AllColumns = AllColumns + ", " + Column.Name EndCycle; AllColumns = Avg(AllColumns, 2); Table = Table1.Copy(); Table.Columns.Add("Sign", New TypeDescription("Number")); Table.FillValues(1, "Sign"); Für jede Zeile aus Table0 Loop FillPropertyValues(Table.Add(), Row) EndLoop; Table.Columns.Add("Konto"); Table.FillValues(1, "Konto"); Table.Collapse(AllColumns, "Sign, Account"); Antwort = Table.Copy(New Structure("Account", 1), AllColumns + ", Sign"); Answer.Sort(Dimensionen); Return Reply EndFunction

3.2 Trick, Falten und Sortieren

Diese Funktion stellt eine geringfügige Modifikation der vorherigen Funktion dar, da die Addition der ersten Tabelle zur zweiten nicht in Zeilen, sondern in Spalten erfolgt. Dies beschleunigt unter bestimmten Bedingungen den Tabellenverknüpfungsvorgang

Funktion DifferenceTableValues(Table0, Table1, Dimensions) Export AllColumns = ""; Für jede Spalte aus Table0.Columns Cycle AllColumns = AllColumns + ", " + Column.Name EndCycle; AllColumns = Avg(AllColumns, 2); Table = Table1.Copy(); Table.Columns.Add("Sign", New TypeDescription("Number")); Table.FillValues(1, "Sign"); Für å = 1 By Table0.Quantity() Cycle Table.Insert(0) EndCycle; Für å = 0 By Table0.Columns.Quantity() - 1 Cycle Table.LoadColumn(Table0.UnloadColumn(å), å) EndCycle; Table.Columns.Add("Konto"); Table.FillValues(1, "Konto"); Table.Collapse(AllColumns, "Sign, Account"); Antwort = Table.Copy(New Structure("Account", 1), AllColumns + ", Sign"); Answer.Sort(Dimensionen); Return Reply EndFunction

3.3. Mit Index verknüpfen

Diese Funktion basiert auf einer einfachen und klaren Idee. Die Schleife durchläuft die Zeilen der ersten Tabelle. Für jede Zeile wird versucht, mithilfe der FindRows-Methode die Zeile in der zweiten Tabelle zu finden, die ihr anhand des Dimensionswerts entspricht. Anschließend werden die Ressourcen der gefundenen Zeilen auf Unstimmigkeiten verglichen, die gefundene Zeile in der zweiten Tabelle wird mit Null markiert, um dann in der ersten Tabelle die nicht markierten „Eins“-Zeilen als fehlend auszuwählen. Damit die FindRows-Methode schnell funktioniert, wird für die zweite Tabelle ein Index über den gesamten Satz von Dimensionen erstellt.

Function DifferenceTableValues(Table0, Table1, Dimensions) Export Selection = New Structure(Dimensions); Ressourcen = Neues Array; For ColumnIndex = 0 By Table0.Columns.Quantity() - 1 Cycle If NOT Select.Property(Table0.Columns[ColumnIndex].Name) Then Resources.Add(ColumnIndex) EndIf EndCycle; Table1.Columns.Add("Sign", New TypeDescription("Number")); Table1.FillValues(1, "Sign"); NewIndex = Table1.Indexes.Add(Dimensionen); Differenz = Table1.CopyColumns(); Für jede Zeile0 aus der Tabelle0-Schleife FillPropertyValues(Selection, Row0); Rows1 = Table1.FindRows(Select); Wenn Rows1.Quantity() = 0, dann FillPropertyValues(Difference.Add(), Row0) Else Row1 = Rows1; Für jede Ressource aus der Ressourcenschleife, wenn Row0[Ressource]<>Row1[Resource] Then FillPropertyValues(Difference.Add(), Row0); FillPropertyValues(Difference.Add(), Line1); Abort EndIfLoopEnd; Line1.Sign = 0 EndIfEndCycle; Für jede Zeile1 aus Table1.FindRows(New Structure("Sign", 1)) Loop FillPropertyValues(Difference.Add(), Row1); EndCycle; Table1.Columns.Delete("Sign"); Table1.Indices.Delete(NewIndex); Rückerstattungsdifferenz EndFunctions

3.4. Compliance-Verbindung

Diese Funktion ist algorithmisch mit der vorherigen identisch, außer dass anstelle eines regulären Index ein „selbst erstellter“ Matching-basierter Index verwendet wird. Dazu wird zunächst die zweite Tabelle durchlaufen, wodurch Referenzen auf deren Zeilen im auf Basis der Korrespondenz aufgebauten Suchbaum gespeichert werden

Function DifferenceTablesValues_(Table0, Table1, Dimensions Row) Export Table1.Columns.Add("Sign", New TypeDescription("Number")); Table1.FillValues(1, "Sign"); DimensionsStructure = New Structure(DimensionsString); Dimensionen = Neues Array; Ressourcen = Neues Array; For Index = 0 By Table0.Columns.Quantity() - 1 Loop ColumnName = Table0.Columns[Index].Name; If DimensionsStructure.Property(ColumnName) Then Dimensions.Add(Index) Else Resources.Add(Index) EndIf EndCycle; DimensionPlus = Dimensions[Dimensions.Quantity() - 1]; Dimensions.Delete(Dimensions.Quantity() - 1); HashMap = Neue Übereinstimmung; Für jede Zeile1 aus Tabelle1 Loop Root = HashMap; Für jede Dimension aus der Dimensionsschleife KeyPart = Row1[Dimension]; Branch = Root[KeyPart]; Wenn Zweig = Undefiniert, dann Zweig = Neue Übereinstimmung; Root[KeyPart] = BranchEndIf; Root = Branch EndCycle; KeyPart = Row1[DimensionPlus]; Root[PartKey] = Row1EndCycle; Dimensions.Add(DimensionPlus); Differenz = Table1.CopyColumns(); Für jede Zeile0 aus Tabelle0 Loop Root = HashMap; Für jede Dimension aus der Dimensionsschleife KeyPart = Row0[Dimension]; Branch = Root[KeyPart]; Wenn Branch = Undefiniert, dann FillPropertyValues(Difference.Add(), Row0); Abort EndIf; Root = Branch EndCycle; Wenn Zweig<>Undefiniert, dann für jede Ressource aus der Ressourcenschleife, wenn Zeile0[Ressource]<>Branch[Resource] Then FillPropertyValues(Difference.Add(), Row0); FillPropertyValues(Difference.Add(), Branch); Abort EndIfLoopEnd; Branch.Sign = 0 EndIfEndCycle; Für jede Zeile1 aus Table1.FindRows(New Structure("Sign", 1)) Loop FillPropertyValues(Difference.Add(), Row1); EndCycle; Table1.Columns.Delete("Sign"); Rückerstattungsdifferenz EndFunctions

3.5. Zusammenschluss

Diese Funktion geht davon aus, dass die verglichenen Tabellen nach Schlüsseldimensionen sortiert sind. Während des Vorgangs werden die Zeilen zweier Tabellen nacheinander gelesen und miteinander verglichen, sodass die resultierende Ausgabe eine zusammengeführte, geordnete Tabelle ohne identische Zeilen ist.

Function DifferenceTablesValues_(Table0, Table1, Dimensions Row) Export Table1.Columns.Add("Sign", New TypeDescription("Number")); Table1.FillValues(1, "Sign"); Differenz = Table1.CopyColumns(); DimensionsStructure = New Structure(DimensionsString); Dimensionen = Neues Array; Ressourcen = Neues Array; For Index = 0 By Table0.Columns.Quantity() - 1 Loop ColumnName = Table0.Columns[Index].Name; If DimensionsStructure.Property(ColumnName) Then Dimensions.Add(Index) Else Resources.Add(Index) EndIf EndCycle; Compare = Neue CompareValues; Index1 = Table0.Quantity() - 1; Index2 = Table1.Quantity() - 1; Zeile1 = Tabelle0[Index1]; Zeile2 = Tabelle1[Index2]; While True-Schleife für jede Dimension aus Dimensionsschleifen-Vergleichsergebnis = Compare.Compare(Row1[Dimension], Row2[Dimension]); Wenn Vergleichsergebnis<>0 Then Abort EndIf End Of Cycle; Wenn Vergleichsergebnis = 0, dann für jede Ressource aus der Ressourcenschleife, wenn Zeile1[Ressource]<>Line2[Resource] Then FillPropertyValues(Difference.Add(), Line1); FillPropertyValues(Difference.Add(), Line2); Abort EndIfLoopEnd; Index1 = Index1 - 1; Index2 = Index2 - 1; Wenn Min(Index1, Index2)< 0 Тогда Прервать КонецЕсли; Строка1 = Таблица0[Индекс1]; Строка2 = Таблица1[Индекс2]; ИначеЕсли РезультатСравнения >0 Then FillPropertyValues(Difference.Add(), Line1); Index1 = Index1 - 1; Wenn Index1< 0 Тогда Прервать КонецЕсли; Строка1 = Таблица0[Индекс1] Иначе ЗаполнитьЗначенияСвойств(Разница.Добавить(), Строка2); Индекс2 = Индекс2 - 1; Если Индекс2 < 0 Тогда Прервать КонецЕсли; Строка2 = Таблица1[Индекс2] КонецЕсли КонецЦикла; Пока Индекс1 >= 0 Schleife Row1 = Table0[Index1]; FillPropertyValues(Difference.Add(), Line1); Index1 = Index1 - 1 EndCycle; While Index2 >= 0 Loop Row2 = Table1[Index2]; FillPropertyValues(Difference.Add(), Line2); Index2 = Index2 - 1 EndCycle; Table1.Columns.Delete("Sign"); Rückerstattungsdifferenz EndFunctions

3.6. Anfrage - vollständige Verbindung

Die Funktion basiert auf der Übergabe zweier Tabellen an eine Abfrage, wo sie durch gleiche Werte in den Dimensionen verbunden werden. Mit der anschließenden „Entfaltung“ ist eine leichte Komplikation verbunden. in zwei Zeilen von Zeilen,unterschiedlich in den Ressourcen.

Funktion LinePart(String, Separator) ExportSeparatorPosition = Find(String, Separator); Wenn SeparatorPosition = 0, dann Antwort = Neues Array; Answer.Add(String); Andernfalls Antwort = StrPart(Avg(Row, SeparatorPosition + StrLength(Separator)), Separator); Answer.Insert(0, Avg(Row, 1, SeparatorPosition - 1)) EndIf; Return Answer EndFunction Function DifferenceTableValues(Table0, Table1, Dimensions) Export Query = New Query("SELECT | 0 AS Sign(), T.Field() | PLACE T0 | FROM | &Table0 AS T |; | |///// ///////////////////////////////////////////// // ///////////////////// |SELECT |1 AS Sign(), T.Field() |PLACE T1 |FROM | &Table1 AS T |; | |/ ////////////////////////////////////////// ///// //////////////////////// |SELECT | 0 AS SIGN |PLACE SIGNS | |CONNECT | |SELECT | 1 | ; | |//// ////////////////////////////////////// ///////// ///////////////////// |SELECT() | SELECT Signs.Sign | WHEN 0 | THEN T0.Field | ELSE T1.Field | END AS Field,( ) | Signs.Sign |FROM | T0 AS T0 | VOLLSTÄNDIGE VERBINDUNG VON T1 AS T1 | BY (TRUE) | () AND T0.Field = T1.Field(), | Signs AS Signs |WHERE | (() T0.Field IST NULL UND Signs.Sign = 1 | ODER T1.Field IST NULL UND Signs.Sign = 0 | () ODER T0.Field<>T1.Field()) | |ORDNEN NACH | ()Field"); DimensionStructure = New Structure(Dimensions); Sections = StrParts(Query.Text, "()"); Query.Text = Sections; Für jede Spalte aus Table1.Columns Cycle Query.Text = Query.Text + StrReplace(Sections, "Field", Column.Name) EndCycle; Query.Text = Query.Text + Sections; Für jede Spalte aus Table1.Columns Cycle Query.Text = Query.Text + StrReplace(Sections, "Field", Column. Name) EndCycle; Query.Text = Query.Text + Sections; Für jede Spalte aus Table1.Columns Cycle Query.Text = Query.Text + StrReplace(Sections, "Field", Column.Name) EndCycle; Query.Text = Query. Text + Abschnitte; Für jedes Element aus der Struktur des Dimensionszyklus Request.Text = Request.Text + StrReplace(Sections, "Field", Element.Key) EndCycle; Request.Text = Request.Text + Abschnitte; Request.Text = Request .Text + StrReplace(Sections, "Field", Table1.Columns.Name); Für jede Spalte aus Table1.Columns-Schleife Wenn NICHT DimensionStructure.Property(Column.Name) Dann Query.Text = Query.Text + StrReplace(Sections, " Field", Column.Name) EndIf EndCycle; Query.Text = Query.Text + Abschnitte; Query.Text = Query.Text + StrReplace(Sections, "Field", Dimensions); Query.SetParameter("Table0", Table0); Query.SetParameter("Table1", Table1); Return Request.Execute().Unload() EndFunction

3.7. Anfrage - Gruppierung

Diese Funktion basiert auf genau der gleichen Idee wie Funktion 3.1, wird jedoch innerhalb der Anfrage implementiert

Funktion DifferenceTableValues(Table0, Table1, Dimensions) Export Query = New Query("SELECT | 0 AS Sign, | T.Field | PLACE T0 | FROM | &Table0 AS T |; | |/////////// ///////////////////////////////////////////// // ///////////////// |SELECT |1 AS Sign, |T.Field |PLACE T1 |FROM | &Table1 AS T |; | |/////// // /////////////////////////////////////////// //// ////////////////// |SELECT |T.Sign, |T.Field |PLACE T |FROM |T0 AS T | |UNITE ALL | |SELECT | T. Sign, | T. Feld | VON | T1 AS T |; | |//////////////////////////// ////// ////////////////////////////////////// | SELECT | SUM(T.Sign) AS Sign, | T.Field |FROM | T AS T | |GROUP BY | T.Field | |HAVING | QUANTITY(*) = 1 | |ORDER BY | Field//"); AllColumns = ""; Für jede Spalte aus Table1.Columns Cycle AllColumns = AllColumns + ", T." + Column.Name EndCycle; Query.Text = StrReplace(Query.Text, "T.Field", Medium(AllColumns, 2)); Query.Text = StrReplace(Query.Text, "Field//", Dimensions); Query.SetParameter("Table0", Table0); Query.SetParameter("Table1", Table1); Return Request.Execute().Unload() EndFunction

Alle hier vorgestellten Funktionen wurden sorgfältig auf maximale Leistung abgestimmt. Berücksichtigung der beim gemeinsamen Tuning im Funktionsforum gewonnenen Erfahrungen für den eindimensionalen Fall. Allerdings wurde hier nicht so sorgfältig vorgegangen wie dort, so dass aus manchen Funktionen vielleicht noch etwas mehr Leistung herausgeholt werden könnte.

4. Testergebnisse

4.1 Einfluss der Zeilenanzahl

Lassen Sie uns die Abhängigkeit der Vergleichszeit von der Anzahl der Zeilen in Tabellen untersuchen. Dazu verwenden wir die folgenden Werte von Testparametern. Anzahl der Zeilen – 20.000, 40.000, 60.000, 80.000, 100.000, Anzahl der Spalten – 10, Anzahl der Schlüsselspalten – 1, Datentyp – Zeichenfolge, Zeichenfolgenlänge – 10, Prozentsatz der Löschungen, Änderungen, Ergänzungen – 5, Anzahl der Tests Wiederholungen - 2. Wir erhalten die folgende Abhängigkeit, die bequemer in Diagrammform dargestellt werden kann.

Dieser Zusammenhang ist bei den meisten Methoden nahezu linear! So soll es sein. Die Laufzeit der FindRows-Methode bei Vorhandensein eines Index hängt nicht von der Anzahl der Zeilen ab, daher wird eine Verknüpfung nach Index in linearer Zeit durchgeführt. Das Gleiche gilt für die Verwendung von Match und Merge. Bei einem vollständigen Join verwendet die Abfrage höchstwahrscheinlich einen Hash-Match, um Tabellen gleicher Größe zu verknüpfen.

Die Nichtlinearität der Sortierzeit für eine relativ kleine Anzahl verschiedener Zeilen weicht geringfügig von der direkten Beziehung für die Faltung ab. Die Methode, die das Zusammenführen durch Kopieren von Spalten verwendet, ist schlechter – diese Kopiermethode führt zusätzlich zu der leichten Nichtlinearität der Sortierung zu einer erheblichen Nichtlinearität. Dadurch geht der Vorteil des „Tricks“ der Verknüpfung von Tabellen mit mehr als 60.000 Zeilen verloren.

4.2 Auswirkung der Wertlänge

Lassen Sie uns nun die Abhängigkeit der Zeit von der Länge der Zeichenfolgenwerte untersuchen. Stellen wir die Anzahl der Zeilen auf 50000 ein. Die übrigen Parameter sind die gleichen wie in 4.1. Das Ergebnis stellen wir in Form eines Balkendiagramms dar. Es zeigt das Verhältnis der Betriebszeit verschiedener Methoden besser an und ermöglicht es Ihnen, den Spitzenreiter zu identifizieren, bei dem es sich in den meisten Fällen um die Faltungsmethode handelt.

Es ist ersichtlich, dass sich die Abhängigkeit der Zeit von der Methode praktisch nicht ändert, wenn sich die Stringlänge ändert. Lediglich die Ausführungszeit der Abfrage erhöht sich.

Um die Abfragemethoden in diesem Diagramm besser beschreiben zu können, wird die Zeit, die zum Eingeben von Tabellen in eine Abfrage benötigt wird, in separate Dimensionen unterteilt. Zu diesem Zweck wurde eine Dummy-Funktion erstellt, die nur die Eingabe von Tabellen in die Abfrage übernimmt und keine anderen Arbeiten ausführt. Die lange Zeit, die für die Tabelleneingabe benötigt wird, zeigt, dass es für die Abfragetechnologie sehr schwierig ist, mit den führenden Methoden zu konkurrieren. In vielen Fällen haben Führungskräfte ihre Arbeit bereits abgeschlossen, wenn die Originaldaten nur noch in der Anfrage enthalten sind.

4.3 Auswirkungen von Datentypen

Die nächste interessante Frage ist die Beziehung zwischen Methoden und Datentypen. Das folgende Diagramm zeigt es. Auch hier beträgt die Anzahl der Zeilen 50000, die Länge der String- und Zahlenwerte beträgt 10. Der Rest ist wie in 4.1.

Es zeigt sich, dass der Datentyp den größten Einfluss auf die Zeit von Abfragemethoden hat. Bei Zahlen funktioniert die Gruppierung besser. Und Datteln werden sehr gut verarbeitet.

4.3 Einfluss der Anzahl der Spalten

Eine weitere Abhängigkeit ist die Abhängigkeit der Vergleichszeit von der Anzahl der Spalten. Das folgende Diagramm zeigt es. Die Anzahl der Zeilen beträgt hier 50.000, der Datentyp ist eine Zeichenfolge der Länge 10, der Prozentsatz der Hinzufügungen, Verzerrungen und Löschungen beträgt 5. Eine Schlüsselspalte.

Es ist ersichtlich, dass die Anzahl der Spalten die Vergleichsgeschwindigkeit der Methoden nicht wesentlich verändert. Die größte Verlangsamung der Abfrageleistung ist die Erhöhung der Spaltenanzahl.

4.4 Einfluss der Anzahl der Messungen

Interessanter ist die unten dargestellte Abhängigkeit von der Anzahl der Schlüsselspalten. Die Anzahl der Zeilen beträgt hier 50.000, der Datentyp ist eine Zeichenfolge der Länge 10, der Prozentsatz der Hinzufügungen, Verzerrungen und Löschungen beträgt 5. Insgesamt gibt es 10 Spalten.

Es zeigt sich, dass die korrespondenzbasierte Methode, die früher gute Ergebnisse zeigte, mittlerweile ein Außenseiter ist. Auch die Fusion verschlechtert sich. Die Suche nach Index ist jedoch verbessert, da weniger Spalten verglichen werden müssen.

4.5 Auswirkungen unterschiedlicher Tischgrößen

Achten wir nun auf die Asymmetrie der Methoden 1–4 (Faltung und Verknüpfung) im Verhältnis zu den Größen der verglichenen Tabellen. Alle diese Methoden profitieren davon, dass die erste Tabelle kleiner wird! Dies wird durch die folgende Tabelle bestätigt, die die Zeit zeigt, die zum Vergleich zweier Tabellen mit 50.000 und 40.000 Zeilen in unterschiedlicher Reihenfolge benötigt wird.

Im Diagramm oben ist ein interessantes Artefakt sichtbar. Angesichts der Anzahl der Zeilen und Spalten erweist es sich als rentabler, 50.000 Zeilen in einer Schleife zu einer Tabelle mit 40.000 Zeilen hinzuzufügen, als umgekehrt. Dies kann an den Besonderheiten der Speicherzuweisung für die Wertetabelle liegen.

4.6 Auswirkung der Anzahl der Unterschiede

Und schließlich untersuchen wir die Abhängigkeit der Vergleichszeit vom Grad der Differenz zwischen den Tabellen. Es ist ersichtlich, dass sich die Faltungsbetriebszeit mit zunehmendem Prozentsatz der Abweichungen verlangsamt. Da beginnt die Nichtlinearität der Sortierung eine Rolle zu spielen.

4.7 Einfluss der Hardware- und Softwareumgebung

Die Tests wurden auf Plattform 8.3.5.1248 auf einem VGN-Z51MRG-Laptop durchgeführt. Die erhaltenen Abhängigkeiten werden im Allgemeinen von anderen Geräten bestätigt, es gibt jedoch auch einige Funktionen, die noch nicht verallgemeinert wurden.

5. Schlussfolgerungen

5.1. Die einfachste Faltungsmethode erweist sich in den meisten Fällen als die produktivste. Es sollte als verwendet werden universelle Methode, aber nicht in Sonderfällen.

5.2 Wenn die Größe klein ist (bis zu 50.000 Zeilen), können Sie die Faltung zusätzlich beschleunigen, indem Sie beim Zusammenführen von Tabellen das Kopieren von Spalten verwenden (Methode 3.2).

5.3 Im Sonderfall einer Schlüsselspalte, einer erheblichen Anzahl von Unterschieden und einem erheblichen Unterschied in den Tabellengrößen sollte ein Match-Join verwendet werden. Das Gleiche sollte auch dann gemacht werden, wenn mehrere Schlüsseltabellen vorhanden sind, der Vergleich jedoch mit derselben Tabelle erfolgt, auf die Sie sich im Voraus vorbereiten können „Entscheidungsbaum“ basierend auf Compliance,auf seine Eigenschaften abgestimmt.

5.4 Im Sonderfall mehrerer Schlüsselspalten mit erheblichen Unterschieden und nicht sortierten Tabellen, die verglichen werden, müssen Sie die Join-Methode nach Index verwenden.

5.5 Für die größtmögliche Effizienz der Methoden 1–4 müssen Sie beim Vergleich die richtige Reihenfolge der Tabellenanzeigen wählen.

5.6 Im Sonderfall sortierter Tabellen, bei denen es eine erhebliche Anzahl von Unterschieden gibt, sollte die Zusammenführung verwendet werden.

5.7. Im Sonderfall großer (je nach Ausstattung) annähernd gleich großer Tabellen, die darüber hinaus erhebliche Unterschiede aufweisen und aus kurzen Zeilen und einer äußerst geringen Anzahl von Spalten bestehen, ist der Einsatz von Abfragen möglich.

5.8 Wenn die Tabellen von numerischen Daten, Datumsangaben sowie mittleren und langen Zeichenfolgen dominiert werden, sollten Tabellenvergleichsabfragen eine Gruppierung und nur für sehr kurze Zeichenfolgen eine vollständige Verknüpfung verwenden.

6. Allgemeine Schlussfolgerungen

6.1 Bevor eine entscheidende Entscheidung getroffen wird, ist es in jedem Fall besser, wenn möglich, mehrere Methoden unter realen Einsatzbedingungen zu vergleichen. Zum Beispiel mithilfe der dem Artikel beigefügten Verarbeitung.

6.2 Die Berücksichtigung der Merkmale der Daten in den Tabellen ermöglicht eine gezielte zusätzliche Optimierung der meisten der vorgestellten Methoden. Dafür gibt es viele Möglichkeiten, die außerhalb des Rahmens der betrachteten Fragestellungen liegen.

6.3 Das Eingeben von Wertetabellen in Abfragen kann viel Zeit in Anspruch nehmen, was in den meisten Fällen die Wirksamkeit ihrer Verwendung bei Aufgaben zunichte macht, bei denen Daten aus dem Speicher und nicht aus der Datenbank entnommen werden. Die gedankenlose Verwendung von Abfragen bei dieser Aufgabe ist ein schädliches Missverständnis.

6.4 Die Laufzeit der FindRows-Methode hängt nicht von der Größe der Wertetabelle ab, wenn ein Index für die in der Auswahl enthaltenen Spalten vorhanden ist. Daher ist die korrekte Schätzung für die Leistung der Tabellenvergleichsmethode unter Verwendung einer Verknüpfung nach Index O(N).

Jeden Monat erhält der HR-Mitarbeiter eine Liste der Mitarbeiter mit deren Gehältern. Die Liste wird in ein neues Blatt in der Excel-Arbeitsmappe kopiert. Die Aufgabe lautet wie folgt: Vergleichen Sie die Gehälter der Mitarbeiter, die sich im Vergleich zum Vormonat geändert haben. Dazu müssen Sie Daten in Excel auf verschiedenen Blättern vergleichen. Lassen Sie uns die bedingte Formatierung verwenden. Auf diese Weise finden wir nicht nur automatisch alle Unterschiede in den Zellwerten, sondern heben sie auch farblich hervor.

Vergleichen Sie zwei Tabellen in Excel

Ein Unternehmen kann mehr als hundert Mitarbeiter haben, von denen einige kündigen, andere eingestellt werden, andere in Urlaub gehen oder krankgeschrieben werden usw. Daher kann es schwierig sein, Gehaltsdaten zu vergleichen. Beispielsweise werden die Nachnamen von Mitarbeitern immer in unterschiedlicher Reihenfolge angezeigt. Wie man einen Vergleich zwischen zwei anstellt Excel-Tabellen auf verschiedenen Blättern?

Die bedingte Formatierung wird uns helfen, dieses schwierige Problem zu lösen. Nehmen wir zum Beispiel die Daten für Februar und März, wie in der Abbildung dargestellt:

So finden Sie Änderungen auf Gehaltsabrechnungen:

Nach Eingabe aller Formatierungsbedingungen markiert Excel automatisch diejenigen Mitarbeiter farblich, deren Gehälter sich im Vergleich zum Vormonat geändert haben.

Das Prinzip des Vergleichs zweier Datenbereiche in Excel auf verschiedenen Blättern:

Unter bestimmten Bedingungen ist die MATCH-Funktion unerlässlich. Sein erstes Argument enthält ein Wertepaar, das im Quellblatt des nächsten Monats, also „März“, zu finden sein sollte. Ein durchsuchbarer Bereich ist als ein durch Namen definiertes Bereichswertpaar definiert. Auf diese Weise werden Zeichenfolgen anhand zweier Merkmale verglichen: Nachname und Gehalt. Für gefundene Übereinstimmungen wird eine Zahl zurückgegeben, was im Wesentlichen für Excel gilt. Daher sollten Sie die Funktion =NOT() verwenden, mit der Sie den TRUE-Wert durch FALSE ersetzen können. Andernfalls wird die Formatierung auf Zellen angewendet, deren Werte übereinstimmen. Für jedes nicht gefundene Wertepaar (d. h. eine Nichtübereinstimmung) &B2&$C2 im Bereich Nachname&Gehalt gibt die MATCH-Funktion einen Fehler zurück. Der Fehlerwert ist kein boolescher Wert. Daher verwenden wir die Funktion IFERROR, die jedem Fehler einen logischen Wert zuweist – TRUE. Dies erleichtert die Zuweisung eines neuen Formats nur für Zellen ohne übereinstimmende Gehaltswerte in Bezug auf den nächsten Monat – März.

Die Möglichkeit, zwei Datensätze in Excel zu vergleichen, ist häufig für Personen nützlich, die große Datenmengen verarbeiten und mit riesigen Tabellen arbeiten. Durch den Vergleich kann beispielsweise festgestellt werden, ob Daten korrekt eingegeben wurden oder ob Daten rechtzeitig in eine Tabelle eingegeben wurden. Der folgende Artikel beschreibt verschiedene Techniken zum Vergleichen zweier Datenspalten in Excel.

Verwenden der bedingten IF-Anweisung

Die Methode zur Verwendung des bedingten IF-Operators unterscheidet sich darin, dass zum Vergleichen zweier Spalten nur der zum Vergleich erforderliche Teil und nicht das gesamte Array verwendet wird. Nachfolgend finden Sie die Schritte zur Implementierung diese Methode:

Platzieren Sie beide Vergleichsspalten in den Spalten A und B des Arbeitsblatts.

Geben Sie in Zelle C2 die folgende Formel ein =IF(ISERROR(MATCH(A2,$B$2:$B$11,0));"";A2) und erweitern Sie sie auf Zelle C11. Diese Formel sucht nacheinander nach dem Vorhandensein jedes Elements aus Spalte A in Spalte B und gibt den Wert des Elements zurück, wenn es in Spalte B gefunden wurde.

Verwenden der SVERWEIS-Ersetzungsformel

Das Funktionsprinzip der Formel ähnelt der vorherigen Methode, der Unterschied liegt in der Suche anstelle von SUCHE. Besonderheit Diese Methode ermöglicht auch den Vergleich zweier horizontaler Arrays mithilfe der HPR-Formel.

Um zwei Spalten mit Daten in den Spalten A und B zu vergleichen (wie bei der vorherigen Methode), geben Sie die folgende Formel =VLOOKUP(A2;$B$2:$B$11;1;0) in Zelle C2 ein und erweitern Sie sie auf Zelle C11.

Diese Formel untersucht jedes Element aus dem Hauptarray im verglichenen Array und gibt seinen Wert zurück, wenn es in Spalte B gefunden wurde. Andernfalls gibt Excel einen #N/A-Fehler zurück.

Verwenden eines VBA-Makros

Durch die Verwendung von Makros zum Vergleich zweier Spalten wird der Prozess vereinheitlicht und die Zeit für die Datenvorbereitung verkürzt. Die Entscheidung, welches Vergleichsergebnis angezeigt werden soll, hängt ganz von Ihrer Vorstellungskraft und Ihren Makrofähigkeiten ab. Nachfolgend finden Sie eine auf der offiziellen Microsoft-Website veröffentlichte Methodik.

In diesem Code wird der Variable CompareRange der Bereich mit dem zu vergleichenden Array zugewiesen. Anschließend wird eine Schleife ausgeführt, die jedes Element im ausgewählten Bereich untersucht und es mit jedem Element im Vergleichsbereich vergleicht. Werden Elemente mit gleichen Werten gefunden, schreibt das Makro den Wert des Elements in Spalte C.

Um das Makro zu verwenden, kehren Sie zum Arbeitsblatt zurück, wählen Sie den Hauptbereich aus (in unserem Fall sind dies die Zellen A1:A11) und drücken Sie die Tastenkombination Alt+F8. Wählen Sie im angezeigten Dialogfeld das Makro aus Finden_Streichhölzer und klicken Sie auf die Schaltfläche „Ausführen“.

Nach der Ausführung des Makros sollte das Ergebnis wie folgt aussehen:

Verwenden des Inquire-Add-Ins

Endeffekt

Deshalb haben wir uns verschiedene Möglichkeiten zum Vergleichen von Daten in Excel angesehen, die Ihnen bei der Lösung einiger analytischer Probleme helfen und das Auffinden sich wiederholender (oder eindeutiger) Werte erleichtern.

In Form von Tabellen dargestellte Informationen lassen sich viel bequemer analysieren und in verschiedenen Berechnungen verwenden. Wenn jedoch Daten aus mehreren ähnlichen Tabellen verglichen werden müssen, ist dies alles visuell nur sehr schwierig durchzuführen. Geeignet Software kann in einer solchen Situation immer weiterhelfen, und als nächstes schauen wir uns an, wie man zwei Tabellen in Excel mit unterschiedlichen Analysemethoden vergleicht.

Leider ist es nicht möglich, Tabellen in Excel mit nur einem Mausklick zu vergleichen. Darüber hinaus müssen Sie möglicherweise die Daten auf irgendeine Weise aufbereiten und eine Vergleichsformel schreiben.

Je nach gewünschtem Ergebnis wird eine Methode zum Vergleich von Daten aus Tabellen ausgewählt. Am einfachsten ist es, beides auf den ersten Blick zu vergleichen identische Spalten um Linien zu identifizieren, in denen dieser Unterschied noch besteht. Auf diese Weise können Sie sowohl numerische Werte als auch Text vergleichen.

Vergleichen wir zwei Spalten mit digitalen Werten, bei denen es nur in wenigen Zellen einen Unterschied gibt. Durch Schreiben einer einfachen Formel in die nebenstehende Spalte wird die Bedingung für die Gleichheit zweier Zellen ermittelt „=B3=C3“, wir werden das Ergebnis erhalten "WAHR", wenn der Inhalt der Zellen gleich ist, und "LÜGE", wenn die Inhalte der Zellen unterschiedlich sind. Indem Sie die Formel über die gesamte Höhe der Spalte mit den verglichenen Werten ausdehnen, ist es sehr einfach, die unterschiedliche Zelle zu finden.

Wenn Sie nur das Vorhandensein oder Nichtvorhandensein von Unterschieden in den Spalten überprüfen müssen, können Sie den Menüpunkt verwenden „Suchen und auswählen“, auf der Registerkarte "Heim". Dazu müssen Sie zunächst die zu vergleichenden Spalten auswählen und dann den gewünschten Menüpunkt auswählen. In der Dropdown-Liste müssen Sie auswählen „Wählen Sie eine Gruppe von Zellen aus...“, und wählen Sie im angezeigten Fenster aus „Unterschiede nach Zeile“.

Bedingte Formatierung von Unterschieden in geordneten Werten
Bei Bedarf können Sie eine bedingte Formatierung auf verschiedene Zellen anwenden, indem Sie die Zelle füllen, die Textfarbe ändern usw. In diesem Fall müssen Sie das Element auswählen "Bedingte Formatierung", in der Dropdown-Liste, die wir auswählen „Regelverwaltung“.

Wählen Sie im Regelmanager das Element aus "Regel erstellen", und beim Erstellen von Regeln wählen wir aus . Jetzt können wir die Formel festlegen "=$B3<>$C3", um die zu formatierende Zelle zu definieren, und legen Sie das Format dafür fest, indem Sie auf die Schaltfläche klicken "Format".

Jetzt haben wir eine Zellauswahlregel, die Formatierung wurde festgelegt und der Bereich der zu vergleichenden Zellen wurde definiert. Nach dem Drücken der Taste "OK", wird die von uns festgelegte Regel angewendet.

Unterschiede in ungeordneten Werten vergleichen und formatieren
Der Vergleich von Excel-Tabellen beschränkt sich nicht auf den Vergleich geordneter Werte. Manchmal müssen Sie Bereiche gemischter Werte vergleichen, in denen Sie feststellen müssen, ob ein Wert in einen Bereich anderer Werte passt.

Beispielsweise haben wir eine Reihe von Werten, die als zwei Spalten formatiert sind, und eine weitere Reihe von Werten desselben Typs. Im ersten Satz haben wir alle Werte von 1 bis 20, im zweiten Satz fehlen einige Werte und werden durch andere Werte dupliziert. Unsere Aufgabe besteht darin, mithilfe der bedingten Formatierung Werte im ersten Satz hervorzuheben, die nicht im zweiten Satz enthalten sind.

Die Vorgehensweise ist wie folgt: Wählen Sie den ersten Datensatz aus, den wir aufrufen „Spalte 1“, und im Menü "Bedingte Formatierung" Wählen Sie einen Artikel aus „Eine Regel erstellen…“. Wählen Sie im angezeigten Fenster aus , geben Sie die gewünschte Formel ein „=COUNTIF($C$3:$D$12,A3)=0“ und wählen Sie die Formatierungsmethode aus.

Unsere Formel verwendet die Funktion „ZÄHLENWENN“, der zählt, wie oft ein Wert aus einer bestimmten Zelle wiederholt wird „A3“ innerhalb eines vorgegebenen Bereichs „$C$3:$D$12“, das ist unsere zweite Spalte. Die Vergleichszelle muss die erste Zelle im Wertebereich sein, auf den die Formatierung angewendet wird.

Nach Anwendung der erstellten Regel werden alle Zellen mit sich nicht wiederholenden Werten in einem anderen Wertesatz mit der angegebenen Farbe hervorgehoben.

Natürlich gibt es auch komplexere Möglichkeiten, zwei Tabellen in Excel zu vergleichen, etwa den Cent-Warenvergleich in der neuen und alten Preisliste. Nehmen wir an, es gibt zwei Tabellen mit Preisen, und neben den Preisen in der neuen Tabelle müssen Sie für jedes Produkt die alten Preise angeben, und die Reihenfolge der Produkte in den Listen wird nicht eingehalten.

Neben den Preisen in der neuen Tabelle müssen Sie in der Zelle der nächsten Spalte eine Formel schreiben, die die Werte auswählt. In der Formel verwenden wir die Funktion „VPR“, das einen Wert aus jeder Spalte in der Zeile zurückgeben kann, in der die Suchbedingung erfüllt wurde. Damit die Funktion ordnungsgemäß funktioniert, ist es erforderlich, dass jede Zeile in der Spalte enthält einzigartige Werte, nach dem gesucht wird. Bei wiederholten Werten wird nur der zuerst gefundene berücksichtigt.

Die Formel, die wir brauchen, wird so aussehen: „=SVERWEIS(B18,$B$3:$C$10,2,FALSE)“. Erster Wert „B18“ entspricht der ersten Zelle des gewünschten Produktnamens. Zweite Bedeutung „$B$3:$C$10“ bedeutet die permanente Adresse des Bereichs der alten Preistabelle, deren Werte wir benötigen. Dritte Bedeutung „2“ bezeichnet die Spaltennummer aus dem ausgewählten Bereich, in deren Zelle wir den alten Preis des Produkts übernehmen. Und die letzte Bedeutung "LÜGE" Gibt eine Suche nur nach exakter Übereinstimmung von Werten an. Nachdem wir die Formel über die gesamte Spalte der neuen Tabelle gezogen haben, erhalten wir in dieser Spalte die alten Preiswerte für jeden in der neuen Tabelle verfügbaren Artikel. Gegenüber dem Namen des letzten Produkts zeigt die Formel eine Fehlermeldung an "#N / A", was auf das Fehlen dieses Namens in der alten Preisliste hinweist.

Es gibt unzählige Möglichkeiten, Tabellen in Excel zu vergleichen, und einige davon sind nur mit einem VBA-Add-In möglich.