logo标签列表

如何在Excel中不使用VBA或宏实现循环？

excelexcel-formula
48

在不安装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。

我已经阅读了那个页面，但没有看到任何有用的信息。似乎你几乎需要安装VBA才能进行复杂的迭代。对于OpenOffice有什么想法吗？ - Jeff
据我所读，Open Office 中的 Calc 比 Excel 有更多限制，但我无法从经验上说话。至于 VBA，它是使得 MS Office 产品如此有用的原因。你的意思是你根本不能运行脚本吗？ - Fionnuala
8个回答
23

要得到你的公式结果，需要在新工作表中开始。

在单元格A1中输入公式

=IF('testsheet'!C1 <= 99,'testsheet'!A1,"")

将该单元格复制到第40行。 在单元格B1中放入公式。

=A1
在单元格B2中输入公式。
=B1 & A2

将该单元格复制到第40行。

你所需的值现在位于第40行的该列中。

这并不是你想要的答案，但这是在Excel中最快完成任务而不创建一个接受范围并进行计算的自定义公式（尽管更有趣）的方法。

9

我正在寻找类似的东西：

我想求出每一列奇数行的总和。

SUMIF 有两个可能的范围，一个是要从中求和的范围，另一个是要考虑标准的范围。

SUMIF(B1:B1000,1,A1:A1000)

这个函数会考虑B范围内的单元格是否为“=1”，只有对应的A单元格为"=1"时才会进行求和。

为了让B范围内返回“=1”，我将以下内容放在B中：

=MOD(ROWNUM(B1),2)

然后自动向下填充以获取模数填充，您可以在此处放置和可计算的条件，以获取您需要循环遍历每个单元格的SUMIF或SUMIFS条件。

比ARRAY更容易，并隐藏了循环的后端！

如果您有多个条件需要检查，请查看SUMIFS。SUMIFS 函数 - Roshana Pitigala
7

我来回答这个问题（如果有错误请纠正）：

在没有安装VBA或启用宏的情况下，无法在Excel中迭代一组行（例如数组）。

1
你是正确的。但是，即使没有VBA，你仍然可以合理地减少当前经历的痛苦的相当大一部分。(使用VBA，该解决方案是微不足道的。) 如果你有兴趣，我可以发布一个回答来解决这个问题，但如果你真的只想要一个是或否的答案，那么答案是“否”，我就不会打扰你了。此外，“没有安装VBA”是指您不想启用宏吗？据我所知，VBA始终“安装”，并内置于Excel中。只是有时组织有禁止其使用的政策。(我在一家小公司工作，这从未发生过。) - jtolle
Jtolle，没错，请尽管发布任何对此有帮助的内容。你说得对，宏被禁用是为了明显的安全原因。 - Jeff
1
我认为@Nat给了你一个相当好的答案。如果你是Excel新手，请注意他的答案使用了相对引用，而不是你的绝对引用。在Excel中，沿列或行复制带有相对引用的公式是惯用法。（Excel会自动正确执行，无需更改每个公式的文本。）我个人认为，在遵循正确的“不要重复自己”的编程实践时，这也算是“仅用于一处”的代码。 - jtolle
1

@Nat 给出了一个好的答案。但由于没有缩短代码的方法，为什么不使用连接来“生成”所需的代码呢？当我懒得在单元格中输入整个代码时，这对我很有效。

因此，我们所需要的就是确定模式 > 使用 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,"") &IF('testsheet'!$C$4 <= 99,'testsheet'!$A$4,"") &IF('testsheet'!$C$5 <= 99,'testsheet'!$A$5,"") &IF('testsheet'!$C$6 <= 99,'testsheet'!$A$6,"") &IF('testsheet'!$C$7 <= 99,'testsheet'!$A$7,"") &IF('testsheet'!$C$8 <= 99,'testsheet'!$A$8,"") &IF('testsheet'!$C$9 <= 99,'testsheet'!$A$9,"") &IF('testsheet'!$C$10 <= 99,'testsheet'!$A$10,"") &IF('testsheet'!$C$11 <= 99,'testsheet'!$A$11,"") &IF('testsheet'!$C$12 <= 99,'testsheet'!$A$12,"") &IF('testsheet'!$C$13 <= 99,'testsheet'!$A$13,"") &IF('testsheet'!$C$14 <= 99,'testsheet'!$A$14,"") &IF('testsheet'!$C$15 <= 99,'testsheet'!$A$15,"") &IF('testsheet'!$C$16 <= 99,'testsheet'!$A$16,"") &IF('testsheet'!$C$17 <= 99,'testsheet'!$A$17,"") &IF('testsheet'!$C$18 <= 99,'testsheet'!$A$18,"") &IF('testsheet'!$C$19 <= 99,'testsheet'!$A$19,"") &IF('testsheet'!$C$20 <= 99,'testsheet'!$A$20,"") &IF('testsheet'!$C$21 <= 99,'testsheet'!$A$21,"") &IF('testsheet'!$C$22 <= 99,'testsheet'!$A$22,"") &IF('testsheet'!$C$23 <= 99,'testsheet'!$A$23,"") &IF('testsheet'!$C$24 <= 99,'testsheet'!$A$24,"") &IF('testsheet'!$C$25 <= 99,'testsheet'!$A$25,"") &IF('testsheet'!$C$26 <= 99,'testsheet'!$A$26,"") &IF('testsheet'!$C$27 <= 99,'testsheet'!$A$27,"") &IF('testsheet'!$C$28 <= 99,'testsheet'!$A$28,"") &IF('testsheet'!$C$29 <= 99,'testsheet'!$A$29,"") &IF('testsheet'!$C$30 <= 99,'testsheet'!$A$30,"") &IF('testsheet'!$C$31 <= 99,'testsheet'!$A$31,"") &IF('testsheet'!$C$32 <= 99,'testsheet'!$A$32,"") &IF('testsheet'!$C$33 <= 99,'testsheet'!$A$33,"") &IF('testsheet'!$C$34 <= 99,'testsheet'!$A$34,"") &IF('testsheet'!$C$35 <= 99,'testsheet'!$A$35,"") &IF('testsheet'!$C$36 <= 99,'testsheet'!$A$36,"") &IF('testsheet'!$C$37 <= 99,'testsheet'!$A$37,"") &IF('testsheet'!$C$38 <= 99,'testsheet'!$A$38,"") &IF('testsheet'!$C$39 <= 99,'testsheet'!$A$39,"") &IF('testsheet'!$C$40 <= 99,'testsheet'!$A$40,"")

我没有输入它，我只是使用"&"符号来组合Excel中排列的单元格（另一个文件，不是我们正在处理的文件）。

请注意：

part1 > IF('testsheet'!$C$

part2 > 1 to 40

part3 > <= 99,'testsheet'!$A$

part4 > 1 to 40

part5 > ,"") &

  • 将part1输入到A1，part3输入到C1，part输入到E1。
  • 在A2中输入" = A1 "，在C2中输入" = C1 "，在E2中输入" = E1 "。
  • 在B2中输入" = B1+1 "，在D2中输入" = D1+1 "。
  • 在G2中输入" =A2&B2&C2&D2&E2 "。
  • 在I2中输入" =I1&G2 "。

现在选择A2:I2，然后向下拖动。注意，每行增加的数字是自动递增的，生成的文本是逐个单元格和逐行合并的。

  • 复制I41的内容，
  • 粘贴到其他地方，在前面添加“=”，删除多余的&和后面的内容。

结果=您想要的代码。

我使用Excel / OpenOfficeCalc来帮助我为项目生成代码。对我有用，希望对他人也有所帮助。 (:

1
当您有不同速率重复的变量循环时，请添加更多列。我不确定您要做什么，但我认为我已经做了一些可以应用的事情。
在Excel中创建单个循环非常简单。它实际上会为您完成工作。在新工作簿上尝试以下操作：
1. 在A1中输入“1” 2. 在A2中输入“=A1+1”
当您向下拖动时，A3将自动变为“=A2+1”。第一步不必那么明确。如果您只在A2中放置“2”，Excel将自动识别模式并计数，但是如果我们希望B1-B5为“100”且B5-B10为“200”（以相同的方式计数），则可以看出知道如何明确地执行此操作的原因。在这种情况下，您只需输入：
3. 在B1中输入“100”，拖动到B5 4. 在B6中输入“=B1+100”
当您向下拖动时，B7将自动变为“=B2+100”等，因此基本上每5行无限增加。要在A列中创建1-5的数字循环：
在单元格A6中输入“=A1”。当您向下拖动时，由于Excel的工作方式，它将自动在单元格A7中变为“=A2”，以此类推。因此，现在我们有列A重复数字1-5，而列B每5个单元格增加100。您可以使用与列A相同的方法使列B重复，例如使用数字100-900，以便生成多个变量的每个可能组合。向下拖动列，它们会无限制地执行此操作。我没有明确解决给定情况，但是如果您按照步骤并理解它们，该概念应该能够为您提供涉及添加更多列并将它们连接或使用它们作为变量的问题的答案。
1
你可以在计算表格的某个地方创建一个表格，为每对单元格执行此操作，并使用自动填充来填充它。
将该表格的结果聚合到一个结果单元格中。
然后，引用结果的200多个单元格可以引用保存聚合结果的单元格。在最新版本的Excel中，您可以命名结果单元格并以此方式引用它，以便更容易阅读。
0
检查一下这个简单的公式，用于生成slug。它可能对你有所帮助。它会将单元格中的值转换为以第一个单词和第二个单词连接的slug。
=LOWER(TEXTJOIN("-",TRUE,(TEXTSPLIT(B2,{" ","&","/"},,TRUE,1))))
这个公式将使用B2单元格中的值，并删除任何空格、&和/字符，并用-字符替换它们。
0
我在寻找一种建立项目预测计划的方法时发现了这个。我希望能够设置项目持续时间，并且从周开始到结束，计划工时能够自动填充。本质上，我想要使用一个循环。由于我不想使用VBA，所以我需要建立一个外部表格，列出每周的编号，并在周编号后面有相应数量的列，每列都填充1。然后我建立了附加的表格，并使用以下公式：
=IFERROR(IF($G14<=L$4, IF($H14<53,VLOOKUP($H14,Table2[#All],(COUNTIF($K14:K14, ">0")+2),), 0)*$E14, 0), 0)

IFERROR会将空单元格中的任何错误删除，并使其显示更加美观。

第一个IF语句用于检查项目开始日期是否等于或晚于日历行中的周日期。

第二个IF语句用于检查项目持续时间是否小于一年，然后找到表2中的正确行，列索引基于COUNTIF函数，该函数计算项目行开头非零值的数量，然后再加上2（如果没有非零值，则列索引将为2，即表2中第一个包含1的列）。

当两个IF语句都评估为TRUE时，结果为1，乘以每周的时间估计。

如果任一IF语句为FALSE，则结果为0。

我还将在使用此公式的区域中的数字格式设置为自定义0;-0;;@，以便将纯零值从视图中删除。

主要表格 表格2

出于好奇，我尝试将这个循环函数简化为只有函数而没有引用外部表格的形式，但是找不到简单的方法来实现。我一直遇到循环引用的问题。向读者发出挑战：这个函数能否只作为一个函数存在，而不是函数和表格的组合？ - Ryan Denman
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接