Daptar eusi
Salah sahiji pidéo anu paling sering ditingali dina saluran YouTube kuring nyaéta pidéo ngeunaan Flash Fill dina Microsoft Excel. Intina alat ieu nyaéta yén upami anjeun kedah kumaha waé ngarobih data sumber anjeun, maka anjeun kedah mimiti ngetik hasil anu anjeun pikahoyong dina kolom anu padeukeut. Saatos sababaraha sél diketik sacara manual (biasana 2-3 cekap), Excel bakal "ngartos" logika transformasi anu anjeun peryogikeun sareng otomatis neraskeun naon anu anjeun ketik, ngalengkepan sadaya padamelan anu monoton pikeun anjeun:
The quintessence efisiensi. Tombol sihir "ngalakukeun éta leres" anu urang sadayana resep pisan, sanés?
Kanyataanna, aya analog tina alat sapertos dina Power Query - aya anu disebut Kolom tina conto (Kolom tina Conto). Nyatana, ieu mangrupikeun intelijen buatan leutik anu diwangun kana Power Query anu tiasa gancang diajar tina data anjeun teras ngarobih. Hayu urang nyandak katingal ngadeukeutan dina kamampuhan na dina sababaraha skenario praktis dina urutan ngartos dimana eta bisa mangpaat pikeun urang dina tugas nyata.
Conto 1. Téks gluing/motong
Anggap urang gaduh tabel "pinter" sapertos di Excel sareng data karyawan:
Muatkeun kana Power Query ku cara standar - nganggo tombol Ti Table / Rentang tab data (Data — Tina Tabél/Rentang).
Anggap urang kudu nambahan kolom kalawan ngaran tukang jeung inisial pikeun tiap pagawe (Ivanov SV pikeun pagawe munggaran, jsb). Pikeun ngabéréskeun masalah ieu, anjeun tiasa nganggo salah sahiji tina dua metode:
- klik katuhu dina judul kolom jeung sumber data tur pilih paréntah Tambahkeun kolom tina conto (Tambihkeun kolom tina conto);
- pilih hiji atawa leuwih kolom kalawan data sarta dina tab Nambahkeun kolom milih tim Kolom tina conto. Di dieu, dina daptar turun-handap, anjeun tiasa nangtukeun naha sadayana atanapi ngan ukur kolom anu dipilih kedah dianalisis.
Teras sadayana saderhana - dina kolom anu nembongan di beulah katuhu, urang mimiti ngalebetkeun conto hasil anu dipikahoyong, sareng intelijen buatan anu diwangun dina Power Query nyobian ngartos logika transformasi urang sareng teraskeun nyalira:
Ku jalan kitu, anjeun tiasa ngalebetkeun pilihan anu leres dina sél mana waé dina kolom ieu, nyaéta henteu kedah luhur-handap sareng sakaligus. Ogé, anjeun tiasa sacara gampil nambihan atanapi ngahapus kolom tina analisa engké nganggo kotak centang dina bar judul.
Nengetan rumus dina luhureun jandela - ieu naon pinter Power Query nyieun pikeun meunangkeun hasil nu urang butuhkeun. Ku jalan kitu, ieu mangrupikeun bédana dasar antara alat ieu sareng Eusian instan dina Excel. Keusikan instan tiasa dianggo sapertos "kotak hideung" - aranjeunna henteu nunjukkeun ka kami logika transformasi, tapi ngan ukur masihan hasil anu siap-siap sareng kami nganggap éta. Di dieu sadayana transparan sareng anjeun salawasna tiasa leres-leres ngartos naon anu kajantenan sareng data.
Upami anjeun ningali yén Power Query "nyandak ideu", maka anjeun tiasa aman pencét tombolna OK atawa potong kompas keyboard Ctrl+asup - kolom khusus sareng rumus anu diciptakeun ku Power Query bakal didamel. Ku jalan kitu, éta tiasa engké gampang diédit salaku kolom anu didamel sacara manual (kalayan paréntah Nambahkeun Kolom - Kolom Adat) ku ngaklik ikon gear ka katuhu tina ngaran léngkah:
Conto 2: Pasualan saperti dina kalimah
Upami anjeun klik katuhu dina judul kolom sareng téks sareng pilih paréntahna transformasi (Transformasi), teras anjeun tiasa ningali tilu paréntah anu tanggung jawab pikeun ngarobih register:
Merenah tur tiis, tapi dina daptar ieu, contona, kuring pribadi geus salawasna lacked hiji pilihan deui - bisi saperti dina kalimat, nalika dimodalan (kapital) janten teu hurup kahiji dina unggal kecap, tapi ngan hurup kahiji dina sél, jeung sésana téks nalika Ieu dipintonkeun dina hurup leutik (leutik).
Fitur anu leungit ieu gampang diimplementasikeun ku intelijen buatan Kolom tina conto - cukup lebetkeun sababaraha pilihan pikeun Power Query pikeun neraskeun dina sumanget anu sami:
Salaku rumus di dieu, Power Query ngagunakeun sababaraha fungsi Téks. Luhur и Téks. Handap, ngarobah téks kana hurup luhur jeung leutik, masing-masing, sarta fungsi Téks.Mimitian и Téks. Pertengahan - analog tina fungsi Excel LEFT sareng PSTR, tiasa nimba substring tina téks ti kénca sareng ti tengah.
Conto 3. Permutasi kecap
Sakapeung, nalika ngolah data anu ditampi, janten kedah nyusun ulang kecap dina sél dina sekuen anu ditangtukeun. Tangtosna, anjeun tiasa ngabagi kolom kana kolom kecap anu misah ku separator teras lem deui dina urutan anu ditangtukeun (tong hilap nambihan rohangan), tapi kalayan bantosan alat. Kolom tina conto sagalana bakal leuwih gampang:
Conto 4: Ngan angka
Tugas anu penting pisan nyaéta ngan ukur ngaluarkeun angka (angka) tina eusi sél. Sapertos sateuacana, saatos ngamuat data kana Power Query, angkat ka tab Nambahkeun kolom - Kolom tina conto sareng eusian sababaraha sél sacara manual supados programna ngartos naon anu urang hoyong kéngingkeun:
Bingo!
Sakali deui, éta patut ningali luhureun jandela pikeun mastikeun Query ngahasilkeun rumus anu leres - dina hal ieu ngandung fungsi. Téks. Pilih, anu, sakumaha anu anjeun panginten, nimba karakter anu dipasihkeun tina téks sumber dumasar kana daptar. Salajengna, daptar ieu, tangtosna, tiasa gampang diédit dina bar rumus upami diperyogikeun.
Conto 5: Téks wungkul
Sarupa oge jeung conto samemehna, Anjeun bisa tarik kaluar jeung sabalikna – ngan téks, mupus sadaya nomer, tanda baca, jsb.
Dina hal ieu, fungsi anu geus sabalikna dina harti dipaké - Text.Remove, nu ngaluarkeun karakter tina string aslina nurutkeun daptar dibikeun.
Conto 6: Ékstrak data tina bubur alfanumerik
Power Query ogé tiasa ngabantosan dina kasus anu langkung hese, nalika anjeun kedah nimba inpormasi anu mangpaat tina bubur alfanumerik dina sél, contona, kéngingkeun nomer rekening tina pedaran tujuan pamayaran dina pernyataan bank:
Catet yén rumus konversi anu dibangkitkeun ku Power Query tiasa rada rumit:
Pikeun betah maca sareng ngartos, éta tiasa dirobih kana bentuk anu langkung waras nganggo jasa online gratis. Power Query Formatter:
Hal pisan gunana - hormat ka panyipta!
Conto 7: Ngarobih tanggal
alat Kolom tina conto tiasa diterapkeun ka kolom tanggal atanapi datetime ogé. Lamun anjeun ngasupkeun digit mimiti tanggal hiji, Power Query bakal mantuan mintonkeun daptar sadaya pilihan konvérsi mungkin:
Janten anjeun tiasa sacara gampil ngarobih tanggal aslina kana format anu aheng, sapertos "taun-bulan-dinten":
Conto 8: Kategorisasi
Lamun urang ngagunakeun alat Kolom tina conto mun kolom kalawan data numerik, gawéna béda. Anggap urang gaduh hasil tés karyawan dimuat kana Power Query (skor kondisional dina rentang 0-100) sarta kami nganggo gradasi kondisional handap:
- Masters - jalma anu ngoleksi leuwih ti 90
- Ahli - ngoleksi ti 70 nepi ka 90
- Pamaké - ti 30 nepi ka 70
- Beginners - jalma anu ngoleksi kirang ti 30
Upami urang nambihan kolom tina conto kana daptar sareng mimitian nyusun gradasi ieu sacara manual, maka pas Power Query bakal nyandak ideu sareng nambihan kolom anu nganggo rumus, dimana operator saling nyambungkeun. if Logika bakal dilaksanakeun, sami sareng anu urang peryogikeun:
Deui, anjeun teu bisa mencet kaayaan nepi ka ahir, tapi klik dina OK teras lereskeun nilai ambang anu parantos aya dina rumus - langkung gancang ku cara kieu:
conclusions
Pasti alat Kolom tina conto sanes "pil ajaib" sareng, engké atanapi engké, bakal aya kaayaan anu henteu standar atanapi khususna kasus anu diabaikan tina "ladang koléktif" dina data, nalika Power Query bakal gagal sareng moal tiasa damel naon anu dipikahoyong. bener keur urang. Nanging, salaku alat bantu, éta saé pisan. Tambih Deui, ku diajar rumus anjeunna dihasilkeun, anjeun tiasa ngalegaan pangaweruh anjeun ngeunaan fungsi basa M, nu bakal salawasna datang dina gunana di mangsa nu bakal datang.
- Parsing Téks sareng Ekspresi Biasa (RegExp) dina Power Query
- Pilarian téks kabur dina Power Query
- Flash Eusian dina Microsoft Excel