W tym poście zajmiemy się tematem czasu wyrażonego w godzinach, minutach i sekundach za pomocą funkcji TEKST. Funkcję tą omówimy na podstawie przykładowych danych z rysunku nr 1.
Przypomnijmy sobie że temat czasu był już omawiany w poradzie 328. Zamienialiśmy wtedy czas na godziny, minuty i sekundy za pomocą Formatowania niestandardowego oraz za pomocą formuły, w której mnożyliśmy nasz czas przez 24 dla wyrażenia ilości godzin, przez 24 i 60 dla wyrażenia ilości minut i analogicznie przez 24, 60 i jeszcze raz 60 dla sekund. Dodatkowym działaniem było zaokrąglanie wyniku do całych liczb.
Zostałem poproszony o pokazanie innego sposobu na wyrażenie czasu w godzinach, minutach i sekundach, który byłby mniej czasochłonny. Aby w pełni zrozumieć funkcję Tekst, musimy sobie przypomnieć sposoby zamiany czasu na godziny, minuty i sekundy za pomocą Formatowania Niestandardowego.
Przy użyciu skrótu klawiszowego Ctrl + 1, otwieramy okno Formatowania komórek (rys. nr 2).
W zakładce Liczby, w okienku Kategoria wybieramy Niestandardowe, a następnie w okienku Typ wpisujemy, co nas interesuje. Kiedy chcemy wyrazić czas w godzinach wpisujemy [g]:mm (rys. nr 3)
W sytuacji, kiedy chcemy wyrazić czas w minutach wpisujemy [mm]. Zapis mm oznacza, że w sytuacji kiedy mamy ilość minut poniżej 10, pojawi się nam 0 wiodące (rys. nr 4)
Jeśli czas potrzebujemy wyrazić w sekundach, zapis będzie wyglądać analogicznie — [s] (rys. nr 5)
Jest to nadany styl formatowania. Pamiętajmy, że pod formatowaniem ukryte są liczby. W Excelu jest funkcja TEKST, która pozwala nam pobrać wartość z komórki i zapisać ją w formie tekstu (rys. nr 6). W funkcji tej najpierw wybieramy wartość bazową, czyli komórkę do której się odwołujemy, następnie wpisujemy format tekstu. Na przykład, jeśli chcemy, aby Excel wstawił nam wartość bazową jako tekst musimy wpisać ‘’@’’
W wyniku takiego zapisu otrzymamy wartość bazową z komórki A2, a nie tą która się wyświetla (rys. nr 7), a co najważniejsze zapisaną jako tekst. Cały drugi wiersz ma taką samą wartość bazową, różni się formatowaniem, czyli sposobem jej wyświetlania.
Jeśli chcemy, aby Excel wyświetlił nam godziny i minuty to nasza formuła będzie wyglądać następująco TEKST(A2;"[g]:mm"), czyli podajemy kod formatowania liczbowego niestandardowego. Funkcja Tekst zwraca nam ten sam wynik, co formatowanie, ale w postaci tekstu, a nie liczby (rys nr 8). Liczba w Excelu jest wyrównana do prawej strony komórki, natomiast tekst jest wyrównany do lewej.
Jeśli chcemy pokazać czas w minutach za pomocą funkcji TEKST, zrobimy to analogicznie, tylko w formule podamy kod formatowania dla minut, czyli [mm]. Nasza formuła będzie wyglądać następująco TEKST(A2;"[mm]"). Otrzymujemy wynik i przeciągamy go w dół na pozostałe wiersze (rys. nr 9).
Wynik jest w postaci tekstu. Możemy sobie zmienić tą wartość tekstową na liczbową, żebyśmy mogli używać tych danych do obliczeń. Wystarczy, że dokonamy na tym tekście dowolną operację matematyczną i Excel automatycznie zwróci nam wynik w postaci liczby. Najłatwiejszym sposobem jest dodanie wartości 0 (rys. nr 10)
Otrzymamy wynik liczbowy w formatowaniu niestandardowym. Musimy zmienić formatowanie w zakładce Narzędzia główne na Ogólne (rys. nr 11)
Innym, a zarazem najszybszym, rozwiązaniem na zmianę tekstu na liczbę (wg Mike’a Girvina) jest użycie podwójnego minusa. Działanie polega to na zmianie znaku naszej wartości dwukrotnie, czyli w efekcie otrzymujemy tę samą wartość (rys. nr 12)
=–TEKST(A2; "[mm]")
Takie działanie matematyczne sprawia, że Excel wartość zapisaną jako tekst, przekształca w liczbę.
Analogicznie postąpimy, gdy będziemy chcieli otrzymać ilość sekund. Wystarczy, że w formule zmienimy [mm] na [s], czyli nasza funkcja będzie wyglądać następująco TEKST(A2;"[s]"). Otrzymamy wynik w postaci tekstowej, następnie aby zmienić go w wartość liczbową, musimy wykonać na nim jakąś operację matematyczną (dodać do niego wartość 0 lub wstawić z przodu dwa minusy).
Podsumowując funkcja TEKST jest bardzo przydatna, kiedy chcemy wyciągnąć jakąś wartość bazową i ją potem odpowiednio sformatować, aby dobrze się prezentowała np. w nagłówkach, etykietach czy nazwach danych.
Mogą pojawić się niewielkie rozbieżności w wynikach funkcji ZAOKR i TEKST. Wynika to z tego, że funkcja ZAOKR zaokrągli nasze dane w dół lub w górę w zależności, jaka jest pierwsza cyfra po przecinku, natomiast funkcja TEKST utnie wartości po przecinku, jakby ich w ogóle nie było.
Książka Mistrz Excela + promo na 35 urodziny
Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą)
II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.
Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".
Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query.
A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.
Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.
Aktualnie w promocji urodzinowej możesz mieć Mistrza Excela w obniżonej cenie, jeśli tylko wpiszesz kod 35URODZINY
https://exceliadam.pl/produkt/ksiazka-mistrz-excela
Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie.
Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.
W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.
Power Query
https://www.udemy.com/course/mistrz-power-query/?couponCode=35URODZINY
Mistrz Excela
https://www.udemy.com/mistrz-excela/?couponCode=35URODZINY
Dashboardy
https://www.udemy.com/course/excel-dashboardy/?couponCode=35URODZINY
Mistrz Formuł
https://www.udemy.com/course/excel-mistrz-formul/?couponCode=35URODZINY
VBA
https://www.udemy.com/course/excel-vba-makra/?couponCode=35URODZINY
Microsoft Power BI
https://www.udemy.com/course/power-bi-microsoft/?couponCode=35URODZINY
