Paragraf
Salah sawijining video sing paling dideleng ing saluran YouTubeku yaiku video babagan Flash Fill ing Microsoft Excel. Inti saka alat iki yaiku yen sampeyan kudu ngowahi data sumber sampeyan, mula sampeyan mung kudu miwiti ngetik asil sing pengin dipikolehi ing kolom jejer. Sawise sawetara sel sing diketik kanthi manual (biasane 2-3 cukup), Excel bakal "ngerti" logika transformasi sing dibutuhake lan kanthi otomatis nerusake apa sing diketik, ngrampungake kabeh karya sing monoton kanggo sampeyan:
Intine efisiensi. Tombol sihir "nindakake kanthi bener" sing kita tresnani banget, ta?
Nyatane, ana analog saka alat kasebut ing Power Query - ana sing diarani Kolom saka conto (Kolom saka Tuladha). Nyatane, iki minangka intelijen buatan cilik sing dibangun ing Power Query sing bisa sinau kanthi cepet saka data sampeyan banjur ngowahi. Ayo dideleng kanthi luwih rinci babagan kemampuane ing sawetara skenario praktis supaya bisa dingerteni manawa bisa migunani kanggo kita ing tugas nyata.
Tuladha 1. Tempel/nglereni teks
Ayo kita duwe tabel "pinter" ing Excel kanthi data karyawan:
Muat menyang Power Query kanthi cara standar - kanthi tombol Saka Tabel / Range tab Data (Data — Saka Tabel/Rentang).
Upaminipun kita kudu nambah kolom karo jeneng mburi lan inisial kanggo saben pegawe (Ivanov SV kanggo pegawe pisanan, etc.). Kanggo ngatasi masalah iki, sampeyan bisa nggunakake salah siji saka rong cara:
- klik-tengen ing judhul kolom karo data sumber banjur pilih printah Tambah kolom saka conto (Tambah kolom saka conto);
- pilih siji utawa luwih kolom karo data lan ing tab Nambah kolom milih tim Kolom saka conto. Ing kene, ing dhaptar gulung mudhun, sampeyan bisa nemtokake manawa kabeh utawa mung kolom sing dipilih kudu dianalisis.
Banjur kabeh iku prasaja - ing kolom sing katon ing sisih tengen, kita miwiti ngetik conto asil sing dikarepake, lan intelijen buatan sing dibangun ing Power Query nyoba mangertos logika transformasi kita lan terus maju dhewe:
Miturut cara, sampeyan bisa ngetik pilihan sing bener ing sembarang sel kolom iki, IE ora kudu ndhuwur-mudhun lan saurutan. Uga, sampeyan bisa kanthi gampang nambah utawa mbusak kolom saka analisis mengko nggunakake kothak centhang ing baris judhul.
Pay manungsa waé menyang rumus ing ndhuwur jendhela - iki apa Power Query pinter nggawe kanggo entuk asil kita kudu. Iki, kanthi cara, prabédan dhasar antarane alat iki lan Isi cepet ing Excel. Isi cepet dianggo kaya "kothak ireng" - padha ora nuduhake kita logika saka transformasi, nanging mung menehi asil siap-digawe lan kita njupuk kanggo diwenehake. Ing kene kabeh transparan lan sampeyan bisa ngerti kanthi jelas apa sing kedadeyan karo data kasebut.
Yen sampeyan ndeleng Power Query "kejiret ide", sampeyan bisa kanthi aman menet tombol kasebut OK utawa trabasan keyboard Ctrl+Ketik – kolom khusus kanthi rumus sing diciptakake dening Power Query bakal digawe. Miturut cara, mengko bisa gampang diowahi minangka kolom sing digawe kanthi manual (kanthi perintah Nambahake Kolom - Kolom Kustom) kanthi ngeklik lambang gear ing sisih tengen jeneng langkah:
Tuladha 2: Kasus kaya ing ukara
Yen sampeyan klik-tengen ing judhul kolom karo teks banjur pilih printah transformasi (Transformasi), banjur sampeyan bisa ndeleng telung printah sing tanggung jawab kanggo ngganti registrasi:
Trep lan kelangan, nanging ing dhaptar iki, umpamane, aku ora duwe pilihan liyane - kaya ing ukara, nalika kapitalisasi (kapital) ora dadi huruf pisanan ing saben tembung, nanging mung huruf pisanan ing sel, lan sisa teks nalika Iki ditampilake ing huruf cilik (cilik).
Fitur sing ilang iki gampang dileksanakake kanthi intelijen buatan Kolom saka conto - mung ketik sawetara opsi kanggo Power Query kanggo nerusake kanthi semangat sing padha:
Minangka rumus ing kene, Power Query nggunakake akeh fungsi Teks. Ndhuwur и Teks. Ngisor, ngowahi teks dadi huruf gedhe lan cilik, saben, lan fungsi Teks. Mulai и Teks.Mid - analog saka fungsi Excel LEFT lan PSTR, bisa ngekstrak substring saka teks saka sisih kiwa lan tengah.
Tuladha 3. Permutasi tembung
Kadhangkala, nalika ngolah data sing ditampa, dadi perlu kanggo ngatur maneh tembung ing sel ing urutan tartamtu. Mesthi, sampeyan bisa dibagi kolom dadi kolom tembung sing kapisah dening pemisah banjur lem maneh ing urutan sing ditemtokake (aja lali nambah spasi), nanging kanthi bantuan alat kasebut. Kolom saka conto kabeh bakal luwih gampang:
Tuladha 4: Mung angka
Tugas liyane sing penting banget yaiku mung narik nomer (nomer) saka isi sel. Kaya sadurunge, sawise ngemot data menyang Power Query, pindhah menyang tab Nambahake kolom - Kolom saka conto lan isi sawetara sel kanthi manual supaya program ngerti apa sing dikarepake:
Bingo!
Maneh, kudu dideleng ing sisih ndhuwur jendhela kanggo mesthekake Query ngasilake rumus kanthi bener - ing kasus iki ngemot fungsi Teks. Pilih, sing, kaya sing sampeyan duga, ngekstrak karakter sing diwenehake saka teks sumber miturut dhaptar. Sabanjure, dhaptar iki, mesthi, bisa gampang diowahi ing garis rumus yen perlu.
Tuladha 5: Mung teks
Kajaba kanggo conto sadurunge, sampeyan bisa narik metu lan kosok balene - mung teks, mbusak kabeh nomer, tandha wacan, etc.
Ing kasus iki, fungsi sing wis ngelawan ing makna digunakake - Text.Remove, kang mbusak karakter saka senar asli miturut dhaftar diwenehi.
Conto 6: Ekstrak data saka bubur alfanumerik
Power Query uga bisa mbantu ing kasus sing luwih angel, nalika sampeyan kudu ngekstrak informasi migunani saka bubur alfanumerik ing sel, contone, entuk nomer akun saka katrangan babagan tujuan pambayaran ing statement bank:
Elinga yen rumus konversi Power Query bisa uga cukup rumit:
Kanggo gampang maca lan ngerti, bisa diowahi dadi bentuk sing luwih waras nggunakake layanan online gratis. Power Query Formatter:
Bab sing migunani banget - ngormati para pangripta!
Conto 7: Ngonversi tanggal
alat Kolom saka conto bisa ditrapake kanggo kolom tanggal utawa tanggal uga. Nalika sampeyan ngetik digit pisanan tanggal, Power Query bakal mbiyantu nampilake dhaptar kabeh opsi konversi bisa:
Dadi sampeyan bisa kanthi gampang ngowahi tanggal asli menyang format eksotis, kayata "taun-sasi-dina":
Tuladha 8: Kategorisasi
Yen kita nggunakake alat kasebut Kolom saka conto menyang kolom karo data numerik, kerjane beda. Upamane kita duwe asil tes karyawan sing dimuat menyang Power Query (skor kondisional ing kisaran 0-100) lan nggunakake gradasi kondisional ing ngisor iki:
- Master - sing ngetung luwih saka 90
- Pakar - ngetung saka 70 nganti 90
- Pangguna - saka 30 nganti 70
- Pemula - sing ngetung kurang saka 30
Yen kita nambahake kolom saka conto menyang dhaptar lan miwiti ngatur gradasi kasebut kanthi manual, banjur enggal Power Query bakal njupuk ide kita lan nambah kolom kanthi rumus, ing ngendi operator nested siji liyane. if logika bakal dileksanakake, meh padha karo sing kita butuhake:
Maneh, sampeyan ora bisa mencet kahanan kanggo mburi, nanging klik ing OK banjur mbenerake nilai ambang sing wis ana ing rumus - luwih cepet kanthi cara iki:
Serat
Mesthi alat Kolom saka conto ora "pil ajaib" lan, cepet utawa mengko, bakal ana kahanan non-standar utawa utamané nguciwakake kasus saka "farm kolektif" ing data, nalika Power Query bakal gagal lan ora bakal bisa kanggo apa kita arep. bener kanggo kita. Nanging, minangka alat bantu, iku apik banget. Kajaba iku, kanthi nyinaoni rumus sing digawe, sampeyan bisa nggedhekake kawruh babagan fungsi basa M, sing bakal migunani ing mangsa ngarep.
- Parsing Teks nganggo Ekspresi Reguler (RegExp) ing Power Query
- Panelusuran teks kabur ing Power Query
- Ngisi Flash ing Microsoft Excel