英文:
Return the name of a named range based on cell?
问题
我想在Google表格中搜索我的命名区域，找到一个值并返回该值存在的区域的名称。如果我有区域“Alpha”和“Beta”，并且在“Alpha”中有值“first”，当我在单元格中键入“first”时，我希望公式会输出“Alpha”。是否可以实现？我希望使用一个公式，以便我可以将其包含在其他公式中。
英文:
Not sure if this is possible in Google sheets, but I'd like to search my named ranges for a value and return the name of the range where the value exists.
If I have ranges "Alpha" and "Beta", and within Alpha is the value "first", when I type "first" into my cell I want the formula will spit out "Alpha". Can it be done? I prefer a formula so that I can include it in other formulae.
答案1
得分: 1
可能需要创建一个自定义函数以获得此结果。要创建自定义函数，请按照此处的说明操作，然后将以下代码粘贴到 code.gs 中：
function WHATRANGE(col, row) {
var result = "Null";
var rangeList = SpreadsheetApp.getActive().getNamedRanges();
rangeList.forEach(function (namedRange) {
var range = namedRange.getRange();
if (col >= range.getColumn() && col <= range.getLastColumn()
&& row >= range.getRow() && row <= range.getLastRow()) {
result = namedRange.getName();
}
});
return result;
}
此函数循环遍历命名范围，并确定参数 row 和 col 是否在该范围内。如果在范围内，则输出范围的名称；如果不在范围内，则输出 Null。要在 Google Sheets 中调用此函数，请输入 =WHATRANGE(3,4)。
我会假设您的数据在一列中，即A列（第1列）。然后使用：
=WHATRANGE(1,MATCH("first",A:A,0))
英文:
You might need to create a custom function to get this result. To create a custom function follow the instructions here, and then paste the following code into code.gs:
function WHATRANGE(col, row) {
var result = "Null";
var rangeList = SpreadsheetApp.getActive().getNamedRanges();
rangeList.forEach(function (namedRange) {
var range = namedRange.getRange();
if (col >= range.getColumn() && col <= range.getLastColumn()
&& row >= range.getRow() && row <= range.getLastRow()) {
result = namedRange.getName();
}
});
return result;
}
This function cycles through the named ranges, and determines if the arguments row and col are within that range. If they are, it outputs the name of the range; if it is not, it outputs Null. To call this function in Google Sheets, enter =WHATRANGE(3,4).
I will assume your data is in one column, namely column A (column 1). Then use:
=WHATRANGE(1,MATCH("first",A:A,0))
答案2
得分: 1
方法 1：
如果您已经知道所有命名区域的名称，比如 Alpha 和 Beta，可以尝试以下公式吗？在这种情况下，单元格 "A1" 的值为 "first"。如果输入的值不包含在命名区域中，则返回 "Not found."。
=LET(
alpha,COUNTIF(Alpha,"="&A1)>0,
beta,COUNTIF(Beta,"="&A1)>0,
IFS(AND(alpha,beta),"Alpha,Beta",alpha,"Alpha",beta,"Beta",TRUE,"Not found.")
)
方法 2：
如果您不知道所有命名区域的名称，可以尝试以下自定义函数吗？在这种情况下，请将以下脚本复制并粘贴到电子表格的脚本编辑器中，并保存脚本。当您使用此脚本时，请输入自定义函数 =SAMPLE("first")。这样，将返回命名区域的名称。如果输入的值不包含在命名区域中，则返回 "Not found."。
const SAMPLE = search =>
SpreadsheetApp.getActiveSheet().getNamedRanges().reduce((ar, r) => {
if (r.getRange().createTextFinder(search).matchEntireCell(true).findNext()) {
ar.push(r.getName());
}
return ar;
}, []).join(",") || "Not found.";
参考链接：
英文:
As another approach, from your question, I guessed the following 2 patterns.
Pattern 1:
If you have already known the names of all named ranges like Alpha and Beta, how about the following formula? In this case, the cell "A1" has a value of "first". If the inputted value is not included in the named ranges, "Not found." is returned.
=LET(
alpha,COUNTIF(Alpha,"="&A1)>0,
beta,COUNTIF(Beta,"="&A1)>0,
IFS(AND(alpha,beta),"Alpha,Beta",alpha,"Alpha",beta,"Beta",TRUE,"Not found.")
)
Pattern 2:
If you have not known the names of all named ranges, how about the following custom function? In this case, please copy and paste the following script to the script editor of Spreadsheet and save the script. When you use this script, please put a custom function of =SAMPLE("first"). By this, the name of the named range is returned. If the inputted value is not included in the named ranges, "Not found." is returned.
const SAMPLE = search =>
SpreadsheetApp.getActiveSheet().getNamedRanges().reduce((ar, r) => {
if (r.getRange().createTextFinder(search).matchEntireCell(true).findNext()) {
ar.push(r.getName());
}
return ar;
}, []).join(",") || "Not found.";
References:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论