Работа с логическими функциями

Рассмотрим работу с функциями на примере логических функций.При решении некоторых задач значение ячейки необходимо вычислять одним из нескольких способов – в зависимости от выполнения или невыполнения одного или нескольких условий.

Для решения таких задач применяют условную функцию ЕСЛИ:

ЕСЛИ(<логическое выражение>, <выражение1>, < выражение2>).

Если логическое выражение имеет значение «Истина», ЕСЛИ принимает значение выражения 1, а если «Ложь» – значение выражения 2. В качестве выражения 1 или выражения 2 можно записать вложенную функцию ЕСЛИ. Число вложенных функций ЕСЛИ не должно превышать семи. Например, если в какой-либо ячейке будет записана функция ЕСЛИ(C5=1,D5*E5,D5-E5)), то при С5=1 функция будет иметь значение «Истина» и текущая ячейка примет значение D5*E5, если С5=1 будет иметь значение «Ложь», то значением функции будет D5-E5.

Если условий много, записывать вложенные функции ЕСЛИ становится неудобно. В этом случае на месте логического выражения можно указать одну из двух логических функций: И (и) или ИЛИ (или).

Формат функций одинаков:

И (<логическое выражение1>,<логическое выражение2>,..),

ИЛИ (<логическое выражение1>,<логическое выражение2>,..).

Функция И принимает значение «Истина», если одновременно истинны все логические выражения, указанные в качестве аргументов этой функции. В остальных случаях Значение И – «Ложь». В скобках можно указать до 30 логических выражений.

Функция ИЛИ принимает значение «Истина», если истинно хотя бы одно из логических выражений, указанных в качестве аргументов этой функции. В остальных случаях значение ИЛИ – «Ложь».

Пример

Рассмотрим, как работают логические функции, на примере.

Создадим таблицу с заголовком «Результаты экзаменов»:

№	Фамилия абитуриента	Набранный балл	Результаты зачисления
	Иванов П.П.	21,5	«зачислен»
	Петров И.И.	18,0	«не зачислен»

Значение последнего столбца может меняться в зависимости от значения набранного бала. Пусть при набранном балле 21 абитуриент считается зачисленным, при меньшем значении – нет. Тогда формула для занесения в последний столбец выглядит следующим образом:

= ЕСЛИ (С2< 21, «не зачислен», «зачислен»)

Задание к выполнению лабораторной работы:

1. В папке Мои документы создайте папку 1Инф (если она не создана).

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

3. Выполните следующие задания, каждое на отдельном листе рабочей книги.

Задание №1

1. Переименуйте Лист 1 вПолитическая карта Украины.

2. Создайте таблицу "Политическая карта Украины" (см. ниже)

3. Добавьте:

а) строку – после «шапки» таблицы

б) два столбца – между вторым и третьим столбцами

Преобразуйте таблицу к виду представленому ниже и заполните столбец «Городское», для нахождения значений в столице «Сельское» используйте соответствующую формулу. Заполните строку «Всего» используя функцию СУММ.

4. Преобразуйте таблицу к виду представленному ниже.

Используя соответствующие формулы заполните пустые столбцы.

5. Дополните таблицу еще одним столбцом – Статус области. В этом столбце в зависимости от соотношения в области сельского и городского населения будет записываться: сельскохозяйственная или промышленная. Если сельское население составляет больше ½ всего населения, то область – сельскохозяйственная (и наоборот)

6. Подсчитайте количество промышленных и сельскохозяйственных областей. Для этого испльзуйте функцию СЧЕТЕСЛИ. Итоговая таблица должна иметь вид представленный ниже.

Задание №2

7. Переименуйте Лист 2 вРасчет доходов предприятия и заполните следующие таблицы.

8. В ячейку С4 введите формулу для вычисления дохода от продажи компьютеров в долларах в январе месяце. Используйте для вычисления значение курса доллара в январе из нижней (справочной) таблицы (
в дол. США= в грн./курс доллара). Запишите в тетрадь формулу которую Вы использовали для расчета в таблице.

9. Дополните таблицу столбцом « Премия за квартал». Премия начисляется в размере 5% если Общий доход в гривнах больше 300000 грн., в противном случае премия – 0%.

Задание № 3

Оформите прайс-лист на товары в зависимости от курса доллара на сегодняшний день.

1. Подготовьте таблицу, состоящую из столбцов: «Наименование товара», «Цена в $USA», «Цена в грн.». Заполните все столбцы, кроме последнего С.

Прайс-лист	Дата:
Курс доллара:
Наименование товара	Цена в $ USA	Цена в грн.
Компьютер Pentium 233MHz	309,70
Компьютер Pentium II 400MHz	480,20
Компьютер Pentium III 500MHz	608,50
Принтер матричный Epson LQ 670	376,66
Принтер струйный Epson Stylus 900C	120,08
Сканер HP ScanJet 5100С	122,24
Картридж HP DJ 720 цветной	30,60
Тонер HP LJ 5L	5,76
Мышь NET Mouse Pro 3x	6,80

2. Присвойте ячейке содержащей курс доллара имя «Курс доллара».

3. Расчет ведите исходя из следующего:

Цена в грн.=Цена в $ USА*Курс доллара.

4. Установите дату, которая будет изменяться автоматически. Для этого используйте функцию СЕГОДНЯ из категории Дата и время .

Задание № 4

Создайте таблицу "Оплата коммунальных услуг"

Вид оплаты	Ед. изм.	Тариф, грн.	Величина (общая площадь, показания счетчика и т.д.)	Начисленная сумма, грн.	Задержка платежа, дн.	Пеня за день просрочки (в % к начисленной сумме)	Сумма пени, грн.	Всего к оплате, грн.
Квартплата (общая площадь)	м2	0.73
Газ	м3	0.176
Вода	м3	0.123
Электричество	кВт	0.156
Телефон	мин.	0.06

Установите для ячеек, содержащих суммы, денежный формат числа.

4..1. Городская семья из нескольких человек проживает в трёхкомнатной квартире. Требуется представить в виде таблицы распределение всей площади квартиры по её отдельным составляющим (комнаты, кухня, коридор, подсобные помещения), а также общую и жилую площадь, суммарно и в пересчёте на одного жильца.

Указания к исполнению.

Ø Разработать вид необходимой таблицы в начале в тетради.

Ø Считать все помещения в квартире имеющими прямоугольную форму.

Ø Длину и ширину каждого помещения выражать в метрах, а площадь – в квадратных метрах.

Ø Точность для длины и ширины – два знака после запятой, для площади – один знак.

4.2. В той же семье трудоспособные члены имеют какой-то оклад по своему основному месту работы, с которого причитается соответствующий подоходный и пенсионный налоги. Представить все эти сведения в виде таблицы, и рассчитать суммарный месячный доход семьи, а также доход, приходящийся в среднем на каждого её члена и на каждого её работника.

Указания к исполнению.

Ø Разработать вид необходимой таблицы в начале в тетради.

Ø Ввести столбец в котором указывать статус жильца – работающий, пенсионер

Ø Подоходный налог принять равным 12 %, а пенсионный – 1 % от оклада.

Ø Пенсию работающих пенсионеров приплюсовывать к их окладу за вычетом из него налога.

Ø

Ø Пенсию неработающих пенсионеров приплюсовывать к суммарному доходу.

Ø Все денежные показатели, в том числе и средние, приводить в гривнах с копейками.

Задание 5.

В школе проводится шахматный турнир, в котором участвуют 12 лучших игроков из разных классов. Соревнование организовано по круговой системе: каждый играет с каждым по одному разу. Результаты встреч заносятся в обычную турнирную таблицу – с диагональю заштрихованных клеточек. В остальные клетки вносятся единицы за победы, нули за поражения и половинки за ничью. Все очки на текущий день подсчитываются и выводятся в итоговый столбец. Лидеры турнира (если их, как это часто бывает, несколько) должны быть в таблице выделены.

Указания к исполнению

1. При вводе результатов игр исключить, во избежание случайных ошибок, избыточность данных, т.е. нули, единицы и половинки вводить только в верхнюю часть таблицы, над диагональю. В нижнюю часть, под диагональ, соответствующие показатели должны заноситься автоматически;

2. Учесть при оформлении таблицы, что она вывешивается на всеобщее обозрение и поэтому должна быть легко читаемой;

3. Под таблицей указать возрастной диапазон участников.

Задание 6.

В небольшом предприятии его сотрудникам выдаётся месячная зарплата. Кассир, имея на руках бухгалтерскую ведомость, собирается ехать в банк за деньгами. При этом он имеет возможность не просто подать заявку на общую сумму, а указать в ней также, какими именно купюрами ему хотелось бы получить её. Делается это для того, чтобы потом было удобнее рассчитываться с каждым отдельным работником (например, если выплаты производится, как говорят, «в конвертах»).

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

Указания к исполнению

1. При определении набора следовать принципу наименьшего числа купюр.

2. Ради упрощения задачи расчёт вести только на бумажные деньги.

3. Для расчётов использовать встроенные функции ЦЕЛОЕ и ОСТАТ из категории «математических».

Задание 7

Создайте таблицу для расчета заработной платы:

№ п/п	Фамилия, имя, отчество	Доход за год, грн.	Среднемесячный доход, грн	Налог, грн.	Доплата, переплата, грн. (+; -)
уплаченный	начисленный

Указания к исполнению

1. Первые 3 столбца заполняются в свободной форме.

2. Графа "Среднемесячный доход" заполняется по следующему правилу:

Среднемесячный доход = Доход за год / 12.

3. Графа "Налог уплаченный" заполняется по факту уплаченного налога (размер налога проставьте сами).

4. Графа "Налог начисленный" заполняется следующему правилу:

Налог начисленный = Среднемесячный налог х 12.

среднемесячный налог рассчитываем исходя из следующей шкалы:

Среднемесячный доход, грн.	Ставки и размеры налога
до 17	налог не берется
18 – 85	10 % от å > 17 грн.
86 – 170	6,8 грн. + 15 % от å >85 грн.
171 – 1020	19,55 грн. + 20 % от å >170 грн.
1021 – 1700	189,55 грн. + 30 % от å >1020 грн.
>1701	313,55 грн. + 40 % от å >1700 грн.

5. Графа «Доплата, переплата» заполняется следующим образом:

q находим разность между графами «Налог начисленный» и «Налог уплаченный» с учетом знака;

q знак «-» перед числом в данной графе указывает на то, что с налогоплательщика взяли больше налога чем полагалось и эту «разность» ему обязаны вернуть, знак «+» говорит о том, что налогоплательщику не доначислили указанную сумму налога и он обязан внести не доначисленную сумму налога.

6. На основе полученных данных создайте таблицу следующего вида, указав для каждого работника сумму доплаты или возврата соответственно:

№ п/п	Фамилия, имя, отчество	Сумма доплаты, грн.	Сумма возврата, грн.

4. Сохраните таблицы в своей папке ФИО_номер_работы.xls

5. Завершите работу с Microsoft Excel.

6. Перепишите сохраненный файл на съемный носитель.