کنترل ورود داده ها ،جلوگیری از ورود داده های غیر مجاز وکشف داده های غیر مجاز وارد شده

برای اینکه داد ه ها رو کنترل کنید راههای زیادی وجود دارد که دو تا از متداولترین راههای آن را توضیح میدهم یکی ازراهها استفاده از ابزار Conditional Formatting میباشد که با رنگی کردن داده ها میتوانید اشتباهها را پیدا کنید ، روش کار به این صورت هست که ابتدا ستون مورد نظر رو انتخاب کنید وسپس از زبانه Home وبلوک Styles روی ابزار Conditional Formatting کلیک کرده وروی اولین گزینه از منوی باز شده یعنی HighLight Cells Rules  رفته وسپس یکی از ابزارهای منوی باز شده با توجه به انتظاری که از آنها دارید را انتخاب کنید که در جدول زیر توضیح مختصری در مورد ابزارها داده شده است .

Greater Than	رنگی کردن اعداد بزرگتر از عدد مورد نظر
Less Than	رنگی کردن اعداد کوچکتر از عدد مورد نظر
Between	رنگی کردن اعداد بین ومساوی دو عدد مورد نظر
Eaual	رنگی کردن متن ،عدد و... مساوی با متن ،عدد و... مورد نظر
Text that Contains	رنگی کردن متون شامل حرف ،کلمه ویا جمله مورد نظر
A Date Occurring	رنگی کردن تاریخهای شامل تاریخ مورد نظر
Duplicate Values	رنگی کردن تکراریها

 فرض کنید یک ستون از اعداد دارید که در این ستون مجاز به وارد کردن اعداد بین 50 و 200 نبودید وحالا میخواهید کنترل کنید که عددی بین این دو عدد در این ستون وارد نشده باشد که برای این کار طبق بیان قبلی ابتدا ستون مورد نظر را انتخاب کرده وسپس به مسیر گفته شده یعنی

  Between    HighLight Cells Rules/  /  Conditional Formatting  /Styles /  Home  رفته ودر کادرهای خالی پنجره باز شده به ترتیب اعداد 50 و200 را وارد کرده ودر کادر آخر فرمت بندی دلخواه رو انتخاب کنید وسپس روی دکمه ok  کلیک کنید، بعد از این مراحل مشاهده خواهید کرد که  در ستون مورد نظر اعداد بین ومساوی اعداد مورد نظر رنگی شده اند که با یک فیلتر رنگی میتوانید اعداد رنگی شده را فیلتر کرده ودر کنار هم مشاهده کنید.که در شکل زیر مشاهده خواهید کرد.

وحالاراه دوم که جالبتر میباشد اینست که ابتدا محدوده اعدادتان را انتخاب کرده وسپس به مسیر Data/Data Validation/settings /Allow رفته واز منوی کشویی ظاهر شده اول گزینه Whole Number  را انتخاب کرده واز منوی کشویی دوم به طور مثال  not Between  را انتخاب کنید وسپس در کادرهای خالی  اول ودوم به ترتیب برای Minimum عدد 50 وبرای  Maximum  عدد 200 را تایپ کنید وسپس برای گذاشتن پیغام جهت هشدار به  وارد کننده اطلاعات به زبانه  Error Alert  در همین پنجره رفته و تیک Show error after invalid data is enterd را زده ودر کادر خالی اول عنوان اخطار ودر کادر بزرگ خالی دوم توضیحات اخطار را تایپ کنید وسپس ok کنید ،حالا شما دیگر نمیتوانید در محدوده انتخاب شده عددی بین  دو عدد 50 و200 را وارد کنید وبا پیغام هشدار مواجه خواهید شد،که اینکار از وارد کردن اعداد غیر مجاز جلوگیری میکند

 

جالبتر از همه اینها اگر از قبل اعدادی واردکرده باشیم وبخواهیم با استفاده از این ابزار  اشتباهها را بیرون بکشیم میتوانید با انتخاب محدوده مورد نظر وسپس کلیک روی منوی  Data Validation وانتخاب گزینه Circle Invalid Data  مشاهده خواهید کرد که نرم افزار اکسل اعدادی را  که مجاز نیستند را با کشیدن خط قرمز دور آنها مشخص خواهد کرد.

برای پاک کردن خطهای قرمز رنگ روی منوی Data Validation کلیک کرده وگزینه سوم یعنی Clear Validation Circles را انتخاب کنید.

روش ساخت لیستهای دینامیک

احتمالاً شما با روش ساختن لیست آشنا هستید ولی لیستهای شماعموماً به منبعی وابسته هست که ثابت است واگر داده ای به آن اضافه شود لیست شما به روز نمی شود،در این پست با روشی آشنا می شوید که می توانیدبا آن لیستهایی بسازیدکه با تغییر در منبع به روز می شوند. برای تهیه چنین لیستی  دو روش وجود داردکه روش اول با استفاده از جداول ونامگذاری آنهاست که در پست مربوط به تهیه لیستهای وابسته کاملاً توضیح داده شدوحالا روش دوم را توضیح می دهم.

همانطور که می دانیدبرای ساخت لیست به مسیر  Data/Data Validation/Allow/List/Source رفته و با فرض اینکه منبع ما ستون A باشد در کادر خالی آخرپنجره باز شده فرمول زیر را تایپ وok  میکنیم.  (OFFSET(A1,0,0,COUNTA(A:A),1)=

اگر متوجه نشدید ویا سئوالی داشتید می توانید در قسمت نظرات مطرح کنید. با تشکر

لیستهای وابسته در اکسل

برای ساخت لیستهای وابسته ابتدا باید اطلاعات منبع را مشخص کنیم،فرض کنید داخل یک شرکت کار می کنیدومیخواهید لیستی از پرسنل داخل شرکت را به تفکیک قسمتها داشته باشیدولیست شما حالت دینامیک داشته باشد یعنی هر زمان که فردی به پرسنل شرکت اضافه شد ویا از پرسنل کم شد با تغییر در اطلاعات منبع لیست ما نیز بروز شود.بنابراین ابتدا چهار جدول را به عنوان منبع تهیه میکنیم ،جدول اول ما شامل لیست واحدهای شرکت وجداول دیگر شامل پرسنل واحدها می باشد مامی خواهیم لیستی از واحدهای شرکت بسازیم که با انتخاب یکی از واحدهای شرکت از این لیست در لیست بعدی پرسنل آن واحد را داشته باشیم.بنابراین برای تهیه این چهار جدول ابتدا خانه ها(سلولهای) مورد نظرمان رابه صورت ستونی انتخاب میکنیم وسپس به تب insert  رفته وگزینه table  را کلیک میکنیم ،به همین ترتیب سه جدول دیگر را نیز تهیه میکنیم سپس جداولمان را نام گذاری میکنیم که برای انجام این کارابتدا جدول واحدهای شرکت را به غیر از عنوان انتخاب میکنیم وسپس در قسمت  namebox نام واحد را تایپ کرده واینتر را می زنیم،بنابراین نام واحد را به جدول اول اختصاص دادیم وبه همین ترتیب جدولهای دیگر را با نامهای ریسندگی ،بافندگی وتکمیل نام گذاری میکنیم.

حال برای ساخت لیست اول ،دراین شیت یا شیت دیگر سلول A2 را  انتخاب کرده وسپس ازتب data روی گزینه Data Validation  کلیک میکنیم.بعد در پنجره باز شده واز زبانه  setting وازمنوی کشویی Allow گزینه List   را انتخاب میکنیم بعد از آن در کادر خالی قسمت Source  کلید F3  را فشار داده تا لیست اسامی نام گذاری شده ظاهر شوداز میان این اسامی نام واحد را انتخاب کرده وسپس روی ok  کلیک میکنیم تا نام واحد در کادر خالی جایگذاری شود و در آخر روی ok  آخر کلیک میکنیم تا پنجره بسته شودبا این کار لیست ما که شامل نام واحدهای شرکت می باشد ساخته می شود،پس از ساخت لیست اول برای ساخت لیست دوم سلول B2 را انتخاب کرده وبه مسیر قبلی رفته ودر کادر خالی فرمول   =INDIRECT(A2)   راتایپ کرده و OK میکنیم.