Лаб работы Excel

20

Рис. 21. Окно диаграммы

Для объединения нескольких условий в единое, могут применяться функции И, ИЛИ. Синтаксис функций И и ИЛИ:

И(логическое_значение1;логическое_значение2; ...) ИЛИ(логическое_значение1;логическое_значение2; ...) ,

где логическое_значение1; логическое_значение2; .. – проверяемые условия.

Функции И или ИЛИ могут быть совместно использованы с функцией ЕСЛИ. Синтаксис:

ЕСЛИ(И(логическое_значение1;логическое_значение2);значение_ если_истина;значение_если_ложь)

Эту запись следует понимать так: Если логическое значение 1 И логическое значение 2 верно, то выполняется выражение šзначение_если_истинаŸ, иначе выполняется šзначение_если_ложьŸ.

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

21

ведётся, если отсутствует хотя бы одно условие, то автомобиль не заведётся. Запишем это условие следующим образом:

ЕСЛИ(И(бак заправлен; аккумулятор заряжен); заведётся; не заведётся).

Пример 3: На промежутке функции y = x +8 при x 3 и случаях.

x [ 5;5 ] вычислить значения x≤ 3 , и y = sin(x) в остальных

Последовательность действий

В столбец А с помощью автозаполнения вводится диапазон изменения аргумента х от –5 до 5. Столбец B содержит рассчитанные значения функции y. Окно ввода аргументов функции ЕСЛИ представлено на рис. 22. Результат вычисления представлен на рис. 23. График данной функции представлен на рис. 24.

Рис. 22. Окно ввода аргументов функции ЕСЛИ

22

Рис. 23. Окно результата вычисления

Рис. 24. Окно диаграммы

Задания для самостоятельной работы

1. Определить наименьший возраст студентов с помощью функции МИН. Результат поместить в ячейку D10. Результат представлен на рис. 25. В данном задании используется таблица, созданная в лабораторной работе №1 (рис. 10).

23

Рис. 25. Таблица сведений о студентах

срассчитанным минимальным возрастом

2.Рассчитать средний балл по каждому предмету (рис. 26). Результаты расположить в ячейках F10, G10, H10. Формат ячеек числовой с двумя знаками после запятой.

Рис. 26. Таблица сведений о студентах с рассчитанным средним баллом по каждому предмету

3. На промежутке x [ 5;5 ] вычислить значения функции y = 9 + x при x 1 или x 1 и y = 2 x 2 в остальных случаях. Построить график функции.

24

ЛАБОРАТОРНАЯ РАБОТА № 3

Работа со ссылками

Цель работы: Научиться производить расчёты с помощью абсолютных и относительных ссылок.

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

Можно также ссылаться на ячейки, находящиеся на других листах рабочей книги, в другой рабочей книге, или даже на данные другого приложения.

В Excel существует 2 вида ссылок:

Ссылка типа A1 называется относительной ссылкой, которая автоматически обновляется в случае копирования или переноса формулы из одной ячейки в другую. Например, если в ячейке A3 была записана формула =A1+A2, то при копировании содержимого A3 в ячейку C3 новая формула примет следующий вид: =C1+C2.

Абсолютные ссылки. В них кроме названия столбца и номера строки используется специальный символ $, который фиксирует данную часть ссылки и оставляет её неизменной при копировании формулы в другую ячейку. Обычно в таких ячейках содержатся значения констант. Например, если необходимо зафиксировать в формуле значение ячейки A1, которое не должно изменяться в случае копирования или переноса формулы, то абсолютная ссылка на эту ячейку будет выглядеть $A$1. Аналогично, если необходимо зафиксировать в ссылке только строку или только столбец, в котором находится используемая ячейка, то ссылка примет вид A$1 или $A1 соответственно.

25

Пример 1: Дано значение x = 0,01 и диапазон изменения x от –5 до 5 (рис. 27). Рассчитать значения x + x, используя абсолютную ссылку.

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

Рис. 27. Окно Excel с вычислением значений x + x

Пример 2: Составить таблицу Пифагора для значений от 1 до 15.

Последовательность действий

С помощью автозаполнения поместим значения от 1 до 15 в столбце А и в первой строке (рис. 28). При расчёте таблицы каждое значение строки необходимо перемножить с каждым значением столбца и наоборот. Для этого используются абсолютные ссылки, которые фиксируют нужный столбец или строку. В ячейке B2 следует записать формулу: =$A2*B$1. Происходит умножение всех строк столбца А на все столбцы первой строки и наоборот. На пересечении этих строк и столбцов содержится результат перемножения. Рассчитанное значение в ячейке B2 следует растянуть вниз, а затем, не убирая выделения, направо.

26

Рис. 28. Окно Excel с таблицей Пифагора

Задание для самостоятельной работы

1. Дано значение x, записанное в ячейку B1 (рис. 29). Диапазон изменения x от –5 до 5. С помощью абсолютных ссылок рассчитать значения x + x. Вычислить значения функции вида y(x)= x2 2x + 3, также вычислить значения функции y, зависящей от аргумента x + x. Построить график функции y(x).

Рис. 29. Окно Excel с расчётом значений функции y и график функции y(x)

27

ЛАБОРАТОРНАЯ РАБОТА № 4

Анализ данных в Excel. Сортировка и фильтрация данных

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

Электронные таблицы Excel часто используют для ведения простейших баз данных. Таблица, используемая в качестве базы данных, обычно состоит из нескольких столбцов, являющихся полями базы данных. Каждая строка представляет отдельную запись.

Существуют ограничения, накладываемые на структуру базы данных:

первый ряд базы данных должен содержать неповторяющиеся имена полей;

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

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

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

Если данные представлены в виде таблицы, Excel позволяет упростить процесс поиска данных путем сортировки и фильтрации данных. Процесс сортировки был подробно рассмотрен в лабораторной работе №1.

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

Фильтрация записей в базе данных

спомощью автофильтра

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

28

По команде Автофильтр в ячейках, содержащих заголовки полей появляются раскрывающие кнопки. Щелчок на такой кнопке открывает доступ к списку вариантов фильтрации. Варианты фильтрации записываются обычно с помощью простых или составных условий. Составные условия формируются с помощью логических выражений и логических операции И или ИЛИ. Записи, не удовлетворяющие условию фильтрации, не отображаются.

Пример 1: Дана база данных студентов и их оценок по предметам. С помощью автофильтра выбрать студентов определённой группы.

Последовательность действий

В данном примере используется база данных, созданная в лабораторной работе №1 (рис. 10). Следует скопировать её на Лист 1 текущей книги.

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

Рис. 30. Окно Excel с автофильтром данных

При помощи этих кнопок можно выбрать критерий по каждому полю таблицы. При выборе одной или нескольких опций из опций раскрывающегося списка, на экран могут выводиться все строки, соответствующие выбранному критерию. Отберём всех студентов группы с номером 5433. Для этого нужно:

29

1)открыть раскрывающийся список в столбе с именем №

группы;

2)снять галочку с пункта Выделить все;

3)поставить галочку рядом с числом 5433.

Записи, не удовлетворяющие данному критерию, отображаться не будут.

Чтобы отобразить все записи, нужно поставить галочку рядом с надписью Отобразить все. Чтобы отменить использование автофильтра, нужно повторно выбрать команду Фильтр на вкладке Данные.

Пример 2: С помощью автофильтра выбрать студентов, родившихся позднее 1 января 1988 г.

Последовательность действий

1.Скопировать базу данных (рис. 30) на Лист 2.

2.Открыть раскрывающийся список в столбце с именем

Дата рождения.

3.Выбрать из списка пункт Фильтры по дате → Настраиваемый фильтр. В появившемся окне Пользовательский автофильтр выбрать критерий отбора, в нашем случае – больше либо равно и вписать нужную дату (рис. 31). После нажатия кнопки ОК в таблице останутся только записи, удовлетворяющие критерию.

Рис. 31. Окно пользовательского автофильтра