Opublikowano

Test z Excela podczas rozmowy o pracę.

Pobierz przykładowe zadania rekrutacyjne 

Przykadowe zadania rekrutacyjne – wersja angielska

Wielu pracodawców wymaga od kandydatów znajomości programu MS Excel. W zależności od stanowiska oczekuje się mniej lub bardziej zaawansowanej znajomości tego programu.

W CV lub liście motywacyjnym nie warto kłamać, na temat posiadanych umiejętności, gdyż coraz częściej jednym z elementów rekrutacji jest test sprawdzający znajomość Excela.

Testy sprawdzające znajomość programu wyglądają podobnie. Składają się z kilku zadań. Dane, przygotowane są w arkuszach, a polecenia mogą być przekazane ustnie lub będą wydrukowane.

Warto podkreślić, że testy te odbywają się pod presją czasu, zwykle na rozwiązanie zadań kandydat ma od 15 do 30 minut.

Trudno przewidzieć, jakich pytań oczekiwać w czasie testu, wszystko zależy od stanowiska, na jakie odbywa się rekrutacja. Jednak na podstawie rozmów z osobami szukającymi pracy wyłania się pewien standardowy zestaw zagadnień pojawiających się w czasie testów, a są to:

  • Tabele przestawne (ang. Pivot Table),
  • Funkcja WYSZUKAJ.PIONOWO (ang. VLOOKUP),
  • Funkcja JEŻELI (ang. IF),
  • Umiejętność zamrażania adresów komórek (tzw. dolary) – często kandydaci proszeni są o zrobienie w Excelu Tabliczki Mnożenia.

Tabliczkę mnożenia jest w stanie bezbłędnie przy pierwszym podejściu wykonać mniej niż 20% osób!

Należy też uważać na pułapki. Do najczęstszych należą liczby w formacie tekstowym, brakujące nagłówki kolumn w zakresie danych źródłowych dla tabeli przestawnej, a w przypadku funkcji WYSZUKAJ.PIONOWO zbędne spacje oraz powtarzające się rekordy.

Poniżej zamieszczony został przykładowy plik z zadaniami oraz link do strony z testem znajomości programu MS Excel.

http://testy.forumszkoleniowe.com.pl/

Najlepszym sposobem przygotowania się do testu jest wykonanie jak największej ilości ćwiczeń. W Internecie można znaleźć wiele bezpłatnych materiałów.

Dobrym rozwiązaniem jest zapisanie się na intensywne szkolenie, np. na poziomie średniozaawansowanym. Na szkoleniu można zawsze zadać trenerowi pytania, poprosić o dodatkowe wyjaśnienia, wymienić swoje spostrzeżenia i doświadczenia z innymi uczestnikami, co jest niewątpliwą przewagą szkoleń nad nauką indywidualną, co więcej, często uczestnicy takich szkoleń mogą kontaktować się z trenerami przed i po zakończeniu kursu. Dodatkowo wpisane w CV szkolenie może wyróżnić Ciebie z pośród innych kandydatów.

Powodzenia w czasie rekrutacji! 


ebook Excel 32 Zadania Rekrutacyjne z rozwiązaniami krok po kroku


ebook Excel 32 Zadania Rekrutacyjne z rozwiązaniami krok po kroku

[related_posts limit=”5″]

Opublikowano

Jak w Excelu wstawić zero przed liczbą

Excel automatycznie usuwa zera wpisane przed liczbą. Istnieje jednak klika sposobów na wyświetlenie zera przed liczbą.

Format tekstowy

Kliknij pustą komórkę, a następnie zmień jej format z okólnego na tekstowy. W tym celu na wstążce programu przejdź do karty Narzędzia główne. Rozwiń listę zawierającą różne sposoby formatowania komórek i wybierz format tekstowy.

Obraz zawierający zrzut ekranu Opis wygenerowany automatycznie

Do sformatowanej komórki wpisz dowolna liczbę, poprzedzając ją zerem np. 01. Zauważ, że w komórce pojawił się zielony trójkąt. Jest to sygnał, że wprowadzona do komórki liczba przechowywana jest jako tekst i nie będzie jej można wykorzystać w obliczeniach.

Dodatkowo wprowadzona wartość jest wyrównana do lewej krawędzi komórki. Do lewej krawędzi komórki wyrównywany jest automatycznie tekst, a wartości liczbowe wyrównywane są automatycznie do prawej krawędzi komórki.

Obraz zawierający niebo, zdjęcie Opis wygenerowany automatycznie

Zacznij wpisywanie od apostrofa

Jeżeli chcesz uniknąć nadawania komórce formatu tekstowego, możesz wprowadzić do komórki liczbę poprzedzoną zerem, jednak musisz pamiętać, aby na początku wpisać apostrof.

‘031

Obraz zawierający zrzut ekranu Opis wygenerowany automatycznie

Tak wprowadzona wartość liczbowa będzie również przechowywana w komórce w formacie tekstowym.

Formatowanie komórek

Innym sposobem pozwalającym na wyświetlenie w Excelu zera przed liczbą jest zastosowanie formatowania niestandardowego komórek. Sposób ten pozwala na zachowanie formatu liczbowego i wykorzystanie wprowadzonych wartości w obliczeniach.

Do komórek wprowadź kilka wartości liczbowych np.: 12, 15, 31.

Obraz zawierający zrzut ekranu Opis wygenerowany automatycznie

Zaznacz komórki, a następnie kliknij zaznaczony zakres komórek prawym klawiszem myszki. Z wyświetlonej listy wybierz opcję Formatuj komórki.

W oknie Formatowania komórek w karcie Liczby przejdź do kategorii Niestandardowej. Skasuj zawartość pola Typ, a następnie wpisz w to pole trzy zera. Zatwierdź zmiany klikając przycisk Ok. W rezultacie wszystkie wpisane do komórek liczby zostaną poprzedzone zerami. Należy pamiętać, że zera są tylko wyświetlane w komórce jako efekt formatowania, a nie zostały tam wprowadzone jako wartości.

Obraz zawierający zrzut ekranu Opis wygenerowany automatycznie

Analogicznie, aby wyświetlić zero przed liczbą jednocyfrową wpisz dwa zera, aby wyświetlić zero przed liczbą dwucyfrową wpisz trzy zera, itp. W ten sam sposób możesz wyświetlić dowolną liczbę zer przed liczbą. Zera wyświetlane przed liczbami określane są czasem jako zera wiodące. Wykorzystuje się je np. aby uzupełnić brakujące cyfry w numerach dokumentów.

Funkcja TEKST

Zero przed liczbę w Excelu można wstawić także korzystając z wbudowanej funkcji TEKST.

Wpisz do pustych komórek klika liczb, tak jak na rysunku poniżej. Kliknij na pustą komórkę obok pierwszej z liczb. Na wstążce programu przejdź od karty Formuły, a następnie rozwiń listę dostępnych formuł tekstowych. Z listy wybierz funkcję TEKST. Funkcja TEKST zwraca liczbę w formacie tekstowym, sformatowaną według zadanego kodu formatowania. Kod formatowania będzie analogiczny, jak kod użyty przy niestandardowym formatowaniu liczby w okienku Formatowania komórek.

Obraz zawierający zrzut ekranu Opis wygenerowany automatycznie

Na ekranie zostanie wyświetlone okienko argumentów funkcji. W polu wartość wprowadź adres komórki zawierającej pierwszą z liczb. W polu Format liczby w cudzysłowie wpisz pięć zer. Tak jak na rysunku poniżej. Zatwierdź argumenty funkcji klikając przycisk Ok.

Obraz zawierający zrzut ekranu Opis wygenerowany automatycznie

W efekcie działania formuły, wszystkie liczby zostaną poprzedzone trzema zerami. Zauważ, że wyniki formuły są wyrównane do lewej krawędzi komórki, co sugeruje, że są to wartości tekstowe.

Opublikowano

Obliczenia procentowe w Excelu

Proste obliczenie procentów

Pierwszy najprostszy sposób obliczania procentów w Excelu przypomina obliczanie procentów na kalkulatorze.

Aby obliczyć ile procent stanowi 80 z 200 należy w pustej komórce Excela podzielić liczbę 80 przez 200.

  1. Do pustej komórki wpisz formułę =80/200 i zatwierdź ją wciskając na klawiaturze przycisk Enter.

  1. W komórce pojawi się wartość 0,4. Wynikowi należy nadać format procentowy.

  1. Kliknij na komórkę z obliczonym wynikiem.
  2. Na wstążce programu przejdź do Karty Narzędzia główne.
  3. Odszukaj i kliknij ikonkę formatowania procentowego.
  4. W komórce zostanie wyświetlony wynik 40%.

Obraz zawierający zrzut ekranu Opis wygenerowany automatycznie

Obliczanie struktury (udziałów procentowych)

W przypadku tabeli Excela zawierającej kilka pozycji należy zastosować formułę zawierającą odwołania do poszczególnych komórek tabeli. Poniższy przykład prezentuje jak obliczyć procentowy udział sprzedaży poszczególnych produktów.

  1. Do komórki wpisz formułę =B2/$B$6. Formuła ta dzieli wartość sprzedaży pierwszego produktu przez sumę sprzedaży wszystkich produktów. Zwróć uwagę, że adres komórki B6, odwołujący się do sumy sprzedaży wszystkich produktów, został zamrożony przy użyciu symbolu $.

Obraz zawierający ściana, szafka, zrzut ekranu Opis wygenerowany automatycznie

  1. Zatwierdź formułę wciskając na klawiaturze klawisz Enter, a następnie przeciągnij formułę do ostatniej komórki w tabeli.
  2. Otrzymane wyniki mają postać ułamków dziesiętnych.

Obraz zawierający ściana, wewnątrz Opis wygenerowany automatycznie

  1. Zaznacz otrzymane wyniki, a następnie zmień format komórek z ogólnego na procentowy. W tym celu kliknij prawym klawiszem myszki na zaznaczone komórki i z wyświetlonej listy wybierz opcję Formatuj komórki. Na ekranie zostanie wyświetlone okno Formatowanie komórek. W okienku tym przejdź do karty Liczby, wybierz kategorię Procentową, w razie potrzeby zmień liczbę miejsc dziesiętnych. Na koniec zatwierdź ustawienia naciskając przycisk Ok.

Obraz zawierający zrzut ekranu Opis wygenerowany automatycznie

  1. W tabeli wyniki zostaną wyświetlone w formacie procentowym.

Obraz zawierający ściana, zrzut ekranu Opis wygenerowany automatycznie

Użytkownicy Excela w wersji 365 mogą do obliczenia udziałów procentowych wykorzystać dynamiczne formuły tablicowe.

  1. Do komórki C2 wprowadź znak =
  2. Zaznacz komórki zawierające wartości sprzedaży, łącznie z sumą od B2 do B6.
  3. Wpisz znak dzielenia /
  4. Kliknij komórkę B6, zawierającą sumę sprzedaży. Gotowa formuła ma następującą postać: =B2:B6/B6. Zauważ, że w formule tablicowej nie musisz zamrażać adresu komórki B6 przy pomocy symbolu $.

Obraz zawierający zrzut ekranu, ściana, wewnątrz Opis wygenerowany automatycznie

  1. Zatwierdź formułę wciskając na klawiaturze przycisk Enter.
  2. Formuła zostanie automatycznie rozlana w tabeli, pamiętaj, aby zmienić format komórek na procentowy. Format komórek zmienisz w taki sam sposób jaki został opisany powyżej.

Obraz zawierający ściana, zrzut ekranu Opis wygenerowany automatycznie

Obliczanie zmiany procentowej

Oblicz o ile procent wzrosła cena w bieżącym miesiącu w porównaniu do ceny w miesiącu poprzednim. W celu obliczenia zmiany procentowej należy zastosować jeden z poniższych wzorów.

lub

Tabela zawiera ceny sprzedaży na koniec stycznia i na koniec lutego. Oblicz o ile procent zmieniły się ceny w ciągu miesiąca.

  1. Do pustej komórki wpisz formułę =C2/B2 -1 lub (C2-B2)/B2
  2. Wprowadzoną formułę zatwierdź wciskając na klawiaturze przycisk Enter.

Obraz zawierający ściana, wewnątrz, zrzut ekranu Opis wygenerowany automatycznie

  1. Przeciągnij formułę do pozostałych komórek w tabeli, pamiętaj o zmianie formatu komórek z formatu ogólnego na format procentowy

Obraz zawierający ściana, wewnątrz Opis wygenerowany automatycznie

Opublikowano

Blokowanie kolumn i wierszy w arkuszu Excela

Blokowanie górnego wiersza

  1. Kliknij na dowolną komórkę w arkuszu.
  2. Upewnij się, że jako pierwszy na ekranie widzisz wiersz, który ma zostać zablokowany.

C:\Users\Marcin\AppData\Local\Temp\SNAGHTML38ac0f6a.PNG

  1. Na wstążce programu przejdź do karty Widok. Kliknij ikonkę Zablokuj okienka, a następnie z wyświetlonej listy wybierz Zablokuj górny wiersz.

  1. Między zablokowanym wierszem, a kolejnym pojawi się cienka czarna linia.

C:\Users\Marcin\AppData\Local\Temp\SNAGHTML38adfdbd.PNG

  1. Przesuń zawartość arkusza w dół. Górny wiersz powinien być zawsze widoczny.

Blokowanie pierwszej kolumny

  1. Kliknij na dowolną komórkę w arkuszu.
  2. Upewnij się, że jako pierwszą na ekranie widzisz kolumnę, która ma zostać zablokowana.

C:\Users\Marcin\AppData\Local\Temp\SNAGHTML38ba5efd.PNG

  1. Na wstążce programu przejdź do karty Widok. Kliknij ikonkę Zablokuj okienka, a następnie z wyświetlonej listy wybierz Zablokuj pierwszą kolumnę.

  1. Między zablokowaną kolumną, a kolejną pojawi się cienka czarna linia.

C:\Users\Marcin\AppData\Local\Temp\SNAGHTML38be74e3.PNG

  1. Przesuń arkusz w prawo, pierwsza kolumna będzie zawsze widoczna.

Jednoczesne blokowanie wiersza i kolumny

Zasada blokowania

Podczas jednoczesnego blokowania wierszy i kolumn, zablokowaniu ulegają wiersze powyżej aktywnej komórki oraz kolumny z lewej strony aktywnej komórki.

C:\Users\Marcin\AppData\Local\Temp\SNAGHTML38c7ffc2.PNG

Przykłady

  • Aby zablokować jednocześnie pierwszy wiersz i pierwszą kolumnę należy ustawić się na komórce B2.
  • Aby zablokować jednocześnie dwie pierwsze kolumny (A i B) oraz wiersze od 1 do 3 należy ustawić się na komórce C4.
  • Aby zablokować jedynie cztery pierwsze kolumny (A, B, C, D) należy ustawić się na komórce E1.
  • Aby zablokować jedynie dziesięć pierwszych wierszy należy ustawić się na komórce A11.

Jednoczesne blokowanie pierwszego wiersza i pierwszej kolumny

  1. Ustaw się na komórce B2.

C:\Users\Marcin\AppData\Local\Temp\SNAGHTML38cd4f32.PNG

  1. Na wstążce programu przejdź do karty Widok. Kliknij ikonkę Zablokuj okienka, a następnie z wyświetlonej listy wybierz Zablokuj okienka.

  1. W arkuszu pojawią się cienkie czarne linie oddzielające zablokowaną kolumnę i wiersz od niezablokowanego obszaru.

  1. Przesuń treść arkusza w dół, a następnie w prawą stronę. Pierwszy wiersz i pierwsza kolumna będą zawsze widoczne na ekranie.

Odblokowanie

  1. Kliknij na dowolną komórkę, w arkuszu z zablokowanym widokiem.
  2. Na wstążce programu przejdź do karty Widok. Kliknij ikonkę Zablokuj okienka, a następnie z wyświetlonej listy wybierz Odblokuj okienka.

Uwaga ! Opcja Odblokuj okienka jest aktywna tylko w arkuszu z zablokowanym widokiem.

 

Opublikowano

Sumowanie na podstawie koloru komórki – proste rozwiązanie

Istnieje kilka rozwiązań w Excelu umożliwiających wykonywanie obliczeń na podstawie koloru wypełnienia komórki jedne są bardziej skomplikowane i wymagają umiejętności programowania inne natomiast prostsze, ale obarczone koniecznością wykonywania wielu kroków.

Sumowanie ze względu na kolor wypełnienia komórek

Poniżej opisana metoda pozwoli w prosty sposób na sumowanie po kolorze wypełnienia komórki. Zaletą tego rozwiązania jest to, że komórki z sumowanymi wartościami mogą być losowo rozmieszczone w arkuszu. W analogiczny sposób będzie można zliczyć komórki o tym samym kolorze oraz wykonywać inne obliczenia, których kryterium jest format komórki.

Krok 1 – wybierz komórki do podsumowania

Na wstążce programu przejdź do karty Narzędzia główne, kliknij ikonkę Znajdź i zaznacz, a następnie z listy wybierz opcję Znajdź. Na ekranie zostanie wyświetlone okienko Znajdowanie i zamienianie z aktywną kartą Znajdź. Okienko to można wywołać także za pomocą skrótu klawiaturowego CTRL+F. Kliknij znajdujący się w dolnym prawym roku okienka przycisk Opcje. Z pawiej strony przycisku Format rozwiń listę i kliknij opcję Wybierz format z komórki.
Okienko zostanie zminimalizowane, a przy kursorze pojawi się symbol pipety. Najedź kursorem i kliknij na jedną z komórek, która ma być sumowana. W efekcie w okienku Znajdowania i zamieniania w polu podglądu pojawi się wybrany format komórki.
Kliknij teraz przycisk Znajdź wszystko. Na dole okienka zostanie wyświetlona lista wszystkich komórek sformatowanych w ten sam sposób. Zaznaczając wszystkie pozycje na tej liście zaznaczysz jednocześnie komórki w arkuszu, najłatwiej zrobisz to wciskając na klawiaturze skrót CTRL+A.
Zamknij okienko Znajdowanie i zamienianie. od tej pory nie klikaj na komórki w arkuszu, aby nie stracić zaznaczenia.

Zauważ, że suma liczb z zaznaczonych komórek o tym samym kolorze wypełnienia pojawiła się już na dole ekranu na pasku stanu.

Krok 2 – zdefiniuj nazwę

Dla zaznaczonego zakresu komórek z tym samym kolorem wypełnienia należy zdefiniować nazwę, którą później będzie można używać w formułach. Na wstążce programu przejdź do karty Formuły i kliknij ikonkę Definiuj nazwę.

Na ekranie zostanie wyświetlone okienko Nowa nazwa. W polu Nazwa wpisz nazwę dla zaznaczonego zakresu komórek np. czerwone. W dolej części okienka w polu Odwołuje się do wymienione są wszystkie komórki wchodzące w skład definiowanej nazwy. Zamknij okienko klikając przycisk Ok.

Krok 3 – sumowanie komórek ze względu na kolor wypełnienia

Kliknij dowolną pustą komórkę i wprowadź do niej formułę Suma. Po otwarciu nawiasu zacznij wpisywać nazwę zdefiniowaną w poprzednim kroku. Zauważ, że Excel podpowiada zdefiniowaną nazwę. Wprowadzoną formułę zatwierdź wciskając na klawiaturze przycisk ENTER.

Operację z zaznaczaniem komórek i nadawaniem nazw należy powtórzyć dla każdego koloru wypełnienia komórek.

 

 

Opublikowano

Metry kwadratowe – formatowanie liczb

Excel nie dysponuje wbudowanym formatem liczb dla przedstawienia takich jednostek jak metry kwadratowe lub metry sześcienne. Jedynym rozwiązaniem jest zastosowanie niestandardowego formatu liczb i napisanie własnego kodu.

Do pustej komórki wprowadź symbol liczby 2 w indeksie górnym. W tym celu a wstążce programu przejdź do karty Wstawianie i kliknij ikonkę Symbol.

W okienku zawierającym różne symbole wybierz liczbę dwa w indeksie górnym,kliknij przycisk Wstaw, a następnie przycisk Zamknij.

W komórce pojawi się liczba dwa w indeksie górnym. Zaznacz liczbę na Pasku formuły, skopiuj ją wciskając na klawiaturze CTRL+C, a następnie wciśnij przycisk ESC, aby wyjść z Paska formuły.

Zaznacz komórki z liczbami, które chcesz sformatować i wywołaj okienko formatowania komórek. W okienku w karcie Liczby przejdź do kategorii Niestandardowe. W polu Typ wpisz następujący kod

# ##0,00" m²"

Pamiętaj, że dwójkę do podawanego wprowadzanego kodu należy wkleić wciskając na klawiaturze CTRL+V. Zatwierdź nowy format liczb klikając przycisk OK.

W efekcie wszystkie zaznaczone wcześniej komórki mają format metrów kwadratowych. W analogiczny sposób można sformatować liczby jako metry sześcienne, miligramy i inne wymagające specjalnego symbolu.

 

Opublikowano

Tabela przestawna – układ klasyczny

Tabele przestawne (Pivot Tables) to jedno z najpotężniejszych narzędzi Excela do analizy danych. Z pomocą tabel przestawnych z łatwością można podsumować dane wyeksportowane do Excela z dowolnego systemu finansowego, sprzedażowego, logistycznego, bazy danych lub innego. Tabele przestawne to narzędzie bardzo często wykorzystywane przez pracowników różnych działów w firmie, działów sprzedaży, marketingu, a w szczególności pracowników działów finansowych. Żaden analityk nie może wyobrazić sobie swojej pracy bez tabel przestawnych. W wielu ogłoszeniach o prace jedną z wymaganych od kandydata umiejętności jest tworzenie raportów i analiza danych z pomocą tabel i wykresów przestawnych.

Wstawianie tabel przestawnych nie jest rzeczą trudną, w Excelu od wersji 2013 pojawiła się nawet funkcja Polecanych Tabel Przestawnych. Na podstawie źródłowego zakresu danych Excel sam proponuje różne układy tabel przestawnych. Wystarczy wybrać interesujący nas układ, a program sam wstawi tabelę przestawną do arkusza.

Często wykorzystywany jest układ klasyczny tabeli przestawnej. Poniżej znajduje się instrukcja, jak wstawić tabelę w takim właśnie układzie. Należy pamiętać, że domyślnym układem tabel przestawnych jest układ kompaktowy.

Wstawianie tabeli przestawnej w układzie klasycznym

Na podstawie dostępnych danych źródłowych należy podsumować ilość turystów w podziale na kraje i lata.

  1. Kliknij dowolną komórkę w zakresie danych.
  2. Na wstążce programu przejdź do karty Wstawianie (Insert) i kliknij ikonkę Tabela Przestawna (Pivot Table).
  1. Na ekranie zostanie wyświetlone okno Tworzenie tabeli przestawnej.
  2. W polu Tabela/zakres (Table/Range) upewnij się, że program wykrył wszystkie dane źródłowe.
  3. Upewnij się, że zaznaczona jest opcja utworzenia tabeli przestawnej w Nowym arkuszu (New worksheet).

Prawidłowe ustawienia pokazano na poniższym rysunku.

  1. Zatwierdź ustawienia klikając przycisk OK.
  2. Do skoroszytu został wstawiony nowy arkusz, zawierający tabelę przestawną.
  3. Prawym klawiszem myszki kliknij na podgląd tabeli przestawnej, a następnie z listy wybierz Opcje tabeli przestawnej (PivotTable Options).
  1. W okienku Opcji Tabeli Przestawnej (PivotTable Options) przejdź do karty Wyświetlanie (Display).
  2. Odszukaj i kliknij na karcie Wyświetlanie (Display) opcję Układ klasyczny tabeli przestawnej (Classic PivotTable layout).
  1. Zatwierdź wprowadzone zmiany klikając przycisk OK.
  2. W arkuszu pojawi się siatka tabeli przestawnej w układzie klasycznym.
  1. Uwaga! Jeżeli klikniesz na komórkę poza tabelą przestawną, tabela zniknie.
  2. Z listy Pola tabeli przestawnej (PivotTable Fields) przeciągnij Kraj do obszaru Upuść pola wierszy tutaj (Drop Row Fields Here).
  1. Z listy Pola tabeli przestawnej (PivotTable Fields) przeciągnij Rok do obszaru Upuść pola kolumn tutaj (Drop Column Fields Here).
  1. Z listy Pola tabeli przestawnej (PivotTable Fields) przeciągnij Liczbę osób do obszaru Upuść pola wartości tutaj (Drop Value Fields Here).

Poprawnie przygotowaną tabelę przestawną w układzie klasycznym prezentuje poniższy rysunek.

Opublikowano

Zadania z Excela

W czasie szkoleń z Excela, wielu uczestników pyta mnie jak to wszystko zapamiętać i jak się tego wszystkiego nauczyć. Jedyną odpowiedzią na to pytanie jest trening.
Należy rozwiązywać zadania, które są jak najbardziej zbliżone do problemów z jakimi spotykają się w swojej codziennej pracy użytkownicy Excela.

Z myślą o wszystkich, którzy potrzebują treningu i poszukują zbioru zadań z Excela, a w szczególności o osobach przygotowujących się do testu z Excela podczas rozmowy o pracę został przygotowany e-book Excel 32 Zadania Rekrutacyjne z rozwiązaniami krok po kroku.

Prezentowany w tym wpisie e-book zawiera 32 typowe zadania z jakimi mogą spotkać się kandydaci do pracy na stanowiska, na których wymagana jest znajomość Excela.
Wszystkie zadania zawierają opisane krok po kroku rozwiązania. Książka jest bogato ilustrowana pritscreenami.
Opracowaniu towarzyszy plik Excela z danymi do samodzielnego rozwiązywania zadań oraz plik Excela z rozwiązanymi zadaniami.
Co więcej jest to jedyne takie opracowanie, w którym użytkownicy angielskiej wersji językowej programu znajdą obok polskich także angielskie nazwy formuł i wszystkich omawianych narzędzi.

Rozwiązując zawarte w e-booku zadania można przećwiczyć następujące zagadnienia:

  • Podstawowe obliczenia matematyczne
  • Adresowanie
  • Skróty klawiaturowe
  • Sortowane i sortowanie niestandardowe
  • Formatowanie warunkowe
  • Formatowanie komórek
  • Formuły logiczne (JEŻELI, ORAZ, LUB, JEŻELI.BŁĄD)
  • Zagnieżdżanie formuł
  • Formuły statystyczne (LICZ.JEŻELI, ŚREDNIA)
  • Formuły matematyczne (SUMA, SUMA.JEŻELI)
  • Formuły wyszukiwania (WYSZUKAJ.PIONOWO)
  • Formuły tekstowe (ZŁĄCZ.TEKST, LEWY, FRAGMENT.TEKSTU, PODSTAW, LITERY.MAŁE)
  • Rozdzielanie tekstu z jednej komórki na kolumny
  • Analizowanie danych przy pomocy Tabel Przestawnych
  • Wstawianie i formatowanie wykresów
  • Znajdowanie i poprawianie błędów
Opublikowano

Listy zależne w Excelu – prosty sposób

W Excelu pomocą narzędzia Sprawdzanie Poprawności można w prosty sposób wstawić do komórki listę rozwijaną, z której użytkownik może wybierać interesujące go pozycje. Co jednak jeżeli elementy na liście są uzależnione od wartości wpisanej w innej komórce. Na przykład przy uzupełnianiu formularza zamówienia z pierwszej listy wybieram kategorię produktową, a z drugiej listy wybieram konkrety produkt. W zależności od wybranej kategorii z pierwszej listy, na drugiej liście prezetowane są tylko produkty wchodzące w skład wybranej kategorii.

W internecie można zaleźć wiele sposobów na tworzenie list zależnych. Niektóre rozwiązania sugerują wykorzystanie w tym celu formuł index i przesunięcie, inne sugerują wykorzystanie makr.
W tym artykule została opisana chyba najprostsza metoda bazująca na nazwach zakresów i formule WARUNKI (użytkownicy starszych wersji Excela zamiast formuły WARUNKI mogą ten sam efekt osiągnąć zagnieżdżając formułę JEŻELI).

Krok pierwszy

W omawianym przykładzie wykorzystane zostaną dwie listy rozwijane. Lista główna zawiera nazwy kategorii produktowej oraz listy zależne zawierające nazwy poszczególnych produktów. Przykładowe listy prezentuje poniższy rysunek.

W arkuszu Formularz do komórki B3 należy wstawić listę pozwalającą na wybór kategorii produktowej. W tym celu na wstążce programu przejdź do karty Dane, a następnie kliknij ikonkę Poprawności danych.

Na ekranie pojawi się okienko Sprawdzania poprawności danych. W okienku tym z pośród wszystkich dozwolonych opcji wybierz Listę, a następnie w polu źródło zaznacz zakres komórek zawierających nazwy kategorii produktowych, tak jak pokazano to na poniższym rysunku.

Po kliknięciu przycisku Ok, w komórce pojawi się rozwijana lista, z której można wybrać nazwę kategorii produktowej.

Krok druginazwanie zakresów

Kolejnym krokiem jest nadanie nazw zakresom zawierającym listy produktów. W tym celu należy zaznaczyć listę wraz z jej tytułem, a następnie na wstążce w karcie Formuły kliknąć ikonkę Utwórz z zaznaczenia. Na ekranie pojawi się okienko Tworzenia nazwy z zaznaczenia. Jeżeli tytuł kolumny znajduje się w pierwszej komórce zaznaczonego zakresu, to upewnij się, że zaznaczona jest opcja Górny wiersz. Zatwierdź wybór klikając przycisk Ok.

Od tej chwili zaznaczonemu zakresowi komórek została przyporządkowana nazwa nabiał. Po zaznaczeniu komórek od C2 do C36 nazwa ta będzie widoczna w polu nazwy nad arkuszem.

Te same kroki powtarzamy w celu nadania nazw zakresom pozostałym listą z nazwami produktów.
Nazwy można później sprawdzić i edytować w okienku menadżera nazw.

Krok trzeci – formuła

Następnym etapem w tworzeniu listy zależnej jest napisanie formuły działającej na następującej zasadzie: jeżeli w komórce B3 wybrana została kategoria nabiał, wtedy wyświetl listę produktów wchodzących w skład tej kategorii, jeżeli natomiast została wybrana pieczywo, wtedy wyświetl listę produktów wchodzącą w skald tej kategorii i tak dalej aż w formule zostaną uwzględnione wszystkie kategorie produktowe i listy.
Użytkownicy Excela 365 i Excela 2019 mogą wykorzystać formułę WARUNKI (IFS), lub tak jak użytkownicy wcześniejszych wersji programu Excel mogą zagnieździć odpowiednią ilość razy formułę JEŻELI (IF).

Ustaw się w dowolnej pustej komórce w arkuszu i wprowadź do niej formułę. Zapis przykładowych formuł podano poniżej.

Formuła WARUNKI

=WARUNKI($B$3=”nabiał”;nabiał;$B$3=”pieczywo”;pieczywo;$B$3=”makaron”;makaron;$B$3=”produkty zbożowe”;produkty_zbożowe;$B$3=”przetwory”;przetwory;$B$3=”sosy i pasty”;sosy_i_pasty;$B$3=”zboża”;zboża;$B$3=”zioła i przyprawy”;zioła_i_przyprawy)

Formuła JEŻELI

=JEŻELI($B$3=”nabiał”;nabiał;JEŻELI($B$3=”pieczywo”;pieczywo;JEŻELI($B$3=”makaron”;makaron;JEŻELI($B$3=”produkty zbożowe”;produkty_zbożowe;JEŻELI($B$3=”przetwory”;przetwory;JEŻELI($B$3=”sosy i pasty”;sosy_i_pasty;JEŻELI($B$3=”zboża”;zboża;zioła_i_przyprawy)))))))

Krok czwarty – wstawienie listy zależnej

Skopiuj z paska formuły wpisaną wcześniej funkcję. Kliknij na komórkę C3 lub na inną komórkę, do której chcesz wstawić listę zależną.
Na wstążce programu przejdź do karty Dane i kliknij ikonkę Poprawność Danych.

W okienku sprawdzania poprawności danych z dozwolonych opcji wybierz listę, a następnie do pola Źródło wklej skopiowaną wcześniej formułę, pamiętaj, żeby formuła zaczynała się od znaku równości. Zatwierdź zmiany klikając przycisk OK.

Uwaga
Pole Źródło ma ograniczenie co do ilości wpisywanych znaków. Ograniczenie to wynosi 255 znaków.

Od tej pory po wybraniu w komórce B3 nazwy kategorii produktowej, w komórce C3 będzie wyświetlała się lista z nazwami produktów należącymi do wybranej kategorii.

Opublikowano

Liczba porządkowa – sortowanie i filtrowanie

Wpisanie na sztywno liczby porządkowej do tabeli sprawia problemy zarówno podczas sortowania jak i flitowania danych. Podczas sortowania tabeli liczby porządkowe układają się w niepoprawnej kolejności, natomiast podczas filtrowania, liczby porządkowe są co prawda w kolejności rosnącej, ale brak im ciągłości. Problem ten można rozwiązać stosując formuły ILE.WIERSZY lub SUMY.CZĘŚCIOWE.

Zastosowanie formuły ILE.WIERSZY

Formułę ILE.WIERSZY możemy zastosować jedynie w przypadku sortowania tabeli.

Przykład zastosowania

Do tabeli wstaw kolumnę Liczba porządkowa (w przykładzie jest to kolumna A), następnie do pierwszej pustej komórki w tej kolumnie wprowadź formułę:

=ILE.WIERSZY($A$2:A2)

Wpisana formuła oblicza ile jest wierszy w zaznaczonym zakresie komórek. W pierwszej komórce kolumny formuła zwróci wartość 1. Należy zwrócić uwagę na zablokowany pierwszy adres w zaznaczonym zakresie komórek. Zastosowanie tu odwołania bezwzględnego, sprawi, że formuła podczas kopiowania do kolejnych komórek w kolumnie będzie zwiększała zakres o kolejną komórkę, a tym samym wynik formuły będzie zwiękaszał się o 1 i w ten sposób utworzona zostanie seria liczb porządkowych.

Sortowanie danych w tabeli nie będzie miało wpływu na wartości w kolumnie Liczba porządkowa, gdyż za każdym razem formuła zostanie ponownie przeliczona i zwróci poprawną serię danych.

Zastosowanie formuły SUMY.CZĘŚCIOWE

Formuła SUMY.CZĘŚCIWOE może być zastosowana zarówno do tworzenia liczby porządkowej podczas sortowania jak i fitrowania, jednak dla poprawnego działania wymaga, aby w tabeli była przynajmniej jedna kolumna z wypełnionymi wszystkimi komórkami.

Przykład zastosowania

Podobnie jak w poprzednim przykładzie wstaw do tabeli kolumnę Liczba porządkowa i wprowadź do pierwszej pustej komórki formułę:

=SUMY.CZĘŚCIOWE(3;$B$2:B9)

Formułą SUMY.CZĘŚCIOWE z parametrem 3 obliczy ile jest nie pustych komórek w kolumnie B. Dzięki zablokowaniu symbolem dolara pierwszego adresu w zaznaczonym zakresie, podczas kopiowania formuły do kolejnych komórek, będzie ona zwiększała swój wynik o 1.

Poniższy rysunek pokazuje wynik działania formuły na oryginalnej tabeli i na tabeli z zastosowanymi filtrami oraz sortowaniem.

Opublikowano

Odwołania cykliczne

Mówiąc najprościej odwołanie cykliczne powstaje wtedy, kiedy formuła w Excelu do obliczeń używa samej siebie. Wyobraźmy sobie nastepującą sytuację. Do komórki A10 wstawiam formułę SUMA, jednka zamiast jako argument formuły podać zakres sumowanych komórek od A1 do A9, podaję jako sumowany zakres całą kolumnę A. W tym momencie powstaje w Excelu odwłoanie cykliczne, jednym z argumentów używanych przez formułę SUMA jest komórka A10, zawierająca właśnie tą formułę, czyli do obliczenia sumy, formuła wykorzystuje swój wynik.


Jeżeli w Excelu powstaje odwołanie cykliczne, program wyświetla ostrzeżenie.

Jak odnaleźć i usunąć odwołania cykliczne

Jeżeli w arkuszu istnieją odwołania cykliczne, należy je odnaleźć i usunąć, gdyż formuły zwracają niepoprawne wyniki. W celu odnalezienia wszystkich odwołań cyklicznych należy na wstążce programu Excel przejść do karty Formuły, nastepnie rozwinąć listę ukrytą pod ikonką Sprawdzanie błędów i wybrać z listy ostatnią pozycję Odwołania cykliczne.

Po wybraniu opcji Odwołania cykliczne zostanie wyświetlona dodatkowa lista z adresami komórek zawierających formuły z odwołaniem cyklicznym. Kliknięcie na adres na liście przenosi nas do komórki zawierającej formułę z odwołaniem cyklicznym. Teraz wystarczy poprawić formułę, aby pozbyć się odwołania cyklicznego.