مغايرت گيري در اکسل به زبان ساده

مغایرت گیری در اکسل هرگونه ناهماهنگی را در فایل‌های بزرگ در لحظه برای شما آشکار می‌کند، بدون اینکه نیاز به جستجوی زمانبر و خسته کننده‌ی دستی داشته باشید. معمولا برای مغایرت گیری در اکسل از فیلترها، conditional formatting، countif، Data Validation و vlookup استفاده می‌شود. در ادامه نحوه استفاده از این توابع را با یکدیگر بررسی می‌کنیم.

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

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

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

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

مقایسه مغایرت دو ستون در اکسل 

یکی از راهکارهای ابتدایی برای مغایرت گیری در اکسل، مقایسه دو ستون است. برای این منظور کافی است یک ستون جدید در فایل اکسل انتخاب کنید. سپس در اولین ردیف، دستور زیر را وارد نمایید: 
=A2=B2 
با Drag کردن و پایین کشیدنِ سلولی که فرمول در آن نوشته شده است، می‌توانید آن را برای تک تک ردیف‌ها کپی کنید. پس از پایان کار، ستون‌های جدید حاوی سطر‌هایی از True (تکراری) و False (غیر تکراری) خواهد بود. با بررسی تصویر زیر می‌توانید متوجه این نوع مقایسه و مغایرت گیری در اکسل شوید..

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

مغایرت گیری در اکسل با conditional formatting 

با استفاده از چند راهکار مختلف می‌توان مغایرت گیری در اکسل را به واسطه conditional formatting انجام داد و نسبت به روش مقایسه دستی دو ستون در اکسل به کار سرعت بخشید.
•    تعیین خودکار داده‌های تکراری: جهت مغایرت گیری در اکسل ستون‌های مد نظرتان را انتخاب کرده، از تب Home گزینه conditional formatting را انتخاب نموده و سپس از Highlight Cells Rules گزینه Duplicate Values را Select کنید. در پنجره‌ای که باز می‌شود، گزینه Duplicate را انتخاب کنید. در انتها داده‌های تکراری برای شما مشخص می‌شود.
•    تعیین خودکار داده‌های غیر تکراری: برای تعیین داده‌های غیر تکراری مسیر مرحله قبل را طی نموده و به جای انتخاب گزینه Duplicate در پنجره‌ی Duplicate Values گزینه Unique را انتخاب کنید تا داده‌های یونیک به شما نمایش داده شوند.
•    تعیین خودکار ردیف‌های حاوی داده تکراری: برای مغایرت گیری در اکسل در بخش conditional formatting گزینه New Rule را انتخاب کنید. بعد از انتخاب Select a Rule Type و Use a formula to determine which cells to format باید عبارت =$A2=$B2 را در بخش format values where this formula is true وارد کنید. به جای A و B می‌توانید نام ستون‌هایی که قصد مقایسه میان آنها را دارید وارد نمایید. برای تعیین داده‌های غیر تکراری می‌توانید دستور =$A2<>$B2 را وارد کنید. با انتخاب دکمه Format و تعیین رنگ در تب Fill این امکان را خواهید داشت تا رنگ عبارت‌های تکراری یا یونیک را به سلیقه خود مشخص نمایید.

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

تابع COUNTIF در اکسل برای شمارش تعداد دفعاتی که یک شرط در محدوده‌ای از سلول‌ها برقرار باشد یا برای محاسبه تعداد دفعات تکرار آن استفاده می‌شود. برای مغایرت گیری در اکسل با استفاده از countif می‌توانیم دو یا چند ستون را با یکدیگر مقایسه کنیم. سپس در هر ردیف که مقادیر مشابه یافت شد، با عددی بزرگتر از 0 مواجه می‌شویم. برای اجرای countif در تب Home گزینه‌ی conditional formatting و سپس گزینه New rule را انتخاب نمایید. در صفحه‌ای که مقابل‌تان باز می‌شود گزینه Use a formula to determine which cells to format را select کنید. فرم دستور countif به صورت زیر است:
COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)
در این فرمول برای مغایرت گیری در اکسل، مقدار دهی criteria_range1 و criteria1 اجباری است. مقدار دهی به criteria_range2 و  criteria 2 کاملا اختیاری است. criteria_range1 اولین محدوده‌ای را تعریف می‌کند که شرط اولِ criteria1 باید برای آن اعمال شود. شرط می‌تواند به صورت عدد، رشته متن یا یک تابع در اکسل تنظیم شود. 

برای مثال فرض کنید دو ستون شامل شماره‌های فاکتور دارید (ستون A و ستون B) و می‌خواهید بررسی کنید که آیا شماره‌های موجود در ستون A در ستون B هم وجود دارند یا خیر. ستون A شامل شماره فاکتورهای لیست اول و ستون B شامل شماره فاکتورهای لیست دوم می‌باشد. برای مغایرت گیری می‌توانید از فرمول زیر استفاده کنید:
=IF(COUNTIF(B:B, A2) > 0, "Exists", "Not Found")
در این دستور، تابع COUNTIF بررسی می‌کند که آیا مقدار سلول A2 در محدوده ستون B وجود دارد یا خیر. اگر نتیجه‌ی دستور نوشته شده بزرگ‌تر از صفر باشد، به این معنی است که مقدار پیدا شده است و "Exists" نمایش داده می‌شود. در غیر این صورت، "Not Found" برگردانده می‌شود. 

حالا به روش گفته شده در ویدئوی مغایرت گیری با تابع  COUNTIFزیر توجه فرمایید:

جلوگیری از مغایرت در داده‌ها با Data Validation 

برای مغایرت گیری در اکسل با استفاده از Data Validation که به آن اعتبار سنجی داده‌ها گفته می‌شود باید در ابتدا سلول‌ها یا ستون‌های مد نظر خود را انتخاب نمایید. در تب Data می‌توانید گزینه Data Validation را به راحتی بیابید. آن را انتخاب کرده و تب Setting را باز کنید. در این بخش می‌توانید شروط مدنظر خود را جهت مغایرت گیری تعیین کنید. شروط قابل تعیین شامل موارد زیر هستند.
•    Time: پذیرش زمان در بازه مشخص.
•    Date: پذیرش تاریخ در بازه مشخص.
•    Whole number: پذیرش اعداد کامل در بازه مشخص.
•    Decimal: پذیرش اعداد اعشاری در بازه مشخص.
•    List: پذیرش داده‌ها در قالب لیست از قبل مشخص شده.
•    Custom: تعیین شرط دلخواه از طریق شروط موجود.
 

شروط دستور Data Validation

بر اساس نوع شرط انتخابی برای مغایرت گیری در اکسل می‌بایست پارامتر تعیین کنید. من باب مثال، بعد از انتخاب Whole number باید کمترین و بیشترین بازه عددی را تعیین کنید تا داده‌ها طبق این پارامتر اعتبار سنجی شوند. به جز تعیین پارامتر امکان مشخص نمودن پیغام خطا برای داده‌هایی که از شرط شما پیروی نمی‌کنند را دارید تا بتوانید از طریق آن مغایرت‌ها را به راحتی پیدا کنید. برای تعیین پیغام خطا می‌توانید از مسیر روبرو استفاده کنید:
Data> Data Validation> Input Message> Error Alert. 

مغایرت گیری در اکسل با ویلوکاپ vlookup

تابع VLOOKUP در اکسل برای جستجوی مقداری خاص در یک ستون و بازگرداندن مقدار مربوط به آن از ستونی دیگر استفاده می‌شود. این تابع زمانی که می‌خواهید داده‌های یک جدول را با داده‌های یک جدول دیگر تطبیق دهید، بسیار کاربردی است. ساختار کلی تابع VLOOKUP به شکل زیر است:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
در این تابع، lookup_value مقداری است که می‌خواهید جستجو کنید. table_array محدوده‌ای از سلول‌ها هستند که قصد دارید جستجو در آنها انجام شود و col_index_num شماره ستون از محدوده‌ای است که قصد دارید داده‌ای از آن بازیابی شود. مقادیر ذکر شده برای استفاده در تابع vlookup حتما می‌بایست مقدار دهی شوند. تعیین مقدار برای range_lookup که صرفا True یا False می‌پذیرد کاملا اختیاری است. 
برای مغایرت گیری در اکسل و مقایسه داده‌های دو جدول از VLOOKUP استفاده می‌شود. سپس بر اساس نتایج، تطابق داده‌ها قابل بررسی است. مثلا فرض کنید که یک ستون حاوی شماره فاکتور دارید و می‌خواهید بررسی کنید که آیا مشابه این شماره‌ها در جدول دوم موجود است یا خیر. از تابع VLOOKUP برای جستجوی هر شماره در جدول دوم استفاده کنید. برای این منظور یک ستون را به عنوان ستونِ نتیجه در نظر بگیرید، آن را انتخاب کنید و سپس در بخش نوار فرمول اکسل دکمه Fx را انتخاب کنید.

لیست کشویی Select a category را باز کرده و گزینه All را انتخاب نمایید. از بین توابع VLOOKUP را بیابید. در پنجره جدیدی که مقابل‌تان باز می‌شود، در بخش lookup_value ستون اول و در بخش table_array محدوده مورد نظر برای جستجو را تعیین کنید. این محدوده کلیه سلول‌هایی از دو ستون هستند که قصد دارید جستجو روی آنها انجام شود. برای بخش col_index_num عدد 1 یا ستونی که قرار است داده از آن بازیابی شود را انتخاب کنید.  

استفاده از تابع vlookup
در صورت وجود تطابق مقدار مربوطه را دریافت می‌کنید، در غیر این صورت اکسل خطای #N/A را برمی‌گرداند که نشان‌ دهنده وجود مغایرت است. یک نمونه از مغایرت گیری در اکسل به کمک VLOOKUP را در ادامه مشاهده می‌کنید:
=IF(ISNA(VLOOKUP(A2, B:B, 1, FALSE)), "Not Found", "Found")
دستور بالا بررسی می‌کند که آیا مقدار سلول A2 در ستون B وجود دارد یا خیر. اگر مقدار مورد نظر وجود داشته باشد،"Found" و در غیر این صورت "Not Found" نمایش داده می‌شود.
    نکته: فرمول نویسی و تسلط به ساختار توابع از جمله دروس ضروری دوره آموزش اکسل پیشرفته محسوب می‌شوند. در صورتی که هنوز با توابع پرکاربرد و ساختارشان آشنا نیستید بهتر است در این دوره شرکت کنید.  

حالا به روش گفته شده در ویدئوی مغایرت گیری با تابع  vlookupزیر توجه فرمایید:

جمع بندی نهایی مغایرت گیری در اکسل

در این مطلب روش‌های مختلفی برای مغایرت گیری و رفع اختلال‌های موجود در یک فایل اکسل ارائه شد. به جز روش‌های مذکور شما می‌توانید از سایر توابع و قالب‌های شرطی همچون Match و روش فیلتر کردن ستون‌ها برای یافتن ناهماهنگی‌ها استفاده کنید. برای یادگیری نحوه کار کردن با اکسل و توابع شرطی آن امتحان نمودن روش‌های جدید و تمرین زیاد را در اولویت قرار دهید. 
برای درک بهتر نحوه مغایرت گیری در اکسل می‌توانید ویدیوی آموزشی زیر را مشاهده نمایید تا مطالب را بهتر بیاموزید.

پاسخ به چند سوال مغایرت گیری

•    چگونه می‌توانم مغایرت گیری در اکسل بین دو لیست را انجام دهم؟
برای مغایرت‌گیری بین دو لیست می‌توانید از توابع VLOOKUP، MATCH، یا COUNTIF استفاده کنید. برای مثال، اگر می‌خواهید بررسی کنید که آیا مقادیر موجود در لیست A در لیست B نیز وجود دارند یا خیر، می‌توانید از تابع COUNTIF استفاده کنید. فرمول زیر می‌تواند بررسی کند که آیا هر مقدار از لیست A در لیست B وجود دارد یا خیر:
=IF(COUNTIF(B:B, A2) > 0, "Exists", "Not Found")
•    چطور می‌توانم تفاوت بین مقادیر دو ستون را بررسی کنم؟
جهت بررسی تفاوت بین مقادیر دو ستون می‌توانید از تابع IF همراه با VLOOKUP یا MATCH استفاده کنید. به عنوان مثال، فرض کنید دو ستون از داده‌های مشابه دارید و می‌خواهید بررسی کنید که آیا مقادیر آنها با هم برابر هستند یا خیر. فرمول زیر بررسی می‌کند که آیا مقدار سلول‌های ستون A با مقادیر ستون B برابر است یا خیر:
=IF(A2=B2, "Match", "Mismatch")
•    چگونه خطاهای مغایرت را در اکسل مدیریت کنم؟
زمانی که از تابع VLOOKUP برای مغایرت‌ گیری استفاده می‌کنید، ممکن است با خطای #N/A مواجه شوید که به این معناست که مقداری در لیست دوم یافت نشده است. برای مدیریت این خطا می‌توانید از تابع IFERROR  استفاده کنید.