Har ya zuwa yanzu, wani lokacin da murmushi na tuna daya daga cikin horo na farko na kamfani shekaru 10 da suka wuce.
Ka yi tunanin: ofishin sararin samaniya na ofishin wakilin wani kamfani na FMCG na kasa da kasa, mai girma kamar filin kwallon kafa. Zane mai kyan gani, kayan ofis masu tsada, lambar sutura, ƙwanƙolin baƙi a cikin sasanninta - wannan ke nan , tare da shugabansu. Mun saba, na tambaye su game da ayyukan kasuwanci, matsaloli, na tambaye su su nuna yawancin fayilolin aiki na yau da kullun. Suna nuna tsawon kilomita na saukewa daga SAP, takaddun rahotannin da suke bayarwa akan wannan, da dai sauransu. To, abu ne da aka saba da shi - A hankali na gano batutuwa da lokaci, daidaitawa ga masu sauraro. Daga kusurwar idona, na lura da yadda ɗaya daga cikin mahalarta, yana nuna wani yanki na rahotonsa, ya yi haƙuri ya ja tantanin halitta tare da ma'anar ta hanyar baƙar fata a kusurwar dama na dama ga layuka dubu da yawa, sannan ya tsallake ƙarshen ƙarshen. Tebur a kan gardama, yana ja da baya, da dai sauransu. Ba zan iya tsayawa ba, na katse shi yana murɗa linzamin kwamfuta a kan allon kuma na nuna danna sau biyu akan giciye na baki, yana bayani game da kammalawa ta atomatik har zuwa tasha.
Nan da nan na gane cewa masu sauraro sun yi shiru kuma kowa yana kallona da ban mamaki. Ina kallon kaina a duk inda zan iya - komai yana da kyau, hannayena da kafafuna suna nan, an kulle kudana sama. A hankali na mayar da kalmomina na ƙarshe don neman wani mummunan magana - babu wani laifi, ga alama. Bayan haka, shugaban ƙungiyar ya tashi cikin shiru, ya girgiza hannuna kuma ya ce da fuskar dutse: “Na gode, Nikolai. Ana iya kammala wannan horon.
To, a taƙaice, ya zama cewa babu ɗayansu da ke da ra'ayi game da danna sau biyu akan giciye baƙar fata da autocomplete. Ko ta yaya ya faru a tarihi cewa babu wanda ya nuna musu abu mai sauƙi amma dole. Dukan sashen sun ja dabaru da hannu don dubunnan layuka, ƴan uwa matalauta. Kuma ga ni. Yanayin mai. Daga nan sai shugaban sashen ya yi tambaya sosai da kar ya bayyana sunan kamfaninsu ga kowa 🙂
Sau da yawa daga baya an sami irin wannan yanayi, amma tare da masu sauraro ɗaya kawai - mafi yawan yanzu, ba shakka, san wannan aikin.
Tambayar ta bambanta. Bayan farin ciki na farko na ƙware irin wannan fasalin mai ban sha'awa, yawancin masu amfani sun fahimci cewa kwafin dabaru ta atomatik ta danna sau biyu akan giciye baƙar fata (alama ta atomatik) yana da dukkan fa'idodi masu kyau da marasa kyau:
- Kwafi ba koyaushe yana faruwa zuwa ƙarshen tebur ba. Idan tebur ba monolithic ba ne, watau akwai sel marasa komai a cikin ginshiƙan da ke kusa, to ba gaskiya bane cewa autocomplete zai yi aiki har zuwa ƙarshen tebur. Mafi mahimmanci, tsarin zai tsaya a mafi kusa da tantanin halitta mafi kusa kafin ya kai ga ƙarshe. Idan akwai sel da wani abu ke ƙarƙashin ginshiƙi, to autocomplete zai tsaya akan su daidai.
- Lokacin yin kwafi zanen tantanin halitta ya lalace, saboda Ta hanyar tsoho, ba kawai tsarin da aka kwafi ba, har ma da tsarin. Don gyara, danna maɓallin zaɓin kwafi kuma zaɓi Ƙimar kawai (Cika ba tare da tsari ba).
- Babu wata hanya mai sauri don kuma shimfiɗa dabarar cikin dacewa ba kasa ba amma zuwa damasai dai a ja da hannu. Danna sau biyu akan baƙar giciye yana ƙasa.
Bari mu yi ƙoƙarin gyara waɗannan gazawar tare da macro mai sauƙi.
Danna gajeriyar hanyar madannai hagu Alt+F11 ko button Kayayyakin aikin Basic tab developer (Mai haɓakawa). Saka sabon tsarin komai ta hanyar menu Saka - Module kuma kwafi rubutun waɗannan macro a wurin:
Sub SmartFillDown() Dim rng Matsayin Range, n Saitin Tsawon rng = ActiveCell.Offset(0, -1).Yankin Yanzu Idan rng.Cells.Count> 1 Sannan n = rng.Cells(1) .Row + rng.Rows. Ƙidaya - ActiveCell.Row ActiveCell.AutoFill Destination:=ActiveCell.Resize(n, 1), Type:=xlFillValues End If End Sub Sub SmartFillRight() Dim rng As Range, n A tsawon Saita rng = ActiveCell.Offset(-1, 0).Yankin Yanzu Idan rng.Cells.Count> 1 Sai n = rng.Cells(1).Column + rng.Columns.Count - ActiveCell.Column ActiveCell.AutoFill Destination:=ActiveCell.Resize(1, n), Type:: =xlFillValues Ƙarshe Idan Ƙarshen Sub
Macros kamar haka:
- na iya cika ba kawai ƙasa (SmartFillDown), amma kuma zuwa dama (SmartFillRight)
- kar a ɓata tsarin sel na ƙasa ko zuwa dama - kawai dabara (darajar) ana kwafi
- Kwayoyin da ke kusa da komai ana yin watsi da su kuma ana yin kwafin yana faruwa daidai zuwa ƙarshen tebur, kuma ba zuwa ga tazara mafi kusa a cikin bayanai ko tantanin halitta na farko da aka mamaye ba.
Don ƙarin dacewa, zaku iya sanya gajerun hanyoyin keyboard zuwa waɗannan macros ta amfani da maɓallin Macros - Zabuka (Macros - Zabuka) dama can kan tab. developer (Mai haɓakawa). Yanzu zai isa shigar da dabara ko ƙima da ake so a cikin tantanin halitta na farko na ginshiƙi kuma danna ƙayyadadden haɗin maɓalli don macro don cika dukkan shafi ta atomatik (ko jere):
Beauty.
PS Wani ɓangare na matsalar tare da kwafin dabarar zuwa ƙarshen tebur an warware shi a cikin Excel 2007 tare da zuwan "Tables masu wayo". Gaskiya ne, ba koyaushe ba ne kuma ba a duk inda ya dace ba. Kuma a dama, Excel bai taɓa koyon kwafi da kansa ba.
- Menene macros, yadda ake amfani da su, inda ake samun Visual Basic code da inda za a liƙa shi.
- Smart Tables a cikin Excel 2007-2013
- Kwafi dabaru ba tare da canjin hanyar haɗi ba