Dear Mr. Kid
Terima kasih bantuan dan penjelasannya, koreksi yang saya maksud fungsi *index* bukan match :) Salam, Berni K On Sun, Jun 26, 2016 at 11:18 PM, 'Mr. Kid' [email protected] [belajar-excel] <[email protected]> wrote: > > > 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 > <#m_-6714374484287557594_Array+form> > Return a reference to specified cells Reference form > <#m_-6714374484287557594_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 <#m_-6714374484287557594_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 >>>>> >>>>> >>>> >>> >> > >
