txheem
Yuav luag txhua qhov kev cob qhia Lub Hwj Chim Query, thaum peb tau txais yuav ua li cas hloov kho cov lus nug tsim thiab tib neeg pom tias cov ntaub ntawv tshiab hloov cov ntaub ntawv qub thaum hloov kho, ib tus neeg mloog tau nug kuv tias: "Nws puas tuaj yeem ua kom paub tseeb tias thaum hloov kho, cov ntaub ntawv qub yog. kuj nyob qhov twg tau txais kev cawmdim thiab tag nrho cov ntaub ntawv hloov tshiab tau pom?
Lub tswv yim tsis yog tshiab thiab cov qauv lus teb rau nws yuav yog "tsis yog" - Lub Hwj Chim Query tau teeb tsa los ntawm lub neej ntawd los hloov cov ntaub ntawv qub nrog cov tshiab (uas yuav tsum muaj nyob rau hauv feem ntau ntawm cov xwm txheej). Txawm li cas los xij, yog tias koj xav tau tiag tiag, koj tuaj yeem ncav cuag qhov kev txwv no. Thiab txoj kev, raws li koj yuav pom tom qab, yog yooj yim heev.
Xav txog cov piv txwv nram qab no.
Cia peb xav tias peb muaj cov ntaub ntawv los ntawm tus neeg siv khoom raws li cov ntaub ntawv nkag (cia peb hu nws, peb hais, Tau qhov twg los) nrog ib daim ntawv teev cov khoom uas nws xav mus yuav nyob rau hauv daim ntawv ntawm ib tug "ntse" dynamic lub npe Daim ntawv thov:
Hauv lwm cov ntaub ntawv (cia peb hu nws los ntawm kev sib piv receiver) peb tsim cov lus nug yooj yim rau import ib lub rooj nrog cov khoom los ntawm Qhov Chaw ntawm Cov ntaub ntawv - Tau txais cov ntaub ntawv - Los ntawm cov ntaub ntawv - Los ntawm Excel Workbook (Cov ntaub ntawv - Tau txais cov ntaub ntawv - Los ntawm cov ntaub ntawv - Los ntawm Excel workbook) thiab upload lub resulting table rau daim ntawv:
Yog tias yav tom ntej tus neeg siv khoom txiav txim siab hloov pauv qhov kev txiav txim hauv nws cov ntaub ntawv Tau qhov twg los, tom qab hloov kho peb qhov kev thov (los ntawm txoj cai-nias lossis ntawm Cov ntaub ntawv - Refresh Txhua) peb yuav pom cov ntaub ntawv tshiab hauv cov ntaub ntawv receiver - txhua tus qauv.
Tam sim no cia peb ua kom paub tseeb tias thaum hloov kho, cov ntaub ntawv qub tsis hloov los ntawm cov tshiab, tab sis cov tshiab tau ntxiv rau cov qub - thiab nrog rau qhov sib ntxiv ntawm hnub tim, kom nws tuaj yeem pom thaum cov kev hloov pauv tshwj xeeb no. ua.
Kauj ruam 1. Ntxiv hnub tim rau cov lus nug thawj
Cia peb qhib kev thov Daim ntawv thovimporting peb cov ntaub ntawv los ntawm Tau qhov twg los, thiab ntxiv ib kab nrog rau hnub-lub sij hawm hloov tshiab rau nws. Txhawm rau ua qhov no, koj tuaj yeem siv lub pob Kev cai kem tab Ntxiv ib kab (Ntxiv kab - Custom column), thiab tom qab ntawd nkag mus rau qhov ua haujlwm DateTime.LocalNow - analogue ntawm kev ua haujlwm Lub TDATA (Tam sim no) hauv Microsoft Excel:
Tom qab txhaj rau OK koj yuav tsum xaus nrog kab zoo nkauj zoo li no (tsis txhob hnov qab teeb tsa hnub-lub sijhawm hom ntawv rau nws nrog lub cim hauv kab ntawv header):
Yog tias koj xav tau, tom qab ntawd rau lub phaj muab tso rau hauv daim ntawv rau kab ntawv no, koj tuaj yeem teeb tsa hnub-lub sij hawm hom nrog vib nas this kom raug ntau dua (koj yuav tau ntxiv cov nyuv thiab "ss" rau cov qauv qauv):
Kauj ruam 2: Nug cov ntaub ntawv qub
Tam sim no cia peb tsim lwm cov lus nug uas yuav ua raws li qhov tsis zoo uas txuag cov ntaub ntawv qub ua ntej hloov kho. Xaiv ib lub xovtooj ntawm lub rooj tshwm sim hauv cov ntaub ntawv receiver, xaiv ntawm tab Cov ntaub ntawv Hais kom Los ntawm Table/Range (Cov ntaub ntawv - Los ntawm lub rooj / ntau) or Nrog nplooj (Los ntawm daim ntawv):
Peb tsis ua dab tsi nrog lub rooj loaded hauv Power Query, peb hu cov lus nug, piv txwv li, cov ntaub ntawv qub thiab nias Tsev — Kaw thiab thauj khoom — Kaw thiab thauj mus rau… — Tsim kev sib txuas nkaus xwb (Lub Tsev - Kaw & Load - Kaw & Load rau… - Tsuas yog tsim kev sib txuas).
Kauj ruam 3. Koom nrog cov ntaub ntawv qub thiab tshiab
Tam sim no rov qab mus rau peb cov lus nug qub Daim ntawv thov thiab ntxiv rau nws los ntawm hauv qab cov ntaub ntawv qub los ntawm kev thov tsis pub dhau los nrog cov lus txib Tsev — Ntxiv Thov (Lub Tsev - Ntxiv Cov Lus Nug):
Yog tag nrho!
Nws tseem yuav rov qab mus rau Excel dhau los Tsev - Kaw thiab rub tawm (Lub Tsev - Kaw & Load) thiab sim ob peb zaug los hloov kho peb cov qauv tag nrho nrog lub pob Hloov Tag Nrho tab Cov ntaub ntawv (Cov ntaub ntawv - Refresh All). Nrog rau txhua qhov hloov tshiab, cov ntaub ntawv tshiab yuav tsis hloov cov ntaub ntawv qub, tab sis yuav thawb nws hauv qab no, khaws tag nrho cov ntaub ntawv hloov tshiab:
Ib qho kev ua kom zoo sib xws tuaj yeem siv tau thaum importing los ntawm ib qho chaw sab nraud (Internet sites, databases, sab nraud cov ntaub ntawv, thiab lwm yam) kom khaws cov txiaj ntsig qub rau keeb kwm yog tias koj xav tau.
- Pivot rooj hla ntau cov ntaub ntawv ntau yam
- Sib sau cov rooj los ntawm cov ntaub ntawv sib txawv siv Power Query
- Sau cov ntaub ntawv los ntawm txhua nplooj ntawv ntawm phau ntawv rau hauv ib lub rooj