جلوگیری از ورود داده تکراری در اکسل
ورود داده تکراری مشکل مهم در ورود اطلاعات
وقتی که داریم داده در اکسل وارد میکنیم یا فرمی تهیه کردیم که سایر افراد از طریق اون داده وارد دیتابیس اکسل بکنند، لازمه که یک سری کنترل هایی روی ورود داده انجام بدیم که داده ها به درستی در بانک اطلاعاتی ذخیره بشن. مثلا بهتره که کنترلی روی ستون مربوط به کد ملی ایجاد کنیم که هر بار چک بکنه که آیا کد ثبت شده ۱۰ رقم هست یا نه. یا مثلا عددی رو در بازه مشخصی داریم ثبت میکنیم، بهتره که کنترلی روی اون ستون داشته باشیم که داخل بازه بودن عدد وارد شده رو چک کنه و اگر در بازه مورد نظر نبود، هشدار بده یا اینکه از ورود اطلاعات تکراری جلوگیری کنه.
هر گاه بخوایم روی ورود داده ها در اکسل از این قسم کنترلها ایجاد کنیم، میتونیم از ابزار Data Validation استفاده کنیم. ابزار data Validation در واقع کمک میکنه به اینکه داده صحیح و سالم و استاندارد در دیتابیس وارد بشه و از ورود داده های ناصحیح جلوگیری میکنه.
جلوگیری از ورود داده تکراری در اکسل از مسائل پر کاربرد به حساب میاد. برای این موضوع یک راه حل ترکیبی ارائه میکنیم. در واقع فرمول نویسی در ابزار Data validation راه حل ترکیبی است که باید مورد استفاده قرار بگیره.
برای این کار باید در دیتاولیدیشن فرمولی بنویسیم که تشخیص بده رکورد فعلی که داره ثبت میشه در محدوده بالای سرش وجود داشته یا نه (در واقع محتوای سلول فعلی در محدوده بالای سرش شمرده بشه، اگر تعدادش بیش از ۰ بود، یعنی در محدوده بالا تکرار شده. پس نباید اجازه ثبت رکورد مورد نظر داده بشه).
برای درک بهتر این موضوع به مثال زیر دقت کنید.
فرض کنید میخواهیم در ستون A کد محصول وارد کنیم و در صورتیکه کد محصول تکراری ثبت کردیم خطا بده و اجازه ثبت داده تکراری رو به ما نده.
مطابق شکل ۱ روی سلول A2 کلیک کرده و از تب Data گزینه Data Validation رو میزنیم.
شکل ۱- جلوگیری از ثبت داده تکراری
از قسمت Allow گزینه Custom رو انتخاب میکنیم و فرمول زیر رو در قسمت Formula می نویسیم. (مطابق شکل ۲)
=Countif ($A$1:A1 , A2) <1
در واقع معنی این فرمول اینه که: به داده ای اجازه ثبت بده که تعدادش در محدوده بالای سرش کمتر از یک (یعنی صفر) باشه.
شکل ۲- نوشتن فرمول Countif در ابزار Data Validation
در قسمت Error Alert هم میتونیم پیام هشدار رو تنظیم کنیم. مثلا بنویسیم: شما مجاز به ثبت داده تکراری نیستید. برای این کار مطابق شکل ۳ عمل میکنیم:
شکل ۳- تنظیم پیام خطا در صورت ثبت داده تکراری
بعد از زدن OK کافیه ولیدیشن رو روی بقیه سلولهای مورد نظر اعمال کنیم. برای این کار سلول A2 را کپی کرده و محدوده A3:A13 رو انتخاب کرده و از پنجره Paste Special (Ctrl+Alt+V) گزینه Validation رو انتخاب میکنیم.
حالا اگر داده تکراری ثبت کنیم، پیام خطا مشابه شکل ۴ نمایش داده خواهد شد.
شکل ۴- نمایش پیام هشدار در صورت ثبت داده تکراری
سوال: فرمول دیگه ای که بتونه جلوگیری از ورود داده تکراری در اکسل رو انجام بده میتونید ارائه بدید؟
پاسخ رو در ادامه همین پست و در قالب کامنت ثبت کنید.
این کار با استفاده از افزونه Kutools در اکسل هم قابل انجام هست.
دانلود فایل اکسل این آموزش
برای دریافت فایل اکسلی که در این آموزش ایجاد شده روی لینک زیر کلیک کنید:
سلام با دیتا ولیدیشن لیست درست کردم – لیست من در شیت دیگری است و مابین سلول ها خالی وجود داره و بعضی از اسامی هم تکراری هستن ، وقتی لیست رو در دیتا ولیدیشن درست کردم هم خانه های خالی و هم افراد تکراری رو به تعداد تکرار آورد چکار کنم که خانه های خالی و تکراری ها رو نیاره
درود
باید ورودی لیست رو درست بهش بدید
یعنی یک لیست بدون تکرار و بدون فضای خالی
مثلا remove duplicate یا تابع unique در ۲۰۲۱
و بعد لیست ایجاد شده رو بدید به ولیدیشن
سلام فرمولی میخوام که وقتی داده ای تکراری میدم به خانه ها داده جدید ثبت بشه و قدیمی همزمان پاک بشه
امکان این امر هست؟
به عنوان مثال عدد جدید که وارد شد همون عدد از اون سطر ولی در خانه های دیگه پاک بشه
درود بر شما
این قبیل کارها با کدنویسی وی بی ای شدنی هست
با سلام
چکونه می توان همزمان دو کار از دیتا ولیدیشن بخواهیم
مثلا همزمان هم این مورد شما را بخواهیم و هم بخواهیم لیست درست کنیم.
عرض سلام
اجرای همزمان این دو کار در دیتاولیدیشن نیست، برای همین باید از VBA استفاده کرد که جلوی ورود تکراری مقادیر رو بگیره.
با سلام خدمت اساتید عزیز چراغی و خاکزاد
جلوگیری از ورود داده با استفاده از Data Validation یه ایراد داره اونم اینکه اگه شما ابتدا سلول پایین تر رو با یه دیتا مشخص پر کنید می تونید توی سلول بالاترش دوباره دیتای تکراری وارد کنید به عنوان مثال اگه روی ستون A این فرمول نوشته بشه ، شما اگه ابتدا سلول A10 رو با داده مشخص مثل NAME پر کنید بعد میتونید به راحتی در سلول A5 دوباره کلمه NAME رو وارد کنید.
راهکاری برای این وجود داره؟
سلام
بله Data Validation به خودی خود ایراداتی داره، برای حل مسئله ای که مطرح کردید میتونید در تابع Countif به جای تعیین دامنه پویای بالای سلول مورد نظر، میتونید کل ستون A یا یک محدوده ثابت و بزرگ رو در نظر بگیرید و شرط رو کوچکتر مساوی یک قرار بدید.
سلام مهندس من میخوام توی دو تا ستون با هم چک کنه که داده تکراری هست یا نه مثلا از ستون a2تا a25 و ستون f2 تا ستون f25
درود
مقاله زیر رو مطالعه کنید
https://excelpedia.net/compare-lists/
سلام
خانم مهندس جسارتا بنده عیناً همان فرمولی که شما تایپ کرده اید را زدم! اما اولا به اون مساوی اولش گیر میده! وقتی مساوی رو بر میدارم فرمول رو قبول میکنه اما وقتی توی سلول های اعمال شده هر عددی رو وارد میکنم error میده!! لازم به ذکر هست دقیقاً مثل شما وارد کردم
درود
اگر دقیقا همه مراحل رو تکرار کرده باشدی که نتیجه میگیرید! یجای کار دقیق نیست
= رو کپی نکنید و تایپ کنید بصورت دستی
جداکنننده ها و پرنتز و ” همه رو تایپ کنید
منم دقیقا همین مشکل و دارم تایپم کزدم نشد
درود بر سروران
با استفاده از تابع countif و انتخاب محدوده یک ستون و مشخص نمودن سلول فعال به عنوان اینکه محتوی این سلول را در ستون جستجو کن و اگر تکرار آن بیش از ۱ بود از ورود آن ممانعت کن ( COUNTIF(A:A,A1)<2) با خطای circular references مواجه می شوم که منطقی بنظر می رسد. فرمول من برای جلوگیری از تکرار ورودی اشتباه است ؟ یا از این خطا صرف نظر کنم؟
درود
اگر این فرمول در data validation نوشته بشه این خطا رو نمیگیرید. اگر در سلول A1 هستید و این فرمول رو نوشتید، خطا رو به درستی دریافت کردید ولی کنترلی بر ورودی ندارید چون فقط در یک سلول نوشته شده نه در ابزار Data validation
از راهنمایی شما بسیار سپاسگذارم
با سلام. مشکلی که من دارم صفر قبل از عدد رو نمیشناسه. مثلا ۱ و ۰۱ رو یه عدد در نظر میگیره و ارور تکرار میده. میتونید راهنماییم کنید چجوری مشکلشو حل کنم؟ ضمنا فورمت سلول هم تکست هستش
درود
بله ظاهرا مسئله توی countif به این صورته.
میتونید از vlookup یا match استفاده کنید
****به محدوده متحرک بالاسری دقت کنید****
بسیار ممنون از راهنماییتون.
سلام
وقت بخیر
برای وارد کردن شماره تلفن از همین روش استفاده کردم ولی هر عددی وارد میکنم خطا میده چه جوری درست میشه وسوال دیگر اینکه وقتی فایل را میبندم
کلا این فرمول پاک میشه واصلا اجرا نمکنه
درود
فرمول رو درست ننوشتید که خطا میده
ضمن اینکه اینموضوع ربطی به بستن فایل نداره. فرمول رو چک کنید مجدد
با سلام در فرمول فوقاگر کد کالای بالایی رواشتباها برای کد کالای پایین بنویسد ثبت میشود واینکه کد کالا تکراری است پیغام نمی دهد .
با تشکر
درود
منظور از کد بالایی و پایینی چی هست؟
سلام وقتی فرمول رو وارد میکنم ارور میده بخاطر داشتن مساوی چیکار میتونم بکنم؟؟؟؟
درود بر شما
شاید مساوی رو جای درست ینمیذارید
به پرانتزها هم دقت کنید . به اندازه و بجا گذاشته بشه
سلام خانوم خاکزاد
خانوم خاکزاد فرمول رو نوشتم و درست عمل کرد و نمیشه اعداد تکراری رو وارد کرد اما چرا وقتی یه عددی رو کپی پیس می کنیم هشدار نمیده و عدد ثبت میشه؟
درود بر شما
data validation روی تایپ عکس العمل نشون میده. با کپی پیست کاری نداره!
کار من انبار گردانی هست و بیتر با کپی پیست کار می کنم ، راه حلی برای این دارید تا وقتی کپی پیست می کنم اعداد تکراری رو ثبت نکنه؟
سلام. خسته نباشید خانم خاکزاد. در یک ستون که نمایشگر ماههای مختلف است که به ازای هر یک از این ماه ، در ستون بعدی روزهای کاری به صورت تاریخ اومده و در ستونهای بعدی، اطلاعاتی در این خصوص در اون روز کاری مربوطه وجود دارد. کاری که می خواستم انجام بدم اینه که بدون VBA که با فرمول، هر بار که یک ماه به خصوص رو فیلتر می کنم، اختلاف تاریخ اولین روز کاری اون ماه، با آخرین روز کاری اون ماه، رو حساب کنم که برای هر ماه این مقدار متغیره. دستور محاسبه تاریخ رو نوشتم و مشکلی ندارم، فقط نمی دونم چطور به فرمول تاریخ بفهمونم که در آرگومان تاریخ مبدا به اولین سطر که معرف اول روز کاری و در تاریخ مقصد به آخرین سطر که مبین آخرین روز کاری است مراجعه کنه؟؟ به طور کلی دستور و اقدامی مشابه ۹)subtotal می خواستم با این تفاوت که به وقتی یک ماه را فیلتر می کنیم جای جمع اون ماه، عمل انتخاب ۲ سلول ابتدایی و انتهایی در سولهای آشکارشده را انجام دهد
درود بر شما
برای انتخاب سلول، باید کد نویسی کنید
اگر سوال اینه که با هر بار فیلتر کردن، سلول اول و اخر انتخاب بشه
سلام
من میخوام روزی یه بار فقط داده تکرای ثبت بشه
منظور هر ۲۴ ساعت یتونم داده تکراری وارد کنم و دیگه نتونم تا فردا
آیا راهی برای این امر هست؟
درود بر شما
باید یک ستون داشته باشید که هر روز رو نشون بده و بعد بیاریدش توی شرط countif
سلام خانم مهندس چطور میتونم تو vba جلوگیری کنم از ورود اطلاعات ؟؟ ممنونم میشم پاسخ بدید
سلام،
میتونید شیت رو پروتکت کنید که این کار با VBA هم انجام شدنی هست.
راه دیگه اینه که در رویداد Change عبارت زیر رو قرار بدید:
سرکارخانم خاکزاد
سلام
امکان ریموت توسط شما هست که مشکل بنده حل بشه ….لطفا؟؟؟؟؟؟
خیر
داخل گروه تلگرامی که لینکش در فوتر هست مطرح کنید. فایل بذارید. مشکل رو بپرسید. دوستان زیادی پاسخگو هستن
موفق باشید
سلام خانم پاکزاد و آفرین بر شما ، شما مایه افتخار و مباهات ما هستید
جسارتا سوالی داشتم از حضورتون
من تازه علاقه مند به اکسل و نمودارهاش شدم ، فلذا میخوام نمودار هیستوگرام ترسیم کنم بدین ترتیب که موارد تکراری رو یکی کنه تو نمودار بطور مثال مهر ۱۰۰۰ ، آبان ۱۵۰۰ ، آذر ۱۲۵۰ و دورباره مهر ۱۳۰۰ ولی میخوام تو نمودار یدونه مهر رو با مبلغ جمع کل ۲۳۰۰ نمایش بده
آیا این امکان وجود داره
ممنون میشم راهنمایی بفرمایید
متشکرم
دکتر بهرام جباری ۰۹۹۰۰۹۹۴۸۹۳
درود بر شما
اگر هیستوگرام میخواید رسم کنید از این لینک استفاده کنید:
https://excelpedia.net/histogram-chart/
اما مثالی که زدید هیستوگرام نیست. یک نمودار ستونی ساده هست که قبلش باید داده هاشو جمع کنید با تابع sumif. یعنی ستونی تشکیل بدید از ماه های سال و این فرمول رو جلوش بنویسید تا جمع برای هر ماه رو نشون بده
https://excelpedia.net/sumif-function/
بعد روی داد ههای بدست آمده نمودار ستونی رو رسم کنید
https://excelpedia.net/charting-tips/
https://excelpedia.net/combined-charts/
در نهایت اگر مایل به فرمول نویسی نبودید، از ابزار pivottable و pivot chart استفاده کنید
https://excelpedia.net/what-is-pivot-table/
https://excelpedia.net/create-pivot-table/
سلام و خسته نباشید
چجوری میشه توی چند کاربرگ یک ستون مشخص رو محدود کرد که از داده تکراری جلوگیری کنه؟ مثلا من ۱۰۰ تا کاربرگ دارم که میخوام ستون F رو توی همشون جلوگیری کنم از داده تکراری .یعنی عددی را که در ستون F هر کدوم از کاربرگها وارد میکنم خودش همه کاربرگهای دیگه رو چک کنه و از تکرارش جلوگیری کنه.ممنون میشم کمکم کنین
سلام
متأسفانه امکان اینکه یکدفعه همه شیت ها رو انتخاب کنید و روی همشون Data Validation بزنید وجود نداره. برای همین شما دو راه دارید (البته ممکنه راه های دیگه هم باشه که الان به ذهنم نرسیده باشه):
1- با استفاده از فرمول نویسی در بخش Custom ابزار Data Validation برای هر شیت این محدودیت رو ایجاد و به شیت های دیگه منتقل کنید.
2- با استفاده از VBA می تونید در رویداد Change هر شیت این محدودیت رو ایجاد کنید.
پیشنهاد میکنم یک شیت Template بسازید که در ستون F اون Data Validation رو زده باشید و هر بار اون شیت رو اضافه کنید و ازش استفاده کنید.
سوال: فرمول دیگه ای که بتونه جلوگیری از ورود داده تکراری در اکسل رو انجام بده میتونید ارائه بدید؟
پاسخ:
فعال کردن conditional formating بر روی ستون مورد نظر هست و انتخاب گزینه duplicate value و رنگی کردن داده هایی که تکراری وارد میشن
دقیقا این یکی از روش های مشخص کردن تکراری هاست
ولی جلوگیری نمیکنه
سلام استاد
این روش یک ضعف بزرگ داره
زمانی که داده وارد شده به صورت کپی وارد میشه دیگه اون رو چک نمی کنه و میتونه تکراری باشه
اگر راهی برای جلوگیری از اون می دونید بگید تا این مشکل برطرف بشه
سلام
این ابزار زمانی که اطلاعات در سلول تایپ میشه اثر داره.
برای این کار میتونید از VBA استفاده کنید.
درود بر شما
البته نمیشه گفت این روش، کلا دیتا ولیدیشن در مقابل کپی پیست منفعله و کاری نمیکنه و من راهی براش نمیدونم واقعا.
مگر اینکه با VBA بحث Copy/Paste رو کنترل کنیم که خب اونم دردسرهای خودش رو داره
درود
از مطلب ارائه شده استفاده نمودم
مطلب بسیار کاربردی و روش آموزش که بصورت تصویری و مرحله به مرحله است بسیار موثر است.
سپاس و مانا باشید
سوال: فرمول دیگه ای که بتونه جلوگیری از ورود داده تکراری در اکسل رو انجام بده میتونید ارائه بدید؟
پاسخ:
احسنت بر شما