Динамические диаграммы в 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)

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

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

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