Всё для Учёбы — студенческий файлообменник
1 монета
pdf

Студенческий документ № 086076 из ЭЭИ

ЛАБОРАТОРНАЯ РАБОТА №1

ФОРМУЛЫ, ФОРМАТ ЯЧЕЕК, ВЫЧИСЛЕНИЯ, АБСОЛЮТНЫЙ И ОТНОСИТЕЛЬНЫЙ АДРЕС

1. Задание 1.1. Создайте таблицу:

Таблица "Среднедневной заработок за январь 2011 г."

№ п.п.Фамилия И.О. ОкладРайонный коэффициент Средний

1 Алексеев И.П. 1234

2 Басов Н.В. 2314

3 Иванов С.Н. 2356 4 Ильина Т.И. 5214

5 Захаров Р.С. 4215

6 Кириллов П.С.3621 7 Кольцова С.В. 3589

10 Яковлев П.Н. 5214 ИТОГО:

Произвести расчеты в таблице по формулам:

• Районный коэффициент = 60% от Оклад.

• Средний = (Оклад + Районный коэффициент) / Календарных дней

Замечание.

Количество календарных дней вынести в отдельную ячейку

• Все вычисления округлять до 2-х знаков после запятой.

• В строке ИТОГО: посчитать среднее значение в колонке Средний.

• Отформатировать таблицу: границы, заливка, установить перенос по словам, выравнивание по центру

• Построить диаграммы разных видов по данным таблицы

2. Методические указания

2.1. Создание книги

Книга Microsoft Office Excel 2007 представляет собой файл, содержащий один или несколько листов, которые можно использовать для организации разнообразной взаимосвязанной информации. Чтобы создать новую книгу, можно открыть пустую (рисунок 1.1).

Рисунок 1.1 - Стартовое окно Microsoft Excel 2007

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

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

Шаблон - Книга, создаваемая и используемая как начальный вариант всех новых книг. Можно создавать шаблоны книг и листов. Используемый по умолчанию шаблон книги называется Книга.xlt. Используемый по умолчанию шаблон листа называется Лист.xlt.

2.2. Открытие новой пустой книги

• Нажмите кнопку Microsoft Office, а затем выберите команду Создать.

• Убедитесь, что в разделе Шаблоны выбран пункт Пустые и последние, и дважды щелкните в правой области списка Пустые и последние пункт Чистая книга.

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

• нажать Кнопку Microsoft Office правой клавишей мыши,

? пункт Настройка панели быстрого доступа,

2.3. Создание новой книги на основе существующей

• Нажмите кнопку Microsoft Office, а затем выберите команду Создать.

• В списке Шаблоны щелкните пункт Из существующего документа.

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

2.4. Создание новой книги на основе шаблона

• Нажмите кнопку Microsoft Office, а затем выберите команду Создать.

• В списке Шаблоны щелкните пункт Установленные шаблоны или Мои шаблоны.

• Выполните одно из следующих действий:

• Чтобы использовать установленный шаблон, в списке Установленные шаблоны щелкните нужный шаблон, а затем выберите команду Создать.

• Чтобы использовать собственный шаблон, на вкладке Мои шаблоны дважды щелкните нужный шаблон.

2.5. Вставка нового листа

Имя (или заголовок) листа отображается на его ярлычке в нижней части экрана. По умолчанию листам присваиваются имена "Лист1", "Лист2" и т. д., но имя любого листа на более подходящее.

Чтобы вставить новый лист, нужно выполнить одно из следующих действий:

• щелкнуть вкладку Вставить лист в нижней части экрана (рисунок 1.2);

Рисунок 1.2 - Вкладка Лист1

• нажать на вкладке Главная в группе Ячейки кнопку Вставить и выбрать команду Вставить лист (рисунок 1.3).

Рисунок 1.3 - Группа Ячейки вкладки Главная

• щелкнуть правой кнопкой мыши ярлычок одного из листов и выбрать команду Вставить.

• На вкладке Общие выделить значок Лист и нажать ОК.

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

• В строке Ярлычок листа (рисунок 1.4) щелкнуть правой кнопкой мыши ярлычок, который требуется переименовать, и выберать команду Переименовать.

• Выделить текущее имя и ввести новое.

Рисунок 1.4

2.7. Удаление одного или нескольких листов

• Выделить лист или листы, которые требуется удалить.

• На вкладке Главная в группе Ячейки (рисунок 1.3) щелкнуть стрелку рядом с командой Удалить, а затем выберать команду Удалить лист.

2.8. Данные, хранимые в ячейке

• Числа (с фиксированным десятичным разделителем или без него),

• текст • даты или время.

2.9. Изменение формата числа

• Выберать ячейку, в которой содержатся нужные числа.

• На вкладке Главная в группе Число (рисунок 1.5) навести указатель на пункт Общий, а затем выбрать в списке нужный формат.

Рисунок 1.5 - Группа Число вкладки Главная

2.10. Форматирование чисел в текстовом формате ? Выбрать пустую ячейку.

• На вкладке Главная в группе Число (рисунок 1.5) навести указатель на пункт Общий, а затем выбрать в списке пункт Текст.

2.11. Обзор формул

Формулы представляют собой выражения, по которым выполняются вычисления на листе. Формула начинается со знака равенства (=). Ниже приведен пример формулы, умножающей 2 на 3 и прибавляющей к результату 5.

=5+2*3

Формула может также содержать такие элементы, как функции.

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

Оператор - знак или символ, задающий тип вычисления в выражении. Существуют математические, логические операторы, операторы сравнения и ссылок.

Константа - постоянное (не вычисляемое) значение. Например, число 210 и текст "Квартальная премия" являются константами. Выражение и результат вычисления выражения константами не являются.

2.12. Элементы формулы

Рисунок 1.6 - Элементы формулы

(1) Функции: функция ПИ() возвращает значение числа пи (3,142...); (2) Ссылки: A2 возвращает значение, хранящееся в ячейке A2; (3) Константы: числовые или текстовые значения, вводимые непосредственно в формулу, например 2; (4) Операторы: оператор ^ (знак крышки) возводит число в степень, а оператор * (звездочка) умножает числа.

2.13. Использование констант в формулах

Константа представляет собой готовое (не вычисляемое) значение. Например, дата

09.10.2008, число 210 и текст "Прибыль за квартал" являются константами.

Выражение, или его значение, константами не являются. Если в формуле в ячейках не содержится ссылок на другие ячейки (например, формула имеет вид =30+70+110), значение в такой ячейке изменяется только после изменения формулы вручную.

2.14. Арифметические операторы

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

Используются следующие арифметические операторы:

2.15. Операторы сравнения

> (знак "больше") Больше A1>B1

= (знак "больше или равно") Больше или равно A1>=B1

(знак "не равно") Не равно A1<>B1

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

Амперсанд (&) используется для объединения нескольких текстовых строк в одну строку.

2.16. Операторы ссылок

Для описания ссылок на диапазоны ячеек используются следующие операторы:

Оператор ссылки Значение Пример

: Ставится между ссылками на первую и B5:B15

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

; Оператор объединения. Объединяет несколько СУММ(B5:B15;D5:D15)

(точка с запятой) ссылок в одну

(пробел) Оператор пересечения множеств, используется B7:D7 C6:C8

для ссылки на общие ячейки двух диапазонов

2.17. Порядок выполнения действий в формулах

Значения обрабатываются формулой в определенном порядке:

• Формула в Microsoft Excel всегда начинается со знака равенства (=).

• Знак равенства свидетельствует о том, что последующие знаки составляют формулу.

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

• Формула вычисляется слева направо в соответствии с определенным порядком для каждого оператора в формуле.

2.18. Приоритет оператора

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

2.19. Использование круглых скобок

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

Пример.

1. Результатом приведенной ниже формулы будет число 11, поскольку в Microsoft Excel умножение выполняется раньше сложения. В данной формуле число 2 умножается на 3, а затем к результату добавляется число 5.

=5+2*3 2. Если же с помощью скобок изменить синтаксис, в Excel будет выполнено сложение 5 и 2, а затем результат будет умножен на 3; результат этих действий равен 21.

=(5+2)*3 3. Скобки вокруг первой части формулы определяют следующий порядок вычислений: определяется значение B4+25, а затем полученный результат делится на сумму значений в ячейках D5, E5 и F5.

=(B4+25)/СУММ(D5:F5)

2.20. Использование функций и вложенных функций в формулах

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

Следующий пример функции ОКРУГЛ (рисунок 1.7), округляющей число в ячейке A10, иллюстрирует синтаксис функции:

Рисунок 1.7

(1) Структура формулы; (2) Имя функции; (3) Аргументы функции; (4) Всплывающая подсказка синтаксиса формулы

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

Имя функции. Чтобы отобразить список доступных функций, щелкните ячейку и нажмите клавиши SHIFT+F3.

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

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

Ссылка на ячейку - координаты, определяющие расположение ячейки на листе. Например, B3 представляет ссылку на ячейку, находящуюся на пересечении столбца B и строки 3.

Всплывающая подсказка аргумента. Всплывающая подсказка с синтаксисом и аргументами появляется после ввода функции. Например, всплывающая подсказка появится после ввода выражения "=ОКРУГЛ". Всплывающие подсказки отображаются только для встроенных функций.

2.21. Ввод функций

Диалоговое окно Мастер функций - шаг 1 из 2 (рисунок 1.8) упрощает ввод функций при создании формул, в которых они содержатся.

Рисунок 1.8

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

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

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

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

Аргумент - Значения, используемые функцией для выполнения операций или вычислений.

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

В следующей формуле (рисунок 1.9) применяется вложенная функция СРЗНАЧ и сравнение результата со значением 50. Функции СРЗНАЧ и СУММ вложены в функцию ЕСЛИ.

Рисунок 1.9 - Пример вложенных функций

2.23. Использование ссылок в формулах

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

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

Ссылки на ячейки других книг называются ссылками или внешними ссылками.

По умолчанию Excel использует стиль ссылок A1, в котором столбцы обозначаются буквами (от A до XFD, всего не более 16 384 столбцов), а строки - номерами (от 1 до 1 048 576). Эти буквы и номера называются заголовками строк и столбцов. Для ссылки на ячейку введите букву столбца, и затем - номер строки. Например, ссылка B2 указывает на ячейку, расположенную на пересечении столбца B и строки 2.

Можно также пользоваться стилем ссылок, в котором нумеруются как строки, так и столбцы. Стиль ссылок R1C1 удобен при вычислении положения столбцов и строк в макросах. В стиле ссылок R1C1 в Microsoft Excel положение ячейки обозначается буквой "R", за которой следует номер строки, и буквой "C", за которой следует номер столбца.

Ссылка Значение

R[-2]C Относительная ссылка на ячейку, расположенную на две строки выше и в том же столбце

R[2]C[2] Относительная ссылка на ячейку, расположенную на две строки ниже и на два столбца правее

R2C2 Абсолютная ссылка на ячейку, расположенную во второй строке и во втором столбце

R[-1] Относительная ссылка на строку, расположенную выше текущей ячейки R Абсолютная ссылка на текущую строку

2.24. Ссылка на другой лист

В приведенном ниже примере функция "СРЗНАЧ" используется для расчета среднего значения диапазона B1:B10 на лист "Маркетинг" той же книги (рисунок 1.10).

Рисунок 1.10 - Пример ссылки в формуле с другого листа

(1) Ссылки на лист "Маркетинг"; (2) Ссылка на диапазон ячеек в промежутке от B1 до B10 включительно; (3) Разделение ссылки на лист от ссылки на диапазон ячеек

2.25. Относительные ссылки

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

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

Например, при копировании или заполнении относительной ссылки (рисунок 1.11) из ячейки B2 в ячейку B3 она автоматически изменяется с =A1 на =A2.

Рисунок 1.11

2.26. Абсолютные ссылки

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

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

Например, при копировании или заполнении абсолютной ссылки (рисунок 1.12) из ячейки B2 в ячейку B3 она остается прежней =$A$1.

Рисунок 1.12

2.27. Смешанные ссылки

Смешанная ссылка содержит либо абсолютный столбец и относительную строку, либо абсолютную строку и относительный столбец.

Абсолютная ссылка столбцов приобретает вид $A1, $B1 и т.д.

Абсолютная ссылка строки приобретает вид A$1, B$1 и т.д.

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

Например, при копировании или заполнении смешанной ссылки (рисунок 1.13) из ячейки A2 в ячейку B3 она изменяется с =A$1 на =B$1.

Рисунок 1.13

2.28. Ошибки в формулах и способы их устранения

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

1. Исправление ошибки #####

1) Столбец недостаточно широк для отображения содержимого

• дважды щелкнуть правую границу заголовка столбца.

2) Сократить содержимое ячейки, чтобы оно поместилось в столбце.

• Выделите столбец.

• На вкладке Главная в группе Ячейки нажать кнопку Формат, нажмите кнопку Формат ячеек, а затем перейдите к вкладке Выравнивание (рисунок 1.3).

• Установить флажок автоподбор ширины.

3) Выберать другой числовой формат

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

4) Значения даты и времени являются отрицательными числами

• Если используется система дат 1900, дата и время в Microsoft Office Excel должны быть положительными величинами.

• При определении интервала дат и времени нужно убедиться в том, что формула составлена правильно.

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

• На вкладке Главная в группе Ячейки нажать кнопку Формат, нажать кнопку Формат ячеек, а затем перейти к вкладке Число.

• Выберите формат, отличный от формата даты или времени.

2. Исправление ошибки #ДЕЛ/0!

Данная ошибка возникает при делении числа на 0.

1) В формуле содержится явное деление на 0 (например, =5/0). Нужно изменить делитель на число, не равное нулю.

2) Используется ссылка на пустую ячейку или ячейку, содержащую ноль в качестве делителя ? Изменить ссылку на ячейку.

• Ввести ненулевое значение в ячейку, используемую в качестве делителя.

• Ввести в ячейку, используемую в качестве делителя, значение #Н/Д. В этом случае ошибка #ДЕЛ/0! изменится на #Н/Д, указывающую, что значение делителя не определено.

• Чтобы ошибка не отображалась, необходимо использовать функцию листа ЕСЛИ.

Например, если ошибка возникает в формуле =A5/B5, можно использовать формулу =ЕСЛИ(B5=0;"";A5/B5). Кавычки означают пустую текстовую строку.

3) Используется макрос, содержащий функцию или формулу, которая возвращает значение #ДЕЛ/0!

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

3. Исправление ошибки #Н/Д

Данная ошибка возникает, если значение недоступно функции или формуле. 1) #Н/Д или НД() были введены вместо недостающих данных

Замените #Н/Д новыми данными.Формулы, содержащие ссылки на эти ячейки будут возвращать значение #Н/Д.

2) В функциях ГПР, ПРОСМОТР, ПОИСКПОЗ или ВПР указано неверное значение аргумента

"искомое_значение"

• Убедитесь, что аргумент "искомое_значение" имеет нужный тип, например, является значением или ссылкой на ячейку, но не ссылкой на диапазон.

3) Для просмотра значений в несортированной таблице используются функции ВПР, ГПР или

ПОИСКПОЗ

По умолчанию сведения для функций просмотра таблиц должны располагаться в возрастающем порядке. Функции ВПР и ГПР содержат аргумент "интервальный_просмотр", позволяющий искать определенное значение в несортированной таблице. Чтобы найти определенное значение, аргумент "интервальный_просмотр" должен иметь значение ЛОЖЬ.

Функция ПОИСКПОЗ содержит аргумент "тип_сопоставления", задающий порядок, в котором список должен быть отсортирован для поиска соответствующего значения. Если невозможно найти соответствующее значение, попробуйте изменить аргумент "тип_сопоставления". Для поиска точного соответствия задайте аргумент "тип_сопоставления" равным 0.

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

• Чтобы исправить эту ошибку, уменьшите диапазон формулы (например,

C1:C10) или увеличьте диапазон, на который ссылается формула (например, A1:A15).

5) Не заданы один или несколько необходимых аргументов стандартной или пользовательской функций листа

• Нужно задать все необходимые аргументы функции.

6) Используется недоступная пользовательская функция

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

7) Используется макрос, вызывающий функцию, которая возвращает значение #Н/Д

• Нужно проверить правильность указания и размещения аргументов в функции.

4. Исправление ошибки #ИМЯ?

Данная ошибка возникает, если Microsoft Excel не может распознать текст в формуле.

1) Используется имя, которое не было определено

2) Убедитесь, что это имя существует. На вкладке Формулы в группе Определенные имена нажмите кнопку Диспетчер имен и посмотрите, указано ли в нем имя. Если имя отсутствует в списке, добавьте его, нажав кнопку Создать.

2) Ошибка в написании имени

? Нужно проверить правописание. Выберите имя в строке формул , нажмите клавишу F3, выберите нужное имя, а затем нажмите кнопку ОК.

3) Ошибка в написании имени функции

? Исправить написание имени. Вставьте правильное имя функции в формулу, нажав кнопку Мастер функций в группе Библиотека функций на вкладке Формулы.

4) В формулу введен текст, не заключенный в двойные кавычки

? Microsoft Excel пытается распознать такую запись как имя, даже если это не предполагалось.

3) Заключите в двойные кавычки содержащийся в формуле текст. Пример правильной записи формулы, объединяющей текстовый фрагмент "Всего: " со значением ячейки B50:

4) ="Всего: "&B50

5) В ссылке на диапазон ячеек пропущено двоеточие (:)

6) Изменен другой лист, не заключенный в ординарные кавычки

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

5. Исправление ошибки #ПУСТО!

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

1) Используется ошибочный оператор диапазона

• Для указания ссылки на непрерывный диапазон ячеек нужно использовать двоеточие (:) в качестве разделителя между первой и последней ячейками диапазона. Например, СУММ(A1:A10) ссылается на диапазон ячеек с A1 до A10 включительно.

• Для создания ссылки на две непересекающиеся области можно использовать оператор объединения, обозначаемый точкой с запятой (;). Например, если формула суммирует два диапазона, между ними должна быть поставлена точка с запятой - СУММ(A1:A10;C1:C10).

2) Диапазоны не имеют общих ячеек

?Изменить ссылки таким образом, чтобы они пересекались.

?При вводе или изменении формулы ссылки на ячейки и границы вокруг соответствующих ячеек выделяются цветом (рисунок 1.14).

Рисунок 1.14

(1) Первая ссылка на ячейку - B3, цвет - синий, и диапазон ячеек имеет синюю границу с квадратными углами; (2) Вторая ссылка на ячейку - C3, цвет - зеленый, и диапазон ячеек имеет зеленую границу с квадратными углами.

?Если ни в одном углу цветной границы нет квадратов, значит, это ссылка на именованный диапазон.

?Квадраты в углах цветной границы означают ссылку на неименованный диапазон.

6. Исправление ошибки #ЧИСЛО!

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

1) В функции с числовым аргументом используется неприемлемый аргумент

? Нужно использовать в функции только числовые аргументы. Например, даже если нужно ввести значение 1 000р., введите в формулу 1000.

2) Используется функция, являющаяся итерацией, например, ВСД или СТАВКА, и невозможно найти результат

• Попробуйте использовать другое начальное приближение для этой функции.

• Измените число итераций при расчете формул в Microsoft Excel (Параметры

Excel).

3) Введена формула, возвращающая числовое значение, которое слишком велико или слишком мало, чтобы его можно было представить в Microsoft Excel

? Изменить формулу так, чтобы результат вычислений находился между

-1*10307 и 1*10307.

7. Исправление ошибки #ССЫЛКА!

Данная ошибка возникает, если ссылка на ячейку указана неверно.

1) Ячейки, на которые ссылаются формулы, были удалены или в эти ячейки было помещено содержимое других скопированных ячеек

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

2) Используется макрос, вызывающий макрофункцию, возвращающую значение #ССЫЛКА! ? Проверить аргументы функции и удостовериться, что они ссылаются на допустимые ячейки или диапазоны ячеек. Например, если макрос вызывает функцию из ячейки, расположенной выше первой строки, отобразится ошибка #ССЫЛКА!, поскольку такой ячейки не существует.

8. Исправление ошибки #ЗНАЧ!

Данная ошибка возникает при использовании недопустимого типа аргументаили операнда.

1) В формулу вместо числа или логического значения (ИСТИНА или ЛОЖЬ) введен текст ? Microsoft Excel не может преобразовать текст в нужный тип данных. Нужно проверить правильность задания типов операндов или аргументов в функции или формуле, а также значений ячеек, на которые ссылается формула. Например, если ячейка A5 содержит число, а ячейка A6 содержит текст "Недоступно", формула =A5+A6 возвратит значение ошибки #ЗНАЧ!.

2) После ввода или редактирования формулы массива нажимается клавиша ВВОД

• Для редактирования формулы укажите ячейку или диапазон ячеек, содержащих формулу массива, нажать клавишу F2 для редактирования формулы, а затем - клавиши CTRL+SHIFT+ВВОД.

3) Ссылка, формула или функция указаны как константа массива

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

4) Для оператора или функции, требующей одного значения, возвращается диапазон ? Ввести вместо диапазона одно значение.

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

5) Используется неправильная матрица в одной из матричных функций листа ? Нужно убедиться в правильном указании размерности матрицы .

6) Используется макрос, вызывающий макрофункцию, в определенных случаях возвращающую значение #ЗНАЧ!

• Необходимо убедиться, что функция не использует недопустимый

аргумент.

ЛАБОРАТОРНАЯ РАБОТА № 2

ПОСТРОЕНИЕ, РЕДАКТИРОВАНИЕ И ФОРМАТИРОВАНИЕ ДИАГРАММ

1. Задание

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

? y ?x2 ? 2x ?1

? x2 y ? 1? x

? 1 y ? x • y?sin x,

• y ?cos x, • . y ?cos2x, x?[? ?;?]

2 2

1.2. Постройте графики функций, заданных в полярных координатах:

• r?2(1? sinj)

• r?cos2j • r?cos 42 j

• r?sin2j? cos2j

2. Методические указания

2.1. Декартова система координат

Для построения графиков функций Y(X) в Microsoft Office Excel используется тип диаграммы Точечная (рисунок 3.1):

Рисунок 3.1 - Диалоговое окно Вставка диаграмма

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

Можно совместить построение нескольких графиков. Такая возможность используется для графического решения систем уравнений с двумя переменными, при проведении сравнения анализа значений y при одних и тех же значениях x.

Пример. Построить графики функций y1= x2 и y2= x3 на интервале [-3; 3] с шагом 0,5. Алгоритм выполнения задания:

1. Заполнить таблицу значений (рисунок 3.2).

2. Выделить таблицу (вместе с заголовками) и указать тип диаграммы Точечная.

3. Выбрать формат точечной диаграммы с гладкими кривыми.

4. В Макете указать название диаграммы (например, "Графики"), дать название осей: X и Y(рисунок 3.3).

Рисунок 3.3

5. Должен получиться график, приведенный на рисунке 3.4.

Рисунок 3.4

Замечание 1.

В версии 97-2003 для получения графика, представленного на рисунке надо провести редактирование.

Замечание 2.

При построении графиков функции необходимо учитывать область допустимых значений (ОДЗ):

- Знаменатель дроби не может быть равным нулю;

- Подкоренное выражение корня четной степени больше или равен нуля;

- Учитывать запрещенные точки тригонометрических функций (tg x, ctg x, ...);

- В логарифмической функции основание больше 1, а логарифмическое выражение больше 0;

- В показательной функции показатель степени не равно 1 и т.п.

2.2. Полярная система координат.

Полярная система координат - система координат, ставящая в соответствие каждой точке на плоскости пару чисел (?;?).

Основными понятиями этой системы являются точка отсчёта - полюс - и луч, начинающийся в этой точке - полярная ось.

Координата ? определяет расстояние от точки до полюса, координата ? - угол между полярной осью и отрезком, соединяющим полюс и рассматриваемую точку. Координата ? берётся со знаком "+", если угол от оси до отрезка вычисляется против часовой стрелки, и со знаком "-" в противоположном случае.

Любая точка в этой системе имеет бесконечное число координат вида (?; ? + 2?n), которым соответствует одна и та же точка при любых целых n. Для полюса ? = 0, а угол ? произвольный.

Иногда допускаются отрицательные значения ?, в этом случае координаты (?; ?) и (?; ? + 2?n) определяют одну и ту же точку плоскости.

Примеры.

1. Уравнение прямой на расстоянии D от полюса: .

D r? cos(j??) 2. Уравнение окружности с центром в полюсе и радиуса R:. r?R

3. Уравнение окружности, проходящей через полюс и радиуса R:

?? 2Rcos(? ?? )

4. Уравнение эллипса с фокусом в полюсе:

. ?? p 1? ?e cos?

5. Уравнения розы с радиусом R: и . Если k - нечетное число, то роза имеет k лепестков; если k -

??Rcosk? ??Rsink?

четное число, то роза имеет 2k лепестков.

6. Уравнение декартового листа: ?? 3a3sin? ?cos3 sin ? ??cos

2.3. Связь полярных координат с декартовыми (формулы перехода) 1). От полярной системы координат к декартовой:

?x?? ?cos , ??y?? ?sin .

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

r? x2 ? y2 ;cosj? x ; sinj? y ;

x2 ? y2 x2 ? y2 y ??, y ?0;

tgj?x (x ?0); j????????2?2 , y ?0. (x ?0)

2.4. Построение в Excel.

1. Для построения графика функции ?(?) потребуется четыре столбика данных ?, ?, x, y. 2. Столбец ? заполняется от 0 до 2? (по заданию) с шагом .

? 12 3. Для заполнения столбцов х и у используется связь между полярной системой

координат и декартовой:

?x?? ?cos , ??y?? ?sin .

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

5. Пример. Построить график функции при ? [0;2?] с шагом .

? ? ?? 2sin cos ?

12 Алгоритм выполнения задания:

1. Заполнить таблицу значений (рисунок 3.5).

2. Выделить столбцы х и у (вместе с заголовками) и указать тип диаграммы Точечная.

3. Выбрать формат точечной диаграммы с гладкими кривыми.

4. В Макете указать название диаграммы (например, "Графики"), дать название осей: X и Y

Рисунок 3.5

5. Должен получиться график, приведенный на рисунке 3.6.

Рисунок 3.6

Замечание 1.

В версии 97-2003 для получения графика, представленного на рисунке надо провести редактирование.

Замечание 2.

При построении графиков функции необходимо учитывать область допустимых значений (ОДЗ)

ЛАБОРАТОРНАЯ РАБОТА № 3

ОРГАНИЗАЦИЯ РАЗВЕТВЛЕНИЯ АЛГОРИТМА

1. Задача

В ячейку "Число" вводится число, в ячейке "Надпись" должна появиться Число Надпись надпись в соответствии с условием:

"До полудня", если число 12.

2. Задача В ячейку "Число" вводится число, в ячейке "Надпись" должна появиться

надпись в соответствии с условием: Число Надпись

"До полудня", если число >= 0 и 12 и 24

3. Задача В столбец C нам необходимо выставить результат экзамена, который должен содержать всего два варианта: Сдал или Не сдал. Те, кто набрал более 45 баллов - сдали экзамен, остальные нет.

4. Задача

Функции ЕСЛИ можно вкладывать друг в друга, если необходимо расширить варианты принятия решений в Excel. Например, для рассмотренного ранее случая переаттестации сотрудников, требуется проставить не результат, а оценку из ряда: Отлично, Хорошо и Удовлетворительно.

Оценка Отлично ставится при количестве баллов более 60, оценка Хорошо при более 45 и оценка Удовлетворительно в остальных случаях.

Методические указания

2.1. Функция ЕСЛИ

Возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ.

Синтаксис:

ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь)

Лог_выражение - любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ.

Например, A10=100 - логическое выражение; если значение в ячейке A10 равно 100, это выражение принимает значение ИСТИНА, а в противном случае - значение ЛОЖЬ. Этот аргумент может использоваться в любом операторе сравнения.

Значение_если_истина - значение, которое возвращается, если аргумент "лог_выражение" имеет значение ИСТИНА. Аргумент "значение_если_истина" может быть формулой.

Значение_если_ложь - значение, которое возвращается, если "лог_выражение" имеет значение ЛОЖЬ. Аргумент "значение_если_ложь" может быть формулой.

Пример 1.

=ЕСЛИ(A2B2;"Превышение бюджета";"ОК") Проверяет строку на превышение бюджета (рисунок 5.2).

Рисунок 5.2

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

Если балл Категори равен я

Больше 89 A

От 80 до 89 B

От 70 до 79 C

От 60 до 69 D Меньше 60 F

=ЕСЛИ(A2>89;"A";ЕСЛИ(A2>79;"B";ЕСЛИ(A2>69;"C";ЕСЛИ(A2>59;"D";"F")))) (см. Рисунок

5.3). Рисунок 5.3

2.2. Функция И

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

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

И(логическое_значение1, [логическое_значение2], ...) Функция И имеет аргументы, указанные ниже:

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

Логическое_значение2. Необязательный аргумент. Дополнительные проверяемые условия, вычисление которых дает значение ИСТИНА или ЛОЖЬ. Условий может быть не более 255.

Пример 4.

=ЕСЛИ(И(1= 200;

"Наценка составляет " & 100 * ВПР(A2; A2:D6; 4; ЛОЖЬ) &"%"; "Стоимость меньше 200р.") Если стоимость изделия больше или равна 200р., отображается строка "Наценка составляет nn%"; в противном случае отображается строка "Стоимость меньше 200р." (наценка составляет 30%) = ЕСЛИ(ВПР(A3; A2:D6; 3; ЛОЖЬ) >= 200;

"Наценка составляет: " & 100 * ВПР(A3; A2:D6; 4;

ЛОЖЬ) &"%"; "Стоимость составляет " & ВПР(A3; A2:D6; 3; ЛОЖЬ)) Если стоимость изделия больше или равна 200р., отображается строка "Наценка составляет nn%"; в противном случае отображается строка "Стоимость составляет n,nn" (стоимость составляет 35,6) Пример 3

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

Рисунок 6.3

Формула Описание (результат) =ЦЕЛОЕ(ДОЛЯГОДА(ДАТА(2004;6;30); ВПР(5;A2:E7;5; ЛОЖЬ); 1)) Вычисление для 2004 финансового года возраста сотрудника под номером 5. Функция ДОЛЯГОДА используется для вычитания даты рождения из конечной даты финансового года и отображения результата в виде целого числа с помощью функции ЦЕЛОЕ (49) =ЕСЛИ(ЕНД(ВПР(5;A2:E7;2;ЛОЖЬ)) =

ИСТИНА; "Сотрудник не найден"; ВПР(5;A2:E7;2;ЛОЖЬ)) При наличии сотрудника под номером 5 отображается его фамилия; в противном случае отображается сообщение "Сотрудник не найден" (Песоцкий).

Функция ЕНД возвращает значение ИСТИНА, если функция

ВПР возвращает значение ошибки #НД =ЕСЛИ(ЕНД(ВПР(15;A3:E8;2;ЛОЖЬ)) =

ИСТИНА; "Сотрудник не найден"; ВПР(15;A3:E8;2;ЛОЖЬ)) При наличии сотрудника под номером 15 отображается его фамилия; в противном случае отображается сообщение "Сотрудник не найден" (Сотрудник не найден).

Функция ЕНД возвращает значение ИСТИНА, если функция

=ВПР(4;A2:E7;3;ЛОЖЬ) & " " &

ВПР(4;A2:E7;2;ЛОЖЬ) & " - это " &

ВПР(4;A2:E7;4;ЛОЖЬ) & "."

ВПР возвращает значение ошибки #НД

Для сотрудника под номером 4 значения трех ячеек объединяются в целое предложение (Дарья Попкова - это торговый представитель.)

2 2

Показать полностью… https://vk.com/doc151473428_451772802
2 Мб, 7 октября 2017 в 8:08 - Россия, Москва, ЭЭИ, 2017 г., pdf
Рекомендуемые документы в приложении