İçerik
Microsoft Excel'deki ücretsiz Power Query eklentisinin araçlarını zaten kullanmaya başladıysanız, çok yakında, kaynak verilere olan bağlantıların sürekli kesilmesiyle ilişkili, oldukça uzmanlaşmış, ancak çok sık görülen ve can sıkıcı bir sorunla karşılaşacaksınız. Sorunun özü, sorgunuzda harici dosya veya klasörlere atıfta bulunuyorsanız, Power Query'nin sorgu metninde bunlara giden mutlak yolu sabit kodlamasıdır. Bilgisayarınızda her şey yolunda gidiyor, ancak iş arkadaşlarınıza bir istek içeren bir dosya göndermeye karar verirseniz, hayal kırıklığına uğrayacaklar çünkü. bilgisayarlarındaki kaynak verilere farklı bir yolu var ve sorgumuz çalışmayacak.
Böyle bir durumda ne yapmalı? Bu duruma aşağıdaki örnekle daha ayrıntılı bakalım.
Sorunun formülasyonu
Diyelim ki klasörde var E:Satış raporları dosya yalan En iyi 100 ürün.xls, kurumsal veritabanımızdan veya ERP sistemimizden (1C, SAP, vb.) bir yükleme olan Bu dosya, en popüler emtia kalemleri hakkında bilgiler içerir ve içinde şöyle görünür:
Excel'de bu formda onunla çalışmanın neredeyse imkansız olduğu hemen hemen açıktır: veri içeren boş satırlar, birleştirilmiş hücreler, fazladan sütunlar, çok düzeyli bir başlık, vb. müdahale edecektir.
Bu nedenle aynı klasörde bu dosyanın yanında yeni bir dosya daha oluşturuyoruz. işleyici.xlsxkaynak yükleme dosyasından çirkin verileri yükleyecek bir Power Query sorgusu oluşturacağımız En iyi 100 ürün.xls, ve bunları sıraya koyun:
Harici bir dosyaya istek yapma
Dosyayı açma işleyici.xlsx, sekmede seçin Veri Komuta Veri Al – Dosyadan – Excel Çalışma Kitabından (Veri — Veri Al — Dosyadan — Excel'den), ardından kaynak dosyanın konumunu ve ihtiyacımız olan sayfayı belirtin. Seçilen veriler, Power Query düzenleyicisine yüklenecektir:
Onları normale döndürelim:
- İle boş satırları sil Ana Sayfa — Satırları sil — Boş satırları sil (Ana Sayfa — Satırları Kaldır — Boş Satırları Kaldır).
- Gereksiz ilk 4 satırı sil Ana Sayfa — Satırları Sil — En Üst Satırları Sil (Ana Sayfa — Satırları Kaldır — En Üst Satırları Kaldır).
- Düğmeyle ilk satırı tablo başlığına yükseltin Başlık olarak ilk satırı kullan çıkıntı Ana Sayfa (Ana Sayfa — İlk satırı başlık olarak kullan).
- Komutu kullanarak beş basamaklı makaleyi ikinci sütundaki ürün adından ayırın. bölünmüş sütun çıkıntı Dönüşüm (Dönüştür — Sütunu Böl).
- Gereksiz sütunları silin ve daha iyi görünürlük için kalanların başlıklarını yeniden adlandırın.
Sonuç olarak, aşağıdaki çok daha hoş resmi almalıyız:
Geriye bu yüceltilmiş tabloyu dosyamızdaki sayfaya geri yüklemek kalıyor. işleyici.xlsx takım kapat ve indir (Ana Sayfa — Kapat&Yükle) çıkıntı Ana Sayfa:
Bir istekte bir dosyanın yolunu bulma
Şimdi, Power Query'de yerleşik olarak bulunan ve kısa adı “M” olan dahili dilde sorgumuzun “başlığın altında” nasıl göründüğüne bakalım. Bunu yapmak için sağ bölmede üzerine çift tıklayarak sorgumuza geri dönün. İstekler ve bağlantılar ve sekmede Değerlendirme seçmek Gelişmiş Düzenleyici (Görünüm — Gelişmiş Düzenleyici):
Açılan pencerede, ikinci satır, orijinal yükleme dosyamızın sabit kodlu yolunu hemen gösterir. Bu metin dizesini bir parametre, değişken veya bu yolun önceden yazıldığı bir Excel sayfa hücresine bir bağlantı ile değiştirebilirsek, daha sonra kolayca değiştirebiliriz.
Dosya yolu olan bir akıllı tablo ekleyin
Şimdilik Power Query'yi kapatalım ve dosyamıza dönelim işleyici.xlsx. Yeni bir boş sayfa ekleyelim ve üzerinde kaynak veri dosyamızın tam yolunun yazılacağı tek hücrede küçük bir “akıllı” tablo yapalım:
Normal bir aralıktan akıllı tablo oluşturmak için klavye kısayolunu kullanabilirsiniz. Ctrl+T veya düğmesi Tablo olarak biçimlendir çıkıntı Ana Sayfa (Ana Sayfa — Tablo Olarak Biçimlendir). Sütun başlığı (hücre A1) kesinlikle herhangi bir şey olabilir. Ayrıca netlik için tabloya bir isim verdiğimi de unutmayın. parametreler çıkıntı Inşaatçı (Tasarım).
Explorer'dan bir yolu kopyalamak veya hatta manuel olarak girmek elbette özellikle zor değildir, ancak insan faktörünü en aza indirmek ve mümkünse yolu otomatik olarak belirlemek en iyisidir. Bu, standart Excel çalışma sayfası işlevi kullanılarak uygulanabilir HÜCRE (HÜCRE), geçerli dosyanın yolu da dahil olmak üzere, argüman olarak belirtilen hücre hakkında bir sürü yararlı bilgi verebilir:
Kaynak veri dosyasının her zaman İşlemcimizle aynı klasörde olduğunu varsayarsak, ihtiyacımız olan yol aşağıdaki formülle oluşturulabilir:
=SOL(HÜCRE(“dosya adı”);BUL(“[“;HÜCRE(“dosyaadı”))-1)&”İlk 100 ürün.xls”
veya İngilizce versiyonunda:
=SOL(HÜCRE(«dosyaadi»);BUL(«[«;HÜCRE(«dosyaadi»))-1)&»Топ-100 товаров.xls»
… fonksiyon nerede LEVSİMV (AYRILDI) tam bağlantıdan köşeli parantez açmaya kadar (yani mevcut klasörün yolu) bir metin parçası alır ve ardından kaynak veri dosyamızın adı ve uzantısı ona yapıştırılır.
Sorgudaki yolu parametrelendir
Son ve en önemli dokunuş kalır - istekte kaynak dosyanın yolunu yazmak En iyi 100 ürün.xls, oluşturduğumuz "akıllı" tablomuzun A2 hücresine atıfta bulunarak parametreler.
Bunu yapmak için Power Query sorgusuna geri dönelim ve tekrar açalım Gelişmiş Düzenleyici çıkıntı Değerlendirme (Görünüm — Gelişmiş Düzenleyici). Tırnak içindeki bir metin dizisi yolu yerine "E:Satış raporlarıEn iyi 100 ürün.xlsx" Aşağıdaki yapıyı tanıtalım:
Excel.CurrentWorkbook(){[Ad=”Ayarlar”]}[İçerik]0 {}[Kaynak verilere giden yol]
Nelerden oluştuğunu görelim:
- Excel.CurrentWorkbook() geçerli dosyanın içeriğine erişmek için M dilinin bir işlevidir
- {[Ad=”Ayarlar”]}[İçerik] – bu, "akıllı" tablonun içeriğini almak istediğimizi belirten önceki işlev için bir iyileştirme parametresidir. parametreler
- [Kaynak verilere giden yol] tablodaki sütunun adıdır parametreleratıfta bulunduğumuz
- 0 {} tablodaki satır numarasıdır parametrelerhangi veriyi almak istiyoruz. Üst sınır sayılmaz ve numaralandırma birden değil sıfırdan başlar.
Aslında hepsi bu.
tıklamak kalıyor Bitiş ve isteğimizin nasıl çalıştığını kontrol edin. Şimdi, her iki dosyanın da bulunduğu klasörün tamamını başka bir bilgisayara gönderirken, istek çalışır durumda kalacak ve verilerin yolunu otomatik olarak belirleyecektir.
- Power Query nedir ve Microsoft Excel'de çalışırken neden gereklidir?
- Power Query'ye kayan bir metin parçacığı nasıl aktarılır
- Power Query ile XNUMXD Çapraz Tabloyu Düz Tabloya Yeniden Tasarlama