Microsoft Excel- one of the most popular programs for working with large amounts of data, which is essentially an editor spreadsheets... The wide functionality of the application does not limit the user to the ability to only edit already created tables, but allows you to create completely new ones, with the introduction of your own data.
The many mathematical, statistical and other functions built into Excel allow you to generate reports and carry out even the most complex calculations in one click, and a wide library of templates and a charting wizard will easily present all information in a graphical form, with emphasis and highlight the necessary connections and trends. That is why, if you often have to deal with large amounts of information, carry out calculations, draw up reports and carry out other similar work, you can download Microsoft Excel for free right now and see from your own experience all the advantages of working with this software product.
A wide and varied toolkit makes Excel indispensable for people working in the field of economics, finance and accounting. By compiling tables and hierarchical relationships between them, you can not only store information, but also instantly access the necessary data, using the ability to sort and filter by various criteria.
Microsoft Excel features
- Compilation of tables with different types data (numeric, text, date, money, time, etc.).
- Extensive formatting options for both the entire sheet and each cell separately.
- Import and export data among other Microsoft Office applications.
- Automatic and customizable sorting of information according to various criteria.
- Built-in data search.
- Usage graphic objects, including charts, graphs and histograms of all kinds.
- High level of information protection.
- Collaboration on projects.
One of the most important advantages of Microsoft Excel is that every user can download free version program and independently evaluate all its advantages. It is worth noting that each new issue Excel contains updates both in appearance and functional content. That is why, before you decide to install a specific version on your computer, be sure to check out system requirements which are presented in the description of each issue.
Hello everyone. This is the first article in a series about Microsoft Excel. Today you will learn:
- What is Microsoft Excel
- What is it for
- What his workspace looks like
In the next article, we will discuss a little more of the program's working environment. But in the third post of this series we will already, so the most interesting is very close! In the future, we will focus on practice, since it is interesting for everyone.
To master this and subsequent articles in the series, you will need a computer with Microsoft Office, including Excel. If you do not have this program yet, you can buy it, for example, here.
What is Excel, what to use it for
Microsoft Excel is a tabular program that allows you to organize data tables, organize, process them, build graphs and charts, perform analytical tasks and much more. Of course, this is not the whole list of possibilities, which you will soon see as you study the course materials. The program is able to do many useful operations for you, which is why it has become a worldwide hit in its industry.Excel workspace
An Excel workspace is called a workbook, which consists of worksheets. That is, one book file can contain one or more tables called Sheets. Each sheet consists of many cells that form a data table. Rows are numbered in order from 1 to 1,048,576. Columns are named by 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, a cell not at the intersection of the 3rd row and the 2nd column has coordinates B3 (see figure). The coordinates of the cell are always highlighted on the sheet in color, look in the figure how 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 that you can easily create various, reports, forms, layouts and, choose the best 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 workspace This concludes our first lesson. We have covered the purpose of Excel and the main (not all) elements of its worksheet. In the next lesson we will look at. Thanks 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 might be under the impression that Excel is only used by a specific group of people to perform some complex task. But you are wrong! In fact, anyone can master this excellent program and use all its power to solve exclusively their everyday tasks.
Excel Is a versatile program that allows you to work with various data formats. In Excel, you can keep a home budget, make both simple and very complex calculations, store data, organize various diaries, draw up reports, build graphs, charts and much, much more.
Excel program is part of the Microsoft Office suite, which consists of a set of products that allow you to create various documents, spreadsheets, presentations, and more.
In addition to Microsoft programs Excel there are a number of other similar programs, which also basically use 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 in general.
What can I do in Excel?
Microsoft Excel has many advantages, but the most significant is, of course, its versatility. Variants Excel applications are almost limitless, therefore, the more knowledge you have on this program, the more you can find applications for it. Possible applications for Microsoft Office Excel are listed below.
- Working with numeric data... For example, drawing up a wide variety of budgets, ranging from home, as the simplest, and ending with 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.
- Creation of 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 greatly enhance the data visualization capabilities of the program.
- Organizing lists and databases... The structure of rows and columns was originally laid in Microsoft Office Excel, 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 the program even more versatile.
- 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 click of the mouse to run a macro.
- Creation of control panels... In Excel, it is possible to place controls directly on the sheet, which allows you to create visual interactive documents.
- Built-in programming language... The Visual Basic for Applications (VBA) programming language built into Microsoft Excel 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.
Possibilities Excel applications can be enumerated for a very long time, above I have presented only the most basic of them. But it is already clear now how much knowledge of this program will be useful for you.
Who is Excel made for?
Initially Excel program 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 courses on Microsoft Office. 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 additional advantage... All this suggests that knowledge office programs, in particular Excel, will be useful to everyone without exception.
If new data appears on the sheet for the plotted diagram, which needs to be added, then you can simply select a range with new information, copy it (Ctrl + C) and then paste it directly into the diagram (Ctrl + V).
Suppose you have a list of full names (Ivanov Ivan Ivanovich), which you need to turn into abbreviated names (Ivanov I.I.). To do this, you just need to start writing the desired text in the adjacent column by hand. On the second or third Excel row will try to predict our actions and perform further processing automatically. All you have to do is press the Enter key to confirm, and all names will be converted instantly. Similarly, you can extract names from email, glue names from fragments, and so on.
You are most likely aware of the magic autocomplete marker. This is a thin black cross in the lower right corner of the cell, pulling on which you can copy the contents of a cell or a formula to several cells at once. However, there is one unpleasant nuance: such copying often violates the design of the table, since not only the formula is copied, but also the cell format. This can be avoided. Immediately after pulling the black cross, click on the smart tag - a special icon that appears in the lower right corner of the copied area.
If you select the Fill Without Formatting option, Excel will copy your formula without formatting and will not spoil the layout.
In Excel, you can quickly display your geodata on an interactive map, such as sales by city. To do this, go to the Office Store on the Insert tab and install the Bing Maps plugin from there. This can be done from the site by clicking the Get It Now button.
After adding a module, you can select it from the My Apps drop-down list on the Insert tab and place it on your worksheet. It remains to select your cells with data and click on the Show Locations button in the map module to see our data on it. If desired, in the plugin settings, you can select the type of chart and colors to display.
If the number of worksheets in the file has exceeded 10, then it becomes difficult to navigate them. Right-click on any of the sheet tabs scroll buttons in the lower left corner of the screen. A table of contents will appear, and you can jump to any desired sheet instantly.
If you have ever had to move cells from rows to columns by hand, then you will appreciate the following trick:
- Highlight the range.
- Copy it (Ctrl + C) or, by clicking on the right mouse button, select "Copy" (Copy).
- Right-click the cell where you want to paste the data and select one of the Paste Special options from the context menu - the Transpose icon. Older versions of Excel do not have this icon, but you can fix the problem by using Paste Special (Ctrl + Alt + V) and choosing the Transpose option.
If in any cell it is supposed to enter strictly defined values from the allowed set (for example, only "yes" and "no" or only from the list of company departments, and so on), then this can be easily organized using the drop-down list.
- Select a cell (or a range of cells) that should have such a limitation.
- Click the "Validation" button on the "Data" tab (Data → Validation).
- In the drop-down list "Type" (Allow) select the option "List" (List).
- In the “Source” field, specify the range containing the reference variants of the elements, which will subsequently drop out when typing.
If you select a range with data and on the "Home" tab click "Format as a table" (Home → Format as Table), then our list will be converted into a smart table that can do a lot of useful things:
- Automatically stretches when new rows or columns are added to it.
- The entered formulas will be automatically copied to the entire column.
- The header of such a table is automatically fixed when scrolling, and it includes filter buttons for filtering and sorting.
- On the appeared tab "Design" in such a table, you can add a row of totals with automatic calculation.
Sparklines are miniature charts drawn right in cells that visualize the dynamics of our data. To create them, click the Line or Columns button in the Sparklines group on the Insert tab. In the window that opens, specify the range with the original numerical data and the cells where you want to display the sparklines.
After clicking on the "OK" button, Microsoft Excel will create them in the specified cells. On the "Design" tab that appears, you can further customize their color, type, enable the display of minimum and maximum values, and so on.
Imagine: you close the report you were messing with for the last half of the day, and in the dialog box that appears "Save changes to file?" suddenly for some reason you press "No". The office announces your heart-rending cry, but it's too late: the last few hours of work went down the drain.
In fact, there is a chance to rectify the situation. If you have Excel 2010, then click on "File" → "Recent" (File → Recent) and find the button "Recover Unsaved Workbooks" in the lower right corner of the screen.
In Excel 2013, the path is slightly different: File → Info → Version Control → File - Properties - Recover Unsaved Workbooks.
In subsequent versions of Excel, open File → Info → Workbook Management.
Will open special folder from the depths of Microsoft Office, where temporary copies of all created or modified, but unsaved books are saved in such a case.
Sometimes when working in Excel, you need to compare two lists and quickly find items that are the same or different in them. Here's the quickest, most intuitive way to do it:
- Select both compared columns (hold down the Ctrl key).
- Select Home tab → Conditional Formatting → Highlight Cell Rules → Duplicate Values.
- Select the Unique option from the dropdown list.
Ever tinker with the input values in your Excel calculation to get the output you want? At such moments you feel like a seasoned artilleryman: just a couple of dozen iterations of "undershoot - overflights" - and here it is, the long-awaited hit!
Microsoft Excel can do this for you, faster and more accurately. To do this, click the "What If" button on the "Data" tab and select the "Parameter selection" command (Insert → What If Analysis → Goal Seek). In the window that appears, specify the cell where you want to select the desired value, the desired result and the input cell that should be changed. After clicking on "OK" Excel will execute up to 100 "shots" in order to find the required total with an accuracy of 0.001.
Introduction
1: MicrosoftExcel
1.1 Concept and capabilities of MS Excel
1.2 Main elements of the MS Excel window
1.4 Possible mistakes when using functions in formulas
2: Data analysis. Using scripts
2.1 Data analysis in MS Excel
2.2 Scripts
2.3 An example of calculating the internal rate of investment turnover
Conclusion
Bibliography
Introduction
Microsoft Office, the most popular family of office software products, includes new versions of familiar applications that support Internet technologies and enable flexible Internet solutions
Microsoft Office is a family of Microsoft software products that brings together the world's most popular applications into a single environment, ideal for working with information. Microsoft Office includes word processor Microsoft Word, Microsoft Excel Spreadsheets, Microsoft PowerPoint Presentation and Presentation Tool, and New Application Microsoft Outlook... All of these applications make up the Standard Edition of Microsoft Office. The Professional Edition also includes Microsoft Access.
MicrosoftExcel- a program designed to organize data in a table for documentation and graphical presentation of 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 links.
The advantage of MSExcel is that the program helps to operate with large amounts of information. MSExcel workbooks provide the ability to store and organize data, calculate the sum of values in cells. MsExcel provides a wide range of methods to make information easy to read.
Nowadays, it is important for every person to know and have skills in working with Microsoft Office applications, since modern world saturated with a huge amount of information, with which you just need to be able to work.
In more detail in this course will present the MSExcel application, its functions and capabilities. And also the use of scripts with their practical application.
1. MicrosoftExcel
1.1 . MicrosoftExcel... Concepts and opportunities
The MS Excel spreadsheet (spreadsheet) is one of the most frequently used applications of the MS Office package, a powerful tool in the right hands, which greatly simplifies the day-to-day 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 work with data and allows you to get results without programming calculations. In combination with the programming language Visual Basic for Application (VBA), the MS Excel spreadsheet processor acquires a universal character and allows you to solve any problem at all, regardless of its nature.
Feature of spreadsheets is the ability to use formulas to describe the relationship between the values of different cells. The calculation according to the given formulas is performed automatically. Changing the contents of a cell leads to a recalculation of the values of all cells that are associated with it by formula relations and, thereby, to update the entire table in accordance with the changed data.
Key features of spreadsheets:
1. Carrying out the same type of complex calculations on large data sets;
2. automation of final calculations;
3. solving problems by selecting parameter values;
4. processing (statistical analysis) of the experimental results;
5. Carrying out a search for optimal values of parameters (solving optimization problems);
6. preparation of tabular documents;
7. construction of diagrams (including summary ones) according to the 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 (the name of the program is indicated in it) with buttons for controlling the program window and the document window (Minimize, Minimize to window or Maximize to full screen, Close);
2. The 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 the Name field and the Insert function (fx) button as elements, 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 tabs (sheet tabs) to move between worksheets.
8. Status bar.
1.3 Structure of spreadsheets
A file created using MS Excel is usually called a workbook. You can create as many workbooks as the availability of free memory on the corresponding memory device allows. You can open as many workbooks as they have been created. However, only one current (open) workbook can be an active workbook.
A workbook is a collection of worksheets, each with a tabular structure. In the document window, only the current (active) worksheet is displayed, with which you are working. Each worksheet has a title that appears on the sheet tab at the bottom of the window. Using shortcuts, you can switch to other worksheets in the same workbook. To rename a worksheet, you need to double-click on its shortcut 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, by placing the mouse pointer on the shortcut of the active worksheet, right-click, and then, in the appeared context menu, click on the Rename line and perform renaming. You can add (insert) new sheets to the workbook or delete unnecessary ones. A sheet can be inserted by executing the Insert menu command, the Sheet line in the list of menu items. The sheet will be inserted in front of the active sheet. The execution of the above actions can be carried out using the context menu, which is activated by pressing the right mouse button, the pointer of which should be set on the tab of the corresponding sheet. To swap worksheets, you need to place the mouse pointer on the tab of the sheet to be 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 by uppercase Latin letters and, further, by two-letter combinations. The worksheet contains 256 columns in total, named A through IV. Lines are sequentially numbered from 1 to 65536.
At the intersection of columns and rows, table cells are formed. They are the minimum elements for storing data. Each cell has its own address. A cell address consists of the column name and the row number at the intersection of which the cell is located, for example, A1, B5, DE324. Cell addresses are used when writing formulas that determine 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 is 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 in adjacent cells that form a rectangular region can be referenced as a whole 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 denoted by the colon-separated address of the first cell and the address of the last cell of the range, for example, B5: F15. Selection of a range of cells can be done by dragging the mouse pointer from one corner cell to the opposite cell along the diagonal. At the same time, the frame of the current (active) cell expands, covering the entire selected range.
To speed up and simplify computing work Excel provides the user with a powerful apparatus of worksheet functions, allowing to carry out almost all possible calculations.
All in all, MS Excel contains over 400 worksheet functions (built-in functions). All of them, in accordance with 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 to a cell of a worksheet must begin with the equals symbol (=). If a function is used as part of any other complex function or in a formula (mega-formula), then the symbol equals (=) is written before this function (formula). Any function can be accessed by specifying its name and the argument (parameter) or parameter list following it 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 function calls as you like should not exceed 1024 characters. It is recommended to type all names when writing (entering) a formula in lowercase letters, then correctly entered names will be displayed in uppercase letters.
1.4 Possible errors when using functions in formulas
