Anda mungkin tidak tahu cara menggunakan fungsi OFFSET dalam Excel

Tahukah anda bahawa fungsi OFFSET dalam Microsoft Excel boleh membantu anda melakukan banyak perkara lain, bukan hanya rujukan sel? Di bawah ialah cara unik untuk menggunakan OFFSET dalam Excel yang mungkin anda tidak tahu.

Fungsi OFFSET Microsoft Excel ialah alat yang fleksibel dan berkuasa. Oleh kerana kerumitan dan bilangan fungsi yang serupa, malangnya, fungsi Excel ini kurang digunakan.

Fungsi yang agak mudah ini dapat membantu pengguna menjimatkan masa dan usaha. Ia memudahkan mendapatkan titik data yang tepat daripada set data induk yang disusun dengan baik. Ia juga membenarkan penciptaan ringkas laporan terperinci, visualisasi interaktif dan papan pemuka yang kompleks.

Buat set data bersaiz tersuai

Salah satu kegunaan paling biasa bagi fungsi OFFSET dalam Excel adalah untuk mengumpul set data boleh saiz semula. Dengan set data yang besar, seseorang mungkin ingin mempertimbangkan hanya sebahagian kecil data pada bila-bila masa.

Contohnya, untuk menyediakan laporan automatik jumlah jualan pekerja, anda perlu mendapatkan jumlah tersebut untuk minggu atau minggu tertentu.

Menulis fungsi SUM yang berbeza untuk setiap jumlah boleh menjadi agak mudah. Walau bagaimanapun, menggunakan fungsi OFFSET akan lebih mudah digunakan dan lebih cepat untuk digunakan semula.

Mula-mula, masukkan tarikh yang anda mahu mulakan dalam sel B6. Kemudian, masukkan bilangan hari yang anda mahu jalankan laporan dalam B7. Seterusnya, gunakan fungsi SUM dan AVERAGE mudah untuk mendapatkan lebih banyak data bagi setiap pekerja sepanjang tempoh yang dipilih:

=SUM(OFFSET(B2, 0, B6 - 1, 1, B7))

=AVERAGE(OFFSET(B2, 0, B6 - 1, 1, B7)

Melaraskan tarikh atau masa mula adalah semudah mengedit sel, tetapi anda boleh menyeret dan mengubah saiz kumpulan data dengan lebih mudah.

Membenarkan membuat laporan tersuai

Gunakan kaedah yang sama seperti di atas untuk membuat laporan lengkap dengan menyesuaikan pembolehubah dengan agak mudah. Anda boleh membuat hujah pertama dalam fungsi OFFSET merujuk helaian lain. Ini membolehkan anda memisahkan kemasukan data dan pelaporan.

Tindakan di atas juga memungkinkan untuk menyeret kepingan data yang dipilih dari satu lembaran kerja ke yang lain. Selain itu, ia membenarkan anda memaparkan hanya maklumat yang berkaitan dengan laporan semasa. Menyediakan laporan yang menunjukkan jumlah harian menggunakan data yang sama dari sebelumnya agak mudah. Anda boleh melakukan ini tanpa mendapatkan semula data untuk tarikh yang tidak berkaitan.

Fungsi offset berikut diletakkan dalam sel B5 akan mendapatkan semula bilangan hari yang dinyatakan dalam B2. Ia bermula pada minggu yang dipilih dalam B1 dan hanya menunjukkan data untuk tempoh yang berkaitan:

=OFFSET(Sheet1!B2, 0, (B1 - 1) * 7, 1, B2 * 7)

Adalah mungkin untuk mengehadkan entri dalam B1 dan B2 dan membuat senarai lungsur turun dalam Excel dengan minggu yang tersedia menggunakan pengesahan data.

Penomboran hamparan

Fungsi OFFSET juga boleh membolehkan penomboran hamparan mudah, tugas yang sering dikaitkan dengan menggunakan tab Pembangun Excel. Ini boleh menjadikan pemprosesan kumpulan data yang besar lebih mudah dan lebih berkesan.

Apabila bekerja dengan hamparan yang mengandungi sehingga 30,000 baris data, mungkin sukar untuk melihat semua data sekaligus. Buat menu lungsur turun yang menerima julat nombor dari 1 hingga 300, membolehkan anda memisahkan data dengan cepat mengikut halaman dengan 100 hasil setiap halaman.

Fungsi OFFSET sentiasa mempunyai titik sauhnya diletakkan di dalam sel data atas. Walau bagaimanapun, hujah kedua ialah offset menegak. Nilai ini sama dengan nombor halaman yang dipilih tolak 100. Offset adalah mendatar, argumen ketiga boleh ditetapkan kepada 0.

Argumen ke-4 ditetapkan kepada 100, argumen ke-5 ialah bilangan lajur dalam set data. Kaedah ini menjadikan pemisahan & pengurusan jadual data amat mudah.

Di bawah ialah contoh data bernombor. Ia termasuk 500 maklumat yang direkodkan daripada penderia dalam satu helaian dan laporan bernombor pada helaian lain.

Laporan menggunakan pengesahan data dalam sel B1 untuk mencipta menu lungsur dengan halaman di dalamnya. Ungkapan berikut dalam sel A3 mencipta badan laporan:

=OFFSET(Sheet1!A2, (B1 - 1) * 100, 0, 100, 21)

Di atas ialah beberapa cara untuk menggunakan fungsi offset dalam Excel yang hanya segelintir orang tahu tetapi ia sangat berguna, terutamanya apabila anda perlu memproses sejumlah besar data.

Semoga artikel itu berguna kepada anda.