Добавил:
Upload Опубликованный материал нарушает ваши авторские права? Сообщите нам.
Вуз: Предмет: Файл:
Скачиваний:
44
Добавлен:
28.02.2016
Размер:
1.35 Mб
Скачать

Лекция 3.

Использование ссылок. Относительные и абсолютные ссылки.

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

По умолчанию в Microsoft Excel используются ссылки A1, в которых столбцы обозначаются буквами от A до IV (256 столбцов максимально), а строки числами — от 1 до 65536). Можно воспользоваться стилем, в котором и столбцы, и строки листа пронумерованы. Этот стиль, называемый R1C1, наиболее полезен при вычислении позиции строки и столбца в макросах, а также при отображении относительных ссылок. В стиле R1C1, после буквы «R» указывается номер строки ячейки, после буквы «C» — номер столбца.

Относительные ссылки используют для указания адреса ячейки, вычисляемого в относительной системе координат с началом в текущей ячейке. Относительные ссылки имеют вил: А1, В2 и т.д.

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

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

Рассмотрим пример.

А

B

C

D

E

F

1

Объемы продаж

2

3

Наименование продукции

январь

февраль

март

Всего

В % к общему

4

Молоко

120

120

100

340

26%

5

Творог

110

200

120

430

33%

6

Сметана

230

180

130

540

41%

7

Итого

460

500

350

1310

100%

Чтобы получить в ячейке Е4 результат суммирования данных по строке, можно ввести формулу: =В4+С4+D4, и затем скопировать ее с помощью автозаполнителя на строки, расположенные ниже.

Чтобы автоматически получить все суммы по строкам и столбцам, выделите блок B4:Е7 и нажмите кнопку автосуммирование на панели инструментов.

Выделите столбец F и задайте ему процентный стиль с помощью кнопки на панели инструментов. В ячейку F4 введите формулу =E4/E7 (если задан процентный стиль, то в формуле не нужно задавать *100).

Для того, чтобы корректно скопировать формулу из ячейки F4 на интервал F5:F7, воспользуйтесь абсолютной адресацией ячейки Е7.

1 способ. Выделите в строке формул адрес Е7 и нажмите клавишу F4, формула в ячейке приобретет вид: =E4/$E$7

2 способ. Назначение имени ячейке. Установитесь в ячейку Е7 и выберите меню-Вставка – Имя - Присвоить и введите ИТОГО. В ячейке F4 наберите формулу =Е4/ИТОГО (все определенные ранее имена можно выбрать из раскрывающегося списка в поле имени слева от строки формул).

При использовании смешанных ссылок, например, А$1 или $A1, часть ссылки, не содержащая знак “$”, будет обновляться при копировании, а другая часть, со знаком “$“, останется без изменений. Чтобы установить такой адрес:

  • установитесь на ячейку;

  • в строке формул выделите ссылку, которую необходимо изменить;

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

Стиль ссылок R1C1 (в котором пронумерованы строки и столбцы) удобен для отображения относительных ссылок. После буквы R (Row) указывается номер строки ячейки, после буквы С (Column) – номер столбца. Например, абсолютная ссылка R1C1 эквивалентная абсолютной ссылке $A$1 для формата А1. Если ячейка А1 является текущей, то относительная ссылка R[1]C[1] указывает на ячейку, расположенную на одну строку ниже и на один столбец правее, т.е. на ячейку В2.

Приведем примеры.

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

R[-2]C

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

R[2]C[2]

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

R2C2

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

R[-1]

Относительная ссылка на строку, расположенную выше текущей ячейки

R

Абсолютная ссылка на текущую строку.

Наиболее часто встречающиеся ошибки в формулах:

#ДЕЛ/0 - деление на нуль – при использовании в формуле адресов пустых ячеек или ячеек с нулевыми значениями.

#ИМЯ - обращение к несуществующему диапазону (возможно адрес ячейки набран в Рус.регистре).

#ПУСТО - появляется когда задано пересечение двух областей, которые в действительности не имеют общих ячеек.

#ЧИСЛО - появляется, когда возникают проблемы при использовании чисел в формуле или функции. Возможно введена формула, возвращающая числовое значение, которое слишком велико или слишком мало, чтобы его можно было представить в Microsoft Excel.

#ССЫЛКА - неверный адрес ячейки.

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

######### - значение не помещается в ячейку (увеличьте ширину столбца).

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

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

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

В Excel используются функции следующих категорий:

  • Финансовые

  • Дата и время

  • Математические

  • Статистические

  • Ссылки и массивы

  • Работа с базой данных

  • Логические

  • Текстовые

  • Проверка свойств и значений

Рассмотрим пример. Известны сведения о поступлении в ВУЗ. Определить признак зачисления для каждого абитуриента, если известно, что проходной балл = 8.

В данном примере воспользуемся логической функцией ЕСЛИ(), которая имеет три аргумента:

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

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

В категории Логические используются следующие функции:

И Возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА.

ЛОЖЬ Возвращает логическое значение ЛОЖЬ.

ЕСЛИ Определяет выполняемую логическую проверку.

НЕ Меняет на противоположное логическое значение своего аргумента.

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

ИСТИНА Возвращает логическое значение ИСТИНА.

Наиболее часто используемые функции Excel:

СУММ Суммирует аргументы.

СУММЕСЛИ Суммирует ячейки, обозначенные заданным условием.

МАКС Возвращает максимальное значение из списка аргументов.

МИН Возвращает минимальное значение из списка аргументов.

СЧЁТ Подсчитывает количество чисел в списке аргументов.

СЧЁТЕСЛИ Подсчитывает количество непустых ячеек, удовлетворяющих заданному условию внутри интервала.

Для операций над матрицами используются функции:

МОПРЕД Возвращает определитель матрицы (матрица хранится в массиве).

МОБР Возвращает обратную матрицу (матрица хранится в массиве).

МУМНОЖ Возвращает произведение матриц (матрицы хранятся в массивах).

Матричные формулы действуют во всех ячейках диапазона, нельзя изменять отдельные ячейки в операндах формулы. Ввод матричной формулы завершается комбинацией клавиш CTRL+SHIFT+ENTER.

Приведем пример. Матрица, которая хранится в диапазоне ячеек А1:С3 умножается на вектор Е1:Е3. Чтобы получить результат, необходимо выделить диапазон ячеек G1:G3, вызвать мастер функций, выбрать в категории математические функцию МУМНОЖ и ввести в качестве Массива1 диапазон А1:С3, а в качестве Массива2 - Е1:Е3. Завершить ввод комбинацией клавиш CTRL+SHIFT+ENTER.

Защита информации.

Защита данных – мера, направленная на предотвращение их потери в случае неосторожных действий пользователя. Различают: средства запрещения изменения данных и средства скрытия источников данных.

При защите листа с помощью команды Сервис- Защита - Защитить лист ограничивается доступ к этому листу. Для снятия этих ограничений необходимо снять защиту листа с помощью команды Сервис - Защита -Снять защиту листа. Если для защищенного элемента был установлен пароль, для снятия защиты необходимо знать этот пароль.

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

  1. Выделите ячейки с исходными данными, т.е. те, в которых должны вноситься изменения.

  2. Выберите пункт меню Формат – Ячейки - вкладка Защита, убрать флажок в пункте Защищаемая ячейка.

  3. Установите защиту листа: Сервис – Защита – Защитить лист.

Соседние файлы в папке Теория