Menggunakan rujukan berstruktur dengan jadual Excel

Penting: Artikel ini diterjemahkan oleh mesin, lihatlah notis penafian. Sila dapatkan versi Bahasa Inggeris artikel ini di sini sebagai rujukan anda

Apabila anda mencipta jadual Excel, Excel memperuntukkan nama untuk jadual dan setiap pengepala lajur dalam Jadual. Apabila anda menambah Formula pada jadual Excel, nama tersebut boleh muncul secara automatik apabila anda memasukkan formula dan memilih rujukan sel dalam Jadual dan bukannya pemasukan mereka. Berikut adalah contoh Apakah Excel:

Selain daripada menggunakan rujukan sel eksplisit

Excel menggunakan nama jadual dan lajur

=Sum(C2:C7)

=SUM(DeptSales[Amaun Jualan])

Gabungan nama jadual dan lajur tersebut dipanggil rujukan berstruktur. Nama dalam rujukan berstruktur akan dilaraskan apabila anda menambah atau mengalih keluar data daripada jadual.

Rujukan berstruktur juga muncul apabila anda mencipta formula di luar jadual Excel yang merujuk data jadual. Rujukan memudahkan jadual dicari dalam buku kerja yang besar.

Untuk memasukkan rujukan berstruktur dalam formula anda, klik sel jadual yang anda ingin rujukkan dan bukannya menaip rujukan sel dalam formula. Mari kita gunakan data contoh berikut untuk memasukkan formula yang menggunakan rujukan berstruktur secara automatik untuk mengira jumlah komisen jualan.

Jualan Individu

Rantau

Amaun Jualan

% Komisen

Amaun komisen

Joe

Utara

260

10%

Robert

Selatan

660

15%

Michelle

Timur

940

15%

Erich

Barat

410

12%

Dafna

Utara

800

15%

Rob

Selatan

900

15%

  1. Salin data sampel dalam Jadual di atas, termasuk tajuk lajur, dan tampalkannya ke dalam sel A1 lembaran kerja Excel baru.

  2. Untuk mencipta jadual, pilih sebarang sel dalam julat data, dan tekan Ctrl + T.

  3. Pastikan kotak Jadual saya mempunyai pengepala ditandakan dan klik OK.

  4. Dalam sel E2, taipkan tanda sama dengan (=), klik sel C2.

    Dalam bar formula, rujukan berstruktur [@[Amaun Jualan]] muncul selepas tanda sama dengan.

  5. Taipkan asterisk (*) terus selepas menutup kurungan, dan klik sel D2.

    Dalam bar formula, rujukan berstruktur [@[% Komisen]] muncul selepas asterisk.

  6. Tekan Enter.

    Excel mencipta lajur terhitung secara automatik dan menyalin formula ke bawah seluruh lajur untuk anda dengan melaraskannya untuk setiap baris.

Apa yang berlaku apabila saya menggunakan rujukan sel tersurat?

Jika anda memasukkan rujukan sel dalam lajur terhitung, adalah lebih sukar untuk melihat apa yang dikira formula.

  1. Dalam lembaran kerja sampel anda, klik sel E2

  2. Dalam formula bar, masukkan = C2 * D2 dan tekan Enter.

Perhatikan walaupun Excel menyalin formula anda ke bawah lajur, ia tidak menggunakan rujukan berstruktur. Jika, contohnya, anda menambah lajur antara lajur C dan D sedia ada, anda perlu mengubah formula anda.

Bagaimanakah saya mengubah nama jadual?

Apabila anda mencipta jadual Excel, Excel mencipta nama jadual lalai (Jadual1, Jadual2 dan seterusnya), tetapi anda boleh mengubah nama jadual untuk menjadikannya lebih bermakna.

  1. Pilih sebarang sel dalam Jadual untuk menunjukkan Alat Jadual > tab Reka bentuk pada reben.

  2. Taipkan nama yang anda inginkan dalam kotak Nama jadual dan tekan Enter.

Dalam data contoh kami, kami telah menggunakan nama DeptSales.

Gunakan peraturan berikut untuk nama Jadual:

  • Gunakan aksara sah  Sentiasa mulakan nama dengan huruf, ada aksara garis bawah (_) atau garis condong belakang (\). Menggunakan huruf, nombor, tempoh dan aksara untuk baki nama garis bawah. Anda tidak boleh menggunakan "C", "c", "R" atau "r" nama, kerana ia telah dilantik sebagai pintasan untuk memilih lajur atau baris bagi sel yang aktif apabila anda memasukkannya dalam kotak Nama atau Pergi ke .

  • Jangan gunakan rujukan sel  Nama tidak boleh sama seperti rujukan sel, seperti Z$ 100 atau R1C1.

  • Jangan gunakan ruang untuk memisahkan perkataan  Kawasan tidak boleh digunakan dalam nama. Anda boleh menggunakan aksara garis bawah (_) dan tempoh (.) sebagai pemisah perkataan. Contohnya, DeptSales, Sales_Tax atau First.Quarter.

  • Gunakan tidak lebih daripada 255 aksara Nama jadual boleh mempunyai sehingga 255 aksara.

  • Gunakan nama jadual unik Nama pendua tidak dibenarkan. Excel tidak membezakan antara aksara huruf besar dan huruf kecil dalam nama jadi jika anda memasukkan "Jualan" tetapi sudah mempunyai nama lain dipanggil "JUALAN" dalam buku kerja yang sama, anda akan digesa untuk memilih nama yang unik.

  • Menggunakan pengecam objek  Jika anda merancang pada mempunyai campuran Jadual, carta dan jadual pangsi, ia adalah idea yang baik untuk awalan nama anda dengan jenis objek. Contohnya: tbl_Sales untuk jadual jualan, pt_Sales untuk jualan Jadual pangsi, dan chrt_Sales untuk Carta jualan atau ptchrt_Sales untuk Carta pangsi jualan. Ini mengekalkan semua nama anda dalam senarai tersusun dalam Pengurus nama.

Peraturan sintaks rujukan berstruktur

Anda juga boleh memasukkan atau mengubah rujukan berstruktur secara manual dalam formula tetapi untuk berbuat sedemikian, ia akan membantu untuk memahami sintaks rujukan berstruktur. Mari kita melalui contoh formula berikut:

=SUM(DeptSales[ [ #Jumlah],[Amaun Jualan] ],DeptSales[[ #Data],[Amaun Komisen]])

Formula ini mempunyai komponen rujukan berstruktur berikut:

  • Nama jadual:   DeptSales ialah nama Jadual tersuai. Ia rujukan data Jadual tanpa sebarang pengepala atau jumlah baris. Anda boleh gunakan nama Jadual lalai, seperti jadual1, atau ubah untuk menggunakan nama tersuai.

  • Penentu lajur:   [Amaun jualan]dan[Amaun komisen] ialah penentu lajur yang menggunakan nama lajur yang ia mewakili. Ia merujuk kepada data lajur, tanpa sebarang lajur pengepala atau jumlah baris. Sentiasa sertakan penentu dalam kurungan seperti yang ditunjukkan.

  • Penentu item:   [#Totals] dan [#Data] ialah penentu item khas yang merujuk kepada bahagian tertentu Jadual, seperti baris jumlah.

  • Penentu jadual:   [[ #Jumlah],[Amaun Jualan]] dan [[ Data],[Amaun Komisen]] ialah penentu jadual yang mewakili bahagian luar bagi rujukan berstruktur. Rujukan luaran mengikuti nama jadual dan anda mengurungnya dalam tanda kurung siku.

  • Rujukan berstruktur:   (DeptSales [[#Totals], [amaun jualan]] dan DeptSales [[#Data], [amaun komisen]] ialah rujukan berstruktur, diwakili oleh rentetan yang bermula dengan nama Jadual dan berakhir dengan penentu lajur.

Untuk mencipta atau mengedit rujukan berstruktur secara manual, gunakan peraturan sintaks ini:

  • Gunakan tanda kurung di sekeliling penentu    Semua Jadual, lajur dan penentu item khas perlu dikurung dalam tanda kurung ([]) yang sepadan. Penentu yang mengandungi penentu lain memerlukan luaran sepadan kurungan mengurung inner yang sepadan dengan tanda kurung penentu lain. Contohnya: = DeptSales [[jurujual]: [rantau]]

  • Semua Pengepala lajur ialah rentetan teks    Tetapi ia tidak memerlukan sebut harga apabila ia digunakan dalam rujukan berstruktur. Nombor atau tarikh, seperti 2014 atau 1/1/2014, juga dianggap sebagai rentetan teks. Anda tidak boleh menggunakan ungkapan dengan pengepala lajur. Contohnya, ungkapan DeptSalesFYSummary [[2014]: [2012]] tidak akan berfungsi.

Menggunakan tanda kurung di sekeliling pengepala lajur dengan aksara khas    Jika terdapat aksara khas, seluruh pengepala lajur perlu dikurung dalam tanda kurung, yang bermakna tanda kurung berganda diperlukan dalam penentu lajur. Contohnya: =DeptSalesFYSummary[[Jumlah Amaun $]]

Berikut adalah senarai aksara khas yang memerlukan tanda kurung tambahan dalam formula:

  • Tab

  • Suapan baris

  • Kembalian penyorong

  • Koma (,)

  • Titik Bertindih (:)

  • Noktah (.)

  • Tanda kurung kiri ([)

  • Tanda kurung kanan (])

  • Tanda paun (#)

  • Tanda petikan tunggal (')

  • Tanda petikan kembar (")

  • Pendakap kiri ({)

  • Pendakap kanan (})

  • Tanda dolar ($)

  • Karet (^)

  • Ampersan (&)

  • Asterisk (*)

  • Tanda tambah (+)

  • Tanda sama dengan (=)

  • Tanda tolak (-)

  • Simbol lebih besar daripada (>)

  • Simbol lebih kecil daripada (<)

  • Tanda bahagi (/)

  • Menggunakan aksara escape untuk sesetengah aksara khas dalam pengepala lajur    Sesetengah aksara mempunyai maksud khas dan memerlukan penggunaan tanda petikan tunggal (') sebagai aksara escape. Contohnya: =DeptSalesFYSummary['#OfItems]

Berikut adalah senarai aksara khas yang memerlukan aksara escape (') dalam formula:

  • Tanda kurung kiri ([)

  • Tanda kurung kanan (])

  • Tanda paun (#)

  • Tanda petikan tunggal (')

Menggunakan aksara ruang untuk meningkatkan kebolehbacaan dalam rujukan berstruktur    Anda boleh menggunakan aksara ruang untuk meningkatkan kebolehbacaan rujukan berstruktur. Contohnya: =DeptSales[ [Jurujual]:[Rantau] ] atau =DeptSales[[#Pengepala], [#Data], [% Komisen]]

Anda disyorkan untuk menggunakan satu ruang:

  • Selepas tanda kurung kiri pertama ([)

  • Sebelum tanda kurung kanan yang terakhir (]).

  • Selepas koma.

Operator rujukan

Untuk kefleksibelan tambahan dalam menentukan julat sel, anda boleh menggunakan operator rujukan yang berikut untuk menggabungkan penentu lajur.

Rujukan berstruktur ini:

Merujuk kepada:

Menggunakan:

Julat selnya:

=DeptSales[[Jurujual]:[Rantau]]

Semua sel dalam dua atau lebih lajur bersebelahan

: (tanda titik bertindih) operator julat

A2:B7

=DeptSales[Amaun Jualan] ,DeptSales[Amaun Komisen]

Gabungan dua atau lebih lajur

, (koma) operator penyatuan

C2:C7, E2:E7

=DeptSales[ [Jurujual] : [Amaun Jualan]] DeptSales[ [Rantau] : [% Komisen]]

Persilangan dua atau lebih lajur

(jarak) operator persilangan

B2:C7

Penentu item khas

Untuk merujuk kepada bahagian tertentu jadual, seperti baris jumlah sahaja, anda boleh menggunakan mana-mana penentu item khas berikut dalam rujukan berstruktur anda.

Penentu item khas:

Merujuk kepada:

#Semua

Seluruh jadual, termasuk pengepala lajur, data dan jumlah (jika ada).

#Data

Hanya baris data.

#Pengepala

Hanya baris pengepala.

#Jumlah

Hanya baris jumlah. Jika tiada yang wujud, maka ia mengembalikan nol.

#Baris Ini

atau

@

atau

@[Nama Lajur]

Hanya sel dalam baris yang sama sebagai formula. Penentu ini tidak boleh digabungkan dengan sebarang penentu item khas lain. Gunakannya untuk memaksa kelakuan persilangan tersirat untuk rujukan atau untuk mengubah kelakuan persilangan tersirat dan merujuk pada nilai tunggal daripada lajur.

Excel secara automatiknya mengubah penentu #Baris Ini kepada @ penentu yang lebih pendek dalam jadual yang mempunyai lebih daripada satu baris data. Tetapi jika jadual anda mempunyai satu baris sahaja, Excel tidak menggantikan penentu #Baris Ini, yang mungkin menyebabkan hasil pengiraan yang tidak dijangka apabila anda menambah lebih banyak baris. Untuk mengelakkan masalah pengiraan, pastikan anda memasukkan berbilang baris dalam jadual anda sebelum anda memasukkan sebarang formula rujukan berstruktur.

Melayakkan rujukan berstruktur dalam lajur terhitung

Apabila anda mencipta lajur terhitung, anda sering kali menggunakan rujukan berstruktur untuk mencipta formula. Rujukan berstruktur ini boleh menjadi tidak layak atau layak sepenuhnya. Contohnya, untuk mencipta lajur terhitung yang dipanggil Amaun Komisen, yang mengira amaun komisen dalam dolar, anda boleh menggunakan formula berikut:

Jenis rujukan berstruktur

Contoh

Komen

Tidak layak

=[Amaun Jualan]*[% Komisen]

Mendarabkan nilai sepadan daripada baris semasa.

Layak sepenuhnya

=DeptSales[Amaun Jualan] * DeptSales[% Komisen]

Mendarabkan nilai sepadan pada setiap baris untuk kedua-dua lajur.

Peraturan am untuk diikuti ialah: Jika anda menggunakan rujukan berstruktur dalam jadual, seperti apabila anda mencipta lajur terhitung, anda boleh menggunakan rujukan berstruktur tidak layak, tetapi jika anda menggunakan rujukan berstruktur di luar jadual, anda perlu menggunakan rujukan berstruktur yang layak sepenuhnya.

Contoh penggunaan rujukan berstruktur

Berikut adalah beberapa cara untuk menggunakan rujukan berstruktur.

Rujukan berstruktur ini:

Merujuk kepada:

Julat selnya:

=DeptSales[[#Semua],[Amaun Jualan]]

Semua sel dalam lajur Amaun Jualan.

C1:C8

=DeptSales[[#Pengepala],[% Komisen]]

Pengepala bagi % lajur Komisen.

D1

=DeptSales[[#Totals],[Region]]

Jumlah lajur Rantau. Jika tiada baris Jumlah, maka ia mengembalikan nol.

B8

=DeptSales[[#Semua],[Amaun Jualan]:[% Komisen]]

Semua sel dalam Amaun Jualan dan % Komisen.

C1:D8

=DeptSales[[#Data],[% Komisen]:[Amaun Komisen]]

Hanya data bagi lajur % Komisen dan Amaun Komisen.

D2:E7

=DeptSales[[#Pengepala],[Rantau]:[Amaun Komisen]]

Hanya pengepala bagi lajur antara Rantau dan Amaun Komisen.

B1:E1

=DeptSales[[#Jumlah],[Amaun Jualan]:[Amaun Komisen]]

Jumlah bagi lajur Amaun Jualan hingga Amaun Komisen. Jika tiada baris Jumlah, maka ia mengembalikan nol.

C8:E8

=DeptSales[[#Pengepala],[#Data],[% Komisen]]

Hanya pengepala dan data bagi % Komisen.

D1:D7

=DeptSales[[#Baris Ini], [Amaun Komisen]]

atau

=DeptSales[@Amaun Komisen]

Sel pada persilangan baris semasa dan lajur amaun komisen. Jika digunakan dalam baris yang sama sebagai pengepala atau jumlah baris, ini akan kembali kepada #VALUE! ralat.

Jika anda menaip bentuk rujukan berstruktur ini yang lebih panjang (#Baris Ini) dalam jadual dengan berbilang baris data, Excel akan menggantikannya secara automatik dengan bentuk yang lebih pendek (@). Kedua-duanya berfungsi cara yang sama.

E5 (jika baris semasa ialah 5)

Strategi untuk bekerja dengan rujukan berstruktur

Pertimbangkan perkara berikut apabila anda menggunakan rujukan berstruktur.

  • Menggunakan Formula AutoLengkap    Anda mungkin mendapati penggunaan Formula AutoLengkap adalah sangat berguna apabila anda memasukkan rujukan berstruktur dan untuk memastikan penggunaan sintaks yang betul. Untuk maklumat lanjut, lihat Menggunakan Formula AutoLengkap.

  • Memutuskan sama ada untuk menjana rujukan berstruktur untuk Jadual dalam pemilihan separa    Secara lalai, apabila anda mencipta formula, mengklik sel julat dalam memilih separa Jadual sel dan memasukkan rujukan berstruktur selain daripada julat sel dalam formula secara automatik. Kelakuan pemilihan separa ini menjadikannya lebih mudah untuk memasukkan rujukan berstruktur. Anda boleh menghidupkan atau mematikan kelakuan ini dengan memilih atau mengosongkan kotak semak Gunakan jadual nama dalam Formula dalam fail > opsyen > Formula > dialog bekerja dengan Formula .

  • Menggunakan buku kerja dengan pautan luaran ke jadual Excel dalam buku kerja lain    Jika buku kerja mengandungi pautan luaran ke jadual Excel dalam buku kerja lain, buku kerja sumber terpaut itu mesti dibuka dalam Excel untuk mengelakkan #REF! ralat dalam buku kerja destinasi yang mengandungi pautan. Jika anda membuka buku kerja destinasi pertama dan #REF! ralat muncul, mereka akan dapat diselesaikan jika anda kemudian buka buku kerja sumber. Jika anda membuka buku kerja sumber pertama, anda akan melihat kod ralat tiada.

  • Menukar julat kepada jadual dan Jadual kepada julat    Apabila anda menukar Jadual kepada julat, Semua rujukan sel berubah kepada mereka sama A1 gaya rujukan mutlak. Apabila anda menukar julat kepada jadual, Excel secara automatik tidak mengubah sebarang rujukan sel bagi julat ini kepada mereka rujukan berstruktur yang sama.

  • Mematikan pengepala lajur    Anda boleh Menogol pengepala lajur dan mematikan dari tab Reka bentuk Jadual > Baris pengepala. Jika anda mematikan pengepala lajur, tidak dipengaruhi oleh rujukan berstruktur yang menggunakan nama lajur dan anda masih boleh menggunakan mereka dalam Formula. Rujukan yang merujuk secara langsung kepada pengepala Jadual berstruktur (cth = DeptSales [[#Headers], [% komisen]]) akan menyebabkan #REF.

  • Tambah atau padamkan lajur dan baris pada jadual    Kerana julat data Jadual kerap mengubah, rujukan sel untuk rujukan berstruktur dilaraskan secara automatik. Contohnya, jika anda menggunakan nama Jadual dalam formula untuk mengira semua sel data dalam Jadual, dan anda kemudian menambah baris data, rujukan sel dilaraskan secara automatik.

  • Menamakan semula jadual atau lajur    Jika anda menamakan semula lajur atau jadual, Excel mengubah penggunaan jadual dan pengepala lajur dalam semua rujukan berstruktur yang digunakan dalam buku kerja.

  • Mengalih, menyalin, dan mengisi rujukan berstruktur    Semua rujukan berstruktur kekal sama apabila anda menyalin atau mengalihkan formula yang menggunakan rujukan berstruktur.

    Nota: Menyalin rujukan berstruktur dan melakukan mengisi rujukan berstruktur tidak perkara yang sama. Apabila anda menyalin, Semua rujukan berstruktur kekal sama semasa apabila anda mengisikan formula, rujukan berstruktur yang layak sepenuhnya melaraskan penentu lajur seperti siri sebagaimana diringkaskan dalam Jadual berikut.

Jika arah isian ialah:

Dan semasa mengisi, anda menekan:

Kemudian:

Atas atau bawah

Tiada apa-apa

Tiada pelarasan penentu lajur.

Atas atau bawah

Ctrl

Penentu lajur dilaraskan seperti siri.

Kanan atau kiri

Tiada

Penentu lajur dilaraskan seperti siri.

Atas, bawah, kanan atau kini

Shift

Selain menulis ganti nilai dalam sel semasa, nilai sel semasa dialihkan dan penentu lajur diselitkan.

Perlukan bantuan lanjut?

Anda sentiasa boleh tanya pakar dalam Komuniti teknikal Excel, dapatkan sokongan dalam komuniti jawapanatau cadangkan ciri baru atau peningkatan pada Excel pengguna suara.

Topik Berkaitan

Gambaran keseluruhan jadual Excel
Video: mencipta dan memformatkan jadual Excel
jumlah data dalam jadual Excel
Format jadual Excel

Menapis data dalam julat atau jadual
menukar Jadual kepada julat
isu keserasian jadual Excel
mengeksport jadual Excel ke SharePoint
gambaran keseluruhan Formula dalam Excel

Nota: Notis Penafian Penterjemahan Mesin: Artikel ini telah diterjemah oleh sistem komputer tanpa campur tangan manusia. Microsoft menawarkan penterjemahan mesin ini untuk membantu pengguna-pengguna yang tidak bertutur dalam Bahasa Inggeris supaya dapat menikmati kandungan mengenai produk, perkhidmatan dan teknologi Microsoft. Artikel ini mungkin mengandungi ralat perbendaharaan kata, sintaks atau tatabahasa kerana ia diterjemahkan oleh mesin.

Kembangkan kemahiran anda
Jelajahi latihan
Dapatkan ciri baru terlebih dahulu
Sertai Office Insiders

Adakah maklumat ini membantu?

Terima kasih atas maklum balas anda!

Terima kasih atas maklum balas anda! Nampaknya ia mungkin akan membantu untuk menyambungkan anda kepada salah seorang daripada ejen sokongan Office kami.

×