Quantcast

[belajar-excel] Rumus pengganti fungsi pivot

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[belajar-excel] Rumus pengganti fungsi pivot

Niky_Nugraha
Dear Para Master,

Mohon pencerahan untuk rumus pengganti fungsi pivot.

Saya lampirkan filenya dimana biasanya saya menggunakan pivot dengan
patokan mengambil angka pada kolom code sebagai patokannya.


Terima kasih atas bantuannya.






ttd

niky n


==========================================================
Please consider the environment before printing this email.

This message is confidential and may also be privileged.  If you are not
the intended recipient, please notify me by return e-mail and delete this
message from your system. If you are not the intended recipient, any use
by you of this message is strictly prohibited. We reserve our right to
pursue any available legal action based on the prevailing laws due to any
misuse of this e-mail or information contained herein.

Rumus pengganti pivot.xls (33K) Download Attachment
Kid
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [belajar-excel] Rumus pengganti fungsi pivot

Kid
Administrator
Hai Niky,

Untuk menyusun kolom kode menggunakan array formula dengan syarat adalah
yang lebih tinggi dari nilai kode sebelumnya.
Jika kode pertama diletakkan pada cell L29, maka array formula L29 :
=IF(N(L28)+(LEFT(L28)="K"),MIN(IF(MID($A$2:$A$14,5,1)*1>N(L28),1*MID($A$2:$A$14,5,1))),"")

file terlampir.

Wassalam,
Kid.




On Mon, May 20, 2013 at 3:12 PM, <[hidden email]> wrote:

> **
>
>
>
> Dear Para Master,
>
> Mohon pencerahan untuk rumus pengganti fungsi pivot.
>
> Saya lampirkan filenya dimana biasanya saya menggunakan pivot dengan
> patokan mengambil angka pada kolom code sebagai patokannya.
>
>
> Terima kasih atas bantuannya.
>
>
>
>
>
>
> ttd
>
> niky n
>
>
> ==========================================================
> Please consider the environment before printing this email.
>
> This message is confidential and may also be privileged.  If you are not
> the intended recipient, please notify me by return e-mail and delete this
> message from your system. If you are not the intended recipient, any use by
> you of this message is strictly prohibited. We reserve our right to pursue
> any available legal action based on the prevailing laws due to any misuse
> of this e-mail or information contained herein.
>  
>

re-Rumus pengganti pivot.xls (50K) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [belajar-excel] Rumus pengganti fungsi pivot

BenQ
Dear para master

Saya tertarik dengan pengganti pivot ini tapi setelah saya coba di kasus
saya tidak berhasil..maklum masih baru belajar exel..
mohon untuk pencerahannya..
sheet 3 adalah hasil gabungan pivot GPD dan WMS.
saya lampirkan filenya..

Regards,
Bambang trianto


2013/5/20 Mr. Kid <[hidden email]>

> **
>
>
> Hai Niky,
>
> Untuk menyusun kolom kode menggunakan array formula dengan syarat adalah
> yang lebih tinggi dari nilai kode sebelumnya.
> Jika kode pertama diletakkan pada cell L29, maka array formula L29 :
>
> =IF(N(L28)+(LEFT(L28)="K"),MIN(IF(MID($A$2:$A$14,5,1)*1>N(L28),1*MID($A$2:$A$14,5,1))),"")
>
> file terlampir.
>
> Wassalam,
> Kid.
>
>
>
>
> On Mon, May 20, 2013 at 3:12 PM, <[hidden email]> wrote:
>
>> **
>>
>>
>>
>> Dear Para Master,
>>
>> Mohon pencerahan untuk rumus pengganti fungsi pivot.
>>
>> Saya lampirkan filenya dimana biasanya saya menggunakan pivot dengan
>> patokan mengambil angka pada kolom code sebagai patokannya.
>>
>>
>> Terima kasih atas bantuannya.
>>
>>
>>
>>
>>
>>
>> ttd
>>
>> niky n
>>
>>
>> ==========================================================
>> Please consider the environment before printing this email.
>>
>> This message is confidential and may also be privileged.  If you are not
>> the intended recipient, please notify me by return e-mail and delete this
>> message from your system. If you are not the intended recipient, any use by
>> you of this message is strictly prohibited. We reserve our right to pursue
>> any available legal action based on the prevailing laws due to any misuse
>> of this e-mail or information contained herein.
>>
>
>  
>

pivot.xlsx (32K) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[belajar-excel] Re: Rumus pengganti fungsi pivot

summonery
Administrator
In reply to this post by Niky_Nugraha
Selain menggunakan Pivot dan formula, coba juga menggunakan fitur Subtotal yang kalau di Excel 2007 ada pada Data > Outline > Subtotal.
Tahapannya:
1. Hapus Grand Totalnya...
2. Tambahkan kolom baru dan isi dengan kategori Code yang baru, misal SMER1A menjadi 1, SMER2 menjadi 2, SMER3A menjadi 3, dst
3. Sorot/blok area data.
4. Data > Outline > Klik Subtotal
5. Atur sesuai yang diinginkan:
   @At each change in: pilih kolom Code baru yang berisi 1,2,3,dst
   @Use function: sum
   @Add Subtotal to: centang semua kolom yang berisi angka (case, basic, dst)
   @Centang Summary below data

Maaf tidak melampirkan file excel.


>semoga bermanfaat


--- In [hidden email], Niky_Nugraha@... wrote:

>
> Dear Para Master,
>
> Mohon pencerahan untuk rumus pengganti fungsi pivot.
>
> Saya lampirkan filenya dimana biasanya saya menggunakan pivot dengan
> patokan mengambil angka pada kolom code sebagai patokannya.
>
>
> Terima kasih atas bantuannya.
>
>
>
>
>
>
> ttd
>
> niky n
>
>
> ==========================================================
> Please consider the environment before printing this email.
>
> This message is confidential and may also be privileged.  If you are not
> the intended recipient, please notify me by return e-mail and delete this
> message from your system. If you are not the intended recipient, any use
> by you of this message is strictly prohibited. We reserve our right to
> pursue any available legal action based on the prevailing laws due to any
> misuse of this e-mail or information contained herein.
>


Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [belajar-excel] Rumus pengganti fungsi pivot

Niky_Nugraha
In reply to this post by Kid
Mr. Kid,

berhasil, terima kasih atas bantuannya.

ttd

niky




From:
"Mr. Kid" <[hidden email]>
To:
BeExcel <[hidden email]>
Date:
20-05-2013 10:32 PM
Subject:
[Caution: Message contains Suspicious URL content] Re: [belajar-excel]
Rumus pengganti fungsi pivot
Sent by:
[hidden email]



 
Hai Niky,

Untuk menyusun kolom kode menggunakan array formula dengan syarat adalah
yang lebih tinggi dari nilai kode sebelumnya.
Jika kode pertama diletakkan pada cell L29, maka array formula L29 :
=IF(N(L28)+(LEFT(L28)="K"),MIN(IF(MID($A$2:$A$14,5,1)*1>N(L28),1*MID($A$2:$A$14,5,1))),"")

file terlampir.

Wassalam,
Kid.




On Mon, May 20, 2013 at 3:12 PM, <[hidden email]> wrote:
 

Dear Para Master,

Mohon pencerahan untuk rumus pengganti fungsi pivot.

Saya lampirkan filenya dimana biasanya saya menggunakan pivot dengan
patokan mengambil angka pada kolom code sebagai patokannya.


Terima kasih atas bantuannya.






ttd

niky n


==========================================================
Please consider the environment before printing this email.

This message is confidential and may also be privileged.  If you are not
the intended recipient, please notify me by return e-mail and delete this
message from your system. If you are not the intended recipient, any use
by you of this message is strictly prohibited. We reserve our right to
pursue any available legal action based on the prevailing laws due to any
misuse of this e-mail or information contained herein.                

[attachment "re-Rumus pengganti pivot.xls" deleted by Niky
Nugraha/Asia Division/Manulife]




==========================================================
Please consider the environment before printing this email.

This message is confidential and may also be privileged.  If you are not
the intended recipient, please notify me by return e-mail and delete this
message from your system. If you are not the intended recipient, any use
by you of this message is strictly prohibited. We reserve our right to
pursue any available legal action based on the prevailing laws due to any
misuse of this e-mail or information contained herein.

_1_0A17E4AC0A17E0AC0040887447257B72 (3K) Download Attachment
_1_0B2A03100B2A00040040887447257B72 (60 bytes) Download Attachment
Kid
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [belajar-excel] Rumus pengganti fungsi pivot

Kid
Administrator
In reply to this post by BenQ
Hai Bambang,

Pada pivot table consolidation range, disyaratkan bahwa kolom pertama tabel
data adalah kolom kunci. Jika tabel memiliki banyak kolom yang menjadi
penentu makna data, maka kolom kuncinya berupa composite key yang bisa
disusun berupa gabungan nilai setiap kolom pembentuk makna data.

Penyusunan composite key sebaiknya menggunakan delimiter (karakter pemisah
antar elemen penyusun composite key) agar mudah di parse.

Pivot Table consolidation range bisa menjadi tabel bantu.

File terlampir.

Wassalam,
Kid.



On Tue, May 21, 2013 at 1:52 AM, Bambang trianto wibowo <
[hidden email]> wrote:

> **
>
>
> Dear para master
>
> Saya tertarik dengan pengganti pivot ini tapi setelah saya coba di kasus
> saya tidak berhasil..maklum masih baru belajar exel..
> mohon untuk pencerahannya..
> sheet 3 adalah hasil gabungan pivot GPD dan WMS.
> saya lampirkan filenya..
>
> Regards,
> Bambang trianto
>
>
> 2013/5/20 Mr. Kid <[hidden email]>
>
>  **
>>
>>
>> Hai Niky,
>>
>> Untuk menyusun kolom kode menggunakan array formula dengan syarat adalah
>> yang lebih tinggi dari nilai kode sebelumnya.
>> Jika kode pertama diletakkan pada cell L29, maka array formula L29 :
>>
>> =IF(N(L28)+(LEFT(L28)="K"),MIN(IF(MID($A$2:$A$14,5,1)*1>N(L28),1*MID($A$2:$A$14,5,1))),"")
>>
>> file terlampir.
>>
>> Wassalam,
>> Kid.
>>
>>
>>
>>
>> On Mon, May 20, 2013 at 3:12 PM, <[hidden email]> wrote:
>>
>>> **
>>>
>>>
>>>
>>> Dear Para Master,
>>>
>>> Mohon pencerahan untuk rumus pengganti fungsi pivot.
>>>
>>> Saya lampirkan filenya dimana biasanya saya menggunakan pivot dengan
>>> patokan mengambil angka pada kolom code sebagai patokannya.
>>>
>>>
>>> Terima kasih atas bantuannya.
>>>
>>>
>>>
>>>
>>>
>>>
>>> ttd
>>>
>>> niky n
>>>
>>>
>>> ==========================================================
>>> Please consider the environment before printing this email.
>>>
>>> This message is confidential and may also be privileged.  If you are not
>>> the intended recipient, please notify me by return e-mail and delete this
>>> message from your system. If you are not the intended recipient, any use by
>>> you of this message is strictly prohibited. We reserve our right to pursue
>>> any available legal action based on the prevailing laws due to any misuse
>>> of this e-mail or information contained herein.
>>>
>>
>>
>  
>

re-pivot.xlsx (73K) Download Attachment
Loading...