X

Excel - liczenie komórek po kolorze tła

wymiatasz 30 May 2010 21:37
  • #1 30 May 2010 21:37
    wymiatasz
    Level 2  
    Helpful post? (0)
    Witam,

    Nigdzie znaleźć nie mogę, czy ktoś wpadł na coś takiego jak zliczanie komórek w excelu (ver. 2003) poprzez identyfikację koloru tła komórek ...

    Ma ktoś jakiś pomysł? tudzież propozycję zastosowania innego programu tego typu ?

    Pozdrawiam
  • #2 30 May 2010 22:18
    yogi009
    Level 39  
    Helpful post? (+1)
    A czy to tło jest wynikiem działania jakiegoś warunku logicznego ? Może pójść tym torem ?
  • #3 30 May 2010 22:28
    wymiatasz
    Level 2  
    Topic author Helpful post? (0)
    Sama "docelowa" komórka, powinna zliczyć z danego wskazanego zakresu komórek, ilość komórek, które mają tło czerwone. Chcę wyswietlić ilość tych komórek po prostu w formie zwykłej liczby. Tła zliczanych komórek nie mają w sumie formatowania warunkowego, tło określam dla danej komórki ręcznie, w zależności od moich "danych zewnetrznych".

    Mogłoby być to też w formie np. wyświetlania do innej komórki jakiejś wartości liczbowej, odpowiadającej kolorowi badanej komórki. Później bym sobie po prostu zliczył te wartości liczbowe.
    Łopatologiczny przykład:
    JEŻELI "komórka A4" ; "kolor czerwony" ; "wyświetl 1"
  • #4 31 May 2010 13:35
    walek33
    Level 28  
    Helpful post? (0)
    Użyj:
    Code:
    (adres_komórki).Interior.ColorIndex
  • #5 31 May 2010 14:28
    wymiatasz
    Level 2  
    Topic author Helpful post? (0)
    A bez makr, skryptów itp itd się nie da? ;)

    Jak się nie da to ktoś mógłby napisać bardziej szczegółową instrukcję jak użyć podobnego sformułowania ;).
  • Helpful post
    #6 31 May 2010 20:46
    adamas_nt
    Moderator Programowanie
    Positive voted Helpful post? (+6)
    wymiatasz wrote:
    A bez makr, skryptów itp itd się nie da? ;)
    Niestety, nie.

    Możesz użyć funkcji użytkownika podobnej do tej
    Code:
    Function LiczKolory(zakres As Range, kolor As Integer)
    
    For Each kom In zakres
      If kom.Interior.ColorIndex = kolor Then
        LiczKolory = LiczKolory + 1
      End If
    Next
    End Function
    Wklejasz do modułu standardowego i używasz jak funkcji Excela tzn
    Code:
    =liczkolory(zakres;indeks koloru)
    Paletę znajdziesz w necie. Można też jako drugi argument wykorzystać indeks komórki wzorcowej.
  • #7 31 May 2010 21:06
    wymiatasz
    Level 2  
    Topic author Helpful post? (0)
    No i nareszcie jest ok :). Dzięki wielkie za pomoc!
  • #8 17 Jun 2010 14:57
    adrian2202
    Level 1  
    Helpful post? (+1)
    Proszę pomóżcie mi w temacie.
    Tak jak koega wymiatasz muszę zliczać ilość komórek zaznaczonych w danym kolorze ale nie mam pojęcia jak do tego dojść.
    Poproszę o instrukcję w miarę krok po kroku jak to zrobić bo nie mam pojęcia, numery kolorów gdzieś tam ściągnołem ale makro stworzyło mi nowy zeszyt z kolorem i jego numerem jednak nie wiem jak się do tego odwołać. Podana przez was formuła nie chce mi się uruchomić..... eghhh muszę to zrobić a nie mam pojęcia jak?
  • #9 17 Jun 2010 16:33
    marek003
    Level 39  
    Helpful post? (0)
    Przecież masz opisane
    Wklejasz makro do modułu
    (paski narzędzi - visual basic - edytor visual basic.
    menu insert - moduł ... i tu wklejasz kod funkcji)
    Edytor możesz zamknąć.

    Przechodzisz do arkusza i w wolnej komórce wpisujesz np =liczkolory(A1:B100;5)
    wg podpowiedzi:
    =liczkolory(zakres;indeks koloru)
    I masz policzony dany kolor
  • #10 23 Jun 2010 20:47
    ktr
    Level 10  
    Helpful post? (0)
    Witam, mam problem jak zrobić jedno zadanie w Excelu - bardzo proszę o pomoc.

    W tabeli Excela są 3 kolumny: "zadanie" (tekst, opis zadania), "deadline" (data, do kiedy zadanie ma być wykonane), "status" (rozwijalne pole wyboru: zrobione, w toku, opóźnione, nie rozpoczęte).

    Kolumna "status" to zwykła funkcja: sprawdzanie poprawności -> Lista, gdzie zdefiniowałem, co można sobie wybrac. Użyłem także formatowania warunkowego: gdy wybierze się 'zrobione' komórka ma kolor zielony, gdy 'w toku' żółty, gdy 'opóźnione' to czerwony - reszta czyli 'nie rozpoczęte' siłą rzeczy ma biały, czyli brak formatowania:)

    Chciałem podrasować tabelę dodając automatyczne sprawdzanie, czy dzisiejsza data jest późniejsza niż określona w deadlinie i jeśli tak - to żeby automatycznie wymuszał kolor czerwony w statusie. Jest to jako Warunek 1 w formatowaniu warunkowym, problem w tym, że nawet jeśli wybierze się status 'zrobione' a data jest po deadlinie to kolor jest czerwony, a powinien być zielony. Nie wiem jak poradzić sobie np. funkcją JEŻELI.

    By pomóc w zrozumieniu mojego problemu załączam tabelkę:)
  • #11 24 Jun 2010 08:58
    walek33
    Level 28  
    Helpful post? (0)
    ktr wrote:
    Użyłem także formatowania warunkowego

    I tu powstał problem ponieważ
    Code:
    kom.Interior.ColorIndex
    nie policzy kolorów formatowania warunkowego.
  • #12 25 Jun 2010 12:45
    ktr
    Level 10  
    Helpful post? (0)
    Czy ktoś jest w stanie pomóc mi w rozwiązaniu mojego problemu? Jestem w Excelu dosyć zielony.
  • #13 25 Oct 2010 07:48
    klonik
    Level 2  
    Helpful post? (0)
    Fajnie to jest rozwiązane ale mam bardziej skomplikowany przykład. Mam w kolumnie wybrać liczbę max. (=max(a1:a5)) Potem przez formatowanie warunkowe komórka z największą liczbą ma zmienić kolor tła i kolor czcionki . W komórkach B1:b5 jest formuła (=jeżeli(max(a1:a5)=a1/2;a1). Taki ciąg ma mieć X kolumn. Następnie w wierszach mam z sumować ile komórek w danym wierszu zmieniło kolor tła.
    Excel - liczenie komórek po kolorze tła
    Powyżej podane rozwiązania powodują, że we wszystkich wierszach mam sumę 4 (jeśli wybiorę nr koloru niebieskie) i 0 jeśli wybiorę każdy inny kolor. Obszedłem to poprzez dodanie "ślepej" kolumny gdzie przy komórce z max liczba jest formuła =jeżeli(max(blok)=komórka na lewo; "max";'''). Po tym zabiegu zliczam komórki w wierszu z napisem "max" {=ile.jeżeli(blok;"max")}. Efekt jest osiągnięty ale ..... Może ktoś to wymyslił??
  • #14 10 Apr 2013 17:54
    mundeczek88
    Level 9  
    Helpful post? (0)
    adamas_nt wrote:
    wymiatasz wrote:
    A bez makr, skryptów itp itd się nie da? ;)
    Niestety, nie.

    Możesz użyć funkcji użytkownika podobnej do tej
    Code:
    Function LiczKolory(zakres As Range, kolor As Integer)
    
    For Each kom In zakres
      If kom.Interior.ColorIndex = kolor Then
        LiczKolory = LiczKolory + 1
      End If
    Next
    End Function
    Wklejasz do modułu standardowego i używasz jak funkcji Excela tzn
    Code:
    =liczkolory(zakres;indeks koloru)
    Paletę znajdziesz w necie. Można też jako drugi argument wykorzystać indeks komórki wzorcowej.

    Przepraszam, że odgrzebuję temat.
    Używam Excela 2003.
    Zastosowałem funkcję, ale niestety Excel nie chce przeliczyć ponownie komórek.
    Dopiero po kliknięciu komórki, wyświetleniu funkcji na pasku i kliknięciu Enter dane zostają przeliczone.
    F9 nie daje rezultatu, poziom zabezpieczeń średni, automatyczne przeliczanie w Excelu włączone.
    Jak zmusić Excela do przeliczenia po każdej zmianie w danych?
  • #15 10 Apr 2013 21:49
    adamas_nt
    Moderator Programowanie
    Helpful post? (+1)
    mundeczek88 wrote:
    F9 nie daje rezultatu
    A powinno (?) W ostateczności wklej w kod arkusza
    Code: vb
    Log in, to see the code
    Arkusz zostanie przeliczony przy każdej zmianie aktywnej komórki.
  • #16 10 Apr 2013 22:13
    mundeczek88
    Level 9  
    Helpful post? (0)
    Bardzo dziękuję za odp.
    No może i F9 nie powinno.
    Wkleiłem, ale bez zmian.
    Nie wiem zupełnie co jest nie tak.
    W Module1 mam:
    Excel - liczenie komórek po kolorze tła

    W Kod Arkusza:
    Excel - liczenie komórek po kolorze tła

    Tylko może opiszę jak plik wygląda.
    Mam kilka arkuszy z kolorowymi zaznaczeniami wierszy.
    W ostatnim arkuszu podsumowanie po ile wierszy danego koloru jest w sumie.
    Kod Arkusza mam tylko w tym ostatnim pliku.
    Może to o to chodzi?
  • #17 10 Apr 2013 22:45
    adamas_nt
    Moderator Programowanie
    Helpful post? (0)
    Spróbuj przeliczyć wszystkie. Podmień kod na:
    Code: vb
    Log in, to see the code
    Ale to chyba nic nie da. Pozostaje F2>Enter, albo napisanie bardziej rozbudowanego makro.
  • #18 11 Apr 2013 20:19
    mundeczek88
    Level 9  
    Helpful post? (0)
    No niestety miałeś rację i nie działa :(
    Co do bardziej rozbudowanego makro to niestety nie znam Visual Basic, żeby takowe napisać.
    Pozostaje mi szukanie w sieci i sprawdzanie empiryczne.
    Dziekuję za pomoc.
  • #19 11 Apr 2013 21:15
    marek003
    Level 39  
    Helpful post? (0)
    :arrow: adamas - było już podobne zagadnienie na forum i chyba sam dawałeś na to rozwiązanie. Na pewno nie było to zwykłe "Calculate". Wydaje mi się że coś w stylu:
    Application.CalculateFull

    Choć sprawdziłem i to też nie działa. (Chyba że podepnę makro pod przycisk, [makro w którym jest jedynie kod "Calculate"] ale wtedy i "zwykłe" calculate działa)

    Chodzi więc bardziej o to gdzie wrzucić ten kod, bo na zmianę koloru komórki nie reaguje "change" arkusza
  • #20 12 Apr 2013 10:25
    Maciej Gonet
    Level 30  
    Helpful post? (0)
    Dzień dobry,
    W sprawie zliczania kolorów chciałbym kolegom podpowiedzieć, że Excel uwzględni zmiany po naciśnięciu Ctrl+Alt+F9.
    Powodzenia!
  • #21 12 Apr 2013 12:30
    marek003
    Level 39  
    Helpful post? (0)
    Maciej Gonet wrote:
    Excel uwzględni zmiany po naciśnięciu Ctrl+Alt+F9

    Jak najbardziej masz rację, ale za to właśnie odpowiada w makrze kod: Application.CalculateFull
    Tylko jak go wywołać "automatycznie" (nie poprzez przycisk a poprzez zmianę koloru komórki formatowaniem [oczywiście nie formatowaniem warunkowym bo to całkiem inne zagadnienie])?.
  • #22 12 Apr 2013 13:37
    Maciej Gonet
    Level 30  
    Helpful post? (0)
    Nie ma chyba innej możliwości niż podpiąć to pod zdarzenie SelectionChange. Zmiana nie będzie automatyczna, ale jak się coś robi w arkuszu, to stale wybiera się jakieś komórki, a gdy zmiana następowałaby z kodu, to trzeba pamiętać, żeby na koniec wybrać jakąś komórkę.
  • #23 20 Sep 2013 14:33
    sushuang
    Level 2  
    Helpful post? (0)
    Witam Wszystkich,

    Postanowiłem napisać od początku do końca rozwiązanie problemu ze zliczaniem kolorów "dla zielonych". Być może komuś na przyszłość się to przyda.

    1. Otwórz Arkusz excel

    2. Wejdz w Plik/Zapisz jako tutaj wybierz z listy Zapisz jako typ: Skoroszyt programu Excel z obsługą makr i wpisz nazwę swojego pliku. Następnie zapisz go w dowolnym miejscu.

    3. Wejdź w zakładke Deweloper na samej górze arkusza i kliknij w ikonę po lewej stronie o nazwie Visual Basic. Na drzewku po lewej stronie kliknij dwukrotnie na Module1 i wklej tam poniższą zawartość(jeżeli module1 nie istnieje to kliknij prawym przyciskiem myszy w puste pole pod napisem "ten skoroszyt" i wstaw module1):

    Code:
    Function Kolor(Adres As Range)
    

        Application.Volatile True

        Kolor = Adres.Interior.ColorIndex

    End Function


    Zamknij wyświetlone okna(zapisz zmiany) wracając do arkusza.

    GOTOWE :)

    Jak to teraz działa?

    Przykład:

    w komórce A1 wpisz Jan
    w komórce B1 wpisz =kolor(a1) => powinna wyskoczyć wartość liczbowa(wciśnij F9)

    Zmień kolor A1 na czerwony i naciśnij F9. Wartość powinna się zmienić.

    Jak teraz zliczyć wartości wszystkich zakolorowanych komórek. Polecam np funkcje licz.jeżeli

    np dla koloru zielonego wartość to 43. W C1 wpisujemy licz.jeżeli(B:B;43), czyli jeżeli w kolumnie B pojawi się liczba 43 to nastąpi suma takich komórek o +1.

    Kolorujemy wszystkie komórki w kolumnie A, wciskamy F9 i w komórce C1 pojawia się liczba ile takich komórek jest. Proste ;) Jeżeli nie pasuje Wam że wartości koloru w kolumnie B są pokazywane proponuje zwyczajnie ukryć tą kolumnę.

    Pozdrawiam!

    Su
  • #24 20 Sep 2013 22:11
    cbrman
    Level 25  
    Helpful post? (0)
    Skoro już temat został odgrzebany to jeszcze dodam, że można wyciągnąć numer ręcznie nadanego koloru tła komórki bez użycia makr.
    Szczegóły w poniższym linku
    http://www.apocotenexcel.pl/okom.htm

    Lista argumentów funkcji o.komórce
    http://www.excelforum.pl/topics1/edytowanie-adresu-komorki-vt31631.htm#167495
  • #25 14 Dec 2013 15:21
    maryna 76
    Level 9  
    Helpful post? (0)
    już 4godz siedze i masakra proszę pomóżcie Otóż mam taki problem a kolumnie a od a1 od a10 wyświetlają się kolory na skutek formatowania warunkowego ( większe o50 w kolorze żółtym a mniejsze o 50 w kolorze niebieskim średnia to 1000
    I wszystko pasuje ale chodzi mi o to ile kolorów tła czyli złotych i niebieskich jest w danym zakresie np. w kolumnie a1 do a10 jest 2żółte i 1 niebieski wynik 3 i z tym mam problem Prosze pomóżcie nie umiem zrobić do tego formuły a jestem na prawdę cienki z excela
  • #26 14 Dec 2013 20:00
    Maciej Gonet
    Level 30  
    Helpful post? (0)
    Trudno jest w Excelu zliczać ex post kolory nadane za pomocą formatowania warunkowego, bo w Visual Basicu nie ma dostępu do odpowiednich danych. Jedyna praktyczna droga to odwołać się do formuł użytych w formatowaniu warunkowym i zliczyć odpowiednimi formułami np. LICZ.JEŻELI, ile razy warunek był spełniony. Bardziej konkretna pomoc wymaga załącznika.
  • #27 15 Dec 2013 15:34
    maryna 76
    Level 9  
    Helpful post? (0)
    Maciej masz racje okazuje się że faktycznie droga przez mękę z kolorami obeszłam to na zasadzie tak, nie i zliczanie działa 100%
  • #28 23 Apr 2014 14:26
    Lucys
    Level 1  
    Helpful post? (0)
    Witam,

    proszę o podpowiedź: co jeśli chciałabym zastosować formułę iloczynu dwóch liczb, jeżeli dana kolumna jest np. koloru zielonego, czy jest na to sposób?
  • #29 16 May 2014 08:10
    serniq
    Level 2  
    Helpful post? (+2)
    https://support.microsoft.com/kb/2815384 all you need is there
  Search 4 million + Products
Browse Products