Использование встроенных функций в расчетах MS Excel

Практическая работа

«Использование встроенных функций в расчетах MS Excel.»

Цель работы. Закрепление  навыков использования функций в расчетных таблицах МS Excel .

Краткие теоретические сведения.

1.     Ввод формулы

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

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

Ссылки различают ОТНОСИТЕЛЬНЫЕ (А2, В5,  Е10) и АБСОЛЮТНЫЕ ($A$2, $В$5,  $Е$10).

Арифметические  операции

+	сложение	= А1 + 35
-	Вычитание	= Е10 – H4
*	Умножение	= F2 * E6
/	Деление	= 24 / L2
^	Возведение в степень	= 3^2
%	проценты	= B2*20%

В примере складывается значение ячейки B4 с числом 25. Полученный результат делится на сумму ячеек  из диапазона D5: F5.

Правила ввода:

1. Укажите ячейку, в которую необходимо ввести формулу.
2. Введите = (знак равенства).

Если нажать кнопку Изменить формулу или Вставка функции, автоматически вставляется знак равенства.

3. Введите формулу.
4. Нажмите клавишу ENTER.

2. Ввод функции

Для ввода функций используется Мастер функций ( fx ) вкладка функции

MS Excel позволяет работать  с логическими, математическими, статистическими, финансовыми  функциями,  ссылками и массивами и др. функциями. Функции могут быть использованы для выполнения простых или сложных вычислений. Самой часто используемой функцией является функция СУММ(), которая используется для сложения чисел в диапазоне ячеек.

Функция СУММ складывает значения в диапазоне D5:F5.

Функция ЕСЛИ  логическая

Возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ.

Функция ЕСЛИ используется при проверке условий для значений и формул.

Синтаксис       ЕСЛИ(лог_выражение; значение_если_истина; значение_если_ложь)

Например:

ЕСЛИ(A10> C12;"БОЛЕЕ"; "МЕНЕЕ") ® если число в ячейке А10 больше числа в ячейке С12 , то в данной ячейке появится текст "БОЛЕЕ», противном случае "МЕНЕЕ".

ЕСЛИ(A10=100; СУММ(B5:B15); " ") ® если значение в ячейке А10 равно 100, то в данной ячейке появится расчет суммы ячеек диапазона B5:B15, противном случае ячейка останется пустой.

ЕСЛИ(A10< O;"убытки"; "  ") ® если число в ячейке А10 меньше 0 (т. е. отрицательное),  то в данной ячейке появится «убытки» , противном случае ячейка останется пустой.

      Выполните расчеты в одном файле (РАСЧЕТЫ)  на разных листах.

Задание 1  Создать расчетную таблицу Реализация билетов в цирк.

Выполнить расчеты  в серых пустых клетках таблицы.

		Цена билетов:	Количество мест
Вокруг арены:		250р.	70
В первых рядах:		200р.	350
В задних рядах:		160р.	350
Курс доллара		50,00р.
			770
		Количество проданных билетов:
День недели	вокруг арены	первые	задние	Выручка в руб		Выручка в % от общей	Заполняемость (% )
вторник	68	108	250
среда	65	209	215
четверг	60	280	280
пятница	65	310	330
суббота	70	340	350
воскресенье	70	346	350
		Общая выручка (руб)				Средний %
		Общая выручка ($)

Задание 2.   Использование производных функций от ЕСЛИ > СЧЁТЕСЛИ, СУММЕСЛИ ….

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

Порядок выполнения:

I. Создайте две таблицы: с исходными данными и результатами.

1. В таблице с исходными данными должны быть поля: «№», «Наименование», «Поставщик», «Количество, л», «Цена», «Стоимость».
2. Заполните таблицу исходными данными, например:

	A	B	C	D	E	F
1	№	Наименование	Поставщик	Количество, л	Цена	Стоимость
2	1	Молоко	Поставщик № 1	76	43,00р.
3	2	Кефир	Поставщик № 2	40	26,00р.
4	3	Варенец	Поставщик № 3	86	34,00р.
5	4	Молоко	Поставщик № 3	45	26,00р.
6	5	Молоко	Поставщик № 4	87	24,00р.
7	6	Варенец	Поставщик № 4	34	41,00р.
8	7	Кефир	Поставщик № 4	67	32,00р.
9	8	Кефир	Поставщик № 3	45	39,00р.
10	9	Молоко	Поставщик № 2	43	35,00р.
11	10	Молоко	Поставщик № 1	32	33,00р.

3. В таблице с результатами должны быть поля: «Наименование», «Количество поставок», «Суммарная стоимость», «Средняя цена».

4. Во второй таблице в столбце «Наименование» названия молочных продуктов из первой таблицы должны встречаться один раз, например:

	A	B	C	D	E
14	№	Наименование	Кол-во поставок	Суммарная стоимость	Средняя цена
15	1	Молоко
16	2	Кефир
17	3	Варенец

II. Введите формулы в таблицы.

1. В первой таблице формула вводится только в поле «Стоимость»: для определения стоимости нужно количество умножить на цену. Примерный вид формулы в ячейке F2:

=D2*E2.

2. Во второй таблице формулы вводятся в поля «Количество поставок», «Суммарная стоимость», Средняя цена»:

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

=СЧЁТЕСЛИ(B$2:B$11;B15),

где B2:B11 — диапазон наименований молочных продуктов из первой таблицы, а B15 — ячейка второй таблицы, содержащая наименование продукта (для данного примера — «Молоко»).

	A	B	C	D	E
14	№	Наименование	Кол-во поставок	Суммарная стоимость	Средняя цена
15	1	Молоко	5
16	2	Кефир	3
17	3	Варенец	2

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

=СУММЕСЛИ(B$2:B$11;B15;F$2:F$11),

где F2:F11 — диапазон стоимости продукта из первой таблицы.

	A	B	C	D	E
14	№	Наименование	Кол-во поставок	Суммарная стоимость	Средняя цена
15	1	Молоко	5	9 087,00р.
16	2	Кефир	3	4 939,00р.
17	3	Варенец	2	4 318,00р.

c) Для определения средней цены нужно сложить все цены на один вид продукта (таблица 1), а затем разделить на количество поставок (таблица 2). Примерный вид формулы в ячейке Е15:

=СУММЕСЛИ(B$2:B$11;B15;E$2:E$11)/C15,

где E2:E11 — диапазон с ценами из таблицы 1, а C15 — ячейка, содержащая количество поставок данного продукта.

Результат:

	A	B	C	D	E
14	№	Наименование	Кол-во поставок	Суммарная стоимость	Средняя цена
15	1	Молоко	5	9 087,00р.	32,20р.
16	2	Кефир	3	4 939,00р.	32,33р.
17	3	Варенец	2	4 318,00р.	37,50р.

Для самостоятельной работы:

1.    Определить количество (в литрах) каждого продукта, завезенного в магазин.

2.    Составить таблицу «Поставщики», в которой определить, на какую сумму каждый поставщик завез в магазин продукции, общий литраж привезенной каждым поставщиком продукции.