Elektroda.pl
Elektroda.pl
X
Please add exception to AdBlock for elektroda.pl.
If you watch the ads, you support portal and users.

Excel - makro kopiujące dane z wielu arkuszy

janohgw 02 Apr 2021 07:32 720 13
  • #1
    janohgw
    Level 5  
    Dzień dobry,
    Poszukuję rozwiązania, które pomoże mi przyspieszyć pracę.
    Otóż w Skoroszyce o nazwie np Dane 1.0 mam 25 arkuszy o różnych nazwach.
    W każdym z tych arkuszy jest aktualnie zapisanych około 200 wierszy z danymi w 25 kolumnach.
    Program, do którego trzeba później wyeksportowac dane może je przyjąć gdy są zapisane w jednym arkuszu.
    Aktualnie więc będzie to około 5000 wierszy i 25 kolumn z danymi.

    Jak powinno wyglądać makro, które do kolumny np. A w skoroszycie Zestawienie, skopiuje wszystkie dane z kolumny A (zaczynając od wiersza 2) z Arkusza 1, gdy natrafi na pusty wiersz, zacznie kopiować dane z kolumny A ( wiersz 2) z Arkusza 2 itd aż do ostatniego arkusza.
    Taką samą operację trzeba będzie przeprowadzić w innych kolumnach.

    Wszystkie arkusze znajdują się w jednym skoroszycie.

    Góry dziękuję za pomoc.
  • #2
    kinggustav
    Level 26  
    Pisząc "pusty wiersz" masz na myśli puste wszystkie wiersze do końca arkusza czy dosłownie, tylko jeden. W takim przypadku musisz powiedzieć co zrobić z danymi za pustym wierszem. I dlaczego chcesz kopiować tylko kolumnę A a nie od razu wszystkie 25?
    Jeżeli wariant 1 to schemat kopiowania w VBA może być mniej więcej taki (z pamięci, nie testowane):
    Code: vbscript
    Log in, to see the code

    Założenia:
    1. "Stoisz" w arkuszu zbiorczym.
    2. Y zawiera numer wiersza do którego chcesz kopiować, na początku 2, potem rośnie
    3. if zabezpieczy przed skopiowaniem nagłówka gdy arkusz jest pusty.
    4. wykonujesz to dla każdego arkusza
  • #3
    User removed account
    Level 1  
  • #4
    janohgw
    Level 5  
    robertar wrote:
    Czy jest to operacja powtarzalna, to znaczy będziesz w przyszłości ponownie zrzucać tych 25 arkuszy do jednego arkusza?
    Bo jeśli nie, to, moim zdaniem lepiej to zrobić na piechotę, kopiując dane z poszczególnych skoroszytów do jednego. Można wcześniej np. ustawić różne kolory tła dla poszczególnych arkuszy, jak również ponazywać zakresy komórek obejmujących dane z poszczególnych zakresów. Dzięki temu w zbiorczym arkuszu wiadomo będzie z którego arkusza pierwotnie pochodziły dane.

    Dodano po 3 [minuty]:

    Errata:
    Jest - dane z poszczególnych zakresów.
    Powinno być - dane z poszczególnych arkuszy.


    W zasadzie zrobiłem to mniej więcej tak jak opisałeś.
    Zrobiłem odwołanie do komórek z nazwą do każdego arkusza tzn. w arkuszu zbiorczym w polu A2 mam formułę ="Arkusz1'A2 aż do wiersza A201 ="arkusz1"a201, potem wiersz A202='Arkusz2'a2 itd.
    Następnie przeciągnąłem całą formułę w prawo przez wszystkie kolumny, i zmieniłem kolumnę A w kolejnych kolumnach na odpowiednią.
    Trzeba dobrze wykonać zakresy dla jednej kolumny, żeby się gdzieś nie pomylić, a później już jakoś leci.
    Problem pojawia się gdy trzeba wprowadzić nową pozycję w którymkolwiek z arkuszy, wymagać to będzie zrobienia wszystkiego od nowa stąd prośba o pomoc w napisaniu makra.


    [quote=]Pisząc "pusty wiersz" masz na myśli puste wszystkie wiersze do końca arkusza czy dosłownie, tylko jeden. W takim przypadku musisz powiedzieć co zrobić z danymi za pustym wierszem. I dlaczego chcesz kopiować tylko kolumnę A a nie od razu wszystkie 25?
    Jeżeli wariant 1 to schemat kopiowania w VBA może być mniej więcej taki (z pamięci, nie testowane):[/quote]

    Pisząc pusty wiersza mam na myśli puste wiersze już do końca.
    Aktualnie w każdym arkuszu jest 200 pozycji, jest to baza szkła zespolonego.
    zależy mi na makrze, gdyż baza będzie cały czas rozszerzana i dochodzić będą nowe pozycję - jednak uwaga - nie we wszystkich arkuszach naraz.
    Może być taka sytuacja, że w przyszłym tygodniu dojdzie mi 10 nowych pozycji w arkuszu 1 i 5 nowych pozycji w arkuszu 9, pozostałe bez zmian.
    Za pomocą makra mógłbym zaktualizować całą listę jednym kliknięciem.

    Zasada działania docelowo powinna być taka, że po uruchomieniu makra do arkusza Zbiorczy do kolumny A kopiowane są wartości z kolumny A pierwszego arkusza, aż do momentu, gdy pojawi się pusty wiersz.
    Następnie kopiowane są wartości z kolumny A z drugiego arkusza aż do pustego wiersza itd.
    Gdy excel natrafi na pusty wiersz w ostatnim arkuszu, to makro się zatrzymuje.
    Docelowo tworzy się zestawienie w arkuszu Zbiorczy, wszystkich wartości z kolumny A ze wszystkich skoroszytów.
  • #5
    User removed account
    Level 1  
  • #6
    Maciej Gonet
    VBA, Excel specialist
    Tak jak napisał Kolega, Excel nie jest programem bazodanowym, więc takie działania nie będą zbyt efektywne.
    Niemniej 5000 wierszy to nie jest jeszcze liczba przerażająca.
    W jednym z wcześniejszych postów był podany schemat kopiowania. Jeśli to Ci nie wystarcza i nie potrafisz sobie tego dostosować, to załącz wreszcie przykładowy plik, w którym dokładnie pokażesz co masz i co chcesz osiągnąć. Bo na razie jest dużo piany i niewiele z tego wynika. Nie rozumiem po co to zbiorcze zestawienie - logiczniej byłoby wszystko wprowadzać do jednego arkusza, a ewentualne zestawienia cząstkowe generować wg potrzeb.
    Nie bardzo też wiem jak chcesz oddzielać w pliku zbiorczym dane pochodzące z różnych arkuszy. Wszystko to powinieneś pokazać na przykładzie.
    I raz piszesz o kopiowaniu tylko kolumny A, a w innym miejscu o wszystkich kolumnach. Jeśli wyobrażasz sobie kopiowanie kolumnami ze wszystkich arkuszy, to jest to bardzo nieefektywne. Jeśli chcesz mieć docelowo skopiowane wszystkie kolumny ze wszystkich arkuszy, to trzeba kopiować wszystkie kolumny od razu.
  • #7
    janohgw
    Level 5  
    Maciej Gonet wrote:
    Tak jak napisał Kolega, Excel nie jest programem bazodanowym, więc takie działania nie będą zbyt efektywne.
    Niemniej 5000 wierszy to nie jest jeszcze liczba przerażająca.
    W jednym z wcześniejszych postów był podany schemat kopiowania. Jeśli to Ci nie wystarcza i nie potrafisz sobie tego dostosować, to załącz wreszcie przykładowy plik, w którym dokładnie pokażesz co masz i co chcesz osiągnąć. Bo na razie jest dużo piany i niewiele z tego wynika. Nie rozumiem po co to zbiorcze zestawienie - logiczniej byłoby wszystko wprowadzać do jednego arkusza, a ewentualne zestawienia cząstkowe generować wg potrzeb.
    Nie bardzo też wiem jak chcesz oddzielać w pliku zbiorczym dane pochodzące z różnych arkuszy. Wszystko to powinieneś pokazać na przykładzie.
    I raz piszesz o kopiowaniu tylko kolumny A, a w innym miejscu o wszystkich kolumnach. Jeśli wyobrażasz sobie kopiowanie kolumnami ze wszystkich arkuszy, to jest to bardzo nieefektywne. Jeśli chcesz mieć docelowo skopiowane wszystkie kolumny ze wszystkich arkuszy, to trzeba kopiować wszystkie kolumny od razu.


    W załączniku przesyłam przykładowy plik z częścią danych.
    Zbiorcze zestawienie potrzebne jest aby wyeksportować je do programu zewnętrznego, w którym będzie można wybierać odpowiednie szkło.
    Program końcowy ma ten mankament, że można do niego wyeksportować tylko plik w formacie xls, gdzie dane umieszczone są w jednym arkuszu w z góry założonych kolumnach - nie ma możliwości zmiany nazw tych kolumn ani wrzucenia danych znajdujących się w kilku arkuszach tego samego skoroszytu.
    Jak widać szkło różni się od siebie grubością ramki i jej kolorem.
    W załączonym pliku mamy ich 9, ogólnie jest ich 20.

    Dane do wprowadzenia dostałem w takim formacie, tj. 20 arkuszy, w każdym dane do szkła złożonego na innej ramce - nie miałem wpływu na to jak dane zostaną ułożone.

    Jako, że wiem jak musi wyglądać plik końcowy i wiem, że cały czas będą dodawane nowe pozycji do już istniejących,
    Uznałem więc, że najlepszym dla mnie rozwiązaniem będzie makro, które będzie aktualizować dane w kolejnych kolumnach (działające na zasadzie:
    Wariant 1: zaznaczam kolumnę A, uruchamiam makro, które kopiuje po kolei wszystkie wartości z kolumny A kolejnych arkuszy, i tak po kolei zaznaczam kolumny i uruchamiam makro

    Wariant 2: makro kopiuje na raz wartości ze wszystkich potrzebnych kolumn.


    Być może ktoś ma inny pomysł jak to zrobić?
    Dodam, że to nie ja będę dodawał kolejne pozycje do już istniejących.
    Moim zadaniem jest ostateczne doprowadzenie danych do formatu, który przyjmuje program końcowy.
  • #8
    User removed account
    Level 1  
  • #9
    janohgw
    Level 5  
    Jeżeli chodzi o program końcowy, to korzystamy z niego w naszej firmie.
    Zainstalowany jest na komputerze każdego konstruktora, odpalany za pomocą klucza USB
  • #10
    User removed account
    Level 1  
  • Helpful post
    #11
    Maciej Gonet
    VBA, Excel specialist
    Koledzy dobrze radzą, ale to trzeba wdrażać od początku projektu.
    Więc na przyszłość trzeba to zaplanować inaczej.

    Tymczasem jednak potrzebne jest rozwiązanie doraźne, polegające na kopiowaniu tego co jest. Na razie to jest ilość danych, z którą Excel sobie poradzi, ale jak danych będzie przybywać, to kopiowanie będzie trwało coraz dłużej.
    Ponieważ w arkuszach szczegółowych są identyfikatory arkuszy wyliczone formułą, więc kopiowane są wyniki tych formuł (wartości).
    Nie zmieniałem formatowania kolumn arkusza RAZEM, chociaż wydaje mi się niezbyt racjonalne (wszystkie kolumny centrowane, niektóre zbyt wąskie), ale to można albo skorygować ręcznie, albo wprowadzić jeszcze modyfikacje do makra.

    Makro można wywołać skrótem klawiaturowym Ctrl+q (albo z menu Makra).

    Założyłem, że arkusz RAZEM będzie zawsze pierwszy, a kopiowane będą wszystkie pozostałe w takiej kolejności jak są umieszczone w skoroszycie.
  • #12
    ex-or
    Level 27  
    Ja bym zastosował to http://www.pentaho.pl/kettle-etl.html Nie jest potrzebny cały pakiet a tylko "spoon" i ewentualnie "pan". Spoon wykonuje transformacje, czyli w tym przypadku odczyt z kiku xls i zapis do jednego xls. Pan automatyzuje wykonanie transformacji, może nie być tutaj potrzebny. Jaka jest wyższość nad makrem excelowym? Przede wszystkim tworzenie transformacji jest duużo łatwiejsze, szybsze i mniej błędogenne od grzebaniny w VBA. Działa szybciej. Może być odpalany automatycznie. Ma się pełną kontrolę nad formatem danych wejściowych i wyjściowych (żadnych nieporządanych konwersji z których słynie Excel). Układ danych wejściowych i wyjściowych może być dowolny (tak długo jak jest to regularna tabela). Można wprowadzić jakąś kontrolę błędów np. sprawdzanie danych numerycznych (typ danej, zakres danej), sprawdzanie czy dane łańcuchowe zachowują założony schemat (regexp). Błędny rekord po odrzuceniu można zapisać w odrębnym raporcie z nazwą pliku i nr wiersza. Wystąpienie błędu może zakończyć transformację lub ją kontynuować.
    Must have dla każdego kto zajmuje się obróbką danych.
  • #13
    User removed account
    Level 1  
  • #14
    janohgw
    Level 5  
    Maciej Gonet wrote:
    Koledzy dobrze radzą, ale to trzeba wdrażać od początku projektu.
    Więc na przyszłość trzeba to zaplanować inaczej.

    Tymczasem jednak potrzebne jest rozwiązanie doraźne, polegające na kopiowaniu tego co jest. Na razie to jest ilość danych, z którą Excel sobie poradzi, ale jak danych będzie przybywać, to kopiowanie będzie trwało coraz dłużej.
    Ponieważ w arkuszach szczegółowych są identyfikatory arkuszy wyliczone formułą, więc kopiowane są wyniki tych formuł (wartości).
    Nie zmieniałem formatowania kolumn arkusza RAZEM, chociaż wydaje mi się niezbyt racjonalne (wszystkie kolumny centrowane, niektóre zbyt wąskie), ale to można albo skorygować ręcznie, albo wprowadzić jeszcze modyfikacje do makra.

    Makro można wywołać skrótem klawiaturowym Ctrl+q (albo z menu Makra).

    Założyłem, że arkusz RAZEM będzie zawsze pierwszy, a kopiowane będą wszystkie pozostałe w takiej kolejności jak są umieszczone w skoroszycie.


    Działa idealnie. Bardzo dziękuję za pomoc.
    Tak jak napisałeś, aktualnie potrzebne jest działanie doraźne - konieczne jest szybkie wprowadzenie danych do programu.
    Przyszłościowo na pewno system wprowadzania i przechowywania danych wymaga udoskonalenia - wezmę pod uwagę rady pozostałych forumowiczów i spróbuję przebudować bazę.