英文:
Google sheets custom function not working with an array
问题
我正在创建一个名为MODINVERSE()的Google表格自定义函数，它接受一个乘数和一个模数，以找到它的模乘逆。如果你输入一个数组，我希望它也能工作。这意味着如果我输入一个数组，比如MODINVERSE(A1:A3,X)，它的输出将是一个列出示例数组A1:A3中每个值的模乘逆的数组。
脚本：
function MODINVERSE(multiplier, modifier) {
var output = [];
if (modifier <= 1){
output.push([0])
} else {
if (multiplier == 0) {
output.push([0])
} else {
for (var X = 1; X < modifier; X++) {
if (((multiplier % modifier) * (X % modifier)) % modifier == 1) {
output.push([X])
}
}
}
}
return output;
}
英文:
I am making a custom function for google sheets called MODINVERSE() which takes a multiplier and a modifier to find its modular multiplicative inverse. I got it to work if you plug in a single value, but I want it to work for an array input. This means if I put in an array such as MODINVERSE(A1:A3,X) its output would be an array that lists the modular multiplicative inverse for each of the three values in the example array of A1:A3.
Script:
function MODINVERSE(multiplier, modifier) {
var output = [];
if (modifier <= 1){
output.push([0])
} else {
if (multiplier == 0) {
output.push([0])
} else {
for (var X = 1; X < modifier; X++) {
if (((multiplier % modifier) * (X % modifier)) % modifier == 1) {
output.push([X])
}
}
}
}
return output;
}
答案1
得分: 0
遍历公式通过一个范围。
你所遗漏的是遍历范围。如下所示，在我的示例中。尽管我们进行了交流，但由于没有可重现的示例，我可能会遗漏某些内容。请随时使用此示例以获得您所需的输出。
示例代码：
function MODINVERSE(range, modifier) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("YourSheetName"); //请根据您的工作表名称进行编辑
var rangeToProcess = sheet.getRange(range); //它用于获取要处理的范围值，以及要使用该公式的次数。
var multiplier = rangeToProcess.getValues();
var output = [];
for(i = 0; i < multiplier.length; i++ ){ //这是它将循环遍历您的范围的部分
if (modifier <= 1){ //在这里只是复制了您的逻辑，进行了最小的编辑，并使用了索引，确定循环的位置
output.push([0])
} else {
if (multiplier[i] == 0) {
output.push([0])
} else {
for (var y = 1; y < modifier; y++) {
if (((multiplier[i] % modifier) * (y % modifier)) % modifier == 1) {
output.push([y])
}
}
}
}
} return output;}
使用这个自定义函数，请注意，您必须使用以下格式为您的公式。=MODINVERSE("A1:A3",5) 必须使用引号将您的范围解析为字符串，您的修饰符应该是一个整数，如果它不是一个范围，请使用代码作为参考，并使用 getValue() 函数将其分配给一个变量。
参考链接：getValues
英文:
Iterate Formula Through a range.
What you are missing is iterating through the range. As shown in my example below. Despite our conversation I might be missing something as we don't have a reproducible example. Please feel free to use this example to get your desired output.
Sample Code:
function MODINVERSE(range, modifier) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("YourSheetName"); //Please edit based on your sheet name
var rangeToProcess = sheet.getRange(range); // it is for getting the range values to process along with getting how many times the formula will be used.
var multiplier = rangeToProcess.getValues();
var output = [];
for(i = 0; i< multiplier.length;i++ ){ // This is the Part where it is going to loop through your range
if (modifier <= 1){ // just copied your logic here with minimal edit and using the index if where the loop is
output.push([0])
} else {
if (multiplier[i] == 0) {
output.push([0])
} else {
for (var y = 1; y < modifier; y++) {
if (((multiplier[i] % modifier) * (y % modifier)) % modifier == 1) {
output.push([y])
}
}
}
}
} return output;}
Using this custom function please take note that you have to use this format for your formula. =MODINVERSE("A1:A3",5) There should be a quotation mark for your range to be parsed as string, your modifier should be an int, if it is not in and a range please use the code as reference and use a getValue() function to assign it to a variable.
Reference:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论