Konten
Banyak proses bisnis (dan bahkan seluruh bisnis) dalam kehidupan ini melibatkan pemenuhan pesanan oleh sejumlah pemain terbatas pada tenggat waktu tertentu. Perencanaan dalam kasus seperti itu terjadi, seperti yang mereka katakan, "dari kalender" dan seringkali ada kebutuhan untuk mentransfer acara yang direncanakan di dalamnya (pesanan, rapat, pengiriman) ke Microsoft Excel - untuk analisis lebih lanjut dengan rumus, tabel pivot, bagan, dll.
Tentu saja, saya ingin menerapkan transfer semacam itu bukan dengan penyalinan bodoh (yang tidak sulit), tetapi dengan pembaruan data otomatis sehingga di masa mendatang semua perubahan yang dibuat pada kalender dan pesanan baru dengan cepat akan ditampilkan di Unggul. Anda dapat menerapkan impor semacam itu dalam hitungan menit menggunakan add-in Power Query yang ada di dalam Microsoft Excel, mulai dari versi 2016 (untuk Excel 2010-2013, dapat diunduh dari situs web Microsoft dan diinstal secara terpisah dari tautan) .
Misalkan kita menggunakan Kalender Google gratis untuk perencanaan, di mana saya, untuk kenyamanan, membuat kalender terpisah (tombol dengan tanda plus di sudut kanan bawah di sebelah Kalender lainnya) dengan judul Kerja. Di sini kami memasukkan semua pesanan yang perlu diselesaikan dan dikirim ke pelanggan di alamat mereka:
Dengan mengklik dua kali pesanan apa pun, Anda dapat melihat atau mengedit detailnya:
Perhatikan bahwa:
- Nama acaranya manajeryang memenuhi pesanan ini (Elena) dan NOMORPESANAN
- Diindikasikan alamat pengiriman
- Catatan berisi (dalam baris terpisah, tetapi dalam urutan apa pun) parameter pesanan: jenis pembayaran, jumlah, nama pelanggan, dll. dalam format Parameter=Nilai.
Untuk kejelasan, pesanan masing-masing manajer disorot dengan warna mereka sendiri, meskipun ini tidak perlu.
Langkah 1. Dapatkan tautan ke Google Kalender
Pertama kita perlu mendapatkan link web ke kalender pesanan kita. Untuk melakukan ini, klik tombol dengan tiga titik Opsi Kalender Bekerja di sebelah nama kalender dan pilih perintah Pengaturan dan Berbagi:
Di jendela yang terbuka, Anda dapat, jika diinginkan, menjadikan kalender publik atau membuka aksesnya untuk pengguna individu. Kami juga memerlukan tautan untuk akses pribadi ke kalender dalam format iCal:
Langkah 2. Muat data dari kalender ke Power Query
Sekarang buka Excel dan pada tab Data (jika Anda memiliki Excel 2010-2013, maka pada tab Kueri Daya) pilih perintah Dari internet (Data — Dari Internet). Kemudian tempel jalur yang disalin ke kalender dan klik OK.
iCal Power Query tidak mengenali formatnya, tetapi mudah untuk membantu. Pada dasarnya, iCal adalah file teks biasa dengan titik dua sebagai pembatas, dan di dalamnya terlihat seperti ini:
Jadi Anda bisa klik kanan pada ikon file yang diunduh dan pilih format yang paling dekat artinya CSV – dan data kami tentang semua pesanan akan dimuat ke editor kueri Power Query dan dibagi menjadi dua kolom dengan titik dua:
Jika Anda melihat lebih dekat, Anda dapat dengan jelas melihat bahwa:
- Informasi tentang setiap peristiwa (urutan) dikelompokkan ke dalam blok yang dimulai dengan kata BEGIN dan diakhiri dengan END.
- Tanggal mulai dan berakhir disimpan dalam string berlabel DTSTART dan DTEND.
- Alamat pengiriman adalah LOKASI.
- Catatan pesanan – kolom DESKRIPSI.
- Nama acara (nama manajer dan nomor pesanan) — bidang RINGKASAN.
Tetap mengekstrak informasi yang berguna ini dan mengubahnya menjadi tabel yang nyaman.
Langkah 3. Ubah ke Tampilan Normal
Untuk melakukannya, lakukan rangkaian tindakan berikut:
- Mari kita hapus 7 baris teratas yang tidak kita perlukan sebelum perintah BEGIN pertama Beranda — Hapus Baris — Hapus Baris Teratas (Beranda — Hapus baris — Hapus baris teratas).
- Filter menurut kolom Column1 baris yang berisi bidang yang kita butuhkan: DTSTART, DTEND, DESCRIPTION, LOCATION dan SUMMARY.
- Pada tab Lanjutan Menambahkan kolom pilih kolom indeks (Tambahkan kolom — Kolom indeks)untuk menambahkan kolom nomor baris ke data kami.
- Di sana di tab. Menambahkan kolom pilih tim kolom bersyarat (Tambahkan kolom — Kolom bersyarat) dan di awal setiap blok (order) kami menampilkan nilai indeks:
- Isi sel kosong di kolom yang dihasilkan Memblokirdengan mengklik kanan pada judulnya dan memilih perintah Mengisi (Mengisi).
- Hapus kolom yang tidak perlu Indeks.
- Pilih kolom Column1 dan melakukan konvolusi data dari kolom Column2 menggunakan perintah Transform – Kolom Pivot (Transformasi — Kolom pivot). Pastikan untuk memilih dalam opsi Jangan agregat (Jangan agregat)sehingga tidak ada fungsi matematika yang diterapkan pada data:
- Pada tabel dua dimensi (silang) yang dihasilkan, hapus garis miring terbalik di kolom alamat (klik kanan pada header kolom – Mengganti nilai) dan hapus kolom yang tidak perlu Memblokir.
- Untuk mengubah isi kolom MULAI DT и DTEND dalam waktu tanggal penuh, sorot mereka, pilih pada tab Transform – Tanggal – Jalankan Analisis (Transformasi — Tanggal — Parse). Kemudian kami memperbaiki kode di bilah rumus dengan mengganti fungsinya Tanggal.Dari on TanggalWaktu.Dariagar tidak kehilangan nilai waktu:
- Kemudian, dengan mengklik kanan pada header, kami membagi kolom DESKRIPSI dengan parameter urutan dengan pemisah – simbol n, tetapi pada saat yang sama, dalam parameter, kami akan memilih pembagian menjadi baris, dan bukan menjadi kolom:
- Sekali lagi, kami membagi kolom yang dihasilkan menjadi dua yang terpisah – parameter dan nilai, tetapi dengan tanda sama dengan.
- Memilih kolom DESKRIPSI.1 lakukan konvolusi, seperti yang kita lakukan sebelumnya, dengan perintah Transform – Kolom Pivot (Transformasi — Kolom pivot). Kolom nilai dalam hal ini akan menjadi kolom dengan nilai parameter DESKRIPSI.2 Pastikan untuk memilih fungsi dalam parameter Jangan agregat (Jangan agregat):
- Tetap mengatur format untuk semua kolom dan mengganti namanya sesuai keinginan. Dan Anda dapat mengunggah hasilnya kembali ke Excel dengan perintah Beranda — Tutup dan Muat — Tutup dan Muat di… (Beranda — Tutup&Muat — Tutup&Muat ke…)
Dan inilah daftar pesanan kami yang dimuat ke Excel dari Kalender Google:
Di masa depan, ketika mengubah atau menambahkan pesanan baru ke kalender, itu hanya akan cukup untuk memperbarui permintaan kami dengan perintah Data – Segarkan Semua (Data — Segarkan Semua).
- Kalender pabrik di Excel diperbarui dari internet melalui Power Query
- Mengubah kolom menjadi tabel
- Buat database di Excel