Moje doświadczenia z Excelem, Accesem i programowaniem w VBA

Wpisy z tagiem: łącza

poniedziałek, 01 maja 2017

W niektórych plikach Excela znajdują się łącza do innych plików. Najczęściej są to działania świadome i celowe, pozwalające np. na agregację danych z różnych arkuszy/skoroszytów. Czasem jednak zdarza się, że łączy teoretycznie nie powinno być, a jednak są widoczne. W dodatku ich odnalezienie jest trudne, nie można ich także przerwać.

łącza skoroszytu 

Wprawdzie można wyłączyć aktualizację łączy, ale to tylko maskuje, a nie rozwiązuje problem. Brak komunikatów nie załatwia sprawy.
W tego typu przypadkach najczęściej okazuje się, że łącza do innego pliku wcale nie są wstawione bezpośrednio w komórkach, a nieco bardziej "zagnieżdżone". Na początek warto więc sprawdzić:

  • ustawienia poprawności danych
    kto wie, czy na którejś z list nie ma odwołania do innego pliku?
  • formatowanie warunkowe
    tu też mogą być łącza

Tego typu sytuacje mogą się zdarzyć, gdy na przykład kopiujemy jakiś zakres komórek z innego skoroszytu, zapominając o tym, że jest tam zdefiniowana lista poprawności danych (przećwiczyłam to na sobie).
Jeśli plik jest mało rozbudowany - można to znaleźć ręcznie, choć czasem trzeba się naszukać. Gorzej, gdy plik zawiera wiele arkuszy, w każdym jest sporo danych i wykresów. Wówczas znalezienie takich odwołań może być naprawdę bardzo pracochłonne. Na takie przypadki jest specjalny dodatek Billa Manville'a:

FindLink

Trafiłam na niego dzięki pomocy przyjaciół z ExcelForum.pl  (to naprawdę najlepsze forum Excela). Zainstalowałam, zastosowałam i problem z dziwnymi łączami zniknął. Teraz dodatek nadal jest zainstalowany w moim Excelu, widać go na karcie Dodatki. I przydaje się.

A tu na wszelki wypadek link do filmiku pokazującego jak zainstalować dodatek w Excelu:

Instalacja dodatku Excela


Kurs Excel - zaawansowane techniki

wtorek, 10 września 2013

W komentarzach do jednej ze starszych notek:

Odczyt i porównanie wartości komórek z wielu plików

pojawił się problem z odświeżaniem łączy w pliku. Wprawdzie na podstawie przekazanych mi informacji nie wiem, co może być tego przyczyną, ale odpowiadając na prośbę czytelnika - zamieszczam makro otwierające i od razu zamykające wszystkie pliki z danego katalogu.
Na pewno odświeży to wartości we wszystkich powiązanych komórkach. Adres katalogu jest pobierany z komórki A1 aktywnego arkusza. Otwieranie i zamykanie kolejnych plików odbywa się w tle - na czas wykonywania makra wyłączone jest odświeżanie ekranu.

Public Sub OtworzPliki()

Dim Katalog As String
Dim NazwaPliku As String
Application.ScreenUpdating = False
Katalog = Range("A1").Value
If Right(Katalog, 1) <> "\" Then Katalog = Katalog & "\"
NazwaPliku = Dir(Katalog & "*.xls*")
Do While NazwaPliku <> ""
Workbooks.Open Filename:=Katalog & NazwaPliku, ReadOnly:=True
ActiveWindow.Close savechanges:=False
NazwaPliku = Dir
Loop
Application.ScreenUpdating = True
End Sub

Mam nadzieję, ze teraz będzie działać.

 


 

 

 

 

 

 

 

Kurs Excel programowanie w VBA



poniedziałek, 20 grudnia 2010

Worksheet_FoloowHyperlink to kolejne makro przypisane do zdarzeń arkusza (inne najłatwiej wyszukać korzystając z tagu zdarzenia arkusza ).
Makro to działa po kliknięciu łącza w arkuszu. Wybrane łącze odczytywane przez parametr wejściowy Target (typu Hyperlink).
W praktyce może wyglądać to tak:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
'ciąg instrukcji
End Sub

Można w ten sposób np.śledzić kliknięte łącza.

środa, 19 maja 2010

Kilka dni temu zostałam poproszona o szybkie porównanie zawartości dwóch prostych plików. Zrobiłam to, ale żeby tym, których to dotyczyło nie umknęło, a pozostałych być może zainspirowało - pokażę rozwiązanie, które przyjęłam.

Dla uproszczenia przykładu - załóżmy, że mamy jeden plik Excela, w którym jest arkusz Spis1 zawierający spis telefonów i ich właścicieli (spis zupełnie wymyślony, wszystkie dane wymyślone i przypadkowe). Oprócz tego - jest też arkusz Spis2 - prawie identyczny.

 porównanie plików

Jak wszędzie jednak to "prawie" może stanowić wielką różnicę. Jak odnaleźć wiersze, w których występują różne dane? Jednym z prostszych sposobów może być wstawienie w jedną z kolumn w obydwu arkusza funkcji Złącz.Teksty.
Dla komórki C2 formuła będzie wyglądać tak:
=ZŁĄCZ.TEKSTY(A2;B2)
W kolejnych wierszach - analogicznie. Następnie w kolejnej w kolumnie wstawiamy formułę porównujące odpowiednie ciągi.
Komórka D2 w arkuszu SPIS2:
=JEŻELI(C2=spis2!C2;"";"różnica")
Formuła ta oznacza, że jeżeli komórka C2 jest równa komórce C2 w arkuszu SPIS2 - to zostaw puste miejsce (wyznaczane przez podwójny cudzysłów), jeżeli nie są równe - to wpisz "róznica".
Dodatkowo zastosowałam tu także formatowanie warunkowe, uzależnione od wartości wiersza w kolumnie D.
Efekt końcowy wygląda tak:

porównanie plików

W tym przypadku - ponieważ porównujemy tylko dwie kolumny (A i B) - można zastosować także prostszą funkcję, porównującą poszczególne komórki:
=JEŻELI(ORAZ(A2=spis2!A2;B2=spis2!B2);"";"różnica")
Jeżeli jednak w każdym z wierszy jest więcej kolumn - formuła może okazać się zbyt długa. Wtedy zdecydowanie lepiej wykorzystąc pierwszy sposób. Warto też pamiętać o tym, że jeżeli takie porównanie ma zostać na dłużej - to może lepiej ukryć te kolumny? Taki złączony tekst wygląda mało estetycznie.

A dla przypomnienia - linki do notek omawiających funkcje wykorzystane w przykładzie:
Jeżeli
Oraz
Złącz.Teksty

Formatowanie warunkowe

poniedziałek, 26 kwietnia 2010

W swojej codziennej pracy często muszę szybko zrobić statystyki z pracujących w trybie wielodostępu plików na serwerze. Kilku różnych użytkowników edytujących poszczególne komórki, dodających kolejne wiersze do tabeli.
Najprostszym rozwiązaniem byłaby tabela przestawna, ale w plikach udotępnionych - nie działa, więc nie można jej tu wykorzystać. Jak sobie poradzić?
Ja na swoim komputerze zakładam nowy plik, kopiuję nagłówek, a w kolejnych wierszach - wstawiam łącza do pliku na serwerze.
Posłużę się tu już wcześniejszym przykładem katalogu książek. Jego pełna nazwa (wraz z ścieżką dostępu na moim komputerze) to:
E:\serwer\Katalog.xls

katalog

W moim pliku statystyk
(C:\Documents and Settings\Desktop\Statystyki_katalogu.xls)
pierwszy wiersz to również nagłówek, a kolejne komórki tworzę wpisując łącza do pliku Katalog.xls.
Na wszelki wypadek wyjaśniam, że najprościej to zrobić w ten sposób, że mając jednocześnie otwarte obydwa te pliki, w komórce A2 w pliku Statystyki_katalogu wpisuję znak równości, przechodzę do pliku Katalog, również do komórki A2 i wciskam Enter.
W komórce pojawi sie wartość 1, a w polu formuły pojawia się:
=[Katalog.xls]spis!$A$2
Wpisany adres jest adresem bezwzględnym. Skopiowanie formuły do kolejnej komórki - nie zmieni jej. Konieczne jest więc wycięcie znaków $
Po korekcie:
=[Katalog.xls]spis!A2
Skopiowana w ten sposób komórka A2 na całą tabelę - przeniesie odpowiednie wartości odowiednich komórek w odpowiednie miejsca.
Jest jeszcze jeden problem. Jeżeli jakaś komórka w pliku źródłowym będzie pusta - to w pliku statystyk pojawi się zero. Spowoduje to zaciemnienie obrazu tabeli, a w wielu przypadkach - zafałszowanie statystyk. Zero jest też wartością, niekiedy bardzo istotną.
Ja radzę sobie za pomocą funkcji Jeżeli...to  
=JEŻELI([Katalog.xls]spis!A2="";"";[Katalog.xls]spis!A2)
Jeżeli wartość komórki A2 (w pliku Katalog) jest pusta (oznaczamy to podwójnym cudzysłowem) - to wstaw "nic" (podwójny cudzysłów), w przeciwnym przypadku - wstaw wartość komórki A2.
I już.
Przy otwarciu pliku Statystyk będzie pojawiał się komunikat informujący o łączach i pytanie, czy zaktualizować ich wartość.

aktualizacja łączy

Plik statystyk jest gotowy do analizy.

czwartek, 18 lutego 2010

Funkcja logiczna "Jeżeli...to..." jest jedną z moich ulubionych i czesto wykorzystywanych. Zsada jej działania jest prosta. Jeżeli jakiś warunek jest spełniony - to komórką z wstawiona formułą ma jedną wartość , jeśli nie jest - inną.

Jeżeli..to...

Wyobraźmy sobie prosty przykład, że komórka A1 ma sprawdzać znak liczby w komórce B1. W  Test_logiczny wpisujemy (można też zaznaczyć komórkę) B1<0. W polu Wartość_jeżeli_prawda "-" (znak minusa w cudzysłowie, gdyż ten minus stanowi w tym przypadku tekstowy znak graficzny. Analogicznie w polu Wartość_jeżeli_fałsz "+".  Potem wystarczy tylko kliknąć przycisk OK.

Zapis  formuły wyglądać będzie tak:
=JEŻELI(B1<0;"-";"+")
Na czerwono zaznaczyłam średniki - oznaczają one koniec poszczególnych pól formuły. Widać ten zapis także w arkuszu, w polu formuły.

Funkcja Jeżeli to

A od strony praktycznej - lubię tę funkcję i często z niej korzystam do tworzenia raportów. W jaki sposób? W pracy sporo pracujemy na udostępnionych plikach Excela na serwerze. Są to arkusze z dużą ilością modyfikowanych i dodawanych danych, nawet ilośc wierszy ulega zmianie. Dołożenie jeszcze dodatkowych raportów - stanowiłoby niepotrzebne obciążenie. Tabeli przestawnej w ogóle nie da się stworzyć i uaktualnić w arkuszu udostępnionym. W takich przypadkach na swoim komputerze tworzę osobny skoroszyt raportów i wstawiam łącza do poszczególnych komórek pliku źródłowego. Problem jednak w tym, że proste łącze:
=[Plik_zrodlowy.xls]Arkusz!$A$1
i analogicznie - w pozostałych komórkach - wszędzie tam, gdzie w pliku źródłowym jest pusta komórka - łącze wskaże "0". Zero jednak też jest wartością i może to całkowicie zafałszować raport. I w tym momencie - korzystam właśnie z funkcji Jeżeli... to...
W pliku raportu, w komórce A1 wstawiam funkcję:
=Jeżeli(Plik_zrodlowy.xls]Arkusz!A1="";"",[Plik_zrodlowy.xls]Arkusz!A1)
Jak to czytać? Jeżeli komórka A1 w pliku żródłowym jest pusta (dwa znaki cudzysłowu obok siebie - "" - to oznaczenie pustego łańcucha tekstowego) - to wynik formuły jest też pusty. W przeciwnym przypadku (wartość jeśli fałsz) - wynkiem komóki jest wartość komórki pliku źródłowego.
Metoda wielokrotnie sprawdzona i wykorzystywana. Nawet nie muszę pilnować ilości wierszy - i tak pokażą się tylko te potrzebne.

| < Listopad 2017 > |
Pn Wt Śr Cz Pt So N
    1 2 3 4 5
6 7 8 9 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28 29 30      


Książki warte polecenia
zobacz szczegóły...


A tu oferta na dziś:





ministat liczniki.org



Napisz do mnie!