نحوه نرمال سازی داده ها در اکسل – آموزش صفر تا صد

عبارت «نرمال‌سازی» (Normalization) کلیدواژه‌ای محبوب در میان افراد فعال در حوزه‌هایی مانند «یادگیری ماشین»، علم داده و آمار است. به بیان ساده‌تر منظور از نرمال‌سازی، تقلیل مقادیر داده به دامنه‌ای مشخص است. نرمال‌سازی داده‌ها در نرم‌افزار اکسل قدمی لازم و ضروری به‌حساب می‌آید. چرا که زمانی می‌توان از آماده بودن داده‌ها برای تجزیه و تحلیل اطمینان حاصل کرد که مقیاس داده‌های دیتاست یا مجموعه‌داده‌ها مشابه بوده و بتوان آن‌ها را با یک‌دیگر مقایسه کرد. اغلب و از آنجا که داده‌ها از منابع مختلفی جمع‌آوری می‌شوند و هر دیتاست دامنه متفاوتی دارد، با کمک فرایند نرمال‌سازی می‌توان بدون ایجاد اختلال و همچنین از دست دادن اطلاعات، مقادیر نمونه داده‌ها را تغییر داد. در این مطلب از مجله فرادرس، نحوه نرمال سازی داده ها در اکسل را یاد می‌گیریم و به بررسی تفاوت آن با استانداردسازی می‌پردازیم.

در این مطلب، ابتدا مفهوم نرمال‌سازی را شرح می‌دهیم و به تفاوت آن با استانداردسازی اشاره می‌کنیم. سپس نحوه نرمال‌سازی در نرم‌افزار اکسل را یاد می‌گیریم و در انتهای این مطلب از مجله فرادرس، توضیحی برای دلایل انجام نرمال‌سازی در کنار استاندارسازی و همچنین ضرورت نرمال‌سازی ارائه می‌دهیم.

نرمال سازی داده چیست؟

فرض کنید دو فروشنده گل می‌خواهند تعداد گل‌های رز به ثمر رسیده در باغ خود را با یک‌دیگر مقایسه کنند. فروشنده اول ادعا می‌کند که تعداد ۵ نهال گل رز داشته و امسال توانسته ۳۰ شاخه گل برداشت کند. از طرف دیگر، فروشنده دوم می‌گوید که در یک ماه، تعداد ۱۰۰ شاخه گل رز برداشت کرده است. اگر تنها ۳۰ شاخه گل فروشنده اول را با ۱۰۰ شاخه فروشنده دوم مقایسه کنید، این‌طور به‌نظر می‌رسد که فروشنده دوم باغبان بهتری است. اما چه اتفاقی می‌افتد اگر فروشنده دوم برای برداشت هر ۱۰۰ شاخه مجبور به کاشت ۲۵ نهال باشد؟

فیلم آموزش مقدمه ای بر داده کاوی (رایگان) در فرادرس

کلیک کنید

در چنین شرایطی، فرایند «نرمال‌سازی داده» (Data Normalization) دید منصفانه‌ای از این مقایسه نتیجه می‌دهد. به‌جای شمارش تعداد کل شاخه‌ها، بهتر است تعداد گل‌های حاصل از هر بوته را معیار قرار دهیم. فروشنده اول ۳۰ شاخه گل رز خود را از ۵ بوته برداشت کرده است. یعنی ۳۰ تقسیم‌بر ۵ یا ۶ شاخه گل به‌ازای هر بوته. در مقابل، ۱۰۰ شاخه گل رز فروشنده دوم از تعداد ۲۵ بوته برداشت شده است. یعنی ۱۰۰ تقسیم‌بر ۲۵ یا ۴ شاخه گل به‌ازای هر بوته. حالا و پس از نرمال‌سازی داده‌ها، ملاحظه می‌کنید که فروشنده اول به مراتب باغبان بهتری است.

داده‌های نرمال شده، نمونه‌هایی هستند که برای قرار گرفتن در محدوده دامنه‌ای مشخص و ساده‌تر تغییر یافته‌اند. لیستی از داده‌ها را تصور کنید که مقادیر آن از $$y$$ تا $$z$$ متغیر است:

حالا ما می‌خواهیم این لیست را با تغییر دامنه آن از $$a$$ تا $$b$$ ساده‌سازی کنیم:

عبارت زیر، بیانگر فرایندی است که برای نرمال‌سازی عنصر اول لیست یا $$X$$ دنبال می‌شود:

$$X_{normalized} = ((b - a)^* (X - y)\:/\:(z - y)) + a$$

علاوه‌بر عنصر اول یا همان $$X$$، همین فرمول را برای نرمال‌سازی سایر مقادیر موجود در لیست نیز تکرار می‌کنیم. نتیجه دامنه‌ای از داده‌هاست که تفسیر و درک آن به مراتب راحت‌تر است. لیست نهایی و نرمال شده به شرح زیر است:

نرمال سازی با استانداردسازی چه تفاوتی دارد؟

نرمال‌سازی و استانداردسازی دو مفهومی هستند که اغلب با یک‌دیگر اشتباه گرفته می‌شوند. با استفاده از محاسباتی که در بخش قبلی شرح داده شد، می‌توان لیستی از اعداد را نرمال‌سازی کرد. اما فرمول استانداردسازی در نرم‌افزار اکسل متفاوت است و با عنوان Standardize شناخته می‌شود. در حالی که فرایند نرمال‌سازی، دامنه مقادیر اولیه را تغییر می‌دهد، در استانداردسازی توزیع داده‌ها به‌گونه‌ای تغییر می‌یابد که میانگین برابر با ۰ و انحراف معیار برابر با ۱ باشد. فرایند استانداردسازی را «نرمال‌سازی امتیاز استاندارد» (Z-score Normalization) نیز می‌نامند. برای یادگیری بیشتر در مورد انواع تکنیک‌های آماده‌سازی، مشاهده فیلم آموزشی روش‌های پیش‌پردازش داده فرادرس که لینک آن در ادامه آورده شده را به شما پیشنهاد می‌کنیم:

فیلم آموزش روش های پیش پردازش داده ها – رایگان در فرادرس

کلیک کنید

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

یادگیری اکسل با فرادرس

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

• مجموعه فیلم‌های آموزش اکسل از مقدماتی تا پیشرفته فرادرس

نرمال سازی داده ها در اکسل چگونه است؟

محاسبات لازم برای انجام استانداردسازی بسیار راحت است. مجدد لیستی از مقادیر را در نظر بگیرد که دامنه آن از $$y$$ تا $$z$$ متغیر بوده و با عدد $$X$$ نیز شروع می‌شود.

فرمول محاسبه مقادر استاندارد شده مقدار $$X$$ مانند زیر است:

$$X_{standardized} = \frac{X - \mu}{\sigma}$$

در عبارت فوق، نماد $$\mu$$ بیانگر میانگین و $$\sigma$$ همان انحراف معیار دامنه است. فرمول محاسبه مقدار استاندارد در نرم‌افزار اکسل به شرح زیر است:

=STANDARDIZE(X; Mean; Standard Deviation)

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

=AVERAGE(Range of Values)

فانکشن STDEV در اکسل به عنوان فرمول اصلی محاسبه انحراف معیار در اکسل شناخته می‌شود. فرم کلی این فرمول به صورت زیر است:

=STDEV(Range of Values)

این فرمول برای محاسبه انحراف معیار نمونه آماری مورد استفاده قرار می‌گیرد. برای محاسبه انحراف معیار جامعه آماری، باید از تابع STDEV یا STDEV.P استفاده کنید. توجه داشته باشید  که فرمول ریاضی تابع STDEV با STDEVP‌ تفاوت دارد. در ادامه، مفاهیم عنوان شده را با استفاده از مثال نمرات ریاضی چند دانش‌آموز پیاده‌سازی می‌کنیم.

قدم ۱: محاسبه میانگین

ابتدا باید با نوشتن فرمول =AVERAGE(Range of Values) در بخش فرمول‌نویسی اکسل که با نماد $$f_x$$ (دایره قرمز رنگ) مشخص شده است، میانگین مجموعه‌داده را به‌دست آوریم:

قدم ۲: محاسبه انحراف معیار

در مرحله بعد و پیش از نرمال‌سازی، با وارد کردن عبارت =STDEV(Range of Values) در بخش فرمول‌نویسی، مقدار انحراف معیار را محاسبه می‌کنیم:

قدم ۳: نرمال سازی مقادیر

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

=STANDARDIZE (X, Mean, Standard Deviation)

در تصویر زیر، مقادیر نرمال شده نمرات را در ستونی با همین عنوان مشاهده می‌کنید:

مجله فرادرس مطلب کامل‌تری را درباره فرایند نرمال‌سازی داده و انواع تکنیک‌های آن آماده کرده است که می‌توانید از طریق لینک زیر آن را مطالعه کنید:

دلایل انجام نرمال سازی و استانداردسازی

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

۱. درک و ارزیابی راحت تر

به عنوان مثال، فرض کنید لیستی از نمرات ریاضی ۱۰ دانش آموز را در اختیار داریم. دامنه پیش‌فرض نمرات از ۰ تا ۱۰۰ است اما به منظور ارزیابی راحت‌تر، می‌خواهیم این دامنه را به محدوده ۰ تا ۱ تغییر دهیم. پس از انجام محاسبات، نمرات مانند زیر خواهند بود:

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

فیلم آموزش رسم نمودار در اکسل – طراحی انواع نمودار و تنظیمات در فرادرس

کلیک کنید

۲. مقایسه چند مجموعه داده با دامنه های متفاوت

این بار علاوه بر ریاضی، لیستی از نمرات امتحان فیزیک را نیز برای تشخیص میزان موفقیت دانش آموزان در هر درس معیار قرار می‌دهیم. اما نمرات ریاضی از ۱۰۰ و نمرات امتحان فیزیک از ۵۰ محاسبه شده‌اند. در نتیجه، با توجه به تفاوت دامنه‌ای که وجود دارد، ممکن است تجزیه و تحلیل مقادیر اصلی گمراه کننده باشد. اما پس از نرمال‌سازی، نمرات به‌دست آمده شبیه به جدول زیر خواهند بود:

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

همانند نرمال‌سازی، برای استانداردسازی نیز دو دلیل قابل برشمردن است که در ادامه به آن اشاره می‌کنیم.

۱. ساده سازی انواع مقادیر با میانگین ۰ و انحراف معیار ۱:

فرض کنید قیمت سهام ۸ شرکت بزرگ را با میانگین ۲۳/۵ و ۲۲/۱ در اختیار داریم. همانطور که در دو جدول زیر مشاهده می‌کنید پس از استانداردسازی، خواندن و ارزیابی مقادیر داده به مراتب راحت‌تر خواهد بود:

با استانداردسازی، انحراف معیار یا همان فاصله از مقدار میانگین به دست می‌آید. اگر مقدار استاندارد شده یک ویژگی منفی باشد، یعنی کوچکتر از میانگین بوده و اگر مثبت باشد یعنی از میانگین بزرگتر است. به عنوان مثال، همان‌طور که در جدول فوق آمده است، انحراف معیار شرکت Walmart منفی بوده و به اندازه ۰/۶۱۰ کمتر و قیمت سهام شرکت Apple با علامت مثبت به اندازه ۱/۵۱۳ بیشتر از میانگین است.

۲. ارزیابی راحت تغییرات با توجه به میانگین یا انحراف معیار متفاوت

ارزش ماهانه دو شرکت را در بازار سهام در نظر بگیرید. در تصویر زیر شاهد تفاوت مقادیر اصلی و استاندارد شده در هر ماه هستید:

نرمال سازی چه ضرورتی دارد؟

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

جمع‌بندی

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