Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Vergleich einer Zelle mit Werten einer Tabelle...

Vergleich einer Zelle mit Werten einer Tabelle...
10.08.2018 07:34:24
Klaus
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 :)

14
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
H3 mit INDIREKT "einfrieren"
10.08.2018 08:09:57
WF
Hi,
schreib:
=SVERWEIS(INDIREKT("H3");Blatt2!A1:C32;3;"FALSCH")
WF
AW: H3 mit INDIREKT "einfrieren"
10.08.2018 08:15:30
Klaus
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 ;-)
Da Du anscheinend viele von den Frmln hast ...
10.08.2018 08:57:39
den
... 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.
Anzeige
AW: Da Du anscheinend viele von den Frmln hast ...
10.08.2018 09:41:31
den
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 .?.
INDIREKT vs. INDEX
10.08.2018 09:54:31
lupo1
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).
Anzeige
AW: "Doppelbeton"?, so nein, aber möglich ...
10.08.2018 10:42:53
neopa
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
.. , - ...
Anzeige
1:1048576
10.08.2018 11:11:47
lupo1
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.
Anzeige
AW: 1:1048576
10.08.2018 11:37:52
Klaus
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 :)
AW: richtig gestellt ...
10.08.2018 13:59:09
neopa
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
.. , - ...
Anzeige
Ok, wenn das so ist, wusste ich es nicht ...
10.08.2018 14:29:31
lupo1
... 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.
Gegen den Volatilitätsvirus bin ich geimpft.
10.08.2018 18:11:01
WF
Ich kann also bedenkenlos =INDIREKT("H3") statt =INDEX(1:1048576;3;8) nehmen.
WF
Ich auch.
10.08.2018 18:30:51
lupo1
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.
Anzeige
AW: solange wirklich stets "H3" gesucht ist ...
11.08.2018 09:55:27
neopa
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
.. , - ...
Anzeige

328 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige