Ian di Nashville mendapatkan data setiap hari dari unduhan sistem. Kolom tanggal berisi Tanggal + Waktu. Ini membuat tabel pivot memiliki beberapa baris per hari, bukan ringkasan satu sel per hari.
Artikel ini menunjukkan tiga cara untuk memecahkan masalah tersebut.
Gambar di bawah ini menunjukkan kolom Tanggal dengan Tanggal plus waktu.
Salah satu solusinya adalah memperbaikinya di tabel pivot.
- Seret bidang Tanggal ke area Baris. Ini muncul dengan Tanggal + Waktu.
- Pilih sel mana saja yang berisi tanggal dan waktu.
- Pada tab Analisis Alat Tabel Pivot, pilih Bidang Grup.
- Dalam dialog Pengelompokan, pilih Hari. Batalkan pilihan Bulan. Saat Anda mengklik OK, tabel pivot akan menampilkan satu baris per hari.
Solusi lain yang membosankan adalah menambahkan kolom baru setelah mengunduh. Seperti yang ditunjukkan pada gambar ini, rumus di kolom J adalah =INT(D2).
Tanggal + waktu adalah hari integer diikuti dengan waktu desimal. Dengan menggunakan fungsi INT untuk mendapatkan bagian integer dari tanggal + waktu, Anda mengisolasi tanggal.
Ini akan berfungsi dengan baik, tetapi sekarang Anda harus menambahkan kolom baru setiap kali Anda mengunduh data.
Solusi pilihan saya adalah menggunakan Power Query. Power Query hanya berfungsi di edisi Windows Excel. Anda tidak dapat menggunakan Power Query di Android, IOS, atau Mac.
Power Query sudah terpasang di Excel 2016. Untuk Excel 2010 dan 2013, Anda bisa mengunduh Power Query dari Microsoft.
Pertama, konversikan data yang diunduh ke Tabel menggunakan Ctrl + T. Kemudian, pada tab Data, pilih Dari Tabel / Rentang.
Di kisi Power Query, pilih kolom Tanggal. Buka tab Transformasi. Buka dropdown Date Type dan pilih Date. Jika Anda mendapatkan pesan tentang Replace Step atau New Step, pilih Replace Step.
Pada tab Beranda Power Query, pilih Tutup dan Muat.
Hasil Anda akan muncul di lembar baru. Buat tabel pivot dari lembar itu.
Lain kali Anda mendapatkan data baru, ikuti langkah-langkah berikut:
- Tempel data baru di atas kumpulan data asli.
- Masuk ke lembar dengan hasil Power Query. Perluas panel Query sehingga Anda dapat melihat Refresh. Klik Segarkan.
- Segarkan tabel pivot.
Menonton video
Transkrip Video
Belajar Excel dari Podcast, Episode 2203: Potong Tanggal dan Waktu menjadi Hanya Kencan.
Hai, selamat datang kembali di netcast, saya Bill Jelen. Pertanyaan hari ini dari Ian di seminar Nashville Power Excel saya. Lihat ini: Ian mendapatkan unduhan dari sistemnya setiap hari, dan memiliki-- di kolom Tanggal-- ada Tanggal dan Waktu, dan itu mengacaukan segalanya karena ketika Ian membuat Tabel Pivot, dan memasukkan Tanggal sisi kiri, bukannya hanya mendapatkan satu baris per Tanggal, dia mendapatkan satu baris untuk setiap Tanggal dan Waktu.
Sekarang, akan mungkin untuk datang ke sini dan berkata, Bidang Grup, dan kelompokkan ini ke Hari, klik OK. Dan, Anda tahu, itu adalah beberapa klik tambahan yang menurut saya tidak perlu kita lakukan. Jadi, untuk Podcast ini, beberapa Podcast berikutnya, kita akan melihat Power Query. Sekarang, ini dibangun ke dalam Excel 2016 di sini, di bawah Dapatkan & Transformasi Data. Jika Anda menjalankan 2010 atau 2013 di Windows, bukan di Mac, Anda bisa mengunduh Power Query secara gratis dari Excel. Dan Power Query ingin mengerjakan Tabel atau Rentang.
Jadi saya akan memilih satu sel data, tekan Ctrl + T untuk Tabel, sempurna. Dan kemudian, pada tab Power Query saya, Data, Dari Tabel / Rentang, saya akan memilih kolom Tanggal dan Transformasi, dan mengatakan bahwa saya ingin ini bukan menjadi Tanggal dan Waktu, tetapi hanya Tanggal. Saya akan memilih Ganti saat ini, lalu Beranda, Tutup & Muat, dan kami mendapatkan formulir lembar baru yang datanya telah dikonversi. Nah, dari sini, tentunya kita bisa meringkas dengan poros dan hasilnya akan sempurna.
Keuntungannya, menurut saya, dari metode ini, Power Query, adalah ketika Ian mendapatkan lebih banyak data… Jadi di sini saya memiliki data baru, saya akan mengambil data baru ini, menyalin data, dan datang ke sini ke data lama saya dan tempel tepat di bawah. Dan, perhatikan bahwa ujung penanda tabel sekarang berada di Baris 474. Saat saya menempel, ujung penanda tabel bergerak ke bawah ke bagian bawah data baru. Baik? Dan kueri ini ditulis berdasarkan tabel. Baik? Jadi, seiring pertumbuhan meja.
Sekarang, satu masalah di sini adalah, jika Anda menggunakan Power Query untuk pertama kalinya, Power Query mulai selebar itu - hanya setengah lebarnya - dan Anda tidak bisa melihat ikon Refresh. Jadi, Anda harus menyeretnya ke sini seperti ini dan mendapatkan ikon Segarkan yang hebat itu, dan cukup klik Segarkan. Saat ini, kami memiliki 473 baris dimuat, dan sekarang 563 baris dimuat. Buat tabel pivot Anda, dan hidup itu menyenangkan.
Sekarang, hei, lihat, saya tahu mungkin saja menambahkan kolom baru di sini, = INT (D2) - --dari tanggal-- salin, salin, dan Tempel Nilai Khusus. Tapi kemudian Anda siap melakukan itu setiap hari. Baik? Jadi meskipun setidaknya ada tiga cara berbeda yang kita bicarakan dalam video ini untuk mengatasinya, bagi saya, Power Query dan, hanya, kemampuan untuk mengklik Refresh, adalah cara yang harus dilakukan.
Power Query, saya membicarakannya di buku baru saya, MrExcel LIVe, 54 Tips Excel Terbaik Sepanjang Masa.
Penutupan Episode hari ini: Ian di Nashville mendapatkan data dari sistem setiap hari, kolom Tanggal secara menjengkelkan memiliki Tanggal plus Waktu-- itu mengacaukan tabel pivotnya, jadi dia harus … Nah, tiga hal yang mungkin: Satu, gunakan fungsi INT, salin, tempel nilai; kedua, biarkan saja sebagai Tanggal dan Waktu lalu kelompokkan tabel pivot berdasarkan Tanggal; atau, solusi yang lebih baik, Power Query, membuat kumpulan data yang diunduh menjadi tabel dengan Ctrl + T, lalu pada tab data, pilih dari tabel, pilih kolom tanggal dan waktu, masuk ke tab transformasi di Power query, konversikan ke Tanggal, Tutup & Muat, buat tabel pivot dari itu. Lain kali Anda mendapatkan data, tempelkan di tabel asli, buka panel Kueri, klik Segarkan, dan Anda siap melakukannya.
Hai, untuk mengunduh buku kerja dari video hari ini, kunjungi URL di deskripsi YouTube.
Saya ingin berterima kasih kepada Ian karena telah hadir dalam seminar saya di Nashville, saya ingin mengucapkan terima kasih telah mampir. Sampai jumpa di lain waktu untuk netcast lain dari.
Unduh File Excel
Untuk mengunduh file excel: date-time-to-date.xlsx
Untuk lebih meningkatkan proses Power Query:
- Pastikan buku kerja unduhan selalu disimpan di folder yang sama dengan nama yang sama.
- Mulai dari buku kerja kosong. Save As dengan nama seperti DownloadedFileTransformed.xlsx
- Dari buku kerja kosong, Data, Dari File, Dari Buku Kerja. Arahkan ke file dari Langkah 1.
- Pilih Bersihkan Data. Ubah Tanggal / Waktu menjadi Tanggal. Tutup & Muat.
- Klik kanan Query Panel dan pilih Properties. Pilih Refresh Data Saat Membuka File.
- Setiap hari, pergilah bekerja. Ambil kopimu. Pastikan file baru untuk # 1 ada di dalam folder.
- Buka buku kerja DownloadFileTransformed.xlsx Anda. Ini akan memiliki data baru dan tanggalnya akan benar.
Pemikiran Excel Hari Ini
Saya telah meminta saran dari teman-teman Master Excel saya tentang Excel. Hari ini pemikiran untuk direnungkan:
"Validasi adalah temanmu."
Duane Aubin
