Существует очень удобный способ построения линейных и других уравнений с использованием определенных имен. Первоначальная настройка немного сложна, но конечный результат является гибким и мощным.
Начните с создания диаграммы Scatter with Smooth Lines с использованием нескольких фиктивных чисел на листе, таких же простых, как число 1 в A1, 2 в A2, 3 в B1 и 4 в B2.
Создайте два определенных имени для переменных x и y с помощью Name Manager
на вкладке Formulas
на ленте. Для имени "x" (например) в поле ввода "Относится к" диспетчера имен создайте набор констант массива, введя формулу «= {1,2,3,4}» (введите фигурные скобки , но не двойные кавычки). Сделайте то же самое для имени "у". (Константы массива - это фиктивные суммы, которые впоследствии будут заменены формулами.
Измените ряд в таблице так, чтобы он ссылался на определенные имена для уравнения. Вероятно, самый простой способ сделать это - выбрать линию на диаграмме, а затем заменить ссылки на диапазон в уравнении ряда в строке формул именами. При вводе имен включите ссылку на рабочую книгу и лист в форму, показанную ниже; в противном случае Excel не примет вашу запись.
Before: =SERIES(,Sheet1!$A$1:$B$1,Sheet1!$A$2:$B$2,1)
You Enter: =SERIES(,[book1]Sheet1!x,[book1]Sheet1]!y,1)
After: =SERIES(,book1!x,book1!y)
На этом этапе ваш лист может выглядеть примерно так: исходные данные, которые вы использовали для создания диаграммы, и основные входные данные. (Исходные данные для диаграммы теперь не используются и могут быть удалены.)
Следующим шагом является переопределение имен x и y в менеджере имен. Идея состоит в том, чтобы заменить диапазоны, на которые изначально указывали имена, выражениями, которые будут производить набор значений x, определенных вами на листе, и значений y, которые получаются в результате применения уравнения к этим значениям x.
Вернитесь в менеджер имен, чтобы редактировать имена x и y.
Для имени x замените константы массива ("= {1,2,3,4}") приведенной ниже формулой. Комбинация функций ROW и OFFSET создает массив {1, 2 ... 20} (для этого примера), который затем смещается в начальное значение -1 и умножается на приращение, на которое увеличивается каждое значение x. Конечный результат представляет собой массив из 20 одинаково расположенных точек, начинающихся с -1 и заканчивающихся на 1.
=Sheet1!$B$5+(ROW(OFFSET(Sheet1!$B$1,0,0,Sheet1!$B$7,1))-1)*(Sheet1!$B$6-Sheet1!$B$5)/(Sheet1!$B$7-1)
Это формула массива, но для ее ввода не требуется никакого специального метода. Excel обрабатывает как формулу массива любое выражение, введенное для определенного имени.
Для имени y замените константы массива формулой
=EVALUATE($B$4)
После выполнения этих шагов график готов для любого окончательного форматирования, которое вам нужно.
Вот еще один пример диаграммы, где единственным изменением было уравнение для y, введенное в лист.
Смотрите эту ссылку для более подробного объяснения этой техники. Рабочую книгу с примером диаграммы можно найти здесь.