Nie uwierzycie, ale temat tego wpisu wymyśliłam 3 kwietnia 2015 roku. Od tego czasu pisałam na blogu o milionie innych wskazówkach, które były z różnych powodów „pilniejsze”. Powiem Wam, że mam takich pomysłów cały segregatorek formatu A6 (ze Statuą Wolności na okładce – taki akurat był w sklepie ;)). Co jakiś czas dorzucam tam pomysły, ale prawda jest taka, że i tak wciąż dochodzą mi nowe, właśnie te pilniejsze i segregatorek sobie leży prawie zapomniany…

Tak wygląda mój segregatorek 🙂

Ale dziś się zdenerwowałam i stwierdziłam, że go odkurzę i napiszę o czymś, co tam się znajduje. Przeglądając go, znalazłam ciekawy temat, o którym kiedyś (bardzo dawno) już trochę pisałam. Chodzi o temat transpozycji, czyli jak zamienić kolumny na wiersze lub odwrotnie. Używałam do tego metody kopiowania i opcji wklej specjalnie jako transpozycja. Metoda świetna i skuteczna – sama bardzo często jej używam. Niestety w niektórych sytuacjach jest niewystarczająca. Głównym założeniem wklejania specjalnego jest to, że zadziała ono zgodnie z oczekiwaniami tylko wtedy, gdy ręcznie wkleimy wartości (np. na formuły już nie zadziała tak, jakbyśmy tego chcieli). Można więc w uproszczeniu powiedzieć, że jest statyczna. A przecież czasem chcemy, by taka transpozycja się aktualizowała, była dynamiczna, czyli działa się za pomocą formuły. Z moich obserwacji wynika, że wtedy użytkownicy po prostu ręcznie wpisują w pionie odwołania do odpowiednich komórek w poziomie lub odwrotnie. Trochę to karkołomne, ale działa. 🙂

Mało osób jednak wie, że istnieje funkcja, która umie sprawić, aby transpozycja była dynamiczna. Ma ona pewien haczyk, więc trzeba wiedzieć, jak ją zastosować, ale zobaczycie sami, że jest prościutka. Pokażę wam!

Tradycyjnie zacznijmy od formatki:

Formatka

Czyli chcemy różowe poziome komórki przenieść za pomocą formuły do czarnej pionowej ramki. Użyjemy do tego funkcji o nazwie TRANSPONUJ. I już w tym momencie napotykamy na wspomniany wyżej haczyk. 😉 Normalnie, czyli przy wpisywaniu zwykłych formuł, piszemy po prostu formułę w pierwszej komórce z danego zakresu (czyli tutaj byłaby to B5) i kopiujemy ją do pozostałych komórek. Z funkcją TRANSPONUJ niestety nie możemy tak zrobić, ponieważ jest to funkcja tablicowa. Wymaga specjalnego traktowania, czyli:

1. Po pierwsze zaznacz zakres, do którego chcesz przetransponować dane. Zakres ten musi mieć identyczny rozmiar, co obszar transponowany (różowy), tylko obrócony o 90 stopni. U nas będzie to zakres B5:B10 (czarna ramka). To właśnie jest ten haczyk. 🙂

2. Wpisz formułę:

=TRANSPONUJ(B3:G3)

Śmiało możesz wskazać zakres B3:G3 myszką.

3. Formułę TRANSPONUJ zatwierdź kombinacją klawiszy Ctrl + Shift + Enter. Dzięki temu Excel będzie wiedział, że jest to formuła tablicowa.

I to tyle. Cała filozofia. Efekt dostaniemy taki:

Wynik

W odróżnieniu od kopiowania i wklejania specjalnego ta metoda jest dynamiczna, czyli cokolwiek zmienimy w różowym zakresie – zmieni się też w czarnej ramce.

Oczywiście można byłoby się czepiać, że fakt zaznaczania komórek, do których wpisujemy formułę jest dość uciążliwy, ale niestety that’s the way it is. Tak działają formuły tablicowe i jest to nie do przeskoczenia… Od razu też powiem, że – jeśli chcielibyście jakkolwiek edytować tę formułę – to również najpierw trzeba zaznaczyć cały zakres, w którym ona się znajduje (czyli czarną ramkę) i dopiero wtedy edytować. Zmiany również należy zatwierdzić kombinacją klawiszy Ctrl + Shift + Enter. Jeśli bowiem spróbujecie edytować formułę bez zaznaczenia wszystkich komórek, dostaniecie taki komunikat:

Komunikat

Strasznie to irytujące z jednej strony, ale z drugiej… ma kilka zalet, np. taką, że większość użytkowników nie będzie umiała nam tej formuły zepsuć. 😉 Chyba że skasuje wszystko z czarnej ramki… Formuły tablicowe – temat rzeka!

Jestem ciekawa, ile razy Wam by się to już przydało? Zachęcam do komentowania i dzielenia się doświadczeniami. 🙂

I wersja wideo: