Theo dõi các thay đổi trong ô công thức Excel. Bạn có thể chỉ ra những mục nào vừa thay đổi do thay đổi một số ô đầu vào không?
Xem video
- Theo dõi các thay đổi trong Excel hơi kỳ lạ.
- Mục tiêu là theo dõi những gì ô công thức trong Excel thay đổi.
- Save As để lưu sổ làm việc dưới dạng XLSM.
- Thay đổi Bảo mật Macro.
- Ghi macro để tìm ra mã để thiết lập định dạng có điều kiện cho các số không bằng 2.
- Chọn Định dạng mà bạn muốn.
- Ghi lại một macro khác để tìm hiểu cách xóa CF khỏi trang tính.
- Trong macro, hãy thêm một vòng lặp cho mỗi trang tính.
- Thêm câu lệnh IF để ngăn nó chạy trên Tiêu đề.
- Thêm một vòng lặp để kiểm tra từng ô công thức.
- Thêm Định dạng có Điều kiện để xem giá trị ô tại thời điểm macro có chạy hay không.
- Quay lại Excel.
- Thêm một hình dạng. Gán macro cho hình dạng.
- Bấm vào Hình dạng để chạy Macro.
- Mẹo bổ sung: Kéo mô-đun VBA vào sổ làm việc mới.
Bản ghi video
Học Excel từ Podcast, Tập 2059: Thay đổi Theo dõi Excel (trong kết quả Công thức)
Chào mừng bạn trở lại netcast, tôi là Bill Jelen. Câu hỏi hôm nay được gửi đến từ Montreal về những thay đổi theo dõi. Theo dõi các thay đổi, được rồi. Vì vậy, đây là những gì chúng tôi có. Chúng ta có 4 Ô đầu vào và một loạt các ô Công thức dựa trên các Ô đầu vào này. Và nếu tôi bật, tôi sẽ quay lại tab Xem lại, bật Thay đổi nổi bật, Theo dõi các thay đổi trong khi chỉnh sửa, bấm OK, được rồi. Và họ đã cảnh báo tôi rằng họ phải lưu sổ làm việc và không thể sử dụng macro trong sổ làm việc được chia sẻ. Bạn có biết rằng? Đây là vấn đề khi bạn theo dõi các thay đổi, chúng dùng chung sổ làm việc và có rất nhiều thứ không thể xảy ra trong sổ làm việc được chia sẻ, bạn biết đấy, như macro và nhiều thứ khác. Nhưng chúng ta hãy xem xét cách các thay đổi theo dõi hoạt động trong Excel ngày hôm nay.
Hãy lấy 2 ô này và thay đổi từ 2 thành 22, và lấy 4 này và thay đổi nó từ 4 thành 44. Được rồi, và bạn thấy đấy, những gì họ đã ghi nhận trong các thay đổi theo dõi là hai ô này đã thay đổi, được rồi, những hình tam giác màu tím đó là theo dõi thực tế thay đổi. Tất cả những thứ màu đỏ này, điều đó không xảy ra nhưng tôi chỉ minh họa rằng tất cả những tế bào màu đỏ này đang thay đổi và theo dõi những thay đổi không nói gì về những thay đổi này, được chứ? Vì vậy, nó chỉ nói rằng, hai ô này đã được thay đổi nhưng tất cả các ô khác cũng được thay đổi. Và câu hỏi đặt ra từ Montreal là, có cách nào để các thay đổi theo dõi thực sự cho chúng ta thấy mọi thứ thay đổi, không chỉ các ô đầu vào này đã thay đổi?
Được rồi, vì vậy, điều đầu tiên chúng ta phải làm là tắt Theo dõi Thay đổi tích hợp trong Excel. Và sau đó, có cách nào để chúng ta có thể - chúng ta có thể xây dựng hệ thống thay đổi theo dõi của riêng mình để cho phép chúng ta xem tất cả các ô công thức đã thay đổi không? Được rồi, vậy Bước 1 và bước này là bước quan trọng nhất, đừng bỏ qua bước này. Nhìn vào tệp của bạn, tệp của bạn được gọi là XLSX, bạn phải lưu cái này: Tệp, Lưu dưới dạng, Dưới dạng sổ làm việc được kích hoạt macro, hoặc không cái nào trong số này sẽ hoạt động. Bạn phải nhấp chuột phải, Tùy chỉnh ruy-băng, bật Nhà phát triển, khi bạn vào Nhà phát triển, hãy chuyển đến Bảo mật Macro, thay đổi từ cài đặt này - cài đặt nói rằng chúng tôi sẽ không cho phép macro chạy hoặc thậm chí sẽ không nói bạn rằng họ ở đó với cài đặt này. Bạn phải thực hiện hai bước đó. Tôi đã thực hiện hai bước đó. Tôi sống mỗi ngày với hai bước đó.Đã được sửa, nhưng nếu bạn chưa quen với macro, thì điều này là mới đối với bạn. Và sau đó, chúng tôi cần tìm ra loại định dạng bạn muốn. Được rồi, tôi sẽ chỉ chọn một số ô ở đây, tôi sẽ Ghi một Macro có tên là HowToCFRed, tôi sẽ không gán vào một phím tắt vì nó sẽ không bao giờ chạy lại. Tôi chỉ ghi lại mã để tìm ra cách hoạt động của định dạng có điều kiện. Và chúng ta sẽ đi vào Trang chủ, Định dạng có điều kiện, Đánh dấu các ô không bằng - Vì vậy, Nhiều quy tắc hơn, Định dạng ô không bằng - Thấy không? Nó không nằm trong danh sách thả xuống ban đầu nhưng nếu bạn vào đây, không bằng 2, rồi chọn định dạng. Đây là một phần quan trọng. Vì vậy, tôi sẽ chọn một nền màu đỏ. Bạn chọn bất kỳ màu nào bạn muốn ở đây, được chứ? Thậm chí vào More Colors, chọn một số màu đỏ khác,vào Tùy chỉnh, chọn một số màu đỏ khác, được chứ? Đó là vẻ đẹp của Macro Recorder, họ sẽ cung cấp cho chúng tôi một số màu đỏ hoàn hảo cho bạn hoặc màu xanh lam hoặc bất cứ điều gì bạn muốn. Được rồi, bấm OK. Và sau đó, chúng tôi sẽ dừng ghi, được thôi. Một lần nữa, toàn bộ vấn đề này chỉ là xem mã là gì cho các định dạng có điều kiện.
Tôi sẽ đến Macro, Cách định dạng có điều kiện màu đỏ và chỉnh sửa. Được rồi, đây là những phần quan trọng của mã này. Tôi có thể thấy họ đang thêm một định dạng có điều kiện bằng cách sử dụng xlNotEqual và chúng tôi khó trích dẫn nó không bằng 2. Và sau đó chúng tôi thay đổi nội thất của ô thành màu đó.
Được rồi, tôi cũng cần tìm cách xóa tất cả định dạng có điều kiện trên trang tính. Vì vậy, quay lại Excel, Ghi lại một Macro khác, Cách xóa tất cả các điều kiện, OK. Tại đây, đến tab Trang đầu, chuyển đến Định dạng có điều kiện, Xóa quy tắc khỏi toàn bộ trang tính, Dừng ghi và chúng ta sẽ xem mã đó. Tuyệt vời, đó là macro một dòng. Và tôi thậm chí thích ở đây rằng cách họ làm điều đó cho toàn bộ trang tính là nó chỉ tham chiếu đến các ô. Nói cách khác, tất cả các ô trên trang tính đang hoạt động.
Bây giờ, tôi cần tạo macro này, macro đã ghi, chung chung hơn một chút. Và tôi đã viết rất nhiều sách về cách thực hiện VBA trong Excel và tôi đã thực hiện các video về cách thực hiện VBA trong Excel và đây là điều đơn giản: bạn cần có thể ghi một macro như thế này nhưng sau đó, hãy thêm khoảng năm hoặc sáu dòng để có thể tạo đủ macro.
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.
À này, tôi muốn cảm ơn bạn đã ghé qua. Chúng tôi sẽ gặp bạn lần sau cho một netcast khác từ.
Tải tập tin
Tải file mẫu tại đây: Podcast2059.xlsm
