Tutorial Microsoft Excel : Formula & Fungsi 2

Dalam kesempatan ini kita akan membahas referensi sel, bagaimana untuk menyalin atau memindahkan formula, dan format sel. Untuk memulai, mari kita memperjelas apa yang kita maksud dengan referensi sel, yang mendukung banyak kekuatan dan fleksibilitas dari formula dan fungsi. Sebuah pemahaman yang konkret tentang bagaimana kerja sel referensi akan memungkinkan Anda untuk mendapatkan hasil maksimal dari spreadsheet Excel Anda!

Catatan: kami berasumsi bahwa Anda sudah tahu bahwa sel adalah salah satu kotak di spreadsheet, diatur dalam kolom dan baris yang direferensikan oleh huruf dan angka berjalan horizontal dan vertikal.

Apa itu Referensi Sel?

Sebuah “referensi sel” berarti sel yang mengacu kepada sel lain . Misalnya, jika Anda memiliki  sel A1 = A2. Kemudian A1 mengacu A2.

excel formula cell reference A1 = A2

Mari kita tinjau apa yang sudah kita pelajari tentang baris dan kolom sehingga kita dapat menjelajahi referensi sel lebih lanjut.

Sel dalam spreadsheet yang disebut dengan baris dan kolom. Kolom vertikal dan diberi label dengan huruf. Baris horisontal dan diberi label dengan 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, kita akan menulis  sebagai baris, kolom, 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.

Relative – Relatif referensi berarti bahwa perubahan alamat sel saat Anda menyalin atau memindahkan itu; yaitu referensi sel relatif ke lokasi.

Mixed – Ini berarti Anda dapat memilih untuk membuat baik itu baris atau kolom ketika Anda menyalin atau memindahkan sel, sehingga jika satu mengalami perubahan sedangkan yang lainnya tidak. Misalnya, Anda bisa  membuat referensi baris kemudian sel bergerak ke bawah dua baris dan di kolom  empat dan referensi baris tetap sama. Kami akan menjelaskan hal ini lebih lanjut di bawah.

Referensi Relatif
Mari kita lihat bahwa contoh sebelumnya – dalam sel A1 kita memiliki formula yang hanya mengatakan = A2. Itu berarti output Excel dalam sel A1 apapun yang diinput ke dalam sel A2. Dalam sel A2 kita telah mengetik “A2” sehingga Excel menampilkan nilai “A2” di sel A1.

excel formula cell reference A1 = A2

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

Ketika Anda menggerakan sel ke kanan, jumlah kolom meningkat. Saat Anda bergerak ke bawah, nomor baris meningkat. Sel yang menunjuk , referensi sel, juga perubahan . Hal ini digambarkan di bawah ini:

excel formula membuat ruang lebih banyak

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 sel C5.

Kita salin sel dua kolom di sebelah kanan dan empat ke bawah. Ini berarti kita telah mengubah sel mengacu dua dan empat di bawah. A1 = A2 sekarang adalah C5 = C6. Bukan mengacu A2, sekarang sel C5 mengacu sel C6 .

excel formula menyalin sel

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

excel formula menyalin sel 2

Contoh: Teks Formula

Mari kita coba contoh lain. Ingat dari Pelajaran sebelumnya kita harus membagi nama lengkap ke nama pertama dan terakhir? Apa yang terjadi ketika kita menyalin rumus ini?

excel formula mengambil string dari kanan

Menulis rumus = RIGHT (A3, LEN (A3) – FIND (“,”, A3) – 1) atau menyalin teks ke sel C3. Jangan menyalin sel yang sebenarnya, hanya teks, menyalin teks, jika tidak maka akan memperbarui referensi.

Anda dapat mengedit isi dari sel di atas sebuah spreadsheet dalam kotak di sebelah kata “fx.” Itu merupakan sebuah kotak yang lebih panjang dari  lebar sel sehingga lebih mudah untuk mengedit.

Sekarang kita memiliki:

excel formula fungsi right

Tidak ada yang rumit, kita baru saja menulis sebuah formula baru dalam sel C3. Sekarang salin C3 sel C2 dan C4. Mengamati hasil berikut:

excel formula fungsi right 2

Sekarang kita memiliki Nuryanti Siti dan nama pertama Sofy Vian.

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

excel formula fungsi right paste REF

Ketika kita menyalin sel dari kolom C ke kolom B referensi diperbarui  satu kolom di sebelah kiri = RIGHT (A2, LEN (A2) – FIND (“,”, A2) – 1).

Itu berubah setiap referensi untuk 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 di B2 misalnya, adalah = RIGHT dan hasilnya adalah #REF (# REF, LEN (# REF) – – FIND (“,”, # REF) 1!!!):

excel formula REF

Menyalin Formula untuk Range 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 menunjuk ke tempat yang benar.

Dengan “range” berarti 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 berbagai lintas 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.

excel formula range

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

excel formula menyalin sel ke beberapa lokasi

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

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

excel formula menyalin sel ke beberapa lokasi 2

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

excel formula menyalin sel ke beberapa lokasi 3

Referensi absolut

Referensi absolut tidak berubah ketika Anda memindahkan atau menyalin sebuah sel. Kami menggunakan tanda $ untuk membuat referensi absolut – ingat bahwa, memikirkan tanda dolar sebagai jangkar.

Misalnya, masukkan formula = $ A $ 1 di setiap sel.  $ di depan berarti kolom A tidak mengubah kolom, $ di depan barisan 1 berarti tidak mengubah kolom ketika Anda menyalin atau memindahkan sel untuk sel lain.

Seperti yang Anda lihat pada contoh di bawah, di sel B1 kita memiliki referensi relatif = A1.Ketika kita menyalin B1 ke empat sel di bawahnya, referensi relatif = A1 perubahan sel ke kiri, sehingga B2 menjadi A2, 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 mutlak, sehingga tidak pernah berubah dan output selalu sama dengan nilai di sel A1.

excel formula Absolut referensi menggunakan $

Misalkan Anda ingin mengetahui minat Anda, seperti pada contoh di bawah. Rumus di C4 = B4 * B1 adalah “bunga” * “saldo” = “bunga per tahun.”

excel formula Absolut referensi perkalian

 

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

Anggaran baru terlihat seperti ini:

xcel formula Absolut referensi perkalian 2

Reksa dana baru mendapatkan $ 0 pada bunga per tahun, yang tidak dapat tepat karena tingkat bunga adalah jelas 5 persen.

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

Menulis ulang perhitungan pertama di C4 untuk membaca = B4 * $ B $ 1 seperti berikut:

excel formula Absolut referensi perkalian menggunakan $

Kemudian copy formula dari C4 ke C5. Spreadsheet sekarang terlihat seperti ini:excel formula Absolut referensi perkalian menggunakan $ copy

Karena kita salin formula satu sel ke bawah, yaitu meningkat baris dengan satu, formula baru = B5 * $ B $ 1. Tingkat bunga reksa dana dihitung dengan benar sekarang, karena tingkat bunga yang 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 “bunga” untuk sel B1, klik kanan-sel dan kemudian pilih “Define Name.”

excel formula Absolut referensi define name

Nama dapat merujuk pada satu sel atau range, dan Anda dapat menggunakan nama dalam formula, misalnya = tingkat bunga * 8 adalah hal yang sama seperti menulis = $ B $ 1 * 8.

Mixed Referensi 

Mixed referensi  adalah ketika salah satu baris atau kolom berlabuh.

Misalnya, Anda adalah seorang petani yang akan membuat anggaran. Anda juga memiliki toko pakan dan menjual benih. Anda akan menanam jagung, kedelai, dan alfalfa. Spreadsheet di bawah ini menunjukkan biaya per acre. “Biaya per acre” = “harga per pon” * “pon per acre” – itulah yang akan dikenakan biaya untuk menanam acre.
Masukkan biaya per acre sebagai = $ B2 * C2 di sel D2 . Anda mengatakan Anda ingin  kolom harga per pound  . Kemudian copy formula yang ke baris lain dalam kolom yang sama:

excel formula mixedreferensi

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

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

excel formula mixed referensi memakai $

Ini adalah mixed referensi  karena kolom adalah mutlak dan baris adalah relatif.

Referensi melingkar
Sebuah referensi melingkar adalah ketika formula mengacu sendiri.

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

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

excel formula reference circular

Sebuah layar peringatan muncul:

excel formula reference circular warning

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.

excel formula reference circular pop up

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 lain
Sebuah “buku kerja” adalah kumpulan “lembar kerja.” 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 misalnya buku kerja memiliki banyak lembar kerja (merah).

excel formula other worksheet

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

excel formula new sheet

Anda dapat mengubah nama worksheet untuk sesuatu yang berguna seperti “pinjaman” atau “anggaran” dengan mengklik kanan pada tab lembar kerja ditampilkan di bagian bawah layar program Excel, memilih mengganti nama, dan mengetik nama baru.

excel formula rename sheet

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
  • Apa pun yang relevan dengan semua lembar kerja di buku kerja

Di bawah ini adalah contoh dari lembar kerja “bunga” membuat referensi ke lembar kerja “pinjaman,” sel B1.

excel formula worksheet

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

excel formula worksheet pinjaman

 

Leave a Reply

Close Menu