Работа с Мастером функций

При первом обращении к Мастеру функций во время набора формулы эту программу можно вызвать либо командой Вставка ® Функция…, либо кнопкой с надписью f_x на стандартной панели инструментов.

Если формула начинается с функции, знак "=" набирать необязательно, Мастер вставит его сам.

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

Работа Мастера разбита на два шага.

На первом в правой части окна выделяется нужная функция. Для того чтобы облегчить её поиск, в левой части в поле Категория можно выделить нужный тип функций. По умолчанию всегда устанавливается тип "10 недавно использовавшихся". Пояснения по смыслу выбранной функции размещаются в нижней части окна и, если нужно, вызываются через справку. После того, как функция найдена, нажимают <ОК>.

На втором шаге Мастер выдаёт окно с полями для ввода аргументов. В них можно вводить константы, ссылки на ячейки или блоки, арифметические или логические выражения. Способы ввода аргументов те же, что и при наборе формулы. Для удобства ввода можно с помощью (Л®) переместить окно аргументов в другую часть экрана так, чтобы оно не загораживало ячейки с нужными данными. Справа от каждого поля выдаётся значение набранного аргумента, под полями всех аргументов – значение функции, внизу окна – значение всей формулы в целом.

Если функция заканчивает формулу, можно нажать <ОК> или клавишу <Enter>, в противном случае следует сделать (Л’) в тексте формулы и продолжать её набор.

Если в аргумент одной функции входит другая функция, то она называется вложенной. Такую функцию можно вызвать только через адресное поле строки формул. По умолчанию в нём высвечивается последняя функция, с которой работал Excel. (Л’) по ней выдаёт сразу окно аргументов (второй шаг Мастера). Если нужно вставить другую функцию, (Л’) по кнопке в правой части адресного поля вызывает их список. В нём перечислены последние функции, с которыми работал Мастер. Если среди них нет нужной, заказывают строку Другие функции…, которая вызывает первое окно Мастера. Для того чтобы окончить работу с вложенной функцией и продолжать набор аргументов первой, следует сделать (Л’) по названию первой функции в информационном поле строки формул.

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

Задание.

Введите в ячейки А1:А10 и В5:В10 какие-либо числа. В ячейку С1 с помощью Мастера функций введите формулу

=СУММ(МАКС(А1:А10);МАКС(В5:В10);МИН(А1:А10);МИН(В5:В10))

1.9. Присваивание имён ячейкам и блокам

Excel содержит ряд средств для замены стандартных адресов на текстовые имена. Практика показывает, что это удобнее при ссылках на информацию из ячеек. Основное ограничение: текстовые имена не должны быть похожи на стандартные адреса ячеек. Например, нельзя использовать для ячейки В15 имя "х1".

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

1. Выделяют нужную ячейку или блок.

2. Делают (Л’) по адресному полю в строке формул. Стандартный адрес, размещённый в нём, выделяется.

3. Набирают удобное пользователю имя.

4. Нажимают клавишу <Enter> для того, чтобы подтвердить замену. Если вместо <Enter> сделать (Л’) в рабочем поле, то замена стандартного адреса отменяется.

Кроме этого способа можно использовать команды меню. Основные из них:

· Вставка®Имя®Присвоить… Эта команда может как присваивать, так и удалять имена. Для создания нового имени следует:

1. Выделить нужную ячейку или блок.

2. Заказать команду.

3. В диалоговом окне в поле Имя ввести текст имени. Если в рабочем поле листа рядом с выделенным блоком предварительно был введён какой-либо текст, то он автоматически вставляется в поле имени. Если нужно присвоить другое имя, оно вводится вручную.

4. Нажать кнопку <ОК> или клавишу <Enter>.

· Вставка®Имя®Создать… Эту команду используют, если ячейки и имена, удобные для их обозначения, расположены на рабочем листе в виде горизонтальной или вертикальной таблицы, и тексты имён уже введены. Для одновременного создания всех имён следует:

1. Выделить блок, состоящий из текстов имён и именуемых ячеек.

2. Заказать команду.

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

4. Нажать кнопку <ОК> или клавишу <Enter>.

Для удаления уже существующих имён надо:

1. Заказать команду Вставка®Имя®Присвоить…

2. В диалоговом окне пометить ненужные имена на удаление. Для этого следует сделать (Л’) и нажать кнопку <Удалить> на каждом из них.

3. Нажать кнопку <ОК> или клавишу <Enter>.

Задание.

Введите в ячейки А3:I3 какие-либо тексты, в ячейки под ними – числа. Присвойте через адресное поле ячейке А4 имя, совпадающее с текстом в А3, а ячейке В4 – не совпадающее с текстом в В3. Для ячеек С4:I4 создайте имена с помощью других команд. Проследите по адресному полю, как теперь обозначает Excel эти ячейки. В А5 введите формулу =А4+В4*С4, в В5 – ту же формулу, но используя новые имена ячеек (либо прямой набивкой, либо по (Л’)).

3. РАСЧЁТНЫЕ АЛГОРИТМЫ В EXCEL

Большинство основных вычислительных алгоритмов в Excel оформлены в виде стандартных функций и вызываются с помощью программы Мастер функций (см. п. 1.8). Самые популярные из них:

· ЕСЛИ – позволяет предусмотреть разные варианты заполнения ячейки;

· СУММ, ПРОИЗВЕД – соответственно суммирование и перемножение значений из одного или нескольких блоков;

· СУММПРОИЗВ – суммирование произведений соответствующих элементов двух или нескольких массивов;

· СРЗНАЧ, СРГЕОМ – расчёт соответственно среднего арифметического и геометрического по числам в заданных блоках;

· СЧЁТ – определение количества чисел в заданном блоке.

Стандартную библиотеку можно дополнять функциями, созданными пользователем самостоятельно (см. [3, 4]).

Более сложные алгоритмы оформлены в виде команд и заказываются через меню Сервис. Наиболее важные из них:

· Подбор параметра… – нахождение аргумента, при котором функция примет нужное значение;

· Поиск решения… – решение систем уравнений и задач оптимизации;

· Пакет анализа – содержит программы, необходимые при статистической обработке данных.

Если нужная для вычислений команда отсутствует в меню, её можно установить с помощью команды Сервис®Надстройки…

3. Расчёт таблицы значений функции от одного аргумента

При явном задании функции таблица состоит из двух главных столбцов (строк). Первый – аргументы, второй – значения функции. Если алгоритм расчёта функции сложный, может потребоваться несколько дополнительных столбцов (строк) для записи промежуточных результатов.

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

Пример.

Найти графически координаты корней и максимумов функции Y=2cos(x+2)e^-0,5x

Составим план размещения информации:

Таблица 2

Ячейки	Информация	Значение, которое вводится
А1

Формулы, занесённые в ячейки А5:А25, будут нагляднее, если перед их набором присвоить константам в А3:С3 имена (см. п. 1.9) и вводить их с помощью (Л’), а в формуле функции использовать вместо адреса подпись диапазона аргументов – заголовок из ячейки А4: =2*COS(Х+2)*EXP(-0,5*Х) (см. п. 1.10).

Задание.

Отформатируйте полученную таблицу и постройте диаграмму типа Точечная по ней.

Таблица и график показывают, что при х > 9 функция практически равна нулю, первый корень лежит в диапазоне 2 < x < 3, первый экстремум – около х = 4. Введём в А3:В3 новые константы: 2 и 4 соответственно. Excel сразу же пересчитал шаг, таблицу и график на новый диапазон аргументов и теперь можно локализовать корень и экстремум уже с точностью 0,1. Диапазон для корня 2,7 < x < 2,8, координата экстремума – x = 3,5. При необходимости можно снова изменить константы, с помощью которых создаётся диапазон аргументов, и продолжить уточнение ответов.

Задание.

Найдите какой-нибудь корень и экстремум этой функции в отрицательной области аргументов.

3.1 Расчёт таблицы значений функции от двух аргументов

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

Рассмотрим пример. Составить таблицу и график функции z = y ² x - ln(x + y) для диапазонов 2 < x < 6 и1 < y < 5.

Будем менять х с шагом 0,4, у – с шагом 0,8. Составим план размещения информации:

Таблица 3

Ячейки	Информация	Значения
А1
А4

При вводе формулы в ячейку С4 символы "$" расставлены с таким расчётом, что при протяжке вбок разрешено менять адрес только у аргумента Y, аргумент Х для всей полученной строки берётся из одной и той же ячейки (в адресе Х закреплён столбец). При протяжке вниз – наоборот: в адресе аргумента Y закреплена строка, поэтому он не меняется, и в столбце оказываются значения функции, сосчитанные для одного и того же Y, но разных Х. Символ "$" можно вводить непосредственной набивкой в английском регистре или повторными нажатиями клавиши <F4> (см. п. 1.12).

Задание. Отформатируйте полученную таблицу (см. п. 1.13):

· объедините ячейки С2:Н2;

· объедините ячейки А4:А14, измените направление текста для Х;

· выделите цветом шрифта или заливкой заголовки таблицы (С2:Н3 и А4:В14);

· обведите толстой линией всю таблицу и отделите этой же линией заголовки с аргументами от значений функции;

· разделите тонкими линиями столбцы таблицы;

· разделите пунктирными линиями строки таблицы.

Перейдём к построению диаграммы. Представим на ней зависимость z(x) при разных y.

На первом шаге закажем тип Точечная с гладкими кривыми без маркеров. На втором шаге выделим диапазон данных В4:Н14 и параметр "Ряды в столбцах". На вкладке Ряд внесём для рядов данных имена: у=1, у=1,8 и т. д. На третьем шаге закажем легенду, основную сетку по осям и введём какие-нибудь заголовки. На четвёртом – размещение на том же листе.

Задание. Откорректируйте диаграмму:

· закажите более толстые линии для каждой зависимости;

· измените масштаб по оси Х;

· подберите тренд для одной из линий и выведите его уравнение на диаграмму.

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

Примечание. Если перед вводом формулы в С4 присвоить диапазонам С3:Н3 и В4:В14 имена соответственно у и х, то формулы в С4 можно ввести в более понятном виде: =у^2*x-LN(x+y) (см. п. п. 1,9, 1.12).

---

Дата добавления: 2015-07-11