آموزش کاربردی اکسل–معرفی کامل دیتا ولیدیشن (Data validation) در نرم افزار اکسل

در این مقاله از مجله قرمز، به این موضوع می‌پردازیم که دیتا ولیدیشن (Data validation) چیست و چه کاربردی دارد. چگونه می‌توانیم داده‌های نامعتبر را تشخیص و از ورود آن‌ها به اکسل جلوگیری کنیم، منوی کشویی چگونه ساخته می‌شود و در کل، نکات کاملی در مورد دیتا ولیدیشن (Data validation) در اکسل به شما ارائه خواهیم داد. اگر می‌خواهید در نرم افزار اکسل به سطح پیشرفته‌تری دست پیدا کنید، پیشنهاد می‌کنیم حتماً کار با دیتا ولیدیشن را یاد بگیرید.

معرفی دیتا ولیدیشن

دیتا ولیدیشن (Data validation)، یک قابلیت در نرم افزار اکسل است که به کاربر کمک می‌کند کنترل بر روی آن چیزی که درون هر سلول وارد می‌کند، داشته باشد. به عنوان مثال، شما می‌توانید از دیتا ولیدیشن برای اطمینان از اینکه تمامی داده‌ها درون هر سلول عددی بین ۱ تا ۶ است، استفاده کنید، یا اینکه مطمئن شوید که چیزی که درون سلول نوشته می‌شود، تعداد کاراکترش کمتر از ۲۵ باشد و بسیاری موارد دیگر؛

با استفاده از دیتا ولیدیشن، به کاربری که در حال وارد کردن داده‌ها در اکسل است می‌گویید که مجاز است چه داده‌هایی را وارد هر سلول در نرم افزار اکسل کند. همچنین اگر کاربر در حال وارد کردن داده‌های اشتباه به درون یک سلول باشد، می‌توانید با استفاده از دیتا ولیدیشن به او اخطار دهید که این داده را نمی‌تواند وارد سلول کند. علاوه بر این‌ها، با استفاده از دیتا ولیدیشن می‌توانید چند مقدار را در یک سلول با استفاده از کشویی کردن آن جای دهید.

نحوه کار با دیتا ولیدیشن (Data validation)

برای شروع کار، ابتدا در تب دیتا، می‌توانید دیتا ولیدیشن را پیدا کنید.

“ هشدار: به یاد داشته باشید که دیتا ولیدیشن یک اشکال اساسی دارد. آن هم این است که اگر کاربر، داده‌ها را از قسمتی که دیتا ولیدیشن برای آن تعریف نشده “کپی” کند و به قسمتی که دیتا ولیدیشن برای آن تعریف شده بیاورد، عملکرد دیتا ولیدیشن با مشکل مواجه می‌شود. بنابراین، همیشه با دیتا ولیدیشن نمی‌توانید صد در صد مطمئن شوید که تمامی داده‌ها معتبرند.”

برای شروع، ابتدا بر روی دیتا ولیدیشن کلیک کنید.

سپس مشاهده می‌کنید که سه تب پیش روی شما وجود دارد: Setting، Input message وError Alert

در قسمت Validation Criteria (شاخص یا قواعد اعتبارسنجی) به اکسل می‌فهمانید که چه داده‌هایی برای شما معتبر محسوب می‌شوند. می‌توانید از بین لیست انتخاب کنید و یا Custom را بزنید به این معنا که خودتان فرمولی را برای اعتبار داده‌ها مشخص کنید.

در تب بعدی (Input message)، می‌توانید تعیین کنید که وقتی کاربر بر روی یک سلول کلیک می‌کند تا داده‌ای را وارد آن کند، چه پیامی برای وی نمایش داده شود. این پیام به کاربر می‌گوید که او مجاز به وارد کردن چه چیزی درون هر سلول است. این تب کاملاً اختیاری است و می‌توانید چیزی برای آن تعریف نکنید تا پیامی هم به کاربر موقع وارد کردن داده‌ها نشان داده نشود.

در تب آخر (Alert Tab) در صورتی که کاربر داده‌ای را به اشتباه وارد کند، اخطاری برای وی نمایش می‌دهد.

در این صورت، اگر کاربر عددی خارج از محدوده‌ی معتبر برای ما، وارد کند پیام زیر برای او نمایش داده می‌شود.

توجه: در تب Alert در قسمت Style سه گزینه را می‌توانید انتخاب کنید. در ادامه، تفاوت‌های این سه گزینه را مشاهده می‌کنید.

راهنمایی بیشتر در مورد تب Setting در دیتا ولیدیشن

توضیحات مربوط به هر کدام از این موارد در زیر آورده شده است:

Any Value: به این معنا که هر داده‌ای که وارد شود صحیح و معتبر است.

Whole Number: فقط اعداد صحیح (بدون اعشار) قابل قبول هستند.

وقتی این گزینه را فعال کنید، می‌توانید به اکسل بفهمانید که فقط اعداد صحیح و مثلاً در محدوده‌ی ۱ تا ۱۰ (یا هر محدوده‌ی دیگری) قابل قبول هستند.

Decimal: اعداد اعشاری قابل قبول هستند.

وقتی این گزینه را فعال کنید، می‌توانید به اکسل بفهمانید که اعداد اعشاری و مثلاً در محدوده‌ی ۱ تا ۳ (یا هر محدوده‌ی دیگری) قابل قبول هستند.

List: فقط اعدادی که در لیستی که از قبل تهیه شده‌است قرار دارند، قابل قبول هستند. اعداد نیز به صورت لیست کشویی به کاربر نشان داده می‌شوند. به عبارت دیگر، وقتی کاربر می‌خواهد یک عدد را وارد یک سلول کند، می‌تواند از منوی کشویی، اعدادی که در لیست قبلی قرار دارند را انتخاب کند و یکی از آن‌ها را برگزیند.

Date: در این حالت، فقط وارد کردن تاریخ قابل قبول است. محدوده‌ی تاریخ قابل قبول را نیز می‌توانید وارد کنید. به عنوان مثال، شما می‌توانید هر تاریخ را بین ۱ ژانویه‌ی ۲۰۱۸ و ۳۱ دسامبر ۲۰۲۱ در سلول‌ها وارد کنید.

Time: فقط اعداد به صورت زمان، قابل قبول هستند. به عنوان مثال، شما می‌توانید یک زمان بین ۹ صبح تا ۵ بعد از ظهر را تعیین کنید.

Text Length: در این حالت، فقط کاراکتر یا حرف قابل قبول است. مثلاً شما می‌توانید برای اکسل تعریف کنید که داده‌های ورودی باید فقط ۵ حرفی باشند.

Custom: در این حالت، می‌توانید داده‌هایی را قابل قبول تعریف کنید که در فرمول دلخواهتان صدق کنند. مثلاً شما می‌توانید فرمولی را بنویسید که طبق آن، کاربر باید داده‌هایی را وارد کند که حتماً ۰۹ در ابتدای آن قرار داشته باشد.

توجه: برای نوشتن فرمول در این قسمت، باید فرمول‌های اکسل را بشناسید. برای مثال، اگر بخواهید به صورت فرمولی به اکسل بگویید که فقط اعداد قابل قبول و معتبر باشند و نه کلمات، می‌توانید از فرمول زیر استفاده کنید:

=ISNUMBER (A1)

در این صورت، اگر کاربر برای مثال عدد ۱۰ را وارد کند، این فرمول برآورده شده و اکسل خطا نمی‌گیرد اما اگر کلمه‌ی “سیب” یا هر کلمه‌ی دیگری را وارد کند، اکسل آن را به عنوان عدد نپذیرفته و خطا می‌دهد.

نکته: اگر فرمولی را در دیتا ولیدیشن نوشتید و اکسل به شما اخطار می‌داد که فرمول نوشته شده اشتباه است اما شما نمی‌دانستید کجای این فرمول اشتباه است، یک راه فهمیدن اینکه کجای فرمول اشتباه است این است که از دیتا ولیدیشن خارج شوید و فرمول مورد نظر را در تب فرمول عمومی بنویسید و ببینید چه خطایی در مورد آن وجود دارد و بعد که تمام خطاها را اصلاح کردید، همان فرمول اصلاح شده را کپی کرده و در قسمت دیتا ولیدیشن paste نمایید.

همچنین دو مورد نیز در کنار قرار دارند که می‌توانید آن‌ها را تیک بزنید:

Ignore Blank: اگر این گزینه را علامت بزنید، به اکسل می‌گویید که اگر کاربر داده‌ای را وارد یک سلول نکرد، آن سلول را نادیده بگیرد. کاربرد این گزینه در حالتی است که بخواهید نرم افزار دور داده‌های نامعتبر را خط بکشد (Circle Invalid Data). در این حالت، اگر این گزینه را تیک زده باشید، اکسل دور سلول‌هایی که فاقد داده هستند (سلول‌های خالی) خط نخواهد کشید.

Apply these changes to other cells with the same settings: اگر این گزینه را علامت بزنید، در صورتی که تغییراتی را در اعتبار یک سلول ایجاد کرده باشید، اکسل برای تمامی سلول‌ها که همین اعتبار قبلی برای آن‌ها تعریف شده بود، نیز این تغییرات را اعمال می‌کند.

ایجاد منوی کشویی ساده

فرض کنید می‌خواهید وقتی کاربر قصد دارد چیزی درون هر سلول وارد کند، از بین ۳ گزینه‌ی “خرید، فروش، رزرو” بتواند انتخاب کند و این گزینه‌ها به صورت کشویی برای وی نمایش داده شوند. برای این کار، می‌توانید به قسمت دیتا ولیدیشن، و سپس تب Setting بروید، گزینه‌ی List را انتخاب کرده و در قسمت Source مقادیر “خرید، فروش و رزرو” را با قرار دادن کاما (ویرگول) بین آن‌ها، تعیین نمائید. توجه داشته باشید که حتماً کاما را باید به انگلیسی وارد کنید.

در این صورت، منوی کشویی در آن سلول، به صورت زیر خواهد بود.

راه دیگری که برای ایجاد منوی کشویی وجود دارد، این است که در قسمت Source، داده‌های یک لیست را وارد کنید. مثلاً می‌خواهید سایزهای Small، Medium و Large را به صورت کشویی در بیاورید تا کاربر برای وارد کردن آن‌ها به هر سلول، فقط کافی باشد منوی کشویی را باز کرده و از بین این سه گزینه به راحتی انتخاب کند. مراحل کار در شکل زیر نشان داده شده است.

استفاده از دیتا ولیدیشن برای خط کشیدن دور داده‌های نامعتبر

شما می‌توانید برای اکسل تعریف کنید که دور داده‌های نامعتبر خط بکشد. برای این کار، به قسمت دیتا ولیدیشن رفته و گزینه‌ی Circle Invalid Data را انتخاب کنید. در این صورت، اکسل دور داده‌های نامعتبر خط خواهد کشید.

پیدا کردن سلول‌هایی که دیتا ولیدیشن برای آن‌ها تعریف شده

برای این که بدانید در فایل اکسل شما، برای کدام سلول‌ها دیتا ولیدیشن تعریف شده (یا به عبارت دیگر، هر داده‌ای را نمی‌توانید وارد این سلول‌ها کنید)، می‌توانید کنترل + G را بزنید و سپس روی Special کلیک کنید.

بعد از آن، در پنجره‌ی باز شده دیتا ولیدیشن را تیک بزنید.

چگونه دیتا ولیدیشن را از یک سلول به سلول دیگر کپی کنیم؟

اگر می‌خواهید دیتا ولیدیشنی را که برای یک سلول تعریف کرده‌اید، به سلول‌های دیگری نیز اعمال کنید و یا به اصطلاح کپی کنید، سلول مورد نظر را کپی کرده و سپس Paste Special را بزنید.

بعد از آن در پنجره‌ی مورد نظر Validation را تیک بزنید.

چگونگی پاک کردن تمامی دیتا ولیدیشن‌ها

برای این که تمامی دیتا ولیدیشن‌هایی که تعریف کرده‌اید را پاک کنید، ابتدا سلول‌هایی را که می‌خواهید فاقد دیتا ولیدیشن باشند را انتخاب کنید و سپس در قسمت دیتا ولیدیشن و تب Setting، گزینه‌ی Clear All را بزنید.

دوست عزیز، اگر هر گونه سوال و یا انتقاد و پیشنهادی در مورد آموزش اکسل در مجله قرمز دارید، می‌توانید در قسمت نظرات در پایین این مطلب با ما در میان بگذارید. سوالات خودتان را در مورد اکسل نیز با ما مطرح کنید تا در حد سوادمان پاسخگوی شما دوست گرامی باشیم.

ترجمه و تالیف اختصاصی مجله قرمز

با کمک از منبع: exceljet.net