ВІКІСТОРІНКА
Навигация:
Інформатика
Історія
Автоматизація
Адміністрування
Антропологія
Архітектура
Біологія
Будівництво
Бухгалтерія
Військова наука
Виробництво
Географія
Геологія
Господарство
Демографія
Екологія
Економіка
Електроніка
Енергетика
Журналістика
Кінематографія
Комп'ютеризація
Креслення
Кулінарія
Культура
Культура
Лінгвістика
Література
Лексикологія
Логіка
Маркетинг
Математика
Медицина
Менеджмент
Металургія
Метрологія
Мистецтво
Музика
Наукознавство
Освіта
Охорона Праці
Підприємництво
Педагогіка
Поліграфія
Право
Приладобудування
Програмування
Психологія
Радіозв'язок
Релігія
Риторика
Соціологія
Спорт
Стандартизація
Статистика
Технології
Торгівля
Транспорт
Фізіологія
Фізика
Філософія
Фінанси
Фармакологія


Относительные и абсолютные ссылки

Ссылки в формуле указывают на позицию ячеек относительно активной ячейки. Таким образом, адреса ячеек в ссылках при копировании формулы автоматически изменяются. Такие ссылки называются относительными ссылками на ячейку. Например, запишем в ячейку А3 формулу: =А1+А2, скопируем эту формулу из А3 в ячейку В3. В результате в ячейке В3 мы увидим формулу: =В1+В2, т.е. ссылки изменились автоматически. Таким образом, относительная ссылка означает, что при копировании формулы в другие ячейки вдоль по строке (столбцу) в формулу будут подставляться данные из ячеек, сдвинутых относительно начальной настолько, насколько изменилось местоположение копируемой формулы.

Если ссылка при копировании не должна изменяться, то используют так называемые абсолютные ссылки на ячейку. В этом случае указывается позиция ячейки на рабочем листе. Поэтому при копировании или перемещении формул указанная в абсолютной ссылке ячейка не изменяется. Признаком абсолютной ссылки является знак доллара ($). Вернемся к рассмотренному выше примеру. Если мы изменим формулу в ячейке А3 следующим образом: =А1+$A$2, то при копировании в ячейке В3 обнаружим: =В1+$A$2, т.е. относительная ссылка автоматически изменилась, а абсолютная - нет.

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

В режиме редактирования вид ссылки можно изменять с помощью клавиши F4: при однократном нажатии относительная ссылка превращается в абсолютную (абсолютная - в относительную), а при повторном нажатии - в смешанную.

Ссылки на листы и книги

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

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

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

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

Если удалить лист, на содержимое которого существует ссылка в формуле, соответствующие ссылки заменятся значением ошибки #ССЫЛКА, а результат вычислений не будет отображаться. После удаления содержимого влияющей ячейки ее значение при вычислениях будет считаться равным 0.

Ссылка на ячейку из другой рабочей книги (внешняя ссылка) создается аналогичным образом.

Влияющие и зависимые ячейки

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

Отслеживать зависимости удобно с помощью панели инструментов Зависимости (рис. 6.1). Чтобы открыть ее, воспользуйтесь подменю Зависимости меню Сервис.

Рис. 6.1. Панель инструментов Зависимости

Функция отслеживания зависимостей позволяет графически обозначить связи между влияющими и зависимыми ячейками. Рассмотрим простой пример. Пусть в ячейках А1 и А2:А5 содержатся некоторые числа, в ячейке В2 запишем формулу =$B$1*A2, с помощью автозаполнения скопируем эту формулу в ячейки В3:В5. Активизируем ячейку А1 и выберем в меню Сервис команду Зависимости - Зависимые ячейки или щелкнем по кнопке . В таблице появятся линии трассировки со стрелками, исходящими из активной ячейки и указывающими на зависимые ячейки в таблице (рис. 6.2).

Рис. 6.2. Линии трассировки, показывающие зависимые от А1 ячейки

Также отображаются линии трассировки для влияющих ячеек (при нажатии кнопки ). Кнопки и предназначены для того, чтобы убрать линии трассировки.

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


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

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

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

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

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

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

Создайте таблицу, изображенную на рис. 6.3.

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

Рис. 6.3. Пример использования вложенных функций

Для этого введем в ячейку В12 формулу =МИН (В4:В10). Скопируем эту формулу в ячейки С12 - Е12. В результате применения функции, определяющей минимальное значение, мы установим минимальную температуру в разных городах. Чтобы определить максимальное из минимальных значений, введем в ячейку В13 формулу:

=МАКС (В12:Е12).

Полностью решить данную задачу можно и с помощью одной-единственной формулы (которую нужно вставить в ячейку В13):

=МАКС (МИН (В4:В10); МИН (С4:С10); МИН (D4:D10); МИН (Е4:Е10)).

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

Существует множество задач (например, округление значений), решать которые на много легче, используя вложенные функции. Для округления чисел в Excel 97 предназначена функция ОКРУГЛ, синтаксис которой несколько отличается от синтаксиса уже знакомых нам функций СУММ, МИН и МАКС. Аргументами функции ОКРУГЛ являются число или ссылка и количество десятичных разрядов результата.

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

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

Панель формул

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

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

Все функции Excel сгруппированы по категориям, имена которых отображаются в списке Категория.

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

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

Существует несколько способов задания аргументов. Рассмотрим некоторые из них на примере вставки функции для вычисления среднего значения СРЗНАЧ. Количество аргументов функции не должно превышать 30. На панели формул для ввода каждого аргумента функции предусмотрено отдельное поле. Сначала таких полей два (рис. 6.4), но по мере ввода аргументов количество полей увеличивается.

Рис. 6.4. Диалоговое окно функции, вычисляющей среднее значение

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

Типы функций

Математические функции

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

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

1. Транспонирование матрицы. Пусть дана матрица . Расположим числа этой матрицы в ячейках А3:С5. Выделим место под транспонированную матрицу, например, ячейки Е3:G5. Вызовем мастер функций (кнопка = на строке формул). В открывшемся диалоговом окне в списке Категория (слева) выберем Математические, а в списке Функция - ТРАНСП. Откроется диалоговое окно. В поле ввода Массив нужно задать диапазон ячеек с исходной матрицей (вручную или с помощью мыши указать в таблице). Щелкнуть по кнопке Готово. Распространить результат на всю выделенную область, нажав клавиши CTRL+SHIFT+ENTER.

2. Произведение двух матриц. Так же, как и в предыдущем примере, нужно выделить диапазон ячеек для матрицы-результата и вызвать мастер функций. В списке функций выбрать МУМНОЖ, откроется диалоговое окно, имеющее два поля ввода Массив 1 и Массив 2. Ввести в эти поля диапазоны ячеек с числами первой и второй матриц, нажать ОК. Затем нажать клавиши CTRL+SHIFT+ENTER. Количество столбцов массива 1 должно быть таким же, как количество строк массива 2.

Текстовые функции

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


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

Создание инициалов.

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

Рис. 6.5. Пример использования текстовых функций

Разместим исходную таблицу в столбцах В (фамилия), С (имя) и D (отчество), начиная со строки 4 (рис. 6.5).

Результирующую таблицу разместим, например, в столбце В, начиная со строки 13.

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

Введем в ячейку В13 следующую формулу:

=B4&" "&ЛЕВСИМВ(C4)&"."&ЛЕВСИМВ(D4)&"."

В этой формуле В4 - фамилия. & - знак объединения символьных фрагментов. Далее следует пробел (символ пробел заключен в кавычки, т.к является текстовой константой); ЛЕВСИМВ - функция из списка (находится в разделе Текстовые), возвращает первый (самый левый ) символ текстовой строки, в нашем примере эта функция возвращает первый символ имени. “.” -текстовая константа «точка». Далее функция ЛЕВСИМВ(D4) - возвращает первый символ отчества, после которого снова ставится точка.

После ввода формулы в ячейку В13 выполним автозаполнение (скопируем эту формулу вниз по столбцу В). В результате в ячейках В1:В17 появятся фамилии и инициалы людей, перечисленных в исходной таблице. Теперь любые изменения, внесенные в исходную таблицу, автоматически отразятся в результирующей таблице.

Функции даты и времени

Excel преобразует значение даты и времени суток в так называемые сериальные числа, которые используются при вычислениях. Эти числа должны быть заданы в качестве аргумента дата_в_числовом_формате. Число 1 соответствует значению даты 01/01/1900, максимальное значение 65380 – дате 31/12/2078.

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

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

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

Занесем исходные данные в ячейки В4:Е8 (вручную) (рис.6.6), а в ячейку F4 запишем формулу:

=ГОД(СЕГОДНЯ()-E4)-1900+МЕСЯЦ(СЕГОДНЯ()-E4)/12

Рис. 6.6. Пример использования функций даты и времени

Функция СЕГОДНЯ() возвращает текущую дату в числовом формате. У этой функции нет аргументов. Функция ГОД возвращает год, соответствующий аргументу дата_в_числовом_формате. Год определяется как целое в интервале 1900-9999. В нашем примере в качестве аргумента используется разность дат сегодняшней и начала работы. Функция МЕСЯЦ возвращает месяц, соответствующий аргументу дата_в_числовом_формате. Месяц определяется как целое в интервале от 1 (Январь) до 12 (Декабрь). В качестве аргумента этой функции также используется разность дат сегодняшней и начала работы, затем количество месяцев делится на 12, т.е. определяется доля года.

С помощью автозаполнения скопируем формулу на все последующие ячейки столбца F. Результат может выглядеть странно, т.к. значения в ячейках отображаются в формате Дата. Изменим его на числовой с 2 знаками после запятой (меню Формат команда Ячейки вкладка Число - Числовой).

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

Логические функции

Логических функций в Microsoft Excel шесть: ЕСЛИ, И, ИЛИ, ИСТИНА, ЛОЖЬ, НЕ.

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

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

Занесем исходные данные в ячейки В3:Е7 (вручную) (рис. 6.7), а в ячейку F3 запишем формулу:

=ЕСЛИ(E3<4;"нет стипендии"; ЕСЛИ(E3>=4,5; "повышенная стипендия"; "обычная стипендия"))

Рис. 6.7. Пример использования логической функции

В этой формуле использована логическая функция ЕСЛИ. Эта функция имеет три аргумента. Первый аргумент – условие, далее следуют два аргумента, которые определяют содержимое ячейки в случае, если условие выполняется - аргумент 2, и , если условие неверно - аргумент 3.

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

При изменении среднего балла автоматически будет изменяться значение в ячейке «Вид стипендии».

© 2013 wikipage.com.ua - Дякуємо за посилання на wikipage.com.ua | Контакти