Mündəricat
Əgər siz artıq Microsoft Excel-də pulsuz Power Query eklentinin vasitələrindən istifadə etməyə başlamışsınızsa, o zaman çox yaxında mənbə məlumatlarına keçidlərin daim pozulması ilə bağlı yüksək ixtisaslaşmış, lakin çox tez-tez və bezdirici problemlə qarşılaşacaqsınız. Problemin mahiyyəti ondan ibarətdir ki, əgər siz sorğunuzda xarici fayl və ya qovluqlara istinad edirsinizsə, Power Query sorğu mətnində onlara gedən mütləq yolu sərt kodlaşdırır. Kompüterinizdə hər şey yaxşı işləyir, ancaq həmkarlarınıza bir sorğu ilə bir fayl göndərmək qərarına gəlsəniz, onlar məyus olacaqlar, çünki. onların kompüterlərində mənbə məlumatlarına fərqli bir yol var və sorğumuz işləməyəcək.
Belə bir vəziyyətdə nə etməli? Aşağıdakı nümunə ilə bu işə daha ətraflı baxaq.
Problemin formalaşdırılması
Fərz edək ki, bizdə qovluq var E:Satış hesabatları faylı yatır Ən yaxşı 100 məhsul.xls, bu, korporativ verilənlər bazamızdan və ya ERP sistemimizdən (1C, SAP və s.) yükləmədir.
Çox güman ki, dərhal aydındır ki, Excel-də onunla bu formada işləmək demək olar ki, qeyri-mümkündür: verilənləri, birləşdirilmiş xanaları, əlavə sütunları, çoxsəviyyəli başlığı və s. olan biri ilə boş sətirlər müdaxilə edəcək.
Buna görə də, eyni qovluqdakı bu faylın yanında başqa bir yeni fayl yaradırıq Handler.xlsx, burada mənbə yükləmə faylından çirkin məlumatları yükləyən Power Query sorğusu yaradacağıq Ən yaxşı 100 məhsul.xls, və onları sıraya qoyun:
Xarici fayla sorğunun edilməsi
Faylın açılması Handler.xlsx, tabda seçin Tarix Komanda Məlumat əldə edin - Fayldan - Excel İş Kitabından (Məlumat - Məlumat əldə etmək - Fayldan - Excel-dən), sonra mənbə faylının yerini və bizə lazım olan vərəqi göstərin. Seçilmiş məlumatlar Power Query redaktoruna yüklənəcək:
Gəlin onları normal vəziyyətə qaytaraq:
- ilə boş sətirləri silin Əsas səhifə — Sətirləri sil — Boş sətirləri silin (Ev - Sətirləri Sil - Boş Sətirləri Sil).
- Lazımsız ilk 4 xətti silin Əsas səhifə — Sətirləri silin — Üst sətirləri silin (Ev - Sətirləri Sil - Üst Sətirləri Sil).
- Düymə ilə ilk sıranı cədvəl başlığına qaldırın Başlıq kimi ilk sətirdən istifadə edin nişanı Əsas səhifə (Ev - Başlıq kimi birinci cərgədən istifadə edin).
- Komandadan istifadə edərək ikinci sütundakı beş rəqəmli məqaləni məhsul adından ayırın bölünən sütun nişanı Transformasiya (Çevr - Ayrılmış Sütun).
- Lazımsız sütunları silin və daha yaxşı görünmək üçün qalanların başlıqlarını dəyişdirin.
Nəticədə, aşağıdakı, daha xoş bir şəkil almalıyıq:
Bu gözəlləşdirilmiş cədvəli faylımızdakı vərəqə yükləmək qalır Handler.xlsx Komanda bağlayın və yükləyin (Ev — Bağla və Yüklə) nişanı Əsas səhifə:
Sorğuda fayla gedən yolun tapılması
İndi gəlin qısaca “M” adı ilə Power Query-ə daxil edilmiş daxili dildə sorğumuzun “başlıq altında” necə göründüyünə baxaq. Bunu etmək üçün, sağ paneldə iki dəfə klikləməklə sorğumuza qayıdın İstəklər və əlaqələr və tabda baxış seçmək Qabaqcıl redaktor (Bax - Qabaqcıl Redaktor):
Açılan pəncərədə ikinci sətir dərhal orijinal yükləmə faylımıza sərt kodlu yolu göstərir. Bu mətn sətirini parametr, dəyişən və ya bu yolun əvvəlcədən yazıldığı Excel vərəq xanasına keçidlə əvəz edə bilsək, sonra onu asanlıqla dəyişə bilərik.
Fayl yolu ilə ağıllı masa əlavə edin
Hələlik Power Query-ni bağlayaq və faylımıza qayıdaq Handler.xlsx. Gəlin yeni boş vərəq əlavə edək və üzərində kiçik bir "ağıllı" cədvəl yaradaq, onun yeganə xanasında mənbə məlumat faylımıza tam yol yazılacaq:
Normal diapazondan ağıllı masa yaratmaq üçün klaviatura qısa yolundan istifadə edə bilərsiniz Ctrl+T və ya düymə Cədvəl kimi formatlayın nişanı Əsas səhifə (Ev - Cədvəl kimi format). Sütun başlığı (A1 xanası) tamamilə hər hansı bir şey ola bilər. Onu da qeyd edim ki, aydınlıq üçün cədvələ ad vermişəm Parameters nişanı konstruktor (Dizayn).
Explorer-dən bir yolu kopyalamaq və ya hətta onu əl ilə daxil etmək, əlbəttə ki, xüsusilə çətin deyil, lakin ən yaxşısı insan amilini minimuma endirmək və mümkünsə, avtomatik olaraq yolu müəyyən etməkdir. Bu standart Excel iş səhifəsi funksiyasından istifadə etməklə həyata keçirilə bilər HÜCRƏ (CELL), arqument kimi göstərilən xana haqqında bir dəstə faydalı məlumat verə bilər – cari faylın yolu da daxil olmaqla:
Mənbə məlumat faylının həmişə Prosessorumuzla eyni qovluqda olduğunu fərz etsək, bizə lazım olan yol aşağıdakı düsturla formalaşa bilər:
=SOL(CELL(“fayl adı”);TAP(“[“;CELL(“fayl adı”))-1)&”Ən yaxşı 100 məhsul.xls”
və ya ingilis dilində:
=SOL(CELL(«fayl adı»);FIND(«[«;CELL(«fayl»))-1)&»Топ-100 товаров.xls»
… funksiya haradadır LEVSIMV (SOL) tam keçiddən açılış kvadrat mötərizəsinə qədər mətn parçası götürür (yəni cari qovluğa gedən yol) və sonra mənbə məlumat faylımızın adı və genişləndirilməsi ona yapışdırılır.
Sorğuda yolu parametrləşdirin
Son və ən vacib toxunuş qalır - sorğuda mənbə faylına gedən yolu yazmaq Ən yaxşı 100 məhsul.xls, yaradılmış “ağıllı” cədvəlimizin A2 xanasına istinad edərək Parameters.
Bunun üçün Power Query sorğusuna qayıdaq və onu yenidən açaq Qabaqcıl redaktor nişanı baxış (Bax - Qabaqcıl Redaktor). Dırnaq içərisində mətn sətir yolu əvəzinə “E:Satış hesabatları Ən yaxşı 100 məhsul.xlsx” Aşağıdakı strukturu təqdim edək:
Excel.CurrentWorkbook(){[Ad="Parametrlər"]}[Məzmun]0 {}[Mənbə məlumatına gedən yol]
Nədən ibarət olduğuna baxaq:
- Excel.CurrentWorkbook() cari faylın məzmununa daxil olmaq üçün M dilinin funksiyasıdır
- {[Ad="Parametrlər"]}[Məzmun] – bu, “ağıllı” cədvəlin məzmununu əldə etmək istədiyimizi göstərən əvvəlki funksiyanın təkmilləşdirmə parametridir. Parameters
- [Mənbə məlumatına gedən yol] cədvəldəki sütunun adıdır Parametersistinad etdiyimiz
- 0 {} cədvəldəki sıra nömrəsidir Parametersondan məlumat almaq istəyirik. Qapaq sayılmır və nömrələmə birdən deyil, sıfırdan başlayır.
Əslində hamısı budur.
Klikləmək qalır finiş və sorğumuzun necə işlədiyini yoxlayın. İndi hər iki faylın olduğu bütün qovluğu başqa bir kompüterə göndərərkən sorğu işlək qalacaq və məlumatlara gedən yolu avtomatik müəyyən edəcək.
- Power Query nədir və Microsoft Excel-də işləyərkən nə üçün lazımdır
- Üzən mətn parçasını Power Query-ə necə idxal etmək olar
- XNUMXD çarpaz cədvəlin Power Query ilə Düz Cədvəl üçün yenidən dizayn edilməsi