Excel Forum - Porady, Pomoc, Excel Help, Excel FAQ - Zliczanie komórek zaznaczonych kolorem w przefiltrowanym zak

cave12 · Forumowicz Wersja: Win Office 365 Posty: 16

Witam :)
Szanowni eksperci excela czy ktoś wie może jakiej formuły użyć aby zliczyć komórki które zostały zaznaczone kolorem w przefiltrowanym zakresie (patrz zdjęcie w załączniku).
Będe bardzo wdzięczny za pomoc.

Pozdrawiam

zdjęcie1.JPG

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

Maciej Gonet · Wysłany: 22-08-2022, 19:26

Przede wszystkim załącz przykładowy plik, bo z obrazka nie odczyta się wielu istotnych rzeczy.
Poza tym, wyjaśnij dokładnie, co chcesz zliczać: wszystkie komórki pokolorowane w arkuszu, czy w jakimś zakresie, na jakiś konkretny kolor, czy na dowolny? Czy to jest pokolorowane ręcznie, czy formatowaniem warunkowym? Czy to ma być statyczne, czy dynamiczne? Mam na myśli, że kolorujesz sobie ileś tam komórek, a potem uruchamiasz makro, które je zlicza, czy chcesz to mieć w każdej chwili aktualizowane?

Kaper · Wysłany: 23-08-2022, 09:31

cave12, o przykładowych plikach pisał choćby Bill w poprzednim wątku: http://www.excelforum.pl/viewtopic.php?p=419706

Może przeczytaj podpowiedź z naszego FAQ (przyklejoną w nagłówku działu): https://www.excelforum.pl...ika-vt59262.htm

cave12 · Forumowicz Wersja: Win Office 365 Posty: 16

Maciej,
bardzo dziękuję za Twoje uwagi i wszystkie pytania. Do tego posta dołączam plik excela w którym w arkuszu 2 opisałem (mam nadzieję) jaki efekt końcowy chciałbym uzyskać.
Będę ogromnie wdzięczny za pomoc w rozwiązaniu zadania. Przyda się ono do zastosowań klinicznych.
Pozdrawiam serdecznie

Kaper · Wysłany: 23-08-2022, 11:45

"Żywe" dane oczywiście mają swoje zalety, ale boję się, że są "ciut za duże". Ale spróbujmy.
Powiedzmy, że w kolumnie AL wyłączyłem w filtrze puste. Czy zliczenie w B3 powinno wynosić 900? tak jak

Kod:

=SUBTOTAL(103;Center_voxel_CSD_EO!$G$2:$AJ$129)

I skoro pod "zaznaczonych kolorem" rozumiesz formatowanie warunkowe (różyk dla >2 i błękit dla <-2)
To co prawda trudniej ale możemy policzyć te dwie grupy:

Kod:

=SUMPRODUCT((Center_voxel_CSD_EO!$G$2:$AJ$129>2)*(SUBTOTAL(103;OFFSET(Center_voxel_CSD_EO!$G$2;ROW(Center_voxel_CSD_EO!$G$2:$G$129)-MIN(Center_voxel_CSD_EO!$G$2:$G$129);0))))+SUMPRODUCT((Center_voxel_CSD_EO!$G$2:$AJ$129<-2)*(SUBTOTAL(103;OFFSET(Center_voxel_CSD_EO!$G$2;ROW(Center_voxel_CSD_EO!$G$2:$G$129)-MIN(Center_voxel_CSD_EO!$G$2:$G$129);0))))

Ale oczywiście kiedy zmienisz filtrowanie to i wyniki się przeliczą.

A skoro chcesz mieć zestawienie dla kolejnych to pewnie niezłym pomysłem byłoby makro, które
albo po kolei by filtrowało dane w ten sposób i zapisywało wynik formuły jako liczbę
albo po prostu robiło pracowicie kolejne pętle po danych (ale po załadowaniu ich z arkusza do pamięci).

plik testowy.xlsx

Pobierz Plik ściągnięto 8 raz(y) 183.94 KB

Maciej Gonet · Wysłany: 23-08-2022, 12:06

Na pewno droga do celu nie wiedzie przez liczenie kolorowych komórek. To jest w Excelu ostateczność, jak zaznaczenie komórki zależy od czyjegoś "widzimisię". Tutaj jeśli kolory są wynikiem formatowania warunkowego, to trzeba zliczać te warunki, a nie kolory. Kolory mogą służyć do wizualizacji, a nie być nośnikiem danych.
Kaper był szybszy, ale ja myślę, że to filtrowanie to "zmyłka". Z opisu wynika, że filtrowanie ma tu charakter pomocniczy, a w istocie chodzi o "trwałe" wyniki niezależne od użycia filtra. Więc raczej nie mieszałbym do tego SUM.CZĘŚCIOWYCH, lecz wykorzystał funkcję FILTRUJ lub coś takiego.

W Twoim pliku zabrakło przykładowych wyników, a to nie jest takie całkiem oczywiste, więc chcę się upewnić, czy dobrze rozumiem. W kolumnie B arkusza 'Stan sieci' mają być zliczone komórki z kolumn G:AJ arkusza Center, które odpowiadają niepustym komórkom w odpowiednich kolumnach z przedziału AL:EI? Przydałaby się pomocnicza kolumna w arkuszu 'Stan sieci' ze wskazaniem, w której kolumnie są dane do danego wiersza. Myślałem, że to się da wyliczyć, ale są niezgodności. Jeśli chodzi o tę część (kol. B), to można chyba założyć, że wszystkie pola w kolumnach G:AJ są wypełnione i liczbę niepustych wierszy pomnożyć przez 30.

Jeśli chodzi o kolumnę C, to tu trzeba już zliczyć osobno te komórki >2 i <-2 i wyniki dodać. Jest tu też pewna niekonsekwencja, bo w formatowaniu warunkowym jest nierówność ostra >, a w opisie nieostra >=. Proszę o wyjaśnienie i potwierdzenie, czy o to chodzi.

Kaper · Wysłany: 23-08-2022, 12:43

Jadę rowerem i "uwolniłem umysł" i chciałem napisać, właściwie to samo, co Maciej. Zdecydowanie odpuścić filtrowanie i w licz.warunki czy suma.iloczynow uwzględnij warunek, że komórka w kolumnie, która byłaby filtrowana ma być niepusta. W tym momencie możesz mieć komplet wyników "na raz" formułami. Przy czym i tak wygodniej będzie zsumować liczbę wyników większych od 2 i mniejszych od -2

cave12 · Forumowicz Wersja: Win Office 365 Posty: 16

Dzięki za formuły :)

"Powiedzmy, że w kolumnie AL wyłączyłem w filtrze puste. Czy zliczenie w B3 powinno wynosić 900?"

Tak powinno wynosić 900

cave12 · Forumowicz Wersja: Win Office 365 Posty: 16

Maciej,
dzięki za wskazówki.

"W Twoim pliku zabrakło przykładowych wyników, a to nie jest takie całkiem oczywiste, więc chcę się upewnić, czy dobrze rozumiem. W kolumnie B arkusza 'Stan sieci' mają być zliczone komórki z kolumn G:AJ arkusza Center, które odpowiadają niepustym komórkom w odpowiednich kolumnach z przedziału AL:EI?"

Tak w kolumnie B "Stan sieci" powinny znaleźć się wyniki zliczeń komórek z kolumn G:AJ arkusza Center które odpowiadają wierszom po przefiltrowaniu odpowiedniej kolumny z przedziału AL:EI i odhaczeniu kwadracika "puste"(generalnie po zastosowaniu takiego filtra wszystkie wiersze danej "filtrowanej" kolumny powinny być wypełnione (zawierać dane struktur lub pól Brodmanna :)). W załączniku plik gdzie wpisałem w arkuszu "stan sieci" pierwsze wiersze z pożądanymi wynikami (chciałbym aby po przefiltrowaniu) arkusz wyrzucał mi taką pełną tabelę o stanie poszczególnych sieci które bym sobie wybierał poprzez np. filtrownie.

"Myślałem, że to się da wyliczyć, ale są niezgodności. Jeśli chodzi o tę część (kol. B), to można chyba założyć, że wszystkie pola w kolumnach G:AJ są wypełnione i liczbę niepustych wierszy pomnożyć przez 30. "

Tak wszystkie komórki w klasterze powinny mieć wartości. Dlaczego chcesz mnożyć przez 30 ?

"Jeśli chodzi o kolumnę C, to tu trzeba już zliczyć osobno te komórki >2 i <-2 i wyniki dodać. Jest tu też pewna niekonsekwencja, bo w formatowaniu warunkowym jest nierówność ostra >, a w opisie nieostra >=. Proszę o wyjaśnienie i potwierdzenie, czy o to chodzi."

Chodzi w obu przypadkach o nierówność nieostrą czyli większe + równe 2.0 oraz mniejsze + równe -2.0

plik testowy_new.xlsx

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

Maciej Gonet · Wysłany: 23-08-2022, 14:49

Byłoby łatwiej jakbyś miał porządek w nazwach.
Dlaczego co innego jest w kolumnie A 'Stanu sieci', a co innego w nagłówkach w arkuszu Center...?
Można by to było jakoś automatycznie dopasować, a tak to trzeba zrobić kolumnę pomocniczą, żeby wiadomo było, co do czego ma pasować.

Zobacz propozycję rozwiązania w załączniku. Filtry nie są potrzebne. Wyniki są niezależne od ustawień filtrów.

P.S. W kolumnie C popraw sobie nierówność ostrą na nieostrą, bo zobaczyłem Twoją odpowiedź już po wysłaniu mojego pliku.

Kaper · Wysłany: 23-08-2022, 15:12

Moja propozycja trochę inna, bo bazująca wyłącznie na jedej funkcji - SUMA.ILOCZYNÓW*

Żeby nie wykorzystywać adresowania pośredniego mam propozycję umieszczenia tabeli wynikowej w układzie poziomym (a skoro zawsze są po dwie kolumny na jeden rodzaj, to co drugą kolumnę). *) skoro chcemy wypełniać co drugą kolumnę, to skorzystamy jeszcze z czy.nieparzysta i z jeżeli

w J3:

Kod:

=IF(ISODD(COLUMN(J1));"";SUMPRODUCT((Center_voxel_CSD_EO!$G$2:$AJ$129<>"")*(Center_voxel_CSD_EO!AL$2:AL$129<>"")))

w J4:

Kod:

=IF(ISODD(COLUMN(J1));"";SUMPRODUCT(((Center_voxel_CSD_EO!$G$2:$AJ$129<-2)+(Center_voxel_CSD_EO!$G$2:$AJ$129>2))*(Center_voxel_CSD_EO!AL$2:AL$129<>"")))

a nagłówki:

Kod:

=IF(ISODD(COLUMN(J1));"";Center_voxel_CSD_EO!AL1)

i kopia wszystkich formuł w prawo

A jak już się przeliczy, nic nie stoi na przeszkodzie, żeby skopiować i wkleić specjalnie jako wartości z transpozycją. i odfiltrować oraz usunąć puste wiersze

plik testowy.xlsx

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

Maciej Gonet · Wysłany: 23-08-2022, 15:19

Pewien problem stanowi to, że kolejność danych w obu arkuszach nie jest zgodna.
Nie wiem, czy jest to kwestia niestaranności, możliwa do skorygowania, czy dane z jakichś względów muszą być tak ułożone.

cave12 · Forumowicz Wersja: Win Office 365 Posty: 16

Panowie Bill i Kaper bardzo dziękuję Wam za pomoc. Bill arkusz który przygotowałeś działa wydaje mi się bez zarzutu. Muszę jeszcze raz sprawdzić czy wszystkie sieci zliczają się poprawnie ale jak przeglądałem wyrywkowo to wszystko było cacy. Masz rację nie ma zgodności w kolumnach w pierwszym i drugim arkuszu opisującym sieci. Kolejność nie ma znaczenia ale myślę że moje niechlujstwo mogło wprowadzić niepotrzebnie dodatkowe trudności z opracowaniem formuły - z czym jak widzę poradziłeś sobie Bill mimo wszystko.
Jeszcze raz very dzięki :)
Pozdrawiam
R (cave12)