Мазмұны
Егер сіз 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:
Сұраныстағы файлдың жолын табу
Енді қысқаша «M» атауы бар Power Query ішіне енгізілген ішкі тілде сұрауымыздың «қапшық астында» қалай көрінетінін көрейік. Мұны істеу үшін оң жақ тақтада оны екі рет басу арқылы сұрауымызға оралыңыз Сұраныстар мен байланыстар және қойындыда шолу таңдау Жетілдірілген редактор (Қарау — Кеңейтілген редактор):
Ашылған терезеде екінші жол біздің бастапқы жүктеп салу файлымызға қатаң кодталған жолды бірден көрсетеді. Бұл мәтін жолын параметрмен, айнымалымен немесе осы жол алдын ала жазылған Excel парағының ұяшығына сілтемемен ауыстыра алатын болсақ, оны кейін оңай өзгерте аламыз.
Файл жолы бар смарт кестені қосыңыз
Әзірге Power Query-ді жауып, файлымызға оралайық Handler.xlsx. Жаңа бос парақты қосып, оған шағын «ақылды» кестені жасайық, оның жалғыз ұяшығында бастапқы деректер файлына толық жол жазылады:
Кәдімгі ауқымнан смарт кесте жасау үшін пернелер тіркесімін пайдалануға болады Ctrl+T немесе батырма Кесте ретінде пішімдеу қойындысы Home (Үй — Кесте ретінде пішімдеу). Баған тақырыбы (A1 ұяшығы) кез келген нәрсе болуы мүмкін. Түсінікті болу үшін кестеге атау бергенімді де ескеріңіз Параметрлер қойындысы Конструктор (Дизайн).
Explorer-ден жолды көшіру немесе тіпті оны қолмен енгізу, әрине, өте қиын емес, бірақ адам факторын барынша азайтып, жолды мүмкіндігінше автоматты түрде анықтаған дұрыс. Мұны стандартты Excel жұмыс парағы функциясы арқылы жүзеге асыруға болады ҰЯША (ҰЯШЫҚ), ол дәлел ретінде көрсетілген ұяшық туралы пайдалы ақпарат топтамасын, соның ішінде ағымдағы файлға жолды бере алады:
Егер бастапқы деректер файлы әрқашан біздің процессорымызбен бір қалтада болады деп болжасақ, онда бізге қажет жолды келесі формула арқылы құруға болады:
=СОЛ(ҰЯШЫҚ(“файл аты”);ТАБУ(“[“;ҰЯШЫҚ(“файл аты”))-1)&”Үздік 100 өнім.xls”
немесе ағылшын нұсқасында:
=СОЛ(ҰЯШЫҚ(«файл аты»);ТАБУ(«[«;ҰЯШЫҚ(«файл аты»))-1)&»Топ-100 товаров.xls»
… функция қайда LEVSIMV (СОЛ) толық сілтемеден ашылатын шаршы жақшаға дейін мәтіннің бір бөлігін алады (яғни ағымдағы қалтаға жол), содан кейін біздің бастапқы деректер файлымыздың атауы мен кеңейтімі оған жабыстырылады.
Сұраудағы жолды параметрлендіріңіз
Соңғы және ең маңызды түрту қалады – сұраудағы бастапқы файлға жолды жазу Үздік 100 өнім.xls, біздің құрылған «ақылды» кестенің A2 ұяшығына сілтеме жасай отырып Параметрлер.
Мұны істеу үшін Power Query сұрауына оралып, оны қайта ашайық Жетілдірілген редактор қойындысы шолу (Қарау — Кеңейтілген редактор). Тырнақшадағы мәтін жолының орнына «E:Сату туралы есептер Топ 100 өнім.xlsx» Келесі құрылымды енгізейік:
Excel.CurrentWorkbook(){[Аты="Параметрлер"]}[Мазмұн]0 {}[Дереккөзге жол]
Оның неден тұратынын көрейік:
- Excel.CurrentWorkbook() ағымдағы файлдың мазмұнына қол жеткізу үшін M тілінің функциясы болып табылады
- {[Аты="Параметрлер"]}[Мазмұн] – бұл алдыңғы функцияның нақтылау параметрі, ол «ақылды» кестенің мазмұнын алғымыз келетінін көрсетеді Параметрлер
- [Дереккөзге жол] кестедегі бағанның аты болып табылады Параметрлербіз сілтеме жасаймыз
- 0 {} кестедегі жол нөмірі болып табылады Параметрлербіз деректерді алғымыз келеді. Қақпақ есептелмейді және нөмірлеу бірден емес, нөлден басталады.
Мұның бәрі, шын мәнінде.
Оны басу қалды Аяқтау және сұрауымыздың қалай жұмыс істейтінін тексеріңіз. Енді екі файлы бар бүкіл қалтаны басқа компьютерге жіберген кезде сұрау жұмыс істейтін болып қалады және деректерге жолды автоматты түрде анықтайды.
- Power Query дегеніміз не және ол Microsoft Excel бағдарламасында жұмыс істегенде не үшін қажет
- Power Query қызметіне өзгермелі мәтін үзіндісін импорттау жолы
- XNUMXD айқас кестені Power Query көмегімен тегіс кестеге қайта жасау