Wyszukiwanie wartości optymalnie dopasowanej do potrzeb
Przyjmijmy, że w firmie, która produkuje krzesła dla twojej szkoły trzeba dobrać odpowiednie części w procesie produkcji. W arkuszu o nazwie Dostępne części znajdują się ich numery fabryczne, typy oraz wymiary. Na podstawie aktualnych potrzeb produkcyjnych dobierane są części o wymiarach najbardziej zbliżonych do potrzeb. Aby nie musieć za każdym razem ręcznie przeszukiwać całej listy dostępnych części i wyszukiwać tej części, która posiada wymiary najbliższe do oczekiwanych, proces ten można zautomatyzować.
Najlepiej w takim przypadku skorzystać z formuł tablicowych i funkcji MODUŁ.LICZBY(), która będzie zwracała wartość bezwzględną z różnicy pomiędzy wymiarem wymaganym a sprawdzanym.
W tym celu:
Utwórz nowy arkusz, w którym będą wpisywane typy poszukiwanych części i ich wymiary. Kliknij ostatnią kartę arkusza, która nie ma nazwy.
W pierwszych dwóch wierszach wpisz nagłówki kolumn i sformatuj je zgodnie z rysunkiem.
W kolumnie A i B wpisz dane potrzebnych części, tj. typ części i szukany wymiar.
W komórce C3 wpisz formułę tablicową:
Linia 1. znak równości JEŻELI otwórz nawias okrągły MIN otwórz nawias okrągły JEŻELI otwórz nawias okrągły ‘Dostępne części’ wykrzyknik $B$2 dwukropek $B$200 otwórz nawias ostrokątny zamknij nawias ostrokątny $A3 średnik B3 średnik MODUŁ kropka LICZBY otwórz nawias okrągły ‘Dostępne części’ wykrzyknik $C$2 dwukropek $C$200 minus $B3 zamknij nawias okrągły zamknij nawias okrągły zamknij nawias okrągły znak równości B3 średnik cudzysłów minus minus minus cudzysłów średnik MIN otwórz nawias okrągły JEŻELI otwórz nawias okrągły ‘Dostępne części’ wykrzyknik $B$2 dwukropek $B$200 otwórz nawias ostrokątny zamknij nawias ostrokątny $A3 średnik B3 średnik MODUŁ kropka LICZBY otwórz nawias okrągły ‘Dostępne części’ wykrzyknik $C$2 dwukropek $C$200 minus $B3 zamknij nawias okrągły zamknij nawias okrągły zamknij nawias okrągły zamknij nawias okrągły.
Zatwierdź ją, naciskając jednocześnie klawisze [Ctrl]+[Shift]+[Enter], a nie sam klawisz [Enter] jak przy zwykłych formułach.
Formuła ta zwróci najmniejszą bezwzględną wartość różnicy pomiędzy wymiarem części danego typu a wymiarem określonym w kolumnie B. Jeśli w arkuszu Dostępne części nie będzie żadnej części danego typu, to formuła zwróci trzy myślniki.
Formuły tablicowe na pasku formuły są wyświetlane w nawiasach klamrowych. Jeżeli w arkuszu Dostępne części masz wypełnionych więcej niż 200 wierszy lub dane pierwszej części znajdują się w innym wierszu niż wiersz 2, to w formule zmień zakres B2:B200 i C2:C200, w taki sposób, aby obejmował wszystkie wiersze z danymi dostępnych części. Zakres ten może sięgać nawet dalej niż do ostatniego wypełnionego wiersza i uwzględniać przyszłe wpisy, ale pamiętaj, aby nie przesadzać, gdyż może to spowodować dłuższe przeliczenie arkusza. Wszystkie odwołania do komórek w kolumnie B i C w formule muszą zawierać dokładnie taką samą liczbę wierszy.
W komórce D3 wpisz formułę tablicową:
Linia 1. znak równości ADR kropka POŚR otwórz nawias okrągły ADRES otwórz nawias okrągły MIN otwórz nawias okrągły JEŻELI otwórz nawias okrągły MODUŁ kropka LICZBY otwórz nawias okrągły ‘Dostępne części’ wykrzyknik $C$2 dwukropek $C$200 minus $B3 zamknij nawias okrągły znak równości C3 średnik WIERSZ otwórz nawias okrągły $A$2 dwukropek $A$200 zamknij nawias okrągły średnik 65536 zamknij nawias okrągły zamknij nawias okrągły średnik 1 średnik średnik średnik cudzysłów Dostępne części cudzysłów zamknij nawias okrągły zamknij nawias okrągły.
Formuła ta zwróci pierwszy od góry numer fabryczny części danego typu, której wymiar różni się od wymiaru zadanego o wartość określoną w kolumnie C. W przypadku braku części danego typu formuła zwróci wartość 0.
Jeśli zmienisz nazwę arkusza Dostępne części, to musisz wpisać nową nazwę arkusza w 5. argumencie funkcji ADRES(), gdyż funkcjonuje ona tam jako wartość tekstowa i nie zostanie automatycznie zmieniona w taki sposób, jak to się dzieje przy odwołaniach.
W komórce E3 wpisz formułę zwykłą:
Linia 1. znak równości JEŻELI otwórz nawias okrągły D3 znak równości 0 średnik 0 średnik WYSZUKAJ kropka PIONOWO otwórz nawias okrągły D3 średnik ‘Dostępne części’ wykrzyknik $A$2 dwukropek $C$200 średnik 3 średnik FAŁSZ zamknij nawias okrągły zamknij nawias okrągły.
aby wyświetlić wymiar znalezionej części. W przypadku braku danego typu części zostanie zwrócona wartość zerowa.
Skopiuj formuły z komórek C3:E3 do komórek niżej, np. poprzez przeciągnięcie myszką tych komórek w dół.
Jeśli chcesz ukryć wartości zerowe mogące się pojawić w kolumnach D i E, gdy nie ma żadnej części danego rodzaju, to wybierz Przycisk pakietu Office lub rozwiń menu Plik, a następnie otwórz Opcje w zakładce Zaawansowane, w sekcji Opcje wyświetlania dla tego arkusza opcję Pokaż zero w komórkach o zerowej wartości zmień na niezaznaczoną.
Teraz możesz wpisywać nowe typy części i poszukiwane rozmiary w kolumnach A i B, a automatycznie w kolumnach C, D i E pojawią się informacje na temat najlepiej dopasowanej części. W arkuszu Dostępne części możesz dodawać nowe części i usuwać te, które zostaną przekazane do produkcji.
R1GsoOqTroASl
Nagranie filmowe dotyczące wyszukiwania wartości optymalnie dopasowanych do potrzeb.
Nagranie filmowe dotyczące wyszukiwania wartości optymalnie dopasowanych do potrzeb.
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.
Przygotuj arkusz zestawiający fragment oferty kilku lokalnych pizzerii. Następnie wykorzystując poznane funkcje, przedstaw pozycję najbliższą zadanej cenie i średnicy.
Przykładowe rozwiązanie zadania:
Arkusz „Pizza”:
Rdp1AanOgv7FJ
Na zrzucie ekranu widoczny jest fragment arkusza Excel. W kolumnie A, B i C wprowadzono dane dotyczące zestawienia wielkości i cen pizzy. W arkuszu kolejno dodano opisy: w komórce A1 Pizza, w komórce B1 Cena, w komórce C1 Średnica w centymetrach. W kolumnie A w komórkach od A2 do A8 wpisano nazwy pizz. W kolumnie B w komórkach od B2 do B8 wpisano cenę pizzy. W kolumnie C w komórkach od C2 do C8 wpisano wartość liczbową średnicy pizz.
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.
R1YbrIUwspZTX
Na zrzucie ekranu widoczny jest fragment arkusza Excel. W komórkach A i B wpisano dane dotyczące pizzy. W arkuszu kolejno dodano opisy: w komórce A2 Cena pizzy, w komórce B2 Oczekiwana średnia. W kolumnie A w komórce A3 wpisano wartość 14. W kolumnie B w komórce B3 wpisano wartość 25. Dodatkowo zaprezentowano sposób obliczania odchylenia. Formułę obliczania należy wpisać w komórce C3. Brzmi ona następująco: =JEŻELI(MIN(JEŻELI(Pizza!$B$2:$B$8<>$A3;B3;MODUŁ.LICZBY(Pizza!$C$2:$C$8-$B3)))=B3;"---";MIN(JEŻELI(Pizza!$B$2:$B$8<>$A3;B3;MODUŁ.LICZBY(Pizza!$C$2:$C$8-$B3)))).
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.
RlR5mMaKR0Pz0
Na zrzucie ekranu widoczny jest fragment arkusza Excel. W komórkach A, B i C wpisano dane dotyczące pizzy. W arkuszu kolejno dodano opisy: w komórce A2 Cena pizzy, w komórce B2 Oczekiwana średnia, w komórce C2 Odchylenie. W kolumnie A w komórce A3 wpisano wartość 14. W kolumnie B w komórce B3 wpisano wartość 25. W kolumnie C w komórce C3 wpisano wartość 5. Dodatkowo zaprezentowano sposób obliczania pasującej pizzy. Formułę obliczania należy wpisać w komórce D3. Brzmi ona następująco: =ADR.POŚR(ADRES(MIN(JEŻELI(MODUŁ.LICZBY(Pizza!$C$2:$C$2-$B3)=C3;WIERSZ($A$2:$A$3);65536));1;;;"Pizza")).
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.
Efekt końcowy:
R1UZNv5z2U4y8
Na zrzucie ekranu widoczny jest fragment arkusza Excel. W kolumnie A, B, C i D wprowadzono dane dotyczące wyników końcowych poszukiwanej pizzy. W arkuszu kolejno dodano opisy: w komórce A2 Cena pizzy, w komórce B2 Oczekiwana średnica, w komórce C2 Odchylenie, w komórce D2 Pasująca pizza. W kolumnie A, w komórce A3 wpisano kwotę za pizzę. W kolumnie B, w komórce B3 wpisano średnicę pizzy. W kolumnie C, w komórce C3 wpisano liczbę odchylenia. W kolumnie D, w komórce D3 wpisano nazwę pizzy.
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.
2
Ćwiczenie 2
Zmodyfikuj przygotowany arkusz. Na podstawie stosunku ceny do średnicy oblicz opłacalność każdego produktu, weź go pod uwagę obliczając optymalny wybór.
Przykładowe rozwiązanie zadania:
R1EyxZwuc1Wdq
Na zrzucie ekranu widoczny jest fragment arkusza Excel. W kolumnie A, B, C i D wprowadzono dane dotyczące zestawienia wielkości i cen różnych pizz. W arkuszu kolejno dodano opisy: w komórce A1 Pizza, w komórce B1 Cena, w komórce C1 Średnica w centymetrach, w komórce D1 Cena / średnica. W kolumnie A w komórkach od A2 do A8 wpisano nazwy pizz. W kolumnie B w komórkach od B2 do B8 wpisano ceny. W kolumnie C w komórkach od C2 do C8 wpisano wartość liczbową średnicy pizz. W kolumnie D w komórkach od D2 do D8 wpisano wartości liczbowe. Pod danymi komórkę A13 zatytułowano Oczekiwana cena, komórkę A14 Oczekiwana średnica, komórkę A15 Oczekiwany stosunek cena / średnica, komórkę C11 Łączne odchylenie. W kolumnie B13 wpisano kwotę 14 złotych. W kolumnie B w komórce B14 wpisano wartość 16. W kolumnie B w komórce B15 wpisano Możliwie najmniejszy. W kolumnie C w komórce C12 wpisano wartość. Dodatkowo zaprezentowano sposób obliczania najbardziej dopasowanej pizzy. Formułę obliczania należy wpisać w odpowiedniej komórce. W tym przypadku w komórce D12. Brzmi ona następująco: =ADR.POŚR(ADRES(MIN(JEŻELI(MODUŁ.LICZBY(B2:B8-B13)+MODUŁ.LICZBY(C2:C8-B14)+MODUŁ.LICZBY(D2:D8)=C12;WIERSZ(A2:A8);100));1;;;"Pizza")).
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.
Przykład zastosowania funkcji:
RQQ5HPqlbDWEX
Na zrzucie ekranu widoczny jest fragment arkusza Excel. W kolumnie A, B, C i D wprowadzono dane dotyczące zestawienia wielkości i cen różnych pizz. W arkuszu kolejno dodano opisy: w komórce A1 Pizza, w komórce B1 Cena, w komórce C1 Średnica w centymetrach, w komórce D1 Cena / średnica. W kolumnie A w komórkach od A2 do A8 wpisano nazwy pizz. W kolumnie B w komórkach od B2 do B8 wpisano ceny. W kolumnie C w komórkach od C2 do C8 wpisano wartość liczbową średnicy pizz. W kolumnie D w komórkach od D2 do D8 wpisano wartości liczbowe. Pod danymi komórkę A13 zatytułowano Oczekiwana cena, komórkę A14 Oczekiwana średnica, komórkę A15 Oczekiwany stosunek cena / średnica, komórkę C11 Łączne odchylenie, komórkę D11 Najbardziej dopasowana pizza. W kolumnie B w komórce B13 wpisano kwotę 14 złotych. W kolumnie B w komórce B14 wpisano wartość 16. W kolumnie B w komórce B15 wpisano Możliwie najmniejszy. W kolumnie C w komórce C12 wpisano wartość liczbową. W kolumnie D w komórce D12 wpisano nazwę pizzy.
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.
Drugi przykład zastosowania funkcji ze zmienionymi parametrami:
R13d4m9nlRj7V
Na zrzucie ekranu widoczny jest fragment arkusza Excel. W kolumnie A, B, C i D wprowadzono dane dotyczące zestawienia wielkości i cen różnych pizz. W arkuszu kolejno dodano opisy: w komórce A1 Pizza, w komórce B1 Cena, w komórce C1 Średnica w centymetrach, w komórce D1 Cena / średnica. W kolumnie A w komórkach od A2 do A8 wpisano nazwy pizz. W kolumnie B w komórkach od B2 do B8 wpisano ceny. W kolumnie C w komórkach od C2 do C8 wpisano wartość liczbową średnicy pizz. W kolumnie D w komórkach od D2 do D8 wpisano wartości liczbowe. Pod danymi komórkę A13 zatytułowano Oczekiwana cena, komórkę A14 Oczekiwana średnica, komórkę A15 Oczekiwany stosunek cena / średnica, komórkę C11 Łączne odchylenie, komórkę D11 Najbardziej dopasowana pizza. W kolumnie B13 wpisano kwotę 20 złotych. W kolumnie B w komórce B14 wpisano wartość 20. W kolumnie B w komórce B15 wpisano Możliwie najmniejszy. W kolumnie C12 wpisano wartość liczbową. W kolumnie D w komórce D12 wpisano nazwę pizzy.
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.
3
Ćwiczenie 3
Ponownie zmodyfikuj arkusz. Wykonując obliczenia optymalnego wyboru weź też pod uwagę listę składników każdej pizzy.
Przykładowe rozwiązanie zadania:
Metoda obliczenia ilości składników:
RCEhqiMhP11DU
Na zrzucie ekranu widoczny jest fragment arkusza Excel. W kolumnie A, B, C, D, E i F wprowadzono dane dotyczące ilości składników różnych rodzajów pizzy wraz z cenami i wielkością. W arkuszu kolejno dodano opisy: w komórce A1 Średnica / cena, w komórce B1 Składniki, w komórce C1 Ilość składników, w komórce D1 Pizza, w komórce E1 Cena, w komórce F1 Średnica w centymetrach. W kolumnie A w komórkach od A2 do A8 wpisano wartość liczbową. W kolumnie B w komórkach od B2 do B8 wpisano nazwy składników pizzy. W kolumnie C w komórkach od C2 do C7 wpisano wartości liczbowe. W kolumnie D w komórkach od D2 do D7 wpisano nazwy pizz. W kolumnie E w komórkach od E2 do E8 wpisano ceny. W kolumnie F w komórkach od F2 do F8 wpisano wartości liczbowe średnicy. Dodatkowo zaprezentowano sposób obliczania optymalnego wyboru. Formułę obliczania należy wpisać w komórce C8. Brzmi ona następująco: =DŁ(B8)-DŁ(PODSTAWA(B8;";";""))+1.
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.
