ارزیابی داده ها در اکسل

آخرین به‌روزرسانی: 27 دی 1401

در سری مقاله های آموزش اکسل، در فصل گذشته به داده های جغرافیایی و سهام پرداختیم، در این مقاله به بررسی ارزیابی داده ها در اکسل می‌پردازیم.

کارهایی که انجام می‌دهیم اغلب شامل واردکردن مقادیر کسل‌کننده‌ای از اعداد می‌شود. وقتی اطلاعات زیادی را وارد نرم‌افزار مایکروسافت اکسل می‌کنیم، اشتباهات به‌سادگی رخ می‌دهند. ویژگی ارزشیابی دادها (Data Validation) در اکسل 2019 می‌تواند به طرز فوق‌العاده‌ای شانس اینکه اشتباهی هزینه بر انجام گیرد را بسیار کاهش دهد.

سؤال‌هایی که در این فصل پاسخ داده شده‌اند:

• امتیازهای مربوط به تیم‌های بسکتبال حرفه‌ای را در اکسل وارد می‌کنم. می‌دانیم که امتیازهای هر تیم در هر بازی از 50 تا 200 امتیاز هستند. یک‌بار به‌جای امتیاز 100 عدد 1000 را وارد کردم که باعث شد تحلیلم به هم بریزد. آیا راهی وجود دارد که از بروز چنین اشتباه‌هایی جلوگیری شود؟
• در حال درج تاریخ و میزان هزینه‌های کسب‌وکار برای سال جدید هستم. اغلب در ابتدای سال ندانسته در ردیف تاریخ، سال گذشته را درج می‌کنم. آیا راهی وجود دارد که اکسل را به شکلی تنظیم کنم که مرا از چنین نوع اشتباه‌هایی بازدارد؟
• فهرست طولانی‌ای از اعداد را وارد می‌کنم. آیا می‌توانم کاری کنم که اکسل به هنگام واردکردن مقداری غیر عددی به من هشدار بدهد؟
• دستیارم می‌بایست به هنگام واردکردن داده‌های چندین و چند مبادلات فروش، نام ایالات را به‌صورت مخفف وارد کند. آیا می‌توان فهرستی از نام‌های مخفف ایالات تنظیم کرد تا شانس نادرست واردکردن عبارات مخفف توسط وی را به حداقل رساند؟

برای تنظیم این ویژگی محدوده سلولی را که می‌خواهید ارزشیابی داده‌ها بر آن اعمال شود را انتخاب کنید. سپس در تب Data روی گزینه Data Validation (در گروه Data Tools) کلیک کنید و سپس معیار مورد نظری را که اکسل از آن استفاده می‌کند تا داده‌های نامعتبر وارد شده را نشان کند انتخاب کنید. (این کار را در مثال‌های همین فصل خواهید دید)

پاسخ به سؤالات این فصل

امتیازهای مربوط به تیم‌های بسکتبال حرفه‌ای را در اکسل وارد می‌کنم. می‌دانم که امتیازهای هر تیم در هر بازی از 50 تا 200 امتیاز هستند. یک‌بار به‌جای امتیاز 100 عدد 1000 را وارد کردم که باعث شد تحلیلم به هم بریزد. آیا راهی وجود دارد که از بروز چنین اشتباه‌هایی جلوگیری شود؟

بیایید فرض کنیم می‌خواهید تعداد امتیازهای کسب شده توسط تیم میزبان در سلول‌های A2:A11 وارد کنیم، در سلول‌های B2:B11 تعداد امتیازهای کسب شده توسط تیم مهمان را وارد می‌کنید. (موارد انجام شده برای این مسئله را در فایلی به نام Nbadvl.xksx خواهید دید) می‌خواهیم مطمئن شویم که هریک از مقادیر وارد شده در محدوده A2:B11 اعدادی صحیح از 50 تا 200 هستند.

کار را با انتخاب محدوده A2:B11 انتخاب می‌کنیم و سپس در تب Data گزینه Data Validation را انتخاب می‌کنیم. همان‌طور که در تصویر 1-42 نشان‌داده‌شده، در کادر محاوره‌ای Data Validation ابتدا تب Setting را انتخاب کرده و از فهرست ارائه شده در بخش Allow گزینه Whole Number یا عدد صحیح را انتخاب و سپس کادر محاوره‌ای را به همان شیوه که می‌بینید پر می‌کنیم.

پاسخ از پیش تعیین شده اکسل به داده‌های نامعتبر (که اخطار خطا نامیده می‌شود) پیامی با متن “داده وارد شده معتبر نیست، کاربر مقادیری که می‌توانید وارد سلول کنید را محدود کرده است” است. می‌توانید از تب Error Alert در کادر محاوره‌ای Data Validation استفاده کنید (تصویر 2-42 را ببینید) تا آیکون، عنوان کادر پیام و متن پیام آن پیغام خطا را به‌دلخواه تغییر دهید.

در تب Input Message می‌توانید پیغامی فوری ایجاد کنید که کاربر را درمورد نوع داده‌ای که می‌تواند بدون مشکل وارد کند اطلاع دهد. این پیام به شکل یک یادداشت در سلول انتخاب شده نمایش داده می‌شود. به‌عنوان‌مثال در اینجا پیام اخطار خطایی وارد کرده‌ایم که می‌گوید: لطفاً عدد صحیحی بین 50 و 200 وارد کنید. می‌بینید پس از واردکردن عددی که خلاف این معیار است (به‌عنوان‌مثال عدد 34 در سلول E5) پیام در تصویر شماره 43-3 نشان‌داده‌شده است.

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

فرض کنید در ابتدای سال 2019 هستید و تاریخ را در محدوده سلولی A2:A20 وارد می‌کنید (فایلی به نام Datedv.xlsx را مشاهده کنید) به‌سادگی محدوده A2:A20 را انتخاب کرده و از گروه Data Tools در تب Data گزینه Data Validation را انتخاب کنید. تب Setting در کادر محاوره‌ای Data Validation را همان‌طور که در تصویر 4-42 نمایش‌داده‌شده پر کنید: در اینجا ما از بخش گزینه‌های Allow گزینه Date را انتخاب کرده و از فهرست گزینه‌های Data گزینه Greater Than Or Equal To(بزرگ‌تر یا مساوی با) را انتخاب می‌کنیم سپس تاریخ 1/1/2019 را به‌عنوان تاریخ شروع قرار داده و روی گزینه Ok کلیک می‌کنیم.

ازاین پس دیگر اجازه واردکردن هیچ تاریخی زودتر از 1/1/2019 نخواهید داشت.

در این حالت اگر تاریخی را در این محدوده سلول وارد کنید که پیش از اول ژانویه سال 2019 باشد، در مورد این خطا اخطاری دریافت خواهید کرد. مثلاً واردکردن تاریخ 1/15/2018 در سلول A3 همان اخطار خطایی را که تعیین کرده بودید را ظاهر می‌سازد.

فهرست طولانی‌ای از اعداد را در اکسل وارد می‌کنم. آیا می‌توانم کاری کنم که اکسل به هنگام واردکردن مقداری غیر عددی به من هشدار بدهد؟

برای استفاده از توانایی‌های کامل ویژگی ارزشیابی داده‌ها نیاز به استفاده از تنظیمات دستی (Custom Setting) دارید. وقتی که گزینه Custom را از فهرست بخش Allow در تب Setting در کادر محاوره‌ای Data Validation انتخاب می‌کنید (تصویر 5-42 را ببینید) از فرمولی برای مشخص‌کردن داده‌های معتبر استفاده می‌کنید. فرمولی که برای ارزشیابی داده‌ها وارد می‌کنید درست مثل فرمولی که برای قالب‌بندی شرطی استفاده می‌کنید و در فصل 24 به نام قالب‌بندی شرطی توضیح داده شد عمل می‌کند.

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

برای نمایش استفاده از تنظیمات دستی (Custom Setting) اجازه دهید فرض کنیم که می‌خواهید مطمئن شوید که هریک از مدخل‌های محدوده سلولی B2:B20 یک عدد محسوب می‌شود (فایلی به نام Numberdv.xlsx را مشاهده کنید) کلید حل این مسئله استفاده از تابع ISNUMBER است. تابع ISNUMBER چنانچه به سلولی حاوی مقادیر عددی اشاره کند عبارت True یا صحیح را برمی‌گرداند. اما چنانچه تابع به سلولی ارجاع دهد که حاوی مقادیری غیر عددی باشد عبارت False یا نادرست را برمی‌گرداند.

بعد از انتخاب محدوده سلول B2:B20 و قراردادن نشانگر ماوس در سلول B2 تب Data را ظاهر کرده و در گروه گزینه‌های Data Tools روی Data Validation کلیک کنید و سپس تب Setting در کادر محاوره‌ای Data Validation را همان‌طور که در تصویر 5-42 نشان‌داده‌شده تکمیل کنید. در اینجا در قسمت گزینه‌های Allow گزینه Custom را انتخاب کرده و در کادر مخصوص فرمول‌ها فرمول: =ISNUMBER(B2) را وارد می‌کنیم.

اکنون چنانچه سعی کنید مقداری غیر عددی در محدوده B2:B20 وارد کنید با پیغام خطا روبرو خواهید شد. مثلاً اگر عبارت Jhon را در سلول B3 وارد کنید اخطار خطا دریافت خواهید کرد.

اگر هنگامی که روی سلول B3 کار می‌کنید بر گزینه Data Validation کلیک کنید فرمول)نشان‌داده‌شده در تصویر 5-42 به شکل =ISNUMBER(B3) نمایش داده می‌شود. این نشان می‌دهد که فرمول وارد شده در سلول B2 به شکل صحیح کپی شده است. واردکردن عبارت Jhon در سلول B3 باعث می‌شود فرمول =ISNUMBER(B3) عبارت نادرست (False) را برگرداند و در نتیجه با پیغام خطا روبرو خواهید شد.

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

کلید حل این مسئله ارزشیابی داده استفاده از معیار List Validation از میان گزینه‌های Allow است. کار را با واردکردن فهرستی از نام‌های مخفف ایالات آغاز می‌کنیم. فایلی به نام Statedv.xlsx را مشاهده نمایید. در این مثال از محدوده I6:I55 استفاده کرده و نام آن را abbrev می‌گذاریم. سپس محدوده‌ای را که می‌خواهیم نام‌های مخفف ایالات را در آن وارد کنیم را انتخاب می‌کنیم.

در این مثال از محدوده D5:D156 استفاده می‌کنیم. پس از کلیک کردن بر روی گزینه Data Validation در تب Data، کادر محاوره‌ای Data Validation را همان‌طور که در تصویر 6-42 نشان‌داده‌شده پر می‌کنیم: در اینجا از میان گزینه‌های بخش Allow گزینه Selected List را انتخاب می‌کنیم، فرمول =abbrev را در کادر Source وارد کرده و روی Ok کلیک می‌کنیم.

حالا هروقت سلولی را در محدوده D5:D156 انتخاب کنید، با کلیک کردن روی پیکان بازکننده منوی کشویی همان‌طور که در تصویر 7-43 می‌بینید فهرستی از نام‌های مخفف ایالات نشان داده می‌شود. تنها نام‌های مخففی که در این لیست می‌بینید مقادیر معتبر در این محدوده محسوب می‌شوند. اگر از منوی کشویی استفاده نکنید و به‌جای آن نام مخففی را تایپ کنید چنانچه نام مخفف غلطی را تایپ کرده باشید (مثلاً ALK برای آلاسکا) با پیغام خطا مواجه خواهید شد.

ملاحظات:

• چنانچه دکمه F5 را فشار دهید، در کادر محاوره‌ای Go To روی گزینه Special کلیک کرده و سپس روی Data Validation کلیک کنید، اکسل تمامی سلول‌های دارای تنظیمات ارزشیابی داده را انتخاب می‌کند. همچنین، می‌توانید از کادر محاوره‌ای Go to Speaicl برای انتخاب تمامی سلول‌های دارای قالب‌بندی شرطی استفاده کنید.
• در نسخه اکسل ماقبل Excel 2010 چنانچه می‌خواستید از منوی کشویی ارزشیابی داده بر اساس منبع داده فهرست شده در کاربرگ متفاوتی استفاده کنید می‌بایست آن فهرست را برای استفاده در منوی کشویی نام‌گذاری کنید (همان‌طور که در مثال این فصل انجام دادیم). در اکسل 2010 و نسخه‌های مابعد آن این محدودیت حذف شده است.
• چنانچه از تکنیک محدوده پویا (Dynamic-range) که در فصل 22 بنام تابع OFFSET توضیح داده شد استفاده کنید، تغییراتی (اضافه‌کردن یا حذف موارد) را که بر فهرست منابع داده انجام می‌دهید به شکل خودکار در منوی کشویی منعکس می‌شوند. (مسئله شماره 10 را ببینید) همچنین، اگر از منبع داده‌های خود جدولی بسازید (فصل 26 بنام جداول را ببینید) تغییرات انجام شده در منبع داده‌ها تا وقتی که به محدوده فهرست اشاره کرده و سعی نکنید که نام جدول را تایپ کنید، زیرا در منوی کشویی منعکس خواهد شد.
• فرض کنید می‌خواهیم از منویی کشویی برای انتخاب شرکتی که به آن آب‌نبات می‌فروشید استفاده کنیم. به منوی کشویی دیگری نیازمندیم تا از آن برای انتخاب لیست آب‌نبات‌هایی که می‌خواهیم به شرکت منتخب بفروشیم استفاده کنیم. مشکل اینجاست که ممکن است همان نوع آب‌نبات‌ها به هرکدام از فروشگاه‌ها فروخته نشوند. چگونه می‌توانیم چنین انتخاب تودرتویی از فهرست‌ها انجام دهیم؟ فرض کنید که فروشگاه‌ها Target و CVS هستند. ما نام محدوده target را به فهرست آب‌نبات‌هایی که در فروشگاه Target فروخته می‌شوند و نام محدوده cvs را به فهرست آب‌نبات‌هایی که در فروشگاه CVS فروخته می‌شوند اختصاص می‌دهیم. اگر منوی کشویی انتخاب فروشگاه مثلاً در سلول A20 باشد می‌توانیم با کلیک روی Data Validation و واردکردن فرمول =INDIRECT(A20) برای فهرست انتخاب شده، منوی کشویی مناسب را در سلول B20 ایجاد کنیم. همان‌طور که در فصل 23 بنام تابع INDIRECT گفتیم، اگر سلول A20 جزو محدوده CVS باشد، آنگاه فهرست تمام محدوده CVS را که حاوی تمام آب‌نبات‌های فروخته شده در CVS است را قفل می‌کند. مسئله شماره 11 را ببینید.
• برای حذف ارزشیابی داده از یک محدوده، آن را انتخاب کرده، Data Validation را در تب Data انتخاب کنید و سپس روی گزینه Clear All کلیک کنید.
• اگر فهرست بلندبالایی دارید، احتمالاً دوست دارید از خاصیت AutoComplete اکسل استفاده کنید. اگر محدوده را از منوی کشویی در سلول انتهایی فهرست آغاز کنید (سلول خالی پذیرفته نمی‌شود) ویژگی AutoComplete به کار می‌افتد. فایلی به نام xlsx را ببینید.
• می‌توانید همچنین، از ارزشیابی داده برای تنظیم معیارهایی بسته به طول متن درون سلول (مسئله شماره 4 را ببینید) و یا زمان روز (مسئله شماره 15 را ببینید) استفاده کنید.

مسئله‌های این فصل:

می‌خواهیم اعداد صحیح مثبت را در محدوده سلول C1:C20 وارد کنیم. ارزشیابی داده‌ها را به‌گونه‌ای تنظیم کنید که مطمئن باشید هر ورودی یک عدد صحیح مثبت است.

در محدوده سلول‌های C1:C15 تاریخ مبادلاتی که در تاریخ ژوئیه سال 2004 انجام شده‌اند را وارد می‌کنید. ارزشیابی داده‌ها را به‌گونه‌ای تنظیم کنید که مطمئن باشید هر تاریخ وارد شده در ژوئیه 2004 رخ‌داده است.

می‌توان با گزینه Text Lenght (از بخش Allow) در تب Setting در کادر محاوره‌ای Data Validation پیام خطایی ایجاد کرد که وقتی تعداد کاراکترهای یک سلول با عددی که تعیین شده برابر نباشند ظاهر شود. از گزینه Text Lenght استفاده کنید تا اطمینان حاصل نمایید که هر سلول در محدوده C1:C10 حاوی حداکثر 5 کاراکتر است (بعلاوه فضای خالی یا space)

نام‌های کارمندان را در محدوده سلولی A1:A10 وارد می‌کنید. از ارزشیابی داده‌ها استفاده کنید تا مطمئن شوید که نام کارمندان بیش از دو بار وارد نشده باشد. راهنمایی: می‌توانید از تنظیمات Custom و همچنین، تابع COUNTIF استفاده کنید.

شماره کد کالاهایی را در محدوده سلولی A1:A15 وارد می‌کنید. این شماره کد می‌بایست همیشه با کاراکترهای xyz پایان گیرد. از ارزشیابی داده‌ها استفاده کنید تا مطمئن شوید هریک از کدهای محصولات وارد شده با حروف xyz پایان می‌پذیرند. راهنمایی: از تنظیمات Custom و تابع RIGHT استفاده کنید.

فرض کنید می‌خواهید هریک از مداخل محدوده‌های سلولی B2:B15 تنها حاوی متن باشد و نه مقدار عددی. از ارزشیابی داده‌ها استفاده کنید تا مطمئن شوید که واردکردن مقداری عددی باعث ظاهرشدن پیغام خطا می‌شود. راهنمایی: از تابع ISTEXT استفاده کنید.

از معیار ارزشیابی داده‌هایی استفاده کنید تا مطمئن شوید که تمامی عددهای تایپ شده در ستون E حاوی دقیقاً دو رقم اعشاری هستند. راهنمایی: از توابع LEN و FIND استفاده کنید.

فایلی به نام Latitude.xlsx حاوی فرمولی برای محاسبه فاصله بین دو شهر با استفاده از طول و عرض جغرافیایی است. فایل همچنین، حاوی طول و عرض جغرافیایی شهرهای مختلفی از ایالات متحده است. یک منوی کشویی تنظیم کنید تا به‌وسیله آن وقتی شهری را در سلول P2 و شهری دیگر در سلول Q2 انتخاب می‌کنید فاصله میان آن دو شهر در سلول Q10 محاسبه شود.

در مسئله شماره 9 مطمئن شوید که اگر شهرهای جدیدی به فهرست شهرها اضافه شود، منوی کشویی شهرهای جدید را در خود نشان دهد.

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

1. کاربران را قادر سازید تا بتوانند نام فروشگاه را از فهرستی کشویی در سلول B19 انتخاب کنند.
2. منویی کشویی در سلول C13 ایجاد کنید تا به کاربران اجازه دهد تا بتوانند نوع آب‌نبات را از لیستی حاوی تنها آب‌نبات‌هایی که در فروشگاه انتخابی فروخته می‌شوند انتخاب کند. راهنمایی: هنگام تعیین فهرست از تابع INDIRECT استفاده کنید.
3. اگر فروشگاه را در سلول B19 تغییر دهید ممکن است سلول C19 به طور موقت فهرستی از آب‌نبات‌ها را که در فروشگاه‌هایی که به‌تازگی انتخاب شده‌اند فروخته می‌شوند را نشان ندهد. مطمئن شوید که چنانچه مورد مطلوب انتخاب شود در سلول C19 عبارت “مورد بالا را انتخاب کنید” نمایش داده شود. مثلاً اگر سلول B19 حاوی فروشگاه CVS و سلول 13 حاوی عبارت آب‌نبات آدامسی بود پس سلول C19 می‌بایست عبارت: “مورد بالا را انتخاب کنید” را نمایش دهد.

100000 دلار پول برای هزینه مخارج دارید. در ستون A هزینه‌ها را به ترتیب رخ‌دادن درج می‌کنید. روند ارزشیابی داده‌هایی ایجاد کنید تا اطمینان حاصل کند که جمع هزینه‌ها که در ستون E درج شده از بودجه شما بالاتر نرود.

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

معیار ارزشیابی داده‌هایی تعیین کنید که شما را مطمئن سازد که یک فرد می‌تواند تنها تاریخ‌هایی را وارد کند که در روزهای دوشنبه تا جمعه هستند. راهنمایی: تابع WEEKDATE برای روز یکشنبه عدد 1، برای روز دوشنبه عدد 2 و به همین روال سایر اعداد را برمی‌گرداند.

معیار ارزشیابی داده‌هایی تعیین کنید که شما را مطمئن سازد که زمان وارد شده در محدوده سلولی A1:A20 زمان پیش از ظهر (صبحگاهی) باشد.