Perkongsian kemahiran Excel Praktikal: 10 formula yang paling biasa digunakan dalam kalangan profesional

Artikel ini telah menyusun 10 formula excel yang paling biasa digunakan untuk profesional saya harap ia dapat membantu anda menyelesaikan masalah anda!

Tidak. Ia benar-benar tidak cekap untuk menggunakan fungsi formula untuk berfungsi, tetapi terdapat begitu banyak fungsi formula yang mustahil untuk mempelajari semuanya sekaligus. Ini adalah dilema yang dihadapi oleh ramai profesional.

Hari ini saya telah merumuskan sepuluh formula yang kerap digunakan untuk dikongsi dengan anda. Saya percaya jika anda mempelajari sepuluh formula ini, anda juga boleh berjaya di tempat kerja.

Formula 1: Pengiraan Bersyarat

Pengiraan bersyarat adalah sangat biasa dalam aplikasi Excel Contohnya, bilangan wanita dalam senarai statistik personel adalah keadaan perwakilan tipikal pengiraan.

Pengiraan bersyarat memerlukan penggunaan fungsi COUNTIF Struktur fungsi ialah =COUNTIF(统计区域,条件) Dalam formula pertama =COUNTIF(B:B,G2) dalam contoh ini, B:B ialah statistik luas, G2 ialah syarat, dan hasil formula menunjukkan bahawa terdapat 14 data "perempuan" dalam lajur B.

Syarat parameter kedua tidak boleh menggunakan rujukan sel, tetapi secara langsung menggunakan kandungan tertentu sebagai syarat Apabila syarat ialah teks, anda perlu menambah petikan berganda bahasa Inggeris pada kedua-dua belah syarat formula =COUNTIF(B:B,"女") adalah dengan cara ini.

Formula 2: Tandai data berulang dengan cepat

Kita sering menghadapi masalah menanda nilai berulang dalam kerja harian kita, contohnya, dalam dokumen Dalam jadual butiran jualan, tandakan nama jurujual pendua.

Mula-mula gunakan formula =COUNTIF(A:A,A2) untuk mengira bilangan kali setiap nama muncul Apabila hasilnya lebih daripada 1, ini bermakna nama itu diulang, dan kemudian gunakan fungsi IF untuk mendapatkan hasil akhir. Formula

ialah: =IF(COUNTIF(A:A,A2)=1,"","重复"), dan hasilnya adalah seperti yang ditunjukkan dalam rajah.

Terdapat cara lain untuk menentukan sama ada nama diulang: kejadian pertama tidak dianggap sebagai pengulangan, dan hanya kejadian kedua dianggap sebagai pengulangan.

Apabila menghadapi situasi ini, anda hanya perlu mengubah suai kawasan keadaan fungsi COUNTIF Formulanya ialah: =IF(COUNTIF($A:A2,A2)=1,"","重复"), dan hasilnya adalah seperti yang ditunjukkan dalam rajah.

Formula 3: Mengira berbilang syarat

Jika anda ingin mengira berbilang syarat, anda mesti Gunakan fungsi COUNTIFS, sebagai contoh, anda perlu mengira bilangan lelaki yang mempunyai ijazah sarjana muda di sekolah menengah.

Struktur fungsi COUNTIFS ialah =COUNTIFS(kawasan data 1, keadaan 1, kawasan data 2, keadaan 2,...), dan boleh ada sehingga 127 set syarat.

Dalam formula pertama contoh ini =COUNTIFS(B:B,G2,C:C,G3), terdapat dua set syarat Lajur B digunakan untuk menilai jantina, dan lajur C digunakan untuk menilai latar belakang pendidikan.

Begitu juga, syarat boleh merujuk kepada sel atau menggunakan kandungan tertentu secara langsung, yang betul-betul sama dengan fungsi COUNTIF.

Formula 4: Penjumlahan bersyarat

Selain pengiraan bersyarat, penjumlahan bersyarat juga digunakan secara meluas, seperti statistik dalam butiran jualan Jumlah TV jualan.

Fungsi SUMIF perlu digunakan apabila menjumlahkan dengan syarat Struktur fungsi ialah =SUMIF (kawasan bersyarat, keadaan, kawasan penjumlahan). kawasan bersyarat Ia adalah lajur B, dan kawasan penjumlahan ialah lajur C. Syarat boleh menggunakan sel atau kandungan tertentu secara langsung Untuk mengira jumlah jualan TV, terdapat dua cara untuk menulis: =SUMIF(B:B,"TV". ,C:C ) dan =SUMIF(B:B,B2,C:C).

Formula 5: Penjumlahan berbilang syarat

Jika ada jumlah bersyarat, akan ada jumlah berbilang syarat, contohnya, berdasarkan jurujual dan produk Apabila menjumlahkan dua syarat, fungsi penjumlahan berbilang syarat SUMIFS digunakan.

Struktur fungsi SUMIFS ialah =SUMIFS (kawasan penjumlahan, kawasan keadaan 1, keadaan 1, kawasan keadaan 2, keadaan 2,...), dalam contoh ini , Kawasan penjumlahan ialah kuantiti jualan dalam lajur D, kawasan syarat pertama ialah jurujual dalam lajur B, dan kawasan syarat kedua ialah nama produk dalam lajur C, jadi formula akhir ialah:

=SUMIFS(D:D,B:B,"沈伊杰",C:C,"壁挂空调")

Satu perkara yang perlu diambil perhatian ialah lokasi kawasan penjumlahan SUMIFS adalah berbeza daripada kawasan penjumlahan SUMIFS adalah dalam parameter pertama, manakala kawasan penjumlahan SUMIFS adalah dalam. parameter ketiga.

Formula 6: Kira tarikh lahir berdasarkan nombor ID

Untuk dapatkan tarikh lahir dari nombor ID, masalah ini amat sukar bagi orang yang terlibat dalam masalah ini Rakan-rakan yang berada dalam jawatan pentadbiran mesti biasa dengannya, dan formulanya agak mudah:

=TEXT(MID(A2,7,8),"0 -00-00") untuk mendapatkan hasil yang diperlukan, seperti yang ditunjukkan dalam rajah Ditunjukkan:

Untuk memahami prinsip formula ini, anda mesti terlebih dahulu mengetahui beberapa peraturan dalam Nombor kad pengenalan Kad pengenalan yang digunakan pada asasnya ialah 18 digit, bermula dari lapan digit bermula dengan tujuh digit mewakili tarikh lahir.

Formula ini melibatkan dua fungsi Pertama, mari kita lihat fungsi MID Fungsi MID mempunyai tiga parameter Formatnya ialah: =MID (dari mana untuk mengekstrak, dari mana untuk memulakan dan berapa banyak perkataan ekstrak).

MID(A2,7,8) bermaksud memintas lapan digit bermula dari nombor ketujuh sel A2 Kesannya adalah seperti yang ditunjukkan dalam rajah:

<.> Selepas tarikh lahir diekstrak, ia bukan kesan yang kami perlukan Pada masa ini, fungsi ahli silap mata TEXT akan dimainkan. dalam format apa yang hendak dipaparkan"), dalam contoh ini Kandungan yang akan diproses ialah bahagian fungsi MID. Format paparan ialah "0-00-00". Sudah tentu, anda boleh memaparkannya dalam format "0年00月00日". Formula ditukar kepada =TEXT(MID(A2 ,7,8),"00月00日") akan melakukan:

Formula 7: Kira umur berdasarkan nombor ID

Dengan tarikh lahir, sudah tentu anda akan terfikir untuk mengira umur formulanya ialah: =DATEDIF(B2,TODAY() . ,"Y")

Fungsi Excel tersembunyi DATEDIF digunakan di sini Fungsi ini memerlukan tiga parameter dan struktur asas ialah =DATEDIF (tarikh mula, tarikh akhir, kaedah pengiraan ).

Dalam contoh ini, tarikh mula ialah tarikh lahir, menggunakan B2 sebagai parameter pertama adalah hari ini, menggunakan fungsi TODAY() sebagai parameter kedua dikira mengikut tahun; menggunakan "Y" sebagai parameter ketiga.

Jika anda perlu mengira umur terus daripada nombor ID, formula boleh ditulis sebagai:

=DATEDIF(TEXT(MID(A2,7,8),"0-00- 00") ,TODAY(),"Y")

Formula 8: Keputusan yang berbeza diperoleh mengikut selang

Masalah jenis ini sering dilihat dalam penilaian prestasi Contohnya, apabila syarikat menjalankan penilaian prestasi untuk pekerja, ia perlu menentukan tahap ganjaran berdasarkan keputusan penilaian adalah: E untuk markah bawah 50, D untuk 50-65 (inklusif), dan D untuk 65-75 (inklusif ) ialah C, 75-90 (termasuk) ialah B, dan di atas 90 ialah A.

Anda boleh menggunakan formula =LOOKUP(E2,{0;50;65;75;90},{"E";"D";"C";"B";"A"}) untuk mendapatkan setiap Tahap ganjaran setiap pekerja, hasilnya adalah seperti yang ditunjukkan dalam rajah:

Sukar untuk menerangkan prinsip formula ini. Anda boleh merujuk kepada tutorial sebelumnya tentang fungsi LOOKUP.

Malah, untuk menyelesaikan masalah seperti ini, cukup untuk mengingati rutin: LOOKUP mengembalikan hasil yang sepadan mengikut selang: =LOOKUP(skor, {senarai nilai had bawah}, {senarai tahap ganjaran }), nilai had bawah Ia dipisahkan oleh koma bertitik, dan tahap ganjaran juga dipisahkan oleh koma bertitik.

Anda juga boleh memasukkan hubungan yang sepadan antara had bawah skor dan tahap ganjaran dalam jadual. Formula boleh diubah suai sebagai =LOOKUP(E2,$I$2:$J$6), dan hasilnya adalah seperti yang ditunjukkan dalam rajah.

Formula 9: Data padanan syarat tunggal

Jika anda ingin menguasai tempat kerja, apakah yang boleh anda lakukan jika anda tidak tahu bagaimana untuk memadankan? Bagaimanakah saya boleh melakukan pemadanan syarat tunggal tanpa VLOOKUP?

Struktur asas fungsi VLOOKUP ialah =VLOOKUP (apa yang perlu dicari, di mana untuk mencarinya, lajur mana yang hendak dicari, bagaimana untuk mencarinya Contohnya, untuk mencari tahap pendidikan tertinggi). dengan nama, anda boleh menggunakan formula =VLOOKUP(G2,B:E,4 ,0) Dapatkan hasil yang diingini, seperti yang ditunjukkan dalam rajah:

Terdapat dua perkara utama yang mesti anda ketahui apabila menggunakan fungsi ini:

①Apa yang anda cari Ia mestilah dalam lajur pertama julat carian Contohnya, apabila mencari mengikut nama, julat carian bermula dari lajur B daripada lajur A.

②Nombor lajur merujuk kepada lajur dalam julat carian dan bukannya lajur dalam jadual Contohnya, jika anda ingin mencari tahap pendidikan tertinggi, ia berada dalam lajur ke-4 julat carian, bukan nombor lajur. 5 dalam jadual.

Formula 10: Memadankan data dengan berbilang syarat

Anda akan benar-benar kebal jika anda belajar memadankan data dengan berbilang syarat!

Berikan contoh memadankan kuantiti jualan mengikut nama dan nama produk, seperti yang ditunjukkan dalam rajah:

Formulanya ialah =LOOKUP(1,0/(($A:$A=E2)*($B:$B=F2)),$C:$C)

Rutin penggunaan fungsi LOOKUP untuk pemadanan berbilang keadaan ialah: =LOOKUP(1,0/((julat carian 1=nilai carian 1)*(julat carian 2=nilai carian 2)*……*(julat carian n= Nilai carian n)), julat hasil), perlu diperhatikan bahawa terdapat hubungan pendaraban antara berbilang keadaan carian, dan ia perlu diletakkan dalam set kurungan yang sama dengan penyebut 0/.

Baiklah, sepuluh formula yang paling biasa digunakan dikongsikan di sini Setelah digunakan, anda benar-benar boleh menguasai tempat kerja!

Cadangan pembelajaran berkaitan: tutorial cemerlang

Atas ialah kandungan terperinci Perkongsian kemahiran Excel Praktikal: 10 formula yang paling biasa digunakan dalam kalangan profesional. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!