Zaɓin Haɗawa

Kuna da babban mai saka idanu, amma teburin da kuke aiki da su sun fi girma. Kuma, kallon allon don neman bayanan da suka dace, akwai damar ko da yaushe don "zamewa" idanunku zuwa layi na gaba kuma ku dubi hanyar da ba ta dace ba. Har ma na san mutanen da, don irin waɗannan lokuta, ko da yaushe suna ajiye wani mai mulki na katako kusa da su don haɗa shi zuwa layi akan na'ura. Fasaha na gaba! 

Kuma idan jere na yanzu da ginshiƙi an haskaka lokacin da tantanin halitta mai aiki ke motsawa a cikin takardar? Irin zaɓin daidaitawa kamar haka:

Gara mai mulki, dama?

Akwai hanyoyi da yawa na bambance-bambancen rikitarwa don aiwatar da wannan. Kowace hanya tana da ribobi da fursunoni. Bari mu duba su dalla-dalla.

Hanyar 1. A bayyane. Macro wanda ke haskaka layin yanzu da shafi

Hanyar da ta fi dacewa don magance matsalarmu "a kan goshi" - muna buƙatar macro wanda zai bibiyar canjin zaɓi a kan takardar kuma zaɓi dukan jere da ginshiƙi don tantanin halitta na yanzu. Hakanan yana da kyawawa don samun damar kunnawa da kashe wannan aikin idan ya cancanta, ta yadda irin wannan zaɓin mai siffa ba zai hana mu shiga ba, alal misali, dabaru, amma yana aiki ne kawai lokacin da muka duba jerin abubuwan da ake buƙata don nema. bayani. Wannan ya kawo mu ga macros guda uku (zaɓa, kunna, da kashe) waɗanda za a buƙaci a ƙara su zuwa ƙirar takarda.

Bude takarda tare da tebur inda kake son samun irin wannan zaɓi na daidaitawa. Danna-dama akan shafin shafin kuma zaɓi umarni daga menu na mahallin Tushen rubutu (Lambar Tushen).Ya kamata taga Visual Basic Edita ya buɗe. Kwafi wannan rubutu na waɗannan macro guda uku zuwa cikinsa:

Dim Coord_Selection As Boolean 'Maimaimai na duniya don zaɓin kunnawa/kashe Sub Selection_On()'Macro akan zaɓi Coord_Selection = Zaɓin Ƙarshen Ƙarshe na Gaskiya Coord_Selection = Ƙarshen Ƙarshen Ƙarshen 'Babban Hanyar da ke aiwatar da Zaɓin Ƙarshen Ƙarshe na Ƙarshe_SelectionChange (ByVal) Range) Dim WorkRange As Range If Target.Cells.Count> 1 Sannan Fita Sub 'idan an zaɓi fiye da tantanin halitta 1, fita Idan Coord_Selection = Ƙarya Sai Ka fita Sub 'idan zaɓin ya kashe, fita Application.ScreenUpdating = Ƙarya Saitin WorkRange = Range ("A6:N300")'adireshin kewayon aiki wanda zaɓin ke bayyane

Canja adireshin kewayon aiki zuwa naku - a cikin wannan kewayon zaɓinmu zai yi aiki. Sa'an nan kuma rufe Visual Basic Editan kuma komawa Excel.

Danna gajeriyar hanyar madannai ALT + F8don buɗe taga tare da jerin macro da ke akwai. Macro Zabi_Kunna, kamar yadda zaku iya tsammani, ya haɗa da zaɓin daidaitawa akan takardar yanzu, da macro Zaɓi_A kashe – Yana kashe shi. A cikin wannan taga, ta danna maballin Siga (Zaɓuɓɓuka) Kuna iya sanya gajerun hanyoyin keyboard zuwa waɗannan macros don ƙaddamarwa cikin sauƙi.

Amfanin wannan hanyar:

  • zumunta sauƙi na aiwatarwa
  • Zaɓin - aikin ba shi da lahani kuma baya canza abun ciki ko tsara tsarin sel ta kowace hanya, duk abin ya kasance kamar yadda yake.

Fursunoni na wannan hanyar:

  • irin wannan zaɓin ba ya aiki daidai idan akwai sel da aka haɗa akan takardar - duk layuka da ginshiƙan da aka haɗa a cikin ƙungiyar an zaɓi su gaba ɗaya.
  • idan ka danna maɓallin Share ba da gangan ba, to ba kawai cell ɗin da ke aiki ba za a share shi, amma duk yankin da aka zaɓa, watau share bayanai daga duka jere da shafi.

Hanyar 2. Asali. CELL + Ayyukan Tsarin Yanayi

Wannan hanya, ko da yake tana da ma'aurata biyu, amma a gare ni tana da kyau sosai. Don aiwatar da wani abu ta amfani da kayan aikin Excel da aka gina kawai, ƙaramin shiga shirye-shirye a cikin VBA shine aerobatics 😉

Hanyar ta dogara ne akan yin amfani da aikin CELL, wanda zai iya ba da bayanai da yawa daban-daban akan tantanin halitta - tsawo, nisa, lambar ginshiƙi, tsarin lamba, da sauransu. Wannan aikin yana da gardama guda biyu:

  • kalmar lamba don ma'auni, kamar "column" ko "jere"
  • adireshin tantanin halitta wanda muke son tantance darajar wannan siga

Dabarar ita ce hujja ta biyu na zaɓi ne. Idan ba a bayyana shi ba, to ana ɗaukar tantanin halitta mai aiki na yanzu.

Bangaren na biyu na wannan hanya shine tsarin tsari. Wannan fasalin Excel mai matukar amfani yana ba ku damar tsara sel ta atomatik idan sun cika ƙayyadaddun yanayi. Idan muka haɗa waɗannan ra'ayoyin biyu zuwa ɗaya, muna samun algorithm mai zuwa don aiwatar da zaɓin haɗin gwiwarmu ta hanyar tsara yanayin:

  1. Muna zaɓar teburin mu, watau waɗancan sel waɗanda yakamata a nuna zaɓin haɗin kai a nan gaba.
  2. A cikin Excel 2003 da tsofaffi, buɗe menu Tsarin - Tsarin Yanayi - Formula (Tsarin - Tsarin Yanayi - Tsarin tsari). A cikin Excel 2007 da sababbi - danna kan shafin Gida (Gida)button Tsarin Yanayi - Ƙirƙiri Doka (Tsarin Yanayi - Ƙirƙirar Doka) kuma zaɓi nau'in ƙa'ida Yi amfani da dabara don tantance waɗanne sel don tsarawa (Yi amfani da dabara)
  3. Shigar da dabara don zaɓin haɗin gwiwarmu:

    = OR(CELL("jere")=ROW(A2),CELL("shafi")=COLUMN(A2))

    = OR(CELL(«jere»)=ROW(A1),CELL(«shafi»)= COLUMN(A1))

    Wannan dabarar tana bincika don ganin ko lambar shafi na kowane tantanin halitta a cikin tebur daidai yake da lambar shafi na tantanin halitta na yanzu. Hakanan tare da ginshiƙai. Don haka, kawai waɗancan sel waɗanda ke da lambar shafi ko lambar jere da ta dace da tantanin halitta na yanzu za a cika su. Kuma wannan shine zaɓin haɗin kai mai siffar giciye da muke son cimmawa.

  4. latsa tsarin (Format) kuma saita cika launi.

Komai ya kusan shirya, amma akwai nuance ɗaya. Gaskiyar ita ce, Excel baya la'akari da canji a cikin zaɓin azaman canji a cikin bayanan da ke kan takardar. Kuma, a sakamakon haka, ba ya haifar da sake ƙididdige ƙididdiga da canza launi na tsarin yanayin kawai lokacin da matsayi na tantanin halitta ya canza. Don haka, bari mu ƙara macro mai sauƙi zuwa ƙirar takarda wanda zai yi wannan. Danna-dama akan shafin shafin kuma zaɓi umarni daga menu na mahallin Tushen rubutu (Lambar Tushen).Ya kamata taga Visual Basic Edita ya buɗe. Kwafi wannan rubutu na wannan macro mai sauƙi a ciki:

Canje-canje na Zaɓuɓɓukan Zaɓuɓɓuka (ByVal Target As Range) ActiveCell.Lissafi Ƙarshen Ƙarshe

Yanzu, lokacin da zaɓin ya canza, za a ƙaddamar da tsarin sake lissafin ƙididdiga tare da aikin Sanarwa a cikin tsari na yanayi da ambaliya jere da ginshiƙi na yanzu.

Amfanin wannan hanyar:

  • Tsarin yanayi ba ya karya tsarin tebur na al'ada
  • Wannan zaɓin zaɓi yana aiki daidai tare da sel da aka haɗa.
  • Babu haɗarin share gaba ɗaya jere da ginshiƙin bayanai akan danna bazata share.
  • Ana amfani da macro kaɗan

Fursunoni na wannan hanyar:

  • Dole ne a shigar da dabara don tsara yanayin da hannu.
  • Babu wata hanya mai sauri don kunna / kashe irin wannan tsarin - ana kunna shi koyaushe har sai an share ƙa'idar.

Hanyar 3. Mafi kyau duka. Tsarin Yanayi + Macros

Golden ma'ana. Muna amfani da tsarin don bin diddigin zaɓin akan takardar ta amfani da macros daga hanya-1 kuma muna ƙara haske mai aminci gare shi ta amfani da tsara yanayin yanayi daga hanya-2.

Bude takarda tare da tebur inda kake son samun irin wannan zaɓi na daidaitawa. Danna-dama akan shafin shafin kuma zaɓi umarni daga menu na mahallin Tushen rubutu (Lambar Tushen).Ya kamata taga Visual Basic Edita ya buɗe. Kwafi wannan rubutu na waɗannan macro guda uku zuwa cikinsa:

Dim Coord_Selection As Boolean Sub Selection_On () Coord_Selection = Gaskiya Ƙarshen Sub Selection_Off() Coord_Selection = Ƙarshen Ƙarshen Ƙarshen Ƙarshen Ƙarshen Ƙarshen Ƙarshen Ƙarshen Ƙarshe_SelectionChange(ByVal Target As Range) Dim WorkRange As Range, CrossRange As Range Set WorkRange (7): N 'адрес рабочего диапазона с таблицей If Target.Count > 300 To Exit Sub If Coord_Selection = Karya Sai Aiki.FormatConditions.Share Exit Sub End If Application WorkRange, Union (Target.EntireRow, Target.EntireColumn)) WorkRange.FormatConditions.Share CrossRange.FormatConditions.Ƙara Nau'in: = xlExpression, Formula1: = "= 1" CrossRange.FormatConditions(1).Interior.ColorIndex = 1 Conditions.FormatConditions. .Goge Ƙarshen Idan Ƙarshen Sub

Kar a manta da canza adireshin kewayon aiki zuwa adireshin teburin ku. Rufe Kayayyakin Basic Editan kuma komawa Excel. Don amfani da ƙarin macros, danna gajeriyar hanyar madannai ALT + F8  kuma ci gaba a cikin hanyar da ta dace da hanyar 1. 

Hanyar 4. Kyawawa. Bi CellPointer add-on

MVP na Excel Jan Karel Pieterse daga Netherlands yana ba da ƙarin ƙarin kyauta akan gidan yanar gizon sa Bi CellPointer(36Kb), wanda ke magance wannan matsala ta hanyar zana kibiya mai hoto ta amfani da macro don haskaka layin yanzu da shafi:

 

Kyakkyawan bayani. Ba tare da glitches a wurare ba, amma tabbas ya cancanci gwadawa. Zazzage ma'ajiyar, cire fakitin zuwa diski kuma shigar da add-on:

  • a cikin Excel 2003 da kuma tsofaffi - ta hanyar menu Sabis - Ƙara-kan - Bayani (Kayan aiki - Ƙara-Ins - Bincike)
  • a cikin Excel 2007 kuma daga baya, ta hanyar Fayil - Zaɓuɓɓuka - Ƙara-kan - Tafi - Bincika (Fayil - Zaɓuɓɓukan Excel - Ƙara-Ins - Je zuwa - Bincike)

  • Menene macros, inda za a saka macro code a cikin Visual Basic

 

Leave a Reply