Методичка (Андреева) информатика для ИФБиБТ

УДК 004.4(07)

ББК

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

Составители: Андреева Н. М.

Информатика и современные информационные технологии. Обработка данных в среде электронных таблиц [Текст] / Н. М. Андреева – Красноярск : Сиб. федер. ун-т, 2013. – 66 с.

Настоящее пособие содержит методические указания к выполнению работ по практической части дисциплины «Информатика и современные информационные технологии» по разделу «Информационные технологии в среде электронных таблиц».

Изложены основные сведения о выполнении расчетов и построении диаграмм в электронных таблицах MS Excel. Приемы работы в электронных таблицах демонстрируются на примерах табуляции функций, заданных в явном и параметрическом виде. Информационная таблица «Биогеоклиматические параметры» из прилагаемого файла служит для демонстрации работа функций рабочего листа.

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

УДК 004.4(07)

ББК

© Сибирский федеральный университет, 2013

2

ПРЕДИСЛОВИЕ

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

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

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

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

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

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

3

1.Расчетные сценарии электронных таблиц

1.1.Структура данных. Формулы. Вставка примечаний

Ввод данных, вычисления, графическое представление данных в MS Excel выполняются на рабочем листе. Рабочий лист – таблица, в которой 16384 столбца и 1048576 строк. Имя столбца – латинские буквы, одна или несколько, например: A, B, C, Z, AA, AB, AC, AZ, BA, BB, ZZ, AAA, XFD.

Допускается использование как строчных, так и прописных букв, например верные имена столбцов: dА, xBe. Cтроки таблицы пронумерованы, номера – целые числа от 1 до 1048576. Местоположение ячейки таблицы определяется именем столбца и номером строки: В2, G256, db5678.

Экран монитора – подвижное окно просмотра рабочего листа. На экране отражается та часть рабочего листа, которая уместилась на экране, полосы прокрутки позволяют перемещать окно просмотра по листу. Графические объекты (диаграммы, рисунки) располагаются поверх рабочего листа, они свободно передвигаются по пространству листа: скользят за курсором мыши, могут прикрыть часть заполненных ячеек, не изменяя их. Существует возможность расположения диаграммы на отдельном рабочем листе, в таком случае на этом листе располагается только одна диаграмма.

Каждый рабочий лист имеет имя, которое присваивается автоматически (Лист1, Лист2, и т.д.). Имена можно менять. Рабочие листы объединяются в одну рабочую книгу, количество рабочих листов в книге – по желанию пользователя. Каждая рабочая книга хранится в отдельном файле. Имя файла определяется автоматически (Книга1.xlsx), при желании файл переименовывают. По умолчанию имена файлов Microsoft Excel 2007 имеют расширение .xlsx или .xlsm. Расширение .xlsx присваивается файлам, которые не содержат макросов, а расширение .xlsm – для файлов с макросами.

Структурная единица рабочего листа – ячейка. В ячейке таблицы хранятся данные: текст, число или формула. Тип данных, хранящихся в ячейке, распознается по форме записи.

Аргументы в формулах – текст, числа, даты, функции или ссылки. Ссылка определяет адрес ячейки, где находится нужное значение. Адрес ячейки определяется именем столбца и номером строки, где расположена ячейка. Например, адрес крайней левой верхней ячейки – А1, адрес крайней правой нижней ячейки – XFD1048576.

Различаются адреса ячеек абсолютные, относительные и смешанные. Абсолютный адрес ячейки позволяет определить местоположение нужного значения, в записи абсолютного адреса ячейки перед именем столбца и перед номером строки пишется знак $. Например, $КE$19 или $L$123. Пример записи относительного адреса: XZ2500 или A40. Относительный адрес ячейки определяет не только местоположение ячейки, но и относительное взаиморасположение ячеек-аргументов и ячейки, где располагается формула.

4

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

Смешанный адрес ячейки состоит из абсолютной и относительной части. Примеры смешанных адресов: АВS$19

или $LКK123. В адресе АВS$19 номер столбца – относительная часть ссылки, а номер строки –

абсолютная, в адресе $LKK123 наоборот: относительная часть –

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

Например, в ячейке D3 записана формула: =$C$2+D$2+$E2, ячейку скопировали и перенесли в ячейку С5 (на один столбец влево и на две строки вниз). При переносе формулы =$C$2+D$2+$E2 из ячейки D3 в ячейку С5:

–абсолютный адрес $C$2 не меняется;

–в относительном адресе D$2 номер столбца уменьшается на 1, номер строки не меняется, эта часть адреса «заморожена»;

–в относительном адресе $E2 «заморожен» номер столбца, номер строки увеличится на 2.

В ячейке С5 формула приобретает вид: =$C$2+C$2+$E4.

Еще один пример переноса: формулу =$C$2+D$2+$E2 из ячейки D3 перенесли в ячейку E5 (на один столбец влево и на две строки вниз), в ячейке Е5 формула приобретает вид: =$C$2+E$2+$E4.

Ссылка на ячейку другого рабочего листа или на другую рабочую книгу называется внешней. Перед адресом ячейки (абсолютным, относительным или смешанным) дописывается имя листа с символом «!». Примеры записи внешней ссылки: Лист1!E9 или Данные!$L$19.

Первый символ в записи формулы – знак равенства (=). Операндами в формулах служат: текст, числа, адреса ячеек и функции. Для операций определен приоритет выполнения (табл. 1.1). Операции, равные по приоритету, выполняются в порядке следования в формуле – слева направо.

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

5

Электронные таблицы содержат многочисленный набор функций, которые производят различные типы вычислений. Имя функции обязательно сопровождается круглыми скобками. Если функция не имеет параметров, то скобки следуют друг за другом. Параметры функции перечисляются через точку с запятой (;). Параметрами функции могут быть: текст, число, функция или логико-арифметическое выражение. Мастер функций упрощает вызов функции: сначала он предлагает пользователю выбрать имя функции из списка, а затем определить значения её параметров в интерактивном режиме.

Допускаются два режима просмотра рабочего листа: просмотр формул и просмотр значений. Команда Показать формулы на вкладке Формулы определяет режим просмотра рабочего листа, позволяет просмотреть формулы в ячейках рабочего листа (Ошибка! Источник ссылки не найден.). В

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

6

Рис. 1. 2. Определение режима просмотра рабочего листа

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

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

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

– последовательность цифр, запятая отделяет целую часть от дробной. В денежном формате эта последовательность сопровождается символами р. Научный формат числа включает символ степени и значение порядка. Например запись числа 0,5 в различных форматах: в числовом: 0,5, в научном: 5,00Е-01, и в денежном: 0,5р.

7

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

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

Команды работы с примечаниями собраны в группе Примечания на вкладке Рецензирование линейки управления (рис. 1.3).

Рис. 1. 3. Группа команд Примечания на линейке управления

Для пояснения проведенных вычислений можно добавить примечание к ячейке. Они автоматически отображаются на экране при наведении указателя мыши на ячейку. Контекстное меню ячейки содержит опции работы с примечаниями: Вставить примечание, Удалить примечание, Изменить примечание.

Рис. 1. 4. Отображение примечаний на рабочем листе

Чтобы отобразить или скрыть все примечания рабочего листа выбирают опцию Показать или скрыть примечания. На

рис. 1. 4 в рабочем листе отображены все примечания.

1.2. Табуляция функций и построение диаграмм

Задание 1. При аналитическом способе задания функциональной зависимости каждому значению аргумента из области определения функции x [ x1 , x 2 ] ставится в соответствие значение функции y , вычисляемое по заданной аналитической формуле y y(x) .

8

П о р я д о к в ы п о л н е н и я з а д а н и я

1.Создать рабочую книгу MS Excel. Переименовать рабочий лист Лист1. Для этого выделить ярлык рабочего листа, вызвать мини-меню, затем выбрать опцию Переименовать. Определить новое имя листа – Функция 1.

2.Заполнить диапазон ячеек A1:В2 рабочего листа как указано в табл. 1.3.

рассчитываются значения аргумента х.

4. Рассчитать значения функции y x 2 sin 2 2x x 2 для каждого значения х из диапазона А3:А62. Для этого «растянуть» с помощью маркера автозаполнения ячейку В2, в которой записана формула расчета функции, на диапазон В3:В62.

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

Установить курсор на одну из ячеек диапазона A1:В62, в котором содержатся вычисленные значения аргумента х и функции y. На линейке

9

выделить диаграмму, нажать на левую кнопку мыши для вызова

мини-меню. Затем выбрать опцию

Формат области построения

(рис. 1. 7).

Рис. 1. 7. Переход к форматированию области построения диаграммы

сплошная заливка, цвет – белый

(рис. 1. 8).

Рис. 1. 8. Параметры форматирования области построения диаграммы

10