Aby znaleźć ostatnią niepustą wartość w Apple Numbers na Macu, użyj kolumny pomocniczej do sprawdzenia każdej komórki, a następnie użyj XLOOKUP, aby przeszukać wyniki od dołu do góry. Ta metoda obsługuje luki i zachowuje zero jako prawidłową wartość.

Zadanie polega na zwróceniu najniżej położonej zajętej komórki w kolumnie tabeli. „Ostatnia” oznacza pozycję wiersza, więc uporządkuj tabelę tak, jak chcesz jej używać. W harmonogramie projektu najniższy wpis oznacza najpóźniejszy etap tylko wtedy, gdy wiersze są ułożone w tej kolejności.

Przygotuj niewielki przykład z lukami

Aktualny internetowy podręcznik Apple dla Maca informuje, że dotyczy Numbers 15.4. Aby sprawdzić zainstalowaną wersję, wybierz Numbers → Informacje o Numbers. Poniższe formuły są zgodne z aktualną pomocą Apple dotyczącą funkcji. Podręcznik użytkownika Numbers dla Maca

Użyj jednej tabeli z pięcioma kolumnami i co najmniej ośmioma wierszami. Wpisz poniższe nagłówki w wierszu 1. Wprowadź przykładowe wartości w kolumnach A i B, pozostawiając wskazane komórki w kolumnie B rzeczywiście puste. Słowa „pozostaw puste” objaśniają konfigurację; nie wpisuj ich do arkusza kalkulacyjnego.

Wiersz A: Etap B: Liczba obiektów C: Pusta? D: Etykieta wyniku E: Wynik
1 Etap Liczba obiektów Pusta? Etykieta wyniku Wynik
2 Układ 12 Ostatnia liczba
3 Montaż pozostaw puste
4 Przegląd 18
5 Korekta 0
6 Wyrównanie pozostaw puste
7 Wykończenie 7
8 Rozszerzenie pozostaw puste

To przykładowa sekwencja liczby obiektów. Oczekiwana odpowiedź to 7 z komórki B7. Pustą komórkę B8 należy pominąć, podobnie jak luki w kolumnie. Wartość 0 w komórce B5 jest wpisem i musi pozostać uwzględniana.

Umieść wynik w komórce E2, poza kolumną źródłową i kolumną pomocniczą. Dzięki temu odpowiedź będzie oddzielona od komórek używanych do obliczeń.

Dodaj test pustej komórki dla każdego wiersza źródłowego

Kliknij C2 i wpisz pełną formułę:

=ISBLANK(B2)

Wpisanie = otwiera Edytor formuł; naciśnij Return, aby zatwierdzić formułę. Apple opisuje tę procedurę wprowadzania danych w przewodniku dotyczącym obliczania wartości w komórkach tabeli. Obliczanie wartości na podstawie danych w komórkach tabeli w Numbers na Macu

ISBLANK zwraca TRUE dla całkowicie pustej komórki, a w pozostałych przypadkach FALSE. W tej kolumnie pomocniczej FALSE oznacza zatem, że komórka źródłowa zawiera jakąś wartość. Spacja lub niedrukowalny znak są traktowane jako zawartość, nawet jeśli komórka wygląda na pustą. ISBLANK

Zaznacz C2 i przesuń wskaźnik nad krawędź zaznaczenia, aż pojawi się żółty uchwyt autowypełniania, a następnie przeciągnij go w dół do C8. Autowypełnianie kopiuje formuły do sąsiednich komórek. Użyj pustej kolumny pomocniczej, ponieważ autowypełnianie nadpisuje istniejącą zawartość komórek docelowych. Autowypełnianie komórek w Numbers na Macu

Po wypełnieniu sprawdź C3 i C8: powinny odwoływać się odpowiednio do B3 i B8. Dla podanego przykładu komórki C2:C8 powinny zawierać następujące wyniki:

Komórka pomocnicza Oczekiwany wynik Przyczyna
C2 FALSE B2 zawiera 12
C3 TRUE B3 jest pusta
C4 FALSE B4 zawiera 18
C5 FALSE B5 zawiera 0
C6 TRUE B6 jest pusta
C7 FALSE B7 zawiera 7
C8 TRUE B8 jest pusta

Zwróć najniżej położoną zajętą wartość

Kliknij E2 i wpisz:

=XLOOKUP(FALSE,C2:C8,B2:B8,\"Brak wpisów\",0,-1)

Ta formuła wyszukuje FALSE w zakresie C2:C8 i zwraca odpowiadającą wartość z zakresu B2:B8. Piąty argument, 0, wymaga dokładnego dopasowania. Ostatni argument, -1, przeszukuje zakres od końca do początku. \"Brak wpisów\" określa komunikat wyświetlany, gdy nie ma dopasowania. Znaczenie tych argumentów podano w dokumentacji XLOOKUP firmy Apple.

W przykładzie najniżej położona wartość FALSE znajduje się w C7, więc E2 zwraca zawartość B7: 7. Zauważ, że ta odpowiedź jest mniejsza niż 18. Zadanie dotyczy pozycji, więc największa liczba byłaby odpowiedzią na inne pytanie.

Wykonaj te trzy tymczasowe zmiany, aby sprawdzić konfigurację, a potem w razie potrzeby przywróć przykładowe dane:

  1. Zmień B7 na 9: E2 powinno zmienić się na 9.
  2. Wyczyść B7: E2 powinno zmienić się na 0, ponieważ B5 jest teraz ostatnią zajętą komórką źródłową.
  3. Wyczyść wszystkie komórki w zakresie B2:B8: E2 powinno wyświetlić Brak wpisów.

Te sprawdzenia pozwalają zweryfikować trzy częste wymagania: reagowanie na zmieniony wpis, zachowanie zera i obsługę całkowicie pustego zakresu. Ułatwiają też zauważenie błędów w formułach pomocniczych.

Zdecyduj, czy „pusta” oznacza pustą czy wyglądającą na pustą

Główna metoda stosuje ścisłą definicję: komórka źródłowa musi być całkowicie pusta, aby ją pominąć. Komórka zawierająca formułę, która zwraca \"\", nadal zawiera formułę, więc nie spełnia tej definicji. W efekcie wizualnie pusty wynik może zostać wybranym wpisem. Apple definiuje ISBLANK jako komórkę całkowicie pustą. ISBLANK

Jeśli kolumna zawiera formuły zwracające tekst, możesz zamiast tego chcieć pomijać wyniki tekstowe o zerowej długości. W takim przypadku zastąp formułę pomocniczą w C2 poniższą formułą i ponownie wypełnij nią kolumnę:

=LEN(B2)=0

Pozostaw formułę wyszukiwania w E2 bez zmian. Ta zmodyfikowana formuła pomocnicza sprawdza, czy wartość źródłowa ma tekst o zerowej długości. Apple opisuje LEN jako funkcję zliczającą grafemy i przyjmującą dowolną wartość; zlicza ona również spacje, liczby i znaki specjalne. Pusty ciąg ma długość zero, a spacja długość jeden. LEN

Przed dalszym użyciem wyniku wybierz odpowiednią regułę. Oryginalna formuła pomocnicza odpowiada na pytanie „Która komórka jest ostatnią zajętą?”. Formuła pomocnicza sprawdzająca długość tekstu odpowiada na pytanie „Która wartość jako ostatnia ma niezerową długość tekstu?”. Żadna z tych reguł nie traktuje spacji jako pustej.

Utrzymuj zgodność zakresów podczas rozbudowy tabeli

Przykład celowo przeszukuje wiersze 2–8. Gdy rozszerzysz źródło do wiersza 20, wypełnij kolumnę pomocniczą do C20 i zaktualizuj oba zakresy wyszukiwania:

=XLOOKUP(FALSE,C2:C20,B2:B20,\"Brak wpisów\",0,-1)

Każdy wiersz pomocniczy musi odpowiadać temu samemu wierszowi źródłowemu. Sprawdzaj też odwołania po zmianach struktury: Apple wyraźnie zaznacza, że dodanie wiersza wewnątrz zakresu, do którego odwołuje się formuła w skategoryzowanej tabeli, wymaga zmiany odwołania, aby nowy wiersz został uwzględniony. Obliczanie wartości na podstawie danych w komórkach tabeli w Numbers na Macu

Jeśli przeniesiesz wynik do innej tabeli, dodaj nazwę tabeli do odwołań. Numbers używa :: między nazwą tabeli a odwołaniem do komórki i automatycznie wstawia nazwę, gdy podczas tworzenia formuły zaznaczysz komórki w innej tabeli. Zaznaczenie właściwych zakresów pozwala uniknąć zgadywania ich adresów. Rodzaje argumentów i wartości

Unikaj skrótów dotyczących tablic, wykresów i wyróżniania, które zmieniają zadanie

Test pustych komórek w całej kolumnie nie zastąpi kolumny pomocniczej

Nie zastępuj wypełnionej kolumny pomocniczej formułą ISBLANK(B2:B8), zakładając, że wykona ona siedem osobnych testów. Apple wyraźnie stwierdza, że gdy ISBLANK odwołuje się do więcej niż jednej komórki, zwraca FALSE, nawet jeśli wszystkie komórki są puste. Właśnie dlatego ta metoda sprawdza każdą komórkę źródłową osobno. ISBLANK

Numbers obsługuje funkcje tablicowe; Apple definiuje tablicę jako sekwencję wartości i wyjaśnia, że funkcje tablicowe mogą przekazywać wartości do innych funkcji. Ta ogólna możliwość nie zmienia jednak sposobu działania argumentów opisanego dla konkretnej funkcji. Rodzaje argumentów i wartości

Wykres przedstawia wybrane dane

Użyj komórki wyniku, aby bezpośrednio sprawdzić odpowiedź. Jeśli dodatkowo tworzysz wykres liczby obiektów, zaznacz go i kliknij Edytuj odwołania do danych, aby sprawdzić lub zmienić komórki źródłowe. Dane ukryte lub odfiltrowane są domyślnie wykluczane z wykresów, a duże wykresy mogą pokazywać reprezentatywną próbkę punktów. Widoczny punkt końcowy należy więc sprawdzić w odniesieniu do tabeli źródłowej. Modyfikowanie danych wykresu w Numbers na Macu

Wyróżnienie pasującej wartości może zaznaczyć kilka komórek

Aby dodać wizualne oznaczenie, Apple obsługuje reguły wyróżniania warunkowego, które porównują wartość komórki z odwołaniem do innej komórki. Zaznacz odpowiednie komórki, otwórz Format → Komórka → Wyróżnianie warunkowe i kliknij Dodaj regułę. Stosowanie warunków dla komórek w Numbers na Macu

Reguła porównująca liczby z wynikiem wskaże pasujące wartości. Jeśli 7 pojawi się w kilku wierszach źródłowych, takie dopasowania nie pozwolą jednoznacznie wskazać B7. Pamiętaj o tej różnicy, gdy zadanie polega na znalezieniu konkretnego wiersza.

Gdy kolumna pomocnicza odzwierciedla już przyjętą przez Ciebie definicję pustej komórki, sprawdź przykładowy wynik i rozszerz oba zakresy jednocześnie. Otrzymasz wtedy przejrzysty sposób na pobranie ostatniej zajętej wartości, z zachowaniem istotnych wpisów, takich jak zero.

Źródła