Thiết lập công thức định dạng có điều kiện sử dụng tham chiếu hỗn hợp. Hầu hết các công thức định dạng có điều kiện yêu cầu tham chiếu tuyệt đối. Nhưng bảng tính này để theo dõi xe tải trong bãi yêu cầu
Xem video
- Anderson đang tìm cách để có thể sao chép các khối dữ liệu chứa định dạng có điều kiện hỗn hợp
- Có cách nào để loại bỏ các ký hiệu đô la sau khi định dạng có điều kiện được thiết lập không?
- Không - không phải là không giới thiệu hàng tá quy tắc mới
- Giải pháp của tôi: các ô trợ giúp sử dụng tham chiếu tương đối để thay thế tham chiếu hỗn hợp trong định dạng có điều kiện
- Các kỹ thuật khác trong tập này:
- Nếu bạn có bốn quy tắc định dạng có điều kiện, hãy thiết lập 3 quy tắc đầu tiên và sau đó đặt quy tắc thứ tư làm màu mặc định
- Outtake # 1: Nhấn F2 để ngăn Excel chèn tham chiếu ô trong hộp thoại định dạng có điều kiện
- Outtake # 2: thiết lập định dạng có điều kiện
Bản ghi video
Học Excel từ Podcast Tập 2105: Sao chép định dạng có điều kiện với tham chiếu hỗn hợp
Này, chào mừng bạn trở lại với netcast. Đây sẽ là một ngày hôm nay phức tạp. Tôi đã tham gia một cuộc hội thảo ngày hôm qua và một trong những người trong hội thảo, Anderson, đã có một bảng tính thú vị với một vấn đề. Được rồi, và Anderson quản lý một bãi - xe kéo đến và xe kéo phải được dỡ xuống trong vòng ba ngày. Được rồi, vậy là - anh ấy bắt đầu, bạn biết đấy, đây là ngày hôm nay, đây là những đoạn giới thiệu đã đến và sau đó anh ấy đã thiết lập định dạng có điều kiện để khi đoạn giới thiệu được tải lên, nó sẽ chuyển sang màu xanh lam. Một khi cái gì đó có màu xanh thì mọi thứ đều tuyệt vời. Nhưng sau đó, anh ấy muốn tô màu mọi thứ. Nếu một thứ gì đó đến hôm nay hoặc hôm qua, nó sẽ có màu mã là màu xanh lá cây. Vì vậy, hôm nay là ngày 29 tháng 6 năm 2017, vì vậy điều này đã đến ngày hôm qua và mọi thứ chưa được dỡ bỏ đều có màu xanh lục nhưng khi nó đã được hơn một ngày,chúng tôi muốn đánh dấu mọi thứ bằng màu vàng và khi nó hơn hai ngày tuổi, đó là những vấn đề chúng tôi muốn làm nổi bật mọi thứ bằng màu đỏ. Và không phải vậy, bạn biết đấy, đây là một bảng tính để quản lý toàn bộ sân, phải không? Không phải là có một tờ cho những thứ đến vào ngày 26 và một tờ khác cho ngày 27 và một tờ khác cho ngày 28. Và bạn biết khó khăn là khi một ngày mới đến, họ có thể sao chép ngày hôm trước sang đây hoặc xuống đây.họ có thể sao chép ngày hôm trước sang đây hoặc xuống đây.họ có thể sao chép ngày hôm trước sang đây hoặc xuống đây.
Được rồi, điểm của video này không phải là về cách thiết lập định dạng có điều kiện này. Vì vậy, tôi sẽ giải quyết nhanh vấn đề này nhưng nếu bạn quan tâm đến cách thiết lập định dạng có điều kiện này, tôi sẽ đặt phiên bản chưa tăng tốc như một phần cuối của video.
Được rồi, vậy là xong. Đã tăng tốc độ đó, bạn có thể xem ở cuối để xem cách hoạt động. Chỉ cần làm một bài kiểm tra ở đây, CTRL; sẽ chuyển sang màu xanh lam. Nếu điều này quay trở lại ngày 26 tháng 6, nó sẽ chuyển sang màu đỏ và nếu là hôm nay, nó không hoạt động. Đúng vậy vì đây là những gì tôi sẽ làm, màu xanh lá cây quy tắc thứ tư của tôi đã đến hôm nay hoặc hôm qua, tôi sẽ sử dụng nó làm mặc định. Nếu không có quy tắc nào trong số ba quy tắc này là đúng thì sẽ có màu xanh là nó sẽ cung cấp cho tôi một quy tắc ít hơn mà tôi phải giải quyết ở đây, được chứ?
Được rồi, vậy chúng ta đang ở thời điểm mà chúng ta có vấn đề về cơ bản của Anderson. Tôi sẽ đặt vào ngày 25 tháng 6 năm 2017, tất cả sẽ chuyển sang màu đỏ ngoại trừ những cái đã được dỡ bỏ. Và bây giờ cuộc sống đang tiếp diễn, đó là ngày hôm sau. Chúng tôi nhận được một số đoạn giới thiệu vào ngày 26/6 và vì vậy Anderson sao chép dữ liệu này, dán vào đây, định dạng Cột Tự động bổ sung và đây sẽ là Đoạn giới thiệu 15. Hãy nhấp để sao chép nó xuống và tăng dần, loại bỏ những đoạn đã đến. Và vì vậy cái này đã đến ngày hôm nay, vì vậy tất cả chúng sẽ chuyển sang màu xanh lá cây nhưng chúng không chuyển sang màu xanh lục. Tại sao chúng không chuyển sang màu xanh lá cây? Chúng không chuyển sang màu xanh lục bởi vì những công thức này, những công thức định dạng có điều kiện này ngay tại đây, chúng ta sẽ xem xét chúng. Chúng được mã hóa cứng để sử dụng $ A $ 1. Ồ, điều đó thực sự tồi tệ.
Được rồi, chúng ta hãy thử và cải thiện mọi thứ ở đây. Điều đầu tiên tôi có thể làm, tôi sẽ loại bỏ tất cả những thứ đó và quay lại tập dữ liệu ban đầu này và thông minh hơn một chút trong lần vượt qua thứ hai và nói rằng chúng tôi không thực sự cần khóa nó xuống Cột A. Tôi sẽ loại bỏ ký hiệu $ đó. Nói cách khác, nó sẽ luôn là cột ở bên trái chúng ta, vì vậy đó sẽ là một tham chiếu hỗn hợp nhưng chúng ta luôn phải trỏ đến $ 1. Chúng tôi sẽ chỉnh sửa quy tắc này, nhấp vào OK. Được rồi, với một thay đổi đó khi chúng tôi sao chép sang bên phải và đưa dữ liệu mới vào, chẳng hạn như ngày hôm nay, nó hoạt động. Được rồi, điều này thật tuyệt. Cuộc sống sẽ trở nên tuyệt vời vào ngày 26/6 và cuộc sống sẽ trở nên tuyệt vời vào ngày 27/6. Được rồi, hoạt động tốt. Nhưng bây giờ chúng tôi gặp phải vấn đề là chúng tôi hết dung lượng trên trang và vì vậy những gì Anderson đang làm sẽ đi xuống,về cơ bản bắt đầu một hàng mới và dán và đây sẽ là 6/28 nhưng nó không chuyển sang màu xanh lục.
Tại sao nó không chuyển sang màu xanh lá cây? Nó không chuyển sang màu xanh lục vì tôi vẫn phải sử dụng $ để quay lại số 1. Được rồi, và bây giờ đây là câu hỏi hóc búa, đây là vấn đề. Bạn làm gì bây giờ Và tôi nghiêm túc, bạn phải làm gì bây giờ? Tôi muốn nghe nhận xét của YouTube về những gì bạn sẽ làm bây giờ.
Bạn biết đấy, hãy nhìn xem, có một lập luận được đưa ra rằng điều này là tốt, chúng ta có thể dừng lại ngay tại đây bởi vì bằng cách sử dụng 1 đô la Úc, chúng ta đã làm theo cách đó, cuộc sống thật dễ dàng vào Ngày 1, sao chép sang Ngày 2, cuộc sống thật tuyệt . Cuộc sống ngày 3 thật tuyệt. Chỉ mỗi ngày thứ 4 khi chúng tôi sao chép ở đây Anderson sẽ phải vào và thiết lập định dạng có điều kiện, chỉnh sửa định dạng này, chỉnh sửa quy tắc, thay đổi 1 thành 18. Nhấp OK, chỉnh sửa quy tắc này và thay đổi định dạng đó thành 18. Bấm OK, bấm OK. Được rồi, vậy Ngày 4, điều chỉnh nhỏ đó sao chép cho Ngày 5, sao chép cho Ngày 6 và sau đó sao chép cho Ngày 7. Thực hiện lại các bước đó. Nhưng này, hãy đối mặt với nó. Trang tính này đã được thiết lập sáu tháng trước với các quy tắc định dạng có điều kiện này và chúng chỉ cần hoạt động. Chúng ta không cần phải vào và thực hiện định dạng có điều kiện lặp đi lặp lại.
Phản ứng đầu tiên của tôi là tôi sẽ giả vờ như đây là một bảng tính nơi tôi có một số công thức ở đây và những công thức đó được xây dựng với tham chiếu tuyệt đối nhưng tôi cần những công thức đó để có thể sao chép qua hoặc xuống và tương đối trong bản sao - cả khi tôi sao chép vào đây và khi tôi sao chép vào đây. Được rồi, và để điều đó hoạt động, tôi sẽ sử dụng tham chiếu tuyệt đối khi thiết lập mọi thứ nhưng sau đó tôi sẽ sử dụng Tìm và thay thế, Ctrl H. Và giả sử hãy loại bỏ các tham chiếu tương đối đó, thay đổi mỗi $ A $ 1 thành A1, Thay thế Tất cả, nhấp vào Đóng và bây giờ là khối này, tất cả các công thức này đều khác nhau, hãy sao chép, dán và dán và nó sẽ hoạt động. Nó sẽ là tương đối. Vì vậy, tôi nói, được rồi, đó là những gì chúng ta cần làm. Chúng ta cần lấy số $ đó ra khỏi công thức.Và vì vậy tôi sẽ viết một macro cho phép tôi chỉnh sửa từng quy tắc định dạng có điều kiện này. Được rồi, và trước khi viết macro đó, tôi sẽ ghi lại macro của việc thay đổi một quy tắc định dạng có điều kiện, nhưng không phải là có 14 quy tắc định dạng có điều kiện ở đây. Nó thậm chí không tuân theo các quy tắc định dạng có điều kiện 14 * 3, 42 ở đây. Chỉ có 3 quy tắc định dạng có điều kiện ở đây và chúng tôi đang áp dụng 3 quy tắc định dạng có điều kiện đó cho một dải ô.chỉ có 3 quy tắc định dạng có điều kiện ở đây và chúng tôi đang áp dụng 3 quy tắc định dạng có điều kiện đó cho một phạm vi ô.chỉ có 3 quy tắc định dạng có điều kiện ở đây và chúng tôi đang áp dụng 3 quy tắc định dạng có điều kiện đó cho một phạm vi ô.
Vì vậy, nếu tôi thay đổi điều này, điều đầu tiên tôi phải làm là lấy 3 quy tắc định dạng có điều kiện này và biến chúng thành 42 quy tắc định dạng có điều kiện. Và sau đó, tôi bắt đầu co rúm người vì Anderson sao chép từ đây đến đây, anh ấy sẽ đưa ra 42 quy tắc mới và sau đó là 42 quy tắc mới. Và trong một tờ giấy có lẽ là 15 ngày, anh ấy sẽ giới thiệu hơn 600 quy tắc, 600 định dạng khác nhau và điều đó sẽ thật kinh khủng. Cuối cùng bạn sẽ gặp phải quá nhiều quy tắc định dạng, chưa kể rằng sẽ rất khó thiết lập ngay cả khi chúng ta có macro để thiết lập nó. Sẽ rất khó để thiết lập.
Al đúng, vậy chúng ta phải làm gì? Đây là những gì tôi đã nghĩ ra và tôi muốn biết liệu bạn có điều gì tốt hơn thế không. Tôi nói với Anderson, tôi nói, “Bạn biết đấy, trông nó khá đơn giản. Tất cả những thứ này đang xem xét một phép tính và phép tính đó là = TODAY- ngày ở bên trái tôi. " Và sẽ không tuyệt sao, nếu chúng ta có thể có câu trả lời đó trong một cột trợ giúp nhỏ ở bên phải. Và trên thực tế, chúng tôi không phải sử dụng bất kỳ $ nào cả, chúng tôi sẽ chỉ đặt tất cả các ô đó xuống bằng công thức nhỏ đơn giản đó.
I can see the look at Anderson’s face, he doesn't want that extra stuff out there erased but that's okay. We can hide, hide that later so we come back into these cells and go into our conditional formatting. That whole TODAY-A1 is simply going to be pointing to C3 and that's going to be a relative reference. So in other words, whatever cell we're in we're always going to look in the cell to the right, click OK, write at this one, click OK. We want to hide this data over here so I'll go in and CTRL 1. I'm going to use the three semicolons - ;;;,click OK. I’m going to do the exact same thing there. I’ll press F4, repeat the last action.
Now, the weird thing here is I have to convince myself that this little part, this blank column is part of the whole thing. So I want to just add a light grey there to remind myself that when I copy and paste, I'm going to have to include the grey. Alright, so here's our test now. I will choose this CTRL C and then I'm actually going to paste there and paste there and paste here. Alright, big tests go to 6/26, go to 6/27, change this to yellow, come to 6/28, it should change to green. Beautiful!
Alright, so now it's working, we have essentially replaced that conditional formatting mixed reference with a relative reference and we should be relatively, relatively good to go.
Alright, topics in this episode. We're looking for a way to be able to copy blocks of data containing conditional formatting that essentially is a mixed reference. So, is there some way to remove the $ once the conditional formatting is set up? Well maybe with a macro but you'd be introducing dozens of new rules instead of just one formula applying to a whole block. So my solution was to use helper cells that use relative references and then just use regular references in the conditional formatting.
Other topics in this episode, if you have 4 conditional formatting rules just set the first three and make the fourth rule be the default color. The outtake coming up next is press F2 to stop Excel from inserting cell references in the conditional formatting dialogue and then setting up the conditional formatting dialogue.
Well, I want to thank Anderson for being in my seminar and hopefully, you know, this helps him. I want to thank you for stopping by. We'll see you next time for another netcast from.
I'll take number 1. When you're dealing with conditional formatting in that stupid dialogue box and you need to edit something that's already in there, you better be darn good at clicking in the right spot. Like if I wanted to change that 1 to be 18, and I clicked right there and then press the Right Arrow key then I have to swear because they're inserting cell references instead. Alright, and so many times when I was recording this episode, I clicked in the wrong spot and hit the Right Arrow key or the Left Arrow key or Shift Arrow key and how to back out of it.
Alright, here is the key if this has been driving you crazy for years. Well key number 1, just be perfect about where you click. Right then you don't have any problem at all, life is great but that's not realistic. Here's the whole trick. When you are in this dialogue box, down here in the lower left-hand corner it says that we are in Enter mode and when you're in Enter mode using Left or Right Arrow keys is going to insert cell references for you like that, right? Really, really annoying. But what you want to do is you want to press the F2 key and that changes us from Enter mode to Edit mode. Bingo! Now we can do whatever we want. We can use the Left Arrow key or the Right Arrow key and we're not inserting cells like that.
Alright, next up is I'll take number 2 where I built this original conditional formatting. I showed that in fast motion before here. Just in case you're interested is the slow motion.
So we're going to have a date here. I'm going to put in yesterday's date just or two days ago date so we have something - this is going to work. Alright, and we're going to assume that there's going to be some number of space for trailers to come in. In this case I'll go down to Trailer 14 and then here, we're going to build conditional format. And there are four rules that we want to do. And the first one, the easy one is if there's a date here then we’re going to turn this thing blue, so alt="" O D to get into conditional formatting. I'm going to create a new rule and that new rule is going to be the easy one format, only cells that contain a value that is greater than 0. Then we're going to format this using a blue color like that, click OK, click OK. Alright, first rule done.
Second rule is the thing- the date up in A1, more than one days old. This one is going to be the tricky one and this is where we have to look at a specific cell. So I’m going to have to use a formula and we'll say =TODAY, today will be today’s date minus that date up in A1. If that is>2,>1 then we're going to format it in yellow.
Alright, and I don't know if I need parentheses here, I'm going to just be safe and put the ( ) in and copy that whole thing so I can create the red color. So copy and then we'll add a new rule and rule is going to be if it’s> or =2, we’ll format as red.
Alright, now we have to be careful here. The first thing I want to do is I want to check to see if the thing is filled in. If the thing is filled in, we get the blue, we stop if true. Then the next thing to do, we have to check for the red before we check for the yellow because this formula for yellow is also going to be true on the days when it should be red.
Được rồi, vậy là xong. Đã tăng tốc độ đó, bạn có thể xem ở cuối để xem cách hoạt động. Chỉ cần làm một bài kiểm tra ở đây. CTRL; sẽ chuyển sang màu xanh lam. Nếu điều này quay trở lại 6/26, nó sẽ chuyển sang màu đỏ. Và nếu nó là ngày hôm nay, nó không hoạt động. Đúng vậy bởi vì đây là những gì tôi sẽ làm. Quy tắc thứ tư của tôi, màu xanh lá cây đến hôm nay hoặc hôm qua, tôi sẽ sử dụng nó làm mặc định. Nếu không có quy tắc nào trong số ba quy tắc khác là đúng thì sẽ có màu xanh lá cây rằng nó sẽ cung cấp cho tôi một quy tắc ít hơn mà tôi phải xử lý ở đây. Ổn thỏa.
Tải tập tin
Tải xuống tệp mẫu tại đây: Podcast2105.xlsx
