英文:

Returning a list of (x,y) coordinates in a matrix based on cells that holds a value

问题

我有一组节点之间的弧线矩阵，只有一些弧存在（具有值的弧）。

我正在尝试获取存在的弧的列表，例如（R01，R02），（R01，R03）等。

我尝试将IF语句与CONCATENATE结合使用，但未能实现期望的结果。

英文:

I have a matrix of arcs between nodes where only some of the arcs exists (those with a value).
Ref:

d_{ij}^k	R01	R02	R03	R04	R05	R06	R07	R08	R09	R10	R11	R12	R13	R14	R15	R16	S01	S02
R01		0.90	1.70	2.60	0.60	1.50	1.15	1.80		1.50
R02	0.90		0.85	1.75	0.95	0.65	1.00	1.00
R03	1.70	0.85		0.85	1.75	0.50	1.50	1.10										1.10
R04	2.60	1.75	0.85		2.60	1.25	2.25	1.70						0.90	3.10
R05	0.60	0.95	1.75	2.60		1.40	0.75	1.40
R06	1.50	0.65	0.50	1.25	1.40		1.00	0.70
R07	1.15	1.00	1.50	2.25	0.75	1.00		0.80			1.65
R08	1.80	1.00	1.10	1.70	1.40	0.70	0.80
R09										1.00
R10	1.50								1.00
R11							1.65					0.60
R12											0.60
R13														1.20
R14				0.90									1.20
R15				3.10											2.00
R16														2.00
S01

What I am trying to do is to get a list of the arcs that exists e.g. (R01,R02), (R01,R03) etc.

I have tried combining the IF statement with CONCATENATE, but I fail to achieve the desired result.

答案1

得分: 1

你期望的结果可以通过使用 LET 函数（例如在 Microsoft 365 中可用）来实现。该函数通常允许为计算结果分配名称，从而存储计算结果、值等。

=LET(name, name_value, calculation)

以下具体实现使用以下步骤：

（I）指定数据的范围（不包括行标题和列标题）、行标题和列标题。

（II）您需要确定所有非空单元格的坐标。因此，您必须首先确定哪些单元格为空白。对于空白单元格，您不希望返回任何内容，对于所有其他单元格，您希望相应地返回坐标。此步骤返回一个带有相应非空单元格坐标的矩阵（要查看这一点，您可以将公式中 "result" 的最后规范更改为 "non_empty_cells"）。

（III）由于您希望将其返回为单个列表而不是矩阵，您收集您的配对并将它们放入正确的输出格式中，通过将配对放入括号中，然后使用 TEXTJOIN() 将它们全部连接在一起以形成一个列表。

（IV）返回结果，即具有所有非空单元格坐标的最终列表。

=LET(
    data_range, $B$2:$S$19,
    row_headers, $A$2:$A$19,
    column_headers, $B$1:$S$1,
    non_empty_cells, IF(ISBLANK(data_range), "", 
    INDEX(row_headers, MATCH(ROW(data_range), ROW(row_headers), 0)) & "," & 
    INDEX(column_headers, MATCH(COLUMN(data_range), COLUMN(column_headers), 0))),
    pairs, IF(non_empty_cells="", "", "("&non_empty_cells&")"),
    result, TEXTJOIN("; ", TRUE, pairs),
    result
)

前三行/列的结果将如下所示（分隔符可以根据需要更改，既可以更改坐标之间的分隔符，也可以更改配对之间的分隔符）：

(R01,R02); (R01,R03); (R02,R01); (R02,R03); (R03,R01); (R03,R02)

<details>
<summary>英文:</summary>

One approach to achieve your desired result could be the use of the `LET` function (available for e.g., Microsoft 365). Generally the function allows to assign names to computed results, and thus, also storing computed results, values etc.

=LET(name, name_value, calculation)

The following concrete implementation uses the following steps:

(I) Specify the ranges of your data (without row_headers and column_headers), row_headers, and column_headers respectively.

(II) You need to determine the coordinates of all non_empty_cells. Thus, you must first determine which cells are blank. For the blank cells you do not want to return anything and for all other cells you want to return the coordinates accordingly. This step returns a matrix with the coordinates in the respective non-empty cells (to see this, you can change the last specification of "result" in the formula to "non_empty_cells").

(III) Since you want to return it as a single list rather than a matrix, you gather your pairs and put them into the proper output format by putting the pairs in paranthesis and then joining them all together with `TEXTJOIN()` to form one list accordingly.

(IV) You return your results, i.e., the final list with all the coordinates of the non-empty cells.

=LET(
data_range, $B$2:$S$19,
row_headers, $A$2:$A$19,
column_headers, $B$1:$S$1,
non_empty_cells, IF(ISBLANK(data_range), "",
INDEX(row_headers, MATCH(ROW(data_range), ROW(row_headers), 0)) & "," &
INDEX(column_headers, MATCH(COLUMN(data_range), COLUMN(column_headers), 0))),
pairs, IF(non_empty_cells="", "", "("&non_empty_cells&")"),
result, TEXTJOIN("; ", TRUE, pairs),
result
)

The result for the first three rows/columns will look as follows (the delimiters can be changed as needed, both for the delimiters between coordinates and between pairs):

(R01,R02); (R01,R03); (R02,R01); (R02,R03); (R03,R01); (R03,R02)