[solved] Zliczanie ciągów liczb i systematyzowanie danych.

Użytkowanie arkusza kalkulacyjnego
Awatar użytkownika
Rafkus
Posty: 513
Rejestracja: czw kwie 12, 2018 10:26 pm

Re: Zliczanie ciągów liczb i systematyzowanie danych.

Post autor: Rafkus »

@Jermor: widzę że wcześniej odpowiedzieliśmy prawie jednocześnie, mnie to nie przeszkadza i twoja wypowiedź jest obszerniejsza więc jest :super: :super:
JedenGość pisze:Rafkus, próbuję Twoje rzowiązanie z funkcją PRZESUNIĘCIE powiązać np. z WYST.NAJCZĘŚCIEJ

Kod: Zaznacz cały

=WYST.NAJCZĘŚCIEJ(PRZESUNIĘCIE($N2;0;0;;ILE.LICZB($N2:$BA2)-1);"<0")
jednak wyskakuje błąd:504.
Błędem jest tekst "<0", funkcja WYST.NAJCZĘŚCIEJ wymaga tylko liczb ewentualnie zakresu liczb. Jeśli chcesz wyznaczyć najczęściej występujące liczby ujemne stwórz warunkowy zakres danych:

Kod: Zaznacz cały

JEŻELI(Zakres<0;  Zakres)
Korzystając z twojego pliku i danych tam zawartych będzie to chyba coś takiego:

Kod: Zaznacz cały

=WYST.NAJCZĘŚCIEJ(JEŻELI(PRZESUNIĘCIE($G11;0;0; ;ILE.LICZB($G11:$R11)-1)<0;  PRZESUNIĘCIE($G11;0;0; ;ILE.LICZB($G11:$R11)-1)))
Oczywiście jest to funkcja macierzowa: zatwierdzamy ją wciskając jednocześnie klawisze CTRL+SHIFT+ENTER

EDIT:
Właśnie przetestowałem formułę i wychodzi na to, że nie trzeba zatwierdzić ją wciskając jednocześnie klawisze CTRL+SHIFT+ENTER :shock:
Ostatnio zmieniony czw lis 26, 2020 8:22 pm przez Rafkus, łącznie zmieniany 1 raz.
Powód: Małe sprostowanie
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: 513
Rejestracja: czw kwie 12, 2018 10:26 pm

Re: Zliczanie ciągów liczb i systematyzowanie danych.

Post autor: Rafkus »

JedenGość pisze:Jak tu nie mogę sobie z nią poradzić to tak samo mam z funkcjami:
- zwykłe LICZ.WARUNKI (chcę policzyć wszystkie liczby <0 z pominięciem ostatniej liczby).
Weź ten wzór podany przeze mnie na obliczenie Sumy liczb ujemnych (wiem miała być średnia ale dałeś radę go przerobić) i zamień funkcję SUMA.JEŻELI na LICZ.WARUNKI i to wszystko:

Kod: Zaznacz cały

=JEŻELI.BŁĄD(LICZ.WARUNKI(PRZESUNIĘCIE(G2;0;0; ;ILE.LICZB(G2:R2)-1);"<0");0)
JedenGość pisze:- CZĘSTOŚĆ (chcę określić częstość występowania liczb od -1 do -3 z pominięciem ostatniej liczby)
CZĘSTOŚĆ - zwróci tobie macierz danych, w tym przypadku TEORETYCZNIE (jeśli dobrze pamiętam) otrzymałbyś jednym poleceniem trzy informacje jednocześnie: ile jest liczb mniejszych od -3, ile jest liczb z przedziału -3...-1, ile jest liczb większych od -1.
Zamiast tego wykorzystaj znowu funkcję LICZ.WARUNKI :

Kod: Zaznacz cały

=JEŻELI.BŁĄD(LICZ.WARUNKI(PRZESUNIĘCIE(G2;0;0; ;ILE.LICZB(G2:R2)-1);  "<=-1";  PRZESUNIĘCIE(G2;0;0; ;ILE.LICZB(G2:R2)-1);  ">=-3");  0)
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: Zliczanie ciągów liczb i systematyzowanie danych.

Post autor: JedenGość »

Jermor, Rafkus - koljny raz bardzo dziękuję.
Jermor, Tobie w szczególności za obszerne wyjaśnienie PRZESUNIĘCIA. Teraz jest już to dla mnie jasne. Powinieneś pisać Helpy do Calca lub Excela. Ew. Adres Twojego bloga powinien być linkowany na stronach pomocy w/w programów.

Rafku Tobie dziękuję za świeże spojrzenie i uproszczenie niektórych formuł.

Piszę dopiero dzisiaj bo wczoraj implementowałem Wasze formuły w swoich plikach. Oczywiście część z nich zmieniałem, aby były kompatybilne. Nie zawsze było prosto i musiałem kombinować.

Teraz jest już jednak finał. Wszystko gra. Dzięki Panowie!
Libre Office 7.5.4.2 / Windows 11
JedenGość
Posty: 195
Rejestracja: czw mar 07, 2019 6:54 pm

Re: [solved] Zliczanie ciągów liczb i systematyzowanie danyc

Post autor: JedenGość »

Wracam na chwilę do tego tematu, ponieważ nie mogę rozgryźć jak stworzyć jedną formułę.

Szczegółowe dane w załaczniku.

ŚREDNIA.JEŻELI uśrednia liczby ujemne z pominięciem ostatniej wykorzystując funkcje PRZESUNIĘCIE i ILE.LICZB.

Potrzebuję obliczyć odchylenie standardowe przy użyciu funkcji PIERWIASTEK i WARIANCJA.

Udało mi się to formułą z B2. Jest ona jednak nie do końca poprawna:
1) Bardzo rozbudowana.
2) Pod uwagę bierze wszystkie liczby z zakresu, a powinna pomijać ostatnią.

W komórce B4 jest podane prawidłowe odchylenie standardowe dla wymaganego zakresu.

Próbuję również tutaj zastosować PRZESUNIĘCIE i ILE.LICZB, ale nie wychodzi mi to.
Załączniki
10 JedenGość_SystematyzowanieDanychA.ods
(13.44 KiB) Pobrany 177 razy
Libre Office 7.5.4.2 / Windows 11
Awatar użytkownika
Rafkus
Posty: 513
Rejestracja: czw kwie 12, 2018 10:26 pm

Re: Zliczanie ciągów liczb i systematyzowanie danych.

Post autor: Rafkus »

Faktycznie masz zagmatwaną formułę, ja wykorzystałbym funkcję macierzową:

Kod: Zaznacz cały

=PIERWIASTEK( WARIANCJA(JEŻELI($C$2:$AP$2<0;   $C$2:$AP$2;   "")))
zatwierdzaną przez jednoczesne wciśnięcie klawiszy CTRL+SHIFT+ENTER.
Funkcją JEZELI wykluczam wszystkie wartości dodatnie (zamiast nich jest wstawiana pusta wartość ""). Na tak określonym zakresie są dokonywane dalsze obliczenia.

Po sprawdzeniu mój wynik jest inny niż napisany przez ciebie - nie pominąłem OSTATNIEJ liczby, zatem PRZESUNIĘCIEM koryguję zakres danych:

Kod: Zaznacz cały

=PIERWIASTEK(WARIANCJA(JEŻELI(PRZESUNIĘCIE($C2;0;0;;ILE.LICZB($C2:$AP2)-1) < 0;   PRZESUNIĘCIE($C2;0;0;;ILE.LICZB($C2:$AP2)-1);   "")))
i tą formułę również zatwierdzam wciskając klawisze CTRL+SHIFT+ENTER.
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
Jermor
Posty: 2239
Rejestracja: sob paź 12, 2013 11:09 am
Kontakt:

Re: Zliczanie ciągów liczb i systematyzowanie danych.

Post autor: Jermor »

Dla obliczenia odchylenia standardowego jest funkcja ODCH.STANDARDOWE()
W twoim przypadku powinna wyglądać tak (dla wiersza 2.):

Kod: Zaznacz cały

=ODCH.STANDARDOWE(JEŻELI(PRZESUNIĘCIE(C2:AP2;0;0;1;ILE.LICZB(C2:AP2)-1)<0;PRZESUNIĘCIE(C2:AP2;0;0;1;ILE.LICZB(C2:AP2)-1);""))
Zatwierdzana oczywiście SHIFT+CTRL+ENTER
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: Zliczanie ciągów liczb i systematyzowanie danych.

Post autor: JedenGość »

No i gra :-)

Chyba nawet miałem taką formułę wypróbowaną, ale bez zatwierdzenia tablicowego i wychodził błąd.

Gubię się w tych formułach macierzowych. Muszę się z nimi bliżej zapoznać. Tak czy inaczej dzięki!
Libre Office 7.5.4.2 / Windows 11
Awatar użytkownika
Jermor
Posty: 2239
Rejestracja: sob paź 12, 2013 11:09 am
Kontakt:

Re: [solved] Zliczanie ciągów liczb i systematyzowanie danyc

Post autor: Jermor »

Formuła macierzowa albo tablicowa (bo czasami tak też jest nazywana) to formuła wykonująca działania dla każdej komórki zakresu, jaki został w niej wskazany.
Wpisz dla przykładu do komórek od A1 do D1 jakieś liczby. Gdybyś chciał w swojej pracy umieścić te liczby w takiej samej kolejności komórek, ale w innym miejscu arkusza, użyłbyś czterech formuł wpisując kolejno =A1, =B1, =C1 i =D1. Formuła macierzowa pozwala na wskazanie od razu wszystkich komórek źródła. Zatem w miejscu docelowym możesz wpisać =A1:D1. Calc oczywiście nie zrozumie takiego zapisu formuły. Dlatego musisz zatwierdzić ją nie zwykłym ENTER, ale skrótem klawiaturowym SHIFT+CTRL+ENTER. Spowoduje to, że Calc umieści cztery sąsiadujące wartości obok siebie, zaczynając od miejsca, w którym formuła została wpisana. Gdy spojrzysz na formułę, która wpisana jest w każdej z tych docelowych komórek, zobaczysz zawsze ten sam zapis ujęty w nawiasy klamrowe {=A1:D1}. Zauważ teraz, że nie możesz nadpisać żadnej z tych wartości. Formuła macierzowa może bowiem zostać zmodyfikowana tylko wówczas, gdy zaznaczone zostaną wszystkie komórki, w których ona występuje i zmiana dotyczy treści formuły, a nie pojedynczej komórki. Jeśli znajdujesz się w komórce zawierającej formułę macierzową, to aby zaznaczyć wszystkie komórki, które jej dotyczą, naciśnij CTRL+/ (znak / na klawiaturze numerycznej!).
Załóżmy teraz, że w komórkach od A2 do D2 znajdują się kolejne liczby. A zadaniem jest policzenie sumy ilorazów odpowiadających sobie liczb. W tradycyjnej formie utworzyłbyś zapewne trzeci, pomocniczy wiersz, zawierający formulę =A1/A2, powieloną do kolejnych komórek, a następnie funkcja SUMA() obliczyłaby ich sumę.
Korzystając z formuły macierzowej, takie ilorazy możesz otrzymać wpisując formułę =A1:D1/A2:D2. Wynikiem takiego działania będzie wektor czterech ilorazów zapisany w kolejnych komórkach wynikowych. Nic nie stoi na przeszkodzie temu, aby taki wektor był argumentem funkcji SUMA().
Możesz więc napisać taką formułę =SUMA(A1:D1/A2:D2). SHIFT+CTRL+ENTER sygnalizuje, że zakresy komórek mają zostać policzone kolejno wg zadanego algorytmu.
Więcej na ten temat jest m.in. tutaj https://yestok.pl/gen/y44.php
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: [solved] Zliczanie ciągów liczb i systematyzowanie danyc

Post autor: JedenGość »

Jermor, teraz po Twoich wyjasnieniach zacząłem to co nieco kumać. Dzięki wielkie.
Libre Office 7.5.4.2 / Windows 11
JedenGość
Posty: 195
Rejestracja: czw mar 07, 2019 6:54 pm

Re: Zliczanie ciągów liczb i systematyzowanie danych.

Post autor: JedenGość »

Wracam do tego tematu, ponieważ potrzebuję modyfikacji rozwiązania podanego tu przez Jermora.

Prawdopodobnie chodzi o modyfikacje tych formuł:

Kod: Zaznacz cały

=POŁĄCZ.TEKSTY("";1;JEŻELI($A2=nazwy ;yn ))
=DŁ(REGEX($B2;LEWY($B2;1)&"+"))*JEŻELI(LEWY($B2;1)="0";-1;1)
=JEŻELI.BŁĄD(DŁ(REGEX(PRAWY($B2;DŁ($B2)-SUMA(MODUŁ.LICZBY($G2:G2)));LEWY(BIEŻĄCY();1)&"+"))*JEŻELI(G2<0;1;-1);"")
=JEŻELI.BŁĄD(DŁ(REGEX(PRAWY($B2;DŁ($B2)-SUMA(MODUŁ.LICZBY($G2:H2)));LEWY(BIEŻĄCY();1)&"+"))*JEŻELI(H2<0;1;-1);"")
O ile rozwiązanie podane w tym temacie potrafiłem wielokrotnie przerabiać i dostosowywać do swoich potrzeb, to jednak teraz przerasta to moje możliwości.

Wszystko opisałem w załączonym pliku.

Z góry dziękuję za pomoc.
Załączniki
11 JedenGość_SystematyzowanieDanychA.ods
(19.33 KiB) Pobrany 148 razy
Libre Office 7.5.4.2 / Windows 11
Awatar użytkownika
Rafkus
Posty: 513
Rejestracja: czw kwie 12, 2018 10:26 pm

Re: Zliczanie ciągów liczb i systematyzowanie danych.

Post autor: Rafkus »

Wydaje mi się, że wystarczy zastosować tą formułę:

Kod: Zaznacz cały

=POŁĄCZ.TEKSTY("";  1;  JEŻELI($B$2:$B$240=G2;  $D$2:$D$240;  JEŻELI($C$2:$C$240=G2;  $E$2:$E$240;  "")))
Formułę tą wpisałem do komórki F2 i przeciągnąłem w dół otrzymując zero-jedynkowe teksty. Dwie następne podane tam formuły, po niewielkiej modyfikacji:

Kod: Zaznacz cały

Na pierwszą wartość do zestawionych danych:
=DŁ(REGEX($F2;LEWY($F2;1)&"+"))*JEŻELI(LEWY($F2;1)="0";-1;1)
I następne wartości:
=JEŻELI.BŁĄD(DŁ(REGEX(PRAWY($F2;DŁ($F2)-SUMA(MODUŁ.LICZBY($H2:H2)));LEWY(BIEŻĄCY();1)&"+"))*JEŻELI(H2<0;1;-1);"")
dawały jakby poprawne zestawy danych. (UWAGA: Trzecia formuła jest formułą macierzową.)
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: Zliczanie ciągów liczb i systematyzowanie danych.

Post autor: JedenGość »

Rafkus, co tu dużo pisać... Trafiłeś w sedno. Wszystko jest tak, jak powinno być. Dzięki bardzo. :bravo: :super:

P.S. Wszystkie trzy formuły muszę zatwierdzić macierzowo, inaczej nie działają...
Libre Office 7.5.4.2 / Windows 11
JedenGość
Posty: 195
Rejestracja: czw mar 07, 2019 6:54 pm

Re: [solved] Zliczanie ciągów liczb i systematyzowanie danyc

Post autor: JedenGość »

Pozwalam sobie ponownie otworzyć ten temat, ponieważ potrzebuję pewnej modyfikacji zaproponowanego tu rozwiązania.

Ów rozwiązanie opiera się głównie na formułach:

Kod: Zaznacz cały

=POŁĄCZ.TEKSTY("";1;JEŻELI($C$2:$C$501 ;$I$2:$I$501))

Kod: Zaznacz cały

=DŁ(REGEX($B2;LEWY($B2;1)&"+"))*JEŻELI(LEWY($B2;1)="0";-1;1)

Kod: Zaznacz cały

=JEŻELI.BŁĄD(DŁ(REGEX(PRAWY($B2;DŁ($B2)-SUMA(MODUŁ.LICZBY($L2:L2)));LEWY(BIEŻĄCY();1)&"+"))*JEŻELI(L2<0;1;-1);"")
Bazą do budowania takiej tabeli było np:

A 6 JEŻELI(A=6;1;0).

Teraz mam problem z odpowiednim zbudowaniem bazy do powyższych formuł. Szczegóły w załączniku.

Panowie - z góry bardzo dziękuję!
Załączniki
JedenGosc_20211230.ods
(27.35 KiB) Pobrany 80 razy
Libre Office 7.5.4.2 / Windows 11
Awatar użytkownika
Rafkus
Posty: 513
Rejestracja: czw kwie 12, 2018 10:26 pm

Re: Zliczanie ciągów liczb i systematyzowanie danych.

Post autor: Rafkus »

Normalizacja danych odbywa się tu na nieco innych zasadach dlatego proponuję zastosować formułę macierzową:

Kod: Zaznacz cały

=POŁĄCZ.TEKSTY(""; 1; 
WARUNKI($B$2:$B$300=$F2;	$D$2:$D$300=3;
		$C$2:$C$300=$F2;	$D$2:$D$300=6;
		1;	""))
pozostałe formuły liczące ile razy wystąpiło 0 i 1 nie trzeba za bardzo zmieniać.

PS. Założyłem że nie wystąpi sytuacja gdy w TWOICH kolumnach a i b w tym samym wierszu nie wystąpi jednocześnie ta sama litera.
Załączniki
JedenGosc_20211230b.ods
(42.65 KiB) Pobrany 90 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: Zliczanie ciągów liczb i systematyzowanie danych.

Post autor: JedenGość »

Rafkus. Krótko, zwięźle i na temat. Przetestowałem. Wszystko działa super. Bardzo dziękuję!

P.S. Myślałem nad tym kilka dni... Widzę, że te formuły macierzowe to najfajniejsza (i najtrudniejsza) rzecz w tej zabawie. Muszę się do nich zabrać bardziej szczegółowo i systematycznie.
Libre Office 7.5.4.2 / Windows 11
ODPOWIEDZ