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.
Rys. nr 1 – przykładowe dane
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).
Rys. nr 2 – kąty zamienione na radiany
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.
Rys. nr 3 – kąty wyrażone ułamkiem wartości π
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.
Rys. nr 4 – kąty wyrażone w stopniach (przekonwertowane z radianów)
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.
Rys. nr 5 – wartości funkcji cosinus dla poszczególnych kątów wyrażonych w radianach
Na podstawie wartości kątów w radianach i funkcji cosinus, możemy stworzyć wykres liniowy tzw. sinusoidę (rys. nr 6).
Rys. nr 6 – sinusoida
Książka Mistrz Excela + promo na 35 urodziny
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.
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.
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).
Rys. nr 1 – podgląd rozwiązania odcinka nr 6 Excela na luzie
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.
Rys. nr 2 – przykładowe dane
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.
Rys. nr 3 – zapis systemu dwójkowego
Innym popularnym zapisem jest szesnastkowy, przedstawiony na rys. nr 4. W zapisie tym zaczynają się pojawiać litery jako cyfry.
Rys. nr 4 – zapis systemu szesnastkowego
W maksymalnym, dostępnym dla Excela 36-cio znakowym zapisie, również mamy litery (rys. nr 5).
Rys. nr 5 – zapis systemu 36-cio znakowego
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.
Rys. nr 6 – liczba uzyskana z zapisu PRIMA dzięki funkcji DZIESIĘTNA
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).
Rys. nr 7 – słowa PRIMA i APRILIS zapisane jako liczby
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.
Rys. nr 8 – funkcje zamieniające systemy zapisu bezpośrednio
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.
Rys. nr 9 – obliczenie zapisu ósemkowego liczby zapisanej w systemie dziesiętnym
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.
Rys. nr 10 – liczba w zapisie ósemkowym przekształcona na liczbę w systemie szesnastkowym
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.
Książka Mistrz Excela + promo na 35 urodziny
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.
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.
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).
Rys. nr 1 – formuła funkcji ZEZWALAJ
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).
Rys. nr 2 – rozpisanie formuły krok po kroku
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).
Rys. nr 3 – wyliczenie tekstu dla jednostek, tysięcy i milionów
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).
Rys. nr 4 – rozpisany zapis słowny liczb
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ą.
Książka Mistrz Excela + promo na 35 urodziny
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.
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.
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łę.
rys1 — przykładowe dane
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.
Rys. nr 2 – lista unikatowych sprzedawców
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.
Rys. nr 3 – numeracja wierszy unikatowych sprzedawców
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:
Po
zatwierdzeniu powyższej formuły otrzymamy wyniki sprzedaży poszczególnych
sprzedawców przedstawione na rys. nr 4.
Rys. nr 4 – wartości sprzedaży poszczególnych sprzedawców
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:
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.
Rys. nr 5 – komunikat Excela
Powyższy komunikat potwierdzimy przyciskiem OK i otrzymamy dane przedstawione na rys. nr 6.
Rys. nr 6 – wyniki sortowania według
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:
Po
zatwierdzeniu powyższej formuły otrzymamy prawidłowe wyniki sortowania według
przedstawione na rys. nr 7.
Rys. nr 7 – prawidłowe wyniki sortowania
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.
Książka Mistrz Excela + promo na 35 urodziny
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.
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.
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).
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:
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).
Rys. nr 3 – okno Znajdowania i zamieniania
Excel wyświetli nam komunikat o ilości takich zamian (rys. nr 4).
Rys. nr 4 – komunikat Excela o ilości zamienionych nazw
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:
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:
Po
zatwierdzeniu formuły otrzymamy wyniki przedstawione na rys. nr 5.
Rys. nr 5 – wyniki działania funkcji ZEZWALAJ
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ą.
Książka Mistrz Excela + promo na 35 urodziny
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.
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.
W tym poście stworzymy ranking sprzedawców tablicowo za pomocą jednej formuły. W poprzednim poście omawialiśmy takie samo rozwiązanie tablicowe, ale z wykorzystaniem kolumn pomocniczych (porada 399 https://exceliadam.pl/excel/excel-ranking-sprzedawcow-tablicowo-porada-399 ). Dane do tego zadania oraz kolumny pomocnicze z poprzedniego postu zostały przedstawione na rys. nr 1.
Rys. nr 1 – dane do rozwiązania zadania
W poprzedniej poradzie rozpisaliśmy rozwiązanie na poszczególne formuły pomocnicze w osobnych kolumnach. Dzięki temu rozwiązanie było proste. W tym poście połączymy te formuły w jedną. Zasada ogólnie jest prosta, czyli będziemy kopiować poszczególne formuły. Formułę z poprzedniego kroku będziemy kopiować i wstawiać do kolejnej, podstawiając ją w miejscu, gdzie odwołujemy się do poprzedniej formuły. To wszystko w takim zamyśle było by proste, ale niestety funkcja POZYCJA.NAJW nie radzi sobie z obliczeniami w swoim argumencie odwołanie. Jeśli do tego argumentu dodamy wartość 0 (zero), czyli teoretycznie nie zmienimy wyniku, to Excel wyświetli nam komunikat o nieprawidłowej formule przedstawiony na rys. nr 2.
Rys. nr 2 – komunikat Excela
Podobnie
zachowuje się funkcja SUMA.JEŻELI. Obie te funkcje nie pozwalają na
obliczenia w swoich argumentach. Podsumowując, nie możemy wstawiać formuł z
poprzedniego kroku do kolejnego. Z tego względu musimy użyć innego rozwiązania,
które pozwoli nam wyciągnąć miejsca w rankingu poszczególnych sprzedawców.
Niestety rozwiązanie to będzie skomplikowane. Tak naprawdę, żeby sprawdzić,
które miejsce ma dany sprzedawca, to musimy sprawdzić, ilu sprzedawców ma
lepsze bądź równe miejsce. Kopiujemy sobie zapis funkcji SUMA.JEŻELI.
Zapis wygląda następująco:
I tworzymy
zapis, w którym sprawdzimy czy nasze wyniki funkcji SUMA.JEŻELI są większe bądź
równe wynikom funkcji SUMA.JEŻELI. Zapis formuły powinien wyglądać następująco:
Po
zatwierdzeniu powyższej formuły otrzymamy wyniki przedstawione na rys. nr 3.
Rys. nr 3 – wyniki porównania dwóch funkcji SUMA.JEŻELI
Otrzymaliśmy
wartości logiczne PRAWDA ponieważ funkcja porównała ze sobą poszczególne
wiersze. Nam chodzi o to, aby funkcja porównała każdy wiersz ze wszystkimi
innymi. Taki efekt możemy uzyskać za pomocą funkcji TRANSPONUJ. Dzięki
tej funkcji transponujemy jedną z naszych tablic. Sprawi to, że otrzymamy
wyniki przedstawione na rys. nr 4. Zapis formuły powinien wyglądać następująco:
Rys. nr 4 – wyniki porównania po transponowaniu jednej z tablic
Przy takiej
orientacji w pierwszym wierszu mamy informację, ile wartości z porównania jest
mniejszych od sprawdzanej aktualnie wartości. Dla pierwszego wiersza
(sprzedawca Maria) mamy 5 wartości logicznych PRAWDA i jedną wartość FAŁSZ,
oznacza to, że 5 osób z listy sprzedawców było od niej lepszych. Można
zauważyć, że wynik się zgadza, ponieważ w tabeli powyżej w kolumnie I mamy
informację, że Maria jest na 5‑tym miejscu w rankingu.
Teraz
wartości logiczne PRAWDA i FAŁSZ chcemy zamienić na wartości 0 i 1. Możemy to
zrobić za pomocą podwójnej negacji, czyli wstawiamy przed formułą dwa znaki
minus. Zapis powinien wyglądać następująco:
Po
zatwierdzeniu powyższej formuły otrzymamy wyniki przedstawione na rys. nr 5.
Rys. nr 5 – zamiana wartości logicznych na 0 i 1
Teraz musimy zsumować wartości w poszczególnych wierszach. Gdybyśmy użyli funkcji SUMA.ILOCZYNÓW to otrzymalibyśmy zliczoną całą tablicę. Możemy tutaj wykorzystać funkcję MACIERZ.ILOCZYN, żeby każdy wiersz przemnożył się przez kolumnę danych, którą pokażemy. Funkcja ta będzie mnożyć poszczególne elementy z wierszy pierwszej tabeli przez kolumny z drugiej tabeli, a następnie wyniki tego mnożenia doda do siebie, by wstawić je w pojedynczą komórkę. Potrzebujemy kolumny o takiej samej ilości komórek co wiersz. Cała formuła z poprzedniego kroku to będzie nasz pierwszy argument funkcji, czyli tablica1. Teraz musimy wstawić drugą tablicę (argument tablica2). Aby stworzyć drugą tablicę, musimy użyć funkcji SEKWENCJA, w której musimy mieć taką samą liczbę kolumn i wierszy. Pierwszy argument funkcji to wiersze, który możemy wyznaczyć za pomocą funkcji ILE.WIERSZYdla zakresu G2# (listy unikatowych sprzedawców). Drugi argument funkcji to kolumny, czyli wpisujemy wartość 1, bo potrzebujemy jednaj kolumny. Trzeci argument to początek, czyli od jakiej wartości chcemy zacząć – u nas wartość 1. Czwarty argument to krok, czyli wartość 0. Kiedy podejrzymy argument tablica2 funkcji MACIERZ.ILOCZYN to możemy zauważyć, że mamy same wartości 1 co widać na rys. nr 6.
Rys. nr 6 – podgląd wartości argumentu tablica2
Z podglądu
edycji formuły wychodzimy za pomocą skrótu klawiszowego Ctrl+Z. Cały
zapis formuły powinien wyglądać następująco:
Powyższą
formułę zatwierdzamy i otrzymamy tablicę z rankingiem sprzedawców przedstawioną
na rys. nr 7.
Rys. nr 7 – tablica z rankingiem sprzedawców
Stworzona
przez nas formuła jest dużo bardziej skomplikowana niż funkcja POZYCJA.NAJW
użyta w rozwiązaniu z kolumnami pomocniczym. Ta skomplikowana formuła poradziła
sobie doskonale z wyznaczeniem miejsc w rankingu poszczególnych sprzedawców. W
tej formule użyliśmy odwołania do zakres G2#, więc odwołanie to musimy jeszcze
zastąpić formułą z komórki G2. Zapis formuły będzie wtedy wyglądał następująco:
Mamy
odpowiednie wyniki i teraz możemy przejść na kolejny poziom obliczeń (ten,
który uzyskaliśmy w kolumnie K w poprzednim poście). Użyjemy funkcji SEKWENCJA
z komórki K2 i wstawimy ją w funkcję X.WYSZUKAJ. Funkcja X.WYSZUKAJ
miała formułę przedstawioną poniżej:
=X.WYSZUKAJ(K2#;I2#;G2)
Teraz w
miejsce poszczególnych zakresów rozlanych formuł musimy wstawić odpowiednie
formuły. Zakres K2# zastąpimy formułą z komórki K2, czyli
SEKWENCJA(ILE.WIERSZY(UNIKATOWE(tSprzedażk[Sprzedawca])))
Następnie
zamiast zakresu I2# nie możemy wstawić formuły z komórki I2, ponieważ funkcja
ta nie radzi sobie z obliczeniami w swoich argumentach. Musimy tutaj wstawić
skomplikowaną formułę, dzięki której wyznaczyliśmy ranking sprzedawców. Formuła
ta wyglądała następująco:
Powyższą
formułę zatwierdzamy i otrzymamy wyniki przedstawione na rys. nr 8.
Rys. nr 8 – wyniki skomplikowanej formuły funkcji X.WYSZUKAJ
Tak jak
zaznaczyłem na wstępie, chcemy mieć jedną formułę do przydzielania miejsc. A na
tą chwilę mamy dwie – w komórkach K2 i L2. Mamy w wynikach dwie kolumny, które
możemy połączyć za pomocą funkcji WYBIERZ. Najpierw kopiujemy całą
formułę funkcji X.WYSZUKAJ. Następnie w komórce K2 wpisujemy funkcję WYBIERZ,
której pierwszym argumentem jest nr_arg, czyli argumenty jakie chcemy
wybrać (w naszym przykładzie pierwszy i drugi argument {1\2}). Używamy tutaj backslasha,
żeby poinformować Excela, że chodzi nam o kolumny danych, a nie wiersze. Drugi
argument funkcji to wartość1, czyli zapis formuły, dzięki której
uzyskaliśmy pierwszą kolumnę danych. Formuła wygląda następująco: SEKWENCJA(ILE.WIERSZY(UNIKATOWE(tSprzedażk[Sprzedawca])))
Kolejny
argument to wartość2, czyli druga kolumna danych uzyskana za pomocą
skomplikowanej formuły funkcji X.WYSZUKAJ.
Zapis całej
formuły funkcji WYBIERZ powinien wyglądać następująco:
Po zatwierdzeniu
powyższej formuły otrzymamy dwie kolumny danych jednocześnie w kolumnach K i L
(takie same jak na rys. nr 8).
Podsumowując,
za pomocą funkcji WYBIERZpołączyliśmy dwie kolumny danych. Jak widać
otrzymaliśmy bardzo skomplikowaną formułę. Dlatego często lepiej jest zrobić
sobie kolumny pomocnicze, by ewentualnie później tylko przekleić formuły w
odpowiednie miejsca do funkcji wyższego poziomu. Gdyby nie funkcja POZYCJA.NAJW,
która nie radzi sobie z obliczeniami w jej argumentach, sprawa była by bardzo
prosta. Jednak tutaj musieliśmy ją zastąpić innym rozwiązaniem.
Mamy wyciągnięte
dane, więc teraz tak jak w poprzednim wideo zmodyfikujemy dane wejściowe przez
doklejenie dodatkowych wierszy. Sprawdzimy w ten sposób, czy nasza formuła
działa dynamicznie. Po doklejeniu dodatkowych danych nasze wyniki się
rozszerzyły o nowe pozycje, co widać na rys. nr 9.
Rys. nr 9 – zmienione dane po zmianach w danych wejściowych
Do wyznaczenia rankingu unikatowych sprzedawców użyliśmy jednej formuły, która zwróciła nam dwie kolumny danych (rys. nr 10).
Rys. nr 10 – skomplikowana formuła zwracająca dwie kolumny danych
Podsumowując,
nie zawsze możemy sobie pozwolić na tworzenie kolumn pomocniczych w analizie
danych. Dlatego możemy sobie je stworzyć tylko po to, by użyć ich do stworzenia
skomplikowanej formuły a potem je skasować.
W kolejnej
poradzie (nr 401) pomówimy o tym jak można ponazywać zakresy danych, żeby
skomplikowana formuła stała się krótsza i bardziej zrozumiała na pierwszy rzut
oka. Użyjemy do tego funkcji ZEZWALAJ.
Książka Mistrz Excela + promo na 35 urodziny
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.
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.
