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

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

فرمول ها و ابزارهایی که در مغایرت گیری استفاده می شوند

1. تابع Vlookup برای جستجو بین داده ها.
2. تابع Match که جایگاه / موقعیت یک ابزار را مشخص می کند.
3. تابع Exact که متن ها را با حساسیت به حروف بزرگ و کوچک مقایسه می کند.
4. فرمت دهی شرطی به وسیله  Conditional Formating
5. فرمول Countif و Countifs که داده های مورد نظر ما را در هر محدوده ای شمارش می کنند.

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

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

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

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

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

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

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

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

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

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

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