пр6-8

2.3.1.4. Копирование формул

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

Методики копирования формул:

∙классическая;

∙ускоренная;

∙оригинальная.

Первые две методики аналогичны копированию содержимого ячеек. Третья – копирует в диапазон ячеек.

Методика копирования в диапазон ячеек:

∙выделить желаемый диапазон для размещения оригинала и копии формулы;

∙внести в поле верхней левой ячейки (не инвертированной) формулу – оригинал;

∙скопировать формулу в остальные ячейки диапазона командой <Ctrl> + <Enter>;

∙проконтролировать окончание копирования по результатам (формулам) в конкретных ячейках диапазона.

Внимание! Координаты адресов ячеек в формуле по умолчанию ориентируются на номера ячейки с формулой (ее координаты). Изменение этого правила реализуется изменением обозначения адресов операндов (см. п. 2.3.1.4).

2.3.1.5. Системы адресации ячеек данных

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

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

Особенность копирования формул с относительной адресацией в Excel – изменение их операндов в соответствии с расположением ячейки формулы. Так расположенная в ячейке F1

53

формула «=A1+B1+C1» при копировании в ячейку данной строки, например F8, примет вид: =A8+B8+C8.

Аналогичная корректировка выполняется и при столбцовых преобразованиях. Так расположенная в ячейке А4 формула «=A1+A2+A3» при копировании в ячейку В4 примет вид: =B1+B2+B3.

Примеры реализации имеют вид:

Корректировке подвергаются операнды в традиционном обозначении номеров ячеек.

Если корректировка формулы не требуется, применяют не относительные, а абсолютные адреса.

Абсолютный адрес – адрес ячейки, дополненный знаками денежной единицы ($). Координаты его при копировании не изменяются.

Структура абсолютного адреса: $ i $ j

где $ – символ абсолютности координаты;

i – обозначение первой координаты (букв столбца); j – обозначение второй координаты (цифр строки).

Пример преобразования адреса ячейки F12 в абсолютный:

F12 → $F$12.

Формирование символа «$» выполняется:

∙вводом с пульта последовательно с координатами ячейки;

∙вводом относительного адреса с последующим нажати-

ем клавиши F4.

Наряду с абсолютным, возможно использование и смешанных адресов.

Смешанный – адрес ячейки с одной абсолютной и одной относительной координатой.

Структура смешанного адреса:

$ i j или i $ j

54

где $ – символ абсолютности координаты;

i – обозначение первой координаты (букв столбца); j – обозначение второй координаты (цифр строки).

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

Вариант $ i j предусматривает стабилизацию буквенной компоненты адреса и имеет смысл при горизонтальном копировании формулы.

Вариант i $ j предусматривает стабилизацию цифровой компоненты адреса и имеет смысл при вертикальном копировании формулы.

Так ячейка с номером F12 может иметь два смешанных адреса:

$F12 или F$12.

Примеры изменения адресов копируемых ячеек в соответствии с формой записи исходных (ячейка В2):

Вварианте $F12 при копировании по вертикали изменяется лишь вторая координата ячейки (цифровая), по горизонтали адрес остается неизменным

Вварианте F$12 при копировании по вертикали адрес остается неизменным, по горизонтали изменяется лишь первая координата ячейки (буквенная)

55

Вварианте $F$12 при копировании как по вертикали, так

ипо горизонтали адрес остается неизменным

2.3.2.Функции в Excel

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

Структура функции:

[=] имя (аргументы)

где = – начало отдельно вычисляемой функции; имя – уникальное обозначение функции в традиционном

или русскоязычном написании; аргументы – обозначение операнда (диапазона операндов)

вычисляемой функции; [ ] – признак необязательности содержимого;

( ) – ограничители аргумента (аргументов). Примеры имен традиционных функций:

SIN, COS, LOG, LOG10, ABS

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

Примеры имен функций с русскоязычными обозначениями:

СРЗНАЧ	среднее значение заданного диапазона;
СЧЕТ	расчет количества ячеек с числовой информа-
	цией в заданном диапазоне
СЧЕТЗ	расчет количества ячеек в заданном диапазоне
МАКС	определение максимального (минимального)
(МИН)	значения в диапазоне
ЕСЛИ	расчет одной из двух возможных ветвей реше-
	ния в зависимости от выполнения проверяемо-
	го условия.

Внимание! Имена функций набираются прописными буквами соответствующего алфавита.

56

Возведение в степень может быть реализовано не только

соответствующей операцией ^, но и функцией:

СТЕПЕНЬ(аргумент; показатель),

где СТЕПЕНЬ – имя функции; аргумент – операнд (арифметическое выражение), подле-

жащий возведению в степень; показатель – операнд (арифметическое выражение), опре-

деляющий значение степени.

; – разделитель аргумента и показателя; Аргументы традиционных функций имеют структуру ана-

логичную математической.

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

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

Структура диапазона:

надр : кадр

где надр – начальный адрес диапазона; кадр – конечный адрес диапазона;

:– признак непрерывности диапазона.

Примеры формирования отдельно вычисляемых функций:

=LOG(С3) =СТЕПЕНЬ(В2+С5;2) =СЧЕТ(А2:А18) =МАКС(А4:В12)

Для функции «ЕСЛИ» аргумент имеет структуру:

усл; резда; резнет

где усл – проверяемое условие - совокупность аргументов, соединенных знаками отношения (<, >, =, >=, <=);

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

резнет – зависимость, определяющая действия при невыполнении условия с аналогичной «резда» структурой;

;– разделитель компонентов.

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

=ЕСЛИ (А3>A4; B7^2; C3)

57

Функция предписывает: сравнить числовые данные в ячейках А3 и А4. Если содержимое А3 больше чем в А4, вычислить квадрат содержимого ячейки В7. Если содержимое А3 меньше (равно) содержимому А4 – в качестве результата использовать содержимое ячейки С3.

Полный перечень функций (см. окно «Мастер функций – шаг 1 из 2» далее) достаточен для решения типовых вычислительных задач.

В Excel, как и в других модулях пакета Office для облегчения действий пользователя предусмотрены различные средства. Одно из них – мастер.

Мастер – программное средство обеспечения эффективной и комфортной работы пользователя

стиповыми компонентами модуля.

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

Мастер функций – программное средство эффективно-

го создания функций в ячейках Excel.

Вызов мастера функций возможен различными вариантами. Простейший – через строку формул.

Методика ввода функции с помощью мастера:

∙выделить требуемую ячейку (проконтролировав ее номер в левом подокне строки формул);

∙вызвать мастер функций щелчком кнопки в строке формул;

∙проконтролировать активизацию мастера функций (открытие его окна):

58

∙выбрать в подокне «Категория» требуемую;

∙выбрать требуемую функцию из предлагаемых категорией в одноименном подокне;

∙проконтролировать открытие окна ввода аргументов функции. Для функции ЕСЛИ оно имеет вид:

;

∙ввести необходимый аргумент (аргументы) в предлагаемые подокна. Например, для функции ЕСЛИ:

∙прекратить работу с мастером функций нажатием кнопки «ОК»;

∙проконтролировать результат выполнения функции в ячейке;

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

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

59

мул. МS Excel предоставляет возможность реализации таких за-

висимостей.

Методика создания формулы с функциями:

∙открыть ячейку для записи формулы;

∙ввести знак «=»;

∙набрать операнд:

∙если он число - с пульта ввести символы или щелкнуть ячейку с ним;

∙если он функция – реализовать методику ввода функции с помощью мастера;

∙проконтролировать набранное в строке формул;

∙активизировать, при необходимости, курсор в нем;

∙ввести символ операции (в строке формул);

∙повторить четыре предыдущих пункта (при необходимости);

∙ввести скобки (при необходимости);

∙дать команду на выполнение формулы нажатием кла-

виши «Enter».

Примеры формул с функциями в качестве операндов: =C8*SIN(C11)+ЕСЛИ(C7>3;1;10)

=LOG10(C7+В3)+(МАКС(C7:C15)-C12)/28

Расположение этих формул в ячейках С3 и С5 в режиме ввода имеет вид:

По окончании ввода высветится результат:

в ячейке С3 - константа 10, в ячейке С5 - «#ЧИСЛО» (обозначение ошибки - отсутствие исходных данных в аргументах функций LOG10 и МАКС).

60

2.3.3. Автосуммирование

Удобное, быстрое вычисление суммы диапазона ячеек реализуется кнопкой «Автосумма» на панели инструментов.

Возможны две методики использования.

Методика автосуммирования 1:

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

∙щелкнуть кнопкой «Автосумма» на панели инструментов;

∙проконтролировать выданную в строку формул зависимость, предлагаемую Excel;

∙откорректировать диапазон (при необходимости);

∙получить результат нажатием клавиши <Enter>.

Методика автосуммирования 2:

∙щелкнуть ячейку для накопления суммы;

∙щелкнуть дважды кнопку «Автосумма»;

∙проконтролировать предлагаемый результат и диапазон.

Внимание! Использование методики 2 предполагает наличие непрерывного диапазона числовых данных над результирующей ячейкой ( при их отсутствии - диапазон слева).

Задание 2.3

1.Создать в ячейке Н7 формулу вычисления периметра квадрата Р (м), если значение стороны L (см) соответствует номеру собственного варианта.

2.Создать авторяд из 15 значений, начиная с ячейки С2 вниз. Начальное значение 1, шаг изменения – номер собственного варианта.

3.Вычислить в ячейке D3 - среднее, в ячейке D5 - максимальное, в ячейке D7 - минимальное значение для авторяда задания 2.

4.Создать в ячейке H8 формулу вычисления площади круга S (мм2) по заданному значению диаметра d (мм). В качестве значения d использовать удвоенный номер собст-

61

венного варианта. Значение Пи ввести напрямую, квадрат диаметра вычислить перемножением.

5.Реализовать в ячейке Н9 задание 4, используя функции (ПИ и СТЕПЕНЬ).

6.Создать в ячейках Н10 и Н11 формулы (см. примеры формул с функциями на предыдущей странице).

Контрольные вопросы 2.3

1.Какова структура формулы в Excel?

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

3.Какова методика создания формулы?

4.Как определить наличие формулы в ячейке?

5.Какова методика редактирования формул?

6.Сколько вариантов копирования формул предлагает Excel?

7.Чем относительная система адресации ячеек отличается от абсолютной?

8.Какова структура отдельно вычисляемой функции?

9.Каковы структуры аргументов функции?

10.Что такое мастер, мастер функции?

11.Какова методика ввода функций с помощью мастера?

12.Какова методика ввода формулы с функциями?

13.Что такое автосуммирование?

14.Каковы методики применения автосуммирования?

2.4. Хранение данных

Excel предоставляет возможности хранения данных с разной степенью детализации (ячейка, «Книга»).

Максимальная степень детализации при хранении – со-

держимое любой ячейки.

Любой информационный фрагмент, ввод которого в ячейку завершен (командой «ОК» или кнопкой «Enter») автоматически становится ее содержимым.

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

Минимальная степень детализации при хранении – « Кни-

га» Excel.

62