Witam. Mam dane historyczne wyników piłki nożnej w excelu.
W LibreOffice tworzę arkusz obliczeniowy w oparciu o dane historyczne.
Ma on liczyć min. średnią goli z wybranego zakresu dat, wybieranego z 2 list (od, do) rozwijanych dla wybranej drużyny.
Drużynę też wybieram z rozwijanej listy.
Funkcji liczących będzie kilka ale skupię się na tej najtrudniejszej dla mnie części.
Mianowicie:
„Średnia goli zdobytych przez drużynę A u siebie w oparciu o wybrany z list zakres dat”
oraz
„Średnia goli zdobytych przez przeciwników drużyny A w oparciu o wyżej wybrany z list zakres dat”
Mam następujące dane w komórkach:
Kolumna D - Daty wszystkich spotkań
Kolumna F - Mecze drużyny A
Kolumna G - Mecze drużyny B
Kolumna I - Gole drużyny A
Kolumna J - Gole druzyny B
Komórka F570 Wybieranie drużyny A jako gospodarza z listy
Komórka F571 Wybieranie daty „od”
Komórka G571 Wybieranie daty „do”
A po prawo analogicznie dla drugiego zespołu.
Do list rozwijanych mam stworzone osobne kolumny z danymi gdzie nie powtarzają się ani daty ani zespoły.
Oczywiście dane odpowiednio sformatowane dla danej kategorii.
Tak więc listy działają, została kwestia najtrudniejsza czyli w.w. funkcje.
Ktoś wie jak powinny one wyglądać, jak je stworzyć?
Ma to docelowo wyglądać i działać podobnie jak na załączonym obrazku.
Tyle, że będę sobie wybierał zakres dat w miejscu wiersza o rozegranych meczach a funkcje będą dynamicznie to obliczały.
Jeśli się natomiast nie da tego zrobić w takim arkuszu albo gdzie indziej będzie prościej, to
poproszę o sugestię gdzie. Najlepiej w oprogramowaniu darmowym.
Trzeba by było się zastanowić, a jakby nie było chętnego do myślenia, to możesz wkleić to całe pytanie do Copilot (microsoft.com) i potem ewentualnie dopytywać go krok po kroku jak co zrobić.
Ewentualnie do Claude, wielu programistów twierdzi, że Claude jest najlepszy w takich sprawach.
Straciłem sporo czasu i przeszedłem wiele sesji na czat.ai z „informatykiem”. Wklejał błędne funkcje, powtarzał się cały czas i generalnie lipa totalna. Dlatego tu napisałem. Wkleił funkcję, która brała do obliczeń datę i albo wychodziło #DZIEL/0! albo jakaś wartość z sufitu, bo cyfry z dat były brane pod uwagę. W kolejnych sesjach nie potrafił tego poprawić, testowałem innymi funkcjami w kolumnach pomocniczych bo szukał problemu nie tam gdzie trzeba, a kolejne wersje funkcji zwracały albo błąd albo zero, albo błędnie liczyły (z uwzględnieniem cyfr z daty). Mogę poszukać mądrzejszego AI ale może tutaj ktoś coś poradzi.
Generalnie żeby pracować z AI, musiałbyś pytanie rozbić na mniejsze fragmenty, mieć pomysł jak to zrobić i umieć ten pomysł opisać słownictwem zbliżonym do tego używanego podczas pracy z arkuszami, jedynie dopytywać o szczegóły. W przeciwnym wypadku programiści byliby niepotrzebni.
AI sprawdza się najlepiej w roli pomocnika konsolidującego materiał z wielu źródeł a nie inżyniera, który zaprojektuje coś od zera. Dla mnie odpowiedź AI to sugestia rozwiązania, często bardzo trafna mimo że są w niej błędy, które trzeba poprawić. Ogólnego pytania często człowiek też nie rozumie, bez dodatkowych wyjaśnień. AI to coś w rodzaju kolegi zza biurka, który żuci pomysłami, mówiąc „sprawdź sobie to i to”.
Przypuszczam, że AI zasugerował Ci użycie właściwych funkcji, choć pewnie popełnił błędy w składni.
Wracając do tematu, gdybyś wstawił arkusz z wypełnionymi przykładowymi danymi, to by było łatwiej.
Użyj funkcji ŚREDNIA.WARUNKÓW
Zakres_funkcji - tu dajesz kolumnę z czego ma liczyć średnią, czyli gole strzelone
Zakres1 - kolumna z datami,
Kryterium1- większe lub równe dacie od
Zakres2- kolumna z datami,
Kryterium2- mniejsze lub równe dacie do
Zakres3- kolumna z nazwami drużyn
Kryterium3- nazwa drużyny (gospodaraża)
Czyli generalnie filtrujesz sobie przedział po datach (ograniczone datą_od do daty_do) a potem jeszcze dorzucasz, żeby liczyło tylko dla konkretnej drużyny
A potem dla goli strzelonych gospodarzą robisz tak samo tylko liczyć średnią z innej kolumny
Zakres_funkcji – wymagany argument. Jest to zakres komórek, nazwa nazwanego zakresu lub etykieta kolumny lub wiersz zawierający wartości do obliczenia średniej.
Zakres1 – wymagany argument. Jest to zakres komórek, nazwa nazwanego zakresu lub etykieta kolumny lub wiersza, do którego ma zostać zastosowane odpowiednie kryterium.
np ta funckja poniżej liczy średnią z zakresu C2 do C6 ale tylko wtedy jeśli komórka w danym wierszu B2 do B6 ma wartość większą lub równą 20 i jednocześnie wartość komórki w C2 do C6 będzie większa od 70. Daty w Libre/Office to są liczby i tez je możesz tak porównywać.
=ŚREDNIA.WARUNKÓW(C2:C6;B2:B6;„>=20”;C2:C6;„>70”)
To ma być funkcja tablicowa czy zwykła?
Odwołanie do dat to ma być do wszystkich dat czy tych unikalnych?
Bo daty się powtarzają w głównej tabeli. Podobnie jak drużyny.
Zakres funkcji: To ma być kolumna z wszystkimi golami dla gospodarzy wszystkich meczy? Mnie interesuje średnia goli wybranej drużyny ale może potem funkcja wybierze odpowiednie komórki?
P.S. Raczej po co wam AI jak są ludzie na forach. Tylko, że ludzie nie zawsze odpowiedzą.
edit: Coś takiego stworzyłem wg. twojej instrukcji i jest błąd 507
=ŚREDNIA.WARUNKÓW (H3:H561; A566:A752; => F571 [;A566:A752 <= G571[;B567:B594]])
Gdzie jest błąd?
Poniżej mój arkusz. Na google drive nazewnictwo jest inne niż na libreoffice bo to inna aplikacja. Arkusz
zobacz czy ci w twoim działa (zrobiłem w excelu) - musiałem spakować 7zip bo forum nie obsługuje xslx jako załącznika.
Generalnie problem był też w dacie, z „r” w komórce excel nie traktował tego jako datę tylko jako tekst
A wtedy nie da się porównać tekstu czy jest większy/mniejszy. Dlatego z boku listy zrobiłem na szybko konwersje do formatu daty (można to w jednej kolumnie zrobić ale dla przykładu lepiej widać rozbite).
Druga sprawa, to zakresy muszą być takie same,
jak tutaj widać wszystkie cztery zakresy od H3 do H561 (w zasadzie nie trzeba było dawać $ żeby zalblokować).
Ok liczy średnią (sprawdzę jeszcze czy poprawnie) ale usunąłeś listę rozwijaną z datami. Mogłoby tak zostać jeśli musi tak być, choć wolałbym listy… A do czego tak z ciekawości była potrzebna dodatkowa kolumna z dniami miesiąca?
Formatowanie dat u mnie było jako data a czemu to było traktowane jako tekst to nie wiem.
Wcześniej testowałem inne funkcje od AI i nie było r w dacie a zakresy były tej samej długości ale może był inny problem. Po prawo jednak dla wyników Away pojawia się błąd #DZIEL/0!.
Edit: W polu wyboru daty wstawiłeś funkcję. Jak mam wybierać datę? Tworząc za każdym razem funkcję?? Generalnie w danych z datami są funkcje z tego co widzę. Tak musi być?
" usunąłeś listę rozwijaną z datami."
nie ma problemu, możesz ją przywrócić, ale daty muszą być w formacie - a nie z dodatkiem „r” na końcu
„A do czego tak z ciekawości była potrzebna dodatkowa kolumna z dniami miesiąca?”
Nie była potrzebna, ale jak wpakuje to do jednej formuły to się robi słabo czytelna.
Generalnie problem był też w dacie, z „r” w komórce excel nie traktował tego jako datę tylko jako tekst
A wtedy nie da się porównać tekstu czy jest większy/mniejszy. Dlatego z boku listy zrobiłem na szybko konwersje do formatu daty (można to w jednej kolumnie zrobić ale dla przykładu lepiej widać rozbite).
„Po prawo jednak dla wyników Away pojawia się błąd #DZIEL/0!.”
Zrób kopię arkusza, filtr na drużynę away i zobacz czy w danym okresie mieli jakieś mecze i czy mieli gole, pewnie było 0 i temu formuła zwraca błąd. Można to np. rozwiązać dodając JEŻELI.BŁAD i wpisać np „brak goli w podanym zakresie”
„Jak mam wybierać datę? Tworząc za każdym razem funkcję??”
możesz wpisać z ręki, albo zrobić listę jak była, tylko upewnij się, że wpisujesz w tym samym formacie.
Np. jak masz w windowsie 2024-04-02 to jak wpiszesz w excelu 02.04.2024 to nie złapie tego jak datę bo bierze regionalne ustawienia windowsa. To jest głównie problem jak masz listę danych z datami z innego kraju (np po angielsku) albo np. z google docs
Wpisałem dla testu zakres ponad roku więc nie możliwe by nie mieli wcale goli w tym czasie.
Zresztą sprawdziłem w komórkach, gole padały.
Edit: Zrobiłem autofiltr na wybraną drużynę, policzyłem średnią goli dla home z określonego/ustawionego zakresu za pomocą prostej funkcji ŚREDNIA by sprawdzić czy Twoja funkcja liczy prawidłowo. Niestety wyniki kompletnie inne. A w away nie wiadomo czemu zwracany jest błąd. Nie zawsze on występuje i nie wiadomo od czego to zależy. Nie mniej jednak jeśli nawet jest jakiś wynik, to jest on nieprawidłowy, bo to ta sama funkcja co dla home.
Też dziwną rzecz odkryłem. Excel/libreoffice w tym podglądzie źle liczy. Policz za pomocą funkcji średnia. Załączam obrazki pokazujące problem. A czemu uważam, że to podgląd/ zaznaczenie źle liczy? Bo funkcja liczy tak jak strona z której pokazałem zrzut w pierwszym poście.
Natomiast czemu błąd #DZIEL/0! czasem występuje to dalej nie wiem.
Edit. Wiem czemu zaznaczenie czasem źle liczy. Nie radzi sobie gdy jest użyty filtr.
W każdym razie wynik średniej z twojej funkcji jest albo zaniżony albo zawyżony.
Z obrazków wynika, że masz tam w tabeli okres 09-04-2023 - 02-09-2024 a ustawione w komórkach 01-05-2023 - 10-05-2024. Średnia liczy tylko to co na szaro czy całość?
Jedno i drugie w każdym razie nie pokrywa się z wyborem zakresu w komórkach. A czemu wyszło tak samo to pojęcia nie mam. Musiałby to być mega przypadek. U mnie jest tak jak pokazałem.
Edit. Ok pobrałem plik i zbadałem w czym rzecz. Zaznaczyłeś tworząc funkcję średnia każdą komórkę z osobna. Daje to inny wynik niż jak zaznaczasz wszystkie komórki z zakresu np. (H199:H380).
Nie wiem czemu tak jest. W każdym razie wynik gdy zaznaczam wszystkie komórki pokrywa się z wynikiem na stronie z której zrobiłem zrzut więc raczej tak się powinno zaznaczać.
„Edit. Ok pobrałem plik i zbadałem w czym rzecz. Zaznaczyłeś tworząc funkcję średnia każdą komórkę z osobna. Daje to inny wynik niż jak zaznaczasz wszystkie komórki z zakresu np. (H199:H380).
Nie wiem czemu tak jest”
Bo jak zrobisz średnią na H199:H380 to liczy średnią właśnie z tego zakresu, nawet jak komórki są pokrywane. Tak działa excel i chyba też reszta podobnego softu.
Jak chcesz liczyć tylko średnią z tego co widzisz to albo dajesz każda komórkę osobno (chyba że zakres jest poukładany obok siebie), zaznaczasz i patrzysz na pasek dolny (na pierwszych screenach tak zrobiłem) albo np stosujesz funkcję SUMY.CZĘŚCIOWE(funkcja_nr;odw1;[odw2];…)
z funkcją 1 (czyli średnia). Np. SUMY.CZĘŚCIOWE(1;H199:H380)
Tylko nie wiem czy taka funkcja jest w libreoffice
Ok ale to zostawmy, chodzi teraz o komórki odkryte i tylko o funkcję średnia. Czemu liczy inaczej gdy zaznaczyłeś każdą komórkę osobno? Średnia to średnia, wyniki powinny być takie same. Tak jak pisałem zaznaczenie zakresu komórek zwraca wynik jak na stronie, więc jest prawidłowy. Ale co ciekawe gdy zaznaczałem wcześniej każdą komórkę osobno ale oddalone od siebie (tylko dla danego zespołu) do funkcji średnia to nie pokryło się to z wynikiem twojej funkcji a pokryło się z wynikiem samego zaznaczenia gdzie wynik pokazywany jest na dolnym pasku po prawo. Wzoruję się na stronie więc muszę zaznaczać tak by wynik się pokrywał a więc tylko zakresy stykających się ze sobą komórek.
To będzie trudne do realizacji dla funkcji bo ta będzie wybierać pojedynczo z komórek bo tylko dla wybranego zespołu. Musiałbym stworzyć osobne tabele z wynikami dla każdego zespołu. A tabela przestawna mogłaby to jakoś inaczej policzyć?
