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.Warunków 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)