Excel Zadania z Rozwiązaniami – część 1
Praktyka czyni mistrza czyli rozwiązujemy praktyczne zadania w Excelu. W dalszej części przedstawię rozwiązania dla trzech zadań w których zastosujemy kilka przydatnych funkcji, poleceń i formatowanie warunkowe. Pobierz plik i rozwiąż zadania samodzielnie.
W dalszej części:
Wstęp do rozwiązywania zadań z Excela
Wszystkie zadania będziemy realizować na podstawie zakresu danych dla sprzętów RTV i AGD zawierającego kod produktu, kategorie, nazwę produktu, cenę, i wymiary opakowania. Dodatkowym utrudnieniem jest to, że plik z zadaniami do samodzielnego wykonania, który znajdziesz na końcu tego artykuły jest zablokowany do edycji a dane można wpisywać tylko w komórki zaznaczone na szaro. Zwróć uwagę, że zablokowanie arkusza nie pozwalana na użycie niektórych poleceń i dlatego zadania robią się ciekawe 🙂
Zadanie nr 1: Excel zadania z rozwiązaniami – Oblicz wartość zamówionych produktów
Na podstawie kodu produktu i ilości mamy za zadanie obliczyć wartość dla tych produktów.
W kolumnie Produkt będziemy chcieli przypisać nazwę produktu na podstawie kolumny Kod Produktu. Do tego zastosujemy funkcję WYSZUKAJ.PIONOWO(), która przybiera poniższą postać:
Po przekopiowaniu funkcji dla wszystkich produktów otrzymamy efekt jak poniżej:
Opis funkcji będę przedstawiał na podstawie kolorystyki podświetlonych danych.
Funkcja wyszukuje niebieską komórkę, w czerwonym zakresie danych, jeśli znajdziemy kod produktu zwracamy wartość znajdującą się w 3 kolumnie zakresu czyli Produkt. Argument FAŁSZ oznacza, że chcemy wyszukać dokładny kod produktu. Jeśli funkcja nie znajdzie kodu produktu zwróci błąd #N/D.
Więcej o funkcji WYSZUKAJ.PIONOWO() dowiesz się z artykułu WYSZUKAJ.PIONOWO w Excelu – Dopasowanie Dokładne i Przybliżone.
Jeśli nie wiesz dlaczego w zakresie danych wprowadzono znak $ zobacz artykuł Jak Zablokować Komórkę w Excelu. Znak $ w Formułach
Tą samą funkcję zastosujemy w kolumnie Wartość zmieniając tylko numer kolumny na 4 i wynik funkcji wymnożymy przez Ilość.
A po przekopiowaniu funkcji dla pozostałych wartości otrzymamy taki efekt
Funkcja WYSZUKAJ.PIONOWO po znalezieniu Wartości (4 kolumna czerwonego zakresu) dla Kodu Produktu (niebieska komórka) zostaje wymnożona przez Ilość (fioletowa komórka)
Zobacz, że jeśli funkcja zwróciła błąd to ten błąd też będzie w podsumowaniu i wynik też jest błędem. Wobec tego naprawmy ten błąd. W tym przypadku świetnie sprawdzi się funkcja JEŻELI.BŁĄD()
JEŻELI.BŁĄD(wartość; wartość_jeżeli_błąd)
W funkcji JEŻELI.BŁĄD musimy podać dwa argumenty:
- wartość – sprawdzana w poszukiwaniu błędu.
- wartość_jeżeli_błąd – wartość, która ma zostać zwrócona, jeśli w pierwszym argumencie jest błąd.
Zmodyfikujmy formuły dla kolumny Produkt i Wartość poprzez dodanie funkcji JEŻELI.BŁĄD() gdzie:
- pierwszym argumentem będzie wynik funkcji WYSZUKAJ.PIONOWO()
- a drugim argumentem to co ma pojawiać się zamiast błędu czyli dla Produktu wpiszemy myślnik „-”
a dla Wartości 0
Efektem finalnym jest prawidłowy wynik 🙂
Zadanie nr 2: Excel zadania z rozwiązaniami – Przekopuj i przekształć
W drugim zadaniu musimy podzielić wymiary opakowania do oddzielnych kolumn szerokość / wysokość / głębokość wyrażone w cm a następnie obliczyć objętość wyrażoną w m3
Zadanie wydaje się proste jeśli nie weźmiemy pod uwagę, że arkusz jest zablokowany do edycji (tylko szare komórki są odblokowane) w takiej sytuacji, że możemy skorzystać Tekst jako kolumny ani Zamień
Poleceniem, które możemy za to wykorzystać jest Wypełnienie błyskawiczne znajdujące się na karcie Dane.
Te polecenie możemy zastosować do danych znajdujących obok komórki w której wykonujemy polecenie. Zatem przekopiujmy do szarej kolumny Wymiary opakowania
Następnie w kolumnie szerokość wypełnijmy samodzielnie dwie pierwsze pozycje czyli 60 i 60
Następnie mając zaznaczoną komórkę poniżej wybieramy polecenie Dane > Wypełnienie błyskawiczne
Polecenie wypełniło nam inteligentnie pozostałe szerokości.
Analogicznie podajemy wysokość i głębokość. Polecenie Wypełnienia błyskawicznego możemy też uruchomić za pomocą skrótu klawiszowego CTRL+E
Gdy mamy już wszystkie wymiary obliczamy objętość poprzez wymnożenie trzech wielkości.
Uzyskane wyniki musimy wymnożyć razy 0,000001 aby wynik wyświetlany był w m3
Otrzymaliśmy prawidłowy wynik równy 2,308 m3
Zadanie nr 3: Excel zadania z rozwiązaniami – Oblicz objętość produktów w podziale na kategorie
W trzecim zadaniu chcemy obliczyć wartość wszystkich produktów w podziale na kategorie, następnie przedstawienie udziału procentowego wartości w stosunku do sumy wartości jako zielony pasek danych (chcemy pokazać sam pasek danych bez wartości procentowej)
Do obliczenia Wartości zastosujemy funkcję SUMA.JEŻELI(). Funkcja ta składa się 3 argumentów.
SUMA.JEŻELI(zakres; kryteria; [suma_zakres])
- zakres – jest to zakres komórek, do których zostaną zastosowane kryteria.
- kryteria – kryteria w postaci liczby, wyrażenia, odwołania do komórki, tekstu lub funkcji określającej, które komórki będą sumowane. Wszelkie kryteria tekstowe oraz zawierające symbole matematyczne lub logiczne należy ująć w podwójny cudzysłów („). Kryteria liczbowe nie wymagają cudzysłowów.
- suma_zakres – argument niewymagany. Jest to zakres komórek, który podlega sumowaniu w przypadku, gdy należy zsumować komórki inne niż określone w argumencie zakres.
W pierwszej pozycji kolumny Wartość wprowadzamy funkcję:
- pierwszym argumentem jest zakres niebieski z kategoriami produktów
- w drugim argumencie podajemy kategorię (czerwona komórka), która będzie naszym kryterium.
- trzecim argumentem jest zakres fioletowych komórek
funkcja sprawdza czy w niebieskim zakresie występuje wartość czerwonej komórki – jeśli tak sumuje wartości komórek fioletowego zakresu.
Po przeciągnięciu funkcji dla pozostałych pozycji otrzymamy poniższy efekt
Aby obliczyć udział procentowy podzielimy wartość dla kategorii przez sumę wartości wszystkich kategorii oczywiście pamiętając o zablokowaniu możliwości przesunięcia czerwonej komórki.
Po zaznaczeniu całego zakresu danych z udziałem procentowym wstawimy zielony pasek danych formatowania warunkowego karta Narzędzia główne > Formatowanie warunkowe > Paski danych
A następnie aby usunąć wartości procentowe po zaznaczeniu zakresu danych przechodzimy do Menadżera reguł formatowania warunkowego wybieramy kartę Narzędzia główne > Formatowanie warunkowe > Zarządzaj Regułami
Następnie po zaznaczeniu reguły klikam Edytuj regułę
W nowym oknie zaznaczam opcję Pokaż tylko pasek
Po zatwierdzeniu reguły formatowania warunkowego, na pasku danych nie widzimy wartości procentowej a efekt końcowy prezentuje się następująco.
Tutaj pobierz plik do tego poradnika
Inne poradniki
Excel Zadania z Rozwiązaniami – część 2
Jeśli szukasz sposobu, by doskonalić swoje umiejętności w programie Excel, to ten film jest właśnie dla Ciebie! „Excel – Zadania z Rozwiązaniami” to krok po kroku wprowadzenie do rozwiązywania różnorodnych problemów, z jakimi można spotkać się na co dzień, pracując z danymi w Excelu. Podczas trwania filmu poznasz przydatne funkcje oraz techniki, które z pewnością ułatwią Ci pracę z arkuszami kalkulacyjnymi.
Dynamiczne Funkcje Tablicowe w Excelu 365
Dynamiczne funkcje tablicowe zmieniają reguł gry i zasady pracy w Excelu. Jeśli posiadasz Excela w wersji 365 musisz poznać te nowe funkcje. Jest to funkcja FILTRUJ, SORTUJ, SORTUJ.WEDŁUG, UNIKATOWE, SEKWENCJA i LOSOWA.TABLICA. Funkcje tablicowe potrafią znacznie uprościć pracę na danych przedstawionych w tabelach i zakresach.
Odkryj Potęgę Funkcji X.Wyszukaj w Excelu 365
Poszukujesz skutecznego narzędzia w Excelu, które ułatwi ci znajdowanie danych? Funkcja X.WYSZUKAJ może być kluczem do rozwiązania tego problemu. W tym artykule przeanalizujemy, jak wykorzystać tę funkcję i jakie korzyści możemy z niej czerpać.
Funkcja JEŻELI Excel, JEŻELI z wieloma warunkami, ORAZ, LUB
Funkcja JEŻELI w Excelu jest jedną z najczęściej stosowanych funkcji a zarazem bardzo prostą w zrozumieniu. W tym wpisie zobaczysz jak używać logicznej funkcji JEŻELI dla prostych sytuacji. Następne przejdziemy do bardziej skomplikowanych metod m.in. zastosowanie funkcji JEŻELI z wieloma warunkami czy też połączenie tej funkcji z innymi funkcjami tj. ORAZ i LUB.
Funkcja INDEKS i PODAJ.POZYCJE w Excelu
Funkcja INDEKS w Excelu podaje wartości na przecięciu wskazanego wiersza i kolumny. W tym artykule przedstawię przykład zastosowania samej funkcji INDEKS oraz bardzo często używanego połączenia jej z funkcją PODAJ.POZYCJĘ. Takie połączenie funkcji INDEKS i PODAJ.POZYCJĘ pozwala nam na wyszukiwanie na podstawie dwóch warunków. Na koniec przedstawię drugi wariant funkcji INDEKS.
WYSZUKAJ.PIONOWO w Excelu – Dopasowanie Dokładne i Przybliżone.
Jak wyszukać komórkę w Excelu? Wykorzystaj do tego niezawodną funkcję WYSZUKAJ.PIONOWO. Funkcja dopuszcza wyszukiwanie wartości w sposób dokładny lub przybliżony. Z tego artykułu dowiesz się jak krok po kroku zastosować funkcję WYSZUKAJ.PIONOWO. Zobacz która metodę wyszukiwania wybrać dla konkretnego przypadku.
do osób, które chcą nauczyć się podstaw Excela za darmo w mniej niż godzinę.
Darmowy Kurs Excel dla początkujących