Excel makrolarının toplanması. MS Office'te etkin çalışma. Hücre ve aralık seçiminden kaçınma

Çok az kişi popüler bir ürünün ilk versiyonunun Microsoft Excel 1985'te ortaya çıktı. O zamandan beri, çeşitli değişikliklerden geçti ve dünya çapında milyonlarca kullanıcı tarafından talep görüyor. Aynı zamanda, birçok kişi bu elektronik tablo işlemcisinin yeteneklerinin yalnızca küçük bir kısmıyla çalışır ve Excel'de programlama yeteneğinin hayatlarını nasıl kolaylaştıracağını bile bilmiyor.

VBA nedir

Excel programlama dil kullanılarak gerçekleştirilir görsel programlama Başlangıçta en ünlü olanın içine yerleştirilmiş olan Temel Uygulama masa işlemcisi Microsoft'tan.

Uzmanlar, göreli geliştirme kolaylığını avantajlarına bağlamaktadır. Uygulamada görüldüğü gibi, profesyonel programlama becerilerine sahip olmayan kullanıcılar bile VBA'nın temellerini öğrenebilir. VBA'nın özellikleri, ofis ortamında komut dosyası yürütmeyi içerir.

Programın dezavantajı uyumluluk sorunlarıdır farklı versiyonlar... VBA program kodunun eriştiği gerçeğinden kaynaklanmaktadır. işlevsellikmevcut olan yeni sürüm ürün, ancak eskisinde eksik. Ayrıca dezavantajlar, değişim için kodun aşırı derecede yüksek açıklığını içerir. yabancı... Yine de Microsoft Officeve IBM Lotus Symphony, kullanıcının tohumu şifrelemesine ve görüntülemek için bir parola belirlemesine izin verir.

Nesneler, Koleksiyonlar, Özellikler ve Yöntemler

Bunlar VBA ortamında çalışacakların anlaması gereken kavramlardır. Her şeyden önce, bir nesnenin ne olduğunu anlamanız gerekir. Excel'de bu bir sayfa, çalışma kitabı, hücre ve aralıktır. Bu nesnelerin özel bir hiyerarşisi vardır, örn. birbirinize itaat edin.

Bunlardan en önemlisi, en çok karşılık gelen Uygulama excel programı... Bunu Çalışma Kitapları, Çalışma Sayfaları ve Aralık izler. Örneğin, belirli bir sayfadaki A1 hücresine başvurmak için hiyerarşiye dayalı bir yol belirtmelisiniz.

"Koleksiyon" kavramına gelince, aynı sınıftaki nesneler grubudur ve kayıtta ChartObjects formuna sahiptir. Ona bireysel unsurlar aynı zamanda nesnelerdir.

Bir sonraki kavram özelliklerdir. Onlar gerekli karakteristik herhangi bir nesne. Örneğin, Aralık için bu Değer veya Formüldür.

Yöntemler, yapılması gerekenleri belirten komutlardır. VBA'da kod yazarken, nesneden bir nokta ile ayrılmalıdırlar. Örneğin, daha sonra gösterileceği gibi, Hücreler (1,1) .Seç komutu, Excel'de programlama yapılırken çok sık kullanılır. Koordinatlı bir hücre seçmeniz gerektiği anlamına gelir

Selection.ClearContents genellikle onunla birlikte kullanılır. Yürütülmesi, seçilen hücrenin içeriğini temizlemek anlamına gelir.

Nasıl başlanır

Daha sonra "Alt" ve "F11" tuşlarının kombinasyonunu kullanmanın yeterli olduğu VB uygulamasına gitmelisiniz. Daha ileri:

• pencerenin üst kısmında bulunan menü çubuğunda, Excel simgesinin yanındaki simgeye tıklayın;
• mudule ekibini seçin;
• görüntülü simgeye tıklayarak kaydedin;
• diyelim ki bir kod taslağı yaz.

Şöyle görünüyor:

Alt program ()

"Kodumuz

Lütfen "Kodumuz" satırının farklı bir renkle (yeşil) vurgulanacağını unutmayın. Nedeni satırın başındaki kesme işaretinde, yani bir yorumun ardından geliyor.

Artık herhangi bir kod yazabilir ve kendiniz için oluşturabilirsiniz yeni enstrüman -de VBA Excel (aşağıdaki program örneklerine bakın). Tabii ki, temelleri bilenler için Visual Basicçok daha kolay olacak. Ancak, bunlara sahip olmayanlar bile, istenirse, yeterince çabuk alışabilecekler.

Excel makroları

Bu ad, dilde yazılmış programları gizler görsel dil Uygulama için Temel. Bu nedenle, Excel'de programlama, doğru kod... Bu özellik sayesinde masa microsoft işlemci kendi kendine gelişen, gereksinimlere uyum sağlayan belirli kullanıcı... Makro yazmak için modüllerin nasıl oluşturulacağını anladıktan sonra, düşünmeye başlayabilirsiniz. somut örnekler vBA programları Excel. En temel kodlarla başlamak en iyisidir.

örnek 1

Görev: Bir hücrenin içeriğinin değerini kopyalayacak ve ardından diğerine yazacak bir program yazın.

Bunun için:

• "Görünüm" sekmesini açın;
• "Makrolar" simgesine gidin;
• "Makro Kaydet" e tıklayın;
• açılan formu doldurun.

Basit olması için, "Makro adı" alanında "Makro1" bırakın ve örneğin "Klavye kısayolu" alanına hh girin (bu, programı "Ctrl + h" blitz komutuyla başlatabileceğiniz anlamına gelir). Enter'a bas.

Şimdi, makro kaydı başladığında, bir hücrenin içeriği diğerine kopyalanır. Orijinal simgeye geri döner. "Makro Kaydet" e tıklayın. Bu eylem, programın sonu anlamına gelir.

• tekrar "Makrolar" satırına gidin;
• listeden "Makro 1" i seçin;
• "Çalıştır" ı tıklayın (aynı eylem "Ctrl + hh" klavye kısayolunun başlatılmasıyla başlatılır).

Sonuç, makronun kaydedilmesi sırasında gerçekleştirilen eylemdir.

Kodun neye benzediğini görmek mantıklı. Bunu yapmak için, "Makrolar" satırına geri dönün ve "Değiştir" veya "Gir" düğmesini tıklayın. Sonuç olarak, kendilerini VBA ortamında bulurlar. Aslında, makronun kodu Sub Macro1 () ve End Sub satırları arasındadır.

Örneğin A1 hücresinden C1 hücresine kopyalama yapıldıysa, kod satırlarından biri Aralık ("C1") gibi görünecektir. Tercüme edildiğinde, "Aralık (" C1 ") gibi görünür. Seç", diğer bir deyişle VBA Excel'e, C1 hücresine geçer.

ActiveSheet.Paste komutu, kodun etkin bölümünü sonlandırır. Seçili hücrenin içeriğini yazmak anlamına gelir ( bu durumda A1) vurgulanan hücre C1'e.

Örnek 2

VBA döngüleri, Excel'de çeşitli makrolar oluşturmanıza yardımcı olur.

VBA döngüleri, çeşitli makrolar oluşturmanıza yardımcı olur. Bir y \u003d x + x 2 + 3x 3 - cos (x) fonksiyonu olduğunu varsayalım. Grafiğini elde etmek için bir makro oluşturmak gerekir. Bu yalnızca VBA döngüleri kullanılarak yapılabilir.

Fonksiyon bağımsız değişkeninin ilk ve son değeri için x1 \u003d 0 ve x2 \u003d 10 alın. Ek olarak, bir sabit girmelisiniz - bağımsız değişkeni değiştirme adımı için değer ve sayaç için başlangıç \u200b\u200bdeğeri.

Tüm örnekler vBA makroları Excel, yukarıda sunulan prosedüre göre oluşturulur. Bu özel durumda, kod şöyle görünür:

Alt program ()

sevişmek \u003d 0.1

Yaparken x1< x2 (цикл будет выполняться пока верно выражение x1 < x2)

y \u003d x1 + x1 ^ 2 + 3 * x1 ^ 3 - Cos (x1)

Hücreler (i, 1). Değer \u003d x1 (x1 değeri hücreye koordinatlarla yazılır (i, 1))

Hücreler (i, 2) .Değer \u003d y (y değeri hücreye koordinatlarla (i, 2) yazılır)

i \u003d i + 1 (sayaç aktif);

x1 \u003d x1 + sevişme (argüman adım boyutuna göre değiştirilir);

Sub.

Bu makroyu Excel'de çalıştırmanın bir sonucu olarak, birincisi x, ikincisi y için değerleri içeren iki sütun elde ederiz.

Daha sonra bunları Excel için standart bir şekilde kullanarak bir grafik çizilir.

Örnek 3

Döngüleri VBA Excel 2010'da diğer sürümlerde olduğu gibi uygulamak için, daha önce verilen Yapım Yaparken For kullanılır.

Bir sütun oluşturacak bir program düşünün. Her hücre, karşılık gelen satırın numarasının karelerini içerecektir. For yapısını kullanmak, sayacı kullanmadan çok kısa sürede yazmanıza izin verecektir.

Öncelikle yukarıda açıklandığı gibi bir makro oluşturmanız gerekir. Ardından, kodun kendisini yazıyoruz. 10 hücre için değerlerle ilgilendiğimizi varsayıyoruz. Kod buna benziyor.

İ \u003d 1 ila 10 Sonraki

Komut, "insan" diline "1'den 10'a kadar tekrarlayın, 1'lik artışlarla" olarak çevrilir.

Görev, kareleri olan bir sütun elde etmekse, örneğin, 1'den 11'e kadar olan tüm tek sayılar, o zaman yazıyoruz:

İ \u003d 1 ila 10 için adım 1 Sonraki.

İşte adım bir adımdır. Bu durumda ikiye eşittir. Varsayılan olarak, döngüde bu kelimenin yokluğu, adımın bir olduğu anlamına gelir.

Elde edilen sonuçlar (i, 1) numaralı hücrelere kaydedilmelidir. Daha sonra, i adım büyüklüğünde artırılarak döngünün her başlangıcında, sıra numarası otomatik olarak büyür. Böylece kod optimize edilecektir.

Genel olarak, kod şöyle görünecektir:

Alt program ()

İ \u003d 1 ila 10 Adım 1 için (i \u003d 1 ila 10 için yazabilirsiniz)

Hücreler (i, 1). Değer \u003d i ^ 2 (yani i karesinin değeri hücreye (i, 1) yazılır)

Sonra (bir anlamda bir sayaç rolünü oynar ve döngünün başka bir başlangıcı anlamına gelir)

Sub.

Bir makroyu kaydetmek ve çalıştırmak da dahil olmak üzere her şey doğru yapılırsa (yukarıdaki talimatlara bakın), onu her çağırdığınızda bir sütun alacaksınız verilen boyut (bu durumda, 10 hücreden oluşur).

Örnek 4

AT günlük yaşam Çoğu zaman, bazı koşullara bağlı olarak belirli bir karar verme ihtiyacı vardır. Onlar olmadan VBA Excel'de yapamazsınız. Algoritma yürütmenin daha ileri yolunun seçildiği ve başlangıçta önceden tanımlanmayan program örnekleri, çoğunlukla If ... Then yapısını kullanır (for zor vakalar) Eğer… O zaman… SON Eğer.

Düşünmek özel durum... Koordinatlı hücrenin (1,1) yazılması için Excel için bir makro oluşturmanız gerektiğini varsayalım:

1 argüman olumlu ise;

Bağımsız değişken boş ise 0;

Bağımsız değişken negatifse -1.

Excel için böyle bir makronun oluşturulması başlıyor standart bir şekilde"sıcak" kullanımıyla alt tuşlar ve F11. Ardından aşağıdaki kod yazılır:

Alt program ()

x \u003d Hücreler (1, 1) Değer (bu komut, x koordinatlarındaki hücre içeriğinin değerini atar (1, 1))

Eğer x\u003e 0 ise Hücreler (1, 1) .Değer \u003d 1

Eğer x \u003d 0 ise Hücreler (1, 1) .Değer \u003d 0

Eğer x<0 Then Cells(1, 1).Value = -1

Sub.

Makroyu çalıştırmak ve Excel'deki bağımsız değişken için istenen değeri almak için kalır.

VBA işlevleri

Fark etmiş olabileceğiniz gibi, Microsoft'un en ünlü elektronik tablo işlemcisiyle programlama o kadar da zor değil. Özellikle VBA işlevlerinin nasıl kullanılacağını öğrenirseniz. Toplamda, özellikle Excel ve Word'de uygulama yazmak için oluşturulan bu programlama dilinin yaklaşık 160 işlevi vardır. Birkaç büyük gruba ayrılabilirler. O:

• Matematiksel fonksiyonlar. Bunları argümana uygulayarak kosinüsün değerini, doğal logaritmayı, tamsayı kısmını vb. Alırlar.
• Finansal fonksiyonlar. Varlıkları ve Excel'deki programlamayı kullanmaları sayesinde, muhasebe ve finansal hesaplamalar için etkili araçlar elde edebilirsiniz.
• Dizi işleme fonksiyonları. Bunlar Array, IsArray; LBound; UBound.
• Dize için VBA Excel işlevleri. Bu oldukça büyük bir grup. Örneğin, bir tamsayı bağımsız değişkenine eşit sayıda boşluk içeren bir dize oluşturmak için Space işlevlerini veya karakterleri ANSI koduna çevirmek için Asc'yi içerir. Hepsi yaygın olarak kullanılır ve Excel'deki satırlarla çalışmanıza izin vererek bu tablolarla çalışmayı büyük ölçüde kolaylaştıran uygulamalar oluşturur.
• Veri türü dönüştürme işlevleri. Örneğin, CVar, İfade bağımsız değişkeninin değerini Variant veri türüne dönüştürerek döndürür.
• Tarihlerle çalışmak için işlevler. Standart olanları önemli ölçüde genişletirler.Örneğin, WeekdayName işlevi haftanın gününün adını (tam veya kısmi) numarasına göre döndürür. Zamanlayıcı daha da kullanışlıdır. Gece yarısından günün belirli bir noktasına kadar geçen saniye sayısını verir.
• Sayısal bir argümanı farklı sayı sistemlerine dönüştürmek için işlevler. Örneğin, Oct sekizlik gösterimde bir sayı verir.
• Biçimlendirme işlevleri. Bunlardan en önemlisi Formattır. Biçim açıklamasında verilen talimatlara göre biçimlendirilmiş bir ifadeye sahip bir Değişken döndürür.
• vb.

Bu işlevlerin özelliklerini ve uygulamalarını incelemek, Excel'in kapsamını önemli ölçüde genişletecektir.

Örnek 5

Daha karmaşık problemleri çözmeye çalışalım. Örneğin:

Gerçek işletme maliyetleri düzeyine ilişkin raporun kağıt bir belgesi verilir. Gereklidir:

• şablon bölümünü Excel elektronik tablo işlemcisini kullanarak geliştirmek;
• İlk verilerin doldurulmasını isteyecek bir VBA programı oluşturun, gerekli hesaplamaları yapın ve bunları şablonun ilgili hücrelerine doldurun.

Çözümlerden birini ele alalım.

Bir şablon oluşturmak

Tüm eylemler standart bir Excel sayfasında gerçekleştirilir. Tüketici şirketin adı, maliyet miktarı, seviyesi ve cirosu ile ilgili verileri girmek için ücretsiz hücreler ayrılmıştır. Raporun düzenlendiği firma (şirket) sayısı sabit olmadığından, sonuçlar için değerlerin girildiği hücreler ve uzmanın tam adı önceden rezerve edilmemiştir. Çalışma sayfasına yeni bir ad verilir. Örneğin, "Օ raporlar".

Değişkenler

Bir şablonun otomatik olarak doldurulması için bir program yazmak için atamaları seçmelisiniz. Değişkenler için kullanılacaklar:

• NN - geçerli tablo satırının numarası;
• TP ve TF - planlanan ve gerçek ciro;
• SF ve SP - gerçek ve planlanan maliyetler;
• IP ve IF - planlanan ve gerçek maliyet düzeyi.

Aynı harflerle, ancak "önek" Itog ile bu sütun için toplamın toplamını gösterelim. Örneğin, ItogTP - "Öngörülen Ciro" başlıklı bir tablo sütununu ifade eder.

VBA programlamayı kullanarak bir sorunu çözme

Tanıtılan gösterimleri kullanarak, sapmalar için formüller elde ederiz. % Cinsinden hesaplamak isterseniz, elimizde (F - P) / P * 100 ve toplamda - (F - P) var.

Bu hesaplamaların sonuçları en iyi doğrudan Excel tablosundaki uygun hücrelere girilebilir.

Olgusal ve tahmini toplamlar için, ItogP \u003d ItogP + P ve ItogF \u003d ItogF + F formülleri kullanılarak elde edilirler.

Sapmalar için, hesaplama yüzde olarak yapılırsa \u003d (ItogF - ItogP) / ItogP * 100 ve toplam değer durumunda - (ItogF - ItogP) kullanın.

Sonuçlar tekrar hemen uygun hücrelere yazılır, bu nedenle onları değişkenlere atamaya gerek yoktur.

Oluşturulan programı başlatmadan önce, çalışma kitabını örneğin "Report1.xls" adı altında kaydetmeniz gerekir.

Başlık bilgileri girildikten sonra "Raporlama tablosu oluştur" düğmesine yalnızca 1 kez basılmalıdır. Diğer kuralları da bilmelisin. Özellikle, tabloya her aktivite türü için değer girildikten sonra her seferinde "Satır Ekle" düğmesine basılmalıdır. Tüm verileri girdikten sonra, "Bitir" düğmesine tıklamanız ve ardından "Excel" penceresine geçmeniz gerekir.

Artık makroları kullanarak Excel görevlerini nasıl çözeceğinizi biliyorsunuz. Vba excel'i kullanma yeteneği (yukarıdaki program örneklerine bakın), şu anda en popüler metin editörü "Word" ortamında çalışmak için de gerekli olabilir. Özellikle yazının en başında gösterildiği gibi yazarak veya kod yazarak menü butonları oluşturabilirsiniz, bu sayede görev başındaki butonlara basarak veya Görünüm sekmesi ve Makrolar simgesi ile metin üzerinde birçok işlem gerçekleştirilebilir.

VBA, Microsoft uygulamaları için standart komut dosyası dili olarak kabul edilir ve artık tüm Office uygulamalarına ve hatta üçüncü taraf uygulamalarına dahil edilmiştir. Bu nedenle, Excel için VBA'da ustalaştıktan sonra, doğrudan diğer Microsoft yazılım ürünleri için makrolar oluşturmaya gidebilirsiniz. Ayrıca, çok çeşitli uygulamaların işlevlerini aynı anda kullanan eksiksiz yazılım ürünleri oluşturabilirsiniz.

Excel'de makrolar nasıl etkinleştirilir

Varsayılan olarak, Excel'de makroları yönetmekten ve gezinmekten sorumlu sekme gizlidir. Etkinleştirmek bu seçenek sekmeye git Dosyagruba Parametreler.Görünen iletişim kutusunda ParametrelerExcel,sekmeye git Şeridi Özelleştirme,sağ birleşik giriş kutusunda, sekmenin karşısına bir işaretçi koyun Geliştirici. Bu adımlar, Excel 2010 ve sonraki sürümler için geçerlidir.

Şeritte yeni bir sekme görünecek Geliştiriciexcel otomasyon kontrolleri ile.

Excel'de makro yazma

Sekmede Geliştiricigrup içinde Kod,düğmesine basın Makro kayıt.Bir iletişim kutusu belirir Makro kayıt,yazılan gelecekteki kod hakkında bazı bilgiler ister. İlk kez bir makro oluşturuyorsanız, düğmeyi tıklamanız yeterlidir. TAMAM.Bundan böyle Excel, veri girişi, biçimlendirme veya grafik oluşturma gibi her kullanıcı eylemini VBA modülüne kaydedecektir. Bir makro kaydını durdurmak için Kaydetmeyi bırak,aynı grupta olan Kod.

Düğmeyi kullanarak makro kaydetmek için alternatif bir seçenek de kullanabilirsiniz. Makro kayıt,excel çalışma kitabının sol alt köşesinde bulunan (durumun sağında) Bitti).

Artık tüm oluşturulan makroların listesini düğmesine tıklayarak görebilirsiniz. Makro,grupta Kod. Görünen iletişim kutusunda, kodlarınıza daha açıklayıcı adlar verebilir veya bunu veya bu makroyu çalıştıracak klavye kısayolları ayarlayabilirsiniz. Bu pencereyi başlatmanın alternatif bir yolu Alt + F8 tuşlarına basmaktır.

Makroları düzenleme

Tebrikler! İlk makronuzu yazdınız. Şimdi Excel'in bizim için hangi kodu oluşturduğunu kontrol etmek mantıklı olacaktır. Oluşturulan kod, VBA'da (Visual Basic for Applications) yazılmıştır. Görmek için açmalısın EditörVB (VBE),alt + F11 tuşlarına basılarak veya GörselTemelsekmede Geliştirici.

Düzenleyicide karışıklığı önlemek için, bir çalışma kitabında, sayfada veya modülde yalnızca bir sekmeyle çalışabilirsiniz. Editör gerçekte böyle görünüyor.

Bu aşamada VBA editörünün çeşitli pencerelerini ve menülerini daha ayrıntılı olarak incelemenizi öneririm. Bu size daha sonra tonlarca zaman kazandıracak.

Kodu görüntülemek için şubeye tıklayın Modüllerprojeler penceresinde ve görünen şubeye çift tıklayın Modül1 ... Editör, resimde gösterildiği gibi kodu içeren bir pencere açacaktır.

Burada, Excel'de çalışırken yazılan oluşturulan kodu düzenleyebilirsiniz. Örneğin, belirli bir sütunu 1'den 10'a kadar değerlerle doldurmanız gerekir. A sütununun ilk üç hücresine 1, 2 ve 3 değerlerini giren ilk üç adıma sahipsiniz. Kalan yedi adımı tamamlamamız gerekiyor.

Yukarıdaki koda bakarsanız, makronun belirli bir şekilde yapılandırıldığını görebilirsiniz. Uygulama ilk olarak Aralığı ("A1") kullanarak imleci hücreye taşır. Seç komutu, ardından içeriğini ActiveCell.FormulaR1C1 \u003d "1" kullanarak düzenler. Böylece kalan adımlar için hücrenin adresini ve o hücreye yazmak istediğiniz değeri değiştirerek bu adımları tekrarlayabiliriz. Örneğin, A4 hücresini 4 olarak ayarlamak için şunu yazarsınız:

Aralık ("A4"). Seçin ActiveCell.FormulaR1C1 \u003d "4"

Kalan değerler için aynı adımları tekrarlayın.

Düzenlemeyi bitirdikten sonra kitabı kaydedin. Makroyu F5 tuşuna basarak çalıştırabilir veya Excel çalışma kitabına dönerek sekmeye gidebilirsiniz. Geliştiricigruba Kod -> Makrolarve listeden ilgilendiğiniz makroyu seçin.

Excel'in oluşturduğu kodu dikkatlice incelemek için birkaç dakikanızı ayırın. Yeni başlayan biriyseniz, kodu öğrenmek için birkaç dakika harcamak size daha sonra VBA nesnelerini tanımada harika sonuçlar verecektir. Lütfen bu örneğin sadece bir örnek olduğunu unutmayın. Daha hızlı ve etkili yollar daha sonra bahsedeceğimiz benzer sonuçlara ulaşmak.

Excel makrolarını hızlandırın

Çok uzak çok iyi. Bir makronun yürütülmesini hızlandırmaya yardımcı olabilecek birkaç numaraya bir göz atalım. Yukarıdaki kod parçacığını örnek olarak düşünün. Modern bilgisayarlar söz konusu kodu o kadar hızlı çalıştıracak ki, farkına bile varmayacaksınız. Ama ya operasyonu 50.000 kez yapmanız gerekirse? Biraz zaman alacak. Yazdığınız bir makro yüzlerce satır uzunluğundaysa, makronun yürütülmesi sırasında kullanılmayan bazı işlemleri keserek kodunuzun yürütülmesini hızlandırabilirsiniz.

Application.ScreenUpdating Komutunu Kullanma

İlk numara, makro çalışırken ekranı güncellemeyi reddetmektir. Bu, Excel'in kaydetmesine izin verecek işlem gücü bilgisayarı açın ve ekranı yalnızca tüm kod yürütüldükten sonra yeni değerlerle güncelleyin. Bunu yapmak için, makronun başlangıcında ekran yenilemeyi devre dışı bırakmak için bir komut ve makronun sonunda ekran yenilemeyi etkinleştirmek için bir komut ekleyin.

1 2 3 4 5 6 7 8 9 10

Alt Makro1 () Aralık ("A1"). Seçin Aralık ("A2"). Seçin Aralık ("A3"). Seçin Sub

Application.ScreenUpdating komutu, Excel'e ekranda yeniden hesaplanan verileri görüntülemeyi durdurmasını ve kod yürütmenin sonunda hazır değerleri döndürmesini söyler.

Uygulama komutunu kullanma. Hesaplama

İkinci numara, otomatik hesaplamaları kapatmaktır. Açıklamama izin ver. Bir kullanıcı veya işlem bir hücreyi her güncellediğinde, Excel tüm bağımlı hücreleri yeniden hesaplamaya çalışır. Diyelim ki, makronun güncellemeye çalıştığı hücre kalan 10.000 hücreyi etkiliyorsa, Excel kodun yürütülmesi bitmeden hepsini yeniden hesaplamaya çalışacaktır. Buna göre eğer varsa bütün çizgi hücreleri etkileyen yeniden hesaplama, kodun yürütülmesini önemli ölçüde yavaşlatabilir. Bunun olmasını önlemek için Uygulama komutunu yükleyebilirsiniz. Formüllerin yeniden hesaplanmasını şu şekilde değiştirecek olan kodun başındaki hesaplama manuel modve makronun sonunda otomatik değerlendirmeye dönün.

1 2 3 4 5 6 7 8 9 10 11 12

Alt Makro1 () Application.ScreenUpdating \u003d Yanlış Aralık ("A1"). Seçin ActiveCell.FormulaR1C1 \u003d "1" Aralık ("A2"). Seçin ActiveCell.FormulaR1C1 \u003d "2" Aralık ("A3"). Seçin ActiveCell.FormulaR1C1 \u003d "3" Application.ScreenUpdating \u003d Doğru Sub

Makronun sonunda bu seçeneği otomatik moda geri getirmeyi unutmamaya dikkat edin. Aksi takdirde, sekmeye tıklayarak bunu Excel'in kendisinde yapmanız gerekecektir. Formüllergruba Hesaplama ve Seç Hesaplama Seçenekleri -\u003e Otomatik.

Hücre ve aralık seçiminden kaçınmak

Modda otomatik kayıt makrolar, Excel'in çok sık Aralık ("A1") gibi bir hücre seçim komutu kullandığını fark edebilirsiniz. Örneğimizde kullandık bu komut bir hücre seçmek ve değerini değiştirmek için birden çok kez. Bunu sadece hücrenin adresini belirterek ve ona gerekli değeri vererek önleyebilirsiniz (Makro, imlecin hareketini bir hücreden diğerine kaydetti, bu nedenle bu adımları ekledi, ancak gerekli değil). Daha fazla verimli kod böyle görünecek.

1 2 3 4 5 6 7 8 9 10 11

Alt Makro1 () Application.ScreenUpdating \u003d Yanlış Application.Calculation \u003d xlCalculationManual Aralık ("A1") .Değer \u003d 1 Aralık ("A2") .Değer \u003d 2 Aralık ("A3"). Değer \u003d 3 Aralık ("A4") .Değer \u003d 4 Aralık ("A5"). Değer \u003d 5 Application.Calculation \u003d xlCalculationAutomatic Application.ScreenUpdating \u003d Doğru Sub

Bu durumda, hücreye referans verdik ve hiç seçmeden ona istenen değeri verdik. Bu yöntem öncekinden daha hızlıdır.

Excel Makro Örnekleri

Aşağıda, en çok tekrarlanan görevleri otomatikleştirmenize yardımcı olacak birkaç örnek VBA kodu verilmiştir.

Tünaydın!

Bu makaleyi, makrolar gibi çok büyük bir MS Excel bölümüne ayırmak istiyorum ya da daha doğrusu, en baştan başlayalım ve Excel'de nasıl bir makro oluşturulacağına, ne işe yaradığına ve işinizde nasıl kullanılacağına bakalım.

Bildiğiniz gibi kendi deneyimi, bir makro ile çalışırken, çok sayıda "rutin" vardır, yani bir sonuç elde etmek için gereken aynı işlemler ve eylemler gerçekleştirilir, aynı tür tablo veya formların doldurulması, verilerin işlenmesi, ikizlere benzer, haftalık, aylık raporlar ve vb. Ancak makro kullanmak, bu eylemleri otomatik modExcel'in yeteneklerini sonuna kadar kullanmak, bu rutin ve monoton işlemleri Excel'in güçlü omuzlarına atmak. Ayrıca makro kullanmanın nedeni eklenebilir doğru fırsatlarhenüz standart olarak uygulanmayan excel işlevleri (örneğin, çıktı, tek bir sayfada veri toplama, vb.).

Bir makroyu hiç duymadıysanız, en doğru tanım şu olacaktır: bunlar, belirli bir sırayla programlanan ve Visual Basic for Applications (VBA) dilinde programlama ortamında kaydedilen eylemlerdir... Bir makro birden çok kez çalıştırılabilir ve bu, Excel'i ihtiyaç duyduğumuz, sevmediğimiz veya manuel olarak gerçekleştirmek istemediğimiz herhangi bir eylem dizisi gerçekleştirmeye zorlar. Tüm Microsoft Office kompleksi için çok çeşitli programlama dillerine rağmen, VBA standarttır ve ofis paketinin herhangi bir uygulamasında çalışır.

Dolayısıyla, Excel'de makro oluşturmak 2 şekilde mümkündür:

Excel'de bir makro oluşturun bir makro kaydedici kullanarak

Öncelikle, bir makro kaydedicinin ne olduğunu ve bir makronun onunla ne ilgisi olduğunu açıklayalım.

Makro kaydedici Dil kodlarındaki herhangi bir kullanıcı eylemini yorumlayan Excel'de yerleşik küçük bir programdır vBA programlama ve çalışma sırasında alınan komutları program modülüne yazar. Yani makro kaydedici açıkken ihtiyacımız olan günlük raporu oluşturursak, makro kaydedici her şeyi adım adım komutlarına kaydedecek ve sonuç olarak otomatik olarak günlük rapor oluşturacak bir makro oluşturacaktır.

Bu yöntem, VBA dil ortamında çalışma becerisine ve bilgisine sahip olmayanlar için oldukça kullanışlıdır. Ancak bir makronun bu yürütme ve kaydetme kolaylığının avantajları yanında dezavantajları da vardır:

• Makro kaydedici yalnızca dokunabildiğini kaydedebilir; bu, eylemleri yalnızca düğmeler, simgeler, menü komutları ve bu ruhla her şey kullanıldığında kaydedebileceği anlamına gelir; bu tür seçenekler, kendisi için mevcut olmayan seçeneklerdir;
• Kayıt sırasında bir hata yapılması durumunda, bu da kaydedilecektir. Ancak iptal düğmesini kullanabilirsiniz son eylem, sil son komut VBA'da yanlış yazdığınız;
• Makro kaydedicide kayıt yalnızca MS Excel penceresinin sınırları içinde gerçekleştirilir ve programı kapatmanız veya başka bir programı açmanız durumunda, kayıt durdurulacak ve çalışmayı durduracaktır.

Makro kaydediciyi kayıt için etkinleştirmek için aşağıdakileri yapın:

Makro kaydediciyle çalışmanın bir sonraki adımı, makronun daha fazla kaydedilmesi için parametrelerini yapılandırmak olacaktır, bu pencerede yapılabilir. "Makro Kaydetme"nerede:

• makro adı alanı - herhangi bir dilde anladığınız, ancak bir harfle başlamanız gereken ve noktalama işaretleri ve boşluklar içermeyen bir isim yazabilirsiniz;
• klavye kısayol alanı - gelecekte sizin tarafınızdan kullanılacaktır. hızlı başlangıç makronuz. Yeni bir tane kaydetmeniz gerektiğinde, bu seçenek menüde mevcut olacaktır. "Hizmet" - "Makro" - "Makrolar" - "Çalıştır" veya sekmede "Geliştirici" düğmeye basmak "Makrolar";
• alanı "Kaydet ..." - makro metninin kaydedileceği (ancak gönderilmeyeceği) yeri belirleyebilirsiniz ve bunlar 3 seçenektir:
  • "Bu kitap" - makro mevcut kitabın modülüne yazılır ve yalnızca şu durumlarda çalıştırılabilir: bu kitap Excel açılacak;
  • "Yeni bir kitap" - makro, Excel'de boş olanın oluşturulmasına bağlı olarak şablona kaydedilecektir yeni bir kitapBu, makronun artık bu bilgisayarda oluşturulacak tüm kitaplarda kullanılabileceği anlamına gelir;
  • "Kişisel Makro Kitap" - özel bir kitaptır excel makrolarıdenen "Personal.xls" ve makroların özel bir depolama kitaplığı olarak kullanılır. Başlangıçta, "Personal.xls" kitabındaki makrolar belleğe yüklenir ve herhangi bir kitapta herhangi bir zamanda çalıştırılabilir.
• açıklama alanı - burada makronun neyi ve nasıl yapması gerektiğini, neden yaratıldığını ve hangi işlevlere sahip olduğunu açıklayabilirsiniz, bu tamamen bilgilendirici bir alandır ve hafıza için denir.

Makronuzu çalıştırıp kaydettikten sonra, gerekli tüm işlemleri tamamladıktan sonra, komutla kaydı durdurabilirsiniz. Kaydetmeyi bırak ve makronuz makro kaydedici kullanılarak oluşturulacaktır.

Excel'de bir makro oluşturun Visual Basic Düzenleyicisi'nde.

Bu yöntemde, yukarıda söylediğim gibi, Excel'in herhangi bir sürümünde yerleşik olan VBA program düzenleyicisi aracılığıyla Excel'de nasıl bir makro oluşturulacağına bakacağız.

VBA program düzenleyicisi, Excel programınızın sürümüne bağlı olarak farklı şekillerde başlar:

• excel 2003 ve sonraki sürümlerinde, menüde ihtiyacımız var "Hizmet", Öğeyi seçin "Makro" ve bas "Visual Basic Düzenleyicisi";
• excel 2007 ve daha yeni sürümlerde, sekmeye ihtiyacımız var "Geliştirici" butona bas "Visual Basic Düzenleyicisi"... Bu sekmeyi bulamamanız durumunda, menü öğesini seçerek etkinleştirmeniz gerekir. "Dosya" - "Seçenekler" - "Şeridi Özelleştir" ve iletişim kutusunda sekmeyi etkinleştirin "Geliştirici".

Açılan pencerede, VBA editörünün arayüzünü düşünebilirsiniz, buradaki her şey İngilizce olarak yazılmıştır ve yerelleştirici yoktur, aramak zorunda değilsiniz, sadece kabul et ve çalış, özellikle de ilk başta sadece anlaşılmaz olduğu için ve sonra her şey tanıdık gelecektir.

Peki, VBA düzenleyicisinde nasıl çalışılır, nerede saklanır ve Excel'de bir makro nasıl oluşturulur. Bu nitelikteki sorular, editörü görür görmez hemen ortaya çıkar ve şimdi hepsini ele alacağız.

Tüm makrolarımız sözde saklanacak yazılım modülleri... Excel'in herhangi bir sürümünün herhangi bir kitabında, herhangi bir yazılım modülleri içlerinde herhangi bir miktar ve yerde yarattığımız tüm makrolar. Bir modül, ihtiyaç duyduğunuz veya oluşturduğunuz herhangi bir sayıda makro içerebilir. Modüller pencerede mevcuttur "Proje Gezgini" ve makro düzenleyicinin sol üst köşesinde bulunur (bunu tuş kombinasyonu ile de çağırabilirsiniz) CTRL + R).

VBA düzenleyicisindeki program modülleri çeşitli biçimlerde bulunur ve farklı seçenekler ve durumlar:

Aslında, standart bir modülde çalışırken makronun kendisi şöyle görünür:

Çalışan bir makro örneğini ele alalım:

• Tüm makrolar mutlaka bir işleçle başlayacaktır Altardından makronuzun adı ve parantez içinde argüman listesi. Bağımsız değişkenlerin eksik olduğu durumlarda parantezler boş bırakılmalıdır;
• Başarısız olmadan, tüm makrolar operatörle biter Sub;
• Operatörler arasındaki veriler Alt ve Sub, makro çalıştırıldığında çalışacak makronun gövdesidir. Örnekte, makro kontrol eder ve veri girerken, veri tabanı listesinde bulur ve kriterler tarafından belirtilen değeri görüntüler.

Gördüğünüz gibi, genel olarak programlama veya özelde VBA konusunda deneyiminiz yoksa ikinci yöntemi kullanmak ve anlamak daha zordur. Makronun otomatik modda çalışmaya başlaması için hangi komutların ve nasıl girildiğini, hangi argümanları kullandığını anlamak ve anlamak çok zordur. Ancak, eski bilgelerin dediği gibi, yol yürüyen kişi tarafından yönetilecek ve bu nedenle pes etmemelisiniz, Lenin'in büyükbabasının emirlerine uymalısınız ...

Araç çubuğunda makroları çalıştırmak için bir düğme oluşturma

Daha önce de söylediğim gibi, bir makro prosedürü çağırabilirsiniz sıcak kombinasyon ancak hangi kombinasyonun kime atandığını hatırlamak çok sıkıcıdır, bu nedenle en iyisi makroyu çalıştırmak için bir düğme oluşturmaktır. Muhtemelen birkaç tipte düğmeler oluşturun, yani:

Bu yöntem, MS Excel'in herhangi bir sürümü için kullanılabilir ve düğmeyi doğrudan çalışma sayfamıza bir grafik nesnesi olarak koyacağımız gerçeğinden oluşur. Bunu yapmak için şunlara ihtiyacınız var:

• MS Excel 2003 ve daha eski sürümlerde menüye git "Görünüm", Seç "Araç Çubuğu" ve düğmeye basın "Formlar".
• MS Excel 2007 ve daha yeni sürümlerde sekmeye ihtiyacın var "Geliştirici" açılır menüyü aç "Yapıştırmak" ve bir nesne seçin "Buton".

Tüm bunlardan sonra, sol fare düğmesini basılı tutarken kağıdınıza bir düğme çizmelisiniz. Çizim işleminin bitiminden sonra, butonunuza tıkladığınızda çalıştırılması gereken makroyu seçmeniz gereken bir pencere otomatik olarak açılacaktır.

VBA'da özel işlevler nasıl oluşturulur

Temel olarak, sözde özel işlevler oluşturmak, standart bir program modülünde normal bir makro oluşturmaktan çok farklı değildir. Bu kavramlar arasındaki fark, makronun, kitabın veya sayfanın nesneleriyle (bunlar formüller, hücreler vb.) Doğasında bulunan eylemleri gerçekleştirecek olmasıdır, ancak kullanıcı tanımlı işlev yalnızca bizden aldığı değerlerle çalışır ve argümanlardır. hesaplamalar için ilk veriler.

Örneğin, oluşturmak için özel işlev katma değer vergisi, yani KDV, VBA düzenleyicimizi açmalı ve yeni modülmenüden seçin "Ekle" paragraf "Modül" ve fonksiyonumuzun metnini buraya girin: Bir işlev ile makro arasındaki temel farkın başlık olduğu unutulmamalıdır. Fonksiyon değiştirildi Alt ve doldurulmuş bir argüman listesi var, bizim örneğimizde Summa... Kodumuz girildikten sonra, standart pencere Menüde bulunan işlev sihirbazları "Formüller", öğe "İşlev ekle".
Ve bir kategori seçin Kullanıcı tanımlı yazılı fonksiyonumuzun gösterileceği "NDS".
Fonksiyonumuzu seçtikten sonra, imleci, KDV'yi hesapladığımız miktarı içerecek bağımsız değişkenli bir hücreye yerleştirebilirsiniz, her şey normal bir fonksiyonda olduğu gibi gerçekleşir.
Ve hepsi benim için! Bunu gerçekten umuyorum Excel'de bir makro oluşturmayla ilgili bir makale anlaşılır ve sizin için yararlıydı. Kalan yorumlar için minnettar olurum, çünkü bu okunabilirliğin bir göstergesi ve yeni makaleler yazmak için ilham veriyor! Okuduklarınızı arkadaşlarınızla paylaşın ve beğenin!

Bu siteyi şöyle oluşturmaya başladım not defteri uygun şekilde excel yetenekleri... İstediğiniz zaman siteye giderek programın belirli bir özelliği hakkındaki bilgilerinizi yenileyebilir, ayrıca Excel kullanarak yardım isteyen arkadaşlarınıza veya iş arkadaşlarınıza makalenin bağlantısını gönderebilirsiniz. Sitenin izleyici kitlesi oldukça hızlı bir şekilde büyüdü ve gördüğüm gibi, kaydedilen bilgiler sadece benim ve diğer birkaç kişi için değil, aynı zamanda tanımadığım birçok kişi için de yararlı ve arama motorları tarafından yüksek puan alıyor. Excel'in yeteneklerine göre kendime güveniyorsam, o zaman bir uzman çok yüksek seviye Kendimi VBA ile isimlendiremiyorum. Bu nedenle yazacağım harika bir yardımcı makale oluşturmak istiyorum kullanışlı makrolarsık kullandığım. Eminim böyle bir VBA özellikleri koleksiyonu sadece benim için faydalı olmayacak.

Sitede zaten VBA ile ilgili birkaç makale var, ancak sık sık kullanılan kod araçlarını tek bir yerde tutmanın yararlı olacağını düşünüyorum.

Basit bir işlevle ekrandaki makro eylemlerin görüntülenmesini kapatın. Bu yalnızca görsel performansı iyileştirmekle kalmayacak, aynı zamanda makronun yürütülmesini de önemli ölçüde hızlandıracaktır.

Sub Makros1 () Application.ScreenUpdating \u003d 0 "Kodunuz Application.ScreenUpdating \u003d 1 End Sub

Makronun sonuna işlevi eklemeyi unutmayın

Makro yürütmeden sonra kopya seçimi nasıl kaldırılır?

Makronuzdaysanız, bunların tümü kopyala ve yapıştır için bir aralık ayırır. Makronun sonunda seçimi kapatmayı unutmayın

Sub Makros1 () "Kodunuz Application.CutCopyMode \u003d 0 End Sub

Yararlı makrolar. Bir aralığın son satırı veya sütunu nasıl bulunur

Bu tasarım, numarayı kolayca bulmanıza yardımcı olacaktır son satır veya doldurulmuş aralıktaki bir sütun. Döngülerde kullanmak özellikle uygundur, kenar boşluğu olan 1000 döngü satırı ayarlamanıza gerek yoktur, Excel, aralığın sonunun bu yapıyı kullandığı yerde kendini bulacaktır:

Sub makros1 () Dim mLastRow As Long Dim nLastCol As Long mLastRow \u003d Hücreler (Rows.Count, 1) .End (xlUp) .Row "Doldurulan aralığın son satır numarasını bulur nLastCol \u003d Cells (1, Columns.Count) .End (xlToLeft ) .Column "End Sub aralığındaki son doldurulan sütunun numarasını bulur

Dahası, popüler Tamsayının büyük tablolar için yeterli olmayabileceği (32.767) bir duruma girmemek için değişkeni hemen Uzun (uzunluk 2.147.483.647) olarak tanımlıyorum.

Ayrıca aramanın ilk sütunda veya ilk satırda olmasına dikkat etmelisiniz, seçin istenen numara, doğru tanım için.

Döngü ve Bir Döngüdeki Durumu Kontrol Etmek İçin

Aralıkların son satır ve sütun numaralarını bulursanız, bunları döngülerde kullanabilirsiniz. Örneğin, ilk sütundan geçmeniz ve boş hücre sayısını saymanız gerekir.

Sub makros1 () Application.ScreenUpdating \u003d 0 Dim mLastRow As Long Dim Kol "Variable quantity Dim i As Long" Döngü değişkeni mLastRow \u003d Hücreler (Rows.Count, 1) .End (xlUp) .Row Kol \u003d 0 i \u003d için 1 mLastRow'a Hücreler (i, 1) .Value \u003d "" Sonra Kol \u003d Kol + 1 Sonraki Varsa Bitir i MsgBox Kol Uygulaması.ScreenUpdating \u003d 1 End Sub

Burada Msgbox da kullanılır, bu özellik ile verileri ayrı bir pencerede görüntüleyebilirsiniz. Örneğim için şöyle görünecek:

Makro Yürütme Süresinin Sayılması

Sub makros1 () TimeStart \u003d Şimdi TimeFinish \u003d Şimdi MsgBox "Saat:" & Biçim (TimeFinish - TimeStart, "h: mm: ss") Son Alt

MsgBox bu çıktıyı üretir:

Daha sonraki optimizasyon için makroların yürütme süresini izlemek çok uygundur.

Bunlar çok sık kullandığım bazı yararlı makrolardır. Bu listeyi kademeli olarak genişleteceğim. Hepsi etkili otomasyon!

Makalemizi sosyal ağlarınızda paylaşın: