Excel__базы_ данных_
.pdf
Рис. 30
Использование функций даты и времени при формировании вычисляемых критериев
Рассмотрим основные функции, предназначенные для работы с датами и временем:
ДАТА(год; месяц; день) – возвращает дату, заданную параметрами, в числовом формате.
СЕГОДНЯ() – возвращает числовое значение текущей даты. В нашем списке эта функция используется при вычислении возраста студентов (табл. 1).
ДЕНЬНЕД(дата; тип) – вычисляет порядковый номер дня недели (от 1 до 7), соответствующего заданной дате. Необязательный второй аргумент тип дает возможность выбрать желаемый порядок нумерации дней недели. Если этот аргумент равен 1 или отсутствует, то первым днем недели считается воскресенье, а последним - суббота. Если тип равен 2, то первым днем недели считается понедельник, а последним - воскресенье.
ГОД(дата) – возвращает значение года (от 1900 до 9999) для данной
даты.
МЕСЯЦ(дата) – возвращает номер месяца (от 1 до 12) для данной даты. ДЕНЬ(дата) – возвращает номер дня в месяце (от 1 до 31) для данной
даты.
ДАТАЗНАЧ(дата_как_текст) – преобразует в числовой формат дату, заданную в текстовом формате.
ДНЕЙ360(нач_дата; кон_дата; метод) – вычисляет количество дней между двумя датами на основе 360-дневного года (12 месяцев по 30 дней).
31
Пусть, например, необходимо выдать список студентов, родившихся летом. Интервал критериев для реализации этого запроса приведен на рис. 31, а полученная выборка - на рис. 32.
Рис. 31
Рис. 32
Функции для анализа списков
В Microsoft Excel имеется 14 функций, предназначенных для обработки списков. Каждая из них возвращает информацию об элементах некоторого интервала, удовлетворяющих одному или нескольким критериям.
СЧЁТЕСЛИ(интервал; критерий) – возвращает количество ячеек в интервале, которые удовлетворяют критерию.
Например, подсчитать число студенток в списке можно по формуле
=СЧЁТЕСЛИ(F2:F26; “Ж”).
СУММЕСЛИ(интервал; критерий; интервал_суммирования) - возвращает сумму значений в ячейках из интервала суммирования, отфильтрованных в соответствии с критерием, применяемым к интервалу.
Каждая из оставшихся функций аналогична «обычной» статистической функции. Различие сводится к тому, что функции баз данных обрабатывают только те ячейки интервала, которые удовлетворяют заданным критериям. При этом их синтаксис одинаков: БДФУНКЦИЯ(база_данных; поле;
32
критерий). Необходимо обратить внимание на правила обращения к функциям баз данных:
первый аргумент задает весь список, а не отдельный столбец;
второй аргумент задает столбец, элементы которого необходимо просуммировать, усреднить и т.п.;
вторым аргументом может быть заголовок столбца в виде текстовой константы (имя поля) или порядковый номер поля в списке;
третий аргумент задает интервал критериев аналогично интервалу критериев расширенного фильтра.
Вот некоторые из этих функций:
БДСУММ(база_данных; поле; критерий) - суммирует значения полей записей базы данных, удовлетворяющих критерию.
ДСРЗНАЧ(база_данных; поле; критерий) - возвращает среднее значение выбранных фрагментов базы данных.
БДПРОИЗВЕД(база_данных; поле; критерий) - перемножает значения определенных полей записей базы данных, удовлетворяющих критерию.
БСЧЕТ(база_данных; поле; критерий) - подсчитывает количество числовых ячеек в полях записей базы данных, отвечающих заданному критерию.
ДМАКС(база_данных; поле; критерий) - возвращает максимальное значение поля среди выделенных записей базы данных.
ДМИН(база_данных; поле; критерий) - возвращает минимальное значение поля среди выделенных фрагментов базы данных.
Пусть необходимо подсчитать средний возраст студентов, обучающихся на специальности «Менеджмент». Интервал критериев и формула для решения этой задачи, расположенные на Лист4 рабочей книги, представлены на рис. 33, а результат вычислений – на рис. 34.
33
Рис. 33
Рис. 34
6. Поиск данных
Процедурой фильтрации с помощью формы можно производить поиск данных по заданным полям (столбцам). Однако если пользователя интересует поиск по всему рабочему листу какого-либо конкретного значения или формулы, то лучше воспользоваться процедурой поиска из меню Правка. Для этого необходимо выполнить следующие действия.
1.Выберите команду Правка → Найти.
2.В открывшемся диалоговом окне Найти и заменить (рис. 35) на вкладке Найти в поле Найти наберите фрагмент содержимого ячейки (например, Марк).
Рис. 35. Диалоговое окно Найти и Заменить.
34
3.Щелкните по кнопке Найти далее. Если поиск оказался успешным, активизирована будет ячейка, содержащая указанный фрагмент
(рис. 36).
4.Закройте диалоговое окно поиска щелчком по кнопке Закрыть.
Рис. 36. Результат поиска.
По умолчанию поиск ведется по строкам. Вы можете изменить схему поиска, задав в диалоге Найти поиск по столбцам. В этом же диалоге можно уточнить, какого рода данные вы ищете: значения, формулы или примечания, щелкнув на кнопке Параметры и в открывшемся диалоговом окне указав необходимые параметры (рис. 37).
Рис. 37. Диалоговое окно для уточнения параметров поиска.
35
В этом диалоговом окне Найти и Заменить возможен поиск с заменой: фрагмент, указанный в поле Найти, вы можете заменить на другой. Для этого необходимо перейти на вкладку Заменить (рис. 38). В новом диалоговом окне укажите в поле Заменить на другой фрагмент, после чего щелкните по кнопке Заменить или Заменить все (глобальная замена). Описанное средство замены удобно тем, что замене подлежит не все содержимое ячейки, а фрагмент.
Рис. 38. Вкладка Заменить диалогового окна Найти и заменить.
Чтобы заменить все содержимое ячейки, следует нажать на кнопку Параметры и в появившемся окне установить флажок Ячейка целиком (рис. 39).
Рис. 39. Окно для уточнения параметров замены.
36
ВОПРОСЫ ДЛЯ САМОКОНТРОЛЯ
1.Что представляет собой список, форма?
2.Правила, которых следует придерживаться при создании списка.
3.Что является основой для создания базы данных в Excel.
4.Поле, запись – их аналоги в Excel.
5.Ввод и редактирование записей в базе данных.
6.Сортировка записей в базе данных по одному параметру.
7.Сортировка записей в базе данных по нескольким параметрам
8.Использование методов фильтрации данных в базе данных Excel: автофильтр, форма.
9.Использование расширенного фильтра как метода фильтрации данных в списке.
10.Использование функций при создании вычисляемых критериев
11.Поиск информации в базе данных.
ЗАДАНИЕ ДЛЯ САМОСТОЯТЕЛЬНОГО ВЫПОЛНЕНИЯ
1.На Лист1 рабочей книги создать список, содержащий информацию
остудентах, обучающихся на экономическом факультете, структура которого определяется табл. 2. Переименовать Лист1 в «Список студентов». Заполнить 25 записей списка, используя форму.
Каждое последующее задание выполнять на отдельном листе (скопировав на него список), задав ему имя, соответствующее представленному на нем заданию.
2.Отсортируйте список в алфавитном порядке фамилий, имен, отчеств, т.е. задать сортировку сразу по трем параметрам.
3.С помощью пользовательского автофильтра создайте выборку студентов, родившихся в 1995 году.
4.Используя автофильтр и символы шаблона создайте выборку студентов, обучающихся на специальности «Менеджмент».
37
|
|
|
|
|
Таблица 2 |
|
|
|
|
Структура списка студентов |
|
|
|
|
|
|
|
|
№ |
Имя поля |
Тип поля |
Назначение |
Комментарий |
|
п/п |
|
|
|
|
|
|
|
|
|
|
|
1. |
ФАМИЛИЯ |
Текстовое |
Эти поля предназначены для |
Строки состоят из букв кириллицы без ведущих и |
|
2. |
ИМЯ |
Текстовое |
хранения ФИО студента |
хвостовых пробелов, первый символ – прописная |
|
3 |
ОТЧЕСТВО |
Текстовое |
|
буква |
|
4. |
ГРУППА |
Текстовое |
Название группы |
Пятисимвольная строка: первые три символа – |
|
|
|
|
|
специальность («Финансы» – «Фин», |
|
|
|
|
|
«Менеджмент» – «Мен», Маркетинг» – «Мрк», |
38 |
|
|
|
|
«Прикладная статистика» – «Пст»), четвертый – |
|
|
|
|
форма обучения (дневная – «д», заочная – «з»), |
|
|
|
|
|
|
|
|
|
|
|
|
последний символ – курс (1…5). Например, Финд2 |
|
5. |
СТУДЕНЧЕСКИЙ |
Числовое |
Номер студенческого билета |
Шестисимвольная строка: первый символ – номер |
|
|
|
|
|
факультета (для экономического факультета он |
|
|
|
|
|
равен 2), два следующих – год поступления, три |
|
|
|
|
|
последних – собственно номер студента. |
|
|
|
|
|
Например, 207151 |
|
6. |
ПОЛ |
Текстовое |
Пол студента |
Односимвольная строка: буквы «м» или «ж» |
|
7. |
ДАТА РОЖДЕНИЯ |
Дата |
Дата рождения студента |
Например, «23.11.59» |
|
8. |
ВОЗРАСТ |
Вычисляемое |
Возраст в годах |
Вычисляется по формуле: |
|
|
|
|
|
текущий год – год даты рождения) |
5.С помощью расширенного фильтра сформировать выборку, используя вычисляемые критерии, в которую бы входили студенты самые младшие по возрасту.
6.С помощью расширенного фильтра создайте выборту студентов мужского пола, родившихся в 1994 году.
7.С помощью расширенного фильтра создайте выборту студентов специальности «Финансы», родившихся в феврале месяце.
8.С помощью расширенного фильтра выдать список студентов, родившихся под знаком зодиака «Водолей» (с 21.01 по 19.02).
9.С помощью расширенного фильтра создайте выборту студентов обучающихся на специальности «Маркетинг» и поступивших на факультет в
2012 году.
10.С помощью расширенного фильтра вычислить максимальный возраст студентов заочного факультета и минимальный возраст студентов дневной формы обучения.
39