Excel Forum - Porady, Pomoc, Excel Help, Excel FAQ - Użycie znaków zastępczych w odwołaniach 3-W

Maciej Gonet · Wysłany: 03-11-2021, 17:40 **Użycie znaków zastępczych w odwołaniach 3-W**

Przeglądając ostatnio filmy na youtube natknąłem się na filmik Leili Gharani z nieznaną mi informacją nt. możliwości użycia znaków zastępczych (* i ?) w odwołaniach 3-W. Nie jest to nic nowego (sprawdziłem najstarszą wersję, do której mam dostęp - 2003 i działa to we wszystkich wersjach tak samo). Ale ja nigdy wcześniej nie napotkałem takiej informacji.
Okazuje się, że przy podawaniu nazw arkuszy w odwołaniu 3-W można używać znaków zastępczych, ale nie jest to typowe ich użycie. Znaki te są akceptowane i uwzględniane, ale nie pozostają na stałe w formule. Przy opracowaniu formuły adres ze znakami zastępczymi zamieniany jest na ciąg odwołań do wszystkich arkuszy pasujących do wzorca i w takiej postaci jest pamiętany.

Jeśli w formule używamy gwiazdki, nazwę zawierającą gwiazdkę lub cały zakres arkuszy należy ująć w pojedyncze apostrofy, natomiast gdy używamy znaku zapytania apostrofy nie są potrzebne. Ponadto przy analizie odwołań pomijany jest arkusz bieżący (ten z którego pochodzi wywołanie), niezależnie czy jego nazwa pasuje do wzorca, czy nie.
W wyniku otrzymujemy ciąg odwołań do pojedynczych arkuszy lub grup arkuszy, które były obok siebie w skoroszycie przy przetwarzaniu formuły i pasowały do wzorca. Tak wygenerowane odwołanie jest już w pewnym sensie statyczne, to znaczy Excel nie pamięta, jak ono powstało i jeśli wprowadzimy jakiś nowy arkusz lub zmienimy nazwę któregoś z obecnych, to zgodność z pierwotnym wzorcem już nie będzie ponownie sprawdzana.
Ogranicza to też grupę funkcji, które mogą akceptować takie odwołania tylko do funkcji agregujących (typu SUMY), bo funkcje tablicowe (jak np. CZĘSTOŚĆ, MIN.K), które akceptują standardowe odwołania 3-W (zapisane w jednym ciągu), nie poradzą sobie z odwołaniem w formie listy.

Możliwość użycia znaków zastępczych jest z jednej strony wygodna, gdy mamy w skoroszycie wiele arkuszy o zróżnicowanych nazwach i chcemy wydobyć dane z grupy podobnych arkuszy, jednak z drugiej strony edycja takich odwołań jest utrudniona przez to, że wzorcowe wyrażenie nie jest zachowane. Można tu sobie trochę pomóc stosując VBA, bo da się zapisać makro, w którym przypisuje się wzorcowe odwołanie ze znakami zastępczymi do właściwości Formula lub pokrewnej i potem można to sobie wywoływać w miarę potrzeby. Ewentualnie można utworzyć kopię formuły w postaci tekstu.
Nie da się natomiast wykorzystać nazwy arkuszowej z takim odwołaniem (można użyć nazwy odnoszącej się do komórek z tego samego arkusza albo do nazwy zawierającej klasyczne 1-częściowe odwołanie 3-W, ale nie do ciągu odwołań z różnych arkuszy).

Może ktoś zna jeszcze jakieś inne aspekty użycia znaków zastępczych w tym przypadku?

Gwiazdki3W.xlsm

Pobierz Plik ściągnięto 193 raz(y) 17.4 KB

Maciej Gonet · Wysłany: 03-11-2023, 15:21

Dwa lata temu napisałem pierwszy post w tym temacie po "odkryciu" nietypowego sposobu filtrowania nazw arkuszy przy odwołaniach 3-W. Sposób ten ma zalety i wady, ale warto o nim pamiętać, szczególnie w kontekście nowych funkcji Excela, które akceptują odwołania 3-W i dzięki temu znacząco rozszerzają możliwość ich użycia z innymi funkcjami. Wspominał o tym niedawno Bill Szysz w odpowiedzi na jeden z tematów.

Spośród nowych funkcji Excela, które akceptują odwołania 3-W należy wskazać przede wszystkim funkcje STOS.PION, STOS.POZ, POŁĄCZ.TEKSTY i ZŁĄCZ.TEKST. Funkcje te traktują odwołanie do każdego arkusza jako osobny argument, zachowując naturalną kolejność arkuszy. Funkcje STOSów nie zmieniają przy tym orientacji zakresów źródłowych, natomiast funkcje tekstowe tworzą jeden ciąg tekstowy odczytując dane z kolejnych arkuszy wierszami.

Istotne jest, że te funkcje nie mają praktycznie ograniczenia liczby argumentów, dzięki czemu odwołania 3-W "zaszyfrowane" przez użycie znaków zastępczych "?" i "*", które mogą przekształcić się w listę odwołań są prawidłowo obsługiwane przez te funkcje. Funkcje STOSów przekształcają w ten sposób odwołania 3-W w tablice, a funkcje tekstowe w liniowy łańcuch tekstowy, które to struktury mogą podlegać dalszej obróbce za pomocą innych funkcji. Tu mam na myśli przede wszystkim funkcję INDEKS, gdyż do tej pory nie było możliwości indeksowania odwołań 3-W.

Jest to bardzo istotne, gdyż dotąd odwołania 3-W były obsługiwane przez niezbyt liczną grupę funkcji, głównie agregujących (typu SUMY), a jeśli już zdarzyło się, że była możliwość ich obsługi przez funkcje z przypisanymi rolami argumentów, to ograniczało się to do obsługi pojedynczego odwołania 3-W. W tym miejscu warto przypomnieć, że odwołań 3-W nie można otoczyć nawiasami, tak jak jest to możliwe w przypadku odwołań odnoszących się do jednego arkusza (w ten sposób tworzy się zakres złożony, który jest traktowany przez funkcje jako jeden argument), gdyż zakres złożony nie obsługuje odwołań z różnych arkuszy.

Wśród funkcji, które potrafią bezpośrednio obsłużyć pojedyncze odwołania 3-W można wskazać: LET, ARKUSZE, CZĘSTOŚĆ, DO.KOLUMNY, DO.WIERSZA, MAX.K, MIN.K, POZYCJA i kilka innych. Tu warto zauważyć, że funkcja AGREGUJ nie obsługuje odwołań 3-W bezpośrednio. Funkcja LAMBDA obsługuje odwołania 3-W o tyle, o ile korzysta z funkcji, które je obsługują. Funkcje pomocnicze LAMBDA nie obsługują 3-W.

Na koniec chciałbym jeszcze powrócić do zapisu odwołania 3-W "zaszyfrowanego" za pomocą znaków zastępczych. Wspominałem w poprzednim poście, że trudno jest taki zapis zachować, bo jest on od razu po wpisaniu formuły "odszyfrowywany" i zamieniany na jedno lub ciąg odwołań 3-W, który dalej pozostaje już statyczny. Pewną możliwość stwarzałoby przechowanie takiego zapisu w formie tekstowej, ale niestety funkcja ADR.POŚR nie obsługuje odwołań 3-W. Światełko w tunelu zaświeciła jednak funkcja LAMBDA w powiązaniu ze starą funkcją makr XLM SZACUJ. Możemy sobie zdefiniować np. funkcję LSZACUJ, według zapisu:

Kod:

=LAMBDA(x; SZACUJ(x))

Wywołanie może być takie:

Kod:

=LSZACUJ("STOS.PION('A*'!A1:B1)")

Ważne jest, żeby argumentem funkcji LSZACUJ było nie samo odwołanie 3-W, lecz to odwołanie użyte jako argument funkcji, która potrafi je obsłużyć. W tym przypadku funkcja STOS.PION zostanie użyta do wszystkich arkuszy, których nazwy rozpoczynają się od A (ewentualnie z pominięciem arkusza, z którego następuje wywołanie).
To wywołanie jest nieulotne, o czym należy pamiętać. Jeśli zmienimy strukturę arkusza dodając lub usuwając arkusz, którego nazwa pasuje do wzorca, funkcja nie przeliczy się automatycznie. Należy ją przeliczyć np. przez Ctrl+Alt+F9.

3WaNoweFunkcje.xlsm

Pobierz Plik ściągnięto 115 raz(y) 13.95 KB