Слишком длинная формула ряда в диаграмме excel

Обновлено: 07.07.2024

После создания диаграммы может потребоваться добавить в нее еще один ряд данных. Ряд данных — это строка или столбец чисел, которые вводятся на листе и отображаются на диаграмме, например список значений прибыли за квартал.

Диаграммы в Office всегда связаны с листом Excel, даже если вы создали диаграмму в другой программе, например в Word. Если диаграмма и данные, использованные при ее создании ("исходные данные"), расположены на одном листе, вы можете быстро перетащить любые новые данные на лист, чтобы добавить их в диаграмму. Если диаграмма расположена на отдельном листе, вам нужно будет использовать диалоговое окно Выбор источника данных для добавления ряда данных.

Примечание: Если вы ищете сведения о добавлении или изменении условных обозначений диаграммы, см. статью Добавление легенды на диаграмму.

Добавление ряда данных в диаграмму, расположенную на том же листе

Введите новый ряд данных, который нужно добавить, в ячейки на листе, расположенные рядом с исходными данными диаграммы или под ними.

В этом примере на диаграмме представлены данные о продажах за каждый квартал 2013 и 2014 гг., и мы добавили на лист новый ряд данных за 2015 г. Обратите внимание на то, что на диаграмме еще нет ряда данных за 2015 г.

Щелкните в любом месте диаграммы.

Отображаемые исходные данные выделяются на листе, и появляются маркеры изменения размера.

Как видно, ряд данных за 2015 г. не выбран.

Перетащите маркеры изменения размера на листе, чтобы включить новые данные.

Диаграмма обновится автоматически и отобразит новый добавленный ряд данных.

Примечания: Если вы просто хотите показать или скрыть отдельные ряды данных на диаграмме (не меняя данных), см. следующие статьи:

Добавление ряда данных в диаграмму на отдельном листе

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

Щелкните лист, содержащий диаграмму.

Щелкните диаграмму правой кнопкой мыши и выберите пункт Выбор данных.

На листе, содержащем исходные данные диаграммы, откроется диалоговое окно Выбор источника данных.

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

Новый ряд данных появится в разделе Элементы легенды (ряды) диалогового окна Выбор источника данных.

Чтобы закрыть диалоговое окно и вернуться на лист диаграммы, нажмите кнопку ОК.

См. также

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

Введите новый ряд данных, который нужно добавить, в ячейки на листе, смежные с исходными данными диаграммы, или под ними.

Выбранные исходные данные отобразятся на листе с маркерами изменения размера.

Перетащите маркеры изменения размера на листе, чтобы включить новые данные.

Диаграмма обновится автоматически и отобразит новый добавленный ряд данных.

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

Добавление ряда данных в диаграмму на листе диаграммы

Введите новые данные и подписи, которые нужно добавить, в ячейки на листе, смежные с исходными данными диаграммы, или под ними.

Щелкните лист диаграммы (отдельный лист, содержащий только диаграмму, которую вы хотите обновить).

На вкладке "Конструктор диаграмм" щелкните Выбрать данные.

На листе, содержащем исходные данные диаграммы, откроется диалоговое окно Выбор источника данных.

При открытом диалоговом окне щелкните лист и выделите все данные для диаграммы, включая новые ряды данных.

Новые ряды данных отобразятся в поле Элементы легенды (ряды).

Чтобы закрыть диалоговое окно и вернуться на лист диаграммы, нажмите кнопку ОК.

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

Вот, например, данные по курсам валют за несколько месяцев:

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

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

Выглядеть это может примерно так:

Нравится? Тогда поехали.

Шаг 1. Создаем дополнительную таблицу для диаграммы

В большинстве случаев для реализации интерактивности диаграммы применяется простой, но мощный прием – диаграмма строится не по исходной, а по отдельной, специально созданной таблице с формулами, которая отображает только нужные данные. В нашем случае, в эту дополнительную таблицу будут переноситься исходные данные только по тем валютам, которые пользователь выбрал с помощью флажков:

В Excel 2007/2010 к созданным диапазонам можно применить команду Форматировать как таблицу ( Format as Table) с вкладки Главная ( Home) :

Это даст нам следующие преимущества:

Любые формулы в таких таблицах автоматически транслируются на весь столбец – не надо «тянуть» их вручную до конца таблицы
При дописывании к таблице новых строк в будущем (новых дат и курсов) – размеры таблицы увеличиваются автоматически, включая корректировку диапазонов в диаграммах, ссылках на эту таблицу в других формулах и т.д.
Таблица быстро получает красивое форматирование (чересстрочную заливку и т.д.)
Каждая таблица получает собственное имя (в нашем случае – Таблица1 и Таблица2), которое можно затем использовать в формулах.

Подробнее про преимущества использования подобных Таблиц можно почитать тут.

Шаг 2. Добавляем флажки (checkboxes) для валют

В Excel 2007/2010 для этого необходимо отобразить вкладку Разработчик ( Developer) , а в Excel 2003 и более старших версиях – панель инструментов Формы ( Forms) . Для этого:

В Excel 2003: выберите в меню Вид – Панели инструментов – Формы (View –Toolbars –Forms)
В Excel 2007: нажать кнопку Офис – ПараметрыExcel – Отобразить вкладку Разработчик на ленте (OfficeButton –Exceloptions –ShowDeveloperTabintheRibbon)
В Excel 2010: Файл – Параметры – Настройка ленты – включить флаг Разрабочик (File –Options –CustomizeRibbon –Developer)

На появившейся панели инструментов или вкладке Разработчик ( Developer) в раскрывающемся списке Вставить ( Insert) выбираем инструмент Флажок ( Checkbox) и рисуем два флажка-галочки для включения-выключения каждой из валют:

Текст флажков можно поменять, щелкнув по ним правой кнопкой мыши и выбрав команду Изменить текст ( Edit text) .

Теперь привяжем наши флажки к любым ячейкам для определения того, включен флажок или нет (в нашем примере это две желтых ячейки в верхней части дополнительной таблицы). Для этого щелкните правой кнопкой мыши по очереди по каждому добавленному флажку и выберите команду Формат объекта ( Format Control) , а затем в открывшемся окне задайте Связь с ячейкой ( Cell link) .

Шаг 3. Транслируем данные в дополнительную таблицу

Теперь заполним дополнительную таблицу формулой, которая будет транслировать исходные данные из основной таблицы, если соответствующий флажок валюты включен и связанная ячейка содержит слово ИСТИНА (TRUE):

Заметьте, что при использовании команды Форматировать как таблицу ( Format as Table) на первом шаге, формула имеет использует имя таблицы и название колонки. В случае обычного диапазона, формула будет более привычного вида:

Обратите внимание на частичное закрепление ссылки на желтую ячейку (F$1), т.к. она должна смещаться вправо, но не должна – вниз, при копировании формулы на весь диапазон.

Шаг 4. Создаем полосы прокрутки для оси времени и масштабирования

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

Полосу прокрутки ( Scroll bar) берем там же, где и флажки – на панели инструментов Формы ( Forms) или на вкладке Разработчик ( Developer) :

Рисуем на листе в любом подходящем месте одну за другой две полосы – для сдвига по времени и масштаба:

Каждую полосу прокрутки надо связать со своей ячейкой (синяя и зеленая ячейки на рисунке), куда будет выводиться числовое значение положения ползунка. Его мы потом будем использовать для определения масштаба и сдвига. Для этого щелкните правой кнопкой мыши по нарисованной полосе и выберите в контекстном меню команду Формат объекта ( Format control) . В открывшемся окне можно задать связанную ячейку и минимум-максимум, в пределах которых будет гулять ползунок:

Таким образом, после выполнения всего вышеизложенного, у вас должно быть две полосы прокрутки, при перемещении ползунков по которым значения в связанных ячейках должны меняться в интервале от 1 до 307.

Шаг 5. Создаем динамический именованный диапазон

Чтобы отображать на графике данные только за определенный интервал времени, создадим именованный диапазон, который будет ссылаться только на нужные ячейки в дополнительной таблице. Этот диапазон будет характеризоваться двумя параметрами:

Отступом от начала таблицы вниз на заданное количество строк, т.е. отступом по временной шкале прошлое-будущее (синяя ячейка)
Количеством ячеек по высоте, т.е. масштабом (зеленая ячейка)

Этот именованный диапазон мы позже будем использовать как исходные данные для построения диаграммы.

Для создания такого диапазона будем использовать функцию СМЕЩ ( OFFSET) из категории Ссылки и массивы ( Lookup and Reference) - эта функция умеет создавать ссылку на диапазон заданного размера в заданном месте листа и имеет следующие аргументы:

В качестве точки отсчета берется некая стартовая ячейка, затем задается смещение относительно нее на заданное количество строк вниз и столбцов вправо. Последние два аргумента этой функции – высота и ширина нужного нам диапазона. Так, например, если бы мы хотели иметь ссылку на диапазон данных с курсами за 5 дней, начиная с 4 января, то можно было бы использовать нашу функцию СМЕЩ со следующими аргументами:

Хитрость в том, что константы в этой формуле можно заменить на ссылки на ячейки с переменным содержимым – в нашем случае, на синюю и зеленую ячейки. Сделать это можно, создав динамический именованный диапазон с функцией СМЕЩ ( OFFSET) . Для этого:

В Excel 2007/2010 нажмите кнопку Диспетчер имен (NameManager) на вкладке Формулы (Formulas)
В Excel 2003 и старше – выберите в меню Вставка– Имя– Присвоить(Insert – Name – Define)

Для создания нового именованного диапазона нужно нажать кнопку Создать ( Create) и ввести имя диапазона и ссылку на ячейки в открывшемся окне.

Сначала создадим два простых статических именованных диапазона с именами, например, Shift и Zoom, которые будут ссылаться на синюю и зеленую ячейки соответственно:

Теперь чуть сложнее – создадим диапазон с именем Euros, который будет ссылаться с помощью функции СМЕЩ ( OFFSET) на данные по курсам евро за выбранный отрезок времени, используя только что созданные до этого диапазоны Shift и Zoom и ячейку E3 в качестве точки отсчета:

Обратите внимание, что перед именем диапазона используется имя текущего листа – это сужает круг действия именованного диапазона, т.е. делает его доступным в пределах текущего листа, а не всей книги. Это необходимо нам для построения диаграммы в будущем. В новых версиях Excel для создания локального имени листа можно использовать выпадающий список Область.

Аналогичным образом создается именованный диапазон Dollars для данных по курсу доллара:

И завершает картину диапазон Labels, указывающий на подписи к оси Х, т.е. даты для выбранного отрезка:

Общая получившаяся картина должна быть примерно следующей:

Шаг 6. Строим диаграмму

Выделим несколько строк в верхней части вспомогательной таблицы, например диапазон E3:G10 и построим по нему диаграмму типа График ( Line) . Для этого в Excel 2007/2010 нужно перейти на вкладку Вставка ( Insert) и в группе Диаграмма ( Chart) выбрать тип График ( Line) , а в более старших версиях выбрать в меню Вставка – Диаграмма ( Insert – Chart) . Если выделить одну из линий на созданной диаграмме, то в строке формул будет видна функция РЯД ( SERIES) , обслуживающая выделенный ряд данных:

Эта функция задает диапазоны данных и подписей для выделенного ряда диаграммы. Наша задача – подменить статические диапазоны в ее аргументах на динамические, созданные нами ранее. Это можно сделать прямо в строке формул, изменив

=РЯД(Лист1!$F$3;Лист1! $E$4:$E$10 ;Лист1! $F$4:$F$10 ;1)

=РЯД(Лист1!$F$3;Лист1! Labels ;Лист1! Euros ;1)

Выполнив эту процедуру последовательно для рядов данных доллара и евро, мы получим то, к чему стремились – диаграмма будет строиться по динамическим диапазонам Dollars и Euros, а подписи к оси Х будут браться из динамического же диапазона Labels. При изменении положения ползунков будут меняться диапазоны и, как следствие, диаграмма. При включении-выключении флажков – отображаться только те валюты, которые нам нужны.

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

После создания диаграммыряд данных можно изменить двумя способами:

В диалоговом окне Выбор источника данных можно изменить данные ряда или изменить их ранг на диаграмме.

отобразить или скрыть данные на диаграмме с помощью фильтров;

Изменение ряда

Щелкните диаграмму правой кнопкой мыши и выберите выбрать выбрать данные.

В поле Элементы легенды (ряды) щелкните ряд, который нужно изменить.

Вносимые изменения могут привести к разрыву связей с исходными данными на листе.

Чтобы изменить ранг ряда, выберем его и нажмите кнопку Вверх или Переместить вниз .

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

Если вы хотите переименовать ряд данных, см. переименовывать ряд данных.

Фильтрация данных на диаграмме

Начнем с фильтров диаграмм.

Щелкните в любом месте диаграммы.

На вкладке Значения выберите ряды или категории, которые нужно отобразить или скрыть.

Если нужно изменить или переупорядочить данные ряда, выберите команду Выбор данных и выполните действия 2-4 из следующего раздела.

Изменение ряда

На ленте щелкните Конструктор диаграмм и выберите выбрать выбрать данные.

При этом будет выбран диапазон данных диаграммы и отобразиться диалоговое окно Выбор источника данных.

Чтобы изменить ряд легенды, в поле Элементы легенды (ряды) выберите ряд, который нужно изменить. Затем отредактируем поля Значения "Имя" и "Y", чтобы внести изменения.

Примечание: Вносимые изменения могут привести к разрыву связей с исходными данными на листе.

Чтобы изменить ранг рядов легенды, в поле Элементы легенды (ряды) щелкните ряд, который вы хотите изменить, и выберите стрелки или .

Фильтрация данных на диаграмме

В Word и PowerPoint: выберите диаграмму, а затем на вкладке Конструктор диаграмм нажмите кнопку Изменить данные в Excel.

В Excel выберите название категории, а затем на вкладке Главная выберите Сортировка и фильтр > Фильтр.

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

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

Тем не менее вы можете задать следующие параметры оси:

Интервал между делениями и подписями.

Порядок отображения категорий.

Тип оси (даты или текст).

Точка пересечения горизонтальной и вертикальной осей.

Примечание: Следующая процедура относится к Office 2013 и более поздним версиям. Ищете по шагам для Office 2010?

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

Щелкните в любом месте диаграммы.

Откроется вкладка Работа с диаграммами с дополнительными вкладками Конструктор и Формат.

На вкладке "Формат" в группе "Текущий выделение" щелкните стрелку в поле вверху и выберите горизонтальную ось (категорий).

На вкладке Формат в группе Текущий фрагмент нажмите кнопку Формат выделенного фрагмента.

В области "Формат оси" сделайте следующее:

Важно: Эти параметры масштабирования доступны только в том случае, если выбрана ось категорий.

Чтобы изменить порядок категорий, раз проверьте параметры оси, а затем выберите их в обратном порядке.

Чтобы изменить тип оси на ось текста или ось дат, разведите параметры оси, а затем в области "Тип оси" выберите ось текста или ось дат. Текст и точки данных распределены на оси текста равномерно. На оси дат даты отображаются в хронологическом порядке через заданные интервалы (единицы измерения), такие как количество дней, месяцев или лет, даже если даты на листе представлены не в последовательном порядке или с использованием разных единиц измерения.

Примечание: Заданное по умолчанию значение Автоматически выбирать на основе данных определяет тип оси, который наилучшим образом подходит для выбранного типа данных.

Чтобы изменить точку, в которой вертикальная ось (значений) пересекает горизонтальную ось (категорий), разведите параметры оси, а затем в области "Вертикальная ось" выберите номер категории и введите нужное число в текстовом поле или выберите пункт "По максимальной категории", чтобы указать, что вертикальная ось (значений) пересекает горизонтальную ось (категорий) после последней категории на оси x.

Чтобы изменить интервал между делеаниями, разведите делеания,а затем введите нужное число в поле "Интервал между деленами".

Примечание: Это число определяет количество категорий, которые отображаются между делениями.

Чтобы изменить положение делений оси, разведите делений, а затем выберите нужные параметры в полях "Основной" и "Незначительный".

Чтобы изменить интервал между подписями осей, разведите подписи, а затем в поле "Интервал между подписями" выберите "Единица интервала" и введите нужное число в текстовое поле.

Совет: Введите 1, чтобы отображалась подпись для каждой категории, 2, чтобы отображалась подпись для каждой второй категории, 3, чтобы отображалась подпись для каждой третьей категории и т. д.

Чтобы изменить расположение подписей оси, разведите подписи, а затем в поле "Расстояние от оси" введите нужное число.

Совет: Чтобы расположить подписи ближе к оси, введите меньшее число. Чтобы увеличить расстояние между подписью и осью, введите большее число.

Изменение масштаба горизонтальной оси (категорий) на диаграмме (Office 2010)

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

Щелкните в любом месте диаграммы.

Откроется панель Работа с диаграммами с дополнительными вкладками Конструктор, Макет и Формат.

На вкладке Формат в группе Текущий фрагмент щелкните стрелку рядом с полем Элементы диаграммы, затем щелкните Горизонтальная ось (ось категорий).

На вкладке Формат в группе Текущий фрагмент нажмите кнопку Формат выделенного фрагмента.

В диалоговом окне Формат оси в разделе Параметры оси выполните одно или несколько из указанных ниже действий.

Важно: Эти параметры масштабирования доступны только в том случае, если выбрана ось категорий.

Чтобы изменить интервал между делениями, в поле Интервал между делениями введите нужное число.

Примечание: Это число определяет количество категорий, которые отображаются между делениями.

Чтобы изменить интервал между подписями оси, в разделе Интервал между подписями выберите Единица измерения интервала, а затем в текстовом поле введите нужное число.

Чтобы изменить расположение подписей оси, в поле Расстояние до подписи от оси введите нужное число.

Чтобы изменить порядок категорий, установите флажок Обратный порядок категорий.

Чтобы изменить тип оси на ось текста или ось дат, в поле Тип оси выберите вариант Ось текста или Ось дат. Текст и точки данных распределены на оси текста равномерно. На оси дат даты отображаются в хронологическом порядке через заданные интервалы (единицы измерения), такие как количество дней, месяцев или лет, даже если даты на листе представлены не в последовательном порядке или с использованием разных единиц измерения.

Чтобы изменить положение делений и подписей оси, выберите нужные параметры в полях Основные, Промежуточные и Подписи осей.

Чтобы изменить точку пересечения вертикальной оси (значений) и горизонтальной оси (категорий), в разделе Вертикальная ось пересекает выберите параметр В категории с номером, а затем введите нужное число в текстовом поле или выберите параметр В наибольшей категории, чтобы указать, что вертикальная ось (значений) должна пересекать горизонтальную ось (категорий) за последней категорией оси X.

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

Масштабирование никак не влияет на перекрытие рядов или ширину зазора между ними. Чтобы изменить параметры перекрытия рядов или ширину зазора между ними, щелкните ряд данных правой кнопкой мыши и выберите пункт Формат ряда данных. В разделе Параметры ряда укажите нужные значения.

По умолчанию минимальное и максимальное значения масштаба для каждой оси на диаграмме рассчитываются автоматически. Однако масштаб можно настроить в соответствии со своими потребностями. Например, если все точки данных в таблице данных находятся в интервале от 60 до 90, для оси значений (у) можно задать диапазон от 50 до 100, а не от 0 до 100. Если ось значений охватывает очень широкий диапазон, ее также можно изменить на логарифмическую шкалу.

Этот шаг относится только к Word для Mac: в меню "Вид" выберите пункт "Разметка печати".

На вкладке "Формат" в списке выберите горизонтальную ось (категорий) и нажмите кнопку "Формат области".

В области "Формат оси" сделайте следующее:

Важно: Эти параметры масштабирования доступны только в том случае, если выбрана ось категорий.

Чтобы изменить порядок категорий, раз проверьте параметры оси, а затем выберите их в обратном порядке.

Примечание: Это число определяет количество категорий, которые отображаются между делениями.

Чтобы расположить метку в нужномместе, разметите список "Подписи" и выберите вариант в списке.

Читайте также: