Basic data types in Excel. Data types in Excel and their input. Microsoft Excel spreadsheet

Excel recognizes two main types of data:

text, that is, a sequence of characters (as you type, they are automatically aligned to the left of the cell);

numbersdistinguishing them as numeric constants, formulas, built-in functions, or dates. As you enter numbers, Excel automatically aligns them to the right edge of the cell and performs calculations on them as needed.

Data text type are typically used to indicate table titles, column headings, text information in rows and columns, and comments.

Data numeric type are used for numeric values \u200b\u200b(or references to corresponding cells) and arithmetic operations that link them.

For example, the following expressions are numeric data in Excel:

• 200 * B5 \u003d 1000 (if the number 5 is in cell B5);

  A7 / B4 \u003d 20 (if, for example, A7 \u003d 80, and B4 \u003d 4).

When entering decimal numbers, use a comma ( , ) as the decimal separator.

Excel adheres to basic mathematical rules when processing formulas:

Expressions within parentheses are evaluated first.

Multiplication and division are performed before addition and subtraction.

Operators with the same priority are executed from left to right.

Parentheses are used to change the order of execution of statements.

If the number of closing and opening parentheses in the formula does not match, the message "Brackets mismatch" is displayed and the wrong part of the formula is highlighted.

Understanding Relative and Absolute Addressing

When copying formulas, all cell addresses are changed taking into account the change in the row number and / or column name. Such an address that changes is called relative... Sometimes this is not necessary because in a formula, you should always refer to a fixed cell.

In this case, to indicate that the address should remain fixed when copying, use absolute addresswhich is obtained from relative by adding a $ sign before the line number and / or column name.

Possible addresses:

$ F$1 - the cell is fixed.

C$3 - the column number is recalculated.

$ D5 - line number is recalculated.

That is, to convert to the rate $ in cell D1, write the formula: \u003d B1 / $ F $ 1, where $ is before F and 1 is the Excel command not to recalculate the cell address when copying the formula.

Functions Excel are standardized formulas that allow you to perform calculations in financial, statistical, mathematical, logical and other areas of activity. They are grouped by category and have the same syntax.

Functions are set using mathematical and other formulas, in accordance with which calculations are performed on specified values, called arguments , and in the order specified by the syntax. The syntax for the built-in functions is quite simple:

Function name (<аргумент1; аргумент2; аргумент3 и т.д.) where:

Function name - shows in the program what calculations are in question. Examples of function names are SUM, AVERAGE, etc.

Arguments - the values \u200b\u200bthat the function uses to calculate the result. The arguments are listed in parentheses following the function name. Arguments can be numeric values, text, boolean values, arrays, error or reference values, date / time, and other functions and formulas. Excel distinguishes between required (which must always be specified) and optional arguments. Individual arguments are separated by semicolons (;).

Result - the value obtained when calculating the function.

Many mathematical operations in Excel are performed using built-in functions.

Functions can be selected from the function list using the menu Insert | Functionor by clicking the icon f ( x ) on the standard toolbar with the appropriate selection of the required function.

After selecting the required function, the Function Wizard is connected, allowing the user to fill in its arguments step by step.

Functions in Excel are divided into the following main groups:

Mathematical, arithmetic and trigonometric functions.

Functions for working with dates and times.

Financial functions.

Logical functions.

Functions for working with databases.

Statistical functions.

Text functions, etc.

Math and trigonometric functions are used to perform elementary computational operations (subtraction, addition, multiplication, division), as well as exponentiation, rounding, logarithmic calculations, operations on random numbers and counting quantities. The group of trigonometric functions combines direct and inverse trigonometric functions.

Functions for working with date and time allow you to analyze and work with date and time values \u200b\u200bin formulas. Date and time values \u200b\u200bare stored and treated as numbers by the program. The start date is January 1, 1900. It corresponds to an integer value of 1. Each next day is represented by an integer value one greater than the value of the previous date.

Financial functions mainly solve the problem of calculating amortization and determining interest on loans and investments, as well as analyze transactions with securities. Using these functions, you can determine indicators such as the current value of investments, the return on securities and others.

Logic functions operate with logical values \u200b\u200band their result is also a logical value - TRUE or FALSE.

Database functionsare used to work with databases (lists) and tables with the corresponding data structure. With these functions, you can analyze the data in the worksheet.

Statistical functions allow you to solve many different problems, both complex professional and simple, for example, determining the arithmetic mean.

Text functions are used when working with text, they allow you to search, replace or combine certain sequences of characters, as well as count the number of characters and much more.

The ten most recent user functions are automatically grouped by Excel into the "10 recently used" category.

Various types of information can be entered into cells: numbers, symbols, time, date, logical constants (true or lying), formulas that calculate values. To change the external presentation of data in table cells, formatting is carried out - the Home toolbar (Fig. 12.7).

Figure: 12.7.

The Number tab provides setting the type of the number format, determining the number of decimal places (Fig. 12.8).

The Alignment tab provides for setting the order of horizontal and vertical alignment of the contents of cells, text orientation (rotation from +90 to -90 °), text splitting into lines and word wrap, as well as cell merging and auto-fitting of the column width (Fig. 12.9).

The Font tab provides a choice of the type and size of the font, style and additional text effects; Border - choice

Figure: 12.8.

Figure: 12.9.

color and type of line, method of framing cells; View - selection of color and pattern of filling cells; Protection - setting the protection of cells, hiding formulas.

Formulas and functions

Formula entry always starts with an equal sign (\u003d) or plus sign (+)

For example: \u003d 46 + 55; \u003d 200 * B5; \u003d A7 / B4.

Notes.

• 1. In the cell containing the formula (by default), only the calculation result is visible. The formula itself can be seen in the formula bar when this cell becomes active.
• 2. Excel evaluates the formula each time the table contents that reference the given formula change.
• 3. If the result of a calculation does not fit in a table cell, Excel may display a sequence of characters "#######" indicating that the column needs to be wider.
• 4. When entering decimal numbers, use a comma (,) as the decimal separator.

Excel functions Are predefined formulas that allow you to make calculations in financial, statistical, mathematical, logical and other areas of activity.

Functions are set using mathematical and other formulas, in accordance with which calculations are performed on specified values, called arguments, and in the order specified by the syntax. The syntax for the built-in functions is quite simple:

Function name (<аргумент1; аргумент2; аргумент3 и т.д.)

Function nameshows what calculations are in question. Examples of function names are SUM, AVERAGE, etc.

Arguments- the values \u200b\u200bthat the function uses to calculate the result.

They are listed in parentheses following the function name. Arguments can be numeric values, text, boolean values, arrays, error or reference values, date / time, and other functions and formulas. Excel distinguishes between required (which must always be specified) and optional arguments. Separate arguments are semicolon (;).

Result- the value obtained when calculating the function.

Many mathematical operations in Excel are performed using built-in functions.

Functions can be selected from the list of functions using the Insert / Function menu or by clicking the fx icon on the standard toolbar with the appropriate set of required functions.

After selecting the required function, the Function Wizard is connected, allowing the user to fill in its arguments step by step.

Functions in Excel are divided into the following main groups: mathematical, arithmetic and trigonometric; to work with dates and times; financial; brain teaser; for working with links and arrays; to work with a database; statistical; text, etc.

Three types of data can be entered in the ET cell:

· Numbers that represent quantitative quantities and numbers that are treated as dates and times. A number, as a quantitative value, is a sequence of characters, which includes numbers, signs "+" and "-" (at the beginning of the sequence) and a comma (or period) as a separator between integer and fractional parts. They are right-aligned. If the number is longer than the cell, then it is displayed in exponential form (7.88E + 07), or if it does not fit in the exponential form, then the characters //////// are displayed and the width of the cell must be increased. Usually, the entered number is immediately assigned the desired format (form of representation of the number). You can change it if you want. To do this, use the following buttons on the Formatting toolbar or the Format / Cells menu options, Number tab. Numbers are stored in computer memory with an accuracy of 15 digits. They are rounded when displayed in the appropriate format.

For the program to interpret the entered data as time and date, you need to use standard formats, for example: 10.5.01, Aug 98, 25 Jan 01, 10 Oct, April 99, 15:21, 5:21:04, etc. EXCEL does not distinguish between lowercase and uppercase letters and when entering dates, allows the use of a period (.), forward slash (/), or hyphen (-);

· Formulas that begin with the "\u003d" sign and are used to perform operations on the contents of cells. For example, \u003d A2 + C3 * F7. Excel allows you to use cell values \u200b\u200bfrom other worksheets in a worksheet. For example, the formula \u003d C5 * Sheet1! A1 finds the product of cells C5 in the current sheet and A1 in the first worksheet.

Calculation by the formula occurs automatically or by pressing the F9 key (if the corresponding option is not set in the book parameters). An instant recalculation and output of the result also occurs when the values \u200b\u200bof the operands included in the formulas are changed, which can be cell addresses, numbers and functions. Operands in formulas are connected with each other by signs of arithmetic operations: ^ (raising to a power), * (multiplication), / (division), + (addition), - (subtraction). When evaluating the value of an arithmetic expression, operations are performed from left to right, following the standard precedence rules. You can change the calculation order using parentheses;

· Text is any sequence of characters that is not a number and does not start with an equal sign. It is left-aligned and positioned on all unoccupied right-hand cells. A cell can contain up to 255 characters. If the text does not fit into the cell, then you can either increase the width of the cell (for this you need to stand on the line dividing the column headings and move it to the desired place), or set the word wrap inside the cell (Format / Cells / Alignment and check the box "Wrap by words ").

25 Describe the techniques of working with MS Excel tables: selection and copying of cells, the method of automatic filling of sequences. Bring the types of cell addressing

To select the desired area:

To select one cell, you need to click on it;

To select a range of cells, drag from the top of the left to the desired right corner of the range. If you need to select a non-contiguous range of cells, hold down the Ctrl key;

To select a line, click on its number;

To select a column, click on the column name;

To select the entire sheet, you need to click on the button at the intersection of row numbers and column names.

Highlighted area actions:

1) Can be placed on the clipboard (Edit / Cut or Cut button).

2) Place in the clipboard without deleting it from the table (Edit / Copy or the Copy button).

3) Paste from clipboard (Edit / Paste or Paste button).

The same commands are in the context menu that appears when you right-click on the selected area.

To speed up computational operations, as well as to create various numerical, textual or other sequences in adjacent cells, the automatic cell filling method is used, which is performed using a fill marker - a small black square located in the lower right corner of a selected cell or a range of cells. Initially, the initial values \u200b\u200bof the sequence are entered into one or two cells (depending on the type of sequence), and then the fill marker is dragged along the filled cells.

Types of addresses (links) of cells:

A relative address is a reference to a cell that changes when you copy (via the clipboard or by autocomplete) the formula containing it to another location. For example, the formula \u003d A1-B1 located in cell C1, when copied to cell C2 (down one row), will turn into \u003d A2-B2.

Data types in Excel, entered into cells, are usually divided into numeric, text and formulas. There are many more formats for input data, and they are practically all of the numeric type. By default, all cells in a newly created workbook have a common format and Excel can automatically assign a suitable data format if it manages to somehow interpret the input data.

Numerical values \u200b\u200bare automatically left-justified as you type, and text data are right-justified. This allows you to visually determine in what format the number is entered, as text, or still as a number.

If decimal places in a numeric value written in a cell of the general format will be specified with a dot, then Excel can interpret this record as a date and apply the appropriate formatting to it. Therefore, it is best to write a numeric value with decimal places separated by commas.

Text data includes all possible characters that can be entered from the keyboard, and characters such as plus, minus, and parentheses can be used in a numeric data type. For example, writing a numeric value in parentheses will be interpreted by Excel as a negative number, and a minus sign will be automatically added instead of the parentheses.

The result of the formula can apply to both numeric and textual data. Formula always starts with an equal sign.

To change the type of data in a cell, just select the cell and select the appropriate type or format from the menu.

There are quite a few data formats in Excel, which in the menu list are represented only by names. To see a more detailed description of each format, you must select in the menu "All formats" or click the corresponding icon in the section "Number" in the tab "Home" to enter advanced settings.

A separate cell can contain data related to one of the following types: character; numeric; formulas and functions, and dates.

Character (text) data includes alphabetic, numeric and special characters. Text data is aligned by default to the left of the cell.

Numeric data cannot contain alphabetic and special characters, but only numbers, since they perform mathematical operations with them. The integer part of the number is separated from the fractional comma ",". Numeric data is right justified in the cell.

Formulas. Any formula starts with an equal sign. After that comes a mathematical expression, consisting of numbers, cell addresses (links) and signs of mathematical operations. After entering a formula, the "Enter" button on the keyboard or in the formula bar (input) is always activated. Links in a formula act as addresses of cells whose contents are used in calculations.

By default, cell references in formulas are treated as relative... This means that the addresses in references containing the original data (operand) are automatically changed when the formula is copied from one cell to another.

When absolute addressing the link addresses do not change when the formula is copied, so the cell that the link points to is treated as permanent. To indicate absolute addressing, the $ symbol is entered. There are two types of absolute links: complete and partial... With full absolute addressing, the $ sign is placed both in front of the column name and in front of the row name (the key F4) ... Partial absolute references include a $ sign either before the row name (so that the row does not change when moving or copying), or before the column name (so that the column does not change when copying or moving).

Functions.They represent a program with a unique name, for which the user must specify specific values \u200b\u200bfor the arguments of the function, which appear in parentheses after its name. Functions can be considered a special case of a formula, i.e. it is the dependence of one variable on one or more variables. The following kinds of functions can be represented in spreadsheets:

− mathematical - perform various mathematical operations - calculating logarithms, trigonometric functions, etc .;

− statistical - perform operations to calculate the parameters of random variables or their distributions;

− text - perform operations on text strings or a sequence of characters;

− brain teaser - are used to build logical expressions;

− financial - are used in financial calculations, for example, the calculation of depreciation, the determination of the interest rate, etc .;

- date and time functions, etc.

All functions have the same notation and include the function name and a comma-separated list of arguments in parentheses.

The following standard errors can occur when creating a formula or function:

##### - numeric data does not fit into the cell in width, or when calculating using a formula, the later date is subtracted from the date;

# DIV / 0 - the denominators of the formula contain a zero value, or a reference to an empty cell;

#NUMBER! - when calculating, a number turned out that is too large or too small for the program;

#NAME? - a typo in the function name or cell (range) name;

#VALUE! - the function has a missing required parameter, or a reference to an empty cell or a cell with data whose type does not match the type of the required parameter is used as a parameter.