Лабораторная работа 8
Вычисления в Excel
1. Прочитайте теоретическую информацию по теме:
Формулы
Все вычисления в Excel выполняются с помощью формул. В качестве знаков математических и логических операций используются следующие символы операций:
| Операция | Символ оператора |
| Сложение | + |
| Вычитание | - |
| Умножение | * |
| Деление | / |
| Возведение в степень | ^ |
| Изменение порядка вычислений | () |
| Равно | = |
| Не равно | <> |
| Больше чем | > |
| Меньше чем | < |
Любая формула начинается со знака равно (=) и может содержать:
· числа
· имена ячеек
· знаки операций
· имена функций
Например:
=3+F5
=(В2+В3)^2
Создание формул
Для создания формулы необходимо выделить ячейку, в которой будет содержаться результат вычисления. Затем формулу можно ввести с клавиатуры и нажать клавишу Enter. Иногда при вводе формул имена ячеек можно указывать щелчком левой кнопки мыши на нужной ячейке или выделением диапазона ячеек, при этом имена ячеек автоматически включаются в формулу.
Относительные и абсолютные адреса ячеек
При копировании формулы из одной ячейки в другую ссылки на ячейки изменяются автоматически. Например, при копировании формулы =F4*G5-D1 на одну ячейку вниз получим новую формулу =F5*G6-D2, а при копировании на одну ячейку вправо – формулу =G4*H5-E1.
Если при копировании ссылка на ячейку изменяется, то адрес называется относительным. Иногда необходимо, чтобы при копировании адрес ячейки не изменялся, поэтому используются абсолютные адреса ячеек. Абсолютной ссылке предшествует знак доллара, например, $E$6. Знак $ перед буквой Е сообщает, что нельзя изменять столбец, а знак $ перед числом 6 – что нельзя изменять строку. При вводе формулы с клавиатуры надо просто ввести знак $ перед адресами строки и столбца или, выбрав ячейку, нажать клавишу F4, что добавит к строке и столбцу знак $.
|
|
Можно создавать смешанные ссылки, в которых некоторые ссылки на адреса ячеек абсолютные, а некоторые - относительные. Например, $E5, E$5. При этом ссылка изменяется только по вертикали или по горизонтали.
Функции
Для вычислений в формулах используются стандартные функции.
Величины, которые используются для вычисления значений функций, называются аргументами. Величины, которые являются результатом вычисления функций, называются возвращаемыми значениями. Последовательность, в которой должны располагаться аргументы функции, называется синтаксисом функции.
Функция может иметь один аргумент или несколько - список аргументов.
Аргументы состоят из чисел, текста, логических величин, массивов (диапазонов) или ссылок, которые должны иметь допустимые для данного аргумента значения. Аргументы могут быть как константами, так и формулами. Эти формулы, в свою очередь, могут содержать другие функции. Функции, являющиеся аргументом другой функции, называются вложенными.
Функции являются формулами, поэтому все они начинаются со знака «=», после идет имя функции, затем аргументы, заключенный в скобки. Например, =СУММ(B4:C8).
Функция суммирования (СУММ) часто используется в электронных таблицах, поэтому для нее в панели инструментов предусмотрена специальная кнопка со значком S и некоторые другие.
Вместо того, чтобы набирать функции "вручную", можно щелкнуть на кнопке со значком ¦x в панели инструментов или выбрать функцию из списка в строке формул. На экране появится диалоговое окно Мастера функций. С его помощью можно ввести и отредактировать любую функцию.
|
|
Математические функции
ABS(число)
ACOS(число)
ASIN(число)
ATAN(число)
COS(число)
EXP(число)
LN(число)
LOG(число; основание)
LOG10(число)
sin(число)
tan(число)
градусы(число) – преобразует радианы в градусы
корень(число)
пи()
произвед(число1;число2; …)
радианы(число) – преобразует градусы в радианы
римское(число; форма) – пре6образует арабские числа в римские в текстовом формате
степень(число; степень)
сумм(число1; число2; …)
фактр(число)
Статистические функции
срзнач(число1;число2; …)
макс(число1;число2; …)
мин(число1;число2; …)
Текстовые функции
СЦЕПИТЬ (текст1; текст2;...)
Объединяет несколько текстовых строк в одну.
Текст1, текст2,... — это от 1 до 30 элементов текста, объединяемых в один элемент текста. Элементами текста могут быть текстовые строки, числа или ссылки, которые ссылаются на одну ячейку.
|
|
& - склейка
длстр(текст) – кол-во символов в строке
ЗАМЕНИТЬ(старый_текст;нач_поз;число_знаков;новый_текст)
Старый_текст — текст, в котором желательно заменить некоторые знаки.
|
|
Нач_поз — позиция знака в тексте старый_текст, начиная с которой знаки заменяются текстом нов_текст.
Число_знаков — число знаков в тексте старый_текст, которые заменяются текстом новый_текст.
Новый_текст — текст, который заменяет знаки в тексте старый_текст.
значен(текст) – преобразует текст в число
НАЙТИ(искомый_текст;просматриваемый_текст;нач_позиция) – находит позицию вхождения, прописные и строчные буквы различаются
Искомый_текст — это искомый текст.
Просматриваемый_текст — это текст, включающий искомый текст.
Нач_позиция — это позиция знака, с которой следует начинать поиск. Первый знак в аргументе просматриваемый_текст имеет номер 1. Если аргумент нач_позиция опущен, то он полагается равным 1.
повтор(текст; число повторений)
поиск(искомый_текст;просматриваемый_текст;нач_позиция) – прописные и строчные буквы не различаются
ЛЕВСИМВ (текст;количество_знаков)
Количество_знаков — количество знаков, извлекаемых функцией ЛЕВСИМВ.
· Количество_знаков должно быть больше либо равно нулю.
· Если количество_знаков больше длины текста, то функция ЛЕВСИМВ возвращает весь текст.
· Если количество_знаков опущено, то предполагается, что оно равно 1.
Пример (ЛЕВСИМВ)
Чтобы этот пример проще было понять, скопируйте его на пустой лист.
|
|
ПРАВСИМВ()
СОВПАД (текст1; текст2)
Сравнивает две строки текста и возвращает значение ИСТИНА, если они в точности совпадают, и ЛОЖЬ в противном случае. Функция СОВПАД учитывает регистр, но игнорирует различия в форматировании. Функция СОВПАД используется для того, чтобы проверить, входит ли некоторый текст в документ.
|
|
Логические функции
Функция "И" возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА; возвращает значение ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ.
Синтаксис:
И (логическое значение 1; логическое значение 2;...)
Функция "ИЛИ" возвращает ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА; возвращает ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ.
Синтаксис:
ИЛИ (логическое значение1; логическое значение2;...)
Функция "НЕ" меняет на противоположное логическое значение своего аргумента. Функция НЕ используется в тех случаях, когда необходимо быть уверенным в том, что значение не равно некоторой конкретной величине.
Синтаксис:
НЕ (логическое значение)
Функция "ИСТИНА" возвращает логическое значение ИСТИНА.
Синтаксис:
ИСТИНА (логическое значение)
Функция "ЛОЖЬ" возвращает логическое значение ЛОЖЬ.
Синтаксис:
ЛОЖЬ (логическое значение)
Функция "ЕСЛИ" возвращает одно значение, если логическое выражение при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ.
Синтаксис:
ЕСЛИ (лог. выражение; значение если истина; значение если ложь)
Пример: ЕСЛИ (D4>0;D4;ABS(D4)) возвращает значение ячейки D4, если оно положительное, его модуль, если отрицательное.
При решении некоторых задач значение ячейки необходимо вычислять одним из нескольких способов, в зависимости от выполнения или невыполнения одного или нескольких условий.
Между объектами можно устанавливать некоторые отношения. Например, о двух числах можно сказать, что они равны друг другу, что одно из них больше другого или что одно из них меньше другого. То есть можно сказать, что два числа находятся в отношении, соответственно равенства, больше и меньше. В электронных таблицах основным объектом является ячейка, поэтому между числовыми значениями ячеек могут быть установлены вышеперечисленные отношения. Отношения >, <, =, >=, <= называют простыми условиями. Формулы, содержащие логическую функцию ЕСЛИ, называются условными, остальные - безусловными.
Функции даты и времени
Примечание:
Microsoft Excel хранит даты в виде последовательных чисел. По умолчанию дате 1 января 1900 года соответствует порядковый номер 1, а 1 января 2003 года — 37622, так как интервал между этими датами в днях равен 37622. Microsoft Excel сохраняет время в виде десятичной дроби (время является частью даты).
Поскольку даты и значения времени представляются числами, их можно складывать и вычитать, а также использовать в других вычислениях. При использовании основного формата для ячеек, содержащих дату и время, можно отобразить дату в виде числа или время в виде дробной части числа с десятичной точкой.
год(число)
месяц(число)
день(число)
ДЕНЬНЕД(число;тип)
Тип — это число, которое определяет тип возвращаемого значения.
| Тип | Возвращаемое число |
| 1 или опущен | Число от 1 (воскресенье) до 7 (суббота). Аналогично предыдущей версии Microsoft Excel. |
| Число от 1 (понедельник) до 7 (воскресенье) | |
| Число от 0 (понедельник) до 6 (воскресенье) |
сегодня()
тдата()
Ссылки и массивы
ПРОСМОТР (искомое_значение; просматриваемый_вектор; вектор_результатов)
Искомое_значение — это значение, которое функция ПРОСМОТР ищет в первом векторе. Искомое_значение может быть числом, текстом, логическим значением, именем или ссылкой, ссылающимися на значение.
Просматриваемый_вектор — интервал, содержащий только одну строку или один столбец. Значения в аргументе просматриваемый_вектор могут быть текстами, числами или логическими значениями.
Важно! Значения в аргументе просматриваемый_вектор должны быть расположены в порядке возрастания:..., -2, -1, 0, 1, 2,..., A-Z, ЛОЖЬ, ИСТИНА; в противном случае функция ПРОСМОТР может вернуть неверный результат. Тексты в нижнем и верхнем регистре считаются эквивалентными.
Вектор_результатов — интервал, содержащий только одну строку или один столбец. Он должен быть того же размера, что и просматриваемый_вектор.
Заметки
- Если ПРОСМОТР не может найти искомое_значение, то подходящим считается наибольшее значение в аргументе просматриваемый_вектор, которое меньше, чем искомое_значение.
- Если искомое_значение меньше, чем наименьшее значение в аргументе просматриваемый_вектор, то функция ПРОСМОТР возвращает значение ошибки #Н/Д.
|
|
Как и в средстве проверки грамматики, в Microsoft Excel для проверки правильности формул используются определенные правила. Эти правила не гарантируют отсутствие ошибок на листе, но они помогают избежать общих ошибок в формулах. Эти правила можно независимо включать и отключать. Оба используемых способа одинаково функциональны.
При вводе формул могут возникать ошибки – левый верхний угол зеленого цвета (нажать стрелку вниз) либо текст в ячейке. Справка по этой ошибке, пропустить ошибку, исправить в строке формул
Вычисление формулы вызывает ошибку
Текстовая дата с 2-значным годом
Число сохранено как текст
Несогласующаяся формула в области
Формула не охватывает смежные ячейки
Формула ссылается на пустые ячейки
#ИМЯ?
Данная ошибка возникает, если Microsoft Excel не может распознать имя, используемое в формуле.
#ЧИСЛО!
Данная ошибка возникает при неправильных числовых значениях в формуле или функции.
#ЗНАЧ!
Данная ошибка возникает при использовании недопустимого типа аргумента или операнда
#Н/Д
Данная ошибка возникает, если значение недоступно функции или формуле.
#ССЫЛКА!
Данная ошибка возникает, если ссылка на ячейку указана неверно.
Ячейки, на которые ссылаются формулы, были удалены или в эти ячейки было помещено содержимое других скопированных ячеек.
#ДЕЛ/0!
Данная ошибка возникает при делении числа на 0 (нуль).
1. Щелкните ячейку с ошибкой, нажмите появившуюся кнопку , а затем нажмите кнопку Источник ошибки, если она появится.
2. Рассмотрите все возможные причины и решения.
Выполните задания из файла Задания_11
.