Excel SUM va OFFSET formulalar

Ma'lumotlarni dinamik intervallarni uchun jamuljamlikni topish uchun SUM va OFFSET dan foydalaning

Agar sizning Excel sahifangiz o'zgaruvchan hujayra qatoriga asoslangan hisob-kitoblarni o'z ichiga oladigan bo'lsa, SUM OFFSET funktsiyasida SUM va OFFSET funksiyalarini birgalikda birgalikda hisob-kitoblarni dolzarbligini saqlash vazifasini soddalashtiradi.

SUM va OFFSET funktsiyalari bilan dinamik intervalli yaratish

Agar doimiy ravishda o'zgarib turadigan bir necha vaqt uchun hisob-kitoblardan foydalansangiz - masalan, oylik savdolar hajmi - OFFSET funktsiyasi har kuni sotish raqamlari qo'shilsa o'zgaruvchan dinamik intervalni o'rnatishga imkon beradi.

O'z-o'zidan, SUM funktsiyasi odatda to'planadigan intervalgacha kiritilgan ma'lumotlarni yangi hujayralarga moslashtirishi mumkin.

Funktsiyaning mavjud bo'lgan xujayrasiga ma'lumotlar kiritilganda bir istisno shakllanadi.

Ushbu maqolada keltirilgan misol tasvirida har bir kun uchun yangi savdo ko'rsatkichlari ro'yxatning pastki qismiga qo'shiladi va bu ma'lumotlar jami yangi ma'lumotlar qo'shilsa, jami bitta kamerani doimiy ravishda pastga aylantirishga majbur qiladi.

Agar SUM funktsiyasi o'z-o'zidan ma'lumotlar yig'ish uchun ishlatilgan bo'lsa, har bir yangi ma'lumotlar qo'shilganda funktsiya argumenti sifatida ishlatiladigan hujayra diapazonini o'zgartirish kerak bo'ladi.

Biroq, SUM va OFFSET funktsiyalarini birgalikda ishlatib, jami oraliq dinamik bo'ladi. Boshqacha aytganda, bu ma'lumotlar yangi hujayralarni joylashtirish uchun o'zgaradi. Ma'lumotlarning yangi hujayralarining qo'shilishi muammolarni keltirib chiqarmaydi, chunki intervalli har bir yangi hujayra qo'shilsa, moslashadi.

Sintaksis va argumentlar

Ushbu maqolada keltirilgan tasvirni ushbu qo'llanmada ko'rib chiqing.

Ushbu formulada SUM funktsiyasi argument sifatida berilgan ma'lumotlar doirasini jamlash uchun ishlatiladi. Ushbu intervalli uchun boshlang'ich nuqtasi statiktir va formuladan yig'iladigan birinchi raqamga hujayra mos yozuvi sifatida aniqlanadi.

OFFSET funktsiyasi SUM funktsiyasi ichida joylashgan bo'lib, formulalar tomonidan jamlangan ma'lumotlarga dinamik yakunlovchi nuqtani yaratish uchun ishlatiladi. Bu nuqta nuqta nuqtasini formulaning joylashuvidan yuqori bo'lgan bitta xonaga o'rnatish orqali amalga oshiriladi.

Formulaning sintaksisi :

= Sum (KOffice boshlash: OFFSET (Reference, Rows, Cols))

Range Start - (to'ldirilishi kerak) SUM funktsiyasi tomonidan jamlanadigan hujayra diapazoni uchun boshlang'ich nuqtasi. Misol tasvirida bu B2 uyasi.

Yo'naltiruvchi - (talab qilinadi) oraliqning so'nggi nuqtasini hisoblash uchun foydalaniladigan uyali ma'lumotnoma ko'plab qatorlar va ustunlar joylashgan. Misol rasmida, Reference argumenti formulalar uchun hujayra mos yozuvi bo'lib, chunki biz doimo intervalli formulaning ustidagi bitta xonani tugatishini xohlaymiz.

Satrlar - (ofsetni hisoblashda ishlatiladigan Yo'naltiruvchi mustaqil o'zgaruvchining yuqorisida yoki pastda qatorlar soni). Bu qiymat musbat, salbiy yoki nolga teng bo'lishi mumkin.

Ofsetning joylashuvi Yo'naltiruvchi mustaqil o'zgaruvchining yuqorisida bo'lsa, bu qiymat salbiy hisoblanadi. Agar u quyida bo'lsa, qatorlar argumenti ijobiydir. Ofset bir qatorda bo'lsa, bu dalillar nolga teng. Ushbu misolda, ofset boshlang'ich argumenti ustidagi bir satrdan boshlanadi, shuning uchun bu dalil qiymati salbiy (-1) qiymatiga teng.

Cols - ofsetni hisoblashda ishlatiladigan Reference argumentining chap yoki o'ng tomonidagi ustunlar sonini (talab qilinadi). Bu qiymat musbat, salbiy yoki nolga teng bo'lishi mumkin

Ofsetning joylashuvi Reference argumentining chap tomonida bo'lsa, bu qiymat salbiy hisoblanadi. Agar o'ng tomonda bo'lsa, Kollar argumenti ijobiydir. Ushbu misolda, jamlangan ma'lumotlar formulalar bilan bir xil ustunli bo'lib, bu dalillar uchun qiymat nol bo'ladi.

RUM OFFSET formulasini jami Sotuvdagi ma'lumotlariga ishlatish

Ushbu misol, ish varag'ining ustunida keltirilgan kundalik savdo ko'rsatkichlari uchun jami qiymatni qaytarish uchun SUM OFFSET formula ishlatiladi.

Dastlab, formula B6 uyasiga kiritilib, sotuv ma'lumotlarini to'rt kunga etkazdi.

Keyingi qadam beshinchi kunning sotuvga chiqarilish joyiga ega bo'lish uchun RUM OFFSET formulasini bir qator pastga ko'chirishdir.

Buni formulani 7-qatorga ko'chiradigan yangi satr 6 qo'shib amalga oshiriladi.

Ushbu harakat natijasida, Excel avtomatik tarzda B7 hujayrasiga Reference argümanını yangilaydi va formuladan to'plangan qatorga B6 xonasini qo'shadi.

RUM OFFSET formulasini kiritish

1. Formulaning natijalari dastlab ko'rsatiladigan joy bo'lgan B6 uyasiga bosing.
2. Ip menyusidagi Formulalar yorlig'ini bosing.
3. Funktsiya ochiladigan ro'yxatni ochish uchun tasmadan Math & Trig ni tanlang.
4. Funktsiyaning dialog oynasini ochish uchun ro'yxatdagi SUM tugmasini bosing.
5. Muloqot oynasida Number1 liniyasini bosing.
6. Ushbu uyali ma'lumotnomani dialog oynasiga kiritish uchun B2 uyasiga bosing. Ushbu manzil quyidagi formula uchun statik so'nggi nuqta hisoblanadi:
7. Muloqot oynasidagi Number2 liniyasini bosing.
8. Quyidagi OFFSET funktsiyasini kiriting: OFFSET (B6, -1,0) formula uchun dinamik so'nggi nuqta hosil qilish uchun.
9. Funktsiyani bajarish va muloqot qutisini yopish uchun OK ni bosing.

Hammasi bo'lib $ 5679,15 B7 xonasida paydo bo'ladi.

B3 xonasida bosganingizda, butun vazifani = SUM (B2: OFFSET (B6, -1,0)) ish varag'i ustidagi formulada ko'rsatiladi.

Keyingi kunning savdo ma'lumotlarini qo'shish

Keyingi kunning savdo ma'lumotlarini kiritish uchun:

1. Kontekst menyusini ochish uchun 6- satr uchun satr sarlavhasini o'ng tugmasini bosing.
2. Menyuda ish sahifasiga yangi satr qo'shish uchun Qo'shish- ni bosing.
3. Natijada, SUM OFFSET formulasi B7 uyasiga o'tadi va 6-satr endi bo'sh.
4. A6 uyasiga bosing.
5. 5- sonli raqamni kiriting, bunda beshinchi kun uchun savdo miqdori kiritiladi.
6. B6 uyasiga bosing.
7. $ 1458.25 raqamini kiriting va Klaviaturadan Enter ni bosing.

Mobil B7 yangilanishi $ 7137,40ni tashkil qiladi.

B7 xonasiga bosganingizda, yangilangan formula = SUM (B2: OFFSET (B7, -1,0)) formula maydonida paydo bo'ladi.

Eslatma : OFFSET funktsiyasi ikkita ixtiyoriy argumentga ega: bu misolda qoldirilgan balandlik va kenglik .

Ushbu argumentlarni OFFSET funktsiyasini chiqish formati juda ko'p satrlar va shuncha ko'p ustunlar jihatidan aytish uchun ishlatilishi mumkin.

Ushbu argumentlarni qoldirib, funktsiya sukut bo'yicha Yo'naltiruvchi mustaqil o'zgaruvchining balandligi va kengligini ishlatadi, bu misolda bir qator yuqori va bitta ustun kengligidir.