Жақында бір досым маған берілген сөздердің жиынтығынан тұратын барлық мүмкін сөз тіркестерін жасауға көмектесуін өтініп келді. Интернеттегі жарнама және SEO-ны жылжыту үшін кілт сөздер мен сөз тіркестерінің тізімін жасау кезінде, іздеу сұрауында сөздердің барлық ықтимал ауыстыруларынан өту қажет болғанда осындай мәселелер туындауы мүмкін:
Математикада бұл операция деп аталады Декарттық өнім. Ресми анықтама келесідей: А және В жиындарының декарттық көбейтіндісі бірінші құрамдас бөлігі А жиынына, ал екінші құрамдас бөлігі В жиынына жататын барлық жұптардың жиыны. Сонымен қатар жиындардың элементтері екеуі де болуы мүмкін. сандар мен мәтін.
Адам тіліне аударғанда бұл дегеніміз, егер А жиынында бізде, мысалы, «ақ» және «қызыл» сөздері болса, ал В жиынында «BMW» және «Mercedes» болса, онда осы екі жиынның декарттық көбейтіндісінен кейін біз get on output – екі тізімдегі сөздерден құралған сөз тіркестерінің барлық мүмкін нұсқаларының жиынтығы:
- ақ bmw
- қызыл bmw
- ақ Мерседес
- қызыл мерседес
… яғни бізге қажет нәрсе. Excel бағдарламасында бұл тапсырманы шешудің бірнеше жолын қарастырайық.
1-әдіс. Формулалар
Формулалардан бастайық. Бастапқы деректер ретінде бізде сәйкесінше A, B және C бағандарындағы түпнұсқа сөздердің үш тізімі бар және әрбір тізімдегі элементтер саны әртүрлі болуы мүмкін делік:
Алдымен индекстері бар үш бағанды, яғни барлық мүмкін комбинациялардағы әрбір тізімнен сөздердің реттік нөмірлерін жасайық. Бірліктердің бірінші жолы (E2:G2) қолмен енгізіледі, ал қалғандары үшін келесі формуланы қолданамыз:
Мұндағы логика қарапайым: егер алдыңғы алдыңғы ұяшықтағы индекс тізімнің соңына жеткен болса, яғни функциямен есептелген тізімдегі элементтер санына тең болса. COUNT (COUNTA), содан кейін нөмірлеуді қайта бастаймыз. Әйтпесе, индексті 1-ге көбейтеміз. Формуланы төмен және оңға көшіру үшін диапазондарды доллар белгілерімен ($) ақылды бекітуге ерекше назар аударыңыз.
Енді бізде әр тізімнен қажетті сөздердің реттік нөмірлері бар болғандықтан, функцияны пайдаланып сөздердің өзін шығара аламыз. INDEX (ИНДЕКС) үш бөлек бағанға:
Егер сіз бұл функцияны жұмысыңызда бұрын кездестірмеген болсаңыз, мен сізге оны ең болмағанда диагональ бойынша зерттеуге кеңес беремін - бұл көптеген жағдайларда көмектеседі және пайдалы емес (және одан да көп!) VPR (КӨРУ).
Осыдан кейін, жалғау белгісін (&) пайдаланып, алынған фрагменттерді сызық бойынша желімдеу ғана қалады:
… немесе (егер сізде Excel бағдарламасының соңғы нұсқасы болса) ыңғайлы функциясы бар КОМБИНА (TEXTJOIN), ол көрсетілген ұяшықтардың барлық мазмұнын берілген бөлгіш таңба (бос орын) арқылы желімдей алады:
2-әдіс. Power Query арқылы
Power Query — екі негізгі тапсырманы орындайтын Microsoft Excel бағдарламасына арналған қуатты қондырма: 1. деректерді кез келген сыртқы көзден Excel бағдарламасына жүктеу және 2. жүктелген кестелерді түрлендірудің барлық түрлері. Power Query қазірдің өзінде Excel 2016-2019 нұсқасына орнатылған, ал Excel 2010-2013 үшін ол бөлек қондырма ретінде орнатылған (оны Microsoft ресми веб-сайтынан тегін жүктеп алуға болады). Егер сіз әлі жұмысыңызда Power Query қолданбасын пайдалануды бастамасаңыз, онда бұл туралы ойланатын кез келді, өйткені жоғарыда сипатталған түрлендірулер онда оңай және табиғи түрде, бір-екі қозғалыста жасалады.
Алдымен, бастапқы тізімдерді Power Query ішінде бөлек сұраулар ретінде жүктеп алайық. Ол үшін әрбір кесте үшін келесі қадамдарды орындаңыз:
- Кестелерді түйме арқылы «ақылдыға» айналдырайық Кесте ретінде пішімдеу қойындысы Home (Үй — Кесте ретінде пішімдеу) немесе пернелер тіркесімі Ctrl+T. Әрбір кестеге автоматты түрде атау беріледі Кесте 1,2,3…, бірақ оны қойындыда қажет болса өзгертуге болады Конструктор (Дизайн).
- Кестедегі белсенді ұяшықты орнатқаннан кейін түймені басыңыз Үстелден (Кестеден) қойындысы мәліметтер (Күні) немесе қойындыда Қуат сұрауы (егер сіз оны Excel 2010-2013 үшін бөлек қондырма ретінде орнатқан болсаңыз).
- Ашылған сұраныс өңдегішінің терезесінде пәрменді таңдаңыз Негізгі бет — Жабу және жүктеу — Жабу және жүктеу… (Басты бет — Жабу&Жүктеу — Жабу&Жүктеу..) содан кейін опция Тек қосылым жасаңыз (Тек қосылым жасау). Бұл жүктелген кестені жадта қалдырады және болашақта оған қол жеткізуге мүмкіндік береді.
Егер сіз бәрін дұрыс орындасаңыз, оң жақ панельдегі шығыс режимде үш сұрау болуы керек Тек қосылым кесте атауларымен:
Енді бірінші сұрауды тінтуірдің оң жақ түймешігімен басып, пәрменді таңдаңыз байланыс (Сілтеме)оның жаңартылатын көшірмесін жасаңыз, содан кейін пәрмен арқылы деректерге қосымша баған қосыңыз Бағанды қосу ž – Теңшелетін баған (Баған қосу-ž Теңшелетін баған). Формула енгізу терезесінде жаңа бағанның атын (мысалы, Fragment2) және формула ретінде өте қарапайым өрнекті енгізіңіз:
=2-кесте
… яғни, басқаша айтқанда, екінші сұраудың атауы:
Басқаннан кейін OK біз жаңа бағанды көреміз, оның әрбір ұяшығында екінші кестедегі сөз тіркестері бар кірістірілген кесте болады (сөздің жанындағы ұяшықтың фонында бассаңыз, осы кестелердің мазмұнын көре аласыз. үстел):
Алынған бағанның тақырыбындағы қос көрсеткілері бар түймені пайдаланып, осы кірістірілген кестелердің барлық мазмұнын кеңейту қалады және құсбелгіні алып тастаңыз. Бастапқы баған атауын префикс ретінде пайдаланыңыз (Бағананың бастапқы атауын префикс ретінде пайдаланыңыз):
... және біз алғашқы екі жиынтықтан элементтердің барлық мүмкін комбинацияларын аламыз:
Әрі қарай, бәрі ұқсас. Формула бар басқа есептелген бағанды қосыңыз:
=3-кесте
..., содан кейін кірістірілген кестелерді қайтадан кеңейтіңіз - және қазір бізде сәйкесінше үш жиыннан сөздерді ауыстырудың барлық мүмкін нұсқалары бар:
Барлық үш бағанды солдан оңға қарай басып тұрып таңдау қалады Ctrl, және пәрмен арқылы бос орындармен бөлінген олардың мазмұнын біріктіріңіз Бағандарды біріктіру (Бағандарды біріктіру) қойындысынан трансформация (Трансформация):
Алынған нәтижелерді бұрыннан таныс пәрмен арқылы қайтадан параққа шығаруға болады Негізгі бет — Жабу және жүктеу — Жабу және жүктеу… (Басты бет — Жабу&Жүктеу — Жабу&Жүктеу..):
Егер болашақта біздің бастапқы кестелерімізде фрагменттері бар бірдеңе өзгерсе, алынған кестені тінтуірдің оң жақ түймешігімен басып, пәрменді таңдау арқылы жасалған сұрауды жаңарту жеткілікті болады. Жаңарту және сақтау (Жаңарту) немесе пернелер тіркесімін басу арқылы Ctrl+Alt+F5.
- Power Query, Power Pivot, Power Map және Power BI дегеніміз не және оларға Excel пайдаланушысы не үшін қажет
- Power Query бағдарламасында Гант диаграммасын құру
- INDEX функциясын пайдаланудың 5 жолы