W dzisiejszym poście omówimy funkcje WYSZUKAJ.PIONOWO zwracającą
wszystkie wartości spełniające dane warunki oraz POŁĄCZ.TEKSTY. Zagadnienie to
omówimy na podstawie przykładowych danych z rysunku nr 1.
rys. nr 1 — Przykładowe dane
W filmie Excel VBA odcinek 12 ( https://www.youtube.com/watch?v=nUhq-noDd2k )
pokazałem jak samodzielnie napisać funkcje, która znajdzie wszystkich
sprzedawców z np. województwa świętokrzyskiego (rys. nr 2).
rys. nr 2 — Szukanie sprzedawców według województwa
Od kiedy udało mi się zainstalować licencję 365 Microsoft MVP
mam dostęp do funkcji POŁĄCZ.TEKSTY, dzięki której możemy znacznie szybciej przypisać
sprzedawców do konkretnych województw. Aby dobrze zrozumieć jak działa ta
funkcja omówimy sobie jej poszczególne argumenty. Zapis funkcji będzie wyglądał
następująco:
=POŁĄCZ.TEKSTY(", ";FAŁSZ;D2:D8)
Pierwszym argumentem funkcji jest Ogranicznik, w naszym przypadku będzie to przecinek i spacja (", "), czyli sposób rozdzielenia wyników. Kolejnym argumentem jest Ignoruj_puste, czyli PRAWDA (ignoruj puste komórki) lub FAŁSZ (uwzględnij puste komórki). W naszym przykładzie wybierzemy FAŁSZ (rys. nr 3).
rys. nr 3 — Drugi argument funkcji
Następnym argumentem jest Tekst_1, czyli tekst lub zakresy tekstu, które chcemy połączyć. W naszym przykładzie zaznaczymy sobie nazwy województw i dwie komórki poniżej, żeby zobaczyć zachowanie funkcji dla pustych komórek (zakres D2:D8). Wybraliśmy jako drugi argument FAŁSZ, dlatego na końcu mamy uwzględnione puste komórki (rys. nr 4).
rys. nr 4 — Uwzględnione puste komórki w wyniku funkcji POŁĄCZ.TEKSTY
W większości sytuacji chcemy ignorować puste komórki
(wpisujemy PRAWDA lub 1), dlatego nasza formuła powinna wyglądać następująco:
=POŁĄCZ.TEKSTY(", ";PRAWDA;D2:D8)
Kiedy zatwierdzimy tak wpisaną formułę nie mamy już
dodatkowych przecinków i pustych miejsc w wynikach.
Teraz kiedy wiemy jak działa funkcja POŁĄCZ.TEKSTY, musimy
podać funkcji właściwe zakresy dane. Będziemy do tego potrzebować formuły tablicowej
– funkcji JEŻELI, która najpierw sprawdzi nam kryteria. Naszym kryterium jest
województwo, czyli zaznaczamy zakres $A$2:$A$18 i porównujemy ten zakres do
województwa które nas interesuje w tym momencie, czyli do komórki D2. Jeżeli
danym wierszu jest wartość która nas interesuje, to chcemy otrzymać sprzedawcę,
czyli jako drugi argument funkcji JEŻELI zaznaczamy zakres ze sprzedawcami
($B$$:$B$18). Natomiast w sytuacji kiedy nasz warunek nie jest spełniony chcemy
otrzymać pusty ciąg tekstowy, czyli jako trzeci argument wpisujemy nic ("").
Zapis funkcji będzie wyglądał następująco:
Jeśli podejrzymy sobie wynik funkcji JEŻELI za pomocą
klawisza F9 w trybie edycji komórki zobaczymy następujące wyniki (rys. nr 5)
rys. nr 5 — Podgląd wyników w trybie edycji komórki
Otrzymamy kilku sprzedawców dla województwa świętokrzyskiego
i puste ciągi tekstowe dla pozostałych województw. Pamiętamy, że cyfra 1 w
funkcji POŁĄCZ.TEKSTY ignoruje te puste miejsca, więc jako wynik otrzymamy
tylko imiona sprzedawców. Zatwierdzamy formułę i otrzymujemy błąd- za dużo
sprzedawców (rys. nr 6).
rys. nr 6 — Błędny wynik funkcji
Błąd ten wynika z tego iż funkcja JEŻELI jest formułą
tablicowa i żeby zadziałała poprawnie należy ją zatwierdzić używając skrótu
klawiszowego Ctrl+Shift+Enter (rys. nr 7).
rys. nr 7 — Poprawny wynik funkcji tablicowej
Przeciągamy formułę w dół i otrzymujemy wyniki dla wszystkich województw (rys. nr 8 ).
rys. nr 8 — Wynik działania funkcji dla całej tabeli
Podsumowując skorzystanie z funkcji JEŻELI i POŁĄCZ.TEKSTY jest
dużo prostsze niż pisanie kodów w VBA. Musimy jednak pamiętać o prawidłowych
zakresach danych i o zatwierdzaniu formuł tablicowych skrótem klawiszowym Ctrl+Shift+Enter.
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 zajmiemy się Polecanymi wykresami z karty Wstawianie. Opcję tą omówimy na podstawie przykładowych danych z rysunku nr 1.
rys. nr 1 — Przykładowe dane
Polecenie to znajduje się na karcie Wstawianie (rys. nr 2)
rys. nr 2 — Polecane wykresy
Udało mi się zainstalować licencję Microsoft MVP 365 i teraz
mam dostępne wszystkie najnowsze dodatki (w tym nowe wykresy), funkcjonalności
do Excela, zmieniła się również wstążka.
Mamy podstawowe dane, które chcielibyśmy przedstawić na wykresie. Kiedy klikniemy na polecenie Polecane wykresy, otworzy nam się okno Wstawianie wykresu (rys. nr 3)
rys. nr 3 — Okno wstawianie wykresu
Ciekawe jest to, że od razu możemy zobaczyć jak nasze dane
będą się prezentować w zależności od wybranego wykresu (kolumnowe, skumulowane,
słupkowe, lejkowe itp.). Wybierzmy wykres liniowy, Zatwierdzamy klikając
przycisk OK. i otrzymujemy wykres z naszymi danymi (rys. nr 4).
rys. nr 4 — Wykres liniowy
Omówimy
kilka rodzajów wykresów w zależności od typów danych, jakie chcemy
zaprezentować. Mamy przykładowe dane z liczbą mieszkańców w kilku województwach
Polski. Przy takich danych Excel sam wykrył, że w danych są nazwy województw i
daje nam możliwość (w Polecanych wykresach) przedstawienia ich na wykresie
Kartogram (rys. nr 5).
rys. nr 5 — Kartogram
My wybierzemy wykres kołowy i nasze dane zaprezentują się
następująco (rys. nr 6).
rys. nr 6 — Wykres kołowy
Jeśli w danych mamy wiersz z Sumą, to polecane wykresy będą
nieprawidłowe, ponieważ ten wiersz wstawią jako część danych do wykresu (rys.
nr 7).
rys nr 7 — Błędne wykresy z SUMĄ
W tej sytuacji musimy zaznaczyć konkretny obszar (bez wiersza
z sumą) i wtedy w poleceniu Polecane wykresy otrzymamy propozycje prawidłowych
wykresów. Aby pokazać jak najwięcej wykresów tym razem wybierzemy sobie Wykres
Pareto (rys. nr 8)
rys. nr 8 — Wykres Pareto
Kolejnym omówionym wykresem będzie histogram. Mamy w danych
wyniki z egzaminu. Przed Excelem 2013 musielibyśmy pogrupować nasze dane a teraz
korzystając z polecenia Polecane wykresy Excel sam przedstawi nam odpowiednio
wyniki na wykresie histogramu (rys. nr 9).
rys. nr 9 — Histogram
Dzięki opcji Polecane wykresy, nie musimy się zastanawiać,
który wykres najlepiej pasuje do naszych danych. Excel sam nam podpowie i
pokaże jak niektóre wykresy będą wyglądać.
Polecane wykresy są bardziej inteligentne niż zwykłe
wstawiane wykresy. Jeśli zaznaczymy jedną komórkę i wstawimy wykres liniowy
ręcznie, Excel pokaże nam za dużo serii danych (lata), wykres będzie mało
czytelny. Kiedy przy zaznaczeniu tej samej komórki użyjemy Polecanych wykresów,
to lata zostaną rozpoznane jako nazwa kategorii a serią będą kwoty sprzedaży
(rys. nr 10).
rys. nr 10 — Wykres liniowy
Kolejnym omówionym przykładem będzie wykres X‑Y. Chcemy
przedstawić jak ilość godzin nauki wpływa na wynik egzaminu i według polecenia
Polecane wykresy najodpowiedniejszy do tego będzie wykres punktowy (rys. nr 11)
rys. nr 11 — Wykres punktowy
Na koniec omówimy wykres pomocniczy, gdzie jedna wartości z
danych jest dużo mniejsza od pozostałych. Przy takich danych Excel poleci nam
wykres Kombi, gdzie główne dane (większe wartości) będą przedstawione na
wykresie kolumnowym, a pomocnicze dane (mniejsze wartości) będą przedstawione
na wykresie liniowym na osi pomocniczej (rys. nr 12).
rys. nr 12 — Wykres Kombi
Podsumowując polecenie Polecane wykresy sprawdza się
doskonale do wstawiania podstawowych wykresów, ponadto podpowiada nam, który
wykres najlepiej będzie prezentował nasze dane. Funkcjonalność ta jest świetnym
rozwiązaniem oszczędzającym nasz czas.
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 zajmiemy się czasem wykonywania procedur i
optymalizacją formuł. Zagadnienie to omówimy na przykładowych danych z rysunku
nr 1.
rys. nr 1 — Przykładowe dane
Pod poradą nr 348, która dotyczyła znalezienia pierwszego
wiersza spełniającego warunek, odbyłem dyskusję z Billem Szysz na temat formuł
i czasu ich wykonywania. Funkcja, z której ja często korzystam (LICZ.JEŻELI)
jest pamięciożerna, czyli obciąża nam procesor. Podobnie działa funkcja
PODAJ.POZYCJĘ , która dla większych zakresów danych działa szybciej.
W filmie Excel sztuczka nr 3 ( https://www.youtube.com/watch?v=CcT51qv8_IA ) pokazałem
jak wykorzystać kod VBA, można go podpiąć i sprawić, aby wykonywał obliczenia –
sprawdzał jak długo dane formuły się liczą.
Zaznaczamy formuły, których chcemy policzyć czas trwania przy
użyciu skrótu klawiszowego Ctrl+A i klikam w makro (rys. nr 2).
rys. nr 2 — Makro
Pojawia nam się wynik obliczeń (rys. nr 3), czyli obliczenia
zostały wykonane w czasie 1,82269 s. Trzeba użyć tego makra kilka razy, aby
sprawdzić średnia z obliczeń, ponieważ czas obliczeń zależy od tego jak jest
obciążony procesor (np. ile mamy uruchomionych programów w danym momencie)
rys. nr 3 — Czas obliczeń funkcji LICZ.JEŻELI
Według moich obliczeń formuła Billa Szysz przelicza się
dłużej (rys. nr 4), bo prawie 5 sekund. Możliwe, że Formuła Billa Szysz jest
szybsza przy jeszcze większej ilości danych.
rys. nr 4 — Czas obliczeń funkcji PODAJ.POZYCJĘ
Bill Szysz zaproponował inne podejście do obliczeń. My tak
naprawdę tylko raz musimy znaleźć ten pierwszy wiersz spełniający warunek.
Kiedy już mamy numer wiersza, to możemy cała skomplikowaną formułę zastąpić
prostą funkcją WIERSZ. W tej funkcji nie podajemy żadnych argumentów tylko
przyrównujemy ją do komórki z wynikiem którego szukamy. Gdy zaznaczymy kolumny
z formuła funkcji WIERSZ otrzymamy wynik 0,005 sekundy, czyli funkcja zwróciła
wynik w tak krótkim czasie (rys. nr 5).
rys. nr 5 — Czas obliczeń funkcji WIERSZ
Podsumowując, nie musieliśmy formuły obliczać 10 tysięcy
razy, wystarczyło policzyć raz, a potem za pomocą funkcji WIERSZ porównać ją z
tym pojedynczym wynikiem. Diametralna zmiana długości czasu wynika, ze zmiany
podejścia do obliczeń. Złota zasada Excela polega na tym, że jeśli potrzebujemy
coś przeliczyć wiele razy ale zawsze jest to ten sam wynik (w naszej sytuacji
ten sam wiersz), to zróbmy to w jednej komórce, a następnie w innej formule
skorzystajmy z tego wyniku.
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 operacje na datach przed rokiem
1900. Zagadnienie to omówimy na przykładowych danych z rysunku nr 1.
rys. nr 1 — Przykładowe dane
John Walkenbach napisał funkcje bazującą na kodach VBA, które
potrafią sobie poradzić z datami przed rokiem 1900. VBA obsługuje daty od roku
100 do 9999. Korzystając z funkcji Johna musimy pamiętać, że pisał je w
angielskim Excelu, więc w funkcjach ogranicznikiem jest przecinek (rys. nr 2).
W polskim Excelu oddzielamy argumenty funkcji znakiem ;.
rys. nr 2 — Typy funkcji obliczeń na datach przed rokiem 1900
Pliki Johna Walkenbacha były dostępne na stronie https://spreadsheetpage.com/index.php/tip/extended_date_functions/ niestety nie ma ich już pod tym adresem, ani nie udało mi się ich znaleźć w internecie, dlatego z godnie z tym jak zrozumiałem licencję udostępniam je za darmo do pobrania na mojej stronie w katalogu XDATEhttps://www.exceliadam.pl/youtube. Ważne jest aby zapamiętać jego lokalizacje (lub zapisać). W Excelu przechodzimy do karty Plik, a następnie Opcje. Otworzy nam się okno Opcje programu Excel. Wybieramy zakładkę Dodatki, upewniamy się że w okienku Zarządzaj mamy Dodatki programu Excel, a następnie wciskamy przycisk Przejdź (rys. nr 3).
rys. nr 3 — Opcje programu Excel
Pojawi nam się okno Dodatki. Wciskamy przycisk Przeglądaj i
musimy znaleźć miejsce, gdzie rozpakowaliśmy pobrane wcześniej pliki. Wybieramy
plik xdate.xla i klikamy przycisk Otwórz. W oknie Dodatki pojawi nam się nasz
dodatek, zaznaczamy checkbox przy tym dodatku (Extended Date Functions) i
zatwierdzamy klikając przycisk OK (rys. nr 4).
rys. nr 4 — Okno Dodatki
Dzięki uruchomieniu tego dodatku, kiedy w komórce zaczniemy
pisać nazwę funkcji zaczynającą się od XD pojawią nam się funkcje Johna Walkenbacha.
Przy użyciu tych funkcji możemy sobie tworzyć daty. Moglibyśmy je stworzyć
wpisując je ręcznie, ale dzięki funkcji XDATE, kiedy po wpisaniu na końcu ciągu
trzech liter ddd, otrzymamy skrócony dzień tygodnia, jakiemu odpowiada ten
dzień. Przy wpisaniu ręcznym nie dałoby się sprawdzić jaki to był dzień
tygodnia.
Funkcja XDATEDIF podaje nam różnice dni, czyli ile dni jest
między danymi datami (rys. nr 5). Aby poprawnie działała musi mieć datę
zapisaną w formacie rr-mm-dd, nie może mieć żadnych dodatków, np. dnia
tygodnia.
rys. nr 5 — Odwołania do dat w formacie rr-mm-dd
Na stronie podanej na początku wpisu możemy doczytać jakie są
te funkcje, jakie maja argumenty i jakie wartości zwracają. Funkcje te działają
na bazie kodu VBA.
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 nauczymy się czym są funkcje nietrwałe,
ulotne w Excelu. Bill Szysz często wypowiada się, żeby nie korzystać z tych
funkcji. Są to przede wszystkim takie funkcji jak: PRZESUNIĘCIE, TERAZ, DZIŚ,
LOS, LOS.ZAKR, ADD.POŚR, KOMÓRKA – w zależności od argumentów, INFO,
SUMA.JEŻELI przy nierównych zakresach. Wadą tych funkcji jest to, że bardzo
często się przeliczają i a to powoduje duże obciążenie procesora. Temat ten omówimy
na podstawie przykładowych danych z rysunku nr 1.
rys. nr 1 — Przykładowe dane
Ogólnie jeśli parametry funkcji się nie zmieniają i wartości
w komórkach, do których się odwołuje pozostają bez zmian, to taka funkcja się
nie przelicza ponownie. Natomiast funkcje wymienione powyżej przeliczają się za
każdym razem, kiedy naciśniemy klawisz F9. W tym wpisie opieram się na
informacjach ze strony internetowej https://www.decisionmodels.com/calcsecretsi.htm Znajdziemy
na tej stronie plik do pobrania (volatileFuncs.zip), który pozwoli ci
przetestować tą ulotność wyżej wymienionych funkcji.
Funkcja INDEKS nie będzie się przeliczać, jeśli jej argumenty
się nie zmienią. Jest funkcją stałą. A reszta funkcji z naszych przykładowych funkcji
to funkcje nietrwałe. Użycie funkcji nietrwałej i jej ponowne przeliczenie
spowoduje zmiany w całym arkuszu, jeśli dalej będziemy mieć odwołania do
komórki z wynikiem z tej funkcji.
Przykładowo w komórce C12 mamy wynik z funkcji nietrwałej. W
komórce niżej mamy formułę VBA, która odwołuje się do wyniku z funkcji
nietrwałej (rys. nr 2).
rys. nr 2 — Formuła VBA zliczająca ilość przeliczeń
Aby zobaczyć jak działa funkcji VBA z komórki C13 musimy użyć
skrótu klawiszowego Alt+F11. W danych mamy jedną modułową zmienną i dodajemy do
niej liczbę 1 za każdym razem kiedy uruchomimy komputer (rys. nr 3).
rys. nr 3 — Okno formuły VBA
Zwracamy wynik z dodatkowymi obliczeniami, aby na pewno te
wartości się nie zmieniły. Użyto tutaj operacji przyrównania (kiedy w VBA
przyrównuje się dwie takie same wartości to zwraca wartość ‑1), więc aby
otrzymać wynik bez zmian trzeba dodać 1. Na końcu mamy linijkę, która (skrót
klawiszowy Ctrl+G) w oknie Immediate wypisuje informacje, w której komórce była
uruchamiana ta funkcja. Można uruchomić okno Immediate za pomocą skrótu
klawiszowego Ctrl+G lub z karty View wybrać opcję Immediate Window (rys. nr 4).
rys. nr 4 — Immediate window
W naszych przykładowych danych kolorem żółtym oznaczono
funkcje nietrwałe. Kiedy naciśniemy klawisz F9, aby ponownie przeliczyć arkusz
to wszystkie funkcje oznaczone kolorem żółtym zmienią wynik.
Podsumowując funkcje nietrwałe przeliczają się za każdym
razem kiedy coś wpiszemy, np. wpiszemy w dowolną komórkę słowo Tekst i
zatwierdzimy Enterem, to funkcje nietrwałe się przeliczą. Zrobią to przy każdej
zmianie danych, nawet takich do których one nie mają odwołania. Każde nasze
działanie w Excelu spowoduje ich ponowne przeliczenie.
Ciekawostką jest, że funkcja SUMA.JEŻELI również może być
nietrwała ale tylko w sytuacji kiedy zakresy, do których się odwołujemy nie są
ze sobą tożsame – nie są tej samej długości (rys. nr 5).
rys. nr 5 — Różne zakresy dla funkcji LICZ.JEŻELI
W naszym przykładzie argument Zakres ma pięć komórek, a drugi
argument po którym sumujemy ma tylko jedną komórkę.
Dodatkowo pamiętajmy, że każda formuła, która odwołuje się do
funkcji ulotnej, będzie przeliczana, ponieważ komórka do której się odwołuje
uległa zmianie.
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.
