Excel „timestamp” (czyli o datowaniu zmian w komórce)

by Marcin

ExcelTimestamps_Excelblog_pl

Od czasu do czasu zachodzi potrzeba zapisania w Excelu daty i godziny wprowadzenia nowej wartości (lub jej zamiany) do komórki. Przykładem może tu być lista, którą będzie wypełniał pracownik, a my chcielibyśmy dokładnie wiedzieć kiedy każda wartość została wpisana. Załóżmy sobie także na dzisiaj (tak dla podniesienia ciśnienia), że edytor VBA ma dzisiaj wolne, poszedł na imprezę i nie wróci do rana – zdani więc jesteśmy tylko na excelowe formuły.

Na pierwszy rzut oka sprawa wydaje sie prosta – jest przecież w Excelu funkcja TERAZ() (ewentualnie funkcja DZIŚ(), jeżeli nie zależy nam na większej precyzji), która wstawi nam do komórki  aktualną datę i czas.

Problem polega tu jednak na tym, że komórka zawierająca formułę =TERAZ() zmieni swoją wartość za każdym razem kiedy skoroszyt będzie przeliczany. W praktyce więc wyglądać to będzie tak, że owszem, data zostanie wstawiona do komórki, ale zostanie ona odświeżona za każdym razem kiedy wpiszesz inną formułę w dowolnym miejscu skoroszytu, przypadkowo (lub nie) wciśniesz F9 lub po prostu otworzysz swój plik Excela. Innym słowy nie rozwiązuje to w żadnym razie naszego problemu.

Można także wstawiać datę ręcznie każdorazowo po zmianie wartości w wybranej komórce. Dla ułatwienia możesz się nawet posłużyć skrótami klawiaturowymi CTR+; i/lub CTR+:. Zasadniczo działa, tyle tylko, że niespecjalnie pomaga. Excel ma ułatwiać pracę, a takie ręczne wstawianie dat ułatwieniem jest …. no nieszczególnym powiedzmy. Trzeba więc inaczej.

Pobróbowałem sobie kilka dni temu i osiągnąłem rezultat, którego oczekiwałem. Zależało mi na tym, aby każda zmiana wartości w wybranej komórce powodowała wstawienie daty i czasu tej zmiany. Oczywiście praca z innymi komórkami, czy też odświeżanie (F9) skoroszytu nie powinny w żaden sposób wpływać na wstawiony “timestamp”. Podobnie wpisanie tej samej wartośc. Dopiero wprowadzenie wartości innej niż wartość aktualna powinno wygenerować nowy znacznik czasu. Dodatkowo chciałem także przechowywać gdzieś ostatnią wprowadzona do komórki wartość.

Znalezione rozwiązanie nie jest może idealne, brak w nim nieco elegancji, ale …. sprawdza się.  Rezultat działania moich formuł całkowicię mnie zadowolił, więc nie męczyłem się dlużej. Jeżeli ktoś ma lepszy pomyć – jak zwykle czekam na kontakt.

Wydaje się, że sposobem na poradzenie sobie z opisanymi problemami z funkcją TERAZ() bedzie wcześniejsze sprawdzenie, czy w naszej komórce znajduje się już jakaś wartość (data/godzina) i wstawienie daty tylko wtedy jeżeli  komórka jest pusta. Jeżeli więc nasz “timestamp” ma się pojawiać w komórce “B7”, odpowiednia formuła mogłaby wyglądać mniej więcej tak:

=JEŻELI(B7<>"",B7,TERAZ())

Formuła sprawdza czy w komórce B7 znajduje się już jakaś wartośc czy nie. Jeżeli coś już w komórce B7 jest to formuła zwraca po prostu tę wartość, jeżeli komórka jest pusta to wstawiamy datę I czas funkcją TERAZ(). Tyle tylko, że ……… skoro wartość w B7 właśnie się zmieniła to cała procedura powtarza się od początku …… „formuła sprawdza czy w komórce B7 znajduje się już jakaś wartośc czy nie” ……. I od początku …… I od początku ……

I mogłoby to trwać całe wieki, gdyby nie to, że Excel jest średnio cierpliwy i zamiast przeliczać tę samą formułę przez resztę czasu,  zakomunikuje nam delikatnie, że może jednak to co zrobiliśmy nie jest do końca mądre, nasza formuła zawiera odwołania cykliczne i nie może być obliczona poprawnie.

Excel odwołania cykliczne - komunikat błędu

Trochę to studzi nasze zapędy, ale okazuje się, że sprawa nie jest jeszcze stracona. Otóż Excel może i stanął okoniem, ale nie oznacza to wcale, że nie chce dla nas dalej pracować. Tyle tylko, że zadanie na całą wieczność (bez odrobiny nadziei na odpoczynek) trzeba zastąpić zadaniem, które jednak da się ukończyć. Okazuje się prostsze niż mógłbyś pomyśleć. Wystarczy w opcjach Excela zaptaszkować “włącz obliczenia iteracyjne”. W naszym przypadku ustawimy maksymalna liczbę iteracji na 1 (teoretycznie można użyć większych numerów, ale z powodów, których jeszcze nie rozumiem przy większych liczbach zdarzało mi się, że moje formuły nie działały poprawnie. Zamiast więc kopać się z koniem  ustawiłem to po prostu na 1 – Excel całość przeliczy tylko raz.)

ExcelTimestamp_ObliczanieIteracyjne

Po ustawieniu tej opcji nasza formuła zaczyna działać i wszystko powoli nabiera sensu. Nasz użytkownik wprowadza dane do komórki B7 (w mom przykładowym skoroszycie), a my korzystać będziemy z komórki pomocniczej, w moim przypadku D7. Znajduje się w niej formuła:

=JEŻELI(B7<>"",JEŻELI(LEWY(D7,DŁ(B7))<>B7,JEŻELI(DŁ(D7)-DŁ(PODSTAW(D7,"|",""))>2,B7&"|"&LEWY(D7,ZNAJDŹ("|",D7)),B7&"|"&D7),D7),"")

Zwracała ona będzie dwa ostatnie wpisy (do komórki B7) oddzielone znakiem “|”. Tak naprawdę to zwraca ona także klika(naście) znaków jeszcze wcześniejszego wpisu (czasami nawet cały), ale po pierwsze jakoś niespecjalnie nam to przeszkadza, a po drugie … mówiłem przecież, że brakuje moim formułom elegancji Smile

 

ExcelTimestamp_CiagTekstowy

Bazując na ciągu tekstowym zwracamym przez formuły w komórce D7, w kolumnie obok “wyciągam” ostatni i przedostatni wpis. Odpowiednie formuły to:

=JEŻELI(CZY.BŁĄD(FRAGMENT.TEKSTU(D7,ZNAJDŹ("|",D7)+1,ZNAJDŹ("|",D7,ZNAJDŹ("|",D7)+1)-ZNAJDŹ("|",D7)-1)),FAŁSZ,FRAGMENT.TEKSTU(D7,ZNAJDŹ("|",D7)+1,ZNAJDŹ("|",D7,ZNAJDŹ("|",D7)+1)-ZNAJDŹ("|",D7)-1))

dla przedostatniego wpisu i

=JEŻELI(CZY.BŁĄD(LEWY(D7,ZNAJDŹ("|",D7)-1)),FAŁSZ,LEWY(D7,ZNAJDŹ("|",D7)-1))

dla ostatniego.

Na koniec, zadanie głowne (od którego się wszystko zaczęło), czyli wstawienie znacznika czasowego (timestamp) do kolejnej komórki.

Bardzo łądnie spisuje się tu formuła:

=JEŻELI(B7<>"",JEŻELI(LUB(F7="",ORAZ(E8<>FAŁSZ,E8<>B7)),TERAZ(),F7),"")

Zachęcam do spędzenia odrobiny czasu z pokazanymi formułami, próby zrozumienia jak działają, a także do własnych eksperymentów.

Plik z przykładem do ściągnięcia za darmo dla wszystkich zarejestrowanych użytkowników.

Excel timestamp - plik przykładowy

Marcin

{ 11 comments… read them below or add one }


Fatal error: Cannot assign by reference to overloaded object in /home/klient.dhosting.pl/antiquus/excelblog.pl/public_html/wp-content/themes/thesis_182/lib/classes/comments.php on line 176