Мундариҷа
Яке аз видеоҳои аз ҳама тамошобин дар канали YouTube-и ман ин видео дар бораи Flash Fill дар Microsoft Excel мебошад. Моҳияти ин асбоб дар он аст, ки агар ба шумо лозим аст, ки ба ягон тариқ маълумоти сарчашмаи худро тағир диҳед, пас шумо танҳо бояд ба чоп кардани натиҷае, ки мехоҳед дар сутуни ҳамсоя ба даст оред, оғоз кунед. Пас аз чанд ячейкаҳои дастӣ чопшуда (одатан 2-3 кифоя аст), Excel мантиқи тағиротҳои ба шумо лозимиро "мефаҳмад" ва ба таври худкор он чизеро, ки шумо чоп кардаед, идома медиҳад ва тамоми кори якрангро барои шумо анҷом медиҳад:
Квинтэссенсияи самаранокӣ. Тугмаи ҷодугарии "инро дуруст кунед", ки ҳамаи мо онро хеле дӯст медорем, дуруст?
Дар асл, аналоги чунин асбоб дар Power Query мавҷуд аст - он ҷо номида мешавад Сутун аз мисолҳо (Сутун аз мисолҳо). Дар асл, ин як зеҳни хурди сунъӣ дар Power Query сохта шудааст, ки метавонад аз маълумоти шумо зуд омӯхта ва сипас онро табдил диҳад. Биёед имкониятҳои онро дар якчанд сенарияи амалӣ бодиққат дида бароем, то бифаҳмем, ки он дар вазифаҳои воқеӣ барои мо муфид буда метавонад.
Намунаи 1. Матн часпак/буридан
Фарз мекунем, ки мо дар Excel чунин ҷадвали "ақл" дорем, ки маълумот дар бораи кормандон дорад:
Онро ба Power Query бо роҳи стандартӣ - бо тугма бор кунед Аз ҷадвал/диапазон ҷадвали Маълумот (Маълумот - Аз ҷадвал/диапазон).
Фарз мекунем, ки мо бояд сутунеро бо фамилия ва ҳарфҳои ибтидоии ҳар як корманд илова кунем (Иванов С.В. барои коргари аввал ва ғайра). Барои ҳалли ин мушкилот шумо метавонед яке аз ду усулро истифода баред:
- ба сарлавҳаи сутун бо маълумоти сарчашма рост клик кунед ва фармонро интихоб кунед Аз мисолҳо сутун илова кунед (Иловаи сутун аз мисолҳо);
- як ё якчанд сутунро бо маълумот ва дар ҷадвал интихоб кунед Илова кардани сутун даста интихоб кунед Сутун аз мисолҳо. Дар ин ҷо, дар рӯйхати афтанда шумо метавонед муайян кунед, ки оё ҳама ё танҳо сутунҳои интихобшуда бояд таҳлил карда шаванд.
Пас ҳама чиз оддӣ аст - дар сутуне, ки дар тарафи рост пайдо мешавад, мо ворид кардани намунаҳои натиҷаҳои дилхоҳро оғоз мекунем ва зеҳни сунъӣ, ки дар Power Query сохта шудааст, мекӯшад мантиқи табдили моро дарк кунад ва минбаъд мустақилона идома диҳад:
Дар омади гап, шумо метавонед вариантҳои дурустро дар ҳама ячейкаҳои ин сутун ворид кунед, яъне на ҳатман аз боло ба поён ва дар як саф. Инчунин, шумо метавонед баъдтар бо истифода аз қуттиҳои қайдкуни дар сатри унвон сутунҳоро аз таҳлил илова ё хориҷ кунед.
Ба формулае, ки дар болои тиреза ҷойгир аст, диққат диҳед - ин он чизест, ки Power Query интеллектуалӣ барои ба даст овардани натиҷаҳои ба мо зарурӣ эҷод мекунад. Дар омади гап, ин фарқияти асосии байни ин асбоб ва аст Пуркунии фаврӣ дар Excel. Пуркунии фаврӣ мисли "қуттии сиёҳ" кор мекунад - онҳо ба мо мантиқи тағиротро нишон намедиҳад, балки танҳо натиҷаҳои тайёр медиҳанд ва мо онҳоро як чизи муқаррарӣ қабул мекунем. Дар ин ҷо ҳама чиз шаффоф аст ва шумо ҳамеша метавонед комилан равшан бифаҳмед, ки бо маълумот чӣ воқеа рӯй медиҳад.
Агар шумо бинед, ки Power Query идеяро "ба даст овард", пас шумо метавонед тугмаро бехатар пахш кунед OK ё миёнабури клавиатура Ctrl+даромадан – сутуни фармоишӣ бо формулаи ихтироъкардаи Power Query сохта мешавад. Дар омади гап, онро баъдтар ҳамчун сутуни муқаррарии дастӣ сохташуда ба осонӣ таҳрир кардан мумкин аст (бо фармони Илова кардани сутун - Сутуни фармоишӣ) бо пахш кардани тасвири фишанг дар тарафи рости номи қадам:
Мисоли 2: Ҳолат тавре ки дар ҷумлаҳост
Агар шумо бо тугмаи рости сутуни матн пахш кунед ва фармонро интихоб кунед дигаргунсозии (Тағйир додан), пас шумо метавонед се фармонро барои тағир додани реестр мебинед:
Қулай ва олиҷаноб, аммо дар ин рӯйхат, масалан, ман шахсан ҳамеша як варианти дигар надоштам - дар ҳолате, ки дар ҷумлаҳо, вақте ки калонҳаҷм (капитал) ҳарфи аввал дар ҳар як калима нест, балки танҳо ҳарфи аввал дар ячейка мегардад ва қисми боқимондаи матн, вақте ки Ин бо ҳарфҳои хурд (хурд) нишон дода мешавад.
Ин хусусияти гумшуда бо зеҳни сунъӣ татбиқ кардан осон аст Сутунҳо аз мисолҳо - танҳо якчанд вариантро барои Power Query ворид кунед, то бо ҳамон рӯҳ идома диҳанд:
Ҳамчун формула дар ин ҷо, Power Query як қатор функсияҳоро истифода мебарад Матн. боло и Матн. Поён, табдил додани матн ба ҳарфҳои калон ва хурд, ва функсияҳо Матн. Оғоз и Матн. Миёна – аналогҳои функсияҳои Excel LEFT ва PSTR, ки қодиранд аз матн аз чап ва мобайн зерхатро ҷудо кунанд.
Мисоли 3. Ивазкунии калимаҳо
Баъзан ҳангоми коркарди маълумоти гирифташуда зарурати аз нав ҷойгир кардани калимаҳо дар ячейкаҳо бо пайдарпаии дода мешавад. Албатта, шумо метавонед сутунро ба сутунҳои калимаҳои алоҳида бо ҷудокунанда тақсим кунед ва сипас онро бо тартиби муқарраршуда часпонед (илова кардани фосиларо фаромӯш накунед), аммо бо ёрии асбоб Сутун аз мисолҳо ҳама чиз хеле осонтар мешавад:
Мисоли 4: Танҳо рақамҳо
Дигар вазифаи хеле муҳим ин берун кардани танҳо рақамҳо (рақамҳо) аз мундариҷаи ҳуҷайра мебошад. Мисли пештара, пас аз боркунии маълумот ба Power Query, ба ҷадвал равед Илова кардани сутун - Сутун аз мисолҳо ва якчанд ячейкаҳоро дастӣ пур кунед, то барнома фаҳмад, ки мо маҳз чиро гирифтан мехоҳем:
Блока!
Боз ҳам, ба болои тиреза нигоҳ кардан лозим аст, то боварӣ ҳосил кунем, ки Query формуларо дуруст тавлид кардааст - дар ин ҳолат он дорои функсия аст. Матн. Интихоб кунед, ки чунон ки шумо тахмин карда метавонед, аломатҳои додашударо аз матни сарчашма мувофиқи рӯйхат хориҷ мекунад. Баъдан, ин рӯйхатро, албатта, дар ҳолати зарурӣ дар сатри формула ба осонӣ таҳрир кардан мумкин аст.
Мисол 5: Танҳо матн
Мисли мисоли қаблӣ, шумо метавонед берун кашед ва баръакс - танҳо матн, нест кардани ҳамаи рақамҳо, аломатҳои пунктуатсия ва ғайра.
Дар ин ҳолат, функсияе, ки аллакай маънояш муқобил аст, истифода мешавад - Text.Remove, ки аломатҳоро аз сатри аслӣ мувофиқи рӯйхати додашуда нест мекунад.
Мисоли 6: Истихроҷи маълумот аз порчаи алифбои рақамӣ
Power Query инчунин метавонад дар ҳолатҳои душвортаре кӯмак кунад, вақте ки ба шумо лозим аст, ки маълумоти муфидро аз порриджи алифбои рақамӣ дар ячейка гиред, масалан, рақами суратҳисобро аз тавсифи мақсади пардохт дар изҳороти бонк гиред:
Дар хотир доред, ки формулаи табдили Power Query тавлидшуда метавонад хеле мураккаб бошад:
Барои осонии хондан ва фаҳмиш, онро метавон бо истифода аз хидмати ройгони онлайн ба шакли оқилонатар табдил дод. Форматгари пурсиш:
Чизи хеле қулай - эҳтиром ба эҷодкорон!
Мисоли 7: Табдил додани санаҳо
асбоби Сутун аз мисолҳо инчунин метавонад ба сутунҳои сана ё санаи вақт татбиқ карда шавад. Вақте ки шумо рақамҳои аввали санаро ворид мекунед, Power Query рӯйхати ҳамаи имконоти табдилдиҳии имконпазирро ба таври муфид намоиш медиҳад:
Ҳамин тавр, шумо метавонед ба осонӣ санаи аслиро ба ҳама гуна формати экзотикӣ табдил диҳед, масалан "сол-моҳ-рӯз":
Мисоли 8: Гурӯҳбандӣ
Агар мо асбобро истифода барем Сутун аз мисолҳо ба сутуни дорои маълумоти рақамӣ, он гуногун кор мекунад. Фарз мекунем, ки мо натиҷаҳои санҷиши кормандонро ба Power Query бор кардаем (холҳои шартӣ дар диапазони 0-100) ва мо дараҷаи шартии зеринро истифода мебарем:
- Магистрҳо – шахсоне, ки зиёда аз 90 хол гирифтаанд
- Коршиносон - аз 70 то 90 хол гирифтанд
- Истифодабарандагон - аз 30 то 70
- Шурӯъкунандагон - онҳое, ки камтар аз 30 хол гирифтаанд
Агар мо сутунеро аз мисолҳо ба рӯйхат илова кунем ва ба тартиб додани ин градатсияҳо ба таври дастӣ оғоз кунем, он гоҳ ба зудӣ Power Query идеяи моро қабул мекунад ва сутунеро бо формула илова мекунад, ки дар он операторҳо дар байни ҳамдигар ҷойгир шудаанд. if мантиқ амалӣ карда мешавад, ки ба он чизе, ки ба мо лозим аст, хеле монанд аст:
Боз, шумо наметавонед вазъро то охир пахш кунед, аммо клик кунед OK ва он гоҳ арзишҳои ҳадди аксарро дар формула ислоҳ кунед - ин тезтар аст:
Хулоса
Албатта асбоб Сутун аз мисолҳо «ҳабҳои ҷодугарӣ» нест ва дер ё зуд дар маълумот ҳолатҳои ғайримуқаррарӣ ва ё махсусан беэътиноӣ дар «колхозӣ» ба амал меоянд, ки Power Query ноком мешавад ва он чизеро, ки мо мехоҳем, кор карда наметавонад. дуруст барои мо. Бо вуҷуди ин, ҳамчун воситаи ёрирасон, он хеле хуб аст. Илова бар ин, бо омӯзиши формулаҳои тавлидкардаи ӯ, шумо метавонед дониши худро дар бораи функсияҳои забони M васеъ кунед, ки дар оянда ҳамеша муфид хоҳад буд.
- Таҳлили матн бо ифодаҳои муқаррарӣ (RegExp) дар Power Query
- Ҷустуҷӯи матни номуайян дар Power Query
- Flash пур дар Microsoft Excel