VERGLEICH-Formel verschachteln?

[Bob der Baumeister]

Hallo zusammen,

meine Aufgabe und damit mein Problem ist folgendes. Vielleicht könnt ihr mir ja mit Rat zur Seite stehen.

Ich habe eine Excel Datei mit zwei Sheets. Tabelle1 enthält sämtliche angelegten Materialien. In Tabelle2 wurden die Materialbewegungen festgehalten. Beide Tabellen sind ein Export aus einer Datenbank. Ich habe dem Anhang eine Beispieldatei angefügt, die noch alle relevanten Daten enthält. Die sensiblen, vertraulichen und unrelevanten Daten habe ich jedoch entfernt. Um an der Dateigröße noch etwas zu schrauben musste die Datei etwas vereinfacht werden.

externer Link entfernt

Das Bewegungsjournal (Tabelle2) beläuft sich bislang auf 34622 Zeilen. Für die Aufgabenstellung ist diese Tabelle (gedanklich) in zwei Teile zu teilen. Das vergangene Kalenderjahr 2017 beginnt in Zeile 2 und endet in Zeile 16458 ("Matrix 1"). In Zeile 16459 beginnt dann das aktuelle Jahr und endet wie schon erwähnt in Zeile 34622 ("Matrix 2"). Zur Vereinfachung werde ich das im weiteren Verlauf Matrix 1 und Matrix 2 bezeichnen.

Der Materialliste (Tabelle1) habe ich zum besseren Verständnis eine Hilfsspalte (Spalte B) hinzugefügt. Diese Spalte existiert im Original nicht. Passieren soll nun folgendes. Es soll eine Prüfung stattfinden, ob sich die Sachnummern aus Spalte A (Tabelle1) in Spalte G (Tabelle2) finden lassen. Falls dies zutreffend sein sollte, soll die Zelle mittels der bedingten Formatierung farblich gekennzeichnet werden. Dazu habe ich diese Formel(n) angewandt.

=VERGLEICH(A5; 'Tabelle2 (Bewegungsjournal)'!$G$2:$G$16458; 0)
=VERGLEICH(B5; 'Tabelle2 (Bewegungsjournal)'!$G$16458:$G$34622; 0)


Auf die zweite Formel möchte ich verzichten. Aus zwei soll eine werden. Das heißt. 

WENN Tabelle1/A2 in Tabelle2/Spalte G in Matrix 1 UND Matrix 2 existiert, markiere nichts farblich. WENN Tabelle1/A2 in Tabelle2/Spalte G in Matrix 1 UND NICHT in Matrix 2 gefunden wird, hebe die Zelle farblich hervor.

Die erste in grün gekennzeichnete Zelle (Tabelle1/Zeile 51) wurde nur in Matrix 2 gefunden. Die Zelle darunter in Matrix 1 und Matrix 2. Und die darauf folgende Zeile wurde wiederum nur in Matrix 1 lokalisiert. Das Ergebnis soll wie Spalte A aussehen. Jedoch nur mit den oben genannten Bedingungen.

Von hier aus könnte ich ganz einfach mit dem Filter weiterarbeiten. Ich habe schon versucht, zwei VERGLEICH-Funktionen zu verschachteln. Mit WENN, INDEX und SVERWEIS gelangte ich nicht ans Ziel. Hoffentlich ist zu dessen Umsetzung kein VBA notwendig. Vielleicht besitzt Ihr den Stein der Weisen und könnt mir bei der Lösung unter die Arme greifen.

Bei Fragezeichen im Kopf zögert nicht, fragt nach.    :92:

[steve1da]

Hola,

deine bedingte Formatierung ist völlig verschoben.
Du fängst in der Formel in der bed. Formatierung in B5 an, der Bereich geht aber über B:B.
Dann musst du entweder in der Formel in B1 anfangen, oder den Bereich bei "wird angewendet auf" auf $B$5:$B$2080 ändern.


Wenn du im 2. Blatt das Jahr aus dem Datum von Spalte B ausliest, z.B. ab J2 mit =jahr(B2) ginge als Formel in der bed. Formatierung:

PHP-Code:

=(ZÄHLENWENNS('Tabelle2 (Bewegungsjournal)'!F:F;A5;'Tabelle2 (Bewegungsjournal)'!J:J;2017)>1)*(ZÄHLENWENNS('Tabelle2 (Bewegungsjournal)'!F:F;A5;'Tabelle2 (Bewegungsjournal)'!J:J;2018)=0) 


Deine markierten Beispiele ergeben alle keinen Sinn, da der Wert aus A51 überhaupt nicht in Tabelle 2 Spalte F (du sprichst auch immer von Spalte G) vorhanden ist.
Im anderen Forum sprichst du auch von einem Filter, der ist hier gar nicht vorhanden. Außerdem wäre es nett, wenn du dort sagst, wo es weiter geht.

Gruß,
steve1da

[Bob der Baumeister]

Hallo.

Zunächst einmal danke für deine Antwort. Werde das heute ausprobieren.

Möchte zu so später Stunde nur auf dein Feedback eingehen.

deine bedingte Formatierung ist völlig verschoben.
Du fängst in der Formel in der bed. Formatierung in B5 an, der Bereich geht aber über B:B.
Dann musst du entweder in der Formel in B1 anfangen, oder den Bereich bei "wird angewendet auf" auf $B$5:$B$2080 ändern.

Die ersten drei Zeilen musst Du dir weg denken. Die existieren nur in der Beispieldatei. Im Original würden die ja auch keinen Sinn machen. Im Original beginne ich folglich immer bei A2 was stets funktioniert hat. Was den Anzeigebereich unter Bedingte Formatierung > Regeln verwalten angeht, weiß ich auch nicht, wieso das so dargestellt wird. Wird bei mir immer so angezeigt (=$A:$A). Selektiert man die Regel jedoch und klickt auf Regel bearbeiten, steht dort dann die Formel wie eingegeben. Und dort steht dann =VERGLEICH(A4; 'Tabelle2 (Bewegungsjournal)'!$F$2:$F$16458; 0).

Was Spalte G (bzw. F) angeht, tut es mir Leid für die Verwirrung. Es war Spalte G. Da ich aber noch etwas Inhalt löschen musste, um die Dateigröße für den Upload zu reduzieren, wurde aus Spalte G Spalte F. Hatte ich vergessen, zu ändern.
Die Erwähnung von Zeile 51 bezog sich auf Spalte B. Zelle B51 stammt aus der Matrix Tabelle2 G16459 bis G34622. Der Wert aus Zelle A52 und B52 findet sich sowohl im Bereich G2 bis G16458 (also Jahr 2017) als auch im Bereich G16459 bis G34622 (Jahr 2018). Und A53 kommt nur im Bereich G2 bis G16458 aus Tabelle 2 vor.
Und der Filter käme erst am Ende, wenn das Werk vollbracht ist. Wenn die richtigen Zellen farblich gekennzeichnet werden, Liesen sich diese dann über den Filter > Nach Farbe filtern ausblenden und ich könnte mit den verbliebenen weiterarbeiten.

Ich hoffe, die Missverständnisse damit aufgeklärt zu haben.

[steve1da]

Hola,

Wird bei mir immer so angezeigt (=$A:$A)

dann  hast du vor dem Setzen der bed. Formatierung die komplette Spalte markiert.
In diesem Fall musst du die Formel in der bed. Formatierung auch mit A1 beginnen lassen, da sich ansonsten alles um eine Zelle verschiebt!
Belässt du die Formel so wie sie ist musst du z.B. bei "wird angewendet auf" schreiben:

Code:

=$A$2:$A$2000

Gruß,
steve1da

[Bob der Baumeister]

Hallo steve,

ich habe bei deiner Formel ein kleines Verständnisproblem. Und zwar mit J:J vor der jeweiligen Jahreszahl. Die Spalte J hat in beiden Tabellen keine Bedeutung. Bezieht sich J:J auf die Jahreszahl?

[steve1da]

Hola,

ich habe in Spalte J als Hilsspalte das Jahr ausgelesen. Steht aber auch oben ;)

Gruß,
steve1da

[Bob der Baumeister]

Ja mei.    'Tschuldigung. Habe mich so auf deine Kritik konzentriert und was ich falsch gemacht habe, dass ich das positive glatt überlesen habe.

Jetzt mal anders rum. Deine Formel funktioniert, grundsätzlich. Sie zeigt nur nicht das richtige an. Ich führe das darauf zurück, dass ich mich anfänglich missverständlich ausgedrückt habe. Ich formuliere es mal anders und ganz vereinfacht, um das zu vermeiden.

Stelle dir vor, Du sollst entscheiden, ob Du Material X noch brauchst. Deshalb schaust Du nach, wann Material X das letzte mal benötigt wurde. Wenn das vom heutigen Datum ausgehend im März war (also erst kürzlich), kann man sagen: "Hm, weis ich nicht. Lassen wir mal liegen". Wurde es erst gestern benötigt, ist es brandaktuell und hat in jedem Fall noch Relevanz. Wurde es aber zuletzt im September 2017 benötigt, ist das beinahe ein Jahr her. Dann entscheidest Du dich für: "Das kann wech".

Bezogen auf mein Problem heißt das.

• Wird das Material NUR im Bereich 2017 gefunden, soll die Zelle in Tabelle1 farblich hervorgehoben werden.
  
• Wird das Material NUR im Bereich 2018 gefunden, ist es zu aktuell. Es soll keine farbliche Kennzeichnung stattfinden.
  
• Wird das Material im Bereich 2017 UND 2018 gefunden, stelle ich das mal zurück. Wird dann entschieden, wenn das "Kann wech" abgearbeitet ist. Eine farbliche Hervorhebung ist notwendig. In dem Fall wäre es jedoch hilfreich, wenn sich im Datum aus Tabelle B das Monat mit berücksichtigen ließe (und ich vielleicht eine andere Farbe nehmen könnte). Hm, jetzt wird's kompliziert. Vielleicht hast Du zu diesem dritten Kriterium eine ganz andere und elegantere Idee. Sicher kann Excel was, das ich noch gar nicht kenne und so viel zu kompliziert denke.
  

Über die Filterfunktion blende ich dann alle Zeilen ohne farbliche Hervorhebung aus. Klingt vielleicht etwas kompliziert in der Umsetzung (nicht Excel, sondern das praktische). Wird sonst aber zu viel und zu unübersichtlich für die Leute. Die drehen mir am Rad. Ist wie mit Bier. Das genießt man auch Schluckweise und schüttet es nicht einfach runter.  :21:

[Bob der Baumeister]

Hallo Steve,

habe mir deine Formel einmal näher angesehen, um zu verstehen, was sie eigentlich macht.

Code:

=(ZÄHLENWENNS('Tabelle2 (Bewegungsjournal)'!F:F;A5;'Tabelle2 (Bewegungsjournal)'!J:J;2017)>1)

Soweit habe ich das verstanden. Mit Ausnahme des >1 am Ende. Ich habe diese Formel zum Test in zwei Spalten angewandt und das Ergebnis ist geringfügig anders. Könntest Du mir das vielleicht kurz erläutern? Wäre nett von dir.

Gruß

[Bob der Baumeister]

Ich werde da echt nicht schlau draus. Es ist dabei unerheblich, ob ich dazu Deine oder meine Formel verwende. Was geschieht, ist, dass manche Zellen farbig gekennzeichnet werden, die in der Suchmatrix kein einziges Mal vorhanden sind. Andere wiederum, die im Suchbereich existieren, werden statt dessen nicht markiert. Eine Abfrage über ZÄHLENWENN brachte dies zum Vorschein.

Eine Idee, was das zur Ursache hat? Es dabei einmal unerheblich, ob eine Formel grundsätzlich zum gewünschten Ergebnis führt. Aber das, wie sie formuliert wurde, sollte sie doch korrekt ausführen.

[Bob der Baumeister]

(Wollte Editieren, war mir nicht mehr möglich).

Möchte Euch nur auf dem laufenden halten, da ja vielleicht jemand anderes das selbe Problem hat. Ich bin dem Fehler auf der Spur. Ich kann zwar noch nicht bestimmen, wieso. Aber ich weis mittlerweile, was die Ursache ist.

Ich habe mir jetzt mal eine ganz vereinfachte Tabelle gebaut. Diese enthält nur die beiden "Sachnummer"-Spalten beider Tabellen. Vor die "Sachnummer"-Spalte der ursprünglichen Materialliste habe ich über ZÄHLENWENN ermitteln lassen, wie oft der Wert in der "Sachnummer"-Spalte des Buchungsjournals gefunden wird. Weiter habe ich auf die "Sachnummer"-Spalte der Buchungen eine Bedingte Formatierung gesetzt, da ich wissen wollte, ob alle Zellen (Fundorte) der ZÄHLENWENN Funktion farbig hervorgehoben wurden. Und auch da kam es zu einem Fehler. Allerdings, und jetzt kommt die Ursache: Wenn ich die Sachnummer, die die Bedingte Formatierung im Buchungsjournal nicht markiert hatte, über den Textfilter suchte, wurde diese dort ebenfalls nicht aufgeführt. Die Suchfunktion selbst (Strg + F) gab die Fundorte jedoch an.

Der Formel an sich sollte das egal sein (?). Aber ist die Range des Filters begrenzt? Scheinbar hängt aber genau das zusammen.

Edit: Wenn ich die Formal so schreibe

Code:

=ZÄHLENWENN($D$D; "Wert")

(wobei bei Wert natürlich etwas angegeben wurde) wird seltsamerweise die ganze Spalte gefärbt.