Pembelajaran fungsi Excel: Mari kita bercakap tentang raja statistik dinamik OFFSET()

Artikel ini akan membawa anda melalui fungsi OFFSET, yang digelar "Raja Statistik Dinamik"! Fungsi OFFSET ialah fungsi yang sangat praktikal Ia memainkan peranan yang tidak boleh ditukar ganti dalam menu lungsur, carta dinamik, rujukan dinamik dan operasi lain. Tidak keterlaluan untuk mengatakan bahawa sebahagian besar daripada kecekapan jadual Excel datang daripada OFFSET.

【Kata Pengantar】

Fungsi OFFSET ialah salah satu fungsi penting untuk menentukan sama ada pengguna fungsi Excel telah maju. Dalam kerja sebenar, jika anda perlu memodelkan fail data di tempat kerja secara sistematik dan automatik, anda pasti akan menggunakan fungsi ini.

[Fungsi dan Sintaks]

Fungsi fungsi OFFSET adalah untuk menggunakan rujukan yang ditentukan sebagai sistem rujukan dan mengembalikan rujukan baharu melalui offset yang diberikan.

Sintaks: OFFSET(reference,rows,cols,[height],[width])

• rujukan ialah titik asas asal

• baris ialah bilangan baris yang akan diimbangi , Nombor positif turun, nombor negatif naik, dan sifar tidak berubah.

• kol ialah bilangan lajur untuk diimbangi, nombor positif pergi ke kanan, nombor negatif pergi ke kiri dan sifar kekal tidak berubah.

• [ketinggian] ialah pengembangan menegak beberapa baris selepas titik asas diimbangi Nombor positif mengembang ke bawah dan nombor negatif mengembang ke atas.

• [lebar] ialah pengembangan mendatar beberapa lajur selepas titik asas diimbangi Nombor positif mengembang ke kanan dan nombor negatif mengembang ke kiri.

Jika parameter keempat dan kelima tidak digunakan (tetapi tidak boleh sifar), kawasan yang baru dirujuk akan mempunyai saiz yang sama dengan titik asas asal.

Titik asas asal boleh menjadi sel atau kawasan.

Bagi pelajar yang baru bersentuhan dengan fungsi OFFSET, mungkin sukar untuk memahami parameter di atas, jadi izinkan kami menerangkannya kepada anda dengan gambar rajah.

Saya percaya anda telah meluangkan banyak masa melihat gambar ini. Mula-mula kita boleh mengisi data ke dalam fungsi OFFSET mengikut garis panduan dalam gambar di atas, dan lakukan secara praktikal untuk melihat sama ada ia konsisten dengan alamat kawasan baharu?

Mari kita uji contoh pertama dahulu dan lihat hasil yang dijalankan apabila nombor positif ialah parameter:

Lepasi pengesahan dan "kawasan baharu" kuning ialah Nilai dijumlahkan dan sama dengan 256, yang konsisten dengan nilai dalam sel C15 dan hasilnya betul. Jika pelajar ingin mensimulasikan data ini, mereka juga boleh memilih sel C15, dan kemudian menggunakan fungsi "Formula - Semakan Formula - Penilaian Formula" dalam bar alat untuk melihat nilai pulangan OFFSET dengan lebih intuitif. (Ia juga mungkin untuk menggunakan F9 dalam fungsi. Pilih bahagian fungsi OFFSET formula dan tekan F9. Saya tidak akan pergi ke butiran lanjut di sini.)

Mari kita uji semula Contoh kedua, lihat keputusan operasi apabila nombor negatif ialah parameter:

Anda boleh menggunakan kaedah "penilaian formula" untuk mengujinya sendiri dan lihat bagaimana kawasan fungsi OFFSET berfungsi.

Setelah mengetahui prinsip operasi asas OFFSET, ia boleh membantu kami melaksanakan banyak operasi dan pengiraan dalam kerja sebenar, dan dengan penyertaan fungsi ini, banyak kesan automatik dalam excel boleh dicapai. Mari kita lihat peranan berkuasa fungsi OFFSET dalam operasi sebenar!

1. Penggunaan umum asas

Sebagai rujukan serantau untuk fungsi lain, ia sepatutnya menjadi penggunaan paling asas bagi fungsi OFFSET. Fungsi OFFSET tidak mengalihkan julat sel, tetapi mengembalikan alamat julat yang dikembangkan mengimbangi. Oleh itu, semua fungsi yang mengambil kawasan rujukan sebagai parameter boleh menggunakan nilai pulangan fungsi OFFSET, seperti contoh kami di atas Jumlah (OFFSET ()), atau contoh berikut:

Prinsip fungsi adalah sama seperti penggunaan di atas, jadi kami tidak akan pergi ke butiran Kami masih menggunakan kawasan yang dikembalikan oleh fungsi OFFSET sebagai parameter fungsi MAX.

2. Penggunaan biasa lanjutan

Kemahiran istimewa ①: Fungsi TRANSPOSE analog

Sebelum menggunakan fungsi TRANSPOSE, kita perlu memilih kawasan transpose dengan saiz yang sepadan, dan kita juga perlu menggunakan Ctrl+Shift+Enter untuk menamatkan formula, yang agak menyusahkan.

Di sini kita boleh menggunakan fungsi OFFSET untuk mensimulasikan kesan transposisi ini, seperti yang ditunjukkan dalam rajah di atas.

Fungsi sel A11:

=OFFSET($A,COLUMN()-1,ROW()-11)

Analisis fungsi:

Mengalih data sebenarnya adalah proses "baris ke lajur" dan "lajur ke baris". Lajur "Nama", lajur pertama dalam data asal, menjadi baris pertama dalam kawasan baharu selepas transposisi. Dengan cara yang sama, nombor baris setiap baris dalam lajur "Nama" menjadi nombor lajur yang ditukar. Prinsip penggunaan OFFSET adalah untuk menukar julat petikan nombor baris dan lajur apabila mengambil nilai offset.

★ Sebagai contoh, dalam sel A11, COLUMN()=1, 1-1=0, maka parameter kedua OFFSET ialah 0, menunjukkan bahawa bilangan baris titik asas asal tidak diimbangi ( parameter kedua OFFSET menunjukkan Row offset, jika anda tidak biasa dengannya, sila baca kandungan sebelumnya). ROW()=11, 11-11=0, dan parameter ketiga OFFSET ialah 0, yang bermaksud bilangan lajur tidak diimbangi, jadi nilai sel titik asas asal A1 disebut.

★★ Tarik fungsi ke kanan untuk mengisi, sel B11, COLUMN()=2, 2-1=1, kemudian parameter kedua OFFSET ialah 1, menunjukkan bahawa bilangan baris asal titik asas adalah ke bawah Gerakkan satu kedudukan. ROW()=11, 11-11=0, dan parameter ketiga OFFSET ialah 0, menunjukkan bahawa bilangan lajur tidak diimbangi, jadi sel B11 merujuk kepada nilai sel A2 selepas titik asas A1 dianjak ke bawah.

★★★ Tarik ke bawah fungsi sel A11 untuk mengisinya Dalam sel A12, COLUMN()=1, 1-1=0 dan bilangan baris tidak diimbangi. ROW()=12, 12-11=1, parameter ketiga OFFSET ialah 1, menunjukkan bahawa nombor lajur diimbangi satu kedudukan ke kanan dari titik asas A1, dan merujuk kepada nilai sel B1 (sebab mengapa A1 dalam formula kami ialah Gunakan rujukan mutlak kerana semua sel kami adalah berdasarkan A1).

Secara analogi, apabila kami menggunakan tetikus untuk menarik ke bawah dan ke kanan untuk mengisi formula, kami menggunakan fungsi COLUMN dan ROW untuk membantu kami mencari offset setiap sel, sekali gus mencapai kesan transposisi.

Lagak ②: Simulasikan fungsi pertanyaan terbalik bagi fungsi Vlookup

Pertanyaan terbalik bagi fungsi VLOOKUP kebanyakannya dilengkapkan dengan bantuan tatasusunan , tetapi kerana tatasusunan, fungsi mungkin tersekat apabila terdapat sejumlah besar data, jadi ramai pelajar juga akan menggunakan fungsi INDEX sebagai gantinya. Jadi hari ini, mari kita memperkayakan pengetahuan semua orang Kami menggunakan fungsi OFFSET untuk menangani masalah jenis ini.

Fungsi sel C12:

=OFFSET($A,MATCH("D2568",$B:$B,0),)

Analisis fungsi:

Kami menggunakan sel A1 sebagai titik asas asal dan nilai yang perlu dikembalikan adalah sama seperti Titik asas asal berada dalam lajur yang sama, jadi kita hanya perlu mempertimbangkan offset baris bagi fungsi OFFSET, bukan ofset lajur. Oleh kerana nombor pekerja secara amnya adalah nilai unik, kami menggunakan fungsi MATCH untuk mendapatkan nombor siri nombor "D2568" dalam kawasan B2:B7 Nilai pulangan 4 digunakan sebagai offset baris bagi fungsi OFFSET dan dibawa ke dalam Fungsi OFFSET =OFFSET($A$1,4,). Jika offset lajur diabaikan, lalai ialah 0, dan jika lebar lanjutan dan ketinggian lanjutan diabaikan, lalai ialah 1 (iaitu, satu sel bukan sahaja sel A5?

Lagak ngeri ③: Versi tetapan semula data yang ditingkatkan - susun semula struktur data

Selepas memasukkan formula dalam kawasan F2:H2, Tarik ke bawah untuk mengisi data, dan anda akan mendapat jadual data satu dimensi di sebelah kanan. Masalah penyusunan semula data seperti ini sepatutnya menjadi perkara yang jarang berlaku dalam kerja sebenar! Jadi apakah kaedah yang akan dipilih oleh pelajar untuk menyelesaikannya? Sebaliknya, penulis merasakan idea tentang fungsi OFFSET adalah lebih ringkas dan jelas.

Analisis fungsi:

Langkah 1: Dapatkan nama berturut-turut

F2 sel fungsi:

=OFFSET($A,INT((ROW(F1)-1)/3)+1,)

Kerana terdapat tiga subjek secara keseluruhan, kita boleh menentukan bahawa nama yang sama perlu muncul tiga kali Kemudian apabila kita melepaskan fungsi pengisian sel F2, kita mesti memastikan bahawa mengimbangi baris fungsi OFFSET dan nilai parameter setiap tiga sel adalah sama. daripada. Di sini kita perlu mempunyai pemikiran matematik tentang "pembundaran pembahagi". , melalui Selepas penukaran INT((nombor siri-1)/3)+1, anda boleh mendapatkan urutan di sebelah kanan (jika terdapat 4 subjek, tukar 3 kepada 4, dan seterusnya). Letakkan nombor siri ini ke dalam parameter kedua fungsi OFFSET sebagai piawaian untuk baris offset, dan anda boleh mendapatkan kesan lajur nama kami.

Langkah kedua: Berikan akaun yang berbeza kepada orang yang sama

Oleh kerana setiap lajur kami dalam lajur F Setiap nama muncul tiga kali, yang menentukan bahawa tiga mata pelajaran bahasa Cina, matematik, dan bahasa Inggeris perlu disenaraikan secara berurutan dan kitaran Seperti langkah pertama, pemikiran matematik "pembahagi dan baki" digunakan untuk mencapai kesan.

=OFFSET($A,,MOD(ROW(G1)-1,3)+1)

Seperti yang ditunjukkan dalam rajah di atas, nombor siri ditukar oleh fungsi MOD untuk mendapatkan nombor siri berjujukan dan disenaraikan secara kitaran. Menggunakan nombor siri ini sebagai lajur parameter ketiga mengimbangi fungsi OFFSET, kandungan akaun data asal boleh diperoleh secara berurutan dan kitaran.

Langkah 3: Simulasikan fungsi INDEX melalui nama dan subjek, dan dapatkan gred daripada data asal

Fungsi sel H2:

=OFFSET($A,MATCH(F2,$A:$A,0),MATCH(G2,$B:$D,0))

Gunakan fungsi MATCH masing-masing untuk mendapatkan nombor jujukan yang sepadan dengan data dalam kawasan yang berkaitan, sebagai offset OFFSET, masukkannya ke dalam yang pertama Antara parameter kedua dan ketiga. Sel yang mengimbangi dari titik rujukan sel A1 ialah nilai gred yang kami perlukan.

Melalui kandungan di atas, kita dapati dengan mudah bahawa fungsi OFFSET sering digunakan bersama dengan fungsi MATCH. Oleh kerana fungsi Padanan boleh mencari nombor jujukan kata kunci dalam tatasusunan, kami sering menggunakan fungsi ini untuk menentukan offset bagi fungsi OFFSET.

3 Idea untuk aplikasi mewah

(prototaip templat laporan dinamik)

Kami menggunakan Excel dengan pantas Analisis data secara geostatistik dan ekstrak dengan cepat apa yang kami perlukan. Sekarang anggap dua senario berikut:

Senario 1:

Pemimpin telah mengatur kerja untuk mengira data jualan suku tertentu Kami mengambil tindakan serta-merta dan menggunakan fungsi untuk cepat membuat laporan ;

Senario 2:

Pemimpin mengatur kerja, kerana data jualan perlu dikira setiap suku tahun, jadi kami telah membuat templat dalam terlebih dahulu, bila ia akan diberikan. Laporan terpulang kepada kami. Ingat, jangan biarkan "pemimpin pertengahan" tahu bahawa anda sangat produktif.

Dua senario, pendekatan yang manakah akan anda pilih? Penulis harap ia adalah yang kedua.

Idea menentukan reka letak jadual kami Ini adalah kes mudah Apabila sumber data diubah suai, data suku tahunan yang sepadan akan dilaraskan secara automatik. Tidak semua kedudukan dalam templat kompleks akan menggunakan fungsi OFFSET, tetapi untuk keperluan merujuk kawasan data secara dinamik, adalah betul untuk menggunakan fungsi OFFSET untuk mengendalikannya.

4. Contoh penggunaan biasa

Lagak ngeri 4: Buat menu lungsur turun dinamik

Dalam data Semasa proses pemodelan, kami sering menggunakan menu lungsur turun (atau kawalan kotak kombo). Untuk memastikan keunikan kandungan lungsur turun, kami akan menggunakan fungsi "Taiwan Balm" INDEX+SMALL+IF+ROW untuk mengekstrak data pendua daripada tatasusunan. Adakah anda masih ingat contoh di mana fungsi OFFSET menggantikan fungsi INDEX yang kita bincangkan dalam artikel sebelum ini? Oleh itu, jika fungsi OFFSET boleh digunakan dan bukannya fungsi Indeks, maka fungsi OFFSET juga boleh mencapai proses "satu saiz untuk semua". Mari kita lihat proses penciptaan "menu lungsur" yang kompleks.

Langkah 1: Gunakan fungsi OFFSET untuk mengekstrak nilai unik formula "Taiwan Balm"

Formula ini agak panjang dan disenaraikan seperti berikut :

Fungsi sel D2:

=IFERROR(OFFSET($A,SMALL(IF(ROW($A:$A)-1=MATCH($A:$A,$A:$A,0),ROW(:),9^9),<br>ROW(D1)),),"")

Formula balsem harimau bukanlah topik yang akan kita bincangkan hari ini, jadi kami tidak akan menjelaskan secara terperinci . Yang penting adalah untuk memberitahu semua orang bahawa fungsi OFFSET juga boleh mencapai kesan duplikasi sedemikian.

Langkah 2: Gunakan fungsi OFFSET dalam pengurus nama untuk mewujudkan sumber data

Kita boleh menggunakan kombinasi kekunci Ctrl+F3 untuk membuka tetingkap pengurus nama, kemudian buat nama baharu dan tetapkan nama kepada " Kawasan", kedudukan rujukan ialah "D2:D15", seperti yang ditunjukkan dalam rajah berikut:

Kemudian pilih sel G1 dan tekan Alt+D+ L untuk membuka kotak Tetapan pengesahan data, pilih "Jujukan" dalam Benarkan, masukkan "=Kawasan" dalam Sumber, seperti yang ditunjukkan di bawah:

Klik butang OK, kemudian G1 kami sel Menu lungsur dibuat. Tetapi masalah juga timbul Kita akan mendapati bahawa terdapat banyak pilihan kosong, yang bukan apa yang kita perlukan.

Sesetengah pelajar akan mengatakan bahawa hanya pilih D2:D5 dalam pengurus nama. Ya, tetapi jika data baru muncul di kawasan lajur A, data akan berkurangan dalam menu lungsur, jadi kami masih menggunakan fungsi OFFSET untuk menangani masalah ini pada masa ini.

Tukar kedudukan rujukan "kawasan" dalam pengurus nama:

=OFFSET(动态下拉菜单!$D,1,,COUNTA(动态下拉菜单!$D:$D)-COUNTBLANK(动态下拉菜单!$D:$D),1)

Oleh kerana satu-satunya nilai lajur D kami diperoleh menggunakan formula, "sel kosong" di dalamnya bukan nominal "kosong", tetapi kosong yang diperolehi oleh formula, jadi ia tidak boleh terus melalui COUNTIF(D2:D15, "") untuk mendapatkan bilangan sel dengan nilai. Oleh itu, kami mula-mula menggunakan fungsi COUNTBLANK (mengira sel kosong) untuk mengira bilangan sel kosong, kemudian menggunakan fungsi COUNTA untuk mengira bilangan sel bukan kosong, dan akhirnya menolak kedua-duanya untuk mendapatkan bilangan sel dengan nilai. . Dengan menggunakan hasil yang diperoleh sebagai parameter keempat bagi fungsi OFFSET (bilangan baris yang dikembangkan dalam kawasan baharu), kesan merujuk data sah secara dinamik dicapai. Seperti yang ditunjukkan dalam rajah di bawah:

Jika nama wilayah baharu ditambahkan pada lajur A, pilihan baharu juga akan ditambahkan pada menu lungsur turun G1 melihat kesannya, saya percaya inilah yang anda perlukan.

Petua 5: Penggunaan fungsi OFFSET dalam carta

Saya percaya semua orang sudah biasa dengan carta di atas . Pelajar yang bekerja akan mempunyai pengalaman dalam membuat carta Pilih kawasan A1:B10 dalam gambar di atas, pergi ke bar alat - "Sisipkan" - Carta Lajur untuk melengkapkan kandungan legenda kami.

Jika kami memadamkan satu baris data, akan ada kurang satu legenda siri dalam carta lajur Walau bagaimanapun, jika kami menambah baris data, kami perlu menukar julat sumber data carta untuk memaparkan carta yang betul. Tetapi kami tidak boleh mengubahnya setiap kali, jika tidak, kami akan kehilangan niat asal untuk menggunakan Excel dengan cekap dan cepat.

Pada masa ini, kita masih boleh belajar daripada fungsi OFFSET untuk menyelesaikan masalah:

Langkah 1: Gunakan fungsi OFFSET untuk mencipta nama tersuai untuk "lajur tarikh" dan "lajur kuantiti " masing-masing

Pengurus nama, kami telah memperkenalkannya di atas, jadi saya tidak akan menerangkan secara terperinci. Pilih "Lajur Tarikh" dan tetapkannya seperti berikut:

Fungsi kedudukan rujukan:

=OFFSET(图表系列!$A,1,0,COUNTA(图表系列!$A:$A00),1)

kerana ia tidak wujud dalam data asal Sel kosong yang diperolehi oleh formula, jadi tidak perlu menggunakan fungsi Countblank di sini Hanya gunakan fungsi CountA untuk mengira bilangan sel bukan kosong dan gunakannya sebagai parameter keempat fungsi OFFSET (. bilangan baris dalam kawasan baharu). A2:A1000 di sini mewakili kawasan yang sangat besar, memastikan kandungan input yang baru berada dalam julat ini.

Pilih "Lajur Kuantiti" dan buat nama tersuai untuk kuantiti dengan cara yang sama, seperti berikut:

Langkah 2: Gunakan nama dalam kawasan carta

Ini adalah kunci kepada carta dinamik OFFSET Kedudukan tempat anda menambah nama adalah sangat penting.

Dalam kawasan lukisan, pilih mana-mana lajur, dan dalam bar edit anda boleh melihat penulisan fungsi ikon (adakah ini kali pertama anda tahu bahawa carta juga mempunyai fungsi). Mari kita ubah suai skop rujukan di sini.

Kami hanya perlu menukar rantau.

• Siri Carta!$A$2:$A$10

• Siri Carta!$B$2:$B$10

Hanya gantikan dua bahagian merah ini dengan nama tersuai Jangan gantikan "Siri Carta!$A$2:$A$10" secara keseluruhan!

Selepas menggantikan, tekan Enter, fungsi akan dipaparkan seperti yang ditunjukkan di atas, OFFSET.xlsx ialah nama buku kerja kami. Kesannya adalah seperti berikut:

Jenis carta lain juga tersedia. Anda boleh cuba mengendalikannya untuk memperdalam kesan anda.

【Nota Editor】

Lima parameter fungsi OFFSET tidak sukar diingat jika anda memahami maksudnya. Nilai pulangannya boleh digunakan sebagai rujukan kepada fungsi lain Begitu juga, fungsi lain yang "nilai pulangannya dalam format berangka" juga boleh digunakan sebagai parameter fungsi OFFSET, supaya data kita boleh bergerak dengan sendirinya.

Fungsi ini memainkan peranan penting dalam fungsi Excel Terutama apabila kita perlu menggunakan pemodelan Excel, fungsi ini sering digunakan untuk merujuk kawasan dinamik dan memproses data secara automatik lebih banyak masa untuk belajar, ia akan memberi manfaat besar kepada proses pembuatan jam tangan anda pada masa hadapan.

Cadangan pembelajaran berkaitan: tutorial cemerlang

Atas ialah kandungan terperinci Pembelajaran fungsi Excel: Mari kita bercakap tentang raja statistik dinamik OFFSET(). Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!