Помощничек
Главная | Обратная связь


Археология
Архитектура
Астрономия
Аудит
Биология
Ботаника
Бухгалтерский учёт
Войное дело
Генетика
География
Геология
Дизайн
Искусство
История
Кино
Кулинария
Культура
Литература
Математика
Медицина
Металлургия
Мифология
Музыка
Психология
Религия
Спорт
Строительство
Техника
Транспорт
Туризм
Усадьба
Физика
Фотография
Химия
Экология
Электричество
Электроника
Энергетика

Правила синтаксиса при записи функций



Лабораторная работа № 1.3.

(3 – 4 балла)

Линейные вычислительные процессы. Построение графиков и линий тренда

Цель работы

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

· Познакомиться с использованием функций в Ехсеl.

· Познакомиться с форматами чисел.

· Научиться строить и редактировать графики функций.

· Научиться осуществлять защиту информации в таблице.

· Научиться распечатывать лист в целом и отдельную таблицу.

Необходимые программные и технические средства

· Персональный компьютер, желательно подключенный к сети Интернет.

· Тип операционной системы – Windows.

· MS Office версии 97-2003 и выше.

Общие сведения

Относительная ссылка (адресация) указывает на ячейку, основываясь на ее положении относительно ячейки, в которой находится формула. Пусть в ячейку В4 введена формула =В1+В2, Excel интерпретирует ее как "прибавить содержимое ячейки, расположенной тремя рядами выше, к содержимому ячейки двумя рядами выше". При копировании формулы =В1+В2 из ячейки В4 в С4, Exсеl также интерпретирует формулу как "прибавить содержимое ячейки, расположенной тремя рядами выше, к содержимому ячейки двумя рядами выше". Таким образом, формула в ячейке С4 изменит свой вид на =С1 +С2.

Абсолютная адресация. Иногда при копировании формул необходимо сохранить ссылку на КОНКРЕТНУЮ ячейку (область), т.е. на основе фиксированного положения ее на листе, например, “ячейка находится в столбце В и в строке 12”. В этом случае необходимо воспользоваться абсолютной адресацией. Для ее задания необходимо перед буквой колонки и перед номером ряда напечатать символ $. Например, $В$4 или $С$2:$F$48 и т.д.

Смешанная адресация. Символ $ ставится только там, где он необходим, например В$4 или $С2. Тогда при копировании один параметр адреса изменяется, а другой нет.

С помощью клавиши F4 можно быстро изменить тип ссылки, например, в ячейке А1 введена формула =В1+В2, и точка вставки находится перед В2. Если нажать F4, то ссылка изменяется на абсолютную: =В1+$B$2. Еще одно нажатие F4 приведет к тому, что ссылка станет смешанной: =B1+B$2. Еще одно нажатие F4 реверсирует ссылку: =B1+$B2.Если снова нажать F4, то произойдет возврат к исходной относительной ссылке.

Имена

Использование имен ячеек и блоков в формулах вместо ссылок повышает читабельность и упрощает анализ. Присвоить имя ячейке или блоку можно двумя способами:

· Выделить ячейку или блок, перейти в поле имени строки формул и набрать в нем нужное имя. Окончание ввода – нажатие клавиши Enter.

· После выделения ячейки или блока выполнить команду Вставка, Имя,Присвоить. В появившемся диалоговом окне Присвоение имени в нижней части будет выведен абсолютный адрес ячейки или блока, который содержит в себе имя рабочего листа, например: =Лист3!$A$1:$A$21 (выделен блок ячеек). В текстовое поле Имя необходимо ввести требуемое имя и нажать кнопку Ok.

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

Задание

1. Составить программу для расчета 21 аргумента Х функции y=f(x) с шагом h.

1. Исходную функцию y=f(x) разбить на три части путём введения промежуточных переменных. Например, так: у1=φ(х), у2=ψ(х), у = у12. Решение должно быть получено в виде таблицы.

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

Варианты заданий:

 

Вариант Выражение Вариант Выражение
y=ln sin x-
y=6+tg
y=ln x +sin x +

 

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

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

Ознакомьтесь с общими сведениями о предмете лабораторной работы (см. выше в описании данной работы) и рекомендуемыми дополнительными материалами.

Уясните цель работы.

Подготовьте необходимые программные и технические средства (см. выше в описании данной работы).

Приступайте к выполнению работы:

1. Откройте файл Л.р. по Excel.xls. Перейдите на Лист2. Присвойте ему имя «4». Создайте основную и вспомогательную таблицы.

1.1 Заполните шапку основной таблицы, начиная с ячейки А1.

Шапка – это верхняя строка таблицы с названием столбцов (см. постановку задачи).

· В ячейку А1 занесите № (название первого столбца таблицы).

· В ячейку В1 занесите Х (название второго столбца таблицы).

· В ячейку С1 занесите Y1=cos2x (название третьего столбца таблицы) и т.д. Индекс установите в разделе Видоизменение вкладки Шрифт диалогового окна Формат ячеек путем выбора соответствующего флажка. Окно Формат ячеек активизируется командой Формат, Ячейки после выделения символа, используемого в качестве индекса, в строке формул.

· Установите ширину столбцов такой, чтобы надписи были видны полностью.

1.2 Заполните вспомогательную таблицу начальными исходными данными, начиная с ячейки Н1: Х0 - начальное значение X.

 

 

Присвойте ячейкам H2 и I2 имена, расположенные в строке 1. Для этого надо:

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

· Выполнить команду Вставка, Имя, Создать.

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

Рис. 1. Диалоговое окно Создание имен

 

1.3. Заполните столбец А числами от 1 до 21, начиная с ячейки А2 и заканчивая ячейкой А22. Эту задачу можно решить двумя способами. Согласно первому способу:

· ввести в А2 цифру 1. Затем щелкнуть по кнопке Ввод в строке формул, если не была нажата клавиша Enter;

· выполнить команду Правка, Заполнить, Прогрессия. В диалоговом окне Прогрессия в разделе Расположение выбрать переключатель по столбцам. Значение шага оставить без изменения, в поле Предельное значение ввести 21 и нажать Ok (рис. 2).

 

Рис. 2. Диалоговое окно Прогрессия

 

Второй способ:

· ввести в ячейку А1 число 1, в ячейку А2 число 2, выделить эти две ячейки;

· указатель в правый нижний угол выделенного блока – на экране указатель приобретает вид черного перекрестия и называется маркером заполнения;

· нажать кнопку мыши и, не отпуская ее, переместить указатель до ячейки А22;

· отпустить кнопку мыши – блок А1:А22 будет заполнен цифрами от 1 до 21.

1.4. Заполните столбец В значениями X.

В ячейку В2 занесите =$Н$2 или =Х0. Это означает, что в ячейку В2 заносится значение из ячейки Н2 (начальное значение X), знак $ указывает на абсолютную адресацию.

В ячейку ВЗ занесите =В2+$I$2 или В2+Шаг.

Это означает, что начальное значение Х будет увеличено на величину шага, которая берется из ячейки I2.

Заполните этой формулой ячейки В4: В22.

Столбец заполнится значениями Х от 0 до 4 с шагом 0,2.

1.5. Заполните столбец С значениями функции Y1=Сos2x, рассчитанными по значениям аргумента в ячейках В2:В22.

В ячейку С2 занесите =Сos(B2)^2.

Заполните этой формулой ячейки С3:С22. Для этого надо выделить эту ячейку, нажать на маркер заполнения и, не отпуская левую кнопку мыши, переместить указатель до ячейки С22. Весь столбец заполнится значениями функции Y1=cos2x.

1.6. Заполните столбец D значениями функции Y2=2ln(х2+1).

В ячейку D2 занесите =2*ln(В2^2-1), скопируйте эту формулу на ячейки D3:D22.

1.7. Заполните столбец Е значениями функции Y=Y1-Y2 путем копирования формулы =$C2-$D2.

2. Понаблюдайте за изменениями в основной таблице при смене данных во вспомогательной. Для этого:

2.1. Введите в ячейку Н2 число -3,14159, а в ячейку 12 занесите 0,1.

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

2.2. Верните исходные значения во вспомогательной таблице: х0=0 и шаг=0,2.

3. Оформите основную и вспомогательную таблицы.

3.1. Вставьте 2 пустые строки сверху для оформления заголовков.

Установите курсор на первую строку.

Выполните команду Вставка, Строки (2 раза).

3.2. Занесите заголовки: в ячейку А1 – Таблицы, в ячейкуА2 – Основная, в ячейку H2 - Вспомогательная.

3.3. Объедините ячейки А1:J1 и центрируйте заголовок Таблицы.

Выделите блок А1:J1.

Используйте кнопку на панели инструментов ОБЪЕДИНИТЬ И ПОМЕСТИТЬ В ЦЕНТР.

3.4. Аналогичным образом центрируйте заголовки: Основная и Вспомогательная.

3.5. Оформите заголовки определенными шрифтами.

Шрифты

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

-тип шрифта и размер; - начертание (Жирный, Курсив, Подчеркивание).

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

Заголовку "Таблицы" установите шрифт Times New Roman Cyr, высота шрифта 14, жирный.

Используйте кнопки панели инструментов форматирование:

Заголовкам "Основная" и "Вспомогательная" установите шрифт Times New Roman Суг, высота шрифта 12, жирный.

Для шапок таблиц установите шрифт Times New Roman Суг, высота шрифта 12, курсив.

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

3.7. Произведите выравнивание надписей шапок по центру.

Выравнивание

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

3.8. Выполните обрамление основной и вспомогательной таблиц.

Обрамление

Для задания обрамления используется кнопка на панели Форматирование . Можно использовать также команду меню Формат, Ячейки или выбрать аналогичную команду из контекстного меню. Затем перейти на вкладку Граница.

3.9. Задайте фон заполнения первой строки – салатный, заголовков таблиц – желтый, заголовков столбцов таблиц – светло-серый.

Фон

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

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

Экран после выполнения работы должен иметь вид, представленный на рис.3.

 

Рис. 3. Таблица значений функции у=cos2х –2ln(x2+1) имен

 

4. Сохраните результаты своей работы на диске рабочего компьютера, в личном каталоге. Имя файла Л.р. по Excel.хls

5. Завершите работу с Excel.

6. В файле Л.р. по Excel.хls на рабочем листе «4», защитите в таблице информацию, которая не изменяется (заголовки, полностью основная таблица, шапка вспомогательной таблицы).

Защита листа

MS Excel позволяет защитить книгу, лист и отдельные строки и столбцы. При защите можно использовать пароль. Защита делает невозможным изменение информации, пока она не отключена. Обычно защищают данные, которые не должны изменяться (расчетные формулы, заголовки, шапки таблиц).

Для защиты листа выполните команду СЕРВИС, ЗАЩИТА, ЗАЩИТИТЬ ЛИСТ. Откроется окно диалога Защита листа (рис. 4).

 

Рис. 4. Диалог команды Защитить лист

 

 

Установленные флажки показывают, какие компоненты листа защищены от изменений.

Введите пароль и нажмите ОК. Лист защищен от внесения изменений по тем компонентам рабочего листа, которые выбраны в разделе Разрешить всем Пользователям этого листа:. Пока лист защищен, все выбранные функции будут недоступны. Защита листа снимается командой СЕРВИС, ЗАЩИТА, СНЯТЬ ЗАЩИТУ ЛИСТА. Если при установке защиты был введен пароль, его необходимо ввести, иначе защита не будет снята.

При выполнении защиты листа можно выполнить отключение защиты с части ячеек. Для этого нужно:

· Выделить блок.

· Выполнить команду ФОРМАТ, ЯЧЕЙКИ и выбрать вкладку ЗАЩИТА, а затем снять флажок Защищаемая ячейка.

· Выполнить команду СЕРВИС, ЗАЩИТА, ЗАЩИТИТЬ ЛИСТ.

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

Защита книги

Установка защиты книги производится с командой СЕРВИС, ЗАЩИТА, ЗАЩИТА КНИГИ. При этом открывается окно диалога Защита книги (рис. 5). Защита снимается командой СЕРВИС, ЗАЩИТА, СНЯТЬ ЗАЩИТУ КНИГИ. Установка флажка Структураозначает, что листы книги будут защищены от перемещения, удаления, вставки.

 

Рис. 5. Диалог команды Защита книги

 

В защищенном листе выделите блок Н4: I4, выполните команду формат, ячейки, защита и снимите флажок защищаемая ячейка.

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

Попробуйте изменить значения в ячейках:

· • В ячейке А4 с 1 на 10 (это невозможно).

· • Значение шага во вспомогательной таблице с 0,2 на 0,5. В основной таблице произошел пересчет.

· • Верните начальное значение шага 0,2.

7. Исследование зависимостей.

Выделите ячейку H4 и выполните команду Сервис, Зависимости, Зависимые ячейки, а затем ту же команду после выделения ячейки I4. От ячеек Н4 и I4 к ячейкам столбца Х протянутся синие стрелки. Теперь можно проследить всю цепочку расчетов. При анализе можно использовать также команду Сервис, Зависимости, Влияющие ячейки, которая позволяет определить, значения каких ячеек влияют на результат в текущей. В этом же меню есть команда Источник ошибок, которая позволяет определить, значения каких ячеек вызывают появление ошибки в текущей ячейке.

Убрать стрелки можно командой Сервис, Зависимости, Убрать все стрелки.

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

8. Снимите защиту с листа с помощью команды сервис, защита, снять защиту листа.

9. Познакомьтесь с функциями пакета Exсеl.

Функции

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

Правила синтаксиса при записи функций

MS Excel имеет мощный аппарат встроенных функций. Их число превышает 200. Все они разбиты для удобства их поиска на категории:

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

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

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

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

Ряд функций не имеет аргументов. Это, например, функции ПИ() и СЕГОДНЯ(). Первая возвращает число ПИ=3,1415926536…, а вторая - текущую дату.

Названия функции и значения аргументов можно вводить в строке формул. Однако целесообразнее использовать специальное средство для работы с функциями – Мастер функций. Вызывается Мастер функций командой меню вставка, функция или кнопкой , в результате чего на экране появится окно, представленное на рис. 2.6, в котором нужно выполнить описанные выше действия.

 

Рис. 6. Первый шаг диалога Мастера функций

 

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

9.1. Нажмите кнопку . Выберите катего-рию Полный алфавит-ный перечень. Пос-мотрите, каким ар-сеналом функций располагает Ехсеl.

9.2. Подсчитайте сумму вычисленных значений Y и запишите ее в ячейку E25.

· • Сделайте текущей ячейку E25. Щелкните по кнопке .

· • В ячейку D25 запишите поясняющий текст “Сумма =” (рис. 8).

9.3. Оформите нахождение среднего арифметического вычисленных значений Y.

· • Сделайте текущей ячейку E26. Щелкните по кнопке .

· •В диалоговом окне Мастер функции — шаг 1 из 2 выберите категорию функций СТАТИСТИЧЕСКИЕ и имя функции СРЗНАЧ.

· • Щелкните по кнопке ОК.

· • В появившемся диалоговом окне (рис. 7) укажите диапазон, в котором вычисляется среднее значение (F4: F24).

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

Занесите в ячейку D26 поясняющий текст, а в E26 – результат расчета среднего значения.

9.4. Оформите нахождение минимального и максимального значений у. Для этого занесите в ячейки D27 и D28 поясняющий текст, а в ячейки E27 и E28 – рассчитанные минимальное и максимальное значения.

Если имя функции известно, то ее можно вставить в формулу без использования Мастера функций, например =МАКС(E4:E24).

10. Оформите блок ячеек D25 : E28.

10.1. Установите границы блоку ячеек D25:E28.

10.2. Поясняющие подписи в ячейках D25:Е28 оформите жирным шрифтом типа Times New Roman Cyr с выравниванием по центру.

Рис. 7. Диалоговое окно функции для вычисления среднего значения

Экран после выполнения данной части работы должен иметь вид, представленный на рис 8. Строки с 5-ю по 20-ю скрыты.

11. Познакомьтесь с форматами чисел в Ехсеl.

Форматы числа

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

 

Рис. 8. Расчет статистических показателей

 

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

Однако, зачастую, общий формат может не устраивать при оформлении результатов расчетов. Excel предоставляет пользователю применять 10 встроенных форматов и, кроме того, возможность создавать пользовательские форматы. Для каждого формата можно установить размер и начертание шрифта с использованием различных эффектов: выравнивание, использование цветовой палитры, условных операторов. Для того, чтобы узнать, каким форматом отформатирована ячейка, надо нажать комбинацию клавиш Ctrl+1, в окне диалога Формат ячеек указатель устанавливается на строке с названием текущего формата ячеек (рис. 9).

Встроенные форматы

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

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

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

· в финансовом формате нельзя назначить красный цвет для отрицательных величин;

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

 

Рис. 9. Установка числового формата

 

В ячейку можно ввести число в виде рациональной дроби, например, 12/56. Для этого надо сначала ввести ноль и пробел, а затем число в виде рациональной дроби, например: 0 12/56. Тот же результат будет, если после ввода числа в виде десятичной дроби выбрать в списке Числовой пункт Дробный, а затем из списка Тип - пункт Дробями до двух цифр. Там же можно увидеть и другие варианты отображения числа в виде рациональной дроби.

Аналогично назначаются встроенные форматы для даты, времени и т.д.

Введите в ячейку В1 число 3 целых 17/28. После выбора в списке Числовые форматы пункта Все форматы можно увидеть как Excel построил этот формат: #” “??/??.

В ячейку С1 введено любое число. Выберите в окне диалога Формат ячеек формат Числовой. Установите флажок Разделитель групп разрядови введите Число десятичных знаковравное 3. В разделе Отрицательные числа установите отображение отрицательных чисел красным цветом. После нажатия ОК в списке Все форматы появится форматный код вида 0, 000_ ;[Красный]_-0,000\. Он работает следующим образом. Когда в ячейке присутствует положительное число, работает первая секция формата 0, 000_ . При вводе отрицательного числа работает вторая секция [Красный]_0,000 и отрицательные числа отображаются красным цветом.

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

 




Поиск по сайту:

©2015-2020 studopedya.ru Все права принадлежат авторам размещенных материалов.