Awọn iṣẹ Excel fun ṣiṣẹ pẹlu awọn ọna asopọ ati awọn ọna

In this example, you will learn all about the Excel functions for working with links and arrays, such as VPR, GPR, Die fara han, INDEX и AYE.

VPR

iṣẹ VPR (VLOOKUP) looks up the value in the leftmost column of the table and returns the value of the cell in the specified column of the same row.

1. Fi sii iṣẹ kan VPR:

   =ВПР(A2;$E$4:$G$7;3;ЛОЖЬ)

   =VLOOKUP(A2,$E$4:$G$7,3,FALSE)

   alaye:

   • iṣẹ VPR looking for a value ID (104) in the leftmost column of the range $E$4:$G$7 and returns the value from the third column of the same row (since the third argument of the function is 3).
   • The fourth argument of the function is IPORO (FALSE) – this means that either an exact match will be found, or an error message will be displayed # N / A (#N/A).
2. Drag mouse to copy function VPR from cell B2 down the column to the cell B11.Explanation: When we copy a function VPR down, absolute link $E$4:$G$7 remains unchanged, while the relative reference A2 awọn ayipada si A3, A4, A5 ati bẹbẹ lọ.

GPR

The function works in a similar way. GPR (HLOOKUP):

Die fara han

iṣẹ Die fara han (MATCH) returns the position of the searched value in the given range:

alaye:

• ọrọ Yellow occupies the third position in the range E4:E7.
• The third function argument is optional. If you enter a value for this argument 0 (zero), then the function will return the position of the element that exactly matches the searched value (A2). If an exact match is not found, the function will return an error. # N / A (#N/A).

INDEX

iṣẹ INDEX (INDEX) returns a given value from a two-dimensional or one-dimensional range.

Explanation: Meaning 92 is at the intersection of the line 3 ati ọwọn 2 ni ibiti E4:F7.

Explanation: Meaning 97 ti wa ni be 3 place in the range E4:E7.

AYE

iṣẹ AYE (CHOOSE) selects a value from the list at the given position number.

Explanation: Word Ọkọ wa ni ipo 3.