مهندسی صنایع - مدیریت اجرایی

اکسل گستره وسیعی از فرمت اعداد را دارا می‌باشد که می‌توانید بر حسب نیاز از آنها استفاده کنید. فرمت اعداد در کادر Format Cells و در سربرگ Number تنظیم می‌شود. برای نمایش این کادر محاوره‌ای می‌توانید از روش‌های زیر استفاده کنید:

•  از منوی Format، گزینه Cells را انتخاب کنید.
• روی سلول مورد نظر راست کلیک کرده و Format Cells را انتخاب کنید.
• کلیدهای ترکیبی Ctrl + 1 را فشار دهید.
• کلیدهای ترکیبی Alt + O + E را فشار دهید.

کادر محاوره‌ای Format Cells شامل شش سربرگ است. در سربرگ Number می‌توانید انواع شکل‌بندی‌های مربوط به اعداد را مشاهده نمایید که از جمله آنها Date و Time می‌باشد. در این بخش منطق تاریخ و زمان در اکسل و نحوه انجام محاسبات آنها توضیح داده می‌شود.

 در یک سلول تایپ کنید 2/5/8 و یا کلیدهای ترکیبی ; + Ctrl را فشار دهید (تاریخ امروز درج می‌شود)، اکسل به طور خودکار تشخیص می‌دهد که شما قصد درج یک تاریخ را دارید و فرمت سلول را به یکی از فرمت‌های تاریخ تبدیل می‌نماید (یعنی نیازی نیست خودتان از کادر محاوره‌ای Format Cells فرمت Date را انتخاب نمایید).

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

اما آیا می‌دانید هر عددی که تایپ می‌کنید، معادل چه تاریخی است؟ و منطق تبدیل اعداد به تاریخ چیست؟ 

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

 مبدا زمان (صفر) در اکسل برابر 01/00/1900  12:00:00 AM است .  12 شب اول ماه ژانویه سال 1900  (یعنی لحظه‌ای که تازه قرار است ماه ژانویه سال 1900 شروع شود.)

 به عبارت دیگر اگر در سلولی که فرمت تاریخ دارد، یک عدد تایپ شود، این عدد تعداد روزی می شود که از لحظه صفر زمان، گذشته است. مثلا عدد اگر عدد 32 تایپ شود، به فرمت تاریخ خواهد شد:  01/02/1900  12:00:00 AM (چون ماه ژانویه 31 روز می‌باشد و عدد 32 یعنی روز اول ماه فوریه)

 تا کنون با منطق ثبت تاریخ در اکسل آشنا شدید، اما زمان چطور در نظر گرفته می‌شود؟

در یک سلول تایپ کنید 9:25، به خودکار فرمت این سلول از نوع زمان می‌شود. حال در همین سلول یک عدد وارد کنید، خواهید دید که زمان 00:00 نمایش داده می‌شود. هر عددی که وارد شود، نتیجه همین خواهد بود!!

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

به عبارت دیگر هر عدد صحیح در اکسل یک روز (24 ساعت) است. بنابراین ساعت 12 شب لحظه 0 و ساعت 12 شب روز بعد را لحظه 1 در نظر گرفته می‌شود. با توجه به جدول زیر این مطلب را بهتر درک می‌کنید:

 حال می توانید با یک تناسب ساده معین کنید که اگر یک عدد  اعشاری (مثلا 0.777) را وارد کنید و سپس فرمت خانه به time تغییر دهید چه اتفاقی خواهد افتاد (زمان 18:38:53 نمایش داده می‌شود). یا اینکه می‌توانید با ضرب عدد در 24، عددی که بین 0 تا 1 تعریف شده بوده را به عددی در بازه 0 تا 24 تبدیل نمایید.

0.777*24=18.648 --> 18:38:53 

1- محاسبه اختلاف بین ساعت‌ها

فرض کنید قرار است  حقوق کارگرهای روز مزد یک کارخانه محاسبه شود. به هر کارگر، ساعتی 1000 تومان دستمزد داده می‌شود، بنابراین لازم است، ساعت کاری کارمندان محاسبه شود. در جدول زیر ساعت‌های ورود و خروج هر شخص نوشته شده است و در سلول‌های D2 تا D5 ستون B از C کم شده است. فرمت سلول‌های ستون D، به طور پیش فرض hh:mm:ss خواهد بود.

 همانطور که مشاهده می‌شود برای رضا که از ساعت 11 شب تا 7 صبح فردا مشغول به کار بوده است، اکسل نتوانسته اختلاف زمانی را بدست آورد. این بدان علت است که ساعت خروج کوچکتر از ساعت ورود است یعنی حاصل عددی منفی می‌شود که برای زمان غیر قابل قبول است. بنابراین فرمول برای سلولهای ستون D به صورت زیر اصلاح می‌شود:

=C2-B2+if(B2>C2,1,0)

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

 حال می‌بایست دستمزد هر کارگر حساب شود، فرض کنید برای محمد این کار با فرمول: D2*1000 انجام شود (در صورتی که این سلول نیز به طور خودکار به فرمت زمان تبدیل شد، فرمت آن را general نمایید).

اگر نتیجه را بررسی کنید خواهید دید که یک عدد منطقی بدست نمی‌آید چون محمد 8.5 ساعت کار کرده و باید 8500 تومان حقوق بگیرد، اما عددی که بدست می‌آید 354.167 می‌باشد!!

در واقع اکسل عدد منناظر 8:30  را در نظر گرفته و سپس آنرا در 1000 ضرب کرده که این نتیجه، غیر منطقی به نظر می رسد. به عبارت دیگر اکسل عدد 8:30 را در محاسبات به صورت 0.354167 در نظر می گیرد.

برای حل این مشکل باید بازه [0،1] به بازه [0،24] تبدیل شود. یعنی ابتدا عدد 0.354167 به حوزه 24 ساعت آورده می‌شود و سپس دستمزد محاسبه می‌گردد. با این عمل نتیجه درست خواهد شد: 

 2- محاسبه جمع ساعات کاری

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

 بعد از نوشتن فرمول SUM، در کمال تعجب دیده می‌شود که مقدار 00:45 بدست می‌آید!!

 با کمی تأمل مشخص می‌شود که اکسل به درستی عمل کرده است. کافیست فرمت سلولی که مجموع ساعات کاری را نشان می‌دهد به m/d/yyyy h:mm تغییر دهید.

برای این منظور از منوی Format گزینه Cells را انتخاب نموده تا کادر محاوره‌ای Format Cells باز شود. در سربرگ Number شکلبندی Custom را انتخاب و فرمت m/d/yyyy h:mm را برگزینید (که هم تاریخ و هم زمان را نشان دهد). 

 بعد از تغییر فرمت سلول حاصل جمع، جدول به صورت زیر خواهد بود: 

 همانطور که مشاهده می‌کنید، اکسل دارد درست عمل می کند در واقع این زمانها را باهم جمع زده و هر 24 ساعت را یک روز در نظر گرفته است. بنابراین مجموع ساعات 2 روز کامل و 45 دقیقه می‌باشد.

اما اگر بخواهید ساعت را نشان دهد: باید در Format Cells، گزینه i[h]:mm:ss را انتخاب کنید. این فرمت امکان درج ساعت بیش از 24 را می‌دهد.

پس از این تغییر فرمت، مشاهده می‌کنید که مجموع ساعات کاری 48:45:00 می‌شود.