Gunakan formula formula bersyarat untuk mengendalikan susunan limpahan di Excel

Pemformatan langsung susunan limpahan dalam Excel boleh menyebabkan masalah, terutamanya apabila bentuk data atau saiz berubah. Peraturan pemformatan bersyarat berasaskan formula membolehkan pemformatan automatik apabila parameter data berubah. Menambah tanda dolar ($) sebelum rujukan lajur menggunakan peraturan untuk semua baris dalam data.

Dalam Excel, anda boleh memohon pemformatan langsung kepada nilai atau latar belakang sel untuk membuat spreadsheet lebih mudah dibaca. Walau bagaimanapun, apabila formula Excel mengembalikan satu set nilai (dipanggil tatasusunan limpahan), memohon pemformatan langsung akan menyebabkan masalah jika saiz atau bentuk perubahan data.

Katakan anda mempunyai spreadsheet ini dengan hasil limpahan dari formula pivotby yang menunjukkan bilangan penonton setiap sukan di setiap rantau sejak empat tahun yang lalu.

Oleh kerana fungsi pivotby tidak mengandungi parameter yang membolehkan anda memformat hasilnya, sukar untuk membezakan antara baris tajuk, baris data, baris subtotal, dan jumlah baris.

Pada ketika ini, anda mungkin mahu memohon pemformatan langsung melalui kumpulan fon pada tab permulaan reben untuk secara intuitif membezakan pelbagai jenis baris dalam data anda.

Walau bagaimanapun, jika anda kemudian mengubahsuai parameter dalam formula pivotby, atau hasilnya diperbesar atau dikurangkan disebabkan oleh perubahan dalam data asal, pemformatan langsung yang anda gunakan tidak akan diselaraskan dengan sewajarnya. Ini kerana pemformatan langsung dalam Excel dikaitkan dengan sel, bukan dengan data yang mereka ada. Lihat tangkapan skrin di bawah di mana data telah dikurangkan, tetapi pemformatan langsung masih digunakan pada baris yang sama, yang boleh menyebabkan kekeliruan.

Sebaliknya, anda harus menggunakan pemformatan bersyarat, yang membolehkan anda memformatnya berdasarkan nilai sel dan baris.

Pilih semua data (dan beberapa baris tambahan di bahagian bawah untuk membolehkan pertumbuhan menegak), dan pada tab permulaan reben, klik Format Bersyarat> Urus Peraturan.

Seterusnya, dalam kotak dialog Pengurus Peraturan Format Bersyarat, klik Peraturan Baru.

Untuk setiap peraturan yang akan anda buat untuk memformat tatasusunan limpahan, anda perlu menggunakan formula. Oleh itu, dalam Kawasan Jenis Peraturan Pilih kotak dialog Peraturan Format baru, pilih pilihan terakhir Gunakan formula untuk menentukan sel mana yang hendak menjadi format.

Peraturan pertama yang anda ingin buat berkaitan dengan baris tajuk. Khususnya, anda mahu sel -sel ini berada di latar belakang kelabu.

Untuk melakukan ini, luangkan masa untuk menentukan apa yang menjadikan baris tajuk berbeza dari baris lain dalam jadual. Dalam contoh ini, baris tajuk adalah satu -satunya baris dalam lajur G yang tidak mengandungi nombor. Jadi, dalam bidang formula, taip:

 <code>=ISTEXT($G1)</code>

Oleh kerana fungsi istext merawat sel dan sel kosong yang mengandungi teks sebagai nilai teks, peraturan pemformatan bersyarat akan mempertimbangkan sel G1 hingga G3 untuk mengandungi teks, manakala sel -sel lain dalam lajur G mengandungi nilai angka.

Yang penting, menambah tanda dolar ($) sebelum rujukan lajur membaiki pemformatan bersyarat ke lajur ini sambil membenarkan Excel memohon peraturan ke seluruh baris.

Akhirnya, kerana pada mulanya anda memilih data dari lajur A hingga G, format bersyarat akan digunakan untuk keseluruhan baris yang memenuhi syarat.

Sekarang, klik format untuk memilih format baris tajuk. Dalam kes ini, anda mahu mereka menjadi kelabu. Kemudian, klik OK dalam Format Cell dan Edit kotak dialog Peraturan Format.

Apabila anda mengklik Sapukan dalam kotak dialog Pengurus Peraturan Format Bersyarat, anda akan melihat bahawa hanya lajur G mengandungi sel kosong atau teks (dengan kata lain, baris tajuk) dipenuhi dengan kelabu.

Seterusnya, anda mahu memformat baris subtotal supaya mereka dipenuhi dengan hijau muda.

Lihatlah dengan lebih dekat data sekali lagi dan lihat syarat -syarat yang boleh anda gunakan untuk memohon format untuk baris ini sahaja. Dalam contoh ini, baris subtotal mengandungi teks dalam lajur A, tetapi tiada apa -apa dalam lajur B. Selain itu, kerana jumlah baris juga memenuhi syarat -syarat ini, anda perlu mengecualikan mana -mana sel dalam lajur A yang mengandungi perkataan "total".

Dengan kotak dialog Pengurus Peraturan Format Bersyarat masih dibuka, klik Peraturan Baru dan pilih pilihan yang membolehkan anda memformat sel menggunakan formula. Kali ini, dalam bidang formula, taip:

 <code>=AND($A1"",$B1="",$A1"Grand Total")</code>

dalam:

• Fungsi dan membolehkan anda menentukan pelbagai syarat dalam kurungan.
• $ A1 " " "Beritahu Excel untuk mencari sel yang tidak mengandungi () sel kosong (" ") dalam lajur A.
• $ B1 = "" Beritahu Excel untuk mencari sel yang mengandungi (=) sel kosong ("") dalam lajur b, dan
• $ A1 "Grand Total" memberitahu Excel untuk mengecualikan () mana -mana sel dalam lajur A yang mengandungi teks "jumlah besar".

Seperti peraturan sebelumnya, ingatlah untuk memasukkan simbol $ sebelum rujukan lajur untuk membolehkan Excel memohon peraturan yang sama untuk semua baris yang dipilih.

Sekarang, klik format untuk memilih warna isi hijau cahaya, dan selepas menutup sel -sel dialog format dan edit kotak dialog, klik Guna untuk melihat bahawa baris subtotal diisi dengan hijau cahaya.

Akhirnya, anda mahu sel -sel dalam barisan keseluruhan diisi dengan hijau yang lebih gelap.

Oleh kerana jumlah baris adalah satu -satunya baris dalam lajur A yang mengandungi perkataan "total", ini adalah syarat yang boleh anda gunakan untuk pemformatan bersyarat. Dalam kotak dialog Pengurus Peraturan Format Bersyarat, klik Peraturan Baru, dan kemudian pilih pilihan terakhir dalam senarai Jenis Peraturan Pilih. Sekarang, dalam bidang formula, taip:

 <code>=$A1="Grand Total"</code>

Seterusnya, klik format dan pilih warna isi hijau berani yang anda mahu memohon kepada sel yang sepadan dengan keadaan ini. Sekarang apabila anda menutup di dialog Peraturan Format dan mengedit dialog Peraturan Format dan klik Terapkan dalam dialog Pengurus Peraturan Format Bersyarat, anda akan melihat bahawa jumlah baris dalam format ini.

Sekarang bahawa anda telah menggunakan semua peraturan, klik Tutup dalam kotak dialog Pengurus Peraturan Format Bersyarat. Kemudian, laraskan beberapa data dalam jadual asal dan lihat jika keputusan limpahan dan formatnya dikemas kini dengan sewajarnya.

Dalam contoh ini, walaupun saya memadam 12 baris dari jadual data asal, hasil pivotby yang melimpah masih diformatkan dengan betul, tingkah laku tajuk adalah kelabu, tingkah laku subtotal adalah hijau muda, dan jumlah tingkah laku adalah hijau berani.

Jika anda perlu menukar peraturan yang telah anda buat dan digunakan, pilihlah mana -mana sel dalam data dan klik Format Bersyarat> Urus Peraturan untuk memulakan semula Pengurus Peraturan Format Bersyarat. Kemudian, klik dua kali peraturan untuk mengubah keadaannya.

Atas ialah kandungan terperinci Cara memformat pelbagai tumpahan di Excel. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!