Re: [belajar-excel] Menghitung jumlah data berdasarkan kolom dan baris tertentu

2016-07-01 Terurut Topik Berni Karanza karanza.be...@gmail.com [belajar-excel] 
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.nm...@gmail.com
[belajar-excel]  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

Re: [belajar-excel] Menghitung jumlah data berdasarkan kolom dan baris tertentu

2016-06-26 Terurut Topik 'Mr. Kid' mr.nm...@gmail.com [belajar-excel] 
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

Re: [belajar-excel] Menghitung jumlah data berdasarkan kolom dan baris tertentu

2016-06-26 Terurut Topik Berni Karanza karanza.be...@gmail.com [belajar-excel] 
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 :



> 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.nm...@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.be...@gmail.com
>> [belajar-excel] :
>>
>>>
>>>
>>> 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
>>>
>>>
>>
>>
>
>

Re: [belajar-excel] Menghitung jumlah data berdasarkan kolom dan baris tertentu

2016-06-26 Terurut Topik Berni Karanza karanza.be...@gmail.com [belajar-excel] 
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.nm...@gmail.com 
mailto:mr.nm...@gmail.com [belajar-excel] :
   
 Hai Berni,


File terlampir dengan beberapa fungsi yang memungkinkan.


Regards,

Kid


 
 2016-06-26 15:19 GMT+07:00 Berni Karanza karanza.be...@gmail.com 
mailto:karanza.be...@gmail.com [belajar-excel] :
   
 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

[belajar-excel] Menghitung jumlah data berdasarkan kolom dan baris tertentu [1 Attachment]

2016-06-26 Terurut Topik Berni Karanza karanza.be...@gmail.com [belajar-excel] 
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


Countifs.xlsx
Description: MS-Excel 2007 spreadsheet