Penjelasan terperinci mengenai pengendali persimpangan tersirat @ simbol Excel

Ciri persimpangan tersirat Excel telah wujud selama bertahun -tahun, tetapi tidak diketahui. Tidak sampai Excel 365 tidak lagi menggunakannya sebagai tingkah laku lalai yang banyak soalan timbul. Tutorial ini bertujuan untuk menerangkan makna simbol "@" (dipanggil pengendali persimpangan tersirat) dalam formula Excel.

Anda mungkin berasa seperti anda tahu buku kerja anda dengan baik, dan kemudian watak "@" tiba -tiba muncul pada permulaan formula. Apa maksudnya? Bagaimana sebenarnya ia berfungsi? Singkatnya, ia adalah pengendali persimpangan tersirat yang melumpuhkan tingkah laku array lalai baru formula dan mengarahkan Excel untuk mengembalikan satu nilai. Untuk maklumat lanjut, sila teruskan membaca.

Persimpangan tersirat Excel

Persimpangan tersirat dalam Excel merujuk kepada memudahkan nilai -nilai berbilang ke dalam satu nilai. Biasanya, apabila array atau julat diberikan kepada formula, formula harus mengeluarkan hanya satu nilai dalam satu sel.

Persimpangan tersirat di Excel 2019 - 2000

Dalam Excel tradisional, persimpangan tersirat adalah tingkah laku lalai. Ia diam -diam dilaksanakan untuk semua formula di latar belakang. Logik persimpangan tersirat adalah seperti berikut:

• Jika formula menghasilkan satu nilai , nilai itu dikembalikan (sebenarnya, dalam hal ini persimpangan tersirat tidak melakukan apa -apa).
• Untuk julat , gunakan nilai dalam sel yang berada dalam baris atau lajur yang sama sebagai formula.
• Untuk tatasusunan , gunakan nilai di sudut kiri atas.

Sebagai contoh, apabila didarab dengan dua lajur nombor, Excel memilih hanya satu nombor dalam setiap lajur yang berada dalam baris yang sama dengan formula dan output hasil hanya dalam satu sel (D2 dalam kes kita):

=B2:B5*C2:C5

Untuk melipatgandakan nombor dalam sel lain, anda perlu menyalin formula ke bawah.

Untuk melumpuhkan persimpangan tersirat , anda mesti menggunakan CTRL Shift Enter untuk memasuki formula array (inilah sebabnya formula array tradisional kadang -kadang dipanggil formula CSE ). Ini secara eksplisit memberitahu Excel untuk memproses pelbagai nilai sebagai julat atau input array.

Dalam contoh kami, pilih sel D2: D5, taipkan formula di atas, dan tekan CTRL Shift Enter untuk mengesahkan bersama. Setelah selesai, formula akan ditutup dalam pendakap {} untuk menunjukkan bahawa ini adalah formula array. Akibatnya, nombor dalam setiap baris didarab dengan segera:

Persimpangan tersirat di Excel 365

Pengenalan tatasusunan dinamik mengubah tingkah laku lalai semua formula dalam Excel 365. Ini menjadikan persimpangan tersirat tidak perlu dan tidak lagi dicetuskan secara lalai . Oleh itu, Excel 365 kadang -kadang dipanggil array dinamik Excel atau DA Excel .

Di sini, julat keseluruhannya didarab dengan formula biasa yang dimasukkan hanya dalam sel paling atas (D2):

=B2:B5*C2:C5

Hasilnya adalah julat limpahan dengan 4 sel:

Jika anda mahu formula untuk mengembalikan hanya satu nilai, anda perlu secara eksplisit membolehkan persimpangan tersirat . Untuk melakukan ini, mereka memperkenalkan pengendali khas, dan di bahagian seterusnya anda akan mendapat butiran lengkap mengenainya.

Operator Persimpangan Implisit - @ Simbol dalam Formula Excel

Pengendali persimpangan tersirat diperkenalkan dalam Excel 365 untuk mengelakkan tingkah laku array dinamik lalai. Jika anda mahu formula untuk mengembalikan hanya satu nilai, tambahkan "@" sebelum nama fungsi (atau sebelum julat atau array dalam formula), dan ia akan berjalan seperti formula bukan array biasa sebelum versi dinamik.

Contohnya:

=@B2:B5*@C2:C5

Nota: Pengendali persimpangan tersirat disokong hanya dalam langganan Microsoft 365. Jika anda cuba menambah simbol "@" dalam versi yang lebih lama, ia akan dikeluarkan dengan senyap selepas anda melengkapkan formula.

Mengapa menambah @ ke formula lama?

Dalam Excel 365, ketika membuka buku kerja yang dibuat dalam versi yang lebih lama, anda mungkin dapati bahawa beberapa formula mempunyai aksara "@" ditambah. Dalam kebanyakan kes, ini dilakukan untuk memaksa formula untuk berkelakuan sama seperti yang dilakukan dalam versi asal di mana ia dicipta. Dalam erti kata lain, jika formula mengembalikan nilai tunggal dalam versi yang lebih lama, tetapi mengembalikan pelbagai hasil dalam Excel 365, ia secara automatik didahului oleh "@" untuk melumpuhkan tingkah laku array.

Biasanya, pengendali persimpangan dimasukkan sebelum fungsi yang boleh mengembalikan pelbagai atau pelbagai sel, seperti mengimbangi, indeks, atau fungsi yang ditentukan oleh pengguna.

Sebagai contoh, formula berikut yang ditulis dalam Excel sebelum dinamik Excel:

=INDEX(B2:C5,,F1)

Dalam Excel Array Dynamic, borang berikut akan diambil:

=@INDEX(B2:C5,,F1)

Sebabnya ialah tanpa pengendali "@", formula mengembalikan semua nilai dalam C2: C5, kerana parameter ROW_NUM fungsi indeks ditinggalkan. Untuk memastikan konsistensi dalam semua versi, persimpangan tersirat sebelum ini tidak mengganggu menjadi jelas. Sila bandingkan hasilnya:

Sekiranya fungsi dengan output pelbagai sel berpotensi bersarang dalam fungsi yang boleh memproses tatasusunan dan mengeluarkan hasil tunggal (seperti jumlah, kiraan, purata, dan lain-lain), tidak ada sebab untuk mencetuskan persimpangan tersirat, dan formula akan dipindahkan ke Excel dinamik seperti tanpa menambah simbol "@". Contohnya:

=AVERAGE(INDEX(B2:C5,,F1))

Bolehkah saya mengeluarkan simbol @ dari formula?

pasti. Excel melakukan penukaran formula hanya sekali. Jika pengendali "@" memecahkan atau mengubah negatif tingkah laku formula, anda boleh memadamkan "@" secara manual dan ia tidak akan muncul lagi selepas menyimpan buku kerja.

Apakah akibat memadam pengendali persimpangan tersirat? Menurut kandungan yang dikembalikan oleh seksyen formula selepas simbol "@", mungkin terdapat tiga hasil yang mungkin:

• Jika satu nilai dikembalikan, tiada perubahan berlaku.
• Sekiranya array dikembalikan, ia akan melimpah ke sel -sel bersebelahan.
• Kesalahan #Spill berlaku jika array dikembalikan tetapi tidak ada sel kosong yang cukup untuk memaparkan semua nilai.

Mengapa menggunakan pengendali @ di Excel 365?

Seperti yang dinyatakan sebelum ini, Excel untuk Microsoft 365 merawat semua formula sebagai formula array secara lalai tanpa menekan CTRL Shift Enter seperti dalam versi sebelumnya. Jika anda ingin melumpuhkan tingkah laku array, masukkan pengendali persimpangan tersirat.

Contoh 1. Hilangkan #Spill! Ralat

Senario yang sangat biasa adalah untuk mencegah atau memperbaiki kesilapan #spill. Sekiranya anda merasa mudah untuk merujuk seluruh lajur (ini biasanya bukan idea yang baik kerana ia melambatkan Excel), namun formula tersebut berfungsi dengan baik dalam versi sebelum dinamik:

=VLOOKUP(A:A, D:E, 2, FALSE)

Dalam Excel dinamik, ia akan menyebabkan kesilapan #Spill kerana tidak ada ruang yang cukup untuk memaparkan hampir 1.05 juta keputusan.

Menambah "@" sebelum mencari parameter nilai dapat menyelesaikan masalah ini:

=VLOOKUP(@A:A, D:E, 2, FALSE)

Apabila Excel meramalkan bahawa formula boleh melimpah tepi lembaran kerja, ia mencadangkan pembetulan, dan bijak untuk menerimanya:

Contoh 2. Buat formula berfungsi dengan betul dalam warisan excel

Jika anda menggunakan langganan Microsoft 365 dan berkongsi fail dengan orang yang mendesak menggunakan versi yang lebih lama, anda mesti memastikan konsistensi dalam tingkah laku formula untuk semua orang.

Katakan anda menulis formula array dinamik ini di Excel 365:

=B2:B5*C2:C5

Dalam versi yang lebih lama, ia akan ditukar kepada versi lama CSE Array Formula:

{=B2:B5*C2:C5}

Dan dalam kedua -dua kes, siri nilai akan dikembalikan:

Jika anda ingin mengeluarkan hanya satu hasil, tambahkan "@" sebelum setiap ungkapan dan salin formula ke seberapa banyak sel yang anda inginkan (untuk memastikan julat tidak berubah ingat untuk menguncinya dengan rujukan sel mutlak):

=@$B$2:$B$5*@$C$2:$C$5

Dalam versi Excel yang lebih lama, simbol "@" akan dipadamkan secara automatik dan formula akan mengambil penampilan biasa:

=$B$2:$B$5*$C$2:$C$5

Apa yang tidak boleh dilakukan ialah mencampurkan persimpangan dan pengiraan array yang tersirat dalam satu formula! Sebagai contoh, jika anda cuba memasukkan yang berikut dalam Excel 365:

=@B2:B5*C2:C5

Anda akan dimaklumkan bahawa formula ini tidak disokong dalam versi lama:

Jika anda menolak varian yang dicadangkan, formula pencampuran akan dikemukakan dan akan memberikan beberapa hasil (walaupun mungkin bukan yang anda harapkan). Walau bagaimanapun, apabila anda membuka formula dalam Excel sebelum Excel dinamik, fungsi _xlfn.single muncul dan bukannya pengendali persimpangan tersirat:

=_xlfn.SINGLE(B2:B5)*C2:C5

Apabila Excel yang lebih tua mengira formula ini, #name! ralat dikembalikan.

Persimpangan tersirat dalam jadual excel

Secara umumnya, tingkah laku persimpangan tersirat dalam jadual adalah konsisten dengan versi Excel anda.

Dalam Excel 2019 dan lebih awal , anda boleh merujuk seluruh lajur dan formula menyelesaikan sel tunggal dalam baris semasa.

Sebagai contoh, formula ini berjaya mengalikan nombor dalam lajur "Harga" dan "Kuantiti":

=[Price]*[Qty.]

Walaupun ia merujuk keseluruhan lajur, cemerlang sebelum Excel dinamik masih beroperasi pada satu nilai pada tahap baris.

Dalam Excel 365 , pendekatan ini tidak akan berfungsi, kerana persimpangan tersirat dimatikan secara lalai. Kerana formula mengembalikan pelbagai nilai dan Excel tidak dapat memasukkannya ke dalam jadual, ia mengesyorkan menambah @ simbol sebelum nama lajur:

Sintaks ini harus biasa jika anda mempunyai pengalaman menggunakan rujukan jadual - @ formula perwakilan simbolik akan mengendalikan nilai lajur "harga" dan "kuantiti" dalam baris yang sama.

=[@Price]*[@[Qty.]]

Ia akan berfungsi dengan baik dalam semua versi:

Petua: Jika anda bercadang untuk menggunakan fungsi array dinamik untuk mengira data jadual, pastikan untuk meletakkan formula di luar meja, kerana tatasusunan dinamik tidak disokong dalam jadual.

Ini adalah bagaimana persimpangan tersirat berfungsi dalam Excel. Terima kasih kerana membaca dan berharap dapat berjumpa dengan anda minggu depan di blog kami!

Atas ialah kandungan terperinci Persimpangan tersirat: Penggunaan @ log masuk formula excel. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!