1 常用设计方法

在Excel中，实现按自定义条件查询业务数据，有几个常用思路：

1）VBA代码

2）筛选

3）公式

我一直持有这个观点，如果Excel本身的功能可以满足业务需求的实现，能不采用VBA代码设计，最好不用。例如这个场景，运用Excel公式对数据的计算、处理能力，同样可以实现数据的查询。筛选尽管是Excel的功能，但是结果是隐藏/显示记录的形式，定制方面有点欠缺。

2 示例：设计一个按条件数据查询的示例。

1）设计两个下拉框，类别与产品，用来选条件，实现思路，请阅上博。

http://s15/middle/62c0483cg880449b8b6be&690
2）选择饮料类的全部产品，即刻显示全部饮料的各项KPI（关键性能指标值）。
http://s3/middle/62c0483cg88044a1ece52&690

3）选择饮料类的具体某个产品，显示该产品（例如：浓缩咖啡）的各项KPI。
http://s11/middle/62c0483cg88044a7aeb3a&690

3 基本思路

在数据显示区，列出符合条件的产品结果集是最为重要。我们以列出某类全部产品为例，解析所运用的公式，考虑：

1）符合条件的第一条记录，例如：饮料类的第一个产品是什么。

2）符合条件的记录有几条，例如：饮料类的产品有几个。

3）如何控制行记录的显示，例如：如何将饮料的产品逐一列出。

关键步骤：

1）创建源数据DATA

http://s2/middle/62c0483cg88057052d4d1&690

2）在数据显示区，对符合条件的结果添加辅助列（A列），辅助列的第一行默认为0。除第一行外，辅助列的第N行根据第N-1行+1递增。至于递增到第几行，由符合条件的记录数控制。

例如：在图一中

A4 = 0

A5 =IF(AND($A4<COUNTIF(CategoryColumn,Category),ProductName = "全部"),$A4+1,"")

A5公式的大意是若上一行的数值小于所选类别（一级下拉框）的个数，且选择全部产品（二级下拉框），则基于上一行的数值+1, 否则为空白。

对A5以后的单元格复制A5公式。

2）在数据显示区，运用下列公式查找出符合条件的第一条记录，例如：源数据中饮料类中的第一个产品“凯牌矿泉水”，

B4 = OFFSET(CategoryStart, MATCH(Category,CategoryColumn,0)-1 + $A4, 1,1,1)

公式大意是从源数据“类别”标题开始，偏移单元格，找出饮料类的第一个产品。向下的偏移量由辅助列控制（$A4）。

3）Excel的查找类公式，例如：Match,Vlookup等，如果在源数据中查找不到待查找的结果，会返回“#N/A”的错误。因此，对B4单元格改进为：

B4 = IF(ISERROR(OFFSET(CategoryStart, MATCH(Category,CategoryColumn,0)-1 + $A4, 1,1,1)),"",OFFSET(CategoryStart, MATCH(Category,CategoryColumn,0)-1 + $A4, 1,1,1))

主要加了iserror()公式，控制查找不到的错误。

对B4以后的单元格，复制B4公式。饮料类的产品显示，非饮料类的产品不显示。

图一

http://s2/middle/62c0483cg88066f273f31&690

4）显示了符合条件的产品结果集之后，运用vlookup公式，将其各项KPI结果显示出来。

4 补充说明

1）运用Offset公式时，要求数据源是排好序的（Sorted），这里要求源数据是按（类别、产品）关键字排序。

2）这个示例，主要说明了在Excel中如何运用公式控制符合条件的行记录，对符合列条件的控制（例如：各项KPI的显示控制），我们也可以进一步展开。