Բովանդակություն
Հիմնական տեքստում հիմնաբառերի որոնումը տվյալների հետ աշխատելիս ամենատարածված խնդիրներից մեկն է: Դիտարկենք դրա լուծումը մի քանի ձևով՝ օգտագործելով հետևյալ օրինակը.
Ենթադրենք, որ դուք և ես ունենք հիմնաբառերի ցանկ՝ մեքենաների ապրանքանիշերի անվանումներ, և բոլոր տեսակի պահեստամասերի մեծ աղյուսակ, որտեղ նկարագրությունները երբեմն կարող են պարունակել մեկ կամ մի քանի նման ապրանքանիշներ միանգամից, եթե պահեստամասը տեղավորվում է մեկից ավելի: մակնիշի ավտոմեքենա. Մեր խնդիրն է գտնել և ցուցադրել բոլոր հայտնաբերված հիմնաբառերը հարևան բջիջներում տրված բաժանարար նիշի միջոցով (օրինակ՝ ստորակետ):
Մեթոդ 1. Power Query
Իհարկե, նախ մենք մեր աղյուսակները վերածում ենք դինամիկ («խելացի») ստեղնաշարի դյուրանցման միջոցով Ctrl+T կամ հրամաններ Գլխավոր – Ձևաչափել որպես աղյուսակ (Տուն — Ձևաչափել որպես աղյուսակ), նրանց անուններ տվեք (օրինակ Նամականիշերи Պահեստամասեր) և մեկ առ մեկ բեռնեք Power Query խմբագրիչում՝ ընտրելով ներդիրում Տվյալներ – Աղյուսակից / միջակայքից (Տվյալներ՝ աղյուսակից/միջակայքից). Եթե ունեք Excel 2010-2013-ի ավելի հին տարբերակներ, որտեղ Power Query-ը տեղադրված է որպես առանձին հավելում, ապա ցանկալի կոճակը կլինի ներդիրում: Power հարցում. Եթե ունեք Excel 365-ի բոլորովին նոր տարբերակ, ապա կոճակը Սեղանից / միջակայքից այժմ այնտեղ զանգահարեց Տերեւներով (Թերթից).
Power Query-ում յուրաքանչյուր աղյուսակ բեռնելուց հետո հրամանով վերադառնում ենք Excel Գլխավոր — Փակել և բեռնել — Փակել և բեռնել… — Ստեղծել միայն կապ (Տուն — Փակել և բեռնել — Փակել և բեռնել… — Ստեղծել միայն կապ).
Հիմա եկեք ստեղծենք կրկնօրինակ հարցում Պահեստամասերաջ սեղմելով դրա վրա և ընտրելով Կրկնօրինակ հարցում (Կրկնօրինակ հարցում), ապա վերանվանեք ստացված պատճենահանման հարցումը Արդյունքները և մենք կշարունակենք աշխատել նրա հետ:
Գործողությունների տրամաբանությունը հետևյալն է.
- Ընդլայնված ներդիրում Սյունակի ավելացում ընտրիր թիմ Պատվերով սյունակ (Ավելացնել սյունակ — Հատուկ սյունակ) և մուտքագրեք բանաձևը = Ապրանքանիշեր. Սեղմելուց հետո OK մենք կստանանք նոր սյունակ, որտեղ յուրաքանչյուր բջիջում կլինի տեղադրված աղյուսակ՝ մեր հիմնաբառերի ցանկով՝ ավտոարտադրող ապրանքանիշեր.
- Օգտագործեք կրկնակի սլաքներով կոճակը ավելացված սյունակի վերնագրում` բոլոր ներդիր աղյուսակները ընդլայնելու համար: Միևնույն ժամանակ, պահեստամասերի նկարագրություններով տողերը կբազմապատկվեն ապրանքանիշերի թվի բազմապատիկով, և մենք կստանանք «պահեստամասերի ապրանքանիշի» բոլոր հնարավոր զույգերը.
- Ընդլայնված ներդիրում Սյունակի ավելացում ընտրիր թիմ Պայմանական սյունակ (Պայմանական սյունակ) և պայման դրեք սկզբնաղբյուր տեքստում հիմնաբառի (ապրանքանիշի) հայտնվելը ստուգելու համար (մասերի նկարագրություն).
- Որոնման գործը անզգայուն դարձնելու համար ձեռքով ավելացրեք երրորդ փաստարկը բանաձևի տողում Համեմատել.OrdinalIgnoreCase առաջացման ստուգման գործառույթին Տեքստ.Պարունակում է (եթե բանաձևի տողը տեսանելի չէ, ապա այն կարելի է միացնել ներդիրում տեսություն):
- Ստացված աղյուսակը զտում ենք՝ վերջին սյունակում թողնելով միայն մեկը, այսինքն՝ համընկնում և հեռացնում ավելորդ սյունակը։ Դեպքեր.
- Նույնական նկարագրությունների խմբավորում հրամանով Խմբի կողմից ականջակալ Փոխակերպում (Փոխակերպում — Խմբավորում ըստ). Որպես միավորման գործողություն՝ ընտրեք Բոլոր տողերը (Բոլոր տողերը). Ելքում մենք ստանում ենք աղյուսակներով սյունակ, որը պարունակում է բոլոր մանրամասները յուրաքանչյուր պահեստամասի համար, ներառյալ մեզ անհրաժեշտ ավտոարտադրողների ապրանքանիշերը.
- Յուրաքանչյուր մասի համար գնահատականներ հանելու համար ներդիրում ավելացրեք ևս մեկ հաշվարկված սյունակ Սյունակի ավելացում – Պատվերով սյունակ (Ավելացնել սյունակ — Հատուկ սյունակ) և օգտագործեք աղյուսակից բաղկացած բանաձև (դրանք գտնվում են մեր սյունակում Մանրամասներ) և արդյունահանված սյունակի անվանումը.
- Մենք սեղմում ենք կրկնակի սլաքներով կոճակը ստացված սյունակի վերնագրում և ընտրում հրամանը Արժեքների արդյունահանում (Հանել արժեքները)Ձեր ուզած ցանկացած սահմանազատիչ նիշով նամականիշեր դուրս բերելու համար.
- Ավելորդ սյունակի հեռացում Մանրամասներ.
- Ստացված աղյուսակին ավելացնելու այն մասերը, որոնք անհետացել են դրանից, որտեղ նկարագրություններում ապրանքանիշեր չեն հայտնաբերվել, մենք կատարում ենք հարցումը համակցելու կարգը. Արդյունք օրիգինալ խնդրանքով Պահեստամասեր կոճակ Միավորել ականջակալ Գլխավոր (Տուն — Միավորել հարցումները). Միացման տեսակը - Արտաքին միացում աջ (Աջ արտաքին միացում):
- Մնում է հեռացնել ավելորդ սյունակները և վերանվանել-տեղափոխել մնացածները, և մեր խնդիրը լուծված է.
Մեթոդ 2. Բանաձևեր
Եթե ունեք Excel 2016 կամ ավելի նոր տարբերակ, ապա մեր խնդիրը կարող է լուծվել շատ կոմպակտ և էլեգանտ ձևով՝ օգտագործելով նոր գործառույթը։ COMBINE- ը (ՏԵՔՍՏՄԻՑ):
Այս բանաձևի տրամաբանությունը պարզ է.
- ֆունկցիա ՈՐՈՆՈՒՄ (ԳՏՆԵԼ) հերթով որոնում է յուրաքանչյուր ապրանքանիշի հայտնվելը մասի ընթացիկ նկարագրության մեջ և վերադարձնում է կամ խորհրդանիշի սերիական համարը, որից սկսած գտնվել է ապրանքանիշը, կամ #VALUE սխալը: եթե ապրանքանիշը նկարագրության մեջ չկա:
- Այնուհետև օգտագործելով գործառույթը IF (ԵԹԵ) и ԷՈՇԻԲԿԱ (ISERROR) մենք սխալները փոխարինում ենք դատարկ տեքստային տողով «», իսկ նիշերի հերթական համարները բրենդային անվանումներով:
- Ստացված դատարկ բջիջների և հայտնաբերված ապրանքանիշերի զանգվածը հավաքվում է մեկ տողի մեջ տվյալ բաժանարար նիշի միջոցով՝ օգտագործելով ֆունկցիան COMBINE- ը (ՏԵՔՍՏՄԻՑ).
Արդյունավետության համեմատություն և Power Query Query Buffering for Speedup
Արդյունավետության փորձարկման համար որպես նախնական տվյալներ վերցնենք 100 պահեստամասերի նկարագրությունների աղյուսակը: Դրա վրա մենք ստանում ենք հետևյալ արդյունքները.
- Վերահաշվարկի ժամանակը բանաձևերով (մեթոդ 2) – 9 վրկ. երբ առաջին անգամ պատճենեք բանաձեւը ամբողջ սյունակում և 2 վրկ. կրկնվող (բուֆերային ազդեցություն, հավանաբար):
- Power Query հարցման (մեթոդ 1) թարմացման ժամանակը շատ ավելի վատ է՝ 110 վայրկյան:
Իհարկե, շատ բան կախված է կոնկրետ ԱՀ-ի սարքաշարից և Office-ի տեղադրված տարբերակից ու թարմացումներից, բայց ընդհանուր պատկերը, կարծում եմ, պարզ է։
Power Query հարցումը արագացնելու համար եկեք բուֆերացնենք որոնման աղյուսակը Նամականիշեր, քանի որ այն չի փոխվում հարցումների կատարման գործընթացում և անհրաժեշտ չէ անընդհատ վերահաշվարկել այն (ինչպես դա անում է Power Query-ը դե ֆակտո)։ Դրա համար մենք օգտագործում ենք ֆունկցիան Աղյուսակ.Բուֆեր ներկառուցված Power Query լեզվից M.
Դա անելու համար բացեք հարցում Արդյունքները և ներդիրի վրա տեսություն սեղմել կոճակը Ընդլայնված խմբագիր (Դիտել — Ընդլայնված խմբագիր). Բացվող պատուհանում ավելացրեք տող նոր փոփոխականով Մարկի 2, որը կլինի մեր ավտոարտադրողի գրացուցակի բուֆերացված տարբերակը և հետագայում օգտագործեք այս նոր փոփոխականը հետևյալ հարցման հրամանում.
Նման ճշգրտումից հետո մեր հարցման թարմացման արագությունը մեծանում է գրեթե 7 անգամ՝ մինչև 15 վայրկյան: Բոլորովին այլ բան 🙂
- Անորոշ տեքստի որոնում Power Query-ում
- Զանգվածային տեքստի փոխարինում բանաձևերով
- Power Query-ում զանգվածային տեքստի փոխարինում List.Acumulate ֆունկցիայով