Menyiapkan rumus pemformatan bersyarat yang menggunakan referensi campuran. Kebanyakan rumus pemformatan bersyarat membutuhkan referensi absolut. Tetapi spreadsheet ini untuk melacak truk di halaman membutuhkan
Menonton video
- Anderson sedang mencari cara untuk dapat menyalin blok data yang berisi pemformatan bersyarat campuran
- Apakah ada cara untuk menghapus tanda dolar setelah pemformatan bersyarat diatur?
- Tidak - bukan tanpa memperkenalkan lusinan aturan baru
- Solusi saya: sel pembantu yang menggunakan referensi relatif untuk menggantikan referensi campuran dalam format bersyarat
- Teknik lain dalam episode ini:
- Jika Anda memiliki empat aturan pemformatan bersyarat, siapkan 3 pertama dan kemudian buat aturan keempat menjadi warna default
- Hasil # 1: Tekan F2 untuk menghentikan Excel dari memasukkan referensi sel dalam dialog pemformatan bersyarat
- Hasil # 2: menyiapkan pemformatan bersyarat
Transkrip Video
Pelajari Excel dari Podcast Episode 2105: Menyalin Format Bersyarat dengan Referensi Campuran
Hei, selamat datang kembali di netcast. Ini akan menjadi yang rumit hari ini. Saya sedang melakukan seminar kemarin dan salah satu peserta seminar, Anderson, memiliki spreadsheet yang menarik tentang suatu masalah. Baiklah, dan Anderson mengelola halaman - trailer tiba dan trailer harus diturunkan dalam tiga hari. Baiklah, jadi ini - dia mulai, Anda tahu, ini adalah harinya, ini adalah trailer yang tiba dan kemudian dia mengatur format bersyarat yang setelah trailer dibongkar, itu berubah menjadi biru. Begitu sesuatu berwarna biru, semuanya bagus. Tapi kemudian, dia ingin mewarnai sesuatu. Jika ada yang datang hari ini atau kemarin, kode warnanya adalah hijau. Jadi hari ini tanggal 29 Juni 2017 jadi ini sudah tiba kemarin dan semua yang belum dibongkar berwarna hijau tapi kalau sudah lebih dari satu hari,kami ingin menyorot hal-hal sebagai kuning dan ketika sudah lebih dari dua hari, itu adalah masalah yang ingin kami tandai sebagai merah. Dan bukan itu, Anda tahu, ini adalah satu lembar kerja untuk mengatur seluruh halaman, bukan? Bukannya ada lembaran untuk hal-hal yang tiba pada tanggal 26 dan satu lagi untuk tanggal 27 dan satu lagi untuk tanggal 28. Dan Anda tahu kesulitannya adalah saat hari baru datang, mereka menyalin hari sebelumnya ke sini atau ke sini.mereka menyalin hari sebelumnya ke sini atau ke bawah ke sini.mereka menyalin hari sebelumnya ke sini atau ke bawah ke sini.
Baiklah sekarang, inti dari video ini bukanlah tentang cara mengatur pemformatan bersyarat ini. Jadi saya akan mempercepat proses ini, tetapi jika Anda tertarik dengan cara menyiapkan pemformatan bersyarat ini, saya akan meletakkan versi yang tidak dipercepat sebagai hasil keluaran di akhir video.
Oke, jadi begitulah. Mempercepatnya, Anda dapat menonton di bagian akhir untuk melihat cara kerjanya. Hanya melakukan tes disini, CTRL; akan berubah menjadi biru. Jika ini kembali ke 6/26, itu akan berubah menjadi merah dan jika hari ini, itu tidak berfungsi. Itu benar karena inilah yang akan saya lakukan, aturan hijau keempat saya tiba hari ini atau kemarin, saya hanya akan menggunakannya sebagai default. Jika tidak satu pun dari ketiga aturan ini yang benar maka itu akan menjadi hijau sehingga akan memberi saya satu aturan lebih sedikit yang harus saya tangani di sini, oke?
Oke, jadi sekarang kita berada pada titik di mana kita pada dasarnya memiliki masalah Anderson. Saya akan memasukkan 25/6/2017, ini semua akan berubah menjadi merah kecuali yang telah dibongkar. Dan sekarang hidup terus berjalan, itu keesokan harinya. Kami mendapat beberapa trailer pada 6/26 dan kemudian Anderson menyalin data ini, paste di sini, format Column AutoFit, dan ini akan menjadi Trailer 15. Klik untuk menyalinnya turun dan naik, singkirkan yang tiba. Dan yang ini tiba hari ini, jadi ini semua harus berubah menjadi hijau tetapi tidak berubah menjadi hijau. Mengapa mereka tidak berubah menjadi hijau? Mereka tidak berubah menjadi hijau karena rumus ini, rumus pemformatan bersyarat di sini, kita akan melihatnya. Mereka di-hardcode untuk menggunakan $ A $ 1. Oh, itu sangat buruk.
Baiklah, jadi mari kita coba dan perbaiki banyak hal di sini. Hal pertama yang dapat saya lakukan, saya akan menyingkirkan semua itu dan kembali ke kumpulan data asli ini dan menjadi sedikit lebih pintar pada lintasan kedua dan mengatakan bahwa kita tidak perlu menguncinya ke Kolom A. Aku akan menyingkirkan tanda $ itu. Dengan kata lain, kolom itu akan selalu menjadi kolom di sebelah kiri kita sehingga itu akan menjadi referensi campuran tetapi kita harus selalu menunjuk ke $ 1. Kami akan mengedit aturan ini, klik OK. Baiklah sekarang, dengan satu perubahan itu ketika kita menyalin ke kanan dan memasukkan data baru, seperti tanggal hari ini, itu berfungsi. Oke, jadi ini bagus. Hidup akan indah pada 26/6 dan hidup akan menyenangkan pada 27/6. Baiklah, bekerja dengan baik. Tapi sekarang kami mengalami masalah di mana kami kehabisan ruang pada halaman dan apa yang telah dilakukan Anderson adalah turun,dasarnya memulai baris baru dan menempel dan ini akan menjadi 6/28 tetapi tidak berubah menjadi hijau.
Mengapa tidak berubah menjadi hijau? Ini tidak berubah menjadi hijau karena saya masih harus menggunakan $ untuk kembali ke 1. Baiklah, dan sekarang inilah teka-teki, inilah masalahnya. Apa yang kamu lakukan sekarang? Dan aku serius, apa yang kamu lakukan sekarang? Saya ingin mendengar di komentar YouTube apa yang akan Anda lakukan sekarang.
Anda tahu, jadi hei lihat, ada argumen yang dibuat bahwa ini bagus, kita bisa berhenti di sini karena dengan menggunakan A $ 1, kita membuatnya seperti itu, hidup mudah di Hari 1, salin ke Hari 2, hidup itu hebat . Kehidupan hari ke-3 itu luar biasa. Hanya setiap hari ke-4 ketika kita menyalin di sini bahwa Anderson harus masuk dan menyiapkan pemformatan bersyarat, mengedit yang ini, mengedit aturan, mengubah 1 menjadi 18. Klik OK, edit aturan ini dan ubah 1 menjadi 18. Klik OK, klik OK. Baiklah, jadi Hari 4, penyesuaian kecil itu disalin untuk Hari 5, salin untuk Hari 6 dan kemudian salin untuk Hari 7. Lakukan langkah-langkah itu lagi. Tapi, hei, hadapi saja. Lembar kerja ini dibuat enam bulan lalu dengan aturan pemformatan bersyarat ini dan mereka hanya perlu bekerja. Kita tidak perlu masuk dan melakukan pemformatan bersyarat lagi dan lagi dan lagi.
Reaksi pertama saya adalah saya akan berpura-pura seperti ini adalah spreadsheet di mana saya memiliki beberapa rumus di sini dan rumus tersebut dibuat dengan referensi absolut tetapi saya membutuhkan rumus tersebut untuk dapat disalin ke atas atau ke bawah, dan menjadi relatif dalam salinan - baik saat saya salin ke sini maupun saat saya salin ke sini. Baiklah, dan untuk membuatnya bekerja, saya akan menggunakan referensi absolut ketika saya mengatur semuanya tapi kemudian saya akan menggunakan Find and Replace, Ctrl H. Dan katakanlah mari kita singkirkan referensi relatif tersebut, ubah setiap $ A $ 1 menjadi A1, Ganti Semua, klik Tutup dan sekarang blok ini, semua rumus ini berbeda sepenuhnya, salin, tempel, dan tempel dan itu akan berfungsi. Itu akan menjadi relatif. Jadi saya berkata, baiklah, itulah yang perlu kita lakukan. Kita perlu mengeluarkan $ itu dari rumus.Jadi saya akan menulis makro yang memungkinkan saya mengedit setiap aturan pemformatan bersyarat ini. Baiklah, dan sebelum saya menulis makro itu, saya akan merekam makro untuk mengubah satu aturan pemformatan bersyarat, tetapi bukan karena ada 14 aturan pemformatan bersyarat di sini. Ini bahkan bukan untuk 14 * 3, 42 aturan pemformatan bersyarat di sini. Hanya ada 3 aturan pemformatan bersyarat di sini dan kami menerapkan 3 aturan pemformatan bersyarat tersebut ke berbagai sel.Hanya ada 3 aturan pemformatan bersyarat di sini dan kami menerapkan 3 aturan pemformatan bersyarat ke berbagai sel.Hanya ada 3 aturan pemformatan bersyarat di sini dan kami menerapkan 3 aturan pemformatan bersyarat ke berbagai sel.
Jadi jika saya akan mengubahnya, hal pertama yang harus saya lakukan adalah mengambil 3 aturan pemformatan bersyarat ini dan menjadikannya 42 aturan pemformatan bersyarat. Dan kemudian, saya mulai merasa ngeri karena saat Anderson menyalin dari sini ke sini, dia akan memperkenalkan 42 aturan baru dan 42 aturan baru. Dan selama satu lembar kertas dengan kemungkinan 15 hari, dia akan memperkenalkan lebih dari 600 aturan, 600 format berbeda dan itu akan menjadi mengerikan. Anda akhirnya akan terkena terlalu banyak aturan pemformatan, belum lagi akan sulit untuk menyiapkannya bahkan jika kami memiliki makro untuk menyiapkannya. Ini akan sulit untuk disiapkan.
Baiklah, jadi apa yang kita lakukan? Inilah yang saya pikirkan dan saya ingin mendengar apakah Anda memiliki sesuatu yang lebih baik dari itu. Saya berkata kepada Anderson, saya berkata, "Anda tahu, ini terlihat sangat sederhana. Semua ini melihat pada satu kalkulasi dan kalkulasi itu adalah = HARI INI- tanggal di sebelah kiri saya. " Dan bukankah itu keren, jika kita bisa mendapatkan jawaban itu di kolom pembantu kecil di sebelah kanan sini. Dan pada kenyataannya, kita tidak perlu menggunakan $ sama sekali, kita hanya akan meletakkan semua sel itu dengan rumus kecil yang sederhana itu.
Saya bisa melihat raut wajah Anderson, dia tidak ingin hal-hal ekstra di luar sana terhapus tapi tidak apa-apa. Kita bisa bersembunyi, menyembunyikannya nanti jadi kita kembali ke sel ini dan masuk ke format kondisional kita. Seluruh TODAY-A1 hanya akan mengarah ke C3 dan itu akan menjadi referensi relatif. Jadi dengan kata lain, di sel mana pun kita berada, kita akan selalu mencari di sel sebelah kanan, klik OK, tulis di sel 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 hal yang persis sama di sana. Saya akan menekan 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.
Oke, jadi begitulah. Mempercepatnya, Anda dapat menonton di bagian akhir untuk melihat cara kerjanya. Hanya melakukan tes disini. CTRL; akan berubah menjadi biru. Jika ini kembali ke 6/26, itu akan berubah menjadi merah. Dan jika hari ini, itu tidak berfungsi. Itu benar karena inilah yang akan saya lakukan. Aturan keempat saya, hijau tiba hari ini atau kemarin, saya hanya akan menggunakannya sebagai default. Jika tidak satu pun dari ketiga aturan ini yang benar maka itu akan menjadi hijau sehingga akan memberi saya satu aturan yang lebih sedikit yang harus saya tangani di sini. Baik.
Unduh berkas
Unduh file contoh di sini: Podcast2105.xlsx
