Łączenie dwóch podobnych tabel w PowerQuery (jedna pod drugą)

Pobierz przykładowe pliki potrzebne do wykonania ćwiczenia

W poprzednich ćwiczeniach opisałem Scalanie tabel, czyli pobieraliśmy te dane w taki sposób, że pomiędzy tabelami zachodziły jakieś relacje np. pobieraliśmy te dane które występują w pierwszej tabeli a nie występują w drugiej i odwrotnie. Teraz wykonamy nieco inne ćwiczenie. Otóż mamy dwie tabele zawierające dane pracowników z dwóch oddziałów. Chcemy obie tabele połączyć w jedną (czyli tak jakby wkleić jedną tabelę pod drugą).

Ważna rzecz: we wszystkich tabelach „wspólne” kolumny powinny mieć takie same nazwy. Kolumny nie muszą występować w takiej samej kolejności (zauważ, że w jednej tabeli mam najpierw imię a potem nazwisko a w drugiej odwrotnie). Również tabele nie muszą posiadać takich samych kolumn (w drugiej tabeli np. nie ma kolumny Wiek). Narzędzie Power Query samo z siebie sprytnie dopasuje obie tabele.

Aby wykonać to ćwiczenie, należy wykonać następujące czynności:

Czytaj dalej
Zaszufladkowano do kategorii Power Query | Otagowano | Dodaj komentarz

Rodzaje sprzężeń w Power Query

Pobierz pliki do ćwiczeń

W poprzednim wpisie dotyczącym PowerQuery omówiłem, jak połączyć dane z dwóch tabel. Ponieważ chcieliśmy wygenerować tabelę z danymi, które znajdują się w pierwszej (lewej) tabeli a nie znajdują się w drugiej (prawej) kazałem użyć sprzężenia Anty lewe. W tym wpisie omówię rodzaje sprzężeń dostępne w PowerQuery (jeśli miałeś do czynienia z bazami danych to zapewne nie obce Ci są słowa kluczowe takie jak np. JOIN, LEFT JOIN, RIGHT JOIN itp.)

Rozważmy taką sytuację: mamy dwie tabele tak jak na poniższym rysunku. W bazach danych jako lewą tabelę się określa tę tabelę, która jako pierwsza występuje w zapytaniu a jako prawą określa się tę, która jako druga występuje w zapytaniu. Za chwilę stworzę 6 scaleń (bo w Power Query tyle jest rodzajów sprzężeń). Zawsze jako pierwszą tabelę będę wybierał Wydawnictwo a jako prawą Gatunek.

Czytaj dalej
Zaszufladkowano do kategorii Power Query | Otagowano , , , | Dodaj komentarz

LibreOffice Calc, Open Office Calc – zdejmowanie ochrony arkusza bez znajomości hasła

Niedawno opisywałem, jak zdjąć ochronę arkusza z plików Excelowych. Natomiast w tym artykule zajmiemy się plikami zapisanymi w formacie ODS (OpenDocument Spreadsheet)

Informacje wstępne

Jeśli nie możesz edytować niektórych komórek w arkuszu a być może nawet w niektórych komórkach są ukryte formuły to należy z (LibreOffice) menu Narzędzia odznaczyć opcję Chroń arkusz (jeśli używasz OpenOffice Calc będzie to menu Narzędzia i Chroń dokument->Arkusz)

Niestety czasami może pojawić się pytanie o hasło. W tym artykule zajmiemy się tym, jak owe hasło zdjąć.

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

Jak usunąć z pierwszej tabeli osoby, które znajdują się w drugiej tabeli?

Pobierz przykładowe pliki potrzebne do wykonania tego ćwiczenia.

Swego czasu opiekowałem się pewnym sklepem internetowym. Właściciel sklepu postanowił zrobić promocję świąteczną 2+1 tj. kupując dwie sztuki pewnego produktu trzeci otrzymasz gratis. Wszystkie osoby, które się zapisały na newsletter otrzymały informacje o świątecznej promocji. Liczba zamówień wzrosła co mnie cieszyło, gdyż „byłem na prowizji”. Niestety, ale otrzymaliśmy kilka niezbyt kulturalnych wiadomości od osób, które np. dwa dni wcześniej złożyły zamówienie na dwie sztuki danego produktu i nie załapały się na promocję. Aby udobruchać takich klientów wysłaliśmy im w ramach przeprosin gratisowy produkt.

Rok później, gdy organizowaliśmy podobną promocję postanowiliśmy wysłać mailing do wszystkich czytelników newslettera, ale z bazy mailingowej usunęliśmy adresy e-mail osób, które w ciągu ostatnich 30 dni składały u nas zamówienie (tak naprawdę nie usuwaliśmy tych osób z bazy newslettera na stałe a jedynie utworzyliśmy nowe konto w programie do wysyłki mailingów).

Jeśli Twoje oprogramowanie do wysyłania mailingów nie posiada opcji nie wysyłania wiadomości do osób, które znajdują się w danej tabeli możesz skorzystać z dodatku Power Query, który jest automatycznie zainstalowany jeśli posiadasz Excela w wersji 2013 lub nowszej.

W tym ćwiczeniu utworzymy nową trzecią tabelę która będzie zawierała wszystkie rekordy osób z tabeli A pod warunkiem, że dana osoba nie znajduje się w tabeli B.

Czytaj dalej
Zaszufladkowano do kategorii Power Query | Otagowano , , , | Dodaj komentarz

Rozwiązujesz krzyżówki? Włącz Excela

Pewnie ten tytuł wpisu Ciebie rozśmieszył. Aczkolwiek jest to świetna okazja, aby poćwiczyć stosowanie symboli wieloznacznych na dość praktycznym przykładzie

Pobierz plik do ćwiczeń

Otóż ze strony sjp.pl do arkusza zaimportowałem słownik wyrazów języka polskiego, który w połączeniu z narzędziem filtr i symbolami wieloznacznymi zrobi nam robotę.

Wyobraź sobie taką sytuację. Rozwiązujesz krzyżówkę i pojawia się hasło: niewielka łódź turystyczna lub sportowa. Nie znasz odpowiedzi na to pytanie. Wiesz jedynie, że wyraz jest 5-literowy, pierwsza litera to k, druga kratka jest pusta, trzecia litera to j, czwarta kratka jest pusta i ostatnia litera to też k.

Aby znaleźć wszystkie wyrazy, które pasują do tego wzorca należy wykonać następujące czynności:

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

Excel – zdejmowanie ochrony arkusza i skoroszytu bez znajomości hasła

Czasami otrzymujemy od urzędu arkusz, w którym niektóre komórki są poblokowane i nie możemy ich edytować a czasem nawet nie możemy podglądnąć jaka jest formuła w zablokowanych komórkach.

Włączoną blokadę możemy rozpoznać po tym, że na karcie Recenzja znajduje się przycisk Nie chroń arkusza.

Po kliknięciu w ten przycisk jak mamy odrobinę szczęścia zostanie zdjęta ochrona a przycisk zmieni nazwę na Chroń arkusz. Gorzej, gdy po kliknięciu w przycisk pojawi się komunikat z prośbą o podanie hasła.

Istnieje jednak pewien sposób na zdjęcie ochrony.

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

Szybkie i wygodne zaznaczenie całego wiersza lub całej kolumny

Przeglądając arkusz zawierający kilkaset wierszy i kilkadziesiąt kolumn może wystapić taka sytuacja, że czytasz np. kolejne oceny studenta, przewijasz arkusz nieco w prawo, aby odczytać kolejną porcję ocen a tutaj niespodzianka, nie wiesz, czy czytasz oceny z odpowiedniego wiersza.

Na szczęście w Excelu są dwa bardzo fajne skróty klawiszowe:

Czytaj dalej
Zaszufladkowano do kategorii sztuczki | 2 komentarze

Skoroszyt binarny programu Excel – co to za wynalazek?

Być może zastanawiałes się, czym różni się format XLSX od XLSB. Sama nazwa binarny skoroszyt programu Excel pewnie zbyt wiele Ci nie mówi.

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

VBA – Dlaczego w nazwach funkcji nie powinno się używać polskich znaków?

Od zawsze programując (nie tylko w VBA) starałem się nie używać w nazwach funkcji, procedur czy zmiennych polskich ogonków. Dlaczego? Bo pamiętam czasy przed Windowsem 95, gdzie istniało kilkanaście różnych „standardów” kodowania polskich znaków. Chyba każdy programista w tamtych czasach ucząc się jakiegoś języka programowania (np. QBasic, Turbo Pascal, C/C++) w ramach ćwiczenia praktycznego pisał własny program do konwersji polskich ogonków z jednego standardu do drugiego.

Czytaj dalej
Zaszufladkowano do kategorii VBA porady | Otagowano , | Dodaj komentarz

Dwa słowa o makrach i bezpieczeństwie

W tym nieco obszernym wpisie poruszę dość ważny temat jakim są makra. Niestety, ale zdarza się, że użytkownik komputera nie czyta komunikatów tylko wesoło sobie klika we wszystko co się rusza a potem krzyczy „olaboga! Hakerska aplikacja zaszyfrowała mi wszystkie pliki w komputerze i domaga się okupu!”. W tym wpisie chcę Ciebie uświadomić czym są makra i jak z nimi żyć tak aby nie narobić sobie biedy. Wersja TLTR znajduje się na końcu artykułu.

Kliknij tutaj, aby pobrać pliki do zagadnień omawianych w artykule.

Czym są te niebezpieczne makra?

Wyobraź sobie, że na kuchennym stole leży nóż. Czy uważasz, że jest to potencjalne narzędzie zbrodni? Raczej nie! Jest to świetne narzędzie, dzięki któremu pokroisz sobie chlebek, wędlinkę w celu zrobienia pożywnej kanapki. Jednak od czasu do czasu przeczytasz w gazecie artykuł o tym, że ktoś użył noża niezgodnie z przeznaczeniem atakując nim inną osobę.

Podobnie jest z makrami. W 99% przypadków ułatwiają one codzienną pracę poprzez np. automatyzowanie żmudnych czynności. Pozostaje jednak ten 1% przypadków, gdy ktoś użył mechanizmu makr do wyrządzenia szkody drugiej osobie.

No dobrze, ale skoro makra mogą być niebezpieczne, to dlaczego są wciąż używane? Oto kilka przykładowych zastosowań makr:

  • Automatyzują monotonne czynności, które można nagrać. Przykładowo: gdy każdego dnia wklejasz dane z Internetu (np. kursy akcji z zagranicznej strony) to być może za każdym razem używasz opcji zamiany wszystkich kropek na przecinki w kolumnie E, oraz usuwasz zbędne kolumny. A gdyby te czynności jeden jedyny raz w życiu nagrać i potem tylko klikać w jeden przycisk, który spowoduje wykonanie wszystkich nagranych wcześniej czynności?
  • Excel posiada ponad 400 funkcji (m.in. SUMA, ŚREDNIA, MAX, LICZ.JEŻELI itp.) Jeśli wśród tych 400 funkcji nie znajdziesz tej, którą potrzebujesz to za pomocą języka VBA można napisać własne funkcje. Przykładowo wystawiasz faktury przy użyciu Excelowego szablonu. Niestety, ale w Excelu nie ma funkcji, która zamieni liczbę 1234,56 na słowną kwotę (tj. jeden tysiąc dwieście trzydzieści cztery złote 56/100). I tutaj znów z pomocą przychodzą makra!
  • Pozwalają na napisanie dodatkowych narzędzi (wtyczek) zwiększających możliwości programów z pakietu MS Office. Takim dodatkiem jest np. narzędzie Solver czy też „moja wtyczka” usuwająca wiszące spójniki
  • Pozwalają na napisanie prostych aplikacji typu: wypełniasz pola w oknie dialogowym a następnie aplikacja wpisuje te dane do odpowiednich komórek w Excelu (uwzględniając różne często rozbudowane warunki), następnie ukrywa niepotrzebne dla danego przypadku wiersze i generuje plik PDF z wypełnionym raportem, który dodatkowo automatycznie jest wysyłany na odpowiedni adres e-mail.
  • Jako ciekawostkę dodam, że w języku VBA napisać jakąś prostą grę np. PacMan
Czytaj dalej
Zaszufladkowano do kategorii Bezpieczeństwo | Otagowano , , , , , , | Dodaj komentarz