Xu Hướng 3/2024 # Colors In An If Function # Top 3 Xem Nhiều

Bạn đang xem bài viết Colors In An If Function được cập nhật mới nhất tháng 3 năm 2024 trên website Hoisinhvienqnam.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất.

Unfortunately, there is no way to acceptably accomplish this task without using macros, in one form or another. The closest non-macro solution is to create a name that determines colors, in this manner:

Select cell A1.

Use a name such as “mycolor” (without the quote marks).

In the Refers To box, enter the following, as a single line:

=IF(GET.CELL(38,Sheet1!A1)=10,"GO",IF(GET.CELL(38,Sheet1!A1) =3,"Stop","Neither"))

With this name defined, you can, in any cell, enter the following:

=mycolor

The result is that you will see text based upon the color of the cell in which you place this formula. The drawback to this approach, of course, is that it doesn’t allow you to reference cells other than the one in which the formula is placed.

The solution, then, is to use a user-defined function, which is (by definition) a macro. The macro can check the color with which a cell is filled and then return a value. For instance, the following example returns one of the three words, based on the color in a target cell:

Function CheckColor1(range) If range.Interior.Color = RGB(256, 0, 0) Then CheckColor1 = "Stop" ElseIf range.Interior.Color = RGB(0, 256, 0) Then CheckColor1 = "Go" Else CheckColor1 = "Neither" End If End Function

This macro evaluates the RGB values of the colors in a cell, and returns a string based on those values. You could use the function in a cell in this manner:

=CheckColor1(B5)

If you prefer to check index colors instead of RGB colors, then the following variation will work:

Function CheckColor2(range) If range.Interior.ColorIndex = 3 Then CheckColor2 = "Stop" ElseIf range.Interior.ColorIndex = 4 Then CheckColor2 = "Go" Else CheckColor2 = "Neither" End If End Function

Whether you are using the RGB approach or the color index approach, you’ll want to check to make sure that the values used in the macros reflect the actual values used for the colors in the cells you are testing. In other words, Excel allows you to use different shades of green and red, so you’ll want to make sure that the RGB values and color index values used in the macros match those used by the color shades in your cells.

One way you can do this is to use a very simple macro that does nothing but return a color index value:

Function GetFillColor(Rng As Range) As Long GetFillColor = Rng.Interior.ColorIndex End Function

Now, in your worksheet, you can use the following:

=GetFillColor(B5)

The result is the color index value of cell B5 is displayed. Assuming that cell B5 is formatted using one of the colors you expect (red or green), you can plug the index value back into the earlier macros to get the desired results. You could simply skip that step, however, and rely on the value returned by GetFillColor to put together an IF formula, in this manner:

=IF(GetFillColor(B5)=4,"Go", IF(GetFillColor(B5)=3,"Stop", "Neither"))

You’ll want to keep in mind that these functions (whether you look at the RGB color values or the color index values) examine the explicit formatting of a cell. They don’t take into account any implicit formatting, such as that applied through conditional formatting.

For some other good ideas, formulas, and functions on working with colors, refer to this page at Chip Pearson’s website:

3 Ways To Sort By Color In Excel

There are several ways to sort data in Microsoft Excel. Learn how to use conditional sorting in Excel to sort by font color, cell background color, or icon color.

Instructions in this article apply to Excel for Microsoft Office 365, Excel 2024, Excel 2024, and Excel 2013 for Windows and Mac.

Select a Range to Be Sorted in Excel

Before data can be sorted, Excel needs to know the exact range to sort. Excel can automatically include related data in a range so long as there are no blank rows or columns within the selected area. Blank rows and columns between areas of related data are okay. Excel then determines if the data area has field names and excludes those rows from the records to be sorted.

Allowing Excel to select the range to be sorted is fine for small amounts of data. However, for large areas of data, the easiest way to ensure that the correct range is selected is to highlight it before sorting.

If the same range is to be sorted repeatedly, the best approach is to give the range a name. If a name is defined for the range to be sorted, type the name in the Name Box, or select it from the associated drop-down list. This way, Excel automatically highlights the correct range of data in the worksheet.

Any sorting requires the use of sort order. When sorting by values, there are two possible sort orders: ascending and descending. However, when sorting by colors, no such order exists, so you must manually define the color sort order.

How to Sort by Cell Background Color in Excel

In the example below, the records of students age 20 and younger are highlighted in red. To sort the data by cell background color so that the red entries appear on top:

Highlight the range of cells to be sorted (cells A2 to D11 in the example).

Select the Sort on drop-down arrow and choose ​Cell Color.

Clear the My data has headers check box so that the first row doesn’t get cut off.

Select the Order drop-down arrow and choose Red.

When Excel finds different cell background colors in the selected data, it adds those colors to the Order drop-down list in the dialog box.

Choose On Top from the drop-down list next to the sort order box so that the red cells will be at the top of the list, then select OK.

The four records with red backgrounds are grouped together at the top of the data range.

When working with calculations, you can make negative numbers in Excel appear red by default to help those numbers stand out more.

How to Sort by Font Color in Excel

In the example below, the records of students enrolled in nursing programs appear in red, and those enrolled in science programs are blue. To sort the data by font color:

Highlight the range of cells to be sorted (cells A2 to D11 in the example).

Select the Sort on drop-down arrow and choose ​Font Color.

Clear the My data has headers check box so that the first row doesn’t get cut off.

Select the Order drop-down arrow, then choose Red.

When Excel finds different font colors in the selected data, it adds those colors to the Order drop-down list in the dialog box.

Choose On Top from the drop-down list next to the sort order box so that the red entries will be at the top of the list.

Select Add to add a second sort level.

Use the same settings as the first sort level, but this time select the Order drop-down arrow and choose Blue.

Select OK to sort the data and close the dialog box.

The two records with the red font color are grouped together at the top of the data range, followed by the two blue records.

How to Sort by Icon in Excel

Icon sets offer an alternative to regular conditional formatting options that focus on the font and cell formatting changes. The example below contains dates and temperatures that have been conditionally formatted with the stoplight icon set based on the daily maximum temperatures.

Follow these steps to sort the data so that records displaying the green icons are grouped first, followed by the yellow icons, and then the red icons:

Highlight the range of cells to be sorted (cells A2 to B31 in the example).

Select the Column drop-down arrow, then choose the column containing the conditional icons (Temperature in the example).

Due to the way conditional formatting with icons works, you can leave the My data has headers check box selected.

Select the Sort on drop-down arrow, then choose Conditional Formatting Icon.

Select the Order drop-down arrow, then choose Green.

Choose On Top from the drop-down list next to the sort order box so that the green icon entries will be at the top of the list.

Select Add to add a second sort level.

Use the same settings as the first sort level, but this time select the Order drop-down arrow and choose Yellow.

Select Add to add a third sort level, then use the same settings as the first two levels, but this time select the Order drop-down arrow and choose Red.

Select OK to sort the data and close the dialog box.

The records with the green icon are grouped together at the top of the data range, followed by the records with the yellow icon, and then those with a red icon.

How To Sort By Color In Excel * Productivity Portfolio

Why Use Color

Perhaps, at one-time, color sorting and filtering wasn’t available, but you can do it now. The tutorial below can be used for font colors or cell color. For these instructions, I’ll be using Microsoft Office 365. However, the steps can be used in older Excel versions.

It’s probably my laziness, but I find it useful to change a cell’s background color. I might use this when I’m prioritizing or grouping a list. I need to highlight a cell and choose a background color.

In my screen snap above, I’ve applied the cell background color to Column C. However, some people might prefer using color across a row. In the second screenshot below, I used conditional formatting. Regardless of whether you’re using columns or rows, the process is the same. It also works with Excel tables.

How to Sort by Color in Excel

Tick the My data has headers checkbox in the top-right if your worksheet uses them.

Select your first fill color and keep the Order value as On Top.

Add in your other color levels. If you prefer, you can also use the On Bottom option.

You should now see your sorted list by color. You’re not limited to just 3 colors, but you can keep adding if they’re on your sheet. You also don’t need to enter the last color as Excel will put it on the bottom. In my case, that was ” No Cell Color.”

Select the column you wish to filter that has color.

Select your cell color from the side menu.

The same process works if you wish to sort by font color.

How to Filter by Color in Excel

Excel allows you to sort and filter by color

The color can be either the cell background color or font color

You can place colors on top or bottom

The system knows which colors you’ve used

No cell color is for normal cells

You can add multiple color sorts

Sort rules are processed in order

Some people prefer to filter instead of sort. This is great if you want to focus in on certain items. Even better, the procedure is shorter.

In my example, I was using a background color. However, when I added colored text, Excel adjusted the options. On the side menu for Filter by Color, you would also see Filter by Font Color.

Quick Takeaways

Disclaimer: Images from Amazon Product Advertising API. I may receive an affiliate commission on these products if you buy. Updated: 2024-04-17

How To Use The Excel Roundup Function

The ROUNDUP function works like the ROUND function, except the ROUNDUP function will always round numbers up. The number of places to round to is controlled by the num_digits argument. Positive numbers round to the right of the decimal point, negative numbers round to the left, and zero rounds to the nearest 1. The table below summarizes this behavior:

Digits Behavior

Round up to nearest .1, .01, .001, etc.

Round up to nearest 10, 100, 1000, etc.

=0 Round up to nearest 1

Example #1 – round to right

To round up values to the right of the decimal point, use a positive number for digits:  

=

ROUNDUP

(

A1

,

1

)

// Round up to 1 decimal place

=

ROUNDUP

(

A1

,

2

)

// Round up to 2 decimal places

=

ROUNDUP

(

A1

,

3

)

// Round up to 3 decimal places

=

ROUNDUP

(

A1

,

4

)

// Round up to 4 decimal places

Example #2 – round to left

To round up values to the left of the decimal point, use zero or a negative number for digits:  

=

ROUNDUP

(

A1

,

0

)

// Round up to nearest whole number

=

ROUNDUP

(

A1

,

-

1

)

// Round up to nearest 10

=

ROUNDUP

(

A1

,

-

2

)

// Round up to nearest 100

=

ROUNDUP

(

A1

,

-

3

)

// Round up to nearest 1000

=

ROUNDUP

(

A1

,

-

4

)

// Round up to nearest 10000

Example #3 – nesting

Other operations and functions can be nested inside the ROUNDUP function. For example, to round the result of A1 divided by B1, you can use a formula like this:

=

ROUNDUP

(

A1

/

B1

,

0

)

// round up result to nearest whole number

Rounding functions in Excel

To round normally, use the ROUND function.

To round to the nearest multiple, use the MROUND function.

To round down to the nearest specified place, use the ROUNDDOWN function.

To round down to the nearest specified multiple, use the FLOOR function.

To round up to the nearest specified place, use the ROUNDUP function.

To round up to the nearest specified multiple, use the CEILING function.

To round down and return an integer only, use the INT function.

To truncate decimal places, use the TRUNC function.

How To Use The Excel Vlookup Function

VLOOKUP is an Excel function to get data from a table organized vertically. Lookup values must appear in the first column of the table passed into VLOOKUP.  VLOOKUP supports approximate and exact matching, and wildcards (* ?) for partial matches. 

V is for vertical

The purpose of VLOOKUP is to get information from a table organized like this:

Using the Order number in column B as a lookup value, VLOOKUP can get the Customer ID, Amount, Name, and State for any order. For example, to get the customer name for order 1004, the formula is:

=

VLOOKUP

(

1004

,

B5:F9

,

4

,

FALSE

)

// returns "Sue Martin"

For horizontal data, you can use the HLOOKUP, INDEX and MATCH, or XLOOKUP.

VLOOKUP is based on column numbers

When you use VLOOKUP, imagine that every column in the table is numbered, starting from the left. To get a value from a particular column, provide the appropriate number as the “column index”. For example, the column index to retrieve the first name below is 2:

The last name and email can be retrieved with columns 3 and 4:

=

VLOOKUP

(

H3

,

B4:E13

,

2

,

FALSE

)

// first name

=

VLOOKUP

(

H3

,

B4:E13

,

3

,

FALSE

)

// last name

=

VLOOKUP

(

H3

,

B4:E13

,

4

,

FALSE

)

// email address

VLOOKUP only looks right

VLOOKUP can only look to the right. The data you want to retrieve (result values) can appear in any column to the right of the lookup values:

If you need to lookup values to the left, see INDEX and MATCH, or XLOOKUP.

Exact and approximate matching

VLOOKUP has two modes of matching, exact and approximate. The name of the argument that controls matching is “range_lookup“. This is a confusing name, because it seems to have something to do with cell ranges like A1:A10. Actually, the word “range” in this case refers to “range of values” – when range_lookup is TRUE, VLOOKUP will match a range of values rather than an exact value. A good example of this is using VLOOKUP to calculate grades.

It is important to understand that range_lookup defaults to TRUE, which means VLOOKUP will use approximate matching by default, which can be dangerous. Set range_lookup to FALSE to force exact matching:

=

VLOOKUP

(

value

,

table

,

col_index

)

// approximate match (default)

=

VLOOKUP

(

value

,

table

,

col_index

,

TRUE

)

// approximate match

=

VLOOKUP

(

value

,

table

,

col_index

,

FALSE

)

// exact match

Note: You can also supply zero (0) instead of FALSE for an exact match.

Exact match

In most cases, you’ll probably want to use VLOOKUP in exact match mode. This makes sense when you have a unique key to use as a lookup value, for example, the movie title in this data:

The formula in H6 to find Year, based on an exact match of movie title, is:

=

VLOOKUP

(

H4

,

B5:E9

,

2

,

FALSE

)

// FALSE = exact match

Approximate match

In cases when you want the best match, not necessarily an exact match, you’ll want to use approximate mode. For example, below we want to look up a commission rate in the table G5:H10. The lookup values come from column C. In this example, we need to use VLOOKUP in approximate match mode, because in most cases an exact match will never be found. The VLOOKUP formula in D5 is configured to perform an approximate match by setting the last argument to TRUE:

=

VLOOKUP

(

C5

,

$G$5:$H$10

,

2

,

TRUE

)

// TRUE = approximate match

VLOOKUP will scan values in column G for the lookup value. If an exact match is found, VLOOKUP will use it. If not, VLOOKUP will “step back” and match the previous row.

Note: data must be sorted in ascending order by lookup value when you use approximate match mode with VLOOKUP.

First match

=

VLOOKUP

(

E5

,

B5:C11

,

2

,

FALSE

)

// returns 17

Wildcard match

The VLOOKUP function supports wildcards, which makes it possible to perform a partial match on a lookup value. For instance, you can use VLOOKUP to retrieve values from a table after typing in only part of a lookup value. To use wildcards with VLOOKUP, you must specify the exact match mode by providing FALSE or 0 for the last argument, range_lookup. The formula in H7 retrieves the first name, “Michael”, after typing “Aya” into cell H4:

=

VLOOKUP

(

$H$4

&

"*"

,

$B$5:$E$104

,

2

,

FALSE

)

Read a more detailed explanation here.

Two-way lookup

Inside the VLOOKUP function, the column index argument is normally hard-coded as a static number.  However, you can also create a dynamic column index by using the MATCH function to locate the right column. This technique allows you to create a dynamic two-way lookup, matching on both rows and columns. In the screen below, VLOOKUP is configured to perform a lookup based on Name and Month. The formula in H6 is:

=

VLOOKUP

(

H4

,

B5:E13

,

MATCH

(

H5

,

B4:E4

,

0

),

0

)

For more details, see this example.

Note: In general, INDEX and MATCH is a more flexible way to perform two-way lookups.

Multiple criteria

The VLOOKUP function does not handle multiple criteria natively. However, you can use a helper column to join multiple fields together, and use these fields like multiple criteria inside VLOOKUP.  In the example below, Column B is a helper column that concatenates first and last names together with this formula:

=

C5

&

D5

// helper column

VLOOKUP is configured to do the same thing to create a lookup value. The formula in H6 is:

=

VLOOKUP

(

H4

&

H5

,

B5:E13

,

4

,

0

)

For details, see this example.

Note: INDEX and MATCH and XLOOKUP are more robust ways to handle lookups based on multiple criteria.

VLOOKUP and #N/A errors

If you use VLOOKUP you will inevitably run into the #N/A error. The #N/A error just means “not found”. For example, in the screen below, the lookup value ”Toy Story 2″ does not exist in the lookup table, and all three VLOOKUP formulas return #N/A:

One way to “trap” the NA error is to use the IFNA function like this:

The formula in H6 is:

=

IFNA

(

VLOOKUP

(

H4

,

B5:E9

,

2

,

FALSE

),

"Not found"

)

The message can be customized as desired. To return nothing (i.e. to display a blank result) when VLOOKUP returns #N/A you can use an empty string like this:

=

IFNA

(

VLOOKUP

(

H4

,

B5:E9

,

2

,

FALSE

),

""

)

// no message

The #N/A error is useful because it tells you something is wrong.  In practice, there are many reasons why you might see this error, including:

The lookup value does not exist in the table

The lookup value is misspelled, or contains extra space

Match mode is exact, but should be approximate

The table range is not entered correctly

You are copying VLOOKUP, and the table reference is not locked

Read more: VLOOKUP without #N/A errors 

More about VLOOKUP Other notes

Range_lookup controls whether value needs to match exactly or not. The default is TRUE = allow non-exact match.

Set range_lookup to FALSE to require an exact match and TRUE to allow a non-exact match.

If range_lookup is TRUE (the default setting), a non-exact match will cause the VLOOKUP function to match the nearest value in the table that is still less than value.

When range_lookup is omitted, the VLOOKUP function will allow a non-exact match, but it will use an exact match if one exists.

If range_lookup is TRUE (the default setting) make sure that lookup values in the first row of the table are sorted in ascending order. Otherwise, VLOOKUP may return an incorrect or unexpected value.

If range_lookup is FALSE (require exact match), values in the first column of table do not need to be sorted.

How To Use The Excel Countif Function

COUNTIF is in a group of eight functions in Excel that split logical criteria into two parts (range + criteria). As a result, the syntax used to construct criteria is different, and COUNTIF requires a cell range, you can’t use an array.

COUNTIF only supports a single condition. If you need to apply multiple criteria, use the COUNTIFS function. If you need to manipulate values in the range argument as part of a logical test, see the SUMPRODUCT and/or FILTER functions.

Basic example

In the worksheet shown above, the following formulas are used in cells G5, G6, and G7:

=

COUNTIF

(

B5:B12

,

"jim"

)

// count name = "jim"

=

COUNTIF

(

C5:C12

,

"ca"

)

// count state = "ca"

Notice COUNTIF is not case-sensitive, “CA” and “ca” are treated the same.

Double quotes (“”) in criteria

In general, text values need to be enclosed in double quotes (“”), and numbers do not. However, when a logical operator is included with a number, the number and operator must be enclosed in quotes, as seen in the second example below:

=

COUNTIF

(

A1:A10

,

100

)

// count cells equal to 100

=

COUNTIF

(

A1:A10

,

"jim"

)

// count cells equal to "jim"

Value from another cell

A value from another cell can be included in criteria using concatenation. In the example below, COUNTIF will return the count of values in A1:A10 that are less than the value in cell B1. Notice the less than operator (which is text) is enclosed in quotes.

=

COUNTIF

(

A1:A10

,

"<"

&

B1

)

// count cells less than B1

Not equal to

To construct “not equal to” criteria, use the “” operator surrounded by double quotes (“”). For example, the formula below will count cells not equal to “red” in the range A1:A10:

Blank cells

COUNTIF can count cells that are blank or not blank. The formulas below count blank and not blank cells in the range A1:A10:

=

COUNTIF

(

A1:A10

,

""

)

// blank

Dates

The easiest way to use COUNTIF with dates is to refer to a valid date in another cell with a cell reference. For example, to count cells in A1:A10 that contain a date greater than the date in B1, you can use a formula like this:

The safest way hardcode a date into COUNTIF is to use the DATE function. This ensures Excel will understand the date. To count cells in A1:A10 that contain a date less than April 1, 2024, you can use a formula like this

=

COUNTIF

(

A1:A10

,

"<"

&

DATE

(

2024

,

4

,

1

))

// dates less than 1-Apr-2024

Wildcards

The wildcard characters question mark (?), asterisk(*), or tilde (~) can be used in criteria. A question mark (?) matches any one character and an asterisk (*) matches zero or more characters of any kind. For example, to count cells in a A1:A5 that contain the text "apple" anywhere, you can use a formula like this:

=

COUNTIF

(

A1:A5

,

"*apple*"

)

// cells that contain "apple"

To count cells in A1:A5 that contain any 3 text characters, you can use:

=

COUNTIF

(

A1:A5

,

"???"

)

// cells that contain any 3 characters

The tilde (~) is an escape character to match literal wildcards. For example, to count a literal question mark (?), asterisk(*), or tilde (~), add a tilde in front of the wildcard (i.e. ~?, ~*, ~~).

Notes

COUNTIF is not case-sensitive. Use the EXACT function for case-sensitive counts.

COUNTIF only supports one condition. Use the COUNTIFS function for multiple criteria.

Cell references in criteria are not enclosed in quotes, i.e. "

The wildcard characters ? and * can be used in criteria. A question mark matches any one character and an asterisk matches any sequence of characters (zero or more).

To match a literal question mark or asterisk, use a tilde (~) in front question mark or asterisk (i.e. ~?, ~*).

COUNTIF requires a range, you can't substitute an array.

COUNTIF returns incorrect results when used to match strings longer than 255 characters.

COUNTIF will return a #VALUE error when referencing another workbook that is closed.

Cập nhật thông tin chi tiết về Colors In An If Function trên website Hoisinhvienqnam.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!