[SOLVED] Podświetlenie najbliższej daty

[MarymeK]

Witam, czy da radę uzyskać taki efekt żeby z różnych zbliżających się dat automatycznie podświetlała się ta która jest najbliżej??

[Rafkus]

Jest to możliwe. Do danych dołączyłbym jeszcze jedną kolumnę z danymi, w której przy pomocy funkcji:

Kod: Zaznacz cały

=DNI(D2;   DZIŚ())

obliczałbym różnicę pomiędzy datami zawartymi np: w kolumnie D a dniem dzisiejszym.
Na podstawie tej wartości formatowałbym warunkowo styl komórki.

[MarymeK]

Hmm, mógłbyś to opisać jakoś dokładniej. Nie jestem aż tak biegły w tym temacie. Dodam, że daty w tym dokumencie mogą znaleść się w różnych wierszach i kolumnach. Oczywiście formułę mogę wpisywać dla każdej komórki oddzielnie.

[Rafkus]

Więcej na temat możesz znaleźć w opracowaniu @Jermora:
https://yestok.pl/ooo/y56.php

[Rafkus]

O i tutaj masz nieco więcej opisane:
https://forum.openoffice.org/pl/forum/v ... owe#p21356

[MarymeK]

ehs za cienki na to jestem. Pokoloruje zaznaczone pole ale tylko z użyciem tych opcji które są jakby wbudowane. Niestety nie do końca spełnia to moje oczekiwania. Może podpowiecie coś prostszego np. jak by wyglądała formuła kolorowania takiego pola gdzie data od dziś ma np.+3 dni??

[Jermor]

Można byłoby to zrobić, jeśli daty znajdują się zawsze w znanych miejscach np. w kolumnie A i C, albo w określonych obszarach.
Napisałeś

daty w tym dokumencie mogą znaleść się w różnych wierszach i kolumnach

Ale co to oznacza, czy to, że w takich konkretnych miejscach raz są ciągi tekstowe, raz liczby a czasami daty?
W zwrotnym pliku przedstawiam ci sposób jak podświetlić najbliższą nadchodząca datę w odniesieniu do daty bieżącej.
Założyłem, że daty będą się znajdować w pewnym określonym obszarze i obszar ten może zawierać tylko daty (trzeba bowiem pamiętać, że daty to w rzeczywistości także liczby).
Wymaga to najpierw obliczenia pewnej wartości pomocniczej, która w arkuszu znajduje się w komórce I1. Zawiera ona następującą formułę macierzową:

Kod: Zaznacz cały

=MIN(DNI(A1:E17;DZIŚ())*JEŻELI(DZIŚ()<=A1:E17;1;-100000))

Formułę należy zatwierdzić skrótem klawiaturowym SHIFT+CTRL+ENTER. Oblicza ona najmniejszą różnicę między datami późniejszymi znajdującymi się w tym obszarze a datą bieżącą. Daty poprzedzające dzień bieżący mają tę różnicę mnożoną przez -100000 dlatego aby prezentująca ich różnica, tworzyła absurdalnie wielką liczbę dodatnią, taką która nie może zostać uznana, za różnicę najbliższą dacie bieżącej. Gdyby tak nie było funkcja MIN zwracałaby wartości ujemne jako najmniejsze.
Teraz można utworzyć formułę formatowania warunkowego dla tego obszaru. Zastosowana przeze mnie podświetli datę lub daty, które odpowiadają tej najmniejszej różnicy.

[MarymeK]

Jermor wzoruje się na tym Twoim pliku jednak mimo wszystko mi to nie wychodzi. Załączam swój plik tam gdzie są daty zależało by mi właśnie na takim formatowaniu. Mogę dodać wiele formuł bo komórki z datami raczej się nie zmienią ewentualnie z czasem przybędą inne miejsca.

Dodaje to obliczenie pomocnicze z podanym zakresem komórek tak jak u Ciebie i oblicza ona tą różnice jednak mimo wpisania warunku formatowanie to ono nie następuje. Nie wiem co robię nie tak, bo wszystko wygląda jak u Ciebie

Czy mógłbyś zerknąć i nakierować mnie co robię nie tak.

[Jermor]

Formatowanie warunkowe nie działa bo odwołujesz się w nim, tak jak w moim przykładzie, do zawartości komórki I1, a przecież swoje obliczenia wpisałeś do M25.
Dla ułatwienia pisania formuły, zakresowi A1:R33 w twoim arkuszu nadałem nazwę "zakres". Wówczas wartość pomocniczą da się wyliczyć jedną formułą.

Kod: Zaznacz cały

=MIN(JEŻELI(CZY.LICZBA(Zakres);DNI(Zakres;DZIŚ())*JEŻELI(DZIŚ()<=Zakres;1;-100000);100000))

Ta formuła przypisuje wynik 100000 tym komórkom, które nie są liczbami, po to aby funkcja MIN() nie uznała ich za najmniejsze. CZY.LICZBA() jest potrzebne aby funkcja DNI() nie wyłożyła się, gdy komórka zawiera tekst a nie liczbę. Druga część to tak jak już napisałem, dla dat poprzedzających DZIŚ() tworzy liczbę co najmniej 100000.
Ten wynik znajduje się w komórce T1 i dla poprawności obliczeń powinien znajdować się poza tym nazwanym obszarem.
Formatowanie warunkowe przypisałem całego ego obszaru a nie do poszczególnych komórek z datami, bo to jest prostsze.
Teraz jeśli chodzi o ewentualne liczby. Funkcja DZIŚ() zwraca w rzeczywistości liczbę całkowitą, mówiącą ile dni upłynęło od 31.12.1899 roku. Dla daty 23.01.2021 jest to liczba 44219. Jeżeli do twojego arkusza byłyby wpisywane liczby tego rzędu, to mogłyby wpływać na podświetlane komórki, gdyż algorytm uznając je za daty obliczałby różnicę między nimi a DZIŚ(). Liczby mniejsze od wynikającej z dzisiejszej daty mogą być wpisywane bez przeszkód, gdyż prezentując datę poprzedzającą dzisiejszą nie są uwzględniane w analizie dat. Problemem będą wartości większe. Dla przytoczonej styczniowej daty, gdyby jakaś komórka w tym obszarze musiała zawierać wynik 44300, to odpowiadałaby dacie 14.04.2021 a to odpowiadałoby różnicy 81 dni. Tak więc od twoich danych zależy zastosowanie tego rozwiązania.

[Jermor]

Po przyjrzeniu się formule wyliczającej wartość pomocniczą stwierdziłem, że można ja zapisać prościej, cały czas pamiętając, że jest to nadal formuła macierzowa. Oto ona:

Kod: Zaznacz cały

=MIN(JEŻELI.BŁĄD(WARUNKI(DZIŚ()<=Zakres;DNI(Zakres;DZIŚ());1=1;100000);100000))

Funkcja WARUNKI() bada, czy data w "zakresie" jest większa od dzisiejszej. Jeśli tak, to oblicza różnicę, jeśli nie to wpisuje 100000. Jeśli w komórce zakres znajduje się tekst, to funkcja DNI() generuje błąd wykonania a wtedy funkcja JEŻELI.BŁĄD() podstawia 100000.
O tych funkcjach można poczytać tutaj: https://yestok.pl/lbo/y63.php

[MarymeK]

Jermor jesteś wielki, widzę że dla Ciebie chyba nie ma rzeczy nie możliwych w tym zakresie (zazdroszczę wiedzy).

Działa to super tak jak bym chciał. Takie jeszcze jedno pytanko, co prawda to już kosmetyka ale gdyby się dało to też fajnie. Czy dało by radę efekt formatowania komórki z datą najbliższą przenieść na inne wyznaczone lub przynajmniej na cztery ją poprzedzające.??

[Jermor]

Czy dało by radę efekt formatowania komórki z datą najbliższą przenieść na inne wyznaczone lub przynajmniej na cztery ją poprzedzające.?

Nie zrozumiałem pytania. Przenieść na inne, czy zastosować także do innych? Daty poprzedzające tę najbliższą to daty już przeszłe. Czyli co chciałbyś zobaczyć? Najbliższą i np. cztery, które już minęły? Co oznacza "inne wyznaczone"?

[MarymeK]

Już tłumaczę. Chodzi o zastosowanie do innych. Czyli komórka z datą dalej ma to formatowanie, a po za nią 4 komórki ją poprzedzające gdzie znajdują się inne dane np. imię, nazwisko, status i wiek. Tak żeby podświetlona była nie tylko data ale wszystkie komórki zawierające te dane. Tak jak w pliku poniżej w jednym miejscu zastosowałem ten styl ręcznie, a chciałbym by działo się to automatycznie na podstawie właśnie najbliższej daty.

[Jermor]

To nie jest kosmetyka. Dlatego zadając pytanie na forum należy dokładnie opisywać co się chce osiągnąć. Inaczej mówiąc powinieneś napisać, że dane są zorganizowane w pięciokomórkowe wierszowe bloki, w których ostatnia komórka zawiera datę. Podświetlenie ma wyróżnić cały wiersz takiego bloku, który zawiera najbliższą nadchodzącą, w stosunku do dnia bieżącego, datę. W twoim przypadku trzeba całkowicie inaczej zorganizować formatowanie warunkowe.
Komórce zawierającej obliczenie pomocnicze przypisałem nazwę "iledni" i tę nazwę będę stosował zamiast adresu.
Formatowanie warunkowe przygotowałem na podstawie pierwszego bloku danych w twoim arkuszu, czyli komórek od A4:E4. W tym celu każda komórka ma przypisane swoje formatowanie, czyli jest 5 takich formatowań i trzeba je przypisać oddzielnie dla każdej z tych komórek:

• A4: E4-DZIŚ()=iledni
• B4: E4-DZIŚ()=iledni
• C4: E4-DZIŚ()=iledni
• D4: E4-DZIŚ()=iledni
• E4: E4-DZIŚ()=iledni

Teraz można zaznaczyć te pięć komórek i wykonać "Kopiuj", następnie zaznaczyć ten obszar, w którym takie formatowanie ma zostać powielone. Wystarczy zaznaczyć tylko pierwszą kolumny tego obszaru i wywołać "Wklej specjalnie" (CTRL+SHIFT+V), zaznaczając w okienku dialogowym w sekcji "Wybór" jedną opcję: "Formaty". Powtórzyć tę czynność wklejania specjalnego dla każdego potrzebnego obszaru.
Wyniki zobaczysz w załączonym pliku.

[MarymeK]

Wielkie dzięki, dokładnie o to mi chodziło.

[MarymeK]

Jermor, czy istniej możliwość przypisania skrótu klawiaturowego do konkretnej formuły??

Np. naciśnięcie klawiszy ctrl+shit+D wstawia formułę =DZIŚ()+14, ale bez aktualizacji przy każdym otwarciu arkusza. Szukałem w Narzędzia - Dostosuj - Klawiatura ale nie widzę tam możliwości wpisania własnych funkcji.

[Jermor]

Najpierw sprawy porządkowe.
Na naszym forum nie dozwolone jest dołączanie elementów niezwiązanych z tematyką forum. Dlatego musisz usunąć link reklamowy.
Dobrym obyczajem jest podanie jakiego programu (w jakiej wersji i na jakim systemie operacyjnym) używasz. Ułatwia to pomoc.
Teraz temat daty.
Można to zrobić. Jednak należy utworzyć makro, które taką datę wstawi. Niestety, ale w przypadku tego problemu nie można skorzystać z opcji nagrywania makr. Dlatego napisałem ci takie makro.

Kod: Zaznacz cały

sub dz
REM Makro wstawia do aktywnej komórki datę o 14 dni późniejszą niż dzień bieżący.
REM Autor: Jermor, Polskie forum ApacheOpenOffice i LibreOffice (2021)
On Local Error GoTo wychodzimy
ThisComponent.CurrentSelection.string=date+14
Exit Sub
wychodzimy:
MsgBox "Możesz wskazać tylko jedną komórkę!!!",48,"Wstawianie daty bieżacej+14"
end sub

Możesz je dodać albo do swojego pliku, albo do kontenera "Moje makra". Dopiero teraz możesz narzędziem "Dostosuj" przypisać to makro do skrótu klawiaturowego. Procedura jak to zrobić (co prawda przy przypisaniu makra do przycisku, ale proces jest analogiczny przy skrócie) opisana i pokazana jest np. tutaj https://yestok.pl/lbo/y60.php

[MarymeK]

Super, wszystko działa jak należy. Wielkie dzięki.

[MarymeK]

Jermor, mam pytanko co do tego makro. Działa super ale chciałem je również zastosować w tabeli w writerze i tam wyskakuje mi ten komunikat który jest zawarty w tym makro "Można wskazać tylko jedną komórkę" o co to chodzi czy w writer nie mogę go stosować??

[Jan_J]

Na szybko, we Writerze dokumenty są zbudowane nieco inaczej. W Calcu zawsze zaznaczona / aktywna jest jakaś komórka albo zespół komórek; we Writerze to aktywne „coś” ma inną budowę wewnętrzną. Wobec tego instrukcja

Kod: Zaznacz cały

ThisComponent.CurrentSelection.string=date+14

generuje we Writerze błąd. A że obsługa błędów jest w tym makrze uproszczona do przypadku Calc, komunikat jest nieadekwatny.

[MarymeK]

ok, a czy da się stworzyć makro które działo by tak samo tylko w writerze??

[Jan_J]

Da się. Tylko potrzebna jest reinterpretacja problemu. We Writerze „zaznaczenie” jest czym innym niż w Calcu. W tabeli Writera, nawet jeśli w komórce stoi kursor, to jeszcze nie znaczy, że jest ona „zaznaczona”.

[Jermor]

Zmodyfikowałem makro. Teraz działa w Calcu i w Writerze. W Writerze makro analizuje gdzie znajduje się kursor. Data wstawiana jest tylko do zwykłego tekstu w miejscu, w którym znajduje się kursor oraz do komórki tabeli, zamieniając jej dotychczasową zawartość na datę.

Kod: Zaznacz cały

sub dz
REM Makro wstawia datę przesunietą o 14 dni do przodu wzgledem daty bieżącej.
REM Robi to tylko w dokumentach Calc i Writer.
REM Autor: Jermor, Polskie Forum Apache OpenOffice i LibreOffice (2021)
Dim oDoc As Object
Dim oText As Object
Dim oVC As Object
Dim oTCurs As Object
Dim dData As Date
Const sCalc = "com.sun.star.sheet.SpreadsheetDocument"
Const sWriter = "com.sun.star.text.TextDocument"
Const sPasek="Wstawianie daty bieżącej+14"
dData=date+14
oDoc = ThisComponent
If oDoc.SupportsService(sCalc) Then 
	REM Makro wstawia do aktywnej komórki datę o 14 dni późniejszą niż dzień bieżący.
	REM Autor: Jermor, Polskie forum ApacheOpenOffice i LibreOffice (2021)
	On Local Error GoTo wychodzimy
	oDoc.CurrentSelection.string=dData
	Exit Sub
End If
If oDoc.SupportsService(sWriter) Then
REM Makro wstawia do dokumentu tekstowego datę o 14 dni późniejszą .
REM Nie wstawia daty do ramki ani do pola.
REM Data wstawiana do komórki tabeli, zastępuje jej zawartość.
	oVC = oDoc.getCurrentController().getViewCursor()
	If NOT IsEmpty(oVC.Cell) Then oVc.Cell.string=dData : Exit Sub	
	If NOT IsEmpty(oVC.TextField) Then MsgBox "Nie wstawiam daty do pola",48,sPasek : Exit Sub
	If NOT IsEmpty(oVC.TextFrame) Then MsgBox "Nie wstawiam daty do ramki",48,sPasek : Exit Sub
	oText = oDoc.Text 
	oTCurs = oText.createTextCursorByRange(oVC.getStart())
	oText.insertString(oTCurs, dData & " ", FALSE)
	Exit Sub
End If
MsgBox "W tym dokumencie nie możesz użyć tego makra!!!",48, sPasek
Exit sub
wychodzimy:
MsgBox "Możesz wskazać tylko jedną komórkę!!!",48,sPasek
end Sub

[MarymeK]

Dzięki, teraz działa i w obu programach