Archiwum kategorii: EXCEL

Excel – jak wygenerować losowo daty?

Stanąłem ostatnio przed problemem wygenerowania losowo dat z jakiegoś zakresu i umieścić je w kolumnie. Poniżej pomysł który przyszedł mi do głowy na rozwiązanie tego problemu. Pewnie rozwiązań jest więcej, ale ja prezentuje Wam taki, który od razu przyszedł mi do głowy. Sprawdziłem i działa.

No to zaczynamy !

Zadanie:

Wypełnij komórki od A1 do A10 (A1:A10) losowymi datami z przedziału od 2020-01-01 do 2020-12-31.

Na początek kilka informacji nt. dat i Excela. Nasz arkusz kalkulacyjny może prezentować daty w różny sposób (w różnym formacie), nawet w postaci liczb. Postać liczbowa daty jest dla nas oczywiście mało czytelna ale dla Excela nie ma to dużego znaczenia.

Przykład:

Wpisz w komórkę arkusza datę w postaci 2020-01-01

Tera potwierdź te dane wciskając klawisz Enter.

Zwróć uwagę że wprowadzałem datę z myślnikami „-” a po potwierdzeniu Excel zamienił separator na kropkę „.”

Dlaczego?

Bo taki format daty mam akurat ustawiony w systemie Windows.

I Excel dostosował format także u siebie na takie jaki jest w Windowsie.

No to teraz zobaczysz że Excel może interpretować datę jako liczbę.

Wróć do komórki A1, przejdź do zakładki „Narzędzia główne” i w części gdzie ustawiasz format pola zmień format z „Data” na „Liczba”. W wyniku powinieneś otrzymać liczbę 43831.

W komórce A2 wpisz datę końcową naszego zakresu, czyli 2020-12-31 i znowu zmień typ pola z „Data” na „Liczba”. Tym razem Excel zwrócił liczbę 44196.

Czyli 1 styczeń 2020 to dla Excela 43831, a 31 grudzień 2020 to 44196.

Wykasuj dane z kolumny A. Ustaw się ponownie w komórce A1 i napisz formułę.

=LOS.ZAKR(43831;44196)

Potwierdź wszystko Enterem i zmień typ pola ale tym razem z „Liczba” na „Data”.

W skopiuj formułę do komórek poniżej aż do A10. W wyniku powinieneś otrzymać tabelkę jak na obrazku poniżej.

Cała „sztuczka” polega na tym że funkcja LOS.ZAKR losowo generuje liczbę z zakresu od: 43831 do: 44196 a przez zmianę format pola (Liczba -> Data) prezentowana jest data a nie liczba.

Zadanie uważam za rozwiązane 🙂 miłej zabawy z Excelem.

<< wróć do kategorii: EXCEL

Excel tabele przestawne cz.1

W tym wpisie zajmiemy się tabelami przestawnymi. Wyjaśnię co to w ogóle jest? Do czego służy? I jak szybko zrobić prostą tabelę przestawną?

Zaczynamy!

Do naszego ćwiczenia przygotowałem specjalną tabelkę z listą zakupów z kilku dni. Celem naszego ćwiczenia będzie uzyskanie informacji ile pieniędzy wydaliśmy na zakupach w każdym ze sklepów.

Lista jest krótka, ale jest to działanie z premedytacją. Na mniejszych przykładach lepiej widać wyniki i szybciej jest sprawdzić czy wszystko dobrze zadziałało.

Żeby uruchomić proces tworzenia tabeli przestawnej musimy znajdować się w obrębie danych które chcemy przetwarzać. W naszym przypadku będzie to zakres pomiędzy komórką A1 a komórką G10. Kliknij na jedną z komórek z zakresu od A1 do G10. Ja zaznaczyłem komórkę A1 co jest widoczne na powyższym zrzucie.

Wybierz zakładkę „Wstawianie” i kliknij w ikonę „tabela przestawna”.

Excel, wstążka: Wstawianie, pozycja: Tabela przestawna

W kolejnym oknie możemy zdefiniować kilka rzeczy dot. naszej tabeli przestawnej.

W części „Zaznacz tabelę lub zakres” masz już automatycznie wybrany zakres od A1:G10 z arkusza „lista zakupów” więc wszystko się zgadza.

W części „Wybierz, gdzie chcesz umieścić raport w formie tabeli przestawnej” domyślnie zaznaczona jest opcja „Nowy arkusz”, czyli tabela przestawna utworzy się w nowym arkuszu i tak pozostaw.

Narazi wszystko potwierdź przyciskiem OK.

Excel utworzy nowy arkusz (u nas o nazwie „Arkusz2”) z widokiem jak poniżej.

Excel, tabela przestawna, widok ogólny.

I tutaj przechodzimy do sedna sprawy. Po prawej stronie w dolnej części widzisz cztery pola: Filtry, Kolumny, Wiersze, Wartości.

Powyżej tych pól znajduje się lista z nazwami kolumn z naszej tabeli źródłowej.

Zabawa będzie polegała na tym, że będziemy musieli „chwycić” nazwę kolumny z listy (czyli kliknąć na nią lewym przyciskiem myszy ale nie zwalniać klawisza myszki) i przeciągnąć ją do odpowiedniego pola na dole.

Powiedzmy, że chcemy uzyskać taki efekt końcowy jak na poniższym obrazku.

Nazwa sklepu po lewej stronie w kolumnie A a w kolumnie B wartość zrobionych zakupów w konkretnym sklepie.

Wróć do naszego nowego arkusza z tabelą przestawną i wykonaj dwie czynności:

  1. Z listy kolumn przeciągnij kolumnę „Nazwa sklepu” i upuść ją nad polem Wiersze.
  2. Z listy kolumn przeciągnij kolumnę „Wartość pozycji” i upuść ją nad polem Wartość

Po wykonaniu tych zadań powinniśmy otrzymać to o co chodziło nam w zadaniu czyli listę sklepów i sumę zakupów dla każdego z nich.

To jeszcze sprawdźmy czy wszytko zadziałało poprawnie. Sprawdźmy wynik dla pozycji „Sklep1”.

Wróć do arkusza „lista zakupów” i załóż filtry na tabelę źródłową (wstążka: Dane -> Filtruj).

Przefiltruj tabelę po kolumnie „Nazwa sklepu” i po wartości „Sklep1” i zlicz wartości dla Sklepu1. W wyniku powinieneś otrzymać wartość 66,55 czyli taką jaka została wygenerowana przez tabelę przestawną.

Także potwierdziliśmy poprawność wyliczeń w tabeli przestawnej z ręcznym zliczeniem wartości dla Sklepu1.

Na dzisiaj kończymy. Serdecznie zapraszam na kolejne wpisy.

<< wróć do kategorii: Excel

Excel, narzędzia główne, schowek, WKLEJ

Wklej to funkcja pozwalająca wkleić zawartość schowka do arkusza Excel. Ponieważ Excel to arkusz kalkulacyjny najczęściej wklejamy tekst lub liczby. Ale możemy wklejać także obrazki.

Jeśli do schowka skopiowaliśmy (Ctrl + C) już jakąś treść to możemy ją wkleić do arkusza na dwa sposoby.

  1. Ustawić się w wybranej komórce i wykorzystać skrót Ctrl + V który służy do wklejania.
  2. Ustawić się w wybranej komórce i kliknij PPM (prawym przyciskiem myszy) na ikonę Wklej.

Jeśli wykorzystamy te domyślne sposoby to skleimy tekst z formatowaniem źródłowym.

Przykład: Chcesz skopiować zdanie „Ala ma kota” z aplikacji Word do Excel. W programie Word to zdanie jest jednak sformatowane. Jest pogrubione, pochylone, czcionka ma kolor czerwony i ma wielkość 20.

W celu skopiowania wciśnij Ctrl + C.

Teraz przejdź do programu Excel, ustaw się w komórce A1 i użyj skrótu Ctrl + V.

Jak Widać tekst wkleił się do Excela ze źródłowym formatowaniem, czyli formatowaniem jaki ten tekst miał w źródłowym dokumencie, czyli w aplikacji Word.

A jak wkleić ten tekst usuwając formatowanie?

Jak już mamy nasz tekst skopiowany do schowka. Przejdź do Excela i kliknij w wybraną komórkę (np. A1) ale LPM (lewy przycisk myszy) i z meny kontekstowego wybierz ikonkę takiej zapisanej kartki z opisem: uwzględnij formatowanie docelowe, czyli takie jakie posiada nasza wskazana w Excelu komórka arkusza.

Uwzględnij formatowanie docelowe

Po wklejeniu nasz tekst będzie wyglądał jak na poniższym obrazku.

<< wróć do kategorii: Excel

Excel przydatne skróty.

W niniejszym wpisie będziemy umieszczać (i uzupełniać) przydatne skróty klawiszowe w programie MS Excel.

Zaznaczanie wartości w kolumnach

Ctrl + Spacja jeśli aktywna komórka znajduje się w środku kolumny
Ctrl + Shift + strzałka w dół jeśli jesteśmy w pierwszej komórce kolumny)

Zaznaczanie obszarów

Ctrl + strzałka (dowolny kierunek) powiększa zaznaczony obszar o 1 komórke we wskazanym kierunku (strzałka)
Ctrl + Shift + strzałki powiększa zaznaczenie do końca wiersza lub kolumny
Ctrl + Shift + End zaznacza wszystko do ostatniej komórki z danymi
Ctrl + Shift + Home zaznacza wszystko do komórki A1
Ctrl + A zaznacza cały arkusz
Ctrl + Shift + 8 zaznacza całą tabelę jeśli jesteś w środku jakiejś tabeli
Shift + spacja zaznacza cały wiersz w którym aktualnie jesteś bez względu w ilu kolumnach są dane
Ctrl + spacja zaznacza całą kolumnę w której aktualnie jesteś bez względu w ilu polach są dane

EXCEL funkcja TERAZ ()

Opis

Funkcja TERAZ() jest rozszerzeniem funkcji DZIŚ() i zwraca aktualną datę i godzinę systemową w formacie w jakim ta data i godzina występują.

Składnia

=TERAZ()

Przykład

Formuła Wynik Opis
=TERAZ() 14.02.2020 08:01 Funkcja zwróci taki wynik przy założeniach że jest 14 lutego 2020, godzina 8:01 i format daty systemu ustawiony jest na "dd.MM.rrrr" a godziny na "GG:mm".

Więcej wpisów na temat funkcji w programie Microsoft EXCEL znajdziesz <<<tutaj>>>.


EXCEL funkcja MIN ()

Opis

Funkcja MIN() służy od znalezienia, jak sama nazwa wskazuje, najmniejszej wartości z podanych argumentów funkcji.

Składnia

=MIN( liczba1 ; [liczba1] ; … )

Przykłady

Poniżej przygotowany zestaw danych.

kurs excel funkcja min

Formuła Wynik Opis
=MIN(1;2;3) 1 Szukaj wartości minimalnej wśród liczb podanych w argumentach funkcji.
=MIN(A3;B2;C1) 3 Szukaj wartości minimalnej w komórkach o adersach podanych w argumentach funkcji.
=MIN(A1:A3) 1 Szukaj wartości minimalnej w kolumnie A od wiersza 1 do wiersza 3
=MIN(A1:C3) 1 Szukaj wartości minimalnej w kolumnach do A do C w wierszach od 1 do 3
=MIN(A1:C3;30;C5) 1 Szukaj wartości minimalnej w kolumnach do A do C w wierszach od 1 do 3, weź dodatkowo pod uwagę wartość 30 i wartość w komórce A3
=MIN(C4) 0 Szukaj wartości minimalnej w kolumnach do A do C w wierszach od 1 do 3, weź dodatkowo pod uwagę wartość 30 i wartość w komórce A3

Więcej wpisów na temat funkcji w programie Microsoft EXCEL znajdziesz <<<tutaj>>>.


EXCEL funkcja MAX ()

Opis

Funkcja MAX() służy od znalezienia, jak sama nazwa wskazuje, największej wartości z podanych argumentów funkcji.

Składnia

=MAX( liczba1 ; [liczba1] ; … )

Przykłady

Poniżej przygotowany zestaw danych.

kurs excel funkcja max

Formuła Wynik Opis
=MAX(1;2;3) 3 Szukaj wartości maksymalnej wśród liczb podanych w argumentach funkcji.
=MAX(A3;B2;C1) 7 Szukaj wartości maksymalnej w komórkach o adersach podanych w argumentach funkcji.
=MAX(A1:A3) 7 Szukaj wartości maksymalnej w kolumnie A od wiersza 1 do wiersza 3.
=MAX(A1:C3) 9 Szukaj wartości maksymalnej w kolumnach do A do C w wierszach od 1 do 3.
=MAX(A1:C3;30;A3) 30 Szukaj wartości maksymalnej w kolumnach do A do C w wierszach od 1 do 3, weź dodatkowo pod uwagę wartość 30 i wartość w komórce A3.

Więcej wpisów na temat funkcji w programie Microsoft EXCEL znajdziesz <<<tutaj>>>.


EXCEL funkcja ILE LICZB()

Opis

W sytuacji kiedy musimy zliczyć ile liczb występuje w danym zakresie danych, przydatną funkcją jest funkcja o nazwie ILE.LICZB(), której celem jest właśnie zliczenie ilości liczb występujących we wskazanym przez użytkownika zakresie.

Składnia

=ILE.LICZB( wartość1 ; [wartość2] ; … )

Przykłady

Poniższe przykłady są oparte o dane z obrazka poniżej.

kurs excel funkcja ile liczb

Formuła Wynik Opis
=ILE.LICZB(B2:B9) 8 W kolumnie B pomiędzy wierszami 2 i 9 jest 8 liczb.
=ILE.LICZB(C2:C9) 5 W kolumnie C pomiędzy wierszami 2 i 9 jest 5 liczb.
=ILE.LICZB(D2:D9) 2 W kolumnie D pomiędzy wierszami 2 i 9 są 2 liczby.
=ILE.LICZB(B2:D9) 15 W kolumnach od B do D, pomiędzy wierszami 2 i 9 jest 15 liczb.

Więcej wpisów na temat funkcji w programie Microsoft EXCEL znajdziesz <<<tutaj>>>.


EXCEL funkcja DZIŚ ()

Opis

Funkcja DZIŚ(), zwraca aktualną datę systemową w formacie w jakim ta data występują. Jeżeli na naszym systemie operacyjnym format daty ustawiony jest np. na taki 2020-01-01 to funkcja DZIŚ() zwróci nam datę w takim samym formacie.

Składnia

=DZIŚ()

Przykład

Formuła Wynik Opis
=DZIŚ() 14.02.2020 Funkcja zwróci taki wynik przy założeniach że jest 14 lutego 2020 i format daty systemu ustawiony jest na "dd.MM.rrrr".

Więcej wpisów na temat funkcji w programie Microsoft EXCEL znajdziesz <<<tutaj>>>.


EXCEL funkcja PODAJ POZYCJĘ ()

Opis

Funkcja PODAJ.POZYCJĘ() zwraca pozycję szukanego elementu we wskazanej tablicy danych.

Składnia

=PODAJ.POZYCJĘ( szukana_wartość ; przeszukiwana_tablica ; [typ_porównania] )

Żeby lepiej zobrazować Wam działanie funkcji PODAJ.POZYCJĘ() zerknijcie na poniższy obrazek. Jest tam lista miast (tabela 1) a obok niej jest przygotowana mini lista szukanych miast (tabela 2).

Excel funkcja podaj pozycje
Excel funkcja podaj pozycje

teraz ustawimy się w komórce E2 (kolumna "Pozycja w tabeli 1" obok wartości "Gdańsk") i utworzymy formułę z funkcją PODAJ.POZYCJĘ() żebyśmy otrzymali pozycję wartości "Gdańsk" w tabeli 1.

zgodnie ze składnią nasza formuła będzie wyglądała tak:

=PODAJ.POZYCJĘ(D2;B2:B9;0)

W pierwszym argumencie funkcji wstawiliśmy wartość D2, czyli nazwę "Gdańsk", bo tej wartości będziemy szukać w tabeli 1.

Drugi argument to zakres danych od komórki B2 do komórki B9, bo w tych komórkach mieszczą się wszystkie wartości z tabeli 1. Tam właśnie będziemy szukać pozycji naszego szukanego elementu. W naszym przypadku będzie to nazwa "Gdańsk".

W trzeci argumencie określamy typ porównania. U nas jest to wartość "0" która oznacza "Dokładne dopasowanie". Ale argument ten może także przyjąć wartość "1" i oznacza to "Mniejsze niż" lub wartość "-1" i będzie to oznaczało "Większe niż".

Jak już wprowadzimy całą formułę (pamiętaj o nawiasię zamykającym) zatwierdzamy wszystko przyciskiem Enter i w komórce E2 powinniśmy otrzymać wartość "2". Dlaczego wartość "2"? Bo nazwa "Gdańsk" znajduje się na drugiej pozycji w tabeli 1.

Skopiuj formułę z komórki E2 do komórki E3. Żeby to zrobić ustaw się w komórce E2, kliknij i przytrzymaj czarny kwadracik który pojawi się w prawym dolnym rogu tej komórki i przeciągnij go w dół na komórkę E3. Wtedy powinniśmy w komórce E3 otrzymać wartość "6". Dlaczego ? Bo nazwa "Rzeszów" znajduje się na "6" pozycji w tabeli 1.

Więcej wpisów na temat funkcji w programie Microsoft EXCEL znajdziesz <<<tutaj>>>.