Rujukan Sel Relatif dan Mutlak, dan Pemformatan

Dalam pelajaran ini kita membincangkan rujukan sel, cara menyalin atau memindahkan formula, dan memformat sel. Untuk memulakan, mari kita jelaskan apa yang kita maksudkan dengan rujukan sel, yang menyokong banyak kuasa dan fleksibiliti formula dan fungsi. Genggaman konkrit mengenai bagaimana kerja rujukan sel akan membolehkan anda memanfaatkan hamparan Excel anda!

NAVIGASI SEKOLAH

1. Kenapa Anda Perlu Formula dan Fungsi?
2. Menentukan dan Mewujudkan Formula
3. Rujukan Sel Relatif dan Mutlak, dan Pemformatan
4. Fungsi Berguna yang Harus Anda Ketahui
5. Lookups, Charts, Statistik, dan Jadual Pivot

Catatan: kami hanya akan mengandaikan bahawa anda sudah mengetahui bahawa sel adalah salah satu petak dalam hamparan, diatur ke dalam lajur dan baris yang dirujuk oleh huruf dan nombor yang berjalan secara mendatar dan menegak.

Apakah Rujukan Sel?

"Rujukan sel" bermaksud sel yang mana sel lain merujuk. Sebagai contoh, jika dalam sel A1 anda mempunyai = A2. Kemudian A1 merujuk kepada A2.

Mari kita tinjau apa yang kita katakan dalam Pelajaran 2 mengenai baris dan lajur supaya kita boleh meneroka rujukan sel lagi.

Sel dalam spreadsheet dirujuk oleh baris dan lajur. Lajur menegak dan dilabelkan dengan huruf. Baris adalah mendatar dan dilabelkan dengan nombor.

Sel pertama dalam spreadsheet adalah A1, yang berarti lajur A, baris 1, B3 merujuk pada sel yang terletak pada lajur kedua, baris ketiga, dan sebagainya.

Untuk tujuan pembelajaran tentang rujukan sel, kami kadang-kadang akan menulisnya sebagai baris, lajur, ini bukan notasi yang sah dalam hamparan dan hanya dimaksudkan untuk membuat sesuatu lebih jelas.

Jenis-jenis rujukan sel

Terdapat tiga jenis rujukan sel.

Mutlak - Ini bermakna rujukan sel tetap sama jika anda menyalin atau memindahkan sel ke mana-mana sel lain. Ini dilakukan dengan melambangkan baris dan lajur, sehingga tidak berubah ketika disalin atau dipindahkan.

Relatif - Rujukan relatif bermaksud bahawa alamat sel berubah apabila anda menyalin atau mengalihkannya; iaitu, rujukan sel adalah relatif kepada lokasinya.

Campuran - Ini bermakna anda boleh memilih untuk memadankan sama ada baris atau lajur apabila anda menyalin atau memindahkan sel, supaya satu berubah dan yang lain tidak. Sebagai contoh, anda boleh mengawal rujukan baris kemudian memindahkan sel ke bawah dua baris dan merentas empat lajur dan rujukan baris tetap sama. Kami akan menjelaskan perkara ini di bawah lagi.

Rujukan relatif

Mari kita rujuk kepada contoh terdahulu - katakan dalam sel A1 kita mempunyai formula yang hanya berkata = A2. Ini bermakna output Excel dalam sel A1 apa sahaja yang dimasukkan ke dalam sel A2. Dalam sel A2 kita telah menaip "A2" jadi Excel memaparkan nilai "A2" dalam sel A1.

Sekarang, katakan kami perlu membuat ruangan dalam spreadsheet kami untuk mendapatkan lebih banyak data. Kita perlu menambah lajur di atas dan baris ke kiri, jadi kita perlu memindahkan sel ke bawah dan ke kanan untuk membuat ruangan.

Apabila anda menggerakkan sel ke kanan, nombor lajur meningkat. Apabila anda mengalihkannya ke bawah, bilangan baris bertambah. Sel yang ditunjukkan olehnya, rujukan sel, juga berubah. Ini digambarkan di bawah:

Teruskan dengan contoh kami, dan melihat grafik di bawah, jika anda menyalin kandungan sel A1 dua ke kanan dan empat ke bawah anda telah memindahkannya ke sel C5.

Kami menyalin dua tiang sel ke kanan dan empat ke bawah. Ini bermakna kita telah menukar sel yang merujuknya dua dan empat ke bawah. A1 = A2 sekarang ialah C5 = C6. Daripada merujuk kepada A2, kini sel C5 merujuk kepada sel C6.

Nilai yang ditunjukkan ialah 0 kerana sel C6 kosong. Dalam sel C6 kita menaip "Saya C6" dan kini C5 memaparkan "Saya C6."

Contoh: Formula Teks

Mari cuba contoh lain. Ingat dari Pelajaran 2 di mana kita terpaksa berpecah nama penuh ke nama pertama dan terakhir? Apa yang berlaku apabila kita menyalin formula ini?

Tulis formula = KANAN (A3, LEN (A3) - MENEMUKAN (",", A3) - 1) atau salin teks ke sel C3. Jangan salin sel sebenar, hanya teks, salin teks, jika tidak, ia akan mengemaskini rujukannya.

Anda boleh mengedit kandungan sel di bahagian atas hamparan di dalam kotak di sebelah mana yang mengatakan "fx." Kotak itu lebih panjang daripada sel yang luas, jadi lebih mudah untuk diedit.

Sekarang kita ada:

Tidak ada yang rumit, kita baru saja menulis formula baru ke dalam sel C3. Sekarang salin C3 ke sel C2 dan C4. Perhatikan keputusan di bawah:

Sekarang kita mempunyai nama-nama pertama Alexander Hamilton dan Thomas Jefferson.

Gunakan kursor untuk menonjolkan sel C2, C3, dan C4. Arahkan kursor ke sel B2 dan tampal kandungannya. Lihat apa yang berlaku - kita mendapat ralat: "#REF." Kenapa ini?

Apabila kita menyalin sel-sel dari ruangan C ke lajur B, ia memperbaharui rujukan satu lajur ke kiri = KANAN (A2, LEN (A2) - MENEMUKAN (",", A2) - 1).

Ia mengubah setiap rujukan kepada A2 ke lajur di sebelah kiri A, tetapi tidak ada lajur di sebelah kiri lajur A. Jadi komputer tidak tahu apa yang anda maksudkan.

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

Menyalin Formula kepada Julat Sel

Menyalin sel sangat berguna kerana anda boleh menulis satu formula dan menyalinnya ke kawasan yang besar dan rujukannya dikemas kini. Ini mengelakkan pengeditan setiap sel untuk memastikan ia menunjuk ke tempat yang betul.

Dengan "julat" kita bermaksud lebih daripada satu sel. Sebagai contoh, (C1: C10) bermaksud semua sel dari sel C1 ke sel C10. Jadi ia adalah lajur sel. Contoh lain (A1: AZ1) ialah baris atas dari lajur A hingga lajur AZ.

Jika pelbagai menyeberangi lima lajur dan sepuluh baris, maka anda menunjukkan rentang dengan menulis sel sebelah kiri dan kanan bawah, mis., A1: E10. Ini adalah kawasan persegi yang menyeberangi baris dan lajur dan bukan hanya sebahagian daripada lajur atau sebahagian daripada baris.

Berikut adalah contoh yang menggambarkan bagaimana untuk menyalin satu sel ke beberapa lokasi. Katakan kami mahu menunjukkan belanjawan kami untuk bulan itu dalam spreadsheet supaya kami dapat membuat anggaran. Kami membuat spreadsheet seperti ini:

Sekarang salin formula dalam sel C3 (= B3 + C2) ke bahagian lajur untuk memberikan keseimbangan berjalan untuk belanjawan kami. Excel mengemas kini rujukan sel semasa anda menyalinnya. Hasilnya ditunjukkan di bawah:

Seperti yang anda dapat lihat, setiap kemas kini sel baru saudara ke lokasi baru, jadi sel C4 mengemas kini formulanya menjadi = B4 + C3:

C5 mengemas kini kepada = B5 + C4, dan sebagainya:

Rujukan Mutlak

Rujukan mutlak tidak berubah apabila anda memindahkan atau menyalin sel. Kami menggunakan tanda $ untuk membuat rujukan mutlak - untuk mengingatnya, memikirkan tanda dolar sebagai jangkar.

Sebagai contoh, masukkan formula = $ A $ 1 dalam mana-mana sel. $ Di hadapan lajur A bermaksud tidak mengubah lajur, $ di depan baris 1 bermakna tidak mengubah lajur apabila anda menyalin atau memindahkan sel ke mana-mana sel lain.

Seperti yang dapat anda lihat dalam contoh di bawah, dalam sel B1 kita mempunyai rujukan relatif = A1. Apabila kita menyalin B1 ke empat sel di bawahnya, rujukan relatif = A1 berubah ke sel ke kiri, jadi B2 menjadi A2, B3 menjadi A3, dan sebagainya. Sel-sel tersebut jelas tidak mempunyai nilai input, jadi output adalah sifar.

Walau bagaimanapun, jika kita menggunakan = $ A1 $ 1, seperti dalam C1 dan kita menyalinnya ke empat sel di bawahnya, rujukan itu mutlak, oleh itu ia tidak pernah berubah dan output sentiasa sama dengan nilai dalam sel A1.

Katakan anda menjejaki minat anda, seperti dalam contoh di bawah. Formula dalam C4 = B4 * B1 ialah "kadar faedah" * "baki" = "faedah setahun."

Kini, anda telah menukar belanjawan anda dan telah menyimpan $ 2,000 tambahan untuk membeli dana bersama. Katakan ia adalah dana kadar tetap dan ia membayar kadar faedah yang sama. Masukkan akaun baru dan keseimbangan ke dalam hamparan dan kemudian salin formula = B4 * B1 dari sel C4 ke sel C5.

Anggaran baru kelihatan seperti ini:

Dana bersama baru memperoleh $ 0 dalam faedah setahun, yang tidak boleh benar kerana kadar faedahnya jelas 5 peratus.

Excel menonjolkan sel-sel yang mana rujukan formula. Anda boleh lihat di atas bahawa rujukan kepada kadar faedah (B1) dipindahkan ke sel B2 kosong. Kita sepatutnya membuat rujukan kepada B1 mutlak dengan menulis $ B $ 1 menggunakan tanda dolar untuk menuding rujukan baris dan lajur.

Tulis semula pengiraan pertama dalam C4 untuk membaca = B4 * $ B $ 1 seperti ditunjukkan di bawah:

Kemudian salin formula itu dari C4 hingga C5. Spreadsheet kini kelihatan seperti ini:

Oleh kerana kita menyalin formula satu sel ke bawah, iaitu peningkatan baris oleh satu, formula baru ialah = B5 * $ B $ 1. Kadar faedah dana bersama dihitung dengan betul sekarang, kerana kadar faedahnya berlabuh ke sel B1.

Ini adalah contoh yang baik apabila anda boleh menggunakan "nama" untuk merujuk kepada sel. Nama adalah rujukan mutlak. Sebagai contoh, untuk memberi nama "kadar faedah" kepada sel B1, klik kanan sel dan kemudian pilih "mentakrifkan nama."

Nama-nama boleh merujuk kepada satu sel atau julat, dan anda boleh menggunakan nama dalam formula, misalnya = interest_rate * 8 adalah perkara yang sama seperti tulisan = $ B $ 1 * 8.

Rujukan Campuran

Rujuk bercampur adalah ketika sama ada baris atau lajur berlabuh.

Misalnya, anda adalah seorang petani yang membuat anggaran. Anda juga memiliki kedai makanan dan menjual biji benih. Anda akan menanam jagung, kacang soya, dan alfalfa. Hamparan di bawah menunjukkan kos setiap ekar. "Kos per ekar" = "harga per paun" * "paun biji setiap ekar" - itulah yang akan dikenakan biaya untuk menanam ekar.

Masukkan kos per ekar sebagai = $ B2 * C2 dalam sel D2. Anda mengatakan bahawa anda ingin menambat harga pada setiap lorong pon. Kemudian salin formula itu ke baris lain dalam ruangan yang sama:

Kini anda ingin mengetahui nilai inventori benih anda. Anda memerlukan harga per paun dan jumlah pound dalam inventori untuk mengetahui nilai inventori.

Kami menambah dua lajur: "pon benih dalam inventori" dan kemudian "nilai inventori." Sekarang, salin sel D2 hingga F4 dan perhatikan bahawa rujukan baris di bahagian pertama formula asal ($ B2) dikemas kini ke baris 4 tetapi lajur tetap tetap kerana $ jangkar ke "B."

Ini adalah rujukan campuran kerana lajur adalah mutlak dan baris adalah relatif.

Rujukan Pekeliling

Rujukan bulat ialah apabila formula merujuk kepada dirinya sendiri.

Sebagai contoh, anda tidak boleh menulis c3 = c3 + 1. Penghitungan jenis ini dipanggil "lelaran" yang bermaksud ia mengulanginya sendiri. Excel tidak menyokong lelaran kerana ia mengira semuanya hanya satu kali.

Jika anda cuba lakukan ini dengan menaip SUM (B1: B5) dalam sel B5:

Skrin amaran muncul:

Excel hanya memberitahu anda bahawa anda mempunyai rujukan bulat di bahagian bawah skrin supaya anda tidak dapat melihatnya. Jika anda mempunyai rujukan bulat dan tutup spreadsheet dan membukanya sekali lagi, Excel akan memberitahu anda dalam tetingkap pop timbul yang anda mempunyai rujukan bulat.

Jika anda mempunyai rujukan bulat, setiap kali anda membuka hamparan, Excel akan memberitahu anda dengan tetingkap pop timbul yang anda mempunyai rujukan bulat.

Rujukan kepada Worksheets lain

"Buku kerja" adalah koleksi "lembaran kerja." Ringkasnya, ini bermakna anda boleh mempunyai berbilang spreadsheet (lembaran kerja) dalam fail Excel yang sama (workbook). Seperti yang dapat anda lihat dalam contoh di bawah, buku kerja contoh kami mempunyai banyak lembaran kerja (dalam warna merah).

Lembaran kerja secara lalai dinamakan Sheet1, Sheet2, dan sebagainya. Anda membuat yang baru dengan mengklik "+" di bahagian bawah skrin Excel.

Anda boleh menukar nama lembaran kerja kepada sesuatu yang berguna seperti "pinjaman" atau "anggaran" dengan mengklik kanan pada tab lembaran kerja yang ditunjukkan di bahagian bawah skrin program Excel, memilih nama semula, dan menaip nama baru.

Atau anda boleh klik dua kali pada tab dan menamakan semula.

Sintaks untuk rujukan lembaran kerja ialah = sel kerja! Sel. Anda boleh menggunakan rujukan semacam ini apabila nilai yang sama digunakan dalam dua lembar kerja, contohnya mungkin:

• Tarikh hari ini
• Kadar penukaran mata wang dari Dolar ke Euro
• Apa-apa sahaja yang berkaitan dengan semua lembaran kerja dalam buku kerja

Berikut adalah contoh "minat" lembaran yang membuat rujukan kepada lembaran kerja "pinjaman," sel B1.

Jika kita melihat lembaran kerja "pinjaman", kita boleh melihat rujukan kepada jumlah pinjaman:

Datang Seterusnya ...

Kami berharap anda kini mempunyai pegangan yang kukuh terhadap rujukan sel termasuk relatif, mutlak, dan bercampur-campur. Pasti banyak.

Itu sahaja untuk pelajaran hari ini, dalam Pelajaran 4, kita akan membincangkan beberapa fungsi berguna yang anda ingin tahu untuk kegunaan harian Excel.