Excel-表格资料重组(SUMPRODUCT,LARGE,SMALL,ROW,OFFSET,阵列
有网友问到:以下图左半部的资料表,此中是每一个学生在分歧日期所选修的课,若何按照这个资料表摘要出每门课的学生(参考下图右半部)?
以下数个部门渐渐来分化。
1. 利用 SUMPRDOCUT+LARGE 函数
贮存格E2:
=SUMPRODUCT(LARGE(($B$2:$C$23=E$1)*ROW($B$2:$C$23),ROW(1:1)))
公式获得的成果是姓名的「列号」,这个做法会以原挨次『相反』的方式显现。
若是你要用阵列公式来处置,则公式:
贮存格E2:{=LARGE(($B$2:$C$23=I$1)*ROW($B$2:$C$23),ROW(1:1))}
输入完成要按 Ctrl+Shift+Enter 键,Excel 会主动加上「{}」。
複製贮存格E2,贴至贮存格E2:G23。
操纵上述公式,根据『列号』查到对应的贮存格内容。
贮存格E2:=IFERROR(OFFSET($A$1,SUMPRODUCT(LARGE(($B$2:$C$23=E$1)*
ROW($B$2:$C$2北京拓展公司3),ROW(1:1)))-1,,,),"")
或
贮存格E2:{=IFERROR(OFFSET($A$1,LARGE(($B$2:$C$23=I$1)*ROW
($B$2:$C$23),ROW(1:1))-1,,,),"")}
这是阵列公式,输入完成要按 Ctrl+Shift+Enter 键,Excel 会主动加上「{}」。
複製贮存格E2,贴至贮存格E2:G23。
2. 操纵阵列公式和 SMALL 函数
贮存格E2:{=SMALL(IF($B$2:$C$23=E$1,ROW($B$2:$C$23),999),ROW(1:1))}
这是阵列公式,输入完成要按 Ctrl+Shift+Enter 键,Excel 会主动加上「{}」。
此中参数 999,只是肆意的一个很年夜的值。
公式获得的成果是姓名的「列号」,这个做法会以原挨次『不异』的方式显现。
複製贮存格E2,贴至贮存格E2:G23。
操纵上述公式,根据『列号』查到对应的贮存格内容。
贮存格E2:{=OFFSET($A$1,SMALL(IF($B$2:$C$23=E$1,
ROW($B$2:$C$23),999),ROW(1:1))-1,,,)}
複製贮存格E2,贴至贮存格E2:G23。
3. 在每一个姓名中将各课程标示那一天上课
贮存格F2:=IFERROR(OFFSET($B$1,,MATCH(F$1,$B2:$C2,0)-1,,),"")
複製贮存格F2,贴至贮存格F2:H23。
创作者先容 vincent- 全站分类:不设分类
- 小我分类:课本资料
- 此分类上一篇: Excel-资料重排仍依原挨次显现(SMLL,ROW,COUNTIF,OFFSET,阵列公式)
- 此分类下一篇: Excel-利用快速键和通用选单按键
- 上一篇: Excel-资料重排仍依原挨次显现(SMLL,ROW,COUNTIF,OFFSET,阵列公式)
- 下一篇: Windows 10-号令提醒字元模式利用更便利了
汗青上的今天
- 2016: Excel-调剂非数值的日期格局(MID)
- 2016: Excel-取用Google表单的线上考试成果自行计较分数(SUMPRODUCT)
- 2014: 善用快速键加速达到下一页/下一笔
- 2014: Evernote-将Xmind所绘心智图酿成一个记事
- 2013: Excel-若何让每列的高度最少为2列文字
- 2013: Excel-在关键阐发表中自订日期格局
- 2011: Word-设定段落文字为垂直置中
- 2011: Word-将被代替的文字标示分歧色采
- 2011: Excel-综合文章(阵列公式)
- 2010: Excel-从年度报表中摘要月报表和季报表
- 2010: Excel-班级期中考绩绩统计(六)
- 2010: Excel-班级期中考绩绩统计(五)
- 2010: Excel-班级期中考绩绩统计(四)