Файл: А.Г. Пимонов Информационные системы в экономике (Часть I).pdf

18

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

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

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

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

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

в одну (конкатенация).

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

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

ивременем:

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

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

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

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

Пусть, например, необходимо найти студентов, родившихся в пятницу или 13-го числа. Критерии для поиска таких студентов представлены на рис.18. Выборка, полученная в результате фильтрации по критериям рис.18, представлена на рис. 19.

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

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

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

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

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

20

необходимо выдать список студентов, родившихся под знаком зодиака «Водолей» (с 21.01 по 19.02). Интервал критериев для реализации этого запроса приведен на рис. 20, а полученная выборка − на рис. 21.

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

3.7.Функции баз данных

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

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

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

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

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

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

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

интервалу.

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

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

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

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

21 4) третий аргумент задает интервал критериев аналогично интервалу критериев расширенного фильтра.

Вот некоторые из этих функций:

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

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

значение выбранных фрагментов базы данных.

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

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

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

Рис. 25. Результаты вычислений по формулам рис. 24

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

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

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

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

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

мальное значение поля среди выделенных записей базы данных.

22 6. ДМИН(база_данных; поле; критерий) − возвращает минималь-

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

дентов заочного факультета и минимальный возраст студентов дневной формы обучения. Формулы для реализации этой задачи представлены на рис. 24, а полученный результат − на рис. 25.

3.8. Промежуточные итоги

Microsoft Excel позволяет автоматически вычислять промежуточные и общие итоги в списке. При вставке автоматических промежуточных итогов

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

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

23

–в поле Добавить итоги по – столбцы, содержащие значения, по которым необходимо подвести итоги;

–чтобы за каждым итогом следовал разрыв страницы, установить флажок Конец страницы между группами;

–чтобы итоги отображались над строками данных, а не под ними,

снять флажок Итоги под данными;

–нажать кнопку

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

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

Для удаления итогов необходимо:

–установив курсор внутрь списка, содержащего итоги, выбрать ко-

манду меню Данные/Итоги;

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

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

1)отсортировать список по полю ГРУППА;

2)выбрать команду меню Данные/Итоги. В результате на экране появится окно диалога (рис. 26). В раскрывающихся списках выбрать:

–При каждом изменении в – поле ГРУППА,

–Операция – Количество,

–Добавить итоги по – поле ГРУППА;

24

3)нажать кнопку .

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

Чтобы рассчитать средний возраст студентов по группам, необходимо дополнить полученную таблицу новыми значениями итогов. Для этого необходимо еще раз запустить инструмент Итоги выбором команды меню Данные/Итоги. В диалоговом окне установить:

–При каждом изменении в – поле ГРУППА;

–Операция – Среднее;

Рис. 27. Фрагмент рабочего листа с итоговым количеством студентов по группам

–Добавить итоги по – поле ВОЗРАСТ;

–снять флажок Заменить текущие итоги;