ВУЗ: Не указан
Категория: Не указан
Дисциплина: Не указана
Добавлен: 01.06.2024
Просмотров: 63
Скачиваний: 0
15
Рис. 12. Критерии для реализации сложного запроса
Рис. 13. Выборка, соответствующая критериям рис. 12
ствует еще специальный класс функций, предназначенных для анализа списков.
3.5. Использование текстовых функций при формировании вычисляемых критериев
Текстовые функции дают возможность выполнять самые разнообразные преобразования текстовых данных. К наиболее часто используемым из них относятся следующие:
1.ЗНАЧЕН(текст) − преобразует числовые данные, введенные на рабочем листе в текстовом формате (т.е. заключенные в кавычки), в числовые значения.
2.ТЕКСТ(значение; формат) − преобразует число в текст по заданному формату.
3.ДЛСТР(текст) − возвращает длину строки (рис. 12) − количество символов в параметре текст, включая пробелы между словами.
4.ПРАВСИМВ(текст; колич_симв) − извлекает заданное количество колич_симв последних (самых правых) символов из текстовой строки текст.
16
5.ЛЕВСИМВ(текст; колич_симв) − извлекает заданное количество колич_симв первых (самых левых) символов (рис. 12) из текстовой строки текст.
6.ПСТР(текст; нач_позиция; колич_симв) − извлекает из исход-
ной строки текст, начиная с указанной позиции нач_позиция, подстроку заданной длины колич_симв.
Пусть, например, необходимо выбрать студентов, у которых в студенческом билете среди трех последних имеются хотя бы две подряд идущие одинаковые цифры. Интервал критериев для реализации данного запроса представлен на рис. 14. Выборка, полученная в результате фильтрации по критериям рис. 14, приведена на рис. 15.
Рис. 14. Критерии для реализации запроса
Рис. 15. Выборка, соответствующая критериям рис. 14
7.СЖПРОБЕЛЫ(текст) − удаляет из текста все начальные и хвостовые пробелы, из внутренних же удаляются все пробелы, за исключением одиночных.
8.НАЙТИ(иск_текст; просм_текст; нач_позиция)
и ПОИСК(иск_текст; просм_текст; нач_позиция) − осущест-
вляют поиск внутри строки некоторого образца, т.е. заданной подстроки, и возвращают порядковый номер символа строки, с которого начинается найденный образец. При подсчете всегда учитываются все символы, включая пробелы и знаки препинания. Обе функ-
17
ции выполняют сходные действия, различие заключается в том, что |
|||
функция НАЙТИ() учитывает регистр, а |
|
|
|
|
|
||
функция ПОИСК() допускает символы |
|
|
|
шаблона. |
|
|
|
Рис. 16. Критерий для |
|||
|
|||
Пусть, например, нам необходим список сту- реализации запроса |
|||
дентов, у которых в фамилии имеется хотя бы одна |
|
|
|
буква «С» независимо от регистра (маленькая или большая). Критерий для реализации данного запроса представлен на рис. 16. Результаты фильтрации показаны на рис. 17.
9.ЗАМЕНИТЬ(старый_текст; нач_позиция; колич_симв; но-
вый_текст) − заменяет заданный фрагмент текста ста-
рый_текст другой строкой новый_текст.
10.СЦЕПИТЬ(текст1; текст2; …) − соединяет несколько строк
Рис. 17. Выборка, соответствующая критерию рис. 16
в одну (конкатенация).
3.6. Использование функций даты и времени при формировании вычисляемых критериев
Рассмотрим основные функции, предназначенные для работы с датами
ивременем:
1.ДАТА(год; месяц; день) − возвращает дату, заданную параметрами, в числовом формате.
2.СЕГОДНЯ() − возвращает числовое значение текущей даты. В нашем списке эта функция используется при вычислении возраста студентов (табл. 1).
3.ДЕНЬНЕД(дата; тип) − вычисляет порядковый номер дня недели
18 (от 1 до 7), соответствующего заданной дате. Необязательный вто-
рой аргумент тип дает возможность выбрать желаемый порядок нумерации дней недели. Если этот аргумент равен 1 или отсутствует, то первым днем недели счита-
|
|
ется воскресенье, а последним − |
|
|
|
суббота. Если тип равен 2, то |
|
|
Рис. 18. Содержимое |
первым днем недели считается |
|
|
интервала критериев |
понедельник, а последним − вос- |
|
|
|
||
|
|
кресенье. |
|
|
|
|
|
|
|
|
|
Рис. 21. Выборка, соответствующая критериям рис. 20
Пусть, например, необходимо найти студентов, родившихся в пятницу или 13-го числа. Критерии для поиска таких студентов представлены на рис. 18. Выборка, полученная в результате фильтрации по критериям рис. 18, представлена на рис. 19.
Рис. 19. Выборка, соответствующая критериям рис. 18
4.ГОД(дата) − возвращает значение года (от 1900 до 9999) для данной даты (рис. 10).
5.МЕСЯЦ(дата) − возвращает номер месяца (от 1 до 12) для данной даты (рис. 10).
6.ДЕНЬ(дата) − возвращает номер дня в месяце (от 1 до 31) для данной даты.
19
Рис. 20. Интервал критериев для реализации запроса
7. ДАТАЗНАЧ(дата_как_текст) − преобразует в числовой формат дату, заданную в текстовом формате.
Пусть, например, необходимо выдать список студентов, родившихся под знаком зодиака «Водолей» (с 21.01 по 19.02). Интервал критериев для реализации этого запроса приведен на рис. 20, а полученная выборка − на рис. 21.
8.ДНЕЙ360(нач_дата; кон_дата; метод) − вычисляет количество дней между двумя датами на основе 360-дневного года (12 месяцев по 30 дней).
3.7.Функции баз данных
ВMicrosoft Excel имеется 14 функций, предназначенных для обработки списков. Каждая из них возвращает информацию об элементах некоторого интервала, удовлетворяющих одному или нескольким критериям.
1.СЧЁТЕСЛИ(интервал; критерий) − возвращает количество яче-
ек в интервале, которые удовлетворяют критерию.
Например, подсчитать число студенток в списке можно по формуле
=СЧЁТЕСЛИ(F2:F26; “Ж”).
2.СУММЕСЛИ(интервал; критерий; интервал_суммирования)
− возвращает сумму значений в ячейках из интервала суммирова-
ния, отфильтрованных в соответствии с критерием, применяемым к интервалу.
Каждая из оставшихся функций аналогична «обычной» статистической функции. Различие сводится к тому, что функции баз данных обрабатывают
20
только те ячейки интервала, которые удовлетворяют заданным критериям. При этом их синтаксис одинаков: БДФУНКЦИЯ(база_данных; поле; критерий). Необходимо обратить внимание на правила обращения к функциям баз данных:
1)первый аргумент задает весь список, а не отдельный столбец;
2)второй аргумент задает столбец, элементы которого необходимо просуммировать, усреднить и т.п.;
3)вторым аргументом может быть заголовок столбца в виде текстовой константы (имя поля) или порядковый номер поля в списке;
4)третий аргумент задает интервал критериев аналогично интервалу критериев расширенного фильтра.
Вот некоторые из этих функций:
1.БДСУММ(база_данных; поле; критерий) − суммирует значения полей записей базы данных, удовлетворяющих критерию.
2.ДСРЗНАЧ(база_данных; поле; критерий) − возвращает среднее
значение выбранных фрагментов базы данных.
Пусть необходимо подсчитать средний возраст студентов, у которых первые две цифры студенческого билета совпадают с последними двумя. Интервал критериев и формула для решения этой задачи представлены на рис. 22, а результат вычислений − на рис. 23.
Рис. 22. Пример использования функции баз данных
3.БДПРОИЗВЕД(база_данных; поле; критерий) − перемножает значения определенных полей записей базы данных, удовлетворяющих критерию.
4.БСЧЕТ(база_данных; поле; критерий) − подсчитывает количе-
ство числовых ячеек в полях записей базы данных, отвечающих заданному критерию.
Рис. 23. Результат вычислений по формуле рис. 22
21
5.ДМАКС(база_данных; поле; критерий) − возвращает макси-
мальное значение поля среди выделенных записей базы данных.
6.ДМИН(база_данных; поле; критерий) − возвращает минималь-
ное значение поля среди выделенных фрагментов базы данных. Пусть, например, необходимо вычислить максимальный возраст сту-
дентов заочного факультета и минимальный возраст студентов дневной формы обучения. Формулы для реализации этой задачи представлены на рис. 24, а полученный результат − на рис. 25.
Рис. 24. Пример использования функций баз данных
Рис. 25. Результаты вычислений по формулам рис. 24
3.8. Промежуточные итоги
Microsoft Excel позволяет автоматически вычислять промежуточные и общие итоги в списке. При вставке автоматических промежуточных итогов Excel изменяет разметку списка, что позволяет отображать и скрывать строки каждого промежуточного итога.
Перед вставкой промежуточных итогов с целью группировки строк, для которых планируется подвести итоги, список необходимо отсортировать (см. п. 3.3) по соответствующему полю.
Для вставки итогов выбирается команда меню Данные/Итоги. В появившемся диалоговом окне (рис. 26) необходимо выбрать:
– в поле При каждом изменении в – столбец, для одинаковых значений которого подсчитываются итоги, при каждом изменении значения в данном