Referensi Sel Relatif dan Mutlak, dan Pemformatan

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

NAVIGASI SEKOLAH

1. Mengapa Anda Membutuhkan Formula dan Fungsi?
2. Mendefinisikan dan Membuat Formula
3. Referensi Sel Relatif dan Mutlak, dan Pemformatan
4. Fungsi Berguna Yang Harus Anda Ketahui
5. Pencarian, Grafik, Statistik, dan Tabel Pivot

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

Apa itu Referensi Sel?

"Referensi sel" berarti sel yang dirujuk sel lain. Misalnya, jika dalam sel A1 Anda memiliki = A2. Kemudian A1 mengacu ke A2.

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

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

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 belajar tentang referensi sel, kadang-kadang kita akan menuliskannya sebagai baris, kolom, ini bukan notasi yang valid dalam spreadsheet dan hanya dimaksudkan untuk membuat semuanya menjadi lebih jelas..

Jenis referensi sel

Ada tiga jenis referensi sel.

Mutlak - Ini berarti referensi sel tetap sama jika Anda menyalin atau memindahkan sel ke sel lain. Ini dilakukan dengan menjangkar 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 meletakkan baris atau kolom saat Anda menyalin atau memindahkan sel, sehingga yang satu berubah dan yang lainnya tidak. Sebagai contoh, Anda bisa memasang referensi baris lalu memindahkan sel ke bawah dua baris dan melintasi empat kolom dan referensi baris tetap sama. Kami akan menjelaskan ini lebih lanjut di bawah ini.

Referensi Relatif

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

Sekarang, misalkan kita perlu memberi ruang pada spreadsheet kita 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, jumlah kolom bertambah. Saat Anda memindahkannya ke bawah, nomor baris bertambah. Sel yang ditunjuknya, referensi sel, juga berubah. Ini diilustrasikan di bawah ini:

Melanjutkan dengan contoh kami, dan melihat grafik di bawah ini, jika Anda menyalin konten 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 diacu dua di dan empat di bawah. A1 = A2 sekarang adalah C5 = C6. Alih-alih merujuk ke A2, sekarang sel C5 merujuk ke sel C6.

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

Contoh: Formula Teks

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

Tulis 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 akan memperbarui referensi.

Anda dapat mengedit konten sel di bagian atas spreadsheet dalam kotak di sebelah tempat bertuliskan "fx." Kotak itu lebih panjang dari lebar sel, sehingga lebih mudah untuk mengedit.

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. Perhatikan 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. Lihat apa yang terjadi - kami mendapatkan kesalahan: "#REF." Mengapa ini?

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

Itu 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.

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

Menyalin Formula ke Berbagai Sel

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

Yang dimaksud dengan "rentang" adalah lebih dari satu sel. Misalnya, (C1: C10) berarti semua sel dari sel C1 ke sel C10. Jadi itu adalah kolom sel. Contoh lain (A1: AZ1) adalah baris atas dari kolom A ke kolom AZ.

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

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

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

Seperti yang Anda lihat, setiap sel baru diperbarui relatif ke lokasi baru, jadi 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 absolut - untuk mengingat itu, anggap tanda dolar sebagai jangkar.

Misalnya, masukkan rumus = $ A $ 1 di sel apa 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 dalam contoh di bawah ini, dalam sel B1 kami memiliki referensi relatif = A1. Ketika kami menyalin B1 ke empat sel di bawahnya, referensi relatif = A1 berubah ke sel di sebelah kiri, sehingga B2 menjadi A2, B3 menjadi A3, dll. Sel-sel itu jelas tidak memiliki nilai yang dimasukkan, sehingga outputnya nol.

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

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

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

Anggaran baru terlihat seperti ini:

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

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

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

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

Karena kami menyalin rumus satu sel ke bawah, mis. Meningkatkan baris per satu, rumus baru adalah = B5 * $ B $ 1. Suku bunga reksa dana dihitung dengan benar sekarang, karena suku bunga tertambat ke sel B1.

Ini adalah contoh yang baik 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 lalu pilih "tentukan nama."

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 petani yang membuat anggaran. Anda juga memiliki toko pakan dan menjual benih. Anda akan menanam jagung, kedelai, dan alfalfa. Spreadsheet di bawah ini menunjukkan biaya per are. “Cost per acre” = “price per pound” * “pound of seed per acre” - itulah yang akan dikenakan biaya untuk menanam satu hektar.

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

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

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

Ini adalah referensi campuran karena kolom adalah absolut dan baris relatif.

Referensi Lingkar

Referensi melingkar adalah ketika rumus mengacu pada dirinya sendiri.

Misalnya, Anda tidak dapat menulis c3 = c3 + 1. Jenis perhitungan ini disebut "iterasi" yang berarti ia mengulangi dirinya sendiri. Excel tidak mendukung iterasi karena hanya menghitung semuanya satu kali.

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

Layar peringatan muncul:

Excel hanya memberi tahu Anda bahwa Anda memiliki referensi melingkar di bagian bawah layar sehingga Anda mungkin tidak melihatnya. Jika Anda memiliki referensi melingkar dan menutup spreadsheet dan membukanya lagi, Excel akan memberi tahu Anda di jendela sembulan bahwa Anda memiliki referensi melingkar.

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

Referensi ke Lembar Kerja Lainnya

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

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

Anda bisa mengubah nama lembar kerja menjadi sesuatu yang berguna seperti "pinjaman" atau "anggaran" dengan mengklik kanan pada tab lembar kerja yang ditunjukkan di bagian bawah layar program Excel, memilih nama, dan mengetik nama baru.

Atau Anda cukup klik dua kali pada tab dan ganti namanya.

Sintaks untuk referensi lembar kerja adalah = lembar kerja! Sel. Anda bisa menggunakan referensi semacam ini ketika nilai yang sama digunakan dalam dua lembar kerja, contoh itu 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 merujuk pada lembar kerja “pinjaman,” sel B1.

Jika kita melihat lembar kerja “pinjaman”, kita dapat melihat referensi ke jumlah pinjaman:

Selanjutnya…

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

Itu saja untuk pelajaran hari ini, dalam Pelajaran 4, kami akan membahas beberapa fungsi berguna yang mungkin ingin Anda ketahui untuk penggunaan Excel setiap hari.