W tym materiale zobaczysz jak rozdzielać tekst w którym występują różne ograniczniki. W zwykłym arkuszu Excela byłoby to dosyć skomplikowane, trzeba byłoby skorzystać z VBA. W Power Query zadanie to jest bardzo proste!
Użyty mechanizm: Power Query
Pobierz plik do filmu: http://www.pmsocho.com/pobierz-pliki-do-filmow
Nazwa pliku: excel-671 – Rozdzielanie tekstu z różnymi separatorami (ogranicznikami) – Power Query.xlsx
W tym filmie zobaczysz jak policzyć stan zatrudnienia na dowolny dzień. Przy każdej osobie określona jest data zatrudnienia a przy niektórych także data zakończenia współpracy. Na pierwszy rzut oka wyglądało to na proste zadanie dla funkcji LICZ.WARUNKI. Po chwili okazało się jednak, że przeszkodą są puste komórki w kolumnie „Data odejścia”. To skierowało mnie w stronę formuł tablicowych…
Użyta/e funkcja/e: SUMA.ILOCZYNÓW
Pobierz plik do filmu: http://www.pmsocho.com/pobierz-pliki-do-filmow
Nazwa pliku: excel-670 – Stan zatrudnienia na dany dzień – SUMA.ILOCZYNÓW.xlsx
W tym odcinku zobaczysz jak policzyć niepuste komórki biorąc pod uwagę kryteria dotyczące innych kolumn.
Użyta/e funkcja/e: LICZ.WARUNKI
Pobierz plik do filmu: http://www.pmsocho.com/pobierz-pliki-do-filmow
Nazwa pliku: excel-669 – Ile niepustych z kryteriami.xlsx
W tym filmie pokażę Ci baaaaardzo prosty trik, który zobaczyłem ostatnio. Nie wiedziałem że tak można więc szczęka mi opadła i cieszyłem się jak dziecko gdy to zobaczyłem 🙂
Użyty mechanizm: Fragmentator, Tabela przestawna
Pobierz plik do filmu: http://www.pmsocho.com/pobierz-pliki-do-filmow
Nazwa pliku: excel-668 – Zaznaczanie wielu elementów we fragmentatorze – szczęka mi opadła :).xlsx
W tym odcinku zobaczysz jak sprawdzić w którym kwartale roku jest dowolna data.
Użyta/e funkcja/e: MIESIĄC, ZAOKR.GÓRA
Pobierz plik do filmu: http://www.pmsocho.com/pobierz-pliki-do-filmow
Nazwa pliku: excel-667 – Kwartał na podstawie daty.xlsx
Kalendarz w Excelu zaczyna się od 1 stycznia 1900 roku. Po zmianie formatu w takiej komórce na Ogólny zamiast takiej daty pojawi się liczba 1. Dzieje się tak dlatego, że Excel przechowuje daty jako liczby!
A jeśli będziemy chcieli wyodrębnić dzień tygodnia z komórki która jest pusta – niespodziewanie jako wynik pojawi się „sobota”. W tym odcinku tłumaczę to zachowanie.
Użyta/e funkcja/e: TEKST
Pobierz plik do filmu: http://www.pmsocho.com/pobierz-pliki-do-filmow
Nazwa pliku: excel-666 – Dzień tygodnia dla pustej komórki to sobota – dlaczego.xlsx
Ulepszona i poprawiona wersja wyszukiwania odwrotnego które pokazywałem w odcinku excel-664:
Użyta/e funkcja/e: JEŻELI, SUMA
Pobierz plik do filmu: http://www.pmsocho.com/pobierz-pliki-do-filmow
Nazwa pliku: excel-664a – Wyszukiwanie odwrotne, dokładne i sumowanie z pierwszego wiersza i kolumny.xlsx
W tym materiale zobaczysz jak sumować dane warunkowo z wielu arkuszy. Funkcja SUMA.JEŻELI nie obsługuje odwołań 3W (3D) ale można wykorzystać dodatkowo funkcję ADR.POŚR która skorzysta z przygotowanej listy arkuszy i „powie” funkcji SUMA.JEŻELI aby przeglądała nie jeden a wiele arkuszy 🙂
Użyta/e funkcja/e: SUMA.JEŻELI, ADR.POŚR
Pobierz plik do filmu: http://www.pmsocho.com/pobierz-pliki-do-filmow
Nazwa pliku: excel-665 – Sumowanie warunkowe z wielu arkuszy SUMA.JEŻELI na wielu arkuszach.xlsx
W tym odcinku zobaczysz jak zrobić wyszukiwanie odwrotne czyli jak zlokalizować wiersz i kolumnę w której dana wartość się znajduje. Dodatkowo wartości z pierwszej kolumny i pierwszego wiersza odpowiadające wyszukiwanej wartości powinny zostać dodane aby otrzymać stosowny wynik 🙂
Zobaczysz sprytne operacje na tablicach!
Użyta/e funkcja/e: SUMA.ILOCZYNÓW
Pobierz plik do filmu: http://www.pmsocho.com/pobierz-pliki-do-filmow
Nazwa pliku: excel-664 – Wyszukiwanie odwrotne, dokładne i sumowanie z pierwszego wiersza i kolumny.xlsx
W tym odcinku pokażę jak połączyć (skonsolidować) dane z wielu plików Excela oczywiście automatycznie i dynamicznie 🙂 Oprócz połączenia danych z wielu plików w jedną tabelę dodatkowo jeszcze trzeba będzie zrobić podsumowanie dla danych.
Zobaczysz mnóstwo fajnych operacji w Power Query:
1. Pobranie wszystkich plików Excela z folderu
2. Zmiana obiektu typu „Binary” na „Table”
3. Grupowanie danych w Power Query
4. Kolumną przestawną w Power Query
Użyty mechanizm: Power Query
Pobierz plik do filmu: http://www.pmsocho.com/pobierz-pliki-do-filmow
Nazwa pliku: excel-663 – Dynamiczna suma odpowiednich pozycji z różnych plików – Power Query.xlsx
