Cara mengunci rumus sumif di excel

Cara Membuat Rumus Tidak Berubah Dalam Excel – Suatu ketika Anda menggunakan Microsoft Excel, Anda ingin membuat rumus yang Anda copy tidak berubah. Kadang kita ingin mengubah sebagian saja dari rumus  tersebut. Untuk itu, kita akan mempelajari alamat relative dan alamat absolute. Mungkin anda masih asing dengan kata – kata yang ini. Namun sebenarnya anda sudah pernah membuat sel relative maupun sel absolute tanpa anda sadari. Nah, bagaimana dengan pengertian dan fungsi dari alamat relative dan alamat absolute ini. Langsung saja simak pembahasannya berikut ini.

Alamat relative

Alamat relative ini sering sekali kita temui dalam penulisan dan peng-copy-an rumus di Excel. Seperti istilahnya yaitu relatif, alamat ini kita perlukan jika kita ingin rumus yang kita copy berubah-ubah secara relatif terhadap alamat yang kita jadikan acuan. Jika kita punya sebuah rumus yang merupakan operasi dari beberapa sel, maka secara otomatis Excel akan menerjemahkan alamat relatif sebagai lokasi relatif suatu alamat sel atau range dari lokasi alamat lainnya. Daripada bingung langsung simak contohnya. 

Contoh: 

Mengenal alamat absolute

Inilah yang kita cari, membuat rumus yang tidak berubah saat dicopy. Alamat absolut adalah alamat yang selalu bernilai sama meskipun rumusnya di copykan ke manapun. Jadi misalkan suatu rumus fungsi sell dicopy ke sell lain maka alamat sellnya tidak berubah. Penulisan alamat absolut selalu ditambahkan tanda “ $ “ pada nama kolom dan/atau nomor barisnya atau dengan menekan tombol F4 pada keyboard. Alamat absolute ini dibagi menjadi 2, yaitu: 

• Semi absolute 

Jika dilihat dari namanya saja kita sudah tahu, bahwa ini adalah alamat sell absolute yang hanya di kunci salah satunya, entah itu baris atau kolomnya. Lebih jelasnya lihat keterangan contoh berikut ini : $A3 : contoh seperti ini dilakukan untuk penguncian kolom A, dengan cara ini ketika cell dicopy kekanan alamat cell akan tetap dibaca sebagai $A3 bukan B3, namun ketika dicopy kebawah alamat cell akan berubah menjadi $A4. A$3 : contoh seperti ini dilakukan untuk penguncian baris 3, dengan cara ini ketika cell dicopy ke bawah alamat cell akan tetap dibaca sebagai A$3 bukan A4, namun ketika dicopy kekanan alamat cell akan berubah menjadi B$3.

• Absolute mutlak 

Namanya saja mutlak, berarti sell absolute ini pengunciannya penuh terhadap semua baris dan kolom pada alamat sell. Lebih jelasnya silahkan perhatikan kasus contoh berikut ini.

Pada sell A5 kita isikan rumus “ =A3*$A$4 “. Jika rumus itu kita Copykan ke sell B5, maka rumusnya akan menjadi “ =B3*$A$4 “. Terlihat bahwa alamat A4 akan tetap sedangkan A3 akan berubah. Sell A4 inilah yang disebut dengan alamat absolut mutlak. Lihat gambar beriku ini supaya lebih jelas.

Jika di copykan ke dalam sell B5 maka akan menjadi seperti yang dibawah ini.

Cukup jelas dengan apa yang dimaksud alamat sel relative, alamat sel semi absolute dan alamat sel absolute mutlak? Semoga anda dapat memperoleh manfaatnya. Kali ini, sekian tutorial dari saya.

Jika rumus formula pada sel C1 tersebut disalin (copy) pada sel C2 maka rumus formula yang terbentuk adalah =A2+B2 yang akan menghasilkan nilai 4.

Kadangkala kita membutuhkan suatu posisi yang tetap (mengacu pada sel yang sama) apabila suatu rumus formula dilakukan copy paste.

Agar suatu sel / rentang sel yang dituju bisa tetap sesuai dengan yang diharapkan, berikut contoh-contoh fungsi $ yang bisa digunakan.

1. MENGUNCI POSISI BARIS DENGAN SIMBOL $

Contoh mengunci baris ini adalah dengan memberikan tanda $ hanya di depan baris seperti contoh rumus formula pada sel C1 awalnya adalah =A1+B1.

Kemudian akan diberikan tanda $ pada baris pertama kolom A sehingga rumus pada sel C2 menjadi A$1+B1.

• =A$1+B2 pada sel C2 dengan hasil 3 dari Sel A1 (1) ditambah sel B2 (2)
• =A$1+B3 pada sel C3 dengan hasil 4 dari Sel A1 (1) ditambah sel B3 (3)
• =A$1+B4 pada sel C4 dengan hasil 5 dari Sel A1 (1) ditambah sel B4 (4)

Dengan mengunci baris menggunakan tanda "$" hanyak akan mengunci posisi sel A1 pada contoh diatas apabila dilakukan copy rumus formulanya ke bawah (berdasarkan baris).

Jika rumus pada sel A$1+B1 pada sel C1 dilakukan copy paste ke sebelah kanan, maka hasil posisi pada kolom A akan bergeser ke kanan dengan hasil 3 dari rumus formula =B$1+C1.

2. MENGUNCI POSISI KOLOM DENGAN SIMBOL $

Contoh mengunci sel berdasarkan kolom ini hanya akan mengunci posisi kolom jika dilakukan copy paste berdasarkan kolom (kanan dan kiri).

Contoh penggunaan simbol $ untuk mengunci posisi berdasarkan kolom ini, sobat bisa lihat pada contoh gambar fungsi $ 3 dibawah ini.

Manfaat dari penggunaan simbol $ berdasarkan kolom ini bisa dilihat hasilnya pada sel D1, dimana rumus tersebut berasal dari sel C1 yang di copy.

Rumus formula yang terbentuk pada sel D1 adalah =$A1+C1, dimana simbol $ akan mengunci posisi tetap pada kolom pertama (A) sedangkan kolom kedua yang tidak dikunci akan bergeser ke kanan. 

3. MENGUNCI POSISI BARIS & KOLOM DENGAN SIMBOL $

Mengunci posisi sel / rentang sel berdasarkan baris dan kolom ini sangat banyak digunakan pada saat melakukan copy paste rumus formula pada aplikasi ms.excel.

Dengan cara ini, pada saat dilakukan copy rumus baik kebawah, keatas, kekanan maupun ke kiri akan tetap mengacu kepada sel / rentang sel yang dituju.

Contoh penggunaan tanda $ untuk posisi baris dan kolom, sobat bisa lihat pada contoh gambar fungsi $ 4 di bawah ini

Penggunaan tanda $ pada baris dan kolom ini akan sangat berguna pada saat menggunakan fuction jenis lookup di excel seperti fungsi vlookup dan fungsi hlookup.

KESIMPULAN

Fugnsi $ pada excel memberi peranan yang sangat penting pada pneggunaan rumus formula ketika terjadi penyalian rumus formula pada sel / rentang sel pada aplikasi ms.excel, baik di microsoft excel 2003, 2007, 2010 maupun 2013.

Bagaimana mengunci rumus di excel?

Bagaimana cara agar rumus excel tidak berubah?

Rumus apa yang digunakan untuk mengunci sel?

Bagaimana cara menarik rumus Excel ke bawah?