Contents
Na riga na rubuta game da yadda zaku iya saurin manne rubutu daga sel da yawa zuwa ɗaya kuma, akasin haka, raba dogon kirtani rubutu cikin abubuwan da aka gyara. Yanzu bari mu kalli aiki na kusa, amma ɗan ƙaramin aiki mai rikitarwa - yadda ake manna rubutu daga sel da yawa lokacin da takamaiman yanayin ya cika.
Bari mu ce muna da bayanan abokan ciniki, inda sunan kamfani ɗaya zai iya dacewa da imel daban-daban na ma'aikatansa. Aikinmu shi ne tattara duk adiresoshin da sunayen kamfani kuma mu haɗa su (wanda aka raba ta waƙafi ko waƙafi) don yin, alal misali, jerin aikawasiku ga abokan ciniki, watau fitar da wani abu kamar:
A wasu kalmomi, muna buƙatar kayan aiki wanda zai manne (haɗi) rubutun bisa ga yanayin - analogue na aikin SUMMESLI (SUMIF), amma don rubutu.
Hanyar 0. Formula
Ba sosai m, amma mafi sauki hanya. Kuna iya rubuta tsari mai sauƙi wanda zai bincika ko kamfanin a jere na gaba ya bambanta da na baya. Idan bai bambanta ba, to ku manne adireshin gaba wanda waƙafi ya rabu. Idan ya bambanta, to, muna "sake saita" abubuwan da aka tara, sake farawa:
Rashin hasara na wannan hanya a bayyane yake: daga dukkanin sel na ƙarin shafi da aka samu, muna buƙatar kawai na ƙarshe ga kowane kamfani (rawaya). Idan lissafin yana da girma, to don zaɓar su da sauri, dole ne ku ƙara wani shafi ta amfani da aikin DLSTR (LEN), duba tsawon igiyoyin da aka tara:
Yanzu zaku iya tace waɗanda kuma ku kwafi manne adireshin da ake buƙata don ƙarin amfani.
Hanyar 1. Macrofunction na gluing ta yanayin daya
Idan jerin asali ba a tsara su ta hanyar kamfani ba, to, tsarin da ke sama mai sauƙi ba ya aiki, amma zaka iya samun sauƙi tare da karamin aikin al'ada a cikin VBA. Bude Editan Kayayyakin Kaya ta latsa gajeriyar hanyar madannai Alt+F11 ko amfani da button Kayayyakin aikin Basic tab developer (Mai haɓakawa). A cikin taga da yake buɗewa, saka sabon tsarin komai a cikin menu Saka - Module da kwafi rubutun aikin mu a can:
Haɗin aikiIf(TextRange As Range, SearchRange As Range, Condition As String) Dim Delimeter As String, i As Long Delimeter = ", " gluings are not equal to each other - we out with an error If SearchRange.Count <> TextRange.Count Sa'an nan MergeIf = CVERr(xlErrRef) Ƙarshen Ayyukan Fita Idan 'ku bi duk sel, duba yanayin kuma tattara rubutun a cikin m OutText For i = 1 To SearchRange. Cells.Kirga Idan SearchRange.Cells(i) Like Condition Sai OutText = OutText & TextRange.Cells(i) & Delimeter Next i 'nuna sakamako ba tare da na ƙarshe delimiter MergeIf = Hagu(OutText, Len(OutText) - Len(Delimeter)) Ƙare aiki
Idan yanzu kun koma Microsoft Excel, to a cikin jerin ayyuka (button fx a cikin dabara bar ko tab Formulas - Saka Aiki) za a iya samun aikin mu Haɗe Idan a cikin category Ma'anar Mai amfani (An bayyana mai amfani). Hujjojin aikin sune kamar haka:
Hanyar 2. Haɗa rubutu ta yanayin rashin daidaituwa
Idan muka maye gurbin hali na farko a cikin layi na 13 na macro = zuwa kusan ma'aikacin wasa Kamar, to, zai yiwu a aiwatar da gluing ta hanyar daidaitattun daidaitattun bayanan farko tare da ma'aunin zaɓi. Misali, idan ana iya rubuta sunan kamfani a cikin bambance-bambance daban-daban, to zamu iya bincika da tattara su duka tare da aiki ɗaya:
Ana tallafawa daidaitattun katunan daji:
- alama (*) - yana nuna kowane adadin kowane haruffa (ciki har da rashin su)
- Alamar tambaya (?) - tana tsaye ga kowane hali guda
- alamar fam (#) - yana tsaye ga kowane lambobi ɗaya (0-9)
Ta hanyar tsoho, mai aiki kamar yana da mahimmanci, watau yana fahimtar, misali, "Orion" da "orion" azaman kamfanoni daban-daban. Don yin watsi da harka, zaku iya ƙara layin a farkon tsarin a cikin Editan Kayayyakin Kaya Zaɓi Kwatanta Rubutu, wanda zai canza Like ya zama yanayin rashin jin daɗi.
Ta wannan hanyar, zaku iya tsara masks masu rikitarwa don duba yanayin, misali:
- ?1##??777RUS - zaɓi na duk faranti na yankin 777, farawa da 1
- LLC * - duk kamfanonin da sunansu ya fara da LLC
- ##7## - duk samfuran da ke da lambar dijital mai lamba biyar, inda lamba ta uku ta kasance 7
- ???? – duk sunayen haruffa biyar, da sauransu.
Hanyar 3. Ayyukan macro don gluing rubutu a ƙarƙashin yanayi biyu
A cikin aikin ana iya samun matsala lokacin da kake buƙatar haɗa rubutun fiye da yanayi ɗaya. Alal misali, bari mu yi tunanin cewa a cikin teburinmu na baya, an ƙara ƙarin shafi tare da birnin, kuma ya kamata a yi gluing ba kawai ga kamfani ba, har ma ga wani birni. A wannan yanayin, aikinmu dole ne ya zama ɗan zamani ta hanyar ƙara wani bincike na kewayo zuwa gare shi:
Haɗin Haɗin Aiki (Rubutu Kamar Range, BincikeRange1 Kamar Range, Yanayi1 A Matsayin Kirtani, SearchRange2 As Range, Condition2 As String) Dim Delimeter As String, i As Dogon Delimeter = "," 'Haruffa masu iyaka (ana iya maye gurbinsu da sarari ko ; da sauransu.) e.) 'if the validation and gluing ranges are not equal to each other, exit with an error If SearchRange1.Count <> TextRange.Count Or SearchRange2.Count <> TextRange.Count Sai MergeIfs = CVERr(xlErrRef) Ƙarshen Aikin Ƙarshen Idan 'shiga cikin dukkan sel, duba duk sharuɗɗan kuma tattara rubutun cikin madaidaicin OutText Don i = 1 To SearchRange1.Cells.Count If SearchRange1.Cells(i) = Condition1 And SearchRange2.Cells(i) = Condition2 Sannan OutText = OutText & TextRange.Cells(i) & Ƙarshen Ƙarshen Idan Na gaba na 'nuna sakamakon ba tare da ƙayyadadden ƙayyadadden ƙayyadaddun ƙayyadaddun ƙayyadaddun abubuwa ba MergeIfs = Hagu (OutText, Len (OutText) - Len (Delimeter)) Ƙarshen Aiki
Za a yi amfani da shi daidai wannan hanya - kawai muhawara yanzu ana buƙatar ƙarin bayani:
Hanyar 4. Ƙungiya da gluing a cikin Tambayar Wuta
Kuna iya magance matsalar ba tare da shirye-shirye ba a cikin VBA, idan kun yi amfani da ƙarar Query Query kyauta. Don Excel 2010-2013 ana iya sauke shi anan, kuma a cikin Excel 2016 an riga an gina shi ta tsohuwa. Jerin ayyuka zai kasance kamar haka:
Query Power bai san yadda ake aiki tare da tebur na yau da kullun ba, don haka mataki na farko shine juya teburin mu zuwa "mai hankali". Don yin wannan, zaɓi shi kuma danna haɗin Ctrl+T ko zaɓi daga shafin Gida - Tsarin azaman tebur (Gida - Tsarin azaman Tebur). A shafin da ya bayyana sannan Constructor (Zane) zaka iya saita sunan tebur (Na bar ma'auni Table 1):
Yanzu bari mu loda tebur ɗin mu a cikin ƙarar Query Query. Don yin wannan, a kan tab data (idan kuna da Excel 2016) ko akan Tambarin Tambayoyi (idan kuna da Excel 2010-2013) danna Daga tebur (Bayanai - Daga Tebur):
A cikin taga editan tambaya da ke buɗewa, zaɓi shafi ta danna kan taken Kamfanin kuma danna maɓallin da ke sama Group (Rukuni Na). Shigar da sunan sabon shafi da nau'in aiki a cikin rukunin - Duk layi (Duk Layukan):
Danna Ok kuma muna samun ƙaramin tebur na ƙimar ƙungiyoyi don kowane kamfani. Abubuwan da ke cikin tebur ɗin suna bayyane sosai idan ka danna hagu-dama akan farin bangon sel (ba akan rubutu ba!) A cikin ginshiƙi da aka samu:
Yanzu bari mu ƙara ginshiƙi guda ɗaya, inda, ta amfani da aikin, muna manne abubuwan da ke cikin ginshiƙan adireshi a cikin kowane ƙaramin teburi, waɗanda aka ware ta waƙafi. Don yin wannan, a kan tab Sanya shafi muna danna Rukunin al'ada (Ƙara shafi - ginshiƙi na al'ada) kuma a cikin taga da ya bayyana, shigar da sunan sabon ginshiƙi da tsarin haɗin kai a cikin yaren M da aka gina cikin Query Query:
Lura cewa duk M-ayyukan suna da hankali (ba kamar Excel ba). Bayan danna kan OK muna samun sabon shafi tare da adiresoshin manne:
Ya rage don cire ginshiƙin da ba dole ba TableAdresses (dama kan take) Share shafi) kuma loda sakamakon zuwa takardar ta danna shafin Gida - Rufe kuma zazzagewa (Gida - Kusa da lodi):
Muhimmiyar nuance: Ba kamar hanyoyin da suka gabata (ayyukan) ba, tebur daga Tambayoyin Wuta ba a sabunta su ta atomatik. Idan a nan gaba za a sami wasu canje-canje a cikin bayanan tushen, to kuna buƙatar danna-dama a ko'ina cikin teburin sakamako kuma zaɓi umarnin. Sabunta & Ajiye (Sake sabuntawa).
- Yadda ake raba dogon zaren rubutu zuwa sassa
- Hanyoyi da yawa don manne rubutu daga sel daban-daban zuwa ɗaya
- Amfani da afaretan Like don gwada rubutu akan abin rufe fuska