Лабораторная работа 1 — Excel


Лабораторная работа №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" и "Склад 2".
2.   Оформить заголовок таблицы "Товары на складе" (интервал D2:G3).
3.   Скопировать через буфер обмена названия товаров: интервал A3:A7 в D4, а интервал A11:A15 в D9.
4.   Записать в E4 формулу =B3. После завершения ввода в E4 должно быть показано содержимое ячейки B3.
5.   Скопировать E4 на интервал E5:E8. Это действие называется автозаполнением ячеек. При автозаполнении адреса ячеек в формулах модифицируются автоматически.
6.   Записать в Е9 формулу =B11 и выполнить автозаполнение для интервала E10:E13.
7.   Задать для интервала F4:G13 для ячейки G14 денежный формат. Для этого выделить интервал или ячейку и обратиться к меню "Формат"( "Ячейки..." ( "Число"( "Денежный". В окне "Формат ячеек" задать "Количество десятичных знаков" - 2.
8.   Ввести цены товаров в интервал F4:F13. Символы "р." не набираются, а вставляются автоматически, в соответствии с форматом ячеек.
9.   В G4 вписать формулу =E4*F4 и выполнить автозаполнение для интервала G5:G13.
10. Выделить G14 и обратиться к меню "Вставка"( "Функция". В первом окне "Мастера функций" выбрать категорию - "Математические" и функцию - СУММ. Во втором окне следует задать интервал G4:G13. Это можно сделать двумя способами: набрав его в поле ввода или выделив мышью этот интервал в основной таблице. В последнем случае необходимо предварительно передвинуть окно мастера функций так, чтобы освободить нужную область таблицы. В ячейку G14 будет вставлена функция =СУММ(G4:G13)
11. Выполнить обрамление таблиц, центрирование данных и другие действия по форматированию таблиц.
 


ЗАДАНИЕ

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

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

Пример использования математических функций:
Построить таблицу значений для функции , для .
Решение:
Для решения задачи необходимо выполнить следующие действия:
Оформить таблицу с двумя столбцами: первый озаглавить x, второй – f(x).
Столбец x заполнить с помощью функции автозаполнения значениями от -10 до 10 с интервалом равным 1.
В ячейку B2 ввести: =КОРЕНЬ(СТЕПЕНЬ(A2;4)+COS(A2)).
C помощью функции автозаполнения заполнить столбец f(x).
В результате получится таблица:




Задания:

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

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

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

Пример использования логических функций:
У Кости есть 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 соответствует вторнику.

Задания:

1. Определить, сколько дней осталось от текущей даты до 1 января 2005 года.

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

Пример 1.Найти произведение двух матриц с помощью соответствующей функции и с помощью формул:
13 EMBED Equation.3 1415
Для решения используем функцию МУМНОЖ(массив1;массив2).
Функция МУМНОЖ (массив1;массив2) – возвращает произведение матриц (матрицы хранятся в массивах).
Результатом решения является таблица:

Порядок выполнения задания:
1. Сформировать два массива.
Выделить интервал ячеек: Е9:G11.
В ячейку Е9 вызвать функцию МУМНОЖ. Эта функция вызывается последовательностью действий: пункт меню Вставка/Функция/Математические функции/МУМНОЖ и выглядит следующим образом

Заполняете, как показано на рисунке и нажимаете CTRL+SHIFT+ENTER.

Использование формул массива.
Пример 2. Даны два массива данных 13 EMBED Equation.3 1415. Найти массив, элементы которого получаются сложением соответствующих элементов исходных массивов.
Порядок выполнения. 1. Введем исходные массивы.
2. Выделим диапазон для результирующего массива (3 Ч3) . Вводим формулу: {диапазон первого массива}+{диапазон второго массива} и нажимаем CTRL+SHIFT+ENTER.
В результате должно получится


Задания:

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

2,307905

2,393316

2,400502

2,464672

2,555918

2,665845

2,711983

2,778117

2,783806

2,789329

2,815507

2,868371

2,883881

2,969267

2,979641

3,099504

3,18204

3,187834

3,203

3,205072

3,298455

3,406127

3,407006

3,471391

3,503493

3,711445

3,752437

3,852843

3,981954

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


Root Entry

Приложенные файлы

  • doc 18763401
    Размер файла: 191 kB Загрузок: 0

Добавить комментарий