Функции просмотра и ссылок. О функции просмотра и ссылок

Условные вычисления

Проверка и анализ данных

Для наглядного представления результатов анализа данных можно использовать функцию ЕСЛИ.

Синтаксис функции:

ЕСЛИ(А;В;С),

где A – логическое выражение, правильность которого следует проверить;

В– значение, если логическое выражение истинно;

C – значение, если логическое выражение ложно.

Например, в таблице на рис. 8.22 функция ЕСЛИ используется для проверки значений в ячейках В2:В12 по условию <0,6%. Если значение удовлетворяет условию, то функция принимает значение "ДА", а если значение не удовлетворяет условию, то функция принимает значение "нет".

Рис. 8.22. Проверка значений

Часто выбор формулы для вычислений зависит от каких-либо условий. Например, при расчете торговой скидки могут использоваться различные формулы в зависимости от размера покупки.

Для выполнения таких вычислений используется функция ЕСЛИ, в которой в качестве аргументов значений вставляются соответствующие формулы.

Например, в таблице на рис. 8.23 при расчете стоимости товара цена зависит от объема партии товара. При объеме партии более 30 цена понижается на 10%. Следовательно, при выполнении условия используется формула B:B*C:C*0,9, а при невыполнении условия – B:B*C:C.

Рис. 8.23. Условное вычисление

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

Поиск значений в таблице

Для поиска значения в крайнем левом столбце таблицы и соответствующего ему значения в той же строке из указанного столбца таблицы используют функцию ВПР.

Синтаксис функции:

ВПР(А;В;С;D),

где A – искомое значение.

В– таблица, в которой производится поиск. Может быть задана диапазоном ячеек или именем диапазона;

C – номер столбца таблицы, в котором должно быть найдено соответствующее значение;

D – логическое значение, которое определяет, нужно ли, чтобы функция искала точное или приближенное соответствие. Если этот аргумент имеет значение ИСТИНА или отсутствует, то находится приблизительно соответствующее значение. Если этот аргумент имеет значение ЛОЖЬ, то функция ищет точное соответствие. Если таковое не найдено, то возвращается значение ошибки #Н/Д.

Например, в таблице на рис. 8.24 необходимо найти товар, объем поставки которого задан в ячейке А11.

Рис. 8.24. Поиск значений в столбцах

Для поиска значения в верхней строке таблицы и соответствующего ему значения в том же столбце из указанной строки таблицы используют функцию ГПР.

Синтаксис функции:

ГПР(А;В;С;D),

где A – искомое значение.

В– таблица, в которой производится поиск. Может быть задана диапазоном ячеек или именем диапазона;

C – номер строки таблицы, в которой должно быть найдено соответствующее значение;

D – логическое значение, которое определяет, нужно ли, чтобы функция искала точное или приближенное соответствие. Если этот аргумент имеет значение ИСТИНА или отсутствует, то находится приблизительно соответствующее значение. Если этот аргумент имеет значение ЛОЖЬ, то функция ищет точное соответствие. Если таковое не найдено, то возвращается значение ошибки #Н/Д.

Например, в таблице на рис. 8.25 необходимо найти товар, объем поставки которого задан в ячейке А4.

Рис. 8.25. Поиск значений в строках

Упражнение 8

Запустите Microsoft Excel 2007.

Задание 1

1. Откройте файл exercise_08_1.xlsx.

2. Перейдите к листу Амортизация 1.

3. В ячейке В5 с использованием линейного метода при заданных условиях рассчитайте размер ежегодных амортизационных отчислений.

4. Перейдите к листу Амортизация 2.

5. В ячейке В6 с использованием метода "суммы чисел" при заданных условиях рассчитайте размер амортизационных отчислений для года эксплуатации, указанного в ячейке А6. Скопируйте формулу на ячейки В7:В12.

6. Перейдите к листу Амортизация 3.

7. В ячейке В6 с использованием метода двойного уменьшения остатка при заданных условиях рассчитайте размер амортизационных отчислений для года эксплуатации, указанного в ячейке А6. Скопируйте формулу на ячейки В7:В12.

8. Перейдите к листу Амортизация 4.

9. В ячейке В6 с использованием метода тройного уменьшения остатка при заданных условиях рассчитайте размер амортизационных отчислений для года эксплуатации, указанного в ячейке А6. Скопируйте формулу на ячейки В7:В12.

10. Перейдите к листу Вклад 1.

11. В ячейке В6 рассчитайте, какова будет итоговая величина вклада на 10 лет под 7 % годовых при начальном вкладе 15000 руб. и ежегодных вложения 20000 руб.

12. Перейдите к листу Вклад 2.

13. В ячейке В6 рассчитайте, какова будет итоговая величина вклада на 5 лет под 6 % годовых при начальном вкладе 20000 руб.

14. Перейдите к листу Инвестиция 1.

15. В ячейке В6 рассчитайте, какую сумму необходимо вложить сейчас под 7 % годовых, чтобы в течение 10 лет ежегодно получать 15000 руб.

16. Перейдите к листу Инвестиция 2.

17. В ячейке В6 рассчитайте, какую сумму необходимо вложить сейчас под 7 % годовых, чтобы через 5 лет получить 200000 руб.

18. Перейдите к листу Инвестиция 3.

19. В ячейке В6 рассчитайте, какую сумму необходимо ежемесячно вносить в течение 7 лет под 7 % годовых, чтобы через 7 лет получить 300000 руб.

20. Перейдите к листу Дата.

21. В ячейку В1 с использованием функций введите текущую дату. В ячейку В2 с использованием формулы введите дату и время последнего изменения данных на листе.

Задание 2

1. Откройте файл exercise_08_2.xlsx.

2. Перейдите к листу Лист 1.

3. Вставьте пустой столбец слева от столбца Шоколад. В новом столбце с использованием функций исправьте регистр текста в ячейках столбца Шоколад.

4. Перейдите к листу Лист 2.

5. В ячейке D2 с использованием функций соедините текст из ячеек А1, А2, В1 и В2 так, чтобы получилась фраза: Фирма Ирис – курирует менеджер Григорьев. Скопируйте формулу на ячейки D2:D6.

6. Перейдите к листу Лист 3.

7. В ячейке D2 с использованием функций создайте такую формулу, чтобы оплата определялась как произведение количества на цену, но при покупке более 50 единиц товара цена уменьшалась на 15%. Скопируйте формулу на ячейки D2:D22.

8. Перейдите к листу Лист 4.

9. В ячейке Е1 с использованием функций найдите название шоколада, объем партии которого указан в ячейке D1.

Сохраните файл exercise_08_1.xlsx под именем Lesson_08_1. Сохраните файл exercise_08_2.xlsx под именем Lesson_08_2.

Закройте Microsoft Excel 2007.

Дата добавления: 2014-01-05