Contents
Tebur na pivot yana da kyau ga kowa da kowa - suna ƙididdigewa da sauri, kuma an daidaita su da sassauƙa, kuma ƙirar za a iya raunata su da kyau, idan an buƙata. Amma kuma akwai 'yan tashi a cikin maganin shafawa, musamman, rashin iyawa don ƙirƙirar taƙaitaccen bayani, inda yankin darajar bai kamata ya ƙunshi lambobi ba, amma rubutu.
Bari mu yi ƙoƙari mu shawo kan wannan iyakance kuma mu fito da "ma'aurata biyu" a cikin irin wannan yanayi.
A ce kamfaninmu yana jigilar kayayyakinsa a cikin kwantena zuwa birane da yawa a cikin ƙasarmu da Kazakhstan. Ana aika kwantena ba fiye da sau ɗaya a wata ba. Kowane akwati yana da lambar haruffa. A matsayin bayanan farko, akwai daidaitaccen jerin abubuwan isarwa, wanda daga ciki kuna buƙatar yin wasu nau'ikan taƙaitawa don ganin a sarari lambobin kwantena da aka aika zuwa kowane birni da kowane wata:
Don saukakawa, bari mu yi tebur tare da bayanan farko “masu wayo” a gaba ta amfani da umarnin Gida - Tsarin azaman tebur (Gida - Tsarin azaman Tebur) kuma ya ba ta suna Ciyarwa tab Constructor (Zane). A nan gaba, wannan zai sauƙaƙa rayuwa, saboda. zai yiwu a yi amfani da sunan tebur da ginshiƙansa kai tsaye a cikin ƙididdiga.
Hanyar 1. Mafi sauƙi - yi amfani da Query Query
Tambayar Wuta shine babban kayan aiki mai ƙarfi don lodawa da canza bayanai a cikin Excel. An gina wannan add-in a cikin Excel ta tsohuwa tun 2016. Idan kana da Excel 2010 ko 2013, za ka iya saukewa kuma shigar da shi daban (cikakken kyauta).
Gabaɗayan tsarin, don tsabta, na yi nazarin mataki-mataki a cikin bidiyo mai zuwa:
Idan ba zai yiwu a yi amfani da Query Query ba, to, zaku iya zuwa wasu hanyoyi - ta hanyar tebur pivot ko dabaru.
Hanyar 2. Takaitaccen bayani
Bari mu ƙara ginshiƙi ɗaya zuwa teburin mu na asali, inda ta amfani da tsari mai sauƙi muna ƙididdige adadin kowane jere a cikin tebur:
Babu shakka, -1 ana buƙata, saboda muna da taken layi ɗaya a teburin mu. Idan teburin ku ba a farkon takardar ba, to, zaku iya amfani da ɗan ƙaramin ƙari, amma dabarar duniya wacce ke ƙididdige bambanci a cikin lambobi na jere na yanzu da taken tebur:
Yanzu, a daidaitaccen hanya, za mu gina pivot tebur na nau'in da ake so bisa ga bayananmu, amma a cikin darajar filin za mu sauke filin. Lambar layi maimakon abin da muke so akwati:
Tun da ba mu da kwantena da yawa a cikin birni ɗaya a cikin wata ɗaya, taƙaitawar mu, a gaskiya, ba za ta ba da adadin ba, amma lambobin layin kwantenan da muke buƙata.
Bugu da ƙari, zaku iya kashe manyan da jimloli akan shafin Constructor - Gabaɗaya jimlar и Ƙarfafa bayanai (Zane-Grand Totals, Subtotals) kuma a wuri guda canza taƙaitawar zuwa shimfidar tebur mafi dacewa tare da maɓallin Bayar da izgili (Tsarin Rahoto).
Don haka, mun rigaya zuwa rabin sakamakon: muna da tebur inda, a tsakiyar tsakiyar birnin da watan, akwai lambar layi a cikin tebur mai tushe, inda lambar akwati da muke bukata ta karya.
Yanzu bari mu kwafi summary (zuwa takarda ɗaya ko wani) mu liƙa a matsayin ƙima, sa'an nan kuma shigar da tsarin mu a cikin yankin darajar, wanda zai cire lambar kwantena ta lambar layin da aka samo a cikin taƙaitaccen bayanin:
aiki IF (IF), a wannan yanayin, bincika cewa tantanin halitta na gaba a cikin taƙaitaccen bayani ba komai bane. Idan komai, sai a fitar da fankon rubutu “”, watau barin tantanin halitta babu komai. Idan ba komai ba, to cire daga ginshiƙi Akwati tebur tushe Ciyarwa abun cikin tantanin halitta ta lambar jere ta amfani da aiki INDEX (INDEX).
Wataƙila kawai abin da ba a bayyane yake ba a nan shi ne kalmar biyu Akwati a cikin dabara. Irin wannan bakon nau'in rubutu:
Kayayyaki[[kwantena]:[kwantena]]
… ana buƙatar kawai don yin la'akari da shafi Akwati ya kasance cikakke (kamar nuni tare da alamun $ don tebur "marasa wayo" na yau da kullun) kuma bai zamewa zuwa ginshiƙan maƙwabta ba lokacin kwafin dabararmu zuwa dama.
A nan gaba, lokacin canza bayanai a cikin teburin tushen Ciyarwa, Dole ne mu tuna don sabunta taƙaitaccen bayanin mu tare da lambobin layi ta danna-dama akan shi kuma zaɓi umarnin Sabunta & Ajiye (Sake sabuntawa).
Hanyar 3. Formules
Wannan hanyar ba ta buƙatar ƙirƙirar tebur mai tsaka-tsaki da sabuntawa ta hannu, amma tana amfani da “makamai mai nauyi” na Excel - aikin. SUMMESLIMN (SUMIFS). Maimakon neman lambobin jere a taƙaice, zaku iya ƙididdige su ta amfani da wannan dabara:
Tare da wasu girman kai na waje, a zahiri, wannan daidaitaccen yanayin amfani ne don aikin taƙaitaccen zaɓi SUMMESLIMNA wanda ya haɗa lambobin jere na birni da watan da aka bayar. Har ila yau, tun da ba mu da kwantena da yawa a cikin birni ɗaya a cikin wata ɗaya, aikinmu ba zai ba da adadin ba, amma lambar layin kanta. Sannan aikin da ya riga ya saba daga hanyar da ta gabata INDEX Hakanan zaka iya cire lambobin kwantena:
Tabbas, a cikin wannan yanayin, ba kwa buƙatar yin tunani game da sabunta taƙaitawar, amma akan manyan tebur, aikin. SUMMESLI na iya zama sananne a hankali. Sa'an nan kuma dole ne ka kashe atomatik sabuntawa na dabara, ko amfani da hanyar farko - pivot tebur.
Idan bayyanar taƙaice ba ta dace da rahoton ku ba, to, zaku iya cire lambobin layi daga gare ta a cikin tebur na ƙarshe ba kai tsaye ba, kamar yadda muka yi, amma ta amfani da aikin. SAMU.PIVOT.TABLE.DATA (SAMU.PIVOT.DATA). Yadda za a yi wannan za a iya samu a nan.
- Yadda ake ƙirƙirar rahoto ta amfani da tebur pivot
- Yadda ake saita lissafi a cikin tebur pivot
- Ƙididdigar zaɓi tare da SUMIFS, COUNTIFS, da sauransu.