[SOLVED] SZUKANIE NAJBLIŻSZEGO WYNIKU W BAZIE Z WARUNKIEM

[hezer]

Dzień dobry,
mam kolumnę (A) z Literą oraz kolumnę (B) z liczbą oraz bazę LiteraLiczba, która jest zbiorem różnych Liter i Liczb.
Potrzebuje sposobu, który po podaniu A i B zwróci mi z bazy tą komórkę która:
- posiada odpowiednią Literę, taką jak w A
- Liczba jest najbardziej zbliżona do podanej w B

Przykładowo wpisując C 120, a w bazie mam rekordy C100, C200 to chce by wynikiem było 100. (lub C100 też da radę)

Załączam screen i przykładowy plik.

Bardzo proszę o pomoc.

[Jan_J]

Gdyby wpisy mogły być uporządkowane w „naturalny” sposób, można by wykorzystać funkcję wyszukującą: PODAJ.POZYCJĘ(wartość; zakres; 1). Końcowa 1 daje wskazówkę, by traktować spis jako uporządkowany, a samo wyszukiwanie jako aproksymacyjne. W tym przypadku wynik jest numerem pozycyjnym, na którym znajduje się wpis najbliższy poszukiwanemu „od dołu”. W takim razie wpis najbliższy powinien spełniać 2 warunki:
* zaczynać się od tej samej litery, co poszukiwany,
* być albo tym, który wskaże PODAJ.POZYCJĘ, albo następnym w kolejności. Wybieramy bliższy z nich.
Warunki te sformułować trzeba samodzielnie.
Trochę trzeba będzie powalczyć z sytuacją, kiedy wpis ma liczbę mniejszą niż liczby w katalogu.
Załączam szkic — formuła w kolumnie I jest niekompletna.

BAZA.ods

(8.11 KiB) Pobrany 63 razy

[hezer]

Dzięki coś pokombinowałem i chyba mam.

Rozbudowałem bazę o 2 kolumny. Kolumnę wyciągającą samą wartość i później średnią z tej wartości i następnej w kolejności, a w wynikowej kolumnie zrobiłem warunek sprawdzający czy litera z H i J jest taka sama, a następnie JEŻELI + WYSZUKAJ PIONOWO z bazy i przyrównałem czy Kolumna B jest większa od tej średniej czy mniejsza. Jeżeli mniejsza to zwraca wynik z H, jeżeli większa to zwraca wynik z J

Ma to ręce i nogi czy totalnie zepsułem wszystko?

[Jermor]

Przesyłam swoją propozycję rozwiązania. W tym ceku wyodrębniłem jednak dwie dodatkowe kolumny, które mogą być ukryte w funkcjonującym skoroszycie, jedna zawiera wyodrębnione litery z bazy, druga — wartości liczbowe.
Sama formuła jest formułą macierzową, wylicza ona moduł różnicy między liczbą a każdą wartością z bazy danych, przy czym jeśli wartość dotyczy pozycji o innej literze niż przypisana liczbie, ta różnica jest powiększana o 1000. W wektorze różnic odnajdywana jest najmniejsza, i jest ona następnie identyfikowana jako pozycja w tych różnicach. Pozycja odpowiada jednocześnie położeniu poszukiwanej wartości w wektorze odseparowanych liczb, więc wykorzystując funkcję INDEKS() można ją podać. Należy wiedzieć, że ten sposób, w przypadku gdy różnica między liczbą a wartością z bazy jest taka sama między dwoma liczbami, zwraca wartość mniejszej z nich. Wynika to z działania funkcji PODAJ.POZYCJĘ().
W rozwiązaniu nadałem nazwę "wynik" obszarowi zawierającemu wyodrębnione wartości liczbowe z bazy.
Dodam także, że gdybyś zaczął korzystać z LibreOffice dostałbyś w gratisie kilka przydatnych funkcji niedostępnych w Apache OpenOffice, np. MIN.WARUNKÓW(), REGEX(), JEŻELI.BŁĄD() i jeszcze kilka innych.

[Jermor]

Zmodyfikowałem nieco algorytm. Tak, aby w sytuacji gdy liczba leży dokładnie w środku między zakresem, wybierana była wartość większa.
Tworzone są dwa pomocnicze obliczenia, przede wszystkim po to, żeby nie tworzyć formuły bardzo długiej i trudnej przez to do analizy.
Pierwotne obliczenie wyniku stało się pierwszym obliczeniem pomocniczym. Druga (nowa i zarazem pomocnicza) formuła znajduje drugie minimum w wektorze. Trzecia, nowa formuła, wyznacza potrzebną wartość wynikową. Jeżeli oba minima są takie same, to do pierwszej wartości minimalnej należy dodać dwukrotność różnicy między nią a liczbą.
Gdybyś jednak uznał, że mniejsza wartość jest tą pożądaną, wystarczy w miejsce wynikowe wstawić pierwszą formułę a resztę usunąć.
W tym rozwiązaniu wyniki także nie zależą od kolejności wartości w bazie.

[hezer]

Wygląda na to, że działa. Chyba będę potrzebował chwili żeby zrozumieć sposób działania bo wydaje mi się to skomplikowane, ale grunt że działa!
Dziękuję