Екцел функције за рад са везама и низовима

In this example, you will learn all about the Excel functions for working with links and arrays, such as ВПР, ГПР, ИЗЛОЖЕНИЈИ, ИНДЕКС и ИЗБОР.

ВПР

функција ВПР (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. Уметање функције ВПР:

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

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

   objašnjenje:

   • функција ВПР 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 ЛЕЖАЊЕ (FALSE) – this means that either an exact match will be found, or an error message will be displayed # Н / А (#Н/А).
2. Drag mouse to copy function ВПР from cell B2 down the column to the cell БКСНУМКС.Explanation: When we copy a function ВПР down, absolute link $E$4:$G$7 remains unchanged, while the relative reference A2 промене у A3, A4, A5 и тако даље.

ГПР

The function works in a similar way. ГПР (HLOOKUP):

ИЗЛОЖЕНИЈИ

функција ИЗЛОЖЕНИЈИ (MATCH) returns the position of the searched value in the given range:

objašnjenje:

• реч Жута occupies the third position in the range Е4: Е7.
• 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. # Н / А (#Н/А).

ИНДЕКС

функција ИНДЕКС (INDEX) returns a given value from a two-dimensional or one-dimensional range.

Explanation: Meaning 92 is at the intersection of the line 3 и колона 2 у опсегу E4:F7.

Explanation: Meaning 97 се налази 3 place in the range Е4: Е7.

ИЗБОР

функција ИЗБОР (CHOOSE) selects a value from the list at the given position number.

Explanation: Word Чамац is in position 3.