Задание 2.
1. Создать новую книгу. Сохранить ее на диске в своей папке с именем «Ассортимент».
2. На первом листе книги набрать данные таблицы 1, приведенной ниже
Указание: При вводе данных используйте Выпадающие списки (Один щелчок правой кнопкой мыши по пустой ячейке под столбцом с данными, команда контекстного меню Выбрать из раскрывающегося списка или нажать сочетание клавиш Alt+стрелка вниз).
3. На втором листе текущей рабочей книги создать таблицу 2:
4. Листам дать название «Ксероксы» и «Факсы» соответственно.
5. На листах «Ксероксы» и «Факсы» в столбиках «Цена», используя абсолютную ссылку, создать формулу для вычисления Цены, при условии, что она на 30% больше стоимости.
6. На листах «Ксероксы» и «Факсы» в столбцах «Сумма» создать формулу =количество*цена;
7. На листах «Ксероксы» и «Факсы» отформатировать таблицы:
шрифт – Times New Roman; размер 13; задать границы – тонкая пунктирная линия;
в столбцах «Товар», «Модель», «Название» установить выравнивание по левому краю;
в столбцах «Стоимость», «Цена», «Количество», «Сумма» установить выравнивание по центру;
в ячейках, содержащих денежные суммы, установить денежный формат.
8. Над таблицами добавить строку, в столбце А1 набрать «Курс $», в ячейке В1
набрать текущий курс доллара и установить денежный фор-мат. Присвоить этой ячейке имя «Курс_$».
9. к таблицам добавить столбец с названиями «Стоимость в у.е.», где создать формулу с абсолютной ссылкой =D3/Курс_$.
10. В Столбце «Стоимость в у.е.» установить форматирование как в предыдущих столбцах, воспользовавшись кнопкой «Формат по образцу».
11. Над таблицами добавить три строки в первой строке объединить столбцы, которые занимает ваша таблица, и набрать в объединенной ячейке «Ассортимент ксероксов фирмы «Флагман», «Ассортимент факсов фирмы «Флагман».
12. Используя «Буфер обмена», скопировать обе таблицы на третий лист текущей книги и назвать его «Полный ассортимент».
13. На листе «Полный ассортимент», используя Условное форматирование, выделить те суммы, величина которых от 6000 руб. до 450$.
14. В ячейке E2 округлите указанную там цену товара до ближайшего целого. Скопируйте формулу на ячейки Е3:Е22.
15. В основной таблице под столбиками «Цена», «Количество», «Сумма» найти: минимальный элемент, максимальный элемент и среднее значение.
16. Вычислите итоговое значение столбцов Количество и Сумма.
17. Сохраните файл под именем «Ксероксы и факсы».
Лабораторная работа 3. Логические выражения и функции Excel 2010. Условные вычисления.
Цель работы:
1. Знакомство с логическими функциями, которые часто используются для сравнения отдельных данных или выражений при решении финансово- экономических задач.
2. Знакомство с дополнительными функциями, которые можно применять для анализа данных с использованием условий. Например, для вычисления суммы значений, попадающих в интервал, заданный текстовой строкой или числами, используется функцию СУММЕСЛИ. А для подсчета количества ячеек внутри диапазона, удовлетворяющих заданному критерию используется СЧЁТЕСЛИ.
Часто выбор формулы для вычислений зависит от каких-либо условий. Например, при расчете торговой скидки могут использоваться различные формулы в зависимости от размера покупки. Для выполнения таких вычислений используется функция ЕСЛИ, в которой в качестве аргументов значений вставляются соответствующие формулы. Синтаксис функции: ЕСЛИ(А;В;С), где A – логическое выражение, правильность которого следует проверить; В– значение, если логическое выражение истинно; C – значение, если логическое выражение ложно. Следующая формула возвращает значение 10, если значение в ячейке А1 больше 3, а в противном случае – 20: =ЕСЛИ(А1>3;10;20). Действие функции: функция ЕСЛИ, записанная в ячейку таблицы, выполняется следующим образом: если условие А истинно, то значение данной ячейки определит В, в противном случае С.
В и С могут быть числами, текстами или формулами. В функции ЕСЛИ можно использовать текстовые аргументы: =ЕСЛИ(А1>=4;"Зачет сдал";"Зачет не сдал"). В качестве аргументов функции ЕСЛИ можно использовать другие функции. Например, =ЕСЛИ(СУММ(А1:А3)=30;А10;""), здесь при невыполнении условия функция возвращает пустую строку вместо 0. Аргумент A (логическое выражение функции ЕСЛИ) может содержать текстовое значение. Например, =ЕСЛИ(А1="Динамо";10;290). Эта формула возвращает значение 10, если ячейка А1 содержит строку "Динамо", и 290, если в ней находится любое другое значение. Совпадение между сравниваемыми текстовыми значениями должно быть точным. Логические выражения строятся с помощью операций отношения (<, >, <= (меньше или равно), >= (больше или равно), =, <> (не равно)) и логических операций (логическое И, логическое ИЛИ, логическое отрицание НЕ). Результатом вычисления логического выражения являются логические значения ИСТИНА или ЛОЖЬ.
Функции И и ИЛИ могут иметь до 30 логических аргументов и имеют синтаксис:=И(логическое_значение1;логическое_значение2...)=ИЛИ (логическое_значение1;логическое_значение2...) Функция НЕ имеет только один аргумент и следующий синтаксис: =НЕ(логическое_значение). Аргументы функций И, ИЛИ, НЕ могут быть логическими выражениями, массивами или ссылками на ячейки, содержащие логические значения.
Иногда бывает очень трудно решить логическую задачу только с помощью операторов сравнения и функций И, ИЛИ, НЕ. В этих случаях можно использовать вложенные функции ЕСЛИ. Всего допускается до 7 уровней вложения функций ЕСЛИ. Например, в следующей формуле ис-пользуются три функцииЕСЛИ:=ЕСЛИ(А1=100;"Всегда";ЕСЛИ(И(А1>=80;А1<100); "Обычно"; ЕСЛИ(И(А1>=60;А1<80);"Иногда";"Никогда"))) Если значение в ячейке А1 является целым числом, формула читается следующим образом: "Если значение в ячейке А1 равно 100, возвратить строку "Всегда". В противном случае, если значение в ячейке А1 находится между 80 и 100, возвратить "Обычно". В противном случае, если значение в ячейке А1 находится между 60 и 80, возвратить строку "Иногда". И, если ни одно из этих условий не выполняется, возвратить строку "Никогда".
Функции ИСТИНА (TRUE) и ЛОЖЬ (FALSE) предоставляют альтернативный способ записи логических значений ИСТИНА и ЛОЖЬ.
Эти функции не имеют аргументов и выглядят следующим образом: =ИСТИНА()
=ЛОЖЬ()
Например, ячейка А1 содержит логическое выражение. Тогда следующая функция возвратить значение "Выдать кредит", если выражение в ячейке А1 имеет значение ИСТИНА: =ЕСЛИ(А1=ИСТИНА();"Выдать кредит";"Не выдавать кредит"), в противном случае формула возвратит "Не выдавать кредит".
Если нужно определить, является ли ячейка пустой, можно использовать функцию ЕПУСТО (ISBLANK), которая имеет следующий синтаксис: =ЕПУСТО(значение), Аргумент значение может быть ссылкой на ячейку или диапазон. Если значение ссылается на пустую ячейку или диапазон, функция возвращает логическое значение ИСТИНА, в противном случае ЛОЖЬ.
Выборочное суммирование. Иногда необходимо суммировать не весь диапазон, а только ячейки, отвечающие некоторым условиям (критериям). В этом случае используют функцию СУММЕСЛИ(А;В;С), где A – диапазон вычисляемых ячеек; В – критерий в форме числа, выражения или текста, определяющего суммируемые ячейки; С – фактические ячейки для суммирования. В тех случаях, когда диапазон вычисляемых ячеек и диапазон фактических ячеек для суммирования совпадают, аргумент С можно не указывать. Можно суммировать значения, отвечающие заданному условию. Напри-мер, в таблице на рис. 3.1 суммированы только продажи по фирмам, средняя цена продукции у которых не меньше 20 тыс. руб.
Рисунок 3.1 . - Выборочное суммирование
Функция СЧЕТЕСЛИ(А;В), подсчитывает в диапазоне A количество значений, удовлетворяющих критерию В. Функции СУММЕСЛИМН и СЧЕТЕСЛИМН работают аналогично классическим функциям СУММЕСЛИ и СЧЕТЕСЛИ, но умеют проверять не одно, а несколько условий (до 128 условий). Функция СРЗНАЧЕСЛИМН аналогична двум предыдущим, но считает не сумму, а среднее арифметическое. Функция ЕСЛИОШИБКА возвращает заданное вами значение или сообщение, если результатом вычисления формулы является значение ошибки, иначе возвращает результат вычисления формулы:
Рисунок 4.2. - Проверка вычислений с помощью функции ЕСЛИОШИБКА.
Задание 1. Рассчитать подоходный налог на доходы физических лиц, если необлагаемая база для лиц, имеющих доход меньше 20000 рублей, равна 400 руб., в противном случае размер налога равен 13% от величины дохода. Решение.
Задание 2. Создать на листе Excel следующую таблицу:
Ответить на следующие вопросы:
1) Как изменится выручка, если курс акций 1 упадет на 2%, а курс акций 3 поднимется на 5%.
2) Как изменится выручка, если курс акций 1 упадет на 2%, а курс акций 2 и курс акций 3 поднимется на 5%.
3) Вычислить общую выручку по каждой ценной бумаге: «Акция 1», «Акция 2», «Акция 3». (Результаты оформить в виде новой таблицы).
Указание. Для суммирования ячеек по заданному условию используется функция СУММЕСЛИ, имеющая следующий формат: =СУММЕСЛИ(Диапазон; Критерий; Диапазон_суммирования).
Аргумент Диапазон – это интервал вычисляемых ячеек. Аргумент Критерий представляет собой число, выражение или текст, который определяет условия суммирования ячеек. Аргумент Диапазон_суммирования – это фактические ячейки для суммирования. Согласно данному выше описанию функции СУММЕСЛИ, выручка по ценной бумаге “Акция 1” может быть вычислена с помощью следующей формулы: =СУММЕСЛИ(А2:А6; “Акция 1”; B2:B6) или = СУММЕСЛИ(А2:А6; А4; B2:B6).
4) С помощью функции ранжирования расположить в порядке возрастания доходы от продажи акций. Результаты поместить в новом столбце.
5) Написать формулу, осуществляющую автоматическую вставку сообщения «лидер на рынке ценных бумаг» для того типа акций, который имеет наибольший рейтинг.
Задание 3. Выборочное суммирование по двум критериям. Имеем таблицу по продажам следующего вида:
Требуется просуммировать все заказы, которые менеджер Григорьев реализовал для магазина "Копейка".
Решение: Если бы в задаче было только одно условие (все заказы Григорьева или все заказы в "Копейку"), то задача решалась бы при помощи встроенной функции Excel СУММЕСЛИ. Но в данном случае имеются два условия, а не одно, поэтому этот способ не подходит.
Добавим к таблице еще один столбец, который будет служить своеобразным индикатором: если заказ был в "Копейку" и от Григорьева, то в ячейке этого столбца будет значение 1 иначе 0. Формула, которую надо ввести в этот столбец очень простая: =(A2="Копейка")*(B2="Григорьев") Логические равенства в скобках дают значения ИСТИНА или ЛОЖЬ, что для Excel равносильно 1 и 0. Таким образом, в результате умножения двух выражений, единица в конечном счете получится только если оба условия выполняются. Теперь стоимости продаж осталось умножить на значения получившегося столбца и просуммировать полученные суммы:
Рисунок 3.3. - Выборочное суммирование по двум критериям
Задание 4.
Создать таблицу по образцу: Продажи холодильников
2. Добавьте столбец «Сумма» и подсчитайте сумму от продаж холодильников. Примените к числовым значениям денежный формат соответствующего наименования там, где это необходимо.
3. Подсчитать количество холодильников, произведенных каждой страной. (Данные оформить в виде новой таблицы).
4. Подсчитать общую сумму, полученную в результате продажи холодильников, произведенных каждой страной.
5. Если оборот больше 10000 $, то написать сообщение о предоставляемой скидке 2%. 6. Подсчитать число проданных холодильников, вес которых больше их среднего веса.
7. Добавьте столбцы «Новая цена» и «Новая сумма». В столбце «Новая цена» рассчитайте новую цену, используя функцию ЕСЛИ. Известно, что производители России и Южной Кореи снизили цены на 10%, а производители Франции и Испании подняли цены на 16% (единая формула вводится в первую ячейку столбца «Новая цена»).
8. Рассчитайте, какова будет новая сумма при том же количестве про-данного товара.
9. Используя условный фильтр, отметьте в столбце «Вес» ячейки, значение которых больше 70, желтым фоном и жирным красным шрифтом, а ячейки, значение которых меньше или равно 60, синим фоном и жирным желтым шрифтом.
10. Сохраните файл под именем «Холодильники.xlsx».