Jak policzyć pola wyboru w programie Excel, tak aby to faktycznie działało

Wyobraź sobie narzędzie do śledzenia projektu zawierające 60 zadań, z których każde ma pole wyboru, które ktoś zaznacza po wykonaniu zadania. Kierownik chce widzieć na górze jedną liczbę: ile zadań jest faktycznie ukończonych. Wpisuje więc formułę COUNTIF, kieruje ją na pola wyboru i otrzymuje wynik zero. Za każdym razem. Nawet jeśli połowa pól jest wyraźnie zaznaczona.

Oto kwestia, którą pomija niemal każdy poradnik. Pole wyboru umieszczone w arkuszu nie jest danymi – to obiekt swobodnie umieszczony, podobnie jak kształt czy obrazek, a formuła nie może go odczytać. Zanim jakiekolwiek liczenie zacznie działać, każde pole wyboru musi być powiązane z komórką, tak aby po zaznaczeniu w nim pojawił się pod nim czytelny wpis. To właśnie jest prawdziwa przeszkoda. Dlatego porady dotyczące funkcji COUNTIF nie sprawdzają się w przypadku większości osób, które szukają sposobu na zliczenie pól wyboru w Excelu, a następnie natychmiast wracają do Google.

Gdy już uporamy się z połączeniami, samo zliczanie zajmuje około dziesięciu sekund. Zajmijmy się więc najpierw spełnieniem warunku wstępnego, a potem zastosujmy wzór.

Dlaczego typowe porady nie działają

Większość artykułów zaczyna się od tego samego schematu. „Wpisz =COUNTIF(B2:B60,TRUE) ” – mówią – i przechodzą dalej, zakładając, że pola te już zawierają dane w komórkach. Dla niewielkiej grupy czytelników, którzy wszystko starannie konfigurują, to w porządku. Wszyscy inni otrzymują po prostu zero i żadnego wyjaśnienia.

Powód ma charakter strukturalny. Pole wyboru z kontrolki Form Control lub pole wyboru ActiveX znajduje się w warstwie rysunkowej nad siatką. Ma ono stan wizualny — zaznaczone lub niezaznaczone — ale stan ten jest związany z samym obiektem, a nie z żadną komórką, na którą może wskazywać formuła. Program Excel traktuje całość jako element dekoracyjny, dopóki nie wskażesz mu inaczej.

Nowsze wersje jeszcze bardziej komplikują tę sprawę. W 2024 roku w programie Excel dla Microsoft 365 dodano natywne pole wyboru, które wstawia się bezpośrednio do komórki i zapisuje w niej wartość TRUE lub FALSE – nie trzeba tworzyć żadnych powiązań. Jednak miliony skoroszytów nadal korzystają ze starszych pól kontrolnych typu „Form Control”, a wiele osób korzysta z programu Excel 2019 lub 2021, w których ta natywna opcja nie istnieje. Chodzi o to, że jeśli pola zostały dodane w starym stylu lub zostały odziedziczone z pliku innej osoby, powiązanie nie jest opcjonalne.

Powiąż pole wyboru z komórką, tak aby zwracało wartość TRUE lub FALSE

To właśnie ten krok sprawia, że cała metoda z polami wyboru w programie Excel (prawda/fałsz) działa. Gdy zaznaczone jest powiązane pole wyboru, w komórce docelowej pojawia się wartość TRUE. Gdy pole jest niezaznaczone — FALSE. To właśnie te wartości są brane pod uwagę przez formułę.

Oto jak powiązać pole wyboru z komórką w programie Excel w przypadku pojedynczego pola kontrolnego formularza:

1. Kliknij prawym przyciskiem myszy pole wyboru i wybierz opcję „Formatuj kontrolkę”.
2. Otwórz kartę „Sterowanie ”.
3. W polu „Powiązanie z komórką ” kliknij komórkę, do której ma zostać zapisany wynik (często jest to komórka znajdująca się bezpośrednio za lub obok pola).
4. Kliknij „OK”.

Zaznacz teraz to pole wyboru, a wartość w powiązanej komórce zmieni się na TRUE. Odznacz je, a pojawi się FALSE. Powtórz tę czynność dla każdego pola wyboru, przypisując każde z nich do własnego wiersza, tak aby wyniki układały się w przejrzystą kolumnę.

Poradnik serwisu How-To Geek dotyczący zliczania pól wyboru w programie Microsoft Excel potwierdza tę samą kolejność: najpierw utworzyć odwołanie, potem zliczyć. Pominięcie utworzenia odwołania jest najczęstszym błędem i właśnie dlatego poradniki, które zaczynają od formuł, wprowadzają użytkowników w błąd.

Metoda zbiorcza dla kilkudziesięciu pól wyboru

Łączenie 60 komórek pojedynczo za pomocą funkcji „Kontrola formatowania” to prawdziwa udręka. Nikt nie chce klikać prawym przyciskiem myszy sześćdziesiąt razy. Istnieją dwa szybsze sposoby, w zależności od tego, jak zbudowany jest arkusz.

Jeśli pola wyboru są już starannie ułożone w kolumnie, najszybszym sposobem jest ręczne utworzenie odnośnika do pierwszego z nich, a następnie kontynuowanie w dół. Problem polega jednak na tym, że program Excel nie zwiększa automatycznie numeracji odnośników do komórek podczas kopiowania pola kontrolnego formularza — co oznacza, że prawdziwe masowe tworzenie odnośników zazwyczaj wymaga użycia krótkiego makra.

Otwórz edytor VBA za pomocą skrótu klawiszowego Alt + F11, wstaw moduł i wklej następujący kod:

Sub LinkCheckboxes()
    Dim cb As CheckBox
    For Each cb In ActiveSheet.CheckBoxes
        cb.LinkedCell = cb.TopLeftCell.Offset(0, 1).Address
    Next cb
End Sub

Uruchom to makro raz, a każde pole wyboru w kontrolce formularza zostanie powiązane z komórką znajdującą się o jedną kolumnę dalej w prawo. Jeśli chcesz uzyskać wynik w innym miejscu, dostosuj wartość Offset(0, 1): Offset(0, -1) zapisuje wynik po lewej stronie, a Offset(0, 0) — w komórce bezpośrednio za polem wyboru. Poradnik ExtendOffice dotyczący sumowania i zliczania zaznaczonych pól wyboru omawia podobne podejście oparte na makrach w przypadku większych arkuszy.

Tworzysz narzędzie do śledzenia od podstaw w Microsoft 365? Pomiń wszystkie poniższe instrukcje. Zaznacz zakres komórek, przejdź do menu Wstaw > Pole wyboru „ > ”, a program Excel umieści natywne pola wyboru, w których komórkach znajdują się już wartości TRUE/FALSE. Bez łączenia, bez makr. Samouczek DataCamp dotyczący dodawania, używania i zliczania pól wyboru szczegółowo omawia działanie tej natywnej wersji.

Jak policzyć pola wyboru w programie Excel za pomocą funkcji COUNTIF

A teraz najłatwiejsza część. Gdy kolumna z linkami zawiera same wartości TRUE i FALSE, wystarczy jedna formuła, by je zliczyć.

Aby zliczyć zaznaczone pola wyboru, program Excel nie potrzebuje niczego bardziej skomplikowanego niż:

=COUNTIF(B2:B60,TRUE)

Zwraca to liczbę zaznaczonych pól. Aby policzyć pola niezaznaczone, należy zamienić wartość TRUE na FALSE:

=COUNTIF(B2:B60,FALSE)

Chcesz obliczyć procent realizacji w tym narzędziu do śledzenia projektów? Podziel liczbę zaznaczonych pozycji przez liczbę całkowitą:

=COUNTIF(B2:B60,TRUE)/COUNTA(B2:B60)

Wystarczy sformatować wynik jako wartość procentową, a otrzymasz aktualizowany na bieżąco wskaźnik postępu, który zmienia się w chwili, gdy ktoś zaznaczy pole wyboru. Schemat wykorzystania funkcji COUNTIF w połączeniu z polami wyboru jest naprawdę tak prosty, o ile istnieją wartości do zliczenia.

Jedna uwaga dotycząca typów danych. Powiązania w kontrolkach formularza zapisują dosłowne słowa „TRUE” i „FALSE” jako wartości logiczne, więc formuła „ COUNTIF(range,TRUE) ” działa bez zarzutu. Jeśli jednak komórka, z którą istnieje powiązanie, zawiera tekst „ "TRUE" ” w cudzysłowie zamiast rzeczywistej wartości logicznej, zliczanie może dać błędny wynik. W takim przypadku należy albo poprawić powiązanie, albo jawnie dopasować formę tekstową. Przepis z książki „Excel Cookbook” dotyczący zliczania zaznaczonych pól wyboru zawiera dobre omówienie pułapki związanej z różnicą między wartościami logicznymi a tekstem.

Rozwiązywanie typowych problemów

Jest kilka rzeczy, które sprawiają ludziom trudności, nawet jeśli link wygląda na poprawny.

Funkcja COUNTIF nadal zwraca zero. Najprawdopodobniej pola te nigdy nie zostały faktycznie połączone albo makro skierowało odnośniki do niewłaściwej kolumny. Kliknij pole wyboru, zaznacz je i sprawdź, czy wartość w którejś z sąsiednich komórek zmieni się na TRUE. Nic się nie zmienia? Oznacza to, że odnośnik nie został ustawiony.

W liczbie jest kilka różnic. Sprawdź, czy nie ma ukrytych lub zduplikowanych pól wyboru ułożonych jedno nad drugim. Lekkie przesunięcie jednego pola może odsłonić drugie, ukryte pod nim, a oba mogą odnosić się do tej samej komórki, co spowoduje podwójne wpisanie tej samej wartości.

Pola tekstowe typu „Native” i „Form Control” występują razem. Jeśli ktoś dodał kilka natywnych pól wyboru z modułu 365 obok starszych pól wyboru z modułu Form Control, kolumna z linkami może zawierać zarówno wartości logiczne w komórkach, jak i komórki z osobnymi linkami. Przed rozpoczęciem liczenia należy ujednolicić typ danych, w przeciwnym razie zakres nie będzie spójny.

Pola skopiowano bez linków. Skopiowanie pola wyboru powiązanego z kontrolką formularza powoduje zachowanie oryginalnego powiązania, więc pięć kopii może zapisywać dane do tej samej komórki. Po wykonaniu zbiorczej operacji kopiowania i wklejania należy ponownie uruchomić makro łączące, aby przywrócić każde pole wyboru do osobnego wiersza.

Najczęściej zadawane pytania

Dlaczego formuła COUNTIF zwraca zero, skoro pola są wyraźnie zaznaczone?
Ponieważ pola wyboru nie są powiązane z komórkami. Pływające pole wyboru przechowuje informację o tym, czy jest zaznaczone, jako właściwość obiektu, a nie jako dane, które formuła może odczytać. Najpierw powiąż każde pole z komórką, tak aby zapisywało wartość TRUE lub FALSE – wtedy funkcja COUNTIF będzie miała rzeczywiste wartości do zliczenia.

Czy w programie Excel dla Microsoft 365 w ogóle potrzebuję linku?
Nie. Pole wyboru typu „native”, dodane w 2024 r., przechowuje wartości TRUE lub FALSE bezpośrednio w swojej komórce, więc można od razu zastosować funkcję COUNTIF do tego zakresu. Tworzenie odnośników jest wymagane tylko w przypadku starszych pól wyboru typu „Form Control” i „ActiveX”.

Czy mogę zliczyć pola wyboru bez użycia formuł?
Można to zrobić w przybliżeniu, korzystając z filtra lub oceniając na oko zawartość kolumny „link”, ale to mija się z celem. Pojedynczy adres =COUNTIF(range,TRUE) zapewnia aktualną i dokładną liczbę, która odświeża się automatycznie, czego nie zapewnia ręczne filtrowanie.

Jaki jest najszybszy sposób na połączenie 50 lub więcej pól wyboru?
Krótkie makro VBA, które przechodzi cyklicznie przez wszystkie pola wyboru i ustawia każdy z nich tak, by odnosił się do sąsiedniej komórki. Obsługuje dziesiątki pól wyboru za jednym razem, co stanowi alternatywę dla pięćdziesięciu oddzielnych kliknięć prawym przyciskiem myszy w opcji „Formatuj kontrolkę”.

Czy funkcja COUNTIF działa w połączeniu z funkcją SUM?
Tak. Jeśli powiązane komórki zawierają wartości TRUE/FALSE, do tego samego podsumowania można również użyć funkcji ` =SUMPRODUCT(--(B2:B60=TRUE)) `, którą niektórzy wolą stosować w połączeniu z innymi warunkami.

Podsumowanie

Całe zadanie sprowadza się do sekwencji, którą większość poradników przedstawia w odwrotnej kolejności. Pola wyboru są obiektami, a nie danymi, dopóki nie powiążesz każdego z nich z komórką zwracającą wartość TRUE lub FALSE. Gdy taka kolumna już istnieje – utworzona ręcznie, za pomocą makra lub przy użyciu natywnego pola wyboru w Office 365 – prosta formuła =COUNTIF(range,TRUE) zlicza zaznaczone pola w ciągu kilku sekund. Wystarczy spełnić ten warunek wstępny, a formuła, po którą wszyscy sięgają w pierwszej kolejności, w końcu spełni swoje zadanie. Oto jak zliczyć pola wyboru w programie Excel bez ręcznego zaznaczania każdego z nich.