Opublikowano

Listy zależne w Excelu – prosty sposób

W Excelu pomocą narzędzia Sprawdzanie Poprawności można w prosty sposób wstawić do komórki listę rozwijaną, z której użytkownik może wybierać interesujące go pozycje. Co jednak jeżeli elementy na liście są uzależnione od wartości wpisanej w innej komórce. Na przykład przy uzupełnianiu formularza zamówienia z pierwszej listy wybieram kategorię produktową, a z drugiej listy wybieram konkrety produkt. W zależności od wybranej kategorii z pierwszej listy, na drugiej liście prezetowane są tylko produkty wchodzące w skład wybranej kategorii.

W internecie można zaleźć wiele sposobów na tworzenie list zależnych. Niektóre rozwiązania sugerują wykorzystanie w tym celu formuł index i przesunięcie, inne sugerują wykorzystanie makr.
W tym artykule została opisana chyba najprostsza metoda bazująca na nazwach zakresów i formule WARUNKI (użytkownicy starszych wersji Excela zamiast formuły WARUNKI mogą ten sam efekt osiągnąć zagnieżdżając formułę JEŻELI).

Krok pierwszy

W omawianym przykładzie wykorzystane zostaną dwie listy rozwijane. Lista główna zawiera nazwy kategorii produktowej oraz listy zależne zawierające nazwy poszczególnych produktów. Przykładowe listy prezentuje poniższy rysunek.

W arkuszu Formularz do komórki B3 należy wstawić listę pozwalającą na wybór kategorii produktowej. W tym celu na wstążce programu przejdź do karty Dane, a następnie kliknij ikonkę Poprawności danych.

Na ekranie pojawi się okienko Sprawdzania poprawności danych. W okienku tym z pośród wszystkich dozwolonych opcji wybierz Listę, a następnie w polu źródło zaznacz zakres komórek zawierających nazwy kategorii produktowych, tak jak pokazano to na poniższym rysunku.

Po kliknięciu przycisku Ok, w komórce pojawi się rozwijana lista, z której można wybrać nazwę kategorii produktowej.

Krok druginazwanie zakresów

Kolejnym krokiem jest nadanie nazw zakresom zawierającym listy produktów. W tym celu należy zaznaczyć listę wraz z jej tytułem, a następnie na wstążce w karcie Formuły kliknąć ikonkę Utwórz z zaznaczenia. Na ekranie pojawi się okienko Tworzenia nazwy z zaznaczenia. Jeżeli tytuł kolumny znajduje się w pierwszej komórce zaznaczonego zakresu, to upewnij się, że zaznaczona jest opcja Górny wiersz. Zatwierdź wybór klikając przycisk Ok.

Od tej chwili zaznaczonemu zakresowi komórek została przyporządkowana nazwa nabiał. Po zaznaczeniu komórek od C2 do C36 nazwa ta będzie widoczna w polu nazwy nad arkuszem.

Te same kroki powtarzamy w celu nadania nazw zakresom pozostałym listą z nazwami produktów.
Nazwy można później sprawdzić i edytować w okienku menadżera nazw.

Krok trzeci – formuła

Następnym etapem w tworzeniu listy zależnej jest napisanie formuły działającej na następującej zasadzie: jeżeli w komórce B3 wybrana została kategoria nabiał, wtedy wyświetl listę produktów wchodzących w skład tej kategorii, jeżeli natomiast została wybrana pieczywo, wtedy wyświetl listę produktów wchodzącą w skald tej kategorii i tak dalej aż w formule zostaną uwzględnione wszystkie kategorie produktowe i listy.
Użytkownicy Excela 365 i Excela 2019 mogą wykorzystać formułę WARUNKI (IFS), lub tak jak użytkownicy wcześniejszych wersji programu Excel mogą zagnieździć odpowiednią ilość razy formułę JEŻELI (IF).

Ustaw się w dowolnej pustej komórce w arkuszu i wprowadź do niej formułę. Zapis przykładowych formuł podano poniżej.

Formuła WARUNKI

=WARUNKI($B$3=”nabiał”;nabiał;$B$3=”pieczywo”;pieczywo;$B$3=”makaron”;makaron;$B$3=”produkty zbożowe”;produkty_zbożowe;$B$3=”przetwory”;przetwory;$B$3=”sosy i pasty”;sosy_i_pasty;$B$3=”zboża”;zboża;$B$3=”zioła i przyprawy”;zioła_i_przyprawy)

Formuła JEŻELI

=JEŻELI($B$3=”nabiał”;nabiał;JEŻELI($B$3=”pieczywo”;pieczywo;JEŻELI($B$3=”makaron”;makaron;JEŻELI($B$3=”produkty zbożowe”;produkty_zbożowe;JEŻELI($B$3=”przetwory”;przetwory;JEŻELI($B$3=”sosy i pasty”;sosy_i_pasty;JEŻELI($B$3=”zboża”;zboża;zioła_i_przyprawy)))))))

Krok czwarty – wstawienie listy zależnej

Skopiuj z paska formuły wpisaną wcześniej funkcję. Kliknij na komórkę C3 lub na inną komórkę, do której chcesz wstawić listę zależną.
Na wstążce programu przejdź do karty Dane i kliknij ikonkę Poprawność Danych.

W okienku sprawdzania poprawności danych z dozwolonych opcji wybierz listę, a następnie do pola Źródło wklej skopiowaną wcześniej formułę, pamiętaj, żeby formuła zaczynała się od znaku równości. Zatwierdź zmiany klikając przycisk OK.

Uwaga
Pole Źródło ma ograniczenie co do ilości wpisywanych znaków. Ograniczenie to wynosi 255 znaków.

Od tej pory po wybraniu w komórce B3 nazwy kategorii produktowej, w komórce C3 będzie wyświetlała się lista z nazwami produktów należącymi do wybranej kategorii.

Opublikowano

Liczba porządkowa – sortowanie i filtrowanie

Wpisanie na sztywno liczby porządkowej do tabeli sprawia problemy zarówno podczas sortowania jak i flitowania danych. Podczas sortowania tabeli liczby porządkowe układają się w niepoprawnej kolejności, natomiast podczas filtrowania, liczby porządkowe są co prawda w kolejności rosnącej, ale brak im ciągłości. Problem ten można rozwiązać stosując formuły ILE.WIERSZY lub SUMY.CZĘŚCIOWE.

Zastosowanie formuły ILE.WIERSZY

Formułę ILE.WIERSZY możemy zastosować jedynie w przypadku sortowania tabeli.

Przykład zastosowania

Do tabeli wstaw kolumnę Liczba porządkowa (w przykładzie jest to kolumna A), następnie do pierwszej pustej komórki w tej kolumnie wprowadź formułę:

=ILE.WIERSZY($A$2:A2)

Wpisana formuła oblicza ile jest wierszy w zaznaczonym zakresie komórek. W pierwszej komórce kolumny formuła zwróci wartość 1. Należy zwrócić uwagę na zablokowany pierwszy adres w zaznaczonym zakresie komórek. Zastosowanie tu odwołania bezwzględnego, sprawi, że formuła podczas kopiowania do kolejnych komórek w kolumnie będzie zwiększała zakres o kolejną komórkę, a tym samym wynik formuły będzie zwiękaszał się o 1 i w ten sposób utworzona zostanie seria liczb porządkowych.

Sortowanie danych w tabeli nie będzie miało wpływu na wartości w kolumnie Liczba porządkowa, gdyż za każdym razem formuła zostanie ponownie przeliczona i zwróci poprawną serię danych.

Zastosowanie formuły SUMY.CZĘŚCIOWE

Formuła SUMY.CZĘŚCIWOE może być zastosowana zarówno do tworzenia liczby porządkowej podczas sortowania jak i fitrowania, jednak dla poprawnego działania wymaga, aby w tabeli była przynajmniej jedna kolumna z wypełnionymi wszystkimi komórkami.

Przykład zastosowania

Podobnie jak w poprzednim przykładzie wstaw do tabeli kolumnę Liczba porządkowa i wprowadź do pierwszej pustej komórki formułę:

=SUMY.CZĘŚCIOWE(3;$B$2:B9)

Formułą SUMY.CZĘŚCIOWE z parametrem 3 obliczy ile jest nie pustych komórek w kolumnie B. Dzięki zablokowaniu symbolem dolara pierwszego adresu w zaznaczonym zakresie, podczas kopiowania formuły do kolejnych komórek, będzie ona zwiększała swój wynik o 1.

Poniższy rysunek pokazuje wynik działania formuły na oryginalnej tabeli i na tabeli z zastosowanymi filtrami oraz sortowaniem.

Opublikowano

Odwołania cykliczne

Mówiąc najprościej odwołanie cykliczne powstaje wtedy, kiedy formuła w Excelu do obliczeń używa samej siebie. Wyobraźmy sobie nastepującą sytuację. Do komórki A10 wstawiam formułę SUMA, jednka zamiast jako argument formuły podać zakres sumowanych komórek od A1 do A9, podaję jako sumowany zakres całą kolumnę A. W tym momencie powstaje w Excelu odwłoanie cykliczne, jednym z argumentów używanych przez formułę SUMA jest komórka A10, zawierająca właśnie tą formułę, czyli do obliczenia sumy, formuła wykorzystuje swój wynik.


Jeżeli w Excelu powstaje odwołanie cykliczne, program wyświetla ostrzeżenie.

Jak odnaleźć i usunąć odwołania cykliczne

Jeżeli w arkuszu istnieją odwołania cykliczne, należy je odnaleźć i usunąć, gdyż formuły zwracają niepoprawne wyniki. W celu odnalezienia wszystkich odwołań cyklicznych należy na wstążce programu Excel przejść do karty Formuły, nastepnie rozwinąć listę ukrytą pod ikonką Sprawdzanie błędów i wybrać z listy ostatnią pozycję Odwołania cykliczne.

Po wybraniu opcji Odwołania cykliczne zostanie wyświetlona dodatkowa lista z adresami komórek zawierających formuły z odwołaniem cyklicznym. Kliknięcie na adres na liście przenosi nas do komórki zawierającej formułę z odwołaniem cyklicznym. Teraz wystarczy poprawić formułę, aby pozbyć się odwołania cyklicznego.

Opublikowano

Drukowanie siatki Excela, nagłówków kolumn i wierszy

Drukując dokument przygotowany w Excelu na wydruku nie pojawi się siatka komórek, nagłówki kolumn ani też nagłówki wierszy. Po wydrukowaniu prostego zestawienia będzie ono wyglądało tak jak na rysunku poniżej.

Prosta tabela
Podgląd wydruku

Istnieje jednak możliwość takiego ustawienia wydruku, aby pojawiła się na nim siatka komórek oraz nagłówki kolumn i wierszy. W tym celu na wstążce programu Excel należy przejść do karty Układ strony i kliknąć ikonkę Tytuły wydruku.

Na ekranie zostanie wyświetlone okienko Ustawienia strony. W oknie tym, w sekcji Drukuj należy zaznaczyć opcje: Linie siatki oraz Nagłówki wierszy i kolumn.

Po zatwierdzeniu wyboru i wydrukowaniu dokumentu lub włączeniu podglądu wydruku, na wydruku będą widoczne linie siatki oraz nagłówki kolumn i wierszy, tak jak na poniższym rysunku.

Wydruk z liniami siatki oraz nagłówkami wierszy i kolumn
Opublikowano

Dodawanie i usuwanie komórek z zaznaczonych zakresów

Każdy z nas potrafi zaznaczać komórki w Excelu za pomocą myszko albo klawiatury. Niesąsiadujące zakresy zaznaczane są przez przytrzymanie na klawiaturze przycisku Ctrl. Co jedank zrobić, gdy do istniejących już zaznaczonych obszarów należy dodać nowe, lub usunąć niechciane zaznaczone zakresy lub chemy zapamiętać zaznaczone już zakresy, żeby dodając nowe przez przyadek nie utracić istniejących.

W celu stosujemy opcję rozszerzonego zaznaczania. W celu uruchomienia tego trybu należy na klawiaturze wcisnąć przyciski Shift F8.

Zaznaczanie dodatkowych zakresów komórek

W celu dodania do zaznaczonego zakresu nowych niesąsiadujących ze sobą zakresów komórek należy:

  1. Zaznaczyć pierwszy zakres komórek (jeżeli nie był wcześniej zaznaczony).
  2. Na klawaiturze wcisnąć klawisze Shift F8.
  3. Zaznaczyć kolejne zakresy przy użyciu myszki lub klawiatury.
  4. Aby wyłączyć zapamiętanie zaznaczonych komórek na klawiaturze ponownie należy wcisnąć przyciski Shift F8 lub przycisk Esc.

Usuwanie zbędnego zaznaczenia

Jeżeli chcemy usunąć zaznaczenie z niektórych komórek, pozostawiając inne komórki zaznaczone należy:

  1. Na klawaiturze wcisnąć klawisze Shift F8.
  2. W ramch zaznaczonego zakresu przy użyciu myszki lub klawiatury wybrać komórki, które nie mają być zazanczone.
  3. Wyłączyć tryb rozszerzonego zaznacznania wciskając na klawiaturze przyciski Shift F8 lub przycisk Esc.
Opublikowano

Blokowanie hasłem wybranych komórek

Włączając w Excelu ochronę arkusza następuje zablokowanie do edycji wszystkich komórek, w tym momencie nasuwa się pytanie co zrobić, aby zablokować do edycji tylko kilka wybranych komórek, a wszystkie pozostałe pozostawić odblokowane z możliwością edycji. Rozwiązanie tego problemu przebiega dwuetapowo.

Pierwszym etapem jest odblokowanie do edycji całego arkusza. Drugi krok polega na zablokowaniu tylko wybranych komórek.

Odblokowanie całego arkusza

Zaznacz cały arkusz, możesz to zrobić za pomocą skrótu klawiaturowego CTRL + A lub klikając przycisk w kształcie trójkąta znajdujący się w lewym górnym rogu arkusza.

Następnie wywołaj okienko Formatowania komórek. Kliknij prawym klawiszem myszy na zaznaczony arkusz i z wyświetlonego menu wybierz opcję Formatuj komórki… możesz także użyć skrótu klawiaturowego CTRL+1.

W oknieku Formatowanie komórek przejdź do karty Ochrona i odznacz opcję Zablokuj, tak jak pokazano to na poniższym rysunku. Następnie kliknij przycisk OK. W efekcie po założeniu ochrony na arkusz wszystkie komórki będą dostępne do edycji.

Zablokowanie wybranych komórek

Zaznacz komórki, które chcesz zablokować do edycji. Jeżeli komórki nie sąsiadują ze sobą , podczas zaznaczania wciśnij na klawiaturze przycisk CTRL.

Następnie wywołaj okienko Formatowania komórek. Kliknij prawym klawiszem myszy na jedną z zaznaczonych komórek i z wyświetlonego menu wybierz opcję Formatuj komórki… możesz także użyć skrótu klawiaturowego CTRL+1.

W oknieku Formatowanie komórek przejdź do karty Ochrona i zaznacz opcję Zablokuj, tak jak pokazano to na poniższym rysunku. Następnie kliknij przycisk OK. W efekcie po założeniu ochrony na arkusz wybrane komórki będą zablokowane do edycji.

Teraz przejdź na wstążce do karty Recenzja i kliknij ikonkę Chroń Arkusz. Ustal hasło do ochrony arkusza i potwierdź je w kolejnym okienku.

Teraz wybrane przez Ciebie komórki są chronione hasłem, pozostałe komórki w arkuszu są dostępne do edycji.

Opublikowano

Czy w Excelu można ukryć zawartość komórki?

O ile w Excelu w łatwy sposób można ukryć wiersz lub kolumnę, to w odniesieni do pojedynczych komórek nie istnieje proste narzędzie. Można sobie jednak z tym zadaniem poradzić w dość łatwy sposób.

Pierwszym krokiem jest zmiana formatowania komórki na format niestandardowy. W tym celu należy zaznaczyć komórkę lub komórki, których wartość ma zostać ukryta. Następnie kliknij na zaznaczone komórki prawym klawiszem myszki i wybierz opcję Formatuj komórki. Na ekranie zostanie wyświetlone okienko Formatowania komórek, okienko to możesz wywołać także za pomocą skrótu klawiaturowego CTRL+1.

W okienku formatowania komórek przejdź do karty Liczby i z dostępnej listy kategorii formatowania komórek wybierz kategorię niestandardową. W polu Typ wpisz kod składający się z trzech średników.

;;;

Kod ten ukrywa wartość wpisaną do komórki. Dzięki temu komórka będzie wyglądała na pustą.

W efekcie opisanych powyżej działań, została ukryta wartość w komórce, jednak jest ona nadal widoczna na pasku formuły, co pokazuje poniższy rysunek.

Kolejnym krokiem jest ukrycie wartości na pasku formuły. W tym celu należy zaznaczyć ponownie komórkę lub komórki, których wartości mają zostać ukryte, a następnie wywołać tak jak poprzednio okienko Formatowania komórek. W okienku formatowania komórek należy przełączyć się do karty Ochrona i zaznaczyć pole Ukryj. Dla Excela jest to komunikat, że w momencie ochrony arkusza hasłem wartość z komórki nie będzie wyświetlana na pasku formuły.

Ostatnią czynnością jest włączenie ochrony arkusza. Na wstążce programu przejdź do karty Recenzja i kliknij ikonkę Chroń arkusz.

Na ekranie zostanie wyświetlone okienko. W polu hasło, wpisz wymyślone przez siebie hasło, potwierdź je wciskając przycisk Ok. W kolejnym okienku potwierdź hasło i ponownie wciśnij przycisk Ok. Założenie hasła na arkusz spowoduje zablokowanie komórek do edycji. Jeżeli chcesz edytować wybrane komórki w arkuszu, to musisz je przed założeniem hasła odblokować do edycji.

Jak zablokować do edycji tylko wybrane komórki przeczytasz TUTAJ

Opublikowano

Kolejność działań matematycznych w Excelu

Kolejność działań matematycznych określa, które z obliczeń mają być wykonane jako pierwsze, aby osiągnąć prawidłowy wynik. Kolejność działań matematycznych w Excelu jest taka sama jakiej uczyliśmy się w szkole na matematyce.

Najpierw wykonaj obliczenia w nawiasach, później oblicz potęgi i pierwiastki, następnie wykonaj mnożenie i dzielenie, a na końcu dodawanie i odejmowanie.

Jeżeli w obliczeniach następuje po sobie mnożenie i dzielenie lub dodawanie i odejmowanie, to obliczenia wykonujemy po kolei tak jak są zapisane, czyli od lewej do prawej strony.

Opublikowano

pdf Analiza i prezentacja danych w Microsoft Excel. Vademecum Walkenbacha. Wydanie II

[twocol_one]

Autor:
John Walkenbach, Michael Alexander

ISBN:

Cena:


[/twocol_one]

[twocol_one_last]


Wykorzystaj możliwości Excela w zarządzaniu!
Jeżeli masz przed sobą setki, a może tysiące lub miliony danych, z których chcesz wyciągnąć celne wnioski, potrzebujesz narzędzia, które pomoże Ci to ogarnąć. Mowa oczywiście o Excelu. Nieważne, kim jesteś – studentem, księgowym, menedżerem czy prezesem – na 100% docenisz drzemiący w nim potencjał!

Dzięki tej książce dowiesz się, jak wyłuskać najistotniejsze informacje z morza danych. W trakcie lektury nauczysz się błyskawicznie przygotowywać raporty oraz prezentacje. Przekonasz się, że tabele przestawne wcale nie muszą być takie straszne, oraz zobaczysz najlepsze techniki prezentacji tendencji czy oceny efektywności w realizacji celów. Kolejne wydanie książki zostało zaktualizowane, ulepszone i rozszerzone o mnóstwo nowych, przydatnych wiadomości. Dowiesz się, jak importować dane z bazy SQL Server oraz jak wykorzystać możliwości dodatku Power View. Książka ta jest idealną pozycją dla tonących w gąszczu danych!

Dzięki tej książce:

poznasz narzędzia Excela w zakresie analizy i prezentacji danych
opanujesz najlepsze techniki projektowania tabel
przygotujesz czytelne raporty
wykorzystasz w pełni możliwości Excela
Uratuj się z morza danych!
[/twocol_one_last]