15 formula ajaib dalam Excel

Excel adalah makcik yang tegas. Di satu pihak, ia adalah alat yang sangat diperlukan untuk membuat laporan, senarai dan analisis. Sebaliknya, anda hanya dapat mengekstrak maklumat yang anda mahukan dari hamparan jika anda menguasai bahasa Excel yang biasa. Rumus Excel seperti itu melampirkan semua jenis hubungan ke sel untuk mengembalikan maklumat yang disasarkan. Berikut adalah 15 ciri yang dapat menjimatkan masa anda.

Manual atau penyihir formula?

Kami menganggap bahawa sementara ini anda telah menguasai formula asas untuk menerapkan operasi utama. Tanpa menjumpai fokus khusus untuk pakar, kami menunjukkan bagaimana formula berguna disatukan. Anda boleh memasukkannya secara manual, tetapi anda juga boleh menggunakan butang fx di bar formula: formula wizard. Dia akan menolong anda untuk membangun formula langkah demi langkah.

01 Masa semasa

Adakah anda seorang yang selalu lupa untuk membuat tarikh pekerjaan mereka dengan betul? Formula HARI INI secara automatik mengisi hari, bulan dan tahun, sementara fungsi SEKARANG malah menambah masa ke minit. Anda kemudian taip = HARI INI () atau = SEKARANG () . Fungsi-fungsi ini juga berguna dalam lembaran kerja di mana anda ingin mengira nilai berdasarkan hari dan waktu semasa. Dengan klik kanan dan pilihan untuk sifat Cell, anda kemudian dapat menyesuaikan paparan tarikh dan waktu. Untuk mengemas kini maklumat masa ini dalam lembaran kerja aktif, tekan Shift + F9; gunakan F9 untuk mengemas kini keseluruhan buku kerja.

02 Hitung sel yang diisi

Sekiranya anda mempunyai sekumpulan sel dengan teks dan nombor dan anda ingin mengetahui berapa banyak nombor dalam pilihan, gunakan fungsi COUNT . Struktur formula kemudian kelihatan seperti ini: = COUNT (kawasan carian) . Kawasan di mana Excel harus mencari muncul di antara tanda kurung. Ini boleh menjadi sel di bawah atau di sebelah satu sama lain, tetapi juga boleh menjadi sel sel yang berbentuk segi empat tepat. Sekiranya terdapat kata-kata dalam pemilihan, mereka yang mempunyai fungsi COUNT tidak akan dikira. Sekiranya anda hanya mahu mengira semua sel di mana sesuatu ditulis, gunakan fungsi = COUNTA (tanpa titik).

03 Berapa kerap?

Untuk menghitung data tertentu dengan cara yang disasarkan, gunakan fungsi COUNTIF . Katakan anda telah membuat grid di mana empat orang muncul, maka anda dapat melihat berapa kerap nama Herman berlaku dengan = NANTAL.IF (kawasan carian; "Herman") . Masukkan julat carian antara tanda kurung dan sertakan kriteria carian dalam tanda petik.

04 Penambahan selektif

Fungsi SUM untuk menjumlahkan sel banyak digunakan. Varian yang lebih bijak ialah SOM.IF () . Pertama tentukan kawasan di mana Excel harus mencari antara kurungan. Julat carian mestilah rangkaian sel bersebelahan. Selepas titik koma anda menentukan apa yang harus ditambah. Ini boleh menjadi angka atau rujukan. Sekiranya ia adalah persamaan, maka anda mesti memasukkannya dalam petikan ganda. Contohnya, = SUMIF (B20: B40, ”> 50”) menjadikan jumlah semua sel dalam julat ini lebih besar daripada 50.

05 Penambahan dalam keadaan

Anda boleh memperluaskan syarat penambahan dengan menggunakan maklumat di lajur lain. Contoh menjadikannya jelas. Katakan anda mempunyai angka yang berkaitan dengan tiga bandar: Amsterdam, Rotterdam dan Eindhoven. Maka anda hanya boleh menambahkan bilangan Amsterdam dengan = SUMIF (julat; "Amsterdam"; julat penambahan) . Dalam kes ini, formula menjadi = SUMIF (C48: C54; "Amsterdam"; B48: B54) . Dalam bahasa biasa: Apabila kata Amsterdam berada dalam julat C48 hingga C54, Excel mesti menjumlahkan nilai sel yang sesuai di sebelahnya dalam julat B48 hingga B54.

06 Gabungan

Dengan fungsi CONCATENATE anda menggabungkan data dari sel yang berbeza. Sebagai contoh, sel nama pertama dan terakhir dengan sesuatu seperti = CONCATENATE TEXT (E34, ”“; F34) . Tanda petik berganda dengan ruang memastikan bahawa terdapat ruang antara nama depan dan nama belakang. Dengan cara yang sama adalah mungkin untuk menggabungkan teks dengan mata wang. Sebagai contoh, untuk menambahkan mata wang euro, anda akan menaipnya sebagai fungsi seperti = CONCATENATE TEXT (A1, ”“; B1; ”“ EURO (C1)) . Anda boleh membacanya sebagai "menggabungkan sel A1, B1 dan C1 dengan jarak di antara mereka dan meletakkan tanda euro sebelum elemen penggabungan ketiga".

07 Balut

Excel mempunyai beberapa pilihan untuk pembundaran. Pembundaran lalai kelihatan seperti = ROUND (bilangan, bilangan tempat perpuluhan) . Jadi, formula = ROUND (12.5624, 1) mengembalikan 12.6 . Bagaimanapun, anda meminta untuk membundarkan satu nombor selepas titik perpuluhan. Excel akan juga bulat kepada bilangan tempat perpuluhan yang anda tentukan dengan ROUND.TO.ABOVEN dan ROUND.BOTH fungsi . = ROUNDUP (12.5624; 2) oleh itu memberikan hasil 12,57 dan = ROUNDDOWN (12.5624; 2), menghasilkan 12.56 . Fungsi INTEGER sebenarnya juga merupakan fungsi pembundaran, tetapi dengan Excel itu membundarkan nombor bulat terdekat.

08 Huruf besar - huruf kecil

Untuk memastikan bahawa semuanya muncul dengan huruf besar dalam lajur, gunakan fungsi UPPERCASE . Rumus KECIL.KEBALIKAN sebaliknya. Dan jika anda mahu setiap perkataan dimulakan dengan huruf besar diikuti dengan huruf kecil, anda menggunakan fungsi SURAT INISI . Rumus = SURAT KECIL (B4) menunjukkan kandungan sel B4, tetapi dalam huruf kecil.

09 Dalam keadaan

Apabila pengiraan bergantung pada keadaan tertentu anda menggunakan fungsi IF . Prinsip fungsi ini adalah: = JIKA (syarat, pengiraan jika keadaan dipenuhi, kes lain) . Untuk merumuskan keadaan, gunakan tanda: = sama dengan,tidak sama dengan, > lebih daripada, < kurang dari, > = lebih daripada atau sama dengan, < = kurang daripada atau sama dengan. Anggap bahawa dalam sebuah organisasi setiap orang mendapat bonus yang telah terjual dengan harga 25,000 euro atau lebih. Sekiranya anda menerima bonus, perkataan "Hooray" akan muncul secara automatik di sebelah nama mereka, jika tidak, perkataan "Sayangnya" akan muncul. Formula yang anda perlukan ialah = JIKA (B2> = 2500; "Hore"; "Sayangnya") .

10 Terbesar - Terkecil

Untuk mendapatkan nilai tertinggi dan terendah dengan cepat, terdapat fungsi MAX dan MIN . Dengan = MAX (B2: B37) anda mendapat nilai tertinggi sel ini, dan dengan = MIN (B2: B37) anda mendapat nilai terendah dalam julat. Fungsi TERBESAR dan KECIL lebih halus: anda juga boleh meminta yang ketiga terbesar atau terkecil kedua, misalnya. Yang terbesar boleh didapati dengan = BESAR (B2: B37; 1); nombor 1 menunjukkan yang paling besar. Dengan = BESAR (B2: B37,2) anda mendapat yang kedua terbesar dan seterusnya. Dengan cara itu anda dapat mengumpulkan 3 teratas atau 10 teratas dengan mudah.

11 Pencarian menegak

Katakan anda mempunyai dua lembaran kerja dengan maklumat berbeza mengenai orang yang sama. Dengan VLOOKUP anda mendapatkan maklumat dari lembaran kerja 2 di lembaran kerja 1. Untuk menjadikannya lebih mudah, kami telah memberikan setiap orang nombor pendaftaran yang unik pada kedua-dua tab tersebut. Beri juga nama untuk julat pada tab 2 dari mana anda ingin mendapatkan maklumat. Dalam contoh ini, di lembaran kerja 2, kami memilih lajur A dan B dan di kotak nama di kiri atas, kami menaip nama Senarai Alamat . Dalam sel E2 lembaran kerja 1, kita meletakkan fungsi VLOOKUP . Formatnya sekarang = VLOOKUP (A2, Senarai Alamat, 2, SALAH) . A2 merujuk pada sel dengan nombor pendaftaran di lembaran kerja kedua, Senarai Alamatmenunjukkan julat carian, 2 adalah jumlah lajur di lembaran kerja 2 di mana data yang diminta berada. Argumen terakhir adalah nilai logik di mana anda memasukkan SALAH jika anda mahukan nilai yang dijumpai sesuai.

12 Padamkan ruang

Gunakan fungsi TRIM untuk menghapus ruang yang tidak diperlukan dalam teks. Fungsi ini meninggalkan beberapa ruang di antara kata-kata, tetapi akan membuang ruang sebelum atau selepas kata. = TRIM (rangkaian sel) berguna untuk teks yang diimport dari program lain. Dalam beberapa versi Excel, fungsi ini dipanggil SPACES.CLEAR .

13 Pertukaran

Anda boleh memindahkan isi lajur ke baris atau sebaliknya dengan fungsi TRANSPOSE . Mula-mula pilih sel di mana maklumat itu mesti diletakkan. Pastikan untuk memilih seberapa banyak sel seperti siri asal. Di sini kami menaip tahun di baris 8 dan suku di lajur A. Kemudian taip fungsi = TRANSPOSE dan buka tanda kurung. Kemudian seret ke atas sel yang ingin anda tukar (di sini dari sel B2 hingga E5). Tutup kurungan dan sekarang tekan kombinasi kekunci Ctrl + Shift + Enter. Ini akan membuat formula larik yang terdapat dalam kurungan keriting.

14 Bayaran balik bulanan

Sekiranya anda meminjam untuk pembelian, berapa banyak yang perlu anda bayar setiap bulan? Anggaplah anda meminjam 25,000 euro ( B1 ) dengan faedah 6% ( B2 ) selama 5 tahun ( B3 ). Kami menunjukkan formula di wizard, tetapi anda juga boleh menaip. Dengan Faedah, anda meletakkan B2 / 12 , kerana faedah tersebut merujuk kepada satu tahun dan anda ingin mengetahui berapa banyak yang anda bayar setiap bulan. Untuk ansuran Nombor anda mengalikan B3 dengan 12 , kerana anda harus menukar tahun menjadi bulan. Kotak PV bermaksud Nilai Semasa , iaitu 25,000 euro. Ini memberikan formula = BET (B2 / 12; B3 * 12; B1) atau =BET (6% / 12.5 * 12.25000) .

15 Angka palsu

Semasa bereksperimen dengan formula, berguna untuk memiliki data palsu. Fungsi SELECT ANTARA menghasilkan data rawak antara nilai terendah dan tertinggi yang ditentukan. Fungsi = RAND ANTARA (50, 150) menghasilkan nombor antara 49 dan 151.