What is Ms excel for? Purpose and capabilities of Microsoft Excel. and simple operations with them
Description of MS Excel features
Microsoft Excel(full title Microsoft Office Excel is a spreadsheet program created by Microsoft for Microsoft Windows, Windows NT and Mac OS. Included in the Microsoft Office suite.
Using Excel, you can analyze large amounts of data. In Excel, you can use more than 600 mathematical, statistical, financial and other specialized functions, link various tables to each other, select arbitrary data presentation formats, and create hierarchical structures.
For graphical presentation of data, in addition to several dozen built-in chart types, you can create your own custom types that help to clearly reflect the subject of the chart.
Using the Object Linking and Embedding (OLE2) mechanism allows you to widely use additional graphic editors, an equation editor and many other utilities that support the OLE2 mechanism. The peculiarity of this mechanism is that any object in the document (drawing, video, text) can be edited directly in the main document without resorting to additional programs.
The mechanism for dynamic data exchange between Excel and others is also impressive. Windows applications. Let's say that a quarterly report is being prepared in Word for Windows. The report is based on data in an Excel table. If you provide a dynamic link between an Excel table and Word document, then the report will always contain the latest data. You can even write the text of a report template, insert table links into it, and thus significantly reduce the time it takes to prepare quarterly reports.
Ease of working with a table affects productivity, so in Excel, tables and working with them are organized in such a way as to provide maximum functionality with a minimum of effort on the part of the user. All tables are immediately combined into workbooks. The desired table is accessed by clicking on the spine of that table, on which its name is written. You can change the table name at any time without having to rely on file naming conventions. It is possible to edit a table directly in a cell, which allows you to simultaneously specify different fonts and their styles in it.
Working with a table is not limited to simply entering data into it and drawing diagrams. Excel includes powerful analysis tools - pivot tables and charts. With their help, you can analyze wide-format tables containing a large amount of unsystematized data, and with just a few clicks of the mouse button, bring them into a convenient and readable form. Mastering this tool is simplified by the presence of an appropriate wizard program.
IntelliSense technology is an integral part of any application in the Microsoft Office family for Windows 9x. For example, the auto correction mechanism is available in any Microsoft Office application, including Microsoft Excel 2003.
Research has shown that more than half of the people who frequently use Microsoft Excel in their work keep a regular calculator on their desktop. The reason turned out to be simple: in order to perform the operation of summing two or more cells to obtain an intermediate result (and, as practice shows, most people have to perform such an operation quite often), it is necessary to perform two extra steps. Find the place in the current table where the total amount will be located and activate the summation operation by pressing the S (sum) button. And only after this you can select those cells whose values are supposed to be summed.
That's why Microsoft Excel, starting with version 7.0, has built in the AutoCalculate function. This function allows you to see the result of the intermediate summation in the status bar by simply highlighting the required table cells. In this case, the user can indicate what type of result he wants to see - the sum, the arithmetic average, or the value of a counter reflecting the number of marked elements.
Another innovation in the latest versions of the product allows you, when filling out a large table, not to waste time on typing text that is already contained in a cell that was filled in earlier. As you enter text, previously entered cells are automatically scanned, and if there is a match, Excel offers to automatically place the found cell contents into a new one. To do this, simply press the Enter key.
The automatic filtering mode allows you to quickly make selections from table records by simply specifying the desired filtering mechanism. The new document open and search dialog provides more user-friendly interface, allowing for preview and filtering documents by several criteria: date of creation, file length, etc.
Microsoft Excel interface latest versions became more intuitive and understandable. Research has shown that when using previous Microsoft versions Excel users often did not have time to “see” the process of inserting a row. While performing this operation, a new line appeared very quickly, and the user often could not understand what happened as a result of performing a specific operation? Has there appeared new line? And if it appeared, then where? To solve this problem, Microsoft Excel implemented a "dynamic interface". Now, when inserting a row, a new table row appears on the screen smoothly, and the result is quite obvious. The execution of other operations, for example, a delete or line break operation, is reflected in a similar way. Other interface details have also become more clear. For example, when you scroll a table window using the slider, the current row number appears on the scroll bar, which helps you navigate the position of the “float” relative to the entire table. For each table cell, you can insert a comment directly into the cell, and when the mouse cursor hits this cell, the comment will be highlighted automatically.
Microsoft Excel interface and data display
The Excel window contains many different elements (see Figure 1.1). Some of them are common to all programs in Windows environment, the rest are available only in this table editor. The entire workspace of the Excel window is occupied by a blank worksheet (or table), divided into separate cells. The columns are headed with letters, the rows with numbers.
As in many other programs in the Windows environment, the worksheet is presented as separate window with its own title - this window is called a workbook window, since several worksheets can be processed in such a window.
On one working page there will be 256 columns and 16384 rows. The rows are numbered from 1 to 16384, the columns are named by letters and combinations of letters. After the 26 letters of the alphabet, the columns are followed by combinations of letters from AA, AB, etc. In the Excel window, as in other programs of the Microsoft Office family, there is a menu bar under the window title.
Just below are the toolbars: “ Standard" And " Formatting" The buttons on the toolbar allow you to quickly and easily access many Excel functions.
Rice. 1.1 Microsoft Excel 2003 interface
You can change the font type, size or execution of text by selecting the corresponding cells and opening the menu “ Format" By selecting the command " Cells" on the menu " Format" After this, a dialog will appear on the screen in which various fonts will be indicated; you can select any font from the list of suggested ones. When you select a font, you can view its style in the “ Example" You can use the fields and buttons located on the toolbar to select the font type, size and style.
Along with choosing the font type and size, you can choose the font style: italic, bold or underlined. Use these styles only for highlighting important information in the text of documents and tables.
In Excel, you can highlight certain fields in a table using background color and pattern to draw attention to them. This selection must be used carefully so as not to overload the table. To do this, select the tab " View" in the dialogue " Cell Format" Here you can select a fill color for the selected cells using the palette.
If you want the records to turn into convenient document, you need to format the numbers in the cells. The easiest way to format cells is where monetary amounts are entered. To do this, you need to select the cells to be formatted. Then select the “Format Cell” menu command, and in the dialog that appears, select the “Number” tab. Select the “Cash” line in the group on the left. Several will appear on the right possible options number formats. The number format is determined by the type of digital template, which can be of two types: to better understand their purpose, let's look at the number formatting options.
The first column contains format templates, as in the “Format codes” field. The second column shows what the number will look like as a result of formatting.
Format Result
#.###,## 13
0.000,00 0.013,00
#.##0,00 13,00
If a zero is used as a digital pattern, it will persist wherever it is not replaced by a significant digit. The number icon (shown as a hash mark) is missing in places where there are no significant digits. It is better to use a number pattern in the form of a zero for numbers after the decimal point, and in other cases use a hash mark.
Excel has a spell checker for text found in worksheet cells, charts, or text fields. To run it, you need to select cells or text fields in which you need to check spelling. If you need to check all the text, including the objects located in it, select the cell starting from which Excel should look for errors. Next you need to select the command “ Service – Spelling" Then Excel will start checking spelling in the text.
You can start checking using the F7 key. If the program detects an error or does not find the word being checked in the dictionary, the dialog “ Spellchecking».
Calculation in Excel
By default, when you enter, edit formulas, or fill cells with formulas, all formula calculations in the worksheet occur automatically. However, with complex integration calculations this can take a long time, so you can cancel the automatic calculation.
To do this, select the menu command “ Service –Options", then in the tab that appears " Calculation» select option « Manually" and set the switch " Recalculate before saving" After this, all calculations in the worksheet will occur only after pressing the " Calculate».
All mathematical functions are described in programs using
special characters called operators. Full list operators are given in Table 1.
The text connection operator is designed so that when creating a sample document, you do not have to enter dates, for example, manually each time - the program itself will access the cell in which the date was entered.
The functions are designed to make it easier to create and interact with spreadsheets. The simplest example of performing calculations is the operation of addition. Let's use this operation to demonstrate the benefits of functions. Without using the function system, you will need to enter the address of each cell separately into the formula, adding a plus or minus sign to them. As a result, the formula will look like this: =B1+B2+B3+C4+C5+D2
Table 1.1. List of MS Excel operators
Operator
Function
Example
Arithmetic operators
addition
A1+1
subtraction
4-С4
multiplication
A3*X123
division
D3/Q6
percent
Telecom operators
range
SUM(A1:C10)
Union
SUM(A1;A2;A6)
Text join operator
connection of texts
It is noticeable that it took a lot of time to write such a formula, so it seems that this formula would be easier to calculate by hand. To quickly and easily calculate a sum in Excel, you just need to use the sum function by clicking the button with the image of the sum sign or from " Function Wizards", you can also manually type the function name after the equal sign. After the name of the functions, you need to open a bracket, enter the addresses of the areas and close the bracket. As a result, the formula will look like this: =SUM(B1:B3;C4:C5;D2) .
If you compare the writing of the formulas, you can see that a colon here denotes a block of cells. Commas separate function arguments. Using blocks of cells, or areas, as arguments for functions is advisable because, firstly, it is more visual, and secondly, with such recording it is easier for the program to take into account changes on the worksheet. For example, you need to calculate the sum of the numbers in cells A1 to A4. This can be written like this: =SUM (A1;A2;A3;A4). Or the same in another way: =SUM (A1:A4).
Building charts
Graphic charts enliven the dry columns of numbers in a table, which is why already in early versions of Excel the ability to create charts was provided. In everything Excel versions starting from version 5.0 enabled " Chart Wizard”, which allows you to create “presentation quality” diagrams.
Charts can be placed next to the table or placed on a separate worksheet.
The Chart Wizard is one of the most powerful tools in Excel. Constructing a diagram with its help is carried out in several steps. The wizard is told the source area of the table, the type of chart, the labels and colors to use. The main panel has an icon for calling the Chart Wizard.
In addition to regular charts, Excel has the ability to create pivot charts that allow preliminary grouping of data.
What-if analysis in MS Excel
Add-in “Parameter Selection”
Special function Goal Seek allows you to determine the parameter (argument) of a function if its value is known. When selecting a parameter, the value of the influencing cell (parameter) changes until the formula depending on that cell returns the specified value.
It is enough to specify the formula, its value and the cell being modified that affects this formula. Excel searches for a solution through successive iterations. Variable cell must contain a value (not a formula) and must affect the result you want to obtain.
To use the Parameter Selection tool, you must perform the following steps:
— select the cell with the formula that needs to be “adjusted” to the specified value;
— execute the command Service > Select parameter. The “Parameter Selection” dialog box will appear (see Fig. 2.1). The “Set in cell” field will already contain a link to the selected cell.
Rice. 2.1 “Parameter selection” tool
— in the Value field, enter the value that needs to be obtained.
In the “Changing cell value” field, enter a link to the original cell. This cell should influence the formula selected in step 1. The Parameter Selection tool will begin the iterative process of finding a solution.
If you need to pause or cancel the lengthy parameter selection process, you must click the “Pause” or “Cancel” button in the “Parameter selection result” dialog box that opens. After pressing the pause button, you can go through the process of finding a solution step by step. To do this, use the “Step” button. To resume automatic search click the “Continue” button.
After a solution has been found, you must click the “OK” button to replace the value on the worksheet with a new one, or click the “Cancel” button to save the previous values.
A special Microsoft Excel add-in will help solve the problem of finding a parameter under imposed boundary conditions Solver (Search for a solution) .
Using lookup tables
Lookup tables (or data tables) are part of a block of tasks that are sometimes called what-if analysis tools. A data table is a range of cells that shows how changing certain values in formulas affects the results of those formulas. Tables provide a way to quickly calculate multiple versions within a single operation, and a way to view and compare the results of all the different versions on a single worksheet.
A data table with two variables, for example, can show the effect of different interest rates and loan terms on your monthly mortgage payment.
Data lookup tables are recalculated whenever a worksheet is recalculated, even if no changes have been made to them. To speed up the process of recalculating a sheet containing a data substitution table, you should change the parameter as follows: Computations so that the sheet is automatically recalculated, but not the tables.
To create a single-variable lookup table, you must create a single-variable lookup table so that the entered values are either in a column (column-oriented) or in a row (row-oriented). Formulas used in single variable lookup tables must reference an input cell.
Either in a separate column or on a separate row, enter a list of values that should be substituted into the input cell.
Perform one of the following actions.
If the values in the lookup table are column oriented, enter the formula in the cell one row above and one cell to the right of the first value. To the right of the first formula, enter any other formulas.
If the values in the lookup table are row oriented, enter the formula in a cell that is one column to the left and one row below the first value. In the same column, but below, enter any other formulas.
Select the range of cells containing formulas and lookup values.
On the menu Data select team Table.
Perform one of the following actions:
if the values in the table are arranged in columns, enter the reference to the input cell in the field Substitute values by row into;
if the values in the table are arranged in rows, enter the reference to the input cell in the field Substitute values by columns into.
To create a lookup table with two variables, use one formula with two sets of values. The formula must reference two different input cells.
Add-on “Search for a solution”
Microsoft Excel add-in Solver (Search for a solution) is not installed automatically during normal installation:
Using Pivot Tables to Analyze Data
Creating and editing pivot tables
A Pivot Table is a table that is used to quickly summarize or combine large amounts of data. By swapping rows and columns, you can create new totals of the original data; By displaying different pages, you can filter the data, and Rice. 3.1 Pivot table example
also display detailed area data (see Figure 3.1). A PivotTable contains fields that summarize the source data in multiple rows. By moving the field button to a different location in the PivotTable, you can change the presentation of the data.
Page field is a field of the original list or table placed in the page orientation area of the pivot table. In this example, "Region" is a page field that can be used to summarize by region. When you specify another page field element, the PivotTable is recalculated to display the totals associated with that element.
Page Field Elements combine records or values of a field or column of the original list (table). In this example, the "East" element displayed in the "Region" page field maps all the data for the eastern region.
Data field is a field in a source list or table that contains data. In this example, the Order Amount field is a data field that summarizes the source data in the Order Amount field or column. The data field typically summarizes a group of numbers (such as statistics or sales quantities), although the current data can also be text. By default, a PivotTable summarizes text data using the Number of Values summary function, and numeric data using the Sum summary function.
Field elements- These are subcategories of a pivot table field. IN in this example the values "Meat" and "Seafood" are field elements in the "Products" field. Field elements represent records in a field or column of source data. Field items appear as row or column headings and in the drop-down list for page fields.
Row fields- These are the fields of the original list or table placed in the row-oriented area of the pivot table. In this example, "Products" and "Salesperson" are row fields. Internal row fields (for example, “Salesperson”) correspond exactly to the data area; The outer row fields (for example, “Products”) group the inner ones.
Column field is a field of the original list or table placed in the columns area. In this example, "Quarters" is a column field that includes two field elements, "QR2" and "QR3". Column internal fields contain elements corresponding to the data area; The outer margins of the columns are placed above the inner margins (the example shows only one column margin).
Data area is the part of a pivot table that contains summary data. Data region cells display totals for row or column field items. The values in each cell of the data area correspond to the original data. In the example above, cell C6 summarizes all source data records containing the same product name, distributor and specific quarter (“Meat”, “Myastorg LLP” and “KV2”).
A pivot table can be created based on data in a list or database Microsoft data Excel, multiple Microsoft Excel sheets, in an external database, and also in another pivot table.
Team Data, Pivot Table causes PivotTable Wizards for building summaries - totals of certain types based on data from lists, other pivot tables, external databases, several separate areas of data in an MS Excel spreadsheet. A PivotTable provides various ways to aggregate information.
Pivot Table Wizard builds a pivot table in several stages:
Stage 1. Specifying the source type of a pivot table:
- using a list (base Excel data);
— use of an external data source;
— use of several consolidation ranges;
— using data from another pivot table.
Depending on the type of source, the subsequent stages of creating a pivot table change. Let's consider the most common case of using lists when building pivot tables.
Stage 2. Specifies the range of cells that contains the source data. The list (Excel database) must necessarily contain the names of the fields (columns). Full name range of cells is written as
[work_name]sheet_name!cell range
If you first place the cursor in the list for which the pivot table is being built, the cell interval will be automatically specified. To link to a closed interval of another workbook, click the button<0бзор>, in the dialog box of the same name, select the disk, directory and file of the closed workbook, enter the name of the worksheet and the range of cells or the name of the block of cells.
Stage 3. Building a Pivot Table Layout. The structure of a pivot table consists of the following areas, defined in the layout (Figure 3.2):
Rice. 3.2 Pivot table layout diagram
page - it contains fields whose values ensure the selection of records at the first level; several fields can be placed on the page, between which a communication hierarchy is established - from top to bottom; It is not necessary to define a page;
column- fields are placed from left to right, providing grouping of the pivot table data according to the hierarchy of fields; As long as a page or row area exists, it is not necessary to define a column;
line- fields are placed from top to bottom, ensuring grouping of table data according to the hierarchy of fields; As long as the page area or columns exist, it is not necessary to define a row;
data - fields for summing up, according to the selected function; The area must be determined.
Placing fields is done by dragging them while holding down the left mouse button to a specific area of the layout. Each field is placed only once in the areas: page, line or column. You can form groups using these fields and get total values in the area data - grouping fields. In area data there can be fields of arbitrary types, the same field can be placed multiple times in the area data. For each such field, the type of function is specified and the necessary settings are made.
To change the structure of a pivot table, you move fields from one area to another (adding new fields, deleting existing fields, changing the location of a field). For pivot tables, the order of the fields is important (from left to right, from top to bottom); the order of the fields also changes by moving them.
In the PivotTable layout, you can customize the settings for the fields placed in the data area. This field setting is done using the Pivot Table Field Calculation dialog box (Figure 2.2).
To do this, place the cursor on the custom field and double-click left button mouse to open the PivotTable Field Calculation dialog box , in which you can rename a field, change the operation performed on the field data, or change the format for representing a number.
The "Advanced" button calls Additional calculations panel for selection of functions, the list of which is given in table. 2. When using the comparison function (Difference, Share, Given difference) select Field And Element, with which the comparison will be made. List Field contains the PivotTable fields to which the underlying data for a custom calculation is associated. List Element contains the values of a field that participates in a custom calculation.
Rice. 3.2 PivotTable Field Calculation Dialog Box
Table 2.1 Kinds additional functions above the field in the data area
Function
Result
Difference
field And element
Share
Data area cell values are displayed as a percentage of the specified element specified in the Field and Element list boxes
The given difference
The values of the cells in the data area are displayed as the difference with the given element specified in the lists field And element, normalized to the value of this element
With a cumulative total in the field
The data area cell values are displayed as a running total for successive items. You should select a field whose elements will be displayed in a cumulative total
Proportion of line amount
Data area cell values are displayed as a percentage of the row total
Proportion of the total by column
Data area cell values are displayed as a percentage of the column total
Share of total amount
Data area cell values are displayed as a percentage of the PivotTable total
Index
When determining the values of cells in a data area, the following algorithm is used: ((Value in cell) * (Grand Total)) / ((Row Total) * (Column Total))
Stage 4. Selecting the location and parameters of the pivot table. In the dialog box that appears in the fourth step (Fig. 2.3), you can select the location of the pivot table by checking the radio button new leaf or existing sheet, for which you need to specify a placement range. After pressing the button<Готово>a pivot table with a standard name will be generated.
Rice. 3.3 PivotTable Wizard dialog box at stage 4
Button<Параметры>in the 4th step dialog box, calls up the “Pivot Table Options” dialog box, in which you set the option for displaying information in the pivot table:
total amount by columns - At the bottom of the pivot table, general totals for the columns are displayed;
total amount by lines- a summary column is formed in the pivot table;
autoformat- allows you to format a pivot table using the command Format, Autoformat and other parameters.
Pivot charts
A PivotChart report allows you to display data in a graphical representation in a PivotTable report. You can change the layout and data displayed in a PivotChart report in the same way as in a PivotTable report.
Rice. 3.4 Sales PivotTable Report
Rice. 3.5 Summary chart report of the same information
Most of the operations for regular charts are the same as those for a PivotChart report. However, there are a number of differences.
Chart type. The standard type for a regular chart is a grouped bar chart, which compares data across categories. The default PivotChart report type is a stacked bar chart, which evaluates the contribution of each value to the total within a category. The PivotChart report can be changed to any type except Scatter, Stock, and Bubble charts.
Chart position. Regular charts are embedded in the worksheet by default. By default, PivotCharts are created on chart sheets. After you create a PivotChart report, you can move it to a worksheet.
Creating a Chart. To create a regular chart in Microsoft Excel, use the Chart Wizard. You can use the Chart Wizard to create a PivotChart report. If you already have a PivotTable report that serves as the source data for a PivotChart report, you can use the PivotTable and PivotChart Wizard.
Initial data. Regular charts are linked directly to worksheet cells. Pivot charts can be based on several various types data, including: Microsoft Excel lists; Database; data located in several consolidation ranges; and external sources (databases Microsoft Access and OLAP databases).
Chart elements. A PivotChart report contains the same elements as a regular chart, but also contains fields and objects that can be added, rotated, or removed to show different views of the data. Categories, series, and data in regular charts became category fields, series fields, and data fields, respectively, in a PivotChart report. The PivotChart report also includes page fields. Each of these fields contains objects that in a regular chart appear as category names or series names in legends. Margin buttons and area outlines may be hidden when printing or posting online.
Formatting. Some formatting options are lost after you change the layout or refresh a PivotChart report. These formatting options include trend lines and error bars, changes to value labels, and changes to data series. Regular charts do not lose these settings after formatting is applied.
Moving elements and resizing them. In a PivotChart report, you cannot move or resize the plot area, legend, axis titles, and charts, but you can choose from several preset positions for the legend and change the font size of the titles. In regular charts, you can move and resize all of these elements.
A PivotChart report can be created:
1. Based on PivotTable report. Make sure that your PivotTable report has at least one row field that will become a category field in the PivotChart report, and a column field that will become a Series field. If your PivotTable report is structured, move at least one field to the column area before you start creating the chart.
2. If there is no PivotTable report. The PivotTables and Charts Wizard specifies the type of source data you want to use and sets options for using the data. The PivotChart report is then laid out similarly to the PivotTable report. If the workbook does not contain a PivotTable report, then when you create a PivotTable report Microsoft charts Excel also generates a PivotTable report. When you change a PivotChart report, the associated PivotTable report changes, and vice versa.
3. Report setup. Then using the Chart Wizard and menu commands Diagram You can change the chart type and other settings such as titles, legend position, data labels, chart position, etc.
4. Using Page Margins. Using page fields is a convenient way to summarize and highlight a subset of data without having to change row and category information. For example, to show sales for all years during a presentation, select the item in the “Year” page field (All). By then selecting specific years, you can focus on information for individual years. Each chart page has the same category and row layout for different years, so data for each year is easily comparable. In addition, by allowing you to retrieve only one page at a time from large set data, page fields save memory when used in a chart external sources data.
2.3 Changing the pivot table: appearance, update, layout and formatting
After the PivotTable is completed, the source data may change. To update the pivot table data in this case, run the command Data, Update data.
To change the structure of an already built pivot table, the cursor is placed in the pivot table area and the command is executed again Data, Pivot Table, which causes Pivot Table Wizards, step 3.
It should be noted that pivot tables are read-only and the data in them cannot be changed. But the user can very easily transform the pivot table: add new rows and columns, swap them, change field names and the level of detail of the displayed data.
Each table area can contain not one, but several data fields. To add unused query fields to the PivotTable, you can simply drag them into the desired area.
For example, you can add the “Clients” field to the filter area. Name" (CompanyName), which will allow you to filter data not only by country, but also by client (see Fig. 2.4). To do this, you need to drag the “Clients” field. Name” (CompanyName) from the list of fields into the filter area and place it next to the “Country” field. Checking the boxes against the right clients, it will be possible to obtain summary data on accounts for each client.
To remove a row, column, or filter field from a table, you must drag it with the mouse outside the pivot table (a cross will appear next to the cursor). The field will disappear from the table.
The user can easily swap fields from the filter area and from the columns area or swap rows with columns. For example, you could move the Customers.Name field to the columns area and the Year field to the filter area. After this, the table columns will display sales data for each client (Fig. 3.6), and using the “Order Date By Month” field, you can filter this data.
Rice. 3. Displaying customer data in a pivot table
When moving fields, pay attention to the shape of the mouse pointer. When it hits one of the areas of the table - rows, columns, data or filter - its shape changes, and you can use it to determine when to release the mouse button.
The PivotTable is linked to the source data, but it does not update automatically when the source data changes. To update a pivot table, you need to select any cell in it and then select the Refresh data button in the Data menu or click the button of the same name on the Pivot tables toolbar.
To have Excel automatically update the PivotTable every time you open the workbook in which it is located, you must select Options from the PivotTable menu on the PivotTable toolbar. Then, in the PivotTable Options dialog box, you must check the Refresh on opening checkbox.
If one pivot table serves as a source of data for another, then updating either table will recalculate both tables.
You can use standard formatting techniques to change the appearance of PivotTable cells. Excel will retain the formats even after updating or reorganizing the table, provided that the Keep Formatting check box is not cleared in the PivotTable Options dialog box.
To ensure that applied formats are not lost when updating or reorganizing a table, you must perform the following steps:
1. Select any cell in the pivot table.
2. Select the Options command from the Pivot Table menu on the Pivot Tables toolbar.
3. In the Pivot Table Options dialog box, you must select the Save formatting checkbox.
Statistical data analysis tools
4.1 Data analysis tools
Microsoft Excel includes a set of data analysis tools (the so-called analysis package), designed to solve complex statistical and engineering problems. To analyze data using these tools, you must specify input data and select parameters; the analysis will be performed using a suitable statistical or engineering macro function and the result will be placed in the output range. Other tools allow you to present analysis results in graphical form.
Microsoft Excel provides a large number of statistical, financial and engineering functions. Some of them are built-in, others are only available after installing the analysis package.
The tools that are included in the data analysis package are described below. They are available through the command Data analysis menu Service. If this command is not in the menu, you must download an add-on Analysis package.
1. Analysis of variance.
There are several types of analysis of variance. The required option is selected taking into account the number of factors and available samples from the general population.
One-way analysis of variance is used to test the hypothesis that the means of two or more samples belonging to the same population are similar. This method also applies to tests for two means (which include, for example, the t-test).
Two-way ANOVA with repetitions. It is a more complex version of one-way analysis with several samples for each group of data.
Two-way ANOVA without repetition. It is a two-way analysis of variance that does not include more than one sample per group. Used to test the hypothesis that the means of two or more samples are the same (the samples belong to the same population). This method also applies to tests of two means, such as the t-test.
2. Correlation analysis.
Correlation analysis is used to quantify the relationship between two sets of data presented in dimensionless form. The sample correlation coefficient represents the ratio of the covariance of two data sets to the product of their standard deviations and is calculated using the following formulas.
Correlation analysis makes it possible to determine whether data sets are associated in magnitude, that is, large values from one data set are associated with large values of another set (positive correlation), or, conversely, small values of one set are associated with large values of another (negative correlation) , or the data of the two ranges are not related in any way (zero correlation).
To calculate the correlation coefficient between two sets of data in a worksheet, use the CORREL statistical function.
3. Covariance analysis.
Covariance is a measure of the relationship between two ranges of data. Used to calculate the average product of the deviations of data points from relative means.
Covariance analysis makes it possible to determine whether data sets are associated in magnitude, that is, large values from one data set are associated with large values in another set (positive covariance), or, conversely, small values of one set are associated with large values in another (negative covariance) , or the data of the two ranges are not related in any way (covariance is close to zero).
Covariance calculations for a single pair of data are performed using the COVARS statistical function.
4. Descriptive statistics.
This analysis tool produces a univariate statistical report that contains information about the central tendency and variability of the input data.
MS Excel also includes other tools for statistical analysis:
- regression analysis;
— Fourier analysis;
— moving average;
— percentile, etc.
4.2 Using a pivot table to consolidate data
Let's look at an example of using pivot tables to consolidate data.
Figure 4.1 shows three worksheets, each containing data for a month on sales of goods in one of the stores of a large retail chain. You need to consolidate this information into one pivot table. In this example, the source data is contained in a single workbook. However, this is not always the case. The data that needs to be consolidated may be in different workbooks.
To create a pivot table, you must complete the following steps.
- add a new sheet, you can call it Results.
— select the Data | PivotTable to launch the PivotTable and Chart Wizard tool.
— in the first dialog box of the wizard, select the In multiple consolidation ranges radio button and click the Next button.
- in the next dialog box, Pivot Table and Chart Wizard - step 2 of 3, select the Create one page field radio button, and then click the Next button.
Rice. 4.1. Worksheets containing monthly data on product sales
Now you need to define the ranges for consolidation. The first range is Store1!A$1:$D12 (its address can be entered directly or specified on the worksheet). You must click the Add button to add a range to the Range List list.
These steps must be repeated for the other two ranges. After this, you need to click on the Next button to go to dialog box step 3.
In the third dialog box of the Pivot Table and Chart Wizard, click on the Finish button.
As a result, the pivot table will look like:
Rice. 4.2 Summary table
In the fourth step of the described procedure, in the PivotTable and Chart Wizard - Step 2a of 3 dialog box, you can select the Create Page Fields radio button. This will assign a name to each element in the page field.
4.2 Grouping elements
Let's look at creating worksheet structures and grouping data.
Before you create a structure, you need to make sure that the data is suitable for creating the structure and that the formulas are set correctly. Data must have a hierarchical organization. All final formulas must be entered correctly and consistent. By coherence we mean that they are in the same relative position.
You can create a structure in one of the following ways:
— automatically;
- manually.
To automatically create a structure for a certain range of data, you need to do the following:
— place the table cursor in any cell of the range.
— select the Data | Group and structure | Creating a structure.
Excel will analyze the formulas from the selected range and create a structure. Depending on the formulas, a horizontal, vertical or mixed structure will be created.
If the worksheet already has a structure, it will ask if the user would like to change the existing structure. You must click the Yes button to delete the old structure and create a new one.
If you use the Data | Totals, then Excel will create the structure automatically, and formulas for calculating subtotals will be automatically inserted if the data is entered in the form of a list.
Excel determines how to create a structure based on the analysis of formulas, so without formulas it is impossible to automatically create a structure. The process of creating a structure manually involves creating groups of rows (for a vertical structure) or groups of columns (for a horizontal structure).
To create a group of rows, you must completely select all the rows that you want to include in this group, except for the row containing the formulas for calculating the totals. Then you need to select the Data | Group and structure | Group. As you create the group, Excel will display the outline symbols.
If you select a range of cells (rather than entire rows or columns) before creating a group, Excel displays a dialog box asking you what you want to group. Then the program will group entirely those rows or columns whose cells are contained in the selected range.
You can also select groups of groups. This will lead to the creation of multi-level structures. The creation of such structures should begin with the inner group and move from the inside out. If a grouping error occurs, you can ungroup using the Data | Group and structure | Ungroup
Excel has tool buttons that can help speed up the process of grouping and ungrouping (Figure 4.3). Alternatively, you can use the combination Alt keys+ Shift + to group selected rows or columns, or Alt + Shift + to perform an ungroup operation.
Rice. 4.3 Structuring tools
The structuring tool contains the following buttons.
Table 4.1 Structure toolbar buttons.
Button
Button name
Purpose
Document structure symbols
Hides and shows document structure symbols
Group
Group selected rows and columns
Ungroup
Ungroup selected rows and columns
Show details
Show details (i.e. corresponding data cells) for a selected total cell
Hide details
Hiding details (corresponding data cells) for a selected total cell
Select visible cells
Selects only visible worksheet cells, leaving hidden cells with data unselected
3.3 Data sorting and summary table summary, summary functions for data analysis
If your data is presented as a list, Excel can simplify the process by sorting and filtering the data.
Sorting is the ordering of data in ascending or descending order. The easiest way to sort this is by selecting one of the cells and clicking on the “ Sort Ascending" or " Sort in descending order" on the toolbar.
Sorting parameters are set with the command “ Data > Sort. This opens the dialog box “ Range sorting". In it you can select from one to three sorting fields, and also set the sort order for each field.
Let's look at calculating totals using a pivot table as an example (using data grouping). Excel provides a handy tool that allows you to group specific field elements. For example, if one of the database fields consists of dates, then each date in the pivot table will have a separate row or column. Sometimes it is useful to group dates into months or quarters and then remove too much detail from the screen. In Fig. Figure 4.4 shows a pivot table created based on the Bank database.
It shows the total balance for each account type (column field) for each branch (row field). You want to create a report that compares the performance of the Western branch with the other two branches combined. The solution is very simple - you need to create a group consisting of data from the central and northern branches.
Rice. 4.4 Pivot table example
To create a group, you need to select the cells that will be grouped, in this case A6:A7. Then you need to select the Data | Group and structure | Group. As a result, Excel will create a new field and name it Department2. There are two elements in this field: Western and Group1 (Fig. 4.5).
Rice. 4.5 Pivot table after grouping data
You can now remove the original Department field and rename the field and element names. Figure 4.6 shows the summary table after these changes. The new field name cannot be the same as the name of an existing field. If the names do not match, Excel simply adds a new field to the PivotTable. Therefore, in the example above, you cannot rename Department2 to Department without deleting the original field.
Rice. 4.6 Summary table after completed transformations
If the elements required for grouping are not located in a row, then you can select them as follows: you must press Ctrl and mark the elements that should form the group.
If field elements contain numbers, dates, or times, you can allow the program to group them automatically. Figure 4.7 shows part of another pivot table that was created from the same banking database. This time the row field is Account and the column field is Type. The data area displays the number of accounts of this type.
Rice. 4.7 Pivot table example
To create a group automatically, you need to check any item in the Account field. Then you need to select the Data | Group and structure | Group. The Grouping dialog box appears, shown in Figure 4.8.
Rice. 4.8 Grouping dialog box
By default it will show the smallest and highest value, which you can change at your discretion. For example, to create a group with increments of 5,000, you would enter 0 in the From field, 100,000 in the To field, and 5,000 in the Increment field. Then you need to click on the OK button, and Excel will create the specified groups. Figure 4.9 shows the resulting pivot table.
Rice. 3.9 Resulting PivotTable
In Excel there are final functions– they are used to calculate automatic subtotals, to consolidate data, and in PivotTable and PivotChart reports. The following summary functions are available in PivotTable and PivotChart reports for all source data types except OLAP (Table 4.2).
Table 4. 2 Summary functions
Function
Result
Sum
Sum of numbers. This operation is the default for summing numeric fields.
Number of values
Amount of data. This operation is the default for summing non-numeric fields. The Number of Values operation works in the same way as the COUNTA function.
Average
Average of numbers.
Maximum
Maximum numbers
Minimum
Minimum numbers
Work
Product of numbers.
Amount of numbers
The amount of data that is numbers. The “Number of numbers” operation works in the same way as the COUNT function.
Unbiased deviation
An unbiased estimate of the standard deviation for a population, where the sample is a subset of the population.
Biased Deviation
Biased estimate of the population standard deviation from a data sample.
Unbiased variance
An unbiased estimate of variance for a population where the sample is a subset of the population.
Biased Variance
Biased estimation of population variance from a data sample.
During the work, we examined such MS Excel tools as “what-if” analysis (and the substitution tables that implement it, the “Solution Search” and “Parameter Selection” add-ons), statistical processing data.
The work describes the main points that the user needs to know when using these tools. In addition, such a powerful Excel tool as the formation of pivot tables and charts was briefly reviewed.
Everything said above is enough to once again be convinced of the advantages of a spreadsheet program in relation to manual calculations, and, in particular, to be inclined in favor of a Microsoft product when choosing software for work.
List of sources used
Dodzhenkov V.A., Kolesnikov Yu.I. Microsoft Excel 2002. - St. Petersburg, BHV-Petersburg, 2003 - 1056 pp..
Dodge M., Stinson K. Effective work with Microsoft Excel 2002. – St. Petersburg: BHV-Petersburg, 2003. – 1072 p.
Mac Fedriz P. et al. Microsoft Office 97. User Encyclopedia. – Kyiv: “Diasoft”, 2009. – 445 p.
Fundamentals of economic informatics. Textbook Manual / Ed. A.N. Morozevich. – Mn.: LLC “New Knowledge”, 2006. – 573 p.
GENERAL CHARACTERISTICS OF PERSONAL COMPUTER SOFTWARE
Microsoft Excel is an extremely useful program in various areas. A ready-made table with the ability to autofill, quickly calculate and calculate, build graphs, diagrams, create reports or analyses, etc.
Spreadsheet tools can greatly facilitate the work of specialists from many industries. The information presented below is the basics of working in Excel for dummies. After mastering this article, you will acquire the basic skills with which any work in Excel begins.
Instructions for working in Excel
An Excel workbook consists of sheets. A sheet is a work area in a window. Its elements:
To add a value to a cell, left-click on it. Enter text or numbers from the keyboard. Press Enter.
Values can be numeric, text, monetary, percentage, etc. To set/change the format, right-click on the cell and select “Format Cells”. Or press the hotkey combination CTRL+1.
For number formats, you can assign the number of decimal places.
Note. To quickly set the number format for a cell, press the hotkey combination CTRL+SHIFT+1.
For Date and Time formats, Excel offers several options for displaying values.
Let's edit the cell values:
To delete a cell value, use the Delete button.
To move a cell with a value, select it and press the button with scissors (“cut”). Or press the combination CTRL+X. Appears around the cell dotted line. The selected fragment remains on the clipboard.
Place the cursor somewhere else in the work field and click “Insert” or the CTRL+V combination.
In the same way, you can move several cells at once. On the same sheet, on another sheet, in another book.
To move multiple cells, you need to select them:
- Place the cursor in the uppermost cell on the left.
- Press Shift, hold and use the arrows on the keyboard to select the entire range.
To select a column, click on its name (Latin letter). To highlight a line, use a number.
To change the size of rows or columns, move the borders (the cursor in this case takes the form of a cross, the crossbar of which has arrows at the ends).
To make the value fit in the cell, the column can be expanded automatically: click on the right border 2 times.
To make it more beautiful, let's move the border of column E a little, align the text in the center relative to the vertical and horizontal.
Let’s merge several cells: select them and click the “Merge and Place in Center” button.
Excel has an AutoFill feature. Enter the word “January” in cell A2. The program recognizes the date format and will fill in the remaining months automatically.
We grab the lower right corner of the cell with the value “January” and drag it along the line.
Let's test the autocomplete function on numeric values. We put “1” in cell A3, and “2” in A4. Select two cells, grab the autofill marker with the mouse and drag it down.
If we select only one cell with a number and drag it down, then this is the number “multiply”.
To copy a column to an adjacent one, select this column, “catch” the autofill marker and drag it to the side.
You can copy strings in the same way.
Let's delete a column: select it - right-click - "Delete". Or by pressing the hotkey combination: CTRL+"-"(minus).
To insert a column, select the one adjacent to the right (the column is always inserted on the left), right-click - “Insert” - “Column”. Combination: CTRL+SHIFT+"="
To insert a line, select the one adjacent to the bottom. Key combination: SHIFT+SPACEBAR to select a line and press the right mouse button - “Insert” - “Row” (CTRL+SHIFT+"=")(the line is always inserted from above).
How to work in Excel: formulas and functions for dummies
In order for the program to perceive the information entered into a cell as a formula, we put the “=” sign. For example, = (2+3)*5. After you press ENTER, Excel calculates the result.
The calculation sequence is the same as in mathematics.
The formula may contain not only numeric values, but also references to cells with values. For example, =(A1+B1)*5, where A1 and B1 are cell references.
To copy a formula to other cells, you need to “hook” the autofill marker in the cell with the formula and drag it down (to the side - if you are copying to row cells).
When copying a formula with relative references to Excel cells changes constants depending on the address of the current cell (column).
In each cell of column C, the second term in brackets is 3 (the reference to cell B1 is constant and unchanging).
Built-in functions significantly expand the functionality of the program. To insert a function, you need to press the fx button (or the SHIFT+F3 key combination). A window like this will open:
To avoid scrolling through a large list of functions, you must first select a category.
When the function is selected, click OK. The Function Arguments window opens.
The functions recognize both numeric values and cell references. To put a link in the argument field, you need to click on the cell.
Excel recognizes another way to enter a function. Place the “=” sign in the cell and begin entering the name of the function. After the first characters, a list of possible options will appear. If you hover your cursor over any of them, a tooltip will appear.
Double click on desired function– the order of filling in the arguments becomes available. To finish entering arguments, you need to close the parenthesis and press Enter.
ENTER - the program found the square root of the number 40.
The current situation is such that the software products of Microsoft Corporation are most widespread in Russia. Disregarding other aspects of this topic, it must nevertheless be said that this creates some kind of universality in their application, or even some kind of undeclared standard, because The software products of this corporation can now be found on almost any computer, and therefore compatibility will be ensured. In addition, Microsoft products are very versatile tools that can be used to solve many problems, and in addition, the degree of integration between products is very high, which also determines their popularity.
IN Microsoft package Office running operating system Microsoft Windows also includes the powerful spreadsheet development tool Microsoft Excel. (By the way, it must be said that although there are other means of developing spreadsheets and automating calculations, they, firstly, are extremely rare (say, in the internal use of a company for its specific needs, and secondly, how It has already been said that Microsoft products have become almost a de facto standard, which was facilitated by their great versatility.) Thus, it was Microsoft Excel that I chose to develop a tool for automating calculations in laboratory work"Precomputations in triangulation". Therefore, other tools for creating spreadsheets are not discussed here, but attention is paid to some specific Excel tools.
EXCEL's capabilities are very high. Text processing, database management - the program is so powerful that in many cases it is superior to specialized editor programs or database programs. Such a variety of functions may at first confuse you, rather than force you to use it in practice. But as you gain experience, you begin to appreciate the fact that the boundaries EXCEL capabilities hard to achieve. Over the 14-year history of tabular calculations using personal computers User requirements for such programs have changed significantly. In the beginning, the main emphasis in a program such as VisiCalc was on counting functions. Today the situation is different. Along with engineering and accounting calculations, the organization and graphical presentation of data is becoming increasingly important. In addition, the variety of functions offered by such a calculation and graphics program, should not complicate the user's work. Windows programs provide the ideal prerequisites for this. IN Lately many have just switched to using Windows as your user environment. As a result, many companies creating software, began to offer a large number of programs for Windows.
The Excel window contains many different elements. Some of them are inherent in all programs in the Windows environment, others are only in the Excel window. The entire workspace of the Excel window is occupied by a blank worksheet (or table), divided into separate cells. The columns are headed with letters, the rows with numbers. As with many Windows programs, you can present a worksheet as a separate window with its own title bar - this window is called a workbook window because it can handle multiple worksheets.
On one working page there will be 256 columns and 16384 rows. The rows are numbered from 1 to 16384, the columns are named by letters and combinations of letters. After the 26 letters of the alphabet, the columns are followed by combinations of letters from AA, AB, etc. In the Excel window, as in other Windows programs, there is a menu bar under the window title. Just below are the Standard and Formatting toolbars.
Toolbar buttons make it quick and easy to access many Excel functions. Design of worksheets. Font selection. You can change the font type, size or execution of text by selecting the corresponding cells and opening the Format menu. Selecting the Cells command from the Format menu. After this, a dialog will appear on the screen in which various fonts will be indicated.
You can select any font from the list of suggested ones. When you select a font, you can view its style in the example window. You can use the fields and buttons located on the toolbar to select the font type, size and style. Font types. Currently, a large number of fonts are used to design tables and documents. One of the most important factors that must be taken into account is the legibility of the text and the design in one or another font.
Along with choosing the font type and size, you can choose the font style: italic, bold or underlined. Use these styles only to highlight important information in the text of documents and tables.
Colors and patterns.
In Excel, you can highlight certain fields in a table using background color and pattern to draw attention to them. This selection must be used carefully so as not to overload the table. Select the View tab in the Format Cells dialog. Here you can select a fill color for the selected cells using the palette. Formatting numbers. If you want your records to turn into a convenient document, you should format the numbers in the cells. The easiest way to format cells is where monetary amounts are entered. To do this, you need to select the cells to be formatted. Then select the menu command Format - Cells, and in the dialog that appears, select the Number tab. Select the Cash line in the group on the left. Several possible number format options will appear on the right. The format of the number is determined by the type of digital template, which can be of two types: To better understand their purpose, consider the options for formatting the number 13. The first column contains format templates, as in the Format Codes field. In the second column you see what the number will look like as a result of formatting.
If a zero is used as a digital pattern, it will persist wherever it is not replaced by a significant digit. The number icon (shown as a hash mark) is missing in places where there are no significant digits. It is better to use a number pattern in the form of a zero for numbers after the decimal point, and in other cases use a hash mark. If you operate with numbers with more than two decimal places and the numbers in them are not equal to zero, then rounding up or down occurs. In the same way, Excel rounds fractional numbers that were formatted as integers, i.e. no decimal places. However, only numbers that are displayed on the screen are rounded; exact values are used in calculations. In the Format codes field, you can select the option of specifying amounts that go “minus”: along with the usual minus they can be displayed in red, which is often used when preparing accounting documentation.
Spellchecking.
Excel has a spell checker for text found in worksheet cells, charts, or text fields. To run it, you need to select cells or text fields in which you need to check spelling. If you want to check all the text, including the objects located in it, select the cell from which Excel should look for errors. Next you need to select the command Tools - Spelling. Then Excel will start checking the spelling in the text. You can start checking using the F7 key. If the program detects an error or does not find the word being checked in the dictionary, the Spell Check dialog will appear on the screen.
Operators.
All mathematical functions are described in programs using special symbols called operators. A complete list of operators is given in the table.
The text connection operator is designed so that when creating a sample document, you do not have to enter dates, for example, manually each time - the program itself will access the cell in which the date was entered.
Recalculating Worksheets.
By default, when you enter, edit formulas, or fill cells with formulas, all formula calculations in the worksheet occur automatically. However, with complex integration calculations this can take a long time, so you can cancel the automatic calculation. To do this, select the menu command Tools - Options, then in the Calculation tab that appears, select the Manual option and select the Recalculate before saving switch. After this, all calculations in the worksheet will occur only after pressing the Calculate key.
Excel functions.
The functions are designed to make it easier to create and interact with spreadsheets. The simplest example of performing calculations is the operation of addition. Let's use this operation to demonstrate the benefits of functions. Without using the function system, you will need to enter the address of each cell separately into the formula, adding a plus or minus sign to them. The resulting formula will look like this:
B1+B2+B3+C4+C5+D2
It is noticeable that it took a lot of time to write such a formula, so it seems that this formula would be easier to calculate by hand. To quickly and easily calculate a sum in Excel, you just need to use the sum function by clicking the button with the sum sign or from the Function Wizard, you can manually type the name of the function after the equal sign. After the name of the functions, you need to open a bracket, enter the addresses of the areas and close the bracket. The resulting formula will look like this:
SUM(B1:B3;C4:C5;D2)
If you compare the writing of the formulas, you can see that a colon here denotes a block of cells. Commas separate function arguments. Using blocks of cells, or areas, as arguments for functions is advisable because, firstly, it is more visual, and secondly, with such recording it is easier for the program to take into account changes on the worksheet. For example, you need to calculate the sum of the numbers in cells A1 to A4. This can be written like this:
SUM(A1,A2,A3,A4)
Or the same in another way:
SUM(A1:A4)
Creating diagrams.
Working with spreadsheets is a lot of fun in itself, but if only you could turn dry columns of numbers into visual charts and graphs. Excel provides this opportunity. Excel has two different ways storing in memory charts compiled from your numerical data: these are, firstly, “embedded” charts and, secondly, “chart pages”. Embedded charts are graphs superimposed on the working page and saved in the same file; new ones are created in chart pages graphic files. The easiest way to create an embedded chart is to use the Chart Wizard, which is part of Excel. Chart toolbar. You can create charts using more than just the Chart Wizard. You can also do this in another way - even faster - using the Chart toolbar. You can enable the image of this panel on the screen using the menu View - Toolbars. Example: Let's enter any data on the basis of which you can build a diagram. Select the data and click on the button with the downward arrow on the toolbar to open a list of chart types. Having selected the chart type and specified a rectangle of the required size in the worksheet, we launch the Chart Wizard. If you need to create a chart on a separate sheet, then you need to select the Chart line in the Create field. After a short dialogue with the Chart Wizard, a separate worksheet will be created.
Cake diagrams.
Typically, this visual representation of data is used when it is necessary to show the constituent shares as a percentage of the whole. You can create it on the screen, just like any other type of chart, using the Chart Wizard. Professional design.
For registration Excel documents offers, in addition to graphs and diagrams, the ability to create other graphic objects, for example, draw on the screen and then print rectangles, ellipses, straight and curved lines, arcs, etc. You can also make drawings using separate graphic objects, so that no one will suspect that they were made with using Excel, and not on purpose graphic editor. To create drawings, use the buttons located on the Drawing toolbar. You can enable the image of this panel on the screen using the button located on the Standard toolbar.
Data exchange.
In all programs written for the operating room Windows systems, the user can use its clipboard (Clipboard), it represents a special memory area made available by the operating environment various programs. Using a buffer, you can, for example, work in Excel, stop and almost instantly switch to another program that Windows keeps ready for you. Moreover, regardless current program the transition is carried out using the same command. To do this, you need to select the corresponding cells. Enter data into the buffer using the menu command Edit - Copy, or the key combination Ctrl+C. Now either Excel itself or another program can remove data from the buffer using the Edit - Paste menu command or one of two key combinations: Shift+Insert or Ctrl+V.
Hi all. This is the first article in a series about Microsoft Excel. Today you will learn:
- What is Microsoft Excel
- What is it for?
- What does his workspace look like?
In the next article we will discuss the program's working environment a little more. But we’ll be in the third post of this series, so the most interesting things are very close! In the future, we will focus on practice, since it is interesting to everyone.
To master this and subsequent articles in the series, you will need a computer with Microsoft Office, including Excel number. If you do not have this program yet, you can buy it, for example, here.
What is Excel and what to use it for
Microsoft Excel is a program with a tabular structure that allows you to organize data tables, systematize, process them, build graphs and diagrams, perform analytical tasks and much more. Of course, this is not the entire list of capabilities, as you will soon see as you study the course materials. The program can perform many useful operations for you, which is why it has become a worldwide hit in its industry.Excel workspace
Workspace Excel is called a workbook, which consists of worksheets. That is, one workbook file can contain one or more tables called Sheets. Each sheet consists of many cells that form a data table. The rows are numbered sequentially from 1 to 1,048,576. The columns are named with the letters A through XFD. Cells and coordinates in Excel In fact, these cells can store a huge amount of information, much more than your computer can process. Each cell has its own coordinates. For example, the cell at the intersection of the 3rd row and 2nd column has coordinates B3 (see figure). The cell coordinates are always highlighted in color on the sheet; look in the picture at what the number of the third line and the letter of the second column look like - they are darkened.By the way, you can place data in any order on the sheet; the program does not limit your freedom of action. This means you can easily create various reports, forms, layouts and choose the optimal place for.
Now let's take a look at the Excel window as a whole and understand the purpose of some of its elements:
Excel workspaceThis concludes our first lesson. We looked at the purpose of the Excel program and the main (not all) elements of its worksheet. In the next lesson we will look at. Thank you for reading this article to the end, keep it up! If you have any questions, write in the comments, I will try to answer everything. Microsoft Office Excel is a spreadsheet program that allows you to store, organize and analyze information. You may have the impression that Excel is only used by a certain group of people to perform complex tasks. But you are wrong! In fact, anyone can master this excellent program and use all its power to solve exclusively their everyday problems.
Excel is a universal program that allows you to work with various data formats. In Excel, you can maintain a home budget, make both simple and very complex calculations, store data, organize various diaries, draw up reports, build graphs, diagrams and much, much more.
Excel program is part of the Microsoft Office suite, which consists of a whole set of products that allow you to create various documents, spreadsheets, presentations and much more.
In addition to Microsoft Excel, there are a number of other similar programs, which are also based on working with spreadsheets, but Excel is by far the most popular and powerful of them, and is rightfully considered the flagship of this direction. I dare say that Excel is one of the most popular programs at all.
What can I do in Excel?
Microsoft Excel has many advantages, but the most significant is, of course, its versatility. The uses for Excel are almost endless, so the more knowledge you have about the program, the more uses you can find for it. The following are possible areas of application for Microsoft Office Excel.
- Working with Numeric Data. For example, drawing up a wide variety of budgets, ranging from home budgets, as the simplest, to the budget of a large organization.
- Work with text. A diverse set of tools for working with text data makes it possible to present even the most complex text reports.
- Creating graphs and charts. A large number of tools allow you to create a wide variety of chart options, which makes it possible to present your data in the most vivid and expressive way.
- Creating diagrams and drawings. In addition to graphs and charts, Excel allows you to insert many different shapes and SmartArt graphics into your worksheet. These tools significantly increase the data visualization capabilities of the program.
- Organizing Lists and Databases. Microsoft Office Excel was originally designed with a structure of rows and columns, so organizing work with lists or creating a database is an elementary task for Excel.
- Data import and export.Excel allows you to exchange data with a wide variety of sources, which makes working with the program even more universal.
- Automation of similar tasks. Using macros in Excel allows you to automate the execution of the same type of time-consuming tasks and reduce human participation to a single mouse click to run the macro.
- Creating Control Panels. In Excel, you can place controls directly on the worksheet, which allows you to create visual, interactive documents.
- Built-in programming language. Built in Microsoft application Excel language Visual programming Basic for Applications (VBA) allows you to expand the program's capabilities at least several times. Knowledge of the language opens up completely new horizons for you, for example, creating your own custom functions or entire add-ons.
The features of the Excel application can be listed for a very long time; above I presented only the most basic of them. But it is already clear to see how useful knowledge of this program will be for you.
Who is Excel made for?
Initially, Excel was created exclusively for office work, since only an organization could afford such a luxury as a computer. Over time, computers began to appear more and more in the homes of ordinary people, and the number of users gradually increased. At the moment, almost every family has a computer and most of them have Microsoft Office installed.
There are hundreds of companies in Russia offering Microsoft Office courses. Excel is taught in educational institutions; hundreds of books and training courses have been published on Excel. Knowledge of Office is required when applying for a job or this knowledge is counted as an additional advantage. All this suggests that knowledge office programs, in particular Excel, will be useful to everyone without exception.
