以下是示例数据。
sales=structure(list(Product = c(775L, 775L, 775L, 775L, 775L, 775L,
775L, 775L, 775L, 7756L, 7756L, 7756L, 7756L, 7756L, 7756L, 7756L,
7756L, 7756L), Day = c("16.01.2020", "17.01.2020", "18.01.2020",
"19.01.2020", "20.01.2020", "21.01.2020", "22.01.2020", "23.01.2020",
"24.01.2020", "16.01.2020", "17.01.2020", "18.01.2020", "19.01.2020",
"20.01.2020", "21.01.2020", "22.01.2020", "23.01.2020", "24.01.2020"
), Price.book. = c(652.79, 1046.55, 5264.37, 3756.38, 7724.47,
3908.69, 2241.46, 1221.06, 3733.03, 272, 195, 447, 699, 689,
375, 762, 796, 571), Sales.value.without.VAT..without.bonus. = c(1488.35,
1431.9, 9735.84, 7239.6, 11859.4, 5700, 3273, 2295.78, 5203.32,
629, 813, 312, 730, 768, 118, 902, 601, 479), Quantity.in.base.units = c(5L,
18L, 144L, 15L, 20L, 1500L, 100L, 6L, 12L, 915L, 760L, 738L,
139L, 339L, 516L, 685L, 221L, 652L), Sales_value = c(1786.02,
1718.28, 11683.01, 8687.52, 14231.28, 6840, 3927.6, 2754.94,
6243.98, 701, 619, 554, 821, 619, 850, 886, 714, 583), Gross_profit = c(835.56,
385.35, 4471.47, 3483.22, 4134.93, 1791.31, 1031.54, 1074.72,
1470.29, 749, 848, 824, 876, 203, 975, 261, 575, 984), Cost.of.accounting = c(652.79,
1046.55, 5264.37, 3756.38, 7724.47, 3908.69, 2241.46, 1221.06,
3733.03, 491, 758, 312, 379, 586, 266, 746, 544, 684), unit.cost = c(130.558,
58.1416, 36.5581, 250.4253, 386.2235, 2.6057, 22.4146, 203.51,
311.0858, 307, 732, 442, 467, 309, 854, 139, 440, 635)), class = "data.frame", row.names = c(NA,
-18L))我需要分别计算每个产品每月的平均成本。现在我通过Excel进行计算，但我需要通过R进行计算。要计算这个，我使用简单的公式（每月总和Price book）/（每月总和Quantity in base units）。例如，让我们取'Product=775。
一月份的总和Price book =29548.8，一月份的总和Quantity in base units =1820。因此29548.8/1820=16.23。2020年一月份的这些值将显示在所有行上。
类似地，产品=7756。一月份的总和Price book =4806，一月份的总和Quantity in base units =4965。因此4806/4965=0.96。2020年一月份的这些值将显示在所有行（Final_cost列）上
所以作为输出
Product Day Price.book.
1 775 16.01.2020 652.79
2 775 17.01.2020 1046.55
3 775 18.01.2020 5264.37
4 775 19.01.2020 3756.38
5 775 20.01.2020 7724.47
6 775 21.01.2020 3908.69
7 775 22.01.2020 2241.46
8 775 23.01.2020 1221.06
9 775 24.01.2020 3733.03
10 7756 16.01.2020 272.00
11 7756 17.01.2020 195.00
12 7756 18.01.2020 447.00
13 7756 19.01.2020 699.00
14 7756 20.01.2020 689.00
15 7756 21.01.2020 375.00
16 7756 22.01.2020 762.00
17 7756 23.01.2020 796.00
18 7756 24.01.2020 571.00
Sales.value.without.VAT..without.bonus. Quantity.in.base.units
1 1488.35 5
2 1431.90 18
3 9735.84 144
4 7239.60 15
5 11859.40 20
6 5700.00 1500
7 3273.00 100
8 2295.78 6
9 5203.32 12
10 629.00 915
11 813.00 760
12 312.00 738
13 730.00 139
14 768.00 339
15 118.00 516
16 902.00 685
17 601.00 221
18 479.00 652
Sales_value Gross_profit Cost.of.accounting unit.cost Final_cost
1 1786.02 835.56 652.79 130.5580 16.2356044
2 1718.28 385.35 1046.55 58.1416 16.2356044
3 11683.01 4471.47 5264.37 36.5581 16.2356044
4 8687.52 3483.22 3756.38 250.4253 16.2356044
5 14231.28 4134.93 7724.47 386.2235 16.2356044
6 6840.00 1791.31 3908.69 2.6057 16.2356044
7 3927.60 1031.54 2241.46 22.4146 16.2356044
8 2754.94 1074.72 1221.06 203.5100 16.2356044
9 6243.98 1470.29 3733.03 311.0858 16.2356044
10 701.00 749.00 491.00 307.0000 0.9679758
11 619.00 848.00 758.00 732.0000 0.9679758
12 554.00 824.00 312.00 442.0000 0.9679758
13 821.00 876.00 379.00 467.0000 0.9679758
14 619.00 203.00 586.00 309.0000 0.9679758
15 850.00 975.00 266.00 854.0000 0.9679758
16 886.00 261.00 746.00 139.0000 0.9679758
17 714.00 575.00 544.00 440.0000 0.9679758
18 583.00 984.00 684.00 635.0000 0.9679758如何分别计算每个产品的月平均成本以获得理想的产量。谢谢你的帮助。
1条答案
按热度按时间tvz2xvvm1#
您可以将
group_by()与dplyr中的mutate一起使用