• DİKKAT

    DOSYA İndirmek/Yüklemek için ÜCRETLİ ALTIN ÜYELİK Gereklidir!
    Altın Üyelik Hakkında Bilgi

Uzun formüller konusunda tavsiye

K

kamenzü

Katılım
20 Aralık 2006
Mesajlar
173
Excel Vers. ve Dili
365 (2016) Türkçe
Merhaba,

yaklaşık 7-8 aydır excel formülleri ve vba'yı öğrenmeye çalışırken uykusuz geçen gecelerin biri daha bitti,

Sonuç aşağıda ancak ustalardan tavsiye almak istiyorum;

Sol kulağımı sağ ayağımla tutmak çok zor oluyor :)

Benim dosyamda yaklaşık 310 sheet var ve bir çok yerinde aşağıdaki gibi formüller var, bunları kısaltmanın yolu nedir?

Örneğin aşağıdaki formülün çok uzamasının bir nedeni;

B168:G169 aralığında içinde değeri "1" veya "1*" olan hücrenin adresini bir kerede bulduramıyorum.

Kod: 
EĞER(UZUNLUK(EĞER($C$4="İç Tetkik";KAYDIR(İNDİS(Bilgiler!B168:G169;EĞER(EĞERYOKSA(KAÇINCI(BİRLEŞTİR(SAYIYAÇEVİR(SAĞDAN($A$2;2));"*");Bilgiler!B168:C168););1;2);EĞERYOKSA(KAÇINCI(BİRLEŞTİR(SAYIYAÇEVİR(SAĞDAN($A$2;2));"*");Bilgiler!B168:C168);KAÇINCI(BİRLEŞTİR(SAYIYAÇEVİR(SAĞDAN($A$2;2));"*");Bilgiler!B169:C169)));-2;);KAYDIR(İNDİS(Bilgiler!B168:G169;EĞER(EĞERYOKSA(KAÇINCI(SAYIYAÇEVİR(SAĞDAN($A$2;2));Bilgiler!B168:C168););1;2);EĞERYOKSA(KAÇINCI(SAYIYAÇEVİR(SAĞDAN($A$2;2));Bilgiler!B168:C168);KAÇINCI(SAYIYAÇEVİR(SAĞDAN($A$2;2));Bilgiler!B169:C169)));-2;))<=8);EĞER($C$4="İç Tetkik";KAYDIR(İNDİS(Bilgiler!B168:G169;EĞER(EĞERYOKSA(KAÇINCI(BİRLEŞTİR(SAYIYAÇEVİR(SAĞDAN($A$2;2));"*");Bilgiler!B168:C168););1;2);EĞERYOKSA(KAÇINCI(BİRLEŞTİR(SAYIYAÇEVİR(SAĞDAN($A$2;2));"*");Bilgiler!B168:C168);KAÇINCI(BİRLEŞTİR(SAYIYAÇEVİR(SAĞDAN($A$2;2));"*");Bilgiler!B169:C169)));-3;);KAYDIR(İNDİS(Bilgiler!B168:G169;EĞER(EĞERYOKSA(KAÇINCI(SAYIYAÇEVİR(SAĞDAN($A$2;2));Bilgiler!B168:C168););1;2);EĞERYOKSA(KAÇINCI(SAYIYAÇEVİR(SAĞDAN($A$2;2));Bilgiler!B168:C168);KAÇINCI(SAYIYAÇEVİR(SAĞDAN($A$2;2));Bilgiler!B169:C169)));-3;)))

Açık hali de bu;

EĞER(UZUNLUK(EĞER($C$4="İç Tetkik";KAYDIR(İNDİS(Bilgiler!B168:G169;EĞER(EĞERYOKSA(KAÇINCI(BİRLEŞTİR(SAYIYAÇEVİR(SAĞDAN($A$2;2));"*");Bilgiler!B168:C168););1;2);EĞERYOKSA(KAÇINCI(BİRLEŞTİR(SAYIYAÇEVİR(SAĞDAN($A$2;2));"*");Bilgiler!B168:C168);KAÇINCI(BİRLEŞTİR(SAYIYAÇEVİR(SAĞDAN($A$2;2));"*");Bilgiler!B169:C169)));-2;);KAYDIR(İNDİS(Bilgiler!B168:G169;EĞER(EĞERYOKSA(KAÇINCI(SAYIYAÇEVİR(SAĞDAN($A$2;2));Bilgiler!B168:C168););1;2);EĞERYOKSA(KAÇINCI(SAYIYAÇEVİR(SAĞDAN($A$2;2));Bilgiler!B168:C168);KAÇINCI(SAYIYAÇEVİR(SAĞDAN($A$2;2));Bilgiler!B169:C169)));-2;))<=8);EĞER($C$4="İç Tetkik";KAYDIR(İNDİS(Bilgiler!B168:G169;EĞER(EĞERYOKSA(KAÇINCI(BİRLEŞTİR(SAYIYAÇEVİR(SAĞDAN($A$2;2));"*");Bilgiler!B168:C168););1;2);EĞERYOKSA(KAÇINCI(BİRLEŞTİR(SAYIYAÇEVİR(SAĞDAN($A$2;2));"*");Bilgiler!B168:C168);KAÇINCI(BİRLEŞTİR(SAYIYAÇEVİR(SAĞDAN($A$2;2));"*");Bilgiler!B169:C169)));-3;);KAYDIR(İNDİS(Bilgiler!B168:G169;EĞER(EĞERYOKSA(KAÇINCI(SAYIYAÇEVİR(SAĞDAN($A$2;2));Bilgiler!B168:C168););1;2);EĞERYOKSA(KAÇINCI(SAYIYAÇEVİR(SAĞDAN($A$2;2));Bilgiler!B168:C168);KAÇINCI(SAYIYAÇEVİR(SAĞDAN($A$2;2));Bilgiler!B169:C169)));-3;)))
Genişletmek için tıkla ...
 
Son düzenleme:
Formülleri kısaltmak için ad tanımlaması yapabilirsiniz.
Formüller sekmesinde ad tanımla kısmından yapabilirsiniz. Mesela kısa bir örnek vereyim;
Diyelim ki A1 hücresi ile B1 hücresini toplayıp, C1 hücresi ile C2 hücresinin toplamı ile çarpmak istiyorsunuz.
Ad tanımlada 2 tane ad eklemeliyiz. İlkinin ismi ilktoplam olsun.
ilktoplam=A1+B1
ikincitoplam=C1+C2

Bu adları tanımladıktan sonra herhangi bir hücreye gelip; =ilktoplam*ikincitoplam diyerek sonuç alabilirsiniz.
Faydalı olması dileğiyle...
 
.

Excel'de bir işin yapılmasında pek çok yol bulunabilir. Bu yollar, kişinin farklı bakış açısı ve Excel bilgisine bağlıdır.

Önce işin ne olduğunu anlatmak gerekir.

.
 
yurttas' Alıntı:
.

Excel'de bir işin yapılmasında pek çok yol bulunabilir. Bu yollar, kişinin farklı bakış açısı ve Excel bilgisine bağlıdır.

Önce işin ne olduğunu anlatmak gerekir.

.
Genişletmek için tıkla ...

Öncelikle teşekkür ederim,

Bir hücre aralığında (Örnegin sheet1 de B168:G169 hücre aralığı)
"1" yada "1*" yı aratmam, bulunan hücrenin adresinin tespiti,

Bu formülde başka şartlar ve yönlendirmeler de var, ancak benim bir türlü beceremediğim ve dolaylı yoldan yaptığım konu bu.

Özetle; çoklu satır ve sütun aralığında; şarta uygun arama ve hücre değeri tespiti...
 
Aradığınız 1 veya 1* değeri (1*'dan maksadınız 1 ile başlayan bir veri midir yoksa sadece tam olarak " 1* " mıdır, anlaşılmıyor. Bir de bu hücre aralığında, bu iki değerden sadece birisi mi olabiliyor , ya da sadece 1 adet mi olabiliyor? Sorunuz açık değil aslında.
Bence dosyanızı eklersiniz cevap alacağınızı sanıyorum.
 
omer.baran' Alıntı:
Aradığınız 1 veya 1* değeri (1*'dan maksadınız 1 ile başlayan bir veri midir yoksa sadece tam olarak " 1* " mıdır, anlaşılmıyor. Bir de bu hücre aralığında, bu iki değerden sadece birisi mi olabiliyor , ya da sadece 1 adet mi olabiliyor? Sorunuz açık değil aslında.
Bence dosyanızı eklersiniz cevap alacağınızı sanıyorum.
Genişletmek için tıkla ...

Merhaba sayın omer.baran,

1. "1*" dan kasıt 1 ile başlayan veri,

2. Bu hücre aralığında bunlardan sadece biri olabiliyor.

3. Formülde önce "1*" ı aratıyorum.

Dosyayı Google drive a yüklerim ancak gerçekten anlatması zor;

İlk mesajda yazan "1*" ifadesi formül içerisinde elde ediliyor.
ilgili formül aşağıdadır;
BİRLEŞTİR(SAYIYAÇEVİR(SAĞDAN($A$2;2)) ;"*")
Bu ifade ile değişik sayfalarda "1 iç", "2 iç" .... "10 iç" gibi değerler elde ediyorum.

Baştaki formülün tamamı ile şunu yapıyorum.

(Kalite Yönetim Sistemi)

İşyerinde herhangi bir aksaklık olduğunda bunun düzeltilmesi için yapılan işlemlerin kağıt üstünde işletilmesi;

Formül "Bilgiler" sayfasındaki tablodan çeşitli bilgiler alarak, "DF" sayfasındaki bazı kayıtların gerekli şartlara uygun bir tanesini formülün çalıştığı hücreye yerleştirmekte.

Alt satırda aynı formülün kopyası mevcut ancak bir rakam değişikliği yaparak "DF" tablosunda bulunan bir yan veya bir alttaki kaydı vermektedir.

Bu şekilde bir çok değişik bağlantı var.

O yüzden dosyayı yüklesem de yapmak istediğimi buradan anlatmam çok zor.
 
kamenzü' Alıntı:
Öncelikle teşekkür ederim,

Bir hücre aralığında (Örnegin sheet1 de B168:G169 hücre aralığı)
"1" yada "1*" yı aratmam, bulunan hücrenin adresinin tespiti,

Bu formülde başka şartlar ve yönlendirmeler de var, ancak benim bir türlü beceremediğim ve dolaylı yoldan yaptığım konu bu.

Özetle; çoklu satır ve sütun aralığında; şarta uygun arama ve hücre değeri tespiti...
Genişletmek için tıkla ...

Burada sadece 1 ve 1* arattınız diyelim .Bulunan hücreler ne olacak?
Misal sayfanın a sütununa mı listelenecek?Listelendi diyelim sonra ne olacak ?
 
acar6783' Alıntı:
Burada sadece 1 ve 1* arattınız diyelim .Bulunan hücreler ne olacak?
Misal sayfanın a sütununa mı listelenecek?Listelendi diyelim sonra ne olacak ?
Genişletmek için tıkla ...

1. Bulunan hücre adresinin iki alt satırına kaydır komutu ile gidip orada yazan değer alınacak.

2. Bulunan hücre adresinin (Şartlı olarak) 2 veya 3 üst satırına kaydır komutu ile gidip orada yazan değer alınacak.

3. Bu iki değer birleştir komutu ile bir araya getirilecek;

Şimdiye kadar yapılanların sonucunda;

"Genel Müdür 3" vb bir ifade elde edeceğim.

Sonra bu ifadeyi başka bir tabloda aratıp,

tespit ettiğim koordinatın sırasıyla 2, 3, 4, 5, 6 ve 7 sağındaki sütunlarda yazan değerler farklı farklı tablolarda kullanılacak.
........

Kabaca Kalite Yönetim Sistemiyle alakalı firmalara dosya hazırlıyorum.

Buna benzer bir çok formül ve yaklaşık 320 sheet lik bir excel dosyası ile 4 klasörlük evrakı %85 oranında otomatik olarak yapabilmekteyim.

Bu konuda excel forumlarından epey yardım gördüm.

Buradan yardım edenlere bir kere daha teşekkür etmeliyim.
 
Yapmak istediğinizi ve formülünüzün içeriğini anlamış değilim ama, ilk mesajınızdan anladığıma göre B168:G169 aralığında 1 ile başlayan hücrenin adresi ve değerini olduğunca kısa bir formül ile bulmak istiyorsunuz. Son bir şey sorayım, bu hücre aralığında formüller kullanılarak sadece 1 adet değer üretiliyor ve bu değer 1 ile başlıyorsa, bunun hücre adresini bulmak istiyorsunuz doğru mudur?
 
Hücre aralığındaki değerler metin mi yoksa sayısal değerler midir?
 
omer.baran' Alıntı:
Yapmak istediğinizi ve formülünüzün içeriğini anlamış değilim ama, ilk mesajınızdan anladığıma göre B168:G169 aralığında 1 ile başlayan hücrenin adresi ve değerini olduğunca kısa bir formül ile bulmak istiyorsunuz. Son bir şey sorayım, bu hücre aralığında formüller kullanılarak sadece 1 adet değer üretiliyor ve bu değer 1 ile başlıyorsa, bunun hücre adresini bulmak istiyorsunuz doğru mudur?
Genişletmek için tıkla ...

Evet kısa bir formül ile bulmak istiyorum.

Hücre aralığındaki her hücre boş veya dolu olabiliyor (Random),

Ancak birbiri ile aynı değeri taşımıyorlar. Yani hepsinin değeri benzersiz.

Ben de bu arada değişik bir şekilde optimizasyon yaptım sayılır;

sonuç;

=İNDİS(DF!B:H;KAÇINCI(ARA(BİRLEŞTİR(C3;" ";KAYDIR(EĞER(C4="İç Tetkik";İNDİS(Bilgiler!B168:G169;EĞER(EYOKSA(ARA("1*";Bilgiler!B168:G168));2;1);EĞERYOKSA(KAÇINCI("1*";Bilgiler!B168:G168;0);KAÇINCI("1*";Bilgiler!B169:G169;0))));2;));DF!B:B);DF!B:B;0);2)
Genişletmek için tıkla ...
 
Aşağıdaki formül ile 1 ile başlayan hücrenin adresini bulabilirsiniz;
Kod: 
=EĞERHATA(ADRES(168;1+KAÇINCI(ARA("1";SOLDAN(B168:G168;1));B168:G168;-1);4);"")&EĞERHATA(ADRES(168;1+KAÇINCI(ARA("1";SOLDAN(B169:G169;1));B169:G169;-1);4);"")
Bulunan adresteki değeri çağırmak için bu formülü DOLAYLI formülü içine alabilirsiniz.

Bu formülün DOLAYLI parantezine alınmış halinin uzunluğu 166 karakter, sizin verdiğiniz son formülün uzunluğu ise 252 karakter.
 
omer.baran' Alıntı:
Hücre aralığındaki değerler metin mi yoksa sayısal değerler midir?
Genişletmek için tıkla ...

değerler;

1, 2, 3,.... veya

"1 iç", "2 iç", "3 iç",....

olarak giriliyor.

hem sayısal hem de metin.

Anladığım kadarıyla makro düşünüyorsunuz,

ben tüm dosyada genel kullanabileceğim makroları ayrı bir dosya olarak birleştirdim ve Office eklenti yaptım.

aktif dosyalarımı makro çalıştıramaz şekilde kaydediyorum.

Buradaki ihtiyacım da dosyanın geneline hitap etmediği için formül ile çözümlemeye ihtiyacım var.
 
Bundan evvelki son mesajımdaki formülü deneyiniz.
Tabi verdiğim formülü, bir sayfa açıp, bu sayfanın B168:g168 aralığına değerler girip aynı sayfadaki başka bir hücreye yazılacak şekilde hazırladım.
Siz formülü başka sayfadaki hücre aralığında arama yapmak şeklinde kullanacaksanız ona göre sayfa adlarını ilgili yerlere eklenmesi lazım.
 
omer.baran' Alıntı:
Aşağıdaki formül ile 1 ile başlayan hücrenin adresini bulabilirsiniz;
Kod: 
=EĞERHATA(ADRES(168;1+KAÇINCI(ARA("1";SOLDAN(B168:G168;1));B168:G168;-1);4);"")&EĞERHATA(ADRES(168;1+KAÇINCI(ARA("1";SOLDAN(B169:G169;1));B169:G169;-1);4);"")
Bulunan adresteki değeri çağırmak için bu formülü DOLAYLI formülü içine alabilirsiniz.

Bu formülün DOLAYLI parantezine alınmış halinin uzunluğu 166 karakter, sizin verdiğiniz son formülün uzunluğu ise 252 karakter.
Genişletmek için tıkla ...

Teşekkür ederim sayın omer.baran,

Yazdıklarınızı anlamaya ve dosyaya adapte etmeye çalışacağım.

Saygılarımla.
 
Estağfurullah iyi günler dilerim.
 
Cevap yazmak için giriş yap yada kayıt ol.
Geri
Üst