podział danych na arkusze i skoroszyty

Podział danych na arkusze i skoroszyty

Jakub Krupa
Power Query jest narzędziem, które świetnie radzi sobie z łączeniem danych pochodzących z wielu źródeł. Często z jego wykorzystaniem łączymy podzielone dane znajdujące się w kilku arkuszach lub kilku skoroszytach. Co w sytuacji gdy chcemy przeprowadzić proces odwrotny do wspomnianego? Co gdy chcielibyśmy zrobić podział danych na arkusze i na skoroszyty? Tutaj Power Query nie będzie w stanie nam pomóc. Musimy posiłkować się makrami w VBA. Kliknij aby przeczytać inne wpisy o Power Query. Kliknij aby przeczytać inne wpisy o makrach VBA. Podział danych na arkusze Excel Nasza pojedyncza tabela z danymi, użyta na potrzeby tego wpisu, zawiera dane zgromadzone w 7miu kolumnach. Dla nas kluczowa będzie kolumna pierwsza zawierająca kolejne numery jednostek. Na jej podstawie będziemy chcieli przenieść makrem...
Więcej

Niestandardowa funkcja użytkownika Excel w VBA

Jakub Krupa
Excel posiada ponad 400 wbudowanych funkcji, z których możemy korzystać do woli i w zależności od naszych potrzeb. Mimo, że ta ilość wydaje się być spora to rośnie z każdą kolejną wersją programu Excel. Jednak tak naprawdę, najczęściej korzystamy zaledwie z kilkudziesięciu najbardziej popularnych formuł, które stosujemy w różnych kombinacjach. Dodatkowo możemy tworzyć formuły zagnieżdżone, w których łączymy ze sobą kilka funkcji. Możemy również stosować formuły tablicowe. Kolejną opcją jest niestandardowa funkcja użytkownika Excel w VBA, którą pisze w tym wpisie. Gdyby jednak okazało się, że na nasze potrzeby chcemy stworzyć jakąś nietypową, niestandardową funkcję to możemy z powodzeniem napisać ją w języku VBA. W wielu przypadkach pozawala to przyspieszyć codzienna pracę i zoptymalizować przeliczenia wewnątrz naszych arkuszy. Napisanie kilku...
Więcej
NIP z KRS w Excelu przez Power Query

NIP z KRS w Excelu przez Power Query

Jakub Krupa
Jednym z ciekawszych zastosowań, jakie znalazłem dla Power Query, jest pobieranie danych z Internetu za pomocą połączeń z API. Dla przykładu zaprezentuję metodę pozwalającą na szybkie pobranie danych z Krajowego Rejestru Sądowego. Informację będą dotyczyć organizacji zarejestrowanych w KRS i zaczytywane będą wyłącznie na podstawie ich NIPu wpisanego do arkusza Excel. W sieci można znaleźć wiele podobnych rozwiązań - w większości opartych na VBA, niestety głównie płatnych. Prezentowana przeze mnie metoda pobierania danych NIP z KRS w Excelu przez Power Query będzie przystępna dla każdego i całkowicie darmowa :) Tworzymy arkusz Excel Na początku zakładamy skoroszyt Excel, do którego będziemy pobierać dane z KRS. API, które nas interesuje zostało udostępnione na stronie: https://mojepanstwo.pl/api/krs Do arkusza będziemy zaczytywać dane wg NIP (strona...
Więcej
Zamiana wartości tekstowej na liczbę w Excel

Zamiana wartości tekstowej na liczbę w Excel

Jakub Krupa
Funkcje tekstowe w Excelu zawsze zwracają ciąg znaków przechowywany jako tekst. Nawet gdy ciąg ten będzie wyglądał jak liczba, funkcja zwróci go w postaci tekstu. Taki efekt jest często niepożądany, gdyż użytkownik wyciągający liczby ma zamiar ich dalszej obróbki pod postacią liczbową np. analizowania z wykorzystaniem tabel przestawnych. Warto wspomnieć, że wewnątrz komórki, wartości tekstowe są wyrównywane do lewej strony, liczbowe do prawej. Umożliwia to łatwą identyfikację typu danych.  Zamiana wartości tekstowej na liczbę w Excel możliwa jest na 8 znanych mi sposobów, którymi będą: Podwójny minus „--” Wklej specjalnie, dodaj 0 Operacja matematyczna na funkcji tekstowej dodająca 0 Tekst jako kolumny Funkcja WARTOŚĆ Konwertuj na liczbę – zielony trójkącik Power Query VBA 1. Podwójny minus „--” Pierwsza metoda będzie...
Więcej
Lista dat przed 1900 w Power Query

Lista dat przed 1900-01-01 w Power Query

Jakub Krupa
W Excelu daty są przechowywane jako wartości liczbowe. Jest to dobre rozwiązanie, umożliwiające chociażby łatwe wykonywanie działań matematycznych na datach. Jednak system ten nie jest idealny i wynika z tego pewien drażniący problem. Programiści Excela rozpoczęli numerowanie dat od 1. stycznia 1900 roku przypisując tej dacie wartość 1. Każdy kolejny dzień to wielokrotność tej wartości. W momencie tworzenia tego wpisu numer aktualnego dnia to 43 512. W przypadku operowania datami po 1900-01-01 nie jesteśmy w żaden sposób ograniczeni. Problem pojawia się gdy chcemy działać na datach wcześniejszych. Nie mogą one bowiem przyjmować wartości ujemnych. Ustawiając format daty na ujemnych wartościach Excel wyświetla „######”, mimo że w komórce de facto mamy liczbę. Dodatkowo na takich wartościach nie możemy prowadzić działań matematycznych. Możemy...
Więcej

Wykres Kartogram – wizualizacja na mapie w Excel

Jakub Krupa
Wizualizacja danych przy pomocy map zyskuje coraz większą popularność. Wydaje się to być logicznym kierunkiem. Wykresy oparte na mapach, w czytelny i przyswajalny dla odbiorcy sposób prezentują płaskie dane powiązane z jednostkami geograficznymi. A takich, z roku na rok, gromadzimy coraz więcej. Coraz większa także jest potrzeba ich obróbki i analizy. Excel, wychodząc naprzeciw temu zapotrzebowaniu, od kilku lat rozwija narzędzia pozwalające wykonywać wizualizacje geograficzne. W wydaniu z 2013 roku premierę miał dodatek o nazwie „Power Map”. W późniejszych wersjach został on fabrycznie wbudowany w Excela i pod zmienioną nazwą - „Mapy 3D” – funkcjonuje obecnie. Moim zdaniem, narzędzie to proponuje ciekawe rozwiązania, aczkolwiek jest mało wydajne i dość toporne w obsłudze. Alternatywnym sposobem na prezentację danych geograficznych w aplikacji...
Więcej
Parametry Power Query komórki Excel

Parametry Power Query pobierane z komórki Excel

Jakub Krupa
Zmienna, która w raportach pojawia się najczęściej to zakresy dat. Pobierając dane z różnych źródeł ograniczamy ogromne zbiory poprzez założenie filtrów na datę od i datę do, w zależności od tego jaki okres czasu nas interesuje. Jak wiemy, idealnym narzędziem do pobierania i przekształcania dużych baz danych, jest Power Query. Aby ułatwić pracę użytkownikom, którzy nie znają tego narzędzia, dobrą praktyką jest ustawienie zmiennych parametrów zapytań. Przykładem mogą być wspomniane daty. Stosując parametry Power Query pobierane z komórki Excel każdy użytkownik będzie w stanie modyfikować tak przygotowanie zapytanie zupełnie nie znając tego narzędzia. Parametry zapytań Power Query pobierane bezpośrednio z komórki Excel Dla uproszczenia całego procesu dane bazowe przechowuje w tym samym skoroszycie, w który będą znajdowały się nasze parametry...
Więcej
Nowe typy danych w Excel - Akcje i Geografia

Nowe typy danych Excel – Akcje i Geografia

Jakub Krupa
Po ostatniej aktualizacji mojego pakietu Office 365 na kacie „Dane” pojawiła się nowa, ciekawa funkcjonalność. Microsoft wprowadził 2 nowe typy danych w Excel - Akcje i Geografia. Jest to pewnego rodzaju przełom. Do tej pory w arkuszach mogliśmy przechowywać jedynie  tzw. płaskie dane. Z wykorzystaniem nowej funkcjonalności jesteśmy w stanie rozpoznać zawartość komórki i przypisać jej szereg wartości, które ją opisują. Wszystko działa w oparciu o zalążki sztucznej inteligencji, a Microsoft zapowiada wprowadzenie  kolejnych typów danych działających na tym silniku.  Nowe typy danych - Akcje Funkcjonalność przetestujemy na polskich spółkach wchodzących w skład indeksu WIG20. Ich listę wklejam do mojego arkusza, zaznaczam nazwy spółek i klikam na odpowiednią ikonę z grupy „Typy danych” na karcie „Dane”. Niestety z listy 20 firm...
Więcej
Dynamiczny wykres z wykorzystaniem formantów

Dynamiczny wykres z wykorzystaniem formantów

Jakub Krupa
Zademonstruję jak można wizualizować dane przy użyciu jednego, interaktywnego grafu. Zbudujemy dynamiczny wykres z wykorzystaniem formantów. Będziemy na nim pokazywać (lub ukrywać) jedną bądź kilka wybranych serii danych. W procesie wykorzystamy narzędzia o nazwie „Kontrolki formularza”. Tzw. formanty nie są zbyt często stosowane w codziennej pracy z Excelem. No może poza standardowymi przyciskami, których często używa się do uruchamiania makr. W opisywanym przykładzie z VBA nie zastosujemy w ogóle, całą sprawę załatwi kilka formuł oraz wspomniane formanty. Efekt końcowy na jakim nam zależy wygląda jak poniżej. Na jednym wykresie chcemy zaprezentować wartości z roku aktualnego, wartości z roku poprzedniego oraz wartości planowane. To wszystko dodatkowo z podziałem na 3 kanały bądź w totalu (zsumowane razem). Zamiast tworzyć kilka równoległych grafów...
Więcej
Odzyskiwanie uszkodzonego pliku xlsm

Odzyskiwanie uszkodzonego pliku Excel xlsm

Jakub Krupa
Od jakiegoś czasu obserwuje problem, który dotyczy plików Excel z rozszerzeniem .xlsm, a więc tych przechowujących makra. Ciężko określić powód, ale z jakichś względów, pliki z dnia na dzień tracą swoją użyteczność. Zostają w jakiś sposób uszkodzone i nie jesteśmy w stanie dalej na nich pracować (co jest szczególnie bolesne gdy nie mamy aktualnego backupa naszego skoroszytu). Otwarcie takiego pliku skutkuje zawieszeniem i wyłączeniem całej aplikacji Excel (wszystkich innych już otwartych skoroszytów). Spotkałem się także z przypadkami gdy po otwarciu takiego zepsutego skoroszytu pojawia się okno z informacją „Can’t find project or library”. Poniżej opisuję proces pozwalający na odzyskiwanie uszkodzonego pliku Excel w formacie xlsm. Skutek kliknięcia w „Ok” jest identyczny jak wcześniej - cała sesja Excela zostaje zamknięta. Jak...
Więcej