Информатика и программирование - Основы информатики (926517), страница 31
Текст из файла (страница 31)
Чтобы Microsoft Excel распознавал заголовки, используемые в формулах, необходимо выполнить следующие действия:
1) выбрать пункт меню Сервис | Параметры;
2) выбрать вкладку Вычисления;
3) в группе Параметры книги установить флажок Допускать названия диапазонов.
10.6.3.Типы адресации в Microsoft Excel
При записи формул приходится ссылаться на ячейки, в которых хранятся исходные значения. Каждая ячейка имеет адрес (ссылку). Возможны следующие способы адресации ячеек.
I. Адресация одной ячейки. Ячейка на пересечении столбца А и строки 3 имеет адрес А3. Всего на листе может быть 65536 строк и 256 столбцов. Столбцы нумеруются A, …, Z, AA, AB, …, IV.
В Excel существуют следующие типы ссылок на ячейки. Отличия типов ссылок становятся заметными при переносе или копировании ячейки со ссылками.
1. Абсолютные ссылки.
Абсолютные ссылки не меняются при переносе или копировании ячейки со ссылками. Перед заголовком столбца и номера строки ячейки ставится знак доллара $. Примеры абсолютных ссылок $A$1, $B$67.
| A | B | |
| 1 | 1 | 2 |
| 2 | = | |
| 3 | ||
| 4 | 1 | 2 |
| 5 | =$A$1+$B$1 |
$A$1+$B$12. Относительные ссылки.
При переносе или копировании ячейки с относительными ссылками, ссылки меняются, сохраняя пространственное соотношение с ячейками, на которые они ссылаются. Относительная ссылка представляет адрес ячейки. Примеры относительных ссылок A2, CD45.
| A | B | |
| 1 | 1 | 2 |
| 2 | = | |
| 3 | ||
| 4 | 1 | 2 |
| 5 | =A4+B4 |
3. Смешанные ссылки.
В смешанных ссылках либо перед заголовком столбца, либо номером строки ставится знак доллара. Этот параметр не меняется при переносе или копировании ячейки со ссылками, как абсолютная ссылка, а параметр, перед которым знак доллара отсутствует – меняется, сохраняя пространственное соотношение, как относительная ссылка. Примеры смешанных ссылок T$2, $AC5.
| A | B | C | |
| 1 | 1 | 2 | 3 |
| 2 | = | ||
| 3 | |||
| 4 | 1 | 2 | 3 |
| 5 | =B$1+$B4 |
A$1+$B1Задача. Формулу из ячейки B2 скопировали в ячейку C3. Какое значение имеет формула в ячейке C3?
| A | B | C | |
| 1 | 1 | 2 | 3 |
| 2 | 3 | =A1+$B$1*A$2 | 4 |
| 3 | 4 | 6 | ? |
Ответ.С3: =B2+$B$1*B$2; B2: 7; C3: 21.
Задача. В ячейке B2 вычисляется сумма двух ячеек. Формулу из ячейки B2 скопировали в ячейку C3. Зависимость между ячейками изображена на рисунке.
| A | B | C | |
| 1 |
| ||
| 2 |
|
| |
| 3 |
|
Какая формула записана в ячейке B2?
Ответ. Формула в B2: =A2+$B1.
4. Трехмерные ссылки (объемные ссылки).
Трехмерная ссылка используется для ссылки на ячейку, находящуюся на другом листе, и состоит из двух частей: названия листа и абсолютной (относительной, смешанной) ссылки на ячейку, разделенных восклицательным знаком, например:
Лист1!B1.
Если название листа содержит пробелы, знаки пунктуации, то название листа в ссылке заключается в апострофы, например:
'Лист 1'!B1.
5. Внешние ссылки.
Внешняя ссылка используется для ссылки на ячейку в другой книге. Внешняя ссылка состоит из названия книги в квадратных скобках и трехмерной ссылки, например:
[Книга1]Лист1!B1.
Если название книги или листа содержит пробелы, знаки пунктуации, то вся часть ссылки до восклицательного знака заключается в апострофы, например:
'[Книга 1.xls]Лист 1'!B1.
Расширение файла книги можно не указывать. Если книга закрыта, то необходимо указать полный путь к файлу книги до квадратных скобок с названием книги, например:
'C:\MyDocs\[Книга 1.xls]Лист 1'!B1.
II. Адресация связных ячеек (диапазона). Диапазон определяется адресами верхней левой и нижней правой ячеек.
Например, три последовательные ячейки А1, В1, С1 можно адресовать как А1:С1.
Возможно задание диапазонов с использованием трехмерных ссылок. Например, адресация диапазона Лист1:Лист3!B1 задает все ячейки B1 с листа Лист1 по лист Лист3, а адресация диапазонов Лист1:Лист3!C1:D9 задает диапазон C1:D9 на листах Лист1-Лист3.
Трехмерные ссылки нельзя использовать для создания явного или неявного пересечения диапазонов.
III. Адресация несвязных ячеек. Непоследовательные ячейки перечисляются через точку с запятой. Например, ячейки А1, А3, В3, С3 можно адресовать как А1; А3:С3.
10.6.4.Присвоение имен ячейкам
и диапазонам в Microsoft Excel
При записи формул удобно ссылаться на часто используемые ячейки не по ссылке, а по имени, например, Ставка_налога.
Существует два типа имен ячеек и диапазонов:
1) на уровне листа;
2) на уровне книги.
Чтобы присвоить имя ячейке или диапазону на уровне листа необходимо выполнить следующие действия:
1) выделить ячейку или диапазон ячеек;
2) выбрать пункт меню Вставка | Имя | Присвоить;
3) в открывшемся окне Присвоение имени в поле Имя ввести имя ячейки или диапазона, причем имя должно начинаться как трехмерная ссылка с названия листа и знака восклицания (!); первый символ имени должен быть буквой или знаком подчеркивания, остальные символы имени могут быть буквами, цифрами, точками или знаками подчеркивания; регистр не учитывается;
4) в поле Формула будет записана ссылка на ячейку или диапазон;
5) нажать кнопку Добавить, чтобы ввести еще имена ячеек или диапазонов, или кнопку Ok, чтобы закрыть окно.
Чтобы присвоить имя ячейке и диапазону на уровне книги необходимо выполнить те же действия, но на шаге 3 при задании имени не указывать название листа.
Чтобы присвоить имя формуле или константе необходимо выполнить те же действия, что и при задании имени на уровне книги, но на шаге 4 в поле Формула необходимо записать формулу или константу, например «=25%».
Присвоенные имена, именованные формулы и константы используются в формулах. При использовании имен на уровне листа на другом листе необходимо записать название листа, знак восклицания и имя, как в трехмерной ссылке. Например, Лист1!Итог.
10.6.5.Формулы Microsoft Excel и их отладка
В каждой ячейке, помимо значений, могут содержаться формулы. Запись формулы начинается со знака равно (=), а далее следует арифметическое выражение или название функции. Параметры функции записываются после имени функции в скобках через точку с запятой (;).
Некоторые функции не имеют параметров. В этом случае записываются пустые скобки. Например, =ПИ(), =СЕГОДНЯ().
Формулы могут содержать следующие арифметические действия:
+ сложение;
– вычитание;
* умножение;
/ деление;
^ возведение в степень.
В табличном процессоре Excel определены различные математические функции, некоторые из них представлены в табл. 10 .20.
Таблица 10.20. Математические функции
табличного процессора Excel
| Функция | Описание |
| КОРЕНЬ | Квадратный корень |
| ФАКТР | Факториал |
| СУММ | Сумма аргументов |
| МАКС | Максимальное значение аргументов |
| МИН | Минимальное значение аргументов |
| СРЗНАЧ | Среднее арифметическое аргументов |
Эти и другие функции, их параметры и справку по ним можно найти с помощью Мастера функций (меню Вставка | Функция).
Пусть дано выражение
y =
,
и значение х содержится в ячейке А1, а y – в A2, тогда соответствующая запись в ячейке A2 будет иметь вид:
=КОРЕНЬ(SIN(A1+2)/(A1+3)+7)/LN(ABS(1+(A1+COS(A1/2)^2)/A1^3)).
Рассмотрим вычисление сложных формул. Пусть дано выражение
y =
.
Воспользуемся функцией ЕСЛИ:
ЕСЛИ(Условие; Значение 1; Значение 2),
где Условие – логическое выражение; Значение 1 – значение, возвращаемое в случае истинности условия; Значение 2 – значение, возвращаемое в случае ложности условия.
Значение 1 и Значение 2 могут быть формулами. Если условие истинно или ложно, а соответствующее значение отсутствует, то возвращается значение 0.
Значение х содержится в ячейке А1, а y – в A2, тогда соответствующая запись в ячейке A2 будет иметь вид:
=ЕСЛИ(A1<0;A1+5;ЕСЛИ(A1<2;A1*A1;-1/A1)).
Чтобы изменить тип ссылки в формуле, необходимо выполнить следующие действия:
1) выбрать ссылку в формуле;
2) нажимать клавишу F4 до тех пор, пока ссылка не будет иметь нужный тип.
Формулы пересчитываются при вводе или изменении значений в ячейках, которые участвуют в вычислениях.
Чтобы принудительно пересчитать формулы во всех открытых книгах, нажмите клавишу F9.
Чтобы установить автоматический пересчет формул необходимо выполнить следующие действия:
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
