Нядаўна да мяне звярнуўся сябар з просьбай дапамагчы сабраць усемагчымыя фразы, якія складаюцца з набору зададзеных слоў. Праблемы такога роду могуць узнікнуць пры складанні спісаў ключавых слоў і фраз для інтэрнэт-рэкламы і SEO-прасоўвання, калі трэба перабраць усе магчымыя перастаноўкі слоў у пошукавым запыце:
У матэматыцы гэтая аперацыя называецца Дэкартавы здабытак. Афіцыйнае вызначэнне гучыць так: дэкартавы здабытак мностваў A і B — гэта мноства ўсіх пар, першы кампанент якіх належыць мноству A, а другі — мноству B. Прычым элементамі мностваў могуць быць як лічбы і тэкст.
У перакладзе на чалавечую мову гэта азначае, што калі ў мностве А мы маем, напрыклад, словы «белы» і «чырвоны», а ў мностве B «БМВ» і «Мэрсэдэс», то пасля дэкартава здабытку гэтых двух набораў мы атрымаць на выхадзе набор усіх магчымых варыянтаў словазлучэнняў, складзеных са слоў абодвух спісаў:
- белы bmw
- чырвоны bmw
- белы мэрсэдэс
- чырвоны мэрсэдэс
… г.зн. менавіта тое, што нам трэба. Давайце разгледзім пару спосабаў вырашэння гэтай задачы ў Excel.
Спосаб 1. Формулы
Пачнем з формул. Выкажам здагадку, што ў якасці зыходных дадзеных у нас ёсць тры спісы зыходных слоў у слупках A, B і C адпаведна, і колькасць элементаў у кожным спісе можа быць рознай:
Спачатку зробім тры слупкі з індэксамі, г.зн. парадкавымі нумарамі слоў з кожнага спісу ва ўсіх магчымых спалучэннях. Першы радок адзінак (E2:G2) будзе ўведзены ўручную, а для астатніх будзем выкарыстоўваць наступную формулу:
Логіка тут простая: калі індэкс у вышэйстаячай папярэдняй ячэйцы ўжо дасягнуў канца спісу, г. зн. роўны колькасці элементаў у спісе, разлічаных функцыяй COUNT (COUNTA), то зноў пачынаем нумарацыю. У адваротным выпадку мы павялічваем індэкс на 1. Звярніце асаблівую ўвагу на разумную фіксацыю дыяпазонаў знакамі даляра ($), каб можна было скапіяваць формулу ўніз і направа.
Цяпер, калі ў нас ёсць парадкавыя нумары патрэбных нам слоў з кожнага спісу, мы можам атрымаць самі словы з дапамогай функцыі індэкс (ІНДЭКС) у тры асобныя слупкі:
Калі вы раней не сутыкаліся з гэтай функцыяй у сваёй працы, то настойліва раю вывучыць яе хаця б па дыяганалі - яна выручае ў многіх сітуацыях і карысная не менш (а нават больш!) ВПР (ВПР).
Ну а пасля гэтага застанецца толькі склеіць атрыманыя фрагменты па радках з дапамогай сімвала канкатэнацыі (&):
… або (калі ў вас апошняя версія Excel) з дапамогай зручнай функцыі Камбінат (ДАЛУЧЭННЕ ТЭКСТУ), які можа склейваць усё змесціва ўказаных вочак праз зададзены сімвал падзельніка (прабел):
Спосаб 2. Праз Power Query
Power Query - гэта магутная надбудова для Microsoft Excel, якая выконвае дзве асноўныя задачы: 1. загрузка даных у Excel практычна з любога вонкавага крыніцы і 2. усе віды пераўтварэнняў загружаных табліц. Power Query ўжо ўбудаваны ў Excel 2016-2019, а для Excel 2010-2013 усталяваны як асобная надбудова (можна спампаваць з афіцыйнага сайта Microsoft бясплатна). Калі вы яшчэ не пачалі выкарыстоўваць Power Query ў сваёй працы, то самы час задумацца, таму што пераўтварэнні, падобныя апісаным вышэй, там робяцца лёгка і натуральна, усяго ў пару рухаў.
Спачатку давайце загрузім зыходныя спісы як асобныя запыты ў Power Query. Для гэтага для кожнай табліцы выканайце наступныя дзеянні:
- Давайце з дапамогай кнопкі ператворым сталы ў «разумныя». Фармат у выглядзе табліцы таб Галоўная (Галоўная — фармат у табліцу) або спалучэнне клавіш Ctrl+T. Кожнай табліцы будзе аўтаматычна дадзена імя Табліца1,2,3…, які, аднак, можна змяніць пры жаданні на ўкладцы Канструктар (Дызайн).
- Усталяваўшы актыўную вочка ў табліцы, націсніце кнопку Са стала (З табліцы) таб Дата (Дата) або на ўкладцы Запыт на харчаванне (калі ён усталяваны як асобная надбудова для Excel 2010-2013).
- У якое адкрылася акне рэдактара запытаў абярыце каманду Галоўная — Зачыніць і загрузіць — Зачыніць і загрузіць у… (Галоўная — Зачыніць&Загрузіць — Зачыніць&Загрузіць у..) а потым варыянт Проста стварыце злучэнне (Стварыць толькі злучэнне). Гэта пакіне загружаную табліцу ў памяці і дазволіць атрымаць да яе доступ у будучыні.
Калі вы ўсё зробіце правільна, то на правай панэлі павінна выйсці тры запыту ў рэжыме Толькі злучэнне з нашымі назвамі табліц:
Цяпер пстрыкніце правай кнопкай мышы першы запыт і абярыце каманду спасылка (Даведка)каб зрабіць абнаўляемую копію, а затым дадаць дадатковы слупок да дадзеных з дапамогай каманды Даданне слупка ž – карыстацкі слупок (Дадаць слупок -ž карыстальніцкі слупок). У акне ўводу формулы ўвядзіце імя новага слупка (напрыклад, Фрагмент2) і надзвычай просты выраз у якасці формулы:
=Табліца2
... г.зн., іншымі словамі, імя другога запыту:
Пасля націску на кнопку OK мы ўбачым новы слупок, у кожнай ячэйцы якога будзе ўкладзеная табліца з фразамі з другой табліцы (змесціва гэтых табліц можна ўбачыць, калі клікнуць на фоне ячэйкі побач са словам табліца):
Засталося разгарнуць усё змесціва гэтых укладзеных табліц з дапамогай кнопкі з падвойнымі стрэлкамі ў загалоўку атрыманага слупка і зняўшы галачку Выкарыстоўвайце зыходнае імя слупка ў якасці прэфікса (Выкарыстоўваць зыходную назву слупка ў якасці прэфікса):
... і мы атрымліваем усе магчымыя камбінацыі элементаў з першых двух набораў:
Далей усё аналагічна. Дадайце яшчэ адзін вылічальны слупок з формулай:
=Табліца3
…, а затым зноў разгарнуць укладзеныя табліцы – і цяпер у нас ужо ёсць усе магчымыя варыянты перастаноўкі слоў з трох набораў адпаведна:
Засталося вылучыць усе тры слупка злева направа, утрымліваючы Ctrl, і аб'яднаць іх змесціва праз прабелы з дапамогай каманды Аб'яднаць слупкі (Аб'яднаць слупкі) з таб Пераўтварэнне (Ператварэнне):
Атрыманыя вынікі можна выгрузіць назад на ліст з дапамогай ужо знаёмай каманды Галоўная — Зачыніць і загрузіць — Зачыніць і загрузіць у… (Галоўная — Зачыніць&Загрузіць — Зачыніць&Загрузіць у..):
Калі ў будучыні нешта зменіцца ў нашых зыходных табліцах з фрагментамі, то дастаткова будзе проста абнавіць згенераваны запыт, пстрыкнуўшы правай кнопкай мышы на выніковай табліцы і выбраўшы каманду Абнавіць і захаваць (Абнавіць) або націснуўшы спалучэнне клавіш Ctrl+Alt+F5.
- Што такое Power Query, Power Pivot, Power Map і Power BI і навошта ім патрэбны карыстальнік Excel
- Стварэнне дыяграмы Ганта ў Power Query
- 5 спосабаў выкарыстання функцыі INDEX