Tworzymy zamówienia towarów pomocy szkolnych (zwanych w arkuszu Towarami) z wykorzystaniem danych znajdujących się w drugim arkuszu. Baza towarów jest bardzo obszerna, dlatego chcemy przygotować dwie listy rozwijane. Z pierwszej będą wybierane kategorie, a z drugiej konkretny towar należący do tej grupy.
Arkusz Zamówienie, do którego będą wprowadzane szczegóły zamówienia, wygląda jak na filmie.
Drugi arkusz noszący nazwę Kategorie zawiera bazę danych, w której towary zostały przyporządkowane do odpowiednich grup. Nazwa każdej kategorii znajduje się w nagłówku kolumny.
Aby na podstawie tak zgromadzonych danych utworzyć powiązane ze sobą listy rozwijane, skorzystamy z nazwanych zakresów.
W tym celu:
W arkuszu Zamówienie na karcie Formuły, w grupie poleceń Nazwy zdefiniowane wybierz Menedżer nazw i naciśnij Nowy.
W oknie dialogowym, w polu Nazwa wpisz: Kategorie, a w polu Odwołuje się do wprowadź formułę:
Linia 1. znak równości PRZESUNIĘCIE otwórz nawias okrągły Kategorie wykrzyknik $A$1 średnik średnik średnik średnik ILE kropka NIEPUSTYCH otwórz nawias okrągły Kategorie wykrzyknik $1 dwukropek $1 zamknij nawias okrągły zamknij nawias okrągły.
Jeśli po prawej stronie arkusza z bazą danych towarów dodamy kolejną kategorię, to zostanie również zawarta w tym nazwanym zakresie komórek. Formuła sprawdza liczbę wypełnionych komórek i za pomocą funkcji PRZESUNIĘCIE() dopasowuje liczbę komórek w tym zakresie. Pamiętajmy, aby w 1. wierszu nie stosować pustych komórek w obrębie zestawienia.
Za sprawdzenie liczby wypełnionych komórek w pierwszym wierszu odpowiada fragment:
Linia 1. ILE kropka NIEPUSTYCH otwórz nawias okrągły Kategorie wykrzyknik $1 dwukropek $1 zamknij nawias okrągły.
ILE.NIEPUSTYCH(Kategorie!$1:$1)
Gdy dodasz kolejną kategorię produktu jako osobną kolumnę, liczba niepustych komórek w pierwszym wierszu zwiększy się o jeden.
Uzyskany wynik, czyli liczba kategorii, przekazywana jest do ostatniego parametru funkcji PRZESUNIĘCIE(). Tworzy ona zakres, poczynając od komórki Kategorie!$A$1 o szerokości zwróconej przez funkcję ILE.NIEPUSTYCH(). Zakres ten zawiera nazwy wszystkich kategorii produktów.
Podobnie jak w drugim kroku dodajemy drugi zakres o nazwie: Towary i w polu Odwołuje się do przyporządkujemy mu następującą formułę:
Linia 1. znak równości PRZESUNIĘCIE otwórz nawias okrągły Kategorie wykrzyknik $A$1 średnik 1 średnik PODAJ kropka POZYCJĘ otwórz nawias okrągły Zamówienie wykrzyknik $B5 średnik Kategorie wykrzyknik $1 dwukropek $1 średnik 0 zamknij nawias okrągły minus 1 średnik ILE kropka NIEPUSTYCH otwórz nawias okrągły PRZESUNIĘCIE otwórz nawias okrągły Kategorie wykrzyknik $A dwukropek $A średnik średnik PODAJ kropka POZYCJĘ otwórz nawias okrągły Zamówienie wykrzyknik $B5 średnik Kategorie wykrzyknik $1 dwukropek $1 średnik 0 zamknij nawias okrągły minus 1 zamknij nawias okrągły zamknij nawias okrągły minus 1 zamknij nawias okrągły.
Jej wynikiem jest lista towarów z kategorii wpisanej w komórce B5. Lista ta, podobnie jak poprzednia, automatycznie dopasowuje się do liczby wpisanych towarów w danej kategorii. Naciskamy przycisk Zamknij, aby zamknąć okno dialogowe do tworzenia nazwanych zakresów. Przejdźmy teraz do utworzenia rozwijanych list:
Mając komórkę B5 zaznaczoną, wybierz na karcie Dane, w grupie poleceń Narzędzia danych wybierz Poprawność danych. W oknie, które się pojawi, w polu Dozwolone wybierz Lista, a w polu Źródło wpisz:
Linia 1. znak równości Kategorie.
=Kategorie
i naciśnij przycisk OK.
Zaznacz komórkę C5 i podobnie jak w poprzednim kroku ustaw listę rozwijaną w komórce za pomocą okna Sprawdzanie poprawności danych. W polu Źródło zastosuj formułę:
Linia 1. znak równości Towary.
=Towary
Zaznacz komórki B5 i C5, a następnie na karcie Narzędzia główne, w grupie poleceń Schowek wybierz Kopiuj.
Następnie na karcie Narzędzia główne, w grupie poleceń Schowek wybierz Wklej.
Teraz wypełnimy zamówienie za pomocą powiązanych list. W komórce kolumny B określamy odpowiednią kategorię, a następnie w tym samym wierszu w komórce kolumny C wskazujemy nazwę towaru należącego do określonej wcześniej grupy. Listy rozwijane znajdujące się w kolumnach B i C są powiązane ze sobą w obrębie każdego wiersza.
R19GnZ7AB22PR
Nagranie filmowe dotyczące powiązań ze sobą dwóch rozwijanych list
Nagranie filmowe dotyczące powiązań ze sobą dwóch rozwijanych list
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.
Pracujesz dorywczo w sklepie sprzedającym np. książki, filmy, płyty muzyczne itp. Zaprojektuj 2 arkusze. Pierwszy ma zawierać spis produktów z podziałem na kategorie (powinny być co najmniej 3) np. w przypadku książek gatunki literackie, a drugi arkusz zamówienia, jakie zostały dokonane na konkretne produkty. Powinny znajdować się tam takie informacje jak: kategoria, nazwa i ilość sztuk. Wykorzystując nowo poznane funkcje Excela, w arkuszu z zamówieniami utwórz listy rozwijane dostępnych kategorii i produktów.
Przykładowe rozwiązanie zadania:
Zawartość arkusza Kategorie:
R1DW066MOXivJ
Na zrzucie ekranu widoczny jest fragment arkusza Excel. W kolumnie A, w komórce A1 wpisano tytuł MUSICAL. W komórkach od A2 do A4 wprowadzono tytuły. W kolumnie B, w komórce B1 wpisano tytuł FAMILIJNY. W komórkach od B2 do B6 wprowadzono tytuły filmów familijnych. W kolumnie C, w komórce C1 wpisano tytuł BAJKI. W komórkach od C2 do C4 wpisano tytuły bajek.
Zawartość arkusza Zamówienie:
R5T94SNe2xvrk
Na zrzucie ekranu widoczny jest fragment arkusza Excel. W kolumnie A, w komórce A1 wpisano tytuł NUMER. W komórkach od A2 do A8 wprowadzono kolejne numery porządkowe. W kolumnie B, w komórce B1 wpisano tytuł GATUNEK. W komórkach od B2 do B8 wprowadzono gatunki filmów. W kolumnie C, w komórce C1 wpisano tytuł TYTUŁ. W komórkach od C2 do C8 wpisano tytuły filmów. W kolumnie D, w komórce D1 wpisano tytuł ILOŚĆ SZTUK. W komórkach od D2 do D8 wpisano dane liczbowe.
R1P53ygKvhDCp
Na zrzucie ekranu widoczny jest fragment arkusza Excel. W ARKUSZU widoczne jest duże okno dialogowe zatytułowane MENADŻER NAZW. Widoczne są przyciski: nowy, edytuj oraz usuń. Następnie w oknie znajdują się dwa wiersze, pięć kolumn, są to Nazwa, Wartość, Odwołuje się do, Zakres oraz Komentarz. W pierwszym wierszu zamieszczono nazwę FILMY, w drugim nazwę KATEGORIE. Dla nazwy FILMY, w kolumnie zatytułowane ODWOŁUJE SIĘ DO wyświetlono zapis formuły, jest ona następująca: =PRZESUNIĘCIE(Kategorie!$A$1;1;PODAJ.POZYCJĘ(Zamówienie!$B19; Kategorie!$1:$1;0)-1; ILE.NIEPUSTYCH(PRZESUNIĘCIE(Kategorie!$A:$A;; PODAJ.POZYCJĘ(Zamówienie!$B19; Kategorie!$1:$1;0)-1))-1)
Rc1RM5p2H0qjK
Na zrzucie ekranu widoczny jest fragment arkusza Excel. W ARKUSZU widoczne jest duże okno dialogowe zatytułowane MENADŻER NAZW. Widoczne są przyciski: nowy, edytuj oraz usuń. Następnie w oknie znajdują się dwa wiersze, pięć kolumn, są to Nazwa, Wartość, Odwołuje się do, Zakres oraz Komentarz. W pierwszym wierszu zamieszczono nazwę FILMY, w drugim nazwę KATEGORIE. Dla nazwy KATEGORIE, w kolumnie zatytułowane ODWOŁUJE SIĘ DO wyświetlono zapis formuły, jest ona następująca: =PRZESUNIĘCIE(Kategorie!$A$1;;;;ILE.NIEPUSTYCH(Kategorie!S1:$1))
R1PQldc66lWXA
Na zrzucie ekranu widoczny jest fragment arkusza Excel. W arkuszu widoczne jest zaznaczenie komórki B2 określające gatunek filmów oraz duże okno dialogowe zatytułowane SPRAWDZANIE POPRAWNOŚCI DANYCH. Widoczne są zakładki w oknie: Ustawienia, Komunikat wejściowy, Alert o błędzie. Aktywna jest zakładka Ustawienia. W zakładce skonfigurowane są kryteria poprawności. Pierwsze kryterium to Dozwolone: Lista. Obok zaznaczone są opcje Ignoruj puste oraz Rozwinięcie w komórce. Drugie kryterium to Wartości danych. Ta opcja jest niedostępna do skonfigurowania. Automatycznie ustawiono tam: między. Trzecie kryterium to Źródło: =Kategorie. W dolnej części okna znajduje się opcja zatytułowana: Zastosuj te zmiany we wszystkich komórkach z tymi samymi ustawieniami. Ta opcja nie została zaznaczona. Poniżej znajdują się przyciski wyczyść wszystko, ok oraz anuluj.
R1DMeTwssXshZ
Na zrzucie ekranu widoczny jest fragment arkusza Excel. W ARKUSZU widoczne jest zaznaczenie komórki C2 określające tytuł filmu oraz duże okno dialogowe zatytułowane SPRAWDZANIE POPRAWNOŚCI DANYCH. Widoczne są zakładki w oknie: Ustawienia, Komunikat wejściowy, Alert o błędzie. Aktywna jest zakładka Ustawienia. W zakładce skonfigurowane są kryteria poprawności. Pierwsze kryterium to Dozwolone: Lista. Obok zaznaczone są opcje Ignoruj puste oraz Rozwinięcie w komórce. Drugie kryterium to Wartości danych. Ta opcja jest niedostępna do skonfigurowania. Z automatu ustawiono tam: między. Trzecie kryterium to Źródło: =Filmy. W dolnej części okna znajduje się opcja zatytułowana: Zastosuj te zmiany we wszystkich komórkach z tymi samymi ustawieniami. Ta opcja nie została zaznaczona. Poniżej znajdują się przyciski wyczyść wszystko, ok oraz anuluj.
2
Ćwiczenie 2
Zmodyfikuj arkusz z poprzedniego zadania. Przyjmij, że każdy towar z danej kategorii kosztuje tyle samo. Utwórz arkusz zawierający nazwę kategorii i cenę jednego produktu. Oblicz kwotę jaką trzeba zapłacić za to zamówienie.
Przykładowe rozwiązanie zadania:
Zawartość arkusza Ceny:
R199xptvFQ3U4
Na zrzucie ekranu widoczny jest fragment arkusza Excel. W kolumnie A, w komórce A1 wpisano tytuł KATEGORIA. W komórkach od A2 do A4 wpisano kategorie filmów. W kolumnie B, w komórce B1 wpisano tytuł CENA. W komórkach od B2 do B4 wpisano kwoty.
RTKTjfqUu9Kob
Na zrzucie ekranu widoczny jest fragment arkusza Excel. W kolumnie A, w komórce A1 wpisano tytuł NUMER. W komórkach od A2 do A8 wprowadzono kolejne numery porządkowe. W kolumnie B, w komórce B1 wpisano tytuł GATUNEK. W komórkach od B2 do B8 wprowadzono gatunki filmów. W kolumnie C, w komórce C1 wpisano tytuł TYTUŁ. W komórkach od C2 do C8 wpisano tytuły filmów. W kolumnie D, w komórce D1 wpisano tytuł ILOŚĆ SZTUK. W komórkach od D2 do D8 wpisano dane liczbowe. W kolumnie E, w komórce E1 wpisano tytuł KWOTA. W komórce E2 zaprezentowano formułę, która brzmi następująco: =WYSZUKAJ.PIONOWO(B2;Ceny!$A$2:$B$4;2;FAŁSZ)
