Excel iş vərəqinizə dəyişən xanalar diapazonuna əsaslanan hesablamalar daxildirsə, hesablamaları yeni saxlamaq tapşırığını sadələşdirmək üçün SUM və OFFSET funksiyalarından birlikdə SUM OFFSET düsturunda istifadə edin.
Bu məqalədəki təlimatlar Microsoft 365, Excel 2019, Excel 2016, Excel 2013 və Excel 2010 üçün Excel-ə aiddir.
SUM və OFFSET funksiyaları ilə dinamik diapazon yaradın
Daim dəyişən bir müddət üçün hesablamalardan istifadə edirsinizsə - məsələn, ay üçün satışların müəyyən edilməsi - hər günün satış rəqəmləri əlavə olunduqca dəyişən dinamik diapazon qurmaq üçün Excel-də OFFSET funksiyasından istifadə edin.
Öz-özlüyündə SUM funksiyası adətən cəmlənən aralığa yeni məlumat xanalarının daxil edilməsini təmin edə bilər. Bir istisna, məlumat hazırda funksiyanın yerləşdiyi xanaya daxil edildikdə baş verir.
Aşağıdakı misalda, hər gün üçün yeni satış rəqəmləri siyahının aşağısına əlavə edilir və bu, yeni data əlavə olunduqca ümumi məbləği hər dəfə bir xana aşağı keçirməyə məcbur edir.
Bu təlimatı izləmək üçün boş Excel iş vərəqini açın və nümunə məlumatları daxil edin. İş vərəqinizin nümunə kimi formatlanmasına ehtiyac yoxdur, lakin məlumatları eyni xanalara daxil etdiyinizə əmin olun.
Məlumatların ümumiləşdirilməsi üçün yalnız SUM funksiyasından istifadə edilirsə, funksiya arqumenti kimi istifadə edilən xanaların diapazonu hər dəfə yeni data əlavə ediləndə dəyişdirilməlidir.
SUM və OFFSET funksiyalarından birlikdə istifadə etməklə, cəmlənən diapazon dinamik olur və məlumatların yeni xanalarını yerləşdirmək üçün dəyişir. Yeni məlumat xanalarının əlavə edilməsi problem yaratmır, çünki hər yeni xana əlavə olunduqca diapazon tənzimlənməyə davam edir.
Sintaksis və Arqumentlər
Bu düsturda SUM funksiyası arqument kimi təqdim edilən məlumat diapazonunu ümumiləşdirmək üçün istifadə olunur. Bu diapazon üçün başlanğıc nöqtəsi statikdir və düsturla cəmlənəcək ilk nömrə üçün xana istinadı kimi müəyyən edilir.
OFFSET funksiyası SUM funksiyasının içərisinə yerləşdirilib və düsturla cəmlənmiş məlumat diapazonu üçün dinamik son nöqtə yaradır. Bu, diapazonun son nöqtəsini formulun yerindən bir xanaya təyin etməklə həyata keçirilir.
Düstur sintaksisi:
=SUM(Range Start:OFFSET(Referans, Satırlar, Cols))
Arqumentlər bunlardır:
- Range Start: SUM funksiyası ilə cəmlənəcək xanalar diapazonu üçün başlanğıc nöqtəsi. Bu nümunədə başlanğıc nöqtəsi B2 xanasıdır.
- Referans: Aralığın son nöqtəsini hesablamaq üçün istifadə edilən tələb olunan xana arayışı. Nümunədə Referans arqumenti düstur üçün xana istinadıdır, çünki diapazon formuladan bir xana yuxarıda bitir.
- Satırlar: Ofsetin hesablanmasında istifadə olunan Referans arqumentinin üstündə və ya altında olan sətirlərin sayı tələb olunur. Bu dəyər müsbət, mənfi və ya sıfır ola bilər. Əgər ofset yeri Referans arqumentindən yuxarıdırsa, dəyər mənfidir. Əgər ofset aşağıdadırsa, Satır arqumenti müsbətdir. Ofset eyni cərgədə yerləşirsə, arqument sıfırdır. Bu misalda ofset İstinad arqumentindən bir sətir yuxarıdan başlayır, ona görə də arqumentin dəyəri mənfi birdir (-1).
- Cols: Ofsetin hesablanması üçün istifadə olunan Referans arqumentinin solunda və ya sağında olan sütunların sayı. Bu dəyər müsbət, mənfi və ya sıfır ola bilər. Əgər ofset yeri Referans arqumentinin solundadırsa, bu dəyər mənfi olur. Ofset sağa doğrudursa, Cols arqumenti müsbətdir. Bu misalda cəmlənən data düsturla eyni sütundadır, ona görə də bu arqument üçün dəyər sıfırdır.
Cəmi Satış Məlumatı üçün SUM OFFSET Formulasından istifadə edin
Bu nümunə iş vərəqinin B sütununda sadalanan gündəlik satış rəqəmləri üçün cəmi qaytarmaq üçün SUM OFFSET düsturundan istifadə edir. Əvvəlcə düstur B6 xanasına daxil edildi və dörd gün ərzində satış məlumatlarını cəmləşdirdi.
Növbəti addım, beşinci günün ümumi satışlarına yer açmaq üçün SUM OFFSET düsturunu bir cərgə aşağı sürüşdürməkdir. Bu, düsturu 7-ci sətirə daşıyan yeni 6-cı sətir daxil etməklə həyata keçirilir.
Köçmə nəticəsində Excel İstinad arqumentini B7 xanasına avtomatik yeniləyir və B6 xanasını formula ilə cəmlənmiş diapazona əlavə edir.
- B6 xanasını seçin, bu düstur nəticələrinin ilkin olaraq göstəriləcəyi yerdir.
-
Lentin Formulalar nişanını seçin.
Image -
Seçin Riyaziyyat və Triq.
Image -
Seçin SUM.
Image - Funksiya Arqumentləri dialoq qutusunda kursoru Nömrə1 mətn qutusuna qoyun.
-
İş vərəqində bu xana istinadını dialoq qutusuna daxil etmək üçün B2 xanasını seçin. Bu yer düstur üçün statik son nöqtədir.
Image - Funksiya Arqumentləri dialoq qutusunda kursoru Nömrə2 mətn qutusuna qoyun.
-
OFSET(B6, -1, 0) daxil edin. Bu OFFSET funksiyası formula üçün dinamik son nöqtəni təşkil edir.
Image -
Funksiyanı tamamlamaq və dialoq qutusunu bağlamaq üçün OK seçin. Cəmi B6 xanasında görünür.
Image
Sonrakı Günün Satış Məlumatını Əlavə edin
Sonrakı günün satış məlumatlarını əlavə etmək üçün:
- 6-cı sətir üçün sıra başlığına sağ klikləyin.
-
İş vərəqinə yeni sətir daxil etmək üçün Insert seçin. SUM OFFSET düsturu bir sıra aşağı B7 xanasına keçir və 6-cı sətir indi boşdur.
Image - A6 xanasını seçin və beşinci gün üçün ümumi satışların daxil edildiyini göstərmək üçün 5 nömrəsini daxil edin.
-
B6 xanasını seçin, $1458.25 daxil edin, sonra Enter düyməsini basın.
Image - Cell B7 yeni cəmi $7137,40-a yenilənir.
B7 xanasını seçdiyiniz zaman yenilənmiş düstur düsturlar panelində görünür.
=SUM(B2:OFSET(B7, -1, 0))
OFSET funksiyasının iki əlavə arqumenti var: Hündürlük və Genişlik, bu nümunədə istifadə olunmayıb. Bu arqumentlər OFFSET funksiyasına sətir və sütunların sayı baxımından çıxışın formasını bildirir.
Bu arqumentləri buraxmaqla funksiya əvəzinə İstinad arqumentinin hündürlüyü və enindən istifadə edir, bu nümunədə bir sıra hündürlükdə və bir sütun enindədir.
