是否可以基于动态日期值在excel中输出列的值? [英] Is it possible to output the values of a column in excel based on a dynamic date value?

问题描述

在下表中，我以星期为列，以雇员为行，其值是每位雇员每周休假的天数.

In the following table I have weeks as columns, employees as rows and the values are the amount of holiday days per employee per week taken.

我想创建一个单独的表作为仪表板，其中输出将根据我输入到日期选择器中的日期而变化.在下面的示例中，我选择2015年2月11日作为我的星期，我只希望在输出表中看到该星期的相应值.我该怎么做呢?

I want to create a separate table as a dashboard where the output will change depending on the date I input into the date selector. In the example below, I have chosen the 2/11/2015 as my week and I would like to only see the corresponding values for that week in the output table. How do I go about doing this?

推荐答案

在B5中尝试一下:

=Index($B$34:$F$48,MATCH($A5,$A$34:$A$48,0),MATCH($C$1,$B$33:$F$33,0))

然后复制下来.

更改公式中的F列引用以获取数据列的范围.

Change the F column References in the formula to get the extent of you columns of data.

注意:如果您所在的国家/地区使用; 作为标准之间的分隔符，而不是，则是带有这些分隔符的公式:

As a Note: If you are in a country that use ; as the delimiter between criteria instead of , here is the formula with those delimiters:

=Index($B$34:$F$48;MATCH($A5;$A$34:$A$48;0);MATCH($C$1;$B$33:$F$33;0))

这篇关于是否可以基于动态日期值在excel中输出列的值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！