Inserted at the beginning of the module is commonly used, the data and the view,
Assignment 2: generate a line height 30, column width 15 sixth column column width (45), called "team learning" Excel table, requirements are as follows: 1, the first listed as job ID, background color to light blue; 2, the second label position type, font color is red; 3, the third as a degree, each cell has a drop-down list, option for college, bachelor, master, doctor; 4, the fourth as industry direction, cell borders for the red dotted line; 5, 5 as compensation, data type of currency, retain two decimal places. 6, 6 listed as position tag,
Adjust the line height column width: selection form (CTRL + A) at the start of the cell under the module, A formatting options, select line height, 30, and set or formatting options, select columns wide, and set up 15, the sixth column column width adjustment, select the sixth column, click on the column label, select start - format - column widths, 45, set to rename table: right click on the select rename at the lower left corner sheet1, fill in "excel team learning", thus creating the form is complete,
First as A job ID, select the first column, font module - there is A pot of paint color, choose shallow blue, the second column is the position type, select the second column, start - font - underlined A - select the color to red, the third degree, the cell has A drop-down list, is A problem in examining data validity, select the C2, Ctrl + shift + down arrow, select the C2 and under the whole column, and then click data - data validation, referring to the following figure set
Fourth as industry direction, cell borders for the red dotted line, select the fourth column here, right set the cell format - the borders, specific Settings below
5 as compensation, data type of currency, retain two decimal places, select the fifth column salary, start - digital module routine - there's a drop-down list, choose the currency,
The final effect as shown:
A generated three action object for the quest "team learning Excel table" freeze panes line 1-3:
Selected in the fourth row, view - freeze panes - the first choice, according to the selected area divided into
Will be the sixth column respectively: selected F column, select tools - data - data respectively, according to the comma respectively, can preview took effect, and then determine, for each column to add a new title, because here have
List the type of export, should use the search to replace the '[' and'] ', use CTRL + H choose find [, replace place don't write characters
Use conditional formatting to pay column is larger than 8000 income fill for dark green, and screening out:
Choose the salary column value area, start - style - conditional formatting to highlight cell rules - more than 8000, choose custom style, choose fill - dark green
Screen is larger than 8000 salary, select the salary column, start - edit module - sorting and filtering, there are two kinds of screening form a choice according to the color filter (is the unit of data screening of blackish green color), is a kind of digital filter selection is more than - 8000,
Remove the duplicate values the second column, only keep the only value:
Tools module - choose the second column, data - data delete duplicate values, with the currently selected area, click choose duplicates, in order to verify whether delete, down to look at whether there are blank, one is inserted into the pivot table, line to choose the second column titles, value still choose the job title, see if it is 1,
Excuse me: learning and familiar with a few common module operation, today start module, data module, and common operations such as view module, but want to know how to use the VBA means the folder more tabular column headings as merge the data to a line direction concat alone?