????? ????? ?? ???????? ?? ???? 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). ??? ???? TRUE ?? ?????? ??? ????? ????? ??????. ??? ??? FALSE? ????? ?? ????? ???.???
Top_Cell??? ?? ?????? ???? ???? ?? ????? ???? ???????. ??? ?? ???? Top_Cell ??? ???? ?? ???? ?? ??????? ?????????. ????? ???? ??????? #VALUE! ???? ???.
Offset_Col ??? ?? ??? ???????? ??? ?????? ?? ??????? ???? ???? ?? ???? ???? ?????? ??????? ?????? ?? ??????. ??? ???? ??????? "5" ?????? Offset_Col ???? ?? ?????? ??????? ?????? ?? ?????? ???? ????? ??? ???? ????. Offset_Col ???? ????? (?? ??? ???? ???? ?????) ?? ????? (?? ??? ???? ???? ?????).

?????

LOOKUP()

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

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

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

?????? ????? ?????? LOOKUP ??? ??????.
????? ?? ????????? ??? ???? ?????? ???? ??? ??? ??????? ?????? ?????? ?? "????? ??????? ? Microsoft":
324986 ????? ??????? ?????? LOOKUP ?? Excel

VLOOKUP()

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

?? ??? ???? ?? ???? ???? ?????? VLOOKUP :
= VLOOKUP (Lookup_Value? Table_Array,Col_Index_Num,Range_Lookup)
???? ?????? ??????? ??? Mary ?? ???? ??? ?????:
=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 ????? ??????? VLOOKUP ?? HLOOKUP ????? ?? ????? ?????

INDEX() ? MATCH()

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

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

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

?????? ??? ???? ?? ?? ??????? ???????? ?? Lookup_Array ?????? Lookup_Value ("????")? ??? ?????? ?????? n/A #.
????? ?? ????????? ??? ???? INDEX? ???? ??? ??? ??????? ?????? ?????? ?? "????? ??????? ? Microsoft":
324988 ????? ??????? ???? INDEX ????? ?? ?????? ?? ????

OFFSET() ?MATCH()

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

?? ??? ?? ???? ????? ???? ??? ??????? ? ?????? ?????? ??? ??????? ??? ????? ? VLOOKUP:
= (???????top_cell? (??????Lookup_Value,Lookup_Array0)?Offset_Col)
???? ??? ?????? ?? ??? Mary ?? ???? ??? ?????:
=OFFSET(A1,MATCH(E2,A2:A5,0),2)
?????? ?????? ???? "Mary" ?? ?????? E2 ????? ?? "Mary" ?? ?????? A. ????? ?????? ?? ?????? ?? ??? ???? ???? ?????? ??? ?????? (?????? C). ??? "????" ?? ?????? A? ???? ?????? ?????? ?? ???? 4 ?? ?????? C (22).
????? ?? ????????? ??? ???? OFFSET? ???? ??? ??? ??????? ?????? ?????? ?? "????? ??????? ? Microsoft":
324991 ????? ??????? ???? OFFSET

???????

???? ???????: 324861 - ????? ??? ??????: 04/?? ?????/1434 - ??????: 5.0
????? ???
  • Microsoft Office Excel 2003
  • Microsoft Office Excel 2007
  • Microsoft Excel 2010
????? ??????: 
kbhowtomaster kbhowto kbmt KB324861 KbMtar
????? ????
???: ??? ????? ??? ??????? ???????? ????? ?????????? ??????? ?????? ??? ??????? ?? ??? ??????? ?? ??????? ??? ??? ?? ???? ????????? ???? ??????? ???????(CTF) ?? ?? ???? ????? ????. ???? ?? ???? ?????????? ??? ???????? ???????? ???? ??? ?? ???????? ??????? ?? ??? ??????? ???????? ?? ??? ??????? ??????? ??? ?????? ?? ?????? ??? ???? ???????? ???????? ?? ????? ??????? ????? ????? ??????. ??? ?? ???????? ???????? ?? ????? ??? ????? ?? ???????? ?? ???? ????? ?? ?????. ?????? ??? ???? ?????????? ???? ?????? ?? ??? ????? ?? ??? ??? ?? ??????? ?? ??? ????? ?? ???? ????? ????? ?? ????? ??????? ???? ?? ???????? ?? ??? ???????.
???? ??? ????? ??????? ?????? ??????????324861

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

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com