Динамические диаграммы в MS EXCEL. Часть4: Выборка данных из определенного диапазона

Часто на диаграмме необходимо отобразить не все данные из исходной таблицы, а лишь только часть, например, значения из 10 последних строк. Причем диаграмма должна динамически изменяться в зависимости от того, какое количество значений пользователь хочет отобразить. Для построения такой диаграммы будем использовать функцию СМЕЩ().

Пусть имеется таблица платежей (см. файл примера):

Построим диаграмму, отображающую только первые несколько записей:

СОВЕТ: Для начинающих пользователей EXCEL советуем прочитать статью Основы построения диаграмм в MS EXCEL, в которой рассказывается о базовых настройках диаграмм, а также статью об основных типах диаграмм.

Количество записей для отображения будет задаваться пользователем с помощью элемента управления Счетчик, связанный с ячейкой F1.

Сформируем набор записей для отображения на диаграмме с помощью функции СМЕЩ() и создадим 2 соответствующих Динамических диапазона: один для дат, другой для сумм платежей.

Функция СМЕЩ() позволяет формировать необходимый диапазон в зависимости от значений введенных пользователем, например, с помощью элемента управления Счетчик.

Формула =СМЕЩ($B$4;;;$F$1) сформирует диапазон дат (значение в ячейке B4 задает левый верхний угол диапазона, в F1 – его высоту), а формула =СМЕЩ($C$4;;;$F$1) – диапазон, содержащий суммы платежей.

Через меню Формулы/ Определенные имена/ Присвоить имя создадим именованный диапазон Даты1:

Аналогичным образом создадим именованный диапазон Платежи1.

Теперь создадим диаграмму:

  • Выделите любую ячейку таблицы;
  • В меню Вставка/ Диаграммы/ Гистрограммы выберите Гистограмма с группировкой;
  • Через меню Работа с диаграммами/ Конструктор/ Данные/ Выбрать данные измените значения ряда на ='Динамические_диаграммы(СМЕЩ).xlsx'!Платежи1, где Динамические_диаграммы(СМЕЩ).xlsx – имя файла, содержащего именованный диапазон. В качестве источника значений для ряда недостаточно указать Имя диапазона Платежи1. Перед именем нужно указать либо название листа (если диапазон имени Лист) или имя файла (если диапазон имени Книга));
  • Аналогично, в качестве источника данных для подписи горизонтальной оси введите ссылку на именованный диапазон Даты1: ='Динамические_диаграммы(СМЕЩ).xlsx'!Даты1

Отображаем на диаграмме несколько последних значений
Для создания диаграммы, отображающей несколько последних значений, потребуется изменить формулу именованного диапазона:
=СМЕЩ($B$4;$J$1-$F$1;;$F$1)

В ячейке J1 содержится число значений в столбце Сумма (предполагается, что в столбец заполнен без пропусков). Если таблица может иметь незаполненные строки, то для определения последней заполненной строки можно воспользоваться идеями из статьи Последняя заполненная ячейка.

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

С другими динамическими диаграммами можно познакомиться в статье Динамические диаграммы. Общие замечания.

Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 5 (1 оценка)

Комментарии

demtsyurina (не проверено)

Скажите,а что если,когда я вставляю ='Книга1.xlsx'!Платежи,то выдает ошибку о неверности ссылки. "Недопустимая ссылка.Ссылка должна указывать на отрытый лист"

Creator

Перед вставкой ссылки, сначала сохраните файл Книга1.xlsx на жесткий диск.