سبد خرید
0

هیچ محصولی در سبد خرید نیست.

تمام دسته ها
  • تمام دسته ها
  • Power BI
  • Power Pivot
  • Power Query
  • ابزارها
  • افزونه ها
  • اکسل مدیا
  • توابع اکسل
  • دسته‌بندی نشده
  • دیده بان
  • گوگل شیت
  • مسائل کاربردی
  • معرفی کتاب
  • نمودار ها
  • وی بی - ماکرو

مغایرتگیری در اکسل

مغایرت گیری
نظر شما در مورد این آموزش

صورت مغایرت بانکی

صورت مغایرت بانکی در واقع نوعی ابزار حسابداری جهت مقایسه و بررسی ورود و خروج پول در دفاتر حسابداری سازمان با حساب های بانکی است. هدف اصلی، از بین بردن مغایرت مالی بین موجودی حساب بانکی و موجودی ثبت شده در سیستم حسابداری یک سازمان است. سازمان‌ها و شرکت‌ها معاملات مالی مختلفی را از جمله واریز و برداشت پول، صدور و وصول چک و تراکنش‌های بانکی انجام می دهند. این تراکنش‌ها در سیستم حسابداری سازمان ثبت می‌شوند. همچنین، تراکنش‌های مشابهی نیز در حساب های بانکی سازمان ثبت می‌شوند. مغایرتگیری بانکی یکی از کارهای پرتکرار و مهم بشمار میره که اکثر حسابدارها و کارشناسان مالی با این مسئله سر و کار دارن. در این مقاله میخواهیم ۲ حالت برای مغایرتگیری در اکسل ارائه کنیم.

حالت اول

یکی از حالت های مغایرتگیری بانکی اینطوری هست که میخوایم ببینیم یک عدد حاصل جمع چه اعدادی است؟ در واقع لیستی از یک سری اعداد داریم و یک عدد کلی، حالا میخوایم بدونیم این عدد کلی، حاصل جمع کدوم یکی از اعداد موجود هست.

برای حل این مسئله میخوایم از ابزار Solver Add Ins استفاده کنیم. ابزار Solver همونطور که قبلا هم توضیح دادیم، افزونه حل مسائل بهینه سازی است که بصورت پیشفرض در اکسل وجود داره. برای فعالسازی این افزونه کافیه از تب Developer/ Excel AddIns از پنجره نمایش داده شده تیک Solver AddIns رو بزنیم و ok کنیم. با این کار گزینه Solver به تب Data اضافه میشه. جهت مشاهده توضیحات بیشتر در مورد نحوه فعالسازی و کار کردن با این افزونه، مقاله مربوط به Solver و مثال های ارائه شده رو به دقت مطالعه کنید.

نمونه داده های توضیح داده شده مطابق با شکل ۱ در شیت اکسل وارد شده اند. در ستون A تا ردیف ۳۸ اعدادی وارد شده اند. همچنین در سلول E2 عددی وجود داره که حاصل جمع چند عدد از داده های ستون A هست و ما میخوایم ببینیم کدوم اعداد حاصل جمعشون برابر شده با ۲۷۳۳.

نمونه داده های موجود برای مدلسازی

شکل ۱ – نمونه داده های موجود برای مدلسازی

حالا برای حل این موضوع ابتدا باید مسئله رو به یک مدل تبدیل کنیم. اگر بیایم و چند داده فرضی رو بصورت تصادفی انتخاب کنیم و در سلول مقابل اونها ۱ بذاریم. میتونیم با تابع Sumproduct به جمع اون چند عدد برسیم. برای واضح شدن موضوع به شکل ۲ دقت کنید: چند عدد تصادفی رو انتخاب کرده و جلوش عدد ۱ رو تایپ میکنیم. در سلول دیگه ای با تابع Sumproduct جمع اعداد انتخاب شده رو حساب میکنیم. سه عدد ۴۰۸، ۲۲۲ و ۱۵۳ که انتخاب شدن، وقتی در ۱ ضرب بشن و با هم جمع بشن، حاصل جمع این سه عدد مشخص میشه (مابقی اعداد هم که در صفر ضرب میشن و عملا در نتیجه جمع اثری ندارند). حالا فرض کنید هر بار بخوایم دستی این ۱ ها رو جابجا کنیم تا ببینیم بالاخره کدوم حاصل جمع کدوم اعداد میشه عدد مورد نظر ما یعنی ۲۷۳۳. اما اینکه  دستی بخوایم این اعداد رو جابجا کنیم، خیلی زمانبر و غیرحرفه ای خواهد بود. پس از سیستم میخوایم که مدلی که ساختیم رو حل کنه. مدلسازی مسئله جهت انجام مغایرت گیری

شکل ۲ – مدلسازی مسئله جهت انجام مغایرت گیری

پس در واقع ما با این کار مدلی ساختیم که باید با یک ابزار حلش کنیم. تابع هدف مدل، متغیرها و محدودیت ها به شرح زیر است:

تابع هدف

تابع Sumproduct تابع هدف ماست که در واقع باید مقدارش برابر بشه با ۲۷۳۳.

متغیرها

متغیرهای این مدل، محدوده B2:B40 هست. در واقع این متغیرها با مقدار صفر و یک در تابع هدف (جمع نهایی) اثر میگذارند.

محدودیت ها

محدودیت های این مدل هم، صفر و یک بودن مقدار متغیرها خواهد بود. در واقع نمیخوایم محدوده B2:B40 مقداری جز مقدار صفر و یک بگیره. چرا که باید بتونه مستقیم در تابع هدف مدل اثر بذاره. پس حالا مدلی داریم که تابع هدف آن حاصل جمع یک سری عدد رو حساب میکنه که میخوایم نتیجه این حاصل جمع برابر با مقدار دلخواه ما بشه. میدونیم که برای حل مدل های خطی میتونیم از  افزونهSolver Add Ins استفاده کنیم. در ادامه میخوایم مدل ایجاد شده رو وارد افزونه کنیم و در نهایت مدل رو حل کنیم و ببینیم کدوم اعداد حاصل جمعشون برابر با ۲۷۳۳ خواهد بود. وارد کردن مشخصات مدل در افزونه Solver

شکل ۳ – وارد کردن مشخصات مدل مغایرت گیری در افزونه Solver

همونطور که در شکل ۳ نمایش داده شده، تابع هدف، همون سلول شامل تابع Sumproduct هست که باید به مقدار ۲۷۳۳ برسه. که این کار رو با تغییر متغیر ها یعنی محدوده B2:B40 انجام میده و با حفظ این فرض که مقادیر متغیر باید یا صفر باشند یا یک و هیچ مقدار دیگه ای نمیتونن داشته باشن.

نکته: جهت مشاهده نحوه تعیین متغیرها، محدویدت ها و تابع هدف، مقاله مربوط به معرفی افزونه Solver Add Ins رو مطالعه کنید.

  حالا با توجه به اینکه مدل خطی است، روش حل رو Simplex LP انتخاب میکنیم و حالا فقط کافیه روی Solve کلیک کنیم تا مدل حل بشه. ستون B رو پاک میکنیم سپس روی Solve کلیک میکنیم و نتیجه مطابق زیر خواهد بود: جواب مدل

شکل ۴- جواب مدل

جلوی اعداد ۴۷۹، ۴۹۹، ۱۳۷، ۳۸۴، ۲۴۵، ۴۹۴ و ۴۹۵ عدد ۱ ثبت شده. اگر جمع این اعداد رو حساب کنیم، نتیجه برابر با ۲۷۳۳ خواهد بود. در واقع وقتی Sumproduct محاسبات انجام میده. این اعداد رو در یک و بقیه رو در صفر ضرب میکنه و در نهایت با هم جمع میکنه. در ستون B با استفاده از Conditional Formatting شرطی گذاشتیم که سلول های بزرگتر از صفر رو رنگی کنه که متغیرهایی که مقدار میگیرند، متمایز بشن.

Home > Conditional Formatting > Highlight Cells Rules > Greater Than

نکته خیلی مهم: افزونه Solver Add Ins برای تعداد متغیر محدودیت داره و این محدودیت برابر هست با ۲۰۰ متغیر. این نکته به این معنی هست که در حل همچین مسئله ای، اگر بخوایم از این روش استفاده کنیم، نباید تعداد اعداد موجود، بیش از ۲۰۰ باشه. در واقع متغیرهایی که در این مسئله ۳۸ تا بود، در نهایت میتونه ۲۰۰ تا باشه. پس برای استفاده از این افزونه حتما باید به این محدودیت دقت داشته باشیم.

  خب حالا اگه بیش از ۲۰۰ متغیر داشته باشیم باید چکار کنیم؟ برای این کار باید از یکی از روش های زیر استفاده کنیم:

  • خرید افزونه Advance Solver . (سایت https://www.solver.com)
  • بهینه سازی مدل و کمتر کردن تعداد متغیرها
  • قطعه قطعه کردن مسئله به بخش های کوچکتر و حل آن
  • در نهایت اگر موارد بالا نشد، باید ابزار حل مسئله رو عوض کرد و از نرم افزارهای دیگه مثل MATLAB استفاده کرد.

حالت دوم

در این حالت باید بین دو جدول موجود (بانک و دفتر) مقایسه انجام بدیم و داده هایی که در هر دو جدول وجود دارن رو مشخص کنیم که راه حل های مختلفی میتونه داشته باشه، در اینجا از COnditionl Formatting و فرمول نویسی برای داده های تکراری چند متغیره استفاده میکنیم.

در CF امکان مشخص کردن داده های تکررای بصورت آماده وجود داره و نیازی به فرمول نویسی نیست. اما این گزینه هر داده تکراری رو مشخص میکنه. در حالیکه ما در این حالت میخوایم که داده هایی که در سه ستون (مجموعا) در جدول دوم وجود دارن مشخص بشن. (چیزی شبیه به منطق داده تکاری در ابزار Remove Duplicate). که برای این مسئله باید بریم سراغ فرمول نویسی توی این ابزار که از تابع Countifs برای شمردن داده مورد نظر استفاده میکنیم.

 

دانلود فایل نمونه این آموزش

فایل استفاده شده برای حل مسئله مغایرت گیری رو دانلود کنید و با تغییر مقادیر، سعی کنید مسئله رو به شکل های مختلف حل کنید.

مشاهده ویدئو مغایرت گیری در اکسل

در ویدئو زیر هم روش حل این مسئله رو با اعداد دیگه ای شرح دادیم:

 

کلیدواژه : تابع Sumproduct
133

من سامان چراغی هستم. دانش آموخته مقطع فوق لیسانس دانشگاه تربیت مدرس در رشته مهندسی صنایع. از سال 1388 اکسل و برنامه نویسی VBA رو به صورت حرفه ای شروع کردم.

دیدگاه کاربران
  • آواتار
    مزرعه ۱۵ اردیبهشت ۱۴۰۳ / ۴:۲۶ ب٫ظ

    اگر چند عدد دیگه جوابشون بشه همون تابع هدف نشون نمیده؟ یعنی ممکنه اعداد دیگه رو هم جمع کنیم به تابع هدف برسیم

    • آواتار
      حسنا خاکزاد ۱۶ اردیبهشت ۱۴۰۳ / ۶:۴۴ ب٫ظ

      بله
      یکیشو میده
      ممکنه

  • محمدجواد محمدی ۲۷ مرداد ۱۴۰۲ / ۱۲:۰۹ ب٫ظ

    من قسمت محدودیت یا فرضیات مسئله اونجا که مساوی با binray شد متوجه نشدم اون از کجا اومد

    • آواتار
      حسنا خاکزاد ۲۷ مرداد ۱۴۰۲ / ۵:۵۰ ب٫ظ

      binary یعنی صفر یا یک
      ما میخوایم متغیرهامون یا صفر باشن یا یک
      یعن یاگع جززو اون اعداد باشن ۱ اگر نشد صفر

  • سارا ۳۰ خرداد ۱۴۰۲ / ۲:۳۴ ب٫ظ

    سلام خسته نباشید خیلی ممنونم کار من رو راه انداخت

  • میلاد ۱۲ آبان ۱۴۰۰ / ۸:۵۱ ق٫ظ

    سلام خسته نباشید اگر ما چند هدف داشته باشیم چطوری باید انجام بدیم؟

    • آواتار
      حسنا خاکزاد ۱۲ آبان ۱۴۰۰ / ۲:۱۶ ب٫ظ

      درود
      یعنی اینکه جند عدد داریم که هر کدوم حاصل یک سری است؟
      اگر اینه، خب هر بار یکیش ر وارد مدل کنید

  • حمید رضا فلاح ۱۸ آذر ۱۳۹۹ / ۹:۲۴ ق٫ظ

    سلام. سایت بسیار خوبی دارید با اطلاعات و دانش فوق العاده مفید و کاربردی. دست مریزاد. سوالی داشت امیدوارم راهنمایی بفرمایید. میخوام حاصلجمع ضرب دو ستون محاسبه کنم با این شرط که در ستون سوم گزینه مورد نظر باشه. یعنی سلولهای نظیر به نظیر دو ستون اول و دوم زمانی در هم ضرب بشه در سلول نظیر در ستون سوم شرط برقرار باشه. سپاس

  • محمد علی عباسب ۲۶ اردیبهشت ۱۳۹۹ / ۱:۴۲ ق٫ظ

    خیلی جالب و کاربردی بود سپاس از لطفتون

    • پیمان ۱۲ شهریور ۱۴۰۲ / ۶:۳۳ ب٫ظ

      سلام ، ببخشید امکان داره ، تعیین کرد اگه اعدادی رو که جمعشون عدد مورد نظرمونه رو پیدا نکرد ، اعدادی رو پیدا کنه که نزدیکترین به حاصل باشه. مثلا اگه عددم ۱۰ باشه مثلا اگه (۳_۲_۵) موجود نبود مثلا (۳_۲-۴) رو که میشه ۹ بهم بده. برام ضروریه لطفا اگه راهی داره ایمیل راهنماییم کنید. ممنون

      • آواتار
        حسنا خاکزاد ۱۳ شهریور ۱۴۰۲ / ۵:۲۴ ب٫ظ

        درود بر شما
        تابع هدف رو بزرگتر یا کوچکتر از اون مقدار قرار بدید بجای مساوی

ارسال دیدگاه

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

توسط
تومان