Мундариҷа
Ман аллакай дар бораи он навишта будам, ки чӣ тавр шумо метавонед матнро аз якчанд ҳуҷайра ба як зуд часпонед ва баръакс, сатри дарози матнро ба ҷузъҳо таҳлил кунед. Акнун биёед як вазифаи наздик, вале каме мураккабтарро дида бароем - чӣ гуна матнро аз якчанд ҳуҷайраҳо часпонед, вақте ки шарти муайяни муайян иҷро карда мешавад.
Фарз мекунем, ки мо базаи муштариён дорем, ки дар он як номи ширкат метавонад ба якчанд мактубҳои гуногуни кормандони он мувофиқат кунад. Вазифаи мо аз он иборат аст, ки ҳама суроғаҳоро аз рӯи номҳои ширкат ҷамъоварӣ кунем ва онҳоро (бо вергул ё нуқта вергул ҷудо карда) ҷамъ кунем, то масалан, рӯйхати почтаи электронӣ барои муштариён тартиб диҳем, яъне чунин натиҷа гиред:
Ба ибораи дигар, ба мо асбобе лозим аст, ки матнро мувофиқи шарт часпонида (пайванд) кунад - аналоги функсия СУММЕСЛИ (SUMIF), балки барои матн.
Усули 0. Формула
На он қадар шево, аммо роҳи осонтарин. Шумо метавонед формулаи оддиеро нависед, ки тафтиш мекунад, ки оё ширкат дар сатри оянда аз ширкати қаблӣ фарқ мекунад. Агар он фарқ накунад, суроғаи навбатии бо вергул ҷудошударо часпонед. Агар он фарқ кунад, мо ҷамъшударо аз нав оғоз мекунем:
Камбудихои ин равиш равшананд: аз хамаи ячейкахои сутуни иловагии ба даст овардашуда барои хар як ширкат (зард) танхо охирини он лозим аст. Агар рӯйхат калон бошад, пас барои зуд интихоб кардани онҳо, шумо бояд бо истифода аз функсия сутуни дигар илова кунед DLSTR (ЛЕН), тафтиши дарозии сатрҳои ҷамъшуда:
Акнун шумо метавонед онҳоро филтр кунед ва суроғаи заруриро барои истифодаи минбаъда нусхабардорӣ кунед.
Усули 1. Макрофунксияи ширеш аз рӯи як шарт
Агар рӯйхати аслӣ аз ҷониби ширкат мураттаб карда нашавад, пас формулаи оддии дар боло овардашуда кор намекунад, аммо шумо метавонед бо функсияи хурди фармоишӣ дар VBA ба осонӣ гирд ояд. Таҳрири Visual Basic -ро бо пахш кардани миёнабури клавиатура кушоед Alt + F11 ё бо истифода аз тугма Visual Basic ҷадвали таҳиякунанда (Таҳиягар). Дар равзанаи кушодашуда, ба воситаи меню модули нави холӣ ворид кунед Ворид - Модул ва матни функсияи моро дар он ҷо нусхабардорӣ кунед:
Функсияи MergeIf(TextRange ҳамчун диапазон, SearchRange ҳамчун диапазон, шарт ҳамчун сатр) Dim Delimeter As String, i As Long Delimeter = ", " часпакҳо ба ҳамдигар баробар нестанд - мо бо хатогӣ мебароем Агар SearchRange.Count <> TextRange.Count Сипас MergeIf = CVErr(xlErrRef) Хуруҷ Функсияи End Агар 'ҳамаи чашмакҳоро гузаред, шартро тафтиш кунед ва матнро дар тағирёбандаи OutText For i = 1 To SearchRange ҷамъ кунед. Cells.Count Агар SearchRange.Cells(i) ба ҳолати монанд монанд бошад, пас OutText = OutText & TextRange.Cells(i) & Delimeter Next i 'натиҷаҳоро бе ҷудокунандаи охирин нишон диҳед MergeIf = Left(OutText, Len(OutText) - Len(Delimeter)) End функсия
Агар шумо ҳоло ба Microsoft Excel баргардед, пас дар рӯйхати функсияҳо (тугмаи fx дар сатри формула ё ҷадвал Формулаҳо - Функсияи дохилкунӣ) вазифаи худро ёфтан мумкин мешавад MergeIf дар категория Корбар муайян карда шудааст (Истифодабаранда муайян карда шудааст). Аргументҳои функсия инҳоянд:
Усули 2. Матнро аз рӯи шарти нодуруст пайваст кунед
Агар мо аломати якумро дар сатри 13-уми макроси худ иваз кунем = ба оператори тахминии мувофиқат мисли, он гоҳ мумкин аст, ки ширешро бо мувофиқати нодурусти маълумоти ибтидоӣ бо меъёри интихоб анҷом диҳед. Масалан, агар номи ширкат метавонад бо вариантҳои гуногун навишта шавад, мо метавонем ҳамаи онҳоро бо як функсия тафтиш ва ҷамъ кунем:
Рамзҳои ҷории стандартӣ дастгирӣ мешаванд:
- ситорача (*) - ҳар як шумораи ҳар гуна аломатҳоро ифода мекунад (аз ҷумла набудани онҳо)
- аломати савол (?) - ҳар як аломати ягонаро ифода мекунад
- Аломати фунт (#) - барои ҳар як рақам (0-9)
Бо нобаёнӣ, оператори Like ба ҳарфҳо ҳассос аст, яъне масалан, "Орион" ва "орион" -ро ширкатҳои гуногун мефаҳмад. Барои нодида гирифтани парванда, шумо метавонед сатрро дар ибтидои модул дар муҳаррири Visual Basic илова кунед Опсияи муқоисаи матн, ки "Лайк"-ро иваз мекунад, то беҳассос ба ҳарф бошад.
Бо ин роҳ, шумо метавонед барои санҷиши шароит ниқобҳои хеле мураккаб созед, масалан:
- ?1##??777RUS – интихоби ҳамаи рақамҳои давлатии минтақаи 777, аз 1 сар карда
- ҶДММ* - ҳама ширкатҳое, ки номашон бо ҶДММ оғоз мешавад
- ##7## – ҳама маҳсулот бо рамзи рақамии панҷрақама, ки рақами сеюм 7 аст
- ?????? - ҳамаи номҳои панҷ ҳарф ва ғайра.
Усули 3. Функсияи макрос барои часпаки матн дар ду шарт
Дар кор метавонад мушкилоте ба миён ояд, ки ба шумо лозим меояд, ки матнро зиёда аз як шарт пайваст кунед. Масалан, биёед тасаввур кунем, ки дар чадвали пештараи мо боз як сутуни дигар бо шахр илова карда шудааст ва ширешкунй на танхо барои як корхона, балки барои як шахр низ бояд гузаронида шавад. Дар ин ҳолат, функсияи мо бояд бо илова кардани санҷиши диапазон ба он каме навсозӣ карда шавад:
Функсияи MergeIfs(TextRange ҳамчун диапазон, SearchRange1 ҳамчун диапазон, Condition1 ҳамчун сатр, SearchRange2 ҳамчун диапазон, шарти2 ҳамчун сатр) торик ҷудокунанда ҳамчун сатр, i ҳамчун сарҳади дароз = ", " 'аломатҳои ҷудокунанда (метавонад бо фосила ё ; ва ғ. иваз карда шавад) д.) 'агар диапазонҳои тасдиқкунӣ ва часпак ба ҳамдигар баробар набошанд, бо хатогӣ бароед. 'аз тамоми чашмакҳо гузаред, ҳама шароитҳоро тафтиш кунед ва матнро ба тағирёбандаи OutText ҷамъ кунед Барои i = 1 Барои SearchRange2.Cells.Count Агар SearchRange1.Cells(i) = Condition1 Ва SearchRange1.Cells(i) = Condition1 Сипас OutText = OutText & TextRange.Cells(i) & Delimeter End If Next i 'нишон додани натиҷаҳо бе ҷудокунандаи охирин MergeIfs = Left(OutText, Len(OutText) - Len(Delimeter)) End Function
Он маҳз ҳамин тавр татбиқ карда мешавад - ҳоло танҳо далелҳо бояд бештар нишон дода шаванд:
Усули 4. Гурўњбандї ва часпак дар Power Query
Шумо метавонед мушкилотро бе барномасозӣ дар VBA ҳал кунед, агар шумо иловаи ройгони Power Query -ро истифода баред. Барои Excel 2010-2013 онро дар ин ҷо зеркашӣ кардан мумкин аст ва дар Excel 2016 он аллакай бо нобаёнӣ сохта шудааст. пайдарпаии амалҳои зерин хоҳад буд:
Power Query намедонад, ки чӣ тавр бо ҷадвалҳои муқаррарӣ кор кунад, аз ин рӯ, қадами аввал ин табдил додани ҷадвали мо ба ҷадвали "ақл" аст. Барои ин, онро интихоб кунед ва комбинатсияро пахш кунед Ctrl+T ё аз ҷадвал интихоб кунед Хона - Формат ҳамчун ҷадвал (Хона — Формат ҳамчун ҷадвал). Дар ҷадвале, ки баъд пайдо мешавад Конструктор (Тарроҳӣ) шумо метавонед номи ҷадвалро таъин кунед (ман стандартро тарк кардам Љадвали 1):
Акнун биёед ҷадвали худро ба замимаи Power Query бор кунем. Барои ин, дар ҷадвал Маълумот (агар шумо Excel 2016 дошта бошед) ё дар ҷадвали Query Power (агар шумо Excel 2010-2013 дошта бошед) клик кунед Аз миз (Маълумот - аз ҷадвал):
Дар равзанаи муҳаррири дархост, ки кушода мешавад, сутунро бо пахш кардани сарлавҳа интихоб кунед ширкат ва тугмаи болоиро пахш кунед гурӯҳ (Гурӯҳ аз ҷониби). Номи сутуни нав ва навъи амалиётро дар гурӯҳбандӣ ворид кунед - Ҳама сатрҳо (Ҳама сатрҳо):
OK-ро клик кунед ва мо ҷадвали хурди арзишҳои гурӯҳбандишударо барои ҳар як ширкат мегирем. Мундариҷаи ҷадвалҳо ба таври равшан намоён мешаванд, агар шумо дар заминаи сафеди чашмакҳо (на дар матн!) дар сутуни натиҷавӣ тугмаи чапи мушро пахш кунед:
Акнун биёед як сутуни дигар илова кунем, ки дар он бо истифода аз функсия мундариҷаи сутунҳои Адресро дар ҳар як ҷадвали хурд бо вергул ҷудо карда часпонед. Барои ин, дар ҷадвал Сутун илова кунед пахш мекунем Сутуни фармоишӣ (Иловаи сутун - Сутуни фармоишӣ) ва дар равзанаи пайдошуда номи сутуни нав ва формулаи пайвасткуниро ба забони M, ки дар Power Query сохта шудааст, ворид кунед:
Дар хотир доред, ки ҳама функсияҳои M-ҳассос мебошанд (баръакси Excel). Пас аз пахш кардан OK мо сутуни нав бо суроғаҳои часпак мегирем:
Он барои хориҷ кардани сутуни аллакай нолозим боқӣ мемонад Суроғаҳои ҷадвал (дар сарлавҳаи рост клик кунед) Сутунро нест кунед) ва натиҷаҳоро бо пахш кардани ҷадвал ба варақ бор кунед Хона — Пӯшед ва зеркашӣ кунед (Хона — Пӯшед ва бор кунед):
Нюанси муҳим: Баръакси усулҳои (функсияҳои) қаблӣ, ҷадвалҳо аз Power Query ба таври худкор нав карда намешаванд. Агар дар оянда дар маълумоти сарчашма ягон тағйирот ба амал ояд, ба шумо лозим меояд, ки дар ягон ҷои ҷадвали натиҷаҳо тугмаи ростро клик кунед ва фармонро интихоб кунед. Навсозӣ ва Захира (навсозӣ).
- Чӣ тавр як сатри матни дарозро ба қисмҳо тақсим кардан мумкин аст
- Якчанд роҳҳо барои часпондани матн аз ҳуҷайраҳои гуногун ба як
- Истифодаи оператори Like барои санҷиши матн бар зидди ниқоб