Excel Summary rooj: yuav ua li cas los tsim thiab ua hauj lwm? Ua hauj lwm nrog Excel PivotTables

Muaj ntau lub chaw ua haujlwm cov neeg siv feem ntau fim ib tug xov tooj ntawm txoj kev sib tw thaum sim mus tsim thiab kho kom raug tej chaw ua hauj lwm cov ntaub ntawv. Feem ntau qhov no yog vim lub fact tias tuam txhab uas muag siv ob peb chaw ua hauj lwm cov kev pab cuam, lub hauv paus ntsiab lus uas yuav txawv heev. Tshwj xeeb tshaj yog ib tug ntau ntawm teeb meem muaj peev xwm tsim Excel PivotTables.

Qhov zoo ces, kuj nyuam qhuav sawv MS Office 2010-2013, uas tsis tsuas muaj xws li ib tug xov tooj ntawm tshiab software rau ua ntawv luam ntaub ntawv, spreadsheets, databases thiab nthuav, tab sis kuj tso cai rau koj mus ua hauj lwm nrog lawv ib txhij mus rau ob peb cov neeg ua haujlwm nyob rau hauv ib tug neeg lub ib puag ncig yog priceless.

Vim li cas cov tshiab version?

Yuav kom to taub lo lus nug no, koj yuav tsum xav seb tag nrho cov tseem ceeb tshaj plaws kev hloov uas tau tshwm sim nrog no chaw ua hauj lwm suite.

Raws li ua ntej, nws yog Excel yog tus thib ob thov rau ib qho kev pab uas tsis tau tsuas yog tso cai rau koj los mus tsim tej yam yooj yim ntxhuav, tab sis twb tsim tau ib tug ncaj complex database. Raws li nrog rau lwm yam, nws tau tau ntxiv rau "Office" khawm, txhaj rau uas koj yuav tseg tau daim ntawv nyob rau hauv cov yam hom ntawv, qhov kev hloov los yog qhia kom meej qhov yuav tsum tau ntsuas rau txoj kev tiv thaiv. Nws twb ho kho cov kev ruaj ntseg module ntawm daim ntawv.

Raws li rau qhov tseem ceeb kev hloov mus rau Excel, nws yuav tsum tau muab sau tseg txoj kev kho ntau yam uas tsis nyob rau hauv cov qauv, vim hais tias ntawm yog dab tsi versions yav dhau los feem ntau nyob heev loj yuam kev nyob rau hauv cov lus teb.

Nws yuav tsum tau hais tias tus tshiab version ntawm lub pob - qhov no yog tsis yog ib tug txheej ntawm chaw ua hauj lwm cov kev pab cuam, tab sis kuj yog ib tug uas haib, tsim los daws kom tau txawm lub feem ntau complex paub tab. Raws li koj tau nkag siab txog peb niaj hnub no yuav muab suav hais tias Excel pivot rooj.

Yuav ua li cas yog nws thiab yog dab tsi rau?

Qhov loj tshaj kev hloov nyob rau hauv lub tshiab version ntawm "Office" yog lawv siab perekroenny interface, uas tau raug hu ua tus creators ntawm cov Ribbon (ribbon). Lub tapes tag nrho cov 1500 commands yog conveniently grouped los ntawm pawg, ces koj yuav tsis muaj ntev mus nrhiav lawv. Rau siab ua raws li cov tsis, lub developers tau ntxiv rau Excel kuj zoo PivotTable.

Niaj hnub no peb xav txog Excel «rau dummies." Ntsiab lus ntxhuav - ib tug tshwj xeeb lub cuab tam, nyob rau hauv uas qhov kev tshwm sim ntawm ib tug txheej txheem kom meej meej grouped. Cias muab, lawv yuav siv tau los saib yuav ua li cas muaj ntau yam ntawm cov neeg los yog lwm yam khoom muag txhua txhua neeg muag khoom rau nws ua haujlwm. Nyob rau hauv tas li ntawd, lawv yog siv nyob rau hauv tus neeg mob qhov twg nws yog tsim nyog:

• Npaj analytical suav kev sau ntawv kawg ntaub ntawv.
• Xam lawv txhua txhua qhov taw qhia nyias.
• Tsim grouping los ntawm hom ntaub ntawv.
• Lim thiab nyob rau hauv-tob tsom xam ntawm cov ntaub ntawv.

Nyob rau hauv no tsab xov xwm peb yuav saib tej yam yooj yim txoj kev los mus tsim lawv. Koj yuav kawm tau npaum li cas Excel pivot rooj yuav pab tau tsim thiab tsom xam lub sij hawm series, los tsim thiab parse ib tug ncauj lus kom ntxaws forecast.

Tsim cov kev kawm ntawv

Yog li ntawd yuav ua li cas rau tsim ib tug pivot rooj nyob rau hauv Excel? Ua ntej koj yuav tsum tau tsim ib tug yooj yim daim ntawv nyob rau hauv uas peb ua cov ntaub ntawv rau tom qab tsom xam. Ib kem yuav tsum tau dab tsi rau ib tug parameter. Siv ib tug piv txwv yooj yim:

• muag lub sij hawm.
• Khoom muag.
• Cov tag nrho cov nqi ntawm kev muag khoom.

Yog li, nrog rau txhua tsis generated nyob rau hauv txhua link kem: xav hais tias nkawm khau muaj muag rau 9 teev sawv ntxov, lub profit ua n-rubles.

formatting ntxhuav

Tom qab npaj tag nrho cov thawj ntaub ntawv, muab lub cursor nyob rau hauv thawj cell ntawm cov thawj kem, qhib lub tab "Insert" thiab ces nias rau ntawm lub khawm "Summary Rooj". Koj yuav tam sim ntawd pom ib tug dialog box nyob rau hauv uas koj yuav tau ua cov nram qab no ua hauj lwm:

• Yog hais tias koj tam sim ntawd nias lub "OK" khawm, ces Excel PivotTables yuav yuav tam sim ntawd tso tawm kom pom nyob rau hauv ib tug nyias muaj nyias ib daim ntawv.
• Nkaus customize feem.

Nyob rau hauv lub caij nyoog kawg no cov ntaub ntawv, cov developers muab rau peb lub sijhawm los mus txiav txim hauv lub chav hlwb uas yuav tsum tso tawm kom pom cov lus qhia ntawd. Tom qab no, tus neeg siv yuav tsum txiav txim seb qhov twg rau cov tshiab lub rooj yog tsim, rau ib tug uas twb muaj lawm los yog tshiab khiv tsim rau ntawm daim ntawv. By txhaj "OK", koj yuav tam sim ntawd saib rau pem hauv ntej ntawm ib tug npaj txhij rooj. Thaum lub creation ntawm pivot ntxhuav nyob rau hauv Excel yog xyaum ua kom tiav.

Nyob rau sab xis ntawm daim ntawv nyob rau tej thaj chaw, uas koj yuav ua hauj lwm. Liaj teb yuav yuav dragged thiab poob mus rau hauv nyias muaj nyias ib qhov chaw, tom qab uas cov ntaub ntawv no yuav tsum tso tawm kom pom nyob rau hauv ib lub rooj. Raws li, qhov pivot rooj nws tus kheej yuav tsum tau nyob rau sab laug sab ntawm lub ntshoo.

Tuav thaum nias rau sab laug nas khawm, tus "khoom", xa nws mus rau hauv ib tug "title bar", thiab nias "Tawm ntawm Muag" nyob rau hauv tib txoj kev forwards nyob rau hauv lub "nqi" (nyob rau sab laug ntawm daim ntawv). Qhov ntawd yog yuav ua li cas koj yuav tau tus nqi ntawm kev muag khoom rau tag nrho analyzed lub sij hawm.

Tsim thiab grouping lub sij hawm series

Yuav kom ntsuam xyuas qhov muag rau tej ntev ntawm lub sij hawm, koj yuav tsum tau ntxig cov paragraphs nyob rau hauv lub rooj nws tus kheej. Ua li no koj yuav tsum mus rau hauv daim ntawv "Cov ntaub ntawv" thiab ces ntxig rau peb tshiab kem tam sim ntawd tom qab lub hnub. Xaiv sab nrog lub npe ntawm cov khoom, thiab ces nias rau ntawm lub khawm "Insert".

Nws yog ib qho tseem ceeb heev, yog li ntawd tag nrho cov tshiab khiv tsim txhua nyob sab hauv ib tug uas twb muaj lawm lub rooj nrog rau qhov chaw cov ntaub ntawv. Nyob rau hauv cov ntaub ntawv no, tus creation ntawm pivot ntxhuav nyob rau hauv Excel dua yuav tsum tau. Koj yuav tsuas ntxiv tshiab teb mus rau lub kev kawm ntaub ntawv.

siv mis

Piv txwv li, tus tshiab khiv tsim txhua yuav tsum tau hu ua lub "Xyoo", "Lub Hlis", "Hlis-xyoo." Yog xav tau cov ntaub ntawv peb xav nyob rau hauv ib tug ntawm lawv yuav tsum tau sau npe ib tug nyias muaj nyias ib cov mis mos rau lub xam:

• Lub "Annual" ntxig cov mis ntawm daim ntawv: "= XYOO" (xa mus rau lub hnub tim).
• Hlis yuav tsum tau supplemented los ntawm qhov qhia: "= HLI" (kuj nrog reference rau hnub).
• Nyob rau hauv lub thib peb sab ntawm daim ntawv peb ntxig lub mis "= CONCATENATE" (xa mus rau lub xyoo thiab lub hli).

Raws li, peb yuav nrhiav tau peb txhua nrog tag nrho cov thawj cov ntaub ntawv. Tam sim no mus rau cov zaub mov "Summary", nias rau ntawm tej qhov chaw tas nyob rau hauv lub rooj nyob rau txoj cai nas khawm, thiab ces nyob rau hauv lub qhib cov ntsiab lus teb ntawv qhia zaub mov xaiv qhov khoom "hloov tshiab". Xyuam xim! Ua tib zoo sau cov qauv nyob rau hauv Excel PivotTables, raws li yam yuam kev yuav ua rau forecasting kom raug.

Yuav ua li cas rau kev kev tsom xam ntawm kev muag khoom cov ntaub ntawv?

Peb nias rau sab laug nas khawm nias lub "Xyoo", thiab ces cab nws mus rau "Kem Lub npe", thiab ces pib cov lus xaus lus no qhia cov kev soj ntsuam ntawm ib tug zoo ntawm cov khoom muag thoob plaws tag nrho cov xyoo. Thiab yog hais tias koj xav kom cia li tsom xam txhua hli muag khoom? Ib yam li ntawd, peb nias lub LMB "Hlis", dragging nws nyob rau hauv lub xyoo kiag.

Yog xav tau ib tug yeeb yam muaj zog ntawm txhua lub hli hloov nyob rau hauv kev muag khoom ntau lub xyoo, lub "Hlis" rau luag ncaj qha nyob rau hauv lub xyoo kem, yav tas los tsiv nyob rau hauv lub "Kem Lub npe". Tom qab ua kom tiav cov kauj ruam no, koj yuav pom ib lub rooj nyob rau hauv cov nram no nthuav qhia:

• Tus nqi ntawm kev muag khoom ntawm ib tug khoom rau tag nrho cov xyoo.
• Lub zog ntawm kev muag khoom ntawm txhua tus ntawm lawv nyob rau lub xyoo.

Tshem tawm cov ntaub ntawv ntawm lub txais

Xav hais tias hais tias qhov teeb meem ntawm, peb yuav tsum tau tshem tawm cov lub kaum hli ntuj tej ntaub ntawv, li ntawd, peb tsis muaj kev txheeb cais rau nws. Cov lus xaus rooj saib "Daim ntawv qhia txog Lim", thiab ces luag nws mus rau lub "Xyoo - Hlis".

lim zoo nkaus li saum toj no rau hauv lub rooj qhov chaw uas koj xav kom muab tso rau hauv lub thawv tom ntej no mus rau "Xaiv ntau yam khoom." Tom qab ntawd, rau hauv daim ntawv uas zoo nkaus li, uncheck Lub kaum hli ntuj, thiab ces nias rau ntawm "OK".

Yog li, koj yuav tau ntxiv thiab tshem tawm los ntawm lub taub lim ntsiab, txoj kev ua ib tug tiag tiag ceev thiab koj yuav tsum ntsuam xyuas. Qhov kev pab cuam txiav laij cov nuj nqis rau cov neeg lub hlis nyob rau hauv uas koj xav, kom meej meej raws li lub zog ntawm kev hloov ntsuas.

Peb cia siab tias qhov

Sim twv seb rau tau ntawm kev muag khoom rau tej lub sij hawm tuaj. Ua li no, koj yuav tsum lov tes taw rau pre-qhia tau hais tias lawv tsis tau ua ib qho yuam kev nyob rau hauv lub generated forecast.

Ua li no, muab tso rau lub cursor nyob rau hauv lub "Grand Tag nrho cov", ces nias rau lub khawm "Tshem tawm cov neeg pej xeem nrog tag nrho." Tom qab ntawd, lub automatic hloov tshiab ntawm Excel PivotTables txoj kev piav saum toj no rau peb (nyem rau ntawm txoj cai nas khawm, xaiv "hloov tshiab").

Raws li ib tug tshwm sim, cov ntaub ntawv no yog poob los ntawm peb txoj kev lub rooj. Yuav kom tsim ib tug sawv cev nrig ntawm forecasts rau kev muag khoom, muab tso rau lub cursor rau lub xyoo, uas yog npaj mus ua ib tug pib, thiab ces peb nias tus nas laug khawm rau lub khawm "Schedule forecasting qauv."

Tom qab hais tias muaj yog ib tug zoo nkauj thiab tuav teeb nyob rau hauv nrig txog kev pom daim ntawv yuav tsum tau ntawm cov neeg siv kev ua si rau nthuav twv ua ntej. Qhov no yuav txuag tau koj lub zog thiab txuag tau nyiaj uas yuav siv rau lub purchase ntawm cov khoom yog tiag tiag tsim nyog.

Peb siv lwm yam ntaub ntawv rau tsim ntawm ib tug PivotTable

Nws yog lub sij hawm mus xav txog ntau Excel qhia. Cov lus xaus rooj yuav tsum tsim nyob rau hauv lub hauv paus ntawm cov ntaub ntawv uas tau raug edited los ntawm lwm cov neeg.

Qhov no yuav pab kom peb ua "Merge". Qhov kev pab no tso cai rau koj yuav tau muab cov ntawv nyeem ntawm daim ntawv thiab qhov tsim nyog qhov chaw nyob, lub npe los yog lwm yam ntaub ntawv tsim nyog los tsim tus kheej forecasts tias nyob rau hauv cov ntaub ntawv no thiab yog yuav tsum tau.

hauj lwm ua ke muaj nuj nqi nyob rau hauv tom qab versions ntawm lub "chaw ua hauj lwm" tsis tau hloov ntau npaum li cas piv rau chaw ua hauj lwm 2003. Cuaj kaum database queries mus xaiv cov ntaub ntawv qhov twg los yuav tsum tau nrog cov kev siv ntawm Microsoft Lus nug. Yog xav tau cov ntaub ntawv txoj kev Excel spreadsheet, koj yuav tsum muaj ob yam khoom:

• Lub ntsiab rau daim ntawv muaj cov lus qhia uas koj xav tau, raws li tau zoo raws li teb hais tias Excel kev pab cuam, rau cov ntaub ntawv muab tso rau hauv cov ntaub ntawv. Lub ntsiab lus yog ib tug txheej txheem ntawv, spreadsheet, tab sis koj yuav tsum qhia kom meej nyob rau hauv lub dialog hauj lwm ua ke (Mail hauj lwm ua ke pab), yog dab tsi nws yuav tsum tau siv raws li cov thawj.
• Cov ntaub ntawv qhov twg los muaj cov ntaub ntawv uas tsim nyog rau qhov kev npaj ntawm cov kev twv. Koj muaj peev xwm tsim ib tug tshiab rooj nyob rau hauv ib txoj kev uas peb tau taw tes qhia tawm saum toj no.

Thaum cov txheej txheem ntawm cov creation ntawm txoj kev cov ntaub ntawv, qhov kev pab cuam tsim ib daim ntawv rau txhua txhua tus cov ntaub ntawv muaj nyob rau hauv cov ntaub ntawv qhov twg los ua ntaub ntawv thov. Cov ntaub ntawv yuav muab tso tawm kom nyob rau hauv ib tug nyias muaj nyias ib seem ntawm tus tshiab cov ntaub ntawv, los yog siv raws li ib tug muab faib rau ntawm koj pivot rooj.

Ib tug muaj tsuas mus nias rau qhov kev kawm teb ntawm koj sab laug nas khawm, thiab koj yuav saib tau lub chaw ntawm cov ntaub ntawv es tsis txhob ntawm lub teb npe uas yuav tshwm sim nyob rau hauv lub ntsiab daim ntawv. Liaj teb yuav muab tso rau ntawm tej qhov chaw nyob rau hauv cov ntaub ntawv.

siv pawg

Tsis tas li ntawd koj yuav siv THOV qeb, uas txhais tau tias "nrhiav" thiab FILLIN dab tsi nws txhais tau hais tias "sau" cov TOM NTEJ thiab NEXTIF, lwm tus neeg. Koj yuav tau qhib qhov tsis muaj kev twv cov ntsiab lus yuav tsum tau tso tawm nyob rau hauv lub kawg lub rooj. Nrhiav cov yam nkag teb chaws yog tau los ntawm kev siv ib tug tshwj xeeb lim los yog los ntawm tsuas yog txheeb cov ntaub ntawv, raws li peb tau hais los saum no.

Thaum koj npaj txhij los ua ib tug tswv ntawv, Merge dialog box tso cai rau tus neeg siv los mus taw qhia thiab kho tej yuam kev ua ntej lawv tshwm sim. Thaum txhua yam yog mus soj ntsuam, koj yuav xyuam xim nias lub khawm "Merge". Yog hais tias, txawm li cas los, lub merger yuav tsum tau nqa tawm ntawm e-mail, koj yuav tsum tau sim hawm caij nyoog rau nruab ib lub mail kev pab cuam (Yog hais tias koj tsis tau twb ntsia).

Nyob rau hauv lub rooj plaub uas muaj tej yam yuam kev, nws yog nyob rau hauv tus txheej txheem ntawm merger hais txog incorrectly teev teb nyob rau hauv lwm daim ntawv, los yog siv cov ntaub ntawv no yog nyob rau hauv ib tug unsupported hom. Yog li ntawd, txoj kev ua no yuav tsum rov ntsuam xyuas.

Nyob rau hauv kev, Excel (ua hauj lwm nrog PivotTables nyob rau hauv uas peb tau pom) tso cai rau tsis tsuas kuj complex tsom xam ntawm kev yuav khoom lub hwj chim, tab sis kuj yuav tsim lawv rau lub hauv paus ntawm forecasts.