Jejak Perubahan dalam Sel Formula - Petua Excel

Jejak Perubahan dalam Sel Formula Excel. Bolehkah anda menunjukkan item mana yang baru berubah akibat menukar sel input tertentu?

Tonton Video

Perubahan trek di Excel agak pelik.
Matlamatnya adalah untuk mengesan sel formula apa yang berubah di Excel.
Simpan Sebagai untuk menyimpan buku kerja sebagai XLSM.
Tukar Keselamatan Makro.
Catat makro untuk mengetahui kod untuk menetapkan pemformatan bersyarat untuk nombor yang tidak sama dengan 2.
Pilih Pemformatan yang anda mahukan.
Rakam makro lain untuk mengetahui cara membuang CF dari lembaran kerja.
Dalam makro, tambahkan gelung untuk setiap lembaran kerja.
Tambahkan pernyataan IF untuk mengelakkannya berjalan di Tajuk.
Tambahkan gelung untuk memeriksa setiap sel formula.
Tambahkan Pemformatan Bersyarat untuk melihat apakah nilai sel pada masa makro berjalan.
Kembali ke Excel.
Tambah bentuk. Tetapkan makro ke bentuknya.
Klik Bentuk untuk Menjalankan Makro.
Petua Bonus: Menyeret modul VBA ke buku kerja baru.

Transkrip Video

Belajar Excel dari Podcast, Episode 2059: Perubahan Jejak Excel (dalam keputusan Formula)

Hei, selamat datang kembali ke netcast, saya Bill Jelen. Soalan hari ini dihantar dari Montreal mengenai perubahan trek. Jejak perubahan, baiklah. Jadi inilah yang kita ada. Kami mempunyai 4 Sel Input, dan sejumlah besar sel Formula yang bergantung pada Sel Input ini. Dan jika saya menghidupkan, saya akan kembali ke tab Ulasan, aktifkan Perubahan Sorotan, Jejak perubahan semasa mengedit, klik OK, baiklah. Dan mereka memberi amaran kepada saya bahawa mereka harus menyimpan buku kerja dan makro itu tidak boleh digunakan dalam buku kerja bersama. Anda tahu itu? Inilah masalahnya apabila anda mengesan perubahan, mereka berkongsi buku kerja dan terdapat banyak perkara yang tidak dapat berlaku dalam buku kerja bersama, anda tahu, seperti makro dan banyak perkara lain. Tetapi mari kita lihat bagaimana perubahan trek berfungsi di Excel hari ini.

Mari ambil ini dan ubah dari 2 hingga 22, dan ambil 4 ini dan ubah dari 4 hingga 44. Baiklah, dan anda lihat, apa yang mereka perhatikan dalam perubahan trek adalah bahawa kedua sel ini berubah, baiklah, segitiga ungu trek sebenar berubah. Semua benda merah ini, itu tidak berlaku tetapi saya hanya menggambarkan bahawa semua sel merah ini berubah dan perubahan jejak tidak mengatakan apa-apa mengenai perubahan ini, baiklah? Oleh itu, hanya dikatakan, kedua sel ini telah diubah tetapi semua sel lain juga telah diubah. Oleh itu, persoalan dari Montreal adalah, apakah ada cara untuk membuat perubahan trek yang sebenarnya menunjukkan kepada kita semua yang berubah, bukan hanya sel input ini yang berubah?

Baiklah, jadi, perkara pertama yang harus kita lakukan adalah mematikan Track Perubahan Excel terbina dalam. Dan kemudian, adakah cara yang kita dapat- kita dapat membina sistem perubahan trek kita sendiri yang akan membolehkan kita melihat semua sel formula yang berubah? Baiklah, jadi Langkah 1 dan langkah ini adalah langkah yang paling penting, jangan ketinggalan. Lihat fail anda, fail anda dipanggil sesuatu XLSX, anda harus menyimpannya: Fail, Simpan Sebagai, Sebagai buku kerja yang diaktifkan makro, atau tidak ada yang berfungsi. Anda mesti klik kanan, Sesuaikan Pita, hidupkan Pembangun, sebaik sahaja sampai ke Pembangun, pergi ke Keamanan Makro, ubah dari tetapan ini - yang mengatakan bahawa kita tidak akan membiarkan makro berjalan atau bahkan tidak akan memberitahu anda bahawa mereka ada di tetapan ini. Anda mesti melakukan dua langkah tersebut. Saya sudah melakukan dua langkah itu. Saya hidup setiap hari dengan dua langkah itu.Sudah diperbaiki, tetapi jika anda baru menggunakan makro, ini baru bagi anda. Dan kemudian, kita perlu mengetahui jenis format yang anda mahukan. Baiklah, jadi saya hanya akan memilih beberapa sel di sini, saya akan Merakam Makro yang dipanggil HowToCFRed, saya tidak akan memberikan kunci pintasan kerana ini tidak akan dapat dijalankan lagi. Saya hanya merakam kod untuk mengetahui bagaimana pemformatan bersyarat berfungsi. Dan kita akan masuk ke Laman Utama, Pemformatan Bersyarat, Sorotan Sel yang tidak sama dengan - Jadi, Lebih Banyak Peraturan, Format sel tidak sama dengan - Lihat itu? Ini bukan dalam drop-down asal tetapi jika anda masuk ke sini, tidak sama dengan 2, dan kemudian pilih formatnya. Ini adalah bahagian yang penting. Oleh itu, saya akan memilih latar belakang merah. Anda memilih warna apa sahaja yang anda mahukan di sini, okey? Malah pergi ke Lebih Banyak Warna, pilih warna merah lain,masuk ke Custom, pilih warna merah lain, okey? Itulah keindahan Perekam Makro, mereka akan memberi kami warna merah yang sempurna untuk anda atau biru atau apa sahaja yang anda mahukan. Baiklah, klik OK. Dan kemudian, kita akan berhenti merakam, baiklah. Sekali lagi, intinya adalah untuk melihat apakah kod itu untuk format bersyarat.

Saya akan pergi ke Makro, Bagaimana format bersyarat merah, dan edit. Baiklah, inilah bahagian penting kod ini. Saya dapat melihat mereka menambah format bersyarat menggunakan xlNotEqual dan kita sukar memetiknya agar tidak sama dengan 2. Dan kemudian kita mengubah bahagian dalam sel menjadi warna itu.

Baiklah, saya juga perlu memikirkan cara menghapus semua format bersyarat pada helaian. Jadi, kembali ke Excel, Rakam Makro lain, Cara menghapus semua bersyarat, OK. Datang ke sini ke tab Laman Utama, pergi ke Pemformatan Bersyarat, Hapus Peraturan dari keseluruhan helaian, Hentikan Rakaman dan kami akan melihat kod tersebut. Hebat, ini adalah makro satu baris. Dan saya suka di sini bahawa cara mereka melakukannya untuk keseluruhan helaian adalah merujuk kepada sel. Jadi dengan kata lain, semua sel pada helaian aktif.

Sekarang, saya perlu membuat makro ini, makro yang dirakam, sedikit lebih generik. Dan saya telah menulis banyak buku mengenai cara melakukan VBA di Excel dan saya telah membuat video mengenai cara melakukan VBA di Excel, dan inilah perkara mudah: anda perlu dapat merakam makro seperti ini tetapi kemudian, tambahkan kira-kira lima atau enam baris untuk menjadikan makro cukup generik.

Dan saya akan bercakap mengenai garis itu, baiklah. Jadi perkara pertama yang ingin saya lakukan ialah saya ingin mengatakan, saya ingin membaca buku kerja aktif, melalui semua lembaran kerja. Jadi untuk setiap lembaran kerja, WS adalah pemboleh ubah objek, saya akan meneliti semua lembaran kerja. Dan orang dari Montreal itu berkata, "Hei, ada satu lembaran yang saya tidak mahu ini berlaku." Jadi, jika Nama WS., dengan nama titik lembaran kerja, tidak sama dengan Tajuk maka kita akan melakukan kod dalam makro. Inilah nama helaian: .Cells.FormatConditions.Delete. Jadi, kita akan membahas setiap individu helaian kecuali tajuk dan menghapus semua syarat format, kemudian kita akan melalui setiap sel dalam helaian tetapi tidak semua sel, hanya sel yang mempunyai formula . Sekiranya ia tidak mempunyai formula maka saya tidakTidak perlu memformatnya kerana ia tidak akan berubah. Cell.FormatConditions.Add, ini langsung dari makro walaupun makro yang direkodkan mengatakan Selection - Saya tidak mahu memilihnya jadi saya hanya akan mengatakan Cell, itulah setiap sel individu. Kami akan menggunakan xlNotEqual dan bukannya Formula: = ”=” 2 yang merupakan kod yang direkodkan di sana, saya telah menggabungkan apa sahaja yang ada di dalam sel itu. Oleh itu, periksa untuk melihat sama ada ia tidak sama dengan nilai semasa. Jadi jika sel pada masa ini mempunyai 2, kita katakan tidak sama dengan 2. Jika sel itu sekarang mempunyai 16.5, kita katakan tidak sama dengan 16.5. Dan selebihnya ini hanyalah makro direkod lurus, makro direkodkan, makro direkodkan, makro direkodkan. Semua itu adalah dari makro yang dirakam. Selesaikan ini Jika dengan Akhir Jika. Selesaikan Ini Untuk dengan WS Seterusnya. tidak akan berubah. Cell.FormatConditions.Tambah, ini langsung dari makro walaupun makro yang direkodkan mengatakan Selection - Saya tidak mahu memilihnya jadi saya hanya akan mengatakan Cell, itu setiap sel individu. Kami akan menggunakan xlNotEqual dan bukannya Formula: = ”=” 2 yang merupakan kod yang direkodkan di sana, saya telah menggabungkan apa sahaja yang ada di dalam sel itu. Oleh itu, periksa untuk melihat sama ada ia tidak sama dengan nilai semasa. Oleh itu, jika sel pada masa ini mempunyai 2, kita katakan tidak sama dengan 2. Jika sel itu sekarang mempunyai 16.5, kita katakan tidak sama dengan 16.5. Dan selebihnya ini hanyalah makro direkod lurus, makro direkodkan, makro direkodkan, makro direkodkan. Semua itu adalah dari makro yang dirakam. Selesaikan ini Jika dengan Akhir Jika. Selesaikan Ini Untuk dengan WS Seterusnya.tidak akan berubah. Cell.FormatConditions.Add, ini langsung dari makro walaupun makro yang direkodkan mengatakan Selection - Saya tidak mahu memilihnya jadi saya hanya akan mengatakan Cell, itulah setiap sel individu. Kami akan menggunakan xlNotEqual dan bukannya Formula: = ”=” 2 yang merupakan kod yang direkodkan di sana, saya telah menggabungkan apa sahaja yang ada di dalam sel itu. Oleh itu, periksa untuk melihat sama ada ia tidak sama dengan nilai semasa. Jadi jika sel pada masa ini mempunyai 2, kita katakan tidak sama dengan 2. Jika sel itu sekarang mempunyai 16.5, kita katakan tidak sama dengan 16.5. Dan selebihnya ini hanyalah makro direkod lurus, makro direkodkan, makro direkodkan, makro direkodkan. Semua itu adalah dari makro yang dirakam. Selesaikan ini Jika dengan Akhir Jika. Selesaikan Ini Untuk dengan WS Seterusnya.ini secara langsung dari makro walaupun makro yang direkodkan mengatakan Selection - Saya tidak mahu memilihnya jadi saya hanya akan mengatakan Cell, itulah setiap sel individu. Kami akan menggunakan xlNotEqual dan bukannya Formula: = ”=” 2 yang merupakan kod yang direkodkan di sana, saya telah menggabungkan apa sahaja yang ada di dalam sel itu. Oleh itu, periksa untuk melihat sama ada ia tidak sama dengan nilai semasa. Oleh itu, jika sel pada masa ini mempunyai 2, kita katakan tidak sama dengan 2. Jika sel itu sekarang mempunyai 16.5, kita katakan tidak sama dengan 16.5. Dan selebihnya ini hanyalah makro direkod lurus, makro direkodkan, makro direkodkan, makro direkodkan. Semua itu adalah dari makro yang dirakam. Selesaikan ini Jika dengan Akhir Jika. Selesaikan Ini Untuk dengan WS Seterusnya.ini secara langsung dari makro walaupun makro yang direkodkan mengatakan Selection - Saya tidak mahu memilihnya jadi saya hanya akan mengatakan Cell, itulah setiap sel individu. Kami akan menggunakan xlNotEqual dan bukannya Formula: = ”=” 2 yang merupakan kod yang direkodkan di sana, saya telah menggabungkan apa sahaja yang ada di dalam sel itu. Oleh itu, periksa untuk melihat sama ada ia tidak sama dengan nilai semasa. Oleh itu, jika sel pada masa ini mempunyai 2, kita katakan tidak sama dengan 2. Jika sel itu sekarang mempunyai 16.5, kita katakan tidak sama dengan 16.5. Dan selebihnya ini hanyalah makro direkod lurus, makro direkodkan, makro direkodkan, makro direkodkan. Semua itu adalah dari makro yang dirakam. Selesaikan Ini Jika dengan Akhir Jika. Selesaikan Ini Untuk dengan WS Seterusnya.Saya mahu memilihnya jadi saya hanya akan mengatakan Sel, itu adalah sel masing-masing. Kami akan menggunakan xlNotEqual dan bukannya Formula: = ”=” 2 yang merupakan kod yang direkodkan di sana, saya telah menggabungkan apa sahaja yang ada di dalam sel itu. Oleh itu, periksa untuk melihat sama ada ia tidak sama dengan nilai semasa. Jadi jika sel pada masa ini mempunyai 2, kita katakan tidak sama dengan 2. Jika sel itu sekarang mempunyai 16.5, kita katakan tidak sama dengan 16.5. Dan selebihnya ini hanyalah makro direkod lurus, makro direkodkan, makro direkodkan, makro direkodkan. Semua itu adalah dari makro yang dirakam. Selesaikan ini Jika dengan Akhir Jika. Selesaikan Ini Untuk dengan WS Seterusnya.Saya mahu memilihnya jadi saya hanya akan mengatakan Sel, itu adalah sel masing-masing. Kami akan menggunakan xlNotEqual dan bukannya Formula: = ”=” 2 yang merupakan kod yang direkodkan di sana, saya telah menggabungkan apa sahaja yang ada di dalam sel itu. Oleh itu, periksa untuk melihat sama ada ia tidak sama dengan nilai semasa. Oleh itu, jika sel pada masa ini mempunyai 2, kita katakan tidak sama dengan 2. Jika sel itu sekarang mempunyai 16.5, kita katakan tidak sama dengan 16.5. Dan selebihnya ini hanyalah makro direkod lurus, makro direkodkan, makro direkodkan, makro direkodkan. Semua itu adalah dari makro yang dirakam. Selesaikan ini Jika dengan Akhir Jika. Selesaikan Ini Untuk dengan WS Seterusnya.= "=" 2 yang dilakukan oleh kod yang direkodkan di sana, saya telah menggabungkan apa sahaja yang ada di sel itu. Oleh itu, periksa untuk melihat sama ada ia tidak sama dengan nilai semasa. Jadi jika sel pada masa ini mempunyai 2, kita katakan tidak sama dengan 2. Jika sel itu sekarang mempunyai 16.5, kita katakan tidak sama dengan 16.5. Dan selebihnya ini hanyalah makro direkod lurus, makro direkodkan, makro direkodkan, makro direkodkan. Semua itu adalah dari makro yang dirakam. Selesaikan ini Jika dengan Akhir Jika. Selesaikan Ini Untuk dengan WS Seterusnya.= "=" 2 yang dilakukan oleh kod yang direkodkan di sana, saya telah menggabungkan apa sahaja yang ada di dalam sel itu. Oleh itu, periksa untuk melihat sama ada ia tidak sama dengan nilai semasa. Oleh itu, jika sel pada masa ini mempunyai 2, kita katakan tidak sama dengan 2. Jika sel itu sekarang mempunyai 16.5, kita katakan tidak sama dengan 16.5. Dan selebihnya ini hanyalah makro direkod lurus, makro direkodkan, makro direkodkan, makro direkodkan. Semua itu adalah dari makro yang dirakam. Selesaikan ini Jika dengan Akhir Jika. Selesaikan ini dengan WS Seterusnya.makro direkodkan, makro direkodkan. Semua itu adalah dari makro yang dirakam. Selesaikan ini Jika dengan Akhir Jika. Selesaikan ini dengan WS Seterusnya.makro direkodkan, makro direkodkan. Semua itu adalah dari makro yang dirakam. Selesaikan ini Jika dengan Akhir Jika. Selesaikan Ini Untuk dengan WS Seterusnya.

Baiklah, jadi saya mempunyai makro yang disebut ApplyCF. Kembali ke Excel, tambahkan bentuk. Mudah mempunyai bentuk di sini: Masukkan, saya selalu memilih segi empat bulat, ketik Reset Ke Nilai Semasa. Kami akan menggunakan Rumah, pusat, dan pusat menjadikannya sedikit lebih besar. Saya suka cahaya. Saya rasa anda menganggapnya bodoh kerana tidak ada, cahaya, suasana yang saya suka tidak ada di sana, jadi saya selalu pergi ke Halaman Layout and Effect dan memilih yang kedua. Dan ketika saya kembali ke format, saya dapat memilih salah satu yang sebenarnya mempunyai sedikit cahaya. Bagi saya, saya rasa ia kelihatan keren, saya rasa ia berbaloi. Klik kanan, Tetapkan Makro dan sebut ApplyCF, klik OK. Baiklah, dan apa yang akan dilakukan ini ialah apabila saya mengkliknya, ia akan melalui semua helaian ini, mencari semua sel formula dan menyediakan format bersyarat yang mengatakan: Sekiranya sel-sel ini tidak sama dengan 7,tukar warna, okey? Itu sahaja. Secepat itu, berlaku secepat itu. BAM! Sudah disiapkan. Dan sekarang, perhatikan jika saya menukar ini menjadi 11, semua sel itu baru sahaja berubah. Sekarang jika kembali ke angka 1, ahh, warnanya berubah. Jadi, apa pun nilainya, apabila kita menukar- jika saya menukar sel ini, semua sel berubah. Sekiranya saya menukar sel ini, semua sel akan berubah. Sekiranya saya menukar sel ini, semua sel akan berubah.semua sel berubah.semua sel berubah.

Alright, now this is the new normal. Now from here, I want to track again. So I Reset To Current Values and then if I change this one to a 3, those sales change. Oh, by the way, these cells back here and these other sheets also changed in response to this. Track changes in Excel as it exists? Yeah, it's really lame. It doesn't show you the things that changed and having to show the workbook is a horrible, horrible thing. But with this simple, simple little macro, it works.

Alright now, the question said, alright, so if this is working how do I now get this to work in my other workbook? So I have some other workbook and I want to copy this over. Alright, so this is a great little bonus tip here. I'll create a brand new workbook and we'll put some stuff in here and I'll have a couple of formulas, and put a cell up there, alright. So we changed that cell, those 4 cells are all formula cells. Now if I want this workbook, Book2, to also have the code from podcast 2059, well I could retype it all again but that would be silly. So we come here to the Developer tab, and go to Visual Basic. And I want to make sure that I can see Book2 and then I can see Podcast 2059. I simply take that module and drag it and drop it on Book2, right there. And now, that code is also in Book2. Coming back to Book2, just add a shape, right click, Assign Macro, click OK. Alright, it works. And then 3, see, we've now applied that setting to this workbook.

Great question. Great question sent in from Montreal. And in this case, great question that my initial reaction is, well yeah, you're right. Track changes is horrible in Excel. And I wonder if I could create something that would actually track the changes. What are the downsides here and I'm sure I'm going to hear about this in the YouTube comments. If you had 10,000 formula cells, well now, all of these conditional formattings are going to be volatile, the things going to slow down, too many Excel format errors. Yeah, I can see all that but, you know, for a nice small workbook 5,6, 7 sheets, maybe 50 rows per sheet, I would think that this has some chance- some chance are working.

Okay, episode recap: Track changes in Excel, it's a little bizarre especially because they share the workbook. Our goal is to track what formula cells in Excel change. You have to save the workbook as XLSM, change your Macro Security. Record a macro to figure out the code to set up conditional formatting for numbers and not equal to 2, that’s just to figure out what red you want to use. Choose the formatting you want, Record another macro to learn how to remove conditional formatting from the worksheet. And then, to that macro that we recorded the first one, add a loop for each worksheet, an IF statement to prevent it from running on the title sheet, then a loop to check each formula cell. Add conditional formatting to each cell that says, if this CELL.VALUE is not equal to the value at the time that it ran, then we're going to highlight the things. Go back to Excel, assign a shape, add a shape, assign a macro to the shape, click the shape to run the macro. And I also showed you the bonus tip: dragging a VBA module to a new workbook.

Baiklah, saya ingin mengucapkan terima kasih kerana berhenti. Kami akan berjumpa anda di lain kali untuk siaran net lain dari.

Muat turun fail

Muat turun fail sampel di sini: Podcast2059.xlsm