Katakanlah Anda menjalankan beberapa proyek dengan anggaran berbeda dan ingin memvisualisasikan biaya Anda untuk masing-masing proyek. Yaitu, dari tabel sumber ini:
.. dapatkan sesuatu seperti ini:
Dengan kata lain, Anda perlu menyebarkan anggaran selama hari-hari setiap proyek dan mendapatkan versi sederhana dari bagan Gantt proyek. Melakukan ini dengan tangan Anda lama dan membosankan, makro sulit, tetapi Power Query untuk Excel dalam situasi seperti itu menunjukkan kekuatannya dalam semua kemuliaan.
Kueri Daya adalah add-on dari Microsoft yang dapat mengimpor data ke Excel dari hampir semua sumber dan kemudian mengubahnya dengan berbagai cara. Di Excel 2016, add-in ini sudah built-in secara default, dan untuk Excel 2010-2013 dapat diunduh dari situs web Microsoft dan kemudian diinstal di PC Anda.
Pertama, mari kita ubah tabel asli kita menjadi tabel "pintar" dengan memilih perintah Format sebagai tabel tab Beranda (Beranda — Format sebagai Tabel) atau dengan menekan pintasan keyboard Ctrl+T :
Lalu buka tab Data (jika Anda memiliki Excel 2016) atau pada tab Kueri Daya (jika Anda memiliki Excel 2010-2013 dan Anda menginstal Power Query sebagai add-in terpisah) dan klik tombol Dari Tabel / Rentang. :
Tabel cerdas kami dimuat ke editor kueri Power Query, di mana langkah pertama adalah menyiapkan format angka untuk setiap kolom menggunakan dropdown di header tabel:
Untuk menghitung anggaran per hari, Anda perlu menghitung durasi setiap proyek. Untuk melakukan ini, pilih (tahan tombol Ctrl) kolom pertama Finish, Dan kemudian Start dan pilih tim Tambahkan kolom – Tanggal – Kurangi hari (Tambahkan Kolom — Tanggal — Kurangi hari):
Angka yang dihasilkan adalah 1 kurang dari yang diperlukan, karena kita seharusnya memulai setiap proyek pada hari pertama di pagi hari dan selesai pada hari terakhir di malam hari. Oleh karena itu, pilih kolom yang dihasilkan dan tambahkan unit ke dalamnya menggunakan perintah Transform – Standar – Tambah (Ubah — Standar — Tambah):
Sekarang mari kita tambahkan kolom tempat kita menghitung anggaran per hari. Untuk melakukan ini, pada tab Tambah Kolom saya tidak bermain Kolom khusus (Kolom Kustom) dan di jendela yang muncul, masukkan nama bidang baru dan rumus perhitungan, menggunakan nama kolom dari daftar:
Sekarang momen paling halus – kami membuat kolom terhitung lainnya dengan daftar tanggal dari awal hingga akhir dengan langkah 1 hari. Untuk melakukan ini, tekan lagi tombol Kolom khusus (Kolom Kustom) dan menggunakan bahasa Power Query bawaan M, yang disebut Daftar.Tanggal:
Fungsi ini memiliki tiga argumen:
- tanggal mulai – dalam kasus kami, diambil dari kolom Start
- jumlah tanggal yang akan dibuat – dalam kasus kami, ini adalah jumlah hari untuk setiap proyek, yang kami hitung sebelumnya di kolom Pengurangan
- langkah waktu – diatur oleh desain #durasi(1,0,0,0), artinya dalam bahasa M – satu hari, nol jam, nol menit, nol detik.
Setelah mengklik OK kami mendapatkan daftar (Daftar) tanggal, yang dapat diperluas ke baris baru menggunakan tombol di header tabel:
… dan kita mendapatkan:
Sekarang yang tersisa hanyalah menciutkan tabel, menggunakan tanggal yang dihasilkan sebagai nama untuk kolom baru. Tim bertanggung jawab untuk ini. Kolom detail (Kolom Pivot) tab mengubah (Mengubah):
Setelah mengklik OK kami mendapatkan hasil yang sangat dekat dengan yang diinginkan:
Null, dalam hal ini, adalah analog dari sel kosong di Excel.
Tetap menghapus kolom yang tidak perlu dan membongkar tabel yang dihasilkan di sebelah data asli dengan perintah Tutup dan muat – Tutup dan muat di… (Tutup & Muat — Tutup & Muat ke…) tab Beranda (Rumah):
Kami mendapatkan sebagai hasilnya:
Untuk keindahan yang lebih besar, Anda dapat menyesuaikan tampilan tabel pintar yang dihasilkan pada tab Pembina (Rancangan): mengatur gaya warna tunggal, menonaktifkan tombol filter, mengaktifkan total, dll. Selain itu, Anda dapat memilih tabel dengan tanggal dan mengaktifkan penyorotan nomor menggunakan pemformatan bersyarat pada tab Beranda — Pemformatan Bersyarat — Skala Warna (Beranda — Pemformatan Bersyarat — Skala Warna):
Dan bagian terbaiknya adalah di masa mendatang Anda dapat dengan aman mengedit yang lama atau menambahkan proyek baru ke tabel asli, lalu memperbarui tabel yang tepat dengan tanggal dengan tombol kanan mouse – dan Power Query akan mengulangi semua tindakan yang telah kita lakukan secara otomatis .
Voila!
- Bagan Gantt di Excel menggunakan pemformatan bersyarat
- Kalender tonggak proyek
- Membuat Baris Duplikat dengan Power Query