Vegyes hivatkozást használó feltételes formázási képlet beállítása. A legtöbb feltételes formázási képlet abszolút hivatkozást igényel. De ehhez a táblához az udvaron lévő teherautók nyomon követése megköveteli
Videót nézni
- Anderson azt keresi, hogy miként lehetne vegyes feltételes formázást tartalmazó adatblokkokat lemásolni
- Van-e mód a dollárjelek eltávolítására a feltételes formázás beállítása után?
- Nem - nem tucatnyi új szabály bevezetése nélkül
- Megoldásom: olyan segítő cellák, amelyek relatív hivatkozásokat használnak a vegyes hivatkozás helyettesítésére a feltételes formázásban
- Egyéb technikák ebben az epizódban:
- Ha négy feltételes formázási szabálya van, állítsa be az első 3-at, majd tegye a negyedik szabályt az alapértelmezett színre
- 1. feladat: Nyomja meg az F2 billentyűt, hogy megakadályozza az Excel cellahivatkozások beszúrását a feltételes formázás párbeszédpanelen
- 2. feladat: feltételes formázás beállítása
Videó átirat
Ismerje meg az Excel programot a Podcast 2105 epizódból: Feltételes formátum másolása vegyes hivatkozásokkal
Hé, üdvözlöm a netcaston. Ez ma bonyolult lesz. Tegnap szemináriumot tartottam, és a szeminárium egyik tagjának, Andersonnak érdekes táblája volt egy problémával. Rendben, Anderson pedig egy udvart kezel - érkeznek a pótkocsik, és a pótkocsikat három napon belül ki kell rakodni. Rendben, így van - indul ki, tudod, ez volt az a nap, ezek voltak a pótkocsik, amelyek megérkeztek, majd feltételes formázást állított be, hogy ha az utánfutót kirakodják, kékre vált. Ha valami kék, minden nagyszerű. De aztán színezni akarja a dolgokat. Ha valami ma vagy tegnap érkezett meg, akkor zöld színű lesz. Tehát ma 2017. június 29-e van, tehát tegnap érkezett meg, és minden, ami nincs kirakva, zöld, de ha több mint egy napos,sárga színnel szeretnénk kiemelni a dolgokat, és amikor két napnál régebbi, akkor ezeket a problémákat szeretnénk piros színnel kiemelni. És nem erről van szó, tudod, ez egy munkalap az egész udvar kezeléséhez, igaz? Nem arról van szó, hogy van egy lap a 26-án érkezett dolgokról, és egy másik 27-én, egy másik pedig 28-án. És tudod, hogy az a nehézség, hogy új nap következik, vagy átmásolják az előző napot ide, vagy ide.vagy átmásolják az előző napot ide vagy le ide.vagy átmásolják az előző napot ide vagy le ide.
Rendben, ennek a videónak nem az a lényege, hogy hogyan állítsuk be ezt a feltételes formázást. Tehát át fogom gyorsítani ezt, de ha érdekel, hogyan állítsd be ezt a feltételes formázást, akkor a videó végére kiteszem a sebesség nélküli verziót.
Oké, szóval ott vagyunk. Ennek felgyorsításával a végén megnézheti, hogy működik. Csak tesztet csinálok itt, CTRL; kékre változik. Ha ez visszatér a 6/26-ra, akkor pirosra vált, és ha ma van, akkor nem működik. Így van, mert itt fogom csinálni, ma vagy tegnap megérkezett a negyedik zöld szabályom, csak ezt fogom használni alapértelmezettként. Ha a másik három szabály egyike sem igaz, akkor zöld lesz, hogy egy kevesebb szabályt ad nekem, amellyel itt foglalkoznom kell, rendben?
Oké, szóval most azon a ponton vagyunk, ahol lényegében Anderson problémája van. Be fogom adni 2017.06.25., Ezek mind kivörösödnek, kivéve azokat, amelyeket kiraktak. És most halad az élet, másnap van. Van néhány pótkocsi a 6/26-os napon, és Anderson lemásolja ezeket az adatokat, ide illessze be, formázza az AutoFit oszlopot, és ez a Trailer 15 lesz. Kattintson a gombra, hogy lemásolja és lefelé növelje, megszabaduljon a megérkezettektől. Tehát ez ma megérkezett, úgyhogy ezeknek mind zöldnek kell lenniük, de nem zöldek. Miért nem zöldülnek meg? Nem válnak zöldellővé, mert ezeket a képleteket, ezeket a feltételes formázási képleteket itt nézzük meg. Nehezen kódoltak a $ A $ 1 használatához. Ó, ez nagyon rossz.
Rendben, igyekezzünk itt javítani a dolgokon. Az első dolog, amit tehetek, meg fogok szabadulni mindezektől, visszatérek ehhez az eredeti adathalmazhoz, és egy kicsit okosabbak leszek a második menetben, és azt mondom, hogy valójában nem kell bezárnunk az A oszlopba. Megszabadulok ettől a $ jeltől. Más szavakkal, ez mindig a baloldali oszlop lesz tőlünk, tehát vegyes hivatkozás lesz, de mindig az 1 dollárra kell mutatnunk. Szerkesztjük ezt a szabályt, kattintson az OK gombra. Rendben, ezzel az egyetlen változtatással, amikor jobbra másoltunk, és új adatokat tettünk be, mint a mai dátum, ez működik. Oké, szóval ez nagyszerű. Az élet nagyszerű lesz 6/26-án, és az élet nagy lesz 6/27-én. Rendben, remekül működik. De most olyan problémába ütközünk, amikor elfogy a hely az oldalon, és ami Anderson tette, az lecsökken,lényegében új sort kezd és beilleszt, és ez 6/28 lenne, de nem vált zöldre.
Miért nem válik zölddé? Nem vált zöldre, mert még mindig a $ -ot kellett használnom ahhoz, hogy visszajussak az 1. Rendben, és így itt van a talány, itt a probléma. Mit csinálsz most? És komolyan mondom, mit csinálsz most? A YouTube-hozzászólásokban szeretném hallani, mit tennél most.
Tudod, hát szia, nézd, felmerült egy érv, hogy ez jó, itt megállhatnánk, mert az A $ 1 használatával így tettük meg, az élet könnyű az 1. napon, másold át a 2. napra, az élet nagyszerű . A 3. nap élete nagyszerű. Csak minden 4. napon, amikor ide másoljuk, Andersonnak be kell lépnie és be kell állítania a feltételes formázást, szerkesztheti ezt, módosíthatja a szabályt, módosíthatja az 1 értéket 18-ra. Kattintson az OK gombra, szerkessze ezt a szabályt és változtassa meg ezt 18. Kattintson az OK gombra, majd az OK gombra. Rendben, tehát a 4. nap, az a kis kiigazító másolat az 5. napra, a 6. napra, majd a 7. napra másol. Végezze el újra ezeket a lépéseket. De hát lássuk be. Ezt a munkalapot hat hónappal ezelőtt állították össze ezekkel a feltételes formázási szabályokkal, és csak működniük kell. Nem kell újra és újra és újra feltételes formázást végezni.
Az első reakcióm az volt, hogy úgy teszek, mintha ez egy táblázat lenne, ahol van néhány képletem, és ezek a képletek abszolút hivatkozásokkal készültek, de szükségem van ezekre a képletekre, hogy átmásolhassam őket, vagy hogy relatívak legyek a másolaton belül - mind ide másolva, mind ide másolva. Rendben, és hogy ez működjön, abszolút referenciákat fogok használni, amikor beállítom a dolgokat, de majd a Keresés és csere, Ctrl H-t fogom használni. És tegyük fel, hogy szabaduljunk meg azoktól a relatív hivatkozásoktól, minden $ A $ 1 értéket módosítson A1-re, Cserélje ki az összeset, kattintson a Bezárás gombra, és most ez a blokk, ezek a képletek egészen mások, másolja, illessze be és illessze be, és működni fog. Viszonylagos lesz. Szóval azt mondtam, rendben, hát ezt kell tennünk. Ki kell vennünk azokat a dollárokat a képletből.Ezért makrót akartam írni, amely lehetővé teszi számomra a feltételes formázási szabályok mindegyikének szerkesztését. Rendben, és mielőtt ezt a makrót írtam, rögzíteni fogom egy feltételes formázási szabály módosításának makróját, de nem arról van szó, hogy itt 14 feltételes formázási szabály van. Még itt sem vonatkozik a 14 * 3, 42 feltételes formázási szabályokra. Itt csak 3 feltételes formázási szabály van, és ezt a 3 feltételes formázási szabályt alkalmazzuk egy cellatartományra.Itt csak 3 feltételes formázási szabály van, és ezt a 3 feltételes formázási szabályt alkalmazzuk egy cellatartományra.Itt csak 3 feltételes formázási szabály van, és ezt a 3 feltételes formázási szabályt alkalmazzuk egy cellatartományra.
Tehát, ha ezen változtatnék, az első dolog, amit tennem kell, hogy megfogadjam ezt a 3 feltételes formázási szabályt, és 42 feltételes formázási szabálygá tegyem őket. És akkor kezdek görcsölni, mert ahogy Anderson innen másol ide, 42 új szabályt, majd 42 új szabályt vezet be. És egy valószínűleg 15 napos papírlap alatt több mint 600 szabályt, 600 különböző formátumot vezet be, és ez csak szörnyű lesz. Végül el fogja érni a túl sok formázási szabály dolgát, nem beszélve arról, hogy nehéz lesz beállítani akkor is, ha van egy makrónk a beállításához. Nehéz lesz beállítani.
Jól van, akkor mit csináljunk? Íme, mit találtam ki, és szeretném hallani, ha van valami ennél jobb. Mondtam Andersonnak, azt mondtam: „Tudod, nézd, ez nagyon egyszerű. Mindezek egy számítást vizsgálnak, és ez a számítás = TODAY- a tőlem balra lévő dátum. És nem lenne jó, ha erre a válaszra egy kis segítő oszlopban lennénk, jobbra. És valójában egyáltalán nem kell használnunk semmilyen dollárt, csak az összes ilyen cellát teljesen le fogjuk rakni ezzel az egyszerű kis képlettel.
Látom Anderson arcának pillantását, nem akarja, hogy ezeket az extra dolgokat kitöröljék, de ez rendben van. Ezt később elrejthetjük, elrejthetjük, így visszatérünk ezekbe a cellákba, és belemegyünk a feltételes formázásba. Ez az egész TODAY-A1 egyszerűen a C3-ra mutat, és ez relatív referencia lesz. Tehát más szavakkal, bármelyik cellában vagyunk, mindig a jobb oldali cellába fogunk nézni, kattintsunk az OK gombra, írjunk ennél, kattintsunk az OK gombra. Ezeket az adatokat ide akarjuk rejteni, így bemegyek és a CTRL 1-et használom. A három pontosvesszőt fogom használni - ;;;, kattintson az OK gombra. Ugyanezt fogom csinálni ott. Megnyomom az F4 billentyűt, megismételem az utolsó műveletet.
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é, szóval ott vagyunk. Ennek felgyorsításával a végén megnézheti, hogy működik. Csak tesztet csinálok itt. CTRL; kékre változik. Ha ez visszatér a 6/26-ra, akkor pirosra vált. És ha ma van, akkor nem működik. Így van, mert ezt fogom tenni. A negyedik szabályom, a zöld ma vagy tegnap érkezett meg, csak ezt fogom használni alapértelmezettként. Ha a másik három szabály egyike sem igaz, akkor zöld lesz, hogy egy kevesebb szabályt ad nekem, amellyel itt foglalkoznom kell. Rendben.
Fájl letöltése
Töltse le a minta fájlt innen: Podcast2105.xlsx
