ابزار Data Validation در اکسل یکی از پر کاربرد ترین ابزار ها در فرمول نویسی ها، Formula Sheet ها، نرم افزار های محاسباتی نوشته شده توسط اکسل و… میباشد. توسط ابزار Data Validation در اکسل شما میتوانید کاربر را طوری محدود کنید که فقط داده های مورد نظر شما را در سلول های تعیین شده وارد نماید، به عنوان مثال میتوانید کاربر را محدود کنید در سلولی فقط بتواند عددی زوج وارد کند، یا اینکه فقط اعداد بزرگتر از ۱۳ قابل وارد شدن در سلول باشند یا اینکه کاربر فقط یکی از گزینه های مورد نظر شما را بتواند در سلول بنویسد و بسیاری محدودیت های دیگر که در ادامه با آنها و روش اعمال آنها آشنا میشویم.
ابزار Data Validation در اکسل
بحث ایجاد محدودیت برای وارد کردن اطلاعات در سلول ها از طریق ابزار Data Validation در اکسل را میتوان به صورت خلاصه به شکل نمودار زیر نشان داد:
محدودیت ها در Data Validation در اکسل
برای استفاده از ابزار Data Validation ابتدا سلول مورد نظر را انتخاب نمایید و سپس از تب Data و در گروه Data Tools بر روی دکمه Data Validation کلیک کنید، شکل زیر مسیر دسترسی به ابزار Data Validation در اکسل را مشخص میکند:
گزینه ی اول (Data Validation…) پنجره Data Validation را باز میکند که برای ایجاد محدودیت ها توسط این ابزار به کار برده میشود. با کلیک بر روی گزینه ی دوم (Circle Invalid Data) سلول هایی از صفحه اکسل که ابزار Data Validation در مورد آنها به کار رفته و محتوی داده ای نا معتبر میباشد مشخص میشوند. برای مشخص کردن سلول های دارای داده ی نا معتبر، دور آن ها خطی قرمز ترسیم میگردد که با کلیک بر روی گزینه ی سوم (Clear Validation Circle) خطوط رسم شده پاک میشوند. پنجره Data Validation در شکی زیر نشان داده شده است:
در تب اول پنجره Data Validation در اکسل میتوان تنظیمات مربوط به Validation Criteria را انجام داد، در کادر Allow ابتدا نوع داده یا نوع محدودیت ورود داده به سلول (داده معتبر) را تعیین مینماییم، داده های معتبر را به سه دسته تقسیم کرده ایم، دسته اول شامل گزینه های Whole Number (عدد)، Decimal (عدد علمی)، Date و Time (تاریخ و زمان) و Text Length (طول رشته متنی) میباشد. دسته دوم گزینه List و دسته سوم Custom است.
به عنوان مثال، اگر گزینه ی Whole Number را انتخاب کنیم، به این معنا است که تنها داده های عددی (اعداد) به عنوان محتوای سلول مورد نظر معتبر میباشند.
در کادر دوم (Data) جزییات مربوط به گزینه ی انتخاب شده در کادر Allow تعیین میگردد، جزییات مربوط به گزینه های قرار گرفته در دسته اول (شامل Whole Number، Decimal، Date، Time و Text Length) یکسان میباشد زیرا همگی ماهیتی عددی دارند. در مثال بالا با فرض تعیین Whole Number یا هریک از گزینه های دسته اول در کادر Allow، در این قسمت اعداد و محدوده های مجاز برای ورود به سلول را به ترتیب زیر تعیین میکنیم:
- اگر میخواهیم اعداد بین دو عدد خاص معتبر باشند گزینه ی Between،
- برای معتبر بودن اعداد خارج از بازه دو عدد خاص گزینه ی Not Between،
- برای معتبر بودن تنها یک عدد خاص گزینه ی Equal to،
- برای معتبر بودن اعدادی غیر از یک عدد خاص گزینه ی Not Equal to،
- برای معتبر بودن اعداد بزرگتر از یک عدد خاص گزینه ی Greater Than،
- برای معتبر بودن اعداد کوچکتر از یک عدد خاص گزینه ی Less Than،
- برای معتبر بودن اعداد بزرگتر مساوی یک عدد خاص گزینه ی Greater than or Equal to،
- و در نهایت برای معتبر بودن اعداد کوچکتر مساوی یک عدد خاص گزینه ی Less than or Equal to.
اگر میخواهید سلول مورد نظر در صورت خالی بودن هم مورد بررسی قرار بگیرد تیک گزینه ی Ignore Blank را بردارید.
گزینه ی لیست (List) در کادر Allow، در صورتی که بخواهید کاربر تنها داده هایی خاص که شما تعیین میکنید را بتواند در سلول وارد کند کاربرد دارد، با انتخاب گزینه ی List، کادری به عنوان Source نشان داده میشود که میتوانید لیست داده های دلخواه که در سلول های اکسل نوشته شده است را انتخاب کنید همچنین با تیک زدن گزینه In-Cell Dropdown با انتخاب سلول مورد نظر توسط کاربر، لیست تعیین شده نشان داده میشود که کاربر میتواند گزینه ی دلخواه را از میان آنها انتخاب کند.
تعیین محدودیت دلخواه در Data Validation
تا اینجا با تمام گزینه های تعیین داده ی معتبر برای سلول ها آشنا شدیم. گزینه ی آخر از فهرست Allow گزینه ی Custom میباشد. با استفاده از گزینه ی Custom کاربران میتوانند محدودیت های دلخواه در ورود داده ها به سلول ها را ایجاد نمایند، مثلاً شما میتوانید کاربر را ملزم به وارد نمودن اعداد زوج نمایید و بسیاری کاربرد های دیگر.
نمونه فایل اکسل برای تمرین Data Validation در اکسل را با کلیک بر روی این خط دانلود نمایید.
برای اینکار پس از انتخاب سلول مورد نظر از پنجره Data Validation و در قسمت Allow گزینه ی Custom را انتخاب نمایید. همانند آنچه در Conditional Formatting و ایجاد قوانین دلخواه دیدیم، در اینجا نیز فرمول نوشته شده حتماً باید خروجی Boolean داشته باشد یعنی خروجی فرمول حتماً باید True یا False باشد. بنابراین توابع Is که جز توابع اطلاعات (Information Functions) میباشند در این مقوله بسیار کاربرد دارند از طرفی توابع منطقی (Logical Functions) مانند توابع IF، AND و OR بسیار مهم و کاربردی میباشند. بنابراین توصیه میگردد حتماً مقالات مربوط به این توابع را مطالعه نمایید.
مثلاً برای اینکه سلول A2 را محدود به پذیرش تنها اعداد زوج نماییم میتوانیم پس از انتخاب Custom در قسمت فرمول، فرمول زیر را بنویسیم، به این ترتیب کاربران تنها میتوانند اعداد زوج را در سلول A2 بنویسند:
=ISEVEN(A2)
به همین ترتیب میتوان از سایر توابع و فرمول ها برای ایجاد محدودیت استفاده نمود، حتی میتوان داده های ورودی به یک سلول را طوری محدود کرد که خروجی سلول دیگری که سلول اول به عنوان مرجع (Reference) در آن استفاده شده به عنوان ملاک معتبر بودن تعیین گردد. در فایل نمونه مثال هایی در این مورد آورده شده است.
نمونه فایل اکسل برای تمرین Data Validation در اکسل را با کلیک بر روی این خط دانلود نمایید.
به علت طولانی شدن مطلب، توضیح و روش تعیین Input Message و انواع Error Alert در مقاله ی بعدی مورد بررسی قرار میگیرد.
با سلام
فرمی دارم که به علت زیادی سلولهای آن میبایست با یک کلیک در آن علامت تیک زده شود(نه با دوبار کلیک) خواهشمند است راهنمایی کنید چطور سلول ها را تعریف کنم تا با یک کلیک بر روی هرکدام این کار را انجام دهد.
سلام، برای این کار شما میتوانید از Control ها استفاده کنید، کنترل Check Box برای شما مناسب میباشد. در تب Developer، در گروه Controls از لیست Form Controls گزینه ی Check Box را انتخاب کنید. موفق باشید.
سلام.خسته نباشید.اقا من ی لیست طولانی از شماره تلفن دارم برا.میخوام ی محمدویتی ایجاد کنم ک شماره تکراری توش حذف بشه یا ثبت نشه.از هرکدوم ی مدل داشته باشم چکارش کنم؟
سلام، متشکرم، برای دیدن رکورد های تکراری میتوانید از ابزار Conditional Formatting استفاده نمایید. اینکه داده ی تکراری قابل درج شدن نباشد در اکسل قابل اجراست و باید از Data Validation استفاده شود ولی نیاز به فرمول نویسی حرفه ای دارد. موفق باشید.
سلام وقتتون بخیر ممکنه این فرمولی ک اشاره کردن رو بنویسید؟
با سلام و خسته نباشید
چطور می توانم اعداد شبیه به هم از دو ستون را شناسایی کرده و سپس این اعداد مشابه که در سلولهای مختلف دو ستون قرار دارند را یکی کنم؟ تعداد رکوردها زیاد اند ( ۳۹۰ هزار و …).
سلام، متشکرم، برای این کار باید کد نویسی انجام دهید. در اکسل ممکن نیست.
سلام
به راحتی و با کاندیشنال فرمتینگ سلول های تکراری هر دو ستون رو مشخص کن و بعد از اون هر دو ستون رو بر اساس رنگ سلول سورت کن جداشون کن و هر کاری دوست داشتی انجام بده
با سلام و عرض ادب؛
یکسری داده عددی دارم(۱و۲و۳) که با if تبدیلش کردم به حروف(AوBوC).حالا میخوام این حروف رو با توجه به رتبه ای که دارند تبدیل به حرف شماره کنم.مثلن
۱ A
۱ A
۱ A
۱ A
۲ B
۲ B
۲ B
۲ B
۲ B
۳ C
۳ C
۳ C
۳ C
۱ A
۱ A
۱ A
۱ A
۱ A
۱ A
۳ C
۳ C
حالا میخوام به حروف رتبه بدم..یعنی بهA های تکراری اولی پشت سر هم A1 اختصاص بدم و به A های تکراری پشت سر هم بعدی A2 و به همین ترتیب تا آخر.برای سایر حروف هم همینطور، یعنی به C های پشت سر هم اولی C1 و به C های پشت سر هم بعدی C2 و الی آخر.
۱ A1 A
۱ A1 A
۱ A1 A
۱ A1 A
۲ B1 B
۲ B1 B
۲ B1 B
۲ B1 B
۲ B1 B
۳ C1 C
۳ C1 C
۳ C1 C
۳ C1 C
۱ A2 A
۱ A2 A
۱ A2 A
۱ A2 A
۱ A2 A
۱ A2 A
۳ C2 C
۳ C2 C
تعداد رکوردهام خیلی زیادن و دارم دستی رتبه هارو وارد میکنم…سخته…
خیلی ممنون میشم راهنماییم کنید.
سلام، راهی برای فرمول نویسی این کار به ذهنم نمیرسه، اگر راهی پیدا کردم پاسخ میدم، البته میشه برای این کار کد نویسی کرد، اگر شما راهی پیدا کردید لطفاً در همینجا توضیح دهید. متشکرم، موفق باشید.
مطالب کامل و آموزنده می باشد.
در صورتی که بخواهیم پس پاسخ دهی کاربر به پنجره اول، پنجره دوم براساس قبلی سئول کند به چه شکل است.
سطح۱:
هزینه خودرو
هزینه مسکن
هزینه جاری
مطالب کامل و آموزنده می باشد.
در صورتی که بخواهیم پس پاسخ دهی کاربر به پنجره اول، پنجره دوم براساس قبلی سئول کند به چه شکل است.
سطح۱:
۱ هزینه خودرو
۲ هزینه مسکن
۳ هزینه جاری
سطح۲(مربوط به خودرو):
۲-۱ سوخت
۲-۲ سرویس
۲-۳ تهعمیرات
سطح۳(مربوط به مسکن):
شارژ
کرایه
قبوض
سلام، انجام این کار در اکسل تنها در صورت استفاده از فرمول نویسی ماتریسی ممکن است و به صورت عادی مقدور نیست، اما در صورتی که تمایل به استفاده از کد نویسی داشه باشید کاری ساده است.
سلام من لیست را در دیتا ولیدیشن ایجاد می کنم و لی فونت لیست بسیار کوچک است و به سختی قابل خواندون است
سلام، در اکسل برای تغییر سایز فونت DATA VALIDATION راهی وجود ندارد، از محیط ویژوال بیسیک باید اقدام شود. موفق باشید.
اقا سلام من میخوام تعیین کنم برای مثال سلول c3 حداقل باید دو نویسه وارد شود
باید چیکار کنم؟؟؟؟؟؟
سلام از DATA VALIDATION استفاده کنید. موفق باشید.
سلام
ببخشید من یه سوال دارم
کدام یک از گزینه های data validationنیست؟
الف)جلو گیری ازورود داده براساس فرمولی خاص ب)ایجاد راهنما برای ورود داده ها در سلول ج)نمایش پیام خطا در صورت رعایت نکردن شرط د)تعیین گذر واژه برای حفاظت کار برگ
سلام
د
سلام خدمت مدیر محترم
این سایت گروه تلگرامی نداره؟؟؟؟
اگه داره لطف کنید ادرس بدید
سلام، خیر
ممنون سایت آموزشی بسیار مناسبی است خصوصا سوال و جواب های کاربران و پاسخ گویی های مناسب یک سوال در برنامه اکسل دیتا وریشن دارم جون در گزینه لیست تعداد کاراکترهایی که میتوان وارد کرد محدوده چگونه میتوان آنرا افزایش داد ممنون از پاسخ گویی شما
سلام، خواهش میکنم، لطفاً بفرمایید کدام کاراکترها مد نظر شما هست؟ اگر لیست مورد نظر است، تعداد ۳۲۷۶۷ سلول را میتوان به عنوان لیست به Data Validation داد، اگر این لیست قرار است تایپ شود این تعداد ۲۵۶ کاراکتر است، برای افزایش این محدودیت ها راههایی وود دارد که البته ساده نیستند. در صورت نیاز بفرمایید تا بیشتر توضیح دهم. موفق باشید.
با سلام با تشکر از مطالب مفید سایت ؛بسیار جامع و راه‌گشا ست
از حضورتان سوالی دارم در data validationاکسل با انتخاب گزینه لیست در قسمت سورس تنها محدودی از کاراکتر قابل انتخاب میباشد چگونه میتوان تعداد میزان این کاراکتر ها رو افزایش داد یعنی ۲۰۰تا انتخاب داشته باشیم با تشکر منتظر پاسخ شما هستم
سلام، به صورت کلی تنظیماتی مشابه آنچنه شما نیاز دارید از محیط اکسلل قابل انجام نیست و این موارد به صورت پیش فرض در آن تعریف شده است، احتمالا این کار از محیط ویژوال بیسیک قابل تغییر است که نیاز است در این مورد جستجو کنید، موفق باشید.
سلام وقت بخیر
یک دیتا ست دارم که بعضی از ستون ها بجای عدد دارای کلمه هست مثلا maelوfemaelکه اونهارو باید به صفر و یک تبدیل کنم که این ستون هایی که دوتا مقدار هستن قابله انجامه ولی بعضی ستون ها بیشتر از دومورد هستند مثلاupوdownوsteadyو….یا اینکه۳۰ که نمیدونم چطور اونهارو به عدد ثابت تبدیل کنم که بشه تو متلب ازشون استفاده کرد
لطفا راهنماییم کنید
سلام، با فرمول نویس شرطی میتوانید این کار را انجام دهید، اما بسیاری از موارد باید دستی فرمول نویسی شود و به محل جدید کپی شود، انجام این دست امور با کد نویسی بسیار سریع و کاربردی تر است.
سلام
روز بخیر
اولین بار هست که با این وب سایت برخورد کردم، باید یه خدا قوت بهتون بگم که اینقدر کامل و گویا مطالب رو بیان کردین
من یک مسئله دارم روی اکسل که نتونستم هنوز حلش کنم
ببینید فرض کنید دو تا سلول دارم، کاربر باید توی یکی از سلول ها رشته تحصیلی رو انتخاب کنه و توی سلول بعدی گرایش رو (که خوب هر دوی اینا باید انتخابی باشند نه اینکه توسط کاربر تایپ بشن)، با توجه به اینکه هر رشته تحصیلی، گرایش مرتبط با خودش رو داره، خواستم بدونم چطور می تونم مقادیر سلول دوم (گرایش) رو مشروط کنم روی مقداری که توی سلول اول انتخاب می شه.
ممنون می شم کمکم کنید
سلام، متشکرم، برای انجام این کار چند راه وجود دارد، راه ساده تر این است که شما لیست مربوط به گرایش های هر رشته تحصیلی را در مجم.عه ای از سلول های اکسل بنویسید و با استفاده از ساختار IF و تابع OFFSET در صورت انتخاب هر رشته گرایش ها را انتخاب نمایید. البته فرمول ها باید به صورت ماتریی نوشته شوند و فرمول نویسی عادی پاسخگو نیست، راه دوم استفاده از کد نویسی است. موفق باشید.
سلام بنده پس از تنظیم و اعمال Data Validation و باز و بسته کردن اکسل با خنثی شدن تنظیمات مواجه میشوم لطفا راهنمایی کنید که چکار بکنم تا ماندگار باشد تنظیمات…
سلام، تنظیمات در صورت save کردن نباید بهم بریزد، بنابراین ممکن است نسخه اکسل شما مشکل داشته باشد و در این صورت نیاز از نرم افزار را مجدداً نصب نمایید. موفق باشید.
با عرض سلام و خسته نباشید
ببخشید یه سوال داشتم برای اینکه بتونیم به صورت روزانه به اکسل دیتا وارد کنیم و سر ماه خروجی بده باید چیکار کنم؟
سلام، برای این کار حتماً باید ماکرو نویسی – کد نویسی ویژوال بیسیک – انجام دهید. موفق باشید.
سلام. برای یک سلول دیتا ولیدیشن را اعمال کرده ام به نحوی که اگر عدد بیش از ۱۸ وارد سلول شود پیغام خطا بدهد. مشکلم اینه که اگه عدد ۲۰ را از یک سلول دیگر به این سلول کپی کنم، پیغام خطا نمیده. لطفا راهنمایی ام کنید. تشکر
سلام، Data Validation مانند یک فرمت سلول رفتار میکند، وقتی شما برای سلولی Data Validation را اعمال میکنند مانند این است که فونت سلول را Bold نمایید. با این اوصاف زمانی که شما سلولی را در یک سلول دارای Data Validation کپی مینمایید در واقع Data Validation را از بین میبرید و خواص سلول کپی شده را در سلول جدید قرار میدهید. برای انجام موفق این کار باید Paste Special انجام دهید و مثلاً تنها Value را Paste نمایید. موفق باشید.
با کپی کردن ، درسته که اگه عدد اشتباهی باشه error نمیده ولی با زدن دکمه circle invalid data میشه موارد اشتباه کپی شده رو مشاهده کرد. درست مثل این می مونه که داده ها رو وارد کنی بعد data validation رو بزنی که به داده ها اعمال نمیشه و فقط موقعی که گزینه ی زده بشه میشه به موارد اشتباه پی برد. بنابراین نمیشه گفت که با کپی کردن ، خواصِ سلولِ مقصد از بین میره.
ممنون از توضیحات شما، به هر حال Data Validation جز خواص سلول ها هست که قابل Over Write شدن میباشد و در کپی و Paste کردن میتواند از بین برود.
سلام آیا پنجره دیتا با کیبرد هم باز میشود؟با چه دکمه ای؟
سلام، خیر تا جایی که من میدانم باید بر روی دکمه مورد نظر در تب Data و در گروه Data Tools کلیک نمایید. موفق باشید.
بله باز می شود:
ابتدا کلید Alt
سپس کلید A
بعد از آن کلید V
و بازهم کلید V را فشار دهید تا باز شود.
پس از آنکه پنجره Data Validation باز شد با استفاده از کلید Tab می توانید پیمایش کنید.
لازم به ذکر است کلید Alt کاربردهای فراوانی در کیبورد دارد.
موفق باشید.
متشکرم از توضیحات شما
سلام لیستی در data validation ساختم میخواهم ویرایش کنم لطفا راهنمایی کنید چگونه اینکار را انجام دهم
سلام، از تب DATA وارد DATA VALIDATION شده و لیست را ویرایش نمایید. اگر لیست در سلول ها نوشته شده که تنها کافیست آن سلول ها را صالاح نمایید. موفق باشید.
با سلام و تشکر از همکاریتون
چطور میتونم دو تا دیتا ولیدیکیشین را به هم ربط بدم به طوری که وقتی یکی از سلول های دیتا رو انتخاب کردم سلول مجاور اتوماتیک تایپ شود .
سلام، برای این کار باید فرمول نویسی انجام دهید، میتوانید از توابعی مانند IF یا CHOOSE برای انتخاب یک عبارت استفاده کنید. موفق باشید.
سلام
من میخوام تویه یه فیلد ۲۴ عدد وارد کنم که اگر ۲۳ عدد شد اخطار بده و اگر ۲۵ عدد شد بازم اخطار بده.
الان با آموزش شما من Text Length رو انتخاب و بعد Equal to انتخاب کردم و تویه فیلد ۲۴ وارد کردم ارور میده
تا ۱۱ عدد مشکلی نداره ولی بیشتر ارور میده و تعداد ۲۴ عدد وارد شده بهم ریخته میشه
ممنون میشم راهنمایی کنید
سلام، علت Error احتمالا این هست که Text Length برای Text به کار میرود و با توجه به اینکه شما عدد وارد میکنید به مشکل میخورد، پیشنهاد من این است که از محدودیت عددی استفاده کنید و Between دو عدد را انتخاب کنید، برای مورد شما عدد مجاز در بازه بزرگتر و مساوی ۱۰ به توان ۲۳ تا کوچکتر از ۱۰ به توان ۲۴ میباشد. به این صورت تنها اعدادی که ۲۴ عدد هستند قابل ورد هستند.
پیش از انجام این کار، در روش پیشنهادی خودتان، بررسی کنید که اگر فرمت سلول را تغییر دهید و آن را به Text تبدیل کنید آیا همچنان Error قبلی را دریافت میکنید یا خیر
موفق باشید.
data validation چه نکاتی دارد