Seuraa kaavasolujen muutoksia - Excel-vinkkejä

Seuraa muutoksia Excel-kaavasoluissa. Voitteko näyttää, mitkä kohteet ovat juuri muuttuneet tiettyjen syöttösolujen vaihtamisen seurauksena?

Katso video

Muutosten seuraaminen Excelissä on hieman outoa.
Tavoitteena on seurata, mitä kaavasoluja Excel muuttaa.
Tallenna nimellä tallentaa työkirjan XLSM-muodossa.
Vaihda makroturvallisuutta.
Tallenna makro selvittääksesi koodin, jotta voit määrittää ehdollisen muotoilun numeroille, jotka eivät ole yhtä suuria kuin 2.
Valitse haluamasi muotoilu.
Tallenna toinen makro oppiaksesi, kuinka CF poistetaan laskentataulukosta.
Lisää makrossa silmukka kullekin laskentataulukolle.
Lisää IF-käsky, jotta sitä ei voida käyttää otsikossa.
Lisää silmukka tarkistaaksesi kaikki kaavasolut.
Lisää ehdollinen muotoilu nähdäksesi, suoritetaanko solun arvo makrolla.
Palaa takaisin Exceliin.
Lisää muoto. Määritä makro muotoon.
Napsauta Muoto suorittaaksesi makron.
Bonusvinkki: VBA-moduulin vetäminen uuteen työkirjaan.

Videon transkriptio

Opi Excel Podcastista, jakso 2059: Excel-seurantamuutokset (kaavan tuloksissa)

Hei, tervetuloa takaisin netcastiin, olen Bill Jelen. Tämän päivän Montrealista lähetetty kysymys radan muutoksista. Seuraa muutoksia, kunnossa. Joten tässä meillä on. Meillä on 4 syöttösolua ja koko joukko Formula-soluja, jotka luottavat näihin syöttösoluihin. Ja jos käynnistän virran, palaan takaisin Tarkastelu-välilehteen, päälle Korosta muutokset, Seuraa muutoksia muokkauksen aikana, napsauta OK, okei. Ja he varoittivat minua siitä, että heidän on tallennettava työkirja ja että makroja ei voida käyttää jaetuissa työkirjoissa. Tiedät sen? Tämä on ongelma, kun seuraat muutoksia, he jakavat työkirjan ja on joukko asioita, joita ei voi tapahtua jaetuissa työkirjoissa, kuten makrot ja koko joukko muita asioita. Mutta katsotaanpa vain, kuinka muutosten seuranta toimii Excelissä tänään.

Otetaan tämä 2 ja vaihdetaan 2: sta 22: een, ja otetaan tämä 4 ja vaihdetaan se 4: stä 44: een. Selvä, ja huomaat, mitä he ovat havainneet raidan muutoksissa, on se, että nämä kaksi solua muuttuivat, okei, nuo violetit kolmiot ovat raidan todelliset muutokset. Kaikkia näitä punaisia juttuja ei tapahdu, mutta minä vain havainnoitin, että kaikki nämä punasolut ovat muuttumassa ja muutoksen seuranta ei kerro mitään näistä muutoksista. Joten, se vain sanoo, nämä kaksi solua muutettiin, mutta myös kaikki nämä muut solut muutettiin. Joten Montrealin kysymys kuuluu, onko olemassa tapa, jolla raidemuutokset todella näyttävät meille kaiken muutoksen, eivät vain nämä syöttösolut ovat muuttuneet?

Selvä, joten ensimmäinen asia, joka meidän on tehtävä, on sammuttaa Excelin sisäinen seuranta. Ja sitten, onko olemassa tapa, jonka voimme saada - voimme rakentaa oman radanmuutosjärjestelmän, jonka avulla voimme nähdä kaikki muuttuneet kaavasolut? Selvä, joten vaihe 1 ja tämä vaihe ovat tärkein vaihe, älä ohita tätä. Katsokaa tiedostoa, tiedosto on nimeltään XLSX, sinun on tallennettava tämä: Tiedosto, Tallenna nimellä, makrokäyttöisenä työkirjana, tai mikään näistä ei toimi. Sinun täytyy napsauttaa hiiren kakkospainikkeella, Mukauta valintanauhaa, kytke Kehittäjä päälle, kun pääset Kehittäjään, siirry Makroturvallisuuteen, vaihda tästä asetuksesta - se, joka sanoo, ettemme anna makrojen suorittaa tai edes aio kertoa sinulle, että he ovat tässä asetuksessa. Sinun on tehtävä nämä kaksi vaihetta. Olen jo tehnyt nuo kaksi vaihetta. Asun joka päivä noilla kahdella askeleella.Jo korjattu, mutta jos olet uusi makroissa, se on uusi sinulle. Ja sitten meidän on selvitettävä, millaista muotoilua haluat. Selvä, joten aion vain valita joitain soluja täällä, aion tallentaa makron, jonka nimi on HowToCFRed, en aio määrittää pikanäppäimeen, koska tätä ei koskaan suoriteta uudelleen. Tallennan vain koodia selvittääkseen, kuinka ehdollinen muotoilu toimii. Ja pääsemme kotiin, ehdolliseen muotoiluun, korostussoluihin, jotka eivät ole yhtä suuria - Joten, lisää sääntöjä, muotoilevat solut, jotka eivät ole yhtä suuria - Näetkö sen? Se ei ole alkuperäisessä pudotusvalikossa, mutta jos tulet tänne, ei ole yhtä suuri kuin 2, ja valitse sitten muoto. Tämä on tärkeä osa. Joten aion valita punaisen taustan. Valitset minkä värin haluat, okei? Jopa mene Lisää värejä, valitse jokin muu punainen,mennä mukautettuun, valita jokin muu punainen, okei? Se on makrotallentimen kauneus, he saavat meille täydellisen punaisen sinulle tai sinisen tai mitä tahansa haluat. Okei, napsauta OK. Ja sitten lopetamme nauhoituksen, okei. Jälleen tämän koko tarkoitus on vain nähdä, mikä koodi on ehdollisille muodoille.

Menen kohtaan Makrot, Kuinka ehdollinen muoto punainen ja muokata. Selvä, joten tässä ovat tämän koodin tärkeät osat. Näen, että he lisäävät ehdollisen muodon käyttämällä xlNotEqual -ohjelmaa, ja olemme vaikeasti lainata sitä, että se ei ole yhtä suuri kuin 2. Ja sitten muutamme solun sisäosaa kyseiseen väriin.

Selvä, minun on myös selvitettävä, kuinka kaikki ehdolliset muotoilut poistetaan taulukosta. Joten, palaa Exceliin, Tallenna toinen makro, Kuinka poistaa kaikki ehdolliset, OK. Tule tänne Koti-välilehteen, siirry kohtaan Ehdollinen muotoilu, Tyhjennä sääntö koko taulukosta, Lopeta tallennus ja katsomme koodia. Hienoa, se on yksirivinen makro. Pidän täällä jopa siitä, että tapa, jolla he tekevät sen koko arkin kohdalla, viittaa vain soluihin. Toisin sanoen kaikki aktiivisen taulukon solut.

Minun on nyt tehtävä tämä makro, nauhoitettu makro, hieman yleisemmäksi. Ja olen kirjoittanut paljon kirjoja VBA: n tekemisestä Excelissä ja olen tehnyt videoita VBA: n tekemisestä Excelissä, ja tässä on yksinkertainen asia: sinun on kyettävä tallentamaan tällainen makro, mutta lisää sitten noin viisi tai kuusi riviä, jotta makro voidaan tehdä riittävän yleiseksi.

Ja aion puhua noista viivoista, okei. Joten ensimmäinen asia, jonka haluan tehdä, on sanoa, haluan käydä läpi aktiivisen työkirjan, käydä läpi kaikki laskentataulukot. Joten jokaiselle laskentataulukolle WS on objektimuuttuja, käyn läpi kaikki laskentataulukot. Ja henkilö Montrealista sanoi: "Hei, on yksi arkki, jota en halua, että tämä tapahtuu." Joten, jos WS.Name ja laskentataulukon pisteen nimi eivät ole yhtä suuria kuin Otsikko, teemme koodin makrossa. Tässä on taulukon nimi: .Cells.FormatConditions.Delete. Joten käymme läpi jokaisen arkin yksittäisen henkilön, paitsi otsikon, ja poistat kaikki muotoehdot, sitten käymme läpi kaikki taulukon solut, mutta ei kaikki solut, vain solut, joilla on kaavat . Jos sillä ei ole kaavaa, niin enEi tarvitse muotoilla sitä, koska se ei muutu. Cell.FormatConditions.Add, tämä on suoraan makrosta, vaikka tallennettu makro sanoi Valinta - en halua joutua valitsemaan sitä, joten aion vain sanoa Cell, se on kukin yksittäinen solu. Aiomme käyttää xlNotEqual ja Formula: = ”=” 2: n sijasta, minkä tallennettu koodi teki juuri siellä, olen liittänyt mitä tahansa solussa. Joten tarkista, onko se yhtä suuri kuin nykyinen arvo. Joten jos solussa on tällä hetkellä 2, sanomme, ettei se ole yhtä suuri kuin 2. Jos solussa on tällä hetkellä 16,5, sanomme, ettei se ole yhtä suuri kuin 16,5. Ja sitten loppuosa on vain suora nauhoitettu makro, nauhoitettu makro, nauhoitettu makro, nauhoitettu makro. Kaikki tämä on tallennettu makro. Viimeistele tämä, jos loppu If. Viimeistele tämä seuraavalla WS: llä. ei tule muuttumaan. Cell.FormatConditions.Add, tämä on suoraan makrosta, vaikka tallennettu makro sanoi Valinta - en halua joutua valitsemaan sitä, joten aion vain sanoa Cell, se on kukin yksittäinen solu. Aiomme käyttää xlNotEqual ja Formula: = ”=” 2: n sijasta, minkä tallennettu koodi teki juuri siellä, olen liittänyt mitä tahansa solussa. Joten tarkista, onko se yhtä suuri kuin nykyinen arvo. Joten jos solussa on tällä hetkellä 2, sanomme, ettei se ole yhtä suuri kuin 2. Jos solussa on tällä hetkellä 16,5, sanomme, ettei se ole yhtä suuri kuin 16,5. Ja sitten loppuosa on vain suora nauhoitettu makro, nauhoitettu makro, nauhoitettu makro, nauhoitettu makro. Kaikki tämä on tallennettu makro. Viimeistele tämä, jos loppu If. Viimeistele tämä seuraavalla WS: llä.ei tule muuttumaan. Cell.FormatConditions.Add, tämä on suoraan makrosta, vaikka tallennettu makro sanoi Valinta - en halua joutua valitsemaan sitä, joten aion vain sanoa Cell, se on kukin yksittäinen solu. Aiomme käyttää xlNotEqual-sovellusta ja sen sijaan, että Formula: = ”=” 2, mitä tallennettu koodi teki juuri siellä, olen liittänyt mitä tahansa solussa olevaa. Joten tarkista, onko se yhtä suuri kuin nykyinen arvo. Joten jos solussa on tällä hetkellä 2, sanomme, ettei se ole yhtä suuri kuin 2. Jos solussa on tällä hetkellä 16,5, sanomme, ettei se ole yhtä suuri kuin 16,5. Ja sitten loppuosa on vain suora nauhoitettu makro, nauhoitettu makro, nauhoitettu makro, nauhoitettu makro. Kaikki tämä on tallennettu makro. Viimeistele tämä, jos loppu If. Viimeistele tämä seuraavalla WS: llä.tämä on suoraan makrosta, vaikka tallennettu makro sanoi Valinta - en halua joutua valitsemaan sitä, joten sanon vain solun, joka on kukin yksittäinen solu. Aiomme käyttää xlNotEqual-sovellusta ja sen sijaan, että Formula: = ”=” 2, mitä tallennettu koodi teki juuri siellä, olen liittänyt mitä tahansa solussa olevaa. Joten tarkista, onko se yhtä suuri kuin nykyinen arvo. Joten jos solussa on tällä hetkellä 2, sanomme, ettei se ole yhtä suuri kuin 2. Jos solussa on tällä hetkellä 16,5, sanomme, ettei se ole yhtä suuri kuin 16,5. Ja sitten loppuosa on vain suora nauhoitettu makro, nauhoitettu makro, nauhoitettu makro, nauhoitettu makro. Kaikki tämä on tallennettu makro. Viimeistele tämä, jos loppu If. Viimeistele tämä seuraavalla WS: llä.tämä on suoraan makrosta, vaikka tallennettu makro sanoi Valinta - en halua joutua valitsemaan sitä, joten sanon vain solun, joka on kukin yksittäinen solu. Aiomme käyttää xlNotEqual-sovellusta ja sen sijaan, että Formula: = ”=” 2, mitä tallennettu koodi teki juuri siellä, olen liittänyt mitä tahansa solussa olevaa. Joten tarkista, onko se yhtä suuri kuin nykyinen arvo. Joten jos solussa on tällä hetkellä 2, sanomme, ettei se ole yhtä suuri kuin 2. Jos solussa on tällä hetkellä 16,5, sanomme, ettei se ole yhtä suuri kuin 16,5. Ja sitten loppuosa on vain suora nauhoitettu makro, nauhoitettu makro, nauhoitettu makro, nauhoitettu makro. Kaikki tämä on tallennettu makro. Viimeistele tämä, jos loppu If. Viimeistele tämä seuraavalla WS: llä.En halua valita sitä, joten aion vain sanoa Cell, se on kukin yksittäinen solu. Aiomme käyttää xlNotEqual-sovellusta ja sen sijaan, että Formula: = ”=” 2, mitä tallennettu koodi teki juuri siellä, olen liittänyt mitä tahansa solussa olevaa. Joten tarkista, onko se yhtä suuri kuin nykyinen arvo. Joten jos solussa on tällä hetkellä 2, sanomme, ettei se ole yhtä suuri kuin 2. Jos solussa on tällä hetkellä 16,5, sanomme, ettei se ole yhtä suuri kuin 16,5. Ja sitten loppuosa on vain suora nauhoitettu makro, nauhoitettu makro, nauhoitettu makro, kirjattu makro. Kaikki tämä on tallennettu makro. Viimeistele tämä, jos loppu If. Viimeistele tämä seuraavalla WS: llä.En halua valita sitä, joten aion vain sanoa Cell, se on kukin yksittäinen solu. Aiomme käyttää xlNotEqual-sovellusta ja sen sijaan, että Formula: = ”=” 2, mitä tallennettu koodi teki juuri siellä, olen liittänyt mitä tahansa solussa olevaa. Joten tarkista, onko se yhtä suuri kuin nykyinen arvo. Joten jos solussa on tällä hetkellä 2, sanomme, ettei se ole yhtä suuri kuin 2. Jos solussa on tällä hetkellä 16,5, sanomme, ettei se ole yhtä suuri kuin 16,5. Ja sitten loppuosa on vain suora nauhoitettu makro, nauhoitettu makro, nauhoitettu makro, nauhoitettu makro. Kaikki tämä on tallennettu makro. Viimeistele tämä, jos loppu If. Viimeistele tämä seuraavalla WS: llä.= ”=” 2 mitä tallennettu koodi teki juuri siellä, olen ketjutellut mitä tahansa siinä solussa. Joten tarkista, onko se yhtä suuri kuin nykyinen arvo. Joten jos solussa on tällä hetkellä 2, sanomme, ettei se ole yhtä suuri kuin 2. Jos solussa on tällä hetkellä 16,5, sanomme, ettei se ole yhtä suuri kuin 16,5. Ja sitten loppuosa on vain suora nauhoitettu makro, nauhoitettu makro, nauhoitettu makro, nauhoitettu makro. Kaikki tämä on tallennettu makro. Viimeistele tämä, jos loppu If. Viimeistele tämä seuraavalla WS: llä.= ”=” 2 mitä tallennettu koodi teki juuri siellä, olen ketjutellut mitä tahansa siinä solussa. Joten tarkista, onko se yhtä suuri kuin nykyinen arvo. Joten jos solussa on tällä hetkellä 2, sanomme, ettei se ole yhtä suuri kuin 2. Jos solussa on tällä hetkellä 16,5, sanomme, ettei se ole yhtä suuri kuin 16,5. Ja sitten loppuosa on vain suora nauhoitettu makro, nauhoitettu makro, nauhoitettu makro, nauhoitettu makro. Kaikki tämä on tallennettu makro. Viimeistele tämä, jos loppu If. Viimeistele tämä seuraavalla WS: llä.nauhoitettu makro, tallennettu makro. Kaikki tämä on tallennettu makro. Viimeistele tämä, jos loppu If. Viimeistele tämä seuraavalla WS: llä.nauhoitettu makro, tallennettu makro. Kaikki tämä on tallennettu makro. Viimeistele tämä, jos loppu If. Viimeistele tämä seuraavalla WS: llä.

Selvä, joten minulla on makro nimeltä ApplyCF. Palaa Exceliin, lisää muoto. Muoto on helppo saada täältä: Lisää, valitsen aina pyöristetyn suorakulmion, kirjoita Palauta nykyisiin arvoihin. Sovellamme Koti, keskusta ja keskusta tekevät siitä hieman suuremman. Rakastan hehkua. Oletan, että luulet, että on typerää nähdä, että sitä ei ole, hehku, asetus, jota pidän, ei ole siellä, joten menen aina sivun asetteluun ja tehosteisiin ja valitsen toisen. Ja sitten kun palaan muotoon, voin valita sellaisen, jolla on todella vähän hehkua. Minusta se mielestäni näyttää siistiltä, mielestäni on sen arvoista. Napsauta hiiren kakkospainikkeella, Määritä makro ja sano ApplyCF, napsauta OK. Selvä, ja mitä sitten tapahtuu, kun napsautan sitä, se käy läpi kaikki nämä taulukot, etsi kaikki kaavasolut ja määrittelee ehdollisen muotoilun, joka sanoo: Jos nämä solut eivät ole yhtä suuria kuin 7,vaihda väriä, okei? Se siitä. Se on niin nopeaa, tapahtui niin nopeasti. BAM! Se on tehty. Ja nyt, katso jos vaihdan tämän 11: ksi, kaikki nuo solut vain muuttuvat. Nyt, jos se palaa arvoon 1, ahh, värit muuttuivat. Joten mitä arvo oli, kun muutamme - jos muutan tätä solua, kaikki nuo solut muuttuvat. Jos vaihdan tämän solun, kaikki nämä solut muuttuvat. Jos vaihdan tämän solun, kaikki nämä solut muuttuvat.kaikki nämä solut muuttuvat.kaikki nämä solut muuttuvat.

Alright, now this is the new normal. Now from here, I want to track again. So I Reset To Current Values and then if I change this one to a 3, those sales change. Oh, by the way, these cells back here and these other sheets also changed in response to this. Track changes in Excel as it exists? Yeah, it's really lame. It doesn't show you the things that changed and having to show the workbook is a horrible, horrible thing. But with this simple, simple little macro, it works.

Alright now, the question said, alright, so if this is working how do I now get this to work in my other workbook? So I have some other workbook and I want to copy this over. Alright, so this is a great little bonus tip here. I'll create a brand new workbook and we'll put some stuff in here and I'll have a couple of formulas, and put a cell up there, alright. So we changed that cell, those 4 cells are all formula cells. Now if I want this workbook, Book2, to also have the code from podcast 2059, well I could retype it all again but that would be silly. So we come here to the Developer tab, and go to Visual Basic. And I want to make sure that I can see Book2 and then I can see Podcast 2059. I simply take that module and drag it and drop it on Book2, right there. And now, that code is also in Book2. Coming back to Book2, just add a shape, right click, Assign Macro, click OK. Alright, it works. And then 3, see, we've now applied that setting to this workbook.

Great question. Great question sent in from Montreal. And in this case, great question that my initial reaction is, well yeah, you're right. Track changes is horrible in Excel. And I wonder if I could create something that would actually track the changes. What are the downsides here and I'm sure I'm going to hear about this in the YouTube comments. If you had 10,000 formula cells, well now, all of these conditional formattings are going to be volatile, the things going to slow down, too many Excel format errors. Yeah, I can see all that but, you know, for a nice small workbook 5,6, 7 sheets, maybe 50 rows per sheet, I would think that this has some chance- some chance are working.

Okay, episode recap: Track changes in Excel, it's a little bizarre especially because they share the workbook. Our goal is to track what formula cells in Excel change. You have to save the workbook as XLSM, change your Macro Security. Record a macro to figure out the code to set up conditional formatting for numbers and not equal to 2, that’s just to figure out what red you want to use. Choose the formatting you want, Record another macro to learn how to remove conditional formatting from the worksheet. And then, to that macro that we recorded the first one, add a loop for each worksheet, an IF statement to prevent it from running on the title sheet, then a loop to check each formula cell. Add conditional formatting to each cell that says, if this CELL.VALUE is not equal to the value at the time that it ran, then we're going to highlight the things. Go back to Excel, assign a shape, add a shape, assign a macro to the shape, click the shape to run the macro. And I also showed you the bonus tip: dragging a VBA module to a new workbook.

Hei, haluan kiittää sinua pysähtymisestä. Nähdään ensi kerralla toisesta netcastista.

Lataa tiedosto

Lataa esimerkkitiedosto täältä: Podcast2059.xlsm