Ehdollisen muotoilun kaavan määrittäminen, joka käyttää sekaviitteitä. Useimmat ehdollisen muotoilun kaavat edellyttävät ehdotonta viittausta. Mutta tämä laskentataulukko kuorma-autojen jäljittämiseksi pihalla vaatii
Katso video
- Anderson etsii tapaa pystyä kopioimaan tietolohkot, jotka sisältävät sekoitettua ehdollista muotoilua
- Onko mahdollista poistaa dollarin merkit, kun ehdollinen muotoilu on määritetty?
- Ei - ei ottamatta käyttöön kymmeniä uusia sääntöjä
- Minun ratkaisuni: auttajasolut, jotka käyttävät suhteellisia viitteitä korvaamaan sekaviitteen ehdollisessa muotoilussa
- Muita tämän jakson tekniikoita:
- Jos sinulla on neljä ehdollista muotoilusääntöä, määritä kolme ensimmäistä ja tee sitten neljäs sääntö oletusväriksi
- Outtake # 1: Paina F2 pysäyttääksesi Excelin lisäämästä soluviittauksia ehdolliseen muotoiluikkunaan
- Outtake # 2: ehdollisen muotoilun määrittäminen
Videon transkriptio
Opi Excel Podcast-jaksosta 2105: Ehdollisen muodon kopioiminen sekaviitteillä
Hei, tervetuloa takaisin netcastiin. Tämä tulee olemaan monimutkainen tänään. Tein eilen seminaarin, ja yhdellä seminaarin ihmisellä, Andersonilla, oli mielenkiintoinen laskentataulukko, jossa oli ongelma. Selvä, ja Anderson hoitaa pihaa - perävaunut saapuvat ja perävaunut on purettava kolmen päivän kuluessa. Selvä, niin tämä on - hän alkaa, tiedät, tämä oli päivä, nämä olivat perävaunut, jotka saapuivat, ja sitten hänellä on ehdollinen muotoilu, että kun perävaunu puretaan, se muuttuu siniseksi. Kun jokin on sinistä, kaikki on hienoa. Mutta sitten hän haluaa värikoodata asioita. Jos jokin saapui tänään tai eilen, se värikoodataan vihreäksi. Joten tänään on 29. kesäkuuta 2017, joten tämä saapui eilen, ja kaikki, mitä ei ole purettu, on vihreää, mutta kun se on yli päivän vanha,haluamme korostaa asioita keltaisina ja kun ne ovat yli kaksi päivää vanhoja, nämä ovat ongelmia, jotka haluamme korostaa asioita punaisina. Eikä se ole, että tiedätkö, että tämä on yksi laskentataulukko koko pihan hallitsemiseksi, eikö? Ei ole, että siellä on arkki asioista, jotka saapuivat 26. ja toinen 27. ja toinen 28. päivä. Ja tiedät, että vaikeus on, kun uusi päivä tulee, he joko kopioivat edellisen päivän tänne tai alas tänne.he joko kopioivat edellisen päivän tänne tai alas tänne.he joko kopioivat edellisen päivän tänne tai alas tänne.
Okei, tämän videon tarkoitus ei ole tämän ehdollisen muotoilun määrittäminen. Joten aion nopeuttaa tätä, mutta jos olet kiinnostunut tämän ehdollisen muotoilun määrittämisestä, laitan nopeuttamattoman version videon loppuun.
Okei, niin siellä me olemme. Nopeuttanut sitä voit katsella lopussa nähdäksesi, miten se toimii. Vain tekemässä testi täällä, CTRL; muuttuu siniseksi. Jos tämä palaa 6/26, se muuttuu punaiseksi ja jos se on tänään, se ei toimi. Se on totta, koska tässä aion tehdä, neljäs sääntöni vihreä saapui tänään tai eilen, aion käyttää sitä vain oletusarvona. Jos mikään näistä kolmesta muusta säännöstä ei pidä paikkaansa, on vihreää, että se antaa minulle yhden säännön vähemmän, jota minun on käsiteltävä täällä.
Okei, joten olemme nyt siinä pisteessä, jossa meillä on lähinnä Andersonin ongelma. Aion laittaa 25.6.2017, nämä kaikki muuttuvat punaisiksi lukuun ottamatta niitä, jotka on purettu. Ja nyt elämä etenee, se on seuraava päivä. Saimme joitain perävaunuja sisään 6/26, joten Anderson kopioi nämä tiedot, liitä tänne, muotoile Sarake AutoFit, ja tämä on Trailer 15. Napsauta kopioidaksesi alas ja lisää, päästä eroon saapuneista. Ja niin tämä saapui tänään, joten näiden kaikkien pitäisi muuttua vihreiksi, mutta ne eivät muutu vihreiksi. Miksi he eivät muutu vihreiksi? Ne eivät muutu vihreiksi, koska nämä kaavat, nämä ehdollisen muotoilun kaavat täällä, katsomme näitä. Ne on koodattu käyttämään $ A $ 1. Voi, se on todella huono.
Selvä, joten yritetään parantaa asioita täällä. Ensimmäinen asia, jonka voin tehdä, aion päästä eroon kaikista niistä ja palata tähän alkuperäiseen tietojoukkoon ja olla hieman älykkäämpi toisella kierroksella ja sanoa, että meidän ei todellakaan tarvitse lukita sitä sarakkeeseen A. Pääsen eroon siitä $ -merkistä. Toisin sanoen, se tulee olemaan aina sarake vasemmalla puolellamme, joten se on sekava viite, mutta meidän on aina osoitettava $ 1. Muokkaamme tätä sääntöä, napsauta OK. Selvä nyt, sillä yhdellä muutoksella, kun kopioimme oikealle ja panimme uuden datan, kuten tänään päivämäärä, se toimii. Okei, joten tämä on hienoa. Elämä tulee olemaan hienoa 6.6. Ja elämä tulee olemaan hienoa 6.6. Selvä, toimi hienosti. Mutta nyt törmäämme ongelmaan, jossa sivun tila loppuu ja mitä Anderson on tehnyt, se laskee,aloittaa olennaisesti uuden rivin ja liittää ja tämä olisi 6/28, mutta se ei muutu vihreäksi.
Miksi se ei muutu vihreäksi? Se ei muutu vihreäksi, koska jouduin käyttämään $: ta palatakseni takaisin 1. Hyvä on, ja nyt tässä on arvoitus, tässä on ongelma. Mitä sinä teet nyt? Ja olen tosissani, mitä teet nyt? Haluan kuulla YouTube-kommenteista, mitä tekisit nyt.
Tiedätkö, niin hei katso, on esitetty väite, että tämä on hyvä, voimme pysähtyä täällä, koska käyttämällä A $ 1, teimme sen niin, elämä on helppoa 1. päivänä, kopioi toiseen 2. Elämä on hienoa . 3. päivän elämä on hienoa. Vasta joka 4. päivä, kun kopioimme tänne, Anderson joutuu määrittelemään ehdollisen muotoilun, muokkaamaan tätä, muokkaamaan sääntöä, muuttamaan sen 1 arvoksi 18. Napsauta OK, muokkaa tätä sääntöä ja muuta se yhdeksi 18. Napsauta OK, napsauta OK. Selvä, siis päivä 4, se pieni säätökopio päivälle 5, kopioi päivälle 6 ja sitten päivälle 7. Tee nämä vaiheet uudelleen. Mutta hei, tunnustetaan. Tämä laskentataulukko perustettiin kuusi kuukautta sitten näillä ehdollisilla muotoilusäännöillä, ja niiden on vain toimittava. Meidän ei tarvitse mennä sisään ja tehdä ehdollista muotoilua uudestaan ja uudestaan ja uudestaan.
Ensimmäinen reaktio oli, että aion teeskennellä, että tämä on laskentataulukko, jossa minulla on joitain kaavoja täällä ja nämä kaavat on rakennettu absoluuttisilla viitteillä, mutta tarvitsen nuo kaavat, jotta ne voidaan kopioida yli tai alas ja olla suhteellinen kopiossa - sekä silloin, kun kopioin tänne että kun kopioin tänne. Selvä, ja saadaksesi sen toimimaan, aion käyttää absoluuttisia viitteitä, kun asetan asiat, mutta sitten aion käyttää Etsi ja korvaa, Ctrl H.Ja sanotaan, että päästään eroon näistä suhteellisista viitteistä, vaihda jokainen $ A $ 1 arvoksi A1, Korvaa kaikki, napsauta Sulje ja nyt tämä lohko, kaikki nämä kaavat ovat erilaiset kokonaan, kopioi, liitä ja liitä ja se toimii. Se on suhteellista. Joten sanoin, okei, niin meidän on tehtävä. Meidän on otettava nämä dollarit pois kaavasta.Joten aioin kirjoittaa makron, jonka avulla voisin muokata kutakin näistä ehdollisista muotoilusäännöistä. Selvä, ja ennen kuin kirjoitin makron, aion tallentaa yhden ehdollisen muotoilusäännön muuttamisen makron, mutta ei ole, että täällä on 14 ehdollista muotoilusääntöä. Kyse ei ole edes 14 * 3, 42 ehdollisen muotoilun säännöistä. Tässä on vain 3 ehdollisen muotoilun sääntöä, ja me sovellamme näitä 3 ehdollisen muotoilun sääntöä solualueelle.Tässä on vain 3 ehdollisen muotoilun sääntöä, ja me sovellamme näitä 3 ehdollisen muotoilun sääntöä solualueelle.Tässä on vain 3 ehdollisen muotoilun sääntöä, ja me sovellamme näitä 3 ehdollisen muotoilun sääntöä solualueelle.
Joten jos muutan tätä, minun on ensin tehtävä nämä 3 ehdollisen muotoilun sääntöä ja tehtävä niistä 42 ehdollisen muotoilun sääntöä. Ja sitten olen alkanut rypistyä, koska kun Anderson kopioi täältä tänne, hän ottaa käyttöön 42 uutta sääntöä ja sitten 42 uutta sääntöä. Ja yhden paperiarkin aikana, jolla on todennäköisesti 15 päivää, hän esittelee yli 600 sääntöä, 600 erilaista muotoa, ja se on vain kamalaa. Voit lopulta lyödä liian monta muotoilusääntöä, puhumattakaan siitä, että se on vaikea asettaa, vaikka meillä olisi makro sen asettamiseen. Se on vaikea asettaa.
Aivan, niin mitä teemme? Tämän keksin ja haluan kuulla, onko sinulla jotain parempaa. Sanoin Andersonille, sanoin: ”Tiedätkö, katso, se on melko yksinkertaista. Kaikki nämä tarkastelevat yhtä laskutoimitusta ja se on = TÄNÄÄN - päiväys, joka on vasemmalla puolellani. " Eikö se olisi siistiä, jos saisimme vastauksen pieneen auttajasarakkeeseen täältä oikealle. Ja itse asiassa meidän ei tarvitse käyttää mitään dollaria lainkaan, vaan laitamme kaikki nämä solut kokonaan alas tällä yksinkertaisella pienellä kaavalla.
Näen Andersonin kasvojen katseen, hän ei halua, että ylimääräiset jutut sieltä poistetaan, mutta se on okei. Voimme piilottaa, piilottaa sen myöhemmin, jotta palaamme takaisin näihin soluihin ja menemme ehdolliseen muotoiluun. Koko TÄNÄÄN-A1 osoittaa yksinkertaisesti C3: een ja se on suhteellinen viite. Joten toisin sanoen, riippumatta siitä, missä solussa olemme, etsimme aina oikeanpuoleista solua, napsauta OK, kirjoita tähän, napsauta OK. Haluamme piilottaa nämä tiedot tänne, joten menen sisään ja CTRL 1. Aion käyttää kolmea puolipistettä - ;;;, napsauta OK. Aion tehdä siellä täsmälleen saman asian. Painan F4, toistan viimeisen toiminnon.
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.
Okei, niin siellä me olemme. Nopeuttanut sitä voit katsella lopussa nähdäksesi, miten se toimii. Vain tekemässä testi täällä. CTRL; muuttuu siniseksi. Jos tämä palaa arvoon 6/26, se muuttuu punaiseksi. Ja jos se on tänään, se ei toimi. Se on totta, koska tässä aion tehdä. Neljäs sääntöni, vihreä saapui tänään tai eilen, aion vain käyttää sitä oletuksena. Jos mikään näistä kolmesta muusta säännöstä ei ole totta, on vihreää, että se antaa minulle yhden säännön vähemmän, jota minun on käsiteltävä täällä. Hyvä on.
Lataa tiedosto
Lataa esimerkkitiedosto täältä: Podcast2105.xlsx
