如何在没有VBA或宏的情况下在Excel中循环？
是否可以在不安装 VBA 或宏的情况下迭代（循环）Excel 中的一组行？网络搜索没有产生任何有用的结果。
=IF('testsheet'!$C$1 <= 99,'testsheet'!$A$1,"") &
IF('testsheet'!$C$2 <= 99, 'testsheet'!$A$2,"") &
IF('testsheet'!$C$3 <= 99, 'testsheet'!$A$3,"") &
... and so on through !$C$40, !$A$40 ...
事实上，我必须在每个单元格中重复上述代码 40 次，并且有超过 200 个单元格需要该代码。
我很擅长 PHP/SQL，但刚刚学习 Excel。
Is it possible to iterate (loop) a group of rows in Excel without VBA or macros installed? A web search didn't yield anything helpful.
=IF('testsheet'!$C$1 <= 99,'testsheet'!$A$1,"") &
IF('testsheet'!$C$2 <= 99, 'testsheet'!$A$2,"") &
IF('testsheet'!$C$3 <= 99, 'testsheet'!$A$3,"") &
... and so on through !$C$40, !$A$40 ...
As it is, I'll have to repeat the above code 40 times in each cell and I have over 200 cells which need the code.
I'm pretty good with PHP/SQL, but just learning Excel.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（9）
获得公式结果的方法是从一个新工作表开始。
在单元格 A1 中输入公式
将该单元格复制到第 40 行
在单元格 B1 中输入公式
在单元格 B2 中输入公式 将该
单元格复制到第 40 行。
您想要的值现在位于第 40 行的该列中。
这并不是您想要的答案，但这是完成任务的最快方法无需创建接受范围并进行计算的自定义公式（这会更有趣）。
The way to get the results of your formula would be to start in a new sheet.
In cell A1 put the formula
Copy that cell down to row 40
In cell B1 put the formula
In cell B2 put the formula
Copy that cell down to row 40.
The value you want is now in that column in row 40.
Not really the answer you want, but that is the fastest way to get things done excel wise without creating a custom formula that takes in a range and makes the calculation (which would be more fun to do).
我只是在寻找类似的东西：
我想对每个奇数行列进行求和。
SUMIF有两个可能的范围，求和范围，以及考虑条件的范围。此函数将考虑单元格是否位于B 范围为“=1”，只有当 A 单元格为“=1”时，才会对相应的 A 单元格求和。
为了让“=1”返回B范围，我把它放在B中：
然后自动填充以获得要填充的模数，您可以在此处放置可计算的标准以获得循环遍历每个单元格所需的SUMIF或SUMIFS条件。
比 ARRAY 的东西更容易，并且隐藏循环的后端！
I was just searching for something similar:
I want to sum every odd row column.
SUMIFhas TWO possible ranges, the range to sum from, and a range to consider criteria in.This function will consider if a cell in the B range is "=1", it will sum the corresponding A cell only if it is.
To get "=1" to return in the B range I put this in B:
Then auto fill down to get the modulus to fill, you could put and calculatable criteria here to get the SUMIF or SUMIFS conditions you need to loop through each cell.
Easier than ARRAY stuff and hides the back-end of loops!
我自己来回答这个问题（如果我错了，请纠正我）：
如果没有安装 VBA/启用宏，则不可能在 Excel 中迭代一组行（如数组）。
Going to answer this myself (correct me if I'm wrong):
It is not possible to iterate over a group of rows (like an array) in Excel without VBA installed / macros enabled.
您可以在计算电子表格上的某个位置创建一个表，该表对每对单元格执行此操作，并使用自动填充来填充它。
将该表中的结果聚合到结果单元格中。
然后，引用结果的 200 个单元格可以引用保存聚合结果的单元格。在最新版本的 Excel 中，您可以命名结果单元格并以这种方式引用它，以便于阅读。
You could create a table somewhere on a calculation spreadsheet which performs this operation for each pair of cells, and use auto-fill to fill it up.
Aggregate the results from that table into a results cell.
The 200 so cells which reference the results could then reference the cell that holds the aggregation results. In the newest versions of excel you can name the result cell and reference it that way, for ease of reading.
当您有以不同速率重复的变量循环时，请添加更多列。我不确定你想做什么，但我认为我已经做了一些可以应用的事情。
在 Excel 中创建单个循环非常简单。它实际上为您完成了这项工作。在新工作簿上尝试此操作
当您向下拖动时，A3 将自动变为“=A2+1”。第一步不必那么明确。如果您在 A2 中输入“2”，Excel 将自动识别该模式并进行计数，但如果我们希望 B1-B5 为“100”，B5-B10 为“200”（以同样的方式向上计数），您就会明白为什么明确知道如何去做很重要。在这种情况下，您只需在B1中输入：
“= B1+100”在您向下拖动时将自动变为“=B2+100”等，所以基本上每5增加一次行无限。要在 A 列中制作数字 1-5 的循环：
因此，现在我们让 A 列重复数字 1-5，而 B 列每 5 个单元格增加 100。您可以使用与 A 列相同的方法重复 B 列，例如，数字 100-900例如，一种产生多个变量的每种可能组合的方法。向下拖动列，它们会无限地执行此操作。我没有明确解决给定的场景，但如果您遵循这些步骤并理解它们，这个概念应该为您提供涉及添加更多列并连接或使用它们作为变量的问题的答案。
Add more columns when you have variable loops that repeat at different rates. I'm not sure explicitly what you're trying to do, but I think I've done something that could apply.
Creating a single loop in Excel is prettty simple. It actually does the work for you. Try this on a new workbook
A3 will automatically be "=A2+1" as you drag down. The first steps don't have to be that explicit. Excel will automatically recognize the pattern and count if you just put "2" in A2, but if we want B1-B5 to be "100" and B5-B10 to be "200" (counting up the same way) you can see why knowing how to do it explicitly matters. In this scenario, You just enter:
B7 will automatically be "=B2+100" etc. as you drag down, so basically it increases every 5 rows infinitely. To make a loop of numbers 1-5 in column A:
So, now we have column A repeating numbers 1-5 while column B is increasing by 100 every 5 cells.You could make column B repeat, for instance, the numbers 100-900 in using the same method as you did with column A as a way to produce, for instance, each possible combination with multiple variables. Drag down the columns and they'll do it infinitely. I'm not explicitly addressing the scenario given, but if you follow the steps and understand them, the concept should give you an answer to the problem that involves adding more columns and concactinating or using them as your variables.
@Nat 给出了很好的答案。但既然没有办法缩短代码，为什么不使用连接来“生成”您需要的代码呢？当我懒惰时（在单元格中输入整个代码），它对我有用。
所以我们需要的只是识别模式 >使用Excel构建模式“结构”>添加“=”并将其粘贴到目标单元格中。
例如，你想要实现（我的意思是，在单元格中输入）：
我没有输入它，我只是使用“&”用于组合 Excel 中排列的单元格的符号（另一个文件，不是我们正在处理的文件）。
请注意：
part1 >
IF('testsheet'!$C$第 2 部分 >
1 到 40第 3 部分 >
<= 99,'testsheet'!$A$part4>
1到40part5>
,"")&现在选择A2:I2，并将其向下拖动。请注意，数字按添加的每行递增，并且生成的文本会逐个单元格、逐行组合。
结果=您想要的代码。
我使用 Excel/OpenOfficeCalc 来帮助我为我的项目生成代码。对我有用，希望对其他人有帮助。 （：
@Nat gave a good answer. But since there is no way to shorten a code, why not use concatenate to 'generate' the code you need. It works for me when I'm lazy (at typing the whole code in the cell).
So what we need is just identify the pattern > use excel to built the pattern 'structure' > add " = " and paste it in the intended cell.
For example, you want to achieve (i mean, enter in the cell) :
I didn't type it, I just use "&" symbol to combine arranged cell in excel (another file, not the file we are working on).
Notice that :
part1 >
IF('testsheet'!$C$part2 >
1 to 40part3 >
<= 99,'testsheet'!$A$part4 >
1 to 40part5 >
,"") &Now select A2:I2 , and drag it down. Notice that the number did the increment per row added, and the generated text is combined, cell by cell and line by line.
Result = code as you intended.
I've used Excel/OpenOfficeCalc to help me generate code for my projects. Works for me, hope it helps for others. (:
当这个问题在 2011 年首次提出时，这是一个很难回答的问题。然而，Excel 从那时起就已经成长起来。
原始问题的简单解决方案是使用以下公式：
但是，OP还询问我们是否可以在电子表格公式中进行循环。对于 Excel 2023，答案是肯定的。 REDUCE 函数将循环遍历数组，但它确实有一些限制； REDUCE 只能将一个变量的值从一次迭代传递到下一次迭代。以下公式将计算 A1:A40 中的行数；有 40 行。然后它将在数组 C1:C40 和 A1:A40 上从 1 到 40 循环。
这两种解决方案在 2011 年都还没有推出。使用递归 LAMBDA 函数也可以实现电子表格公式循环。
This was a difficult question to answer when it was first asked in the year 2011. However, Excel has grown up since then.
The easy solution to the original problem is to use the following formula:
However, the OP also asked if we can make a loop within a spreadsheet formula. With Excel 2023, the answer is yes. The REDUCE function will loop through arrays, but it does have some limitations; REDUCE can only pass the value of one variable from one iteration to the next. The following formula will count the number of rows in A1:A40; it's 40 rows. Then it will loop from 1 to 40 over the arrays C1:C40 and A1:A40.
Neither of these solutions was available back in 2011. Spreadsheet formula loops are also possible with recursive LAMBDA functions.
我在寻找一种方法来制定预测项目的时间表时遇到了这个问题。我希望能够设置项目持续时间，并从一周开始到结束填充计划的小时数。本质上我想做一个 for 循环。由于我不想使用 VBA，因此需要构建一个外部表，其中列出了每个周数，并具有相应数量的列，周数后面有 1。然后，我构建了附加表并使用以下公式：
IFERROR删除空白单元格中的任何错误并提供更好的外观。第一个
IF检查项目开始日期是否等于或大于日历行中的周日期。第二个
IF检查项目持续时间是否少于一年，然后在表2中找到正确的行，然后列索引基于COUNTIF，它计算从项目行开头算起的非零数，然后加 2（如果没有非零，则索引的列将为第 2 列，即 Table2 中第一列包含 1 的列）。当两个
IF语句的计算结果都为 TRUE 时，结果为 1，乘以每周的时间估计值。如果任一
IF语句为FALSE，则结果为0。我还将使用此公式的区域中的数字格式化为“自定义
0;-0;;@”，以从视图中删除任何纯零。主表
表 2
I came across this while looking for a way to build a schedule to forecast projects. I wanted to be able to set project durations and have the planned amount of hours populate from the week start to the end. Essentially I wanted to do a for loop. Since I did not want to use VBA, I needed to build an external table that listed each week number and have a corresponding number of columns with 1s following the week number. I then built the attached sheet and used the following formula:
IFERRORremoves any errors from blank cells and gives a nicer look.1st
IFchecks to see if project start date is equal to or greater than the week date in calendar row.2nd
IFchecks to see if the project duration is less than a year, then finds the correct line in Table 2, then the column index is based onCOUNTIF, which counts the number of non zeros from the beginning of the project row, and then adds 2 (if there are no non zeros, then the column indexed will be column 2, which is the first column with 1s from Table2).When both
IFstatements evaluate to TRUE, then 1 is the result, which is multiplied by the Time Estimate per Week.If either
IFstatement isFALSEthen0is the result.I also formatted the numbers in the area where I use this formula to Custom
0;-0;;@to remove any plain zeros from view.Main Sheet
Table 2
检查这个简单的公式来制作鼻涕虫。它可能会帮助你。它获取单元格中的值并生成第一个单词第二个单词的 slug。
=LOWER(TEXTJOIN("-",TRUE,(TEXTSPLIT(B2,{" ","&","/"},,TRUE,1))))
此公式将使用 B2 中的值并删除所有空格的 &和 / 字符并将其替换为 - 字符。
Check this easy formula out for making slugs. It might help you out. It takes the value in a cell and makes a first-word-second-word slug.
=LOWER(TEXTJOIN("-",TRUE,(TEXTSPLIT(B2,{" ","&","/"},,TRUE,1))))
This formula will use the value in B2 and remove any spaces the & and / characters and replace them with the - character.