W tym poście nauczymy się, jak można zwiększać cenę produktu o różne stawki. Będziemy to robić dwoma sposobami: za pomocą formuł i wklejania specjalnego.
Temat ten omówimy na podstawie przykładowych danych z rys. nr 1.
Rys. nr 1 – przykładowe dane (przykład 1)
W pierwszym przykładzie będziemy zwiększać cenę o 10%. Jak widać na rys. nr 1 w kolumnie C mamy aktualną cenę, którą żeby zwiększyć o 10% musimy pomnożyć, czyli mnożymy aktualną cenę razy Wzrost (komórka G2 zablokowana bezwzględnie za pomocą klawisza F4). Zapis formuły powinien wyglądać następująco:
=C2*$G$2
Powyższą formułę zatwierdzamy i kopiujemy w dół. Otrzymamy ceny powiększone o 10% przedstawione na rys. nr 2.
Rys. nr 2 – ceny powiększone o 10% za pomocą formuły
Jak skopiujemy formułę to zaznaczony zakres możemy skopiować za pomocą skrótu klawiszowego Ctrl+C, następnie wklejamy w kolumnie C za pomocą wklejania specjalnego. Klikamy prawym przyciskiem myszy na kolumnę C a następnie z podręcznego menu wybieramy wklej Wartość (W) z Opcji wklejania (rys. nr 3).
Rys. nr 3 – Opcja wklejania Wartość (W)
Otrzymamy wklejone nowe ceny do kolumny C (rys. nr 4). Można też zauważyć, że formuła z kolumny E automatycznie się przeliczyła i stworzyła wartości większe o 10% od tych nowych wklejonych cen.
Rys. nr 4 – nowe ceny wklejone do kolumny C
Jest inny sposób, który czasami daje ciekawe możliwości. Zaznaczamy komórkę G2 z wartością informującą o wielkości wzrostu cen i ją kopiujemy za pomocą skrótu klawiszowego Ctrl+C, zaznaczamy zakres D2:D11 i włączamy opcje wklejania specjalnego za pomocą skrótu klawiszowego Alt+Ctrl+V. Otworzy nam się okno wklejania specjalnego, gdzie w polu Wklej zaznaczamy checkbox Wartości (punkt 1 na rys. nr 5), a w polu Operacja zaznaczamy Przemnóż (punkt 2). Tak ustawione parametry wklejania specjalnego zatwierdzamy przyciskiem OK (punkt 3).
Rys. nr 5 – okno Wklejania specjalnego
Otrzymamy ceny powiększone o 10% uzyskane za pomocą wklejania specjalnego przedstawione na rys. nr 6.
Rys. nr 6 — ceny powiększone o 10% uzyskane za pomocą wklejania specjalnego
Jeśli mamy takie proste obliczenia, to wklejanie specjalne może być troszeczkę szybsze od formuły.
Przejdziemy teraz do przykładu drugiego, żeby pokazać jak się różni działanie formuły i wklejania specjalnego (rys. nr 7).
Rys. nr 7 — przykładowe dane (przykład 2)
W przykładowych danych mamy podane 3 stawki wzrostu cen. Z założenia mamy 3 wariancje produktów, które różnią się składem. Każdą z nich powinniśmy przemnożyć przez odpowiednią wartość z kolumny Wzrost. Użyjemy do tego funkcji JEŻELI. W argumencie test_logiczny chcemy sprawdzić czy komórka O2 jest pusta (O2=""). Jeżeli warunek jest spełniony to w argumencie wartość_jeżeli_prawda chcemy otrzymać wartość z komórki K2, w przeciwnym razie chcemy aktualną cenę przemnożyć przez Wzrost – argument wartość_jeżeli_fałsz. Zapis formuły powinien wyglądać następująco:
=JEŻELI(O2="";K2;K2*O2)
Powyższą formułę zatwierdzamy i kopiujemy w dół. Otrzymamy nowe ceny uzyskane za pomocą funkcji JEŻELI (rys. nr 8).
Rys. nr 8 — nowe ceny uzyskane za pomocą funkcji JEŻELI
Takie rozwiązanie tworzy nam nową cenę jeśli w kolumnie Wzrost jest podana wartość.
Co ciekawe, jeśli w kolumnie Wzrost mamy 3 komórki wypełnione, a w kolumnie z cenami ilość danych będzie wielokrotnością trzech (lub zaznaczymy tylko np. 9 komórek), to za pomocą wklejania specjalnego uzyskamy interesujący efekt. Zaznaczmy zakres ze wzrostem cen (O2:O4) i skopiujmy go za pomocą skrótu klawiszowego Ctrl+C, następnie zaznaczmy tylko 9 komórek w kolumnie L i za pomocą skrótu klawiszowego Alt+Ctrl+V otwórzmy okno Wklejania specjalnego. W tym oknie w polu Wklej zaznaczamy checkbox Wartości, a w polu Operacja wybieramy Przemnóż (jak na rys. nr 5). Tak ustawione parametry zatwierdzamy przyciskiem OK.
Otrzymamy nowe ceny w kolumnie L przedstawione na rys. nr 9.
Rys. nr 9 – nowe ceny uzyskane za pomocą wklejania specjalnego
Jak widać na rysunku powyżej każda grupa 3 cen została przemnożona przez wartości z kolumny O. Mianowicie po kolei pierwszy element z grupy Produkt1 został pomnożony przez wartość 110%, drugi element z grupy Produkt1 został pomnożony przez wartość 120% i analogicznie 3 produkt przez 130% i tak dalej, aż do 9tego produktu.
A co w sytuacji, jeśli zaznaczony zakres w kolumnie L nie będzie wielokrotnością ilości komórek w kolumnie Wzrost? Sprawdzimy to w ten sposób, że zaznaczamy zakres O2:O4, następnie zaznaczamy zakres L2:L11 i uruchamiamy Wklejanie specjalne. Analogicznie jak wcześniej zaznaczamy Wartość i Przemnóż (jak na rys. nr 5). Zatwierdzamy przyciskiem OK. Otrzymamy wyniki przedstawione na rys. nr 10.
Rys. nr 10 – wyniki wklejania specjalnego
Jak widać na rysunku powyżej zostały pomnożone tylko 3 pierwsze komórki. Reszta cen nie uległa zmianie.
Przejdziemy teraz do trzeciego przykładu. Przykładowe dane zostały przedstawione na rys. nr 11.
Rys. nr 11 – przykładowe dane (przykład 3)
W rozwiązaniu za pomocą funkcji JEŻELI formuła wygląda analogicznie jak w poprzednim przykładzie. Zapis funkcji w kolumnie U powinien wyglądać następująco:
=JEŻELI(W2="";T2;T2*W2)
Czyli jeśli komórka ze wzrostem ceny nie jest pusta, chcemy zwiększyć cenę o 15%. Jeśli jest pusta ma pozostać aktualna cena.
Teraz zrobimy takie samo rozwiązanie za pomocą wklejania specjalnego. Zaznaczamy zakres W2:W11i kopiujemy, następnie zaznaczamy zakres w kolumnie T (T2:T11) i uruchamiamy Wklejanie specjalne za pomocą skrótu klawiszowego Ctrl+Alt+V. Ponownie zaznaczamy Wartość i Przemnóż. Istotne jest, aby tutaj zaznaczyć dodatkowy checkbox Pomijaj puste. Zatwierdzamy przyciskiem OK (rys. nr 12).
Ten dodatkowy checkbox sprawia, że jeśli w danym wierszu mamy pustą komórkę w kolumnie Wzrost, to ta cena nie jest mnożona. Nie jest wykonywana żadna operacja w tym wierszu. Otrzymamy dane przedstawione na rys. nr 13.
Rys. nr 13 – wyniki uzyskane za pomocą wklejania specjalnego z pominięciem pustych komórek
Jak widać na rys. powyżej zostały wykonane operacje zmiany ceny tylko w wierszach, gdzie w kolumnie Wzrost mieliśmy niepuste komórki.
Podsumowując opcja Wklejania specjalnego pozwala nam przemnożyć ceny przez różne stawki wzrostu. Często może być to szybszym i ciekawszym rozwiązaniem niż pisanie formuł.
W
dzisiejszym poście omówimy funkcjonalność polegającą na sprawdzaniu czy dany
element jest na liście. W przykładowych danych przedstawionych na rys. nr 1
mamy tabelę ze sprzedawcami, gdzie w zależności na której liście znajduje się
dany sprzedawca, to z tej listy chcemy pobierać dane.
Rys. nr 1 – przykładowe dane
Zakładamy,
że główna lista zawiera województwo, w którym działa dany sprzedawca i właśnie
stamtąd chcemy pobierać dane. W pierwszym kroku musimy pobrać nasze dane z
tabel pobrać do Power Query. Możemy to zrobić wybierając polecenie Z
tabeli/zakresu z karty Dane (rys. nr 2).
Rys. nr 2 – polecenie Z tabeli/ zakresu
Otworzy nam
się edytor zapytań Power Query z wczytaną tabelą pobraną z Excela przedstawioną
na rys. nr 3.
Rys. nr 3 – edytor zapytań Power Query z wczytaną tabelą z danymi
W
analogiczny sposób pobieramy dane z drugiej tabeli. Ustawiamy aktywną komórkę w
obszarze tabeli z regionami i wybieramy polecenie Z tabeli/ zakresu z karty Dane (jak na rys. nr 2).
Otrzymamy dwa odrębne zapytania w Edytorze zapytań Power Query (rys. nr 4).
Rys. nr 4 – dwa zapytania w Power Query (dwie tabele)
Możemy
przybliżyć dane (powiększyć) za pomocą skrótu klawiszowego Ctrl+Shift+=.
Przechodzimy na zapytanie tSprzedaż_k, gdzie naszym zadaniem będzie napisanie
kolumny warunkowej, która na podstawie sprzedawcy będzie wybierała wartość z
kolumny Świętokrzyskie albo Mazowieckie. W tym celu wybieramy polecenie Kolumna warunkowa z karty Dodaj kolumnę (rys. nr 5).
Rys. nr 5 – polecenie Kolumna warunkowa
Otworzy nam
się okno Dodawania kolumny warunkowej. Ta funkcjonalność Power Query posłuży
nam do zbudowania struktury funkcji IF (funkcji JEŻELI). Kolumnę tą łatwo
będzie stworzyć, ale później dokonamy pewnych modyfikacji, żeby sprawdzić czy
dany element jest na liście. W polu Nazwa nowej kolumny wpisujemy Główny
region (punkt 1 na rys. nr 6). W polu Nazwa kolumny wybieramy z listy
rozwijanej kolumnę Sprzedawca (punkt 2), w polu Operator zostawiamy
domyślny, czyli równa się (punkt 3). W polu Wartość wpisujemy nazwę
dowolnego sprzedawcy z regionu Świętokrzyskie np. Aleksandra (punkt 4),
natomiast w polu Wartość wyjściowa najpierw rozwijamy ikonkę obok pola i
wybieramy z listy rodzaj danych – Wybierz kolumnę (oznaczone strzałką) i
w polu wybieramy wartość z kolumny Świętokrzyskie (punkt 5). Następnie klikamy
przycisk Dodaj regułę, aby stworzyć kolejną strukturę funkcji JEŻELI.
W drugiej
regule w polu Nazwa kolumny wybieramy kolumnę Sprzedawca, pole Operator
zostaje bez zmian, w polu Wartość wpisujemy Dominik, następnie zmieniamy typ Wartości
wyjściowej na Wybierz kolumnę (jak dla pierwszej reguły) i wybieramy
kolumnę Mazowieckie. Powinniśmy stworzyć takie reguły dla każdej pojedynczej
osoby. Było by to bardzo czasochłonne. My zbudowaliśmy tu tylko strukturę a
później przez odpowiednie przekształcenie, sprawimy, żeby to działało dla
dowolnego sprzedawcy. W polu W przeciwnym razie wpisujemy wartość null
(oznaczone strzałką na rys. nr 7). Tak ustawione parametry kolumny warunkowej
zatwierdzamy przyciskiem OK.
Otrzymamy
dane z nową kolumną przedstawione na rys. nr 8. W kolumnie Główny region
stworzyliśmy strukturę, zbudowaną za podstawie prostych obliczeń funkcji
JEŻELI.
Rys. nr 8 – Dane z nową kolumną warunkową
W zapisie
formuły kolumny warunkowej w pasku formuły sprzedawca został porównany do
konkretnego sprzedawcy, a naszym celem będzie sprawdzenie czy dany sprzedawca
jest na liście. Podsumowując zastąpimy to porównanie funkcją List.Contains
(rys. nr 9).
Rys. nr 9 – zapis formuły kolumny warunkowej
Funkcja
List.Contains potrzebuje jako pierwszego argumentu listy wszystkich
sprzedawców, listę w Power Query zapisujemy w nawiasach klamrowych. Następnie
sprawdzamy czy w danej liście znajduje się ten konkretny sprzedawca z danego
wiersza, czyli tutaj możemy użyć struktury zbudowanej przez kolumnę warunkową.
Zapis formuły powinien wyglądać następująco:
=Table.AddColumn(#"Zmieniono
typ", "Główny region", each if List.Contains
({"Aleksandra", "Robert"}, [Sprzedawca]) then
[Świętokrzyskie] else if [Sprzedawca]="Dominik" then [Mazowieckie]
else null))
Zmieniliśmy
tylko część formuły – dla pierwszych 2 sprzedawców i po zatwierdzaniu formuły
otrzymamy zmienione dane przedstawione na rys. nr 10.
Rys. nr 10 – zapis funkcji List.Contains dla pierwszej reguły kolumny warunkowej
Ten sposób
jest łatwiejszy, ponieważ bezpośrednio w formule możemy wypisać wszystkich
sprzedawców z danego województwa. Musimy pamiętać, że pobraliśmy drugą tabelę z
danymi (zapytanie tRegiony_k), gdzie mamy listy sprzedawców z podziałem na
województwa. W zapisie formuły możemy się odwołać bezpośrednio do danych z tego
zapytania (do poszczególnych kolumn), zamiast wpisywać ręcznie nazwy
poszczególnych sprzedawców (rys. nr 11). Zapis formuły będzie wtedy wyglądał
następująco:
=Table.AddColumn(#"Zmieniono
typ", "Główny region", each if List.Contains (tRegiony_k
[Świętokrzyskie], [Sprzedawca]) then [Świętokrzyskie] else if
[Sprzedawca]="Dominik" then [Mazowieckie] else null))
Rys nr 11 – odwołanie do kolumny Świętokrzyskie z zapytania tRegiony_k
Po
zatwierdzeniu formuły otrzymamy dane, gdzie każde wystąpienie sprzedawcy z
regionu świętokrzyskiego zostanie rozpoznane. To odwołanie działa dla pierwszej
reguły, którą stworzyliśmy dla kolumny warunkowej. Analogicznie możemy zmienić drugą
regułę – dla regionu Mazowieckiego. Zapis formuły będzie wyglądał następująco:
=Table.AddColumn(#"Zmieniono
typ", "Główny region", each if List.Contains (tRegiony_k
[Świętokrzyskie], [Sprzedawca]) then [Świętokrzyskie] else if List.Contains
(tRegiony_k [Mazowieckie] then [Mazowieckie] else null))
Power Query
przeliczy dane po zatwierdzeniu kolumny i otrzymamy dane przedstawione na rys.
nr 12.
Rys. nr 12 – dane po użyciu w zapisie formuły funkcji List.Contains
W danych
mamy jednego sprzedawcę (Beata), który nie znajduje się na żadnej z list.
Formuła zadziała tak, że otrzymamy dla tego sprzedawcy wartość null. Dzieje się
tak dlatego, że w kolumnie warunkowej wpisaliśmy, że jeśli warunki nie zostaną
spełnione kolumna warunkowa ma nam zwrócić wartość null ( parametr W przeciwnym
razie na rys. nr 7).
Tak
przekształcone dane możemy załadować do Excela za pomocą polecenia Zamknij i
załaduj do z karty Narzędzia
główne (rys. nr 13).
Rys. nr 13 – polecenie Zamknij i załaduj do
Otworzy nam
się okno Importowania danych, gdzie wybieramy sposób wyświetlania danych jako
Tabela i wskazujemy miejsce ich wstawienia – konkretny arkusz oraz wskazujemy
konkretną komórkę. Tak ustawione parametry importowania danych zatwierdzamy
przyciskiem OK (rys. nr 14).
Rys. nr 14 – okno Importowania danych
Otrzymamy
dane zaimportowane do Excela i przedstawione na rys. nr 15.
Rys. nr 15 – Dane zaimportowane do Excela
Jak widać na
rysunku powyżej dane z odpowiednich kolumn są pobierane do kolumny warunkowej
Główny region.
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 zajmiemy się dopasowaniem raty
kredytowej. Pokażemy jak zmieniając parametry (cenę, liczbę rat i stopę
procentową) wyliczyć konkretną wartość raty kredytu. Temat ten omówimy na
przykładowych danych z rysunku nr 1.
rys. nr 1 — Przykładowe dane
Możemy to policzyć za pomocą polecenia Szukaj wyniku z karty
Dane — Analiza warunkowa (rys. nr 2).
rys. nr 2 — Polecenie Szukaj wyniku
Otworzy nam się okno Szukanie wyniku (rys. nr 3). Przyjmujemy,
że chcemy zmienić wartość spłaty, czyli w polu Ustaw komórkę odwołujemy się do
komórki z wysokością raty kredytowej (F4). W polu Wartość wpisujemy wartość
raty jaką chcemy osiągnąć – wartość ta jest ujemna, ponieważ spłata kredytu
wyciąga pieniądze z naszego portfela. Następnie w polu Zmieniając komórkę odwołujemy
się bezwzględnie do komórki z ceną całkowitą ($F$1).
rys. nr 3 — Okno Szukanie wyniku
Zatwierdzamy parametry klikając przycisk OK i otrzymujemy
cenę samochodu jaką możemy spłacić, przy wysokości raty 1500 zł.
Podsumowując przy 24 ratach w wysokości 1500 zł i stopie
procentowej 1,2% możemy pożyczyć z banku 31 119 zł (rys. nr 4).
rys. nr 4 — Kwota jaką możemy spłacić przy 24 ratach po 1500 zł
Aby otworzyć okno Szukanie wyniku możemy użyć skrótu klawiszowego – musimy naciskając po sobie Alt, N, J.
W drugim przykładzie chcemy wyznaczyć ilość rat przy założeniu stopy procentowej na poziomie 1,2 %, wysokości raty 1500 zł oraz wysokości ceny – kwoty jaką chcemy pożyczyć Ustawiamy komórkę G4, następnie jej wartość w kwocie 1500 zł, Zmieniając komórkę $G$2, czyli ilość rat (rys. nr 5).
rys. nr 5 — Okno Szukanie wyniku
Zatwierdzamy przyciskiem OK i otrzymujemy ilość rat (w
zaokrągleniu 28), potrzebnych do spłaty kwoty 35000 zł, przy wysokości raty
1500 zł (rys. nr 6).
rys. nr 6 — Ilość rat do spłacenia przy założeniu długu 35000 zł i racie 1500 zł
Należy pamiętać o wpisywaniu wysokości raty na minusie,
ponieważ wartość dodatnia jest niemożliwa i Excel zwróci nam błąd.
Podsumowując za pomocą polecenia Szukaj wyniku możemy sobie
dopasowywać wysokość kwoty jaką możemy pożyczyć z banku przy założonej
wysokości raty lub policzyć ilość rat o danej wysokości do spłaty konkretnej
kwoty pożyczonej z banku.
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.
