Çözüldü tarihe göre personelin gittiği yeri saydırma

[tukayf]

Merhabalar değerli hocalarım. Yaklaşık bir 4-5 yıl excell kullandıktan sonra 3-4 yıl ara verdik şu an gene lazım oldu. öğrendiğimiz her şeyi unutmuşuz. Ekteki dosyayı da forumdan bir hocamız hazırladı ancak kendisi müsait olmadığı için soramıyorum. Bu haliyle de tam verimli kullanamıyoruz. Yardımcı olabilirseniz çok memnun olurum şimdiden çok teşekkürler.
Bütün açıklamalar aynı zamanda dosyanın içerisinde de bulunmaktadır.

A sütunu Personel isimlire
B sütunu personelin BUGÜN itibariyle merkezden ilçeye gideli kaç gün olmuş onu hesaplıyor
C sütunu personelin BUGÜN itibariyle ilçeden merkeze dönüşünün kaç gün olduğunu hesaplıyor.
D sütunu E sütunundan başlayarak personelin toplamda ne kadar il dışında kaldığını hesaplıyor.
Formüllerle yaptığımızda e sütunu dolduğunda her sütun eklemede formül kaydığı için verim alamıyoruz. Ayrıca F sütununa personelin dönüş tarihini girsek bile E sütununda değer olmadığı zaman D sütunundaki işlem için son değeri hesaplamaya dahil etmiyor.


İlçe sayfasında hangi personel hangi yere kaç kez gitmiş bunu hesaplatıyoruz. ancak kod verdiğimiz icin personelin ilk harfiyle kodumuz aynıysa bunu da sayarak gittiği ilçeyi bir fazla sayıyor.
Örneğin Bayram hiç bir zaman B kodlu yere gitmemiş ancak isminde "B" geçtiği için bunu da b koduna dahil ediyor.
Aynı şekilde Ergün E bölgesine 1 kez gitmiş ancak aynı sebepten onu da 2 sayıyor.

A sütunu Personel isimlğış

B sütunu personelin BUGÜN itibariyle personelin ne zaman il dışından geldiğini hesaplıyor.

C sütunu D sütunundan başlayarak personelin toplamda ne kadar il dışında kaldığını hesaplıyor.

Formüllerle yaptığımızda D sütunu dolduğunda her sütun eklemede formül kaydığı için verim alamıyoruz. Örneğin bayram tekrar il dışına gidecek olduğu zaman sütun ekleyip tekrar D sütununa veri girmeye devam edeceğiz.

 

[Korhan Ayhan]

Formülün kaymaması için DOLAYLI fonksiyonunu kullanabilirsiniz.

 

[tukayf]

Öneriniz için sağ olun hocam. Ancak formüller benim değil maalesef formül bilgim de yeterli değil. en azından takip sayfasındaki problemi çözebilmemiz mümkün müdür? Formülle olması şu aşamada daha iyi olacaktır.

 

[Ömer BARAN]

Merhaba.
Benim dikkatimi çeken; tarihlerin sağdan sola doğru büyüyor olması.
Anladığım kadarıyla sürekli sütun eklemesi yapıp, veri girişini hep E sütununa yapıyorsunuz.
Sütun eklemesi olunca da her sütun eklemede, formüllerin güncellenmesi gerekiyor.

Bunun yerine, en yeni verinin en sağda olmasını sağlarsanız formüllerin güncellenme ihtiyacı geciktirilebilir,
(dolu sütunları gizleyerek veya sütunları gruplandırarak sağdaki kısımların ekranda hep görünür olmasını da sağlayabilirsiniz)
belki AD TANIMLAMASI kullanılarak dolu alan tespit edilip formüllerin de bunun üzerinden hesaplama yapması sağlanabilir.

Veri girişlerini elle mi yapıyorsunuz bilemiyorum ama Ms.Excel kullanırken bir sütunda aynı tip veri olmasını sağlamanız,
bir hücrede tek veri olmasını sağlamanız, mümkün olduğunca hücre birleştirmelerinden kaçınmanız gibi hususlar
belgede kullanılacak TOPLAMA/SAYMA/ARAMA gibi işlemleri daha kolay yapmanızı sağlar diye düşünüyorum.
.

 

[Korhan Ayhan]

L2 hücresine uygulayıp deneyiniz.

=EĞERSAY(DOLAYLI("İlçe!D"&KAÇINCI($A2;İlçe!$A:$A;0)&":Z"&KAÇINCI($A2;İlçe!$A:$A;0));Takip!L$1&"*")

 

[tukayf]

Ömer Hocam elle veri giriyoruz sağa doğru ilerletiyoruz, personelin hem gittiği ilçeyi hem de tarihi aynı hücrede görebileceğimiz bir şablon oluşturamadım. Hayal gücüm ve excell bilgim yetersiz kaldı sanırım. Farklı şablonlar deneyeceğim. En azından verileri sağa doğru girmek daha mantıklı ancak bu sefer gene formülün düzeltilmesi gerekiyor. Bu konuda da yetersiz olduğumdan sanırım bu şekilde devam edeceğim. Her veri girişinden sonra formülü elle düzelteceğiz gibi duruyor. Yardımlarınız için teşekkürler.

 

[tukayf]

L2 hücresine uygulayıp deneyiniz.

=EĞERSAY(DOLAYLI("İlçe!D"&KAÇINCI($A2;İlçe!$A:$A;0)&":Z"&KAÇINCI($A2;İlçe!$A:$A;0));Takip!L$1&"*")

Çok teşekkürler hocam. Zİhninize sağlık.

 

[Ömer BARAN]

Yanlış anlamıyorsam;
İlçe isimli sayfa D sütununda, sağ taraftaki alanda (iki tarih olan hücreler), yer alan tarihlerin aralarındaki farkların toplamını hesaplatıyorsunuz.
Eğer durum/işlem bu ise; İlçe isimli sayfa D2 hücresindeki formülü hem aşağıdaki gibi kısaltıp, hem de sütun ekleme işleminden
(A sütununun soluna sütun eklenmemesi koşuluyla) etkilenmemesini sağlamak mümkün gibi görünüyor.

Verdiğim formüldeki;
-- EĞER(UZUNLUK(... kısmı tek tarih yazılmış veya veri yazılmamış hücreler için işlem yapılmamasını sağlar,
-- 4 sayısı, işlem yapılacak ilk sütun olan E sütununun sütun numarası-1 (hücrede tek tarih varsa veya boşsa zaten toplama dahil edilmeyecek),
-- 40 ise işleme dahil edilecek son sütunun sütun numarası (AN sütunu) olup,
formülü D2 hücresine yapıştırdıktan sonra DİZİ FORMÜLÜne dönüştürün (CTRL+SHIFT+ENTER ile formül uygulaması)
ve ardından da aşağı doğru koplalayın. E sütunu ve devamındaki sütunlardaki tarihlerin her zaman 10 karakter olarak yazıldığı varsayıldı.
Bu işlemi yaptıktan sonra D ve E sütunu arasına yeni sütun ekleyerek, sütun ekleme işleminden etkilenip etkilenmediğini kontrol edin.

-- Ayrıca eğer sayfanızın birinci satırında sağdaki tarih alanındaki tüm sütunlarda (en azından son dolu sütun için) METİNSEL bir BAŞLIK varsa;
FORMÜLLER menüsündeki AD TANIMLAMA kısmını açıp sonsut adını ve =KAÇINCI("ZZZ";İlçe!$1:$1;1) formülünü kullanarak bir ad tanımlaması yaparsanız, aşağıdaki formülde 40 sayısı yerine sonsut yazarak da işlem yapılabilir ve böylece formülün güncellenmesine hiç ihtiyaç kalmaz.
.

=TOPLA(EĞER(UZUNLUK(KAYDIR(A2;0;4;1;40))>20;SAĞDAN(KAYDIR(A2;0;4;1;40);10)-PARÇAAL(KAYDIR(A2;0;4;1;40);BUL("-";KAYDIR(A2;0;4;1;40))-10;10)))

 

[tukayf]

Merhabalar Ömer Hocam. Yardımlarınız için teşekkürler. Formülü uyguladım gayet düzgün çalışıyor. Ad tanımlamayı uygulamadım onu da uygulayacağım. B ve C sütunlarındaki formüllerde de aynı sorunlar var sütun eklemeden dolayı formül güncelleniyor. Bunlar için çözüm üretebilir misiniz bizlere. Çok vaktinizi alıyoruz Allah hepinizden razı olsun.

 

[Ömer BARAN]

B ve C sütunu için; ayrı ayrı nasıl hesaplama yapıldığını açıklarsanız bakayım.
Ayrıca ilgili sütunda her zaman tek tarih mi oluyor, yoksa diğğer sütunlardaki gibi iki tarih olailiyor mu bunu mutlaka bbelirtin.

 

[tukayf]

Hocam B sütunu E ve F sütunundaki hücrelerin içinde tek tarih varsa merkezden ilçeye gitmiş, gideli kaç gün olmuş BUGÜN itibariyle saydırmak istiyoruz.
C sütunu ise E ve F sütunundaki hücrelerin içinde iki tarih varsa ilçeden merkeze dönmüş, döneli kaç gün olmuş onu da BUGÜN itibariyle saydırmak istiyoruz. Şu an kullandığımız formüller doğru çalışıyor ancak sütun eklediğimizde formül güncelliyor tekrardan biz de formül güncellemek durumunda kalıyoruz.
Çok teşekkürler.

 

[Ömer BARAN]

O zaman B ve C sütunundaki formüllerdeki;
-- E2'lerin yerine KAYDIR(A2;0;4)
-- F2'lerin yerine KAYDIR(A2;0;5)
yazmanız yeterli gibi görünüyor.

 

[tukayf]

Çok teşekkürler hocam. Bu sorunu da çözdüm ancak il sayfasındaki C sütununa uyarlayamadım formülünüzü. Bu sayfada nerede hata yapıyorum acaba,

 

[Ömer BARAN]

İl sayfasında da İlçe sayfasındaki gibi araya sütun ekleyerek sağa doğru sütunları dolduracaksınız sanırım.
Örnek belgenizdeki İl sayfasında tek tarih olan hücre de yok, boş hücre de yok.

Mevcut duruma göre elbette formül oluşturulabilir ama, örnek belgenizi;
-- dolu/boş hücre olma durumu,
-- hücrede/hücrelerde tek tarih/iki tarih olma durumu
gibi farklı seçenekleri içerecek şekild eörnek veri ekleyerek güncelleyip, B ve C sütununda olması gereken sonuçları
haricen hesaplayıp kendi hücrelerine elle yazarak örneklendirirseniz, gereksiz mesajlaşma olmadan sonuca ulaştıracak formülü
oluşturmak daha kolay olur.
.

 

[tukayf]

Kriterler ekli dosya içerisinde hocam.

 

[Ömer BARAN]

Tekrar merhaba.
-- B2 hücresine (normal formül) ;

=BUGÜN()-PARÇAAL(KAYDIR(A2;0;3+(KAYDIR(A2;0;3)=""));BUL("-";KAYDIR(A2;0;3+(KAYDIR(A2;0;3)="")))+1;10)

-- C2 hücresine (dizi formülü olup CTRL+SHIFT+ENTER ile uygulanmalıdır);

=TOPLA(EĞER(UZUNLUK(KAYDIR($A2;0;EĞER(KAYDIR($A2;0;3)="";4;3);1;40))>16;PARÇAAL(KAYDIR($A2;0;EĞER(KAYDIR($A2;0;3)="";4;3);1;40);BUL("-";KAYDIR($A2;0;EĞER(KAYDIR($A2;0;3)="";4;3);1;40))+1;10)-SOLDAN(KAYDIR($A2;0;EĞER(KAYDIR($A2;0;3)="";4;3);1;40);BUL("-";KAYDIR($A2;0;EĞER(KAYDIR(A2;0;3)="";4;3);1;40))-1)))

 

[tukayf]

Allah sizlerden razı olsun hocam sayenizde çok güzel oldu. Selamlar.

 

[Ömer BARAN]

Eyvallah, kolay gelsin.
Son formüllerin bir miktar uzamasının sebebi, diğer sayfadan farklı olarak, bu sayfadai tarihlerin bir kısmının 10 karakterden oluşmaması.
Eğer tarihler hep 10 karakterli olsaydı, formüldeki BUL.... kısımları hiç olmayacaktı.

 

[tukayf]

Hocam kusura bakmayınız o benim hatam normalde tarihler hep 10 haneli ancak bu dosyaya aktarım yaparken baştaki "0" otomatik silinmiş. Bende fark etmemişim siz söyleyince durumu anladım. Hakkınızı helal edin.

 

[Ömer BARAN]

Kafam sakinleştiğinde (umarım unutmam) formüllerin kısaltılmış halini tekrar yazarım.
İyi çalışmalar dilerim.