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.
Naciśnij OK, a potem ponownie przycisk Nowy.
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.
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.
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.
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
Pobierz przykładowe dane:
RXWfFa40bUAbE
Ćwiczenie 1
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
Zawartość arkusza Zamówienie:
R5T94SNe2xvrk
R1P53ygKvhDCp
Rc1RM5p2H0qjK
R1PQldc66lWXA
R1DMeTwssXshZ
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.