3 cara kegemaran saya untuk menggunakan data dalam jadual excel

Tiga fungsi praktikal untuk pengekstrakan dan ringkasan data Excel

Katakan anda mempunyai buku kerja yang besar dengan jadual yang diformat, ditapis dan disusun dengan indah. Anda mungkin berfikir bahawa kerja itu dilakukan, tetapi pada hakikatnya, Excel sedang menunggu anda untuk berbuat lebih banyak pada jadual ini, bersemangat untuk membantu anda memanfaatkan sepenuhnya kerja keras yang telah anda lakukan setakat ini.

Artikel ini akan memperkenalkan tiga fungsi atau kombinasi fungsi yang sering saya gunakan yang boleh digunakan untuk mengekstrak atau meringkaskan maklumat dalam jadual Excel.

vlookup dan hlookup

VLOOKUP dan HLOOKUP kedua -duanya digunakan untuk mencari dan mengambil nilai di lokasi tertentu dalam jadual.

• VLOOKUP bergantung pada jadual data menegak dan melihat lajur pertama (menegak) dalam jadual.
• Hlookup bergantung pada jadual data mendatar dan melihat baris pertama (mendatar) dalam jadual.

vlookup

di sini, saya mempunyai senarai skor ujian dan gred yang diperlukan untuk setiap gred (kami panggil Jadual 1). Saya juga mempunyai jadual dengan gred pelajar (dipanggil Jadual 2 dari titik ini). Saya mahu Excel menggunakan maklumat dalam Jadual 1 untuk melengkapkan lajur yang hilang dalam Jadual 2.

Saya akan menggunakan VLOOKUP kerana saya mahu Excel mencari nilai dalam lajur pertama Jadual 1 untuk mengembalikan gred setiap pelajar dalam Jadual 2. Sintaks fungsi vlookup adalah seperti berikut:

<code>=VLOOKUP(a,b,c,d)</code>

of:

• a adalah nilai yang akan dicari (dalam contoh di atas, iaitu nilai dalam lajur e),
• b adalah jadual yang mengandungi nilai rujukan (dalam kes ini, ia adalah sel A1 hingga B9, atau Jadual 1),
• c adalah nombor lajur dalam jadual itu (saya mahu ia mengembalikan tahap, jadi ia adalah lajur kedua dalam Jadual 1), dan
• d adalah keadaan pilihan yang memberitahu Excel sama ada untuk menghampiri nilai carian ("benar") atau nilai carian yang tepat ("palsu"). Jika dibiarkan kosong, nilai lalai adalah benar.

Jadi, dalam kes saya, saya akan memasukkan formula ini dalam sel F2 untuk mengira gred TOM dan kemudian gunakan Autofill untuk mencari gred lain dalam jadual:

<code>=VLOOKUP(E2,$A:$B,2,TRUE)</code>

Saya menggunakan simbol $ untuk membuat rujukan mutlak untuk nilai di atas

b kerana saya mahu Excel menggunakan sel A1 hingga B9 secara berterusan untuk mencari nilai. Saya juga menggunakan "benar" untuk nilai d kerana jadual sempadan skor mengandungi julat, bukan pangkat yang diberikan kepada satu skor.

hlookup

di sini, kami mempunyai maklumat sempadan tahap yang sama, tetapi kali ini ia dipaparkan secara mendatar. Ini bermakna data yang kita mahu ambil terletak di barisan kedua jadual sempadan.

sintaks fungsi hlookup adalah serupa dengan vlookup:

of:

• a adalah nilai yang dapat dijumpai (dalam contoh ini, nilai dalam lajur c),
• b adalah rujukan mutlak kepada sel yang mengandungi nilai carian (dalam kes ini, ia adalah a1 hingga i2),
• c adalah nombor baris dalam jadual (saya mahu ia mengembalikan tahap, jadi ia adalah baris kedua), dan
• d (pilihan) adalah "benar" (nilai anggaran) atau "palsu" (nilai tepat).

jadi saya akan memasukkan formula ini dalam sel C5 untuk mengira gred TOM dan kemudian gunakan Autofill untuk mencari gred lain dalam jadual:

<code>=VLOOKUP(a,b,c,d)</code>

indeks dan perlawanan

Satu lagi cara yang berkesan untuk mencari dan mengambil nilai adalah melalui indeks dan perlawanan, terutamanya apabila digunakan bersama. Indeks mencari dan mengembalikan nilai kedudukan yang ditakrifkan, sementara perlawanan mencari dan mengembalikan sedikit nilai. Bersama -sama, mereka dapat melaksanakan pengambilan data dinamik.

Tatabahasa tunggal

Sebelum kita melihat fungsi -fungsi ini bersama -sama, mari kita lihat secara ringkasnya secara individu.

Sintaks indeks adalah

di mana

<code>=VLOOKUP(E2,$A:$B,2,TRUE)</code>

a

adalah julat sel yang mengandungi data, b adalah nombor baris yang akan dinilai, dan c adalah nombor lajur yang akan dinilai. berdasarkan ini,

akan menilai sel -sel B2 hingga D8 dan mengembalikan nilai -nilai dalam barisan keempat dan lajur kedua dalam julat itu.

<code>=HLOOKUP(a,b,c,d)</code>

untuk perlawanan, kami ikuti

di mana

<code>=HLOOKUP(B5,$A:$I,2,TRUE)</code>

x

adalah nilai yang kita cari, berdasarkan ini, akan memberitahu saya di mana nombor 5 berada dalam julat B2 hingga B8, manakala 0 memberitahu Excel untuk melakukan perlawanan yang tepat.

Gunakan dalam kombinasi

Dalam contoh ini, saya mahu Excel memberitahu saya jumlah matlamat skor pemain tertentu dalam bulan tertentu. Lebih khusus lagi, saya ingin tahu berapa banyak gol yang dijaringkan oleh pemain C pada bulan ketiga, tetapi saya akan membuat formula ini supaya saya dapat mengubah keadaan pada bila -bila masa.

<code>INDEX(a,b,c)</code>

Untuk melakukan ini, saya memerlukan Excel untuk menentukan kedudukan pemain C dalam jadual dan kemudian beritahu saya nilai dalam lajur ketiga data.

Dalam sel G4, saya akan bermula dengan fungsi indeks kerana saya mahu Excel melihat dari data asal saya dan mengembalikan nilai. Saya kemudian akan memberitahu Excel di mana untuk mencari data itu.

Bahagian seterusnya sintaks indeks adalah nombor garis, yang akan berbeza -beza bergantung kepada pemain yang saya nyatakan dalam sel G2. Sebagai contoh, jika saya mahu mencari pemain A, ia akan menjadi baris pertama. Untuk melakukan ini, saya akan memulakan fungsi perlawanan kerana saya mahu Excel sepadan dengan pemain dalam sel G2 ke sel yang sepadan dalam lajur Pemain (A2: A8) dan mengetahui baris mana yang terletak. Saya juga menambah 0 yang terakhir kerana saya mahu Excel mengembalikan pengambilan semula yang tepat.

<code>=VLOOKUP(a,b,c,d)</code>

Sekarang saya telah memberitahu nombor talian fungsi indeks Excel, saya perlu melakukannya dengan nombor lajur. Dalam contoh saya, nombor lajur mewakili nombor bulan yang saya taip dalam sel G3.

<code>=VLOOKUP(E2,$A:$B,2,TRUE)</code>

Apabila saya menekan kekunci Enter, Excel dengan betul memberitahu saya bahawa Player C telah menjaringkan lima gol pada bulan ketiga.

Sekarang saya boleh menukar apa -apa nilai dalam jadual carian saya untuk mencari jumlah pemain dalam mana -mana bulan.

countif dan sumif

Seperti yang anda lihat dari nama mereka, kedua -dua fungsi ini mengira dan nilai jumlah berdasarkan syarat yang anda tetapkan. Apa -apa pun yang tidak termasuk dalam keadaan anda tidak akan ditambah atau dikira, walaupun ia berada dalam skop yang anda tentukan.

countif

Countif mengira sel yang mengandungi keadaan tertentu. Sintaks adalah

di mana

<code>=HLOOKUP(a,b,c,d)</code>

a

adalah julat yang anda mahu hitung, dan b adalah syarat untuk mengira. Begitu juga, jika saya ingin memasukkan pelbagai syarat, saya akan menggunakan countif:

di mana

<code>=HLOOKUP(B5,$A:$I,2,TRUE)</code>

a dan b

boleh mempunyai sehingga 127 pasang). Jika sebarang keadaan adalah simbol teks atau logik atau matematik, ia mesti disertakan dalam petikan berganda. Dalam jadual gaji saya di bawah, saya ingin mengira bilangan orang yang memperoleh lebih dari £ 40,000 dan bilangan orang yang menerima lebih daripada £ 1,000 bonus secara berasingan. Untuk mengira bilangan pekerja yang mempunyai gaji lebih dari £ 40,000, saya perlu memasukkan formula ini dalam sel D8:

di mana

c2: c6

adalah julat gaji,

"& gt; 40000" adalah keadaan.

Untuk mengira bilangan kakitangan perkhidmatan yang menerima lebih daripada £ 1,000 bonus, saya akan menggunakan countif kerana saya mempunyai dua syarat.

<code>INDEX(a,b,c)</code>

B2: B6, "Perkhidmatan" Bahagian adalah pasangan bersyarat pertama, d2: d6, "& gt; 1000" adalah yang kedua.

Walaupun saya memisahkan ribuan digit dengan koma dalam jadual saya di atas, saya tidak memasukkan koma ini dalam formula, kerana koma mempunyai fungsi yang berbeza di sini.

<code>INDEX(B2:D8,4,2)</code>

sumif sumif jumlah sel mengikut syarat yang anda tetapkan. Ia berfungsi sama seperti COUNTIF, tetapi mengandungi lebih banyak parameter dalam kurungan. Sintaks adalah di mana

A adalah pelbagai sel yang anda ingin menilai sebelum meminta,

b

adalah syarat untuk penilaian itu (ini boleh menjadi nilai atau rujukan sel),

c (pilihan) adalah sel yang akan ditambah jika ia berbeza daripada

a .

Kali ini, kita perlu mengira tiga perkara: jumlah gaji lebih daripada £ 40,000, jumlah gaji untuk jabatan perkhidmatan dan jumlah bonus untuk pekerja yang mempunyai lebih daripada £ 35,000.

Pertama, untuk mengira jumlah gaji lebih daripada £ 40,000, saya perlu memasuki formula berikut dalam sel D8:

<code>=VLOOKUP(a,b,c,d)</code>

di mana

c2: c6 memetik gaji dalam jadual, "& gt; 40000" memberitahu Excel untuk jumlah nilai hanya melebihi jumlah ini.

Seterusnya, saya ingin mengetahui jumlah gaji jabatan perkhidmatan. Jadi, dalam sel C9, saya akan menaip

<code>=VLOOKUP(E2,$A:$B,2,TRUE)</code>

di mana

b2: b6 petikan lajur jabatan, "Perkhidmatan" memberitahu Excel Saya mencari pekerja di jabatan perkhidmatan, c2: c6 beritahu excel Mengenai upah pekerja ini disimpulkan.

Tugas terakhir saya adalah untuk mengetahui berapa banyak bonus pekerja yang memperoleh lebih dari £ 35,000 yang diterima. Dalam sel C10, saya akan menaip

<code>=HLOOKUP(a,b,c,d)</code>

di mana

c2: c6 memberitahu Excel untuk menilai upah, "& gt; 35000" Syarat -syarat Bonus Peribadi. Excel juga mempunyai fungsi SUMIFS, yang melakukan prosedur yang sama, tetapi untuk pelbagai keadaan. Ia sangat berbeza dengan sintaks Sumif:

di mana

a

adalah julat sel yang akan ditambah,

b

<code>=HLOOKUP(B5,$A:$I,2,TRUE)</code>

adalah julat pertama yang akan dinilai,

c adalah b , d dan e adalah pasangan keadaan julat seterusnya (anda boleh mempunyai sehingga 127 pasang). Menggunakan jadual di atas, katakan saya ingin jumlah bonus untuk pekerja yang memperoleh lebih dari £ 45,000. Inilah formula yang akan saya masukkan: Selepas menguasai fungsi di atas, cuba gunakan fungsi XlookUp, yang direka untuk menyelesaikan beberapa kelemahan VLOOKUP dengan mencari nilai di sebelah kiri dan kanan lajur Nilai Cari tanpa menyusun semula data.

Atas ialah kandungan terperinci 3 cara kegemaran saya untuk menggunakan data dalam jadual excel. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!