Lompat ke konten
Home » Blog » Rumus ADDRESS pada Excel

Rumus ADDRESS pada Excel

Share :

Rumus ADDRESS pada Excel adalah fungsi yang masuk dalam kategori fungsi lookup dan referensi, seperti LOOKUP, XLOOKUP, MATCH, atau INDEX.

Unduh file berikut untuk melihat aplikasi rumus-rumus di artikel ini secara langsung.

File: fungsi-address-excel.xlsx.

Apa Itu Fungsi ADDRESS di Excel?

Fungsi ADDRESS adalah fungsi yang mengembalikan representasi teks dari alamat cell berdasarkan angka-angka yang menyatakan posisi baris dan kolom.

Sintaks

ADDRESS(row_num; column_num; [abs_num]; [a1]; [sheet_text])

  • row_num (wajib). Angka yang menunjukkan posisi baris cell.
  • column_num (wajib). Angka yang menunjukkan kolom posisi cell.
  • abs_num (opsional). Angka untuk mengatur keabsolutan referensi.
    • 1 (default atau bila tidak disertakan). Referensi absolut untuk baris dan kolom.
    • 2. Absolut hanya untuk baris. Relatif untuk kolom.
    • 3. Relatif untuk baris. Absolut untuk kolom.
    • 4. Relatif untuk baris dan kolom.
  • A1 (opsional). Nilai logika untuk menentukan tipe referensi.
    • TRUE (default atau bila tidak disertakan). Mengembalikan referensi dalam bentuk A1. Contoh: B1 (merujuk pada cell yang terletak di kolom B baris satu).
    • FALSE. Mengembalikan referensi dalam bentuk R1C1. Contoh: R1C2 (merujuk pada cell di kolom ke dua dan baris pertama).
  • sheet_text (opsional). Parameter teks yang menyatakan nama sheet. Nama sheet bisa berupa apa pun selama valid dan tidak harus sheet di mana rumus ditulis.

Rumus ADDRESS pada Excel dijalankan setiap kali workbook dibuka atau ada perubahaan terhadap workbook. Semakin banyak rumus ADDRESS dan semakin besar data yang diolah, semakin lambat workbook.

Kegunaan Rumus ADDRESS pada Excel

Fungsi ADDRESS adalah fungsi sederhana yang hampir tidak ada gunanya bila berdiri sendiri.

Tapi, bila digabungkan dengan fungsi-fungsi lain, ADDRESS bisa menciptakan fleksibilitas rumus yang sulit dilakukan oleh fungsi-fungsi lain. Contohnya:

  • Membuat referensi bagi rumus lain. Pada kelanjutannya, referensi ini bisa digunakan sebagai validasi data atau perhitungan lainnya.
  • Mencari lokasi cell tertentu.
  • Mencari nilai terkecil dan terbesar dalam suatu range data.
  • Membuat formula dinamis.

Cara Menggunakan Rumus ADDRESS Pada Excel

Rumus-rumus berikut menunjukkan nilai-nilai kembalian ADDRESS dengan struktur parameter yang berbeda-beda.

Setiap rumus menunjuk pada baris dan kolom kedua worksheet.

Rumus Hasil Penjelasan
=ADDRESS(2; 2) atau =ADDRESS(2; 2; 1) $B$2 Referensi cell dengan baris dan kolom absolut.
=ADDRESS(2; 2; 2) B$2 Referensi cell dengan baris absolut dan kolom relatif.
=ADDRESS(2; 2; 3) $B2 Referensi cell dengan baris relatif dan kolom absolut.
=ADDRESS(2; 2; 4) B2 Referensi cell dengan baris dan kolom relatif.
=ADDRESS(2; 2; 1; 0) R2C2 Referensi cell dalam bentuk R1C1 dengan baris dan kolom absolut.
=ADDRESS(2; 2; 1; 1) $B$2 Seperti di atas, tapi dalam bentuk A1.
=ADDRESS(2; 2; 1; 1; "Excelandor") Excelandor!$B$2 Referensi cell dengan baris dan kolom absolut dan penambahan nama sheet Excelandor di depannya.

Nilai Cell Hasil Rumus ADDRESS Pada Excel

Hasil kembalian fungsi ADDRESS Excel adalah teks, bukan referensi yang sesungguhnya.

Menggunakan hasil ADDRESS sebagai referensi cell bagi fungsi lain akan menghasilkan pesan error #VALUE!.

=ADDRESS(2; 2)+1 → Error

Agar dapat berfungsi sebagai referensi, gunakan fungsi INDIRECT.

=INDIRECT(ADDRESS(2; 2))+1 → Gak error

Gambar 01. Kombinasi fungsi INDIRECT dengan rumus ADDRESS pada Excel.
Gambar 01. Kombinasi fungsi INDIRECT dengan rumus ADDRESS pada Excel.

Mengubah Angka Kolom Menjadi Huruf

Fungsi MATCH bisa mengembalikan angka yang mewakili posisi kolom tertentu dalam worksheet.

Untuk mengubah kembali angka ini menjadi huruf kolom, gunakan fungsi ADDRESS. Contohnya, 1 menjadi A atau 10 menjadi J.

=ADDRESS(1; A2; 4)

Rumus di atas mengubah cell pada baris pertama dan kolom pertama (ditunjukkan oleh referensi A2) menjadi referensi cell relatif.

Angka 1 di sini adalah umpan. Kita dapat mengganti angka ini menjadi angka berapa pun, karena pada akhirnya hanya nilai kolom yang diambil.

Hasil rumus di atas adalah A1.

Untuk menghilangkan nilai 1 dari A1, bungkus rumus dengan fungsi SUBSTITUTE.

=SUBSTITUTE(ADDRESS(1; A2; 4); "1"; "")

Rumus SUBSTITUTE mengganti 1 dengan teks kosong dan memberikan hasil akhir A.

Gambar 02. Rumus ADDRESS pada Excel mengubah angka menjadi huruf kolom.
Gambar 02. Rumus ADDRESS pada Excel mengubah angka menjadi huruf kolom.

Mencari Posisi Cell dari Nilai Tertentu

Rumus-rumus di bawah ini mengembalikan nilai terkecil dan terbesar di range B2:B6.

=MIN(B2:B6)

=MAX(B2:B6)

Dengan berandai bahwa data cukup besar dan menyulitkan mencari nilai satu per satu dengan mata, kita ingin tahu di mana posisi kedua nilai tersebut di range B2:B6.

Mencari posisi baris masing-masing nilai dapat dilakukan dengan fungsi MATCH.

=MATCH(MIN(B2:B6); B:B; 0)

=MATCH(MAX(B2:B6); B:B; 0)

B:B adalah range yang mengakomodasi semua cell pada kolom B.

Berhubung data penjualan sudah diketahui berada di kolom B, kita hanya perlu menggunakan fungsi COLUMN untuk mengambil posisi nomor kolom.

=COLUMN(B1)

Dengan demikian, rumus secara keseluruhan adalah sebagai berikut:

=ADDRESS(MATCH(MIN(B2:B6); B:B; 0); COLUMN(B1))

=ADDRESS(MATCH(MAX(B2:B6); B:B; 0); COLUMN(B1))

Gambar 03. Rumus ADDRESS pada Excel untuk mencari lokasi nilai terbesar dan terkecil dalam range data.
Gambar 03. Rumus ADDRESS pada Excel untuk mencari lokasi nilai terbesar dan terkecil dalam range data.

Mengapa pakai COLUMN(B1) untuk mengambil posisi kolom? Kenapa gak langsung aja tuliskan 2?

Fleksibilitas.

Bila nantinya ada penambahan kolom yang menyebabkan kolom penjualan bergeser, referensi B1 akan disesuaikan relatif terhadap penambahan jumlah kolom.

Selain itu, cell apa pun selama berada di kolom B bisa menggantikan B1.

Rumus ADDRESS Mencari Alamat Nama Range

Memakai nama tabel atau nama range sering kali lebih efisien dalam mengolah data ketimbang alamat range.

Pada tabel data, nama tabel akan mengakomodasi baris atau kolom baru yang ditambahkan setelah penamaan. Karena itu, penggunaannya lebih disarankan, alih-alih menggunakan alamat referensi secara langsung.

Tapi, sesekali kita butuh alamat tersebut.

Cara termudah adalah melihat cell pertama, lalu scroll ke cell terakhir. Tapi, cara ini tidak fleksibel. Bila kemudian ada penambahan atau pengurangan baris atau kolom, alamat range tidak lagi sama.

Cara lain adalah dengan fungsi ADDRESS.

Rumus Address untuk Cell Pertama Tabel Data

Rumus berikut menghasilkan alamat cell pertama tabel data.

=ADDRESS(@ROW(Penjualan2022);@COLUMN(Penjualan2022))

Berikut penjelasan dari setiap bagian rumus.

Bagian Rumus Hasil Penjelasan
@ROW(Penjualan2022) 2 ROW mengembalikan array baris-baris di mana data berada pada tabel Penjualan2022.

Operator @ (operator irisan implisit) mengambil hanya baris pertama, alih-alih keseluruhan array.

@COLUMN(Penjualan2022) 1 COLUMN mengembalikan array indeks kolom-kolom di mana data berada pada tabel Penjualan2022.

Operator @ mengambil hanya indeks kolom pertama.

Berdasarkan hasil potongan-potongan rumus, ADDRESS mengembalikan alamat cell yang berlokasi pada kolom pertama dan baris kedua worksheet, yaitu A2.

Catatan: Kami menggunakan Excel 365.

Excel versi ini memberlakukan secara default apa yang disebut sebagai dynamic array. Array sudah default dari sananya.

Tanpa operator @, ROW akan mengembalikan array baris-baris range dan hasil rumus mungkin akan berbeda.

Bila rumus dengan operator @ dijalankan pada Excel yang tidak memberlakukan dynamic array, seperti Excel 2019, 2016, atau lebih awal, Excel akan menghilangkan operator ini saat kita menekan Enter.

Rumus ADDRESS untuk Cell Terakhir Tabel Data

Rumus berikut mencari alamat cell terakhir tabel data.

=ADDRESS(@ROW(Penjualan2022)+ROWS(Penjualan2022)-1;@COLUMN(Penjualan2022)+COLUMNS(Penjualan2022)-1)

Berikut penjelasan tiap bagian rumus.

Bagian Rumus Hasil Penjelasan
@ROW(Penjualan2022)+ROWS(Penjualan2022)-1 2+5-1=6 Rumus ini adalah rumus untuk mendapatkan posisi baris terakhir tabel data Penjualan2022.
@COLUMN(Penjualan2022)+COLUMNS(Penjualan2022)-1) 1+2-1=2 Potongan rumus ini berfungsi untuk mendapatkan posisi kolom terakhir Penjualan2022.

Secara keseluruhan, fungsi mengembalikan B6, yaitu alamat cell dari kolom kedua dan baris keenam worksheet bersangkutan.

Alamat Cell Terakhir Nama Range

Setelah mendapatkan alamat cell pertama dan terakhir, langkah selanjutnya adalah menggabungkan keduanya.

Menggabungkan dua teks bisa dilakukan dengan fungsi CONCATENATE atau operator ampersand (&).

=CONCATENATE(ADDRESS(@ROW(Penjualan2022);@COLUMN(Penjualan2022));":";ADDRESS(@ROW(Penjualan2022)+ROWS(Penjualan2022)-1;@COLUMN(Penjualan2022)+COLUMNS(Penjualan2022)-1))

Atau

=ADDRESS(@ROW(Penjualan2022);@COLUMN(Penjualan2022))&":"&ADDRESS(@ROW(Penjualan2022)+ROWS(Penjualan2022)-1;@COLUMN(Penjualan2022)+COLUMNS(Penjualan2022)-1)

Hasil yang kita peroleh adalah $A$2:$B$6.

Gambar 04. Rumus ADDRESS pada Excel untuk mencari alamat berdasarkan nama range.
Gambar 04. Rumus ADDRESS pada Excel untuk mencari alamat berdasarkan nama range.

Mengambil Data N Terakhir di Excel

Dengan rumus ADDRESS pada Excel kita bisa mengambil n data terakhir pada suatu tabel data atau range data biasa.

=INDIRECT(ADDRESS(@ROW(Performa2023)+ROWS(Performa2023)-E1;@COLUMN(Performa2023[Penjualan]))&":"&ADDRESS(@ROW(Performa2023)+ROWS(Performa2023)-1;@COLUMN(Performa2023[Penjualan])))

Rumus di atas menghasilkan range tiga baris terakhir dari kolom Penjualan pada tabel data Performa2023.

Rumus di atas menggunakan konsep yang sama dengan rumus mengenai bagian mengambil alamat dari nama range. Teman-teman bisa baca kembali bagian tersebut.

Tabel berikut menjelaskan bagian awal rumus.

Bagian Rumus Hasil Penjelasan
@ROW(Performa2023) 2. Mengambil angka baris teratas data Performa2023.
ROWS(Performa2023) 7. Jumlah baris Performa2023.
@ROW(Performa2023)+ROWS(Performa2023)-E1 2+7-3=6. E1 adalah jumlah baris terakhir yang akan diambil.

Berdasarkan hasilnya, maka data akan diambil mulai dari baris keenam.

@COLUMN(Performa2023[Penjualan]) 2 Mengambil indeks kolom Penjualan dari Performa2023.
ADDRESS(@ROW(Performa2023)+ROWS(Performa2023)-E1;@COLUMN(Performa2023[Penjualan])) $B$6 Penggabungan dua bagian rumus sebelumnya.

Mengembalikan alamat cell terawal dari tiga cell terakhir di kolom Penjualan.

Secara keseluruhan, rumus akan menghasilkan $B$6:$B$8. Range ini kemudian dibungkus oleh fungsi INDIRECT yang mengembalikan array berupa nilai-nilai pada range bersangkutan.

=INDIRECT($B$6:$B$8)

Karena hasil rumus di atas berupa array, kita dapat terapkan fungsi-fungsi statistik atau matematika untuk mencari nilai terkecil, terbesar, rata-rata, dan jumlah.

=MIN(INDIRECT(ADDRESS(@ROW(Performa2023)+ROWS(Performa2023)-E1;@COLUMN(Performa2023[Penjualan]))&":"&ADDRESS(@ROW(Performa2023)+ROWS(Performa2023)-1;@COLUMN(Performa2023[Penjualan]))))

=MAX(INDIRECT(ADDRESS(@ROW(Performa2023)+ROWS(Performa2023)-E1;@COLUMN(Performa2023[Penjualan]))&":"&ADDRESS(@ROW(Performa2023)+ROWS(Performa2023)-1;@COLUMN(Performa2023[Penjualan]))))

=AVERAGE(INDIRECT(ADDRESS(@ROW(Performa2023)+ROWS(Performa2023)-E1;@COLUMN(Performa2023[Penjualan]))&":"&ADDRESS(@ROW(Performa2023)+ROWS(Performa2023)-1;@COLUMN(Performa2023[Penjualan]))))

=SUM(INDIRECT(ADDRESS(@ROW(Performa2023)+ROWS(Performa2023)-E1;@COLUMN(Performa2023[Penjualan]))&":"&ADDRESS(@ROW(Performa2023)+ROWS(Performa2023)-1;@COLUMN(Performa2023[Penjualan]))))

Gambar 05. Mencari nilai n terakhir dengan rumus ADDRESS.
Gambar 05. Mencari nilai n terakhir dengan rumus ADDRESS.

Tinggalkan Balasan

Alamat email Anda tidak akan dipublikasikan. Ruas yang wajib ditandai *