????? ????? ?? ???????? ?? ???? Excel

???? ???????: 324861 - ??? ???????? ???? ????? ????? ??? ???????.
????? ???????? ????? ????? ??????? ?????? ?????? ???????
????? ???? | ?? ????

?? ??? ??????

??????

???? ??? ??????? ???? ????? ????? ????? ?? ???????? ?? ???? (?? ???? ?? ???????) ???????? ????? ????? ?????? ?? Microsoft Excel. ????? ?????? ??? ??????? ??? ?????? ?????? ??? ??? ???????.

????? ????? ???? ?????

?????? ??? ?????? ???? ??? ????? ?????? ?? Excel ??????? ???????? ??? ???? ?????? ??????? ??? ??? ?? ?????? A ?????? ??? ??? ????? ?? ?????? c. ?????? ???? ????? ???? ?? ?????? ???????? ??????? ?? ???? ??? Excel ?????.

??? ????? ?????? ?????? ??? ???? ????? ???? ???? ?????? E2. ????? ????? ?????? ?? ?? ???? ????? ?? ??? ???? ?????.
?? ??? ??????????? ??? ??????
ABCDE
1???????????????? ?? ??????
2????50128????
3????20119
4????10122
5???30129

??????? ???????

?????? ??? ??????? ????????? ??????? ???? Excel ????? ?????:
?? ??? ??????????? ??? ??????
??????????????????
Table_Array????? ?????? ????.A2:C5
Lookup_Value???? ??? ????? ???? ?? ?????? ?????? ?? Table_Array. E2
Lookup_Array
-??-
Lookup_Vector		???? ??????? ???? ????? ??? ??? ??? ?????.A2: A5
Col_Index_Num??? ?????? ?? Table_Array ??? ?? ??? ????? ?????? ???????? ?.3 (?????? ?????? ?? Table_Array)
Result_Array
-??-
Result_Vector		?????? ???? ? ????? ??? ?? ?? ???? ???? ???. ??? ?? ???? ???? ??? Lookup_Array ?? Lookup_Vector.C2:C5
Range_Lookup ???? ?????? (TRUE ?? FALSE). If ????? TRUE ?? ?????? ??? ????? ????? ??????. ??? ???? FALSE? ???? ????? ?? ?????? ????.???
Top_Cell??? ?? ?????? ???? ???? ????? ???? ???????. ??? ?? ???? Top_Cell ??? ???? ?? ???? ?? ??????? ?????????. ????? ???? ??????? #VALUE! ???? ???.
Offset_Col ??? ?? ??? ???????? ??? ?????? ?? ??????? ???? ????? ?? ?????? ??????? ?????? ?? ?????? ??????? ???. ?? ??? ??? ???? ??????? "5" ?????? Offset_Col ???? ?? ?????? ??????? ?????? ?? ???? ?????? ???? ????? ??? ???? ????. ???? ?? ???? Offset_Col ????? ?? ????? (?? ??? ???? ???? ?????) (????? ???? ??? ???? ???? ?????).

?????

LOOKUP()

???? LOOKUP ???? ?? ???? ?? ?? ???? ?? ???? ???????? ????? ?? ??? ?????? ?? ??? ?? ?? ????.

????? ?????? ???? ?? ???? ???? ?????? ????? :
= (?????Lookup_Value,Lookup_Vector,Result_Vector)

???? ?????? ??????? ??? ???? ?? ?????? ???? ?????:
=LOOKUP(E2,A2:A5,C2:C5)
?????? "Mary" ?????? ?? ?????? E2 ?????? ?????? ?? "Mary" ?? ???? ????? (?????? A). ????? ?????? ?? ?????? ?? ??? ?? ?? ???? ??????? (?????? C). ??? "Mary" ?? ???? 4? ???? LOOKUP ?????? ?????? ?? ???? 2 ?? ?????? C (22).

?????? ????? ?????? LOOKUP ??? ??????.
????? ?? ????????? ??? ???? ?????? ???? ??? ??? ??????? ?????? ?????? ?? "????? ??????? ? Microsoft":
324986
(http://support.microsoft.com/kb/324986/ )
????? ??????? ?????? LOOKUP ?? Excel

VLOOKUP()

??? ??????? ?????? VLOOKUP ?? ??? ????? ????? ??? ??? ???????? ?? ???????. ??? ?????? ?????? ?? ???? ?? ?????? ???? ?????? ?????????? ??? ???????? ?? ???? ???? ?? ??? ????. ????? ??????? VLOOKUP ????? ?? ?????? ?? ???? ???? ?? ????? ?? ?? ??? ?????. ?????? ?????? ?????? ???? ?? ???????? ???? ?? ??? ?????.

?? ??? ???? ?? ???? ???? ?????? VLOOKUP :
= VLOOKUP (Lookup_Value? Table_Array,Col_Index_Num,Range_Lookup)
???? ?????? ??????? ??? ???? ?? ?????? ???? ?????:
=VLOOKUP(E2,A2:C5,3,FALSE)
?????? "Mary" ?????? ?? ?????? E2 ?????? ?????? ?? "Mary" ?? ?????? ???? ?????? (?????? A). ????? ?????? ?? ?????? ?? ??? ?? ?? Column_Index. ?????? ??? ?????? "3" ? Column_Index (?????? C). ??? "Mary" ?? ???? 4? VLOOKUP ?????? ?????? ?? ???? 4 ?? ?????? C (22).
????? ?? ????????? ??? ???? VLOOKUP? ???? ??? ??? ??????? ?????? ?????? ?? "????? ??????? ? Microsoft":
181213
(http://support.microsoft.com/kb/181213/ )
????? ??????? VLOOKUP ?? HLOOKUP ????? ?? ????? ?????

INDEX() ? MATCH()

????? ??????? ?????? ?????? ?????? ??? ?????? ??? ??? ??????? ??? ??????? "???" ?? VLOOKUP.

?????? ???? ??? ???? ?????? ???? ???? INDEX ? MATCH ?????? ??? ??????? ??? ????? ? VLOOKUP ?? ??????? ???????:
= (????Table_Array? (????????Lookup_Value,Lookup_Array? 0)?Col_Index_Num)

???? ?????? ??????? ??? ???? ?? ?????? ???? ?????:
=INDEX(A2:C5,MATCH(E2,A2:A5,0),3)
?????? ?????? ???? "Mary" ?? ?????? E2 ????? ?? "Mary" ?? ?????? A. ?? ????? ?????? ???????? ?? ??? ???? ?? ?????? c. ??? "Mary" ?? ???? 2? ???? ?????? ?????? ?? ???? 2 ?? ?????? C (22).

?????? ??? ??? ?? ?? ??????? ?? Lookup_Array?????? Lookup_Value ????? ??? ?????? ("Mary")? ????? #N/
????? ?? ????????? ??? ???? INDEX? ???? ??? ??? ??????? ?????? ?????? ?? "????? ??????? ? Microsoft":
324988
(http://support.microsoft.com/kb/324988/ )
????? ??????? ???? INDEX ????? ?? ?????? ?? ????

OFFSET() ?MATCH()

????? ??????? ????? ?????? ?????? ??? ?????? ??? ??????? ?????? ?? ?????? ??????.

?????? ???? ??? ???? ?????? ???? ???? ????? ? ???????? ?????? ??? ??????? ??? ????? ? VLOOKUP:
= (???????top_cell? (????????Lookup_Value,Lookup_Array? 0)?Offset_Col)
???? ??? ?????? ?? ??? Mary ?? ???? ??? ?????:
=OFFSET(A1,MATCH(E2,A2:A5,0),2)
?????? "Mary" ?????? ?? ?????? E2 ?????? ?????? ?? "Mary" ?? ?????? a. ?????? ?? ????? ?????? ???????? ?? ??? ???? ???? ?????? ??? ?????? (?????? C). ??? "Mary" ?? ?????? A? ???? ?????? ?????? ?? ???? 4 ?? ?????? C (22).
????? ?? ????????? ??? ???? OFFSET? ???? ??? ??? ??????? ?????? ?????? ?? "????? ??????? ? Microsoft":
324991
(http://support.microsoft.com/kb/324991/ )
????? ??????? ???? OFFSET
???? ??? ?????? | ????? ???????

???????

???? ???????: 324861 - ????? ??? ??????: 26/????? ??????/1434 - ??????: 4.0
????? ???
  • Microsoft Office Excel 2003
  • Microsoft Office Excel 2007
  • Microsoft Excel 2010
????? ??????: 
kbhowtomaster kbhowto kbmt KB324861 KbMtar
????? ????
???: ??? ????? ??? ?????? ???????? ?????? ????? ???? ????? ?????????? ????? ?? ????????? ?????? ????. ???? ???? ?????????? ???? ?? ???????? ???????? ?????? ????????? ????? ????????? ???????? ????? ???????? ?????? ?? ?????? ??? ?? ???????? ???????? ?? ????? ??????? ?????? ??? ??????? ?????? ??. ?????? ?? ???? ??? ??????? ???????? ????? ?? ???? ????? ?????? ??? ????? ??? ????? ??????? ?? ????? ?? ?????? ??? ??? ??????? ??????? ?? ????? ????? ????? ????? ?????. ?? ????? ???? ?????????? ??????? ??? ????? ?? ??????? ?? ????? ?????? ?? ??? ????? ?? ????? ??????? ?? ???????? ?? ??? ???????. ???? ???? ?????????? ???????? ??? ????? ?????? ??????? ??????
???? ??? ????? ??????? ?????? ??????????324861
(http://support.microsoft.com/kb/324861/en-us/ )
???? ??? ?????? | ????? ???????

????? ???????

 
???? ??? ?????????? ??? ??????