Çalışma Kitabında Bölgeler Adlandırmak

Çalışma Kitabında Bölgeler Adlandırmak

Amaç

• Bir Excel çalışma kitabının sayfalarında belirlenen bölgeleri adlandırmak.

Açıklamalar

• Bir bölgeye anlamlı bir ad vermek yazacağınız formülleri anlamlı kılar:

  • "=A1*B1" yerine "=Maas*VergiOran",

  • "=TOPLA(B2:B10)" yerine "=TOPLA(Ciro_2010)", …

Formüllerde bölge adı kullanmak bölge adresini hatırlamak gerekliliğini de ortadan kaldırır.

• Bölge adları tanımlamak daha sonra veri grupları üzerinde hesaplama yaptırmayı ve grafikler oluşturmayı kolaylaştıracaktır.

  • Aktardığınız özet verilerin satır/sütun düzenlemeleri değişirse, bölge adreslerine dayalı formül veya grafiklerin yeniden elden geçirilmesi gerekir. Halbuki bölge adlarını kullanan formüllerin veya grafiklerin değiştirilmesi gerekmez.

• Bir çalışma kitabını temsil eden Workbook nesnesinin Names özelliğinden kitapta tanımlı olan adlar koleksiyonuna erişebilirsiniz.

• Workbook nesnesinin Names özelliği aslında Names türünden bir koleksiyon nesnesidir.

• Bu Names özelliğinden eriştiğiniz koleksiyonun Add fonksiyonuyla yeni bir ad tanımı eklersiniz. Bu fonksiyonun hepsi de isteğe bağlı olan argümanlarından ilk ikisi önemlidir:

  • Name argümanıyla tanımlayacağınız bölge adını belirlersiniz.

Bölge adı tek bir sözcük olmak zorundadır; boşluk karakteri ve _ (alt çizgi) haricinde özel karakterler içermeyeceği gibi, rakamlarla da başlayamaz.

• • RefersTo argümanıyla da ad verceğiniz bölgeyi temsil eden Range türü nesnenin referansını verirsiniz.

Birbirlerinden belli sayıda satır veya sütunla ayrılan çok sayıda bölgeye adlar verecekseniz, bölge adlarını döngülerde değiştirdiğiniz satır/sütun numaralarıyla oluşturmanın yollarını bulmalısınız.

• Kitapta tanımlı bir bölge adına Names koleksiyonuna bir sıra numarasını vererek erişebilirsiniz:

• Me.Names(1)

• Sonuç ilgilendiğiniz bölge adını temsil eden Name türünden bir nesnedir.

• Names özelliğinde bölge adını sıra numarası gibi kullanarak o ada sahip bölgeye erişebilirsiniz:

• Me.Names("Yil_2010")

• Bir Name nesnesinin iki önemli özelliği vardır; ad eklerken kullandığınız argümanlarla aynı adlara sahiptirler:

  • Name özelliği bölge adını verir.

  • RefersTo özelliği adlandırılmış bölgeyi temsil eden bir Range nesnesidir.

• Bir bölge adını temsil eden Name nesnesini Delete fonksiyonuyla silebilirsiniz.

• Names türü bir koleksiyon nesnesinin Count özelliği koleksiyondaki bölge adı sayısını verir.

Adımlar

• Bölge adları tanımlayacağınız çalışma kitabına ait VBA projesinde (proje organizasyon görünümündeki “BuÇalışmaKitabı” veya “ThisWorkbook” adlı proje) bir altyordam yaratın.

• Bir Worksheet nesnesi tanımlayın:

• Dim sayfa As Worksheet

• Bu nesne aracılığıyla kitabın ilgilendiğiniz sayfasına erişin:

• Set sayfa = Me.Worksheets("Veriler")

• Çalışma kitabının Names koleksiyonuna Add fonksiyonuyla bir bölge adı ekleyin:

• Me.Names.Add Name:="FirmaAdlari", RefersTo:=sayfa.Range("C1:F1")

• İşlemleri bir döngüyle tekrarlayacaksanız, adlandıracağınız bölgeleri sınırlayan satır/sütun değişken tanımlarını yapın:

• Dim satir As Integer, sutun As Integer, _

•     ilksatir As Integer, sonsatir As Integer, …

• Döngüyle adlandırma yapıyorsanız, bölge adınını döngü sayaç değişkeniyle oluşturacak komutlarınız olmalıdır:

• For yilno = 0 To 5

•     yil = 2010 + yilno

•     bolgeadi = "Yil_" & yil

•     …

• Next yilno

• Döngü içinde adlandıracağınız bölgeyi belirlemek için satır/sütun numaraları kullanacaksanız, bölge sınır hücrelerinin referanslarını Cells fonksiyonuyla elde etmelisiniz:

• Set yilbolge = sayfa.Range(sayfa.Cells(ilksatir, 3), sayfa.Cells(sonsatir, 6))

• Döngü içinde tanımladığınız bölge adı ve bölge referansıyla Add fonksiyonunu çağırarak her adımda yeni bir bölge adı ekleyebilirsiniz:

• Me.Names.Add Name:=bolgeadi, RefersTo:=yilbolge

• Adlandırdığınız bölgenin içeriği verilerle bir grafik oluşturacaksanız, adlandırdığınız bölgenin kategori veya seri etiketlerinin olduğu başlık hücrelerini içermesi (özellikle gruplandırılmış sütun grafiklerinde) işinizi kolaylaştırabilir.

Örnek Altyordam

Bu örnek altyordamı birkaç hayali holdingin 2010-2015 yıllarında üç farklı sektörde elde ettikleri ciroları içeren bir çalışma sayfası üzerinde bölgelere ad vermek için planladık.

Yukarıda bu sayfadaki 2010 yılı verilerini içeren kısmı görüyorsunuz. Altyordamla bu kısım içindeki verileri içeren bölgeye yıla göre ad verdirdik. Firma adlarını içeren “C1:F1” bölgesine “FirmaAdlari” ve sektör adlarını içeren “B2:B4” bölgesine de “SektorAdlari” diye ad verdik. Bu adlar sonraki örneklerde firma ve sektör adlarını içeren bölgeleri belirlemek içindi. Her firmanın adı altındaki üç sütun hücresine o firmanın o yılına ait verileri içerdiğini gösteren bir bölge adı, her sektör adını isleyen dört satır hücresine de o sektörde o yıla ait verileri içerdiğini gösteren bir bölge adı verdik.

Kaynak Dosya: “KitapdaBolgelerAdlandirmak.xlsm”

Unutmayın; kitap veya ekran satırına sığmayan komut satırları alta sarkmış olabilir. Siz onları tek satır olarak yazacaksınız!

Sub KitapdaBolgelerAdlandirmak()

    'Bu döngü önceki denemelerde eklenmiş adları silmek için.

    Dim ad As Name

    For Each ad In Me.Names

        ad.Delete

    Next

    Dim sayfa As Worksheet

    Set sayfa = Me.Worksheets("Veriler")

    Dim yilno As Integer, yil As Integer, satir As Integer, yilsatir As Integer, _

        ilksatir As Integer, sonsatir As Integer, sutun As Integer

    Dim yilbolge As Range, sektorbolge As Range, firmabolge As Range, firmasektorbolge As Range

    Dim sektoradi As String, firmaadi As String, bolgeadi As String   

    Me.Names.Add Name:="FirmaAdlari", RefersTo:=sayfa.Range("C1:F1")

    Me.Names.Add Name:="SektorAdlari", RefersTo:=sayfa.Range("B2:B4")   

    For yilno = 0 To 5

        yil = 2010 + yilno

        'Yıla ait verilerin başlangıç ve bitiş satırlarını belirle

        yilsatir = 5 * yilno + 1

        ilksatir = yilsatir + 1

        sonsatir = yilsatir + 3

        'yil değerinin nasıl değiştiği önceden bilinmiyorsa, yilsatir nolu satırın

        'ilk hücresindeki bilgiyi almak daha doğru olur.

        '   yil = sayfa.Cells(yilsatir,1).Value2

        bolgeadi = "Yil_" & yil       

        Set yilbolge = sayfa.Range(sayfa.Cells(yilsatir, 2), sayfa.Cells(sonsatir, 6))

        Me.Names.Add Name:=bolgeadi, RefersTo:=yilbolge       

        'Bu yıl için farklı sektörlere ait bölgeleri adlandır

        For satir = ilksatir To sonsatir

            sektoradi = sayfa.Cells(satir, 2).Value2

            Set sektorbolge = sayfa.Range(sayfa.Cells(satir, 2), sayfa.Cells(satir, 6))           

            'Dikkat! Bölge adını bir hücreden metin alarak oluşturuyorsanız

            'metinde boşluk karakteri olmadığından emin olun!

            bolgeadi = sektoradi & "_" & yil

            Me.Names.Add Name:=bolgeadi, RefersTo:=sektorbolge

        Next satir       

        'Bu yıl için farklı firmalara ait bölgeleri adlandır

        For sutun = 3 To 6

            firmaadi = sayfa.Cells(yilsatir, sutun).Value2

            Set firmabolge = sayfa.Range(sayfa.Cells(yilsatir, sutun), sayfa.Cells(sonsatir, sutun))           

            'Dikkat! Bölge adını bir hücreden metin alarak oluşturuyorsanız

            'metinde boşluk karakteri olmadığından emin olun!

            bolgeadi = firmaadi & "_" & yil

            Me.Names.Add Name:=bolgeadi, RefersTo:=firmabolge

        Next sutun

    Next yilno

End Sub

 Bu altyordamın tanımladığı bölge adlarını kontrol şeritinin “Formüller” sekmesindeki aynı etiketl düğmeyi tıklayarak açacağınız “Ad Yöneticisi” formundan görebilirsiniz: