Tablo pivot yo bon pou tout moun - yo kalkile byen vit, epi yo konfigirasyon fleksib, ak konsepsyon an ka blese nan yo elegant, si sa nesesè. Men, gen tou yon vole kèk nan odè a, an patikilye, enkapasite a yo kreye yon rezime, kote zòn nan valè pa ta dwe gen nimewo, men tèks.
Ann eseye evite limit sa a epi vini ak yon "koup beki" nan yon sitiyasyon ki sanble.
Sipoze konpayi nou an transpòte pwodwi li yo nan resipyan nan plizyè vil nan peyi nou an ak Kazakhstan. Yo voye veso yo pa plis pase yon fwa pa mwa. Chak veso gen yon nimewo alfanumerik. Kòm premye done, gen yon tablo estanda ki bay lis livrezon, ki soti nan ki ou bezwen fè kèk kalite rezime yo nan lòd yo wè aklè kantite resipyan yo voye nan chak vil ak chak mwa:
Pou konvenyans, ann fè tab la ak premye done yo "entelijan" davans lè l sèvi avèk lòd la Kay - Fòma kòm yon tab (Kay — Fòma kòm Tablo) epi ba li yon non Livrezon tab Constructor (Design). Nan tan kap vini an, sa a pral senplifye lavi, paske. li pral posib pou itilize non tab la ak kolòn li yo dirèkteman nan fòmil yo.
Metòd 1. Pi fasil la - itilize Power Query
Power Query se yon zouti super pwisan pou chaje ak transfòme done nan Excel. Add-in sa a te bati nan Excel pa default depi 2016. Si ou gen Excel 2010 oswa 2013, ou ka telechaje ak enstale li separeman (konplètman gratis).
Tout pwosesis la, pou klè, mwen analize etap pa etap nan videyo sa a:
Si li pa posib pou itilize Power Query, Lè sa a, ou ka ale nan lòt fason - atravè yon tab pivot oswa fòmil.
Metòd 2. Rezime oksilyè
Ann ajoute yon lòt kolòn nan tablo orijinal nou an, kote lè l sèvi avèk yon fòmil senp nou kalkile kantite chak ranje nan tablo a:
Li evidan, -1 nesesè, paske nou gen yon header yon sèl liy nan tablo nou an. Si tab ou a pa nan kòmansman fèy la, Lè sa a, ou ka itilize yon fòmil yon ti kras pi konplèks, men inivèsèl ki kalkile diferans ki genyen nan nimewo yo nan ranje aktyèl la ak tèt tab la:
Koulye a, nan yon fason estanda, nou pral bati yon tablo pivot nan kalite a vle ki baze sou done nou an, men nan jaden an valè nou pral lage jaden an. Nimewo liy lan olye de sa nou vle veso:
Piske nou pa gen plizyè resipyan nan menm vil la nan menm mwa a, rezime nou an ap bay non kantite lajan an, men nimewo liy resipyan nou bezwen yo.
Anplis de sa, ou ka fèmen gran ak subtotals sou tab la Konstriktè - total jeneral и Sou-total (Konsepsyon — Gwo Total, Subtotal) ak nan menm kote a chanje rezime a nan yon Layout tab ki pi pratik ak bouton an Rapòte mockup (Rapò Layout).
Kidonk, nou deja mwatye nan rezilta a: nou gen yon tab kote, nan entèseksyon vil la ak mwa, gen yon nimewo ranje nan tablo sous la, kote kòd veso nou bezwen an manti.
Koulye a, ann kopye rezime a (nan menm fèy la oswa nan yon lòt) epi kole li kòm valè, epi antre fòmil nou an nan zòn valè a, ki pral ekstrè kòd veso a pa nimewo liy yo jwenn nan rezime a:
fonksyon IF (SI), nan ka sa a, tcheke si selil kap vini an nan rezime a pa vid. Si li vid, lè sa a soti yon kòd tèks vid "", sa vle di kite selil la vid. Si ou pa vid, Lè sa a, ekstrè soti nan kolòn nan Veso tab sous Livrezon kontni selil pa nimewo ranje lè l sèvi avèk fonksyon Main Index (ENDEX).
Petèt sèlman pwen an pa trè evidan isit la se mo a doub Veso nan fòmil la. Tankou yon fòm ekriti etranj:
Pwodwi pou[[Venè]:[Venè]]
… li nesesè sèlman pou referans kolòn nan Veso te absoli (tankou yon referans ak siy $ pou tab òdinè "ki pa entelijan") epi li pa glise nan kolòn vwazen lè w kopye fòmil nou an sou bò dwat la.
Nan tan kap vini an, lè chanje done yo nan tab la sous Livrezon, nou dwe sonje mete rezime oksilyè nou an ak nimewo liy lè nou klike sou li epi chwazi kòmandman an. Mete ajou & Sove (Rafrechi).
Metòd 3. Fòmil
Metòd sa a pa mande pou kreye yon tablo pivot entèmedyè ak aktyalizasyon manyèl, men li sèvi ak "zam lou" Excel la - fonksyon an. SUMMESLIMN (SUMIFS). Olye pou w chèche nimewo ranje nan yon rezime, ou ka kalkile yo lè l sèvi avèk fòmil sa a:
Avèk kèk bulkiness ekstèn, an reyalite, sa a se yon ka itilize estanda pou fonksyon an somasyon selektif SUMMESLIMNA ki sòm nimewo ranje yo pou vil la ak mwa yo bay la. Yon fwa ankò, piske nou pa gen plizyè resipyan nan menm vil la nan menm mwa a, fonksyon nou an pral, an reyalite, bay soti pa kantite lajan an, men nimewo liy lan tèt li. Lè sa a, fonksyon an deja abitye nan metòd anvan an Main Index Ou kapab tou ekstrè kòd veso yo:
Natirèlman, nan ka sa a, ou pa bezwen panse ankò sou mete ajou rezime a, men sou gwo tab, fonksyon an. SUMMESLI ka ralanti. Lè sa a, ou pral oblije fèmen otomatik aktyalizasyon fòmil yo, oswa itilize premye metòd la - yon tab pivot.
Si aparans nan rezime a pa trè apwopriye pou rapò ou a, Lè sa a, ou ka ekstrè nimewo ranje soti nan li nan tablo final la pa dirèkteman, jan nou te fè, men lè l sèvi avèk fonksyon an. JWENN.TABLE.PIVOT.DONE (JWENN.PIVOT.DONE). Ki jan yo fè sa ka jwenn isit la.
- Ki jan yo kreye yon rapò lè l sèvi avèk yon tablo pivot
- Ki jan yo mete kalkil nan tablo pivot
- Konte selektif ak SUMIFS, COUNTIFS, elatriye.