Zmiana kryterium wyszukiwania w wyszukaj.pionowo [SOLVED]

[Rafał_Zmys]

Witam
Mam mam problem z podmianą adresu komórek w WYSZUKAJ.PIONOWO

ten wiersz działa prawidłowo i komórka koloruje się jak jest prawda.

=LICZ.WARUNKI(D3:M3; JEŻELI.BŁĄD(WYSZUKAJ.PIONOWO(TEKST($B3; "dd.mm.yyyy") & " " & TEKST($C3; "hh:mm"); DANE!$A$6:$Y$1500; 25; 0); 0))

chciałem aby funkcja sczytywała i zmieniała date i godzinę według schematu

=JEŻELI.BŁĄD(WYSZUKAJ.PIONOWO(TEKST(JEŻELI(GODZINA(C3) = 14; DATA(ROK(B3); MIESIĄC(B3); DZIEŃ(B3)) + CZAS(22;0;0); JEŻELI(GODZINA(C3) = 22; DATA(ROK(B3); MIESIĄC(B3); DZIEŃ(B3)+1) + CZAS(14;0;0); B3); "dd.mm.yyyy") & " " & TEKST(JEŻELI(GODZINA(C3) = 14; DATA(ROK(B3); MIESIĄC(B3); DZIEŃ(B3)) + CZAS(22;0;0); JEŻELI(GODZINA(C3) = 22; DATA(ROK(B3); MIESIĄC(B3); DZIEŃ(B3)+1) + CZAS(14;0;0); C3); "hh:mm:ss"); DANE!$A$6:$Y$1500; 25; 0); 0)))
daje błąd Błąd:504

Data jest w kolumnie B a godzina w C
Jeżeli w kolumnie B jest godzina 14 to podmienić ma na 22 data bez zmian,
jeżeli w kolumnie B jest godzina 22 to podmienić na 14 a date jeden dzień do przodu.
Nie wiem czy tak można to wykonać, ewentualnie jak wpisać godzinę bo komórka wyświetla 14:00 lub 22:00

[Jermor]

Spróbuj opisać to co chcesz osiągnąć bardziej zrozumiale. Zaczynasz od zdania

Mam mam problem z podmianą adresu komórek w WYSZUKAJ.PIONOWO,

⁣po którym informujesz, że

ten wiersz działa prawidłowo i komórka koloruje się jak jest prawda.

Zupełnie nie wiem, o co chodzi w tym opisie.
Dalej przytaczasz swoją formułę, która ma policzyć, ile razy wartość wyznaczona funkcją WYSZUKAJ.PIONOWO(), (w której wyszukiwane jest połączenie daty i godziny podanych w komórkach B3 i C3 w obszarze znajdującym się w arkuszu DANE określonym adresem $A$6:$Y$1500.)
Tutaj widzę problem. W LibreOffice nazwę arkusza od adresu w tym arkuszu rozdziela się znakiem kropki, a nie wykrzyknika. Ponadto z powodu ewentualnego braku poszukiwanej daty funkcję WYSZUKAJ.PIONOWO() ująłeś dodatkowo w JEŻELI.BŁĄD().
Działając na datach, zdecyduj się raczej na właściwości Calca pozwalające manipulować datami, a nie na postać tekstowego zapisu daty.
Do komórki możesz wpisać ciąg w postaci zapisu daty i czasu, np. taki: 28.10.24 14:00, a zostanie on automatycznie przekształcony w liczbę daty zawierającą także czas. Analogicznie wpisanie ciągu np. 29.10.24 przekształci go w numer seryjny dnia i sformatuje komórkę jako datę, Aby wpisać czas, należy po liczbie godzin postawić dwukropek (po nim można już niczego nie wpisywać a jeżeli chcemy wpisać tylko liczbę minut, np. 21, to musimy to wpisać jako ciąg: 0:21)
W stopce podajesz, że używasz wersji 24.8.2, możesz zatem wykorzystać nową funkcję X.WYSZUKAJ(), więc twoja formuła mogłaby wyglądać tak:

Kod: Zaznacz cały

=LICZ.WARUNKI(D3:M3; X.WYSZUKAJ($B3+$C3;DANE.$A$6:$A$1500;$Y$6:$Y$1500;0))

przy założeniu, że obszar A6:A1500 zawiera liczbę odpowiadającą dacie połączonej z czasem, czyli po sformatowaniu kodem "DD.MM.YYYY HH:MM:SS" wyglądającej jak ciąg pokazany powyżej.

Dalej

chciałem aby funkcja sczytywała i zmieniała date i godzinę według schematu

Nie wiem, do jakiej komórki ta formuła jest przypisana jednak, wykorzystując właściwości daty, o jakich wspomniałem powyżej, można ją znacznie uprościć, zamiast:
TEKST(JEŻELI(GODZINA(C3) = 14; DATA(ROK(B3); MIESIĄC(B3); DZIEŃ(B3)) + CZAS(22;0;0); JEŻELI(GODZINA(C3) = 22; DATA(ROK(B3); MIESIĄC(B3); DZIEŃ(B3)+1) + CZAS(14;0;0); B3); "dd.mm.yyyy") & " " & TEKST(JEŻELI(GODZINA(C3) = 14; DATA(ROK(B3); MIESIĄC(B3); DZIEŃ(B3)) + CZAS(22;0;0); JEŻELI(GODZINA(C3) = 22; DATA(ROK(B3); MIESIĄC(B3); DZIEŃ(B3)+1) + CZAS(14;0;0); C3); "hh:mm:ss")
(nawiasem mówiąc, cała ta pokolorowana przeze mnie część jest zbędna, bo dla tej podstawowej części mogłeś podać format "DD.MM.YYYY HH:MM:SS") można napisać:

Kod: Zaznacz cały

WARUNKI(GODZINA(C3)=14;B3+C3+CZAS(8;;);Godzina(C3)=22;B3+C3+CZAS(16;;);1;B3)

Wykorzystując, także nową, funkcję LET() można byłoby całą tę formułę zapisać:

Kod: Zaznacz cały

=LET(termin;B3+C3;godz;GODZINA(C3);X.WYSZUKAJ(WARUNKI(godz=14;termin+CZAS(8;;);godz=22;termin+CZAS(16;;);1;B3);DANE.$A$6:$A$1500;$Y$1500;0)

Jeśli chodzi o wpisywanie czasu, to aby został on automatycznie zamieniony na "liczbę czasu" należy go wprowadzać, wpisując znak dwukropka po godzinie i minucie. Jeśli nie podajemy sekund, można nie wpisywać dwukropka po minutach. Po godzinie zawsze należy postawić dwukropek nawet wtedy gdy nie będziemy podawali minut.

Mam nadzieję, że nie pomyliłem się w formułach, bo wszystkie pisałem na sucho.

[Rafał_Zmys]

Dziękuje za bardzo szybką odpowiedz, ja dopiero dziś znalazłem czas.
Pewnie to źle wytłumaczyłem ale podane rozwiązania nie działają.

Calc nie rozpoznaje nazwy arkusza z kropką "DANE." wyświetla "dane." natomiast rozpoznaje "DANE!" możliwe ze jakieś ustawienia systemowe są pozmieniane.
Uproszczenie formuły "TEKST($B3; "dd.mm.yyyy") & " " & TEKST($C3; "hh:mm")" do "$B3+$C3" też nie jest jednoznaczne bo wynik pierwszej formuły to "23.10.2024 14:00" a druga wyświetla się "2024-10-23 14:00" mimo że data w $B3 jest wyświetlana tak "23.10.2024".

Jeżeli to działa i wyszukuje prawidłowo:
=LICZ.WARUNKI(D3:M3; JEŻELI.BŁĄD(WYSZUKAJ.PIONOWO(TEKST($B3; "dd.mm.yyyy") & " " & TEKST($C3; "hh:mm"); DANE!$A$6:$Y$1500; 25; 0); 0))

to podmiana na to już nie:
=LICZ.WARUNKI(D3:M3; X.WYSZUKAJ($B3+$C3;DANE!$A$6:$A$1500;$Y$6:$Y$1500;0))
Kreator funkcji pokazuje błąd
"DANE!$A$6:$Y$1500 = #AGR! {"10.09.2024 14:00";"10.09.2024 22:00";"11.09.2024 14:00";"11.09.2024 22:00";"12.09.2024 14:00";"12.09.2024 22:00";...
$Y$6:$Y$1500 = #AGR! {0;0;0;0;0;0;0;0...


=LICZ.WARUNKI(D3:M3; X.WYSZUKAJ(TEKST($B3; "dd.mm.yyyy") & " " & TEKST($C3; "hh:mm");DANE!$A$6:$A$1500;$Y$6:$Y$1500;0))
Kreator funkcji również pokazuje błąd
"DANE!$A$6:$Y$1500 = #AGR! {"10.09.2024 14:00";"10.09.2024 22:00";"11.09.2024 14:00";"11.09.2024 22:00";"12.09.2024 14:00";"12.09.2024 22:00";...
$Y$6:$Y$1500 = #AGR! {0;0;0;0;0;0;0;0...

w wolnych chwili podeśle arkusz dla zobrazowania.

[Rafkus]

Calc nie rozpoznaje nazwy arkusza z kropką "DANE." wyświetla "dane." natomiast rozpoznaje "DANE!" możliwe ze jakieś ustawienia systemowe są pozmieniane.

Widocznie w opcjach programu dla Calca, w opcjach formuły jako składnię masz wybraną opcję Excel A1

Składnia formuły
Możliwe są trzy opcje. Przyjrzyjmy się im na przykładzie. W przykładowym skoroszycie są dwa arkusze, Arkusz1i Arkusz2. W komórce A1 Arkusza1 jest odniesienie do komórki C4 w Arkuszu2.

• Calc A1 - Domyślne ustawienie w LibreOffice Calc. Odniesienie wygląda następująco =$Arkusz2.C4
• Excel A1- Domyślne ustawienie w Microsoft Excel. Odniesienie wygląda następująco =Arkusz2!C4
• Excel R1C1 - To jest relatywne adresowanie wiersz/kolumna znane z Microsoft Excel. Odniesienie wygląda następujące =Arkusz2!R[3]C[2]

[Jermor]

Co do wykrzyknika, odpowiedź napisał już kolega @Rafkus.
W mojej odpowiedzi zasugerowałem to, abyś niezbędne działania na datach wykonywał na tzw. liczbie daty i czasu, a nie na ciągach tekstowych w postaci np. 23.10.2024 14:00 (co uzyskujesz, wykorzystując funkcję TEKST()).
Liczbę daty i czasu możesz bowiem zawsze przedstawić w wybrany przez siebie sposób przy pomocy formatowania. O tym jak może to działać, pokazuje załącznik. Wymagana jest wersja 24.8+ LibreOffice.

[Rafał_Zmys]

Dziękuje Panowie rozjaśniło mi to problem dat.