Sczytywanie ostatniej wartości z kolumny [SOLVED]

Użytkowanie arkusza kalkulacyjnego
Mental
Posty: 21
Rejestracja: sob cze 06, 2020 8:45 pm

Sczytywanie ostatniej wartości z kolumny [SOLVED]

Post autor: Mental »

Dzień dobry,

Jaką funkcję zastosować, aby w komórce B1 pojawiała się zawsze ostatnia istniejąca wartość z zakresu A1:A10? Jak w przykładzie poniżej:
Obrazek

Przykładowo: jeśli skasuję wartość 54, to w komórce B1 pojawi się wartość 3.

Na anglojęzycznych forach znalazłem sugestię użycia funkcji LOOKUP (WYSZUKAJ), ale nie jestem pewien czy się nada. Poza tym jej składnia jest dość enigmatyczna dla kogoś korzystającego z Calc z doskoku, oficjalny help nie jest bardziej pomocny.

Z góry dziękuję za pomoc
Ostatnio zmieniony sob kwie 09, 2022 12:46 pm przez Mental, łącznie zmieniany 2 razy.
Version: 7.5.6.2 (x64)
Awatar użytkownika
Rafkus
Posty: 513
Rejestracja: czw kwie 12, 2018 10:26 pm

Re: Sczytywanie ostatniej wartości z kolumny

Post autor: Rafkus »

Kod: Zaznacz cały

=INDEKS($A$1:$A$10; MAKS(JEŻELI($A$1:$A$10=""; ""; WIERSZ($A$1:$A$10))))
Jest to funkcja macierzowa dlatego należy ją zatwierdzić po wpisaniu wciskając jednocześnie klawisze: CTRL+SHIFT+ENTER
Funkcja indeks zwraca z pewnego obszaru $A$1:$A$10 z wiersza wyznaczonego przez funkcję MAKS określoną wartość.
Funkcja JEŻELI tworzy dla funkcji MAKS listę pozycji na których jest coś wpisane, a MAKS wybiera największą wartość czyli ostatnią pozycję.
LibreOffice 7.4.6 (preferowany) oraz OpenOffice 4.1.6. Widows 10
OpenOffice 4.1.3. oraz Libre 4.2.5.2 Windows XP
Mental
Posty: 21
Rejestracja: sob cze 06, 2020 8:45 pm

Re: Sczytywanie ostatniej wartości z kolumny

Post autor: Mental »

Dziękuję. Niestety ta funkcja zwraca mi wartość 0. Użyłem za to innej, podpatrzonej na forum:

Kod: Zaznacz cały

=WYSZUKAJ(2;1/CZY.LICZBA(A$1:A$10);A$1:A$10)
Ta funkcja spełnia swoje zadanie, aczkolwiek nie wiem dlaczego, nie jestem w stanie ogarnąć składni.
Version: 7.5.6.2 (x64)
Awatar użytkownika
Rafkus
Posty: 513
Rejestracja: czw kwie 12, 2018 10:26 pm

Re: Sczytywanie ostatniej wartości z kolumny

Post autor: Rafkus »

Całkiem sprytne rozwiązanie
Funkcja WYSZUKAJ poszukuje w pewnym zbiorze danych wartości 2, której nigdy nie znajdzie, bo zbiorem liczb jest wynik działania 1/(0 lub 1) a więc w zbiorze jest albo 1 albo błąd z dzielenia przez 0.
A skoro funkcja WYSZUKAJ nie znajduje 2 to zwraca wartość z ostatniej 1 - ??? ignorując pozycje z wynikiem 1/0 ???
LibreOffice 7.4.6 (preferowany) oraz OpenOffice 4.1.6. Widows 10
OpenOffice 4.1.3. oraz Libre 4.2.5.2 Windows XP
Mental
Posty: 21
Rejestracja: sob cze 06, 2020 8:45 pm

Re: Sczytywanie ostatniej wartości z kolumny

Post autor: Mental »

Ciekawe. Nie wiem tylko skąd wynika, że jeśli wartość 2 nie zostanie znaleziona, to w komórce B1 zwrócona zostanie wartość 54 z komórki A6.

Dokumentacja mówi: "Jeśli polecenie WYSZUKAJ nie odnalazło informacji odpowiadających kryteriom wyszukiwania, zwraca największą wartość w przeszukiwanym wektorze mniejszą lub równą kryterium wyszukiwania."
Czy powinno zwrócić błąd, ponieważ żadna z liczb nie jest mniejsza lub równa od 2?
Version: 7.5.6.2 (x64)
Awatar użytkownika
Jermor
Posty: 2239
Rejestracja: sob paź 12, 2013 11:09 am
Kontakt:

Re: Sczytywanie ostatniej wartości z kolumny [SOLVED]

Post autor: Jermor »

Rozwiązanie @Rafkusa wymagałoby jednak pewnej modyfikacji, ze względu na to, że działa poprawnie pod warunkiem, że rzeczony zakres zaczyna się od pierwszego wiersza w arkuszu. Jeśli taki zakres danych zaczyna się w dowolnym wierszu, to ogólną formułą byłaby

Kod: Zaznacz cały

=INDEKS(A1:A10; MAKS(JEŻELI(A1:A10=""; ""; 1+WIERSZ(A1:A10)-WIERSZ($A$1))))
To oczywiście jest formuła macierzowa.

Można też napisać taką formułę (w tym przypadku dotyczy ona tylko danych liczbowych)

Kod: Zaznacz cały

=WARTOŚĆ(REGEX(POŁĄCZ.TEKSTY(";";1;A1:A10);"(-*[:digit:]+;)*";""))
W tym przypadku jest to zwykła formuła.
Funkcja POŁĄCZ.TEKSTY() łączy w jeden tekst zawartość wszystkich komórek niepustych, rozdzielając liczby średnikiem.
Funkcja REGEX() zamienia każdy ciąg cyfrowy (z uwzględnieniem liczb ujemnych i z częścią dziesiętną) zakończony średnikiem na ciąg pusty. W efekcie jedynym ciągiem liczbowym niezawierającym kończącego go średnika jest ostatni człon tego ciągu. Ponieważ funkcja REGEX() zwraca wartość tekstową, zastosowana jest dodatkowa funkcja WARTOŚĆ().

Jeśli chodzi o funkcję WYSZUKAJ(), to ta funkcja zakłada, że dane drugiego argumentu są posortowane rosnąco. Ten argument jest wewnętrznie utworzonym wektorem zero-jedynkowym, w którym 0 odpowiada zawartości nieliczbowej a jedynka zawartości liczbowej. Działanie: 1/CZY.LICZBA() ma na celu zamianę tego wektora na układ jedynek i komunikatu #DZIEL0! Jest to niezbędne, gdyż przy wartościach zero-jedynkowych WYSZUKAJ() nie potrafi zinterpretować sortowania. Ponieważ 2 i w zasadzie każda inna liczba, nie zostanie znalezione, to zwrócona jest zawartość wektora opisanego argumentem trzecim na pozycji ostatniej znalezionej "największej" jedynki. Jeśli w argumencie drugim nie pojawi się żadna liczba, (co wygeneruje wektor samych zer), wynikiem formuły będzie #N/D

Metoda @Rafkusa jest najbardziej ogólna, bo dotyczy każdego rodzaju wpisu. Ale i tu pojawia się następująca uwaga. Jeżeli dane przeszukiwanego wektora zawierają nie liczby, ale odwołania do innych komórek, to odwołanie do komórki pustej w tym wektorze wystawi 0, a to jest liczbą.
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: Sczytywanie ostatniej wartości z kolumny [SOLVED]

Post autor: Jermor »

Przyjrzałem się propozycji z REGEX() i można ją zmodyfikować następująco:

Kod: Zaznacz cały

=WARTOŚĆ(REGEX(POŁĄCZ.TEKSTY(";";1;A1:A15);"(.+;)*";""))
Co oznacza, że odnaleźć trzeba wszystkie ciągi zakończone średnikiem, nie ma potrzeby wyróżniania, że wyszukiwane są liczby. To upraszcza całą formułę. Po zrezygnowaniu z funkcji WARTOŚĆ() zwracany jest ostatni element w zakresie, niezależnie od jego typu.
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: Sczytywanie ostatniej wartości z kolumny [SOLVED]

Post autor: Jermor »

Kolejne zastanowienie się nad formułą REGEX-ową pozwoliło ponownie zweryfikować drugi argument tej funkcji (poszukiwany ciąg tekstowy) i ostatecznie formuła może wyglądać następująco:

Kod: Zaznacz cały

=WARTOŚĆ(REGEX(POŁĄCZ.TEKSTY(";";1;A1:A15);".*;";""))
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