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.
W dzisiejszym poście omówimy kody kreskowe w Excelu. Omówione
rozwiązanie znalazłem na stronie internetowej https://www.myonlinetraininghub.com/create-barcodes-with-excel-vba Z podanej
strony możesz sobie pobrać czcionki i materiały potrzebne do stworzenia kodów
kreskowych. Temat ten omówimy na podstawie przykładowych danych z rysunku nr 1.
rys. nr 1 — Przykładowe dane
W kodach kreskowych są różne implementacje, w tym poście
omówimy kody typu 39 i 128. W kodach każdy znak odpowiada określonej ilości
kresek. Kody te pozwalają na zapis zarówno cyfr jak i liter w postaci kresek.
Podstawą do tworzenia kodów w Excelu są odpowiednie czcionki,
podane na stronie internetowej na początku wpisu (podani są również ich autorzy
ze względu na prawa autorskie). Musimy pobrać te pliki z czcionkami, wypakować
i zainstalować. Są to pliki ze skrótem .ttf. W Windowsie wystarczy kliknąć na
plik prawym przyciskiem myszki i z podręcznego menu wybrać opcję Zainstaluj
(rys. nr 2). Później reset komputera lub chociaż Excela, aby zmiany zostały
wprowadzone i zainstalowana była dostępna do użycia.
rys. nr 2 — Zainstaluj w systemie Windows
Kolejnym krokiem jest pobranie pliku Excela z kodem VBA z
dołu strony. Plik będzie dostępny do pobrania dopiero po podaniu waszego adresu
e‑mail. Ten plik powinien wystarczyć do tworzenia kodów, ponieważ wystarczy, że
napiszesz String, czyli ciąg znaków liczbowych i tekstowych, ustawisz
odpowiednią czcionkę, a funkcja sama stworzy kod odpowiadający tym znakom (rys.
nr 3).
rys. nr 3 — Kod kreskowy
W miejscach oznaczonych strzałkami niebieską i czerwoną są
wyniki funkcji do tworzenia kodów. Strzałką czerwoną oznaczyłem kod kreskowy, a
niebieską znaczki odpowiadające temu kreskowemu. Pokazują się dziwne znaczki bo
nie zmieniliśmy czcionki. Wystarczy, że zmienimy czcionkę na Kod 128 i
otrzymamy właściwy zapis (rys. nr 4).
rys. nr 4 — Zmiana czcionki
Po wybraniu odpowiedniej czcionki otrzymamy kod kreskowy,
następnie trzeba zmienić wielkość czcionki, aby był dobrze widoczny.
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 wpisie omówimy polecenie Przechodzenia do —
specjalnie. Jest to polecenie umieszczone na końcu karty Narzędzia główne (rys.
nr 1).
rys. nr 1 — Polecenie Przechodzenie do — specjalnie
Zagadnienie to omówimy sobie na podstawie przykładowych
danych z rysunku nr 2.
rys. nr 2 — Przykładowe dane
W pierwszej kolejności omówimy sobie polecenie Przejdź do.
Z karty Narzędzia główne wybieramy polecenie Znajdź i zaznacz, a następnie
Przejdź do. Otworzy nam się okno Przechodzenie do, które służy głównie do
przechodzenia do nazwanych zakresów danych. My potrzebujemy bardziej
specjalistycznego podejścia, więc wciskamy przycisk Specjalnie, pokazany na
rysunku nr 3.
rys. nr 3 — Okno Przechodzenie do
Otworzy nam się okno Przechodzenie do – specjalnie. Okno
to możemy otworzyć za pomocą skrótów klawiszowych – Ctrl+G, aby otworzyć okno
Przechodzenie do, a następnie Alt+S, aby przejść do okna Przechodzenie do –
specjalnie (rys. nr 4).
rys. nr 4 — Okno Przechodzenie do — specjalnie
W tym oknie mamy wiele możliwości. Pierwszą opcją są
Komentarze. Jeśli mamy zaznaczona jedną komórkę i skorzystamy z opcji
Komentarze, Excel zaznaczy nam wszystkie komórki w arkuszu, gdzie są
umieszczone jakieś komentarze. Kiedy zaznaczymy zakres danych (przynajmniej 2
komórki) i skorzystamy z tego samego polecenia, Excel zaznaczy nam komórki z
komentarzem tylko w zaznaczonym zakresie.
W oknie Przechodzenie do – specjalnie, możemy wybrać
wartości Stale lub Formuły w danym zakresie, do jednych i drugich obowiązują
checkboxy z rysunku nr 5.
rys. nr 5 — checkboxy dla Formuł i Stałych
W naszym przykładzie chcemy, aby Excel zaznaczył nam
wartości Stałe, będące Liczbami bądź Tekstem, czyli w checkboxie odznaczamy
Logiczne i Błędy. Otrzymujemy dane z rysunku nr 6 z zaznaczonymi tylko
komórkami z wartościami liczbowymi lub tekstem. (skreślone komórki nie
zaznaczone).
rys. nr 6 — Zaznaczone komórki z Liczbami i Tekstem
Zaznaczyliśmy Stałe wartości, więc możemy się domyślić, że
komórki niezaznaczone zawierają formuły.
Możemy wybrać opcje Puste, wtedy Excel zaznaczy nam puste
komórki na zaznaczonym zakresie. Kolejną opcją jest Bieżący obszar w stosunku
do komórki dla której wybraliśmy tą opcję. Bieżący obszar możemy również
zaznaczyć za pomocą skrótów klawiszowych Ctrl+A lub Ctrl+Shift+8.
Kolejnym polecenie jest Bieżąca tablica, w naszym
przykładzie Excel nie znajdzie takich danych, bo nie mamy w naszych danych
formuły tablicowej (rys. nr 7)
rys. nr 7 ‑Komunikat nie znaleziono wyników
Jeśli zaznaczymy jakiś zakres (fragment tablicy, ale aktywna
komórka będzie się znajdować w zakresie tej tablicy), gdzie mamy takie formuły,
to Excel zaznaczy nam całą tablicę. Natomiast jeśli zaznaczymy jakiś zakres
danych, gdzie aktywna komórka będzie znajdować się poza tablicą (mimo iż część
zakresu będzie w tej tablicy) otrzymamy komunikat, że nie znaleziono komórek.
Następną opcją są Obiekty, czyli Excel zaznaczy nam wszystkie
obiekty w arkuszu(rys. nr 8).
rys. nr 8 — Polecenie Zaznaczanie obiektów
Dla mnie lepszą opcją do zaznaczania obiektów jest polecenie
Okienko zaznaczenia (karta Narzędzia główne, polecenie Znajdź i zaznacz) – rys.
nr 9, ponieważ tu mogę jasno kontrolować elementy, które chce zaznaczyć.
rys. nr 10 — Polecenie Znajdź i zaznacz
W naszych danych mamy umieszczony ukryty wiersz. W opcjach
okna Przechodzenie do – specjalnie jest opcja Tylko widoczne komórki, która zaznaczy
nam obszar bez ukrytych komórek. W celu zaznaczenia tylko widocznych komórek
możemy również użyć skrótu klawiszowego Ctrl+;.
Kolejną opcją jest zaznaczenie Różnic w wierszach (rys. nr
10). Analogicznie zadziała polecenie Różnice w kolumnach – otrzymamy zaznaczone
komórki z innym wynikiem. Wtedy łatwo jest np. zmienić im kolor, aby móc
sprawdzić skąd wynika ta różnica.
rys. nr 10 — Polecenie Różnice w wierszach
Kolejną opcją są Poprzedniki, czyli jeśli jakieś formuły
odwołują się do komórek, to te komórki będą zaznaczone.
Następnie mamy polecenie Zależności, czyli w sytuacji jeśli
mamy formułę odwołującą się do innej komórki. W tym przypadku dla mnie
wygodniej jest skorzystać z poleceń na karcie Formuły – Śledź poprzedniki lub
zależności (rys. nr 11). To rozwiązanie jest dla mnie lepsze bo fizycznie
pokazuje te zależności między komórkami, która od której jest zależna.
rys. nr 11 — Polecenie Śledź poprzedniki
Polecenie Ostatnia komórka zaznaczy nam ostatnią używaną
komórkę. Ogólnie rzecz biorąc ta komórka może być pusta, ale jest to komórka
wyznaczona niejako przez przecięcie się ostatniego używanego wiersza z ostatnią
używaną kolumną. Ostatnią komórkę możemy również wyznaczyć za pomocą skrótu
klawiszowego Ctrl+End.
W oknie Przechodzenie do – specjalnie mamy również opcję
Sprawdzanie poprawności danych. Polecenie to wyznaczy nam komórki, których
zadaniem jest sprawdzanie poprawności danych. Zostaną zaznaczone wszystkie
takie komórki na danym zakresie lub takie same – o takim samym sprawdzaniu poprawności danych.
Opcja Formatowanie warunkowe Wszystkie sprawi, że Excel
zaznaczy obszar z formatowaniem warunkowym w całym arkuszu. Łatwiejszym
sposobem dla mnie jest użycie polecenia Zarządzaj regułami z Formatowania
warunkowego na karcie Narzędzia główne (rys. nr 12).
rys. nr 12 — Polecenie Zarządzaj regułami
Wyświetli nam się Meneger reguł formatowania warunkowego, w
którym mamy listę wszystkich komórek z takim formatowaniem w arkuszu (rys. nr 13).
rys. nr 13 — Okno Meneger reguł formatowania warunkowego
Podsumowując Przechodzenie do – specjalnie ułatwia pracę w
Excelu poprzez zaznaczanie komórek, które nas interesują, spełniających jakieś
kryterium.
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 stworzyć rozrastająco
się listę rozwijaną w Excelu. Załóżmy, że mamy listę kilku sprzedawców a chcemy
mieć możliwość dopisania do niej sprzedawcy spoza naszej listy, który
automatycznie zostanie dołączony do listy rozwijanej. Zagadnienie to omówimy na
przykładowych danych z rysunku nr 1.
rys. nr 1 — Przykładowe dane
Przede wszystkim musimy stworzyć sobie listę unikatowych sprzedawców,
aby żaden z nich się nie powtórzył. Aby stworzyć listę unikatowych wartości, używam
funkcji LICZ.JEŻELI. Pierwszym argumentem funkcji jest Zakres, czyli od komórki
powyżej naszej formuły do komórki powyżej naszej formuły. Wpisujemy zakres
$E$1:E1, pierwsza wartość blokujemy bo jest to wartość skrajna a druga ma nam
się rozrastać w dół. Drugim argumentem funkcji są kryteria, czyli wszystkie
wartości z naszej tabeli (wartości z listy sprzedawców).
Zapis funkcji będzie wyglądał następująco: =LICZ.JEŻELI($E$1:E1;tSprzedawcy[Sprzedawca]).
Zatwierdzamy funkcję. Jako początkowy wynik otrzymujemy 0. Możemy sobie rozwinąć ten wynik za pomocą skrótu klawiszowego F9, aby otrzymać ={0;0;0;0;0}. Taki wynik jest następstwem tego, że żaden sprzedawca nie pojawił się w komórce powyżej aktualnej komórki z formułą. Pożądany efekt uzyskamy dodając kolejne kroki, bo chcemy aby nasza formuła zwróciła nam kolejnego sprzedawcę. Musimy skorzystać z funkcji PODAJ.POZYCJĘ, która będzie szukała wartości 0 w zakresie z funkcji LICZ.JEŻELI na zasadzie dokładnego dopasowania. Zapis funkcji będzie wyglądał następująco:
=PODAJ.POZYCJĘ(LICZ.JEŻELI($E$1:E1;tSprzedawcy[Sprzedawca]);0),
czyli funkcja znajdzie pierwszą wartość 0 jaka się pojawi.
Musimy pamiętać, że jest to formuła tablicowa i należy ją
zatwierdzić klawiszami Ctrl+Shift+Enter. Otrzymamy wartość 1, ale nas nie
interesuje cyfra, tylko wartość jaka znajduje się na pierwszej pozycji (nazwa
sprzedawcy). Aby uzyskać taki wynik musimy użyć funkcji INDEKS po kolumnie ze
sprzedawcami. Zapis formuły będzie wyglądał następująco:
=INDEKS(tSprzedawcy[Sprzedawca]PODAJ.POZYCJĘ(LICZ.JEŻELI($E$1:E1;tSprzedawcy[Sprzedawca]);0)),
zatwierdzamy Ctrl+Shift+Enter. Tak przygotowaną formułę przeciągamy w dół (rys.
nr 2).
rys. nr 2 — funkcja INDEKS
Dzięki tej formule otrzymujemy listę sprzedawców bez
duplikatów, nie uwzględnia ona powtarzających się wartości. Robimy ją tak
długą, żeby mieć pewność, że nie zabraknie nam w niej miejsca. Jednak musimy ją
sobie ograniczyć tylko do prawidłowych wartości. Możemy to zrobić za pomocą
funkcji LICZ.JEŻELI. Pierwszym argumentem funkcji jest Zakres, czyli komórki
E2:E13, musimy pamiętać zablokować bezwzględnie zakres za pomocą skrótu
klawiszowego F4. Drugim argumentem to szukane wartości, czyli "?*".
Są to dwa symbole wieloznaczne w Excelu, wykorzystywane między innymi w funkcji
LICZ.JEŻELI. Zapis formuły będzie wyglądał następująco:
=LICZ.JEŻELI($E$2:$E$13; "?*")
Zapis ten oznacza, że szukamy komórki w której pojawi się
przynajmniej jeden z tych znaków. Jak sobie zatwierdzimy formułę, otrzymamy
cyfrę 4 (ilość pozycji spełniających warunek). Funkcja ta nie zwraca uwagę na
wartość 0 i na błędy (rys. nr 3).
rys. nr 3 — Funkcja LICZ.JEŻELI
Listę rozwijaną tworzy się często za pomocą funkcji
PRZESUNIĘCIE, ale ta funkcja jest nietrwała i często się przelicza. My
stworzymy listę rozwijaną za pomocą funkcji INDEKS, która zwraca odwołanie do
zakresu (blokujemy za pomocą klawisza F4) i numer wiersza G2.
Zapis formuły będzie wyglądał następująco:
=$E$2:INDEKS($E$2:$E$13;$G$2).
Funkcja INDEKS zwróci nam ostatnią prawidłową wartość – w
naszym przypadku "Wojciech" (rys. nr 4).
rys. nr 4 — Podejrzenie formuły klawiszem F9
W formule otrzymujemy zakres od komórki E2 do wyniku funkcji
INDEKS, czyli jeśli podejrzymy sobie wyniki formuły za pomocą klawisza F4 to
okazuje się, że wynikiem są nazwy wszystkich unikalnych sprzedawców (rys. nr 5).
rys. nr 5 — Podejrzenie wyników formuły za pomocą klawisza F4
Końcowy zapis formuły będzie wyglądał następująco:
Zastąpiliśmy odwołanie do komórki G2, zapisem formuły, z
której powstał tam wynik. Otrzymaliśmy cały zbudowany zakres. Formułę tą musimy
skopiować w trybie edycji komórki. Korzystając ze skrótu klawiszowego Ctrl+F3
przechodzimy do okna Menedżera nazw (rys. nr 6)
rys. nr 6 — okno Menedżer nazw
Kolejnym krokiem jest stworzenie nowej nazwy. Zmieniamy nazwę
na Sprzedawcy, a następnie w miejscu Odwołuje się do wkleić naszą skopiowaną
formułę (rys. nr 7). Zatwierdzamy klikając OK.
rys. nr 7 — Okno Nowa nazwa
Zamykamy Menedżera nazw. Następnie zaznaczamy kolumnę z
nazwami sprzedawców i wybieramy polecenie Poprawność danych z karty Dane (rys.
nr 8).
rys. nr 8 — polecenie Poprawność danych
Otworzy
nam się okno Sprawdzanie poprawności danych, gdzie jako kryterium poprawności
wybieramy Listę, a jako źródło ustawiamy nazwę Sprzedawcy(rys. nr 9).
rys. nr 9 — Sprawdzanie poprawności danych
Aby wybrać źródło, rozwijamy okno Wklejanie nazwy za pomocą
klawisza F3 i wybieramy nazwę naszego zakresu (rys. nr 10).
rys. nr 10 — Okno Wklejanie nazwy
Zatwierdzamy przyciskiem OK. W efekcie przy każdej komórce z
nazwą sprzedawcy utworzyła nam się lista rozwijana.
rys. nr 11 — Listy rozwijane w każdej komórce
Zapomniałem zrobić jednej ważnej rzeczy przy sprawdzaniu
poprawności danych, mianowicie w karcie Alert o błędzie musimy odznaczyć
Pokazuj alerty po wprowadzeniu nieprawidłowych danych (rys. nr 12). Przypomniałem
sobie o tej funkcjonalności oglądając, któryś z filmów Maliny z malinowego
bloga o Excelu. Musimy pamiętać o tej czynności, ponieważ chcemy mieć możliwość
wprowadzania dodatkowych wartości spoza listy.
rys. nr 12 — Pokazuj alerty po wprowadzeniu nieprawidłowych danych
Teraz kiedy dopiszemy kolejnego sprzedawcę, będzie on
uwzględniony w liście rozwijanej w każdej komórce z nazwami sprzedawców. Możemy
budować sobie listę, wybierać sprzedawców z list i wstawiać ich jeszcze raz lub
dodawać nowych.
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 dzisiejszej poradzie omówimy wyszukiwanie binarne, czyli
wyszukiwanie przybliżone w Excelu. Temat ten omówimy na podstawie przykładowych
danych z rysunku nr 1. Wpis ten jest niejako kontynuacją tematu z poprzedniego
postu, dotyczącego funkcji WYSZUKAJ.PIONOWO z dopasowaniem przybliżonym.
Opisaliśmy wtedy obrazowo jak działa dopasowanie przybliżone.
rys. nr 1 — Przykładowe dane
Dopasowanie przybliżone jest trochę bardziej skomplikowane od
dokładnego, ale działa szybciej. Musimy pamiętać, żeby zawsze mieć posortowane
dane (rosnąco, od najmniejszej do największej lub od A do Z). Kiedy nasze dane
nie będą posortowane to funkcja WYSZUKAJ.PIONOWO z dopasowaniem dokładnym
znajdzie wynik dla Miś (szuka od góry do dołu), ale jeśli mamy tych wyników
mamy bardzo dużą ilość zajmie to sporo czasu. Funkcja WYSZUKAJ.PIONOWO z
dopasowaniem przybliżonym działa dużo szybciej, ale wymogiem jest, aby dane
były posortowane, bo inaczej funkcja może zwrócić nam nieprawidłowy wynik.
Aby dobrze zrozumieć dlaczego może pojawić się nieprawidłowy
wynik opiszemy to na konkretnym przykładzie. Wyszukiwanie binarne można
powiedzieć, że dzieli na pół nasze tablice, listy danych. Załóżmy, że szukamy
Misia, w dopasowaniu przybliżonym nie zaczynamy go szukać od początku tylko od
środka listy danych. W naszym przykładzie na środku jest Kowboj, K jest
mniejsze od M (bo w alfabecie K jest przed M), więc wszystkie wartości powyżej
przestają nas interesować (rys. nr 2).
rys. nr 2 — Działanie dopasowania przybliżonego
Po
pominięciu wyników powyżej zostaje nam mniejsza tabelka, dokładnie Pastereczka,
Dinozaur i Lalka. Teraz na środku znajduje się Dinozaur, D jest mniejsze od M,
więc po raz kolejny pomijamy wszystkie wyniki powyżej Dinozaura (czyli tego
który aktualnie sprawdzamy). Zostaje nam jeden wynik – Lalka i otrzymujemy jako
wynik całej funkcji cenę Lalki (rys. nr 3).
rys. nr 3 — Sposób działania funkcji WYSZUKAJ.PIONOWO z dopasowaniem przybliżonym
Dzieląc tak listy na pół, możemy bardzo szybko znaleźć
szukaną wartość, bo nie musimy sprawdzać niejako wszystkich wyników.
Pamiętajmy, że funkcja WYSZUKAJ.PIONOWO znajduje tylko jedną wartość – pierwszą
na którą trafi.
Podsumowując funkcja WYSZUKAJ.PIONOWO z dopasowaniem przybliżonym
może zwrócić nam nieprawidłowy wynik dla nieposortowanych danych.
W drugim przykładzie mamy posortowane dane. Zaczynamy szukać
od polowy listy i trafiamy akurat na szukaną nazwę, więc nie kontynuujemy tej
drogi z poprzedniego przypadku, bo Miś=Miś (rys. nr 4).
rys. nr 3 — Wyszukiwanie binarne
Należy pamiętać, że Excel robi coś jeszcze przy dopasowaniu
przybliżonym nie kończy wyszukiwania gdy
znajdzie wartość tylko sprawdza dane poniżej, czy nie powtórzyła się szukana
wartość. Jeśli pod spodem jest więcej danych o takiej samej nazwie (Miś), to
Excel zawsze jako ostateczny wynik poda nam wartość przypisaną dla ostatniego
wystąpienia tej nazwy (rys. nr 5), ale takie same wartości muszą być tuż pod
sobą. Jeśli nazwa Miś powtórzy się drugi raz, ale w odstępie kilku nazw to
Excel już jej nie znajdzie.
rys. nr 5 — Ostatnia znaleziona wartość jako stateczny wynik
Analogicznie wygląda sytuacja z liczbami, a nawet jest dużo
prostsza do zrozumienia (rys. nr 6).
rys. nr 6 — Wyszukiwanie z dopasowanie przybliżonym na wartościach liczbowych
Szukamy wartości 62, aby wystawić ocenę. Sprawdzamy wartość w
połowie tabelki, otrzymujemy 70. Widzimy, iż 70>62, więc pomijamy wyniki
poniżej wartości którą rozpatrywaliśmy (rys. nr 7). Skracamy nasze dane do
wartości powyżej 70.
rys. nr 7 — Zasada działania wyszukiwania z dopasowaniem przybliżonym na liczbach
Rozpatrujemy teraz górną połowę wyników, sprawdzamy wartość
50 (w środku danych), wiemy że 50<60, więc pomijamy wartości powyżej 50 w
tabeli. Pozostaje nam jedna wartość 60, w ten sposób otrzymujemy wynik –
Przeciętnie.
Myślę,
że wyjaśniłem wystarczająco temat wyszukiwania binarnego. Jeśli
chcesz poznać bardziej precyzyjnie zasady działania wyszukiwania przybliżonego
w Excelu przeczytaj post na forum Billa Szysz https://www.excelforum.pl/topics1/formula-zwracajaca-tekst-ze-zbioru-komorek-vt51013.htm
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.
