Cara Menggunakan Fungsi Referensi di Excel 2016 - dummies

Fungsi referensi 2016 pada daftar tombol daftar perintah Cari dan Referensi pada Formula Tab dari Ribbon dirancang untuk menangani secara khusus berbagai aspek referensi sel di lembar kerja. Kelompok fungsi ini mencakup:

ADDRESS untuk mengembalikan referensi sel sebagai entri teks di sel lembar kerja
AREAS untuk mengembalikan jumlah bidang dalam daftar nilai ( area yang didefinisikan sebagai rangkaian sel bersebelahan atau sel tunggal dalam referensi sel)
COLUMNS untuk mengembalikan jumlah kolom dalam referensi
FORMULATEXT untuk mengembalikan rumus yang diacu sebagai string teks > GETPIVOTDATA untuk mengembalikan data yang tersimpan dalam tabel pivot Excel
HYPERLINK untuk membuat tautan yang membuka dokumen lain yang tersimpan di komputer, jaringan, atau Internet Anda (Anda juga dapat melakukan ini dengan perintah Insert → Hyperlink)
< ! --2 ->
TIDAK LANGSUNG untuk mengembalikan referensi sel yang ditentukan oleh string teks dan membawa isinya ke sel yang merujuk ke sel itu
LOOKUP untuk mengembalikan nilai dari array
OFFSET ke kembalikan rujukan ke kisaran sel yang ditentukan oleh jumlah baris dan kolom dari sel atau kisaran sel
ROW untuk mengembalikan nomor baris rujukan sel
ROWS untuk mengembalikan jumlah baris dalam rentang sel atau array
RTD untuk mengembalikan data real-time dari server yang menjalankan program yang mendukung otomasi COM (Component Object Model)
TRANSPOSE untuk mengembalikan array vertikal sebagai array horizontal dan sebaliknya
Dapatkan kurus pada kolom dan baris
Fungsi COLUMNS dan ROWS mengembalikan jumlah kolom dan baris dalam rentang sel tertentu atau array. Misalnya, jika Anda memiliki rentang sel di spreadsheet bernama product_mix, Anda dapat mengetahui berapa banyak kolom yang dikandungnya dengan memasukkan rumus ini:

= COLUMNS (product_mix)

Jika Anda ingin mengetahui berapa banyak baris rentang ini gunakan, Anda kemudian masukkan rumus ini:

= ROWS (product_mix)

Anda dapat menggunakan fungsi COLUMNS dan ROWS bersama-sama untuk menghitung jumlah sel dalam rentang tertentu. Misalnya, jika Anda ingin mengetahui jumlah sel yang tepat yang digunakan dalam kisaran sel produk_mix, Anda membuat rumus perkalian sederhana berikut dengan menggunakan fungsi COLUMNS dan ROWS:

= COLUMNS (product_mix) * ROWS (product_mix) > Jangan membingungkan fungsi COLUMNS (jamak) dengan fungsi COLUMN (singular) dan fungsi ROWS (jamak) dengan fungsi ROW (singular).Fungsi COLUMN mengembalikan jumlah kolom (seolah Excel menggunakan sistem referensi R1C1) untuk referensi sel yang Anda tentukan sebagai satu-satunya argumennya. Demikian juga, fungsi ROW mengembalikan jumlah baris untuk referensi sel yang Anda tentukan sebagai argumennya.

Transposing cell range

Fungsi TRANSPOSE memungkinkan Anda mengubah orientasi rentang sel (atau array). Anda dapat menggunakan fungsi ini untuk mentranspos rentang sel vertikal tempat data mengalir deretan kolom yang berdekatan ke satu tempat data melintasi kolom baris yang berdekatan dan sebaliknya. Untuk berhasil menggunakan fungsi TRANSPOSE, Anda tidak hanya harus memilih rentang yang memiliki jumlah kolom dan baris yang berlawanan, namun Anda juga harus memasukkannya sebagai formula array.

Misalnya, jika Anda menggunakan fungsi TRANSPOSE untuk mentranspos rentang sel 2 x 5 (yaitu kisaran yang mengambil dua baris yang berdekatan dan lima kolom yang berdekatan), Anda harus memilih rentang sel 5 x 2 kosong. (yaitu kisaran yang membutuhkan lima baris yang berdekatan dan dua kolom yang berdekatan) di lembar kerja sebelum Anda menggunakan tombol Insert Function untuk memasukkan fungsi TRANSPOSE di sel pertama. Kemudian, setelah memilih rentang sel 2 x 5 yang berisi data yang ingin Anda transposasikan di kotak teks Array pada kotak dialog Function Arguments, Anda perlu menekan Ctrl + Shift + Enter untuk menutup kotak dialog ini dan memasukkan fungsi TRANSPOSE ke seluruh rentang sel yang dipilih sebagai formula array (dilapisi dengan kurung kurawal).

Misalkan Anda ingin mentranspos data yang dimasukkan ke dalam kisaran sel A10: C11 (array 2 x 3) ke kisaran sel kosong E10: F12 (array 3 x 2) dari lembar kerja. Saat Anda menekan Ctrl + Shift + Enter untuk menyelesaikan formula array, setelah memilih kisaran sel A10: C11 sebagai argumen

array, Excel akan menempatkan formula array berikut di setiap sel dari kisaran:

{ = TRANSPOSE (A10: C11)} Gambar berikut menggambarkan penggunaan fungsi TRANSPOSE. Rentang sel B2: C4 berisi array 3 x 2. Untuk mengonversi array 3 x 2 ini dalam kisaran sel B2: C4 ke array 2 x 3 di kisaran B6: D7, ikuti langkah-langkah ini: Pilih rentang sel kosong B6: D7 di lembar kerja.

Klik tombol perintah Lookup & Reference pada tab Rumus Ribbon lalu pilih opsi TRANSPOSE dari menu drop-down tombol.

sisipan Excel = TRANSPOSE () pada Formula bar dan buka kotak dialog Function Arguments dimana kotak teks argumen Array dipilih.

Seret melalui rentang sel B2: C4 di lembar kerja sehingga kotak teks argumen Array berisi B2: C4 dan formula pada bilah Formula sekarang dibaca = TRANSPOSE (B2: C4).
Tekan Ctrl + Shift + Enter untuk menutup kotak dialog Insert Arguments (jangan klik OK) dan masukkan formula array TRANSPOSE ke kisaran sel B6: D7 seperti yang ditunjukkan di sini.

Menggunakan fungsi TRANSPOSE untuk mengubah orientasi array sederhana.
Mengeklik tombol OK di kotak dialog Function Arguments memasukkan fungsi TRANSPOSE ke sel aktif dari pemilihan sel saat ini.Melakukan ini mengembalikan #VALUE! nilai error ke sel. Anda harus ingat untuk menekan Ctrl + Shift + Enter untuk menutup kedua kotak dialog dan memasukkan formula ke dalam keseluruhan rentang sel.
Jika semua yang ingin Anda lakukan adalah mentranspos judul baris dan kolom atau tabel data sederhana, Anda tidak perlu melewati omong kosong untuk menciptakan formula array menggunakan fungsi TRANSPOSE. Cukup salin rentang sel yang akan dialihkan dengan tombol perintah Salin pada tab Home dari Ribbon. Posisikan kursor sel di sel kosong pertama dimana rentang yang ditransisi ditempelkan sebelum Anda mengklik opsi Transpose pada menu drop-down tombol Tempel.