Jak korzystać z dynamicznych nazwanych zakresów w programie Excel

Anonim

W moim ostatnim artykule omówiłem wszystkie nazwane zakresy w programie Excel. Podczas eksploracji nazwanych zakresów pojawił się temat dynamicznych zakresów. W tym artykule wyjaśnię, w jaki sposób możesz utworzyć zakres dynamiczny w programie Excel.

Co to jest dynamiczny nazwany zakres w programie Excel?

Normalny nazwany zakres jest statyczny. Jeśli zdefiniujesz C2:C10 jak Przedmiot, przedmiot będzie zawsze odnosić się do C2:C10, dopóki nie zmienisz go ręcznie. Na poniższym obrazku liczymy puste miejsca wPrzedmiot lista. Pokazuje 2. Gdyby był dynamiczny, pokazałby 0.

Dynamiczny zakres nazw to zakres nazw, który rozszerza się i kurczy zgodnie z danymi. Na przykład, jeśli masz listę przedmiotów z zakresu C2:C10 i nazwij ją Rzeczy, powinien rozwinąć się do C2:C11 jeśli dodasz nowy element w zakresie i powinien się zmniejszyć, jeśli zmniejszysz, gdy usuniesz jak powyżej.

Jak utworzyć dynamiczny zakres nazw

Twórz nazwane zakresy za pomocą tabel Excel

Tak, tabele programu Excel mogą tworzyć dynamiczne nazwane zakresy. Uczynią każdą kolumnę w tabeli o nazwie zakres, która jest wysoce dynamiczna.
Ale jest jedna wada nazw tabel, że nie można ich używać w walidacji danych i formatowaniu warunkowym. Można tam jednak użyć określonych nazwanych zakresów.

Użyj formuły POŚREDNIA i COUNTA

Aby zakres nazw był dynamiczny, możemy użyć funkcji ADR.POŚR i ILE.NIEPUSTYCH

. Jak? Zobaczmy.

Formuła ogólna do zapisania w Odnosi się do: sekcja

= ADR.POŚR("$komórkapoczątkowa:$literakolumnowa$"&LICZBA(Litera$kolumny:Litera$kolumny))

Powyższa ogólna formuła może wydawać się skomplikowana, ale w rzeczywistości jest łatwa. Zobaczmy na przykładzie.
Podstawowym założeniem jest określenie ostatnio używanej komórki.

Przykład zakresu dynamicznego

W powyższym przykładzie mieliśmy statyczny zakres nazw Item w zakresie C2:C10. Zróbmy to dynamicznie.

    • Otwórz Menedżera nazw, naciskając klawisze CTRL+F3.
    • Teraz, jeśli nazwa już istnieje w zakresie, kliknij ją, a następnie kliknij edytuj. W przeciwnym razie kliknij Nowy.
    • Nazwij go pozycja.
    • W Odnosi się do: Sekcja wpisz poniżej Formułę.
=ADR.POŚR("$C2:$C$"&LICZBA($C:$C))
  • Naciśnij przycisk OK.

I gotowe. Teraz za każdym razem, gdy wpiszesz element w polu nazwy lub w dowolnej formule, będzie on odwoływał się do C2 do ostatnio używanej komórki w zakresie.

Ostrożność: Żadna komórka nie powinna być pusta pomiędzy zakresem. W przeciwnym razie zakres zostanie zmniejszony o liczbę pustych komórek.

Jak to działa?

Jak powiedziałem, jego jedyną kwestią jest znalezienie ostatnio używanej komórki. W tym przykładzie żadne komórki nie powinny być puste. Czemu? Ty będziesz wiedział.

Funkcja ADR.POŚR w programie Excel konwertuje tekst na zakres. =POŚREDNIA("$C$2:$C$9") będzie odnosić się do zakresu bezwzględnego $C$2:$C$10. Musimy tylko dynamicznie znaleźć ostatni numer wiersza (9).
Ponieważ wszystkie komórki mają pewną wartość z zakresu C2:C10, możemy użyć funkcji ILE.NIEPUSTYCH, aby znaleźć ostatni wiersz.
Więc,=POŚREDNI("$C2:$C$"& ta część naprawia początkowy wiersz i kolumnę oraz LICZBA($C:$C) dynamicznie oblicza ostatni używany wiersz.

Tak, w ten sposób możesz stworzyć najbardziej efektywne dynamiczne nazwane zakresy, które będą działać z każdą formułą i funkcjonalnością programu Excel. Nie musisz ponownie edytować nazwanego zakresu po zmianie danych.

Pobieranie pliku:

Dynamiczne nazwane zakresy w Excelu

Jak korzystać z nazwanych zakresów w programie Excel

17 niesamowitych funkcji tabel Excel

Popularne artykuły:

50 skrótów Excela, które zwiększą Twoją produktywność

Jak korzystać z funkcji WYSZUKAJ.PIONOWO w programie Excel

Jak korzystać z funkcji LICZ.JEŻELI w programie Excel

Jak korzystać z funkcji SUMA.JEŻELI w programie Excel