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

Лабораторная работа  2

ВЫЧИСЛЕНИЯ  В  ЭЛЕКТРОННЫХ  ТАБЛИЦАХ

2.1. В в о д    ф о р м у л ы

Вычисление значения ячейки выполняется вводом формулы. Формулы всегда начинаются со знака равенства “=”.

Формулы позволяют выполнять обычные математические операции над значениями из ячеек рабочей таблицы. Например, надо сложить значения в ячейках В1 и В2 и вывести их сумму в ячейке В5. Для этого необходимо поместить курсор в ячейку В5 и ввести формулу “= В1 + В2”.

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

В формулах можно использовать следующие операторы:

а р и ф м е т и ч е с к и е  –

+    –  сложение;	–      –  вычитание;
/     –  деление;	*      – умножение;
Ù    –  возведение в степень;	%     –  процент;

с р а в н е н и я –

=      – равно;	<>     – не равно;
>      – больше;	<       – меньше;
>=    – не меньше;	<=     – не больше;

т е к с т а –

& – соединение текстовых значений.

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

2.2. С о з д а н и е   ф о р м у л ы   с   п о м о щ ь ю   м ы ш и

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

поместить курсор в ячейку, в которую хотите ввести формулу;

ввести знак равенства  “=” ;

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

ввести оператор (например, знак  “+”) или другой символ, продолжающий формулу;

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

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

2.3. А б с о л ю т н ы е   и   о т н о с и т е л ь н ы е   а д р е с а   я ч е е к

Существует три основных типа адресов (ссылок): относительные, абсолютные и смешанные.

Относительные  ссылки указывают адрес ячейки относительно той, в которой находится формула, обозначаются А1, В4 и т. д.

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

Смешанные ссылки сочетают абсолютный и относительный адрес, например: A$1, $A1, B$4 и т. д.

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

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

Например, в ячейке А1 содержится константа 4, в ячейках от В1 до В10 –  значения от 0,1 до 1 шагом 0,1. Для того чтобы получить результат в ячейках D1:D10 по формуле 4b_i, где i = 1, 2, …, 10, необходимо набрать в ячейке D1 “=$A$1*B1” и скопировать формулу в ячейки D2, D3, …, D10. При этом в D2 будет фраза “$A$1*B2”, в D3 – “$A$1*B3” и т. д., где содержимое $A$1 не изменяется, так как адрес (ссылка) является абсолютным, а В1 изменяется на В2, В3, …, В10, так как адрес относительный.

Для обозначения интервала ячеек в формулах необходимо использовать символ  “:”, например: А2 : A5.

Для обозначения группы несмежных ячеек надо применить символ “;”, например: А2;В5; Е10.

2.4. Р е д а к т и р о в а н и е   ф о р м у л

Формулы редактируются так же, как и содержимое ячеек.

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

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

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

Одна из самых полезных возможностей EXCEL  – это широкий выбор функций, которые позволяют производить различные типы вычислений.    Каждая функция имеет синтаксис записи:

ИМЯ ФУНКЦИИ ( аргумент 1; аргумент 2; …).

Аргументами функций могут быть числа, тексты, логические значения, значения ошибки, ссылки, массивы. В десятичных числах целая часть отделяется от дробной символом “,”, например: –30,003.

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

Логическими значениями являются ИСТИНА и ЛОЖЬ. Логическими аргументами  могут быть также выражения сравнения, для которых может быть вычислено значение  ИСТИНА или ЛОЖЬ, например: B10 > 20.

Например, функция СРЗНАЧ вычисляет среднее арифметическое ряда значений. Выражение “= СРЗНАЧ(6;12;15;16)” даст результат 12,75. Если значения 6, 12, 15, 16 хранятся в ячейках  B10 – B15, то формулу можно записать так: “= СРЗНАЧ( B10:B15)”.

Функция СУММ применяется для определения суммы значений,                например: “ = СУММ (В10: В15) ”. Будут просуммированы числа 6, 12, 15, 16.

Ввести функцию в формулу удобно с помощью Мастера функции. Мастер функций позволяет вводить функцию в создаваемую формулу. Для этого необходимо выполнить следующее:

поместить курсор в ячейку, в которую надо ввести функцию;

в стандартной панели инструментов щелкнуть на кнопке мастера функций ¦_х  или выполнить команду Вставка + Функция;

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

в списке Функция выбрать функцию и щелкнуть на кнопке ОК;

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

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

щелкнуть в диалоговом окне на кнопке  ОК.

2.6. А в т о м а т и ч е с к о е   с у м м и р о в а н и е

Самый простой метод подведения итогов в таблице – автосуммирование. Для этого необходимо поместить курсор в ячейку ниже столбца или правее строки, значения которой  нужно просуммировать и щелкнуть мышью на кнопке стандартной панели инструментов Автосуммирование (на ней изображен символ  “å”). Затем нажать кнопку Enter.

При суммировании элементов матрицы по столбцам и по строкам удобно выделить ячейки матрицы с дополнительными строкой и столбцом, после чего нажать кнопку “å”. Автоматически получится сумма всех строк и стобцов матрицы.

2.7. Ф о р м у л ы    д л я   р а б о т ы    с   м а с с и в а м и

Формулы для массивов (табличные формулы) позволяют произвести множество вычислений, записав одну формулу. Например, нужно перемножить значения столбца  А2 : А6 на соответствующие В2 : В6. Результат записать в   С2 : С6 без копирования формулы.

Необходимо выполнить следующие действия:

выделить ячейки результата С2 : С6;

ввести знак “=”;

выделить ячейки  А2 : А6;

ввести знак “*”;

выделить В2 : В6;

нажать клавиши клавиатуры Shift + Ctrl + Enter.

В строке формул высветится формула “{= А2 : А6 * В2 : В6}”, а во всех ячейках С2 : С6 получится результат.

З А Д А Н И Е  2

1) Запустите программу EXCEL.

2) В собственном каталоге создайте файл с именем “lab_2.хls”.

3) Первый лист рабочей книги назовите “Лаб. № 2 (ввод формул)”.

4) В ячейке  А1 напишите свою фамилию.

5) Создайте табель успеваемости по образцу табл. 2. Просуммируйте элементы в каждом столбцe и каждой строке. Средний балл считайте с использованием формул.

Т а б л и ц а  2

	Математика	Экономика	Информатика	Средний балл
Иванов	5	3	4	4,00
Петров	4	4	5	4,33
Сидоров	3	5	5	4,33
Яковлев	4	5	3	4,00
Средний балл	4	4,25	4,25

6) Подсчитайте количество оценок “5” по каждому предмету. Распечатайте список студентов, средний балл которых больше “4”.

7) Вычислите y = 2 x²  + 3 х + 5, где аргумент  х  меняется от 0,1 до 1 шагом 0,1. Для задания констант 2, 3, 5 используйте абсолютные ссылки, а для аргумента  х  – относительные.

8) Для матрицы размером 4´4 вычислите ее определитель, обратную к ней матрицу, возведите ее в квадрат и найдите транспонированную матрицу, используя табличные формулы.

9) Сохраните содержимое рабочей книги.