VBA'da Excel Hücreleri ve Aralık İşlevlerini Anlama

VBA'da Excel Hücreleri ve Aralık İşlevlerini Anlama

Excel güçlüdür. Çok kullanırsanız, muhtemelen formülleri veya otomatik biçimlendirmeyi kullanan birçok püf noktası biliyorsunuzdur, ancak hücreler ve Menzil VBA'daki işlevleri kullanarak Excel analizlerinizi tamamen yeni bir düzeye yükseltebilirsiniz.





VBA'da Hücreler ve Aralık işlevlerini kullanmanın sorunu, ileri düzeylerde çoğu insanın bu işlevlerin gerçekte nasıl çalıştığını anlamakta güçlük çekmesidir. Bunları kullanmak çok kafa karıştırıcı olabilir. Bunları muhtemelen hiç hayal etmediğiniz şekillerde nasıl kullanabileceğiniz aşağıda açıklanmıştır.



Hücre İşlevi

Hücreler ve Aralık işlevleri size şunu söylemenizi sağlar: VBA betiğiniz çalışma sayfanızda tam olarak nereden almak istediğinizi veya verileri yerleştirmek istediğiniz yeri seçin. İki hücre arasındaki temel fark, başvurdukları şeydir.





hücreler genellikle bir seferde tek bir hücreye başvururken, Menzil aynı anda bir hücre grubuna başvurur. Bu işlevin formatı şudur: Hücreler(satır, sütun) .

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



Worksheets('Sheet1').Cells

Bu, üst satırın solundan üçüncü hücreye başvurur. Hücre C1:

Worksheets('Sheet1').Cells(3)

Aşağıdaki kod, D15 hücresine başvurur:



Worksheets('Sheet1').Cells(15,4)

İsterseniz, 'Hücreler(15,'D')' ile D15 hücresine de başvurabilirsiniz --- sütun harfini kullanmanıza izin verilir.

Sütun ve hücre için bir sayı kullanarak bir hücreye başvuruda bulunabilme konusunda çok fazla esneklik vardır, özellikle döngü çok sayıda hücre (ve bunlar üzerinde hesaplamalar yapın) çok hızlı bir şekilde. Buna aşağıda daha ayrıntılı olarak geleceğiz.



Aralık İşlevi

Aralık 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. Hücre referansları sayı olmadığı için (içine Hücreler işlevini gömmediğiniz sürece) bir Aralık işlevi arasında döngü yapmak istemeyeceksiniz.

Bu işlevin formatı şudur: Aralık(Hücre #1,Hücre #2 . Her hücre bir harf-sayı ile belirtilebilir.

Birkaç örneğe bakalım.

okul wifi nasıl geçilir

Burada, aralık işlevi A5 hücresine başvuruyor:

Worksheets('Sheet1').Range('A5')

Burada, aralık işlevi A1 ile E20 arasındaki tüm hücrelere başvuruyor:

Worksheets('Sheet1').Range('A1:E20')

Yukarıda belirtildiği gibi, sayı-harf hücre atamalarını kullanmak zorunda değilsiniz. Sayfada bir aralığı tanımlamak için bir Aralık işlevinin içindeki iki Hücre işlevini şu şekilde kullanabilirsiniz:


With Worksheets('Sheet1')
.Range(.Cells(1, 1), _
.Cells(20, 5))
End With

Yukarıdaki kod, Range('A1:E20') işlevinin yaptığıyla aynı aralığa başvurur. Bunu kullanmanın değeri, döngüleri kullanarak aralıklarla dinamik olarak çalışan kod yazmanıza izin vermesidir.

Artık Hücreler ve Aralık işlevlerini nasıl biçimlendireceğinizi anladığınıza göre, bu işlevleri VBA kodunuzda nasıl yaratıcı bir şekilde kullanabileceğinize geçelim.

Hücre Fonksiyonu ile Veri İşleme

Hücreler işlevi, en çok, birden çok hücre aralığında gerçekleştirmek istediğiniz karmaşık bir formülünüz olduğunda kullanışlıdır. Bu aralıklar, birden çok sayfada da bulunabilir.

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

Sende olmalı Sayfa1 bu, satış sayılarını ve satış hacmini takip eder.

Açık Sayfa2 şirketinizin müşterilerinden son 30 gün içindeki geri bildirim puanlarını takip ettiğiniz yerdir.

İki sayfadaki değerleri kullanarak ilk sayfadaki bonusu hesaplamak istiyorsanız, bunu yapmanın birçok yolu vardır. İki sayfadaki verileri kullanarak hesaplamayı yapan ilk hücreye bir formül yazıp aşağı sürükleyebilirsiniz. Bu işe yarayacak.

Buna bir alternatif, sayfayı her açtığınızda çalıştırmak için tetiklediğiniz veya sayfadaki bir komut düğmesiyle tetiklenen VBA komut dosyası oluşturmaktır, böylece ne zaman hesaplayacağını kontrol edebilirsiniz. Yine de tüm satış verilerini harici bir dosyadan çekmek için bir VBA komut dosyası kullanabilirsiniz.

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

Eylemde Hücreler İşlevi

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

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

kullanabilirsiniz Sokmak bir komut düğmesi eklemek için menü veya sadece Kodu Görüntüle kodlamaya başlamak için.

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

Private Sub Workbook_Open()
End Sub

Kod pencereniz şöyle görünecek.

Artık hesaplamayı halletmek için kodu yazmaya hazırsınız. Tek bir döngü kullanarak 11 çalışanın tamamında adım adım ilerleyebilir ve Hücreler işleviyle hesaplama için gereken üç değişkeni çekebilirsiniz.

Hücreler işlevinin, her bir hücreyi tanımlamak için parametreler olarak satır ve sütuna sahip olduğunu unutmayın. Satırı 'x' yapacağız, her sütunun verilerini istemek için bir sayı kullanacağız. Satır sayısı çalışan sayısıdır, dolayısıyla bu 1 ile 11 arasında olacaktır. Sütun tanımlayıcısı Satış Sayısı için 2, Satış Hacmi için 3 ve Geri Bildirim Puanı için Sayfa 2'den 2 olacaktır.

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

  • (Satış Sayısı/50) x 0,4
  • (Satış Hacmi/50.000) x 0,5
  • (Geri Bildirim Puanı/10) x 0.1

Bu basit yaklaşım, satış çalışanlarına ağırlıklı bir ikramiye verir. 50'lik bir sayı, 50.000$'lık hacim ve 10'luk bir puan için ay için maksimum bonusun tamamını alırlar. Ancak herhangi bir faktörde mükemmelin altındaki herhangi bir şey bonusu azaltır. İdealden daha iyi olan her şey bonusu artırır.

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

Private Sub Workbook_Open()
For x = 2 To 12
Worksheets('Sheet1').Cells(x, 4) = (Worksheets('Sheet1').Cells(x, 2).Value / 50) * 0.4 _
+ (Worksheets('Sheet1').Cells(x, 3).Value / 50000) * 0.5 _
+ (Worksheets('Sheet2').Cells(x, 2).Value / 10) * 0.1 _
Next x
End Sub

Bu betiğin çıktısı böyle görünecek.

tiktokta kaç takipçi canlı yayına geçecek

Bonus sütununun yüzde yerine gerçek dolar bonusunu göstermesini istiyorsanız, bunu maksimum bonus miktarıyla çarpabilirsiniz. Daha da iyisi, bu miktarı başka bir sayfadaki bir hücreye koyun ve kodunuzda referans alın. Bu, kodunuzu düzenlemek zorunda kalmadan daha sonra değeri değiştirmeyi kolaylaştırır.

Hücreler işlevinin güzelliği, veri çekmek için oldukça yaratıcı bir mantık oluşturabilmenizdir. birçok hücre birçok farklı sayfada ve bazılarını gerçekleştirin oldukça karmaşık hesaplamalar onlarla.

Hücreler işlevini kullanarak hücreler üzerinde her türlü eylemi gerçekleştirebilirsiniz --- hücreleri temizlemek, yazı tipi biçimlendirmesini değiştirmek ve çok daha fazlası gibi.

Daha fazla yapabileceğiniz her şeyi keşfetmek için Microsoft MSDN sayfası Hücreler nesnesi için.

Aralık İşlevli Hücreleri Biçimlendirme

Bir seferde birçok hücre arasında döngü yapmak için Hücreler işlevi mükemmeldir. Ancak, tüm hücre aralığına aynı anda bir şey uygulamak istiyorsanız, Aralık işlevi çok daha verimlidir.

Bunun için bir kullanım durumu, belirli koşullar karşılanırsa, komut dosyası kullanarak bir dizi hücreyi biçimlendirmek olabilir.

Örneğin, tüm satış çalışanlarındaki tüm satış hacminin toplamı toplamda 400.000 ABD Dolarını aşarsa, ekibin fazladan bir ekip bonusu kazandığını belirtmek için bonus sütunundaki tüm hücreleri yeşil renkle vurgulamak istediğinizi varsayalım.

Bunu nasıl yapabileceğinize bir göz atalım bir IF ifadesi .

Private Sub Workbook_Open()
If Worksheets('Sheet1').Cells(13, 3).Value > 400000 Then
ActiveSheet.Range('D2:D12').Interior.ColorIndex = 4
End If
End Sub

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

Bu, Aralık işlevini kullanarak hücre grupları üzerinde gerçekleştirebileceğiniz birçok eylemin basit bir örneğidir. Yapabileceğiniz diğer şeyler şunlardır:

  • Grubun çevresine bir anahat uygulayın
  • Bir hücre aralığı içindeki metnin yazımını kontrol edin
  • Hücreleri temizleme, kopyalama veya kesme
  • 'Bul' yöntemiyle bir aralıkta arama yapın
  • Daha fazla

mutlaka okuyun Microsoft MSDN sayfası Range nesnesinin tüm olasılıkları görmesi için.

Excel'i Bir Sonraki Seviyeye Taşıyın

Artık Hücreler ve Aralık işlevleri arasındaki farkları anladığınıza göre, VBA komut dosyanızı bir sonraki düzeye taşımanın zamanı geldi. Dann'in Excel'de Sayma ve Ekleme işlevlerini kullanma hakkındaki makalesi, tüm veri kümelerinizde çok hızlı bir şekilde değer biriktirebilen daha da gelişmiş komut dosyaları oluşturmanıza olanak tanır.

Excel'de VBA kullanmaya yeni başlıyorsanız, harika bir Excel VBA'ya giriş kılavuzu senin için de.

Paylaş Paylaş Cıvıldamak E-posta Canon vs. Nikon: Hangi Fotoğraf Makinesi Markası Daha İyi?

Canon ve Nikon, kamera endüstrisindeki en büyük iki isim. Ancak hangi marka daha iyi kamera ve lens yelpazesi sunuyor?

Sonrakini Oku İlgili konular
  • üretkenlik
  • Programlama
  • Visual Basic Programlama
  • Microsoft Excel
Yazar hakkında Ryan Dube(942 Makale Yayınlandı)

Ryan, Elektrik Mühendisliği alanında lisans derecesine sahiptir. 13 yıl otomasyon mühendisliğinde, 5 yıl BT'de çalıştı ve şimdi bir Uygulama Mühendisi. MakeUseOf'un eski Genel Yayın Yönetmeni olarak, Veri Görselleştirme üzerine ulusal konferanslarda konuştu ve ulusal TV ve radyoda yer aldı.

Ryan Dube'dan Daha Fazla

Haber bültenimize abone ol

Teknik ipuçları, incelemeler, ücretsiz e-kitaplar ve özel fırsatlar için bültenimize katılın!

Abone olmak için buraya tıklayın