Zamkatimu
Matebulo a pivot ndi abwino kwa aliyense - amawerengera mwachangu, ndipo amakonzedwa mosinthika, ndipo kapangidwe kake kakhoza kupangidwa mwaluso, ngati pakufunika. Koma palinso ntchentche zochepa mu mafuta odzola, makamaka, kulephera kupanga chidule, kumene malo amtengo wapatali sayenera kukhala ndi manambala, koma malemba.
Tiyeni tiyesetse kuthana ndi malire awa ndikupeza “ndodo zingapo” mumkhalidwe wofananawo.
Tiyerekeze kuti kampani yathu imanyamula katundu wake m'makontena kupita kumizinda ingapo ya Dziko Lathu ndi Kazakhstan. Zotengera zimatumizidwa osapitilira kamodzi pamwezi. Chidebe chilichonse chili ndi nambala ya alphanumeric. Monga zidziwitso zoyambira, pali mndandanda wazomwe zimaperekedwa, pomwe muyenera kupanga chidule chamtundu wina kuti muwone bwino kuchuluka kwa zotengera zomwe zimatumizidwa ku mzinda uliwonse komanso mwezi uliwonse:
Kuti zikhale zosavuta, tiyeni tipange tebulo ndi deta yoyambirira "yanzeru" pasadakhale pogwiritsa ntchito lamulo Kunyumba - Pangani ngati tebulo (Kunyumba - Pangani Monga Table) ndi kumupatsa dzina Kuperekedwa tsamba Constructor (Kapangidwe). M'tsogolomu, izi zipangitsa moyo kukhala wosalira zambiri, chifukwa. zidzatheka kugwiritsa ntchito dzina la tebulo ndi mizati yake mwachindunji mu ndondomeko.
Njira 1. Chophweka - gwiritsani ntchito Power Query
Power Query ndi chida champhamvu kwambiri chotsitsa ndikusintha deta mu Excel. Zowonjezera izi zamangidwa mu Excel mwachisawawa kuyambira 2016. Ngati muli ndi Excel 2010 kapena 2013, mukhoza kukopera ndikuyiyika padera (yaulere kwathunthu).
Njira yonse, kuti imveke bwino, ndidasanthula pang'onopang'ono muvidiyoyi:
Ngati sizingatheke kugwiritsa ntchito Power Query, ndiye kuti mutha kupita njira zina - kudzera pa tebulo la pivot kapena mafomu.
Njira 2. Chidule chothandizira
Tiyeni tiwonjezere ndime ina patebulo lathu loyambirira, pomwe pogwiritsa ntchito njira yosavuta timawerengera nambala ya mzere uliwonse patebulo:
Mwachiwonekere, -1 ndiyofunikira, chifukwa tili ndi mutu wa mzere umodzi patebulo lathu. Ngati tebulo lanu silili koyambirira kwa pepala, ndiye kuti mutha kugwiritsa ntchito njira yovuta kwambiri, koma yapadziko lonse lapansi yomwe imawerengera kusiyana kwa manambala a mzere wapano ndi mutu wa tebulo:
Tsopano, mwanjira yokhazikika, tidzapanga tebulo la pivot la mtundu womwe tikufuna kutengera zomwe tikufuna, koma m'munda wamtengo wapatali tidzagwetsa gawolo. Nambala ya mzere m'malo mwa zomwe tikufuna chophimba:
Popeza tilibe zotengera zingapo mumzinda womwewo m'mwezi womwewo, chidule chathu sichidzapereka kuchuluka kwake, koma manambala amizere yazotengera zomwe tikufuna.
Kuphatikiza apo, mutha kuzimitsa zazikulu ndi subtotals pa tabu Womanga - Ziwerengero zonse и Zolemba (Kupanga - Magulu Akuluakulu, Ma Subtotals) ndipo pamalo omwewo sinthani chidulecho kuti chikhale chosavuta patebulo ndi batani Nenani mongoyerekeza (Kapangidwe ka lipoti).
Chifukwa chake, tili kale theka lazotsatira: tili ndi tebulo pomwe, pamzere wa mzinda ndi mwezi, pali nambala ya mzere pagome loyambira, pomwe chidebe chomwe timafunikira chagona.
Tsopano tiyeni tikopere chidulecho (patsamba lomwelo kapena china) ndikuchiyika ngati mfundo, kenako lowetsani fomula yathu mugawo lamtengo wapatali, lomwe lidzatulutse nambala yachidebe ndi nambala ya mzere yomwe ikupezeka mwachidule:
ntchito IF (NGATI), pamenepa, imayang'ana kuti selo lotsatira muchidule liribe kanthu. Ngati mulibe, tulutsani chingwe chopanda kanthu "", mwachitsanzo, siyani m'chipindacho mulibe kanthu. Ngati mulibe kanthu, chotsani kuchokera pamndandanda Chotsitsa gwero tebulo Kuperekedwa zomwe zili mu cell ndi nambala ya mzere pogwiritsa ntchito ntchito INDEX (INDEX).
Mwina mfundo yosadziwika bwino apa ndi mawu awiri Chotsitsa mu formula. Kulemba kwachilendo chotere:
Zothandizira[[Chotengera]:[Chotengera]]
… zimangofunika kutchula gawoli Chotsitsa zinali mtheradi (monga mawu osonyeza $ $ pamagome wamba "osakhala anzeru") ndipo sizinasunthike kupita kumagulu oyandikana nawo pokopera fomula yathu kumanja.
M'tsogolomu, posintha deta mu tebulo lochokera Kuperekedwa, tiyenera kukumbukira kusinthira chidule chathu chothandizira ndi manambala a mzere podina kumanja ndikusankha lamulo Sinthani & Sungani (Bwezeretsani).
Njira 3. Zolemba
Njirayi sikutanthauza kupanga tebulo lapakati la pivot ndi kusinthidwa kwamanja, koma imagwiritsa ntchito "chida cholemera" cha Excel - ntchitoyo. SUMMESLIMN (SUMIFS). M'malo moyang'ana manambala amizere mwachidule, mutha kuwerengera pogwiritsa ntchito njira iyi:
Ndi kuchulukira kwina kwakunja, kwenikweni, iyi ndi njira yogwiritsiridwa ntchito yosankha yosankha SUMMESLIMNA yomwe imawerengera manambala amizere a mzinda ndi mwezi womwe wapatsidwa. Apanso, popeza tilibe zotengera zingapo mumzinda womwewo m'mwezi womwewo, ntchito yathu idzapereka osati kuchuluka kwake, koma nambala ya mzere womwewo. Ndiyeno ntchito kale bwino njira yapita INDEX Mukhozanso kuchotsa zizindikiro zotengera:
Zachidziwikire, pakadali pano, simuyeneranso kuganiza zosintha mwachidule, koma pamatebulo akulu, ntchitoyo. SUMMESLI ikhoza kukhala yochedwa kwambiri. Kenako muyenera kuzimitsa kusinthika kwamitundu, kapena kugwiritsa ntchito njira yoyamba - tebulo la pivot.
Ngati mawonekedwe achidule sali oyenera lipoti lanu, ndiye kuti mutha kuchotsa manambala amizere kuchokera patebulo lomaliza osati mwachindunji, monga tidachitira, koma pogwiritsa ntchito ntchitoyi. GET.PIVOT.TABLE.DATA (GET.PIVOT.DATA). Momwe mungachitire izi zitha kupezeka apa.
- Momwe mungapangire lipoti pogwiritsa ntchito tebulo la pivot
- Momwe mungakhazikitsire mawerengedwe mu pivot tables
- Kuwerengera kosankhidwa ndi SUMIFS, COUNTIFS, ndi zina.