Memformat warna sel berdasarkan nilai di lembar dan sel lain

Saya memiliki buku kerja dengan dua lembar. Saya ingin memformat warna latar belakang sel di kolom pertama lembar 1 berdasarkan nilai di kolom kedua lembar 2.

Misalnya, jika nilai sheet2, baris 6, kolom 2 katakanlah, 4, maka saya ingin warna latar belakang lembar 1, baris 4, kolom 1 menjadi hijau. Jika tidak ada nilai dalam lembar 2, kolom 2 yang merujuk ke baris tertentu dalam lembar 1, saya ingin membiarkannya disetel ke tanpa warna. Tidak ada larangan nilai yang sama muncul beberapa kali di kolom kedua lembar 2. Bonus pujian jika Anda dapat memberi tahu saya cara menghapus warna jika nilai terakhir di lembar 2 yang menunjuk ke baris di lembar 1 dihapus.

Saya yakin bahwa bagi para ahli Excel di luar sana, hal ini bisa jadi sepele, tetapi saya jarang memiliki kesempatan untuk menggunakan excel dan tentu saja tidak punya waktu untuk menjadi sabuk hitam di dalamnya. Adakah yang bisa memberikan saran, petunjuk, atau rumus cepat untuk melakukan ini? Jika ini akan membutuhkan kode VB yang rumit untuk diterapkan, itu tidak layak.

Terima kasih!

Pemrograman
excel
16 Januari 2009 в 5:31
34 tayangan
Larutan

Inilah cara saya melakukannya di Excel 2003 menggunakan pemformatan bersyarat.

Untuk menerapkan pemformatan bersyarat ke Sheet1 menggunakan nilai dari Sheet2, Anda perlu mencerminkan nilai ke dalam Sheet1.

Membuat cerminan dari Sheet2, kolom B di Sheet 1

  1. Buka Sheet1.

  2. Sisipkan kolom baru dengan mengklik kanan header kolom A dan pilih "Insert".

  3. Masukkan rumus berikut ke dalam A1:

    =IF(ISBLANK(Sheet2!B1),"",Sheet2!B1)

  4. Salin A1 dengan mengklik kanan dan memilih "Copy".

  5. Tempelkan rumus ke dalam kolom A dengan mengklik kanan headernya dan memilih "Paste".

Sheet1, kolom A seharusnya sekarang persis mencerminkan nilai di Sheet2, kolom B.

(Catatan: jika anda tidak menyukainya di kolom A, akan sama baiknya jika anda memilikinya di kolom Z atau dimanapun juga).

Menerapkan pemformatan bersyarat

  1. Tetap pada Sheet1.
  2. Pilih kolom B dengan mengklik kiri headernya.
  3. Pilih item menu Format > Conditional Formatting...
  4. Ubah Condition 1 ke "Formula is" dan masukkan formula ini:
<code>=MATCH(B1,$A:$A,0)</code>
  1. Klik tombol Format... dan pilih latar belakang hijau.

Anda sekarang akan melihat latar belakang hijau diterapkan ke sel yang cocok di Sheet1.

Menyembunyikan kolom cermin

  1. Tetap pada Sheet1.
  2. Klik kanan header pada kolom A dan pilih "Hide".

Ini akan secara otomatis memperbaharui Sheet1 kapanpun apapun di Sheet2 diubah.

mskfisher
Jawaban edit 16 Januari 2009 в 9:13
Komentar (2)

Saya menggunakan Excel 2003 -

Masalah dengan menggunakan pemformatan bersyarat di sini adalah Anda tidak dapat merujuk lembar kerja atau buku kerja lain dalam kondisi Anda. Apa yang dapat Anda lakukan adalah mengatur beberapa kolom pada lembar 1 sama dengan kolom yang sesuai pada lembar 2 (dalam contoh Anda = Lembar2!B6). Saya menggunakan Kolom F dalam contoh saya di bawah ini. Kemudian Anda dapat menggunakan pemformatan bersyarat. Pilih sel di Sheet 1, baris, kolom 1 dan kemudian pergi ke menu pemformatan bersyarat. Pilih " Formula Is &" dari drop down dan setel kondisinya ke " = $ F $ 6 = 4 &". Klik pada tombol format dan kemudian pilih tab Patterns. Pilih warna yang Anda inginkan dan selesai.

Anda dapat menggunakan alat pelukis format untuk menerapkan pemformatan bersyarat ke sel lain, tetapi ketahuilah bahwa secara default Excel menggunakan referensi absolut dalam kondisi tersebut. Jika Anda ingin mereka menjadi relatif, Anda harus menghapus tanda dolar dari kondisi tersebut.

Anda dapat memiliki hingga 3 kondisi yang diterapkan ke sel (gunakan tombol add >> di bagian bawah dialog Conditional formatting) jadi jika baris terakhir tetap (misalnya, Anda tahu bahwa itu akan selalu menjadi baris 10) Anda dapat menggunakannya sebagai kondisi untuk mengatur warna latar belakang menjadi tidak ada. Dengan asumsi bahwa nilai terakhir yang Anda pedulikan ada di baris 10 kemudian (masih dengan asumsi bahwa Anda telah mengatur kolom F pada sheet1 ke sel yang sesuai pada lembar 2) kemudian atur kondisi pertama ke Rumus Apakah = $ F $ 10 = &" &" dan pola ke Tidak ada. Jadikan itu kondisi pertama dan itu akan mengesampingkan pernyataan berikut yang bertentangan.

Komentar (2)

Saya telah melakukan ini sebelumnya dengan pemformatan bersyarat. Ini adalah cara yang bagus untuk memeriksa sel secara visual di buku kerja dan melihat outlier dalam data Anda.

Komentar (1)