Być może nie wiesz, jak korzystać z funkcji PRZESUNIĘCIE w programie Excel

Czy wiesz , że funkcja PRZESUNIĘCIE w programie Microsoft Excel może pomóc w wielu innych czynnościach, a nie tylko w odwołaniach do komórek? Poniżej znajdują się unikalne sposoby korzystania z PRZESUNIĘCIA w programie Excel , o których możesz nie wiedzieć.

Funkcja PRZESUNIĘCIA programu Microsoft Excel jest elastycznym i potężnym narzędziem. Niestety, ze względu na swoją złożoność i liczbę podobnych funkcji, ta funkcja Excela jest niedostatecznie wykorzystywana.

Ta stosunkowo prosta funkcja może pomóc użytkownikom zaoszczędzić czas i wysiłek. Upraszcza uzyskiwanie dokładnych punktów danych z dobrze posortowanego głównego zbioru danych. Umożliwia także proste tworzenie szczegółowych raportów, interaktywnych wizualizacji i złożonych dashboardów.

Utwórz zbiór danych o niestandardowym rozmiarze

Jednym z najczęstszych zastosowań funkcji PRZESUNIĘCIE w programie Excel jest zbieranie zestawów danych o zmiennym rozmiarze. W przypadku dużego zbioru danych można w danym momencie uwzględnić tylko niewielką część danych.

Na przykład, aby udostępnić automatyczny raport dotyczący sumy sprzedaży pracowników, musisz uzyskać sumę za dany tydzień lub tygodnie.

Napisanie innej funkcji SUMA dla każdej sumy może być całkiem łatwe. Jednakże użycie funkcji PRZESUNIĘCIE będzie łatwiejsze w użyciu i szybsze w ponownym użyciu.

Najpierw wpisz datę, od której chcesz zacząć, w komórce B6. Następnie wprowadź liczbę dni, przez które chcesz uruchomić raport w B7. Następnie użyj prostych funkcji SUMA i ŚREDNIA, aby uzyskać więcej danych dla każdego pracownika w wybranym okresie:

=SUM(OFFSET(B2, 0, B6 - 1, 1, B7))

=AVERAGE(OFFSET(B2, 0, B6 - 1, 1, B7)

Dostosowywanie daty lub godziny rozpoczęcia jest tak proste, jak edytowanie komórki, ale można znacznie łatwiej przeciągać i zmieniać rozmiar grup danych.

Umożliwia tworzenie niestandardowych raportów

Użyj tej samej metody, co powyżej, aby utworzyć kompletny raport, dostosowując w łatwy sposób zmienne. Możesz sprawić, że pierwszy argument funkcji OFFSET odniesie się do innego arkusza. Pozwala to na oddzielenie wprowadzania danych i raportowania.

Powyższa akcja umożliwia także przeciągnięcie wybranych fragmentów danych z jednego arkusza do drugiego. Dodatkowo umożliwia wyświetlenie wyłącznie informacji istotnych dla bieżącego raportu. Skonfigurowanie raportu pokazującego sumy dzienne przy użyciu tych samych danych co wcześniej, jest dość proste. Można to zrobić bez pobierania danych dla niepowiązanych dat.

Następująca funkcja offsetu umieszczona w komórce B5 pobierze liczbę dni określoną w B2. Rozpoczyna się w tygodniu wybranym w B1 i pokazuje tylko dane za odpowiedni okres:

=OFFSET(Sheet1!B2, 0, (B1 - 1) * 7, 1, B2 * 7)

Istnieje możliwość ograniczenia wpisów w B1 i B2 oraz stworzenia rozwijanej listy w Excelu z dostępnymi tygodniami przy wykorzystaniu walidacji danych.

Paginacja arkusza kalkulacyjnego

Funkcja PRZESUNIĘCIE umożliwia także prostą paginację arkusza kalkulacyjnego, co często wiąże się z korzystaniem z karty Programista programu Excel. Może to sprawić, że przetwarzanie dużych grup danych będzie prostsze i bardziej efektywne.

Podczas pracy z arkuszami kalkulacyjnymi zawierającymi do 30 000 wierszy danych przeglądanie wszystkich danych na raz może być trudne. Utwórz menu rozwijane, które akceptuje zakres liczb od 1 do 300, co pozwala szybko oddzielić dane według stron ze 100 wynikami na stronę.

Funkcja OFFSET zawsze ma swój punkt kontrolny umieszczony w górnej komórce danych. Jednakże drugim argumentem jest przesunięcie pionowe. Ta wartość jest równa wybranemu numerowi strony minus 100. Przesunięcie jest poziome, trzeci argument można ustawić na 0.

Czwarty argument jest ustawiony na 100, piąty argument to liczba kolumn w zbiorze danych. Ta metoda sprawia, że ​​dzielenie tabel danych i zarządzanie nimi jest niezwykle łatwe.

Poniżej znajduje się przykład danych paginowanych. Zawiera 500 informacji zarejestrowanych z czujnika na jednym arkuszu oraz raport podzielony na strony na drugim arkuszu.

Raport wykorzystuje weryfikację danych w komórce B1 w celu utworzenia menu rozwijanego zawierającego strony. Poniższe wyrażenie w komórce A3 tworzy treść raportu:

=OFFSET(Sheet1!A2, (B1 - 1) * 100, 0, 100, 21)

Powyżej przedstawiono kilka sposobów wykorzystania funkcji offset w programie Excel , które zna niewiele osób, a są one naprawdę przydatne, szczególnie gdy trzeba przetwarzać duże ilości danych.

Mam nadzieję, że artykuł będzie dla Ciebie przydatny.