Задания для практических работ Microsoft Excel 4 Анализ данных с использованием логических функций в среде ms excel
 Скачать 0.63 Mb.
|
|
Задания для практических работ Microsoft Excel №4 Анализ данных с использованием логических функций в среде MS Excel Цель работы - знакомство с логическими функциями, которые часто используются для сравнения отдельных данных или для анализа данных с использованием условий. Задание 1. Ознакомьтесь с теоретическими сведениями о логических функциях. Часто выбор формулы для вычислений зависит от каких-либо условий. Например, при расчете торговой скидки могут использоваться различные формулы в зависимости от размера покупки. Для выполнения таких вычислений используется функция ЕСЛИ, в которой в качестве аргументов значений вставляются соответствующие формулы. Синтаксис функции: ЕСЛИ(А;В;С), где A – логическое выражение, правильность которого следует проверить; В– значение, если логическое выражение истинно; C – значение, если логическое выражение ложно Следующая формула возвращает значение 10, если значение в ячейке А1 больше 3, а в противном случае – 20: =ЕСЛИ(А1>3;10;20). Действие функции: функция ЕСЛИ, записанная в ячейку таблицы, выполняется следующим образом: если условие А истинно, то значение данной ячейки определит В, в противном случае С. 51 В и С могут быть числами, текстами или формулами. В функции ЕСЛИ можно использовать текстовые аргументы: =ЕСЛИ(А1>=4;"Зачет сдал";"Зачет не сдал"). В качестве аргументов функции ЕСЛИ можно использовать другие функции. Например, =ЕСЛИ(СУММ(А1:А3)=30;А10;""), здесь при невыполнении условия функция возвращает пустую строку вместо 0. Аргумент A (логическое выражение функции ЕСЛИ) может содержать текстовое значение. Например, =ЕСЛИ(А1="Динамо";10;290). Эта формула возвращает значение 10, если ячейка А1 содержит строку "Динамо", и 290, если в ней находится любое другое значение. Совпадение между сравниваемыми текстовыми значениями должно быть точным. Логические выражения строятся с помощью операций отношения (<, >, <= (меньше или равно), >= (больше или равно), =, <> (не равно)) и логических операций (логическое И, логическое ИЛИ, логическое отрицание НЕ). Результатом вычисления логического выражения являются логические значения ИСТИНА или ЛОЖЬ. Функции И и ИЛИ могут иметь до 30 логических аргументов и имеют синтаксис: =И(логическое_значение1;логическое_значение2...) =ИЛИ(логическое_значение1;логическое_значение2...) Функция НЕ имеет только один аргумент и следующий синтаксис: =НЕ(логическое_значение) Аргументы функций И, ИЛИ, НЕ могут быть логическими выражениями, массивами или ссылками на ячейки, содержащие логические значения. Иногда бывает очень трудно решить логическую задачу только с помощью операторов сравнения и функций И, ИЛИ, НЕ. В этих случаях можно использовать вложенные функции ЕСЛИ. Всего допускается до 7 52 уровней вложения функций ЕСЛИ. Например, в следующей формуле используются три функции ЕСЛИ: =ЕСЛИ(А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 – диапазон вычисляемых ячеек; В – критерий в форме числа, выражения или текста, определяющего суммируемые ячейки; С – фактические ячейки для суммирования. В тех случаях, когда диапазон вычисляемых ячеек и диапазон фактических ячеек для суммирования совпадают, аргумент С можно не указывать. Можно суммировать значения, отвечающие заданному условию. Напри-мер, в таблице на рис. 1 суммированы только продажи по фирмам, средняя цена продукции у которых не меньше 20 тыс. руб. Рис. 1 . Выборочное суммирование Функция СЧЕТЕСЛИ(А;В), подсчитывает в диапазоне A количество значений, удовлетворяющих критерию В. Функции СУММЕСЛИМН и СЧЕТЕСЛИМН работают аналогично классическим функциям СУММЕСЛИ и СЧЕТЕСЛИ, но умеют проверять не одно, а несколько условий (до 128 условий). Функция СРЗНАЧЕСЛИМН аналогична двум предыдущим, но считает не сумму, а среднее арифметическое. Функция ЕСЛИОШИБКА возвращает заданное вами значение или сообщение, если результатом вычисления формулы является значение ошибки, иначе возвращает результат вычисления формулы: Рис. 2. Проверка вычислений с помощью функции ЕСЛИОШИБКА. Задание 2. Выполнить выборочное суммирование по двум критериям. Имеем таблицу по продажам следующего вида: Рис. 3. Исходные данные. Требуется просуммировать все заказы, которые менеджер Григорьев реализовал для магазина "Копейка". Решение: Если бы в задаче было только одно условие (все заказы Григорьева или все заказы в "Копейку"), то задача решалась бы при помощи встроенной функции Excel СУММЕСЛИ. Но в данном случае имеются два условия, а не одно, поэтому этот способ не подходит. Добавим к таблице еще один столбец, который будет служить своеобразным индикатором: если заказ был в "Копейку" и от Григорьева, то в ячейке этого столбца будет значение 1 иначе 0. Формула, которую надо ввести в этот столбец очень простая: =(A2="Копейка")*(B2="Григорьев") Логические равенства в скобках дают значения ИСТИНА или ЛОЖЬ, что для Excel равносильно 1 и 0. Таким образом, в результате умножения двух выражений, единица в конечном счете получится только если оба условия выполняются. Теперь стоимости продаж осталось умножить на значения получившегося столбца и просуммировать полученные суммы: Рис. 4. Выборочное суммирование по двум критериям. Задание 3. Создать таблицу по образцу и выполнить перечисленные ниже вычисления. 3.1 Создать таблицу по образцу. Продажи холодильников Модель Страна- изготовите ль Вес, кг Цена, $ Количество Stinol Россия 78 310 18 Sharp Таиланд 69 750 10 Samsung Южная Корея 56 450 13 Bosh Испания 52 419 17 LG Южная Корея 69 600 8 Daewoo Южная Корея 71 840 4 Electrolux Швеция 75 680 12 Whiripool США 80 790 9 Атлант Россия 76 300 25 Indezit Франция 81 420 14 Ariston Франция 59 415 10 DeLongy Италия 60 395 15 3.2. Добавьте столбец «Сумма» и подсчитайте сумму от продаж холодильников. Примените к числовым значениям денежный формат соответствующего наименования там, где это необходимо. 3.3. Подсчитать количество холодильников, произведенных каждой страной. (Данные оформить в виде новой таблицы). 3.4. Подсчитать общую сумму, полученную в результате продажи холодильников, произведенных каждой страной. 3.5. Если оборот больше 10000 $, то написать сообщение о предоставляемой скидке 2%. 3.6. Подсчитать число проданных холодильников, вес которых больше их среднего веса. 3.7. Добавьте столбцы «Новая цена» и «Новая сумма». В столбце «Новая цена» рассчитайте новую цену, используя функцию ЕСЛИ. Известно, что производители России и Южной Кореи снизили цены на 10%, а производители Франции и Испании подняли цены на 16% (единая формула вводится в первую ячейку столбца «Новая цена»). 3.8. Рассчитайте, какова будет новая сумма при том же количестве проданного товара. 3.9. Используя условный фильтр, отметьте в столбце «Вес» ячейки, значение которых больше 70, желтым фоном и жирным красным шрифтом, а ячейки, значение которых меньше или равно 60, синим фоном и жирным желтым шрифтом. |