excel如何查找满足条件的所有的值公式5

一、数据透视表法至此透视表做完了。单选班级就得到你所要的内容了。————————————————————————————————————————————————————————————————————————————————————二、公式法B13单元格，设置数据有效性：这样便于输入。（1）B14单元格公式之一=IF(ROW()-13<=COUNTIF($A$2:$A$9,$B$13),INDIRECT("R"&SMALL(IF($B$13=$A$2:$A$9,ROW($A$2:$A$9)),ROW(A1))&"C"&COLUMN(),),"")有拉1列，再下拉7行（与上面人员行数相同）。再在B13单元格输入班级，其结果如下：（2）其公式之二=IF(ROW()-13<=COUNTIF($A$2:$A$9,$B$13),INDEX($A$1:$C$9,SMALL(IF($B$13=$A$2:$A$9,ROW($A$2:$A$9)),ROW(A1)),COLUMN()),"")（3）其公式之三=IF(ROW()-13<=COUNTIF($A$2:$A$9,$B$13),OFFSET($A$1,SMALL(IF($B$13=$A$2:$A$9,ROW($A$2:$A$9)),ROW(A1))-1,COLUMN()-1),"")或=IF(ROW()-13<=COUNTIF($A$2:$A$9,$B$13),OFFSET($A$1,SMALL(IF($B$13=$A$2:$A$9,ROW($A$1:$A$8)),ROW(A1)),COLUMN(A1)),"")