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

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

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

وجود مغایرت در سطرها و ستون‌های اکسل می‌تواند حاصل خطای انسانی باشد. مثل داده‌های تکراری یا بی‌قاعدگی در قالب‌بندی داده. برای مثال در جدول زیر جنسیت Walter White و Wayne Cena به جای «Male»، به اشتباه «Mael» نوشته شده است. مطمئناً این ایراد حین مرور داده، یک‌به‌یک قابل اصلاح هستند، اما وقتی با حجم زیادی از داده در سطح‌ها و ستون‌ها مواجهیم، این کار منطقی به نظر نمی‌رسد. برای اصلاح این مغایرت‌ها در اکسل می‌توانید از قابلیت‌های زیر استفاده کنید:

• فیلتر کردن: با استفاده از فیلترها مغایرت را در ستون داده‌ها به سرعت پیدا کنید.
• «قالب‌بندی شرطی» (Conditional Formatting): با استفاده از قوانین قالب‌بندی سلول‌هایی که از شرط مد نظر پیروی نمی‌کنند، برجسته کنید.
• توابع پیشرفته اکسل: از توابعی مثل تابع MATCH و ویلوکاپ بهره بگیرید این مغایرت را بین داده‌ها بیابید.

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

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

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

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

استفاده از فیلتر برای مغایرت گیری

استفاده از فیلتر یکی از ساده‌ترین راه‌ها برای مغایرت گیری در اکسل است. برای مثال در تصویر زیر با استفاده از فیلتر داده‌های اشتباه را در ستون «جنسیت» (Gender) ایزوله می‌کنیم. ابتدا در سربرگ «Home» روی دکمه «Sort & Filter» بزنید.

«Filter» را انتخاب کنید.

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

در این مثال بالای ستون جنسیت را انتخاب می‌کنیم و داده صحیح (Male) را از حالت انتخاب شده در می‌آوریم.

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

سپس به سربرگ Home بخش «Find & Select» بروید و گزینه «Replace» را انتخاب کنید.

مقدار اشتباه را روبروی «Find what» و مقدار صحیح را روبروی «Replace with» بنویسید سپس روی «Replace All» بزنید.

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

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

برای مغایرت گیری در اکسل باید با مفاهیمی مانند فرمول‌نویسی، استفاده از توابع مرسوم مانند IF و VLOOKUP و ایجاد قوانین شرطی در اکسل آشنا باشید. پس از آشنایی با مفاهیم پایه، می‌توانید به مراحل پیشرفته‌تر مغایرت گیری در اکسل بپردازید. برای مثال باید از توابع INDEX-MATCH و جداول پیوت که پیش‌تر در مجله فرادرس نیز به آن اشاره شده، استفاده کنید. همچنین، باید از ابزارها و تکنیک‌های جدید برای مدیریت و تجزیه و تحلیل داده‌ها نیز کمک بگیرید تا مدیریت داده‌های حجیم، ساده‌تر شود.

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

• آموزش استفاده از توابع و فرمول نویسی در اکسل Excel
• آموزش ترفندهای کاربردی در اکسل Excel

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

• فیلم آموزش آموزش اکسل از مقدماتی تا پیشرفته

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

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

داده مد نظر خود را که شامل سطرها و ستون‌های دلخواه شماست انتخاب کنید. در این مثال ستون A و ستون B شامل نام شرکت و نام کشور را انتخاب می‌کنیم. سپس در سربرگ Home روی «Conditional Formatting» کلیک می‌کنیم.

نوع شرط و سپس خود شرط را انتخاب کنید. در این مثال ابتدا «Highlight Cell Rules» و سپس «Duplicate Values» را انتخاب می‌کنیم.

حال باید انتخاب کنیم که مقادیر تکراری یا مقادیر یکتا «برجسته» (Highlight) شوند. رنگ و فونت مد نظر را برای قالب‌بندی سلول انتخاب می‌کنیم. سپس روی Ok کلیک می‌کنیم.

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

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

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

توابع IF و IS

برای مقایسه سلول‌ها و یافتن مغایرت بین آن‌ها می‌توانیم از دو تابع IF و IS به صورت ترکیبی استفاده کنیم برای مثال از فرمول زیر استفاده می‌کنیم تا سلول A2 و B2 را مقایسه کنیم.

=IF(A2=B2,"MATCH","NOT MATCHED")

فرمول را در سلول C2 می‌نویسیم. با کشیدن مربع کوچک پایین سلول تا انتهای جدول، فرمول را تکرار می‌کنیم. اگر سلول‌های متناظر در ستون A و B با یکدیگر برابر باشند، عبارت «Match» و در غیر این صورت «Not Match» نمایش داده می‌شود.

تابع IF را با تابع IS مثل ISNUMBER یا ISTEXT ترکیب کنید تا انواع مختلف داده بررسی شوند. برای مثال فرمول زیر «Number» را برمی‌گرداند. اگر ستون ‌B2 مقدار عددی داشته باشد و در غیر این صورت «NaN» برگردانده می‌شود.

=IF(ISNUMBER(B2), "Number", "NaN")

استفاده از فرمول‌های VLOOKUP، HLOOKUP و XLOOKUP

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

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) 

آرگومان تابع اچلوکاپ نیز به ترتیب زیر است:

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

• lookup_value   مقادیری را که قصد جستجوی آن را در جدول دارید.
• table_array   بازی از سلول‌ها در جدول دوم که قصد مقایسه با آن را دارید.
• col_index_num   شماره ثبت یا ستونی که قصد برگرداندن داده از آن را دارید.
• [range_lookup]   این آرگومان مقدار False برای جستجو با تطبیق دقیق و True برای جستجوی تقریبی را می‌پذیرد.

در این مطلب با استفاده از VLOOKUP در ستون E نام کارمندان را در ستون A و D مقایسه می‌کنیم تا درآمد آن‌ها از ستون B استخراج شود.

تابع ایکسلوکاپ نسخه جلوتری از ویلوکاپ و اچلوکاپ است که در اکسل ۲۰۲۱ به بعد وجود دارد. آرگمان این تابع به ترتیب زیر است:

=XLOOKUP(lookup_value, lookup_array, return_array,[if_not_found])

• lookup_value   مقداری که قصد یافتن آن را دارید.
• lookup_array   آرایه‌ای از سلول‌ها که مقدار مد نظر را در آن جستجو می‌کنید.
• return_array   آرایه‌ای که قضد دارید مقداری از آن بازگردانده شود.
• [if_not_found]   متنی که مایلید برگردانده شود، اگر تابع نتوانست مقدار مد نظر را بیابد.

در تصویر زیر مثال قبلی را به کمک این تابع انجام می‌دهیم:

تابع MATCH

با تابع MATCH می‌توانید دو لیست از داده را برای مغایرت‌ گیری مقایسه کنید. تابع MATCH برای پیدا کردن داده مشخصی در یک لیست داده کاربردی است و موقعیت آن داده را در لیست برمی‌گرداند.

=MATCH(lookup_value, lookup_array, [match_type])

برای مثال در تصویر زیر موقعیت «Watermelons» در لیست یافت شده که در ردیف چهارم لیست قرار دارد.

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

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

افزونه «Spreadsheet Inquire» را در نظر بگیرید. این افزونه به شما کمک می‌کند که دو فایل اکسل را مقایسه کنید و تفاوت‌های آن‌ها را برجسته کنید. برای فعال سازی این افزونه طبق مراحل زیر عمل کنید:

1. به سربرگ File بروید.
2. روی Options کلیک کنید سپس به بخش «Add-ins» بروید.
3. در بخش «Manage» گزینه «COM Add-ins» را انتخاب کنید و سپس روی «Go» بزنید.
4. Spreadsheet Inquire را انتخاب کنید سپس روی Ok بزنید.
5. بعد از فعال شدن به سربرگ Inquire بروید و از دستور «Compare Files» استفاده کنید.

یکی دیگر از ابزارهای مفید برای پیدا کردن مغایرت‌ها «Analysis ToolPak» است. این افزونه توابع پیشرفته آماری را به همراه ابزارهای پیشرفته بررسی داده ارائه می‌کند که می‌تواند برای بررسی مغایرت‌ها استفاده شود. برای فعال کردن این ابزار مراحلی را مشابه ابزار قبلی طی کنید و در بخش Manage گزینه Analysis را فعال کنید.

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

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

برای پیشگیری از به وجود آمدن مغایرت در داده‌ها می‌توانید قانون اعتبارسنجی داده (Data Validation Rules) ایجاد کنید. برای این کار طبق مراحل زیر عمل کنید:

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

به سربرگ «Data» بروید و روی «Data Validation» کلیک کنید

در پنجره Data Validation به سربرگ «Setting» بروید. در این بخش می‌توانید شرایط مد نظر خود را تعریف کنید.

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

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

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

علاوه بر شرط اعتبارسنجی می‌توانید پیام های دلخواه خود را برای خطا و غیره تنظیم کنید تا برای مثال، اگر داده‌ای از شرط اعمالی شما پیروی نکرد، این پیام نمایش داده شود. به ترتیب می‌توانید درک داده‌ها را برای مخاطب آسان‌تر کنید. برای این کار در پنجره Data Validation به سربرگ «Input Message» و «Error Alert» بروید تا پیام‌های مد نظر خود را وارد کنید.

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