Нора вела журнал погоды в течение многих лет. Каждый лист рабочей книги представляет собой отдельный год, причем столбец A на каждом листе представляет даты в году, а столбец B — количество осадков в этот день, если таковые имеются. Нора хочет создать диаграмму, показывающую количество осадков в этом году по сравнению с прошлогодними осадками на сегодняшний день. Она задается вопросом, есть ли способ, чтобы эта диаграмма автоматически ссылалась на правильные значения осадков за оба года на основе сегодняшней даты.

Есть несколько способов удовлетворить эту потребность, в зависимости от того, чего именно вы хотите достичь. Если вы просто хотите сравнить количество осадков в этом году с количеством осадков в прошлом году по дате, вы можете легко сделать это, настроив несколько динамических именованных диапазонов, которые определяют данные, которые вы хотите использовать.

Для начала предположим, что ваши данные за 2015 год находятся на листе с именем 2015, а данные за 2016 год (пока) находятся на листе с именем 2016.

На каждом листе строка 1 содержит заголовки, что означает, что ваши даты фактически начинаются в ячейке A2, а ваши показания осадков — в ячейке B2.

Выполните следующие действия, чтобы настроить диапазоны:

  1. Откройте вкладку «Формулы» на ленте.

  2. В группе «Определенные имена» щелкните инструмент «Диспетчер имен». Excel отображает диалоговое окно Диспетчер имен.

  3. Щелкните кнопку New. Excel отображает диалоговое окно «Новое имя». (См. Рис. 1.)

  4. В поле Имя введите имя CurrentYear (обратите внимание, что это одно слово без пробелов).

  5. В поле «Ссылается на» введите следующую формулу:

  6. Нажмите кнопку ОК, чтобы завершить создание именованного диапазона. Новый диапазон должен появиться в диалоговом окне «Диспетчер имен».

  7. Снова нажмите кнопку «Новый». Excel (снова) отображает диалоговое окно «Новое имя».

  8. В поле «Имя» введите имя «Предыдущий год» (обратите внимание, что это снова одно слово без пробелов).

  9. В поле «Ссылается на» введите следующую формулу:

  10. Щелкните кнопку ОК. Новый диапазон должен появиться в диалоговом окне «Диспетчер имен».

  11. Нажмите кнопку «Создать» в третий раз. Excel отображает диалоговое окно «Новое имя».

  12. В поле Имя введите имя Даты.

  13. В поле «Ссылается на» введите следующую формулу:

  14. Нажмите кнопку «Закрыть», чтобы закрыть диалоговое окно «Диспетчер имен».

Определив диапазоны, вы можете создать диаграмму, используя эти диапазоны:

  1. Откройте вкладку Вставка на ленте.

  2. В группе «Диаграммы» выберите параметр «Столбец». Excel отображает раскрывающийся список.

  3. Щелкните один раз на формате Clustered Column. (Это первый вариант под заголовком двумерного столбца.) Excel создает пустой объект диаграммы на вашем листе.

  4. Щелкните правой кнопкой мыши объект диаграммы. Excel отображает контекстное меню.

  5. Выберите «Выбрать данные» в контекстном меню. Excel отобразит диалоговое окно «Выбор источника данных», которое должно быть полностью пустым, поскольку вы не добавляли в диаграмму диапазоны данных.

  6. В левой части диалогового окна под Легендами (Серии)

заголовок нажмите кнопку Добавить. Excel отображает диалоговое окно «Редактировать ряд». (См. Рис. 2.)

  1. В поле «Название серии» введите 2015.

  2. В поле «Значения серии» введите это: «2015»! Предыдущий год.

  3. Щелкните кнопку ОК. Теперь ряд данных появится в диалоговом окне «Выбор источника данных».

  4. Снова нажмите ту же кнопку «Добавить», которую вы нажимали на шаге 6. Excel снова отображает диалоговое окно «Редактировать ряд данных».

  5. В поле «Название серии» введите 2016.

  6. В поле «Значения серии» введите это: «2016»! CurrentYear.

  7. Щелкните кнопку ОК. Эта вторая серия данных теперь отображается в диалоговом окне «Выбор источника данных».

  8. В правой части диалогового окна под горизонтальной (Категория)

В заголовке Axis Labels нажмите кнопку Edit. Excel отображает диалоговое окно «Ярлыки осей». (См. Рис. 3.)

  1. В поле Axis Label Range введите это: «2016»! Dates.

  2. Щелкните кнопку ОК. Диалоговое окно «Выбор источника данных» должно быть полностью заполнено необходимой информацией. (См. Рис. 4.)

  3. Нажмите ОК, чтобы закрыть диалоговое окно «Выбор источника данных».

Ваша обновленная диаграмма, показывающая только даты до сегодняшней даты, теперь должна быть видна. При желании вы можете продолжить форматирование диаграммы.

(Например, вы, вероятно, захотите отформатировать даты на диаграмме, чтобы они не включали год.) Кроме того, диаграмма является динамической, так что, когда вы откроете книгу завтра, она будет отражать на один день больше, чем раньше. Cегодня.

Другой способ справиться с этим — пересмотреть способ хранения данных.

Вместо того, чтобы хранить все ваши показания осадков на отдельных листах (по годам), поместите их все на один лист. Поскольку Excel может обрабатывать более миллиона строк данных на листе, вы не столкнетесь с какими-либо практическими ограничениями. (Миллион строк представляет более 2700 лет.)

Теперь на другом листе вы можете использовать две формулы массива для расчета совокупного количества осадков за оба года на сегодняшний день. Следующая формула массива предоставит количество осадков за предыдущий год:

=SUM(Data!B2:B1000*IF(Data!A2:A1000>=DATE(YEAR(NOW())-1,1,1),IF(Data!A2:A1000<=EDATE(NOW(),-12),1,0)))

Это предполагает, что исходные показания осадков находятся на листе с именем «Данные» и не превышают 1000 строк. (При необходимости вы можете изменить любой из них.) Чтобы получить текущее количество осадков за этот год, вы можете использовать следующую формулу массива:

=SUM(Data!B2:B1000*IF(Data!A2:A1000>=DATE(YEAR(NOW()),1,1),IF(Data!A2:A1000<=NOW(),1,0)))

Помните: это обе формулы массива, поэтому их следует вводить с помощью Ctrl + Shift + Enter. Единственное значение, возвращаемое каждой формулой, представляет совокупное количество осадков за каждый год на сегодняшний день. Затем эти два значения можно использовать в любой диаграмме.

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (13427) применим к Microsoft Excel 2007, 2010, 2013 и 2016.