Miesięczne archiwum: czerwiec 2022

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