Seperti yang diharapkan, apa yang bisa diselesaikan di MS Excel dapat diimplementasikan di Google Sheets. Tetapi banyak upaya untuk memecahkan masalah dengan bantuan mesin pencari favorit Anda hanya menghasilkan pertanyaan baru dan hampir tidak ada jawaban.
Oleh karena itu, diputuskan untuk membuat hidup lebih mudah bagi orang lain dan untuk memuliakan diri sendiri.

Secara singkat tentang utama

Agar Excel atau spreadsheet (Google spreadsheet) memahami bahwa yang tertulis adalah rumus, Anda harus meletakkan tanda "\u003d" di bilah rumus (Gambar 1).

• alfanumerik (SURAT \u003d KOLOM; ANGKA \u003d BARIS) misalnya "A1".
• dalam gaya R1C1, dalam sistem R1C1, baik baris maupun kolom ditetapkan dengan angka.

Alamat sel "B3" dalam sistem seperti itu akan terlihat seperti R3C2 (R \u003d baris \u003d baris, C \u003d kolom \u003d kolom). Untuk skrip, misalnya, kedua gaya digunakan.
Di mana kami menulis "\u003d formula", misalnya, \u003d SUM (A1: A10) dan nilai kami akan ditampilkan.
Prinsip operasi umum rumus RC ditunjukkan pada Gambar 2.

Gambar 2
Seperti yang Anda lihat dari Gambar 3, nilai sel relatif terhadap sel di mana rumus dengan tanda sama dengan akan ditulis. Untuk menjaga tampilan estetika formula, formula mengandung simbol yang dapat dihilangkan: RC \u003d RC.

Gambar 3
Perbedaan antara Gambar 2 dan Gambar 3 adalah bahwa Gambar 3 merupakan formulasi generik yang tidak terikat pada baris dan kolom (lihat nilai baris dan kolom), yang tidak dapat dikatakan tentang Gambar 2. Tetapi gaya RC pada spreadsheet terutama digunakan untuk menulis skrip javascript.

Jenis tautan (jenis pengalamatan)

Untuk merujuk ke sel, digunakan tautan, yang terdiri dari 3 jenis:

• Tautan relatif (contoh, A1);
• Tautan mutlak (contoh, $ A $ 1);
• Tautan campuran (contoh, $ A1 atau A $ 1, mereka setengah relatif, setengah mutlak).

Tanda $ di sini menunjukkan jenis tautan. Perbedaan antara berbagai jenis tautan dapat dilihat dengan menyeret gagang pelengkapan otomatis sel aktif atau rentang sel yang berisi rumus dengan tautan.

Tautan relatif

Tautan relatif "mengingat" pada jarak berapa (dalam baris dan kolom) Anda mengklik RELATIF ke posisi sel tempat Anda meletakkan "\u003d" (offset dalam baris dan kolom). Kemudian tarik ke bawah pada pegangan pelengkapan otomatis, dan rumus ini akan disalin ke semua sel yang kita rentangkan.

Tautan mutlak

Seperti yang disebutkan di atas, jika Anda menyeret formula yang berisi tautan relatif pada penanda pelengkapan otomatis, Tabel akan menghitung ulang alamatnya. Jika rumus berisi tautan absolut, alamatnya tidak akan berubah. Sederhananya - referensi absolut selalu menunjuk ke sel yang sama.
Untuk membuat referensi relatif menjadi absolut, cukup letakkan tanda "$" di depan huruf kolom dan alamat baris, misalnya $ A $ 1. Lebih cara cepat - pilih tautan relatif dan tekan tombol "F4" sekali, sedangkan spreadsheet akan meletakkan tanda "$" itu sendiri. Jika Anda menekan "F4" untuk kedua kalinya, tautan akan menjadi tipe campuran A $ 1, jika ketiga kalinya - $ A1, jika keempat kalinya - tautan akan kembali menjadi relatif. Dan di dalam lingkaran.

Tautan campuran

Tautan campuran setengah mutlak dan setengah relatif. Tanda dolar di dalamnya muncul sebelum huruf kolom atau sebelum nomor baris. Ini adalah jenis tautan yang paling sulit untuk dipahami. Misalnya, sel berisi rumus "\u003d A $ 1". Referensi A $ 1 bersifat relatif di kolom A dan absolut di baris 1. Jika kita menyeret rumus ini ke bawah atau ke atas pada penanda pelengkapan otomatis, tautan di semua rumus yang disalin akan mengarah ke sel A1, yaitu, akan berperilaku sebagai absolut. Namun, jika kita menyeret ke kanan atau ke kiri, tautan berperilaku sebagai relatif, yaitu, spreadsheet akan mulai menghitung ulang alamatnya. Dengan demikian, rumus yang dibuat dengan pelengkapan otomatis akan menggunakan nomor baris yang sama ($ 1), tetapi nilai literal kolom (A, B, C ...) akan berubah.

Mari kita lihat contoh penjumlahan sel dengan perkalian dengan koefisien tertentu.

Contoh ini mengasumsikan adanya nilai koefisien di setiap sel terhitung (sel D8, D9, D10 ... E8, F8 ...). (Gambar 4).
Panah merah menunjukkan arah peregangan dengan gagang pengisian rumus, yang ada di sel C2. Dalam rumusnya, perhatikan perubahan di sel D8. Saat direntangkan, hanya angka yang melambangkan string yang berubah. Meregangkan ke kanan hanya mengubah kolom.

Gambar 4
Mari sederhanakan contoh dengan menggunakan tanda $ (Gambar 5).

Gambar 5
Tetapi tidak selalu perlu untuk membekukan semua kolom dan baris, terkadang hanya pembekuan baris atau kolom saja yang digunakan (Gambar 6)

Gambar 6
Anda dapat membaca tentang semua rumus di situs resmi support.google.com
Penting: Data yang perlu diproses dalam rumus tidak boleh dalam format dokumen yang berbeda, ini hanya dapat dilakukan dengan menggunakan skrip.

Kesalahan rumus

Jika Anda salah mengeja rumus, Anda akan diberi tahu oleh komentar tentang kesalahan sintaks dalam rumus (Gambar 7).

Gambar 7
Meskipun kesalahan tidak hanya sintaksis, tetapi juga, misalnya, matematika, seperti pembagian dengan 0 (Gambar 7) dan lainnya (Gambar 7.1, 7.2, 7.3). Untuk melihat catatan yang menunjukkan kesalahan mana yang terjadi, arahkan kursor Anda ke segitiga merah di sudut kanan atas kesalahan.

Gambar 7.1

Gambar 7.2

Gambar 7.3
Untuk kenyamanan membaca tabel, kami akan mewarnai semua sel dengan rumus dengan warna ungu.
Untuk melihat formula "live", tekan Ctrl + hot key atau pilih View (View)\u003e All formula dari menu atas. (Angka 8).

Angka 8

Bagaimana rumus ditulis

Ada perbedaan dalam susunan kata rumus di buku referensi dan rumus yang digunakan untuk bekerja saat ini. Mereka terdiri dari fakta bahwa alih-alih "koma", yang digunakan sebelumnya dalam banyak rumus, "titik koma" sudah digunakan (perubahan terjadi lebih dari setengah tahun yang lalu).
Untuk melihat apa yang dirujuk rumus pada halaman ini (Gambar 9), Anda perlu mengklik bilah rumus di sebelah kanan label Fx (Fx terletak di bawah menu utama, di sebelah kiri).

Gambar 9
PENTING: Agar rumus berfungsi dengan benar, rumus harus ditulis dalam huruf LATIN. Rusia (Sirilik) "A" atau "C" dan Latin "A" atau "C" untuk rumusnya adalah 2 huruf yang berbeda.

Rumus

Rumus aritmatika.

Tentu saja, tidak ada yang akan menjelaskan operasi abadi dari penjumlahan, pengurangan, dll., Tetapi mereka akan membantu untuk memahami dasar-dasarnya. Beberapa contoh akan menunjukkan kepada Anda bagaimana mereka bekerja di lingkungan ini. Semua rumus diberikan dalam dokumen, tautan yang diberikan di akhir artikel, tetapi kami hanya akan berhenti di tangkapan layar.

Penjumlahan, pengurangan, perkalian, pembagian.

• Deskripsi: Rumus untuk penjumlahan, pengurangan, perkalian dan pembagian.
• Jenis rumus: "Sel_1 + Sel_2", "Sel_1-Sel_2", "Sel_1 * Sel_2", "Sel_1 / Sel_2"
• Rumusnya sendiri: \u003d E22 + F22, \u003d E23-F23, \u003d E24 * F24, \u003d E25 / F25.

Kami memiliki data awal dalam rentang E22: H25, dan hasilnya di kolom D. Gambar 10 menunjukkan header untuk semua data yang akan digunakan.

Gambar 10

Kemajuan.

• Deskripsi: Rumus untuk menambah semua sel berikutnya dengan satu (penomoran baris dan kolom).
• Jenis rumus: \u003d Sel sebelumnya + 1.
• Rumusnya sendiri: \u003d D26 + 1

Ingatlah bahwa jika Anda ingin menggunakan rentang, itu akan menjumlahkan semua sel dalam satu baris, dan jika Anda perlu menjumlahkan sel dalam urutan tertentu, maka Anda perlu menentukannya dengan ";" dalam urutan yang benar. Kami memiliki data awal untuk perkembangan di sel D26, dan hasilnya di sel E26: H26 (Gambar 11) Digunakan untuk memberi nomor baris dan kolom.

Gambar 11

Pembulatan.

• Deskripsi: Rumus untuk membulatkan angka dalam sel.
• Jenis rumus: \u003d ROUND (sel dengan angka); counter (berapa banyak angka yang harus dibulatkan setelah koma desimal).
• Rumusnya sendiri: \u003d ROUND (E28; 2).

Kami memiliki data awal di sel E28, dan hasilnya di sel D28 (Gambar 12)

Gambar 12
“ROUND” diakhiri dengan hukum matematika, jika setelah koma desimal ada angka 5 atau lebih, maka seluruh bagian bertambah satu, jika 4 atau kurang, maka tetap tidak berubah, Anda juga bisa membulatkan menggunakan menu FORMAT -\u003e Angka -\u003e " 1000,12 "2 tempat desimal (Gambar 13). Jika Anda membutuhkan lebih banyak karakter, maka Anda perlu mengklik FORMAT -\u003e Angka -\u003e Desimal pribadi -\u003e Dan tentukan jumlah karakter.

Gambar 13

Jumlah jika sel tidak berurutan.

Mungkin fitur yang paling familiar

• Deskripsi: jumlah angka yang berada di sel berbeda.
• Jenis rumus: \u003d SUM (bilangan_1; bilangan_2;… bilangan_30).
• Rumusnya sendiri: "\u003d SUM (E30; H30)" write through ";" jika sel berbeda.

Kami memiliki data awal di sel E30 dan H30, dan hasilnya di sel D30

(Gambar 14).
Jumlah jika sel berurutan.

• Deskripsi: penjumlahan angka yang mengikuti satu sama lain (secara berurutan).
• Jenis rumus: \u003d SUM (bilangan_1: bilangan_N).
• Rumusnya sendiri: \u003d SUM (E31: H31) "tulis melalui": "jika itu adalah rentang kontinu.
• Kami memiliki data awal dalam kisaran sel E31: H31, dan hasilnya di sel D31 (Gambar 15).

Gambar 15

Rata-rata.

• Deskripsi: Rentang angka dijumlahkan dan dibagi dengan jumlah sel dalam rentang.
• Jenis rumus: \u003d AVERAGE (sel dengan angka atau bilangan_1; sel dengan bilangan atau bilangan_2; ... sel dengan bilangan atau bilangan_30).
• Rumusnya sendiri: \u003d AVERAGE (E32: H32)

Kami memiliki data awal dalam kisaran sel E32: H32, dan hasilnya di sel D32 (Gambar 16).

Gambar 16
Tentu saja, ada yang lain, tapi kami melangkah lebih jauh.

Rumus teks.

Dari sekian banyak rumus teks yang dapat digunakan untuk melakukan apa saja dengan teks tersebut, yang paling populer, menurut saya, adalah rumus untuk "menempelkan" nilai teks. Ada beberapa opsi untuk implementasinya:

Merekatkan nilai teks (rumus).

• Deskripsi: nilai teks "perekatan" (opsi A).
• Jenis rumus: \u003d CONCATENATE (sel dengan angka / teks atau teks_1; sel dengan angka / teks atau teks_2;…, sel dengan angka / teks atau teks_30).
• Rumusnya sendiri: \u003d CONCATENATE (E36; F36; G36; H36).

Kami memiliki data awal dalam kisaran sel E36: \u200b\u200bH36, dan hasilnya di sel D36 (Gambar 17).
Menggunakan Google Docs, mereka sering melakukan survei karyawan atau membuat polling opini melalui Google Formulir (ini adalah formulir khusus yang dapat dibuat melalui menu Sisipkan-\u003e Formulir. Setelah mengisi formulir, data disajikan dalam tabel. Lalu, mereka menggunakan berbagai rumus untuk bekerja dengan data, misalnya, untuk merekatkan nama lengkap).

Gambar 17

Mengikat nilai numerik.

• Deskripsi: "menempelkan" nilai teks dengan tangan, tanpa menggunakan fungsi khusus (opsi B - penulisan rumus secara manual, kompleksitas rumusnya adalah apa saja.).
• Jenis rumus: \u003d sel dengan angka / teks 1 & "" & sel dengan angka / teks 2 & "" & sel dengan angka / teks 3 & "" & sel dengan angka / teks 4 ("" - spasi, & tanda berarti merekatkan , semua nilai teks ditulis dengan tanda petik “”).
• Rumusnya sendiri: \u003d E37 & "" & F37 & "" & G37 & "" & H37.

Kami memiliki data awal dalam kisaran sel E37: H37, dan hasilnya dalam sel D36 (Gambar 18 - angka terpaku).

Gambar 18

Mengikat nilai numerik dan teks.

• Deskripsi: "menempelkan" nilai teks dengan tangan, tanpa menggunakan fungsi khusus (varian C - tipe campuran, kompleksitas rumusnya ada).
• Jenis rumus: \u003d "text_1" & cell_1 & "text_2" & cell_2 & "text_3" & cell_3
• Penting: semua teks yang akan ditulis dengan "" tidak akan diubah untuk rumusnya.
• Rumusnya sendiri: \u003d "1 more" & E38 & "use" & F38 & "like US" & G38.

Kita memiliki data awal “1 more”, “use”, “like US” dan dalam range sel E38: G38, oleh karena itu disarankan untuk menggunakan rumus semacam ini, dan hasilnya ada di sel D36 (Gambar 19) .
Kami merekatkan teks dan nilai numerik.

Gambar 19

LOGIS DAN LAINNYA

Transfer data dari setiap lembar file yang sama.

Kami telah sampai pada yang paling menarik, menurut saya, fungsi: LOGIS DAN LAINNYA.
Salah satu rumus paling berguna:

• Deskripsi: transfer data dari lembar mana pun dari file yang sama (untuk Excel, Anda bisa mentransfer dari lembar satu buku kerja ke lembar lain dari buku kerja yang sama, atau dari lembar satu buku kerja ke lembar buku kerja lain).
• Jenis rumus: \u003d "Sheet_Name"! sel_1
• Rumusnya sendiri: \u003d Data! A15 (Data adalah lembar, A15 adalah sel di lembar itu).

Kami memiliki data awal pada lembar Data, sel A15 (Gambar 20), dan hasilnya pada lembar Formula di sel D41 (Gambar 20.1).

Gambar 20

Gambar 20.1

Larik rumus.

Kebanyakan program spreadsheet berisi dua jenis rumus array: "multisel" dan "sel tunggal".
Google Spreadsheet memisahkan jenis ini menjadi dua fungsi: CONTINUE dan ARRAYFORMULA.
Rumus array multisel memungkinkan rumus mengembalikan beberapa nilai. Anda bisa menggunakannya tanpa menyadarinya, cukup dengan memasukkan rumus yang mengembalikan beberapa nilai.
Rumus array satu sel memungkinkan Anda menulis rumus menggunakan input array, bukan output. Saat Anda mengapit rumus dalam fungsi \u003d ARRAYFORMULA, Anda bisa meneruskan larik atau rentang ke fungsi dan operator yang biasanya hanya menggunakan argumen non-larik. Fungsi dan operator ini akan diterapkan, satu untuk setiap entri dalam larik, dan akan mengembalikan larik baru dengan semua keluarannya.
Jika Anda ingin menyelidiki lebih detail, Anda harus mengunjungi support.google.
Secara sederhana, untuk bekerja dengan rumus yang mengembalikan larik data, untuk menghindari kesalahan sintaks, Anda harus memasukkannya ke dalam larik rumus.

Menjumlahkan sel dengan kondisi IF.

Untuk beroperasi dengan rumus logika, yang biasanya berisi larik data yang besar, mereka ditempatkan di larik rumus ARRAYFORMULA (rumus).

• Deskripsi: Menjumlahkan sel dengan kondisi IF (rumus SUMIF).
• Jenis rumus: \u003d SUMIF ('Sheet'! Range; kriteria; 'Leaf'! Total_range)

Untuk menjelaskan rumusnya, mari kita lihat lebih dekat contoh: 3 pembeli diinstruksikan untuk membeli produk dari daftar, tetapi membayar dalam satu jumlah. Setelah belanjaan dihancurkan di kasir, ada daftar produk (Gambar 21) di kolom A, dan jumlahnya di kolom B.
Tugasnya, struk fiskal seperti apa yang akan dimiliki setelah dicetak (Anda tinggal menambahkan produk dari 3 pembeli dan mengetahui jumlah produk secara total untuk setiap posisi)?

Gambar 21
Kami memiliki data awal di lembar Data (Gambar 21), dan hasilnya di lembar Formula di kolom D (Gambar 22). Kolom E, F, G memperlihatkan argumen yang digunakan dalam rumus, dan kolom H memperlihatkan tampilan umum rumus, yang ada di kolom D dan menghitung hasilnya.

Gambar 22
Contoh di atas menunjukkan gambaran umum tentang cara kerja rumus "Sum If" dengan satu ketentuan, tetapi yang paling umum digunakan adalah "Sum IF" (dengan beberapa ketentuan).

Menjumlahkan sel IF, banyak kondisi.

Kami terus mempertimbangkan masalah dengan produk di tingkat yang berbeda.
Pestanya baru saja dimulai, dan setelah menelepon teman-teman Anda, Anda mulai mengerti bahwa tidak akan ada cukup alkohol. Dan Anda perlu membelinya. Setiap teman Anda harus membawa minuman keras bersama mereka. Anda perlu mencari tahu jumlah botol bir yang perlu Anda bawa dan memberikan tugas kepada teman Anda.

• Deskripsi: jumlah IF (dengan beberapa kondisi).
• Jenis rumus: \u003d SUMIF ('Data'! Range_1 & 'Data'! Range_2; criteria_1 & criterion_2; 'Data'! Total_range).
• Rumusnya sendiri: \u003d (ARRAYFORMULA (SUMIF ((Data! E: E & Data! F: F); (B53 & C53); Data! G: G)))

Kami memiliki data awal pada lembar Data (Gambar 23).

Gambar 23
Misalkan pada lembar Formula, di sel B53 (criterion_1 \u003d Beer) harus ada nama minumannya, dan cell C53 (criterion_2 \u003d 2) adalah banyaknya teman yang akan membawa Beer. Hasilnya, sel D53 akan berisi hasil yang kita butuhkan untuk membeli 15 botol bir. (Gambar 23.1) artinya, rumus akan menentukan jumlah berdasarkan dua kriteria - bir dan jumlah teman.

Gambar 23.1
Jika ada lebih banyak posisi seperti itu, baris 16 dan 21 (Gambar 24), maka jumlah gelembung di kolom G dijumlahkan (Gambar 24.1).

Gambar 24
Total:

Gambar 24.1

Sekarang mari kita berikan contoh yang lebih menarik:

Ha ... pestanya terus berjalan, dan Anda ingat bahwa Anda membutuhkan kue, tetapi tidak mudah, dan kue super-mega, dengan rempah-rempah yang berbeda, yang, seperti keberuntungan, juga dienkripsi dengan angka. Tantangannya adalah membeli bumbu dalam jumlah kantong yang tepat untuk setiap bumbu. Koki mengenkripsi jumlah yang diperlukan dalam tabel (Gambar 25.1), kolom A dan B (di kolom yang berdekatan kami melakukan perhitungan kami).
Setiap bumbu memiliki nomor seri sendiri: 1,2,3,4. (Gambar 25).

Gambar 25
Tugas kita adalah menghitung jumlah nilai yang berulang, dalam kasus kita, ini adalah angka dari 1 hingga 4 di kolom B dan menentukan persentase masing-masing bumbu.

• Deskripsi: Menghitung jumlah digit identik dalam array besar dalam kondisi tambahan.
• Jenis rumus: COUNT IF ('Formula'! Range_A55: A61 + 'Formula'! Range_B55: B61; ConditionA "Spices" + ConditionB "number from 1 to 4"; Sheet "Formula '! Range_B55: B61) / ConditionB" number from 1 hingga 4 ")

• Rumusnya sendiri: \u003d ((ARRAYFORMULA (SUMIF ("Formula"! $ A $ 55: $ A $ 61 & "Formula"! $ B $ 55: $ B $ 61; $ F $ 55 & $ E59; "Formula" ! $ B $ 55: $ B $ 61))) / $ E59)

Kami memiliki data awal di kisaran A55: B61, kondisi pemilihan dipilih oleh sel F55 dan E59: E62, dan hasilnya ada di kisaran sel F59: F62 (menghitung jumlah pengulangan nilai numerik saat kondisi pertandingan).

• Deskripsi: Menghitung persentase bumbu.
• Jenis rumus: Jumlah * 100% / Jumlah_total
• Rumusnya sendiri: \u003d F58 * $ G $ 56 / F $ 56

Gambar 25.1
Pada akhirnya, kami memiliki jumlah pengulangan dan persentase.
Untuk menulis rumus dengan benar, Anda harus memahami sepenuhnya apa yang Anda miliki, apa yang ingin Anda peroleh dan dalam bentuk apa. Anda mungkin perlu mengubah tampilan data awal untuk ini.
Pindah ke contoh berikutnya

Menghitung nilai dalam sel yang digabungkan.

Jika rumus menggunakan nilai dalam "sel yang digabungkan", maka sel pertama untuk data yang digabungkan ditunjukkan, dalam kasus kami adalah kolom F, dan sel F65 (Gambar 26)



Gambar 26.
Dan akhirnya, kita sampai pada rumus terburuk.

Menghitung jumlah angka dalam daftar argumen.

Ada beberapa jenis perhitungan seperti itu, mereka cocok untuk tabel besar di mana Anda perlu menghitung jumlah kata atau jumlah angka yang identik. Tetapi dengan pemahaman yang benar tentang formula ini, Anda dapat mengerjakannya dengan keajaiban seperti, misalnya: menghitung kata tanpa memperhitungkan kata-kata pengecualian. Lihat contoh di bawah.

• Deskripsi: Menghitung jumlah sel yang berisi angka tanpa variabel teks.
• Jenis rumus: JUMLAH (nilai_1; nilai_2; ... nilai_30)
• Rumusnya sendiri: \u003d COUNT (E45; F45; G45; H45)

Kami memiliki data awal dalam rentang sel E70: H70, dan hasilnya di sel D70 (Gambar 27 - menghitung sel yang berisi nilai numerik dalam rentang yang berisi sel dengan teks).

Gambar 27.
Sel yang berisi teks dan angka juga tidak dihitung.

Gambar 27.1.

Menghitung jumlah sel yang berisi angka dengan variabel teks.

• Deskripsi: Menghitung jumlah sel yang berisi angka dengan variabel teks.
• Jenis rumus: COUNTA (nilai_1; nilai_2; ... nilai_30)
• Rumusnya sendiri: \u003d COUNTA (E46: H46)

Kami memiliki data awal di kisaran sel E71: H71, dan hasilnya di sel D71 (Gambar 28 - menghitung semua nilai dalam kisaran).

Gambar 28.
Selain itu, rumus menghitung sel yang hanya berisi tanda baca, tab, tetapi tidak menghitung sel kosong.

Gambar 28.1

Substitusi nilai dalam kondisi.

• Deskripsi: Substitusi nilai dalam kondisi.
• Jenis rumus: "\u003d IF (AND ((Condition1); (Condition2)); Result adalah 0, jika condition 1 dan 2 terpenuhi; jika tidak, maka hasilnya adalah 1)"
• Rumusnya sendiri: "\u003d IF (AND ((F73 \u003d 5); (H73 \u003d 5)); 0; 1)"

Kami memiliki data awal di sel F73 dan H73, dan hasilnya di sel D73 (Jika F73 \u003d 5 dan H73 \u003d 5 maka D73 \u003d 0 di semua kasus lain 1) (Gambar 29).

Gambar 29.

Gambar 29.1
Mari kita memperumit contoh.
Hitung jumlah sel di mana kerangka waktu ditulis, tidak termasuk kata "jawab otomatis", "sibuk", "-".

• Jenis rumus: "\u003d COUNTA (Range_A) -COUNTIF (Range_A;" auto answer ") - COUNTIF (Range_A;" - ") - COUNTIF (Range_A;" busy ")"

• Rumusnya sendiri: \u003d COUNTA ($ E74: $ H75) -COUNTIF ($ E74: $ H75; "answer") - COUNTIF ($ E74: $ H75; "-") - COUNTIF ($ E74: $ H75; "busy ")

Kami memiliki data awal dalam kisaran sel E74: H75, dan hasilnya di sel D74 (Gambar 30).

Gambar 30
Jadi kita sampai pada akhir program pendidikan kecil kita tentang formula di Google SpreadSheet dan saya memiliki harapan besar bahwa saya telah menjelaskan beberapa aspek pekerjaan analitis dengan formula.
Sejujurnya, rumusnya benar-benar sulit didapat. Masing-masing telah dibuat seiring waktu. Saya harap Anda menikmati artikel saya dan contoh di dalamnya.
Dan terakhir, sebagai hadiah. Dan semoga para pengembang memaafkan saya!

Rumus "PEMBUNUH DOKUMEN".

Jika Anda perlu menyembunyikan dokumen dari mata yang mengintip selamanya, maka rumus ini cocok untuk Anda.
Rumusnya sendiri: "\u003d (ARRAYFORMULA (SUMIF ($ A: $ A & $ C: $ C; $ H: $ H & F $ 2; $ C: $ C)))". $ H: $ H mengatur distribusi rumus. Setelah fomlula dijalankan (Gambar 31), di bawah ini sel akan mulai mengalikan fungsi selanjutnya LANJUTKAN (sel; baris; kolom).

Gambar 31
Rumus secara siklis menambahkan ke seluruh kolom rumus. Untuk membunuh dokumen, Anda perlu mencoba sedikit, membuat jumlah sel ke-N dan menulis rumus di sel pertama dari jumlah kolom ke-N. Segala sesuatu! Tidak ada orang lain yang dapat mengoreksi dan memeriksa dokumen!
Inilah yang dikatakan halaman bantuan Google tentang beban kerja dan batasan -

Organisasi penghitungan dalam spreadsheet, Referensi relatif, Referensi absolut, Referensi campuran, Fungsi built-in, Fungsi logis, Fungsi bersyarat, Bosov Informatika Grade 9, Informatika Grade 9

Tujuan utama spreadsheet adalah untuk mengatur semua jenis penghitungan. Anda sudah tahu bahwa:
- Perhitungan adalah proses menghitung dengan rumus;
- Rumus dimulai dengan tanda sama dengan dan dapat menyertakan tanda operasi, angka, referensi, dan fungsi bawaan. Pertama-tama mari kita pertimbangkan masalah yang terkait dengan pengaturan kalkulasi di spreadsheet.

3.2.1. Tautan relatif, absolut, dan campuran
Referensi menunjukkan sel atau rentang sel yang ingin Anda gunakan dalam rumus. Tautan memungkinkan:
- gunakan dalam satu rumus yang ada di bagian berbeda dari spreadsheet;
- gunakan nilai satu sel dalam beberapa rumus. Ada dua jenis tautan utama:
1) relatif - tergantung pada posisi rumus;
2) absolut - tidak tergantung pada posisi rumus.
Perbedaan antara referensi relatif dan absolut muncul saat Anda menyalin rumus dari sel saat ini ke sel lain.
Tautan relatif. Referensi relatif dalam rumus menentukan lokasi sel data relatif terhadap sel tempat rumus ditulis. Saat Anda mengubah posisi sel yang berisi rumus, referensi berubah.
Pertimbangkan rumus \u003d A1 ^ 2 yang ditulis di sel A2. Ini berisi referensi A1 relatif yang dianggap prosesor meja sebagai berikut: isi sel yang satu baris lebih tinggi dari yang ada rumusnya harus dikuadratkan.
Saat Anda menyalin rumus di sepanjang kolom dan di sepanjang baris, referensi relatif secara otomatis disesuaikan seperti ini:
- diimbangi dengan satu kolom menyebabkan perubahan referensi satu huruf di nama kolom;
- offset satu baris menyebabkan perubahan referensi nomor baris satu per satu.
Misalnya, saat Anda menyalin rumus dari sel A2 ke sel B2, C2 dan D2, referensi relatif otomatis berubah dan rumus di atas menjadi: \u003d B1 ^ 2, \u003d C1 ^ 2, \u003d D1 ^ 2. Saat menyalin rumus yang sama ke dalam sel AZ dan A4, kita mendapatkan, masing-masing, \u003d A2 ^ 2, \u003d AZ ^ 2 (Gbr. 3.4).

Contoh 1. Di kelas 8, kami mempertimbangkan masalah populasi kota tertentu, setiap tahun meningkat 5%. Mari kita hitung perkiraan populasi kota dalam 5 tahun ke depan di spreadsheet, jika tahun ini 40.000 orang.
Mari masukkan yang asli ke dalam tabel, masukkan rumusnya \u003d B2 + 0,05 * B2 di sel OT dengan tautan relatif; salin rumus dari sel OT ke kisaran sel B4: B7 (Gbr. 3.5).



Kami (sesuai dengan kondisi masalah) melakukan penghitungan tahunan populasi menurut rumus yang sama, yang awal selalu berada dalam sel yang terletak di kolom yang sama, tetapi satu baris lebih tinggi dari rumus penghitungan. Saat menyalin rumus yang berisi referensi relatif, perubahan yang kami butuhkan dibuat secara otomatis.
Tautan mutlak. Referensi absolut dalam rumus selalu merujuk ke sel di lokasi (tetap) tertentu. Dalam referensi absolut, $ ditempatkan sebelum setiap huruf dan angka, misalnya $ A $ 1. Mengubah posisi sel yang berisi rumus tidak mengubah referensi absolut. Saat menyalin rumus di sepanjang garis dan kolom, referensi absolut tidak disesuaikan (Gbr. 3.6).

Contoh 2. Seorang warga negara tertentu membuka rekening di bank seharga 10.000 rubel. Ia diberitahu bahwa setiap bulan jumlah setoran akan meningkat 1,2%. Untuk mengetahui jumlah yang mungkin dan kenaikan jumlah setoran setelah 1, 2, ..., 6 bulan, warga membuat perhitungan berikut (Gbr. 3.7).

Tautan campuran. Referensi campuran berisi kolom yang benar-benar dapat dialamatkan dan string yang relatif dapat dialamatkan ($ A1), atau kolom yang relatif dapat dialamatkan dan string yang benar-benar dapat dialamatkan (A $ 1). Saat Anda mengubah posisi sel yang berisi rumus, bagian relatif dari alamat berubah, tetapi bagian absolut dari alamat tidak berubah.
Saat menyalin atau mengisi rumus di sepanjang baris dan sepanjang kolom, referensi relatif secara otomatis disesuaikan, dan referensi absolut tidak disesuaikan (Gambar 3.8).

Untuk mengonversi tautan dari relatif ke absolut dan sebaliknya, Anda dapat memilihnya di baris input dan tekan F4 (Microsoft Office Excel) atau Shift + F4 (OpenOffice.org Calc). Jika Anda memilih referensi relatif, seperti A1, maka pertama kali Anda menekan tombol ini (kombinasi tombol), baris dan kolom akan disetel ke referensi absolut ($ A $ 1). Pada klik kedua, hanya string (A $ 1) yang akan mendapatkan tautan absolut. Pada klik ketiga, hanya kolom ($ A1) yang akan mendapatkan referensi absolut. Jika Anda menekan tombol F4 (kombinasi tombol Shift + F4) lagi, maka untuk link relatif kolom dan baris (A1) disetel lagi.
Contoh 3. Diperlukan untuk menyusun tabel penjumlahan angka sepuluh pertama, yaitu mengisi tabel form berikut ini:

Saat mengisi sel mana pun dari tabel ini, nilai yang sesuai dari sel kolom A dan baris 1 ditambahkan. Dengan kata lain, nama kolom tetap tidak berubah untuk istilah pertama (referensi absolut harus diberikan padanya) , tetapi nomor baris diubah (referensi relatif harus diberikan padanya); suku kedua mengubah nomor kolom (referensi relatif), tetapi nomor baris (referensi absolut) tetap tidak berubah.
Masukkan di sel B2 rumusnya \u003d $ A2 + B $ 1 dan salin ke seluruh rentang B2: J10. Anda harus memiliki tabel penjumlahan yang akan dikenali oleh setiap siswa kelas satu.


3.2.2. Fungsi bawaan
Saat memproses data di spreadsheet, Anda dapat menggunakan fungsi bawaan - rumus yang telah ditentukan sebelumnya. Fungsi mengembalikan hasil dari melakukan tindakan pada nilai yang bertindak sebagai argumen. Menggunakan fungsi memungkinkan Anda menyederhanakan rumus dan membuat proses penghitungan lebih jelas.
Beberapa ratus fungsi bawaan diimplementasikan dalam spreadsheet, dibagi menjadi: matematika, statistik, logika, tekstual, keuangan, dll.
Setiap fungsi memiliki nama unik yang digunakan untuk memanggilnya. Nama biasanya merupakan singkatan bahasa alami dari nama fungsi. Saat melakukan kalkulasi tabel, fungsi berikut sering digunakan:
SUM (SUM) - jumlah argumen;
MIN (MIN) - penentuan nilai terkecil dari daftar argumen;
MAX (MAX) - tentukan nilai terbesar dari daftar argumen.
Kotak dialog Wizard Fungsi menyederhanakan pembuatan rumus dan meminimalkan kesalahan ketik dan kesalahan sintaks. Saat Anda memasukkan fungsi ke dalam rumus, kotak dialog Panduan Fungsi menampilkan nama fungsi, semua argumennya, deskripsi fungsi dan setiap argumen, hasil saat ini dari fungsi dan seluruh rumus.
Contoh 4. Aturan wasit dalam pertandingan internasional di salah satu cabang olahraga adalah sebagai berikut:
1) kinerja setiap atlet dievaluasi oleh N juri;
2) nilai maksimum dan minimum (satu, jika ada beberapa) dari setiap atlet dibuang;
3) mean aritmatika dari sisa nilai diperhitungkan untuk atlet.
Informasi tentang kompetisi disajikan dalam spreadsheet:



Diperlukan untuk menghitung nilai semua peserta dalam kompetisi dan menentukan nilai pemenang. Untuk ini:
1) di sel А10, A1, А12 dan А14 kita masukkan teks "Nilai maksimum", "Nilai minimum", "Tanda akhir", "Skor pemenang";
2) di sel B10 kita masukkan rumus \u003d MAX (OT: B8); salin isi sel B10 ke sel C10: F10;
3) di sel В11 kita masukkan rumusnya \u003d MIN (ВЗ: В8); salin isi sel B10 ke sel C11: F11;
4) di sel B12 kita masukkan rumusnya \u003d (SUM (OT: B8) -B10-B11) / 4; salin isi sel B12 ke sel C12: F12;
5) di sel B14 kita masukkan rumus \u003d MAKC (B12: F12).

3.2.3. Fungsi logika
Dalam mempelajari materi sebelumnya, Anda telah berulang kali menemukan operasi logis BUKAN, DAN, ATAU (BUKAN, DAN, ATAU). Anda menggunakan ekspresi logis yang dibangun dengan bantuan mereka saat mengatur pencarian di database, saat memprogram berbagai proses komputasi.
Operasi Boolean juga diterapkan di spreadsheet, tetapi di sini mereka disajikan sebagai fungsi: namanya ditulis lebih dulu operasi logisdan kemudian operand logis dicantumkan dalam tanda kurung.
Misalnya, ekspresi boolean yang sesuai dengan pertidaksamaan ganda 0<А1<10, в электронных таблицах будет записано как И(А1>0; A1<10).
Ingat bagaimana kita menulis ekspresi logis yang serupa ketika kita mengenal database dan bahasa pemrograman Pascal.
Contoh 5. Mari kita hitung dalam spreadsheet nilai dari ekspresi logis BUKAN A DAN BUKAN untuk semua kemungkinan nilai dari variabel logis yang termasuk di dalamnya.

Dalam memecahkan masalah ini, kami mengikuti algoritme terkenal untuk membangun tabel kebenaran untuk ekspresi logis. Perhitungan dalam rentang sel SZ: C6, D3: D6, EZ: E6 dilakukan oleh komputer sesuai dengan rumus yang telah kami tentukan.

Untuk memeriksa kondisi saat melakukan penghitungan di spreadsheet, fungsi IF logis yang disebut fungsi bersyarat diimplementasikan.
Fungsi bersyarat memiliki struktur berikut:
JIKA (<условие>; <действие1>; <действие2>)
Sini<условие> - Ekspresi Boolean, yaitu ekspresi apa pun yang dibuat menggunakan operasi dan operasi logika yang mengambil nilai TRUE atau FALSE.
Jika ekspresi boolean benar, maka nilai sel tempat fungsi bersyarat ditulis akan ditentukan<действие1>jika salah -<действие2>.
Apa yang mengingatkan Anda tentang fungsi kondisional?
Contoh 6. Pertimbangkan masalah masuk ke tim bola basket sekolah: seorang siswa dapat diterima ke dalam tim ini jika tingginya minimal 170 cm.
Data pelamar (nama keluarga, tinggi) disajikan dalam spreadsheet.

Menggunakan fungsi kondisional dalam rentang sel СЗ: С8 memungkinkan Anda membuat keputusan (diterima / tidak diterima) untuk setiap pemohon.
Fungsi COUNTIF memungkinkan Anda menghitung jumlah sel dalam rentang yang memenuhi kondisi tertentu. Fungsi ini menghitung jumlah pelamar yang dipilih untuk tim di sel C9.

menulis rumus matematika

Karakteristik umum dan peluncuran editor formula

Penulisan dan pengeditan rumus di Word dilakukan dengan menggunakan editor rumus Microsoft Equation 3.0 yang berisi sekitar 120 template. Ini memungkinkan Anda untuk memasukkan tanda dan ekspresi matematika ke dalam dokumen Anda, termasuk pecahan, pangkat, integral, dll. Saat Anda menulis rumus, gaya yang sesuai secara otomatis diterapkan untuk berbagai komponennya (ukuran font yang diperkecil untuk eksponen, miring untuk variabel, dll.).

Contoh. Meluncurkan editor formula.

1. Tempatkan kursor di tempat Anda memasukkan dan mengedit rumus.

2. Di menu Memasukkanatur perintahnya Sebuah Objek…, buka kotak dialog Memasukkan Objek.

3. Pada tab Makhlukdi lapangan Jenis objek:mari kita pilih MicrosoftEquation3.0.

4. Klik pada tombol OK.

Ini akan membuka dialog untuk bekerja dengan editor rumus.

Untuk memulai editor rumus untuk mengedit rumus yang sudah ada, klik dua kali di bidang rumus.

Penyelesaian pengeditan atau penulisan rumus dilakukan di luar kotak entri rumus.

Antarmuka editor formula

Setelah memulai editor rumus, jendela editor rumus akan terbuka, yang memiliki bilah alat sendiri. Panel ini terdiri dari dua baris tombol:

 akses ke set karakter,

 akses ke set template.

Anda dapat memasukkan huruf-huruf dari alfabet Rusia dan Latin ke dalam rumus dari keyboard, serta tanda-tanda operasi matematika paling sederhana (+, -, /).

Deretan tombol untuk mengakses kumpulan karakter memungkinkan Anda memasukkan simbol matematika (tanda operasi dan huruf alfabet Yunani) ke dalam rumus.

Kumpulan karakter berikut ini terletak di baris atas toolbar dari kiri ke kanan:

• Simbol hubungan;

 Interval dan titik;

 Perbedaan matematika;

 Tanda-tanda operasi;

 Simbol panah;

• Simbol teori himpunan;

 Tanda-tanda logis;

 Berbagai simbol;

 Huruf Yunani.

Dengan menggunakan templat bilah alat, Anda dapat memasukkan tanda-tanda sejumlah operasi matematika ke dalam rumus, mengatur simbol integral, jumlah, produk. Selain itu, templat memungkinkan Anda mengatur bentuk ekspresi matematika (pecahan, derajat, indeks, matriks, dll.) Untuk input simbol matematika berikutnya ke dalam benda kerja yang diperoleh menggunakan templat.

Baris bawah dari toolbar berisi kumpulan template berikut dari kiri ke kanan:

 Template kendala;

 Templat dari pecahan dan akar;

 Pembuatan sub dan superskrip;

 Integral;

 Garis besar dan garis bawah;

 Panah bertanda;

 Karya dan template teori himpunan;

 Template matriks.

Saat mengetik karakter rumus, kursornya berupa karakter “atau”. Karakter yang dimasukkan dalam rumus ditempatkan di kanan atau kiri bilah vertikal dan di atas garis horizontal dari kursor input.

Menulis dan mengedit rumus

Saat menulis dan mengedit rumus, karakter berikutnya dapat dimasukkan di ujungnya ke baris input utama - tempat karakter yang dimasukkan secara otomatis ditandai dengan slot (persegi panjang dengan garis putus-putus). Jika Anda perlu memasukkan simbol untuk jumlah, integral, atau struktur rumus kompleks lainnya, Anda harus menggunakan mouse untuk memilih ikon yang sesuai dalam kumpulan templat yang sesuai.

Kosong yang diperoleh dengan menggunakan templat dapat dimasukkan di tengah slot. Dengan demikian, rumus multi-langkah dibuat.

Mengedit rumus yang ada melibatkan penghapusan elemen individualnya dan memasukkan yang baru menggunakan editor rumus.

Contoh. Menulis penggalan rumus.

Mari kita perkenalkan sebuah fragmen formula dari form: .

1. Klik untuk membuka submenu dengan satu set templat jumlah.

2. Pilih templat penjumlahan dengan batas atas dan bawah (di baris atas, templat paling kanan) dengan mengklik mouse.

Hasilnya, kolom kosong akan muncul di jendela pengeditan rumus: .

3. Mari masukkan simbol, angka atau ekspresi yang diperlukan di setiap slot, setelah menempatkan kursor input di sana, dan fragmen rumus akan mengambil bentuk yang diinginkan.

Contoh. Menghapus elemen rumus.

1. Pilih elemen yang akan dihapus dengan mengklik mouse.

2. Tekan tombol .

Jika elemen rumus adalah bagian dari fragmen yang dibuat menggunakan template, maka setelah menghapusnya, slot input. Slot masukan hanya dapat dihapus bersama dengan templatnya.

Dalam beberapa kasus, setelah menghapus elemen rumus, representasi grafik dari beberapa elemen yang tersisa mungkin rusak. Untuk mengembalikan formula ke tampilan normal, jalankan perintah Gambar ulangtidak bisa Melihat.

Contoh. Memasukkan item baru ke dalam formula.

1. Tempatkan kursor penyisipan pada posisi yang diinginkan dalam rumus.

2. Mari kita perkenalkan urutan simbol yang diperlukan.

3. Jika perlu, dengan menggunakan templat, sisipkan bagian kosong, lalu isi slotnya dengan simbol yang diperlukan.

Contoh. Menulis rumus dengan garis pecahan.

.

1. Tempatkan kursor di lokasi rumus.

3. Di slot untuk memasukkan rumus menggunakan keyboard, ketikkan awal rumus "

».

4. Di set Pola pecahan dan akarklik pada template

(template kiri atas).

Ini akan memasukkan templat dengan dua slot di pembilang dan penyebut pecahan.

5. Ke dalam slot penyebut, masukkan ekspresi

, dan di slot pembilang -

.

6. Dalam satu set template Membuat subskrip dan superskrippilih template yang menentukan pembuatan indeks kanan atas.

7. Di slot yang muncul, masukkan ekspresi untuk derajat "n-1".

8. Tempatkan kursor di akhir bagian rumus yang sudah diketik.

9. Dalam satu set template Membuat subskrip dan superskrippilih template.

10. Di bagian kosong di slot utama, masukkan simbol " e", Dan di slot superskrip yang tepat kita masukkan ekspresi derajat" - nt».

Dengan mengklik di luar kotak rumus, tutup dialog pengeditan rumus.

Menulis rumus matriks

Untuk menulis rumus matriks di baris bawah toolbar, ada satu set Template matriks.

Contoh. Menulis rumus dengan tanda kurung kurawal.

Pertimbangkan untuk menulis rumus dari formulir:

2. Buka jendela editor formula.

3. Di slot untuk memasukkan rumus dari keyboard, masukkan " y= ».

4. Di set Template pembatasklik pada template.

Ini akan memasukkan penjepit keriting dengan slot di sebelah kanannya.

5. Tempatkan kursor di slot bernama.

6. Dalam kumpulan template Matrix, pilih template :.

Akibatnya, slot di sebelah kanan kurung kurawal diubah menjadi dua slot yang terletak satu di atas yang lain. Ini akan meningkatkan ukuran penjepit keriting itu sendiri secara proporsional.

7. Ke slot atas dan bawah, masukkan ekspresi rumus yang sesuai.

8. Tutup dialog pembuatan formula dengan mengklik mouse.

Contoh. Penulisan rumus matriks.

Perhatikan contoh penulisan rumus determinan orde-3:

.

1. Tempatkan kursor di lokasi rumus.

2. Buka jendela editor formula.

3. Di celah bingkai untuk memasukkan rumus dari keyboard, masukkan " \u003d".

4. Buka set Template matriksdan pilih template:

5. Kotak dialog akan terbuka Matriks... Mari kita atur jumlah baris dan jumlah kolom dari matriks.

6. Dengan mengklik ke kiri dan kanan gambar matriks di jendela, atur garis vertikal di sepanjang tepi matriks.

7. Dalam kelompok sakelar Perataan kolompilih sakelar Pusat.

8. Di grup tombol radio Row alignment, pilih radio button Di jalur utama... Klik OK.

Ini akan memasukkan matriks kosong dengan tiga baris dan tiga kolom dan garis vertikal di sisinya.

9. Di slot pertama dari baris pertama, masukkan simbol " dan».

10. Dalam kumpulan template Buat sub dan superskrip, pilih template yang menentukan pembuatan indeks kanan bawah.

11. Mari masukkan "11" ke dalamnya.

12. Isi sisa slot dengan cara yang sama.

13. Dengan mengklik di luar kotak rumus, tutup dialog pembuatan rumus.

Ubah ukuran dan pindahkan rumus

Mengubah ukuran dan memindahkan rumus dilakukan tepat di jendela utama dokumen Word. Sebelum melakukan tindakan ini, rumus harus dipilih dengan satu klik mouse.

Contoh. Mengubah ukuran formula.

1. Pilih rumus dengan mengklik mouse.

2. Tempatkan penunjuk mouse pada salah satu dari delapan pegangan kotak pemilihan dan seret sampai Anda mendapatkan ukuran yang diinginkan.

Jika Anda mengubah ukuran rumus secara tidak proporsional, posisi relatif elemen dapat dilanggar.

Untuk mengubah skala rumus, pilih rumus dan pilih Sunting | Objek | Formula | Buka... Kemudian pilih skala yang sesuai (25% hingga 400%) dari menu Melihat.

Contoh. Pindahkan rumusnya.

1. Pilih rumus dengan mengklik mouse.

2. Gerakkan pointer mouse ke atas rumus sehingga berbentuk panah mengarah ke kiri.

3. Tekan tombol kiri mouse dan seret rumus ke lokasi yang diinginkan dalam dokumen.

4. Untuk mengubah posisi horizontal rumus, atur perintah Gugus kalimat…tidak bisa Formatdan mengatur nilai yang diperlukan untuk parameter paragraf dengan rumus.

Ini adalah satu bab dari buku itu: Michael Girwin. Ctrl + Shift + Enter. Menguasai rumus array di Excel.

Posting ini untuk mereka yang benar-benar tertarik dengan rumus array yang kompleks. Jika Anda hanya perlu mengambil daftar nilai unik sekali, jauh lebih mudah menggunakan Filter Tingkat Lanjut atau PivotTable. Keuntungan utama menggunakan rumus adalah pembaruan otomatis saat mengubah / menambahkan data sumber atau kriteria pemilihan. Sebelum membaca, disarankan untuk memoles ide-ide yang terkandung dalam materi sebelumnya:

• (bab 11);
• (bab 13);
• (bab 15);
• (bab 17).

Angka: 19.1. Mengambil Record Unik Menggunakan Option Filter lanjutan

Unduh catatan dalam format atau, contoh dalam format

Mengambil daftar unik dari satu kolom menggunakan opsi Filter lanjutan

Dalam gambar. 19.1 menunjukkan kumpulan data (rentang A1: C9). Tujuan Anda adalah mendapatkan daftar trek balap yang unik. Karena Anda perlu menyimpan data asli, Anda tidak dapat menggunakan opsi ini Hapus duplikat (Tidak bisa DATA –> Bekerja dengan data –> Hapus duplikat). Tapi Anda bisa menggunakan Filter lanjutan... Untuk membuka kotak dialog Filter lanjutan, buka menu DATA –> Sortir dan filter –> Selain itu, atau tekan dan tahan Alt lalu tekan S, L secara berurutan (untuk Excel 2007 atau yang lebih baru).

Di kotak dialog terbuka Filter lanjutan (gbr.19.1) atur opsi salin hasilnya ke lokasi lain, centang kotak centang Rekaman unik saja, tentukan wilayah dari mana nilai unik akan diambil ($ B $ 1: $ B $ 9) dan sel pertama tempat data yang diambil akan ditempatkan ($ E $ 1). Dalam gambar. 19.2 menunjukkan daftar unik yang dihasilkan (kisaran E1: E6). Jika Anda tidak menyertakan nama bidang di Rentang Asli kotak dialog Filter lanjutan (alih-alih menggunakan $ B $ 2: $ B $ 9 pada Gambar 19.1), Excel akan memperlakukan baris pertama dari range sebagai nama field, dan Anda berisiko mendapatkan duplikat. Dalam gambar. 19.3 menunjukkan salah satu dari banyak kemungkinan penggunaan daftar unik.

Ambil daftar unik berdasarkan kriteria dengan opsi Filter lanjutan

Dalam contoh terakhir, Anda mengambil daftar unik dari satu kolom. Filter tingkat lanjut juga bisa mengambil sekumpulan rekaman unik (yaitu, seluruh baris dari tabel asli) menggunakan kriteria. Dalam gambar. 19.4 dan 19.5 menunjukkan situasi di mana Anda ingin mengekstrak catatan unik dari rentang A1: D10, dengan nama perusahaan ABC. Nanti di bab ini, Anda akan melihat bagaimana melakukan pekerjaan ini dengan menggunakan rumus. Namun, jika Anda tidak membutuhkan proses otomatis, Anda dapat menggunakan Filter lanjutan, yang tentunya lebih sederhana dari rumusnya.

Angka: 19.4. Anda memerlukan catatan unik untuk perusahaan ABC; untuk memperbesar gambar, klik kanan padanya dan pilih Buka gambar di tab baru

Angka: 19.5. Menggunakan Filter lanjutan mengambil rekaman unik berdasarkan kriteria jauh lebih mudah daripada metode rumus. Namun, rekaman yang diambil tidak akan diperbarui secara otomatis jika kriteria atau data sumber berubah.

Mengambil Daftar Unik dari Satu Kolom Menggunakan Tabel Pivot

Jika Anda sudah menggunakan tabel pivot, maka Anda tahu bahwa setiap kali Anda meletakkan bidang apa pun di area tersebut String atau Kolom (Gambar 19.6), Anda secara otomatis akan mendapatkan daftar unik. Dalam gambar. Gambar 19.6 menunjukkan bagaimana Anda dapat dengan cepat membuat daftar trek balap yang unik, dan kemudian menghitung jumlah kunjungan ke masing-masing jalur tersebut. Meskipun tabel pivot berguna untuk mengambil daftar unik dari satu kolom, ini tidak mungkin berguna untuk mengambil catatan unik berdasarkan kriteria.

Angka: 19.6. Kamu bisa memakai tabel ringkasanketika Anda membutuhkan daftar unik dan perhitungan selanjutnya berdasarkan itu

Ekstrak daftar unik dari satu kolom menggunakan rumus dan kolom pembantu

Menggunakan kolom pembantu membuat pengambilan data unik lebih mudah daripada menggunakan rumus array (Gambar 19.7). Contoh ini menggunakan teknik yang Anda pelajari (menggunakan fungsi COUNTIF) dan (menggunakan kolom pembantu). Jika Anda sekarang mengubah data asli dalam rentang B2: B9, rumus akan secara otomatis mencerminkan perubahan ini di area D15: D21.

Rumus array: Ekstrak daftar unik dari satu kolom menggunakan fungsi SMALL

Karena rumus array yang digunakan dalam bagian ini cukup kompleks untuk dipahami, pembuatannya dipecah menjadi beberapa tahap: yang pertama adalah fragmen yang menghitung nilai unik (Bab 17); yang kedua adalah ekstraksi data berbasis kriteria (Bab 15). Dalam gambar. Gambar 19.8 memperlihatkan rumus untuk menghitung nilai unik (karena ini adalah rumus array, Anda dapat memasukkannya dengan menekan Ctrl + Shift + Enter). Perhatikan aspek-aspek berikut dari rumus ini:

1. Fungsi FREQUENCY mengembalikan larik angka (Gbr. 19.9): untuk kemunculan pertama trek balap, jumlah kemunculannya dalam data asli dikembalikan; untuk setiap kemunculan trek balap berikutnya, nol dikembalikan (lihat). Misalnya, Sumner muncul di posisi pertama dan kelima dalam larik. Di posisi pertama, fungsi FREQUENCY mengembalikan 2 - jumlah total musim panas dalam rentang B2: B9, di posisi kelima - 0.
2. Fungsi FREQUENCY terletak di argumen log_expression fungsi IF, sehingga fungsi IF mengembalikan TRUE untuk nilai bukan nol, dan FALSE untuk nilai bukan nol.
3. Argumen nilai_jika_ benar dari fungsi IF berisi 1, sehingga fungsi SUM menghitung jumlah dari fungsi tersebut.

Angka: 19.8. Fungsi FREQUENCY terletak di argumen log_expression fungsi IF

Angka: 19.9. (1) fungsi FREQUENCY mengembalikan larik angka; (2) fungsi IF mengembalikan 1 untuk angka bukan nol dan FALSE untuk nol

Sekarang mari buat rumus untuk mengambil daftar unik. Dalam gambar. 19.10 menunjukkan larik posisi relatif yang ditempatkan dalam argumen himpunan fungsi KECIL.

Dalam contoh sebelumnya (Gbr. 19.9) dalam argumen nilai_jika_ benar fungsi IF memiliki satu alokasi, sehingga fungsi IF mengembalikan satu dan FALSE. Di sini (Gbr. 19.10) argumennya nilai_jika_ benar berisi: LINE ($ B $ 2: $ B $ 9) -LINE ($ B $ 2) +1. Oleh karena itu, fungsi IF (di dalam fungsi SMALL) mengembalikan nomor posisi relatif dalam rentang dengan trek balap unik, atau FALSE untuk pengambilan (Gambar 19.11).

Angka: 19.11. Fungsi IF mengembalikan nomor posisi relatif dalam rentang dengan trek balap unik, atau FALSE untuk pengambilan

Dalam gambar. 19,12 menunjukkan hasil rumus. Dalam gambar. 19.13 menunjukkan bahwa segera setelah data awal berubah, rumus segera mencerminkan perubahan ini. Tetapi bagaimana jika Anda menambahkan entri baru? Selanjutnya, Anda akan melihat cara membuat rumus rentang dinamis.

Angka: 19.13. Jika data asli berubah, rumus segera diperbarui. Filter dan Filter Tingkat Lanjut tidak dapat memperbarui secara otomatis tanpa menulis kode VBA

Rumus array: ekstrak daftar unik dari satu kolom menggunakan rentang dinamis

Mari tambahkan contoh terakhir dengan apa yang Anda pelajari tentang rumus yang menggunakan nama tertentu berdasarkan dynamic range (). Dalam gambar. 19.14 adalah rumus untuk menentukan nama Jalur... Rumus ini mengasumsikan bahwa Anda tidak pernah memasukkan catatan setelah baris 51.

Angka: 19.14. Definisi nama Jalur berdasarkan rumus

Setelah Anda mengidentifikasi namanya, Anda dapat menggunakannya dalam rumus apa pun. Dalam gambar. Gambar 19.15 menunjukkan cara menggunakan nama untuk menghitung jumlah nilai unik (bandingkan dengan Gambar 19.8). Dan dalam gambar. 19.16 menunjukkan rumus yang mengekstrak nilai unik itu sendiri dari daftar trek balap. Perhatikan bahwa alih-alih cuplikan jarak<>"" (Seperti pada Gambar 19.8 dan 19.10), fungsi ETEXT digunakan (teks apa pun akan mengembalikan nilai TRUE). Saat menggunakan ETEXT, jika Anda memasukkan angka (seperti di sel B11), atau non-teks lainnya, rumus akan mengabaikan nilai tersebut. Dalam gambar. 19.17 menunjukkan bahwa rumus akan secara otomatis mengekstrak nama jejak baru, mengabaikan angka.

Angka: 19.16. Mengambil Nama Perataan Unik Berdasarkan Rentang Dinamis

Buat Formula Nilai Unik untuk Daftar Dropdown

Berdasarkan contoh yang baru saja dilihat, mari tentukan nama tengah - Daftar lagu, juga berdasarkan rentang dinamis, tetapi sekarang mengacu pada daftar jejak unik (rentang E5: E14, Gambar 19.18). Karena rentang E5: E14 hanya berisi teks dan nilai kosong (string uji dengan panjang nol - ""), dalam argumen nilai lookup fungsi MATCH dapat menggunakan wildcard *? (yang berarti setidaknya satu karakter). Dan dalam argumennya match_type fungsi MATCH harus menggunakan nilai –1 untuk menemukan elemen teks terakhir dalam kolom yang berisi setidaknya satu karakter. Seperti yang ditunjukkan pada gambar. 19.18, maka Anda dapat menggunakan nama tertentu di lapangan Sumber jendela Memvalidasi Nilai Input (untuk detail tentang membuat daftar drop-down, lihat). Daftar tarik-turun dapat meluas dan menyusut saat data baru ditambahkan atau dihapus di kolom B.

Dimana wildcard harus diperlakukan seperti karakter biasa

Seperti yang Anda pelajari, terkadang karakter pengganti harus diperlakukan sebagai simbol. Dalam gambar. 19.18 memperlihatkan bagaimana Anda dapat mengubah rumus untuk kasus seperti itu. Anda menambahkan tilde sebelum rentang argumen nilai lookup Fungsi SEARCH dan menambahkan string kosong ke belakang rentang dalam argumen lookup_array.

Menggunakan kolom pembantu atau rumus array untuk mengambil rekaman unik berdasarkan kriteria

Di awal posting ini, ditunjukkan bahwa untuk mengambil catatan unik berdasarkan kriteria, itu berfungsi dengan baik Filter lanjutan... Namun, jika Anda membutuhkan update instan, Anda dapat menggunakan kolom helper (Gambar 19.20) atau rumus array (Gambar 19.21).

Rumus dinamis untuk mengekstrak nama pelanggan dan penjualan

Rumusnya ditunjukkan pada Gambar. 19.22. Misalnya, jika Anda menambahkan entri baru TTTruk di baris 17 , rumus SUMIF di sel F15 secara otomatis akan menambahkan nilai baru. Jika Anda menambahkan pelanggan baru di kolom B, itu akan langsung muncul di kolom E, dan rumus SUMIF di kolom F akan memperlihatkan total baru.

Angka: 19.22. Menggunakan nama tertentu dan dua rumus array untuk mengambil pelanggan dan penjualan unik

Perhatikan bahwa fungsi SUMIF dalam argumen jumlah_rentangberisi satu sel - $ C $ 10. Inilah yang dikatakan bantuan rumus SUMIF tentang topik ini: argumen jumlah_rentang mungkin tidak sama ukurannya dengan argumen jarak... Sel kiri atas argumen digunakan sebagai sel awal saat menentukan sel aktual yang akan ditambahkan jumlah_rentang, lalu sel dari bagian rentang yang sesuai ukurannya dengan argumen dijumlahkan jarak... Rumus yang dimasukkan dalam sel E15 dan F15 disalin di sepanjang kolom.

Mengurutkan nilai numerik

Rumus untuk mengurutkan angka cukup sederhana, tetapi untuk mengurutkan data campuran sangat rumit. Oleh karena itu, jika Anda tidak memerlukan pembaruan instan, maka lebih baik melakukannya tanpa rumus dengan menggunakan opsi Penyortiran... Dalam gambar. 19.23 menunjukkan dua rumus pengurutan.

Dalam gambar. 19.24 menunjukkan bagaimana Anda dapat menggunakan kolom pembantu untuk mengurutkan angka. Karena fungsi RANK tidak mengurutkan angka yang sama (memberi mereka peringkat yang sama), fungsi COUNTIF telah ditambahkan untuk membedakannya. Perhatikan bahwa fungsi COUNTIF memiliki rentang diperpanjang yang memulai satu baris. Ini diperlukan agar penampilan pertama nomor apa pun tidak berkontribusi. Kemunculan kedua nomor tersebut akan meningkatkan peringkat satu per satu. Penomoran berurutan ini menetapkan urutan fungsi INDEX dan SEARCH mengambil rekaman dalam rentang A8: B12.

Jika Anda mampu membuat kolom tambahan di area ekstraksi data (kisaran A10: A14 pada Gambar 19.25), akan lebih mudah untuk menerapkan pengurutan angka yang dijelaskan di atas berdasarkan fungsi SMALL, dan berdasarkan fungsi tersebut, ekstrak nama menggunakan fungsi array.

Angka: 19.25. Jika Anda tidak dapat menggunakan kolom pembantu, terapkan pengurutan KECIL (di sel A11) dan rumus array (di sel B11)

Seringkali dalam bisnis dan olahraga diperlukan untuk mengekstrak N nilai terbaik dan nama yang diasosiasikan dengan nilai tersebut. Mulailah solusi dengan rumus COUNTIF (sel A11 pada Gambar 19.26), yang akan menentukan jumlah record untuk ditampilkan. Perhatikan bahwa argumennya kriteria dalam fungsi COUNTIF di sel A11 - lebih atau sama nilai di sel D8. Ini memungkinkan semua nilai batas untuk ditampilkan (dalam contoh kami, meskipun kami ingin menampilkan 3 Teratas, ada empat nilai yang sesuai).

Angka: 19.26. Mengambil tiga skor teratas dan nama yang sesuai. Ketika N berubah di sel D8, area A15: B21 akan diperbarui

Mengurutkan nilai teks

Jika Anda dapat menggunakan kolom bantu, tugasnya tidak terlalu sulit (Gbr 19.27). Operator perbandingan memproses karakter teks berdasarkan kode ASCII numerik yang ditetapkan ke karakter. Di sel C3, fungsi COUNTIF pertama mengembalikan nol, dan yang kedua menambahkan satu. Dalam C4: 2 + 1, C5: 0 + 2, C6: 3 + 1.

Menyortir data campuran

Rumus yang memungkinkan Anda mengekstrak nilai unik dari data campuran dan kemudian mengurutkannya sangat besar (Gambar 19.28). Dalam pembuatannya, ide-ide digunakan yang ditemukan sebelumnya dalam buku ini. Mari mulai menjelajahi rumus dengan melihat cara kerja fungsi pengurutan Excel standar.

Excel mengurutkan hasil dalam urutan berikut: angka terlebih dahulu, lalu teks (termasuk string panjang nol), FALSE, TRUE, nilai kesalahan dalam urutan kemunculannya, sel kosong. Semua penyortiran dilakukan sesuai dengan kode ASCII. Ada 255 kode ASCII, yang masing-masing sesuai dengan angka dari 1 sampai 255:

Misalnya, 5 adalah ASCII 53 dan S adalah ASCII 83. Jika Anda mengurutkan dua nilai, 5 dan S, dari terendah ke tertinggi, maka 5 lebih tinggi dari S karena 53 kurang dari 83.

Kumpulan data dalam rentang A2: A5 (Gbr. 29) diubah ke rentang E2: E5 sesuai dengan aturan penyortiran. Untuk lebih memahami prinsip pengurutan, pertimbangkan nilai dalam rentang C2: C5. Misalnya, jika Anda mengajukan pertanyaan "Berapa peringkat di atas saya?" untuk ID di sel A2 (54678) jawabannya nol, karena di daftar yang diurutkan, ID 54678 akan menjadi yang paling atas. SD-987-56 akan memiliki tiga ID di atasnya. Anda membutuhkan rumus untuk mendapatkan nilai dalam rentang C2: C5.

Pertama, pilih rentang E1: H1 dan di bilah rumus, ketik \u003d TRANSPOSE (A2: A5), masukkan rumus dengan menekan Ctrl + Shift + Enter (Gbr. 19.30). Selanjutnya, pilih rentang E2: H5 di bilah rumus, ketik \u003d A2: A5\u003e E1: H1 dan masukkan rumus dengan menekan Ctrl + Shift + Enter (Gbr. 19.31). Dalam gambar. Gambar 19.32 menunjukkan hasil berupa array persegi panjang dengan nilai TRUE dan FALSE yang sesuai dengan masing-masing sel dalam array yang dihasilkan, sebagai jawaban untuk pertanyaan "Apakah judul baris lebih besar dari judul kolom?"

Angka: 19.30. Pilih rentang E1: H1 dan masukkan rumus array

Angka: 19.31. Dalam rentang E2: H5, masukkan rumus array \u003d A2: A5\u003e E1: H1

Angka: 19.32. Setiap sel dari rentang E2: H5 berisi jawaban atas pertanyaan "Apakah tajuk baris lebih besar dari tajuk kolom?"

Misalnya, sel E3 menanyakan pertanyaan: SD-987-56\u003e 54678. Karena 54678 lebih kecil dari SD-987-56, jawabannya BENAR. Perhatikan bahwa rentang E3: H3 mencakup tiga nilai TRUE dan satu FALSE. Melihat kembali ara. 19.29, Anda dapat melihat bahwa angka tiga ada di sel C3.

Seperti yang diperlihatkan dalam Gambar 19,33 dan 19,34, Anda dapat mengonversi nilai TRUE dan FALSE menjadi satu dan nol dengan menambahkan negatif ganda ke rumus array. Karena larik awal (E2: H5) memiliki dimensi 4 × 4, dan Anda ingin hasilnya berupa larik 4 × 1, gunakan fungsi MULTIFUNCTION (lihat Gambar 19.35 dan). Fungsi MULTIPLE adalah fungsi array, jadi masukkan dengan menekan Ctrl + Shift + Enter (Gambar 19.36). Sekarang, daripada menggunakan rentang E2: H5, tambahkan elemen yang sesuai di dalam rumus (Gambar 19.37).

Angka: 19.36. Dengan memilih rentang C2: C5 dan memasukkan fungsi array MULTIPLE Anda mendapatkan kolom angka yang mengatakan berapa banyak ID dalam daftar yang diurutkan di atas yang dipilih

Angka: 19.37. Alih-alih menggunakan rentang bantu E2: H5, elemen terkait ditambahkan di dalam rumus

Dalam gambar. 19.38 menunjukkan bagaimana Anda bisa mengganti array konstanta dengan STRING ($ A $ 2: $ A $ 5) ^ 0.

Angka: 19.39. Untuk menangani sel kosong potensial, semua kemunculan A2: A5 harus dilengkapi dengan IF (A2: A5<>"", A2: A5); fungsi STRING tidak memerlukan penambahan seperti itu, karena fungsi tersebut bekerja dengan alamat sel, bukan isinya

Karena rumus terakhir akan digunakan di tempat lain, Anda perlu membuat semua rentang menjadi absolut (Gambar 19.40). Dalam gambar. 19.41 menunjukkan nilai yang dihasilkan.

Angka: 19.40. Rentang A2: A5 berubah menjadi mutlak

Karena elemen ini akan digunakan dua kali di masa mendatang, Anda dapat menyimpannya dengan nama tertentu. Seperti yang ditunjukkan pada kotak dialog (Gbr. 19.42), rumusnya dinamai SZB - Berapa Banyak Nilai Lagi.

1. Argumen himpunan fungsi INDEX mengacu pada rentang asli A2: A5.
2. Fungsi MATCH pertama akan memberi tahu fungsi INDEX posisi relatif elemen dalam larik A2: A5.
3. Sedangkan argumentasi nilai lookup fungsi SEARCH dikosongkan.
4. Nama yang ditentukan (SZB) dalam argumen lookup_array akan memungkinkan Anda mengakses elemen yang memiliki nilai 0, lalu 2, dan terakhir 3.
5. Nol dalam argumentasi match_type menentukan pencocokan tepat, yang akan menghilangkan referensi ke duplikat.

Angka: 19.43. Anda memulai rumus untuk mengekstrak dan mengurutkan data di sel A11. Argumen nilai lookup fungsi SEARCH dikosongkan untuk saat ini

Sebelum Anda membuat argumen nilai lookup fungsi PENCARIAN, ingat apa yang sebenarnya Anda butuhkan. Ada tiga ID unik yang perlu diurutkan, jadi Anda memerlukan tiga angka dalam argumen nilai lookup saat rumus disalin. Angka-angka ini akan memungkinkan Anda menemukan posisi relatif dalam larik A2: A5, yang perlu Anda berikan ke fungsi INDEX:

1. Di sel A11, fungsi MATCH akan mengembalikan 0, yang sesuai dengan posisi relatif 1 dalam nama MSB yang ditentukan.
2. Saat rumus disalin ke sel A12, fungsi MATCH harus mengembalikan angka 2, dan posisi relatif \u003d 4 di dalam MSB.
3. Di sel A13, fungsi MATCH harus mengembalikan 3, dan posisi relatif \u003d 2 dalam MSB.

Gambaran tersebut muncul ketika Anda memikirkan tentang apa argumennya nilai lookup saat menyalin rumus ke bawah, kueri harus cocok: "Berikan nilai minimum dalam nama tertentu dari SZB yang belum digunakan." Seperti yang ditunjukkan pada gambar. 19.44 elemen rumus MIN (IF (UND (SEARCH ($ A $ 2: $ A $ 5; A $ 10: A10; 0)); SZB)) mengembalikan nilai minimum saat Anda menyalin rumus ke bawah, menjawab kueri dengan tepat. Alasan ini berhasil adalah karena dalam fragmen UNM (SEARCH ($ A $ 2: $ A $ 5; A $ 10: A10; 0)) dua daftar dibandingkan (lihat). Perhatikan rentang perluasan A $ 10: A10 dalam argumen lookup_array... Di sel A11, kombinasi UND dan MATCH membantu mengekstrak semua nomor unik dari MSB dan menyediakannya ke fungsi MIN. Saat Anda menyalin rumus ke sel A12, ID yang diekstrak di sel A11 kembali hadir dalam rentang yang diperluas dan akan ditemukan lagi di kisaran $ A $ 2: $ A $ 5. Namun, UND mengembalikan FALSE dan tidak ada nilai yang diambil dari MWB. Untuk melihatnya, masukkan rumus array pada Gambar 19.44 dengan menekan Ctrl + Shift + Enter dan salin ke bawah.

Angka: 19.44. Elemen rumus dalam argumen nilai lookup function MATCH cocok dengan query: "Berikan nilai minimum dalam nama SZB tertentu yang belum digunakan"

Dalam gambar. 19.45 menunjukkan bahwa dalil lookup_array fungsi kedua CARI kisaran A $ 10: A10 telah diperluas menjadi A $ 10: A11. Untuk memahami cara kerja rumus ini, pilih fragmennya secara berurutan dan klik F9 (Gbr. 19.46-19.49).

Angka: 19.45. Rentang yang dapat diperluas A $ 10: A11 sekarang (dalam sel A12) menyertakan ID pertama (54678)

Angka: 19.46. Kombinasi fungsi UND dan SEARCH kedua menyediakan berbagai boolean; dua nilai FALSE mengecualikan nilai null dari nama MSB tertentu

Angka: 19.47. Angka nol dikecualikan dan hanya angka 3 dan 2 yang tersisa; angka 2 adalah minimum, jadi inilah yang harus diekstraksi sebagai berikut

Angka: 19.48. Fungsi MIN memilih angka 2; sekarang fungsi MATCH dapat menemukan posisi relatif yang benar untuk fungsi INDEX

Angka: 19.49. Fungsi INDEX akan mengambil nilai 2, yang sesuai dengan posisi keempat relatif ID dalam rentang A2: A5

Sekarang, kembali ke sel A11, Anda dapat menambahkan kondisi lain sehingga sel kosong tidak memengaruhi rumus (Gambar 19.50).

Angka: 19.50. Ada dua kondisi di dalam fungsi MIN; pertama: "sel tidak kosong?", kedua: "nilai belum digunakan?"

Dalam gambar. 19,51 adalah rumus terakhir. Kondisi telah ditambahkan sehingga baris dalam rentang A11: A15 tetap kosong setelah nilai unik yang diurutkan diambil. Dalam gambar. 19.52 menunjukkan apa yang terjadi jika sel A3 dikosongkan. Penambahan kami untuk memeriksa sel kosong berfungsi.

Itu tidak mudah. Tapi, jika Anda sudah membaca sejauh ini, saya harap Anda menikmatinya.

PERHITUNGAN PADA TABEL ELEKTRONIK

2.1. D f o r m u l s

Perhitungan nilai sel dilakukan dengan memasukkan rumus. Rumus selalu dimulai dengan tanda sama dengan " = ”.

Rumus memungkinkan Anda melakukan operasi matematika umum pada nilai dari sel di lembar kerja. Misalnya, Anda perlu menambahkan nilai di sel B1 dan B2 dan menampilkan jumlahnya di sel B5. Untuk melakukan ini, letakkan kursor di sel B5 dan masukkan rumus "\u003d B1 + B2".

Input rumus muncul di sel tabel dan bilah rumus. Saat tombol ditekan Memasukkan perhitungan dilakukan dan hasilnya diperoleh di sel aktif.

Operator berikut dapat digunakan dalam rumus:

a r i f m e t i c e -

w a n i -

t e c t a -

& - penggabungan nilai teks.

Saat menghitung rumus dalam tabel, urutan operasi aritmatika diterapkan.

2.2. PEMBUATAN FORMUL DENGAN BANYAK

Anda dapat memasukkan koordinat sel dalam rumus dengan mengarahkan kursor ke sel yang diinginkan. Ada risiko membuat kesalahan saat memasukkan rumus secara manual. Ini dapat dihindari dengan bertindak sebagai berikut:

letakkan kursor di sel tempat Anda ingin memasukkan rumus;

masukkan tanda sama dengan “\u003d”;

letakkan kursor di dalam sel, yang koordinatnya harus berada di awal rumus, dan klik tombol mouse;

masukkan operator (misalnya, tanda "+") atau karakter lain yang melanjutkan rumus;

pindahkan kursor ke sel yang koordinatnya ingin Anda gunakan dalam rumus dan klik tombol mouse;

lakukan tindakan ini sampai rumus berakhir.

2.3. A b s o lute dan tentang s dan t e l n e c e c

Ada tiga jenis utama alamat (tautan): relatif, absolut, dan campuran.

Perbedaan antara referensi relatif dan absolut muncul saat Anda menyalin dan memindahkan rumus dari satu sel ke sel lainnya.

Saat Anda memindahkan atau menyalin, tautan absolut dalam rumus tidak berubah, dan tautan relatif secara otomatis diperbarui berdasarkan posisi baru.

Misalnya, sel A1 berisi konstanta 4, sel B1 hingga B10 berisi nilai dari 0,1 hingga 1 dengan kelipatan 0,1. Untuk mendapatkan hasil di sel D1: D10 sesuai rumus 4b i, di mana i \u003d 1, 2,…, 10, Anda perlu mengetik di sel D1 "\u003d $ A $ 1 * B1" dan salin rumus ke sel D2, D3,…, D10. Dalam hal ini, D2 akan berisi frase "$ A $ 1 * B2", di D3 - "$ A $ 1 * B3", dll., Di mana konten $ A $ 1 tidak berubah, karena alamat (tautan ) mutlak, dan B1 berubah menjadi B2, B3, ..., B10, karena alamatnya relatif.

Untuk menunjukkan rentang sel dalam rumus, gunakan simbol " : ”, Contoh: A2 : A5.

Untuk menunjukkan sekelompok sel yang tidak berdekatan, gunakan simbol " ; ”, Misalnya: A2; B5; E10.

2.4. MENGEDIT FORMUL

Rumus diedit dengan cara yang sama seperti konten sel.

Pertamajalan th... Anda harus memilih sel yang Anda inginkan, klik pada bilah rumus dan edit.

Cara kedua... Klik ganda sel dan edit rumus langsung di dalam sel.

2.5. Dan menggunakan fungsinya

Salah satu fitur EXCEL yang paling berguna adalah berbagai fungsinya yang memungkinkan Anda melakukan berbagai jenis penghitungan. Setiap fungsi memiliki sintaks untuk menulis:

NAMA FUNGSI (argumen 1; argumen 2; ...).

Argumen fungsi dapat berupa angka, teks, nilai boolean, nilai kesalahan, tautan, array. Dalam bilangan desimal, bagian integer dipisahkan dari karakter pecahan “,”, misalnya: –30.003.

Nilai teks harus diapit tanda kutip ganda. Jika teks itu sendiri mengandung tanda kutip ganda, maka teks itu harus digandakan.

Nilai Boolean TRUE dan FALSE. Argumen Boolean juga dapat menjadi ekspresi perbandingan yang TRUE atau FALSE-nya dapat dievaluasi, misalnya: B10\u003e 20.

Misalnya, fungsi AVERAGE menghitung mean aritmatika dari serangkaian nilai. Ekspresi "\u003d AVERAGE (6; 12; 15; 16)" akan memberikan hasil 12.75. Jika nilai 6, 12, 15, 16 disimpan di sel B10 - B15, maka rumusnya dapat ditulis seperti ini: “\u003d AVERAGE (B10: B15)”.

Fungsi SUM digunakan untuk menentukan penjumlahan nilai, misalnya: “\u003d SUM (B10: B15)”. Angka 6, 12, 15, 16 akan dijumlahkan.

Lebih mudah untuk memperkenalkan fungsi ke dalam rumus menggunakan Wizard Fungsi ... Wizard Fungsi memungkinkan Anda memasukkan fungsi ke dalam rumus yang Anda buat. Untuk melakukan ini, lakukan hal berikut:

letakkan kursor di sel tempat Anda ingin memasukkan fungsi;

di toolbar standar, klik tombol wizard fungsi ¦ x atau jalankan perintah Sisipkan + Fungsi ;

di kotak dialog yang muncul di daftar Kategori pilih kategori fungsi yang diinginkan. Setelah itu di daftar Fungsi fungsi kategori yang dipilih muncul;

dalam daftar Fungsi pilih fungsi dan klik tombolnya baik ;

kotak dialog akan muncul tergantung pada jenis fungsi yang dipilih;

masukkan nilai atau rentang sel yang diinginkan untuk argumen fungsi;

klik tombol di kotak dialog baik .

2.6. Sistem matic otomatis dan e

Metode paling sederhana untuk menjumlahkan tabel adalah penjumlahan otomatis. Untuk melakukan ini, letakkan kursor di sel di bawah kolom atau di sebelah kanan baris, yang nilainya perlu diringkas dan klik tombol pada toolbar standar Penjumlahan otomatis (ini menunjukkan simbol " å ”). Lalu tekan tombolnya Memasukkan .

Saat menjumlahkan elemen matriks dengan kolom dan baris, akan lebih mudah untuk memilih sel matriks dengan baris dan kolom tambahan, lalu tekan tombol " å ". Jumlah semua baris dan kolom dari matriks akan diperoleh secara otomatis.

2.7. F o rm uly untuk bekerja dengan m a s i in a m i

Rumus array (rumus tabel) memungkinkan Anda melakukan banyak penghitungan dengan menulis satu rumus. Misalnya, Anda perlu mengalikan nilai di kolom A2: A6 dengan B2: B6 yang sesuai. Catat hasilnya di C2: C6 tanpa menyalin rumus.

Anda perlu melakukan hal berikut:

pilih sel dari hasil C2: C6;

masukkan tanda “\u003d”;

pilih sel A2: A6;

masukkan tanda "*";

sorot B2: B6;

tekan tombol keyboard Shift + Ctrl + Enter .

Rumus "(\u003d A2: A6 * B2: B6)" akan ditampilkan di bilah rumus, dan hasilnya akan diperoleh di semua sel C2: C6.

Z A D A N I E 2

1) Jalankan program EXCEL.

2) Di direktori Anda sendiri buat file bernama "lab_2.хls".

3) Beri nama lembar pertama dari buku kerja “Lab. No. 2 (memasukkan rumus) ”.

4) Di sel A1 tulis nama belakang Anda.

5) Buat rapor sesuai tabel contoh. 2. Jumlahkan item di setiap kolom dan setiap baris. Hitung skor rata-rata menggunakan rumus.

Meja 2

	Matematika	Ekonomi	Ilmu Komputer	Skor rata - rata
Ivanov	5
Petrov	4
Sidorov	3
Yakovlev	4
Skor rata - rata	4

6) Hitung jumlah tanda "5" di setiap mata pelajaran. Cetak daftar siswa dengan IPK lebih dari "4".

7) Hitung y \u003d 2 x 2 + 3 x + 5, di mana argumen x berubah dari 0,1 menjadi 1 dalam kelipatan 0,1. Gunakan referensi absolut untuk konstanta 2, 3, 5, dan referensi relatif untuk x.

8) Untuk matriks 4´4, hitung determinannya, matriks inversnya, kuadratkan dan cari matriks yang ditransposisikan menggunakan rumus tabel.

9) Simpan konten buku kerja.