Fungsi TRANPOSE

Pada artikel kali ini, saya akan membahas Fungsi Transpose(), dimana sangat berguna untuk memindahkan data dengan format Tabel yang berbeda, fungsi ini berguna jika data yang anda pindahkan berisi ratusan data. Fungsi Transpose ini merupakan Formula Array, yang mana di dalam penerapannya dengan menekan Tombol CTRL-SHIFT-ENTER atau saya singkat CSE.

Misalkan anda mempunyai tabel data seperti ini:

Tetapi Bos Anda menginnginkan Format Tabel seperti ini:

Hal ini menjadi problem bagi anda jika data yang anda punya berisi ratusan atau ribuan data, tetapi jangan kuatir, di dalam MS Excel telah menyediakan Fungsi Transpose untuk memindahkan data anda dengan cara yang praktis.

Langkah-langkahnya:

Silahkan Blok dulu Tabel

Setelah di-blok, silahkan anda langsung mengetikkan  =Transpose(

Kemudian anda blok Tabel Data anda, pada contoh disini, saya memisahkan Tabel Data dan Tabel Baru di dalam Lembar Kerja yang terpisah.

Setelah di-blok, anda tekan Tombol CTRL-SHIFT-ENTER secara bersamaan. Dan Voila.... data anda berpindah sesuai Tabel Database anda secara AJAIB...!



Download FIle Latihan

Penggunaan Fungsi SUMIFS

Anda pasti sudah sangat familiar dengan Fungsi VLOOKUP() dari MS Excel yang sangat berguna untuk mengambil data tertentu sesuai kriteria yang kita inginkan (Multiple Criteria). Di dalam MS Excel  2007 - 2010 terdapat fungsi SUMIFS() yang di dalam Contoh yang akan saya berikan berfungsi hampir sama dengan Fungsi VLOOKUP(). Kelemahan Fungsi ini tidak bisa menampilkan data TEKS, hanya NUMERIK saja.

Fungsi SUMIFS ini berguna jika kita mengambil data tertentu dengan 2 kriteria. Pada contoh diatas, Kriteria Pertama adalah SUPPLIER, kemudian Kriteria Kedua adalah ITEM.

Download File Latihan

Nama Depan dan Nama Belakang

Disini saya akan share beberapa formula untuk memisahkan Nama Depan, Nama Tengah dan Nama Belakang, serta formula untuk. Dan formula untuk konversi nama dari pola Nama Depan & Nama Belakang menjadi Nama Belakang & Nama Depan maupun sebaliknya, serta beberapa trik rumus yang lainnya. Satu-persatu kasusnya, yaitu

Kasus A

Rumus Nama Depan:
=LEFT(A2;SEARCH(" ";A2;1)-1)
Rumus Nama Belakang
=TRIM(RIGHT(SUBSTITUTE(A2;" ";REPT(" ";99));99))

Kasus B

Rumus Nama Depan:
=LEFT(A7;SEARCH(" ";A7;1)-1)
Rumus Nama Tengah:
=TRIM(MID(SUBSTITUTE(A7;" ";REPT(" ";99));100;99))
Rumus Nama Belakang:
=TRIM(RIGHT(SUBSTITUTE(A7;" ";REPT(" ";99));99))

Kasus C

Rumus menghilangkan sebutan atau gelar di depan nama:
=TRIM(RIGHT(A12;LEN(A12)-SEARCH(" ";A12)))

Kasus D

Rumus konversi:
=MID(A18;SEARCH(" ";A18)+1;50)&" "&LEFT(A18;SEARCH(" ";A18;1)-2)

Kasus E

Rumus konversi:
=TRIM(RIGHT(SUBSTITUTE(A24;" ";REPT(" ";99));99))&", "&LEFT(A24;(FIND("|";SUBSTITUTE(A24;" ";"|";2)))-1)

Kasus F

Rumus memisahkan teks nama yang tanpa spasi tetapi harus ada huruf kapitalnya pada setiap awal kata karena untuk penanda
=TRIM(MID($A30;(SMALL(FIND(0;SUBSTITUTE($A30;CHAR(ROW(INDIRECT("65:90")));0)&0);COLUMNS($A$1:A$1)));(SMALL(FIND(0;SUBSTITUTE($A30;CHAR(ROW(INDIRECT("65:90")));0)&0);COLUMNS($A$1:A$1)+1))-(SMALL(FIND(0;SUBSTITUTE($A30;CHAR(ROW(INDIRECT("65:90")));0)&0);COLUMNS($A$1:A2)))))

*rumus ini rumus array, you need to be sure press all together CTRL+SHIFT+ENTER button, ENTER alone don't works

Kasus G

Rumus menambahkan spasi pada teks nama yang tidak ada spasinya dan minimal harus ada huruf kapital setiap awal kata.
=TRIM(LEFT(A35;MIN(FIND(CHAR(64+COLUMN($A$1:$W$1));MID(A35;2;255)&(CHAR(64+COLUMN($A$1:$W$1))))))&" "&MID(A35;1+MIN(FIND(CHAR(64+COLUMN($A$1:$W$1));MID(A35;2;255)&(CHAR(64+COLUMN($A$1:$W$1)))));SMALL(FIND(CHAR(64+COLUMN($A$1:$W$1));MID(A35;2;255)&(CHAR(64+COLUMN($A$1:$W$1))));2)-MIN(FIND(CHAR(64+COLUMN($A$1:$W$1));MID(A35;2;255)&(CHAR(64+COLUMN($A$1:$W$1))))))&" "&LEFT(REPLACE(A35;1;SMALL(FIND(CHAR(64+COLUMN($A$1:$W$1));MID(A35;2;255)&(CHAR(64+COLUMN($A$1:$W$1))));2);"");MIN(FIND(CHAR(64+COLUMN($A$1:$W$1));MID(REPLACE(A35;1;SMALL(FIND(CHAR(64+COLUMN($A$1:$W$1));MID(A35;2;255)&(CHAR(64+COLUMN($A$1:$W$1))));2);"");2;255)&(CHAR(64+COLUMN($A$1:$W$1))))))&" "&SUBSTITUTE(SUBSTITUTE(REPLACE(A35;1;SMALL(FIND(CHAR(64+COLUMN($A$1:$W$1));MID(A35;2;255)&(CHAR(64+COLUMN($A$1:$W$1))));2);"");LEFT(REPLACE(A35;1;SMALL(FIND(CHAR(64+COLUMN($A$1:$W$1));MID(A35;2;255)&(CHAR(64+COLUMN($A$1:$W$1))));2);"");MIN(FIND(CHAR(64+COLUMN($A$1:$W$1));MID(REPLACE(A35;1;SMALL(FIND(CHAR(64+COLUMN($A$1:$W$1));MID(A35;2;255)&(CHAR(64+COLUMN($A$1:$W$1))));2);"");2;255)&(CHAR(64+COLUMN($A$1:$W$1))))));"");MID(REPLACE(A35;1;SMALL(FIND(CHAR(64+COLUMN($A$1:$W$1));MID(A35;2;255)&(CHAR(64+COLUMN($A$1:$W$1))));2);"");MIN(FIND(ROW($1:$10)-1;REPLACE(A35;1;SMALL(FIND(CHAR(64+COLUMN($A$1:$W$1));MID(A35;2;255)&(CHAR(64+COLUMN($A$1:$W$1))));2);"")&5^19));255);"")&" "&MID(REPLACE(A35;1;SMALL(FIND(CHAR(64+COLUMN($A$1:$W$1));MID(A35;2;255)&(CHAR(64+COLUMN($A$1:$W$1))));2);"");MIN(FIND(ROW($1:$10)-1;REPLACE(A35;1;SMALL(FIND(CHAR(64+COLUMN($A$1:$W$1));MID(A35;2;255)&(CHAR(64+COLUMN($A$1:$W$1))));2);"")&5^19));255))

rumus terakhir ini juga rumus array, jangan lupa caranya yaaa...
FILE LATUHAN

Memperbaiki Data Text Dengan Fungsi PROPER

Terkadang kita pusing dengan data nama Pelanggan yang yang cara penulisannya berbeda-beda formatnya. Antara huruf kapital dan huruf kecil campur aduk tidak karuan. Jangan kuatir, di dalam MS Excel telah disediakan Fungsi PROPER() untuk memperbaikinya supaya rapi dan enak dibaca.

Menghilangkan Spasi Kosong

Saya lakukan perbaikan dan penambahan materi pada posting ini semoga bermanfaat

1. Menghilangkan Spasi Kosong

Rumus :
=SUBSTITUTE(A2;" ";"")

2. Mengganti Kata Menggunakan Fungsi SUBSTITUTE dan REPLACE

Rumus dengan SUBSTITUTE:
=SUBSTITUTE(A7;"Bapak";"Ibu")

Rumus dengan REPLACE
=REPLACE(A7;5;5;"Ibu")

Lebih simpel menggunakan SUBSTITUTE tetapi ini sifatnya "case sensitive", misalnya Ibu dengan ibu itu berbeda, jadi harus "exact match". Pada contoh rumus menggunakan fungsi REPLACE lebih ribet karena harus mencari posisi kata dari suatu kalimat yang harus diganti, dsilahkan dicermati sintaks dari rumus ini:

REPLACE( old_text, start, number_of_chars, new_text )

3. Membersihkan Spasi Kosong Yang Tertinggal

Mungkin sudah jelas ya lewat gambar diatas, kadang kita menemui data yang dipenuhi oleh spasi kosong yang tertinggal, nah hal ini terkadang yang membuat rumus excel kita jadi error. Anda bisa cek cell yang terdapat teks untuk menguji apakah jumlah karakternya sama dengan yang terpampang di monitor, cobalah memalkai fungsi LEN. Sebetulnya ada cara yang lebih praktis lewat excel untuk membersihkan "trailing spaces" ini, karena blog saya rumus excel ya saya terangkan lewat rumus saja yaaa....