Ngaganti téks bulk dina Power Query kalawan List.Accumulate fungsi

Kumaha gancang sareng seueur ngagentos téks dumasar kana daptar rujukan sareng rumus - kami parantos nyortir. Ayeuna hayu urang cobian ngalakukeunana dina Power Query.

Sakumaha sering kajadian ngalaksanakeun tugas ieu loba gampang ti ngajelaskeun kunaon gawéna, tapi hayu urang coba mun ngalakukeun duanana 🙂

Janten, urang gaduh dua tabel dinamis "pinter" anu didamel tina rentang biasa sareng potong kompas keyboard Ctrl+T atawa tim Imah - Format salaku méja (Imah - Format salaku Méja):

Kuring disebut tabel munggaran data, tabél kadua - pitunjuknganggo widang Ngaran méja (Ngaran méja) tab Constructor (Desain).

Tugas: ngaganti alamat dina tabél data kabéh kajadian ti kolom Pikeun mendakan Buku Panduan ka counterparts bener pakait maranéhanana ti kolom ngagentos. Sésana téks dina sél kedah tetep teu kacekel.

Lengkah 1. Muat diréktori kana Power Query jeung ngahurungkeun kana daptar

Saanggeus nyetél sél aktip ka sagala tempat dina tabel rujukan, klik dina tab data (Kaping)atawa dina tab Patarosan Kakuatan (upami anjeun gaduh vérsi Excel anu lami sareng anjeun pasang Power Query salaku tambihan dina tab anu misah) dina tombol Tina méja / rentang (Tina Méja/Rasaran).

Tabél rujukan bakal dimuat kana pangropéa pamundut Power Query:

Dina raraga teu ngaganggu, hiji hambalan otomatis ditambahkeun tipe dirobah (Tipe Robah) dina panel katuhu, léngkah anu diterapkeun tiasa dihapus sacara aman, ngan ukur nyéépkeun léngkah sumber (Sumber):

Ayeuna, pikeun ngalakukeun transformasi salajengna jeung ngagantian, urang kudu ngahurungkeun tabel ieu kana daptar (daptar).

Dismis liris

• meja mangrupa susunan dua diménsi anu diwangun ku sababaraha baris jeung kolom.
• Rékam (Rekam) – Asép Sunandar Sunarya hiji-diménsi, diwangun ku sababaraha widang-unsur jeung ngaran, contona [Ngaran = "Masha", Gender = "f", Umur = 25]
  
• daptar - hiji-diménsi Asép Sunandar Sunarya-kolom, diwangun ku sababaraha elemen, contona {1, 2, 3, 10, 42} or {"Iman Harepan Cinta" }
  

Pikeun ngajawab masalah urang, urang bakal utamana museurkeun jenis daptar.

Trik di dieu nyaéta yén daptar item dina Power Query tiasa henteu ngan ukur nomer banal atanapi téks, tapi ogé daptar atanapi rékaman sanés. Aya dina daptar (daptar) anu rumit, anu diwangun ku rékaman (rékaman) yén urang kedah ngaktipkeun diréktori urang. Dina notasi sintaksis Power Query (éntri dina kurung kuadrat, daptar dina kurung keriting) ieu bakal siga:

{

    [ Manggihan = "St. Petersburg", Ganti = "St. Petersburg" ] ,

    [ Manggihan = "St. Petersburg", Ganti = "St. Petersburg" ] ,

    [Panggihan = "Peter", Ganti = "St. Petersburg" ] ,

jsb

}

Transformasi sapertos kitu dilakukeun nganggo fungsi khusus tina basa M anu diwangun kana Power Query - Table.ToRecords. Pikeun nerapkeun éta langsung dina bar rumus, tambahkeun fungsi ieu kana kode léngkah anu aya sumber.

Tadina:

saatos:

Saatos nambahkeun fungsi Table.ToRecords, penampilan tabel urang bakal robah - eta bakal robah jadi daptar rékaman. Eusi rékaman individu tiasa ditingali di handapeun jandela panempoan ku ngaklik dina latar sélulér gigireun kecap naon waé. catetan (tapi henteu dina hiji kecap!)

Salian di luhur, asup akal pikeun nambahkeun hiji stroke deui - ka cache (panyangga) daptar dijieun kami. Ieu bakal maksakeun Power Query pikeun ngamuat daptar panéangan urang sakali kana mémori sareng henteu ngitung deui nalika urang engké ngaksés éta pikeun ngagentos éta. Jang ngalampahkeun ieu, bungkus rumus kami dina fungsi anu sanés - Daptar. Panyangga:

cache sapertos bakal masihan kanaékan pisan noticeable dina speed (ku sababaraha kali!) Jeung jumlah badag data awal bakal diberesihan.

Ieu ngalengkepan persiapan Buku Panduan.

Tetep klik dina Imah - Tutup sareng Beban - Tutup sareng Beban ka… (Imah — Tutup&Muat — Tutup&Muat ka..), pilih hiji pilihan Ngan nyieun sambungan (Ngan nyieun sambungan) sarta balik deui ka Excel.

Lengkah 2. Loading tabel data

Sagalana geus trite dieu. Sapertos sateuacan buku rujukan, urang angkat ka tempat mana waé dina méja, klik dina tab data kancing Ti Table / Rentang jeung méja urang data asup kana Power Query. Lengkah otomatis ditambahkeun tipe dirobah (Tipe Robah) anjeun ogé tiasa ngahapus:

Henteu aya tindakan persiapan khusus anu kedah dilakukeun sareng éta, sareng urang teraskeun kana hal anu paling penting.

Lengkah 3. Ngalaksanakeun ngagantian ngagunakeun fungsi List.Accumulate

Hayu urang tambahkeun kolom diitung kana tabel data urang nganggo paréntah Nambahkeun Kolom - Kolom Adat (Tambihkeun kolom - Kolom khusus): sareng lebetkeun nami kolom tambihan dina jandela anu muka (contona, alamat dilereskeun) jeung fungsi magic urang Daptar.Akumulasi:

Tetep klik dina OK - sareng kami nampi kolom kalayan panggantian anu dilakukeun:

Catet éta:

• Kusabab Power Query mangrupikeun sénsitip, henteu aya gaganti dina garis penultimate, sabab dina diréktori kami ngagaduhan "SPb", sanés "SPb".
• Lamun aya sababaraha substrings pikeun ngaganti sakaligus dina data sumber (contona, dina garis ka-7 anjeun kudu ngaganti duanana "S-Pb" jeung "Prospectus"), lajeng ieu teu nyieun masalah nanaon (teu kawas ngaganti rumus ti métode saméméhna).
• Upami teu aya anu ngagentos dina téks sumber (baris 9), teras teu aya kasalahan (teu sapertos, deui, tina ngagantian ku rumus).

Laju pamundut sapertos kitu pisan, santun pisan. Contona, pikeun tabel data awal kalayan ukuran 5000 baris, query ieu diropéa dina waktu kurang ti sadetik (tanpa buffering, ku jalan, ngeunaan 3 detik!)

Kumaha fungsi List.Accumulate jalan

Sacara prinsip, ieu bisa jadi tungtung (keur kuring nulis, sarta pikeun anjeun maca) artikel ieu. Lamun hayang teu ngan bisa, tapi ogé ngarti kumaha gawéna "dina tiung", mangka anjeun kudu teuleum saeutik deeper kana liang kelenci jeung nungkulan List.Accumulate fungsi, nu tuh sakabeh ngagantian bulk. dianggo pikeun urang.

Sintaksis pikeun fungsi ieu nyaéta:

=Daptar.Akumulasi(daptar, siki, akumulator)

di mana

• daptar mangrupa daptar anu elemen kami iterating leuwih. 
• siki - kaayaan awal
• akumulator - fungsi nu ngalakukeun sababaraha operasi (matematika, téks, jsb) dina unsur hareup daptar sarta accumulates hasil processing dina variabel husus.
  

Sacara umum, sintaksis pikeun nuliskeun fungsi dina Power Query siga kieu:

(argument1, argument2, ... argumentN) => sababaraha lampah kalawan argumen

Contona, fungsi penjumlahan bisa digambarkeun salaku:

(a, b) => a + b

Pikeun List.Accumulate, fungsi accumulator ieu ngagaduhan dua argumen anu diperyogikeun (aranjeunna tiasa namina naon waé, tapi nami biasa nyaéta kaayaan и ayeuna, sakumaha dina pitulung resmi pikeun fungsi ieu, dimana:

• kaayaan - variabel dimana hasilna akumulasi (nilai awalna nyaéta anu disebatkeun di luhur siki)
  
• ayeuna - nilai iterated salajengna tina daptar daptar
  

Contona, hayu urang nempo léngkah tina logika konstruksi handap:

=Daptar.Akumulasi({3, 2, 5}, 10, (kaayaan, ayeuna) => kaayaan + ayeuna)

Ieu akumulasi panganyarna kaayaan nilaina List.Accumulate fungsi urang jeung outputs salaku hasilna:

Upami anjeun ngalamun sakedik, teras nganggo fungsi List.Accumulate, anjeun tiasa simulasi, contona, fungsi Excel CONCATENATE (dina Power Query, analogna disebut. Téks.Gabungkeun) ngagunakeun ungkapan:

Atawa malah neangan nilai maksimum (niru fungsi MAX Excel, nu dina Power Query disebut Daptar.Max):

Sanajan kitu, fitur utama List.Accumulate nyaéta kamampuhan pikeun ngolah teu ukur téks basajan atawa béréndélan numerik salaku argumen, tapi objék nu leuwih kompleks - contona, daptar-ti-béréndélan atawa daptar-ti-catetan (halo, Diréktori!)

Hayu urang tingali deui konstruksi anu ngalaksanakeun ngagantian dina masalah urang:

Daptar. Akumulasi (pitunjuk, [Alamat], (kaayaan,ayeuna) => Text.Ganti(kaayaan, ayeuna[Teangan], ayeuna[Ganti]) )

Naon anu lumangsung di dieu?

1. Salaku nilai awal (siki) urang nyokot téks kagok munggaran ti kolom [Alamat] méja kami: 199034, St. Petersburg, str. Beringa, d. 1
2. Lajeng List.Accumulate iterates leuwih elemen daptar hiji-hiji - Buku Panduan. Unggal unsur tina daptar ieu mangrupa rékaman diwangun ku sapasang widang "Naon pikeun manggihan - Naon nu kudu diganti ku" atawa, dina basa sejen, baris salajengna dina diréktori.
3. Fungsi accumulator nempatkeun kana variabel kaayaan nilai awal (alamat munggaran 199034, St. Petersburg, str. Beringa, d. 1) sarta ngalakukeun hiji fungsi accumulator - operasi ngagantian ngagunakeun standar M-fungsi Téks.Ganti (sarua jeung fungsi SUBSTITUTE Excel). Sintaksisna nyaéta:

   Text.Replace (téks aslina, naon anu urang pilari, naon anu urang ngagantikeun)

   sareng di dieu urang gaduh:

• kaayaan nyaeta alamat kotor urang, nu perenahna di kaayaan (ka ditu ti siki)
  
• ayeuna [Search] - nilai widang Pikeun mendakan ti éntri iterated salajengna tina daptar pitunjuk, nu perenahna di variabel ayeuna
• ayeuna [Ganti] - nilai widang ngagentos ti éntri iterated salajengna tina daptar pitunjukngagolér di ayeuna

Ku kituna, pikeun tiap alamat, siklus pinuh enumeration sadaya garis dina diréktori dijalankeun unggal waktu, ngaganti téks ti widang [Papanggihan] jeung nilai ti widang [Ganti].

Miharep anjeun ngagaduhan ide 🙂

• Bulk ngaganti téks dina daptar maké rumus
  
• Babasan Biasa (RegExp) dina Power Query