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.

rys. nr 1 — Przykładowe dane

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).

rys. nr 2 — Okno Formatowanie komórek

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)

rys. nr 3 — Formatowanie niestandardowe — godziny i minuty

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)

rys. nr 4 — Formatowanie niestandardowe — minuty

Jeśli czas potrzebujemy wyrazić w sekundach, zapis będzie wyglądać analogicznie — [s] (rys. nr 5)

rys. nr 5 — Formatowanie niestandardowe — sekundy

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ć ‘’@’’

rys. nr 6 — funkcja TEKST

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.

rys. nr 7 — Wartość bazowa

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.

rys. nr 8 — Różnica w wyrównaniu tekstu i liczby

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).

rys. nr 9 — Funkcja TEKST — minuty

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)

rys. nr 10 — Zmiana tekstu na liczbę

Otrzymamy wynik liczbowy w formatowaniu niestandardowym. Musimy zmienić formatowanie w zakładce Narzędzia główne na Ogólne (rys. nr 11)

rys. nr 11 — Zmiana formatowania

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ę.

rys. nr 12 — Zmiana tekstu na 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.


Właśnie dodałem mój kurs o Power BI Desktop firmy Microsoft na Udemy.com.
W związku z tym, możesz dostać ten kurs w promocyjnej Cenie Na Start za zaledwie 34,99 PLN.
To najniższa cena jaką mogę ustawić na platformie edukacyjnej Udemy!

Kurs Power BI Desktop to:
- Ponad 6 godziny nagrań wideo, które krok po kroku wprowadzają Cię w tajniki pobierania, łączenia i analizy danych, a na koniec ich wizualizacji.
- Pliki do pracy razem z filmami.
- Dożywotni dostęp.
- Elektroniczny certyfikat ukończenia

Spis treści kursu o PowerBI Desktop:

Kurs jest podzielony na 6 rozdziałów, które pozwolą Ci wejść w tematykę analizy i wizualizacji danych za pomocą odpowiednio stworzonych zapytań i relacji w PowerBI Desktop.

  1. Wstęp do aplikacji PowerBI Desktop i jej możliwości
  2. Tworzenie i modyfikowanie zapytań (pobieranie danych)
  3. Modelowanie danych w PowerBI Desktop
  4. Wizualizacja danych i tworzenie raportów
  5. Usługa internetowa
  6. PowerBI Pro — kilka słów o płatnej części usługi PowerBI

Wejdź na stronę kursu PowerBI Desktop i zobacz szczegóły kursu
oraz udostępnione do podglądu filmy,
żeby przekonać się czy to kurs dla Ciebie.