2015-04-01
612
Вариант 1. По данным таблицы успеваемости студентов (вариант 3 задания 7) вывести строки, удовлетворяющие критерию: оценка по политэкономии больше минимальной оценки по политэкономии, полученной студентами данной группы.
Вариант 2. По данным таблицы успеваемости студентов (вариант 3 задания 7) вывести строки, удовлетворяющие критерию: оценка по политэкономии больше минимальной и меньше максимальной оценки по политэкономии, полученной студентами данной группы.(Указание: использовать правило реализации критериев, объединяемых операцией логического умножения).
Вариант 3. По данным таблицы успеваемости студентов (вариант 3 задания 7) вывести строки, удовлетворяющие критерию: оценка по политэкономии равна минимальной или максимальной оценке по политэкономии, полученной студентами данной группы.(Указание: использовать правило реализации критериев, объединяемых операцией логического сложения).
Вариант 4. Заполнить 15 - 20 строк таблицы по следующей форме:
| Фамилия И.О, | Дата рождения | Пол | Зарплата | Возраст |
| Ухабин Ю.В, | 15.03.51 | м | ||
| Асюшкин И.К. | 19.10.68 | м | ||
| ... |
Отфильтровать строки, соответствующие работникам, у которых зарплата больше средней.
Вариант 5. Отфильтровать строки, соответствующие мужчинам, у которых зарплата больше средней по всему списку работников (использовать таблицу из 4-го варианта данного задания).
10. Функции баз данных*
Изученные при выполнении заданий 7 и 8 методы использования фильтрации при работе с таблицами обеспечивают выполнение ряда функций, свойственных системам управления базами данных (СУБД). Поэтому группа функций, обрабатывающих отфильтрованные данные, названа авторами EXCEL “Функциями баз данных”.
Каждая из этих функций аналогична обычной статистической функции. Различие состоит в том, что функции баз данных обрабатывают только те ячейки интервала, которые удовлетворяют заданным критериям. Основные статистические функции (как обычные, так и баз данных) перечислены в следующей таблице:
| Статистическая функция | Статистическая функция баз данных | Выполняемое действие |
| СУММ | БДСУММ | Суммирует аргументы |
| СРЗНАЧ | ДСРЗНАЧ | Вычисляет среднее значение |
| СЧЕТЗ | БСЧЕТА | Подсчитывает число непустых ячеек в выборке из заданной базы данных |
| СЧЕТ | БСЧЕТ | Подсчитывает количество чисел в списке аргументов |
| МИН | ДМИН | Возвращает минимальное значение из списка аргументов |
| МАКС | ДМАКС | Возвращает максимальное значение из списка аргументов |
| СТАНДОТКЛ | ДСТАНДОТКЛ | Расчет стандартного отклонения |
Функции баз данных вводятся в следующем формате:
<имя функции> (интервал исходной таблицы; имя столбца; интервал критериев).
В качестве примера рассмотрим следующую таблицу, расположенную в интервале А7:А14 (исходные данные - в столбцах A, B, C, D, зарплата в рублях, возраст - в годах; необходимо, чтобы заголовки столбцов умещались в одну строку). Ставится задача: определить среднюю зарплату работников не моложе 40 лет.
Прежде всего, необходимо рассчитать возраст работников в годах на текущий момент времени. Для этого используем функцию ДОЛЯГОДА (см. раздел 6).
| А | B | C | D | E | |
| Средняя зарплата работников не моложе 40 лет | 382966.67 р. | ||||
| Возраст | |||||
| >=40 | |||||
| Фамилия И.О. | Дата рождения | Пол | Зарплата | Возраст | |
| Ухабин Ю.В, | 15.03.51 | м | 46.405 | ||
| Асюшкин И.К. | 19.10.68 | м | 28.795 | ||
| Мандрыгина А.П. | 24.04.53 | ж | 44.293 | ||
| Бабаев О.П. | 07.10.58 | м | 38.836 | ||
| Атаева В.В. | 19.07.43 | ж | 54.066 | ||
| Мандрыгин П.Л. | 17.08.60 | м | 36.973 | ||
| Федоров В.К. | 20.10.67 | м | 29.795 |
В ячейку E8 введена команда ДОЛЯГОДА(B8; СЕГОДНЯ(); 1), которая вычисляет разность между текущей датой и датой рождения в годах, в ячейку E4 - команда ДСРЗНАЧ(A7:E14; D7, A5:A6), формат ячейки Е4 установим с помощью команды Формат, Формат ячейки, Категория “Денежный”, Код формата “# ##00.00р.”. A7:E14 - интервал исходной таблицы, D7 - имя столбца, по данным которого вычисляется функция базы данных, A5:A6 - интервал критериев. Результат - в ячейке Е4.
Рассмотренную задачу можно было бы решить без привлечения функции баз данных и усиленного фильтра. Для этого выполним такие команды: выделить таблицу (интервал A7:E14), Данные, Фильтр, Автофильтр, Условие, задаем условие: возраст >40 лет. В результате получаем отфильтрованную по данному критерию таблицу. Выделяем интервал отфильтрованной таблицы, команды Данные, Итоги, выделяем имя столбца, по которому хотим рассчитать среднее (в данном случае - Зарплата), затем с помощью полосы прокрутки задаем необходимую функцию - Среднее. Результат получаем в ячейке под столбцом Зарплата. Как видим, применение функций баз данных облегчает решение задач такого типа. В то же время данный пример показывает, что одну и ту же задачу можно решать различными способами.
