Hanyar 1. Formules
Bari mu fara, don dumama, tare da zaɓi mafi sauƙi - dabaru. Idan muna da ƙaramin tebur wanda aka jera ta kwanan wata azaman shigarwa, sannan don ƙididdige jimlar gudana a cikin wani ginshiƙi daban, muna buƙatar dabara ta farko:
Babban fasalin a nan shi ne gyare-gyare mai banƙyama na kewayon cikin aikin SUM - ana yin la'akari da farkon kewayon cikakke (tare da alamun dollar), kuma zuwa ƙarshe - dangi (ba tare da daloli ba). Dangane da haka, lokacin da ake kwafin dabarar zuwa gabaɗayan ginshiƙi, muna samun kewayon faɗaɗawa, adadin abin da muke ƙididdige shi.
Abubuwan da ke cikin wannan hanyar a bayyane suke:
- Dole ne a jera tebur ta kwanan wata.
- Lokacin ƙara sabbin layuka tare da bayanai, ƙirar za a ƙara da hannu.
Hanyar 2. Pivot tebur
Wannan hanyar ta ɗan fi rikitarwa, amma ta fi daɗi. Kuma don ta'azzara, bari mu yi la'akari da matsala mai tsanani - tebur na layuka 2000 na bayanai, inda babu rarrabuwa ta ginshiƙin kwanan wata, amma akwai maimaitawa (watau za mu iya siyar da sau da yawa a rana ɗaya):
Muna juyar da teburin mu na asali zuwa gajeriyar hanyar madannai ta “smart” (tsauri). Ctrl+T ko tawaga Gida - Tsarin azaman tebur (Gida - Tsarin azaman Tebur), sa'an nan kuma mu gina pivot tebur akan shi tare da umarnin Saka - PivotTable (Saka - Tebur Pivot). Mun sanya kwanan wata a cikin layuka a cikin taƙaitaccen bayani, da adadin kayan da aka sayar a cikin yanki mai daraja:
Da fatan za a lura cewa idan kuna da tsohuwar sigar Excel wacce ba ta daɗe ba, to ana tara kwanakin ta atomatik ta shekaru, kwata da watanni. Idan kuna buƙatar ƙungiyoyi daban-daban (ko ba ku buƙatar ta kwata-kwata), to zaku iya gyara ta ta danna-dama akan kowace rana kuma zaɓi umarni. Ƙungiya / Ƙungiya (Gungiya / Ƙungiya).
Idan kuna son ganin duka jimlar sakamakon ta lokaci da jimlar gudana a cikin wani ginshiƙi daban, to yana da ma'ana don jefa filin cikin ƙimar ƙimar. Saya sake don samun kwafin filin - a ciki za mu kunna nunin jimlar gudu. Don yin wannan, danna-dama akan filin kuma zaɓi umarnin Ƙarin Lissafi - Jimillar Tarin (Nuna Darajoji azaman - Jimlar Gudu):
A can kuma za ku iya zaɓar zaɓi na girma jimlar a matsayin kashi, kuma a cikin taga na gaba kuna buƙatar zaɓar filin da tarin zai tafi - a cikin yanayinmu, wannan shine filin kwanan wata:
Amfanin wannan hanyar:
- Ana karanta babban adadin bayanai da sauri.
- Babu dabara da ake buƙatar shigar da su da hannu.
- Lokacin canzawa a cikin bayanan tushen, ya isa ya sabunta taƙaice tare da maɓallin linzamin kwamfuta na dama ko tare da Bayanai - Refresh All umarni.
Rashin hasara ya biyo bayan gaskiyar cewa wannan taƙaitaccen abu ne, wanda ke nufin cewa ba za ku iya yin duk abin da kuke so a ciki ba (saka layi, rubuta tsarin ƙira, gina kowane zane, da dai sauransu) ba zai ƙara yin aiki ba.
Hanyar 3: Tambayar Wuta
Bari mu loda tebur ɗin mu na “smart” tare da bayanan tushe a cikin editan Query Query ta amfani da umarnin Bayanai - Daga Tebur / Range (Bayanai - Daga Tebura/Range). A cikin sabbin sigogin Excel, ta hanyar, an sake masa suna - yanzu ana kiran shi Tare da ganye (Daga Sheet):
Sannan za mu aiwatar da matakai masu zuwa:
1. Tsara tebur a tsarin hawan sama ta ginshiƙin kwanan wata tare da umarni Tsara hawa a cikin jerin zazzagewar tacewa a cikin taken tebur.
2. Bayan ɗan lokaci, don ƙididdige jimlar gudu, muna buƙatar ginshiƙi na taimako tare da lambar jere na yau da kullun. Bari mu ƙara shi tare da umarni Ƙara Rukunin - Rukunin Fihirisa - Daga 1 (Ƙara shafi - Shagon Fihirisa - Daga 1).
3. Har ila yau, don ƙididdige jimlar gudana, muna buƙatar tunani zuwa shafi Saya, inda mu takaitaccen bayanin ya ta'allaka ne. A cikin Tambayoyin Wutar Lantarki, ana kuma kiran ginshiƙan lissafin (jeri) kuma don samun hanyar haɗin kai zuwa gare ta, danna dama akan taken shafi kuma zaɓi umarnin. Cikakkun bayanai (Nuna cikakken bayani). Maganar da muke buƙata za ta bayyana a cikin ma'aunin dabara, wanda ya ƙunshi sunan matakin da ya gabata #"An ƙara index", Daga inda muka dauki tebur da sunan shafi [Sales] daga wannan tebur a maƙallan murabba'i:
Kwafi wannan magana zuwa allo don ƙarin amfani.
4. Share matakin karshe mara amfani Saya kuma ƙara maimakon ginshiƙi ƙididdigewa don ƙididdige jimlar gudana tare da umarnin Ƙara ginshiƙi - Shagon Al'ada (Ƙara shafi - ginshiƙi na al'ada). Tsarin da muke buƙata zai yi kama da haka:
Anan aikin Jerin.Range yana ɗaukar jerin asali (column [Sales]) kuma yana fitar da abubuwa daga gare ta, yana farawa daga farko (a cikin dabarar, wannan shine 0, tunda lambobi a cikin Query Query yana farawa daga sifili). Adadin abubuwan da za a dawo dasu shine lambar layin da muke ɗauka daga ginshiƙi [Index]. Don haka wannan aikin na layin farko yana mayar da tantanin halitta na farko ne kawai na ginshiƙi Saya. Don layi na biyu - riga na farko guda biyu, na uku - uku na farko, da dai sauransu.
To, sai aikin Lissafi.Tam yana taƙaita ƙimar da aka fitar kuma muna samun a cikin kowane jere jimlar duk abubuwan da suka gabata, watau jimlar jimlar:
Ya rage don share ginshiƙin Fihirisar da ba mu buƙata kuma mu mayar da sakamakon zuwa Excel tare da Gida - Kusa & Load don umarni.
An warware matsalar.
Fast da Furious
A ka'ida, ana iya dakatar da wannan, amma akwai ƙaramin gardama a cikin maganin shafawa - buƙatar da muka ƙirƙira yana aiki a cikin saurin kunkuru. Misali, akan PC na ba mafi rauni ba, tebur na layuka 2000 kawai ana sarrafa shi cikin daƙiƙa 17. Idan akwai ƙarin bayanai fa?
Don haɓakawa, zaku iya amfani da buffering ta amfani da aikin List.Buffer na musamman, wanda ke ɗaukar jerin (jeri) da aka ba shi azaman gardama a cikin RAM, wanda ke saurin saurin samun damar zuwa gaba. A cikin yanayinmu, yana da ma'ana don adana jerin #"Ƙara bayanin ƙididdiga"[Sold], wanda Query Query ya sami damar shiga yayin ƙididdige jimlar gudana a cikin kowane jeri na tebur ɗin mu mai jere 2000.
Don yin wannan, a cikin editan Query akan Babban shafin, danna maɓallin Babban Edita (Gida – Babban Edita) don buɗe lambar tushe ta tambayar mu a cikin harshen M da aka gina cikin Query Query:
Sannan ƙara layi mai ma'ana a can MyList, darajar wanda aikin buffering ya dawo da shi, kuma a mataki na gaba muna maye gurbin kira zuwa jeri tare da wannan m:
Bayan yin waɗannan canje-canje, tambayar mu za ta yi sauri sosai kuma za ta jimre da tebur mai jere 2000 a cikin daƙiƙa 0.3 kacal!
Wani abu, dama? 🙂
- Taswirar Pareto (80/20) da yadda ake gina shi a cikin Excel
- Neman mahimmin kalma a cikin rubutu da buffering na tambaya a cikin Query Query