به منظور جلوگیری از ورود داده تکراری در اکسل، دو روش استفاده از ماکرو و استفاده از دستور Data Validation وجود دارد که روش دوم نسبت به استفاده از ماکرو ساده تر و کاربردی تر است که در این پست به توضیح آن پرداخته شده است.
Data Validation
ابزار Data Validation (توضیح کامل در اینجا) در تب Data برای محدود کردن اطلاعات وارد شده درون یک سلول استفاده میشود و فرآیند تعریف آن به این صورت است که با انتخاب محدوده مد نظر و انتخاب Data Validation از تب Data پنجرهِ آن باز میشود و کاربر میتواند از این قسمت حالت مد نظر را انتخاب کند.
در این پنجره بخش Custom قسمتی است که کاربر میتواند با استفاده از فرمول، محدودیت مد نظر را اعمال کند. در این بخش با استفاده از فرمول محدودیت اعمال میشود و بعد از ورود اطلاعات و زدن اینتر در آن سلول، آن فرمول محاسبه میشود، در صورتی که حاصل فرمول برای مقدار وارد شده در آن سلول برابر با True باشد، عبارت وارد شده در آن سلول پذیرفته میشود ولی در صورتی که حاصل فرمول برای مقدار وارد شده برابر با False باشد، مقدار وارد شده مورد پذیرش قرار نمیگیرد.
روش اجرا
فرض کنیم بخواهیم مقادیر وارد شده در ستون B را کنترل کنیم به گونه‌ای که به کاربر اجازه وارد کردن داده تکراری در آن محدوده داده نشود، لذا در این حالت باید مقدار وارد شده در سلول فعال بررسی شود، سپس با استفاده از تابع Countif چک شود در ستون B آن عبارت چند بار تکرار شود. در صورتی که تعداد تکرار آن عبارت در ستون B برابر با ۱ باشد، باید مقدار وارد شده مورد پذیرش قرار بگیرد و در غیر این صورت مقدار وارد شده نباید مورد پذیرش قرار بگیرد.
بدین منظور ابتدا ستون B را انتخاب کرده و از تب Data گزینه Data Validation را برگزیده و در پنجره باز شده در قسمت Allow حالت Custom را برگزیده و در قسمت Formula فرمول ذیل را وارد نموده.
=COUNTIF(B:B,B1)=1
سپس در پنجره Data Validation وارد تب Error Alert شده و در قسمت Title عبارت “خطا” را ثبت نموده و در قسمت Error Message عبارت “مقدار وارد شده تکراری است، لطفا مقدار را مجددا وارد کنید”. با تایید کردن پنجره فرمول اعمال میشود و مشاهده میشود که در صورت ورود داده تکراری، کاربر با خطا مواجه خواهد شد.
در این حالت اگر در ستون B داده تکراری وارد شود (داده ای که قبلا در آن ستون وارد شده باشد) مطابق با شکل ذیل کاربر با خطا مواجه میشود و نمیتواند آن مقدار را درون سلول وارد کند.
سلام و سپاس
هرجوری وارد میکنم باز خطا میده.
مثلا میزنم ۲۱۲، کافیه یه سلول داده اش ۲۱ باشه اولش؛ سریع خطا میده!!!
فرمول را اشتباه وارد کرده اید قطعا
سلام
فرمولی که فرمودید برای داده های تکراری خوب بود ولی وقتی داده رو کپی پیست می کنیم خطا نمیگیره، چه کنیم؟
متاسفانه محدودیت Data Validation همین است، اگر بخواهید روش کامل داشته باشید میتونید از VBA استفاده کنید.
سلام من زدم درست بود
سلام لطفا راهنمایی کنید
وقتی میخوام روی اوکی بزنم یا روی تب Error Alert خطای زیر میده
https://s4.uupload.ir/files/2021-11-07_001537_r27n.png
سلام
روز بخیر
فرمول وارد شده اشتباه است که این پیغام را میده
یا به جای ویرگول نقطه ویرگول بزار (هر علامتی که هنگام فرمول نویسی میزاری) یا یک پرانتز کم گذاشتید
سلام
باید به شکل زیر وارد کنید
COUNTIF($A$1:$A$10ºA1)=1=
سلام
باید به شکل زیر وارد کنید
COUNTIF($A$1:$A$10ºA1)=1=
سلام لطفا راهنمایی کنید
وقتی میخوام روی م برم خطای زیر میده
https://s4.uupload.ir/files/2021-11-07_001537_r27n.png
سلام
باید به شکل زیر وارد کنید
COUNTIF($A$1:$A$10ºA1)=1=
سلام و عرض ادب
یه اکسل بابت کلاسهای آموزشی دارم. با تایید یک کارآموز و ثبت “ok” برای ایشان، میخوام یه سری اطلاعات به طور خودکار به شیت های دیگه منتقل بشه.
از ترکیبت توابع index و match رفتم اما اولین شخص تایید شده رو تکرار میکنه.
لطفا راهنمایی کنید.
سلام
چرا وقتی داده از ی فایل دیگه کپی میکنیم، داده تکراری رو تشخیص نمیده؟؟
سلام
چرا وقتی داده از ی فایل دیگه کپی میکنیم، داده تکراری رو تشخیص نمیده؟؟
متوجه منظور نشدم
چنین چیزی غیر ممکن است
سلام
این فرمول اشتباه است
من به هر روشی وارد کردم یا کلا خطا میده واجازه وارد کردن شماره نمیده یا اینکه اصلا به شماره های تکراری ایراد نمیگیره
سلام
شما بجای علامت , از علامت ; استفاده کنین ببینین درست میشه یا نه
سلام دوست عزیز، فرمول صحیح است ولی در این مثال نقطه شروع نوشتن فرمول بسیار حساس است، دقت کننید کدوم سلول فعال می باشد.
میشه راهنمایی کنید همین شرط منحصر به فرد بودن رو در vba چطور باید اعمال کرد
در وی بی ای باید از Event Procedure ها استفاده کنید که یک مقدار فایلتون را کند میکنه و پیشنهاد نمی کنم
سلام…
نگاه کن ببین به دردت میخوره
تو این فایل به دو روش اینکار رو انجام دادم، یکی همین دستوری که آموزش داده شده ولی با استفاده از VBA و دیگری هم با استفاده از جست‌وجو در دستورات VBA
فایل نمونه مثال:
https://s16.picofile.com/file/8425308984/Duplicate.xlsm.html
توی table ها کار نمی کنه، وقتی عدد غیر تکراری وارد می کنم، بازم فکر می کنه تکراری هست.
فرمول را اشتباه وارد کردید
هنگام ورود مراقب محدوده انتخاب شده باشید
سلام
فرمول صحیح برای یک ستون:
=COUNTIF(A:A؛A1)=1
بجای ویرگول از ویرگول بالاش نقطه (سمیکالن) استفاده کنید
این به تنظیمات سیستم در کنترل پنل وابسته است.
سلام بنده قصد دارم از طریق گوشی و برنامه اکسل یک سری کلمات غیرتکراری وارد نمایم چیکار کنم تا از ورود تکراری کلمات جلوگیری نمایم.
یاداور می شوم میخواهم از گوشی در برنامه اکسل استفاده نمایم
ممنون میشم اگه راهنمایی فرمایید
سلام
اگر از اپ اکسل گوشی میخواهید این کار را بکنی، امکان پدیر نیست ولی اگر از طریق گوشی بخواهید این کار را از طریق وب انجام دهید کافیه فایل را در کامپیوتر درست کنید و در دراب باکس بارگدرای کنید
ممنون، استفاده کردم توضیحات عالی بود.
بسیار عالی بود، من واقعا تشکر و سپاسگذارم. متشکرم
خواهش میکنم
ممنونم من نیاز داشتم به این اموزش و ازش استفاده کردم . ممنون اجازه دادین یاد بگیرم
دستت درد نکنه
کمک بزرگی به من کرد
خواهش میکنم
سلام وقت بخیر
داده های من شامل ۴ حرف انگلیسی که میخوام هم جلو تکراری بودنشون رو بگیرم و هم میخوام از این ۴ حرف نشه کمتر یا بیشتر وارد کرد میشه فرمول این کار رو بهم بگید؟
سلام
میشه بفرمایید برای اینکه شماره تلفن تکراری وارد نکنه و اخطار بده از چه تابعی استفاده کنیم
چون این تابع در صورتی که در سلول دو عدد تکراری وجود داشته باشه اخطار میده
مثلا ۰۹۱۲۳۳۴ چون دوتا ۳ داره اخطار میده
سلام
خیر
چنین اتفاقی نمی افتد
دوباره چک کنید
سلام قبلش ‘ بذارید دیگه ارور نمی ده
سلام مشکل من با این فرمول اینه که اکسل پیام میده و میگه نمیشه اول فرمول مساوی داشته باشه و باید از ‘ قبل فرمول استفاده کنی یا مساوی رو پاک کنی بعد که مساوی رو پاک میکنم یا کما میذارم اول فرمول وقتی میخوام تو سلولی مقداری وارد کنم اجازه نمیده و میگه که دیتا وارد شده با دیتا ولیدیشن مچ نیست و نمیشه وارد کرد هر نوع دیتا هم باشه فرق نداره چه عدد چه تکست
منم همین مشکل رو داشتم
فرمول زیر رو کپی بزن فقط ستونت رو خواستی تغییر بده درست میشه. اول مساوی نداره
COUNTIF(B:B;B)=1
دمتون گرم
خیلی خوب بود
چرا برای من وقتی میرم سلول بعدی ارور میده .میخوام توی یک ستون شماره تلفن وارد کنم و اگه شماره تکراری وارد کردم ارور بده چیکار کنم .من مراحل رو درست رفتم ولی سلول به سلول تا شماره وارد میکنم پیغام میاد
دقت بفرمایید در این روش محدوده انتخاب شده و سلول فال (سلولی که اسم ان در پنرجه مدیریت نامها نوشته شده) بسیار اهمیت دارد و ورودی دوم تابعتان حتما باید همان سلول باشد.
باسلام
فرمولی که عرض فرمودین اشتباهه COUNTIF(B:B,B1)=1=
وقتی وارد میکنم ارور میده
COUNTIF(B:B;B)=1=
سلام
آقا مصطفی ،اگه بشرح فوق وارد کرده باشی پاسخ میده.
البته دربعضی کامپیوتر ها بجای”;” باید از”,” استفاده کنید.
من همین که سلول رو پر می کنم خطا می ده
مراقب باشید تمامی مراحل را به درستی انجام دهید.
در این آموزش محدوده انتخاب شده نیز قابل اهمیت است.
سلام
چند مورد را چک بفرمایید
اول اینکه این چیزی که نوشتند اگر در ستون B هست فرمول ها همه B نوشته شود (یعنی در هر ستون همان نوشته حرف نوشته شود)
دوم : بعضی از فرمول ها به جای , از ; استفاده شود
نکته سوم از کل فرمول بعد از مساوی رو توی پرانتز بگذارید مثال: (COUNTIF(D:D;D3)=1)=
بنظرم این کاملتره
سلام این (گذاشتن پرانتز بعد از مساوی) بهترین پاسخی بود که من ازش جواب گرفتم و در هیچ جا این راه حل ارائه نشده بود ضمن اینکه اصلا هم به فکرم نمی رسید
ممنونم
خواهش میکنم