Kuphatikiza kwa data mu Excel - momwe mungachitire, ndi zofunikira pa matebulo

Kuphatikiza kwa Data ndi gawo mu Excel, chifukwa chomwe ogwiritsa ntchito ali ndi mwayi wophatikiza deta kuchokera pamatebulo angapo kukhala amodzi, komanso kuphatikiza mapepala omwe ali m'mafayilo omwewo kapena osiyana kukhala amodzi.

Zofunikira zovomerezeka pamatebulo omwe ali ofunikira kuti amalize kuphatikiza

Njira yotchedwa "Consolidate" siigwira ntchito ngati matebulo sakukwaniritsa zofunikira. Kuti mumalize bwino njira yolumikizira deta, muyenera:

• yang'anani tebulo la mizere yopanda kanthu/mizere ndikuchotsa ngati ilipo;
• gwiritsani ntchito ma templates omwewo;
• tsatirani mayina a mizati, asasiyane.

Njira Zophatikizira Zoyambira mu Excel

Mukasonkhanitsa deta kuchokera ku malipoti osiyanasiyana, matebulo, mitundu yamtundu womwewo kukhala fayilo imodzi wamba, mutha kugwiritsa ntchito njira zingapo. Njira ziwiri zazikulu zofotokozera mwachidule deta zidzakambidwa pansipa: ndi udindo ndi gulu.

• M'mitundu yoyamba, deta yomwe ili m'magawo oyambirira ili mu dongosolo lomwelo momwe malemba ofanana amayikidwa. Pindani ndi udindo kuti muphatikize deta kuchokera ku mapepala a 3-4 omwe amachokera ku template yomweyi, mwachitsanzo, ndondomeko zachuma ndizoyenera kufufuza njira iyi.
• Muchisankho chachiwiri: deta ili mwachisawawa, koma khalani ndi zilembo zofanana. Gwirizanitsani ndi gulu kuti muphatikize deta kuchokera pamapepala angapo ogwirira ntchito okhala ndi masanjidwe osiyanasiyana koma zolemba zofanana.

Zofunika! Njirayi ikufanana kwambiri ndi kupanga tebulo la pivot. Komabe, mutha kukonzanso magulu mu PivotTable. 

• Palinso njira yachitatu yophatikizira deta - uku ndikuphatikizana pogwiritsa ntchito njira. Zowona, sizimagwiritsidwa ntchito kawirikawiri, chifukwa zimatenga nthawi yambiri kuchokera kwa wogwiritsa ntchito.

Malangizo apang'onopang'ono opangira kuphatikiza mu Excel

Kenako, tikambirana njira yosavuta komanso yachangu kwambiri yophatikizira.

Kotero, momwe mungagwirizane ndi ma tebulo ambiri:

1. Choyamba muyenera kupanga pepala latsopano, pambuyo pake pulogalamuyo idzangowonjezera kumanja. Ngati ndi kotheka, mukhoza kukokera pepala kumalo ena (mwachitsanzo, mpaka kumapeto kwa mndandanda) pogwiritsa ntchito batani lakumanzere.
2. Pepala lowonjezeredwa, imirirani mu cell yomwe muti mugwire nayo ntchito. Kenako pitani ku tabu "Data", pezani gawo la "Kugwira ntchito ndi data", dinani chinthucho "Consolidation".
3. A yaing'ono zoikamo zenera adzaoneka pa polojekiti.
4. Kenako, muyenera kusankha ntchito yoyenera kuphatikiza deta.
5. Mukasankha ntchito, pitani kumunda wa "Link" podina mkati mwake. Apa muyenera kusankha angapo maselo mmodzimmodzi. Kuti muchite izi, choyamba sinthani ku pepala ndi mbale yoyamba.
6. Kenako sankhani mbale pamodzi ndi mutu. Onetsetsani kuti zonse zachitika molondola, kenako dinani "Add" mafano. Mwa njira, mutha kusintha / kusintha zolumikizira nokha pogwiritsa ntchito kiyibodi, koma izi ndizosasangalatsa.
7. Kuti musankhe chikalata chatsopano, tsegulani kaye mu Excel. Pambuyo pake, yambani kugwirizanitsa m'buku loyamba ndikusintha kwachiwiri, sankhani pepala loyenera mmenemo, ndiyeno sankhani gawo lina la maselo.
8. Zotsatira zake, kulowa koyamba kudzapangidwa mu "List of Ranges".
9. Bwererani kumunda wa "Link", chotsani zidziwitso zonse zomwe zilimo, kenaka yikani zolumikizira za mbale zotsalira pamndandanda wamagawo.
10. Chongani mabokosi pafupi ndi ntchito zotsatirazi: "Zapamwamba Mizere Label", "Kumanzere Column Values", "Pangani Maulalo ku Source Data".
11. Kenako dinani "Chabwino".
12. Excel ichita njirayi ndikupanga chikalata chatsopano molingana ndi magawo osankhidwa ndi ntchito zomwe zasankhidwa.

Mu chitsanzo, kulumikiza kunasankhidwa, kotero zotulukazo zidayikidwa m'magulu kuti zithandizire kuwonetsa / kubisa tsatanetsatane.

Zambiri zokhudzana ndi kugwiritsa ntchito masanjidwe, kuwonjezera ndi kuchotsa maulalo

• Kuti mugwiritse ntchito mndandanda watsopano pakuphatikiza deta, muyenera kusankha njira ya "Consolidate", dinani pa "Link" ndikusankha mtunduwo kapena ikani ulalo. Mukadina batani la "Add", ulalo udzawonekera pamndandanda wamitundu.
• Kuti muchotse ulalo, sankhani ndikudina "Chotsani".
• Kuti musinthe ulalo, sankhani pamndandanda wamitundu. Idzawonekera m'munda wa "Link", kumene ikhoza kusinthidwa. Pambuyo pazosintha zomwe zachitika, dinani batani "Add". Kenako chotsani mtundu wakale wa ulalo wosinthidwa.

Kuphatikizika kwa data kumathandizira kuphatikiza zofunikira zomwe sizipezeka m'matebulo ndi masamba osiyanasiyana, komanso mafayilo ena (mabuku). Njira yosakaniza sitenga nthawi yochuluka ndipo n'zosavuta kuyamba kugwiritsa ntchito malangizo a sitepe ndi sitepe.