MICROSOFT EXCEL: RUMUS DAN FUNGSI

MICROSOFT SHARE | MICROSOFT EXCEL: RUMUS DAN FUNGSI | Dalam pelajaran ini kita membahas referensi sel, cara untuk menyalin atau memindahkan rumus, dan format sel. Untuk memulai, mari kita menjelaskan apa yang kita maksud dengan referensi sel, yang mendukung banyak kekuatan dan fleksibilitas dari rumus dan fungsi. Sebuah pemahaman yang konkret tentang bagaimana referensi sel bekerja akan memungkinkan Anda untuk mendapatkan hasil maksimal dari spreadsheet Excel Anda.

A "referensi sel" berarti sel yang sel lain merujuk. Misalnya, jika dalam sel A1 Anda memiliki = A2. Kemudian A1 mengacu A2.


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

Sel-sel dalam spreadsheet yang disebut dengan 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 ke sel yang terletak di kolom kedua , baris ketiga , dan seterusnya .

Untuk tujuan tentang referensi sel belajar , kita akan di kali menulis mereka sebagai baris, kolom , hal ini tidak berlaku notasi dalam spreadsheet dan hanya dimaksudkan untuk membuat hal-hal yang lebih jelas .
Jenis referensi sel

Ada tiga jenis referensi sel .

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

Relatif - Relatif referensi berarti bahwa perubahan alamat sel saat Anda menyalin atau memindahkan itu , yaitu referensi sel relatif ke lokasi.

Dicampur - Ini berarti Anda dapat memilih untuk jangkar baik baris atau kolom ketika Anda menyalin atau memindahkan sel , sehingga satu perubahan dan yang lainnya tidak . Sebagai contoh, Anda bisa jangkar referensi baris kemudian bergerak sel turun dua baris dan di empat kolom dan referensi baris tetap sama . Kami akan menjelaskan hal ini lebih lanjut di bawah .

Referensi relatif

Mari kita lihat bahwa contoh sebelumnya - kira dalam sel A1 kita memiliki formula yang hanya mengatakan = A2 . Itu berarti output Excel dalam sel A1 apapun yang dimasukkan ke sel A2 . Dalam sel A2 kita telah mengetik " A2 " sehingga Excel menampilkan nilai " A2 " dalam sel A1 .

Sekarang, misalkan kita perlu membuat ruang dalam spreadsheet kami untuk lebih banyak data. Kita perlu menambahkan kolom dan baris di atas ke kiri, jadi kami harus pindah sel ke bawah dan ke kanan untuk membuat ruang.

Ketika Anda memindahkan sel ke kanan, jumlah kolom meningkat. Ketika Anda bergerak ke bawah, nomor baris meningkat. Sel yang menunjuk ke, referensi sel, perubahan juga. Hal ini digambarkan 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 pindah ke C5 sel.

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

Nilai yang ditampilkan adalah 0 karena sel C6 kosong. Dalam C6 sel kita ketik "Saya C6" dan sekarang C5 menampilkan "Saya C6".

Tuliskan rumus = RIGHT (A3, LEN (A3) - MENCARI (",", A3) - 1) atau menyalin teks ke sel C3. Jangan menyalin sel yang sebenarnya, hanya teks, menyalin teks, selain itu akan memperbarui referensi.

Anda dapat mengedit isi dari sel di bagian atas spreadsheet dalam kotak di sebelah mana berkata "fx". Kotak yang lebih panjang dari sel yang lebar, sehingga lebih mudah untuk mengedit.

Sekarang kita memiliki:

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

Sekarang kita memiliki Alexander Hamilton dan nama pertama Thomas Jefferson .

Gunakan kursor untuk menyorot sel C2 , C3 , dan C4 . Arahkan kursor ke sel B2 dan paste isi . Lihatlah apa yang terjadi - kita mendapatkan error : " # REF " . Mengapa ini?

Ketika kita menyalin sel dari kolom C ke kolom B diperbarui referensi satu kolom ke kiri = RIGHT ( A2 , LEN ( A2 ) - MENCARI ( " , " , A2 ) - 1 ) .

Ini berubah setiap referensi 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 di B2 misalnya, adalah = RIGHT ( # REF , LEN ( # REF ) - MENCARI ( " , " , # REF ) - 1 ! ) Dan hasilnya adalah # REF :

Menyalin Formula ke Kisaran Sel

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

Dengan " range" kita berarti lebih dari satu sel . Sebagai contoh, ( C1 : C10 ) berarti semua sel dari C1 sel ke sel C10 . Jadi itu adalah kolom sel . Contoh lain ( A1 : AZ1 ) adalah baris atas dari kolom A ke kolom AZ .

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

Berikut adalah contoh yang menggambarkan bagaimana untuk menyalin satu sel ke beberapa lokasi . Misalkan kita ingin menunjukkan biaya kami diproyeksikan untuk bulan di spreadsheet sehingga kita bisa membuat anggaran . Kami membuat spreadsheet seperti ini :

Sekarang salin rumus di sel C3 ( = B3 + C2 ) ke seluruh kolom untuk memberikan keseimbangan berjalan untuk anggaran kami . Update Excel referensi sel saat Anda menyalinnya . Hasilnya adalah sebagai berikut:

Seperti yang Anda lihat , setiap sel baru update relatif ke lokasi baru , sehingga update sel C4 rumus untuk = B4 + C3 :

Update sel C5 untuk = B5 + C4 , dan sebagainya :

Referensi absolut

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

Misalnya, masukkan rumus = $ A $ 1 pada setiap sel . The $ di depan berarti kolom A tidak mengubah kolom, $ di depan baris 1 berarti tidak mengubah kolom ketika Anda menyalin atau memindahkan sel untuk sel lain .

Seperti yang Anda lihat pada contoh di bawah ini , dalam sel B1 kita memiliki acuan relatif = A1.When kita copy B1 ke empat sel di bawahnya , referensi relatif = A1 perubahan pada sel ke kiri , sehingga menjadi A2 B2 , B3 menjadi A3 , dll sel-sel jelas tidak memiliki nilai diinput , sehingga output adalah nol .

Namun, jika kita menggunakan = $ A1 $ 1, seperti di C1 dan kami 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 . Rumus dalam C4 = B4 * B1 adalah " suku bunga " * "keseimbangan " = " bunga per tahun " .

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

Anggaran baru terlihat seperti ini :

Reksa dana baru mendapatkan $ 0 dalam bunga per tahun , yang tidak dapat tepat karena tingkat bunga jelas 5 % .

Excel menyoroti sel yang formula referensi . Anda dapat lihat di atas bahwa mengacu pada tingkat suku bunga ( B1 ) akan dipindahkan ke sel B2 kosong . Kita harus membuat referensi B1 mutlak dengan menulis $ B $ 1 menggunakan dolar menandatangani untuk jangkar baris dan kolom referensi .

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

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

Karena kita menyalin formula satu sel ke bawah , yaitu meningkat baris per satu , formula baru = B5 * $ B $ 1. Tingkat bunga reksa dana dihitung dengan benar sekarang , karena tingkat bunga adalah berlabuh ke sel B1 .

Ini adalah contoh yang baik ketika Anda bisa menggunakan " nama " untuk merujuk ke sel . Sebuah nama adalah sebuah referensi mutlak . Misalnya, untuk menetapkan nama " suku bunga " untuk sel B1 , klik kanan - sel dan kemudian pilih " menetapkan nama " .

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

Referensi Campuran ketika salah satu baris atau kolom berlabuh .

Misalnya, Anda adalah seorang petani membuat anggaran . Anda juga memiliki toko pakan dan menjual benih . Anda akan menanam jagung , kedelai , dan alfalfa . Spreadsheet di bawah ini menunjukkan biaya per hektar . "Biaya per acre " = " Harga per pon " * " pon biji per hektar " - itulah yang akan dikenakan biaya untuk menanam satu hektar .

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

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

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

Ini adalah referensi campuran karena kolom adalah mutlak dan baris relatif .
Referensi melingkar

Sebuah referensi melingkar adalah ketika formula mengacu pada dirinya sendiri .

Misalnya, Anda tidak dapat menulis c3 = c3 + 1 . Semacam ini perhitungan ini disebut " iterasi " berarti berulang . Excel tidak mendukung iterasi karena menghitung segala sesuatu hanya satu kali .

Jika Anda mencoba melakukan hal ini dengan mengetikkan SUM ( B1 : B5 ) dalam sel B5 :

Layar peringatan muncul :

Excel hanya memberitahu Anda bahwa Anda memiliki referensi melingkar di bagian bawah layar sehingga Anda mungkin tidak menyadarinya . Jika Anda memiliki referensi melingkar dan menutup spreadsheet dan buka lagi , Excel akan memberitahu Anda di jendela pop - up yang Anda memiliki referensi melingkar .

Jika Anda memiliki referensi melingkar , setiap kali Anda membuka spreadsheet , Excel akan memberitahu Anda dengan jendela pop - up yang Anda memiliki referensi melingkar .
Referensi ke Lembar Kerja Lainnya

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

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

Anda dapat mengubah nama worksheet untuk sesuatu yang berguna seperti " pinjaman " atau " anggaran " dengan mengklik kanan pada tab worksheet yang ditampilkan di bagian bawah layar program Excel , memilih mengubah nama , dan mengetikkan nama baru .

Atau Anda dapat mengklik dua kali pada tab dan mengubah nama itu .

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

Tanggal hari ini
Tingkat konversi mata uang dari dolar ke Euro
Segala sesuatu yang relevan untuk semua lembar kerja di buku kerja

Di bawah ini adalah contoh dari worksheet " bunga " membuat referensi untuk lembar kerja " pinjaman " , sel B1 .

Jika kita melihat pada " pinjaman " worksheet , kita dapat melihat referensi ke jumlah pinjaman :



(iklan)