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

EXCEL - problem z przenoszeniem danych między arkuszami

Wieslaw3 17 Gru 2010 19:56 17680 52
  • #1 17 Gru 2010 19:56
    Wieslaw3
    Poziom 18  

    Mam dwa arkusze. W pierwszym kilka wierszy danych, które zaimportowałem z pliku XML. W drugim arkuszu chciałem mieć tylko te dane z arkusza pierwszego, które mnie interesują.
    Przenoszę więc dane najprostszą formułą:
    =Arkusz1!J2
    =Arkusz1!L2
    =Arkusz1!M2
    itd.
    Zauważcie, że formuły te odnoszą się jednego i tego samego wiersza (drugiego) w arkuszu 1. Zamiarem moim było drukować uformowane w arkuszu 2 dane, później chciałem usunąć cały wiersz do którego odnoszą się powyższe formuły (czyli wiersz 2 w arkuszu 1). Jak wiadomo usunięcie wiersza powoduje, że wiersz 3 „wskakuje” na miejsce wiersza 2 wynikiem czego w arkuszu drugim powinny (według mnie) pojawić się inne wartości.
    Niestety po usunięcia wiersza z danymi w arkuszu 1, w arkuszu 2 pola z w/w formułami się najeżyły czymś takim #ADR!

    Czy może mi ktoś poradzić jakiej formuły powinienem użyć

    0 29
  • Pomocny post
    #2 17 Gru 2010 21:22
    marek003
    Poziom 40  

    Nie można usuwać komórek do których odwołują się formuły bo właśnie wtedy pojawia się błąd odwołania. Odwołania również są usuwane.

    Jeżeli chcesz by odwołanie było cały czas "sprawne" kasuj dane a nie usuwasz wiersz.

    Jeżeli chcesz aby wybierać różne wiersze w arkuszu docelowym skorzystaj z formuły indeks() i poprzez dodatkową komórkę określającą nr wiersza wybieraj dany wiersz z arkusza1.

    1
  • #3 17 Gru 2010 22:13
    Wieslaw3
    Poziom 18  

    Jeśli skasuje tylko dane (czyli wyczyszczę wiersz) nie osiągnę zamierzonego efektu zmiany danych w arkuszu2. Ale skoro piszesz, że tak nie można to trudno. Trzeba kombinować inaczej.
    A może ktoś z was wie jak wyglądał by kod VBA który zliczy ile razy w kolumnie np Y występuje ta sama liczna. Potrzebne jest to dla instrukcji For... Next.

    0
  • #4 17 Gru 2010 22:56
    marek003
    Poziom 40  

    Co do pierwszego proponuje wykorzystać funkcje indeks

    np:
    =INDEKS(Arkusz1!J1:J5000;A1)
    gdzie w A1 będziesz podawał nr wiersza np 2 potem 3

    Druga rzecz

    Cytat:
    ile razy w kolumnie np Y występuje ta sama liczna
    Czy "liczna" to liczba?

    Jeżeli tak to np. taki kod dla liczby 1 (wykorzystanie w VBA funkcji excelowskiej licz.jeżeli):

    Code:
    szukana = 1 
    
    ilosc = WorksheetFunction.CountIf(Range("Y1:Y3000"), szukana)

    0
  • #5 18 Gru 2010 11:39
    marek003
    Poziom 40  

    Jeszcze jeden pomysł mi przyszedł z rana :).
    Pozwoli na usuwanie wierszy z arkusza1 nie niszcząc odwołań i spełni twoje oczekiwania.
    Wykorzystaj funkcję Adres() i adr.pośr()

    W funkcji adres() tworzysz adres za pomocą liczb:
    ADRES(nr_wiersza ;nr_kolumny ;typ_adresu;a1;tekst_arkusz)

    A za pomocą ADR.POŚR(adres_tekst;a1) odwołasz się bezpośrednio do danych wskazanych w adresie,
    czyli dla twoich odwołań:
    =Arkusz1!J2
    =Arkusz1!L2
    =Arkusz1!M2

    będzie
    =ADR.POŚR(ADRES(2;10;1;1;"Arkusz1"))
    =ADR.POŚR(ADRES(2;12;1;1;"Arkusz1"))
    =ADR.POŚR(ADRES(2;13;1;1;"Arkusz1"))

    Wtedy możesz usuwać 2 wiersz a kolejny (3) wiersz wskoczy na miejsce 2 i "jego" dane zostaną "przekazane" w komórki gdzie wpiszesz powyższe funkcje.

    0
  • #6 18 Gru 2010 13:36
    Wieslaw3
    Poziom 18  

    Z tym adresem pośrednim DZIAŁA SUPER!!! Jesteś wielki :)
    Co do wyszukiwania liczby (nie liczny - przepraszam) troszkę źle mnie zrozumiałeś. Ja nie znam wartości tej liczny.
    Dokładniej chodzi mi o to, żeby funkcja policzyła ile razy w kolumnie Y występuje liczba występująca w komórce Y2. Zwrócona wartość powinna się zapisać do zmiennej PESEL

    0
  • #7 18 Gru 2010 16:56
    marek003
    Poziom 40  

    Wieslaw3 napisał:
    ...Ja nie znam wartości tej liczny.
    Dokładniej chodzi mi o to, żeby funkcja policzyła ile razy w kolumnie Y występuje liczba występująca w komórce Y2. Zwrócona wartość powinna się zapisać do zmiennej PESEL


    To ja teraz tak to rozumiem:
    Założenie:
    szukana liczba jest w komórce A1
    Kolumna/tabela z liczbami to B1:B100
    zmienna PESEL ma informować ile razy wystąpiła dana liczba
    np jeżeli liczba "32" wpisana w A1 występuje 5 razy w kolumnie/tabeli B1:B100 to zmienna PESEL przyjmie wartość 5.

    Code:
    Sub cos()
    

    szukana = Range("A1").Value
    PESEL = WorksheetFunction.CountIf(Range("B1:B100"), szukana)

    End Sub


    Albo jeszcze krócej (bez zmiennej "szukana"):
    Code:
    Sub cos()
    

    PESEL = WorksheetFunction.CountIf(Range("B1:B100"), Range("A1").Value)

    End Sub



    Tak na marginesie pytanie: chyba VBA w twoim przypadku to dopiero pierwsze kroki? Nie ma się czego wstydzić. Tylko warto o tym wspomnieć bo inaczej się "podpowiada" metody początkującym a inaczej tym którzy mają już jakieś doświadczanie.
    Powyżej to tylko podpowiedz jak "wyciągnąć" ilość wystąpień "szukanej" w tabeli. Nie wiem co chcesz docelowo osiągnąć więc np. nie "kierunkuje" tego na konkretny arkusz.

    Przykład do kodu:

    0
  • #8 18 Gru 2010 18:19
    walek33
    Poziom 28  

    A tak sorry, że się wcinam ale...
    myślę sobie, że jak nie jest niezbędnie konieczne używanie VBA to lepiej jest wykorzystywać wbudowane formułki oferowane przez ludzi Billa.
    I w tym przypadku posiłkowałbym się dedykowaną Licz.Jeżeli() lub odpowiednią {}. Kolega marek003 wie o co chodzi. :D

    0
  • #9 18 Gru 2010 21:18
    Wieslaw3
    Poziom 18  

    No taaak, są to moje wielkie początki VBA :)
    A czy nie ma przeszkód jeśli wartość szukana (czyli ta liczba PESEL) jest w tej samej kolumnie którą przeszukujemy?

    Edit: Sprawdziłem - działa nawet gdy liczba szukana jest w tej samej kolumnie:

    Code:
    PESEL = WorksheetFunction.CountIf(Range("B1:B100"), Range("B1").Value)


    Wyczytałem że jest rodzaj pętli której można z góry określić ilość powtórzeń. Mając powyższą funkcję powinno się to udać.

    Kolejna prośba to kod vba który wypełni mi w określonym zakresie puste komórki zerami.

    0
  • #10 18 Gru 2010 21:23
    walek33
    Poziom 28  

    Przeszkód nie ma. Ale mając pewność, że ona tam jest po co jej szukać?

    0
  • #11 18 Gru 2010 21:27
    Wieslaw3
    Poziom 18  

    Nie chodzi o to żeby ją szukać, tylko żeby zliczyć ilość komórek w których ona występuje (w danej kolumnie).

    Kolejna prośba o instrukcję
    Zakres komórek od A1 do AS100
    jeśli pusta komórka wstaw "0"

    0
  • #12 19 Gru 2010 12:56
    marek003
    Poziom 40  

    Nie wiem co chcesz osiągnąć ale może rzeczywiście to nie ważne. Zapewne przyświeca ci jakiś cel.

    Sztywny zakres komórek A1:AS100

    Code:
    Sub cos2()
    

    For y = 1 To 45
        For x = 1 To 100
            If Cells(x, y).Value = "" Then Cells(x, y).Value = 0
        Next x
    Next y

    End Sub


    Tłumaczenie:

    "Wchodzimy" w pierwszą pętlę Y, y otrzymuje 1 i "wchodzimy" w drugą pętle X, x otrzymuje 1. "wchodzimy" w warunek: jeżeli (if) wartość komórki o parametrach: wiersz 1 i kolumna 1 jest pusta ("") wtedy (then) nadaj tej komórce wartość 0. Jeżeli nie to nie rób nic (to w domyśle). Następnie nadaj wartość x kolejny numer (next x) czyli 2 i znów zacznij pętlę "X" więc znów sprawdź czy komórka o parametrach: wiersz 2 kolumna 1 spełnia warunek . Jak tak to 0 jak nie to nic i znów kolejne x. I tak aż do x = 100. Bo jak x =100 wychodzimy z pętli "X" i wchodzimy na następny y (next y) czyli y=2, w górę i znów 100 powtórzeń pętli X . Kolejne y i znów 100 powtórzeń x itd (45 razy).

    Myślę że to w miarę jasne.

    Jeżeli był błąd i zakres powinien być :
    Sztywny zakres komórek A1:A100
    To pomiń pętlę Y a w pętli X w warunku IF za y wpisz na stałe 1 (dla kolumny A)

    0
  • #13 19 Gru 2010 15:25
    adamas_nt
    Moderator Programowanie

    Jak zwykle między wódkę a zakąskę :)

    Marku, zapomniałeś o For Each, gdzie wygodnie zmieniasz zakres(y).

    Code:
    Dim kom As Range
    
    For Each kom In Range("A1:AS100")
      If kom = "" Then kom = 0
    Next

    I jeszcze jedno uproszczenie (wg mnie wystarczy)
    Code:
    =ADR.POŚR("Arkusz1!J2 ")
    i adekwatnie dla L2 i M2.

    0
  • #14 19 Gru 2010 19:09
    Wieslaw3
    Poziom 18  

    Dziękuję Panowie. Szczegółowe opisanie tego co chcę osiągnąć było by dość długie.
    W arkuszu pierwszym mam zaimportowane pewne dane z pliku csv.
    Dane te dotyczą różnych osób i tu nie ma reguły. Np dla Jana Kowalskiego mamy 3 wiersze danych, dla Marii Kowalskiej mamy 4 wiersze. Natomiast dla Stanisława Nowaka mamy też jakiś tam wiersz z informacją że "nie występuje w ewidencji".
    Na szczęście kolumny po zaimportowaniu takiego pliku są zawsze w tym samym układzie.
    Makro ma za zadanie skopiować kilka wartości z wiersza 2 arkusza1 do arkusza2, i tam je umieścić w pierwszym wolnym wierszu (poczynając od wiersza 14) potem usunąć wiersz 2 w arkuszu1. Dodatkową trudnością jest to, że gdy dane w arkuszu1 będą dotyczyły już innej osoby, makro ma przerwać kopiowanie, wydrukować arkusz2, wyczyścić komórki w zakresie od A12:J100 i od nowa rozpocząć kopiowanie (danych dotyczących już kolejnej osoby). Wyznacznikiem dla odróżnienia czy dany wiersz dotyczy dotyczy tej samej osoby, czy nie jest kolumna z numerem PESEL.
    Dodatkowo, w przypadku gdy napotka wiersz Stanisława Nowaka (który nie występował w ewidencji) chciałbym żeby makro wydrukowało arkusz 3, w którym widnieje wzmianka, że Stanisław Nowak (makro wkopiuje tylko imię i nazwisko) nie figuruje w ewidencji.

    Obecnie męczę się nad instrukcją, która by mi w arkuszu2 w zakresie komórek A12;J100 zrobiła krawędzie w niepustych komórkach. Proszę o pomoc w tym temacie

    0
  • #15 19 Gru 2010 19:25
    adamas_nt
    Moderator Programowanie

    Zamiast tłumaczyć, co każdy z nas zrozumie po swojemu ;) lepiej załącz plik z przykładowymi danymi, koniecznie w układzie jaki masz po imporcie + to co już "zmęczyłeś". Pomyślimy, zobaczymy co da się zrobić...

    0
  • #16 19 Gru 2010 19:58
    Wieslaw3
    Poziom 18  

    Przykładowy plik z moimi wypocinami:

    Początkowo myślałem że zrobię to na podstawie pesel ale później okazało się, że jeśli dane nie dotyczą osoby fizycznej, numeru pesel nie ma. Dlatego użyłem kolumny idWewnetrzneWniosku

    0
  • #17 19 Gru 2010 20:09
    adamas_nt
    Moderator Programowanie

    Czy identyfikować przez "numerPesel-parametr"? We wszystkich wierszach figuruje tajemniczy Jan Nowak...
    I co z tym "Nie figuruje w ewidencji"? W takim wierszu nie ma interesujących Cię danych (pojazdu).

    0
  • #18 19 Gru 2010 20:13
    Wieslaw3
    Poziom 18  

    Wyznacznikiem jest kolumna idWewnętrzneWniosku.

    Dodano po 2 [minuty]:

    Gdy osoba nie figuruje w ewidencji, wtedy wypełnia arkusz3

    0
  • Pomocny post
    #19 19 Gru 2010 20:29
    adamas_nt
    Moderator Programowanie

    Potwierdź, bo widzę to inaczej:

    EXCEL - problem z przenoszeniem danych między arkuszami

    Wg tego, co piszesz: wiersze od 2 do 4 to trzy różne osoby, 5-14 to jedna osoba?

    0
  • #20 19 Gru 2010 20:34
    marek003
    Poziom 40  

    adamas_nt napisał:
    Jak zwykle między wódkę a zakąskę :)

    Marku, zapomniałeś o For Each, gdzie wygodnie zmieniasz zakres(y).
    Code:
    Dim kom As Range
    
    For Each kom In Range("A1:AS100")
      If kom = "" Then kom = 0
    Next

    I jeszcze jedno uproszczenie (wg mnie wystarczy)
    Code:
    =ADR.POŚR("Arkusz1!J2 ")
    i adekwatnie dla L2 i M2.


    :) Coś mi świtało że musi być prostsze rozwiązanie od standardowych pętli. :)
    :) Przynajmniej jest różnica w wypełnianiu zerami. U mnie zapełnia od lewej do prawej a u ciebie z góry na dół :) Może komuś na tym zależeć :D
    A co do adresu pośredniego to ... tylko spuszczę oczy.

    A na poważnie
    Chylę czoła Panie Adamas. Jak zwykle najtrafniejsze rozwiązania.

    0
  • #21 19 Gru 2010 20:34
    Wieslaw3
    Poziom 18  

    Potwierdzam, zgadza się.
    Może GG? - będzie szybciej

    0
  • #22 19 Gru 2010 20:38
    marek003
    Poziom 40  

    Wieslaw3 napisał:
    ...Może GG? - będzie szybciej ...


    Nie wyprowadzaj dyskusji poza forum bo możesz oberwać od moderatora :)

    0
  • #23 19 Gru 2010 20:41
    Wieslaw3
    Poziom 18  

    Chodzi o domówienie tych drobnych szczegółów.

    0
  • #24 19 Gru 2010 21:07
    adamas_nt
    Moderator Programowanie

    Ja to widzę tak:
    Przyciski w arkuszach 2 i 3 label="Start".
    Klikasz i makro kopiuje dane wg "idWewnetrzneWniosku":
    1. Jeśli osoba ma status "Nie figuruje w ewidencji" do Arkusza 3 i następnie realizuje pkt.3-4
    2. Jeśli <> od "Nie figuruje w ewidencji", do tabelki w Arkusz2.
    3. W zależności od statusu aktywujemy arkusz 2 lub 3. Przycisk zmienia etykietę (label) na "Drukuj", dodajemy krawędzie.
    4. Po kliknięciu przycisku "Drukuj" usuwamy skopiowane wiersz/wiersze z Arkusz1, wysyłamy wynik do drukarki, czyścimy tabelkę i jedziemy dalej od pkt.1 aż do usunięcia/wydrukowania wszystkich.

    Pkt 3 i 4 można zautomatyzować. Nie wiem tylko co na to drukarka i zawsze trzeba liczyć się z błędem. Plusem w/w rozwiązania jest to, że wszystko mamy pod kontrolą.

    :?:

    0
  • #25 19 Gru 2010 21:15
    Wieslaw3
    Poziom 18  

    Wszystko się zgadza tylko punkt 3 miał by się wykonać automatycznie gdyż te czynności wykonywane będą codziennie a danych (wierszy w arkuszu1) jest poro.
    Drukarka domyślna.
    Też właśnie obawiam się błędów. Czy po komendzie drukuj można zrobić powiedźmy 4 sekundową pauzę? Cy to coś pomoże?

    0
  • #26 19 Gru 2010 21:16
    marek003
    Poziom 40  

    Dla mnie ten kod jest troszeczkę pogmatwany i też jestem za tym aby na wstępie stworzyć poszczególne kroki procedury.

    I tu jeszcze się zapytam co ma być jak ta sama osoba występuje parę razy.
    Rozumiem, że wtedy w Arkuszu2 będzie lista danych (parę wierszy) -zaczynając od 13 wiersza.
    Ale jeżeli procedura później usunie tylko jeden wiersz w arkusz1 (tylko drugi wiersz) to dalej pozostaną wpisy dla tej samej osoby która już została wydrukowana. W dalszym kroku procedura znów wypełni dane dla tej osoby oczywiście pomijając jeden (usunięty wcześniej) wers.

    Tak ma być???

    dodane po dłuższej chwili
    --------------------------------------------------

    4. Po kliknięciu przycisku "Drukuj" usuwamy skopiowane wiersz/wiersze z Arkusz1, wysyłamy wynik do drukarki, czyścimy tabelkę i jedziemy dalej od pkt.1 aż do usunięcia/wydrukowania wszystkich.

    Tego nie zauważyłem.

    Rozumiem że nie koniecznie jedna osoba jest zawsze po kolei i makro ma jeszcze poszukać które wiersze usunąć w arkusz1.

    0
  • #27 19 Gru 2010 21:22
    adamas_nt
    Moderator Programowanie

    :arrow: marek003
    Kluczem jest "idWewnetrzneWniosku" (liczba). Wiersze z identycznym id "idą" do tabelki i mają być usunięte. Hmm, z tym usuwaniem też bym się zastanowił. Można przecież tylko wydrukować i w razie kuchy sprawdzić sobie z oryginałem. Wyczyszczenie arkusza to kwestia naciśnięcia jednego klawisza...

    0
  • #28 19 Gru 2010 21:25
    Wieslaw3
    Poziom 18  

    marek003 napisał:
    Dla mnie ten kod jest troszeczkę pogmatwany i też jestem za tym aby na wstępie stworzyć poszczególne kroki procedury.

    I tu jeszcze się zapytam co ma być jak ta sama osoba występuje parę razy.
    Rozumiem, że wtedy w Arkuszu2 będzie lista danych (parę wierszy) -zaczynając od 13 wiersza.
    Ale jeżeli procedura później usunie tylko jeden wiersz w arkusz1 (tylko drugi wiersz) to dalej pozostaną wpisy dla tej samej osoby która już została wydrukowana. W dalszym kroku procedura znów wypełni dane dla tej osoby oczywiście pomijając jeden (usunięty wcześniej) wers.

    Tak ma być???


    Hmm, w tym co ja wypociłem (z Waszą pomocą) jest tak, że procedura liczy ile razy dana osoba występuje (ile jest wierszy dotyczących tej samej osoby) i tyle razy wykonuje pętle: kopiuje dane z 2 wiersza usuwa go i znów kopiuje dane z 2 wiersza który wcześniej był wierszem poniżej.

    Początkowo na pewno będę sprawdzał, czy dane drukują się prawidłowo (mam możliwość wydrukowania tych danych bezpośrednio ze źródła). Makro robię dlatego, że źródło nie ma narzędzia drukującego wszystko jednym kliknięciem. Trzeba każdą osobę drukować osobno odwiedzając po drodze 2 zakładki - żmudna robota

    0
  • #29 19 Gru 2010 21:28
    marek003
    Poziom 40  

    wolniej :) bo się mijamy :)

    Ta sama osoba jest zawsze po kolei? nie jest rozrzucona "po wierszach"?

    0
  • #30 19 Gru 2010 21:31
    Wieslaw3
    Poziom 18  

    Nie jest rozrzucona.

    0