Excel Forum - Porady, Pomoc, Excel Help, Excel FAQ - formuła, zliczająca z 12 msc różne wartości dla dwóch progów

pracowniczka0 · Świeżak Wersja: Win Office 2010 Posty: 6

Cześć, potrzebuje pomocy ze stworzeniem formuł(y).

Mianowicie, mam kolumnę C z pacjentami, w kolumnie B mam obok liczbę porządkową, aby wiedzieć ilu ich jest. W kolumnach od D i dalej są miesiące, gdzie najbardziej interesuje mnie okres 12 miesięcy od kolumny D (listopad 2023) do kolumny P (listopad 2024). Każdy pacjent dostaje co miesiąc wartość. Mnie interesują te kolorowe, czyli 0, gdyż potrzebuje mieć próg 10% pacjentów, którzy mają wartość 0, ale też 5 lub 10 lub 15, bo razem muszę mieć co najmniej 20% pacjentów z tymi wartościami.
Obliczyłam sobie niżej progi, jeden to B24*10%, drugi B24*20%, a nawet stworzyłam formuły, aby mi pokazywało czy w danym miesiącu próg 10% czy 20% jest spełniony:
=LICZ.JEŻELI(D2:D24,"0") i =JEŻELI(B31<=D28,"git","za mało") na 10%
=LICZ.JEŻELI(D2:D24,"5")+LICZ.JEŻELI(D2:D24,"10")+LICZ.JEŻELI(D2:D24,"15") i =JEŻELI(B32<=D29,"git","za mało") na 20%

Na podstawie tych informacji wyżej, mam nadzieję, że ktoś będzie mógł mi pomóc stworzyć formułę, która mi zliczy, czy w okresie 12 miesięcy (kolumna od D do P) został spełniony próg 10% dla wartości 0 i oddzielnie próg 20% dla wartości: 5, 10 i 15.
Z tym, że chciałabym, aby ta formuła brała pod uwagę całość, czyli jak np. patrzymy na kolumnę F, styczeń 2024 to tam jest 8 takich pacjentów, a np. w kolumnie N (wrzesień 2024) jest tylko 6 pacjentów, a ja bym chciała, aby tak jak zaznaczyłam komórki brała pod uwagę, że w sumie było w tym okresie 10 pacjentów (tu aby mnie zrozumieć, proszę patrzeć na zrzut)
Zatem jeśli próg dla wartości 5, 10 i 15 to 23*20%=4,6 ~5 pacjentów, a ja mam 10 pacjentów w tym okresie, to aby mi pokazywało, że próg jest spełniony na ten dany okres.
Analogicznie do progu 10%, gdzie ręcznie sobie zliczam 23*10%=2,3 pacjenta, zaokrąglając "mocno" do góry potrzebuje przynajmniej trzech pacjentów z wartością 0 w tym okresie i jak widać we wrześniu jest to spełnione, ale tak samo jest w ciągu tych 12 miesięcy gdy się patrzy na całość.

Mam nadzieję, że da się zrozumieć co napisałam i nie jest to zbyt pogmatwane i chaotyczne. Dajcie znać czy w ogóle da się stworzyć taką formułę. Siedzę nad tym już tydzień i próbowałam na różne sposoby, ale coś nie łapie.

arkuszformula.png
proszę się sugerować tym zrzutem ekranu do opisu

Plik ściągnięto 41 raz(y) 58.76 KB

xfish · Wysłany: 30-09-2024, 13:18

Na pewno da się coś wymyślić ale załącz plik a nie zdjęcie.
Do tego napisz w nim jakie są oczekiwane wartości, wtedy będzie dużo łatwiej osobie, która będzie chciała do tego usiąść.

pracowniczka0 · Świeżak Wersja: Win Office 2010 Posty: 6

dołączam plik do tematu

forum.xlsx
plik do tematu

Pobierz Plik ściągnięto 9 raz(y) 74.17 KB

Maciej Gonet · Wysłany: 30-09-2024, 15:11

Niestety opis nie jest ścisły, a w obliczeniach wszystko powinno być określone precyzyjnie.
Brak też oczekiwanych wyników, o które prosił kolega xfish.
Zacznijmy od tego, że kolumny D:P to okres 13 miesięcy, a nie 12.
Zatem, czy chodzi o ten konkretny okres, czy ogólnie okres 12 miesięcy, zaczynając od jakiegoś wskazanego miesiąca?
W wierszu 35. nie można porównywać konkretnych liczb z procentami tylko liczby z liczbami. Zatem jeśli w B31 jest już obliczony próg 10%, to można tę wartość zastosować. Rozumiem, że tu przyjęto, że liczba pacjentów pozostaje stała w całym okresie.
Jeśli każdy pacjent w określonym miesiącu ma być traktowany jako osobna jednostka ("pacjentomiesiąc" :?:

), to na czym polega problem? Przecież w funkcji LICZ.JEŻELI można wskazać dowolny zakres, nie tylko jedną kolumnę. Patrz wiersze 37 i 38.
Jeszcze uwaga na koniec. Jeśli kryterium w LICZ.JEŻELI jest liczbowe, to nie trzeba go ujmować w cudzysłów. Cudzysłów jest potrzebny gdy chcemy użyć kryterium z "<" lub ">" itp.

Jeśli jednak miało być coś innego, to proszę o dokładny opis z przykładem co i jak liczyć.

forum (pacjenci).xlsx

Pobierz Plik ściągnięto 7 raz(y) 74.61 KB

pracowniczka0 · Świeżak Wersja: Win Office 2010 Posty: 6

Dobra po kolei, nie wiem o jakie "oczekiwane wartości"/wyniki chodzi.

Jeśli jest 23 pacjentów, to próg dla 10% to 2,3, który potrzebuje do góry zaokrąglać, zatem potrzebuje przynajmniej 3 pacjentów z wartością 0.
Do wartości 5, 10 i 15 potrzebuje 20% czyli 4,6 ~ 5 pacjentów.

Wiersz 35 z pliku proszę zignorować, to moje wcześniejsze próby.
Faktycznie wychodzi, że od listopada 2023 do listopada 2024 jest 13 miesięcy, to aby to poprawić, chodzi mi do października 2024 włącznie, czyli kolumny O.

Liczba pacjentów nie jest stała w okresie, bo jak widać po "x" znikają, albo później dochodzą. Ale jak się chociaż pojawią na jeden miesiąc z jakąkolwiek wartością, to się wliczają.

Chodzi właśnie o ten okres 12 miesięcy. Czy pacjent się pojawi na miesiąc czy na cały ten okres to się wlicza w całość. I jeśli ma wartość 0 tylko przez miesiąc czy dwa albo wartość 5/10/15 to tak samo się bierze pod uwagę. Tak jak na tym zrzucie ekranu zaznaczyłam konkretne komórki, aby pokazać co się liczy. Pacjent 1 się wlicza z wartością 10, mimo, że jest do stycznia 24, pacjent 5 jest cały okres, też się wlicza, a np. pacjent 6 jest tylko od marca do czerwca 2024, ale też się wlicza. Bo jakby chodzi żeby patrząc na ten okres 12 miesięcy patrząc np. wierszami widać było, że w sumie pacjentów z 5/10/15 było 10. A próg to przecież 5, zatem wszystko jest jak trzeba.
A nawet jest ich 11, tylko zapomniałam zaznaczyć do zrzutu pacjenta nr 20.

Na przykładzie wartości 0. Gdyby pacjent nr 20 od września nie miał wartości 0, to w całym okresie 12 miesięcy byłoby tylko 2 pacjentów z wartością 0, a żeby spełniać próg potrzebne jest 3 pacjentów.

I właśnie tego nie potrafię ogarnąć, aby stworzyć formułę, która patrzy na to tak jak ja to widzę.

Z tego pliku excela dołączonego zostało policzone coś, czego nie potrafię sobie przetłumaczyć. Próg*kolumny ma być mniejszy lub równy wartości "0". To chyba nie o to chodzi. Mam nadzieję, że teraz jakoś lepiej to wyżej określiłam.
Liczby 24 czy 82, to nie takie, gdyż z mojej perspektywy powinny być 3 i 11 (znowu odsyłam do zrzutu, tam zaznaczyłam te 11 komórek, a w sumie zaznaczyłam 10, bo pominęłam pacjenta nr 20).

Proszę dać znać czy teraz da się zrozumieć o co mi chodzi.

Maciej Gonet · Wysłany: 01-10-2024, 12:14

Najistotniejsze jest jak rozumieć pojęcie "pacjent" w kontekście okresu wielomiesięcznego.
Gdyby wartości liczbowe przypisane do pacjenta były zawsze jednakowe (pomijając komórki puste i "x"), to można byłoby przyjąć taką interpretację: jeśli w rozpatrywanym okresie wszystkie wartości liczbowe są równe, to przyjmujemy tę wartość jako "miarę" pacjenta.
Ale jest np. wiersz z pacjentem numer 20, w którym w różnych miesiącach są różne liczby. Co przyjąć w takim przypadku?

Jeśli prosiłem o oczekiwane wyniki, to znaczy, że chciałbym poznać dokładny tok rozumowania, który ma doprowadzić do takiego czy innego wyniku obliczeń.
Jak się okazuje moje zrozumienie problemu było nietrafne, zatem sprawa nie jest oczywista.

pracowniczka0 · Świeżak Wersja: Win Office 2010 Posty: 6

Jakby wartości właśnie są miarą, przyjmijmy zdrowia pacjenta, który pod wpływem różnych czynników może się pogarszać lub polepszać.

Czy przez to, że się zmieniają wartości i przez to, że właśnie są puste komórki i komórki z "x" nie da się stworzyć tego w taki sposób jaki bym chciała?

Maciej Gonet · Wysłany: 02-10-2024, 08:25

Ale właśnie dokładnie nie wiadomo jak byś chciała, bo nie podajesz konkretnych oczekiwanych wyników w określonym układzie danych.
Puste komórki i komórki z "x" można pominąć, jeśli będzie powiedziane, że należy je pomijać.
Natomiast z pacjentami, którym wartości się zmieniają trzeba coś zrobić - ale to jest Twoja kompetencja, bo formułę zawsze można napisać, tylko musi być znany algorytm (sposób postępowania).

pracowniczka0 · Świeżak Wersja: Win Office 2010 Posty: 6

Oczekiwanym wynikiem będzie, gdy excel określi mi np. "git", gdy będzie spełniony warunek:

1. Spośród wszystkich pacjentów (23) 10% z nich będzie mieć wartość 0 (przynajmniej raz w ciągu tych 12 miesięcy). Czyli 23*10%=2,3 a zaokrąglając do góry 3 pacjentów. I tak jak jest na zrzucie, mamy 3 takich pacjentów, więc wtedy gdzieś pokazuje mi "git", a jeśli byłoby ich mniej to "za mało".

2. Spośród wszystkich pacjentów (23) 20% z nich będzie miało wartość 5 lub 10 lub 15 przynajmniej raz przez 12 miesięcy (kolumny D:O). 23*20%=4,6 a w zaokrągleniu do góry 5 pacjentów. Czyli jak na zrzucie widać jest takich pacjentów 11, zatem daje mi znać, że jest "git", a jakby było mniej np. 2, to wtedy "za mało".

Maciej Gonet · Wysłany: 02-10-2024, 09:11

Czyli należy przyjąć, że jeśli jeden i ten sam pacjent miał w swojej "historii" w jednym miesiącu 0, a w innym 5, 10 lub 15, to należy go zliczyć w obu tych kategoriach. Tak?

Jeśli tak, to można zrobić bilans pacjentów jak w kolumnach V i W (można oczywiście przenieść to gdzie indziej gdyby przeszkadzało w wypełnianiu kolejnych miesięcy). Podsumowanie tego bilansu w komórkach V31:W31.

forum (pacjenci2).xlsx

Pobierz Plik ściągnięto 11 raz(y) 75.54 KB

pracowniczka0 · Świeżak Wersja: Win Office 2010 Posty: 6

Dziękuję pięknie, teraz wszystko jest super.
Próbowałam z taką kolumną na TAK/NIE wcześniej, ale dalej nie potrafiłam tego pociągnąć i gdzieś w tym wszystkim się gubiłam.

Tylko jeśli mogę zapytać, to dlaczego w podsumowaniach jest: "ZAOKR.GÓRA($B$43,0)"? Chodzi o te 0, dlaczego 0?

Jeszcze raz dziękuję!

Maciej Gonet · Wysłany: 02-10-2024, 14:43

To "0" w formule ZAOKR.GÓRA oznacza liczbę miejsc po przecinku, zero to znaczy zaokrąglenie w górę do najbliższej liczby całkowitej.