1945

выделить обе ячейки с данными;

поместить указатель мыши на маркер заполнения;

убедиться, что указатель принял вид черного креста;

нажать левую кнопку мыши и протянуть маркер заполнения в

нужном направлении на необходимое число ячеек.

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

Вычисление итоговых результатов

Для быстрого вычисления итоговых результатов (сумма, среднее, максимум, минимум) для нескольких ячеек можно воспользоваться кнопкой Автосумма на панели инструмен-

тов «Стандартная». Для этого необходимо:

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

нажать стрелку справа от кнопки Автосумма и выбрать нужную функцию;

выделить блок ячеек, над которыми производятся вычисления;

нажать клавишу [Enter].

Мастер функций

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

Для вызова окна мастера функций нужно:

установить курсор в то место формулы, где должна быть функция;

выполнить команду Вставка – Функция или нажать кнопку Вставка функции слева от строки формул;

выбрать категорию функций и саму функцию, нажать кнопку ОК;

ввести аргументы функции и нажать кнопку ОК.

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

21

Виды ссылок на ячейки электронной таблицы

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

относительные (например, В2);

абсолютные (например, $B$2);

смешанные (например, $B2, B$2).

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

Рис. 4. Относительные ссылки

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

Рис. 5. Абсолютные ссылки

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

22

тельная будет меняться в зависимости от нового положения формулы

(рис. 6).

Рис. 6. Смешанные ссылки

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

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

Построение графиков и диаграмм

Чтобы построить график или диаграмму, следует:

1. Выделить данные, которые необходимо отобразить на диаграмме.

2. Выполнить команду Вставка – Диаграмма горизонтального меню или нажать кнопку Мастер диаграмм на панели инструментов «Стандартная».

3.Выбрать тип диаграммы и нажать кнопку Далее.

4.Указать, как выбираются данные из рабочего листа (из столбцов или строк) и проверить диапазон данных. Здесь уже можно увидеть внешний вид будущей диаграммы. На вкладке «Ряд» задать имена рядов и подписи по оси Х. Нажать кнопку Далее.

5.Задать параметры диаграммы (заголовки осей и диаграммы, положение легенды, подписи данных, линии сетки) и нажать кнопку

Далее.

6.Указать расположение диаграммы (на отдельном листе или на имеющемся) и нажать кнопку Готово.

На каждом шаге построения диаграммы можно вернуться к предыдущему шагу, нажав кнопку Назад.

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

23

Редактирование диаграммы

Для редактирования диаграммы необходимо ее выделить и выбрать нужную команду в меню Диаграмма (Тип диаграммы,

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

Для форматирования какого-либо элемента диаграммы нужно щелкнуть по этому элементу правой кнопкой мыши и в появившемся контекстном меню выбрать команду Формат….

Нахождение корней уравнения

Рассмотрим решение уравнения вида f(x)=0.

Если f(x) является многочленом n-й степени, т.е. уравнение имеет вид аnxn + аn-1xn-1 + … + а1x + а0 = 0, то у него n корней. Все корни находятся в диапазоне [–k; k], где k вычисляется по формуле

k=1+max(|an-1|; …; |a1|; |a0|)/an.

24

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

Для нахождения корней с помощью Excel нужно:

1)определить диапазон, в котором находятся корни уравнения;

2)заполнить ячейки последовательностью значений переменной

хв найденном диапазоне с каким-нибудь небольшим шагом;

3)вычислить значение функции f(x) при каждом х;

4)построить график функции f(x) (тип диаграммы – «точечная»);

5)по графику функции или по таблице ее значений определить значения приближенных корней уравнения и ввести их в свободные ячейки электронной таблицы;

6)вычислить значения функции от найденных приближенных значений корней уравнения;

7)уточнить значение каждого корня с помощью команды Сервис

– Подбор параметра, установив в окне «Подбор параметра» следующие значения:

поле «Установить в ячейке» – ссылка на ячейку со значением f(x) от приближенного корня;

поле «Значение» – 0;

поле «Изменяя значение ячейки» – ссылка на ячейку со значением приближенного корня.

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

Относительную погрешность вычислений можно задать с помощью команды Сервис – Параметры на вкладке «Вычисления».

Пример

Решить уравнение х3+х2–2х–1=0.

В нашем примере максимальный по модулю коэффициент уравнения равен 2, а коэффициент при х3 равен 1, следовательно, k=3, интервал, в котором лежат все корни уравнения, равен [–3; 3].

Составим таблицу значений функции y = х3+х2–2х–1, х [–3; 3] с шагом 0,4 и построим график этой функции.

25

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

Рис. 8. Поиск корней уравнения

В рассматриваемом примере эти отрезки следующие: [–2,2; –1,8], [–0,6; –0,2], [1; 1,4]. В качестве приближенных значений корней возьмем числа –1,9; –0,5 и 1,2 и запишем их в ячейки С2, С3 и С4. В ячейку D2 введем формулу =С2^3+C2^2–2*C2–1, скопируем эту формулу с помощью маркера заполнения в ячейки D3 и D4. Таким образом, мы вычислили значения функции f(x)=х3+х2–2х–1 от приближенных значений корней этой функции.

Напомним, что если х1 – корень функции f(x), то значение функции в корне, т.е. f(x1), равняется нулю.

Теперь выделим ячейку D2 и выполним команду Сервис – Подбор параметра горизонтального меню. В появившемся диалоговом окне запишем следующее: в поле «Установить в ячейке» введем D2, в поле «Значение» введем число 0, в поле «Изменяя значение ячейки» введем С2. То есть добьемся того, чтобы значение функции f(x) равнялось нулю, подбирая точное значение корня х, приближенное значение которого записано в ячейке С2.

Вводить ссылки на ячейки D2 и С2 в поля диалогового окна можно и с помощью мыши, щелкнув левой кнопкой мыши по

26

соответствующей ячейке таблицы. При этом Excel автоматически превращает ссылки в абсолютные ($C$2).

Полученные после уточнения корней значения функции f(x) могут быть записаны в экспоненциальной форме, например, в ячейке D2 получилось число –2E–05. Это означает –2 10–5, т.е. –0,00002. Учитывая относительную погрешность 0,001, можно сказать, что это ноль, следовательно, корень найден и он равен –1,802.

Аналогично в ячейках С3 и С4 можно получить значения двух других корней нашего уравнения.

Итак, корни уравнения х3+х2–2х–1 = 0 равны:

х1 = –1,802; х2 = –0,445; х3 = 1,2469.

Работа с массивами

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

Общий порядок работы с массивами в Excel следующий:

1)ввести в ячейки электронной таблицы элементы исходных массивов (каждый элемент в отдельной ячейке);

2)выделить на листе диапазон пустых ячеек для результата (в зависимости от операции и размерности исходных массивов);

3)ввести формулу в строке формул;

4)нажать клавиши [Ctrl]+[Shift]+[Enter].

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

Операции над массивами

1. Сложение (вычитание) массивов.

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

Общий вид формулы: =массив1+массив2 (=массив1–массив2)

Пример

= А2:А8+В2:В8

27

= А2:А8–В2:В8

2. Умножение массива на число.

Размерность результата совпадает с размерностью исходного массива.

Общий вид формулы: =массив*число

Пример

=А2:А8*4

3. Матричное произведение.

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

Общий вид формулы: =МУМНОЖ(массив1;массив2)

Пример

=МУМНОЖ(А2:В5;D2:G3)

4. Вычисление обратной матрицы (обозначение А–1).

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

Общий вид формулы: =МОБР(массив)

Пример

=МОБР(А2:В3)

5. Вычисление определителя матрицы.

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

Общий вид формулы: =МОПРЕД(массив)

Пример

=МОПРЕД(А2:В3)

6. Транспонирование матрицы (обозначение АТ).

28

Размерность результата: количество строк равно количеству столбцов исходного массива, количество столбцов равно количеству строк исходного массива.

Общий вид формулы: =ТРАНСП(массив)

Пример

=ТРАНСП(А2:В3)

Решение системы линейных уравнений

Рассмотрим решение системы линейных уравнений вида:

а11x1+а12x2+…+ а1nxn= b1; а21x1+а22x2+…+ а2nxn= b2;

………..

an1x1+аn2x2+…+ аnnxn= bn.

Пусть А – матрица коэффициентов при неизвестных; В – вектор-столбец свободных членов; Х – вектор-столбец неизвестных, т.е.

Тогда систему уравнений можно записать в матричном виде:

А Х=В.

I способ

Решение системы уравнений имеет вид Х=А–1 В, где А–1 – матрица, обратная матрице А.

Для решения системы линейных уравнений необходимо:

1)ввести в ячейки электронной таблицы матрицу А;

2)ввести в ячейки электронной таблицы вектор-столбец В;

3)выделить ячейки для результата (вектор-столбец Х);

4)в строке формул ввести формулу =МУМНОЖ(МОБР(матрица А);массив В);

5)нажать клавиши [Ctrl]+[Shift]+[Enter].

29

II способ (метод Крамера)

Для решения системы линейных уравнений необходимо:

1)ввести в ячейки электронной таблицы матрицу А;

2)скопировать матрицу и вставить копию n раз;

3)заменить поочередно в каждой из копий 1-й, 2-й и т.д. столбцы на вектор-столбец В, получив вспомогательные матрицы А1, А2 и т.д.;

4)вычислить определители матриц А, А1, А2 и т.д.;

5)вычислить неизвестные x1, x2 и т.д., разделив определитель соответствующей вспомогательной матрицы на определитель матрицы А.

Сортировка данных

Для сортировки данных необходимо:

1)выделить таблицу (без заголовка);

2)выполнить команду Данные – Сортировка;

3)выбрать столбец, по которому нужно сортировать данные;

4)выбрать порядок сортировки (по возрастанию или убыванию);

5)если нужно, повторить п. 3 и 4 для других столбцов.

Выбор данных по условию (создание фильтра)

Для создания фильтра необходимо:

1)выделить таблицу (с заголовком);

2)выполнить команду Данные – Фильтр – Автофильтр;

3)снять выделение с таблицы;

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

Чтобы снова отобразить все данные таблицы, нужно нажать на кнопку и выбрать пункт «Все». Для отмены фильтра выполнить п. 1 и 2.

Скрытие данных

В Excel можно скрыть столбцы, строки или рабочий лист. Для того чтобы скрыть столбец (строку), необходимо выделить столбец

30