Функции для работы с базами данных

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

ПО КУРСУ “КОМПЬЮТЕРНЫЕ ИНФОРМАЦИОННЫЕ ТЕХНОЛОГИИ”

РАЗВИТЫЕ ФУНКЦИИ ДЛЯ ОБРАБОТКИ ДАННЫХ В EXCEL

Цель работы: изучение встроенных функций процессора Excel для сложных операций обработки данных

Функции для работы с базами данных

Для данной работы требуется ввести исходные данные (сведения о заработной плате работников некоторой организации), как показано на рисунке 1, начиная с ячейки A1.

Рисунок 1 – Исходные данные

В Excel имеется набор функций для операций с базами данных: БСЧЕТ (подсчет строк базы данных, содержащих заполненное числовое поле), БСЧЕТА (подсчет строк базы данных, содержащих заполненное поле любого типа), ДМАКС (выбор максимального значения поля), ДМИН (выбор минимального значения поля), БДСУММ (суммирование значений поля), ДСРЗНАЧ (подсчет среднего значения по полю) и другие. Эти функции объединены в категорию “Работа с базой данных”. Все эти функции имеют следующий формат:

ФУНКЦИЯ(база_данных, поле, критерий),

где база_данных – диапазон ячеек, содержащих базу данных (включая заголовки);

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

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

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

Пример 1 –Подсчитать количество работников отделов 1, 3 и 4.

1В рабочем листе с базой данных указать критерий, как показано на рисунке 2.

2Установить курсор в любую свободную ячейку рабочего листа.

3Вызвать функцию БСЧЁТА.

4В появившемся окне указать следующие параметры функции БСЧЁТА:
База_данных: A1:D15; Поле: A1 (т.е. ячейку с именем поля, для которого выполняется подсчет значений, в данном случае подсчитываются фамилии); Критерий: D18:D21. Нажать OK.

5Убедиться, что количество работников подсчитано правильно.

Пример 2 -Подсчитать количество следующих работников: работники отдела 1 с зарплатой от 250 ден.ед. и выше, отдела 2 – с зарплатой от 150 до 400 ден.ед., отдела 3 – от 300 ден.ед. и выше.

Указать критерий, как показано на рисунке 3. В любой свободной ячейке ввести функцию БСЧЁТА со следующими параметрами: База_данных: A1:D15; Поле: A1; Критерий: C18:E21.

Рисунок 2 – Критерий для примера 1	Рисунок 3 – Критерий для примера 2

Пример 3 -Подсчитать количество следующих работников: все работники отделов 1 и 4; стажеры из отдела 2; совместители и стажеры из отдела 3.

Критерий для этой задачи показан на рисунке 4.

Пример 4 -Подсчитать количество следующих работников: все работники отдела 3, а также все совместители и стажеры (из всех отделов).

Критерий для этой задачи показан на рисунке 5.

Рисунок 4 – Критерий для примера 3	Рисунок 5 – Критерий для примера 4

Пример 5 -Подсчитать количество всех работников, кроме внештатных.

Эту задачу можно решить следующим образом: подсчитать количество работников, для которых указан какой-либо номер отдела. Для этого можно с помощью функции БСЧЁТ подсчитать количество записей базы данных, содержащих заполненное поле Отдел. В данном случае требуется использовать именно функцию БСЧЁТ (а не БСЧЁТА), так как поле Отдел – числовое. Критерий в этом случае указывать не требуется, так как подсчет будет выполняться по всей базе данных. Для решения задачи следует в любой свободной ячейке ввести функцию БСЧЁТ со следующими параметрами: База_данных: A1:D15; Поле: C1; Критерий: A1:D15 (т.е. вся база данных).

Примечание – Конечно, эту задачу можно решить и многими другими способами, в том числе с использованием критерия, аналогично предыдущим примерам.

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

Вид экрана при решении этой задачи показан на рисунке 6. В данной задаче критерий задается с использованием формулы. В ячейке C19 введена следующая формула критерия: =D2>СРЗНАЧ($D$2:$D$13). Для решения задачи следует в любой свободной ячейке ввести функцию БСЧЁТА со следующими параметрами: База_данных: A1:D15; Поле: A1; Критерий: C18:C19.

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

Рисунок 6 – Вид экрана при решении примера 6	Рисунок 7 – Вид экрана при решении примера 7

Пример 7 -Подсчитать количество стажеров и совместителей, имеющих зарплату выше средней по предприятию.

Вид экрана при решении этой задачи показан на рисунке 7. В данной задаче критерий задается с использованием формулы. В ячейках C19 и C20 введена такая же формула критерия, как и в примере 6. Для решения задачи следует в любой свободной ячейке ввести функцию БСЧЁТА со следующими параметрами: База_данных: A1:D15; Поле: A1; Критерий: B18:C20.

Пример 8 –Найти максимальную зарплату по предприятию.

Для решения задачи требуется в любой свободной ячейке ввести функцию ДМАКС со следующими параметрами: База_данных: A1:D15; Поле: D1 (т.е. поле, из которого требуется выбрать максимальное значение); Критерий:A1:D15 (т.е. вся база данных).

Пример 9 –Найти сумму зарплаты, выплачиваемой всем работникам, кроме штатных.

Критерий для этой задачи показан на рисунке 8. Для решения задачи требуется в любой свободной ячейке ввести функцию БДСУММ со следующими параметрами: База_данных: A1:D15; Поле: D1 (т.е. поле, для которого выполняется суммирование); Критерий:ячейки, где указан критерий, приведенный на рисунке 8.

Пример 10 –Найти среднюю зарплату штатных работников отдела 1.

Критерий для этой задачи показан на рисунке 9. Для решения задачи требуется в любой свободной ячейке ввести функцию ДСРЗНАЧ со следующими параметрами: База_данных: A1:D15; Поле: D1 (т.е. поле, для которого вычисляется среднее); Критерий:ячейки, где указан критерий, приведенный на рисунке 9.

Рисунок 8 – Критерий для примера 9	Рисунок 9 – Критерий для примера 10

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

- найти количество работников, получающих зарплату от 200 до 300 ден.ед.;

- найти количество работников отдела 1, получающих зарплату выше средней зарплаты по своему отделу (указание: сначала найти среднюю зарплату по отделу, используя функцию ДСРЗНАЧ, затем воспользоваться функцией БСЧЁТА с критерием, заданным в виде формулы);

- найти минимальную зарплату штатного работника.

Функции с условиями

Под функциями с условиями будем понимать функции, в которых могут задаваться какие-либо условия, и возвращаемое функцией значение зависит от того, истинны или ложны эти условия. В данном разделе рассматриваются основные из этих функций. Рассматриваемые функции применимы для обработки не только баз данных (как функции, рассмотренные в разделе 1), но и любых других данных.

Функции И и ИЛИ

Эти функции возвращают значение ИСТИНА или ЛОЖЬ в зависимости от заданных условий. Функции входят в категорию “Логические”. Формат функций следующий:

И (условие_1; условие_2; …;условие_n)

ИЛИ (условие_1; условие_2; …;условие_n)

Функция И возвращает значение ИСТИНА, если истинны все заданные в ней условия; если хотя бы одно из них ложно, то возвращается значение ЛОЖЬ. Функция ИЛИ возвращает значение ИСТИНА, если истинно хотя бы одно из заданных в ней условий; если все эти условия ложны, то возвращается значение ЛОЖЬ.

Пример 11 –Пусть в ячейку A1 введено значение 7, в ячейку B1 – значение 5, в ячейку C1 – значение 2. Если ввести в любую свободную ячейку функцию =И(A1>3;B1<10;C1=2), то будет получено значение ИСТИНА, так как все заданные условия верны. Если ввести =И(A1>3;B1<4;C1=2), то будет получено значение ЛОЖЬ, так как одно из условий (B1<4) неверно.

Функция =ИЛИ(A1>3;B1<4;C1=2) возвращает значение ИСТИНА, так как среди указанных условий есть верные. Функция =ИЛИ(A1>10;B1<4;C1>7) возвращает значение ЛОЖЬ, так как все условия, указанные в функции, ложны.

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

Функция ЕСЛИ

Формат функции ЕСЛИ следующий:

ЕСЛИ (условие; значение_1;значение_2)

Если условие истинно, то функция возвращает значение_1, если ложно – значение_2. Условие может включать функции И и ИЛИ. Значение_1 и значение_2 могут представлять собой как конкретные значения (числа или текст), так и формулы, в том числе с использованием вложенных функций ЕСЛИ, а также функций И и ИЛИ. Допустимая глубина вложенности функций ЕСЛИ – до 7.

Функция ЕСЛИ входит в категорию “Логические”.

Пример 12 –Некоторая организация при приеме сотрудников на работу проводит их тестирование на знание английского языка и делопроизводства. Для приема на работу необходимо иметь оценку не ниже 8 баллов по английскому языку и не ниже 6 баллов – по делопроизводству. Результаты тестирования претендентов на работу приведены на рисунке 10.

Рисунок 10 – Исходные данные

Требуется получить в столбце D результаты тестирования в виде сообщений “принят” или “не принят”.

1Перейти на свободный рабочий лист. Ввести исходные данные, как показано на рисунке 10.

2В ячейке D2 вызвать функцию ЕСЛИ. В появившемся окне указать следующие параметры функции: Лог_выражение: И(B2>=8;C2>=6); Значение_если_истина: “принят”; Значение_если_ложь: “не принят”.

Примечание – Можно не вызывать функцию ЕСЛИ через меню, а просто ввести в ячейке D2 следующую формулу: =ЕСЛИ(И(B2>=8;C2>=6);"принят";"не принят").

3Распространить содержимое ячейки D2 на ячейки D3:D9.

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

Пример 13 –Пусть организация принимает сотрудников на работу по результатам тестирования по следующим правилам: на работу принимаются претенденты, получившие не ниже 8 баллов по английскому языку и не ниже 6 баллов – по делопроизводству, а также получившие от 6 до 7 баллов по английскому языку и не ниже 8 баллов – по делопроизводству.

Для получения сообщений о результатах тестирования необходимо в ячейке D2 ввести: =ЕСЛИ(ИЛИ(И(B2>=8;C2>=6);И(B2>=6;B2<=7;C2>=8));"принят";"не принят"). Распространить эту формулу на ячейки D3:D9.

Пример 14 –Пусть при приеме на работу проводится тестирование по английскому языку, делопроизводству, а также по офисным компьютерным программам. На работу принимаются претенденты, имеющие средний результат тестирования не ниже 7 баллов; при этом ни по одному из тестов оценка не должна быть ниже 5 баллов.

1Скопировать на новый рабочий лист исходные данные, приведенные на рисунке 10. В столбце D привести результаты теста по офисным программам (оценки ввести самостоятельно).

2В ячейке E2 ввести: =ЕСЛИ(И(СРЗНАЧ(B2:D2)>=7;B2>=5;C2>=5;D2>=5);"принят";"не принят").

3Распространить эту формулу на ячейки E3:E9.

Пример 15 –Пусть по базе данных о работниках предприятия (см. рисунок 1) требуется вычислить налог с заработной платы каждого сотрудника. Налог составляет 9% при заработной плате менее 200 ден.ед, 12% - при заработной плате от 200 до 400 ден.ед., 15% - свыше 400 ден.ед.

Для этого на рабочем листе с базой данных о работниках предприятия в ячейке E2 ввести формулу: =ЕСЛИ(D2<200;0,09*D2;ЕСЛИ(И(D2>=200;D2<400);0,12*D2;0,15*D2)). Распространить ее на ячейки E3:E15.

Функция СЧЕТЕСЛИ

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

Формат функции СЧЕТЕСЛИ следующий:

СЧЕТЕСЛИ (диапазон; критерий).

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

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

Для этого необходимо в любой свободной ячейке вызвать функцию СЧЕТЕСЛИ. В окне параметров функции указать: Диапазон: D2:D15; Критерий: “>=400”. Нажать OK.

Примечание – Можно не вызывать функцию СЧЕТЕСЛИ через меню, а просто ввести в свободной ячейке следующую формулу: =СЧЁТЕСЛИ(D2:D15;">=400").

Пример 17 –Подсчитать количество стажеров.

Для этого в любой свободной ячейке ввести: =СЧЁТЕСЛИ(B2:B15;"стажер").

Пример 18 –Подсчитать количество работников отдела 1.

Для этого в любой свободной ячейке ввести: =СЧЁТЕСЛИ(C2:C15;1).

Пример 19 –Подсчитать количество работников каждого из отделов.

Конечно, можно решить эту задачу, выполнив для каждого из отделов действия, указанные в примере 18. Рассмотрим другой способ решения, не требующий многократного повторения одинаковых действий.

1В ячейках A20, A21, A22, A23 (или в других свободных ячейках в одном столбце) ввести номера отделов: 1, 2, 3, 4.

2В ячейке B20 ввести: =СЧЁТЕСЛИ($C$2:$C$15;A20). Это означает, что требуется подсчитать количество ячеек в диапазоне C2:C15, значение которых равно ячейке A20 (т.е. 1).

3Распространить содержимое ячейки B20 на ячейки B21:B23.

Примечание – Для функции СЧЕТЕСЛИ невозможно задать сложные условия (например, условия для нескольких ячеек, или несколько условий для одной ячейки).

Функция СУММЕСЛИ

Функция СУММЕСЛИ предназначена для суммирования ячеек, соответствующих некоторому критерию. Функция входит в категорию “Математические”.

Формат функции СУММЕСЛИ следующий:

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

Суммируются ячейки в диапазоне суммирования, если соответствующие ячейки в диапазоне отбора удовлетворяют критерию.

Пример 20 –Подсчитать сумму зарплат, выплачиваемых стажерам.

Для этого в свободной ячейке ввести: =СУММЕСЛИ(B2:B15;"стажер";D2:D15). Это означает, что требуется вычислить сумму тех ячеек из диапазона D2:D15, для которых в соответствующих ячейках диапазона B2:B15 указано слово “стажер”.

Пример 21 –Подсчитать сумму зарплат, выплачиваемых работникам, имеющим зарплату свыше 300 ден.ед.

Для этого в свободной ячейке ввести: =СУММЕСЛИ(D2:D15;">300";D2:D15).

Примечание – Так как в данном случае диапазон отбора и диапазон суммирования совпадают, можно было указать только диапазон отбора: =СУММЕСЛИ(D2:D15;">300"). Такая формула означает, что должны суммироваться ячейки из диапазона D2:D15, содержащие значения свыше 300.

Пример 22 –Подсчитать суммарную зарплату работников каждого из отделов.

Пусть номера отделов (1, 2, 3, 4) введены в ячейках A20, A21, A22, A23 (см. пример 19). Для подсчета суммарной зарплаты каждого отдела требуется выполнить следующее.

1В ячейке C20 ввести: =СУММЕСЛИ($C$2:$C$15;A20;$D$2:$D$15).Это означает, что требуется вычислить сумму ячеек из диапазона D2:D15, для которых соответствующая ячейка диапазона С2:С15 равна ячейке A20 (т.е. содержит номер отдела 1).

2Распространить содержимое ячейки C20 на ячейки C21:C23.

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

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

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

Не нашли, что искали? Воспользуйтесь поиском по сайту: