BITTE helfen Sie uns HEUTE mit einer SPENDE
Helfen Sie das LibreOffice Forum zu erhalten!

❤️ DANKE >><< DANKE ❤️

> KEINE WERBUNG FÜR REGISTRIERTE BENUTZER!<
Ihre Spende wird für die Deckung der laufenden Kosten sowie den Erhalt und Ausbau 🌱 des LibreOffice Forums verwendet.
🤗 Als Dankeschön werden Sie im Forum als LO-SUPPORTER gekennzeichnet. 🤗

Verweis-Funktion funktioniert nur im begrenzten Bereich

CALC ist die Tabellenkalkulation, die Sie immer wollten.
Antworten
Mr.TomTom
! Supporter - Spende !
Beiträge: 5
Registriert: So 19. Jun 2022, 19:57

Verweis-Funktion funktioniert nur im begrenzten Bereich

Beitrag von Mr.TomTom » Di 21. Jun 2022, 22:53

Hallo Leute!
Mich quält die "Verweis"-Funktion, weil sie offensichtlich nur sehr begrenzt funktioniert und ich komme einfach nicht dahinter, woran das liegen könnte. Dabei ist die Geschichte gar nicht mal komplex.

Ich nutze die Spalten A um dort von Zelle A3 bis Zelle A23 die Zahlen 1. . .21 aufsteigen abzutragen. In der Spalte B sind nun zu jeder dieser einzelnen Nummern individuelle Werte eingetragen. Aktuelle Werte im Bereich 0 . . . 1548.

In der Spalte D identifiziere ich nun die jeweils fünf größten Werte absteigend und angefangen in D3mit =KGRÖSSTE(B$3:B$23,1), =KGRÖSSTE(B$3:B$23,2) bis eben =KGRÖSSTE(B$3:B$23,5). Soweit so gut und alles kein Hexenwerk und tadelloser Funktion.

Jetzt zum Knackpunkt: In der Spalte C verwende ich nun die Verweis-Funktion wie folgt: =VERWEIS(D3,B$3:B$23,A$3:A$23). =VERWEIS(D4,B$3:B$23,A$3:A$23), =VERWEIS(D5,B$3:B$23,A$3:A$23), =VERWEIS(D6,B$3:B$23,A$3:A$23) und =VERWEIS(D7,B$3:B$23,A$3:A$23), um den fünf identifizierten Werten aus der Spalte B die zugehörige Zahle (1 . . .21) aus der Spalte A zuzuordnen.

Auch das sollte eiegentlich kein Hexenwerk sein und ist ziemlich simple, allerdings funktioniert die korrekte Zuordnung nur für die Zeilen 3 und 4. Ab Zeile 5 wird mir, aus welchen Grund auch immer, stests der Wert bzw. die Zahl 11 zurückgegeben. Der Zahl 11 in Spalte A ist in Splate B allerdings nur ein Wert in Höhe von 38 zugeordnet.

Ich verstehe es nicht . . . :?
Mache ich irgendeinen Fehler mit meiner Abfrage und sehe den Wald vor lauter Bäumen nicht mehr???
Test_Verweis.ods
(11.94 KiB) 56-mal heruntergeladen
Kann mich irgendjemand aus meiner Verzweifelung retten?

Aller besten Dank und Grüße,
Mr. TomTom

echo
Beiträge: 161
Registriert: Do 27. Jan 2022, 09:53

Re: Verweis-Funktion funktioniert nur im begrenzten Bereich

Beitrag von echo » Di 21. Jun 2022, 23:42

Hallo Tom
In Zeile 14 wird der Wert in Spalte B größer wie das Suchkriterium.

Verweis sucht in der Suchspalte nur so lange weiter wie der nächste Wert in der Spalte kleiner oder gleich dem Suchkriterium ist. Wird der Wert größer (wie in B14) bricht Verweis die Suche ab und zeigt den Inhalt der Zelle an.

Deine Liste ist für Verweis schlicht weg nicht geeignet.
Wegen dem oben genannten Grund ist zwingend eine aufsteigende Sortierung der Suchspalte notwendig, sonnst kommt Murks raus.

Verwende statt dessen eine Kombination aus Index und Vergleich.
Vergleich lässt sich so einstellen, das nach einer GENAUEN Übereinstimmung gesucht wird. Da ist sie Sortierung dann nicht notwendig
C3:
=INDEX($A$3:$A$23;VERGLEICH(D3;$B$3:$B$23;0))

Gruß Holger

PS
Sortiere doch deine Liste zum Spaß mal nach Spalte B aufsteigend :)
Dateianhänge
Test_Verweis.ods
(15.86 KiB) 54-mal heruntergeladen

Benutzeravatar
karolus
Beiträge: 2170
Registriert: Fr 10. Dez 2010, 10:01

Re: Verweis-Funktion funktioniert nur im begrenzten Bereich

Beitrag von karolus » Mi 22. Jun 2022, 00:37

Hallo

…andererseits könnte man auch gleich das ganze nach Spalte B absteigend sortieren, hätte damit die gesuchten Werte oben in der gewünschten Reihenfolge und bräuchte infolgedessen kein zusätzliches Formel-geschwurbel.
LO7.4.7.2 debian 12(bookworm) auf Raspberry4b 8GB (64bit)
LO7.6.3.2 flatpak debian 12(bookworm) auf Raspberry4b 8GB (64bit)

Mr.TomTom
! Supporter - Spende !
Beiträge: 5
Registriert: So 19. Jun 2022, 19:57

Re: Verweis-Funktion funktioniert nur im begrenzten Bereich

Beitrag von Mr.TomTom » Mi 22. Jun 2022, 23:06

Hallo Karolus, Echo!
Allerbesten Dank!
Das ging fix.
Werde das nicht mehr heute, aber dann hoffentlich gleich morgen testen können.
Ich habe diesen konkreten Fall jetzt noch nicht unter Excel getestet, verwende die Verweis-Funktion dort beruflich aber häufiger und meine, ein solches Phänomen ist mir da nicht nicht begegnet. Oder sollte ich mich da täuschen?
Wie auch immer, bei der privaten Nutzung ist mir LibreOffice deutlich sympathischer als die MS-Produkte. Insofern werde ich mein Wissen in dem Bereich wohl noch ausdehnen müssen.

Beste Grüße.
Mr.TomTom

echo
Beiträge: 161
Registriert: Do 27. Jan 2022, 09:53

Re: Verweis-Funktion funktioniert nur im begrenzten Bereich

Beitrag von echo » Do 23. Jun 2022, 08:25

Oder sollte ich mich da täuschen?
JA, Du täuscht dich ! VORSICHT MIT DEM ERGEBNIS !

Anders wie zB. in SVERWEIS oder VERGLEICH kennt VERWEIS den 4. Parameter nicht wo man anweisen kann genau zu suchen.
Auch dort währe eine Sortierung zwingend erforderlich, wenn man den Parameter auf WAHR (1) setzt.

Also bei VERWEIS immer sortieren !!

Gruß Holger

Mr.TomTom
! Supporter - Spende !
Beiträge: 5
Registriert: So 19. Jun 2022, 19:57

Re: Verweis-Funktion funktioniert nur im begrenzten Bereich

Beitrag von Mr.TomTom » Do 23. Jun 2022, 23:53

Ja, Teufel!
Ich täusche mich tatsächlich!
Warum ist mir das bislnag denn noch nie aufgefallen?
Und jetzt stolpere ich bei einer solch simplen Geschichte darüber.
Das muss ich mir morgen im Büro nochmal genauer ansehen.

Wie auch immer, Deine Index/Verweis-Kombination löst das Problem wie gewünscht.

Noch mal allerbesten Dank!

Gruß,
Mr.TomTom

Mr.TomTom
! Supporter - Spende !
Beiträge: 5
Registriert: So 19. Jun 2022, 19:57

Re: Verweis-Funktion funktioniert nur im begrenzten Bereich

Beitrag von Mr.TomTom » So 26. Jun 2022, 11:11

Hallo "Echo", Holger!
Ich bn noch mit einem anderen Problem konfrontiert . . . .
Meine auszuwertende Liste ist etwas länger als die der zuvor angehängten Test-Datei.
Sie enthält in der Spalte A auch keine reinen Zahlen, was aber unerheblich sein sollte.
Ich hatte das lediglich der Einfachheit halber zur Poblemerläuterung beispielhaft entsprechend angelegt.

Das Problem, welches sich auftut ist, wenn sich mehrere identische Zahlen in Spalte D befinden.
Dann bricht die Suche bei der derzeitigen Formelgestaltung immer an der ersten gefundenen Übereinstimmung ab und gibt den zugehörigen Inhalt aus der Spalte A in der Spalte C wieder. Ich würde aber gerne den Inhalt der nächsten Zelle in A sehen, die eben zufällig den gleichen Wert in Spalte D besitzt wie die erste gefundene.

Hätte ich über VLOOKUP und MATCH (sorry, EXCEL Funktionen, die Pendants in CALC muss ich noch recherchieren) eine Chance?

Das aufsteigende Sortieren der Spalte B habe ich noch nicht getestet, weil die Daten in A und B von extern importiert werden. Da müsste ich dann am Import der Daten "stricken", was mir im Augenblick komplexer erscheint.

Besten Dank und Gruß,
Dieter

echo
Beiträge: 161
Registriert: Do 27. Jan 2022, 09:53

Re: Verweis-Funktion funktioniert nur im begrenzten Bereich

Beitrag von echo » So 26. Jun 2022, 13:21

Hallo Dieter
die einfachste Version ist meist, wenn du dafür sorgst das es beim Suchkriterium keine Duplikate gibt, ohne das sich diese in der Reihenfolge ändern. Dazu kann man zB. die Zeilennummer/100000 als sehr kleinen Wert addieren.
Die Reihenfolge der Werte bleibt erhalten aber auch Duplikate unterscheiden sich.

Es geht auch komplett per Matrix-Formel
Ich hänge mal ein Beispiel an
Gruß Holger
Dateianhänge
Test_Verweis (1).ods
(28.86 KiB) 54-mal heruntergeladen

Mr.TomTom
! Supporter - Spende !
Beiträge: 5
Registriert: So 19. Jun 2022, 19:57

Re: Verweis-Funktion funktioniert nur im begrenzten Bereich

Beitrag von Mr.TomTom » Mi 29. Jun 2022, 23:02

Hallo Holger!
Die Idee mit der Zeilennummer dividiert durch 100000 ist natürlich perfekt.
Sehr clevere Idee! ich sehe mir aber auch mal die Matrix-Formel in Deinem Beispiel an. Ich denke das ist dass, was Du angehängt hattest. Richtig? Da kann ich bestimm noch einiges lernen und das Verständnis vertiefen.

Wiederum besten Dank und einen ebensolchen Gruß,
Dieter

An alle, die das LibreOffice-Forum nutzen:


Bitte beteiligen Sie sich mit 7 Euro pro Monat und helfen uns bei unserem Budget für das Jahr 2024.
Einfach per Kreditkarte oder PayPal.
Als Dankeschön werden Sie im Forum als LO-SUPPORTER gekennzeichnet.

❤️ Vielen lieben Dank für Ihre Unterstützung ❤️

Antworten