Hai Berni,
Maaf, saya kurang paham dengan kalimat ini :
'apa artinya row (pada fungsi MATCH) diisi dengan angka 0'
rasanya tidak ada row pada fungsi match.
Untuk Index, coba lihat di Excel Help...
Saya copas-ken deh... Lihatnya jangan cuma bagian Array Form, tapi baca
seluruhnya, termasuk bagian Reference Form...
;)
INDEX function
[image: Show All]Show All
[image: Hide All]Hide All
Returns a value or the reference to a value from within a table or range.
There are two forms of the INDEX function: the array (array: Used to build
single formulas that produce multiple results or that operate on a group of
arguments that are arranged in rows and columns. An array range shares a
common formula; an array constant is a group of constants used as an
argument.) form and the reference form.
If you want to Then see
Return the value of a specified cell or array of cells Array form <#Array
form>
Return a reference to specified cells Reference form <#Reference form>
------------------------------
Array form
Returns the value of an element in a table or an array (array: Used to
build single formulas that produce multiple results or that operate on a
group of arguments that are arranged in rows and columns. An array range
shares a common formula; an array constant is a group of constants used as
an argument.), selected by the row and column number indexes.
Use the array form if the first argument to INDEX is an array constant.
*INDEX*(*array*,row_num,column_num)
*Array* is a range of cells or an array constant.
-
If array contains only one row or column, the corresponding row_num or
column_num argument is optional.
-
If array has more than one row and more than one column, and only
row_num or column_num is used, INDEX returns an array of the entire row or
column in array.
*Row_num* selects the row in array from which to return a value. If
row_num is omitted, column_num is required.
*Column_num* selects the column in array from which to return a value. If
column_num is omitted, row_num is required.
*Remarks*
- If both the row_num and column_num arguments are used, INDEX returns
the value in the cell at the intersection of row_num and column_num.
- If you set row_num or column_num to 0 (zero), INDEX returns the array
of values for the entire column or row, respectively. To use values
returned as an array, enter the INDEX function as an array formula (array
formula: A formula that performs multiple calculations on one or more sets
of values, and then returns either a single result or multiple results.
Array formulas are enclosed between braces { } and are entered by pressing
CTRL+SHIFT+ENTER.) in a horizontal range of cells for a row, and in a
vertical range of cells for a column. To enter an array formula, press
CTRL+SHIFT+ENTER.
-
Row_num and column_num must point to a cell within array; otherwise,
INDEX returns the #REF! error value.
*Example 1*
The example may be easier to understand if you copy it to a blank worksheet.
[image: Show]How to copy an example
1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.
* Note * Do not select the row or column headers.
[image: Selecting an example from Help]
Selecting an example from Help
3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that
return the results, press CTRL+` (grave accent), or on the *Formulas*
tab, in the *Formula Auditing* group, click the *Show Formulas* button.
1
2
3
A B
Data Data
Apples Lemons
Bananas Pears
Formula Description (Result)
=INDEX(A2:B3,2,2) Value at the intersection of the second row and second
column in the range (Pears)
=INDEX(A2:B3,2,1) Value at the intersection of the second row and first
column in the range (Bananas)
*Example 2*
The example may be easier to understand if you copy it to a blank worksheet.
[image: Show]How to copy an example
1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.
* Note * Do not select the row or column headers.
[image: Selecting an example from Help]
Selecting an example from Help
3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that
return the results, press CTRL+` (grave accent), or on the *Formulas*
tab, in the *Formula Auditing* group, click the *Show Formulas* button.
1
2
3
A B
Formula Description (Result)
=INDEX({1,2;3,4},0,2) Value in the first row, second column in the array
constant (2)
Value in the second row, second column in the array constant (4)
* Note * The formula in the example must be entered as an array formula.
After copying the example to a blank worksheet, select the range A2:A3
starting with the formula cell. Press F2, and then press CTRL+SHIFT+ENTER.
If the formula is not entered as an array formula, the single result is 2.
Top of Page <#backtotop>
------------------------------
Reference form
Returns the reference of the cell at the intersection of a particular row
and column. If the reference is made up of nonadjacent selections, you can
pick the selection to look in.
*INDEX*(*reference*,row_num,column_num,area_num)
*Reference* is a reference to one or more cell ranges.
-
If you are entering a nonadjacent range for the reference, enclose
reference in parentheses.
-
If each area in reference contains only one row or column, the row_num
or column_num argument, respectively, is optional. For example, for a
single row reference, use INDEX(reference,,column_num).
*Row_num* is the number of the row in reference from which to return a
reference.
*Column_num* is the number of the column in reference from which to
return a reference.
*Area_num* selects a range in reference from which to return the
intersection of row_num and column_num. The first area selected or entered
is numbered 1, the second is 2, and so on. If area_num is omitted, INDEX
uses area 1.
-
For example, if reference describes the cells (A1:B4,D1:E4,G1:H4), then
area_num 1 is the range A1:B4, area_num 2 is the range D1:E4, and area_num
3 is the range G1:H4.
*Remarks*
- After reference and area_num have selected a particular range, row_num
and column_num select a particular cell: row_num 1 is the first row in the
range, column_num 1 is the first column, and so on. The reference returned
by INDEX is the intersection of row_num and column_num.
-
If you set row_num or column_num to 0 (zero), INDEX returns the
reference for the entire column or row, respectively.
- Row_num, column_num, and area_num must point to a cell within
reference; otherwise, INDEX returns the #REF! error value. If row_num and
column_num are omitted, INDEX returns the area in reference specified by
area_num.
- The result of the INDEX function is a reference and is interpreted as
such by other formulas. Depending on the formula, the return value of INDEX
may be used as a reference or as a value. For example, the formula
CELL("width",INDEX(A1:B2,1,2)) is equivalent to CELL("width",B1). The CELL
function uses the return value of INDEX as a cell reference. On the other
hand, a formula such as 2*INDEX(A1:B2,1,2) translates the return value of
INDEX into the number in cell B1.
*Example*
The example may be easier to understand if you copy it to a blank worksheet.
[image: Show]How to copy an example
1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.
* Note * Do not select the row or column headers.
[image: Selecting an example from Help]
Selecting an example from Help
3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that
return the results, press CTRL+` (grave accent), or on the *Formulas*
tab, in the *Formula Auditing* group, click the *Show Formulas* button.
1
2
3
4
5
6
7
8
9
10
11
A B C
Fruit Price Count
Apples 0.69 40
Bananas 0.34 38
Lemons 0.55 15
Oranges 0.25 25
Pears 0.59 40
Almonds 2.80 10
Cashews 3.55 16
Peanuts 1.25 20
Walnuts 1.75 12
Formula Description (Result)
=INDEX(A2:C6,2,3) The intersection of the second row and third column in
the range A2:C6, which is the content of cell C3. (38)
=INDEX((A1:C6,A8:C11),2,2,2) The intersection of the second row and second
column in the second area of A8:C11, which is the content of cell B9. (3.55)
=SUM(INDEX(A1:C11,0,3,1)) The sum of the third column in the first area of
the range A1:C11, which is the sum of C1:C6. (216)
=SUM(B2:INDEX(A2:C6,5,2)) The sum of the range starting at B2, and ending
at the intersection of the fifth row and the second column of the range
A2:A6, which is the sum of B2:B6. (2.42)
2016-06-26 20:37 GMT+07:00 Berni Karanza [email protected]
[belajar-excel] <[email protected]>:
>
>
> Dear Mr.Kid,
>
> Kalo boleh tau pada formula:
>
> =COUNTIFS($D$3:$D$15;D19;*INDEX($F$3:$M$15;0;MATCH(F19;$F$2:$M$2;0))*;E19)
>
> Bagaimana penjelasannya, sehingga pada formula (teks merah) bisa
> menghasilkan criteria_range
> apa artinya row (pada fungsi MATCH) diisi dengan angka 0, biasanya
> kombinasi fungsi INDEX/MATCH
> saya pakai sebagai alternatif VLOOKUP, gak kepikir kalo bisa untuk
> menghasilkan range tertentu.
>
> Terima kasih,
>
> Salam,
>
> Berni K
>
> Salam,
>
> Berni K
>
> 2016-06-26 20:21 GMT+07:00 Berni Karanza <[email protected]>:
>
>> Dear Mr.Kid,
>>
>> Terima kasih, alternatif formula yang diberikan juga sangat membantu.
>>
>> Salam,
>>
>> Berni K
>>
>> 2016-06-26 18:24 GMT+07:00 'Mr. Kid' [email protected] [belajar-excel] <
>> [email protected]>:
>>
>>>
>>>
>>> Hai Berni,
>>>
>>> File terlampir dengan beberapa fungsi yang memungkinkan.
>>>
>>> Regards,
>>> Kid
>>>
>>>
>>> 2016-06-26 15:19 GMT+07:00 Berni Karanza [email protected]
>>> [belajar-excel] <[email protected]>:
>>>
>>>>
>>>>
>>>> Dear master,
>>>>
>>>> Mohon penjelasannya, bagaimana menulis formula pada cell (F21) untuk
>>>> menghitung banyaknya jumlah "shift" (D321) pada tabel C4:L17 berdasarkan
>>>> referensi pada tabel C20:E21 (data dinamis)
>>>>
>>>>
>>>>
>>>> Dalam contoh di atas berarti berapa banyak jumlah "2S" pada tabel
>>>> dengan EGI
>>>> D155 pada tanggal 02 Jun 2016.
>>>>
>>>> Terima kasih, atas bantuannya.
>>>>
>>>> Salam,
>>>>
>>>> Berni K
>>>>
>>>>
>>>
>>
>
>
