Wyszukiwanie liczby w zależności od liczby wcześniejszej.

Użytkowanie arkusza kalkulacyjnego
Awatar użytkownika
Jermor
Posty: 2233
Rejestracja: sob paź 12, 2013 11:09 am
Kontakt:

Re: Wyszukiwanie liczby w zależności od liczby wcześniejszej

Post autor: Jermor »

Niestety @JedenGość niezwykle chaotycznie opisuje to co chce osiągnąć. Zaczął od stwierdzenia, że interesują nas tylko liczby mniejsze niż -6. Na tej podstawie wykoncypowałem taki opis:
Wiersze danych zawierają co najwyżej 23 liczby całkowite ułożone względem pierwszej liczby w wierszu w ten sposób, że każda kolejna liczba ma znak przeciwny do liczby ją poprzedzającej.
Należy policzyć:
  1. Ile liczb w wierszu jest mniejszych od -6.
  2. Ile razy pojawia się sekwencja dwóch liczb, z których pierwsza jest mniejsza od -6 a druga – większa od 1.
  3. Ile razy pojawi się sekwencja trzech liczb, z których pierwsza jest mniejsza od -6, druga jest równa 1, a trzecia jest równa -1.
  4. Ile razy pojawi się sekwencja trzech liczb, z których pierwsza jest mniejsza od -6, druga jest równa 1, a trzecia jest równa -2.
  5. Ile razy pojawi się sekwencja trzech liczb, z których pierwsza jest mniejsza od -6, druga jest równa 1, a trzecia jest równa -3.
  6. Ile razy pojawi się sekwencja trzech liczb, z których pierwsza jest mniejsza od -6, druga jest równa 1, a trzecia jest równa -4.
  7. Ile razy pojawi się sekwencja trzech liczb, z których pierwsza jest mniejsza od -6, druga jest równa 1, a trzecia jest równa -5.
  8. Ile razy pojawi się sekwencja trzech liczb, z których pierwsza jest mniejsza od -6, druga jest równa 1, a trzecia jest równa -6.
  9. Ile razy pojawi się sekwencja trzech liczb, z których pierwsza jest mniejsza od -6, druga jest równa 1, a trzecia jest mniejsza od -6.
  10. Wyznaczyć liczbę pomocniczą 0 lub 1. (Ta liczba jest prawdopodobnie niezbędna, aby policzyć takie wystąpienia liczby mniejszej od -6, którego nie można dopasować do żadnego ciągu opisanego punktami od 2 do 9.) Obliczenie następuje wg reguły:
    1. Jeżeli przedostatnia liczba w wierszu jest mniejsza niż -6 i ostatnią liczbą jest 1, liczba pomocnicza wynosi 1, w przeciwnym wypadku 0 (zapewne dlatego, że po dopisaniu następnej liczby, a będzie to na pewno liczba mniejsza od 0, tak rozpoczęty ciąg będzie pasował do jednego ze wzorców z punktów od 3 do 9).
    2. Jeżeli ostatnia liczba w wierszu jest mniejsza od -6, liczba pomocnicza wynosi 1 w przeciwnym wypadku 0 (a to dlatego, że każda następna dopisana liczba, a będzie to na pewno liczba dodatnia, spowoduje dopasowanie otrzymanego ciągu albo do punktu 2. gdy będzie to liczba większa od 1, albo sprawi, że tak rozpoczęty ciąg spełni warunki punktu "a" powyżej, gdyż będzie to 1).
  11. . Liczbę pomocniczą oraz wartość z pkt.1 należy powiększyć o 1, wtedy gdy ostatnia liczba w wierszu jest mniejsza od -6. (Sądzę, że ten punkt jest zbędny. Z arytmetycznego punktu widzenia: jeśli N jest sumą a+b+c+d, to oczywiste jest, że N+1 jest sumą a+b+c+d+1, więc dodawanie jedynki do obu stron tego równania jest bez sensu.)
Powyższy opis nie uwzględnia sytuacji gdy wiersz danych zawiera tylko jedną liczbę. @JedenGość nic o tym nie wspomina, a my założyliśmy, że zawsze wystąpi liczba przedostatnia. Jeśli jej nie będzie funkcja INDEKS() może wygenerować błąd. Ten problem może wystąpić przy obliczeniach punktu a. Wykorzystując funkcję JEŻELI.BŁĄD() można ten problem rozwiązać.

Jeśli to, co opisałem, jest prawdą, to moja wersja arkusza zawiera właściwą metodę.
Tu cytat:
JedenGość pisze:To co napisałem w tabeli było właściwe. Mamy do czynienia z jedną liczbą <-6, czyli -7 z N20. Następna dodatnia =1 i kolejna ujemna jest >=-6 (dokładnie -6). W tej sytuacji kolumna AH powiększa się o 1.
Tylko że ta sekwencja odpowiada temu co opisuje punkt 8 i odpowiednia wartość powinna znaleźć się w kolumnie AF.
Inny przykład:
Zgodnie z opisem pkt 9. jeśli ostatnią wartością w wierszu jest liczba >=6 w AH ma pojawić się 1.
(To jest ten fragment, którego nie rozumiem i powyżej opisałem własne rozumowanie). Mimo to sądzę, że chodzi o liczbę -6, a nie 6. Lecz jeśli tak, a to wynika z analizy przykładu w pliku JedenGosc_20211127_8.ods, to zasada powinna brzmieć >=-6 i <0, w przeciwnym razie jedynka pojawiłaby się w AH prawie zawsze. Każda liczba dodatnia jest przecież większa od -6.

Co do kolumny AI. Widzisz w niej wyniki PRAWDA, bo przy formatowaniu komórki, jako wartości logicznej, wartości 0 są traktowane jako FAŁSZ natomiast wartości różne od 0 jako PRAWDA. Jeśli obejrzysz wyniki w zwykłym formatowaniu, zobaczysz, że AI7 i AI18 zawierają w rzeczywistości wyniki -1.

Jeszcze uwaga na temat nadmiernego komplikowania formuł. Poniższa formuła

Kod: Zaznacz cały

=JEŻELI(INDEKS($A3:$W3;ILE.LICZB($A3:$W3))>=-6;JEŻELI(Y3=SUMA(Z3:AH3));JEŻELI(Y3=SUMA(Z3:AH3))-1)
może być zapisana następująco:

Kod: Zaznacz cały

=Y3=SUMA(Z3:AH3)-(INDEKS($A3:$W3;ILE.LICZB($A3:$W3))<-6)
AOO 4.1.15, LO 7.5.9 (x64) na Windows 10 64bit
Ważne!
Jeśli twój problem został rozwiązany, wróć do swojego pierwszego postu, przejdź do edycji i dopisz [SOLVED] w temacie.
Inni, którzy mają podobny problem, będą wiedzieli, że istnieje jego rozwiązanie.
JedenGość
Posty: 195
Rejestracja: czw mar 07, 2019 6:54 pm

Re: Wyszukiwanie liczby w zależności od liczby wcześniejszej

Post autor: JedenGość »

Zgodzę się z Tobą, że wyjaśniłem to chaotycznie. Chociaż powiedziałbym, że może nie chaotycznie, a z BŁĘDAMI. Po prostu opisując co potrzebuję pomyliłem się kilka razy myśląc o jednym, a pisząc drugie.

Wiem. Zdaję sobie sprawę, że pisząc o jednym, a myśląc o drugim wprowadzam innych w błąd, przez co wypracowane rozwiązania mogą być błęne. Jezcze raz przepraszam.

Nawet wyżej zrobiłem błąd:
Zgodnie z opisem pkt 9. jeśli ostatnią wartością w wierszu jest liczba >=6 w AH ma pojawić się 1.
Jermor, słusznie zauważyłeś, że powinno być >=-6.

Co do kolumny AH.

Opisywałem to bodajże w drugim pliku.

1. Ostatnia liczba nigdy nie będzie ostatnią.
2. Jeżeli jest ujemna to będzie się pomniejszać, lub w kolejnym polu pojawi się dodatnia.
3. Jeżeli będzie dodatnia, to będzie się powiększać lub w kolejnym polu pojawi się ujemna.
4. Jeżeli przedostatnia liczba jest <-6, a ostatnia >1, to kolumna Z powiększa się o 1.
5. Jeżeli trzecia od końca liczba jest <-6, a ostatnia >=-6, to kolumna AH powiększa się o 1. Dlaczego? Dlatego, że nie wiem, jaka to będzie ostatecznie liczba. Granicą jest tu dla mnie -7. Czyli jeżeli jest >=-6 to wtedy AH, a jeżeli <-6, wtedy AG.
6. Jeżeli przedostatnia jest <-6, a ostatnia =1 to wtedy AH zgodnie z zasadą punkt wyżej.
7. Zgodnie z powyższym, jeżeli dopisujemy 1 do AH, to nie powinno się to dublować we wcześniejszych komórkach. Tzn. np. trzecia od końca wynosi -9, a ostatnia -4. Wtedy AH powiększamy o 1, ale już AD nie.
8. Jeżeli przedostatnia lub trzecia od końca jest >=-6 to w ogóle nie ma tematu.

P.S. Ciąg liczb nigdy nie będzie jednoliczbowy, a jeżeli nawet to jest to dla mnie nieistotne i wtedy faktycznie, można to obejść funkcją JEŻELI.BŁĄD.
Libre Office 7.5.4.2 / Windows 11
Awatar użytkownika
Rafkus
Posty: 511
Rejestracja: czw kwie 12, 2018 10:26 pm

Re: Wyszukiwanie liczby w zależności od liczby wcześniejszej

Post autor: Rafkus »

Myślałem jeszcze nad formułą w AH.
Sądząc, że moje formuły dają spodziewane wyniki wymieniłem ze dwie błahe prywatne wiadomości. Wczoraj wieczorem @JedenGość uświadomił mi że porównanie kontrolne z kolumny AI sypie się przypadku:
ale testując dopisałem w J15 -8, a P20 zamieniłem -6 na -7.
Przemyślałem sprawę jeszcze raz, przejrzałem wpisy i doszedłem do wniosku że ta nieszczęsna formuła powinna wyglądać tak:
  • Jeżeli ostatnia cyfra jest mniejsza niż - 6 to wynik powinien wynosić 1
  • jeżeli przedostatnia <-6 i ostatnia liczba = 1 to wynik wynosi 1
  • jeżeli przed przedostatnia <-6 , przedostatnia liczba = 1 i ostatnia liczba <0 to ma być zwrócona wartość 1
  • każda inna kombinacja ma dać 0
Takie wyniki uzyskałem stosując formułę:

Kod: Zaznacz cały

=WARUNKI(INDEKS($A3:$W3;1;ILE.LICZB($A3:$W3))<-6;1;
I(INDEKS($A3:$W3;1;ILE.LICZB($A3:$W3)-1)<-6; INDEKS($A3:$W3;1;ILE.LICZB($A3:$W3))=1); 1;
I(INDEKS($A3:$W3;1;ILE.LICZB($A3:$W3)-2)<-6; INDEKS($A3:$W3;1;ILE.LICZB($A3:$W3)-1)=1; INDEKS($A3:$W3;1;ILE.LICZB($A3:$W3))<0); 1;
1;N("zero dla każdego innego wyniku"))
Tylko że znowu ta formuła daje inne wyniki od spodziewanych, ale porównanie kontrolne : =Y3=SUMA(Z3:AH3) teraz sygnalizuje prawdę ...
Może wystarczy pobawić się kolejnością warunków i wyników?
Ostatnio zmieniony pt gru 03, 2021 6:56 pm przez Rafkus, łącznie zmieniany 1 raz.
Powód: Usunięcie pliku, poniżej poprawiona wersja
LibreOffice 7.4.6 (preferowany) oraz OpenOffice 4.1.6. Widows 10
OpenOffice 4.1.3. oraz Libre 4.2.5.2 Windows XP
JedenGość
Posty: 195
Rejestracja: czw mar 07, 2019 6:54 pm

Re: Wyszukiwanie liczby w zależności od liczby wcześniejszej

Post autor: JedenGość »

Pomijając kwestię sumy kontrolnej wszystko by się zgadzało, poza:

1. S7 - ostatnia jest <-6, ale trzecia od końca =-1.
2. I18 - ostatnia jest <-6, ale trzecia od końca =-4.

Czyli nie mam tutaj spełnionego warunku, że w przypadku ostatniej ujemnej, trzecia od końca musi wynosić <-6.

Chyba żeby przyjąć założenie, że jeżeli ostatnia wynosi <-6 to wtedy AH+1. Zmienia to nieco zasady z punktu widzenia pisania formuła jako takich. Jednak biorąc pod uwagę moje potrzeby jeśli chodzi o merytoryczne wskazania tych formuł to... to ma sens. Porównanie kontrolne z AK też się zgadza.

Jednak gdy, testując zmieniam Q7 z -1 na -7 w AK pojawia się FAŁSZ. To w sumie mało istotne. Gorzej, że AH7 dałej zawiera 1, a AG7 nic...
Libre Office 7.5.4.2 / Windows 11
Awatar użytkownika
Rafkus
Posty: 511
Rejestracja: czw kwie 12, 2018 10:26 pm

Re: Wyszukiwanie liczby w zależności od liczby wcześniejszej

Post autor: Rafkus »

JedenGość pisze:Jednak gdy, testując zmieniam Q7 z -1 na -7 w AK pojawia się FAŁSZ. To w sumie mało istotne. Gorzej, że AH7 dałej zawiera 1, a AG7 nic...
UPS; komórka AG7 nie zawiera formuły (podobnie jak AG18) i dlatego pojawił się FAŁSZ...
I znowu troszkę pokombinowałem, zamieniłem warunki miejscami, wyrzuciłem test ostatniej liczby i otrzymałem coś takiego:

Kod: Zaznacz cały

=WARUNKI(
I(INDEKS($A3:$W3;1;ILE.LICZB($A3:$W3)-2)<-6; INDEKS($A3:$W3;1;ILE.LICZB($A3:$W3)-1)=1; INDEKS($A3:$W3;1;ILE.LICZB($A3:$W3))>=-6);1;
I(INDEKS($A3:$W3;1;ILE.LICZB($A3:$W3)-1)<-6; INDEKS($A3:$W3;1;ILE.LICZB($A3:$W3))=1);1;
1; N("zero dla każdego innego wyniku"))
LibreOffice 7.4.6 (preferowany) oraz OpenOffice 4.1.6. Widows 10
OpenOffice 4.1.3. oraz Libre 4.2.5.2 Windows XP
Awatar użytkownika
Rafkus
Posty: 511
Rejestracja: czw kwie 12, 2018 10:26 pm

Re: Wyszukiwanie liczby w zależności od liczby wcześniejszej

Post autor: Rafkus »

Poprawiony plik

EDIT:
Podmieniłem plik, zmiany:
- Dodany arkusz z wersją alternatywną, Która to miała być nieco inna, ale wyszła bardzo zbliżona do rozwiązania @Jermora (REGEX rządzi :lol: )
- poeksperymentowałem z funkcją WARUNKI - nie znałem jej wcześniej...

EDIT: plik z opisem poniżej
Ostatnio zmieniony pn gru 06, 2021 12:54 am przez Rafkus, łącznie zmieniany 1 raz.
Powód: Usunięcie pliku, poniżej poprawiona wersja
LibreOffice 7.4.6 (preferowany) oraz OpenOffice 4.1.6. Widows 10
OpenOffice 4.1.3. oraz Libre 4.2.5.2 Windows XP
JedenGość
Posty: 195
Rejestracja: czw mar 07, 2019 6:54 pm

Re: Wyszukiwanie liczby w zależności od liczby wcześniejszej

Post autor: JedenGość »

Rafkus, wychodzi na to, że dopiąłeś swego!

Przetestowałem kilka wierszy i jest ok.

Powrzucam to teraz do właściwych plików.

Zobaczymy jak tam to będzie się "kompatybilizować" :D

Dziękuję!
Libre Office 7.5.4.2 / Windows 11
JedenGość
Posty: 195
Rejestracja: czw mar 07, 2019 6:54 pm

Re: Wyszukiwanie liczby w zależności od liczby wcześniejszej

Post autor: JedenGość »

Wracam jeszcze do tematu.

Nie wiem, czy formuły są zależne od ilości pól oraz ich rozmieszczenia w arkuszu, ale gdy je skopiowałem do innego układu to się sypie. Przykład w załączniku. Na żółto zaznaczyłem pola, które powinny zawierać wartość 1.
Załączniki
JedenGosc_20211127_10.ods
(19.88 KiB) Pobrany 88 razy
Libre Office 7.5.4.2 / Windows 11
Awatar użytkownika
Rafkus
Posty: 511
Rejestracja: czw kwie 12, 2018 10:26 pm

Re: Wyszukiwanie liczby w zależności od liczby wcześniejszej

Post autor: Rafkus »

problemem jest ta formuła:

Kod: Zaznacz cały

KOLUMNA($L2:$BI2)
Funkcja kolumna zwraca zazwyczaj numer kolumny czyli KOLUMNA(L1) = 12, KOLUMNA(M100) = 13, itd. W funkcji LICZ.WARUNKI miała ona dostarczyć numer pozycji na której są liczby - musi zestaw liczb zaczynających się od 1 . Tutaj wszystko się poprzesuwało...
Zalecenia:
  • zadbać aby tutaj zakres zaczynał się od kolumny A: KOLUMNA($A2:$??I2) dałem pytajniki bo nie chciało mi się sprawdzać dokąd ma sięgać - jest na to lepszy sposób.
  • w pierwszym wierszu masz kolejne ponumerowane wartości odpowiadające numerom pozycji zatem zastąp w tych formułach
    to: KOLUMNA($L2:$BI2) na to $L$1:$BI$1 <- polecam ten sposób
  • trzeci sposób to dokonanie pewnych obliczeń, aby zakres zaczynał się od 1, np w ten sposób : KOLUMNA($L2:$BI2) - KOLUMNA($L$2) +1 czyli od numeru kolumny odjąć numer pierwszej kolumny z zakresu i dodania 1 (opcjonalnie można odjąć numer kolumny przed twym zakresem) - ale jak napisałem wcześniej polecam środkową opcję
LibreOffice 7.4.6 (preferowany) oraz OpenOffice 4.1.6. Widows 10
OpenOffice 4.1.3. oraz Libre 4.2.5.2 Windows XP
JedenGość
Posty: 195
Rejestracja: czw mar 07, 2019 6:54 pm

Re: Wyszukiwanie liczby w zależności od liczby wcześniejszej

Post autor: JedenGość »

OK. Ale, albo czegoś nie rozumiem, albo coś nie działa. Pozmieniałem dla -1.

Kod: Zaznacz cały

=LICZ.WARUNKI(($L2:$BI2<-6)*2 & ($M2:$BJ2=1)*2 & ($N2:$BK2=-1)*2;"222";KOLUMNA($L$1:$BI$1);"<" & (ILE.LICZB($L2:$BI2)-2))
W BM17 dalej nie pokazuje właściwej wartości.
Libre Office 7.5.4.2 / Windows 11
Awatar użytkownika
Rafkus
Posty: 511
Rejestracja: czw kwie 12, 2018 10:26 pm

Re: Wyszukiwanie liczby w zależności od liczby wcześniejszej

Post autor: Rafkus »

pozbyć się funkcji KOLUMNA:

Kod: Zaznacz cały

=LICZ.WARUNKI(($L2:$BI2<-6)*2 & ($M2:$BJ2=1)*2 & ($N2:$BK2=-1)*2;"222"; $L$1:$BI$1;"<" & (ILE.LICZB($L2:$BI2)-2))
=LICZ.WARUNKI(($L2:$BI2<-6)*2 & ($M2:$BJ2=1)*2 & ($N2:$BK2=-1)*2;"222";KOLUMNA($L$1:$BI$1);"<" & (ILE.LICZB($L2:$BI2)-2))
LibreOffice 7.4.6 (preferowany) oraz OpenOffice 4.1.6. Widows 10
OpenOffice 4.1.3. oraz Libre 4.2.5.2 Windows XP
JedenGość
Posty: 195
Rejestracja: czw mar 07, 2019 6:54 pm

Re: Wyszukiwanie liczby w zależności od liczby wcześniejszej

Post autor: JedenGość »

O. To by było to.

W takim razie po co była ta funkcja KOLUMNA? (już zacząłem oglądać tutoriale na YT na jej temat)

Pojawiły mi się zera i jedynki. Jak uzyskać 0 w BL, BS i BT? JEŻELI nic mi nie daje, już sprawdziłem...

Edit. Już z grubsza wiem o co chodzi z KOLUMNA (to jest to samo co NR.KOLUMNY w excelu?).
Libre Office 7.5.4.2 / Windows 11
Awatar użytkownika
Jermor
Posty: 2233
Rejestracja: sob paź 12, 2013 11:09 am
Kontakt:

Re: Wyszukiwanie liczby w zależności od liczby wcześniejszej

Post autor: Jermor »

Uwzględniłem twoje wyjaśnienie dotyczące kolumny AH i podsyłam moje rozwiązanie.
Zawarte jest ono w Arkusz2. By nie rozbudowywać nadmiernie formuł, skorzystałem z trzech kolumn pomocniczych zawierających trzy ostatnie liczby w wierszu.
Zdążyłeś już "zatwierdzić" rozwiązanie @Rafkus, więc to będzie tylko wersja poglądowa (mam nadzieję, że spełniająca warunki).
Wykorzystałem tu działanie na tekście utworzonym z liczb w wierszu.
Zastosowałem inne podejście do wyliczenia zawartości AG. Z twojego twierdzenia wynika, że ilość wszystkich liczb <-6 jest równa sumie wartości w kolumnach od Z do AH. Wszystkie kolumny z wyjątkiem AG są stosunkowo proste do wyliczenia. To oznacza, że kolumnę AG można wyznaczyć jako różnicę między tą ilością a sumą z pozostałych kolumn.
Załączniki
JedenGosc_20211127 Jermor_3.ods
(28.78 KiB) Pobrany 73 razy
AOO 4.1.15, LO 7.5.9 (x64) na Windows 10 64bit
Ważne!
Jeśli twój problem został rozwiązany, wróć do swojego pierwszego postu, przejdź do edycji i dopisz [SOLVED] w temacie.
Inni, którzy mają podobny problem, będą wiedzieli, że istnieje jego rozwiązanie.
Awatar użytkownika
Rafkus
Posty: 511
Rejestracja: czw kwie 12, 2018 10:26 pm

Re: Wyszukiwanie liczby w zależności od liczby wcześniejszej

Post autor: Rafkus »

JedenGość pisze:po co była ta funkcja KOLUMNA?
We wcześniejszych wersjach pozycje zestawu danych nie były ponumerowane, a że formuły pisałem tak żeby nie było kolumn czy wierszy pomocniczych to w taki sposób stworzyłem takie dane.
JedenGość pisze:to jest to samo co NR.KOLUMNY w excelu?).
Tak
JedenGość pisze:Pojawiły mi się zera i jedynki. Jak uzyskać 0 w BL, BS i BT? JEŻELI nic mi nie daje, już sprawdziłem...
?? Nie rozumiem, chodzi o to aby zera były wyświetlane? One są w tej chwili ukryte formatowaniem komórki:
Kliknij na wybraną komórkę Prawym Przyciskiem Myszy , wyskoczy okienko na którym na samym dole będzie: Formatuj komórki. Na karcie Liczby wybierz jaki styl cię interesuje. (Ja wpisałem # co poukrywało zera jakoś wizualnie zaciemniały mi obraz sytuacji).
LibreOffice 7.4.6 (preferowany) oraz OpenOffice 4.1.6. Widows 10
OpenOffice 4.1.3. oraz Libre 4.2.5.2 Windows XP
Awatar użytkownika
Rafkus
Posty: 511
Rejestracja: czw kwie 12, 2018 10:26 pm

Re: Wyszukiwanie liczby w zależności od liczby wcześniejszej

Post autor: Rafkus »

@Jermor tak z ciekawości zapoznałem się z twoim rozwiązaniem i znalazłem pewne niedociągnięcie w kolumnie "AG". Masz tam formułę:

Kod: Zaznacz cały

=C4-SUMA(D4:J4)-L4
Otóż trzeba w niej jeszcze odjąć 1 jeśli ostatnią liczbą jest liczba mniejsza niż -6,

A tak poza tym Dzięki za pokazanie funkcji WARUNKI() - nie znałem jej.
LibreOffice 7.4.6 (preferowany) oraz OpenOffice 4.1.6. Widows 10
OpenOffice 4.1.3. oraz Libre 4.2.5.2 Windows XP
JedenGość
Posty: 195
Rejestracja: czw mar 07, 2019 6:54 pm

Re: Wyszukiwanie liczby w zależności od liczby wcześniejszej

Post autor: JedenGość »

Panowie, jestem poza domem, dlatego jestem mniej online.
Jermor, dziękuję za Twoje rozwiązanie. Jeszcze mu się nie przyglądałem (po zmianach), ale na pewno to zrobię. Być może okaże się lepsze, a jak nie to na pewno będzie świetnym poligonem do nauki.
Jeszcze raz dziękuję!
Libre Office 7.5.4.2 / Windows 11
Awatar użytkownika
Jermor
Posty: 2233
Rejestracja: sob paź 12, 2013 11:09 am
Kontakt:

Re: Wyszukiwanie liczby w zależności od liczby wcześniejszej

Post autor: Jermor »

Skoro @JedenGość jest zainteresowany, poprawiłem formuły zgodnie z sugestią @Rafkus.
Jakoś nigdzie nie zauważyłem informacji, że AG ma zostać pomniejszone o 1 jeśli ostatnią liczbą jest liczba mniejsza od -6. Ale też nie śledziłem kolejnych postów, ponieważ przyjąłem całkowicie inną metodę obliczeń. Dlatego tak ważnym jest dokładne opisanie zależności.
Sądzę, że sam opis algorytmu nie jest jasny. Przykład (nie wnikając w to, że autor poprawiał potem wartości liczbowe):
Jeżeli przedostatnia liczba w wierszu wynosi <6 to: w kolumnie AH powinno się znaleźć 1 jeżeli ostatnią liczbą w wierszu jest 1 lub 1jeżeli ostatnia liczba w wierszu wynosi >= -6.
Taki opis jest niejasny. Należałoby zapisać go mniej więcej tak:
Jeżeli przedostatnia liczba w wierszu wynosi <6 i ostatnią liczbą jest 1 lub jeżeli ostatnia liczba w wierszu wynosi >= -6, to w kolumnie AH powinna być wartość 1.

Sposób obliczenia liczby wystąpień trójelementowych układów: mniejsze od -6, jeden, równe -1 (do -6), powinien brzmieć: Ile razy pojawi się sekwencja trzech liczb, z których pierwsza jest mniejsza od -6, druga jest równa 1, a trzecia jest równa -1 (do -6) pod warunkiem, że nie są to trzy ostatnie liczby w wierszu.

A opis dla kolumny Z powinien mieć opis: W kolumnie Z powinna być wartość ile razy po -6 wystąpiła liczba >1, pomniejszona o 1 jeżeli ostatnią liczbą w ciągu jest liczba mniejsza od -6.

Zazwyczaj dodatkowym atutem jest zrozumienie, do czego służą obliczenia, jednak opis tego nie zawsze jest tak prosty, stąd konieczność precyzyjnego opisania co i jak ma zostać obliczone w poszczególnych komórkach.

Do @Rafkus. Użyłem funkcji WARUNKI(), ale potem zorientowałem się, że te kontrolowane warunki się wykluczają, więc można po prostu pododawać do siebie te wyrażenia warunkowe.
Załączniki
JedenGosc_20211127 Jermor_4.ods
(29.1 KiB) Pobrany 75 razy
AOO 4.1.15, LO 7.5.9 (x64) na Windows 10 64bit
Ważne!
Jeśli twój problem został rozwiązany, wróć do swojego pierwszego postu, przejdź do edycji i dopisz [SOLVED] w temacie.
Inni, którzy mają podobny problem, będą wiedzieli, że istnieje jego rozwiązanie.
Awatar użytkownika
Rafkus
Posty: 511
Rejestracja: czw kwie 12, 2018 10:26 pm

Re: Wyszukiwanie liczby w zależności od liczby wcześniejszej

Post autor: Rafkus »

Jermor pisze:Jakoś nigdzie nie zauważyłem informacji, że AG ma zostać pomniejszone o 1 jeśli ostatnią liczbą jest liczba mniejsza od -6.
bo to raczej samo wyszło z z warunku testującego poprawność rozwiązań niż z opisu problemu.

Chciałbym jeszcze raz zaprezentować inne rozwiązanie nad którym myślałem na początku, ale przez jakąś blokadę umysłową z niej zrezygnowałem. W zasadzie to rozwiązanie zostało zaprezentowane przez @Jermora. Wstyd się przyznać ale ZAPOMNIAŁEM :crazy: i nie potrafiłem wymyśleć :crazy:, jak policzyć ile razy w danym tekście wystąpiła pewna fraza. A przecież ja sam również korzystałem z tej metody... Tworząc tą alternatywną wersję chciałem przede wszystkim zapoznać się z względnie nowymi (przynajmniej dla mnie) funkcjami jakie daje LO. A więc zaczynam od początku:
  1. Normalizacja Danych
    Na początku postanowiłem pozmieniać dane z wiersza danych w jeden tekst według schematu:
    • liczby większe niż 1 zamieniałem na literę b;
    • liczby równe 1 zamieniałem na literę a;
    • liczby większe lub równe -6 przepisywałem ale pozbywałem się znaku minusa
    • liczby mniejsze niż -6 wpisywałem jako 7
    • po sprawdzeniu wyszło, że w miejscu pustej komórki zostawało wpisane zero, co mi nie przeszkadzało a nawet później pomogało ...
    Tak stworzone dane połączyłem w jeden tekst przy pomocy funkcji:

    Kod: Zaznacz cały

    =ZŁĄCZ.TEKST(WARUNKI(
    		$Arkusz1.$A3:$Arkusz1.$W3>1;	"b";
    		$Arkusz1.$A3:$Arkusz1.$W3=1;	"a";
    		$Arkusz1.$A3:$Arkusz1.$W3>-7;	-$Arkusz1.$A3:$Arkusz1.$W3;
    		$Arkusz1.$A3:$Arkusz1.$W3<-6;	7);
    	"00")
    
    Do każdego tworzonego ciągu dopisywałem jeszcze "00" te dwa zera będą pomocne gdy cały zakres danych będzie wypełniony liczbami.
    Funkcja WARUNKI bardzo uprościła moją wcześniejszą formułę WYBIERZ- tam musiałem wymyślić jakąś formułę dającą jakiś numer z przedziału 1..4 odpowiadający numerowi wyniku.
  2. Ile razy wystąpiła liczba mniejsza niż - 6 - to rozwiązał sam autor zagadek
  3. Ile razy wystąpiła sekwencja liczb <- 6 oraz >1 - po normalizacji 7b

    Kod: Zaznacz cały

    =DŁ($A3) - DŁ(PODSTAW($A3; "7b"; "#"))
    • DŁ($A3) - ile znaków ma ciąg po normalizacji;
    • PODSTAW - każde wystąpienie ciągu "7b" jest zastępowane znakiem "#" - jeden znak zamiast dwóch;
    • różnica długości pierwotnej a tą po podstawieniu daje ilość wystąpień paru liczb 7b.
  4. Ile razy wystąpiła sekwencja liczb: <- 6, =1 oraz =-1, przy czym nie można brać pod uwagę sekwencji występującej na końcu danych - po normalizacji szukamy występowania 7a1
    • Na początku należy pozbyć się ostatniego wystąpienia poszukiwanej frazy, a które będzie ostatnie? oczywiście te, po którym wystąpi 0. Zatem to będzie tak: PODSTAW($A3; "7a10"; "####") - szukam pewnego czteroznakowego tekstu i zastępuję go jakimś innym;
    • w tym nowym tekście szukam teraz wystąpienia trzy znakowego tekstu "7a1" i zastępuję go jakimś innym dwuznakowym tekstem "##": PODSTAW(PODSTAW($A3; "7a10"; "####"); "7a1"; "##")
    • I teraz ilość wystąpień jest obliczana tak jak w poprzednim punkcie.
    • UWAGA: To wyrażenie: PODSTAW(PODSTAW($A3; "7a10"; "####"); "7a1"; "##") można zastąpic REGEXEM:

      Kod: Zaznacz cały

      REGEX($A3; "7a1[^0]"; "###"; "g")
      czyli w danym tekście szukam frazy "7a1" po której występuje jeszcze jeden [^0] dowolny znak INNY NIŻ ZERO. Te 4 znaki są zamieniane trzema "###"
    Podobnie należy począć wyszukując kolejne wartości: 7a2, 7a3, 7a4, 7a5, 7a6
  5. Ile razy wystąpiła sekwencja liczb: <- 6, =1 oraz <-6, po normalizacji poszukiwana fraza to 7a7. Szkopuł w tym, że ostatnia cyfra w tej frazie może być zarazem początkiem nowego. Metodą prób doszedłem do ostatecznej formuły:

    Kod: Zaznacz cały

    =(DŁ($A3) - DŁ(REGEX($A3; "7(a7){1,}"; "#"; "g")))/2

    To wyrażenie: "7(a7){1,}" oznacza, że w danym tekście ma zostać znaleziona "7" po której ma wystąpić przynajmniej raz fraza "a7", nie ma określonego limitu wystąpień dlatego będzie wyszukiwana największe możliwe dopasowanie (a więc może znaleźć zarówno 7a7, jak i 7a7a7a7). Znalezioną frazę zastąpi jednym znakiem "#" czyli z oryginalnego tekstu poznikają wielokrotności 2 znaków. Dlatego obliczaną różnice znaków należy podzielic przez 2.
  6. Ile razy wystąpiła sekwencja liczb: <- 6, =1 oraz >=-6 na końcu danych, po normalizacji poszukiwana fraza to 7a_ (znak _ oznacza tu dowolną cyfrę z przedziału 1...6) oraz po wcześniejszych rozmowach doszedł jeszcze warunek poszukiwana na końcu sekwencji <- 6, =1 czyli 7a.
    I znowu pojawia się pytanie: kiedy dana fraza jest ostatnia? a więc wtedy, gdy wystąpi po niej 0. Zatem:

    Kod: Zaznacz cały

    =DŁ($A3) - DŁ(REGEX($A3; "7a[1-6]0|7a00"; "###"; "g"))
    albo tak, też zadziała:
    =JEŻELI.BŁĄD(SZUKAJ.TEKST("7a[1-6]0|7a00";A3)^0;0)
Jak widać tym razem nawet nie trzeba wiedzieć ile jest liczb, i jaka jest ostatnia, przedostatnia lub przed przedostatnia. Tradycyjnie dołączam plik z alternatywną wersją.
Załączniki
JedenGosc_20211127_9b.ods
(34.56 KiB) Pobrany 77 razy
LibreOffice 7.4.6 (preferowany) oraz OpenOffice 4.1.6. Widows 10
OpenOffice 4.1.3. oraz Libre 4.2.5.2 Windows XP
JedenGość
Posty: 195
Rejestracja: czw mar 07, 2019 6:54 pm

Re: Wyszukiwanie liczby w zależności od liczby wcześniejszej

Post autor: JedenGość »

Jermor pisze: A opis dla kolumny Z powinien mieć opis: W kolumnie Z powinna być wartość ile razy po -6 wystąpiła liczba >1, pomniejszona o 1 jeżeli ostatnią liczbą w ciągu jest liczba mniejsza od -6.
Raczej powinno być: W kolumnie Z powinna być wartość ile razy po <-6 wystąpiła liczba >1.

Jak widać, przy takim rozpisywaniu słowami tego co się ma na myśli mogą występować pomyłki. Nawet najlepszym w temacie - jak widać - zdarzyć się może..

Panowie, a ja już wcześniej chciałem poprzednie rozwiązanie Rafkusa stosować na szerszą skalę w swoich plikach...

Teraz mam kolejne kilka godzin studiowania rozwiązań... Czy to lepiej? Lepiej, że mogę poznać nowe pomysły i spojrzenie na temat. Szczególnie po tak wyczerpujących opisach.

Dzięki Panowie.

Edit.

Jermor, zauważyłem błąd w Twoim rozwiązaniu.

Arkusz1.
P20 zamieniłem na -8, a w Q20 wstawiłem 2.
Arkusz2.
W tej sytuacji powinno być 1 w C21 i 1 w J21.
Jest 2 w C21 i 0 w K21.

Inny przykład.
Arkusz1.
Q12 zamieniłem na -9, a w R12 wstawiłem 3.
Arkusz2.
W tej sytuacji powinno być 1 w C13.
Jest 2 w C13 i -1 (!) w J13.

Rafkus.
Sprawdziłem Twoje ostatnie rozwiązanie w ten sam sposób jak Jermora powyżej. Jest OK.
Zastanawiam się tylko jaka jest różnica w porównaniu z poprzednim. Chyba chodzi tylko o sposób budowania formuł. Jak rozumiem ten może być szybszy, wygodniejszy i mniej wymagający, np. dla pamięci obliczeniowej komputera?
Libre Office 7.5.4.2 / Windows 11
Awatar użytkownika
Rafkus
Posty: 511
Rejestracja: czw kwie 12, 2018 10:26 pm

Re: Wyszukiwanie liczby w zależności od liczby wcześniejszej

Post autor: Rafkus »

JedenGość pisze:Zastanawiam się tylko jaka jest różnica w porównaniu z poprzednim.
Praktycznie sam sobie odpowiedziałeś:
  • sposób budowania formuły - dzięki wykorzystaniu jednej pomocniczej kolumny przechowywującej znormalizowane dane następne formuły stały się znacznie krótsze;
  • szybszy, wygodniejszy - do ewentualnych poprawek. Wystarczy spojrzeć tutaj na te dwa warunki liczące to samo:

    Kod: Zaznacz cały

    =WARUNKI(I(INDEKS($A3:$W3;1;ILE.LICZB($A3:$W3)-2)<-6; INDEKS($A3:$W3;1;ILE.LICZB($A3:$W3)-1)=1; INDEKS($A3:$W3;1;ILE.LICZB($A3:$W3))>=-6);1; I(INDEKS($A3:$W3;1;ILE.LICZB($A3:$W3)-1)<-6; INDEKS($A3:$W3;1;ILE.LICZB($A3:$W3))=1);1; 1;N("zero dla każdego innego wyniku"))
    
    =$B$1-DŁ(REGEX($A3;"7a[1-6]0|7a00";"###";"g"))
    i spróbuj za miesiąc je zmienić tak żeby np. wykluczyć tylko zestaw <-6, =1, =-3 lub <-6, >1
  • czy mniej wymagający, np. dla pamięci obliczeniowej komputera - nie jestem specjalistą więc tego nie mogę potwierdzić. Osobiście nie sądzę. Pierwsza metoda liczy ile komórek spełnia jakiś warunek, druga ile razy występuje jakiś tekst w tekście
LibreOffice 7.4.6 (preferowany) oraz OpenOffice 4.1.6. Widows 10
OpenOffice 4.1.3. oraz Libre 4.2.5.2 Windows XP
JedenGość
Posty: 195
Rejestracja: czw mar 07, 2019 6:54 pm

Re: Wyszukiwanie liczby w zależności od liczby wcześniejszej

Post autor: JedenGość »

Dzięki @Rafkus za wyjaśnienia.

Coś nie działa w Twoim rozwiązaniu.

Gdy wstawiam go do swoich plików pokazują się dziwne dane (obrazek). Zamiast 0000000000 jest bbbbbbbbbb. Konsekwencją są nieprawidłowe wyniki

Nie wiem co jest powodem.

Być może to, że wszystkie pola (nawet te "puste") do których odwołuja się formuły zawierają jakieś dane.

Konkretnie:

Kod: Zaznacz cały

=JEŻELI.BŁĄD(DŁ(REGEX(PRAWY($B3;DŁ($B3)-SUMA(MODUŁ.LICZBY($L3:P3)));LEWY(BIEŻĄCY();1)&"+"))*JEŻELI(P3<0;1;-1);"")
To formuła podpowiedziana przez @Jermor w tym wątku:
https://forum.openoffice.org/pl/forum/v ... f=9&t=5482

Gdy wszystkie liczby dodatnie i ujemne skopiuję i wkleję specjalnie jako liczby (bez formuł) - Twoja formuła i wyniki są ok.

????
Załączniki
wyszukiwanie-liczby-1.jpg
Libre Office 7.5.4.2 / Windows 11
Awatar użytkownika
Rafkus
Posty: 511
Rejestracja: czw kwie 12, 2018 10:26 pm

Re: Wyszukiwanie liczby w zależności od liczby wcześniejszej

Post autor: Rafkus »

Przetestowałem i masz rację:
JedenGość pisze:Być może to, że wszystkie pola (nawet te "puste") do których odwołuja się formuły zawierają jakieś dane.
I, jak przypuszczam, podałeś jaka formuła jest w tych polach: - jeżeli wystąpi jakiś błąd to zostanie wstawiony "" (pusty tekst). Zatem do formuły normalizujące dane musisz dodać jeszcze jeden i to na samym początku, warunek czy w danym polu znajduje się taki pusty tekst:

Kod: Zaznacz cały

=ZŁĄCZ.TEKST(WARUNKI(

		$Arkusz1.$A3:$W3="";	0;

		$Arkusz1.$A3:$Arkusz1.$W3>1;	"b";
		$Arkusz1.$A3:$Arkusz1.$W3=1;	"a";
		$Arkusz1.$A3:$Arkusz1.$W3>-7;	-$Arkusz1.$A3:$Arkusz1.$W3;
		$Arkusz1.$A3:$Arkusz1.$W3<-6;	7);
	"00")
Przypominam, jest to formuła macierzowa.
LibreOffice 7.4.6 (preferowany) oraz OpenOffice 4.1.6. Widows 10
OpenOffice 4.1.3. oraz Libre 4.2.5.2 Windows XP
JedenGość
Posty: 195
Rejestracja: czw mar 07, 2019 6:54 pm

Re: Wyszukiwanie liczby w zależności od liczby wcześniejszej

Post autor: JedenGość »

Super. Teraz działa bardzo dobrze. Rafkus, bardzo dziękuję!
Libre Office 7.5.4.2 / Windows 11
JedenGość
Posty: 195
Rejestracja: czw mar 07, 2019 6:54 pm

Re: Wyszukiwanie liczby w zależności od liczby wcześniejszej

Post autor: JedenGość »

Wracam do tego tematu, ponieważ poszukuję drogi do uzyskania nowych danych na bazie poprzednich rozwiązań.

Szczegóły w pliku.

Ostatnie prawie 3 godziny spędziłem nad rozgryzaniem funkcji REGEX oraz innych zastosowanych w poprzednich rozwiązaniach. Niestety, to jest dla mnie zbyt skomplikowane. Pewnie gdyby mi ktoś, jak w szkole to łopatologicznie wytłumaczył to (może) bym zrozumiał. Na razie jednak jeszcze to nie nastąpiło.

Z góry dziękuję za pomoc.
Załączniki
JedenGosc_20220109.ods
(21.43 KiB) Pobrany 79 razy
Libre Office 7.5.4.2 / Windows 11
Awatar użytkownika
Rafkus
Posty: 511
Rejestracja: czw kwie 12, 2018 10:26 pm

Re: Wyszukiwanie liczby w zależności od liczby wcześniejszej

Post autor: Rafkus »

Stworzyłem pewne rozwiązanie, ale raczej nie spodoba się tobie...

W każdym razie, na początku formułą macierzową określam w której kolumnie w danym wierszu znajduje się liczba mniejsza od -6:

Kod: Zaznacz cały

 MIN.K(JEŻELI($A3:$W3<-6; KOLUMNA($A3:$W3); ""); AK$2)+1
Funkcją JEŻELI wykluczam wszystkie liczby z danego zakresu nie spełniające warunku a funkcja KOLUMNA powie na której pozycji znajduje się liczba spełniająca warunek. Dzięki MIN.K otrzymasz jeden wynik, a który to określisz w komórce AK$2 (1,2,3...). Na końcu formuły jest jeszcze +1 ponieważ tak naprawdę chcesz zwrócić jakąś kolejną wartość po <-6. Przeciągając tą formułę uzyskasz kolejne wartości dla poszczególnych wierszy. Właśnie ta obliczona wartość w połączeniu z funkcją INDEKS($A3:$W3; ; MIN.K(...)+1) będzie zwracała kolejne wyniki.

I teraz jest problem, bo stworzyłem sobie pewien zakres któremu nadałem nazwę "ktory" i zdefiniowanym obszarem była właśnie powyższa formuła. Dzięki temu znacznie uprościłem sobie kolejną formułę:

Kod: Zaznacz cały

=JEŻELI.BŁĄD(
	WARUNKI( 
		INDEKS($A3:$W3; ;ktory)>1;	INDEKS($A3:$W3; ;ktory);
Sprawdź Warunek 1: jeśli odczytana wartość jest większa niż 1, to wypisz tą wartość a jeśli nie to

Kod: Zaznacz cały

		ILE.LICZB($A3:$W3)-ktory<=1;	 "?"; 
Sprawdź Warunek 2: jeśli różnica ilości liczb w danym wierszu i wyznaczona pozycja jest mniejsza lub równa 1, to wypisz "?" (sprawdzenie czy to liczba ostatnia, przedostatnia albo przed przedostatnia), a jeśli nie to

Kod: Zaznacz cały

		INDEKS($A3:$W3; ;ktory)=1;	INDEKS($A3:$W3; ;ktory+1));
Sprawdź Warunek 3: jeśli odczytana wartość jest równa 1, to wypisz kolejną wartość

Kod: Zaznacz cały

	"")
Zakończenie funkcji JEŻELI.BŁĄD: Jeżeli wynik funkcji warunki będzie błędny, to zostanie wyświetlona pusta wartość.

Pisząc tego posta doszedłem do wniosku, że można w powyższej formule zamiast zdefiniowanej nazwy "ktory" wpisać pierwszą formułę i uzyskać:

Kod: Zaznacz cały

=JEŻELI.BŁĄD(
	WARUNKI( 
		INDEKS($A3:$W3; ;MIN.K(JEŻELI($A3:$W3<-6; KOLUMNA($A3:$W3); ""); AK$2)+1)>1;	INDEKS($A3:$W3; ;MIN.K(JEŻELI($A3:$W3<-6; KOLUMNA($A3:$W3); ""); AK$2)+1);
		(ILE.LICZB($A3:$W3)-MIN.K(JEŻELI($A3:$W3<-6; KOLUMNA($A3:$W3); ""); AK$2))<=2;	 "?"; 
		INDEKS($A3:$W3; ;MIN.K(JEŻELI($A3:$W3<-6; KOLUMNA($A3:$W3); ""); AK$2)+1)=1;	INDEKS($A3:$W3; ;MIN.K(JEŻELI($A3:$W3<-6; KOLUMNA($A3:$W3); ""); AK$2)+2));
	"")
Choć tego nie testowałem - też powinno zadziałać.

PS. To są formuły macierzowe.
Załączniki
JedenGosc_20220109_R1.ods
(34.71 KiB) Pobrany 69 razy
LibreOffice 7.4.6 (preferowany) oraz OpenOffice 4.1.6. Widows 10
OpenOffice 4.1.3. oraz Libre 4.2.5.2 Windows XP
JedenGość
Posty: 195
Rejestracja: czw mar 07, 2019 6:54 pm

Re: Wyszukiwanie liczby w zależności od liczby wcześniejszej

Post autor: JedenGość »

I wszystko gra.

Widzisz Rafkus... Ja rozkminiam REGEXy, BIEŻĄCY i inne, a tu nie tędy droga.

Bardzo dziękuję za wyjaśnienia. Dzięki nim (może) to zakumam.

Dziękuję bardzo za pomoc.
Libre Office 7.5.4.2 / Windows 11
ODPOWIEDZ