Excel Bilgilerimizi Pekiştirelim

[Korhan Ayhan]

Merhaba Değerli Arkadaşlar,

Evlerimizde bulunduğumuz bu süreçte bazı konuları ele almayı hedefliyorum. Tahmin ediyorum bizleri (Özel Üye-Destek Ekibi-Uzman-Yönetici ve Katkı Sağlayan Diğer Üyeler) rahatsız eden durumlardan biride tekrar eden benzer içerikli sorular.

Benim şahsi olarak bakış açım şu şekildedir;

Excel ile vakit geçiren ve mesaj sayısı 500'ü geçmiş bir üyenin artık bazı işlemleri kendi başına çözebiliyor olmasıdır.

Fakat takip ettiğim kadarıyla üyelerimizde bu durumun gerçekleşmediğini gözlemliyorum. Elbette her sorunu kendiniz çözemeyebilirsiniz. Bizler bu gibi durumlarda devreye girmeye her zaman hazırız.

Bu süreçte elimden geldiğince ve bilgim dahilinde olan excel bilgilerini paylaşmayı düşünüyorum.

Lütfen bu konu altında excelde neleri yapmakta zorlandığınızı ya da öğrenmek istediğinizi açık şekilde belirtiniz.

Gelen taleplere göre konu başlıklarını belirleyip anlatıma hızlı bir şekilde geçebiliriz.

Saygılarımla.

 

[y.selim]

Merhaba Korhan Bey,
Değerli çalışmalarınız için teşekkürler.
Kendi adıma dizi formüllerinin mantığı ve genel kullanımı diyebilirim.

 

[gicimi]

Merhaba Korhan Hocam,
Makro kodlar ile Kapalı dosyalardan veri arama ve aktarma,

 

[YUSUF44]

Ben de dizi formülleri ile makroda verileri diziye alıp hızlıca rapor vs oluşturulmasını öğrenmek isterim.

 

[cems]

Scripting.Dictionary seçme ve kullanma mantığı ile kod akışını hızlandırma , çalışmayabilecek excel versiyonu var mı ?

 

[Erdem Akdemir]

Merhaba,

Excel'i öğrenme sürecinde beni en çok zorlayan dizi formülleri diyebilirim.
Fazlaca tekrar ve deneme yanılma yöntemi ile ilerlemek gerekiyor.
Dizi formüllerindeki amaç birden fazla sonuç döndürmektir. Sonunda çıkan bu sonuçlardan biri seçilir yada tamamı birlikte değerlendirilir.
Basit bir mantık dizisinden gitmek gerekirse A1:A5 aralığına A,B,C,A,E değerlerini sırası ile giriniz.
B1 hücresine =(A1:A5="A") yazınız. Dizi formülleri F9 ve CTRL+Z arasında gidip gelen bir süreçtir.
B1 hücresinin içine girip F9'a bastığınızda ={DOĞRU\YANLIŞ\YANLIŞ\DOĞRU\YANLIŞ} mantık dizini elde etmiş olursunuz.
Bunun sayısal karşılığını görmek isterseniz formülü =--(A1:A5="A") şeklinde yazabilirsiniz.
Mantıksal dizileri kontrol etmek için bu şekilde yazmak daha iyidir. Ayrıca çıkan dizini toplamanıza da yarar.
Bu şekilde yazdığınızda ={1\0\0\1\0} sonucu elde etmiş olursunuz. 1 (DOĞRU) - 0 (YANLIŞ)
=TOPLA(--(A1:A5="A")) formülü ile tamamlarsanız 2 sonucu elde edersiniz. Dizi formülü olduğu için CTRL+SHIFT+ENTER ile kapatılmalıdır.
=TOPLA.ÇARPIM(--(A1:A5="A")) olarak yazarsanız CTRL+SHIFT+ENTER ile kapatmanıza gerek kalmaz.

Not: Dizi formülü içten dışa doğru yazılmalıdır. Çıkan sonuca göre diğer formül onun üzerine yazılır.

 

[ibrahimpeng]

exelde makro aciklamasinda sayi2 nin yanina ikinci sayi girişi nasil yazılır?

 

[Cumas]

İyi Akşamlar,
Excel öğretiminde en çok aradığım uzun formüllerin cümle olarak ifade edilmesidir. Bu şekildeki bir yöntemle daha kolay ve hızlıca öğrenmenin gerçekleşeceğine inanıyorum. Basit bir örnek vererek açıklamak istiyorum. C1 hücresinde şöyle bir formülümüz olsun. =EĞER(A1="";"";A1*B1) bunun cümle olarak ta ifade edilmesi gerekir diye düşünüyorum. Bu formülün cümle olarak ifadesi Eğer A1 hücresi boşsa bir şey yapma boş kalsın; değilse A1 ile B1'i çarp ve yaz.
Sorunlara çözüm için yazılan formüller ve makrolardaki kodlar bu şekilde cümle şeklinde ifade edilirse aynı zamanda güzel bir eğitim verilmiş olur.
Sanki çivi yazısına bakıp hiç bir şey anlamadan dosyamıza aktarmış oluyoruz.
Özet olarak proje temelli ve yukarıdaki yöntemle dersler verilirse çok yararlı olacağına inanıyorum.
Kolay gelsin.

 

[ibrahimpeng]

Sayın Cumas cümle şeklinde ifade etmek gerektiğini ben de yeni farkettim.benim sorum açıklamanın yanına yazılan açıklama.gorsel olarak
bunun nasıl yapıldığı örneğin : sayi2 ikinci sayı girişi
sayi3 üçüncü sayı girişi gibi. İLGİNİZE TESEKKUR EDERIM SAYIN CUMAS...

 

[MrExcelMr]

Korhan Bey Merhaba;
Özellikle Makro öğrenmek isteyen kişiler için basit ve orta seviye kodların yazım şeklini yazılan kodların anlamlarını tanımlamaların, nesnelerin, döngüleri uygulumalı detaylı şekilde paylaşılması. Düşeyara, İndis Kaçıncı gibi fonksiyonlar le yapılan işlemleri bir dosyada anlatımlı olarak ders veriilrse çok faydalı olacağını düşünüyorum. Teşekkürler.

 

[Tevfik_Kursun]

Sayın Korhan Hocam,
Muhteşem bir çalışma. Zevkle izleyeceğim.
Saygılarımla

 

[HOOGEZAND]

Cumas'a katılıyorum, formülün cümle olarak ifade konusuna değinilmesi harika olur.

 

[Erdem Akdemir]

Dizi formülleri ile ilgili olarak Korhan Bey'in forumda vermiş olduğu bir cevabın incelemesini yapalım.

=TOPLA.ÇARPIM(ARA(ETARİHLİ(A1;SATIR(DOLAYLI(B1&":"&C1-1));"Y");{0\15\18};{3;2;1}))

A1 = 01.05.2000 (Doğum Tarihi)
B1 = 28.04.2015 (Başlangıç Tarihi)
C1 = 05.05.2015 (Bitiş Tarihi)

Ekteki soruda yaşı 15'in altında olduğunda C1-B1'in arasındaki gün farkı 3 ile çarpılacak. 15-18 aralığında olduğunda 2 ile, diğer yaşlarda ise gün 1 ile çarpılacak. Yukarıdaki değerlere göre sonucun 17 çıkması gerekiyor.Bu örnekteki karmaşa kullanıcı aradaki günlerde 14 ve 15 yaşında oluyor. Bazı günleri 3 ile bazı günlerin ise 2 ile çarpılması gerekiyor. İşte bunun için dizi formülü kullanacağız.

Önceki mesajımda yazdığım üzere dizi formülünün iç kısmını inceleyerek başlıyoruz. DOLAYLI formülü en iç kısım ama o F9 ile açılmadığından geçiyorum. Dolaylı formülünü inceleyerek o kısmını çözmeyi size bırakıyorum.

SATIR formülü dizi formüllerinde yapısı gereği ardışık sayı üretebildiği için sıklıkla kullanılır. Aşağıdaki formülde B1 hücresindeki tarihin excel'deki sayısal karşılığı olan 42122 ile C1'deki tarihin karşılığı 42129 arasında çalışması istenmiş. Bunun için SATIR ve DOLAYLI formülü bir arada kullanılmış.
C1-B1'in sonucu 7'dir . Formüldeki -1 ise bitiş tarihini diziye almaması için. -1 olmasaydı oluşacak dizin 8 üyeli olacaktı. Yukarıdaki dizi formülünün içine girerek aşağıdaki kısmı seçerek F9 tuşuna bastığınızda {42122\42123\42124\42125\42126\42127\42128} dizinini elde etmiş olursunuz. Eğer sayarsanız dizinin 7 üyeden oluştuğunu göreceksiniz. Eklediğim dosyanın ayrı bir hücresine =METNEÇEVİR(SATIR(DOLAYLI(B1&":"&C1-1));"gg.aa.yyyy") formülü yazarak F9 tuşuna basın {"28.04.2015"\"29.04.2015"\"30.04.2015"\"01.05.2015"\"02.05.2015"\"03.05.2015"\"04.05.2015"} dizinini görecekseniz. Bunlar başlangıç ve bitiş tarihi arasındaki günler.

SATIR(DOLAYLI(B1&":"&C1-1))

Aşağıdaki formülde ise bir önceki dizinden elde ettiğimiz tarihleri doğum tarihi ile kıyaslayarak , hangi günlerde kaç yaşında olduğunu tespit ediyoruz. Bunun için excel'de kullanacağımız formül ETARİHLİ. Aşağıdaki formülü seçip F9 yaptığımızda {14\14\14\15\15\15\15} dizinini elde etmiş oluruz. Böylelikle başlangıç ve bitiş tarihlerinin 3 günü 14 yaşına, 4 günü ise 15 yaşına denk geliyor.

ETARİHLİ(A1;SATIR(DOLAYLI(B1&":"&C1-1));"Y")

Aşağıdaki kısımda ise daha önce belirlediğimiz şartlara göre ilgili yaşa karşılık gelen sayıyı bulacağız. Bunun için excel'de ARA formülünü kullanabilirsiniz. Aşağıdaki formülü seçip F9 yaptığımızda {3\3\3\2\2\2\2} dizinini elde etmiş oluruz. ARA ve ETARİHLİ fonksiyonu burada dizi formülü için araçtır. Ne işe yaradıklarını ayrıca araştırınız. Formüllerin geneli dizi formüllerinde kullanılabilir.

ARA(ETARİHLİ(A1;SATIR(DOLAYLI(B1&":"&C1-1));"Y");{0\15\18};{3;2;1})

Yukarıdaki formülden çıkan dizini'de aşağıdaki formül ile toplatarak sonucu elde etmiş oluyoruz.

=TOPLA.ÇARPIM(ARA(ETARİHLİ(A1;SATIR(DOLAYLI(B1&":"&C1-1));"Y");{0\15\18};{3;2;1}))

 

[Merhum İdris SERDAR]

.

Dizi formülleri ile ilgili çalışmalarım:

Dizi-Array Formlü – 1 – Excel Gurusu

Dizi (array) Örnekleri – Excel Gurusu

Dizi Formülünün Analitiği – Excel Gurusu

Bazı İstatistik- Dizi Formül Örnekleri – Excel Gurusu

Belirli Bir Düzende Sayı Dizisi – Excel Gurusu

Dizi-Array ve Matris Formülleri – Excel Gurusu

Sabit Dizi Elemanları Kullanma – Excel Gurusu

AND-OR FONKSİYONLARININ DİZİ FORMÜLLERİNDE KULLANINMI – Excel Gurusu

Excel’de dizi(array) formülleri lineer cebir, matris matematiği – Excel Gurusu

.

 

[Cengiz Demir]

Korhan bey öncelikle ince düşünceniz için teşekkürler.
Kutools'da şöyle bir özellik var. (Reading Layout) Bunun gibi bir eklenti yazılabilmesi mümkün müdür ? Böyle bir eklenti yapılabilirse çoğu kişinin işine yarayacağına eminim. Özellikle satır sütün takip edilerek yapılması gereken çalışmalarda çok işe yarayacaktır.

Özetle bir çeşit işaretleyici. İstediğiniz zaman kapatıp açabiliyorsunuz. Excelin kendi işaretleycisinin geliştirilmiş hali gibi bir şey yapmışlar.

 

[Erdem Akdemir]

Tekrardan merhaba. Alaylı bir excel kullanıcısı olarak kapalı dosyalardan veri alma ve sorgu konusu anlatmak istiyorum.
Verdiğim bilgiler başlangıç seviyesindedir.Merak edenler için yazmak istedim.

Veritabanlarında hızlı sorgulama yapmak ve sonuç almak için ADO nesnesinin öğrenilmesini herkese tavsiye ederim. Bu nesne ile çalışmak bana göre oldukça kolay , kodlaması kısa ve basittir. Önemli olan veritabanının doğru bir şekilde kurgulanması ve kurallarının iyi belirlenmesidir. Şimdilik sadece excel'i veritabanı olarak düşünerek anlatımıma devam edeceğim. Öncelikle excel dosyanızda verilerinizi tuttuğunuz yer 1. satırdan başlamalı ve ilk satırda daima başlık olmalıdır.Birleştirilmiş hücre kesinlikle olmamalıdır. Başlıkların da noktalama işareti içermemesi iyi olur.

Bu işlemi kapalı yada açık dosya fark etmeksizin kullanabilirsiniz. İleride anlatacağım üzere sadece yol kısmını değiştirmek yeterli olacaktır.
Veri sorgulaması için Scripting.Dictionary nesnesini, kodlanmasının zorluğu ve kapalı dosyalarda çalışmadığı için tavsiye etmiyorum. ADO ile zaman geçirdikten sonra kullanmak isteyeceğinizi düşünmüyorum.

Öncelikle bu işin A,B,C'si olarak el altınızda tutmanız gereken kod bloğu var. Bunları kopyala yapıştır ile kodunuzun başına ekleyebilirsiniz.
Sadece Con özelliği ile ADO'yu kullanabilirsiniz. Değişkenleri tanımlayarak kod yazanlar beni eleştirebilirler, onlara hak veriyorum.
Set con ile ADO bağlantı nesnesini hazırlıyoruz. Yol değişkeni ise bağlanılacak dosyanın adının yazıldığı kısım.Çalışma kitabının kendi sayfalarında sorgu yapacaksanız ThisWorkbook.FullName ile ilerleyebilirsiniz. Kapalı bir dosyaya bağlanacaksınız yol kısmına windows özelliklerdeki nesne adını yazmalısınız.

Set con = VBA.CreateObject("adodb.Connection")

yol = ThisWorkbook.FullName

con.Open "provider=microsoft.ace.oledb.12.0;data source=" & _
yol & ";extended properties=""Excel 12.0;hdr=yes"""


Con.open satırında bağlanılacak yolun özellikleri ve adı verilmektedir. Çok çeşitli veritabanlarına ve sistemlere bağlanılabilir.Bunun için https://www.connectionstrings.com/ adresini ziyaret edebilirsiniz. Bağlantınızı kontrol etmek için con nesnesinin state özelliğine bakabilirsiniz. 1 olmuşsa bağlantı kurulmuştur. HDR=yes ifadesi başlıklar var demektir. No olması sorguda yazım şeklini değiştirir.

En önemli kısım ise sorgu'dur. İşin büyük kısmı burada olmaktadır. Bu yazıda sadece seçme özelliğini anlatacağım.
Aşağıda sorgulara çok basit bir örnek vermek istedim. SELECT ifadesi ile başlıyor * işareti ile tablo'daki tüm verileri almak istediğimi ifade ediyorum
From burada değişmez ifadelerden biridir ondan sonra ulaşılacak tablonun adını yazmak gerekir. Bu excel'de sayfa adına işaret eder. Excel tablolarını $ işareti ile bitirmek gerekir. Excel'de sayfanızın içindeki alanları tablo olarak tanımlarsanız yada yazdırma alanını belirle olarak kullandığınız kısımda tablo olarak görülür. Kafanızın çok karışmaması için excel sayfa ismi = tablo ismi olarak gidin. "select istenen sütunlar from [ tablo adı] "

Sorgu konusunda kendinizi geliştirmeniz gerekir. Başlangıç seviyesi için https://www.sqlkodlari.com/kitap.asp adresini ziyaret etmenizi öneririm.

sorgu = "Select * from[sayfa1$]

Sorgu'dan sonra sonuç alınabilmesi için bunların bir kayıt nesnesine yazdırılması gerekir.Ben genelde con nesnesinin execute özelliğini kullanıyorum. Eğer daha verimli bir kayıt nesnesi ile çalışmak isterseniz ADO'nun kendi recordset özelliğini kullanabilirsiniz.

Set rs = con.Execute(sorgu)

Kayıt nesnesini oluşturduktan sonra bunları excel'e aktarmak için RANGE methodunun copyfromrecordset özelliğini kullanabilirsiniz. Yada kayıt nesnesini dizine almak için deg = rs.getrows yapabilirsiniz.

Range("A1").CopyFromRecordset rs

Not: ADO nesnesinin çalışması için çalışılan dosyanın bilgisayara kayıt edilmesi gerekir. Boş bir excel açarak onu kaydetmeden çalışırsanız hata verir.

-----------------------------------------------------------------------------

Sub ADO_deneme()

Set con = VBA.CreateObject("adodb.Connection")

yol = ThisWorkbook.FullName

con.Open "provider=microsoft.ace.oledb.12.0;data source=" & _
yol & ";extended properties=""Excel 12.0;hdr=yes"""

sorgu = "Select * from[sayfa1$]"

Set rs = con.Execute(sorgu)

Sayfa2.Range("A1").CopyFromRecordset rs

End Sub

 

[Tevfik_Kursun]

Sayın Erdem_34 Hocam,
Tam da bugünlerde aramaya başlayacağım bir konu idi. Püf noktalarını da belirtmeniz çok güzel olmuş. Hasseten kendi adıma teşekkür ederim.
Sormak istediğimi ileride anlatacaksınız konu bütünlüğü bozulmasın, siz dersinize devam edin lütfen. Ben de deneyerek takip ediyorum. En sonra da cevap verseniz olur.
excel dosyanızda verilerinizi tuttuğunuz yer 1. satırdan başlamalı ve ilk satırda daima başlık olmalıdır demiştiniz yukarıda.
G5:M??? alanında olan 12 farklı dosyada 24 farklı sayfa var. Bunları bir dosyada arka arkaya toplama şansım yok mudur?
Saygılarımla

 

[Erdem Akdemir]

@korhan bey internet işlemleri ile ilgili düzenli bir döküman yok. Xmlhttp ve internet explorer'ı anlatan bir çalışma iyi olurdu.

 

[Korhan Ayhan]

Değerli Arkadaşlar,

İlginiz, yorumlarınız ve katkılarınız için çok teşekkür ediyorum.

Benim hedefim daha çok başlangıç ya da orta seviye konulardı. Ama görüyorum ki üyelerimiz ileri seviye konulara merak duyuyorlar.

Belki konuyu görmeyen ya da mesaj yazmaya çekinenler olabilir. Çekinmeden taleplerini yazabilirler.

İleri seviye konulara (bilgim dahilinde olanlara) değinmek istemememin sebebi gerçekten bahsettiğim mesaj sayılarına ulaşan üyelerimizin halâ çok basit diyebileceğimiz soruları sormasıdır. Formül soruluyor. Verilen cevap sonrasında ben bu formülü nereye yazacağım deniliyor. Ya da mesajlarımızda DİZİ formülü uyarısı yapıyoruz bu hiç dikkate alınmıyor. Ya da sayfa-kitap kodu öneriyoruz gidip modüle uygulanıyor. Gibi....

Benim asıl değinmek istediğim durumlar bunlardır. Elbette ki ileri seviye konularda işlenebilir. Fakat yine tekrar ediyorum; "Binanın temelini kuvvetlendirmezseniz en ufak sarsıntıda yıkılacaktır."

Bu excel içinde geçerlidir. Sayfadaki tablo yapılarına, formüllere, yerleşik menülerdeki işlevlere hakim olmayan kişilerin makro öğrenme talebi kusura bakmayın ama bana çok garip geliyor.

Saygılarımla.

 

[Tevfik_Kursun]

Sayın Korhan Hocam,
Affınıza sığınarak naçizane fikrim; istekler yerine siz, kafanızda ilk planladığınız müfredatı uygulamaya başlayın. Faydalanmayı düşünenler zaten kendi antrenmanlarını yapar. Anlattıklarınızı bilen arkadaşlar sadece takip eder. Bilmeyenler yeni şeyler öğrenir. Yazım tekniğiniz bile insanlara farklı görüş verebilir.
İleri konu isteyenler için belki daha sonra açıklayıcı örnekler ve/veya bilgiler ekleyebilirsiniz.
Bana göre, koronalı günler kolay geçmiyor.
Saygılarımla