Haddii aad isticmaashay in aad isticmaasho noocyadii hore ee Excel , fursadaha qaabaynta shuruudaha ee Excel 2007 , 2010, iyo 2013 ayaa ku yaabi doona. Haddaba maxaad u doonaysaa inaad u dhibto isticmaalka qaabaynta shuruudaysan? Hagaag, halkan waxaa ah dhowr sababood oo aan u jeclahay isticmaalka habkan Excel :

1. Si aad xogtaada uga dhigto mid soo jiidasho leh.

2. Si aad xaashida faafintaada uga dhigto mid sahlan in la fahmo marka la eego.

3. Si loo aqoonsado noocyada tirooyinka qaarkood si looga caawiyo xallinta dhibaatada.

4. Si uu kaaga caawiyo in aad gunaanad ka soo saarto xogtaada.

5. Si muuqaal ahaan loogu muujiyo isticmaalaha waxa "wanaagsan" ama "xun" adoo isticmaalaya cagaar iyo casaan.

Hadda, waxaad isticmaali kartaa qaabaynta shuruudaha si aad u qaabayso unug kasta oo kala duwan iyadoo lagu saleynayo shuruudahaaga (waxaana jira xulashooyin badan oo qaabaynta oo laga dooran karo). Tusaale ahaan, haddii aad haysato xaashi faa'iido oo aad rabto in aad midabayso dhammaan faa'iidooyinka ka weyn $200 sida cagaar iyo dhammaan faa'iidooyinka ka yar $200 sida jaalaha ah iyo dhammaan khasaarooyinka sida casaanka, markaa waxaad isticmaali kartaa qaabeynta shuruudaha si aad si dhakhso ah u qabato dhammaan shaqada adiga. .

Qaabaynta shuruuda ah ee Excel

Habaynta shuruuda ah waxay awood kuu siinaysaa inaad si dhakhso leh oo fudud u qaabayso xog tiro badan - adigoo wali awood u leh inaad kala soocdo noocyada kala duwan ee xogta. Waxaad u abuuri kartaa sharciyo ikhtiyaarada qaabaynta kuwaas oo u oggolaanaya Microsoft Excel inay si toos ah kuu qaabayso. Runtii waa inaad raacdaa saddex tallaabo oo fudud.

Tallaabada 1:^{(Step 1:)} Dooro unugyada aad rabto in aad qaabeyso.

Talaabada 2:^{(Step 2:)} Guji badhanka Qaabaynta Shuruudaha^{(Conditional Formatting)} ah ee hoos yimaada menu -ka guriga^(Home) , qaybta Styles .

Tallaabada 3:^{(Step 3:)} Dooro xeerarkaaga. Waxa jira Xeerarka Unugyada Highlight^{(Highlight Cells Rules)} iyo Xeerarka Top/Bottom Rules  ee ugu sarreeya kuwaas oo kuu oggolaanaya inaad samayso isbarbardhigga qiyamka. Tusaalahan, waxaanu soo rogey saddex xeer. Midda kowaad waxay ahayd in qiimo kasta oo ka badan $200 uu ahaa cagaar.

Waxa xusid mudan in qaybta Xeerarka Unugyada Highlight^{(Highlight Cells Rules)} sidoo kale loo isticmaali karo in lagu barbar dhigo kaydka xog-ururinta. Wax kasta oo kale ayaa kaliya isticmaali doona hal xog-ururin ah oo aad iftiimisay oo is barbar dhig qiyamka midba midka kale. Tusaale ahaan, marka la isticmaalayo qaanuunka Greater Than , waxaan barbardhigi karaa qiyamka A1 ilaa A20 tiro gaar ah ama waxaan barbar dhigi karaa A1 ilaa A20 marka loo eego B1 ilaa B20 .

Isla caqli-gal ayaa lagu dabaqay xeerarkii labaad iyo saddexaad. Xeerka labaad wuxuu ahaa in wax kasta oo u dhexeeya $0 iyo $200 loo qaabeeyey jaale. Xeerka saddexaad wuxuu ahaa in wax ka yar $0 loo qaabeeyey casaan. Waa kan sida ay u egtahay qayb xaashida la dhammeeyey.

Haddii aadan jeclayn xulashooyinka qaabayntan, Excel waxa uu leeyahay doorashooyin Habayn ^(Excel)Shuruudeed^{(Conditional Formatting)} oo badan oo kala duwan oo aad ka isticmaali karto. Tusaale ahaan, waxaad gelin kartaa calaamado sida fallaadho midab leh ( Icon Sets ), jaantusyada bar sida tusaalaha labaad ( Baarrada Xogta^{(Data Bars)} ), ama xitaa noocyo kala duwan oo midabyo ah oo si toos ah loo doortay sida tusaalaha ugu dambeeya ( Miisaanka Midabka^{(Color Scales)} ). Saddexdan doorasho ayaa kaliya is barbar dhigaya qiyamka isla xog-ururinta. Haddii aad doorato A1 ilaa A20 , waxay is barbardhigi doontaa qiyamkaas midba midka kale.

Haddii aad mar dambe go'aansato inaadan rabin in unugyadaada qaab shuruudaysan loo qaabeeyey, waxa kaliya ee ay tahay inaad samayso ayaa ah inaad nadiifiso qaabaynta. Si tan loo sameeyo, dooro badhanka Qaabaynta Shuruuda ah oo dooro ^{(Conditional Formatting)}Xeerarka Nadiifi^{(Clear Rules)} . Kadib, ka dooro inaad rabto inaad ka nadiifiso xeerarka unugyada la doortay oo keliya ama warqadda shaqada oo dhan.

Sidoo kale, haddii aad samaysay xeerar dhowr ah, waxaa laga yaabaa inaad illowdo xeerarkii aad ku dabaqday unugyadee. Maadaama aad xeerar badan ku dabaqi karto isla unugyo isku mid ah, waxay noqon kartaa wax jahawareer gaar ahaan haddii qof kale uu sameeyay xaashida. Si aad u aragto dhammaan sharciyada, dhagsii badhanka Qaabaynta Shuruuda^{(Conditional Formatting)} ah ka dibna dhagsii Maamul Xeerarka.^{(Manage Rules.)}

Marka aad haysato wax ka badan hal xeer oo lagu dabaqo isla tirada unugyada, xeerarka waxaa loo qiimeeyaa sida ay u kala horreeyaan sare ilaa mudnaanta hoose. Sida caadiga ah, sharciga cusub ee lagu daray wuxuu yeelan doonaa mudnaanta sare. Waxaad taas ku bedeli kartaa adigoo gujinaya qaanuunka ka dibna isticmaalaya badhamada fallaadha kor iyo hoos si aad u beddesho nidaamka. Sidoo kale, waxaad riixi kartaa hoos udhaca ugu sarreeya oo waxaad arki kartaa sharciyada kaliya xulashada hadda ama xaashi kasta oo ku jirta buugga shaqada.

Waxa kale oo jira sanduuqa hubinta ee la yiraahdo Jooji Haddii Run^{(Stop If True)} , kaas oo aanan halkan ku faahfaahin doonin sababtoo ah waa arrin adag. Si kastaba ha ahaatee, waxaad ka akhrisan kartaa qoraalkan Microsoft oo si faahfaahsan u sharaxaya.

Fursadaha Habaynta Shuruuda Cusub ee Excel 2010^{(New Conditional Formatting Options Excel 2010)}

Kaliya wax walba waa isku mid Excel 2010 marka ay timaado qaabeynta shuruuda^{(Conditional Formatting)} ah ee lagu soo daray Excel 2007 . Si kastaba ha ahaatee, waxaa jira hal sifo oo cusub oo runtii ka dhigaysa mid aad u xoog badan.

Horaan u sheegay in qaybta Xeerarka Unugyada Highlight^{(Highlight Cells Rules)} ay kuu ogolaanayso inaad barbar dhigto hal xog iyo xog kale oo isla xaashida faafinta ah. Sannadka 2010, waxaad hadda tixraaci kartaa xaashi kale oo shaqo isla buugga shaqada. Haddii aad isku daydo inaad tan ku sameyso Excel 2007 , waxay kuu ogolaan doontaa inaad ka doorato xogta xaashi kale oo shaqo, laakiin waxay ku siin doontaa fariin qalad ah markaad isku daydo inaad gujiso OK dhamaadka.

In Excel 2010 , hadda waad sameyn kartaa tan, laakiin waa xoogaa khiyaano ah sidaa darteed waxaan doonayaa inaan u sharaxo tallaabo tallaabo. Aynu nidhaahno waxaan haystaa laba warqadood oo shaqo, xaashi kastana waxaan hayaa xog ka socota B2 ilaa B12 wax la mid ah faa'iido. Haddi aan rabo inaan arko qiimayaasha B2 ilaa B12 ee xaashida 1 ka wayn qiyamka B2 ilaa B12 ^{(Great Than)}ee^(B12) xaashida 2, waxaan marka hore dooran lahaa qiimayaasha B2 ilaa B12 ee xaashida 1^(B12)

Hadda dhagsii badhanka tixraaca unugga ee aan kor ku muujiyey. Sanduuqa ayaa isbedeli doona oo calaamada calaamaduhu waxay noqon doontaa iskutallaab cad. Hadda horay u soco oo guji xaashida 2 oo dooro  KALIYA^(ONLY)  unugga B2. Ha dooran^(NOT)  dhammaan noocyada B2 ilaa B12 .

Waxaad arki doontaa in sanduuqu hadda leeyahay qiimaha =Sheet2!$B$2 . Waxaan u baahanahay inaan tan u bedelno =Sheet2!$B2 . Asal ahaan, kaliya ka takhalus $  ka hor 2. Tani waxay ilaalin doontaa tiirka, laakiin u ogolow lambarka safka inuu si toos ah u beddelo. Sabab kasta ha ahaatee, kaliya kuma oggolaan doonto inaad doorato tirada oo dhan.

Guji^(Click) badhanka tixraaca unugga mar labaad ka dibna dhagsii OK. Hadda qiyamka xaashida 1 ee ka weyn xaashida 2 waxa loo qaabayn doonaa iyadoo la raacayo xulashooyinka qaabaynta ee aad dooratay.

Waxaan rajeyneynaa^(Hopefully) , in wax walba ay macno samaynayaan! Markaad eegto Excel 2013 , uma muuqato wax sifooyin cusub ah marka ay timaado qaabeynta shuruudaha. Sida tilmaantii ugu dambaysay, haddii aad dareento in xeerarka caadiga ahi aanay ku habboonayn waxa aad isku dayayso inaad fuliso, waxaad riixi kartaa  ikhtiyaarka Xeerka Cusub^{(New Rule)} oo ka bilow xoq. Waxa ugu weyn ee ku saabsan abuurista xeer cusub waa in aad isticmaali karto qaacido si aad u go'aamiso unugyada qaabaynta, taas oo aad u awood badan.

Inkasta oo qaabaynta shuruudu ay u muuqato mid sahlan oo sahlan oo dusha sare ah, waxay noqon kartaa mid aad u adag iyadoo ku xiran xogtaada iyo baahidaada. Haddii aad wax su'aalo ah qabtid, xor ayaad u tahay inaad faallo ka bixiso. Ku raaxayso!

Format Cells using Conditional Formatting in Excel

If you are used to using older versions of Excel, the conditional formatting options in Excel 2007, 2010, and 2013 will amaze you. So why would you wаnt to bother using condіtional formatting? Well, here are a couple of reasons why I love using thiѕ feature of Excеl:

1. To make your data more visually appealing.

2. To make your spreadsheets easier to understand at a glance.

3. To identify certain types of numbers for help in problem solving.

4. To assist you in drawing conclusions from your data.

5. To visually display to the user what is “good” or “bad” by using green and red.

Now, you can use conditional formatting to format every cell in a range based on your own criteria (and there are a lot of formatting options to choose from). For example, if you have a profit sheet and you want to color code all profits greater than $200 as green and all profits less than $200 as yellow and all losses as red, then you can use conditional formatting to quickly do all the work for you.

Conditional Formatting in Excel

Conditional formatting enables you to format significant amounts of data quickly and easily – while still being able to distinguish different types of data. You can create rules for the formatting options that will allow Microsoft Excel to auto-format for you. You really only have to follow three simple steps.

Step 1: Select the cells you want to format.

Step 2: Click the Conditional Formatting button under the Home menu, Styles section.

Step 3: Select your rules. There are Highlight Cells Rules and Top/Bottom Rules at the top that let you do comparisons with values. For this example, we imposed three rules. The first was that any value greater than $200 was green.

It’s worth noting that only the Highlight Cells Rules section can also be used to compare a dataset to another dataset. Everything else will just use the one dataset that you have highlighted and compare the values against each other. For example, when using the Greater Than rule, I can compare values from A1 to A20 against a specific number or I can compare A1 to A20 against B1 to B20.

The same logic was applied to the second and third rules. The second rule was that anything between $0 and $200 was formatted yellow. The third rule was that anything less than $0 was formatted red. Here is what a portion of the finished spreadsheet looks like.

If you do not like these formatting options, Excel has many different new Conditional Formatting options that you can use from. For example, you can insert icons like colored arrows (Icon Sets), bar charts like in the second example (Data Bars), or even a range of automatically selected colors like in the last example (Color Scales). These three options only compare values from the same dataset. If you select A1 to A20, it’ll only compare those values against each other.

If you later decide that you don’t want your cells to be conditionally formatted, all you have to do is clear the formatting. To do this, select the Conditional Formatting button and select Clear Rules. Then, select whether you want to clear the rules from only the selected cells or from the entire worksheet.

Also, if you have created several rules, you might forget what rules you have applied to what cells. Since you can apply many rules to the same set of cells, it can become quite confusing especially if someone else created the spreadsheet. To see all the rules, click on the Conditional Formatting button and then click on Manage Rules.

When you have more than one rule applied to the same range of cells, the rules are evaluated in order from higher precedence to lower precedence. By default, the newest rule added will have the higher precedence. You can change that by clicking on the rule and then using the up and down arrow buttons to change the order. Also, you can click the dropdown at the very top and see the rules for only the current selection or for each sheet in the workbook.

There is also a checkbox called Stop If True, which I won’t go into detail here because it’s quite complicated. However, you can read this post from Microsoft that explain it in great detail.

New Conditional Formatting Options Excel 2010

Just about everything is the same in Excel 2010 when it comes to Conditional Formatting that was included in Excel 2007. However, there is one new feature that really makes it much more powerful.

Earlier I had mentioned that the Highlight Cells Rules section lets you compare one set of data to another set of data on the same spreadsheet. In 2010, you can now reference another worksheet in the same workbook. If you try to do this in Excel 2007, it will let you select the data from another worksheet, but will give you an error message when you try to click OK at the end.

In Excel 2010, you can now do this, but it’s a bit tricky so I’m going to explain it step by step. Let’s say I have two worksheets and on each sheet I have data from B2 to B12 for something like profit. If I want to see which values in B2 to B12 from sheet 1 are greater than the B2 to B12 values of sheet 2, I would first select the B2 to B12 values in sheet 1 and then click on Great Than under Highlight Cells Rules.

Now click on the cell reference button that I have shown above. The box will change and the cursor icon will become a white cross. Now go ahead and click on sheet 2 and select ONLY cell B2. Do NOT select the entire range of B2 to B12.

You’ll see that the box now has a value of =Sheet2!$B$2. We’re going to need to change this to =Sheet2!$B2. Basically, just get rid of the $ that comes before the 2. This will keep the column fixed, but allow the row number to change automatically. For whatever reason, it won’t just let you select the entire range.

Click the cell reference button again and then click OK. Now the values in sheet 1 that are greater than sheet 2 will be formatted according to the formatting options you chose.

Hopefully, that all makes sense! When looking at Excel 2013, there doesn’t seem to be any new features when it comes to conditional formatting. As a last tip, if you feel that the default rules don’t match what you are trying to accomplish, you can click the New Rule option and start from scratch. What’s great about creating a new rule is that you can use a formula to determine which cells to format, which is very powerful.

Even though conditional formatting looks relatively easy and simple on the surface, it can become quite complex depending on your data and your needs. If you have any questions, feel free to post a comment. Enjoy!

Related posts

• Isku xidh Unugyada u dhexeeya Xaashiyaha iyo Buugaagta Shaqada ee Excel

• Isticmaal daaqada daawashada Excel si aad ula socoto unugyada muhiimka ah ee buug-shaqeedka

• Sida loo qariyo xaashiyaha, unugyada, tiirarka, iyo qaaciidooyinka gudaha Excel

• U isticmaal qaab rinjiile ah oo ku jira Word si aad u koobbiso oo aad u dhejiso qaabaynta

• Kudar Khadka Dib-u-Celinta Toosan ee Qorshaha Kala firdhisan ee Excel

• Sida Taariikhaha Looga Jaro Excel

• Sida Loo Bedelo Taariikhda Kooxda Microsoft

• Isticmaal Hawlaha Kooban si aad u soo koobto Xogta ku jirta Excel

• Sida loo sameeyo Histogram gudaha Excel

• Sida Loogu Beddelayo Inta u dhaxaysa Waraaqaha Shaqada ee Excel

• Adeegso Excel Mobile's cusub ee shaqada "Ka Geli Xogta Sawirka".

• Sida loo kala saaro Magacyada Hore iyo Dambe ee Excel

• Sida loo xafido erayga sirta ah ee Excel File

• Sida Loo Muujiyo Calaamadaha Qaabaynta ee Word

• Casharrada Aasaasiga ah ee Microsoft Excel - Barashada Sida loo Isticmaalo Excel

• Sida loogu daro faallooyinka unugga Xaashida shaqada ee Excel

• Sida Loo Fahmo Maxaa-Haddii Falanqaynta Microsoft Excel

• Sida Loo Isticmaalo Shaqada YEARFRAC gudaha Excel

• Sida Loo Sameeyo Jadwalka socodka gudaha Word iyo Excel

• Sida loo shaandheeyo xogta gudaha Excel