Excel Formül Hücrelerindeki Değişiklikleri İzleyin. Belirli giriş hücrelerinin değiştirilmesinin bir sonucu olarak az önce hangi öğelerin değiştiğini gösterebilir misiniz?
Video izle
- Excel'deki değişiklikleri izlemek biraz tuhaf.
- Hedef, Excel'deki hangi formül hücrelerinin değiştiğini izlemektir.
- Çalışma kitabını XLSM olarak kaydetmek için Farklı Kaydet.
- Makro Güvenliğini Değiştirin.
- 2'ye eşit olmayan sayılar için koşullu biçimlendirmeyi ayarlamak üzere kodu bulmak için bir makro kaydedin.
- İstediğiniz Biçimlendirmeyi seçin.
- CF'yi çalışma sayfasından nasıl kaldıracağınızı öğrenmek için başka bir makro kaydedin.
- Makroda, her çalışma sayfası için bir döngü ekleyin.
- Başlıkta çalışmasını önlemek için bir IF ifadesi ekleyin.
- Her formül hücresini kontrol etmek için bir döngü ekleyin.
- Makronun çalıştığı anda hücre değerinin olup olmadığını görmek için Koşullu Biçimlendirme ekleyin.
- Excel'e geri dönün.
- Bir şekil ekleyin. Makroyu şekle atayın.
- Makroyu Çalıştırmak için Şekle tıklayın.
- Bonus İpucu: Bir VBA modülünü yeni bir çalışma kitabına sürüklemek.
Video Metni
Podcast'ten Excel Öğrenin, Bölüm 2059: Excel Değişiklikleri İzle (Formül sonuçlarında)
Hey, İnternet'e tekrar hoş geldiniz, ben Bill Jelen. Bugün Montreal'den yol değişiklikleri hakkında gönderilen soru. Değişiklikleri izleyin, tamam. İşte elimizde ne var. 4 Giriş Hücresimiz ve bu Giriş Hücrelerine dayanan bir sürü Formül hücreniz var. Ve eğer açarsam, Gözden Geçirme sekmesine geri döneceğim, Değişiklikleri Vurgula'yı açacağım, Değişiklikleri düzenleme sırasında izle, Tamam'ı tıklayın. Çalışma kitabını kaydetmeleri gerektiği ve makroların paylaşılan çalışma kitaplarında kullanılamayacağı konusunda beni uyardılar. Bunu biliyorsun? Değişiklikleri takip ettiğinizde sorun budur, çalışma kitabını paylaşırlar ve makrolar ve diğer pek çok şey gibi paylaşılan çalışma kitaplarında gerçekleşemeyen bir sürü şey vardır. Ancak, değişiklikleri izleme bugün Excel'de nasıl çalıştığına bir göz atalım.
Bu 2'yi alalım ve 2'den 22'ye değiştirelim ve bu 4'ü alalım ve 4'ten 44'e değiştirelim. Pekala, iz değişikliklerinde not ettikleri şey bu iki hücrenin değişti, pekala, bu mor üçgenler gerçek parça değişiklikleri. Tüm bu kırmızı şeyler olmaz ama ben sadece tüm bu kırmızı hücrelerin değiştiğini ve değişiklikleri takip etmenin bu değişiklikler hakkında hiçbir şey söylemediğini gösterdim, tamam mı? Yani, sadece bu iki hücrenin değiştiğini ancak diğer tüm hücrelerin de değiştiğini söylüyor. Ve o zaman Montreal'den gelen soru şudur: İz değişikliklerinin bize sadece bu giriş hücrelerinin değişmediğini, aslında değişen her şeyi göstermesini sağlamanın bir yolu var mı?
Pekala, yapmamız gereken ilk şey Excel'in yerleşik Değişiklikleri İzle özelliğini kapatmak. Ve sonra, elde edebileceğimiz bir yol var mı? Değişen tüm formül hücrelerini görmemizi sağlayacak kendi değişiklik izleme sistemimizi oluşturabilir miyiz? Pekala, 1. Adım ve bu adım en önemli adım, bunu atlamayın. Dosyanıza bakın, dosyanıza XLSX adı verilir, bunu kaydetmeniz gerekir: Dosya, Farklı Kaydet, Makro etkin çalışma kitabı olarak, yoksa bunların hiçbiri çalışmaz. Sağ tıklamanız, Şeridi Özelleştirmeniz, Geliştirici'yi açmanız, Geliştirici'ye vardığınızda, Makro Güvenlik'e gitmeniz, bu ayardan değişiklik yapmanız gerekir - makroların çalışmasına izin vermeyeceğimizi ya da söylemeyeceğimizi söyleyen bu ortamda olduklarını siz söyleyin. Bu iki adımı yapmanız gerekir. Bu iki adımı zaten yaptım. Her günü bu iki adımla yaşıyorum.Zaten düzeltildi, ancak makrolarda yeniyseniz, bu sizin için yenidir. Ve sonra, ne tür bir biçimlendirme istediğinizi bulmamız gerekiyor. Pekala, burada birkaç hücre seçeceğim, HowToCFRed adlı bir Makro Kaydedeceğim, bir kısayol tuşu atamayacağım çünkü bu bir daha asla çalışmayacak. Koşullu biçimlendirmenin nasıl çalıştığını anlamak için kod kaydediyorum. Ve Ana Sayfa'ya, Koşullu Biçimlendirmeye, Eşit olmayan Hücreleri Vurgulamaya başlayacağız - Yani, Daha Fazla Kural, Hücreleri eşitlemez - Gördünüz mü? Orijinal açılır menüde değil, ancak buraya gelirseniz 2'ye eşit değildir ve sonra biçimi seçin. Bu önemli kısım. Bu yüzden kırmızı bir arka plan seçeceğim. Burada istediğin rengi seç, tamam mı? Hatta Daha Fazla Renk'e gidin, başka bir kırmızıyı seçin,Custom'a girin, başka bir kırmızı seçin, tamam mı? Makro Kaydedicinin güzelliği bu, bize sizin için mükemmel bir kırmızı ya da mavi ya da istediğiniz her neyse verecekler. Pekala, Tamam'ı tıklayın. Ve sonra, kaydı durduracağız, pekala. Yine, bunun tüm amacı, koşullu formatlar için kodun ne olduğunu görmektir.
Makrolar, Kırmızı koşullu biçimlendirme nasıl yapılır ve düzenleme bölümüne gidiyorum. Pekala, işte bu kodun önemli kısımları. XlNotEqual kullanarak koşullu bir biçim eklediklerini görebiliyorum ve 2'ye eşit olmaması için zor alıntı yapıyoruz. Ve sonra hücrenin içini o renge değiştiriyoruz.
Pekala, sayfadaki tüm koşullu biçimlendirmeyi nasıl sileceğimi de bulmam gerekiyor. Öyleyse, Excel'e dönün, Başka bir Makro kaydedin, Tüm koşullu nasıl silinir, Tamam. Buraya Ana Sayfa sekmesine gelin, Koşullu Biçimlendirme'ye gidin, Tüm sayfadan Kuralı Temizle'ye gidin, Kaydı Durdurun ve bu koda bakalım. Harika, tek satırlık bir makro. Ve burayı bile, tüm sayfa için yaptıkları şekilde sadece hücrelere atıfta bulunmalarını seviyorum. Diğer bir deyişle, aktif sayfadaki tüm hücreler.
Şimdi, bu makroyu, kaydedilen makroyu biraz daha genel hale getirmem gerekiyor. Ve Excel'de VBA'nın nasıl yapılacağına dair birçok kitap yazdım ve Excel'de VBA'nın nasıl yapılacağına dair videolar hazırladım ve işte basit olan şey: bunun gibi bir makro kaydedebilmelisiniz ama sonra şunu ekleyin: makroyu yeterince jenerik yapabilmek için yaklaşık beş veya altı satır.
Ve ben bu satırlardan bahsedeceğim, pekala. Yani yapmak istediğim ilk şey, şunu söylemek istiyorum, aktif çalışma kitabını gözden geçirmek, tüm çalışma sayfalarını gözden geçirmek istiyorum. Yani her çalışma sayfası için WS nesne değişkeni, tüm çalışma sayfalarını gözden geçireceğim. Ve Montreal'den biri, "Hey, bunun olmasını istemediğim bir sayfa var" dedi. Öyleyse, çalışma sayfası nokta adıyla WS.Name, Başlık'a eşit değilse, kodu makroda yapacağız. İşte sayfa adı: .Cells.FormatConditions.Delete. Bu nedenle, başlık dışında sayfanın her bir tek tekini gözden geçireceğiz ve tüm biçim koşullarını sileceğiz, ardından sayfadaki her bir hücreye gideceğiz, ancak tüm hücreleri değil, yalnızca formülleri olan hücreleri inceleyeceğiz. . Formülü yoksa ben de yokumbiçimlendirmeye gerek yok çünkü değişmeyecek. Cell.FormatConditions.Add, bu doğrudan makrodan, ancak kaydedilen makro Seçim dedi - onu seçmek zorunda kalmak istemiyorum, bu yüzden sadece Cell diyeceğim, bu her bir hücre. XlNotEqual'i kullanacağız ve Formula: = ”=” 2 yerine kaydedilen kodun tam orada yaptığı şey, o hücrede ne varsa onu birleştirdim. Yani mevcut değere eşit olup olmadığını kontrol edin. Yani hücrede şu anda 2 varsa, 2'ye eşit değil diyoruz. Hücrede şu anda 16,5 varsa, 16,5'e eşit değil diyoruz. Ve sonra bunun geri kalanı sadece düz kaydedilmiş makro, kaydedilmiş makro, kaydedilmiş makro, kaydedilmiş makro. Bunların hepsi kaydedilmiş bir makrodan. Bunu Eğer Bir End If ile bitirin. Bunu Bir Sonraki WS ile Bitirin. değişmeyecek. Cell.FormatConditions.Add, bu doğrudan makrodan, ancak kaydedilen makro Seçim dedi - onu seçmek zorunda kalmak istemiyorum, bu yüzden sadece Cell diyeceğim, bu her bir hücre. XlNotEqual'i kullanacağız ve Formula: = ”=” 2 yerine kaydedilen kodun tam orada yaptığı şey, o hücrede ne varsa onu birleştirdim. Yani mevcut değere eşit olup olmadığını kontrol edin. Yani hücrede şu anda 2 varsa, 2'ye eşit değil diyoruz. Hücrede şu anda 16,5 varsa, 16,5'e eşit değil diyoruz. Ve sonra bunun geri kalanı sadece düz kaydedilmiş makro, kaydedilmiş makro, kaydedilmiş makro, kaydedilmiş makro. Bunların tümü kaydedilmiş bir makrodan. Bunu Eğer Bir End If ile bitirin. Bunu Bir Sonraki WS ile Bitirin.değişmeyecek. Cell.FormatConditions.Add, bu doğrudan makrodan, ancak kaydedilen makro Seçim dedi - onu seçmek zorunda kalmak istemiyorum, bu yüzden sadece Cell diyeceğim, bu her bir hücre. XlNotEqual'i kullanacağız ve Formula: = ”=” 2 yerine kaydedilen kodun tam orada yaptığı şey, o hücrede ne varsa onu birleştirdim. Yani mevcut değere eşit olup olmadığını kontrol edin. Yani hücrede şu anda 2 varsa, 2'ye eşit değil diyoruz. Hücrede şu anda 16,5 varsa, 16,5'e eşit değil diyoruz. Ve sonra bunun geri kalanı sadece düz kaydedilmiş makro, kaydedilmiş makro, kaydedilmiş makro, kaydedilmiş makro. Bunların hepsi kaydedilmiş bir makrodan. Bunu Eğer Bir End If ile bitirin. Bunu Bir Sonraki WS ile Bitirin.Bu, kaydedilen makro Seçim demesine rağmen doğrudan makrodan geliyor - onu seçmek zorunda kalmak istemiyorum, bu yüzden sadece Hücre diyeceğim, bu her bir hücre. XlNotEqual'i kullanacağız ve Formula: = ”=” 2 yerine kaydedilen kodun tam orada yaptığı şey, o hücrede ne varsa onu birleştirdim. Yani mevcut değere eşit olup olmadığını kontrol edin. Yani hücrede şu anda 2 varsa, 2'ye eşit değil diyoruz. Hücrede şu anda 16,5 varsa, 16,5'e eşit değil diyoruz. Ve sonra bunun geri kalanı sadece düz kaydedilmiş makro, kaydedilmiş makro, kaydedilmiş makro, kaydedilmiş makro. Bunların hepsi kaydedilmiş bir makrodan. Bunu If ile bitirin. Bunu Bir Sonraki WS ile Bitirin.Kaydedilen makro Seçim dediğine rağmen bu doğrudan makrodan geliyor - onu seçmek zorunda kalmak istemiyorum, bu yüzden sadece Hücre diyeceğim, bu her bir hücre. XlNotEqual'i kullanacağız ve Formula: = ”=” 2 yerine kaydedilen kodun tam orada yaptığı şey, o hücrede ne varsa onu birleştirdim. Yani mevcut değere eşit olup olmadığını kontrol edin. Yani hücrede şu anda 2 varsa, 2'ye eşit değil diyoruz. Hücrede şu anda 16,5 varsa, 16,5'e eşit değil diyoruz. Ve sonra bunun geri kalanı sadece düz kaydedilmiş makro, kaydedilmiş makro, kaydedilmiş makro, kaydedilmiş makro. Bunların hepsi kaydedilmiş bir makrodan. Bunu Eğer Bir End If ile bitirin. Bunu Bir Sonraki WS ile Bitirin.onu seçmek zorunda kalmak istemiyorum, bu yüzden sadece Cell diyeceğim, bu her bir hücre. XlNotEqual'i kullanacağız ve Formula: = ”=” 2 yerine kaydedilen kodun tam orada yaptığı şey, o hücrede ne varsa onu birleştirdim. Yani mevcut değere eşit olup olmadığını kontrol edin. Yani hücrede şu anda 2 varsa, 2'ye eşit değil diyoruz. Hücrede şu anda 16,5 varsa, 16,5'e eşit değil diyoruz. Ve sonra bunun geri kalanı sadece düz kaydedilmiş makro, kaydedilmiş makro, kaydedilmiş makro, kaydedilmiş makro. Bunların hepsi kaydedilmiş bir makrodan. Bunu If ile bitirin. Bunu Bir Sonraki WS ile Bitirin.onu seçmek zorunda kalmak istemiyorum, bu yüzden sadece Cell diyeceğim, bu her bir hücre. XlNotEqual'i kullanacağız ve Formula: = ”=” 2 yerine kaydedilen kodun tam orada yaptığı şey, o hücrede ne varsa onu birleştirdim. Yani mevcut değere eşit olup olmadığını kontrol edin. Yani hücrede şu anda 2 varsa, 2'ye eşit değil diyoruz. Hücrede şu anda 16,5 varsa, 16,5'e eşit değil diyoruz. Ve sonra bunun geri kalanı sadece düz kaydedilmiş makro, kaydedilmiş makro, kaydedilmiş makro, kaydedilmiş makro. Bunların hepsi kaydedilmiş bir makrodan. Bunu Eğer Bir End If ile bitirin. Bunu Bir Sonraki WS ile Bitirin.= ”=” 2 kaydedilen kodun orada yaptığı şeydi, o hücrede ne varsa onu birleştirdim. Yani mevcut değere eşit olup olmadığını kontrol edin. Yani hücrede şu anda 2 varsa, 2'ye eşit değil diyoruz. Hücrede şu anda 16,5 varsa, 16,5'e eşit değil diyoruz. Ve sonra bunun geri kalanı sadece düz kaydedilmiş makro, kaydedilmiş makro, kaydedilmiş makro, kaydedilmiş makro. Bunların tümü kaydedilmiş bir makrodan. Bunu Eğer Bir End If ile bitirin. Bunu Bir Sonraki WS ile Bitirin.= ”=” 2 kaydedilen kodun orada yaptığı şey buydu, o hücrede ne varsa onu birleştirdim. Yani mevcut değere eşit olup olmadığını kontrol edin. Yani hücrede şu anda 2 varsa, 2'ye eşit değil diyoruz. Hücrede şu anda 16,5 varsa, 16,5'e eşit değil diyoruz. Ve sonra bunun geri kalanı sadece düz kaydedilmiş makro, kaydedilmiş makro, kaydedilmiş makro, kaydedilmiş makro. Bunların hepsi kaydedilmiş bir makrodan. Bunu If ile bitirin. Bunu Bir Sonraki WS ile Bitirin.kaydedilmiş makro, kaydedilmiş makro. Bunların hepsi kaydedilmiş bir makrodan. Bunu Eğer Bir End If ile bitirin. Bunu Bir Sonraki WS ile Bitirin.kaydedilmiş makro, kaydedilmiş makro. Bunların hepsi kaydedilmiş bir makrodan. Bunu If ile bitirin. Bunu Bir Sonraki WS ile Bitirin.
Pekala, ApplyCF adında bir makrom var. Excel'e geri dönün, bir şekil ekleyin. Burada bir şekle sahip olmak kolaydır: Ekle, her zaman yuvarlak bir dikdörtgen seçerim, Geçerli Değerlere Sıfırla yazın. Yuva'yı, merkeze uygulayacağız ve merkez onu biraz daha büyüteceğiz. Parıltısı seviyorum. Sanırım orada olmadığını görmenin aptalca olduğunu düşünüyorsunuz, parıltı, sevdiğim ortam orada değil, bu yüzden her zaman Sayfa Düzeni ve Efektler'e gidip ikincisini seçiyorum. Ve sonra formata geri döndüğümde, aslında biraz parıltılı olanı seçebilirim. Bana göre harika göründüğünü düşünüyorum, buna değer olduğunu düşünüyorum. Sağ tıklayın, Makro Ata ve Uygula deyin, Tamam'a tıklayın. Pekala, ve sonra bunun yapacağı şey, tıkladığımda, tüm bu sayfalardan geçecek, tüm formül hücrelerini bulacak ve şunu söyleyen bir koşullu biçimlendirme ayarlayacak: Bu hücreler 7'ye eşit değilse,rengi değiştir, tamam mı? Bu kadar. O kadar hızlı, o kadar hızlı oldu. BAM! Bitti. Ve şimdi, bunu 11'e değiştirirsem, tüm bu hücreler değişti. Şimdi 1'e geri dönerse, ahh, renkler değişti. Yani, değer ne olursa olsun, değiştirdiğimizde - bu hücreyi değiştirirsem, tüm bu hücreler değişir. Bu hücreyi değiştirirsem, tüm bu hücreler değişir. Bu hücreyi değiştirirsem, tüm bu hücreler değişir.tüm bu hücreler değişir.tüm bu hücreler değişir.
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.
Pekala, uğradığın için teşekkür etmek istiyorum. Bir dahaki sefere başka bir internet yayını için görüşürüz.
Dosyayı indir
Örnek dosyayı buradan indirin: Podcast2059.xlsm
