Формулы и функции

Операции с ячейками, ссылки

Электронная таблица и модель ячейки

Некоторые из этих вопросов рассмотрены в данной лекции.

План лекции

1. Электронная таблица и модель ячейки

2. Интерфейс программы MS Excel 2003

3. Операции с ячейками, ссылки

4. Формулы и функции

5. Диаграммы

6. Термины и определения

7. Типовые тестовые задания

Среди основных тем, рассматриваемых обычно при изучении программы работы с электронными таблицами MS Excel 2003, представлены следующие [5, 6, 7]:

· Технология обработки информации на основе электронных таблиц.

· Программа MS Excel 2003 (назначение, версии, интерфейс, меню, панель задач и панели инструментов, сервисные возможности, работа с ее файлами и с системой помощи).

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

· Вычисления в MS Excel 2003 (создание и редактирование формул, адресация ячеек, категории функций, правила работы с формулами и функциями, простейшие математические и статистические функции, поиск и исправление ошибок в вычислениях).

· Сортировка и фильтрация данных. Поиск нужной информации. Работа с диапазонами ячеек. Специальная вставка и автозаполнение.

· Диаграммы MS Excel 2003 (типы и мастер диаграмм, оформление элементов и областей диаграмм).

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

· Связь MS Excel 2003 с другими офисными пакетами (с MS Word 2003 и с MS Access 2003). XML в MS Excel 2003.

· Приемы и примеры практической работы с данными в электронных таблицах (например, обработка данных социологического исследования и компьютерного тестирования).

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

Модель ячейки в ЭТ имеет пять следующих уровней. Изображение на экране – это отформатированные значения - числа, текст, результат вычисления формул. Скрытое форматирование – это форматы чисел (показаны ниже на рис. 10), тип шрифта, вид рамки, уровень защиты ячейки. Формула, которая состоит из чисел, значков и математических выражений. Имя ячейки, однозначно идентифицирующее каждую конкретную ячейку. Комментарий (примечание) - это текст, который через дополнительное окно связывается с ячейкой. При этом в правом верхнем углу ячейки показывается красная точка.

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

2. Интерфейс программы MS Excel 2003

Программа MS Excel 2000/2003 является наиболее распростаненной в мире для работы с ЭТ на персональных компьютерах. Она имеет все самые современные компоненты и режимы, работает как самостоятельная программа или в составе пакета MS Office 2003. Файлы с ЭТ имеют расширение xls.

Интерфейс программы MS Excel 2003 показан на рис. 11. Элементами интерфейса являются:

· Строка заголовка.

· Строка меню.

· Панель инструментов.

· Панель форматирования.

· Поле имени ячейки, строка формул.

· Заголовок столбца и заголовок строки.

· Ярлычки листов.

· Полосы прокрутки и строка состояния.

Рис. 11. Интерфейс программы MS Excel 2003

Файл MS Excel 2003 называется рабочей книгой. Она состоит из листов, имена которых (Лист1, Лист2, … ) выведены на ярлыках в нижней части окна рабочей книги. Лист ячеек это 256 столбцов и 16384 строки. Заголовок столбца обозначается буквами A, B, C... Z, AA, AB,... AZ..., а заголовок строки нумеруется 1, 2, 3 и т.д. Щелкая по ярлыкам, можно переходить от листа к листу внутри рабочей книги.

Имеется д ва режима просмотра для работы(меню Вид):

· Обычный – наиболее удобный для выполнения большинства операций.

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

Настройка содержимого и вида панелей инструментов MS Excel 2003 выполняется через меню. Можно сделать кнопки цветными, большими, с подсказками.

В ЭТ можно вставлять строки и столбцы (в т.ч. и с форматированием), несколько столбцов или строк. Можно удалять строки и столбцы целиком. Можно их копировать, вырезать и вставлять, используя, например, типовые комбинации клавиш:

CTRL+INS - скопировать объект в буфер.

SHIFT+DEL - вырезать объект в буфер.

SHIFT+INS - вставить объект из буфера в таблицу.

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

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

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

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

Правая кнопка мышки над выделенным объектом ЭТ дает контекстно-зависимое меню (вырезать, копировать, вставить, удалить, очистить, форматировать). Специальная вставка позволит для объекта работать по выбору - вставить все или только формулы, значения, форматы, примечания.

Важнейший этап работы с ЭТ - это оформление листов. При этом можно выбирать через меню (формат/ячейки) по закладкам - формат чисел, выравнивание, шрифт, размер, вид, стиль, защита (см. рис 9). Рекомендуется соблюдать следующие правила - не более 2-х, 3-х типов и 2-х размеров штифтов на 1 странице, не увлекаться при назначении стилей (жирно, курсив, подчеркивание) и при выборе цвета и узоров, чтобы не перегружать таблицу.

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

Каждая ячейка таблицы имеет адрес, который состоит из имени строки и имени столбца. Например, если ячейка находится в столбце F и строке 7, то она имеет адрес F7.

Ссылкой однозначно определяется ячейка или группа ячеек листа, а также упрощается поиск значений или данных, используемых в формуле. Ссылки на ячейки других книг называются внешними ссылками. Ссылки на данные других приложений называются удаленными ссылками. По умолчанию в MS Excel 2003 используются ссылки A1, в которых столбцы обозначаются буквами от A до Z (256 столбцов максимально), а строки числами - от 1 до 65536). Чтобы указать ссылку на ячейку, введите букву заголовка столбца, а затем номер строки. Например, D50 является ссылкой на ячейку, расположенную в пересечении столбца D с 50-й строкой.

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

Чтобы сослаться на:	Введите:
Ячейку столбца A и 10-й строки	A10
Диапазон ячеек столбца A с 10-й строки по 20-ю	A10:A20
Диапазон ячеек в 15-й строке со столбца B по столбец E	B15:E15
Все ячейки в 5-й строке	5:5
Все ячейки между 5-й и 10-й строками включительно	5:10
Все ячейки в столбце H	H:H
Все ячейки между столбцами H и J включительно	H:J

Можно воспользоваться стилем, в котором и столбцы, и строки листа пронумерованы. Этот стиль, называемый R1C1, наиболее полезен при вычислении позиции строки и столбца в макросах, а также при отображении относительных ссылок. В стиле R1C1, после буквы «R» указывается номер строки ячейки, после буквы «C» — номер столбца.

В зависимости от поставленной задачи можно использовать относительные ссылки, которые ссылаются на ячейки относительно позиции формулы, или абсолютные ссылки, которые ссылаются всегда на одну и ту же ячейку. Если необходимо, чтобы ссылки не изменялись при копировании формулы в другую ячейку, воспользуйтесь абсолютными ссылками. Для создания абсолютной ссылки на ячейку C1, поставьте знак доллара ($) перед той частью, которая не должна изменяться. Чтобы создать абсолютную ссылку на ячейку C1, поместите знак доллара так, как показано в примере: =A5*$C$1

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

В Excel используются операторы (сложение, вычитание, умножение, деление, возведение в степень, вычисление процентов), знаки (равно, меньше, больше, меньше или равно, больше или равно, не равно), связи (диапазон и объединение). Синтаксис формулы - это порядок, в котором вычисляются значения. Последовательность выполнения операторов в формуле (иерархия) такая - связи, отрицания, проценты, возведение в степень, точечные вычисления, линейные вычисления, текст, сравнение.

Расчеты начинается с вставки и редактирования формул. Используется такая технология. Выберите ячейку для формулы, например, С8. Знак =. Щелчок мышкой на С5, затем +, щелчок на С7. Окончание ввода формулы по клавише Enter. Вычисление содержимого ячейки - в строке формул это записи типа =С5+С6+С7 (здесь знак равенства означает, что далее записана формула) или типа =СУММ(D4:D7), что в обоих случаях означает суммирование содержимого ячеек из диапазона.

Функции (это запрограммированные формулы, позволяющие проводить часто встречающиеся последовательности вычислений) включаются либо через пиктограмму " Сумматор " либо через " Интеграл " и тогда запускается диалог с " Мастером функций " (см. рис. 12 и 13).

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

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

Рис. 12. Мастер функций MS Excel 2003 (шаг 1 из 2)

Рис. 13. Мастер функций MS Excel 2003 (шаг 2 из 2)

Библиотека функций программы MS Excel 2003 [5, 6, 7, 8] насчитывает около 330 различных функций, объединенных в следующие тематические группы:

· Функции баз данных.

· Функции даты и времени.

· Инженерные функции.

· Финансовые функции.

· Информационные функции.

· Логические функции.

· Функции ссылки и автоподстановки.

· Арифметические и тригонометрические функции

· Статистические функции.

· Текстовые функции.

Дата добавления: 2014-01-07