Contents [show]
Banyak proses perniagaan (dan juga keseluruhan perniagaan) dalam kehidupan ini melibatkan pemenuhan pesanan oleh bilangan pelaksana yang terhad mengikut tarikh akhir yang diberikan. Perancangan dalam kes sedemikian berlaku, seperti yang mereka katakan, "dari kalendar" dan selalunya terdapat keperluan untuk memindahkan acara yang dirancang di dalamnya (pesanan, mesyuarat, penghantaran) ke Microsoft Excel - untuk analisis lanjut dengan formula, jadual pangsi, carta, dan lain-lain.
Sudah tentu, saya ingin melaksanakan pemindahan sedemikian bukan dengan menyalin bodoh (yang tidak sukar), tetapi dengan pengemaskinian automatik data supaya pada masa hadapan semua perubahan yang dibuat pada kalendar dan pesanan baharu dengan cepat akan dipaparkan dalam Excel. Anda boleh melaksanakan import sedemikian dalam beberapa minit menggunakan alat tambah Power Query terbina dalam Microsoft Excel, bermula dari versi 2016 (untuk Excel 2010-2013, ia boleh dimuat turun dari tapak web Microsoft dan dipasang secara berasingan daripada pautan) .
Katakan kita menggunakan Kalendar Google percuma untuk perancangan, di mana saya, untuk kemudahan, mencipta kalendar berasingan (butang dengan tanda tambah di penjuru kanan sebelah bawah di sebelah Kalendar lain) dengan tajuk kerja. Di sini kami memasukkan semua pesanan yang perlu disiapkan dan dihantar kepada pelanggan di alamat mereka:
Dengan mengklik dua kali sebarang pesanan, anda boleh melihat atau mengedit butirannya:
Perhatikan bahawa:
- Nama acara tersebut ialah pengurusyang memenuhi perintah ini (Elena) dan ORDERNUMBER
- Ditunjukkan alamat penghantaran
- Nota itu mengandungi (dalam baris berasingan, tetapi dalam sebarang pesanan) parameter pesanan: jenis pembayaran, jumlah, nama pelanggan, dsb. dalam format Parameter=Nilai.
Untuk kejelasan, pesanan setiap pengurus diserlahkan dalam warna mereka sendiri, walaupun ini tidak perlu.
Langkah 1. Dapatkan pautan ke Kalendar Google
Mula-mula kami perlu mendapatkan pautan web ke kalendar pesanan kami. Untuk melakukan ini, klik pada butang dengan tiga titik Pilihan Kalendar Berfungsi di sebelah nama kalendar dan pilih arahan Tetapan dan Perkongsian:
Dalam tetingkap yang terbuka, anda boleh, jika mahu, menjadikan kalendar awam atau membuka akses kepadanya untuk pengguna individu. Kami juga memerlukan pautan untuk akses peribadi ke kalendar dalam format iCal:
Langkah 2. Muatkan data daripada kalendar ke dalam Power Query
Sekarang buka Excel dan pada tab Tarikh (jika anda mempunyai Excel 2010-2013, kemudian pada tab Kuasa Kuasa) pilih arahan Dari Internet (Data — Dari Internet). Kemudian tampal laluan yang disalin ke kalendar dan klik OK.
Pertanyaan Kuasa iCal tidak mengenali format, tetapi mudah untuk membantu. Pada asasnya, iCal ialah fail teks biasa dengan titik bertindih sebagai pembatas, dan di dalamnya kelihatan seperti ini:
Jadi, anda boleh klik kanan pada ikon fail yang dimuat turun dan pilih format yang paling hampir dengan maksud CSV – dan data kami tentang semua pesanan akan dimuatkan ke dalam editor pertanyaan Power Query dan dibahagikan kepada dua lajur mengikut titik bertindih:
Jika anda melihat dengan teliti, anda boleh melihat dengan jelas bahawa:
- Maklumat tentang setiap acara (order) dikumpulkan ke dalam satu blok bermula dengan perkataan BEGIN dan berakhir dengan END.
- Tarikh mula dan tamat disimpan dalam rentetan berlabel DTSTART dan DTEND.
- Alamat penghantaran ialah LOCATION.
- Nota pesanan – medan DESCRIPTION.
- Nama acara (nama pengurus dan nombor pesanan) — Medan RINGKASAN.
Ia kekal untuk mengekstrak maklumat berguna ini dan mengubahnya menjadi jadual yang mudah.
Langkah 3. Tukar kepada Paparan Biasa
Untuk melakukan ini, lakukan rangkaian tindakan berikut:
- Mari padamkan 7 baris teratas yang kita tidak perlukan sebelum arahan BEGIN pertama Laman Utama — Padam Baris — Padam Baris Teratas (Laman Utama — Alih keluar baris — Alih keluar baris atas).
- Tapis mengikut lajur Column1 baris yang mengandungi medan yang kami perlukan: DTSTART, DTEND, DESKRIPSI, LOKASI dan RINGKASAN.
- Pada tab Lanjutan Menambah lajur memilih Lajur indeks (Tambah lajur — lajur indeks)untuk menambah lajur nombor baris pada data kami.
- Di sana pada tab. Menambah lajur memilih pasukan Lajur bersyarat (Tambah lajur — Lajur bersyarat) dan pada permulaan setiap blok (pesanan) kami memaparkan nilai indeks:
- Isikan sel kosong dalam lajur yang terhasil Menyekatdengan mengklik kanan pada tajuknya dan memilih arahan Isi - Bawah (Isi - Bawah).
- Alih keluar lajur yang tidak perlu indeks.
- Pilih lajur Column1 dan lakukan lilitan data daripada lajur Column2 menggunakan arahan Transform – Lajur Pivot (Ubah — lajur Pivot). Pastikan anda memilih dalam pilihan Jangan agregat (Jangan agregat)supaya tiada fungsi matematik digunakan pada data:
- Dalam jadual dua dimensi (palang) yang terhasil, kosongkan garis miring ke belakang dalam lajur alamat (klik kanan pada pengepala lajur – Menggantikan nilai) dan keluarkan lajur yang tidak diperlukan Menyekat.
- Untuk menghidupkan kandungan lajur DTSTART и DTEND dalam masa tarikh penuh, menyerlahkannya, pilih pada tab Transform – Tarikh – Run Analysis (Transform — Tarikh — Parse). Kemudian kami membetulkan kod dalam bar formula dengan menggantikan fungsi Tarikh dari on TarikhMasa.Daripadasupaya tidak kehilangan nilai masa:
- Kemudian, dengan mengklik kanan pada pengepala, kami membahagi lajur DESCRIPTION dengan parameter pesanan mengikut pemisah – simbol n, tetapi pada masa yang sama, dalam parameter, kami akan memilih pembahagian ke dalam baris, dan bukan ke dalam lajur:
- Sekali lagi, kami membahagikan lajur yang terhasil kepada dua yang berasingan - parameter dan nilai, tetapi dengan tanda sama.
- Memilih lajur HURAIAN.1 melakukan lilitan, seperti yang kita lakukan sebelum ini, dengan arahan Transform – Lajur Pivot (Ubah — lajur Pivot). Lajur nilai dalam kes ini akan menjadi lajur dengan nilai parameter - HURAIAN.2 Pastikan anda memilih fungsi dalam parameter Jangan agregat (Jangan agregat):
- Ia kekal untuk menetapkan format untuk semua lajur dan menamakan semula mereka seperti yang dikehendaki. Dan anda boleh memuat naik hasil kembali ke Excel dengan arahan Laman Utama — Tutup dan Muatkan — Tutup dan Muatkan dalam… (Laman Utama — Tutup&Muatkan — Tutup&Muat ke…)
Dan berikut ialah senarai pesanan kami yang dimuatkan ke dalam Excel daripada Kalendar Google:
Pada masa hadapan, apabila menukar atau menambah pesanan baharu pada kalendar, ia hanya mencukupi untuk mengemas kini permintaan kami dengan arahan Data – Muat semula Semua (Data — Muat semula Semua).
- Kalendar kilang dalam Excel dikemas kini daripada internet melalui Power Query
- Mengubah lajur menjadi jadual
- Buat pangkalan data dalam Excel