1.3. Вычисления с условиями

1.3.1. Общие сведения

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

Это, прежде всего, функции СУММА, МАКС, МИН, СРЕДЗНАЧ. Они известны со школьного курса и не требуют комментариев.

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

Функция ЕСЛИ

Позволяет производить вычисления по условию.

Имеет следующий формат:

ЕСЛИ(Условие;

Вычисления при соблюдении условия;

Вычисления при не соблюдении условия)

Например.

Пусть имеются сведения о расходах и доходах разных организаций следующего типа:

	B	C	D	E
4		Расходы	Доходы	Баланс
5	ТД "Акатуй"	1234	5432
6	ТД "Пятерочка"	2345	1234
7	ТД "Яхтинг"	4321	5432
8	…

В колонку «Баланс» необходимо вывести следующую информацию:

Если доходы превышают расходы, то в указанной колонке вывести “+”, иначе вывести “–“.

Для этого в ячейку Е5 вводится формула: = ЕСЛИ(D5>C5;”+”;”–‘), которая затем копируется на весь столбец Е.

Функции И, ИЛИ

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

Например, для данных:

	C	D	E	F
9	Фамилия	Пол	Возраст	Статус
10	Петрова	ж	48
11	Кузнецов	м	65
12	Степанова	ж	34
13	Сидорова	ж	56
14	….	….	….	….

требуется заполнить колонку «Статус» со значениями «пенсионер» или «не пенсионер».

Формально статус пенсионера определяется по условию:

Если ((Возраст>60) и (Пол = «м»)) или ((Возраст>55) и (Пол = «ж»)).

В синтаксисе Excel это записывается следующим образом (в ячейку F10):

=ЕСЛИ(ИЛИ(И(E10>60;D10="м");И(E10>55;D10="ж"));"пенсионер"; "–")

Затем данная формула копируется на весь столбец F.

Функция СЧЕТЕСЛИ

Подсчитывает количество данных, удовлетворяющих некоторому условию.

Имеет следующий формат:

СЧЕТЕСЛИ(Диапазон просмотра; Критерий)

Например.

Имеются следующие данные:

	C	D	E	F
9	Фамилия	Пол	Возраст	Статус
10	Петрова	Ж	48	–
11	Кузнецов	М	65	пенсионер
12	Степанова	Ж	34	–
13	Сидорова	Ж	56	пенсионер

Необходимо подсчитать количество пенсионеров.

Для этого, например, в ячейку D15 вводим формулу:

=СЧЁТЕСЛИ(F10:F13;"пенсионер")

Функция СУММЕСЛИ

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

Общий формат:

СУММЕСЛИ(Диапазон просмотра;

Критерий поиска; Диапазон суммирования)

Например.

Для данных приведенных в таблице вычислить доходы торгового дома «Акатуй».

	С	D	E	F
3	Организация	Дата	Расходы	Доходы
4	ТД Акатуй	12.12.2008	1234	5432
5	ТД Пятерочка	12.12.2008	2345	1234
6	ТД Яхтинг	12.12.2008	4321	5432
7	ТД Акатуй	13.12.2008	3456	1234
8	…	…	…	…

Для этого, например, в ячейку С10 вводится формула:

=СУММЕСЛИ(C4:C7;"ТД Акатуй";F4:F7)

Функция ПРОСМОТР

Позволяет производить поиск информации по заданному критерию.

Общий формат:

ПРОСМОТР(Критерий поиска;

Диапазон поиска;

Диапазон результатов поиска)

Например.

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

	C	D	E	F
19	Фамилия	Отдел	Оклад	Дата рождения
20	Васильев	Плановый	10700	1967
21	Кузнецов	Производственный	9900	1986
22	Кузьмина	Снабжения	4400	1972
23	Петрова	Снабжения	6400	1981
24	Сидорова	Снабжения	12200	1960
25	Степанова	Снабжения	4300	1974

Необходимо найти оклад сотрудника Кузьминой.

Для этого, например, в ячейку D30 вводим формулу:

=ПРОСМОТР("Кузьмина";C20:C25;E20:E25)

Примечания

1. Для того чтобы функция работала корректно необходимо, чтобы данные были отсортированы по возрастанию в столбце поиска (в данном случае по фамилиям).

2. Если в столбце поиска имеется несколько записей, соответствующих критерию поиска, то функция находит первую из них.

1.3.2. Варианты заданий

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

1. По данным табл.1 подсчитать:

а) общий средний балл по факультету;

б) средние баллы по каждому предмету;

в) средние баллы для каждой группы;

г) определить группы с максимальным и минимальным средним баллом.

2. По данным табл. 1 подсчитать количество студентов

а) сдавших сессию только на отлично;

б) имеющих хотя бы одну двойку;

в) сдавших только на 4 и 5. Для этих же студентов подсчитать средний балл.

3. По данным табл. 1 подсчитать:

а) средние баллы по каждому курсу;

б) определить курсы с максимальным и минимальным средним баллом.

4. По данным табл. 2 подсчитать:

а) суммарную выручку от продаж;

б) найти товары, дающие максимальную и минимальную выручку;

в) найти товары, имеющие максимальный и минимальный спрос.

5. По данным табл. 2 подсчитать:

а) суммарную выручку по дням;

б) определить дни, в которые была получена минимальная и максимальная выручка.

6. Для данных табл. 2 получить рассчитать:

а) в табл. 2 ввести дополнительный столбец, содержащий «+», если доходы были больше расходов, и «–», если доходы были меньше расходов.

б) получить данные о расходах и доходах по дням.

7. По данным табл. 3 подсчитать:

а) общий объем выдач и объем выдач литературы по отделам библиотеки;

б) определить наиболее и наименее читаемых авторов.

8. По данным табл. 3 определить:

а) общий объем выдач литературы по дням;

б) определить дни с максимальным и минимальным объемом выдач.

9. По данным табл. 4 рассчитать:

а) общую выручку от оказанных услуг;

б) объем выручки по каждой услуге;

в) определить услуги, дающие наибольшую и наименьшую выручку.

в) определить услуги, имеющие наибольший и наименьший спрос.

10. По данным табл. 4 определить:

а) выручку по дням;

б) определить дни с максимальной и минимальной выручкой.

11. Для данных табл. 4. получить следующие данные:

а) ввести дополнительный столбец, в котором указывается «+», если объем оплаты наличными превосходит объем оплаты по кредитной карточке. Иначе в этот столбец должен выводится знак «–»;

б) определить общие суммы выручки по виду оплаты.

12. Скользящий график

В цехе с непрерывным производством работают пять человек по скользящему графику. Одна смена продолжается 6 часов: первая – с 00 до 6.00, вторая – с 6.00 до 12.00, третья – с 12.00 до 18.00 и четвертая – с 18.00 до 24.00. При этом первая смена считается ночной. Имеется табель выходов на работу следующего вида:

День недели	ВТ				СР				..	..
День месяца	1				2				..	..
Смена	1	2	3	4	1	2	3	4
Иванов	6					6			..	..
Петров		6					6		..	..
Сидоров			6					6	..	..
Кузнецов				6					..	..
Алексеев					6				..	..

Имеются также данные о почасовой ставке каждого рабочего:

Иванов – 15 руб/час; Петров – 12 руб/час; Сидоров – 15 руб/час; Кузнецов – 14 руб/час; Алексеев – 14 руб/час.

За работу в ночную смену добавляется 25% к основной ставке. За работу по субботам и воскресеньям также добавляется по 25%.

Вычислить заработную плату рабочих за март месяц.

Таблица 1

Итоги сессии

Фамилия	Курс	Группа	Экономика	Математика	Физика	Философия
Иванов	1	АО–А	5	3	2	4
Петров	1	НО–А	4	4	3	4
Сидоров	2	НО–А	3	5	4	3
Васильев	2	ФО–А	4	3	4	3
Кузьмин	3	ФО–А	5	4	5	5
Кузнецов	1	АО–А	5	3	2	4
Алексеева	1	НО–А	4	4	3	4
Андреева	2	НО–А	3	5	4	3
Васильева	2	ФО–А	4	3	4	3
Кузьмина	3	ФО–А	5	4	5	5
Иванова	1	АО–А	5	3	2	4
Петрова	1	НО–А	4	4	3	4
Сидорова	2	НО–А	3	5	4	3
Васильева	2	ФО–А	4	3	4	3
Горина	3	ФО–А	5	4	5	5
…	…	…	…	…	…	…

Таблица 2

Данные о поступлении и продажах товаров

Дата	Товар	Получено, шт.	Продано, шт.	Закупочная цена, руб./шт.	Продажная цена, руб./шт.
12.01.05	Валенки	33	22	123	154
12.01.05	Лапти	44	65	321	432
12.01.05	Галоши	22	22	213	265
13.01.05	Валенки	33	22	123	154
13.01.05	Лапти	44	65	321	432
13.01.05	Галоши	22	22	213	265
14.01.05	Валенки	33	22	123	154
14.01.05	Лапти	44	65	321	432
14.01.05	Галоши	22	22	213	265
15.01.05	Валенки	33	22	123	154
15.01.05	Лапти	44	65	321	432
15.01.05	Галоши	22	22	213	265
16.01.05	Валенки	33	22	123	154
16.01.05	Лапти	44	65	321	432
16.01.05	Галоши	22	22	213	265
…	…	…	…	…	…

Таблица 3

Данные о работе различных отделов библиотеки.

Дата	Автор	Абонемент	Читальный зал	Книгохранение
18.12.04	Пушкин	8	53	2
18.12.04	Толстой	4	24	0
18.12.04	Лермонтов	7	35	4
18.12.04	Достоевский	1	23	0
18.12.04	Гоголь	2	14	0
19.12.04	Пушкин	…	…	…
19.12.04	Толстой
19.12.04	Пушкин	8	53	2
19.12.04	Толстой	4	24	0
19.12.04	Лермонтов	7	35	4
19.12.04	Достоевский	1	23	0
19.12.04	Гоголь	2	14	0
20.12.04	Пушкин	…	…	…
20.12.04	Толстой
20.12.04	Пушкин	8	53	2
20.12.04	Толстой	4	24	0
20.12.04	Лермонтов	7	35	4
20.12.04	Достоевский	1	23	0
20.12.04	Гоголь	2	14	0
21.12.04	Пушкин	…	…	…
21.12.04	Толстой

Таблица 4

Данные по итогам ежедневной работы автосервиса

Дата	Услуга	Стоимость услуги, руб.	Количество по видам оплаты
			Наличными	Кредитная карточка
18.12.04	Шиномонтаж	765	3	1
18.12.04	Балансировка	820	4	2
18.12.04	Развал	510	5	5
18.12.04	Расхождение	430	8	11
18.12.04	Мойка	250	14	8
18.12.04	Окраска	3500	2	1
19.12.04	Шиномонтаж	765	3	1
19.12.04	Балансировка	820	4	2
19.12.04	Развал	510	5	5
19.12.04	Расхождение	430	8	11
19.12.04	Мойка	250	14	8
19.12.04	Окраска	3500	2	1
20.12.04	Шиномонтаж	765	3	1
20.12.04	Балансировка	820	4	2
20.12.04	Развал	510	5	5
20.12.04	Расхождение	430	8	11
20.12.04	Мойка	250	14	8
20.12.04	Окраска	3500	2	1
21.12.04	Шиномонтаж	765	3	1
21.12.04	Балансировка	820	4	2
21.12.04	Развал	510	5	5
21.12.04	Расхождение	430	8	11
21.12.04	Мойка	250	14	8
21.12.04	Окраска	3500	2	1
…	…	…	…	…