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.

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

• 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.

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

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).

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

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

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

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

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

untuk perlawanan, kami ikuti

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

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.

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

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.

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

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

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

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

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

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

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

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

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

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

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!