Мундариҷа
Тартиб додани мушкилот
Биёед ҳалли зебоеро барои яке аз ҳолатҳои хеле стандартие, ки аксар корбарони Excel дер ё зуд дучор меоянд, бубинем: ба шумо лозим аст, ки зуд ва ба таври худкор маълумотро аз шумораи зиёди файлҳо дар як ҷадвали ниҳоӣ ҷамъ кунед.
Фарз мекунем, ки мо папкаи зерин дорем, ки дар он якчанд файлҳо бо маълумот аз шаҳрҳои филиалҳо мавҷуданд:
Шумораи файлҳо аҳамият надорад ва метавонад дар оянда тағир ёбад. Ҳар як файл дорои варақи ном дорад SalesДар куҷо ҷадвали маълумот ҷойгир аст:
Миқдори сатрҳо (фармоишҳо) дар ҷадвалҳо, албатта, гуногун аст, аммо маҷмӯи сутунҳо дар ҳама ҷо стандартӣ мебошанд.
Вазифа: ҷамъ овардани маълумот аз ҳама файлҳо дар як китоб бо навсозии минбаъдаи автоматӣ ҳангоми илова кардан ё нест кардани файлҳои шаҳрӣ ё сатрҳо дар ҷадвалҳо. Мувофиқи ҷадвали ҷамъбастии ҷамъбастӣ, он гоҳ имкон медиҳад, ки ҳама гуна ҳисоботҳо, ҷадвалҳои ҷамъоварӣ, маълумотҳои филтрӣ ва ғайра сохта шаванд. Чизи асосӣ ин аст, ки ҷамъоварӣ карда шавад.
Мо силоҳро интихоб мекунем
Барои ҳалли мо ба версияи охирини Excel 2016 (функсияи зарурӣ аллакай дар он бо нобаёнӣ сохта шудааст) ё версияҳои қаблии Excel 2010-2013 бо иловаи ройгони насбшуда лозим аст. Дархости барқ аз Microsoft (онро дар ин ҷо зеркашӣ кунед). Power Query як абзори хеле фасеҳ ва пурқудрат барои бор кардани маълумот ба Excel аз ҷаҳони беруна ва сипас канда ва коркарди он мебошад. Power Query тақрибан ҳамаи манбаъҳои мавҷудаи маълумотро дастгирӣ мекунад - аз файлҳои матнӣ то SQL ва ҳатто Facebook 🙂
Агар шумо Excel 2013 ё 2016 надошта бошед, пас шумо наметавонед минбаъд хонед (шӯхӣ кунед). Дар версияҳои кӯҳнаи Excel, чунин вазифаро танҳо тавассути барномарезии макрос дар Visual Basic (ин барои шурӯъкунандагон хеле душвор аст) ё нусхабардории якрангии дастӣ (ки вақти зиёдро талаб мекунад ва хатогиҳоро ба вуҷуд меорад) иҷро кардан мумкин аст.
Қадами 1. Як файлро ҳамчун намуна ворид кунед
Аввалан, биёед маълумотро аз як китоби корӣ ҳамчун намуна ворид кунем, то Excel ин идеяро қабул кунад. Барои ин, китоби нави холии корӣ эҷод кунед ва…
- агар шумо Excel 2016 дошта бошед, пас ҷадвалро кушоед Маълумот ва баъдан Эҷоди дархост - Аз файл - Аз китоб (Маълумот - Дархости нав - Аз файл - Аз Excel)
- агар шумо Excel 2010-2013 бо замимаи Power Query насб карда бошед, пас ҷадвалро кушоед Дархости барқ ва дар он интихоб кунед Аз файл - Аз китоб (Аз файл — Аз Excel)
Пас, дар равзанаи кушодашуда, ба ҷузвдони мо бо гузоришҳо равед ва яке аз файлҳои шаҳрро интихоб кунед (фарқ надорад, ки кадоме аз онҳо, зеро ҳама маъмуланд). Пас аз чанд сония, равзанаи Навигатор пайдо мешавад, ки дар он шумо бояд варақеро, ки ба мо лозим аст (Фурӯш) дар тарафи чап интихоб кунед ва мундариҷаи он дар тарафи рост нишон дода мешавад:
Агар шумо тугмаи дар кунҷи рости поёни ин тирезаро пахш кунед Download (бор кардан), он гоҳ ҷадвал фавран ба варақ дар шакли аслии худ ворид карда мешавад. Барои як файл, ин хуб аст, аммо мо бояд бисёр чунин файлҳоро бор кунем, аз ин рӯ, мо каме фарқ мекунем ва тугмаро пахш мекунем Тоза кардан (Таҳрир). Пас аз он, муҳаррири дархости Power Query бояд дар равзанаи алоҳида бо маълумоти мо аз китоб намоиш дода шавад:
Ин як асбоби хеле пурқувватест, ки ба шумо имкон медиҳад, ки ҷадвалро ба намуди зоҳирии ба мо лозима «анҷом диҳед». Ҳатто тавсифи рӯякии ҳамаи вазифаҳои он тақрибан сад саҳифаро дар бар мегирад, аммо агар хеле мухтасар бошад, бо истифода аз ин тиреза шумо метавонед:
- филтр кардани маълумоти нолозим, сатрҳои холӣ, сатрҳои бо хатогиҳо
- маълумотро аз рӯи як ё якчанд сутун ҷудо кунед
- аз такрор даст кашед
- матни часпакро ба сутунҳо тақсим кунед (аз рӯи ҷудокунандаҳо, шумораи аломатҳо ва ғайра)
- матнро ба тартиб гузоред (тоза кардани фосилаҳои иловагӣ, ҳарфҳои дуруст ва ғ.)
- намудҳои маълумотро бо ҳар роҳ табдил диҳед (рақамҳоро ба монанди матн ба рақамҳои муқаррарӣ табдил диҳед ва баръакс)
- љадвалњоро људо кунед (гиред) ва љадвалњои ду-ченакаро ба љадвалњои њамвор васеъ кунед
- ба ҷадвал сутунҳои иловагӣ илова кунед ва формулаҳо ва функсияҳоро дар онҳо бо истифода аз забони M, ки дар Power Query сохта шудааст, истифода баред.
- ...
Масалан, биёед сутунеро бо номи матни моҳ ба ҷадвали худ илова кунем, то баъдтар сохтани гузоришҳои ҷадвали ҷамъоварӣ осонтар шавад. Барои ин кор, тугмаи рости мушро дар сарлавҳаи сутун пахш кунед таърихва фармонро интихоб кунед Сутуни такрорӣ (Сутуни такрорӣ), ва он гоҳ болои сарлавҳаи сутуни такрорӣ, ки пайдо мешавад, тугмаи ростро клик кунед ва Фармонҳо -ро интихоб кунед Табдил додан - Номи моҳ - моҳ:
Сутуни нав бояд бо номҳои матни моҳ барои ҳар як сатр ташкил карда шавад. Бо ду маротиба пахш кардани сарлавҳаи сутун, шумо метавонед онро аз он иваз кунед Санаи нусхабардорӣ ба бароҳаттар моҳ, масалан.
Агар дар баъзе сутунҳо барнома навъи маълумотро дуруст эътироф накунад, шумо метавонед ба он тавассути клик кардани тасвири формат дар тарафи чапи ҳар як сутун кӯмак кунед:
Шумо метавонед бо истифода аз филтри оддӣ хатҳои дорои хато ё сатрҳои холӣ, инчунин менеҷерҳо ё муштариёни нолозимро истисно кунед:
Ғайр аз он, ҳама тағиротҳои анҷомдодашуда дар панели рост ҷойгир карда мешаванд, ки дар он ҷо онҳо ҳамеша метавонанд баргардонида шаванд (убур) ё параметрҳои худро иваз кунанд (фишанги):
Сабук ва шево, ҳамин тавр не?
Қадами 2. Биёед дархости худро ба функсия табдил диҳем
Барои минбаъд такрор кардани ҳама тағироти додаҳои барои ҳар як китоби воридшуда, мо бояд дархости офаридаамонро ба функсия табдил диҳем, ки он дар навбати худ ба ҳамаи файлҳои мо татбиқ карда мешавад. Барои ин кор дар асл хеле содда аст.
Дар Муҳаррири дархост, ба ҷадвали Намоиш гузаред ва тугмаро пахш кунед Муҳаррири пешрафта (Намоиш — Муҳаррири пешрафта). Тиреза бояд кушода шавад, ки дар он ҳамаи амалҳои қаблии мо дар шакли код бо забони M навишта мешаванд. Лутфан таваҷҷӯҳ намоед, ки роҳ ба файле, ки мо барои мисол ворид кардем, дар код сахт рамзгузорӣ шудааст:
Акнун биёед якчанд ислоҳот ворид кунем:
Маънои онҳо оддӣ аст: сатри аввал (Пайти файл) => тартиби моро ба функсия бо аргумент табдил медиҳад роҳи файл, ва дар зер мо роҳи собитро ба арзиши ин тағирёбанда иваз мекунем.
Ҳама. -ро пахш кунед тамом кардан ва бояд инро бубинад:
Натарсед, ки маълумот нопадид шудааст - дар асл, ҳама чиз хуб аст, ҳама чиз бояд чунин бошад 🙂 Мо вазифаи фармоишии худро бомуваффақият эҷод кардем, ки дар он тамоми алгоритми воридот ва коркарди маълумот бидуни пайвастшавӣ ба файли мушаххас дар хотир нигоҳ дошта мешавад. . Ба он номи фаҳмотар додан боқӣ мемонад (масалан гирифтани маълумот) дар панели рост дар майдон ном ва шумо метавонед дарав кунед Хона — Пӯшед ва зеркашӣ кунед (Хона — Пӯшед ва бор кунед). Лутфан таваҷҷӯҳ намоед, ки роҳ ба файле, ки мо барои мисол ворид кардем, дар код сахт рамзгузорӣ шудааст. Шумо ба равзанаи асосии Microsoft Excel бармегардед, аммо дар тарафи рост панел бо пайвасти ба функсияи мо сохташуда бояд пайдо шавад:
Қадами 3. Ҷамъоварии ҳамаи файлҳо
Ҳама қисми душвортарин паси сар аст, қисми гуворо ва осон боқӣ мемонад. Ба ҷадвал равед Маълумот - Эҷоди дархост - Аз файл - Аз ҷузвдон (Маълумот - Дархости нав - Аз файл - Аз ҷузвдон) ё, агар шумо Excel 2010-2013 дошта бошед, ба ин ҷадвал Дархости барқ. Дар равзанаи пайдошуда папкаеро, ки дар он ҳамаи файлҳои шаҳрии сарчашмаи мо ҷойгиранд, муайян кунед ва клик кунед OK. Қадами навбатӣ бояд равзанаро кушояд, ки дар он ҳамаи файлҳои Excel дар ин ҷузвдон (ва зерпапкаҳои он) ва тафсилоти ҳар яки онҳо номбар карда мешаванд:
ангушт занед иваз (Таҳрир) ва боз мо ба равзанаи муҳаррири дархости шинос ворид мешавем.
Акнун мо бояд ба ҷадвали худ як сутуни дигарро бо функсияи сохтаамон илова кунем, ки маълумотро аз ҳар як файл "кашад". Барои ин, ба ҷадвал равед Иловаи Сутун - Сутуни фармоишӣ (Иловаи сутун - Иловаи сутуни фармоишӣ) ва дар равзанаи пайдошуда функсияи моро ворид кунед гирифтани маълумот, барои он ҳамчун далел роҳи пурраи ҳар як файлро муайян кунед:
Баъд аз пахш кардан OK сутуни сохташуда бояд ба ҷадвали мо дар тарафи рост илова карда шавад.
Акнун биёед ҳамаи сутунҳои нолозимро нест кунем (ба монанди дар Excel бо истифода аз тугмаи рости муш - хориҷ), танҳо сутуни иловашуда ва сутун бо номи файл боқӣ мемонад, зеро ин ном (аниқтараш, шаҳр) дар маълумоти умумӣ барои ҳар як сатр муфид хоҳад буд.
Ва ҳоло "лаҳзаи вой" - нишонаро бо тирчаҳои худ дар кунҷи рости болои сутуни иловашуда бо функсияи мо клик кунед:
… қайдро хориҷ кунед Номи аслии сутунро ҳамчун префикс истифода баред (Номи аслии сутунро ҳамчун префикс истифода баред)ва пахш кунед OK. Ва функсияи мо маълумотро аз ҳар як файл, аз рӯи алгоритми сабтшуда бор мекунад ва коркард мекунад ва ҳама чизро дар ҷадвали умумӣ ҷамъ мекунад:
Барои зебоии комил, шумо инчунин метавонед васеъшавии .xlsx-ро аз сутуни аввал бо номҳои файл хориҷ кунед - бо ивази стандартӣ бо "ҳеҷ чиз" (ро пахш кардани тугмаи рости сарлавҳаи сутун - Тағирдиҳӣ) ва номи ин сутунро бо ҳиссиёт. Ва инчунин формати маълумотро дар сутун бо сана ислоҳ кунед.
Ҳама! -ро пахш кунед Хона - Пӯшед ва бор кунед (Хона — Пӯшед ва бор кунед). Ҳама маълумоте, ки тавассути пурсиш барои ҳамаи шаҳрҳо ҷамъоварӣ шудааст, ба варақи кунунии Excel дар формати "мизи оқил" бор карда мешавад:
Пайвасти сохташуда ва функсияи васлкунии мо ба ҳеҷ ваҷҳ алоҳида захира кардан лозим нест - онҳо якҷоя бо файли ҷорӣ ба таври муқаррарӣ захира карда мешаванд.
Дар оянда, бо ҳама гуна тағирот дар ҷузвдон (илова кардан ё хориҷ кардани шаҳрҳо) ё файлҳо (тағйир додани шумораи сатрҳо), тугмаи рости мушро пахш кардани ҷадвал ё дархост дар панели рост ва интихоб кардани фармон Навсозӣ ва Захира (навсозӣ) - Power Query тамоми маълумотро дар тӯли чанд сония дубора "аз нав месозад".
PS
Ислоҳот. Пас аз навсозиҳои январи соли 2017, Power Query худ аз худ чӣ гуна ҷамъ кардани китобҳои кории Excel-ро омӯхт, яъне дигар сохтани функсияи алоҳида лозим нест - он ба таври худкор сурат мегирад. Ҳамин тариқ, қадами дуюми ин мақола дигар лозим нест ва тамоми раванд ба таври назаррас соддатар мешавад:
- интихоб кардан Эҷоди дархост - Аз файл - Аз ҷузвдон - Папкаро интихоб кунед - Хуб
- Пас аз пайдо шудани рӯйхати файлҳо, пахш кунед иваз
- Дар равзанаи Муҳаррири дархост, сутуни Бинариро бо тирчаи дугона васеъ кунед ва номи варақеро, ки аз ҳар як файл гирифта мешавад, интихоб кунед.
Ва ин ҳама! Суруд!
- Таҷдиди ҷадвали кросс ба ҳамворе, ки барои сохтани ҷадвалҳои даврӣ мувофиқ аст
- Сохтани диаграммаи аниматсионии ҳубобӣ дар Power View
- Макрос барои ҷамъ кардани варақҳо аз файлҳои гуногуни Excel ба як