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

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

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

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

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

روش اول مغایرت گیری در اکسل: استفاده از Conditional Formatting

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

۱- جدول سمت راست را (ترجیحا بدون گرفتن سر ستون ها) انتخاب کنید.

۲- وارد زبانه Home شده و از گروه Styles گزینه Conditional Formatting را انتخاب کنید.

فرمت دهی شرطی برای مغایرت گیری

۳- از منوی باز شده گزینه …New Rule را انتخاب کنید تا به صفحه زیر برسید.

new rule

۴- در صفحه …New Rule گزینه آخر یعنی Use a formula to determine which cells to format را انتخاب کنید تا بتوانید داخل آن فرمول نویسی کنید. البته دادن فرمت را از طریق دکمه …Format فراموش نکنید. در این مرحله فرمول و فرمت را می دهیم:

=COUNTIFS($H$3:$H$9,$B3,$I$3:$I$9,$C3,$J$3:$J$9,$D3)>0

نکته اینکه محدوده ستون H مربوط به تاریخ های جدول اکسل (سمت چپی)، ستون I مربوط به ستون دریافت از جدول سمت چپ و ستون J مربوط به پرداخت های جدول سمت چپی می باشند. با توجه به اینکه فرمول فوق در Cinditional Formatting نوشته شده است در هر مرحله مقادیر موجود در سطر جاری جدول سمت راست را در کل ستون های جدول سمت چپ بطور همزمان شمارش می کند و اگر همگی همزمان موجود باشند سطر جاری از جدول سمت راست به شکل زیر آبی می شود.

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

نتیجه نهایی به شکل زیر خواهد بود:

فرمت دهی شرطی

روش های دیگر مغایرت گیری در اکسل در آینده ارائه خواهند شد. ما را از نظرات خود بهره مند کنید.

پوریا بغدادی

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

دیدگاه بگذارید

avatar
  عضویت  
به دوستتان خبر دهید.