Menentukan dan Mewujudkan Formula

Dalam pelajaran ini, kami memperkenalkan anda kepada peraturan asas untuk membuat formula dan menggunakan fungsi. Kami merasakan salah satu cara terbaik untuk belajar adalah melalui amalan, jadi kami menyediakan beberapa contoh dan terangkan secara terperinci. Topik-topik yang akan kita pelajari termasuk:

NAVIGASI SEKOLAH

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

baris dan lajur
fungsi matematik contoh: SUM ()
pengendali
keutamaan pengendali
Contoh fungsi kewangan: PMT (), bayaran pinjaman
menggunakan fungsi "rentetan" ("rentetan" adalah rentetan untuk "rentetan teks") di dalam formula dan fungsi bersarang

Formula adalah campuran "fungsi," "pengendali," dan "pengendali." Sebelum kita menulis beberapa formula, kita perlu membuat fungsi tetapi sebelum kita dapat membuat fungsi, kita perlu terlebih dahulu memahami nota baris dan lajur.

Baris dan Lajur

Untuk memahami cara menulis formula dan fungsi, anda perlu tahu mengenai baris dan lajur.

Baris berjalan secara melintang dan lajur berjalan secara menegak. Untuk mengingati yang mana, fikirkan satu lajur memegang bumbung - lajur naik ke atas dan dengan itu barisan pergi ke kiri kanan.

Lajur dilabelkan dengan huruf; baris mengikut nombor. Sel pertama dalam spreadsheet adalah A1 yang bermaksud lajur A, baris 1. Lajur dilabel A-Z. Apabila abjad habis, Excel meletakan huruf lain di hadapan: AA, AB, AC ... AZ, BA, BC, BC, dll.

Contoh: Jumlah Fungsi ()

Sekarang mari kita tunjukkan cara menggunakan fungsi.

Anda menggunakan fungsi dengan menaipnya secara langsung atau menggunakan wizard fungsi. Wizard fungsi terbuka apabila anda memilih fungsi dari menu "Formula" dari "Fungsi Perpustakaan." Jika tidak, anda boleh menaip = dalam sel dan menu drop-down berguna akan membolehkan anda memilih fungsi.

Penyihir memberitahu anda apa argumen yang perlu anda sediakan untuk setiap fungsi. Ia juga menyediakan pautan kepada arahan dalam talian jika anda memerlukan bantuan memahami apa fungsi dan cara menggunakannya. Sebagai contoh, jika anda menaip = jumlah ke dalam sel, wizard dalam talian menunjukkan kepada anda apa hujah yang diperlukan untuk fungsi SUM.

Apabila anda menaip fungsi, penyihir berada dalam baris atau kanan di jari anda. Apabila anda memilih fungsi dari menu "Formula", wizard adalah kotak pop timbul. Berikut ialah wizard pop timbul untuk fungsi SUM ().

Untuk fungsi pertama kami, mari kita gunakan SUM (), yang menambah senarai nombor.

Katakan kami mempunyai hamparan ini untuk mengandungi rancangan untuk membiayai percutian keluarga anda:

Untuk mengira jumlah kos yang anda boleh tulis = b2 + b3 + b4 + b5 tetapi lebih mudah untuk menggunakan fungsi SUM ().

Dalam Excel, cari simbol Σ di sudut kiri atas skrin Excel untuk mencari butang AutoSum (ahli matematik menggunakan huruf Yunani Σ untuk menambah satu siri nombor).

Sekiranya kursor berada di bawah nombor belanjawan keluarga, Excel cukup bijak untuk mengetahui bahawa anda mahu menyenaraikan nombor di atas di mana anda meletakkan kursor, jadi ia menyoroti nombor-nombor.

Tekan "enter" untuk menerima julat yang dipilih oleh Excel atau gunakan kursor untuk menukar sel mana yang dipilih.

Jika anda melihat apa Excel dimasukkan ke dalam spreadsheet anda dapat melihat bahawa ia menulis fungsi ini:

Dalam formula ini, Excel menjumlahkan nombor dari B2 hingga B9. Perhatikan, kami meninggalkan beberapa bilik di bawah baris 5 supaya anda boleh menambah bajet percutian keluarga - kosnya pasti akan muncul sebagai senarai anak-anak tentang apa yang mereka mahu lakukan dan di mana mereka mahu pergi lebih lama!

Fungsi matematik tidak berfungsi dengan huruf, jadi jika anda meletakkan huruf ke dalam lajur hasilnya ditunjukkan sebagai "#NAME?" Seperti ditunjukkan di bawah.

#NAME? menunjukkan bahawa ada beberapa jenis kesilapan. Ia boleh menjadi beberapa perkara termasuk:

rujukan sel buruk
menggunakan huruf dalam fungsi matematik
tidak menghalang hujah yang diperlukan
nama ejaan nama salah
operasi matematik haram seperti pembahagian oleh 0

Cara paling mudah untuk memilih hujah dalam pengiraan adalah dengan menggunakan tetikus. Anda boleh menambah atau mengalih keluar dari senarai argumen ke fungsi dengan membesarkan atau membuat kotak yang lebih kecil yang digambar Excel apabila anda menggerakkan tetikus atau klik pada sel lain.

Kami telah mengklik pada bahagian atas dataran yang dikeluarkan oleh Excel untuk mengambil "tiket penerbangan" daripada bajet. Anda boleh melihat simbol silang rambut yang boleh anda lukis untuk membuat julat yang dipilih lebih besar atau lebih kecil.

Tekan "masukkan" untuk mengesahkan hasilnya.

Pengiraan Pengiraan

Terdapat dua jenis pengendali: matematik dan perbandingan.

Pengendali Matematik	Definisi
+	tambahan
-	pengurangan, atau penolakan, mis., 6 * -1 = -6
*	pendaraban
/	bahagian
%	peratus
^	eksponen, cth. 2⁴= 2 ^ 4 = 2 * 2 * 2 * 2 = 16

Terdapat pengendali lain yang tidak berkaitan dengan matematik seperti "&" yang bermaksud menggabungkan (menyambungkan hujung-ke-akhir) dua rentetan. Sebagai contoh, = "Excel" & "Fun" sama dengan "Excel Fun".

Sekarang kita melihat pengendali perbandingan.

Pengendali Perbandingan	Definisi
=	sama, mis., 2 = 4 atau "b" = "b"
>	lebih besar daripada, contohnya, 4> 2 atau "b"> "a"
<	kurang daripada, mis., 2 < 4 or “a” < “b”
> =	lebih besar daripada atau sama dengan - cara lain untuk memikirkan ini adalah> = bermakna sama ada > atau =.
<=	kurang daripada atau sama dengan.
	tidak sama dengan, mis., 46

Seperti yang dapat anda lihat di atas, pengendali perbandingan bekerja dengan nombor dan teks.

Perhatikan, jika anda memasukkan = "a"> "b" ke dalam sel, ia akan mengatakan "FALSE" kerana "a" tidak lebih besar daripada "b." "B" > "B" atau "B"> "a."

Precedence Order Operator

Urutan mendahului adalah idea dari matematik. Excel harus mengikuti peraturan yang sama seperti matematik. Topik ini lebih rumit, jadi ambillah dan marilah masuk.

Urutan mendahulukan bermaksud perintah di mana komputer mengira jawapannya. Seperti yang dijelaskan dalam Pelajaran 1, kawasan bulatan adalah πr², yang sama seperti π * r * r. Ia adalah tidak (πr)².

Oleh itu, anda perlu memahami urutan keutamaan apabila anda menulis formula.

Pada umumnya, anda boleh mengatakan ini:

Excel pertama menilai item dalam kurungan yang berfungsi di dalamnya.
Ia kemudian menggunakan peraturan mendahului perintah matematik.
Apabila dua item mempunyai keutamaan yang sama, kerja Excel dibiarkan ke kanan.

Keutamaan pengendali matematik ditunjukkan di bawah, dalam urutan menurun.

(dan)	Apabila tanda kurung digunakan, mereka mengatasi peraturan biasa yang terdahulu. Ini bermakna Excel akan melakukan pengiraan ini terlebih dahulu. Kami jelaskan perkara ini di bawah.
-	Penolakan, mis., -1. Ini adalah sama dengan mendarab nombor sebanyak -1. -4 = 4 * (-1)
%	Peratusan, bererti berganda dengan 100. mis., 0.003 = 0.3%.
^	Eksponen, mis., 10 ^ 2 = 100
* dan /	Maju dan bahagikan. Bagaimanakah dua pengendali mempunyai keutamaan yang sama? Ia hanya bermakna jika formula mempunyai dua lagi pengendali dengan keutamaan yang sama, maka pengiraan dilakukan ke kiri ke kanan.
+ dan -	Penambahan dan penolakan.

Terdapat peraturan pendahuluan lain yang berkaitan dengan rentetan dan pengendali rujukan. Buat masa ini, kita akan tetap berpegang pada apa yang baru kita tutupi. Sekarang, mari kita lihat beberapa contoh.

Contoh: Mengira kawasan bulatan

Kawasan bulatan adalah= PI () * jejari ^ 2.

Melihat jadual di atas kita melihat bahawa pesaing datang sebelum pendaraban. Jadi komputer mula-mula menghitung radius ^ 2 dan kemudiannya gandaan yang dihasilkan oleh Pi.

Contoh: Mengira kenaikan gaji

Katakan bos anda memutuskan anda melakukan kerja yang baik dan dia akan memberi anda kenaikan 10%! Bagaimana anda mengira gaji baru anda??

Pertama, ingat bahawa pendaraban datang sebelum penambahan.

Adakah itu = gaji + gaji * 10% atau adakah = gaji + (gaji * 10%)?

Katakan gaji anda adalah $ 100. Dengan kenaikan 10%, gaji baru anda akan:

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

Anda juga boleh menulis seperti ini:

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

Dalam kes kedua, kami telah membuat perintah keutamaan secara jelas dengan menggunakan kurungan. Ingat bahawa kurungan dinilai sebelum operasi lain.

Dengan cara ini, cara yang lebih mudah untuk menulis ini ialah = gaji * 110%

Parentheses boleh bersarang di dalam satu sama lain. Oleh itu, apabila kita menulis (3 + (4 * 2)), bekerja dari dalam ke luar, mula-mula menghitung 4 * 2 = 8, kemudian tambahkan 3 + 8 untuk mendapatkan 11.

Beberapa lagi contoh

Berikut adalah contoh lain: = 4 * 3 / 2. Apakah jawapannya?

Kita lihat dari peraturan dalam jadual diatas yang * dan / mempunyai keutamaan yang sama. Jadi kerja Excel dari kiri ke kanan, 4 * 3 = 12 pertama, kemudian membahagikan bahawa dengan 2 untuk mendapatkan 6.

Sekali lagi anda boleh membuat yang jelas dengan menulis = (4 * 3) / 2

Bagaimana dengan = 4 + 3 * 2?

Komputer melihat kedua-dua * dan + pengendali. Oleh itu, mengikuti peraturan-peraturan precedence (pendaraban datang sebelum penambahan) ia mengira 3 * 2 = 6 terlebih dahulu, kemudian menambahkan 4 untuk mendapatkan 10.

Sekiranya anda ingin mengubah susunan keutamaan anda akan menulis = (4 + 3) * 2 = 14.

Bagaimana dengan yang satu ini = -1 ^ 3?

Kemudian jawapannya ialah -3 kerana komputer dikira = (-1) ^ 3 = -1 * -1 * -1 = -1.

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

Oleh itu, ada beberapa contoh perintah matematik dan keutamaan, kami berharap dapat membantu menyelesaikan beberapa perkara mengenai bagaimana Excel melakukan perhitungan (dan itu mungkin matematik yang cukup untuk bertahan seumur hidup untuk sesetengah daripada anda).

Contoh: Pembayaran Pinjaman Fungsi (PMT)

Mari lihat contoh untuk mengira bayaran pinjaman.

Mula dengan membuat lembaran kerja baru.

Format nombor dengan tanda dolar dan gunakan sifar tempat perpuluhan kerana kami tidak berminat pada sen sekarang kerana mereka tidak begitu penting apabila anda bercakap tentang dolar (dalam bab seterusnya kita meneroka cara memformatkan nombor secara terperinci). Sebagai contoh, untuk memformatkan kadar faedah, klik kanan pada sel dan klik "sel format." Pilih peratusan dan gunakan 2 tempat perpuluhan.

Begitu juga format sel-sel lain untuk "mata wang" dan bukannya peratusan dan pilih "nombor" bagi tempoh pinjaman.

Sekarang kita ada:

Tambah fungsi SUM () untuk "jumlah" perbelanjaan bulanan.

Nota, yang gadai janji sel tidak termasuk dalam jumlah keseluruhan. Excel tidak tahu bahawa anda ingin memasukkan nombor itu, kerana tidak ada nilai di sana. Oleh itu, berhati-hati untuk memanjangkan fungsi SUM () ke atas sama ada dengan menggunakan kursor atau menaip E2 di mana ia mengatakan E3 untuk memasukkan gadai janji dalam jumlah.

Letakkan kursor di dalam sel pembayaran (B4).

Pada menu Formula pilih drop-down "Kewangan" dan kemudian pilih fungsi PMT. Penyihir muncul:

Gunakan kursor untuk memilih "kadar.", "Nper" (istilah pinjaman), "Pv" ("nilai sekarang" atau jumlah pinjaman). Perhatikan bahawa anda perlu membahagikan kadar faedah sebanyak 12 kerana faedah dikira setiap bulan. Anda juga perlu membiayai jangka panjang pinjaman selama 12 tahun untuk mendapatkan pinjaman dalam beberapa bulan. Tekan "OK" untuk menyimpan hasil dalam hamparan.

Perhatikan bahawa pembayaran ditunjukkan sebagai nombor negatif: -1013.37062. Untuk menjadikannya positif dan menambahnya kepada perbelanjaan bulanan, arahkan ke sel gadai janji (E2). Ketik "= -" kemudian gunakan kursor untuk menunjuk ke medan pembayaran. Formula yang dihasilkan ialah = -B4.

Sekarang spreadsheet kelihatan seperti ini:

Perbelanjaan bulanan anda ialah $ 1,863 - Ouch!

Contoh: Fungsi Teks

Di sini kita menunjukkan cara menggunakan fungsi dalam formula dan fungsi teks.

Katakan anda mempunyai senarai pelajar seperti yang ditunjukkan di bawah. Nama pertama dan terakhir berada dalam satu medan yang dipisahkan oleh koma. Kita perlu meletakkan nama terakhir dan tegas ke dalam sel yang berasingan. Bagaimana kita melakukan ini?

Untuk menangani masalah ini, anda perlu menggunakan algoritma - iaitu, prosedur langkah demi langkah untuk melakukan ini.

Contohnya, lihat "Washington, George." Prosedur untuk membahagikannya kepada dua perkataan ialah:

Hitung panjang rentetan.
Cari kedudukan koma (ini menunjukkan di mana satu perkataan berakhir dan yang lain bermula).
Salin sebelah kiri rentetan sehingga koma.
Salin sebelah kanan tali dari koma hingga akhir.

Mari kita bincangkan bagaimana untuk melakukan ini dengan langkah demi langkah dalam "Excel" di Excel.

Kirakan panjang rentetan dengan fungsi = LEN (A3) - hasilnya ialah 18.
Sekarang tentukan kedudukan koma dengan memasukkan fungsi ini = MENEMUKAN (",", A3 ") - hasilnya ialah 11.
Sekarang ambil bahagian kiri rentetan sehingga koma dan buat formula bersarang ini menggunakan hasil dari Langkah 1: = KIRI (A3, MENEMUKAN (",", A3) -1). Perhatikan, kita perlu tolak 1 dari panjang kerana FIND memberikan kedudukan koma.

Berikut adalah apa yang semua kelihatan seperti apabila semua fungsi ditempatkan bersama dalam formula. Dalam sel B3, anda dapat melihat formula ini mengambil semua maklumat dari sel A3 dan input "Washington" ke dalamnya.

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

Perhatikan, bahawa kami dapat menyelamatkan keputusan dari Langkah 1 dalam sel dengan sendirinya, katakan, B6, kemudian tulis rumus yang lebih mudah = KIRI (A3, B6-1). Tetapi itu menggunakan satu sel untuk langkah sekejap.

Ingat kedudukan koma atau kirakan lagi.
Hitung panjang rentetan.
Kira aksara dari hujung rentetan ke koma.

Ambil bilangan huruf dari Langkah 3 dan tolak satu untuk menghilangkan koma dan ruang.

Mari kita lakukan langkah demi langkah ini.

Dari atas, ini ialah = MENEMUKAN (",", A3 ")
Panjang tali adalah = LEN (A3)
Anda perlu menggunakan beberapa matematik untuk mencari bilangan aksara yang perlu diambil: = LEN (A3) - MENEMUKAN (",", A3) - 1
Bahagian sebelah kanan rentetan yang kita mahu ialah = KANAN (A3, LEN (A3) - MENEMUKAN (",", A3) - 1)

Hamparan anda sekarang akan kelihatan serupa dengan tangkapan skrin di bawah. Kami menyalin formula sebagai teks ke bahagian bawah hamparan untuk memudahkan membaca dan melihatnya.

Itu agak sukar tetapi anda hanya perlu menulis formula ini sekali sahaja.

Datang Seterusnya ...

Ini menyimpulkan pelajaran kami hari ini. Anda harus mempunyai pemahaman yang cukup tegas sekarang mengenai formula dan fungsi, baris dan lajur, dan cara ini semua boleh digunakan melalui beberapa contoh yang pasti.

Akan datang seterusnya dalam Pelajaran 3, kami akan membincangkan rujukan dan pemformatan sel serta rumusan bergerak dan menyalin supaya anda tidak perlu menulis semula setiap formula berulang kali!