Elektroda.pl
Elektroda.pl
X
Proszę, dodaj wyjątek dla www.elektroda.pl do Adblock.
Dzięki temu, że oglądasz reklamy, wspierasz portal i użytkowników.

VBA Excel - Transpozycja tabeli (macierzy)

12 Cze 2015 15:49 2223 14
  • Poziom 8  
    Cześć,

    mam na warsztacie ostatnio temat związany z transpozycją tabeli.
    W tabeli w załączniku potrzebuję przepisać wartości kolumn od 0-9 (stałe - w sensie ilość się nie zmieni, bo po osiągnięciu każdej 9 licznik przeskakuje o kolejny wiersz) przyporządkowane do serii wierszy, do układu dwóch kolumn, otrzymując
    w pierwszej kolumnie licznik z połączenia wiersza i kolumny oraz w drugiej kolumnie wartość. Dla załączonego przykładu podaję kilkanaście pierwszych wyników:
    20,00 0,03
    21,00 0,04
    22,00 0,05
    23,00 0,07
    24,00 0,09
    25,00 0,11
    26,00 0,14
    27,00 0,16
    28,00 0,20
    29,00 0,23
    30,00 0,27
    31,00 0,31
    32,00 0,36
    33,00 0,40
    34,00 0,46
    35,00 0,51
    36,00 0,57
    37,00 0,64
    38,00 0,70
    39,00 0,78
    40,00 0,85

    Do tej pory korzystałem z makra (które dostosowałem sobie z akademii-vba),
    które wyrzuca mi ciąg dwukolumnowy, ale muszę go jeszcze potem obrabiać.
    Dla takiego kodu
    Kod: vba
    Zaloguj się, aby zobaczyć kod


    i wynikiem jest (tu podaję kilkanaście wierszy w załączniku jest całość):
    NAGŁÓWEK WARTOŚĆ
    20 0 0,026
    30 0 0,269
    40 0 0,851
    50 0 1,84
    60 0 3,28
    70 0 5,23
    80 0 7,71
    90 0 10,8
    100 0 14,4
    110 0 17,3
    120 0 20,2
    130 0 23,5
    140 0 27,0
    150 0 31,0
    20 1 0,037
    30 1 0,311
    40 1 0,931
    50 1 1,96
    60 1 3,46
    70 1 5,45
    80 1 7,99
    90 1 11,1
    100 1 14,8
    110 1 17,6
    120 1 20,5
    130 1 23,8
    140 1 27,4
    150 1 31,4

    Z tego dopiero łączę sobie pierwsze dwie kolumny i sortuje (poprzez wyszukiwanie) w kolejnych arkuszach.

    Proszę podpowiedzcie, na pewno jest prostszy sposób, żeby przetransponować tą tabelę na dwie kolumny.
    Chciałem tylko dodać, że tabele, które mam do transpozycji mają różną ilość wierszy.
  • Poziom 25  
    Generalnie to myślę że jest lepszy sposób na opisanie problemu bo czytam to trzeci raz i dalej nie wiem o co chodzi.
  • Poziom 8  
    Fakt, może zakręciłem.

    jeszcze raz:

    mam taką tabelę (w skrócie):
    xx 0 1 2 3 4 5 6 7 8 9
    20 a a a a a a a a a a
    30 b b b b b b b b b b
    40 c c c c c c c c c c c
    50 d d d d d d d d d d

    i potrzebuję ją przetransponować na taką:
    20 a
    21 a
    22 a
    23 a
    24 a
    25 a
    26 a
    27 a
    28 a
    29 a
    30 b
    31 b
    32 b
    33 b
    34 b
    35 b
    36 b
    37 b
    38 b
    39 b
    40 c itd.

    Jak to napisać w vba prościej niż to co znalazłem (bo i tak mi nie załatwia całości zadania i muszę przerabiać ręcznie lub półautomatycznie).

    Szczegóły w ZIPie Transpozycja. Arkusz "Tablica" zawiera rzeczywistą tabelę wyjściową do transpozycji, arkusz "Transpozycja" zawiera mój obecny rezultat zastosowanego makra vba, a arkusz "Oczekiwany_wynik" to to co chcę uzyskać z "Tablicy" i obecnie robię półautomatycznie.
  • Poziom 23  
    Bez mechanizmów Excela będzie chyba najprościej. Czyli robisz dwie pętle: jedna (zewnętrzna) po wierszach tabeli źródłowej. W niej tylko zapamiętujesz pierwszy element wiersza i wykonujesz pętlę po pozostałych elementach, w której wypisujesz zapamiętaną wartość i bieżący (x,y) element. Gdzie i jak to zapisać to pewnie wiesz.
  • Poziom 8  
    Hehe,

    no właśnie niekoniecznie, ale spróbuję:)
  • Pomocny post
    Poziom 25  
    Prościej. Proszę bardzo :)
    Proponuje awk:
    Kod: bash
    Zaloguj się, aby zobaczyć kod

    Uruchamiamy poleceniem:
    Kod: bash
    Zaloguj się, aby zobaczyć kod

    Ale w sumie po co tworzyć pliki ;)
    Kod: bash
    Zaloguj się, aby zobaczyć kod
  • Poziom 8  
    NAXIN:

    właśnie wolałbym z makrem, żeby nie wprowadzać ręcznie wartości
    w pierwszej kolumnie, bo zwykle mam je za każdym razem inne.
    Stała pozostaje zasada, że w wierszach są jakieś wartości z sztywnym krokiem
    np. co dziesięć, wówczas w kolumnach są jedności pomiędzy jednym a drugim wierszem.
    Czasem jest tak, że w wierszach są liczby wzrastające co 1, a w kolumnach od 0,0 do 0,9, więc szukam czegoś co utworzy ciąg (w pionie) powstały z zestawów sum
    wiersz1+kolumny (od 0 do 9) potem wiersz2+kolumny (od 0 do 9) i tak aż nie skończą się wiersze, stąd myślę, że pętla jest najlepszym rozwiązaniem.

    namok:
    nie mam doświadczenia w awk, jak to uruchomić, w wierszu poleceń?
    mogę zrobić z tego batcha? masz jakiegoś linka, gdzie można się z tego podszkolić?
  • Pomocny post
    Poziom 25  
    Ja używam tego pod linuksem ale wersje pod windows też są: https://www.gnu.org/software/gawk/manual/html_node/PC-Installation.html.
    Kursów też jest sporo, pierwszy w googlach: http://sokrates.mimuw.edu.pl/~sebek/awk.html.
    Czasem może występować pod hasłem gawk.

    Uruchomienie pod windows pewnie będzie podobne do pierwszego:
    Kod: bash
    Zaloguj się, aby zobaczyć kod
    (tabela.txt - dane źródłowe). Być może przekierowanie wyniku do pliku ">" będzie inne ale google powinno być pomocne ;).
  • Poziom 8  
    -psiak- @NAXIN

    uwielbiam formuły i funkcje w Excelu i za wasze bardzo dziękuję
    przy okazji podzielę się ciekawym rozwiązaniem tablicowym, które
    wykorzystywałem w swoim dotychczasowym półautomacie.

    Co do idei: aby skorzystać z funkcji tablicowej potrzebuję "małą tablicę" z ciągami nagłówków
    (niestety musiałem go przygotować ręcznie i w każdym przypadku na nowo)
    zatem w podanym przykładzie (tabliczka1.xls) w pierwszej kolumnie uzyskuję ciąg liczb przyrastających o 1 (licznik 0-9) oraz przyrastających o 10 (20-90)
    w zakresie 20 - 99, jak niżej:
    20 0
    20 1
    20 2
    20 3
    20 4
    20 5
    20 6
    20 7
    20 8
    20 9
    30 0
    (..)
    90 9
    następnie korzystam z funkcji wyszukaj.pionowo i jako szukaną wartość ustawiam pierwszą wartość tej kolumny (czyli 20 pod A13), przeszukiwaną tablicą jest tabela źródłowa, koniecznie musi zawierać pierwszą kolumnę (tu zakres $A$1:$K$19), dalej zamiast numeru indeksu (czyli kolumny, której wartości będą przepisywane) korzystam z funkcji podaj.pozycję, w która lokalizuje mi element macierzy (tabeli) na przecięciu wartości z kolumny A i wiersza 1 tabeli. Wszystkie dopasowania ustawiam jako dokładne (0) i w ten sposób uzyskuję dynamiczne przeszukiwanie całej tabeli z przepisywaniem do układu 2-kolumnowego. Problemem jest tylko ustawianie pierwszej kolumny, co trzeba zrobić ręcznie (inaczej jeszcze nie potrafię) choć dla ograniczonej liczby przypadków (w sensie wierszy tabeli) można ustawić na sztywno przepisywanie, ja zrobiłem tak dla 15 wierszy, ale to tez jest błędogenne.

    W załączeniu kolumna z moim rozwiązaniem.
    Może się przydać w innych sytuacjach.


    PRL

    właśnie o to chodziło. Krótko i na temat. Bardzo dziękuję.
    Czułem, że to prostsze niż ciekawsze.

    Wszystkim serdeczne DZIĘKI,
    po raz kolejny uczestnicy serwisu spisali się na medal.
    Temat uważam za wyczerpany.
  • Poziom 32  
    Widać że nie bardzo zrozumiałeś.
    Formuły podane przez @NAXIN oraz przeze mnie wpisuje się w pierwszą komórkę a dalej się "rozciąga" na pozostałe komórki.
    My zrobiliśmy to samo tylko że te współrzędne wyliczamy automatycznie.
  • Poziom 8  
    -pisak-

    formuła, jej treść lokalizacja itp. są dla mnie jasne,
    natomiast nie rozwiązuje on całości tematu, bowiem oprócz przepisywania współrzędnych z tabeli potrzebuję jeszcze wartości tej kolumny obok, której
    zostają transponowane wartości z tabeli.

    Jest to o tyle istotne, że te "nagłówki" wynikające z połączenia pierwszej liczby w wierszach tabeli i 10 cyfr kolumn (0-9),
    w zależności od tabeli, którą opracowuję, mają zmienne wartości i zmienną ilość wierszy np. raz jest od 20 do 100 a innym razem od 200 - 1000,
    dlatego zależało mi również na mechanizmie, który będzie tworzył ciąg
    łączący 10tki (z wiersza) z jedynkami (z kolumn) - coś jak w moim rozwiązaniu.
    20+0
    20+1
    20+2
    m+n itp.

    Tak czy inaczej dziękuję za pomoc, poznałem nowe zastosowanie funkcji wyszukaj,
    co również przypisuję jako duży plus tego posta.
  • Poziom 32  
    Wg równania od @NAXIN możesz podać normalnie 27 i wyszuka co trzeba, twoja formuła potrzebuje 20 oraz 7 - wg mnie utrudnienie.