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

Вычисления в Excel

 

Общие сведения.

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

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

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

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

Пример.

Имеются 2 таблицы "Склад 1" и "Склад 2", содержащие сведения о количестве товаров на складах. Требуется составить таблицу "Товары на складах", показывающую количество и стоимость товаров на обоих складах. При изменении количества любого товара в исходных таблицах, сведения о нём в таблице "Товары на складе" должны меняться автоматически.

	A	B	C	D	E	F	G
1	Склад 1
2	Наименование	Количество		Товары на складах
3	Товар 1	71		Наименование	Количество	Цена	Стоимость
4	Товар 2	55		Товар 1	71	22,40р.	1 590,40р.
5	Товар 3	48		Товар 2	55	33,15р.	1 823,25р.
6	Товар 4	83		Товар 3	48	57,50р.	2 760,00р.
7	Товар 5	39		Товар 4	83	38,90р.	3 228,70р.
8				Товар 5	39	45,00р.	1 755,00р.
9	Склад 2			Товар 6	56	26,60р.	1 489,60р.
10	Наименование	Количество		Товар 7	34	60,50р.	2 057,00р.
11	Товар 6	56		Товар 8	28	110,00р.	3 080,00р.
12	Товар 7	34		Товар 9	72	43,10р.	3 103,20р.
13	Товар 8	28		Товар 10	60	47,80р.	2 868,00р.
14	Товар 9	72		Суммарная стоимость:			23 755,15р.
15	Товар 10	60

 

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

1. 1.   Оформить таблицы "Склад 1" и "Склад 2".

2. 2.   Оформить заголовок таблицы "Товары на складе" (интервал D2:G3).

3. 3.   Скопировать через буфер обмена названия товаров: интервал A3:A7 в D4, а интервал A11:A15 в D9.

4. 4.   Записать в E4 формулу =B3. После завершения ввода в E4 должно быть показано содержимое ячейки B3.

5. 5.   Скопировать E4 на интервал E5:E8. Это действие называется автозаполнением ячеек. При автозаполнении адреса ячеек в формулах модифицируются автоматически.

6. 6.   Записать в Е9 формулу =B11 и выполнить автозаполнение для интервала E10:E13.

7. 7.   Задать для интервала F4:G13 для ячейки G14 денежный формат. Для этого выделить интервал или ячейку и обратиться к меню "Формат"à "Ячейки..." à "Число"à "Денежный". В окне "Формат ячеек" задать "Количество десятичных знаков" - 2.

8. 8.   Ввести цены товаров в интервал F4:F13. Символы "р." не набираются, а вставляются автоматически, в соответствии с форматом ячеек.

9. 9.   В G4 вписать формулу =E4*F4 и выполнить автозаполнение для интервала G5:G13.

10. 10. Выделить G14 и обратиться к меню "Вставка"à "Функция". В первом окне "Мастера функций" выбрать категорию - "Математические" и функцию - СУММ. Во втором окне следует задать интервал G4:G13. Это можно сделать двумя способами: набрав его в поле ввода или выделив мышью этот интервал в основной таблице. В последнем случае необходимо предварительно передвинуть окно мастера функций так, чтобы освободить нужную область таблицы. В ячейку G14 будет вставлена функция =СУММ(G4:G13)

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

 

Задание

1. Оформить таблицу согласно варианту (файл «ExcelV2.doc») и провести вычисления по формулам в соответствующих ячейках.

2. Выполнить следующие примеры и задания:

Математические функции.

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

Построить таблицу значений для функции , для .

Решение:

Для решения задачи необходимо выполнить следующие действия:

1. Оформить таблицу с двумя столбцами: первый озаглавить x, второй – f(x).

2. Столбец x заполнить с помощью функции автозаполнения значениями от -10 до 10 с интервалом равным 1.

3. В ячейку B2 ввести: =КОРЕНЬ(СТЕПЕНЬ(A2;4)+COS(A2)).

4. C помощью функции автозаполнения заполнить столбец f(x).

В результате получится таблица:

Задания:

1. Создать таблицу умножения чисел от 1 до 9 (9 строк, 9 столбцов). В ячейке, соответствующей произведению 1*1, должна быть записана формула, которая затем должна быть скопирована во все остальные 80 ячеек.

2. Вычислить значение y, при заданных значениях величин a и b :

3. Построить таблицу значений функции в интервале -5<x<15.

Логические функции.

Пример использования логических функций:

У Кости есть 20 рублей. Хватит ли ему денег на 4 яблока, 3 груши и 5 апельсинов, если цена фруктов 3 руб., 3,5 руб. и 2 руб. соответственно.

Решение:

1. Вводим таблицу стоимости фруктов.

фрукты	количество	цена	стоимость
Яблоко	4	3 р.
Груша	3	3,5 р.
Апельсин	5	2 р.

2. Столбец стоимости заполняем с помощью формулы: количество*цена.

3. В любую пустую ячейку вводим логическую функцию:

=ЕСЛИ((D2+D3+D4)<=20;"хватит";"не хватит").

Задания:

1. Выяснить истинны или ложны выражения:

И(5<7;9+3=12);

И(4*24=96;0>5);

ИЛИ(15-5=11;45*3=100;23+3=20);

ИЛИ(4*4=16;6-5=2);

И(5=5;7=7;17-10=8).

2. Вывести сообщение в ячейке, в которой пишется условие, “значение в интервале”, если оно меньше -300 или больше 300. В противном случае вывести сообщение: "значение вне интервала".

3. В ячейках столбца с заголовком "Температура" набрать некоторые значения температуры. Используя соответствующую формулу, создать столбец с заголовком "Результат" по следующему правилу:

Температура<5oC – холодно

5oC≤Температура<15oC - прохладно.

15oC≤Температура<25oC – тепло

Температура>=25oC - жарко

4. Составить таблицу экзаменационных оценок студентов и на ее основе определить средний балл и размер стипендии для каждого студента.

Поля таблицы: фамилия и инициалы студента, несколько полей для экзаменационных оценок по предметам (информатика, история, право, экология, философия и т.д.), средний балл, размер стипендии. Правила определения размера стипендии:

Первое правило:

3,0≤СрБалл<3,5 - 300р.

3,5≤СрБалл<4,0 - 400р.

4,0≤СрБалл<4,5 - 500р.

4,5≤СрБалл<5,0 - 600р.

СрБалл=5,0 - 700р.

Второе правило:

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

Студентов должно быть не менее 5, предметов не менее 5.

5. Предположим, что рабочий лист по расходам содержит в ячейках B2:B4 фактические расходы за январь, февраль, март: 1500, 500 и 500 соответственно. Ячейки C2:C4 содержат данные по предполагаемым расходам за те же периоды: 900, 900 и 925. Нужно написать формулы для проверки соответствия бюджету расходов определенного месяца, генерируя тексты сообщений: ”Превышение бюджета” – когда бюджет превышает расходы и OK – в противном случае.

Функции для работы с датой и временем.

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

Определить на какой день недели приходится текущая дата.

Для решения этой задачи воспользуемся двумя функциями: СЕГОДНЯ() и ДЕНЬНЕД(дата_в_числовом_формате;тип).

Функция СЕГОДНЯ() возвращает текущую дату в числовом формате и не имеет аргументов.

Функция ДЕНЬНЕД(дата_в_числовом_формате;тип) возвращает день недели, соответствующий аргументу дата_в_числовом_формате. День недели определяется как целое в интервале от 1 (воскресенье) до 7 (суббота).

Дата_в_числовом_формате  — это число, соответствующее дате, день недели которой необходимо найти. Даты могут вводиться как текстовые строки в двойных кавычках (например "30.1.1998"), как числа (например 35825 представляет 30 января 1998 г. при использовании системы дат 1900) или как результат других формул или функций.

Тип   — это число, которое определяет тип возвращаемого значения.

Тип	Возвращаемое число
1 или опущен	Число от 1 (воскресенье) до 7 (суббота).
2	Число от 1 (понедельник) до 7 (воскресенье)
3	Число от 0 (понедельник) до 6 (воскресенье)

Решение:

Результатом решения является таблица:

В ячейке В1 находится формула =СЕГОДНЯ(). В ячейке С1 — формула =ДЕНЬНЕД(B1;2).

Для отображения дня недели принят тип 2, то есть число "2" в ячейке С1 соответствует вторнику.