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

Użytkowanie arkusza kalkulacyjnego
hezer
Posty: 7
Rejestracja: wt cze 23, 2020 2:47 pm

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

Post autor: 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.
Załączniki
BAZA.ods
(9.2 KiB) Pobrany 44 razy
bazascreen.png
bazascreen.png (6.3 KiB) Przejrzano 355 razy
Ostatnio zmieniony wt maja 23, 2023 7:45 am przez hezer, łącznie zmieniany 1 raz.
OpenOffice 4.1.7 na Windows 10
Jan_J
Posty: 4558
Rejestracja: pt maja 22, 2009 1:20 pm
Lokalizacja: Wrocław

Re: SZUKANIE NAJBLIŻSZEGO WYNIKU W BAZIE Z WARUNKIEM

Post autor: 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 41 razy
JJ
LO (7.6|24.2) ∙ Python (3.12|3.10) ∙ Unicode 15 ∙ LᴬTEX 2ε ∙ XML ∙ Unix tools ∙ Linux (Rocky|CentOS)
hezer
Posty: 7
Rejestracja: wt cze 23, 2020 2:47 pm

Re: SZUKANIE NAJBLIŻSZEGO WYNIKU W BAZIE Z WARUNKIEM

Post autor: 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?
Załączniki
BAZA.ods
(11.66 KiB) Pobrany 38 razy
OpenOffice 4.1.7 na Windows 10
Awatar użytkownika
Jermor
Posty: 2239
Rejestracja: sob paź 12, 2013 11:09 am
Kontakt:

Re: SZUKANIE NAJBLIŻSZEGO WYNIKU W BAZIE Z WARUNKIEM

Post autor: 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.
Załączniki
hezer BAZA.ods
(11.17 KiB) Pobrany 41 razy
AOO 4.1.15, LO 7.5.9 (x64) na Windows 10 64bit
Ważne!
Jeśli twój problem został rozwiązany, wróć do swojego pierwszego postu, przejdź do edycji i dopisz [SOLVED] w temacie.
Inni, którzy mają podobny problem, będą wiedzieli, że istnieje jego rozwiązanie.
Awatar użytkownika
Jermor
Posty: 2239
Rejestracja: sob paź 12, 2013 11:09 am
Kontakt:

Re: SZUKANIE NAJBLIŻSZEGO WYNIKU W BAZIE Z WARUNKIEM

Post autor: 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.
Załączniki
hezer BAZA_J.ods
(11.46 KiB) Pobrany 43 razy
AOO 4.1.15, LO 7.5.9 (x64) na Windows 10 64bit
Ważne!
Jeśli twój problem został rozwiązany, wróć do swojego pierwszego postu, przejdź do edycji i dopisz [SOLVED] w temacie.
Inni, którzy mają podobny problem, będą wiedzieli, że istnieje jego rozwiązanie.
hezer
Posty: 7
Rejestracja: wt cze 23, 2020 2:47 pm

Re: SZUKANIE NAJBLIŻSZEGO WYNIKU W BAZIE Z WARUNKIEM

Post autor: 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ę
OpenOffice 4.1.7 na Windows 10
ODPOWIEDZ