Excel Forum - Porady, Pomoc,  Excel Help, Excel FAQ Strona Główna
 FAQ  RegulaminRegulamin  Szukaj   Użytkownicy   Grupy   Rejestracja   Profil   Twoje wiadomości   Zaloguj 


Poprzedni temat «» Następny temat
ID tematu: 73263 Skopiuj do schowka Zliczanie komórek zaznaczonych kolorem w przefiltrowanym zak
Autor Wiadomość
cave12
Forumowicz


Wersja: Win Office 365
Posty: 16
Wysłany: 22-08-2022, 18:28   Zliczanie komórek zaznaczonych kolorem w przefiltrowanym zak

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

ID posta: 419862 Skopiuj do schowka
 
 
Maciej Gonet 
Excel Expert


Wersja: Win Office 365
Pomógł: 2828 razy
Posty: 8516
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?
ID posta: 419864 Skopiuj do schowka
 
 
Kaper 



Zaproszone osoby: 2
Wersja: Win Office 365
Pomógł: 4362 razy
Posty: 8612
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
_________________
Kaper Jej Królewskiej Mości :boss

Jeśli dane będą torturowane dość długo, przyznają się do wszystkiego
ID posta: 419877 Skopiuj do schowka
 
 
cave12
Forumowicz


Wersja: Win Office 365
Posty: 16
Wysłany: 23-08-2022, 09:47   

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


Maciej Gonet napisał/a:
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?


plik testowy.xlsx
Pobierz Plik ściągnięto 13 raz(y) 182.27 KB

ID posta: 419878 Skopiuj do schowka
 
 
Kaper 



Zaproszone osoby: 2
Wersja: Win Office 365
Pomógł: 4362 razy
Posty: 8612
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 5 raz(y) 183.94 KB

_________________
Kaper Jej Królewskiej Mości :boss

Jeśli dane będą torturowane dość długo, przyznają się do wszystkiego
ID posta: 419883 Skopiuj do schowka
 
 
Maciej Gonet 
Excel Expert


Wersja: Win Office 365
Pomógł: 2828 razy
Posty: 8516
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.
ID posta: 419884 Skopiuj do schowka
 
 
Kaper 



Zaproszone osoby: 2
Wersja: Win Office 365
Pomógł: 4362 razy
Posty: 8612
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
_________________
Kaper Jej Królewskiej Mości :boss

Jeśli dane będą torturowane dość długo, przyznają się do wszystkiego
ID posta: 419887 Skopiuj do schowka
 
 
cave12
Forumowicz


Wersja: Win Office 365
Posty: 16
Wysłany: 23-08-2022, 14:33   

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
ID posta: 419890 Skopiuj do schowka
 
 
cave12
Forumowicz


Wersja: Win Office 365
Posty: 16
Wysłany: 23-08-2022, 14:45   

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 6 raz(y) 183.59 KB

ID posta: 419892 Skopiuj do schowka
 
 
Maciej Gonet 
Excel Expert


Wersja: Win Office 365
Pomógł: 2828 razy
Posty: 8516
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.

cave12 napisał/a:
Dlaczego chcesz mnożyć przez 30 ?
Bo zliczam komórki wypełnione jakimiś tekstami w jednej kolumnie, a kolumn z liczbami jest 30 od [1Hz] do [30Hz]. Tam jest jeszcze odjęta jedynka - to ze względu na nagłówek.

Kopia plik testowy2.xlsx
Pobierz Plik ściągnięto 11 raz(y) 183.74 KB

ID posta: 419893 Skopiuj do schowka
 
 
Kaper 



Zaproszone osoby: 2
Wersja: Win Office 365
Pomógł: 4362 razy
Posty: 8612
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 8 raz(y) 183.62 KB

_________________
Kaper Jej Królewskiej Mości :boss

Jeśli dane będą torturowane dość długo, przyznają się do wszystkiego
ID posta: 419895 Skopiuj do schowka
 
 
Maciej Gonet 
Excel Expert


Wersja: Win Office 365
Pomógł: 2828 razy
Posty: 8516
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.
ID posta: 419896 Skopiuj do schowka
 
 
cave12
Forumowicz


Wersja: Win Office 365
Posty: 16
Wysłany: 25-08-2022, 13:27   

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)
ID posta: 419997 Skopiuj do schowka
 
 
Wyświetl posty z ostatnich:   
Odpowiedz do tematu
Nie możesz pisać nowych tematów
Nie możesz odpowiadać w tematach
Nie możesz zmieniać swoich postów
Nie możesz usuwać swoich postów
Nie możesz głosować w ankietach
Nie możesz załączać plików na tym forum
Możesz ściągać załączniki na tym forum
Dodaj temat do Ulubionych
Wersja do druku

Skocz do:  

Powered by phpBB modified by Przemo © 2003 phpBB Group
Theme xandgreen created by spleen& Programosy modified v0.3 by warna
Opieka techniczna www.wip.pl

Archiwum

Strona używa plików cookies.

Kliknij tutaj, żeby dowiedzieć się jaki jest cel używania cookies oraz jak zmienić ustawienia cookie w przeglądarce.
Korzystając ze strony użytkownik wyraża zgodę na używanie plików cookies, zgodnie z bieżącymi ustawieniami przeglądarki.
Sprawdź, w jaki sposób przetwarzamy dane osobowe