课程: Excel Essential Training (Microsoft 365)

Copying a formula into adjacent cells

课程: Excel Essential Training (Microsoft 365)

开始一个月试用

Copying a formula into adjacent cells

“

- [Instructor] On this worksheet called Copy Formulas, we've got a formula in place in Cell B4. One habit you want to develop as you work with Excel is when you see a sea of numbers like this here, as you click on a cell, get in the habit of looking in the formula bar. It's above the column letters right up here. And you want to view this in the same way that you look out your rear view mirror in the car as you're driving around. Is that a formula there in Cell G2? No, that's a value, it's 420. What about this to the right? Oh, that's a function using sum. And how about this one here? Now you don't do that for every single cell, of course, but you quickly realize that many times when you're looking at an Excel worksheet, there are a lot of formulas there. Now, we've written the formula here in the previous movie to subtract these two cells, and in a certain sense, we want to do the same thing here. If we describe it in the same way, we want to subtract the two cells above it. Same thing here and here and here and here. And if we had 12 months, we'd be doing this 11 more times. We certainly do not want to write that formula 11 more times. And surely one of Excel's best shortcuts is the idea that we can copy a formula, sometimes you'll hear the word relatively, we're not copying the formula exactly. We don't want this formula here, to subtract those two cells, we want it to subtract these two. So when you copy a formula, Excel adjusts the addresses. We copy from the lower right-hand corner, that's that fill handle, hold down the left mouse button, drag across into June, and it's all done. And you can check out the results. Remember, you can click on one and see it up in the formula bar. You can also double click if you've zoomed in and it's larger, you can see it better here maybe. And you quickly develop that habit of looking for formulas understanding where there are, and there are techniques for rapidly finding them too. Previously there's a formula written in cell H2, as I double click here, reminder of what was done there. Notice that the formula is wider than the cell itself. That's okay, not a problem. Do we want to do the same kind of thing here? This formula is doing what? Well, it's adding up G2, and F2, and E2 and so on. But phrased differently, what is it doing? It's adding up the six cells to its immediate left. Do we want to do that here? Yes, and here? Down here? Yes. So we simply in the same way point to that fill handle, hold down the left mouse button, drag it down to here, and we've got our totals here. As I double click, you can see what's happening, and up here, double click. And with average, the same general idea, what is this average doing? It's dividing the total by six. We want to do the same thing here and here, take the same approach. Drag that fill handle, point to the lower right-hand corner, hold down the left mouse button, drag downward, and we've got our totals there. And with these in place, if this data, for example, is still volatile, we make a change. If I make a change here to that 120, 125, no I haven't pressed Enter yet, but when I do, you know the profit number is going to change below it. And what will happen off to the right? The total for sales will change, the average will change. And down under Profits, the total there and the average will change as well. We're not trying to say that it's a goal to have one cell change and then have five others or hundreds of others change. But in some worksheets, change a cell, a lot happens. And as I press Enter now, we see those changes taking place. So many times in Excel, formulas are based on writing a single formula and then copying those formulas into adjacent cells.

内容