Wybieramy okładkę dla mojej nowej książki Excel SuperHero. Zdjęcia okładek znajdziesz poniżej — mają swoje numerki. Głosujesz zostawiając komentarz z wybranym numerem, ewentualnie kilkoma.
Możesz też spróbować zgadnąć, którą okładkę zaprojektowała moja żona, a którą córka 😉
W dzisiejszym poście omówimy temat zamiany kątków na radiany, następnie z radianów na stopnie. Zadanie to wykonamy na podstawie przykładowych danych z rys. nr 1.
Powinieneś wiedzieć, że mamy 360 stopni, czyli całe koło. Istnieje inny sposób zapisu tego koła jako radiany. Wystarczy sobie przypomnieć wzór na obwód równy 2*π*r, gdzie r to promień koła. Jeśli w stopniach całe koło daje 360o, to w radianach całe koło to 2*π. W Excelu możemy skorzystać z funkcji RADIANY, której argumentem jest kąt, czyli wartość kąta, zapis formuły powinien wyglądać następująco:
=RADIANY(A2)
Po zatwierdzeniu formuły i skopiowaniu jej na wiersze poniżej otrzymamy wartość poszczególnych kątów w radianach (rys. nr 2).
Pod postacią tych wartości w kolumnie Radiany mamy wartość π. Aby dokładniej zobaczyć jaką część wartości π stanowi dany kąt, musimy podzielić wartość kąta w radianach przez wartość π. Aby uzyskać wartość π, możemy w Excelu wykorzystać funkcję PI. Zapis formuły powinien wyglądać następująco:
=B2/PI()
Powyższą formułę zatwierdzamy i kopiujemy na komórki poniżej. Otrzymamy dane przedstawione na rys. nr 3.
Teraz wykonamy konwersję odwrotną, czyli radiany zamienimy na stopnie. Użyjemy do tego funkcji STOPNIE, której argumentem jest kąt, wyrażony w radianach. Zapis formuły powinien wyglądać następująco:
=STOPNIE(B2)
Powyższą formułę zatwierdzamy i kopiujemy na komórki poniżej. Otrzymamy kąty wyrażone w stopniach przedstawione na rys. nr 4.
Istotne jest, że funkcje trygonometryczne np. Cosinus korzystają w Excelu z radianów. Zapis funkcji COSINUS powinien wyglądać następująco:
=COSINUS(B2)
Powyższą formułę zatwierdzamy i kopiujemy w dół. Otrzymamy wartości funkcji cosinus dla poszczególnych kątów przedstawione na rys. nr 5.
Na podstawie wartości kątów w radianach i funkcji cosinus, możemy stworzyć wykres liniowy tzw. sinusoidę (rys. nr 6).
Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą)
II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.
Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".
Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query.
A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.
Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.
Aktualnie w promocji urodzinowej możesz mieć Mistrza Excela w obniżonej cenie, jeśli tylko wpiszesz kod 35URODZINY
https://exceliadam.pl/produkt/ksiazka-mistrz-excela
Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie.
Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.
W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.
Power Query
https://www.udemy.com/course/mistrz-power-query/?couponCode=35URODZINY
Mistrz Excela
https://www.udemy.com/mistrz-excela/?couponCode=35URODZINY
Dashboardy
https://www.udemy.com/course/excel-dashboardy/?couponCode=35URODZINY
Mistrz
Formuł
https://www.udemy.com/course/excel-mistrz-formul/?couponCode=35URODZINY
VBA
https://www.udemy.com/course/excel-vba-makra/?couponCode=35URODZINY
Microsoft Power BI
https://www.udemy.com/course/power-bi-microsoft/?couponCode=35URODZINY
W dzisiejszym poście nauczymy się jak zmienić systemy zapisów liczbowych. W filmie Excel na luzie odcinek nr 6, przedstawiłem formułę Bila Szysza z okazji 1 kwietnia (prima aprilis). Formuła ta z liczb tworzyła słowa. Używaliśmy do tego funkcji PODSTAWA, która opiera się o systemy liczbowe. W wyżej wymienionym filmie pokazywałem jak to policzyć ręcznie, za pomocą różnych funkcji, takich jak: INDEKS, PODAJ.POZYCJĘ, ILE.WIERSZY (rys. nr 1).
Pokazywałem ręczne rozwiązanie, bo nie udało mi się wtedy znaleźć funkcji, która przechodziła by z systemu 36-cio znakowego na standardowy dziesiętny. Temat ten omówimy na podstawie przykładowych danych z rys. nr 2.
Zajmiemy się dziś funkcjami PODSTAWA i DZIESIĘTNA, które dostępne są dla użytkowników od Excela 2013. Mamy nasz standardowy zapis dziesiętny, który ma 10 znaków (0,1,2,3,4,5,6,7,8,9). Istnieją też inne systemy, np. system dwójkowy, którego zapis przedstawiony został na rys. nr 3. W tym systemie używa się do zapisu liczb dwóch znaków: 1 i 0.
Innym popularnym zapisem jest szesnastkowy, przedstawiony na rys. nr 4. W zapisie tym zaczynają się pojawiać litery jako cyfry.
W maksymalnym, dostępnym dla Excela 36-cio znakowym zapisie, również mamy litery (rys. nr 5).
W tym zapisie mamy dostępne wszystkie litery z języka angielskiego (bez polskich znaków), dzięki temu można w zabawny sposób budować sobie liczby.
Aby obliczyć słowo PRIMA, możemy użyć funkcji DZIESIĘTNA. Pierwszym argumentem funkcji jest liczba, czyli w naszym przykładzie komórka F2, która w tym zapisie jest liczbą. Drugi argument funkcji to podstawa, czyli jakiego systemu zapisu użyliśmy – u nas 36-cio znakowy. Zapis formuły powinien wyglądać następująco:
=DZIESIĘTNA(F2;36)
Po zatwierdzeniu formuły otrzymamy wynik przedstawiony na rys. nr 6.
Analogicznie, aby otrzymać wynik dla zapisu APRILIS, wystarczy skopiować formułę na komórkę obok. Otrzymamy wtedy liczby, jakie w zapisie 36-cio znakowym utworzą podane słowa (rys. nr 7).
W Excelu istnieją funkcje, które zamieniają zapisy, ale zazwyczaj opierają się na podstawowych systemach (dwójkowy, ósemkowy, szesnastkowy czy dziesiętny), co widać na rys. nr 8.
Funkcja DZIESIĘTNA radzi sobie z taką zamianą zapisu od 2 do 36 cyfr. Teraz mamy słowo PRIMA w formie liczby w zapisie dziesiętnym. Naszym celem jest uzyskanie liczby w zapisie ósemkowym. Użyjemy do tego funkcji PODSTAWA. Pierwszym argumentem funkcji jest liczba, czyli komórka z liczbą, która chcemy zamienić (F7). Drugi argument funkcji to podstawa, czyli ilość znaków w systemie, u nas zmieniamy liczbę na zapis ósemkowy, więc musimy odwołać się do komórki E8. Zapis formuły powinien wyglądać następująco:
=PODSTAWA(F7;E8)
Po zatwierdzeniu formuły otrzymamy wynik przedstawiony na rys. nr 9.
Następnie zapis w systemie ósemkowym chcemy przekształcić na zapis w systemie szesnastkowym. Jeśli mamy informację, że to jest zapis ósemkowy i chcemy przekształcić go na szesnastkowy to musimy zrobić dwa kroki. Najpierw musimy zamienić tą liczbę na system dziesiętny za pomocą funkcji DZIESIĘTNA. Zapis jej wyglądać będzie następująco:
=DZIESIĘTNA(F8;E8)
Następnie uzyskaną z funkcji DZIESIĘTNA liczbę, musimy zamienić na liczbę w systemie szesnastkowym za pomocą funkcji PODSTAWA. Jako pierwszy argument wpisujemy wynik funkcji DZIESIĘTNA, a drugi argument to odwołanie do komórki E9. Zapis funkcji powinien wyglądać następująco:
=PODSTAWA(DZIESIĘTNA(F8;E8);E9)
Po zatwierdzeniu formuły otrzymamy wynik przedstawiony na rys. nr 10.
Mając stworzoną formułę, możemy się pobawić tymi zapisami zmieniając ilość znaków w systemie z 16 na inne np. 2 czy 4.
Podsumowując, dzięki funkcjom DZIESIĘTNA i PODSTAWA, możemy zmieniać zapisy liczbowe na systemy o różnej ilości cyfr.
Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą)
II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.
Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".
Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query.
A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.
Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.
Aktualnie w promocji urodzinowej możesz mieć Mistrza Excela w obniżonej cenie, jeśli tylko wpiszesz kod 35URODZINY
https://exceliadam.pl/produkt/ksiazka-mistrz-excela
Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie.
Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.
W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.
Power Query
https://www.udemy.com/course/mistrz-power-query/?couponCode=35URODZINY
Mistrz Excela
https://www.udemy.com/mistrz-excela/?couponCode=35URODZINY
Dashboardy
https://www.udemy.com/course/excel-dashboardy/?couponCode=35URODZINY
Mistrz
Formuł
https://www.udemy.com/course/excel-mistrz-formul/?couponCode=35URODZINY
VBA
https://www.udemy.com/course/excel-vba-makra/?couponCode=35URODZINY
Microsoft Power BI
https://www.udemy.com/course/power-bi-microsoft/?couponCode=35URODZINY
W tym poście pokażemy jak uzyskać zapis słowny danej kwoty za pomocą formuły. Formułę tą zapożyczyłem od Bila Szysza. Dzięki użyciu funkcji ZEZWALAJ możemy sobie pozwolić na stworzenie w miarę sensownej długości formułę (rys. nr 1).
Tak naprawdę możemy użyć gotowej formuły Billa Szysza. Wystarczy w niej podmienić jedną wartość, czyli odwołanie do komórki, z której kwotę chcesz przekształcić. Reszta formułe zadziała automatycznie.
Wcześniej takie rozwiązania widziałem tylko w VBA, które dla zwykłego użytkownika jest dużo trudniejsze. Ogólnie pojedyncze formuły są bardzo proste, problem stanowi połączenie tych formuł w jedną całość. Dlatego rozpisałem tą formułę krok po kroku, ponadto ponazywałem poszczególne elementy, żeby można to było łatwiej zrozumieć (rys. nr 2).
W pierwszym kroku musimy mieć liczbę, którą chcemy zapisać słownie, następnie musimy z niej wyciągnąć liczbę całkowitą, a w zasadzie moduł liczby, bo jest ona ujemna. Ucinamy końcówkę z groszami i pozbywamy się minusa z przodu. Trzy elementy formuły dają nam możliwość obliczenia tekstu dla jednostek, tysięcy i milionów (rys. nr 3).
Tak naprawdę są to te same formuły, tylko operują na innych częściach liczby. W kolejnym etapie musimy sobie przygotować rozpisaną dużą ilość liczb, żeby Excel wiedział jakiej końcówki słowa ma użyć. W języku polskim niestety zapis słowny ma wiele zmiennych końcówek (rys. nr 4).
W formułach musimy odwoływać się do tych przygotowanych zakresów, aby zapis słowny był prawidłowy z punktu widzenia języka polskiego.
Podsumowując, kiedy potrzebujesz uzyskać zapis słowny kwot pieniężnych, możesz użyć gotowej formuły. Wystarczy zmienić odwołanie do komórki z liczbą.
Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą)
II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.
Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".
Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query.
A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.
Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.
Aktualnie w promocji urodzinowej możesz mieć Mistrza Excela w obniżonej cenie, jeśli tylko wpiszesz kod 35URODZINY
https://exceliadam.pl/produkt/ksiazka-mistrz-excela
Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie.
Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.
W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.
Power Query
https://www.udemy.com/course/mistrz-power-query/?couponCode=35URODZINY
Mistrz Excela
https://www.udemy.com/mistrz-excela/?couponCode=35URODZINY
Dashboardy
https://www.udemy.com/course/excel-dashboardy/?couponCode=35URODZINY
Mistrz
Formuł
https://www.udemy.com/course/excel-mistrz-formul/?couponCode=35URODZINY
VBA
https://www.udemy.com/course/excel-vba-makra/?couponCode=35URODZINY
Microsoft Power BI
https://www.udemy.com/course/power-bi-microsoft/?couponCode=35URODZINY
W tym poście wykonamy na podstawie przykładowych danych z rys. nr 1 ranking sprzedawców tablicowo. Tym razem omówimy prostą metodę. W poprzednich poradach (399–401) szliśmy torem myślenia Excela klasycznego, używaliśmy kolumn pomocniczych i różnych skomplikowanych funkcji żeby pokazać możliwości łączenia tych funkcji w jedną formułę.
Używaliśmy skomplikowanych formuł, żeby wyciągnąć dane do rankingu sprzedawców. Poprzednie posty są ważne, bo pokazują pewien tok myślenia jak łączyć dane, jak przekształcać poszczególne elementy. W tym poście opiszemy sposób, jak wyciągnąć ranking sprzedawców w jak najprostszy sposób, żeby jednak kolumna odpowiadała niejako za jedną kolumnę danych. Zaczniemy od listy unikatowych sprzedawców. Jesteśmy w Excelu tablicowym, więc skorzystamy z funkcji UNIKATOWE. Argumentem funkcji UNIKATOWE jest Tablica, czyli zakres z którego chcemy wyciągnąć unikatowe wartości (kolumna A – Sprzedawca). Jest to formuła tablicowa, więc mamy tutaj nomenklaturę tablicową, czyli kolumnę zapiszemy jako tSprzedażk[Sprzedawca]. Zapis formuły powinien wyglądać następująco:
=UNIKATOWE(tSprzedażk[Sprzedawca])
Po zatwierdzeniu formuły otrzymamy listę unikatowych sprzedawców przedstawioną na rys. nr 2.
Ilość unikatowych sprzedawców nie zmieni się, kiedy zmienimy ich kolejność. Z tego względu możemy im dołożyć numerację w kolumnie Miejsce. Użyjemy do tego funkcji SEKWENCJA. Pierwszym argumentem funkcji są wiersze, czyli ilość wierszy wypełnionych listą unikatowych sprzedawców. Ilość tych wierszy obliczymy za pomocą funkcji ILE.WIERSZY. Argumentem tej funkcji jest tablica, czyli zakres, w którym chcemy obliczyć ilość wierszy (jesteśmy w Excelu tablicowym więc rozlany zakres zapiszemy H2#). Zapis całej formuły będzie wyglądał następująco:
=SEKWENCJA(ILE.WIERSZY(H2#))
Po zatwierdzeniu powyższej formuły otrzymamy numerację wierszy przedstawioną na rys. nr 3.
Mając listę unikatowych sprzedawców, możemy podliczyć (zsumować) ich wyniki sprzedaży. Zrobimy to przy użyciu funkcji SUMA.JEŻELI. Pierwszym argumentem funkcji jest zakres, czyli nasza kolumna B – Sprzedawca (tSprzedażk[Sprzedawca]). Drugi argument to kryteria, czyli lista naszych unikatowych sprzedawców z rozlanej formuły (H2#). Trzeci argument to suma_zakres, czyli kolumna po jakiej chcemy zsumować wartości (tSprzedażk[Sprzedaż]). Zapis formuły powinien wyglądać następująco:
=SUMA.JEŻELI(tSprzedażk[Sprzedawca];H2#; tSprzedażk[Sprzedaż])
Po zatwierdzeniu powyższej formuły otrzymamy wyniki sprzedaży poszczególnych sprzedawców przedstawione na rys. nr 4.
W Excelu tablicowym mamy możliwość posortowania jednej kolumny według drugiej, czyli możemy posortować listę unikatowych sprzedawców według kolumny Sprzedaż. Trzeba tylko odpowiednio te dane połączyć. Zaczniemy od skopiowania formuły funkcji SUMA.JEŻELI za pomocą skrótu klawiszowego Ctrl+C. Teraz w kolumnie Sprzedawca wpiszemy funkcję SORTUJ.WEDŁUG. Pierwszym argumentem funkcji jest tablica, czyli kolumna którą chcemy posortować, w naszym przypadku będzie to lista unikatowych sprzedawców uzyskana dzięki funkcji UNIKATOWE. Drugi argument funkcji to według_tablicy1, czyli kolumna według której chcemy posortować pierwszą kolumna. W naszym przykładzie będzie to kolumna z podsumowaniem sprzedaży – kolumna Sprzedaż, uzyskana dzięki funkcji SUMA.JEŻELI. Jednak w tej formule odwołujemy się do kolumny, która się rozlewa (H2#), więc musimy ją poprawić. Kolejny argument funkcji to kolejność_sortowania, gdzie mamy do wyboru sortowanie rosnące (wartość 1) lub malejące (wartość ‑1). Wybieramy wartość ‑1. Zapis formuły powinien wyglądać następująco:
=SORTUJ.WEDŁUG(UNIKATOWE(tSprzedażk[Sprzedawca]);SUMA.JEŻELI(tSprzedażk[Sprzedawca];H2#; tSprzedażk[Sprzedaż]);-1)
Jeśli zatwierdzimy formułę bez zmiany zakresu H2# w funkcji SUMA.JEŻELI, to pojawi się nam komunikat Excela o odwołaniach cyklicznych przedstawiony na rys. nr 5.
Powyższy komunikat potwierdzimy przyciskiem OK i otrzymamy dane przedstawione na rys. nr 6.
Jak widać na rysunku powyżej zepsuła nam się lista unikatowych sprzedawców. Wynika to z tego, że nie zmieniliśmy odwołania do rozlanej formuły (H2#) w formule funkcji SUMA.JEŻELI dla funkcji SORTUJ.WEDŁUG. Aby formuła zadziałała prawidłowo, musimy podmienić zakres H2# na formułę, ż której ten zakres powstał, czyli na UNIKATOWE(tSprzedażk[Sprzedawca]. Zapis formuły powinien wyglądać następująco:
=SORTUJ.WEDŁUG(UNIKATOWE(tSprzedażk[Sprzedawca]);SUMA.JEŻELI(tSprzedażk[Sprzedawca]; UNIKATOWE(tSprzedażk[Sprzedawca];tSprzedażk[Sprzedaż]);-1)
Po zatwierdzeniu powyższej formuły otrzymamy prawidłowe wyniki sortowania według przedstawione na rys. nr 7.
Wykonaliśmy nasze zadanie za pomocą dwóch prostych formuł. Mamy ranking (numerację), posortowanych sprzedawców oraz wartości sprzedaży, które same się posortowały, dopasowując się do miejsca konkretnego sprzedawcy w rankingu. Jest to dużo prostszy sposób i bardziej zrozumiały niż formuły z poprzednich postów, gdzie używaliśmy wielu różnych funkcji.
Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą)
II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.
Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".
Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query.
A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.
Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.
Aktualnie w promocji urodzinowej możesz mieć Mistrza Excela w obniżonej cenie, jeśli tylko wpiszesz kod 35URODZINY
https://exceliadam.pl/produkt/ksiazka-mistrz-excela
Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie.
Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.
W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.
Power Query
https://www.udemy.com/course/mistrz-power-query/?couponCode=35URODZINY
Mistrz Excela
https://www.udemy.com/mistrz-excela/?couponCode=35URODZINY
Dashboardy
https://www.udemy.com/course/excel-dashboardy/?couponCode=35URODZINY
Mistrz
Formuł
https://www.udemy.com/course/excel-mistrz-formul/?couponCode=35URODZINY
VBA
https://www.udemy.com/course/excel-vba-makra/?couponCode=35URODZINY
Microsoft Power BI
https://www.udemy.com/course/power-bi-microsoft/?couponCode=35URODZINY
W tym poście omówimy stworzenie rankingu sprzedawców tablicowo za pomocą jednej formuły z użyciem funkcji ZEZWALAJ. Jest to kontynuacja tematu z poprzednich dwóch postów, w których najpierw rozwiązywaliśmy to zagadnienie za pomocą kolumn pomocniczych, a następnie za pomocą jednej formuły (stworzonej z pojedynczych formuł, jakich używaliśmy w kolumnach pomocniczych). Przypomnimy najpierw przykładowe dane do tego zadania (rys. nr 1).
W tym poście pokażemy jak stworzyć ranking unikatowych sprzedawców za pomocą dużo mniej skomplikowanej formuły, niż ta, którą stworzyliśmy w poradzie nr 400 (rys. nr 2) https://exceliadam.pl/excel/excel-ranking-sprzedawcow-1-formula-tablicowa-porada-400 .
Skrócimy powyższą formułę stosując funkcję ZEZWALAJ. Z mojego punktu widzenia, wygodniej jest zastosować nazewnictwo funkcji ZEZWALAJ w już istniejącej formule (zastępując poszczególne elementy). Zastępujemy poszczególne elementy, ponieważ zazwyczaj kiedy odwołujemy się do wcześniejszej formuły stosujemy odwołanie do jej wyników a nie wpisujemy (kopiujemy) jej formuły. Zapis formuły z poprzedniej porady wygląda następująco:
=WYBIERZ({1\2}; SEKWENCJA(ILE.WIERSZY(UNIKATOWE(tSprzedażk[Sprzedawca]))); X.WYSZUKAJ(SEKWENCJA(ILE.WIERSZY(UNIKATOWE(tSprzedażk[Sprzedawca]))); MACIERZ.ILOCZYN(–(TRANSPONUJ(SUMA.JEŻELI(tSprzedażk[Sprzedawca]; UNIKATOWE(tSprzedażk[Sprzedawca]); tSprzedażk[Sprzedaż]))>= SUMA.JEŻELI(tSprzedażk[Sprzedawca]; UNIKATOWE(tSprzedażk[Sprzedawca]); tSprzedażk[Sprzedaż]));SEKWENCJA(ILE.WIERSZY(UNIKATOWE(tSprzedażk[Sprzedawca]));1;1;0)); UNIKATOWE(tSprzedażk[Sprzedawca])))
Aby wstawić Enter w formule (przejść do niższego wiersza) musimy w trybie edycji komórki użyć skrótu klawiszowego Alt+Enter. Zaczynamy od znaku równa się, a następnie wpisujemy nazwę funkcji ZEZWALAJ.
Musimy mieć tutaj "bystre oczko", żeby zauważyć, jakie elementy powtarzają się w naszej skomplikowanej formule. Możemy łatwo zauważyć, że często powtarza się zakres tSprzedażk[Sprzedawca], dlatego ten zakres zastąpimy jako pierwszy mniej skomplikowaną nazwą. Pierwszym argumentem funkcji ZEZWALAJ jest nazwa1, czyli wybrany przez nas element (nazwiemy go np.SprzedW, czyli wszyscy sprzedawcy. Drugi argument funkcji to wartość_nazwy1, czyli wartość naszego nazwanego argumentu – tablica tSprzedażk[Sprzedawca].
Aby za każdym razem nie szukać tej dłuższej nazwy tSprzedażk[Sprzedawca], żeby ją podmienić, musimy ją sobie skopiować. Następnie zaznaczamy dwie komórki (jedną z naszą formułą, a drugą spoza tego zakresu) i używając skrótu klawiszowego Ctrl+H, otwieramy okno Znajdowania i zamieniania. W tym oknie przechodzimy na zakładkę Zamień i w polu Znajdź wklejamy skopiowaną nazwę zakresy sprzed zmiany, a następnie w polu Zamień na wpisujemy nową nazwę nadaną w funkcji ZEZWALAJ dla tego zakresu – SprzedW. Tak ustawione parametry zatwierdzamy przyciskiem Zamień wszystkie (rys. nr 3).
Excel wyświetli nam komunikat o ilości takich zamian (rys. nr 4).
Otrzymaliśmy błąd w formule #NAZWA?, który wynika z tego, że został zamieniony również drugi argument funkcji ZEZWALAJ. Musimy go poprawić i wpisać pierwotną nazwę.
Trzeci argument funkcji to obliczenie_lub_nazwa2. U nas będzie to kolejny arguemnt nazwa2. Szukamy teraz kolejnego elementu, który się powtarza w naszej długiej formule – będzie to kolejny zakres tSprzedażk[Sprzedaż]. W formule ponownie naciskamy skrót Alt+Enter, aby przejść do kolejnego wiersza i tam podajemy kolejny argument funkcji – (nazwa2) Sprzedaż, trzeci argument, czyli wartość_nazwy2 to będzie tSprzedażk[Sprzedaż]. Ponownie zaznaczamy jedną komórkę z zakresu z naszą formułą oraz jedną z sąsiadujących i za pomocą skrótu klawiszowego Ctrl+H, otwieramy okno Znajdowania i zamieniania. W tym oknie analogicznie jak dla pierwszej nazwy w polu Znajdź wpisujemy wartość_nazwy2 (tSprzedażk[Sprzedaż]), a w polu Zamień na wpisujemy nazwa2 (Sprzedaż). Zatwierdzamy zamienianie przyciskiem Zamień wszystkie. Ponownie wyświetli nam się komunikat Excela o ilości zmian i otrzymamy błąd w formule, wynikający z tego ze został zamieniony również argument wartość_nazwy2. Wystarczy to zmienić i formuła zadziała prawidłowo. Na tym etapie formuła funkcji ZEZWALAJ powinna wyglądać następująco:
=ZEZWALAJ(SprzedW; tSprzedażk[Sprzedawca];
Sprzedaż; tSprzedażk[Sprzedaż];
WYBIERZ({1\2}; SEKWENCJA(ILE.WIERSZY(UNIKATOWE(SprzedW))); X.WYSZUKAJ(SEKWENCJA(ILE.WIERSZY(UNIKATOWE(SprzedW))); MACIERZ.ILOCZYN(–(TRANSPONUJ(SUMA.JEŻELI(SprzedW; UNIKATOWE(SprzedW); Sprzedaż))>= SUMA.JEŻELI(SprzedW; UNIKATOWE(SprzedW); Sprzedaż));SEKWENCJA(ILE.WIERSZY(UNIKATOWE(SprzedW));1;1;0)); UNIKATOWE(SprzedW)))
Fragmenty danych, które nazywamy możemy również ręcznie zamieniać w formule bez korzystania z polecenia Znajdywania i zamieniania. Jednak wykorzystanie go daje nam gwarancję, że żadne ich wystąpienie nam nie umknie.
Kolejnym powtarzającym się elementem jest funkcja UNIKATOWE(SprzedW) – argument wartość_nazwa3, którą zastąpimy słowem Sprzedawcy (nazwa3). Znowu analogicznie jak dla poprzednich dwóch nazw zastępujemy odpowiednie nazwy polecenie Znajdywania i zamieniania.
Następną powtarzającą się nazwą jest funkcja ILE.WIERSZY(Sprzedawcy), czyli nasz kolejny argument funkcji ZEZWALAJ – wartość_nazwy4, który zamienimy na Wiersze (nazwa4). Ponownie zamieniamy wartości w całej formule.
Kolejna nazwa, którą zamienimy to SUMA.JEŻELI(SprzedW; Sprzedawcy; Sprzedaż). Powtarza się ona dwa razy, więc podmienimy ją ręcznie. Przypiszemy jej nazwę Wyniki. Zapis całej formuły będzie wyglądał następująco:
=ZEZWALAJ(SprzedW; tSprzedażk[Sprzedawca];
Sprzedaż; tSprzedażk[Sprzedaż];
Sprzedawcy; UNIKATOWE(SprzedW);
Wiersze; ILE.WIERSZY(Sprzedawcy);
Wyniki; SUMA.JEŻELI(SprzedW; Sprzedawcy; Sprzedaż);
WYBIERZ({1\2}; SEKWENCJA(Wiersze); X.WYSZUKAJ(SEKWENCJA(Wiersze); MACIERZ.ILOCZYN(–(TRANSPONUJ(Wyniki)>= Wyniki);SEKWENCJA(Wiersze;1;1;0)); Sprzedawcy))
Po zatwierdzeniu formuły otrzymamy wyniki przedstawione na rys. nr 5.
Podsumowując, najłatwiej było zastosować funkcje ZEZWALAJ na istniejącej długiej formule. Dzięki jej wykorzystaniu mogliśmy podmienić elementy powtarzające się w formule.
Nasza formuła nadal działa dynamicznie, czyli kiedy wprowadzimy zmiany do danych wejściowych, wyniki automatycznie się zaktualizują.
Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą)
II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.
Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".
Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query.
A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.
Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.
Aktualnie w promocji urodzinowej możesz mieć Mistrza Excela w obniżonej cenie, jeśli tylko wpiszesz kod 35URODZINY
https://exceliadam.pl/produkt/ksiazka-mistrz-excela
Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie.
Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.
W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.
Power Query
https://www.udemy.com/course/mistrz-power-query/?couponCode=35URODZINY
Mistrz Excela
https://www.udemy.com/mistrz-excela/?couponCode=35URODZINY
Dashboardy
https://www.udemy.com/course/excel-dashboardy/?couponCode=35URODZINY
Mistrz
Formuł
https://www.udemy.com/course/excel-mistrz-formul/?couponCode=35URODZINY
VBA
https://www.udemy.com/course/excel-vba-makra/?couponCode=35URODZINY
Microsoft Power BI
https://www.udemy.com/course/power-bi-microsoft/?couponCode=35URODZINY
