Формулы и функции в электронных таблицах

Практическая работа

Тема: Формулы и функции в электронных таблицах

Цель: научиться создавать и копировать формулы, выполнять сортировку и фильтрацию данных в MS Excel.

Краткие теоретические сведения

Формулой в Ехсеl называется последовательность символов, которая начинается со знака равенства (=) и содержит вычисляемые элементы (операнды) и операторы.

Операндами могут быть: постоянные значения, ссылки на ячейки (диапазон ячеек), имена, функции.

Существуют четыре вида операторов:

• арифметические;

• операторы сравнения;

• текстовый оператор «&», который используется для обозначения операции объединения нескольких последовательностей символов в одну;

• адресные операторы.

Арифметические операторы

Функции в Ехсеl - это готовые стандартные формулы, которые применяются для выполнения определенных вычислений. Значения, которые используются для вычисления функций, называются аргументами. Значения, возвращаемые функциями в качестве ответа, называются результатами.

Примеры функций: СУММ( вычисляет сумму), СРЗНАЧ (вычисляет среднее значение), МИН(находит минимальное) и т.д.

Вставка функции:

1. Выделить ячейку, в которой должен находиться результат;

2. Выполнить команду ФормулыВставить функцию

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

4. Нажать ОК или Enter.

Например:

=СУММ(А1: А150) – вычислить сумму чисел, находящихся в ячейках с А1 по А150

=МИН(В4:В10)- найдет минимальное, среди чисел в ячейках с В4 по В10

СУММ (С1; С2; С8) – просуммирует числа, находящиеся в ячейках С1, С2 и С8.

Примеры: СУММ ( вычисляет сумму), СРЗНАЧ (вычисляет среднее значение), МИН(находит минимальное), МАКС (находит максимальное), КОРЕНЬ (вычисляет корень числа). Рассмотрим еще некоторые функции.

Статистические функции

СУММЕСЛИ – суммирует числа, удовлетворяющие какому-либо условию

СЧЕТЕСЛИ – подсчитывает количество чисел, удовлетворяющих какому-либо условию.

СЧЕТ – количество чисел в указанных ячейках.

Математические функции.

ABS – определяет модуль числа

SIN – определяет синус угла. Угол должен быть в радианах.

COS - определяет косинус угла. Угол должен быть в радианах.

ПИ () – результат работы функции – число ПИ, с точностью до 15 знака

РАДИАНЫ – переводит градусную меру угла в радианы

Логические функции ЕСЛИ, И, ИЛИ, НЕ

И – результат Истина, если все ее аргументы имеют значение Истина.

результат Ложь, если хотя бы один аргумент имеет значение Ложь

ИЛИ – результат Истина, если хотя бы один аргумент имеет значение Истина.

результат Ложь, если все аргумента имеют значение Ложь

НЕ – результат Истина, если аргумент имеет значение Ложь.

результат Ложь, если аргумент имеет значение Истина.

Сортировка – упорядочивание данных по определенному параметру.

Виды: по возрастанию, по убыванию.

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

Фильтрация – выделение определенных записей, которые удовлетворяют заданным критериям.

Два режима фильтрации: автофильтр (Данные – Фильтр) и расширенный фильтр (Данные - Фильтр – Дополнительно).

Задание и порядок выполнения

1. Запустите Microsoft Excel, решите с помощью Excel следующую задачу: известны три стороны треугольника и высота. Найти периметр, полупериметр и площадь треугольника.

• Чтобы найти периметр, нужно в ячейку F3 ввести следующую формулу =B3+C3+D3

• Чтобы найти полупериметр, нужно в ячейку G3 ввести формулу =(B3+C3+D3)/2

• Чтобы найти площадь, нужно в ячейку H3 ввести формулу =1/2*E3*B3 (из курса математики мы знаем S=1/2 ha, где h- высота, а- основание треугольника)

Введите другие значения сторон и высоты. Убедитесь, что результат изменился.

2. На Листе 2 создайте таблицу согласно образцу:

• Выполните сортировку товаров по возрастанию их цены.

• Скопируйте введенную информацию на Лист 3 и Лист 4.

• На Листе 3 С помощью автофильтра выберите в списке такие принтеры, цена которых не превышает 200 у.е. Для этого выделите ячейку «Цена (у.е.)», выберите Данные – Фильтр, щелкните по кнопке , в появившемся окне выберите пункт «Числовые фильтры» и укажите условие Ok, проследите за изменениями.

• Выберите Данные – Фильтр – Автофильтр, чтобы отобразить все товары.

• Выберите из списка такие товары, цена на которые превышает 2000 у.е.

• Переименуйте Лист 3, назовите его «Автофильтр».

• Перейдите на Лист 4, при помощи Расширенного фильтра выполните фильтрацию оставив только те принтеры, цена которых превышает 150 у.е. Для этого под главной таблицей создайте таблицу-критерий с такой же шапкой, как у главной таблицы, в поле товар укажите «Принтер», в поле «Цена (у.е.)» 150.

Выделите главную таблицу, выберите Данные – Фильтр – Дополнительно, в появившемся окне щелкните рядом с пунктом Диапазон условий и выделите таблицу-критерий, щелкните по кнопке Ok. Проследите за изменениями.

• Переименуйте Лист 4 в лист Расширенный фильтр.

3. На новом листе (Лист 5) наберите следующую таблицу успеваемости: введите семестровые оценки по трем предметам для 5 студентов вышей группы и количество пропусков за семестр. Найдите:

а) средний бал каждого студента

б) количество троечников по первому предмету

в) количество отличников по второму предмету

г) общее количество пропусков

д) предусмотрите столбец Стипендия, в котором будет выводится сообщение «Стипендия не положена», если средний бал ниже 4, если средний бал равен или больше 4, то выводится сумма стипендии 1100 руб.

Лист назовите Стипендия.

4. Сохраните книгу под именем «Расчеты» в папку своей группы.

5. Продемонстрируйте результаты работы преподавателю.

Дополнительное задание

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

2. Дан прямоугольный параллелепипед со сторонами а, b и с. Вычислить объем V=abc

3. Дан прямоугольный параллелепипед со сторонами а, b и с. Вычислить площадь поверхности: S=2(ab+bc+ac)

4. Задан радиус окружности r. Вычислить площадь круга

Контрольные вопросы

1. Что такое формула в MS Excel?

2. Какие арифметические операторы вы знаете? Каково их обозначение в MS Excel?

3. Как создать формулу в MS Excel?

4. Какие операторы используют в формулах?