B1 中級 4 タグ追加 保存
We've seen several function combinations so far in this lesson.
Well present another one that can be useful.
I suppose you'd like to create a table with the number of people working in management and admin personnel for company D and E.
Again, we can use match to define the column number we need in the V look of function.
I am sure you now know the first part of the V.
Look up, function by heart.
This will be our look of value and we will fix it's column reference.
The entire table above is the look up array.
This is when match comes in play.
The look of value of match will be this cell when I copy man paste this function.
I need this reference to change on Lee when I move to the right along the horizontal axis and not to change when I moved down.
Therefore I will anchor It's row reference Great.
In the next step, we have to define the area or the look up array where Excel will try to find the look of value.
In our case, that means company D.
Therefore, I will select the header of the source table and fix it.
It is not supposed to move when we copy and paste the function, and this pretty much does it all right.
It worked okay for the number of managers in Company D, let's apply this formula to the other three cells in the table.
It worked.
Therefore, one could combine V, look up and match in the same formula from all that you've learned.
Until this moment, you would probably agree that nesting a match function within a V look up differs from nesting columns within a V.
Look up, though.
Now, if I replace the name of a company with another one, the number of workers in the management and admin personnel would change accordingly.
This is not the case with the columns function as it works fine on Lee for adjacent companies in the source table.


VLOOKUP and MATCH another useful functions combination [Advanced Excel]

林宜悉 2020 年 3 月 10 日 に公開
  1. 1. クリック一つで単語を検索


  2. 2. リピート機能


  3. 3. ショートカット


  4. 4. 字幕の表示/非表示


  5. 5. 動画をブログ等でシェア


  6. 6. 全画面再生


  1. クイズ付き動画


  1. クリックしてメモを表示

  1. UrbanDictionary 俚語字典整合查詢。一般字典查詢不到你滿意的解譯,不妨使用「俚語字典」,或許會讓你有滿意的答案喔