Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1588to1592
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Vergleich mit mehreren Kriterien

Vergleich mit mehreren Kriterien
02.11.2017 15:47:43
Bernd
Moin zusammen,
lange ist es gut gegangen, doch mit diesem Problem werde ich nicht fertig und hoffe auf eure Hilfe.
Ich habe zwei identisch aufgebaute Tabellen in zwei Tabellenblätter der selben Datei.
Eine Tabelle ist vom 2710 und die andere vom 0211. Ich möchte nun die Unterschiede in den beiden Tabellen in einem anderen Blatt darstellen.
Das Problem ist jedoch, dass ich verschiedene Kriterien habe, nach denen ich vergleichen muss.
Hier die Tabellen grob dargestellt:
Blatt 2710
ArtNr ArtikelText Menge Auftrag ID Datum
1 a 10 1 1 2017.10
1 a 20 2 1 2018.01
2 b 30 3 1 2017.11
2 b 25 3 1 2017.11
2 b 15 4 1 2018.01
3 c 55 5 1 2018.02
4 d 15 6 1 2018.03
Blatt 0211
ArtNr ArtikelText Menge Auftrag ID Datum
1 a 20 2 1 2018.01
2 b 15 3 1 2017.11
2 b 48 3 1 2017.12
2 b 20 4 1 2018.02
3 c 55 5 1 2018.02
4 d 15 6 1 2018.03
Es fehlt hier die erste Zeile des Blattes 2710 und in den anderen Zeilen wurden Veränderungen bei der Menge und beim Datum vorgenommen.
Der Vergleich soll sich ausschließlich auf Menge und Datum beschränken.
Verglichen werden sollen aber die Mengen je Monat, d.h. man müsste erst in beiden Tabellen die Menge je Artikel pro Monat ermitteln (das habe ich sogar hinbekommen :-) ) (im Blatt 2710 wären dann für Artikel Nummer 2 in 2017.11 schlappe 55 und in 2018.01 ganze 15). Im Blatt 0211 gibt es jetzt drei Positionen mit neuen Mengen und neuen Datumsangaben.
Nun möchte ich in der neuen Tabelle die Veränderungen kenntlich gemacht bekommen (und da bin ich gescheitert :-( ). Wäre aber für eine Komplett-Lösung auch sehr dankbar.
Wie und in welcher Form ist mir egal !
In der Hoffnung, dass ich das Problem verständlich erklärt habe, bedanke ich mich schon mal an jeden der eine Idee hat.
Bernd

15
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: so nicht wirklich eindeutig ...
02.11.2017 16:09:45
...
Hallo Bernd,
... Du solltest dann schon für Deine Beispieldaten aufzeige, wie Du Dir Dein "Ergebnis" vorstellst. Da gäbe es verschiedene Interpretationsmöglichkeiten.
Gruß Werner
.. , - ...
AW: so nicht wirklich eindeutig ...
02.11.2017 17:17:43
curry0110
Moin Werner,
habe mal meine Vorstellung in eine Datei gepackt. Ist übersichtlicher !
https://www.herber.de/bbs/user/117391.xlsx
Schon mal meinen lieben Dank !
Gruß
Bernd
AW: zumind. ein Wert ist nicht nachvollziehbar ...
02.11.2017 18:23:58
...
Hallo Bernd,
... und zwar Deine Angabe zur Mengendifferenz 13 für 2017.12.
Unabhängig davon würde ich meinen, das je Tabellenblatt in der jeweiligen Zeile die Mengendifferenz und zwar nicht nur bezogen auf den Monat, sondern auch für den jeweiligen Artikel ermittelt wird. Das wäre mE transparenter und mit einer relative einfachen Formel auch realisierbar.
Gruß Werner
.. , - ...
Anzeige
AW: zumind. ein Wert ist nicht nachvollziehbar ...
02.11.2017 22:11:38
curry0110
Moin Werner,
wie anfangs schon geschrieben: Wie die Lösung aussieht ist mir egal, Hauptsache ich bekomme die Differenzen für den Monat und die Menge. Wenn du da schon eine Lösung hast, dann nehme ich die doch !
Erklärung Mengendifferenz:
Menge Datum Menge Datum DiffMenge DiffDatum
25 2017.11 48 2017.12 +13 2017.12
Die alte Menge war 25 die neue ist 48, das macht die Differenz von 13 aus, die jetzt in 2017.12 statt in 2017.11 produziert werden.
Gruß Bernd
Rechne: 48 - 25 = Differenz 23 (owT)
03.11.2017 09:16:19
EtoPHG

AW: Rechne: 48 - 25 = Differenz 23 (owT)
03.11.2017 09:30:47
Bernd
Oh weh, eine meiner schwärzesten Stunden :-(. Wo ist das nächste tief gelegene Loch, in dem ich mich verkriechen kann..
Wenn ihr demnächst von jemanden hört der ein Drittel Gehaltserhöhung ablehnt, weil er ein Viertel möchte :-) , das war ja dann wohl ich....
Danke für die Nachhilfe
Bernd in Depression
Anzeige
AW: das dies nun geklärt ist, kann ...
03.11.2017 09:48:57
...
Hallo Bernd,
... ich Dich vielleicht mit folgenden Vorschlag wieder etwas aufhellen. Formeln nach unten kopieren:
Tabelle1

 ABCDEFGHI
1Daten von Blatt 2710 Diff. zu 0211
2ArtNrArtikelTextMengeAuftragIDDatum MengeDatum
31a10112017.10 102017.10
41a20212018.01 02018.01
52b30312017.11 182017.12
62b25312017.11 232017.12
72b15412018.01 52018.02
83c55512018.02 02018.02
94d15612018.03 02018.03
10         

Formeln der Tabelle
ZelleFormel
H3=WENN(A3="";"";WENNFEHLER(VERWEIS(9;1/(Tabelle2!A$1:A$19&Tabelle2!B$1:B$19=A3&B3)/(Tabelle2!D$1:D$19=D3); Tabelle2!C$1:C$19)-C3;C3))
I3=WENN(A3="";"";WENNFEHLER(VERWEIS(9;1/(Tabelle2!A$1:A$19&Tabelle2!B$1:B$19=A3&B3)/(Tabelle2!D$1:D$19=D3); Tabelle2!F$1:F$19); F3))

Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat
I31. / Formel ist =F3<>I3Abc


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
und analog auch in
Tabelle2

 ABCDEFGHI
1Daten von Blatt 0211 Diff. zu 2710
2ArtNrArtikelTextMengeAuftragIDDatum MengeDatum
31a20212018.01 02018.01
42b15312017.11 102017.11
52b48312017.12 -232017.11
62b20412018.02 -52018.01
73c55512018.02 02018.02
84d15612018.03 02018.03
9         

Formeln der Tabelle
ZelleFormel
H3=WENN(A3="";"";WENNFEHLER(VERWEIS(9;1/(Tabelle1!A$1:A$19&Tabelle1!B$1:B$19=A3&B3)/(Tabelle1!D$1:D$19=D3); Tabelle1!C$1:C$19)-C3;C3))
I3=WENN(A3="";"";WENNFEHLER(VERWEIS(9;1/(Tabelle1!A$1:A$19&Tabelle1!B$1:B$19=A3&B3)/(Tabelle1!D$1:D$19=D3); Tabelle1!F$1:F$19); F3))

Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat
I31. / Formel ist =F3<>I3Abc


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...
Anzeige
Mailand oder Madrid - Hauptsache Italien
03.11.2017 14:14:12
lupo1
nimmst nicht so tragisch - der Andi war trotzdem ein erfolgreicher Mann am Ball.
Oder kam das Viertel und Drittel vom Icke?
AW: Mailand oder Madrid - Hauptsache Italien
03.11.2017 14:28:03
Bernd
Moin,
der Spruch ist von Horst Szymaniak. Habe ich gerade gegoogelt :-).
Zum Thema: Ja es funktioniert, ist aber unwahrscheinlich langsam, da sich Excel über 2300 Zeilen quälen muss. Gibt es da einen Turbo ?
Aber mir ist da eine andere Idee gekommen. Die Auftrags-Nummern sind einzigartig, diese könnte ich ja für einen sverweis incl. Vergleich gebrauchen. Ich melde mich, wenn ich damit rumprobiert habe. Die Idee mit dem "Vergleiche Blatt 1 mit Blatt 2 in Blatt 1" und umgekehrt hat schon was.
Bis dahin schon mal lieben Dank
der Bernd
Anzeige
AW: nachgefragt ...
03.11.2017 15:04:02
...
Hallo Bernd,
... wie hast Du denn die Formeln an Deine Originaldaten angepasst? Die Zeilennummer von 19 auf 2300? Oder hast Du gleich den gesamten Spaltenbereich angegeben?
Natürlich haben die Formeln bei 2300 Datensätzen schon etwas zu rechnen aber wesentlich weniger als wenn Du den gesamten Spaltenbereich angeben haben solltest.
Gruß Werner
.. , - ...
AW: nachgefragt ...
03.11.2017 15:18:53
Bernd
Moin Werner,
ich habe den ganzen Spaltenbereich genommen, blöd oder ?
Aber dank deiner Idee die Blätter in den Blättern zu vergleichen, bin ich auf eine ganz einfache Lösung gekommen.
Mir ist dann mal die Idee gekommen, nicht die Artikel-Nr und den Artikel, sondern den Auftrag als fixen Bestandteil für einen sverweis zu nehmen.
Habe dann die Spalte mit den Auftragsnummern in beiden Blättern in die Spalte A kopiert. Habe danach per sverweis nach der Auftragsnummer gesucht und mir dann die Menge oder das Datum vom anderen Blatt ausgeben lassen. Danach habe ich die von dir bedingte Formatierung angewandt. Und schon hatte ich das von ein Ergebnis, auf das ich ohne deine Ideen nie gekommen wäre. Zwar fehlen mir da noch die Mengenänderungen zu meinem Glück, aber ich sehe schon einmal die Differenzen und wenn ich nicht so dusselig bin wie bei 48-25, dann könnte ich mir das auch im Kopf ausrechnen.
Vielen lieben Dank für deine Ideen und dein Hirnschmalz. Ohne die hätte ich es nie geschafft.
Alles Gute
der nun glückliche Bernd
Anzeige
AW: dazu angemerkt ...
03.11.2017 15:54:14
...
Hallo Bernd,
... aus Deine eingestellten Beispieldaten war ersichtlich, dass weder die Artikelnummern (ohne und mit Artikelbezeichnung) noch die Auftragsnummer eineindeutig sind. Demzufolge habe ich nicht auf SVERWEIS() zugegriffen, weil damit lediglich immer nur die erste Auftragsnummer gefunden wird. Meine vorgeschlagen VERWEIS() Matrixfunktion(alität)sformeln suchen dagegen eineindeutig in Abhängigkeit aller drei Werte. Diese braucht zwar in der Auswertung länger als eine SVERWEIS-Formel ermöglicht dagegen die Ermittlung aller Ergebniswerte. Und bei Reduzierung der Auswertung auf den max. notwendigen Spaltenbereich sollte die Zeit sich auch noch in vertretbaren grenzen halten.
Gruß Werner
.. , - ...
Anzeige
AW: dazu angemerkt ...
04.11.2017 10:25:49
curry0110
Moin Werner,
alles was du schreibst ist richtig. Das die Auftragsnummern eindeutig sind, habe ich erst später gemerkt. War mir nicht bekannt, da neues Aufgabengebiet.
Ich habe jetzt noch einmal deine Formeln mit Spaltenbegrenzung angewandt: Läuft deutlich schneller als ohne Begrenzung, aber auch langsamer als die Geschichte mit dem einfachen sverweis. Was ja auch klar ist.
Lieben Dank noch einmal für deine Bemühungen.
Gruß Bernd
AW: wenn dem so ist, dann ist der thread ...
04.11.2017 19:26:39
...
Hallo Bernd,
.. auch abgeschlossen und bedarf nicht mehr der von Dir gesetzten Kennzeichnung als "offen".
Für evtl. Mit- und oder Nachleser noch die Anmerkung, in Deiner Aussage "... die Auftragsnummern eindeutig sind,..." wohl aus Versehen das Wörtchen "nicht" vergessen hast, anderenfalls ergibt Deine Aussage einen nicht nachvollziehbaren Sinn.
Gruß Werner
.. , - ...
Anzeige
AW: Vergleich mit mehreren Kriterien
02.11.2017 16:18:18
Peter(silie)
Hallo,
das ist eine größere aufgabe und ohne beispiel Daten nicht umsetzbar.
Denkanstöße: Dictionarys, Arrays, Sorting, Look_Ups
Kleine Codes die helfen könnten: Public Function Array_from_Range_Fast(ByRef ThisRange As Range) As Variant() Array_from_Range_Fast = ThisRange.Value2 End Function Public Function Array_To_Dictionary(ByRef source() As Variant) As Dictionary Dim tmp As New Dictionary Dim varitem As Variant For Each varitem In source If Not tmp.Exists(varitem) Then tmp.Add varitem, vbNull Next varitem Set Array_To_Dictionary = tmp: Set tmp = Nothing End Function Public Function Keys_To_Array(ByRef dictionary_ As Dictionary) As Variant() Keys_To_Array = dictionary_.Keys End Function Public Function Items_To_Array(ByRef dictionary_ As Dictionary) As Variant() Items_To_Array = dictionary_.Items End Function Public Function Array_Length(ByRef source() As Variant) As Long Array_Length = UBound(source) - LBound(source) + 1 End Function Public Function Array_Index_Of(ByRef source() As Variant, ByVal what_ As Variant) As Long With Application If Not VBA.IsError(.Match(what_, source, 0)) Then Array_Index_Of = .Match(what_, source, 0) - 1 End If End With End Function Public Function Array_Count_Item(ByRef source() As Variant, ByVal what_ As Variant) As Long Dim i, n As Long For i = LBound(source) To UBound(source) If source(i) = what_ Then n = n + 1 Next i Array_Count_Item = n End Function Public Function Array_Contains(ByRef source() As Variant, ByVal this_ As Variant) As Boolean If Not VBA.IsError(Application.Match(this_, source, 0)) Then Array_Contains = True End Function Public Function Where_Key_Is(ByRef source As Dictionary, ByVal this_ As Variant) As Variant With Application If Not VBA.IsError(.Match(this_, source.Keys, 0)) Then Where_Key_Is = source(.Match(this_, source.Keys, 0)) End If End With End Function Public Function Count_Occurence_Of(ByVal this_ As Variant, ByVal In_This_Range As Range) As _ Long Dim n, i As Long Dim tmp As Range Set tmp = In_This_Range With Application Do If Not IsError(.Match(this_, tmp, 0)) Then n = n + 1 i = .Match(this_, tmp, 0) Set tmp = tmp.Resize(tmp.Rows.Count - i, 1).Offset(i) Else Exit Do End If Loop End With Count_Occurence_Of = n End Function Public Function Array_1D_From_Range_2D(ByRef source() As Variant, ByVal direction_ As Long) As _ Variant() Dim i, ii, n, length_ As Long Dim tmp() As Variant length_ = (UBound(source, 1) * UBound(source, 2)) - 1 ReDim tmp(length_): n = 0 If direction_ = 0 Then For i = 1 To UBound(source, 1) For ii = 1 To UBound(source, 2) tmp(n) = source(i, ii): n = n + 1 Next ii Next i Else For ii = 1 To UBound(source, 2) For i = 1 To UBound(source, 1) tmp(n) = source(i, ii): n = n + 1 Next i Next ii End If Array_1D_From_Range_2D = tmp End Function Public Sub QuicksortAscending(ByRef source() As Variant, ByVal low As Long, ByVal high As Long) Dim i As Long: i = low Dim j As Long: j = high Dim tmp As Variant Dim ref As Variant: ref = source((low + high) / 2) Do While (source(i) ref): j = j - 1: Wend If (i j) If (low
Anzeige

315 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige