Dalam tutorial ini, kami akan memandu Anda tentang cara membuat rumus kompleks di Excel dan juga melihat kesalahan khasyang timbul dari pengguna pemula karena kurangnya perhatian. Jika Anda baru mengenal Excel, kami menyarankan Anda untuk terlebih dahulu merujuk ke pelajaran di mana kita membahas pembuatan rumus sederhana.

Cara membuat rumus kompleks di Excel

Dalam contoh di bawah ini, kami akan mendemonstrasikan bagaimana Excel menghitung rumus kompleks berdasarkan urutan operasi. Dalam contoh ini, kami ingin menghitung jumlah pajak penjualan untuk layanan makanan. Untuk melakukan ini, tulis ekspresi berikut di sel D4: \u003d (D2 + D3) * 0,075... Rumus ini akan menjumlahkan nilai semua item dalam invoice kemudian dikalikan dengan jumlah pajak penjualan 7,5% (ditulis sebagai 0,075).

Excel mengikuti urutan dan menambahkan nilai dalam tanda kurung terlebih dahulu: (44.85+39.90)=$84.75 ... Kemudian dia mengalikan angka itu dengan tarif pajak: $84.75*0.075 ... Hasil perhitungan menunjukkan bahwa pajak penjualan akan menjadi $6.36 .

Sangatlah penting untuk memasukkan rumus kompleks dalam urutan yang benar. Jika tidak, perhitungan Excel mungkin tidak akurat. Dalam kasus kami, jika tidak ada tanda kurung, perkalian dilakukan terlebih dahulu, dan hasilnya salah. Tanda kurung adalah jalan terbaik menentukan urutan perhitungan di Excel.

Buat rumus yang kompleks menggunakan urutan tindakan

Dalam contoh di bawah ini, kami akan menggunakan tautan bersama dengan data kuantitatif untuk membuat rumus kompleks yang menghitung total biaya tagihan makanan. Rumus akan menghitung biaya setiap item menu dan kemudian menambahkan semua nilai bersama.

Anda dapat menambahkan tanda kurung ke rumus apa pun agar lebih mudah dibaca. Meskipun ini tidak akan mengubah hasil dalam contoh ini, kita masih bisa meletakkan tanda kurung di sekitar perkalian. Ini akan memperjelas bahwa itu dijalankan sebelum penambahan.

Excel tidak selalu memperingatkan tentang kesalahan rumus, jadi Anda perlu memeriksa sendiri semua rumus Anda. Untuk mempelajari bagaimana Anda bisa melakukan ini, lihat Rumus Pemeriksaan Pelajaran.

Jika Anda belum pernah bekerja dengan Excel sebelumnya, Anda akan segera mengetahui bahwa ini bukan hanya spreadsheet untuk memasukkan angka. Tentu saja, di Excel, Anda hanya dapat menghitung jumlah dalam baris dan kolom, tetapi Anda juga dapat menghitung pembayaran hipotek, menyelesaikan masalah matematika dan teknik, dan menemukan opsi yang paling disukai tergantung pada variabel yang diberikan.

Di Excel, semua ini dilakukan dengan menggunakan rumus dalam sel. Rumus ini digunakan untuk melakukan penghitungan dan tindakan lain dengan data di lembar. Rumus selalu dimulai dengan tanda sama dengan (\u003d), setelah itu Anda bisa memasukkan angka, operator matematika (seperti tanda + dan - untuk penjumlahan dan pengurangan), dan fungsi Excel bawaan yang sangat memperluas kapabilitas rumus.

Di bawah ini adalah contoh rumus yang mengalikan 2 dengan 3 dan menjumlahkan 5 sehingga hasilnya menjadi 11.

Berikut ini adalah contoh rumus yang dapat digunakan pada lembar kerja.

Bagian dari Formula Excel

Rumus juga bisa berisi satu atau lebih elemen seperti fungsi, tautan, operator dan konstanta.

Urutan tindakan dalam rumus

Dalam beberapa kasus, urutan evaluasi dapat memengaruhi nilai yang dikembalikan oleh rumus, jadi penting untuk memahami urutan standar evaluasi dan mengetahui cara mengubahnya untuk mendapatkan hasil yang diinginkan.

Menggunakan fungsi dan fungsi bertumpuk dalam rumus Excel

Fungsi adalah rumus yang sudah ditentukan sebelumnya yang melakukan penghitungan pada nilai yang diberikan - argumen - dalam urutan atau pola tertentu. Fungsi dapat digunakan untuk melakukan kalkulasi sederhana dan kompleks. Semua fungsi Excel bisa dilihat di tab Rumus.

Menggunakan tautan dalam rumus Excel

Tautan menunjuk ke sel atau rentang sel dalam lembar kerja dan memberi tahu Microsoft Excel, di mana nilai atau data yang dibutuhkan oleh rumus berada. Dengan menggunakan tautan, Anda dapat menggunakan data di berbagai bagian lembar kerja dalam satu rumus, serta menggunakan nilai satu sel dalam beberapa rumus. Selain itu, Anda bisa mengatur link ke sel di lembar berbeda dari satu buku kerja atau ke sel dari buku lain. Referensi ke sel di buku lain disebut link atau xrefs.

Menggunakan nama dalam rumus Excel

Anda dapat membuat nama tertentu untuk merujuk ke sel, rentang sel, rumus, konstanta, dan tabel Excel. Nama adalah sebutan singkat yang bermakna yang menjelaskan tujuan referensi sel, konstanta, rumus, atau tabel, karena mungkin sulit untuk dipahami secara sekilas. Berikut adalah contoh nama dan cara penggunaannya untuk membuat rumus lebih mudah dipahami.

Contoh 1

Contoh 2

Salin contoh data dari tabel di bawah ini dan tempelkan ke sel A1 yang baru lembar kerja Excel... Untuk menampilkan hasil rumus, pilih dan tekan F2, lalu tekan Enter. Selain itu, Anda dapat mengatur lebar kolom sesuai dengan data yang dikandungnya.

catatan: Dalam rumus di kolom C dan D, nama tertentu Penjualan diganti dengan referensi ke kisaran A9: A13, dan nama Informasi Penjualan diganti dengan kisaran A9: B13. Jika buku tidak memiliki nama-nama ini, rumus di D2: D3 akan mengembalikan kesalahan #NAME?.

Jenis contoh	Contoh yang tidak menggunakan nama	Contoh penggunaan nama	Rumus dan hasil menggunakan nama
	"\u003d SUM (A9: A13)	"\u003d SUM (Penjualan)	SUM (Penjualan)
	"\u003d TEXT (VLOOKUP (MAX (A9: 13), A9: B13,2, FALSE)," hh / mm / tttt ")	"\u003d TEXT (VLOOKUP (MAX (Penjualan), Info Penjualan, 2, FALSE)," dd.mm.yyyy ")	TEXT (VLOOKUP (MAX (Penjualan), Info Penjualan, 2, FALSE), "dd.mm.yyyy")
	Tanggal penjualan

Untuk informasi selengkapnya, lihat artikel Menentukan dan menggunakan nama dalam rumus.

Menggunakan rumus array dan konstanta array di Excel

Rumus array bisa melakukan beberapa penghitungan lalu mengembalikan satu nilai atau sekelompok nilai. Rumus array memproses beberapa set nilai yang disebut argumen array. Setiap argumen larik harus berisi jumlah baris dan kolom yang sama. Rumus array dibuat dengan cara yang sama seperti rumus lainnya, dengan perbedaan yang Anda gunakan CTRL + SHIFT + ENTER untuk memasukkan rumus. Beberapa fungsi bawaan adalah rumus array dan harus dimasukkan sebagai array untuk mendapatkan hasil yang benar.

Konstanta array dapat digunakan sebagai pengganti referensi jika Anda tidak perlu memasukkan setiap konstanta dalam sel terpisah di atas kertas.

Menggunakan rumus array untuk menghitung satu atau beberapa nilai

catatan: Saat Anda memasukkan rumus array, Excel secara otomatis mengapitnya dengan tanda kurung kurawal (dan). Jika Anda mencoba memasukkan tanda kurung kurawal secara manual, Excel akan menampilkan rumus sebagai teks.

Menggunakan konstanta array

Dalam rumus biasa, Anda bisa memasukkan referensi ke sel dengan nilai atau ke nilai itu sendiri, juga disebut konstanta. Demikian pula, Anda bisa memasukkan referensi ke larik atau larik nilai yang terdapat dalam sel (terkadang disebut konstanta larik) ke dalam rumus larik. Rumus array menerima konstanta dengan cara yang sama seperti rumus lainnya, namun, konstanta array harus dimasukkan dalam format tertentu.

Konstanta array dapat berisi angka, teks, nilai Boolean seperti TRUE atau FALSE, atau nilai kesalahan seperti # N / A. Satu konstanta array dapat berisi nilai dari berbagai tipe, misalnya (1,3,4; TRUE, FALSE, TRUE). Angka-angka dalam konstanta array bisa berupa bilangan bulat, desimal, atau eksponensial. Teks harus diapit dengan tanda petik ganda, misalnya "Selasa".

Pastikan persyaratan berikut terpenuhi saat memformat konstanta array.

Konstanta diapit oleh kurung kurawal ( { } ).

Kolom dipisahkan dengan koma ( , ). Misalnya, untuk mewakili nilai 10, 20, 30, dan 40, masukkan (10,20,30,40). Konstanta array ini adalah matriks 1-kali-4 dan terkait dengan referensi satu baris dan empat kolom.

Nilai sel dari garis yang berbeda dipisahkan oleh titik koma ( ; ). Misalnya, untuk merepresentasikan nilai 10, 20, 30, 40, dan 50, 60, 70, 80 dalam sel di bawah satu sama lain, Anda dapat membuat konstanta array 2-kali-4: (10,20,30, 40; 50, 60,70,80).

Menghapus rumus

Bersama rumus, hasil penghitungannya juga dihapus. Namun, Anda bisa menghapus rumus itu sendiri dan membiarkan hasil penghitungannya sebagai nilai dalam sel.

Menggunakan titik dua ( : ), referensi ke sel pertama dan terakhir dalam rentang dipisahkan. Sebagai contoh: A1: A5.

Argumen yang diperlukan ditentukan

Suatu fungsi dapat memiliki argumen wajib dan opsional (yang terakhir ditunjukkan dalam sintaksis dengan tanda kurung siku). Semua argumen yang diperlukan harus dimasukkan. Selain itu, cobalah untuk tidak memasukkan terlalu banyak argumen.

Rumus tersebut berisi tidak lebih dari 64 tingkat penumpukan fungsi

Level bersarang tidak boleh melebihi 64.

Nama buku dan lembar diapit tanda kutip tunggal

Jika nama lembar atau buku kerja yang Anda rujuk sel atau nilai berisi karakter non-alfabet, Anda harus mengapitnya dalam tanda kutip tunggal ( " ).

Path ke buku eksternal ditentukan

Nomor dimasukkan tanpa pemformatan

Jangan gunakan tanda dolar saat memasukkan angka dalam rumus, karena digunakan untuk menunjukkan referensi absolut. Misalnya, alih-alih nilainya $1000 harus masuk 1000 .

Penting: Hasil yang dihitung dari rumus dan beberapa fungsi lembar kerja Excel mungkin sedikit berbeda pada komputer Windows x86 atau x86-64 dan komputer Windows RT berbasis ARM.

Dalam posting ini, seperti pada kebanyakan posting blog, saya mengacu pada rentang sel tempat perintah diterapkan sebagai tabel Excel. Kotak - Tabel - Meja ... Anda dapat membaca lebih lanjut tentang tujuan dan manfaat dari prosedur semacam itu di. Jika Anda belum membaca postingan ini, akan lebih sulit bagi Anda untuk menyerap informasi yang disajikan di sini.

Sekarang mari kita lihat lebih dekat bagaimana rumus bekerja dalam tabel.

Garis total

Untuk menghitung total, tabel memiliki baris total. Untuk menampilkannya, pilih sel mana saja di dalam tabel dan centang kotak di pita Pembuat - Opsi Gaya Tabel - Baris Total ... Baris "Total" akan muncul di bawah tabel. Pilih sel mana saja di baris ini untuk menghitung total kolom. Di daftar turun bawah, pilih rumus yang akan digunakan program untuk menghitung total. Misalnya, Jumlah, Minimum, Rata-rata, dll. Klik "Fungsi Lainnya ..." untuk memilih fungsi lain.

Total baris di Excel

Anda juga dapat menulis rumus untuk menghitung total sendiri, itu ditulis dengan cara yang sama seperti rumus biasa, hanya sebagai pengganti alamat sel, nama mereka dari header tabel ditampilkan. Tentukan referensi ke sel dengan mouse, program akan mengganti namanya dengan sendirinya.

Ingatlah bahwa baris total memproses sel tabel yang terlihat di layar. Jika Anda telah menerapkan filter dengan beberapa nilai tabel, total akan dihitung ulang hanya untuk data yang dipilih.

Menulis rumus ke kolom

Jika Anda perlu melakukan penghitungan di kolom tabel, ini bahkan lebih mudah daripada di rentang sel biasa. Buat kolom baru di tabel. Tempatkan kursor di sel mana pun di kolom ini dan tulis rumusnya. Gunakan metode mengarahkan mouse ke referensi sel. Program akan menampilkan nama kolom, bukan dalam rumus, ini akan memudahkan persepsi rumus. Tekan Enter dan kaget! Excel tidak hanya akan menghitung hasilnya, tetapi juga menyalin rumus ke semua sel di kolom. Anda bahkan tidak perlu menyalin rumus secara manual!

Rumus Excel

Fitur ini tidak selalu berguna, jadi Anda dapat menonaktifkannya. Untuk melakukan ini, saat memasukkan formula, klik pada smart tag dan pilih “ Jangan secara otomatis membuat kolom kalkulasi "

Menyesuaikan Rumus Tabel Excel

Mengacu pada tabel dengan nama

Anda dapat merujuk ke isi tabel dengan nama. Untuk menentukan nama tabel, masukkan di bidang pada pita: Pembuat - Properti - Nama Tabel .

Sekarang, untuk melakukan beberapa operasi dengan data tabel, Anda bisa merujuknya dengan nama. Misalnya, tabel kami disebut People:

• Formula \u003d Jumlah (Orang) akan mencoba menjumlahkan semua sel tabel
• Rumus \u003d jumlah (Orang [Sales_Sum]) akan menentukan jumlah data di kolom "Jumlah_penjualan" di tabel "Orang"
• \u003d Orang [@Sales_Sum] / 100 - akan dibagi dengan 100 penjualan dari baris yang sama

Jika nomor baris sel dengan rumus dan sel dalam tabel tidak cocok, Excel akan menampilkan referensi tradisional dengan koordinat.

Penggunaan rumus dalam tabel sangat otomatis dan intuitif. Meskipun banyak yang menganggapnya sulit untuk digunakan pada awalnya, setelah beberapa hari Anda akan terbiasa dan menghargai manfaat yang diberikan spreadsheet Excel.

Terima kasih telah membaca postingan ini. Dan di artikel selanjutnya saya akan memberi tahu Anda. Sampai jumpa di halaman blog.

Seperti yang Anda ketahui, editor kantor spreadsheet Excel pada awalnya dirancang untuk melakukan perhitungan matematika, aljabar, dan lainnya, yang menggunakan input rumus khusus. Namun, mengingat cara menulis rumus di Excel, harus diingat bahwa sebagian besar operasi input pada dasarnya berbeda dari semua yang biasanya digunakan dalam kehidupan sehari-hari. Artinya, rumus itu sendiri memiliki bentuk yang sedikit berbeda dan menggunakan operator yang sama sekali berbeda yang digunakan saat menulis penghitungan standar.

Mari pertimbangkan pertanyaan tentang bagaimana menulis rumus di Excel, menggunakan beberapa contoh sederhana, tanpa menyentuh operasi yang rumit, untuk memahami yang Anda butuhkan untuk mempelajari program dengan cukup mendalam. Tetapi bahkan pengetahuan sebelumnya akan memberi pengguna pemahaman tentang prinsip-prinsip dasar penggunaan rumus untuk kasus yang berbeda.

Cara menulis rumus di Excel: konsep dasar

Jadi, input nilai rumus pada program agak berbeda dengan operasi standar, simbol yang digunakan dan operator yang digunakan. Saat memecahkan masalah bagaimana menulis rumus di Excel, perlu dibangun di atas konsep dasar yang digunakan di hampir semua sistem komputer.

Faktanya adalah bahwa mesin tidak memahami masukan dari kombinasi seperti "2 x 2" atau meletakkan komponen umum di luar tanda kurung ("2 + 2) 5"). Untuk ini, beberapa jenis simbol disediakan, yang disajikan dalam tabel di bawah ini, tidak termasuk operator logika.

Dalam hal ini, prioritas melakukan operasi dimulai dari derajat dan diakhiri dengan penjumlahan dan pengurangan. Selain itu, meskipun Excel dapat digunakan seperti kalkulator biasa, sebagai aturan, Anda harus menentukan nomor sel atau rentang sel untuk penghitungan. Tak perlu dikatakan bahwa format data dalam sel tersebut harus diatur ke yang sesuai (setidaknya numerik).

Jumlah dan perbedaan

Bagaimana cara menulis rumus jumlah atau selisih di Excel? Jadi, mari kita mulai dengan yang paling sederhana, saat Anda perlu menghitung jumlahnya. Di baris rumus (terlebih lagi, untuk semua operasi), tanda sama dengan pertama kali dimasukkan, setelah itu rumus yang diinginkan dimasukkan. Dalam kasus kalkulator konvensional, Anda dapat menentukan sel yang diinstal "\u003d 2 + 2".

Jika penjumlahan dilakukan untuk nilai yang dimasukkan langsung di sel lain (misalnya, A1 dan A2), rumusnya menjadi "\u003d A1 + A2". Tidak jarang menggunakan tanda kurung untuk menggunakan operator tambahan. Untuk perbedaan - hal yang sama, hanya dengan minus, bukan plus.

Saat Anda perlu menentukan jumlah sel atau jangkauannya, perintah penjumlahan khusus dapat digunakan (dalam versi Rusia "SUM", dalam bahasa Inggris - SUM). Saat menentukan beberapa sel, ini terlihat seperti ini: "\u003d SUM (A1; A2)", untuk rentang - "SUM (A1: A10)", asalkan Anda perlu menghitung jumlah semua angka dalam sel dari yang pertama hingga kesepuluh. Pada prinsipnya, jika Anda mengatur sel aktif, yang terletak tepat setelah sel terakhir di kolom dengan nilai awal, Anda tidak perlu memasukkan rumus, tetapi cukup klik tombol penjumlahan otomatis.

Perkalian, pembagian dan eksponen

Sekarang mari kita lihat cara menulis rumus perkalian atau pembagian di Excel. Urutannya sama seperti saat memasukkan jumlah atau selisih, hanya operator yang berbeda.

Untuk produk, formulir digunakan "\u003d A1 * A2", untuk hasil bagi - "A1 / A2". Omong-omong, ini adalah perintah yang persis sama yang dapat ditemukan saat menggunakan kalkulator Windows standar.

Simbol "^" digunakan untuk eksponen. Untuk nilai di sel A1, yang, misalnya, perlu dikuadratkan, rumus "\u003d A1 ^ 2" diterapkan.

Perhitungan persentase

Dengan bunga, jika Anda tidak menyentuh perhitungan yang rumit, semuanya juga sederhana. Bagaimana cara menulis rumus dengan persentase di Excel?

Cukup memasukkan rumus dalam bentuk "\u003d A1 * 5%", setelah itu Anda akan mendapatkan lima persen dari nilai tersebut di sel yang sesuai.

Menggunakan input rumus berbasis pemilihan sel

Tetapi semua ini terkait dengan penugasan manual atau yang disebut input langsung dari rumus (input langsung atau langsung). Bahkan, terkadang berguna untuk menggunakan mouse dan tombol Ctrl.

Sambil menahan tombol mouse, Anda cukup memilih sel yang diinginkan dengan terlebih dahulu memasukkan kalkulasi yang diperlukan di bilah rumus. Sel akan ditambahkan langsung ke bilah rumus. Namun, bergantung pada jenis rumusnya, terkadang tanda kurung harus dimasukkan secara manual.

Jenis sel absolut, relatif, dan campuran

Perlu dicatat secara terpisah bahwa program dapat menggunakan beberapa jenis sel, belum lagi data yang dikandungnya.

Sel absolut tidak berubah dan dilambangkan sebagai $ A $ 1, relative adalah referensi ke lokasi biasa (A1), campuran - ada kombinasi referensi ke sel absolut dan relatif ($ A1 atau A $ 1). Biasanya, format seperti itu digunakan saat membuat rumus saat data terlibat dalam lembar buku yang berbeda atau bahkan dalam file yang berbeda.

Rumus VLOOKUP

Terakhir, mari kita lihat cara menulis rumus VLOOKUP di Excel. Teknik ini memungkinkan Anda memasukkan data dari satu rentang ke rentang lainnya. Dalam kasus ini, metode tersebut agak mirip dengan yang digunakan untuk memecahkan masalah bagaimana menulis rumus "Kondisi" di Excel, yang menggunakan simbol yang ditunjukkan pada tabel di atas.

Secara umum, penghitungan seperti itu seperti filter sederhana yang diterapkan ke kolom, di mana Anda hanya ingin memfilter nilai yang tepat, bukan nilai perkiraan.

Dalam opsi ini, pertama melalui "Wizard Fungsi", rentang nilai tabel asli (pertama) digunakan, rentang kedua dengan memperbaiki konten (F4) ditunjukkan di bidang "Tabel", lalu kolom angka diindikasikan, dan nilai "FALSE" disetel di bidang tampilan interval jika sebenarnya, saat memfilter, Anda benar-benar hanya perlu mendapatkan nilai yang akurat, bukan perkiraan. Biasanya, rumus seperti itu lebih sering digunakan di gudang atau akuntansi, ketika tidak mungkin untuk menginstal produk perangkat lunak khusus.

Kesimpulan

Tetap mengatakan bahwa tidak semua rumus yang dapat digunakan di editor spreadsheet Excel dijelaskan di sini. Ini, bisa dikatakan, hanyalah dasar. Faktanya, jika Anda menggali lebih jauh ke trigonometri atau menghitung logaritma, matriks, atau bahkan persamaan tensor, semuanya akan terlihat jauh lebih rumit. Tetapi untuk mempelajari semua ini, manual untuk editor itu sendiri perlu dipelajari secara menyeluruh. Dan ini bukan lagi pertanyaan tentang fakta bahwa game logika yang paling sederhana pun dapat dibuat di Excel berdasarkan data yang berubah. Sebagai contoh, kami dapat mengutip "ular" yang sama, yang awalnya tidak ada hubungannya dengan editor spreadsheet, tetapi direproduksi oleh para penggemar di bidangnya di Excel.

Selebihnya, harus dipahami dengan jelas bahwa, setelah mempelajari rumus atau tindakan primitif dengan data, maka akan mungkin untuk dengan mudah menguasai perhitungan yang lebih kompleks, katakanlah, dengan pembuatan referensi silang, penggunaan berbagai jenis skrip atau Skrip VB, dll. Semua ini membutuhkan waktu, jadi jika Anda ingin mempelajari program dan semua kemungkinannya secara maksimal, Anda harus berkeringat tentang bagian teoritis.