txheem
Tsis ntev dhau los, qhov txuas ntxiv ntawm Excel 2016 tab tom tos koj. Tam sim no, ib qho dawb Kev Saib Ua Ntej version ntawm Office 2016 twb muaj rau txhua tus neeg los tshuaj xyuas. Cia peb pom dab tsi tshiab thiab qab hauv Redmond.
Kev saib dav dav
Raws li koj tuaj yeem pom los ntawm lub screenshot dhau los, tag nrho saib ntawm lub interface tsis tau hloov pauv ntau. Lub keeb kwm yav dhau ntawm ribbon tau tig ntsuab, thiab ribbon nws tus kheej tau tig grey, uas, hauv kuv lub tswv yim, yog qhov zoo - lub active tab yuav pom tau meej dua thiab cov ribbon tsis ua ke nrog daim ntawv, raws li yav dhau los. Excel. Cov npe ntawm cov tabs hais lus zoo rau CAPITAL - ib qho me me, tab sis zoo.
Hauv kev teeb tsa Cov ntaub ntawv – xaiv koj tuaj yeem, zoo li ua ntej, hloov cov qauv xim ntawm lub interface, tab sis kev xaiv (raws li ua ntej) rau qee qhov laj thawj yog kev nyuaj siab tag. Ntxiv nrog rau ntsuab thiab dawb huv, ib qho tsaus grey version kuj muaj:
... thiab dav hlau dub:
Tsis nplua nuj rau ib qho kev pab cuam uas muaj ntau txhiab tus neeg siv thoob ntiaj teb saib rau 5-10 teev ib hnub qee zaum. Tseem muaj chaw rau kev txhim kho nyob rau hauv cov nqe lus ntawm kev tsim, uas yog qhov tseeb. (Tus sau ntawv: Kuv tsuas yog ib leeg nkees ntawm lub ntsej muag lub ntsej muag tsis muaj lub ntsej muag-tsim nyob txhua qhov chaw thiab ib puag ncig?)
Assistant
Ib daim teb tau tshwm sim nyob rau sab xis sab xis ntawm qhov screen Assistant. Nov yog hom kev rov ua dua tshiab ntawm Paperclip nto moo - lub tshuab tshawb nrhiav nrawm nrawm rau tag nrho cov haujlwm thiab cov cuab yeej ntawm Excel. Hauv daim teb no, koj tuaj yeem pib ntaus lub npe ntawm cov lus txib lossis kev ua haujlwm, thiab Assistant tam sim muab ib daim ntawv teev cov lus qhia uas koj tuaj yeem siv:
Tau kawg, nws yuav tsum muaj cov qauv yooj yim thiab raug nrog cov lus hais ("sparklines", tsis yog "microdiagrams", thiab lwm yam), tab sis nws yog qhov zoo. Novice cov neeg siv hauv qhov xwm txheej "Kuv nco qab tias muaj lub luag haujlwm, tab sis kuv tsis nco qab qhov twg" yuav tsum nyiam nws.
Kuv vam tias yav tom ntej qhov no yuav tsis tsuas yog tshawb nrhiav hauv kev pab, tab sis txhawb lub suab tawm tswv yim thiab nkag siab - lus morphology - ces koj tuaj yeem qhia Excel koj xav ua dab tsi: "Ua ntawv qhia peb lub hlis twg los ntawm cheeb tsam thiab xa mus rau koj. tus thawj!”
Hom kab ntawv tshiab
Lub sijhawm kawg Microsoft tau ntxiv hom kab ntawv tshiab rau Excel yog xyoo 1997—yuav luag 20 xyoo dhau los! Thiab thaum kawg, cov dej khov tau tawg ntawm qhov teeb meem no (tsis yog tsis muaj tus phooj ywg pendles rau cov neeg tsim tawm los ntawm cov tswv cuab ntawm MVP zej zog, kuv mam li qhia koj ib qho zais cia). Hauv Excel 2016, ntau npaum li 6 hom kab kos tshiab tau tshwm sim tam sim ntawd, feem ntau ntawm cov ntawv qub tuaj yeem tsim tau siv tshwj xeeb add-ins lossis seev cev nrog tambourine. Tam sim no txhua yam yog ua tiav nyob rau hauv ob lub zog. Yog li, ntsib:
Daim duab dej tsaws tsag
Lwm lub npe: choj (choj), "cov kauj ruam", daim duab dej tsaws tsag. Ib hom kab ntawv feem ntau siv hauv kev txheeb xyuas nyiaj txiag (thiab tsis yog nkaus xwb) uas qhia meej txog qhov kev hloov pauv ntawm lub sijhawm hloov pauv (nyiaj ntsuab ntws, kev nqis peev) lossis kev cuam tshuam ntawm ntau yam ntawm qhov tshwm sim (tus nqi tsom xam). Yav dhau los, txhawm rau tsim cov duab kos, koj yuav tsum tau shamanize lossis yuav tshwj xeeb add-ons.
Hierarchical (Treemap Daim Ntawv Qhia)
Ib hom kab ntawv tshwj xeeb rau kev pom qhov muag pom kev faib tawm ntawm qhov ntsuas los ntawm qeb nyob rau hauv daim ntawv ntawm ib hom duab plaub "patchwork quilt". Ntxiv mus, koj tuaj yeem siv ob theem ntawm kev ua zes ntawm pawg (cov nroog hauv lub tebchaws). Nws yog qhov yooj yim siv rau kev pom, piv txwv li, cov nyiaj tau los ntawm cheeb tsam lossis cov nyiaj tau los ntawm cov khoom lag luam. Nyob rau hauv cov laus versions, lub tsev xws li ib daim ntawv yog heev nyuaj thiab feem ntau yuav tsum tau lub installation ntawm ntxiv add-ons.
Sunburst Daim ntawv qhia
Ib qho analogue ntawm hom yav dhau los, tab sis nrog ib ncig ntawm cov ntaub ntawv nyob rau hauv cov sectors, thiab tsis nyob rau hauv cov duab plaub. Hauv qhov tseeb, ib yam dab tsi zoo li lub ncuav qab zib lossis daim ntawv qhia donut. Txhawm rau pom qhov kev faib tawm, qhov no yog qhov tseem ceeb, thiab koj tsis txwv rau ob theem ntawm kev ua zes, tab sis tuaj yeem decompose rau hauv peb (qeb-khoom-khoom) lossis ntau dua.
Pareto (Pareto Daim Ntawv Qhia)
Daim duab classic rau kev pom "80/20 txoj cai" lossis "Pareto txoj cai", uas ntau tus, kuv xav tias, tsawg kawg tau hnov txog. Hauv cov ntsiab lus dav dav, nws tau tsim ua "20% ntawm kev siv zog muab 80% ntawm qhov tshwm sim." Thaum ua ntawv thov rau kev lag luam, qhov no tau ua kom zoo rau "20% ntawm cov khoom lag luam ua 80% ntawm cov nyiaj tau los", "20% ntawm cov neeg siv khoom tsim 80% ntawm cov teeb meem", thiab lwm yam. Hauv daim duab zoo li no, tag nrho cov nyiaj tau los ntawm txhua yam khoom yog pom pom. raws li cov histogram thiab, tib lub sijhawm, cov duab txiv kab ntxwv qhia tau hais tias cov nyiaj tau los sib faib. Qhov twg txoj kab hla 80% (ze Pineapple) thiab koj tuaj yeem ua rau lub hlwb kos kab ntsug kom cais cov khoom tseem ceeb (rau sab laug ntawm Pineapple) los ntawm cov tsis tseem ceeb (rau sab xis ntawm Pineapple). Daim ntawv qhia mega-pab tau rau ABC tsom xam thiab cov khoom zoo sib xws.
Mustache Box (BoxPlot Daim Ntawv Qhia)
Lwm lub npe yog "scatter plot" lossis Box-and-Whiskers Chart. Ib hom kab ntawv uas siv ntau heev hauv kev ntsuas kev txheeb xyuas uas qhia txog cov ntaub ntawv teev ib zaug:
- arithmetic txhais tau tias - cruciform notch
- nruab nrab (50% quantile) - kab rov tav ntawm lub thawv
- qis (25%) thiab sab sauv (75%) quantiles yog cov ciam teb qis thiab sab sauv ntawm lub thawv
- emissions - nyob rau hauv daim ntawv ntawm cais cov ntsiab lus
- siab tshaj thiab yam tsawg kawg nkaus tus nqi - nyob rau hauv daim ntawv ntawm ib tug mustache
Zaus histogram (Histogram Chart)
Rau cov ntaub ntawv teev tseg, qhia cov naj npawb ntawm cov ntsiab lus uas poob rau hauv qhov kev teev tseg ntawm qhov tseem ceeb. Qhov dav ntawm cov ntu lossis lawv tus lej tuaj yeem raug teeb tsa. Ib daim duab muaj txiaj ntsig zoo hauv kev txheeb xyuas zaus, segmentation thiab lwm yam. Yav dhau los, xws li ib txoj hauj lwm feem ntau yog daws los ntawm kev ua pab pawg los ntawm cov lej ua ntu zus hauv cov lus pivot lossis siv cov add-in Kev Ntsuam Xyuas Pob.
Lub Zog Lus Nug
Cov ntaub ntawv Import Add-in Lub Zog Lus Nug, yav dhau los xa khoom cais rau Excel 2013, tam sim no ua-hauv los ntawm lub neej ntawd. Hauv tab Cov ntaub ntawv (Hnub) nws tau nthuav tawm raws li ib pawg Download & Hloov:
Siv cov cuab yeej ntawm pab pawg no, koj tuaj yeem rub tawm cov ntxhuav rau Excel los ntawm yuav luag txhua hom ntawv tseem ceeb ntawm cov ntaub ntawv, Internet thiab lwm qhov chaw:
Tom qab thauj khoom, cov ntaub ntawv tau txais kuj tuaj yeem ua tiav siv Power Query, "coj nws mus rau siab":
- kho cov lej-raws-ntawv thiab hnub-as-ntawv nyeem
- ntxiv cov lej suav lossis tshem tawm qhov tsis tsim nyog
- consolidate cov ntaub ntawv los ntawm ob peb lub rooj rau hauv ib tug txiav, thiab lwm yam.
Feem ntau, qhov no yog qhov muaj txiaj ntsig zoo ntxiv rau cov uas tsis tu ncua thauj cov ntaub ntawv ntau ntawm lub ntiaj teb sab nraud rau hauv Excel.
Pivot tables
Xws li cov cuab yeej muaj txiaj ntsig zoo li cov lus pivot hauv qhov version no tau txais ob qho kev txhim kho me me. Ua ntej, nyob rau hauv lub vaj huam sib luag nrog cov npe ntawm cov teb, thaum tsim cov ntsiab lus, ib qho cuab yeej rau kev nrhiav cov teb uas xav tau sai sai tau tshwm sim:
Ib qho tseem ceeb heev thaum muaj kaum ob kab hauv koj lub rooj + koj kuj tau ntxiv suav teb los ntawm koj tus kheej.
Thib ob, yog tias lub rooj pivot tau lim los ntawm Slicer lossis Scale, thiab koj ob npaug nyem rau ntawm lub xov tooj ntawm tes nrog cov ntaub ntawv kom "poob dhau" rau hauv cov ntsiab lus, tam sim no cov kev txwv xaiv ntawm cov hlais thiab cov nplai tau muab coj los rau hauv tus account (yav dhau los lawv tau tsis quav ntsej, zoo li tsis muaj cov hlais, tsis muaj qhov ntsuas txhua).
Cov cuab yeej kwv yees
Excel 2016 tau txais ntau yam kev kwv yees tshiab. Ua ntej, nyob rau hauv qeb Qhia tawm (Kev txheeb cais) muaj cov haujlwm rau xam qhov kev kwv yees siv txoj kev exponential smoothing:
- FORECAST.ETS - muab tus nqi kwv yees rau hnub tim yav tom ntej siv cov kev hloov kho raws caij nyoog exp.smoothing
- FORECAST.ETS.DOVINTERVAL – Xam qhov kev ntseeg siab ib ntus rau kev kwv yees
- FORECAST.ETS.SEASONALITY - Txheeb xyuas lub caij nyoog hauv cov ntaub ntawv thiab suav nws lub sijhawm
- FORECAST.ETS.STAT – Muab cov ntaub ntawv ntxaws ntxaws ntawm cov lej series rau cov kev kwv yees suav
- PREDICT.LINEST – Xam cov kab sib txawv
Ib qho cuab yeej yooj yim rau kev kwv yees ntawm ya kuj tau tshwm sim - lub pob Forecast daim ntawv tab Cov ntaub ntawv (Hnub):
Yog tias koj xaiv cov ntaub ntawv hauv qab no (lub sijhawm lossis hnub thiab qhov tseem ceeb) thiab nyem lub pob no, ces peb yuav pom lub qhov rai hauv qab no:
Raws li koj tuaj yeem pom, koj tuaj yeem yooj yim teeb tsa qhov kev kwv yees tsim nyog hauv nws thiab tam sim ntawd pom qhov tshwm sim hauv cov duab sawv cev - yooj yim heev. Yog koj nias lub pob tsim, tom qab ntawv ib daim ntawv tshiab yuav tshwm sim, qhov twg cov qauv kev kwv yees yuav raug txiav tawm nrog cov qauv:
Cov khoom zoo. Yav dhau los, piv txwv li, ntawm kev cob qhia kev kwv yees, peb tau ua qhov no manually "los ntawm" thiab "rau" - thiab nws siv sijhawm zoo heev.
Tsis tas li ntawd nyob rau hauv no version, ob peb paub ua lej thiab kev txheeb cais tau tsiv mus rau qeb compatibility (Compatibility), vim tsis yog lawv, lawv "cov xeeb leej xeeb ntxwv" zoo tshaj plaws tshwm sim.
Cov lus xaus kawg
Kev saib ua ntej tsis yog kev tso tawm, thiab tej zaum hauv qhov kawg version peb yuav pom qee qhov kev hloov pauv thiab kev txhim kho ntxiv. Tab sis, thaj, tsis muaj dab tsi supernatural yuav tsum tau (ib tug yuav hais tias qhov no yog qhov zoo dua, tej zaum). Microsoft txhob txwm tshaj tawm thiab txheej txheem polishes cov yam ntxwv uas twb muaj lawm thiab maj mam ntxiv cov tshiab los ntawm version mus rau version.
Nws yog qhov zoo uas, thaum kawg, cov kab kos tshiab tau tshwm sim uas txhua tus tau tos ntev, tab sis tseem muaj chaw rau kev loj hlob - cov phiaj xwm kab kos (Gantt, ncua sij hawm), cov kab kos ("thermometers"), thiab lwm yam. cov scenes. Kuv kuj tseem nyob ntsiag to txog qhov tseeb tias sparklines tuaj yeem ua tau ntev ntev tsis yog peb hom, tab sis tseem ceeb dua, zoo li hauv thawj.
Nws yog qhov zoo uas muaj txiaj ntsig add-ons (Power Query, Power Pivot) tau tsim rau hauv qhov kev pab cuam los ntawm lub neej ntawd, tab sis tom qab ntawd nws yuav muaj peev xwm ua siab dav txawm nyob ntawm Fuzzy Lookup nrog Power Map. Hmoov tsis, tseem tsis tau.
Thiab tus kheej, kuv thov txim uas peb yuav tsis pom, zoo li, hauv Excel 2016 tshiab, tsis muaj cov cuab yeej ua haujlwm siab heev rau kev ua haujlwm nrog ntau yam (piv txwv li kev sib piv), lossis kev txhim kho hauv Visual Basic programming ib puag ncig (uas. tsis tau hloov txij li xyoo 1997), thiab tsis muaj haujlwm tshiab xws li VLOOKUP2 lossis Sum hauv cov lus.
Kuv vam tias yuav nyob mus txog rau lub sijhawm thaum txhua qhov no tshwm sim hauv Excel, tab sis tam sim no kuv yuav tau siv cov cuab yeej niaj hnub.