Średnia z warunkiem [SOLVED]

Użytkowanie arkusza kalkulacyjnego
Mental
Posty: 21
Rejestracja: sob cze 06, 2020 8:45 pm

Średnia z warunkiem [SOLVED]

Post autor: Mental »

Dzień dobry,

Proszę o pomoc w następującym zagadnieniu: jak obliczyć średnią z wartości w kolumnie B, ale z warunkiem, że próbka do średniej jest brana tylko wtedy gdy komórka po lewej stronie jest liczbą (albo odrzucana gdy po lewej znajduje się myślnik/tekst; zapis dowolny, liczy się prawidłowe działanie na koniec).
W przykładzie poniżej ze średniej odrzucone byłyby wartości w niebieskich komórkach.
Obrazek

Pozdrawiam
Ostatnio zmieniony śr paź 20, 2021 1:46 pm przez Mental, łącznie zmieniany 1 raz.
Version: 7.5.6.2 (x64)
Awatar użytkownika
Jermor
Posty: 2239
Rejestracja: sob paź 12, 2013 11:09 am
Kontakt:

Re: Średnia z warunkiem

Post autor: Jermor »

Zajrzyj na stronę https://yestok.pl/lbo/y63.php
Znajdziesz opis różnych funkcji warunkowych.
Użyjesz prawdopodobnie funkcji: ŚREDNIA.JEŻELI()
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.
Mental
Posty: 21
Rejestracja: sob cze 06, 2020 8:45 pm

Re: Średnia z warunkiem

Post autor: Mental »

Dziękuję, przy okazji poznałem trochę nowych funkcji.

Idąc po linii najmniejszego oporu zastosowałem formułę ŚREDNIA.JEŻELI(A$2:A$11;">0";B$2:B$11), która zdaje się spełniać swoje zadanie, choć nie do końca wiem dlaczego (podejrzewam, że ma to związek ze znaczkiem $, którego funkcji nie znam).
Ostatnio zmieniony śr paź 20, 2021 3:13 pm przez Mental, łącznie zmieniany 1 raz.
Version: 7.5.6.2 (x64)
Awatar użytkownika
Jermor
Posty: 2239
Rejestracja: sob paź 12, 2013 11:09 am
Kontakt:

Re: Średnia z warunkiem

Post autor: Jermor »

"Znaczek $" nie ma tutaj żadnego znaczenia.
Ten symbol decyduje o sposobie traktowania adresów komórek podczas kopiowania zawartych w nich formuł. Adresowanie komórek odbywa się bowiem w sposób względny, mieszany albo bezwzględny. Poprzedzenie elementu adresowego symbolem $ oznacza, że ten element adresu ma być niezmienny podczas kopiowania zawartości komórki, czyli bezwzględny.
Jeżeli w komórce B1 wpiszesz formułę =A1, a następnie skopiujesz komórkę B1 np. do F5, to w tej docelowej komórce zobaczysz formułę =E5. Adres w formule jest względny i odniesiony do oryginału. Czyli, tak jak w oryginale odnosi się także do pierwszej komórki po lewej stronie w tym samym wierszu.
Jeżeli w komórce B1 zapiszesz formułę =$A1, to analogiczna kopia będzie zawierała formułę =$A5, czyli formuła nadal będzie się odwoływała do kolumny A (ten element ma być niezmienny), lecz wiersz będzie odpowiadał temu samemu wierszowi, w którym zapisana jest formuła.
Kolejna zmiana formuły w B1 na =A$1 sprawi, że kopia w komórce F5 będzie zawierała formułę =E$1. W tym wypadku wiersz ma być niezmienny, natomiast kolumna jest pierwsza po lewej stronie.
No i wersja formuły =$A$1 oznacza, że po skopiowaniu w nowym miejscu pojawi się formuła =$A$1, czyli niezmieniony wiersz i niezmieniona kolumna (adres bezwzględny).
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.
Mental
Posty: 21
Rejestracja: sob cze 06, 2020 8:45 pm

Re: Średnia z warunkiem

Post autor: Mental »

Dziękuję za odpowiedź.
Version: 7.5.6.2 (x64)
Awatar użytkownika
Jermor
Posty: 2239
Rejestracja: sob paź 12, 2013 11:09 am
Kontakt:

Re: Średnia z warunkiem [SOLVED]

Post autor: Jermor »

Jeszcze jedna uwaga, dotycząca już konkretnie twojego przypadku. ŚREDNIA.JEŻELI(A$2:A$11;">0";B$2:B$11) wyznaczy średnią dla liczb większych od zera.
To znaczy, że jeśli wynik próbki może być zerowy albo ujemny, to nie zostanie wzięty pod uwagę podczas liczenia średniej.
Zatem lepszym rozwiązaniem byłoby zapisanie:

Kod: Zaznacz cały

=ŚREDNIA.JEŻELI(A$2:A$11;"<>-";B$2:B$11)
Warunek mówi, że zawartość komórki w kolumnie A ma być różna od myślnika.
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.
Mental
Posty: 21
Rejestracja: sob cze 06, 2020 8:45 pm

Re: Średnia z warunkiem [SOLVED]

Post autor: Mental »

Zdarza się, że zamiast myślnika wpisuję inny tekst. Lepiej pasowałaby formuła sprawdzająca czy pole w kolumnie A jest liczbą.
Version: 7.5.6.2 (x64)
Awatar użytkownika
Jermor
Posty: 2239
Rejestracja: sob paź 12, 2013 11:09 am
Kontakt:

Re: Średnia z warunkiem [SOLVED]

Post autor: Jermor »

Przepraszam, to ja coś poplątałem. Warunek sprawdza przecież numer próbki (nie jej wartość), a ten będzie jakąś liczbą większą od zera. Zatem twoja formuła jest OK.
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
Jermor
Posty: 2239
Rejestracja: sob paź 12, 2013 11:09 am
Kontakt:

Re: Średnia z warunkiem [SOLVED]

Post autor: Jermor »

Co do wpisywania innego tekstu. Jeśli chcesz, aby obliczenia dotyczyły tylko tych komórek, które w komórkach towarzyszących mają tylko liczbę, to stosując użytą przez ciebie funkcję ŚREDNIA.JEŻELI() zdefiniuj kryterium jako wyrażenie regularne (te funkcje mogą z nich korzystać).
Aby z takich wyrażeń można było korzystać, musisz mieć ustawioną stosowną opcję: "Narzędzia -> Opcje -> LibreOffice Calc -> Oblicz -> Włącz wyrażenia regularne w formułach".
Samo wyrażenie zależy od tego, jakiej postaci liczby się spodziewasz. Zakładając, że może to być dowolna liczba, np. -12,456 albo 0,22222, takim wyrażeniem może być: "^-?[0-9]+,?[0-9]*$"
Czyli formuła mogłaby wyglądać następująco:

Kod: Zaznacz cały

=ŚREDNIA.JEŻELI(A2:A11;"^-?[0-9]+,?[0-9]*$";B2:B11)
Jak zinterpretować to wyrażenie regularne?
Symbol "^" oznacza, że analizowanie zawartości rozpocznie się od pierwszego znaku komórki.
Zapis "-?" mówi, że pierwszym znakiem może być minus.
Zapis "[0-9]+" oznacza, że musi wystąpić przynajmniej jedna cyfra z zakresu cyfr od 0 do 9.
Zapis ",?" oznacza, że po ciągu cyfr może pojawić się przecinek.
Zapis "[0-9]*" oznacza, że po przecinku mogą, ale nie muszą wystąpić dalsze cyfry od 0 do 9
Znak "$" oznacza, że tym ma się zakończyć cały zapis w komórce.
Jeśli taką liczbą może być tylko jakaś dodatnia liczba całkowita, to takim wyrażeniem może być: "^[0-9]+$"
O wyrażeniach regularnych poczytasz m.in. tu: https://yestok.pl/ooo/y16.php#p8b
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.
Mental
Posty: 21
Rejestracja: sob cze 06, 2020 8:45 pm

Re: Średnia z warunkiem [SOLVED]

Post autor: Mental »

I już człowiek mądrzejszy. :)

Akurat na potrzeby tego projektu wystarczy pierwotna formuła, nie ma ryzyka wystąpienia błędu. Ale jeśli kiedyś praca się rozbuduje to chętnie skorzystam zarówno z powyższych rad jak i reszty Pańskiego bloga.

Moja pierwotna konfuzja odnośnie działania formuły wynikała z faktu, że program jakby z automatu np. dla komórki B3 przyjmował warunek z komórki A3, a więc z tego samego wiersza.
Ale gdyby formułę zapisać np. tak:

Kod: Zaznacz cały

ŚREDNIA.JEŻELI(A$4:A$6;">0";B$5:B$11)
to program też zwróci pewną wartość, ale jak została ona policzona, to tego już nie wiem.

Pozdrawiam
Version: 7.5.6.2 (x64)
Jan_J
Posty: 4558
Rejestracja: pt maja 22, 2009 1:20 pm
Lokalizacja: Wrocław

Re: Średnia z warunkiem [SOLVED]

Post autor: Jan_J »

Łatwy do zaaranżowania test wskazuje, że
* w przypadku, gdy obszar opisujący kryteria jest krótszy niż obszar liczb (sumowanych albo uśrednianych), nadmiarowe liczby nie są brane pod uwagę. Niby OK;
ale
* w przypadku, gdy obszar opisujący kryteria jest dłuższy niż obszar liczb, faktycznie przetwarzany region liczb jest szerszy niż deklarowany. To jest niebezpieczne.
(sprawdzałem w LO 7.1.6.2 rpm 64bit)
test_sumif.ods
(6.98 KiB) Pobrany 84 razy
Z tym, że test jest sprawdzaniem a posteriori, po skutkach. W informatyce ważniejsze jest, by dana operacja była wyspecyfikowana, tj. by jej zachowanie było w pełni przewidywalne.
W tym przypadku można by się wykpić stwierdzeniem, że oba wektory mają mieć jednakową długość. Jednak help nie wspomina o takim ograniczeniu. Obawiam się, że rozproszony i nie do końca ukierunkowany rozwój pakietu sprawia, że ani dokumentacja nie jest kompletna, ani zachowanie funkcji w warunkach nie odpowiadających zamysłom jej użycia nie jest do końca wynikiem projektu.
Wolałbym się mylić w tej sprawie.
JJ
LO (7.6|24.2) ∙ Python (3.12|3.10) ∙ Unicode 15 ∙ LᴬTEX 2ε ∙ XML ∙ Unix tools ∙ Linux (Rocky|CentOS)
Mental
Posty: 21
Rejestracja: sob cze 06, 2020 8:45 pm

Re: Średnia z warunkiem [SOLVED]

Post autor: Mental »

Popróbowałem jeszcze inne kombinacje i z otrzymanych wyników można wywnioskować, że po prostu kolejna komórka kryterium odpowiada kolejnej komórce próbki.

Np. w poniższym przypadku do obliczenia średniej wzięte zostaną komórki B3 i B5:
Obrazek

a w tym komórki B9 i B10:
Obrazek
Version: 7.5.6.2 (x64)
Awatar użytkownika
Jermor
Posty: 2239
Rejestracja: sob paź 12, 2013 11:09 am
Kontakt:

Re: Średnia z warunkiem [SOLVED]

Post autor: Jermor »

Formuła powinna zawierać taką samą ilość komórek w obszarze kryteriów i obszarze obliczeń. Obszary te niemuszą do siebie przylegać, mogą być nawet w różnych arkuszach. To liczba komórek w zakresie kryteriów decyduje o tym, ile komórek będzie analizowanych pod względem wykorzystania ich w funkcji. Dlatego, choć jak sądzę nie wszyscy o tym wiedzą, formuła zapisana tak:

Kod: Zaznacz cały

=ŚREDNIA.JEŻELI(A$4:A$6;">0";B$5)
nie zasygnalizuje błędu i zostanie wyliczona z uwzględnieniem tylu kolejnych komórek, poczynając od B5, ile ich jest w obszarze kryteriów i w takim kierunku, jaki wynika z ułożenia obszaru kryteriów.
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.
ODPOWIEDZ