Menyiapkan formula pemformatan bersyarat yang menggunakan rujukan campuran. Sebilangan besar formula pemformatan bersyarat memerlukan rujukan mutlak. Tetapi hamparan ini untuk mengesan trak di halaman memerlukan
Tonton Video
- Anderson sedang mencari cara untuk dapat menyalin blok data yang mengandungi format bersyarat campuran
- Adakah cara untuk membuang tanda dolar setelah format bersyarat disiapkan?
- Tidak - tidak tanpa memperkenalkan berpuluh-puluh peraturan baru
- Penyelesaian saya: sel pembantu yang menggunakan rujukan relatif untuk menggantikan rujukan campuran dalam format bersyarat
- Teknik lain dalam episod ini:
- Sekiranya anda mempunyai empat peraturan pemformatan bersyarat, sediakan 3 yang pertama dan kemudian jadikan peraturan keempat menjadi warna lalai
- Keluar # 1: Tekan F2 untuk menghentikan Excel memasukkan rujukan sel dalam dialog pemformatan bersyarat
- Keluar # 2: menetapkan pemformatan bersyarat
Transkrip Video
Belajar Excel dari Podcast Episode 2105: Menyalin Format Bersyarat dengan Rujukan Campuran
Hai, selamat datang kembali ke siaran net. Ini akan menjadi rumit hari ini. Saya melakukan seminar semalam dan salah seorang daripada orang yang berada dalam seminar itu, Anderson, mempunyai hamparan yang menarik dengan masalah. Baiklah, dan Anderson menguruskan halaman - treler tiba dan treler mesti dibongkar dalam masa tiga hari. Baiklah, jadi ini - dia bermula, anda tahu, ini adalah hari, ini adalah treler yang tiba dan kemudian dia telah mengatur format bersyarat bahawa setelah treler dimuat, ia berubah menjadi biru. Setelah sesuatu berwarna biru semuanya menjadi hebat. Tetapi kemudian, dia mahu mewarnai kod perkara. Sekiranya sesuatu tiba hari ini atau semalam, ia akan diberi warna yang berwarna hijau. Jadi hari ini adalah 29 Jun 2017, jadi ini tiba semalam dan semua yang tidak dibongkar berwarna hijau tetapi ketika berusia lebih dari satu hari,kami mahu menyerlahkan perkara seperti kuning dan ketika berusia lebih dari dua hari, itu adalah masalah yang ingin kami tonjolkan sebagai merah. Dan bukan begitu, anda tahu, ini adalah satu lembaran kerja untuk menguruskan seluruh halaman, bukan? Bukannya ada helaian untuk perkara-perkara yang tiba pada 26 dan satu lagi untuk 27 dan satu lagi untuk 28. Dan anda tahu kesukarannya adalah ketika hari baru akan datang, mereka menyalin hari sebelumnya ke sini atau ke sini.mereka menyalin hari sebelumnya ke sini atau ke sini.mereka menyalin hari sebelumnya ke sini atau ke sini.
Baiklah sekarang, intinya dari video ini bukan mengenai cara menyediakan format bersyarat ini. Oleh itu, saya akan mempercepat ini tetapi jika anda berminat untuk mengatur format bersyarat ini, saya akan meletakkan versi yang tidak pantas sebagai jalan keluar pada akhir video.
Baiklah, jadi kita ada. Lanjutkan, anda boleh menonton di hujungnya untuk melihat bagaimana ia berfungsi. Cuma melakukan ujian di sini, CTRL; akan bertukar menjadi biru. Sekiranya ini kembali ke 6/26, ia akan berubah menjadi merah dan jika hari ini, ia tidak berfungsi. Betul kerana inilah yang akan saya lakukan, peraturan keempat saya tiba hari ini atau semalam, saya hanya akan menggunakannya sebagai lalai. Sekiranya tidak ada ketiga-tiga peraturan ini yang benar, maka akan menjadi hijau kerana ia akan memberi saya satu peraturan yang kurang harus saya hadapi di sini, okey?
Baiklah, jadi sekarang kita berada di titik di mana kita pada dasarnya mempunyai masalah Anderson. Saya akan masukkan pada 25/6/2017, semuanya akan menjadi merah kecuali yang telah dimuat turun. Dan sekarang kehidupan terus berjalan, ia adalah keesokan harinya. Kami mendapat beberapa treler pada 6/26 dan oleh itu Anderson menyalin data ini, tampal di sini, format Column AutoFit, dan ini akan menjadi Trailer 15. Pergi klik untuk menyalinnya dan naikkan, hilangkan yang telah tiba. Dan yang ini tiba hari ini, jadi ini semua mesti bertukar menjadi hijau tetapi mereka tidak berubah menjadi hijau. Mengapa mereka tidak berubah menjadi hijau? Mereka tidak berubah menjadi hijau kerana formula ini, formula pemformatan bersyarat di sini, kita akan melihatnya. Mereka sukar menggunakan $ A $ 1. Oh, itu sungguh teruk.
Baiklah, jadi mari kita cuba dan memperbaikinya di sini. Perkara pertama yang boleh saya lakukan, saya akan menyingkirkan semua itu dan kembali ke set data asal ini dan menjadi sedikit lebih bijak pada hantaran kedua dan mengatakan bahawa kita tidak perlu menguncinya ke Lajur A. Saya akan menghilangkan tanda $ itu. Dengan kata lain, ini selalu menjadi lajur di sebelah kiri kita sehingga menjadi rujukan campuran tetapi kita harus selalu menunjukkan $ 1. Kami akan mengedit peraturan ini, klik OK. Baiklah sekarang, dengan perubahan itu ketika kita menyalin ke kanan dan memasukkan data baru, seperti tarikh hari ini, ia berfungsi. Baiklah, jadi ini bagus. Hidup akan menjadi hebat pada 6/26 dan hidup akan menjadi hebat pada 6/27. Baiklah, bekerja dengan hebat. Tetapi sekarang kita menghadapi masalah di mana kehabisan ruang di halaman dan jadi apa yang dilakukan oleh Anderson adalah menurun,pada dasarnya memulakan baris dan pasta baru dan ini akan menjadi 6/28 tetapi tidak berubah menjadi hijau.
Mengapa ia tidak berubah menjadi hijau? Ini tidak berubah menjadi hijau kerana saya masih harus menggunakan $ untuk kembali ke 1. Baiklah, dan sekarang inilah teka-teki, inilah masalahnya. Apa yang kamu buat sekarang? Dan saya serius, apa yang anda buat sekarang? Saya ingin mendengar di YouTube komen apa yang akan anda lakukan sekarang.
Anda tahu, jadi hei, ada argumen yang dibuat bahawa ini bagus, kita boleh berhenti di sini kerana dengan menggunakan A $ 1, kita berjaya seperti itu, kehidupan itu mudah pada Hari 1, salin ke Hari 2, hidupnya hebat . Kehidupan hari ke-3 sungguh hebat. Setiap hari ke-4 apabila kita menyalin di sini, Anderson harus masuk dan menyiapkan pemformatan bersyarat, mengedit yang ini, mengedit peraturan, mengubahnya menjadi 18. Klik OK, edit peraturan ini dan ubah 1 menjadi 18. Klik OK, klik OK. Baiklah, jadi Hari 4, salinan sedikit penyesuaian untuk Hari 5, salin untuk Hari 6 dan kemudian salin semula untuk Hari 7. Lakukan langkah-langkah itu lagi. Tapi hei, mari kita hadapi. Lembaran kerja ini disediakan enam bulan yang lalu dengan peraturan pemformatan bersyarat ini dan mereka hanya perlu berfungsi. Kami tidak perlu masuk dan melakukan format bersyarat lagi dan lagi dan lagi.
Reaksi pertama saya ialah saya akan berpura-pura seperti ini adalah spreadsheet di mana saya mempunyai beberapa formula di sini dan formula tersebut dibina dengan rujukan mutlak tetapi saya memerlukan formula tersebut untuk dapat disalin di atas atau di bawah, dan menjadi relatif dalam salinannya - baik ketika saya menyalin ke sini dan ketika saya menyalin ke sini. Baiklah, dan untuk menjadikannya berfungsi, saya akan menggunakan rujukan mutlak ketika saya menyiapkan sesuatu tetapi kemudian saya akan menggunakan Cari dan Ganti, Ctrl H. Dan katakan mari kita hilangkan rujukan relatif itu, tukar setiap $ A $ 1 ke A1, Ganti Semua, klik Tutup dan sekarang blok ini, semua formula ini berbeza, salin, tampal dan tampal sehingga ia akan berfungsi. Itu akan menjadi relatif. Jadi saya katakan, baiklah, itulah yang perlu kita lakukan. Kita perlu mengeluarkan $ tersebut daripada formula.Oleh itu, saya akan menulis makro yang membolehkan saya mengedit setiap peraturan pemformatan bersyarat ini. Baiklah, dan sebelum saya menulis makro itu, saya akan merakam makro mengubah satu peraturan pemformatan bersyarat, tetapi tidak ada 14 peraturan pemformatan bersyarat di sini. Bahkan tidak mengikut peraturan pemformatan bersyarat 14 * 3, 42 di sini. Hanya ada 3 peraturan pemformatan bersyarat di sini dan kami menerapkan 3 peraturan pemformatan bersyarat itu ke sebilangan sel.hanya 3 peraturan pemformatan bersyarat di sini dan kami menerapkan 3 peraturan pemformatan bersyarat itu ke sebilangan sel.hanya 3 peraturan pemformatan bersyarat di sini dan kami menerapkan 3 peraturan pemformatan bersyarat itu ke sebilangan sel.
Oleh itu, jika saya mengubahnya, perkara pertama yang harus saya lakukan adalah mengambil 3 peraturan pemformatan bersyarat ini dan menjadikannya 42 peraturan pemformatan bersyarat. Dan kemudian, saya mula merasa ngeri kerana ketika Anderson menyalin dari sini ke sini, dia akan memperkenalkan 42 peraturan baru dan kemudian 42 peraturan baru. Dan selama satu helai kertas dengan kemungkinan 15 hari, dia akan memperkenalkan lebih dari 600 peraturan, 600 format yang berbeza dan itu akan menjadi mengerikan. Anda akhirnya akan memukul terlalu banyak peraturan peraturan pemformatan, apatah lagi sukar untuk disiapkan walaupun kita mempunyai makro untuk menyiapkannya. Akan sukar untuk disiapkan.
Baiklah, jadi apa yang kita buat? Inilah yang saya buat dan saya ingin mendengar sekiranya anda mempunyai sesuatu yang lebih baik daripada itu. Saya berkata kepada Anderson, saya berkata, “Anda tahu, nampaknya cukup sederhana. Semua ini melihat satu pengiraan dan pengiraannya adalah = HARI INI - tarikh di sebelah kiri saya. " Dan bukankah itu keren, jika kita dapat jawapan itu di ruangan penolong kecil di sini di sebelah kanan. Dan sebenarnya, kita tidak perlu menggunakan $ sama sekali, kita akan meletakkan semua sel-sel itu dengan formula kecil yang mudah.
Saya dapat melihat wajah Anderson, dia tidak mahu barang tambahan di luar sana terhapus tetapi tidak mengapa. Kita dapat menyembunyikan, menyembunyikannya kemudian sehingga kita kembali ke sel-sel ini dan memasuki format bersyarat kita. Seluruh HARI INI-A1 hanya akan menunjuk ke C3 dan itu akan menjadi rujukan relatif. Oleh itu, dengan kata lain, sel apa sahaja yang ada di dalamnya, kita akan sentiasa melihat sel di sebelah kanan, klik OK, tulis di sebelah ini, klik OK. Kami ingin menyembunyikan data ini di sini jadi saya akan masuk dan CTRL 1. Saya akan menggunakan tiga titik koma - ;;;;, klik OK. Saya akan melakukan perkara yang sama di sana. Saya akan tekan F4, ulangi tindakan terakhir.
Now, the weird thing here is I have to convince myself that this little part, this blank column is part of the whole thing. So I want to just add a light grey there to remind myself that when I copy and paste, I'm going to have to include the grey. Alright, so here's our test now. I will choose this CTRL C and then I'm actually going to paste there and paste there and paste here. Alright, big tests go to 6/26, go to 6/27, change this to yellow, come to 6/28, it should change to green. Beautiful!
Alright, so now it's working, we have essentially replaced that conditional formatting mixed reference with a relative reference and we should be relatively, relatively good to go.
Alright, topics in this episode. We're looking for a way to be able to copy blocks of data containing conditional formatting that essentially is a mixed reference. So, is there some way to remove the $ once the conditional formatting is set up? Well maybe with a macro but you'd be introducing dozens of new rules instead of just one formula applying to a whole block. So my solution was to use helper cells that use relative references and then just use regular references in the conditional formatting.
Other topics in this episode, if you have 4 conditional formatting rules just set the first three and make the fourth rule be the default color. The outtake coming up next is press F2 to stop Excel from inserting cell references in the conditional formatting dialogue and then setting up the conditional formatting dialogue.
Well, I want to thank Anderson for being in my seminar and hopefully, you know, this helps him. I want to thank you for stopping by. We'll see you next time for another netcast from.
I'll take number 1. When you're dealing with conditional formatting in that stupid dialogue box and you need to edit something that's already in there, you better be darn good at clicking in the right spot. Like if I wanted to change that 1 to be 18, and I clicked right there and then press the Right Arrow key then I have to swear because they're inserting cell references instead. Alright, and so many times when I was recording this episode, I clicked in the wrong spot and hit the Right Arrow key or the Left Arrow key or Shift Arrow key and how to back out of it.
Alright, here is the key if this has been driving you crazy for years. Well key number 1, just be perfect about where you click. Right then you don't have any problem at all, life is great but that's not realistic. Here's the whole trick. When you are in this dialogue box, down here in the lower left-hand corner it says that we are in Enter mode and when you're in Enter mode using Left or Right Arrow keys is going to insert cell references for you like that, right? Really, really annoying. But what you want to do is you want to press the F2 key and that changes us from Enter mode to Edit mode. Bingo! Now we can do whatever we want. We can use the Left Arrow key or the Right Arrow key and we're not inserting cells like that.
Alright, next up is I'll take number 2 where I built this original conditional formatting. I showed that in fast motion before here. Just in case you're interested is the slow motion.
So we're going to have a date here. I'm going to put in yesterday's date just or two days ago date so we have something - this is going to work. Alright, and we're going to assume that there's going to be some number of space for trailers to come in. In this case I'll go down to Trailer 14 and then here, we're going to build conditional format. And there are four rules that we want to do. And the first one, the easy one is if there's a date here then we’re going to turn this thing blue, so alt="" O D to get into conditional formatting. I'm going to create a new rule and that new rule is going to be the easy one format, only cells that contain a value that is greater than 0. Then we're going to format this using a blue color like that, click OK, click OK. Alright, first rule done.
Second rule is the thing- the date up in A1, more than one days old. This one is going to be the tricky one and this is where we have to look at a specific cell. So I’m going to have to use a formula and we'll say =TODAY, today will be today’s date minus that date up in A1. If that is>2,>1 then we're going to format it in yellow.
Alright, and I don't know if I need parentheses here, I'm going to just be safe and put the ( ) in and copy that whole thing so I can create the red color. So copy and then we'll add a new rule and rule is going to be if it’s> or =2, we’ll format as red.
Alright, now we have to be careful here. The first thing I want to do is I want to check to see if the thing is filled in. If the thing is filled in, we get the blue, we stop if true. Then the next thing to do, we have to check for the red before we check for the yellow because this formula for yellow is also going to be true on the days when it should be red.
Baiklah, jadi kita ada. Lanjutkan, anda boleh menonton di hujungnya untuk melihat bagaimana ia berfungsi. Hanya melakukan ujian di sini. CTRL; akan bertukar menjadi biru. Sekiranya ini kembali ke 6/26, ia akan berubah menjadi merah. Dan jika hari ini, ia tidak berfungsi. Betul kerana inilah yang akan saya lakukan. Peraturan keempat saya, hijau tiba hari ini atau semalam saya hanya akan menggunakannya sebagai lalai. Sekiranya tidak ada ketiga-tiga peraturan ini yang benar, maka akan menjadi hijau kerana ini akan memberi saya satu peraturan yang kurang harus saya hadapi di sini. Baiklah.
Muat turun fail
Muat turun fail contoh di sini: Podcast2105.xlsx
