نورا سال ها گزارش آب و هوا را نگه داشته است. هر برگ از کتاب کار یک سال جداگانه است، ستون A در هر برگ تاریخ های سال و ستون B مقدار بارندگی در آن روز است، در صورت وجود. نورا مایل است نموداری ایجاد کند که بارندگی سال جاری را در مقایسه با بارندگی سال گذشته تا به امروز نشان دهد. او متعجب است که آیا راهی برای ارجاع خودکار این نمودار به مقادیر صحیح بارش برای هر دو سال بر اساس تاریخ امروز وجود دارد؟
بسته به اینکه دقیقاً چه چیزی را می خواهید به دست آورید، چند راه وجود دارد که می توان این نیاز را پر کرد. اگر فقط می‌خواهید میزان بارندگی امسال را با بارندگی سال گذشته مقایسه کنید، می‌توانید به راحتی این کار را با تنظیم محدوده‌های نامگذاری شده پویا که داده‌هایی را که می‌خواهید استفاده کنید تعریف می‌کنند، انجام دهید.
برای شروع، اجازه دهید فرض کنیم که داده های شما برای سال 2022 در یک کاربرگ به نام 2022 و داده های شما برای سال 2023 (تا کنون) در یک کاربرگ به نام 2023 قرار دارد. در هر کاربرگ، ردیف 1 شامل سرفصل هایی است، به این معنی که تاریخ های شما در واقع از این صفحه شروع می شوند. سلول A2 و خوانش بارش شما در سلول B2. برای تنظیم محدوده ها مراحل زیر را دنبال کنید:
شکل 1. کادر محاوره ای New Name.
=OFFSET(INDIRECT(YEAR(NOW())&"!A1"),1,1,TODAY()-DATE(YEAR(NOW()),1,1)+1,1)
=OFFSET(INDIRECT(YEAR(NOW())-1&"!A1"),1,1,TODAY()-DATE(YEAR(NOW()),1,1)+1,1)
=OFFSET(INDIRECT(YEAR(NOW())&"!A1"),1,0,TODAY()-DATE(YEAR(NOW()),1,1)+1,1)
- تب Formulas روبان را نمایش دهید.
- در گروه نام‌های تعریف شده، روی ابزار مدیریت نام کلیک کنید. اکسل کادر محاوره ای Name Manager را نمایش می دهد.
- روی دکمه New کلیک کنید. اکسل کادر محاوره ای New Name را نمایش می دهد. (شکل 1 را ببینید.)
- در قسمت Name، نام CurrentYear را وارد کنید (توجه داشته باشید که این یک کلمه است، بدون فاصله).
- در قسمت Refers To فرمول زیر را وارد کنید:
- روی دکمه OK کلیک کنید تا ایجاد محدوده نامگذاری شده به پایان برسد. محدوده جدید باید در کادر محاوره ای Name Manager ظاهر شود.
- دوباره روی دکمه New کلیک کنید. Excel (دوباره) کادر محاوره ای New Name را نمایش می دهد.
- در قسمت Name، نام PreviousYear را وارد کنید (توجه داشته باشید که این یک کلمه است، بدون فاصله).
- در قسمت Refers To فرمول زیر را وارد کنید:
- روی دکمه OK کلیک کنید. محدوده جدید باید در کادر محاوره ای Name Manager ظاهر شود.
- برای بار سوم روی دکمه New کلیک کنید. اکسل کادر محاوره ای New Name را نمایش می دهد.
- در قسمت نام، نام تاریخ ها را وارد کنید .
- در قسمت Refers To فرمول زیر را وارد کنید:
- روی دکمه Close کلیک کنید تا کادر گفتگوی Name Manager حذف شود.
با محدوده های تعریف شده، اکنون می توانید نمودار را با استفاده از این محدوده ها ایجاد کنید:
شکل 2. کادر محاوره ای Edit Series.
شکل 3. کادر محاوره ای Axis Labels.
- زبانه Insert روبان را نمایش دهید.
- در گروه نمودارها روی گزینه Column کلیک کنید. اکسل یک لیست کشویی را نمایش می دهد.
- یک بار بر روی قالب Clustered Column کلیک کنید. (این اولین گزینه تحت عنوان ستون دو بعدی است.) اکسل یک شی نمودار خالی در کاربرگ شما ایجاد می کند.
- روی شی نمودار کلیک راست کنید. اکسل یک منوی زمینه را نمایش می دهد.
- از منوی Context گزینه Select Data را انتخاب کنید. اکسل کادر محاوره ای Select Data Source را نمایش می دهد که باید کاملا خالی باشد زیرا هیچ محدوده داده ای را به نمودار اضافه نکرده اید.
- در سمت چپ کادر محاوره ای، در زیر عنوان Legend Entries (Series)، روی دکمه Add کلیک کنید. اکسل کادر محاوره ای Edit Series را نمایش می دهد. (شکل 2 را ببینید.)
- در قسمت نام سری، 2022 را وارد کنید .
- در قسمت Series Values، این را وارد کنید: 2022!PreviousYear .
- روی دکمه OK کلیک کنید. سری داده ها اکنون در کادر محاوره ای انتخاب منبع داده ظاهر می شود.
- دوباره روی همان دکمه Add که در مرحله 6 کلیک کردید کلیک کنید. اکسل دوباره کادر محاوره ای Edit Data Series را نمایش می دهد.
- در قسمت نام سری، 2023 را وارد کنید .
- در قسمت Series Values، این را وارد کنید: 2023! CurrentYear .
- روی دکمه OK کلیک کنید. این سری داده دوم اکنون در کادر محاوره ای انتخاب منبع داده ظاهر می شود.
- در سمت راست کادر محاوره‌ای، در زیر عنوان برچسب‌های محور افقی (دسته)، روی دکمه ویرایش کلیک کنید. اکسل کادر محاوره ای Axis Labels را نمایش می دهد. (شکل 3 را ببینید.)
- در محدوده برچسب محور، این را وارد کنید: 2023!Dates .
- روی دکمه OK کلیک کنید. کادر محاوره ای انتخاب منبع داده باید به طور کامل با اطلاعات لازم پر شود.
- روی OK کلیک کنید تا کادر محاوره ای انتخاب منبع داده حذف شود.
نمودار به روز شده شما، که فقط تاریخ های تا تاریخ امروز را نشان می دهد، اکنون باید قابل مشاهده باشد. می توانید به شکل دلخواه نمودار را ادامه دهید. (به عنوان مثال، احتمالاً می خواهید تاریخ های نمودار را طوری قالب بندی کنید که شامل یک سال نباشد.) علاوه بر این، نمودار پویا است، به طوری که وقتی فردا کتاب کار را باز می کنید، یک روز بیشتر از امروز منعکس می شود.
راه دیگری برای رسیدگی به آن این است که در نحوه ذخیره داده های خود تجدید نظر کنید. به‌جای ذخیره‌سازی تمام مقادیر بارندگی در کاربرگ‌های جداگانه (بر اساس سال)، همه آنها را در یک کاربرگ قرار دهید. از آنجایی که اکسل می تواند بیش از یک میلیون ردیف داده را در یک کاربرگ مدیریت کند، با هیچ محدودیت عملی روبرو نخواهید شد. (یک میلیون ردیف نشان دهنده بیش از 2700 سال است.)
اکنون، در یک کاربرگ متفاوت، می توانید از دو فرمول آرایه برای محاسبه بارندگی تجمعی برای هر دو سال، تا به امروز استفاده کنید. فرمول آرایه زیر میزان بارندگی سال قبل را نشان می دهد:
=SUM(Data!B2:B1000*IF(Data!A2:A1000>=DATE(YEAR(NOW())-1,1,1),IF(Data!A2:A1000
این فرض را بر این می‌گذارد که قرائت‌های اولیه بارش در یک کاربرگ به نام داده هستند و بیش از 1000 ردیف گسترش نمی‌یابند. (در صورت لزوم می توانید یکی از این موارد را تغییر دهید.) برای دریافت بارندگی به روز برای امسال، می توانید از این فرمول آرایه استفاده کنید:
=SUM(Data!B2:B1000*IF(Data!A2:A1000>=DATE(YEAR(NOW()),1,1),IF(Data!A2:A1000
به یاد داشته باشید: هر دو فرمول آرایه هستند، بنابراین باید با استفاده از Ctrl+Shift+Enter وارد شوند . مقدار منفرد برگردانده شده توسط هر فرمول نشان دهنده بارندگی تجمعی از هر سال تا به امروز است. این دو مقدار می توانند در هر نموداری که می خواهید استفاده شوند.
