مغایرت گیری در اکسل | آموزش ۱۰ روش ساده و کاربردی

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

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

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

سازمان‌ها با استفاده از قابلیت‌های مختلف اکسل می‌توانند مغایرت‌ها را شناسایی، خطاها را اصلاح و سوابق مالی دقیق و قابل اتکایی ایجاد و حفظ کنند.

پیام اسفندیاری

علوم داده

وبینار معرفی مسیر شغلی تحلیل‌گر داده

۴,۰ (۴۵ رای) ۱۵۹۶ نفر ۷۴ ساعت و ۴ دقیقه

رایگان

بیشتر

مغایرت گیری در اکسل چه کاربردی دارد؟

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

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

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

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

کدام روش را انتخاب کنیم؟

• برای مقایسه ساده دو ستون: IF یا COUNTIF
• برای پیدا کردن اطلاعات متناظر: XLOOKUP
• برای نسخه‌های قدیمی‌تر Excel: VLOOKUP
• برای نمایش سریع مغایرت‌ها: Conditional Formatting
• برای مقایسه دستی دو فایل: View Side by Side
• برای فایل‌های بزرگ یا مغایرت‌گیری‌های تکرارشونده: Power Query

روش اول: مقایسه ردیف‌ها برای مغایرت‌گیری دو مجموعه داده

یک ستون جدید ایجاد کنید و عنوان «نتیجه» را برای آن در نظر بگیرید.

به سلول H5 بروید و فرمول زیر را وارد کنید:

=B5=E5

در این فرمول، B5 و E5 به مقدار مربوط به «شرکت ۱» در لیست ۱ و ۲ اشاره دارند. اگر مقدار موجود در B5 و E5 یکسان باشد، نتیجه TRUE و در صورت متفاوت بودن، نتیجه FALSE خواهد بود.

با استفاده از ابزار Fill Handle، فرمول را به سلول‌های پایین‌تر کپی کنید.

نتیجه مقایسه را مطابق تصویر زیر مشاهده کنید.

روش دوم: استفاده از conditional formatting

ستون‌هایی که می‌خواهید مغایرت‌گیری کنید، در این مثال لیست ۱ و لیست ۲ را انتخاب کنید.

در تب Home، روی منوی کشویی Conditional Formatting کلیک کنید.

گزینه Highlight Cells Rules را انتخاب کرده و سپس از فهرست بازشده، روی Duplicate Values کلیک کنید.

با این کار، پنجره Duplicate Values باز می‌شود.

گزینه Unique را انتخاب کنید و برای رنگ سلول و متن، گزینه Light Red Fill with Dark Red Text را در نظر بگیرید. سپس روی OK کلیک کنید.

دوباره گزینه Duplicate Values را انتخاب و این بار Green Fill with Dark Green Text را برای رنگ سلول و متن انتخاب کنید.

در نهایت، نتایج باید مشابه تصویر زیر نمایش داده شوند.

روش سوم: استفاده از فرمول IF

به سلول H5 بروید و فرمول زیر را وارد کنید:

=IF(B5=E5;"TRUE";"FALSE")

در این فرمول، سلول‌های B5 و E5 به مقدار شرکت ۱ در لیست ۱ و ۲ اشاره دارند.

نتیجه باید مشابه تصویر زیر نمایش داده شود.

این فرمول بررسی می‌کند که آیا یک شرط برقرار است یا خیر. اگر شرط TRUE بود، یک مقدار و اگر FALSE باشد، مقدار دیگری را برمی‌گرداند. در اینجا، B5=E5 همان آرگومان logical_test است که بررسی می‌کند آیا مقدار موجود در سلول B5 با مقدار سلول E5 برابر است یا خیر. اگر دو مقدار یکسان باشند، تابع عبارت TRUE را به‌عنوان آرگومان value_if_true برمی‌گرداند؛ در غیر این صورت، عبارت FALSE به‌عنوان آرگومان value_if_false نمایش داده می‌شود.

روش چهارم: استفاده از تابع MATCH

به سلول H5 بروید و فرمول زیر را وارد کنید:

=ISNUMBER(MATCH(F7;$C$5:$C$13;0))

در این فرمول، سلول F5 به مبلغ شرکت ۱ اشاره دارد و محدوده C5:C13 آرایه مربوط به لیست ۱ است. در این حالت بررسی می‌کنیم که مقدار سلول F5 در ستون C5:C13 وجود دارد یا نه.

نتیجه باید مشابه تصویر زیر نمایش داده شود.

تابع MATCH موقعیت نسبی یک مقدار را در یک آرایه پیدا می‌کند و در صورت یافتن مقدار موردنظر، موقعیت آن را برمی‌گرداند. در اینجا، F5 آرگومان lookup_value است که به مقدار محصول شرکت ۱ اشاره دارد. سپس، $C$5:$C$13 آرگومان lookup_array است؛ یعنی محدوده‌ای که اکسل مقدار موردنظر را در آن جست‌وجو می‌کند. در نهایت، ۰ آرگومان اختیاری match_type است که نشان می‌دهد جست‌وجو باید بر اساس تطابق دقیق (Exact Match) انجام شود.
تابع ISNUMBER بررسی می‌کند که آیا مقدار واردشده یک عدد است یا خیر و در صورت عدد بودن TRUE و در غیر این صورت FALSE را برمی‌گرداند. در اینجا، مقدار ۱ به‌عنوان آرگومان value وارد شده است و چون یک عدد است، تابع مقدار TRUE را برمی‌گرداند.

علی سعیدی

علوم داده

کارگاه داستان‌سرایی داده

(کمتر از ۵ رای) ۱۵ نفر نامشخص

بیشتر

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

برای اجرای VLOOKUP مراحل زیر را اجرا کنید.

به سلول H5 بروید و فرمول زیر را وارد کنید:

=VLOOKUP(F5؛$C$5:$C$13؛۱؛FALSE)

در این فرمول، سلول F5 به مقدار قیمت محصول شرکت ۱ اشاره دارد و محدوده C5:C13 آرایه مربوط به لیست ۱ است.

فرمول را به سلول‌های پایین‌تر کپی کنید. نتیجه باید مشابه تصویر زیر باشد.

اگر عدد در ستون اول نباشد خطای NA نشان داده می‌شود و در غیر این صورت عدد متناظر نوشته می‌شود. 
این تابع مقدار موردنظر را در دومین ستون جدول جست‌وجو می‌کند و سپس مقدار موجود در همان ردیف را از ستونی که مشخص کرده‌اید، برمی‌گرداند. در اینجا، F5 آرگومان lookup_value است که مقدار موردنظر برای جست‌وجو را مشخص می‌کند و در محدوده $C$5:$C$13، که آرگومان table_array است، جست‌وجو می‌شود.

سپس، عدد ۱ در آرگومان col_index_num نشان می‌دهد که مقدار موردنظر باید از ستون اول محدوده برگردانده شود. در نهایت، FALSE در آرگومان range_lookup به این معناست که جست‌وجو باید بر اساس تطابق دقیق (Exact Match) انجام شود.

اگر به اول فرمول بالا، تابع ISTEXT اضافه کنید، این تابع بررسی می‌کند که آیا مقدار موردنظر یک رشته متنی است یا خیر و در صورت متنی بودن مقدار TRUE و در غیر این صورت FALSE را برمی‌گرداند. در اینجا، اگر ستون اول هر جدول را در نظر بگیریم شرکت آرگومان value است و چون یک مقدار متنی است، تابع مقدار TRUE را برمی‌گرداند.

روش ششم: استفاده از XLOOKUP

برای انجام XLOOKUP مراحل زیر را انجام دهید.

به سلول H5 بروید و فرمول زیر را وارد کنید:

=IFERROR(IF(C5=XLOOKUP(B5,$E$5:$E$13,$F$5:$F$13),"مطابق","مغایرت"),"در لیست دوم نیست") 

بعد فرمول را تا H13 پایین بکشید.

این تابع شرکت B5 را در جدول دوم پیدامی‌کند. قیمتش را برمی‌دارد و با قیمت C5 مقایسه می‌کند. اگر برابر بود “مطابق”، اگر متفاوت بود “مغایرت”، و اگر شرکت اصلاً پیدا نشد “در لیست دوم نیست” را در ستونی که فرمول نوشته شده نشان می‌دهد.

روش هفتم: استفاده از COUNTIF

فرمول زیر را در سلول H5 وارد کنید:

=IF(COUNTIF(C5:C13;F5:F13)<>0;"True";"False")

در اینجا، تابع COUNTIF تعداد دفعاتی را که یک مقدار یا متن مشخص در یک محدوده وجود دارد، شمارش می‌کند.

سپس کلید Enter را فشار دهید.

تابع COUNTIF بررسی می‌کند که مقادیر موجود در محدوده F5:F12 چند بار در محدوده C5:C12 وجود دارند.

C5:C12 محدوده‌ای است که در آن جست‌وجو انجام می‌شود.

F5:F12 محدوده‌ای است که مقدار یا مقادیری که باید در محدوده اول جست‌وجو شوند.

اگر یک یا چند مقدار از محدوده F5:F12 در محدوده C5:C12 پیدا شوند، نتیجه COUNTIF بزرگ‌تر از صفر خواهد بود.

بخش <>0 بررسی می‌کند که آیا نتیجه COUNTIF بزرگ‌تر از صفر است یا خیر. اگر نتیجه صفر نباشد، شرط درست است. اگر نتیجه صفر باشد، شرط نادرست است.

بخش IF(…,”True”,”False”)، بر اساس نتیجه شرط، یکی از دو عبارت را نمایش می‌دهد:

• اگر مقدار پیدا شود: True
• اگر مقدار پیدا نشود: False

روش هشتم: استفاده از PivotTable 

ابتدا دو مجموعه‌داده را در یک جدول با یکدیگر ترکیب کنید.

از آنجا که این دو مجموعه‌داده مربوط به دو ماه متفاوت هستند، یک ستون جدید با عنوان ماه ایجاد کنید و ماه مربوط به هر فروش را در آن وارد کنید.

پس از ترکیب دو مجموعه‌داده، از INSERT گزینه PIVOT TABLE را انتخاب و در پنجره جدید، محدوده سلول‌های انتخاب‌شده برای ایجاد PivotTable را انتخاب کنید.

روی OK کلیک کنید.

پنل PivotTable Fields در کنار کاربرگ ظاهر می‌شود.

فیلدهای محصول، ماه، و فروش را به‌ترتیب در بخش‌های Rows، Columns و Values قرار دهید.

در همان کاربرگی که PivotTable جدید ایجاد شده است، در آخرین سلول پس از جدول، برای محاسبه اختلاف اعداد، فرمول تفاضل سلول‌ها را وارد کنید. در این مثال فرمول زیر استفاده می‌شود: 

=G7-H7

شماره سلول‌ها را باید به‌صورت دستی در فرمول وارد کنید. زیرا اگر برای ایجاد ارجاع، روی یک سلول کلیک کنید، اکسل به‌صورت خودکار تابع GETPIVOTDATA را وارد می‌کند و در این حالت نمی‌توانید فرمول را به‌راحتی در تمام سلول‌های کاربرگ کپی کنید.

کلید Enter را فشار دهید.

با استفاده از ابزار AutoFill، فرمول را برای تمام ردیف‌های داده کپی کنید.

به این ترتیب، با تفریق مقادیر دو ماه می‌توانید اختلاف فروش را شناسایی کرده و مغایرت‌های موجود در داده‌ها را بررسی کنید.

امیر هنرمند

علوم داده

هوشمند سازی کسب و کار با ابزار Microsoft Power BI

۴,۰ (۱۸۸ رای) ۱۳۱۷ نفر ۵۰۸ ساعت و ۵۴ دقیقه

بیشتر

مسیر یادگیری تحلیل داده با آکادمی همراه اول

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

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

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

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

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

مقایسه دو ستون در اکسل یکی از مهارت‌های ضروری برای متخصصانی است که به‌طور منظم با داده‌ها کار می‌کنند. استفاده از فرمول‌هایی مانند IF، VLOOKUP و COUNTIF و همچنین ابزارهایی مانند Conditional Formatting و PivotTable به کاربران کمک می‌کند تا از صحت داده‌ها مطمئن شوند، خطاها را کاهش دهند و در زمان خود صرفه‌جویی کنند. ترکیب این روش‌ها، رویکردی کاربردی و کارآمد برای مدیریت و بررسی مجموعه‌داده‌های بزرگ در اختیار شما قرار می‌دهد.

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

ساده‌ترین روش برای داده‌های کم مقایسه چشمی با استفاده از گزینه View Side by Side برای مغایرت‌گیری دو مجموعه داده در دو فایل اکسل جداگانه است. برای داده‌های بیشترین میتوانید از روش‌هایی که در بخش‌های قبلی این مطلب ارائه شد به ویژه روش‌های conditional formating، VLOOKUP و IFCOUNT استفاده کنید. در این حالت کافیست ستون‌ها را از بین رو فایل انتخاب کنید. 

روش نهم:  View Side by Side

• به تب View بروید و روی گزینه View Side by Side کلیک کنید.

• با این کار، پنجره Compare Side by Side باز می‌شود. یا دو فایل بلافاصله کنار هم نمایش داده می‌شود. 
• از فهرست نمایش‌داده‌شده، فایل اکسل دیگری را که می‌خواهید با فایل فعلی مقایسه کنید، انتخاب کنید.

• حالا دو فایل اکسل در کنار یکدیگر نمایش داده می‌شوند تا بتوانید تفاوت‌ها و مغایرت‌های آن‌ها را بررسی و مقایسه کنید. برای تغییر نحوه قرارگیری فایل‌ها می‌توانید از بخش Arrange All  گزینه مورد نظر خود را انتخاب کنید. 

اگر حجم داده‌ها زیاد باشد چه کنیم؟

برای حجم داده‌های زیاد میتوانید از XVLOOKUP و power query استفاده کنید. در بخش‌های قبلی مطلب مغایرت گیری در اکسل نحوه استفاده از XLOOKUP را توضیح دادیم. در این بخش مغایرت‌گیری با power query را بررسی می‌کنیم.  

روش دهم: مغایرت گیری با POWER QUERY

در این بخش از مطلب آکادمی همراه اول مغایرت گیری در اکسل با power query برای دو فایل جدا یا دو workbook را توضیح می‌دهیم.

مرحله اول: وارد کردن جدول خرید به Power Query

فرض کنید داده‌های سفارش‌های خرید در یک جدول در workbook تمرین ۱ قرار دارند. این جدول شامل ستون‌های شماره سفارش، مشتری، محصول و مبلغ است. هدف ما در این مرحله فقط این است که این داده‌ها را به‌عنوان یک Connection وارد Power Query کنیم.

ابتدا یکی از سلول‌های جدول را انتخاب کنید، سپس به تب Data بروید و روی Get Data یا New Query کلیک کنید. در این بخش می‌توانید انواع مختلف منابعی را که Power Query از آن‌ها پشتیبانی می‌کند، از فایل‌های Text/CSV گرفته تا JSON و حتی پوشه‌هایی شامل چندین فایل را مشاهده کنید.

از آنجا که داده‌های خرید از قبل به‌صورت یک جدول در همین فایل اکسل قرار دارند، گزینه From Table/Range و سپس جدول خود را انتخاب کنید. با این کار Power Query Editor باز می‌شود و پیش‌نمایشی از داده‌ها را نمایش می‌دهد. از آنجا که داده‌های ما از قبل پاک‌سازی و آماده هستند، در این مرحله نیازی به انجام هیچ تبدیلی روی آن‌ها نداریم.

حالا روی Close & Load To بزنید. در پنجره Import Data، گزینه Only Create Connection را انتخاب و روی OK کلیک کنید.

به این ترتیب، داده‌های خرید در Power Query ذخیره می‌شوند، بدون اینکه یک جدول تکراری از همان داده‌ها در کاربرگ اکسل ایجاد شود.

مرحله دوم: وارد کردن داده‌های فاکتور به Power Query

حالا به تب تمرین ۲ بروید. در این workbook، جدول زیر را در اختیار داریم:

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

در این مرحله نیز دقیقا همان مراحل قبلی را انجام می‌دهیم.

مرحله سوم: ادغام داده‌ها، محاسبه اختلاف و ایجاد جدول مغایرت گیری در اکسل

حالا که هر دو منبع داده را وارد Power Query کرده‌ایم، وقت آن است که آن‌ها را با یکدیگر مقایسه کنیم. به مسیر Data → Get Data → Combine Queries → Merge بروید. با این کار پنجره Merge باز می‌شود.

در پنجره Merge، در بخش جدول اول، جدول خرید و در بخش جدول دوم، جدول فاکتور را انتخاب کنید.

در مرحله بعد، باید ستون پارامتر مشترک را در هر دو جدول مشخص کنیم. در پیش‌نمایش جدول خرید روی ستون شماره سفارش و در پیش‌نمایش جدول فاکتور روی ستون رفرنس کلیک کنید.

نوع Join Kind را روی Left Outer قرار دهید. این گزینه باعث می‌شود تمام ردیف‌های خرید در نتیجه نهایی نمایش داده شوند و هر ردیفی از فاکتور که با آن‌ها مطابقت داشته باشد نیز در کنارشان قرار بگیرد.

اگر یک سفارش خرید نمونه متناظر در جدول نداشته باشد، آن ردیف همچنان در نتیجه نمایش داده می‌شود، اما ستون‌های مربوط به جدول فاکتور مقدار null خواهند داشت. این دقیقاً همان چیزی است که برای شناسایی مغایرت‌ها به آن نیاز داریم.

روی OK کلیک کنید. Power Query یک Query با نام Merge1 ایجاد می‌کند. نتیجه شامل تمام ردیف‌های جدول خرید و یک ستون جدید با نام جدول فاکتور است که برای هر ردیف تطبیق‌داده‌شده، یک جدول تو‌در‌تو (Nested Table) در خود دارد.

برای اضافه کردن ستون‌های موردنیاز، روی آیکون Expand در سربرگ این ستون کلیک کنید.

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

تیک گزینه‌های فاکتور و مبلغ کل را نگه دارید. همچنین تیک گزینه Use original column name as prefix را بردارید تا نام ستون‌ها ساده و مرتب باقی بماند.

روی OK کلیک کنید.

حالا هر شش ستون را در کنار یکدیگر می‌بینیم.

در ردیف ۱۲، ستون‌های فاکتور و مبلغ کل، مقدار null دارند. این یعنی سفارش مربوط به جدول خرید هرگز وارد جدول فاکتور  نشده است.

قبل از اینکه بتوانیم دو ستون مربوط به مبالغ را از یکدیگر کم کنیم، باید مقدار null را مدیریت کنیم.

ابتدا ستون مبلغ کل را انتخاب کنید، سپس به مسیر Transform → Replace Values بروید.

در کادر Value To Find عبارت null و در کادر Replace With عدد ۰ را وارد کنید و روی OK کلیک کنید.

حالا ستون مبلغ را انتخاب کنید، کلید Ctrl را نگه دارید و ستون مبلغ نهایی را نیز انتخاب کنید.

به تب Add Column بروید و گزینه‌های Standard → Subtract را انتخاب کنید.

Power Query یک ستون جدید ایجاد می‌کند که اختلاف بین دو مقدار را نشان می‌دهد. نام این ستون را به «اختلاف» تغییر دهید.

حالا که ستون اختلاف ایجاد شده است، به مسیر Home → Close & Load To بروید.

این بار گزینه Table را انتخاب کنید و سپس یک سلول از کاربرگ موجود در تب تمرین ۳  را به‌عنوان محل قرارگیری جدول انتخاب کنید. سپس روی OK کلیک کنید.

جدول مغایرت‌گیری آماده است.

در این جدول می‌توانیم بلافاصله دو مورد مغایرت را مشاهده کنیم:

• سفارش SH-1012 مربوط به شرکت ۱۲ فاقد فاکتور است و مقدار اختلاف برابر با ۸۰۰ است. این یعنی این سفارش هرگز در جدول فاکتور ثبت نشده است.
• سفارش SH-1010 مربوط به شرکت ۱۰ مقدار اختلاف برابر با ۴۵۰- دارد. یعنی مبلغ سفارش در خرید برابر با ۵۰ بوده، اما در جدول فاکتورمبلغ ۵۰۰  ثبت شده است.

هر دو مورد دقیقا از همان خطاهایی هستند که این فرآیند برای شناسایی آن‌ها طراحی شده است.

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

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

احمد حاجی تراب

علوم داده

مبانی مهندسی داده

۴,۰ (۱۰۴ رای) ۳۶۴ نفر ۲۳۷ ساعت و ۲۰ دقیقه

بیشتر

نکات مهم برای جلوگیری از خطا در مغایرت گیری

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

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

اطمینان حاصل کنید که مبالغ به‌صورت عددی و با علامت صحیح ثبت شده‌اند

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

برای تبدیل مبالغی که به‌صورت متنی ثبت شده‌اند، از توابعی مانند VALUE، ابزار Text to Columns یا تغییر نوع داده در Power Query کمک بگیرید. پیش از محاسبه مجموع مبالغ، علامت بدهکار و بستانکار را یکسان و استاندارد کنید. از وارد کردن متن‌های توضیحی در فیلدهای مربوط به مبلغ بپرهیزید.

انتخاب کلیدهای پایدار برای تطبیق

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

پیش از ایجاد کلید نهایی، تعداد ارقام را یکسان‌سازی کنید (برای مثال، صفرهای ابتدایی را حفظ کنید)، پیشوندها را فقط زمانی حذف کنید که قاعده آن مستند و مشخص شده باشد و فاصله‌های اضافی را نیز حذف کنید.

حذف ردیف‌های تکراری پیش از انجام تطبیق

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

اگر تعداد تکرار یک مورد بیشتر از یک باشد، نباید صرفا به همین دلیل آن ردیف را حذف کرد. این وضعیت باید باعث بررسی بیشتر شود تا مشخص شود که آیا مورد شناسایی‌شده واقعاً یک رکورد تکراری است، یک تراکنش تفکیک‌شده است یا یک تراکنش تکرارشونده و معتبر.

استفاده از فیلترها و قالب‌بندی شرطی

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

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

تهیه چک‌لیست پیش از بارگذاری و تعیین مسئول هر مورد

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

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

نتیجه‌گیری

مغایرت گیری در اکسل یکی از روش‌های کاربردی برای مقایسه، بررسی و اعتبارسنجی داده‌ها و شناسایی اختلاف میان دو یا چند مجموعه‌داده است. با توجه به نوع و حجم اطلاعات، می‌توان از روش‌هایی مانند IF، COUNTIF، VLOOKUP و XLOOKUP برای مقایسه داده‌ها و از ابزارهایی مانند Conditional Formatting برای شناسایی بصری مغایرت‌ها استفاده کرد.

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

 پرسش‌های متداول (FAQ)

در این بخش از مطلب مغایرت گیری با اکسل به تعدادی از پرسش‌های متداول در مورد این موضوع پاسخ می‌دهیم. 

چگونه دو ستون را در اکسل با هم مقایسه کنیم؟

برای مقایسه دو ستون در اکسل، می‌توانید از تابع IF استفاده کنید. اگر هدف شما پیدا کردن داده‌های مشترک یا مواردی باشد که فقط در یکی از ستون‌ها وجود دارند، توابعی مانند COUNTIF و XLOOKUP نیز گزینه‌های مناسبی هستند. همچنین می‌توانید از Conditional Formatting برای مشخص کردن مغایرت‌ها با رنگ استفاده کنید.

چگونه مغایرت دو فایل اکسل را پیدا کنیم؟

 پیدا کردن مغایرت دو فایل اکسل، ابتدا داده‌های هر دو فایل را بر اساس یک شناسه مشترک مانند کد کالا، شماره فاکتور یا شناسه مشتری مقایسه کنید. می‌توانید از توابعی مانند XLOOKUP، COUNTIF و IF برای شناسایی رکوردهای مفقود یا مقادیر متفاوت استفاده کنید. برای فایل‌های بزرگ‌تر نیز Power Query گزینه مناسبی است. Power Query امکان مقایسه و ادغام داده‌ها و شناسایی مغایرت‌ها را به‌صورت سریع‌تر و ساختاریافته فراهم می‌کند.

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

بهترین فرمول به نوع مغایرت‌گیری بستگی دارد. اگر فقط می‌خواهید بررسی کنید که یک مقدار در لیست دیگر وجود دارد یا خیر، COUNTIF گزینه‌ای ساده و کاربردی است. برای پیدا کردن و مقایسه اطلاعات متناظر، XLOOKUP انتخاب مناسب‌تری است. همچنین، VLOOKUP برای نسخه‌های قدیمی‌تر اکسل کاربرد دارد. اگر با حجم زیادی از داده‌ها یا دو فایل اکسل جداگانه کار می‌کنید، Power Query معمولاً گزینه حرفه‌ای‌تر و مناسب‌تری برای مغایرت‌گیری است.

 چگونه داده‌های متفاوت را در اکسل رنگی کنیم؟

برای رنگی کردن داده‌های متفاوت در اکسل، می‌توانید از قابلیت Conditional Formatting استفاده کنید. ابتدا محدوده موردنظر را انتخاب کرده و از مسیر Home > Conditional Formatting گزینه Highlight Cells Rules یا New Rule را انتخاب کنید. سپس شرط موردنظر را تعیین کنید تا مقادیر متفاوت به‌صورت خودکار با رنگ دلخواه مشخص شوند. این روش برای شناسایی سریع مغایرت‌ها بین دو ستون بسیار کاربردی است.

آیا می‌توان مغایرت‌گیری در اکسل را برای تعداد زیادی داده انجام داد؟

بله، اکسل می‌تواند برای حجم زیادی از داده‌ها نیز مغایرت‌گیری انجام دهد. برای داده‌های گسترده، استفاده از ابزارهایی مانند Power Query و توابعی مانند XLOOKUP و COUNTIFS کمک می‌کند مقایسه و شناسایی مغایرت‌ها سریع‌تر و دقیق‌تر انجام شود. همچنین Power Query برای فرایندهای تکراری گزینه مناسبی است، زیرا می‌توان داده‌ها را یک‌بار تنظیم و در دفعات بعد با به‌روزرسانی اطلاعات، مغایرت‌گیری را دوباره انجام داد.

منابع:

• ExcelDemy
• Excel University
• Fire Look Up