答案1
如果您的 Excel 版本没有 TEXTBEFORE 和 TEXTAFTER 等 O365 函数,并且您使用的是支持 FILTERXML 和 TEXTJOIN 的 2019 左右的版本,您可以尝试以下操作。
我假设数据是数字或文本,但是不是日期。
D1 中的公式。
=AVERAGE(TRANSPOSE(FILTERXML("<t><s>"&SUBSTITUTE(TEXTJOIN("/",TRUE,LEFT(D2:D5,FIND("/",D2:D5)-1)),"/","</s><s>")&"</s></t>","//s"))) &"/"&AVERAGE(TRANSPOSE(FILTERXML("<t><s>"&SUBSTITUTE(TEXTJOIN("/",TRUE,RIGHT(D2:D5,LEN(D2:D5)-FIND("/",D2:D5))),"/","</s><s>")&"</s></t>","//s")))
在公式栏中按CTRL+ SHIFT+ENTER创建数组公式。公式将自动括在花括号中,以表明这是一个数组公式。这一步很重要。
答案2
您没有指定格式,因此也没有指定截屏文本的性质。鉴于 Excel 对各种格式的数字材料的正常格式化,人们会认为数据是“m/d”短日期格式的日期(“m/d”是因为“d/m”会使“3/14”变成错误)。
但它们可能是分母至少有两个位置的分数,然后右对齐,或者只是纯文本。
因此,对于其中的每一个,一种做法是按照以下基本思路(数据是文本格式)进行操作,然后对另外两个进行如下修改:
=AVERAGE(VALUE(TEXTBEFORE(A1:A5,"/")))
=AVERAGE(VALUE(TEXTAFTER(A1:A5,"/")))
因此,TEXTBEFORE
对于左侧,TEXTAFTER
对于右侧。VALUE
因为这两个文本函数生成的文本,由于 Excel 有充分的理由相信您希望继续作为文本,所以不会通过使用类似函数AVERAGE
(或AGGREGATE
平均选择)将其输出强制转换为数字。
上述方法有很多种分解数据的方法,但您没有特别询问,所以我只是演示了这个过程:分解数据,只保留您需要的部分,将其强制(不是“强迫”)为数字,然后对其进行您想要的操作,在本例中是取平均值。每个步骤都可能涉及,每个步骤都有其他完成方式,您需要支持的 Excel 版本越早,每个步骤就越复杂且不通用。
因此,对于其他两种数据,您需要将其强制转换为文本,然后用公式替换“A1:A5”。例如:
日期:
=AVERAGE(VALUE(TEXTBEFORE( TEXT(A1:A5,"m/d"), "/")))
分数:
=AVERAGE(VALUE(TEXTBEFORE( TEXT(A1:A5,"# ??/??"), "/")))
根据您的确切数据及其“清洁度”,您可能需要使用AGGREGATE
,而不是选择或不选择。
答案3
如果您使用的是旧版本的 Excel,则可能需要尝试以下操作:
=AVERAGE(NUMBERVALUE(LEFT(D7:D11,(FIND("/",D7:D11)-1))))&"/"&AVERAGE(NUMBERVALUE(RIGHT(D7:D11,LEN(D7:D11)-FIND("/",D7:D11))))
或者
=AVERAGE(--(LEFT(D28:D32,(FIND("/",D28:D32)-1))))&"/"&AVERAGE(--(RIGHT(D28:D32,LEN(D28:D32)-FIND("/",D28:D32))))
您的列表(文本格式)位于单元格 D7:D11 中
这是一个数组公式,因此请单击工作表上方的公式栏,然后按 CTRL + SHIFT + ENTER 来激活它。