Bazen Excel’in kendi formülleri işinizi görmez. Karmaşık bir işlemi ya da süreci kendi algoritmanızla tanımlayıp formülize edersiniz. Ya da iç içe excel formülleri yerine temiz bir makro yazıp dışarıdan formül olarak bunu çağırmak istersiniz. Bu durumda şu linkten yazdığım kodlarla kendi formülünüzü oluşturabilirsiniz.
Kategori: Excel VBA
Tahmin ettiğinizin aksine histogramı excel’de çizmek herzaman kolay değildir. Sürümden sürüme bazı problemler çıkabilmektedir. İşte kesin çözüm (Excel 2010 için):
Excel’inizde “Çözücü” ya da İngilizce olararak kullanıyorsanız “Solver” eklentisi(add-on) kurulu olmalıdır. Bunun için şu adresten nasıl kurulacağını öğrenebilirsiniz. Daha sonra excel dosyası içinden Veri> Veri çözümleyicisi’ni seçim oradan açılan pencere’den Histogram‘ı seçin.
Kolay gelsin
Çok spesifik durumlar dahilinde Excel dosyanız içindeki bir veriyi makro yordamı ile web sunucusuna yollamak isterseniz aşağıdaki makro işinizi görecektir. Makro çalıştırıldığında data sunucuya yollanmakta ve cevap ekrana mesaj olarak yansıtılmaktadır.
Kodlarım şurada: https://gist.github.com/suatatan/e377de41ce7f6ea94ba3
Google App ile excel tablolarının Google dökümanlardaki muadili olan Google e-tablolara entegre formlar yazabiliriz:
Aşağıdaki gibi:
Bunu oluşturabilmek için Araçlar> Komut dosyası yöneticisini kullanıyoruz.
Aşağıdaki gibi kodlarımızı yazıyoruz:
Kodlarımızı yazdıktan sonra üçgene basarak form_goster fonksiyonumuzu çalıştırınca ilk resimdek form çıkıyor. Bu forma girilen veri form_isle fonksiyonu ile A1 hücresine yazdırılacak.
Açıklamalı kodlarımız aşağıda:
function form_goster() {
//Uygulama oluştur
var app=UiApp.createApplication();
app.setTitle(“Suat Google Apps Form”);
//panel oluştur
var panel=app.createVerticalPanel();
//texbox oluştur
var tf=app.createTextBox();
//Ekle butonu oluştur
var bt=app.createButton(“Ekle”);
//oluşturulan texbota isim ata
tf.setName(“ad”).setId(“ad”);
//bu elemanları panele yerlestir
panel.add(tf);
panel.add(bt);
//form submit edilince yapilacak islem
var formtetik=app.createServerHandler(“form_isle”);
//tetigi butona atadik
bt.addClickHandler(formtetik);
//is bitince panele don.
formtetik.addCallbackElement(panel);
//paneli de uygulamaya yerlerstir
app.add(panel);
//uygulamayi goster
var doc=SpreadsheetApp.getActive();
doc.show(app);
}
function form_isle(e){
//calisan uygulamay ele al
var app=UiApp.getActiveApplication();
//formdan gelen veriyi cek
var formdan_gelen_ad=e.parameter.ad;
//bu veriyi ne yapalim. alip A1’e yazdiralim
var sheet=SpreadsheetApp.getActiveSpreadsheet();
sheet.getRange(“A1”).setValue(formdan_gelen_ad);
}
Google App Script ile aşağıda yazdığım fonksiyonlarla A sütununda sıralanmış parasal tutarların risk değerleri için 1 ila 5 arası değerleme yapılıp B sütununa otomatik olarak yazdırılır.
İşte script:
function risk_test(tutar){
var x=tutar;
var risk=0;
if ((x>10000) && (x
risk=1;
}
else if ((x>100000) && (x
risk=2;
}
else if ((x>500000) && (x
risk=3;
}
else if ((x>1000000) && (x
risk=4;
}
else if (x>2000000){
risk=5;
}
else
{
risk=0;
}
return risk;
}
function risk_hesabi_yap_suat_atan_abi(){
var ss=SpreadsheetApp.getActiveSpreadsheet();
var sheet=ss.getSheets()[0];
for(i=2;i
var kaynak=sheet.getRange(i,1).getValue();
var hedef=sheet.getRange(i,2);
var sonuc=risk_test(kaynak);
hedef.setValue(sonuc);
}
Browser.msgBox(“Risk hesaplandı”);
}
Google Drive (Docs) kullanıyorsanız excel makroları muadili olarak kullanılan ve saf Javascriptle yazılan “Google App Script” kodlamayla üstün ve karmaşık işlemler yapabilirsiniz. Buna mail gönderimi, google data servislerine erişim de dahildir. Bu yönüyle excel makrolarından daha fazla şey vadediyor. Ve daha kolay…
Script A1 hücresindeki değeri alıp kdv’sini hesaplayıp B1’e yazıyor.
İşte ilk scriptim:
function kdv_hesapla(deger) {
var kdv=deger*0.18;
return kdv;
}
function run(){
var ss=SpreadsheetApp.getActiveSpreadsheet();
var sheet=ss.getSheets()[0];
var kaynak=sheet.getRange(“A1”);
var hedef=sheet.getRange(“B1”);
var deger=kdv_hesapla(kaynak.getValue());
hedef.setValue(deger)
}
Bazen excel’deki formüllerin yetmediği durumlar olabilir. Mesela içiçe 5 eğer fonksiyonu veya karmaşık matematiksel hesaplamalar gibi. Bu durumlarda excel makrolarını kullanabilirsiniz.
Aşağıdaki ekte bulunan makro bundan evvel şu makalede anlattığımız python ile yaptığımız parasal değere karşılık risk analizi yapan formülasyonun excel makrosu dili (VBA) ile yazılmış halidir.
Şu adresten indirip makro alanına import edebilirsiniz.
https://docs.google.com/open?id=0B2QbjSFSlgaMX2NJWkU2U0dhT28
Makro kaynak kodları ise şöyle:
Sub VeriKontrol()
‘For dongumuzu acalim
For i = 1 To 99
‘Parasal degerimizi tanimliyoruz
Dim parasal_deger As Long
‘Sonra bu degere A1,A2,A3… hucremizden aldigimiz degeri atiyoruz her seferinde
parasal_deger = Range(“A” & i)
‘Parasal degerimizi checkediyoruz.
‘Bunun icin parasal degeri x sayalim
Dim x As Long
x = parasal_deger
‘Risk degerimizi varsayian olarak 0 atayalim
Dim risk As Integer
risk = 0
‘If blogumuz pythondaki : yerine Then ve sonda Endif kullandik
If x > 10000 And x
risk = 1
ElseIf x > 100000 And x
risk = 2
ElseIf x > 500000 And x
risk = 3
ElseIf x > 1000000 And x
risk = 4
ElseIf x > 2000000 Then
risk = 5
Else:
risk = 0
End If
‘Simdi hesaplanan risk degerimizi B sutunumuza siraliyoruz
Range(“B” & i) = risk
‘For dongumuzu guzel guzel next ile devam ettiryoruz
Next i
End Sub
Excel’de bir hücreye yazı yazmak için çift tıklanıp girildiği, yazının bitiminde ise başka hücreye geçmek için ilgili hücreye tıklanarak veri girildiği bilinmektedir.
Bu rutin büyük çaplı excel dosyaları üzerinde çalışırken sıkıntı verebilmektedir. Ayrıca değiştirilen yerlerin hangisi değişti, hangisi değişmedi diye bulmak zahmet olmaktadır.
Bunun için bir VBA makrosu geliştirdim.
Siz istediğiniz hücredeyken, tıklama yapmaksızın Ctrl+q tuş kombinasyonunu çalıştırdığınızda açılan veri girme formuna verinizi girip tamam dediğiniz anda, ilgili hücreye veri giriyor ve arka plan sarı renge boyanıyor.
Böylece formlar üzerindeki değişikliği hızla gerçekleştirebiliyorsunuz.
İşte kodlar:
Sub IstediginHucreyeDirektVeriYazirma()
Dim veri
'
' Mesela bir hucreyi secip ctrl+q diyince
' forma veri girildigi anda
' girilen veri ayni anda ilgili hucreye islenir
' ve arka plan sari olur
' Hizli tablo doldurma icin ideal cunku hucreleri
' secme ve yazma rutinleri olmuyor
' Klavye Kısayolu: Ctrl+q
' herkes-icin-excel.blogspot.com
ActiveCell.Select
veri = InputBox("Veri", "SZL")
ActiveCell.FormulaR1C1 = veri
ActiveCell.Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Sub
Excel makroları ile ya da diğer adı ile VBA ile yapılamaycak şey yok gibi. Şimdi diyelim ki bir excel tablosu ile çalışırken, döviz kurları ve benzeri bir veriyi almak için sürekli ve sıkça aynı web sitesini açıp bakmanız gerekiyor. Bunu elle yapmak yerine excel içerisine bir makro yazarak, makroya ister kısayol eklemek suretiyle ister bir butona atamak suretiyle otomatik olarak internet sitesini excel içinde açabilirsiniz.
Bunun direkt kodu vermek yerine teker teker ekran görüntüleri ile yapılışını gösteriyorum
Önce excel tablomuzu açıyor (2007 versiyon), Geliştirici sekmesinde Visual Basic butonunu tıklıyoruz.
Sonra açılan VBA ekranımıza kodlarımızı ekliyoruz.
En son olarak excel sayfamıza dönüp makromuzu test ediyoruz.
Açılan ekranda gelştirici sekmesinde makroları tıklayıp, GoToWebsite adlı fonksiyonu seçip çalıştır dediğimizde açılmasını istediğimiz suatatan.wordpress.com isimli sayfamız açılıyor.
Bu da kodlar
Sub GoToWebSite()
Dim appIE As Object ' InternetExplorer
Dim sURL As String
Application.ScreenUpdating = False
Set appIE = CreateObject("InternetExplorer.Application")
sURL = "http://www.codeforexcelandoutlook.com"
With appIE
.Navigate sURL
.Visible = True
End With
Application.ScreenUpdating = True
Set appIE = Nothing
End Sub
Önceki yazımızda Excel ile bir internet sitesini otomatik olarak nasıl açacağımızı işlemiştik. Şimdi ise içinde form barındıran herhangi bir siteyi açarak, formu otomatik olarak nasıl doldurabileceğimizi göstereceğiz.
Lütfen şurayı tıklayarak ilgili yazımı görün:
https://suatatan.wordpress.com/2012/11/29/excel-vba-ile-web-sunucusuna-http-protokolu/
