General information about excel. Purpose and main functions of table processors General information on excel
The simplest means of processing numerical data on a computer is standard program Windows Calculator. However, the Calculator's capabilities are limited; it is difficult to process large amounts of numerical data, for example, experimental results, statistical data, etc. In these cases, spreadsheets are used.
Spreadsheet is an interactive numerical data processing program that stores and processes data in rectangular tables.
The spreadsheet consists of columns And lines. Column headings are designated by letters or combinations of letters (A, G, AB, etc.), row headings are designated by numbers (1, 16, 278, etc.). Cell – the intersection of a column and a row. Each table cell has its own address. Cell address spreadsheet made up of a column header and a row header, for example, Al, F123, R7. The cell with which some actions are performed is highlighted with a frame and called active.
In the Windows&Office environment, spreadsheets are implemented using Excel applications. The application window has a standard appearance. In Excel tables are called worksheets. A worksheet (spreadsheet) is the main type of document used in Excel to store and process data; a worksheet consists of cells organized into columns and rows. You can enter and change data on multiple sheets at the same time, and perform calculations based on data from multiple sheets.
By default, sheets are named “Sheet 1”, “Sheet 2”, etc. The names and order of the sheets can be changed. Selecting another workbook sheet is done by clicking on the tab of this sheet. The selected sheet becomes active. Each Excel spreadsheet file is workbook, consisting of several sheets. To make it easier to work with workbook sheets, sheet shortcuts and scroll buttons are located in the lower left part of the window.
Database. Purpose and main functions
Any of us, starting from early childhood, have repeatedly encountered “databases”. These are all kinds of directories (for example, telephone directories), encyclopedias, etc., notebooks, etc.
Databases are information models containing data about objects and their properties. Databases store information about groups of objects with the same a set of properties.
For example, the “Address Book” database stores information about people, each of whom has a last name, first name, telephone number, etc. A library catalog stores information about books, each of which has a title, author, year of publication, etc.
Information in databases is stored in an orderly manner. So, in notebook All records are organized alphabetically, and in the library catalog either alphabetically (alphabetical catalogue) or by field of knowledge (subject catalogue).
Database is an information model that allows you to orderly store data about a group of objects that have the same set of properties.
There are several different structures information models and correspondingly various types databases: tabular, hierarchical And network.
Tabular database contains a list of objects of the same type, i.e. objects that have the same set of properties. It is convenient to represent such a database in the form of a two-dimensional table: in each of its rows the values of the properties of one of the objects are sequentially placed; Each property value is in its own column, headed by the property name.
The columns of such a table are called fields; each field is characterized by its name (the name of the corresponding property) and the data type representing the values of this property. A database field is a table column that contains values a certain property.
The table rows are records about the object; these records are broken down into fields by table columns, so each record represents a set of values contained in the fields. A database record is a table row containing a set of values for a specific property, located in database fields.
Each table must contain at least one key field, the contents of which are unique for each record in this table. A key field allows you to uniquely identify each record in a table. A key field is a field whose values uniquely identify each record in a table.
The key field most often used is a field containing the data type counter. However, sometimes it is more convenient to use other fields as the key field of the table: product code, inventory number, etc.
The type of a field is determined by the type of data it contains. Fields can contain the following basic data types:
– Counter contains a sequence of integers that are specified automatically when entering records; these numbers cannot be changed by the user;
– Text contains up to 255 characters;
– Numerical contains numbers;
– Date Time contains dates or times;
– Monetary contains numbers in monetary format;
– Logical contains values True(Yes) or Lie(No);
Each field type has its own set of properties. The most important field properties are:
– Field size defines the maximum length of a text or number field;
– Field Format sets the data format;
– Obligatory field indicates that this field must be filled in.
Hierarchical databases can be graphically represented as an inverted tree consisting of objects of different levels. Top level occupies one object, the second - objects of the second level, etc. There are connections between objects; each object can include several lower-level objects. Such objects are in relation ancestor(object closer to the root) to descendant(a lower-level object), while an ancestor object may have no children or have several of them, while a descendant object necessarily has only one ancestor. Objects that have a common ancestor are called twins.
The hierarchical database is Catalog Windows folders, With which you can work with by launching Explorer. The top level is occupied by the folder Desktop. On the second level there are folders My computer. My Documents, network And Basket, which are children of the folder Desktop, and are twins to each other. In turn, the folder My computer is the ancestor of third-level folders, disk folders ( Disc 3.5(A:), (C:), (D:), (E:), (F:)) and system folders ( Printers, Control Panel and etc.).
Network Database is a generalization of the hierarchical one due to the assumption of objects having more than one ancestor, i.e. each element of a higher level can be associated simultaneously with any elements of the next level. In general, no restrictions are imposed on the connections between objects in network models.
A network database is actually The World Wide Web global computer network Internet. Hyperlinks link hundreds of millions of documents.
Spreadsheets. Purpose and main functions.
One of the most productive ideas in the field of computer information technologies became the idea of a spreadsheet. Many development companies software created their own versions for PC table processors- application programs designed to work with spreadsheets. Of these, the most famous are Lotus 1-2-3 from Lotus Development, Supercalc from Computer Associates, Multiplan and Excel from Microsoft. Domestic school computers are also equipped with simplified (educational) versions of table processors.
Table processors (TP) are a convenient tool for economists, accountants, engineers, scientists - all those who have to work with large amounts of numerical information. These programs allow you to create tables that (unlike relational databases data) are dynamic, i.e. they contain so-called calculated fields, the values of which are automatically recalculated using specified formulas when the values of the source data contained in other fields change. When working with spreadsheet processors, documents are created - spreadsheets (ET). A spreadsheet (document) is created in the computer's memory. In the future, you can view it, change it, write it to a magnetic disk for storage, or print it on a printer.
Spreadsheet Environment
The working field of the spreadsheet processor is the display screen on which the spreadsheet is presented in the form of a matrix. ET, like a chessboard, is divided into cells, which are usually called table cells. The rows and columns of the table are labeled. Most often, the rows are numbered numerically, and the columns are alphabetic (letters of the Latin alphabet) notation. Like on a chessboard, each cell has its own name (address), consisting of the column name and row number, for example: A1, C13, F24, etc.
But if there are only 8x8 = 64 cells on a chessboard, then there are much more cells in a spreadsheet. For example, the tabular Excel processor The maximum table size contains 256 columns and 16384 rows. Since there are only 26 letters in the Latin alphabet, starting from the 27th column, two-letter notations are used, also in alphabetical order: AA, AB, AC,..., AZ, BA, BB, BC,..., BZ, CA... The last, 256th column is named IY. This means that there are cells with the following names, for example: DL67, HZ10234, etc.
Excel spreadsheets. Basic information.
Presenting data in the form of tables greatly simplifies the analysis of information. To solve problems that can be presented in the form of tables, special software packages have been developed, called spreadsheets or table processors. They are focused primarily on solving economic problems, but with their help you can solve mathematical, physical and engineering problems, for example, carry out calculations using formulas, build graphs and diagrams.
Excel program included in the office software package Microsoft Office and is designed for preparing and processing spreadsheets under the Windows operating shell. Versions of Excel 4.0 and Excel 5.0 are designed to work on Windows 3.1, and Excel 7.0 and 97 are designed to work on Windows-95/98. There is Excel-2000, part of the Office-2000 software package, running on Windows-2000. The older the Excel version number, the more advanced it is.
Excel is one of the main office programs. computer technologies processing numerical data.
An Excel document is a file with an arbitrary name and an XLS extension. This *.xls file is called a Work Book. Each *.xls file can contain from 1 to 255 spreadsheets, each of which is called a worksheet. One spreadsheet consists of 16,384 rows and 256 columns located in computer memory. The rows are numbered with integers from 1 to 16384, and the columns are designated by letters of the Latin alphabet A,B,C,...,Z,AA,AB,AC,...,IY.
At the intersection of a column and a row, the main element of the table is located - the cell. In any cell you can enter source data - a number, text, as well as a formula for calculating derived information. The width of a column or row can be changed using the mouse. When you enter data into a cell, this happens automatically, i.e. spreadsheets are rubbery. To indicate a specific cell, an address is used, which is made up of the column designation and row number at the intersection of which the cell is located, for example: A1, B2, F8, C24, AA2, etc.
To make a cell active, you need to point the mouse at it and press the left mouse button. The cell will be highlighted with a rectangular frame. When entering a formula, you must first enter the = sign, since the = sign is a sign of the formula. The rectangular group of cells defined by the first and last cells separated by a colon is called an interval. Example: C5:D10. Selecting a group of cells is done with the mouse.
Excel spreadsheets can be used to create Databases. Excel is a multi-window program. The windows are Excel worksheets. To sort the data you need to point the mouse
Menu Data, Sorting.
To launch Excel, you first need to start Windows, then find the Excel icon on the desktop or in the Start button menu and double-click on it. For old Windows 3.1 you need to open the MS Office (Excel 5.0) or Applications (Excel 4.0) program group window. In these windows there is a program element Excel 4.0 or 5.0. Excel is launched by double-clicking on the Excel program element.
At the top there is a Main (horizontal) drop-down menu consisting of 8 items. Below the menu is a toolbar (buttons with special icons).
Getting Help, Calling Excel Help - F1 Key or Sign? on the menu. The Excel 5.0 Help system includes a Quick Start tutorial.
To create a file, you need to point the mouse to the File menu item, then Save As, find the directory on the disk where the file will be located, and set the file name. You can use the F12 key (Excel 4.0). The file extension will be xls. The file name, if not specified, will be book1.xls (Excel 5.0, Excel-97) or sheet1.xls (Excel 4.0).
To update (clear) a window in Excel, you need to select the File item with the mouse and then the Create File item or Ctrl-N in Excel 5.0-97. To save the file - menu item File, Save or Ctrl-S (Excel 5.0-97), or Shift-F12 (Excel 4.0).
In order to load (read) a file from disk - menu item File, Open file or Ctrl-O (Excel 5.0-97), or Ctrl-F12 (Excel 4.0). Printing a file - menu item Print or Ctrl-P (Excel 5.0-97) or Ctrl-Shift-F12 (Exel 4.0). Before printing, you need to select and frame the part of the table with filled cells that you want to print.
To exit (exit) Excel - menu item File and then Exit or Alt-F4. If there is an unsaved file in the window, then you need to save it or exit without saving, but then the information will be lost.
A file created in Excel 4.0 can be read in Excel 5.0 or Excel-97, but not vice versa. In Excel, you can quickly fill tables with numbers using a formula, for example, using the Edit Menu, Fill Down.
In Excel, you can use built-in tools: Chart Wizard for plotting graphs, Function Wizard for performing mathematical calculations, programs for creating drawings (as in Word).
To call the Function Wizard, select Insert Menu, Function, and select the required function from the list of built-in functions.
To call the Chart Wizard, select Insert Menu, Chart. But first, highlight a column of numbers.
To call the program for creating drawings, select Insert Menu, Object, and select MS Draw in the list of objects. To call the specified objects, you can also use the corresponding buttons in the toolbar. Excel also has its own drawing program.
To insert a graphic file *.bmp, *.wmf, etc. into an Excel spreadsheet, you need to point the mouse to Insert Menu, Picture, select the required graphic file containing the picture on the disk, and OK.
In Excel, as in Word, it is possible to exchange information (text, graphics, formulas, charts, etc.) with other applications (Word, MS Works, PaintBrush, etc.) via the Windows Clipboard.
Copying, cutting and pasting the selected cell contents is done in the same way as in Word 6.0 through the Insert menu item or the corresponding buttons in the toolbar.
A powerful tool used in spreadsheets is functions. Functions are predefined formulas that perform calculations on given values, called arguments, in a specified order.
For example, the SUM function sums the values in a range of cells, and the PPLAT function calculates the payment amount for one annuity period based on constant payments and a constant interest rate. The function is included in the formula as an operand.
In order for the MS Excel spreadsheet processor to correctly recognize a function and perform calculations in accordance with its algorithm, it is necessary to strictly adhere to the syntax - a set of rules for writing this function. A function begins with its name, followed by an opening parenthesis, arguments separated by semicolons, and then a closing parenthesis. If you start writing a formula with a function, enter an equal sign (=) before the function name.
Function arguments are quantities, the type and order of which when written must strictly correspond to the syntax of the function. The function's arguments can contain numbers, text, booleans (such as TRUE or FALSE), arrays, error values (such as #N/A), or references. In addition, arguments can be either constants or formulas. These formulas, in turn, can contain other functions.
In total, the MS Excel spreadsheet processor includes more than three hundred functions, which for convenience are combined into 11 categories (for Excel 2003).
1. Functions for working with databases. Used to analyze data in lists. In this case, the range of cells forming a list or database is considered as a database. The MS Excel TP assumes that the database is a list of related data, in which the data rows are records and the columns are fields. The top line of the list contains the names of all columns. The names of many database functions often begin with the character "D".
2. Date and time functions. Used to analyze and work with date and time values in formulas.
3. Engineering functions are used to perform engineering analysis. In this category, three groups of functions can be distinguished: a) functions for working with complex numbers; b) functions for converting numbers from one number system to another (decimal, hexadecimal, octal and binary); c) functions for converting quantities from one system of weights and measures to another.
4. Financial functions allow you to perform typical financial calculations and are used for planning and analysis of the financial and economic activities of an enterprise, as well as in solving problems related to investing funds.
5. Information functions are designed to determine the type of data stored in a cell, check the fulfillment of some condition and return TRUE or FALSE depending on the result.
6. Logic functions allow you to check the fulfillment of one or more conditions, which makes it possible to implement selection or loop algorithms.
7. Browse functions are used to find the desired data in lists or tables. Using these functions you can receive text or numeric values, which are impractical or impossible to calculate, but can be selected from the table on the worksheet.
For example, by the name of a product or service, you can determine its cost from the price list, etc.
8. Mathematical functions allow you to perform simple and complex calculations, such as calculating the sum of a range of cells, calculating the sum of cells in a range that satisfy a specified condition, rounding numbers, etc. These include arithmetic, logarithmic and trigonometric functions.
9. Statistical functions are required to perform statistical analysis on ranges of data. For example, you can use a statistical function to draw a line through a group of values, calculate the slope and y-intercept, and so on.
10. Text functions are used to perform actions on strings of text (for example, changing case or determining the length of a string, concatenating multiple strings into one, etc.).
11. In TP, in addition to the named functions, external functions can be used. They operate in the form of add-ons - auxiliary programs that serve to add special commands or capabilities to Microsoft Office. Examples of such functions include:
EUROCONVERT - converts an amount into euros, converts from euros into the national currency of a country using the euro, or converts from one national currency to another using the euro as an intermediate;
SQL.REQUEST - provides connection to external source data and executing a query from the sheet. The result is returned as an array. No additional programming is required.
In some cases, it is necessary to use a function as one of the arguments to another function. For example, formula (6.13) uses the nested function AVERAGE and compares the result with the value 50. Moreover, it contains another nested function CYMM(G2:G5)
ECJIH(CP3HA4(F2:F5)>50,CyMM(G2:G5),0) (6.13)
A nested function used as an argument must evaluate to the data type corresponding to that argument. For example, if the argument must be a Boolean, that is, have the value either TRUE or FALSE, then the nested function as a result of the calculations must also produce a Boolean value, either TRUE or FALSE. Otherwise, the error message “#VALUE!” will appear.
You can use up to seven levels of function nesting in formulas. If function B is an argument to function A, then it has a second level of nesting. For example, in formula (6.13), the AVERAGE and SUM functions are second-level functions because they are both arguments to the IF function. A function that might be nested as an argument in the AVERAGE or SUM function would be a third-level function, and so on.
To avoid syntax errors when entering formulas, it is advisable to use the Function Wizard - step by step instructions, which makes this task easier. In this case, the function is entered in two stages. Using the Insert/Function... command, the Function Wizard dialog box appears (Fig. 6.10), in which the user must select the function he needs to perform calculations. At the same time, in
Rice. 6.10. Function Wizard Dialog Box (Step 1)
The lower part of the window displays the function name, its description and arguments.
In addition, from this window, using a hyperlink, you can call up help on this function, which, in addition to the description, provides examples of calculations. After clicking the OK button of this dialog box, the following Function Wizard window appears (Fig. 6.11), where you can see a description of each argument, the current result of the function and the entire formula. When you perform this step, help on the function is also available.
stupid to the user. Entering the function ends by pressing the OK button.
When using links as arguments, the user must consider the possibility of further copying the created formula. To do this, write them down in the dialog box in absolute or relative form.
In cases where the user does not know exactly which function to use to solve a problem, the Function Wizard can also be useful. In the first step, just enter in the “Search for function” field short description action you want to perform and click the Find button. A list of functions found in accordance with the compiled description is displayed in the Select a function field.
Spreadsheet– Excel document – a table with named columns and numbered rows.
In each cell of the spreadsheet you can enter numerical or text information, date and time, a hyperlink, and a formula for calculating or obtaining data.
Each column of the table has a name specified in letters of the Latin alphabet ( A, B, C, ...,A.A., ..., AB, ..., XFD), each line has its own number ( 1…1048576 ). Text and numeric information is entered into certain table cells. In this case, the expression “enter into cell A1 information" means move the cursor to the cell A1(column A line 1) and enter the necessary information from the keyboard.
Spreadsheet workspace – The area of the Excel window intended for entering information is the document window.
BookExcel– Excel 2007 file.
Excel Sheet – work area – an area for entering and editing information, which is a table consisting of 1,048,576 rows and 16,384 columns.
Table cell– an area of a spreadsheet intended for entering information, which has its own unique name, consisting of the column name and the row number. For example, D1.
Cell range – area characterizing a collection of cells. Format: top left range cell: bottom right range cell.
Formula– the contents of the cell, starting with the “=” (equals) sign, which is an instruction for generating information in this cell.
Absolute link– cell address used in the formula, which does not change when moved or copied. An absolute reference differs from a relative reference by the presence of a dollar sign before the column name and (or) row name, which means the fixed nature of the cell address elements (row number, column name).
Formatting a cell spreadsheet is done through activating the formatting buttons, selecting the font type and size, selecting the frame, or through the menu Form at I cells, where you can completely set the format for presenting data in a cell and its appearance.
Basic Excel technologies include:
Data entry and editing.
Entering formulas.
Selecting cells.
Copying and moving worksheet cells.
Deleting cells and data in cells.
Filling cells.
Formatting cells.
Copy cell formatting.
Hiding and showing rows and columns.
Working with blocks of information (arrays, named ranges).
Control of data entry into cells.
Finding and replacing information.
Sorting lists and tables.
Auto-filtering of lists and tables. Getting totals in a filtered list.
MS Excel 2007 window
The first time you launch Excel, a window appears on the screen containing control elements that correspond to the standard setting. This window can be divided into several areas
Rice. MS Excel 2007 working window.
The areas of the Microsoft Excel 2007 window in the standard configuration are listed below.
TabFile.
In File 2007, every application has a tab that replaces the File button in 2007. Clicking this button provides a set of commands that let you do things you're familiar with. In earlier versions of office programs, these commands were located in the File menu. These are, in particular, the commands for creating, saving and opening documents. Quick Access Toolbar quick access in the form of small buttons with pictures. These buttons are called tools, and clicking on any of them will execute the corresponding Excel commands. The Quick Access Toolbar is customizable.
By default, it has only three buttons, but by customizing it you can display any number of Excel commands. Object name field
. This is a convenient tool for assigning names to various table objects (pictures, charts, individual cells and groups of cells). You can then access named objects by their names. Formula bar
. This is where you will enter formulas to calculate various values. The same line allows you to edit the text of an already entered formula or view it if the table itself displays their calculated values instead of formulas.
Buttons for controlling the main program window. These buttons allow you to expand, minimize, or close the main Excel window.
Buttons for managing child program windows. These buttons allow you to expand, collapse, and close individual Excel workbooks within the main window, which remains open.
Table column headings. To access individual cells within a spreadsheet, cell names are used, which are a combination of the column name and row number at the intersection of which the cell is located. Accordingly, the column names appear in their headings. By default, a standard Excel 2007 table contains 16,384 columns, designated by combinations of letters of the Latin alphabet, starting with A and ending with XFD.
Table row numbers. By default, a standard Excel table contains 1,048,576 rows (over a million!), numbered consecutively.
The method of accessing cells in an Excel table is reminiscent of a game of “battleship” and should not cause you any difficulties. Navigator for book sheets . An Excel workbook by default contains 3 tables called sheets. have the names Sheet 1, Sheet 2 and Sheet 3. However, the user can give the added sheets other arbitrary names, as well as change the existing standard sheet names.
Status bar.
This line identifies the state of certain Excel parameters, as well as some properties of the document being edited. The various areas of the status bar not only act as indicators, but also act as tools. This means that clicking on some status bar icons allows you to change the corresponding setting. Tool Ribbon . In Excel 2007, you won't find the menu commands you might be used to in others.
