DATE

Ən çox İnsan Resursları mütəxəssislərinin, bəzən də mühasiblərin istifadə etdiyi staj hesablama əməliyyatlarını Excelin köməkliyi ilə necə asanlaşdıra biləcəyimizi bu məqalədə baxacayıq.

Şəkildəki nümunədə, hər bir işçi üzrə onun çalışma müddətləri göstərilib. İstənilən tarixdən başlayaraq geriyə doğru hesablamaq istəyirik ki, həmin əməkdaş hansı tarixdə işə başlayıb. Burdakı ən böyük problem, əməkdaşın çalışma müddəti bir xana içərisində və mətn kimi yazilib. Excel həmin xanadakı məlumatı ancaq mətn kimi tanıyır. İçərisində qeyd olunan rəqəm və onların ifadə etdiyi tarixlər haqqında heçbir ipucuna sahib deyil. Bu sadəcə olaraq istifadəçinin görsəl olaraq başadüşəbiləcəyi məlumatdır və tarix hesablamaları üçün istəfadəsi mümkün deyil.

Həmin mətnlərin düzgün formaya gətirməmişdən qabaq, biz geriyə doğru tarix hesablamasını hansı funksiya ilə edəcəyik ona baxaq. Bunun üçün isə, “işləmə müddəti” məlumatlarımızın şəkildəki kimi göstərildiyini fərz edək.

Bu məlumatlar əsasında biz, il-ay-gün sütunlarında olan rəqəmlərdən istifadə edərək qeyd olunan tarixdən geriyə doğru hesabalama aparacayıq.

=DATE(YEAR(A2)-C2;MONTH(A2)-D2;DAY(A2)-E2)    "Nəticə: 12 Avqust 2015"

DATE funksiyasının hər bir arqumentində il-ay-gün qeyd edilərək tarix yaradılır. İstənilən tarixi əldə etmək üçün əlimizdə olan tarixin ilindən (2019) geriyə doğru hesablanası ili çıxırıq. Yəni, 2019-3 = 2016 (digər arqumentləridəki ay-gün hesablaması 2015 ilinə gətirir). Digər ay və gün məlumatları eyni şəkildə hesablanır. Tarixin ilini tapmaq üçün YEAR, ay nömrəsini tapmaq üçün MONTH və gün nömrəsini tapmaq üçün isə DAY funksiyalarından istifadə etdik. Funksiya içərisindəki arqumentlərin nəticəsi bu şəkildədir.

=DATE(2019-3;1-4;1-20)    "Nəticə: 12 Avqust 2015"

Ancaq bizim yüzlərcə işçimiz olarsa, hər bir sətrdə bu məlumatların əlnən yazılması dügün olmayacaq.

Əgər bizim tarix məlumatlarmız şəkildə kimi olmuş olsaydı bu məlumatları əldə etmək çox daha asan olacaqdı. Çünki, hər bir rəqəmin yeri standartdır və asan funksiyalar ilə o rəqəmləri əldə edə bilərdik.

"İl rəqəmi üçün"
=LEFT(A1;2)
"Ay rəqəmi üçün"
=MID(A1;7;2)
"Gün rəqəmi üçün"
=MID(A1;13;2)

Real data isə, qeyd olunan standartdan uzaqdır. Bu isə, istənilən rəqəmləri əldə etmək üçün daha qəliz funksiyalardan istifadə edəcəyimiz mənasına gəlir.

Bizə burda üç ayrı məlumat lazımdır, il-ay-gün. Həmin məlumatları şəkildəki kimi ayrı ayrılıqda tapıb DATE funksiyası içərisində istifadə edəcəyik.

Əsas məntiq, əgər “il” rəqəmini əldə ediriksə, “il” kəliməsinə qədər olan 2 rəqəmi mətndə çıxarmalı, əgər “il” məlumatı yoxdursa o zaman “0” nəticəsini əldə etməliyik.

“İl” rəqəminin tapılması

Əvvəlcə tələb olunan formatı axtarırıq

=SEARCH("?? il";" "&B2);5)

“?? il” formatı, 2 ədəd istənilən mətn – boşluq – “il” deməkdir. SEARCH funksiyasının ikinci arqumentində ” ” (boşluq) simvolunu mətn ilə birləşdirdim. Çünki, əgər ilk rəqəm tək simvoldan ibarətdirsə o zaman xəta verəcək.

Sonra, MID funksiyası ilə “il” kəliməsi daxil həmin mətnləri çıxardırıq.

=MID(" "&B2;SEARCH("?? il";" "&B2);5)    "Nəticə:  3 il"

Üçüncü addım, “il” kəliməsini silirik.

=SUBSTITUTE(MID(" "&B2;SEARCH("?? il";" "&B2);5);"il";"")

Dördüncü addım, həmin əldə olunan məlumatı rəqəmə çüviririk.

VALUE(SUBSTITUTE(MID(" "&B2;SEARCH("?? il";" "&B2);5);"il";""))

Son olaraq isə, əgər mətn içərisində “il” kəliməzsi olmazsa xəta verəcəyi üçün IFERROR ilə onu “0” rəqəminə çeviririk.

=IFERROR(VALUE(SUBSTITUTE(MID(" "&B2;SEARCH("?? il";" "&B2);5);"il";""));0)

Yekundə “3” rəqəmini alırıq.

“Ay” rəqəmininin tapılması

=IFERROR(VALUE(SUBSTITUTE(MID(" "&B2;SEARCH("?? ay";" "&B2);5);"ay";""));0)

Funksiyanın məntiqi eynidir, “il” kəliməsini “ay” kəliməsi ilə əvəz edirik.

“Gün” rəqəminin tapılması

=IFERROR(VALUE(SUBSTITUTE(MID(" "&B2;SEARCH("?? gün";" "&B2);6);"gün";""));0)

Burda da, “gün” kəliməsi istifadə edildi və MID funksiyasının üçüncü arqumentində 6 rəqəmini yazdıq, çünkü formatımız 6 simvoldan ibarətdir.

Yekun

İndi isə, əldə olnan rəqəmləri DATE funksiyası içərisində istifadə edirik.

=DATE(YEAR(A2)-C2;MONTH(A2)-D2;DAY(A2)-E2)

Köməkçi sütunlardan istifadə etmək istəmiriksə, o zaman qeyd oluan hər bir funksiya DATE içərisində yazılacaq.

=DATE(YEAR(A2)-IFERROR(VALUE(LEFT(MID(" "&B2;SEARCH("?? il";" "&B2);5);3));0);
MONTH(A2)-IFERROR(VALUE(LEFT(MID(" "&B2;SEARCH("?? ay";" "&B2);5);3));0);
DAY(A2)-IFERROR(VALUE(LEFT(MID(" "&B2;SEARCH("?? gün";" "&B2);6);3));0))

Bu məsələyə dair sual və ya daha sadə həll yollarınız varsa şərh hissəsinə yaza bilərsiniz.

Qeyd olunan funskyalar haqqında ilkin məlumatları aşağıdakı linklərdən əldə edə bilərsiniz.

Tarix Funksiyalarına Qısa Baxış (YEAR, MONTH, DAY, DATE, TODAY, NOW, WEEKDAY, WEEKNUM)

Exceldə Mətn İşarələrinin (Wildcard – *,?,~,=) Istifadəsi

Cümlələrdən İstənilən Mətni necə Ayırmaq olar?

Məsələnin həllini daha ətraflı olaraq Youtube kanalımdan izləyə bilərsiniz.

Array Formulalar
Hesablanmış dəyərə əsasən LOOKUP verilməsi – Array (+Video)

Biz normal şərtlərdə LOOKUP verərkən sadə məntiqlə axtarılan dəyərin qarşısındakı (və ya hərhansı tərəfindəki) məlumatları əldə etmək istəyirik. Bəs müəyyən şərtlərə əsasən hesablanmış rəqəmlərin qarşısındakı məlumatları necə gətirmək olar? Bugünkü məqalədə bucür hesablamaların əsasında əldə edilən dəyərlərə görə necə LOOKUP veriləbiləcəyinə baxacayıq. Nümunə olaraq aşağıdakı cədvələ nəzər yetirək: Nümunədə hər hansı …

Array Formulalar
Şərtə görə MAX və MIN dəyərlərin tapılması – Array ilə (+Video)

Bir əvvəlki məqaləmizdə OFFSET ilə şərtə əsasən MAX və MIN rəqəmlərin tapılmasını gördük. İndi isə eyni nəticəyə Array metodu ilə gələcəyik. Array istifadəsinin OFFSET-ə nəzərən üstünlükləri: OFFSET funksiyasında şərtlərin olduğu sütun ardıcıl olmalıydı. Ancaq array istifadəsində buna ehtiyac yoxdur Şərtlərimizin sayı 2 və daha çox olarsa OFFSET ilə bunu həll …

COUNTIF
Şərtə görə MAX və MIN dəyərlərin tapılması – OFFSET ilə (+Video)

Düşünün ki, şəkildəki kimi “Bolgələr və Şəhərlər” üzrə satış rəqəmləriniz var və siz hər bir regionda ən az (MIN) və ən çox (MAX) satış rəqəmlərini əldə etmək istəyirsiniz. Biz istəyirik ki, şəkildəki cədvələ əsasən əgər “Bakı Abşeron Bölgəsi” seçərsəm bizə ən az satış olan 600 (1-ci ay üçün) və ən …