Spis treści
Czy masz tabele z danymi w Excelu, których rozmiar można zmieniać, tzn. ilość wierszy (kolumn) może się zwiększać lub zmniejszać w toku pracy? Jeśli rozmiary stołu „pływają”, będziesz musiał stale monitorować ten moment i poprawiać go:
- linki w formułach raportów, które odnoszą się do naszej tabeli
- początkowe zakresy tabel przestawnych zbudowanych zgodnie z naszą tabelą
- początkowe zakresy wykresów zbudowane według naszej tabeli
- zakresy dla list rozwijanych, które wykorzystują naszą tabelę jako źródło danych
Wszystko to w sumie nie pozwoli Ci się nudzić 😉
Dużo wygodniej i poprawniej będzie utworzyć dynamiczny zakres „gumowy”, który automatycznie dopasuje rozmiar do rzeczywistej liczby wierszy i kolumn danych. Aby to zrealizować, istnieje kilka sposobów.
Metoda 1. Inteligentny stół
Zaznacz zakres komórek i wybierz z zakładki Strona główna — Formatuj jako tabelę (Strona główna — Formatuj jako tabelę):
Jeśli nie potrzebujesz wzoru w paski, który jest dodawany do tabeli jako efekt uboczny, możesz go wyłączyć na wyświetlonej karcie Konstruktor (projekt). Każda stworzona w ten sposób tabela otrzymuje nazwę, którą w tym samym miejscu na zakładce można zastąpić wygodniejszą Konstruktor (projekt) na polu Nazwa tabeli (Nazwa tabeli).
Teraz możemy użyć dynamicznych linków do naszej „inteligentnej tabeli”:
- Tabela 1 – link do całej tabeli z wyjątkiem wiersza nagłówka (A2:D5)
- Tabela1[#Wszystkie] – link do całej tabeli (A1:D5)
- Stół1[Piotr] – odwołanie do kolumny zakresu bez pierwszego nagłówka komórki (C2:C5)
- Tabela1[#Nagłówki] – link do „nagłówka” z nazwami kolumn (A1:D1)
Takie odniesienia świetnie sprawdzają się w formułach, na przykład:
= SUM (Tabela1[Moskwa]) – obliczenie sumy dla kolumny „Moskwa”
or
=VPR(F5;Tabela 1;3;0) – wyszukaj w tabeli miesiąc z komórki F5 i wystaw dla niego sumę petersburską (co to jest WYSZUKAJ.PIONOWO?)
Takie linki można z powodzeniem wykorzystać podczas tworzenia tabel przestawnych, wybierając na karcie Wstaw — tabela przestawna (Wstaw — tabela przestawna) oraz wpisanie nazwy inteligentnej tabeli jako źródła danych:
Jeśli wybierzesz fragment takiej tabeli (np. dwie pierwsze kolumny) i stworzysz diagram dowolnego typu, to podczas dodawania nowych wierszy zostaną one automatycznie dodane do diagramu.
Podczas tworzenia list rozwijanych nie można używać bezpośrednich linków do elementów inteligentnej tabeli, ale można łatwo obejść to ograniczenie, stosując sztuczkę taktyczną – użyj funkcji POŚREDNI (POŚREDNI), który zamienia tekst w link:
Tych. link do inteligentnej tabeli w postaci ciągu tekstowego (w cudzysłowie!) zamienia się w pełnoprawny link, a rozwijana lista normalnie go postrzega.
Metoda 2: Dynamiczny nazwany zakres
Jeśli przekształcenie danych w inteligentną tabelę jest z jakiegoś powodu niepożądane, możesz skorzystać z nieco bardziej skomplikowanej, ale znacznie bardziej subtelnej i wszechstronnej metody – utworzyć dynamiczny nazwany zakres w Excelu, który odwołuje się do naszej tabeli. Następnie, podobnie jak w przypadku inteligentnej tabeli, możesz dowolnie używać nazwy utworzonego zakresu w dowolnych formułach, raportach, wykresach itp. Zacznijmy od prostego przykładu:
Zadanie: utwórz dynamiczny nazwany zakres, który odnosiłby się do listy miast i automatycznie rozciągał się i zmniejszał podczas dodawania nowych miast lub ich usuwania.
Będziemy potrzebować dwóch wbudowanych funkcji Excela dostępnych w dowolnej wersji − POICPOZ (MECZ) aby określić ostatnią komórkę zakresu, i INDEKS (INDEKS) aby utworzyć łącze dynamiczne.
Znalezienie ostatniej komórki za pomocą MATCH
PODAJ.POZYCJĘ(wyszukiwana_wartość; zakres; typ_dopasowania) – funkcja wyszukująca daną wartość z zakresu (wiersza lub kolumny) i zwracająca numer porządkowy komórki, w której została znaleziona. Na przykład formuła PODAJ.POZYCJĘ(„Marzec”;A1:A5;0) zwróci w rezultacie liczbę 4, ponieważ słowo „Marzec” znajduje się w czwartej komórce w kolumnie A1:A5. Ostatni argument funkcji Match_Type = 0 oznacza, że szukamy dokładnego dopasowania. Jeśli ten argument nie zostanie podany, to funkcja przełączy się w tryb wyszukiwania najbliższej najmniejszej wartości – dokładnie to można z powodzeniem wykorzystać do znalezienia ostatniej zajętej komórki w naszej tablicy.
Istota tej sztuczki jest prosta. MATCH szuka komórek w zakresie od góry do dołu i teoretycznie powinien się zatrzymać, gdy znajdzie najbliższą najmniejszą wartość do podanej. Jeśli określisz wartość, która jest oczywiście większa niż jakakolwiek dostępna w tabeli jako pożądana wartość, to PODAJ.POZYCJĘ dotrze do samego końca tabeli, niczego nie znajdzie i poda numer sekwencyjny ostatniej wypełnionej komórki. A my tego potrzebujemy!
Jeśli w naszej tablicy znajdują się tylko liczby, to jako pożądaną wartość możemy podać liczbę, która jest oczywiście większa niż którakolwiek z tych w tabeli:
Jako gwarancję możesz użyć liczby 9E + 307 (9 razy 10 do potęgi 307, czyli 9 z 307 zerami) – maksymalna liczba, z jaką w zasadzie może pracować Excel.
Jeżeli w naszej kolumnie znajdują się wartości tekstowe, to jako odpowiednik największej możliwej liczby można wstawić konstrukcję REPEAT(„i”, 255) – ciąg tekstowy składający się z 255 liter „i” – ostatnia litera Alfabet. Ponieważ Excel faktycznie porównuje kody znaków podczas wyszukiwania, każdy tekst w naszej tabeli będzie technicznie „mniejszy” niż tak długa linia „yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy”:
Wygeneruj link za pomocą INDEX
Teraz, gdy znamy pozycję ostatniego niepustego elementu w tabeli, pozostaje on do utworzenia linku do całego naszego asortymentu. Do tego używamy funkcji:
INDEKS(zakres; numer_wiersza; numer_kolumny)
Podaje zawartość komórki z zakresu po numerze wiersza i kolumny, czyli np. funkcja =INDEX(A1:D5;3;4) w naszej tabeli z miastami i miesiącami z poprzedniej metody da 1240 – zawartość z trzeciego rzędu i czwartej kolumny, czyli komórki D3. Jeśli jest tylko jedna kolumna, to jej numer można pominąć, tzn. formuła INDEX(A4:A3;2) da „Samara” na ostatnim zrzucie ekranu.
I jest jeden nie do końca oczywisty niuans: jeśli INDEKS nie jest tylko wprowadzany do komórki po znaku =, jak zwykle, ale jest używany jako ostatnia część odwołania do zakresu po dwukropku, to już nie daje zawartość komórki, ale jej adres! Zatem formuła taka jak $ A $ 2: INDEKS ($ A $ 2: $ A 100 $; 3) da na wyjściu odwołanie do zakresu A2: A4.
I tu pojawia się funkcja MATCH, którą wstawiamy do INDEKSU, aby dynamicznie określić koniec listy:
=$A$2:INDEKS($A$2:$A$100; DOPASUJ(POWT("I";255);A2:A100))
Utwórz nazwany zakres
Pozostaje spakować to wszystko w jedną całość. Otwórz kartę formuła (Formuły) I kliknij Menedżer nazw (Nazwisko menedżera). W oknie, które się otworzy, kliknij przycisk Stwórz (Nowy), wpisz w polu nazwę naszego asortymentu i wzór łodzie (Odniesienie):
Pozostaje kliknąć OK a gotowy zakres można wykorzystać w dowolnych formułach, listach rozwijanych czy wykresach.
- Używanie funkcji WYSZUKAJ.PIONOWO do łączenia tabel i wartości wyszukiwania
- Jak utworzyć automatycznie wypełnianą listę rozwijaną
- Jak stworzyć tabelę przestawną do analizy dużej ilości danych