Лекция: Ссылки в формулах и построение графиков

Адреса ячеек, формулы и автозаполнение

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

Каждый лист представляет из себя сетку, состоящую из пересечения столбцов (с именами A,B,C,D...) и строк (с номерами 1,2,3). Ячейки клеток имеют адреса, сформированные из имени столбца и номера строки (например, самая левая верхняя ячейка имеет адрес A1).

Давайте рассмотрим пример построения графика параболы: \(y = x^2\). Для этого нам понадобятся данные для \(x\) и данные для \(y\).

В столбец A поместим значения \(x\) в диапазоне от -2 до 2 с шагом 0,1, а в столбце B проведем рассчет значений \(y\). В ячейки A1 и B1 запишем текст "x" и "y", затем в ячейках A2 и A3 запишем начальные значения \(x\) "-2", "-1,9". После этого выделим ячейки A2 и A3.

Обратите внимание на маленький черный квадрат в правом нижнем углу рамки выделения. Это - маркер заполнения. Если за него тянуть вниз-вверх, вправо-влево, то Excel пытается продолжить логику выделенных ячеек. После "протягивания" за маркер заполнения у вас должен получиться заполненный диапазон от -2 до 2.

Для проведения рассчетов в Excel необходимо в ячейке поставить знак "=". После этого указать формулу для расчета. В ячейке B2 рассчитаем значение \(y\) для \(x=2\). Для этого запишем "=A2*A2".

В формуле мы указали A2 - это адрес ячейки из которой нужно взять значение для рассчета. Если мы "протянем" эту формулу вниз, то Excel сам будет изменять формулу: в ячейку B3 он подставит "=A3*A3", в B4 - "=A4*A4" и так далее.

Построение графиков

Для построения графика параболы, воспользуемся инструментом "Диаграмма".

Нам нужно выделить диапазон для построения диаграммы. В диапазон должны войти ячейки с данными и заголовки. Если вы все сделали правильно, то это будут ячейки от A1 до B42. При вставке диаграммы Excel запускает "Мастер диаграмм".

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

Абсолютные адреса ячеек

Мы построили простую параболу по формуле \(y=x^2\). Полная формула параболы \(y=a x^2 + b x + c\).

Кроме \(x\) нам понадобятся переменные \(a\), \(b\), \(c\). Заполним ячейки C1, D1, E1 названиями "a", "b", "c". В ячейках C2, D2, E2 запишем значения переменных, например "2", "1", "-5".

Формулу в ячейке B2 тоже придется исправить. Можно догадаться, что там должно быть "=C2*A2*A2+D2*A2+E2".

Если теперь "протянуть" ячейку вниз, то Excel будет менять все адреса ячеек. Например, в ячейку B12 он поставит "=C12*A12*A12+D12*A12+E12".

Так как в ячейках С12, D12, E12 ничего нет, то Excel подставляет "0". Нам нужно сделать так, чтобы Excel понял, что адрес A2 надо менять при "протягивании", а С2, D2, E2 - нельзя. Для обозначения адресов, которые при "протягивании" не должны меняться в Excel нужно поставить знак "$" перед именем столбца и номером строки. Такие адреса называются абсолютными. Т.е. В формулу вместо "C2" нужно записать "$C$2". Если поставить курсор на адрес внутри формулы и нажать на клавиатуре кнопку "F4", то excel сам поставит знаки "$", т.е. преобразует относительный адрес в абсолютный.

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

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

Задание для самостоятельного выполнения

На основе полученных сведений, постройте график функции \(y=a\cdot sin(b x-c) +d x\). Чтобы понять правильный у вас результат или нет, можете подставить значения \(a, b, c, d\) "2", "1", "-5"," 1". Должен получиться подобный график: