Elektroda.pl
Elektroda.pl
X
Elektroda.pl
Proszę, dodaj wyjątek dla 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

17 Gru 2010 19:56 18940 52
  • 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ć
  • Pomocny post
    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.
  • 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.
  • 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)
  • 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.
  • 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
  • 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:
  • 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
  • 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.
  • Poziom 28  
    Przeszkód nie ma. Ale mając pewność, że ona tam jest po co jej szukać?
  • 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"
  • 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)
  • 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.
  • 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
  • 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ć...
  • 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
  • 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).
  • Poziom 18  
    Wyznacznikiem jest kolumna idWewnętrzneWniosku.

    Dodano po 2 [minuty]:

    Gdy osoba nie figuruje w ewidencji, wtedy wypełnia arkusz3
  • Pomocny post
    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?
  • 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.
  • Poziom 18  
    Potwierdzam, zgadza się.
    Może GG? - będzie szybciej
  • Poziom 40  
    Wieslaw3 napisał:
    ...Może GG? - będzie szybciej ...


    Nie wyprowadzaj dyskusji poza forum bo możesz oberwać od moderatora :)
  • Poziom 18  
    Chodzi o domówienie tych drobnych szczegółów.
  • 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ą.

    :?:
  • 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?
  • 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.
  • 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...
  • 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
  • Poziom 40  
    wolniej :) bo się mijamy :)

    Ta sama osoba jest zawsze po kolei? nie jest rozrzucona "po wierszach"?
  • Poziom 18  
    Nie jest rozrzucona.