Membagikan
13 Januari 2022
Ada dua cara untuk membuat Excel secara otomatis me-refresh koneksi data dan menghitung ulang lembar kerja. Trik ini penting untuk jenis data Saham dan Mata Uang di Excel 365, tetapi juga untuk situasi lainnya
Biasanya Excel akan memperbarui sendiri saat Anda mengubah nilai sel. Hari-hari ini ada situasi di mana sel mengubah nilai tetapi Excel TIDAK memperbarui lembar kerja. Dengan kata lain, Excel modern telah berubah dengan cara yang belum diadaptasi sepenuhnya oleh Microsoft
Idealnya, Excel akan memiliki pengaturan keseluruhan untuk menyegarkan lembar kerja setiap 'n' detik atau menit. Pada tahun 2022, Microsoft akhirnya menambahkan beberapa opsi penyegaran otomatis Stok/Mata Uang tetapi tidak lengkap dan terbatas
Artikel ini memiliki DUA cara berbeda untuk memaksa pembaruan di Excel. Salah satunya adalah pendekatan makro 'tradisional', yang lainnya adalah trik yang lebih baru menggunakan PowerQuery bersamaan dengan makro tunggal
Opsi mana pun memberi Anda lebih banyak kontrol untuk memperbarui daripada penambahan 'centang kotak' Microsoft yang sederhana ke Excel 365
Jenis Data Stok
Dengan Jenis Data Saham, kemampuan untuk memperbarui secara otomatis menjadi lebih penting. Pengguna akan menginginkan lembar kerja mereka mengambil harga terbaru secara otomatis, sesuatu yang tidak dapat dilakukan oleh rilis pratinjau saat ini. Alih-alih memiliki 'ticker' otomatis yang bagus, kami diharapkan mengklik 'Refresh' untuk mendapatkan harga terbaru
Saham, Mata Uang, dan tipe data tertaut lainnya adalah binatang yang aneh. Itu adalah koneksi data ke sumber eksternal tetapi TIDAK muncul sebagai Koneksi Data Excel. Itu berarti Anda tidak dapat menyiapkan penyegaran data otomatis, seperti yang Anda lakukan dengan koneksi data normal. Faktanya, tidak ada kontrol terbuka untuk Saham, Mata Uang, atau tipe data tertaut lainnya
SEKARANG [] dan fungsi volatil lainnya
Fungsi SEKARANG [] memperbarui ke tanggal dan waktu terbaru setiap kali Excel menghitung ulang lembar kerja. Tetapi jika tidak ada yang membuat hal itu terjadi, Now[] tidak mengubah nilainya. Beberapa faktor eksternal diperlukan untuk membuat Excel memperbarui Now[] dan lembar kerja lainnya. Dengan kata lain, Anda harus dapat melirik lembar kerja dan mengetahuinya hingga detik, tetapi itu tidak mungkin dengan Excel 'di luar kotak'
Microsoft menyebut NOW[] dan fungsi serupa 'volatile' karena nilainya dapat berubah meskipun tidak ada sel lain yang berubah. Fungsi volatile lainnya adalah Today[], Randbetween[], Offset[] dan Indirect[]. Dalam beberapa situasi Info[], Cell[] dan SumIf[] juga bisa berubah-ubah
Fungsi kustom VBA juga dapat ditandai sebagai volatile menggunakan baris ini dalam kode fungsi
Application.Volatile
Baris itu membuat fungsi berjalan kapan saja Excel memperbarui/menghitung ulang lembar kerja
Caranya adalah dengan membuat makro Excel yang memaksa koneksi data untuk diperbarui lalu tandai fungsinya sebagai Volatile sehingga akan dijalankan setiap kali buku kerja diperbarui
Perhatian ekstra
Mungkin Anda ingin lembar kerja Anda diperbarui secara otomatis sebagai tindakan pencegahan?
Kode pembaruan otomatis
Metode standar untuk memaksa pembaruan otomatis Excel adalah cuplikan singkat kode VBA. Inilah yang kami gunakan, ada banyak variasi pada tema yang sama. Kode lengkap ada di bagian bawah artikel
Ada tiga fungsi
RefreshAllDataConn
apakah penyegaran koneksi data yang sebenarnya [ Workbooks[ThisWorkbook. Nama]. RefreshAll ] dan kami menambahkan dua, opsional, baris untuk menampilkan terakhir kali disegarkan di bilah status bawah
Jika Anda ingin ekstra hati-hati, tambahkan baris untuk memaksa penghitungan ulang secara eksplisit. Baik ActiveSheet. Hitung atau Aplikasi ekstrim. Hitung Penuh [gunakan dengan hemat, ini akan memperlambat lembar kerja yang besar]
Penyegaran Otomatis
jalankan sub RefreshAllDataConn setiap menit atau nilai apa pun yang Anda tetapkan pada Aplikasi baris. Tepat Waktu Sekarang + Nilai Waktu[“00. 01. 00"], "Penyegaran Otomatis"
Workbook_Open
fungsi Excel bawaan yang berjalan secara otomatis saat lembar kerja dibuka. Dalam hal ini akan memulai Penyegaran Otomatis
Solusi koneksi data PowerQuery
Kerugian dari pendekatan VBA adalah bahwa a. lembar kerja xlsm diperlukan [lembar kerja Excel berkemampuan makro]. Mungkin ada masalah berbagi file yang diaktifkan makro karena masalah keamanan
Kehadiran PowerQuery / Get and Transform berarti ada cara lain untuk memaksa penghitungan ulang lembar kerja. Ini solusi dan tidak sempurna, tetapi mungkin dan tidak memerlukan lembar kerja yang mendukung makro
Singkatnya, pastikan ada penyiapan kueri data dengan penyegaran otomatis. Jika tidak ada koneksi data, tambahkan yang kecil ke lembar kerja
Setelah Anda memiliki kueri penyegaran otomatis, lembar kerja yang menyertakan fungsi volatil apa pun juga harus disegarkan
Idealnya tipe data Stock dan Geo juga harus disegarkan
Kueri data Excel apa pun dilengkapi dengan beberapa opsi penyegaran di properti Kueri. Kebanyakan dari mereka default OFF
Segarkan setiap nnn menit – default nonaktif dengan saran 60 menit
Segarkan data saat membuka file
Aktifkan penyegaran latar belakang
Refresh koneksi ini di Refresh All
Solusi penyegaran otomatis adalah membuat koneksi data yang kecil dan praktis tidak signifikan. Kemudian konfigurasikan koneksi data itu untuk memperbarui setiap menit atau kapan pun Anda mau. Itu akan memaksa lembar kerja untuk memperbarui termasuk fungsi volatil yang disebutkan di atas
Beberapa versi solusi ini menambahkan tautan ke file csv kecil di komputer yang sama. Kami mendapatkan hasil yang sama menggunakan koneksi data dari tabel di lembar kerja
Buat tabel kecil dengan satu sel. Sel dapat memiliki apa saja tetapi kami membuat sel dengan SEKARANG [] di dalamnya, untuk alasan yang akan kami jelaskan nanti
Pilih tabel lalu pilih Data. Dapatkan Data. Dari sumber lain. dari Tabel/Rentang. Item menu yang tepat mungkin berbeda tergantung pada versi Excel Anda
Saat Editor Kueri terbuka, Tutup saja dan Muat. Di panel koneksi data, Anda akan melihat kueri
Klik kanan kueri, pilih properti untuk melihat pengaturan yang kita perlukan
Tetapkan kecepatan penyegaran yang cocok untuk Anda
Agar rapi, kami memindahkan tabel sumber [kanan] ke lembar yang sama di kueri yang dimuat [kiri]. Karena kami menggunakan SEKARANG [] di sel tabel sumber, akan mudah untuk melihat kapan/jika lembar kerja telah disegarkan
Contoh Kode VBA
Sub RefreshAllDataConn[] ' Refresh all Data Connections. ' This should include Stock and Currency data types, even though they aren't listed. Workbooks[ThisWorkbook.Name].RefreshAll ' Show update time on status bar to confirm. ' comment these lines out if not needed. Application.DisplayStatusBar = True Application.StatusBar = "Refreshed at: " & Now[] End Sub Sub AutoRefresh[] ' to run a Refresh All on the workbook every n minutes RefreshAllDataConn ' Repeat every minute or change to whatever value you prefer. Application.OnTime Now + TimeValue["00:01:00"], "AutoRefresh" ' this is a simple example. There's no coded way to exit this function. End Sub Private Sub Workbook_Open[] ' Starts the automatic refresh when the workbook is opened, ' commented out as a precaution. ' AutoRefresh End Sub_
Data Saham / Mata Uang Excel [agak, agak] mendapat penyegaran otomatis
Buat daftar lembar kerja Excel otomatis atau daftar isi
Selesaikan solusi Excel NetworkDays[] dengan hari libur & liburan
Bergabunglah dengan Office for Mere Mortals hari ini
Office for Mere Mortals adalah tempat ribuan orang mendapatkan tips dan trik berguna untuk Word, Excel, PowerPoint, dan Outlook
Cobalah. Anda dapat berhenti berlangganan kapan saja. Kami tidak pernah mengirim spam atau menjual alamat sejak kami memulai lebih dari dua puluh tahun yang lalu
Bagaimana cara menyegarkan tabel pivot di Aplikasi Seluler Excel?
Mengapa saya tidak bisa me-refresh Data di Excel?
Bisakah Anda melacak perubahan di aplikasi Excel?