Файл: Ю.В. Клещ Электронная таблица Exel как простейшая реляционная база данных.pdf

10

2.если условия располагаются в одной строке, то это означает одновременность их выполнения, т.е. считается, что между ними поставлена логическая операция И;

3.для истинности критерия, состоящего из условий, располагающихся в разных строках, требуется выполнение хотя бы одного из них, т.е. считается, что они соединены логической операцией ИЛИ;

4.интервал критериев должен располагаться выше или ниже списка, либо на другом рабочем листе;

5.в интервале критериев не должно быть пустых строк.

При формировании текстовых критериев необходимо помнить о том,

что:

1.если в ячейке содержится только один символ (рис. 10 – колонка A), то такому условию удовлетворяют любые тексты, начинающиеся с этого символа;

Рис. 10. Содержимое интервала критериев рис. 9

2.если содержимое ячейки представляет собой текстовую константу вида ”>БУКВА” или ”<БУКВА”, то такому условию соответствует любой текст, начинающийся с этой и последующих БУКВ, или начинающийся с предшествующих ей БУКВ;

3.для поиска текста на полное совпадение содержимое ячейки с критерием должно иметь вид =”=ТЕКСТ”;

4.в текстовых критериях можно использовать символы шаблона. Вычисляемый критерий представляет собой формулу (рис. 10), в кото-

рой обязательно имеется ссылка (для реализации каких–либо вычислений) на соответствующую ячейку первой строки списка. Так как эта формула является логическим выражением, то в ячейке, ее содержащей, отображается результат вычисления (ИСТИНА либо ЛОЖЬ) для первой записи списка (рис. 9). А в результате процесса фильтрации в списке будут скрыты те записи, для которых при вычислении формулы получается значение ЛОЖЬ.

11

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

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

2.в самом условии ссылки на ячейки внутри списка должны быть записаны в относительной форме;

3.ссылки на ячейки вне списка должны быть абсолютными.

Пусть, например, перед нами стоит следующая задача. Необходимо выдать список студентов мужского пола, родившихся летом, или же студенток, год рождения которых является високосным. Содержимое интервала критериев, созданного для реализации этого запроса, приведено на рис. 10. Верхняя строка интервала содержит заголовки критериев, первый из которых совпадает с заголовком поля, а оставшиеся три уникальны. В ячейки B3, C3, D4 записаны формулы (вычисляемые критерии).

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

Рис. 11. Выборка, соответствующая критериям рис. 10

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

1.названные в честь отца;

2.самые младшие по возрасту;

3.самые старшие.

12

Интервал критериев для реализации этого запроса приведен на рис. 12, а полученная выборка – на рис. 13.

Рис. 12. Критерии для реализации сложного запроса

Рис. 13. Выборка, соответствующая критериям рис. 12

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

2.4. Использование текстовых функций при формировании вычисляемых критериев

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

1.ЗНАЧЕН(текст) − преобразует числовые данные, введенные на рабочем листе в текстовом формате (т.е. заключенные в кавычки) в числовые значения.

2.ТЕКСТ(значение; формат) − преобразует число в текст по заданному формату.

3.ДЛСТР(текст) − возвращает длину строки (рис. 12) − количество символов в параметре текст, включая пробелы между словами.

14

поиск внутри строки некоторого образца, т.е. заданной подстроки и возвращают порядковый номер символа строки, с которого начинается найденный образец. При подсчете всегда учитываются все символы, включая пробелы и знаки препинания. Обе функции выполняют сходные действия, различие заключается в том, что функция НАЙТИ() учитывает регистр, а функция ПОИСК() допускает символы шаблона.

Пусть, например, нам необходим список студентов, у которых в фамилии имеется хотя бы одна буква «С», независимо от регистра (маленькая или большая). Критерий для реализации данного запро-

са представлен на рис. 16. Результаты фильтрации показаны на рис. 17.

Рис. 17. Выборка, соответствующая критерию 16

9.ЗАМЕНИТЬ(старый_текст; нач_позиция; колич_симв; но-

вый_текст) − заменяет заданный фрагмент текста старый_текст другой строкой новый_текст.

10.СЦЕПИТЬ(текст1; текст2; …) − соединяет несколько строк в одну (конкатенация).

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

Рассмотрим основные функции, предназначенные для работы с датами

ивременем:

1.ДАТА(год; месяц; день) − возвращает дату, заданную параметрами, в числовом формате.

15

2.СЕГОДНЯ() − возвращает числовое значение текущей даты. В нашем списке эта функция используется при вычислении возраста студентов (таблица 1).

3.ДЕНЬНЕД(дата; тип) − вычисляет порядковый номер дня недели (от 1 до 7), соответствующего заданной дате. Необязательный второй аргумент тип дает возможность выбрать желаемый порядок нумерации дней недели. Если этот аргумент равен 1 или отсутствует, то первым днем недели считается воскресенье, а последним − суббота. Если тип равен 2, то первым днем недели считается поне-

ная в результате фильтрации по критериям рис.18, представлена на рис. 19.

Рис. 19. Выборка, соответствующая критериям рис. 18

4.ГОД(дата) − возвращает значение года (от 1900 до 9999) для данной даты (рис. 10).

5.МЕСЯЦ(дата) − возвращает номер месяца (от 1 до 12) для данной даты (рис. 10).

6.ДЕНЬ(дата) − возвращает номер дня в месяце (от 1 до 31) для данной даты.

7.ДАТАЗНАЧ(дата_как_текст) − преобразует в числовой формат дату, заданную в текстовом формате.

на рис. 20, а полученная выборка − на рис. 21.

Рис. 21. Выборка, соответствующая критериям рис 20

8.ДНЕЙ360(нач_дата; кон_дата; метод) − вычисляет количество дней между двумя датами на основе 360-дневного года (12 месяцев по 30 дней).

2.6.Функции для анализа списков

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

1.СЧЁТЕСЛИ(интервал; критерий) − возвращает количество ячеек в

интервале, которые удовлетворяют критерию.

Например, подсчитать число студенток в списке можно по формуле

=СЧЁТЕСЛИ(F2:F26; “Ж”).

2. СУММЕСЛИ(интервал; критерий; интервал_суммирования) − воз-

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

Каждая из оставшихся функций аналогична «обычной» статистической функции. Различие сводится к тому, что функции баз данных обрабатывают только те ячейки интервала, которые удовлетворяют заданным критериям. При этом их синтаксис одинаков: БДФУНКЦИЯ(база_данных; поле; крите-

17

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

1)первый аргумент задает весь список, а не отдельный столбец;

2)второй аргумент задает столбец, элементы которого необходимо просуммировать, усреднить и т.п.;

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

4)третий аргумент задает интервал критериев аналогично интервалу

критериев расширенного фильтра. Вот некоторые из этих функций:

1.БДСУММ(база_данных; поле; критерий) − суммирует значения полей записей базы данных, удовлетворяющих критерию.

2.ДСРЗНАЧ(база_данных; поле; критерий) − возвращает среднее значение выбранных фрагментов базы данных.

Рис. 22. Пример использования функции для анализа списка

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

риев и формула для решения этой задачи представлены на рис. 22, а результат вычислений− на рис. 23.

3.БДПРОИЗВЕД(база_данных; поле; критерий) − перемножает значения определенных полей записей базы данных, удовлетворяющих критерию.

4.БСЧЕТ(база_данных; поле; критерий) − подсчитывает количество числовых ячеек в полях записей базы данных, отвечающих заданному критерию.

5.ДМАКС(база_данных; поле; критерий) − возвращает максимальное значение поля среди выделенных записей базы данных.