Wyszukaj.pionowo dla wartości między min a max

Użytkowanie arkusza kalkulacyjnego
Pysio
Posty: 58
Rejestracja: czw mar 12, 2020 11:39 am

Wyszukaj.pionowo dla wartości między min a max

Post autor: Pysio »

Mam coś takiego:

https://prnt.sc/10nbomf

I będę wprowadzał dł. np. 1,5 czyli znajduje się w przedziale dla wiersza 4.

Jak zrobić pierwszą część formuły wyszukaj.pionowo aby jako wartość szukaną wskazywało mi wartości pomiędzy min a max?

Tj. jeśli wprowadzę 1,5 to wartością szukaną ma być przedział między 1 a 2?
Tutaj oczywiście jest to mega uproszczone, ale sam w sobie cennik jest dość skomplikowany.

Myślałem o dodaniu komórki pomocniczej, wyznaczającej przedział i dostosowaniu przedziału, ale czuję, że da się to zrobić wydajniej.

Myślałem też aby zrobić, że jeżeli wartość jest niższa od max to powinna się znaleźć w danym przedziale, ale to znów będzie sporo klepania tych przedziałów.
OpenOffice 4.1.7 / LibreOffice 6.4.4.2 na Windows 10
Awatar użytkownika
Jermor
Posty: 2239
Rejestracja: sob paź 12, 2013 11:09 am
Kontakt:

Re: Wyszukaj.pionowo dla wartości między min a max

Post autor: Jermor »

Funkcja WYSZUKAJ.PIONOWO(), przy opuszczeniu czwartego argumentu, zakłada, że pierwsza kolumna obszaru przeszukiwania jest posortowana narastająco. W związku z tym już z definicji poda wynik przypisany do podanej wartości, a jeśli takiej wartości nie ma, to przypisanej do najbliższej podanej mniejszej od poszukiwanej.
Zatem twoja tabela powinna wyglądać mniej więcej tak:

Kod: Zaznacz cały

1,000	100	110	120
2,001	  0	  0	  0
3,000	200	220	230
4,001	  0	  0	  0
5,000	300	330	330
6,001	  0	  0	  0
Dla wartości od 1 do <2,001 uzyskasz dane jak dla 1.
Dla wartości od 2,001 do 2,99999... uzyskasz 0, bo w tym przedziale nie może wystąpić poszukiwana wartość.
Dla wartości od 3 do <4,001 uzyskasz dane jak dla 3.
itd.
Musisz zwrócić uwagę na to, że w tym przypadku najmniejsza możliwą wartością jest 1. Przy poszukiwaniu wartości mniejszej niż najmniejsza z podanych, wynikiem jest błąd (#N/D). Wszystkie wartości większe od 6 lub równe 6, zwrócą wyniki dla 6.

Innym rozwiązaniem byłoby zbudowanie formuły, która na podstawie wpisanej wartości wyliczałaby, w jakim przedziale się ona znajduje, i jej wynik byłby pierwszym argumentem w funkcji: WYSZUKAJ.PIONOWO().
Zakładając, że poszukiwana wartość znajduje się w komórce A1 a przedziały wyglądają tak jak je określiłeś w przykładzie, formuła mogłaby wyglądać następująco:

Kod: Zaznacz cały

=PRZEŁĄCZ(CZY.PARZYSTE(A1)+2*(LICZBA.CAŁK(A1)=A1);1;0;3;LICZBA.CAŁK(A1)-1;LICZBA.CAŁK(A1))
Wyznacza ona liczbę nieparzystą, będącą dolną granicą przedziału, przy czym do tego przedziału zalicza także liczbę całkowitą parzystą, będąca górną granicą tego przedziału. Niecałkowite liczby parzyste generują wartość 0, gdyż nie powinny wystąpić.
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: Wyszukaj.pionowo dla wartości między min a max

Post autor: Jermor »

Dopiero teraz spojrzałem na stopkę w twoim poście. Funkcja PRZEŁĄCZ() niestety nie jest dostępna w Apache OpenOffice. Jest tylko w LibreOffice.
Zamiast niej można zaadaptować funkcję WYBIERZ(), wykorzystując zmodyfikowaną formułę obliczeniową:
CZY.PARZYSTE(A1)+2*(LICZBA.CAŁK(A1)=A1)+1
Ta formuła wyliczy wartość od 1 do 4, która będzie pierwszym argumentem funkcji.
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.
Pysio
Posty: 58
Rejestracja: czw mar 12, 2020 11:39 am

Re: Wyszukaj.pionowo dla wartości między min a max

Post autor: Pysio »

Jermor, dzięki za odpowiedź. Poprawiłem już stopkę, od jakiegoś czasu przesiadłem się na Libre.

Zerknę na funkcję przełącz, myślę, że będzie dla mnie odpowiedniejsza. Cennik jest w oparciu o długości i nie chcę go za bardzo rozbudowywać bo ew. zmiany w nim byłyby dla mnie czasochłonne.
Przyjrzę się funkcji przełącz, z grubsza widzę, że zrozumiałeś moją koncepcję i powinno zadziałać.


EDIT: Możesz podesłać jakiegoś helpa odnośnie funkcji =przełącz() ? Wygląda to super, wydaje mi się, że może mi się to przydać w przyszłości, a chciałbym lepiej zrozumieć tę funkcję.


EDIT2: Ok, działa to świetnie z liczbami całkowitymi, i mój przykład rzeczywiście pasuje do tego. Zagwozdkę mam teraz, ponieważ u mnie min i max nie są liczbami całkowitymi a liczbami z miejscami dziesiętnymi. np. 5,1 - 5,4


EDIT3: Na razie zrobiłem to za pomocą funkcji jeżeli:

Kod: Zaznacz cały

=JEŻELI(I(A1>=A4;A1<=B4);A4;JEŻELI(...))
Na zasadzie kilku warunków. Nie wygląda to zbyt estetycznie, zwłaszcza jak pobieram dane z innego pliku, ale nie mam za bardzo czasu aby się nad tym skupiać. Działa :)
OpenOffice 4.1.7 / LibreOffice 6.4.4.2 na Windows 10
Awatar użytkownika
Jermor
Posty: 2239
Rejestracja: sob paź 12, 2013 11:09 am
Kontakt:

Re: Wyszukaj.pionowo dla wartości między min a max

Post autor: Jermor »

No więc tak, samą funkcję PRZEŁĄCZ() i inne funkcje warunkowe opisałem w tym opracowaniu https://yestok.pl/lbo/y63.php.
Natomiast co do twojego projektu: proponuję inne rozwiązanie, które ci zresztą załączam.
Twojemu cennikowi przypisałem nazwę "cennik". Jak zobaczysz, obejmuje ona także wiersz nagłówkowy cennika.
Cennik w dwóch pierwszych kolumnach zawiera długości minimalne i maksymalne, między którymi powinna znajdować się wyszukiwana długość, aby pobrane zostały dane z tego wiersza.
Z podanego przez ciebie przykładu, a niczego nie zmieniłeś w kolejnym poście, wynika, że istnieją wartości niepasujące do żadnego przedziału, np. 2,3. Dlatego formuła wyliczająca numer przedziału jest bardziej złożona.
Napisałem ją jako formułę macierzową:

Kod: Zaznacz cały

=JEŻELI.BŁĄD(PODAJ.POZYCJĘ(1;(A9>=B3:B5)*(A9<=C3:C5);0);0)
czyli zatwierdzaną skrótem klawiaturowym CTRL+SHIFT+ENTER.
Ta formuła wylicza, na której pozycji w cenniku (wyłączając wiersz tytułowy) znajduje się poszukiwana wartość. Gdy wartość nie znajduje się w żadnym podanym w cenniku przedziale, otrzymywany jest wynik #N/D.
Dlatego wykorzystałem funkcję JEŻELI.BŁĄD() (także dostępną tylko w LibreOffice), aby w takim przypadku wynikiem było 0.
Same wartości z cennika są teraz pobierane przy pomocy funkcji INDEKS(), w której numerem wiersza jest wynik formuły okazanej powyżej powiększony o 1. Dzięki temu gdy długość zostanie podana spoza zakresu (#N/D) zostaną pobrane napisy z nagłówków cennika (pierwszy wiersz). A gdy wartość znajduje się w jakimkolwiek przedziale, to jest to numer wiersza w tablicy "Cennik".
Załączniki
pysio.ods
(11.21 KiB) Pobrany 134 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.
ODPOWIEDZ