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 nonmacro 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:
=mycolorThe 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 userdefined 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 FunctionThis 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 FunctionWhether 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 FunctionNow, 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 dropdown 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 dropdown 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 dropdown arrow and choose Red.
When Excel finds different cell background colors in the selected data, it adds those colors to the Order dropdown list in the dialog box.
Choose On Top from the dropdown 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 dropdown 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 dropdown arrow, then choose Red.
When Excel finds different font colors in the selected data, it adds those colors to the Order dropdown list in the dialog box.
Choose On Top from the dropdown 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 dropdown 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 dropdown 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 dropdown arrow, then choose Conditional Formatting Icon.
Select the Order dropdown arrow, then choose Green.
Choose On Top from the dropdown 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 dropdown 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 dropdown 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 onetime, 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 topright 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 TakeawaysDisclaimer: Images from Amazon Product Advertising API. I may receive an affiliate commission on these products if you buy. Updated: 20240417
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 rightTo 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 leftTo 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 – nestingOther 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 verticalThe 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 numbersWhen 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 rightVLOOKUP 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 matchingVLOOKUP 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 matchIn 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 matchIn 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 matchThe 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.
Twoway lookupInside the VLOOKUP function, the column index argument is normally hardcoded 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 twoway 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 twoway lookups.
Multiple criteriaThe 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 errorsIf 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 nonexact match.
Set range_lookup to FALSE to require an exact match and TRUE to allow a nonexact match.
If range_lookup is TRUE (the default setting), a nonexact 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 nonexact 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 exampleIn 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 casesensitive, “CA” and “ca” are treated the same.
Double quotes (“”) in criteriaIn 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 cellA 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 toTo 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 cellsCOUNTIF 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
DatesThe 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 1Apr2024
WildcardsThe 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 casesensitive. Use the EXACT function for casesensitive 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!