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 - kopiowanie wzoru z wprowadzeniem zmian - tylko makro?

mario22 10 Sty 2012 10:46 2547 8
  • #1 10 Sty 2012 10:46
    mario22
    Poziom 11  

    Witam,

    Excel 2003.

    Mam taki oto wzór:
    =JEŻELI(CZY.BŁĄD(JEŻELI([1.xls]Arkusz1!$L$12="B50, 3231";"3231";JEŻELI([1.xls]Arkusz1!$L$12="B5, 231";"231";"Błąd, sprawdź wybór """)));"błąd";JEŻELI([1.xls]Arkusz1!$L$12="B50, 3231";"3231";JEŻELI([1.xls]Arkusz1!$L$12="B5, 231";"231";"Błąd, sprawdź wybór""")))

    Jak widać są odwołania do plików zewnętrznych. Problem tkwi w tym że wzór trzeba skopiować do kolejnych komórek niżej ze zmianą nazwy pliku na kolejny nr czyli 2.xls, 3.xls itd. Niestety excel nie ma możliwości zmiany nazwy pliku jak ma to miejsce w przypadku odwołań względnych przy kopiowaniu komórek. Nie mam już pomysłu jak to rozwiązać.
    W kolejnych kolumnach są inne wzory również z odwołaniami do plików zewnętrzych (każdy wiersz odwołuje się do innego pliku).
    Niestety z makrami nie mam doświadczenia.
    Czy można to rozwiązać bez makra? A może jakieś proste makro?

    Pozdrawiam

    0 8
  • #2 10 Sty 2012 11:06
    marcinj12
    Poziom 40  

    Poniższe makro wstawi tą formułę w 20 komórek poniżej zaznaczonej komórki:

    Kod: vb
    Zaloguj się, aby zobaczyć kod


    Formuła została uzyskana przez nagranie makra, gdzie wstawiłem Twoją funkcję do Excela, a potem zapisany na sztywno [1.xls] zastąpiłem przez sklejenie tekstu ze zmieniającą się zmienną i: [" & i & ".xls]. To tak na przyszłość, jakbyś chciał to kiedyś zmodyfikować.

    0
  • #3 10 Sty 2012 12:38
    adamas_nt
    Moderator Programowanie

    Można i bez makra. Jeśli każde odwołanie

    Code:
    [1.xls]Arkusz1!$L$12
    zastąpisz
    Code:
    ADR.POŚR("["&WIERSZ()&".xls]Arkusz1!$L$12")
    Jeśli formuła znajduje się niżej, to należy odjąć liczę wierszy powyżej. Np dla formuły w A3 (3-ci wiersz) 1=3-2
    Code:
    ADR.POŚR("["&WIERSZ()-2&".xls]Arkusz1!$L$12")


    Kopiując w dół, wynik funkcji WIERSZ() będzie rósł, zmieniając argument a tym samym nazwę pliku...

    0
  • #4 10 Sty 2012 13:17
    mario22
    Poziom 11  

    Skorzystałem z pierwszej opcji (makro) i działa, zmodyfikowałem już sobie makro dla wypełnienia 10 kolumn jednocześnie (w każdej inny wzór)
    Drugą opcję sprawdzę w wolnej chwili.
    Mam jednak jeszcze jeden mały problem z arkuszem.
    Ponieważ nie wszystkie pliki do których jest odwołanie jeszcze istnieją, (będą powstawać i mają się same uzupełniać w zestawieniu) każda komórka dla której brak takiego pliku wyświetla okno z aby podać ścieżkę, trzeba wcisnąc anuluj aby przeszedł dalej. Przy kilku kolumnach dla np. 100 wierszy klikanie enterem stanie się uporczywe. Czy można to w jakiś sposób obejść?

    Dzięki

    0
  • #5 10 Sty 2012 13:59
    marcinj12
    Poziom 40  

    Można, w ten sposób:

    Kod: vb
    Zaloguj się, aby zobaczyć kod

    0
  • #6 10 Sty 2012 15:17
    mario22
    Poziom 11  

    Dzięki, o to chodziło... niestety napotkałem kolejny duży problem.

    Nie wiem czy to wina Excela 2003,ale okazuje się że w tak przygotowanym arkuszu nie są aktualizowane łącza.
    W zasadzie miało to działać tak:
    Wiersze z danymi są zaciągane z zewnętrznych plików, numerowanych od 1 wzwyż, stąd konieczna była zmiana nazw. W przypadku braku pliku excel wyświetla błąd i jest to ukrywane (nie będzie widoczne dla wydruku). Gdy zostanie utwożony kolejny plik, dane z niego powinny się zaczytać automatycznie przy uruchomieniu pliku głównego. I tutaj jest problem bo aktualizacja nic nie daje. Excel nie widzi pliku, pomimo że plik istnieje.

    Można to obejść?

    0
  • #7 10 Sty 2012 15:44
    marcinj12
    Poziom 40  

    Może to dlatego, że w takiej formie jak to podałeś teraz, funkcja aktualizuje dane tylko z otwartego arkusza o takiej nazwie. Żeby aktualizować dane z arkusza umieszczonego w jakiejś konkretnej lokalizacji, powinieneś podać pełną ścieżkę do niego. Spróbuj przerobić kod w podobny sposób jak przykład poniżej (zwróć uwagę na zmianę nazwy arkusza na pełną ścieżkę oraz apostrofy wokół niej). Zmieniłem też aktywną komórkę, zamiast tego podając wprost numer wiersza i kolumny od której ma zacząć wstawianie:

    Kod: vb
    Zaloguj się, aby zobaczyć kod


    Po otwarciu pliku powinno wyskoczyć okienko z pytaniem o aktualizację łączy, a dane zaktualizują się po wybraniu odpowiedniego przycisku. Chyba że masz to w zabezpieczeniach Excela wyłączone, wtedy ustaw poziom zabezpieczeń na "Niski" lub "Średni".

    0
  • #8 10 Sty 2012 15:55
    mario22
    Poziom 11  

    Pliki główny oraz pozostałe są w tym samym folderze, nie mogę podać ścieżki bo z innego miejsca (inny komputer) się nie odpali, a jest to konieczne.
    Okno z monitem do aktualizacji łącz wyskakuje, ale aktualizacja nie przynosi spodziewanego skutku.

    0
  • #9 10 Sty 2012 16:29
    marcinj12
    Poziom 40  

    Cóż, tak jak pisałem - zapis adresu jako: [1.xls]Arkusz1!$L$12 oznacza komórkę L12 z Arkusza1 otwartego pliku o nazwie 1.xls

    Spróbuj najpierw otworzyć plik źródłowy (jeden lub kilka), potem ten plik z makrem to się okaże, że zaktualizował łącza tylko w otwartych plikach.

    Chcąc się odnieść do nieotwartego pliku, moim zdaniem musisz zawsze podać pełną ścieżkę. Nie mam pomysłu jak to zrobić w formule (o ile w ogóle jest to możliwe), natomiast w VBA możesz użyć ThisWorkbook.Path do pobrania bieżącego katalogu z którego jest uruchomiony arkusz.

    0