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

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

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

حالت اول

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

برای حل این مسئله میخوایم از ابزار 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

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

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

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

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

Home > Conditional Formatting > Highlight Cells Rules > Greater Than

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

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

حالت دوم

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

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

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

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

مطمئن باشید ایمیل شما فقط در اختیار اکسل پدیا قرار خواهد گرفت و صرفا برای ارسال مطالب مرتبط استفاده خواهد شد.

آدرس ایمیل

گروه تخصصی اکسل پدیا | همراه شما در مسیر یادگیری

بارگذاری...

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

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