мазмуну
Эгер сиз Microsoft Excelдеги бекер Power Query кошумчасынын куралдарын мурунтан эле колдоно баштаган болсоңуз, анда жакында сиз булак маалыматтарына шилтемелерди үзгүлтүксүз бузуу менен байланышкан жогорку адистештирилген, бирок өтө тез-тез жана тажатма көйгөйгө туш болосуз. Көйгөйдүн маңызы, эгерде сиз сурооңузда тышкы файлдарга же папкаларга кайрылсаңыз, анда Power Query суроонун текстинде аларга абсолюттук жолду катуу коддойт. Сиздин компьютериңизде баары жакшы иштейт, бирок сиз кесиптештериңизге өтүнүч менен файлды жөнөтүүнү чечсеңиз, анда алар капа болушат, анткени. алардын компьютеринде булак маалыматтарына башка жол бар жана биздин сурообуз иштебейт.
Мындай кырдаалда эмне кылуу керек? Бул ишти төмөнкү мисал менен кененирээк карап көрөлү.
Проблеманы түзүү
Бизде папка бар дейли E:Сатуу отчеттору файл жатат Мыкты 100 продукт.xls, бул биздин корпоративдик маалымат базабыздан же ERP тутумубуздан (1C, SAP ж.б.) жүктөлгөн жүктөө. Бул файл эң популярдуу товарлар жөнүндө маалыматты камтыйт жана ичинде мындай көрүнөт:
Excelде аны менен бул формада иштөө дээрлик мүмкүн эмес экени түшүнүктүү болсо керек: маалыматтары бар бир саптар, бириктирилген уячалар, кошумча мамычалар, көп деңгээлдүү аталыштар жана башкалар тоскоолдук кылат.
Ошондуктан, ошол эле папкадагы бул файлдын жанында биз дагы бир жаңы файлды түзөбүз Handler.xlsx, анда биз булак жүктөө файлынан жагымсыз маалыматтарды жүктөй турган Power Query сурамын түзөбүз Мыкты 100 продукт.xls, жана аларды иретке келтириңиз:
Сырткы файлга суроо-талап жасоо
Файлды ачуу Handler.xlsx, өтмөктөн тандаңыз маалыматтар буйрук Маалыматтарды алуу - Файлдан - Excel иш китебинен (Маалымат - Маалымат алуу - Файлдан - Excelден), андан кийин баштапкы файлдын жайгашкан жерин жана бизге керектүү баракты көрсөтүңүз. Тандалган маалыматтар Power Query редакторуна жүктөлөт:
Келгиле, аларды нормалдуу абалга келтирели:
- менен бош саптарды жок кылуу Негизги бет — Саптарды жок кылуу — Бош саптарды жок кылуу (Башкы бет — Саптарды алып салуу — Бош саптарды алып салуу).
- Керексиз жогорку 4 сапты жок кылыңыз Башкы бет — Саптарды жок кылуу — Жогорку саптарды жок кылуу (Башкы бет — Саптарды алып салуу — Жогорку саптарды алып салуу).
- баскычы менен биринчи катарды столдун башына көтөрүңүз Баш аты катары биринчи сапты колдонуңуз табулатура Home (Башкы бет — Биринчи катарды баш катары колдонуу).
- Буйрук аркылуу экинчи тилкедеги беш орундуу макаланы товардын аталышынан бөлүңүз тилкени бөлүү табулатура кайра (Трансформация — Бөлүнгөн тилке).
- Керексиз тилкелерди жок кылып, жакшыраак көрүнүү үчүн калгандарынын аталыштарын өзгөртүңүз.
Натыйжада, биз төмөнкү, алда канча жагымдуу сүрөттү алуу керек:
Бул өркүндөтүлгөн таблицаны кайра биздин файлдагы баракка жүктөө калды Handler.xlsx команда жабуу жана жүктөп алуу (Үй — Жабуу&Жүктөө) табулатура Home:
Сурамдагы файлга жол табуу
Эми биздин сурамыбыз Power Query ичинде кыскача "M" аталышы менен орнотулган ички тилде "капчыктын астында" кандай көрүнөрүн карап көрөлү. Бул үчүн, оң панелдеги аны эки жолу чыкылдатуу менен биздин сурообузга кайтыңыз Сурамдар жана байланыштар жана өтмөктө кароо тандап алуу Өркүндөтүлгөн редактор (Көрүү — Өркүндөтүлгөн редактор):
Ачылган терезеде, экинчи сап дароо биздин баштапкы жүктөө файлыбызга катуу коддолгон жолду ачып берет. Эгерде биз бул текст сапты параметр, өзгөрмө же бул жол алдын ала жазылган Excel барагынын уячасына шилтеме менен алмаштыра алсак, анда биз аны кийинчерээк оңой эле өзгөртө алабыз.
Файл жолу менен акылдуу үстөлдү кошуңуз
Азырынча Power Query'ди жаап, файлыбызга кайрылып көрөлү Handler.xlsx. Келгиле, жаңы бош баракты кошуп, ага кичинекей "акылдуу" таблица түзөлү, анын жалгыз уячасында биздин баштапкы маалымат файлына толук жол жазылат:
Кадимки диапазондон акылдуу үстөл түзүү үчүн, баскычтоптун жарлыгын колдоно аласыз Ctrl+T же баскыч Таблица катары форматтоо табулатура Home (Башкы бет — Таблица катары формат). Мамычанын аталышы (A1 уячасы) эч нерсе болушу мүмкүн. Тактык үчүн мен таблицага ат бердим параметрлер табулатура Конструктор (Дизайн).
Explorerден жолду көчүрүү же аны кол менен киргизүү, албетте, өзгөчө кыйын эмес, бирок адам факторун минималдаштыруу жана жолду, мүмкүн болсо, автоматтык түрдө аныктоо эң жакшы. Бул стандарттуу Excel иш барагынын функциясын колдонуу менен ишке ашырылышы мүмкүн CELL (уяча), ал аргумент катары көрсөтүлгөн уяча жөнүндө бир топ пайдалуу маалыматты бере алат, анын ичинде учурдагы файлга жол:
Эгерде баштапкы маалымат файлы ар дайым биздин Процессорубуз менен бир папкада болот деп ойлосок, анда бизге керектүү жол төмөнкү формула менен түзүлүшү мүмкүн:
=СОЛ(CELL("файлдын аты");ФИНД("[“;CELL(“файлдын аты”))-1)&”Мыкты 100 продукт.xls”
же англис версиясында:
=СОЛ(CELL(«файлдын аты»);ФИНД(«[«;CELL(«файлдын аты»))-1)&»Топ-100 товаров.xls»
… функция кайда LEVSIMV (СОЛ) толук шилтемеден ачылуучу чарчы кашаага чейин тексттин бир бөлүгүн алат (б.а. учурдагы папкага баруучу жол), андан кийин биздин баштапкы маалымат файлыбыздын аты жана кеңейтүүсү ага чапталат.
Суроодо жолду параметрлештириңиз
Акыркы жана эң маанилүү тийүү бойдон калууда - суроо-талаптагы булак файлына жолду жазуу Мыкты 100 продукт.xls, биздин түзүлгөн "акылдуу" таблицабыздын A2 уячасына шилтеме кылуу параметрлер.
Бул үчүн, келгиле, Power Query сурамына кайрылып, аны кайра ачалы Өркүндөтүлгөн редактор табулатура кароо (Көрүү — Өркүндөтүлгөн редактор). Тырмакчадагы текст сап-жолунун ордуна "E: Сатуу отчеттору. Top 100 products.xlsx" Төмөнкү структураны киргизели:
Excel.CurrentWorkbook(){[Аты="Жөндөөлөр"]}[Мазмун]0 {}[Булак дайындарына жол]
Келгиле, анын эмнеден турганын карап көрөлү:
- Excel.CurrentWorkbook() учурдагы файлдын мазмунуна жетүү үчүн M тилинин функциясы болуп саналат
- {[Аты="Жөндөөлөр"]}[Мазмун] – бул мурунку функциянын тактоо параметри, бул биз “акылдуу” таблицанын мазмунун алууну каалайбыз. параметрлер
- [Булак дайындарына жол] таблицадагы мамычанын аты болуп саналат параметрлербиз кайрылабыз
- 0 {} таблицадагы катардын номери параметрлерандан биз маалыматтарды алгыбыз келет. Калпак эсептелбейт жана номерлөө бирден эмес, нөлдөн башталат.
Чындыгында баары ушунда.
Бул басуу үчүн калды бүтүрүү жана биздин суроо-талапыбыз кантип иштээрин текшериңиз. Эми, ичиндеги эки файлы бар папканы толугу менен башка компьютерге жөнөткөндө, суроо-талап иштеп калат жана автоматтык түрдө берилиштерге жолду аныктайт.
- Power Query деген эмне жана ал Microsoft Excelде иштөөдө эмне үчүн керек
- Power Queryге калкыма текст үзүндүсүн кантип импорттоо керек
- Power Query менен жалпак столго XNUMXD кайчылаш таблицаны кайра долбоорлоо