Bill a trimis întrebarea Excel din această săptămână.
Am o bază de date cu evenimente în Excel și șeful meu vrea să trasez diagrame de frecvență pe lună. Am citit trucul dvs. pentru a schimba datele zilnice în date lunare și despre formulele Excel CSE. Am încercat fiecare criteriu la care mă pot gândi în formula Excel CountIf de mai jos pentru a face să se uite la 2 criterii.
Situația dvs. ar putea fi rezolvată cu ușurință cu ajutorul unui tabel pivot (XL95-XL2000) sau al unei diagrame pivot (numai XL2000). Deocamdată, să abordăm întrebarea pe care ați pus-o. În stânga este foaia dvs. de lucru. Se pare că veți dori să introduceți formule în celulele B4406: D4415 pentru a calcula numărul anumitor evenimente în fiecare lună.
Funcția CountIf este o formă specializată a unei formule matrice, care este excelentă atunci când aveți un singur criteriu. Nu funcționează bine atunci când aveți mai multe criterii. Următoarele formule eșantion ar număra numărul de rânduri cu Rain și numărul de evenimente din ianuarie 97:
=COUNTIF(B2:B4403,"=Rain")
=COUNTIF(A2:A4403,"="&A4406)
Nu există nicio modalitate de a folosi CountIf pentru a obține intersecția a două condiții.
Pentru orice cititor care nu este familiarizat cu modul de introducere a formulelor matrice, vă recomand cu tărie să revizuiți Folosirea formulelor CSE pentru a supraîncărca Excel.
Bill nu a spus-o în întrebarea sa, dar vreau să construiesc o formulă pe care să o poată introduce o singură dată în celula B4406 care poate fi copiată cu ușurință în celelalte celule din gama sa. Utilizând referințe absolute și mixte în formulă, puteți salva dificultatea de a introduce o nouă formulă pentru fiecare intersecție.
Iată o revizuire rapidă a formulelor absolute, relative și mixte. În mod normal, dacă introduceți o formulă ca =SUM(A2:A4403)în D1 și apoi copiați formula în E2, formula dvs. din E2 se va schimba în =SUM(B3:C4403). Aceasta este o caracteristică interesantă a foilor de lucru numite „adresare relativă”, dar uneori nu vrem să se întâmple asta. În acest caz, dorim ca fiecare formulă să se refere la intervalul A2: B4403. Pe măsură ce copiem formula de la celulă la celulă, ar trebui să indice întotdeauna spre A2: B4403. În timp ce introduceți formula, apăsați F4 o dată după introducerea intervalului, iar formula dvs. se va schimba în=SUM($A$2:$A$4403). Semnul de dolar indică faptul că acea porțiune de referință nu se va modifica pe măsură ce copiați formula. Aceasta se numește adresare absolută. Este posibil să blocați doar coloana cu $ și să permiteți rândul să fie relativ. Aceasta se numește o referință mixtă și ar fi introduse ca =$A4406. Pentru a bloca rândul, dar permiteți coloanei să fie relativă, utilizați =B$4405. Pe măsură ce introduceți o formulă, utilizați F4 pentru a comuta între cele patru arome de referințe relative, absolute și mixte.
Iată formula pentru celula B4406:
=SUM(IF($C$2:$C$4403=$A4406,IF($B$2:$B$4403=B$4405,1,0),0))
Tastați formula. Când terminați formula, țineți apăsat Ctrl, Shift și apoi introduceți. Acum puteți copia formula în C4406: D4406 și apoi copiați aceste trei celule în jos pe fiecare rând din tabelul de rezultate.
Formula folosește toate cele trei forme de referințe mixte și absolute. Cuibărește 2 dacă instrucțiunile, deoarece funcția ȘI () nu pare să funcționeze într-o formulă matrice. Pentru o explicație mai bună a ceea ce se întâmplă cu funcționalitatea matricei, citiți din nou Utilizați formulele CSE pentru a supraîncărca Excel menționat mai sus.
