Introduction
1: Microsoft Excel
1.1 Concept and capabilities of MS Excel
1.2 Basic elements of the MS Excel window
1.4 Possible errors when using functions in formulas
2: Data analysis. Using Scripts
2.1 Data analysis in MS Excel
2.2 Scenarios
2.3 Example of calculating the internal investment turnover rate
Conclusion
Bibliography
Introduction
Microsoft Office, the most popular family of office software products, includes new versions of familiar applications that support Internet technologies, allowing you to create flexible Internet solutions
Microsoft Office is a family of Microsoft software products that combines the world's most popular applications into a single environment ideal for working with information. Microsoft Office includes the Microsoft Word word processor, Microsoft Excel spreadsheets, Microsoft PowerPoint presentation tool, and the new Microsoft Outlook application. All of these applications make up the Standard Edition of Microsoft Office. The Professional edition also includes the Microsoft Access DBMS.
MicrosoftExcel– a program designed to organize data in a table for documenting and graphically presenting information.
The MSExcel program is used to create complex documents in which it is necessary:
· use the same data in different worksheets;
· change and restore connections.
The advantage of MSExcel is that the program helps to operate with large volumes of information. MSExcel workbooks provide the ability to store and organize data, and calculate the sum of values in cells. MsExcel provides a wide range of methods to make information easy to understand.
Nowadays, it is important for every person to know and have skills in working with Microsoft Office applications, since the modern world is saturated with a huge amount of information that you simply need to be able to work with.
This coursework will present the MSExcel application, its functions and capabilities in more detail. As well as the use of scenarios with their practical application.
1. MicrosoftExcel
1.1 . MicrosoftExcel. Concepts and features
The spreadsheet processor MS Excel (spreadsheets) is one of the most frequently used applications in the MS Office suite, a powerful tool in capable hands that greatly simplifies routine daily work. The main purpose of MS Excel is to solve almost any calculation problems, the input data of which can be presented in the form of tables. The use of spreadsheets simplifies working with data and allows you to obtain results without programming calculations. In combination with the Visual Basic for Application (VBA) programming language, the MS Excel spreadsheet processor becomes universal and allows you to solve any problem, regardless of its nature.
A special feature of spreadsheets is the ability to use formulas to describe the relationship between the values of different cells. Calculation using specified formulas is performed automatically. Changing the contents of a cell leads to the recalculation of the values of all cells that are connected to it by formula relations and, thereby, to updating the entire table in accordance with the changed data.
Main features of spreadsheets:
1. carrying out complex calculations of the same type over large data sets;
2. automation of final calculations;
3. solving problems by selecting parameter values;
4. processing (statistical analysis) of experimental results;
5. searching for optimal parameter values (solving optimization problems);
6. preparation of tabular documents;
7. constructing diagrams (including summary ones) based on available data;
8. creation and analysis of databases (lists).
1.2. Basic elements of the MS Excel window
The main elements of the working window are:
1. Title bar (it indicates the name of the program) with buttons for controlling the program window and document window (Collapse, Minimize to window or Maximize to full screen, Close);
2. Main menu bar (each menu item is a set of commands united by a common functional focus) plus a window for searching for help information.
3. Toolbars (Standard, Formatting, etc.).
4. The formula bar, containing as elements the Name field and the Insert Function (fx) button, is intended for entering and editing values or formulas in cells. The Name field displays the address of the current cell.
5. Work area (active worksheet).
6. Scroll bars (vertical and horizontal).
7. A set of shortcuts (sheet shortcuts) for moving between worksheets.
8. Status bar.
1.3 Spreadsheet structure
A file created using MS Excel is commonly called a workbook. You can create as many workbooks as the availability of free memory on the appropriate memory device allows. You can open as many workbooks as you have created. However, only one current (open) workbook can be an active workbook.
A workbook is a collection of worksheets, each of which has a tabular structure. The document window displays only the current (active) worksheet with which you are working. Each worksheet has a title, which appears on the worksheet tab at the bottom of the window. Using shortcuts, you can switch to other worksheets included in the same workbook. To rename a worksheet, you need to double-click on its tab and replace the old name with a new one or by executing the following commands: Format menu, Sheet line in the menu list, Rename. Or you can, by placing the mouse pointer on the active worksheet shortcut, right-click, then in the context menu that appears, click on the Rename line and perform the renaming. You can add (insert) new sheets to the workbook or delete unnecessary ones. Inserting a sheet can be done by executing the Insert menu command, line Sheet in the list of menu items. The sheet will be inserted before the active sheet. The above actions can also be performed using the context menu, which is activated by clicking the right mouse button, the pointer of which should be placed on the tab of the corresponding sheet. To swap worksheets, you need to place the mouse pointer on the tab of the sheet being moved, press the left mouse button and drag the tab to the desired location.
A worksheet (table) consists of rows and columns. The columns are headed with capital Latin letters and, further, with two-letter combinations. The worksheet contains a total of 256 columns, named A through IV. The lines are numbered sequentially from 1 to 65536.
Table cells are formed at the intersection of columns and rows. They are the minimum elements designed to store data. Each cell has its own address. The cell address consists of the column name and row number at the intersection of which the cell is located, for example, A1, B5, DE324. Cell addresses are used when writing formulas that define the relationship between values located in different cells. At the current time, only one cell can be active, which is activated by clicking on it and highlighted with a frame. This frame acts as a cursor in Excel. Data entry and editing operations are always performed only in the active cell.
Data located in adjacent cells that form a rectangular area can be referenced as a single unit in formulas. A group of cells bounded by a rectangular area is called a range. The most commonly used rectangular ranges are formed at the intersection of a group of sequential rows and a group of sequential columns. A range of cells is indicated by specifying the address of the first cell and the address of the last cell in the range, separated by a colon, for example, B5:F15. Selecting a range of cells can be done by dragging the mouse pointer from one corner cell to the opposite cell diagonally. The frame of the current (active) cell expands, covering the entire selected range.
To speed up and simplify computational work, Excel puts at the user's disposal a powerful apparatus of worksheet functions that allow almost all possible calculations to be carried out.
In total, MS Excel contains more than 400 worksheet functions (built-in functions). All of them, according to their purpose, are divided into 11 groups (categories):
1. financial functions;
2. date and time functions;
3. arithmetic and trigonometric (mathematical) functions;
4. statistical functions;
5. functions of links and substitutions;
6. database functions (list analysis);
7. text functions;
8. logical functions;
9. information functions (checking properties and values);
10.engineering functions;
11.external functions.
Writing any function into a worksheet cell must begin with the equals symbol (=). If a function is used as part of any other complex function or in a formula (megaformula), then the equal symbol (=) is written before this function (formula). Any function is accessed by specifying its name followed by an argument (parameter) or a list of parameters in parentheses. The presence of parentheses is required; they serve as a sign that the name used is the name of a function. List parameters (function arguments) are separated by semicolons (;). Their number should not exceed 30, and the length of a formula containing as many calls to functions as desired should not exceed 1024 characters. When writing (entering) a formula, it is recommended to type all names in lowercase letters, then correctly entered names will be displayed in capital letters.
1.4 Possible errors when using functions in formulas
Microsoft Excel is a component of Microsoft Office. Excel was introduced in 1987. A spreadsheet is one of the most convenient applications for processing data and presenting it in tabular form. Programs that process spreadsheets are called spreadsheet processors. Excel allows you to analyze data using charts, create document forms, and make calculations using formulas. For people whose professional activities involve numbers, drawing up reports and forms, and analyzing these results, the program is an indispensable assistant. With its help, they can keep a log of business transactions, issue an invoice, payroll, staffing table or invoice, and perform any other tasks related to systematization and organization of information. The name of the program is short for the English word Excellent - which means “excellent”.
The Excel menu consists of:
2. Editing - can be used when editing a document. It contains the commands Undo, Cut, Copy, Paste, Find, Fill, Go. Selecting the Undo command allows you to discard the formatting, copying or deleting that was just performed, i.e. any action the result of which you are not happy with. The Cut, Copy and Paste commands can be used to fill cells with data, and the Go To command is used to move to a cell with a specific address.
3. The Insert menu item contains the commands Rows, Sheet, Chart, Functions, Note. As the name suggests, you can use the commands on this menu to add rows, sheets, and charts, as well as to insert functions into your document when creating formulas and notes.
4. The Format menu item contains commands that can be used when formatting cells.
5. The Data menu item is used when working with databases. The Sorting, Filter, Pivot Table commands allow you to sort database tables, select data from them according to certain criteria, and create pivot tables.
6. The Service menu item is used to configure the program.
In Excel, row and column numbers are called headings, and cell numbers are called cell names. The cell in which the user is working is called active and is highlighted with a bold frame. The name of the active cell appears at the top left of the Excel window, called the name field. If we want to get a product in any cell (for example, calculating the cost of some parts quantity * price) - we do it as follows: activate this cell (where the result will be) → Standard toolbar → Insert functions → Function Wizard dialog box in On the left side of the window, in the category section, select Mathematical functions and look for PRODUCT. After selecting the desired function and clicking OK, the second window of the Function Wizard opens - in which you need to specify the product of which two (or more) cells we need. Cells are listed using: (colon). Next, the formula needs to be set for the entire column, for this we use Autocomplete. The active cell is marked with a bold rectangle, and there is a bold dot in the lower right corner. This point is called autocomplete marker. If you place the mouse pointer on this marker, it will turn into a black cross. We make active a cell in which there is already a formula, move the pointer to the autofill marker, click the left button and, without releasing it, “stretch” the frame of the active cell down to the last line we filled in. We release the mouse button and see that the column is filled with data. Now let's make cell G 19 active, click on the button Σ Autosum, which is located on the Standard toolbar to the left of the Insert Functions button. To select cells, click the left button and, without releasing, “stretch” the cell to the last row we need. In the formula bar and in cell G 19 itself you can see the formula (SUM(G 7:G 17)), press Enter and the result is obtained in the cell. Excel allows you to specify what kind of data is stored in cells, this is called number formats. The set of all cell settings is called cell format.
Menu Format, select the item Cells, in the dialog box on the first tab Number In the Number formats field, select Currency, you can specify how many decimal places we want to see. After selecting the format OK. After this, the PRICE and AMOUNT numbers will be displayed in monetary terms. The table is ready, but when printed, the cell borders will not be displayed. If we want to show these borders, we need to use framing tools. To do this, use the button Borders. After framing and filling, the table will look the same in printed form as it does on the monitor screen.
Selecting a column/row. To select a column or row, just click on the heading of the desired column or row. If you need to select several consecutive columns or rows, then you need to click on the heading of the first desired column/row and, without releasing the button, drag the pointer over the headings that we need. If some of the selected rows or columns did not need to be selected, you can selectively deselect (or select) using the Ctrl key.
Changing column/row width/height. The change is made by the pointer when it accepts a double-headed arrow.
Inserting rows/columns/cells. If during work there is a need to insert rows or columns into the table, we will do it as follows:
¯ select the row above which you want to add a new one in the table
¯ right-click on the selection and in the context menu that opens, select Add Cells.
Delete rows/columns/cells. To delete rows or columns, you must first select them. You can delete either a single row or a group of rows or columns. Select by clicking the left mouse button, “stretch” until we mark everything that needs to be deleted, press the right mouse button and select the item in the context menu Delete. Select the deletion method in the window (shift left or up).
Excel sheets. Excel documents are not the single table we've worked with so far, but a workbook that contains three such tables by default. These tables are called sheets. It's easy to move from sheet to sheet; there are sheet shortcuts in the lower left corner. When working with complex documents, sheets are very convenient, since from any sheet you can make links to cells of other sheets and use data from several sheets when creating formulas.
Sheet formatting. You can change the appearance of the data you enter. Changing the appearance of a document is called formatting the sheet or cells. For formatting, use the panel located under the title of the program window, or the Format→Cells command from the menu bar. Cells must first be selected. You can also use the Format Cells context menu command to open the Format Cells dialog box.
Sample format. To copy the cell format from one sheet to another, use the button Sample format. The point of copying a format is to transfer only the format of the selected cell to another cell, leaving its contents the same. The same is true for a group of cells.
1. Select a fragment formatted appropriately and click the Format As Sample button. A brush icon will appear next to the mouse pointer.
2. Select the cell or range to which you want to apply the selected format.
3. Release the mouse button. The selected cells will be formatted as expected.
Exercise 1.
In cells A1-A3, enter the number 20 and assign the cells the formats General, Cash, Interest
2000%, center the numbers
Exercise 2.
Open Sheet 2, in cell with address A4 enter the value Departure, in cell A5 the value Arrival. To make the data in cells look beautiful, you can use the Format→Column→Auto-Fit Width command. In cells B4 and B5, indicate the dates of departure and return from the business trip. Let it be 01/10/2004 and 01/16/2004. In the cell with the address B6, enter the formula for calculating the days of stay on a business trip: = B5 - B4.
Building charts. A diagram is one way of presenting data. The point of diagrams is to more clearly present the information contained in the table. Information presented in graphical form is much easier to perceive in pictures than in text or tabular form. How to create a chart in Excel - you need to select the data for the chart and launch the Chart Wizard. QC and all other Microsoft Office Wizards, this wizard will ask you a series of questions to determine the parameters of the object being created, and then creates it in accordance with the instructions received. Chart Wizard on the toolbar, or the menu command Insert→Diagram. In Excel you can create 3D and 2D charts. By default, the diagram is built over the entire selected area. If there is text in the top line and left column, the program automatically generates a legend based on them. The legend is a description of the symbols used in this diagram. After creation, the diagram can be edited: change the colors of the lines and columns, and the font of the inscriptions on the diagram. To do this, you need to switch to the diagram editing mode by double-clicking on one of the diagram elements.
Exercise 3.
Sale of goods.
A1 - title of the diagram - by warehouse
A3 - wholesale warehouse
A4 - general warehouse
Q2 - 2 - Quarter 1 - Quarter 3. - use the auto-fill option.
Fill the table with data, select cells and launch the Chart Wizard. To analyze quarterly sales, it is convenient to use a pie chart.
To analyze sales for a wholesale warehouse, mark cells A2:E2
For general warehouse A3:E3
Examples of using Excel
Registration of the transaction log
Filling out A1 - transaction log
B2 - Business transaction
C2 - Debit
D2 - Credit
E2 - Amount
Resize cell B2 so that the text is fully visible. Select cells A2-E2 and format them using bold style, font size 14. Align the cells to the center, highlight them with color (use the formatting toolbar buttons). Select cells A1-E1 and click on the button Combine and place in the center on the formatting toolbar. The title should be in the center of the magazine. Fill the log with data. If a business transaction spans several lines, select Format→Cells→Alignment tab and check the Wrap by words box (there are three boxes, check the box you want). The numbers can be aligned to the center of the cell; to do this, click on the Center button, having first selected the cells, starting with E3. Format these cells by selecting the Currency format. To do this, Format→Cells→Number, set the format to Currency in the Number Formats field and enter the value 2 in the Number of Decimal Places field. Similarly, the Excel program can be used to prepare payroll statements, invoices, and balance sheets. Of course, we are talking about using Excel for these forms if the enterprise is small and the reporting on it is not too cumbersome.
Microsoft Excel (also sometimes called Microsoft Office Excel) is a spreadsheet program created by Microsoft Corporation. It provides economic and statistical calculations and graphical tools. Microsoft Excel is part of Microsoft Office and today Excel is one of the most popular applications in the world.
Application areas of spreadsheets:
accounting and banking;
resource allocation planning;
design and estimate work;
engineering and technical calculations;
processing large amounts of information;
study of dynamic processes.
Main features of spreadsheets:
analysis and modeling based on calculations and data processing;
design of tables and reports;
formatting the data contained in the table;
constructing diagrams of the required type;
creating and maintaining databases with the ability to select records by a given criterion and sort by any parameter;
transferring (inserting) information into a table from documents created in other applications running in the Windows environment;
Printing the final document in whole or in part.
Advantages of using ET when solving problems:
Solving problems using spreadsheets frees you from creating an algorithm and debugging a program. You just need to write down the initial data and mathematical relationships included in the model in a certain way in the table.
When using formulas of the same type, there is no need to enter them multiple times; you can copy the formula to the desired cell. This will automatically recalculate the relative addresses found in the formula. If it is necessary that when copying a formula, the reference to a certain cell does not change, then it is possible to specify an absolute (unchangeable) cell address.
Practical lesson No. 1 Analysis of data series. Calculation of the trend line for cases of single-factor and multi-factor dependencies.
Statement of the problem: it is necessary to analyze the presented data series and find statistical dependencies between them.
Initial data:
|
Month |
Freight rate, $/day |
Demand, million dwt |
Supply, million dwt |
A. Find the equation for the one-factor dependence of changes in the freight rate over time by constructing a trend line.
1. For analysis, from the presented data, take the columns “Month” and “Freight Rate”. Construct a graph, presenting the data in the form scatter plot. At the same time, along the axis X the month is located along the axis at – freight rate (Fig. 1).
2. Add a trend line to the chart (Fig. 2-4):
Right-click to select the graph points and select “Add trend line” in the menu that appears (select linear trend line type);
In the same menu, in the last tab, select the items “Show the equation on the diagram” and “Place the approximation reliability value on the diagram.”
The trend line that appears on the chart can be edited by right-clicking the menu.
The type of trend line is selected depending on the analyzed data, according to the approximation reliability indicator R 2 . The closer R2 is to one, the better the trend line describes the data.
3. Add an additional column “Theoretical freight rate 1” to the table. Using the linear trend formula presented in the graph ( y=70.536x+1855), calculate the theoretical values of the freight rate.
B. Find an equation for the multifactorial dependence of the freight rate on supply and demand for tonnage by performing regression analysis.
1. In the “Service” menu, select “Data Analysis”. In the menu that appears, select the “Regression” analysis tool.
2. In the menu that opens, enter in the active lines of input intervals: input interval at– the entire set of actual values of the freight rate; input interval X– the entire set of indicators on which dependence is sought (namely, supply and demand) (Fig. 6).
3. The result of regression analysis is presented in Fig. 7. For the regression equation, the coefficients from the lower table are used.
The regression equation for this example is:
|
Y-intersection | ||
|
Variable X 1 | ||
|
Variable X 2 |
Thus,
4. We create another column in the table “Theoretical freight rate 2”. In the column, using the resulting regression equation, we calculate the values of the freight rate.
The result of the calculations is depicted graphically (Fig. 9).
Microsoft Excel is a program for performing calculations and managing so-called spreadsheets.
Excel allows you to perform complex calculations that can use data located in different areas of the spreadsheet and linked together by a certain dependency. To perform such calculations in Excel, it is possible to enter various formulas into table cells. Excel performs the calculation and displays the result in the formula cell. The available formulas range from simple addition and subtraction to financial and statistical calculations.
An important feature of using a spreadsheet is the automatic recalculation of results when cell values change. For example, you can use Excel to perform financial calculations, record and control the organization's personnel, etc. Excel can also build and update graphs based on entered numbers.
The file that Excel works with is called a book . A book, as a rule, consists of several worksheets that may contain tables, texts, diagrams, and drawings.
The book is a good organizational tool. For example, you can collect all documents in one book ( worksheets), related to a specific project (task), or all documents maintained by one executor. The base of the worksheet (Fig. 1) is a grid of rows and columns. Cell formed by the intersection of a row and a column. The cell(s) selected by the mouse is called active (in Fig. 1 the active cell is highlighted with a frame).
A row in a worksheet is identified by a name (number) that appears on the left side of the worksheet.
A column in a worksheet is also identified by a name (letters of the Latin alphabet) that appears at the top of the worksheet.
An Excel worksheet can contain up to 65,536 rows and 256 columns. A cell - the main element of a table - has its own unique address, consisting of a column and row number, for example E4 .
Each cell contains one piece of information, be it a numeric value, text, or a formula.
When you open a previously created file, a workbook with the entered data appears in the Excel window.
Workbook in Excel, it is a file in which data is stored and analyzed. A workbook file consists of several worksheets, which can contain tables, text, charts, or pictures. Each worksheet is identified by a name that appears on the worksheet label.
Let's look at specific elements of the Excel window (Fig. 2).
| Rice. 2. Excel window |
Status bar contains information about the active document, the selected menu command, and keyboard mode indicators. In it, the user receives messages on how to complete the started command and view the intermediate results of some calculations.
Formula bar shows the formula (if present in the cell) or data contained in the active cell. You can enter and edit text, numbers, and formulas in the formula bar.
IN active cell You can enter and edit data directly in a cell or in the formula bar.
Scroll buttons The windows at the bottom left are used to view sheet shortcuts and to move between sheets in a workbook containing a large number of sheets.
“Microsoft Excel (also sometimes called Microsoft Office Excel) is a spreadsheet program created by Microsoft for Microsoft Windows, Windows NT and Mac OS. It provides economic-statistical capabilities, graphical tools, and, with the exception of Excel 2008 on Mac OS X, the macro programming language VBA (Visual Basic for Application). Microsoft Excel is part of Microsoft Office and today Excel is one of the most popular applications in the world.” Source: Wikipedia.org
Microsoft Excel capabilities?
The areas and possibilities for using Excel are varied:
As can be seen from all of the above, the main focus of Excel is working with financial and analytical data, so ownership of this application is in demand in many types of activities related to calculations, reports, forecasting, etc. And an employee with in-depth knowledge is a gift
That's all for now! In the next lesson we will get acquainted with the MS Excel 2003 interface.
For those who want to delve into the history of the development of MS Excel, you can take a look at.
Choice
