نحوه استفاده از اعتبارسنجی داده ها در گوگل شیت
در دنیایی که به طور فزاینده‌ای مبتنی بر داده است، حفظ داده‌های دقیق و سازگار برای تصمیم‌گیری مؤثر و گردش‌های کاری عملیاتی بسیار مهم است. چه در حال مدیریت وظایف پروژه باشید و چه در حال پیگیری موجودی محصول، حتی اشتباهات یا ناسازگاری‌های جزئی نیز می‌توانند منجر به مشکلات قابل توجهی شوند. با اعتبارسنجی داده ها در Google Sheets، یک ابزار صفحه گسترده آنلاین برای تجزیه و تحلیل و مدیریت داده‌ها، می‌توانید با محدود کردن داده‌هایی که می‌توانید بر اساس قوانین از پیش تعریف شده در برگه کاری خود وارد کنید، از صحت و یکپارچگی داده‌های خود اطمینان حاصل کنید.
طبق گزارشی از Precisely و کالج تجاری LeBow دانشگاه Drexel، 64 درصد از سازمان‌ها کیفیت داده‌ها را به عنوان یک چالش رایج برای حفظ یکپارچگی داده‌ها ذکر می‌کنند. این امر بر اهمیت داشتن داده‌های با کیفیت بالا، دقیق و قابل اعتماد برای وظایف تجزیه و تحلیل داده‌ها تأکید می‌کند. اعتبارسنجی داده‌های Google Sheets می‌تواند به شما در انجام این کار کمک کند.
نحوه استفاده از اعتبارسنجی داده ها در Google Sheets و همچنین تکنیک‌های پیشرفته اعتبارسنجی داده‌ها و نحوه حل مشکلات رایج اعتبارسنجی داده‌ها را بررسی کنید.
اعتبارسنجی داده‌ها در گوگل شیت چیست؟
اعتبارسنجی داده ها به شما این امکان را می‌دهد که با ایجاد قوانین از پیش تعریف‌شده که مقادیری را که می‌توانید در سلول‌ها وارد کنید محدود می‌کنند، از عدم وجود خطا در داده‌های صفحه گسترده خود اطمینان حاصل کنید. با تعیین معیارهای خاص برای نوع مقداری که در هر سلول مجاز می‌دانید، می‌توانید مطمئن شوید که برگه‌های کاری شما فقط حاوی داده‌های مجاز و مناسب هستند. پارامترهایی که می‌توانید تنظیم کنید شامل اعداد، تاریخ، متن و فرمول‌های سفارشی است. به عنوان مثال، می‌توانید یک محدودیت مقدار عددی برای یک سلول تعیین کنید تا فقط اعداد را در یک محدوده مشخص شده وارد کند.
همچنین می‌توانید گوگل شیت را طوری برنامه‌ریزی کنید که اگر کاربری داده‌هایی را وارد کند که معیارهای از پیش تعیین‌شده شما را برآورده نمی‌کند، یک پیام خطا نمایش دهد و کیفیت داده‌ها را حفظ کند. علاوه بر این، می‌توانید از یک لیست کشویی برای جلوگیری از تایپ غلط‌های املایی یا تایپی کاربران استفاده کنید و به آنها اجازه دهید فقط از گزینه‌های ارائه شده شما انتخاب کنند.
این تضمین می‌کند که داده‌های صفحه گسترده شما دقیق و سازگار هستند و می‌توانند بینش‌های قابل اعتمادی در مورد تجزیه و تحلیل داده‌ها ارائه دهند. به این ترتیب، افراد یا مشاغلی که نیاز به مدیریت حجم زیادی از داده‌ها دارند، می‌توانند در زمان و تلاشی که صرف انجام دستی بررسی داده‌ها می‌شود، صرفه‌جویی کنند.
چه زمانی باید از اعتبارسنجی داده ها استفاده کنید؟
تابع اعتبارسنجی داده ها می‌تواند با اطمینان از عدم ورود اطلاعات نادرست به داده‌هایتان، به شما در انجام تحلیل‌ها و مدل‌های دقیق کمک کند. اعتبارسنجی داده‌ها همچنین زمانی مفید است که نیاز دارید برگه یا کتاب کار خود را با چندین نفر به اشتراک بگذارید و از سازگاری داده‌های ورودی آنها اطمینان حاصل کنید.
شما می‌توانید از اعتبارسنجی داده‌ها زمانی استفاده کنید که می‌خواهید:
- ورود داده‌ها را در یک لیست ساختارمند و ثابت در سراسر برگه کار خود نگه دارید
- ورود اعداد خارج از یک محدوده خاص را محدود کنید
- ورود تاریخ‌ها یا زمان‌ها را خارج از یک بازه زمانی خاص محدود کنید
- یک لیست کشویی از گزینه‌های از پیش تعریف شده ایجاد کنید، به عنوان مثال، انتخاب دپارتمان‌ها
- تعداد کاراکترهای متن را در یک سلول محدود کنید
تنظیم اعتبارسنجی داده ها در Google Sheets
برای تنظیم اعتبارسنجی داده‌ها در Google Sheets، ابتدا باید قانونی را تعریف کنید که تعیین کند چه مقادیری را در صفحه گسترده خود مجاز می‌دانید. در مرحله بعد، باید یک لیست کشویی از این گزینه‌های مجاز، یا از یک محدوده یا با وارد کردن مقادیر از پیش تعریف شده، ایجاد کنید. این مراحل را در زیر به تفصیل بررسی کنید.
افزودن یک rule در اعتبارسنجی داده‌ها
گوگل شیت به شما امکان می‌دهد با تایپ کردن مقادیر مجاز در یک لیست اعتبارسنجی یا با ایجاد یک کاربرگ جدید که شامل لیستی از مقادیر مجاز است، یک قانون برای اعتبارسنجی داده‌ها تعیین کنید. اگر تعداد زیادی مقادیر مجاز دارید، باید ایجاد یک کاربرگ جدید برای این مقادیر را در نظر بگیرید، زیرا ورود دستی این مقادیر برای کل لیست به 256 کاراکتر محدود می‌شود.
اگر می‌خواهید یک کاربرگ جداگانه با مقادیر مجاز اضافه کنید، مراحل زیر را برای تعریف یک قانون اعتبارسنجی داده‌ها دنبال کنید:
1. در workbook گوگل خود که داده‌های برنامه خود را در آن دارید، یک workbook جدید برای مقادیر مجاز اضافه کنید. به عنوان مثال، اگر workbook شما شامل وظایف مدیریت پروژه است، می‌توانید workbook داده اصلی خود را ProjectData و workbook حاوی مقادیر مجاز را DataValues نامگذاری کنید.
2. در این workbook جدید، با فرض اینکه بیش از یک مجموعه دارید، یک ستون برای اولین مجموعه مقادیر مجاز خود اختصاص دهید و یک سربرگ ستون اضافه کنید. به عنوان مثال، در کاربرگ DataValues، می‌توانید ستون A را انتخاب کنید و وضعیت پروژه را به عنوان سربرگ در سلول A1 تایپ کنید.
3. در زیر این ستون، تمام مقادیر مجاز را وارد کنید. برای مثال بالا، این مقادیر ممکن است در سلول‌های A2 تا A5 «تکمیل‌شده -Completed»، «در حال انجام-In progress»، «مسدودشده-Blocked» یا «در حال بررسی-Under review» باشند.
4. در برگه داده اصلی خود، که در اینجا ProjectData است، به ستونی که حاوی مقادیر مجاز در برگه‌های DataValues است بروید و تمام سلول‌هایی را که می‌خواهید rule خود را در آنها اعمال کنید، به جز سرستون، انتخاب کنید. به عنوان مثال، این ممکن است ستون C باشد و شما سلول‌های C2 تا C15 را با حذف سلول سرستون C1 انتخاب می‌کنید.
5. به منوی Data بروید و روی Data Validation کلیک کنید. این کار پنجره Data validation rules را باز می‌کند.
6. روی + Add rule کلیک کنید.
ایجاد یک لیست کشویی از یک محدوده
7. فیلد Apply to range را علامت بزنید تا مطمئن شوید که محدوده صحیح سلول‌ها را برای درج منوهای کشویی انتخاب کرده‌اید. برای مثال بالا، این فیلد باید ProjectData!C2:C15 را نمایش دهد.
8. در منوی Criteria، Dropdown (from a range) را انتخاب کنید. یک فیلد متنی در زیر ظاهر می‌شود.
9. در این فیلد متنی، روی آیکون کادر در سمت راست به نام Select data range کلیک کنید. کادر محاوره‌ای Select a data range باز خواهد شد.
10. در حالی که این کادر محاوره‌ای باز است، به workbook حاوی لیست مقادیر مجاز، که در مثال DataValues است، بروید و آن را باز کنید.
11. محدوده مناسب سلول‌ها را در ستون حاوی مقادیر مجاز انتخاب کنید. خواهید دانست که یک محدوده را انتخاب کرده‌اید زیرا فیلد متنی در کادر محاوره‌ای Select a data range چیزی را نمایش می‌دهد. در مثال بالا، این باید شامل «تکمیل‌شده -Completed»، «در حال انجام-In progress»، «مسدودشده-Blocked» یا «در حال بررسی-Under review» در سلول‌های A2 تا A5 به عنوان محدوده باشد که به صورت DataValues!A2:A5 ظاهر می‌شود.
12. در کادر محاوره‌ای روی OK کلیک کنید. کادر محاوره‌ای «data range dialog» بسته می‌شود و پنجره قوانین اعتبارسنجی داده، محدوده‌ای را که انتخاب کرده‌اید در بالای نام مواردی که در این محدوده بودند، نمایش می‌دهد. این مقادیر مجاز برای اعتبارسنجی را تشکیل می‌دهد.
13. در پایین پنجره قوانین اعتبارسنجی داده، روی «Done» کلیک کنید.
یک لیست کشویی برای مقادیر از پیش تعریف‌شده اضافه کنید
اگر مقادیر مجاز زیادی ندارید و با تایپ دستی مقادیر مجاز خود مشکلی ندارید، می‌توانید مراحل زیر را برای تعریف یک قانون و اضافه کردن یک لیست کشویی برای داده‌های خود دنبال کنید.
مثال بالا را در نظر بگیرید، که در آن یک برگه داده به نام ProjectData با تمام داده‌های اصلی خود دارید و مراحل زیر را برای تنظیم یک لیست کشویی برای مقادیر از پیش تعریف شده دنبال کنید:
1. یک یا چند سلول را که می‌خواهید در آنها یک منوی کشویی ایجاد کنید، انتخاب کنید. به عنوان مثال، برگه داده شما ستون A را به عنوان لیست وظایف دارد، که در آن A1 سربرگ با نام وظایف از A2 تا A6 است. در ستون B، می‌خواهید یک منوی کشویی بر اساس اولویت هر وظیفه اضافه کنید، بنابراین سربرگ Priority را در B1 اضافه می‌کنید. سپس، B2 تا B6 را به عنوان محدوده سلول‌هایی که لیست‌های کشویی خواهند داشت، انتخاب کنید.
2. روی Data، سپس Data validation و در نهایت + Add rule کلیک کنید. فیلد Apply to range در پنجره Data validation rules، ProjectData!B2:B6 را برای مثال بالا نمایش می‌دهد.
3. در قسمت Criteria، Dropdown را انتخاب کنید.
4. فیلدهای قابل ویرایش “Option 1″، “Option 2” و غیره را مشاهده خواهید کرد. این نام‌ها را به نام‌هایی که می‌خواهید لیست کشویی شما نمایش داده شود، تغییر دهید. می‌توانید بر روی «Add another item» کلیک کنید تا فیلدهای قابل ویرایش بیشتری را مطابق نیاز خود اضافه کنید. برای مثال، در مثال بالا، می‌توانید برای گزینه‌های اولویت وظیفه، «بحرانی-Critical»، «زیاد-High»، «معمولی-Normal» و «کم-Low» وارد کنید.
5. پس از وارد کردن مقادیر، روی «Done» کلیک کنید.
تکنیک‌های پیشرفته اعتبارسنجی داده‌ها
تکنیک‌های پیشرفته اعتبارسنجی داده‌ها به شما این امکان را می‌دهند که با گنجاندن منوهای کشویی وابسته و پیام‌های خطا برای ورودی‌های نامعتبر، اعتبارسنجی داده‌ها را بهبود بخشید.
استفاده از اعتبارسنجی داده‌های پویا
اعتبارسنجی داده‌های پویا شامل استفاده از منوهای کشویی وابسته است که در آن انتخاب‌ها در یک لیست کشویی به طور خودکار بر اساس انتخاب‌ها در لیست کشویی دیگر تغییر می‌کنند و به شما امکان می‌دهند صفحات گسترده انعطاف‌پذیرتر و کارآمدتری ایجاد کنید. می‌توانید با استفاده از محدوده‌های نامگذاری شده و فرمول INDIRECT در Google Sheets به این هدف دست یابید.
برای درک بهتر نحوه استفاده از اعتبارسنجی داده‌های پویا، مثال موجودی محصول را که دارای دو دسته است در نظر بگیرید: لباس و لوازم جانبی. هر دسته دارای سه زیردسته است: لباس، تاپ و شلوار جین برای لباس و کفش، کیف و جواهرات برای لوازم جانبی. شما باید دو لیست کشویی ایجاد کنید، یکی برای دسته و دیگری برای زیردسته، که در آن مقادیر کشویی بر اساس انتخاب شما در منوی کشویی اول تغییر می‌کنند. می‌توانید خلاصه‌ای از مراحل راه‌اندازی اعتبارسنجی داده‌های پویا را در زیر بیابید:
1. فهرستی از دسته‌ها و زیردسته‌های خود را برای دو منوی کشویی آماده کنید.
۲. اولین لیست کشویی خود را در یک سلول جداگانه با استفاده از نام دسته‌ها به عنوان محدوده ایجاد کنید.
3. برای هر دسته، محدوده‌های نامگذاری شده ایجاد کنید. مطمئن شوید که نام محدوده با نام دسته شما یکسان باشد.
4. در سلول دیگری، از تابع INDIRECT برای اشاره به سلولی که شامل لیست کشویی اول است استفاده کنید.
5. لیست کشویی دوم خود را با استفاده از محدوده موجود در فرمول INDIRECT ایجاد کنید، که یک لیست کشویی پویا ایجاد می‌کند، که در آن گزینه‌های موجود در لیست کشویی دوم بر اساس انتخاب‌های شما در لیست کشویی اول تغییر می‌کنند.
تنظیم پیام‌های خطا برای ورودی نامعتبر
شما می‌توانید گوگل شیت را طوری برنامه‌ریزی کنید که در صورت نامعتبر بودن ورودی، پیام خطایی نمایش دهد.
1. پس از وارد کردن محدوده داده‌ها و معیارهای کشویی خود در پنجره قوانین اعتبارسنجی داده‌ها، روی گزینه‌های Advanced کلیک کنید.
2. در اینجا، می‌توانید گزینه نمایش هشدار را انتخاب کنید که به کاربر اجازه می‌دهد داده‌های نامعتبر را وارد کند اما به او پیام هشداری مبنی بر عدم تطابق ورودی با قوانین می‌دهد، یا گزینه رد ورودی را انتخاب کنید که هیچ داده نامعتبری را نمی‌پذیرد.
3. همچنین می‌توانید اگر می‌خواهید به کاربر اطلاع دهید که چرا ورودی‌اش نامعتبر است، گزینه نمایش متن راهنما را برای سلول انتخاب شده تیک بزنید.
عیب‌یابی مشکلات رایج اعتبارسنجی داده ها
هنگام اعتبارسنجی داده‌ها در Google Sheets، ممکن است با مشکلات یا پیام‌های خطای خاصی مواجه شوید. برخی از مشکلات رایج عبارتند از:
- گزینه‌های کشویی به درستی نمایش داده نمی‌شوند: مطمئن شوید که محدوده‌ای که برای منوی کشویی انتخاب کرده‌اید شامل تمام موارد مرتبط است و هیچ ردیف یا ستون پنهانی را انتخاب نکرده‌اید.
- اعتبارسنجی داده‌ها کار نمی‌کند: مطمئن شوید که اعتبارسنجی داده‌ها را در محدوده صحیح اعمال کرده‌اید، داده‌ها را کپی پیست نمی‌کنید و هر فرمولی که استفاده می‌کنید بدون خطا است.
- ورودی داده‌ها رد شد: مطمئن شوید که معیارهای اعتبارسنجی دقیقی را وارد کرده‌اید و هرگونه غلط املایی یا فاصله اضافی را حذف کرده‌اید.
خلاصه مراحل
اعتبارسنجی داده ها در Google Sheets می‌تواند به شما کمک کند تا مطمئن شوید که داده‌های دقیق و بدون خطا را در صفحات گسترده خود وارد می‌کنید. می‌توانید اعتبارسنجی داده‌ها را با دنبال کردن مراحل زیر تنظیم کنید:
- محدوده‌ای از سلول‌ها را برای اعتبارسنجی انتخاب کنید
- روی Data → Data validation کلیک کنید و یک قانون اعتبارسنجی اضافه کنید
- مقادیر از پیش تعریف شده را به صورت دستی یا مقادیری از لیست دیگر اضافه کنید
- تنظیمات برگزیده خود را برای مدیریت خطا اضافه کنید
- لیست‌های کشویی خود را ذخیره و آزمایش کنید
مقاله های مرتبط:
1- نمودارهای پویای گوگل شیت (Google Sheets Dynamic Charts)
2- ابزارهای گزارش دهی کلان داده یا Big Data
3- راه حل های یکپارچه سازی داده ها – کلید یک عملیات موفق
4-داشبورد سازی در نرم افزار تبلو و تجسم داده ها