Lacak Perubahan di Sel Formula Excel. Dapatkah Anda menunjukkan item mana yang baru saja diubah sebagai hasil dari perubahan sel input tertentu?
Menonton video
- Lacak perubahan di Excel agak aneh.
- Tujuannya adalah untuk melacak sel formula apa di Excel yang berubah.
- Save As untuk menyimpan buku kerja sebagai XLSM.
- Ubah Keamanan Makro.
- Rekam makro untuk mengetahui kode guna menyiapkan pemformatan bersyarat untuk angka yang tidak sama dengan 2.
- Pilih Pemformatan yang Anda inginkan.
- Rekam makro lain untuk mempelajari cara menghapus CF dari lembar kerja.
- Di makro, tambahkan satu lingkaran untuk setiap lembar kerja.
- Tambahkan pernyataan IF untuk mencegahnya berjalan di Judul.
- Tambahkan satu lingkaran untuk memeriksa setiap sel formula.
- Tambahkan Pemformatan Bersyarat untuk melihat apakah nilai sel pada saat makro berjalan.
- Kembali ke Excel.
- Tambahkan bentuk. Tetapkan makro ke bentuk.
- Klik Bentuk untuk Menjalankan Makro.
- Tip Bonus: Menyeret modul VBA ke buku kerja baru.
Transkrip Video
Belajar Excel dari Podcast, Episode 2059: Excel Track Changes (dalam hasil Formula)
Hai, selamat datang kembali di netcast, saya Bill Jelen. Pertanyaan hari ini dikirim dari Montreal tentang perubahan trek. Lacak perubahan, baiklah. Jadi inilah yang kami miliki. Kami memiliki 4 Sel Input, dan sejumlah besar sel Formula yang mengandalkan Sel Input ini. Dan jika saya ingin menghidupkan, saya akan kembali ke tab Tinjau, aktifkan Perubahan Sorotan, Lacak perubahan saat mengedit, klik OK, baiklah. Dan mereka memperingatkan saya bahwa mereka harus menyimpan buku kerja dan makro tidak bisa digunakan di buku kerja bersama. Kamu tahu itu? Ini adalah masalah saat Anda melacak perubahan, mereka berbagi buku kerja dan ada banyak hal yang tidak bisa terjadi di buku kerja bersama, Anda tahu, seperti makro dan banyak hal lainnya. Tapi mari kita lihat bagaimana melacak perubahan bekerja di Excel hari ini.
Mari kita ambil 2 ini dan ubah dari 2 menjadi 22, dan ambil 4 ini dan ubah dari 4 menjadi 44. Baiklah, dan Anda lihat, apa yang mereka catat dalam perubahan lintasan adalah bahwa kedua sel ini berubah, oke, segitiga ungu itu adalah trek yang sebenarnya berubah. Semua hal merah ini, itu tidak terjadi tetapi saya hanya menggambarkan bahwa semua sel darah merah ini berubah dan melacak perubahan tidak mengatakan apa-apa tentang perubahan ini, oke? Jadi, dikatakan, kedua sel ini telah berubah tetapi semua sel lainnya juga berubah. Lalu pertanyaan dari Montreal adalah, adakah cara agar perubahan trek benar-benar menunjukkan kepada kita semua yang berubah, tidak hanya sel input ini yang berubah?
Baiklah, jadi, hal pertama yang harus kita lakukan adalah mematikan Track Changes bawaan Excel. Dan kemudian, adakah cara yang bisa kita dapatkan- kita bisa membangun sistem perubahan lacak kita sendiri yang memungkinkan kita melihat semua sel formula yang berubah? Baiklah, jadi Langkah 1 dan langkah ini adalah langkah yang paling penting, jangan lewatkan ini. Lihat file Anda, file Anda disebut XLSX, Anda harus menyimpan ini: File, Simpan Sebagai, Sebagai buku kerja yang mendukung makro, atau semua ini tidak akan berfungsi. Anda harus mengklik kanan, Kustomisasi Pita, aktifkan Pengembang, setelah Anda masuk ke Pengembang, buka Keamanan Makro, ubah dari pengaturan ini - yang mengatakan kami tidak akan membiarkan makro berjalan atau bahkan tidak akan memberi tahu Anda bahwa mereka ada di sana untuk pengaturan ini. Anda harus melakukan dua langkah itu. Saya sudah melakukan dua langkah itu. Saya hidup setiap hari dengan dua langkah itu.Sudah diperbaiki, tetapi jika Anda baru mengenal makro, ini baru bagi Anda. Dan kemudian, kita perlu mencari tahu format seperti apa yang Anda inginkan. Baiklah, jadi saya hanya akan memilih beberapa sel di sini, saya akan merekam Makro yang disebut HowToCFRed, saya tidak akan menetapkannya ke tombol pintas karena ini tidak akan pernah berjalan lagi. Saya hanya merekam kode untuk mencari tahu cara kerja pemformatan bersyarat. Dan kita akan masuk ke Beranda, Pemformatan Bersyarat, Sel Sorot yang tidak sama dengan - Jadi, Lebih Banyak Aturan, Format sel tidak sama dengan - Lihat itu? Ini bukan di drop-down asli tetapi jika Anda masuk ke sini, tidak sama dengan 2, lalu pilih format. Ini bagian yang penting. Jadi saya akan memilih latar belakang merah. Anda memilih warna apa pun yang Anda inginkan di sini, oke? Bahkan pergi ke Lebih Banyak Warna, pilih warna merah lainnya,masuk ke Custom, pilih warna merah lainnya, oke? Itulah keindahan dari Perekam Makro, mereka akan memberi kami warna merah sempurna untuk Anda atau biru atau apa pun yang Anda inginkan. Oke, klik Oke. Dan kemudian, kami akan berhenti merekam, oke. Sekali lagi, inti dari ini hanyalah untuk melihat apa kode untuk format bersyarat.
Saya akan ke Macro, Bagaimana memformat merah bersyarat, dan mengedit. Baiklah, inilah bagian penting dari kode ini. Saya dapat melihat mereka menambahkan format bersyarat menggunakan xlNotEqual dan kami sulit mengutipnya agar tidak sama dengan 2. Dan kemudian kami mengubah interior sel ke warna itu.
Baiklah, saya juga perlu memikirkan cara menghapus semua pemformatan bersyarat pada lembar. Jadi, kembali ke Excel, Rekam Makro lain, Cara menghapus semua bersyarat, OK. Datang ke sini ke tab Beranda, buka Pemformatan Bersyarat, Hapus Aturan dari seluruh lembar, Hentikan Perekaman dan kita akan melihat kode itu. Bagus, ini makro satu baris. Dan saya bahkan suka di sini bahwa cara mereka melakukannya untuk seluruh lembar adalah hanya mengacu pada sel. Jadi dengan kata lain, semua sel di lembar aktif.
Sekarang, saya perlu membuat makro ini, makro yang direkam, sedikit lebih umum. Dan saya telah menulis banyak buku tentang cara melakukan VBA di Excel dan saya telah membuat video tentang cara melakukan VBA di Excel, dan inilah hal yang sederhana: Anda harus bisa merekam makro seperti ini, tetapi kemudian, tambahkan sekitar lima atau enam baris agar dapat membuat makro generik cukup.
And I'm going to talk about those lines, alright. So the first thing I want to do is I want to say, I want to go through the active workbook, go through all of the worksheets. So for each worksheet, WS is the object variable, I'll go through all the worksheets. And the person from Montreal said, “Hey, there is one sheet that I don't want to have this happen on.” So, if the WS.Name, with the worksheet dot name, is not equal to Title then we're going to do the code in the macro. Here's the sheet name: .Cells.FormatConditions.Delete. So, we're going to go through each individual of the sheet except for the title and delete all the format conditions, then we're going to go through each cell in the sheet but not all the cells, just the cells that have formulas. If it doesn't have a formula then I don't need to format it because it's not going to change. Cell.FormatConditions.Add, this is directly from the macro although the recorded macro said Selection - I don't want to have to select it so I'm just going to say Cell, that's each individual cell. We're going to use the xlNotEqual and instead of Formula:=”=”2 which is what the recorded code did right there, I've concatenated whatever's in that cell. So checking to see if it's not equal to the current value. So if the cell currently has 2, we're saying not equal to 2. If the cell currently has 16.5, we're saying not equal to 16.5. And then the rest of this is just straight recorded macro, recorded macro, recorded macro, recorded macro. All of that is from a recorded macro. Finish this If with an End If. Finish this For with a Next WS.
Alright, so I have a macro called ApplyCF. Go back to Excel, add a shape. Easy to have a shape here: Insert, I always choose a rounded rectangle, type Reset To Current Values. We’ll apply Home, the center, and the center make it a little bit larger. I love the glow. I suppose you think it's silly seeing it's not there, the glow, the setting I like isn't there so I always go to Page Layout and Effects and choose that second one. And then when I go back to the format, I can choose one that actually has a little bit of glow. To me, I think it looks cool, I think it's worth it. Right-click, Assign Macro and say ApplyCF, click OK. Alright, and then what this will do is when I click it, it’ll go through all of these sheets, find all of the formula cells and set up a conditional formatting that says: If these cells not equal to 7, change the color, alright? That's it. It's that fast it, happened that fast. BAM! It's done. And now, watch if I change this one to 11, all of those cells just changed. Now if it goes back to the 1, ahh, the colors changed. So, whatever the value was, when we change- if I change this cell, all of those cells change. If I change this cell, all of those cells change. If I change this cell, all of those cells change.
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 sudah mampir. Sampai jumpa di lain waktu untuk netcast lain dari.
Unduh berkas
Unduh file contoh di sini: Podcast2059.xlsm
