Matafura epivot akanakira munhu wese - anoverengera nekukurumidza, uye anogadziriswa achichinjika, uye dhizaini inogona kukuvadzwa mukati mawo zvine mutsindo, kana zvichidikanwa. Asi kunewo zvishomanana zvinobhururuka mumafuta, kunyanya, kusakwanisa kugadzira pfupiso, apo nzvimbo inokosha haifaniri kuva nenhamba, asi zvinyorwa.
Ngatiedzei kutenderera ichi chipimo uye tiuye ne "madondoro maviri" mumamiriro akafanana.
Ngatitii kambani yedu inotakura zvigadzirwa zvayo mumidziyo kuenda kumaguta akati wandei muNyika Yedu neKazakhstan. Zvigubhu zvinotumirwa kwete kanopfuura kamwe chete pamwedzi. Chigaba chimwe nechimwe chine nhamba yealphanumeric. Sedhata rekutanga, kune yakajairwa tafura yerondedzero yekutumira, kubva kwaunofanirwa kuita imwe mhando yepfupiso kuitira kuti uone zvakajeka huwandu hwemidziyo inotumirwa kuguta rega rega uye mwedzi wega wega:
Kuti zvive nyore, ngatigadzirire tafura ine yekutanga data "smart" pachine nguva tichishandisa murairo Kumba – Fomati setafura (Kumba - Fomati seTafura) uye umupe zita Anonunura Tab Constructor (Dhizaini). Mune ramangwana, izvi zvicharerutsa hupenyu, nekuti. zvichave zvichigoneka kushandisa zita retafura uye makoramu ayo zvakananga mumafomula.
Nzira 1. Iyo iri nyore - shandisa Power Query
Power Query chishandiso chine simba kwazvo chekurodha nekushandura data muExcel. Iyi yekuwedzera yakavakwa muExcel nekukasira kubva 2016. Kana uine Excel 2010 kana 2013, unogona kudhawunirodha nekuiisa zvakasiyana (yakasununguka zvachose).
Maitiro ese, kuti ajekese, ndakaongorora nhanho nhanho muvhidhiyo inotevera:
Kana zvisingaite kushandisa Power Query, saka unogona kuenda nedzimwe nzira - kuburikidza netafura yepivot kana mafomula.
Nzira 2. Pfupiso yebetsero
Ngatiwedzerei imwe mbiru patafura yedu yepakutanga, patinoshandisa fomula iri nyore tinoverenga nhamba yemutsara wega wega patafura:
Zviripachena, -1 inodiwa, nekuti isu tine-mutsara wemusoro mutafura yedu. Kana tafura yako isiri pakutanga kwepepa, saka iwe unogona kushandisa zvishoma zvakanyanya kuomarara, asi yepasirese formula inoverenga mutsauko muhuwandu hweiyo mutsara uye musoro wetafura:
Zvino, nenzira yakajairwa, isu tichavaka tafura yepivot yerudzi rwaunoda zvichibva pane yedu data, asi mundima yekukosha tinodonha munda. Mutsara nhamba pachinzvimbo chezvatinoda mudziyo:
Sezvo isu tisina midziyo yakati wandei muguta rimwe chete mumwedzi mumwe chete, pfupiso yedu, muchokwadi, ichapa kwete huwandu, asi nhamba dzemitsara dzemidziyo yatinoda.
Pamusoro pezvo, iwe unogona kudzima makuru uye subtotals pane iyo tab Muvaki – General totals и Subtotals (Design - Grand Totals, Subtotals) uye panzvimbo imwe chete shandura pfupiso yacho kune imwe yakanakira tafura marongero nebhatani Report mockup (Report Layout).
Nekudaro, isu tatove hafu yemhedzisiro: isu tine tafura apo, pamharadzano yeguta nemwedzi, pane mutsara nhamba mune tafura yekubva, uko kodhi yemidziyo yatinoda inonyepa.
Zvino ngatikopei pfupiso (kune bepa rimwe chete kana imwe) toiisa semakoshero, tobva taisa fomula yedu munzvimbo yakakosha, iyo inoburitsa kodhi yemudziyo nenhamba yemutsetse inowanikwa muchidimbu:
basa IF (KANA), mune iyi kesi, inotarisa kuti sero rinotevera muchidimbu harina chinhu. Kana isina chinhu, wobva waburitsa tambo isina chinhu "", kureva kusiya sero isina chinhu. Kana isina chinhu, bvisa kubva mukoramu Container source table Anonunura sero zvemukati nemutsara nhamba uchishandisa basa INDEX (INDEX).
Zvichida iyo chete isiri pachena pfungwa pano ishoko rechipiri Container mufomula. Manyorero anoshamisa akadaro:
Supplies[[mudziyo]:[mudziyo]]
... inongodiwa chete kuratidza iyo column Container yakanga iri mhedziso (sereferensi ine masaini emadhora ematafura akajairwa “asina hungwaru”) uye haina kutsvedza kumakoramu akavakidzana pakukopa fomula yedu kurudyi.
Mune ramangwana, pakuchinja data mune tafura yekubva Anonunura, tinofanira kuyeuka kugadzirisa pfupiso yedu yebetsero nenhamba dzemutsara nekudzvanya-kurudyi pairi uye kusarudza murairo. Gadziridza & Chengeta (Refresh).
Nzira 3. Zvirongwa
Iyi nzira haidi kugadzirwa kwetafura yepakati pivot uye kuvandudzwa kwebhuku, asi inoshandisa "chombo chinorema" cheExcel - basa. SUMMESLIMN (SUMIFS). Panzvimbo pekutsvaga nhamba dzemitsara muchidimbu, unogona kudziverenga uchishandisa iyi formula:
Nekumwe kwekunze bulkiness, kutaura zvazviri, iyi ndiyo yakajairwa yekushandisa kesi yeiyo yakasarudzika kuita basa SUMMESLIMNA inoverengera nhamba dzemitsara yeguta nemwedzi zvakapihwa. Zvekare, sezvo tisina midziyo yakati wandei muguta rimwe chete mumwedzi mumwe chete, basa redu, muchokwadi, rinozopa kwete huwandu, asi nhamba yemutsara pachayo. Uye ipapo basa rave kujairana kubva yapfuura nzira INDEX Iwe unogona zvakare kubvisa makodhi makodhi:
Ehe, mune iyi kesi, iwe hauchafanirwa kufunga nezve kuvandudza pfupiso, asi pamatafura makuru, basa racho. SUMMESLI inogona kunonoka zvinooneka. Ipapo iwe uchafanirwa kudzima otomatiki gadziriso yemafomula, kana kushandisa yekutanga nzira - tafura yepivot.
Kana kutaridzika kwechidimbu kusinganyatsokodzere chirevo chako, saka unogona kubvisa nhamba dzemitsara kubva mairi kuenda patafura yekupedzisira kwete zvakananga, sezvatakaita, asi uchishandisa basa. GET.PIVOT.TABLE.DATA (GET.PIVOT.DATA). Maitiro ekuita izvi anogona kuwanikwa pano.
- Maitiro ekugadzira report uchishandisa pivot tafura
- Maitiro ekuseta macalculation mumatafura epivot
- Kuverenga kwakasarudzika neSUMIFS, COUNTIFS, nezvimwe.