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

«Использование встроенных функций в расчетах MS Excel»

Цель работы. Приобретение основных навыков использования функций в расчетных таблицах МS Excel

Краткие теоретические сведения.

1.      Ввод формулы

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

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

Ссылки различают ОТНОСИТЕЛЬНЫЕ (А2, В5,  Е10) и АБСОЛЮТНЫЕ ($A$2, $В$5,  $Е$10).

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

В примере складывается значение ячейки B4 с числом 25. Полученный результат делится на сумму ячеек  из диапазона D5: F5.

Правила ввода:

1. Укажите ячейку, в которую необходимо ввести формулу.
2. Введите = (знак равенства).

Если нажать кнопку Вставка функции, автоматически вставляется знак равенства.

3. Введите формулу.
4. Нажмите клавишу ENTER.

2.      Ввод функции

MS Excel позволяет работать  с логическими, математическими, статистическими, финансовыми  функциями,  ссылками и массивами и др. функциями. Функции могут быть использованы для выполнения простых или сложных вычислений. Самой часто используемой функцией является функция СУММ, которая используется для сложения чисел в диапазоне ячеек.

Функция СУММ складывает значения в диапазоне D5:F5.

Для ввода функций используется Мастер функций ( fx ).  Формула может быть введена в диапазон ячеек с помощью операций вставки-копирования.

2.1.   Стандартные функции

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

2.2.   Условная функция

Общий вид условной функции следующий: ЕСЛИ(<условие>;<выражение1>;<выражение2>)

Условие – это логическое выражение, которое может принимать значение ИСТИНА или ЛОЖЬ. <выражение1> и <выражение2> могут быть числами, формулами или текстами.

Условная функция, записанная в ячейку таблицы, выполняется так: если условие истинно, то значение данной функции определит <выражение1>, в противном случае - <выражение2>.

Задание 1. Создайте таблицу по образцу

1. Введите Таблицу. Установите нужные шрифты и переносы по словам.

5. Сохраните таблицу под именем Tab1.xls

Задание 2. Составьте таблицу «Прейскурант»

1. Заполните  таблицу по образцу, соблюдая шрифты и переносы по словам.

3. Названия столбцов Цена(у.е.), Цена(руб) выровнены по центру, как по горизонтали, так и по вертикали.

4. В клетки С4:С8 занесите формулы для вычисления цены товара в рублях. Для этого в ячейку С4 введите формулу для расчета цены товара в рублях: =B4*C2 и измените в формуле относительный адрес ячейки С2 на абсолютный $C$2. Такое значение позволит правильно копировать его в другие ячейки. Для получения абсолютного значения нажмите клавишу «F4».

3.   Скопируйте значение вниз по столбцу в ячейки С5:С8.

4.   Сохраните таблицу под именем Tab2.xls

Задание 3. Составьте таблицу «Ведомость»

1.Заполните  таблицу по образцу, соблюдая шрифты и переносы по словам.

2. Введите фамилии и инициалы служащих.

З. В ячейку С4 введите произведение минимальной зарплаты ($С$2) на соответствующий коэффициент В4. В формуле должна использоваться абсолютная ссылка на клетку, содержащую минимальную зарплату.

4. Скопируйте формулу в ячейки С5:СI0.

5. В ячейку D4 введите налог 16% от зарплаты: =16*С4/100. Скопируйте формулу в ячейки D5:D10.

6. В ячейку Е4 введите сумму к выдаче: =С4-D4. Скопируйте формулу в ячейки Е5:Е10.

7. Задайте в ячейке С11 формулу для суммы всех зарплат C4:C10 и скопируйте ее в ячейки D11, Е11 для вычисления суммы налога и суммы к выдаче.

8. Сохраните таблицу под именем Tab3.xls

Задание 4. Составьте таблицу "Прибыль предприятия"

1. Заполните таблицу по образцу, соблюдая шрифты и переносы по словам.

Задание 5. Составьте таблицу "Абитуриент”

1.      Заполните таблицу по образцу, соблюдая шрифты и переносы по словам.

2.      В ячейку С3 введите значение проходного балла: 13

3.      В ячейке Е5 введите формулу для суммирования баллов за 3 экзамена:

        = СУММ(B5:D5)

4.      Формула в ячейке F5 задается с помощью условной функции: =ЕСЛИ(E5>=$C$3;"Да";"Нет")

                                                                                                               Если сумма баллов (E5) ≥ проходному баллу

                                                                                                               (С1), то в ячейке F5 будет отображаться текст –

                                                                                                               Да, в противном случает – Нет.

                                                                                                               Для проходного балла в формуле используется

                                                                                                               абсолютный адрес $C$3, т.к. проходной балл

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

5.      После заполнения 5-ой строки формулами, произведите копирование соответствующих

 формул в нижние строки

6.      Сохраните таблицу под именем Tab5.xls

Задание 6. Составьте таблицу "Сальдо”

(Сальдо - разность между финансовыми поступлениями и расходами за определенный промежуток времени)

1. Заполните таблицу по образцу, соблюдая шрифты и переносы по словам.

2. Введите формулы расчета общей прибыли в ячейки B6:D6.

3. В ячейке Е4 задайте формулу для суммирования данных в ячейках B4:D4 и скопируйте ее в ячейки E5: E6.

4. В ячейке В16 задайте формулу суммирования платежей и скопи­руйте ее в ячейки С16, D16.

5. В ячейке Е8 задайте формулу для суммы данных в ячейках B8:D8 и скопируйте ее в ячейки Е9:Е15.

6. В ячейку E16 введите формулу суммы всех платежей.

7. Задайте в ячейке В17 формулу расчета сальдо

=B6-B16 и скопируй­те ее в ячейки С17:Е17.

8.  Сохраните таблицу под именем Tab6.xls