EXCEL PUANTAJ PROGRAMI İLE PERSONEL MAAŞLARINI OTOMATİK HESAPLAMA

Bu yazımızda Excel de dinamik bir puantaj programı hazırlıyor olacağız. Program sonunda personellerin hakedişlerini de gösteren bir program oluşturacağız. Dilerseniz Youtube‘daki Excel eğitim videolarımızda konunun yapılışıyla ilgili bilgiyi aşağıdaki videodan edinebilirsiniz.🙂

👉 Dosyayı indirmek için tıklayın.

Program toplam 3 sayfadan oluşmaktadır. İlk sayfa olan Giriş sayfasında kullanıcının görüp takip edebileceği bir program yer almaktadır. Personellerim sayfası veri girişi yapılan sayfadır ve son olarak Data sayfası ise formülleri gireceğimiz ve programı hazırladıktan sonra gizleyeceğimiz bir sayfadır. Bu 3 sayfayı ön hazırlık olarak aşağıdaki şekilde hazırlayabilirsiniz. Giriş sayfasındaki tasarımda istediğiniz gibi değişiklikler yapabilirsiniz.

👉 Dosyayı indirmek için tıklayın.

Giriş sayfasında üst konumda yer alan Çalışıyor(Ç), Yıllık İzin(Yİ), Raporlu(R), Resmi Tatil(RT), Ücretsiz İzin(Üİ) başlıklarında personel hangi durumda ise bu başlıkları sembol harflerle puantaj programında ifade edeceğiz.

Personel Adı Otomatik Çektirme

Giriş sayfasında Personel Adı sütununa hücreleri otomatik Personellerim sayfasından çekmek için ilk personelin yer alacağı B10 hücresine gelip = işareti koyup Personellerim sayfasındaki B2 hücresinde yer alan Özlem Avcıoğlu kişi ismine tıklayıp Enter tuşuna basalım. Özlem Avcıoğlu kişisinin Giriş sayfasına otomatik olarak geldiğini göreceksiniz. Bunu Personellerim sayfasındaki tüm kişiler için uygularken Özlem Avcıoğlu’nun Giriş sayfasında yazılı olduğu B10 hücresinin sağ alt kenarında bulunan yeşil kareden tutup tablo bitimine kadar çektiğinizde tüm personellerin isimlerini Personellerim sayfasından çekmiş olursunuz.

Giriş sayfasında bazı hücreler 0 olarak gözükmektedir. Bunun sebebi Personellerim sayfasındaki boş hücreleri de çektiğinden Giriş sayfasın 0 olarak gözükmektedir. Bunu düzeltmek için aşağıdaki formülü kullanırız. Formülün anlamı Personellerim sayfasındaki B2 hücresi boş ise hiçbir şey yazmasın boş kalsın, değil ise Personellerim sayfasındaki B2 hücresi yazsın.

=EĞER(Personellerim!B2="";"";Personellerim!B2)

Bu bölümde yer alan formüllerin ingilizcesi,

✔️EĞER() = IF()

Yıl Seçtirme

Giriş sayfasının üst konumunda yer alan Yıl Seçiniz kısmında yılları dinamik bir şekilde seçebilmek için Data sayfasına gelip Yıllar sütununun 2 alt satırındaki hücreye basalım. Bu hücreye şuanda içinde bulunduğumuz yılı formül ile girip seneye aynı çalışma kitabını kullandığınızda seneyeki yılı gösterecektir. Bu hücrenin bir alt satırlarına ise girdiğimiz formüle +1 ekleyerek içinde bulunulan yıldan 1 sonraki yılı göstermek için kullanılabilir. Aynı şekilde bir önceki yılı göstermek için ise formüle -1 yazılarak gösterilir.

Bu yılları Giriş sayfasında açılır liste kutusu şeklinde göstermek için Veri sekmesinde bulunan Veri Doğrulama seçeneğini seçelim. İzin verilen kısmında Liste seçeneğini seçelim ve Kaynak kısmına Data sayfasında hazırladığımız Yıllar sütunundaki verileri seçelim. Tamam tuşuna basalım.

Bu bölümde yer alan formüllerin ingilizcesi,

✔️ YIL() = YEAR()

✔️ BUGÜN() = TODAY()

Ay Seçtirme

Giriş sayfasının üst konumunda yer alan Ay Seçiniz kısmında ayları dinamik bir şekilde seçebilmek için Data sayfasındaki Aylar sütununa formül tanımlamamız gerekmektedir. Giriş sayfasında Yıl Seçiniz kısmında seçilen yıl ile o yıla ait ayların uyumlu olması için Data sayfasında Aylar sütununda B2 hücresine aşağıdaki formül girilir. Formülün ilk kriterindeki yıl kriterine Giriş sayfasında seçili olan yılı tanımlıyorum, ay kriterine ilk ay olan Ocak ayını temsil ettiği için 1 yazıyoruz ve 1 Ocak olmasını istediğim için son kriter olan gün kriterine 1 yazarak formülü tamamlamış oluruz.

=TARİH(Giriş!C3;1;1)

Öbür aylar için ise ilk aya ekleme olarak SERİAY() formülünü kullanırız. Bu formül seçilen tarihten belirli bir ay sonrasını tanımlıyor. B2 hücresinde yazılı olan aydan 1 sonraki ayı yazmasını istediğim için 2.kritere 1 değerini girdim.

=SERİAY(B2;1)

Data sayfasında oluşturduğumuz ayları Giriş sekmesinde Ayları Seçiniz bölümünde açılır liste kutusu şeklinde göstermek için Veri sekmesinde bulunan Veri Doğrulama seçeneğini seçelim. İzin verilen kısmında Liste seçeneğini seçelim ve Kaynak kısmına Data sayfasında hazırladığımız Aylar sütunundaki verileri seçelim. Tamam tuşuna basalım.

Bu bölümde yer alan formüllerin ingilizcesi,

✔️ TARİH() = DATE()

✔️ SERİAY() = EDATE()

Tarihleri Otomatik Getirme

Giriş sayfasında 9.satırda yer alan karelerin üzerindeki bölüme, seçimi yapılmış yıl ve aya ait günler yer alacaktır. Buraya o yıl ve aya ait ilk günü girmek için =C4 yazarız. Seçili olan ilgili ay sütununa ait ilk gün bu hücreye girilmiş olur. Bir sonraki günü yazmak için ise E9 hücresine =D9+1 yazarız. Bu durumu sütun bitimine kadar çektiğimizde bir önceki güne 1 ekleyerek ilerleyecektir.

Excel’de Haftasonlarını Renklendirme

Haftasonlarını ay ve yıla göre dinamik olarak değişkenlik gösterecek şekilde renklendireceğiz. Bunu genellikle firmalar Cumartesi ve Pazar günleri çalışmadığı için bu 2 günü renklendirip dikkat çekmesini sağlayacağız. Bu durumu HAFTANINGÜNÜ() formülü ile sağlayacağız. Bu formülde tarihlere göre haftanın hangi günü ise ona göre nümerik rakamlar atayacak ve bizde bu rakamlara göre bir koşullu biçimlendirme uygulayarak tatil günlerini renklendireceğiz.

HAFTANINGÜNÜ() formülünde ilk kritere D9 hücresindeki ayın ilk gününü seçerek tanımlarız. 2. kriterine ise açılan pencerede haftanın günlerini nasıl numaralandırmak istediğinize göre seçim yapacaksanız seçmelisiniz. Genellikle Pazartesi 1.gün Pazar ise 7.gün olarak sayıldığı için ve formülde 2.sırada yer aldığı için formüle 2 yazarız. Enter‘a bastıktan sonra oluşan formülü diğer günlere de uygulamak için sütun sonuna kadar hücrenin kenarında bulunan yeşil kareden tutup çekilir.

Ay ve yılı değiştirdiğinizde tarih ile birlikte bu sayılar da otomatik olarak değişecektir.

=HAFTANINGÜNÜ(D9;2)

Cumartesi ve Pazar günlerini kareli bölgede renkli göstermek için koşullu biçimlendirme uygularız. Kareli alanda renklendirme yapılmasını istediğim için kareli bölgeyi seçerim ve Giriş sekmesinde bulunan Koşullu Biçimlendirme kuralını seçip Kuralları Yönet‘e basıyorum. Yeni Kural butonuna bastıktan sonra Biçimlendirilecek Hücreleri Belirlemek İçin Formül Kullan seçeneğini seçip formülümü yazarız.

=D$8>5

Formüle 5 yazılmasının sebebi, 6 ve 7 rakamlarının eşleştirildiği günler Cumartesi ve Pazar olduğu için ve bu değerler 5’ten büyük olduğu için renklendirmeyi ona göre yapacak olmasıdır. Formülü yazdıktan sonra renklendirme için aynı pencerede bulunan Biçimlendir seçeneğine basıp bir renk seçimi yapılır. Biz turuncu renk seçimini tercih ettik. 2 defa Tamam tuşuna basalım ve ardından Uygula tuşuna bastıktan sonra ilgili aya ait haftasonları renklendirilecektir.

Eğer çalıştığınız firma sadece Pazar günü tatil ise koşullu biçimlendirme kuralında yazdığımız formülü =D$8>6 olarak değiştirebilirsiniz.

Bu bölümde yer alan formüllerin ingilizcesi,

✔️ HAFTANINGÜNÜ() = WEEKDAY()

Fazla Ayları Saklamak

Giriş sayfasında tarih kısmında örneğin Şubat ayını seçtiğimizde 2021 yılında 28 gün çekmektedir. Sayfadaki 9.satırda bulunan tarih bilgileri toplam 31 hücreden oluşmaktadır, 1 ayda en fazla 31 gün olduğu için. Şubat ayında 3 gün mart ayına aittir. Bunları kaldırmak için yine koşullu biçimlendirme uygulanır. Giriş sayfasında 9.satırda bulunan tarihler, Ay Seçiniz bölümünde bulunan aya eşit değilse bu hücreleri gizle kuralını uygulayacağız. Şubat ayı için kuralı uygulayacak olursak son 3 hücre Mart ayına aittir.

Bu 3 hücreyi gizlemek için, Giriş sekmesinde bulunan Koşullu Biçimlendirme kuralını seçip Kuralları Yönet‘e basıyorum. Yeni Kural butonuna bastıktan sonra Biçimlendirilecek Hücreleri Belirlemek İçin Formül Kullan seçeneğini seçip formülümü yazarız. Biçimlendirme için açılan pencerede Sayı sekmesini seçip İsteğe Uyarlanmış kategorisi seçilir ve Tür‘üne ;;; yazıyoruz ve Tamam tuşuna basılır. Üç noktalı virgül verinin gizlenmesini sağlar.

=AY($C$4)<>AY(D9)

Bu bölümde yer alan formüllerin ingilizcesi,

✔️AY() = MONTH()

Programın devamı aşağıdaki video anlatımı ile devam etmektedir. Dilerseniz buradan da programın hazırlanışını izleyebilirsiniz.

Kullanıcı Girişlerini Ayarlama

Kullanıcı girişinde personellerin ilgili tarihte Çalışıyor(Ç), Yıllık İzin(Yİ), Raporlu(R), Resmi Tatil(RT), Ücretsiz İzin(Üİ) durumlarından birini girmesi için açılır liste kutusu yapacağız fakat bunun için önce Data sayfasında Durum sütununa bu sembolik harfleri = yazarak girelim. Toplam 5 adet durum oluşması gerekiyor.

Data sayfasına durum girişlerini yaptıktan sonra Veri sekmesinde bulunan Veri Doğrulama seçeneğini seçelim. İzin verilen kısmında Liste seçeneğini seçelim ve Kaynak kısmına Data sayfasında hazırladığımız Durum sütunundaki 5 durumu seçelim. Tamam tuşuna basalım. Bu sayede kullanıcı; Ç,Yİ,R,RT,Üİ gibi değerleri hücrelere tanımlayabiliyor.

5 farklı personel için ilgili aya farklı durum girişleri yapıldı ve Giriş sayfasının sağında yer alan hesaplama yapacağımız alan geçebiliriz.

Hesaplamalar

Bu alanda Toplam Haftasonu Gün Sayısı, Toplam Çalıştığı Gün Sayısı, Toplam Yıllık İzinli Gün Sayısı, Toplam Raporlu Gün Sayısı, Toplam Resmi Tatil Gün Sayısı, Toplam Ücretsiz İzinli Gün Sayısı ve Hakediş sütunları yer almaktadır.

Toplam Haftasonu Gün Sayısı

Toplam haftasonu gün sayısını bulmak için ÇOKEĞERSAY() formülünü kullanırız. D8 ile AH8 aralığında haftanın günlerinin nümerik olarak ifade edilişi yer almaktadır. Bu duruma göre 5’ten büyük olan 6 ve7. günler cumartesi ve pazarı kapsamaktadır. Bu yüzden formülde 5’ten büyük olarak tanımlandı.

Haftasonlarını sayarken kareli alandaki başka haftanın sonlarını da saymaması için Giriş sayfası 7.satıra AY() formülü gireriz ve tablo sonuna kadar hücrenin sağ kenarındaki yeşil kareden tutup çekeriz. Böylelikle 7. satırda 9.satırdaki hangi tarihe ait ay var ise onun numarası yazacaktır. Aşağıdaki formülde bunu son kriterde 2. ölçüt olarak belirtiriz.

=EĞERSAY($D$8:$AH$8;">5";$D$7:$AH$7;AY($C$4))

Toplam Çalıştığı Gün Sayısı

Toplam çalıştığı gün sayısını kullanıcının Ç harfi ile girişini yaptığı verileri sayarak bulunur.

=EĞERSAY(D10:AH10;$J$3)

Toplam Yıllık İzinli Gün Sayısı

=EĞERSAY(D10:AH10;$J$4)

Toplam Raporlu Gün Sayısı

=EĞERSAY(D10:AH10;$O$3)

Toplam Resmi Tatil Gün Sayısı

=EĞERSAY(D10:AH10;$O$4)

Toplam Ücretsiz İzinli Gün Sayısı

=EĞERSAY(D10:AH10;$T$3)

Hakediş

Personellerin hakedişlerini DÜŞEYARA formülü ile Giriş sayfasındaki tablodan personel adını çekip aralık bak kriterinde Personellerim sayfasındaki tablo seçilir. Enter tuşuna bastığımızda çıkan rakam günlük ücretidir. Bunu aylık hakettiği gün sayıları ile çarpınca toplam hakedişi bulunur.

=EĞERHATA(DÜŞEYARA(B10;Tablo1[[Ad Soyad]:[Günlük Ücreti]];2;0)*(AI10+AJ10+AK10+AL10+AM10);"")

Hakediş bölümündeki formülde Toplam Haftasonu Gün Sayısı, Toplam Çalıştığı Gün Sayısı, Toplam Yıllık İzinli Gün Sayısı, Toplam Raporlu Gün Sayısı ve Toplam Resmi Tatil Gün Sayısı toplanır. Toplam Ücretsiz İzinli Gün Sayısı ise çıkartılır.

Yeni personel girişi yaparken Personellerim sayfasındaki tablodan giriş yapılır. Hazırlamış olduğumuz formüllerle ise Giriş sayfasına veriyi otomatik olarak çekmiş olur.

Excel ile ilgili daha çok bilgi edinmek istiyorsanız Youtube, Instagram, Facebook, Tiktok, Linkedin platformlarından bizi takip edebilirsiniz. Udemy eğitimlerimiz için aşağıdaki görselden Kayıt Ol butonuna basarak derslere kayıt olabilirsiniz.

İlginize teşekkür ederiz, iyi günler dileriz🙂👋

Diğer Yazılarımıza da göz atın;

• Excel Satış Takip Programı
• Excel’de Personel İzin Takibi
• Ev Tipi Gelir Gider Programı

SERTİFİKALI KURSLARIMIZA GÖZ ATIN