excel中A列跟B列一一对应,A1对B1,A2对B2,当B列单元中的值为1时,提取A列单元值到C1

关键字：excel 更新时间：2019-12-23 06:35:52 132次访问

如：A列：B列：C1，A1数值为10.A2.A3.A4.A5数值为20.A6.A7.A8数值为30.A9.A10数值，B列单元1.2.3数值对应A列单元A1.A2.A3.A4.A5.A6.A7.A8.A9.A10数值，提取A列单元数值到C1单元。

如：A列：B列：C1，的A1数值,10，B1数值3，A5数值20，B1数值也是3，提取A列数值到C1单元，C1得到2个数值10.20，如B8也是3那么C1就是3个数值10.20.30

辅助列的公式

=IFERROR(INDIRECT("A"&SMALL(IF($B$1:$B$8=$B1,ROW($B$1:$B$8)),COLUMN(A$1))),"")

=SUBSTITUTE(CONCATENATE(D1,IF(D1="","",","),E1,IF(E1="","",","),F1,IF(F1="","",","),G1,IF(G1="","",","),H1,IF(H1="","",","),I1,IF(I1="","",","),J1,IF(J1="","",","),K1,IF(K1="","",","),L1,IF(L1="","",",")),",",,COUNTIF($B$1:$B$8,B1))

相关标签：excel