MS Excel. Расчеты с условиями. Работа со списками

⇐ ПредыдущаяСтр 7 из 10Следующая ⇒

Цель работы:

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

- освоить вычисления с помощью условных функций и с помощью функции условного суммирования для массивов;

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

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

- освоить приемы работы с базами данных и списками.

Общие сведения

Расчеты с условиями

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

Функция ЕСЛИ()

Синтаксис: ЕСЛИ(< условие>; < выражение1>; < выражение2> )

Условие – это логическое выражение, которое может принимать значение ИСТИНА или ЛОЖЬ.

< выражение1> и < выражение2 > могут быть числами, формулами или текстами. Текст должен быть заключен в кавычки.

Выполнение: если условие истинно, значение ячейки определяет < выражение1>, в противном случае – < выражение2>.

Логическое выражение состоит из констант, адресов или имен ячеек, знаков операций отношений (<, >, =, < =, > =, < > ) и логических операций И, ИЛИ, НЕ.

Логические операции в Excel используются как логические функции, при их вызове записывается знак операции, затем в круглых скобках перечисляются логические операнды, разделяемые точкой с запятой, например: ИЛИ(A4> 2; A4< -2 ).

Пример записи формул, содержащих условную функцию:

Пусть в таблице хранится информация о зачислении слушателей на курсы:

A	B	C	D
Список слушателей
ФИО	Баллы	Учебное заведение	Информация о зачислении
Петров Н.Г		лицей №1	не зачислен
Алексеев А.Л.		школа №12	зачислен
Андреева Е.Н.		лицей №1	зачислен
Тихонов К.П.		лицей №1	зачислен
Ветрова В.В.		лицей №4	зачислен

Тогда при условии зачисления тех, кто набрал не менее 12 баллов, в ячейку D3 введена формула

=ЕСЛИ(B3> =12, " зачислен", " не зачислен" ),

а при условии зачисления тех, кто набрал не менее 12 баллов и учится в лицее №1, в ячейку D3 должна быть введена формула

=ЕСЛИ(И(B3> =12, C3=" лицей №1" ), " зачислен", " не зачислен" ).

Функции СЧЁТЕСЛИ() и СУММЕСЛИ()

Функция СЧЁТЕСЛИ(). подсчитывает количество ячеек внутри интервала, удовлетворяющих заданному критерию.

Синтаксис: СЧЁТЕСЛИ(интервал; критерий)

Критерий ‑ критерий в форме числа, выражения или текста, который определяет, какие ячейки надо подсчитывать в заданном интервале. Например, критерий может быть записан следующим образом: 32, " 32", " > 32", " яблоки".

Пример:

Пусть имеется таблица, содержащая информацию о сборе фруктов:

А	В	С
Сбор фруктов
Фамилия	Вид	Вес, кг
Иванов	яблоки
Круглов	апельсины
Пономарев	персики
Алексеев	яблоки

Тогда функция СЧЁТЕСЛИ(A3: С6, " яблоки" ) возвращает значение 2 (количество сборщиков яблок), а СЧЁТЕСЛИ(A3: C6, " > 55" ) возвращает значение 3 (количество сборщиков, собравших более 55 кг фруктов). Точно такие же результаты дадут функции

СЧЁТЕСЛИ(B3: B6, " яблоки" ) и СЧЁТЕСЛИ(C3: C6, " > 55" ).

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

Функция СУММЕСЛИ() действует аналогично функции СЧЕТЕСЛИ(), но вычисляет сумму содержимого ячеек заданного диапазона.

Синтаксис: СУММЕСЛИ(интервал выбора; критерий; интервал суммирования ).

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

Например, для приведенной выше таблицы с данными о сборе фруктов по формуле =СУММЕСЛИ(B3: B6; " яблоки", C3: C6) вычисляется вес всех собранных яблок.

Если аргумент интервал суммирования опущен, то суммируются значения ячеек из диапазона интервал выбора. Например, для той же таблицы значение функции СУММЕСЛИ(С3: С6, " > 50" ) равно 218.

Функция условного суммирования для массивов

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

Массив – прямоугольные диапазоны формул или ячеек, которые Excel обрабатывает как единую группу.

Например, для подсчета количества сборщиков, собравших яблок больше 50 кг можно записать формулу

{=СУММ((B3: B6=" яблоки" )*(C3: C6> 50))}.

Фигурные скобки здесь обозначают операцию с массивами и получаются, если при вводе формулы нажать не клавишу [Enter], а одновременно три клавиши [Ctrl+Shift+Enter]. В данном применении функции СУММ знак * соответствует логической операции И, а знак + ‑ операции ИЛИ. Например, для таблицы " Мониторы. Учет" (см. задание 1 предыдущей лабораторной работы) при подсчете количества складов, имеющих мониторов любого вида меньше 5 шт, следует применить формулу

{=СУММ((B3: B7< 5)+(C3: C7< 5)+(D3: D7< 5))}.

Результатом будет значение 1.

Для подсчета количества учащихся лицея №1, набравших не менее 12 баллов (по данным таблицы " Список слушателей" ) можно применить формулу

{=СУММ((B3: B7> =12)*(C3: C7=" лицей №1" ))}.

Результатом будет значение 2.

Работа с массивом должна быть выполнена и для функции ЕСЛИ(), когда в качестве условия проверяются значения ячеек из диапазона.

Условное форматирование

В Excel возможно применение разных форматов к ячейке в зависимости от хранимых в ней данных.

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

Работа с базами данных и списками

Excel предоставляет возможность работать с базами данных, которые здесь называются списками. Список – таблица, построенная по принципу базы данных: множество однотипных строк-записей, разделенных на поля. Одна или две верхних строки списка должны содержать заголовки столбцов (имена полей). Приведенная ниже таблица, содержащая данные о сотрудниках, представляет собой список:

Сотрудники
ФИО	Возраст	Должность	Стаж	Оклад, руб
Алексеев Н.Г		программист
Пронина Е.Е.		бухгалтер
Воронин Н.П.		гл. бухгалтер
Алексеева А.Г.		лаборант

Имена полей выделены серым цветом. Запись хранит данные об одном сотруднике.

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

В Excel все операции по работе с данными содержатся в меню Данные.

Сортировка данных

Команда Данные→ Сортировка и фильтр→ Сортировка позволяет упорядочивать данные в списке. Курсор должен находиться в области списка. В диалоговом окне Сортировка следует выбрать последовательность полей сортировки. Например, при выборе в поле Сортировать по: возраст, а в поле Затем по: ФИО для приведенного выше списка получим:

Сотрудники
ФИО	Возраст	Должность	Стаж	Оклад, руб
Алексеева А.Г.		лаборант
Алексеев Н.Г		программист
Пронина Е.Е.		бухгалтер
Воронин Н.П.		гл. бухгалтер

Фильтрация данных

Команда Данные→ Фильтр позволяет выделять нужные записи. Фильтрация возможна через автоматический фильтр Автофильтр (для простых условий отбора) и через Расширенный – ручной (для более сложных условий отбора). Курсор должен находится в области списка.

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

При использовании Автофильтра на именах полей появятся кнопки, нажимая на которые можно задавать критерии фильтрации. В появившемся подменю пункт Первые 10 оставляет 10 наибольших или наименьших элементов в списке, пункт Все оставляет все элементы списка, а пункт Условие… вызывает диалоговое окно, в котором можно установить параметры фильтрации.

Для отмены фильтрации следует повторно вызвать команду Данные→ Фильт р и убрать галочку в поле Автофильтр .

Команда Расширенный фильтр позволяет:

§ задавать условия, соединенные логическим оператором ИЛИ для нескольких столбцов (для объединения условий с помощью логического оператора И проще дважды использовать команду Автофильтр);

§ задавать три и более условий для конкретного столбца с использованием, по крайней мере, одного логического оператора ИЛИ. Например, можно оставить на экране записи о сотрудниках, чьи фамилии начинаются с букв А, В или Л;

§ задавать вычисляемые условия;

§ извлекать строки из списка и вставлять эти копии в другую часть текущего листа. При извлечении строк с помощью Автофильтра копирование и вставку автоматически выполнить невозможно.

При использовании расширенного фильтра необходимо в диалоговом меню указать три области:

§ Исходный диапазон – диапазон данных из списка, для которого необходимо выполнить фильтрацию;

§ Диапазон условий – диапазон ячеек рабочего листа, в котором указаны критерии отбора;

§ Поместить результат в диапазон – диапазон рабочего листа, предназначенный для вывода отобранных записей. Установка диапазона возврата результатов становится доступной только при выбранном переключателе скопировать результат в другое место. В противном случае список отфильтруется на месте исходного диапазона.

Правила для задания текстовых условий:

§ единственная буква – поиск всех значений, начинающихся с этой буквы;

§ символы < или > ‑ поиск всех значений, которые находятся по алфавиту до или после введенного значения;

§ формула =”=текст” ‑ поиск всех значений, которые точно совпадают со строкой символов текст. Например, при задании =”=Алексеев”, будут найдены все строки, содержащие фамилию Алексеев, при задании фамилии Алексеев без формулы выберутся также строки с фамилиями Алексеева, Алексеевич и т. д.

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

Сотрудники

ФИО Возраст Должность Стаж Оклад, руб

Алексеева А.Г. лаборант

Алексеев Н.Г программист

Пронина Е.Е. бухгалтер

Воронин Н.П. гл. бухгалтер

Возраст Стаж

< 20 < 10

> 40 < 20

ФИО Возраст Должность Стаж Оклад, руб

Алексеева А.Г. лаборант

Воронин Н.П. гл. бухгалтер

Отменить фильтрацию можно командой Данные→ Фильтр→ Отобразить все.

Содержание работы

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

Задание 1

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

Добавьте в эту таблицу еще три столбца с оценками по дисциплинам Информатика, Математика, Английский язык перед столбцом Баллы (оценки выставляются по пятибалльной системе), подсчитайте значение среднего балла в столбце Баллы. Добавьте еще несколько строк. Заполните столбец с информацией о зачислении следующим образом: зачисленными считаются все, набравшие не менее 13 баллов либо набравшие 12 баллов, но имеющие по математике оценку 5. Ячейки столбца о зачислении должны иметь значение зачислен, остальные ячейки должны остаться пустыми.

Зачисленных слушателей выделите полужирным шрифтом с помощью условного форматирования. Набравших не менее11 баллов и не более 12 баллов выделите в столбце Баллы заливкой зеленого цвета.

Выполните сортировку:

· по алфавиту,

· по среднему баллу (по убыванию).

Подсчитайте общее количество зачисленных, количество зачисленных учащихся школы № 12, количество набравших более 12 баллов с помощью функции СЧЕТЕСЛИ и функции суммирования для массивов.

Выполните фильтрацию данных:

с помощью команды Автофильтр, оставив видимыми только тех, кто зачислен на курсы и учится в лицее №1;
с помощью команд Автофильтр и Расширенный фильтр, выделить всех зачисленных, обучающихся в лицее № 1 или в школе № 12,
с помощью команды Расширенный фильтр, выделив всех учащихся лицея № 1, получивших пятерку по математике и всех учащихся лицея № 4, получивших пятерку по информатике.

Задание 2

Постройте таблицу с данными о сборе фруктов, добавьте в нее несколько новых строк (не вводите новые виды фруктов). Выполните все приведенные в описании примеры расчетов.

Вычислите вес каждого вида собранных фруктов с помощью функции СУММЕСЛИ.

Вычислите вес собранных фруктов в английских фунтах (1 фунт = 0, 4536 кг) с помощью формулы для массивов:

введите новую ячейку с текстом Вес, фунты,
выделите диапазон значений для размещения результата (столбец Вес, фунты), наберите нужную формулу, сделав ссылку на весь диапазон исходных данных (Вес, кг),
завершите ввод формулы нажатием [Ctrl+Shift+Enter].

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

1. Какие логические функции имеются в Excel?

2. Что такое массив, как выполняется работа с массивами в Excel?

3. В чем заключается различие между функцией СУММЕСЛИ() и функцией условного суммирования для массивов?

4. Для чего используется условное форматирование?

5. Дайте определение базы данных.

6. Как называется база данных в Excel?

7. Как должен быть оформлен список в Excel?

8. Какие действия с базами данных выполняются в Excel?

9. В чем заключаются различия между сортировкой и фильтрацией?

10. В каких случаях используется расширенный фильтр?

Лабораторная работа № 8

⇐ Предыдущая 1 2 3 4 5 678 9 10 Следующая ⇒