excel 创建文件超链接 2024-10-21

需求：

image.png

点击 C3之后，会弹出与之对应的文件（超链接）

但是这个超链接地址会根据单号的不同更换路径。

image.png

先写答案：

=(HYPERLINK("D\file " & INDEX(Sheet1!D:D, (SUMPRODUCT((ABS(Sheet1!B2:C100-(LEFT(B3, 6)))=MIN(ABS(Sheet1!B2:C100-(LEFT(B3, 6)))))*ROW(Sheet1!B2:C100)))) & "" & B3 & ".xlsx", B3))

答案解释：
1，这些要打开的文件都在路径：D\file........xlsx 里面
2，变换路径的字典表在“sheet1”
3，超链接函数：
HYPERLINK(路径, 昵称)
昵称就用B3
现在的问题点就是“路径”

"D\file" & INDEX(Sheet1!D:D, (SUMPRODUCT((ABS(Sheet1!2:100-(LEFT(B3, 6)))=MIN(ABS(Sheet1!2:100-(LEFT(B3, 6)))))*ROW(Sheet1!2:100)))) & "" & B3 & ".xlsx"

2，路径的关键点就是那个要变动的路径如何自动选择：

image.png

也就是要获取上图的“行号”

3，INDEX(array, row_num, [column_num])
array : 数组，也就是这里的 Sheet1!D:D （sheet1是变动路径的字典）
row_num：对应行号，也就是这里的：
(SUMPRODUCT((ABS(Sheet1!2:100-(LEFT(B3, 6)))=MIN(ABS(Sheet1!2:100-(LEFT(B3, 6)))))*ROW(Sheet1!2:100)))

LEFT(B3, 6)，意思是B3单元格的左边6个位。这里就是220001
ABS(A-B），输出两个数差的绝对值。
Sheet1!2:100, 这个是sheet1表格中B2至C100的数组，也就是说B2至B100和C2至C100所有的单元格的数。
Sheet1!2:100-(LEFT(B3, 6))，就是B2至C100的数组与220001的差。
MIN(ABS(Sheet1!2:100-(LEFT(B3, 6))))，B2至C100的数组与220001的差的绝对值的最小值。
SUMPRODUCT(C2:C12,D2:D12)
这个的函数的意思是C2D2+C3D3+....C12*D12 （就是这些商品的总价），
举这个不相干的例子，就是为了更好理解sumproduct()函数。

image.png

重点
我在sumproduct中相要得到的结果就是在Sheet1!2:100这里找出最接近220001的值
sumproduct（）第一个参数：(ABS(Sheet1!2:100-(LEFT(B3, 6)))=MIN(ABS(Sheet1!2:100-(LEFT(B3, 6))))) 输出结果是

（0，0，0，0，0，1，0，0，0，0）*（1，1，2，2，3，3，4，4，5，5，6，6，）
第一个乘数数组（0意味着false，也就是不是最小值，1就是那个最小值，唯一）
第二个乘数数组（就是各个单元格的行号）

搞定。