第 1 列包含如下值:
AAA
AAB
AABAAA
AABAAB
AABAAC
AABAACAAA
第 2 列包含数字,例如 1、2、3、4、5、6
我需要根据第 2 列的值更改第 1 列的最后 3 个字符
规则是 1 = AAA、2 = AAB、3 = AAC、4 = AAD 等......
例如:如果第 1 列包含 AABAAC,第 2 列中的值为 1,我需要将第 1 列中的最后 3 个字符更改为 AAA。第 1 列的结果将是 AABAAA
答案1
您所说的“更改”是什么意思?创建一个包含操纵值的新列就足够了吗?如果可以,请按照描述设置查找表作者:TheCompWiz,C1
设置
=LEFT(A1, LEN(A1)-3) & VLOOKUP(B1, Sheet2!A$1:B$9999, 2)
并向下拖动/填充。
LEFT(
X
, LEN(
X
) - 3)
是全部X
除了最右边的 3 个字符之外,&
是字符串连接。
问题是,
规则是 1 = AAA、2 = AAB、3 = AAC、4 = AAD 等...
这看起来像是一种 26 进制表示法。我猜 26 = AAZ,27 = ABA,260 = AJZ,261 = AKA,676 (26 × 26) = AZZ,677 = BAA,等等。巧合的是,这看起来很像 Excel 标记列的方式。如果您有足够新的 Excel 版本(2007?2010?),您可以使用以下公式获得此结果
LEFT(ADDRESS(1, 26*26 + 26 +
n, 4), 3)
其中ADDRESS
是将坐标转换为单元格地址的函数。例如,ADDRESS(4, 2, …)
返回B4
,并且ADDRESS(4, 27, …)
返回AA4
。第三个参数指定是否使用绝对寻址或相对寻址;
4
表示行和列是相对的。所以ADDRESS(1, 26*26 + 26 + 1, 4)
是AAA1
,并且LEFT(
string
, 3)
返回前(最左边)三个字符string
。显然你会B1
插入n
。
PS 您需要当前版本的 Excel,因为在 Excel 2003 及更早版本中(我不确定 2007 是否如此),工作表最多可以有 256 列,并且该ADDRESS
函数不会让您生成不存在的单元格的名称(即,列号 > 256 的单元格)。
答案2
有几种方法可以实现这一点...但我建议在某处准备一张查找表,以防您需要修改数字对应的值:
在第 1 列中,值应如下所示:
="AAA AAB AABAAA AABAAB AABAAC AABAAC" & LOOKUP(B1,Sheet2!A$1:A$9999,Sheet2!B$1:B$9999)
在工作表 2 中,您可以创建一些公式要查找的内容,如下所示:
...等等,直到您找到想要查找的任意多个值。
我认为这是最简单的方法……但其他一些替代方法可能包括依赖 CHAR() 函数,该函数可以获取数值,添加 40,然后从 AZ 开始获取字符……假设您使用数值 1-26……或者您可以采取更极端的方法,在 VBA 中创建一个函数,该函数可以按照您认为合适的方式进行渲染。最后一种方法可能不是最好的主意,因为您必须克服许多困难才能使其发挥作用。