Excel – Wyszukaj pionowo z duplikatami (wiele wartości jako wyniki) Office inny niż 365

Piotr MajcherExcel - formuły tablicowe

W tym odcinku (podobnie jak w odcinku 866) pokazuję jak wyszukiwać w sytuacji gdy w przeszukiwanej bazie są duplikaty. Funkcja WYSZUKAJ.PIONOWO niestety „naturalnie” nie obsługuje wyszukiwania z duplikatami. Trzeba jej w takiej sytuacji pomagać kolumnami z formułami pomocniczymi albo wykorzystywać inne funkcje.

Jeśli nie masz pakietu Office 365 wówczas czeka Cię tworzenie długiej i skomplikowanej formuły, która obsłuży wyszukiwanie z duplikatami.

Budowanie tej formuły polecam tylko zaawansowanym i wytrwałym użytkownikom 🙂

Użyta/e funkcja/e: INDEKS, JEŻELI, LICZ.JEŻELI,MIN.K, ILE.WIERSZY, LICZBA.KOLUMN, WIERSZ

Użyty mechanizm: Formuły tablicowe

 

Pobierz plik do filmu: http://www.pmsocho.com/pobierz-pliki-do-filmow

Nazwa pliku: excel-867 – Wyszukaj pionowo z duplikatami (wiele wartości jako wyniki) Office inny niż 365.xlsx

 

Excel – Wyszukaj pionowo z duplikatami (wiele wartości jako wyniki) Office 365

Piotr MajcherExcel - formuły tablicowe

W tym odcinku pokazuję jak wyszukiwać w sytuacji gdy w przeszukiwanej bazie są duplikaty. Funkcja WYSZUKAJ.PIONOWO niestety „naturalnie” nie obsługuje wyszukiwania z duplikatami. Trzeba jej w takiej sytuacji pomagać kolumnami z formułami pomocniczymi albo wykorzystywać inne funkcje.

Rozwiązaniem powyższego problemu jest funkcja FILTRUJ (nie mylić z mechanizmem Filtrowania).
Przy jej pomocy możemy bardzo łatwo!! tworzyć w zasadzie dowolne wyszukiwania.

Miej na uwadze, że funkcja FILTRUJ jest dostępna tylko dla użytkowników pakietu Office 365 (dla ogółu użytkowników od lipca 2020)

Użyta/e funkcja/e: FILTRUJ, TRANSPONUJ, POŁĄCZ.TEKSTY

Użyty mechanizm: Tablice dynamiczne, Rozlewanie formuł

 

Pobierz plik do filmu: http://www.pmsocho.com/pobierz-pliki-do-filmow

Nazwa pliku: excel-866 – Wyszukaj pionowo z duplikatami (wiele wartości jako wyniki) Office 365.xlsx

 

Excel – Lipiec 2020 – radosny miesiąc dla użytkowników Excela

Piotr MajcherExcel

W tym odcinku tylko jedna ważna informacja.

Na lipiec 2020 zapowiedziana jest aktualizacja Office 365, która sprawi, że wszyscy użytkownicy Office 365 będą mieli dostępne tablice dynamiczne oraz nowe fantastyczne funkcje!

Tablice dynamiczne i nowe funkcje takie jak FILTRUJ, SORTUJ, SEKWENCJA, UNIKATOWE, X.WYSZUKAJ zostały udostępnione dla testerów pod koniec 2018 roku ale „zwykli” użytkownicy pakietu Office 365 niestety nie mogli z nich skorzystać. Nareszcie się to zmieni!

Użyty mechanizm: Tablice dynamiczne

 

Pobierz plik do filmu: http://www.pmsocho.com/pobierz-pliki-do-filmow

Nazwa pliku: excel-865 – Lipiec 2020 – radosny miesiąc dla użytkowników Excela.xlsx

 

Excel – Szybkie kopiowanie do pustych komórek w dół (wypełnianie pustych)- kilka sztuczek

Piotr MajcherExcel, Excel - Power Query

Poprawny układ danych to klucz do sukcesu w efektywnej analizie danych.

W tym odcinku zobaczysz jak uzupełnić puste komórki wartościami znajdującymi się ponad nimi tak, aby każdy wiersz zawierał kompletną informację o tym, co jest w tabeli/tabelce.

Zobaczysz jak to zrobić kilkoma metodami – wybierz najlepszą dla Ciebie.

Użyte mechanizmy: Power Query, Przejdź do Specjalnie…, Wypełnij w dół, Filtr, Wklej specjalnie…

 

Pobierz plik do filmu: http://www.pmsocho.com/pobierz-pliki-do-filmow

Nazwa pliku: excel-864 – Szybkie kopiowanie do pustych komórek w dół (wypełnianie pustych)- kilka sztuczek.xlsx

 

Excel – Dodawanie dni roboczych gdy data i czas są zapisane razem

Piotr MajcherExcel

W tym odcinku zobaczysz jak dodać do daty i czasu (zapisanych razem) dowolną liczbę dni roboczych tak, aby w wyniku otrzymać datę razem z czasem.

Nie jest to takie proste, jak mogłoby się wydawać ponieważ funkcja DZIEŃ.ROBOCZY zwraca w wyniku jedynie datę. Aby otrzymać również czas można wspomóc się funkcją MOD

W tym odcinku przypominam także, że daty to liczby całkowite a daty i czas to liczby z ułamkami 🙂

Użyta/e funkcja/e: DZIEŃ.ROBOCZY, MOD

Użyty mechanizm: Formatuj komórki, Format niestandardowy

 

Pobierz plik do filmu: http://www.pmsocho.com/pobierz-pliki-do-filmow

Nazwa pliku: excel-863 – Dodawanie dni roboczych gdy data i czas są zapisane razem.xlsx

 

Excel – Liczenie, sumowanie i duplikaty nieprawidłowe – rozwiązanie konkursu

Piotr MajcherExcel - formuły tablicowe

W tym odcinku zobaczysz jak liczyć liczby przechowywane jako teksty. Niektóre funkcje (LICZ.JEŻELI, SUMA.JEŻELI) nie radzą sobie poprawnie jeśli liczby przechowywane jako teksty mają więcej niż 15 cyfr…

Użyta/e funkcja/e: SUMA, SUMA.ILOCZYNÓW, LICZ.JEŻELI

Użyty mechanizm: Formatowanie warunkowe – formuła

 

Pobierz plik do filmu: http://www.pmsocho.com/pobierz-pliki-do-filmow

Nazwa pliku: excel-862 – Liczenie, sumowanie i duplikaty nieprawidłowe – rozwiązanie konkursu.xlsx

 

Excel – Lista unikatowych wartości z obsługą pustych komórek – łatwa formuła

Piotr MajcherExcel - formuły tablicowe

W tym odcinku zobaczysz jak przy pomocy dwóch prostych funkcji generować lity unikalnych wartości ignorując puste komórki z kolumny.

Zobacz jak działają funkcje o nazwach UNIKATOWE i FILTRUJ. Dostępne są one tylko dla użytkowników Office 365. Być może w momencie gdy to czytasz masz je już w Twoim Excelu! 🙂

Funkcje te zostały wprowadzone wraz z mechanizmem tablic dynamicznych i wykorzystują one mechanizm rozlewania, który polega na tym, że jeśli formuła generuje wiele wartości jako wynik to wartości te automatycznie pojawiają się w wielu komórkach bez potrzeby ręcznego kopiowania formuły.
Więcej o tym zobaczysz w odcinku 853.

Użyta/e funkcja/e: UNIKATOWE, FILTRUJ

Użyte mechanizmy: Rozlewanie formuł, Tablice dynamiczne

 

Pobierz plik do filmu: http://www.pmsocho.com/pobierz-pliki-do-filmow

Nazwa pliku: excel-860 – Lista unikatowych wartości z obsługą pustych komórek – łatwa formuła.xlsx

 

Excel – Konkurs – liczenie, sumowanie i duplikaty nieprawidłowe

Piotr MajcherExcel

Twoje zadania:

0. Pobierz plik do filmu – link powyżej.

1. W komórce F3 napisz formułę, która poprawnie policzy ile razy w zakresie B3:B15 występuje wartość z komórki E3

2. W komórce G3 napisz formułę, która poprawnie zsumuje kwoty z zakresu C3:15 dla numeru podanego w komórce E3

3. Ustaw na zakresie B3:B15 formatowanie warunkowe, które poprawnie zaznaczy duplikujące się wartości

4. (Opcjonalnie) Opisz/wyjaśnij z czego wynikają problemy, które omówiłem na początku tego filmu.

Plik z rozwiązaniami prześlij na adres:
pmsocho małpa pmsocho kropka com

Masz czas do:
Sobota, 2020-01-25, 23:59

Użyta/e funkcja/e: LICZ.JEŻELI, SUMA.JEŻELI

Użyte mechanizmy: Formatowanie warunkowe

 

Pobierz plik do filmu: http://www.pmsocho.com/pobierz-pliki-do-filmow

Nazwa pliku: excel-861 – Konkurs – liczenie, sumowanie i duplikaty nieprawidłowe.xlsx

 

Excel – Funkcja FILTRUJ – jak wyszukać wszystkie wartości jedną funkcją

Piotr MajcherExcel - formuły tablicowe

W tym odcinku zobaczysz rozwiązanie odwiecznego problemu: Jak wyszukać wszystkie wartości przypisane do wyszukiwanej wartości. Czyli nic innego jak wyszukiwanie z obsługą duplikatów.

Zapewne wiesz, że funkcje WYSZUKAJ.PIONOWO czy PODAJ.POZYCJĘ nie obsługują duplikatów – znajdują pierwsze wystąpienie szukanej wartości o koniec. Można oczywiście to obejść ale trzeba pisać skomplikowane formuły tablicowe albo korzystać z kolumn pomocniczych.

W najnowszym Excelu (Office 365) Microsoft wprowadził funkcję o nazwie FILTRUJ. Nazwa tej funkcji nie odzwierciedla jej możliwości.

Funkcja FILTRUJ jest bardzo elastyczna. Można jej można używać do wyszukiwania według jednego kryterium lub według wielu kryteriów.

Przy pomocy funkcji FILTRUJ można robić dowolne wyszukiwania. Ponadto, obsługuje ona duplikaty i jako wynik potrafi zwrócić wiele wartości i wpisać te wartości do komórek!

Użyta/e funkcja/e: FILTRUJ

Użyty mechanizm: Tablice Dynamiczne

 

Pobierz plik do filmu: http://www.pmsocho.com/pobierz-pliki-do-filmow

Nazwa pliku: excel-859 – Funkcja FILTRUJ – jak wyszukać wszystkie wartości jedną funkcją.xlsx

 

Excel – PRAWDA i FAŁSZ – jak je sprytnie wykorzystać

Piotr MajcherExcel

W tym odcinku zobaczysz jak sprytnie wykorzystać wartości logiczne PRAWDA i FAŁSZ w Excelu.

Przy wykonywaniu operacji dodawania, odejmowania, mnożenia lub dzielenia wartości logiczne konwertowane są na za 1 i 0.

Użyta/e funkcja/e: DZIEŃ

Użyty mechanizm: Wartości logiczne

 

Pobierz plik do filmu: http://www.pmsocho.com/pobierz-pliki-do-filmow

Nazwa pliku: excel-858 – PRAWDA i FAŁSZ – jak je sprytnie wykorzystać.xlsx