جمع کارانه یک بخش با ترکیب توابع Sum و Xlookup در اکسل

در گزارش کارانه ماهانه بیمارستان، معمول ترین شکل جدول این است که هر سطر یک بخش باشد و هر ستون یک ماه؛ یعنی یک جدول پهن با ده ها ستون در طول سال. وقتی کارشناس مالی یا مدیر بخش فقط بخواهد جمع کارانه یک بخش خاص در یک بازه از این جدول را ببیند، فیلتر کردن دستی یا کپی تک تک سلول ها زمان بر و پرخطاست. با ترکیب دو تابع XLOOKUP و SUM، همین جمع با یک فرمول و با وارد کردن فقط کد بخش به دست می آید.

داده نمونه؛ گزارش کارانه ماهانه بخش ها

فرض کنید جدول زیر را داریم؛ ستون اول کد بخش، ستون دوم نام بخش و شش ستون بعدی مبلغ کارانه هر بخش در شش ماه اول سال:

هدف این است که با وارد کردن کد یک بخش (مثلا ۲۰۵ برای مراقبت ویژه ICU)، جمع کارانه آن بخش در کل بازه شش ماهه بدون فیلتر کردن یا نوشتن ۶ فرمول جدا به دست بیاید.

فرمول ترکیبی Sum و Xlookup برای جمع ستون

تابع XLOOKUP با این ساختار کار می کند: XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). معمولا return_array یک ستون تک است و XLOOKUP فقط یک عدد برمی گرداند؛ اما اگر return_array را به جای یک ستون، محدوده چند ستونی (مثلا هر شش ستون ماه) بدهید، XLOOKUP کل آن سطر را به صورت یک آرایه برمی گرداند. اینجاست که SUM وارد می شود؛ وقتی SUM دور XLOOKUP قرار بگیرد، همان آرایه چند عددی را جمع می زند و یک عدد کل تحویل می دهد.

مراحل ساخت فرمول:

1. در یک سلول خالی (مثلا K1)، کد بخش مورد نظر را برای جستجو وارد کنید.
2. در سلول کناری (K2) این فرمول را بنویسید: =SUM(XLOOKUP(K1,A2:A9,C2:H9))
3. در این فرمول، A2:A9 ستون کد بخش (محدوده جستجو) و C2:H9 محدوده شش ستون ماهانه (فروردین تا شهریور) است.
4. XLOOKUP سطر مربوط به کد بخش وارد شده در K1 را در ستون A پیدا می کند و شش مقدار ماهانه همان سطر را به عنوان یک آرایه برمی گرداند.
5. SUM دور آن، این شش عدد را جمع می زند و جمع کارانه شش ماهه همان بخش را نشان می دهد.

با تغییر عدد داخل K1 (مثلا از ۲۰۵ به ۲۰۳)، نتیجه فورا برای بخش جدید به روز می شود؛ بدون دست زدن به فرمول یا فیلتر کردن جدول.

چرا Xlookup و نه فقط Vlookup یا Sumif؟

• برخلاف VLOOKUP که فقط می تواند از چپ به راست جستجو کند و ستون نتیجه باید سمت راست ستون جستجو باشد، XLOOKUP محدودیت جهت ندارد و هر دو ستون کد و مبلغ می توانند در هر ترتیبی باشند.
• XLOOKUP به طور پیش فرض تطابق دقیق (exact match) انجام می دهد و نیازی به آرگومان false در انتهای فرمول (مثل VLOOKUP) نیست.
• SUMIF/SUMIFS برای جمع یک ستون تک بر اساس یک شرط عالی است؛ اما وقتی باید کل یک سطر چند ستونی (چند ماه کنار هم) با یک فرمول جمع شود، ترکیب SUM و XLOOKUP ساده تر و کوتاه تر از نوشتن SUMIFS جداگانه برای هر ماه است.

تکنیک پیشرفته؛ جمع بین دو ردیف با دو Xlookup

یک کاربرد دیگر ترکیب SUM و XLOOKUP، جمع کردن یک بلوک از ردیف های پیوسته در یک لیست تک ستونی است؛ مثلا در لیست ردیف های هزینه که بر اساس کد حساب مرتب شده و چند ردیف پشت سر هم به یک کد حساب خاص تعلق دارند، اما شماره ردیف دقیق شروع و پایان آن بلوک را نمی دانید یا با اضافه شدن ردیف جدید تغییر می کند.

راه حل، دو بار XLOOKUP روی همان ستون مبلغ است؛ یک بار با آرگومان search_mode=1 (جستجو از بالا، یعنی اولین رخداد کد حساب) و یک بار با search_mode=-1 (جستجو از پایین، یعنی آخرین رخداد همان کد حساب). نتیجه این دو XLOOKUP، دو سر یک بازه پیوسته می شود و SUM بین آن دو سر را جمع می زند:

فرمول کامل این مثال: =SUM(XLOOKUP(612,A2:A11,C2:C11,,0,1):XLOOKUP(612,A2:A11,C2:C11,,0,-1)). XLOOKUP اول اولین ردیف کد حساب ۶۱۲ را پیدا می کند، XLOOKUP دوم آخرین ردیف همان کد را؛ دو نتیجه با علامت : به هم وصل می شوند و یک محدوده پیوسته می سازند که SUM آن را جمع می زند.

نکات کاربردی

• تابع XLOOKUP فقط در Excel نسخه های ۲۰۲۱ به بعد و Microsoft 365 در دسترس است؛ در Excel های قدیمی تر (مثل ۲۰۱۹ و قبل تر) این تابع اصلا وجود ندارد و باید از ترکیب INDEX و MATCH یا SUMIFS استفاده کرد.
• برای جلوگیری از خطای #N/A وقتی کد بخش وارد شده در جدول پیدا نشود، آرگومان چهارم XLOOKUP یعنی if_not_found را پر کنید؛ مثلا =SUM(XLOOKUP(K1,A2:A9,C2:H9,"یافت نشد")).
• اگر جدول کارانه هر ماه ردیف جدید (بخش جدید) می گیرد، محدوده A2:A9 و C2:H9 را به صورت جدول اکسل (Insert > Table) تعریف کنید تا با اضافه شدن ردیف، محدوده فرمول خودکار گسترش پیدا کند.
• همین روش برای جمع مبلغ پرداختی به یک بیمارستان خاص در یک گزارش کارانه چند صفحه ای دانشگاهی، یا جمع دستمزد یک گروه شغلی خاص در لیست حقوق ماهانه هم قابل استفاده است؛ کافی است ستون کد و محدوده چند ستونی مقادیر را متناسب با آن گزارش عوض کنید.
• برای جمع شرطی روی بیش از یک شرط (مثلا هم کد بخش و هم سال مالی)، می توان چند XLOOKUP تودرتو نوشت یا در کنار آن از SUMIFS با چند شرط استفاده کرد؛ انتخاب بین این دو به ساختار دقیق گزارش بستگی دارد.

📄 دانلود فایل اکسل نمونه «جمع کارانه با ترکیب Sum و Xlookup» — شامل جدول گزارش کارانه ماهانه با فرمول زنده SUM(XLOOKUP()) و یک برگه جداگانه با تکنیک پیشرفته جمع بلوک کد حساب با دو XLOOKUP، برای تمرین گام به گام.

منبع : سایت حسابداری بیمارستان
کانال تلگرام @hospitalaccounting