Пратите промене у ћелијама Екцел формуле. Можете ли показати које су се ставке управо промениле као резултат промене одређених улазних ћелија?
Погледајте видео
- Промена стаза у програму Екцел мало је бизарна.
- Циљ је пратити шта се ћелије формуле у програму Екцел мењају.
- Сачувај као да бисте радну свеску сачували као КСЛСМ.
- Промените сигурност макронаредби.
- Снимите макронаредбу да бисте схватили код за постављање условног форматирања за бројеве који нису једнаки 2.
- Изаберите форматирање које желите.
- Снимите још један макронаредбу да бисте научили како уклонити ЦФ са радног листа.
- У макро, додајте петљу за сваки радни лист.
- Додајте израз ИФ да бисте спречили његово покретање у наслову.
- Додајте петљу да бисте проверили сваку ћелију формуле.
- Додајте условно обликовање да бисте видели да ли се покреће вредност ћелије у тренутку.
- Вратите се у Екцел.
- Додајте облик. Доделите макро облику.
- Кликните на Облик да бисте покренули макро.
- Бонус савет: Превлачење ВБА модула у нову радну свеску.
Видео транскрипт
Научите Екцел из Подцаста, Епизода 2059: Промене у програму Екцел (у резултатима Формуле)
Хеј, добродошао назад на нетцаст, ја сам Билл Јелен. Данашње питање упућено из Монтреала о променама колосека. Промена стазе, у реду. Дакле, ево шта имамо. Имамо 4 улазне ћелије и целу гомилу ћелија Формуле које се ослањају на ове улазне ћелије. А ако бих укључио, вратићу се на картицу Ревиев, укључити Хигхлигхт Цхангес, Пратити промене током уређивања, кликнути ОК, у реду. И упозорили су ме да морају да сачувају радну свеску и да се макронаредбе не могу користити у дељеним радним свескама. Ви знате да? То је проблем када пратите промене, они деле радну свеску и читава гомила ствари се не може догодити у дељеним радним свескама, знате, попут макронаредби и читаве гомиле других ствари. Али хајде да само погледамо како промене записа данас функционишу у програму Екцел.
Узмимо ово 2 и променимо са 2 на 22, а узмемо ово 4 и променимо га са 4 на 44. У реду, и видите, оно што су приметили у променама стаза је да су се ове две ћелије промениле, у реду, ти љубичасти троуглови су стварне промене стазе. Све ове црвене ствари се не догађају, али управо сам илустровао да се све те црвене ћелије мењају и промене промена не говоре ништа о тим променама, у реду? Дакле, само каже, ове две ћелије су промењене, али су промењене и све ове ћелије. И онда је питање из Монтреала, да ли постоји начин да промене трага заиста покажу све што се мења, а не само ове улазне ћелије?
У реду, дакле, прва ствар коју морамо да урадимо је да искључимо уграђене у Екцел промене праћења. И онда, да ли постоји начин који можемо добити - можемо да изградимо сопствени систем промена стаза који ће нам омогућити да видимо све ћелије формуле које су се промениле? У реду, дакле, корак 1 и овај корак је најважнији корак, немојте ово прескочити. Погледајте датотеку, датотека се зове нешто КСЛСКС, морате да сачувате ово: Датотека, Сачувај као, Као радна свеска са омогућеним макронаредбама, или ништа од овога неће успети. Морате да кликнете десним тастером миша, прилагодите врпцу, укључите програмера, након што дођете до програмера, одете на Мацро Сецурити, промените ову поставку - ону која каже да нећемо пустити макрое да раде или чак нећемо рећи ви да сте тамо у овој поставци. Морате направити та два корака. Већ сам учинио та два корака. Свакодневно живим са та два корака.Већ је поправљено, али ако сте нови у макроима, ово је ново за вас. А онда, морамо да схватимо какво обликовање желите. У реду, па ћу овде само изабрати неке ћелије, Снимићу макро који се зове ХовТоЦФРед, нећу додељивати у пречицу, јер се ово више никада неће покренути. Само снимам код да бих открио како функционише условно форматирање. А ми ћемо ући у Почетна, Условно форматирање, Истакнути ћелије које нису једнаке - Дакле, Више правила, Обликовање ћелија које нису једнаке - Видите то? Није у оригиналном падајућем менију, али ако уђете овде, није једнако 2, а затим одаберите формат. Ово је важан део. Па ћу изабрати црвену позадину. Овде бираш коју год боју желиш, у реду? Чак идите на Више боја, одаберите неку другу црвену,уђите у Цустом, одаберите неку другу црвену, у реду? То је лепота Мацро Рецордер-а, они ће нам донијети савршену црвену за вас или плаву или шта год желите. У реду, кликните ОК. А онда ћемо зауставити снимање, у реду. Опет, цела поента овога је само да видимо какав је код за условне формате.
Идем на Макронаредбе, Како условно обликовати црвено и уредити. У реду, овде су важни делови овог кода. Видим да додају условни формат помоћу клНотЕкуал и тешко цитирамо да не буде једнако 2. А онда мењамо унутрашњост ћелије у ту боју.
У реду, такође морам да смислим како да избришем сва условна форматирања са листа. Дакле, вратимо се у Екцел, Снимите још један макро, Како избрисати све условне, у реду. Дођите овде на картицу Почетна, идите на Условно форматирање, Обриши правило са целог листа, Заустави снимање и погледаћемо тај код. Супер, то је макро са једним редом. А овде ми се чак свиђа што се то чини за цео лист тако што се односи само на ћелије. Другим речима, све ћелије на активном листу.
Сада морам да учиним овај макро, снимљени макро, мало генеричким. Написао сам пуно књига о томе како радити ВБА у програму Екцел, а видео сам и видео записе о томе како радити ВБА у програму Екцел, и ево једноставне ствари: морате да будете у могућности да снимате овакав макро, али онда додајте око пет или шест редова како би се макро могао учинити довољно генеричким.
И разговараћу о тим редовима, у реду. Дакле, прва ствар коју желим да урадим је да желим да кажем, желим да прођем кроз активну радну свеску, да прођем кроз све радне листове. Дакле, за сваки радни лист, ВС је променљива објекта, проћи ћу кроз све радне листове. А особа из Монтреала је рекла, "Хеј, постоји један лист на којем не желим да се ово догоди." Дакле, ако име ВС.Наме, са именом тачке радног листа, није једнако наслову, онда ћемо направити код у макронаредби. Ево назива листа: .Целлс.ФорматЦондитионс.Делете. Дакле, проћи ћемо кроз сваки појединац листа осим наслова и избрисати све услове форматирања, а затим ћемо проћи кроз сваку ћелију листа, али не све ћелије, већ само ћелије које имају формуле . Ако нема формулу, онда не знамто не треба форматирати јер се то неће променити. Целл.ФорматЦондитионс.Адд, ово је директно из макронаредбе, иако је снимљени макро рекао Селецтион - не желим да морам да га бирам, па ћу само рећи Целл, то је свака појединачна ћелија. Користићемо клНотЕкуал и уместо Формуле: = ”=” 2, што је снимљени код радио управо тамо, спојио сам све што се налази у тој ћелији. Дакле, проверавамо да ли није једнак тренутној вредности. Дакле, ако ћелија тренутно има 2, кажемо да није једнако 2. Ако ћелија тренутно има 16,5, кажемо да није једнако 16,5. А онда је све ово само директно снимљени макро, снимљени макро, снимљени макро, снимљени макро. Све је то из снимљеног макронаредбе. Завршите ово Иф са завршетком Иф. Завршите ово са следећим ВС. се неће променити. Целл.ФорматЦондитионс.Адд, ово је директно из макронаредбе, иако је снимљени макро рекао Селецтион - не желим да морам да га бирам, па ћу само рећи Целл, то је свака појединачна ћелија. Користићемо клНотЕкуал и уместо Формуле: = ”=” 2, што је снимљени код радио управо тамо, спојио сам све што се налази у тој ћелији. Дакле, проверавамо да ли није једнак тренутној вредности. Дакле, ако ћелија тренутно има 2, кажемо да није једнако 2. Ако ћелија тренутно има 16,5, кажемо да није једнако 16,5. А онда је све ово само директно снимљени макро, снимљени макро, снимљени макро, снимљени макро. Све је то из снимљеног макронаредбе. Завршите ово ако завршите ако. Завршите ово са следећим ВС.се неће променити. Целл.ФорматЦондитионс.Адд, ово је директно из макронаредбе, иако је снимљени макро рекао Селецтион - не желим да морам да га бирам, па ћу само рећи Целл, то је свака појединачна ћелија. Користићемо клНотЕкуал и уместо Формуле: = ”=” 2, што је снимљени код радио управо тамо, спојио сам све што се налази у тој ћелији. Дакле, проверавамо да ли није једнак тренутној вредности. Дакле, ако ћелија тренутно има 2, кажемо да није једнако 2. Ако ћелија тренутно има 16,5, кажемо да није једнако 16,5. А онда је све ово само директно снимљени макро, снимљени макро, снимљени макро, снимљени макро. Све је то из снимљеног макронаредбе. Завршите ово Иф са завршетком Иф. Завршите ово са следећим ВС.ово је директно из макроа, иако је снимљени макро рекао Селецтион - не желим да морам да га бирам, па ћу само рећи Целл, то је свака појединачна ћелија. Користићемо клНотЕкуал и уместо Формуле: = ”=” 2, што је снимљени код радио управо тамо, спојио сам све што се налази у тој ћелији. Дакле, проверавамо да ли није једнак тренутној вредности. Дакле, ако ћелија тренутно има 2, кажемо да није једнако 2. Ако ћелија тренутно има 16,5, кажемо да није једнако 16,5. А онда је све ово само директно снимљени макро, снимљени макро, снимљени макро, снимљени макро. Све је то из снимљеног макронаредбе. Завршите ово Иф са завршетком Иф. Завршите ово са следећим ВС.ово је директно из макроа, иако је снимљени макро рекао Селецтион - не желим да морам да га бирам, па ћу само рећи Целл, то је свака појединачна ћелија. Користићемо клНотЕкуал и уместо Формуле: = ”=” 2, што је снимљени код радио управо тамо, спојио сам све што се налази у тој ћелији. Дакле, проверавамо да ли није једнак тренутној вредности. Дакле, ако ћелија тренутно има 2, кажемо да није једнако 2. Ако ћелија тренутно има 16,5, кажемо да није једнако 16,5. А онда је све ово само директно снимљени макро, снимљени макро, снимљени макро, снимљени макро. Све је то из снимљеног макронаредбе. Завршите ово Иф са завршетком Иф. Завршите ово са следећим ВС.не желим да морам да је одаберем, па ћу само рећи Целл, то је свака појединачна ћелија. Користићемо клНотЕкуал и уместо Формуле: = ”=” 2, што је снимљени код радио управо тамо, спојио сам све што се налази у тој ћелији. Дакле, проверавамо да ли није једнак тренутној вредности. Дакле, ако ћелија тренутно има 2, кажемо да није једнако 2. Ако ћелија тренутно има 16,5, кажемо да није једнако 16,5. А онда је све ово само директно снимљени макро, снимљени макро, снимљени макро, снимљени макро. Све је то из снимљеног макронаредбе. Завршите ово Иф са завршетком Иф. Завршите ово са следећим ВС.не желим да морам да је одаберем, па ћу само рећи Целл, то је свака појединачна ћелија. Користићемо клНотЕкуал и уместо Формуле: = ”=” 2, што је снимљени код радио управо тамо, спојио сам све што се налази у тој ћелији. Дакле, проверавамо да ли није једнак тренутној вредности. Дакле, ако ћелија тренутно има 2, кажемо да није једнако 2. Ако ћелија тренутно има 16,5, кажемо да није једнако 16,5. А онда је све ово само директно снимљени макро, снимљени макро, снимљени макро, снимљени макро. Све је то из снимљеног макронаредбе. Завршите ово Иф са завршетком Иф. Завршите ово са следећим ВС.= ”=” 2, што је оно што је снимљени код управо радио, спојио сам све што се налази у тој ћелији. Дакле, проверавамо да ли није једнак тренутној вредности. Дакле, ако ћелија тренутно има 2, кажемо да није једнако 2. Ако ћелија тренутно има 16,5, кажемо да није једнако 16,5. А онда је све ово само директно снимљени макро, снимљени макро, снимљени макро, снимљени макро. Све је то из снимљеног макронаредбе. Завршите ово Иф са завршетком Иф. Завршите ово са следећим ВС.= ”=” 2, што је оно што је снимљени код управо радио, спојио сам све што се налази у тој ћелији. Дакле, проверавамо да ли није једнак тренутној вредности. Дакле, ако ћелија тренутно има 2, кажемо да није једнако 2. Ако ћелија тренутно има 16,5, кажемо да није једнако 16,5. А онда је све ово само директно снимљени макро, снимљени макро, снимљени макро, снимљени макро. Све је то из снимљеног макронаредбе. Завршите ово Иф са завршетком Иф. Завршите ово са следећим ВС.снимљени макро, снимљени макро. Све је то из снимљеног макронаредбе. Завршите ово Иф са завршетком Иф. Завршите ово са следећим ВС.снимљени макро, снимљени макро. Све је то из снимљеног макронаредбе. Завршите ово Иф са завршетком Иф. Завршите ово са следећим ВС.
У реду, тако да имам макро под називом АпплиЦФ. Вратите се у Екцел, додајте облик. Овде је лако имати облик: Уметни, увек бирам заобљени правоугаоник, укуцај Врати на тренутне вредности. Применићемо Хоме, центар и центар да га мало повећају. Волим сјај. Претпостављам да мислите да је глупо кад је нема, сјај, поставка која ми се свиђа нису ту, па увек идем на Изглед странице и ефекти и бирам ону другу. А онда, када се вратим на формат, могу да изаберем онај који заправо има мало сјаја. Мени се чини да изгледа цоол, мислим да вреди. Кликните десним тастером миша, доделите макронаредбу и изговорите АпплиЦФ, кликните ОК. У реду, а онда ће ово када се кликне проћи кроз све ове листове, пронаћи све ћелије формуле и поставити условно форматирање које каже: Ако ове ћелије нису једнаке 7,променити боју, у реду? То је то. То је тако брзо, догодило се тако брзо. БАМ! Готово је. А сада, пазите да ли ову променим на 11, све те ћелије су се управо промениле. Ако се врати на 1, ахх, боје су се промениле. Дакле, каква год била вредност, када се променимо - ако променим ову ћелију, промене се све те ћелије. Ако променим ову ћелију, промене се све те ћелије. Ако променим ову ћелију, промене се све те ћелије.све те ћелије се мењају.све те ћелије се мењају.
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.
Па, хеј, желим да ти захвалим што си свратио. Видимо се следећи пут за још једно емитовање од.
Скини докуменат
Преузмите датотеку узорка овде: Подцаст2059.клсм
