Jak ustawić zakres wartości w Excelu?

Korzystając z Microsoft Excel, bardzo często użytkownik nie wie z góry, ile informacji znajdzie się w tabeli. Dlatego nie we wszystkich sytuacjach rozumiemy, jaki zakres należy objąć. W końcu zestaw komórek jest pojęciem zmiennym. Aby pozbyć się tego problemu, konieczne jest automatyczne generowanie zasięgu tak, aby opierało się wyłącznie na ilości danych wprowadzonych przez użytkownika.

Automatyczna zmiana zakresów komórek w Excelu

Zaletą zakresów automatycznych w programie Excel jest to, że znacznie ułatwiają korzystanie z formuł. Ponadto pozwalają znacznie uprościć analizę złożonych danych zawierających dużą liczbę formuł, które zawierają wiele funkcji. Możesz nadać temu zakresowi nazwę, a następnie zostanie on automatycznie zaktualizowany w zależności od zawartych w nim danych.

Jak dokonać automatycznej zmiany zakresu w programie Excel

Załóżmy, że jesteś inwestorem, który musi zainwestować w jakiś obiekt. W efekcie chcemy uzyskać informację o tym, ile możesz w sumie zarobić przez cały czas, w którym pieniądze będą pracować na ten projekt. Niemniej jednak, aby uzyskać te informacje, musimy regularnie monitorować, jaki całkowity zysk przynosi nam ten obiekt. Zrób taki sam raport, jak na tym zrzucie ekranu.

Na pierwszy rzut oka rozwiązanie jest oczywiste: wystarczy zsumować całą kolumnę. Jeśli pojawią się w nim wpisy, kwota zostanie zaktualizowana niezależnie. Ale ta metoda ma wiele wad:

1. Jeśli problem zostanie rozwiązany w ten sposób, nie będzie możliwe wykorzystanie komórek zawartych w kolumnie B do innych celów.
2. Taka tabela pochłonie bardzo dużo pamięci RAM, co uniemożliwi korzystanie z dokumentu na słabych komputerach.

Dlatego konieczne jest rozwiązanie tego problemu poprzez dynamiczne nazwy. Aby je utworzyć, musisz wykonać następującą sekwencję czynności:

1. Przejdź do zakładki „Formuły”, która znajduje się w menu głównym. Pojawi się sekcja „Zdefiniowane nazwy”, w której znajduje się przycisk „Przypisz nazwę”, w który musimy kliknąć.
2. Następnie pojawi się okno dialogowe, w którym należy wypełnić pola, jak pokazano na zrzucie ekranu. Ważne jest, aby pamiętać, że musimy zastosować funkcję = PRZEMIESZCZENIE wraz z funkcją SPRAWDZaby utworzyć zakres automatycznej aktualizacji. 
3. Następnie musimy użyć funkcji SUMA, który wykorzystuje nasz zakres dynamiczny jako argument. 

Po wykonaniu tych kroków możemy zobaczyć, jak aktualizowane jest pokrycie komórek należących do zakresu „dochód”, gdy dodajemy tam nowe elementy.

Funkcja PRZESUNIĘCIA w Excelu

Przyjrzyjmy się funkcjom, które wcześniej zapisaliśmy w polu „zakres”. Korzystanie z funkcji SPRZEDAŻ możemy określić ilość zakresu, biorąc pod uwagę, ile komórek w kolumnie B jest wypełnionych. Argumenty funkcji są następujące:

1. Uruchom komórkę. Za pomocą tego argumentu użytkownik może wskazać, która komórka w zakresie zostanie uznana za górną lewą stronę. Zgłosi się w dół i na prawo.
2. Zakres przesunięty o wiersze. Korzystając z tego zakresu, ustawiamy liczbę komórek, o jaką powinno nastąpić przesunięcie od lewej górnej komórki zakresu. Możesz używać nie tylko wartości dodatnich, ale także zera i minusa. W takim przypadku przemieszczenie może w ogóle nie nastąpić lub zostanie przeprowadzone w przeciwnym kierunku.
3. Zakres przesunięty o kolumny. Ten parametr jest podobny do poprzedniego, tylko umożliwia ustawienie stopnia przesunięcia zakresu w poziomie. Tutaj możesz również użyć zarówno wartości zerowych, jak i ujemnych.
4. Zakres wysokości. W rzeczywistości tytuł tego argumentu wyjaśnia nam, co to znaczy. Jest to liczba komórek, o którą powinien wzrosnąć zakres.
5. Wartość zakresu w szerokości. Argument jest podobny do poprzedniego, tylko dotyczy kolumn.

Nie musisz określać dwóch ostatnich argumentów, jeśli nie musisz. W takim przypadku wartość zakresu będzie wynosić tylko jedną komórkę. Na przykład, jeśli określisz formułę =PRZESUNIĘCIE(A1;0;0), ta formuła będzie odwoływać się do tej samej komórki, co w pierwszym argumencie. Jeśli przesunięcie pionowe jest ustawione na 2 jednostki, to w tym przypadku komórka będzie odnosić się do komórki A3. Opiszmy teraz szczegółowo, co oznacza funkcja SPRAWDZ.

Funkcja LICZENIE w Excelu

Korzystanie z funkcji SPRAWDZ określamy, ile komórek w kolumnie B w sumie wypełniliśmy. Oznacza to, że za pomocą dwóch funkcji określamy, ile komórek w zakresie jest wypełnionych, i na podstawie otrzymanych informacji określamy rozmiar zakresu. Dlatego ostateczna formuła będzie następująca: =СМЕЩ(Лист1!$B$2;0;0;СЧЁТ(Лист1!$B:$B);1)

Przyjrzyjmy się, jak poprawnie zrozumieć zasadę tej formuły. Pierwszy argument wskazuje, gdzie zaczyna się nasz zakres dynamiczny. W naszym przypadku jest to komórka B2. Dalsze parametry mają zerowe współrzędne. Sugeruje to, że nie potrzebujemy przesunięć względem lewej górnej komórki. Wypełniamy jedynie pionowy rozmiar zakresu, którego użyliśmy funkcji as SPRAWDZ, który określa liczbę komórek zawierających pewne dane. Czwartym parametrem, który wypełniliśmy, jest jednostka. W ten sposób pokazujemy, że całkowita szerokość zakresu powinna wynosić jedną kolumnę.

Tak więc, korzystając z funkcji SPRAWDZ użytkownik może wykorzystać pamięć tak wydajnie, jak to możliwe, ładując tylko te komórki, które zawierają pewne wartości. W związku z tym nie będzie dodatkowych błędów w pracy związanych ze słabą wydajnością komputera, na którym będzie działał arkusz kalkulacyjny.

W związku z tym, aby określić rozmiar zakresu w zależności od liczby kolumn, musisz wykonać podobną sekwencję działań, tylko w tym przypadku musisz określić jednostkę w trzecim parametrze, a formułę w czwartym SPRAWDZ.

Widzimy, że za pomocą formuł Excela można nie tylko zautomatyzować obliczenia matematyczne. To tylko kropla w morzu potrzeb, ale tak naprawdę pozwalają zautomatyzować niemal każdą operację, jaka przyjdzie do głowy.

Wykresy dynamiczne w Excelu

Tak więc w ostatnim kroku udało nam się stworzyć zakres dynamiczny, którego rozmiar całkowicie zależy od liczby wypełnionych komórek. Teraz na podstawie tych danych możesz tworzyć dynamiczne wykresy, które będą się automatycznie zmieniać, gdy tylko użytkownik dokona zmian lub doda dodatkową kolumnę lub wiersz. Sekwencja działań w tym przypadku jest następująca:

1. Wybieramy nasz zakres, po czym wstawiamy wykres typu „Histogram z grupowaniem”. Możesz znaleźć tę pozycję w sekcji „Wstaw” w sekcji „Wykresy – Histogram”.
2. Klikamy lewym przyciskiem myszy na losowo wybraną kolumnę histogramu, po czym w wierszu funkcji zostanie pokazana funkcja =SERIES(). Na zrzucie ekranu możesz zobaczyć szczegółową formułę. 
3. Następnie należy wprowadzić pewne zmiany w formule. Musisz zamienić zakres po „Arkuszu1!” do nazwy zakresu. Spowoduje to następującą funkcję: =WIERSZ(Arkusz1!$B$1;;Arkusz1!Dochód;1)
4. Teraz pozostaje dodać nowy rekord do raportu, aby sprawdzić, czy wykres jest aktualizowany automatycznie, czy nie.

Rzućmy okiem na nasz diagram.

Podsumujmy, jak to zrobiliśmy. W poprzednim kroku stworzyliśmy zakres dynamiczny, którego wielkość zależy od tego, ile zawiera elementów. W tym celu wykorzystaliśmy kombinację funkcji SPRAWDZ и SPRZEDAŻ. Nazwaliśmy ten zakres, a następnie użyliśmy odniesienia do tej nazwy jako zakresu naszego histogramu. Nie jest tak ważne, jaki konkretny zakres wybrać jako źródło danych na pierwszym etapie. Najważniejsze, aby później zastąpić go nazwą zakresu. W ten sposób możesz zaoszczędzić dużo pamięci RAM.

Nazwane zakresy i ich zastosowania

Porozmawiajmy teraz bardziej szczegółowo o tym, jak poprawnie tworzyć nazwane zakresy i używać ich do wykonywania zadań ustawionych dla użytkownika programu Excel.

Domyślnie używamy zwykłych adresów komórkowych, aby zaoszczędzić czas. Jest to przydatne, gdy trzeba raz lub kilka razy wpisać zakres. Jeśli ma być używany przez cały czas lub musi być adaptacyjny, należy użyć nazwanych zakresów. Znacznie ułatwiają tworzenie formuł, a użytkownikowi nie będzie tak trudno analizować złożone formuły zawierające dużą liczbę funkcji. Opiszmy niektóre kroki związane z tworzeniem zakresów dynamicznych.

Wszystko zaczyna się od nazwania komórki. Aby to zrobić, po prostu wybierz go, a następnie wpisz nazwę, której potrzebujemy w polu jego nazwy. Ważne, aby było łatwe do zapamiętania. Przy nazewnictwie należy wziąć pod uwagę pewne ograniczenia:

1. Maksymalna długość to 255 znaków. To wystarczy, aby przypisać imię, którego pragnie twoje serce.
2. Nazwa nie może zawierać spacji. Dlatego jeśli zawiera kilka słów, to można je rozdzielić za pomocą znaku podkreślenia.

Jeżeli później na innych arkuszach tego pliku będziemy potrzebowali wyświetlić tę wartość lub zastosować ją do dalszych obliczeń, to nie ma potrzeby przechodzenia do pierwszego arkusza. Możesz po prostu zapisać nazwę tej komórki zakresu.

Następnym krokiem jest utworzenie nazwanego zakresu. Procedura jest w zasadzie taka sama. Najpierw musisz wybrać zakres, a następnie określić jego nazwę. Następnie ta nazwa może być używana we wszystkich innych operacjach na danych w programie Excel. Na przykład nazwane zakresy są często używane do definiowania sumy wartości.

Ponadto możliwe jest utworzenie nazwanego zakresu za pomocą zakładki Formuły za pomocą narzędzia Ustaw nazwę. Po jej wybraniu pojawi się okno, w którym musimy wybrać nazwę dla naszego zakresu, a także określić obszar, do którego będzie się on rozciągał ręcznie. Możesz również określić, gdzie ten zakres będzie działał: w ramach jednego arkusza lub w całej książce.

Jeśli został już utworzony zakres nazw, to do jego wykorzystania służy specjalna usługa o nazwie menedżer nazw. Pozwala nie tylko edytować lub dodawać nowe nazwy, ale także usuwać je, jeśli nie są już potrzebne.

Należy pamiętać, że w przypadku używania w formułach zakresów nazwanych, to po ich skasowaniu, formuły nie zostaną automatycznie nadpisane poprawnymi wartościami. Dlatego mogą wystąpić błędy. Dlatego przed usunięciem nazwanego zakresu należy upewnić się, że nie jest on używany w żadnej z formuł.

Innym sposobem utworzenia nazwanego zakresu jest pobranie go z tabeli. Aby to zrobić, istnieje specjalne narzędzie o nazwie „Utwórz z zaznaczenia”. Jak rozumiemy, aby z niego skorzystać, należy najpierw wybrać zakres, który będziemy edytować, a następnie ustawić miejsce, w którym mamy nagłówki. W efekcie na podstawie tych danych Excel automatycznie przetworzy wszystkie dane, a tytuły zostaną automatycznie przypisane.

Jeśli tytuł zawiera kilka słów, Excel automatycznie oddzieli je podkreśleniem.

W ten sposób odkryliśmy, jak tworzyć dynamiczne nazwane zakresy i jak pozwalają one zautomatyzować pracę z dużymi ilościami danych. Jak widać, wystarczy skorzystać z kilku funkcji i narzędzi programowych wbudowanych w funkcjonalność. Nie ma w tym nic skomplikowanego, choć na pierwszy rzut oka może się to wydawać początkującemu.