Excel zadania z rozwiązaniami

Czytanie o funkcjach Excela daje mniej niż godzina z prawdziwym arkuszem. Dlatego tutaj są zadania z rozwiązaniami: pobierasz plik, rozwiązujesz sam, a arkusz od razu pokazuje, czy wynik się zgadza.

Zadań jest sześć, wszystkie na jednej tabeli sprzętu RTV i AGD. Pierwsze trzy są na start: wyszukiwanie, rozdzielanie danych i podsumowanie z paskami danych. Kolejne trzy, w drugiej części, są dla średniozaawansowanych. Przy każdym zadaniu jest treść, rozwiązanie krok po kroku i film.

Jest jedno utrudnienie: arkusz z zadaniami jest zablokowany i część poleceń, po które zwykle sięgasz, nie działa. Trzeba poszukać innej drogi, i właśnie tego te zadania uczą najwięcej.

W tym poradniku:

1. Dane do zadań i zablokowany arkusz
2. Zadanie nr 1: Oblicz wartość zamówionych produktów (część 1)
3. Zadanie nr 2: Rozdziel wymiary i oblicz objętość (część 1)
4. Zadanie nr 3: Wartość produktów w podziale na kategorie (część 1)
5. Zadanie nr 4: Wartość sprzedaży z warunkiem (część 2)
6. Zadanie nr 5: Nazwa, kod i cena w jednej komórce (część 2)
7. Zadanie nr 6: Kod produktu na dwa sposoby (część 2)
8. Podsumowanie i pliki do pobrania

Dane do zadań i zablokowany arkusz

Wszystkie zadania działają na jednej tabeli sprzętu RTV i AGD z kolumnami: kod produktu, kategoria, nazwa, cena i wymiary opakowania. W pliku do pobrania arkusz ZADANIA jest zablokowany do edycji. Wpisywać można tylko w komórki zaznaczone na szaro, a część poleceń Excela jest niedostępna. W arkuszu ROZWIĄZANIE są gotowe formuły do porównania.

Pod każdym zadaniem stoi komórka kontrolna. Gdy wynik jest dobry, pokazuje „wynik prawidłowy. Gratulacje!”.

Zadanie nr 1: Oblicz wartość zamówionych produktów

Masz listę zamówień: kod produktu i ilość. Uzupełnij nazwę produktu i wartość zamówienia, czyli cenę pomnożoną przez ilość. Część kodów celowo nie istnieje w tabeli.

Nazwa produktu po kodzie

Nazwę po kodzie znajdzie WYSZUKAJ.PIONOWO (VLOOKUP). Szuka kodu z komórki G6 w pierwszej kolumnie tabeli A5:E25 i zwraca wartość z trzeciej kolumny, czyli nazwę. FAŁSZ na końcu oznacza dopasowanie dokładne.

=WYSZUKAJ.PIONOWO(G6;$A$5:$E$25;3;FAŁSZ)

Zakres tabeli ma znaki $, żeby po skopiowaniu formuły w dół się nie przesuwał. Po skopiowaniu dla wszystkich zamówień przy kodach, których nie ma w tabeli, pojawia się błąd #N/D.

Więcej o tej funkcji jest w poradniku WYSZUKAJ.PIONOWO w Excelu.

Wartość zamówienia

Ta sama funkcja, tylko z czwartą kolumną (cena), a wynik mnożymy przez ilość z kolumny H:

=WYSZUKAJ.PIONOWO(G6;$A$5:$E$25;4;FAŁSZ)*H6

Problem widać w podsumowaniu: jeden błąd #N/D w kolumnie wystarczy, żeby suma też była błędem.

Błąd zamieniony na myślnik i zero

Błąd przechwyci funkcja JEŻELI.BŁĄD (IFERROR). Pierwszy argument to formuła, która może zwrócić błąd, drugi to wartość pokazywana zamiast błędu. W kolumnie Produkt będzie to myślnik:

=JEŻELI.BŁĄD(WYSZUKAJ.PIONOWO(G6;$A$5:$E$25;3;FAŁSZ);"-")

W kolumnie Wartość zero, żeby podsumowanie się policzyło:

=JEŻELI.BŁĄD(WYSZUKAJ.PIONOWO(G6;$A$5:$E$25;4;FAŁSZ)*H6;0)

Suma wartości w poprawnym rozwiązaniu wynosi 52 690. O innych sposobach obsługi błędów jest w poradniku o błędach w Excelu.

Ponad 180 ćwiczeń z Excela

✓Zeszyt ćwiczeń dostosowany do Ciebie - mnóstwo zadań na dwóch poziomach

✓Śledź swoje postępy w arkuszu tytułowym

✓Uproszczona nawigacja po całym pliku

✓Bezterminowy dostęp do ćwiczeń

Zobacz więcej

Zadanie nr 2: Rozdziel wymiary i oblicz objętość

Wymiary opakowania stoją w jednej komórce, na przykład „60 cm x 60 cm x 85 cm”. Rozdziel je na szerokość, wysokość i głębokość w centymetrach, a potem oblicz objętość w metrach sześciennych.

Zadanie wygląda na proste, ale arkusz jest zablokowany. Tekst jako kolumny i Zamień nie zadziałają. Zadziała za to wypełnianie błyskawiczne, które uzupełnia kolumnę na podstawie wzoru z kilku wpisanych ręcznie przykładów.

1. Skopiuj wymiary opakowania do szarej kolumny obok, bo wypełnianie błyskawiczne bierze wzór z sąsiednich komórek.
2. W kolumnie szerokość wpisz ręcznie dwie pierwsze wartości, czyli 60 i 60.
3. Zaznacz komórkę pod nimi i wybierz Dane > Wypełnianie błyskawiczne albo naciśnij Ctrl + E. Excel uzupełni pozostałe szerokości.
4. Tak samo uzupełnij wysokość i głębokość.

Objętość to iloczyn trzech wymiarów. Wymiary są w centymetrach, więc wynik mnożymy przez 0,000001, żeby dostać metry sześcienne (1 m³ to 1 000 000 cm³):

=N6*O6*P6*0,000001

Suma objętości w poprawnym rozwiązaniu wynosi 2,308 m³. Więcej o wypełnianiu błyskawicznym i innych skrótach jest w poradniku o skrótach klawiszowych w Excelu.

Zdobądź jedną z najważniejszych umiejętności w pracy biurowej - naucz się Excela od zera

✓40 lekcji online - krok po kroku, bez chaosu

✓52 ćwiczenia z rozwiązaniami i nagraniami

✓Certyfikat ukończenia + bonusy o wartości 48 zł

✓Gwarancja zwrotu 30 dni - bez pytań

Zobacz więcej

Zadanie nr 3: Wartość produktów w podziale na kategorie

Policz łączną wartość produktów w każdej kategorii, a potem udział każdej kategorii w całości. Udział pokaż jako zielony pasek danych, bez wartości procentowej w komórce.

Wartość kategorii

Sumę z warunkiem liczy SUMA.JEŻELI (SUMIF). Ma trzy argumenty:

=SUMA.JEŻELI(zakres; kryteria; [suma_zakres])

• zakresKomórki sprawdzane pod kątem warunku, tutaj kolumna z kategoriami.
• kryteriaWarunek: liczba, tekst, odwołanie do komórki albo wyrażenie. Tekst i wyrażenia z operatorami (np. ">100") bierzemy w cudzysłów, same liczby nie.
• suma_zakresOpcjonalny. Komórki do zsumowania, gdy są inne niż sprawdzane, tutaj kolumna z ceną.

W pierwszym wierszu kolumny Wartość:

=SUMA.JEŻELI($B$6:$B$25;S6;$D$6:$D$25)

Funkcja sprawdza, w których wierszach kolumny B stoi kategoria z komórki S6, i sumuje ceny z kolumny D z tych wierszy. Więcej przykładów jest w poradniku o funkcji SUMA.JEŻELI.

Udział procentowy jako pasek danych

Udział to wartość kategorii podzielona przez sumę wszystkich kategorii. Komórka z sumą musi być zablokowana znakami $, żeby po skopiowaniu formuły w dół się nie przesunęła:

=T6/$T$16

1. Zaznacz kolumnę z udziałem i wybierz Narzędzia główne > Formatowanie warunkowe > Paski danych, zielony pasek.
2. Nie zdejmując zaznaczenia, otwórz Formatowanie warunkowe > Zarządzaj regułami, zaznacz regułę i kliknij Edytuj regułę.
3. Zaznacz Pokaż tylko pasek i zatwierdź.

W komórkach zostaną same paski, bez liczb. Udziały w poprawnym rozwiązaniu sumują się do 100%. Więcej o paskach danych i innych regułach jest w poradniku o formatowaniu warunkowym.

Zadanie nr 4: Wartość sprzedaży z warunkiem

Od tego zadania zaczyna się część 2: trzy zadania dla średniozaawansowanych na tabeli produktów AGD z kodem, kategorią, nazwą, ceną i objętością opakowania. Plik do tej części jest pod artykułem, a na filmie rozwiązuję wszystkie trzy zadania krok po kroku.

Oblicz wartość sprzedanych produktów, których opakowanie ma objętość większą niż 0,1 m³. Potem zaznacz automatycznie na zielono trzy największe wartości.

Podpowiedź: cenę produktu z warunkiem na objętość da SUMA.WARUNKÓW (SUMIFS), a trzy największe wyniki zaznaczy reguła formatowania warunkowego, bez żadnej formuły.

Rozwiązanie zadania 4

Dla każdego produktu z listy (nazwa w kolumnie G, ilość w H) SUMA.WARUNKÓW bierze cenę z kolumny D, ale tylko wtedy, gdy nazwa się zgadza, a objętość w kolumnie E przekracza 0,1. Wynik mnożymy przez ilość:

=SUMA.WARUNKÓW($D$6:$D$25;$C$6:$C$25;G6;$E$6:$E$25;">0,1")*H6

Produkty o mniejszym opakowaniu dostają zero. Suma wszystkich wyników to 74 900. Trzy największe wartości zaznacza reguła Formatowanie warunkowe > Reguły pierwszych/ostatnich > 10 pierwszych elementów z liczbą zmienioną na 3. Więcej o tej regule w poradniku o formatowaniu warunkowym.

Zadanie nr 5: Nazwa, kod i cena w jednej komórce

Połącz w jednej komórce nazwę produktu, kod i cenę, tak jak w przykładzie:

Pralka automatyczna | P3947 | 1 500,00 zł

Podpowiedź: samo połączenie znakiem & da cenę 1500 bez separatora i bez waluty. Format komórki przy łączeniu z tekstem znika.

Rozwiązanie zadania 5

Funkcja TEKST (TEXT) nadaje cenie wygląd, który zginąłby przy łączeniu:

=C6&" | "&A6&" | "&TEKST(D6;"# ##0,00 zł")

Drugi argument funkcji TEKST to ten sam kod, który wpisuje się w niestandardowym formatowaniu komórek. Więcej sposobów łączenia jest w poradniku o łączeniu tekstu w Excelu.

Zadanie nr 6: Kod produktu na dwa sposoby

Dla wskazanych produktów podaj kod produktu, na dwa różne sposoby. W kolejnej kolumnie zostaw z kodu sam numer (z „P3947” zostaje „3947”) i zaznacz numery większe niż 7000.

Podpowiedź: nazwa produktu stoi w tabeli na prawo od kodu, więc WYSZUKAJ.PIONOWO tu nie wystarczy.

Rozwiązanie zadania 6

Pierwszy sposób to INDEKS (INDEX) z PODAJ.POZYCJĘ (MATCH): PODAJ.POZYCJĘ znajduje wiersz z nazwą produktu, a INDEKS zwraca kod z tego wiersza.

=INDEKS($A$6:$A$25;PODAJ.POZYCJĘ(O6;$C$6:$C$25;0))

Drugi sposób to X.WYSZUKAJ (XLOOKUP), która szuka w dowolną stronę, także w lewo:

=X.WYSZUKAJ(O12;$C$6:$C$25;$A$6:$A$25)

Obie funkcje opisuję osobno: INDEKS i PODAJ.POZYCJĘ oraz X.WYSZUKAJ.

Numer z kodu daje PRAWY, która bierze cztery ostatnie znaki. Wynik jest jednak tekstem, a tekstu reguła „większe niż 7000” nie zaznaczy. Dlatego PRAWY jest owinięta w WARTOŚĆ, która zamienia tekst na liczbę:

=WARTOŚĆ(PRAWY(P6;4))

Numery powyżej 7000 zaznacza reguła Formatowanie warunkowe > Reguły wyróżniania komórek > Większe niż. Suma numerów w poprawnym rozwiązaniu wynosi 50 969.

Podsumowanie i pliki do pobrania

Sześć zadań przeprowadziło Cię przez funkcje i polecenia, po które w pracy sięga się najczęściej:

• wyszukiwanie - WYSZUKAJ.PIONOWO, INDEKS z PODAJ.POZYCJĘ i X.WYSZUKAJ (zadania 1 i 6),
• obsługa błędów - JEŻELI.BŁĄD zamiast #N/D w podsumowaniu (zadanie 1),
• porządkowanie danych - wypełnianie błyskawiczne w zablokowanym arkuszu (zadanie 2),
• sumy z warunkiem - SUMA.JEŻELI i SUMA.WARUNKÓW (zadania 3 i 4),
• tekst i liczby - łączenie z funkcją TEKST i zamiana tekstu na liczbę funkcją WARTOŚĆ (zadania 5 i 6),
• formatowanie warunkowe - paski danych, największe wartości i reguła „większe niż” (zadania 3, 4 i 6).

Pod artykułem są dwa pliki: część 1 z zadaniami 1-3 i część 2 z zadaniami 4-6. W każdym jest arkusz ZADANIA do samodzielnego rozwiązania, z komórkami kontrolnymi, oraz arkusz ROZWIĄZANIE z gotowymi formułami. Najwięcej zyskasz, gdy zajrzysz do rozwiązania dopiero po własnej próbie.

Ponad 180 ćwiczeń z Excela

✓Zeszyt ćwiczeń dostosowany do Ciebie - mnóstwo zadań na dwóch poziomach

✓Śledź swoje postępy w arkuszu tytułowym

✓Uproszczona nawigacja po całym pliku

✓Bezterminowy dostęp do ćwiczeń

Zobacz więcej