اعتبارسنجی داده در اکسل؛ ساخت فهرست کشویی و جلوگیری از ورودی اشتباه
وقتی سه نفر وضعیت یک سفارش را به شکلهای «ارسال شد»، «ارسالشده» و «فرستاده شد» وارد میکنند، گزارش شما ممکن است سه گروه جدا نشان بدهد. مشکل همیشه در فرمول نیست؛ گاهی داده از لحظه ورود با قرارداد تیم هماهنگ نبوده است. اعتبارسنجی داده در اکسل کمک میکند برای ورودیها قاعده مشخص بسازید و کاربر هنگام ثبت اطلاعات راهنمایی دریافت کند. کاربرد روشن آن برای یک کسب و کار کوچک، یکسان کردن وضعیتها، محدود کردن تعداد به مقدار معتبر و کاهش اصلاح دستی فایلهای مشترک است.
در این راهنما یک دفتر ثبت درخواست انبار فرضی میسازیم. هدف، خرید نرمافزار تازه یا طراحی سامانه پیچیده نیست؛ میخواهیم بفهمیم چه ورودیهایی باید کنترل شوند، قاعده چگونه تنظیم شود و از کجا بدانیم کنترل واقعاً کار میکند. مثالها و اعداد، آموزشی هستند و نتیجه مطالعه آماری یا اطلاعات واقعی فروشگاه نیستند. مراحل اصلی برای اکسل دسکتاپ با رابط انگلیسی نوشته شدهاند؛ نام و جای بعضی گزینهها در مک، نسخه وب یا زبانهای دیگر ممکن است تفاوت داشته باشد.
اعتبارسنجی داده چه مسئلهای را حل میکند؟
این قابلیت را مانند قرارداد ورود اطلاعات ببینید: یک ستون قرار است چه چیزی بپذیرد و کاربر هنگام اشتباه چه راهنمایی ببیند؟ مثلاً «تعداد درخواستی» باید عدد صحیح مثبت باشد و «وضعیت رسیدگی» باید از واژههای توافقشده انتخاب شود. بر اساس راهنمای رسمی مایکروسافت درباره اعمال اعتبارسنجی، مسیر Data سپس Data Validation امکان تعریف قواعدی مانند عدد، فهرست و طول متن را فراهم میکند. این قابلیت جای بررسی صحت کسب و کاری هر درخواست را نمیگیرد.
عدد ۲۰ میتواند از نظر نوع داده صحیح باشد اما برای سفارشی که فقط دو قلم نیاز دارد نادرست باشد. انتخاب «تحویل شد» از فهرست نیز ثابت نمیکند کالا واقعاً تحویل شده است. بنابراین دو پرسش جدا بپرسید: آیا ورودی با قالب و محدوده مورد توافق سازگار است؟ آیا برای این رویداد مشخص، واقعیت را درست بیان میکند؟ اعتبارسنجی عمدتاً به پرسش اول کمک میکند و پرسش دوم همچنان به سند، مسئول مشخص و بازبینی نیاز دارد.
پیش از تنظیم اکسل، قرارداد ستونها را بنویسید
یک نسخه آزمایشی از فایل تهیه کنید و برای هر ستون بنویسید چه کسی آن را پر میکند، چه زمانی لازم میشود و کدام مقدارها معتبرند. از همه ستونها شروع نکنید. ابتدا دو ستونی را انتخاب کنید که بیشترین اصلاح دستی یا اختلاف گزارش را ایجاد میکنند. در مثال انبار، «وضعیت» و «تعداد» انتخابهای اولیه هستند. شناسه درخواست را نیز به عنوان مرجع پیگیری نگه میداریم تا پس از اصلاح، ردیفها قابل ردیابی باشند.
| ستون | قاعده توافقی | نمونه قابل قبول | مسئول تصمیم |
|---|---|---|---|
| شناسه درخواست | شناسه متنی طبق روش شمارهگذاری تیم | REQ-001 | ثبتکننده |
| تعداد | عدد صحیح بین ۱ و ۵۰۰ | ۱۲ | مسئول انبار |
| وضعیت | فقط یکی از چهار وضعیت مصوب | در حال بررسی | مسئول رسیدگی |
| شرح استثنا | توضیح برای درخواست خارج از روال | نیاز به تأیید خرید ویژه | سرپرست |
حد بالای ۵۰۰ در این مثال صرفاً سیاست فرضی همان تیم است. آن را بدون دلیل به فایل خود منتقل نکنید. اگر سفارشهای واقعی شما گاهی از این حد بیشترند، باید مسیر ثبت استثنا داشته باشید؛ وگرنه کاربر برای عبور از محدودیت، مقدار را میشکند یا در ستون دیگری مینویسد. قاعده خوب، خطای معمول را روشن میکند و برای مورد غیرمعمول مسیر پاسخگویی دارد.
ساخت فهرست کشویی برای وضعیت درخواست
در یک برگه به نام Lists چهار سلول متوالی را با این عبارتها پر کنید: «ثبت اولیه»، «در حال بررسی»، «آماده تحویل» و «تحویل شد». هر عبارت فقط یک بار بیاید و میان گزینهها سلول خالی نگذارید. سپس این چهار سلول را انتخاب کنید و از Formulas و Define Name، نامی مانند StatusList بسازید. استفاده از نام مشخص، فهمیدن منبع فهرست را برای نگهدارنده بعدی آسانتر میکند؛ نام را با حروف لاتین و بدون فاصله انتخاب کنید.
در برگه اصلی، محدوده وضعیتها، مثلاً D2:D200، را انتخاب کنید. در Data Validation، بخش Allow را روی List قرار دهید و در Source بنویسید =StatusList. گزینه In-cell dropdown باید فعال باشد. در راهنمای ساخت فهرست کشویی مایکروسافت نیز انتخاب محدوده مقصد و تعیین منبع گزینهها توضیح داده شده است. پس از تأیید، در یک سلول مقصد فهرست را باز کنید و هر چهار گزینه را بررسی کنید.
فهرست را از واژههایی بسازید که افراد واقعاً میفهمند. اگر «تکمیل» برای یک نفر به معنی پایان بررسی و برای دیگری به معنی تحویل کالا باشد، یکسان شدن نوشتار مشکل گزارش را حل نمیکند. کنار فایل، تعریف هر وضعیت و مسئول تغییر آن را بنویسید. همچنین به خاطر داشته باشید محدوده ثابت D2:D200 فقط همین ردیفها را پوشش میدهد؛ پیش از اضافه کردن داده بیرون از آن، پوشش قاعده را گسترش دهید و دوباره آزمایش کنید.
تعیین قاعده تعداد و نوشتن پیام قابل اجرا
برای ستون تعداد، محدوده C2:C200 را انتخاب کنید. در Data Validation نوع Whole Number، شرط Between، کمینه 1 و بیشینه 500 را قرار دهید. در Input Message توضیح کوتاهی مانند «تعداد واحد کالا را به صورت عدد صحیح وارد کنید» بنویسید. در Error Alert نیز پیام مشخصی قرار دهید: «تعداد باید بین ۱ تا ۵۰۰ باشد؛ برای مقدار بیشتر با مسئول انبار هماهنگ کنید». پیام باید راه اصلاح را بگوید، نه فقط عبارت مبهم «ورودی اشتباه است» را نمایش دهد.
برای ورودی تایپی که باید اصلاح شود، سبک Stop را انتخاب کنید. طبق توضیح مایکروسافت درباره پیامهای اعتبارسنجی، سبک Warning امکان پذیرفتن مقدار نامعتبر را به کاربر میدهد؛ بنابراین صرف ظاهر شدن هشدار به معنی جلوگیری از ثبت نیست. همین تفاوت کوچک را در آزمون بررسی کنید. اگر تیم تصمیم دارد بعضی مقادیر فقط هشدار بگیرند، آن تصمیم و مسئول بازبینی بعدی باید مشخص باشد.
مثال کامل: از درخواست اشتباه تا ردیف قابل گزارش
فرض کنید کارگاه کوچکی در پایان روز درخواستهای مصرف قطعات را جمع میکند. در ردیف دوم، شناسه REQ-001، نام کالا «بسته پیچ» و تعداد ۱۲ وارد میشود. ثبتکننده از فهرست وضعیت، «ثبت اولیه» را انتخاب میکند. مسئول رسیدگی پس از کنترل درخواست، وضعیت را به «در حال بررسی» تغییر میدهد. همه افراد بر یک واژه مشترک توافق دارند و گزارش وضعیتها نیازمند تبدیل چند عبارت هممعنی نیست.
اکنون در ردیف سوم، تعداد ۲٫۵ را تایپ کنید. چون واحد این کالا «بسته کامل» تعریف شده، در مثال ما مقدار کسری پذیرفته نیست و باید پیام اصلاح نمایش داده شود. سپس ۱۲ را وارد کنید تا مسیر معتبر هم آزمایش شود. اگر کالای دیگری با واحد کیلوگرم دارید، همان قاعده عدد صحیح برای آن مناسب نیست. یا ستون و فرم جدا طراحی کنید، یا قاعده را پس از روشن شدن نیاز واقعی بازطراحی کنید؛ تغییر واحد را پشت یک محدودیت ثابت پنهان نکنید.
برای آزمون وضعیت، عبارت «ارسال شد» را مستقیم در سلول مقصد تایپ کنید. انتظار این است که با تنظیم Stop، ورود تایپی خارج از فهرست پذیرفته نشود. اگر پذیرفته شد، ابتدا مطمئن شوید همان سلول تحت قاعده قرار دارد، منبع فهرست درست است و پیام خطا فعال شده است. بعد از اصلاح، آزمون را از ابتدا تکرار کنید. یک بار تنظیم کردن قاعده بدون امتحان کردن مسیر اشتباه، شواهد کافی برای آماده بودن فایل نیست.
کپی و جایگذاری: محدودیتی که باید جدی آزمایش شود
یک سلول بیرون از محدوده بسازید و در آن «نامشخص» بنویسید؛ سپس آن را در ستون وضعیت کپی کنید. مایکروسافت در راهنمای شناسایی داده نامعتبر تصریح میکند پیامهای اعتبارسنجی برای ورود مستقیم طراحی شدهاند و در مسیرهایی مانند کپی یا پر کردن سلول، ورود نامعتبر ممکن است بدون همان پیام رخ دهد. بنابراین فهرست کشویی را دیوار قطعی محافظ داده تصور نکنید.
در نسخه دسکتاپی که این گزینه را دارد، از منوی Data Validation، فرمان Circle Invalid Data را برای بررسی داده موجود اجرا کنید. این ابزار مقدارهایی را که با قواعد موجود ناسازگارند مشخص میکند. اما اگر عمل جایگذاری خود قاعده را نیز حذف کرده باشد، کنترل صرف مقدار کافی نیست؛ باید وجود قاعده در سلول مقصد را هم بررسی کنید. نوع جایگذاری و رفتار نسخه واقعی اکسل تیم را در فایل آزمایشی ثبت کنید. پاک کردن علامتهای بررسی نیز به معنی اصلاح داده نیست.
برای دادههای حجیم، یک برگه دریافت جدا در نظر بگیرید. داده خام ابتدا آنجا قرار بگیرد، مسئول مشخص موارد غیرعادی را بررسی کند و سپس داده پذیرفتهشده به دفتر اصلی منتقل شود. در این مسیر، نمونهای از ردیفهای انتقالیافته و قواعد سلولهای مقصد را دوباره کنترل کنید. اگر اطلاعات از CSV میآید، راهنمای وارد کردن CSV در اکسل و حفظ صفر شناسهها نیز به مرحله پیش از اعتبارسنجی مربوط است؛ عددی شدن شناسه را با محدود کردن وضعیت نمیتوان جبران کرد.
سناریوی دوم: یکسان کردن وضعیت پیگیری مطالبات
در دفتر مطالبات، همکاران ممکن است «تماس گرفته شد»، «پیگیری تلفنی» و «صحبت شد» را برای یک رویداد بنویسند. پیش از ساخت فهرست، روشن کنید ستون مورد نظر «اقدام آخر» را ثبت میکند یا «وضعیت طلب» را. این دو یکی نیستند: تماس تلفنی یک اقدام است، اما وعده پرداخت یا تسویه کامل وضعیت متفاوتی دارد. اگر هر دو را در یک فهرست بریزید، گزارش شما از نظر واژه منظم اما از نظر معنا مبهم خواهد بود.
پیشنهاد عملی این است که برای اقدام آخر یک ستون مستقل و برای وضعیت پرونده ستون دیگری داشته باشید. تاریخ اقدام و توضیح توافق نیز جدا ثبت شود. ابتدا چند پرونده واقعی را با مسئول وصول مرور کنید و ببینید آیا هر کدام در یکی از گزینهها جا میگیرد. موارد اختلاف را یادداشت کنید و پس از توافق فهرست را نهایی کنید. صرفاً برای کوتاه کردن فهرست، وضعیتهای واقعاً متفاوت را ادغام نکنید؛ هدف، گزارش قابل تصمیم است، نه زیبایی ظاهر سلول.
خطاهای رایج در نگهداری فایل
اولین خطا، افزودن گزینه تازه بدون توضیح به تیم است. اگر «در انتظار تأیید» به فهرست اضافه شد، معلوم کنید چه زمانی استفاده میشود و چه نسبتی با «در حال بررسی» دارد. دومین خطا، حذف گزینه قدیمی بدون بررسی سوابق است. ممکن است دادههای پیشین به همان گزینه وابسته باشند. پیش از هر تغییر، نسخه پشتیبان بگیرید و تصمیم بگیرید سوابق قبلی حفظ شوند یا با قاعده مشخص تبدیل شوند؛ اصلاح دستهجمعی بدون ثبت دلیل، پیگیری تغییر را دشوار میکند.
سومین خطا، اتکا به رنگ است. سبز بودن سلول نشان نمیدهد مقدار درست وارد شده یا سند پشتیبان بررسی شده است. چهارمین خطا، سخت کردن همه ستونها از روز اول است؛ این کار ممکن است مسیر ثبت استثنا را نامعلوم کند و کاربران را به نگهداری فایل جانبی سوق دهد. پنجمین خطا، فراموش کردن ردیف تازه است. آخرین ردیف محدوده و نخستین ردیف بیرون از آن را عمداً آزمایش کنید تا مرز پوشش برای شما روشن باشد.
چکلیست تحویل فایل به تیم
- تعریف هر ستون، واحد مقدار و مسئول ثبت نوشته شده است.
- گزینههای فهرست تکراری و مبهم نیستند و منبعشان مشخص است.
- ورودی معتبر، ورودی نامعتبر و مقدار مرزی آزمایش شدهاند.
- کپی و جایگذاری و افزودن ردیف در نسخه واقعی تیم امتحان شده است.
- دادههای قبلی و وجود قواعد در محدوده مقصد بازبینی شدهاند.
- برای مقدار خارج از روال، مسئول پاسخ و مسیر ثبت استثنا تعیین شده است.
- نسخه پشتیبان و یادداشت تغییرات پیش از تحویل آماده است.
چطور نتیجه را بسنجیم؟
یک دوره کوتاه پیش از تغییر و دوره مشابهی پس از آن را انتخاب کنید. برای نمونه، از هر دوره صد ردیف ثبتشده را با روش یکسان بررسی کنید و تعداد ردیفهایی را که به اصلاح وضعیت یا تعداد نیاز دارند بشمارید. نرخ اصلاح را از تقسیم تعداد ردیف نیازمند اصلاح بر تعداد ردیف بررسیشده و ضرب در صد به دست آورید. اگر در نمونه فرضی اول ۱۲ ردیف و در نمونه دوم ۵ ردیف مشکل داشته باشند، نرخها ۱۲ و ۵ درصدند؛ این اعداد فقط نمونه محاسبه هستند.
زمان بازبینی، تعداد درخواستهای استثنا و نظر ثبتکنندگان را نیز کنار نرخ اصلاح یادداشت کنید. کاهش خطا همراه با کند شدن شدید ثبت، الزاماً به معنی طراحی مناسب نیست. اگر حجم کار، افراد یا نوع سفارشها عوض شده، کاهش مشاهدهشده را به تنهایی به اعتبارسنجی نسبت ندهید. نمونهها را با توضیح شرایط نگه دارید و پس از تغییر فهرست دوباره بررسی کنید. نتیجه قابل اتکا از ترکیب قاعده روشن، آزمون مسیرهای واقعی و بازبینی منظم به دست میآید.
برای شروع امروز، فقط یک ستون پرخطا را انتخاب کنید، پنج ورودی آزمایشی بنویسید و رفتار مورد انتظار هر کدام را ثبت کنید. سپس قاعده را روی نسخه آزمایشی اعمال کنید و از یک همکار بخواهید بدون راهنمایی شفاهی از آن استفاده کند. هر جا مجبور شدید توضیح بدهید، پیام یا تعریف ستون را بهتر کنید. وقتی این چرخه کوچک جواب داد، دامنه را گسترش دهید؛ فایل قابل نگهداری حاصل تصمیمهای روشن و آزموده است.
پیشنهاد از فروشگاه راه ایده آل




دیدگاهها