用函数的方法去除空白的行和列

zgjxxx

能不能用函数的方法去除空白的行和列？表2右边和底下的平均分也要随着数据的变化以及行数和列数的增减，相应变化（表2所有数据都是自动生成的，包括总分和平均分，以及对应的位置也要相应变化）

如表1所示，测试的科目和测试者经常会变化（表1标记
颜色的编号和科目的总范围，A1:H11是固定的），
导致会出现空行或者空列。
想在表2中，自动生成一个新的表格，去除空白的行和列，并计算总分和平均分（总分和平均分必须很紧凑的跟在
数据旁边，不出现多余的空白的行和列，不知道有没有好一点的思路。最好用函数解决，不用VBA或者定位空置删除等思路。因为源数据特别复杂，底下还有大量的数据要分析。想用函数的方法自动生成要的表格）。
用函数的方法去除空白的行和列.zip (67.6 KB, 下载次数: 19)

2024-8-2 10:48 上传

点击文件名下载附件

solextrade

五分钟，学会一对多、多对多查询经典公式
https://club.excelhome.net/thread-1484562-1-1.html
(出处: ExcelHome技术论坛)

无关岁月

1. =INDIRECT("表1!"&TEXT(SMALL(IF(表1!$A$1:$H$11<>"",ROW($1:$11)/1%+COLUMN($A:$H)),ROW(A1)*COUNTIF(表1!$1:$1,"<>")-COUNTIF(表1!$1:$1,"<>")+COLUMN(A1)),"!r0c00"),)

复制代码

zgjxxx

solextrade 发表于 2024-8-2 11:06
五分钟，学会一对多、多对多查询经典公式
https://club.excelhome.net/thread-1484562-1-1.html
(出处: E ...

感谢回复。但是这个方法不能解决我的问题啊，我想去掉空白的行和列，最下面和最右边还要计算平均分和总分。当表1数据增加或减少的时候，表2也要变化。

良良

A1=IFERROR(INDEX(表1!$A:$H,SMALL(IF(表1!$A$1:$A$11<>"",ROW($1:$11)),ROW(A1)),SMALL(IF(表1!$A$1:$H$1<>"",COLUMN($A:$H)),COLUMN(A1))),"")，三键，下拉、右拉。

袁勇

空行是不是全空，空列是不是全空，会不会某个列或行中有数据，但不完全？

咔咔乱坠

高版本
解1

1. =FILTER(FILTER(A1:H11,A1:H1>0),A1:A11>0)

复制代码

解2

1. =LET(a,A1:H11,FILTER(FILTER(a,TAKE(a,1)>0),TAKE(a,,1)>0))

复制代码

zgjxxx

良良 发表于 2024-8-2 11:17
A1=IFERROR(INDEX(表1!$A:$H,SMALL(IF(表1!$A$1:$A$11"",ROW($1:$11)),ROW(A1)),SMALL(IF(表1!$A$1:$H$1"", ...

感谢回复。要的数据是提出来了，但是紧贴着的总分和平均分能弄出来吗？要是能全部解决就完美了。

part98

高版本可以，低版本就别想通过函数一步完成了
=LET(s,FILTER(FILTER(表1!A:Z,表1!A:A<>""),表1!A1:Z1<>""),z,BYROW(DROP(s,1,2),SUM),a,HSTACK(s,VSTACK("总分",z)),p,BYCOL(DROP(a,1,2),AVERAGE),VSTACK(a,HSTACK("","平均分",p)))

leepoison

1. =LET(b,LET(a,WRAPROWS(TOCOL(表1!A1:G11,3),COUNTA(表1!A1:H1)),HSTACK(a,VSTACK("总分",BYROW(DROP(a,1,2),LAMBDA(x,SUM(x)))))),VSTACK(b,HSTACK("","平均分",BYCOL(DROP(b,1,2),LAMBDA(y,AVERAGE(y))))))

复制代码