۴ روش برای دسته بندی ساعت در اکسل
۴ روش برای دسته بندی ساعت در اکسل
خیلی وقت ها در داده هایی که ذخیره میکنیم، داده هایی از جنس ساعت وجود داره، مثلا ساعت ورود و خروج کارکنان، ساعت و زمان خرید مشتری ، ساعت ورود یک کاربر به یک سایت و مدت زمان حضور و سایر موارد. حالا میخوایم این داده ها رو تحلیل کنیم مثلا ببینیم در چه بازه های زمانی فروش بیشتری داشتیم، یا مثلا در چه ساعاتی کاربران بیشتری به سایت مراجعه میکنن و خلاصه تحلیل هایی رو روی داده های زمانی موجود داشته باشیم که بتونیم بهتر تصمیم گیری کنیم، عملا قصد داریم به نحوی روی داده های خودمون گروه بندی یا دسته بندی ساعت انجام بدیم. برای این کار، ۴ روش کاربردی ارائه میکنیم که بنا به شرایط میتونید از هر کدوم استفاده کنید:
روش اول: استفاده از امکان گروه بندی در pivotable
داده هایی از زمان خرید مشتریان یک فروشگاه ثبت شده است. میخواهیم ببینیم در هر یک ساعت ،چند خرید ثبت شده است. امکان گروه بندی در پیوت تیبل، یکی از امکانات بسیار پر کاربرد هست که برای اعداد، تاریخ و ساعت وجود داره. البته گروه بندی دستی هم امکان پذیره. برای گروه بندی زمان در پیوت تیبل کافیه اول گزرش رو بسازیم (برا یان کار من فیلد زمان رو در row و فیلد کاربر رو در value قرار دادم):
- روی یکی از آیتم های ساعت کلیک میکنیم
- از تب Pivotable analyze روی group کلیک کرده و group field رو میزنیم.(شکل ۱)
- حالا از پنجره باز شه، روی hour کلیک میکنیم و بقیه رو از حالت انتخاب در میاریم. ( شکل ۲)
شکل ۱- گزارشگیری با پیوت تیبل و گروه بندی
همونطور که در شکل ۲ نمایش داده شده گروه بندی ساعت تحت شرایط و دسته بندی های خاصی ارائه میشه، یعنی نهایتا میتونیم گروه بندی بصورت ساعتی، دقیقه ای و ثانیه ای داشته باشیم. پس یعنی نمیتونیم هر ۴ ساعت یا هر ۰.۵ ساعت، داده ها رو دسته بندی کنیم.
شکل ۲- دسته بندی ساعت در اکسل با پیوت تیبل
الان در گزارش نمایش داده شده در شکل ۱، میتونیم ببینیم در هر ساعت از شبانه روز و در مجموع چند خرید انجام شده. اما حالا میخوایم مثلا بازه های ۲ ساعته یا ۴ ساعته (دلخواه) تعریف کنیم که بتونیم در بازه های دلخواه تحلیل انجام بدیم. برای این کار میریم سراغ فرمول نویسی. پس روش بعد رو ببینید:
روش دوم: استفاده از تابع Floor برای ایجاد بازه دلخواه
قبلا راجع به توابع گرد کردن صحبت کردیم. تابع Floor عدد رو به مضرب دلخواه گرد میکنه. از طرفی میدونیم که ساعت در واقع عددی است بین ۰ تا ۱. مثلا ساعت ۱۲ ظهر، معادل عدد ۰.۵ در نظر گرفته میشه. راجع به مفهوم ساعت، حتما مقاله زمان در اکسل رو مطالعه کنید. حالا با ترکیب این دو مفهوم میتونیم ساعت رو ۴ ساعت ۴ ساعت گرد کنیم که خروجی ،ساعاتی باشه که مضرب ۴ ساعت باشه، یعنی ۰، ۴، ۸، ۱۲، ۱۶، ۲۰، ۲۴.
برای این کار کافیه یک ستون به دیتابیس استفاده کنیم و بنویسیم:
=FLOOR ( B2 , “۴:۰۰” )
شکل ۳- گروه بندی زمان با تابع Floor
وقتی این کار و کنیم، همه ساعت ها به مضرب ۴ ساعت گرد میشن و ساعت های کتر از ۴ ساعت میشن دسته ۰:۰۰ و ساعت های بین ۴ تا ۸ ساعت میشن ۴:۰۰ و … حالا میتونیم روی داده جدید پیوت بگیریم و گزارش رو بصورت زیر ببینیم.
شکل ۴- دسته بندی زمان در بازه ۴ ساعتی
در گزارش بالا، میبینیم که بیشترین خریدها یعنی ۹ خرید در بازه ساعتی ۸-۱۲ اتفاق افتاده. پس میبینیم که با وجود امکان دسته بندی، تحلیل داده خیلی بهتر و دقیق تر انجام میشه.
روش سوم: استفاده از تابع Mround برای دسته بندی نزدیک به عدد دلخواه
مثلا فرض کنید داده هایی داریم که میخوایم حول ۱۵ دقیقه گرد بشه. یعنی اگه ۱۶ دقیقه هم بود، ۱۵ در نظر گرفته بشه و اگه ۱۳ دقیقه هم بود، باز هم ۱۵ در نظر گرفته بشه. برای این کار باید از تابع Mround استفاده کنیم. توضیحات و مثال های بیشتر این تابع رو در مقاله گرد کردن مضربی مطالعه کنید.
برای این کار مضرب رو ۱۵ دقیقه در نظر میگیریم . پس فرمول رو به شکل زیر می نویسیم:
=MROUND( B2, “۰۰:۱۵:۰۰”)
روش چهارم: استفاده از تابع Vlookup برای دسته بندی نامنظم
فرض کنید میخواهیم داده ها را بصورت بازه ای ولی این بار بصورت نامنظم دسته بندی کنیم. مثلا بازه اول ۲ ساعتی، بعدی ۶ ساعتی و … برای این کار میتونیم از تابع Vlookup در حالت جستجوی بازه ای استفاده کنیم. منطق این تابع رو میتونید در مقاله جستجوی بازه ای مطالعه کنید.
برای این کار اول جدول دلخواه رو میسازیم. مطابق شکل ۵.
شکل ۵- ایجاد جدول برای بازه های دلخواه
هدف در این مقاله، دسته بندی ساعت و مفهوم اونه. نحوه محاسبه فراوانی و تعداد تکرار در هر بازه، متنوعه و هم با پیوت تیبل، هم با فرمول نویسی امکان پذیره. برای انجام محاسبات فراوانی حتما سه مقاله کاربردی محاسبه فراوانی در اکسل رو مطالعه کنید.محاسبه فراوانی داده در اکسل با تابع Countifsمحاسبه فراوانی داده ها با تابع Frequencyمحاسبه فراوانی داده ها با استفاده از Pivot Table
حالا کافیه فرمول زیر رو بنویسیم:
=VLOOKUP ( B2 , $I$2:$J$7 , ۲ , ۱ )
شکل ۶- جستجوی بازه ای و دسته بندی ساعت به دسته های دلخواه با تابع vlookup
در این مقاله ۴ روش برای دسته بندی زمان در اکسل یاد گرفتیم. برای درک این مطالب لازمه که مفهوم زمان در اکسل، توابع گرد کردن و جستجوی بازه ای با تابع Vlookup رو خوب یاد بگیرید. حتما مقالات مرتبط رو مطالعه کنید و فایل زیر رو دانلود کنید که بتونید تمرین هم انجام بدید.
