RumusHitung.Com

RumusHitung.com – Halo guys, bagaimana kabarnya? Semoga masih semangat yaa.. Kali ini rumushitung akan melanjutkan pembahasan mengenai rumus Excel. Pada pembahasan sebelumnya, sobat telah mempelajari tentang gabungan rumus Average pada Excel, pengelompokan data (grouping), gabungan rumus Rank pada Excel, seri tanggal (date series) pada Excel, gabungan rumus Random pada Excel, Nama dalam Excel, dan rumus Excel pada internet. Selanjutnya, kita akan bahas mengenai Buku kerja (Workbook) pada Excel.

Ada 11 contoh rumus Excel yang akan digunakan pada buku kerja :
1. Referensi buku kerja dinamis
2. Referensi lembar kerja dinamis
3. Dapatkan nama dan jalur buku kerja lengkap
4. Dapatkan nama sheet saja
5. Dapatkan nama dan jalur buku kerja tanpa lembar
6. Dapatkan nama buku kerja saja
7. Dapatkan jalur buku kerja saja
8. Berbagai lembar nama tidak langsung berbeda
9. Buat daftar nomor indeks lembar
10. Buat daftar nama sheet dengan formula
11. Nama lembar kerja ada

Referensi Buku Kerja Dinamis

Untuk membuat referensi lembar kerja dinamis – referensi ke buku lain yang dibuat dengan rumus yang didasarkan pada variabel yang mungkin berubah. Gunakan rumus yang didasarkan pada fungsi INDIRECT. Pada contoh yang ditunjukkan, rumus di sel E6 :

=INDIRECT(“ ‘[“&B6&”]”&C6&” ‘!”&D6)

Inti dari rumus ini adalah membuat referensi lengkap ke rentang di buku kerja lain sebagai teks, lalu gunakan fungsi INDIRECT untuk mengonversi teks menjadi referensi aktual.

Referensi ke lembar kerja eksternal terlihat seperti ini :

‘[sample data.xlsx]Sheet1’!A1

Perhatikan tanda kurung siku ([ ]) di sekitar nama buku kerja, tanda kutip tunggal (‘ ‘) di sekitar lembar kerja + lembar, dan tanda seru (!) yang mengikuti.

Untuk membuat referensi seperti ini menggunakan teks, penggabungan untuk menggabungkan nilai dari kolom B, C, dan D dengan tanda kurung, tanda kutip, dan tanda seru yang diperlukan :

=INDIRECT(“ ‘[“&B6&”]”&C6&” ‘!”&D6)

Hasilnya dimasukkan ke dalam INDIRECT sebagai ref_text :

=INDIRECT(“ ‘[sample data.xlsx]Sheet1’!A1”)

Fungsi INDIRECT kemudian mengevaluasi teks dan mengubahnya menjadi referensi. Excel mengikuti referensi dan menghasilkan nilai pada referensi yang diberikan.

Catatan : jika referensi tidak valid, atau jika buku kerja yang direferensikan tidak terbuka, INDIRECT akan memunculkan kesalahan #REF. Kesalahan ini dapat diatasi dengan fungsi IFERROR dan menampilkan hasil khusus jika menginginkannya.

Referensi Lembar Kerja Dinamis

Untuk membuat formula dengan nama sheet dinamis gunakan fungsi INDIRECT.

Pada contoh yang ditampilkan, rumus di C6 adalah :

=INDIRECT(B6&”!A1”)

Catatan : Inti dari pendekatan ini, memungkinkan dalam membuat rumus dengan nama sheet adalah variabel dinamis. Misalnya, dapat mengubah nama sheet (mungkin dengan menu drop down) dan menarik informasi dari worksheet yang berbeda.

Fungsi INDIRECT mencoba mengevaluasi teks sebagai referensi lembar kerja.

Dalam contoh ini, di kolom B terdapat Sheet (lembar), jadi bisa gabungkan nama lembar ke referensi sel A1 menggunakan penggabungan :

=INDIRECT(B6&”!A1”)

Setelah penggabungan, diperoleh :

=INDIRECT(“Sheet1!A1”)

INDIRECT mengenali ini sebagai referensi yang valid ke sel A1 di Sheet1, dan menghasilkan nilai di A1, 100.

Di sel C7, rumus mengevaluasi seperti ini :

=INDIRECT(B7&”!A1”)
=INDIRECT(“Sheet2!A1”)

Begitu seterusnya, untuk setiap rumus di C.

Dapatkan Nama dan Jalur Buku Kerja Lengkap

Untuk mendapatkan jalur lengkap, nama, dan lembar buku kerja dengan rumus, bisa gunakan fungsi CELL dan referensi ke sel mana pun di buku kerja. Dalam contoh yang ditampilkan, rumusnya adalah :

=CELL(“filename”,A1)

Perhatikan bahwa jika ingin mendapatkan hasil, simpan dulu lembar kerjanya.

Fungsi CELL dapat menghasilkan berbagai informasi tentang lembar kerja. CELL bisa memperoleh hal-hal seperti alamat dan nama file, serta info detail tentang pemformatan yang digunakan dalam sel. Jenis informasi yang akan dihasilkan ditentukan oleh argumen info_type.

Dapatkan Nama Sheet Saja

Untuk mendapatkan nama lembar kerja saat ini (yaitu tab saat ini) gunakan rumus berdasarkan fungsi CELL. CELL mengambil nama dan lembar buku kerja, dan fungsi MID dan FIND digunakan untuk mengekstrak hanya nama lembar. Dalam contoh yang ditampilkan, rumus dalam E3 adalah :

=MID(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))+1,255)

Fungsi CELL digunakan untuk mendapatkan nama dan jalur file lengkap :

CELL(“filename”,A1)

Hasilnya terlihat seperti ini :

path[workbook.xlsm]sheetname

CELL memperoleh hasil ini ke fungsi MID sebagai argumen teks. Nama sheet dimulai tepat setelah braket kiri, sehingga posisi awal dihitung dengan FIND :

FIND(“]”,CELL(“filename”,A1))+1

Jumlah karakter yang akan diekstrak adalah hardcode 255. Di UI Excel, tidak bisa memberi nama lembar kerja yang lebih dari 31 karakter, tetapi format file itu sendiri mengizinkan nama lembar kerja hingga 255 karakter, jadi ini memastikan seluruh nama diambil.

Menggunakan Fungsi  RIGHT

Bisa juga menggunakan fungsi RIGHT untuk mengekstrak nama sheet, sebagai ganti MID :

=RIGHT(CELL(“filename”,A1),LEN(CELL(“filename”,A1)-FIND(“]”,CELL(“filename”,A1)))

Ini membutuhkan lebih banyak pemanggilan fungsi, tetapi juga berfungsi dengan baik.

Dapatkan Nama dan Jalur Buku Kerja Tanpa Lembar

=SUBSTITUTE(LEFT(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))-1),”[“,” “)

Jika ingin memperoleh nama lengkap dan jalur buku kerja saat ini tanpa nama lembar, gunakan rumus yang menggunakan beberapa fungsi teks untuk menghapus nama lembar. Hasil akhirnya akan menjadi string teks yang terlihat seperti ini :

path[workbook.xlsm]

Fungsi CELL digunakan dengan “filename” untuk mendapatkan nama dan jalur buku kerja lengkap :

CELL(“filename”,A1)

Hasilnya adalah jalur lengkap yang terlihat seperti :

path[workbook.xlsm]sheetname

Fungsi LEFT menerima jalur lengkap sebagai teks, bersama dengan lokasi tanda kurung siku “]” (minus 1) yang dihitung oleh fungsi FIND :

FIND(“]”,CELL(“filename”,A1))

Dengan argumen ini, LEFT secara efektif menghapus nama sheet dari jalur. Namun, tanda kurung siku kiri “]” tetap ada :

path[workbook.xlsm

Fungsi terakhir, SUBSTITUTE, yaitu menghapus “]” dengan menggantinya dengan string kosong (” “).

=SUBSTITUTE(path[workbook.xlsm,”[“,” “)

Dapatkan Nama Buku Kerja Saja

=MID(CELL(“filename”,A1),FIND(“]”,CELL(“filename””,A1))+1,FIND(“]”,CELL(“filename”,A1))-FIND(“[“,CELL(“filename”,A1))-1)

Jika ingin mendapatkan nama buku kerja saja (yaitu nama file tanpa jalur atau nama lembar), bisa melakukannya dengan rumus yang agak panjang yang menggunakan fungsi MID bersama dengan fungsi FIND.

Dapatkan Jalur Buku Kerja Saja

=LEFT(CELL(“filename”,A1),FIND(“[“,CELL(“filename”,A1))-1)

Jika ingin mendapatkan jalur buku kerja (direktori) saja, bisa melakukannya dengan rumus yang menggunakan fungsi LEFT dan FIND. Hasilnya akan terlihat seperti ini :

Drive:\path\to\file\   / /  versi Windows
Drive:path:to:file:   / /  versi Mac

Berbagai Lembar Nama Tidak Langsung yang Berbeda

Untuk mereferensikan rentang bernama di sheet lain, gunakan fungsi INDIRECT dengan sintaks atau rumus sheet yang diperlukan. Dalam contoh yang diperlihatkan, rumus di D6 adalah :

=SUM(INDIRECT(“ ‘ “&B6&” ‘!”&C6))

Yang menghasilkan jumlah dari “data” rentang bernama Sheet1.

Rumus di atas mengevaluasi sesuaiti seperti :

=SUM(INDIRECT(“ ‘ “&B6&” ‘!”&C6))
=SUM(INDIRECT(“ ‘ “&”Sheet1”&” ‘!”&”data”))
=SUM(‘Sheet1’!data)

Setelah string dirangkai menggunakan nilai di B6 dan C6, INDIRECT mengevaluasi dan mengubah string menjadi referensi yang tepat.

Catatan : bisa merujuk ke rentang bernama dalam rumus tanpa menggunakan INDIRECT. Misalnya, rumus di D6 bisa ditulis :

=SUM(‘Sheet1’!data)

Namun, jika ingin merangkai referensi sebagai teks, dan Excel memperlakukan teks tersebut sebagai referensi, perlu menggunakan INDIRECT.

Catatan : Tanda kutip tunggal ditambahkan dalam rumus di atas sehingga rumus akan berfungsi saat nama lembar berisi spasi.

Buat Daftar Nomor Indeks Lembar

Untuk membuat daftar nomor indeks lembar di buku kerja Excel, bisa memasukkan nama lembar, lalu gunakan rumus berdasarkan fungsi SHEET dan INDIRECT. Pada contoh yang ditunjukkan, rumus di C5 adalah :

=SHEET(INDIRECT(B5&”!A1”))

Fungsi INDIRECT mencoba mengevaluasi teks sebagai referensi yang valid. Dalam hal ini, nama sheet ditarik dari kolom B dan digabungkan dengan tanda seru dan teks A1 :

=B5&”!A1”
=”Sheet1”&”!A1”
=”Sheet!A1”

Fungsi INDIRECT kemudian memaksa teks “Sheet1!A1” menjadi referensi yang valid, yang diteruskan ke fungsi SHEET.

Fungsi SHEET kemudian menghasilkan indeks saat ini untuk setiap lembar seperti yang terdaftar.

Buat Daftar Nama Sheet Dengan Rumus

Untuk membuat daftar lembar kerja di buku kerja Excel, gunakan pendekatan 2 langkah :

(1) menentukan rentang bernama “nama lembar” dengan perintah makro lama.
(2) menggunakan fungsi INDEX untuk mengambil nama lembar menggunakan rentang bernama.

Dalam contoh yang ditunjukkan, rumus di B5 adalah :

=INDEX(MID(sheetnames,FIND(“]”,sheetnames)+1,255),ROWS($B$5:B5))

Nama Lembar Kerja Ada

Untuk menguji apakah nama lembar kerja ada di buku kerja, bisa menggunakan rumus yang didasarkan pada fungsi ISREF dan INDIRECT. Pada contoh yang ditunjukkan, rumus di C5 adalah :

=ISREF(INDIRECT(B5&”!A1”))

Fungsi ISREF mengembalikan TRUE untuk referensi lembar kerja yang valid dan FALSE tidak.

Dalam hal ini, ingin mengetahui lembar tertentu yang ada di buku kerja, jadi dibuatlah referensi lengkap dengan menggabungkan nama lembar di kolom B dengan tanda seru dan “A1” :

B5&”!A1”

Menghasilkan teks :

“Sheet1!A1”

Yang masuk ke fungsi INDIRECT. INDIRECT kemudian mencoba mengevaluasi teks tersebut sebagai referensi.

Ketika INDIRECT berhasil, referensi diteruskan ke ISREF yang menghasilkan TRUE. Jika INDIRECT tidak dapat membuat referensi, kesalahan #REF akan muncul, dan ISREF akan menghasilkan FALSE.

Demikian penjelasan mengenai buku kerja (workbook) pada Excel, semoga bermanfaat. Sekian terima kasih.