Znasz to uczucie, kiedy kopiujesz dane z innego pliku, strony internetowej albo systemu i… trafiasz na zakres otoczony pustymi wierszami i kolumnami? Trzeba wtedy ręcznie przycinać dane, kombinować z filtrowaniem albo pisać makra. Ale na szczęście Excel 365 ma na to lekarstwo – funkcję PRZYTNIJZAKRES, czyli TRIMRANGE.
Co robi ta funkcja? Przycina wskazany zakres, usuwając zbędne puste wiersze i kolumny z jego brzegów. A do tego potrafi działać dynamicznie, dzięki specjalnemu zapisowi .:.
Plik Excela wykorzystywany w przykładach możesz pobrać tutaj
Argumenty:
=PRZYTNIJZAKRES(zakres; [przytnijWiersze]; [przytnijKolumny])
Excel od zawsze pozwalał odwoływać się do zakresów, np. A1:D100. Ale co, jeśli chcesz automatycznie przyciąć tylko to, co naprawdę zawiera dane i nie pisać dodatkowych funkcji? W Excelu 365 masz taką możliwość dzięki tzw. odwołaniom przycinającym.
Jest to skrócona notacja, która mówi Excelowi, żeby automatycznie przyciął zakres usuwając puste wiersze i kolumny z przodu, z tyłu albo z obu stron.
| Skrócony zapis | Funkcja | Działanie |
| A1.:.D100 | =PRZYTNIJZAKRES(A1:D100;3;3) | Przycina puste wiersze i kolumny z każdej strony |
| A1:.D100 | =PRZYTNIJZAKRES(A1:D100;2;2) | Przycina końcowe kolumny.wiersze |
| A1.:D100 | =PRZYTNIJZAKRES(A1:D100;1;1) | Przycina początkowe kolumny/wiersze |
Oba zapisy działają dokładnie tak samo. Jednak co właściwie oznaczają te kropki?
Działa to także, z całymi kolumnami lub wierszami
Masz tabelę składającą się z trzech kolumn. Nazwiska, działu oraz dodatku. Twoim zadaniem będzie na podstawie działu przypisać wysokość dodatku, który znajduje się w dodatkowej tablicy w tym samym arkuszu. Oczywiście, możemy wykorzystać tu funkcję wyszukującą i wszystko będzie działało prawidłowo. Jednakże, co jeśli chcielibyśmy, żeby funkcja x.wyszukaj automatycznie wypisywała nam informację o dodatku dla pracowników, którzy zostaną dopisani do listy? Tu z pomocą przychodzi funkcja PRZYTNIJZAKRES.
Jeżeli napiszemy funkcję x.wyszukaj, w której szukaną wartością będzie zakres komórek od B2 do B1000, to pod ostatnim wierszem z pracownikiem uzyskamy wynik w postaci #N/D sugerujący, że nie znalazł szukanej wartości, co jest oczywiście prawdą. Jeśli chcielibyśmy, żeby funkcja x.wyszukaj działała na zakresie od B2 do B1000, ale tylko, gdy jest tam wpisany jakiś pracownik możemy wykorzystać do tego funkcję PRZYTNIJZAKRES.
Teraz funkcja w przypadku braku wpisanego pracownika nie zwróci błędu, tylko po prostu nie będzie się tam wykonywała. W momencie dodania nowego pracownika, dodatek zostanie przypisany automatycznie.
Oczywiście zamiast stosowania funkcji PRZYTNIJZAKRES moglibyśmy użyć takiego zapisu:
=X.WYSZUKAJ(B2.:.B1000;G2:G6;H2:H6)
Kolejnym przykładem wykorzystania funkcji PRZYTNIJZAKRES może być automatycznie rozszerzająca się lista rozwijana dodana za pomocą poprawności danych.
Stosując klasyczne odwołanie w poprawności danych, w którym wskazujemy zakres komórek, które mają być źródłem listy możemy go zwiększyć w celu uwzględnienia potencjalnych wartości, które mogą zostać dodane.
Jednakże, takie odwołanie spowoduje, że posta pozycja zostanie wyświetlona na rozwijanej liście, co nie jest oczekiwanym wynikiem.
Jeśli jednak skorzystamy z przycięcia zakresu czy to za pomocą funkcji czy to za pomocą dynamicznych odwołań będzie to wyglądało znacznie lepiej.
Wtedy na rozwijanej liście będzie widoczna tylko lista unikatowych pozycji z kolumny z działem, która automatycznie będzie się powiększała wraz z dodaniem kolejnych pozycji w kolumnie A.
Oczywiście zapisanie tego z wykorzystaniem funkcji również dałoby poprawny wynik.
Funkcja unikatowe jest kolejnym przykładem funkcji, która po połączeniu z funkcją PRZYTNIJZAKRES działa znacznie lepiej. Chcąc wyciągnąć informację o liczbie unikatowych państw znajdujących się w kolumnie można skorzystać z funkcji UNIKATOWE, która bez problemu sobie z tym poradzi. Chcąc jednak automatycznie poszerzać listę unikatowych wartości o kolejne nowo dodane państwa, zaznaczenie większego zakresu będzie wiązało się, z pojawieniem wartości 0.
Rozwiązaniem znowu jest funkcja PRZYTNIJZAKRES, która spowoduje działanie funkcji UNIKATOWE, tylko w zakresie niepustych komórek.
Chcąc stworzyć automatycznie sortującą się listę pracowników, można wykorzystać do tego funkcję sortuj. Jednak jeśli zaznaczymy większy zakres danych, który uwzględni potencjalnych nowych pracowników, to efektem działania tej funkcji będą wartości 0.
Używając funkcji PRZYTNIJZAKRES rozwiążemy ten problem i będziemy mieli automatycznie aktualizującą się listę pracowników posortowanych w kolejności od A do Z.
Funkcja PRZYTNIJZAKRES to świetne narzędzie, które pozwala zaoszczędzić mnóstwo czasu i uniknąć frustracji z ręcznym czyszczeniem danych. Szczególnie kiedy połączysz ją z poprawnością danych czy też dynamicznymi funkcjami typu SORTUJ, UNIKATOWE, XWYSZUKAJ. Wpisz =A1.:.D100 i ciesz się porządkiem w danych jak nigdy dotąd!
