[Excel] Zahl mit einem Spaltenbereich vergleichen und nebenstehende Zahl ausgeben

[Nitschi66]

Hey Leute,

ich stehe gerade etwas aufm Schlauch...
Hier eine Testdatei:Anhang anzeigen CB-Matrix Spalte vergleichen.xlsx

Ich möchte in Zeile H1 überprüfen, ob der Wert in G1 und C1 identisch ist, WENN E1 und A1 identisch sind. Die Krux ist aber, dass nicht alle Zahlen in Spalte A auch in Spalte E vorhanden sind, da sind manchmal noch ein paar dazwischen.
Also will ich eigentlich überprüfen, ob die Wert in G1 und C"X" (X=variabel) identisch ist, wenn E1 und A"X" (SELBE variable wie bei C) identisch sind.

Jemand n Tipp?

 

[PERKELE]

Schaue mal, reicht dir das?

Siehe Anhang.

 

[Nitschi66]

Nee leider ja gar nicht. Du berücksichtigst nicht, dass der Wert in Zelle "E3" der gleiche wie in "A4" ist und dann der Wert "C4" und "G3" verglichen werden sollte.
Deswegen "Zahl mit einem Spaltenbereich" vergleichen. Die einzelnen Zellen vergleichen kriege ich auch hin :-)

 

[spcqike]

so?
"=WENN(ISTZAHL(VERGLEICH(E1;$A$1:$A$13;0));WENN(G1=INDIREKT("C"&VERGLEICH(E1;$A$1:$A$13;0);WAHR);"ja";"nein");"nein2")"

 

[Nitschi66]

Die Lösung von SPCQIKE scheint zu funktionieren! Danke!
Toll ist, dass durch "nein" und "nein2" ersichtlich ist, wo der Fehler in der Überprüfung sein könnte: im Vergleich (nein) von Spalte A mit einem Wert aus Spalte E oder aus dem Vergleich (nein2) von Spalte C mit einem Wert aus Spalte G.

Beim Reinkopieren deiner Formel hat Excel gemeckert, es sei irgendwo ein Rechtschreibfehler und das Programm korrigiert ihn selbständig.
Das ist das, was jetzt in meiner Zelle steht:

=WENN(ISTZAHL(VERGLEICH(E2;A:A;0));WENN(G2=INDIREKT("C"&VERGLEICH(E2;A:A;0);WAHR);"ja";"nein");"nein2")

Edit: Ich korrigiere nochmal die Formel oben in meinem "Quote" [Spalte A ist jetzt gesamt drin, und nicht nur von Reihe 1 bis 13]

 

[PERKELE]

Lol - an der gleichen Lösung habe ich eben gerade auch gearbeitet. ^^ Bei meinem ersten Versuch habe ich deinen Initialpost nicht ganz zu Ende gelesen.

 

[Nitschi66]

Habe ja irgendwie gehofft, dass das mit einem SVERWEIS oder so eleganter geht, die jetzige Formel erschlägt einen ja doch ziemlich

 

[Sun_set_1]

Eieiei, Freaks @Work

Durch die Nutzung von INDIREKT, baut man sich quasi einen einfachen Array auf. Ist schick, kann bei größeren Tabellen aber inperformant werden.

Workaround wäre Nutzung einer Hilfsspalte in welcher man dies über Verketten von A&B respektive E & G und dann über WENN(SVERWEIS( löst.

Durch das Verketten kannst Du dann auch einfach festlegen, welche Kombinations-Kriterien gesucht werden sollen. Ohne viele WENN(SVERWEIS zu verschachteln, oder auch INDIREKT bzw. Arrays zu nutzen.


EDIT:

Geht auch mit WENN / ISTFEHLER / SVERWEIS:

=WENN(ISTFEHLER(SVERWEIS(E2;A2:C14;1;FALSCH));"nein2";WENN(SVERWEIS(E2;A:C;3)=SVERWEIS(E2;E:G;3;FALSCH);"ja";"nein"))

Ich ersetze Indirekt durch einen sich selbst ausgebenden SVERWEIS.
Formel prüft allerdings nur ob E in A enthalten ist. Nicht ob A in E.
Könnte man über

WENN(ODER(ISTFEHLER(

lösen.

 

[Nitschi66]

@Sun_set_1
Puh, ich muss deine Formel mal etwas anpassen in meiner Datei, ich glaube du hast diese aus H2 rauskopiert ne?

Edit:

=WENN(ISTFEHLER(SVERWEIS(E1;A:C;1;FALSCH));"nein2";WENN(SVERWEIS(E1;A:C;3)=SVERWEIS(E1;E:G;3;FALSCH);"ok";"nein"))

Das wäre die überarbeitete Formel von Sun_set_1, sie gibt leider nicht die richtigen ergebnisse aus.
Überarbeitungen: Zielzelle ist "H1" und nicht "H2" (wodurch dich fast alle 2en in Sun_set_1 formel in 1en bei mir ändern) und im ersten ISTFEHLER() ist "A2:C14" zu "A:C" geändert worden. Meine Datei beinhaltet mehr als 14 Zeilen.
Leider bleibts dabei dass diese Formel nicht die gleichen (richtigen) jas, neins, und nein2s ausgibt wie die Formel in Beitrag 5

 

[Sun_set_1]

Ach Mist, schon wieder geschlossen. Es war glaube ich H1. In I1 war zum Vergleich spcqike's Formel. Ergebnisse waren gleich.
Die Anpassung mit

WENN(ODER(ISTFEHLER(SVERWEIS1);ISTFEHLER(SVERWEIS2)

Müsstest Du selber hinbekommen
Kleiner Tipp, ISTFEHLER prüft false positiv ab.
Heißt ist eine Formel wie SVERWEIS = #NV, dann ist die Antwort auf ISTFEHLER: WAHR, womit man dann wunderbar in einer WENN weiterarbeiten kann.

 

[Nitschi66]

Hmm, komisch...

Edit:
Ok, ich habe einen Teil des Fehlers gefunden, was die Lösung von Sun_set_1 für mich unbrauchbar macht.
Trotz mehrmaligen Aktualisierens wurden die angaben "nein" und "nein2" trotz richtiger angaben nicht korrigiert. Excel etc alles aktualisiert, formel neu "gestartet" Dann habe ich den Bereich A:C nach Spalte A alphabetisch sortieren lassen und dann waren viele Fehlangaben weg. Was aber das größere Problem ist:
Ich habe im Bereich A:C auch etwas Datenmüll. Zeile 3 findet sich nochmal in Zeile 12 wieder, hat dort aber in Spalte C keinen Eintrag. Damit kommt die Formel von Sun_set_1 nicht zurecht und gibt dann einen Fehler aus. Diesen Datenmüll kann ich leider nicht verhindern, daher kann ich (trotz eleganterer und performanterer lösung) diese Formel nicht nutzen und bleibe bei der aus beitrag 5

 

[Sun_set_1]

@Nitschi

Sorry hatte einen Denkfehler, man muss gar nicht Kreuz-Prüfen...

=WENN(ISTFEHLER(SVERWEIS(E1;A:C;1;FALSCH));"E in A nicht gefunden";WENN(SVERWEIS(E1;A:C;3;FALSCH)=SVERWEIS(E1;E:G;3;FALSCH);"Preis gleich";"Relation vorhanden, Preis unterschiedlich"))

Formel für I1.

 

[spcqike]

bei mir funktioniert die Formel von Sun_set_1 auch wunderbar. allerdings ohne wenn(oder(..)).
Die Lösung mit dem doppelten SVerweis finde ich sehr gelungen. Mir ist auf die Schnelle nur die Indirekt Lösung eingefallen, die ja bei größeren Tabellen zu Perfomanceproblemen führt. Über die Größe der Ausgangstabelle wurde aber nichts gesagt

Nebenbei, ist die Überprüfung ob E in A ist nicht das selbe, wie ob A in E ist? Eine einseitige Überprüfung reicht doch.... oder?

 

[Nitschi66]

So gerne ich lerne und so sehr ich deine Einstellung teile - mit deinen Angaben kriege ich das nicht hin.
In deinem (jetzt zweimal) geposteten Wenn-Code fehlt ne Klammer zu. Diese wird sicherlich einfach hinten rangehangen. Die Parallelen zum ersten Code sehe ich zwar, doch fehlt in der ODER-Überprüfung der dritte SVERWEIS den du im ersten Code drin hast. Ich blick da nicht durch.

@spcqike:
In A sind werte drin, die in E auf keinen Fall sind. Die Werte in E sollten (daher der Test/Vergleich) aber in A sein.

 

[Sun_set_1]

Nebenbei, ist die Überprüfung ob E in A ist nicht das selbe, wie ob A in E ist? Eine einseitige Überprüfung reicht doch.... oder?

Danke

Haha, genau das ist mir auch aufgefallen. Wenn ein Wert aus E in A vorhanden ist, wird er ja logischer Weise mitgeprüft. Somit kann in A kein Wert mehr stehen der auch in E vorhanden ist und nicht geprüft wurde. Musste ich aber auch kurz drüber nachdenken

Muss jetzt ins Meeting. Melde mich nach Feierabend nochmal gerne.

EDIT:

Eventuell ist die Formel bei den ganzen Edits untergegangen:

=WENN(ISTFEHLER(SVERWEIS(E1;A:C;1;FALSCH));"E in A nicht gefunden";WENN(SVERWEIS(E1;A:C;3;FALSCH)=SVERWEIS(E1;E:G;3;FALSCH);"Preis gleich";"Relation vorhanden, Preis unterschiedlich"))

In I1 muss gehen, da fehlt auch keine Klammer. Gerade nochmal geprüft

 

[Nitschi66]

Danke für dein Update, ja das ging bei mir wirklich etwas unter.
Die Formel funktioniert so halb.

Beispiel neue Werte:
Zelle-E14 = 5054 (wie in E13)
Zelle-G14 = 8,00 €.​

Also kleine Nummer aber anderer Preis. Die Formel von spcqike gibt "Relation vorhanden, Preis unterschiedlich" aus. Das wäre für mich in dem Zusammenhang auch die gewünschte Ausgabe, deine Formel gibt das bei mir nicht aus.

 

[Sun_set_1]

Die „Relation vorhanden“- Formel ist doch die Letzte von mir?

Aber ja, Formel prüft Block gegen Block, nicht innerhalb eines Blockes selbst auch nochmal auf doppelte Werte.
Wenn doppelte Werte in einer Spalte stehen, nutzt man dafür normalerweise auch keine Formel, sondern die Funktion Überprüfen -Daten. Dort kannst Du auch sagen, prüfe ob in E und G jeweils gleiche Werte stehen, und lösche diese.
Damit sortiert man solche Werte aus, um anschließend zu den Formelauswertungen überzugehen. Alles andere ist Performance- und Übersicht-Selbstmord in großen Dateien.

Die einzige Begründung, wieso man diese nicht aussortieren will, wären bspw. unterschiedliche Kriterien, bspw. Mengen.
Also Relation 1; Menge A; Preis A.

Spätestens jetzt, würde ich aber auf Verketten gehen. Aka Relation und Menge in einer Zelle verketten und rechts daneben dann die Preise. Dann hast Du auch ein eindeutiges Suchkriterium. In der Logistik benutzen wir für sowas häufig solche Kombos.

Aka D21D22;5 | 350€

Was übersetzt bedeuten würde, von D-21 nach D-22, 5 Paletten, 350€.
Solche Identifier lassen sich eigentlich immer irgendwie basteln und in Formeln super verwenden. Hoffe es hilft.

[Das Problem in Excel ist, dass 99% der Standard-Formeln, nur das erste richtige Ergebnis berücksichtigen und dann aufhören. Beispielsweise SVERWEIS oder auch Index. Diese gehen nur bis zum ersten Treffer und geben von diesem die Werte zurück.]

Daher brauch man als Suchkritierium immer eindeutige Idents.

 

[ohmsl]

Wozu ein Sverweis? Es reicht auch die normale Vergleich-Funktion.
z.B.

=WENN(ISTFEHLER(UND(VERGLEICH(G1;$C$1:$C$13;0)>0;VERGLEICH(E1;$A$1:$A$13;0)>0));"Nein";"Ja")

 

[Nitschi66]

Die „Relation vorhanden“- Formel ist doch die Letzte von mir?

ja ja^^ ich wollte nur jetzt nicht "nein" und "nein2" schreiben weil das so unübersichtlich geworden wäre.

Es können leider keinen spezifischeren Idents gegeben werden als die Nummern in Spalte A und E, da kann ich nicht viel dran rütteln. Aber ich habe ja jetzt eine Formel, die super die Anforderungen erfüllt. Und bei einer Excel mit weniger als 300 Zeilen und nur 7 Spalten ist das auch nicht der große Ressourcenfresser :-)

 

[Sun_set_1]

Wozu ein Sverweis? Es reicht auch die normale Vergleich-Funktion.
z.B.

=WENN(ISTFEHLER(UND(VERGLEICH(G1;$C$1:$C$13;0)>0;VERGLEICH(E1;$A$1:$A$13;0)>0));"Nein";"Ja")

Hi ohmsl,

habe gerade kein Excel offen. Aber was passiert mit deiner Formel, wenn es für die gleiche Nummer, versch. Preise + gleiche Preise, für versch. Nummern gibt?

Also
1234 200€ | 1234 400€
6789 400€ | 6789 200€


Dann wären doch beide Vergleich(-Bedingungen) erfüllt und ISTFEHLER somit false, was in der WENN wiederum „ja“ auswirft. Obwohl es keinen gleichen Preis gibt. Oder habe ich was übersehen?

@Nitschi,

Alles klar. Dann geht’s ja. Leute die über Google hierhin kommen, dürften nun auch genug Ansatzpunkte haben