Agar Excel ish varagʻingiz oʻzgaruvchan katakchalar diapazoniga asoslangan hisob-kitoblarni oʻz ichiga olsa, hisob-kitoblarni yangilab turish vazifasini soddalashtirish uchun SUM va OFFSET funksiyalaridan birgalikda SUM OFFSET formulasidan foydalaning.
Ushbu maqoladagi koʻrsatmalar Microsoft 365, Excel 2019, Excel 2016, Excel 2013 va Excel 2010 uchun Excel uchun amal qiladi.
SUM va OFFSET funksiyalari bilan dinamik diapazon yarating
Agar siz doimiy ravishda oʻzgarib turadigan vaqt oraligʻida hisob-kitoblardan foydalansangiz (masalan, oy uchun sotuvlarni aniqlash) har kunlik savdo koʻrsatkichlari qoʻshilganda oʻzgarib turadigan dinamik diapazonni sozlash uchun Excelda OFFSET funksiyasidan foydalaning.
Oʻz-oʻzidan SUM funksiyasi odatda yigʻilayotgan diapazonga yangi maʼlumotlar kataklarini kiritishni oʻz ichiga oladi. Bitta istisno, maʼlumotlar funksiya joylashgan katakka kiritilganda yuzaga keladi.
Quyidagi misolda har bir kun uchun yangi savdo koʻrsatkichlari roʻyxatning pastki qismiga qoʻshiladi, bu esa yangi maʼlumotlar qoʻshilganda jami doimiy ravishda bitta katakcha pastga siljishiga majbur qiladi.
Ushbu qoʻllanmani davom ettirish uchun boʻsh Excel ish varagʻini oching va namuna maʼlumotlarini kiriting. Ish varaqingiz misoldagi kabi formatlanishi shart emas, lekin maʼlumotlarni bir xil kataklarga kiritganingizga ishonch hosil qiling.
Agar maʼlumotlarni jamlash uchun faqat SUM funksiyasidan foydalanilsa, funksiya argumenti sifatida foydalaniladigan hujayralar diapazoni har safar yangi maʼlumotlar qoʻshilganda oʻzgartirilishi kerak boʻladi.
SUM va OFFSET funksiyalaridan birgalikda foydalanilganda jamlangan diapazon dinamik bo'lib, yangi ma'lumotlar kataklarini joylashtirish uchun o'zgaradi. Yangi maʼlumotlar kataklarini qoʻshish muammo tugʻdirmaydi, chunki har bir yangi hujayra qoʻshilganda diapazon oʻzgarib turadi.
Sintaksis va argumentlar
Ushbu formulada SUM funksiyasi argument sifatida taqdim etilgan maʼlumotlar oraligʻini jamlash uchun ishlatiladi. Bu diapazonning boshlanish nuqtasi statik va formula bo‘yicha jami yig‘iladigan birinchi raqamga yacheyka havolasi sifatida aniqlanadi.
OFFSET funksiyasi SUM funksiyasi ichiga joylashtirilgan va formula boʻyicha jami maʼlumotlar oraligʻiga dinamik yakuniy nuqta yaratadi. Bunga diapazonning so‘nggi nuqtasini formula joylashgan joydan bir katakka o‘rnatish orqali erishiladi.
Formula sintaksisi:
=SUM(Range Start:OFFSET(Malumot, Qatorlar, Cols))
Argumentlar:
- Range Start: SUM funksiyasi bilan jamlanadigan hujayralar diapazoni uchun boshlangʻich nuqta. Ushbu misolda boshlang'ich nuqta B2 katakdir.
- Ma'lumotnoma: diapazonning so'nggi nuqtasini hisoblash uchun foydalaniladigan kerakli hujayra havolasi. Misolda, havola argumenti formula uchun yacheyka havolasidir, chunki diapazon formuladan bir katak yuqorida tugaydi.
- Qatrlar: Ofsetni hisoblashda foydalaniladigan havola argumentidan yuqori yoki pastdagi qatorlar soni talab qilinadi. Bu qiymat ijobiy, salbiy yoki nolga o'rnatilishi mumkin. Agar ofset joylashuvi Malumot argumentidan yuqori bo'lsa, qiymat salbiy hisoblanadi. Agar ofset quyida bo'lsa, Rows argumenti ijobiy bo'ladi. Agar ofset bir xil qatorda joylashgan bo'lsa, argument nolga teng. Bu misolda ofset Malumot argumentidan bir qator yuqoridan boshlanadi, shuning uchun argument qiymati manfiy bir (-1).
- Cols: Ofsetni hisoblash uchun foydalaniladigan Malumot argumentining chap yoki o'ng tomonidagi ustunlar soni. Bu qiymat ijobiy, salbiy yoki nolga o'rnatilishi mumkin. Agar ofset joylashuvi Malumot argumentining chap tomonida bo'lsa, bu qiymat manfiy hisoblanadi. Agar ofset o'ngga bo'lsa, Cols argumenti ijobiy bo'ladi. Bu misolda jamlanayotgan maʼlumotlar formula bilan bir xil ustunda joylashgan, shuning uchun bu argument qiymati nolga teng.
Jami savdo ma'lumotlari uchun SUM OFFSET formulasidan foydalaning
Ushbu misolda ishchi varaqning B ustunida sanab oʻtilgan kunlik savdo koʻrsatkichlari uchun jami summani qaytarish uchun SUM OFFSET formulasidan foydalaniladi. Dastlab, formula B6 katakka kiritildi va to'rt kunlik savdo ma'lumotlarini jamladi.
Keyingi qadam beshinchi kunlik sotuvlar summasiga joy ochish uchun SUM OFFSET formulasini bir qator pastga siljitishdir. Bunga formulani 7-qatorga koʻchiradigan yangi 6-qator qoʻshish orqali amalga oshiriladi.
Koʻchirish natijasida Excel havola argumentini B7 yacheykaga avtomatik ravishda yangilaydi va B6 yacheykani formula boʻyicha yigʻilgan diapazonga qoʻshadi.
- B6 katakchani tanlang, bu formula natijalari dastlab koʻrsatiladigan joy.
-
Tasmaning Formulalar yorlig'ini tanlang.
Image -
Tanlang Matematik va trig.
Image -
SUM ni tanlang.
Image - Funktsiya argumentlari muloqot oynasida kursorni 1-raqam matn maydoniga qo'ying.
-
Ish varaqidagi B2 katakchasini dialog oynasiga ushbu katakcha havolasini kiritish uchun tanlang. Bu joy formula uchun statik oxirgi nuqtadir.
Image - Funktsiya argumentlari muloqot oynasida kursorni 2-raqam matn maydoniga qo'ying.
-
OFFSET(B6, -1, 0) kiriting. Ushbu OFFSET funksiyasi formulaning dinamik yakuniy nuqtasini tashkil qiladi.
Image -
Funksiyani bajarish va muloqot oynasini yopish uchun OK ni tanlang. Jami B6 katakchada ko'rsatiladi.
Image
Keyingi kungi savdo ma'lumotlarini qo'shing
Keyingi kungi savdo ma'lumotlarini qo'shish uchun:
- 6-qator uchun qator sarlavhasini oʻng tugmasini bosing.
-
Ishchi varaqga yangi qator kiritish uchun Insert ni tanlang. SUM OFFSET formulasi B7 katakchaga bir qator pastga siljiydi va 6-qator endi bo‘sh.
Image - A6 katakchani tanlang va beshinchi kundagi sotuvlar jami kiritilayotganligini bildirish uchun 5 raqamini kiriting.
-
B6 katakchani tanlang, $1458,25 kiriting, so'ng Enter tugmasini bosing.
Image - Cell B7 yangilanadi jami $7137,40.
B7 katakchani tanlaganingizda, formulalar qatorida yangilangan formula paydo boʻladi.
=SUM(B2:OFSET(B7, -1, 0))
OFSET funksiyasi ikkita ixtiyoriy argumentga ega: Balandlik va Kenglik, bu misolda ishlatilmagan. Bu argumentlar OFFSET funksiyasiga satr va ustunlar soni boʻyicha chiqish shaklini bildiradi.
Ushbu argumentlarni oʻtkazib yuborish orqali funksiya oʻrniga havola argumentining balandligi va kengligidan foydalanadi, bu misolda bir qator balandlikda va bir ustun kengligidan iborat.
