فهرست
در کار حرفه ای با صفحات گسترده، تعامل با تاریخ و زمان غیر معمول نیست. شما نمی توانید بدون آن انجام دهید. بنابراین، خود خدا دستور داد که یاد بگیرند که چگونه با داده هایی از این نوع کار کنند. این باعث صرفه جویی در زمان شما می شود و از اشتباهات زیادی در حین کار با صفحات گسترده جلوگیری می کند.
متأسفانه، بسیاری از مبتدیان نمی دانند که چگونه داده ها پردازش می شوند. بنابراین، قبل از در نظر گرفتن این دسته از عملیات، لازم است برنامه آموزشی دقیق تری انجام شود.
نحوه نمایش تاریخ در اکسل
اطلاعات تاریخ به عنوان تعداد روزهایی از 0 ژانویه 1900 پردازش می شود. بله، اشتباه نمی کنید. در واقع از صفر اما این لازم است تا یک نقطه شروع وجود داشته باشد، به طوری که 1 ژانویه قبلاً شماره 1 در نظر گرفته شود و غیره. حداکثر مقدار تاریخ پشتیبانی شده 2958465 است که به نوبه خود 31 دسامبر 9999 است.
این روش امکان استفاده از تاریخ را برای محاسبات و فرمول ها فراهم می کند. بنابراین، اکسل امکان تعیین تعداد روزهای بین تاریخ ها را فراهم می کند. این طرح ساده است: عدد دوم از یک عدد کم می شود و سپس مقدار حاصل به قالب تاریخ تبدیل می شود.
برای وضوح بیشتر، در اینجا جدولی وجود دارد که تاریخ ها را با مقادیر عددی مربوطه نشان می دهد.
برای تعیین تعداد روزهایی که از تاریخ A تا B گذشته است، باید اولین را از آخرین آن کم کنید. در مورد ما، این فرمول است = B3-B2. پس از وارد کردن آن، نتیجه به شرح زیر است.
مهم است که توجه داشته باشید که مقدار آن بر حسب روز است زیرا ما قالب متفاوتی را برای سلول نسبت به تاریخ انتخاب کرده‌ایم. اگر در ابتدا قالب "تاریخ" را انتخاب کرده بودیم، نتیجه این می شد.
توجه به این نکته در محاسبات بسیار مهم است.
یعنی برای نمایش شماره سریال صحیح که به طور کامل با تاریخ مطابقت دارد، باید از هر فرمتی غیر از تاریخ استفاده کنید. به نوبه خود، برای تبدیل شماره به تاریخ، باید قالب مناسب را تنظیم کنید.
نحوه نمایش زمان در اکسل
نحوه نمایش زمان در اکسل کمی با تاریخ متفاوت است. روز به عنوان مبنا در نظر گرفته می شود و ساعت ها، دقیقه ها، ثانیه ها قسمت های کسری آن هستند. یعنی 24 ساعت 1 است و هر مقدار کوچکتر به عنوان کسر آن در نظر گرفته می شود. بنابراین، 1 ساعت 1/24 روز، 1 دقیقه 1/1140 و 1 ثانیه 1/86400 است. کوچکترین واحد زمان موجود در اکسل 1 میلی ثانیه است.
مشابه تاریخ ها، این شیوه نمایش، انجام محاسبات را با زمان ممکن می سازد. درست است، اینجا یک چیز ناخوشایند وجود دارد. پس از محاسبات، بخشی از روز را دریافت می کنیم، نه تعداد روزها.
اسکرین شات مقادیر را در قالب عددی و قالب "زمان" نشان می دهد.
روش محاسبه زمان مشابه تاریخ است. باید زمان قبل را از زمان بعد کم کرد. در مورد ما، این فرمول است = B3-B2.
از آنجایی که سلول B4 ابتدا یک قالب عمومی داشت، سپس در پایان معرفی فرمول، بلافاصله به "زمان" تغییر می کند.
اکسل، هنگام کار با زمان، عملیات حسابی معمول را با اعداد انجام می دهد، که سپس به فرمت زمانی آشنا برای ما ترجمه می شود.
فرمت تاریخ و زمان
تا آنجا که می دانیم، تاریخ ها و زمان ها را می توان در قالب های مختلف ذخیره کرد. بنابراین، باید بدانید که چگونه آنها را به درستی وارد کنید تا قالب بندی صحیح باشد.
البته هنگام وارد کردن تاریخ و ساعت می توانید از شماره سریال روز یا بخشی از روز استفاده کنید، اما این روش بسیار ناخوشایند است. علاوه بر این، شما باید دائماً یک قالب خاص را به سلول اعمال کنید، که فقط ناراحتی را افزایش می دهد.
بنابراین اکسل به شما امکان می دهد زمان و تاریخ را به روش های مختلف مشخص کنید. اگر یکی از آنها را اعمال کنید، برنامه بلافاصله اطلاعات را به عدد مناسب تبدیل می کند و فرمت صحیح را در سلول اعمال می کند.
برای لیستی از روش های ورودی تاریخ و زمان که توسط اکسل پشتیبانی می شوند، به جدول زیر مراجعه کنید. ستون سمت چپ فرمت های ممکن را فهرست می کند و ستون سمت راست نحوه نمایش آنها در اکسل پس از تبدیل را نشان می دهد. توجه به این نکته ضروری است که در صورت عدم تعیین سال، سال جاری که در سیستم عامل تنظیم شده است، به طور خودکار تعیین می شود.
در واقع راه های بسیار بیشتری برای نمایش وجود دارد. اما اینها کافی است. همچنین، گزینه ثبت تاریخ خاص ممکن است بسته به کشور یا منطقه و همچنین تنظیمات سیستم عامل متفاوت باشد.
قالب بندی سفارشی
هنگام کار با سلول ها، کاربر می تواند تعیین کند که چه قالبی خواهد بود. او می تواند آن را طوری بسازد که فقط زمان، ماه، روز و غیره نمایش داده شود. همچنین امکان تنظیم ترتیب فرمول بندی تاریخ و همچنین جداکننده ها وجود دارد.
برای دسترسی به پنجره ویرایش، باید تب "Number" را باز کنید، جایی که می توانید گزینه "Format Cells" را پیدا کنید. در کادر محاوره‌ای که باز می‌شود، یک دسته «تاریخ» وجود خواهد داشت که در آن می‌توانید قالب صحیح تاریخ را انتخاب کنید.
اگر دسته "زمان" را انتخاب کنید، بر این اساس، لیستی با گزینه هایی برای نمایش زمان ظاهر می شود.
برای اعمال یک گزینه قالب بندی خاص در یک سلول، باید فرمت مورد نظر را انتخاب کرده و روی OK کلیک کنید. پس از آن، نتیجه اعمال خواهد شد. اگر فرمت‌های کافی وجود ندارد که اکسل ارائه می‌کند، می‌توانید دسته «همه فرمت‌ها» را پیدا کنید. گزینه های زیادی نیز وجود دارد.
اگر هیچ گزینه ای مناسب نیست، همیشه می توانید گزینه خود را ایجاد کنید. انجام این کار بسیار آسان است. شما فقط باید فرمت های از پیش تعیین شده را به عنوان نمونه انتخاب کنید و مراحل زیر را دنبال کنید:
- سلولی را که می خواهید قالب آن را تغییر دهید انتخاب کنید.
- کادر محاوره ای "Format Cells" را باز کرده و تب "Number" را پیدا کنید.
- بعد، دسته "همه فرمت ها" باز می شود، جایی که ما فیلد ورودی "TYPE" را پیدا می کنیم. در آنجا باید یک کد قالب عددی را مشخص کنید. پس از وارد کردن آن، روی "OK" کلیک کنید.
- پس از این مراحل، سلول اطلاعات تاریخ و زمان را در قالب سفارشی نمایش می دهد.
استفاده از توابع با تاریخ و زمان
هنگام کار با تاریخ و زمان، کاربر می تواند از بیش از 20 عملکرد مختلف استفاده کند. و اگرچه ممکن است این مقدار برای کسی زیاد باشد، اما می توان از همه آنها برای دستیابی به اهداف خاصی استفاده کرد.
برای دسترسی به تمام عملکردهای ممکن، باید به دسته «تاریخ و زمان» از گروه «کتابخانه توابع» بروید. ما فقط برخی از توابع اصلی را در نظر خواهیم گرفت که امکان استخراج پارامترهای مختلف از تاریخ و زمان را فراهم می کند.
سال ()
امکان دریافت سالی را که مربوط به یک تاریخ خاص است را فراهم می کند. همانطور که می دانید، این مقدار می تواند بین 1900 و 9999 باشد.
سلول 1 تاریخ را در قالب DDDD DD.MM.YYYY hh:mm:ss نشان می دهد. این قالبی است که قبلا ایجاد کردیم. بیایید به عنوان مثال فرمولی را در نظر بگیریم که تعیین می کند چند سال بین دو تاریخ گذشته است.
در عین حال، اگر با دقت بیشتری نگاه کنید، معلوم می شود که تابع نتیجه کاملاً صحیح را محاسبه نکرده است. دلیلش این است که فقط از تاریخ در محاسبات خود استفاده می کند.
ماه ()
با استفاده از این تابع، می توانید تعداد ماه مربوط به یک تاریخ خاص را برجسته کنید. نتیجه ای از 1 تا 12 را برمی گرداند. این عدد به نوبه خود با تعداد ماه مطابقت دارد.
DAY()
مشابه توابع قبلی، این یکی تعداد روز را در یک تاریخ معین برمی گرداند. نتیجه محاسبه می تواند از 1 تا 31 متغیر باشد.
زمان()
همانطور که از نام آن پیداست، این تابع عدد ساعت را برمی گرداند که از 0 تا 23 متغیر است.
دقایق()
تابعی که تعداد دقیقه ها را در یک سلول خاص برمی گرداند. مقادیر ممکنی که برگردانده می شوند از 0 تا 59 هستند.
SECONDS()
این تابع مقادیر مشابه قبلی را برمی گرداند، با این تفاوت که ثانیه ها را برمی گرداند.
DAY()
با این تابع می توانید تعداد روزهای هفته را که در این تاریخ استفاده شده است، دریابید. مقادیر ممکن از 1 تا 7 هستند، اما به خاطر داشته باشید که شمارش معکوس از یکشنبه شروع می شود، نه دوشنبه، همانطور که معمولا انجام می دهیم.
با این حال، با استفاده از آرگومان دوم، این تابع به شما امکان می دهد قالب را سفارشی کنید. به عنوان مثال، اگر مقدار 2 را به عنوان پارامتر دوم ارسال کنید، می توانید قالب را طوری تنظیم کنید که عدد 1 به جای یکشنبه به معنای دوشنبه باشد. این برای کاربر داخلی بسیار راحت تر است.
اگر در آرگومان دوم 2 بنویسیم، در مورد ما تابع مقدار 6 را که مربوط به شنبه است، برمی گرداند.
امروز()
این تابع بسیار ساده است: هیچ آرگومان برای کار کردن آن مورد نیاز نیست. شماره سریال تاریخ تنظیم شده در رایانه را برمی گرداند. اگر روی سلولی اعمال شود که قالب عمومی برای آن تنظیم شده است، به طور خودکار به قالب "تاریخ" تبدیل می شود.
تاتا ()
این تابع نیز به هیچ آرگومان نیاز ندارد. به همان روش قبلی فقط با تاریخ و زمان کار می کند. در صورت نیاز به درج تاریخ و ساعت فعلی که در رایانه تنظیم شده است، از آن استفاده می شود. و درست مانند عملکرد قبلی، هنگام اعمال این یکی، سلول به طور خودکار به فرمت تاریخ و زمان تبدیل می شود، مشروط بر اینکه قالب "عمومی" قبلا تنظیم شده باشد.
هر دو عملکرد قبلی و این عملکرد به طور خودکار با هر بار محاسبه مجدد برگه تغییر می کنند و نمایش به روزترین زمان و تاریخ را ممکن می سازد.
به عنوان مثال، چنین فرمولی می تواند زمان فعلی را تعیین کند.
=TODAY()-TODAY()
در این حالت، فرمول کسری از روز را در قالب اعشاری تعیین می کند. درست است، اگر می خواهید دقیقا زمان نمایش داده شود و نه عدد، باید قالب زمان را در سلولی که فرمول در آن نوشته شده است اعمال کنید.
تاریخ ()
این تابع دارای سه آرگومان است که هر کدام باید وارد شوند. پس از محاسبات، این تابع شماره سریال تاریخ را برمی گرداند. سلول به طور خودکار به فرمت "تاریخ" تبدیل می شود اگر قبلا قالب "عمومی" داشته باشد.
استدلال روز یا ماه می تواند مثبت یا منفی باشد. در حالت اول تاریخ افزایش می یابد و در حالت دوم کاهش می یابد.
همچنین می توانید از عملیات ریاضی در آرگومان های تابع DATE استفاده کنید. به عنوان مثال، این فرمول 1 سال و 5 ماه و 17 روز به تاریخ سلول A1 اضافه می کند.
و چنین فرمولی این امکان را فراهم می کند که یک رشته متن را به یک تاریخ کاری کامل تبدیل کنید که می تواند در سایر توابع استفاده شود.
زمان()
درست مثل تابع تاریخ ()، این تابع دارای سه پارامتر مورد نیاز است - ساعت، دقیقه و ثانیه. پس از استفاده از آن، یک عدد اعشاری در سلول حاصل ظاهر می شود، اما خود سلول در صورتی که قبلا قالب "عمومی" را داشته باشد، در قالب "زمان" قالب بندی می شود.
با اصل عملکرد آن، تابع زمان() и تاریخ () خیلی چیزهای مشابه بنابراین، تمرکز بر روی آن منطقی نیست.
توجه به این نکته ضروری است که این تابع نمی تواند زمانی بیشتر از 23:59:59 را برگرداند. اگر نتیجه بیشتر از این باشد، عملکرد به طور خودکار به صفر بازنشانی می شود.
توابع تاریخ () и زمان() را می توان با هم اعمال کرد.
در این اسکرین شات، سلول D1 که از هر دوی این توابع استفاده می کند، دارای قالب تاریخ است.
توابع محاسبه تاریخ و زمان
در مجموع 4 تابع وجود دارد که به شما امکان می دهد عملیات ریاضی را با تاریخ و زمان انجام دهید.
DATAMES()
با استفاده از این تابع، می توانید شماره ترتیبی یک تاریخ را که پشت یک تعداد مشخص ماه (یا جلوتر از یک تاریخ معین) قرار دارد، پیدا کنید. این تابع دو آرگومان می گیرد: تاریخ شروع و تعداد ماه. استدلال دوم می تواند مثبت یا منفی باشد. اگر می خواهید تاریخ آینده را محاسبه کنید، گزینه اول باید مشخص شود و گزینه دوم - اگر قبلی باشد.
EOMONTH()
این تابع تعیین شماره ترتیبی آخرین روز ماه که عقب یا جلوتر از یک تاریخ معین است را ممکن می سازد. همان آرگومان های قبلی را دارد.
روز کاری()
همان عملکرد DATAMES()، فقط تاخیر یا پیشروی با تعداد معینی از روزهای کاری اتفاق می افتد. نحو مشابه است.
هر سه این توابع یک عدد را برمی‌گردانند. برای مشاهده تاریخ، باید سلول را به فرمت مناسب تبدیل کنید.
CLEAR()
این تابع ساده تعداد روزهای کاری بین تاریخ 1 و تاریخ 2 را تعیین می کند.