VBA'da Excel Hücreleri ve Menzil İşlevlerini Anlama

Excel güçlüdür. Çok kullanırsanız, muhtemelen formül veya otomatik biçimlendirme kullanarak birçok şeyi biliyorsunuzdur; Hücreler ve menzil VBA’daki işlevler sayesinde, Excel analizlerinizi yepyeni bir seviyeye yükseltebilirsiniz..

VBA'da Hücreler ve Menzil işlevlerini kullanmadaki problem, ileri seviyelerde, çoğu insanın bu işlevlerin gerçekte nasıl çalıştığını anlamada zorlanmalarıdır. Bunları kullanmak çok kafa karıştırıcı olabilir. Onları muhtemelen hayal edemediğiniz şekillerde nasıl kullanabileceğinizi aşağıda bulabilirsiniz.

Hücreler İşlevi

Hücreler ve Aralık işlevleri, VBA komut dosyanızın söyleyebilmesini sağlar 4 VBA ile Excel Makrolarını Programlarken Kalacağınız Hatalar 4 VBA ile Excel Makrolarını Programlarken Kalacağınız Hatalar Basit kod ve makrolar, Microsoft Excel süper güçlerinin anahtarıdır. Programlamacılar bile, Sanal Temeller Uygulamaları (VBA) ile elektronik tablolarına kolayca etkileyici işlevler ekleyebilirler. Sadece bu başlangıç ​​programlama hatalarından kaçının! çalışma sayfanızda tam olarak nereden edinmek istediğiniz ya da verileri yerleştirdiğiniz. İki hücre arasındaki temel fark, başvuruda bulundukları şeydir..

Hücreler genellikle bir seferde tek bir hücreye atıfta bulunurken menzil Aynı anda bir hücre grubuna atıfta bulunur. Bu işlevin biçimi Hücreler (satır, sütun).

Bu, tüm sayfadaki her hücreye atıfta bulunur. Hücreler işlevinin tek bir hücreye başvurmadığı örnek.

Çalışma Sayfaları ( "Sayfa1"). Cells

Bu, üstteki satırın solundaki üçüncü hücreye gönderme yapar. Celi hücre:

Çalışma ( "Sheet1"). Hücreler (3)

Aşağıdaki kod D15 hücresini gösterir:

Çalışma Sayfaları ( "Sayfa1"). Cells (15,4)

İsterseniz, ayrıca D15 hücresini de referans olarak kullanabilirsiniz. “Hücreler (15,”D”)”-sütun mektubu kullanmanıza izin verilir.

Sütun ve hücre için bir sayı kullanarak, özellikle yapabilen komut dosyalarında bir hücreye başvuruda bulunma konusunda çok fazla esneklik vardır. döngü çok sayıda hücre (ve üzerinde hesaplamalar yapar) çok hızlı bir şekilde. Bunu aşağıda daha ayrıntılı olarak ele alacağız.

Menzil İşlevi

Range işlevi birçok yönden Hücreleri kullanmaktan çok daha güçlüdür, çünkü tek bir hücreye veya belirli bir hücre aralığına aynı anda başvurmanıza olanak tanır. Range işlevinde döngü yapmak istemeyeceksiniz, çünkü hücrelere ilişkin referanslar sayı değildir (Hücreler işlevini içine gömmediğiniz sürece).

Bu işlevin biçimi Aralık (Hücre # 1, Hücre # 2). Her hücre bir harf numarası ile tanımlanabilir.

Birkaç örneğe bakalım.

Burada, range işlevi A5 hücresini gösteriyor:

Çalışma Sayfaları ( "Sayfa1"). Range ( "A5")

Burada range işlevi, A1 ila E20 arasındaki tüm hücrelere gönderme yapıyor:

Çalışma Sayfaları ( "Sayfa1") Menzil. ( "A1: E20")

Yukarıda belirtildiği gibi, sayı harfli hücre atamaları kullanmak zorunda değilsiniz. Sayfadaki bir aralığı tanımlamak için bir Range işlevinin içindeki iki Hücre işlevini kullanabilirsiniz, şunun gibi:

 Çalışma Sayfalarıyla ("Sayfa1"). Aralık (. Hücreler (1, 1), _. Hücreler (20, 5)) Bitiş 

Yukarıdaki kod, Aralık (“A1: E20”) işlevi yok. Bunu kullanmanın değeri, döngü kullanarak aralıklar ile dinamik olarak çalışan kod yazmanıza izin vermesidir. Bilgisayar Programında Nasıl Yapılır Döngüler Bilgisayar Programında Nasıl Çalışır? Bilgisayar Programında Döngü Nasıl Çalışır? Döngüler ilk kontrol türlerinden biridir. programlamada öğrenir. Muhtemelen süre ve döngüler hakkında bir şey biliyorsunuzdur, fakat bir süre-süresi döngü ne başarır? .

Artık Hücreler ve Menzil işlevlerini nasıl formatlayacağınızı anladığınıza göre, VBA kodunuzda bu işlevlerden nasıl yaratıcı bir şekilde faydalanabileceğinizi inceleyelim..

Hücreler İşleviyle Veri İşleme

Hücreler işlevi, çok sayıda hücre aralığında gerçekleştirmek istediğiniz karmaşık bir formüle sahip olduğunuzda en faydalıdır. Bu aralıklar birden fazla sayfada da bulunabilir..

Basit bir örnek alalım. Diyelim ki 11 kişilik bir satış ekibini yönetiyorsunuz ve her ay performanslarına bakmak istiyorsunuz..

Sende olmalı Sayfa1 Bu onların satış sayısını ve satış hacmini izler.

üzerinde Sheet2 Şirketinizin müşterilerinden son 30 gün için aldığı geri bildirimlerini takip ettiğiniz yer.

İlk sayfadaki bonusu iki sayfadaki değerleri kullanarak hesaplamak istiyorsanız, bunu yapmanın birden fazla yolu vardır. Hesaplamayı gerçekleştiren ilk hücreye iki sayfadaki verileri kullanarak bir formül yazabilir ve aşağı sürükleyebilirsiniz. İşe yarayacak.

Buna bir alternatif, ya sayfayı açtığınızda çalışacak şekilde tetikleyebileceğiniz ya da sayfadaki bir komut düğmesiyle tetiklenerek hesaplanırken kontrol edebilmeniz için VBA komut dosyası oluşturmaktır. Tüm satış verilerini harici bir dosyadan almak için bir VBA komut dosyası kullanabilirsiniz..

Öyleyse neden o zamanki aynı komut dosyasındaki bonus sütunu için hesaplamaları tetiklemiyorsunuz??

Eylemdeki Hücreler İşlevi

Excel'de VBA'yı daha önce hiç yazmadıysanız, Geliştirici menü öğesini etkinleştirmeniz gerekir. Bunu yapmak için gidin Dosya > Seçenekler. Tıklamak Şeridi Özelleştir. Son olarak, sol bölmeden Geliştirici'yi seçin., Eklemek sağ bölmede ve onay kutusunun seçili olduğundan emin olun.

Şimdi, tıkladığınızda tamam ana sayfaya geri döndüğünüzde Geliştirici menüsü seçeneğini göreceksiniz..

Kullanabilirsiniz Ekle Bir komut düğmesi eklemek için menüyü veya Kod Görüntüle kodlamaya başlamak.

Bu örnekte, çalışma kitabının her açılışında komut dosyasını çalıştırırız. Bunu yapmak için, sadece tıklayın Kod Görüntüle geliştirici menüsünden, aşağıdaki yeni işlevi kod penceresine yapıştırın..

Özel Alt Çalışma Kitabı_Açık () Alt Sub

Kod pencereniz böyle bir şeye benzeyecek.

Artık hesaplamayı işlemek için kod yazmaya hazırsınız. Tek bir döngü kullanarak, 11 çalışanın hepsine adım atabilirsiniz ve Hücreler işlevi hesaplama için gereken üç değişkeni seçer..

Hücreler işlevinin her bir hücreyi tanımlamak için parametre olarak satır ve sütun içerdiğini unutmayın. Biz yapacağız “x” Satır, her sütunun verilerini istemek için bir sayı kullanın. Satır sayısı çalışan sayısıdır, bu nedenle bu 1 ila 11 arasında olacaktır. Sütun tanımlayıcısı Satış Sayısı için 2, Satış Hacmi için 3 ve Geribildirim Puanı için Sayfa 2'den 2 olacaktır..

Son hesaplama, toplam bonus puanının yüzde 100'ünü eklemek için aşağıdaki yüzdeleri kullanır. İdeal satış sayısının 50, satış hacminin 50.000 ABD doları ve geri bildirim puanının 10 olması.

• (Satış Sayısı / 50) x 0.4
• (Satış Hacmi / 50.000) x 0.5
• (Geribildirim Puanı / 10) x 0.1

Bu basit yaklaşım, satış çalışanlarına ağırlıklı bir bonus kazandırır. 50 sayı, 50.000 dolarlık hacim ve 10'luk bir puan için, bu ayın maksimum bonusunu alıyorlar. Ancak, herhangi bir faktörde mükemmel olan herhangi bir şey, bonusu azaltır. İdeal olandan daha iyi olan her şey bonusu arttırır.

Şimdi, tüm bu mantığın çok basit, kısa bir VBA komut dosyasında nasıl çıkarılabileceğine bakalım:

Özel Alt Çalışma Kitabı_Açık () x = 2 - 12 Çalışma Sayfası İçin ("Sayfa1") Hücreler (x, 4) = (Çalışma Sayfaları ("Sayfa1"). Hücreler (x, 2) .Value / 50) * 0.4 _ + (Çalışma Sayfaları) ("Sayfa1"). Hücreler (x, 3) .Vue / 50000) * 0.5 _ + (Çalışma Sayfaları ("Sayfa2"). Hücreler (x, 2) .Vue / 10) * 0.1 _ Sonraki x End Sub 

Bu betiğin çıktısının nasıl görüneceği.

Bonus sütununda yüzde yerine gerçek dolar bonusunu göstermek isterseniz, bunu maksimum bonus tutarıyla çarpabilirsiniz. Daha da iyisi, bu miktarı başka bir sayfada bir hücreye yerleştirin ve kodunuzda referans alın. Bu, daha sonra kodunuzu düzenlemek zorunda kalmadan değeri değiştirmeyi kolaylaştıracaktır.

Hücreler işlevinin güzelliği, verileri almak için oldukça yaratıcı bir mantık oluşturabilmenizdir. birçok hücre birçok farklı sayfada, ve bunlarla birlikte oldukça karmaşık hesaplamalar yapın..

Hücreler işlevini kullanarak hücreleri temizlemek, yazı tipi biçimlendirmesini değiştirmek ve daha pek çok işlem yapmak için her türlü işlemi gerçekleştirebilirsiniz..

Yapabileceğiniz her şeyi keşfetmek için, Cells nesnesinin Microsoft MSDN sayfasına bakın..

Aralığı Fonksiyonlu Hücreleri Biçimlendirme

Bir seferde bir çok hücrede döngü oluşturmak için Hücreler işlevi mükemmeldir. Fakat aynı anda tüm hücrelere bir şey uygulamak istiyorsanız, Range işlevi çok daha verimlidir..

Bunun için bir kullanım durumu, belirli koşullar yerine getirildiğinde, komut dosyası kullanarak bir hücre aralığını biçimlendirmek olabilir..

Örneğin, tüm satış çalışanlarındaki tüm satış hacminin toplam tutarı toplamda 400.000 ABD Dolarını geçerse, takımın ekstra bir takım bonusu kazandığını belirtmek için bonus sütunundaki tüm hücreleri yeşil renkle vurgulamak istiyorsunuz..

Bir IF ifadesiyle bunu nasıl yapabildiğinize bir göz atalım: Microsoft Excel'de IF İfadeleri Nasıl Kullanılır Microsoft Excel'de IF İfadeleri Nasıl Kullanılır İster deneyimli bir uzman, isterse bir elektronik tablo acemi olsun, bunu kontrol etmek isteyeceksiniz Excel'de IF ifadeleri için rehber. .

Özel Alt Çalışma Kitabı_Açık () Çalışma Sayfaları ("Sayfa1") ise Hücreler (13, 3) .Değer> 400000 Sonra ActiveSheet.Range ("D2: D12"). Interior.ColorIndex = 4 Eğer Son Alt ise Son

Bu çalıştığında, hücre ekip hedefinin üzerindeyse, aralıktaki tüm hücreler yeşil renkte doldurulur..

Bu, Range işlevini kullanarak hücre gruplarında gerçekleştirebileceğiniz birçok eylemin yalnızca bir örneğidir. Yapabileceğiniz diğer şeyler:

• Grubun etrafına anahat uygulayın
• Bir hücre aralığındaki metnin yazım denetimini yapın
• Hücreleri temizle, kopyala veya kes
• İle bir aralıkta arama yapın “bulmak” yöntem
• Daha fazla

Tüm olanakları görmek için Range nesnesinin Microsoft MSDN sayfasını okuduğunuzdan emin olun..

Excel'i Bir Sonraki Seviyeye Taşı

Artık Hücreler ve Menzil işlevleri arasındaki farkları anladığınıza göre, VBA komut dizinizi bir sonraki seviyeye taşımanın zamanı geldi. Dann'ın Excel'de Sayma ve Ekleme işlevlerini kullanma konusundaki makalesi, tüm veri kümelerinizde çok hızlı bir şekilde değer biriktirebilecek daha gelişmiş komut dosyaları oluşturmanıza olanak sağlar.

Excel'de VBA'ya yeni başlıyorsanız, unutmayın Excel VBA için harika bir tanıtım rehberimiz var. Yeni Başlayanlar İçin Excel VBA Programlama Eğitimi Yeni Başlayanlar İçin Excel VBA Programlama Eğitimi VBA, bir Microsoft Office güç aracıdır. Makrolarla görevleri otomatikleştirmek, tetikleyiciler ayarlamak ve çok daha fazlasını yapmak için kullanabilirsiniz. Size basit bir proje ile Excel görsel temel programlamasını tanıtacağız. senin için de.