Sabtu, 02 Juli 2016

[belajar-excel] Digest Number 3903

1 Message

Digest #3903

Message

Fri Jul 1, 2016 8:04 pm (PDT) . Posted by:

"Berni Karanza" karanza.berni

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' mr.nmkid@gmail.com
[belajar-excel] <belajar-excel@yahoogroups.com> 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 karanza.berni@gmail.com
> [belajar-excel] <belajar-excel@yahoogroups.com>:
>
>>
>>
>> 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 <karanza.berni@gmail.com>:
>>
>>> 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' mr.nmkid@gmail.com [belajar-excel]
>>> <belajar-excel@yahoogroups.com>:
>>>
>>>>
>>>>
>>>> Hai Berni,
>>>>
>>>> File terlampir dengan beberapa fungsi yang memungkinkan.
>>>>
>>>> Regards,
>>>> Kid
>>>>
>>>>
>>>> 2016-06-26 15:19 GMT+07:00 Berni Karanza karanza.berni@gmail.com
>>>> [belajar-excel] <belajar-excel@yahoogroups.com>:
>>>>
>>>>>
>>>>>
>>>>> 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
>>>>>
>>>>>
>>>>
>>>
>>
>
>
============================================================
Pojok Lowongan Kerja yang disediakan milis :
http://milis-belajar-excel.1048464.n5.nabble.com/Pojok-Lowongan-Kerja-f5725753.html
*** Posting lowongan kerja : ke link tersebut dan klik New Topic
============================================================
bergabung ke milis (subscribe), kirim mail kosong ke: belajar-excel-subscribe@yahoogroups.com
posting ke milis, kirimkan ke: belajar-excel@yahoogroups.com
berkunjung ke web milis : http://tech.groups.yahoo.com/group/belajar-excel/messages
melihat file archive / mendownload lampiran : http://www.mail-archive.com/belajar-excel@yahoogroups.com/
atau (sejak 25-Apr-2011) bisa juga di : http://milis-belajar-excel.1048464.n5.nabble.com/
menghubungi moderators & owners: belajar-excel-owner@yahoogroups.com
keluar dari membership milis (UnSubscribe), kirim mail kosong ke : belajar-excel-unsubscribe@yahoogroups.com
---------------------------------------------------------------------

Tidak ada komentar:

Posting Komentar