روش‌های استخراج داده از اکسل به همراه معرفی دو فرمول کاربردی

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

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

معنای استخراج داده از اکسل و کاربردهای آن

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

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

این روند با استفاده از راه‌های ساده‌ای مانند جدول و فیلتر و همچنین راه‌های پیشرفته‌تر مانند Pivot Table و توابع پیش می‌رود که کاربردهای متنوع در حوزه‌های مختلف دارد. از مهم‌ترین کاربردهای استخراج داده از اکسل می‌توان به موارد زیر اشاره کرد:

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

استخراج داده از اکسل به زبان ساده

اگر به‌تازگی کار خود را با اکسل آغاز کرده‌اید، با استفاده از قابلیت‌هایی مانند فیلتر کردن (Filter)، مرتب‌سازی (Sort)، فیلتر پیشرفته (Advanced Filter) و رسم جدول (Table) می‌توانید داده مورد نظر خود را استخراج کنید. موارد زیر از کاربردی‌ترین روش‌های استخراج داده از اکسل هستند:

فیلتر کردن (Filter)، فیلتر پیشرفته (Advanced Filter)

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

1. در نوار بالای نرم‌افزار از تب Home در گروه Font روی گزینه Table کلیک کنید تا داده‌ها در جدول قرار بگیرد.
2. در نوار بالای نرم‌افزار از تب Data در گروه Sort & Filter روی گزینه Filter کلیک کنید.
3. علامت مثلث کوچک بر اول سلول هر ردیف ظاهر می‌شود که می‌توانید داده‌ها را براساس نام شهر فیلتر کنید.
4. پیش از انجام این مراحل دقت کنید که تمام انواع مختلف داده مانند نام شهر، فروشنده، نام کالا و غیره در یک ستون قرار گرفته باشد تا اطلاعات به‌درستی فیلتر شود. علاوه بر روش بالا می‌توانید از تب Home در گروه Editing و کلید میانبر Ctrl + Shift + L فیلترینگ داده‌ها انجام دهید.
5. همچنین اگر از تب Data در گروه Sort & Filter روی گزینه Advanced کلیک کنید، پنجره‌ای باز می‌شود که امکان فیلتر کردن داده‌ها را با دادن مقادیر به شما می‌دهد.

مرتب‌سازی (Sort)

با استفاده از قابلیت Sort در اکسل می‌توانید داده‌های خود را براساس یک ترتیب مشخص مانند از کم به زیاد و برعکس، به ترتیب حروف الفبا و مواردی از این نوع مرتب کنید. این قابلیت در تب Data و در گروه Sort & Filter قرار دارد.

علاوه بر آن به کمک این ابزار، امکان مرتب‌سازی داده‌ها براساس یک معیار مشخص مانند تاریخ یا نام مشتریان را هم خواهید داشت. به این ترتیب که پس از انتخاب ستون مدنظر، باید روی گزینه Sort کلیک کنید تا داده‌ها مرتب شود. 

شما می‌توانید این مسیر را با انتخاب چند ستون نیز طی کنید که در این صورت پنجره‌ای شامل موارد زیر باز می‌شود:

• Sort by: داده‌ها براساس کدام فیلد یا ستون مرتب شود؟ آن را در این گزینه مشخص کنید.
• Sort On: شیوه نمایش داده‌های مرتب‌شده چگونه باشد؟ نمایش براساس رنگ سلول، رنگ قلم، مقادیر و غیره را می‌توانید در این قسمت انتخاب کنید.
• Order: برای مرتب‌سازی داده‌ها چه درخواستی دارید؟ در اینجا می‌توانید داده‌های ستون مورد نظر را از قدیمی به جدید، بزرگ به کوچک، ترتیب حروف الفبا و غیره مرتب کنید.
• Add Level: با استفاده از Add Level می‌توانید سطر ایجاد اضافه کنید و شرط جدید برای مرتب‌سازی داده‌ها بگذارید. به این ترتیب داده‌ها براساس چند معیار مرتب می‌شود.

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

رسم جدول (Table)

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

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

• از تب Home و در گروه Font گزینه Bottom Border را انتخاب کنید.
• منوی کشویی باز خواهد شد که می‌توانید چارچوب حدود جدول را مشخص و سپس به صورت دستی یا خودکار جدول را رسم کنید.

سه روش رایج برای استخراج داده تکراری در اکسل

برخی از کسب‌وکارها مانند سایت‌های فروشگاهی با تعداد زیاد داده‌های تکراری در اکسل مواجه می‌شوند که فرایند ورود اطلاعات محصول را پیچیده می‌کند. اگر در این شرایط یا مورد مشابه قرار دارید، با استفاده از استخراج داده در اکسل به روش‌های Conditional Formatting، Remove Duplicates و توابع می‌توانید داده‌های تکراری را مشخص و حذف کنید:

روش Conditional Formatting

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

برای این منظور به ترتیب زیر پیش بروید:

• محدوده مدنظر خود را تعیین کنید. این محدوده می‌تواند شامل یک ردیف، ستون و کل برگه باشد.
• از تب Home و در گروه Styles  گزینه Conditional Formatting را انتخاب کنید.
• پنجره‌ای باز می‌شود که می‌توانید متناسب با داده‌های خود شروطی مانند Greater Than (بزرگتر از)، Less Than (کمتر از)، Between (بین دو مقدار) و غیره را بر داده‌های خود اعمال کنید. این شروط در دو بخش اصلی Highlight Cell Rules و Top/Bottom Rules قرار دارند.
• برای پیدا کردن داده‌های تکراری می‌توانید در قسمت Highlight Cells Rules گزینه Duplicate Values را انتخاب کنید تا پس از اعمال تنظیمات لازم، داده‌های تکراری را به صورت هایلایت به شما نمایش دهد.
• علاوه بر آن می‌توانید با تعیین رنگ داده‌های خود را قالب‌بندی کنید.

پس از طی این مراحل داده‌های تکراری را پیدا کرده‌اید و می‌توانید برای حذف یا انجام سایر تغییرات تصمیم‌گیری کنید.

روش Remove Duplicates

این قابلیت برای پیدا کردن داده‌های تکراری در اکسل‌هایی با حجم زیاد بسیار موثر است و فرایند پاکسازی داده‌ها را ساده‌تر می‌کند:

• از تب Data در گروه Data Tools گزینه Remove Duplicates را انتخاب کنید.
• پنجره‌ای باز می‌شود. باید ستون‌هایی که می‌خواهید در آن داده‌های تکراری وجود نداشته باشد، انتخاب کنید.
• پس از آن بر دکمه OK کلیک کنید. پنجره‌ای مقابل شما باز خواهد شد که درباره تعداد داده‌های تکراری توضیح داده است که در صورت تایید این داده‌ها را حذف می‌کند.

روش توابع

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

تابع COUNTIF

اگر داده‌های شما در ستون A قرار دارد، در یکی از ستون‌های جز ستون A فرمول مقابل را بنویسید:

 COUNTIF=(A:A, A2,“Red”) 

 درواقع A:A, A2 محدوده داده دارای مقدارهای تکراری مشخص می‌شود. علاوه بر آن می‌توانید به شیوه نمایش داده شرط بدهید که این داده‌ها با قالب‌بندی خاصی نمایش داده شود: 

COUNTIF=(A:A, A2,“Red”) 

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

COUNTIFS(range1, criteria1, range2, criteria2)

همچنین شما می‌توانید این مراحل را با تابع UNIQUE نیز طی و داده‌های تکراری را مشخص کنید.

شیوه استخراج داده از فایل CSV

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

• از تب Data بر گزینه From Tex کلیک کنید.
• از پنجره‌ای که باز شده است، فایل CSV خود را انتخاب و بر دکمه Open کلیک کنید.
• پس از آن فایل در اکسل باز می‌شود و می‌توانید آن را با فرمت اکسل ذخیره کنید.

شیوه خروجی گرفتن داده از اکسل به SPSS

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

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

پس از آن نرم‌افزار SPSS را باز و از تب Data Editor فایل اکسل خود را انتخاب کنید. به این ترتیب می‌توانید فایل اکسل را در این نرم‌افزار مورد بررسی قرار دهید.

جمع‌بندی

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

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