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

انواع داده در اکسل

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

برای اینکه بتوانید از اکسل استفاده کنید باید داخل این سلول‌ها را با مقادیر خاصی پر کنید. در نرم افزار اکسل چندین نوع داده داریم که می‌توانیم داخل این سلول‌ها قرار دهیم. در ادامه تمامی این گزینه‌ها را با هم بررسی می کنیم:

• داده‌های عددی: اولین و مهم‌ترین داده‌ای که در این دوره با آن سر و کار داریم داده‌های عددی است. داده‌های عددی که آن‌ها را با عنوان Numerical data هم می‌شناسند در دنیای تحلیل داده از اهمیت بسیار زیادی برخوردارند. به عنوان مثال قیمت محصولات جزء داده‌های عددی هستند. این داده‌ها به دو دسته داده‌های عددی کامل و داده‌های عددی اعشاری تقسیم بندی می‌شوند. اگر شما یک قیمت رند را در اکسل وارد کنید در حال کار با داده های کامل هستید. اگر بخواهید قیمت را با علامت اعشار وارد کنید به این نوع داده‌ها، داده‌های عددی اعشاری می‌گویند( به طور مثال 320.250)
• داده‌های متنی: حتماً برای شما هم پیش آمده است که بخواهید متنی را در اکسل وارد کنید. این نوع داده‌ها را داده‌های متنی می‌گویند. به عنوان مثال وقتی اسم مشتری را در اکسل ثبت می‌کنید یا آدرس آن را وارد نرم‌افزار می‌کنید در حال کار با داده‌های متنی هستید. یادتان باشد که این داده‌ها هم از اهمیت بسیار زیادی برخوردارند و باید به دقت ثبت و مورد بررسی قرار گیرند.
• داده‌های مربوط به تاریخ و زمان: اکسل یکی از جذاب‌ترین نرم‌افزارهایی است که من تا به حال دیده‌ام. اگر تاریخ فروش یا ارسال کالا را در این نرم‌افزار ثبت کنید می‌توانید اسم این نوع داده را داده های مربوط به تاریخ بگذارید.
• داده‌های خالی: گاهی از اوقات به دلایل مختلف برخی از سلول‌ها داده‌ای در خود ندارند. ما به این سلول‌های خالی، blank می‌گوییم. در ادامه روش مدیریت این نوع داده‌ها را با هم بررسی می‌کنیم.
• داده مربوط به کارنسی: اگر می‌خواهید داده‌های مربوط به قیمت را بر اساس کارنسی وارد کنید این داده‌ها برای شما طراحی شده‌اند.
• داده‌های کسری و درصدی هم نوع دیگری از داده‌ها هستند که در اکسل مورد استفاده قرار می‌گیرند.

تمرین: اگر در حال حاضر مشتری دارید اطلاعات مختلفی که نیاز دارید را از او بگیرید. به عنوان مثال نام، نامخانوادگی، محصول خریداری شده، تعداد محصول، نوع محصول، دسته بندی محصول، تاریخ ثبت سفارش، تاریخ ارسال سفارش، آدرس مشتری و غیره.

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

چگونه نوع داده را در ستون‌های اکسل تغییر دهیم؟

خوب حالا که صفحه اکسل خود را تشکیل داده‌اید شاید یک سوال مهم به ذهن‌تان خطور کرده باشد. ایا من می‌توانم نوع داده را در اکسل تغییر دهم؟ بله. این امکان برای شما فراهم شده است. کافیست در نرم‌افزار اکسل به تب Home بروید و بخش number را پیدا کنید.

در این بخش یک باکس کشویی می‌بینید. اگر سلول خود را انتخاب کنید می‌توانید نوع داده را بر اساس نیاز تغییر دهید. به طور مثال فرض کنید شما قیمت را به صورت عدد وارد کرده‌اید. در واقع یک داده عددی دارید. حالا تصمیم گرفته‌اید این عدد را به داده کارنسی تبدیل کنید. کافیست سلول یا سلول‌های مدنظر را انتخاب کنید و  از باکس کشویی عبارت currency را بردارید. البته یک راه میانبر هم دارید.

برای استفاده از این راه میانبر روی سلول مدنظر راست کلیک کنید. گزینه format cells را انتخاب نمایید و از پنجره باز شده و در بخش category عبارت currency  را انتخاب کنید. در این بخش می‌توانید هر نوع کارنسی که دوست داشتید را روی سلول‌تان اعمال کنید(ریال ایران هم قابل اعمال است).

چگونه می‌توانیم محدودیتی برای وارد کردن داده در اکسل اعمال کنیم؟

بیایید این بخش را با یک مثال ساده پیش ببریم. فرض کنید شما در شرکت‌تان بخش حسابداری دارید و قرار است تمامی اطلاعات مربوط به مشتریان را در صفحات اکسل ذخیره کنید. افرادی که برای این بخش استخدام کرده‌اید تازه با شما وارد همکاری شده‌اند و از محدودیت‌ها و قوانین حاکم بر شرکت‌تان آگاه نیستند.

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

برای دسترسی به این بخش کافیست تب data را در اکسل انتخاب کنید. زمانی که این تب را انتخاب می‌کنید بخش‌های مختلفی برایتان نمایان می‌شود. بخش data tools را پیدا کنید. در این بخش گزینه‌ای به نام Data Validation وجود دارد. data validation در عمل همان کاری را انجام می‌دهد که شما نیاز دارید. در واقع به کمک این بخش می‌توانید مشخص کنید چه نوع داده‌هایی باید وارد اکسل شوند و چه محدودیت‌هایی باید وجود داشته باشد.

زمانی که بر روی این بخش کلیک می‌کنید یک پنجره برایتان باز می‌شود. این پنجره سه تب به نام‌های setting، input message و Error Alert دارد. بیایید هر کدام از این تب‌ها را یک به یک بررسی کنیم.

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

تب input message: اگر تیک گزینه show input message when cell selected را بزنید زمانی که کاربر روی سلول مدنظر کلیک می‌کند و آن را انتخاب می‌نماید یک پیغام برایش نشان داده می‌شود. عنوان پیام و متنی که داخل این باکس نشان داده می‌شود را خودتان تعیین می‌کنید.

تب alert message: اگر کاربر با محدودیت‌هایی که شما توسط این قابلیت اعمال کرده‌اید آشنا نباشد چه طور؟ چگونه می‌توان او را از وجود این محدودیت‌ها مطلع کرد؟ بهترین راه نمایش یک پیغام هشدار یا خطاست. در این تب پیغامی را تنظیم می‌کنید تا اگر کاربر از محدودیت‌های اعمال شده تخطی کرد به او نشان داده شود.

مثلاً فرض کنید شما به کاربر خود اجازه داده‌اید یک محدوده را با اعدادی بین 10 تا 100 پر کند. حالا اگر کاربر به هر دلیلی عددی بیشتر از 100 و کمتر از 10 را وارد نماید پیغام هشداری به او نمایش داده می‌شود و به او می‌گوید مسیر را اشتباه رفته است. بهتر است این بخش را به گونه‌ای تنظیم کنید که راهنمای کاربران‌تان باشد.

تمرین: اعمال محدودیت برای وارد کردن فهرست محصولات

خوب حالا که با قابلیت data validation آشنا شدیم بهتر است یک تمرین عملی با هم انجام دهیم. در این مثال فرض می‌کنیم یک فروشگاه بزرگ داریم و می‌خواهیم اسم محصولات‌مان را به صورت فهرست در اکسل وارد کنیم و سپس قیمت هر کدام را ثبت نماییم.

برای اینکار یک صفحه اکسل جدید باز می‌کنیم. حالا یک ستون به اسم محصول یا محصولات ایجاد می‌کنیم. محدوده‌ای که می‌خواهیم اسم محصولات را بنویسیم انتخاب می‌کنیم. من 4 سلول را انتخاب می‌کنم. حالا به تب data  می رویم و بر روی data validation کلیک می‌کنیم.

در پنجره باز شده و از منوی کشویی عبارت list را بر می‌داریم. حالا یک بخش جدید به نام source برایمان فعال می‌شود. در این بخش می‌توانیم به صورت دستی اسم محصولات را وارد کنیم و با علامت کاما آن‌ها را از هم جدا کنیم. البته شما می‌توانید این فهرست را در بخشی از اکسل وارد کنید و آدرس آن را در این بخش بنویسید.

اگر دوست دارید پیغامی به کاربر نشان داده شود بخش های Alert message  و input message را هم فعال می‌کنید. به همین راحتی…..

شناسایی داده‌های تکراری در اکسل

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

برای اینکه بتوانید داده‌های تکراری یا همان duplicate را بیابید چندین روش پیش رو دارید. اولین روش استفاده از فرمت دهی شرطی است. به کمک این روش می‌توانید داده‌های تکراری را هایلایت یا برجسته کنید.

به همین منظور، محدوده مدنظر را انتخاب کنید. به تب home بروید و از قسمت styles بخش conditional formatting را انتخاب کنید. حالا یک فهرست برایتان باز می‌شود. از این فهرست قسمت اول یا همان Highlight Cells Rules را انتخاب کنید. این بخش به شما اجازه می‌دهد قوانینی را برای هایلایت یا برجسته کردن سلول ها اعمال کنید. از فهرست باز شده گزینه Duplicate Values را انتخاب کنید. حالا اگر در محدوده انتخاب شده داده تکراری وجود داشته باشد به صورت رنگی در می آید.

اگر می‌خواهید داده‌های تکراری را حذف کنید باید به سراغ تب Data بروید. در این تب قابلیتی به نام  Remove Duplicates وجود دارد. روی این بخش کلیک کنید. در پنجره باز شده می‌توانید ستون‌هایی که حاوی داده تکراری هستند را انتخاب کنید و این داده‌ها را حذف نمایید.

پیدا کردن سلول‌های خالی در اکسل

سلول‌های blank یا خالی از جمله مواردی هستند که ممکن است شما را به دردسر بیندازند. بعداً درباره روش‌های تحلیلی پیشرفته با هم صحبت خواهیم کرد. در این روش‌ها بهتر است سلول خالی نداشته باشیم تا تجزیه و تحلیل مان را با قدرت بیشتری به پیش ببریم.

برای شناسایی سلول‌های خالی هم چندین روش وجود دارد اما ساده‌ترین راه استفاده از قابلیت‌های آماده در اکسل است. در این روش محدوده مدنظر را انتخاب کنید. سپس از تب home به سراغ بخش editing بروید. در این بخش find and select را بیابید و انتخاب کنید.

در فهرست باز شده گزینه go to special را بزنید. حالا پنجره‌ای برایتان باز می‌شود. در پنجره باز شده گزینه blank را تیک بزنید و اوکی را بزنید. اگر نگاهی به محدوده موجود بیندازید متوجه می‌شوید که سلول‌های خالی هایلایت شده و در حالت انتخاب قرار دارند. اگر دوست داشتید می‌توانید این خانه‌ها را با یک عدد ثابت پر کنید، آن‌ها را رنگی کنید یا یک متن ثابت برای پر کردن‌شان انتخاب کنید.

استفاده از قابلیت فیلتر برای پیدا کردن سلول‌های خالی

اگر بخواهید سلول‌های خالی یک ستون را پیدا کنید قابلیت filter در اکسل بهترین انتخاب برای شما خواهد بود. به کمک این قابلیت به ظاهر ساده کارهای جذاب زیادی می‌توان انجام داد. کافیست ستون مدنظر را انتخاب کنید. به سراغ تب home  بروید و عبارت sort and filter را بیابید. بر روی آن کلیک کنید. در فهرست باز شده عبارت filter را انتخاب کنید. بعد از انتخاب این گزینه، یک علامت مربعی شکل به بالای ستون شما اضافه می‌شود.

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

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

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

=IF(ISBLANK(B2),”Blank”,”Not Blank”)

=IF(B2=””,”Blank”,”Not Blank”)

در فرمول اول به اکسل می‌گوییم اگر سلول B2 خالی بود عبارت blank را در آن قرار بده و اگر خالی نبود عبارت not  blank را بنویس. فرمول دوم هم دقیقا همین کار را برای شما انجام می‌دهد.

تمرین را فراموش نکنید

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