Placeholder Image

字幕列表 影片播放

  • in this video, we'll learn how to use another Interesting Excel function.

  • Indirect.

  • In addition, we'll see how it could be applied in combination with V.

  • Look up.

  • Previously, we've said it is common practice to use cell references in formulas and indirect, if implemented properly, can be helpful when working with cell references.

  • Please follow this lecture carefully to understand how indirect functions.

  • It can be tricky, but don't worry.

  • We are here for you.

  • Indirect returns, the reference indicated by a text string.

  • So the input we should have in the brackets must be a text string.

  • What do I mean by that?

  • Suppose I was interested in the precise number of staff employees working in Company A.

  • And I wanted to obtain that number by using indirect.

  • In other words, I am interested in the number written in cell C six, Right.

  • Let's see what happens if I select Cell C six.

  • As the input of the function Excel displays an error message.

  • Why?

  • Because see, six is not a text string, and hence the function can't provide a result.

  • Look at how the result changes.

  • If I play, see six within inverted commas, See, and why did it work?

  • Because putting C six in inverted commas makes indirect Read the cell as a text string indirect simply returns the information contained in the reference we've specified as text here.

  • It gives us the number contained in C six.

  • An alternative way of including C six as a text string is to have the letter C in inverted commas.

  • And then Khan captain ate it with the number six.

  • This way, Excel will read it as a text string and you can obtain an answer.

  • And the trick here is if I change the number within the function, its results will change as well.

  • If I modify the letter, the outcome will be different.

  • And this is very important.

  • You'll see why in a second.

  • So this is the first thing to remember about indirect.

  • As long as the content between parentheses is texts forming a cell reference the function.

  • We'll get you there.

  • All right.

  • Excellent.

  • Let's move on to the next sheet.

  • Now I will show you how to combine V look up and indirect.

  • Here we have a different table which provides information about the annual salary of employees.

  • What I would like is a small table showing the number of employees and salaries of management and the staff of company D.

  • So I'll start by typing V.

  • Look up.

  • The look of value will be management.

  • This reference must not change.

  • When I dragged the formula to the right so I'll fix the column.

  • Okay.

  • Earlier, we learned how to name cell range is remember, here we can use these ranges as an input for indirect.

  • The best part is these ranges are also the source table for our V Look up function.

  • Therefore, the input for indirect can be the cell where we've written personnel.

  • It points to the entire source table above and to copy the function downwards, I must fix the road number of this reference.

  • Okay, finally, him these ranges The information about company D is in the fifth column.

  • So I'll type five here.

  • We're looking for an exact match and the function is complete.

  • Let's press OK hands see the result correct.

  • The number of managers and company D is three.

  • Let's paste the formula in the other three cells and see what happens.

  • The numbers look okay.

  • Technically, you just witnessed an example of dynamic lookup tables dynamic because the V look up function we have here can work with two source tables.

  • You see that the look of table is responsive and collects the data correctly?

  • The same would happen if I had Maur ranges and I referred to them in these cells here.

  • Why did I write five to indicate the column?

  • We're looking in this way.

  • If I changed the company I am interested in, I would still get the results for company D here.

  • And this number must be changed manually.

  • Ah, function can automate the process.

  • We'll learn more about it in our next lesson.

  • Thanks for watching.

in this video, we'll learn how to use another Interesting Excel function.

字幕與單字

單字即點即查 點擊單字可以查詢單字解釋

B1 中級

INDIRECT Excel函數。如何工作和何時使用[高級Excel] (INDIRECT Excel Function: How it works and when to use it [Advanced Excel])

  • 3 1
    林宜悉 發佈於 2021 年 01 月 14 日
影片單字