txheem
Peb muaj ob lub rooj (piv txwv li, cov qub thiab tshiab versions ntawm daim ntawv teev nqi), uas peb yuav tsum tau sib piv thiab sai sai nrhiav qhov sib txawv:
Nws yog tam sim ntawd paub meej tias ib yam dab tsi tau ntxiv rau daim ntawv teev nqi tshiab (hnub, qej ...), ib yam dab tsi tau ploj mus (blackberries, raspberries ...), cov nqi tau hloov pauv rau qee yam khoom (fig, melons ...). Koj yuav tsum nrhiav sai sai thiab tso saib tag nrho cov kev hloov pauv no.
Rau txhua txoj haujlwm hauv Excel, yuav luag txhua zaus ntau dua ib qho kev daws teeb meem (feem ntau 4-5). Rau peb qhov teeb meem, ntau txoj hauv kev tuaj yeem siv tau:
- muaj nuj nqi VPR (VLOOKUP) - saib cov npe khoom los ntawm cov npe tshiab ntawm tus nqi qub thiab tso saib tus nqi qub ntawm tus tshiab, thiab tom qab ntawd ntes qhov sib txawv
- ua ke ob daim ntawv teev npe rau hauv ib qho thiab tom qab ntawd tsim lub rooj pivot raws li nws, qhov sib txawv yuav pom meej meej
- siv Power Query Add-in rau Excel
Cia peb coj lawv mus rau hauv kev txiav txim.
Txoj Kev 1. Sib piv cov ntxhuav nrog VLOOKUP muaj nuj nqi
Yog tias koj tsis paub meej txog qhov zoo kawg nkaus no, ua ntej saib ntawm no thiab nyeem lossis saib cov yeeb yaj kiab qhia ntawm nws - txuag koj tus kheej ob peb xyoos ntawm lub neej.
Feem ntau, txoj haujlwm no yog siv los rub cov ntaub ntawv los ntawm ib lub rooj mus rau lwm qhov los ntawm kev sib piv qee qhov tsis sib xws. Hauv qhov no, peb yuav siv nws los thawb cov nqi qub rau hauv tus nqi tshiab:
Cov khoom, tawm tsam qhov #N / A yuam kev tawm, tsis nyob hauv daim ntawv teev npe qub, piv txwv li tau ntxiv. Cov nqi hloov pauv kuj pom meej.
pros hom no: yooj yim thiab meej, "classic ntawm hom", raws li lawv hais. Ua haujlwm hauv txhua qhov version ntawm Excel.
cons tseem muaj. Txhawm rau tshawb nrhiav cov khoom ntxiv rau hauv daim ntawv teev nqi tshiab, koj yuav tau ua tib txoj kev nyob rau hauv qhov kev taw qhia, piv txwv li rub cov nqi tshiab rau tus nqi qub nrog kev pab los ntawm VLOOKUP. Yog tias qhov loj me ntawm cov ntxhuav hloov tag kis, cov qauv yuav tsum tau hloov kho. Zoo, thiab ntawm cov rooj loj loj (> 100 txhiab kab), tag nrho cov kev zoo siab no yuav ua rau qeeb qeeb.
Txoj Kev 2: Sib piv cov rooj siv pivot
Cia peb luam peb cov ntxhuav hauv qab ib sab, ntxiv ib kab nrog lub npe ntawm daim ntawv teev nqi, kom tom qab ntawd koj tuaj yeem nkag siab los ntawm cov npe kab twg:
Tam sim no, raws li lub rooj tsim, peb yuav tsim cov ntsiab lus los ntawm Insert – PivotTable (Insert — Pivot Table). Wb muab pov tseg khoom mus rau thaj tsam ntawm kab, teb nqe mus rau kab thiab teb Цena nyob rau hauv qhov ntau:
Raws li koj tuaj yeem pom, lub rooj pivot yuav cia li tsim cov npe dav dav ntawm txhua yam khoom los ntawm cov npe qub thiab cov npe tshiab (tsis muaj rov ua dua!) thiab txheeb cov khoom raws li cov tsiaj ntawv. Koj tuaj yeem pom meej cov khoom ntxiv (lawv tsis muaj tus nqi qub), cov khoom tshem tawm (lawv tsis muaj tus nqi tshiab) thiab cov nqi hloov pauv, yog tias muaj.
Grand tag nrho nyob rau hauv xws li ib lub rooj tsis muaj kev nkag siab, thiab lawv tuaj yeem ua tsis taus ntawm lub tab Constructor – Grand tag nrho – Disable rau kab thiab kab (Tsim - Grand Totals).
Yog tias tus nqi hloov pauv (tab sis tsis yog qhov ntau ntawm cov khoom!), ces nws yog txaus los hloov kho cov ntsiab lus tsim los ntawm txoj cai-nias ntawm nws - refresh.
pros: Txoj hauv kev no yog qhov kev txiav txim siab nrawm nrog cov rooj loj dua VLOOKUP.
cons: koj yuav tsum tau manually luam cov ntaub ntawv nyob rau hauv ib leeg thiab ntxiv ib kem nrog lub npe ntawm daim ntawv teev nqi. Yog tias qhov ntau thiab tsawg ntawm cov ntxhuav hloov, ces koj yuav tsum ua txhua yam ntxiv.
Txoj Kev 3: Sib piv cov ntxhuav nrog Cov Lus Nug Lub Zog
Power Query yog qhov pub dawb ntxiv rau Microsoft Excel uas tso cai rau koj thauj cov ntaub ntawv mus rau hauv Excel los ntawm yuav luag txhua qhov chaw thiab tom qab ntawd hloov cov ntaub ntawv no hauv txhua txoj kev xav tau. Hauv Excel 2016, qhov no add-in yog twb ua nyob rau hauv los ntawm lub neej ntawd ntawm lub tab Cov ntaub ntawv (Cov ntaub ntawv), thiab rau Excel 2010-2013 koj yuav tsum rub tawm nws cais los ntawm Microsoft lub vev xaib thiab nruab nws - tau txais lub tab tshiab Lub Zog Lus Nug.
Ua ntej thauj peb cov nqi teev rau hauv Power Query, lawv yuav tsum xub hloov mus rau hauv cov ntxhuav ntse. Txhawm rau ua qhov no, xaiv qhov ntau nrog cov ntaub ntawv thiab nias qhov sib xyaw ua ke ntawm cov keyboard Ctrl+T los yog xaiv lub tab ntawm lub ribbon Tsev – Format ua ib lub rooj (Lub Tsev — Format as Table). Cov npe ntawm cov rooj tsim tuaj yeem raug kho ntawm lub tab constructor (Kuv yuav tawm ntawm tus qauv rooj 1 и rooj 2, uas tau txais los ntawm lub neej ntawd).
Load tus nqi qub hauv Power Query siv lub pob Los ntawm Table/Range (Los ntawm Table/Range) los ntawm tab Cov ntaub ntawv (Hnub) los yog los ntawm lub tab Lub Zog Lus Nug (nyob ntawm qhov version ntawm Excel). Tom qab thauj khoom, peb yuav rov qab mus rau Excel los ntawm Power Query nrog cov lus txib Kaw thiab thauj khoom - Kaw thiab thauj hauv… (Kaw & Load — Kaw & Load To…):
… thiab nyob rau hauv lub qhov rais uas tshwm ces xaiv Tsuas yog tsim kev sib txuas (Kev sib txuas nkaus xwb).
Rov ua tib yam nrog daim ntawv teev nqi tshiab.
Tam sim no cia peb tsim cov lus nug thib peb uas yuav sib xyaw thiab sib piv cov ntaub ntawv los ntawm ob qho dhau los. Txhawm rau ua qhov no, xaiv hauv Excel ntawm lub tab Cov Ntaub Ntawv – Tau Cov Ntaub Ntawv – Muab Cov Lus Thov – Sib xyaw (Data — Tau Data — Merge Queries — Merge) los yog nias lub pob Ua ke (Merge) tab Lub Zog Lus Nug.
Nyob rau hauv lub qhov rais koom, xaiv peb cov ntxhuav nyob rau hauv lub drop-down daim ntawv teev, xaiv cov kab nrog cov npe ntawm cov khoom nyob rau hauv lawv, thiab nyob rau hauv qab, teem txoj kev koom - Ua kom tiav sab nraud (Tawm sab nraud):
Tom qab txhaj rau OK ib lub rooj ntawm peb kab yuav tsum tshwm sim, qhov twg nyob rau hauv lub thib peb kem koj yuav tsum tau nthuav cov ntsiab lus ntawm nested ntxhuav siv ob tug xub nyob rau hauv lub header:
Yog li ntawd, peb tau txais kev sib koom ua ke ntawm cov ntaub ntawv los ntawm ob lub rooj:
Nws yog qhov zoo dua, tau kawg, los hloov npe cov npe hauv kab hauv header los ntawm ob-nias ntawm cov neeg nkag siab ntau dua:
Thiab tam sim no qhov nthuav tshaj plaws. Mus rau tab Ntxiv kem (Ntxiv Kem) thiab nyem rau ntawm lub pob xwm kab ke (Conditional Kem). Thiab tom qab ntawd hauv lub qhov rais uas qhib, nkag mus rau ntau qhov kev sim nrog lawv cov txiaj ntsig tso tawm:
Nws tseem nyem rau OK thiab upload cov ntawv ceeb toom rau Excel siv tib lub pob kaw thiab download tau (Kaw & Load) tab Tsev (Tsev):
Kev zoo nkauj.
Ntxiv mus, yog tias muaj kev hloov pauv tshwm sim hauv cov npe ntawm tus nqi yav tom ntej (kab ntxiv lossis tshem tawm, tus nqi hloov pauv, thiab lwm yam), ces nws yuav txaus tsuas yog hloov kho peb cov lus thov nrog keyboard shortcut. Ctrl+Alt+F5 los yog khawm Hloov tshiab txhua yam (Refresh Txhua) tab Cov ntaub ntawv (Hnub).
pros: Tej zaum txoj kev zoo nkauj tshaj plaws thiab yooj yim ntawm txhua tus. Ua haujlwm ntse nrog cov rooj loj. Tsis xav tau phau ntawv kho thaum resizing rooj.
cons: Yuav tsum muaj Power Query add-in (hauv Excel 2010-2013) lossis Excel 2016 los nruab. Cov npe kab npe hauv cov ntaub ntawv yuav tsum tsis txhob hloov pauv, txwv tsis pub peb yuav tau txais qhov yuam kev "Kem xws li thiab qhov ntawd tsis pom!" thaum sim hloov kho cov lus nug.
- Yuav ua li cas sau cov ntaub ntawv los ntawm tag nrho cov ntaub ntawv Excel hauv ib daim nplaub tshev uas siv Power Query
- Yuav ua li cas nrhiav qhov sib tw ntawm ob daim ntawv teev npe hauv Excel
- Muab ob daim ntawv teev npe yam tsis muaj qhov sib npaug