एक्सेल सशर्त प्रारूप मिश्रित संदर्भ जानें - एक्सेल टिप्स

एक सशर्त स्वरूपण सूत्र सेट करना जो मिश्रित संदर्भ का उपयोग करता है। अधिकांश सशर्त स्वरूपण फ़ार्मुलों को एक पूर्ण संदर्भ की आवश्यकता होती है। लेकिन एक यार्ड में ट्रकों को ट्रैक करने के लिए इस स्प्रेडशीट की आवश्यकता होती है

वीडियो देखेंा

• एंडरसन मिश्रित सशर्त स्वरूपण वाले डेटा के ब्लॉक की प्रतिलिपि बनाने में सक्षम होने के लिए एक रास्ता ढूंढ रहा है
• क्या सशर्त स्वरूपण स्थापित करने के बाद डॉलर के संकेतों को हटाने का कोई तरीका है?
• नहीं - दर्जनों नए नियमों को लागू किए बिना नहीं
• मेरा समाधान: हेल्पर कोशिकाएं जो सशर्त स्वरूपण में मिश्रित संदर्भ को बदलने के लिए सापेक्ष संदर्भ का उपयोग करती हैं
• इस कड़ी में अन्य तकनीकें:
• यदि आपके पास चार सशर्त स्वरूपण नियम हैं, तो पहले 3 सेट करें और फिर चौथा नियम डिफ़ॉल्ट रंग बनाएं
• # 1 से बाहर करें: एक्सेल को सशर्त स्वरूपण संवाद में सेल संदर्भ डालने से रोकने के लिए F2 दबाएं
• # 2 से आगे: सशर्त स्वरूपण की स्थापना

वीडियो ट्रांसक्रिप्ट

पॉडकास्ट एपिसोड 2105 से एक्सेल सीखें: मिश्रित संदर्भ के साथ सशर्त प्रारूप की नकल करना

अरे, नेटकास्ट में वापस स्वागत है। यह आज एक जटिल होने जा रहा है। मैं कल एक संगोष्ठी कर रहा था और संगोष्ठी में शामिल लोगों में से एक, एंडरसन, एक समस्या के साथ एक दिलचस्प स्प्रेडशीट था। ठीक है, और एंडरसन एक यार्ड का प्रबंधन करता है - ट्रेलरों का आगमन होता है और तीन दिनों के भीतर ट्रेलरों को उतारना पड़ता है। ठीक है, इसलिए यह है - वह बाहर शुरू होता है, आप जानते हैं, यह दिन था, ये ट्रेलर थे जो पहुंचे और फिर उन्होंने सशर्त स्वरूपण सेट किया है कि एक बार ट्रेलर अनलोड होने के बाद, यह नीले रंग में बदल जाता है। एक बार जब कुछ नीला होता है तो सब कुछ बहुत अच्छा होता है। लेकिन फिर, वह कोड चीजों को रंग देना चाहता है। अगर आज या कल कुछ आ गया, तो उसका रंग हरा हो जाता है। तो आज २ ९ जून २०१th है इसलिए यह कल आया था और जो कुछ भी अनलोड नहीं हुआ है वह हरा है लेकिन जब यह एक दिन से अधिक पुराना है,हम चीजों को पीले रंग के रूप में उजागर करना चाहते हैं और जब यह दो दिन से अधिक पुराना हो जाता है, तो वे समस्याएं हैं जो हम चीजों को लाल रंग में उजागर करना चाहते हैं। और ऐसा नहीं है, आप जानते हैं, यह पूरे यार्ड का प्रबंधन करने के लिए एक वर्कशीट है, है ना? ऐसा नहीं है कि 26 वें और 27 वें और दूसरे और 28 वें स्थान पर आने वाली चीजों के लिए एक शीट है। और आप जानते हैं कि कठिनाई एक नया दिन आने के साथ है, वे या तो पिछले दिन की नकल यहाँ या यहाँ करने के लिए नीचे।वे या तो पिछले दिन को यहाँ या यहाँ पर नीचे की ओर कॉपी करते हैं।वे या तो पिछले दिन को यहाँ या यहाँ पर नीचे की ओर कॉपी करते हैं।

अब ठीक है, इस वीडियो का मुद्दा यह नहीं है कि इस सशर्त प्रारूपण को कैसे सेट किया जाए। इसलिए मैं इसके माध्यम से गति करने जा रहा हूं, लेकिन यदि आप इस सशर्त स्वरूपण को स्थापित करने के तरीके में रुचि रखते हैं, तो मैं वीडियो के अंत में एक अप्रकाशित संस्करण को एक आउटटेक के रूप में रखूंगा।

ठीक है, तो हम वहाँ हैं। यह काम किया है, आप अंत में देख सकते हैं कि यह कैसे काम करता है। बस यहाँ एक परीक्षण कर, CTRL; नीले रंग में बदल जाएगा। यदि यह 6/26 पर वापस चला जाता है, तो यह लाल में बदल जाएगा और यदि यह आज है, तो यह काम नहीं कर रहा है। यह सही है क्योंकि यहाँ मैं क्या करने जा रहा हूँ, मेरा चौथा नियम आज या कल आ गया है, मैं बस इसे डिफ़ॉल्ट के रूप में उपयोग करने जा रहा हूँ। यदि इन तीन नियमों में से कोई भी सत्य नहीं है, तो यह हरा होने वाला है कि यह मुझे एक कम नियम देगा जो मुझे यहां से निपटना होगा, ठीक है?

ठीक है, इसलिए हम अब उस बिंदु पर हैं जहां हमें अनिवार्य रूप से एंडरसन की समस्या है। मैं 6/25/2017 में डालने जा रहा हूं, ये सभी अनलोड किए गए लोगों को छोड़कर लाल हो जाएंगे। और अब जीवन आगे बढ़ रहा है, यह अगले दिन है। हमें 6/26 पर कुछ ट्रेलर मिले और इसलिए एंडरसन ने इस डेटा को कॉपी किया, यहां पेस्ट किया, कॉलम ऑटोफिट को प्रारूपित किया, और यह ट्रेलर 15 होगा। उस डाउन और इंक्रीमेंट को कॉपी करने के लिए क्लिक करें, जो आए, उससे छुटकारा पाएं। और इसलिए यह आज आया है, इसलिए इन सभी को हरे रंग में बदलना चाहिए, लेकिन वे हरे रंग में बदल नहीं रहे हैं। वे हरे क्यों नहीं हो रहे हैं? वे हरे रंग में नहीं बदल रहे हैं क्योंकि ये सूत्र, ये सशर्त स्वरूपण सूत्र यहीं हैं, हम इन्हें देखेंगे। वे $ A $ 1 का उपयोग करने के लिए हार्ड-कोडेड हैं। ओह, यह वास्तव में बुरा है।

ठीक है, तो चलो कोशिश करते हैं और यहां चीजों में सुधार करते हैं। पहली चीज जो मैं कर सकता हूं, मैं उन सभी से छुटकारा पाकर इस मूल डेटा सेट पर वापस आऊंगा और दूसरे पास पर थोड़ा होशियार रहूंगा और कहूंगा कि हमें वास्तव में इसे कॉलम ए पर लॉक करने की आवश्यकता नहीं है। मैं उस $ संकेत से छुटकारा पा लूंगा। दूसरे शब्दों में, यह हमेशा हमारे बाईं ओर कॉलम होने जा रहा है, इसलिए यह एक मिश्रित संदर्भ होने जा रहा है, लेकिन हमें हमेशा $ 1 को इंगित करना होगा। हम इस नियम को संपादित करेंगे, ठीक पर क्लिक करें। अब ठीक है, उस बदलाव के साथ जब हम दाईं ओर कॉपी करते हैं और नया डेटा डालते हैं, जैसे आज की तारीख में, यह काम करता है। ठीक है, तो यह बहुत अच्छा है। जीवन 6/26 पर महान होने जा रहा है और 6/27 पर जीवन महान होने जा रहा है। ठीक है, महान काम कर रहा है। लेकिन अब हम उस समस्या में भाग लेते हैं जहां हम पृष्ठ पर स्थान से बाहर भागते हैं और इसलिए एंडरसन जो कर रहा है वह नीचे चला जाता है,अनिवार्य रूप से एक नई पंक्ति और पेस्ट शुरू होता है और यह 6/28 होगा लेकिन यह हरा नहीं हो रहा है।

यह हरा क्यों नहीं हो रहा है? यह हरा नहीं हो रहा है क्योंकि मुझे अभी भी $ 1. का उपयोग करने के लिए वापस आना था। ठीक है, और इसलिए अब यहाँ पर समस्या है, यहाँ समस्या है। अभी तुम क्या करती हो? और मैं गंभीर हूं, अब आप क्या करते हैं? मैं YouTube टिप्पणियों में सुनना चाहता हूं कि आप अब क्या करेंगे।

आप जानते हैं, इसलिए अरे देखो, एक तर्क दिया गया है कि यह अच्छा है, हम यहीं रुक सकते हैं क्योंकि A $ 1 का उपयोग करके, हमने इसे इस तरह बनाया है, दिन 1 पर जीवन आसान है, दिन 2 पर कॉपी करें, जीवन महान है । दिन 3 जीवन महान है। यह केवल हर 4 वें दिन होता है जब हम यहां कॉपी करते हैं कि एंडरसन को सशर्त स्वरूपण में जाना होगा, इसे संपादित करना होगा, नियम को संपादित करना होगा, 1 को 18 में बदलना होगा। ठीक पर क्लिक करें, इस नियम को संपादित करें और 1 को बदलें 18. ओके पर क्लिक करें, ओके पर क्लिक करें। ठीक है, इसलिए दिन 4, कि 5 दिन के लिए थोड़ा समायोजन कॉपी, दिन 6 के लिए कॉपी और फिर 7 दिन के लिए कॉपी। उन चरणों को फिर से करें। लेकिन हे, चलो इसे सामना करते हैं। यह कार्यपत्रक इन सशर्त प्रारूपण नियमों के साथ छह महीने पहले स्थापित किया गया था और उन्हें बस काम करने की आवश्यकता है। हमें बार-बार सशर्त स्वरूपण में जाने की आवश्यकता नहीं है।

मेरी पहली प्रतिक्रिया मैं इस तरह का नाटक करने जा रहा था, यह एक स्प्रेडशीट है, जहां मेरे पास कुछ सूत्र हैं और उन सूत्रों को पूर्ण संदर्भों के साथ बनाया गया था, लेकिन मुझे उन सूत्रों को कॉपी या डाउन करने में सक्षम होना चाहिए, और प्रतिलिपि के भीतर सापेक्ष होना चाहिए। - जब मैं यहां कॉपी करता हूं और जब मैं यहां कॉपी करता हूं तो दोनों। ठीक है, और उस काम को पाने के लिए, मैं पूर्ण संदर्भों का उपयोग करने जा रहा हूं जब मैं चीजों को सेट करता हूं, लेकिन फिर मैं फाइंड और रिप्लेसमेंट का उपयोग करने जा रहा हूं, Ctrl H. और मान लीजिए कि उन सापेक्ष संदर्भों से छुटकारा पाएं, हर $ A $ 1 को A1 में बदलें, सभी को बदलें, क्लोज़ को क्लिक करें और अब इस ब्लॉक को, ये सभी फॉर्मूले नीचे, कॉपी, पेस्ट और पेस्ट के अलग-अलग हैं और यह काम करेगा। यह सापेक्ष होगा। तो मैंने कहा, ठीक है, ठीक है कि हमें क्या करना है। हमें उन $ को सूत्र से बाहर निकालने की आवश्यकता है।और इसलिए मैं एक मैक्रो लिखने जा रहा था जो मुझे इन सशर्त प्रारूपण नियमों में से प्रत्येक को संपादित करने की अनुमति देगा। ठीक है, और इससे पहले कि मैंने लिखा कि मैक्रो मैं एक सशर्त स्वरूपण नियम को बदलने के मैक्रो को रिकॉर्ड करने जा रहा था, लेकिन ऐसा नहीं है कि यहां 14 सशर्त स्वरूपण नियम हैं। यह 14 * 3, 42 सशर्त प्रारूपण नियमों के लिए भी नहीं है। यहां केवल 3 सशर्त स्वरूपण नियम हैं और हम उन 3 सशर्त स्वरूपण नियमों को कक्षों की एक सीमा तक लागू कर रहे हैं।यहां केवल 3 सशर्त स्वरूपण नियम हैं और हम उन 3 सशर्त स्वरूपण नियमों को कोशिकाओं की एक सीमा तक लागू कर रहे हैं।यहां केवल 3 सशर्त स्वरूपण नियम हैं और हम उन 3 सशर्त स्वरूपण नियमों को कोशिकाओं की एक सीमा तक लागू कर रहे हैं।

इसलिए अगर मैं इसे बदलूंगा, तो पहली बात मुझे ये तीन सशर्त प्रारूपण नियम लेने होंगे और उन्हें 42 सशर्त प्रारूपण नियम बनाने होंगे। और फिर, मैं ऐंठन शुरू कर रहा हूं क्योंकि एंडरसन यहां से यहां तक ​​की नकल करता है, वह 42 नए नियम और फिर 42 नए नियम पेश करने जा रहा है। और शायद 15 दिनों के साथ कागज की एक शीट के दौरान, वह 600 से अधिक नियमों, 600 विभिन्न स्वरूपों को पेश करने जा रहा है और यह सिर्फ भयानक होने वाला है। आप अंततः बहुत अधिक फ़ॉर्मेटिंग नियम वाली चीज़ को हिट करने जा रहे हैं, यह उल्लेख नहीं करने के लिए कि यह सेट अप करने के लिए कठिन है, भले ही हमारे पास इसे सेट करने के लिए मैक्रो हो। इसे स्थापित करना कठिन होता जा रहा है।

Al right, so what do we do? Here's what I came up with and I want to hear if you have something better than that. I said to Anderson, I said, “You know, look it's pretty simple. All of these are looking at one calculation and that calculation is =TODAY- the date that’s to the left of me.” And wouldn’t it be cool, if we could have that answer in a little helper column over here to the right. And in fact, we don't have to use any $ at all, we'll just put all of those cells all the way down with that simple little formula.

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.

ठीक है, तो हम वहाँ हैं। यह काम किया है, आप अंत में देख सकते हैं कि यह कैसे काम करता है। बस यहां एक परीक्षण कर रहे हैं। CTRL; नीले रंग में बदल जाएगा। यदि यह 6/26 पर वापस चला जाता है, तो यह लाल रंग में बदल जाएगा। और अगर यह आज है, यह काम नहीं कर रहा है। यह सही है क्योंकि यहाँ मैं क्या करने जा रहा हूँ। मेरा चौथा नियम, ग्रीन आज आ गया है या कल मैं इसे डिफ़ॉल्ट के रूप में उपयोग करने जा रहा हूं। यदि इन तीन नियमों में से कोई भी सत्य नहीं है, तो यह हरा होने वाला है कि यह मुझे एक कम नियम देगा जो मुझे यहां से निपटना होगा। ठीक है।

फ़ाइल डाउनलोड करें

यहाँ नमूना फ़ाइल डाउनलोड करें: Podcast2105.xlsx