Упражнение 1. Использование функции ВПР

Создайте две таблицы на разных листах EXCEL. Одна таблица – справочная (дайте листу название – «Прейскурант»). Она содержит цены за 1 день пребывания в стране:

Второй лист переименуйте – Путевки. На этом листе расположите таблицу:

Стоимость путевки будем искать по функции ВПР. Искомым значением будет – страна, в которую отправляется турист. Таблица, в которой мы будем искать значение, это таблица на листе «Прейскурант», выделять надо всю таблицу с заголовками и обязательно нажать клавишу F4, для того, чтобы создать абсолютную ссылку.

Устанавливаем курсор в ячейку С2 и вызываем Формулы, Ссылки и массивы, ВПР. В появившемся окне Задаем искомое значение – ячейку В2. Далее переходим к аргументу «Таблица». Переходим на лист «Прейскурант» и выделяем всю справочную таблицу вместе с заголовком. И нажимаем клавишу F4. Далее – аргумент «номер столбца» ставим цифру 2, т.к. во втором столбце аргумента «таблица» расположены цена за 1 день пребывания в стране. И последний аргумент «интервальный просмотр» пишем ложь, т.к. во-первых, нам необходимо найти точное соответствие искомому значению, и, во-вторых, таблица поиска не отсортирована по алфавиту. Вид окна функции ВПР должен быть:

Обратите внимание, что если все введено правильно – в окне функции уже виден результат поиска – число 12, что соответствует 1 дню пребывания в Китае. Нажимаем ОК.

Но мы нашли не стоимость путевки, а всего лишь стоимость 1 дня. Поэтому, находясь в строке формул полученное значение умножаем на количество дней:

Нам осталось всего лишь скопировать формулу вниз на нужное количество строк (используя маркер заполнения). Результаты стоимости путевок будут найдены для каждого клиента.

Упражнение 2. Создание связей между таблицами, находящимися на разных листах.

Ключ к заданию

· Тариф (стоимость проката за сутки) определяется длительностью срока. Базовой стоимостью является стоимость за сутки со сроком до 10 дней. При увеличении срока пользования вводится поправочный коэффициент (соответственно 0,9; 0,85 и 0,8).

· Таким образом, таблица «Тарифы» является базовой. Вводится стоимость проката за сутки при сроке до 10 дней, а затем вводятся формулы для расчета стоимостей в остальных столбцах.

· Таблица «Салон проката» - ведомость выдаваемых в прокат товаров. Стоимость проката зависит от срока, в течение которого товар будет находиться в пользовании.

· Сумма оплаты зависит от тарифа, определяемого сроком проката. Чтобы выбрать тариф, используют функцию ВПР, она позволяет по наименованию выбрать тариф. Чтобы определить, из какого столбца выбирается стоимость проката за сутки, используется функция ЕСЛИ. Вид формулы:

=ВПР(B3;Тариф!$A$3:$E$13;ЕСЛИ(E3<=10;2;ЕСЛИ(E3<=20;3;ЕСЛИ(E3>30;5;4))))*E3

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

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

Цель занятия: изучение информационной технологии организации расчетов с использованием встроенных функций в таблицах MS Excel.

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

Логические функции предназначены для проверки выполнения условия или несколько условий.

И – возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА, возвращает значение ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ.

Синтаксическая формула:

= И (логическое_значение1; логическое_значение2…)

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

§ Аргументы должны быть логическими значениями, массивами или ссылками, которые содержат логические значения.

ИЛИ – возвращает ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА, возвращает ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ.

Синтаксическая формула:

= ИЛИ(логическое_значение1;логическое_значение2;…)

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

ЕСЛИ – позволяет при вычислении значения ячейки проверить содержимое других ячеек и в зависимости от результата проверки задать те или иные дополнительные условия.

Синтаксическая формула:

= ЕСЛИ (лог_выражение; значение_если_истина; значение_если_ложь)

Пример: С помощью функции ЕСЛИ рассчитать в ячейке С2 премии сотрудников по следующим критериям: если выработка за 1 квартал превышает 1000 изделий, то работнику выплачивается премия 50 рублей за каждое изделие сверх 1000.