Ay Bazlı Gruplama ve Ortalama, verileri ay bazında gruplamakta ve her grup için ortalama değerleri hızlıca hesaplamaktadır.
Excel’de Ay Bazlı Gruplama ve Ortalama
Dosyada, her çalışanın tarih bazlı olarak 5 ayrı mesai türüne dair saatlik şeklinde verileri bulunmaktadır. Bu verilerin, çalışan ve ay bazlı olarak gruplandırılması amaçlanmaktadır. Daha sonra, gruplanan verilerin ortalamalarının bulunması sağlanmaktadır.
Formül Adımları
Dosyada oldukça ileri düzey ama bir o kadarda faydalı bir formül yer almaktadır. Bu formülün işleyişine dair adımlar şu şekildedir:
- LET fonksiyonu, formülün içinde kullanılacak değişkenleri tanımlamaktadır.
- t değişkeni, ayırıcı olarak ‘-‘ karakterini tutmaktadır.
- w değişkeni, iki parametre alan ve metni ‘-‘ karakterine göre bölerek belirli bir parçayı döndüren bir LAMBDA fonksiyonudur.
- a değişkeni, A2:A425, B2:B425, C2:C425, D2:D425 ve E1:I1 ile E2:I425 aralıklarındaki hücrelerin değerlerini ‘-‘ karakteri ile birleştirerek tek bir sütun oluşturmaktadır.
- sicil değişkeni, a değişkenindeki metinleri ‘-‘ karakterine göre bölerek ilk parçayı almaktadır.
- isim değişkeni, a değişkenindeki metinlerin ikinci parçasını almaktadır.
- soy değişkeni, a değişkenindeki metinlerin üçüncü parçasını almaktadır.
- tarih değişkeni, a değişkenindeki metinlerin dördüncü parçasını alır ve tarih formatına dönüştürmektedir.
- fm değişkeni, a değişkenindeki metinlerin beşinci parçasını almaktadır.
- değer değişkeni, a değişkenindeki metinlerin altıncı parçasını alır ve sayısal değere dönüştürmektedir.
- z değişkeni, ÖZETÖLÇÜT fonksiyonunu kullanarak sicil, isim, soy ve tarih bilgilerine göre fm ve değer değişkenlerini gruplayarak toplama işlemi yapmaktadır.
- EĞERYOKSA fonksiyonu, DÜŞEYYIĞ fonksiyonunun sonucunu kontrol etmektedir.
- DÜŞEYYIĞ fonksiyonu, YATAYYIĞ fonksiyonunun sonucunu kullanarak ortalama değerleri hesaplamaktadır. Ardından, GRUPLAÖLÇÜT fonksiyonunu kullanarak isim ve soy isim bazında ortalamaları yuvarlamaktadır.
- Sonuç olarak, formül verileri belirli kriterlere göre gruplamakta, toplam ve ortalama değerleri hesaplamakta ve eksik değerleri kontrol etmektedir.
Bu formül, tarih bazlı verileri kişi bazında aylık olarak gruplandırmaktadır. Ardında, her ay için ortalama değerleri hesaplamaktadır. Böylece, 365 satır gibi günlük veriler yerine, bu verilerin ait olduğu 12 aya karşılık gelen ortalamalar elde edilir. Bu yöntem, verilerin aylık performansını daha net analiz etmeye ve karşılaştırmaya olanak sağlar. Ayrıca, veri setinin boyutunu da anlamlı şekilde küçültür. Özellikle İnsan Kaynaklarında Puantaj analizleri yapanlar için oldukça kıymetli bir Excel dosyasıdır.
Yararlanılması temennisiyle.
