Microsoft Excel

Herbers Excel/VBA-Archiv

Vergleich einer Zelle mit Werten einer Tabelle...


Betrifft: Vergleich einer Zelle mit Werten einer Tabelle... von: Klaus
Geschrieben am: 10.08.2018 07:34:24

Guten morgen liebe Experten.
Ich stehe wiedereinmal vor einem Problem und benötige eure Hilfe.

Sachverhalt:

In Blatt 1 ist eine Tabelle, wo sich die Werte im Bereich A3-I32 ständig ändern (händisch).
In Blatt 2 ist eine Tabelle, die als Datenbank dient. Hier sind alle Werte, die in Blatt 1 Spalte H vorkommen können fest hinterlegt.

In Blatt 1 soll nun verglichen werden, ob der Wert aus:
H3 vorhanden in Blatt 2 A1:A32
H4 vorhanden in Blatt 2 D1:D30
H5 vorhanden in Blatt 2 G1:G48
H6 vorhanden in Blatt 2 A1:A32
H7 vorhanden in Blatt 2 D1:D30
H8 vorhanden in Blatt 2 G1:G48
...
...
H30 vorhanden in Blatt 2 A1:A32
H31 vorhanden in Blatt 2 D1:D30
H32 vorhanden in Blatt 2 G1:G48

Bei H32 in Blatt 1 ist schluss.
Als Ergebniss soll er mir in Blatt 1 Spalte J (pro Zeile) ein i.O. für Wahr und n.i.O. für Falsch ausgeben (Bed. Formatierung färbt dann entsprechend die Zelle ein).

Mein Ansatz bisher war =SVERWEIS(H3;Blatt2!A1:C32;3;"FALSCH") ((Spaltenindex ist eine leere Spalte). Das funktioniert auch nur mein Problem ist, dass ich den Wert aus H3 im Fall eines Fehlers innerhalb der Spalte H verschieben muss (händisch nach oben oder unten). Tu ich dies ändert sich in der Formel das Suchkriterium (H3) mit. Fülle ich H3 nun wieder mit einem neuen Wert erhalte ich ein falsches Ergebniss da das Suchkriterium ja immernoch an den Wert "gebunden" ist, der vorher dort stand.

Bsp.:
Aktueller Wert in H3 Ergebniss in J3 mit =SVERWEIS(H3;Blatt2!A1:C32;3;"FALSCH") i.O.
Aktueller Wert verschoben in H40 Ergebniss in J3 =SVERWEIS(H40;Blatt2!A1:C32;3;"FALSCH") i.O.
Neuer anderer(!) Wert in H3 Ergebniss in J3 =SVERWEIS(H40;Blatt2!A1:C32;3;"FALSCH") i.O.

Das darf so nicht sein. Ich benötige nun entweder eine Formel oder am liebsten ein Makro über einen Button welches (ständig) die Werte in H3:H32 mit den Tabellen in Blatt 2 abgleicht und mir das entsprechende Ergebniss in Spalte J liefert ohne sich "fest an den Wert zu klammern", der zuerst in der Zelle stand.

Langer Beitrag aber ich hoffe, dass ich euch somit so genau wie möglich erklären konnte, was mein Problem und Anliegen ist. Falls nicht bitte einfach fragen!
Beispieldatei kann ich leider nicht hochladen, da es sich um eine Firmeninterne Datei handelt.

Da heute Freitag ist und ich im Büro sitze kann es durchaus sein, dass ich mich erst am Montag wieder hierzu melden kann. Verzeiht mir dies bitte *smile*

Vielen Dank vorab für die Mühe und investierte Zeit :)

  

Betrifft: AW: Vergleich einer Zelle mit Werten einer Tabelle... von: Klaus
Geschrieben am: 10.08.2018 08:07:35

Ich habe nun doch eine kleine Testmappe erstellen können:

https://www.herber.de/bbs/user/123248.xlsx


  

Betrifft: H3 mit INDIREKT "einfrieren" von: WF
Geschrieben am: 10.08.2018 08:09:57

Hi,

schreib:
=SVERWEIS(INDIREKT("H3");Blatt2!A1:C32;3;"FALSCH")

WF


  

Betrifft: AW: H3 mit INDIREKT "einfrieren" von: Klaus
Geschrieben am: 10.08.2018 08:15:30

Es ist wiedereinmal unfassbar, dass es nur ein einer solchen "Kleinigkeit" liegt.
Funktioniert perfekt.

Ich danke dir vielmals für den kleinen Anstoßer.

Frage lasse ich dennoch auf, falls jemand die Lust verspührt, eine Makro zu basteln ;-)


  

Betrifft: Da Du anscheinend viele von den Frmln hast ... von: lupo1
Geschrieben am: 10.08.2018 08:57:39

... könnte man aus
=SVERWEIS(INDIREKT("H3");Blatt2!A1:C32;3;"FALSCH")

noch
=SVERWEIS(INDEX(H:H;3);Blatt2!A1:C32;3;)

machen.

Hintergrund: INDIREKT ist i Ggs z INDEX volatil, wird also auch ohne Änderung der Quelle bei jeder Neukalkulation gerechnet. Das kostet unnötig Zeit.


  

Betrifft: AW: Da Du anscheinend viele von den Frmln hast ... von: Klaus
Geschrieben am: 10.08.2018 09:41:31

Uff...

Danke auch dir lupo1.

Getestet habe ich es und des funktioniert auch perfekt.
Leider sind meine Kenntnisse, gerade in Bezug auf Formeln noch ziemlich gering. So ganz erschließt sich mir also deine Idee nich.

Könntest du mir (wenn du die Zeit und Lust hast) das genauer erklären?
Bei lediglich 32 Zeilen spielt die Zeit ja eig. keine große Rolle, oder? Ich würde auf das INDEX wohl zugreifen, wenn es sich um eine größere Anzahl an zu prüfenden Zeilen handelt .?.


  

Betrifft: INDIREKT vs. INDEX von: lupo1
Geschrieben am: 10.08.2018 09:54:31

INDIREKT("H3") betoniert Deine Zelle in beide Richtungen.
INDEX(H:H;3) betoniert nur die ZEILE, da Du die Formel innerhalb der SPALTE H:H zu bewegen gedachtest.

Volatilität (zu vermeiden) habe ich erklärt.

INDEX(H:H;$X$1) erlaubt Dir, die 3 in eine andere Zelle zu schreiben. Das könnte Vorteile haben.

INDEX($A:$IU;3;8) entspricht übrigens INDIREKT("H3") (Doppelbeton).


  

Betrifft: AW: "Doppelbeton"?, so nein, aber möglich ... von: neopa C
Geschrieben am: 10.08.2018 10:42:53

Hallo lupo,

... denn es kommt mE darauf an, welchen Zellenwert man letztendlich wirklich auswerten will.

Die Formel =INDIREKT("H3") liefert "starr" immer den Wert aus H3, dagegen =INDEX($A:$IU;3;8) nicht immer. Z.B. nicht, wenn man Spalten vor Spalte H einfügt.

Wenn man ein gleiches "starres" Ergebnis mit INDEX() erzielen will, müsste man =INDEX(1:1048576;3;8) schreiben. Aber derartige "Starrheit" wird mE wohl ganz selten gebraucht.

Insofern bin ich auch weiterhin ein Verfechter für INDEX()-Formeln, weil INDEX() nicht nur nicht volatil ist, sondern man die INDEX()-Formeln auch einfach(er) und flexibel(er) definieren kann und zwar so, dass diese auch bei evtl. notwendigen Strukturänderungen die ursprüngliche Auswertungsziele ohne Formeländerung noch wahrnehmen.

Gruß Werner
.. , - ...


  

Betrifft: 1:1048576 von: lupo1
Geschrieben am: 10.08.2018 11:11:47

ist doch das gleiche wie A:XFD. Jenes wird jedoch automatisch in 1:1048576 umgewandelt - mit unerwünschten Folgen:

1) kollidiert das (genauso wie A:XFD selbst schon) dann mit .xls
2) passiert dies mit A:IV genauso ungewollt, falls migrierend

Daher nehme ich immer A:IU. Das bleibt so stehen, klappt in .xls und .xlsx (auch hin und her) und ist somit die größtmögliche stabile und übergreifend gültige Gesamtadressierung für INDEX.

Einfügen oder Löschen von Spalten (und/oder Zeilen - verstehe nicht, warum Du nur eine Dimension betrachtest) hält das natürlich nicht aus (bzw. die eine der beiden denkbaren Dimensionen). Hier ging es aber um Kopieren.


  

Betrifft: AW: 1:1048576 von: Klaus
Geschrieben am: 10.08.2018 11:37:52

Ich hoffe, dass ich jetzt hier keine Diskusion losgetreten habe.

Mein Wunsch wurde komplett und perfekt erfüllt.
Ein Makro wäre mir auch recht gewesen weil man dann keine Formel in einer Zelle stehen hat, die "versehentlich" von einem anderen User geändert/gelöscht wird (Blattschutz nicht möglich!).
Ich habe in Excel zuerst mit Makros begonnen, daher bin ich wohl eher Fan davon als von Formeln.

Aber seis drum. Alles gut. Ich bin mehr als zufrieden, habe was ich gesucht hab (Punkt).

Ich danke euch vielmals und wünsche schoneinmal ein erholsames Wochenende :)


  

Betrifft: AW: richtig gestellt ... von: neopa C
Geschrieben am: 10.08.2018 13:59:09

Hallo,

... ich hatte mich insofern verschrieben, dass ich meinte: "wenn man Spalten vor Spalte A einfügt" Und dadurch ändert sich das Ergebnis bei $A:$IU gegenüber der INDIREKT()-Formel bzw.meiner INDEX()-Formel, wenn man Spalten vor der Spalte A einfügt.

Übrigens ist meine Angabe 1:1048576 nur scheinbar relativ. Diese ist bereits damit automatisch auf das gesamte Tabellenblatt fixiert, genau wie $1:$1048576.

Auf das Einfügen von Zeilen bin ich bewusst nicht eingegangen, weil bei Bereichsangaben wie z.B. $A:$IU dies sowieso keine Bezugsänderung im Ergebnis bewirken kann.

Eine Bezugsangabe mit 1:1048576 auch bleibt beim Einfügen von Zeilen vor Zeile 1 exakt erhalten, obwohl der Bezug sich auf Zeilennummern bezieht. Das ist eben der kleine Unterschied, der aber wie ich auch schon schrieb, in seltenen Fällen überhaupt zum Tragen kommen dürfte.

Gruß Werner
.. , - ...


  

Betrifft: Ok, wenn das so ist, wusste ich es nicht ... von: lupo1
Geschrieben am: 10.08.2018 14:29:31

... und es hätte durchaus einen guten Nutzen.

Die Abwägung, ob man dies (Einfügen von Zeilen) oder $A:$IU (Wechsel zu Antik-Excel) verwendet, muss dann halt jeder selbst treffen.


  

Betrifft: Gegen den Volatilitätsvirus bin ich geimpft. von: WF
Geschrieben am: 10.08.2018 18:11:01

Ich kann also bedenkenlos =INDIREKT("H3") statt =INDEX(1:1048576;3;8) nehmen.

WF


  

Betrifft: Ich auch. von: lupo1
Geschrieben am: 10.08.2018 18:30:51

Formeln, die mein Modell verlangsamen, lösche ich grundsätzlich bis auf eine Kopierquellenformel, und fülle sie nur bei Bedarf nach unten oder rechts aus.

Daher ist Volatilität egal. Es gibt auch noch die Möglichkeit, ein Modell komplett über VBA zu rechnen. Das erspart Formeln wiederum ganz.

Trotzdem sollte der Vorteil von INDEX ggü INDIREKT immer mal wieder genannt sein. Denn nicht jeder löscht gern Formeln oder verwendet gar ein Sub.


  

Betrifft: AW: solange wirklich stets "H3" gesucht ist ... von: neopa C
Geschrieben am: 11.08.2018 09:55:27

Hallo WF,

... ist INDIREKT("H3") natürlich die einfachere Version, die ich auch nutzen würde, wenn wirklich der absolute "starre" Zellbezug gesucht ist.

Und die Volatilität spielt bei nicht massenweisen INDIREKT()-Formeln oder gar nur einer Formel bei heutigen PC-Generationen fast keine Rolle (mehr).

Die Formel =INDEX(1:1048576;3;8) anstelle INDIREKT("H3") habe ich nur in meiner Argumentation gegenüber lupos "Doppelbeton" -Formel angeführt.

Nach meinen Erfahrungen wird von vielen Usern meistens ein "relativer" Wert gesucht, d.h. es soll auch nach Löschen oder Einfügen von Spalten und Zeilen vor der entsprechenden Bezugsadresse entweder nur der Spaltenbezug oder nur der Zeilenbezug "starr" beibehalten und auch die Formel nicht händisch angepasst werden. In so einem Fall rate ich Usern immer zu einer INDEX()-Formel.

Meine diesbzgl. wiederholenden Aussagen in entsprechenden threads bzgl. flexiblere INDEX()-Funktionalität sind insofern z.B. auch konform zu lupos letzter Aussage.

Gruß Werner
.. , - ...


Beiträge aus dem Excel-Forum zum Thema "Vergleich einer Zelle mit Werten einer Tabelle..."