“Spreadsheet”的意思、由来-开放百科全书

A spreadsheet is an interactive computer application for organization, analysis and storage of data in tabular form.^[1]^[2]^[3] Spreadsheets developed as computerized analogs of paper accounting worksheets.^[4] The program operates on data entered in cells of a table. Each cell may contain either numeric or text data, or the results of formulas that automatically calculate and display a value based on the contents of other cells. A spreadsheet may also refer to one such electronic document.^[5]^[6]^[7]

Spreadsheet users can adjust any stored value and observe the effects on calculated values. This makes the spreadsheet useful for "what-if" analysis since many cases can be rapidly investigated without manual recalculation. Modern spreadsheet software can have multiple interacting sheets, and can display data either as text and numerals, or in graphical form.

Besides performing basic arithmetic and mathematical functions, modern spreadsheets provide built-in functions for common financial and statistical operations. Such calculations as net present value or standard deviation can be applied to tabular data with a pre-programmed function in a formula. Spreadsheet programs also provide conditional expressions, functions to convert between text and numbers, and functions that operate on strings of text.

Spreadsheets have replaced paper-based systems throughout the business world. Although they were first developed for accounting or bookkeeping tasks, they now are used extensively in any context where tabular lists are built, sorted, and shared.

LANPAR, available in 1969,^[8] was the first electronic spreadsheet on mainframe and time sharing computers. LANPAR was an acronym: LANguage for Programming Arrays at Random.^[8] VisiCalc was the first electronic spreadsheet on a microcomputer,^[9] and it helped turn the Apple II computer into a popular and widely used system. Lotus 1-2-3 was the leading spreadsheet when DOS was the dominant operating system.^[10] Excel now has the largest market share on the Windows and Macintosh platforms.^[11]^[12]^[13] A spreadsheet program is a standard feature of an office productivity suite; since the advent of web apps, office suites now also exist in web app form. Web based spreadsheets are a relatively new category.

Usage

A spreadsheet consists of a table of cells arranged into rows and columns and referred to by the X and Y locations. X locations, the columns, are normally represented by letters, "A", "B", "C", etc., while rows are normally represented by numbers, 1, 2, 3, etc. A single cell can be referred to by addressing its row and column, "C10" for instance. This electronic concept of cell references was first introduced in LANPAR (Language for Programming Arrays at Random) (co-invented by Rene Pardo and Remy Landau) and a variant used in VisiCalc, and known as "A1 notation".

Additionally, spreadsheets have the concept of a range, a group of cells, normally contiguous. For instance, one can refer to the first ten cells in the first column with the range "A1:A10". LANPAR innovated forward referencing/natural order calculation which didn't re-appear until Lotus 123 and Microsoft's MultiPlan Version 2.

In modern spreadsheet applications, several spreadsheets, often known as worksheets or simply sheets, are gathered together to form a workbook. A workbook is physically represented by a file, containing all the data for the book, the sheets and the cells with the sheets. Worksheets are normally represented by tabs that flip between pages, each one containing one of the sheets, although Numbers changes this model significantly. Cells in a multi-sheet book add the sheet name to their reference, for instance, "Sheet 1!C10". Some systems extend this syntax to allow cell references to different workbooks.

Users interact with sheets primarily through the cells. A given cell can hold data by simply entering it in, or a formula, which is normally created by preceding the text with an equals sign. Data might include the string of text hello world, the number 5 or the date 16-Dec-91. A formula would begin with the equals sign, =5*3, but this would normally be invisible because the display shows the result of the calculation, 15 in this case, not the formula itself. This may lead to confusion in some cases.

The key feature of spreadsheets is the ability for a formula to refer to the contents of other cells, which may in turn be the result of a formula. To make such a formula, one simply replaces a number with a cell reference. For instance, the formula =5*C10 would produce the result of multiplying the value in cell C10 by the number 5. If C10 holds the value 3 the result will be 15. But C10 might also hold its own formula referring to other cells, and so on.

The ability to chain formulas together is what gives a spreadsheet its power. Many problems can be broken down into a series of individual mathematical steps, and these can be assigned to individual formulas in cells. Some of these formulas can apply to ranges as well, like the SUM function that adds up all the numbers within a range.

Spreadsheets share many principles and traits of databases, but spreadsheets and databases are not the same thing. A spreadsheet is essentially just one table, whereas a database is a collection of many tables with machine-readable semantic relationships between them. While it is true that a workbook that contains three sheets is indeed a file containing multiple tables that can interact with each other, it lacks the relational structure of a database. Spreadsheets and databases are interoperable—sheets can be imported into databases to become tables within them, and database queries can be exported into spreadsheets for further analysis.

A spreadsheet program is one of the main components of an office productivity suite, which usually also contains a word processor, a presentation program, and a database management system. Programs within a suite use similar commands for similar functions. Usually sharing data between the components is easier than with a non-integrated collection of functionally equivalent programs. This was particularly an advantage at a time when many personal computer systems used text-mode displays and commands, instead of a graphical user interface.

History

Paper spreadsheets

The word 'spreadsheet' came from 'spread' in its sense of a newspaper or magazine item (text or graphics) that covers two facing pages, extending across the center fold and treating the two pages as one large page. The compound word 'spread-sheet' came to mean the format used to present book-keeping ledgers—with columns for categories of expenditures across the top, invoices listed down the left margin, and the amount of each payment in the cell where its row and column intersect—which were, traditionally, a "spread" across facing pages of a bound ledger (book for keeping accounting records) or on oversized sheets of paper (termed 'analysis paper') ruled into rows and columns in that format and approximately twice as wide as ordinary paper.^[14]

Early implementations

Batch spreadsheet report generator

A batch "spreadsheet" is indistinguishable from a batch compiler with added input data, producing an output report, i.e., a 4GL or conventional, non-interactive, batch computer program. However, this concept of an electronic spreadsheet was outlined in the 1961 paper "Budgeting Models and System Simulation" by Richard Mattessich.^[15] The subsequent work by Mattessich (1964a, Chpt. 9, Accounting and Analytical Methods) and its companion volume, Mattessich (1964b, Simulation of the Firm through a Budget Computer Program) applied computerized spreadsheets to accounting and budgeting systems (on mainframe computers programmed in FORTRAN IV). These batch Spreadsheets dealt primarily with the addition or subtraction of entire columns or rows (of input variables), rather than individual cells.

In 1962 this concept of the spreadsheet, called BCL for Business Computer Language, was implemented on an IBM 1130 and in 1963 was ported to an IBM 7040 by R. Brian Walsh at Marquette University, Wisconsin. This program was written in Fortran. Primitive timesharing was available on those machines. In 1968 BCL was ported by Walsh to the IBM 360/67 timesharing machine at Washington State University. It was used to assist in the teaching of finance to business students. Students were able to take information prepared by the professor and manipulate it to represent it and show ratios etc. In 1964, a book entitled Business Computer Language was written by Kimball, Stoffells and Walsh and both the book and program were copyrighted in 1966 and years later that copyright was renewed^[16]

In the late 1960s Xerox used BCL to develop a more sophisticated version for their timesharing system.

LANPAR spreadsheet compiler

A key invention in the development of electronic spreadsheets was made by Rene K. Pardo and Remy Landau, who filed in 1970 {{US patent|4398249}} on a spreadsheet automatic natural order calculation algorithm. While the patent was initially rejected by the patent office as being a purely mathematical invention, following 12 years of appeals, Pardo and Landau won a landmark court case at the Predecessor Court of the Federal Circuit (CCPA), overturning the Patent Office in 1983 — establishing that "something does not cease to become patentable merely because the point of novelty is in an algorithm." However, in 1995 the United States Court of Appeals for the Federal Circuit ruled the patent unenforceable.^[17]

The actual software was called LANPAR — LANguage for Programming Arrays at Random.^[18] This was conceived and entirely developed in the summer of 1969, following Pardo and Landau's recent graduation from Harvard University. Co-inventor Rene Pardo recalls that he felt that one manager at Bell Canada should not have to depend on programmers to program and modify budgeting forms, and he thought of letting users type out forms in any order and having an electronic computer calculate results in the right order ("Forward Referencing/Natural Order Calculation"). Pardo and Landau developed and implemented the software in 1969.^[19]

LANPAR was used by Bell Canada, AT&T and the 18 operating telephone companies nationwide for their local and national budgeting operations. LANPAR was also used by General Motors. Its uniqueness was Pardo's co-invention incorporating forward referencing/natural order calculation (one of the first "non-procedural" computer languages)^[20] as opposed to left-to-right, top to bottom sequence for calculating the results in each cell that was used by VisiCalc, SuperCalc, and the first version of MultiPlan. Without forward referencing/natural order calculation, the user had to manually recalculate the spreadsheet as many times as necessary until the values in all the cells had stopped changing. Forward referencing/natural order calculation by a compiler was the cornerstone functionality required for any spreadsheet to be practical and successful.

The LANPAR system was implemented on GE400 and Honeywell 6000 online timesharing systems, enabling users to program remotely via computer terminals and modems. Data could be entered dynamically either by paper tape, specific file access, on line, or even external data bases. Sophisticated mathematical expressions, including logical comparisons and "if/then" statements, could be used in any cell, and cells could be presented in any order.

Autoplan/Autotab spreadsheet programming language

In 1968, three former employees from the General Electric computer company headquartered in Phoenix, Arizona set out to start their own software development house. A. Leroy Ellison, Harry N. Cantrell, and Russell E. Edwards found themselves doing a large number of calculations when making tables for the business plans that they were presenting to venture capitalists. They decided to save themselves a lot of effort and wrote a computer program that produced their tables for them. This program, originally conceived as a simple utility for their personal use, would turn out to be the first software product offered by the company that would become known as Capex Corporation. "AutoPlan" ran on GE’s Time-sharing service; afterward, a version that ran on IBM mainframes was introduced under the name AutoTab. (National CSS offered a similar product, CSSTAB, which had a moderate timesharing user base by the early 1970s. A major application was opinion research tabulation.)

AutoPlan/AutoTab was not a WYSIWYG interactive spreadsheet program, it was a simple scripting language for spreadsheets. The user defined the names and labels for the rows and columns, then the formulas that defined each row or column. In 1975, Autotab-II was advertised as extending the original to a maximum of "1,500 rows and columns, combined in any proportion the user requires..."^[21]

IBM Financial Planning and Control System

The IBM Financial Planning and Control System was developed in 1976, by Brian Ingham at IBM Canada. It was implemented by IBM in at least 30 countries. It ran on an IBM mainframe and was among the first applications for financial planning developed with APL that completely hid the programming language from the end-user. Through IBM's VM operating system, it was among the first programs to auto-update each copy of the application as new versions were released. Users could specify simple mathematical relationships between rows and between columns. Compared to any contemporary alternatives, it could support very large spreadsheets. It loaded actual financial data drawn from the legacy batch system into each user's spreadsheet on a monthly basis. It was designed to optimize the power of APL through object kernels, increasing program efficiency by as much as 50 fold over traditional programming approaches.

APLDOT modeling language

An example of an early "industrial weight" spreadsheet was APLDOT, developed in 1976 at the United States Railway Association on an IBM 360/91, running at The Johns Hopkins University Applied Physics Laboratory in Laurel, MD.^[22] The application was used successfully for many years in developing such applications as financial and costing models for the US Congress and for Conrail. APLDOT was dubbed a "spreadsheet" because financial analysts and strategic planners used it to solve the same problems they addressed with paper spreadsheet pads.

VisiCalc

Because of Dan Bricklin and Bob Frankston's implementation of VisiCalc on the Apple II in 1979 and the IBM PC in 1981, the spreadsheet concept became widely known in the late 1970s and early 1980s. VisiCalc was the first spreadsheet that combined all essential features of modern spreadsheet applications (except for forward referencing/natural order recalculation), such as WYSIWYG interactive user interface, automatic recalculation, status and formula lines, range copying with relative and absolute references, formula building by selecting referenced cells. Unaware of LANPAR at the time PC World magazine called VisiCalc the first electronic spreadsheet.^[23]

Bricklin has spoken of watching his university professor create a table of calculation results on a blackboard. When the professor found an error, he had to tediously erase and rewrite a number of sequential entries in the table, triggering Bricklin to think that he could replicate the process on a computer, using the blackboard as the model to view results of underlying formulas. His idea became VisiCalc, the first application that turned the personal computer from a hobby for computer enthusiasts into a business tool.

VisiCalc went on to become the first "killer application",^[24]^[25] an application that was so compelling, people would buy a particular computer just to use it. VisiCalc was in no small part responsible for the Apple II's success. The program was later ported to a number of other early computers, notably CP/M machines, the Atari 8-bit family and various Commodore platforms. Nevertheless, VisiCalc remains best known as an Apple II program.

SuperCalc

SuperCalc was a spreadsheet application published by Sorcim in 1980, and originally bundled (along with WordStar) as part of the CP/M software package included with the Osborne 1 portable computer. It quickly became the de facto standard spreadsheet for CP/M and was ported to MS-DOS in 1982.

Lotus 1-2-3 and other MS-DOS spreadsheets

The acceptance of the IBM PC following its introduction in August, 1981, began slowly, because most of the programs available for it were translations from other computer models. Things changed dramatically with the introduction of Lotus 1-2-3 in November, 1982, and release for sale in January, 1983. Since it was written especially for the IBM PC, it had good performance and became the killer app for this PC. Lotus 1-2-3 drove sales of the PC due to the improvements in speed and graphics compared to VisiCalc on the Apple II.^[26]

Lotus 1-2-3, along with its competitor Borland Quattro, soon displaced VisiCalc. Lotus 1-2-3 was released on January 26, 1983, started outselling then-most-popular VisiCalc the very same year, and for a number of years was the leading spreadsheet for DOS.

Microsoft Excel

Web based spreadsheets

With the advent of advanced web technologies such as Ajax circa 2005, a new generation of online spreadsheets has emerged. Equipped with a rich Internet application user experience, the best web based online spreadsheets have many of the features seen in desktop spreadsheet applications. Some of them such as EditGrid, Google Sheets, Microsoft Excel Online, Smartsheet, or Zoho Sheet also have strong multi-user collaboration features or offer real time updates from remote sources such as stock prices and currency exchange rates.

Other spreadsheets

Gnumeric is a free, cross-platform spreadsheet program that is part of the GNOME Free Software Desktop Project. OpenOffice.org Calc and the closely related LibreOffice Calc (using the LGPL license) are free and open-source spreadsheets.

Other products

A number of companies have attempted to break into the spreadsheet market with programs based on very different paradigms. Lotus introduced what is likely the most successful example, Lotus Improv, which saw some commercial success, notably in the financial world where its powerful data mining capabilities remain well respected to this day.

Spreadsheet 2000 attempted to dramatically simplify formula construction, but was generally not successful.

Concepts

The main concepts are those of a grid of cells, called a sheet, with either raw data, called values, or formulas in the cells. Formulas say how to mechanically compute new values from existing values. Values are generally numbers, but can also be pure text, dates, months, etc. Extensions of these concepts include logical spreadsheets. Various tools for programming sheets, visualizing data, remotely connecting sheets, displaying cells' dependencies, etc. are commonly provided.

Cells

A "cell" can be thought of as a box for holding data. A single cell is usually referenced by its column and row (C2 would represent the cell containing the value 30 in the example table below). Usually rows, representing the dependent variables, are referenced in decimal notation starting from 1, while columns representing the independent variables use 26-adic bijective numeration using the letters A-Z as numerals. Its physical size can usually be tailored to its content by dragging its height or width at box intersections (or for entire columns or rows by dragging the column- or row-headers).

An array of cells is called a sheet or worksheet. It is analogous to an array of variables in a conventional computer program (although certain unchanging values, once entered, could be considered, by the same analogy, constants). In most implementations, many worksheets may be located within a single spreadsheet. A worksheet is simply a subset of the spreadsheet divided for the sake of clarity. Functionally, the spreadsheet operates as a whole and all cells operate as global variables within the spreadsheet (each variable having 'read' access only except its own containing cell).

Values

A value can be entered from the computer keyboard by directly typing into the cell itself. Alternatively, a value can be based on a formula (see below), which might perform a calculation, display the current date or time, or retrieve external data such as a stock quote or a database value.

Automatic recalculation

A standard of spreadsheets since the 1980s, this optional feature eliminates the need to manually request the spreadsheet program to recalculate values (nowadays typically the default option unless specifically 'switched off' for large spreadsheets, usually to improve performance). Some earlier spreadsheets required a manual request to recalculate, since recalculation of large or complex spreadsheets often reduced data entry speed. Many modern spreadsheets still retain this option.

Recalculation generally requires that there are no circular dependencies in a spreadsheet. A dependency graph is a graph that has a vertex for each object to be updated, and an edge connecting two objects whenever one of them needs to be updated earlier than the other. Dependency graphs without circular dependencies form directed acyclic graphs, representations of partial orderings (in this case, across a spreadsheet) that can be relied upon to give a definite result.^[34]

Real-time update

This feature refers to updating a cell's contents periodically with a value from an external source—such as a cell in a "remote" spreadsheet. For shared, Web-based spreadsheets, it applies to "immediately" updating cells another user has updated. All dependent cells must be updated also.

Locked cell

Once entered, selected cells (or the entire spreadsheet) can optionally be "locked" to prevent accidental overwriting. Typically this would apply to cells containing formulas but might be applicable to cells containing "constants" such as a kilogram/pounds conversion factor (2.20462262 to eight decimal places). Even though individual cells are marked as locked, the spreadsheet data are not protected until the feature is activated in the file preferences.

Data format

A cell or range can optionally be defined to specify how the value is displayed. The default display format is usually set by its initial content if not specifically previously set, so that for example "31/12/2007" or "31 Dec 2007" would default to the cell format of date.

Similarly adding a % sign after a numeric value would tag the cell as a percentage cell format. The cell contents are not changed by this format, only the displayed value.

Some cell formats such as "numeric" or "currency" can also specify the number of decimal places.

This can allow invalid operations (such as doing multiplication on a cell containing a date), resulting in illogical results without an appropriate warning.

Cell formatting

Depending on the capability of the spreadsheet application, each cell (like its counterpart the "style" in a word processor) can be separately formatted using the attributes of either the content (point size, color, bold or italic) or the cell (border thickness, background shading, color). To aid the readability of a spreadsheet, cell formatting may be conditionally applied to data; for example, a negative number may be displayed in red.

A cell's formatting does not typically affect its content and depending on how cells are referenced or copied to other worksheets or applications, the formatting may not be carried with the content.

Named cells

In most implementations, a cell, or group of cells in a column or row, can be "named" enabling the user to refer to those cells by a name rather than by a grid reference. Names must be unique within the spreadsheet, but when using multiple sheets in a spreadsheet file, an identically named cell range on each sheet can be used if it is distinguished by adding the sheet name. One reason for this usage is for creating or running macros that repeat a command across many sheets. Another reason is that formulas with named variables are readily checked against the algebra they are intended to implement (they resemble Fortran expressions). Use of named variables and named functions also makes the spreadsheet structure more transparent.

Cell reference

In place of a named cell, an alternative approach is to use a cell (or grid) reference. Most cell references indicate another cell in the same spreadsheet, but a cell reference can also refer to a cell in a different sheet within the same spreadsheet, or (depending on the implementation) to a cell in another spreadsheet entirely, or to a value from a remote application.

A typical cell reference in "A1" style consists of one or two case-insensitive letters to identify the column (if there are up to 256 columns: A–Z and AA–IV) followed by a row number (e.g., in the range 1–65536). Either part can be relative (it changes when the formula it is in is moved or copied), or absolute (indicated with $ in front of the part concerned of the cell reference). The alternative "R1C1" reference style consists of the letter R, the row number, the letter C, and the column number; relative row or column numbers are indicated by enclosing the number in square brackets. Most current spreadsheets use the A1 style, some providing the R1C1 style as a compatibility option.

When the computer calculates a formula in one cell to update the displayed value of that cell, cell reference(s) in that cell, naming some other cell(s), cause the computer to fetch the value of the named cell(s).

Some spreadsheet implementations in Excel allow a cell references to another spreadsheet (not the current open and active file) on the same computer or a local network. It may also refer to a cell in another open and active spreadsheet on the same computer or network that is defined as shareable. These references contain the complete filename, such as:

In a spreadsheet, references to cells automatically update when new rows or columns are inserted or deleted. Care must be taken, however, when adding a row immediately before a set of column totals to ensure that the totals reflect the additional rows values—which they often do not.

A circular reference occurs when the formula in one cell refers—directly, or indirectly through a chain of cell references—to another cell that refers back to the first cell. Many common errors cause circular references. However, some valid techniques use circular references. These techniques, after many spreadsheet recalculations, (usually) converge on the correct values for those cells.

Cell ranges

Likewise, instead of using a named range of cells, a range reference can be used. Reference to a range of cells is typically of the form (A1:A6), which specifies all the cells in the range A1 through to A6. A formula such as "=SUM(A1:A6)" would add all the cells specified and put the result in the cell containing the formula itself.

Sheets

In the earliest spreadsheets, cells were a simple two-dimensional grid. Over time, the model has expanded to include a third dimension, and in some cases a series of named grids, called sheets. The most advanced examples allow inversion and rotation operations which can slice and project the data set in various ways.

Formulas

A formula identifies the calculation needed to place the result in the cell it is contained within. A cell containing a formula therefore has two display components; the formula itself and the resulting value. The formula is normally only shown when the cell is selected by "clicking" the mouse over a particular cell; otherwise it contains the result of the calculation.

A formula assigns values to a cell or range of cells, and typically has the format:

When a cell contains a formula, it often contains references to other cells. Such a cell reference is a type of variable. Its value is the value of the referenced cell or some derivation of it. If that cell in turn references other cells, the value depends on the values of those. References can be relative (e.g., A1, or B1:B3), absolute (e.g., $A$1, or $B$1:$B$3) or mixed row– or column-wise absolute/relative (e.g., $A1 is column-wise absolute and A$1 is row-wise absolute).

The available options for valid formulas depends on the particular spreadsheet implementation but, in general, most arithmetic operations and quite complex nested conditional operations can be performed by most of today's commercial spreadsheets. Modern implementations also offer functions to access custom-build functions, remote data, and applications.

A formula may contain a condition (or nested conditions)—with or without an actual calculation—and is sometimes used purely to identify and highlight errors. In the example below, it is assumed the sum of a column of percentages (A1 through A6) is tested for validity and an explicit message put into the adjacent right-hand cell.

The best way to build up conditional statements is step by step composing followed by trial and error testing and refining code.

A spreadsheet does not, in fact, have to contain any formulas at all, in which case it could be considered merely a collection of data arranged in rows and columns (a database) like a calendar, timetable or simple list. Because of its ease of use, formatting and hyperlinking capabilities, many spreadsheets are used solely for this purpose.

Functions

Spreadsheets usually contain a number of supplied functions, such as arithmetic operations (for example, summations, averages and so forth), trigonometric functions, statistical functions, and so forth. In addition there is often a provision for user-defined functions. In Microsoft Excel these functions are defined using Visual Basic for Applications in the supplied Visual Basic editor, and such functions are automatically accessible on the worksheet. In addition, programs can be written that pull information from the worksheet, perform some calculations, and report the results back to the worksheet. In the figure, the name sq is user-assigned, and function sq is introduced using the Visual Basic editor supplied with Excel. Name Manager displays the spreadsheet definitions of named variables x & y.

Subroutines

Functions themselves cannot write into the worksheet, but simply return their evaluation. However, in Microsoft Excel, subroutines can write values or text found within the subroutine directly to the spreadsheet. The figure shows the Visual Basic code for a subroutine that reads each member of the named column variable x, calculates its square, and writes this value into the corresponding element of named column variable y. The y column contains no formula because its values are calculated in the subroutine, not on the spreadsheet, and simply are written in.

Remote spreadsheet

Whenever a reference is made to a cell or group of cells that are not located within the current physical spreadsheet file, it is considered as accessing a "remote" spreadsheet. The contents of the referenced cell may be accessed either on first reference with a manual update or more recently in the case of web based spreadsheets, as a near real time value with a specified automatic refresh interval.

Charts

Many spreadsheet applications permit charts, graphs or histograms to be generated from specified groups of cells that are dynamically re-built as cell contents change. The generated graphic component can either be embedded within the current sheet or added as a separate object.

Multi-dimensional spreadsheets

In the late 1980s and early 1990s, first Javelin Software and Lotus Improv appeared. Unlike models in a conventional spreadsheet, they utilized models built on objects called variables, not on data in cells of a report. These multi-dimensional spreadsheets enabled viewing data and algorithms in various self-documenting ways, including simultaneous multiple synchronized views. For example, users of Javelin could move through the connections between variables on a diagram while seeing the logical roots and branches of each variable. This is an example of what is perhaps its primary contribution of the earlier Javelin—the concept of traceability of a user's logic or model structure through its twelve views. A complex model can be dissected and understood by others who had no role in its creation.

In these programs, a time series, or any variable, was an object in itself, not a collection of cells that happen to appear in a row or column. Variables could have many attributes, including complete awareness of their connections to all other variables, data references, and text and image notes. Calculations were performed on these objects, as opposed to a range of cells, so adding two time series automatically aligns them in calendar time, or in a user-defined time frame. Data were independent of worksheets—variables, and therefore data, could not be destroyed by deleting a row, column or entire worksheet. For instance, January's costs are subtracted from January's revenues, regardless of where or whether either appears in a worksheet. This permits actions later used in pivot tables, except that flexible manipulation of report tables was but one of many capabilities supported by variables. Moreover, if costs were entered by week and revenues by month, the program could allocate or interpolate as appropriate. This object design enabled variables and whole models to reference each other with user-defined variable names, and to perform multidimensional analysis and massive, but easily editable consolidations.

Logical spreadsheets

Spreadsheets that have a formula language based upon logical expressions, rather than arithmetic expressions are known as logical spreadsheets. Such spreadsheets can be used to reason deductively about their cell values.

Programming issues

Just as the early programming languages were designed to generate spreadsheet printouts, programming techniques themselves have evolved to process tables (also known as spreadsheets or matrices) of data more efficiently in the computer itself.

End-user development

Spreadsheets are a popular end-user development tool.^[36] EUD denotes activities or techniques in which people who are not professional developers create automated behavior and complex data objects without significant knowledge of a programming language. Many people find it easier to perform calculations in spreadsheets than by writing the equivalent sequential program. This is due to several traits of spreadsheets.

Spreadsheet programs

A "spreadsheet program" is designed to perform general computation tasks using spatial relationships rather than time as the primary organizing principle.

It is often convenient to think of a spreadsheet as a mathematical graph, where the nodes are spreadsheet cells, and the edges are references to other cells specified in formulas. This is often called the dependency graph of the spreadsheet. References between cells can take advantage of spatial concepts such as relative position and absolute position, as well as named locations, to make the spreadsheet formulas easier to understand and manage.

Spreadsheets usually attempt to automatically update cells when the cells they depend on change. The earliest spreadsheets used simple tactics like evaluating cells in a particular order, but modern spreadsheets calculate following a minimal recomputation order from the dependency graph. Later spreadsheets also include a limited ability to propagate values in reverse, altering source values so that a particular answer is reached in a certain cell. Since spreadsheet cells formulas are not generally invertible, though, this technique is of somewhat limited value.

Many of the concepts common to sequential programming models have analogues in the spreadsheet world. For example, the sequential model of the indexed loop is usually represented as a table of cells, with similar formulas (normally differing only in which cells they reference).

Spreadsheets have evolved to use scripting programming languages like VBA as a tool for extensibility beyond what the spreadsheet language makes easy.

Shortcomings

While spreadsheets represented a major step forward in quantitative modeling, they have deficiencies. Their shortcomings include the perceived unfriendliness of alpha-numeric cell addresses.^[42]

While there are built-in and third-party tools for desktop spreadsheet applications that address some of these shortcomings, awareness and use of these is generally low. A good example of this is that 55% of Capital market professionals "don't know" how their spreadsheets are audited; only 6% invest in a third-party solution^[59]

Spreadsheet risk

Spreadsheet risk is the risk associated with deriving a materially incorrect value from a spreadsheet application that will be utilised in making a related (usually numerically-based) decision. Examples include the valuation of an asset, the determination of financial accounts, the calculation of medicinal doses or the size of load-bearing beam for structural engineering. The risk may arise from inputting erroneous or fraudulent data values, from mistakes (or incorrect changes) within the logic of the spreadsheet or the omission of relevant updates (e.g., out of date exchange rates). Some single-instance errors have exceeded US$1 billion.^[60]^[61] Because spreadsheet risk is principally linked to the actions (or inaction) of individuals it is defined as a sub-category of operational risk.

In the report into the 2012 JPMorgan Chase trading loss, a lack of control over spreadsheets used for critical financial functions was cited as a factor in the trading losses of more than six billion dollars which were reported as a result of derivatives trading gone bad.

Despite this, research^[62] carried out by ClusterSeven revealed that around half (48%) of c-level executives and senior managers at firms reporting annual revenues over £50m said there were either no usage controls at all or poorly applied manual processes over the use of spreadsheets at the firms.^[62]^[63]

In 2013 Thomas Herndon, a graduate student of economics at the University of Massachusetts Amherst found major coding flaws in the spreadsheet used by the economists Carmen Reinhart and Kenneth Rogoff in a very influential 2010 journal article. The Reinhart and Rogoff article was widely used as justification to drive 2010–2013 European austerity programs.^[64]

See also

References

1. ^{{cite web|title=spreadsheet|url=http://www.merriam-webster.com/dictionary/spreadsheet|website=merriam-webster.com|publisher=Merriam-Webster|accessdate=23 June 2016}}
2. ^{{cite book|title=American Heritage Dictionary of the English Language|date=2011|publisher=Houghton Mifflin Harcourt Publishing Company|edition=5th|quote=A software interface consisting of an interactive grid made up of cells in which data or formulas are entered for analysis or presentation.}}
3. ^{{cite book|title=Collins English Dictionary – Complete and Unabridged|date=2014|publisher=HarperCollins Publishers|edition=12th|quote=(Computer Science) a computer program that allows easy entry and manipulation of figures, equations, and text, used esp for financial planning and budgeting}}
4. ^{{cite web|title=spreadsheet|url=http://whatis.techtarget.com/definition/spreadsheet|website=WhatIs.com|publisher=TechTarget|accessdate=23 June 2016}}
5. ^{{cite web|title=spreadsheet|url=http://www.dictionary.com/browse/spreadsheet|website=Dictionary.com Unabridged|publisher=Random House, Inc|accessdate=23 June 2016}}
6. ^{{cite web|last1=Beal|first1=Vangie|title=spreadsheet|url=http://www.webopedia.com/TERM/S/spreadsheet.html|website=webopedia|publisher=QuinStreet|accessdate=23 June 2016}}
7. ^{{cite web|title=Spreadsheet|url=http://www.computerhope.com/jargon/s/spreadsh.htm|website=Computer Hope|accessdate=23 June 2016}}
8. ^¹{{Cite book|url=https://books.google.com/?id=ms--K3jipt4C&pg=PT149&dq=LANPAR#v=onepage&q=LANPAR&f=false|title=The Grid Book|last=Higgins|first=Hannah|date=2009-01-01|publisher=MIT Press|isbn=9780262512404|language=en}}
9. ^Charles Babcock, "What's The Greatest Software Ever Written?", Information Week, 11 Aug 2006. Accessed 25 June 2014
10. ^{{cite web |title=The Executive computer; Lotus 1-2-3 Faces Up to the Upstarts |url=https://www.nytimes.com/1988/03/13/business/the-executive-computer-lotus-1-2-3-faces-up-to-the-upstarts.html?pagewanted=all&src=pm | accessdate=2012-10-14| website=NYTimes.com |publisher=The New York Times Company | date= 1988-03-13 | first=Peter H. | last=Lewis |quote=Release 3.0 is being written in the computer language known as C, to provide easy transportability among PCs, Macs and mainframes.}}
11. ^{{cite web |url=http://knowledge.wharton.upenn.edu/article.cfm?articleid=1795 |title=Rivals Set Their Sights on Microsoft Office: Can They Topple the Giant? –Knowledge@Wharton |publisher=Wharton, University of Pennsylvania |accessdate=2010-08-20}}
12. ^{{cite web |url=http://www.utdallas.edu/%7Eliebowit/book/sheets/sheet.html |title=spreadsheet analysis from winners, losers, and Microsoft |website=Utdallas.edu |accessdate=2010-08-20}}
13. ^{{cite web |url=http://www.utdallas.edu/%7Eliebowit/book/wordprocessor/word.html |title=A |website=Utdallas.edu |accessdate=2010-08-20}}
14. ^¹{{Cite web |url=http://dssresources.com/history/sshistory.html |last=Power |first=D. J. |title=A Brief History of Spreadsheets |website=DSSResources.COM |edition=3.6 |date=30 August 2004 |accessdate=25 June 2014}}
15. ^{{cite journal|title=Budgeting Models and System Simulation |first=Richard |last=Mattessich |author-link=Richard Mattessich|journal=The Accounting Review |volume=36 |issue=3 |year=1961 |pages=384–397 |jstor=242869}}
16. ^{{cite web |url= |title=Business Computer Language |accessdate= |author=Brian Walsh |year=1996 |work=IT-Directors.com}}
17. ^{{cite web|url=http://www.ll.georgetown.edu/Federal/judicial/fed/opinions/95opinions/95-1350.html |title=Refac v. Lotus |publisher=Ll.georgetown.edu |access-date=2010-08-20}}
18. ^This may be a backronym, as "LANPAR is also a portmanteau of the developers' surnames, "Landau" and "Pardo"
19. ^{{cite web|url=http://www.renepardo.com|title=Rene Pardo – Personal Web Page|work=renepardo.com}}
20. ^http://www.renepardo.com/articles/spreadsheet.pdf
21. ^[https://books.google.com/books?id=PPeM_JUhUqEC&pg=PA19&lpg=PA19&dq=autotab-iI&source=bl&ots=-aGsSzb8VP&sig=byBwwRhRG2tDlbWq_Bv8MEWmeuY&hl=en&sa=X&ved=0ahUKEwjA1-2goazNAhXHQpQKHe6qDKQQ6AEIHjAB#v=onepage&q=autotab-iI&f=false "'Autotab' Update Extends Former Matrix Size Limits"], 28 May 1975, p19, Computerworld
22. ^portal.acm.org – APLDOT
23. ^PC World – Three Minutes: Godfathers of the Spreadsheet
24. ^Power, D.J., A Brief History of Spreadsheets, DSSResources.COM, v3.6, 8 August 2004
25. ^"Killer Applications" (overview), Partha gawaargupta. Arizona State University in Tempe, Arizona, May 2002, Web page: ASU-killer-app {{Webarchive|url=https://web.archive.org/web/20110929024420/http://cactus.eas.asu.edu/partha/Columns/2002/07-01-killer-app.htm |date=2011-09-29 }}.
26. ^{{cite web|url=https://archive.org/stream/Whole_Earth_Software_Catalog_for_1986_1985_Point/Whole_Earth_Software_Catalog_for_1986_1985_Point_djvu.txt|title=Whole Earth Software Catalog|quote=Some say that half of all IBM PCs, in their hundreds of thousands, are running just 1-2-3. Numbers— clever, quick, knowledgeable— boiling the stupidity out of countless business decisions. Interesting how essential the quickness is. It's 1-2-3's speed that put it on top.}}
27. ^{{cite book |last1=Liebowitz |first1=Stan |last2=Margolis |first2=Stephen |year=2001 |chapter=6 |editor1-last=Ellig |editor1-first=Jerome |title=Dynamic Competition and Public Policy: Technology, Innovation, and Antitrust Issues |chapter-url=https://books.google.com/books?id=ynmOndJFoeQC&pg=PA171&lpg=PA171&dq=microsoft+ported+excel+windows+1987&source=bl&ots=RHbdl3Fec1&sig=zC6ht-MfeI0CU9t56JIdfnfo5js&hl=en&sa=X&ei=CBhuVJHDEYyCigKA0YDgDg&ved=0CB4Q6AEwAA#v=onepage&q=microsoft%20ported%20excel%20windows%201987&f=false |location=Cambridge |publisher=Cambridge University Press |page=171 |isbn=978-0-521-78250-0 }}
28. ^{{cite web |url=http://www.zdnet.com/goodbye-lotus-1-2-3-7000015385/ |title=Goodbye, Lotus 1-2-3 |last1=Vaughan-Nichols |first1=Steven J. |date=15 May 2013 |website=zdnet.com |publisher=CBS Interactive |accessdate=24 July 2014}}
29. ^{{cite web|url=http://simson.net/clips/91.MIPS.ImprovPowerStep.html |title=Improv and PowerStep |accessdate=2010-08-20 |archiveurl= https://web.archive.org/web/20020606140158/http://simson.net/clips/91.MIPS.ImprovPowerStep.html |archivedate= 2002-06-06}}
30. ^{{cite web|url=https://query.nytimes.com/gst/fullpage.html?res=940DE4DF1138F930A25750C0A96E948260|title=THE EXECUTIVE COMPUTER – Lotus 1-2-3 Faces Up to the Upstarts – NYTimes.com|date=13 March 1988|work=nytimes.com}}
31. ^{{cite web|url=http://vip.hex.net/~cbbrowne/spreadsheets.html|archiveurl=https://web.archive.org/web/20020806213619/http://vip.hex.net/~cbbrowne/spreadsheets.html|title=Linux Spreadsheets|archivedate=6 August 2002|work=hex.net|deadurl=yes|df=}}
32. ^{{cite journal | last=Kay | first=Alan | authorlink=Alan Kay | title=Computer Software | journal =Scientific American | volume =251 | issue =3 | pages =52–59 |date=September 1984 | doi=10.1038/scientificamerican0984-52 | pmid=6390676 | last2=Goldstein | first2=JL| bibcode=1984SciAm.251c..52K }} – Value Rule
33. ^{{cite journal|last=Burnett |first=Margaret|author1-link=Margaret Burnett |last2=Atwood |first2=J. |last3=Walpole Djang |first3=R. |last4=Reichwein |first4=J. |last5=Gottfried |first5=H. |last6=Yang |first6=S. |title=Forms/3: A first-order visual language to explore the boundaries of the spreadsheet paradigm |journal=Journal of Functional Programming |volume=11 |issue=2 |pages=155–206 |date=March 2001 |url=http://journals.cambridge.org/action/displayAbstract?aid=72731 |accessdate=2008-06-22}}
34. ^{{citation |mode=cs1 | last1=Al-Mutawa | first1=H. A. | last2=Dietrich | first2=J. | last3=Marsland | first3=S. | last4=McCartin | first4=C. | contribution=On the shape of circular dependencies in Java programs | doi=10.1109/ASWEC.2014.15 | pages=48–57 | publisher=IEEE | title=23rd Australian Software Engineering Conference | year=2014| isbn=978-1-4799-3149-1 }}
35. ^¹{{cite web|title=Trapeze|url=http://basalgangster.macgui.com/RetroMacComputing/The_Long_View/Entries/2011/3/26_Trapeze.html}}
36. ^{{cite web|url=http://www.uxmatters.com/mt/archives/2009/08/empowering-users-to-create-their-own-software.php|title=Empowering Users to Create Their Own Software|author=Peter Hornsby}}
37. ^{{cite web | title=Improving the world's most popular functional language: user-defined functions in Excel | first1=Simon | last1=Peyton Jones |authorlink1=Simon Peyton Jones |first2=Margaret |last2=Burnett|author2-link=Margaret Burnett |first3=Alan |last3=Blackwell |authorlink3=Alan Blackwell | url=http://research.microsoft.com/~simonpj/Papers/excel/index.htm |date=March 2003 |archiveurl=https://web.archive.org/web/20051016011341/http://research.microsoft.com/~simonpj/Papers/excel/index.htm |archivedate=2005-10-16}}
38. ^{{cite book|last1=Sarkar|first1=Advait|last2=Blackwell|first2=Alan|last3=Jamnik|first3=Mateja|last4=Spott|first4=Martin|title=Teach and Try: A simple interaction technique for exploratory data modelling by end users|journal=2014 IEEE Symposium on Visual Languages and Human-Centric Computing (VL/HCC 2014)|pages=53–56|doi=10.1109/VLHCC.2014.6883022|year=2014|isbn=978-1-4799-4035-6|citeseerx=10.1.1.695.2025}}
39. ^{{Cite book|title=Interactive visual machine learning in spreadsheets|journal=2015 IEEE Symposium on Visual Languages and Human-Centric Computing (VL/HCC)|date=2015-10-01|pages=159–163|doi=10.1109/VLHCC.2015.7357211|first=A.|last=Sarkar|first2=M.|last2=Jamnik|first3=A.F.|last3=Blackwell|first4=M.|last4=Spott|isbn=978-1-4673-7457-6}}
40. ^{{cite news | url=https://archive.org/stream/byte-magazine-1985-11/1985_11_BYTE_10-11_Inside_the_IBM_PCs#page/n145/mode/2up | title=Circuit Design with Lotus 1-2-3 | work=BYTE | date=Fall 1985 | accessdate=19 March 2016 | author=Haynes, John L. | pages=143–156}}
41. ^{{Cite journal|title=Translating Relational Queries into Spreadsheets|journal=IEEE Transactions on Knowledge and Data Engineering|date=2015-02-02|volume=27|issue=8|pages=1041–4347|doi=10.1109/TKDE.2015.2397440|first=J.|last=Sroka|first2=A.|last2=Panasiuk|first3=K.|last3=Stencel|first4=J.|last4=Tyszkiewicz|arxiv=1305.2103}}
42. ^Douglas Butler, "Why are spreadsheets so unfriendly?", The Fifth International Conference on Technology in Mathematics Teaching, August 2001. Accessed 25 June 2014
43. ^{{cite web|url=http://www.actuarialpost.co.uk/article/spreadsheet-risk-management-within-uk-organisations-351.htm|title= Spreadsheet Risk Management within UK Organisations|date=July 2011}}
44. ^¹{{cite web|url=http://mba.tuck.dartmouth.edu/spreadsheet/product_pubs.html|title= A Critical Review of the Literature on Spreadsheet Errors|accessdate=2008-04-18|first1= Stephen G. |last1=Powell |first2=Kenneth R. |last2=Baker |first3=Barry |last3=Lawson|publisher=Tuck School of Business at Darthmouth College|date=2007-12-01}}
45. ^{{cite web|url=http://www.theiia.org/intAuditor/itaudit/2009-articles/eliminating-spreadsheet-risks/|title=Eliminating Spreadsheet Risks|work=Internal Auditor Magazine|publisher=Institute of Internal Auditors (IIA)|accessdate=2010-05-10|author=Richard E. Blaustein|date=November 2009|archive-url=https://web.archive.org/web/20100905061742/http://www.theiia.org/intAuditor/itaudit/2009-articles/eliminating-spreadsheet-risks/|archive-date=2010-09-05|dead-url=yes|df=}} unabridged version
46. ^{{cite book|title=Professional Excel Development |author=Stephen Bullen, Rob Bovey & John Green |publisher=Addison-Wesley |url=https://books.google.com/books?id=VnegO0pMYlIC&pg=PA1 |isbn=978-0-321-50879-9 |year=2009 |edition=2nd }}
47. ^¹²{{cite web|url=http://lumina.com/dlana/papers/Whats%20wrong%20with%20spreadsheets.pdf|title= What's Wrong with Spreadsheets – and How to Fix them with Analytica|accessdate=2010-11-13|author= Max Henrion|date=2004-07-14}}
48. ^¹{{cite web|url=http://www.lionhrtpub.com/orms/orms-2-97/savage.html|title=Weighing the Pros and Cons of Decision Technology in Spreadsheets|work=ORMS Today Volume 24 Number 1|accessdate=2010-11-13|author=Sam Savage|date=February 2010}}
49. ^{{cite web|url=http://www.it-director.com/article.php?articleid=12681|title=Managing spreadsheets|accessdate=2006-06-29|author=Philip Howard|date=2005-04-22|work=IT-Directors.com}}
50. ^{{cite web|url=http://panko.shidler.hawaii.edu/ssr/Mypapers/whatknow.htm|title=What We Know About Spreadsheet Errors|accessdate=2006-09-22|author=Raymond R. Panko|date=January 2005}}
51. ^Is Excel Budgeting a Mistake?
Excel's critics say that Excel is fundamentally unsuited for budgeting, forecasting, and other activities that involve collaboration or consolidation. Are they correct?
52. ^http://www.cs.uiowa.edu/~jcryer/JSMTalk2001.pdf {{Webarchive|url=https://web.archive.org/web/20090126164319/http://cs.uiowa.edu/~jcryer/JSMTalk2001.pdf |date=2009-01-26 }} Problems With Using Microsoft Excel for Statistics
53. ^{{cite web|url=http://www.burns-stat.com/pages/Tutor/spreadsheet_addiction.html|title=Spreadsheet Addiction|work=burns-stat.com}}
54. ^{{cite web|url=https://support.office.com/en-us/article/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3 |title=Excel specifications and limits – Excel – Microsoft Office |publisher=Office.microsoft.com |accessdate=2018-11-06}}
55. ^{{cite web |url=http://wiki.services.openoffice.org/wiki/Documentation/FAQ/Calc/Miscellaneous/What%27s_the_maximum_number_of_rows_and_cells_for_a_spreadsheet_file%3F |title=What's the maximum number of rows and cells for a spreadsheet file? – OpenOffice.org Wiki |publisher=Wiki.services.openoffice.org |date=2008-11-26 |accessdate=2010-08-20 |archive-url=https://web.archive.org/web/20090504095550/http://wiki.services.openoffice.org/wiki/Documentation/FAQ/Calc/Miscellaneous/What's_the_maximum_number_of_rows_and_cells_for_a_spreadsheet_file |archive-date=2009-05-04 |dead-url=yes |df= }}
56. ^{{cite web |url=http://www.deloitte.com/assets/Dcom-UnitedStates/Local%20Assets/Documents/AERS/us_risk_spreadsheet_mgt_022509%20(2).pdf |title=Spreadsheet Management: Not what you figured |author= |date=2009 |website=deloitte.com |publisher=Deloitte |accessdate=24 July 2014}}
57. ^{{cite web|url=http://www.accountingweb.co.uk/cgi-bin/item.cgi?id=40301|title=Excel spreadsheets in School budgeting – a cautionary tale (2001)|work=AccountingWEB|access-date=2007-12-18|archive-url=https://web.archive.org/web/20071007195537/http://www.accountingweb.co.uk/cgi-bin/item.cgi?id=40301|archive-date=2007-10-07|dead-url=yes|df=}}
58. ^{{cite web|url=http://www.eusprig.org/stories.htm|title=European Spreadsheet Risks Interest Group – spreadsheet risk management and solutions conference|work=eusprig.org}}
59. ^{{cite web|url=http://download.microsoft.com/download/4/6/1/461062F0-2D3E-4620-890E-826CE55D27B9/SpreadsheetsandCapitalMarkets.pdf|title=Spreadsheets and Capital Markets|date=June 2009|access-date=2009-08-13|archive-url=https://web.archive.org/web/20110604204942/http://download.microsoft.com/download/4/6/1/461062F0-2D3E-4620-890E-826CE55D27B9/SpreadsheetsandCapitalMarkets.pdf|archive-date=2011-06-04|dead-url=yes|df=}}
60. ^{{cite web |url= http://www.accessanalytic.com.au/Spreadsheet-Auditing.html|title= Excel Financial Model Auditing |accessdate=20 February 2013}}
61. ^{{cite news |title= Fannie Mae Corrects Mistakes In Results|author= Jonathan Glater|url= https://www.nytimes.com/2003/10/30/business/fannie-mae-corrects-mistakes-in-results.html|newspaper= The New York Times|date= 30 October 2003|accessdate=12 June 2012}}
62. ^¹{{cite web|url=http://www.ft.com/cms/s/0/60cea058-778b-11e2-9e6e-00144feabdc0.html#axzz2YaLVTi2m|title=Finance groups lack spreadsheet controls|author =Financial Times|date=18 March 2013}}
63. ^{{cite web|url=https://www.theguardian.com/media-network/media-network-blog/2013/apr/04/spreadsheet-risk-cyber-threat-finance|title=Spreadsheet risk and the threat of cyber attacks in finance|author =The Guardian|date=4 April 2013}}
64. ^{{cite web|url=http://chronicle.com/article/UMass-Graduate-Student-Talks/138763/|title=They Said at First That They Hadn't Made a Spreadsheet Error, When They Had'|newspaper= The Chronicle Of Higher Education|date=24 April 2013}}