Bagaimana cara mengunci rumus f4 di excel?

Menggunakan Microsoft Excel adalah cara terbaik untuk menghasilkan kalkulasi dengan cepat pada data internal Anda, tetapi hal-hal dapat membuat frustasi dengan sangat cepat

Hal pertama yang saya pelajari di Excel adalah mengklik sebuah sel, mengklik plus dan mengklik sel lain dan totalnya. Kemudian saya bisa menyeretnya ke bawah daftar, itu mudah, pikir saya bagus

Namun, satu hal yang awalnya saya perjuangkan pada beberapa hari pertama saya, adalah meringkas informasi tetapi kemudian perlu menghitung sesuatu yang selalu berada di sel yang sama seperti tarif pajak. Di sinilah referensi sel absolut masuk

Tombol F4 / Menggunakan tanda dolar. $

Sedikit informasi ini adalah kuncinya, jika Anda mengklik sesuatu yang akan selalu berada di sel yang sama, tetapi yang lainnya ada di sel berurutan bergerak ke bawah atau ke seberang lalu tekan tombol F4. Itu akan segera memasukkan dolar ke sel dalam rumus yang merupakan tanda ia telah mengunci referensi dengan sel itu

Sebagai contoh. Jika Anda memiliki tarif pajak di sel A1, itu akan mengubahnya menjadi $A$1 saat Anda menekan F4

Mari kita tonton video untuk pemahaman yang lebih baik

Pertama, ini akan membawa Anda melalui referensi sel relatif yang biasa kita semua gunakan saat excel mengisi rumus untuk Anda. Kemudian itu akan membawa Anda melalui referensi sel absolut dari 1 menit 45 detik

Informasi Lebih Lanjut

Menggunakan Microsoft Excel atau aplikasi spreadsheet lainnya seperti Google Sheets dapat menjadi manfaat besar untuk pekerjaan Anda sehari-hari yang memungkinkan Anda mengatur informasi dengan cepat, meringkas angka, dan bereaksi terhadap perubahan pola data di dalam bisnis Anda. Di sini, di Bowe, kami selalu hanya berjarak satu panggilan telepon untuk membantu dalam situasi di mana Anda mungkin memerlukan saran. Hubungi kami di sini untuk mengetahui apakah kami dapat membantu

Jika Anda bertanya-tanya mengapa Anda bahkan ingin menggunakan tabel data alih-alih hanya mengatur sel dengan baik di Excel, mungkin perlu penyegaran cepat…

Tabel data di Excel berguna khususnya karena tumbuh dan berubah untuk menerima data baru saat ditambahkan ke tabel, tanpa perlu memperbarui rumus atau referensi. Ini sangat berharga ketika Anda ingin menyalin dan menempel data ke dalam spreadsheet dari sumber eksternal agar tetap mutakhir. Di tabel data, Anda bisa membuat kolom terhitung yang mereferensikan bagian lain dari tabel data. Saat data baru ditambahkan, kolom terhitung akan secara otomatis diperbarui dengan informasi baru dan baris baru

Rumus ringkasan seperti

3167

3167

3167

3167

3167

Fleksibilitas tabel data menjadikannya pilihan pertama untuk bekerja dengan informasi terstruktur, jadi itu artinya kita perlu mempelajari cara bekerja dengannya. Baca terus untuk mengetahui caranya

Cara Kerja Referensi Tabel Data Normal

Referensi Kolom Tabel Data

Saat Anda bekerja dengan tabel data di Excel, referensi sel terlihat sedikit berbeda dari kombinasi huruf-angka A1 biasa untuk baris-kolom. Ini karena setiap baris tabel bertindak seperti berada di spreadsheet 1 barisnya sendiri. Itu artinya referensi hanya perlu mengacu pada nama tabel dan kolom. Baris diasumsikan sebagai dirinya sendiri

Mari kita lihat tabel sederhana

=SUM(Table1[Sales])

Output untuk formula ini adalah

3167

Perhatikan bahwa itu tidak meminta baris awal atau akhir. Itu hanya meminta nama kolom dan tabel

Sekarang, mari tambahkan data ke tabel

=SUM(Table1[Sales])

Namun, sekarang, keluaran untuk rumus ini telah diperbarui secara otomatis

6450

Tanpa melakukan apapun, kalkulasi menampung data tambahan. Tidak perlu mengubah referensi dalam rumus SUM()

Contoh Tabel Data Dengan Beberapa Referensi

Mari tunjukkan satu lagi contoh rumus menggunakan tabel yang sama

=SUMIFS([Sales],[Region],[@Region])

Seharusnya terlihat seperti ini

Perhatikan bahwa istilah terakhir – kriteria untuk SUMIFS() – menggunakan

=SUM(Table1[Sales])

Perhitungan SUMIF() menjumlahkan semua Penjualan untuk setiap Wilayah, memberikan pencocokan total untuk setiap baris dari wilayah Utara (dan juga untuk masing-masing wilayah lainnya)

Sekarang Anda bisa melihat seberapa kuat tabel data Excel, Anda mungkin ingin menggunakannya… Untuk membangun formula kompleks dengan cepat dan andal menggunakan tabel data, Anda harus bisa mengunci referensi, jadi itulah yang akan kita pelajari selanjutnya

Cara Membuat Referensi Penguncian

Biasanya, mudah untuk membuat kolom dan baris dengan mengunci referensi dalam formula menggunakan F4 atau menambahkan tanda dolar ke baris dan kolom referensi… Hal ini memungkinkan Anda menyeret formula ke bawah beberapa baris atau melintasi beberapa kolom untuk membuat spreadsheet yang berfungsi dengan cepat

Contoh Referensi Sel Relatif dan Absolut (Terkunci) Normal

Ambil contoh sederhana ini

Di sini, kami membuat rumus untuk menghitung pendapatan triwulanan berdasarkan volume dan harga satuan. Volume akan berubah setiap kuartal di kolom baru, tetapi harga satuan akan tetap sama, jadi kami mengunci referensi dengan menekan F4 atau mengetik tanda dolar untuk

=SUM(Table1[Sales])

Setelah rumus dimasukkan, klik di sudut kanan bawah untuk menyeretnya melintasi kolom untuk kuartal lainnya

Saat kami melepaskan klik, rumus untuk setiap kuartal dihitung. Referensi sel untuk sel volume (berwarna biru) tetap menjadi referensi "relatif" yang bergerak dengan lokasi sel rumus. Referensi untuk harga unit (berwarna merah), bagaimanapun, telah menjadi "mutlak", yang berarti dikunci ke lokasi aslinya

Membuat Referensi Mutlak (Mengunci) di Tabel Data

Sayangnya, menggunakan F4 atau menambahkan tanda dolar tidak mengunci referensi di tabel data. Itu bahkan tidak menghitung sel, malah membuat kesalahan. Untungnya, ada cara untuk mencapai perilaku yang sama, tetapi memerlukan penggunaan sintaksis tabel data Excel yang telah kita bicarakan di atas

Cara termudah untuk menunjukkan bagaimana hal ini dilakukan adalah melalui contoh. Mari gabungkan dua contoh struktur data yang telah kita kerjakan untuk merancang masalah yang akan dipecahkan di bagian berikutnya. Klik Berikutnya untuk melanjutkan

Cara Membuat Referensi Penguncian di Tabel Data

Contoh data

Mari kita perluas tabel Penjualan Regional kita menjadi periode 6 bulan penuh. Ini mungkin tampilan data Anda jika Anda mengimpornya dari database atau perangkat lunak pelaporan lainnya. Ini sangat dapat dibaca oleh mesin tetapi tidak dapat dibaca oleh manusia. Dalam contoh ini, kita akan menamai tabel di Excel sebagai

=SUM(Table1[Sales])

Sekarang, mari buat tabel untuk mengubah data menjadi format yang lebih mudah dibaca. Dalam hal ini, tujuan kami adalah memiliki kolom terpisah untuk setiap bulan dan baris terpisah untuk setiap wilayah

Tabel ini akan disebut

=SUM(Table1[Sales])

Membangun Rumus yang Mengacu pada Kolom dan Baris Tabel Data Terkunci

Mulai dari kolom Utara dan baris Jan-16, buat rumus ini

=SUMIFS(DataTable[[Sales]:[Sales]],DataTable[[Region]:[Region]],B$1,DataTable[[Month]:[Month]],PivotTable[@[Month]:[Month]])

Ini akan terlihat seperti ini

Kami mengunci referensi dalam tiga cara terpisah dalam rumus ini

1. Merujuk ke seluruh kolom yang terkunci di tabel data
2. Merujuk ke baris saat ini dari kolom terkunci di tabel data
3. Mengacu pada baris header yang dikunci dari tabel data

Masing-masing menggunakan sintaks yang sedikit berbeda, jadi saya akan menunjukkannya masing-masing secara berurutan

1. Mengunci Seluruh Kolom Tabel Data

Referensi kolom tabel data normal terlihat seperti ini saat Anda berada di dalam tabel yang sama

[Sales]

Saat Anda berada di luar tabel tempat kolom berada, mereka terlihat seperti ini

DataTable[Sales]

Trik untuk mengunci referensi kolom melibatkan memperlakukannya seperti rentang kolom yang hanya menyertakan kolom itu sendiri. Anda juga harus selalu mereferensikan nama tabel, bahkan dari dalam tabel. Jadi, sintaks kolom tabel data terkunci terlihat seperti ini

DataTable[[Sales]:[Sales]]

2. Merujuk Baris Saat Ini dari Kolom Tabel Data Terkunci

Referensi baris normal saat ini untuk tabel data terlihat seperti ini saat Anda berada di dalam tabel yang sama

[@Month]

Saat Anda berada di luar tabel tempat kolom berada, mereka terlihat seperti ini

3167

Mirip dengan mengunci seluruh kolom, seperti yang terlihat di atas, trik untuk mengunci referensi baris saat ini adalah memperlakukannya seperti rentang, tetapi simbol @ hanya digunakan di awal referensi rentang

3167

3. Mengunci Referensi Header Tabel Data

Bekerja dengan header tabel data Excel agak sulit karena Excel memperlakukannya seperti elemen tetap. Itu berarti tidak akan mengingat bahwa itu adalah tanggal atau jenis data khusus lainnya. Mereka menjadi nama bidang. Asumsinya adalah jumlah kolom dalam tabel data Anda tidak akan berubah seiring waktu – hanya jumlah baris

Ini berarti bahwa untuk mereferensikan header dalam tabel data dalam rumus (dan membiarkan rumus tersebut tetap dapat ditarik), Anda perlu mengunci referensi. Untungnya, baris header tabel data selalu berada di tempat yang sama, sehingga Anda dapat menguncinya seperti referensi sel biasa di Excel. Dalam contoh rumus di atas, itulah yang kami lakukan

3167

Notasi ini memastikan bahwa ketika rumus diseret, itu akan mereferensikan Baris 1 di kolom saat ini setiap saat. (Nomor baris dikunci, tetapi kolomnya masih relatif. )

Rumus Tabel Data Lengkap

Setelah menyeret rumus ke bawah dan melintasi tabel data PivotTable baru, Anda akan melihat persamaan berubah secara dinamis untuk mengisi sel lainnya

Rumus baru di sel E7 berbunyi

3167

Dilakukan dengan benar, rumus terlihat sangat mirip di setiap sel tabel data. Satu-satunya hal yang akan berubah adalah referensi ke baris header tabel data, karena ini adalah referensi penguncian normal untuk sel Excel

Ini adalah tabel data final, lengkap dengan informasi pivot. Ini jauh lebih mudah dibaca, dan dapat digunakan untuk membuat bagan dan hasil pelaporan lainnya jika diperlukan

Dapatkan Add-In Excel untuk Memetakan "Mengunci Referensi Tabel Data" ke F4

Proses menambahkan rentang ke semua referensi tabel data Anda untuk membuatnya absolut mungkin merepotkan jika Anda harus sering melakukannya… Untungnya, Jon Acampora di Excel Campus telah membuat add-in yang akan memetakan rentang penguncian ini ke keyboard

Anda dapat melihat add-in-nya di sini. Referensi Terstruktur Mutlak dalam Rumus Tabel Excel

Dapatkan tips dan trik Excel terbaru dengan bergabung dalam buletin

Bergabunglah dengan buletin untuk tetap mengikuti artikel terbaru. Daftar dan Anda akan mendapatkan panduan gratis dengan 10 pintasan keyboard hemat waktu

Mengapa F4 tidak mengunci sel di Excel?

Bagaimana cara mengunci referensi rumus di Excel?

Apa yang terjadi jika Anda menekan tombol F4 pada bagian rumus di Excel?