Mendefinisikan dan Membuat Formula

Dalam pelajaran ini, kami memperkenalkan Anda pada aturan dasar untuk membuat rumus dan menggunakan fungsi. Kami merasa salah satu cara terbaik untuk belajar adalah melalui latihan, jadi kami memberikan beberapa contoh dan menjelaskannya secara rinci. Topik yang akan kami bahas meliputi:

NAVIGASI SEKOLAH

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

baris dan kolom
contoh fungsi matematika: SUM ()
operator
prioritas operator
contoh fungsi keuangan: PMT (), pembayaran pinjaman
menggunakan fungsi "string" ("string" adalah singkatan untuk "string teks") di dalam rumus dan fungsi bersarang

Rumus adalah campuran dari "fungsi," "operator," dan "operan." Sebelum kita menulis beberapa rumus, kita perlu membuat fungsi tetapi sebelum kita dapat membuat fungsi, pertama-tama kita harus memahami notasi baris dan kolom.

Baris dan kolom

Untuk memahami cara menulis rumus dan fungsi, Anda perlu tahu tentang baris dan kolom.

Baris berjalan secara horizontal dan kolom berjalan secara vertikal. Untuk mengingat yang mana, pikirkan kolom yang menahan atap - kolom naik-turun dan dengan demikian baris ke kiri-kanan.

Kolom dilabeli dengan huruf; baris dengan angka. Sel pertama dalam spreadsheet adalah A1 yang berarti kolom A, baris 1. Kolom diberi label A-Z. Ketika alfabet habis, Excel menempatkan huruf lain di depan: AA, AB, AC ... AZ, BA, BC, BC, dll.

Contoh: Function Sum ()

Sekarang mari kita tunjukkan bagaimana cara menggunakan suatu fungsi.

Anda menggunakan fungsi dengan mengetiknya langsung di atau menggunakan fungsi wizard. Panduan fungsi terbuka ketika Anda memilih salah satu fungsi dari menu "Formula" dari "Function Library." Jika tidak, Anda bisa mengetikkan = dalam sel dan menu tarik-turun yang berguna memungkinkan Anda untuk memilih fungsi.

Wisaya memberi tahu Anda argumen apa yang perlu Anda sediakan untuk setiap fungsi. Ini juga menyediakan tautan ke instruksi online jika Anda memerlukan bantuan untuk memahami apa fungsi ini dan bagaimana menggunakannya. Misalnya, jika Anda mengetik = jumlah ke dalam sel, panduan in-line menunjukkan kepada Anda argumen apa yang diperlukan untuk fungsi SUM.

Saat Anda mengetik suatu fungsi, wisaya itu sejajar atau tepat di jari Anda. Saat Anda memilih fungsi dari menu “Rumus”, panduannya adalah kotak munculan. Ini adalah wizard pop-up untuk fungsi SUM ().

Untuk fungsi pertama kami, mari gunakan SUM (), yang menambahkan daftar angka.

Misalkan kita memiliki spreadsheet ini berisi rencana untuk menganggarkan liburan keluarga Anda:

Untuk menghitung total biaya, Anda dapat menulis = b2 + b3 + b4 + b5 tetapi lebih mudah untuk menggunakan fungsi SUM ().

Di Excel, cari simbol Σ di sudut kiri atas layar Excel untuk menemukan tombol JumlahOtomatis (ahli matematika menggunakan huruf Yunani Σ untuk menambahkan serangkaian angka).

Jika kursor berada di bawah angka anggaran keluarga, Excel cukup pintar untuk mengetahui bahwa Anda ingin menjumlahkan daftar angka di atas tempat Anda meletakkan kursor, jadi itu menyoroti angka.

Tekan "enter" untuk menerima rentang yang dipilih oleh Excel atau menggunakan kursor untuk mengubah sel mana yang dipilih.

Jika Anda melihat apa yang dimasukkan Excel ke dalam spreadsheet, Anda dapat melihat bahwa Excel menulis fungsi ini:

Dalam rumus ini, Excel menjumlahkan angka dari B2 ke B9. Catatan, kami meninggalkan beberapa kamar di bawah baris 5 sehingga Anda dapat menambah anggaran liburan keluarga - biayanya akan naik karena daftar anak-anak tentang apa yang ingin mereka lakukan dan ke mana mereka ingin pergi tumbuh lebih lama!

Fungsi matematika tidak berfungsi dengan huruf, jadi jika Anda memasukkan huruf ke dalam kolom, hasilnya ditampilkan sebagai "#NAME?" Seperti yang ditunjukkan di bawah ini.

#NAMA? menunjukkan bahwa ada beberapa jenis kesalahan. Ini dapat berupa sejumlah hal termasuk:

referensi sel yang buruk
menggunakan huruf dalam fungsi matematika
menghilangkan argumen yang diperlukan
nama fungsi pengejaan salah
operasi matematika ilegal seperti pembagian dengan 0

Cara termudah untuk memilih argumen dalam perhitungan adalah dengan menggunakan mouse. Anda bisa menambah atau menghapus dari daftar argumen ke fungsi dengan memperbesar atau memperkecil kotak yang digambar Excel saat Anda menggerakkan mouse atau mengklik di sel lain.

Kami telah mengklik di bagian atas kotak yang ditarik oleh Excel untuk mengambil "tiket pesawat" dari anggaran. Anda bisa melihat simbol lintas rambut yang bisa Anda gambar untuk membuat rentang yang dipilih lebih besar atau lebih kecil.

Tekan "enter" untuk mengkonfirmasi hasil.

Operator Perhitungan

Ada dua jenis operator: matematika dan perbandingan.

Operator Matematika	Definisi
+	tambahan
-	pengurangan, atau negasi, mis., 6 * -1 = -6
*	perkalian
/	divisi
%	persen
^	eksponen, mis. 2⁴= 2 ^ 4 = 2 * 2 * 2 * 2 = 16

Ada operator lain yang tidak terkait dengan matematika seperti "&" yang berarti menyatukan (bergabung ujung-ke-ujung) dua string. Sebagai contoh, = "Excel" & "is Fun" sama dengan "Excel is Fun".

Sekarang kita melihat operator perbandingan.

Operator Pembanding	Definisi
=	sama dengan, mis., 2 = 4 atau “b” = “b”
>	lebih besar dari, mis., 4> 2 atau “b”> “a”
<	kurang dari, mis., 2 < 4 or “a” < “b”
> =	lebih besar dari atau sama dengan - cara lain untuk memikirkan ini adalah> = berarti antara > atau =.
<=	kurang dari atau sama dengan.
	tidak sama dengan, mis., 46

Seperti yang Anda lihat di atas, operator perbandingan bekerja dengan angka dan teks.

Catatan, jika Anda memasukkan = "a"> "b" ke dalam sel, ia akan mengatakan "FALSE" karena "a" tidak lebih besar dari "b." "B" muncul setelah "a" dalam alfabet, jadi "a" > "B" atau "B"> "a."

Prioritas Pesanan Operator

Urutan diutamakan adalah ide dari matematika. Excel harus mengikuti aturan yang sama dengan matematika. Topik ini lebih rumit, jadi ambil napas dan mari selami.

Urutan diutamakan berarti urutan di mana komputer menghitung jawabannya. Seperti yang kami jelaskan dalam Pelajaran 1, area lingkaran adalah .r², yang sama dengan π * r * r. ini tidak ()r)².

Jadi, Anda harus memahami urutan prioritas ketika Anda menulis formula.

Secara umum, Anda dapat mengatakan ini:

Excel pertama mengevaluasi item dalam tanda kurung yang bekerja di dalam.
Ini kemudian menggunakan urutan aturan matematika.
Ketika dua item memiliki prioritas yang sama, Excel bekerja dari kiri ke kanan.

Diutamakan operator matematika ditunjukkan di bawah ini, dalam urutan menurun.

(dan)	Ketika tanda kurung digunakan, mereka mengesampingkan aturan prioritas yang normal. Ini berarti bahwa Excel akan melakukan perhitungan ini terlebih dahulu. Kami jelaskan lebih lanjut di bawah ini.
-	Negasi, mis., -1. Ini sama dengan mengalikan angka dengan -1. -4 = 4 * (-1)
%	Persen, artinya kalikan dengan 100. E.g., 0,003 = 0,3%.
^	Eksponen, mis., 10 ^ 2 = 100
* dan /	Lipat gandakan dan bagi. Bagaimana mungkin dua operator memiliki prioritas yang sama? Ini hanya berarti bahwa jika formula memiliki dua operator lagi dengan prioritas yang sama, maka perhitungan dilakukan dari kiri ke kanan.
+ dan -	Penambahan dan pengurangan.

Ada aturan prioritas lainnya yang terkait dengan string dan operator referensi. Untuk saat ini, kita hanya akan tetap dengan apa yang baru saja kita bahas. Sekarang, mari kita lihat beberapa contoh.

Contoh: Menghitung luas lingkaran

Luas lingkaran adalah= PI () * radius ^ 2.

Melihat tabel di atas kita melihat bahwa eksponen datang sebelum perkalian. Jadi komputer pertama menghitung jari-jari ^ 2 dan kemudian mengalikan hasil itu dengan Pi.

Contoh: Menghitung kenaikan gaji

Katakanlah bos Anda memutuskan Anda melakukan pekerjaan dengan baik dan dia akan memberi Anda kenaikan 10%! Bagaimana Anda menghitung gaji baru Anda?

Pertama, ingatlah bahwa perkalian datang sebelum penambahan.

Apakah itu = gaji + gaji * 10% atau apakah itu = gaji + (gaji * 10%)?

Misalkan gaji Anda adalah $ 100. Dengan kenaikan 10%, gaji baru Anda adalah:

= 100 + 100 * 10% = 100 + 10 = 110

Anda juga dapat menulisnya seperti ini:

= 100 + (100 * 10%) = 100 + 10 = 110

Dalam kasus kedua, kami telah membuat urutan diutamakan dengan menggunakan tanda kurung. Ingat bahwa tanda kurung dievaluasi sebelum operasi lainnya.

Omong-omong, cara termudah untuk menulis ini adalah = gaji * 110%

Kurung dapat bersarang di dalam satu sama lain. Jadi, ketika kita menulis (3 + (4 * 2)), bekerja dari dalam ke luar, pertama menghitung 4 * 2 = 8, kemudian tambahkan 3 + 8 untuk mendapatkan 11.

Beberapa contoh lagi

Berikut adalah contoh lain: = 4 * 3 / 2. Apa jawabannya?

Kita melihat dari aturan dalam tabel di atas bahwa * dan / memiliki prioritas yang sama. Jadi Excel bekerja dari kiri ke kanan, 4 * 3 = 12 pertama, kemudian membaginya dengan 2 untuk mendapatkan 6.

Sekali lagi Anda bisa membuatnya eksplisit dengan menulis = (4 * 3) / 2

Bagaimana dengan = 4 + 3 * 2?

Komputer melihat operator * dan +. Jadi mengikuti aturan diutamakan (perkalian datang sebelum penambahan) itu menghitung 3 * 2 = 6 pertama, kemudian menambahkan 4 untuk mendapatkan 10.

Jika Anda ingin mengubah urutan prioritas Anda akan menulis = (4 + 3) * 2 = 14.

Bagaimana dengan yang ini = -1 ^ 3?

Maka jawabannya adalah -3 karena komputer dihitung = (-1) ^ 3 = -1 * -1 * -1 = -1.

Ingatlah bahwa waktu negatif negatif adalah positif dan waktu negatif positif adalah negatif. Anda dapat melihat ini seperti ini (-1 * -1) * -1 = 1 * -1 = -1.

Jadi ada beberapa contoh urutan dan prioritas matematika, kami harap itu membantu membersihkan beberapa hal tentang bagaimana Excel melakukan perhitungan (dan itu mungkin cukup matematika untuk bertahan seumur hidup bagi sebagian dari Anda).

Contoh: Pembayaran Pinjaman Fungsi (PMT)

Mari kita lihat contoh untuk menghitung pembayaran pinjaman.

Mulai dengan membuat lembar kerja baru.

Memformat angka dengan tanda dolar dan menggunakan nol tempat desimal karena kami tidak tertarik dengan sen sekarang karena mereka tidak terlalu berarti ketika Anda berbicara tentang dolar (pada bab berikutnya kita akan mengeksplorasi cara memformat angka secara terperinci). Misalnya, untuk memformat tingkat bunga, klik kanan pada sel dan klik "format sel." Pilih persentase dan gunakan 2 tempat desimal.

Demikian pula, format sel lain untuk "mata uang", bukan persentase dan pilih "angka" untuk jangka waktu pinjaman.

Sekarang kita punya:

Tambahkan fungsi SUM () ke "total" pengeluaran bulanan.

Perhatikan, itu hipotek sel tidak termasuk dalam total. Excel tidak tahu bahwa Anda ingin memasukkan nomor itu, karena tidak ada nilai di sana. Jadi hati-hati untuk memperpanjang fungsi SUM () ke atas baik dengan menggunakan kursor atau mengetik E2 di mana dikatakan E3 untuk memasukkan hipotek dalam jumlah.

Masukkan kursor ke sel pembayaran (B4).

Pada menu Rumus pilih drop-down "Keuangan" dan kemudian pilih fungsi PMT. Wisaya muncul:

Gunakan kursor untuk memilih "rate.", "Nper" (jangka waktu pinjaman), "Pv" ("nilai sekarang" atau jumlah pinjaman). Perhatikan bahwa Anda harus membagi suku bunga dengan 12 karena bunga dihitung setiap bulan. Anda juga perlu melipatgandakan jangka waktu pinjaman dalam 12 tahun untuk mendapatkan masa pinjaman dalam beberapa bulan. Tekan "OK" untuk menyimpan hasilnya di spreadsheet.

Perhatikan bahwa pembayaran ditampilkan sebagai angka negatif: -1013.37062. Untuk membuatnya positif dan menambahkannya ke pengeluaran bulanan, tunjuk ke sel hipotek (E2). Ketik “= -” lalu gunakan kursor untuk menunjuk ke bidang pembayaran. Formula yang dihasilkan adalah = -B4.

Sekarang spreadsheetnya terlihat seperti ini:

Pengeluaran bulanan Anda adalah $ 1,863 - Aduh!

Contoh: Fungsi Teks

Di sini kami mendemonstrasikan cara menggunakan fungsi di dalam rumus dan fungsi teks.

Misalkan Anda memiliki daftar siswa seperti yang ditunjukkan di bawah ini. Nama depan dan belakang dalam satu bidang yang dipisahkan oleh koma. Kita perlu memasukkan nama terakhir dan perusahaan ke dalam sel yang terpisah. Bagaimana kita melakukan ini?

Untuk mengatasi masalah ini, Anda perlu menggunakan algoritma - yaitu, prosedur langkah demi langkah untuk melakukan ini.

Misalnya, lihat “Washington, George.” Prosedur untuk membaginya menjadi dua kata adalah:

Hitung panjang tali.
Temukan posisi koma (ini menunjukkan di mana satu kata berakhir dan yang lainnya dimulai).
Salin sisi kiri string ke atas hingga koma.
Salin sisi kanan string dari koma ke ujung.

Mari kita bahas bagaimana melakukan ini dengan "George Washington" selangkah demi selangkah di Excel.

Hitung panjang string dengan fungsi = LEN (A3) - hasilnya adalah 18.
Sekarang temukan posisi koma dengan memasukkan fungsi ini = MENCARI (“,”, A3 ”) - hasilnya adalah 11.
Sekarang ambil sisi kiri string ke atas hingga koma dan buat formula bersarang ini menggunakan hasil dari Langkah 1: = KIRI (A3, FIND (",", A3) -1). Catatan, kita harus mengurangi 1 dari panjang karena FIND memberikan posisi koma.

Inilah yang terlihat seperti ketika semua fungsi ditempatkan bersama dalam formula. Di sel B3, Anda dapat melihat rumus ini mengambil semua informasi dari sel A3 dan memasukkan "Washington" ke dalamnya.

Jadi kita memiliki "Washington," sekarang kita perlu mendapatkan "George." Bagaimana kita melakukan ini?

Perhatikan, bahwa kita dapat menyimpan hasil dari Langkah 1 dalam sel dengan sendirinya, katakanlah, B6, lalu tulis rumus yang lebih sederhana = KIRI (A3, B6-1). Tapi itu menghabiskan satu sel untuk langkah intermiten.

Ingat posisi koma atau hitung lagi.
Hitung panjang tali.
Hitung karakter dari ujung string ke koma.

Ambil jumlah karakter dari Langkah 3 dan kurangi satu untuk menghilangkan koma dan spasi.

Mari kita lakukan langkah demi langkah.

Dari atas, ini = MENCARI (“,”, A3 ”)
Panjang string adalah = LEN (A3)
Anda perlu menggunakan beberapa matematika untuk menemukan jumlah karakter yang harus diambil: = LEN (A3) - FIND (“,”, A3) - 1
Sisi kanan dari string yang kita inginkan adalah = KANAN (A3, LEN (A3) - FIND (“,”, A3) - 1)

Spreadsheet Anda sekarang akan terlihat mirip dengan tangkapan layar di bawah ini. Kami menyalin rumus sebagai teks ke bagian bawah spreadsheet untuk membuatnya lebih mudah dibaca dan dilihat.

Yang itu agak sulit tetapi Anda hanya perlu menulis formula ini sekali saja.

Selanjutnya…

Ini mengakhiri pelajaran kami untuk hari ini. Anda harus memiliki pemahaman yang cukup kuat sekarang tentang rumus dan fungsi, baris dan kolom, dan cara semua ini dapat digunakan melalui beberapa contoh yang pasti.

Datang berikutnya dalam Pelajaran 3, kita akan membahas referensi dan pemformatan sel serta memindahkan dan menyalin rumus sehingga Anda tidak perlu menulis ulang setiap rumus berulang-ulang!