Обработка данных средствами электронных таблиц

Лекция 13

Обработка данных средствами электронных таблиц

Понятие «Электронные таблицы».

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

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

MS Excel.

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

Файлы с книгами Microsoft Excel имеют расширение xls.

Рабочая область Excel содержит набор листов – такой набор называется книгой. При первом запуске Excel на экране появляется принятая по умолчанию книга (Книга1), из которой отображается первый лист (Лист1). Лист делится на строки и столбцы (листы могут содержать до 65 536 строк и 256 столбцов). Каждому столбцу книги назначается своя буква латинского алфавита, а каждой строке – число. Пересечения строк со столбцами образуют ячейки листа, на которые можно ссылаться по их адресу. Например, ячейка на пересечении столбца А и строки 1 – это ячейка А1.

 

Ввод информации в MS Excel.

Excel разрешает вводить в ячейки следующие виды информации:

• числовые значения (например, числа 15,000, $29.95 и 33 %);

• текстовые значения (например, слова «Итого», «1-й квартал» и «Офис»);

• даты и время суток (например, Янв-96, 11/12/63 или 1:00 РМ);

• примечания, предназначенные для вас или других пользователей (например,                                                                                                          «Этот регион лидирует по объемам продаж»);

• формулы (например, =В5*1.081 или =СУММ(ВЗ:В7));

• гиперссылки на адреса Интернета и другие документы;

• картинки, фотографии, карты и иллюстрации.

 

Ввод числовых значений.

Чтобы ввести в ячейку число, выделите ее с помощью мыши или клавиатуры, наберите число и нажмите клавишу Enter. При вводе число появляется в активной ячейке и в строке формул над листом. Редактировать содержимое ячеек можно как в строке формул, так и по двойному щелчку в самой ячейке.

Числовое значение может быть целым (32), десятичной дробью (499,95), обыкновенной дробью (3/4) или экспоненциальной научной нотацией (4.09Е+13). По умолчанию числовые значения выравниваются по правому краю ячейки.

 

Ввод текстовых значений.

Чтобы ввести в ячейку текстовое значение, выделите ячейку, наберите текст и нажмите клавишу Enter.

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

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

Если вы хотите, чтобы Excel сохранил определенное значение (число, дату или номер) в виде текста, достаточно начать ввод значения с апострофа. Например, если ввести в ячейку '55, там появится число 55, выровненное по левому краю без апострофа.

 

Ввод даты и времени.

Если вы хотите занести в ячейку листа дату или время, воспользуйтесь одним из заранее определенных форматов даты и времени (Формат → Ячейка → Число).

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

 

 

Ввод примечаний.

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

Чтобы изменить имя, которое появляется во время ввода примечания, выберите команду Параметры из меню Сервис, перейдите на вкладку Общие и введите имя пользователя в текстовом поле Имя пользователя.

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

Ячейки, снабженные примечаниями, можно узнать по маленькой красной точке в правом верхнем углу. Чтобы вызвать примечание во время работы с листом, наведите указатель мыши на ячейку с примечанием и задержите до появления всплывающего окна с текстом. Чтобы удалить примечание, выделите ячейку, выберите команду Очистить из меню Правка, а затем команду Примечания из подменю, либо воспользуйтесь командами контекстного меню.

 

Ввод формул.

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

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

Примеры формул: =(А4+В8)*С6, =F7*34+B12, =СУММ(А1:А5)

Excel в ячейке, содержащей формулу, помещает результат вычислений.

Если значения в ячейках, на которые есть ссылки в формулах, меняются, то результат изменится автоматически.

Создание формулы, в которую входят ссылки на ячейки, может происходить:

• набором имени ячейки на клавиатуре;

• выделением нужной ячейки мышью;

• выделением нужной ячейки с помощью клавиатуры.

 

Сохранение рабочей книги.

Для сохранения рабочей книги необходимо вызвать команду Сохранить меню Файл или щелкнуть кнопку . При первом сохранении появляется диалоговое окно Сохранение документа. Чтобы сохранить книгу под другим именем или в другой папке, следует в меню Файл выбрать команду Сохранить как, после чего появляется окно Сохранение документа.

Редактирование листа Excel.

К основным приемам редактирования относятся:

• выделение ячеек и диапазонов;

• очистка и удаление ячеек;

• копирование данных из одной ячейки в другую;

• использование новой панели инструментов Буфер обмена;

• перемещение данных из одной ячейки в другую перетаскиванием;

• добавление к листу новых строк и столбцов;

• отмена и повторение команд.

 

Очистка и удаление ячеек.

Для удаления содержимого группы ячеек просто выделите ее и нажмите клавишу Delete. Командой Очистить из меню Правка Excel вызывает подменю с командами, позволяющими удалить форматирование, содержимое ячеек, примечания или все сразу.

 

Форматирование листа.

К основным приемам форматирования можно отнести:

• форматирование ячеек.

• изменение ширины столбцов и высоты строк.

• условное форматирование.

• использование стилей.

• работа с шаблонами.

• разрывы страниц.

 

Форматирование ячеек.

Команда Ячейки из меню Формат открывает диалоговое окно Формат ячеек. В нем имеется шесть вкладок с атрибутами формата, которыми можно пользоваться для изменения внешнего вида ячеек листа. Самые распространенные команды форматирования выполняются также кнопками панели инструментов Форматирование либо через контекстное меню.

Для изменения формата числовых значений в Excel используется вкладка Число диалогового окна Формат ячеек.

 

Встроенные функции.

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

Аргументы функции записываются в круглых скобках сразу за названием функции и отделяются друг от друга символом точка с запятой «;». Скобки позволяют Excel определить, где начинается и где заканчивается список аргументов.

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

В состав Excel входит свыше 300 функций. Самый простой и удобный способ использования встроенных функций – использование Мастера функций, в котором все функции разделены на несколько категорий.

 

Категория	Назначение
Финансовые	Расчет процентов,              увеличения и       уменьшения            суммы капитала и т. д.
Дата и время	Вычисления с датами и временем.
Математические	Математические и тригонометрические операции вроде тех, которые выполняются на научных калькуляторах.
Статистические	Вычисление средних значений, сумм, распределений и стандартных отклонений.
Ссылки и массивы	Вычисления с таблицами данных.
Работа с базой данных	Работа со списками и внешними базами данных.
Текстовые	Сравнение, преобразование и форматирование текста в ячейках.
Логические	Вычисления, результатом которых может быть значение ИСТИНА или ЛОЖЬ.
Проверка свойств	Определение ошибок, происходящих при вычислениях и значений.

Вызов Мастера функций осуществляется командой Функция меню Вст а вка, либо кнопка

Вставка функции на стандартной панели инструментов.

Одна из самых часто используемых математических функций – функция СУММ, которая автоматически вычисляет сумму значений из выделенного диапазона. Эта функция может иметь до 30 аргументов, причем каждый аргумент может быть числом, адресом ячейки или блока ячеек, содержащих число или формулу. Например,

СУММ(А2; В2:К2; 500; КОРЕНЬ(9))

Функция СУММ игнорирует аргументы, которые ссылаются на пустые ячейки, текстовые или логические значения. Так как СУММ является часто используемой функцией, то на панели инструментов для ввода этой функции есть специальная кнопка – Автосумма.

Отметим еще некоторые математические функции:

ОКРУГЛ – округляет число до указанного количества десятичных знаков;

ОТБР – отбрасывает дробную часть числа;

ЦЕЛОЕ – округляет число до ближайшего меньшего целого.

Из логических функций следует отметить функцию ЕСЛИ, которая определяет действие в случае выполнения какого-либо условия, и имеющая следующий синтаксис:

ЕСЛИ (<условие>; <результат 1>; <результат 2>)

Результат 1 – результат, который должен возвращаться функцией, если условие выполняется.

Результат 2 – результат, который должен возвращаться функцией, если условие не выполняется.

Например:

= ЕСЛИ (СУММ(В1:В6)>0; СУММ(В1:В6); 0)

 

Ошибки в функциях.

Диалоговое окно Мастер функций делает ввод функций относительно несложным. Если вы при вводе сделаете ошибку, то увидите в ячейке код, называемый ошибочным значением. Ошибочное значение начинается со знака (#) и оканчивается обычно восклицательным знаком.

Коды ошибочных значений Мастера функций.

 

Ошибочное значение	Описание
#ДЕЛ/О!	Вы ввели в формулу деление на ноль. Убедитесь, что в формуле нет ссылок на пустые ячейки.
#Н/Д	Возможно, вы пропустили аргумент функции. В ячейке отсутствует значение.
#ИМЯ?	Имя используемого диапазона не найдено в рабочей книге.
#ПУСТО!	Вы попытались использовать пересечение двух областей, не имеющих общих ячеек. Возможно, в одном из аргументов имеются лишние пробелы.
#ЧИСЛО!	В функции использованы неприемлемый аргумент или не сходящиеся итерации, поэтому невозможно получить результат.
#ССЫЛКА!	В формуле содержится недопустимая ссылка на ячейку.
#ЗНАЧ!	В качестве аргумента использована ячейка, содержащая текст.
#####	Результат вычисления не помещается в ячейку. Увеличьте ширину столбца.

 

Планирование диаграммы.

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

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

• внедренные диаграммы – помещается на том же рабочем листе, где и данные, по которым они построены;

• диаграммы в формате полного экрана на новом рабочем листе.

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

Необходимо также выбрать тип создаваемой диаграммы. Excel поддерживает 14 основных типов диаграмм, каждый из которых может иметь несколько разновидностей.

На подготовительном этапе, прежде, чем приступить к созданию диаграммы, следует ответить на следующие вопросы:

• Какие данные будут представлены диаграммой.

• В какой документ войдет диаграмма (отчет, презентация и т.д.)

• Как должна выглядеть диаграмма (тип).

 

Создание диаграммы.

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

Лекция 13

Обработка данных средствами электронных таблиц