First, as shown in Figure 3. 1, enter Chinese characters "total score", "average score", "grade", "passing number" and "highest average score" in each position.
"The lowest average score" and "The average score is higher than 75 people".
(1) Find the total score
Enter "=SUM(D2:F2)" or "=D2+E2+F2" (without quotation marks) in G2.
Drag the mouse to G 15.
Note: Do not enter: =97+86+67. Although the values in G2 are correct, you can't use the padding function.
The following operations on finding the average score, grade, and number of words passed are similar to summation operations, so only formulas are given.
(2) Round off to get the average score
Enter the formula in H2 and fill it in, and the formula is =ROUND(AVERAGE(D2:F2), 1).
(3) Seek achievement
Enter the formula in I2 and fill it in, the formula is = if(H2 >；; =85, "excellent", if (H2 >; =60, "qualified", "unqualified").
(4) Pass all the language requirements.
Enter the formula in J2 and fill it in, the formula is = if (and (D2 >; =60, E2 & gt=60), "qualified" and "unqualified")
(5) The number of people with the highest average score, the lowest average score and the average score higher than 75.
Enter the formulas in B 17 to B 19 respectively: =MAX(H2:H 15), =MIN(H2:H 15), =COUNTIF(H2:H 15, "&
(6) Copy the student number, name and average score to the table 1.
Select the three columns of related content you need and copy them to the clipboard with CTRL+C. Click cell a 1 in the table1to use them.
Ctrl+V paste.
If you only need numerical values, you can use the Paste Special command on the Edit menu.
Operating steps:
Requirement 2: Conditional formatting and page setup.
(1) conditional format
Select D2:F 15 and click the conditional format command item in the format menu in the drop-down bar (open the dialog box as shown in Figure 3.2).
Select "greater than or equal to" in the table box, enter 90 in the text box on the side, and click the format button (a dialog box appears) to select "Graphics".
The color of the chassis label is light blue. Click OK (return to the Conditional Formatting dialog box). Click the Add button in the.
Select "less than" in the drop-down list box in the second row, enter 60 in the text box on the side (a dialog box appears), select "Tilt" in the font tab and set the color to red, click OK, and then click OK.
Figure 3.2 conditional format dialog box and its conditional drop-down list box.
(2) Set "Top Title Line"
Use the Page Setup command on the File menu (open the Page Setup dialog box), click the Sheet tab, and then click the Page Setup tab.
Enter "$ 1:$ 1" in the Top Title Line, and then click OK.
In this way, when worksheet data is printed on multiple pages, the first line (or lines) is printed as a header on the header of each page. Users can
Add some data to the original worksheet and use the print preview command to observe the effect.
Create a chart Create a chart Create a chart Edit a chart Edit a chart Edit a chart Edit a chart
Complete the operation:
Create a chart as shown in Figure 3.3 for Sheet2 in the theme 1 (the worksheet of corresponding data has been calculated by the formula in Figure 3. 1);
Move the chart to A22:H38 area; Then change the chart type to data point line chart; Modify the title so that the title text is "Guan Gong"
Professional student performance chart "; Add computer scores to the chart; Add the text "Class 1" to the diagram.
Operating steps:
(1) Create a chart
Select the data range C 1:E6 (including the column label part), and then click Chart on the Insert menu (the chart of the Chart Wizard appears.
Table Type) Select the Cluster Column Chart as the chart type, and then click Next (Chart with Chart Wizard appears).
Table source data ") If the displayed data area is feasible, click Next. Otherwise, reselect the data area and click "Down".
The "Step" button ("Chart Options" of the chart wizard appears) is set to "Student Achievement Chart", and the title of the classification X axis is set to "Last Name".
Name ",and set the value of the Y-axis title to" Score "; Set the data values in the data flags tab, and then click Next (the.
Chart Wizard -4) Select Insert as Object for this topic, and then click Finish.
(2) Move the chart and change the size of the chart
Click the chart area and drag it to the area of A22:H38. If the chart is large,
Small is not suitable, you can click on the chart area to drag the handle.
(3) Change the chart type
Select Chart in the Chart menu (Chart menu appears).
Select the chart type command item in the menu (the chart type dialog box appears), and select the data point line chart whose chart type is line chart.
Click the OK button.
(4) Modify the title
Select Chart Select a chart option from the chart menu.
Figure 3.3 Creating a Chart Experiment
Command item (Chart Options dialog box appears) Click the Title option.
The title of the card is "chart of public administration students' grades", and click "OK".
(5) Add data to the chart
Select the added data range F2:F7 Use the Copy command to select the chart, and then use the Paste command.
If you want to delete the data series in the chart without deleting the corresponding data in the worksheet, you can click the data series you want to delete, and then press.
Delete key.
(6) Add text to the chart.
Click on the chart, directly enter the text "Class 1", press enter (Excel automatically inserts a "text box" in the middle of the chart), and then press.
If necessary, drag the text box to the appropriate position in the diagram. (You can also use the text box.)
Topic topic topic topic 3 data table operation data table operation data table operation:: sorting sorting summary: sorting sorting summary.
Complete the operation:
Copy the a 1: j15 area of Sheet2 in the theme1to "Table 2" to form a data list. Just press "Name" for the data in Table 2.
Sort in ascending order; Reorder the data in Table 2, and the total score is required to be 1 keyword, with math scores as the second keyword and Chinese scores as the second keyword.
Sort by the third keyword (all descending); Copy the "Table 2" worksheet, rename the new table as "Table 3", and classify the data in Table 3 by specialty.
Summarize in class and find the average value of each numerical field in each major.
Operating steps:
(1) Copy and sort
Select the A 1:J 15 area in Sheet2, and use the copy and paste commands to copy to table2. Click any non-blank cell in the Name column (don't select a column), and then click Common.
"Sorting in Ascending Order" button in the toolbar (arranged in Pinyin order by default).
(2) multi-level sorting
To use the menu command for multilevel sorting, follow these steps:
Click any cell in the data list with the mouse, and then use the Sort command on the Data menu.
(Open the sorting dialog box) Make the settings as shown in Figure 3.4 in the dialog box, and then click OK.
Button.
Figure 3.4 Multi-level sorting
(3) Classification and summary
Copy the backup of Table 2 and rename it to Table 3. Sort the majors first and then use the numbers.
Displays the dialog box in the menu "Classified Summary" command (open the "Classified Summary" dialog box).
As shown in Figure 3.5 (setting classification fields, summary methods and summary items), click OK to make the settings.
Button.
At this point, three scoring symbols will appear on the left side of the worksheet, of which the last level is the previous level.
No detailed data is provided. Users can use hierarchical display symbols to show and hide detailed data, as shown in the figure.
As shown in 3.6, the general bus belongs to 1 level, and the summary data of each major belongs to level 2, so the students are fine.
The segment data row belongs to level 3.
If you want to show or hide the detail lines under a specific level, you can click or under the level button.
Layered display symbols. Figure 3.5 Classification summary
Undo Subtotal is to click the Delete All command button in the Subtotal dialog box.
Figure 3.6 Classification summary results
Title Title Title Title 4 Datasheet Operation Datasheet Operation Datasheet Operation:: Filter Data: Filter Data Filter Data Filter Data
Complete the operation:
Copy the "Table 2" worksheet, rename the new table as "Table 4", and select data rows with higher total scores according to 20% of the total number of people; Copy "Table 2"
Worksheet, the new table is renamed as "Table 5", showing data rows with language and mathematics greater than or equal to 80; Re-filter Table 5 to show the language.
Data rows with text greater than or equal to 90 or less than 60. Copy the "Table 2" worksheet, rename the new table as "Table 6", and perform advanced filtering: filter out.
A data row whose language or mathematics is greater than or equal to 90.
Operating steps:
Excel provides automatic filtering and advanced filtering functions. By filtering, you can compress the data list and hide the unqualified information lines.
Only the qualified information lines are displayed.
(1) Filter the top 20% of data rows.
Copy the Table 2 worksheet and rename the new table as Table 4. Click any cell in the Table 4 data list.
Single "filter" and "automatic filter" command items, click the drop-down arrow to the right of "total score" and select "(front 10 …)" (open as shown in figure (a)
Dialog box) dialog box, click OK to set Max, 20 and Percent.
(a) (b)
Figure 3.6 "Top 10 automatic filtering "and" custom automatic filtering method "dialog boxes.
At this point, the unqualified lines have been hidden (not deleted), and the line numbers are no longer continuous.
(2) Display data rows with language and mathematics greater than or equal to 80.
Copy the Table 2 worksheet and rename the new table as Table 5. Click any cell in the Table 5 data list to use the Data menu.
Click the drop-down arrow to the right of "Language" and select "Custom …" (as shown in Figure 3.6(b)).
As shown in the dialog box) set "greater than or equal to", 80 click "OK" button, click the drop-down arrow on the right side of mathematics, and select "Custom"
In the dialog box, select "greater than or equal to", enter 80, and click "OK".
At this point, list the data rows whose Chinese and mathematics are greater than or equal to 80. It is equivalent to screening mathematics again on the basis of Chinese screening.
choose
(3) Display data lines with language greater than or equal to 90 or less than 60.
Click any cell in the data list first, and then use the data menus "Filter" and "Auto Filter" to remove the original screen.
Select the arrow.
Then use the "Filter" and "Auto Filter" command items in the data menu to open it.
In the "Chinese Custom Automatic Filtering Method" dialog box, set "Big" in Figure (b).
Equal to ",90, select" or "radio button," less than ",60, and click" Yes ".
Settings "button.
(4) Screening out data rows with language or mathematics greater than or equal to 90 Figure 3.7 Conditions for advanced screening
Advanced filtering can be adopted by using OR operation between different projects.
Copy the "Table 2" worksheet, rename the new table as "Table 6", and enter it into the cell in "Table 6" that is not adjacent to the data list as shown in the figure.
The displayed condition (in L 1:M3) Click any cell in the data list and use the data menu filter advanced filter.
To enter "L 1:M3" or "$L$ 1:$M$3" in the condition area, click OK.
Button.
Note: In the advanced filter, the conditions of the AND operation are placed on the same line, while the conditions of the OR operation should be placed on different lines.
Theme theme theme theme 5 Create Create Create Pivot Table Pivot Table Pivot Table Pivot Table
Complete the operation:
For the "Production" table shown in 3.8, count the man-hours spent by each team on each product, as shown in Figure 3.9.
Detailed data of 20. 1 and 140 in 3.9.
Operating steps:
You can get statistics by creating a PivotTable, that is, using PivotTable and Numbers.
Create a PivotTable report according to the Perspective Wizard, and display and organize it by dragging data fields.
Data. The specific operation is:
(1) Create a PivotTable report
Click any cell in the original data list, and then click data in the data menu.
PivotTable and PivotChart commands (The PivotTable and PivotChart Wizard appears
Step 1) Select the data source type and report type (using the default settings) and click "Figure 3.8 Raw Data of Production Table"
Step button (Step 2 of the PivotTable and PivotChart Wizard appears).
Select the data source range (you don't need to change it), and then click the Next button (step 3 of the PivotTable and PivotChart Wizard appears).
Determine the display position of the PivotTable (the default is "New Worksheet"), and click the "Layout" button (the layout dialog box as shown in Figure 3. 10 appears.
Box) Drag the Team field to the row, the Product field to the column, the Working Time field to the data area, and then click OK.
Button (return to step 3 of the wizard) Click the Finish button.
Figure 3. 10 PivotTable layout
Figure 3.9 Pivot Table Created from Original Data
If the user wants to change the summary method, such as asking for the maximum value, he can double-click the summation item in Figure 3.9: Working hours to open the data.
The PivotTable field dialog box sets the summary method (such as count, average, maximum, etc.). ).
If you click the Finish button instead of the Layout button in the wizard, you can also set up the report on the worksheet.
The layout of.
2. Check the detailed data
Double-click the cells containing data 20. 1 and 140 in Figure 5.9 respectively to view the detailed data.
According to the website http:/10.71.45.100, it is required to draw the text and chart (Word operation data) as shown in figure 2. 1.
Text and tables are mixed.
This problem is the most basic operation, so I won't explain it in detail, just say it briefly.
(1) Use the File menu Page Setup command to set it.
"Paper size" is "16K", and the left-right distance is 2cm up and down.
The margin is 1.2 cm.
(2) the title is centered, "official script" and "initial number". And use "format" dishes.
Single border and shading command, set 20% red shading.
(3) The text is "No.2" and the format menu "Paragraph" is used.
"Drop" command, set the first line indentation "2 characters".
(4) using the "column" command in the "format" menu, the column can be
Use the Separator command on the Insert menu to select Continuous.
As a balance bar).
(5) Use the "Insert" and "Form" commands in the "Form" menu.
Insert a table. Use the settings in the Tables and Borders toolbar to set line styles and edges.
Box.
(6) Use the "Text Box" button in the drawing toolbar to insert a text box;
Use the Insert Picture button in the Drawing toolbar to insert a picture in the text box;
Figure 2. 1 word title 1 effect
Use the Format Text Box command on the text box shortcut menu to set the version.
Type is "compact", and select "wireless bar color" at the line color; Move the text box to the appropriate position.
(7) Use the Insert menu "Picture WordArt" command to insert WordArt.
Theme theme theme 2 Format replacement Format replacement Format replacement Format replacement
According to the text provided by the website http:/10.71.45.100, the first paragraph of the document ("beekeeping has a long history, …") is replaced by the format.
All numbers in the paragraph are set to red and underlined (single line); Replace all marks of two consecutive paragraphs with the following
Paragraph marks.
The operation method is as follows:
(1) Select a paragraph.
(2) click the "replace" command in the edit menu (open the dialog box), select the "replace" tab, and click the "high
Level Button Click the Find What box, and then click the Special Characters button.
The symbol "#" of "any number" appears in the box)
(3) Click the "Replace with" box, click the "Special Characters" button and select "Find what" (in this case, the "Replace with" box.
The symbol "&"which means "find what" appears in. ) Click the Format button and select a font (the replacement font appears).
Dialog box) Select a color and underline in the Replace Font dialog box, and click OK (replace with,
The format set by the user is shown in the figure below.
As shown in 2.2)
(4) Click "Replace All" at most.
Word prompts "Word has completed the selection.
Content search, * * * replaces n locations. Search or not
For the rest of the document "(n is a specific character
Number), click the No button.
Mark all two consecutive paragraphs.
Do some operations on the marked part of the paragraph: it can be
Find the content and replace it with "Unlimited"
Format ",and then, where the content is found, make the
With the special character ^p^p (marked by two paragraphs),
Figure 2.2 Example of "Format Substitution"
Use p, where is replaced by, and click Replace All.
Change the button.
Theme theme theme theme theme theme 3 Create a directory Create a directory Create a directory Create a directory Create a directory Create a directory Create a directory and an index and an index and an index.
Complete the following operations: (1) Set multi-level symbols for the titles of chapter 1, chapter 2, 1. 1, 1.2, 2.2. 1, etc. , and create a directory.
(2) Establish an index similar to that shown in Figure 2.3.
(1) Create a directory
When you write a book or a graduation thesis, you should not only mark the first chapter, the first section or 1. 1, but also
Make a directory. If you insert header lines only according to the text style, they will be printed.
This is not much different from applying the heading style, but in other ways you may lose many convenient operations provided by Word: making.
Using document structure diagram has no effect; To make a catalog, you need to manually enter the title and page number of the catalog. If the article is modified,
Then the page numbers in the catalogue must also be re-checked and revised.
If you use the heading style or set multilevel symbols, you can use the Word command to quickly create a table of contents.
A. setting multilevel symbols
The easiest way to set multilevel symbols is to use the default title style to make titles. The basic steps are: move the cursor to the title of the chapter, such as 1 chapter, and select it from the "Style" drop-down list box in the formatting toolbar.
Title 1. Move the cursor to the title of the section (for example, 1. 1), and select "Tag" in the style drop-down list box in the formatting toolbar.
Question 2 ".
Use the same operation for other chapter titles; Or use the format brush to copy the title format of chapter 1 to the second chapter.
At the chapter title, copy the title format of 1. 1 to other secondary titles, and copy the title format of 2.2. 1 to other tertiary titles.
Location.
At this time, you can see the corresponding effect by opening the document structure diagram.
You can also use the Format Bullets and Numbers command item to set multi-level titles.
B. Create a directory
With multi-level titles, it is very convenient to establish a directory. The basic steps are as follows: move the cursor to the end of the document, and insert a page break with Ctrl+Enter to make the table of contents at the end. Use the Insert menu Reference Index and Table of Contents command to click the Table of Contents option (open the dialog box).
The display level of the card is set to 3. Click OK.
After creating the table of contents, once the document changes, the page number will also change. At this point, you can update the table of contents by clicking the mouse.
Anywhere in the directory, press the F9 function key, and an Update Directory dialog box will appear on the screen.
Box, the user can select the item to update as needed, and then click OK.
(2) Establish an index
As can be seen from Figure 2.3, it is necessary to establish "ladybug", "ladybug" and "honey"
Bees and many other indicators. Among them, "beekeeping" is a sub-index and "ladybug" is a sub-index.
All tags, so as you can see from the page number, the corresponding figure in the document, it
Appears on pages 2 and 3.
A. To establish an index, first mark the index items, as follows:
Select the word "bee" in the document and use the insert menus "Reference" and "Cable".
References and Contents command Click Mark Index Entries on the Index tab to index the document according to Figure 2.3.
Button (to open the dialog box), the word "bee" will automatically appear in the "main index item".
Click the "Mark" button.
Similarly, "ladybug" and "ladybug turtle" are marked as index items respectively.
To establish a secondary index, you can: position the cursor on the page where "Beekeeping" is located, open the "Mark Index Items" dialog box, and click "Main".
Enter the word "bee" in the index item, enter the word "beekeeping" in the sub-index item, and click the "Mark" button.
For the word "ladybug", a similar method is adopted, except that the "Mark All" command button is finally clicked in the dialog box.
B. create an index. The steps are as follows:
Click where you want to insert the index. Use the Reference, Index and Table of Contents command on the Insert menu, and click Index.
Select the number of columns of the index as 1, select the format as "official", and click "OK".
Title Title Title Title Title 4 Create Title and Cross Reference Create Title and Cross Reference Create Title and Cross Reference Create Title and Cross Reference Create Title and Cross Reference Create Title and Cross Reference
Requirements: First insert a picture of ladybug with the caption "Figure 2. 1" (because it was inserted first, so
The caption number starts from 1), use the word "(see Figure 2. 1)" next to the ladybug as shown in the figure, and then insert a ladybug.
As shown in figure 2.4, the explanatory diagram is also used next to the ladybug. See also text. (You can use other diagrams for specific operations.
Generation)
If the numbers of these figures are manually input, then when a figure is inserted into the figure of "turtle-patterned ladybug", then "turtle"
The figure number of striped ladybug will not automatically become 2; If the drawing numbers of these reference texts are entered manually, then the actual drawings
If the number changes, the drawing number at the reference text will not change. In order to make the figure number change with the position of the figure
When changing, the caption should be used; In order to make the drawing number in the reference text change with the specific drawing number, cross-reference should be used.
Use.
The operation steps are as follows:
(1) Insert pictures and captions
Use Insert Menu Picture in File to insert a chart.
Click the picture, and then click the Text Box tool in the Drawing toolbar.
Zoom pictures and text boxes.
Remove the border of the text box and move the text box to the appropriate position to make a selection.
Screen, press the "->" key, and then press the "Enter" key, the light is on.
Move the marker to the next line of the picture in the text box and use the Insert disk.
A single "reference" and "title" command (open the dialog box) click "new
Tab button (dialog box appears) Enter the text "Figure 2", click "OK" and then click the "OK" button (where the cursor is located
Insert the caption "Figure 2. 1" at the position and then enter "turtle ladle".
Worm ".
Figure 2.4 Example of Cross Reference
Description: The 2 entered in the new tab indicates Chapter 2. Ruobiao
If the topic is automatically numbered, you can use the numbering button in the caption dialog box to request "include chapter number", so that
When inserting the caption, Word will automatically add the chapter number.
Add the ladybug diagram according to the above steps, then copy the caption "Figure 2. 1" at the bottom of the ladybug diagram, and then
Enter the text "Ladybug".
In this way, two pictures and their explanatory text have actually been inserted. But on the surface, it is all "Figure 2. 1". If you want to see it at once.
To do this, you can use the update field.
(2) Insert cross reference:
After moving the cursor to "2.2. 1 ladybug", enter "(see) and use" Reference "and" Cross Reference "in the Insert menu.
Select "Figure 2" and use the command in "Reference Type" (open the dialog box as shown in Figure 2.5) to select the reference content.
"Label and number only", select "Figure 2. 1 ladybug" in "Which caption is cited" and click the "Insert" button (this
When the "Cancel" button automatically changes to the "Close" button), click the "Close" button (cross-reference insertion is completed) and then go to the next one ").
Figure 2. 1 in the two figures 2.5 here is because it has not been carried out.
Operation of domain update.
Quote "ladybug" in the same way.
(3) domain name update
Use a print preview once. Close the preview and view it again.
The original explanation and quotation are consistent with Figure 2.4.
In fact, after making changes, it should be made in time.
Update operation of row field.
In addition, if you want to delete the cross reference, you can use: Select.