How to loop in excel without VBA or macros?_问答_开发者

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,""开发者_如何学JAVA) &
... 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.

The way to get the results of your formula would be to start in a new sheet.

In cell A1 put the formula

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

Copy that cell down to row 40 In cell B1 put the formula

=A1

In cell B2 put the formula

=B1 & A2

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).

I was just searching for something similar:

I want to sum every odd row column.

SUMIF has TWO possible ranges, the range to sum from, and a range to consider criteria in.

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

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:

=MOD(ROWNUM(B1),2)

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!

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.

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.

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

Enter "1" in A1
Enter "=A1+1" in A2

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:

"100" in B1, drag through to B5 and
"=B1+100" in B6

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:

Enter "=A1" in cell A6. As you drag down, it will automatically be "=A2" in cell A7, etc. because of the way that Excel does things.

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 gave a good answer. But since there is no way to shorten a code, why not use contatenate 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) :

=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,"")

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 40

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

part4 > 1 to 40

part5 > ,"") &

Enter part1 to A1, part3 to C1, part to E1.
Enter " = A1 " in A2, " = C1 " in C2, " = E1 " in E2.
Enter " = B1+1 " in B2, " = D1+1 " in D2.
Enter " =A2&B2&C2&D2&E2 " in G2
Enter " =I1&G2 " in I2

Now select A2:I2 , and drag it down. Notice that the number did the increament per row added, and the generated text is combined, cell by cell and line by line.