گیدو باید زمان های منفی را وارد سلول ها کند. اگر او یک زمان مثبت (2:00) وارد کند، اکسل آن را به خوبی تشخیص می دهد. اگر بخواهد -2:00 را تایپ کند، اکسل به او می گوید که در فرمول او خطایی وجود دارد. Guido در مورد ساده ترین راه برای وارد کردن یک مقدار زمانی منفی در یک سلول و اینکه اکسل آن را به عنوان یک مقدار زمانی تشخیص دهد تعجب می کند.
قبل از ارائه چند پاسخ احتمالی به این سوال، خوب است نحوه مشاهده تاریخ و زمان اکسل را درک کنید. در داخل، تاریخ ها و زمان ها به عنوان شماره سریال ذخیره می شوند که چیزی جز اعداد واقعی نیستند. فرض کنید یک تاریخ را در سلول B4 وارد کرده اید، مانند "17 مارس 2020" (بدون علامت نقل قول. اکسل متوجه می شود که شما یک تاریخ را وارد می کنید و آن را به شماره سریال تبدیل می کند. شما نمی توانید شماره سریال آن را بگویید، زیرا اکسل به طور مفید تاریخ نشان داده شده توسط شماره سریال را به شما نشان می دهد (شکل 1 را ببینید.)
شکل 1. وارد کردن تاریخ در اکسل.
توجه داشته باشید که تاریخ در سلول به عنوان 17-مارس-20 و در نوار فرمول به عنوان 3/17/2020 نشان داده می شود. (دقیقا نحوه نمایش تاریخ توسط اکسل به نحوه وارد کردن آن و تنظیمات منطقه ای شما در ویندوز بستگی دارد.) تاریخ نشان داده شده در سلول (17-مارس-20) نتیجه نحوه قالب بندی سلول است. وقتی تاریخ را در سلول وارد کردید و از آنجایی که سلول قبلاً قالب "عمومی" داشت، اکسل قالب بندی سلول را تغییر داد تا تاریخ را به بهترین شکل ممکن نمایش دهد.
به دلیل تاریخ نشان داده شده در نوار فرمول (3/17/2020) ممکن است فکر کنید که این روشی است که اکسل تاریخ ها را به صورت داخلی ذخیره می کند. اینطور نیست، شما می توانید این را با وارد کردن یک فرمول در یک سلول مجاور (=B4) و سپس قالب بندی آن سلول به عنوان "General" ببینید. (شکل 2 را ببینید.)
شکل 2. مشاهده اینکه چگونه اکسل یک تاریخ را ذخیره می کند.
نوار فرمول فرمول (=B4) را همانطور که باید نشان می دهد، اما سلول مقدار 43907 را نشان می دهد. اگر تاریخ سلول B4 را به صورت کلی قالب بندی کرده باشید، همان مقدار را نیز نشان می دهد. این شماره سریال است. این راهی است که اکسل تاریخ ها را به صورت داخلی ذخیره می کند.
اما شماره سریال به چه معناست؟ این "شمار" تعداد روزهای پس از تاریخ پایه است. معمولاً این تاریخ پایه 1 ژانویه 1900 است و آن روز روز 1 است. 2 ژانویه 1900 روز دوم خواهد بود و غیره. بنابراین، 43907 روز 43907 است که از 1 ژانویه 1900 شروع می شود.
با این حال، ممکن است تاریخ پایه یک کتاب کار متفاوت باشد. اکسل، برای اهداف تاریخی، دو تاریخ پایه ممکن را درک می کند. دیگری به عنوان سیستم تاریخ 1904 نامیده می شود. شمارش با 1 ژانویه 1904 شروع می شود. برخلاف سیستم تاریخ 1900، سیستم تاریخ 1904 با 0 شروع به شمارش می کند. و غیره.
با دنبال کردن مراحل زیر می توانید ببینید که کدام سیستم تاریخ در کتاب کار استفاده می شود:
شکل 3. گزینه های پیشرفته در کادر محاوره ای Excel Options.
- کادر محاوره ای Excel Options را نمایش دهید. (در اکسل 2007 روی دکمه Office کلیک کنید و سپس روی Excel Options کلیک کنید. در اکسل 2010 یا نسخه جدیدتر، تب File را در نوار نمایش داده و سپس روی گزینه ها کلیک کنید.)
- در سمت چپ کادر گفتگو روی Advanced کلیک کنید.
- به پایین اسکرول کنید تا قسمت When Calculating this Workbook را ببینید. (شکل 3 را ببینید.)
- توجه داشته باشید که آیا کادر بررسی Use 1904 Date System انتخاب شده است یا خیر.
- کادر محاوره ای را ببندید.
اگر چک باکس در مرحله 4 انتخاب شده باشد، کتاب کار شما از سیستم تاریخ 1904 استفاده می کند. اگر انتخاب نشده باشد، از سیستم تاریخ 1900 استفاده می کنید.
این چه ربطی به سوال اصلی Guidos در مورد ورود به زمان های منفی دارد؟ در واقع فراوان است. ببینید، مقدار زمانی برای اکسل، چیزی بیش از بخشی از یک روز نیست. توجه داشته باشید که نمونه های ارائه شده تا کنون شامل تاریخ هایی است که به عنوان شماره سریال کامل ذخیره می شوند، در این مورد 43907.
اگر تاریخ را با زمان در یک سلول وارد کنید، مانند "3/17/20 6:00 بعد از ظهر" (دوباره بدون علامت نقل قول، سپس به عنوان بخشی از شماره سریال در سمت راست اعشار ذخیره می شود. نقطه (شکل 4 را ببینید.)
شکل 4. مشاهده نحوه ذخیره اکسل تاریخ و زمان.
بخش کسری ذخیره شده با شماره سریال (.75) به این معنی است که زمانی که وارد کرده اید (6:00 بعد از ظهر) 75٪ در طول روز است. زمان - بخش اعشاری - می تواند از 0.0 (12:00:00 صبح) تا 0.999988426 (11:59:59 بعد از ظهر) باشد.
به دلیل این طرح ذخیره سازی تاریخ ها و زمان ها به صورت داخلی به عنوان شماره سریال، چیزی به نام "زمان منفی" در دنیای اکسل وجود ندارد. هیچ راهی برای تجزیه و ذخیره چنین مقداری وجود ندارد، بنابراین اکسل هنگام تلاش پیغام خطا می دهد. می‌توانید «ریاضی تاریخ» را انجام دهید و زمان را از مقدار زمانی که قبلاً در یک سلول ذخیره شده کم کنید، اما تنها کاری که انجام می‌دهید تنظیم تاریخ و شماره سریال زمانی است. به عنوان مثال، اگر شماره سریال 43907.75 را در یک سلول ذخیره کرده باشید و یک "زمان" از آن کم کنید که نتیجه منفی می دهد، تمام کاری که انجام داده اید این است که به روز قبل بروید. (شکل 5 را ببینید.)
شکل 5. تفریق از تاریخ و زمان.
توجه داشته باشید که در سلول B6 یک زمان، بدون تاریخ همراه، وارد شده است (10:00 شب). این مورد توسط اکسل به عنوان یک شماره سریال با "تاریخ صفر" همانطور که در سلول C6 نشان داده شده است، تجزیه و تحلیل شد. اگر سعی کنید زمانی را از آن شماره سریال کم کنید که نتیجه منفی دارد، اکسل نمی تواند نتیجه را به عنوان زمان نمایش دهد. (شکل 6 را ببینید.)
شکل 6. شما نمی توانید خیلی از زمان ها را کم کنید.
اگر نشانگر ماوس را روی علامت های هش نشان داده شده در سلول B9 قرار دهید، پیام خطا را مشاهده خواهید کرد: "تاریخ ها و زمان هایی که منفی یا خیلی بزرگ هستند به صورت ###### نمایش داده می شوند." (مطلب دقیق پیام خطا ممکن است از نسخه ای به نسخه دیگر اکسل متفاوت باشد.)
اگر سعی کنید از راه های دیگر به زمان های منفی نیز برسید، همان نتیجه را خواهید گرفت. به عنوان مثال، اگر مقدار زمانی را در یک سلول وارد کنید و سپس در یک سلول دیگر، از فرمولی برای ضرب زمان در -1 استفاده کنید. وقتی سعی می کنید نتیجه را به صورت زمان قالب بندی کنید، دوباره علامت های هش را دریافت می کنید. اکسل به سادگی نمی تواند زمان های منفی را نمایش دهد زیرا تا آنجا که به برنامه مربوط می شود نمی توانند وجود داشته باشند.
پس Guido باید چه کار کند؟ اگر او واقعاً نیاز به کار با زمان های منفی داشته باشد، به دلایلی که قبلاً توضیح داده شده است، کار بسیار کمی می تواند انجام دهد. (هر چند در یک لحظه بیشتر در این مورد توضیح دهید.) اگر او به سادگی می خواهد زمان ها را کم کند، داستان متفاوت است. این واقعیت که گویدو سعی می کند چیزی مانند -02:00 را وارد کند، نشان می دهد که او با مدت زمان رویداد کار می کند، نه با "زمان های ساعت" واقعی. (به یاد داشته باشید که اکسل با زمان های ساعت کار می کند تا تجزیه خود را انجام دهد و 02:00- زمان ساعت نیست - نمی توانید به کسی بگویید که در 02:00- با آنها ملاقات خواهید کرد.)
برای رسیدگی به مدت زمان رویداد، می‌توانید به سادگی اجازه دهید اکسل این محاسبات را برای شما انجام دهد. به عنوان مثال، زمان شروع را می توان در سلول B4 قرار داد و تعدادی ساعت را که Guido می خواهد از آن کم کند (2) را می توان در سلول C4 قرار داد. سپس در سلول D4 می تواند از یک فرمول برای محاسبه نتیجه استفاده کند:
=B4 – (C4 / 24)
تقسیم تعداد ساعت بر 24 مقدار اعشاری صحیحی را بدست می دهد که در داخل اکسل در شماره سریال آن استفاده می شود. البته، اگر نتیجه فرمول منجر به شماره سریال منفی شود، هنگامی که می‌خواهید نتیجه را به عنوان زمان قالب‌بندی کنید، علائم هش را دریافت خواهید کرد. تنها راه حل این است که مطمئن شوید آنچه در سلول B4 ذخیره می شود حاوی تاریخ است. شما می توانید سلول B4 را طوری قالب بندی کنید که فقط زمان را نشان دهد، اما درج تاریخ باعث می شود که نتیجه خطا نباشد. با نگاهی به این ExcelTip می‌توانید درباره کار با زمان‌های سپری شده منفی اطلاعات بیشتری کسب کنید :
http://excelribbon.PCcloob.Ir/T006239
بعد از اینکه همه چیز گفته شد و انجام شد، یک راه وجود دارد که می‌توانید با زمان‌های منفی واقعی کار کنید: همانطور که قبلاً بحث شد، به سیستم تاریخ 1904 بروید. به عنوان مثال، هنگام استفاده از سیستم تاریخ 1904، به نتایج تفریق ها نگاه کنید. (شکل 7 را ببینید.)
شکل 7. سیستم تاریخ 1904 زمان های منفی را مدیریت می کند.
این رویکرد برای کتاب‌های کاری جداگانه در سیستم شما عالی عمل می‌کند، اما ممکن است زمانی که دیگران از Workbook شما استفاده می‌کنند چندان عالی نباشد. تکیه بر کتاب‌های کاری که از سیستم‌های تاریخ متفاوتی استفاده می‌کنند (بعضی از 1900 و برخی دیگر 1904) می‌تواند دلیل مشکلات احتمالی باشد زیرا شما (یا دیگران) اطلاعات را از Workbook به Workbook کپی می‌کنید. این موضوع در یک ExcelTip متفاوت مورد بحث قرار گرفت :
http://excelribbon.PCcloob.Ir/T010942
هنگام استفاده از سیستم تاریخ 1904، همچنان هنگام وارد کردن زمان های منفی با ***** مواجه می شوید، اما نتایج فرمول ها می توانند زمان های منفی را بدون مشکل نشان دهند.
