Jak za pomocą VBA zmienić wartość parametru dla zapytań w Power Query

Korzystając z Power Query dość często korzystam z Parametrów. Jest to bardzo wygodna rzecz gdyż np. źródłem danych nie jest na sztywno wpisana nazwa pliku tylko parametr, który przechowuje ową nazwę.

Oczywiście kilka różnych zapytań może korzystać z tego samego parametru co jest bardzo wygodne, bo gdy zmieni się nazwa pliku wejściowego to edytuję tylko jeden parametr a nie wszystkie zapytania po kolei.

Niedawno natrafiłem na pewien problem: otóż napisałem proste makro, które prosi użytkownika o wskazanie pliku. I właśnie nazwę tego pliku wraz z ścieżką chciałem zapisać jako wartość parametru.

Czytaj dalej
Zaszufladkowano do kategorii Power Query, VBA porady | Dodaj komentarz

Wtyczka wyświetlająca formułę przetłumaczoną na język angielski

Używasz Excela w wersji polskojęzycznej? Czy wiesz, że korzystając z wersji anglojęzycznej używa się angielskich nazw funkcji (np. IF zamiast JEŻELI, SUMIF zamiast LICZ.JEŻELI itp.)?

Wskazówka: Opisana poniżej wtyczka działa również na MacOs!

Czasami człowiek jest stawiany przed koniecznością nauczenia się angielskich nazw funkcji. Np. w nowej pracy będzie zainstalowany anglojęzyczny Excel. A może chcesz przystąpić do jakiegoś egzaminu gdzie będzie anglojęzyczny Excel? Mam kilka rozwiązań tego problemu:

Czytaj dalej
Zaszufladkowano do kategorii Dodatki (Add-in) | Otagowano | Dodaj komentarz

Dlaczego nie powinno się ukrywać kolumn i arkuszy zawierających wrażliwe dane?

Dość często początkujące osoby na różnych grupach zadają pytanie czy można jakoś zmusić Excela do tego, aby odkrycie ukrytej kolumny wymagało podania hasła. Inne często pojawiające się pytanie dotyczy tego czy można jakoś zmusić Excela aby użytkownik musiał podać hasło aby odczytać zawartość jakiegoś arkusza. Niestety Excel nie posiada takich możliwości.

Napisanie makra, które odkrywa kolumny/arkusze po podaniu hasła też nie jest dobrym pomysłem, gdyż zaawansowany użytkownik znajdzie sposób jak samodzielnie odkryć to co przed nim ukryłeś.

Zdaję sobie z tego sprawę, że mając jakiś skoroszyt z danymi pracowników chcesz wysłać innej osobie okrojoną wersję (np. same imiona i nazwiska pracowników wraz z numerami telefonów, ale już bez danych adresowych i numeru PESEL). Jedyne skuteczne rozwiązanie jakie mi przychodzi do głowy to skopiowanie wybranych danych do nowego skoroszytu i wysłanie takiego okrojonego skoroszytu.

Tak na marginesie: power-userzy pewnie powiedzą, że można tak zaprojektować model danych, aby wrażliwe można było pobierać z zewnętrznych baz danych do których dostęp należy uwierzytelnić loginem/hasłem, i odpowiednio ustawić aby pobierane dane nie były przechowywane w skoroszycie, ale to już jest wyższa szkoła jazdy.

W tym artykule opiszę jak dobrać się do danych, które ktoś przed nami ukrył na kilka różnych sposobów.

Czytaj dalej
Zaszufladkowano do kategorii Bezpieczeństwo, Kącik hakera | Otagowano , | Dodaj komentarz

Funkcja PORÓWNAJ

Gdy pierwszy raz spotkałem się z funkcją PORÓWNAJ zacząłem się zastanawiać jaki jest sens jej użycia. Otóż ta funkcja zwraca wartość PRAWDA lub FAŁSZ w zależności od tego czy oba porównywane teksty są takie same.

Oto najprostszy przykład użycia tej funkcji:

=PORÓWNAJ(A2;B2)

No dobrze, ale jeśli chciałbym osiągnąć ten sam efekt, to mógłbym równie dobrze użyć operatora =

=A2=B2

Efekt będzie taki sam. Ale czy aby na pewno??

Czytaj dalej
Zaszufladkowano do kategorii funkcje | Otagowano , | Dodaj komentarz

SUMY.CZĘŚCIOWE – czyli jak zsumować dane w widocznych komórkach

Załóżmy, że mamy jakąś listę pracowników jak na poniższym rysunku.

Aby zsumować wszystkie pensje mogę użyć następującej funkcji:

=SUMA(E11:E30)

A teraz niespodzianka: co by się stało, gdybym dla powyższej tabeli założył filtr i postanowił wyświetlić tylko te wiersze, w których w kolumnie Płeć jest wpisana literka K? Otóż wynik by się nie zmienił, funkcja SUMA nadal by zsumowała wszystkie wartości od E11 do E30 pomimo tego, że 11 z 20 wierszy byłoby ukrytych.

Czytaj dalej
Zaszufladkowano do kategorii funkcje | Otagowano , , , , | Dodaj komentarz

Jak pobrać nazwy wszystkich plików z określonego folderu do kolejnych komórek?

Czasami potrzebujemy pobrać nazwy wszystkich plików do kolejnych wierszy w Excelu. Przykładowo każdy plik może zawierać oceny konkretnego pracownika, a pliki powstają przy eksportowaniu danych z jakiegoś zewnętrznego programu. No i chcemy zrobić sobie tabelkę z podsumowaniem. W niniejszym wpisie opiszę jak to zadanie można wykonać na 3 różne sposoby za pomocą różnych narzędzi. Pierwsza rzecz jaka mi przyszła do głowy to wykorzystanie Power Query.

Czytaj dalej
Zaszufladkowano do kategorii Power Query | Dodaj komentarz

Word – jak zamienić wszystkim czerwonym wyrazom kolor na inny?

Czasami chcemy w Wordzie zmienić hurtowo pewną cechę formatowania. Np. chcemy aby wszystkie czerwone wyrazy były teraz koloru niebieskiego. Przykładowo załóżmy, że jesteś nauczycielem języka angielskiego i w każdej „czytance” wyróżniasz czerwonym kolorem wszystkie nowe słówka. Nagle postanawiasz zmienić kolor nowych słówek z czerwonego na niebieski. Jeśli korzystasz ze stylów to sprawa jest banalna: wystarczy zmienić definicję stylu i po sprawie. Gorzej, gdy wszystkie nowe słówka były „kolorowane ręcznie”. Ale tutaj z pomocą nam przychodzi proste narzędzie: Znajdowanie i zamienianie.

W pierwszej kolejności wybierz polecenie Zamień znajdujące się w grupie poleceń Edytowanie na karcie Narzędzia główne (lub naciśnij kombinację klawiszy Ctrl+H)

Czytaj dalej
Zaszufladkowano do kategorii word - samouczki | Otagowano , | Dodaj komentarz

Jak ukryć arkusz, aby się nie dało go odkryć?

Aby ukryć arkusz należy kliknąć prawym przyciskiem myszy na karcie z nazwą arkusza i z menu kontekstowego wybrać polecenie Ukryj.

Taki arkusz można bardzo łatwo odkryć – wystarczy na karcie z nazwą dowolnego arkusza kliknąć prawym przyciskiem myszy i wybrać polecenie Odkryj.

Istnieje jedna sposób na takie ukrycie arkusza, aby nie dało się go odkryć poprzez wybranie polecenia Odkryj. W tym celu należy wykonać następujące czynności:

Czytaj dalej
Zaszufladkowano do kategorii sztuczki | Otagowano | Dodaj komentarz

Jak szybko sprawdzić, czy są jakieś ukryte arkusze

Otrzymując jakiś ciekawy skoroszyt (szczególnie ten zawierający makra) chcemy sprawdzić, czy nie zawiera on jakiś ukrytych arkuszy. Wszak nie wiem czy wiesz, ale bardzo często właśnie w takich ukrytych arkuszach są zapisane najciekawsze rzeczy.

Aby odkryć ukryte arkusze należy w dolnej części Excela na dowolnej karcie z nazwą arkusza kliknąć prawym przyciskiem myszy i wybrać polecenie Odkryj (jeśli nie ma ukrytych arkuszy to owe polecenie jest nieaktywne).

Pojawi się okno dialogowe Odkrywanie w którym należy zaznaczyć arkusz który chcesz odkryć i następnie należy kliknąć w przycisk OK.

Niestety, ale ten sposób odkrywania nie działa, gdy dany arkusz został ukryty poprzez zmianę mu właściwości Visible na VeryHidden (taki arkusz zarówno jest ukryty, jak również nie jest widoczny na liście arkuszy w okienku Odkrywanie).

Jak najszybciej można sprawdzić listę wszystkich arkuszy w skoroszycie (również tych normalnie ukrytych jak i tych „VeryHidden”?) Za chwilę opiszę moje niedawne odkrycie.

Czytaj dalej
Zaszufladkowano do kategorii sztuczki | Otagowano , | Dodaj komentarz

Jak utworzyć dysk Z?

Jakiś czas temu otrzymałem od klienta ciekawe zlecenie. Otóż musiałem zmodernizować pewną aplikację napisaną w języku VBA. W głównym arkuszu była pewna tabelka z wieloma wierszami i kilkudziesięcioma kolumnami zawierająca informacje o pewnych wyrobach. Dla zaznaczonego wiersza po kliknięciu w przycisk należało wygenerować plik PDF z karta informacyjną produktu (odpowiednie komórki z wybranego w tabeli wiersza były kopiowane do osobnego ładnie sformatowanego arkusza i ten arkusz był eksportowany do pliku PDF).

I tutaj zaczyna się zabawa. Otóż każdy z produktów z tabelki ma swój folder na dysku gdzie są przechowywane różnego rodzaju wygenerowane pliki PDF. I ten folder był na dysku Z (pewnie w firmie był to zmapowany folder sieciowy). Zacząłem się zastanawiać jak w najszybszy sposób utworzyć u siebie dysk Z (mogłem w sumie dokonać stosownej zmiany w kodzie aplikacji np. poprzez ustawienie jakiejś stałem globalnej, ale musiałbym pamiętać o zmianie stałej przy oddaniu klientowi gotowej aplikacji). Pomysłów miałem kilka:

Czytaj dalej
Zaszufladkowano do kategorii Pozostałe | Dodaj komentarz