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

Лабораторная работа №1

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

Построение линейного графика.

Цели и задачи лабораторной работы

Лабораторная работа направлена на ознакомление с принципами работы в табличном процессореMicrosoftExcel, получение навыков по анализу и методам обработки числовой и финансово-статистической информации.

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

 

Теоретические сведения для выполнения лабораторной работы

Формулы

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

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

• =5+2*3 Сумма числа 5 и произведения чисел 2 и 3.
• =A1+A2+A3 Сумма значений в ячейках A1, A2 и A3.
• =SQRT(A1) Функция SQRT возвращает квадратный корень значения в ячейке A1.
• =СЕГОДНЯ() Возвращает текущую дату.
• =ПРОПИСН("привет") Преобразует строку "привет" в "ПРИВЕТ" с помощью функции ПРОПИСН.
• =ЕСЛИ(A1>0) Анализирует ячейку A1 и проверяет, превышает ли значение в ней нуль.

Элементы формулы

Формула также может содержать функцию, ссылку на ячейку, оператор и константу.

Части формулы

1. Функции. Функция ПИ() возвращает значение числа Пи: 3,142...

2. Ссылки. A2 возвращает значение ячейки A2.

3. Константы. Числа или текстовые значения, введенные непосредственно в формулу, например 2.

4. Операторы: оператор ^ ("крышка") возводит число в степень, а оператор * ("звездочка") перемножает числа.

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

Константа представляет собой готовое (не вычисляемое) значение, которое всегда остается неизменным. Например, дата 09.10.2008, число 210 и текст "Прибыль за квартал" являются константами. выражение или его значение константами не являются. Если формула в ячейке содержит константы, но не ссылки на другие ячейки (например, имеет вид =30+70+110), значение в такой ячейке изменяется только после изменения формулы.

Использование операторов в формулах

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

Типы операторов

Приложение MicrosoftExcel поддерживает четыре типа операторов: арифметические, текстовые, операторы сравнения и операторы ссылок.

Арифметические операторы

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

Арифметический оператор	Значение	Пример
+ (знак "плюс")	Сложение	3+3
– (знак "минус")	Вычитание Отрицание	3–1 –1
* (звездочка)	Умножение	3*3
/ (косая черта)	Деление	3/3
% (знак процента)	Процент	20%
^ (крышка)	Возведение в степень	3^2

Операторы сравнения

Операторы сравнения используются для сравнения двух значений. Результатом сравнения является логическое значение: ИСТИНА либо ЛОЖЬ.

Оператор сравнения	Значение	Пример
= (знак равенства)	Равно	A1=B1
> (знак "больше")	Больше	A1>B1
< (знак "меньше")	Меньше	A1<B1
>= (знак "больше или равно")	Больше или равно	A1>=B1
<= (знак "меньше или равно")	Меньше или равно	A1<=B1
<> (знак "не равно")	Не равно	A1<>B1

Текстовый оператор конкатенации

Амперсанд (&) используется для объединения (соединения) одной или нескольких текстовых строк в одну.

Текстовый оператор	Значение	Пример
& (амперсанд)	Соединение или объединение последовательностей знаков в одну последовательность	Выражение "Северный "&"ветер" дает результат "Северный ветер".

Операторы ссылок

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

Оператор ссылки	Значение	Пример
: (двоеточие)	Оператор диапазона, который образует одну ссылку на все ячейки, находящиеся между первой и последней ячейками диапазона, включая эти ячейки.	B5:B15
, (запятая)	Оператор объединения. Объединяет несколько ссылок в одну ссылку.	СУММ(B5:B15,D5:D15)
(пробел)	Оператор пересечения множеств, используется для ссылки на общие ячейки двух диапазонов.	B7:D7 C6:C8

Порядок вычислений

Значения обрабатываются формулой в определенном порядке. Формула в MicrosoftExcel всегда начинается со знака равенства (=). Знак равенства свидетельствует о том, что следующие за ним знаки составляют формулу. Элементы, следующие за знаком равенства, являются используемыми в расчетах операндами (например, константами или ссылками на ячейки), которые разделяются операторами вычислений. Формула в Excel вычисляется слева направо в соответствии с порядком, определенным для каждого оператора в формуле.

Приоритет операторов

Если в одной формуле используется несколько операторов, MicrosoftExcel выполняет операции в порядке, указанном в приведенной ниже таблице. Если формула содержит операторы с одинаковым приоритетом — например, операторы деления и умножения, — они выполняются слева направо.

Оператор	Описание
: (двоеточие) (один пробел) , (запятая)	Операторы ссылок
–	Знак "минус"
%	Процент
^	Возведение в степень
* и /	Умножение и деление
+ и -	Сложение и вычитание
&	Объединение двух текстовых строк в одну
= <> <= >= <>	Сравнение

Синтаксис функций

Приведенный ниже пример функции ОКРУГЛ, округляющей число в ячейке A10, демонстрирует синтаксис функции.

Структура функции

1. Структура.

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

2. Имя функции.

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

3. Аргументы.

Существуют различные типы аргументов: числа, текст, логические значения (ИСТИНА и ЛОЖЬ), массив, значения ошибок (например, "#Н/Д") или ссылка на ячейку. В качестве аргументов можно использовать константа, формулы или функции. В каждом конкретном случае необходимо использовать аргументы подходящего типа.

4. Всплывающая подсказка аргумента.

При вводе функции появляется всплывающая подсказка с синтаксисом и аргументами. Например, всплывающая подсказка появляется после ввода выражения =ОКРУГЛ(. Всплывающие подсказки отображаются только для встроенных функций.

Стиль ссылок A1

Стиль ссылок по умолчанию

По умолчанию MicrosoftExcel использует стиль ссылок A1, в котором столбцы обозначаются буквами (от A до XFD, всего не более 16 384 столбцов), а строки — номерами (от 1 до 1 048 576). Эти буквы и номера называются заголовками строк и столбцов. Для ссылки на ячейку введите букву столбца, и затем — номер строки. Например, ссылка B2 указывает на ячейку, расположенную на пересечении столбца B и строки 2.

Ячейка или диапазон	Ссылка
Ячейка на пересечении столбца A и строки 10	A10
Диапазон ячеек: столбец А, строки 10-20.	A10:A20
Диапазон ячеек: строка 15, столбцы B-E	B15:E15
Все ячейки в строке 5	5:5
Все ячейки в строках с 5 по 10	5:10
Все ячейки в столбце H	H:H
Все ячейки в столбцах с H по J	H:J
Диапазон ячеек: столбцы А-E, строки 10-20	A10:E20

Ссылка на другой лист

В приведенном ниже примере функция СРЗНАЧ используется для расчета среднего значения диапазона B1:B10 на листе "Маркетинг" той же книги.

Ссылка на диапазон ячеек в другом листе той же книги

1. Ссылка на лист "Маркетинг".

2. Ссылка на диапазон ячеек от B1 до B10 включительно.

3. Ссылка на лист, отделенная от ссылки на диапазон значений.

Относительные ссылки

Относительная ссылка в формуле, например A1, основана на относительной позиции ячейки, содержащей формулу, и ячейки, на которую указывает ссылка. При изменении позиции ячейки, содержащей формулу, изменяется и ссылка. При копировании или заполнении формулы вдоль строк и вдоль столбцов ссылка автоматически корректируется. По умолчанию в новых формулах используются относительные ссылки. Например, при копировании или заполнении относительной ссылки из ячейки B2 в ячейку B3 она автоматически изменяется с =A1 на =A2.

 

Абсолютные ссылки

Абсолютная ссылка на ячейку в формуле, например $A$1, всегда ссылается на ячейку, расположенную в определенном месте. При изменении позиции ячейки, содержащей формулу, абсолютная ссылка не изменяется. При копировании или заполнении формулы по строкам и столбцам абсолютная ссылка не корректируется. По умолчанию в новых формулах используются относительные ссылки, а для использования абсолютных ссылок надо активировать соответствующий параметр. Например, при копировании или заполнении абсолютной ссылки из ячейки B2 в ячейку B3 она остается прежней в обеих ячейках: =$A$1.

 

Смешанные ссылки

Смешанная ссылка содержит либо абсолютный столбец и относительную строку, либо абсолютную строку и относительный столбец. Абсолютная ссылка столбцов приобретает вид $A1, $B1 и т.д. Абсолютная ссылка строки приобретает вид A$1, B$1 и т.д. При изменении позиции ячейки, содержащей формулу, относительная ссылка изменяется, а абсолютная ссылка не изменяется. При копировании или заполнении формулы вдоль строк и вдоль столбцов относительная ссылка автоматически корректируется, а абсолютная ссылка не корректируется. Например, при копировании или заполнении смешанной ссылки из ячейки A2 в ячейку B3 она изменяется с =A$1 на =B$1.

Диаграммы

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

Чтобы создать диаграмму в Excel, сначала введите числовые данные на лист. Затем можно построить на их основе диаграмму, выбрав нужный тип диаграммы на вкладке Вставить в группе Диаграммы.

1. Данные листа

2. Диаграмма, созданная на основе данных листа

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

Элементы диаграммы

Диаграмма состоит из различных элементов. Некоторые из них отображаются по умолчанию, другие можно добавлять по мере необходимости. Можно изменить вид элементов диаграммы, переместив их в другое место или изменив их размер либо формат. Также можно удалить элементы диаграммы, которые не требуется отображать.

1. Область диаграммы.

2. Область построения диаграммы.

3. Точки данных для ряда данных, отмеченные на диаграмме.

4. Ось категорий (горизонтальная) и значений (вертикальная), вдоль которых строится диаграмма.

5. Легенда диаграммы.

6. Названия диаграммы и осей, которые можно использовать в диаграмме.

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

 

Содержание лабораторной работы

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

Требования к выполнению работы:

 

1. Начальные значения для переменных, констант и шага необходимо задавать отдельно от вычислений, чтобы была возможность их смены.
2. В формуле использовать стандартные функции, абсолютные, относительные и смешанные ссылки.
3. Получить как минимум 20 результатов вычислений.
4. Построить линейный график функции и оформить его.

 

В оформлении должно присутствовать:

- название графика;

- подписанные оси категорий и значений;

- легенда графика;

- подписи данных.

1. Оформить отчет по лабораторной работе.

 

Отчет должен содержать разделы:

a) Титульный лист;

b) Цели и задачи лабораторной работы;

c) Содержание лабораторной работы;

d) Описание решения в виде алгоритма;

e) Результаты вычислений и график функции;

f) Вывод по лабораторной работе.

 

Задания на лабораторную работу

29.

30. ln(x)*y+ln(y)*x

31. 2^x-tg(x)

32. (x³-3)/sin(x)

33. |x²-cos(x+1)|+sin(y)

34. (sin(x)+cos(x))²/sin(x)

 

5. Контрольные вопросы

1. Элементы формулы.

2. Использование констант в формулах.

3. Арифметические операторы.

4. Операторы сравнения.

5. Операторы ссылок.

6. Порядок выполнения действий в формулах.

7. Приоритет операторов.

8. Использование круглых скобок в формулах.

9. Использование функций в формулах.

10. Использование ссылок в формулах.

11. Относительные ссылки.

12. Абсолютные ссылки.

13. Смешанные ссылки.

14. Создание диаграммы.

15. Элементы диаграммы.

 

Лабораторная работа №1

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