Создание деловой графики

Зачетное задание

Практическая работа 3. «Элементарные вычисления в MS Excel»

Цель работы: Освоить работу с электронными таблицами MS Excel на примере вычисления значений функции при различных значениях аргумента. Освоить понятия «абсолютной» и «относительной» ссылки.

Задание:

1. Открыть MS Excel и создать новый документ.

2. Заполнить столбец значений аргумента с заголовком.

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

4. Открыть второй лист.

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

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

7. Открыть третий лист.

8. Заполнить значения аргумента x в первый столбец, начиная со второй строки, а значения аргумента y в первую строку, начиная со второго столбца.

9. Вычислить значения функции двух переменных.

10. Переименовать листы.

11. Сохранить документ.

Пример выполнения задания:

Задание:

1. f(x)=x ² +x+ 3, где x меняется от 0 до 5 с шагом 0,5.

2. F(x,y)=x ² +y ²–10, где x меняется от 0 до 4 с шагом 0,5, а y меняется от 0 до 4 с шагом 0,25.

1. Для выполнения задания 1 найдем в меню «Пуск» строчку «Программы», вы увидите выпадающее меню:

в котором надо выбрать строку .

В результате вы увидите окно, в котором уже открыт новый документ.

Если в окне нет документа или открыт чужой документ, необходимо выбрать пункт меню «Файл» и там найти команду «Создать...»:

Откроется новый документ.

Отметим, что чистый документ представляет из себя таблицу, у которой проименованы столбцы и пронумерованы строки. Имена столбцов задаются латинскими буквами в алфавитном порядке до столбца с именем Z, следующий столбец имеет имя AA, затем AB, AC и т.д.

Примечание. Если имена ячеек задаются в виде R1C1, изменить на латинский алфавит можно с помощью \ Сервис \ Параметры…, там выбрать закладку «Общие» и убрать флажок из поля «Стиль ссылок R1C1» в разделе «Параметры».

В остальном оформление окна не отличается от окна MS Word, за исключением строки формул:

Слева в ней указывается адрес активной ячейки, справа набираются формулы начиная со знака «=», а также имеется ряд кнопок:

, которая вызывает встроенные функции Excel и присутствует всегда;

, которая стирает формулу из строки формул;

– вычисляет формулу.

Примечание. Рассматривать работу с панелью инструментов «Стандартная» и «Форматирование» не будем, так как обе эти панели подробно разобраны в пособии «Лабораторный практикум. Часть 1. Word». Отметим только, что применять форматирование к ячейкам можно или до набора в них текст, или к уже набранному тексту. Для этого надо выйти из режима набора текста в ячейку (щелчок левой кнопки мыши на любой другой ячейке) и установить на нее курсор ячейки, теперь можно применять форматирование.

В Excel имеется несколько видов курсоров, рассмотрим их все:

ê – текстовой курсор, позволяющий набирать текст в ячейки строку формул.

– курсор мыши, позволяющий производить выбор и выделение ячеек.

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

– курсор ячейки.

– курсор мыши, позволяющий перемещать ячейки.

и – курсоры мыши, позволяющие расширять строки и столбцы. Появляются только в заголовке столбцов и нумерации строк.

и – курсоры мыши, позволяющие выделять целиком столбец или строку.

2. Теперь мы можем начать работать в Excel.

Заполним столбец значений аргумента x, который изменяется от 0 до 5 с шагом 0,5.

Это можно сделать несколькими способами:

Способ 1. В ячейка А1 запишем «x» и отформатируем его по центру. В ячейку А2 запишем 0. В ячейку А3 – 0,5. В ячейку А4 – 1 и т.д. до ячейки А12, в которую записывается 5.

Способ 2. В ячейку А1 запишем «х» и отформатируем его по центру. В ячейку А2 запишем 0. В ячейку А3 – 0,5. Затем выделим ячейки А2 и А3, установим курсор мыши в нижний правый угол (он примет вид «+»), нажмем левую кнопку мыши и, удерживая ее, перемещаем до ячейки А12 включительно. (Отметим, что справа от курсора появляется желтенький прямоугольник, в котором при движении мыши меняются цифры. Эти цифры указывают значение в ячейке, мимо которой движется указатель мыши.) Данный способ эффективен, если параметр изменяется с постоянным шагом.

Способ 3. В ячейку А1 запишем «х» и отформатируем его по центру. В ячейку А2 запишем 0. В ячейку А3 установим курсор и наберем формулу:

= А2+0,5

нажмем Enter. Затем установим курсор ячейки на А3, а курсор мыши – на нижний правый угол («+») и, удерживая левую кнопку мыши, растянем до А12.

В результате получим:

3. Вычислим значения заданной функции f(x)=x ² +x+ 3.

Для этого в ячейке В1 наберем текст «f(x)» и центрируем его. В ячейку В2 наберем формулу:

=А2*А2+А2+3

т.е. вместо «х» вы набираете адрес ячейки, в которой находится соответствующий аргумент.

Нажмите кнопку «Вычисление формулы».

Затем, установив курсор мыши в нижний правый угол (+), растяните формулу до ячейки В12.

В результате получим:

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

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

4. У MS Excel есть еще одно отличие от редактора MS Word. Каждый документ MS Excel состоит из нескольких отдельных листов, выполняющих роль отдельных документов, но при этом с возможностью ссылки друг на друга. Закладки листов находятся в нижней части окна MS Excel, сразу над строкой состояния:

Вычислим функцию двух переменных на листе 2. Для этого откроем второй лист (щелкнув левой кнопкой мыши на закладке «Лист2»).

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

В ячейку А1 запишем «х» по центру. В ячейку В1 – «y» по центру. В ячейку А2 – 0. В ячейку В2 – 0. В ячейку А3 – 0. В ячейку В3 – 0,25.

Теперь выделим четыре ячейки от А2 до В3, установим курсор мыши в нижний правый угол и, удерживая левую кнопку мыши, растягиваем до строки 18 включительно, получим:

Теперь посчитаем для второго значения х. В ячейку А19 набираем 0,5. В ячейку В19 – 0. В А20 – 0,5. В В20 – 0,25. Выделяем ячейки А19 – В20 и растягиваем черным крестиком (+), получаем:

И так заполним для каждого х до значения 4 с шагом 0,5. Вы получите длинную таблицу из 154 строк.

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

6. Вычислим функцию f(x,y)=x ² +y ² – 10 в третьем столбце. Для этого в ячейку С1 наберем «f(x,y) » и центрируем. В ячейку С2 наберем:

=А2*А2+В2*В2-10

и вычислим формулу.

После чего растянем формулу за нижний правый угол и получим:

7. Теперь откроем лист 3, для этого надо щелкнуть левой кнопкой мыши по закладке «Лист3».

8. Заполним значения аргумента для функции двух переменных, только теперь значения х расположим в столбец, а значения y – в строку.

Ячейку А1 оставим пустой. В ячейку А2 запишем 0. В А3 – 0,5. Теперь выделим ячейки А2 и А3 и растянем за нижний правый угол до строки 10.

Заполняем значения y. Для этого в ячейку В1 запишем 0. В С1 – 0,25. Выделим ячейки В1 и С1, а далее растянем за нижний правый угол до ячейки R1. Мы получили:

9. Теперь заполним значения функции в ячейку В2:

= А2*А2+В1*В1-10

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

Нам необходимо зафиксировать в ссылках для х столбец А (сделать абсолютную ссылку на столбец А), а в ссылках для y – строку 1 (сделать абсолютную ссылку на строку 1). Для того чтобы фиксировать строки и столбцы в ссылках, в Excel предназначен знак «$». Тогда в нашей формуле правильно записать ссылки для х – $A2, а ссылки для y – B$1. Мы получим:

=$A2*$A2+B$1*B$1-10

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

10. Теперь переименуем листы Excel. Для этого установим курсор на закладку «Лист1» и щелкнем один раз правой кнопкой мыши, получим контекстное меню вида:

в котором выберем пункт «Переименовать» и запишем туда «f(x)».

Повторим процедуру для второго и третьего листа, назвав их «f1(x,y)» и «f2(x,y)» соответственно. Мы получим:

11. Для сохранения документа выполним одно из следующих действий:

· \ Файл \ Сохранить … В открывшемся окне в поле «Папка» указываем диск и папку, куда сохраняем файл. В поле «Имя» набираем имя сохраняемого документа. Нажимаем кнопку «Сохранить».

Нажимаем кнопку на панели инструментов «Стандартная» и в полученном окне проводим вышеуказанные действия.

Практическая работа №4. «Работа с листами и графиками в MS Excel»

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

Задание:

1. Открыть MS Excel и созданный ранее документ.

2. Создать новый лист (Лист4) и переименовать его в f3(x).

3. Вычислить значения заданной функции в столбце А на новом листе, взяв значения аргумента с Листа1, названного f(x).

4. Построить график полученной функции на этом же листе.

5. По заданной таблице построить поверхность на отдельном листе.

6. Сохранить документ.

Пример выполнения задания:

Задание:

1. f(x)=x ² +x+ 3, где x меняется от 0 до 5 с шагом 0,5.

2. F(x,y)=x ² +y ² – 10, где x меняется от 0 до 4 с шагом 0,5, а y меняется от 0 до 4 с шагом 0,25.

1. Откроем Еxcel и загрузим в него уже имеющийся файл, для чего выполним одно из следующих действий:

· \ Файл \ Открыть …. Открывается диалоговое окно «Открыть», очень похожее на окно «Сохранить», в поле «Папка» открываем диск и папку, где был записан файл, в окне выбора находим свой файл и дважды щелкаем по нему левой кнопкой мыши (или один щелчок по файлу левой кнопкой мыши, а когда его имя появится в поле «Имя», нажать кнопку «Открыть»).

· Нажать кнопку на панели инструментов «Стандартная», далее появится диалоговое окно «Открыть», действия в котором мы уже описывали.

2. Создадим новый лист, для этого выполним \ Вставка \ Лист и получим:

Так как мы уже переименовали листы, то новый лист опять носит название Лист1. Переименуем его в f3(x).

3. Произведем вычисление значений функции f(x). Для этого в ячейку А1 наберем «f(x)» и центрируем, а в ячейку А2 наберем:

=’f(x)’!A2*’f(x)’!A2+’f(x)’!A2+3

где ’ f(x)’! – ссылка на лист с именем f(x).

Теперь вычислим формулу и растянем ее до строки 12, поскольку значения аргумента у нас посчитаны только до этой строки. Получим:

4. Теперь построим график данной функции на этом же листе, для этого выделим ячейки от А2 до А12 и выполним \ Вставка \ Диаграмма... Появится диалоговое окно вида:

На закладке «Стандартные» в поле выбора «Тип» выберем «График», в поле выбора «Вид» выберем первый во втором ряду и нажмем кнопку «Далее», получим окно:

Первая закладка этого окна автоматически заполнилась, заполним вторую закладку:

Для этого в поле «Имя» запишем

=’f3(x)’!$A$1

в поле «Подписи оси Х» заполним

=’f(x)’!$A$2:$A$12

Нажмем кнопку «Далее» и получим окно:

Данное окно позволяет произвести оформление графика. Начнем с первой закладки «Заголовки». Здесь в поле ввода «Название диаграммы:» вы вводите название вашего графика, назовем его «График функции», в поле «Ось Х (категорий)...» введем «х», в поле «Ось Y (значений)» вводим «f(x)».

Открываем вторую закладку «Оси»:

Здесь можно отключить оси с разбиением и подписями («ось Х» и «ось Y»), а также выбрать один из трех видов осей.

На третьей закладке «Линии сетки» вы можете включить и выключить основные и дополнительные линии сетки, как по x, так и по y:

На закладке «Легенда» вы можете добавить или убрать легенду, а также выбрать ее месторасположение на графике:

(Легендой в MS Excel называют прямоугольник, в котором сообщается, что нарисовано и какого цвета линия отрисовки, например: .)

На закладке «Подписи данных»: вы можете добавить имена рядов (в нашем случае ряд один – это f(x)), имена категорий (в данном случае значения х), значения самой функции, а также поставить различные величины между ними.

Заметим, что все эти величины пишутся прямо на графике, например:

Закладка «Таблица данных»:

На ней вы можете включить таблицу данных в график, например:

Когда настройки все сделаны, нажмите кнопку «Далее», вы получите диалоговое окно:

В этом окне вы можете выбрать положение графика (на отдельном листе или на том же самом, где и данные). Выберем имеющийся лист и нажмем кнопку «Готово», получим график вида:

5. Откроем лист f2(x,y).

6. Для построения поверхности выделим ячейки от А1 до R10 и выберем \ Вставка \ Диаграмма... В появившемся диалоговом окне, на закладке «Стандартные» в поле выбора «Тип» выберем «Поверхность», а в поле выбора «Вид» выберем вид поверхности (имеются три вида изображения поверхности: 1) классическое изображение поверхности с выделением уровней цветами; 2) изображение поверхности черными контурными линиями; 3) плоское изображение с выделением высот и впадин цветом и четкой отрисовкой линий уровня (напоминает школьный атлас по географии); 4) плоское изображение состоящие только из линий уровня(как в контурной карте)).

Когда вы выбрали нужный вид поверхности, нажмите кнопку «Далее», получите окно вида:

Обращаем ваше внимание на то, что при таком способе построения в этом окне заполнены все необходимые поля на обеих закладках. Такое возможно, только если ячейка А1 пуста.

Нажимаем кнопку «Далее» и получаем диалоговое окно для настройки заголовков, осей, легенды и т.д. Опять нажимаем кнопку «Далее» и получаем окно для выбора размещения диаграммы. На этот раз выберем размещение на отдельном листе, в поле ввода вместо «Диаграмма 1» поместим другое название этого листа: f21(x,y) и нажмем кнопку «Готово». В результате получим:

Как видим, появился отдельный лист, на котором есть только данная поверхность.

Практическая работа №5. «Операции с условием в MS Excel»

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

Задание:

1. Открыть MS Excel и созданный ранее документ. Создать новый лист и назвать его if(x).

2. Вычислить значение заданной функции одной переменной f1 с условием.

3. Вычислить количество точек функции, попадающих в заданный интервал.

4. Вычислить значения заданной функции одной переменной f2.

5. Вычислить сумму тех значений функции, аргументы которых лежат в заданном интервале.

6. Вычислить значение функции двух переменных.

7. Вычислить максимальное и минимальное значение функции.

8. Вычислить количество положительных и сумму отрицательных элементов функции.

9. Посчитать произведение тех значений функции, которые меньше 2.

10. Сохранить документ.

Пример выполнения задания:

Задание:

1. ,

где х меняется от –2 до 2 с шагом 0,2. Интервал: [–0,2; 0,2].

2. ,

где х меняется от –4 до 4 с шагом 0,5. Интервал [–1; 1]

где х меняется от –4 до 4 с шагом 0,5, а y от –2 до 2 с шагом 0,25.

1. Откроем документ (\ Файл \ Открыть...) и создадим новый лист (\ Вставка \ Лист). Переименуем лист при помощи контекстного меню в if(x).

2. Рассмотрим алгоритм вычисления данной функции.

Для этого построим блок-схему:

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

В первое поле ввода «Лог_выражение» вводится условие, записанное в нашей блок-схеме в ромбе. Во второе поле ввода «Значение_если_истина» вводится, то что присваивается в случае истинности условия. В третье поле ввода записывается оставшееся выражение, которое присваивается в случае невыполнения условия.

Данную функцию можно записать и без вызова ее окна, для этого записываем:

=ЕСЛИ(условие;выр_истина;выр_ложь)

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

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

В ячейках А1 и В1 запишем заголовок таблицы: «х» и «f1(x)» соответственно. В ячейку А2 введем –2, в А3 –1,8, теперь выделим ячейки А2 и А3 и растянем до ячейки А22. Теперь установим курсор ячейки на В2 и на строке формул нажмем кнопку , появится окно:

Развернем поле выбора «Категория» и выберем «Логические», получим:

В поле выбора «Выберите функцию:» выберем «ЕСЛИ» и нажмем «ОК». Получим окно функции «ЕСЛИ». Заполним первое поле этого окна:

Обращаем ваше внимание на то, что вместо х набрано А2, так как ячейке В2, содержащей значения функции, соответствует значение аргумента из ячейки А2. Далее, аналогично заполняя поля «Значение_если_истина» и «Значение_если_ложь», получим:

Теперь, нажав «ОК», мы получим следующий результат:

Обратите внимание, как в строке формул заполнилась сама функция ЕСЛИ().

Осталось только растянуть формулу до ячейки В22 и получить окончательный результат.

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

Для этого предназначена функция СЧЁТЕСЛИ().