cs 202, ics 202…..class 7
DESCRIPTION
CS 202, ICS 202…..Class 7. By A.Penjira Kanthawongs. Class 7 - Agenda. Lecture on “Excel Project 1.” G. HW: Find and input inf. to create an Excel document with a Chart for your company (Look at requirements like Page E 1.60 and from the lecture) turn in next class. - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: CS 202, ICS 202…..Class 7](https://reader035.vdocuments.us/reader035/viewer/2022081506/568148b2550346895db5c987/html5/thumbnails/1.jpg)
A.Penjira Kanthawongs 1
CS 202, ICS 202…..Class 7
By A.Penjira Kanthawongs
![Page 2: CS 202, ICS 202…..Class 7](https://reader035.vdocuments.us/reader035/viewer/2022081506/568148b2550346895db5c987/html5/thumbnails/2.jpg)
A.Penjira Kanthawongs 2
Class 7 - Agenda
Lecture on “Excel Project 1.” G. HW: Find and input inf. to create an Excel document with a Chart for your company (Look at requirements like Page E 1.60 and from the lecture) turn in next class. Ind. HW: Do 20 MC and 10 SA questions for Excel P. 1 and turn in next class and read Excel Project 2 in advance. Announce Midterm scores.
![Page 3: CS 202, ICS 202…..Class 7](https://reader035.vdocuments.us/reader035/viewer/2022081506/568148b2550346895db5c987/html5/thumbnails/3.jpg)
A.Penjira Kanthawongs 3
Microsoft Excel is…
A spreadsheet program that allows you to …Organize dataComplete calculationsMake decisionsGraph dataDevelop professional looking reports
![Page 4: CS 202, ICS 202…..Class 7](https://reader035.vdocuments.us/reader035/viewer/2022081506/568148b2550346895db5c987/html5/thumbnails/4.jpg)
A.Penjira Kanthawongs 4
Publish organized data to the Web
Access real-time data from Web sites
![Page 5: CS 202, ICS 202…..Class 7](https://reader035.vdocuments.us/reader035/viewer/2022081506/568148b2550346895db5c987/html5/thumbnails/5.jpg)
A.Penjira Kanthawongs 5
4 Vacation Packages Bahamas Repose Daytona Delight Key West Haven South Padre Del Sol
4 Sales Channels- Mail - Campus
- Telephone - Web
0 1st/ 2 3 4nd/ rd/ th 3 6 9 12months/ months/ months/ months
No. of Qtr in 1 yearJan. Mar. Jun. Sept.Dec.
![Page 6: CS 202, ICS 202…..Class 7](https://reader035.vdocuments.us/reader035/viewer/2022081506/568148b2550346895db5c987/html5/thumbnails/6.jpg)
A.Penjira Kanthawongs 6
Source of thedata: From Eric Jacobs, Chief financial officer (CFO) of Fun – N – Sun Sojourn.Calculation:
Total sales for each sales channel
Total company sales
Total sales for each vacation package
![Page 7: CS 202, ICS 202…..Class 7](https://reader035.vdocuments.us/reader035/viewer/2022081506/568148b2550346895db5c987/html5/thumbnails/7.jpg)
A.Penjira Kanthawongs 7
Chart requirements:
Below the worksheet, construct a 3-D Column chart that compares the amount of sales to the 4 sales channels for each vacation package.
![Page 8: CS 202, ICS 202…..Class 7](https://reader035.vdocuments.us/reader035/viewer/2022081506/568148b2550346895db5c987/html5/thumbnails/8.jpg)
A.Penjira Kanthawongs 8
4 major parts of Excel areWorksheets -> allow you to enter, calculate, manipulate, and analyze data such as numbers and text.Charts -> pictorially represent data.Databases -> manage data.
![Page 9: CS 202, ICS 202…..Class 7](https://reader035.vdocuments.us/reader035/viewer/2022081506/568148b2550346895db5c987/html5/thumbnails/9.jpg)
A.Penjira Kanthawongs 9
Web support -> You can access real-time data using Web queries.
From the orange box at the From the orange box at the bottom of page E 1.7bottom of page E 1.7Worksheet Development …The key to developing a useful worksheet is careful planning.
![Page 10: CS 202, ICS 202…..Class 7](https://reader035.vdocuments.us/reader035/viewer/2022081506/568148b2550346895db5c987/html5/thumbnails/10.jpg)
A.Penjira Kanthawongs 10
Worksheet Development 1. Define the problem, including
need, source of data, calculations, and charting and Web requirements
2. Design the worksheet3. Enter the data and formulas4. Test the worksheet
![Page 11: CS 202, ICS 202…..Class 7](https://reader035.vdocuments.us/reader035/viewer/2022081506/568148b2550346895db5c987/html5/thumbnails/11.jpg)
A.Penjira Kanthawongs 11
Vocabularies in this chapter…
Workbook -> notebookWorksheets -> inside the workbookSheet Tab -> at the bottom of the workbook
Worksheets can be added -> 255 at a maximum
![Page 12: CS 202, ICS 202…..Class 7](https://reader035.vdocuments.us/reader035/viewer/2022081506/568148b2550346895db5c987/html5/thumbnails/12.jpg)
A.Penjira Kanthawongs 12
Figure 1-6E 1.10Workbook
Rectangular gridcontaining columns (vertical)
..and rows (horizontal)
![Page 13: CS 202, ICS 202…..Class 7](https://reader035.vdocuments.us/reader035/viewer/2022081506/568148b2550346895db5c987/html5/thumbnails/13.jpg)
A.Penjira Kanthawongs 13
2 2 2 22222 22 - 2 5 6 A , B , C , … to.. IV
..and 65,536 rows -1,2,3, … to65,536
.. Cell reference “C5”
![Page 14: CS 202, ICS 202…..Class 7](https://reader035.vdocuments.us/reader035/viewer/2022081506/568148b2550346895db5c987/html5/thumbnails/14.jpg)
A.Penjira Kanthawongs 14
Block plus sign
Block Arrow
![Page 15: CS 202, ICS 202…..Class 7](https://reader035.vdocuments.us/reader035/viewer/2022081506/568148b2550346895db5c987/html5/thumbnails/15.jpg)
A.Penjira Kanthawongs 15
Standard Toolbar for Excel 97
AutoSum Paste Function
Standard Toolbar for Excel 2000
![Page 16: CS 202, ICS 202…..Class 7](https://reader035.vdocuments.us/reader035/viewer/2022081506/568148b2550346895db5c987/html5/thumbnails/16.jpg)
A.Penjira Kanthawongs 16
Mode indicator Status bar
Formula bar
AutoCalculate Area
![Page 17: CS 202, ICS 202…..Class 7](https://reader035.vdocuments.us/reader035/viewer/2022081506/568148b2550346895db5c987/html5/thumbnails/17.jpg)
A.Penjira Kanthawongs 17
Mouse pointer becomes an I-beam
![Page 18: CS 202, ICS 202…..Class 7](https://reader035.vdocuments.us/reader035/viewer/2022081506/568148b2550346895db5c987/html5/thumbnails/18.jpg)
A.Penjira Kanthawongs 18
What you need to know in this chapter?
How AutoCorrec t work in Page E118. ?In Excel, text is Left-aligned unless you change it. 1.20 .
Read in Orange box on Page E 1.20 about “Entering Data.”In Excel, numbers is Right-aligned unless you change it.
![Page 19: CS 202, ICS 202…..Class 7](https://reader035.vdocuments.us/reader035/viewer/2022081506/568148b2550346895db5c987/html5/thumbnails/19.jpg)
A.Penjira Kanthawongs 19
If you want Excel to treat numbers, such as ZIP codes, as text, you enter an apostrophe (‘) in front of the numbers.
On Page E 1.20 Numb ers can contain only …
0 1 2 3 4 5 6 7 8 9 + - () , / . $ % E e.. I have question about this part, see if anyone get this point?? (consider multiple sign) (5 points for person(s) who can explain.
![Page 20: CS 202, ICS 202…..Class 7](https://reader035.vdocuments.us/reader035/viewer/2022081506/568148b2550346895db5c987/html5/thumbnails/20.jpg)
A.Penjira Kanthawongs 20
To use AutoSum button.After you enter all data, click the cell you want the total to display, make sure the cell is below the number you want find the total, click AutoSum button.
![Page 21: CS 202, ICS 202…..Class 7](https://reader035.vdocuments.us/reader035/viewer/2022081506/568148b2550346895db5c987/html5/thumbnails/21.jpg)
A.Penjira Kanthawongs 21
Marquee, SUM function, Range
A moving border is called a marquee.SUM function in formula barSUM function display in active cellThe group of adjacent cells B3, B4, B5, and B6 is called a Range.
![Page 22: CS 202, ICS 202…..Class 7](https://reader035.vdocuments.us/reader035/viewer/2022081506/568148b2550346895db5c987/html5/thumbnails/22.jpg)
A.Penjira Kanthawongs 22
Use Fill Handle to copy cells 1. With cell B7 active, point to the fill handle. 2. Drag the fill handle to select the past area,
range C7:E7.3. Release the mouse button.
13
2
See-throughview
![Page 23: CS 202, ICS 202…..Class 7](https://reader035.vdocuments.us/reader035/viewer/2022081506/568148b2550346895db5c987/html5/thumbnails/23.jpg)
A.Penjira Kanthawongs 23
SUM functionSUM function will sum numbers on the top of the active cell first or sum numbers on the left of the active cell.
![Page 24: CS 202, ICS 202…..Class 7](https://reader035.vdocuments.us/reader035/viewer/2022081506/568148b2550346895db5c987/html5/thumbnails/24.jpg)
A.Penjira Kanthawongs 24
Relative reference and Show Formulas
Each adjusted cell reference is called a relative reference.For all homework, you need to Show Formulas … click Tool | Options | and check the box “Formulas” | OK
![Page 25: CS 202, ICS 202…..Class 7](https://reader035.vdocuments.us/reader035/viewer/2022081506/568148b2550346895db5c987/html5/thumbnails/25.jpg)
A.Penjira Kanthawongs 25
To Use AutoFormatSelect the range you want to have AutoFormat (exclude the title).Click Format | AutoFormat | Accounting 2
![Page 26: CS 202, ICS 202…..Class 7](https://reader035.vdocuments.us/reader035/viewer/2022081506/568148b2550346895db5c987/html5/thumbnails/26.jpg)
A.Penjira Kanthawongs 26
To Center a Cell’s Contents To Center a Cell’s Contents Across Columns (Page E 1.33)Across Columns (Page E 1.33)
Click cell A1. Drag the block plus sign to the rightmost cell (F1) of the range to center (A1:F1). Point to the Merge and Center button on the formatting toolbar.Click Merge and Center button.Click cell A9 to deselect cell A1.
![Page 27: CS 202, ICS 202…..Class 7](https://reader035.vdocuments.us/reader035/viewer/2022081506/568148b2550346895db5c987/html5/thumbnails/27.jpg)
A.Penjira Kanthawongs 27
Figure 1 - 48 (Page E 1.36)Embedded Chart = is a chart which is drawn on the same worksheet as the data.
![Page 28: CS 202, ICS 202…..Class 7](https://reader035.vdocuments.us/reader035/viewer/2022081506/568148b2550346895db5c987/html5/thumbnails/28.jpg)
A.Penjira Kanthawongs 28
To Add a 3-D Column Chart
Select the range of the chart which includes the column title(s), the row title(s), data of the column(s), and data of the row(s).
Click the Chart Wizard button.Choose the chart typeClick Finish
![Page 29: CS 202, ICS 202…..Class 7](https://reader035.vdocuments.us/reader035/viewer/2022081506/568148b2550346895db5c987/html5/thumbnails/29.jpg)
A.Penjira Kanthawongs 29
Sizing handles, ALT key
Sizing Handles
Sizing handles indicate the chart is selected.While holding down the ALT key, you can extend the width of the chart.
![Page 30: CS 202, ICS 202…..Class 7](https://reader035.vdocuments.us/reader035/viewer/2022081506/568148b2550346895db5c987/html5/thumbnails/30.jpg)
A.Penjira Kanthawongs 30
Chart Location
X-axis = category axis
Y-axis = value axis
Legend
![Page 31: CS 202, ICS 202…..Class 7](https://reader035.vdocuments.us/reader035/viewer/2022081506/568148b2550346895db5c987/html5/thumbnails/31.jpg)
A.Penjira Kanthawongs 31
To Save a workbook 1 1You save excel file = you save wor
2 222 2222 222 2222222222= 222 2222 2222222 , Option = let you set password (case sensitive = lower or upper cases are matter) and set backup
![Page 32: CS 202, ICS 202…..Class 7](https://reader035.vdocuments.us/reader035/viewer/2022081506/568148b2550346895db5c987/html5/thumbnails/32.jpg)
A.Penjira Kanthawongs 32
To use AutoCalculate Select the range.
- Right click the A utoCalculate are
a. Click type of calc
ulation you want to see the result.