Tutorial Tom Untuk Excel. Satu rumus mengembalikan nilai sel yang sama di beberapa lembar kerja
Inilah cara satu rumus dapat mengembalikan nilai alamat sel yang sama dari beberapa lembar kerja. Pada gambar, Anda memiliki buku kerja pengeluaran dengan lima lembar kerja. Empat lembar kerja (bernama Sheet2, Sheet3, Sheet4, dan Sheet5) disiapkan dengan cara yang sama, dengan jumlah total pengeluaran regional di sel B12
Pada lembar Ringkasan buku kerja Anda adalah "B12" (tanpa tanda kutip) di beberapa sel, untuk contoh ini sel E1 seperti yang terlihat pada gambar berikut. Sekarang, gunakan fungsi INDIRECT untuk merujuk ke nilai sel tersebut di E1, dan fungsi ROW untuk membantu merujuk ke masing-masing dari empat nama lembar sumber, Anda dapat memasukkan rumus ini seperti yang terlihat di sel B3…
=INDIRECT("'Sheet"&ROW()-1&"'!"&$E$1)
.. dan salin sesuai kebutuhan. Ini mengembalikan nilai sel B12 di setiap lembar kerja
Triknya adalah dengan merencanakan ke depan saat Anda mendesain buku kerja Anda agar satu rumus dapat mengerjakan banyak rumus. Dan keindahan dari sistem ini adalah, jika Anda mengubah lokasi sel Total pada lembar kerja regional, seperti jika item pengeluaran lain ditambahkan, cukup ubah sel E1 pada lembar Ringkasan untuk alamat sel Total baru itu
DDrew_
Dewan Reguler
BergabungJul 8, 2017Pesan87
- 10 Juli 2017
- #1
Halo semua
Saya ingin menarik data dari sel yang sama di beberapa lembar sehingga muncul dalam satu kolom di lembar master. Dengan kata lain, saya ingin menarik sel D27 dari Lembar 1 dan meletakkannya di E2 pada lembar master. Kemudian di E3, saya ingin sel D27 dari Lembar 2, di sel E4 D27 dari Lembar 3, dll. Apakah ada cara untuk melakukan ini?
DDonnyF
Dewan Reguler
BergabungApr 25, 2017Pesan76
- 10 Juli 2017
- #2
Pada lembar master gunakan kolom pembantu misalkan kolom D. Di D2 taruh nomor satu dan lanjutkan urutan ke bawah kolom untuk semua lembar kerja yang perlu Anda rujuk
Kemudian di sel E2
=INDIRECT("'Lembar" & D2 & "'. $D$27")
Kemudian cukup seret ke angka terakhir di kolom D
semoga membantu
Terakhir diedit. 10 Juli 2017
DDrew_
Dewan Reguler
BergabungJul 8, 2017Pesan87
- 10 Juli 2017
- #3
Ya ini berhasil. Saya punya pertanyaan serupa lainnya. Saya ingin rata-rata sel yang sama dari beberapa lembar menjadi sel di lembar master, tetapi mengecualikan 0 nilai. Saya menemukan formula di forum ini yang berfungsi untuk orang lain tetapi tidak untuk saya
=JUMLAH('1. 60'. N7)/INDEKS(FREKUENSI('1. 60'. N7,0),2)
N7 adalah sel yang ingin saya tarik dari setiap lembar, lembar saya diberi nama 1 hingga 60. Saya bertanya-tanya dari mana asal 2 di akhir formula dan bagaimana saya bisa membuat formula ini bekerja untuk saya?
Aladin Akyurek
MrExcel MVPBergabungFeb 14, 2002Messages85,210
- 10 Juli 2017
- #4
kata Drew_ Ya ini berhasil. Saya punya pertanyaan serupa lainnya. Saya ingin rata-rata sel yang sama dari beberapa lembar menjadi sel di lembar master, tetapi mengecualikan 0 nilai. Saya menemukan formula di forum ini yang berfungsi untuk orang lain tetapi tidak untuk saya Klik untuk meluaskan
=JUMLAH('1. 60'. N7)/INDEKS(FREKUENSI('1. 60'. N7,0),2)
N7 adalah sel yang ingin saya tarik dari setiap lembar, lembar saya diberi nama 1 hingga 60. Saya bertanya-tanya dari mana asal 2 di akhir formula dan bagaimana saya bisa membuat formula ini bekerja untuk saya?
Lihat posting balasan Anda yang lain
Anda mungkin memiliki data di beberapa lembar Excel yang perlu dikonsolidasikan ke dalam satu lokasi pusat. Untungnya, Anda dapat membuat Master Sheet dengan ringkasan dan total lainnya dari beberapa lembar sumber yang berbeda.
Dalam tutorial ini, kami akan memperkenalkan cara menghitung total dan mendapatkan data ringkasan dari beberapa lembar dengan struktur serupa
Dapatkan data dari beberapa sheet menjadi master sheet di Excel
1. Hitung Total Berdasarkan Referensi Sel
Artikel ini menunjukkan cara mendapatkan data dari beberapa sheet ke dalam master sheet. Bergantung pada situasinya, ini dapat dilakukan dengan berbagai metode. Anggaplah kita memiliki 3 lembar penjualan dari 3 toko yang berbeda. 1, 2 dan 3 dengan format serupa di bawah ini
Kami ingin menambahkan Total penjualan Toko 1, 2 dan 3 bersama-sama dan menempatkan hasilnya di lembar "Ringkasan". Dalam contoh ini, kita dapat menggunakan referensi sel seperti pada rumus berikut
Masalah dengan menggunakan operator penjumlahan adalah kami tidak dapat terus menambahkan sel dengan lebih banyak lembar
2. Hitung Total Berdasarkan Formula 3D
Excel memiliki rumus 3D untuk membantu kami menjumlahkan semua data di beberapa sheet yang berdekatan. Pada tab Ringkasan, ketik =sum( di sel C2, pilih sel D9 di tab Store1, tahan tombol Shift dan pilih tab lainnya. Bilah rumus ditampilkan
=sum(‘Store1:Store3’!D9)
Rumus 3D ini menghitung total semua sel D9 di lembar berbeda dari Toko 1 hingga Toko 3
Rumus ini memberikan hasil yang sama dengan menggunakan referensi sel. Lihat di bawah
Perhatikan saat menggunakan rumus ini, pengguna harus memastikan untuk mempertahankan urutan lembaran yang sama. Jika salah satu sheet di tengah rentang sheet (Store to Store 3) dipindahkan dari rentang, sheet tersebut akan dikeluarkan dari perhitungan. Pada screenshot di bawah, kita melihat bahwa saat sheet Store2 dipindahkan ke kanan sheet Store3, nilai Total penjualan turun menjadi 645. 9 yang merupakan penjualan Store1 + Store2 saja
3. Kumpulkan data dari beberapa lembar menggunakan INDIRECT()
Kita dapat menambahkan data dari beberapa sheet ke dalam sheet ringkasan menggunakan fungsi INDIRECT(). Fungsi INDIRECT() membutuhkan kita untuk menyimpan nama sheet secara eksplisit
=SUM(INDIRECT(C1&"!"&"D9"),INDIRECT(D1&"!"&"D9"),INDIRECT(E1&"!"&"D9"))_
Dalam rumus di atas, INDIRECT(C1&”. ”&”D9″) memberi kami total penjualan yang terletak di sel D9 pada lembar Store1, INDIRECT(D1&”. ”&”D9″) memberi kita total penjualan yang terletak di sel D9 di Store2, dan seterusnya
Rumus ini lebih baik daripada rumus 3D karena tidak bergantung pada lokasi lembar. Namun, rumus di atas memerlukan daftar semua nama sheet
Seperti yang telah Anda lihat dari tutorial ini, mengumpulkan data dari beberapa lembar dapat dilakukan dengan beberapa cara berbeda dan tidak ada solusi terbaik. Bergantung pada situasinya, seseorang dapat menggunakan rumus 3D atau fungsi INDIRECT(). Dalam beberapa kasus lain, Anda juga dapat menggunakan Konsolidasi Data untuk menghitung total
Masalah yang berbeda memiliki solusi terbaiknya sendiri. Jika Anda mengalami kebuntuan dan ingin mendapatkan solusi dengan cepat, ajukan pertanyaan Anda agar dijawab oleh pakar Excel dalam 20 menit. Mereka tersedia untuk membantu Anda 24/7 di tautan di sebelah kanan. Pertanyaan pertama gratis