Yestok.pl

Opracowanie powstało w oparciu o Excel 2007.

Jeden ze studentów zwrócił się kiedyś z prośbą o pomoc przy rozwiązaniu problemu rozliczenia czasu rozmów telefonicznych z konkretnym numerem. Ponieważ zagadnienie to może interesować więcej osób, zamieszczam jego rozwiązanie.

Otrzymywane przez nas rachunki telefoniczne zawierają wykaz naszych rozmów, i czas trwania każdej z nich, kiedy jednak chcemy przyjrzeć się ile czasu rozmawialiśmy z pewnym konkretnym numerem, musimy to zrobić ręcznie. W przypadku długiego rachunku może to być pracochłonne.

Aby rozwiązać problem musimy mieć nasz rachunek, a dokładniej, wykaz połączeń, w postaci elektronicznej. Na szczęście dostawcy usług telekomunikacyjnych pozwalają nam pobrać wersję rachunku z serwisu internetowego. W opisie tym przedstawię rozwiązanie dotyczące sieci ERA, ale może ono być zrealizowane dla każdego operatora, pod warunkiem, że udostępnia możliwość pobrania danych w postaci pliku tekstowego. Jeśli dane te dostępne byłyby w postaci pliku arkusza kalkulacyjnego, lub bazodanowego, problemów w ogóle by nie było. Od 5 czerwca 2011 roku sieć ERA funkcjonuje pod nową, międzynarodową marką T-Mobile. Opisane dalej procedury należy więc traktować jako opis działania i dopasować je do konkretnych rozwiązań.

Po zalogowaniu się do sieci ERA należy dotrzeć do sekcji "Płatności – Historia faktur". Dla wybranej faktury wybrać łącze "Jednorazowy rachunek szczegółowy" w postaci pliku txt (na ilustracji poniżej zaznaczone czerwonym kółkiem).

Przeglądarka zaproponuje otwarcie, bądź zapisanie pliku. Wybierzmy zapisanie i zapiszmy go pod własną lub zaproponowaną nazwą.

Tutaj praca przeglądarki internetowej się kończy. Otwieramy pobrany plik edytorem Word, i zaznaczamy ten fragment dokumentu (może być wielostronicowy), który zawiera zestawienie rozmów, tak jak to pokazano na ilustracji drugiej.

Zaznaczony tekst kopiujemy a następnie wklejamy do arkusza kalkulacyjnego Excel. Excel analizując strukturę pierwszego wiersza w zaznaczonym obszarze rozmieszcza dalsze dane w odpowiednich kolumnach przyjmując, że znak tabulacji oddziela dane poszczególnych kolumn. Uzyskany wynik przedstawia poniższa ilustracja.

Teksty mające postać xx:yy:zz są automatycznie konwertowane na dane przedstawiające czas. Dla mniej zorientowanych przypominam, że prezentowanie czasu w Excelu jest wyświetleniem w odpowiedniej postaci ułamka dziesiętnego od wartości 0,0 (oznaczającego początek doby) poprzez 0,5 (oznaczającego południe), do 0,99999 (oznaczającego godzinę 23:59:59). Ułamek taki jak każda liczba może podlegać operacjom arytmetycznym. Przeniesione do arkusza informacje są dłuższe niż szerokość poszczególnych kolumn. Zwróćmy uwagę, że np. nr telefonu wyświetla się tu jako 4,86E+10. Dzieje się tak gdy liczba, która ma zostać wyświetlona w komórce jest za długa aby się w niej zaprezentować. Po rozszerzeniu kolumn zobaczymy pełne teksty i właściwe numery. Elementem na który należy zwrócić uwagę to sposób przedstawiania liczb dziesiętnych. W dokumentach generowanych na podstawie baz danych, liczby dziesiętne mają wstawiony znak kropki jako separator części ułamkowej. Tak jak to widać na ilustracji powyżej w kolumnach netto i brutto. Zapis taki w Excelu nie jest liczbą lecz tekstem. By przekształcić te dane w liczby należy zaznaczyć te kolumny liczb i zamienić w nich znak kropki na znak przecinka. Korzystamy oczywiście z operacji Znajdź i zamień. W efekcie uzyskamy postać jak na ilustracji poniżej.

W arkuszu można w ten sposób zgromadzić dane z wielu miesięcy a nawet lat. Arkusz Excela 2007 może mieć 1 048 576 wierszy (65 536 w wersjach starszych). Realizując to zagadnienie nadałem arkuszowi zawierającemu dane zbiorcze, nazwę „Rachunek”. Na następnym arkuszu nazwanym „Czasy koszty” wstawiłem formuły, które dla podanego numeru telefonu wyświetlają: liczbę połączeń, czas ich trwania i koszt. Treść wstawionych formuł jest przytoczona pod odpowiednią komórką.

W formułach widać odwołania do zakresu danych w arkuszu „Rachunek”. Proszę zwrócić uwagę, że jeśli zakresy zostaną podane z dużym nadmiarem (nawet do ostatniego wiersza arkusza) będzie można dopisywać nowe zestawy danych i korzystać z obliczeń. Zakresy warto definiować przewidując maksymalną ilość wierszy, aby w przyszłości nie poprawiać formuł jeśli wprowadzono więcej danych. Jeśli ktoś zechce korzystać z arkusza aby wprowadzać tylko bieżące dane, powinien dla nich zrobić miejsce poprzez skasowanie danych poprzednich a następnie wklejenie danych nowych. Nie należy w tym celu używać operacji Wytnij, gdyż wówczas usunięte zostaną zakresy z formuł obliczeniowych.

Przykładowy skoroszyt w Excelu można pobrać przez ten link: obliczanie_czasu.xlsx . W arkuszu "Czasy koszty" zakresy zostały zdefiniowane do 65 536. wiersza. Sam arkusz poprawnie zafunkcjonuje w wersji 2007 i nowszych. W wersji starszej liczba „przegadanych” godzin może nie być poprawnie podana jeśli łączny czas rozmów z tym konkretnym numerem był dłuższy niż 24 godziny. Nie miałem okazji tego zweryfikować ale jeśli tak by było, to wynika to z niewystępującego w starszych wersjach formatu wyświetlania liczb. Format ten, ogólnie przedstawiany jest jako [g]:mm:ss i oznacza wyświetlanie pełnej liczby godzin a nie tylko godzin w obrębie doby. Jeśli więc wynik zsumowania ułamków reprezentujących poszczególne czasy rozmów wyniósłby np. 1,5, oznaczałoby to 1 dobę (24 godziny) i 12 godzin (ułamek 0,5). W formatach starszej generacji liczba ta pokazałaby się jednak tylko jako 12:00:00, w nowszej już jako 36:00:00.Pobrany arkusz może zostać otwarty i poprawnie zadziała także w programie Calc pakietu LibreOffice od wersji 3.4.