[SOLVED] WYSZUKAJ.PIONOWO - problem z wyszukiwaniem tekstu

[Vakarian]

Postanowiłem uprościć sobie życie i zamiast wpisywać długie formuły najeżone warunkami JEŻELI, skorzystać z funkcji WYSZUKAJ.PIONOWO. Kiedyś miałem z nią problemy, więc na jakiś czas odłożyłem ją na bok, jednak bez niej... Cóż, bez niej wszystko jest bardziej czasochłonne, dlatego też postanowiłem dopytać, dlaczego formuła ta "niedomaga" w niektórych sytuacjach.

W arkuszu, który załączam, komórka A3 stanowi listę wyboru miesiąca, komórka A1 zaś powinna wyszukiwać, na podstawie wybranego miesiąca, przyporządkowaną mu liczbę. Niby wszystko fajnie, tyle że funkcja przy niektórych miesiącach zwraca właściwą liczbę, przy innych złą, a przy bodajże dwóch #N/D. Co jest tego przyczyną?

[Husar]

właściwa formuła to:

Kod: Zaznacz cały

=WYSZUKAJ.PIONOWO(A3;C1:D12;2;0)

[Vakarian]

Dzięki wielkie za rozwiązanie - teraz działa bez zarzutu. Nurtuje mnie jednak pytanie, dlaczego "porządek sortowania" odgrywa takie znaczenie - czy funkcja nie powinna po prostu odnaleźć danej wartości, dajmy na to czerwiec, i zwrócić zawartość odpowiedniej, dopasowanej do niej komórki?

[Jan_J]

Porządek we wpisach ma znaczenie.
Wszyscy, którzy pamiętają papierowe książki telefoniczne, wiedzą, że poszukując np. Macina Kowalskiego z ul. Kolejowej 13 nie czytamy wszystkich wpisów od początku do skutku, tylko staramy się jak najszybszym sposobem dotrzeć w rejon rejestru, w którym taki wpis mógłby się znajdować. Z kolei poszukując w tej samej książce właściciela numeru 317735120 złapiemy się za głowę, bo nie widzimy innego sposobu niż czytanie wszystkiego po kolei.
Wniosek: przekonanie, że spis jest uporządkowany, skłania do zastosowania bardziej wydajnych metod przeszukiwania. Jeśli takie przekonanie jest mylne, mamy sporą szansę na niepowodzenie w poszukiwaniach. W przypadku funkcji wyszukujących arkusza, nie sprawdzają one, czy spis jest uporządkowany, tylko w zależności od ostatniego argumentu (+1, 0 albo -1) reagują zastosowaniem odpowiedniej metody. Sprawdzanie uporządkowania za długo by trwało, dłużej niż samo wyszukiwanie.

[Jermor]

Uzupełniając wypowiedź Jana_J.
Jeśli funkcji zasygnalizowano posortowane wartości pierwszej kolumny, a poszukiwanej wartości nie będzie na tej liście, funkcja przyjmie za znalezioną najbliższą leżącą poniżej. Gdy np. wartości tej listy zapisano tak: 10, 20, 30 40 a poszukiwana wartość to 35 funkcja przyjmie, że znaleziono 30. Jeśli listę zapisano tak: 10, 20, 40, 30 to funkcja uzna za znalezioną wartość 20 (bo jeśli zasygnalizowano uporządkowanie to napotkanie wartości 40 oznacza, że nie trzeba dalej przeglądać listy). Przy wartościach uporządkowanych nie można znaleźć wyniku dla poszukiwanej wartości mniejszej od pierwszej wartości z listy (#N/D).
Jeśli funkcji zasygnalizowano brak uporządkowania, to poszukiwana wartość musi znajdować się na liście. Jeśli takiej nie będzie pojawi się #N/D.
Tak więc gdy lista ma postać: 10, 20, 30, 40 a poszukiwana wartością jest 35 zwrócona zostanie wartość #N/D.

[Husar]

Dzięki szanownym kolegom Jan_J i Jermor za wyjaśnienia. Mi się nie chciało oraz nie mam smykałki nauczycielskiej jak koledzy.

[Vakarian]

Ogromnie dziękuję za obszerne wytłumaczenie, ale...

Jeśli funkcji zasygnalizowano brak uporządkowania, to poszukiwana wartość musi znajdować się na liście. Jeśli takiej nie będzie pojawi się #N/D.
Tak więc gdy lista ma postać: 10, 20, 30, 40 a poszukiwana wartością jest 35 zwrócona zostanie wartość #N/D.

W moim przykładzie nawet wyszukując wartości, które znajdują się na liście, funkcja zwraca #N/D. Zauważyłem, że w przypadku wyszukiwania tekstu problemy są dość częste. Zrozumiałbym jeszcze, gdyby wpisy na liście były podobne, np. różniły się jedynie końcówką słowa, ale maj od lutego różni się bardzo, a w obu przypadkach funkcja zwraca wartość 2 (przypisaną do lutego).

[Jan_J]

Prawdopodobnie wyszukujesz z ostatnim parametrem 1 (być może zadanym domyślnie, niejawnie), a wpisy tak naprawdę nie są uporządkowane ALFABETYCZNIE albo NUMERYCZNIE. Dopisz ;0 jako ostatni, czwarty argument.
W przypadku założenia uporządkowania NAPRAWDĘ się z tego założenia korzysta. Szukasz Kowalskiego, otwierasz wykaz na środkowej stronie, a tam Maliniak. Więc odrzucasz całą drugą połowę, bo wiesz, że Kowalski musi być wcześniej. Ale jeśli faktycznie uporządkowanie nie ma miejsca, narażasz się tym samym na odrzucenie tego, czego szukasz.

styczeń, luty, marzec -- to nie jest porządek alfabetyczny.

[Jermor]

Wyszukuje prawidłowo. W funkcji ostatni parametr pominięty oznacza przyjęcie go jako PRAWDA, zatem wyszukiwanie słowa Maj napotyka na słowo Marzec w którym trzecia litera informuje, że poszukiwanie jest już poza zakresem (r>j), więc zwraca najbliższy poniżej czyli Luty.

[Jermor]

Powyżej wypowiedziałem się co do działania funkcji. Jednak nie do końca mam rację wyjaśniając ostatni post Vakariana.
Gdy przeszukiwana kolumna zawiera teksty i są one uporządkowane rosnąco, funkcja zwraca wartości zgodnie z oczekiwanymi wtedy gdy czwarty argument sygnalizuje uporządkowanie.
Np gdy uporządkuje się nazwy miesięcy alfabetycznie, to m. in. po marcu występuje październik. Gdy wyszukiwanym tekstem jest np litera "n" to zwrócone zostaną dane dla marca, bo to on poprzedza bezpośrednio tę literę.
Gdy dane nie są uporządkowane i jednocześnie czwarty argument sygnalizuje, że są (a to jest domyślnie ustawiona wartość argumentu, gdy zostanie pominięty, [może też przyjąć wartość PRAWDA albo 1]) wyniki są całkowicie nieoczekiwane a przynajmniej ja nie potrafię wyjaśnić przyczyny takich wyników. Nie zadziała tu zasada, tak jak w przypadku nieuporządkowanych wartości liczbowych, że skoro poszukiwana wartość jest większa od przeglądanej w kolumnie to podawana jest wartość najbliższa poniżej.
Dlatego dla tekstów lub wartości liczbowych nieuporządkowanych zawsze należy w funkcjach podać ten czwarty argument. Musi on być wpisany jako FAŁSZ albo 0.

[Jan_J]

Zawsze wierzyłem, że w przypadku założenia uporządkowania stosowany jest algorytm typu bisekcji.
Nawet zacząłem pisać odpowiedź, ale w próbach mi się nie zgadzało, więc wstrzymałem, żeby przemyśleć.
W każdym razie dla wyszukiwania w ciągu liczb wygląda to jak bisekcja. Jak jest dla tekstów, nie wiem, choć nie widzę powodu, by miało być inaczej. A jednak coś się nie zgadza.
Być może chodzi o to, że nazwy miesięcy tworzą listę; coś w rodzaju typu wyliczeniowego z ustalonym porządkiem wartości. Muszę to przemyśleć i poeksperymentować.

Niezależnie od tego, nie należy ufać wynikom wyszukiwania, kiedy sekwencja nie jest uporządkowana, a algorytm zakłada, że jest.

[Vakarian]

Jeszcze raz OGROMNIE dziękuję za wyjaśnienia - jednak stwierdzenie "szukajcie, a znajdziecie" się sprawdza, zwłaszcza gdy na forum są tacy specjaliści jak Wy, Panowie.

Pozwolę sobie zadać jeszcze jedno pytanie, również dotyczące funkcji WYSZUKAJ.PIONOWO, mianowicie: w arkuszu, który zamieszczam w załączniku, mam listę władców wraz z datami granicznymi ich rządów. Daty te zamieniane są na jedną liczbę, w celu dokładniejszego wyszukiwania władcy, który rządził w danym okresie (a dokładniej, konkretnego dnia). Komórka C6 arkusza Cykle i kadencje wyszukuje pionowo władcę na podstawie tej właśnie liczby, utworzonej z daty początku jego rządów. Problem jest taki, że funkcja zwraca dobre wartości jedynie, gdy w macierzy funkcji podam przedział od A3 do maksymalnie O40. Jeżeli wpiszę O41 lub większą, to funkcja zwraca #N/D. Próbowałem różnych rzeczy, ale nic nie pomogło... Co może być przyczyną takiego zachowania funkcji?

[Jermor]

Vakarianie szanowny
Przypomniałem sobie, że na temat funkcji WYSZUKAJ.PIONOWO dość obfitą korespondencję już prowadziliśmy na łamach tego forum. Wszystko to, o czym tu pisał Jan_J jak i ja było już poruszone i wyjaśniane.
Przyjrzałem się Twemu projektowi i nie tyle, że nie potrafię Ci pomóc, co niechęć do analizowania Twego zawiłego zamysłu sprawiła, że przedstawiam Ci inną koncepcję.
Znam Twoje zdanie, żeś uparty i wdrażać chcesz swoje pomysły ale zastanów się nad proponowanym rozwiązaniem.
Arkusz "Władcy" składa się teraz z trzech kolumn. W pierwszej wpisana jest data początku rządów (temu obszarowi nadałem nazwę "DP"), w drugiej data końca rządów (ten obszar otrzymał nazwę "DK") i w trzeciej nazwiska władców (obszar o nazwie "Władca").
Arkusz "śmieci" nie jest potrzebny.
Arkusz "Cykle i kadencje" zreorganizowałem tak, że wpisać trzeba konkretną datę do komórki F2. Jest ona kontrolowana pod kątem poprawnej daty.
Ten sposób jest lepszy od zastosowanego przez Ciebie. W twoim rozwiązaniu np. kontrolowałeś dzień, by była to liczba całkowita z przedziału 1 do 31. A co wobec tego z miesiącami, które mają tylko 30, 28 albo 29 dni?
Wszystkie daty należy wpisywać w postaci dd.mm.rrrr Założyłeś, że dopuszczalnymi są lata od 1 do 2819. Z tego względu pierwszą datę zakresu należy wprowadzić tak: 1.1.0001.
Na podstawie daty w F2 wyznaczane są wartości dnia, miesiąca, roku, dnia tygodnia. Oraz wyznaczana jest pozycja tej daty w obszarze DP i pozycja w obszarze DK. Obie te liczby wyświetlane są w komórkach A2 i A3. Są to obliczenia pomocnicze tylko po to aby nie komplikować formuł w komórkach C6, D7, D8, D9.
Na podstawie wyznaczenia pozycji w tych obszarach określany jest władca lub stwierdzenie, że data znajduje się w okresie bezkrólewia.
W arkuszu "Cykle i kadencje" niechroniona jest tylko komórka F2, więc przy włączeniu ochrony tego arkusza, tylko do niej można wpisać cokolwiek.
Całość projekty podsyłam w załączniku.
Arkusz "Cykle..." jest chroniony bez hasła.

[Vakarian]

Dziękuję Ci, Jermor, za rozwiązanie. Działa ono mniej więcej dobrze, lecz nie wyświetla imienia pierwszego władcy ("1"), tylko zwraca #N/D. Niestety, jako żem uparty, nie do końca mnie ono satysfakcjonuje. Nie chodzi o sam fakt, że to nie moje rozwiązanie - chciałbym po prostu wiedzieć, przed spisaniem mojego rozwiązania na straty, dlaczego nie funkcjonuje ono w sposób, jaki sobie zamierzyłem (i wyciągnąć jakieś wnioski na przyszłość). Dodatkowo nazwy miesięcy, dni tygodnia etc. zmieniłem w przykładzie na stosowane powszechnie, żeby uczynić go bardziej przejrzystym i nie objawiać swoich pomysłów światu (jeszcze zbyt wcześnie na to). W "swoim" arkuszu posługuję się nazwami własnego autorstwa, dlatego chciałbym, aby wyświetlana była właśnie nazwa miesiąca, a nie zapis liczbowy - dużo łatwiej jest mi się wtedy połapać oraz, nie ukrywam, jest to dużo milsze dla oka.

niechęć do analizowania Twego zawiłego zamysłu

Kurcze, a mnie wydawał się genialny w swojej prostocie. Jedna liczba utworzona na podstawie roku, miesiąca i dnia, według której funkcja wyszukiwałaby odpowiednie wartości. Cóż, następnym razem przemyślę takie rozwiązanie dwa razy, zanim przemodeluję cały arkusz... Dobrze, że zostawiłem kopię.

PS Nie mylisz się, kiedyś już pisaliśmy o tejże funkcji (bardziej Ty pisałeś, ja zadawałem pytania i marudziłem ), jednak nie wszystko, co zostało napisane teraz, napisano wtedy.

[Jermor]

Funkcja PODAJ.POZYCJĘ analogicznie jak WYSZUKAJ.PIONOWO także zawiera argument (trzeci) sygnalizujący jak ma być traktowany przeszukiwany obszar. Jego brak oznacza przyjęcie wartości 1 czyli założenie, że wartości przeszukiwane są uporządkowane narastająco.
(To jest m. in. warunek funkcjonowania zaproponowanego rozwiązania.)
Wskazana wartość jest poszukiwana w obszarze i jeśli zostanie znaleziona zwracana jest jej pozycja względem początku obszaru. Jeśli wartości nie ma, podawana jest pozycja najbliższego wyniku poniżej. To właśnie było przyczyną niepoprawnego działania. Aby wykryć, czy data należy do jakiegoś obszaru rządów trzeba znaleźć dwie pozycje daty, jedną względem dat początkowych i drugą względem dat końcowych. Przy analizowaniu dat końca rządów dla pierwszego okresu, wybrana data jest zawsze mniejsza od daty końcowej tego okresu. Funkcja powinna zwrócić wartość poprzednią. Jej jednak nie ma bo jest to najmniejsza wartość w tym obszarze i zwraca #N/D. Pojawienie się takiego wyniku powoduje, że wszystkie obliczenia wykorzystujące taka komórkę (nawet pośrednio) także zwracają wynik #N/D.
Ale należało rozwiązać jeszcze jeden problem. Lista władców zaczyna się od daty 2.12.1821 a kończy na 30.12.2460, natomiast dopuszcza się wprowadzanie dat od 1.1.0001 do 31.12.2819, czyli dat poprzedzających pierwszą datę z wykazie i występujących po ostatniej dacie. One także powodowałyby błędy.
I to właśnie także uwzględniłem w poprawionym rozwiązaniu.
Zwróć uwagę, że algorytm sam interpretuje okresy bezkrólewia.
Jeszcze co wymyślonych własnych nazw stosowanych w projekcie. Do nich właśnie można zastosować omawianą już funkcję WYSZUKAJ.PIONOWO.

[Vakarian]

Nie pozostaje mi nic innego, tylko ponownie podziękować. Jest jednak jeszcze kwestia - poprawność dat wpisywanych w komórce F2. Komórka ta opiera się na kalendarzu gregoriańskim, który rozmija się z tym, stworzonym przeze mnie, np. w moim kalendarzu drugi miesiąc ma 30 dni, lecz w komórce F2 takiej daty wpisać nie mogę.

Co się tyczy listy władców, to będzie ona dużo dłuższa i będzie obejmowała okres od roku 1821 do 2819. Możliwe będzie jedynie wpisanie daty wcześniejszej, lecz w takim przypadku odpowiednia komórka poinformuje, że jest to okres przed powstaniem królestwa.

PS Wybacz, Jermorze, że kolejne informacje o stosowanym przeze mnie kalendarzu podaję tak "szczątkowo", zamiast napisać to wszystko na samym początku. W moim pierwotnym zamyśle nie miało to aż takiego znaczenia i nie pomyślałem o tym.

[Jermor]

Zatem wracamy do arkusza roboczego i konieczności przypisania datom własnych identyfikatorów.
Najlepiej gdy data odpowiadałaby kolejnej liczbie poczynając od 1 dla pierwszego dnia i zwiększała się co 1 dla każdego następnego, tworząc tak zwaną liczbę daty. Tak zresztą działają daty w Calcu.
Podsyłam Ci kolejny wariant. Tym razem utworzyłem własny kalendarz. Możesz go sobie zmodyfikować, bowiem kalendarz składać się może z dowolnej liczby miesięcy (ja wpisałem ich 13) oraz każdy Twój miesiąc może mieć przypisaną odrębną liczbę dni. Zwróć uwagę na dwie kolejne kolumny kalendarza. Są potrzebne do właściwego obliczania dat. Utworzyłem też własny system tygodni, który także możesz rozbudować (ja zastosowałem tydzień ośmiodniowy).
Tym obszarom przypisałem nazwy i wykorzystałem je w tworzonych formułach, dlatego powiększając obszary rób to wewnątrz nich. Wówczas nazwy obejmą nowe granice. Wykaz zastosowanych nazw znajduje się w arkuszu roboczym.
Arkusz "Władcy" powrócił do poprzedniego konceptu. Ponieważ funkcjonuje własny kalendarz, to trzeba podać wszystkie elementy daty aby wyliczyć liczbę daty.
Wszystkie elementy wprowadzane do arkusza są kontrolowane. Zatem rok musi być liczbą całkowitą z przedziału 1-2819, miesiąc wybierany z listy, a dzień liczbą całkowitą z przedziału od 1 do największej liczby w danym miesiącu. Dlatego dobrze byłoby wprowadzań najpierw miesiąc, potem dzień. Po jego lewej stronie wyliczane są liczby daty dla początku i końca okresu rządów.
Rozbudowany arkusz Roboczy pozwala uprościć budowę formuł poprzez wykonywanie obliczeń cząstkowych.
To jest tylko pomysł, dalej działaj sam.
Powodzenia.

[Vakarian]

Wiem, że się powtarzam, ale po raz kolejny dziękuję. Nie przeglądałem jeszcze podesłanego przez Ciebie arkusza, ale gdy będę miał więcej wolnego czasu, to dokładnie go przeanalizuję i postaram się dostosować do moich potrzeb. Za powodzenia nie dziękuję, żeby nie zapeszyć.