4. Spreadsheets. Microsoft Excel

4.1. Purpose and Main Functions of Table Processors

Experience shows that addressing many economic challenges in high-level languages using the full range of professional programming techniques is complex and detailed. A radical new approach was found and implemented as spreadsheets — a tool available to non-professionals. The basic application of spreadsheets contains those areas of human activity where information is delivered in rectangular tables (planning, financial and accounting documents, store accounting, etc.) that require mathematical calculations while processing. That’s probably the way the term “table processor” originated. Note that relational databases, which are also represented as tables, are usually not adapted for calculations.

Currently, there are plenty of alternatives to spreadsheets: ABACUS, Varitab-86. Superplan, Multiplan, SuperCalk, QuattroPro, Excel,
Lotus 1-2-3, etc. They all represent a table processor and differ only by the interface and service capabilities.

A spreadsheet is a rectangular matrix composed of self-numbered cells.

The cell number is determined in the usual coordinate way, for example, the B3 cell, etc.

A block of cells (a range) is defined by a colon, for example, B3:D4, and forms a rectangle that is provided with the cells ВЗ, СЗ, D3, В4, С4, D4.

In each cell you can enter a number, a formula (an arithmetic expression), or a text. If a formula is written in a spreadsheet cell, the current state of the screen displays the value of that formula, not the formula itself. The formula’s operands can be mathematical functions, constants, and cell numbers (the contents of the cell with the specified number). A spreadsheet cell has a complex, multilayered structure; it may contain a link to another cell, the value of which is the result of calculations using a different formula, etc.

These are the examples of functions:

• sum(A2:A8) — the sum of the values of all cells from A2 to A8;
• sin(D5) — the sine of a number from cell D5;
• cos(F3) — the cosine of a number from cell F3.

This is an example of a formula:

2,7*A6 + cos (sum (D5:F7))

The above formula means that we want to get the result of the following calculations: the product of the number from cell A6 by 2,7 is added with the cosine of the angle, which is the sum of the numbers from cells D5, E5, F5, D6, E6, F6, D7, E7, F7.

The data included in the tables can be automatically presented in graphs, charts, histograms, etc.

The user carries on a dialogue with the specific program that allows to fill out the necessary contents (texts, numbers, or formulae for calculations); to clean, copy, delete, or sort data (i.e., set cells with rows and columns in order); to perform computations that span across the entire table or part of it; to save the table to the disk, and partially or in full print it, etc.

Here is an example that illustrates the capabilities of a spreadsheet.

Set up the payroll.

This is what the input values to fill in the spreadsheet look like:

Here we are dealing with three types of cell content: text, number, and formula. Input data entry is made on the command prompt. After filling in the table, the screen will display the following:

Normally, the accounting employee, after correcting one of the numbers, had to check for errors in the entire set of interrelated documents which explicitly or implicitly included the corrected parameter. A spreadsheet can capture this change instantaneously and throughout.

Modern Excel spreadsheets use the mouse manipulator; they have a convenient and comfortable interface.

The working window Sheet # contains a worksheet (several such pages are forming a book) emerging as a two-dimensional rectangular table. On the right and at the bottom of the worksheet, there are rulers with scroll arrows that allow you to quickly in-page navigate via a mouse click.

In the Excel window, as in other Microsoft Office programs, there are Tabs under the header area. Below there is a row of formulae that allow you to quickly and easily activate multiple Excel functions.

The data to be processed are placed on several sheets of an Excel workbook. To perform an activity on the data placed in the cells (to input, copy, delete, format, etc.), you should select them. To select the cell, point the cursor to it and click the mouse button. When the button is pressed, you can select a range of cells. Once the desired area is selected, right-click the context menu, which allows you to perform a number of commands: Cut, Copy, Insert, etc.