JUST CLICK THE LINKS BELOW. THESE LINKS WILL SERVE AS YOUR NOTES
Notes 1 Notes 2 Notes 3 notes 4 Notes 5
Wednesday, January 12, 2011
Sunday, July 11, 2010
Identify Basic Parts of the OpenOffice.org Calc Window
The Basics of the OpenOffice.org Window
Many items you see on the Calc screen are standard features in most other OpenOffice.org software programs like OpenOffice.org Writer (text document), OpenOffice.org Impress (presentation), and OpenOffice.org Draw (drawing). Some elements are specific to Calc.
Let's briefly review the basic components of the OpenOffice.org window before we move onto the Calc spreadsheet.
Shown below is the OpenOffice.org default window. When you launch Calc, a new, blank document, or default window, opens in Print Layout view. Here is a brief explanation of the Calc window.
The Menu bar displays all the menus that are available in Calc. The contents of any menu can be displayed by clicking on the menu name with the left mouse button.
The Toolbar contains commands that have pictures or icons associated with them. These pictures may also appear as shortcuts in the Toolbar.
Each OpenOffice.org Calc spreadsheet contains 256 columns. Each column is named by a letter or combination of letters.
Each spreadsheet contains 65,536 rows. Each row is named by a number.
The Name Box shows the address of the current selection or active cell.
The Formula Bar contains information that you have entered or that you are currently entering as you type in the current or active cell. The contents of a cell can also be edited in the Formula bar.
A cell is an intersection of a column and row. Each cell has a unique cell address. In the picture above, the cell address of the selected cell is B3. The heavy border around the selected cell is called the cell pointer.
Navigation buttons allow you to move from one worksheet to another in an OpenOffice.org Calc workbook. The navigation buttons display the first, previous, next, or last worksheets in the workbook.
Sheet tabs separate a workbook into specific worksheets. A workbook defaults to three worksheets. A workbook must contain at least one worksheet.
Column headings are referenced by alphabetic characters in the gray boxes that run across the screen, beginning with the Column A and ending with Column IV.
Rows are referenced by numbers that appear on the left and then run down the screen. The first row is named Row 1 and the last row is named 65,536.
In the following picture, cell C3, which is formed by the intersection of column C and row 3, contains the dark border. It is the active cell.
To move up and down the spreadsheet, use the vertical scroll bar located along the right edge of the screen. To move left or right, use the horizontal scroll bar, located at the bottom of the screen.
Use the PageUp and PageDown keys on the keyboard to move the cursor up or down one screen at a time. You can also use the Home key to move the active cell to the first column on the current row, and Ctrl+Home to move the cursor to the top left corner of the spreadsheet or cell A1.
Pull-down menus include the following components:
Many items you see on the Calc screen are standard features in most other OpenOffice.org software programs like OpenOffice.org Writer (text document), OpenOffice.org Impress (presentation), and OpenOffice.org Draw (drawing). Some elements are specific to Calc.
Let's briefly review the basic components of the OpenOffice.org window before we move onto the Calc spreadsheet.
Shown below is the OpenOffice.org default window. When you launch Calc, a new, blank document, or default window, opens in Print Layout view. Here is a brief explanation of the Calc window.
Workbook
Also called a spreadsheet, the Workbook is a unique file created by Calc.
Menu Bar
The Menu bar displays all the menus that are available in Calc. The contents of any menu can be displayed by clicking on the menu name with the left mouse button.
Toolbar
The Toolbar contains commands that have pictures or icons associated with them. These pictures may also appear as shortcuts in the Toolbar.
Column Headings
Each OpenOffice.org Calc spreadsheet contains 256 columns. Each column is named by a letter or combination of letters.
Row Headings
Each spreadsheet contains 65,536 rows. Each row is named by a number.
Name Box
The Name Box shows the address of the current selection or active cell.
Formula Bar
The Formula Bar contains information that you have entered or that you are currently entering as you type in the current or active cell. The contents of a cell can also be edited in the Formula bar.
Cell
A cell is an intersection of a column and row. Each cell has a unique cell address. In the picture above, the cell address of the selected cell is B3. The heavy border around the selected cell is called the cell pointer.
Navigation Buttons and Sheet Tabs
Navigation buttons allow you to move from one worksheet to another in an OpenOffice.org Calc workbook. The navigation buttons display the first, previous, next, or last worksheets in the workbook.
Sheet tabs separate a workbook into specific worksheets. A workbook defaults to three worksheets. A workbook must contain at least one worksheet.
Workbooks and Worksheets
A workbook automatically shows in the workspace when you open a Calc spreadsheet. Each workbook contains three worksheets. A worksheet is a grid of cells, consisting of 32,000 rows by 230 columns. You can enter a variety of information, including text, numbers, or mathematical formulas into these different cells.
Column headings are referenced by alphabetic characters in the gray boxes that run across the screen, beginning with the Column A and ending with Column IV.
Rows are referenced by numbers that appear on the left and then run down the screen. The first row is named Row 1 and the last row is named 65,536.
Important Terms
- A workbook is made up of three worksheets.
- Worksheets are labeled Sheet1, Sheet2, and Sheet3.
- Each worksheet is made up of columns and rows.
- In order to access a worksheet, click on the tab that says Sheet1, Sheet2, or Sheet3.
The Cell
A Calc worksheet is made up of columns and rows. The area where these columns and rows intersect are called cells. Each cell has a name that is comprised of two parts: the column letter and the row number. The active cell, or the area that is currently selected for either data entry or editting, is outlined by a dark border. All of the other cells have a light gray border.
In the following picture, cell C3, which is formed by the intersection of column C and row 3, contains the dark border. It is the active cell.
Important Terms
- Each cell has a unique cell address composed of a cell's column and row.
- The active cell is the cell that you select to enter data or other commands you give it.
- A darkened border, called the cell pointer, identifies the active cell.
Working Calc in Full-Screen View
To help you stay organized as you work, Calc offers a Full Screen view to display only the document that you are working on. All of the other pieces of the Calc window are removed except for one button that allows you to turn the Full-Screen view Off.To display a full-screen view:
- Choose View Full Screen on the menu bar.
- All toolbars, except the main toolbar, are removed from the screen and replaced by only row headings, column headings, and cells.
Displaying Calc Toolbars
Calc allows you to turn on and off any of the five different toolbars: Function Bar, Object Bar, Main Toolbar, Hyperlink Bar, and the Formula Bar.To change the toolbars view:
- Choose View Toolbars on the menu bar to display a complete list of Calc toolbars. The checkmarks indicate the toolbars that you are currently using in the view.
- Choose Function Bar to turn the function bar on or off.
- Choose Object Bar to turn the object bar on or off.
- Choose Main Toolbar to turn the main toolbar on or off.
- Choose Hyperlink Bar to turn the hyperlink bar on or off.
- Choose Formula Bar to turn the formula bar on or off.
Moving around the worksheet
You can move around the spreadsheet in several different ways.To move the cell pointer:
- To activate any cell, point to a cell with the mouse and click.
- To move the pointer one cell to the left, right, up, or down, use the keyboard arrow keys.
To scroll through the worksheet:
To move up and down the spreadsheet, use the vertical scroll bar located along the right edge of the screen. To move left or right, use the horizontal scroll bar, located at the bottom of the screen.
Use the PageUp and PageDown keys on the keyboard to move the cursor up or down one screen at a time. You can also use the Home key to move the active cell to the first column on the current row, and Ctrl+Home to move the cursor to the top left corner of the spreadsheet or cell A1.
To move between worksheets:
As mentioned, each workbook defaults to three worksheets. These worksheets are represented by tabs -- named Sheet1, Sheet2 and Sheet3 -- that appear at the bottom of the spreadsheet window.- Click on the sheet tab (Sheet1, Sheet2 or Sheet 3) that you want to display
Using the Pull-Down Menus
Each OpenOffice.org program features a menu bar. The menu bar is made up of many different menus. Each menu contains commands that enable you to work within the program. Calc uses pull-down menus to display commands that users often need.Pull-down menus include the following components:
- File
- Edit
- View
- Insert
- Format
- Tools
- Window
- Help
To open a menu:
- Click on a menu name on the menu bar to display the commands that pertain to the menu that you've selected.
- View the commands listed under the pull-down menu.
- With the menu open, drag the mouse pointer to a command and click on it to select the command. (As you drag your mouse pointer over the commands, you will highlight each command in light blue.)
- If there is a small, black triangle next to a command, hover the mouse pointer over the command with the triangle and a cascading menu with additional options will appear. Point and click to make a selection from the cascading menu.
Wednesday, June 23, 2010
Database
Database
A database consists of an organized collection of data for one or more multiple uses. One way of classifying databases involves the type of content, for example: bibliographic, full-text, numeric, image. Other classification methods start from examining database models or database architectures: see below. Software organizes the data in a database according to a database model. As of 2010 the relational model occurs most commonly. Other models such as the hierarchical model and the network model use a more explicit representation of relationships.
In computing, databases are sometimes classified according to their organizational approach. The most prevalent approach is the relational database, a tabular database in which data is defined so that it can be reorganized and accessed in a number of different ways. A distributed database is one that can be dispersed or replicated among different points in a network. An object-oriented programming database is one that is congruent with the data defined in object classes and subclasses.
Computer databases typically contain aggregations of data records or files, such as sales transactions, product catalogs and inventories, and customer profiles.
Typically, a database manager provides users the capabilities of controlling read/write access, specifying report generation, and analyzing usage. Databases and database managers are prevalent in large mainframe systems, but are also present in smaller distributed workstation and mid-range systems such as the AS/400 and on personal computers. SQL (Structured Query Language) is a standard language for making interactive queries from and updating a database such as IBM's DB2, Microsoft's Access, and database products from Oracle, Sybase, and Computer Associates.
-----------------------------------------
Uses for a Database
Databases for Businesses
The business world depends on databases 24 hours a day/seven days a week. Inventory, order processing, payroll, accounting, shipping and transportation routing are often tracked within a main database that keeps the company functioning.
Databases for Educational Institutions
From elementary schools to colleges, educational institutions have used databases to keep track of students, grades, transfers, transcripts and other student data. There are even specialized database packages geared toward schools and colleges.
Databases for Non-Profit Organizations
Like businesses and educational institutions, non-profit organizations must have a system to keep track of information. Many charities and other non-profit groups use a database for keeping track of donations, volunteers, hours served in the community, clients helped and other information related to the organization.
Databases for Household and Family Management
The database also has a home in household and family management for many individuals and families. Many individuals/families use a database to keep track of family birthdays, bills and expenses within a home; addresses of friends and relatives; movie/DVD collections; and other lists.
Databases In Use Everyday
Each time you make a purchase and the sales clerk asks for a your address or ZIP code, your information is kept and stored in a customer database. These collections of data are used to send mailings of special offers, discounts and other deals.
-----------------------------------------
Why Use a Database?
So, what possible use could you have for a database on your website? After all, you are just creating a site for yourself, your friends and your family.
As I mentioned earlier, there are a myriad of uses for databases. I'm sure it is easy to imagine all of the uses for a database on a commercial web site like an online store. You could store visitor information for logging in, purchase information, order logs, bookkeeping information, company reports, pricing structures, account information and the list goes on.
But what about personal stuff? Well, imagine this. You started a site for you and your family. You have a few pages for your family, your parents have a few pages and you just added some pages about your friends. Now your site just seems to keep growing and you don't really have the time to keep up with your own stuff let alone all of the things your family and friends keep sending you.
For example, you started a page so that you and your friends can borrow each others DVDs. Your page keeps track of who has what DVD and who is the original owner of the DVD. Each time a DVD changes hands someone sends you an email and you have to change the information on the website.
Another example. Your parents have 7 grandchildren. They love to keep up with their grandchildren's' activities. So, they are constantly sending you each grandchild's schedule of events to post. You get sports schedules, school activities and recitals just to name a few.
You also created a community page for your visitors. On it you have the names, email addresses and telephone numbers of your family and friends. The trouble is your friends and family seem to keep adding their friends and family and you keep getting all of the emails asking you to add more and more people.
Getting tired just thinking about it? Can you see how a database could be your friend?
If you could create a database driven DVD checkout system, calendar of events and community contacts you could save yourself a ton of time by letting your users do the updating instead of you through some simple web forms that you create and then use your database to update your pages automatically.
back to top
-----------------------------------------
What Types of Databases are There?
You wouldn't believe.
DBMSs (Data Base Management Systems) come in many different shapes, sizes and flavors, however, there are two basic categories: Enterprise and Personal.
(In case you are confused, DBMS refers to the software that handles moving the data in, out and around your database while the term database refers to the actual body of data that you are storing.)
Enterprise DBMSs are designed primarily for use with corporations, governments and any other function that would require the database to handle large amounts of data coming in and out. Information Technology folks refer to this as scalability which simply means how well the database handles multiple simultaneous transactions (the handling of data). For example, a high scalability would mean that the database can handle everything from a few transactions an hour to thousands of transactions or more happening at the same time.
Some examples of Enterprise DBMSs that you might have heard of would be Microsoft's SQL (pronounced sequel) Server, Oracle or IBM's DB2.
Personal DBMSs are generally not highly scalable. They are intended to be used as learning tools or in low demand situations like you may run into while working on your personal projects. Depending on the DBMS, you might even find good uses for a personal DBMS in small business. Many Enterprise DBMSs also have personal versions like Oracle that was mentioned above. Other companies
produce separate products like Microsoft's Access which are very similar to their Enterprise product.
-----------------------------------------
Since then, spreadsheets have become much more sophisticated tools. But they still fall far short of databases. Here's why a database is still a better tool for business:
• Easier to share. You can have two or more people editing a database at the same time. But spreadsheet users have to wait until nobody is using a file before it's free for them to use.
• Better security. Along with the ability to better share information is the ability to better secure it. You can protect private information from wandering mice better with a database. You can also protect users from their own mistakes, like adding up the wrong column or forgetting to include sales tax in the price quote.
• More efficient. Databases are relational, allowing you to link related tables to minimize duplication. Here's an example. Say you have a mailing list that includes 20 real estate agents at the same office. All of them have the same company name, address, phone number and fax. In a spreadsheet, you would have to duplicate that information 20 times for each agent. In a relational database manager, you enter that information once, then link or "relate" it to each of the 20 agents.
• Better reporting. With a database, you can format the same data many ways in reports. Here's an example. For monthly sales reports, you want to look at sales by person, by region and by product. In a spreadsheet, you basically have to enter or copy that information three times in three spreadsheets to get the desired reports. In a database, you enter it once and then use the reporting features to compile the data in the three formats with a click of the button.
• Greater capacity. Databases also have capacity to hold much greater numbers of records (into the billions).
• Easier to maintain. What makes spreadsheets so simple to use -- the fact that the programming and the formatting are together on one page with the data -- also makes them hard to maintain. Every place I go where people use spreadsheets for database activities, such as list management and process tracking, I spend time fixing spreadsheets because someone messed up the formatting while changing the data. You don't have that problem with databases.
• Less duplication. There are two kinds of duplication with spreadsheets.
First, there is file duplication. Because you can't share the price-quote spreadsheet, everyone makes a personal copy. Second is duplication of source data. Nine of 10 spreadsheets duplicate information already housed in a database, such as an accounting program.
In short, databases require a greater investment in training. But the return is greater than any spreadsheet.
So when should you use spreadsheets? I like them for two circumstances:
• When compiling information from diverse sources. For example, you want to compare your company's financial ratios with those of industry standards supplied by your accountant.
• When you are doing a one-time analysis. There isn't a faster tool for building a simple snapshot that you might not use again.
Of course, you're free to use spreadsheets however you want. But if you want to debate the merits of spreadsheets vs. databases, I'll see you on Springer.
A database consists of an organized collection of data for one or more multiple uses. One way of classifying databases involves the type of content, for example: bibliographic, full-text, numeric, image. Other classification methods start from examining database models or database architectures: see below. Software organizes the data in a database according to a database model. As of 2010 the relational model occurs most commonly. Other models such as the hierarchical model and the network model use a more explicit representation of relationships.
In computing, databases are sometimes classified according to their organizational approach. The most prevalent approach is the relational database, a tabular database in which data is defined so that it can be reorganized and accessed in a number of different ways. A distributed database is one that can be dispersed or replicated among different points in a network. An object-oriented programming database is one that is congruent with the data defined in object classes and subclasses.
Computer databases typically contain aggregations of data records or files, such as sales transactions, product catalogs and inventories, and customer profiles.
Typically, a database manager provides users the capabilities of controlling read/write access, specifying report generation, and analyzing usage. Databases and database managers are prevalent in large mainframe systems, but are also present in smaller distributed workstation and mid-range systems such as the AS/400 and on personal computers. SQL (Structured Query Language) is a standard language for making interactive queries from and updating a database such as IBM's DB2, Microsoft's Access, and database products from Oracle, Sybase, and Computer Associates.
-----------------------------------------
Uses for a Database
Databases for Businesses
The business world depends on databases 24 hours a day/seven days a week. Inventory, order processing, payroll, accounting, shipping and transportation routing are often tracked within a main database that keeps the company functioning.
Databases for Educational Institutions
From elementary schools to colleges, educational institutions have used databases to keep track of students, grades, transfers, transcripts and other student data. There are even specialized database packages geared toward schools and colleges.
Databases for Non-Profit Organizations
Like businesses and educational institutions, non-profit organizations must have a system to keep track of information. Many charities and other non-profit groups use a database for keeping track of donations, volunteers, hours served in the community, clients helped and other information related to the organization.
Databases for Household and Family Management
The database also has a home in household and family management for many individuals and families. Many individuals/families use a database to keep track of family birthdays, bills and expenses within a home; addresses of friends and relatives; movie/DVD collections; and other lists.
Databases In Use Everyday
Each time you make a purchase and the sales clerk asks for a your address or ZIP code, your information is kept and stored in a customer database. These collections of data are used to send mailings of special offers, discounts and other deals.
-----------------------------------------
Why Use a Database?
So, what possible use could you have for a database on your website? After all, you are just creating a site for yourself, your friends and your family.
As I mentioned earlier, there are a myriad of uses for databases. I'm sure it is easy to imagine all of the uses for a database on a commercial web site like an online store. You could store visitor information for logging in, purchase information, order logs, bookkeeping information, company reports, pricing structures, account information and the list goes on.
But what about personal stuff? Well, imagine this. You started a site for you and your family. You have a few pages for your family, your parents have a few pages and you just added some pages about your friends. Now your site just seems to keep growing and you don't really have the time to keep up with your own stuff let alone all of the things your family and friends keep sending you.
For example, you started a page so that you and your friends can borrow each others DVDs. Your page keeps track of who has what DVD and who is the original owner of the DVD. Each time a DVD changes hands someone sends you an email and you have to change the information on the website.
Another example. Your parents have 7 grandchildren. They love to keep up with their grandchildren's' activities. So, they are constantly sending you each grandchild's schedule of events to post. You get sports schedules, school activities and recitals just to name a few.
You also created a community page for your visitors. On it you have the names, email addresses and telephone numbers of your family and friends. The trouble is your friends and family seem to keep adding their friends and family and you keep getting all of the emails asking you to add more and more people.
Getting tired just thinking about it? Can you see how a database could be your friend?
If you could create a database driven DVD checkout system, calendar of events and community contacts you could save yourself a ton of time by letting your users do the updating instead of you through some simple web forms that you create and then use your database to update your pages automatically.
back to top
-----------------------------------------
What Types of Databases are There?
You wouldn't believe.
DBMSs (Data Base Management Systems) come in many different shapes, sizes and flavors, however, there are two basic categories: Enterprise and Personal.
(In case you are confused, DBMS refers to the software that handles moving the data in, out and around your database while the term database refers to the actual body of data that you are storing.)
Enterprise DBMSs are designed primarily for use with corporations, governments and any other function that would require the database to handle large amounts of data coming in and out. Information Technology folks refer to this as scalability which simply means how well the database handles multiple simultaneous transactions (the handling of data). For example, a high scalability would mean that the database can handle everything from a few transactions an hour to thousands of transactions or more happening at the same time.
Some examples of Enterprise DBMSs that you might have heard of would be Microsoft's SQL (pronounced sequel) Server, Oracle or IBM's DB2.
Personal DBMSs are generally not highly scalable. They are intended to be used as learning tools or in low demand situations like you may run into while working on your personal projects. Depending on the DBMS, you might even find good uses for a personal DBMS in small business. Many Enterprise DBMSs also have personal versions like Oracle that was mentioned above. Other companies
produce separate products like Microsoft's Access which are very similar to their Enterprise product.
-----------------------------------------
Since then, spreadsheets have become much more sophisticated tools. But they still fall far short of databases. Here's why a database is still a better tool for business:
• Easier to share. You can have two or more people editing a database at the same time. But spreadsheet users have to wait until nobody is using a file before it's free for them to use.
• Better security. Along with the ability to better share information is the ability to better secure it. You can protect private information from wandering mice better with a database. You can also protect users from their own mistakes, like adding up the wrong column or forgetting to include sales tax in the price quote.
• More efficient. Databases are relational, allowing you to link related tables to minimize duplication. Here's an example. Say you have a mailing list that includes 20 real estate agents at the same office. All of them have the same company name, address, phone number and fax. In a spreadsheet, you would have to duplicate that information 20 times for each agent. In a relational database manager, you enter that information once, then link or "relate" it to each of the 20 agents.
• Better reporting. With a database, you can format the same data many ways in reports. Here's an example. For monthly sales reports, you want to look at sales by person, by region and by product. In a spreadsheet, you basically have to enter or copy that information three times in three spreadsheets to get the desired reports. In a database, you enter it once and then use the reporting features to compile the data in the three formats with a click of the button.
• Greater capacity. Databases also have capacity to hold much greater numbers of records (into the billions).
• Easier to maintain. What makes spreadsheets so simple to use -- the fact that the programming and the formatting are together on one page with the data -- also makes them hard to maintain. Every place I go where people use spreadsheets for database activities, such as list management and process tracking, I spend time fixing spreadsheets because someone messed up the formatting while changing the data. You don't have that problem with databases.
• Less duplication. There are two kinds of duplication with spreadsheets.
First, there is file duplication. Because you can't share the price-quote spreadsheet, everyone makes a personal copy. Second is duplication of source data. Nine of 10 spreadsheets duplicate information already housed in a database, such as an accounting program.
In short, databases require a greater investment in training. But the return is greater than any spreadsheet.
So when should you use spreadsheets? I like them for two circumstances:
• When compiling information from diverse sources. For example, you want to compare your company's financial ratios with those of industry standards supplied by your accountant.
• When you are doing a one-time analysis. There isn't a faster tool for building a simple snapshot that you might not use again.
Of course, you're free to use spreadsheets however you want. But if you want to debate the merits of spreadsheets vs. databases, I'll see you on Springer.
Monday, June 21, 2010
Introduction to 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)