mrexcel
2011/03/11, 08:34
این آموزش با توضیخ برخی توابع dateبه راهکارهای تجربی در زمینه استفاده از تاریخمیپردازد .و با توضیح چندین نکته در زمینه محاسبات زمان پس به سراغ ابزارهای جستجو رفته و نکاتی را در جهت جستجو دقیق تر اطلاعات عنوان مینماید.
فرمت تاریخ و زمان :اگر چه فرمت تاریخ و زمان در مباحث مقدماتی دوره توضیح داده شده است ولی به دلیل اهمیت و ارتباط تنگاتنگ با موضوع فوق خلاصه ای از مبحث فوق مجددا در این قسمت توضیح داده میشود . اگر ما در یک سلول اطلاعات اعداد را به صورکسری مانند 3/4 بنویسیم خروجی سلول ما به صورت تاریخ داد نمایش میشود و در صورتی که در سلول فوق باز هم عدد وارد نمائید حاصل باز هم یک رشته تاریخی میباشد .بنابراین برای کار کردن با تاریخ ما باید فرمتهای مختلف را علاوه بر شناختن کنترل بنمائیم.مجموعه توابع تاريخ و زمان امكاناتي در زمينه محاسبات زماني و تقويمي در اختيار ما قرار مي دهند.براي فرمت تاريخ 01/01/1900 مبنای محاسبات مي باشد كه متناظر با عدد 1 مي باشند. براساس اين مبنای تاريخ هاي بعدي متناظر با يك عدد هستند. محاسبات بر روي اعداد متناظر انجام مي شود و سپس تاريخ متناظر با اين عدد نمايش داده مي شود. در عمل اين شماره سريال ها هستند كه زحمت محاسبات را بر دوش مي كشند.برای دیدن یک شماره سریال در فرمت زمان باید فرمت سلول ها را زمان تبدیل به فرمت عدد نمود.اکسل از دو سیستم تاریخی پشتیبانی می کند : 1-مبنای تاریخ 1900 میلادی (قراردادی ویندوز)2-مبنای تاریخ 1904 میلادی یکی از مزایای استفاده از سیستم تاریخی 1904 در این است که تاریخهای منفی را نمایش می دهدبرای تغییر فرمت قراردادی زمان شما نیاز دارید تا از طریق مسیر tools/option/calculate /1904date system
گزینه فوق را باید فعال نمود .برای اینکه فرمتهای مختلف تاریخی را بتوانیم در سلول تنظیم بنمائیم نیاز است تا از مسیر کلیک راست formatcell/number/custom /فرمت ها را به شکل های زیر وارد بنمائبد . به عنوان مثال در صورتی که بخواهیم سال را به صورت دو رقمی وارد بنمائیم باید فرمت را بدینگونه وارد نمود yy/mm/dd
موارد نمونه زیر جهت درک بهتر موضوع آورده شده است
Yy/mmmm/ddd 78/march/fri yyyy/mm/ddd 1978/03/friMmmm/dddd March/friday Yy-ddd 78-fri فرمت زمان :یاد داشتن فرمت زمان از اهمیت بسیار بالائی برخوردار میباشد . زیرا در بسیاری موارد ما نیاز داریم
محاسباتی را بروی اطلاعات زمان انجام دهیم مانند : ورود خروج پرسنل – حقوق دستمزدزمان خرابی دستگاهها – برنامه ریزی تولیدکنترل زمانبندی فعالیتهای یک پروژه نکات مهم زیر در مورد وارد کردن فرمت زمان در اکسل دارای اهمیت میباشد .1.اگر پس از انتخاب یک سلول کلید ctrl+shift+:را فشار دهیم زمان وقت کامپیوتر در ان سلول وارد میشود .یک زمان مثلا8:00ساعته از نظر عددی برابر است با کسری از عدد 24 یعنی تقریبا 0.33در یک فرمول فرمت تاریخی را در فرمول نمیتوان تایپ کرد همانند
(sum(05:33;a1=در صورتی که نیاز باشد زمان در فرمول وارد شود باید value ان را گذاشت مثلا همان 0.33برای جمع ساعت های که حاصل آنها بیشتر از 24میشود فرمت سلول را باید همانند زیر نوشتh]:mm] یعنی hرا که نشان دهنده ساعت میباشد باید در [ ]گذاشت 5.برای استفاده از تعدادی توابع زمان شما نیاز دارید تا Analysis Toolpak
را نصب نمیایید در برخی موارد ما نیاز داریم زمان های مختلف را جمع نمائیم و در یک عدد خاصی ضرب نمائیم به عنوان مثال ساعت های اضافه کاری را جمع نمائیم در نرخ اضافه کاری ضرب نمائیم اگر در این حالت بخواهیم به جواب درست دست بیابیم باید حاصل را در عدد 24 ضرب بنمائیم .
sum(d1:D30)*1000*24=افراد مختلف در فایل های مختلف از فرمت های زیر برای تاریخ استفاده مینمایند که معایب و مزایا هر کدام را توضیح میدهیم 1. تاریخ را به فرمت تاریخ میلادی بنویسیم ولی در قسمت format cell>custom فرمت ان را به صورت yy/mm/ddنوشت در این حالت تاریخ 21/05/1987 به صورت 21/05/87دیده میشوددراین حالت تاریخها به راحتی مقایسه میشوند و فرمت اطلاعات هم تاریخ هست و لی از نظر محاسبات روی تاریخ مشکل داریم مثلا ما تاریخ 31/03/87در تاریخ شمسی داریم ولی در تاریخ میلادی این تاریخ وجود ندارد چون ماه 3 انها 31 روزه نمیباشد و مشکل دوم اگر از توابع فاصله بین 2 تاریخ استفاده کنیم جواب با خطائ روبرو میشود.2.حالت دوم نوشتن تاریخ به صورت عدد مثلا 870321این روش نوشتن بسیار ساده میباشد با روش عادی قابل محاسبه روزهای این 2 تاریخ نیست ولی براحتی مقایسه میگردد و هر گونه عددی را میتوان نوشت حالت سوم view
مناسبی را دارد و به صورت 21/05/1387نوشته میشود و نه مقایسه بر روی ان
انجام میشود نه محاسبه در این حالت اطلاعات در حکم text میباشد .روش چهارم استفاده از کدهای vba موجود در اینترنت میباشد توابع زمان تاریخ Date اين تابع شماره سريال يك تاريخ خاص را نشان مي دهد.
(Date (Year, Month, Day=
Year
مي تواند يك تا چهار رقم از بين 1 تا 9999 باشد. اگر ما ماه سال و روز را در 3 سلول مجزا نوشته باشیم در این روش میتوان آنها را به یک رشته تاریخی تبدیل نمائیم . Today
اين تابع تاريخ ميلادي روز جاري را برمي گرداند. اگر تابع را در سلولي كه فرمت آن، قالب Generalمي باشد وارد كنيد مقدار نتيجه به صورت mm/dd/yyyy
نمايش مي يابد.today() اين تابع هيچ آرگوماني را نمي گيرد ولي حتماً پرانتزهاي خالي را بياوريد. این سلول با هر با بازکردن فایل و اکتیو کردن هر سلول این فرمول به روز میشود یعنی بر اساس تاریخ کامپیوتر زمان را نمایش میدهد Days 360 تعداد روزهاي بين دو تاريخ را براساس يك سال 360 روزه برمي گرداند. (Day360 (start-date, end-date, method= Method: يك مقدار منطقي است اگر Falseباشد يا آورده نشود در محاسبه از روش آمريكايي استفاده مي شود اگر Trueباشد روش اروپايي بكار گرفته مي شود. Day مقدار قسمت عددروز مربوط يك مقدار تاريخ/زمان سريال را برمي گرداند.
(Day (serial-number= serial-number: يك مقدار تاريخ، يك آدرس يا متن به صورت ثابت تاريخ در زوج علامت نقل مي تواند باشد.مثال: Day (2002/01/04) 04 توابع month - year–day نیز همانند این تابع عمل مینماید. Weekday :شماره سريال روز هفته را مي دهد.
=Weekday (serial-number, return-type) Serial-number: يك مقدار تاريخ- يك آدرس يا متني به فرم تاريخ در علامت نقل قول. اگر عددي را وارد نكنيم يا 1 باشد مبناي شروع هفته از يك شنبه خواهد بود، در نتيجه روز 2 شنبه عدد 2 و سه شنبه عدد 3 و الي آخر خواهد بود.فشردن همزمان کلید CTRL+; در یک سلول تاریخ کامپیوتر را می دهد .توابع زمان
TIME
اين تابع شماره سريال مربوط به زمان مشخص شده را مي آورد.
= TIME (hour, minute, second) مثال: = TIME (13:30:20) HOUR
اين تابع ساعت متناظر با شماره سريال را برمي گرداند.
= HOUR (serial-number) = HOUR (0.5627) = (13:30:20) NOW ( )
اين تابع تاريخ و زمان فعلي را درج مي نمايد. كه شامل يك مقدار صحيح (تاريخ) و يك مقدار اعشاري (زمان) مي باشد. برای استفاده از تعدادی توابع زمان شما نیاز دارید تا Analysis Toolpak
را نصب نمیایید. WEEKNUM تعداد هفته را در یک سال مشخص می دهد
فرمت تاریخ و زمان :اگر چه فرمت تاریخ و زمان در مباحث مقدماتی دوره توضیح داده شده است ولی به دلیل اهمیت و ارتباط تنگاتنگ با موضوع فوق خلاصه ای از مبحث فوق مجددا در این قسمت توضیح داده میشود . اگر ما در یک سلول اطلاعات اعداد را به صورکسری مانند 3/4 بنویسیم خروجی سلول ما به صورت تاریخ داد نمایش میشود و در صورتی که در سلول فوق باز هم عدد وارد نمائید حاصل باز هم یک رشته تاریخی میباشد .بنابراین برای کار کردن با تاریخ ما باید فرمتهای مختلف را علاوه بر شناختن کنترل بنمائیم.مجموعه توابع تاريخ و زمان امكاناتي در زمينه محاسبات زماني و تقويمي در اختيار ما قرار مي دهند.براي فرمت تاريخ 01/01/1900 مبنای محاسبات مي باشد كه متناظر با عدد 1 مي باشند. براساس اين مبنای تاريخ هاي بعدي متناظر با يك عدد هستند. محاسبات بر روي اعداد متناظر انجام مي شود و سپس تاريخ متناظر با اين عدد نمايش داده مي شود. در عمل اين شماره سريال ها هستند كه زحمت محاسبات را بر دوش مي كشند.برای دیدن یک شماره سریال در فرمت زمان باید فرمت سلول ها را زمان تبدیل به فرمت عدد نمود.اکسل از دو سیستم تاریخی پشتیبانی می کند : 1-مبنای تاریخ 1900 میلادی (قراردادی ویندوز)2-مبنای تاریخ 1904 میلادی یکی از مزایای استفاده از سیستم تاریخی 1904 در این است که تاریخهای منفی را نمایش می دهدبرای تغییر فرمت قراردادی زمان شما نیاز دارید تا از طریق مسیر tools/option/calculate /1904date system
گزینه فوق را باید فعال نمود .برای اینکه فرمتهای مختلف تاریخی را بتوانیم در سلول تنظیم بنمائیم نیاز است تا از مسیر کلیک راست formatcell/number/custom /فرمت ها را به شکل های زیر وارد بنمائبد . به عنوان مثال در صورتی که بخواهیم سال را به صورت دو رقمی وارد بنمائیم باید فرمت را بدینگونه وارد نمود yy/mm/dd
موارد نمونه زیر جهت درک بهتر موضوع آورده شده است
Yy/mmmm/ddd 78/march/fri yyyy/mm/ddd 1978/03/friMmmm/dddd March/friday Yy-ddd 78-fri فرمت زمان :یاد داشتن فرمت زمان از اهمیت بسیار بالائی برخوردار میباشد . زیرا در بسیاری موارد ما نیاز داریم
محاسباتی را بروی اطلاعات زمان انجام دهیم مانند : ورود خروج پرسنل – حقوق دستمزدزمان خرابی دستگاهها – برنامه ریزی تولیدکنترل زمانبندی فعالیتهای یک پروژه نکات مهم زیر در مورد وارد کردن فرمت زمان در اکسل دارای اهمیت میباشد .1.اگر پس از انتخاب یک سلول کلید ctrl+shift+:را فشار دهیم زمان وقت کامپیوتر در ان سلول وارد میشود .یک زمان مثلا8:00ساعته از نظر عددی برابر است با کسری از عدد 24 یعنی تقریبا 0.33در یک فرمول فرمت تاریخی را در فرمول نمیتوان تایپ کرد همانند
(sum(05:33;a1=در صورتی که نیاز باشد زمان در فرمول وارد شود باید value ان را گذاشت مثلا همان 0.33برای جمع ساعت های که حاصل آنها بیشتر از 24میشود فرمت سلول را باید همانند زیر نوشتh]:mm] یعنی hرا که نشان دهنده ساعت میباشد باید در [ ]گذاشت 5.برای استفاده از تعدادی توابع زمان شما نیاز دارید تا Analysis Toolpak
را نصب نمیایید در برخی موارد ما نیاز داریم زمان های مختلف را جمع نمائیم و در یک عدد خاصی ضرب نمائیم به عنوان مثال ساعت های اضافه کاری را جمع نمائیم در نرخ اضافه کاری ضرب نمائیم اگر در این حالت بخواهیم به جواب درست دست بیابیم باید حاصل را در عدد 24 ضرب بنمائیم .
sum(d1:D30)*1000*24=افراد مختلف در فایل های مختلف از فرمت های زیر برای تاریخ استفاده مینمایند که معایب و مزایا هر کدام را توضیح میدهیم 1. تاریخ را به فرمت تاریخ میلادی بنویسیم ولی در قسمت format cell>custom فرمت ان را به صورت yy/mm/ddنوشت در این حالت تاریخ 21/05/1987 به صورت 21/05/87دیده میشوددراین حالت تاریخها به راحتی مقایسه میشوند و فرمت اطلاعات هم تاریخ هست و لی از نظر محاسبات روی تاریخ مشکل داریم مثلا ما تاریخ 31/03/87در تاریخ شمسی داریم ولی در تاریخ میلادی این تاریخ وجود ندارد چون ماه 3 انها 31 روزه نمیباشد و مشکل دوم اگر از توابع فاصله بین 2 تاریخ استفاده کنیم جواب با خطائ روبرو میشود.2.حالت دوم نوشتن تاریخ به صورت عدد مثلا 870321این روش نوشتن بسیار ساده میباشد با روش عادی قابل محاسبه روزهای این 2 تاریخ نیست ولی براحتی مقایسه میگردد و هر گونه عددی را میتوان نوشت حالت سوم view
مناسبی را دارد و به صورت 21/05/1387نوشته میشود و نه مقایسه بر روی ان
انجام میشود نه محاسبه در این حالت اطلاعات در حکم text میباشد .روش چهارم استفاده از کدهای vba موجود در اینترنت میباشد توابع زمان تاریخ Date اين تابع شماره سريال يك تاريخ خاص را نشان مي دهد.
(Date (Year, Month, Day=
Year
مي تواند يك تا چهار رقم از بين 1 تا 9999 باشد. اگر ما ماه سال و روز را در 3 سلول مجزا نوشته باشیم در این روش میتوان آنها را به یک رشته تاریخی تبدیل نمائیم . Today
اين تابع تاريخ ميلادي روز جاري را برمي گرداند. اگر تابع را در سلولي كه فرمت آن، قالب Generalمي باشد وارد كنيد مقدار نتيجه به صورت mm/dd/yyyy
نمايش مي يابد.today() اين تابع هيچ آرگوماني را نمي گيرد ولي حتماً پرانتزهاي خالي را بياوريد. این سلول با هر با بازکردن فایل و اکتیو کردن هر سلول این فرمول به روز میشود یعنی بر اساس تاریخ کامپیوتر زمان را نمایش میدهد Days 360 تعداد روزهاي بين دو تاريخ را براساس يك سال 360 روزه برمي گرداند. (Day360 (start-date, end-date, method= Method: يك مقدار منطقي است اگر Falseباشد يا آورده نشود در محاسبه از روش آمريكايي استفاده مي شود اگر Trueباشد روش اروپايي بكار گرفته مي شود. Day مقدار قسمت عددروز مربوط يك مقدار تاريخ/زمان سريال را برمي گرداند.
(Day (serial-number= serial-number: يك مقدار تاريخ، يك آدرس يا متن به صورت ثابت تاريخ در زوج علامت نقل مي تواند باشد.مثال: Day (2002/01/04) 04 توابع month - year–day نیز همانند این تابع عمل مینماید. Weekday :شماره سريال روز هفته را مي دهد.
=Weekday (serial-number, return-type) Serial-number: يك مقدار تاريخ- يك آدرس يا متني به فرم تاريخ در علامت نقل قول. اگر عددي را وارد نكنيم يا 1 باشد مبناي شروع هفته از يك شنبه خواهد بود، در نتيجه روز 2 شنبه عدد 2 و سه شنبه عدد 3 و الي آخر خواهد بود.فشردن همزمان کلید CTRL+; در یک سلول تاریخ کامپیوتر را می دهد .توابع زمان
TIME
اين تابع شماره سريال مربوط به زمان مشخص شده را مي آورد.
= TIME (hour, minute, second) مثال: = TIME (13:30:20) HOUR
اين تابع ساعت متناظر با شماره سريال را برمي گرداند.
= HOUR (serial-number) = HOUR (0.5627) = (13:30:20) NOW ( )
اين تابع تاريخ و زمان فعلي را درج مي نمايد. كه شامل يك مقدار صحيح (تاريخ) و يك مقدار اعشاري (زمان) مي باشد. برای استفاده از تعدادی توابع زمان شما نیاز دارید تا Analysis Toolpak
را نصب نمیایید. WEEKNUM تعداد هفته را در یک سال مشخص می دهد