Sekojiet izmaiņām Excel formulu šūnās. Vai varat parādīt, kuri vienumi ir tikko mainīti, mainot noteiktas ievades šūnas?
Skatīties video
- Izmaiņu izsekošana programmā Excel ir nedaudz dīvaina.
- Mērķis ir izsekot, kādas formulas šūnas programmā Excel mainās.
- Saglabāt kā, lai saglabātu darbgrāmatu kā XLSM.
- Mainīt makro drošību.
- Ierakstiet makro, lai noskaidrotu kodu, lai iestatītu nosacītu formatēšanu skaitļiem, kas nav vienādi ar 2.
- Izvēlieties vajadzīgo formatējumu.
- Ierakstiet citu makro, lai uzzinātu, kā noņemt CF no darblapas.
- Makro pievienojiet cilpu katrai darblapai.
- Pievienojiet IF priekšrakstu, lai novērstu tā darbību sadaļā Title.
- Pievienojiet cilpu, lai pārbaudītu katru formulas šūnu.
- Pievienojiet nosacīto formatējumu, lai redzētu, vai šūnas vērtība makro darbojas.
- Atgriezieties programmā Excel.
- Pievienojiet formu. Piešķiriet makro formai.
- Noklikšķiniet uz formas, lai palaistu makro.
- Bonusa padoms: velciet VBA moduli uz jaunu darbgrāmatu.
Video atšifrējums
Uzziniet programmu Excel no Podcast, 2059. sērija: Excel izseko izmaiņas (formulas rezultātos)
Sveiki, sveicināti atkal netcast ēterā, es esmu Bils Jelens. Šodienas jautājums tika nosūtīts no Monreālas par trases izmaiņām. Sekojiet izmaiņām, labi. Tātad, lūk, kas mums ir. Mums ir 4 ievades šūnas un vesela virkne Formula šūnu, kas paļaujas uz šīm ievades šūnām. Un, ja es ieslēgtos, es atgriezīšos cilnē Pārskatīšana, ieslēdzu Izcelt izmaiņas, Rediģēšanas laikā izsekot izmaiņām, noklikšķiniet uz Labi, labi. Un viņi mani brīdināja, ka viņiem ir jāsaglabā darbgrāmata un makro nevar izmantot koplietojamās darbgrāmatās. Tu to zini? Šī ir problēma, kad izsekojat izmaiņas, viņi koplieto darbgrāmatu, un koplietojamās darbgrāmatās, piemēram, makro un daudzās citās lietās, var notikt vesela virkne lietu. Bet apskatīsim tikai to, kā izmaiņas programmā Excel darbojas šodien.
Paņemsim šo 2 un mainīsimies no 2 uz 22, un ņemsim šo 4 un mainīsim to no 4 uz 44. Labi, un redzat, ko viņi ir atzīmējuši ceļa izmaiņās, šīs divas šūnas mainījās, labi, šie violetie trīsstūri ir faktiskās sliežu ceļa izmaiņas. Visas šīs sarkanās lietas tā nenotiek, bet es vienkārši ilustrēju, ka visas šīs sarkanās šūnas mainās un dziesmu izmaiņas neko nepasaka par šīm izmaiņām, labi? Tātad, tas tikai saka, šīs divas šūnas tika mainītas, bet visas šīs citas šūnas arī tika mainītas. Un tad jautājums no Monreālas ir, vai ir veids, kā trases izmaiņas mums faktiski parāda visu, kas mainās, ir mainījušās ne tikai šīs ievades šūnas?
Labi, tāpēc pirmā lieta, kas mums jādara, ir izslēgt Excel iebūvēto Track Track. Un vai tad ir veids, kā mēs varam iegūt - mēs varam izveidot savu trases izmaiņu sistēmu, kas ļaus mums redzēt visas mainītās formulas šūnas? Labi, tāpēc 1. solis un šis solis ir vissvarīgākais solis. Nepalaidiet to garām. Paskatieties uz savu failu, jūsu failu sauc par kaut ko XLSX, jums tas jāsaglabā: Fails, Saglabāt kā, darbgrāmatu kā makro iespējotu vai arī tas nedarbosies. Jums ar peles labo pogu ir jānoklikšķina, Pielāgot lenti, jāieslēdz izstrādātājs, kad esat nonācis pie izstrādātāja, dodieties uz Macro Security, mainiet šo iestatījumu - tas, kas saka, ka mēs neļausim makro palaist vai pat neteiksim to pateikt jūs, ka viņi tur šo iestatījumu. Jums ir jāveic šie divi soļi. Es jau esmu veicis šos divus soļus. Es katru dienu dzīvoju ar šiem diviem soļiem.Tas jau ir salabots, bet, ja makro jums ir jauns, tas jums ir jauns. Un tad mums ir jāizdomā, kādu formatējumu vēlaties. Labi, tāpēc es šeit vienkārši izvēlos dažas šūnas, es ierakstīšu makro ar nosaukumu HowToCFRed, es nepiešķiršu īsinājumtaustiņam, jo tas nekad vairs nedarbosies. Es tikai ierakstīju kodu, lai saprastu, kā darbojas nosacītā formatēšana. Un mēs nonāksim mājās, nosacītajā formatēšanā, iezīmēšanas šūnās, kas nav vienādas ar - Tātad, vairāk noteikumu, Formatēt šūnas, kas nav vienādas ar - redzat to? Tas nav sākotnējā nolaižamajā izvēlnē, bet, ja jūs šeit ienāksit, nebūs vienāds ar 2, un pēc tam izvēlieties formātu. Šī ir svarīgā daļa. Tāpēc es izvēlos sarkanu fonu. Jūs šeit izvēlaties kādu krāsu vēlaties, labi? Pat dodieties uz Vairāk krāsu, izvēlieties kādu citu sarkanu,iedziļināties Custom, izvēlēties kādu citu sarkanu, labi? Tas ir Macro Recorder skaistums, viņi iegūs mums ideālu sarkanu krāsu vai zilu krāsu, vai arī ko vien vēlaties. Labi, noklikšķiniet uz Labi. Un tad mēs pārtrauksim ierakstīšanu, labi. Atkal visa tā būtība ir tikai, lai redzētu, kāds ir nosacīto formātu kods.
Es eju uz Makro, Kā rediģēt nosacīto formātu. Labi, tāpēc šeit ir svarīgas šī koda daļas. Es redzu, ka viņi pievieno nosacītu formātu, izmantojot xlNotEqual, un mēs to grūti citēt, lai tas nebūtu vienāds ar 2. Un tad mēs mainām šūnas interjeru uz šo krāsu.
Labi, man arī jāizdomā, kā izdzēst visu nosacīto formatējumu lapā. Tātad, atgriezieties programmā Excel, ierakstiet citu makro, kā izdzēst visus nosacītos, labi. Nāciet šeit uz cilni Sākums, dodieties uz sadaļu Nosacītais formatējums, Notīrīt kārtulu no visas lapas, Apturēt ierakstīšanu un mēs apskatīsim šo kodu. Lieliski, tas ir vienas rindas makro. Man šeit pat patīk, ka tas, kā viņi to dara visai lapai, attiecas tikai uz šūnām. Tātad citiem vārdiem sakot, visas aktīvās lapas šūnas.
Tagad man šis makro, ierakstītais makro ir jāpadara mazliet vispārīgāks. Esmu uzrakstījis daudz grāmatu par to, kā veikt VBA programmā Excel, un esmu izveidojis videoklipus par to, kā VBA veikt programmā Excel, un šeit ir vienkārša lieta: jums jāspēj ierakstīt šāda veida makro, bet pēc tam pievienojiet apmēram piecas vai sešas rindiņas, lai varētu makro padarīt pietiekami vispārīgu.
Un es runāšu par šīm rindām, labi. Tāpēc pirmā lieta, ko vēlos darīt, ir teikt, es vēlos pārskatīt aktīvo darbgrāmatu, iziet visas darblapas. Tātad katrai darblapai WS ir objekta mainīgais, es iziešu visas darblapas. Un persona no Monreālas teica: "Hei, ir viena lapa, kurā es nevēlos, lai tas notiktu." Tātad, ja WS.Name ar darblapas punkta nosaukumu nav vienāds ar virsrakstu, kods tiks veikts makro. Šeit ir lapas nosaukums: .Cells.FormatConditions.Delete. Tātad, mēs iziesim caur katru lapas atsevišķu personu, izņemot virsrakstu, un izdzēsīsim visus formāta nosacījumus. Tad mēs iziesim cauri katrai lapas šūnai, bet ne visām šūnām, tikai šūnām, kurām ir formulas . Ja tam nav formulas, es to nedaruTas nav jāformatē, jo tas nemainīsies. Cell.FormatConditions.Pievienot, tas ir tieši no makro, lai gan ierakstītajā makro bija teikts Atlase - es nevēlos, lai tas būtu jāatlasa, tāpēc es vienkārši saku Šūna, tā ir katra atsevišķa šūna. Mēs izmantosim xlNotEqual un Formulas vietā: = ”=” 2, ko ierakstītais kods izdarīja tieši tur, es esmu saliedējis visu, kas atrodas šajā šūnā. Tāpēc pārbaudiet, vai tā nav vienāda ar pašreizējo vērtību. Tātad, ja šūnā pašlaik ir 2, mēs sakām, ka tas nav vienāds ar 2. Ja šūnā pašlaik ir 16,5, mēs sakām, ka tas nav vienāds ar 16,5. Un tad pārējais ir tikai taisni ierakstīts makro, ierakstīts makro, ierakstīts makro, ierakstīts makro. Tas viss ir no ierakstīta makro. Pabeigt šo Ja ar beigu Ja. Pabeidziet šo darbu ar nākamo WS. s nemainīsies. Cell.FormatConditions.Pievienot, tas ir tieši no makro, lai gan ierakstītajā makro bija teikts Atlase - es nevēlos, lai tas būtu jāatlasa, tāpēc es vienkārši saku Šūna, tā ir katra atsevišķa šūna. Mēs izmantosim xlNotEqual un Formulas vietā: = ”=” 2, ko ierakstītais kods izdarīja tieši tur, es esmu saliedējis visu, kas atrodas šajā šūnā. Tāpēc pārbaudiet, vai tā nav vienāda ar pašreizējo vērtību. Tātad, ja šūnā pašlaik ir 2, mēs sakām, ka tas nav vienāds ar 2. Ja šūnā pašlaik ir 16,5, mēs sakām, ka tas nav vienāds ar 16,5. Un tad pārējais ir tikai taisni ierakstīts makro, ierakstīts makro, ierakstīts makro, ierakstīts makro. Tas viss ir no ierakstīta makro. Pabeigt šo Ja ar beigu Ja. Pabeidziet šo darbu ar nākamo WS.s nemainīsies. Cell.FormatConditions.Pievienot, tas ir tieši no makro, lai gan ierakstītajā makro bija teikts Atlase - es nevēlos, lai tas būtu jāatlasa, tāpēc es vienkārši saku Šūna, tā ir katra atsevišķa šūna. Mēs izmantosim xlNotEqual un Formulas vietā: = ”=” 2, ko ierakstītais kods izdarīja tieši tur, es esmu saliedējis visu, kas atrodas šajā šūnā. Tāpēc pārbaudiet, vai tā nav vienāda ar pašreizējo vērtību. Tātad, ja šūnā pašlaik ir 2, mēs sakām, ka tas nav vienāds ar 2. Ja šūnā pašlaik ir 16,5, mēs sakām, ka tas nav vienāds ar 16,5. Un tad pārējais ir tikai taisni ierakstīts makro, ierakstīts makro, ierakstīts makro, ierakstīts makro. Tas viss ir no ierakstīta makro. Pabeigt šo Ja ar beigu Ja. Pabeidziet šo darbu ar nākamo WS.tas ir tieši no makro, lai gan ierakstītajā makro bija teikts Atlase - es nevēlos, lai tas būtu jāatlasa, tāpēc es vienkārši saku Šūna, tā ir katra atsevišķa šūna. Mēs izmantosim xlNotEqual un Formulas vietā: = ”=” 2, ko ierakstītais kods izdarīja tieši tur, es esmu saliedējis visu, kas atrodas šajā šūnā. Tāpēc pārbaudiet, vai tā nav vienāda ar pašreizējo vērtību. Tātad, ja šūnā pašlaik ir 2, mēs sakām, ka tas nav vienāds ar 2. Ja šūnā pašlaik ir 16,5, mēs sakām, ka tas nav vienāds ar 16,5. Un tad pārējais ir tikai taisni ierakstīts makro, ierakstīts makro, ierakstīts makro, ierakstīts makro. Tas viss ir no ierakstīta makro. Pabeigt šo Ja ar beigu Ja. Pabeidziet šo darbu ar nākamo WS.tas ir tieši no makro, lai gan ierakstītajā makro bija teikts Atlase - es nevēlos, lai tas būtu jāatlasa, tāpēc es vienkārši saku Šūna, tā ir katra atsevišķa šūna. Mēs izmantosim xlNotEqual un Formulas vietā: = ”=” 2, ko ierakstītais kods izdarīja tieši tur, es esmu saliedējis visu, kas atrodas šajā šūnā. Tāpēc pārbaudiet, vai tā nav vienāda ar pašreizējo vērtību. Tātad, ja šūnā pašlaik ir 2, mēs sakām, ka tas nav vienāds ar 2. Ja šūnā pašlaik ir 16,5, mēs sakām, ka tas nav vienāds ar 16,5. Un tad pārējais ir tikai taisni ierakstīts makro, ierakstīts makro, ierakstīts makro, ierakstīts makro. Tas viss ir no ierakstīta makro. Pabeigt šo Ja ar beigu Ja. Pabeidziet šo darbu ar nākamo WS.Es nevēlos to atlasīt, tāpēc es tikai saku Šūna, tā ir katra atsevišķa šūna. Mēs izmantosim xlNotEqual un Formulas vietā: = ”=” 2, ko ierakstītais kods izdarīja tieši tur, es esmu saliedējis visu, kas atrodas šajā šūnā. Tāpēc pārbaudiet, vai tā nav vienāda ar pašreizējo vērtību. Tātad, ja šūnā pašlaik ir 2, mēs sakām, ka tas nav vienāds ar 2. Ja šūnā pašlaik ir 16,5, mēs sakām, ka tas nav vienāds ar 16,5. Un tad pārējais ir tikai taisni ierakstīts makro, ierakstīts makro, ierakstīts makro, ierakstīts makro. Tas viss ir no ierakstīta makro. Pabeigt šo Ja ar beigu Ja. Pabeidziet šo darbu ar nākamo WS.Es nevēlos to atlasīt, tāpēc es vienkārši saku Šūna, tā ir katra atsevišķa šūna. Mēs izmantosim xlNotEqual un Formulas vietā: = ”=” 2, ko ierakstītais kods izdarīja tieši tur, es esmu saliedējis visu, kas atrodas šajā šūnā. Tāpēc pārbaudiet, vai tā nav vienāda ar pašreizējo vērtību. Tātad, ja šūnā pašlaik ir 2, mēs sakām, ka tas nav vienāds ar 2. Ja šūnā pašlaik ir 16,5, mēs sakām, ka tas nav vienāds ar 16,5. Un tad pārējais ir tikai taisni ierakstīts makro, ierakstīts makro, ierakstīts makro, ierakstīts makro. Tas viss ir no ierakstīta makro. Pabeigt šo Ja ar beigu Ja. Pabeidziet šo darbu ar nākamo WS.= ”=” 2, ko ierakstītais kods darīja tieši tur, es esmu saliedējis visu, kas atrodas šajā šūnā. Tāpēc pārbaudiet, vai tā nav vienāda ar pašreizējo vērtību. Tātad, ja šūnā pašlaik ir 2, mēs sakām, ka tas nav vienāds ar 2. Ja šūnā pašlaik ir 16,5, mēs sakām, ka tas nav vienāds ar 16,5. Un tad pārējais ir tikai taisni ierakstīts makro, ierakstīts makro, ierakstīts makro, ierakstīts makro. Tas viss ir no ierakstīta makro. Pabeigt šo Ja ar beigu Ja. Pabeidziet šo darbu ar nākamo WS.= ”=” 2, ko ierakstītais kods darīja tieši tur, es esmu saliedējis visu, kas atrodas šajā šūnā. Tāpēc pārbaudiet, vai tā nav vienāda ar pašreizējo vērtību. Tātad, ja šūnā pašlaik ir 2, mēs sakām, ka tas nav vienāds ar 2. Ja šūnā pašlaik ir 16,5, mēs sakām, ka tas nav vienāds ar 16,5. Un tad pārējais ir tikai taisni ierakstīts makro, ierakstīts makro, ierakstīts makro, ierakstīts makro. Tas viss ir no ierakstīta makro. Pabeigt šo Ja ar beigu Ja. Pabeidziet šo darbu ar nākamo WS.ierakstīts makro, ierakstīts makro. Tas viss ir no ierakstīta makro. Pabeigt šo Ja ar beigu Ja. Pabeidziet šo darbu ar nākamo WS.ierakstīts makro, ierakstīts makro. Tas viss ir no ierakstīta makro. Pabeigt šo Ja ar beigu Ja. Pabeidziet šo darbu ar nākamo WS.
Labi, tāpēc man ir makro ar nosaukumu ApplyCF. Atgriezieties programmā Excel, pievienojiet formu. Šeit ir viegli iegūt formu: Ievietot, es vienmēr izvēlos noapaļotu taisnstūri, ierakstiet Atiestatīt uz pašreizējām vērtībām. Mēs pielietosim māju, centru un centru, kas to padarīs mazliet lielāku. Es mīlu mirdzumu. Es domāju, ka jūs domājat, ka tas ir dumjš, redzot, ka tā nav, mirdzums, iestatījums, kas man patīk, nav, tāpēc es vienmēr dodos uz lapu izkārtojumu un efektiem un izvēlos otro. Un tad, kad es atgriezīšos pie formāta, es varu izvēlēties tādu, kuram patiesībā ir mazliet svelme. Manuprāt, es domāju, ka tas izskatās forši, es domāju, ka tas ir tā vērts. Ar peles labo pogu noklikšķiniet uz Piešķirt makro un sakiet ApplyCF, noklikšķiniet uz Labi. Labi, un tad tas, ko tas darīs, ir tad, kad es to noklikšķinu, tas iet cauri visām šīm lapām, atrod visas formulas šūnas un izveido nosacītu formatējumu, kurā teikts: Ja šīs šūnas nav vienādas ar 7,mainīt krāsu, labi? Tieši tā. Tas ir tik ātri, ka notika tik ātri. BAM! Tas ir pabeigts. Un tagad, skatieties, vai es mainīšu šo vienu uz 11, visas šīs šūnas vienkārši mainījās. Tagad, ja tas atgriežas pie 1, ahh, krāsas mainījās. Tātad, lai kāda būtu vērtība, kad mēs maināmies - ja es mainu šo šūnu, visas šīs šūnas mainās. Ja es mainu šo šūnu, visas šīs šūnas mainās. Ja es mainu šo šūnu, visas šīs šūnas mainās.visas šīs šūnas mainās.visas šīs šūnas mainās.
Labi, tagad tas ir jauns normāls. Tagad no šejienes es vēlos izsekot vēlreiz. Tāpēc es atjaunoju pašreizējās vērtības, un tad, ja es mainīšu šo uz 3, šie pārdošanas apjomi mainīsies. Ak, starp citu, šīs šūnas šeit un šīs citas lapas arī mainījās, reaģējot uz to. Izsekot izmaiņas programmā Excel, kā tas pastāv? Jā, tas tiešām ir klibs. Tas neparāda lietas, kas mainījās, un nepieciešamība parādīt darbgrāmatu ir briesmīga, briesmīga lieta. Bet ar šo vienkāršo, vienkāršo mazo makro tas darbojas.
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.
Nu, hei, es gribu pateikties, ka apstājies. Tiksimies nākamreiz, lai skatītu citu netcast raidījumu no.
Lejupielādēt failu
Lejupielādējiet faila paraugu šeit: Podcast2059.xlsm
