Uzziniet Excel nosacītā formāta jauktas atsauces - Excel padomi

Nosakot nosacītu formatēšanas formulu, kurā tiek izmantota jaukta atsauce. Lielākajai daļai nosacīto formatēšanas formulu ir nepieciešama absolūta atsauce. Bet šī izklājlapa kravas automašīnu izsekošanai pagalmā prasa

Skatīties video

  • Andersons meklē veidu, kā varētu kopēt tādu datu blokus, kas satur jauktu nosacītu formatējumu
  • Vai ir iespējams noņemt dolāra zīmes, kad ir iestatīts nosacīts formatējums?
  • Nē - ne bez desmitiem jaunu noteikumu ieviešanas
  • Mans risinājums: palīgu šūnas, kurās nosacītas formatēšanas gadījumā jauktās atsauces aizstāj relatīvās atsauces
  • Citi paņēmieni šajā epizodē:
  • Ja jums ir četri nosacījuma formatēšanas kārtulas, iestatiet pirmos 3 un pēc tam iestatiet ceturto kārtulu par noklusējuma krāsu
  • Izpilde Nr. 1: nospiediet taustiņu F2, lai apturētu Excel ievietot šūnu atsauces nosacītā formatēšanas dialoglodziņā
  • Izpilde Nr. 2: nosacīta formatējuma iestatīšana

Video atšifrējums

Uzziniet programmu Excel no Podcast epizodes 2105: Nosacītā formāta kopēšana ar jauktām atsaucēm

Sveiki, sveicināti atkal netcast. Šodien tas būs sarežģīts. Es vakar rīkoju semināru, un vienam no semināra cilvēkiem Andersonam bija interesanta izklājlapa ar problēmu. Labi, un Andersons pārvalda pagalmu - ierodas piekabes, un treileri ir jāizkrauj trīs dienu laikā. Labi, tā tas ir - viņš sāk darbu, jūs zināt, šī bija diena, tās bija piekabes, kas ieradās, un pēc tam viņam ir iestatīts nosacīts formatējums, ka pēc piekabes izkraušanas tā mainās uz zilu. Kad kaut kas ir zils, viss ir lieliski. Bet tad viņš vēlas krāsot lietas. Ja kaut kas ieradās šodien vai vakar, tā krāsa tiek kodēta kā zaļa. Tātad šodien ir 2017. gada 29. jūnijs, tāpēc tas ieradās vakar, un viss, kas nav izkrauts, ir zaļš, bet, kad tas ir vairāk nekā vienu dienu vecs,mēs vēlamies izcelt lietas kā dzeltenas, un, kad tas ir vairāk nekā divas dienas vecs, tās ir problēmas, kuras mēs vēlamies izcelt lietas kā sarkanas. Un tas nav tā, ka jūs zināt, šī ir viena darblapa, lai pārvaldītu visu pagalmu, vai ne? Nav tā, ka tur būtu lapa ar lietām, kas pienāca 26. datumā, un cita - 27. datumā, un cita - 28. datumā. Un jūs zināt, ka grūtības sagādā jaunā diena, viņi vai nu kopē iepriekšējo dienu šeit, vai arī šeit.viņi vai nu kopē iepriekšējo dienu šeit, vai arī šeit.viņi vai nu kopē iepriekšējo dienu šeit, vai arī šeit.

Labi, šī videoklipa būtība nav par to, kā iestatīt šo nosacīto formatējumu. Tāpēc es to paātrināšu, taču, ja jūs interesē, kā iestatīt šo nosacīto formatējumu, videoklipa beigās es ievietošu nepaaugstināto versiju.

Labi, tātad, mēs esam. To paātrinot, beigās varat skatīties, kā tas darbojas. Tikai veicot testu šeit, CTRL; mainīsies uz zilu. Ja tas atgriežas līdz 6/26, tas mainīsies uz sarkanu un, ja tas ir šodien, tas nedarbojas. Pareizi, jo lūk, ko es darīšu, mans ceturtais kārtulas zaļais pienāca šodien vai vakar, es to vienkārši izmantošu kā noklusējumu. Ja neviens no šiem trim pārējiem noteikumiem nav patiess, tad būs zaļš, ka tas man iedos vienu noteikumu mazāk, ar kuru man šeit jātiek galā, labi?

Labi, tāpēc mēs tagad esam tajā vietā, kur mums būtībā ir Andersona problēma. Es ielikšu 25.06.2017. Šie visi kļūs sarkani, izņemot tos, kas ir izkrauti. Un tagad dzīve iet uz priekšu, tā ir nākamā diena. Mēs saņēmām dažas piekabes 6/26, un tāpēc Andersons kopē šos datus, ielīmē šeit, formatē kolonnu AutoFit, un tas būs Treileris 15. Pārejiet uz klikšķi, lai kopētu to uz leju un palielinātu, atbrīvojieties no tiem, kas ieradās. Un tāpēc šis ieradās šodien, tāpēc tiem visiem vajadzētu kļūt zaļiem, bet tie nekļūst zaļi. Kāpēc viņi nekļūst zaļi? Tās nepārvēršas zaļā krāsā, jo šīs formulas, šīs nosacītā formatēšanas formulas šeit, mēs tās apskatīsim. Tie ir grūti kodēti, lai izmantotu $ A $ 1. Ak, tas ir patiešām slikti.

Labi, tāpēc mēģināsim šeit uzlabot lietas. Pirmā lieta, ko es varu darīt, es atbrīvošos no visiem šiem un atgriezīšos pie šīs sākotnējās datu kopas, un es būšu mazliet gudrāks otrajā piegājienā un saku, ka mums tas faktiski nav jānoslēdz A kolonnā. Es tikšu vaļā no šīs $ zīmes. Citiem vārdiem sakot, tā vienmēr būs kolonna pa kreisi no mums, tāpēc tā būs jaukta atsauce, bet mums vienmēr ir jānorāda uz $ 1. Mēs rediģēsim šo kārtulu, noklikšķiniet uz Labi. Labi tagad, ar šīm vienām izmaiņām, kad mēs nokopējām pa labi un ievietojām jaunus datus, piemēram, šodienas datums, tas darbojas. Labi, tāpēc tas ir lieliski. Dzīve būs lieliska 6/26, un dzīve būs lieliska 6/27. Labi, lieliski strādā. Bet tagad mēs saskaramies ar problēmu, kad mums pietrūkst vietas lapā, un tas, ko Andersons ir darījis, iet uz leju,būtībā sāk jaunu rindu un ielīmē, un tas būtu 6/28, bet tas nekļūst zaļš.

Kāpēc tas nekļūst zaļš? Tas nav kļuvis zaļš, jo man joprojām bija jāizmanto $, lai atgrieztos 1. Labi, un tāpēc tagad šeit ir mīkla, šeit ir problēma. Ko tu tagad dari? Un es nopietni, ko jūs tagad darāt? Es vēlos YouTube komentāros dzirdēt, ko jūs darītu tagad.

Jūs zināt, tāpēc, skatieties, ir izteikts arguments, ka tas ir labi, mēs varētu apstāties tieši šeit, jo, izmantojot A $ 1, mēs to paveicām, 1. dienā dzīve ir viegla, kopējiet uz 2. dienu, dzīve ir lieliska . 3. dienas dzīve ir lieliska. Tikai katru 4. dienu, kad mēs šeit nokopējam, Andersonam būs jāieiet un jāiestata nosacīts formatējums, rediģēt šo, rediģēt kārtulu, mainīt šo vērtību uz 18. Noklikšķiniet uz Labi, rediģējiet šo kārtulu un mainiet šo 18. Noklikšķiniet uz Labi, noklikšķiniet uz Labi. Labi, tāpēc 4. diena ir tā mazā pielāgojuma kopija 5. dienai, 6. un 6. dienai. Veiciet šīs darbības vēlreiz. Bet, re, pieņemsim, ka būsim pretī. Šī darblapa tika izveidota pirms sešiem mēnešiem ar šiem nosacītā formatēšanas noteikumiem, un viņiem vienkārši jāstrādā. Mums nav jāiet iekšā un jāveic nosacīta formatēšana atkal un atkal un atkal.

Mana pirmā reakcija bija, ka es izlikšos, ka šī ir izklājlapa, kur man ir dažas formulas, un šīs formulas tika veidotas ar absolūtām atsaucēm, bet man ir vajadzīgas šīs formulas, lai tās varētu kopēt virs vai uz leju, un es būtu relatīva kopijā - gan tad, kad kopēju uz šejieni, gan tad, kad kopēju šeit. Labi, un, lai tas darbotos, es iestatīšu lietas, izmantojot absolūtās atsauces, bet pēc tam es izmantoju Atrast un aizstāt, Ctrl H. Un pieņemsim, ka tiksim vaļā no šīm relatīvajām atsaucēm, mainīt katru $ A $ 1 uz A1, Aizstāt visu, noklikšķiniet uz Aizvērt un tagad šis bloks, visas šīs formulas ir atšķirīgas līdz galam, kopējiet, ielīmējiet un ielīmējiet, un tas darbosies. Tas būs relatīvs. Tāpēc es teicu, labi, labi, tas mums ir jādara. Mums šie $ ir jāizņem no formulas.Tāpēc es gatavojos uzrakstīt makro, kas ļautu man rediģēt katru no šiem nosacītā formatēšanas noteikumiem. Labi, un pirms es uzrakstīju šo makro, es ierakstīšu viena nosacītā formatēšanas kārtulas maiņas makro, taču nav tā, ka šeit būtu 14 nosacījuma formatēšanas kārtulas. Šeit nav pat 14 * 3, 42 nosacītā formatēšanas kārtulas. Šeit ir tikai 3 nosacījuma formatēšanas kārtulas, un mēs šos 3 nosacītā formatēšanas noteikumus lietojam šūnu diapazonam.Šeit ir tikai 3 nosacījuma formatēšanas kārtulas, un mēs šos 3 nosacītā formatēšanas noteikumus lietojam šūnu diapazonam.Šeit ir tikai 3 nosacījuma formatēšanas kārtulas, un mēs šos 3 nosacītā formatēšanas noteikumus lietojam šūnu diapazonam.

Tātad, ja es to mainītu, pirmā lieta, kas man būtu jādara, ir pieņemt šos 3 nosacītā formatēšanas noteikumus un padarīt tos par 42 nosacījuma formatēšanas noteikumiem. Un tad es sāku raustīties, jo, Andersonam kopējot no šejienes uz šejieni, viņš ieviesīs 42 jaunus noteikumus un pēc tam 42 jaunus noteikumus. Un vienas papīra lapas laikā, iespējams, 15 dienu laikā viņš ieviesīs vairāk nekā 600 noteikumus, 600 dažādus formātus, un tas būs vienkārši šausmīgi. Jūs galu galā sasniegsiet pārāk daudz formatēšanas kārtulas, nemaz nerunājot par to, ka to būs grūti iestatīt, pat ja mums ir makro, lai to iestatītu. To būs grūti iestatīt.

Labi, ko tad mēs darām? Lūk, ko es izdomāju, un es vēlos dzirdēt, vai jums ir kaut kas labāks par to. Es teicu Andersonam, es teicu: “Zini, izskatās, ka tas ir diezgan vienkārši. Visi šie dati tiek aplūkoti vienā aprēķinā, un aprēķins ir = ŠODIEN - datums, kas atrodas man pa kreisi. " Un vai tas nebūtu forši, ja mums būtu šāda atbilde nelielā palīga slejā tepat pa labi. Un patiesībā mums vispār nav jāizmanto nekādi $, mēs vienkārši ievietosim visas šīs šūnas līdz galam ar šo vienkāršo mazo formulu.

Es redzu Andersona sejas skatienu, viņš nevēlas, lai šie papildu sīkumi tur tiktu izdzēsti, bet tas ir labi. Mēs varam to paslēpt, paslēpt vēlāk, lai mēs atgrieztos šajās šūnās un pārietu uz mūsu nosacīto formatējumu. Tas viss TODAY-A1 vienkārši norāda uz C3, un tā būs relatīva atsauce. Citiem vārdiem sakot, neatkarīgi no šūnas, kurā mēs atrodamies, mēs vienmēr meklēsim šūnu labajā pusē, noklikšķiniet uz Labi, rakstiet šajā, noklikšķiniet uz Labi. Mēs vēlamies paslēpt šos datus šeit, tāpēc es iešu un ievadīšu CTRL 1. Es izmantošu trīs semikolus - ;;;, noklikšķiniet uz Labi. Es tur darīšu tieši to pašu. Es nospiedīšu F4, atkārtoju pēdējo darbību.

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.

Labi, tātad, mēs esam. To paātrinot, beigās varat skatīties, kā tas darbojas. Vienkārši šeit veicot pārbaudi. CTRL; mainīsies uz zilu. Ja tas atgriežas līdz 6/26, tas mainīsies uz sarkanu. Un, ja tas ir šodien, tas nedarbojas. Pareizi, jo lūk, ko es darīšu. Mans ceturtais noteikums, zaļš pienāca šodien vai vakar, es to vienkārši izmantošu kā noklusējumu. Ja neviens no šiem trim pārējiem noteikumiem nav patiess, tad būs zaļš, ka tas man iedos vienu noteikumu mazāk, ar kuru man šeit jātiek galā. Labi.

Lejupielādēt failu

Lejupielādējiet faila paraugu šeit: Podcast2105.xlsx

Interesanti raksti...