Katakan anda menjalankan beberapa projek dengan belanjawan yang berbeza dan ingin menggambarkan kos anda untuk setiap projek. Iaitu, dari jadual sumber ini:
.. dapatkan sesuatu seperti ini:
Dalam erti kata lain, anda perlu menyebarkan belanjawan sepanjang hari setiap projek dan mendapatkan versi ringkas carta Gantt projek. Melakukan ini dengan tangan anda adalah panjang dan membosankan, makro adalah sukar, tetapi Power Query untuk Excel dalam situasi sedemikian menunjukkan kuasanya dalam semua kemuliaannya.
Kuasa Kuasa ialah alat tambah daripada Microsoft yang boleh mengimport data ke dalam Excel daripada hampir mana-mana sumber dan kemudian mengubahnya dalam sekumpulan cara yang berbeza. Dalam Excel 2016, tambahan ini sudah terbina dalam secara lalai dan untuk Excel 2010-2013 ia boleh dimuat turun daripada tapak web Microsoft dan kemudian dipasang pada PC anda.
Mula-mula, mari tukar jadual asal kami menjadi jadual "pintar" dengan memilih arahan Format sebagai jadual tab Laman Utama (Laman Utama — Format sebagai Jadual) atau dengan menekan pintasan papan kekunci Ctrl+T :
Kemudian pergi ke tab Tarikh (jika anda mempunyai Excel 2016) atau pada tab Kuasa Kuasa (jika anda mempunyai Excel 2010-2013 dan anda memasang Power Query sebagai tambahan yang berasingan) dan klik butang Daripada Jadual / Julat. :
Jadual pintar kami dimuatkan ke dalam editor pertanyaan Power Query, di mana langkah pertama adalah untuk menyediakan format nombor untuk setiap lajur menggunakan menu lungsur dalam pengepala jadual:
Untuk mengira belanjawan sehari, anda perlu mengira tempoh setiap projek. Untuk melakukan ini, pilih (tahan kekunci Ctrl) lajur dahulu Selesai, Dan kemudian Start dan pilih pasukan Tambah lajur – Tarikh – Tolak hari (Tambah Lajur — Tarikh — Tolak hari):
Nombor yang terhasil adalah kurang 1 daripada yang diperlukan, kerana kami sepatutnya memulakan setiap projek pada hari pertama pagi dan selesai pada hari terakhir pada waktu petang. Oleh itu, pilih lajur yang terhasil dan tambah unit padanya menggunakan arahan Transform – Standard – Tambah (Ubah — Standard — Tambah):
Sekarang mari kita tambahkan lajur tempat kita mengira belanjawan setiap hari. Untuk melakukan ini, pada tab Tambah lajur Saya tidak bermain Lajur tersuai (Lajur Tersuai) dan dalam tetingkap yang muncul, masukkan nama medan baharu dan formula pengiraan, menggunakan nama lajur daripada senarai:
Kini detik paling halus – kami mencipta satu lagi lajur terkira dengan senarai tarikh dari mula hingga akhir dengan langkah selama 1 hari. Untuk melakukan ini, tekan butang sekali lagi Lajur tersuai (Lajur Tersuai) dan gunakan bahasa Power Query terbina dalam M, yang dipanggil Senarai.Tarikh:
Fungsi ini mempunyai tiga hujah:
- tarikh mula – dalam kes kami, ia diambil dari lajur Start
- bilangan tarikh yang akan dijana – dalam kes kami, ini ialah bilangan hari untuk setiap projek, yang kami kira lebih awal dalam lajur Penolakan
- langkah masa – ditetapkan mengikut reka bentuk #duration(1,0,0,0), bermakna dalam bahasa M – satu hari, sifar jam, sifar minit, sifar saat.
Selepas mengklik OK kami mendapat senarai (Senarai) tarikh, yang boleh dikembangkan menjadi baris baharu menggunakan butang dalam pengepala jadual:
… dan kita dapat:
Kini yang tinggal hanyalah meruntuhkan jadual, menggunakan tarikh yang dijana sebagai nama untuk lajur baharu. Pasukan bertanggungjawab untuk ini. Lajur terperinci (Lajur Pangsi) tab Tukar (Transformasi):
Selepas mengklik OK kami mendapat hasil yang sangat hampir dengan yang dikehendaki:
Null ialah, dalam kes ini, analog sel kosong dalam Excel.
Ia kekal untuk mengalih keluar lajur yang tidak perlu dan memunggah jadual yang terhasil di sebelah data asal dengan arahan Tutup dan muat – Tutup dan muatkan… (Tutup & Muatkan — Tutup & Muatkan ke…) tab Laman Utama (Rumah):
Kami mendapat hasilnya:
Untuk keindahan yang lebih besar, anda boleh menyesuaikan penampilan jadual pintar yang terhasil pada tab Pembina (Reka Bentuk): tetapkan gaya warna tunggal, lumpuhkan butang penapis, dayakan jumlah, dsb. Selain itu, anda boleh memilih jadual dengan tarikh dan membolehkan penserlahan nombor untuknya menggunakan pemformatan bersyarat pada tab Laman Utama — Pemformatan Bersyarat — Skala Warna (Laman Utama — Pemformatan Bersyarat — Skala Warna):
Dan bahagian yang terbaik ialah pada masa hadapan anda boleh mengedit projek lama dengan selamat atau menambah projek baharu pada jadual asal, dan kemudian mengemas kini jadual yang betul dengan tarikh dengan butang tetikus kanan – dan Power Query akan mengulangi semua tindakan yang telah kami lakukan secara automatik .
Voilà!
- Carta Gantt dalam Excel menggunakan pemformatan bersyarat
- Kalendar pencapaian projek
- Menjana Baris Pendua dengan Power Query