Śledź zmiany w komórkach formuł programu Excel. Czy możesz pokazać, które elementy właśnie się zmieniły w wyniku zmiany niektórych komórek wejściowych?
Obejrzyj wideo
- Śledzenie zmian w programie Excel jest trochę dziwne.
- Celem jest śledzenie zmian w komórkach formuł w programie Excel.
- Zapisz jako, aby zapisać skoroszyt jako XLSM.
- Zmień zabezpieczenia makr.
- Zarejestruj makro, aby dowiedzieć się, jak skonfigurować formatowanie warunkowe dla liczb nie równych 2.
- Wybierz odpowiednie formatowanie.
- Zarejestruj kolejne makro, aby dowiedzieć się, jak usunąć CF z arkusza.
- W makrze dodaj pętlę dla każdego arkusza.
- Dodaj instrukcję IF, aby uniemożliwić jej uruchamianie na Title.
- Dodaj pętlę, aby sprawdzić każdą komórkę formuły.
- Dodaj formatowanie warunkowe, aby sprawdzić, czy wartość komórki w czasie działania makra.
- Wróć do programu Excel.
- Dodaj kształt. Przypisz makro do kształtu.
- Kliknij kształt, aby uruchomić makro.
- Dodatkowa wskazówka: Przeciągnij moduł VBA do nowego skoroszytu.
Transkrypcja wideo
Naucz się programu Excel z podcastu, odcinek 2059: Excel Śledź zmiany (w wynikach formuł)
Hej, witaj z powrotem w netcastie, jestem Bill Jelen. Dzisiejsze pytanie wysłane z Montrealu dotyczące zmian w torach. Śledź zmiany, dobrze. Oto, co mamy. Mamy 4 komórki wejściowe i całą masę komórek formuł, które opierają się na tych komórkach wejściowych. A jeśli bym włączył, wrócę do zakładki Recenzja, włączę Podświetl zmiany, Śledź zmiany podczas edycji, kliknij OK, w porządku. Ostrzegli mnie, że muszą zapisać skoroszyt i że makra nie mogą być używane we współdzielonych skoroszytach. Wiesz to? To jest problem, gdy śledzisz zmiany, udostępniają skoroszyt i jest cała masa rzeczy, które nie mogą się wydarzyć w udostępnionych skoroszytach, wiesz, jak makra i cała masa innych rzeczy. Ale spójrzmy tylko, jak działa śledzenie zmian w programie Excel dzisiaj.
Weźmy to 2 i zmieńmy z 2 na 22, weźmy to 4 i zmieńmy z 4 na 44. W porządku, i widzicie, co zauważyli w śledzeniu zmian, to że te dwie komórki się zmieniły, w porządku, te fioletowe trójkąty są rzeczywiste zmiany ścieżki. Wszystkie te czerwone rzeczy, które się nie zdarzają, ale właśnie zilustrowałem, że wszystkie te czerwone krwinki się zmieniają i śledzenie zmian nic nie mówi o tych zmianach, dobrze? Tak więc, mówi tylko, że te dwie komórki zostały zmienione, ale wszystkie te inne komórki również zostały zmienione. Zatem pytanie z Montrealu brzmi: czy istnieje sposób, aby śledzenie zmian faktycznie pokazywało nam wszystko, co się zmienia, a nie tylko te komórki wejściowe się zmieniły?
W porządku, więc pierwszą rzeczą, którą musimy zrobić, jest wyłączenie wbudowanego śledzenia zmian w programie Excel. A potem, czy jest sposób, który możemy uzyskać - możemy zbudować własny system śledzenia zmian, który pozwoli nam zobaczyć wszystkie komórki formuły, które się zmieniły? W porządku, więc krok 1 i ten krok jest najważniejszym krokiem, nie pomijaj tego. Spójrz na swój plik, twój plik nazywa się czymś XLSX, musisz zapisać to: Plik, Zapisz jako, Jako skoroszyt z włączoną obsługą makr lub nic z tego nie zadziała. Musisz kliknąć prawym przyciskiem myszy, Dostosuj Wstążkę, włączyć Dewelopera, gdy dojdziesz do Dewelopera, przejdź do Bezpieczeństwa makr, zmień to ustawienie - to, które mówi, że nie pozwolimy uruchamiać makr lub nawet nie zamierzamy powiedzieć że są tam w tym miejscu. Musisz zrobić te dwa kroki. Zrobiłem już te dwa kroki. Codziennie żyję tymi dwoma krokami.To już naprawione, ale jeśli jesteś nowy w makrach, to jest dla Ciebie nowość. Następnie musimy dowiedzieć się, jakiego rodzaju formatowania chcesz. W porządku, więc po prostu wybiorę tutaj kilka komórek, zamierzam nagrać makro o nazwie HowToCFRed, nie zamierzam przypisywać do klawisza skrótu, ponieważ to się nigdy nie uruchomi. Po prostu nagrywam kod, aby dowiedzieć się, jak działa formatowanie warunkowe. I przejdziemy do Strona główna, Formatowanie warunkowe, Podświetl komórki, które nie są równe - Więc, Więcej reguł, Formatowanie komórek nie jest równe - Widzisz? Nie ma tego w oryginalnym menu rozwijanym, ale jeśli wejdziesz tutaj, nie jest równe 2, a następnie wybierz format. To jest ważna część. Więc wybiorę czerwone tło. Wybierasz tutaj dowolny kolor, dobrze? Przejdź nawet do Więcej kolorów, wybierz inny czerwony,przejdź do Custom, wybierz inny czerwony, dobrze? Na tym polega piękno Rejestratora Makr, dadzą nam idealną czerwień dla Ciebie, błękit czy cokolwiek zechcesz. W porządku, kliknij OK. A potem zatrzymamy nagrywanie, w porządku. Ponownie, chodzi o to, aby zobaczyć, jaki jest kod dla formatów warunkowych.
Idę do Makra, Jak warunkowo formatować na czerwono i edytować. W porządku, więc oto ważne części tego kodu. Widzę, że dodają format warunkowy za pomocą xlNotEqual i trudno nam cytować, aby nie był równy 2. A potem zmieniamy wnętrze komórki na ten kolor.
W porządku, muszę też dowiedzieć się, jak usunąć całe formatowanie warunkowe z arkusza. Wracając do Excela, nagraj kolejne makro, jak usunąć wszystkie warunkowe, OK. Przejdź tutaj do karty Strona główna, przejdź do Formatowanie warunkowe, Wyczyść regułę z całego arkusza, Zatrzymaj nagrywanie, a my przyjrzymy się temu kodowi. Świetnie, to jednowierszowe makro. I nawet podoba mi się to, że sposób, w jaki robią to dla całego arkusza, odnosi się tylko do komórek. Innymi słowy, wszystkie komórki w aktywnym arkuszu.
Teraz muszę uczynić to makro, zarejestrowane makro, trochę bardziej ogólne. Napisałem wiele książek o tym, jak robić VBA w Excelu i nagrałem filmy o tym, jak robić VBA w Excelu, a oto prosta rzecz: musisz być w stanie nagrać takie makro, ale potem dodaj około pięciu lub sześciu wierszy, aby makro było wystarczająco ogólne.
I mam zamiar porozmawiać o tych kwestiach, w porządku. Więc pierwszą rzeczą, którą chcę zrobić, jest powiedzieć, chcę przejrzeć aktywny skoroszyt, przejrzeć wszystkie arkusze. Więc dla każdego arkusza roboczego WS jest zmienną obiektu, przejdę przez wszystkie arkusze. A osoba z Montrealu powiedziała: „Hej, jest jeden arkusz, na którym nie chcę, żeby to się stało”. Tak więc, jeśli WS.Name, z nazwą kropki arkusza roboczego, nie jest równe Tytułowi, zrobimy kod w makrze. Oto nazwa arkusza: .Cells.FormatConditions.Delete. Więc przejdziemy przez każdą osobę z arkusza z wyjątkiem tytułu i usuniemy wszystkie warunki formatowania, a następnie przejdziemy przez każdą komórkę w arkuszu, ale nie przez wszystkie komórki, tylko komórki zawierające formuły . Jeśli nie ma wzoru, to nie robięNie trzeba go sformatować, ponieważ się nie zmieni. Cell.FormatConditions.Add, to jest bezpośrednio z makra, chociaż zarejestrowane makro mówiło: Selection - nie chcę go wybierać, więc powiem tylko Cell, to jest każda pojedyncza komórka. Użyjemy xlNotEqual i zamiast Formuły: = ”=” 2, czyli to, co zrobił nagrany kod, połączyłem wszystko, co jest w tej komórce. Więc sprawdzam, czy nie jest równa bieżącej wartości. Więc jeśli komórka ma obecnie 2, mówimy, że nie jest równa 2. Jeśli komórka ma obecnie 16,5, mówimy, że nie jest równa 16,5. A reszta to po prostu nagrane makro, zarejestrowane makro, zarejestrowane makro, zarejestrowane makro. Wszystko to z zarejestrowanego makra. Zakończ to If z End If. Zakończ to za pomocą następnego WS. to się nie zmieni. Cell.FormatConditions.Add, to jest bezpośrednio z makra, chociaż nagrane makro mówiło: Selection - nie chcę go wybierać, więc powiem tylko Cell, to każda pojedyncza komórka. Użyjemy xlNotEqual i zamiast Formuły: = ”=” 2, czyli to, co zrobił nagrany kod, połączyłem wszystko, co jest w tej komórce. Więc sprawdzam, czy nie jest równa bieżącej wartości. Więc jeśli komórka ma obecnie 2, mówimy, że nie jest równa 2. Jeśli komórka ma obecnie 16,5, mówimy, że nie jest równa 16,5. A reszta to po prostu nagrane makro, zarejestrowane makro, zarejestrowane makro, zarejestrowane makro. Wszystko to z zarejestrowanego makra. Zakończ to If z End If. Zakończ to za pomocą następnego WS.to się nie zmieni. Cell.FormatConditions.Add, to jest bezpośrednio z makra, chociaż nagrane makro mówiło: Selection - nie chcę go wybierać, więc powiem tylko Cell, to każda pojedyncza komórka. Użyjemy xlNotEqual i zamiast Formuły: = ”=” 2, czyli to, co zrobił nagrany kod, połączyłem wszystko, co jest w tej komórce. Więc sprawdzam, czy nie jest równa bieżącej wartości. Więc jeśli komórka ma obecnie 2, mówimy, że nie jest równa 2. Jeśli komórka ma obecnie 16,5, mówimy, że nie jest równa 16,5. A reszta to po prostu nagrane makro, zarejestrowane makro, zarejestrowane makro, zarejestrowane makro. Wszystko to z zarejestrowanego makra. Zakończ to If z End If. Zakończ to za pomocą następnego WS.pochodzi to bezpośrednio z makra, chociaż nagrane makro mówiło Selection - nie chcę go wybierać, więc powiem tylko Cell, to jest każda pojedyncza komórka. Użyjemy xlNotEqual i zamiast Formuły: = ”=” 2, czyli to, co zrobił nagrany kod, połączyłem wszystko, co jest w tej komórce. Więc sprawdzam, czy nie jest równa bieżącej wartości. Więc jeśli komórka ma obecnie 2, mówimy, że nie jest równa 2. Jeśli komórka ma obecnie 16,5, mówimy, że nie jest równa 16,5. A reszta to po prostu nagrane makro, zarejestrowane makro, zarejestrowane makro, zarejestrowane makro. Wszystko to z zarejestrowanego makra. Zakończ to If z End If. Zakończ to za pomocą następnego WS.pochodzi to bezpośrednio z makra, chociaż nagrane makro mówiło Selection - nie chcę go wybierać, więc powiem tylko Cell, to jest każda pojedyncza komórka. Użyjemy xlNotEqual i zamiast Formuły: = ”=” 2, czyli to, co zrobił nagrany kod, połączyłem wszystko, co jest w tej komórce. Więc sprawdzam, czy nie jest równa bieżącej wartości. Więc jeśli komórka ma obecnie 2, mówimy, że nie jest równa 2. Jeśli komórka ma obecnie 16,5, mówimy, że nie jest równa 16,5. A reszta to po prostu nagrane makro, zarejestrowane makro, zarejestrowane makro, zarejestrowane makro. Wszystko to z zarejestrowanego makra. Zakończ to If z End If. Zakończ to za pomocą następnego WS.Nie chcę go wybierać, więc powiem tylko Komórka, to jest każda pojedyncza komórka. Użyjemy xlNotEqual i zamiast Formuły: = ”=” 2, czyli to, co zrobił nagrany kod, połączyłem wszystko, co jest w tej komórce. Więc sprawdzam, czy nie jest równa bieżącej wartości. Więc jeśli komórka ma obecnie 2, mówimy, że nie jest równa 2. Jeśli komórka ma obecnie 16,5, mówimy, że nie jest równa 16,5. A reszta to po prostu nagrane makro, zarejestrowane makro, zarejestrowane makro, zarejestrowane makro. Wszystko to z zarejestrowanego makra. Zakończ to If z End If. Zakończ to za pomocą następnego WS.Nie chcę go wybierać, więc powiem tylko Komórka, to jest każda pojedyncza komórka. Użyjemy xlNotEqual i zamiast Formuły: = ”=” 2, czyli to, co zrobił nagrany kod, połączyłem wszystko, co jest w tej komórce. Więc sprawdzam, czy nie jest równa bieżącej wartości. Więc jeśli komórka ma obecnie 2, mówimy, że nie jest równa 2. Jeśli komórka ma obecnie 16,5, mówimy, że nie jest równa 16,5. A reszta to po prostu nagrane makro, zarejestrowane makro, zarejestrowane makro, zarejestrowane makro. Wszystko to z zarejestrowanego makra. Zakończ to If z End If. Zakończ to za pomocą następnego WS.= ”=” 2, czyli to, co zrobił nagrany kod, połączyłem wszystko, co jest w tej komórce. Więc sprawdzam, czy nie jest równa bieżącej wartości. Więc jeśli komórka ma obecnie 2, mówimy, że nie jest równa 2. Jeśli komórka ma obecnie 16,5, mówimy, że nie jest równa 16,5. A reszta to po prostu nagrane makro, zarejestrowane makro, zarejestrowane makro, zarejestrowane makro. Wszystko to z zarejestrowanego makra. Zakończ to If z End If. Zakończ to za pomocą następnego WS.= ”=” 2, czyli to, co zrobił nagrany kod, połączyłem wszystko, co jest w tej komórce. Więc sprawdzam, czy nie jest równa bieżącej wartości. Więc jeśli komórka ma obecnie 2, mówimy, że nie jest równa 2. Jeśli komórka ma obecnie 16,5, mówimy, że nie jest równa 16,5. A reszta to po prostu nagrane makro, zarejestrowane makro, zarejestrowane makro, zarejestrowane makro. Wszystko to z zarejestrowanego makra. Zakończ to If z End If. Zakończ to za pomocą następnego WS.nagrane makro, zarejestrowane makro. Wszystko to z zarejestrowanego makra. Zakończ to If z End If. Zakończ to za pomocą następnego WS.nagrane makro, zarejestrowane makro. Wszystko to z zarejestrowanego makra. Zakończ to If z End If. Zakończ to za pomocą następnego WS.
W porządku, więc mam makro o nazwie ApplyCF. Wróć do programu Excel, dodaj kształt. Łatwo tu mieć kształt: Wstaw, zawsze wybieram zaokrąglony prostokąt, wpisuję Resetuj do bieżących wartości. Zastosujemy Dom, środek, a środek sprawi, że będzie trochę większy. Uwielbiam ten blask. Przypuszczam, że myślisz, że to głupie, widząc, że go tam nie ma, blasku, ustawienia, które lubię, nie ma, więc zawsze idę do układu strony i efektów i wybieram to drugie. A kiedy wracam do formatu, mogę wybrać taki, który faktycznie ma trochę blasku. Dla mnie wydaje mi się, że wygląda fajnie, myślę, że warto. Kliknij prawym przyciskiem myszy, Przypisz makro i powiedz ZastosujCF, kliknij OK. W porządku, a kiedy kliknę, przejdzie przez wszystkie te arkusze, znajdzie wszystkie komórki formuły i ustawia formatowanie warunkowe, które mówi: Jeśli te komórki nie są równe 7,zmień kolor, dobrze? Otóż to. To tak szybko, stało się tak szybko. BAM! Zrobione. A teraz zobacz, jeśli zmienię ten na 11, wszystkie te komórki właśnie się zmieniły. Teraz, jeśli wróci do 1, ahh, kolory się zmieniły. Więc niezależnie od wartości, kiedy się zmieniamy - jeśli zmienię tę komórkę, wszystkie te komórki się zmienią. Jeśli zmienię tę komórkę, wszystkie te komórki się zmienią. Jeśli zmienię tę komórkę, wszystkie te komórki się zmienią.wszystkie te komórki się zmieniają.wszystkie te komórki się zmieniają.
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.
Hej, chcę ci podziękować za zatrzymanie się. Do zobaczenia następnym razem z kolejnym netcastem od.
Pobieranie pliku
Pobierz przykładowy plik tutaj: Podcast2059.xlsm
