Microsoft Excel (also sometimes called Microsoft Office. Excel) - a program for working with spreadsheets created by Microsoft Corporation for Microsoft Windows., Windows NT and Mac OS. It provides opportunities for economic and statistical calculations, graphic tools and, with the exception of Excel 2008 under Mac OS X, VBA macroprogramming language (Visual Basic for applications). Microsoft Excel is part of Microsoft Office and today Excel has one of the most popular programs in the world.
The valuable feature of Excel is the ability to write code based on Visual Basic for applications (VBA). This code is written using the editor separate from the tables. The spreadsheet is performed using an object-oriented code and data model. Using this code, the data of the input tables will be instantly manned and displayed in tables and charts (graphs). The table becomes the code interface, allowing it easy to work, change it and manage calculations.
With Excel, you can analyze large data arrays. In Excel, you can use more than 400 mathematical, statistical, financial and other specialized functions, bind different tables with each other, choose arbitrary data presentation formats, create hierarchical structures. Truly infinite methods of graphical representation of data: except for several dozen embedded types of diagrams, you can create your own, that types are configured, help usably display the chart thematics. Those who are only mastering work on Excel will appreciate the help of "Masters" - auxiliary programsthat help when creating charts. They, as good wizards, asking suggestive questions about foreseen further steps and showing, depending on the planned response, the result will hold the user "by hand" for all stages of constructing the chart by the shortest way.
Working with the table is not limited to a simple data enhancing and building diagrams. It is hard to imagine an area wherever the analysis of this data. In Excel, a powerful analysis tool includes a summary table. With it, you can analyze widescreen tables, contain a large number of Unbounded data, and only by several codal mouse button to bring them in a convenient and read view. The development of this tool is simplified by the presence of the appropriate master program.
Microsoft Excel has two main types of objects: book and letter.
Book Microsoft Excel is a file that is used for processing and storing data. Each book can consist of several sheets, so in one file you can put a variety of information and establish the necessary connections between them.
Letters Serve for organizing and analyzing data that can be entered and edited simultaneously on multiple sheets, as well as perform calculation based on data from several sheets. After creating a diagram, you can put on the letter with the appropriate data or on a separate letter of the chart.
The names of the sheets are displayed on the labels at the bottom of the book window. To switch from one letter to another you need to specify the corresponding shortcut. The name of the active sheet is highlighted in bold.
Microsoft Excel has a lot of various functions, for example:
1. FinancialAmong the many special features that computing interest on the deposit or loan, depreciation deductions, the profit rate and the diverse inverse and related values.
2. Date and Time Functions - Most of the functions of this category seeks the transformations of the date and time in different formats. Two special functions today and tdata insert into the camork current date (first) and date and time (second), updating them with each file call or when making any changes to the table.
5. Link and arrays.In this category, there are functions that are allowed to refer to the data array (by column, row, rectangular interval) and get a variety of information: the number of columns and rows, it includes their number, the contents of the solid element you need; It can be found in which camork this array is the desired number or text, etc.
6. Text - In this group, dozen two teams. With their help, you can count the number of characters in the collar, including spaces (DLTRT), find out the symbol code (codemes), find out which symbol is worth the first (LevsMB) and the last (right-hand) in the line of the text, put a certain number of characters from another Collars (PSTR), put all the text from another camork in the active camork (register), check, or coincide two text cups (coincide), find some text (search, find) and replace it with others (replace ).
7. Check properties and values - There are commands here, with which you can get information about the data type in the collar (the number there is, text or some other information), about the format, about the current operating environment, about typical errors that occur in the formula, etc. P..
8. Working with database - Here you can find statistical accounting teams (BDDIP - dispersion by sample from the base, BDDISP - dispersion by the general set, the dstandot lock is a standard deviation by sample), operations with columns and strings of the base, the amount of non-empty (BSCT) or (BSET) cells and T ..
9. Master Chart - Built-in Excel program, which simplifies work with the main capabilities of the program.
Assign MS Excel.
MS Excel is one of the most popular spreadsheet programs today. It enjoys scientists, accountants, journalists, etc., with its help leads a variety of tables, lists and catalogs, make up financial and statistical reports, calculate the state of the trading enterprise, process the results of the scientific experiment, keep records, prepare presentation materials. Excel features are very high. Text processing, database management - the program is so powerful that in many cases surpasses specialized editors or database programs. Such a variety of functions may initially confuse how to make applied in practice. But as experience gained experience, you begin to appreciate what the boundaries of Excel capabilities are hard to achieve.
For a long history of table calculations using personal computers User requirements for such programs have changed significantly. Initially, the main focus in such a program, as, for example, VisiCalc was raised to count functions. Today, along with engineering and accounting calculations, the organization and graphic image Data acquires increasing importance. In addition, the variety of functions offered by such a computational and graphic program should not complicate the user's work. Windows programs create ideal prerequisites for this. IN lately Many just switched to using windows As your user environment. As a result, many firms creating software began to offer a large number of programs under Windows.
The Excel program provides both ease when accessing data and their safety. Excel allows you to quickly perform work for which you do not need to spend a lot of paper and time, as well as attract professional accountants and financiers.
This program It will be able to calculate the amounts on strings and columns of the tables, to calculate the arithmetic average, bank percentage or dispersion, here you can use many standard functions: financial, mathematical, logical, statistical ones.
Excel has more benefits. This is a very flexible system "grows" along with the needs of the user, changes its appearance and adjusts to you. The Excel base is the cell field and the menu at the top of the screen. In addition, up to 10 toolbars with buttons and other controls can be located on the screen. It is possible not only to use standard toolbars, but also create your own.
Conclusion.
In order to know Excel, you need to work in it. Using this program, you will probably open all new and new features and properties. Explore and experiment. If you do not like the results, try again. The consequences of almost all actions in Excel can be canceled, so experimenting, you will not lose anything but a few minutes of your time.
Excel is an indispensable universal analytical tool for dynamic solutions to computing tasks. Consider on concrete examples as Excel helps improve the quality of our modern life and makes us more successful.
7 examples why need Excel
Why do you need Excel? We present the life examples of the application of formulas, functions and charts in solving important tasks. These 7 useful examples of using the formulas are given as solutions for specific tasks.
Analysis of profitability of investment
Calculate the value of the future value of the investment object will help the function \u003d BS ().
Task 1: Calculate the amount accumulated on the account in 5 years when investing in an investment object. Provided that at the beginning of each year $ 6,000 is paid, and the annual interest rate is 5.6%. Despite the fact that at the beginning of investment, the amount on the balance sheet has already accumulated $ 5,000.
Solution of task 1 in Excel using the BS function:
Consideration of loan repayment options
This task is to check the loan repayment options with the Bank's offered monthly payments. Moreover, it can be clearly traced in actually to close the loan at such conditions of crediting and repayment.
Task 2: The column "Is repayment is possible" - this is a possible calculation of repayment using if, in which the type of value returned by the KPER function should be considered. In the "Monthly Contribution Size Check" column, use the PL function to calculate the amount of payment for the number of payments returned by the Cper function, rounded to the nearest whole. Thus, we can check or obtain the correct analysis of other input parameters of lending conditions. Suppose when we are already known to be known for the loan repayment and amount, as well as the number of regular closure payments.
Solution of the problem 2 in Excel with the help of KPER and PL functions:
Note! If the bank prohibits making early repayment of the loan without penalties, then under the conditions of crediting in the first line it is impossible to pay off this loan! Be careful and learn Excel.
Car depreciation service cost
The method of a decreasing balance allows us to analyze this type of costs as depreciation of the car, tractor, equipment or other expensive in the operation of the device. Solving this taskYou understand your motives and rationality in the acquisition of such annexted product. To begin with, we correctly set the task, which will be as close as possible similar to other similar tasks in the life of a modern person.
Task 3: The cost of purchasing a car for freight is $ 20000 after 10 years of operation its cost is only $ 40,000. Identify depreciation deductions and the current residual cost of the vehicle for each year throughout the entire period of operation.
Solution of task 3 in Excel using formulas, graphics and functions \u003d PoU ():
Forecasting future progress
Now we will analyze temporary rows with progression during exponential growth of indicators.
Task 4: Prejoin the future indicators of the company's progress. A table containing all the values \u200b\u200bof an important series for all time points is automatically populated by using one prediction function growth (directly working with data tables).
In the second argument the function, the growth of "known x" - contains the range of cells in which points are placed outside the time series for which the values \u200b\u200bof the test parameter will be extrapolated. These are predicted values \u200b\u200b(predicted in the future) series.
The constant is a logical constant that defines the point of intersection of the trend line with an independent variable axis. This is usually a coordinate point (0, b), and this is the default value of True. Use the default value.
Solution of task 4 in Excel with the help of growth prediction function - growth and graphics.
Smart Data Filtering
Extended filter Autofilter in Excel allows you to quickly process large amounts of data and immediately issue a result in accordance with the filter criteria.
Task 5: Show fish with prices not higher than 20 and prices for spices at least 20 with a non-zero stock in stock. And also, show drinks with prices from 10 to 20 with a non-zero stock in stock.
Solution of task 5 in Excel with the help of an extended autofilter.
 Microsoft Excel 2008. |
|
| A type | |
|---|---|
| Developer | |
| Operating system | |
| Latest version | |
| License | |
| Website | |
Microsoft Excel. (also sometimes called Microsoft Office Excel ) - a program for working with spreadsheets created by Microsoft Corporation for Microsoft Windows, Windows NT and Mac OS. It provides opportunities for economic and statistical calculations, graphic tools and, with the exception of Excel 2008 under Mac OS X, VBA macroprogramming language ( Visual Basic for Application). Microsoft Excel is part of Microsoft Office and today Excel is one of the most popular Apps in the world.
History
VBA functionality made Excel easy target for macroviruses. And it was a serious problem until the antivirus products have learned to detect them. Microsoft, after receiving measures to reduce risk, added the ability to select the security mode:
- fully disable macros
- enable macros when opening a document
- trust all macros signed using reliable certificates.
Excel versions from 5.0 to 9.0 contain various "Easter eggs", although, starting with version 10 Microsoft, it began to take action on their liquidation.
Version
Versions for Windows and Windows NT
Excel 97 (8.0) on Windows XP
Microsoft Office Excel 2003
- 1988 - Excel 2.0 for Windows
- 1990 - Excel 3.0
- 1992 - Excel 4.0
- 1993 - Excel 5.0 (Office 4.2 and 4.3, there is also a 32-bit version for Windows NT only)
- 1995 - Excel 7 for Windows 95 (enabled in Microsoft Office 95)
- 1997 - Excel 97 (enabled in Microsoft Office 97)
- 1999 - Excel 2000 (9) - Microsoft Office 2000
- 2001 - Excel 2002 (10) - Microsoft Office XP
- 2003 - Excel 2003 (11) - Microsoft Office 2003
- 2007 - Excel 2007 (12) - Microsoft Office 2007
- 2010 - Excel 2010 (14) - Microsoft Office 2010
- Comment: Version 1.0 was not used to avoid confusion with Apple products
- Comment: Excel 6.0 does not exist because windows version 95 Comes with Word 7. All Office 95 and Office 4.x programs used OLE 2.0 (automatic data transmission between different programs), and Excel 7 It should have shown that it corresponds to Word 7.
- Comment: Version 13.0 was skipped. Excel 2010 has an internal version of 14.0.
Versions for Macintosh.
- 1985 - Excel 1.0
- 1988 - Excel 1.5
- 1989 - Excel 2.2
- 1990 - Excel 3.0
- 1992 - Excel 4.0
- 1993 - Excel 5.0
- 1998 - Excel 8.0 (Office "98)
- 2000 - Excel 9.0 (Office 2001)
- 2001 - Excel 10.0 (Office v. X)
- 2004 - Excel 11.0 (Office 2004)
- 2008 - Excel 12.0 (Office 2008)
- 2011 - Excel 14.0 (Office 2011)
OS / 2 versions
- 1989 - Excel 2.2
- 1990 - Excel 2.3
- 1991 - Excel 3.0
Excel versions up to 7.0 were limited in data storage capabilities, accommodating up to 16,384 (2 14) lines. Versions 8.0-11.0 could cope with 65,536 (2 16) rows and 256 columns (2 8). Version 12.0 and 14.0 are treated to 1,048,576 (2 20) rows and 16,384 (2 14) columns.
File formats
Microsoft Excel up to 2003 version inclusive, used its own binary file format (BIFF) as the main one. Excel 2007 uses Microsoft Office Open XML as its main format.
Despite the fact that Excel 2007 supports and is aimed at using new XML formats as the main, it is still compatible with traditional binary formats. In addition, most microsoft versions Excel can read CSV, DBF, SYLK, DIF and other formats.
Office Open Xml.
Microsoft Excel 2007, along with other Microsoft Office 2007 products, enters many new file formats. They are part of Office Open XML (OOXML) specification.
| Format | Expansion | Notes |
|---|---|---|
| Excel Workbook | .xlsx | Standard Format of Excel 2007 Workbooks. In fact, this is a compressed zip archive of the XML document directory. It is a replacement of a former binary format.xls, although it does not support macros for security reasons. |
| Excel Workbook with macros | .xlsm. | Is the same workbook Excel, but with macros support. |
| Excel Binary Workbook | .xlsb. | The same Excel Workbook with macros, but storing information in binary format, opening documents faster than XML. In particular, it is often used for very large documents with tens of thousands of rows and / or hundreds of columns. |
| Excel template with macro | .xltm | The template created as a basis for working books is included by macros support. Replaces the old format.xlt. |
| Excel add-in | .xlam | Excel add-in aimed at adding additional functionality and tools. |
Export and Move Tables
API allows you to open Excel tables in a number of other applications. This includes the opening of Excel documents on web pages using ActiveX or plagins such as Adobe Flash Player. The Apache POI project presents the Java libraries to read and write Excel spreadsheets. Attempts have been made to copy Excel's tables into web applications using the values \u200b\u200bseparated by commas (CSV).
Programming
Excel's valuable feature is the ability to write code based on Visual Basic for applications (VBA). This code is written using the editor separate from the tables. The spreadsheet is carried out through an object-oriented code model and data. Using this code, the data of the input tables will be instantly processed and displayed in tables and charts (graphs). The table becomes the code interface, allowing it to easily work to change it and manage calculations.
MS Excel Password Protection
Microsoft Excel Protection provides several types of different passwords:
All passwords except the password to open the document can be removed instantly, regardless of the MS Excel version in which the document was created. These types of passwords primarily serve for collective work on the document. When they are used, the document is not encrypted, and the document saves hash from password installed. Although when using a password to protect the book, the document is encrypted on famous password "Velvetsweatshop"This does not add additional protection document. The only type of password that can prevent an attacker to access the document is "Password for opening"But the cryptoposticness of such protection strongly depends on the MS Excel version in which the document was created.
In MS Excel version 95 and earlier "Opening Password" It is converted to a 16-bit key that is crashed instantly. In MS Excel 97/2000, the password has been transformed into a 40-bit key that modern equipment It can also be hacked very quickly. In addition, password hacking programs can sort out hundreds of thousands of passwords per second, which allows not only to decrypt the document, but also find the original password. In MS Excel 2003 / XP, the situation has improved a little - the user can select a practical any encryption algorithm available in the CRYPTOSERVICEPROVIDER. But by default, all the same protection MS Excel 97/2000 remained. For users who are accustomed to trusting the behavior of programs by default, this means that there is no protection for their documents.
The situation has changed radically in MS Excel 2007 - for encryption, the modern AES algorithm with a key of 128 bits was used, and to obtain the key to use 50000 to use the SHA1 hash function, which reduced the speed of the extinguishing speed to hundreds of passwords per second. In MS Excel, the default protection resistance has been increased by another 2 times, thanks to the application already 100,000 multiple SHA1 password conversion to the key. Conclusion: on this moment Persistent protection provides only documents stored in Office 2007 \\ 2010 complicated on them "Password for opening".
Criticism
The overall criticism of the tabular processors is applicable to Excel. Specific critics in Excel are accuracy, dates and display errors. Excel 2007.
Accuracy
Due to the fact that Excel works based on floating semicolons, Excel's statistical accuracy is criticized. Supporters Excel declare, these errors are manifested only in special conditions specially selected source data that affect a relatively small number of users, and with a low probability can meet in practice. For versions 97, 2000, 2002, errors are fixed when performing the MOD function (division with the residue) with certain arguments, where the function returns the #NUM! Instead of the result.
Problems with date
In Excel versions until 2007 inclusive, 1900 was mistakenly considered leap. The error occurred in Lotus 1-2-3 and was intentionally entered in Excel for compatibility and maintained for backward compatibility.
Excel Display Errors
Screenshot Microsoft Excel 2007, showing the display error 65535
On September 22, 2007, it was reported that in certain situations Excel 2007 will show incorrect results. In particular, for those pairs of numbers whose product is 65535 (for example, 850 and 77,1), Excel will appear as a result of 100,000. This occurs with about 14.5% of such pairs. In addition, if you add an unit to the result, Excel will result in 100001. However, if you deduct from the total one, the correct result is the correct result of 65534. (Also, if you multiply or divided by 2, 131070 and 32767.5, respectively, will be displayed.)
Microsoft reported Microsoft Excel blog that the problem exists in the mapping of six-specific floating point values \u200b\u200bbetween 65534,9999999995 and 65535 and six values \u200b\u200bbetween 65535,999999995 and 65536 (not including boundaries). Any calculations, the result of which is equal to one of the twelve values, will be displayed incorrectly. The actual data stored and transmitted to other cells is true, is incorrect only to display the value. The error appeared in Excel 2007 and does not exist in previous versions. October 9, 2007 Microsoft has released a patch that corrects the problem. He entered the Service Pack 1 correct.
Notes
Links
- Working with Excel files with .NET Framework (English)
- List of MVP sites (eng.)
| Microsoft Office. | ||
|---|---|---|
| Client applications | Accounting · Access · Lync · Entourage · Excel · Groove · INFOPATH · INTERCONNECT · MAPPoint · OneNote · Outlook · PowerPoint · Project · Publisher · SharePoint Designer · Visio · Word | |
| Server applications | Communications · Forms · Groove · PERFORMANCEPOINT · Project · Project Portfolio · SharePoint | |
| Outdated applications | Binder · Data Analyzer · Frontpage. · Liquid Motion. · Mail · Office Assistants. · Photodraw. · Photo editor · Schedule +. · Vizact. · Web Components. | |
| Online services | Office Online · Office 365 · Office Live Meeting · SkyDrive | |
| Versions for Windows. | 3.0 · 4.0 · 4.3 · 95 · 97 · 2000 · XP · 2003 · · · | |
| Versions for Mac OS | 98 · 2001 · x · 2004 · 2008 · 2011 | |
| Technologies | Object Linking and Embedding · Office Open XML · Visual Studio Tools for Office · Visual Basic for Applications | |
| Category | ||
If you have never used a tabular processor for creating documents, we advise you to read our Excel (Excel) Guide for Dummies.
After that, you can create your first tabular sheet with tables, graphs, mathematical formulas and formatting.
Detailed information on basic functions and table processor capabilities.
Description of the main elements of the document and instructions for working with them in our material.
By the way, to work more effectively with Exel tables, you can familiarize yourself with our material.
Work with cells. Filling and formatting
Before proceeding with specific actions, it is necessary to deal with the basic element of any.
The excel file consists of one or more sheets separated into small cells.
The cell is the basic component of any exhale report, table or graphics. Each cell contains one information block. It may be a number, date, money amount, unit of measurement or other data format.
To fill the cell, it is enough to simply click on it by the pointer and enter the necessary information.
To edit a previously filled cell, click on it with a double mouse click.
Fig. 1 - Example of filling the cells
Each cell on the sheet has its own unique address. Thus, it can be calculated or other operations.
When you press the cell at the top of the window, a field will appear with its address, title and formula (if the cell is learning in any calculations).
Choose the cell "Share of Shares". The address of its location is A3. This information is specified in the properties panel that opens. We can also see the contents.
There is no formula for this cell, so they are not shown.
More cell properties and functions that can be used with respect to it are available in the context menu.
Click on the cell with the right key of the manipulator.
The menu will open, with which you can format the cell, analyze the contents, assign another value and other steps.
Fig. 2 - Context Menu Cell and its basic properties
Data sorting
Frequently, users face the task of sorting data on a sheet in Excel. This feature helps to quickly choose and view only the desired data from the entire table.
Before you already (how to create it will look further in the article). Imagine that you need to sort the data for January ascending.
How would you do it? Banal reprinting table is an extra work, moreover, if it is volumetric, no one will do this.
For sorting to Excel, there is a specially designated function. The user is required only:
- Select a table or information block;
- Open the "Data" laying;
- Click on the "Sort" icon;
Fig. 3 - Tab "Data"
- In the window that opens, select a table column over which we will conduct actions (January).
- Next, the type of sorting (we carry out a grouping by value) and, finally, the order is ascending.
- Confirm the action by clicking on "OK".
Fig. 4 - Setting Sort Parameters
There will be automatic data sorting:
Fig. 5 - the result of sorting numbers in the "January" column
Similarly, it is possible to sort the color, font and other parameters.
Mathematical calculations
The main advantage of Excel is the ability to automatically carry out calculations in the process of filling the table. For example, we have two cells with values \u200b\u200b2 and 17. How to enter their result in the third cell, without making calculations yourself?
For this, you need to click on the third cell, which will be entered the final result of calculations.
Then press the F (X) icon, as shown in the figure below.
In the window that opens, select the action you want to apply. The amounts are the amount, the average value, and so on.
A complete list of features and their names in the Excel editor can be found on the official website of Microsoft.
We need to find the amount of two cells, so click on the "sums".
Fig. 6 - Select the "amount" function
In the function arguments, there are two fields: "Number 1" and "Number 2". Select the first field and click on the cell with the number "2".
Her address will be recorded in the argument string.
Click on "Number 2" and click on the cell with the number "17". Then confirm the action and close the window.
If you need to perform mathematical actions with three or large quantity Cells, just continue to enter the values \u200b\u200bof the arguments in the field "Number 3", "Number 4" and so on.
If in the future the value of the summable cells will change, their amount will be updated automatically.
Fig. 7 - the result of the calculation
Creating Tables
In Exeleian tables, you can store any data.
Using a function fast settings and formatting, the editor is very easy to organize a personal budget control system, a list of expenses, digital data for reporting and so on.
Have an advantage over the similar option in other office programs.
Here you have the opportunity to create a table of any dimension. The data is filled easily. There is a feature panel to edit content.
In addition, the finished table can be integrated into dOCX file Using the usual copy-insert function.
To create a table, follow the instructions:
- Open the Insert tab. On the left side of the options panel, select "Table". If you need to enable any data, select the "Summary Table" item;
- Use the mouse to highlight the place on the sheet, which will be assigned to the table. And you can enter the location of the data into the element creation window;
- Click OK to confirm the action.
Fig. 8 - Creating a standard table
To format the appearance of the resulting plate, open the contents of the designer and in the "Style" field click on the template you like.
If you wish, you can create your own appearance with another color gamut and selection of cells.
Fig. 9 - formatting table
The result of filling the table of data:
Fig. 10 - Filled Table
For each cell cell, you can also configure the data type, formatting and display mode. The designer window accommodates all the necessary options for further configuration of the plate, based on your requirements.
Microsoft Excel. - This is an integral part of Microsoft Office. The Excel program appeared in 1987. The spreadsheet is one of the most convenient applications for data processing and presenting them in tabular form. Programs processing spreadsheets are called tabular processors. The Excel program allows you to analyze data using diagrams, create documents forms, produce calculations using formulas. For people, professional activity which are associated with numbers, compiling reports and forms, analysis of these results, the program is an indispensable assistant. With its help, they can lead a log of economic operations, issue an invoice, settlement and payment statement, staff schedule or invoice and carry out any other tasks associated with systematization and ordering information. The program name is the abbreviated from the English word Excellent - which means "Excellent".
The Excel menu consists of:
2. Edit - you can use when editing a document. It contains commands to cancel, cut, copy, insert, find, fill, go. The selection of the command to cancel allows you to refuse the newly completed formatting, copying or deletion, i.e. Any action whose result does not suit you. Cut commands, copy and paste can be used to fill the cells data, the go command is used to move to a cell with a specific address.
3. The Insert menu item contains a string, sheet, diagram command, a note. As it follows from the name, the commands of this menu can be used when adding strings, sheets and diagrams, as well as to insert the function to the document when creating formulas and notes.
4. Menu item The format contains commands that can be used when formatting cells.
5. Menu item The data is used when working with databases. Commands Sort, Filter, Summary Table Allows you to sort database tables, select data from them by specific criteria, create consolidated tables.
6. The menu item is used to configure the program.
In Excel, the numbers of strings and columns are called headers, and the cell numbers are called the name of the cell. The cell in which the user works is called active, it is highlighted in a fat frame. The active cell name is displayed in the upper left part of the Excel window, this field is called the name field. If we want to get a product in any cell (on the example of counting the cost of any parts number * price) - we do this as follows: activate this cell (where there will be a result) → Standard toolbar → Function Insertion → Function Master dialog box The left part of the window in the Category section chosen mathematical functions and are looking for a piece. After selecting the desired function and click OK, the second window of the functions wizard opens - in which you need to specify the product of what two (or more) cells we need. Cells are listed through: (colon). Next formula must be set throughout the column, for this we use Autocomplete. The active cell is marked with a fat rectangle, and in the lower right corner there is a bold point. This point is called auto-filler marker. If you put the mouse pointer to this marker, then it will turn into a black cross. We make an active cell in which there is already a formula, we move the pointer to the autocill marker, click the left button and not releasing it "spread" the frame of the active cell down, to the last row filled with us. We release the mouse button and see that the column was filled with data. Now make an active cell G 19, click on the button Σ Avosumnwhich is located on the standard toolbar to the left of the Function Insert Button. To select the cells, click the left button and not releasing the "stretch" the cell to the last row we need. In the string of formulas and in the cell itself G 19, the formula is visible (sums (G 7: G 17)), press ENTER and the result is the result. Excel allows you to specify what kind of data is stored in cells, it is called numeric formats. The combination of all cell settings is called cell format.
Menu Format, Select item Cells, in the dialog box on the first tab Number In the field numeric formats, choose money, you can specify how many signs after the comma we go to see. After selecting the format OK. After that, the number of price and sum will be displayed in monetary terms. The table is ready, but when printing the boundaries of the cells will not be displayed, if we want these boundaries to show, then you need to use the means of framing. To do this, use the button Borders. After framing and fill, the table will look in print as well as on the monitor screen.
Select column / row. To highlight whether the string column is enough to click on the header header or row. If you want to highlight a few running columns or rows, then you need to click on the header header of the first desired column / string and not releasing the button to drag the pointer to the headers that we need. If some of the selected rows or columns needed to be allocated, you can selectively cancel the selection (or allocate) using the Ctrl key.
Changing the width / height of the column / string. Change is performed using a pointer when it takes a bidirectional arrow.
Insert rows / columns / cells. If during operation it will be necessary to insert a string or columns in the table, then we will do it as follows:
¯ highlight in the table string over which you want to add a new one
¯ Right-click on the selection and in the context menu that opens, select Add Cells.
Deleting rows / columns / cells. To delete rows or columns, you must first allocate them. You can delete both a string and a group of strings or columns. We highlight the left key of the "stretch" mouse until you decide, all that you want to delete, press the right mouse button and select the clause in the context menu Delete. Select the removal method in the window (with a shear left or up).
Excel sheets. Excel documents are not a single table with which we still worked, and the book where the default contains three such tables. These tables are called sheets. Go from the sheet to the sheet is easy, for this in the lower left corner there are labels of sheets. When working with complex documents, it is very convenient to use sheets, because from any sheet you can make references to cells of other sheets and when drawing up the formulas to use data from several sheets.
Formatting sheet. The appearance of the data you entered can be changed. The change external view The document is called the formatting of a sheet or cells. For formatting, a panel is used under the header of the program window, or the format command → cells from the menu bar. Pre-cells must be highlighted. To call the dialog box, the cell format can also be used to use the context menu command format cells.
Sample format. To copy the cell format from one sheet to another, the button is served Sample format. The meaning of copying the format is to transfer to another cell only the format of the selected cell, and its contents are left for the same. The same is true for the group of cells.
1. Select a fragment formatted properly and click on the sample format button. Next to the mouse pointer will appear the tassel icon.
2. Select the cell or range to which you want to distribute the selected format.
3. Release the mouse button. Selected cells will be formatted properly.
Exercise 1.
In A1-A3 cells, we enter the number 20 and the appointment of the formats general, monetary, interest
2000%, Align the numbers in the center
Exercise 2.
We open the sheet 2, in the cell with the address A4 we introduce the value of the departure, into the cell A5 the value of the arrival. In order for the data in the cells beautifully, you can use the format → column → the width of the width. In C4 and B5 cells, specify the dates of departure and return from the business trip. Let it be 01/10/2004 and January 16, 2004. In the cell with the address B6, we introduce the formula to calculate the days of stay on a business trip: \u003d B5 - B4.
Building diagrams. The diagram is one of the ways of representing data. The meaning of the diagrams is to more clearly submit the information contained in the table. The information presented in graphicThe pictures are perceived much easier than text or tabular. How to create a chart in Excel - you need to highlight the data for the diagram and start the master charts. QC and all other Masters Microsoft Office This wizard will task you a number of questions to determine the parameters of the object being created, and then creates it in accordance with the instructions received. Master of charts on the toolbar, or menu command Insertion → Chart. In Excel, you can build bulk and flat charts. By default, the diagram is built throughout the allocated area. If the text is in the top line and in the left column, the program automatically generates a legend on their basis. Legend is a description conventionsadopted in this diagram. After creating a diagram, you can edit: change the colors of lines and columns, the font of the inscriptions in the diagram. In order to move to the diagram editing mode, double-clicking on one of the chart items.
Exercise 3.
Sale of goods.
A1 - Diagram name - in warehouses
A3 - Warehouse Wholesale
A4 - Common Warehouse
B2 - 2 - Quarter 1 - Quarter 3. Use the ability to autofill.
Fill in the table data, select the cells and run the charts wizard. For sales analysis, it is safely convenient to use a circular diagram.
To analyze sales in a warehouse wholesale, we mark the cells A2: E2
In the warehouse total A3: e3
Excel Usage Examples
Registration of the log of operations
Fill A1 - Operations Log
B2 - Economic Operation
C2 - Debit
D2 - Credit
E2 - sum
Change the B2 cell size so that the text is completely visible. Select the A2-E2 cells and format them by applying the sewing sequence, font size 14. Align the cells in the center, select the color (use the formatting toolbar buttons). Select the A1-E1 cells and click on the button Combine and put in the center On the formatting toolbar. The title should be in the center of the journal. Fill the log data. If the economic operation takes several lines, select Format → Cells → Alignment tab and check the box to transfer the following words (there are three windows, mark the desired checkbox). Figures can be aligned in the center of the cell, click to do this on the button in the center, highlighting the pre-cell, starting with E3. Format these cells by choosing a cash format. To do this, format → Cells → Number, set a cash format in the Numeric format field and specify the value 2 in the number of decimal signs. Similarly, the Excel program can be applied to design a payment statement, invoices, a ruble sild statement. Of course, we are talking about the use of Excel for these forms, if the enterprise is small and reporting on it is not too cumbersome.
