Lista rozwijalna

Lista rozwijalna to często stosowany sposób na wymuszenie możliwości wpisywania do komórki tylko konkretnych wartości. Jest też bardzo praktyczna, gdyż wystarczy kliknięcie na jeden z elementów listy, aby wprowadzić ją do komórki.
Efekt ten uzyskamy poprzez zastosowanie opcji Poprawność danych, dostępnej na karcie Dane.

W formularzu, który się otworzy, wybieramy Lista:

Definicję wartości, które mają się pojawić na liście wpisujemy w pole Źródło.
Jest tu kilka sposobów:

    • bezpośredni wpis poszczególnych elementów listy, oddzielonych średnikami. Do zastosowania w przypadkach, gdy wybór ma tylko kilka wartości
    • wpisanie zakresu komórek, w których są wartości mające się pokazać jako elementy listy
      lista rozwijalna
    • odwołanie do zdefiniowanej nazwy zakresu (zakres musi mieć szerokość 1 kolumny, nie może mieć więcej)
    • odwołanie do zakresu dynamicznego (ilość elementów zmienia się w zależności od niepustych komórek w zaznaczonej kolumnie). W polu Źródło wpisujemy funkcję:
      =PRZESUNIĘCIE(Arkusz3!$A$1;;;ILE.NIEPUSTYCH(Arkusz3!$A:$A);1)
      – oczywiście dostosowując nazwę arkusza i kolumnę z danymi do własnych potrzeb.

Nie pamiętam jak to było w Excelu 2007, ale od wersji 2010 źródło listy rozwijalnej może być w innym arkuszu niż ten, w którym znajduje się lista. Przy bardziej rozbudowanych projektach i różnych listach warto umieścić je w osobnym arkuszu. Może być ukryty.

 

Zakres dynamiczny

Zasady tworzenia nazwanych zakresów komórek opisałam w notce:
Nazwa zakresu
Tak zdefiniowane zakresy są jednak sztywne, zapisane na stałe.  Chcąc je rozszerzyć – trzeba wprowadzić zmiany, edytując odwołanie nazwy.
Nazwy można jednak także opisywać formułą automatycznie zmieniającą zakres wchodzących w jej skład komórek.

Załóżmy, że mamy tabelkę jak na screenie:

Jak zdefiniować taki zakres, który automatycznie zmieni się po dopisaniu wartości w kolejnych komórkach?  Można to zrobić poprzez taką formułę:
=PRZESUNIĘCIE(Arkusz3!$A$1;;;ILE.NIEPUSTYCH(Arkusz3!$A:$A);1)

Tak zdefiniowany zakres będzie się automatycznie zmieniać, gdy dopiszemy kolejne wiersze. W wyniku działania funkcji – wysokość zakresu jest określona  przez liczbę niepustych komórek w kolumnie A.
Bardzo przydatne narzędzie m.in. w liście wybieralnej.

 

 

Nazwa zakresu

Gdy często odwołujemy się do jakiegoś określonego zakresu komórek, warto jest zdefiniować i zapisać pod jakąś nazwą. Służy do tego grupa opcji Nazwy zdefiniowane na karcie Formuły.

Możemy zrobić to na kilka sposobów:

  • zaznaczamy zakres komórek, któremu chcemy przypisać nazwę, klikamy na przycisk Definiuj nazwę i wypełniamy formularz:
    Nadajemy unikalną nazwę, ewentualnie wpisujemy komentarz. Pamiętajmy o ustawieniu prawidłowego zakresu – dana nazwa może być przypisana do całego skoroszytu lub do konkretnego arkusza. Jeżeli ustawimy Zakres na konkretny arkusz – w innych arkuszach ta nazwa nie będzie widziana i nie będzie można się do niej odwoływać. Domyślną opcją jest Skoroszyt.
  • klikamy w przycisk Menedżer nazw, a następnie Nowy…
    Dalsze kroki – jak w poprzednim przykładzie
  • zaznaczamy zakres komórek, w którym znajdują się także planowane nazwy zakresów i naciskamy przycisk Utwórz z zaznaczenia:
    W ten sposób zostały utworzone 2 zakresy o nazwie Dziewczynki i Chłopcy.

 

Przegląd i zakres poszczególnych nazw można sprawdzić w Menedżerze nazw.
Można też je usuwać czy edytować.
A sam Menedżer nazw służy nie tylko do definiowania zakresów, ale także np. do tworzenia własnych formuł – ale to już temat na oddzielną notkę.

 

 

Kurs Excel dla sprzedawców

 

Suma.Warunków

Suma.Warunków to  funkcja matematyczna Excela. W wyniku jej działania otrzymujemy sumę wartości komórek spełniających określone kryteria wyszukiwania. Jest to w zasadzie rozszerzenie funkcji Suma.Jeżeli – działa na tej samej zasadzie, ale wprowadza możliwość wprowadzenia większej ilości warunków wyszukiwania.
Argumenty funkcji to:

    • Suma_Zakres – zakres komórek, które mają zostać zsumowane, argument wymagany
    • Kryteria_zakres1 – zakres komórek, w których muszą  być spełnione kryteria1,
      argument wymagany
    • Kryteria1 – warunek do spełnienia dla komórek z Kryteria_zakres1,
      argument wymagany

Argumentami opcjonalnymi są kolejne pary Kryteria_zakres2 i Kryteria2, Kryteria_zakres3 i Kryteria3…. itd.
Takich par można wstawić 127.
Warto zwrócić uwagę, że wszystkie te kryteria dla poszczególnych zakresów muszą być spełnione jednocześnie. Czyli szukamy rekordów, w których dla Kryterium_zakres1 jest spełniony warunek Kryterium1 ORAZ dla Kryterium_zakres2 jest spełniony warunek Kryterium2 ORAZ ….

Zarówno Kryteria_Zakres.. jak i Suma Zakres muszą być ciągłymi zakresami komórek.
Argument Kryteria opisałam w notce:
Kryteria wyszukiwania
warto tam zajrzeć i sprawdzić, jakie są możliwości.

W praktyce wygląda to tak:

Na przykład z widocznej na screenie tabeli chcemy wyznaczyć sumę kwot przypisanych do kobiet o nazwisku Nowak. Formuła wyznaczająca tę wartość to:
=SUMA.WARUNKÓW(D:D;A:A;”* Nowak”;B:B;”K”)
Wielkość liter w kryterium wyszukiwania nie ma znaczenia. Zakres i zakres wyszukiwania jest oznaczona natomiast całymi kolumnami czyli formuła będzie działać nawet przy rozszerzeniu tabeli o kolejne wiersze.
Inny przykład – zsumowanie kwot o wartości co najmniej 50zł przypisanych do osób poniżej 18 lat to formuła:
=SUMA.WARUNKÓW(D:D;D:D;”>=50″;C:C;”>18″)
W przypadku odwołania do innej komórki np. F1 mogłoby to wyglądać tak:
=SUMA.WARUNKÓW(D:D;D:D;”>=” & F1;C:C;”>” & F2)

Odpowiednikiem funkcji Suma.Jeżeli jest funkcja arkuszowa SumIfs.

Kurs Excel - zaawansowane funkcje i formuły

 

Suma.Jeżeli

Suma.Jeżeli to jedna z funkcji matematycznych Excela. W wyniku jej działania otrzymujemy sumę wartości komórek spełniających określone kryterium wyszukiwania.
Argumenty funkcji to:

    • Zakres – zakres komórek, spełniających dany warunek, argument wymagany
    • Kryteria – warunek do spełnienia w zakresie komórek, argument wymagany
    • Suma_zakres – zakres komórek, z których wartości będą sumowane, jeżeli odpowiadające im komórki z pierwszego argumentu spełniają kryterium.  Jest to argument opcjonalny. Jeżeli zostanie pominięty – sumowane będą komórki z argumentu Zakres.

Zarówno Zakres jak i Suma Zakres muszą być ciągłymi zakresami komórek.
Argument Kryteria opisałam w notce:
Kryteria wyszukiwania
warto tam zajrzeć i sprawdzić, jakie są możliwości.

W praktyce wygląda to tak:

Załóżmy, że z widocznej na screenie tabeli chcemy wyznaczyć sumę kwot przypisanych do osób o nazwisku Nowak. Formuła wyznaczająca tę wartość to:
=SUMA.JEŻELI(A:A;”* nowak”;D:D)
Przypominam, że wielkość liter w kryterium wyszukiwania nie ma znaczenia. Zakres i zakres wyszukiwania jest oznaczona natomiast całymi kolumnami czyli formuła będzie działać nawet przy rozszerzeniu tabeli o kolejne wiersze.
Inny przykład – zsumowanie kwot o wartości co najmniej 20zł to formuła:
=SUMA.JEŻELI(D:D;”>=20″)
W przypadku odwołania do innej komórki np. F1 mogłoby to wyglądać tak:
=SUMA.JEŻELI(D:D;”>=” & F1)
natomiast suma wartości powyżej średniej to:
=SUMA.JEŻELI(D:D;”>” & ŚREDNIA(D:D))

Odpowiednikiem funkcji Suma.Jeżeli jest funkcja arkuszowa SumIf.

Kurs Excel dla księgowych

 

Kryteria wyszukiwania

Kryteria wyszukiwania to argument takich funkcji Excela jak m.in. Licz.Jeżeli, Licz.Warunki, Suma.Jeżeli czy Suma.Warunków. Argument ten definiuje zasadę wyszukiwania komórek pobieranych do zliczania czy sumowania.
W przypadku liczb sprawa jest prosta: po prostu wpisujemy liczbę jako kryterium i funkcja wyszuka komórki równe dokładnie tej wartości. W pozostałych przypadkach tworzymy ciąg tekstowy, wpisując do kryterium albo konkretne wartości albo odwołania do innych komórek. Możemy tu zastosować symbole wieloznaczne:
* – zastępuje dowolny ciąg tekstowy, również o zerowej długości
? – zastępuje dowolny pojedynczy znak

Załóżmy, że mamy taką prostą tabelkę, w której chcemy policzyć lub zsumować komórki spełniające dany warunek. Dodatkowo, w oznaczona na żółto komórka F1 to miejsce wypisywania parametrów.

Jeżeli chcemy np. wyszukać wszystkie kobiety – czyli oznaczenia K w kolumnie B jako kryterium wystarczy wstawić po prostu k.  Excel sam wstawi je w oznaczający tekst cudzysłów. Wielkość liter nie ma znaczenia. Chcąc odwołać się do komórki F1 jako parametru, wpisujemy tam K, natomiast jako kryterium wpisujemy F1 (tu znak cudzysłowu się nie pojawia).
Jeżeli wyszukiwanie miałoby dotyczyć wszystkich pozycji z wyjątkiem kobiet – kryterium wyszukiwania to “<>k” lub z odwołaniem do komórki F1: “<>” & F1

W przypadku, gdybyśmy chcieli wyszukać osoby, których imię zaczyna się na A (kolumna A tabeli) – kryterium wyszukiwania to “A*” lub w odwołaniu do parametru w komórce F1: F1 & “*”
Jeżeli wyszukiwanie miałoby dotyczyć np. nazwiska rozpoczynającego się na literę A kryterium w formule byłoby
“* A*” (lub “* ” & F1 & “*”) czyli coś na początku, następnie spacja i litera A, coś na końcu.
Analizując jednak dane w tabelce – dodatkowo trzeba by wykluczyć przypadek, ze na literę A zaczyna się nie nazwisko tylko drugie imię. W takim przypadku konieczne jest dodanie drugiego kryterium (czyli skorzystanie z funkcji Licz.Warunki czy Suma.Warunków) wykluczającego, że po ciągu “spacja A” pojawi się kolejna spacja. Byłoby to np. “<>* A* *” (a z parametrem w F1: “<>* ” & F1 & “* *”).
A gdybyśmy chcieli wyszukać np. ilość komórek, które mają pięcioliterowe nazwisko? Potrzebny byłby schemat, w którym byłoby coś na początku, spacja i 5 znaków czyli: “* ?????”.

Takich różnych kombinacji można oczywiście tworzyć więcej, stosownie do potrzeb i rodzaju danych.
Warto też pamiętać, że w niektórych przypadkach kryterium może też zawierać inną funkcję Excela. W przypadku tej tabeli można wyznaczyć na przykład ilość/sumę komórek zawierających kwotę o wartości co najmniej średniej wszystkich kwot. Takie kryterium wyglądałoby tak: “>=” & ŚREDNIA(D2:D14)

 

 

Licz.Warunki

Licz.Warunki to jedna z funkcji statystycznych Excela. W wyniku jej działania zliczana ilość komórek spełniających określone kryteria wyszukiwania.
Argumenty funkcji to:

    • Kryteria_zakres1 – zakres komórek, w którym chcemy wyliczyć ilość spełniających dany warunek
    • Kryteria1 – warunek wymagany do spełnienia

Zasady stosowania są identyczne jak opisane w notce dotyczącej funkcji Licz.Jeżeli, nie będę więc ich tu powielać.
Polecam też notkę o tworzeniu kryteriów wyszukiwania.
Takich par kryteriów może być 127. To, co najistotniejsze – wszystkie te warunki muszą być spełnione jednocześnie dla każdego rekordu. Działa tu funkcja ORAZ czyli iloczyn logiczny..

W praktyce wygląda to tak:
Załóżmy, że w tabeli jak na ilustracji chcemy policzyć ile osób o nazwisku Nowak ma przypisaną kwotę co najmniej 100zł:

Formuła dla takiego przypadku:
=LICZ.WARUNKI(A:A;”Nowak”;D:D;”>=100″)
A ile kobiet o nazwisku Nowak i mężczyzn o nazwisku kończącym się na k (ale nie Nowak) wpłaciło powyżej 20zł? W takim przypadku musimy zastosować sumę czyli:
=LICZ.WARUNKI(A:A;”Nowak”;B:B;”K”;D:D;”>20″)+LICZ.WARUNKI(A:A;”k”;A:A;”<>Nowak”;B:B;”M”;D:D;”>20″)

Odpowiednikiem funkcji Licz.Warunki w VBA jest funkcja arkuszowa CountIfs.

 

 

Kurs Excel - zaawansowane funkcje i formuły

 

Licz.Jeżeli

Licz.Jeżeli to jedna z funkcji statystycznych Excela. W wyniku jej działania otrzymujemy ilość komórek spełniających określone kryterium wyszukiwania.
Argumenty funkcji to:

      • Zakres – zakres komórek, w którym chcemy wyliczyć iloś ć komórek spełniających dany warunek
      • Kryteria – warunek do spełnienia

Obydwa argumenty są wymagane.

Polecam też notkę o tworzeniu kryteriów wyszukiwania.

W praktyce  wygląda to tak:

Mamy prostą tabelkę z danymi. Chcąc policzyć ile jest kobiet (czyli oznaczenie “K” w kolumnie B) przy użyciu funkcji Licz.Jeżeli zapisujemy:
=LICZ.JEŻELI(B2:B14;”K”)
Argumentu Kryteria nie musimy wpisywać w cudzysłowie, Excel sam przekształci wpisaną tam wartość w tekst. Wielkość liter nie ma tu znaczenia.
Gdybyśmy chcieli policzyć ilość mężczyzn – jako kryterium wystarczy wstawić literkę M.
Chcąc natomiast policzyć ilość osób, które wpłaciły np.10zł – możemy zastosować formułę:
=LICZ.JEŻELI(D2:D14;10)

Kryterium nie musi być jednak wpisywane na sztywno do funkcji. Można tu odwoływać się do wartości w innej komórce. W załączonym przykładzie niech będzie to np.komórka F1. Formuła odwołująca się do niej to:
=LICZ.JEŻELI(B2:B14;F1)
Jeżeli w komórce F1 wpiszemy np.K – to funkcja zwróci nam ilość kobiet. Wystarczy zmienić na M i policzymy mężczyzn.

Kryterium wyszukiwania może być także bardziej złożone. Jeżeli chcielibyśmy np. sprawdzić, do ilu osób w tabelce przypisana jest np. kwota powyżej 100zł możemy zastosować formułę:
=LICZ.JEŻELI(D2:D14;”>100″)
lub odwołując się do wartości np.w komórce F1:
=LICZ.JEŻELI(D2:D14;”>” & F1)
Warto zauważyć, że adres komórki (czyli F1) musi być poza znakiem cudzysłowu, korzystamy tu z łączenia tekstów za pomocą znaku konkatenacji &.

Można też tworzyć bardziej złożone kryteria. Załóżmy, że w kolumnie A chcemy wyszukać liczbę imion rozpoczynających się na literę A. Formuła w tym przypadku będzie wyglądać tak:
=LICZ.JEŻELI(A2:A14;”A*”)
Znak * oznacza dowolny ciąg tekstowy, o dowolnej długości, również zerowy. W przypadku odwołania do komórki F1 wygląda to tak:
=LICZ.JEŻELI(A2:A14;F1 & “*”)

Symbol ? oznacza dokładnie jeden znak. Czyli funkcja
=LICZ.JEŻELI(A2:A14;”*a?”)
wskaże ilość komórek w kolumnie A, zawierających literę “a” na przedostatnim miejscu.

Odpowiednikiem funkcji Licz.Jeżeli w VBA jest funkcja arkuszowa CountIf.

 

 

Kurs Excel 2010 esencja

 

Łączenie tekstów

W jaki sposób łączyć wartości zapisane w komórkach Excela? Służy do tego przede wszystkim funkcja tekstowa Excela Złącz.Teksty.
Funkcja ta może mieć do 255 argumentów, będących albo wpisanymi wartościami albo odwołaniami do komórek .  W wyniku jej działania otrzymujemy wartość tekstową.

W przykładzie z grafiki powyżej argumenty 1 i 3 to odwołania do adresów komórek, natomiast argument drugi to tekst, a dokładnie rzecz biorąc ” ” czyli cudzysłów spacja cudzysłów.

Identyczny efekt jak w przypadku zastosowania funkcji Złącz.Teksty możemy osiągnąć również poprzez zastosowania znaku konkatenacji czyli & . W tym przypadku wpisujemy formułe bezpośrednio w pasku formuły.

Efekt końcowy jest identyczny.

W obydwu przypadkach warto pamiętać o tym, że łączone są wartości tekstowe . Jeżeli odwołujemy się do komórki z liczbą, to pobierana jest ona jako tekst. Czasami mogą się przez to pojawiać niespodzianki, np. takie:

Wprawdzie w komórce w kolumnie H jest data, ale w wyniku złączenia widzimy dziwną liczbę 43844. I nie ma w tym nic dziwnego – data w Excelu to przecież liczba.
Data w Excelu to liczba
A to, co widzimy na ekranie to tylko kwestia formatowania.

Kurs Excel 2013 od podstaw