Când vedeți rezultatul formatării condiționate, probabil că doriți să creați formule care se referă numai la date care au fost formate condiționate. În Excel, această opțiune nu este foarte bine implementată, dar o vom ajuta.
Utilizatorii Excel întreabă în mod regulat cum este posibilă efectuarea calculelor numai cu celule de o anumită culoare de fundal. Această problemă apare atât de des, deoarece Excel nu oferă o funcție standard pentru această sarcină; Cu toate acestea, acest lucru se poate face folosind o funcție definită de utilizator, așa cum demonstrează Trick No. 88 "Numărarea sau însumarea celulelor cu o anumită culoare de umplere".
Singura problemă cu utilizarea unei funcții personalizate este aceea că nu înțelege formatul impus de formatarea condiționată. Cu toate acestea, după un mic gând, puteți obține rezultatul dorit fără a aplica o funcție personalizată.
Să fie o listă lungă de numere în intervalul $ A $ 2: $ A $ 100. Ați aplicat formatarea condiționată acestor celule astfel încât să fie etichetate numai numerele între 10 și 20. Acum trebuie să adăugați valorile în celulele care îndeplinesc criteriul stabilit și apoi să selectați suma valorilor utilizând formatarea condiționată. Nu contează ce fel de formatare este aplicată acestor celule, cu toate acestea, trebuie să cunoască criteriile în funcție de care sunt evidențiate celulele (în acest caz, cu condiția ca valoarea este între 10 și 20).
Pentru a adăuga o gamă de celule care îndeplinesc un anumit criteriu, puteți utiliza funcția SUMIF. dar puteți specifica doar un criteriu. Dacă aveți mai mulți factori de validare, puteți utiliza formula matrice. Formula matricei poate fi scrisă ca:
= SUM (DACA ($ A $ 2: $ A $ 100> 10; IF ($ A $ 2: $ A $ 100<20;$A$2:$A$100)))
În versiunea rusă a Excel
= SUM (DACA ($ A $ 2: $ A $ 100> 10; IF ($ A $ 2: $ A $ 100<20;$А$2:$А$100)))
Căi alternative
Alternativ, puteți utiliza o coloană suplimentară (de exemplu, coloana B) pentru a face referire la celulele din coloana A. Aceste linkuri vor returna rezultatul în coloana B numai dacă valoarea corespunde condiției specificate, adică> 10, <20. Для этого сделайте следующее:
Selectați celula B1 și introduceți următoarea formulă:
= DACĂ (AND (A2> 10; A2<20);A2;"")
În versiunea rusă a Excel
= DACĂ (AND (A2> 10; A2<20);А2;"")
Înregistrați această formulă în fiecare celulă în celula B100. După aceasta, dacă există valori în coloana A, coloana B trebuie să aibă valori cuprinse între 10 și 20.
Pentru a copia rapid formula în celule adiacente până la ultima linie, introduceți formula din prima celulă (B2), re-selectați această celulă și faceți dublu clic pe marcatorul de umplere. Acest lucru se poate face selectând Edit → Fill → Down.
Acum, selectați orice celulă în care ar trebui să apară suma și utilizați-o pentru a adăuga o funcție SUM standard. (Puteți ascunde coloana B dacă nu doriți să vedeți o coloană suplimentară cu valorile returnate de formula.)
Ambele metode anterioare funcționează bine pentru sarcină, dar Excel oferă o funcție suplimentară care vă permite să specificați mai multe condiții. Această funcție este inclusă în setul de funcții ale bazelor de date Excel și se numește BSSUMM (DSUM).
Pentru a le testa, folosiți același set de numere în intervalul A2: A100 e celule C1: D2 și atribuiți acest nume SumCriteria. tastând-o în câmpul de nume din stânga liniei de formula. Acum, selectați celula C1 și introduceți = $ A $ 1. adică, o referință la prima celulă din foaie. Copiați același lucru în celula D1 și veți primi două copii ale coloanei antet A. Aceste copii vor fi folosite ca anteturi pentru condițiile DSUM (C1: D2). pe care l-ați numit SumCriteria.
În celula C2, introduceți> 10. În celula D2, tastați <20. В ячейке, где должен быть результат, введите следующий код:
= DSUM ($ A $ 1: $ A $ 100; $ A $ 1; SumCriteria)
În versiunea rusă a Excel
= BDUUMM ($ A $ 1: $ A $ 100; $ A $ 1; SumCriteria)
DSUMM (DSUM) este modalitatea preferată și cea mai eficientă de a lucra cu celule care îndeplinesc un anumit criteriu. Spre deosebire de arrays, funcțiile bazelor de date încorporate sunt concepute special în acest scop și chiar și atunci când se referă la o gamă foarte largă și sunt utilizate în număr mare, impactul lor negativ asupra vitezei de conversie și a eficienței este destul de mic în comparație cu formulele de matrice.
Articole similare
Trimiteți-le prietenilor: