Ini adalah masalah anggaran petugas pemadam kebakaran. Orang-orang di pemadam kebakaran telah melakukan kesalahan anggaran di Excel. Transformasi Power Query yang luar biasa menyediakan solusinya.
Menonton video
- Steve harus menjumlahkan angka-angka yang telah dimasukkan ke dalam kolom teks
- Ada beberapa baris di setiap sel, dipisahkan oleh alt = "" + Enter
- Perlu memisahkan baris tersebut menjadi baris, lalu parsing jumlah dolar dari tengah setiap sel
- Rangkum menurut Pusat Biaya
- Buat tabel pencarian
- Dapatkan total dari tabel pemeta, menggunakan IFNA untuk mengabaikan kesalahan di baris kosong
- Bonus: Tambahkan makro acara untuk memperbarui lembar kerja saat mereka mengubah sel.
Transkrip Video
Belajar Excel dari, Podcast Episode 2160: SUM Data Yang Telah Alt + Masuk.
Hei. Selamat datang kembali di netcast. Saya Bill Jelen. Saya tidak mengada-ada. Saya mendapat pertanyaan dari seseorang yang memiliki data - data anggaran - yang terlihat seperti ini. Sekarang, saya meletakkan kata-kata palsu di sini sehingga kami tidak memiliki informasi anggaran mereka, tetapi orang baru di departemen akuntansi, pergi ke sebuah perusahaan, dan perusahaan ini selama bertahun-tahun telah melakukan anggaran seperti ini. Mereka bukanlah akuntan yang melakukan anggaran, mereka adalah orang-orang garis, tetapi begitulah cara mereka melakukannya, dan dia tidak dapat membuat mereka berubah. Jadi, inilah tujuan kami. Dia mengatakan ini sama buruknya dengan mengetikkan anggaran ke Word.
Hampir, tapi untungnya, berkat power query, ini akan menyelamatkan masalah kita. Inilah tujuan kami. Untuk setiap PUSAT BIAYA di sini, kami ingin melaporkan total semua angka itu. Jadi, ada nama pengeluaran, a -, secara rutin a -, lalu tanda $, dan kemudian, hanya untuk membuat hidup menarik, sesekali, catatan acak setelahnya; tidak setiap saat, hanya beberapa saat. Baris kosong di antara setiap baris. Berton-ton data.
Jadi, inilah yang akan saya lakukan. Saya akan turun ke bagian paling bawah, sel terakhir, saya akan memilih semua hal ini, termasuk judulnya. Saya akan membuat NAME. Saya akan menyebutnya MyData. MyData, seperti itu, oke? Baik. Sekarang kita akan menggunakan power query yang gratis pada tahun 2010 atau 2013, dibangun pada tahun 2016 dan 2016 Office 365. Itu akan datang dari TABEL ATAU RENTANG. Baik. Hal pertama, kapan pun kita memiliki kekosongan itu di KOLOM A, semua NULL yang ingin kita singkirkan. Jadi saya akan menghapus centang NULL. Hebat. Baik. Sungguh, dalam data ini, dalam versi data ini, karena saya akan membuat VLOOKUP, kita tidak memerlukan kolom ini. Jadi, saya akan klik kanan dan hapus kolom itu, jadi HAPUS kolom.
Baik. Sekarang, di sinilah keajaiban mengerikan akan terjadi. Pilih kolom ini, SPLIT COLUMN BY A DELIMITER, dan kami pasti akan masuk ke ADVANCED. Pembatas akan menjadi karakter khusus dan kita akan membagi setiap kemunculan pembatas. Jadi, di sini, saya pikir mereka sebenarnya sudah menemukannya karena saya mengembangkannya, tapi saya akan menunjukkannya kepada Anda. MASUKKAN KARAKTER KHUSUS. Saya akan mengatakan bahwa ini adalah LINE FEED, baiklah, jadi, pada setiap kejadian LINE FEED, dan saya akan MENYEBABKAN KE BARIS. Baiklah, dan yang akan terjadi di sini adalah, 1, 2, 3, 4, 5, saya akan mendapatkan 5 baris atau saya akan mengatakan 1001, tetapi, di setiap baris, itu akan berbeda baris dari sel ini. Ini luar biasa. Ada 1, 2, 3, 4, 5, 1001. Baiklah. Sekarang kita hanya perlu mengurai bocah nakal ini. Baik,jadi, pilih kolom itu, SPLIT COLUMN BY A DELIMITER. Kali ini, pembatas akan menjadi tanda $. Itu sempurna, sekali, pada tanda $ pertama yang kami temukan, kalau-kalau ada tanda $ di luar sana di bagian yang akan datang. Kami akan MEMBAGI KE KOLOM. Klik OK. Baik. Jadi, ada detailnya. Ini uang kita.
Sekarang, saya akan membagi ini di SPACE. Jadi, pilih kolom ini, SPLIT COLUMN BY A DELIMITER, dan pembatasnya akan menjadi SPASI, ya, sekali di KIRI-PALING DELIMITER, klik OK, dan saya tidak perlu komentar itu di luar sana jadi komentar itu kita ' kembali akan HAPUS. Sebenarnya, tidak perlu ini juga karena saya hanya mencoba untuk mendapatkan total semua barang itu, jadi saya akan MENGHAPUS.
Sekarang, ubah. GROUP BY COST CENTER, NEW COLUMN NAME akan disebut TOTAL, OPERASI akan menjadi SUM, dan kolom mana yang akan kita JUMLAH? DETAIL2.1. Cantik. Klik Oke, baiklah, dan yang akhirnya kita dapatkan adalah satu baris per PUSAT BIAYA dengan TOTAL dari semua item baris tersebut. RUMAH, TUTUP & BEBAN. Ini mungkin akan memasukkan lembar kerja baru. Saya berharap itu menyisipkan lembar kerja baru, dan memang begitu, dan lembar kerja itu disebut MYDATA_1. MYDATA_1.
Baik. Sekarang kita akan kembali ke data asli dan melakukan langkah-langkah ini. Yang pertama, = VLOOKUP 1001 ke dalam hasil kami. Ini seperti menyiapkan referensi melingkar tetapi tidak akan memberi kita referensi melingkar. , 2, SALAH. Saya ingin yang sama persis. Baiklah, tapi kami tidak akan melakukan itu untuk sel kosong. Jadi, saya akan mengatakan, sebenarnya, mari kita salin semuanya. CONTROL + C, turunkan semuanya hanya untuk melihat apa yang kami dapatkan. Mungkin kita mendapatkan N / As dan saya bisa menghilangkannya dengan IFNA. Ya, cantik, baiklah. Jadi, mari kita hilangkan N / As. Jika N / A, maka kita hanya ingin “”. Kami tidak ingin apa pun di sana. CONTROL + ENTER. Baik. Sekarang, itu seharusnya TOTAL. Mari kita lihat apakah kita dapat menemukan yang singkat dan langsung menghitungnya. = 627,37 + 7264,25 + 6066.01 + 4010.66 + 9773.94, dan TOTAL, 27742.23 adalah itu. Sangat luar biasa. (= IFNA (VLOOKUP (A2, MyData_1,2, FALSE), “”))
Sekarang, inilah kesepakatannya. Jadi, kami memiliki orang-orang garis yang ada di sini mengubah hal-hal, baiklah, dan katakanlah mereka berhasil dan mereka mengubah anggaran, 40294.48, dan mereka datang ke sini dan mengubah yang ini menjadi 6000, seperti itu, dan mereka menambahkan yang baru, ALT + ENTER, SOMETHING - $ sign, $ 1000 baru saja ditambahkan. Baik. Sekarang, tentu saja, ketika saya menekan ENTER, nomor ini, 40294.48, tidak akan diperbarui, baiklah, tetapi yang harus kita lakukan adalah pergi ke tab DATA dan kita ingin SEGARKAN SEMUA. Jadi, 40294.48. Tonton, tonton, tonton, tonton. SEGARKAN SEMUA. Sangat luar biasa.
Saya suka power query. Power query adalah hal yang paling menakjubkan. Data ini, yang pada dasarnya sama seperti data kata dalam sel, sekarang kami telah memperbaruinya. Anda bahkan mungkin dapat membuat semacam makro yang mengatakan bahwa setiap kali seseorang mengubah sesuatu di KOLOM C, kami melanjutkan dan mengklik SEGARKAN SEMUA menggunakan makro dan mendapatkan hasil tersebut secara konstan, terus-menerus menyegarkan.
Sungguh pertanyaan yang mengerikan dikirim. Saya merasa kasihan kepada Steve yang harus berurusan dengan ini, tetapi sekarang, menggunakan kueri daya di Office 365 atau diunduh untuk 2010 atau 2013, Anda memiliki cara yang sangat, sangat mudah untuk menyelesaikannya.
Tunggu. Oke, tambahan: mari kita membuatnya lebih baik. Sheet ini disebut DATA dan saya telah menyimpan buku kerja sebagai makro-enabled, jadi xlsm. Jika Anda xlsx, jangan lewatkan penyimpanan sebagai xlsm. ALT + F11. Temukan buku kerja yang disebut DATA, klik dua kali, kiri atas, LEMBAR KERJA, lalu GANTI setiap kali kita mengganti lembar kerja, dan kita akan mengatakan BUKU AKTIVITAS.REFRESHALL, lalu tutup, baiklah, dan sekarang mari kita coba. Ayo edit sesuatu. Jadi, kami akan mengambil raspberry yang saat ini 8.000 dan kami akan mengubahnya menjadi 1000, jadi kami mengurangi 7000. Saat saya menekan ENTER, saya ingin melihat bahwa 42.000 turun menjadi 35.000. Ah. Hebat.
Baiklah. Di sinilah saya biasanya meminta Anda untuk membeli buku saya tetapi, hari ini, saya akan meminta Anda untuk membeli buku teman-teman saya - Ken Puls dan Miguel Escobar - M untuk (DATA) MONKEY. Semua yang saya pelajari tentang power query, saya pelajari dari buku ini. Itu buku yang luar biasa. Lihat itu.
Ringkasan episode: Steve memiliki jumlah angka yang telah dimasukkan di kolom teks; beberapa baris di setiap sel, dipisahkan oleh ALT + ENTER; perlu membagi baris tersebut menjadi baris, lalu mengurai jumlah dolar dari tengah setiap sel; diringkas dengan PUSAT BIAYA; membangun tabel pemeta; dapatkan total dari tabel pemeta, menggunakan IFNA untuk mengabaikan kesalahan di baris kosong; lalu, bonus, makro di bagian akhir, makro peristiwa untuk memperbarui lembar kerja saat mereka mengubah sel.
Saya ingin berterima kasih kepada Steve karena telah mengirimkan pertanyaan itu dan saya sangat senang saya memiliki jawaban - sebelum permintaan yang kuat, itu akan sangat, sangat sulit - dan saya ingin berterima kasih karena Anda telah mampir. Sampai jumpa di lain waktu untuk netcast lain dari.
Unduh berkas
Unduh file contoh di sini: Podcast2160.xlsm
