Лабораторная работа №1.03. Вычисления в MS Excel

Цель работы: ознакомиться со стандартными функциями MS Excel, освоить технологию использования формул и построения графиков.

Задание

Разработать электронную таблицу для расчета функции в виде таблицы из 30 точек и последующего построения по ней графика по формуле

где a,b,c,e – постоянные значения исходных данных x -переменная аргумента, величина которой задается с помощью: Xn – начального значения, Xk -конечного значения.

1. Ход работы

1.2. Запуск среды ms Excel

Загрузка среды Microsoft Excel производится одним из следующих способов:

• с помощью двойного клика левой кнопкой мыши по одноименному ярлыку на Рабочем столе системы Windows;

• нажатием кнопки Пуск, с последующим выбором в меню Программы пункта Microsoft Excel;

• нажатием кнопки Пуск, затем выбором в Стартовом меню раздела выполнить, далее введя в поле ввода путь C:\Program Files\Microsoft Office\Office\Excel.exe.

После успешной загрузки среды на экране отобразится окно MS Excel.

Рисунок 1.1 – Окно среды Microsoft Excel

1.3. Создание файла электронной таблицы (книги)

При загрузке среды в окне сразу формируется новая электронная книга MS Excel, которая по умолчанию состоит из трех электронных таблиц, расположенных соответственно на страницах ЛИСТ1, ЛИСТ2 и ЛИСТ3.

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

1.4. Задание параметров страницы электронной таблицы

Перед заполнением электронной таблицы следует задать параметры отображения страницы на листе бумаги формата А4. Для этого следует выбрать меню Разметка страницы/Параметры страницы. При этом на экране раскроется одноименное окно.

Рисунок 1.2 – Вид окна диалога Параметры страницы

Сначала с помощью мышки нужно установить маркер в точке книжная в разделе Ориентация. Далее необходимо с помощью выпадающего списка установить размер бумаги, соответствующий формату А4 (210 х 297 мм).

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

Рисунок 1.3 – Настройки полей страницы электронной таблицы

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

1.5. Формирование электронной таблицы

1.5.1. Установка ширины столбцов ячеек

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

1.5.2. Запись исходных данных

Затем необходимо разместить в ячейках таблицы исходные данные. Для ввода текстового комментария или численного значения в рабочей области следует навести курсор на выбранную ячейку, затем набрать текст или численное значение и ввести его нажатием <Enter>. В отличие от языков программирования при вводе десятичных чисел дробная часть отделяется от целой символом запятой. По умолчанию численные константы выравниваются по правому краю ячейки, а строковые – по левому. В ячейке будет отображаться заданные значения. Если нужно скорректировать содержимое ячейки, то следует установить на нее курсор и нажать клавишу <F2> (или можно выполнить двойной клик левой кнопкой мыши), при этом в поле имени будет указан адрес ячейки, а в строке формул – содержащиеся в ней данные. Редактирование содержимого ячейки производится в строке формул или в самой ячейке.

Рисунок 1.4 – Пример задания исходных данных

1.5.3. Расчет шага изменения аргумента

Для формирования таблицы понадобится предварительно определить значение шага изменения аргумента. Для этого выделяется отдельная ячейка. Сначала в соседнюю слева от нее ячейку записывается текст комментария. Затем наводится курсор на выделенную ячейку и набирается формула определения шага, которая должна начинаться со знака равенства (=). Шаг определяется по формуле

Шаг = (Xконечное - Xначальное ) / число точек таблицы

При записи формулы вместо имен переменных указываются адреса ячеек, где хранятся нужные при расчете данные. Адрес ячейки обозначается с помощью латинских букв ее номера столбца и цифр номера строки. Для рассматриваемого примера в ячейке B8 формула расчета шага имеет вид = (B7 – B6)/30.

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

Рисунок 1.5 – Реализация расчета шага изменения аргумента

1.5.4. Формирование таблицы результатов расчета

Сначала формируется текст заголовка таблицы. В первом столбце отображаются возрастающие значения аргумента, а во втором – соответствующее ему значение функции. Первое значение аргумента задается с помощью записи формулы в виде адреса ячейки, хранящего начальное значение аргумента Xn. В примере содержимое ячейки А11 имеет вид формулы =B6

В ячейке для расчета значения функции, которая находится справа от ячейки со значением аргумента, записывается с учетом синтаксиса стандартных функций расчетная формула, содержащая вместо имен переменных их адреса. Синтаксис функций Excel описан в пункте 2. Пример простой расчетной формулы для ячейки B11

=B2*A11^3+B3*КОРЕНЬ(ABS(A11-B4))+B5

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

Абсолютный адрес записывается с символом доллара перед именем столбца номером сроки в виде: $<имя столбца>$<номер строки>. Чтобы изменить адресацию с относительной на абсолютную во введенной формуле, следует перейти в режим редактирования ячейки формулы (нажав <F2>) и добавить символ $ в адреса исходных данных, оставив неизменным относительным адрес аргумента. Абсолютный адрес в формуле можно выставить при редактировании формулы сразу, наведя курсор в строке формулы на нужный адрес и нажав клавишу <F4>.

Рисунок 1.6 – Вид формулы расчета функции в среде Excel

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

=A11+$B$8

Для формулы ячейки B11 выражение примет вид

=$B$2*A11^3+$B$3*КОРЕНЬ(ABS(A11-$B$4))+$B$5

Далее следует, чтобы не набирать заново формулу расчета функции скопировать ее из верхней строки. Для этого наводится курсор на верхнюю ячейку с формулой и копируется ее содержимое в буфер нажатием клавиш <Ctrl>+<C>. После этого вокруг скопированной ячейки появляется выделение из бегающих точек. Далее курсор переводится на ячейку, куда будет скопирована формула, и вставляется содержимое буфера нажатием клавиш <Ctrl>+<V>. При этом в формуле абсолютные адреса ячеек останутся без изменения, а изменится лишь адрес текущего аргумента на единицу.

Чтобы автоматически заполнить остальные 28 строк таблицы, следует выделить полностью вторую строку таблицы результатов. Для этого подводится курсор в начало строки и нажимается клавиша <Shift> и удерживается, а курсор перемещается клавиатурой к концу выделяемого блока. После того как нужный блок будет выделен темным фоном, клавиша <Shift> отпускается. Выделить блок можно и с помощью мыши.

Рисунок 1.7 – Таблица результатов расчетов

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

1.5.5. Оформление результатов расчетов

В полученной таблице результатов расчетов устанавливается видимой внешняя и внутренняя границы (устанавливается обрамление). Для этого выделяются в виде блока все ячейки таблицы и, используя меню Главная/Число или Формат ячеек в контекстном меню (по правой кнопке мыши), раскрывается окно диалога вида

Рисунок 1.8 – Окно настроек формата ячеек таблицы

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

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

1.5.6. Оформление листа таблицы для анализа данных

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

На втором листе должна отображаться текущая дата расчетов, которая выдается функцией СЕГОДНЯ.

По значениям функции из первого листа при анализе данных таблицы функции необходимо определить максимальное и минимальное значение функции(с помощью функций МАКС и МИН), а также среднее значение и среднее отклонение значений функции в таблице (используя СРЗНАЧ и СРОТКЛ), и общую сумму элементов таблицы (с помощью функции СУММ). При этом используются функции из таблицы 2.1. В адресах ячеек таблицы необходимо указывать наименование первого листа.

Таблица 2.1.

Анализ результатов расчета функции
Дата расчета	=СЕГОДНЯ()
Максимум функции =	=МАКС(Лист1!B11:Лист1!B41)
Минимум функции =	=МИН(Лист1!B11:Лист1!B41)
Среднее значение	=СРЗНАЧ(Лист1!B11:Лист1!B41)
Среднее отклонение	=СРОТКЛ(Лист1!B11:Лист1!B41)
Сумма функций	=СУММ(Лист1!B11:Лист1!B41)

Результаты анализа оформляются в виде таблицы. Для листа задаются параметры страницы аналогичные предыдущему листу с данными.