مقایسه دو لیست از اطلاعات در اکسل
در این مقاله می خواهیم اطلاعات دو لیست در اکسل را با هم مقایسه نموده و ورودی هایی که منحصر به فرد هستند (غیر تکراری) را از هر دو لیست پیدا کنیم.
بارها اتفاق افتاده که در هنگام کار با صدها ردیف از اطلاعات در اکسل خواسته ایم داده های خود را از دو لیست به صورت دستی مقایسه نماییم.
برای آنکه مجبور به مقایسه دستی نباشیم، از ابزارConditional Formatting و COUNTIF استفاده می کنیم.
سئوال: من می خواهم دو ستون از اطلاعات را با هم مقایسه نموده و در هر ستون داده های منحصر به فرد را با یک رنگ خاص مشخص نمایم.
در این مثال توضیح داده می شود که چگونه با استفاده از conditional formatting دو ستون از داده ها را با هم مقایسه کنیم.
استفاده از Conditional Formatting
برای مثال دو لیت مطابق شکل زیر را می خواهیم با هم مقایسه نماییم.
برای این منظور به روش زیر عمل می کنیم.
ابتدا ناحیه A1:A6 را انتخاب نموده و نام FirstList را برای آن انتخاب می کنیم.
(برای نام گذاری در کادر اسم نام مورد نظر را بدون فاصله تایپ کرده و کلید Enter را فشار می دهیم)
به همین ترتیب ناحیه B1:B6 را انتخاب نموده و برای آن نام SecondList را انتخاب می کنیم.
ناحیه A1:A6 را انتخاب می کنیم . (می توانیم از لیست کادر نام عبارت FirstList را انتخاب نماییم)
از سربرگ Home ابزار Conditional formatting را انتخاب نمایید و سپس بر روی گزینه New rule کلیک کنید.
از پنجره ظاهر شده عبارت “Use a formula to determine which cells to format” را انتخاب نمایید.
فرمول را به صورت زیر وارد نمایید.
=COUNTIF(SecondList,A1)=0
بر روی دکمه Format کلید کنید و از پنجره ظاهر شده یک رنگ دلخواه انتخاب نمایید.
بر روی دکمه ok کلیک کنید.
چک باکس stop if true را در حالت انتخاب قرار داده و بر روی دکمه ok کلیک کنید.
نتیجه به شکل زیر خواهد بود.
همانطور که در شکل می بینید Delhi Daredevils و Royal Challengers Bangalore منحصر به فرد هستند (در ناحیه SecondList قرار ندارند.)
برای مشخص کردن آیتم هایی که در SecondList باشند و در FirstList نباشند از فرمول زیر استفاده می کنیم.
=COUNTIF(FirstList,B1)=0
در اینجا فرمول (COUNTIF(SecondList,A1 تعدادآیتم های ناحیه SecondList که برابر آیتم سلول A1 هستند را شمارش می کند.
(به عبارت دیگر آیا آیتم واقع درسلول A1 در SecondList هست یا خیر).
پس اگر =0( COUNTIF(SecondList,A1 آیتم واقع در سلول A1 در ناحیه SecondList موجود نیست. و در پایان Conditional formatting آیتم های منحصر به فرد را به رنگ مشخص شده نمایش می دهد.
راه دوم برای مقایسه لیست ها
یک کپی از صفحه موجود کپی نموده و سپس تمام قوائد conditional formatting را از آن حذف کنید
(انتخاب هر دو ستون – انتخاب conditional formatting از سربرگ Home- انتخاب clear rules و سپس انتخاب clear rules from selected cells)
ابتدا ناحیه داده ها را انتخاب نمایید.
از سربرگ home ابزار conditional formatting و سپس گزینه Duplicate values را انتخاب نمایید.
از پنجره ظاهر شده زیر عبارت Format cells that contain گزینه Unique را انتخاب نمایید و سپس بر روی دکمه ok کلیک نمایید.
نتیجه مشابه مراحل قبل می باشد.
آموزش تصویری مقایسه دو لیست با استفاده از تابع Match
دانلود آموزش ویدیویی تابع IF
عالی بود. سپاس
سلام. ببخشید من دو تا کپی از یه فایل دارم . میخوام با این فرمول متوجه شم چه خونه هایی از کپی دوم ویرایش شده (در واقع تفاوت ها رو با کپی اول پیدا کنم) . میشه راهنماییم کنید؟
ممنون
سلام
خیلی ممنون از اطلاعات خوبی که گذاشتین.
یه سوال.اگر در این دوستون نام و نام خانوادگی باشه واسامی تکراری هم باشه اما متاسفانه بعض جاها اسپیس بینشون هست یا مثلا به جای ی از یِ استفاده شده، اون وقت اکسل این رو تشخیص نمیده بایستی چه کار کرد؟
منتظر راهنمایی ارزشمندتون هستم
This is really interesting, You are a very skilled
blogger. I have joined your rss feed and look forward to seeking more of your excellent post.
Also, I’ve shared your website in my social networks!
thank you for your favor
Hmm it looks like your blog ate my first comment (it was super long) so I guess I’ll
just sum it up what I wrote and say, I’m thoroughly enjoying your blog.
I too am an aspiring blog blogger but I’m still new to the whole thing.
Do you have any tips for rookie blog writers? I’d really appreciate it.
I am also a beginner
more study and describe many successful examples will contribute greatly to your progress
Hi there very cool website!! Guy .. Excellent ..
Amazing .. I’ll bookmark your site and take the feeds also?
I’m satisfied to find so many helpful info right here within the put up, we need work out extra strategies in this regard, thanks for sharing.
. . . . .
بسیارعالی سپاس فراوان امروز کلا درگیر این موضوع بودم
I am very happy to read this. This is the kind of manual
that needs to be given and not the random misinformation that’s at the other blogs.
Appreciate your sharing this greatest doc.
My brother recommended I would possibly like this website.
He used to be entirely right. This post truly made my day.
You can not consider simply how so much time I had spent for this information! Thank you!
Thanks for your comment
كامل توضيح ندادين با تابع COUNTIF چه جور ميشه كاركرد اصلا دو شرط چه جوري ميشه اعمال كرد
My brother recommended I would possibly like this web site.
He was once entirely right. This put up actually made my day.
You cann’t consider simply how a lot time I had spent for this information! Thanks!
سلام. خسته نباشید
ببخشید یک سوال داشتم: من یک ستونی از اعداد دارم که به ترتیب از کوچک به بزرگ چیده شده است.
یک ستون دیگر از اعداد نیز دارم که نصف تعداد عدد موجود در ستون یک را داراست با این تفاوت که اعداد بهم ریخته هستند و به ترتیب نیستند-
چطور میتونم کاری کنم که اکسل اعداد مشترک هر دو ستون را بشناسد و بیاید روبروی هم قرار بدهد؟ ممنون
با سلام
با فرض اینکه اعداد مرتب شده در ناحیه A1:A10 و اعداد سری دوم نامرتب که شامل تعدادی از اعداد سری اول هستند در ناحیه B1:B10 قرار داشته باشند از فرمول زیر استفاده کن
(“”,((IFERROR(INDEX($A$1:$A$10,MATCH(B1,$A$1:$A$10,0=
فرمول را در ستون C1 بنویس و تا C10 کپی کن
سلام -بسیار ممنونم از لطفتون
سلام..من دوفایل از یکسری دیتا دارم که فقط یک ستون مشابه از دو فایل ممکنه تغییر کرده باشه …..با چه فرمولی میتونم متوجه تغییرات اتفاق افتاده بشم. البته من اطلاعات دو فایل رو تو یک شیت ریختم که کار راحت بشه
با سلام وخسته نباشید،
ببخشید یک سوال داشتم؛ یک ستونی از شماره موبایل 11 رقمی در اکسل دارم که تعدادشان بسیار زیاد است، بعضی از شماره ها در بعضی از سلول ها غلط هستند و تعداد کاراکترشان یا بیشتر از 11 رقم است یا کمتراز آن، چگونه اون سلول هایی که تعداد کارکترشان 11 رقم نیست رو مشخص کنم؟
با تشکر
میتونی از فرمول len استفاده کنی این فرمول بهت میگه که تعداد کارکترهای یک سلول چند تاست
Hi there! I just want to offer you a huge thumbs up
for your excellent info you’ve got right here on this post.
I am returning to your website for more soon.
سلام و خسته نباشید
من دو تا شیت اکسل دارم که یکیش هر ماه عوض میشه میخام یکی از ستون ها رو با یه ستون از شیت قبلی مقایسه کنه هر جا اون عدد رو دید بیاد و از شیت اول سلول هم سطر اون رو بنویسه
بطور مثال در شیت دوم این اطلاعات است
علی غلامی 335812
ابراهیم عسگری 358544
و در شیت اول این اطلاعات
335812 – محسن
358548 – علی
میخام جلوی سلول علی غلامی 335812 اسم محسن رو بنویسه
-( برای جداکردن سلولها گذاشتم)
بسیار ممنون از آموزشای خوبتون
تشکر. مفید بود و استفاده کردم
سلام
دو تا ستون از اعداد یا اسامی که تعداد ردیفشون هم خیلی بالاست رو چطور میشه سلولهای مشابه رو کنار هم قرار داد؟
درود بر شما
من هر روز یک فایل اکسل دارم که باید اطلاعات اون را با اطلاعات روز قبل مقایسه کنم .ستون اول آن نام و نام خانوادگی است و ستون های دیگر شامل اطلاعات هر فرد است .از آنجا که تعداد اسامی هر روز تغییر می کند ، در نتیجه ترتیب اسامی نیز به هم می ریزه .چطور می تونم اطلاعات این دو فایل را طوری با هم ادغام کنم که اطلاعات روزهای مختلف هر فرد را در یک سطر روبروی نام فرد ، داشته باشم ؟
ممنون از لطف شما
سلام
این مطلب بسیار فوق العاده بود
یک دنیا سپاس