Dinamik aralıq məlumatlar üçün cəmi tapmaq üçün SUM və OFFSET istifadə edin
Excel iş səhifəniz dəyişən bir sıra hüceyrələri əsasında hesablamalara daxildirsə, SUM OFFSET funksiyasında SUM və OFFSET funksiyalarını birgə istifadə edərək hesablamaları gündəmə gətirmək vəzifəsini asanlaşdırır.
SUM və OFFSET funksiyaları ilə dinamik bir sıra yaradın
Dəyişən bir müddət ərzində hesablamaları istifadə edirsinizsə - məsələn, ayın satışları - OFFSET funksiyası hər gün satış rəqəmləri əlavə olaraq dəyişən bir dinamik aralığa imkan verir.
Özü ilə, SUM funksiyası ümumiyyətlə yığılmış aralığa daxil edilən məlumatların yeni hüceyrələrini yerləşdirə bilər.
Bir istisna, funksiya hazırda yerləşdiyi hüceyrəyə daxil edildikdə meydana gəlir.
Bu məqalə ilə müşayiət olunan nümunə şəklində, hər gün üçün yeni satış rəqəmləri siyahının altına əlavə olunur, bu da cəmi yeni məlumatlar əlavə olunduqdan sonra hər dəfə bir hüceyrəni aşağıya keçirməyə məcbur edir.
SUM funksiyası məlumatları toplamaq üçün özü istifadə edərsə, hər dəfə yeni məlumatlar əlavə edildikdə funksiyanın argumenti kimi istifadə olunan hüceyrələrin aralığını dəyişdirmək lazımdır.
SUM və OFFSET funksiyalarını birlikdə istifadə etməklə birlikdə, ardıcıl olan sıra dinamik olur. Başqa sözlə, məlumatların yeni hüceyrələrini yerləşdirmək üçün dəyişir. Yeni məlumatların hüceyrələrinin əlavə olunması hər bir yeni hüceyrənin əlavə olunduğu kimi tənzimlənməyə davam etdiyinə görə problem yaratmır.
Sintaksis və arqumentlər
Bu maddəni bu təlimatla birlikdə təqib etməklə müşayiət edən görüntüyə baxın.
Bu formulda, SUM funksiyası, verdiyi arqument kimi verilmiş məlumatların ümumi sayını istifadə etmək üçün istifadə olunur. Bu aralığın başlanğıc nöqtəsi statikdir və formula ilə cəmləniləcək ilk sayına hücum istinadı olaraq müəyyən edilir.
OFFSET funksiyası SUM funksiyası içərisində yerləşdirilib və formula ilə cəmlənən məlumatların dinamik son nöqtəsi yaratmaq üçün istifadə olunur. Bu, intervalın son nöqtəsini formulun yerindən yuxarıdakı bir hüceyrəyə yerləşdirməklə həyata keçirilir.
= SUM (Range Start: OFFSET (Referans, Satırlar, Cols))Range Start - SUM funksiyası ilə cəmlənəcək hüceyrələrin aralığının başlanğıc nöqtəsi (tələb olunur). Nümunə şəklində bu B2 hüceyrəsidir.
Referans - aralığın son nöqtəsini hesablamaq üçün istifadə olunan hüceyrə istinadları bir çox satır və sütunun kənarında yerləşir (tələb olunur). Məsələn nümunədə Referans arqumenti formulun özü üçün hüceyrə istinaddır, çünki biz həmişə aralığın formula üzərində yuxarıdakı bir hüceyrəni bitirməsini istəyirik.
Satırlar - ofsetin hesablanmasında istifadə olunan Referans arqumentinin yuxarı və ya aşağı hissəsində satırların sayını (tələb olunur). Bu dəyər müsbət, mənfi və ya sıfıra bərabər ola bilər.
Ofsetin yeri Referans argümanının üstündədirsə, bu dəyər mənfi olur. Aşağıda isə Satırlar arqumenti müsbətdir. Ofset eyni satırda yerləşirsə, bu arqument sıfırdır. Bu misalda, ofset, istinad arqumentinin yuxarısında bir sıra başlayır, beləliklə, bu arqumentin dəyəri mənfi (-1).
Cols - Ofsetin hesablanmasında istifadə olunan Müqavilənin arqumentinin sol və ya sağa sütunların sayı (tələb olunur). Bu dəyər müsbət, mənfi və ya sıfıra bərabər ola bilər
Ofsetin yeri Müqavilənin arqumentinin solunda, bu dəyər mənfi olur. Sağ tərəfdə isə Cols arqumenti müsbətdir. Bu misalda, verilən məlumatlar formulla eyni sütunda, beləliklə, bu arqumentin dəyəri sıfırdır.
SUM OFFSET Formülünü Toplam Satış məlumatlarına istifadə etməklə
Bu nümunə işin S sütununda göstərilən gündəlik satış rəqəmləri üçün cəmi qaytarmaq üçün SUM OFFSET formula istifadə edir.
Başlanğıcda formul B6 hücresinə daxil edilmiş və satış məlumatlarını dörd gün təşkil etmişdir.
Sonrakı addım, SUM OFFSET formülünü beşinci günün satış həddi üçün yer açmaq üçün bir sıra aşağı hərəkət etməkdir.
Bu, formulun 7 nöqtəsinə qədər hərəkət edən yeni bir sıra 6 daxil edilərək həyata keçirilir.
Hərəkətin nəticəsi olaraq, Excel avtomatik olaraq B7 hücresinə Müqavilənin arqumentini avtomatik olaraq yeniləyir və formulla toplanan aralığa B6 hücresini əlavə edir.
SUM OFFSET Formula daxil
- Formula nəticələrinin əvvəlcə nümayiş ediləcəyi yer olan B6 hücresinə basın.
- Şerit menyusunun Formüller sekmesini basın.
- Funksiya açılan siyahı açmaq üçün lentdən Math & Trig seçin.
- Fəaliyyətin informasiya qutusunu yetişdirmək üçün siyahıda SUM düyməsini basın.
- Əlaqə qutusunda, Number1 satırına basın.
- Bu hücrenin başvurusunu informasiya qutusuna daxil etmək üçün B2 hücresinə vurun. Bu yer formula üçün statik son nöqtəsidir;
- Əlaqə qutusunda, Number2 xəttini basın.
- Aşağıdakı OFFSET funksiyasını daxil edin: OFFSET (B6, -1,0) formula üçün dinamik son nöqtəni meydana gətirir.
- Funksiyanı tamamlamaq və dialoq qutusunu bağlamaq üçün OK düyməsini basın.
Ümumi $ 5679.15 B7 hücresində görünür.
B3 hücresini tıkladığınızda, tam funksiya = SUM (B2: OFFSET (B6, -1,0)) iş səhifəsinin yuxarıdakı formula barında görünür.
Növbəti gün satış məlumatlarını əlavə etmək
Gələn gün satış məlumatlarını əlavə etmək üçün:
- Kontekst menyusunu açmaq üçün satır 6 satır başlığını sağ basın.
- Menyuda iş səhifəsinə yeni bir satır əlavə etmək üçün Əlavə et vurun.
- Nəticədə, SUM OFFSET formula B7 hüceyrəsinə doğru hərəkət edir və satır 6 boşdur.
- A6 hücresinə vurun.
- Beşinci gün üçün satışın ümumi sayının girildiyini göstərmək üçün 5 nömrə daxil edin.
- B6 hücresinə basın.
- $ 1458.25 nömrəsini daxil edin və Klaviaturada Enter düyməsini basın.
Cell B7 yeni cəmi 7137,40 dolları yeniləyir.
B7 hücresini tıkladığınızda, yenilənmiş formula = SUM (B2: OFFSET (B7, -1,0)) formula barında görünür.
Qeyd : OFFSET funksiyasında iki nümunə mövcuddur: height və width .
Bu arqumentlər OFFSET funksiyasını çıxış növünü çox sayda yüksək və çox sütun genişliyi ilə ifadə etmək üçün istifadə edilə bilər.
Bu arqumentləri atlayaraq, funksiyanı, default olaraq, bu nümunədə bir satır yüksək və bir sütun genişliyinə malik olan Referans arqumentinin hündürlüyü və genişliyini istifadə edir.