Excel Forum - Porady, Pomoc,  Excel Help, Excel FAQ Strona Główna
 FAQ  RegulaminRegulamin  Szukaj   Użytkownicy   Grupy   Rejestracja   Profil   Twoje wiadomości   Zaloguj 


Poprzedni temat «» Następny temat
ID tematu: 64074 Skopiuj do schowka zamiana formuł (duża ilość transpozycji) na VBA do tablic
Autor Wiadomość
Roberto33 
Exceloholic


Posty: 130
Wysłany: 08-02-2019, 07:53   zamiana formuł (duża ilość transpozycji) na VBA do tablic

Witam,
Wyciągnąłem sobie całą tabelę formułami ale jak przyszło co do czego to cały plik zaczął za bardzo mielić.

Chciałbym zastąpić taką formułę
operacją porania danych do tablicy
przemielenia 70 transpozycji i zwrócenia do arkusza.... x 365 dni.


Korzystałem z takiej formuły
Kod:
=PRZESUNIĘCIE(DANE!$C$17;VBA_tablice!C$1;PODAJ.POZYCJĘ(VBA_tablice!$A4;DANE!$D$17:$GNQ$17;0))


Mam tabelę z danymi do wyciągnięcia
od D20 do GNQ89 co stanowi

Tabela składa się z 70 wierszy.
Celem jest szybkie pobieranie danych we właściwej oreintacji do wykresów.

od to jest 1 dzień - 01.01.2019
D20 do Q20 = 1 wiersz (14 kolumn) to powinno być zwrócone do arkusza VBA
zwrócenie od
C4 do P4

później 2 dzień 02.01.2019
C5 do P5 (z zakresu arksza DANE R20 do AE 20 (14 kolumn)

etc

bardzo proszę o pomoc przerasta mnie taki temat. Pewnie wykonując to na arkuszu a nie w tablicy jeszcze stracił bym na szybkości operacji.


ps
był problem ze zgarniem pliku ze wzg na pomjęmność i musiąłem go nieco odformatować...

pozdrawiam

przykład_tablice_vs_forumuły.xlsx
Pobierz Plik ściągnięto 10 raz(y) 807.8 KB

ID posta: 362189 Skopiuj do schowka
 
 
Roberto33 
Exceloholic


Posty: 130
Wysłany: 08-02-2019, 09:52   

Witam,
wstawiam poprawiony plik, wcześniej formuła mogła troszkę pomieszać..


Poprawiłem efekt jaki powinien być osiągnięty.
Mam nadzieje że teraz łatwiej będzie odczytać moje intencje.

pozdrawiam

przykład_tablice_vs_forumuły _poprawiony.xlsx
Pobierz Plik ściągnięto 14 raz(y) 853.68 KB

ID posta: 362193 Skopiuj do schowka
 
 
Artik 



Wersja: Win Office 365
Pomógł: 2624 razy
Posty: 8659
Wysłany: 08-02-2019, 21:49   

Takie tam wyliczanki na paluszkach. ;-)
Kod:
Sub TransponujDane()
    Dim lOstKol     As Long
    Dim lOstWie     As Long
    Dim wksZrodlo   As Worksheet
    Dim wksCel      As Worksheet
    Dim i           As Long
    Dim j As Long, k As Long
    Dim w As Long, z As Long
    Dim vTmp        As Variant
    Dim varWynik    As Variant

    Set wksZrodlo = Worksheets("Dane")
    Set wksCel = Worksheets("Dane VBA")

    With wksZrodlo
        lOstKol = .Cells(19, .Columns.Count).End(xlToLeft).Column
        lOstWie = .Cells(.Rows.Count, 1).End(xlUp).Row

        ReDim varWynik(1 To Application.RoundUp((lOstKol - 3) / 14, 0), 1 To (lOstWie - 20 + 1) * 14)

        For i = 20 To lOstWie
            vTmp = .Range(.Cells(i, 4), .Cells(i, lOstKol)).Value

            For j = 1 To UBound(vTmp, 2) Step 14
                w = w + 1
                For k = 1 To 14
                    varWynik(w, k + z * 14) = vTmp(1, k + (j - 1))
                Next k
            Next j

            w = 0
            z = z + 1
        Next i
    End With

    wksCel.Range("C4").Resize(UBound(varWynik), UBound(varWynik, 2)).Value = varWynik

End Sub

Artik
_________________
Persistence is a virtue in the world of programming.
ID posta: 362243 Skopiuj do schowka
 
 
Roberto33 
Exceloholic


Posty: 130
Wysłany: 09-02-2019, 05:29   

Kapitalnie Mistrzu:) Dziękuję.

Nie wiem czy będziesz miał Arti na tyle cierpliwości możesz mi to jakoś wytłumaczyć skąd to się bierze bo to chyba tylko kwestia braku mojej wyobraźni.
Idę krok za krokiem deklaracje tabeli itd ale jak już realizują się działania na tych tablicach to mózg mi nie ogarnia.

Co prawda nie umiałbym tak wymyślić ale na logikę przyjmuje ze tak musi być zdeklarowane
Kod:
varWynik(w, k + z * 14) = vTmp(1, k + (j - 1))

przesunięcie od Kolumny K + VarWynik z *14
=
Vtmp ma podczas rozpoczęcia pętli For next - wpisany step 14.


Ale tego fragmentu nie rozumiem wogóle - dlaczego odejmujemy (j-1)
Kod:
varWynik(w, k + z * 14) = vTmp(1, k + (j - 1))
ID posta: 362249 Skopiuj do schowka
 
 
Artik 



Wersja: Win Office 365
Pomógł: 2624 razy
Posty: 8659
Wysłany: 09-02-2019, 11:31   

A co, na paluszkach nie potrafimy policzyć. To może patyczki? ;-) O! albo na pieniądzach. Pieniądze to wszyscy potrafią policzyć. :mrgreen:

"Duża" pętla
Kod:
 For i = 20 To lOstWie
za każdym obrotem ma za zadanie pobieranie do tablicy tymczasowej vTmp wiersza danych z arkusza Dane. To chyba jest zrozumiałe. Tablica vTmp ma zawsze jeden wiersz, ale od groma kolumn (tu dokładnie 5110). Zadanie nasze polega nie na prostej transpozycji z poziomu do pionu, a na:
1. Pobraniu pierwszych 14. elementów z tablicy vTmp i wstawieniu ich do pierwszego wiersza tablicy wynikowej (varWynik) w pozycji horyzontalnej,
2. pobieraniu kolejnych 14. elementów z tablicy vTmp i wstawianiu do kolejnych wierszy tablicy wynikowej. I tak aż do końca kolumn w tablicy vTmp.

5110/14=365 - liczba faktycznych obrotów pętli For j=....
Oba liczniki k i j potrzebne nam są do wyliczania pozycji kolumny przetwarzanej w danym momencie.
Przy pierwszym obrocie pętli For j=..., jej licznik zawiera 1. Potem mamy pętlę
Kod:
For k = 1 To 14
Popatrzmy tylko na vTmp(1, k + (j - 1)).
Licznik k przyjmuje wartości od 1 to 14. Czyli w pierwszym obrocie For j=... pobierane są kolumny od 1 do 14.
Teraz drugi obrót pętli For j=.... Licznik j nie przyjmuje wartości 2 tylko 15 (1 z pierwszego obrotu + Step 14 = 15). Z tablicy vTmp pobierane są więc kolumny od 15 do 29. W trzecim obrocie 30 do 44 itd.
Roberto33 napisał/a:
dlaczego odejmujemy (j-1)
No teraz to już chyba jesteś w stanie policzyć. :-)

Przy okazji. W kodzie jest drobny błąd, który może się ujawnić w przypadku pewnej niekonsekwencji użytkownika. Ogólnie chodzi o to, że liczba kolumn danych w arkuszu Dane MUSI być wielokrotnością liczby 14. Np. gdyby na końcu dodać tylko jedną kolumnę w wierszu 19 (zamiast 14. kolumn), to wykonanie się wysypie. Wiem, że to mało prawdopodobne, ale lepiej dmuchać na zimne.
Dlatego początek procedury należałoby zmienić na:
Kod:
    With wksZrodlo
        lOstKol = .Cells(19, .Columns.Count).End(xlToLeft).Column
        lOstKol = Application.RoundUp((lOstKol - 3) / 14, 0) * 14 + 3
        lOstWie = .Cells(.Rows.Count, 1).End(xlUp).Row

        ReDim varWynik(1 To (lOstKol - 3) / 14, 1 To (lOstWie - 20 + 1) * 14)

        For i = 20 To lOstWie
...

Artik
_________________
Persistence is a virtue in the world of programming.
ID posta: 362254 Skopiuj do schowka
 
 
Roberto33 
Exceloholic


Posty: 130
Wysłany: 10-02-2019, 01:18   

Jestem bardzo wdzięczny Artik - przynajmniej w zagadnieniach tego problemu - nawet zatrybiłem::::)
ID posta: 362291 Skopiuj do schowka
 
 
Wyświetl posty z ostatnich:   
Odpowiedz do tematu
Nie możesz pisać nowych tematów
Nie możesz odpowiadać w tematach
Nie możesz zmieniać swoich postów
Nie możesz usuwać swoich postów
Nie możesz głosować w ankietach
Nie możesz załączać plików na tym forum
Możesz ściągać załączniki na tym forum
Dodaj temat do Ulubionych
Wersja do druku

Skocz do:  

Powered by phpBB modified by Przemo © 2003 phpBB Group
Theme xandgreen created by spleen& Programosy modified v0.3 by warna
Opieka techniczna www.marketingNET.pl

Archiwum

Strona używa plików cookies.

Kliknij tutaj, żeby dowiedzieć się jaki jest cel używania cookies oraz jak zmienić ustawienia cookie w przeglądarce.
Korzystając ze strony użytkownik wyraża zgodę na używanie plików cookies, zgodnie z bieżącymi ustawieniami przeglądarki.
Sprawdź, w jaki sposób przetwarzamy dane osobowe