понедельник, 18 марта 2013 г.

Подготовка к ГИА 2013

По многочисленным просьбам! Просто о сложном.

Excel. Функции СЧЕТЕСЛИ и СУММЕСЛИ

Пример задания:
Компания, выпускающая косметику, выдает распространителям прайс-лист, в котором указано название продукта и его цена для распространителя и для клиента. Создать «электронный калькулятор» распространителя, с помощью которого он сможет определить, какая сумма ему потребуется, чтобы выкупить заказанную покупателем продукцию у компании, сколько ему должен заплатить клиент, и прибыль, которую он получит в результате продажи.
(Нажмите на картинку для увеличения!)

В ячейке L3 (желтый цвет) запишем формулу:
=СУММЕСЛИ($B$3:$B$19;$I3;D$3:D$19)*$J3
Из всего списка наименований косметики $B$3:$B$19, выбираем только мусс для волос $I3  и суммируем все ячейки диапазона "стоимости для клиента" данного мусса D$3:D$19, не забыв умножить на количество $J3. В списке наименований может быть несколько товаров "мусс для волос" ,  мы отбираем их все.
СПРАВКА
 СУММЕСЛИ(диапазон;критерий;диапазон_суммирования)
Суммирует ячейки, заданные критерием.
Диапазон     — диапазон вычисляемых ячеек.
Критерий     — критерий в форме числа, выражения или текста, определяющего суммируемые ячейки. Например, критерий может быть выражен как 32, "32", ">32", "яблоки".
Диапазон_суммирования     — фактические ячейки для суммирования.
Ячейки в «диапазон_суммирования» суммируются, только если соответствующие им ячейки в аргументе «диапазон» удовлетворяют критерию.Если «диапазон_суммирования» опущен, то суммируются ячейки в аргументе «диапазон».
=СУММЕСЛИ(A2:A5;">160000";B2:B5)

СЧЁТЕСЛИ (диапазон; критерий)
Подсчитывает количество ячеек внутри диапазона, удовлетворяющих заданному критерию.
Диапазон    — диапазон, в котором нужно подсчитать ячейки.Критерий    — критерий в форме числа, выражения, ссылки на ячейку или текста, который определяет, какие ячейки надо подсчитывать. Например, критерий может быть выражен следующим образом: 32, "32", ">32", "яблоки" или B4.
=СЧЕТЕСЛИ(A2:A5;"яблоки")

ЛЮБИТЕЛЯМ ПРОСМОТРОВ!


P.S. Шутка дня: "Алла Юрьевна, Excel - ТАКАЯ полезная программа!!! Я когда прихожу домой,  включаю комп и прежде чем "Вконтакт" зайти, Ехсеl скорее открываю!"

САМОСТОЯТЕЛЬНО:

  Упражнение 1. «Магазин»
В магазин «Молоко» каждый день завозят молочные продукты несколько поставщиков. Составить таблицу учета поставок за день для этого магазина. В отдельной таблице определить количество поставок молочных продуктов одного вида за день, их суммарную стоимость и среднюю цену.
Комментарии:
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
Молоко
Поставщик № 5
32
33,00р.

Таблица 1
3. В таблице с результатами должны быть поля: «Наименование», «Количество поставок», «Суммарная стоимость», «Средняя цена».
4. Во второй таблице в столбце «Наименование» названия молочных продуктов из первой таблицы должны встречаться один раз, например:
A
B
C
D
E
14
№
Наименование
Кол-во поставок
Суммарная стоимость
Средняя цена
15
1
Молоко



16
2
Кефир



17
3
Варенец





ПРОВЕРЬ СЕБЯ! ОТВЕТ:
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р.



Для определения количества поставок нужно определить, сколько раз за день в магазин завозили, например, молоко. Для этого нужно использовать функцию СЧЁТЕСЛИ, которая определяет количество данных в диапазоне, равных критерию. Примерный вид формулы в ячейке С15:
=СЧЁТЕСЛИ(B$2:B$11;B15),
где B2:B11 — диапазон наименований молочных продуктов из первой таблицы, а B15 — ячейка второй таблицы, содержащая наименование продукта (для данного примера — «Молоко»).

b) Для определения суммарной стоимости всех продуктов одного названия нужно выбрать из первой таблицы и сложить стоимость всего, например, молока в магазине. Для этого используйте функцию СУММЕСЛИ, которая суммирует данные, отобранные по заданному критерию в данном диапазоне. Примерный вид формулы в ячейке D15:
=СУММЕСЛИ(B$2:B$11;B15;F$2:F$11),
где F2:F11 — диапазон стоимости продукта из первой таблицы.

c) Для определения средней цены нужно сложить все цены на один вид продукта (таблица 1), а затем разделить на количество поставок (таблица 2). Примерный вид формулы в ячейке Е15:
=СУММЕСЛИ(B$2:B$11;B15;E$2:E$11)/C15,
где E2:E11 — диапазон с ценами из таблицы 1, а C15 — ячейка, содержащая количество поставок данного продукта.
Литература
1.     Информатика: Практикум.. Н.В. Макаровой.
2.     http://office.microsoft.com/ru-RU/?CTT=97
Автор: на