Excel — WYSZUKAJ.PIONOWO wszystkie wartości — funkcja POŁĄCZ.TEKSTY — porada 355

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:

=POŁĄCZ.TEKSTY(", ";1;JEŻELI($A$2:$A$18=D2; $B$$:$B$18;""))

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.


Właśnie dodałem mój kurs o Power BI Desktop firmy Microsoft na Udemy.com.
W związku z tym, możesz dostać ten kurs w promocyjnej Cenie Na Start za zaledwie 34,99 PLN.
To najniższa cena jaką mogę ustawić na platformie edukacyjnej Udemy!

Kurs Power BI Desktop to:
- Ponad 6 godziny nagrań wideo, które krok po kroku wprowadzają Cię w tajniki pobierania, łączenia i analizy danych, a na koniec ich wizualizacji.
- Pliki do pracy razem z filmami.
- Dożywotni dostęp.
- Elektroniczny certyfikat ukończenia

Spis treści kursu o PowerBI Desktop:

Kurs jest podzielony na 6 rozdziałów, które pozwolą Ci wejść w tematykę analizy i wizualizacji danych za pomocą odpowiednio stworzonych zapytań i relacji w PowerBI Desktop.

  1. Wstęp do aplikacji PowerBI Desktop i jej możliwości
  2. Tworzenie i modyfikowanie zapytań (pobieranie danych)
  3. Modelowanie danych w PowerBI Desktop
  4. Wizualizacja danych i tworzenie raportów
  5. Usługa internetowa
  6. PowerBI Pro — kilka słów o płatnej części usługi PowerBI

Wejdź na stronę kursu PowerBI Desktop i zobacz szczegóły kursu
oraz udostępnione do podglądu filmy,
żeby przekonać się czy to kurs dla Ciebie.

Excel — Polecane wykresy — polecenie na karcie Wstawianie — porada 354

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.


Właśnie dodałem mój kurs o Power BI Desktop firmy Microsoft na Udemy.com.
W związku z tym, możesz dostać ten kurs w promocyjnej Cenie Na Start za zaledwie 34,99 PLN.
To najniższa cena jaką mogę ustawić na platformie edukacyjnej Udemy!

Kurs Power BI Desktop to:
- Ponad 6 godziny nagrań wideo, które krok po kroku wprowadzają Cię w tajniki pobierania, łączenia i analizy danych, a na koniec ich wizualizacji.
- Pliki do pracy razem z filmami.
- Dożywotni dostęp.
- Elektroniczny certyfikat ukończenia

Spis treści kursu o PowerBI Desktop:

Kurs jest podzielony na 6 rozdziałów, które pozwolą Ci wejść w tematykę analizy i wizualizacji danych za pomocą odpowiednio stworzonych zapytań i relacji w PowerBI Desktop.

  1. Wstęp do aplikacji PowerBI Desktop i jej możliwości
  2. Tworzenie i modyfikowanie zapytań (pobieranie danych)
  3. Modelowanie danych w PowerBI Desktop
  4. Wizualizacja danych i tworzenie raportów
  5. Usługa internetowa
  6. PowerBI Pro — kilka słów o płatnej części usługi PowerBI

Wejdź na stronę kursu PowerBI Desktop i zobacz szczegóły kursu
oraz udostępnione do podglądu filmy,
żeby przekonać się czy to kurs dla Ciebie.

Excel — Czas wykonywania funkcji i optymalizacja formuł — porada 353

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.


Właśnie dodałem mój kurs o Power BI Desktop firmy Microsoft na Udemy.com.
W związku z tym, możesz dostać ten kurs w promocyjnej Cenie Na Start za zaledwie 34,99 PLN.
To najniższa cena jaką mogę ustawić na platformie edukacyjnej Udemy!

Kurs Power BI Desktop to:
- Ponad 6 godziny nagrań wideo, które krok po kroku wprowadzają Cię w tajniki pobierania, łączenia i analizy danych, a na koniec ich wizualizacji.
- Pliki do pracy razem z filmami.
- Dożywotni dostęp.
- Elektroniczny certyfikat ukończenia

Spis treści kursu o PowerBI Desktop:

Kurs jest podzielony na 6 rozdziałów, które pozwolą Ci wejść w tematykę analizy i wizualizacji danych za pomocą odpowiednio stworzonych zapytań i relacji w PowerBI Desktop.

  1. Wstęp do aplikacji PowerBI Desktop i jej możliwości
  2. Tworzenie i modyfikowanie zapytań (pobieranie danych)
  3. Modelowanie danych w PowerBI Desktop
  4. Wizualizacja danych i tworzenie raportów
  5. Usługa internetowa
  6. PowerBI Pro — kilka słów o płatnej części usługi PowerBI

Wejdź na stronę kursu PowerBI Desktop i zobacz szczegóły kursu
oraz udostępnione do podglądu filmy,
żeby przekonać się czy to kurs dla Ciebie.

Excel — Operacje na datach przed 1900 rokiem — porada 351

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

Aby mieć możliwość skorzystania z funkcji Johna Walkenbacha musimy je sobie pobrać ze strony http://spreadsheetpage.com/index.php/tip/extended_date_functions/ i rozpakować. 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.


Właśnie dodałem mój kurs o Power BI Desktop firmy Microsoft na Udemy.com.
W związku z tym, możesz dostać ten kurs w promocyjnej Cenie Na Start za zaledwie 34,99 PLN.
To najniższa cena jaką mogę ustawić na platformie edukacyjnej Udemy!

Kurs Power BI Desktop to:
- Ponad 6 godziny nagrań wideo, które krok po kroku wprowadzają Cię w tajniki pobierania, łączenia i analizy danych, a na koniec ich wizualizacji.
- Pliki do pracy razem z filmami.
- Dożywotni dostęp.
- Elektroniczny certyfikat ukończenia

Spis treści kursu o PowerBI Desktop:

Kurs jest podzielony na 6 rozdziałów, które pozwolą Ci wejść w tematykę analizy i wizualizacji danych za pomocą odpowiednio stworzonych zapytań i relacji w PowerBI Desktop.

  1. Wstęp do aplikacji PowerBI Desktop i jej możliwości
  2. Tworzenie i modyfikowanie zapytań (pobieranie danych)
  3. Modelowanie danych w PowerBI Desktop
  4. Wizualizacja danych i tworzenie raportów
  5. Usługa internetowa
  6. PowerBI Pro — kilka słów o płatnej części usługi PowerBI

Wejdź na stronę kursu PowerBI Desktop i zobacz szczegóły kursu
oraz udostępnione do podglądu filmy,
żeby przekonać się czy to kurs dla Ciebie.

Excel — Funkcje nietrwałe ulotne (Volatile) — porada 332

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 http://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.


Właśnie dodałem mój kurs o Power BI Desktop firmy Microsoft na Udemy.com.
W związku z tym, możesz dostać ten kurs w promocyjnej Cenie Na Start za zaledwie 34,99 PLN.
To najniższa cena jaką mogę ustawić na platformie edukacyjnej Udemy!

Kurs Power BI Desktop to:
- Ponad 6 godziny nagrań wideo, które krok po kroku wprowadzają Cię w tajniki pobierania, łączenia i analizy danych, a na koniec ich wizualizacji.
- Pliki do pracy razem z filmami.
- Dożywotni dostęp.
- Elektroniczny certyfikat ukończenia

Spis treści kursu o PowerBI Desktop:

Kurs jest podzielony na 6 rozdziałów, które pozwolą Ci wejść w tematykę analizy i wizualizacji danych za pomocą odpowiednio stworzonych zapytań i relacji w PowerBI Desktop.

  1. Wstęp do aplikacji PowerBI Desktop i jej możliwości
  2. Tworzenie i modyfikowanie zapytań (pobieranie danych)
  3. Modelowanie danych w PowerBI Desktop
  4. Wizualizacja danych i tworzenie raportów
  5. Usługa internetowa
  6. PowerBI Pro — kilka słów o płatnej części usługi PowerBI

Wejdź na stronę kursu PowerBI Desktop i zobacz szczegóły kursu
oraz udostępnione do podglądu filmy,
żeby przekonać się czy to kurs dla Ciebie.

Excel — Dopasowanie raty kredytowej — polecenie Szukaj wyniku — porada 352

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.


Właśnie dodałem mój kurs o Power BI Desktop firmy Microsoft na Udemy.com.
W związku z tym, możesz dostać ten kurs w promocyjnej Cenie Na Start za zaledwie 34,99 PLN.
To najniższa cena jaką mogę ustawić na platformie edukacyjnej Udemy!

Kurs Power BI Desktop to:
- Ponad 6 godziny nagrań wideo, które krok po kroku wprowadzają Cię w tajniki pobierania, łączenia i analizy danych, a na koniec ich wizualizacji.
- Pliki do pracy razem z filmami.
- Dożywotni dostęp.
- Elektroniczny certyfikat ukończenia

Spis treści kursu o PowerBI Desktop:

Kurs jest podzielony na 6 rozdziałów, które pozwolą Ci wejść w tematykę analizy i wizualizacji danych za pomocą odpowiednio stworzonych zapytań i relacji w PowerBI Desktop.

  1. Wstęp do aplikacji PowerBI Desktop i jej możliwości
  2. Tworzenie i modyfikowanie zapytań (pobieranie danych)
  3. Modelowanie danych w PowerBI Desktop
  4. Wizualizacja danych i tworzenie raportów
  5. Usługa internetowa
  6. PowerBI Pro — kilka słów o płatnej części usługi PowerBI

Wejdź na stronę kursu PowerBI Desktop i zobacz szczegóły kursu
oraz udostępnione do podglądu filmy,
żeby przekonać się czy to kurs dla Ciebie.