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.
- Zadanie nr 1: Excel zadania z rozwiązaniami – Oblicz wartość zamówionych produktów
- Zadanie nr 2: Excel zadania z rozwiązaniami – Przekopuj i przekształć
- Zadanie nr 3: Excel zadania z rozwiązaniami – Oblicz objętość produktów w podziale na kategorie
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.
Jak nauczyć się Excela ?
Dla osób, które chcą przypomnieć sobie podstawy Excela oraz nauczyć się nowych trików przyspieszających pracę przygotowałem darmowe szkolenie online.
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 ćwiczenia:
Kurs Excel
Chcesz dobrze nauczyć się Excela ? Nie trać więcej czasu na rzeczy które możesz wykonać jednym kliknięciem. Prawie 10 godzin praktycznej wiedzy, w której przedstawiam najprzydatniejsze możliwości Excela. Wszystkie 72 lekcje wideo oparte są na przykładach podsumowanych w ebooku. Dodatkowo 62 zadania do samodzielnego wykonania.
