Pobierz przykładowy skoroszyt
Omówiliśmy w innych artykułach, w jaki sposób funkcje takie jak PRZESUNIĘCIE i ADR.POŚR są niestabilne. Jeśli zaczniesz używać wielu z nich w arkuszu kalkulacyjnym lub masz wiele komórek zależnych od funkcji ulotnej, możesz spowodować, że komputer będzie spędzał zauważalny czas na wykonywaniu ponownych obliczeń za każdym razem, gdy próbujesz zmienić komórkę. Zamiast denerwować się tym, że Twój komputer nie jest wystarczająco szybki, w tym artykule omówimy alternatywne sposoby rozwiązywania typowych sytuacji, w których ludzie używają PRZESUNIĘCIA i POŚREDNIEJ.
Zastąpienie PRZESUNIĘCIA w celu utworzenia dynamicznej listy
Po zapoznaniu się z funkcją PRZESUNIĘCIE powszechnie uważa się, że jest to jedyny sposób na zwrócenie wyniku z dynamicznym rozmiarem przy użyciu ostatnich kilku argumentów. Spójrzmy na listę w kolumnie A, gdzie nasz użytkownik może później zdecydować się na dodanie dodatkowych elementów.
Aby utworzyć listę rozwijaną w komórce C2, możesz zdefiniować nazwany zakres za pomocą lotnej formuły, takiej jak
= PRZESUNIĘCIE($A$2, 0, 0; ILE.LICZB($A:$A)-1;1)
Przy obecnej konfiguracji z pewnością zwróciłoby to odwołanie do zakresu A2:A5. Istnieje jednak inny sposób wykorzystania nieulotnego INDEKSU. Aby to zrobić, pomyśl o tym, że napiszemy odniesienie do zakresu od A2 do A5. Kiedy piszesz „A2: A5”, nie myśl o tym jako o pojedynczym kawałku danych, ale raczej jako o „Punkcie początkowym” i „Punkcie końcowym” oddzielonymi dwukropkiem (np. Punkt początkowy:Punkt końcowy). W formule zarówno StartingPoint, jak i EndingPoint mogą być wynikami innych funkcji.
Oto wzór, którego użyjemy do utworzenia zakresu dynamicznego za pomocą funkcji INDEX:
=$A$2:INDEKS($A:$A; LICZBAA($A:$A))
Zauważ, że stwierdziliśmy, że punktem początkowym dla tego zakresu będzie zawsze A2. Po drugiej stronie dwukropka używamy INDEX do określenia, gdzie powinien znajdować się EndingPoint. COUNTA określi, że w kolumnie A jest 5 komórek z danymi, więc nasz INDEKS utworzy odwołanie do A5. Formuła jest zatem oceniana w następujący sposób:
=$A$2:INDEX($A:$A, COUNTA($A:$A)) =$A$2:INDEX($A:$A, 5) =$A$2:$A5
Korzystając z tej techniki, możesz dynamicznie zbudować odwołanie do dowolnej listy, a nawet dwuwymiarowej tabeli za pomocą funkcji INDEX. W arkuszu kalkulacyjnym z mnóstwem funkcji PRZESUNIĘCIA zastąpienie PRZESUNIĘĆ INDEKSEM pozwoli na szybsze uruchomienie komputera.
Zastępowanie ADR.POŚREDNICH nazw arkuszy
Funkcja ADR.POŚR jest często wywoływana, gdy skoroszyty zostały zaprojektowane z danymi rozproszonymi w wielu arkuszach. Jeśli nie możesz umieścić wszystkich danych w jednym arkuszu, ale nie chcesz używać funkcji ulotnej, możesz użyć opcji WYBIERZ.
Rozważmy następujący układ, w którym mamy dane sprzedaży w 3 różnych arkuszach. W naszym arkuszu podsumowania wybraliśmy, z którego kwartału chcemy wyświetlić dane.
Nasza formuła w B3 to:
=WYBIERZ(DOPASUJ(B2, D2:D4, 0); Jesień! A2, Zima! A2, Wiosna! A2)
W tej formule funkcja MATCH określi, który obszar chcemy zwrócić. Następnie informuje funkcję WYBIERZ, który z poniższych zakresów ma zwrócić jako wynik.
Możesz również użyć funkcji WYBIERZ, aby zwrócić większy zakres. W tym przykładzie mamy tabelę danych sprzedaży w każdym z naszych trzech arkuszy.
Zamiast pisać funkcję ADR.POŚR do budowania nazwy arkusza, możesz pozwolić WYBIERZ określić, w której tabeli przeprowadzić wyszukiwanie. W moim przykładzie nazwałem już trzy tabele tbFall, tbWinter i tbSpring. Wzór w B4 to:
=WYSZUKAJ.PIONOWO(B3; WYBIERZ(PODZIEL(B2, D2:D4, 0); tbFall; tbZima; tbSpring); 2, 0)
W tej formule funkcja PODAJ.POZYCJĘ określi, że chcemy 2NS pozycja z naszej listy. WYBIERZ następnie weźmie te 2 i zwróci odwołanie do tbWinter. Wreszcie nasza VLOOKUP będzie mogła zakończyć wyszukiwanie w podanej tabeli i stwierdzi, że całkowita sprzedaż Banana w zimie wyniosła 6000 USD.
= WYSZUKAJ.PIONOWO(B3, WYBIERZ(PODZIEL(B2, D2:D4, 0), tbFall; tbWinter, tbSpring), 2, 0) = WYSZUKAJ.PIONOWO(B3, CHOOSE(2, tbFall, tbWinter, tbSpring), 2, 0) = WYSZUKAJ.PIONOWO(B3, tbZima, 2, 0) =6000
Ta technika jest ograniczona przez fakt, że musisz wypełnić funkcję WYBIERZ wszystkimi obszarami, z których możesz chcieć pobrać wartość, ale daje to korzyść w postaci uniknięcia niestabilnej formuły. W zależności od tego, ile obliczeń musisz wykonać, ta umiejętność może okazać się bardzo cenna.
