یکی از قابلیت های متعدد Microsoft Excel توانایی مقایسه دو لیست داده ، شناسایی مطابقت بین لیست ها و شناسایی مواردی است که فقط در یک لیست یافت می شوند. این امر هنگام مقایسه سوابق مالی یا بررسی اینکه آیا نام خاصی در پایگاه داده وجود دارد ، مفید است. می توانید از تابع MATCH برای شناسایی و علامت گذاری سوابق مطابق یا غیر مطابقت استفاده کنید ، یا می توانید از قالب بندی شرطی با عملکرد COUNTIF استفاده کنید. مراحل زیر به شما می گوید که چگونه از هریک برای مطابقت با داده های خود استفاده کنید.
مراحل
روش 1 از 2: شناسایی رکوردها با تابع MATCH
مرحله 1. فهرست داده ها را در یک کاربرگ واحد کپی کنید
Excel می تواند با چندین کاربرگ در یک کتاب کار یا با چندین کتاب کار کند ، اما اگر اطلاعات آنها را در یک کاربرگ کپی کنید ، مقایسه لیست ها آسان تر می شود.
مرحله 2. به هر مورد فهرست یک شناسه منحصر به فرد بدهید
اگر دو لیست شما روش مشترکی برای شناسایی آنها ندارند ، ممکن است لازم باشد یک ستون اضافی به هر لیست داده اضافه کنید که آن مورد را در Excel مشخص می کند تا بتواند ببیند آیا موردی در یک لیست مشخص به یک مورد مرتبط است یا خیر در لیست دیگر ماهیت این شناسه بستگی به نوع داده ای دارد که سعی می کنید با آن مطابقت داشته باشید. برای هر لیست ستون به یک شناسه نیاز دارید.
- برای داده های مالی مرتبط با یک دوره معین ، مانند سوابق مالیاتی ، این می تواند شرح یک دارایی ، تاریخ به دست آوردن دارایی یا هر دو باشد. در برخی موارد ، یک ورودی ممکن است با یک شماره کد مشخص شود. با این حال ، اگر از سیستم یکسانی برای هر دو لیست استفاده نشود ، این شناسه ممکن است در مواردی که وجود ندارد مطابقت ایجاد کند یا مطابقت هایی را که باید ایجاد شود نادیده بگیرد.
- در برخی موارد ، می توانید مواردی را از یک لیست بردارید و آنها را با مواردی از لیست دیگر ترکیب کنید تا یک شناسه ایجاد کنید ، مانند شرح دارایی فیزیکی و سال خرید آن. برای ایجاد چنین شناسه ای ، داده های دو یا چند سلول را با استفاده از ampersand (&) به هم متصل می کنید (اضافه کنید ، ترکیب کنید). برای ترکیب توضیحات مورد در سلول F3 با تاریخ در سلول G3 ، جدا شده با فاصله ، فرمول '= F3 & "" & G3' را در سلول دیگری در آن ردیف ، مانند E3 وارد کنید. اگر می خواهید فقط سال را در شناسه (زیرا یک لیست از تاریخ کامل استفاده می کند و دیگری فقط از سال استفاده می کند) استفاده کنید ، تابع YEAR را با وارد کردن '= F3 & "" & YEAR (G3)' در سلول E3 به جای آن وارد کنید. (نقل قول های تک را وارد نکنید ؛ آنها فقط برای نشان دادن مثال وجود دارند.)
- هنگامی که فرمول را ایجاد کردید ، می توانید با انتخاب سلول با فرمول و کشیدن دسته پر کردن بر روی سلول های دیگر ستون که می خواهید فرمول را کپی کنید ، آن را در سایر سلول های ستون شناسه کپی کنید. هنگامی که دکمه ماوس خود را رها می کنید ، هر سلولی که کشیده اید با فرمول پر شده و منابع سلول به سلول های مناسب در همان ردیف تنظیم می شود.
مرحله 3. در صورت امکان داده ها را استاندارد کنید
در حالی که ذهن تشخیص می دهد که "Inc." و "Incorporated" به یک معناست ، Excel این کار را نمی کند مگر اینکه یک کلمه یا کلمه دیگر را دوباره قالب بندی کنید. به همین ترتیب ، ممکن است مقادیری مانند 11 ، 950 دلار و 11 دلار ، 999.95 دلار را به اندازه کافی نزدیک برای مطابقت با یکدیگر در نظر بگیرید ، اما Excel آن را نخواهد گفت مگر اینکه به آن بگویید.
- شما می توانید با برخی از اختصارات ، مانند "Co" برای "Company" و "Inc" برای "Incorporated با استفاده از تابع رشته LEFT برای کوتاه کردن نویسه های دیگر ، برخورد کنید. سایر اختصارات ، مانند" Assn "برای" Association "، ممکن است به بهترین شکل باشد با ایجاد راهنمای سبک ورود داده ها و سپس نوشتن برنامه ای برای جستجو و اصلاح فرمت های نامناسب ، با آن برخورد می شود.
- برای رشته های اعدادی ، مانند کد های پستی که برخی از نوشته ها شامل پسوند ZIP+4 هستند و برخی دیگر ندارند ، می توانید مجدداً از تابع رشته LEFT برای تشخیص و مطابقت فقط کدپستی اصلی استفاده کنید. برای اینکه Excel مقادیر عددی نزدیک به هم را تشخیص دهد اما یکسان نیستند ، می توانید از تابع ROUND برای گرداندن مقادیر نزدیک به یک عدد و مطابقت آنها استفاده کنید.
- فضاهای اضافی ، مانند تایپ دو فاصله بین کلمات به جای یک ، با استفاده از عملکرد TRIM قابل حذف است.
مرحله 4. ستون هایی را برای فرمول مقایسه ایجاد کنید
همانطور که مجبور شدید ستون هایی برای شناسه های لیست ایجاد کنید ، باید ستون هایی را برای فرمول ایجاد کنید که برای شما مقایسه می شود. برای هر لیست به یک ستون نیاز دارید.
باید بخواهید این ستونها را با چیزی مانند "گم شده؟"
مرحله 5. فرمول مقایسه را در هر سلول وارد کنید
برای فرمول مقایسه ، از تابع MATCH استفاده می کنید که درون یک تابع دیگر Excel ، ISNA قرار گرفته است.
- فرمول به شکل "= ISNA (MATCH (G3 ، $ L $ 3: $ L $ 14 ، FALSE))" است ، جایی که یک سلول از ستون شناسه لیست اول با هریک از شناسه های موجود در لیست دوم مقایسه می شود. ببینید آیا با یکی از آنها مطابقت دارد یا خیر. اگر مطابقت نداشته باشد ، یک رکورد وجود ندارد و کلمه "TRUE" در آن سلول نمایش داده می شود. اگر مطابقت داشته باشد ، رکورد موجود است و کلمه "FALSE" نمایش داده می شود. (هنگام وارد کردن فرمول ، نقل قول های ضمیمه را وارد نکنید.)
- همانطور که فرمول شناسه سلول را کپی کرده اید ، می توانید فرمول را در سلولهای باقی مانده ستون کپی کنید. در این مورد ، فقط مرجع سلول برای سلول شناسایی تغییر می کند ، زیرا قرار دادن علامت های دلار در مقابل ردیف های ستون و ستون برای سلول های اول و آخر در لیست شناسه های سلول دوم ، آنها را به مراجع مطلق تبدیل می کند.
- می توانید فرمول مقایسه لیست اول را در سلول اول ستون لیست دوم کپی کنید. سپس باید منابع سلول را ویرایش کنید تا "G3" با مرجع اولین سلول شناسایی در لیست دوم و "$ L $ 3: $ L $ 14" با اولین و آخرین سلول شناسه جایگزین شود. لیست دوم (علائم دلار و دو نقطه را تنها بگذارید.) سپس می توانید این فرمول ویرایش شده را در سلولهای باقی مانده در ردیف مقایسه لیست دوم کپی کنید.
مرحله 6. فهرست ها را مرتب کنید تا در صورت لزوم ، مقادیر غیر منطبق را به راحتی مشاهده کنید
اگر لیست های شما بزرگ هستند ، ممکن است لازم باشد آنها را مرتب کنید تا همه مقادیر غیر منطبق را در کنار هم قرار دهید. دستورالعمل های زیر در زیر فرمول ها را به مقادیر تبدیل می کند تا از خطاهای محاسبه مجدد جلوگیری شود ، و اگر لیست های شما بزرگ باشد ، از زمان دوباره محاسبه طولانی جلوگیری می کند.
- موس خود را روی همه سلول های موجود در یک لیست بکشید تا انتخاب شود.
- Copy را از منوی Edit در Excel 2003 یا از گروه Clipboard نوار Home در Excel 2007 یا 2010 انتخاب کنید.
- از منوی Edit در Excel 2003 یا از دکمه کشویی Paste در گروه Clipboard در روبان صفحه Excel 2007 یا 2010s ، Paste Special را انتخاب کنید.
- "Values" را از لیست Paste As در کادر محاوره ای Paste Special انتخاب کنید. روی OK کلیک کنید تا محاوره بسته شود.
- از منوی Data در Excel 2003 یا گروه Sort and Filter از نوار Data در Excel 2007 یا 2010 ، Sort را انتخاب کنید.
- در فهرست "محدوده داده های من" از "مرتب سازی بر اساس" ، "سرفصل سرصفحه" را انتخاب کنید ، "گم شده؟" (یا نامی که در واقع عنوان ستون مقایسه را ذکر کرده اید) و روی OK کلیک کنید.
- این مراحل را برای لیست دیگر تکرار کنید.
مرحله 7. موارد غیر مطابقت را از نظر بصری مقایسه کنید تا ببینید چرا با هم مطابقت ندارند
همانطور که قبلاً اشاره شد ، Excel به گونه ای طراحی شده است که به دنبال داده های دقیق باشد ، مگر اینکه آن را برای جستجوی داده های تقریبی تنظیم کرده باشید. عدم مطابقت شما می تواند به سادگی جابجایی تصادفی حروف یا ارقام باشد. همچنین می تواند چیزی باشد که نیاز به تأیید مستقل دارد ، مانند بررسی اینکه آیا دارایی های فهرست شده باید در وهله اول گزارش شوند.
روش 2 از 2: قالب بندی مشروط با COUNTIF
مرحله 1. فهرست داده ها را در یک کاربرگ واحد کپی کنید
مرحله 2. تصمیم بگیرید که در کدام لیست می خواهید سوابق مطابقت یا غیر تطبیق را برجسته کنید
اگر می خواهید سوابق را فقط در یک لیست برجسته کنید ، احتمالاً می خواهید سوابق منحصر به فرد آن لیست را برجسته کنید. یعنی رکوردهایی که با رکوردهای لیست دیگر مطابقت ندارند. اگر می خواهید رکوردها را در هر دو لیست برجسته کنید ، می خواهید رکوردهایی را که با یکدیگر مطابقت دارند برجسته کنید. برای اهداف این مثال ، فرض می کنیم که لیست اول سلولهای G3 تا G14 و لیست دوم سلولهای L3 تا L14 را در بر می گیرد.
مرحله 3. مواردی را که می خواهید موارد منحصر به فرد یا منطبق را در آنها برجسته کنید انتخاب کنید
اگر می خواهید موارد منطبق در هر دو لیست را برجسته کنید ، باید لیست ها را یکی یکی انتخاب کرده و فرمول مقایسه (توضیح داده شده در مرحله بعد) را در هر لیست اعمال کنید.
مرحله 4. فرمول مقایسه مناسب را اعمال کنید
برای انجام این کار ، باید در محاوره قالب بندی شرطی در نسخه Excel خود دسترسی پیدا کنید. در اکسل 2003 ، این کار را با انتخاب قالب بندی شرطی از منوی قالب انجام می دهید ، در حالی که در اکسل 2007 و 2010 ، روی دکمه قالب بندی شرطی در گروه Styles از نوار خانه کلیک می کنید. نوع قانون را به عنوان "فرمول" انتخاب کرده و فرمول خود را در قسمت Edit the Rule Description (قانون ویرایش قانون) وارد کنید.
- اگر می خواهید رکوردهای منحصر به فرد در لیست اول را برجسته کنید ، فرمول "= COUNTIF ($ L $ 3: $ L $ 14 ، G3 = 0)" خواهد بود ، در حالی که محدوده سلولهای لیست دوم به صورت مقادیر مطلق ارائه شده و مرجع به اولین سلول از لیست اول به عنوان مقدار نسبی. (نقل قول های نزدیک را وارد نکنید.)
- اگر می خواهید رکوردهای منحصر به فرد در لیست دوم را برجسته کنید ، فرمول "= COUNTIF ($ G $ 3: $ G $ 14 ، L3 = 0)" خواهد بود ، با محدوده سلول های لیست اول به صورت مقادیر مطلق و مرجع به سلول اول لیست دوم به عنوان مقدار نسبی. (نقل قول های نزدیک را وارد نکنید.)
- اگر می خواهید سوابق موجود در هر لیست را که در لیست دیگر وجود دارد برجسته کنید ، به دو فرمول نیاز دارید ، یکی برای لیست اول و دیگری برای دوم. فرمول لیست اول "= COUNTIF ($ L $ 3: $ L $ 14 ، G3> 0)" است ، در حالی که فرمول لیست دوم COUNTIF ($ G $ 3: $ G $ 14 ، L3> 0) "است. قبلاً ذکر شد ، شما لیست اول را برای اعمال فرمول آن انتخاب می کنید و سپس لیست دوم را برای اعمال فرمول آن انتخاب می کنید.
- هر فرمت مورد نظر را برای برجسته کردن رکوردهای پرچم گذاری شده اعمال کنید. روی OK کلیک کنید تا محاوره بسته شود.
ویدئو - با استفاده از این سرویس ، ممکن است برخی از اطلاعات با YouTube به اشتراک گذاشته شود
نکات
- به جای استفاده از مرجع سلولی با روش قالب بندی شرطی COUNTIF ، می توانید مقداری را برای جستجو وارد کرده و برای مواردی از این مقدار ، یک یا چند لیست را پرچم گذاری کنید.
- برای ساده سازی فرم های مقایسه ، می توانید نام هایی برای لیست خود ایجاد کنید ، مانند "List1" و "List2". سپس ، هنگام نوشتن فرمولها ، این لیستها می توانند جایگزین محدوده سلولهای مطلق مورد استفاده در مثالهای بالا شوند.
