☰

替代公式 EXCEL - 切换数字

tag-icon • tag-icon microsoft-excel worksheet-function
替代公式 EXCEL - 切换数字

我正在尝试用反向数字替换一组数字 1,2,3,4,5，作为需要完成的计算的一部分。1=5、2=4、3=3、4=2、5=1。

我使用的公式是

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(F13,"2","4",1 ),"1", "5", 1 ),"4", "2", 1 ),"5", "1", 1 ),"2","4", 1)

但是，它只对数字 2 和 5 有效，不能替代数字 1 和 4。

答案1

这将对数字进行替换（无文本）

=SUMPRODUCT((6-MID(A1,ROW($A$1:INDEX(A:A,LEN(A1))),1))*10^(LEN(A1)-ROW($A$1:INDEX(A:A,LEN(A1)))))

在此处输入图片描述

它只是迭代从 6 中减去每个部分的数字以得到翻转的数字，然后将其乘以 10 以按正确的顺序排列。

字符串会变得稍微复杂一些。我们可以使用 CONCAT 的数组版本，它是在 Office 365 Excel 中引入的。

=CONCAT(IF(ISNUMBER(--MID(A1,ROW($A$1:INDEX(A:A,LEN(A1))),1)),6-MID(A1,ROW($A$1:INDEX(A:A,LEN(A1))),1),MID(A1,ROW($A$1:INDEX(A:A,LEN(A1))),1)))

作为数组公式，退出编辑模式时必须使用 Ctrl-Shift-Enter 而不是 Enter 进行确认。

在此处输入图片描述

这也会迭代字符串，如果它是一个数字，则从 6 中减去并将其与连接结果一起放回。

如果要继续使用嵌套的 SUBSTITUTES，则需要先将两个数字更改为其他数字，然后再切换其他两个数字。然后将特殊字符切换为所需的输出。

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,1,"!"),2,"@"),4,2),5,1),"@",4),"!",5)

在此处输入图片描述

答案2

在此处输入图片描述

在单元格中写入此数组公式B2：

{=RIGHT(SUM(VALUE(MID(A2&"1",ROW(INDIRECT("1:"&LEN(A2&"1"))),1))*10^(ROW(INDIRECT("1:"&LEN(A2&"1")))-1)),LEN(A2))}

笔记，用 完成公式Ctrl+Shift+Enter。

对于以逗号分隔的数字/文本列表，请将下面写的 VBA 代码复制并粘贴为 Sheet 中的模块。

Function ReverseTxt(ByVal Target As Range)
    myLength = Len(Target.Value)
    For x = 0 To myLength
        ReverseTxt = Left(WorksheetFunction.Substitute(Target.Value, Left(Target.Value, x), ""), 1) & ReverseTxt
    Next x
End Function

在 Cell 中写入 UDFB4并填写：=ReverseTxt(A2)

注意：

  • 上面显示的 UDF 适用于单个数字/字符列表。
  • 如果您尝试 UDF，12, 13那么将是31, 21&DA将会AD。

相关内容