Cum să numeri casetele de selectare în Excel – o metodă care chiar funcționează

Imaginați-vă un tabel de urmărire a proiectelor cu 60 de sarcini, fiecare având o casetă de selectare pe care cineva o bifează pe măsură ce sarcina este finalizată. Managerul dorește să vadă un singur număr în partea de sus: câte dintre ele sunt efectiv finalizate. Așa că introduce o formulă COUNTIF, o direcționează către casetele de selectare și primește ca rezultat zero. De fiecare dată. Chiar și atunci când jumătate dintre casete sunt clar bifate.

Iată partea pe care aproape orice ghid o omite. O casetă de selectare afișată într-o foaie de calcul nu reprezintă date — este un obiect flotant, precum o formă sau o imagine, iar o formulă nu o poate citi. Pentru ca orice numărare să funcționeze, fiecare casetă de selectare trebuie să fie legată de o celulă, astfel încât, atunci când este bifată, să scrie ceva lizibil sub ea. Acesta este adevăratul obstacol. De aceea sfaturile legate de COUNTIF nu funcționează pentru majoritatea persoanelor care caută cum să numere casetele de selectare în Excel și apoi se întorc imediat la Google.

Odată ce legăturile sunt stabilite, numărarea propriu-zisă durează aproximativ zece secunde. Așadar, să rezolvăm mai întâi condiția prealabilă, apoi să aplicăm formula.

De ce sfaturile obișnuite nu dau rezultate

Majoritatea articolelor încep cu această formulă. „Tastați =COUNTIF(B2:B60,TRUE) ”, spun ele, și trec mai departe, presupunând că casetele alimentează deja o celulă. Pentru o mică parte dintre cititori, care își configurează totul cu atenție, este în regulă. Toți ceilalți nu primesc decât un zero sec și nicio explicație.

Motivul este de natură structurală. O casetă de selectare din Form Control sau o casetă de selectare ActiveX se află într-un strat de desen situat deasupra grilei. Aceasta are o stare vizuală – bifată sau debifată –, dar această stare este asociată obiectului, nu unei celule anume la care formula ta ar putea face referire. Excel tratează totul ca pe un element decorativ până când îi indici altfel.

Versiunile mai noi complică și mai mult lucrurile. Excel pentru Microsoft 365 a introdus în 2024 o casetă de selectare nativă care se inserează direct într-o celulă și stochează valorile TRUE sau FALSE chiar acolo, fără a fi necesară vreo legătură. Însă milioane de registre de lucru utilizează încă casetele de control de tip formular mai vechi, iar mulți utilizatori folosesc Excel 2019 sau 2021, unde opțiunea nativă nu există. Ideea este că, dacă casetele dvs. au fost adăugate în modul vechi sau au fost moștenite dintr-un fișier al altcuiva, legarea nu este opțională.

Asocierea unei casete de selectare cu o celulă, astfel încât aceasta să returneze TRUE sau FALSE

Acesta este pasul care face ca întreaga abordare cu casetele de selectare din Excel (adevărat/fals) să funcționeze. Când o casetă de selectare asociată este bifată, celula țintă afișează „TRUE”. Dacă nu este bifată, afișează „FALSE”. Aceste valori sunt cele pe care le ia în calcul formula.

Iată cum se poate lega o casetă de selectare la o celulă din Excel pentru o singură casetă de control din formular:

1. Faceți clic dreapta pe caseta de selectare și alegeți „Formatare control”.
2. Deschideți fila „Control ”.
3. În câmpul „Legătură celulă ”, faceți clic pe celula în care doriți să fie înscris rezultatul (de obicei, celula situată imediat în spatele sau lângă casetă).
4. Faceți clic pe OK.

Bifează caseta acum și celula asociată va afișa valoarea TRUE. Debifeaz-o și vei obține valoarea FALSE. Repetă această operațiune pentru fiecare casetă de selectare, asociind fiecare cu propriul rând, astfel încât rezultatele să se alinieze într-o coloană ordonată.

Ghidul pas cu pas al site-ului How-To Geek privind numărarea casetelor de selectare în Microsoft Excel confirmă aceeași secvență: mai întâi se creează legătura, apoi se efectuează numărarea. Omiterea legăturii este cea mai frecventă greșeală, iar acesta este motivul pentru care ghidurile care pun formula pe primul plan îi fac pe utilizatori să se învârtă în cerc.

Metoda „în bloc” pentru zeci de casete de selectare

Conectarea a 60 de celule, una câte una, prin intermediul funcției „Format Control” este o adevărată pacoste. Nimeni nu vrea să dea clic dreapta de șaizeci de ori. Există două metode mai rapide, în funcție de modul în care este structurată foaia de calcul.

Dacă casetele de selectare sunt deja aliniate ordonat într-o coloană, cea mai rapidă metodă este să conectați manual prima dintre ele, apoi să continuați în jos. Doar că Excel nu va incrementa automat legăturile dintre celule atunci când copiați o casetă de control de formular – ceea ce înseamnă că, de obicei, conectarea în bloc se realizează cu ajutorul unei macrocomenzi scurte.

Deschideți editorul VBA apăsând Alt + F11, inserați un modul și lipiți un cod similar cu acesta:

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

Rulați-o o singură dată și fiecare casetă de selectare din Form Control va fi asociată cu celula situată cu o coloană mai la dreapta. Modificați funcția „ Offset(0, 1) ” dacă doriți ca rezultatul să apară în altă parte: „ Offset(0, -1) ” scrie în stânga, iar „ Offset(0, 0) ” scrie în celula situată imediat în spatele casetei. Ghidul ExtendOffice privind totalizarea și numărarea casetelor de selectare bifate prezintă o abordare similară bazată pe macrocomenzi pentru foi de calcul mai mari.

Creați trackerul de la zero în Microsoft 365? Săriți peste toate aceste etape. Selectați intervalul dorit, accesați meniuul Insert (Inserați) > (Casetă de selectare), iar Excel va insera casete de selectare native care conțin deja valorile TRUE/FALSE în celule. Fără legături, fără macro. Tutorialul DataCamp privind adăugarea, utilizarea și numărarea casetelor de selectare prezintă în detaliu versiunea nativă.

Cum se numără casetele de selectare în Excel cu funcția COUNTIF

Acum urmează partea ușoară. Având coloana de legături plină de valori TRUE și FALSE, numărarea se face cu o singură formulă.

Pentru a număra casetele de selectare bifate, Excel nu are nevoie de nimic mai sofisticat decât:

=COUNTIF(B2:B60,TRUE)

Aceasta returnează numărul de casete bifate. Înlocuiește TRUE cu FALSE pentru a număra cele nebifate:

=COUNTIF(B2:B60,FALSE)

Vrei să afli procentajul de finalizare pentru acel instrument de urmărire a proiectelor? Împarte numărul elementelor bifate la total:

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

Formatează rezultatul ca procent și vei obține o afișare în timp real a progresului, care se actualizează imediat ce cineva bifează o casetă. Modelul de utilizare a funcției COUNTIF cu casetele de selectare este într-adevăr atât de simplu, odată ce valorile care trebuie numărate există deja.

O precizare privind tipurile de date. Legăturile din Form Control înregistrează cuvintele „TRUE” și „FALSE” ca valori booleene, astfel încât formula „ COUNTIF(range,TRUE) ” funcționează corect. Însă, dacă o celulă legată afișează textul „ "TRUE" ” între ghilimele, în loc de o valoare booleană reală, numărarea poate da rezultate eronate. În acest caz, fie corectați legătura, fie potriviți explicit forma textului. Rețeta din „Excel Cookbook” pentru numărarea casetelor de selectare bifate oferă o explicație detaliată a diferenței dintre valorile booleene și text.

Rezolvarea problemelor frecvente

Există câteva aspecte care pot crea confuzie, chiar și atunci când legăturile par corecte.

Funcția COUNTIF afișează în continuare zero. Cel mai probabil, casetele nu au fost niciodată legate între ele sau macro-ul a direcționat legăturile către coloana greșită. Fă clic pe o casetă de selectare, bifeaz-o și observă dacă vreo celulă din apropiere se schimbă în TRUE. Nu se schimbă nimic? Înseamnă că legătura nu este configurată.

Numărul este cu câteva mai mic. Verificați dacă există casete de selectare ascunse sau duplicate, suprapuse una peste alta. Dacă trageți ușor de o casetă, se poate descoperi o a doua casetă ascunsă sub ea, iar ambele pot fi legate de aceeași celulă, ceea ce duce la înregistrarea dublă a valorii.

Casete de control native și de formular amestecate între ele. Dacă cineva a adăugat câteva casete de selectare native 365 alături de cele mai vechi din Form Control, coloana cu linkuri ar putea conține o combinație de valori booleene în celule și celule legate separat. Standardizați-le la un singur tip înainte de a efectua numărarea, altfel intervalul dvs. nu va fi consecvent.

Casete copiate fără linkurile lor. Copierea unei casete de selectare dintr-un control de formular legat păstrează legătura inițială, astfel încât toate cele cinci copii pot scrie în aceeași celulă. Rulați din nou macrocomanda de legare după orice operațiune de copiere-lipire în bloc pentru a reseta fiecare casetă la propriul rând.

Întrebări frecvente

De ce formula COUNTIF returnează zero, deși casetele sunt clar bifate?
Deoarece casetele de selectare nu sunt legate de celule. O casetă de selectare flotantă își păstrează starea de selectare ca proprietate a obiectului, nu ca date pe care o formulă le poate citi. Leagă mai întâi fiecare casetă de o celulă, astfel încât aceasta să conțină valoarea TRUE sau FALSE; abia atunci funcția COUNTIF va avea valori reale pe care să le numere.

Am nevoie de un link în Excel pentru Microsoft 365?
Nu. Caseta de selectare nativă adăugată în 2024 stochează valorile TRUE sau FALSE direct în celula sa, astfel încât puteți aplica imediat funcția COUNTIF asupra intervalului respectiv. Legarea este necesară doar pentru casetele de selectare mai vechi de tip Form Control și ActiveX.

Pot număra casetele de selectare fără să folosesc vreo formulă?
Poți face asta, aproximativ, folosind un filtru sau analizând cu ochiul liber coloana cu linkuri, dar asta contrazice scopul. Un singur =COUNTIF(range,TRUE) oferă o valoare actualizată în timp real și precisă, care se actualizează automat, ceea ce filtrarea manuală nu face.

Care este cea mai rapidă metodă de a lega 50 sau mai multe casete de selectare?
O macro VBA scurtă care parcurge toate casetele de selectare și setează fiecare legătură către o celulă adiacentă. Aceasta procesează zeci de casete într-o singură execuție, spre deosebire de a face clic dreapta pe „Formatare control” de cincizeci de ori separat.

Funcția COUNTIF funcționează în schimb cu SUM?
Da. Dacă celulele legate conțin valorile TRUE/FALSE, poți folosi și funcția „ =SUMPRODUCT(--(B2:B60=TRUE)) ” pentru aceeași numărătoare, pe care unii o preferă pentru a o combina cu alte condiții.

Încheiere

Întreaga sarcină se reduce la o secvență pe care majoritatea ghidurilor o prezintă în ordine inversă. Casetele de selectare sunt obiecte, nu date, până când nu le asociați pe fiecare cu o celulă care returnează TRUE sau FALSE. Odată ce acea coloană există – creată manual, cu ajutorul unei macrocomenzi sau prin intermediul casetei de selectare native din Excel 365 – o simplă formulă „ =COUNTIF(range,TRUE) ” numără casetele bifate în câteva secunde. Îndeplinește condiția prealabilă, iar formula la care toată lumea apelează în primul rând își face în sfârșit treaba. Așa se numără casetele de selectare în Excel fără a le bifa manual pe fiecare în parte.