Excel—工作常用

上周起始日期

=TODAY()-WEEKDAY(TODAY(),2)-6

TODAY()表示的是当天的日期，WEEKDAY(TODAY(),2)表示本周的星期几，TODAY()-WEEKDAY(TODAY(),2)这段公式算出了上一周的最后一天，-6就变成了上一周的第一天。

 

 

 

上周结束日期

=TODAY()-WEEKDAY(TODAY(),2)

和上周起始日期基本一样，就是后面不用减6了，因为前面的那段公式正好算出本周的结束日期。

 

 

 

上一年的同期周开始日期和结束日期

=DATE(YEAR(XXXXXXX)-1,MONTH(XXXXXXX),DAY(XXXXXXX))

以上两个函数表示的当年的上一周的起始和结束日期，如果要算出上一年的同期日期需要引用本年上周开始和结束日期的单元格

上一年同期的周结束日期公式是=YEAR(XXXXXXX)-1&"-"&MONTH(XXXXXXX)&"-"&DAY(XXXXXXX)，其中XXXXXXX就是引用的单元格（上周的结束日期），就相当于把本年的本周结束日期年的部分减了1，月和日都不变，直接引用过来

而上一年的同期的周开始日期公式是=YEAR(XXXXXXX)-1&"-"&MONTH(XXXXXXX)&"-"&DAY(XXXXXXX)，其中XXXXXXX就是引用的单元格（上周的开始日期），和上面一样，或是也可以直接引用上一年的周结束日期的单元格然后减6，结果是一样的。

 

 

 

 

调取一段日期的函数公式

=CUBESET("SYRetail","{[日期].[日期].&["&TEXT(XXXXXXX,"yyyy-mm-dd")&"]:[日期].[日期].&["&TEXT(XXXXXXX,"yyyy-mm-dd")&"]}",TEXT(XXXXXXX,"YY.MM.DD")&"-"&TEXT(XXXXXXX,"MM.DD"))

=CUBESET("SYRetail","{[日期].[日期].&["&TEXT(XXXXXXX,"yyyy-mm-dd")&"]:[日期].[日期].&["&TEXT(XXXXXXX,"yyyy-mm-dd")&"]}",TEXT(XXXXXXX,"YYYY.MM.DD")&"—"&TEXT(XXXXXXX,"YYYY.MM.DD"))

通过上面的几个公式，我们得到了4个日期，分别是本年的上周开始和结束日期，和上一年的上周开始和结束日期，如果把一段日期写在一个单元格（就是要在一个单元格写出一个日期区间），我们可以用到下面这段公式

CUBESET表示引用数据源的一段区域，这里我们要的是一段日期，SYRetail是我们数据源的名称，固定的，敲出CUBESET函数，打出双引号是会自动出来这个数据源名称的。

"{[日期].[日期].&["&TEXT(XXXXXXX,"yyyy-mm-dd")&"]:[日期].[日期].&["&TEXT(XXXXXXX,"yyyy-mm-dd")&"]}"  这段函数表示的是我要取一段日期，然后它的开始日期样式和值是&["&TEXT(XXXXXXX,"yyyy-mm-dd")&"]，   

：表示是到的意思

：前面的是起始日期，后面是结束日期，注意引用的单元格，第一处XXXXXXX是引用的开始日期，第二处XXXXXXX是引用的结束日期。

另外     "{[日期].[日期].&["&TEXT(XXXXXXX,"yyyy-mm-dd")&"]:[日期].[日期].&["&TEXT(XXXXXXX,"yyyy-mm-dd")&"]}",    注意这整段函数的符号，首先要取一段的值，那就一定会用到一个大括号{}相当于数组，然后链接的日期样式也都是成对出现的。&["&              &"]。这里TEXT函数是将数字转化成文本，因为数据源中的日期都是文本的形式，不转换一下是调取不到数值的。把要的这段日期区间以什么形式呈现出来，同理因为以后要直接引用这段日期区间，所以要把这段日期转换为文本，

公式是  TEXT(XXXXXXX,"YY.MM.DD")&"-"&TEXT(XXXXXXX,"MM.DD")，这段公式很好理解，唯一要注意的就是中间区分两个日期的符号表现形式，这里用的是  -  但是俩个日期中间加了这么多&"-"&，这里注意一下。

 

 

 

 

本月起始日期

=DATE(YEAR(XXXXXXX),MONTH(XXXXXXX),DAY(1))

DATE可以得到一个日期函数，里面有3个函数，分别表示的是年、月、日，因为要表示本月的起始日期，day参数就固定写成“1”就好了，而这里面的两个XXXXXXX都是引用的同一个单元格，就是当天日期。

方法二（不建议使用，逻辑容易了）

=EOMONTH(XXXXXXX,-1)+1

XXXXXXX是引用单元格（引用的是本年本周的结束日期），EOMONTH是表示指定日期月份的之前或之后的最后一天函数，第二个参数就是表示前面的月份最后一天还是，后面的月份最后一天，例如我要引用的单元格日期是2017-07-07，那么我第二参数要是写-1就会得到2017-06-30，如果是1就会得到2017-08-31，如果是2就会得到2017-09-30。而在我们要得出的是本月的起始日期，那么我们引用下本年上周的结束日期，用它得到上个月的最后一天，然后在加上1，就巧妙的得到了本月的起始日期。

 

 

 

 

本月结束日期

=DATE(YEAR(XXXXXXX),MONTH(XXXXXXX)+1,DAY(1))-1

DATE可以得到一个日期函数，里面有3个函数，分别表示的是年、月、日。XXXXXXXX都是引用的同一个单元格，就是当天日期。MONTH加1表示的是下个月的月份，day参数用“1”代表1号，DATE(YEAR(XXXXXXX),MONTH(XXXXXXX)+1,DAY(1))这段公式代表的是下月的1号，然后在这段公式后面再减掉一个1，就得到了本月的月末日期。

由于工作需要，为了直观可以写成   =TODAY()    表示的是当天的日期

 

 

 

年的起始日期

=DATE(YEAR(XXXXXXX),MONTH(1),DAY(1))

XXXXXXX引用的是当天日期，MONTH和DAY参数都写的是1，表示1月1号。

 

 

 

本年的结束日期

=DATE(YEAR(XXXXXXX)+1,MONTH(1),DAY(1))-1

 XXXXXXX引用的是当天的日期，DATE(YEAR(XXXXXXX)+1,MONTH(1),DAY(1))表示明年的1月1号，后面再减掉个1就得到了本年的结束日期。

 由于工作需要，为了直观可以写成   =TODAY()    表示的是当天的日期

 

 

 

调取数据源中的某个条件

（说白了就是我要某个单元格呈现数据源的条件，为了我做表可以直接引用，但是手写的文本和数值当单元格引用条件时是不能调用的，也会报错，所以我们要引用的单元格内的数值或是文本必须是由公式代入进去的）

比如

调取一个日期的函数公式

=CUBEMEMBER("SYRetail","[日期].[日期].&["&TEXT(XXXXXXX,"yyyy-MM-dd")&"]")

CUBEMEMBER表示调取某一个条件，"SYRetail"是数据源，固定格式，"[日期].[日期].&["&TEXT(XXXXXXX,"yyyy-MM-dd")&"]"是调取的内容，前半段[日期].[日期]表示数据源的内容，&["&TEXT(XXXXXXX,"yyyy-MM-dd")&"]表示引用的条件所呈现的样式。

这个公式里面有一个单元格引用，引用的是你想要调取的日期，虽然这个日期是手写的，但是也要注意格式“0000-00-00”

 

 

调取一段日期的函数公式
=CUBESET("SYRetail","{[日期].[日期].&["&TEXT(XXXXXXX,"yyyy-mm-dd")&"]:[日期].[日期].&["&TEXT(XXXXXXX,"yyyy-mm-dd")&"]}",TEXT(XXXXXXX,"YY.M.DD")&"-"&TEXT(XXXXXXX,"M.DD"))
这里一共4处需要引用单元格，1和3处是引用开始日期的，2和4处引用的是结束日期的

 

 

 

调取同比日期的公式
=CUBEMEMBERPROPERTY("SYRetail",XXXXXXX,"对比日期")
引用的单元格是本年的日期，得到的是去年的同比日期（比如2018-05-10这个日期得到的同比日期是2017-05-11日，具体算法暂时不清楚，有可能是根据节日日期人为更改的）

 

 

 

 

调取店铺编码的函数公式
=CUBEMEMBER("SYRetail","[机构].[店铺代码].&["&XXXXXXX&"]")

其中XXXXXXX是表示引用的单元格，而这个单元格的内容就是店铺编码，但不是公式性的，我们没法直接嵌套函数。就相当于我们插了一列辅助列，然后用这个公式调取一列带有公式的“店铺名称”，这样我们就可以直接当条件来调取BI中的数据了。

 

 

 

调取店铺名称的函数公式（很少用到，一般都用店铺代码来做辅助列）
=CUBEMEMBER("SYRetail","[机构].[店铺].&["&XXXXXXX&"]")
其中XXXXXXX是表示引用的单元格，而这个单元格的内容就是店铺名称，但不是公式性的，我们没法直接嵌套函数。就相当于我们插了一列辅助列，然后用这个公式调取一列带有公式的“店铺名称”，这样我们就可以直接当条件来调取BI中的数据了。

 

 

 

调取一段值的函数公式
=CUBEVALUE("SYRetail",XXXXXXX,XXXXXXX,XXXXXXX。。。)

通过上面的几个公式我们现在可以从数据源调取了一些条件，而下面这个函数就可以使我们利用这些条件找到相应的值，比如自营区5月1号到5号的销售金额是多少

CUBEVALUE是调用数据源中多条件的值的函数，"SYRetail"数据源名称固定格式，后面的参数分别是引用的条件，每个引用的单元格都是一个条件，中间用逗号隔开，可以写无限个条件，注意这些条件是并且的关系。
（比如18年5月1日的童装分公司的销售金额，就需要引用3个单元格，“童装”，“2018-05-01”，“销售金额”。引用的顺序无所谓）

 

 

 

 

周会报表说明

一.大围销售数据

销售金额，三个参数维度(1.时间或时间段  2.区域或整个自营区  3.销售金额（固定的公式，目的直接调用）)，一般都是反应和去年的同比，所以销售金额参数是固定的，不同情况会用两个变量参数，一个是时间，一个是区域。

 

 

 

 

二.原店销售数据（店铺属性有更新时，就是大围店和原店有变化时，需要更新表格）

提到原店的销售数据就一定要先制作一个表格，不能直接在BI里面调用，因为BI中是无法区分原店和大围店的，制作的这个表是要利用实时更新的店铺属性表，然后在区分好的店铺属性表里面做销售数据，同样加入三个参数维度，(1.时间或时间段  2.区域或整个自营区  3.销售金额（固定的公式，目的直接调用）)，最后把做好的数据利用sumifs函数引用到新的表格中，这样就可以把原店的分出来了。

 

 

 

 

三.大围出货数据（需要每周手动更新）需要到配货系统里面调取上一周的数据，每周都要填数据

出货数据（又叫发货数据）要到配货系统里面调取数据，可以取一天的也可以取一个时间段的，但是只可以统计昨天前的，当天的数据不准确！调取的出货数据是以款号区分的。我们要统计的表是以18年季度统计的，但是调取的数据不单单有18年的，还有其他的年份季节和配衬，所以我们要利用IFERROR和VLOOKUP函数加入一列辅助列，来区分18年的春夏秋冬季和其他季度的。统计的金额也是结算后的金额（就是折后价）。

 

 

 

四.大围折扣数据

大围折扣数据和大围销售数据一样都可以直接到BI数据源里面直接调取你们的数值，三个参数（1.时间或时间段  2.区域或自营区  3.销售折扣率（折扣率=实际销售金额/吊牌价））

 

 

 

 

五.原店毛利数据（店铺属性有更新时，就是大围店和原店有变化时，需要更新表格）

要算毛利一定要调取2个参数，一个是销售金额一个是吊牌金额

原店！！！依然要制作表格，然后引用表格中的数据做表。要用的sumifs函数。做这个表格之前要调取销售金额和吊牌金额的数据，可以直接利用公式引用。

这里有几个公式：

毛利额=销售金额（实际成交金额）*毛利率

毛利率=（销售折扣率-进货折扣率）/销售折扣率      这里的销售折扣率是指的实际销售折扣率，而进货折扣一般都是0.55

销售折扣率=实际销售金额/吊牌金额

 

 

 

六.大围售出数据

售出率可以直接引用BI公式，但是这里又延伸一个概念，售出率分为发货售出率和订单售出率

发货售出率=报表系统销售数量/配货系统出货数量

订单售出率=报表系统销售数量/订单系统原始订单数量

一年4个季度，订货会一般是在3、6、9、12月份，订单数量是订货会订购的数量，如果后期涉及到加单了就要算出货数量了，一般每个季度在前一个半月我们算的是订单售出率，因为很少有加单的，在每个季度的后一个半月我们一般就算发货售出率了，因为会有一些加单的。

 

 

 

 

七.大围库存数据（这个表也是需要每周手动更新的）

库存也是需要到配货系统里面调取数据的，需要做几列辅助列，首先是实际库存，实际的库存需要刨除掉负值和B仓的库存，负值是厂家欠我们库房的货品，B仓是次品仓。（还要把配衬和其他季节区分开）

每次统计的时候需要把上一次的数据保存下来，也就是上周的数据，把上周的数据放到大上周，然后和上周的数据做比较。

我们一共7个仓，每个仓的含义：

A仓（是总仓，就是总厂发来的货物首先要进到总仓，然后从总仓往各个分仓发货）

B仓（次品仓，所有的次品都会放到次品仓）

E仓（自营仓，就是我们自营店铺的货品存放处）

F仓（童装仓，所有的童装都是放到这个仓库的，包括客户的童装和自营的童装）

T仓（原来是田峰仓，现在也算自营仓了）

Y仓（客户仓，所有的客户货品会放到这个仓）

Z仓（快单仓，就是我们自己加单的货都会进到这个仓，有时候我们自己觉得卖的好的货品也会进点，到时候推给客户或自己消化）

 

 

 

 

 

八.导入图片（通过款号导入图片）

操作方法：

1.选中需要插入图片的行或列首个单元格，然后选择“视图”选项卡中的“宏”，然后输入一个名字（英文组成），点击创建。

 

进入到公式编写栏，将之前的代码全部清除掉

 

把下面这段公式复制到代码编写栏，注意修改5处代码，下方已用颜色标注。

 

 

Sub 清空图片()
'删除当前sheet上所有的图片文件
For Each Shape In ActiveSheet.Shapes
Shape.Delete

Next
End Sub

Sub 插入图片()
FilePath = "C:\00\" '图片文件路径
Fill = 0 '插入方式:1:按列插入图片(横向) , 0 按行插入图片(纵向)
Row = 6 '款号所在的行数
col = 42 '款号所在的列数
Location = -1 ' 1: 图片在款号右边或下面 , -1 : 图片在款号左边或上面

Do
'根据第一行的图片文件名,将图片插入在第二行内,并调整图片的高与宽
With Cells(Row + IIf(Fill = 1, Location, 0), col + IIf(Fill = 0, Location, 0))
.Select
L = .Left
T = .Top
W = .Width
H = .Height

Filename = Cells(Row, col).Text & ".jpg" '生成文件名
If Filename = ".jpg" Then '如果1行为空,表示结束
Exit Do
End If

If Dir(FilePath & Filename) <> "" Then '检查文件存在
'插入图片
ActiveSheet.Shapes.AddShape(1, L, T, W, H).Fill.UserPicture (FilePath & Filename)
End If
End With

If Fill = 1 Then
col = col + 1
Else
Row = Row + 1
End If
Loop

Cells(1, 1).Select '回到A1单元格
End Sub

 

 

 

 

 

总结

1.如果要统计原店数据，那么一定要重新制作表格，要引用店铺属性的汇总表，不能直接引用BI数据源，因为BI数据源是没法区分原店和大围店的

2.若是要统计出货（发货）和库存的数据，需要到配货系统里面调取数据，BI的数据不准确，出货（发货）起始日期和结束日期都可以选择（可以选择单独一天或一个时间段），库存则只有一个时间段，库存是实时数据，是截至现在库存的数据。

 

转载于:https://www.cnblogs.com/zhaoyudang/p/9009166.html