Lacak Kemajuan Menuju Tujuan - Artikel TechTV

Dengan tahun baru yang akan datang, Anda mungkin ingin melacak kemajuan menuju suatu tujuan. Bagan keren di Excel ini berisi elemen yang menunjukkan apakah Anda berada di jalur yang tepat untuk mencapai tujuan. Katakanlah Anda ingin berlari sejauh 250 mil kuartal ini.

Garis hitam adalah tujuannya
Garis biru adalah garis "di jalur" - Anda harus berlari 2,7 mil per hari untuk mencapai tujuan
Garis hijau adalah jarak tempuh sebenarnya
Garis merah adalah prediksi di mana Anda akan menyelesaikan kuartal tersebut, berdasarkan kemajuan Anda sejauh ini. Garis merah sejauh ini merupakan bagian paling keren dari grafik.

Berikut adalah cara membuat grafik.

Masukkan judul ini di bagian atas lembar kerja. Di sel A5, masukkan tanggal pertama untuk bagan.
Letakkan cellpointer di A5. Anda akan melihat bahwa ada titik persegi di sudut kanan bawah A5. Ini adalah pegangan isian. Dengan mouse, ambil gagang isian dan seret ke bawah. Perhatikan tooltip sampai Anda melihat bahwa Anda telah menyeret cukup jauh untuk memasukkan tanggal terakhir kuartal.
Rumus untuk B2 adalah =COUNT(A5:A200). Ini akan menghitung jumlah hari dalam rentang Anda.
Rumus untuk B3 adalah =B1/B2. Ini menghitung seberapa jauh Anda harus berlari setiap hari untuk mencapai tujuan.
Gunakan kolom B untuk memasukkan seberapa jauh Anda berlari setiap hari.
Meskipun kolom B berisi data Anda yang sebenarnya, itu tidak cocok untuk membuat grafik. Anda benar-benar akan membangun rentang bagan yang benar-benar baru di kolom C: F yang akan berisi elemen yang dibutuhkan untuk bagan.
Kolom C akan berisi tanggal. Jika satu sumbu rentang bagan Anda berisi angka atau tanggal, Anda harus mengosongkan sel kiri atas rentang bagan.
Ini adalah bug aneh di Excel - membiarkan sel itu kosong akan memungkinkan Intellisense Excel mengenali rentang grafik nanti. Jadi, biarkan sel C5 kosong, dan masukkan heading untuk Distance, Track, dan Goal di D5: F5.
Rumus di C5 adalah =A5.
Pilih sel C5. Dari menu, pilih Format Cells. Pada tab Numerik, pilih kategori Tanggal, lalu format 3/14.
Pilih sel C5. Seret gagang isian ke bawah untuk menyalin rumus ini. Tarik ke bawah sejauh Anda memiliki data di kolom A.
Untuk kolom D & E, rumus di baris 5 akan berbeda dengan rumus di baris 6 & di bawahnya. Rumus di baris 5 akan memberikan awal numerik. Rumus di baris 6 & di bawah ini akan menambah nilai numerik di baris 5. Rumus untuk D5 adalah =B5- ini hanya akan menyalin jarak hari pertama ke kisaran grafik.
Di langkah selanjutnya, Excel akan menambahkan garis tren berdasarkan nilai di kolom D. Jika Anda hanya menyalin D5 ke bawah, Anda akan berakhir dengan nol untuk tanggal mendatang. Angka nol akan membatalkan garis tren. Triknya adalah Anda perlu mengisi tanggal mendatang dengan nilai kesalahan # N / A.
Dalam bahasa Inggris, rumus pada dasarnya mengatakan, "Jika kita menjalankan hari ini, tambahkan nilai hari ini ke total kemarin. Jika tidak, masukkan N / A di sel ini.

Untuk mencapai hasil ini, masuk =IF(NOT(ISBLANK(B6)),D5+B6,NA())ke sel D6.
Pilih sel D6. Klik dua kali gagang isian untuk menyalin rumus ini ke bawah. Trik klik dua kali berfungsi karena kolom di sebelah kiri sel ini berisi nilai yang akan memandu Excel sejauh mana rumus perlu disalin.
Rumus dalam E5 perlu menyalin per hari dari B3. Masukkan =B3E5.
Rumus sisa E perlu menambahkan B3 ke hari sebelumnya. Rumus untuk E6 bisa jadi =E5+B3tetapi rumus ini tidak akan berfungsi saat Anda menyalin sel ke baris lain. Anda selalu ingin rumus menambahkan B3 ke sel di atas.
Jadi, gunakan =E5+$B$3di E6. Tanda dolar memastikan bahwa saat Anda menyalin rumus, rumus akan selalu mengarah ke B3.
Pindahkan cellpointer kembali ke E6 dan klik dua kali pegangan isian untuk menyalin rumus ke bawah.
Rumus untuk semua kolom F adalah =$B$1. Kisaran ini akan digunakan untuk menggambar garis gawang hitam pada grafik. Berikut tip untuk memasukkan rumus ini. Mulai di F5. Ketik tanda sama dengan. Menggunakan mouse, sentuh B1. Kemudian, ketik tombol F4 untuk memasukkan tanda dolar.
Tekan Ctrl + Enter untuk menerima rumus dan tetap di sel saat ini. Anda sekarang dapat mengklik ganda gagang isian untuk menyalin rumus ke bawah.
Akhirnya, Anda siap membuat bagan. Pilih kisaran C4: F94. Gunakan Sisipkan - Bagan dari menu, atau klik ikon Wisaya Bagan di toolbar standar.
Pada langkah 1, pilih diagram Garis.
Bagan akan membutuhkan banyak penyesuaian, tetapi tidak ada yang terjadi di wizard, jadi cukup klik Selesai untuk membuat bagan yang tampak mengerikan ini.
Klik kanan pada background abu-abu dan pilih Format Plot Area.
Pilih Efek Isi.
Siapkan gradien 2 warna seperti yang ditunjukkan di sini.
Klik kanan garis rangkaian Sasaran kuning. Pilih Format Seri Data.
Pilih garis biru tebal untuk rangkaian gawang.
Ulangi langkah 23-24 untuk mengubah garis trek menjadi garis hitam tipis.
Sulit untuk memformat deret Jarak, karena hanya ada 2 titik dan hampir tidak mungkin untuk dilihat. Jika sulit untuk mengklik kanan elemen grafik, Anda dapat menggunakan menu dropdown di toolbar Charting.
Setelah rangkaian jarak dipilih, gunakan ikon Properti untuk membuka dialog Format untuk rangkaian yang dipilih.
Tambahkan Marker ke dialog ini. Ubah warna menjadi biru tua dan ketebalan garis menjadi medium.
Untuk menambahkan garis tren, Anda harus bisa mengklik kanan pada rangkaian jarak. Ini hampir tidak mungkin dilakukan pada saat ini. Jadi - Saya menyarankan agar Anda "curang". Masukkan titik data palsu 20 di sel B7. Ini akan memungkinkan Anda untuk mengklik kanan pada deret jarak. Pilih Tambahkan Garis Tren.
Pada dialog Garis Tren, pilih Garis Tren Linear.
Pilih sel B7 dan ketik tombol Delete untuk menghapus nilai palsu di sana. Anda sekarang akan memiliki garis tren yang digambar sebagai garis hitam tebal.
Klik kanan garis tren dan pilih Format Garis Tren.
Ubah Trendline menjadi garis merah putus-putus dengan ketebalan tipis.
Pada tab Opsi dari Format Garis Tren, ubah nama seri menjadi Prediksi

Anda sekarang memiliki grafik yang lengkap. Jika Anda melewatkan satu hari berlari, masukkan nol di kolom B. Jika Anda melewatkan beberapa hari, garis prediksi merah akan menunjukkan bahwa Anda tidak berada di jalur yang benar untuk mencapai tujuan.