O tym, jak Excel może pracować za Ciebie
Blog > Komentarze do wpisu

Transpozycja w Ms Excel, czyli zamiana wierszy na kolumny lub odwrotnie. Sposób 3

Sposób 3

Ten wpis jest kontynuacją dłuższego tematu, którego początek znajdziesz tutaj.

Tym razem, zadanie polegające na automatycznej zamianie rekordów bazy danych zapisanych w jednej kolumnie na rekordy zapisane w kolejnych wierszach, wykonam przy pomocy funkcji WIERSZ() i NR.KOLUMNY(). Funkcje te wywołane bez argumentu, zwracają odpowiednio numer wiersza i numer kolumny komórki, w której zostały wywołane.

Ponownie zakładam, że zaimportowane dane masz w osobnym arkuszu o nazwie Dane1. Ty natomiast ustaw kursor w komórce A1 nowego arkusza i wpisz do niej formułę

=ADR.POŚR("Dane1!A"&WIERSZ()*5-5+NR.KOLUMNY())

Przypominam, że funkcja ADR.POŚR dokonuje zamiany ciągu znaków, podanych w jej argumencie, na adres/odwołanie. Zauważ, że argumentem tej funkcji jest faktycznie ciąg znaków, który złożony jest z kilku elementów.

Pierwszym elementem jest ciąg znaków "Dane1!A" reprezentujący fragment odwołania. Przy pomocy operatora & przyklejony zostaje do niego wynik zwracany przez wyrażenie WIERSZ()*5-5+NR.KOLUMNY(). W komórce A1 wartości zwracane przez funkcje WIERSZ() i NR.KOLUMNY() będą takie same i równe 1, dlatego komórce A1 wynik zwracany przez to wyrażenie będzie równy 1*5-5+1 = 1. Idąc dalej, w komórce A2, wyrażenie to będzie równe 1*5-5+2=2, w komórce B12*5-5+1=6, itd.

Jasne więc jest, że w zależności od tego, w której komórce znajdzie się formuła, postać ciągu znaków reprezentującego odwołanie, będzie za każdym razem inna. Na podstawie powyższych obliczeń mogę teraz napisać, że w komórce A1, ciąg znaków będących argumentem funkcji ADR.POŚR, będzie postaci "Dane1!A1" …. A stąd funkcja ta zwróci odwołanie Dane1!A1. Inaczej pisząc, komórka A1 będzie zawierała formułę =Dane1!A1. Analogicznie w komórce A2, wynikiem zwracanym przez funkcję ADR.POŚR, będzie formuła =Dane1!A6., w komórce B1 – formuła =Dane1!A1.

W ten sposób powstanie nowa tabela, której wartości odczytywane są z arkusza dane. Ze względu zastosowany mechanizm, jej postać będzie zgodna z naszym żądaniem – dane kolejnych osób będą zapisane teraz wierszami.

Nie trudno dostosować ten przykład do własnych potrzeb. Jeśli przykładowo spotkałbyś się z podobnym zadaniem, ale pojedynczy rekord bazy zawierałby 9, a nie 5 pól jak w omawianym wyżej przykładzie postać formuły zmieni się nieznacznie do

=ADR.POŚR("Dane1!A"&WIERSZ()*9-9+NR.KOLUMNY())

Jeśli dodatkowo chciałbyś umieścić nową tabelę w innym miejscu w arkuszu modyfikacja formuły również nie przysporzy większego kłopotu. Potrzeba jedynie odjąć odpowiednie wartości od wyników zwracanych przez funkcje WIERSZ() i NR.KOLUMNY().

Zakładam dla przykładu, że chcesz, aby początek tabeli znajdował się w komórce C10. Wówczas formuła będzie postaci

=ADR.POŚR("Dane1!A"&(WIERSZ()-9)*9-9+NR.KOLUMNY()-2)

W komórce C10 wartość zwracana przez funkcję WIERSZ() to 10, przez funkcję NR.KOLUMNY() - 3. Ty jednak nadal potrzebujesz, aby kopiowanie danych z arkusza Dane1 rozpoczęło się od komórki A1. Dlatego też musisz zmodyfikować wartości zwracane przez te funkcje tak, aby dawały dokładnie wyrażenie w postaci 1*9-9+1 = (10-9)*9-9+(3-2) = 1.

I to byłoby na tyle. Masz jakieś pytania? To napisz.

środa, 16 czerwca 2010, slawek.sroka

Polecane wpisy