Excel. Практическая работа №11. (2 часа).

  

Организация вычислений в электронных таблицах

 

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

 

Порядок выполнения работы

 

1.     Особенности ввода формул

В Excel все формулы начинаются со знака =. Формулы могут включать числовые величины, знаки арифметических операций (+ сложение, - вычитание, * умножение, / деление, Ù возведение в степень, % процент)перации сравнения, операции с текстом, функции, скобки, данные ячеек и имена.

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

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

Можно показать на рабочем листе формулы, а не результаты, если выполнить команды меню Сервис – Параметры – вкладка Вид – флажок Формулы. Повторное выполнение приведёт снова к отображению результатов расчётов.

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

 

Ø     Введите в ячейки следующую информацию (тренировочное задание).

 

 

A

B

C

D

1

5

7

=A1*B1

 

2

10

2

 

 

3

12

4

 

 

4

 

 

 

 

 

Ø     Измените данные в ячейке А1. Изменится ли значение в С1?

 

Ø     Включите режим просмотра формул на листах.

 

Excel выполняет вычисления в формулах в следующем порядке:

1. Возведение в степень и выражения в круглых скобках.

2. Умножение и деление.

3. Сложение.

Если не учитывать порядок выполнения действий, то при вводе формул могут возникнуть ошибки. Например, если вы хотите определить среднее арифметическое величин, находящихся в ячейках А1, А2 и А3 и введёте =А1+ В1 + С1/3, то получите неправильный результат. Верно будет =(А1+ В1 + С1)/3.

 

2.     Использование относительных и абсолютных ссылок на ячейки

 

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

 

Ø     Скопируйте формулу из ячейки С1 в ячейку С2, С3. Что изменилось?

 

Ø     Скопируйте формулу из ячейки С1 в ячейку D1. Что изменилось?

 

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

Ø     Заполните ячейки с А4 по А20 числами от 1 с шагом 3.

Указание

В ячейку А4 запишем число 1. В ячейке А5 должно быть число 1+3=4, поэтому запишем формулу =А4+3. Если мы скопируем эту формулу в ячейку А6, то она будет выглядеть как =А5+3 (т.е. 3+3=6). То есть можно копировать эту формулу в ячейки с А6 по А20, и мы получим желаемый результат.

 

Ø     Отключите режим просмотра формул. Верным ли получился результат заполнения?

Иногда при копировании формулы вам нужно, чтобы ссылки на ячейки не менялись. Тогда нужно использовать абсолютные ссылки. Чтобы сделать ссылку на ячейку в формуле абсолютной, нужно добавить знак $ перед буквой и номером, которые составляют адрес ячейки. Например, $A$1.

 

Ø     Исправьте формулу в ячейке С1 на =$A$1*B1.

 

Ø     Скопируйте формулу из ячейки С1 в ячейки С2, С3. Что изменилось?

 

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

 

Ø     Исправьте формулу в ячейке С1 на =$A1*B1.

 

Ø     Скопируйте формулу из ячейки С1 в ячейки С2, С3.

 

Ø     Скопируйте формулу из ячейки С1 в ячейки D1, D2.

Проанализируйте результаты копирования.

 

3. Встроенные функции

 

Функции – это стандартные формулы, которые обеспечивают выполнение определённого набора операций над заданным диапазоном величин. Например, для определения суммы величин в ячейках от А1 до Н1 можно задать функцию = СУММ (А1: Н1) вместо ввода = А1 + В1 + С1 + D1… .

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

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

 

Наиболее часто употребляющиеся функции Excel

 

Функция

Пример

Описание

ABS

 

КОРЕНЬ

 

ЦЕЛОЕ

 

ОСТАТ

 

СТЕПЕНЬ

 

ПИ

 

СРЗНАЧ

 

 

СЧЁТ

 

 

 

 

 

СЧЁТЕСЛИ

 

 

ЕСЛИ

 

 

 

 

МАКС

 

 

МИН

 

 

СУММ

 

 

СУММЕСЛИ

=ABS(A1)

 

=КОРЕНЬ(А1)

 

=ЦЕЛОЕ(А1)

 

=ОСТАТ(А1, В1)

 

=СТЕПЕНЬ(А1, В1)

 

=ПИ()

 

= СРЗНАЧ(B4: B9)

 

 

= СЧЁТ(А3:А7) 

 

 

 

 

 

=СЧЁТЕСЛИ(В4:В10, “Иванов”)

 

=ЕСЛИ(А3>=100;А3*2;А2*2)

 

 

 

=МАКС(В4:В10)

 

 

=МИН(В4:В10)

 

 

=СУММ(А1:А10)

 

 

=CУММЕСЛИ

(интервал, критерий, сумм_интервал)

Абсолютная величина аргумента.

 

Корень квадратный.

 

Целая часть числа.

 

Остаток от деления целого числа А на целое В.

 

А в степени В.

 

Число p.

 

Вычисляет среднее арифметическое нескольких чисел. 

 

Подсчитывает количество чисел в диапазоне. Например, если в диапазоне содержатся ячейки с текстом и ячейки с числами, то вы можете подсчитать, сколько в этом диапазоне находится чисел.

 

Подсчитывает, в скольких ячейках этого диапазона содержится текст «Иванов».

 

Позволяет вам ввести в формулу условие. В этом примере, если значение А3 больше или равно 100, то будет использоваться формула А3*2. Если А3 меньше 100, будет использоваться формула А2*2.

 

Возвращает максимальное значение в заданном диапазоне ячеек.

 

Возвращает минимальное значение в заданном диапазоне ячеек.

 

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

 

Суммирует все ячейки в сумм_интервал, только если соответствующие им ячейки в аргументе интервал удовлетворяют определённому критерию. Например, =СУММЕСЛИ (А24,>100,В2:В4) складывает те ячейки в диапазоне А2:А4, которым соответствуют ячейки в столбце В, большие 100.

 

Ø     С помощью функции СУММ в ячейке С1 просуммируйте содержимое ячеек А1 и В1.

 

Ø     В ячейке С2 найдите максимальное из А2 и В2.

 

Ø     В ячейке С3 найдите среднее арифметическое чисел в ячейках А3 и В3.

Пример использования функции ЕСЛИ

 

(Нахождение большего из 3х чисел)

В ячейки В7, В8, В9 введём три разных произвольных числа. В ячейку В11 запишем логическую функцию ЕСЛИ(), которая должна выбрать наибольшее из этих чисел:

=ЕСЛИ(И(В7>В8; В7>В9); В7; ЕСЛИ(И(В8>В7; В8>В9); В8; В9))

 

Так как одной из наиболее часто употребляющихся функций является функция СУММ, в Excel предусмотрен быстрый способ её ввода. Вам нужно просто выделить ячейку, в которую вы хотите вставить сумму, на стандартной панели инструментов щёлкнуть кнопку Автосумма, если выделенный программой диапазон окажется неверным, перетащить курсор мыши через нужный вам диапазон и нажать Enter.

 

Задания для самостоятельного выполнения

 

Все задания выполнять в одном файле PR11.xls на разных листах!

 

Задание 1.  (1 балл)

 

Рассчитайте температурную шкалу по Фаренгейту (F), Реомюру (R), Кельвину (К), если дана таблица температур по Цельсию (от -100  до +100). Формулы перевода температур:

tF = 1,8*С + 32;

tR = 0,8*С;

tK = С + 273,15.

Задание 2.  (2 балла)

1.2  Составьте прямоугольную таблицу умножения натуральных чисел до 20.

 

Задание 3. (2 балла)

 

Задача «Соревнования по плаванию».

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

·        3 заплыва;

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

·        для каждого участника определяется лучшее личное время;

·        определяется лучший результат соревнований;

·        для каждого участника определяется отклонение его личного лучшего результата от лучшего результата соревнований.

 

Задание 4. (2 балла)

 

Составьте таблицу вычисления среднего балла учащихся по N предметам, определения коэффициента на стипендию и суммы стипендии: ( коэф. * минимальн. стипенд., равную 62 руб.).

                   Ср. бал          коэффициент

                     <5                      0

                     [5..6)                  1

[6..8)                 1,2

[8..9)                 1,4

[9..10]                1,6

Задание 5. (3 балла)

Коммерческие структуры арендуют помещения под офис. Арендная плата вносится помесячно, не позднее 15 числа следующего месяца. В случае просрочки платежей каждый арендатор уплачивает арендодателю пеню в размере 0,1% от неуплаченной суммы за каждый день просрочки. Составьте отчёт о состоянии на 30 ноября текущего года арендной платы n фирм за октябрь месяц в следующем виде:

 

Название фирмы

Сумма оплаты

Дата оплаты

Пеня

Всего