Cyfrwch y gwerthoedd coll
Gan dybio bod gennych ddwy restr, i gyfrif cyfanswm nifer y gwerthoedd mewn un rhestr nad ydynt yn bodoli mewn rhestr arall, gallwch ddefnyddio a SUMPRODUCT fformiwla gyda chymorth MATCH a swyddogaethau ISNA, neu'r COUNTIF swyddogaeth.
Cyfrwch y gwerthoedd coll gyda SUMPRODUCT, MATCH ac ISNA
Cyfrwch y gwerthoedd coll gyda SUMPRODUCT a COUNTIF
Cyfrwch y gwerthoedd coll gyda SUMPRODUCT, MATCH ac ISNA
I gyfri'r cyfanswm nifer y gwerthoedd yn rhestr B sydd ar goll o restr A fel y dangosir uchod, gallwch yn gyntaf ddefnyddio'r ffwythiant MATCH i ddychwelyd arae o safle cymharol y gwerthoedd o restr B yn rhestr A. Os nad yw gwerth yn bodoli yn rhestr A, dychwelir gwall #N/A. Bydd y ffwythiant ISNA wedyn yn adnabod y # N/A gwallau a bydd SUMPRODUCT yn cyfri cyfanswm y gwallau.
Cystrawen generig
=SUMPRODUCT(--ISNA(MATCH(range_to_count,lookup_range,0)))
- ystod_i_gyfrif: Yr ystod i gyfrif gwerthoedd coll ohono. Yma mae'n cyfeirio at restr B.
- ystod_chwilio: Yr ystod i gymharu â'r ystod_i_gyfrif. Yma yn cyfeirio at y rhestr A.
- 0: Mae adroddiadau math_match 0 gorfodi MATCH i berfformio union baru.
I gyfri'r cyfanswm nifer y gwerthoedd yn rhestr B sydd ar goll o restr A, copïwch neu nodwch y fformiwla isod yn y gell H6, a gwasgwch Rhowch i gael y canlyniad:
=SUMPRODUCT(--ISNA(MATCH(F6: F8,B6: B10, 0)))
Esboniad o'r fformiwla
=SUMPRODUCT(--ISNA(MATCH(F6:F8,B6:B10,0)))
- MATCH(F6:F8,B6:B10,0): Mae adroddiadau math_match 0 gorfodi'r ffwythiant MATCH i ddychwelyd gwerthoedd rhifol sy'n dynodi safleoedd cymharol y gwerthoedd trwy gelloedd F6 i F8 yn yr ystod B6: B10. Os nad yw gwerth yn bodoli yn rhestr A, bydd gwall #D/A yn cael ei ddychwelyd. Felly, bydd y canlyniadau mewn amrywiaeth fel hyn: {2;3;#Amh}.
- ISNA (MATCH(F6:F8,B6:B10,0)) = ISNA ({2;3;#Amh}): Mae ISNA yn gweithio i ddarganfod a yw gwerth yn wall “#N/A” ai peidio. Os oes, bydd y swyddogaeth yn dychwelyd TURE; Os na, bydd yn dychwelyd ANGHYWIR. Felly, bydd fformiwla ISNA yn dychwelyd {FALSE;FALSE;TRUE}.
- SUMPRODUCT(--ISNA (MATCH(F6:F8,B6:B10,0))) = SUMPRODUCT(--{FALSE;FALSE;TRUE}): Mae'r arwydd negyddol dwbl yn trosi TRUEs i 1s a ANGHYWIR yn 0s: {0; 1; 0}. Yna mae'r ffwythiant SUMPRODUCT yn dychwelyd y swm: 1.
Cyfrwch y gwerthoedd coll gyda SUMPRODUCT a COUNTIF
I gyfri'r cyfanswm nifer y gwerthoedd yn rhestr B sydd ar goll o restr A, gallwch hefyd ddefnyddio'r swyddogaeth COUNTIF i ddweud a yw gwerth yn bodoli yn rhestr A ai peidio gyda'r amod “=0”, oherwydd bydd 0 yn cynhyrchu os yw gwerth ar goll. Yna bydd y SUMPRODUCT yn cyfrif cyfanswm nifer y gwerthoedd coll.
Cystrawen generig
=SUMPRODUCT(--(COUNTIF(lookup_range,range_to_count)=0))
- ystod_chwilio: Yr ystod i gymharu â'r ystod_i_gyfrif. Yma yn cyfeirio at y rhestr A.
- ystod_i_gyfrif: Yr ystod i gyfrif gwerthoedd coll ohono. Yma mae'n cyfeirio at restr B.
- 0: Mae adroddiadau math_match 0 gorfodi MATCH i berfformio union baru.
I gyfri'r cyfanswm nifer y gwerthoedd yn rhestr B sydd ar goll o restr A, copïwch neu nodwch y fformiwla isod yn y gell H6, a gwasgwch Rhowch i gael y canlyniad:
=SUMPRODUCT(--(COUNTIF(B6: B10,F6: F8)=0))
Esboniad o'r fformiwla
=SUMPRODUCT(--(COUNTIF(B6:B10,F6:F8)=0))
- COUNTIF(B6:B10,F6:F8): Mae'r ffwythiant COUNTIF yn cyfrif nifer digwydd y celloedd drwodd F6 i F8 yn yr ystod B6: B10. Bydd y canlyniad mewn arae fel hyn: {1; 1; 0}.
- - (COUNTIF(B6:B10,F6:F8)= 0) = - ({1; 1; 0}= 0): Mae'r pyt {1;1;0}=0 yn rhoi arae CYWIR ac ANGHYWIR {FALSE;FALSE;TRUE}. Yna mae'r arwydd negyddol dwbl yn troi GWIR yn 1s a ANGHYWIR yn 0s. Mae'r arae olaf fel hyn: {0; 0; 1}.
- SUMPRODUCT (- (COUNTIF(B6:B10,F6:F8)= 0)) = SUMPRODUCT ({0; 0; 1}): Yna mae'r SUMPRODUCT yn dychwelyd y swm: 1.
Swyddogaethau cysylltiedig
Yn Excel, gellir defnyddio'r swyddogaeth SUMPRODUCT i luosi dwy golofn neu arae neu fwy gyda'i gilydd, ac yna cael swm y cynhyrchion. Mewn gwirionedd, mae'r SUMPRODUCT yn swyddogaeth ddefnyddiol a all helpu i gyfrif neu symio gwerthoedd celloedd â meini prawf lluosog fel swyddogaeth COUNTIFS neu SUMIFS. Bydd yr erthygl hon yn cyflwyno'r gystrawen swyddogaeth a rhai enghreifftiau ar gyfer y swyddogaeth SUMPRODUCT hon.
Mae swyddogaeth Excel MATCH yn chwilio am werth penodol mewn ystod o gelloedd, ac yn dychwelyd safle cymharol y gwerth.
Mae swyddogaeth COUNTIF yn swyddogaeth ystadegol yn Excel a ddefnyddir i gyfrif nifer y celloedd sy'n cwrdd â maen prawf. Mae'n cefnogi gweithredwyr rhesymegol (<>, =,>, a <), a'r cardiau gwyllt (? A *) ar gyfer paru rhannol.
Fformiwlâu Cysylltiedig
Mae yna achosion pan fydd angen i chi gymharu dwy restr i wirio a yw gwerth rhestr A yn bodoli yn rhestr B yn Excel. Er enghraifft, mae gennych restr o gynhyrchion, ac rydych chi am wirio a yw'r cynhyrchion yn eich rhestr yn bodoli yn y rhestr cynnyrch a ddarperir gan eich cyflenwr. I gyflawni'r dasg hon, yma gwnaethom restru tair ffordd isod, croeso i chi ddewis yr un rydych chi'n ei hoffi.
Bydd yr erthygl hon yn canolbwyntio ar y fformwlâu Excel ar gyfer cyfrif y celloedd sy'n union yr un fath â llinyn testun rydych chi'n ei nodi neu'n rhannol hafal i'r llinyn testun a roddir fel isod sgrinluniau a ddangosir. Yn gyntaf, bydd yn egluro cystrawen a dadl y fformiwla, mae'n darparu enghreifftiau i chi eu deall yn well.
Nifer y celloedd nad ydyn nhw rhwng dau rif penodol
Mae cyfrif nifer y celloedd rhwng dau rif yn dasg gyffredin i ni yn Excel, ond, mewn rhai achosion, efallai yr hoffech chi gyfrif y celloedd nid rhwng dau rif penodol. Er enghraifft, mae gen i restr o gynhyrchion gyda'r gwerthiannau o ddydd Llun i ddydd Sul, nawr, mae angen i mi gael nifer y celloedd nad ydyn nhw rhwng y niferoedd isel ac uchel penodol fel y dangosir isod. Yr erthygl hon, byddaf yn cyflwyno rhai fformiwlâu ar gyfer delio â'r dasg hon yn Excel.
Yr Offer Cynhyrchedd Swyddfa Gorau
Kutools for Excel - Yn Eich Helpu i Sefyll Allan O Dyrfa
Kutools ar gyfer Excel Mae ganddo Dros 300 o Nodweddion, Sicrhau mai dim ond clic i ffwrdd yw'r hyn sydd ei angen arnoch chi...
Tab Office - Galluogi Darllen a Golygu Tabiau yn Microsoft Office (gan gynnwys Excel)
- Un eiliad i newid rhwng dwsinau o ddogfennau agored!
- Gostyngwch gannoedd o gliciau llygoden i chi bob dydd, ffarweliwch â llaw llygoden.
- Yn cynyddu eich cynhyrchiant 50% wrth wylio a golygu sawl dogfen.
- Yn dod â Thabiau Effeithlon i'r Swyddfa (gan gynnwys Excel), Just Like Chrome, Edge a Firefox.
