Conditional Formatting
Yüklə 86,81 Kb.
|
- Bu səhifədəki naviqasiya:
- Conditional Formatting Dialog Box
- Spinner Control Dialog Box
- Validation Message Box
|
Copyright@1997 by David Hager Chapter Outline – Conditional Formatting Overview How It Works Using Cell Values to Format Data Combining Old and New Conditional Formatting More About Operators Data Protection Viewing Differences in Data Refining the Process with a Spinner Control Using a Formula to Format Data Viewing Data Above and Below the Average Conditional Formatting as a Guide to Data Entry Spotting Duplicate Data Entry Formatting for Data Quality Using Excel’s Macro Functions for Formatting Creating an Autoformat that Appears Like Magic Modifying Hyperlink Formats Viewing Sequential Data by Direction Formatting Dates and Merged Cells in a Schedule Locating the Last Action with Conditional Formatting Using Conditional Formatting to Validate a Sorted Column Finding Cells that have Conditional Formats Data Filtering and Conditional Formatting Viewing Maximum and Minimum Values in a Filtered List Applying Custom Functions to Filtered Lists Format-Filtering a Data List Using a Complex Formula for Numeric Fields Multi-Sheet Data Comparison by Conditional Formatting Summary
OVERVIEW
Note: The range selection to be conditionally formatting can be non-contiguous (more than one range selected). In order to make multiple selections, press Shift and F8 after making the first selection. Then, highlight the other range(s) to be added. The selection can be extended to other worksheets within a workbook by grouping the sheets before the selection is made. This can be done by holding down the Ctrl key while clicking the sheet tabs for the worksheets to be grouped.
Conditional Formatting Dialog Box
Note: By design, the application of conditional formats works on a hierarchical structure. A total of three conditional formats are allowed. If a cell has conditional formats 1 & 2, and both of the conditions are TRUE, only the format associated with condition 1 will be applied to the cell. The same is true for conditional formats 2 & 3. Figure CCFIn each case, the object is to format all cell values in the range to have a red font if <=100, a green font if <=1000 and a blue font if <=10000. All of the formatting options are selected by clicking on the Format button in the conditional formatting dialog box. The first two examples uses the Cell Value Is option and the last two use the Formula Is option. All situations will not provide multiple options such as this simple example provides, so it is important to examine each case and apply the best option for the job at hand. Option 1 - Cell Value Is: less than or equal to (a value entered in the CF dialog box) This is the least flexible of the available options. However, in cases where is it not desirable to have a criteria value visible to the user, this option could prove to be useful. Option 2 - Cell Value Is: less than or equal to (a value in a worksheet cell) The value in a cell can be entered manually, or it can change if it is linked to a worksheet control (see an example of this later in this chapter) or a formula in the cell can change based on changed in the cells ranges used in the formula. Option 3 - Formula Is: =E1<=value (formula that returns True or False) This is a powerful option that is only limited by the user’s ability to construct the right formula for the task at hand. Option4 – Formula Is: =LTOET(G1, value) (custom function that returns True or False) If a formula still cannot do the job, using VBA to construct custom functions further extends the utility of conditional formatting. The VBA code for the LTOET function is shown below. Function LTOET(ByVal cfcell As Object, cnum As Double) As Boolean If cfcell <= cnum Then LTOET = True Else LTOET = False End If End Function
Caution: When you use a conditional format on a cell that contains rich-text formatting (formats applied to specified characters rather than the entire cell), the effect will depend on how the rich-text formatting was applied. The conditional formatting for font changes will only occur up to the character before the first custom formatted character. If the first character of the cell has rich-text formatting, conditional formatting will have no effect on the font properties of the cell. However, the ability to direct border and pattern changes with conditional formatting is still viable in this case. The reason that other formatting options found in Excel are not available for conditional formatting is that those formats would impact the row height or column width, and since this would effect cells that are not part of the conditional format, those formats are not included. Note: When a conditional format has been applied to a worksheet range, the insertion of rows or columns into that range affords cells that also have the same conditional formats corresponding to the formatted range. USING CELL VALUES TO FORMAT DATA COMBINING OLD AND NEW CONDITIONAL FORMATTING Under certain conditions the old conditional formatting technique used in Excel can be combined with the new conditional formatting method to produce a useful number format scheme. If we take the custom number format shown previously, [Blue] General; [Red]”-“General;[Green]General, and combine it with the new conditional formatting process, a format can be produced that uses 6 different criteria with their 6 different corresponding font colors. Fig CC1In this example, the following three conditions were set:
By using this formatting scheme, the range of numbers shows up in six different colors depending on their relative value. An unwanted side effect of this formatting scheme is that all text entries are formatted as though they are greater than 10000. This is due to how the “>” operator functions in Excel. Another example of combining old and new conditional formatting provides a method to format the days of the week with different colors. In column C is placed a series of consecutive dates. In cell D1 is typed the formula =WEEKDAY(C1) and that formula is filled down the length of the date series. The WEEKDAY function returns a value from 1 to 7 for the days Sunday through Saturday respectively (when the default form of the second argument is used). Now, it turns out that January 1, 1900, which is Day One of the default Excel serial date system, fell on a Sunday. Thus, the values returned by the WEEKDAY function for serial dates can be formatted as days of the week correctly. When the following formatting is applied to column D, each workday is a different color, while Saturday and Sunday are formatted in yet another color. Cell Value Is: Equal to 2 with Format Font color of orange Cell Value Is: Equal to 3 with Format Font color of aqua Cell Value Is: Equal to 4 with Format Font color of tan Custom number format: [Blue][=5]dddd;[Green][=6]dddd;[Red]dddd As these examples show, with judicious use of traditional and new conditional formatting it is possible to format data with up to 6 different criteria to produce quite useful formatting effects. MORE ABOUT OPERATORS The options available when using Cell Value Is are between, not between, greater than, greater than or equal to, less than, less than or equal to, equal to and not equal to. Examples illustrating two of these options use the criteria shown below. Cell Value Is: Greater than or equal to =$B$1 with Format Font red Cell Value Is: Between =$D$1 and =$D$2 with Format Font red Fig CC2In the first example (shown in Fig. CC2), the conditional formatting for column B turns each number red that is larger than the number in cell B1 (which is 21). However, the result for column D is somewhat unexpected. In this case, the format turns all numbers red between the value in D1 and the value in D2. As you can see, though, the numbers 21 and 26 in this column are also formatted red. Thus, it is apparently by design that Excel treats values inclusively when the “between” criteria is used. DATA PROTECTION There are different levels of security that can be used in Excel to hide sensitive data. However, if an expert Excel user has access to you system, that person can likely find out what they want to know despite your best efforts to protect the data. Most of the time, though, you just want to make sure that prying eyes in the office don’t browse your data while you are away from the desk for a few minutes. Conditional formatting provides an easy way to do this. A cell is selected as a trigger for the formatting. In this example, A1 is used. The following format is then applied to all of the cells of the worksheet. Figure CF4Note: To select all of the cells in a worksheet, click the button just to the left of the header for column A. Formula Is: =$A$1=1 with Format Patterns cell shading of black. The formula relies on a “1” being entered in cell A1. If it is, then all of the cells on the worksheet turn black. Of course, there are a lot of alternatives that will work just as well. You might even want to enter a password in a cell to produce the same effect. Caution: There are some bugs that occur from time to time when using conditional formatting in the initial release of Excel97. In the testing of the example on hiding data, some of the cells in the screen sometimes do not black out when the format is initially applied. As a workaround to this problem, the offending cells can be removed from the screen by scrolling away from that portion of the worksheet. Upon returning to these cells they are formatted as expected. VIEWING DIFFERENCES IN DATA In the majority of cases, users collect data over specified time intervals and then it is analyzed to see the characteristics of that data over time. A typical data set is shown in Figure CC3. In previous versions of Excel, formulas were written to show differences in data. A typical formula to flag differences is: =OR((H5*0.8)>D5:G5) This formula returns TRUE if any of the values in the range D5:G5 are less than 80% of the value in cell H5, which in this case is the average of D5:G5. Still, this formula does not indicate which of the values in the range D5:G5 fits the specified condition. However, this is easily accomplished with conditional formatting. Figure CC3In the example, the following conditions were set for the range D5:G14.
2) Cell Value Is: less than or equal to “=$H5*0.8” with Format Font red. Take note of the cell reference, which changes by row but not by column. Thus, the format is always based on the value in column H, which contains formulas, for each row of data. Now, an entry in the table will appear blue if it is greater than 20% above the average, or appear red if it is 20% below the average for that row of data.
This model was set up with two spinners. Each spinner was set to return values between 0 and 100. The top spinner and the bottom spinners are linked to cells C1 and C2 respectively. The formulas in cells B1 & B2 (=C1*.01 and =C2*.01 respectively) use those values to generate percentages between 0% and 100%. The formulas used in the base example are modified to use input from B1 & B2.
Figure CC4With this enhanced model, the values in the data table can have formatting applied by setting the desired percentage deviations above and below the average to date for each row with the spinner controls. When each of the spinner value settings is at zero, all of the values in the table will have a red or blue font. As the percentage deviations are adjusted to a value away from zero, the data points that no longer meet the changing criteria will lose their formatted color. Thus, each value can be scrutinized visually for each 1% deviation from the average by using the spinner controls. USING A FORMULA TO FORMAT DATA VIEWING DATA ABOVE AND BELOW THE AVERAGE A simple example of how a conditional format can be set by a formula is shown in Fig. CF1. In this example, the values in the range A2:A11 will show up with a red font if the value is less than the average of that range. In order to do this, the following information was entered in the conditional formatting dialog box: Formula Is: =AVERAGE($A$2:$A$11)>A2 with Format Font of red. It is important to note how relative and absolute references are used in the formula, as well as where active cell is positioned within the range to be formatted. The range of cells that the AVERAGE function operates on is absolute, as signified by the use of the “$” sign for both the column and row parts of the cell reference. The formula is written to apply to the active cell and Excel changes the relative references in the formula to apply to the other cells in the selection to be formatted. CONDITIONAL FORMATTING AS A GUIDE TO DATA ENTRY One of the many uses of conditional formatting is as a guide to data entry. It is always important to know that the data being entered is going in the right place and all of the necessary data has been entered. Two such techniques are demonstrated in Figure CF2. Figure CF2For example, a certain range in column A has been designated as a data entry range and was formatted such that all of the cells that do not currently contain data have red cell shading. Once the data is entered in one of these cells, the red shading disappears. This conditional formatting was applied by highlighting the range A2:A20 and using the formula =A2="" in the Formula Is option. Note: When you copy cells on a worksheet that have a conditional format applied to them, the formats are copied as well. This is true for a copy/paste operation as well as filling cells with the fill handle. Conditional formats are also carried along when the copy/paste operation is done to other workbooks. Another example involves entering data in a series of cells in order to complete a data set. The object is to format the column containing the sum formulas so that the result will not appear until all of the required data has been entered. This is done by using the following conditional formatting scheme to column G. Formula Is: =OR(C2:F2="") with Format Font of white. In any of the values on a row in the table are omitted, the answer for the sum of those values will not be visible. It is important to remember that the formula itself will still return a sum even if all of the data entry has not been completed and any other formulas that may use the values from these ranges in their calculations must be validated in some way as well. Conditional formatting creates a visual effect that is very useful, but it cannot change the value of a cell. SPOTTING DUPLICATE DATA ENTRY Most users of Excel have a worksheet list where they need to keep track of items. In many cases, it is important that no duplicate items are added to the list. In Fig. CF3, column A is formatted so that the first time an item is entered, its font color is blue. All subsequent entries of that item in the list will appear with a black font. This is accomplished by conditionally formatting column A (starting with cell A2) with the formula shown below. Formula Is: =COUNTIF($A$2:A2,A2)=1 with Format Font of blue. Figure CF3The formula as shown is the formula applied to cell A2. As in the previous example, it is important that the formula that is entered apply to the active cell, which is this case was A2. Since the range the COUNTIF function operates on contains only one “A”, the formula returns a value of TRUE and the cell font is blue. Since the 2nd half of the range reference is relative, the formula applied to cell A3 by Excel is: =COUNTIF($A$2:A3,A3)=1 Notice that the range that the COUNTIF function operates on is now one cell larger than for the previous cell. The expansion of the range is the key to flagging duplicate values. The range A2:A3 contains 2 A’s, and the formula returns FALSE, so the font remains black for the “A” in A3, which marks it as a duplicate value. FORMATTING FOR DATA QUALITY The subject of statistical quality control (SQC) is important to everyone who uses data to track a process. It is possible to analyze data to determine whether the process is in statistical control by charting the data in a QC program or an Excel add-in designed for that task, but conditional formatting allows the data to be viewed directly. In the following example, three conditional formats are used to show the relationship of individual data points to the mean. Formula Is: =ABS(AVERAGE($A$2:$A$20)-A2)>=AVEDEV($A$2:$A$20)*3 with Format Font red Formula Is: =ABS(AVERAGE($A$2:$A$20)-A2)>=AVEDEV($A$2:$A$20)*2 with Format Font blue Formula Is: =ABS(AVERAGE($A$2:$A$20)-A2)>=AVEDEV($A$2:$A$20) with Format Font magenta These formulas return TRUE if a data point in the table is greater or less than the average of the data by an amount determined by the part of the formula to the right of the >= operator. This example illustrates that the order of formulas used in conditional formatting is in many cases critical to the success of the model. It is important that the formula that formats values that are three absolute deviations from the mean be used with the first conditional format, so that values that might fit the criteria for 2 or 1 absolute deviation(s) are formatted correctly. VALIDATING WITH CONDITIONAL FORMATTING In many cases it is important that data entry be restricted in some way. This is a common feature of most database products. Now, in Excel97, by using conditional formatting along with data validation (which is also a new feature in Excel97) it is possible to restrict data entry in some quite useful ways. For example, when entering advertiser information in a record table, there may be a need to make sure that only the name of the advertisers found in the approved list of advertisers is a valid entry. There are two other basic features that are being used for this model. One is that the master list should not be stored on the same worksheet as the worksheet used for data entry. The other feature is that the master list must be dynamic, so those new advertisers can be added without any modification to the formulas used in the conditional format. All of this is accomplished by the formulas shown below, used in a conditional formatting framework. Define lrange =OFFSET('CF8'!$E$2,,,COUNTA('CF8'!$E:$E)-1,1) Note: Formulas that include sheet references that are not on the sheet where they are being used cannot be used directly as a conditional format formula. However, they can be used when they are used as a defined name formula, as demonstrated in this example. The formula that is defined as lrange returns a worksheet range located on sheet CF8 that starts at cell E2 and includes all of the cells below E2 that contain entries. Then, if a new advertiser is added to the next empty cell in column E of that sheet, lrange automatically includes this new entry. The conditional formatting formula incorporates lrange and it is applied to all of the cells in column A. Formula Is: =AND(A1<>"",ISNA(MATCH(A1,lrange,0))) with Format solid border outline. The data validation feature also uses a formula to act as the trigger for displaying a message box to inform the user that the entry was not found in the list, as well as providing a dropdown box of the advertiser list to aid in the insertion of list items. The cells in column A were set with these data validation conditions. Settings: Allow: list , Source: =lrange Input Message: None Error Alert: Style: Information
The error box message is shown in Fig. CF7. Each time a cell is highlighted in column A, the dropdown box appears with the list of items from the dynamic list range. If an item is selected from the dropdown, the conditional format is not applied, since the item is from the list. However, if a new item is typed into the cell, the error message appears to inform the user that the item needs to be added to the master list. The conditional format (which is this case is a dark outline around the cell) then reminds the user that the item is not yet in the list. When the item is added to the list, the outline format disappears. USING EXCEL’S MACRO FUNCTIONS FOR FORMATTING Besides being able to use worksheet functions and VBA custom functions in conditional formats, it turns out that there is a wealth of xlm (the old Excel macro language) information functions buried in the worksheet interface that are ideal for a numbers of neat formatting tricks. However, these functions cannot be entered directly into a worksheet cell (or the conditional formatting dialog box) as part of a formula. Instead, they must be given defined names through Insert, Name, Define in the main menu in order to be used. The name that you want to use for the formula goes in the Names in workbook box, and the formula is typed in the Refers to box. This same technique can be used on “normal” formulas, examples of which are shown throughout this chapter. Caution: The use of xlm information functions in defined name formulas is undocumented. Thus, there is no guarantee that future versions of Excel will support this feature. It is also important to note that these functions are non-volatile, which means that they may not recalculate as expected. In cases where the xlm function returns a value, it can be made volatile by adding “+NOW()-NOW()” to the end of the defined name formula. Also, note that cells that have conditional formats that use xlm functions cannot be copied / pasted to another worksheet. As an example of just how useful these forgotten functions can be, the following technique allows for the conditional formatting of all cells containing formulas on a worksheet, as well as the ability to differentiate between normal formulas and array formulas. Define aformula as: =GET.CELL(49,'CF9'!A1) Define fformula as: =GET.CELL(48,'CF9'!A1) 1) Formula Is: =aformula with Format Font Red & Bold 2) Formula Is: =fformula with Format Font Black & Bold
=INDIRECT(“rc”,FALSE) Globref returns the reference of the cell where the formula is used. It is a workbook-level reference, independent of any specific worksheet. This technique is necessary since Excel adds the name of the sheet that is active (when the formula is defined) to all cell references in the formula. In using the INDIRECT function, Excel does not recognize the use of “rc” as a reference, so that it can be applied globally throughout the workbook. In essence, if an entire worksheet is formatted based on the contents of each individual cell, the reference A1 would be applied if the formula were intended to be worksheet specific, whereas the reference globref could be used on all worksheets. The following global formulas to be used for the conditional autoformat are as shown below. Define GlobalIsFormula as: =GET.CELL(48,globref) Define GlobalIsText as: =ISTEXT(globref) Define GlobalIsNumber as: =ISNUMBER(globref) Formula Is: = GlobalIsFormula with Format Border solid line and Patterns color background of yellow Formula Is: = GlobalIsText with Format Border solid line and Patterns color background of green Formula Is: = GlobalIsNumber with Format Border solid line and Patterns color background of light blue Insert a class module from the Insert menu in the VBE and type: Public WithEvents formatEvent1 As Application
Set fObj1.formatEvent1 = Application End Sub Sub UnInitFE01() Set fObj1.formatEvent1 = Nothing End Sub
Private Sub formatEvent1_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range) Dim cInd As Integer For Each tcell In Target cInd = 1
With tcell If Application.WorksheetFunction.IsText(tcell.Value) Then cInd = 4 If Application.WorksheetFunction.IsNumber(tcell.Value) Then cInd = 8 If Left(.Formula, 1) = "=" Then cInd = 6 If cInd > 1 Then .Borders.LineStyle = xlContinuous .Borders.Weight = xlThin .Interior.ColorIndex = cInd .HorizontalAlignment = xlCenter End If
End With Next
End Sub The initialization process is done by running InitFE01, which creates an instance of the Application object named fObj1.formatEvent1. Then, the formatting procedure will run automatically each time a change is made in any worksheet of all open workbooks. This process serves as a general way to apply custom formatting and it leaves open the option of using conditional formatting for other purposes. However, one distinct advantage that the in-cell conditional formatting provides is that when an entry in a cell is deleted, the underlying format appears. As pointed out earlier in this section, this format does not have to be the default cell format. This feature would be difficult to accomplish without in-cell conditional formatting. MODIFYING HYPERLINK FORMATS One of the new features of Excel97 is the HYPERLINK function. When this function is used in a worksheet cell, a hyperlink is created as defined in the formula that allows the user to click on it to go to the linked file. The format of these hyperlinks is a font of Arial 7.5, blue and underlined. The hyperlinks that can be created in Excel97 allow the user to “click and go” to web pages, files that are accessed through file transfer protocol (FTP) on the Internet and local files (on the desktop or LAN). Through the use of conditional formatting, formats can be made that differentiate these 3 types of hyperlinks. =HYPERLINK("http://www.business.com/report/budget report.xls", "Click for report") for web page =HYPERLINK("ftp://ftp.microsoft.com/", “Get Microsoft file”) for ftp file =HYPERLINK("\\FINANCE\Statements\1stqtr.xls", D5) for local file
=NOT(ISERROR(FIND("http:",GET.CELL(6,globref)))) Define GlobalIsFTP as: =NOT(ISERROR(FIND("ftp:",GET.CELL(6,globref)))) Formula Is: = GlobalIsWebPage with Format Formula Is: = GlobalIsFTP with Format There is a type of doubleclicking goto feature that has been in Excel since version 4. This involves the doubleclicking of a cell that contains a linked formula. When this is done, the first reference that appears in the formula will be opened or activated. In order to use this feature, select Tools, Options, Edit from the menu and uncheck the “Edit directly in cell” box. A way to format cells containing linked formulas is shown below. This method assumes that the symbol “!” is performing a linking role in the formula. Define GlobalIsLinked as: =AND(ISERROR(FIND("HYPERLINK",GET.CELL(6,globref))),NOT(ISERROR(FIND("!",GET.CELL(6,globref))))) Formula Is: =GlobalIsLinked with Format GlobalIsLinked returns TRUE if the cell formula has an exclamation point symbol and the formula does not contain the word “HYPERLINK”. VIEWING SEQUENTIAL DATA BY DIRECTION The conditional formatting technique utilized in Fig. CF12 is a prime example of how simple formulas can be used to create a powerful visual effect. The data list represents the collection of values for a stock market index. The charting of this data allows the user to see the intervals where this market is going up or down. With the application of the following conditional format, the actual data can be viewed from this perspective as well. The following conditions were applied to the cells in column A, excluding A1.
Since all of the references are relative rather than absolute, a cell in this list always is formatted based on its value and the value of the cell immediately above it. Although this type of visual presentation will not replace the viewing of charted data, it certainly represents a valuable supplement to it. Caution: When pictures that are linked to cells that have conditional formats, a problem can occur if the conditional format is changed. The picture will not update immediately. In order for the updated format to appear, a new entry must be made on the sheet containing the linked cells or the update can be done manually by pressing the F9 key. FORMATTING DATES AND MERGED CELLS IN A SCHEDULE Most people that use spreadsheets invariably have sheets that are used for keeping track of schedules of some sort, and that involves using dates. One such scheduling model is shown in Figure CF13. In order to quickly locate the current week from a column of dates, the date column can be conditionally formatted to highlight those cells in the following way. Formula Is: =AND(A2>TODAY()-WEEKDAY(TODAY()),A2 Caution: You should be aware that when merging cells that have already had conditional formats applied, only the format of the upper-left cell of the cells to be merged will be retained in the resultant cell. There is no problem in applying a conditional format to a merged cell. Function IsMergedCell(mcell) IsMergedCell = Range(mcell.Address).MergeCells End Function
=CELL("address")=REFTEXT('CF14'!A1,TRUE); Formula Is: =IsLastAction with Format Light Blue cell shading & thin black border. Whenever this format is applied, and the user makes a change in the selection on the worksheet, the background of the active cell of that selection is shaded blue. Caution: Excel allows you to track changes during your work. To do this, select Tools, Track Changes, Highlight Changes from the main menu and check the Track changes while editing box. However, the ability to use conditional formatting is lost when using this feature. This also happens any time that the workbook is shared. USING CONDITIONAL FORMATTING TO VALIDATE A SORTED COLUMN The ability to sort data is fundamental to data analysis, and sorting in Excel is quick and easy. However, there no good way of visually examining a column of data to see if is has actually been sorted. Now, with the advent of conditional formatting, this has become possible. When the following formula is applied to all of the cells in a worksheet, the entries in each column turn red if they are not sorted (this formula was constructed for column ranges that begin in the first row). Define IsSorted as: =NOT(OR(AND(A$1:INDEX(A:A,COUNTA(A:A)-1)>=A$2:INDEX(A:A,COUNTA(A:A))) ,AND(A$1:INDEX(A:A,COUNTA(A:A)-1)<=A$2:INDEX(A:A,COUNTA(A:A))))) Formula Is: =IsSorted with Format Font Red To understand how the IsSorted formula works, it is necessary to break it down into its component pieces. Actually, using IsSorted for the formula name is somewhat of a misnomer, since everything in the NOT function represents a formula that returns TRUE if the referenced range is sorted. This formula uses the OR function to return TRUE if either of the two formulas that it contains are TRUE. These formulas are: AND(A$1:INDEX(A:A,COUNTA(A:A)-1)>=A$2:INDEX(A:A,COUNTA(A:A))) and AND(A$1:INDEX(A:A,COUNTA(A:A)-1)<=A$2:INDEX(A:A,COUNTA(A:A))) The first formula returns TRUE if the referenced range is sorted in descending order (the other formula is for ascending order). We will examine only the first formula, since they both operate on the same principle. In order to simplify this formula further, we will replace INDEX(A:A,COUNTA(A:A)-1) with A$4 and INDEX(A:A,COUNTA(A:A)) with A$5. The INDEX functions return these references when only A1:A5 of column A contain entries. In general, INDEX(A:A,COUNTA (A:A)) will return the last occupied cell in a column if all of the cells from row 1 down contain entries. Thus, the formula simplifies to: AND(A$1:A$4<=A$2:A$5) Let’s say that the range A1:A5 contains the values 1-5 respectively for this example. Then, this array formula returns an array of {TRUE,TRUE,TRUE,TRUE}, since 1<=2, 2<=3, 3<=4 and 4<=5. The AND function returns TRUE since all of its arguments are true. The > and < operators also work on text values and they use Excel’s sorting hierarchy to determine relative ranking. Please note that the sorting referred to here is for single columns only and not data tables that have been sorted based on a primary key. In this case only one of the columns in the table would be sorted as per the definition of sorting used in this example, which is that all entries are greater than or equal to the preceding entry, or less than or equal to the preceding entry. FINDING CELLS THAT HAVE CONDITIONAL FORMATS You might have a need to find out whether a cell has a conditional format. It is true that you can go to the menu under Format, Conditional Formatting… to see whether such a format has been applied. However, Excel provides a much easier way of accomplishing this. To find all the cells in a worksheet that have conditional formats, select Edit GoTo… from the menu and click the Special button. Then, check the Conditional Formats box and press Enter to highlight those cells. If there are no cells with a conditional format, a message box will appear to inform you of this fact. If you prefer, you can use this VBA procedure to perform the same job. Sub GotoCFCells() On Error GoTo CFMessage ActiveCell.SpecialCells(xlCellTypeAllFormatConditions).Select Exit Sub
CFMessage: MsgBox "No cells were found!" End Sub
Function HasCondFormat(cfcell) As Boolean If Range(cfcell.Address).FormatConditions.Count = 0 Then HasCondFormat = False Else
HasCondFormat = True End If
End Function One of the new event-driven VBA procedures possible in Excel97 is the Worksheet_SelectionChange procedure. Each time a sheet is created in a workbook, a corresponding sheet module appears in the Visual Basic Editor with this event as the default sub (without code). Each time a new selection is made on the worksheet and the corresponding sheet module contains the procedure shown below, a message box appears as shown when the active cell has a conditional format. Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) If HasCondFormat(ActiveCell) Then MsgBox ActiveCell.Address & " has a conditional format." End If
End Sub DATA FILTERING AND CONDITIONAL FORMATTING VIEWING MAXIMUM AND MINIMUM VALUES IN A FILTERED LIST One of the most powerful and easy-to-use features in Excel is the filtering of data in lists. Introduced in version 5, data filtering is invoked by selecting a cell within a list and choosing Data, Filter, AutoFilter from the menu. Excel then places buttons in each column-heading cell that activate dropdown boxes containing information from that column. When the user selects the desired filtering effect, the rows of data that do not correspond to the specified criteria are hidden from view. With conditional formatting, an additional layer of data analysis can be applied to the data set. Consider the application of the following conditional formats to the data table shown in Figure CF17. 1) Formula Is: =MAX(E$5:E$21)=E5 Format Font Blue & Bold 2) Formula Is: =MIN(E$5:E$21)=E5 Format Font Red & Bold This formatting causes the maximum and minimum value in each column to be highlighted. However, if the list is filtered, this formatting scenario does not guarantee that the max and min values in the filtered data will be highlighted. In order to do this, we must turn to a function specifically designed to be used with filtered lists, which is the SUBTOTAL function. Note: The SUBTOTAL function returns information about only the visible rows in a filtered list and two arguments are required. The first argument is a number from 1 to 11, which corresponds to a specific statistical function to be applied to the filtered data. For example, 4 is the MAX function and 5 is the MIN function. The second argument is the range of cells in the unfiltered list to be analyzed. When the conditional formats shown below are applied to the data table, the filtered list will now update to show the max and min value for the filtered data. APPLYING CUSTOM FUNCTIONS TO FILTERED LISTS It would be nice if this technique could be extended to highlighting the Nth smallest and largest values in a filtered list. Unfortunately, the SUBTOTAL function does not include the SMALL and LARGE functions among its available options. In order to create such an effect, the VBA function procedures shown below were constructed to perform these tasks. Function SSUBTOTAL(sRange, snum As Integer) Dim sCell Dim sArray() As Double Dim sCount As Integer snum = Int(Abs(snum)) sCount = Application.WorksheetFunction.Subtotal(3, sRange) ReDim sArray(sCount) n = 0
For Each sCell In sRange If Not sCell.EntireRow.Hidden Then n = n + 1 IsArray(n) = sCell.Value End If Next
If snum > sCount Then SSUBTOTAL = Application.WorksheetFunction.Max(sArray) Else SSUBTOTAL = Application.WorksheetFunction.Small(sArray, snum) End If End Function The SSUBTOTAL function works like the SUBTOTAL function but it returns the Nth smallest value in a filtered range (the LSUBTOTAL function returns the Nth largest value). An explanation of the key elements of the VBA code for the SSUBTOTAL function follows (you can find the code for the similar LSUBTOTAL function on the accompanying CD).
Dim sArray() As Double Dim sCount As Integer
For Each sCell In sRange If Not sCell.EntireRow.Hidden Then n = n + 1 sArray(n) = sCell.Value End If
Next The variable n is set to 0 to be used in the For Each…Next loop. The statement Not sCell.EntireRow. Hidden is true for visible rows. If it is true, the variable n is incremented by one and the value for the visible cell is added to sArray. If snum > sCount Then SSUBTOTAL = Application. WorksheetFunction.Max(sArray) Else SSUBTOTAL = Application. WorksheetFunction.Small(sArray, snum) End If Once the For Each…Next loop has completed, sArray now contains all of the filtered values in the designated column. If the user has used a value for the argument snum that is equal to or larger than the number of filtered values, SSUBTOTAL returns the maximum value for the filtered values. Otherwise, this function returns the nth smallest filtered value. Even without conditional formatting , the SSUBTOTAL and LSUBTOTAL functions could be very useful as custom worksheet functions in returning information on a filtered list. The formulas =SSUBTOTAL (E5: E21,A2) and =LSUBTOTAL($E$5:$E$21,A3) are entered in the cells E1 and E2 respectively, as shown in Figure CF18. The cells A2 and A3, which are arguments for these functions, are linked to the spinner controls, which change the values in these cells. When the custom functions are used to conditionally format the filtered data table, the variably large and small filtered values are visible in the table instead of having to return those values to separate cells. FORMAT-FILTERING A DATA LIST It is true, as discussed previously, that data filtering provides a powerful way for the user to view data. However, in some cases the user may prefer a method of viewing filtered data that highlights the data that matches the established criteria but leaves all of the data in the list visible. Conditional formatting provides a method for achieving that effect, which is shown in Figure CF19. On the worksheet cell shortcut menu there is a menu item called “Pick from list”. Although poorly documented, this command is quite useful for extracting information from a column list of text items. In order to use this feature in building a formatted list, right-click a cell immediately above a column heading in the list and select “Pick from list”. A dropdown menu will appear that contains all of the unique items in that column list. When the desired item is clicked, that item will appear in the active cell. Thus, the cells above the list can be used as a criteria range for this conditional formatting technique. This allows for the construction of a list that is filtered by formatting. Formula Is: =$C$2&$D$2&$E$2&$F$2=IF($C$2="","",$C4)&IF($D$2="","",$D4)& IF($E$2="","",$E4)&IF($F$2="","",$F4) with Format Font Bold. This formula compares the concatenated string from the criteria range with a concatenated row record entry. If the record matches the criteria string, the entire row record is formatted bold. This conditional formatting technique works great for data tables that consist only of text entries. USING A COMPLEX FORMULA FOR NUMERIC FIELDS However, if any of the fields are numeric, this method does not work since the fields in a table that contain numbers do not work with the Pick from List dropdown. In order to simulate the effect that data filtering provides for numbers, another method was needed. In the example shown in Figure CF20, a dropdown control is used to select the operator to be used in conjunction with the value placed in the criteria range (F2) for the data column. Formula Is: =$C$2&$D$2&$E$2&CHOOSE($F$1,IF($F$2<$F4,$F4,""),IF($F$2>$F4,$F4,""), IF($F$2<=$F4,$F4,""),IF($F$2>=$F4,$F4,""),IF($F$2=$F4,$F4,""))=IF($C$2="","",$C4)&IF ($D$2="","",$D4)&IF($E$2="","",$E4)&IF($F$2="","",$F4) The dropdown control is linked to cell F1 and the value that is returned to that cell is used by the CHOOSE function to pick the desired treatment of the value in cell F2. MULTI-SHEET DATA COMPARISON BY CONDITIONAL FORMATTING The comparison of data sets is at the heart of data analysis. In Excel, pivot tables are the primary way that users can view relationships among groups of data. However, conditional formatting can provide alternate methods of comparing data that do not require any manipulation. In particular, it is common to have yearly worksheets that contain the related costs for running a business. A simple example of this is shown in Fig. 1994. Caution: Cells that are part of a pivot table can receive a conditional format, but they lose it when the data in the pivot table is refreshed. It is useful to view how the numbers change from one year to the next. If each sheet is set up identical to the next years’ sheet, a comparison can be made by using conditional formatting and applying a custom function that acts as a lookup for the previous sheet (shown below). Function PrevShtCellValue(prevcell) PrevShtIndex = ActiveCell.Parent.Index - 1 PrevShtCellValue = Sheets(PrevShtIndex).Range(prevcell.Address).Value End Function The VBA statement ActiveCell.Parent.Index returns the sheet number of the caller worksheet and subtracting one from this number gives the number of the previous sheet. This number is stored in the variable PrevShtIndex and is used with the Sheets method to return a reference to the previous worksheet. The Address property of the function argument prevcell returns the cell reference as text, which is used by the Range method to afford the cell reference. The Value property then returns the value in the cell on the previous worksheet. For the range C3:F14 on the summary sheets for 1995, the following conditional formats were applied.
With this applied format, it becomes very easy to view year-to-year changes throughout the data table for 1995. For example, all of the values that are formatted blue on the table for 1995 are greater than the corresponding values on the 1994 table. Using the data input method with spinner controls introduced earlier in this chapter, additional comparison information can be brought out in the data table with conditional formatting. With the spinner controls linked to cell K3 and K6 in Figure 1996, the data table was formatted with formulas modified to use their input.
Figure 1996The comparison of 1995 vs. 1996 data can now be viewed under a multitude of percentage difference scenarios by using the spinner controls to customize the formatting. Information reflecting the custom settings is shown in the cells to the right of the data table. The formulas used in these cells also use the value in the cell linked to the spinner control. SUMMARY The focus for this chapter has been on examining numerous and diverse examples related to conditional formatting. It is clear that conditional formatting can have a big effect on the way you work with Excel. From a user standpoint, the ability to derive information from data visually is a significant step forward in data analysis. From a performance standpoint, conditional formatting can in many cases cut down on the number of formulas needed in a worksheet. You may not have a specific need to use most of the examples presented here, but the techniques shown here should prove to be invaluable in the building of your custom formatting solutions. Although some of the formulas used to create the formatting special effects presented in this chapter may seem to be somewhat complex, they lie at the heart of Excel productivity. It is hoped that this material will provide a deeper understanding and appreciation of the possibilities that Excel and conditional formatting present. Yüklə 86,81 Kb. Dostları ilə paylaş:
|