Isi
- Menggunakan Fungsi OFFSET dalam Formula di Excel 2007 dan Excel 2010
- Kapan Menggunakan Angka Positif dan Negatif untuk Baris dan Kolom dalam rumus OFFSET
- Menggunakan OFFSET Dengan COUNTA dalam Formula untuk Membuat Rentang Dinamis di Excel 2007 dan Excel 2010
- Rentang Dinamis di Excel
- Dan akhirnya....
Robbie kebanyakan menulis tentang Skyrim tetapi juga kadang-kadang menjelaskan keanehan aplikasi Microsoft seperti Excel dan Outlook.
Pada artikel ini, kita akan melihat sesuatu yang agak keren dan sesuatu yang saya butuhkan di Excel Spreadsheets saya untuk beberapa waktu.
Bayangkan jika Anda mau, spreadsheet tempat Anda mencatat faktur, penjualan, web hit, dll, dan kemudian JUMLAH mereka untuk memberi diri Anda total berjalan. Setiap kali Anda menambahkan baris ekstra ke spreadsheet Anda, Anda harus memperbarui JUMLAH rumus untuk memungkinkan item tambahan.
Nah sekarang tidak lagi! Selamat datang di rentang dinamis menggunakan MENGIMBANGI dan COUNTA fungsi. Menggunakan fungsi ini, setiap kali baris ditambahkan (atau dihapus) dan juga setiap kali sel yang disalin dimasukkan, file JUMLAH formula akan secara otomatis dan dinamis diperbarui untuk mencerminkan ini.
Menggunakan Fungsi OFFSET dalam Formula di Excel 2007 dan Excel 2010
Itu MENGIMBANGI fungsi memungkinkan Anda untuk meminta Excel untuk memberi tahu Anda konten sel (atau rentang sel) sejumlah baris dan kolom dari sel referensi (atau sel).
Jika Anda menggunakan MENGIMBANGI dengan sendirinya, ini akan mengembalikan nilai sel yang Anda rujuk. Kami akan mulai dengan itu sebagai contoh:
= OFFSET (B3,1,1,1,1)
Rumusnya terdiri dari bagian-bagian berikut:
- Sel tempat Anda meminta Excel untuk memulai (dalam hal ini B3) (dikenal oleh Excel sebagai Referensi)
- Bagian selanjutnya dari rumus ini adalah bilangan Baris (yang bisa naik atau turun) dari sel referensi ke sel yang Anda lihat. Kami tertarik pada sel C4 jadi nilainya 1 karena satu baris di bawah dari sel B3.
- Selanjutnya, kita perlu tahu berapa jumlahnya Kolom dari awal hingga sel referensi. Ini adalah 1 lagi karena ini adalah satu kolom dari B3 ke C4
- Elemen keempat dari rumus ini adalah Tinggi. Ini adalah jumlah baris yang Anda inginkan untuk mengembalikan data.
- Akhirnya, kami punya Lebar atau jumlah kolom yang Anda inginkan datanya.
catatan:Baris dan Kolom bisa berupa angka negatif atau positif. Silakan lihat tabel di bawah ini untuk memahami kapan harus menggunakan nilai positif atau negatif.
Kapan Menggunakan Angka Positif dan Negatif untuk Baris dan Kolom dalam rumus OFFSET
| Baris | Kolom |
Positif | Di bawah Referensi | Hak Referensi |
Negatif | Referensi Di Atas | Referensi Kiri |
catatan:Tinggi dan Lebar keduanya harus positif. Jika tidak ditentukan, Excel akan menggunakan tinggi dan lebar referensi.
catatan: Jika Anda mendapatkan #VALUE, Anda biasanya meminta Excel untuk menampilkan sesuatu yang tidak dapat ditampilkan.
Untuk mengilustrasikan ini:
Jika saya menggunakan rumus berikut:
= OFFSET (B3,1,1,2,1)
Saya meminta Excel untuk menampilkan hasil satu sel dalam dua sel yang tidak dapat dilakukan sehingga mengembalikan kesalahan.
catatan: Jika Anda mendapatkan kesalahan #REF Anda telah keluar dari tepi lembar kerja Anda.
Rumus ini menghasilkan kesalahan #REF, karena tidak ada baris empat baris di atas B3.
= OFFSET (B3, -4,2,1,1)
Menggunakan OFFSET Dengan COUNTA dalam Formula untuk Membuat Rentang Dinamis di Excel 2007 dan Excel 2010
Sekarang setelah kita memahami file MENGIMBANGI fungsi dan cara menggunakannya dalam rumus, kita sampai ke bagian yang sangat menarik dari artikel ini; kami dapat membuat rentang dinamis menggunakannya bersama COUNTA. Kalau begitu kita bisa JUMLAH nilai dalam rentang dinamis dan rumus ini akan secara otomatis menghitung ulang jika sel ditambahkan, dihapus atau isinya berubah. Tentu saja, JUMLAH hanya contoh yang saya pilih untuk artikel ini, Anda dapat menggunakan RATA-RATA, MAKS atau fungsi lain yang Anda rasa sesuai dengan kebutuhan Anda di samping itu MENGIMBANGI dan COUNTA fungsi.
Pertama, mari kita lihat dengan cepat COUNTA lakukan untuk kita dalam contoh ini:
Ini hanya menghitung jumlah sel yang tidak kosong antara sel awal dan akhir (dalam hal ini ada 29 sel tidak kosong antara sel C3 dan sel C32).
= COUNTA (C3: C32)
Sekarang, kami menyertakan file COUNTA rumus di atas sebagai bagian dari kami MENGIMBANGI rumus untuk membuat rentang dinamis. Rumus akhirnya adalah:
= SUM (OFFSET (C3,0,0, COUNTA (C3: C32), 1))
Kami akan mulai dengan melihat file MENGIMBANGI bagian dari rumus:
OFFSET (C3,0,0, COUNTA (C3: C32), 1)
- Kami mulai di sel C3 sebagai milik kami Referensi
- Jumlah Baris kami meminta Excel untuk pindah dari sel awal C3 adalah 0
- Begitu juga dengan jumlah Kolom juga 0 (Saya suka menambah dan menghapus kolom ke data saya jadi menyimpannya di 0 berarti rumus tidak akan pernah rusak)
- Sekarang sampai pada bagian dinamis dari seluruh rumus. Untuk Tinggi, kami memberi tahu Excel bahwa tinggi adalah hasil dari rumus COUNTA yang saat ini 29. Jika kita menambah atau menghapus baris, ini akan menyesuaikan secara otomatis!
- Terakhir, kami menyarankan Excel dari Lebar, yang dalam hal ini 1 kolom.
Sekarang kita sudah memahami file MENGIMBANGI bagian dari rumus yang kami tambahkan JUMLAH berfungsi untuk melengkapi rumus keseluruhan yang memberi kita:
= SUM (OFFSET (C3,0,0, COUNTA (C3: C32), 1))
Jika kita sekarang Memasukkan baris kosong tambahan Anda akan melihat bahwa Total Penjualan turun 18 (yang merupakan nilai di sel C32 yang merupakan baris terakhir) dari 1.085 pada gambar di atas menjadi 1.067 pada gambar di bawah.
Sekarang saya menambahkan nilai ke dalam baris kosong dan Total Penjualan sekarang sudah benar dan menyertakan nilai di C32.
catatan: Memasukkan baris kosong ke dalam rentang dinamis Anda akan mengarah ke hasil yang ditampilkan di JUMLAH rumus tidak benar.
catatan: Jika Anda menambahkan baris tambahan, misalnya dengan Memasukkan Sel yang Disalin Excel hanya akan meningkatkan COUNTA bagian dari rumus yang membuatnya dinamis.
Sebagai contoh, saya menambahkan empat baris ke spreadsheet saya dan Excel memperbarui rumus saya secara otomatis!
= SUM (OFFSET (C3,0,0, COUNTA (C3: C36), 1))
Rentang Dinamis di Excel
Kemampuan untuk memiliki rentang dinamis dalam spreadsheet Excel Anda yang berisi data seperti penjualan, faktur, web hits dll yang kemudian dapat ditambahkan atau dirata-ratakan adalah hal yang sangat berguna. Ini berarti Anda tidak lagi harus memperbarui rumus Anda secara manual setiap kali Anda membuat penambahan atau penghapusan pada data Anda.
Untuk mencapai ini:
- Kami mulai dengan menggunakan MENGIMBANGI berfungsi dalam rumus. Fungsi ini memungkinkan Anda untuk memulai dengan sel referensi (atau sel) dan meminta Excel untuk menampilkan konten area lain sejumlah baris dan kolom dari sel referensi
- Selanjutnya, kami menambahkan COUNTA berfungsi untuk menyediakan Tinggi dari area untuk MENGIMBANGI. Setiap kali sel ditambahkan atau dihapus dari rentang, file COUNTA bagian rumus diperbarui secara otomatis.
- Dengan menambahkan JUMLAH fungsi ke rumus ini, sekarang kita dapat menambahkan sel yang telah ditampilkan Excel melalui MENGIMBANGI.
Saat semua fungsi ini ditambahkan ke rumus, sekarang akan secara dinamis menjumlahkan rentang sel, memperbarui secara otomatis saat data ditambahkan atau dihapus dari rentang.Bagi saya ini adalah keindahan dan kekuatan Excel, fakta bahwa Anda dapat menambahkan dua fungsi bersama dan membuat sesuatu yang tidak hanya sangat berguna, tetapi juga lebih dari sekadar jumlah bagian-bagiannya.
Saya harap Anda menemukan artikel ini bermanfaat dan informatif dan bermanfaat MENGIMBANGI akan mengubah spreadsheet Anda dengan cara yang sama seperti mengubah spreadsheet saya. Terima kasih banyak telah membaca, silakan tinggalkan komentar apa pun yang mungkin Anda miliki di bawah.
Dan akhirnya....
Artikel ini akurat dan benar sepanjang pengetahuan penulisnya. Konten hanya untuk tujuan informasi atau hiburan dan tidak menggantikan nasihat pribadi atau nasihat profesional dalam masalah bisnis, keuangan, hukum, atau teknis.
