Электронные таблицы MS Excel

Цель работы

Освоить порядок работы в табличном процессоре Microsoft Excel 2016.

Общие сведения

С помощью электронных таблиц (ЭТ) можно решать финансовые, экономические и математические задачи.

Электронные таблицы целесообразно использовать для хранения счетов и внесения в них поправок, для многовариантного прогнозирования результатов предполагаемых финансовых операций, составления различных бланков, оформления деловой графики и выполнения баланса фирмы. С помощью ЭТ можно облегчить решение таких задач, как обработка заказов и планирование производства, расчёт налогов и заработной платы, учёт кадров и издержек, управление сбытом, составление прайс-листов, статистический анализ данных и др.

При работе с ЭТ практически исключается традиционное программирование (например, с помощью процедурно-ориентированных языков). Другое равноправное название электронной таблицы — табличный процессор.

Первую электронную таблицу VisiCalc создали в 1979 г. Дэн Бриклин (Dan Bricklin) и Боб Фрэнкстон (Bob Frankston). В 1982 г. Мич Кейпор (Mitch Kapor) и Джонатан Сачс (Jonathan Sachs) разработали другую удачную программу Lotus 1-2-3. В 1987 г. фирма Microsoft создала популярную в настоящее время электронную таблицу MS Excel.

Все файлы, создаваемые в электронных таблицах, называются книгами, причём каждая книга состоит из нескольких листов. Первоначально листы в ЭТ бывают пустыми, и после использования одного листа можно переходить на следующий лист. Между листами можно установить необходимые связи. Имена листов отображаются на ярлычках в нижней части окна книги.

Электронная таблица состоит из столбцов и строк. Столбцы чаще всего обозначаются заглавными латинскими буквами (A, B, C,.., AA, AB, AC,..), а строки — арабскими цифрами. Каждое пересечение строки и столбца образует «клетку» таблицы, которая может содержать текст, число или формулу.

Клетка обозначается буквой и цифрой по маркировке столбца и строки, на пересечении которых она находится. Набор координат, определяющих положение ячейки, называют ссылкой, например, С4. Обозначение ячеек похоже на обозначения, используемые в играх шахматы и морской бой. Иногда клетки называют ячейками, а ссылку порой называют адресом.

Заметим, что кроме рассмотренного стиля маркировки ячеек, который получил название А1, существует стиль, при котором вначале указывается порядковый номер строки, а затем порядковый номер столбца. Название этого стиля R1C1.

Формула = B2 + C4, расположенная в ячейке B6 (см. предыдущий рисунок), означает, что нужно взять содержимое ячейки B2, к нему прибавить содержимое ячейки C4 и результат поместить в ячейку B6. Всякое изменение содержимого ячеек B2 или C4 приведёт к автоматическому изменению результата в ячейке B6.

Пользователь может задать любой ячейке собственное имя и затем использовать его при расчётах. Например, =B3+ИТОГ. В данном случае одной из ячеек дано имя «ИТОГ». Использование имён ячеек облегчает составление формул и делает их более наглядными и информативными. Например, следующая формула говорит сама за себя:

=ДОХОД-РАСХОД

Формулы позволяют обрабатывать содержимое сразу нескольких ячеек (диапазона ячеек). Например, чтобы просуммировать содержимое ячеек B7, C7, D7, E7, достаточно записать:

=СУММ(B7:E7)

Формулы могут ссылаться на ячейки текущего листа, ячейки листов той же книги или ячейки других книг.

Диапазоном ячеек (также диапазоном ссылок) называются две или более ячейки, расположенные на одном листе.

Синтаксисом формул называется правила записи (структуру) элементов, входящих в формулу.

Формулы содержат знак равенства (=), вычисляемые элементы (операнды) и операторы.

Операнды — величины, с которыми оперирует (работает) ЭТ.

Операндами могут быть константы, ссылки или диапазоны ссылок, заголовки, имена и функции. Важно запомнить, что любая формула должна начинаться со знака равенства.

Константы — это величины, которые не изменяются в процессе вычислений, например, число 4 или текст «Прибыль». При записи констант перед ними знак равенства не ставится.

Ссылка — координата ячейки, её адрес.

Заголовки — ключевые слова, размещённые сверху столбца и слева от строки, с помощью которых описываются данные внутри блока данных. Заголовки можно использовать при ссылке на необходимые данные. В следующей таблице заголовками являются слова: Иванов, Петров, Сидоров, Физика, Химия.

	Физика	Химия
Иванов
Петров
Сидоров

Заголовки в ряде случаев делают обработку информации очень наглядной. Например, чтобы напечатать оценку Петрова по химии достаточно набрать формулу:

=Петров Химия

Пробел между заголовками является оператором пересечения диапазонов, который предписывает формуле вернуть значение из ячейки, находящейся на пересечении строки «Петров» и столбца «Химия».

Функция — это стандартная подпрограмма с уникальным именем, которая возвращает результат выполнения определённых действий над элементами, выступающими в роли аргументов. Например, функция

=МАКС(A1:A5;B3:B7)

отбирает максимальное число среди чисел двух указанных диапазонов ячеек.

Функции облегчают выполнение стандартных расчётов. Перед функцией ставится знак равенства, а аргументы заключаются в круглые скобки.

Аргументами в функции могут быть числовые значения, текст, ссылки, диапазоны ссылок, имена и вложенные функции.

Операторами обозначаются операции, которые выполняются над операндами. В Microsoft Excel имеется четыре вида операторов: арифметические, текстовые, сравнения, а также адресные операторы.

Арифметические операторы используются для выполнения математических операций над числами (сложение, вычитание, умножение, деление, возведение в степень, вычисление процентов). Результатом выполнения арифметической операции всегда является число.

Текстовой оператор — & (амперсант) позволяет объединить последовательности символов, находящихся в разных ячейках, в одну последовательность. У операции объединения символов есть специальное название – конкатенация.

Операторы сравнения используются для сопоставления двух чисел. К ним относятся операторы: равно, больше, меньше, больше или равно, меньше или равно, неравно. Результатом выполнения операции сравнения являются логические величины ИСТИНА или ЛОЖЬ.

Например, следующая функция позволяет просуммировать числа, значения которых больше четырёх:

=СУММЕСЛИ(А1:А7; “>4”)

Адресные операторы — двоеточие, запятая и пробел объединяют диапазоны ячеек.

Например, формула =СУММ(А1:А7) говорит о том, что должно быть просуммировано содержимое ячеек А1, А2, …, А7. Формула =МИН(В1:В5,С5:С9) отберёт минимальное число из ячеек двух указанных диапазонов. Наконец, пробел в формуле =Иванов Физика позволит вывести содержимое ячейки, находящейся на пересечении этих заголовков.

При обработке числовых данных могут выполняться арифметические операции (сложение, вычитание, умножение и т. д.), а также вычисляться многие статистические функции (определение среднего значения, дисперсии, медианы, нахождение максимального значения и т. п.).

Пользовательский интерфейс программы MS Excel показан на следующем рисунке.

ЭТ обладают элементами искусственного интеллекта.

MS Excel позволяет быстро ввести дни недели (от понедельника до воскресенья), причём достаточно набрать на клавиатуре только первый элемент, а остальные будут добавлены автоматически. Аналогично можно ввести месяцы (от января до декабря) либо другую заранее подготовленную последовательность элементов (например, фамилии).

Ещё одна интересная возможность ЭТ состоит в использовании арифметических и геометрических прогрессий для быстрого заполнения большого числа ячеек.

Например, чтобы ввести порядковые номера студентов, достаточно указать только цифры 1 и 2. Все остальные номера будут введены автоматически. С помощью арифметической прогрессии легко ввести, например, только чётные (или нечётные) числа.

За счёт геометрической прогрессии просто сформировать такие (и подобные) последовательности чисел: 5; 20; 80; 320 или 1; 2; 4; 8; 16; 32.

ЭТ позволяют «предсказывать» результаты. Например, если известны антропологические параметры трёх студентов, то можно попытаться «угадать» параметры четвёртого студента, для которого известна только часть данных.

Рост, см (х)
Вес, кг (y)

Предположим, что рост студента 170 см, а вес неизвестен. Расчёт веса можно произвести с помощью функции:

=ПРЕДСКАЗ(170;{52;63;74};{152;163;174})

В результате будет получен ответ: 70 кг.

Расчёты в данном случае ведутся по методу наименьших квадратов, и исходная зависимость заменяется (аппроксимируется) уравнением прямой линии.

В качестве аргументов здесь использованы два массива чисел, которые заключены в фигурные скобки.

ЭТ дают возможность копировать или перемещать содержимое клетки в другие позиции.

Рассмотрим два важных понятия: относительная и абсолютная ссылки.

На следующем рисунке в ячейке С1 записана формула сложения содержимого ячеек А1 и В1. При этом использованы относительные ссылки. Копирование этой формулы в ячейку С2 привело к автоматическому изменению ссылок. Вместо формулы =А1+В1 в ячейке С2 появилась формула =А2+В2. В ячейке С4 записана формула сложения, в которой использованы абсолютные ссылки. Легко заметить, что добавлены знаки долларов. Копирование этой формулы в ячейку С5 не привело к изменению этой формулы.

Понятно, что использование относительных и абсолютных ссылок даёт разные результаты. Это иллюстрирует следующий рисунок.

Таким образом, абсолютная ссылка — это использующаяся в формуле ссылка, которая не изменяется при копировании в другую ячейку. Относительная ссылка — это ссылка, которая изменяется при копировании формулы.

Электронные таблицы позволяют эффективно решать задачи сортировки данных.