按周分组数据，但显示日期格式轴-Groupdatabyweekbutdisplaydateformataxis

1  

What you could do is make a line chart on the date axis, add and format error bars, then hide the lines and markers. Use the Minus option for the error bars, with no End Caps, and a Percentage Value of 100%, so the error bar reaches the horizontal axis. Format the error bar lines to simulate a thicker bar.

您可以做的是在日期轴上创建一个线形图，添加并格式化错误条，然后隐藏行和标记。对错误条使用负号选项，没有结束帽，百分比为100%，因此错误条到达水平轴。格式化错误条线以模拟粗条。

0  

I had to do this recently and the way I got around it was to create a second set of values using the =Text(A1,"MMM YY") formula.

我最近不得不这么做，我绕过它的方法是使用=Text(A1，“MMM YY”)公式创建第二组值。

Instructions on your actual sample data:

关于实际样本数据的说明:

Insert a column between Week beginning and Sales (For the purpose of this we will assume Week beginning is in column A)

在周开始和销售之间插入一列(为此，我们假设周开始在a列)

In the new column B row 2 enter this formula:

在新的B列第2行中输入这个公式:

=IF(TEXT(A2,"MMM YY")<>TEXT(A1,"MMM YY"),TEXT(A2,"MMM YY"),"")

Here we are telling it only to list if it is the first instance of the new month

在这里，我们告诉它，如果它是新的一个月的第一个实例，我们就只列出它

Copy it down as far as column A has data

复制到A列有数据的地方

Highlight the data in column B and C

突出显示B和C列中的数据

Create a column chart

创建一个柱状图

Your chart will now look how you wanted it

现在你的图表将会是你想要的样子

Edit: I just noticed you have "Week beginning" but I assume you want your chart to be week ending for the titles?

编辑:我刚注意到你有“星期开始”，但我猜你想要你的图表以周结束标题?

If so then make the B2 formula

如果是，那就用B2公式

=TEXT(A2+5,"MMM YY")

and then make the B3 one

然后做B3 1

=IF(TEXT(A3+4,"MMM YY")<>TEXT(A2+4,"MMM YY"),TEXT(A3+4,"MMM YY"),"")

And drag down, I have added 4 on the assumption your week closes on Friday (Monday + 4 Days) but you can adjust that accordingly

拖下来，我假设你的一周在周五(周一+ 4天)结束时增加了4个，但你可以相应地调整