1. Ana Sayfa
  2. Microsoft Office
  3. Excel’de Kullanıcı Tanımlı Fonksiyon Oluşturmak

Excel’de Kullanıcı Tanımlı Fonksiyon Oluşturmak

Makale Özet: Excel’de yer alan fonksiyon kullanıcının bütün ihtiyaçlarını karşılayamayabilir. Bu makalede Excel’de var olmayan bir fonksiyonun VBA ortamında oluşturulmasından ve bütün çalışma kitaplarında kullanılabilmesi için gerçekleştirilmesi gereken işlemler anlatılmıştır. Kullanıcı Tanımlı Fonksiyon (User Defined Function) oluşturabilmek için orta seviyede VBA(Visual Basic Application) bilgisi gereklidir.

Makale İçeriği:

1. Fonksiyon nedir?

Fonksiyon, program içerisinde parametrelerle çağırılan, parametrelerle bir takım işlemler yapan ve sonunda da bir değer döndüren komutlar dizisidir. Microsoft Excel’de fonksiyonlar, ‘Bağımsız değişken olarak adlandırılan özel değerleri, belirli bir sırada ve yapıda alarak bir takım işlemler gerçekleştiren ve sonucunda da bir değer döndüren önceden tanımlanmış formüller’ olarak adlandırılır.

Fonksiyonlar önceden tanımlanmış oldukları için, özellikleri değiştirilemez. Farklı Excel sayfalarına ya da formlarına fonksiyonlar içerisinden atama yapılamaz. Sadece çağırdıkları yere değer döndürürler. Excel’de Formül sekmesindeki Fonksiyon Kütüphanesi’ alanında yer alan bütün formüller, yerleşik fonksiyonlardır.

Bilindiği gibi Excel’de yerleşik olarak gelen fonksiyonlar ile hemen hemen bütün ihtiyaçlar karşılanabilir. Ancak bazı durumlarda, istenen özel işlemlerde bu fonksiyonlar yeterli olmaz ve ayrıca, kullanıcının ihtiyaçlarını karşılayabilecek yeni fonksiyonlar oluşturulması gerekir. Yeni fonksiyonlar ise, Excel’de VBA(Visual Basic Application) ekranında, kodlarla yapılabilir.

2. UDF gerektiren durumlar

Excel VBA’in en büyük özelliklerinden biri, kullanıcının kendi fonksiyonlarını oluşturabilmesidir. Kullanıcı tanımlı Fonksiyonlar (UDF) karmaşık fonksiyonlarla elde edilen sonuçları, daha basit kodlar kullanarak elde edilmesini sağlar.

Veri tabloları üzerinde çalışılırken, elde etmek istenilen sonuca ulaşabilmek için, birden fazla fonksiyonu iç içe kullanmak ya da farklı sütunlarda adım adım işlemleri gerçekleştirmek gerekebilir. Bu hesaplamalar belirli aralıklarla düzenli bir şekilde yapılıyorsa; zaman hatta dikkat kaybına sebep olabilir. Örneğin bir KDV ya da Iskonto hesaplama bir kaç adımda gerçekleştirilebilir.

Bazen tablolar üzerinde analiz yapılırken Excel’deki fonksiyonların da yapamayacağı işlemleri gerçekleştirmek zorunda kalınabilir. Örneğin, seçili alandaki bir sayının yazıya çevrilmesi, hücrenin rengine göre toplama ya da saydırma gibi işlemler Excel’deki fonksiyonlarla çözüme ulaşmaz.

Bu ve bunun gibi durumlarda kullanıcının kendi fonksiyonlarını oluşturması gerekebilir.

3. Fonksiyon oluşturmak

Bu bölümde oluşturulacak iki fonksiyonun adı ‘RengeGoreTopla’ ve ‘RengeGoreSay’. Seçili bir alanda belirtilen renkteki hücrelerin değerlerinin toplamını ve sayısını verecektir. İşlem için öncelikle RengeGore adını verilen boş bir Excel sayfasında Alt+F11 tuşu ile VBA ekranı açılır. Ekranın sol tarafındaki Project ekranından Excel çalışma kitabına sağ click yapılarak yeni bir modül eklenir.


Şekil1: Yeni bir VBA Modülü eklemek

Buraya yazılacak iki fonksiyonun kodu aşağıdaki gibidir.

Function RengeGoreTopla(aralik As Range, hucre As Range)

Application.Volatile

sayi = aralik.Count

Dim toplam As Double

toplam = 0

For i = 1 To sayi

If aralik(i).Interior.ColorIndex = hucre.Interior.ColorIndex Then

toplam = toplam + aralik(i).Value

End If

Next i

RengeGoreTopla = toplam

End Function

Function RengeGoreSay(aralik As Range, hucre As Range)

Application.Volatile

sayi = aralik.Count

Dim sayisi As Integer

sayisi = 0

For i = 1 To sayi

If aralik(i).Interior.ColorIndex = hucre.Interior.ColorIndex Then

sayisi = sayisi + 1

End If

Next i

RengeGoreSay = sayisi

End Function

4. Add-in olarak kaydetme 

Fonksiyonların yazılımı gerçekleştirildikten sonra, VBA penceresi kapatılır. Tüm çalışma kitaplarında bu fonksiyonlardan yararlanabilmek için, dosyanın ‘.xlam’ olarak kaydedilmesi gerekir. Bunun için, File-> Save As dedikten sonra dosyanın kaydedilme türü olarak Excel Add-In(*.xlam) seçilir.


Şekil2: Dosyayı Add-In olarak kaydetmek

Daha sonra pencere otomatik olarak “C:\…..\AppData\Roaming\Microsoft\AddIns klasörüne yönlenir ve dosyanın buraya kaydedilmesini ister. Tamam dedikten sonra artık bu iki fonksiyon bir eklenti dosyası olarak ilgili yere kaydedilmiş olur. Çalışma kitabı kapatılır.

5. Add-In’leri Aktifleştirmek

RengeGoreTopla ve RengeGoreSay gibi kullanıcı tanımlı fonksiyonları oluşturup Eklenti (Add-In) olarak kaydettikten sonra çağırabilmek için, Excel çalışma kitabı tekrar açılır ve File-> Options-> Add-Ins seçeklerinden sonra pencerenin alt tarafındaki Go butonuna basılır.


Şekil3 Add-In’leri aktifleştirme butonu

Karşılaşılan pencere, Excel’e yüklü olan ama kullanabilmek için buradan aktifleştirilmesi gereken eklentilerdir. Oluşturduğumuz Add-In’i buradan işaretledikten sonra Tamam butonuna basılır.


Şekil4: Eklentilerin seçilmesi

6. Fonksiyonları Kullanmak

Add-In olarak kaydedilen fonksiyonlar artık her çalışma kitabında kullanılabilir durumdadır. Tek yapılması gereken “=” ile fonksiyonu hücre içerisine yazmak. Yazmaya başlayınca fonksiyonların geldiği görülecektir.


Şekil5: Fonksiyonun çağırılması

Her iki fonksiyon da çalışabilmek için iki parametre ister. Bunlarda birincisi toplamını almak istediğimiz alan, ikincisi de seçili alandaki toplamı alınmak istenen hücre rengi. Hücre rengi, toplamı alınacak alandan seçilebilir. Hiçbir sıkıntı yaratmaz. Ancak sırası, VBA kodunda yazılan sırada olmalıdır. İlk olarak alan, ikinci olarak da hücre seçilir. Dikkat edilmesi gereken bir başka nokta da iki parametre arasına “;” koymayı unutmamaktır. UDF olduğu için fonksiyon açıklaması şimdilik yok.


Şekil6: Fonksiyonun kullanılması

Enter tuşuna basıldığında fonksiyonun döndürdüğü sonuç gözlemlenebilir. Aynı şekilde oluşturulan diğer fonksiyonu da çalıştırıldığında, belirlenen alanda, belirtilen renge sahip hücrelerin sayısını verir.


Şekil7: Fonksiyoların sonuçları

7. Fonksiyonun güncellenmesi

Eğer, seçili olan alanda herhangi bir hücrenin renginde değişiklik yapılırsa toplama ve saydırma işlemlerinin güncellenebilmesi için hücre içerisine girip fonksiyonu yeniden çalıştırmak gerekirdi. Fakat hücre değerinde değişiklik yapıldığında güncelleme otomatik sağlanmakta. Renk değişikliğinin fonksiyon tarafından yakalanabilmesi için yazılan “Application.Volatile” komutu ile değişiklikler Excel tarafından izlenir bir hale getirildi. Herhangi bir alanda renk değişikliği yapıldığında “F9” tuşu ile fonksiyonlar kendini güncelleyebilir bir duruma gelmiştir.

Bu konuyla ilgili sorularınızı https://forum.mshowto.org linkini kullanarak ulaşacağınız forum sayfamızda sorabilirsiniz.

Referanslar

https://www.mshowto.org

Yorum Yap

Yazar Hakkında

Kubilay Taştutar, 1987 yılında İstanbul’da doğdu. Marmara Üniversitesi Bilgisayar Teknolojileri ve Programlama Bölümünü bitirdikten sonra aynı Üniversitede Bilgisayar ve Kontrol Öğretmenliği Bölümünü tamamladı. Akabinde, Mühendislik Tamamlama Sınavı ile Yalova Üniversitesi Bilgisayar Mühendisliği'nden mezun oldu. Aynı zamanda, aynı üniversitede Bilgisayar Mühendisliği üzerine Yüksek Lisansı devam etmektedir. Marmara Üniversitesini bitirdikten sonra 2014 yılında Bilge Adam Kurumsalda Microsoft Office Biriminde Danışman olarak işe başladı. Bir yıl kadar Danışmanlık görevini devam ettirdikten sonra İş ve Veri Yönetimi Biriminde kıdemli danışman olarak çalışmalarına devam etmektedir. Uzmanlık alanı olan Microsoft Office Uygulamalarında Türkiye'nin önde gelen birçok firmasına eğitim ve danışmanlık hizmeti vermiştir.

Yorum Yap

Yorumlar (5)

  1. 2 sene önce

    çok faydalı ve anlaşılır bir paylaşım, çok teşekkürler, harika bir çalışma yaptım sayenizde hocam teşekkürler.

  2. 10 ay önce

    Kubilay bey merhabalar,
    bilgiler için çok teşekkür ederim. benim fonksiyonlar ile şöyle bir sorum olacak. excelin kendi orjinal fonksiyonlarını kullanırken işlev ipuçları veriyor, mesela “eğer” fonksiyonunu yazdığımda önce koşul yazmam gerektiğini yazıyor. ben buna benzer ipuçlarını nasıl fonksiyonuma tanımlayabilirim? fonksiyonumu kullanacak olan kişi önce hangi veriyi veya alanı seçeceğini bilsin. Çok teşekkürler şimdiden. Kolay gelsin.

    • Sarp bey merhaba,
      Kullanıcı tanımlı fonksiyonların parametrelerinin açıklamaları, Excel’de fonksiyonu hücre içinde yazarken çıkan rehber şeklinde ne yazık ki oluşturulamıyor.
      Sadece, fonksiyon penceresini açarsanız görebilirsiniz.
      örnek olması açısından EXCEL– RANKIF Fonksiyonu Kullanımı makalemi inceleyebilirsiniz.

      Eğer, ilgili makaledeki gibi bir gösterim yeterli olur derseniz, ilgili kodları paylaşabilirim.

      iyi çalışmalar.

  3. 10 ay önce

    Kubilay bey merhaba,
    çözemediğim bir konuda destek olabilirseniz sevinirim. Bir matris veya tabloda yatay başlıklarda belirli bir koşulu sağlayan belirli bir sütün ile düşey eksende belirli bir koşulu sağlayan belirli satırın kesişimi olan hücre değerini getiren bir formül varmıdır?

    desteğiniz için şimdiden teşekkür ederim.

    • Merhaba Ömer bey, aradığınız fonksiyon ikilisi Index-Match (Indis-Kaçıncı)
      bu ikili ile, satır ve sütunda başlıkları olan bir veri setinde aradığınız kriterlere uygun hücre değerini döndürebilirsiniz.