قبلا در مورد پیدا کردن داده های تکراری در اکسل و حتی جلوگیری از ورود اطلاعات تکراری مطلبی منتشر کردیم و حتی مطلبی دیگری در سایت داریم با عنوان استخراج داده های یکتا از یک محدوده اطلاعات ، اما این بار می خواهیم روشهای مختلف بررسی و مقایسه دو ستون در اکسل برای یافتن مقادیر تکرایر و داده های متفاوت بررسی کنیم .
مقایسه سطر به سطر دو ستون
فرض می کنیم داده هایی مشابه تصویر زیر داریم ، میخواهید داده های ستون A را با ستون B مقایسه کنیم ( سطر به سطر )
- در ستون C فرمول زیر را بنویسد A2=B2
- در صروتی که کاملا برابر باشند نتیجه True و در غیر اینصورت نتیجه False می شود.
قالبدهی شرطی و رنگی کردن داده های تکراری
- محدوده داده مورد نظر را انتخاب کنید
- از تب Home به قسمت Conditional Formatting را انتخاب کنید .
- روی گزینه Highlight Cell Rules رفته و Duplicate Values را برگزینید.
- پنجره بعدی را اوکی کنید که نتیجه مانند تصویر زیر خواهد شد .
نکته : اگر در تصویر بالا به جای Duplicate گزینه Unique را انتخاب کنبد داده های یکتا رنگی می شود.
استفاده از CountIf و مقایسه داده های عددی
در سلول C2 فرمول زیر را می نویسیم
=COUNTIF($A$2:$A$20,B2)
فرمول بالا را تا C20 امتداد می دهیم و خواهید دید که روبروی داده های تکراری ، دفعات تکرار را نمایش می دهد .
مقایسه دو ستون متنی در اکسل
اصلی ترین قسمت این مطلب آموزشی مربوط به مقایسه دو داده متنی در دو ستون اکسل است و در این مثال ما نام شرکتها را در دو ستون وارد کرده ایم و میخواهیم از لحاظ تکراری بودن بررسی کنیم.
- استفاده از تابع countif
دقیقا مشابه روش قبلی عمل میکنیم ولی ضعف این روش این است که اگر فاصله ای بین نوشته ها وجود داشته باشد ، اکسل قادر به تشخیص آن نیست و علی رقم اینکه متون تکراری هستند آنها را تکراری تشخیص نمی دهد ، تصویر زیر را ببینید
نام شرکت ایران ترانسفو با اینکه تکراری هست ، اکسل آن را تشخیص نداده به این دلیل که در انتهای آن یک فاصله وجود دارد که دیده نمیشود و چاره کار استفاده از تابع Trim می باشد .
- استفاده از تابع Trim برای حذف فاصله های اضافه بین و ابتدا و انتهای کلمات
با فرض اینکه داده های ما در ستوناهای A و B قرار دارد باید در ستون C فرمول زیر را بنویسیم ، که باعث می شود فاصله های اضافه حذف شده و در نتیجه ایران ترانسفو را به عنوان تکراری تشخیص دهد .
اما این تمام ماجرا نیست و در ادامه به مشکلی بر میخوریم که کلمات فاصله اصلی را ندارند مثلا مقایسه “نادر بحری” با “نادربحری” ، در این مورد فرمول بالا به تنهایی کافی نیست - استفاده از SUBSTITUTE برای جایگزینی حروف “ی” و “ک” عربی
احتمال دارد در نوشتن کلمات از کیبورد عربی استفاده شده باشد که باید حروف ی و ک در آنها اصلاح شود که راه چاره استفاده از SUBSTITUTE و ترکیب آن با تابع TRIM است مانند تصویر زیر
- حذف تمام فاصله ها و ترکیب چند SUBSTITUTE
نهایتا در یان مرحله باید از یک SUBSTITUTE دیگر استفاده کنیم تا تمام فاصله ها را حذف کنیم تا احتمال خطا را به حداقل برسانیم و مرحله آخر با تابع COUNTIF و ترکیب آن با تابع TRIM و استفاده از سه تابع SUBSTITUTE به صورت تو در تو به نتیجه دلخواه برسیم.
درتصویر بالا شاید نتیجه به دست آمده در ستون کمکی نامفهوم باشد ولی مهم نیست چرا که موقت هستند و فقط قرار است که برای ما داده های تکراری متنی را پیدا کنند و بعد از رفع نیاز می توانیم آن ها را حذف کنیم و اینکه مخفی نمائیم
درتصویر بالا شاید نتیجه به دست آمده در ستون کمکی نامفهوم باشد ولی مهم نیست چرا که موقت هستند و فقط قرار است که برای ما داده های تکراری متنی را پیدا کنند و بعد از رفع نیاز می توانیم آن ها را حذف کنیم و اینکه مخفی نمائیم
دانلود فایل اکسل رایگان مقایسه دو ستون
دانلود فایل نمونه
در این فایل دو شیت وجود دارد که حاوی بررسی داده های تکراری عددی و متنی می باشد
آقای بحری واقعا ممنون عالی بود ، ولی ویدیو نمیذارید براش
ویدیو اول پست قرار گرفت
سلام من ی فایل اکسس دارم احتیاج دارم وارد اکسل کنم بهم بگید چجوری ممنون.
سلام
خدمت شما
https://officebaz.ir/product/transfer-between-excel-and-access/
سلام ممنون از شما.میخواسثم سه هزارشماره تلفن رو اشتراک گیری کنم. شماره های تکرای و مشابه سه نقطه
امتیاز بینندگان:5 Stars
سلام آقای بحری روز بخیر بین دو محدوده در دو شیت میخواهم مقایسه انجام بدهم ممکنه روشی معرفی کنید
این آموزش متاسفانه ویدیو نداره ولی توضیحات رو به همراه تصاویر سعی کردم کامل بذارم ، ضمن اینکه تو اینستا گرام هم ویدیو گذاشتم
سلام من می خوام دو ستون شماره تلفن رو مقایسه کنم به این صورت که موارد مشابه در هر دو ستون را روبروی هم قرار دهد مثال
۱ ۱
۲ ۲
۳
ممنون . آموزشتون خیلیییییی مفید بود.
خواهش میکنم
ممنونم از پست مفیدتون
مرسی خیلی خوب بود
با سلام و ضمن تشکر از شما مقایسه دو ستون بسیار عالی بود اگه زحمتی نیست در اکسس نیز یه مثال بیاورید
سلام
ببخشید من دو فایل جداگانه دارم یکی شامل 10000 اسم و شماره تلفن و دیگری شامل 1000 اسم که حالا میخوام بدونم چه جوری میشه این 1000 اسم رو توی فایل اصلی سرچ کرد و شماره تلفن هاشون رو استخراج کرد به جای اینکه دونه دونه این کار رو انجام بدم.
سلام این کار رو با Vlookup به سادگی می تونید انجام بدین
با سلام و احترام
یک لیست خرید ماهانه دارم. میخوام یه لیست دیگه ایجاد کنم که اقلام خریداری شده به تفکیک نوع کالا در اون لیست اضافه بشه.
برای این کار، میخوام هر موقع که اسم کالایی رو در لیست اول وارد کردم در صورتی که توی اون لیست(تمام سلول های بالا و پایین سلول جاری) وجود نداشته باشه بطور خودکار به لیست دوم اضافه بشه.
اگه بخوام از if های تو در تو استفاده کنم خیلی پیچیده میشه. میتونین راهنمایی بفرمایین؟
با سلام
تخفیفاتی که بر روی محصولات اعمال کردید چرا کلی است و به تفکیک هر دوره نمی باشد؟
به طور مثال دوره اکسس مقدماتی به تنهایی؟
سلام امروز تخفیف ها رو تو اینستا اعلام میکنم
سلام
وقت بخیر
من یه جدولی دارم شامل ستون شماره فاکتور و دسته محصولات میخوام تعداد فاکتورهای غیر تکراری برای هر دسته محصول رو شمارش کنم
مثلا اگر 7 دسته محصول داریم وقتی دسته 1 رو بخوام تعداد فاکتورها رو شمارش کنم بدون اینکه فاکتورهای تکراری رو شمارش کنه دقیق بگه دسته یک چندتا فاکتور صادر شده دسته 2 چندتا و دسته 7
لازم بذکر است کد دسته رو از ورودی دریافت کنه داخل فرمول اشاره به سلول ورودی بشه
من از طریق sumproduct , countifs نتونستم جواب بگیرم میشه لطف کنید راهنمایی کنید.
با سلام
۱)سوالم این است که اگر دو ستون مختلف یک سری اسامی یا نام داشته باشیم بخواهیم در ستون سوم اسامی یا نام مشترک را نمایش دهیم به چه صورت است
۲) و مورد دیگر اگر بخواهیم یک تعداد سلول رنگی بر اساس انتخاب رنگ که حاوی عدد میباشد را مثلا با هم جمع کنیم
با تشکر فراوان از شما
سلام .
من یه شیت دارم کد ملی و شماره موبایل
یه شیت دیگه دارم کد ملی و نام خانوادگی
میخوام تو یه شیت جدید نام خانوادگی و موبایل رو داشته باشم. میشه راهنماییم کنید
سلام خسته نباشید، یه سوالی داشتم از خدمتتون
من یه فایل دارم که دو شیت داره، در شیت اول سه ستون هست شامل نام و نام خانوادگی و کد ملی ، در شیت دوم چهار ستون شامل نام و نام خانوادگی و کد ملی و محل کار و این شیت به عنوان بانک اطلاعاتی محسوب میشه
چطوری میتونم نفرات شیت اول رو به تفکیک محل کار جدا کنم؟؟؟
خیلی لطف میکنید اگر به سوالم پاسخ بدید،
متشکرم
سلام فکر می کنم با VLOOKUP به راحتی بتونین این کار رو انجام بدین
https://officebaz.ir/product/vlookup/
خیلی عالی بود ممنو
سلام . خیلی ممنون از مطالب خوب شما . من راهنمایی میخواستم . من فایل دارم با چهار ستون . ستون اول و چهارم اسامی تیم های فوتبال است بصورت میزبان و مهمان و دو ستون وسط اعداد نتیجه بازی . برای مثال پرسپولیس در یک هفته میزبان و هفته بعد مهمان است .
من چطور میتونم مثلا ده ردیف آخر که پنج بار اسم پرسپولیس در ستون اول اومده و پنج بار در ستون چهارم ستونهای رو بروش رو با هم جمع کنم .
البته الان sort میکنم دوبار تا به نتیجه برسم . ولی آیا راهی داره ؟؟؟؟؟
من هر چند وقت که نیاز به کمک درخصوص اکسل دارم به سایت شما مراجعه میکنم ، تا بحال خیلی از مشکلات من به کمک سایت شما رفع شده ، بدینوسیله از شما بابت زحماتتون تشکر و قدردانی می کنم و آرزمند موفقت روزافزون شما از خداوند متعال هستم
ممنون لطف شماست
خدا
پدر
مادر
همه دوستان و …
مستقیم بفرسه
بهشت
سلام و با تشکر
سوال دارم چطور میشه دو ستون را در اکسس با هم‌مقایسه کرد و درصد اختلافشون رو پیدا کرد
مثلا ستون بدهی و ستون پرداخت شده
میخوام پیدا کنم هر مشتری چند درصد بدهیشو پرداخت کرده
سلام باید از کوئری استفاده کنید
سلام بر استاد بحری عزیز
آموزش های شما بسیار شیوا و روان است . به نظرم شما تا فیها خالدون مطلب رو بیان می کنید(ببخشید که اینطور نوشتم).
من میخوام در یک فایل اکسل دو ستون رو با هم مقایسه کنم منتهی با نوشتن کد برنامه نویسی. و ستونهایی هم که با هم مغایرت دارن رنگی شده و میزان مغایرت آنها در ستون دیگر نشان داده شود. / متشکرم از شما
با سلام وقتتان بخیر ممنون میشم من را در خصوص پیدا کردن راهی که بتوان بیشترین مقدار در یک ستون که در ستون مقابل آن داده مورد نظرم هست راهنمایی بفرمائید
سلام متاسفانه متوجه منظورتان نشدم
سلام آقای بحری;
در ابتدا ازتون بابت تمام آموزش هاتون که در اختیار عموم قرار دادید سپاسگزارم;
سوالی که در اینجا دارم اینه:
فایل اکسلی دارم شامل 5000 ردیف از کالاهایی که در بازه زمانی متفاوت خریداری شده، و میخواهم این کالاها را بر اساس تاریخ ورود مرتب کنم بطوری که فقط بروزترین تاریخ رو مشاهده کنم و بقیه تاریخ خرید های مربوط به یک کالا حذف شوند.
ممنون میشم کمکم کنید.
سلام برای اینکه به صورت خودکار حذغ بشن باید کدنویسی کنید
سلام وقتتون بخیر من دو‌تا ستون از جدول رو به این می خوام بررسی کنم که در مرحله اول در ستون اول داده های تکراری نشان داده شود و در مرحله دوم داده های تکراری که تاریخ قدیم تر نسبت به هم دارند حذف شوند میشه لطفا راهنمایی بفرمایید
*ستون دوم شامل تاریخ است
* قذیمی ترین تاریخ برای هر داده تکراری
با سلام و وقت بخیر
من میخواستم اطلاعات یک ستون در شیت 1 رو به ستون خالی دیگر در شیت 2 بصورت پویا فراخوانی کنم
بطوری که اگر در ستون شیت 1 اطلاعاتی وارد کردم یا ویرایش کردم در ستون شیت 2 هم بصورت داینامیک و پویا فراخوانی بشه و نمایش بده
ضمنا میخواستم بپرسم که راهکار شما در اکسل آیا در گوگل شیت هم کاربرد داره و کار میکنه یا خیر
با تشکر از شما
سلام آقای دکتر، وقت بخیر
بنده کارشناش آموزش هستم و برنامه های درسی مدیران گروهها رو بنده باید ثبت کنم. مشکلی که من غالبا با آن مواجه هستم اینست که: برخی از مدیران سهوا میزان ساعت ارائه درس رو اشتباه مینویسند. برای اینکه این اشتباه پیش نیاید، لازم است اختلاف ساعت، شروع و پایان ارائه درس مساوی با ستون میزان ارائه درس شود. این فرمول رو چطوری تعریف کنم که؛ ستون پایان ارائه درس رو منهای ستون شروع درس بکنه و اگر مساوی با ستون میزان ارائه درس نبود؛ ستون شروع و پایان ارائه درس رو هایلایت کنه.