Karışık bir referans kullanan koşullu bir biçimlendirme formülü ayarlama. Çoğu koşullu biçimlendirme formülü, mutlak bir başvuru gerektirir. Ancak bir bahçedeki kamyonları izlemek için kullanılan bu elektronik tablo,
Video izle
- Anderson, karışık koşullu biçimlendirme içeren veri bloklarını kopyalamanın bir yolunu arıyor
- Koşullu biçimlendirme kurulduktan sonra dolar işaretlerini kaldırmanın bir yolu var mı?
- Hayır - düzinelerce yeni kural getirmeden olmaz
- Çözümüm: Koşullu biçimlendirmede karma referansı değiştirmek için göreli referanslar kullanan yardımcı hücreler
- Bu bölümdeki diğer teknikler:
- Dört koşullu biçimlendirme kuralınız varsa, ilk 3'ü ayarlayın ve ardından dördüncü kuralı varsayılan renk yapın
- Çıktı 1: Excel'in koşullu biçimlendirme iletişim kutusunda hücre referansları eklemesini durdurmak için F2'ye basın
- Çıktı # 2: Koşullu biçimlendirmeyi ayarlama
Video Metni
Excel'i Podcast Bölüm 2105'ten Öğrenin: Koşullu Biçimi Karışık Referanslarla Kopyalama
Hey, İnternet'e tekrar hoş geldiniz. Bu bugün karmaşık olacak. Dün bir seminer yapıyordum ve seminerdeki kişilerden biri olan Anderson'ın problemli ilginç bir elektronik tablosu vardı. Pekala, Anderson bir bahçeyi yönetiyor - römorklar geliyor ve römorkların üç gün içinde indirilmesi gerekiyor. Pekala, öyleyse bu - başlıyor, biliyorsunuz, bu gün geldi, bunlar gelen fragmanlardı ve ardından, römork boşaldıktan sonra maviye dönecek şekilde koşullu biçimlendirme ayarını yaptı. Bir şey mavi olduğunda her şey harika. Ama sonra bir şeyleri renklendirmek istiyor. Bugün veya dün bir şey gelirse, rengi yeşil olarak kodlanır. Yani bugün 29 Haziran 2017, bu yüzden bu dün geldi ve boşaltılmayan her şey yeşil, ancak bir günden fazla olduğunda,her şeyi sarı olarak vurgulamak istiyoruz ve iki günden daha eski olduğunda, kırmızı olarak vurgulamak istediğimiz sorunlar bunlar. Ve bu o değil, biliyorsun, bu tüm bahçeyi yönetmek için bir çalışma sayfası, değil mi? 26'sında, 27'sinde başka ve 28'inde gelen şeyler için bir sayfa olduğundan değil. Ve yeni bir gün geldikçe zorluk olduğunu biliyorsunuz, ya önceki günü buraya kopyalarlar ya da buraya.ya önceki günü buraya ya da buraya kopyalarlar.ya önceki günü buraya ya da buraya kopyalarlar.
Pekala, bu videonun amacı bu koşullu biçimlendirmenin nasıl ayarlanacağı değil. Bu yüzden bunu hızlandıracağım, ancak bu koşullu biçimlendirmeyi nasıl kuracağınızla ilgileniyorsanız, hızlandırılmamış sürümü videonun sonuna bir çıkış olarak koyacağım.
Tamam, işte buradayız. Bunu hızlandırın, nasıl çalıştığını görmek için sonunda izleyebilirsiniz. Burada sadece bir test yapıyorum, CTRL; maviye dönecek. Bu 6 / 26'ya geri dönerse, kırmızıya dönecek ve bugünse, çalışmıyor. Bu doğru çünkü yapacağım şey şu, dördüncü kuralım yeşil bugün veya dün geldi, sadece bunu varsayılan olarak kullanacağım. Bu diğer üç kuraldan hiçbiri doğru değilse, o zaman bana burada uğraşmam gereken bir kural daha az verecek, tamam mı?
Tamam, şimdi Anderson'un problemini esasen yaşadığımız noktadayız. 6/25/2017 koyacağım, boşaltılmış olanlar dışında bunların hepsi kırmızıya dönecek. Ve şimdi hayat devam ediyor, ertesi gün. 6/26 tarihinde bazı fragmanlar aldık ve bu nedenle Anderson bu verileri kopyalayıp buraya yapıştırın, Sütunu Otomatik Sığdır'ı biçimlendirin ve bu da Fragman 15 olacaktır. Onu kopyalamak ve artırmak için gidin, gelenlerden kurtulun. Ve bu bugün geldi, yani bunların hepsi yeşile dönmeli ama yeşile dönmüyorlar. Neden yeşile dönmüyorlar? Yeşile dönmüyorlar çünkü bu formüller, bu koşullu biçimlendirme formülleri tam burada, bunlara bakacağız. $ A $ 1 kullanmak için kodlanmışlardır. Oh, bu gerçekten kötü.
Pekala, hadi burada bazı şeyleri geliştirmeyi deneyelim. Yapabileceğim ilk şey, tüm bunlardan kurtulacağım ve bu orijinal veri kümesine geri döneceğim ve ikinci geçişte biraz daha akıllı olacağım ve gerçekten Sütun A'ya kilitlememize gerek olmadığını söyleyeceğim. Bu $ işaretinden kurtulacağım. Diğer bir deyişle, her zaman solumuzdaki sütun olacak, bu yüzden bu karışık bir referans olacak, ancak her zaman 1 doları göstermemiz gerekiyor. Bu kuralı düzenleyeceğiz, Tamam'ı tıklayın. Pekala şimdi, bugünün tarihi gibi sağa kopyalayıp yeni bir veri koyduğumuzda yapılan bir değişiklikle işe yarıyor. Tamam, bu harika. 6/26'da hayat harika olacak ve 6/27'de hayat harika olacak. Pekala, harika çalışıyor. Ama şimdi sayfada yer kalmadığımız sorunla karşılaşıyoruz ve Anderson'ın yaptığı şey aşağı iniyor,esasen yeni bir sıra başlatır ve yapıştırır ve bu 6/28 olur ama yeşile dönmüyor.
Neden yeşile dönmüyor? Yeşile dönmüyor çünkü 1. sıraya geri dönmek için hala $ kullanmak zorunda kaldım. Pekala, işte muamma, işte sorun şu. Şimdi ne yapıyorsunuz? Ve ben ciddiyim, şimdi ne yapıyorsun? Şimdi ne yapacağınızı YouTube yorumlarında duymak istiyorum.
Bilirsiniz, hey bak, bunun iyi olduğuna dair bir argüman var, burada durabiliriz çünkü A $ 1 kullanarak, bu şekilde başardık, 1. Günde hayat kolay, 2. Güne kopyala, hayat harika . 3. gün hayatı harika. Sadece her 4 günde bir buraya kopyaladığımızda, Anderson'ın girip koşullu biçimlendirmeyi ayarlaması, bunu düzenlemesi, kuralı düzenlemesi, 1'i 18 olarak değiştirmesi gerekir. Tamam'ı tıklayın, bu kuralı düzenleyin ve 1'i 18. Tamam'a tıklayın, Tamam'a tıklayın. Pekala, 4. Gün, 5. Gün için o küçük ayarlama, 6. Gün için kopyalayın ve ardından 7. Gün için kopyalayın. Ama hey, bununla yüzleşelim. Bu çalışma sayfası, bu koşullu biçimlendirme kuralları ile altı ay önce oluşturuldu ve sadece çalışması gerekiyor. Tekrar tekrar girip koşullu biçimlendirme yapmamıza gerek yok.
İlk tepkim, burada bazı formüllere sahip olduğum ve bu formüllerin mutlak referanslarla oluşturulduğu bir elektronik tablo gibi davranacaktım, ancak bu formüllerin üstünden veya altından kopyalanabilmesi ve kopya içinde göreceli olabilmesi için ihtiyacım var. - hem buraya kopyaladığımda hem de buraya kopyaladığımda. Pekala, ve bunun işe yaraması için, işleri ayarlarken mutlak referanslar kullanacağım ama sonra Bul ve Değiştir, Ctrl H'yi kullanacağım. Ve diyelim ki bu göreceli referanslardan kurtulalım, her $ A $ 1'i A1 olarak değiştirin, Tümünü Değiştir, Kapat'ı tıklayın ve şimdi bu blok, tüm bu formüller tamamen farklıdır, kopyalayıp yapıştırın ve çalışacaktır. Göreceli olacak. Ben de dedim ki, pekala, yapmamız gereken de bu. Bu $ları formülden çıkarmalıyız.Ve ben de bu koşullu biçimlendirme kurallarının her birini düzenlememe izin verecek bir makro yazacaktım. Pekala, ve bu makroyu yazmadan önce, bir koşullu biçimlendirme kuralını değiştirmenin makrosunu kaydedecektim, ama burada 14 koşullu biçimlendirme kuralı olduğundan değil. Buradaki 14 * 3, 42 koşullu biçimlendirme kurallarına bile değil. Burada yalnızca 3 koşullu biçimlendirme kuralı var ve bu 3 koşullu biçimlendirme kuralını bir hücre aralığına uyguluyoruz.Burada yalnızca 3 koşullu biçimlendirme kuralı var ve bu 3 koşullu biçimlendirme kuralını bir hücre aralığına uyguluyoruz.Burada yalnızca 3 koşullu biçimlendirme kuralı var ve bu 3 koşullu biçimlendirme kuralını bir hücre aralığına uyguluyoruz.
Yani bunu değiştirirsem, yapmam gereken ilk şey bu 3 koşullu biçimlendirme kuralını alıp 42 koşullu biçimlendirme kuralı yapmaktır. Ve sonra, alay etmeye başlıyorum çünkü Anderson buradan buraya kopyalarken, 42 yeni kural ve sonra 42 yeni kural getirecek. Ve muhtemelen 15 günlük bir sayfa boyunca, 600'den fazla kural, 600 farklı format getirecek ve bu korkunç olacak. Sonunda çok fazla biçimlendirme kuralı meselesine denk geleceksiniz, bunu ayarlayacak bir makromuz olsa bile ayarlamanın zor olacağını söylemem. Kurmak zor olacak.
Pekala, ne yapacağız peki? İşte bulduğum şey ve bundan daha iyi bir şeyin olup olmadığını öğrenmek istiyorum. Anderson'a dedim ki, “Biliyorsun, bak çok basit. Bunların hepsi tek bir hesaplamaya bakıyor ve bu hesaplama = BUGÜN - benim solumdaki tarih. " Ve bu yanıtı sağdaki küçük bir yardımcı sütunda alabilseydik, harika olmaz mıydı? Ve aslında, hiç $ kullanmak zorunda değiliz, tüm bu hücreleri o basit küçük formülle tamamen yere koyacağız.
Anderson'ın yüzüne bakışı görebiliyorum, oradaki fazladan şeylerin silinmesini istemiyor ama sorun değil. Bunu daha sonra gizleyebilir, gizleyebiliriz, böylece bu hücrelere geri dönebilir ve koşullu biçimlendirmemize geçebiliriz. Bu TODAY-A1'in tamamı basitçe C3'ü işaret edecek ve bu göreceli bir referans olacak. Başka bir deyişle, içinde bulunduğumuz hücre ne olursa olsun, her zaman sağdaki hücreye bakacağız, Tamam'a tıklayın, buna yazın, Tamam'a tıklayın. Bu veriyi buraya gizlemek istiyoruz, bu yüzden içeri gireceğim ve CTRL 1. Üç noktalı virgül kullanacağım - ;;;, Tamam'a tıklayın. Ben de orada aynısını yapacağım. F4'e basacağım, son işlemi tekrarlayacağım.
Şimdi, buradaki tuhaf şey, kendimi bu küçük kısmın, bu boş sütunun her şeyin bir parçası olduğuna ikna etmem gerekiyor. Bu yüzden, kopyalayıp yapıştırdığımda griyi de dahil etmem gerekeceğini kendime hatırlatmak için oraya sadece açık gri eklemek istiyorum. Pekala, işte şimdi testimiz. Bu CTRL C'yi seçeceğim ve sonra aslında oraya yapıştırıp buraya yapıştıracağım. Pekala, büyük testler 26/6, 6/27’ye git, bunu sarıya değiştir, 6/28’e gel, yeşile dönmeli. Güzel!
Pekala, şimdi işe yarıyor, esasen bu koşullu biçimlendirmeyi karma referansı göreceli bir referansla değiştirdik ve görece, nispeten iyi gitmeliyiz.
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.
Tamam, işte buradayız. Bunu hızlandırın, nasıl çalıştığını görmek için sonunda izleyebilirsiniz. Burada sadece bir test yapıyorum. CTRL; maviye dönecek. Bu 6 / 26'ya geri dönerse, kırmızıya dönecektir. Ve bugünse, işe yaramıyor. Bu doğru çünkü yapacağım şey şu. Dördüncü kuralım, yeşil bugün veya dün geldi, bunu varsayılan olarak kullanacağım. Bu diğer üç kuraldan hiçbiri doğru değilse, o zaman bana burada uğraşmam gereken bir kural daha az verecek olan yeşil olacaktır. Peki.
Dosyayı indir
Örnek dosyayı buradan indirin: Podcast2105.xlsx
