1. Откройте книгу «Лабораторные».
2. Дополните таблицу «Товары на складе» двумя столбцами - «Общая цена (р.)» и «Общая цена (?)», как показано на рис. 8. Также введите курс евро в ячейку D19.
Рис. 8
3. Чтобы рассчитать общую цену каждого товара в рублях, необходимо количество этого товара умножить на цену за единицу товара. Поэтому в ячейку H3 (для первого товара) введите знак «=» и формулу: =D3*E3 и нажмите Enter. В ячейке H3 появится значение.
4. С помощью Автозаполнения заполним остальные ячейки этого столбца: выделите ячейку H3; подведите курсор мыши к правому нижнему углу ячейки, курсор примет знак черного крестика; протащите курсор до конца диапазона (до ячейки H16).
Обратите внимание, что при перемещении формулы ссылки на ячейки тоже изменяются, переориентируясь на то место, куда переносится формула. Такие ссылки называются относительными.
5. Чтобы рассчитать общую цену каждого товара в евро, необходимо общую цену в рублях этого товара разделить на курс евро. Поэтому в ячейку I3 введите формулу: =H3/$D$19. Нажмите Enter и протащите формулу до конца таблицы.
При перемещении формулы ссылка на ячейку D19 не изменялась. Такие ссылки называются абсолютными и формируются с помощью знака $.
6. Через меню Формат - Ячейки установите соответствующий денежный формат для этих двух столбцов, цены в евро округлите до двух десятичных знаков.
7. Посчитайте суммарную цену всех товаров в рублях и евро:
- в ячейку H17 поставьте знак равенства и в списке функций (кнопка 
) найдите функцию СУММ (Эта функция также вызывается щелчком по кнопке S на панели инструментов);
- в открывшемся окне функции в поле Число1 введите диапазон суммируемых значений H3:H16 и нажмите ОК (или покажите мышью диапазон суммируемых значений).
|
|
В результате должны получиться следующие значения (рис. 9) и формулы (рис. 10):
Рис. 9 Рис. 10
4.4. Лабораторная работа №3. Вычисления с помощью мастера функций. Использование функции ЕСЛИ
1. Создайте новую книгу.
2. Составьте таблицу с информацией об успеваемости студентов (рис. 11).
| Отчетные данные за семестр | |||||
| Фамилия | Экономика | Математика | Информатика | Средний балл | На отчисление |
| Иванов | |||||
| Петров | |||||
| Сидоров | |||||
| Зайцев | |||||
| Волков | |||||
| Орлов |
Рис. 11
3. Необходимо вычислить средний балл для каждого из студентов и указать, каких из них нужно отчислить. Отчисление студента следует, если его средний балл меньше 3. Полученный результат нужно округлить до одного десятичного разряда. Округление можно реализовать, используя меню Формат - Ячейки - вкладка Число - выбрать строку Числовой и установить число десятичных знаков равным 1. Другой способ - использовать специальную функцию округления. Применим его. Так как сначала необходимо получить значение среднего балла, а уже затем его округлять, то функция получения среднего значения будет вложена в функцию округления. Для выполнения:
· Сделайте активной ячейку, куда следует поместить округленное значение среднего балла. В нашем случае это будет первая ячейка в столбце Средний балл.
· С помощью меню Вставка - Функции (иликнопки 
слева от строки формул) запустите мастер функций.
|
|
· В поле Категория выберите Математические, а в поле Функция - функцию ОКРУГЛ. Щелкните ОК. Появляется диалоговое окно этой функции, округляющей число до указанного количества десятичных разрядов.
 |
· Перейдите щелчком левой клавиши мыши в поле Число_разрядов (или Количество_цифр). Введите с клавиатуры количество десятичных разрядов, до которых нужно округлить число, например, 1 (см. рис 12).
Рис. 12
· Перейдите в поле Число. В этом поле должно находиться округляемое число, т.е. значение среднего балла. Его можно вычислить с помощью функции СРЗНАЧ. Таким образом, функция СРЗНАЧ будет являться аргументом функции ОКРУГЛ, т.е. в поле Число должен стоять вызов функции СРЗНАЧ. Для этого:
· Вызовите еще раз мастер функций. Для этого в строке формул щелкните левой клавишей мыши на кнопке со стрелкой вниз, расположенной справа от окна функций. В открывшемся меню выберите функцию СРЗНАЧ, если она там есть, иначе выберите пункт Другие функции, запускающий мастер функций.
· В поле Категория выберите Статистические, а в поле Функция выберите функцию СРЗНАЧ. Щелкните на кнопке ОК. При этом появляется диалоговое окно для задания аргументов функции СРЗНАЧ.
· В поле Число1 необходимо ввести диапазон ячеек с данными, для которых нужно вычислить их среднее значение. Excel сразу предлагает некоторый диапазон. Если он вас не устраивает, вы можете указать свой (выделить мышью или записать в поле вручную адрес - имя диапазона). После того, как диапазон обозначен, щелкните ОК.
· Чтобы вычислить средний балл для остальных учеников достаточно воспользоваться автозаполнением: протащить за маркер автозаполнения формулу вниз до конца таблицы. Теперь необходимо указать, какие из учеников должны быть отчислены. Для этого воспользуемся логической функцией ЕСЛИ:
|
|
· Сделайте активной ячейку, в которую следует поместить сообщение о возможном отчислении данного ученика. В нашем случае это будет первая ячейка в столбце На отчисление (здесь - F3). Щелкните на кнопке Вставка функции. Появится диалоговое окно Мастера функций.
· В поле Категория выберите Логические, а в поле Функция выберите функцию ЕСЛИ. Щелкните на кнопке ОК. При этом появляется диалоговое окно для задания аргументов функции ЕСЛИ (см. Рис. 13).
 |
Рис. 13
· В поле Логическое_выражение следует ввести условие, по значению которого либо будет, либо не будет выдаваться сообщение об отчислении. В данном случае будет сравниваться значение ячейки, содержащей средний балл, с числом 3, т.е. условие имеет вид: E3<3. Введите его в поле. Если значение в E3 окажется меньше трех, то ячейка, в которую сейчас вводится функция (у нас это F3), примет текстовое значение «Отчислить». Иначе - ячейка останется пустой. Чтобы этого добиться, заполните оставшиеся поля:
· В поле Значение_если_истина следует ввести значение, которое возвращается (будет результатом всей функции), если логическое выражение имеет значение истина. В нашем случае это текст «Отчислить».
· В поле Значение_если_ложь следует ввести значение, которое возвращается, если логическое выражение имеет значение ложь. В нашем случае это будет пустая текстовая константа, состоящая из пробела (в кавычках), т.к. мы хотим, чтобы в этом случае ячейка оставалась пустой. Введите поэтому строку “ “. Нажмите ОК.
· Теперь в ячейке F2 находится результат вычислений. Саму же формулу можно увидеть в строке формул. Там же ее можно при желании отредактировать.
· Протащите формулу за маркер автозаполнения вниз до конца таблицы. Результат представлен на рис.14. Формулы раскрыты на рис. 15.
Рис. 14 Рис. 15
Функция ЕСЛИ является весьма полезным инструментом, позволяющим выбрать один из вариантов вычисления в зависимости от выполнения (истинности) в данный момент некоторого условия. В общем случае Функция ЕСЛИ имеет синтаксис:
=ЕСЛИ(логическое_ выражение; значение_если_истина; значение_если_ложь)
Таким образом, эта функция имеет три аргумента: логическое условие, ветку, если это условие выполняется (истинно) и ветку, если оно не выполняется (ложно). Прочитать эту формулу можно так: если логическое_ выражение истинно, то вычислить значение_если_истина, иначе вычислить значение_если_ложь.
Все выражение набирается без пробелов. Например, формула =ЕСЛИ(А6<22;5;10) возвратит (выдаст в качестве результата) число 5, если значение в ячейке А6 меньше 22. В противном случае она возвращает 10.
Можно использовать другие функции в качестве аргументов функции ЕСЛИ. Например: =ЕСЛИ(СУММ(А1:А10)>0;СУММ(А1:А10);0) возвратит сумму значений в ячейках от А1 до А10, если она больше нуля. В противном случае она возвратит 0.
В этой функции можно использовать текстовые аргументы, как в приведенном выше задании.
Логическое выражение может быть сложным, содержащим дополнительные логические функции И, ИЛИ, НЕ. Функции И, ИЛИ могут иметь до 30-ти аргументов логического типа (т.е. аргументов, значения которых есть истина или есть ложь), перечисляемых через точку с запятой (;). Функция НЕ имеет один логический аргумент).
Например, пусть в ячейке G4 содержится количество пропусков занятий. Будем считать, что студент сдал сессию, если у него меньше 5-ти пропусков и средний балл более 2,9. Тогда функция примет вид: =ЕСЛИ(И(G4<5;E4>2,9); "Сдал"; "Не сдал").
Вложенные функции ЕСЛИ. Пусть в ячейке А1 содержится целое число. Запишем в виде формулы выражение: «Если значение в ячейке А1 равно 100, сообщить (возвратить строку) Всегда. В противном случае, если значение в ячейке А1 находится между 80 и 100, сообщить Обычно. В противном случае, если значение А1 находится между 60 и 80 (от 60 до 79), сообщить Иногда. И, наконец, если ни одно из условий не выполняется, возвратить строку Увы! Соответствующая этой процедуре формула: =ЕСЛИ(А1=100; "Всегда"; ЕСЛИ(И(А1>=80;А1<100); "Обычно"; ЕСЛИ(И(А1>=60; А1<80); "Иногда"; "Увы!"))) Можно использовать и более трех уровней вложения. Обратите внимание, что новая (вложенная) функция ЕСЛИ записывается в аргумент, соответствующий ветке Иначе (когда условие ложно).
При вводе функции ЕСЛИ удобно пользоваться мастером функций, как рассмотрено в приведенном выше задании. Если формула содержит вложенную функцию ЕСЛИ, то после перехода в поле Значение_если_ложь (щелчка мышью в этом поле) нужно снова войти в Мастер функций и выбрать функцию ЕСЛИ, затем заполнить очередное (новое) окно аргументов функции ЕСЛИ.
Можно не пользоваться Мастером функций и ввести эту функцию вручную, разделитель аргументов - точку с запятой нужно тогда также набрать.