Տեքստի կապում ըստ պայմանի

Ես արդեն գրել եմ այն ​​մասին, թե ինչպես կարող եք արագ սոսնձել տեքստը մի քանի բջիջներից մեկի մեջ և, ընդհակառակը, երկար տեքստային տողը վերլուծել բաղադրիչների: Հիմա եկեք նայենք սերտ, բայց մի փոքր ավելի բարդ առաջադրանքին՝ ինչպես սոսնձել տեքստը մի քանի բջիջներից, երբ բավարարված է որոշակի պայման: 

Ասենք, որ մենք ունենք հաճախորդների տվյալների բազա, որտեղ մեկ ընկերության անվանումը կարող է համապատասխանել իր աշխատակիցների մի քանի տարբեր էլ. Մեր խնդիրն է հավաքել բոլոր հասցեները ընկերությունների անուններով և միացնել դրանք (բաժանված ստորակետերով կամ կիսատ-ստորակետերով), որպեսզի, օրինակ, փոստացուցակ կազմենք հաճախորդների համար, այսինքն.

Այլ կերպ ասած, մեզ անհրաժեշտ է գործիք, որը կսոսնձի (կկապի) տեքստը ըստ պայմանի՝ ֆունկցիայի անալոգը։ ՍՈՒՄՄԵՍԼԻ (SUMIF), բայց տեքստի համար։

Մեթոդ 0. Բանաձև

Ոչ շատ էլեգանտ, բայց ամենահեշտ ձևը: Դուք կարող եք գրել մի պարզ բանաձև, որը կստուգի, թե արդյոք հաջորդ շարքի ընկերությունը տարբերվում է նախորդից: Եթե ​​այն չի տարբերվում, ապա կպցրեք հաջորդ հասցեն՝ բաժանված ստորակետով: Եթե ​​այն տարբերվում է, ապա մենք «վերակայում» ենք կուտակվածը՝ նորից սկսելով.

Այս մոտեցման թերությունները ակնհայտ են. ստացված լրացուցիչ սյունակի բոլոր բջիջներից մեզ անհրաժեշտ են միայն վերջինները յուրաքանչյուր ընկերության համար (դեղին): Եթե ​​ցուցակը մեծ է, ապա դրանք արագ ընտրելու համար պետք է ֆունկցիայի միջոցով ավելացնել ևս մեկ սյունակ DLSTR (ԼԵՆ), ստուգելով կուտակված տողերի երկարությունը.

Այժմ դուք կարող եք զտել դրանք և պատճենել անհրաժեշտ հասցեի սոսնձումը հետագա օգտագործման համար:

Մեթոդ 1. Սոսնձման մակրոֆունկցիան մեկ պայմանով

Եթե ​​սկզբնական ցուցակը տեսակավորված չէ ըստ ընկերության, ապա վերը նշված պարզ բանաձևը չի աշխատում, բայց դուք հեշտությամբ կարող եք շրջանցել VBA-ում փոքր մաքսային ֆունկցիայի միջոցով: Բացեք Visual Basic խմբագրիչը՝ սեղմելով ստեղնաշարի դյուրանցումը Alt + F11 կամ օգտագործելով կոճակը Visual Basic ականջակալ երեվակիչ (մշակող). Բացվող պատուհանում մենյուի միջոցով տեղադրեք նոր դատարկ մոդուլ Ներդիր – մոդուլ և պատճենեք մեր ֆունկցիայի տեքստը այնտեղ.

Function MergeIf (TextRange As Range, Search Range As Range, Condition as String) Dim Delimeter As String, i As Long Delimeter = ", " սոսնձումները իրար հավասար չեն. մենք դուրս ենք գալիս սխալով, եթե SearchRange.Count <> TextRange.Count Այնուհետև MergeIf = CVERr(xlErrRef) Ելք ֆունկցիան Ավարտ Եթե 'անցեք բոլոր բջիջներով, ստուգեք պայմանը և հավաքեք տեքստը փոփոխական OutText For i = 1 To Search Range: Cells.Count If SearchRange.Cells(i) Like Condition then OutText = OutText & TextRange.Cells(i) & Delimeter Հաջորդը ես ցուցադրում եմ արդյունքները առանց վերջին սահմանազատողի MergeIf = Left(OutText, Len(OutText) - Len(Delimeter)) Վերջ ֆունկցիան  

Եթե ​​այժմ վերադառնում եք Microsoft Excel, ապա գործառույթների ցանկում (կոճակ fx բանաձևի տողում կամ ներդիրում Բանաձևեր – Տեղադրեք ֆունկցիա) հնարավոր կլինի գտնել մեր ֆունկցիան MergeIf կատեգորիայում Օգտագործողը սահմանվում է (Օգտվողի կողմից սահմանված). Գործառույթի արգումենտները հետևյալն են.

Մեթոդ 2. Միացնել տեքստը ոչ ճշգրիտ պայմանով

Եթե ​​մեր մակրոյի 13-րդ տողում փոխարինենք առաջին նիշը = մոտավոր համընկնման օպերատորին նման, ապա հնարավոր կլինի սոսնձում կատարել սկզբնական տվյալների ոչ ճշգրիտ համապատասխանությամբ ընտրության չափանիշի հետ։ Օրինակ, եթե ընկերության անվանումը կարելի է գրել տարբեր տարբերակներով, ապա մենք կարող ենք ստուգել և հավաքել դրանք բոլորը մեկ գործառույթով.

Ստանդարտ wildcards-ը աջակցվում են.

• աստղանիշ (*) - նշանակում է ցանկացած թվով նիշ (ներառյալ դրանց բացակայությունը)
• հարցական նշան (?) – նշանակում է ցանկացած առանձին նիշ
• ֆունտի նշան (#) – նշանակում է ցանկացած մեկ թվանշան (0-9)

Լռելյայնորեն, Like օպերատորը մեծատառերի զգայուն է, այսինքն՝ հասկանում է, օրինակ, «Orion»-ը և «orion»-ը որպես տարբեր ընկերություններ: Մեծատառերը անտեսելու համար կարող եք տողը ավելացնել Visual Basic խմբագրիչում մոդուլի հենց սկզբում Տարբերակ Համեմատել տեքստը, որը կփոխի Like-ը մեծատառերի անզգայուն լինելու համար:

Այսպիսով, դուք կարող եք ստեղծել շատ բարդ դիմակներ պայմանները ստուգելու համար, օրինակ.

• ?1##??777RUS – 777 մարզի բոլոր համարանիշների ընտրություն՝ սկսած 1-ից.
• ՍՊԸ* – բոլոր ընկերությունները, որոնց անվանումը սկսվում է ՍՊԸ-ով
• ##7## – բոլոր ապրանքները հնգանիշ թվային կոդով, որտեղ երրորդ նիշը 7 է
• ????? - հինգ տառերի բոլոր անունները և այլն:
  

Մեթոդ 3. Երկու պայմանով տեքստի սոսնձման մակրո ֆունկցիա

Աշխատանքում կարող է խնդիր առաջանալ, երբ անհրաժեշտ է տեքստը կապել մեկից ավելի պայմանի հետ: Օրինակ, եկեք պատկերացնենք, որ մեր նախորդ աղյուսակում ավելացվել է ևս մեկ սյուն քաղաքով, և սոսնձումը պետք է իրականացվի ոչ միայն տվյալ ընկերության, այլ նաև տվյալ քաղաքի համար։ Այս դեպքում մեր գործառույթը պետք է մի փոքր արդիականացվի՝ դրան ավելացնելով մեկ այլ միջակայքի ստուգում.

Function MergeIfs (TextRange As Range, Search Range1 as Range, Condition1 As String, Search Range2 as Range, Condition2 As String) Dim Delimeter As String, i As Long Delimeter = ", " 'սահմանազատիչ նիշ (կարելի է փոխարինել բացատով կամ ; այլն) ե.) «եթե վավերացման և սոսնձման միջակայքերը իրար հավասար չեն, դուրս եկեք սխալմամբ, եթե SearchRange1.Count <> TextRange.Count կամ SearchRange2.Count <> TextRange.Count Հետո MergeIfs = CVERr(xlErrRef) Ելք ֆունկցիան Ավարտ, եթե 'անցեք բոլոր բջիջներով, ստուգեք բոլոր պայմանները և հավաքեք տեքստը OutText փոփոխականի մեջ For i = 1 To SearchRange1.Cells.Count If SearchRange1.Cells(i) = Condition1 And SearchRange2.Cells(i) = Condition2 Այնուհետեւ OutText = OutText & TextRange.Cells(i) & Delimeter End If Next i 'ցուցադրում եմ արդյունքները առանց վերջին սահմանազատողի MergeIfs = Left(OutText, Len(OutText) - Len(Delimeter)) Վերջի ֆունկցիա  

Այն կկիրառվի ճիշտ նույն ձևով. այժմ միայն փաստարկները պետք է ավելին նշել.

Մեթոդ 4. Խմբավորում և սոսնձում Power Query-ում

Խնդիրը կարող եք լուծել առանց VBA-ում ծրագրավորման, եթե օգտագործում եք անվճար Power Query հավելումը։ Excel 2010-2013-ի համար այն կարելի է ներբեռնել այստեղ, իսկ Excel 2016-ում այն ​​արդեն ներկառուցված է լռելյայն: Գործողությունների հաջորդականությունը կլինի հետևյալը.

Power Query-ն չգիտի, թե ինչպես աշխատել սովորական աղյուսակների հետ, ուստի առաջին քայլը մեր աղյուսակը «խելացի» դարձնելն է: Դա անելու համար ընտրեք այն և սեղմեք համակցությունը Ctrl+T կամ ընտրեք ներդիրից Գլխավոր – Ձևաչափել որպես աղյուսակ (Տուն — Ձևաչափել որպես աղյուսակ). Այնուհետև հայտնվող ներդիրում շինարար (Դիզայն) դուք կարող եք սահմանել աղյուսակի անունը (ես թողել եմ ստանդարտը Աղյուսակ 1):

Հիմա եկեք բեռնենք մեր աղյուսակը Power Query հավելումում: Դա անելու համար ներդիրում Ամսաթիվ (եթե ունեք Excel 2016) կամ Power Query ներդիրում (եթե ունեք Excel 2010-2013) սեղմեք Սեղանից (Տվյալներ - աղյուսակից):

Հարցման խմբագրիչի բացվող պատուհանում ընտրեք սյունակը` սեղմելով վերնագրի վրա Ընկերության մասին և սեղմեք վերևի կոճակը խումբ (Խմբով ըստ). Մուտքագրեք նոր սյունակի անվանումը և գործողության տեսակը խմբավորման մեջ. Բոլոր տողերը (Բոլոր տողերը):

Սեղմեք OK և մենք ստանում ենք խմբավորված արժեքների մինի աղյուսակ յուրաքանչյուր ընկերության համար: Աղյուսակների բովանդակությունը հստակ տեսանելի է, եթե արդյունքում սյունակում ձախ սեղմեք բջիջների սպիտակ ֆոնի վրա (ոչ տեքստի վրա):

Այժմ ավելացնենք ևս մեկ սյունակ, որտեղ, օգտագործելով ֆունկցիան, յուրաքանչյուր մինի աղյուսակում սոսնձում ենք Address սյունակների պարունակությունը՝ բաժանված ստորակետերով։ Դա անելու համար ներդիրում Ավելացնել սյունակ մենք սեղմում ենք Պատվերով սյունակ (Ավելացնել սյունակ — Հատուկ սյունակ) և երևացող պատուհանում մուտքագրեք նոր սյունակի անունը և միացման բանաձևը Power Query-ում ներկառուցված M լեզվով.

Նկատի ունեցեք, որ բոլոր M-ֆունկցիաները մեծատառերի զգայուն են (ի տարբերություն Excel-ի): Սեղմելուց հետո OK մենք ստանում ենք նոր սյունակ սոսնձված հասցեներով.

Մնում է հեռացնել առանց այն էլ անհարկի սյունակը Սեղանի հասցեներ (աջ սեղմեք վերնագրի վրա) Ջնջել սյունակը) և արդյունքները վերբեռնեք թերթում՝ սեղմելով ներդիրի վրա Գլխավոր — Փակել և ներբեռնել (Տուն — Փակել և բեռնել):

Կարևոր նրբերանգԻ տարբերություն նախորդ մեթոդների (գործառույթների), Power Query-ի աղյուսակները ինքնաբերաբար չեն թարմացվում: Եթե ​​ապագայում աղբյուրի տվյալների մեջ փոփոխություններ լինեն, ապա ձեզ հարկավոր է աջ սեղմել արդյունքների աղյուսակի ցանկացած կետում և ընտրել հրամանը. Թարմացնել և պահպանել (Թարմացնել).

• Ինչպես բաժանել երկար տեքստային տողը մասերի
• Տարբեր բջիջներից մեկում տեքստը սոսնձելու մի քանի եղանակ
• Օգտագործելով Like օպերատորը՝ տեքստը դիմակի դեմ փորձարկելու համար