Study Guides (248,044)
Canada (121,254)
Final

Computer Science Exam Review.docx

16 Pages
89 Views
Unlock Document

Department
Computer Science
Course
COMPSCI 1BA3
Professor
Anthony Hurst
Semester
Fall

Description
Computer Science Exam Review - Excel Session 1.1 – The Excel Window Terms: Workbook – Where Excel stores its spreadsheets in files Name Box – Displays the cell reference of the active cell Select All button – Used to select all of the cells in the active worksheet Row Headings – The numbers along the left side of the worksheet window that identify the different rows in the worksheet. You click a row heading to select the entire worksheet row. Sheet tab scrolling buttons – Scroll the list of sheet tabs in the worksheet Active Sheet – the sheet currently displayed in the workbook window and its tab is white Formula bar – displays the value or formula entered in the active cell Sheets – Excel is made up of individual sheets. Each sheet is identified by a name, which appears in the sheet tab Cell – The intersection of a row and a column Active Cell – The cell currently selected in the active worksheet is the active cell. The active cell is outlined with a thick border and the corresponding row and column headings are highlighted. Column Headings – The letters along the top of the worksheet window that identify the different columns in the worksheet. You click a letter to select the entire column. Worksheet – displays the contents of the spreadsheet laid out in a grid of rows and columns Spreadsheet – A collection of text and numbers laid out in a rectangular grid. Cell Reference – Indicates a cells column and row location Text Data – Any combination of letters, numbers, and symbols that form words and sentences. Number Data – Any numerical value that can be used in a mathematical calculation Truncated – when text is hidden because it is too large to fit in the cell to be displayed properly AutoComplete – When Excel tries to complete what you’re typing by displaying possible words/numbers from previously recorded entries Pixel – A single point on a computer monitor or printout. A column width of 8.43 characters equals 64 pixels. Point – Is approximately 1/72 of an inch. The default row height is 15 points or 20 pixels. Autofitting – Eliminates empty space by matching the row height or column width to its tallest or longest cell entry. Clearing – Clearing data from a worksheet removes the data, leaving blank cells where the data had been. Deleting – Deleting data from the worksheet removes both the data and the cells. Content: Navigation Between Worksheets - A workbook can have two kids of sheets: o Worksheets – contains a grid of rows and columns into which the user enters data values and formulas o Chart sheets – contains Excel chart that provides visual representation of worksheet data. o Charts can also be embedded within worksheets, displaying both the data and charts in one sheet Navigating Within a Worksheet - Each cell within a worksheet is identified by a cell reference Entering Text, Numbers, and Dates - Text data is often referred to a text string, because it contains a string of text characters. - Date and time data are commonly recognized format for date and time values. - By default, text is left-aligned in cells and numbers, dates, and times are right- aligned - If the adjacent cells contain text or data, only the text that fits into the cell is displayed. The rest of the text entry is hidden, or truncated, from the view. - Excel tries to anticipate the remaining characters by displaying text that begins with the same letters as a previous entry in the same column. This feature, known as AutoComplete, helps make entering repetitive text easier. To accept the suggested text, press the Tab or Enter key. To override the suggested text, continue to type the text you want to enter in the cell. International Date Formats - Many international businesses adopt ISO (International Organization for Standardization) dates in the format yyyy-mm-dd Deleting and Clearing a Row or Column - When a cell is deleted, the remaining cells in the worksheet shift into the deleted location. - Note: pressing the Delete key does not DELETE the selected row or column; instead, it CLEARS the contents, leaving the row or column in place Session 1.2 – Worksheet Data Terms: Spelling checker – verifies the words in the active worksheet against the program’s dictionary Cell Range/Range – a group of cells Formula – an expression that returns a value Nonadjacent range – is two or more distinct adjacent ranges Adjacent range – is a group of cells in a single rectangular block of cells View buttons – change how the worksheet content is displayed Sum button – (also referred to as the AutoSum feature) inserts Excel functions to sum, average, or count all the values in a column or row, as well as display the minimum or maximum value in a column or row. Range reference – Indicates the location and size of a cell range Function – A named operation that returns a value. Functions are used to simplify formulas, reducing what might be a long formula into a compact statement. Normal View – Shows the contents of the worksheet Page Layout View – Shows how the worksheet will appear when printed Page Break Preview – Displays the location of the different breaks within the worksheet Portrait Orientation – The page is TALLER than it is WIDER Landscape Orientation – The page is WIDER than it is TALLER Formula View – a view of the worksheet contents that displays formulas instead of the resulting values Scaling – A printout reduces the width and the height of the printout to fit the number of pages you specify by shrinking the text size as needed. Content: Moving and Copying Range - One way to move a cell or range is to select it, position the pointer over the bottom border of the selection, and then drag the selection to a new location. This technique is called drag and drop because you are dragging the range and dropping it in a new location. - If too complex, its often more efficient to cut and past the cell contents. Cutting places the cell contents into computer memory or on the Clipboard. The contents can then be pasted from the Clipboard into a new location in the worksheet. Entering a Formula - Every Excel formula begins with an equal sign (=) followed by an expression describing the operation that returns the value. - Note: if you dot begin the formula with the equal sign, Excel assumes that you are entering text and will not treat the cell contents as a formula. - A formula is written using operators that combine different values, resulting in a single value that is then displayed within the cell. - The most commonly used operators are arithmetic operators that perform addition, subtraction, multiplication, division and exponentiation. - The order of precedence is a set of predefined rules used to determine the sequence in which operators are applies in a calculation. Remember, BEDMAS? Copying and Pasting Formulas - With formulas, Excel adjusts the formula’s cell references to reflect the new location of the formula in the worksheet. Entering Functions with the AutoSum Feature - The AutoSum feature includes buttons to quickly insert the SUM, AVERAGE, COUNT, MIN and MAX functions to generate the following: o Sum of the values in the column or row o Average value in column or row o Total count of numeric values in the column or row o Min value in the column or row o Max value in the column or row Editing Worksheet Content - Sometimes you need to edit only a portion of an entry rather than change the entire content of a cell, especially if the cell contains a large block of text or a complex formula. To edit the cell contents, you can work in Edit mode. Using Find and Replace - You can use the Find command to locate a string of characters known as a search string and replace the text with a replacement string of new text characters. Session 5.1 – An Excel Table Terms: Field – each column represents a field, which describes some characteristics of a person, place or thing such as a last name, address, city or state Header Row – The first row of the range that contains field names Record – each row represents a record, which is a collection of related fields that are grouped together Sort – rearranging the records in a table or range based on one or more fields Field name/Column Header – is a unique label that describes the contents of the data in a column Excel Table – is a range of related data that is managed independently from data in other rows and columns in the worksheet Sort Field – the fields that you use to order data Structured Range of Data – commonly referred to as a list or table Data Definition Table – documentation that lists the fields to be maintained for each record (in this case, each art object) and a description of the information each field will include. Freezing – lets you keep headings visible as you work with the data in a large worksheet. As you scroll the data, the first row remains on the screen so the column headers are visible, making it easier to identify the data in each record. Ascending Order – Arranges text alphabetically from A to Z, numbers from smallest to largest, and dates from oldest to newest. BLANK FIELDS ARE PLACED AT THE END OF THE TABLE. Descending Order – Arranges text alphabetically from Z to A, numbers from largest to smallest and dates from newest to oldest. BLANK FIELDS ARE PLACED AT THE END OF THE TABLE. Primary Sort Field – the first sort field Secondary Sort Field – the second sort field… And so on Custom List – indicates the sequence in which you want data ordered. Excel provides four predefined custom sort lists. Content: Renaming an Excel Table - Table names must start with a letter or an underscore and can use any combination of letters, numbers and underscores for the rest of the name. Table names CANNOT include spaces. Session 5.2 – Filtering Table Data Terms: Total Row – Used to calculate summary statistics (including sum. Average, count, maximum, and minimum) for any column in an Excel table Split bar – Indicates the division between the panes. You can drag the split bar to resize the panes Flitering – The process of displaying a subset of rows in an Excel table that meets the criteria you specify Pane – Each area of a worksheet Criteria filters – Enable you to specify various conditions in addition to those that are based on an “equal” criterion Content: Clearing Filters - When you want to redisplay all of the data in a filtered table, you need to clear (or remove) the filters. When you clear a filter from a column, any other filters are still applied. Inserting Subtotals - The Subtotal command offers many kinds of summary information, including counts, sums, averages, minimum and maximums. The Subtotal command inserts a subtotal row into the range for each group of data and adds a grand total row below the last row of data. - The Subtotal command cannot be used in an Excel table, so you must first convert the Excel table to a range. Using the Subtotal Outline View - The Subtotal feature “outlines” your worksheet so you can control the level of details that is displayed. The three Outline buttons at the top of detail in the worksheet. By default, the highest level is active; in this case, Level 3. - Level 3 displays the most detail – the individual art object records, the subtotals and the grand total. - Level 2 displays subtotals and the grand total, but not the individual records. - Level 1 displays only the grand total Session 5.3 – PivotTable and PivotChart Terms: PivotTable – An interactive table used to group and summarize either a range of data or an Excel table into concise, tabular format for easier reporting and analysis Values field – The fields that contain summary data in a PivotTable, this PivotTable uses the total of the Appraised Value as the values field. PivotChart – A graphical representation of the data in a PivotTable Slicer – An object used to filter a PivotTable or PivotChart. You can create more than one slicer for a PivotTable or PivotChart Category Fields – are the fields that group the values in a PivotTable. Category fields appear in PivotTables as row labels, column labels and report filters Content: Analyzing Data with PivotTables - A PivotTable summarizes fata into categories using functions such as COUNT, SUM, AVERAGE, MAX and MIN - In PivotTable terminology, the fields that contain values in a PivotTable, such as condition, location, and year acquired. Category fields appear in PivotTables as row labels, column labels, and report filters, which allows you to focus on a subset of the PivotTable by displaying one, several or all items. Summarizing Data with a PivotTable - The PivotTable omits all the detailed data, enabling readers to focus on the bugger picture. This makes it easier for readers to understand the results and gain insights about the topic. Creating a PivotTable - When you create a PivotTable you need to specify where to find the data for the PivotTable - The PivotTable Field List is divided into two sections. The upper field list section displays the names of each field in the [ArtObjects] table. You check a field check box to add that field to the PivotTable. - The lower layout section includes boxes for the four areas in which you can place fields: Report Filter, Row Labels, Column Labels, and Values. Figure 5-32; Layout areas for a PivotTable - Row labels – The fields you want to display as the rows in the PivotTable. One row in display for each unique item in this area. You can have nested row fields. - Column Labels – The fields you want to display as columns at the top of the PivotTable. One column is displayed for each unique item in this area. You can have nested column fields. - Report Filter – A field used to filter the report by selecting one or more items, enabling you to display a subset of data in a PivotTable report. - Values – The fields you want to summarize Changing the PivotTable Report Layout Options - The Compact report layout places all fields from the row area in a single column and indents the items from each field below the other fields. This is the default layout; each field in the row area takes a column in the PivotTable. By default, the outline form shows the subtotals for each group at the top of every group. The Tabular report layout displays one column for each field and leaves space for column headers. A total for each group appears at the bottom of each group. You can find these report layout options on the PivotTable Tools Design tab in the Layout group. Adding a Report Filter to a PivotTable - A report filter is used to filter the PivotTable to display summarized data for one or more field items or all field items in the Report Filter area. Sorting PivotTable Fields - You can sort a PivotTable field either by its own items or by the values in the body of the PivotTable. To sort a PivotTable field, you can use any of the Sort buttons on the Options tab to sort the Information in a PivotTable report. Creating Different Types of PivotTable Reports - Most PivotTable summaries are based on numeric data, but PivotTables can also contain only nonnumeric data. You cannot add nonnumeric data, so you must use the COUNT function to produce summaries. - You can use PivotTables to combine items into groups. Items that appear as row labels or column labels can be grouped. If items are numbers or dates, they can be grouped automatically using the Grouping dialog box, or they can be grouped manually using the Ctrl key to select items in a group
More Less

Related notes for COMPSCI 1BA3

Log In


OR

Join OneClass

Access over 10 million pages of study
documents for 1.3 million courses.

Sign up

Join to view


OR

By registering, I agree to the Terms and Privacy Policies
Already have an account?
Just a few more details

So we can recommend you notes for your school.

Reset Password

Please enter below the email address you registered with and we will send you a link to reset your password.

Add your courses

Get notes from the top students in your class.


Submit