Referensi Sel Relatif dan Absolut, dan Format

2024 Pengarang: Geoffrey Carr | [email protected]. Terakhir diubah: 2023-12-17 10:58

Dalam pelajaran ini kita membahas referensi sel, cara menyalin atau memindahkan rumus, dan memformat sel. Untuk memulai, mari kita perjelas apa yang kita maksud dengan referensi sel, yang mendukung banyak kekuatan dan keserbagunaan rumus dan fungsi. Sebuah pemahaman konkrit tentang bagaimana referensi sel bekerja akan memungkinkan Anda untuk mendapatkan hasil maksimal dari spreadsheet Excel Anda!

NAVIGASI SEKOLAH

Mengapa Anda Perlu Rumus dan Fungsi?
Mendefinisikan dan Membuat Rumus
Referensi Sel Relatif dan Absolut, dan Format
Fungsi Berguna yang Harus Anda Ketahui
Lookup, Grafik, Statistik, dan Tabel Pivot

catatan: kami hanya akan berasumsi bahwa Anda sudah tahu bahwa sel adalah salah satu kotak dalam spreadsheet, disusun dalam kolom dan baris yang direferensikan oleh huruf dan angka yang berjalan secara horizontal dan vertikal.

Apa itu Referensi Sel?

"Referensi sel" berarti sel yang merujuk pada sel lain. Misalnya, jika dalam sel A1 Anda memiliki = A2. Maka A1 mengacu pada A2.

Mari tinjau apa yang kami katakan di Pelajaran 2 tentang baris dan kolom sehingga kami dapat menjelajahi referensi sel lebih lanjut.

Sel dalam spreadsheet disebut oleh baris dan kolom. Kolom vertikal dan diberi label dengan huruf. Baris horisontal dan diberi label dengan angka.

Sel pertama dalam spreadsheet adalah A1, yang berarti kolom A, baris 1, B3 mengacu pada sel yang terletak di kolom kedua, baris ketiga, dan seterusnya.

Untuk tujuan pembelajaran tentang referensi sel, kita akan sering menulisnya sebagai baris, kolom, ini bukan notasi yang valid dalam spreadsheet dan hanya dimaksudkan untuk membuat semuanya lebih jelas.

Jenis referensi sel

Ada tiga jenis referensi sel.

Absolute - Ini berarti referensi sel tetap sama jika Anda menyalin atau memindahkan sel ke sel lain. Ini dilakukan dengan penahan baris dan kolom, sehingga tidak berubah ketika disalin atau dipindahkan.

Relatif - Referensi relatif berarti bahwa alamat sel berubah saat Anda menyalin atau memindahkannya; yaitu referensi sel relatif terhadap lokasinya.

Campuran - Ini berarti Anda dapat memilih untuk melabuhkan baik baris atau kolom ketika Anda menyalin atau memindahkan sel, sehingga yang satu berubah dan yang lainnya tidak. Misalnya, Anda dapat mengaitkan referensi baris kemudian memindahkan sel ke dua baris dan melintasi empat kolom dan referensi baris tetap sama. Kami akan menjelaskan lebih lanjut di bawah ini.

Referensi Relatif

Mari kita simak contoh sebelumnya - misalkan di sel A1 kita memiliki rumus yang hanya mengatakan = A2. Itu berarti output Excel di sel A1 apa pun yang dimasukkan ke sel A2. Di sel A2 kami telah mengetik "A2" sehingga Excel menampilkan nilai "A2" di sel A1.

Sekarang, anggaplah kita perlu menyediakan ruang di spreadsheet kami untuk lebih banyak data. Kita perlu menambahkan kolom di atas dan baris ke kiri, jadi kita harus memindahkan sel ke bawah dan ke kanan untuk memberi ruang.

Saat Anda memindahkan sel ke kanan, nomor kolom akan bertambah. Saat Anda memindahkannya ke bawah, nomor baris meningkat. Sel yang ditunjuknya, referensi sel, juga berubah. Ini diilustrasikan di bawah ini:

Melanjutkan contoh kita, dan melihat grafik di bawah ini, jika Anda menyalin isi sel A1 dua ke kanan dan empat ke bawah Anda telah memindahkannya ke sel C5.

Kami menyalin sel dua kolom ke kanan dan empat ke bawah. Ini berarti kita telah mengubah sel yang merujuk ke dua dan empat ke bawah. A1 = A2 sekarang adalah C5 = C6. Alih-alih mengacu pada A2, sekarang sel C5 merujuk ke sel C6.

Nilai yang ditampilkan adalah 0 karena sel C6 kosong. Di sel C6 kami mengetik "Saya C6" dan sekarang C5 menampilkan "Saya C6."

Contoh: Formula Teks

Mari coba contoh lain. Ingat dari Pelajaran 2 di mana kami harus membagi nama lengkap menjadi nama depan dan belakang? Apa yang terjadi ketika kami menyalin formula ini?

Tuliskan rumus = KANAN (A3, LEN (A3) - FIND (",", A3) - 1) atau salin teks ke sel C3. Jangan menyalin sel yang sebenarnya, hanya teks, salin teks, jika tidak maka akan memperbarui referensi.

Anda dapat mengedit isi sel di bagian atas spreadsheet di kotak di sebelah di mana ada tulisan "fx." Kotak itu lebih panjang dari sel yang lebar, jadi lebih mudah diedit.

Sekarang kita punya:

Tidak ada yang rumit, kami baru saja menulis formula baru ke dalam sel C3. Sekarang salin C3 ke sel C2 dan C4. Amati hasil di bawah ini:

Sekarang kami memiliki nama depan Alexander Hamilton dan Thomas Jefferson.

Gunakan kursor untuk menyorot sel C2, C3, dan C4. Arahkan kursor ke sel B2 dan tempel konten. Lihatlah apa yang terjadi - kami mendapatkan kesalahan: "#REF." Mengapa ini?

Ketika kita menyalin sel dari kolom C ke kolom B, itu memperbarui referensi satu kolom ke kiri = KANAN (A2, LEN (A2) - MENCARI (“,”, A2) - 1).

Ini mengubah setiap referensi ke A2 ke kolom di sebelah kiri A, tetapi tidak ada kolom di sebelah kiri kolom A. Jadi komputer tidak tahu apa yang Anda maksud.

Rumus baru dalam B2 misalnya, adalah = KANAN (#REF!, LEN (#REF!) - FIND (",", # REF!) - 1) dan hasilnya #REF:

Menyalin Formula ke Berbagai Sel

Menyalin sel sangat berguna karena Anda dapat menulis satu rumus dan menyalinnya ke area yang luas dan referensi diperbarui.Ini menghindari harus mengedit setiap sel untuk memastikannya menunjuk ke tempat yang benar.

Dengan "rentang" yang kami maksudkan lebih dari satu sel. Sebagai contoh, (C1: C10) berarti semua sel dari sel C1 ke sel C10. Jadi itu adalah kolom sel. Contoh lain (A1: AZ1) adalah baris teratas dari kolom A ke kolom AZ.

Jika rentang melewati lima kolom dan sepuluh baris, maka Anda menunjukkan rentang dengan menulis sel kiri atas dan kanan bawah, misalnya, A1: E10. Ini adalah area persegi yang memotong baris dan kolom dan bukan hanya bagian dari kolom atau bagian dari satu baris.

Berikut ini contoh yang menggambarkan cara menyalin satu sel ke beberapa lokasi. Misalkan kita ingin menunjukkan pengeluaran yang diproyeksikan untuk bulan ini dalam spreadsheet sehingga kita dapat membuat anggaran. Kami membuat spreadsheet seperti ini:

Sekarang salin rumus dalam sel C3 (= B3 + C2) ke sisa kolom untuk memberikan saldo berjalan untuk anggaran kami. Excel memperbarui referensi sel saat Anda menyalinnya. Hasilnya ditunjukkan di bawah ini:

Seperti yang Anda lihat, setiap pembaruan sel baru relatif ke lokasi baru, sehingga sel C4 memperbarui rumusnya ke = B4 + C3:

Sel C5 memperbarui ke = B5 + C4, dan seterusnya:

Referensi Mutlak

Referensi absolut tidak berubah ketika Anda memindahkan atau menyalin sel. Kami menggunakan tanda $ untuk membuat referensi mutlak - untuk mengingat itu, pikirkan tanda dolar sebagai jangkar.

Misalnya, masukkan rumus = $ A $ 1 di sel mana pun. $ Di depan kolom A berarti tidak mengubah kolom, $ di depan baris 1 berarti tidak mengubah kolom ketika Anda menyalin atau memindahkan sel ke sel lain.

Seperti yang Anda lihat pada contoh di bawah ini, di sel B1 kita memiliki referensi relatif = A1. Ketika kita menyalin B1 ke empat sel di bawahnya, referensi relatif = A1 berubah menjadi sel ke kiri, jadi B2 menjadi A2, B3 menjadi A3, dll. Sel-sel itu jelas tidak memiliki nilai yang dimasukkan, jadi hasilnya nol.

Namun, jika kita menggunakan = $ A1 $ 1, seperti di C1 dan kita salin ke empat sel di bawahnya, referensi adalah mutlak, sehingga tidak pernah berubah dan output selalu sama dengan nilai dalam sel A1.

Misalkan Anda melacak minat Anda, seperti pada contoh di bawah ini. Rumus dalam C4 = B4 * B1 adalah “suku bunga” * “saldo” = “bunga per tahun.”

Sekarang, Anda telah mengubah anggaran Anda dan telah menyimpan tambahan $ 2.000 untuk membeli reksa dana. Misalkan itu adalah dana tingkat bunga tetap dan membayar tingkat bunga yang sama. Masukkan akun baru dan saldo ke dalam spreadsheet dan kemudian salin rumus = B4 * B1 dari sel C4 ke sel C5.

Anggaran baru terlihat seperti ini:

Reksa dana baru menghasilkan $ 0 dalam bunga per tahun, yang tidak dapat benar karena tingkat bunga jelas 5 persen.

Excel menyoroti sel yang menjadi referensi rumus. Anda dapat melihat di atas bahwa referensi ke tingkat bunga (B1) dipindahkan ke sel kosong B2. Kita seharusnya membuat referensi ke B1 absolut dengan menulis $ B $ 1 menggunakan tanda dolar untuk mengaitkan referensi baris dan kolom.

Tulis ulang perhitungan pertama di C4 untuk dibaca = B4 * $ B $ 1 seperti yang ditunjukkan di bawah ini:

Kemudian salin rumus itu dari C4 ke C5. Spreadsheet sekarang terlihat seperti ini:

Karena kami menyalin formula satu sel ke bawah, yaitu meningkatkan baris satu, rumus baru adalah = B5 * $ B $ 1. Suku bunga reksa dana dihitung dengan benar sekarang, karena tingkat bunga berlabuh ke sel B1.

Ini adalah contoh bagus ketika Anda bisa menggunakan "nama" untuk merujuk ke sel. Nama adalah referensi absolut. Misalnya, untuk menetapkan nama "suku bunga" ke sel B1, klik kanan sel dan kemudian pilih "define name."

Nama dapat merujuk ke satu sel atau rentang, dan Anda dapat menggunakan nama dalam rumus, misalnya = interest_rate * 8 adalah hal yang sama dengan menulis = $ B $ 1 * 8.

Referensi Campuran

Referensi campuran adalah kapan antara baris atau kolom berlabuh.

Misalnya, Anda adalah seorang petani yang membuat anggaran. Anda juga memiliki toko pakan dan menjual benih. Anda akan menanam jagung, kacang kedelai, dan alfalfa. Spreadsheet di bawah ini menunjukkan biaya per acre. “Biaya per acre” = “harga per pon” * “pon benih per acre” - itulah yang akan membebani Anda untuk menanam acre.

Masukkan biaya per acre sebagai = $ B2 * C2 di sel D2. Anda mengatakan Anda ingin jangkar kolom harga per pon. Kemudian salin rumus itu ke baris lain di kolom yang sama:

Sekarang Anda ingin tahu nilai persediaan benih Anda. Anda membutuhkan harga per pon dan jumlah pound dalam persediaan untuk mengetahui nilai inventaris.

Kami menambahkan dua kolom: "pound benih dalam persediaan" dan kemudian "nilai persediaan." Sekarang, salin sel D2 ke F4 dan perhatikan bahwa referensi baris di bagian pertama dari rumus asli ($ B2) diperbarui untuk baris 4 tetapi kolom tetap karena $ jangkar ke "B."

Ini adalah referensi campuran karena kolomnya absolut dan barisnya relatif.

Referensi Edaran

Referensi melingkar adalah ketika rumus mengacu pada dirinya sendiri.

Misalnya, Anda tidak dapat menulis c3 = c3 + 1. Perhitungan semacam ini disebut "iterasi" yang berarti berulang dengan sendirinya. Excel tidak mendukung iterasi karena menghitung semuanya hanya sekali saja.

Jika Anda mencoba melakukan ini dengan mengetik SUM (B1: B5) di sel B5:

Excel hanya memberi tahu Anda bahwa Anda memiliki referensi melingkar di bagian bawah layar sehingga Anda mungkin tidak menyadarinya. Jika Anda memiliki referensi melingkar dan menutup spreadsheet dan membukanya kembali, Excel akan memberi tahu Anda di jendela pop-up bahwa Anda memiliki referensi melingkar.

Jika Anda memiliki referensi melingkar, setiap kali Anda membuka spreadsheet, Excel akan memberi tahu Anda dengan jendela pop-up bahwa Anda memiliki referensi melingkar.

Referensi ke Lembar Kerja Lainnya

"Buku kerja" adalah kumpulan "lembar kerja". Sederhananya, ini berarti Anda dapat memiliki banyak spreadsheet (lembar kerja) dalam file Excel yang sama (buku kerja). Seperti yang Anda lihat pada contoh di bawah ini, contoh buku kerja kami memiliki banyak lembar kerja (berwarna merah).

Lembar kerja secara default diberi nama Sheet1, Sheet2, dan seterusnya. Anda membuat yang baru dengan mengklik "+" di bagian bawah layar Excel.

Anda dapat mengubah nama lembar kerja menjadi sesuatu yang bermanfaat seperti “pinjaman” atau “anggaran” dengan mengklik kanan pada tab lembar kerja yang ditunjukkan di bagian bawah layar program Excel, memilih mengganti nama, dan mengetikkan nama baru.

Atau Anda cukup mengklik dua kali pada tab dan mengganti namanya.

Sintaks untuk referensi lembar kerja adalah = worksheet! Sel. Anda dapat menggunakan referensi semacam ini ketika nilai yang sama digunakan dalam dua lembar kerja, contohnya mungkin:

Tanggal hari ini
Nilai tukar mata uang dari Dolar ke Euro
Apa pun yang relevan dengan semua lembar kerja di buku kerja

Di bawah ini adalah contoh lembar kerja "bunga" yang mengacu pada lembar kerja "pinjaman," sel B1.

Jika kita melihat lembar kerja "pinjaman", kita dapat melihat referensi untuk jumlah pinjaman:

Selanjutnya …

Kami harap Anda sekarang memiliki pemahaman yang kuat tentang referensi sel termasuk relatif, mutlak, dan campuran. Pasti ada banyak.

Itu saja untuk pelajaran hari ini, di Pelajaran 4, kita akan membahas beberapa fungsi berguna yang mungkin ingin Anda ketahui untuk penggunaan Excel sehari-hari.