Excel

Bilangin ang mga natatanging halaga ng teksto na may pamantayan

Count Unique Text Values With Criteria

Formula ng Excel: Bilangin ang mga natatanging halaga ng teksto na may pamantayanGenerikong pormula
{= SUM (--( FREQUENCY ( IF (criteria, MATCH (vals,vals,0)), ROW (vals)- ROW (vals.first)+1)>0))}
Buod

Upang mabilang ang mga natatanging halaga ng teksto sa isang saklaw na may pamantayan, maaari kang gumamit ng isang formula ng array batay sa FREQUENCY at MATCH pagpapaandar Sa halimbawang ipinakita, ang pormula sa G6 ay:





 
{= SUM (--( FREQUENCY ( IF (C5:C11=G5, MATCH (B5:B11,B5:B11,0)), ROW (B5:B11)- ROW (B5)+1)>0))}

na nagbabalik ng 3, dahil tatlong magkakaibang tao ang nagtrabaho sa proyekto na Omega.

Tandaan: ito ay isang formula ng array at dapat na ipinasok na may control + shift + enter.





Kasama si Excel 365 , maaari mong gamitin ang a mas simpleng formula batay sa Natatanging pagpapaandar . Paliwanag

Ito ay isang kumplikadong pormula na gumagamit ng FREQUENCY upang mabilang ang mga halagang bilang ayon sa numero na nakuha sa pagpapaandar ng MATCH. Paggawa mula sa loob palabas, ang function na MATCH ay ginagamit upang makuha ang posisyon ng bawat halaga na lilitaw sa data:

 
 MATCH (B5:B11,B5:B11,0)

Ang resulta mula sa MATCH ay isang array na tulad nito:



 
{1131167}

Palaging ibinabalik ng MATCH ang posisyon ng una tugma, mga halagang lumilitaw nang higit sa isang beses sa data ay bumalik sa parehong posisyon. Halimbawa, dahil lumitaw ang 'Jim' ng 4 na beses sa listahan, nagpapakita siya sa array na ito ng 4 na beses bilang bilang 1.

Sa labas ng pagpapaandar ng MATCH, ang KUNG pagpapaandar ay ginagamit upang mag-apply ng mga pamantayan, na sa kasong ito ay nagsasangkot ng pagsubok kung ang proyekto ay 'omega' (mula sa cell G5):

 
 IF (C5:C11=G5 // filter on 'omega'

Ang pag-andar ng IF ay gumaganap tulad ng isang filter, pinapayagan lamang ang mga halaga mula sa MATCH na dumaan kung nauugnay sila sa 'omega'. Ang resulta ay isang array na tulad nito:

 
{FALSEFALSEFALSE1167} // after filtering

Ang na-filter na array ay ihinahatid nang direkta sa pagpapaandar na FREQUENCY bilang data_array pagtatalo Susunod, ang ROW na pag-andar ginagamit sa pagbuo ng a sunud-sunod na listahan ng mga numero para sa bawat halaga sa data:

 
 ROW (B3:B12)- ROW (B3)+1

Lumilikha ito ng isang array na tulad nito:

 
{12345678910}

na nagiging ang bins_array pagtatalo sa FILTER. Sa puntong ito, mayroon kaming:

 
 FREQUENCY ({FALSEFALSEFALSE1167},{1234567})

Ang FREQUENCY ay nagbabalik ng isang hanay ng mga numero na nagpapahiwatig ng isang bilang para sa bawat halaga sa data array, na inayos ayon sa basurahan. Kapag nabilang na ang isang numero, ang FREQUENCY ay babalik sa zero. Ang resulta mula sa FREQUENCY ay isang array tulad nito:

 
{20000110} // result from FREQUENCY

Tandaan: Laging nagbabalik ang FREQUENCY ng isang array na may isa pang item kaysa sa bins_array .

gamit ang excel upang mahanap standard lihis

Sa puntong ito, maaari naming muling isulat ang pormulang tulad nito:

 
= SUM (--({20000110}>0))

Sinusuri namin ang mga halagang higit sa zero, na nagko-convert sa mga numero sa TUNAY o MALI:

 
= SUM (--({TRUEFALSEFALSEFALSEFALSETRUETRUEFALSE}))

Pagkatapos ay gumagamit kami ng a doble-negatibo upang pilitin ang mga lohikal na halaga sa 1s at 0:

 
= SUM ({10000110})

Panghuli, ang Pagpapaandar ng SUM nagbabalik ng 3 bilang pangwakas na resulta.

Tandaan: ito ay isang array formula at dapat na ipasok gamit ang Control + Shift + Enter.

Pangangasiwa ng walang laman na mga cell sa saklaw

Kung ang anumang mga cell sa saklaw ay walang laman, kakailanganin mong ayusin ang pormula upang maiwasan na maipasa ang mga walang laman na cell sa pagpapaandar ng MATCH, na magtatapon ng isang error. Maaari mo itong gawin sa pamamagitan ng pagdaragdag ng isa pang naka-saladahang pag-andar KUNG upang suriin ang mga blangko na cell:

 
{= SUM (--( FREQUENCY ( IF (B5:B11'', IF (C5:C11=G5, MATCH (B5:B11,B5:B11,0))), ROW (B5:B11)- ROW (B5)+1)>0))}

Na may dalawang pamantayan

Kung mayroon kang dalawang pamantayan, maaari mong palawakin ang lohika ng pormula sa pamamagitan ng pagdaragdag ng isa pang nakapugad KUNG:

 
{= SUM (--( FREQUENCY ( IF (c1, IF (c2, MATCH (vals,vals,0))), ROW (vals)- ROW (vals.1st)+1)>0))}

Kung saan c1 = pamantayan1, c2 = pamantayan2 at waltz = saklaw ng mga halaga.

Sa boolean na lohika

Kasama si lohika ng boolean , maaari mong bawasan nakapugad IFs :

 
{= SUM (--( FREQUENCY ( IF ((criteria1)*(criteria2), MATCH (vals,vals,0)), ROW (vals)- ROW (vals.1st)+1)>0))}

Ginagawa nitong mas madali upang magdagdag at mamahala ng mga karagdagang pamantayan.

Halaw mula sa Mike Givin's mahusay na libro sa mga formula ng array, Control-Shift-Enter. May-akda na si Dave Bruns


^