Методика по Информатике / Excel / Лавриненко Л.Г.-Работа в табличном процессоре EXCEL / Ex_Лаб2

Лабораторная работа 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_, где  = 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) Сохраните содержимое рабочей книги.

15