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

{ 15 comments… read them below or add one }

empet

o jak mi tego brakowało 🙂 Dzięki!

Marcin

Można jeszcze prościej

http://office.microsoft.com/pl-pl/excel-help/dodawanie-dat-HP003056112.aspx

w jakiejś odległej kolumnie wystarczy wstawić ciąg od 1 do nieskończoności
do daty =teraz dodać wartość i tyle

Wymaga to co prawda grupowania czynności dla jednej daty, ale też się sprawdza.

sqtamorda

Jeżeli powielisz twoje formuły to przy zmianie wartości w polu „edytowana komórka” data zmienia się także we wszystkich wierszach.

Ankit Kaul

Hi Martin, I was just wondering if I use the Now() function and then save the sheet. The next time when I want to open the sheet will the Now() function change its value or it will remain as it is.

polaczek007

Fajna sprawa 🙂 dziękuję i pozdrawiam. Kris

luter

wszystko było by fajnie ale jest jakiś błąd gdyż po drugim wpisaniu cyferek zmienia się czas litery jest ok dwa wpisy nie zmieniają czasu

eMKa

Super!
Dzięki

chinczyk

A jak wkleić formułę do komórki. Zaznaczam, że ta =JEŻELI(B7″”,B7,TERAZ()) w normalny sposób też nie wchodzi. I nie ma takich opcji jak opisane powyżej w pozycji „… zaptaszkować…”
😉

chinczyk

Trzeba było zaznaczyż jaka to versja Excela, u mnie iteracja to Narzędzia/opcje/przeliczanie – inna versja.
Niemożność wklejania formuł to błędy interpunkcyjne w formułach. Nie wiem czy to moja wina i nie myślę tego sprawdzać, bo boję się że kilkadziesiąt arkuszy przestanie działać, a mogą wynikać z poniższego lub innej versji excela.
Nie chciało mi się przyciskać SHIFTU przy wpisywaniu danych i zmieniłem „Opcje regionalne i językowe (dotyczące czasu i daty)z panelu sterowania” znaki tzw. interpunkcyjne (;:.,). I tu zaczyna się prawdziwe kopanie z koniem. Excel zaczyna dziwnie działać przede wszystkim w formatowaniu daty i czasu i być może w formułach (dotyczy zmienionych znaków interpunkcyjnych w systemie operacyjnym) w ver 2000, nie wiem jak działają versje późniejsze, czy są odporne na takie zmiany. Inaczej działa ten sam arkusz excela przed wprowadzeniem zmian i ten sam arkusz excela po wprowadzeniu zmian.
To dobry psikus dla kogoś niemiłego 😉

chinczyk

Jeśli potrzebuję znaleźć używki w liście zakupów, to jak to zrobić najprościej (czy można to zrobić jeszcze prościej) niż …
=CZY.LICZBA(POTĘGA(CZY.LICZBA(SZUKAJ.TEKST(„pieros”;B16))+CZY.LICZBA(SZUKAJ.TEKST(„luck”;B16));0))
jeśli chcę znależć następny element listy to dodaję opcję (CZY.LICZBA(SZUKAJ.TEKST(„coś czego szukam”;B16))+ itd…
W odpowiedzi otrzymuję FAŁSZ – 0 -jak się nic nie znajduje,albo PRAWDA -1.
Podobno można kożystać z listy (Lucky stiki, spiki, i in. papierosy) oraz z formuł zapisywanych jako „funkcje” jak to zrobić ?

Michal

Niestety, ta formuła nie działa najlepiej, ponieważ w przypadku wprowadzania jakiejkolwiek innej danej w arkusz – data (czas wpisu) ulega zmianie. Ciekawy i trudny sposób ale dość mało użyteczny… Mnie interesuje sporządzenie dużego arkuszu w którym wprowadzałbym wartości dla różnych wierszy, i chciałbym mieć informacje kiedy zmieniłem którąś z wprowadzonych informacji. Jest jakiś sposób na to??

Darek

Dotyczy: Excel „timestamp”
Dzień dobry,

Mam pytanie:
Pobrałem plik i go sprawdzam. Jednak przy jakiejkolwiek zmianie innej komórki. Data w F9 ulega odświeżeniu i zmianie
Czy można temu zaradzić?
Pozdrawiam,
Darek

Błażej

Witam, mam pytanie jak zedytować tą formułę aby wszystko było w jednym wierszu?
(chodzi o to żeby „pierwszy wpis” i „drugi wpis” były w komórkach obok siebie a nie pod spodem…

pozdrawiam Błażej.

Marek Szczuka

Witam

Pański plik xls dotyczący funkcji Timestamp zawiera funkcje, które bardzo mi pomogą za co szczerze dziękuję. Zgłaszam się także z pytaniem, jakim modyfikacjom należałoby poddać plik, by widoczne były wszystkie wprowadzone zmiany albo miejsce, w którym będzie prezentowana suma zmian. Ze wszelkie informacje będę bardzo wdzięczny i jeszcze raz dziękuję za udostępnienie pliku online.

z wyrazami szacunku
Marek Szczuka

Jakub

Witam,
mimo zaznaczenia opcji obliczania iteracyjnego, gdy mamy kilka wierszy to przy zmianie dowolnego wiersza, timestamp zmienia się we wszystkich.
Jest sposób aby to obejść?

Leave a Comment

Previous post:

Next post: