برای ایجاد نمایش آپدیت خودکار در اکسل ، فرض میکنیم که لیستی از نامها دارید که برای هر کدام ساعت متفاوتی وارد شده است که باید با آنها تماس بگیرید و حالا میخواهیم در ستون کمکی نمایش دهد که چقدر زمان تا موعد تماس باقی مانده است و یا زمان آن گذشته است و یا زمان تماس گرفتن است.
فهرست محتوا
شرح مسئله آپدیت خودکار در اکسل
تصویر زیر را درنظر بگیرید ، می بینید که یک سری زمان تماس وارد شده که کاربر باید در آن زمانها کاری را انجام دهد . حالا می خواهیم در ستون کناری حالتهای زیر اتفاق بیوفتد .
- اگر ساعت ویندوز از ساعت تماس بزرگتر باشد ، از زمان تماس گذشته
- اگر 5 دقیقه به زمان انجام باقی باشد و یا 5 دقیقه گذشته باشد ، زمان تماس گرفتن است
- اگر زمان انجام کار از زمان ویندوز کمتر بود اختلاف زمانی نمایش داده شود .
چالش :
خب احتمالا به ذهن شما می رسد که با تابع IF به صورت تودرتو به راحتی می توان این مسئله را حل کرد ، اما مشکلی وجود دارد ، آن هم اینکه این اتفاقات باید به صورت هر یک دقیقه یک بار آپدیت خودکار در اکسل شوند و نباید از کدنویسی اکسل استفاده کنید .
تابع Time برای بدست آوردن ساعت جاری سیستم
ابتدا ما باید ساعت فعلی سیستم را در یک سلول داشته باشیم تا بر اساس آن اختلاف زمان انجام کار با زمان فعلی را محاسبه کنیم .
میدانیم که تابع NOW بدون آرگومان نمایش دهنده تاریخ و زمان فعلی ویندوز در اکسل است ، اما ما فقط به ساعت و دقیقه نیاز داریم پس آن را با تابع Time ترکیب می کنیم .
در یکی از سلولها که در این مثال D1 می باشد فرمول زیر را می نویسیم تا زمان فعلی ویندوز را نمایش دهد .
=TIME(HOUR(NOW()),MINUTE(NOW()),0)
برای راحتی کار سلول D1 را انتخاب می کنیم و نام cTime را برای آن در نظر می گیریم. اگر این کار را نکنیم هر جا فرمول می نویسیم و نیاز به زمان فعلی داشته باشیم باید از $D$1 استفاده کنیم . قبلا در مورد آدرسهای مطلق و نسبی به طور کامل در دوره جامع مقدماتی اکسل توضیخ داده ایم .
از این پس در هر فرمولی که از cTime استفاده کنیم منظور ما زمان فعلی ویندوز است که در سلول D1 قرار گرفته است.
تابع IF برای نمایش حالتهای مختلف
مراحل قبلی را که انجام دادیم ، زمان آن رسیده که برای نمایش حالتهای مختلف و آپدیت خودکار در اکسل که قبلا بحث کردیم کافیست فرمول زیر را در ستون مقابل ساعتهای انجام کار وارد کنیم و از تابع IF به صورت تودرتو استفاده کنیم.
=IF(ABS(C4-cTime)<=0.0035,"Call",IF(ABS(cTime-C4)<=0.0035,"call",IF(cTime>C4,"Too Late",IF(C4=cTime,"Call",C4-cTime))))
با انجام فرمول فوق ، 5 دقیقه مانده به زمان انجام کار و تا 5 دقیقه بعد از زمان انجام کار نوشته داخل سلول به Call تغییر می کند و اگر از زمان کذشته باشد کلمه Too Late نوشته می شود و اگر به زمان باقی مانده باشد هم زمان باقی مانده را نمایش می دهد .
آپدیت خودکار در اکسل بدون کد نویسی
اگر نخواهیم از کدنویسی در اکسل استفاده نمائیم برای بروزرسانی داده ها به صورت خودکار در اکسل می توانیم مراحل زیر را انجام دهیم .
- یک شیت جدید ایجاد می کنیم و سلول A1 و A2 را داده های دلخواه وارد می کنیم .
- حالا مخدوده A1:A2 را انتخاب می کنیم و با زدن کلیدهای Ctrl + T آن را جدول می کنیم.
- از تب Data گزینه From Table/Range را انتخاب می کنیم.
- نمای پاور کوئری باز میشود .
- روی Save and load کلیک میکنیم .
- روی کانکشن موجود سمت راست کلیک راست کرده و Properties را انتخاب می کنیم .
- زمان بروزرسانی رو روی یک دقیقه قرا می دهیم.
