Ini adalah masalah anggaran pemadam kebakaran. Orang-orang di rumah bomba telah membuat anggaran yang salah dalam Excel. Transformasi Kuasa Kuasa yang luar biasa memberikan penyelesaiannya.
Tonton Video
- Steve harus menjumlahkan nombor yang telah dimasukkan dalam lajur teks
- Terdapat beberapa baris di setiap sel, dipisahkan dengan alt = "" + Enter
- Perlu membahagikan garis ke baris, kemudian menguraikan jumlah dolar dari tengah setiap sel
- Ringkaskan mengikut Pusat Kos
- Bina jadual carian
- Dapatkan jumlah dari jadual carian, menggunakan IFNA untuk mengabaikan kesalahan pada baris kosong
- Bonus: Tambahkan makro Peristiwa untuk mengemas kini lembaran kerja apabila mereka menukar sel.
Transkrip Video
Belajar Excel dari, Podcast Episode 2160: Data SUM Yang Sudah Dimasukkan Alt +.
Hey. Selamat kembali ke netcast. Saya Bill Jelen. Saya tidak membuat ini. Saya mendapat soalan daripada seseorang yang mempunyai data - data anggaran - yang kelihatan seperti ini. Sekarang, saya meletakkan kata-kata palsu di sini supaya kami tidak mempunyai maklumat anggaran mereka, tetapi seseorang yang baru ke jabatan perakaunan, pergi ke sebuah syarikat, dan syarikat ini selama bertahun-tahun telah melakukan anggaran mereka seperti ini. Mereka bukan akauntan yang melakukan anggaran, mereka adalah orang-orang lini, tetapi ini adalah cara mereka melakukannya, dan dia tidak dapat membuat mereka berubah. Jadi, inilah matlamat kami. Dia mengatakan bahawa ini seburuk menaip anggaran ke Word.
Ya, hampir, tapi untungnya, berkat pertanyaan kuasa, ini akan menyelamatkan masalah kita. Inilah matlamat kami. Untuk setiap PUSAT KOS di sini, kami ingin melaporkan jumlah semua nombor tersebut. Jadi, ada nama perbelanjaan, a -, secara rutin a -, kemudian tanda $, dan kemudian, hanya untuk menjadikan hidup menarik, sesekali, nota rawak selepas; tidak sepanjang masa, hanya sebilangan masa. Baris kosong antara masing-masing. Banyak data.
Jadi, inilah yang akan saya lakukan. Saya akan turun ke bahagian paling bawah, sel terakhir, saya akan memilih semua perkara ini, termasuk tajuknya. Saya akan membuat NAMA. Saya akan memanggilnya MyData. MyData, seperti itu, okey? Baiklah. Sekarang kita akan menggunakan kueri kuasa yang percuma pada tahun 2010 atau 2013, dibina pada 2016 dan 2016 Office 365. Itu akan datang dari JADUAL ATAU RANGE. Baiklah. Perkara pertama, bila-bila masa kita mempunyai kekosongan dalam COLUMN A, semua NULLS yang ingin kita hilangkan. Oleh itu, saya akan nyahtandakan NULL. Hebat. Baik. Sungguh, dalam data ini, dalam versi data ini, kerana saya akan membina VLOOKUP, kami tidak memerlukan ruangan ini. Jadi, saya akan klik kanan dan hilangkan lajur itu, jadi HAPUSKAN lajur.
Baiklah. Sekarang, di sinilah sihir aneh itu akan berlaku. Pilih lajur ini, SPLIT COLUMN OLEH DELIMITER, dan kita pasti akan masuk ke ADVANCED. Pembatas akan menjadi watak khas dan kita akan membelah setiap kejadian pembatas. Jadi, di sini, saya rasa mereka sebenarnya sudah mengetahuinya kerana saya mengembangkannya, tetapi saya akan menunjukkan kepada anda. MASUKKAN KARAKTER KHAS. Saya akan mengatakan bahawa ini adalah MAKANAN LINE, baiklah, jadi, pada setiap kejadian MAKANAN LINE, dan saya akan MENGHASILKAN DALAM ROWS. Baiklah, dan apa yang akan berlaku di sini ialah, 1, 2, 3, 4, 5, saya akan mendapat 5 baris atau saya akan mengatakan 1001, tetapi, di setiap baris, ia akan mempunyai perbezaan garis dari sel ini. Ini luar biasa. Terdapat 1, 2, 3, 4, 5, 1001. Baiklah. Sekarang kita hanya perlu menguraikan budak jahat ini. Baiklah,jadi, pilih lajur itu, SPLIT COLUMN OLEH DELIMITER. Kali ini, pembatas akan menjadi tanda $. Itu sempurna, sekali, pada tanda $ pertama yang kami dapati, sekiranya ada $ log keluar di bahagian yang akan datang. Kita akan SPLIT INTO COLUMNS. Klik OK. Baiklah. Jadi, ada perinciannya. Inilah wang kita.
Sekarang, saya akan membahagikan ini di RUANG. Oleh itu, pilih lajur ini, SPLIT COLUMN BY A DELIMITER, dan pembatas akan menjadi RUANG, ya, sekali di LEFT-PALING DELIMITER, klik OK, dan saya tidak memerlukan komen-komen di luar sana sehingga komen-komen itu kami ' kembali akan MENGHILANGKAN. Sebenarnya, saya juga tidak memerlukan ini kerana saya hanya berusaha mendapatkan jumlah keseluruhan barang, jadi saya akan MENGHAPUSKAN.
Sekarang, ubah. KUMPULAN MENGIKUT PUSAT KOS, NAMA COLUMN BARU akan dipanggil JUMLAH, OPERASI akan menjadi SUM, dan lajur mana yang akan kita sampaikan? BUTIRAN2.1. Cantik. Klik OK, baiklah, dan yang akhirnya kita dapat ialah satu baris per PUSAT KOS dengan JUMLAH semua item baris tersebut. RUMAH, TUTUP & PINJAMAN. Mungkin akan memasukkan lembaran kerja baru. Saya berharap ia memasukkan lembaran kerja baru, dan ia berlaku, dan lembaran kerja tersebut dipanggil MYDATA_1. MYDATA_1.
Baiklah. Sekarang kita akan kembali ke sini ke data asal dan melakukan langkah-langkah ini. Pada yang pertama, = VLOOKUP dari 1001 ke hasil kami. Ini seperti membuat rujukan pekeliling tetapi tidak akan memberi kita rujukan pekeliling. , 2, SALAH. Saya mahukan padanan yang tepat. Baiklah, tetapi kami tidak mahu melakukannya untuk sel kosong. Jadi, saya akan katakan, sebenarnya, mari kita menyalinnya sepanjang masa. CONTROL + C, turun ke bawah hanya untuk melihat apa yang kita dapat. Mungkin kita mendapat N / As dan saya dapat menyingkirkannya dengan IFNA. Ya, cantik, baiklah. Oleh itu, mari kita hilangkan N / As. Sekiranya T / A, maka kita hanya mahukan "". Kami tidak mahu apa-apa di sana. KAWALAN + MASUK. Baiklah. Sekarang, itu harus menjadi JUMLAH. Mari kita lihat apakah kita dapat mencari yang pendek dan hanya melakukan matematik. = 627.37 + 7264.25 + 6066.01 + 4010.66 + 9773.94, dan JUMLAHnya, 27742.23 adalah itu. Mengagumkan. (= IFNA (VLOOKUP (A2, MyData_1,2, SALAH), ""))
Sekarang, inilah perjanjiannya. Oleh itu, kita mempunyai orang-orang yang keluar dari sini untuk menukar barang, baiklah, dan katakanlah bahawa mereka melaluinya dan mereka mengubah anggaran, 40294.48, dan mereka datang ke sini dan mengubahnya menjadi 6000, seperti itu, dan mereka menambah yang baru, ALT + ENTER, SESUATU - tanda $, $ 1000 baru sahaja ditambahkan. Baiklah. Sekarang, tentu saja, ketika saya menekan ENTER, nombor ini, 40294.48, tidak akan dikemas kini, baiklah, tetapi apa yang harus kita lakukan ialah pergi ke tab DATA dan kita ingin MENYATAKAN SEMUA. Jadi, 40294.48. Menonton, menonton, menonton, menonton. REFRESH SEMUA. Mengagumkan luar biasa.
Saya suka pertanyaan kuasa. Pertanyaan kuasa adalah perkara yang paling menakjubkan. Data ini, yang pada dasarnya sama seperti data kata di dalam sel, kita sekarang telah diperbaharui. Anda mungkin boleh membuat semacam makro yang mengatakan bahawa setiap kali seseorang mengubah sesuatu dalam COLUMN C, kita teruskan dan mengklik REFRESH ALL menggunakan makro dan hanya memperoleh hasilnya secara berterusan, sentiasa menyegarkan.
Soalan yang mengerikan dihantar. Saya merasa tidak senang dengan Steve yang harus menangani perkara ini, tetapi sekarang, dengan menggunakan pertanyaan kuasa di Office 365 atau dimuat turun untuk tahun 2010 atau 2013, anda mempunyai cara yang sangat, sangat mudah untuk menyelesaikannya.
Tunggu. Baiklah, tambahan: mari menjadikannya lebih baik. Lembaran ini dipanggil DATA dan saya telah menyimpan buku kerja sebagai berkemampuan makro, jadi xlsm. Sekiranya anda xlsx, jangan lewatkan menyimpan sebagai xlsm. ALT + F11. Cari buku kerja yang disebut DATA, klik dua kali, kiri atas, LEMBARAN KERJA, dan kemudian UBAH bila-bila masa kita menukar lembaran kerja, dan kita akan mengatakan ACTIVEWORKBOOK.REFRESHALL, dan kemudian tutup, baiklah, dan sekarang mari kita mencubanya. Mari edit sesuatu. Oleh itu, kita akan mengambil raspberi yang sekarang adalah 8.000 dan kita akan mengubahnya menjadi 1000, jadi kita mengurangkan sebanyak 7000. Apabila saya menekan ENTER, saya ingin melihat bahawa 42,000 turun menjadi 35,000. Ah. Hebat.
Baiklah, hei. Di sinilah saya biasanya meminta anda membeli buku saya tetapi, hari ini, saya akan meminta anda untuk membeli buku rakan saya - Ken Puls dan Miguel Escobar - M adalah untuk (DATA) MONKEY. Semua yang saya pelajari mengenai pertanyaan kuasa, saya belajar dari buku ini. Ia adalah buku yang luar biasa. Lihatlah.
Penutup episod: Steve mempunyai jumlah hingga jumlah yang telah dimasukkan dalam lajur teks; beberapa baris dalam setiap sel, dipisahkan oleh ALT + ENTER; perlu membahagikan garis ke baris, kemudian menguraikan jumlah dolar dari tengah setiap sel; ringkaskan oleh PUSAT KOS; membina jadual carian; dapatkan jumlah dari jadual carian, menggunakan IFNA untuk mengabaikan kesalahan pada baris kosong; dan kemudian, bonus, makro di akhir, makro acara untuk mengemas kini lembaran kerja apabila mereka menukar sel.
Saya ingin mengucapkan terima kasih kepada Steve kerana telah menghantar soalan itu dan saya sangat gembira kerana saya mendapat jawapan - sebelum pertanyaan kuasa, tentunya sangat sukar - dan saya ingin mengucapkan terima kasih kerana berhenti. Kami akan berjumpa anda di lain kali untuk siaran net lain dari.
Muat turun fail
Muat turun fail contoh di sini: Podcast2160.xlsm
