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

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

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

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

 

Функция ЕСЛИ

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

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

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

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

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

 

Например.

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

 

	B	C	D	E
		Расходы	Доходы	Баланс
	ТД "Акатуй"
	ТД "Пятерочка"
	ТД "Яхтинг"
	…

 

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

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

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

 

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

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

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

 

	C	D	E	F
	Фамилия	Пол	Возраст	Статус
	Петрова	ж
	Кузнецов	м
	Степанова	ж
	Сидорова	ж
	….	….	….	….

 

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

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

 

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

 

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

 

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

 

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

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

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

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

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

Например.

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

 

	C	D	E	F
	Фамилия	Пол	Возраст	Статус
	Петрова	Ж		–
	Кузнецов	М		пенсионер
	Степанова	Ж		–
	Сидорова	Ж		пенсионер

 

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

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

 

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

 

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

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

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

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

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

Например.

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

 

	С	D	E	F
	Организация	Дата	Расходы	Доходы
	ТД Акатуй	12.12.2008
	ТД Пятерочка	12.12.2008
	ТД Яхтинг	12.12.2008
	ТД Акатуй	13.12.2008
	…	…	…	…

 

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

 

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

 

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

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

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

 

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

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

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

 

Например.

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

 

	C	D	E	F
	Фамилия	Отдел	Оклад	Дата рождения
	Васильев	Плановый
	Кузнецов	Производственный
	Кузьмина	Снабжения
	Петрова	Снабжения
	Сидорова	Снабжения
	Степанова	Снабжения

 

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

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

 

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

 

Примечания

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

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

 

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

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

 

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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