99 365新函数 CHOOSECOLS CHOOSEROWS

 

CHOOSECOLS CHOOSEROWS这两个函数属于同一类函数，函数的目标很明确，就是返回一组数据中，指定的行或列，类似OFFSET中返回指定区域，只不过OFFSET函数需要指定高度和宽度，在这里不需要，直接用行号或者列号来替代了；还是先看看参数

函数参数：

CHOOSECOLS(array,col_num1,[col_num2],…)；

CHOOSEROWS(array,row_num1,[row_num2],...)；

从参数中可以发现，365版本函数就单个函数的参数来说，都是非常好理解的，就是选中一个区域，指定条件返回等；如昨天分享的TOCOL、TOROW，一个返回列，一个返回行，参数也类似；单单看参数不好理解，直接上案例；

基础用法

如下图数据中，表1是一个数据区域，如需要在表2或其它区域返回5月的各个产品在各超市的销量和乐购超市1到6月的销量数据，可以在以下单元格分别录入以下函数：

K2=CHOOSECOLS(C2:H9,5)

B11=CHOOSEROWS(B3:H9,4)

以上函数理解，第二参数行号和列号是相对的就可以了，理解好后，可以根据这两个行号创建一些复杂的动态引用了，如返回某段时间范围的数据并汇总，来看一下高级用法；

范围求和

还是上面的数据，需要查询指定销售月份的所有超市的汇总销量，如想知道1月到3月或者3月到4月的销量汇总，如何设计是查询报表？设计此类查询报表只需要思考变量和定量分别是什么就可以了。变量就是查询条件的月份开始和结束时间。定量就是销售数据，确定后就不变了；

确定开始月和结束月，用MATCH来判断，长度可以直接相减就可以了；

为了确保大家理解公式，公式先不合并，分段写，分别录入以下函数：

D11 =MATCH(C11,C2:H2,0) 开始月列号

D12 =MATCH(C12,C2:H2,0) 结束月列号

D13 =SEQUENCE(,D12-D11+1,D11,1) 范围列,返回2到4列

C13 =SUM(CHOOSECOLS(C3:H9,SEQUENCE(,D12-D11+1,D11,1))),用CHOOSECOLS返回C3:H9区域的第2到4列，并用SUM函数求和；写好这个函数后，只需要调整查询范围就可以快速找到对应的动态范围的销售数据。

过滤数据

有时候用筛选函数对指定数据区域进行条件筛选的时候，因为数据区域和条件的原因，会出现我们不想要的列，此时用CHOOSECOLS可以对筛选结果进行指定返回列。如下图对源数据中条件为本科的员工返回对应的联系电话。录入以下函数：

G1= =VSTACK({"姓名","电话"},CHOOSECOLS(FILTER(A2:E14,C2:C14="本科"),1,5))结果如下图所示。

为了方便大家理解，分步运算结果如下：

步骤1：=FILTER(A2:E14,C2:C14="本科") ，筛选本科的结果

步骤2：=CHOOSECOLS(FILTER(A2:E14,C2:C14="本科"),1,5),保留第1列和第5列

步骤3：=VSTACK({"姓名","电话"},CHOOSECOLS(FILTER(A2:E14,C2:C14="本科"),1,5))

整理数据

有时候从ERP软件中或者网页中复制数据到Excel的时候发现，数据错行错列，虽然是错行错列，但是还是有一点规律，如下图数据中，源数据中日期与消费金额与消费备注分别在两列，每一笔记录对应三行，第一行为日期，第二行为金额，第三行为空，边上一列对应消费备注，如何把这些数据整理成标准的一维数据呢？

步骤1：=WRAPROWS(B1:B14,3) ，把第1列转成1行最多3列的数据

步骤2：=WRAPROWS(C1:C14,3)，把第2列也转成转成1行最多3列的数据

步骤3：==HSTACK(WRAPROWS(B1:B14,3),WRAPROWS(C1:C14,3)),把第1列和第2列并到一起；

步骤4：=CHOOSECOLS(HSTACK(WRAPROWS(B1:B14,3),WRAPROWS(C1:C14,3)),1,2,4)，保留第1列、第2列、第4列；

步骤5：

=VSTACK({"消费日期","金额","备注"},CHOOSECOLS(HSTACK(WRAPROWS(B1:B14,3),WRAPROWS(C1:C14,3)),1,2,4))，加上标题

思路总结

Office 365 的某个新函数绝对不是单单一个函数就可以发挥最大功能的，配合上其它函数才是战斗力最强的，特别是理解数组形态，垂直、水平、行、列方向，配合一些特定组合可以达到一个公式搞定特定需求的结果；

合并区域：VSTACK+ HSTACK，特别适合加标题；

选择行列：CHOOSECOLS CHOOSEROWS，特别适合去除无效行列，保留有效行列；

筛选结果：FILTER，条件筛选函数

合并结果：TEXTJOIN、CONCAT; 把结果合并

分开结果：TEXTSPLIT 把结果再次分开

我是古哥：

从事制造行业18年，在企业运营、供应链管理、智能制造系统等方面具有丰富的实战经验。企业智能化，柔性化计划运营管理专家，擅长通过企业流程优化规范，企业管理、导入计划运营提升企业效率；对提高企业准时交货率，降低企业库存，输出智能制造人才有丰富的经验。学习PMC生产计划，关注古哥计划！