Как отобразить формулу в Excel из графика

Обновлено: 20.11.2024

Ряд данных – это просто группа связанных данных, представляющая строку или столбец на листе.
Когда вы выбираете конкретный ряд данных на диаграмме, соответствующая формула ряда появляется в строке формул.

При изменении исходных данных вы увидите, что формула ряда изменяется автоматически.
При выборе ряда формула ряда отображается в строке формул вверху. Имя ряда отображается в «поле имени» слева от строки формул.
Excel использует формулу или функцию ряда для определения ряда данных для диаграммы. Существует одна формула ряда для каждого ряда данных.
Хотя это напоминает типичную формулу рабочего листа, есть несколько важных отличий.
1) Функцию РЯД нельзя использовать непосредственно из другой ячейки.
2 ) Эта формула не может содержать какие-либо функции рабочего листа.
Однако можно редактировать эту формулу и вручную изменять аргументы.

Поверхностные диаграммы не имеют формулы ряда?
типы диаграмм - 83,84,85,86

[=SERIES(Имя, X-значения, Y-значения, PlotOrder, Размер пузырьков) ]

Данные, используемые в каждой серии на диаграмме, определяются формулой SERIES. Нажмите Enter, чтобы применить любые изменения.
Имя — (необязательно) — это название серии, которое отображается в легенде. Если диаграмма имеет только одну серию, это имя используется в качестве заголовка. Это может быть пустое место, текстовая строка в двойных кавычках, ссылка на диапазон рабочего листа или именованный диапазон. Если оставить это поле пустым, Excel предоставит имя по умолчанию (серия 1, 2, 3 и т. д.)
X-значения — (необязательно) — это метки, которые используются на осях категорий. Если оставить это поле пустым, используются последовательные целые числа 1,2,3 . Это может быть пустое поле, буквенный массив числовых значений или текстовых меток, заключенных в фигурные скобки, ссылка на диапазон рабочего листа или именованный диапазон. Также возможно иметь прерывистый эталонный диапазон. Отдельные диапазоны должны быть заключены в скобки и разделены запятыми (например, ??)
Y-значения — это значения, которые вы хотите отобразить. Это может быть пустое поле, буквенный массив числовых значений, заключенный в фигурные скобки, ссылка на диапазон рабочего листа или именованный диапазон. Также возможно иметь прерывистый эталонный диапазон. Отдельные диапазоны должны быть заключены в скобки и разделены запятыми (например, ??).
PlotOrder — это порядок построения ряда данных. На вкладке «Серии» отображаются серии в том порядке, в котором они нанесены на график. Это также порядок, в котором названия серий будут появляться в легенде. Если есть только одна серия, то она опускается. Это должно быть целое число между 1 и номером ряда на диаграмме. Если вы вводите ноль, используется 1. Если вы введете число больше, чем количество серий, то будет использовано общее количество серий. Это не может быть ссылкой на ячейку.
Размеры пузырьков — (необязательно) — это значения размеров пузырьков. Это может быть пустое поле, буквенный массив числовых значений, заключенный в фигурные скобки, ссылка на диапазон рабочего листа или именованный диапазон. Также возможно иметь прерывистый эталонный диапазон. Отдельные диапазоны должны быть заключены в скобки и разделены запятыми (например, ??).

Правила формул серии и факты

Ссылки на диапазоны в формуле SERIES всегда являются абсолютными и всегда содержат имя рабочего листа. Если вы определили именованные диапазоны, формула ряда может содержать именованный диапазон вместо диапазона.
Любые используемые ссылки на диапазоны всегда имеют префикс имени рабочего листа.
Все используемые ссылки на диапазоны всегда являются абсолютными ссылками ( в отличие от относительной).
Если вы случайно удалите имя рабочего листа или используете относительную ссылку, они будут изменены автоматически.
Каждый ряд данных имеет соответствующую формулу ряда, и количество аргументов будет зависеть от тип диаграммы.

Примеры корректной формулы ряда

=SERIES(Имя, X-значения, Y-значения, PlotOrder, Размер кружка)
=SERIES(Лист1!$C$2, Лист1!$B$3:$B$9, Лист1!$C$3: $C$9, 1)
=СЕРИЯ("Север", , , 1)
=СЕРИЯ(Лист1!$C$2, , Лист1!$C$3:$C$9, 2)
=SERIES(Лист1!$C$2, Лист1!$B$3:$B$9, Лист1!$C$3:$C$9, 3, Лист1!$D$3:$D$9)
=СЕРИЯ(, (Лист1!$B$3:$B$9, Лист1!$B$20:$B$30), (Лист1!$C$3:$C$9, Лист1!$C$20:$C$30), 4)

Изменение аргументов

Вы можете изменить любой из аргументов формулы ряда четырьмя способами, которыми вы можете изменить имя ряда ряда данных:
1) перезаписать ячейки, на которые ссылается аргумент.
2) Использование автоматических дальномеров для перемещения исходных данных.
3) Использование (Диаграмма > Исходные данные) (вкладка "Серии").
4) Редактирование формулы серии.
Единственный способ изменить порядок рядов данных заключается в использовании (Диаграмма > Исходные данные) (вкладка "Серии") или непосредственном редактировании формулы ряда.

Важно

Обычная практика Excel предполагает, что все ряды имеют одни и те же значения X в первом столбце или строке и что каждый последующий столбец или строка содержат данные Y для отдельного ряда.
Поверхностные диаграммы этого не делают. имеют формулы ряда.
При создании диаграммы, в которой используется именованный диапазон, Excel не заменяет имя в формуле ряда автоматически.
Если вы удаляете именованный диапазон, который используется в ряду диаграммы, недопустимое имя останется, и диаграмма не будет отображаться должным образом.
Круговые диаграммы могут иметь только один ряд, если только .
Все различные части формулы ряда (кроме порядка построения) можно связать с рабочим листом (в любой рабочей книге).
Максимальное количество точек данных на обычной двумерной диаграмме — 32 000.
Для пузырьковых диаграмм требуется три набора значений, представляющих значение x, значение y и размер пузырьков.
Вы можете отображать до 255 различных рядов данных на диаграмме (кроме круговых диаграмм).
Попробуйте чтобы избежать слишком большого количества рядов данных на одной диаграмме.
У вас может быть объект диаграммы без ряда, хотя я не вижу никакой точки
Существует прямая связь между диаграммой и рабочим листом, содержащим данные. Базовые данные на рабочем листе можно изменить, перетащив отдельную точку диаграммы в новое положение.
Важно помнить, что любой из параметров в формуле SERIES может быть константой, литералом или ссылкой. к диапазону рабочего листа или именованному диапазону.

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

Рисунок 1. Уравнение графика

Сведение данных в таблицу

Рисунок 2. Таблица данных

Затем мы выделим всю нашу таблицу и нажмем «вставить», затем выберем значок «точечная диаграмма», чтобы отобразить график.

Рисунок 3. Точечная диаграмма

Рисунок 4. Графическое представление данных

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

Рисунок 5. Отредактированные параметры графика

Теперь мы добавим уравнение диаграммы, щелкнув правой кнопкой мыши любую точку на диаграмме, выбрав «добавить линию тренда», затем прокрутив вниз и, наконец, выбрав «Отобразить уравнение на диаграмме».

Рисунок 6: Уравнение линии

Мгновенное подключение к эксперту через нашу службу Excelchat

В большинстве случаев проблема, которую вам нужно решить, будет более сложной, чем простое применение формулы или функции. Если вы хотите сэкономить часы исследований и разочарований, попробуйте наш онлайн-сервис Excelchat! Наши эксперты по Excel доступны круглосуточно и без выходных, чтобы ответить на любой вопрос, который у вас может возникнуть. Мы гарантируем подключение в течение 30 секунд и индивидуальное решение в течение 20 минут.

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

Показать серии диаграмм с флажками

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

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

Примечание. Это руководство основано на методе, которому я научился у Джона Пелтье, который создает утилиты Excel для создания диаграмм, которые экономят время.

Создать повторяющийся диапазон данных

В этом примере исходные данные для диаграммы Microsoft Excel находятся в ячейках B2:H5

Примечание. Ячейка B2 остается пустой

Вместо использования исходного диапазона данных для создания диаграммы формулы используются для создания повторяющегося диапазона, связанного с исходными данными.

Диапазон данных диаграммы, показанный ниже, находится в ячейках B8:H11

Как создать повторяющийся диапазон данных

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

Чтобы создать заголовки столбцов Месяц:

В ячейке C8 введите следующую формулу: =C2
Скопируйте формулу в ячейку H8.

Чтобы создать заголовки строк региона:

В ячейке B9 введите следующую формулу: =C3
Скопируйте формулу в ячейку B11.

Чтобы создать серию данных:

В ячейке C9 введите следующую формулу: =IF($A9=TRUE,C3,NA())
Скопируйте формулу в ячейку H9, а затем скопируйте ее в строку 11.

Добавьте флажки

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

Если флажок установлен, данные по этому региону появятся на диаграмме.

Чтобы добавить флажок, выполните следующие действия:

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

На рабочем листе щелкните ячейку A3, чтобы добавить флажок.
Удалите текст из заголовка флажка и сделайте его уже, чтобы он поместился в столбце А.

Скопируйте флажок и вставьте копии в ячейки A4 и A5.

Связать флажки с ячейками листа

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

Нажмите правой кнопкой мыши флажок в ячейке A3 и выберите "Управление форматом".
Нажмите на поле "Ссылка на ячейку", а затем на рабочем листе нажмите на ячейку A9, чтобы связать флажок с этой ячейкой.

Нажмите "ОК", чтобы закрыть окно "Управление форматом".
Свяжите оставшиеся флажки с соответствующими ячейками в диапазоне повторяющихся данных.
Нажмите на каждый из флажков, чтобы добавить галочку и добавить ИСТИНА в связанную ячейку.

Создать линейную диаграмму

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

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

Выберите данные и заголовки в повторяющемся диапазоне данных – ячейки B8:H11.
На ленте Excel перейдите на вкладку "Вставка".
В группе типов диаграмм нажмите команду "Вставить линейную диаграмму".
В списке линейных диаграмм выберите параметр "Двухмерная линия".

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

Формат графика

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

Чтобы удалить легенду диаграммы:

Нажмите на легенду диаграммы, чтобы выбрать ее.
На клавиатуре нажмите клавишу Delete.

Чтобы связать заголовок диаграммы с ячейкой:

Нажмите на заголовок диаграммы, чтобы выбрать ее.
Нажмите в строке формул и введите знак равенства (=)
Нажмите на ячейку B1, содержащую название листа.
Нажмите Enter, чтобы завершить формулу.

Чтобы добавить метки данных:

Установите флажки, чтобы показать все серии.
Нажмите на первую серию, чтобы выбрать ее.
Нажмите на последнюю точку в выбранной серии, чтобы выбрать точку данных.
Нажмите правой кнопкой мыши на выбранную точку данных и нажмите "Добавить метку данных", затем нажмите "Добавить метку данных".

Дважды нажмите на метку данных: при первом нажатии будут выбраны все метки данных для ряда, а при втором — только одна метка данных.
Нажмите правой кнопкой мыши выбранную метку данных и выберите "Форматировать метку данных".
На панели «Формат метки данных» в разделе «Параметры метки» установите флажок «Имя серии» и снимите флажок «Значения».

Повторите шаги 2–7 для остальных рядов данных.
СОВЕТ. Если метки данных расположены слишком близко к линиям, нажмите на область графика диаграммы и немного сузьте ее.

Протестируйте интерактивную линейную диаграмму

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

Значение в связанной ячейке изменится на ЛОЖЬ
Данные для этого ряда регионов исчезнут с диаграммы.

Показать серию диаграмм с крестиком

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

Позже снимите "X", чтобы удалить ряд с диаграммы.

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

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

В ячейке A9 введите следующую формулу: =A3<>""
Скопируйте формулу в ячейку A11.

Формула в ячейке A9 проверяет ячейку A3 (диапазон основных данных), чтобы убедиться, что она не равна пустой строке (не пуста)

Если ячейка A3 не пуста (если она содержит X или любой другой символ), результатом формулы в ячейке A9 будет TRUE
Если ячейка A3 пуста (не содержит символов), результатом формулы в ячейке A9 будет FALSE

Протестируйте интерактивную онлайн-диаграмму

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

Флажки недоступны в интерактивном онлайн-представлении, поэтому в этом примере в столбце A используются метки "X".

Введите X в столбце A, чтобы отобразить регион.
Очистите ячейку в столбце A, чтобы скрыть область.

Загрузить книгу

Чтобы увидеть обе версии настройки диаграммы, загрузите файл примера. Файл книги Excel заархивирован и имеет формат xlsx без макросов. Образец файла интерактивной диаграммы

поиск меню

Урок 22. Диаграммы

Введение

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

Понимание диаграмм

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

Нажимайте стрелки на слайд-шоу ниже, чтобы узнать больше о типах диаграмм в Excel.

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

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

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

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

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

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

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

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

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

Легенда

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

В этом примере легенда идентифицирует разных продавцов на диаграмме.

Название диаграммы

Заголовок должен четко описывать, что иллюстрирует диаграмма.

Вертикальная ось

Вертикальная ось (также известная как ось Y) — это вертикальная часть диаграммы.

Здесь вертикальная ось измеряет значение столбцов, поэтому ее также называют осью значений. В этом примере измеренным значением является чистый объем продаж каждого продавца.

Горизонтальная ось

Горизонтальная ось (также известная как ось x ) – это горизонтальная часть диаграммы.

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

Серии данных

Серия данных состоит из связанных точек данных на диаграмме.

В этом примере синие столбцы представляют чистые продажи Роберта Джонсона. Мы знаем, что его данные выделены синим цветом из-за легенды справа.

Читая ряд данных, мы видим, что Роберт был лучшим продавцом в 1 и 3 кварталах, а во 2 и 4 кварталах он был вторым по величине.

Чтобы вставить диаграмму:

Выберите ячейки для диаграммы, включая заголовки столбцов и метки строк. Эти ячейки будут исходными данными для диаграммы. В нашем примере мы выберем ячейки A1:F6.

Выбор ячеек A1:F6

Нажатие команды Столбчатая диаграмма

Выбор типа диаграммы

Вставленная диаграмма

Если вы не уверены, какой тип диаграммы использовать, команда "Рекомендуемые диаграммы" предложит несколько разных диаграмм на основе исходных данных.

Нажатие команды "Рекомендуемые диаграммы"

Макет и стиль диаграммы

После вставки диаграммы есть несколько вещей, которые вы можете изменить в способе отображения ваших данных. Макет и стиль диаграммы легко изменить на вкладке "Дизайн".

Редактирование текста-заполнителя заголовка диаграммы

Выбор макета диаграммы

Выбор нового стиля диаграммы

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

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

Другие параметры диаграммы

Существует множество других способов настройки и организации диаграмм. Например, Excel позволяет переупорядочивать данные диаграммы, изменять тип диаграммы и даже перемещать диаграмму в другое место в книге.

Чтобы переключить данные строки и столбца:

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

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

Выберите диаграмму, которую хотите изменить.
На вкладке "Дизайн" выберите команду "Переключить строку/столбец".

Нажатие команды "Переключить строки/столбцы"

Переключенные данные строки и столбца

Чтобы изменить тип диаграммы:

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

Нажатие команды "Изменить тип диаграммы"

Выбор нового типа диаграммы

Новый тип диаграммы

Чтобы переместить диаграмму:

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

Выберите диаграмму, которую хотите переместить.
Перейдите на вкладку "Дизайн", затем выберите команду "Переместить диаграмму".

Нажатие команды "Переместить диаграмму"

Перемещение диаграммы на новый лист

Диаграмма на отдельном листе

Поддержание диаграмм в актуальном состоянии

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

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

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

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