Как в excel посчитать стоимость товара

REDMOND

Средневзвешенная цена в EXCEL

history 5 января 2013 г.
    Группы статей

  • Вычисление Среднего

Рассчитаем средневзвешенную цену товара без использования вспомогательных столбцов. Также рассчитаем средневзвешенную цену с условием.

Пусть дана таблица продаж партий одного товара (см. файл примера ). В каждой партии указано количество проданного товара (столбец А ) и его цена (столбец В ).

Найдем средневзвешенную цену. В отличие от средней цены, вычисляемой по формуле СРЗНАЧ(B2:B8) , в средневзвешенной учитываются «вес» каждой цены (в нашем случае в качестве веса выступают значения из столбца Количество ). Т.е. если продали одну крупную партию товара по очень низкой цене (строка 2 ), а другие небольшие партии по высокой, то не смотря, что средняя цена будет высокой, средневзвешенная цена будет смещена в сторону низкой цены.

Средневзвешенная цена вычисляется по формуле. =СУММПРОИЗВ(B2:B8;A2:A8)/СУММ(A2:A8)

Если в столбце «весов» ( А ) будут содержаться одинаковые значения, то средняя и средневзвешенная цены совпадут.

Средневзвешенная цена с условием

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

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

Формула для вычисления средневзвешенной цены для Поставщика1:

К аргументам функции СУММПРОИЗВ() добавился 3-й аргумент: —($B$7:$B$13=B17)

Если выделить это выражение и нажать F9 , то получим массив <1:1:1:1:0:0:0>. Т.е. значение 1 будет только в строках, у которых в столбце поставщик указан Поставщик1. Теперь сумма произведений не будет учитывать цены от другого поставщика, т.к. будут умножены на 0. Сумма весов для Поставщика1 вычисляется по формуле СУММЕСЛИ ($B$7:$B$13;B17;$D$7) .

Решение приведено в файле примера на листе Пример2.

Решение с 2-мя условиями приведено в файле примера на листе Пример3.

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

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

Упражнение 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р.

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

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

REDMOND

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

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

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

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

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

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

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

где 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:

где 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. Составить таблицу «Поставщики», в которой определить, на какую сумму каждый поставщик завез в магазин продукции, общий вес привезенной каждым поставщиком продукции, и сколько видов продуктов привез каждый из поставщиков.

Упражнение 2. «Студенческие стипендии» [1]

Составить таблицу экзаменационных оценок студентов и на ее основе определить средний балл и размер стипендии для каждого студента. Правила определения размера стипендии:

  • стипендия не назначается, если среди оценок есть хотя бы одна двойка;
  • 3,0
    Фамилия Предметы Средний балл Стипендия
    Матем. Физика Информ. Химия История
    1 Алексеев 5 3 2 3 3
    2 Гаврилов 4 4 3 3 3
    3 Зайцев 3 3 3 3 3
    4 Иванов 4 3 5 3 3
    5 Краснов 5 4 4 4 4
    6 Кузнецов 4 5 5 5 5
    7 Петров 5 5 5 5 5
    8 Сидоров 4 3 3 3 5
    9 Смирнов 4 4 4 4 4
    10 Солдатов 5 4 5 2 3

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

=ЕСЛИ(СЧЁТЕСЛИ(C3:G3;2)>0;0;ЕСЛИ(H3

Фамилия Предметы Средний балл Стипендия
Матем. Физика Информ. Химия История
1 Алексеев 5 3 2 3 3 3,2 0р.
2 Гаврилов 4 4 3 3 3 3,4 1 000р.
3 Зайцев 3 3 3 3 3 3 1 000р.
4 Иванов 4 3 5 3 3 3,6 1 200р.
5 Краснов 5 4 4 4 4 4,2 1 500р.
6 Кузнецов 4 5 5 5 5 4,8 1 800р.
7 Петров 5 5 5 5 5 5 2 000р.
8 Сидоров 4 3 3 3 5 3,6 1 200р.
9 Смирнов 4 4 4 4 4 4 1 500р.
10 Солдатов 5 4 5 2 3 3,8 0р.
Итого: 11 200р.

Упражнение 3. «Завод железобетонных изделий»

Завод ЖБИ выпускает бетонные строительные блоки. Характеристики блоков: марка, длина (м), ширина (м), высота (м) и удельный вес бетона, из которого изготовлен блок (кг/м3). На завод поступил заказ, который представляет собой список, содержащий марки требуемых блоков и количество блоков каждой марки. Определить, сколько вагонов потребуется для отправки блоков заказчику, если: блоки разных марок не могут находиться в одном вагоне, а грузоподъемность одного вагона N тонн.

I. Для решения задачи нужно создать две таблицы: с исходными данными и результатами.

1. Первая таблица должна содержать поля: «Марка», «Длина», «Ширина», «Высота», «Удельный вес», «Вес блока».
a) Заполните таблицу, кроме столбца «Вес блока» (не менее 10 марок).
2. Заведите отдельную ячейку для значения грузоподъемности.
3. Вторая таблица должна содержать поля: «Марка», «Количество блоков», «Количество вагонов».
a) В столбце «Марка» повторите названия нескольких марок блоков из первой таблицы (не менее 4).
b) Столбец «Количество блоков» заполните произвольными данными.

1. Вес блока (в первой таблице) определите с помощью функции ПРОИЗВЕД и переведите в тонны.
2. Чтобы определить количество вагонов для блоков каждой марки, нужно количество блоков умножить на вес блока и разделить на грузоподъемность:
a) Для того чтобы выбрать соответствующий вес блока из первой таблицы, используйте функцию СУММЕСЛИ.
b) Так как количество вагонов может быть только целым числом, то результат округлите до целого с помощью функции ОКРУГЛВВЕРХ.
3. В отдельной ячейке определите общее количество вагонов для блоков всех марок.

Упражнение 4. «Прайс-лист»

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

Литература

  1. Информатика: Практикум по технологии работы на компьютере/ Под ред. Н.В. Макаровой. – 3-е изд., переработ. – М.: Финансы и статистика, 2003. – 256 с.: ил.

REDMOND

Ссылка на основную публикацию