Тема 3.3. Электронные таблицы

Цель:Сформировать знания у студентов об электронных таблицах.

План лекции:

1. Понятие – электронная таблица.

2. Основные понятия электронных таблиц.

3. Ячейки и их адресация. Диапазон ячеек.

4. Ввод, редактирование и форматирование ячеек.

5. Вычисления в электронных таблицах. Формулы.

6. Аргументы функции.

7. Ссылки в формулах.

8. Операторы в формулах.

9. Диаграммы.

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

Основные понятия электронных таблиц.

Документ Excel называется рабочая книга. Рабочая книга представляет собой набор рабочих листов, каждый из которых имеет табличную структуру и может содержать одну или несколько таблиц. В окне документа в программе Excel отображается только текущий рабочий лист, с которым и ведется работа. Каждый рабочий лист имеет название, которое отображается на ярлычке листа, отображаемом в его нижней части. С помощью ярлычка можно переключаться к другим рабочим листам, входящим в ту же самую рабочую книгу. Чтобы переименовать рабочий лист, надо дважды щелкнуть на его ярлычке, ввести новое название и нажать ENTER. Рабочий лист состоит из строк и столбцов. Столбцы озаглавлены прописными латинскими буквами и, далее, двухбуквенными комбинациями. Всего рабочий лист может содержать до 256 столбцов, пронумерованных от A до IV. Строки последовательно нумеруются цифрами, от 1 до 65536 (максимально допустимый номер строки).

Ячейки и их адресация.

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

Диапазон ячеек.

На данные, расположенные в соседних ячейках, можно ссылаться в формулах, как на единое целое. Такую группу ячеек называют диапазоном. Наиболее часто используют прямоугольные диапазоны, образующиеся на пересечении группы последовательно идущих строк и группы последовательно идущих столбцов. Диапазон ячеек означают, указывая через двоеточие номера ячеек, расположенных в противоположных углах прямоугольника, например, А1:С15. Если требуется выделить прямоугольный диапазон ячеек, то это можно сделать протягиванием мыши от одной угловой ячейки до противоположной по диагонали. Чтобы выбрать столбец или строку целиком, следует щелкнуть на заголовке столбца (строки). Протягиванием указателя по заголовкам можно выбрать несколько идущих подряд столбцов или строк.

Ввод, редактирование и форматирование ячеек.

Отдельная ячейка может содержать данные, относящиеся к одному из трех типов: текст, число, формула, - а также оставаться пустой. Тип данных, размещаемых в ячейке, определяется автоматически при вводе. Ввод формулы начинается с символа «=».

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

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

Вычисления в электронных таблицах. Формулы.

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

О функциях в формулах

Функции — заранее определенные формулы, которые выполняют вычисления по заданным величинам, называемым аргументами, и в указанном порядке. Эти функции позволяют выполнять как простые, так и сложные вычисления. Например, функция ОКРУГЛ округляет число в ячейке A10.

Структура функции начинается со знака равенства (=), за ним следует имя функции, открывающая скобка, список аргументов, разделенных запятыми, закрывающая скобка. Для появления списка доступных функций щелкните ячейку и нажмите клавиши SHIFT+F3.

Аргументы функции.

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

Ввод формул.

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

Вложенные функции.

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

Допустимые типы вычисляемых значений. Вложенная функция, используемая в качестве аргумента, должна вычислять соответствующий этому аргументу тип данных. Например, если аргумент должен быть логическим, то есть иметь значение либо ИСТИНА, либо ЛОЖЬ, то вложенная функция в результате вычислений тоже должна давать логическое значение либо ИСТИНА, либо ЛОЖЬ. Иначе появится сообщение об ошибке «#ЗНАЧ!».

Ограничение количества уровней вложения функций.

В формулах можно использовать до семи уровней вложения функций. Когда функция Б является аргументом функции А, функция Б находится на втором уровне вложенности.

О ссылках в формулах

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

Ссылка на другой лист в той же книге: Лист1!А1. Обратите внимание на то, что имя листа и восклицательный знак (!) предшествуют ссылке на диапазон ячеек.

Относительные ссылки. Относительная ссылка в формуле, например A1, основана на относительной позиции ячейки, содержащей формулу, и ячейку, на которую указывает ссылка. При изменении позиции ячейки, содержащей формулу, изменяется и ссылка. При копировании формулы вдоль строк и вдоль столбцов ссылка автоматически корректируется. По умолчанию в новых формулах используются относительные ссылки. Например, при копировании относительной ссылки из ячейки B2 в ячейку B3, она автоматически изменяется с =A1 на =A2.

Абсолютные ссылки. Абсолютная ссылка ячейки в формуле, например $A$1, всегда ссылается на ячейку, расположенную в определенном месте. При изменении позиции ячейки, содержащей формулу, абсолютная ссылка не изменяется. При копировании формулы вдоль строк и вдоль столбцов абсолютная ссылка не корректируется. По умолчанию в новых формулах используются относительные ссылки, и для использования абсолютных ссылок надо выбрать соответствующий параметр. Например, при копировании абсолютной ссылки из ячейки B2 в ячейку B3, она остается прежней =$A$1.

Смешанные ссылки. Смешанная ссылка содержит либо абсолютный столбец и относительную строку, либо абсолютную строку и относительный столбец. Абсолютная ссылка столбцов приобретает вид $A1, $B1 и т. д. Абсолютная ссылка строки приобретает вид A$1, B$1 и т. д. При изменении позиции ячейки, содержащей формулу, относительная ссылка изменяется, а абсолютная ссылка не изменяется. При копировании формулы вдоль строк и вдоль столбцов относительная ссылка автоматически корректируется, а абсолютная ссылка не корректируется. Например, при копировании смешанной ссылки из ячейки A2 в ячейку B3, она изменяется с =A$1 на =B$1.

Об операторах в формулах

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

Типы операторов

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

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

Копирование содержимого ячеек.

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

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

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

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

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

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

Заполнение ряда ячеек числами, датами либо другими элементами. Microsoft Excel может автоматически продолжать заполнение прогрессии числами, комбинациями чисел и текста, датами и временем, основываясь на установленном образце. Например, в таблице ниже приведены примеры продолжения начальных значений. Элементы, разделенные запятыми, находятся в соседних ячейках.

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

Автозаполнение формулами.

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

Построение диаграмм и графиков.

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

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

Тип диаграммы.

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

Выбор данных.

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

Оформление диаграммы. Размещение диаграммы.

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

Редактирование диаграммы.

Готовую диаграмму можно изменить. Она состоит из отдельных элементов, таких, как сами графики (ряды данных), оси координат, заголовок диаграммы, область построения и прочее. При щелчке на элементе диаграммы он выделяется маркерами, а при наведении на него указателя мыши – описывается стандартной подсказкой. Открыть диалоговое окно для форматирования элемента диаграммы можно через меню Формат (для выделенного элемента) или через контекстное меню (команда Формат). Различные вкладки открывшегося диалогового окна позволяют изменять параметры отображения выбранного элемента данных.

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

Чтобы удалить диаграмму, можно удалить рабочий лист, но котором она расположена (Правка – Удалить лист), или выбрать диаграмму, внедренную в рабочий лист с данными, и нажать клавишу DELETE.

Контрольные вопросы к лекции:

1. Что такое электронная таблица?

2. Какие основные понятия электронных таблиц?

3. Что такое ячейки и их адресация?

4. Что такое диапазон ячеек?

5. Как производится ввод, редактирование и форматирование ячеек?

6. Как производится вычисление в электронных таблицах?

7. Какие бывают ссылки в формулах?

8. Какие операторы в формулах вы знаете?

9. Что такое диаграммы и как их строить?

Практические задания для самостоятельной работы:

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

2. Определение порядка действий для выполнения задания.

3. Представление и защита выполненной работы

Лекция 11