Копирование формул

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

 

Пример. Вычисление по формулам.

Вычислить значение функции F(x, y) по формуле F(x, y)=4x³ -3y² +6x для х=1 и у=2.

1 способ.

В ячейку А1 записать x, в ячейку B1 – y, в ячейку C1 – F(x, y).

В ячейку А2 записать значение 1, а в ячейку B2 значение 2. В ячейку С2 ввести вышеуказанную формулу, используя для операции возведение в степень символ ^ и для операции умножения символ *.

Адреса ячеек в формулу можно вводить с клавиатуры либо выбирая нужную ячейку мышкой.

	А	В	С
	x	y	F(x, y)
			=4*А2^3-3*B2^2+6*A2

 

После нажатия клавиши Enter в ячейке С2 получаем результат.

Для второго варианта присвоить ячейке А2 со значением х имя х,
а ячейке В2 со значением у имя у. Присвоить имя ячейке можно на вкладке Формула.

С дополнительным материалом по теме можно ознакомиться в литературе [1, 3, 4, 7].

 

Задание. Составить таблицу значений функции двух переменных F(x, y), в прямоугольной области [a, b]× [c, d], для аргументов x_i=a+ih_x, y_j=c+jh_y,

где i=0..N_x, j=0.. N_y, (h_x=(b-a)/N_x, h_y=(d-c)/N_y). Результат сохранить в книге.
N_x, N_y принять равными по 10 итераций.

 

Варианты заданий

№	F(x, y)	a	b	c	d	hx	hy
	xy+5, 6(x+y)					0, 1	0, 1
	xy-5, 6(x-y)					0, 2	0, 2
	xy+x2+y2					0, 1	0, 1
	x(x+y)					0, 1	0, 1
	(x+xy)+y					0, 2	0, 2
	(xy+x)+xy					0, 1	0, 1
	2, 5(x+y)					0, 1	0, 1
	y(x+y)+xy					0, 2	0, 2
	(x-y)+5xy					0, 1	0, 1
	y+(2, 5x+y)					0, 1	0, 1
	(xy+1, 4x)+xy					0, 2	0, 2
	5, 2 (x+y)					0, 1	0, 1
	xy(x+y)					0, 1	0, 1
	xy+(x+y)-4					0, 2	0, 2
	x(x+y)+y					0, 1	0, 1
	xy(x+y)					0, 1	0, 1
	(x+xy)+xy-2					0, 2	0, 2
	xy(x+y)+xy					0, 1	0, 1
	x(x-y)+5, 3y					0, 1	0, 1
	x+y(xy+1)					0, 2	0, 2
	y(x+1)+y					0, 1	0, 1
	y(x+y)+xy					0, 1	0, 1
	x+(x+y)					0, 2	0, 2
	xy+(xy+4, 7)					0, 1	0, 1
	(x+y)(x+3, 1)					0, 1	0, 1

 

Содержание отчета

Отчетом является файл Таблица значений_Фио_студента.xlsx, созданный в результате выполнения задания.

 

Технология выполнения работы

Заполнение файла Таблица значений_Фио_студента.xlsx по заданному варианту должно быть выполнено с использованием правил ввода информации в ячейки, автозаполнения и копирования формул.

 

Вопросы для защиты работы

1. Охарактеризовать основные элементы окна MS Excel.

2. Назвать типы данных, используемых в электронных таблицах.

3. Как выполняется Автозаполнение в электронных таблицах?

4. Описать абсолютные и относительные ссылки.

 

Лабораторная работа № 6
Электронные таблицы MS Excel: диаграммы, графики, условия, функции, макросы

 

Время выполнения – 4 часа.

 

Цель работы

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

 

Задачи лабораторной работы

После выполнения работы студент должен:

1) уметь работать с диаграммами;

2) правильно использовать оператор условия;

3) применять функции для расчетов в электронных таблицах;

4) освоить работы с макросами.

 

Перечень обеспечивающих средств

Для обеспечения выполнения работы необходимо иметь компьютер со следующим обеспечением: операционная система Windows и MS Office 2007 и выше.

Общие теоретические сведения

Анализ данных в электронных таблицах

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

Основные типы диаграмм: графики, гистограммы, круговые, лепестковые, точечные диаграммы.

Построение диаграмм и графиков можно выполнить с помощью мастера диаграмм, пиктограммы диаграмм можно видеть на вкладке Вставка (рис. 2).

Рис. 2. Область Диаграммы на Ленте MS Excel 2007

 

Последовательность построения задается мастером диаграмм. Тип диаграммы можно выбрать также с помощью пиктограмм вкладки Вставка. Основной объект диаграммы – ряд Данных. Ряд данных – это совокупность данных, содержащая количественные характеристики объекта. Эти данные содержатся в одном из векторов (в столбце или в строке), составляющих таблицу. В качестве имен рядов данных Excel использует заголовки столбцов или строк данных. Имена рядов отображаются в легенде диаграммы. Отображением рядов данных на диаграмме являются маркеры данных. Геометрические размеры маркера соответствуют численному значению отображаемых данных. Каждый маркер соответствует одному значению данных вектора, использованного в качестве ряда данных. С понятием «ряды данных» тесно связано понятие «категории данных», отражающее качество (свойство) элементов в ряду. В качестве имен оси категорий Excel использует заголовки тех столбцов или строк таблицы, которые не используются в качестве рядов данных.

Другими объектами диаграмм являются:

1) легенда – текст, идентифицирующий отдельные элементы
диаграммы;

2) ось – одна из сторон диаграммы. По горизонтальной оси обычно отображаются категории и/или названия рядов. По вертикальной оси – данные;

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

На первом этапе пользователь имеет возможность выбрать те диаграммы, которые в наибольшей степени соответствуют целям анализа. Следующий этап заключается в выборе или уточнении диапазона данных, используемых для построения диаграммы. Для этого используется вкладка Диапазон данных. При активной вкладке выделить диапазон значений функции. При активной вкладке Ряд в строке Подписи оси Х активизировать курсор и выделить диапазон исходных данных. Кроме того, с помощью команд, доступных при переходе к вкладке Ряд, можно добавить или удалить ряды данных. Затем, на третьем шаге, пользователь может выбрать дополнительные объекты для включения в состав диаграммы и задать некоторые их характеристики и содержание надписей. На заключительном, четвертом этапе пользователь должен решить вопрос размещения диаграммы – на отдельном листе или на том листе, где расположены данные. Если свойства объектов, включенных в диаграмму не устраивают пользователя, то ее следует переформатировать.

Большое место в MS Excel занимают функции. Для работы с функциями существует специальное средство – Мастер функций. Диалоговое окно Мастер функций можно выбрать во вкладке Вставка или кнопкой f_x в строке формул. В открывшемся диалоговом окне выбрать нужную категорию функций и требуемую функцию. В следующем окне Аргументы функции задать данные для расчета (список аргументов).

 

Использование математических функций

Среди математических функций значительное место занимают тригонометрические функции. В их число входят прямые и обратные тригонометрические, а также гиперболические функции. Для вычисления этих функций следует ввести только один аргумент – число. Для функций SIN(число), СОS(число) и ТАN(число) аргумент число – это угол в радианах, для которого определяется значение функции. Если угол задан в градусах, его следует преобразовать в радианы путем умножения его на ПИ()/180 или использования функции РАДИАНЫ.

Пример. Составить таблицу значений функции у=sin(x) для х, принадлежащего отрезку [20^o, 60^o] с шагом h=3^o

Построить по данным таблицы график функции у=sin(x).

 

	А	В	С	D
	№	Х(град)	Х(радианы)	У
			=радианы(В2)	=sin(C2)
		…

 

Столбцы А и В заполнить, используя автозаполнение. Для этого поместить в ячейки В2 и В3 соответственно значения х в градусах 20 и 23, выделить обе ячейки, подвести указатель мыши к маленькому черному квадрату, нажать левую кнопку мыши и, не отпуская ее, провести по всем ячейкам данного столбца. Таким же образом, заполнить столбец А. Установить курсор в ячейку С2 и вызвать мастер функций. Выбрать категорию функций Математическая в открывшемся диалоговом окне и в списке отыскать функцию радианы для перевода угла из градусов в радианы. В следующем диалоговом окне указать адрес ячейки, для которой выполняется операция. Адрес ячейки рекомендуется указывать с помощью мыши. За черный квадрат распространить формулу на остальные ячейки.

Аналогичные действия выполнить для столбца D (рис. 3).

Рис. 3. Составленная таблица значений функции y=sin(x)

 

Для построения графика выделить столбец х(радианы) или столбец х(град), нажать клавишу Ctrl и, не отпуская ее, выделить столбец у. Вызвать мастер диаграмм, выбрать Точечная (рис. 4).

Рис. 4. Построенный точечный график функции y=sin(x) при xÎ [20; 59]