Note: you must be online or connected to the internet to view the notes below
JUST CLICK THE LINKS BELOW. THESE LINKS WILL SERVE AS YOUR NOTES
Notes 1 Notes 2 Notes 3
Wednesday, February 27, 2013
Database and Spreedsheet Basics
Database
A database is any organized collection of data. Some examples of databases you may encounter in your daily life are:
-a telephone book
-T.V. Guide
-airline reservation system
-motor vehicle registration records
-papers in your filing cabinet
-files on your computer hard drive.
Data vs. information:
What is the difference?
What is data?
Data can be defined in many ways. Information science defines data as unprocessed information.
What is information?
Information is data that have been organized and communicated in a coherent and meaningful manner.
Data is converted into information, and information is converted into knowledge.
Knowledge; information evaluated and organized so that it can be used purposefully.
Why do we need a database?
Keep records of our:
-Clients
-Staff
-Volunteers
-To keep a record of activities and interventions
-Keep sales records;
-Develop reports;
-Perform research
-Longitudinal tracking
Types of Databases
Non-relational databases
Non-relational databases place information in field categories that we create so that information is available for sorting and disseminating the way we need it. The data in a non-relational database, however, is limited to that program and cannot be extracted and applied to a number of other software programs, or other database files within a school or administrative system. The data can only be "copied and pasted.“ Example: a spread sheet
Relational databases
In relational databases, fields can be used in a number of ways (and can be of variable length), provided that they are linked in tables. It is developed based on a database model that provides for logical connections among files (known as tables) by including identifying data from one table in another table
Spreadsheet Basics
Used to organize and analyze information
Made up of columns and rows
Columns and rows intersect to form cells
Column letter plus row number makes cell address
Spreadsheet Formulas
-A combination of values or cell references and mathematical operators
-Mathematical Operators: + - * /
-Formulas begin with an equal sign
-The actual formula displays in the entry bar
-The results of the formula display in the cell
Examples of formulas:
Add cells B4, C4, D4, and E4: =B4+C4+D4+E4
Subtract cell F10 from cell F9: =F9-F10
Multiply cells A3 and A4: =A3*A4
Divide cell D5 by cell D6 =D5/D6
Saturday, September 15, 2012
Excel Functions and Formulas
SUM function
The SUM function adds all the numbers that you specify as arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.). Each argument can be a range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.), a cell reference (cell reference: The set of coordinates that a cell occupies on a worksheet. For example, the reference of the cell that appears at the intersection of column B and row 3 is B3.), an array (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.), a constant (constant: A value that is not calculated. For example, the number 210 and the text "Quarterly Earnings" are constants. An expression, or a value resulting from an expression, is not a constant.), a formula (formula: A sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. A formula always begins with an equal sign (=).), or the result from another function. For example, SUM(A1:A5) adds all the numbers that are contained in cells A1 through A5. For another example, SUM(A1, A3, A5) adds the numbers that are contained in cells A1, A3, and A5.
The AVERAGE function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):
Syntax
=SUM(number1, [number2], [number3], [number4], ...)
The SUM function syntax has the following arguments:- number1 Required. The first item that you want to add.
- number2, number3, number4, ... Optional. The remaining items that you want to add, up to a total of 255 items.
AVERAGE function
Description
Returns the average (arithmetic mean) of the arguments. For example, if the range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) A1:A20 contains numbers, the formula =AVERAGE(A1:A20) returns the average of those numbers.Syntax
=AVERAGE(number1, [number2],...)
The AVERAGE function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):
- number1 Required. The first number, cell reference (cell reference: The set of coordinates that a cell occupies on a worksheet. For example, the reference of the cell that appears at the intersection of column B and row 3 is B3.), or range for which you want the average.
- number2, ... Optional. Additional numbers, cell references or ranges for which you want the average, up to a maximum of 255.
MAX function
Returns the largest value in a set of values.
Syntax
=MAX(number1,number2,...)
Number1, number2, ... are 1 to 255 numbers for which you want to find the maximum value.
|
Sunday, June 24, 2012
Spreadsheet Concepts
What is a Spreadsheet?
A spreadsheet (or spreadsheet program) is software that permits numerical data to be used and to perform automatic calculations on numbers contained in a table. It is also possible to automate complex calculations by using a large number of parameters and by creating tables called worksheets.
In addition, spreadsheets can also easily produce graphic representations of the data entered:
• histograms
• curves
• sector charts
• ...
Therefore, the spreadsheet is a multi-use tool that works as well for secretarial activities that involve organising large quantities of data, as at the strategic and decisional level by creating graphical representation of synthesised information.
The Main Spreadsheets
Numerous spreadsheets have been produced by the main software companies. The main spreadsheets are:
• Microsoft Excel, in the Microsoft Office office suite
• Sun StarOffice Calc, in the StarOffice suite
• OpenCalc, in the OpenOffice suite
• IBM/Lotus 1-2-3 in the SmartSuite suite
• Corel Quattro Pro in the WordPerfect suite
• KSpread in the KOffice free suite from Linux
Examples in the following articles are based on the Microsoft Excel spreadsheet, but the other spreadsheets contain the same functionalities.
The Concept of a Worksheet
Spreadsheets display data and formulas in a table form (lines and columns) called a worksheet.
A worksheet is made of lines (numbered with numbers) and of columns (numbered with letters). The intersection of a line and a column is called a cell. A cell is therefore represented by a number and a letter.
A worksheet can contain up to 65,536 lines and 256 columns, more that 17 million cells.
Each of the cells on a worksheet may contain values. These values are entered directly (numbers, text, date, etc.) or are automatically calculated by the spreadsheet. This is referred to as formulas, meaning expressions that calculate a value in function of one or more values in other cells on the worksheet.
Spreadsheets have a large number of integrated functions for math calculations, statistics, etc.
Here is an example of a worksheet that contains student's grades for which the spreadsheet automatically calculates the averages:
The Concept of a Cell
A "cell" is the intersection between a line (horizontal) and a column (vertical) on a worksheet. Thus, the name of the line combined with the name of the column gives the cell's coordinates (the term address is sometimes also used).
Coordinates (called reference styles) are generally grouped into two types, depending on the worksheet:
• The mode known as L1C1 (Line 1, Column 1), where the cell is located by the line number preceded by the letter L and the column number preceded by the letter C. L12C34 designates the cell at the intersection of the 12th line and the 34th column.
• The mode known as A1, where line numbers are designated by numbers and columns by letters. Thus AA17 designates the cell at the intersection of the 27th column and the 17th line.
Most spreadsheets have options to allow both modes to be used. With Excel and StarOffice, go into Tools > Options > General and click the box "L1C1 Reference Style".
Cell References
In order to work with data from cells when performing calculations, it is necessary to reference the cells. Many ways of referencing cells exist:
• absolute reference
• relative reference
• mixed reference
• named reference
Absolute References
An absolute reference represents the method of distinguishing a unique cell in a worksheet. Depending on the reference mode (L1C1 or A1), the absolute reference will
be written differently:
• In L1C1 mode: a cell's absolute reference is written by preceding the number by a letter L and the column number by the letter C.
LLineNumberCColumnNumber
For example, L12C24 represents the cell located at the intersection of line 12 and column 24.
• In A1 mode: a cell's absolute reference is written by preceding the line number and the column number by the $ sign.
$ColumnLetter$LineNumber
For example, $AC$34 represents the cell located at the intersection of the indicated column, AC, and line, 34.
Relative References
A cell's relative reference is the expression of its position relative to another cell. Thus, the relative reference gives the difference (in terms of the number of lines and columns) between the cell (called reference) and the target cell (called referenced cell).
By convention, upward difference along the vertical axis and difference towards the left along the horizontal axis are negative.
• In L1C1 mode: the relative difference of a cell is shown as cell coordinates in parenthesis:
L(LineNumber) C(ColumnNumber)
For example, L(3)C(-2) represents a cell located 3 lines lower and 2 columns left of the reference cell:
When there is no difference, a zero is not required in the parenthesis. Thus, L(0)C(12) may be written as LC(12).
• In A1 mode, the expression of the difference between the cells is hidden. In effect, a relative reference in A1 mode is implicit: just click on the coordinates of the target cell (referenced) without writing the $ sign:
Mixed References
A mixed reference is a reference where the horizontal position is expressed in an absolute manner and the vertical position in a relative manner, or vice-versa.
• In L1C1 notation, a mixed reference could look like L2C(3) or L(4)C17.
• In A1 notation, a mixed reference could look like $C5 or F$18.
Named References
A name may be given to a cell or to a group of cells.
To name a cell in Excel, select the cell or the range of cells to be named and go to Insert > Name > Define
When a cell or the range of cells has a name (the term label is sometimes used), it may be referenced by name. This functionality is especially useful when certain cells or cell ranges contain characteristic data because they may be referenced by name even if the cell or the group of cells has been moved.
For example, on an invoice, using a cell name such as total_bt for the cell that gives the total of an order before taxes is a good idea. You can also create a cell called VAT that contains the value of the VAT. Thus, when you need to calculate the total with taxes included, it is as easy as multiplying the cell called total_bt with the cell called VAT.
Comments
Commentary (sometimes called an annotation) may be added to a cell to provide additional information that we do not wish (or that we cannot) show on the worksheet.
To add a comment to a cell in Excel, select the cell or the range of cells to be named and then go to Insert > Comment or click on the cell or group of cells with the right mouse button and choose Insert a comment.
The small red triangle on the upper right of the cell D5 shows that the cell has a comment. This encourages the user to slide the mouse over the cell to read its contents.
Cell Content
A cell of a worksheet can contain a value or be empty. The value of a cell has two essential characteristics:
• a type, which means the intrinsic type of the data. There are generally three types of values:
o numeric values, for example 3.1415927, 58%, or 1984.
o alphanumeric values, for example Table2, Title or Name.
o formulas, which means expressions that represent calculations based on data and performed by the spreadsheet.
• a format that represents the way the spreadsheet displays the data. For example, if it is a number, the spreadsheet can be set to display it as an integer, as a decimal number (as well as the number of decimal places displayed), etc.
The cell may have a style, which means a particular visual appearance (font, size, color, border, etc.), independent of the value it contains.
Numeric Data Entry
When entering numbers in a cell (including monetary signs, percentages, etc.), the spreadsheet interprets the data as being numbers, which makes the use of mathematical formulas on the data possible. By default (without action on your part), the data recognized as being numbers will be right justified in the cell.
Alphanumeric Data Entry
When entering letters in a cell, the spreadsheet interprets the data as being alphanumeric, thus blocking the option of using mathematical tools on the data. By default (without action on your part), the data recognized as being alphanumeric will be left justified in the cell.
Entering a Formula
Formulas are expressions that, when they are interpreted by the spreadsheet, allow calculations to be performed using data present in other cells.
To enter a formula in a cell, select the cell and start entering the formula starting with the "equal" sign (=)!
If the syntax of the formula entered after the equal sign (=) is not correct, the spreadsheet will display an error message preceded with the # character! In case of an invalid cell reference, the spreadsheet will display the #REF! value.
A formula may contain references to other cells, expressions, which means operators that allow simple operations to be performed on the values of other cells, as well as functions.
The integrated functions of the spreadsheet allow elaborated calculations to be performed based on values in the spreadsheet, like averages, rounding, etc.
Defining the Format of a Cell
The format of a cell defines the way its value will be displayed by the spreadsheet. To define the format of a cell, click on the cell, go to the menu Format > Cell and choose the Number tab (or Numbers in StarOffice). The spreadsheet will offer a series of formats divided by category:
Excel StarOffice
http://en.kioskea.net/contents/tableur/tablintro.php3
A spreadsheet (or spreadsheet program) is software that permits numerical data to be used and to perform automatic calculations on numbers contained in a table. It is also possible to automate complex calculations by using a large number of parameters and by creating tables called worksheets.
In addition, spreadsheets can also easily produce graphic representations of the data entered:
• histograms
• curves
• sector charts
• ...
Therefore, the spreadsheet is a multi-use tool that works as well for secretarial activities that involve organising large quantities of data, as at the strategic and decisional level by creating graphical representation of synthesised information.
The Main Spreadsheets
Numerous spreadsheets have been produced by the main software companies. The main spreadsheets are:
• Microsoft Excel, in the Microsoft Office office suite
• Sun StarOffice Calc, in the StarOffice suite
• OpenCalc, in the OpenOffice suite
• IBM/Lotus 1-2-3 in the SmartSuite suite
• Corel Quattro Pro in the WordPerfect suite
• KSpread in the KOffice free suite from Linux
Examples in the following articles are based on the Microsoft Excel spreadsheet, but the other spreadsheets contain the same functionalities.
The Concept of a Worksheet
Spreadsheets display data and formulas in a table form (lines and columns) called a worksheet.
A worksheet is made of lines (numbered with numbers) and of columns (numbered with letters). The intersection of a line and a column is called a cell. A cell is therefore represented by a number and a letter.
A worksheet can contain up to 65,536 lines and 256 columns, more that 17 million cells.
Each of the cells on a worksheet may contain values. These values are entered directly (numbers, text, date, etc.) or are automatically calculated by the spreadsheet. This is referred to as formulas, meaning expressions that calculate a value in function of one or more values in other cells on the worksheet.
Spreadsheets have a large number of integrated functions for math calculations, statistics, etc.
Here is an example of a worksheet that contains student's grades for which the spreadsheet automatically calculates the averages:
The Concept of a Cell
A "cell" is the intersection between a line (horizontal) and a column (vertical) on a worksheet. Thus, the name of the line combined with the name of the column gives the cell's coordinates (the term address is sometimes also used).
Coordinates (called reference styles) are generally grouped into two types, depending on the worksheet:
• The mode known as L1C1 (Line 1, Column 1), where the cell is located by the line number preceded by the letter L and the column number preceded by the letter C. L12C34 designates the cell at the intersection of the 12th line and the 34th column.
• The mode known as A1, where line numbers are designated by numbers and columns by letters. Thus AA17 designates the cell at the intersection of the 27th column and the 17th line.
Most spreadsheets have options to allow both modes to be used. With Excel and StarOffice, go into Tools > Options > General and click the box "L1C1 Reference Style".
Cell References
In order to work with data from cells when performing calculations, it is necessary to reference the cells. Many ways of referencing cells exist:
• absolute reference
• relative reference
• mixed reference
• named reference
Absolute References
An absolute reference represents the method of distinguishing a unique cell in a worksheet. Depending on the reference mode (L1C1 or A1), the absolute reference will
be written differently:
• In L1C1 mode: a cell's absolute reference is written by preceding the number by a letter L and the column number by the letter C.
LLineNumberCColumnNumber
For example, L12C24 represents the cell located at the intersection of line 12 and column 24.
• In A1 mode: a cell's absolute reference is written by preceding the line number and the column number by the $ sign.
$ColumnLetter$LineNumber
For example, $AC$34 represents the cell located at the intersection of the indicated column, AC, and line, 34.
Relative References
A cell's relative reference is the expression of its position relative to another cell. Thus, the relative reference gives the difference (in terms of the number of lines and columns) between the cell (called reference) and the target cell (called referenced cell).
By convention, upward difference along the vertical axis and difference towards the left along the horizontal axis are negative.
• In L1C1 mode: the relative difference of a cell is shown as cell coordinates in parenthesis:
L(LineNumber) C(ColumnNumber)
For example, L(3)C(-2) represents a cell located 3 lines lower and 2 columns left of the reference cell:
When there is no difference, a zero is not required in the parenthesis. Thus, L(0)C(12) may be written as LC(12).
• In A1 mode, the expression of the difference between the cells is hidden. In effect, a relative reference in A1 mode is implicit: just click on the coordinates of the target cell (referenced) without writing the $ sign:
Mixed References
A mixed reference is a reference where the horizontal position is expressed in an absolute manner and the vertical position in a relative manner, or vice-versa.
• In L1C1 notation, a mixed reference could look like L2C(3) or L(4)C17.
• In A1 notation, a mixed reference could look like $C5 or F$18.
Named References
A name may be given to a cell or to a group of cells.
To name a cell in Excel, select the cell or the range of cells to be named and go to Insert > Name > Define
When a cell or the range of cells has a name (the term label is sometimes used), it may be referenced by name. This functionality is especially useful when certain cells or cell ranges contain characteristic data because they may be referenced by name even if the cell or the group of cells has been moved.
For example, on an invoice, using a cell name such as total_bt for the cell that gives the total of an order before taxes is a good idea. You can also create a cell called VAT that contains the value of the VAT. Thus, when you need to calculate the total with taxes included, it is as easy as multiplying the cell called total_bt with the cell called VAT.
Comments
Commentary (sometimes called an annotation) may be added to a cell to provide additional information that we do not wish (or that we cannot) show on the worksheet.
To add a comment to a cell in Excel, select the cell or the range of cells to be named and then go to Insert > Comment or click on the cell or group of cells with the right mouse button and choose Insert a comment.
The small red triangle on the upper right of the cell D5 shows that the cell has a comment. This encourages the user to slide the mouse over the cell to read its contents.
Cell Content
A cell of a worksheet can contain a value or be empty. The value of a cell has two essential characteristics:
• a type, which means the intrinsic type of the data. There are generally three types of values:
o numeric values, for example 3.1415927, 58%, or 1984.
o alphanumeric values, for example Table2, Title or Name.
o formulas, which means expressions that represent calculations based on data and performed by the spreadsheet.
• a format that represents the way the spreadsheet displays the data. For example, if it is a number, the spreadsheet can be set to display it as an integer, as a decimal number (as well as the number of decimal places displayed), etc.
The cell may have a style, which means a particular visual appearance (font, size, color, border, etc.), independent of the value it contains.
Numeric Data Entry
When entering numbers in a cell (including monetary signs, percentages, etc.), the spreadsheet interprets the data as being numbers, which makes the use of mathematical formulas on the data possible. By default (without action on your part), the data recognized as being numbers will be right justified in the cell.
Alphanumeric Data Entry
When entering letters in a cell, the spreadsheet interprets the data as being alphanumeric, thus blocking the option of using mathematical tools on the data. By default (without action on your part), the data recognized as being alphanumeric will be left justified in the cell.
Entering a Formula
Formulas are expressions that, when they are interpreted by the spreadsheet, allow calculations to be performed using data present in other cells.
To enter a formula in a cell, select the cell and start entering the formula starting with the "equal" sign (=)!
If the syntax of the formula entered after the equal sign (=) is not correct, the spreadsheet will display an error message preceded with the # character! In case of an invalid cell reference, the spreadsheet will display the #REF! value.
A formula may contain references to other cells, expressions, which means operators that allow simple operations to be performed on the values of other cells, as well as functions.
The integrated functions of the spreadsheet allow elaborated calculations to be performed based on values in the spreadsheet, like averages, rounding, etc.
Defining the Format of a Cell
The format of a cell defines the way its value will be displayed by the spreadsheet. To define the format of a cell, click on the cell, go to the menu Format > Cell and choose the Number tab (or Numbers in StarOffice). The spreadsheet will offer a series of formats divided by category:
Excel StarOffice
http://en.kioskea.net/contents/tableur/tablintro.php3
Subscribe to:
Posts (Atom)