Średnia z warunkiem [SOLVED]
Średnia z warunkiem [SOLVED]
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.
Pozdrawiam
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.
Pozdrawiam
Ostatnio zmieniony śr paź 20, 2021 1:46 pm przez Mental, łącznie zmieniany 1 raz.
Version: 7.5.6.2 (x64)
Re: Średnia z warunkiem
Zajrzyj na stronę https://yestok.pl/lbo/y63.php
Znajdziesz opis różnych funkcji warunkowych.
Użyjesz prawdopodobnie funkcji: ŚREDNIA.JEŻELI()
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.
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.
Re: Średnia z warunkiem
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).
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)
Re: Średnia z warunkiem
"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).
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.
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.
Re: Średnia z warunkiem [SOLVED]
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:Warunek mówi, że zawartość komórki w kolumnie A ma być różna od myślnika.
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)
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.
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.
Re: Średnia z warunkiem [SOLVED]
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)
Re: Średnia z warunkiem [SOLVED]
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.
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.
Re: Średnia z warunkiem [SOLVED]
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:
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
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)
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.
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.
Re: Średnia z warunkiem [SOLVED]
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:
to program też zwróci pewną wartość, ale jak została ona policzona, to tego już nie wiem.
Pozdrawiam
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)
Pozdrawiam
Version: 7.5.6.2 (x64)
Re: Średnia z warunkiem [SOLVED]
Ł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) 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.
* 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) 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)
LO (7.6|24.2) ∙ Python (3.12|3.10) ∙ Unicode 15 ∙ LᴬTEX 2ε ∙ XML ∙ Unix tools ∙ Linux (Rocky|CentOS)
Re: Średnia z warunkiem [SOLVED]
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:
a w tym komórki B9 i B10:
Np. w poniższym przypadku do obliczenia średniej wzięte zostaną komórki B3 i B5:
a w tym komórki B9 i B10:
Version: 7.5.6.2 (x64)
Re: Średnia z warunkiem [SOLVED]
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:
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.
Kod: Zaznacz cały
=ŚREDNIA.JEŻELI(A$4:A$6;">0";B$5)
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.
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.