txheem
Thaum xub thawj siab ib muag (tshwj xeeb tshaj yog thaum nyeem cov kev pab), muaj nuj nqi TSEEM CEEB (NTSEEG) zoo nkaus li yooj yim thiab txawm tias tsis tsim nyog. Nws lub ntsiab lus yog tig cov ntawv nyeem uas zoo li qhov txuas mus rau qhov sib txuas ua tiav. Cov. Yog tias peb xav tau xa mus rau ntawm tes A1, ces peb tuaj yeem ua ib txwm ua qhov txuas ncaj qha (sau qhov sib npaug ntawm qhov kos npe hauv D1, nyem rau ntawm A1 thiab nias Enter), lossis peb tuaj yeem siv TSEEM CEEB rau tib lub hom phiaj:
Thov nco ntsoov tias qhov kev sib cav muaj nuj nqi - kev siv rau A1 - tau nkag rau hauv cov lus hais, yog li, qhov tseeb, yog cov ntawv ntawm no.
"Zoo, OK," koj hais. "Thiab dab tsi yog qhov txiaj ntsig?"
Tab sis tsis txhob txiav txim los ntawm thawj qhov kev xav - nws yog kev dag ntxias. Qhov no feature tuaj yeem pab koj tawm hauv ntau qhov xwm txheej.
Piv txwv 1. Transpose
Ib tug classic ntawm ib hom ntawv nyeem: koj yuav tsum tig lub ntsug dia
zawj rau kab rov tav (transpose). Tau kawg, koj tuaj yeem siv cov ntxig tshwj xeeb lossis ua haujlwm TRANSP (HLOOV) nyob rau hauv ib tug array formula, tab sis koj yuav tau txais los ntawm peb TSEEM CEEB:
Lub logic yog qhov yooj yim: kom tau txais qhov chaw nyob ntawm lub xovtooj tom ntej, peb nplaum tsab ntawv "A" nrog cov cim tshwj xeeb "&" thiab kab zauv ntawm lub xovtooj tam sim no, uas lub luag haujlwm muab rau peb. SAB (COLUMN).
Cov txheej txheem rov qab zoo dua ua qhov txawv me ntsis. Txij li lub sijhawm no peb yuav tsum tsim qhov txuas mus rau hlwb B2, C2, D2, thiab lwm yam, nws yooj yim dua los siv R1C1 txuas hom es tsis yog classic "sea sib ntaus sib tua". Hauv hom no, peb lub hlwb yuav txawv tsuas yog nyob rau hauv kab zauv: B2 = R1C2, C2 = R1C3, D2 = R1C4 thiab lwm yam
Qhov no yog qhov thib ob xaiv muaj nuj nqi sib cav los hauv. TSEEM CEEB. Yog sib npaug dag (FALSE), ces koj tuaj yeem teeb qhov chaw nyob txuas hauv R1C1 hom. Yog li peb tuaj yeem yooj yim hloov cov kab rov tav mus rau ntsug:
Piv txwv 2. Xum ua ntu zus
Peb twb tau txheeb xyuas ib txoj hauv kev ntawm kev sib sau ua ke ntawm lub qhov rais (ntau) ntawm ib qho me me ntawm ib daim ntawv uas siv cov haujlwm TSEEM CEEB (OFFSET). Ib qho teeb meem zoo sib xws kuj tuaj yeem daws tau siv TSEEM CEEB. Yog tias peb yuav tsum tau sau cov ntaub ntawv tsuas yog los ntawm qee lub sijhawm-lub sijhawm, peb tuaj yeem muab nws los ntawm cov ntawv thiab tom qab ntawd tig nws mus rau hauv qhov sib txuas tag nrho, uas peb tuaj yeem ntxig rau hauv cov haujlwm. sum (SUM):
Piv txwv 3. Cov ntawv teev lus ntse ntse
Qee lub sij hawm Microsoft Excel tsis kho cov npe ntawm cov lus ntse thiab kab ntawv raws li kev sib txuas tag nrho. Yog li, piv txwv li, thaum sim tsim ib daim ntawv teev npe poob (tab Cov ntaub ntawv - Data validation) raws kab neeg ua hauj lwm los ntawm lub rooj ntse Cov neeg peb yuav tau txais qhov yuam kev:
Yog tias peb " qhwv" qhov txuas nrog peb txoj haujlwm TSEEM CEEB, tom qab ntawd Excel yuav yooj yim lees txais nws thiab peb cov npe poob qis yuav raug hloov kho tshiab thaum ntxiv cov neeg ua haujlwm tshiab mus rau qhov kawg ntawm lub rooj ntse:
Piv txwv 4. Kev sib txuas tsis tau
Raws li koj paub, Excel tau kho qhov chaw nyob hauv cov qauv thaum ntxig lossis tshem cov kab kab ntawm ib daim ntawv. Feem ntau, qhov no yog qhov tseeb thiab yooj yim, tab sis tsis tas li. Cia peb hais tias peb yuav tsum tau hloov cov npe ntawm cov neeg ua haujlwm cov npe mus rau daim ntawv tshaj tawm:
Yog tias koj tso cov kev sib txuas tsis tu ncua ( nkag = B2 hauv thawj lub xov tooj ntsuab thiab luam tawm), ces thaum koj rho tawm, piv txwv li, Dasha, peb yuav tau txais #LINK! yuam kev nyob rau hauv ntsuab cell sib raug rau nws. (#REF!). Nyob rau hauv cov ntaub ntawv ntawm kev siv lub luag hauj lwm los tsim kev sib txuas TSEEM CEEB yuav tsis muaj teeb meem li ntawd.
Piv txwv 5: Sau cov ntaub ntawv los ntawm ntau nplooj ntawv
Piv txwv li peb muaj 5 nplooj ntawv nrog cov lus ceeb toom ntawm tib hom los ntawm cov neeg ua haujlwm sib txawv (Mikhail, Elena, Ivan, Sergey, Dmitry):
Cia peb xav tias cov duab, qhov loj me, txoj hauj lwm thiab qhov sib lawv liag ntawm cov khoom thiab lub hlis hauv txhua lub rooj yog tib yam - tsuas yog cov lej sib txawv.
Koj tuaj yeem sau cov ntaub ntawv los ntawm txhua nplooj ntawv (tsis suav nws, tab sis muab tso rau hauv ib leeg hauv "pile") nrog rau ib qho qauv:
Raws li koj tuaj yeem pom, lub tswv yim zoo ib yam: peb nplaum qhov txuas mus rau qhov xav tau ntawm tes ntawm daim ntawv muab, thiab TSEEM CEEB hloov nws mus rau hauv "nyob". Txhawm rau kom yooj yim, saum lub rooj, kuv ntxiv cov ntawv ntawm kab ntawv (B, C, D), thiab ntawm sab xis - kab zauv uas yuav tsum tau muab los ntawm txhua daim ntawv.
kev cov nyom
Yog tias koj siv TSEEM CEEB (NTSEEG) Koj yuav tsum nco ntsoov txog nws qhov tsis muaj zog:
- Yog tias koj txuas mus rau lwm cov ntaub ntawv (los ntawm gluing lub npe cov ntaub ntawv nyob rau hauv square brackets, daim ntawv npe, thiab lub cell chaw nyob), ces nws tsuas ua hauj lwm thaum lub thawj cov ntaub ntawv qhib. Yog tias peb kaw, peb tau txais qhov yuam kev #LINK!
- INDIRECT tsis tuaj yeem xa mus rau qhov chaw muaj npe dynamic. Ntawm static - tsis muaj teeb meem.
- INDIRECT yog ib qho kev hloov pauv lossis "volatile" muaj nuj nqi, piv txwv li nws tau rov suav dua rau txhua qhov kev hloov pauv hauv ib lub xov tooj ntawm daim ntawv, thiab tsis yog cuam tshuam rau cov hlwb, xws li hauv cov haujlwm ib txwm muaj. Qhov no muaj kev cuam tshuam tsis zoo rau kev ua haujlwm thiab nws yog qhov zoo dua kom tsis txhob nqa mus nrog cov rooj loj INDIRECT.
- Yuav ua li cas los tsim ib tug dynamic ntau yam nrog nws pib-sizing
- Summing dhau ntawm qhov ntau-qhov rais ntawm ib daim ntawv nrog OFFSET muaj nuj nqi