15 formula ajaib di Excel

Excel adalah bibi yang ketat. Di satu sisi, ini adalah alat yang sangat diperlukan untuk membuat laporan, daftar, dan analisis. Di sisi lain, Anda hanya dapat mengekstrak informasi yang Anda inginkan dari spreadsheet jika Anda menguasai bahasa Excel yang khas. Rumus Excel tersebut melampirkan semua jenis hubungan ke sel untuk mengembalikan informasi yang ditargetkan. Berikut 15 fitur yang dapat menghemat waktu Anda.

Manual atau panduan rumus?

Kami berasumsi bahwa saat ini Anda telah menguasai rumus dasar untuk menerapkan operasi utama. Tanpa menjadi fokus fokus untuk spesialis, kami menunjukkan bagaimana formula yang berguna disatukan. Anda bisa memasukkannya secara manual, tetapi Anda juga bisa menggunakan tombol fx di bilah rumus: panduan rumus. Dia akan memandu Anda untuk menyusun formula selangkah demi selangkah.

01 Waktu saat ini

Apakah Anda seseorang yang sering lupa mengencani pekerjaannya dengan benar? Rumus HARI INI secara otomatis mengisi hari, bulan, dan tahun, sementara fungsi SEKARANG bahkan menambahkan waktu ke menit. Anda kemudian mengetik = TODAY () atau = NOW () . Fungsi ini juga berguna dalam lembar kerja tempat Anda ingin menghitung nilai berdasarkan hari dan waktu saat ini. Dengan klik kanan dan pilihan untuk properti Sel, Anda dapat menyesuaikan tampilan tanggal dan waktu. Untuk memperbarui informasi waktu ini di lembar kerja aktif, tekan Shift + F9; gunakan F9 untuk memperbarui seluruh workbook.

02 Menghitung sel yang terisi

Jika Anda memiliki sekelompok sel dengan teks dan angka dan Anda ingin mengetahui berapa banyak angka dalam pilihan, gunakan fungsi COUNT . Struktur rumusnya kemudian terlihat seperti ini: = COUNT (area pencarian) . Area tempat Excel harus mencari muncul di antara tanda kurung. Ini bisa berupa sel di bawah atau di samping satu sama lain, tetapi bisa juga berupa sel pilihan persegi panjang. Jika ada kata dalam pilihan, kata-kata dengan fungsi COUNT tidak dihitung. Jika Anda hanya ingin menghitung semua sel tempat ada sesuatu yang ditulis, gunakan function = COUNTA (tanpa titik).

03 Seberapa sering?

Gunakan fungsi COUNTIF untuk menghitung data tertentu . Misalkan Anda telah membuat sebuah kotak di mana empat orang muncul, maka Anda dapat melihat seberapa sering nama Herman muncul dengan = NANTAL.IF (area pencarian; “Herman”) . Masukkan rentang pencarian di antara tanda kurung dan masukkan kriteria pencarian dalam tanda kutip.

04 Penambahan selektif

Fungsi SUM untuk menjumlahkan sel banyak digunakan. Varian yang lebih cerdas adalah SOM.IF () . Pertama tentukan area tempat Excel harus mencari di antara tanda kurung. Rentang pencarian harus berupa rangkaian sel yang berdekatan. Setelah titik koma, Anda menentukan apa yang harus ditambahkan. Ini bisa berupa angka atau referensi. Jika merupakan persamaan, Anda harus mengapitnya dengan tanda kutip ganda. Misalnya, = SUMIF (B20: B40, "> 50") membuat jumlah semua sel dalam rentang ini lebih besar dari 50.

05 Penambahan dalam kondisi

Anda dapat memperluas kondisi penjumlahan lebih banyak lagi dengan menggunakan informasi di kolom lain. Contoh membuatnya jelas. Misalkan Anda memiliki angka yang terkait dengan tiga kota: Amsterdam, Rotterdam, dan Eindhoven. Maka Anda hanya dapat menambahkan nomor Amsterdam dengan = SUMIF (kisaran; "Amsterdam"; jarak penjumlahan) . Dalam hal ini, rumusnya menjadi = SUMIF (C48: C54; "Amsterdam"; B48: B54) . Dalam bahasa yang sederhana: Saat kata Amsterdam berada dalam rentang C48 hingga C54, Excel harus menjumlahkan nilai sel yang sesuai di sampingnya dalam rentang B48 hingga B54.

06 Gabung

Dengan fungsi CONCATENATE Anda menggabungkan data dari sel yang berbeda. Misalnya, sel nama depan dan belakang dengan sesuatu seperti = CONCATENATE TEXT (E34, ”“; F34) . Tanda kutip ganda dengan spasi memastikan bahwa ada spasi antara nama depan dan nama belakang. Dengan cara yang sama dimungkinkan untuk menggabungkan teks dengan mata uang. Misalnya, untuk menambahkan mata uang euro, Anda harus mengetiknya dengan fungsi seperti = CONCATENATE TEXT (A1, ""; B1; "" EURO (C1)) . Anda dapat membaca ini sebagai "menggabungkan sel A1, B1 dan C1 dengan spasi di antara mereka dan menempatkan tanda euro sebelum elemen ketiga dari penggabungan".

07 Bungkus

Excel memiliki beberapa opsi untuk pembulatan. Pembulatan default terlihat seperti = ROUND (angka, jumlah tempat desimal) . Jadi, rumusnya = ROUND (12.5624, 1) mengembalikan 12.6 . Bagaimanapun, Anda meminta untuk membulatkan ke satu angka setelah koma desimal. Excel juga akan membulatkan ke jumlah tempat desimal yang Anda tentukan dengan fungsi ROUND.TO.ABOVEN dan ROUND.BOTH . = ROUNDUP (12.5624; 2) sehingga memberikan hasil 12,57 dan = ROUNDDOWN (12.5624; 2), menghasilkan 12.56 . Fungsi INTEGER sebenarnya juga merupakan fungsi pembulatan, tetapi dengan itu Excel membulatkan ke bilangan bulat terdekat.

08 Huruf besar - huruf kecil

Untuk memastikan bahwa semuanya muncul dengan huruf kapital di kolom, gunakan fungsi UPPERCASE . Rumus SMALL.LETTERS melakukan kebalikannya. Dan jika Anda ingin setiap kata dimulai dengan huruf kapital diikuti dengan huruf kecil, Anda menggunakan fungsi INITIAL LETTERS . Rumus = HURUF KECIL (B4) memperlihatkan isi sel B4, tapi dalam huruf kecil.

09 Dalam kondisi

Saat penghitungan bergantung pada kondisi tertentu, Anda menggunakan fungsi IF . Prinsip dari fungsi ini adalah: = IF (syarat, kalkulasi jika syarat terpenuhi, kasus lain) . Untuk merumuskan kondisi tersebut, gunakan tanda: = sama dengan,tidak sama dengan, > lebih dari, < kurang dari, > = lebih dari atau sama dengan, < = kurang dari atau sama dengan. Misalkan dalam sebuah organisasi setiap orang menerima bonus yang telah terjual seharga 25.000 euro atau lebih. Jika Anda menerima bonus, kata "Hore" akan otomatis muncul di sebelah nama mereka, jika tidak, kata "Sayangnya" akan muncul. Rumus yang Anda butuhkan adalah = IF (B2> = 2500; "Hore"; "Sayangnya") .

10 Terbesar - Terkecil

Untuk mencari nilai tertinggi dan terendah dengan cepat, ada fungsi MAX dan MIN . Dengan = MAX (B2: B37) Anda mendapatkan nilai tertinggi dari sel ini, dan dengan = MIN (B2: B37) Anda mendapatkan nilai terendah dalam rentang tersebut. Fungsi TERBESAR dan TERKECIL lebih halus: Anda juga dapat meminta yang terbesar ketiga atau terkecil kedua, misalnya. Yang terbesar ditemukan dengan = TERBESAR (B2: B37; 1); angka 1 menunjukkan yang terbesar. Dengan = LARGE (B2: B37,2) Anda mendapatkan yang terbesar kedua dan seterusnya. Dengan begitu, Anda dapat dengan mudah menyusun 3 teratas atau 10 teratas.

11 Pencarian vertikal

Misalkan Anda memiliki dua lembar kerja dengan informasi berbeda tentang orang yang sama. Dengan VLOOKUP Anda mengambil informasi dari lembar kerja 2 di lembar kerja 1. Untuk membuatnya lebih mudah, kami telah memberi setiap orang nomor registrasi unik pada kedua tab. Juga beri nama untuk rentang pada tab 2 dari mana Anda ingin mendapatkan informasi. Dalam contoh ini, di lembar kerja 2, kami memilih kolom A dan B dan di kotak nama di kiri atas, kami mengetik nama Daftar Alamat . Di sel E2 lembar kerja 1, kami menempatkan fungsi VLOOKUP . Formatnya sekarang = VLOOKUP (A2, Daftar Alamat, 2, FALSE) . A2 merujuk ke sel dengan nomor pendaftaran di lembar kerja kedua, Daftar Alamatmenunjukkan rentang pencarian, 2 adalah nomor kolom di lembar kerja 2 tempat data yang diminta. Argumen terakhir adalah nilai logika di mana Anda memasukkan FALSE jika Anda ingin nilai yang ditemukan sama persis.

12 Hapus spasi

Dengan fungsi TRIM Anda menghapus spasi yang tidak perlu dalam teks. Fungsi ini akan meninggalkan beberapa spasi di antara kata, tetapi akan menghapus spasi sebelum atau sesudah kata. = TRIM (rentang sel) berguna untuk teks yang diimpor dari program lain. Di beberapa versi Excel, fungsi ini disebut SPACES.CLEAR .

13 Pertukaran

Anda dapat mentransfer konten kolom ke baris atau sebaliknya dengan fungsi TRANSPOSE . Pertama pilih sel tempat informasi harus ditempatkan. Pastikan untuk memilih sel sebanyak seri aslinya. Di sini kami mengetik tahun-tahun di baris 8 dan kuartal di kolom A. Kemudian ketikkan function = TRANSPOSE dan buka tanda kurung. Kemudian seret sel yang ingin Anda tukar (di sini dari sel B2 ke E5). Tutup tanda kurung dan sekarang tekan kombinasi tombol Ctrl + Shift + Enter. Ini akan membuat rumus array yang terkandung dalam tanda kurung kurawal.

14 Pembayaran bulanan

Jika Anda meminjam untuk suatu pembelian, berapa yang harus Anda bayar setiap bulan? Anggaplah Anda meminjam 25.000 euro ( B1 ) dengan bunga 6% ( B2 ) selama 5 tahun ( B3 ). Kami memperlihatkan rumus di wizard, tetapi Anda juga bisa mengetik. Dengan Bunga Anda menempatkan B2 / 12 , karena bunganya mengacu pada satu tahun dan Anda ingin tahu berapa yang Anda bayarkan setiap bulan. Untuk Angsuran angka kalikan B3 dengan 12 , karena tahun harus diubah menjadi bulan. Kotak PV berarti nilai saat ini , yaitu 25.000 euro. Ini memberikan rumusnya = BET (B2 / 12; B3 * 12; B1) atau =BET (6% / 12,5 * 12,25000) .

15 Tokoh palsu

Saat bereksperimen dengan formula, ada gunanya memiliki data palsu. Fungsi SELECT BETWEEN menghasilkan data acak yang berada di antara nilai terendah dan tertinggi yang ditentukan. Fungsi = RANDBET (50, 150) menghasilkan angka antara 49 dan 151.