13.07.2018 tarihinde Formlar-Kontroller sayfası eklenmiştir

25.05.2018 Hosting şirketi dğeiştirmekten kaynaklı bir hata nedeniyle Excelent add-ini indirirken hata alınmaktaydı. Bu hata düzeltilmiştir. İki ayrı download alternatifi sunulmuştur. Kurumunuzun BT politikalarının veya şahsi PC’nizdeki güvenlik ayarlarının izin vermesi durumunda yöntemlerden biriyle kurulum yapabilmelisiniz. Bi sorun olursa bana iletebilirseniz sevinirim.

25.04.2018 tarihinde,VBA konularına Formlar-Temeller sayfası eklenmiştir.

VBAMakroFonksiyonlar5

Kullanıcı Tanımlı Fonksiyonlar(UDF)

Neden?

Bazı anlar olur ki "Ya bunun formülü nasıl yazılıyordu" dersiniz, o formülü daha önce yazmışsınızdır ama o kadar uzun bir formüldür ki tekrar hatırlamak biraz zamana mal olur, hele o parantezler yok mu, "ya bu parantezlerden hangisi fazla" diye düşünür durursunuz. Burada bir alternatif, daha önceden yaptığınız ve bir yerlere(!) kaydettiğiniz çalışmayı bulmak ve formülü kopyalamak, bir diğeri de kendi fonksiyonunuzu yani UDF(User Defined Functions) tanımlamak ve bunu her ihtiyacınız olduğunda çok kolay şekilde kullanmaktır.

UDF'in tek kullanım amacı bu değildir tabiki, bir diğer kulanım amacı ise, Excel'in mevcut fonksiyonlarıyla yapmanın zor olduğu hatta imkansız olduğu şeyleri bunlarla yapabilmektir. Evet, her ne kadar Excel'in çok geniş bir fonksiyon kütüphanesi olsa bile bunlar bazen yetersiz kalabilmektedir. Gerçi her yeni versiyonda bazı eksiklikler tamamlanmaktadır. Şahsen benim yazdığım en az bi düzine fonksiyon artık son durumda(bu sayfayı yazarken son versiyon 2016 idi) boşa çıkmıştır. Bunlara da yeri gedikçe bakacağız.

Giriş

Şimdi UDF konusuna girerken olaylara bakışmızı biraz değiştirmekte, kendimizi Micorosft çalışanıymış gibi düşünmekte fayda var. Kendimize şunu soralım: Yerel fonksiyonları Excel mühendisleri nasıl hazırlamış olabilir?

Mesela SUM fonksiyonuna bakalım:

Bu yukarıdaki gibi bir fonksiyonu biz nasıl hazırlardık? Bunu önce sözlü dile getirelim. Seçili alandaki tüm hücrelerin değerini tek tek topla. Algoritmik hali de şöyledir.

  • İlk hücreden toplamaya başla
  • Sonraki hücreye geç, bir önceki değerle topla
  • Bunu son hücreye kadar devam ettir

O zaman son minvalde kodumuz şöyle olacaktır:

Function Topla(alan As Range) As Double
Dim a As Range
Dim gecici As Double

For Each a In alan
    gecici = gecici + a.Value
Next a

Topla = gecici
End Function

Sonuca bakalım.

Ne yaptığmıza bir bakalım

  • Fonksiyon ismini Function kelimesi ile belirttim, tipini de Double.(Tip belirtmezsem Variant olur)
  • Fonksiyonuma parametre olacak olan "alan"ı veri tipi Range olacak şekilde belirttim.(Normalde yerel SUM fonksiyonu kendisine bir alan değil de adedi belirsiz olan sayısal değerleri kabul eder, biz basit olsun diye alan belirttik. Yerel SUM'ın yaptığı gibi de yapabilirdik, bunu ParamArray kısmında göreceğiz)
  • Sonra geçici bir değişken tanımladım, hücreler içinde dolaşırken ara toplamı hep bu geçici değişkende tuttum.(Geçici değişkenler Function tanımlamalarında sıklıkla kullanılırlar)
  • Sonra bir For Each döngüsü ile tüm hücrelerin arasında dolaşıp toplamı hesapladım
  • En son da fonksiyon ismi olarak belirttiğim ifadeye(Topla) geçicinin değerini atadım

Fonksiyon tanımlamanın jenerik yapısı aşağıdaki gibidir:

Function fonksiyonadı(parametre1 As veritipi,parametre2 As veritipi,...) As DönüşTipi
'Gerekliyse değişken tanımlamaları

'Kod bloğu
'varsa geçicideğer

fonksiyonadı=geçicideğer
'geçici değer yoksa hesabı direk fonksiyon adı üzerinde yaparız
'fonksiyonadı=hesaplama kodları
End Function

Mesela aynı mantıkla COUNT fonksiyonunu düşünün. Bu fonksiyon bildiğiniz gibi sayı içeren hücreleri sayar. Hadi biz de aynı görevi gören bi UDF hazırlayalım. VBA, boş hücreleri de 0 gibi düşündüğü için bunları numerik sayar, o yüzden dolu hücrelere sayı içeriyor mu diye bakacağız.

Function NumerikSay(alan As Range)
Dim a As Range
Dim gecici As Double

For Each a In alan
   If IsNumeric(a) And Not IsEmpty(a) Then gecici = gecici + 1
Next a

NumerikSay = gecici
End Function

Aynı mantıkla COUNTA fonksiyonunu düşünün. Bu fonksiyon bildiğiniz gibi içi dolu hücreleri sayar. Bunun da UDF versiyonunu hazırlayalım.

Function DolularıSay(alan As Range)
Dim a As Range
Dim gecici As Double

For Each a In alan
   If Not IsEmpty(a) Then gecici = gecici + 1
Next a

DolularıSay= gecici
End Function

Performans

Hazırladığınız fonksiyonu binlerce satırdan oluşan listelerde kullanacaksanız mutlaka hem fonskiyonun parametrelerini hem de kodda kullanılacak tüm değişkenleri uygun veri tipinde tanımlayın. Buna rağmen fonksiyonunuz çok komplike ise yeterince hızlı çalışmayabilir. Yerel fonksiyonlarla yapmak çok zor değilse yerel fonksiyonları kullanmanız gerekebilir. Yerel fonksiyonlar sonuçta en temel seviyde çalışırlar. Kendi UDF'lerimiz ise bir seviye üstte çalşır, o yüzden temel seviyeye çevrilmeleri gerekir, en son da makine diline çevrilirler. Bu performans farkını test ettikten sonra görebilir, duruma göre karar verirsiniz.

Yerleşim ve Erişim

Yerleşim

Tekil kullanımlık UDF'ler

Yazdığımız fonksiyonları bazı durumlarda sadece ilgili dosya içinde çalışmasını isteriz, çünkü o dosyaya özgü çözüm sunarlar. Bunları başka bir yerde kullanmayacağımız için genel UDF'leri tutacağımız yerde bulundurmamıza gerek yoktur.

İlgili dosyada UDF yazmak için o dosyaya Modül eklemeli, modül sayfasına yazmalıyız. ThisWorkbook ve Sheet modülleri UDF'ler için uygun değildir.

Dİkkat:Tekil kullanımlık UDF'in kaydolduğu dosyayı xlsm veya xlsb uzantısıyla kaydetmek gerekmektedir.

Genel kullanım UDF'lerinin yerleşimi

Eğer kodlarınızı genele yaygın bir şekilde kullanmak istiyorsanız bunun için bir alternatif Personal.xlsb dosyasıdır ama bu genelde kötü bir alternatiftir. Normal Sub prosedürler için Personal.xlsb güzel bir adrestir ancak Function'lar için aynısını söyleyemeyiz. Zira bunlara ne Insert Function menüsündeki User Defined kategorisinden ulaşmak kolaydır, ne de bir hücreye doğrudan ismini yazarak ulaşabiliriz.

User Defined kategorisinden ulaşmak biraz karışıktır. Diyelimki fonksiyon adı DolularıSay olsun. Bu fonksiyon listede Personal.xlsb!DolularıSay  şeklinde görünür ancak bunun için P harfine gidip önce Personla.xlsb'yi bulmanız gerekmez. UDF'ler, içinde bulundukları dosyadan bağımsız olarak kendi adlarına göre alfabetik sıralıdır. Dolularısay için de D harfiyle başlayan fonksiyonlara gitmeniz gerekir. Aşağıda görüldüğü üzere N harfiyle başlayan NumerikSay fonksiyonu P harfiyle başlayan Personal.xlsb!DolularıSay 'dan sonra geliyor, önce değil.

Bunu seçtiğimizde yukardaki ilk örnekte gördüğümüz gibi Excel içindeki görünümü =Personal.xlsb!DolularıSay(B2:D7) şeklinde olup biraz garip bir görünüme sahiptir. Normal bir dosya içindeki tekil kullanımlık UDF'lerde ise bu sorun yoktur.

Peki soru şu:UDF'imizi hem tekil kullanımlık UDF'teki gibi önünde dosya ismi olmadan kullanmak hem de genele yaygın kullanmak istiyorsak ne yapmalıyız? Cevap:UDF'lerimizi Add-in içinde kaydetmek.

Add-in içinde yerleşim

Boş bir dosya açın, kaydet düğmesine basın, dosya tipini Excel Add-in olarak değiştirin.

Otomatikman adresin yukardaki gibi değişmesi lazım. Sizdeki adres Excel versiyonunuza göre değişebilir. Bende şöyle: C:\Users\Volkan\AppData\Roaming\Microsoft\AddIns\

Bu add-in'i Developer menüsü>Excel Add-in menüsünden aşağıdaki gibi aktive edebilirsiniz. Bir kere aktive olduktan sonra Excel her açıldığında bu dosya da açılacaktır. Dosyanın kendisi görüntülenemez. Sadece VBE ortamında görünür. Yanlız bunlar Personal.xlsb'den biraz farklıdır. Personal.xlsb'yi istersek unhide yaparak görebiliriz ancak add-in dosyaları asla normal worbooklar gibi görüntülenemezler. Aşağıdaki görselde farkettiyseniz Excelle hazır gelen Anlaysis Toolpak ve Solver gibi add-inler de var. Bunların kullanımıyla ilgili detaylara yandaki anamenüde Excel altındaki Data menüsünden ulaşabilirsiniz.(NOT:Developer menüsündeki COM Add-in butonu bu sitenin VSTO konusuyla ilgili olduğu için ona burada değinmeyeceğiz.)

Bundan sonrasında yapılması Gereken bu dosyaya VBE'de modül ekleyip UDF kodlarımızı oraya yazmaktır.

Gördüğünüz gibi artık UDF'in önünde dosya adı yok ve de herhangi bir dosyada kullanabiliyoruz.

 

Erişim

Add-in'lerinizi başka kişilerle de paylaşmaya karar verdiyseniz bunların nerede olduğuna ulaşmak için VBE'de Immediate Window'a şunu yazın: ?Application.UserLibraryPath. Add-inlerinizin aslında istediğiniz yere kaydedebilirsiniz, ancak daha sonra aktive(veya pasifize) ederken  kolaylık olması adına bu adreste olması daha iyidir.

Aktivasyon işlemini yukarda göstermiştik. Bir diğer alternatif de, File> Options>Add-ins menüsündendir.

Son olarak, Add-inler sadece UDF depolamak için kullanılan araçlar değillerdir. Ayrıca menü yaratıp, bu menüye çeşitli düğmeler ve altmenülere ekleyerek Sub prosedürlerinizi yani makrolarınızı çalıştırmak için de bir arayüz sağlarlar. Bu konuya ayrı sayfada değineceğiz.

UDF Açıklamaları ve Fonksiyon Kategorisi

Fonksiyonlara açıklama eklemek özellikle başkalarının kullanımı için faydalı olabilir. Bunun için aşağıdaki gibi bir ayarlama yapılabildiği gibi toplu tanımlama yapma imkanı da varır, ki bunun için tabiki yine VBA kodu kullanırız.

Çıkan kutuya direkt açıklama yazılır.

Toplu işlem için kullancağımız kodda ise fonksiyonun ait olacağı kategoryi de belirtebiliriz. Mesela NumerikSay için İstatistiki kategorisine koyabiliriz, ki yukardaki yöntemde bu yapılamamaktadır.

Mevcut kategoriler aşağıdakiler olup bunların dışında yeni kategoriler de yaratabiliriz.

'0 Kategori yok, All içinde görünür
'1 Financial
'2 Date & Time
'3 Math & Trig
'4 Statistical
'5 Lookup & Reference
'6 Database
'7 Text
'8 Logical
'9 Information
....
'14 User Defined default
'15 Engineering (Analysis Toolpak add-in'i kuruluysa kullanılabilir)

Aşağıdaki kod, NumerikSay fonksiyonunu Sayısal Fonksiyonlar isimli kategori içine atar, bu kategori mevut değilse bunu yaratır.

Sub UDFaçıklaması()
  Application.MacroOptions _
  Macro:="NumerikSay", _
  Description:="Belli bir alandaki sayısal değerlerin toplam adedini verir", _
  Category:="Sayısal Fonksiyonlar"
End Sub

Excel 2010 ile birlikte fonksiyonların argümanlarına da açıklama girme imkanı gelmiştir. MacroOptions metoduna ArgumentDescriptions parametresini ekleyerek bu işi hallederiz.(Normalde VBA ölü bir dildir, yani yeni güncellemeler, özellikler eklenmez ancak ender de olsa böyle küçük iyileştirmeler yapılmakta)

Önemli bir nokta var ki, o da bu girdiğiniz açıklamalar sadece sizde geçerli olur. UDF'erinizi bir add-in haline getirip gönderirseniz ve onlarda da görünmesini isterseniz bu tanım ekleme kodlarını Workbook_Open içine yazmakta fayda var, ki kişinin add-ini yüklenir yüklenmez açıklama kodu da çalışsın. UDF'lerin açıklamaları ve parametrelerin açıklamalarıyla birlikte kodunuz çok uzayacak gibi olursa bunları bir text dosyasından veya Excel dosyasından okutabilirsiniz.(Biraz aşağıda örneği var)

Şimda tek bir fonksiyon için nasıl yapıyoruz ona bakalım.

Sub UDFaçıklaması()

Dim fonkAd As String
Dim fonkTanım As String
Dim fonkKategori As String
Dim argumanAdedi As Integer
Dim argumanlar() As String

argumanAdedi = 1
ReDim argumanlar(1 To argumanAdedi)

fonkAd = "NumerikSay" 
fonkTanım = "Belli bir alandaki sayısal değerlerin toplam adedini verir"
fonkKategori = 4

argumanlar(1) = "Toplanacak sayıların olduğu alanı seçin."

Application.MacroOptions _
Macro:=fonkAd, _
Description:=fonkTanım, _
Category:=fonkKategori , _
ArgumentDescriptions:=argumanlar

End Sub

Herhangi bir hücreye fonksiyonumuzu yazdığımızda aşağıdaki gibi görünür:

Aşağıda da Workbook_Open makrosuna yazılı olan kod var. Bu kod ile, fonksiyon açıklamalarını bir excel dosyasından okuyoruz.

Private Sub Workbook_Open()
    Application.ScreenUpdating = False
    Workbooks.Open "C:\makrolar\kılavuz.xlsx", UpdateLinks:=0 'herkes dosyayı buraya koymalı
    Workbooks("kılavuz.xlsx").Sheets("UDFDesc").Select
    Call macrodesc
    Workbooks("kılavuz.xlsx").Close savechanges:=False
    Application.ScreenUpdating = True
End Sub

'bu da çaprılan macrodesc prosedürü
Sub macrodesc()

Dim fonkAd As String
Dim fonkTanım As String
Dim fonkKategori As String
Dim argumanAdedi As Integer
Dim argumanlar() As String
Dim i As Integer

Range("A2").Select
Do
    fonkAd = ActiveCell.Value2
    fonkTanım = ActiveCell.Offset(0, 1).Value2
    fonkKategori = ActiveCell.Offset(0, 2).Value2
    argumanAdedi = Range(ActiveCell, ActiveCell.End(xlToRight)).Cells.Count - 3
    
    ReDim argumanlar(1 To argumanAdedi)
    
    For i = 1 To argumanAdedi
        argumanlar(i) = ActiveCell.Offset(0, i + 2).Value2
    Next i
        
    Application.MacroOptions _
        Macro:=fonkAd, _
        Description:=fonkTanım, _
        Category:=fonkKategori, _
        ArgumentDescriptions:=argumanlar
Loop Until ActiveCell.Value = ""

End Sub

Diğer Hususlar

ParamArray ile belirsiz sayıda parametre temini

Yukarıda Excelin yerel SUM fonksiyonunu, kaynağı bir alan olacak şekilde tasarlamış ve demiştik ki, aynı yerel SUM gibi sayısı belirsiz olan elemanları içerecek şekilde de yapabiliriz. İşte bunun yolu ParamArray ifadesini kullanmaktır. Önce örneği yapalım sonra açıklayalım.

Function ToplaParamarray(ParamArray sayılar()) As Double
Dim a As Variant
Dim gecici As Double

For Each a In sayılar
gecici = gecici + a
Next a

ToplaParamarray = gecici

End Function

ParamArray ifadesinden sonra bu sayısı beliri olmayan elemanları içerecek bir dizi adını gireriz. Bu Variant tipli bir değişken olmak zorundadır, yani başka bir veritipiyle tanımlayamayız. Zaten mantıklısı da budur. Mesela Toplam örneğinde hem 0,25 gibi double tipindeki sayıları hem de 5 gibi integerları input olarak alabiliriz, böyle bi durumda Varianttan başka çare yoktur.

Diğer kıstlamalar şöyle;

  • Fonksiyonun parametre listesinde başka parametreler de varsa Paramarray sonucu parametre olmalıdır. Ör:ToplamınXinciÜssü şeklindeki fonksiyon şöyle tanımlanırdı. Function ToplamınXinciÜssü(üs As Integer,ParamArray sayılar())
  • Bir fonksiyonda sadce 1 tane paramarray tanımlanabilir
  • Bir fonksiyonda Optional ve Paramarray parametrelerden yanlız biri kullanılabilir.
  • İlgili dizinin tabanı 0'dır. İsterseniz Option Base ile genel dizi tabanını 1 yapmış olun farketmez. ForEach yerine klasik for kulanılacaksa Lbound(dizi) to Ubound(dizi) şeklinde kullanılabilir.

NOT:Bu yukardaki örnekte bir alan seçimi yapılamayacağına dikkat edin; tek tek sayı temin etmek zorundasınız. Excel'in SUM fonksiyonu ise hem alan kabul edebiliyor hem de tek tek sayılar kabul edebiliyor. SUM'ın bunu nası yaptığını düşünün ve her iki versiyonu da kapsayacak bir fonksiyonu yazmayı deneyin.

Optional ile opsiyonel parametre temini

Bazen girdiğimiz parametrelerin sık kullanılan değerini biz baştan gireriz ve son kullanıcıya bunu değiştirme imkanı veririz. Bu, yerel Excel fonksiyonlarında da var olan default(varsayılan) değerlerle aynı şeydir. Mesela VLOOKUP fonksiyonunun son parametresi opsiyonel olup default değeri TRUE(1)'dur, yani girilmezse TRUE(1) algılanır.

İşte biz de bu opsiyonel parametreyi Optional ifadesi ile sağlarız, istersek varsayılan değer de girebiliriz, girmezsek de bunun girilip girilmediğini IsMissing fonksiyonu ile test ederiz. Ancak IsMissing sorgulaması yapabilmek için ilgili parametrenin datatipi Variant olarak girilmelidir. (ParamArrayda datatipi Variant olmak zorundayken Opsiyonellerde ise tavsiyedir, olur da IsMissing ile sorulgarız diye, ama zorunlu değildir). Bu değer Variant değilse ve kullanıcı tarafından girilmezse bunlara default değerleri atanır; String için "", sayısal tipler için 0, boolean için false.

Dikkat edilmesi gereken diğer hususlar şöyledir:

  • Bunlar da ParamArray gibi son parametre olarak girilmelidir
  • ParamArray'de belirttiğimiz gibi aynı anda hem ParamArray hem Optional ifadesi kullnılamaz.
  • Birden fazla Optional ifadesi kullanılabilir

Opsiyonel Örnek 1

Aşağıda varsayılan değerin girilmiş olduğu bir örnek bulunuyor. Bu örnekte bir hücredeki metni kelimelere ayırıyoruz. Normal bir cümlede ayraç boşluk olacağı için ayracı girmeye gerek yok, o yüzden default olarak " " atadım. Ama kullanıcı isterse kelimeleri farklı bir ayraçla da ayırabilir. Mesela - ile ayrılmış kelimeler varsa ayraç olarak - kullanılabilir. Fonksiyonda Split fonksiyonu kullanarak kelimeleri parçalıyor ve bir diziye atıyorum. Kaçıncı parametresi ile de istediğim kelimeyi elde ediyorum.

Function kelimesec(hucre As Range, kaçıncı As Byte, Optional ayrac As String = " ")
    Dim kelimeler As Variant
    kelimeler = Split(hucre.Value2, ayrac)
    kelimesec = kelimeler(kaçıncı - 1)
End Function

Örnek kullanım  aşağıdaki gibidir. B7'deki formül şöyle:

=kelimesec(A7,2) //son parametreyi girmedim

Opsiyonel Örnek 2 

Şimdi üç tane opsiyonel değişkeni olan bir fonksiyonumuz var. Bunlardan birinin varsayılan değeri girilmiş, birinin girilmemiş, biri de Variant olarak tanımlanmış.

Bu örnek bankacılık dünyasından bir örnek olacak. Mevduat/Kredi gibi hacimsel bir büyüklük ile bu hacimden ne kadar kar elde ettiğimizi gösteren spread bilgisini zorunlu olarak giriyoruz. Bu fonksiyonu iki şekilde kullanabiliyoruz. Eğer belirli bir alan seçilmezse şube bazında NFG(Net Faiz Geliri) hesplayacağız, ancak ilgili alanı seçersek o alandaki MT(Müşteri Temsilcisi) adedi başına NFG hesaplanmış olacak. İşte bu alan bilgisini Variant olarak girdim, ki IsMissing ile alanın girilip girilmediğini sorgulayabileyim. İkinci opsiyonel seçenek Kur bilgisi olup, ilgili hacim türünün TL mi yoksa döviz mi olduğuna göre değer alacak. Varsayılan olarak döviz tipinin TL olduğunu düşünerek değeri 1 girdim, ancak kullanıcı isterse farklı bir kur değeri girebilir. Son değişken ise Para birimi olup, varsayılan değer girilmemiştir. Kullanıcı isterse TL, USD gibi değerler girebilir, eğer girmezse Stringlerin varsayılan değeri olan "" atanacak olup herhangi bir para birimi yazmayacaktır.

Son olarak, fonksiyon için dönüş tipi belirtmedim, yani Variant olacak. Zira Birim parametresi girilirse sonuç String, girilmezse double olacak,  yani ikisini de kapsayan  bir tip olmalı, ki bu da Variant oluyor.

Function NFGHesapla(hacim As Double, spread As Double, Optional alan As Variant, Optional Kur As Double = 1, Optional Birim As String)
    Dim adet As Integer
    
    If IsMissing(alan) = True Then
        NFGHesapla = (hacim * spread * Kur / 1200) & Birim
    Else
        NFGHesapla = (hacim * spread * Kur / 1200) / WorksheetFunction.CountA(alan) & Birim
    End If
End Function	

Kullanım şekli aşağıdaki gibidir. G kolonunda şube başına ve para birimsiz versiyonunu görürken, H kolonunda MT başına ve para birimli versiyonunu görüyoruz.

NOT: Bu örnekle, aslında modern programlama dillerinde olan ancak VBA'de olmayan "method overloading" kavramını (tam olarak olmasa da) bir nevi taklit etmiş olduk. Yani bir metodun ismi aynı olup farklı parametre veya dönüş tipi alıyorsa bu işleme method overloading denir. Biz de buna benzer birşey yapmış olduk. Hem alan tipini seçip seçmemeye göre hem de sonuna parabirimi koyup koymamaya göre 4 farklı kullanım şekli sunduk.

Volatile

Yazdığmız fonksiyon, eğer sayfada bir güncelleme olduğunda bundan etkileniyorsa anında güncellenmez. Volatile ile bu güncellemeyi anlık olarak yapmış oluruz. Ancak üstadlar der ki, UDF'inizi öyle bi hazırlayın ki bu fonksiyonu kullanma ihtiyacınız hiç olmasın.

Bu ve şu sayfalarda bu konuyla ilgili çeştli tartışmalar da yapılmış, İngilizceniz varsa bakabilirsiniz.

Özet  tavsiyem: Araştırmalarınızda bu ifadeyi görüseniz ne olduğunu bilin ama bunu kullanmayın. UDF'inizde bunun kulanımına gerek olmayacak şekilde tüm paralemetreleri dahil edin.(Örnek vererek kafanızı da karıştırmak istemiyorum)

Çeşitli örnekler

Süpercombine ile hücreleri birleştirin

Bu fonksiyon, ardışık bir hücre grubunu belirli bir işaret/ayraç ile birleştirir. 2016 ile gelen TEXTJOIN ve CONCAT fonksiyonlarıyla gereksiz hale gelmiştir ancak eski Excel versiyonlarını kullananlar için hala geçerlidir.

Function Süpercombine(Hucre_grubu As Range, isaret As String)
x = ""For Each k In Hucre_grubu
    If Not IsEmpty(k) Then
    x = x & k.Value & isaret
    End If
Next k

Süpercombine= Mid(x, 1, Len(x) - 1)
EnEnd Function

Örnek kullanım aşağıdaki gibidir. Müşteri numaraları ";" işareti ile birleştirilmiştir.


Süperlookup ile kayan lookup işlemi yapın

Sheet2'de şöyle bir listemiz var,

Sheet1'de bulunan aşağıdaki listeye(sadece A kolonu olduğunu düşünün) lookup çekmek isteseydik şu formülü yazardık.(veya yardımcı bir index satırı kullanırdık, ama şık olanı bu aşağıdakidir)

=VLOOKUP($A2;Sheet2!$A:$F;MATCH(B$1;Sheet2!$A$1:$F$1;0);0)

İşte bizim süperlookup fonksiyonumuz bizi bu uzun formülden kurtarmış oluyor.

Function süperlookup(alan As Range, sütun As Range, aranan As Range)
On Error GoTo hata
    süperlookup = alan.Columns(1).Find(aranan, lookat:=xlWhole).Offset(0, sütun.Column - alan.Columns(1).Column).Value
    Exit Function
hata:
    süperlookup = "Bulunamadı"
End Function

Gün nosundan(1-7) gün adını veren fonksiyon

Normade bunu aşağıdaki fomrülle yazabiliriz ama TEXT fonksiyonu bazen karışık olabilmekte ve bazı kişelerin yıldızı bu fomrülle barışık olmayabiliyor.

=TEXT(A1+1;"gggg") //A1'de 2 yazıyorsa  salı döndürür. 1 ekleme sebebi günlerin Ameirkan fomratına göre Pazardan başlamasıdır

Gün UDF'i çok daha basit gibi duruyor.

Function gün(hucre As Range)Select Case hucre.Value
    Case 1
        gün = "Pazartesi"
    Case 2
        gün = "Salı"
    Case 3
        gün = "Çarşamba"
    Case 4
        gün = "Perşembe"
    Case 5
        gün = "Cuma"
    Case 6
        gün = "Cumartesi"
    Case 7
        gün = "Pazar"
    Case Else
        gün = "*****Hata*****,Ben sadece 1 ve 7 arasındaki günler için çalışırım"
    End Select	

Hücredeki formül

=gün(A1)

Uçhariçortalma ile bir veri kümesindeki uç değerleri elimine edin

Diyelim ki hedefleme veya tahminleme yapıyorsunuz. Hedef verdiğiniz birimin (bölge/şube/bayi/mağaza/v.s) son 1 yıllık satışlarına bakacak ve ortalama alacaksınız, ama bir seferlik aşırı düşük/büyük rakamların da ortalamayı etkilemesini istemiyorsunuz. O yüzdeb en küçük ve en büyük rakamları çıkartıp kalan 10 ayın ortalamasını almak istiyorsunuz. Bunun için normalde şu Excel fonksiyonunu yazardınız.

=(SUM(B2:B13)-SMALL(B2:B13;1)-LARGE(B2:B13;1))/(COUNT(B2:B13)-2)	

Ama biz şu UDF ile işimizi kısaca hallederiz.

Function uçhariçort(alan As Range, Uç As Variant)
Dim aratoplam As Double
Dim enbüyükler As Double
Dim enküçükler As Double

For i = 1 To Uç
    enbüyükler = enbüyükler + WorksheetFunction.Large(alan, i)
Next i
    
For i = 1 To Uç
    enküçkler = enküçkler + WorksheetFunction.Small(alan, i)
Next i
     
aratoplam = WorksheetFunction.Sum(alan) - enbüyükler - enküçkler
uçhariçort = aratoplam / (alan.Count - Uç * 2)

End Function

UDF'imizi =uçhariçort(B2:B13;1) şeklinde kullanınca sonuç aşağıdaki gibidir.

Sona girdiğimiz 1 parametresi en küçük/büyük 1 değeri hariç tutmamızı sağlar. Eğer 2şer küçük/büyük hariç tutmak istersek buraya 2 gireriz, bu sefer kalan 8 ayın ortalaması alınmış olur.

Süpertrim ile TRIM yapılamayan karakterleri de silin

Bu fonksiyon Outlook gibi farklı bir ortamdan kopyalanarak alınan rakamların önündeki ve sonundaki boşlukları yoketmek için kullanılır. Genelde herkes Excel'in TRIM fonksiyonunu bilir ve bununla bu boşlukları kaldırabileceğini düşünür ancak aslında bunlar her zaman bildiğimiz boşluk olmayabiliyor, o yüzden TRIM yetersiz kalıyor. Metinsel fonksiyonlarda bu durumu açıklamıştık. Detayı oradan okuyabilirsiniz. İşte bu linkte verilen fomrülü yazmak yerine aşağıdaki UDF oldukça iş görecektir

Function süpertrim(hucre As Range)
    süpertrim = Val(WorksheetFunction.Trim(WorksheetFunction.Substitute(hucre, Chr(160), "")))
End Function

Sapmaoranı ile veri kümesindeki sapmayı bulun

Bu fonksiyon ile incelediğiniz küme içinde bi dengesizlik var mı, rakamlar birbirinden çok mu farklı yoksa makul sınırlarda mı sapma var, bunu görmümş olursunuz. Yapılan işlem aslında kümenin standart sapmasının ortalamaya bölümüdür. Bu oran ne kadar küçükse o kadar dengelidir, yani rakamlar birbirine yakındır, ne kadar büyükse sapma miktarı o kadar yüksektir. Tek elemanlı bir kümede hata alınır, siz eleman sayısının tek olup olmamasına bakarak fonksiyonu iyileştirin.

Function sapmaoranı(alan As Range)
    sapmaoranı = WorksheetFunction.StDev_S(alan) / WorksheetFunction.Average(alan)
End Function

Aşağıdaki örnekte şubelerdeki müşteri temsilcilerine bağlanmış müşteri adetleri görülmekte. 2 nolu şubede portföyler arasında dengesizlik sözkonusu ve müşterilerin yeniden dağıtılmasında fayda var diyebiliriz.

YORUMLAR