Contents
Samar da matsala
A ce muna da irin wannan tebur wanda dole ne mu "rawa" tare da kowace rana:
Ga wanda tebur ɗin ya yi ƙanƙanta - a hankali ninka shi sau ashirin ta yanki, yana ƙara wasu shingen biyu da dozin manyan biranen dozin biyu.
Ayyukan shine cirewa na ɗan lokaci daga layuka da ginshiƙan allo waɗanda a halin yanzu basu da buƙata don aiki, watau,
- boye bayanai ta wata-wata, barin kawai kwata
- boye jimlar ta watanni da kwata, barin jimlar kawai tsawon rabin shekara
- ɓoye biranen da ba dole ba a yanzu (Ina aiki a Moscow - me yasa zan ga St. Petersburg?), da dai sauransu.
A cikin rayuwa ta ainihi, akwai teku na misalan irin wannan tebur.
Hanyar 1: Boye layuka da ginshiƙai
Hanyar, a gaskiya, ita ce ta farko kuma ba ta dace sosai ba, amma ana iya faɗi kalmomi biyu game da shi. Duk wani layuka ko ginshiƙai da aka zaɓa a baya za a iya ɓoye su ta danna dama kan shafi ko kan layi da zaɓin umarni daga menu na mahallin. Ɓoye (Boye):
Don nunin baya, zaɓi layuka / ginshiƙai masu kusa kuma, ta danna dama, zaɓi daga menu, bi da bi, nuni (Ba a boye).
Matsalar ita ce dole ne ku yi hulɗa da kowane ginshiƙi da jere daban-daban, wanda ba shi da daɗi.
Hanyar 2. Rukuni
Idan ka zaɓi layuka da yawa ko ginshiƙai sannan zaɓi daga menu Bayanai - Ƙungiya da Tsarin - Ƙungiya (Bayanai - Rukuni da Shaci - Rukuni), sa'an nan kuma za a kewaye su a cikin wani madaidaicin sashi (rukunin). Bugu da ƙari, ana iya haɗa ƙungiyoyi ɗaya zuwa ɗayan (ana ba da izinin matakan tsukewa 8):
Hanya mafi dacewa kuma mafi sauri ita ce amfani da gajeriyar hanyar madannai zuwa rukuni da aka riga aka zaɓa layuka ko ginshiƙai. Alt+Shift+Kibiya Dama, da kuma ga ungrouping Alt+Shift+Kibiya Hagu, bi da bi.
Wannan hanyar ɓoye bayanan da ba dole ba ta fi dacewa - zaku iya danna maɓallin tare da "+"Ko"-", ko kuma a kan maɓallan tare da matakin rukuni na lamba a cikin kusurwar hagu na sama na takardar - sannan duk ƙungiyoyin matakin da ake so za su rushe ko fadada su lokaci guda.
Har ila yau, idan tebur ɗinku ya ƙunshi taƙaitaccen layuka ko ginshiƙai tare da aikin taƙaita sel makwabta, wato, dama (ba 100% gaskiya ba) cewa Excel zai kirkiro dukkan kungiyoyin da ake bukata a cikin tebur tare da motsi ɗaya - ta hanyar menu Bayanai - Ƙungiya da Tsarin - Ƙirƙiri Tsarin (Bayani - Ƙungiya da Ƙirar - Ƙirƙirar Ƙirƙiri). Abin baƙin ciki shine, irin wannan aikin yana aiki sosai ba tare da annabta ba kuma wani lokaci yana yin cikakken maganar banza akan hadaddun tebur. Amma kuna iya gwadawa.
A cikin Excel 2007 da sababbi, duk waɗannan abubuwan farin ciki suna kan shafin data (Kwanan wata) cikin rukuni Structure (Outline):
Hanya 3. Ɓoye alamun layuka/ginshiƙai tare da macro
Wannan hanya watakila ita ce mafi dacewa. Bari mu ƙara layin mara komai da ginshiƙi mara komai a farkon takardar mu kuma yi alama da kowane gunki waɗannan layuka da ginshiƙan da muke son ɓoyewa:
Yanzu bari mu bude Kayayyakin Basic Edita (ALT + F11), saka sabon tsarin komai a cikin littafinmu (menu Saka - Module) da kwafi rubutun macro guda biyu masu sauƙi a wurin:
Sub Hide() Dim cell As Range Application.ScreenUpdating = Ƙarya 'Karya sabunta allo don yin sauri Ga Kowane tantanin halitta A ActiveSheet.UsedRange.Rows(1) .Cell' Yana daidaita dukkan sel a jere na farko Idan cell.Value = "x " Sai cell .EntireColumn.Hidden = Gaskiya 'idan a cikin cell x - boye shafi na gaba Ga Kowane Tantanin halitta A ActiveSheet.UsedRange.Columns(1) .Cells 'suna shiga cikin dukkan sel na ginshiƙin farko Idan cell.Value = "x" Sannan cell.EntireRow.Hidden = Gaskiya 'idan a cikin cell x - boye jere na gaba Application.ScreenUpdating = True End Sub Show() Columns.Hidden = Qarya' soke duk layuka da ginshiƙai Layuka.Hidden = Ƙarshen Ƙarshen Ƙarshe
Kamar yadda zaku iya tsammani, macro Ɓoye boye da macro show – Nuna baya masu lakabin layuka da ginshiƙai. Idan ana so, ana iya sanya macros hotkeys (Alt+F8 da maballin Siga), ko ƙirƙirar maɓalli kai tsaye a kan takardar don ƙaddamar da su daga shafin Mai Haɓakawa - Saka - Maɓallin (Mai haɓakawa - Saka - Maɓallin).
Hanyar 4. Boye layuka / ginshiƙai tare da launi da aka ba
Bari mu ce a cikin misalin da ke sama, mu, akasin haka, muna so mu ɓoye jimlar, watau layukan shunayya da baƙar fata da ginshiƙan rawaya da kore. Sa'an nan macro ɗinmu na baya dole ne a ɗan canza shi ta ƙara, maimakon bincika kasancewar "x", rajistan don dacewa da launi mai cike da sel samfurin da aka zaɓa:
Sub HideByColor() Dim cell As Range Application.ScreenUpdating = Ƙarya Ga Kowane Tantanin halitta A ActiveSheet.Amfani da Range.Rows(2).Cells Idan cell.Interior.Launi = Range("F2").Interior.Launi Sai cell.EntireColumn.Hidden = Gaskiya Idan cell.Interior.Launi = Range("K2").Interior.Launi Sai cell.EntireColumn.Hidden = Gaskiya Na Gaba Ga Kowane Tantanin halitta A ActiveSheet.UsedRange.Columns(2).Cells If cell.Interior.Color = Range ("D6").Interior.Launi Sai cell.EntireRow.Hidden = Gaskiya Idan cell.Interior.Launi = Range("B11").Interior.Launi Sai cell.EntireRow.Hidden = True Next Application.ScreenUpdating = True End Sub. Duk da haka, kada mu manta game da caveat guda ɗaya: wannan macro yana aiki ne kawai idan sel na teburin tushen sun cika da launi da hannu, kuma ba amfani da tsarin yanayi ba (wannan shine iyakancewar cikin gida.Color dukiya). Don haka, alal misali, idan kun haskaka duk ma'amaloli ta atomatik a cikin tebur ɗin ku inda adadin bai wuce 10 ta amfani da tsara yanayin yanayi:
... kuma kuna son ɓoye su a cikin motsi ɗaya, to, macro na baya dole ne a “ƙare”. Idan kuna da Excel 2010-2013, to zaku iya fita ta amfani da maimakon kayan Interior dukiya DisplayFormat.Interior, wanda ke fitar da launi na tantanin halitta, ba tare da la'akari da yadda aka saita shi ba. Macro don ɓoye shuɗin layukan na iya yin kama da haka:
Sub HideByConditionalFormattingColor() Dim cell As Range Application.ScreenUpdating = Karya Ga Kowacce Tantanin halitta A ActiveSheet.UsedRange.Columns(1) .EntireRow.Hidden = Gaskiya Na Gaba Aikace-aikace.ScreenUpdating = Gaskiya Karshen Sub
Ana ɗaukar Cell G2 azaman samfuri don kwatanta launi. Abin takaici dukiya Tsarin Nuni ya bayyana a cikin Excel kawai yana farawa daga nau'in 2010, don haka idan kuna da Excel 2007 ko sama da haka, dole ne ku fito da wasu hanyoyi.
- Menene macro, inda za a saka macro code, yadda ake amfani da su
- Ƙungiya ta atomatik a cikin jerin matakai masu yawa