Konfigurowanie warunkowej formuły formatowania korzystającej z odwołania mieszanego. Większość formuł formatowania warunkowego wymaga odwołania bezwzględnego. Ale ten arkusz kalkulacyjny do śledzenia ciężarówek na placu wymaga
Obejrzyj wideo
- Anderson szuka sposobu na kopiowanie bloków danych zawierających mieszane formatowanie warunkowe
- Czy istnieje sposób na usunięcie znaków dolara po skonfigurowaniu formatowania warunkowego?
- Nie - nie bez wprowadzenia dziesiątek nowych zasad
- Moje rozwiązanie: komórki pomocnicze, które używają odwołań względnych, aby zastąpić odwołanie mieszane w formatowaniu warunkowym
- Inne techniki w tym odcinku:
- Jeśli masz cztery reguły formatowania warunkowego, ustaw pierwsze 3, a następnie ustaw kolor domyślny czwartej reguły
- Outtake # 1: Naciśnij klawisz F2, aby zatrzymać wstawianie odwołań do komórek w oknie dialogowym formatowania warunkowego przez program Excel
- Wynik nr 2: konfigurowanie formatowania warunkowego
Transkrypcja wideo
Naucz się programu Excel z podcastu, odcinek 2105: Kopiowanie formatu warunkowego z mieszanymi odniesieniami
Hej, witaj ponownie w netcastie. Dziś będzie to skomplikowane. Wczoraj prowadziłem seminarium i jedna z osób na seminarium, Anderson, miała ciekawy arkusz kalkulacyjny z problemem. W porządku, Anderson zarządza stocznią - przyjeżdżają przyczepy, a przyczepy muszą zostać rozładowane w ciągu trzech dni. W porządku, więc to jest - zaczyna, wiesz, to był dzień, to były zwiastuny, które przybyły, a potem ma skonfigurowane formatowanie warunkowe, które po rozładowaniu przyczepy zmienia kolor na niebieski. Kiedy coś jest niebieskie, wszystko jest super. Ale potem chce oznaczać rzeczy kolorami. Jeśli coś dotarło dziś lub wczoraj, zostanie oznaczone kolorem zielonym. Więc dzisiaj jest 29 czerwca 2017, więc to dotarło wczoraj i wszystko, co nie jest rozładowane, jest zielone, ale jeśli ma więcej niż jeden dzień,Chcemy wyróżniać rzeczy na żółto, a kiedy mają więcej niż dwa dni, to właśnie te problemy chcemy wyróżniać na czerwono. I nie chodzi o to, wiesz, to jeden arkusz roboczy do zarządzania całym podwórkiem, prawda? Nie chodzi o to, że istnieje arkusz na rzeczy, które dotarły 26., a drugi na 27 i jeszcze jeden na 28. I wiesz, że trudność polega na tym, że gdy nadchodzi nowy dzień, albo kopiują poprzedni dzień tutaj, albo w dół, tutaj.albo kopiują poprzedni dzień tutaj lub w dół tutaj.albo kopiują poprzedni dzień tutaj lub w dół tutaj.
W porządku, celem tego filmu nie jest to, jak skonfigurować to formatowanie warunkowe. Więc zamierzam to przyspieszyć, ale jeśli interesuje cię, jak ustawić to formatowanie warunkowe, umieszczę wersję bez przyspieszenia jako przechwytywanie na końcu wideo.
Ok, więc jesteśmy. Przyspieszono, możesz obejrzeć na koniec, aby zobaczyć, jak to działa. Robię tutaj test, CTRL; zmieni się na niebieski. Jeśli wróci do 26.06, zmieni się na czerwony, a jeśli to dzisiaj, to nie działa. Zgadza się, ponieważ oto, co zamierzam zrobić, moja czwarta reguła zielona dotarła dzisiaj lub wczoraj, po prostu użyję tego jako domyślnego. Jeśli żadna z pozostałych trzech zasad nie jest prawdziwa, to będzie zielone, że da mi to jedną regułę mniej, z którą mam tutaj do czynienia, dobrze?
Okej, więc jesteśmy teraz w punkcie, w którym zasadniczo mamy problem Andersona. Zamierzam wstawić 25.06.2017, wszystkie zmienią kolor na czerwony z wyjątkiem tych, które zostały rozładowane. A teraz życie toczy się dalej, jest następny dzień. Mamy kilka zwiastunów 26 czerwca, więc Anderson kopiuje te dane, wklej tutaj, formatuje autodopasowanie kolumny, a to będzie zwiastun 15. Kliknij, aby skopiować to w dół i zwiększyć, pozbyć się tych, które dotarły. Więc ten przybył dzisiaj, więc wszystkie powinny zmienić kolor na zielony, ale nie zmieniają się na zielone. Dlaczego nie zmieniają się na zielone? Nie zmieniają koloru na zielony, ponieważ te formuły, te formuły formatowania warunkowego tutaj, przyjrzymy się im. Są na stałe zakodowane, aby używać $ A $ 1. Och, to jest naprawdę złe.
W porządku, więc spróbujmy poprawić to tutaj. Pierwszą rzeczą, jaką mogę zrobić, jest pozbycie się ich wszystkich i powrót do pierwotnego zestawu danych, trochę sprytniejszego drugiego przejścia i powiem, że tak naprawdę nie musimy blokować tego w kolumnie A. Pozbędę się tego znaku $. Innymi słowy, zawsze będzie to kolumna po lewej stronie, więc będzie to mieszane odniesienie, ale zawsze musimy wskazywać na 1 $. Zmodyfikujemy tę regułę, kliknij OK. W porządku, z tą jedną zmianą, kiedy skopiowaliśmy w prawo i wstawiliśmy nowe dane, jak dzisiejsza data, działa. Ok, więc to jest świetne. Życie będzie wspaniałe 26 czerwca, a życie będzie wspaniałe 27 czerwca. W porządku, działa świetnie. Ale teraz napotykamy problem polegający na tym, że brakuje nam miejsca na stronie, więc to, co robi Anderson, spada,zasadniczo rozpoczyna nowy rząd i wkleja i będzie to 6/28, ale nie zmieni koloru na zielony.
Dlaczego nie zmienia koloru na zielony? Nie zmienia koloru na zielony, ponieważ nadal musiałem użyć $, aby wrócić do 1. W porządku, a więc teraz jest zagadka, oto problem. Co teraz robisz? I mówię poważnie, co teraz robisz? Chcę usłyszeć w komentarzach YouTube, co byś teraz zrobił.
Wiesz, więc hej, spójrz, jest argument, że to jest dobre, możemy zatrzymać się tutaj, ponieważ używając 1 dolara, zrobiliśmy to w ten sposób, życie jest łatwe w dniu 1, skopiuj do dnia 2, życie jest wspaniałe . Życie w trzecim dniu jest świetne. Tylko co 4 dni, kiedy kopiujemy tutaj, Anderson musiałby wejść i ustawić formatowanie warunkowe, edytować to, edytować regułę, zmienić 1 na 18. Kliknij OK, edytuj tę regułę i zmień 1 na 18. Kliknij OK, kliknij OK. W porządku, więc dzień 4, ta mała kopia korekty na dzień 5, skopiowanie na dzień 6, a następnie skopiowanie na dzień 7. Powtórz te kroki. Ale hej, spójrzmy prawdzie w oczy. Ten arkusz roboczy został utworzony sześć miesięcy temu z tymi warunkowymi regułami formatowania i po prostu muszą działać. Nie musimy w kółko wchodzić i wielokrotnie wykonywać formatowania warunkowego.
Moja pierwsza reakcja była taka, że zamierzam udawać, że to arkusz kalkulacyjny, w którym mam tutaj kilka formuł, a te formuły zostały zbudowane z odniesieniami bezwzględnymi, ale potrzebuję tych formuł, aby można je było skopiować w górę lub w dół i być względnym w kopii - zarówno wtedy, gdy kopiuję tutaj, jak i kiedy kopiuję tutaj. W porządku, aby to zadziałało, zamierzam używać bezwzględnych odniesień, kiedy konfiguruję, ale potem zamierzam użyć funkcji Znajdź i zamień, Ctrl H. I powiedzmy, że pozbędziemy się tych względnych odniesień, zmień każdy $ A 1 na A1, zamień wszystko, kliknij Zamknij, a teraz ten blok, wszystkie te formuły są różne aż do końca, kopiuj, wklej i wklej i zadziała. To będzie względne. Więc powiedziałem, w porządku, to jest to, co musimy zrobić. Musimy usunąć te $ z formuły.Zamierzałem więc napisać makro, które pozwoliłoby mi edytować każdą z tych reguł formatowania warunkowego. W porządku, a zanim napisałem to makro, zamierzałem nagrać makro zmieniające jedną regułę formatowania warunkowego, ale nie chodzi o to, że jest tutaj 14 reguł formatowania warunkowego. Nie dotyczy to nawet reguł formatowania warunkowego 14 * 3, 42. Są tutaj tylko 3 reguły formatowania warunkowego i stosujemy te 3 reguły formatowania warunkowego do zakresu komórek.to tylko 3 reguły formatowania warunkowego, a my stosujemy te 3 reguły formatowania warunkowego do zakresu komórek.to tylko 3 reguły formatowania warunkowego i stosujemy te 3 reguły formatowania warunkowego do zakresu komórek.
Więc gdybym to zmienił, pierwszą rzeczą, którą musiałbym zrobić, jest przyjęcie tych 3 reguł formatowania warunkowego i uczynienie z nich 42 reguł formatowania warunkowego. A potem zaczynam się wzdrygać, ponieważ Anderson kopiuje stąd do tego miejsca, ma zamiar wprowadzić 42 nowe zasady, a następnie 42 nowe zasady. I w ciągu jednej kartki papieru z prawdopodobnie 15 dniami wprowadzi ponad 600 reguł, 600 różnych formatów i to będzie po prostu okropne. W końcu trafisz na zbyt dużą regułę formatowania, nie wspominając o tym, że będzie to trudne do skonfigurowania, nawet jeśli mamy makro do skonfigurowania. To będzie trudne do skonfigurowania.
W porządku, więc co robimy? Oto, co wymyśliłem i chcę usłyszeć, czy masz coś lepszego niż to. Powiedziałem do Andersona, powiedziałem: „Wiesz, spójrz, to całkiem proste. Wszystkie te obliczenia dotyczą jednego obliczenia, a to obliczenie to = DZIŚ - data, która jest po lewej stronie mnie. ” I czy nie byłoby fajnie, gdybyśmy mogli mieć tę odpowiedź w małej kolumnie pomocników tutaj po prawej stronie. W rzeczywistości nie musimy w ogóle używać znaku $, po prostu umieścimy wszystkie te komórki na samym dole za pomocą tej prostej małej formuły.
Widzę wyraz twarzy Andersona, on nie chce wymazać tych dodatkowych rzeczy, ale to w porządku. Możemy to ukryć, ukryć później, więc wrócimy do tych komórek i przejdziemy do naszego formatowania warunkowego. Cały ten DZIŚ-A1 będzie po prostu wskazywał na C3 i będzie to odniesienie względne. Innymi słowy, niezależnie od komórki, w której się znajdujemy, zawsze będziemy patrzeć do komórki po prawej stronie, kliknij OK, napisz w tej komórce, kliknij OK. Chcemy ukryć te dane tutaj, więc wejdę i CTRL 1. Użyję trzech średników - ;;;, kliknij OK. Zrobię tam dokładnie to samo. Nacisnę F4, powtórzę ostatnią akcję.
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.
Ok, więc jesteśmy. Przyspieszono, możesz obejrzeć na koniec, aby zobaczyć, jak to działa. Robię test tutaj. CTRL; zmieni się na niebieski. Jeśli wróci do 6/26, zmieni się na czerwony. A jeśli to dzisiaj, to nie działa. Zgadza się, ponieważ oto, co mam zamiar zrobić. Moja czwarta reguła, zielony, dotarła dziś lub wczoraj, użyję jej jako domyślnej. Jeśli żadna z pozostałych trzech zasad nie jest prawdziwa, to będzie zielone i da mi jedną regułę mniej, z którą muszę się tutaj zmierzyć. W porządku.
Pobieranie pliku
Pobierz przykładowy plik tutaj: Podcast2105.xlsx
