Технология обработки числовой информации. Электронные таблицы MS Excel. Основные приёмы работы

Обработка информации –
преобразование одних «информационных объектов» (структурных данных) в другие путём выполнения некоторых алгоритмов

Табличный процессор –
это прикладная программа, предназначенная для организации табличных вычислений на компьютере.
Вычислительная таблица, которая создаётся с помощью табличного процессора, называется электронной таблицей
Первый табличный процессор был создан в 1979году, предназначался для компьютеров типа Apple II и назывался VisiCalc. В 1982 году появился табличный процессор Lotus 1-2-3, предназначенный для IBM PC. Lotus объединил в себе возможности электронных таблиц, деловую графику и некоторые функции реляционной СУБД. Практически все последующие табличные процессоры (Multiplan, QuattroPro, SuperCalc и др.) поддерживали эти три функции. Одним из самых популярных табличных процессоров сегодня является MS Excel, входящий в состав пакета Microsoft Office.

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

Электронная таблица (ЭТ) –
это своеобразная компьютерная технология организации табличных расчётов.
В основе ЭТ лежат несколько главных идей.

Первая идея –
рабочее поле (бесконечная книга)структурировано по образцу шахматной доски.
ЭТ подобно шахматной доски. ЭТ подобно шахматной доске разделена на клетки.
Строки таблицы (по вертикали) пронумерованы числами, а столбцам (по горизонтали) присвоены буквенные имена.

Клетки-ячейки
именуются подобно клеткам шахматной доски: А1, В2 – адрес ячейки

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

Занесение формулы выполняется по алгоритму : начало «=»формула для вычисления на стандартной панели f(x) для окончания операция клавиша «ENTER»
Сразу после занесения формулы в ячейку табличный процессор её вычисляет автоматически и отражает в ячейке полученное значение.
При изменении значений в ячейках-операндах мгновенно происходит пересчёт формул.

Третья идея –
принцип относительной адресации. Адрес ячейки, присутствующий в формуле, обозначает её расположение относительно ячейки, в которой записана формула.

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

Для отмены действия принципа относительной адресации используется символ «$». С помощью этого символа можно сделать неизменным (абсолютным) как весь адрес, так и отдельную его часть (строку или столбец).
Абсолютная адресация

Табличный процессор MS Excel позволяет:
1. Решать математические задачи: выполнять разнообразные табличные вычисления, вычислять значения функций, строить графики и диаграммы и т.п.;
2. Осуществлять численное исследование (Что будет, если? Как сделать, чтобы?);
3. Проводить статистический анализ; связи.

4. Реализовать функции базы данных – ввод, поиск, сортировку, фильтрацию (отбор) и анализ данных;
5. Устанавливать защиту на отдельные фрагменты таблицы, делать их невидимыми;
6. Наглядно представлять данные в виде диаграмм и графиков;
7. Вводить и редактировать тексты;
8. Осуществлять обмен данными с другими программами, например, вставлять текст, рисунки, таблицы, подготовленные в других приложениях;
9. Осуществлять многотабличные связи.
Табличный процессор MS Excel позволяет:

Диапазон (блок) ячеек –
выделенные смежные ячейки, образующие прямоугольный участок таблицы;

Адрес диапазона (блока) ячеек –
определяется адресом верхней левой и нижней правой ячейки, разделенных двоеточием (:), B2:C7 → B2, B3, B4, B5, B6, B7, C2, C3, C4, C5, C6, C7

Книга –
документ электронной таблицы, состоящий из листов, объединенных одним именем и являющихся файлом

Лист –
рабочее поле, состоящее из ячеек

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

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

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

MS Excel – база данных
В современных табличных процессорах, например, в Microsoft Excel, в качестве базы данных можно использовать список (набор строк таблицы, содержащий связанные данные). При выполнении обычных операций с данными, например, при поиске, сортировке или обработке данных, списки автоматически распознаются как базы данных. Перечисленные ниже элементы списков учитываются при организации данных:
• столбцы списков становятся полями базы данных;
• заголовки столбцов становятся именами полей базы данных;
• каждая строка списка преобразуется в запись данных.

Типы данных в ячейках электронной таблицы. Правила записи арифметических операций
Ячейки рабочего листа электронной таблицы могут содержать:
исходные или первичные данные – константы;
производные данные, которые рассчитываются с помощью формул или функций.
Данные в ячейках таблицы могут относиться к одному из следующих типов: текст, числа, даты, формулы и функции

Текст
последовательность букв, иногда цифр или некоторых специальных символов

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

Дата и время
вводятся в ячейки электронной таблицы как числа и выравниваются по правому краю.

Формулой
в электронной таблице называют алгебраические и логические выражения.
Формулы всегда начинаются со знака равенства (=) и вводятся в латинском регистре.
Например: =А5*2/В1

Функция
представляет собой программу с уникальным именем, для которой пользователь должен задать конкретные значения аргументов.
Функции могут вводиться в таблицу в составе формул либо отдельно.
Например, функция суммирования имеет вид =СУММ(А1:А4)

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

Мастер функций
MS Excel содержит более 400 встроенных функций.
Имена функций можно набирать в любом регистре – верхнем или нижнем.
Для облегчения работы с встроенными функциями используется Мастер функций

Форматирование элементов таблицы. Формат числа
Форматированием называется изменение внешнего оформления таблиц и данных в них.
Важно! Excel различает форматирование всей ячейки и форматирование содержимого ячейки.
К форматированию ячеек относится: изменение шрифта содержимого ячеек, выравнивание данных в ячейках, представление чисел в разных форматах, оформление границ ячеек, и т.д. Для того чтобы изменить формат ячейки необходимо щелкнуть на ней и выполнить команду Формат—Ячейки. Появившееся диалоговое окно Формат Ячеек, позволит изменить формат всей ячейки.

Для выравнивания данных следует:
щелкнуть на форматируемой ячейке или выделить диапазон ячеек, содержащих выравниваемый текст;
выполнить команду Формат—Ячейки. На закладке Выравнивание установить опцию Переносить по словам, которая позволяет располагать текст одной ячейки в несколько строк.

Для оформления предварительно выделенных ячеек с помощью рамок
следует выполнить команду Формат—Ячейки.
На закладке Граница следует выбрать тип линии для рамки, а затем щелкнуть на кнопке с нужным видом рамки.
(Возможно поочередное назначение нескольких видов рамок).

Для назначения цветного фона
предварительно выделенным ячейкам необходимо выполнить команду Формат—Ячейки.
На закладке Вид выбрать цвет фона ячеек и нажать кнопку ОК.

Для изменения шрифта предварительно выделенного
текста или числа, находящихся в нескольких ячейках, необходимо выполнить команду Формат—Ячейки.
На закладке Шрифт следует указать тип шрифта (Arial, Times New Roman и т. д.), начертание (жирный, курсив, подчеркнутый), размер шрифта, цвет и т.д.

Для изменения формата чисел,
находящихся в выделенном диапазоне ячеек необходимо выполнить команду Формат—Ячейки.
С помощью закладки Число можно установить один из следующих форматов чисел: денежный, процентный, дата, текстовый и т.д.

Ошибки в формулах в Microsoft Excel
При проведении расчетов в электронной таблице Microsoft Excel иногда в ячейках вместо ожидаемых значений выводятся различные “непонятные” результаты. Сегодня мы попытаемся сделать их для нас понятными.

Ячейки заполнены знаками
# # # # # # # # #
Ячейка может заполняться набором символов “решетки” (#) по одной из следующих причин:
1. ширина столбца недостаточна для размещения численного значения, для которого установлен формат Числовой, Денежный или Финансовый. Такая ситуация встречается, например, в ячейке, в которую скопирована формула из другой ячейки, для которой был установлен один из перечисленных форматов. Для исправления ошибки необходимо либо расширить ячейку, либо изменить формат представления данных в ней;
2. ячейка содержит формулу, возвращающую некорректную дату или время. Excel, например, не поддерживает даты до 1900 года или отрицательные значения времени. Увеличение ширины ячейки данную ошибку не исправляет.

Ошибка # ДЕЛ/0!
О том, что делить на ноль нельзя, знают даже учащиеся начальных классов. Но тем не менее такая ситуация в Excel встречается довольно часто. Например, на листе, подготовленном для решения линейного уравнения вида ax + b = 0:
— формула в ячейке B4, имеющая вид =–B3/B2, вернет значение ошибки #ДЕЛ/0! в ситуации, когда значение коэффициента а в ячейке B2 еще не задано. Для исключения вывода ошибки можно воспользоваться функцией ЕСЛИ, которая проверяет наличие “неправильного” значения в ячейке B2:
=ЕСЛИ(B2=0; ““; –B3/B2)
Данная формула выводит “пустое” значение (““), если ячейка пустая или содержит 0; в противном случае выводится вычисленное значение корня уравнения.
Другое решение заключается в использовании функции ЕСЛИ для проверки существования любой ошибки. Следующая формула также отображает “пустое” значение в случае получения ошибки любого типа:
=ЕСЛИ(ЕОШИБКА(–B3/B2); ““; –B3/B2)
С особенностями функции ЕОШИБКА ознакомьтесь самостоятельно.

Ошибка Имя?
Как известно, в Excel можно использовать в формулах не только адреса отдельных ячеек или диапазонов, но и их имена. Это облегчает понимание формул. Например, вы согласитесь, что формула нахождения корня линейного уравнения, оформленная в виде:
=ЕСЛИ(a=0; ““; –b/a)
— где a и b — имена ячеек, в которых задаются значения коэффициентов уравнения a и b соответственно, выглядит гораздо понятнее приведенного ранее варианта.
Ошибка Имя? возникает в следующих случаях:
1. формула содержит неопределенное имя ячейки или диапазона. Здесь же следует отметить одну особенность Excel. Если удалить имя какой-то ячейки или какого-то диапазона, используемое в формуле, то в самой формуле оно останется (казалось бы, Excel мог бы преобразовать имена в ссылки на соответствующие ячейки, однако этого почему-то не происходит);
2. формула содержит текст, который Excel интерпретирует как неопределенное имя. Например, ошибка в написании имени функции приводит к отображению ошибки Имя?.

Ошибка # Знач!
Ошибка весьма распространена и встречается, как правило, в следующих ситуациях.
1. Формула пытается провести операцию, используя некорректные данные (например, пытается сложить число и текстовое значение). Если на листе в ячейке B7 (см. фрагмент листа ниже) была записана формула =B3+B4+B5+B6, которая затем была распространена (скопирована) на ячейки C7 и D7, то для приведенных исходных данных в ячейке будет выведен результат #Знач! (ясно, что складывать числа и текст нельзя).

Использование функции СУММ позволит найти сумму числовых значений в диапазоне, в котором имеются и текстовые значения: =СУММ(D3:D6). Это означает, что в ячейке B7 можно записать формулу =СУММ(B3:B6) и распространить (скопировать) ее на ячейки C7 и D7.
2. В качестве аргументов функции использованы данные несоответствующего типа (например, в функции ЕСЛИ вместо условия использовано число или текст).
3. В качестве аргумента функции используется диапазон, тогда как аргументом должна быть отдельная ячейка. Пример ошибочного оформления: =КОРЕНЬ (A3:A6).

Ошибка # Число!
Ошибка #Число! возникает в одном из двух случаев:
1) для функции, использующей числовой аргумент, задан аргумент другого типа. Пример ошибочного оформления: =КОРЕНЬ(И3);
2) формула содержит слишком большое или слишком маленькое значение. Excel поддерживает величины в пределах
от 1Е-307 до 1Е307 (от 10-307 до 10307).

Ошибка # Ссылка!
Ошибка #Ссылка! возникает в случае использования формулой ошибочной ссылки на ячейку. Данная ошибка может встречаться в следующих ситуациях.
1. Ячейка, на которую ссылалась формула, была удалена. Например, следующая формула отображает ошибку #Число!, если удалена строка 20, столбец A или столбец B:
=A20/B20
2. Формула скопирована в новое место, где относительные ссылки на ячейки становятся недействительными. Например, если формулу =A1–1 в ячейке A2 скопировать в ячейку A1, формула вернет ошибку #Число!, так как в ней будет присутствовать ссылка на несуществующую ячейку.
Содержимое ячейки с формулой было вырезано (командой Вырезать) и затем вставлено в ячейку, на которую ссылается формула.