fbpx

Wartość ostatniej komórki w kolumnie dzięki nazwie

21.10.2019 | Analizy sprzedaży, ECP2, Nazwy

Czyli dynamiczna nazwa

Chciałabym zawsze wiedzieć, jaka jest ostatnia transakcja na mojej liście. Konkretnie interesuje mnie jej numer i wartość (chcę je widzieć nad zakresem, w żółtych komórkach). Zestaw danych mam w zwykłym zakresie, o taki:

Formatka i wynik

Formatka i wynik

Ma to działać tak, że jak dopiszę dane do tego zakresu – to właśnie te dopisane, czyli ostatnie, mają się pojawić w żółtych komórkach. Dokonam tego za pomocą nazewnictwa. Najpierw pokażę jak to zrobić dla konkretnego arkusza, w którym jesteśmy (i tylko dla niego!), a potem – jak to zrobić do każdego, niezależnie jak się nazywa (jeśli układ arkusza jest analogiczny).

Ostatnia komórka w konkretnym arkuszu

W pliku mamy 2 zestawienia danych o identycznej strukturze (jak na obrazku powyżej). Każde z nich znajduje się w innym arkuszu: Kwiecień i Maj. Chciałabym móc w szybki sposób mieć dostępną informację o ostatnim numerze transakcji w arkuszu Kwiecień. Niezależnie od tego, w jakim arkuszu jestem.

W tym celu stworzę sobie nazwę, a konkretnie nazwaną formułę, która wskaże mi tę właśnie wartość (id ostatniej transakcji w kwietniu).

Aby ułatwić sobie życie – wpiszę formułę do żółtej komórki E2, a potem skopiuję ją gdy będę tworzyła nazwę.

Formuła jest następująca:

=INDEKS($B:$B;4+ILE.NIEPUSTYCH($B:$B))

Zadziała ona w aktywnym arkuszu i zakłada, że wszystkie dane w kolumnie ID transakcji w naszym zakresie danych są uzupełnione. Funkcja INDEKS wyświetli wartość z odpowiedniego wiersza kolumny B. Funkcja ILE.NIEPUSTYCH policzy ile jest w tej kolumnie zapełnionych komórek. Dodaję do niej 4, ponieważ nad zakresem danych w mojej formatce są 4 puste wiersze. Suma obu da nam numer ostatniego wiersza w kolumnie – tego, który chcemy wyświetlić. Tutaj pojawia się bardzo ważna kwestia – kolumna zakresu danych, w której liczymy niepuste komórki musi być cała wypełniona. Jeśli nie jesteś tego pewna(y) – wskaż taką kolumnę w zakresie, która na pewno uzupełniona będzie (zazwyczaj kolumny z id takie właśnie są).

I na tym etapie taka formuła wystarczy. Jak za chwilę będziemy kopiować ją do okienka nazwy – nazwa aktywnego arkusza sama wskoczy do formuły.

W takim razie stwórzmy sobie nazwę.

  1. Wywołaj okienko tworzenia nazw Formuły/Menedżer nazw/Nowy i uzupełnij pola Nazwa (jak uważasz, u mnie to OstTransakcjaKwi) i Odwołuje się do (wklej napisaną przed chwilą formułę):

    Tworzenie nazwy

    Tworzenie nazwy

  2. Zatwierdź OK
  3. Zauważ, że po zatwierdzeniu nazwy, Excel dorzucił sobie nazwę aktywnego arkusza do odwołań do kolumny:

    Excel dorzucił nazwy arkuszy

    Excel dorzucił nazwy arkuszy

  4. Zamknij okienko.

Teraz, kiedy w żółtej komórce (czy jakiejkolwiek innej w dowolnym arkuszu) wpiszesz odwołanie do tej nazwy – otrzymasz wynik 422, gdyż jest to id ostatniej transakcji na liście. Jeśli zaś dopiszesz do tego zakresu dane – formuła się przeliczy i zwróci nowy wynik.

Dla wartości ostatniej transakcji formuła będzie analogiczna, tylko odwołująca się do kolumny F:

=INDEKS(Kwiecień!$F:$F;4+ILE.NIEPUSTYCH(Kwiecień!$F:$F))

Obie nazwy będą działać w całym pliku, niezależnie w którym arkuszu ich użyjesz.

Ostatnia komórka w aktywnym arkuszu

Teraz przyjrzyjmy się sytuacji, kiedy zależy nam, aby wyświetlić id ostatniej transakcji, ale w odniesieniu do dowolnego arkusza. Czyli jeśli stoimy w arkuszu Kwiecień – to do kwietniowej transakcji, a jeśli w arkuszu Maj – do majowej. Chodzi po prostu o to, aby nie tworzyć kilku nazw, tylko jedną, działającą w zależności od kontekstu, w jakim występuje.

Brzmi strasznie trudno, ale w rzeczywistości trzeba tylko troszkę zmodyfikować napisane przez nas wcześniej formuły. Mianowicie usunąć z nich nazwy arkusza. Powtórzę: nazwy arkusza. I tylko. Wykrzyknik zostawić. Taki zapis w nazewnictwie oznacza, że odwołujemy się do aktywnego arkusza, czyli takiego, w jakim nazwa została wywołana, a o to właśnie nam chodzi.

Czyli formuła dla id transakcji będzie następująca:

=INDEKS(!$B:$B;4+ILE.NIEPUSTYCH(!$B:$B))

W okienku tworzenia nazwy wygląda to tak:

Nazwa dla aktywnego arkusza

Nazwa dla aktywnego arkusza

To dopiero ciekawy zapis! Popróbuj – polecam! No właśnie. Bo to już cały magic trick 🙂

Powiązane produkty

 

 A tutaj plik z gotowcem do pobrania:

MalinowyExcel Nazwa ostatniej komórki dw.xlsx

 

I wersja wideo:

 

 

 

Jeżeli chcesz lepiej poznać Excela na poziomie średniozaawansowanym zapisz się na listę zainteresowanych kursem Excel w codziennej pracy cz. 2! Podczas oczekiwania na kurs będę Ci wysyłała informacje, ciekawostki i excelowe triki. Bezpłatnie! 

Pomogłam Ci? Postaw mi wirtualną kawę! →

Przeczytaj podobne wpisy

Kategorie

12 komentarzy

  1. Super wpis. Właśnie takie rozwiązania szukałem. Dziękuję 😉

    Reply
    • Sławek, super! Bardzo się cieszę!

      Reply
  2. Malina, a jak zmodyfikować formułę by zadziałała w kolumnie w której nie wszystkie dane są uzupełnione (ewentualnie niektóre komórki są scalone i mają jedną wartość) ?

    Reply
    • Hej,
      na scalone komórki to niestety nie ma mocnych – trzeba je rozcalić i wpisać wartość w każdą komórkę osobno (scalone komórki to zło! ;)). Jak to już będzie – to będzie ideał i nawet nie trzeba będzie modyfikować formuły (jeśli scalone są jedynymi „pustymi” komórkami). Jeśli jednak są puste, a nam nadal zależy, aby podać ostatnią komórkę w dynamicznym zakresie – można to zrobić tak: =WYSZUKAJ(9999999999;F6:INDEKS(F:F;4+ILE.NIEPUSTYCH(B:B))). Tez oczywiście można podpiąć pod nazwę, jeśli formuła ma być względnie schowana przed użytkownikiem 🙂

      Reply
  3. Dzięki serdeczne. Poszedłem za Twoją radą i rozcaliłem. Będę się starał pamiętać że scalone komórki to zło.
    Pozdrawiam

    Reply
    • Super! Zdecydowanie scalone komórki to zło 🙂

      Reply
  4. Przydało się. Dzięki!

    Reply
    • Cieszę się 🙂

      Reply
  5. Mam bardzo długą kolumnę. Wiem jak ją całą zaznaczyć czyli używam CTRL + SHIFT + STRZAŁKA by zaznaczyć naraz całą kolumnę. Ja jednak potrzebuję ID komórki czyli np. B225626. By następnie z tej wartości wyekstrahować do dalszych obliczeń liczbę 225626. Jak mam to zrobić?

    Za pomoc z góry dziękuję

    Reply
  6. Cieszę się że znalazłem tę stronę. Malina masz dar przyjaznego i skrótowego przekazywania z pozoru skomplikowanych treści. Pomogla mi odpowiedź dla danka z dnia 8 stycznia 2020 at 12:41 do arkuszy Google. Dziękuję

    Reply
    • Bardzo mi miło to słyszeć, dziękuję! Cieszę się, że znalazłeś rozwiązanie dla siebie. Zapraszam ponownie :):):)

      Reply
  7. Czy da się to zrobić analogicznie dla ostatniej komórki w wierszu? Wprowadzam dane mając w 1 kolumnie listę użytkowników, a potem w każdej kolejnej kolumnie kolejny dzień miesiąca. Zależy mi na tym, by ustalić, w którym dniu miesiąca został wprowadzony ostatni rekord (nie wszystkie komórki uzupełniam, ale mogę uzupełnić „0” i wtedy zrobić wartość większą niż „0”. Szukam jakiej formuły użyć, ale nic nie pasuje 🙂

    Reply

Submit a Comment

Twój adres e-mail nie zostanie opublikowany. Wymagane pola są oznaczone *

Pin It on Pinterest