5 Yam Uas Koj Ua Tau Nrog Lub Zog Pivot rau Excel

Cov txheej txheem:

5 Yam Uas Koj Ua Tau Nrog Lub Zog Pivot rau Excel
5 Yam Uas Koj Ua Tau Nrog Lub Zog Pivot rau Excel
Anonim

Power Pivot yog qhov pub dawb ntxiv rau Excel uas tso cai rau koj los ua cov ntaub ntawv tshawb xyuas thiab tsim cov qauv ntaub ntawv uas zoo tshaj qhov koj tuaj yeem tsim hauv Excel.

Image
Image

Thaum muaj ntau yam peb nyiam hauv Power Pivot rau Excel, cov no yog tsib qhov txias tshaj plaws.

Koj tuaj yeem siv Power Pivot hauv Excel 2019, 2016, 2013, 2010, thiab Excel rau Microsoft 365.

Ua Haujlwm Nrog Cov Ntaub Ntawv Loj heev

Tus lej siab tshaj plaws hauv Excel yog 1, 048, 576.

Nrog Lub Zog Pivot rau Excel, muaj qhov theoretically tsis txwv rau cov kab ntawm cov ntaub ntawv. Qhov kev txwv tiag tiag nyob ntawm qhov version ntawm Microsoft Excel koj tab tom khiav thiab seb koj puas yuav luam tawm koj daim ntawv nthuav qhia rau SharePoint.

Yog tias koj tab tom khiav 64-ntsis version ntawm Excel, Lub Zog Pivot tuaj yeem tshaj tawm txog 2 GB ntawm cov ntaub ntawv, tab sis koj kuj yuav tsum muaj RAM txaus los ua qhov no ua haujlwm zoo. Yog tias koj npaj yuav tshaj tawm koj lub zog Pivot raws li Excel spreadsheet rau SharePoint, nco ntsoov xyuas seb cov ntaub ntawv loj tshaj plaws yog dab tsi.

Microsoft muaj qhov yuav ua li cas-rau kev txhim kho Power Pivot yog tias koj muaj teeb meem. Saib seb koj puas siv 32-ntsis lossis 64-ntsis version ntawm Windows yog tias koj tsis paub tseeb tias qhov twg rub tawm los ntawm Microsoft lub vev xaib.

Power Pivot rau Excel tuaj yeem tuav ntau lab cov ntaub ntawv. Yog tias koj ntaus qhov siab tshaj plaws, koj yuav tau txais qhov yuam kev nco.

Yog tias koj xav ua si nrog Power Pivot rau Excel siv ntau lab cov ntaub ntawv, rub tawm Lub Hwj Chim Pivot rau Excel Cov Qauv Qhia Cov Ntaub Ntawv (kwv yees li 2.3 lab cov ntaub ntawv) uas muaj cov ntaub ntawv koj xav tau rau Power Pivot Workbook Tutorial.

Muab cov ntaub ntawv los ntawm ntau qhov chaw

Excel ib txwm muaj peev xwm tswj hwm cov ntaub ntawv sib txawv, xws li SQL Server, XML, Microsoft Access thiab txawm tias cov ntaub ntawv hauv web. Qhov teeb meem tuaj thaum koj xav tsim kev sib raug zoo ntawm ntau yam ntaub ntawv.

Muaj cov khoom lag luam thib peb muaj los pab nrog qhov no, thiab koj tuaj yeem siv Excel ua haujlwm zoo li VLOOKUP los "sib koom" cov ntaub ntawv, tab sis cov txheej txheem no tsis siv rau cov ntaub ntawv loj. Power Pivot rau Excel yog tsim los ua kom tiav txoj haujlwm no.

Nrog Lub Hwj Chim Pivot, koj tuaj yeem xa cov ntaub ntawv los ntawm txhua yam ntaub ntawv. Ib qho ntawm cov ntaub ntawv tseem ceeb tshaj plaws yog SharePoint List. Koj tuaj yeem siv Power Pivot rau Excel los muab cov ntaub ntawv los ntawm SQL Server thiab ib daim ntawv teev npe los ntawm SharePoint.

Thaum koj txuas Power Pivot rau ib daim ntawv teev npe SharePoint, koj yog technically txuas rau cov ntaub ntawv pub. Txhawm rau tsim cov ntaub ntawv pub dawb los ntawm cov npe SharePoint, qhib nws thiab nyem rau ntawm List ribbon. Ces nyem rau Export as Data Feed thiab txuag nws.

Cov pub muaj nyob hauv URL hauv Power Pivot rau Excel. Mus saib daim ntawv dawb Siv SharePoint Sau Cov Ntaub Ntawv hauv Power Pivot (nws yog MS Word DOCX cov ntaub ntawv) kom paub ntau ntxiv txog kev siv SharePoint ua cov ntaub ntawv rau Power Pivot.

Create Visually Appealing Analytical Models

Power Pivot rau Excel cia koj tso ntau yam ntaub ntawv pom rau koj daim ntawv ua haujlwm Excel. Koj tuaj yeem xa cov ntaub ntawv rov qab rau hauv PivotTable, PivotChart, Daim Ntawv Qhia thiab Table (kab rov tav thiab ntsug), Ob kab ntawv (kab rov tav thiab ntsug), Plaub kab lus, thiab lub PivotTable Flattened.

Lub hwj chim los thaum koj tsim ib daim ntawv ua haujlwm uas suav nrog ntau qhov kev tso tawm, uas muab lub dashboard saib ntawm cov ntaub ntawv uas ua rau kev tsom xam yooj yim. Txawm tias koj cov thawj coj yuav tsum tuaj yeem cuam tshuam nrog koj daim ntawv tshaj tawm yog tias koj tsim nws raug.

Slicers, muaj nrog Excel 2010 thiab tom qab ntawd, ntxiv cov nyees khawm uas koj tuaj yeem siv los lim cov lus lossis PivotTable cov ntaub ntawv.

Koj tsuas tuaj yeem txuag Power Pivot cov ntaub ntawv hauv phau ntawv ua haujlwm uas siv XLSX, XLSM, lossis XLSB cov ntaub ntawv txuas ntxiv.

Siv DAX los tsim cov lej suav rau kev txiav thiab txiav cov ntaub ntawv

DAX (Data Analysis Expressions) yog hom lus siv hauv Power Pivot cov lus, feem ntau hauv kev tsim cov kab laum. Mus saib TechNet DAX Reference kom ua tiav kev siv.

Koj tuaj yeem siv DAX hnub ua haujlwm los ua hnub tim teb muaj txiaj ntsig zoo dua. Hauv Pivot Table nyob rau hauv Excel uas suav nrog cov hnub uas raug formatted, koj tuaj yeem siv pab pawg ntxiv kom muaj peev xwm lim lossis pab pawg los ntawm xyoo, quarter, hli thiab hnub.

Hauv Lub Hwj Chim Pivot, koj yuav tsum tsim cov kab ntawv suav kom ua tiav ib yam. Ntxiv ib kem rau txhua txoj hauv kev uas koj xav tau lim lossis pab pawg cov ntaub ntawv hauv koj Lub Rooj Pivot. Ntau lub hnub ua haujlwm hauv DAX yog tib yam li Excel cov qauv, uas ua rau qhov no yog snap.

Piv txwv li, siv =YEAR([hnub kem]) hauv kab ntawv suav tshiab ntxiv rau xyoo rau koj cov ntaub ntawv teev hauv Power Pivot. Tom qab ntawd koj tuaj yeem siv qhov tshiab no YEAR teb ua tus txiav lossis pab pawg hauv koj Lub Rooj Pivot.

Tshaj tawm Dashboards rau SharePoint

Power Pivot, thaum ua ke nrog SharePoint, tso lub zog ntawm dashboards rau hauv tes ntawm koj cov neeg siv.

Ib qho ntawm qhov yuav tsum tau ua ua ntej ntawm kev tshaj tawm Power Pivot-tsav kab kos thiab cov lus rau SharePoint yog kev siv Power Pivot rau SharePoint ntawm koj lub SharePoint ua liaj ua teb. Koj pab neeg IT yuav tau ua qhov no.

Pom zoo: