Jadual dinamik OFFSET eklektik pembelajaran carta Excel

Ia adalah permulaan musim sejuk dan ia sejuk! Tiada pemanasan di dalam rumah? Hanya bergerak, hentak kaki, gosok tangan anda, dan anda akan berasa hangat. Ia adalah permulaan musim sejuk, dan satu tahun lagi akan berakhir! Belum ada kenaikan gaji? Biarkan jam tangan anda bergerak berbeza daripada sebelumnya, kemas kini serentak dan dipaparkan secara dinamik Bukan sahaja ia akan menjimatkan masalah anda, bos anda juga ingin membayar anda...

Langkah pertama: Kagumkan bos anda dengan carta bergerak

Bos sudah biasa melihat jadual biasa yang anda laporkan (di bawah), dan kini anda tiba-tiba menunjukkan kepadanya carta (di bawah) yang boleh ditanya secara dinamik, anda berkata Bolehkah anda memenangi hati bos anda yang kritikal? Bolehkah anda mengejutkan bos anda dengan ganjaran?

Laporan anda sekarang                                                                                                                                                                                                                                                                                                                                                                                                                             Ia sangat mudah, hanya buat carta dinamik.

Carta dinamik bermakna bos memilih kawasan yang berbeza dan carta memaparkan data yang berbeza. Terdapat tiga langkah untuk mencapai ini: satu langkah ialah membuat menu lungsur untuk dipilih oleh bos, satu langkah ialah membuat kawasan data yang berubah secara dinamik mengikut pemilihan, dan satu langkah ialah memasukkan carta berdasarkan kawasan data dinamik.

Kawasan data dinamik biasanya dilaksanakan dengan fungsi VLOOKUP, tetapi hari ini kami mengambil pendekatan yang berbeza Kami menggunakan fungsi OFFSET untuk melengkapkan kawasan data dinamik.

Langkah 1: Buat pilihan lungsur turun

1 Pilih sel J1 dan klik "Pengesahan Data" di bawah tab "Data".

2 Dalam pilihan "Tetapan" di bahagian bawah tetingkap "Pengesahan Data", pilih "Siri" untuk "Benarkan" dan pilih sel di mana lima jualan kawasan terletak sebagai sumber =$A$2:$A$6", klik OK.

Langkah 2: Buat kawasan data tambahan yang dikemas kini secara dinamik

3. Langkah seterusnya ialah mengemas kini carta secara dinamik berdasarkan nilai sel J1. J1 memilih "Kawasan Beijing". Masukkan "=OFFSET(B1,MATCH($J$1,$A$2:$A$6,0),0)" dalam sel B7. Kemudian formula mengisi ke kanan ke sel G7. Dengan cara ini, sel B7:G7 mengembalikan volum jualan di kawasan Beijing dari Januari hingga Jun.

Analisis:

Gunakan OFFSET untuk mengambil "B1" sebagai sistem rujukan, dan bilangan baris offset ialah Gunakan fungsi MATCH untuk mendapatkan $J $1 dalam $A$2 :Pada kedudukan $A$6, nombor lajur offset ialah 0, yang bermaksud tiada offset. Seperti yang ditunjukkan dalam Rajah J1, nilainya ialah "kawasan Beijing", dan kedudukan $A$2:$A$6 ialah 1. Nilai yang dikembalikan oleh OFFSET ialah rujukan mengimbangi satu baris ke bawah dengan "B1" sebagai sistem rujukan. Dengan cara ini, apabila kawasan yang dipilih $J$1 terus berubah, sel B7:G7 akan memperoleh data jualan bagi kawasan yang sepadan.

4. Kemudian tetapkan data baris purata dan masukkan "=AVERAGE($B$7:$G$7)" dalam sel B8 untuk mendapatkan purata $B$7:$G$7. Kemudian formula mengisi ke kanan ke sel G8. Jika kawasan yang dipilih $J$1 berubah, maka $B$7:$G$7 berubah dan nilai purata juga akan berubah dengan sewajarnya.

Langkah 3: Buat carta

5 Kami mencipta carta berdasarkan baris tambahan yang ditetapkan. Pilih pengepala B1:G1 dan kawasan baris tambahan B7:G8 dan klik "Carta Lajur Dua dimensi" dalam kumpulan "Carta" di bawah tab "Sisipkan".

6. Masukkan "=J1&"Data Jualan"" dalam sel K1.

Klik kotak tajuk carta dan masukkan "=Helaian2!$K$1" dalam bar edit, supaya tajuk carta dan kawasan pengesahan data dikemas kini secara serentak.

7 Klik "Tukar Jenis Carta" di bawah tab "Reka Bentuk" di bawah "Alat Carta".

8 Dalam tetingkap "Tukar Jenis Carta", klik "Gabungan" di bawah pilihan "Semua Carta" dan tukar siri di mana nilai purata terletak kepada ". Carta Garis".

9 Akhir sekali, padamkan legenda carta dan tukar fon data tambahan B7:G8 dan sel K1 kepada putih dan tidak kelihatan, dan anda sudah selesai.

Tindakan kedua: pautan, supaya jadual pangsi dan sumber data boleh dikemas kini secara serentak

Jika anda ingin mengemas kini jadual pangsi secara serentak dengan sumber data, saya telah memperkenalkan anda ke meja super sebelum mencapai. Selain itu, kita juga boleh menggunakan OFFSET. Seperti yang ditunjukkan dalam rajah, jadual pangsi di sebelah kanan disisipkan berdasarkan sumber data di sebelah kiri.

Kini kita memerlukan jadual pangsi untuk dikemas kini serentak selepas sumber data dikemas kini, seperti berikut:

Langkah 1: Tentukan nama

1 Klik "Tentukan Nama" dalam kumpulan pilihan "Nama Ditentukan" di bawah tab "Formula".

2. Dalam tetingkap "Nama Baharu", masukkan "data" dalam lajur "Nama" dan formula berikut dalam "Kedudukan Rujukan"

=OFFSET(Helaian1 !$A$1,,,COUNTA(Helaian1!$A:$A),COUNTA(Helaian1!$1:$1))

Analisis:

"Helaian1" ialah lembaran kerja tempat data berada. Fungsi ini mengambil "Sheet1!$A$1" sebagai sistem rujukan, tidak mengimbangi (baris dan lajur mengimbangi kosong) dan mengembalikan keseluruhan data jadual secara dinamik. COUNTA(Sheet1!$A:$A) digunakan untuk mendapatkan bilangan baris data jadual, dan COUNTA(Sheet1!$1:$1) digunakan untuk mendapatkan bilangan lajur data jadual. Keputusan yang mereka peroleh adalah dinamik dan berubah apabila bilangan baris dan lajur dalam jadual bertambah atau berkurang.

Langkah 2: Tukar sumber data

3 Klik mana-mana sel pada jadual pangsi dan "Jadual Pangsi". muncul alat". Kemudian klik "Tukar Sumber Data" dalam tab "Analisis" di bawah "Alat Jadual Pangsi".

4. Dalam tetingkap "Tukar Sumber Data Jadual Pangsi", tukar "Jadual/Julat" kepada nama "Data" yang telah ditentukan dan klik OK.

Langkah 3: Muat semula penyegerakan

5. Seterusnya, tambahkan data dalam baris terakhir data.

6. Klik kanan jadual pangsi dan pilih arahan "Muat Semula", dan jadual pangsi akan dikemas kini.

Menggunakan fungsi Offset, kami telah melaksanakan dua "tindakan". Tindakan pertama ialah menggunakan carta dinamik untuk menarik perhatian bos supaya dia boleh membuka "gaji". Tindakan kedua ialah memautkan jadual pangsi dengan sumber data Simpan diri anda lebih banyak masalah. Ok, cepat hantar borang anda! Musim sejuk akan tiba, walaupun tangan dan kaki sejuk tidak dapat dielakkan, tetapi kenaikan gaji akan menghangatkan hati anda!

Cadangan pembelajaran berkaitan: tutorial cemerlang

Atas ialah kandungan terperinci Jadual dinamik OFFSET eklektik pembelajaran carta Excel. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!