Computers, Software
"COUNTIF" nyob rau hauv Excel: Piv txwv hauj lwm
Lub "COUNTIF" - ib qho ntawm feem nquag upotrebimykh nta ib tug haib spreadsheet daim ntawv thov "Excel" los ntawm lub tuam txhab "Microsoft". Yog vim li cas yog nws tsim nyog? Nws yog twb tseeb los ntawm nws cov title. Nws yuav pab tau cov neeg siv mus nug ib lo lus nug "Yuav ua li cas rau suav lub hlwb nyob rau hauv Excel rau tej kev?", Xav tau txoj cai tooj.
muaj nuj nqi lus sib cav
Qhov no muaj nuj nqi yuav siv sij hawm ob yuav tsum tau cov lus sib cav. Cov Thawj zaug ntawm lawv - ib tug ntau yam nyob rau hauv uas mus suav cov xov tooj ntawm hlwb uas hais tias tau tus mob.
Qhov thib ob sib cav - ib tug mob uas yuav tsum tau muab teem nyob rau hauv daim ntawv ntawm cov zauv los yog lus, raws li tau zoo raws li nyob rau hauv daim ntawv ntawm tej yam kev mob.
Peb yuav npaj ib tug ntau ntawm cov ntaub ntawv thiab saib yuav ua li cas mus nrhiav kev nta "COUNTIF" nyob rau hauv Excel piv txwv raws li nws. Wb hais tias peb muaj ib daim ntawv teev txiv hmab txiv ntoo thiab zaub nyob rau hauv cia nrog ib daim ntawv teev lawv cov purchase price, kev muag khoom nyiaj thiab lub hnub purchase. Pib ua hauj lwm nrog cov ntxhuav rau Excel - tam sim no noj rau ib tug tsab ntawv khom nqi rau nram qab no ntaub ntawv:
A | Cov | C | D | |
1 | Txiv hmab txiv ntoo / zaub | Yuav nqi (kg) | Allowance (%) | Hnub ntawm purchase |
2 | kub apples | 74,5 | 28 | 05/01/2017 |
3 | Fuji apples | 69,8 | 50 | 05/16/2017 |
4 | tsawb | 41 | 75 | 05/04/2017 |
5 | tsawb mini | 74,5 | 42 | 05/14/2017 |
6 | apples Gloster | 54,2 | 34 | 05/15/2017 |
7 | dib Suav | 64 | 17 | 07/05/2017 |
8 | pollinated dib | 110,6 | 28 | 05/16/2017 |
9 | dib unsprayed | 132,9 | 28 | 05/01/2017 |
ib qho tseem ceeb complement
Qhov thib ob sib cav yog ib tug mob. Yog hais tias peb xav tau ib qho kev sib cav kom meej lub xov tooj los yog qhov chaw nyob ntawm ib tug cell, koj muaj peev xwm sau txoj cai txoj kev. Yog hais tias peb xav kom lawv muaj ib tug complex mob nrog ib tug sib piv ntawm cov cim, nrhiav ntawv nyeem, los yog ib tug tej hnub, lub cav yuav tsum tau muab ntim rau hauv quotes. Cia li hauv qab no nws yuav kuj sib tham txog yuav ua li cas siv "ampersand" tus cim yuav ua ke nyob rau hauv ntau yam zog muab.
Tej yam yooj yim tej yam kev mob rau cov kev ua
Tswvcuab nyob rau hauv lub yav dhau los tshooj lus no, cov ntaub ntawv txheej yuav pab deal nrog cov "COUNTIF" nyob rau hauv Excel. Cov piv txwv los sib tham nyob rau hauv seem no yog heev yooj yooj yim, tab sis lawv yog siv, thiab xav txog lawv tsim nyog, ua ntej cov tswv yim rau ntau txoj.
Nyob rau hauv tabular daim ntawv peb txo cov mis, cov kev tshwm sim ntawm lawv siv, raws li tau zoo raws li sau ib nyuag explanatory saib. Tag nrho cov lus teb yog raws li nyob rau hauv spreadsheet cov ntaub ntawv nkag mus hauv saum toj no, thiab khiav nyob rau hauv cov hnub 16.05.2017.
Piv txwv li | teb | saib |
= COUNTIF (B2: B9; 74.5) | 2 | Nws qhia ntau npaum li cas cov khoom muas ntawm ib tug nqi 74,5 |
= COUNTIF (A2 A9, A2) | 1 | Yog hais tias cov mis no "stretch" mus rau thaum xaus ntawm lub rooj, peb yuav pom hais tias peb muaj duplicate khoom (yog tias 1 - nws yuav siv sij hawm tsis muaj) |
= COUNTIF (D2: D9; HNUB NO ()) | 2 | Number of hlwb nrog rau cov khoom, yuav tsum muaj hnub no (siv lub computer system hnub) |
= COUNTIF (C2: C9, "") | 0 | Nws suav cov xov tooj ntawm dawb paug hlwb nyob rau hauv ib tug ntau yam. Ceeb toom! Yog hais tias koj muab tso rau ib qhov chaw nyob rau hauv ib lub cell, nws tsis yog raws li ib qho kev npliag |
= COUNTIF (B2: B9; "<55") | 2 | Number of hlwb nrog ib tug nqi ntawm tsawg tshaj li 55. Ib yam li ntawd, cov kev sib piv yog ua ntau tshaj li ">" tsis sib npaug zos rau "<>" |
= COUNTIF (B2: B9; "<55") + COUNTIF (B2: B9; "> 100") | 4 | Tag nrho cov xov tooj ntawm cov hlwb nyob rau ntawm lub ua ke ntawm ob tug mob |
wildcards
Lub thiaj li hu ua wildcards yog lug siv nrog cov kev ua "COUNTIF" nyob rau hauv Excel. Piv txwv wildcards:
cim | piav qhia |
* | Phau ntawv (yam tsawg cim) |
? | Phau ntawv (tus naj npawb ntawm cov cim coj mus rau tus xov tooj ntawm lo lus nug tias). Tej hauj lwm tsuas nrog cov ntawv nyeem cov ntaub ntawv! Yog hais tias lub cell tsis yog ib tsab ntawv, yuav yeej ib txwm 0 |
Tag nrho cov ua hauj lwm nrog cov ntxhuav nyob rau hauv Excel thiab tsom xam cov ntaub ntawv khaws tseg nyob rau hauv lawv yog zoo tsis yooj yim sua yam uas tsis muaj kev siv ntawm wildcards.
Tej yam kev mob rau cov kev ua nrog wildcard cim. combining zog
Thaum lawv tseem nrog rau lwm cov nta tsim yam tej yam kev mob tshwm sim vim muaj ib tug zajlus kom tej yam kev mob compound ( "ntau", "tsawg tshaj", "tsis sib npaug zos") thiab cov kev kawm muaj nuj nqi cim "ampersand" (k).
Nyob rau hauv tabular daim ntawv no, xav txog ib tug ntau cov piv txwv ntawm kev siv lub cuab tam "COUNTIF."
Piv txwv li | teb | saib |
= COUNTIF (A2: A9, "apples") | 0 | Number of hlwb muaj tsuas lo lus "Apples" (cov ntaub ntawv loog), thiab tag nrho cov |
= COUNTIF (A2: A9; "Kua *") | 3 | Cov nab npawb ntawm cov hlwb uas pib nrog cov lo lus "Kua" thiab tej thaum xaus |
= COUNTIF (A2: A9; "* D *") | 3 | Cov nab npawb ntawm cov hlwb uas muaj tsab ntawv "d" nyob rau hauv tej ib feem ntawm cov ntawv nyeem. Feem ntau siv tau los nrhiav thiab hloov rau ntxiv "thawj" mus rau "i" thiab "e" mus rau "e" |
= COUNTIF (A2: A9; "??????") | 1 | Cov khoom siv nrog lub npe ntawm ib tug ntev ntawm 6 cim |
= COUNTIF (D2: D9; "<" & (HNUB NO () - 10)) | 3 | Number of hlwb nrog lub hnub ntawm purchase ntawm ntau tshaj li kaum hnub dhau los |
= COUNTIF (C2: C9; ">" & NRUAB NRAB (C2: C9)) | 3 | Number of hlwb nrog ib tug nqi siab tshaj qhov nruab nrab muag nyiaj |
Qhov no xaus suav hais tias yog "COUNTIF" nyob rau hauv Excel.
Cov piv txwv muaj nyob rau hauv no tsab xov xwm yuav pab koj to taub tej ntsiab cai ntawm kev tsim kho thiab ob peb ua ke ntawm tej yam kev mob uas yog siv raws li ib tug sib cav mus rau ib tug muaj nuj nqi, thiab ntse thov lawv nyob rau hauv xyaum.
Similar articles
Trending Now