Использование встроенных функций EXCEL

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

· Нажать клавиши <Shift><F3>;

· Задать команду Вставка – Функция;

· Нажать кнопку Вставка функции [f_x] на стандартной панели инструментов.

Математические

СУММ (число1;число2;…) – суммирует все числа в списке аргументов, где число1;число2;… - это от 1 до 30 аргументов, для которых требуется определить итог или сумму.

КОРЕНЬ (число) – извлекает корень из положительного числа.

СТЕПЕНЬ (число, степень) – возводит число в заданную степень.

Статистические

МАКС (число1;число2;…) – возвращает наибольшее значение в списке аргументов. Число1;число2;… - это от 1 до 30 чисел, среди которых ищется максимальное значение.

МИН (число1;число2;…) – возвращает наименьшее значение в списке аргументов. Число1;число2;… - это от 1 до 30 чисел, среди которых ищется минимальное значение.

СРЗНАЧ (число1;число2;…) – возвращает среднее (арифметическое) значение в списке аргументов. Число1;число2;… - это от 1 до 30 чисел, для которых вычисляется среднее значение.

ДИСП (число1;число2;…) – оценивает дисперсию по выборке. Число1;число2;… - это от 1 до 30 числовых аргументов, соответствующих выборке из генеральной совокупности.

Логические

ЕСЛИ (условие;результат_если_ условие истинно; результат_если_ условие ложно) – оценивает логическое условие, и если оно истинно, возвращает первый результат, если ложно – второй.

6. База данных – организованная структура, предназначенная для хранения информации.

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

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

На листе не следует помещать более одного списка. Некоторые функции обработки списков не позволяют работать с несколькими списками одновременно.

В списке не должно быть пустых строк и столбцов. Это упрощает идентификацию и выделение списка.

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

А) Создание списков

Первый, наиболее важный шаг при создании базы данных - это разработка хорошо продуманной структуры (организации хранения данных), которая и определяет возможности будущей обработки информации. Структура списка определяется структурой одинаково организованных записей, его составляющих. Под структурой записи принято понимать совокупность ее полей (их имена, типы, назначение). Пусть, например, мы хотим создать список, содержащий информацию о студентах, обучающихся в Братском Государственном Университете. Структура такого списка может быть достаточно простой (табл. 1)

Таблица 1

Структура списка студентов

№ п/п	Имя поля	Тип поля	Назначение	Комментарий
	Фамилия	Текстовое	Эти поля предназначены для хранения ФИО студента	Строки состоят из букв кириллицы без ведущих и хвостовых пробелов, первый символ – прописная буква
	Имя	Текстовое
	Отчество	Текстовое
	Группа	Текстовое	Название группы	Пятисимвольная строка: первые два символа – специальность, третий – форма обучения, последние два – год поступления
	Студенческий	Числовое	Номер студенческого билета	Шестизначное число: первая цифра – номер филиала, две следующих – год поступления, три последних – собственно номер
	Пол	Текстовое	Пол студента	Односимвольная строка: буквы «м» или «ж»
	Дата рождения	Дата	Дата рождения студента	Например, «23.11.59»
	Возраст	Вычисляемое	Возраст в годах	Вычисляется по формуле: (текущая дата – дата рождения)/365, т.е. =(СЕГОДНЯ()–A7)/365

 

В ячейках списка чаще всего хранятся константы соответствующих основных типов: числовые, текстовые, даты и времени. Кроме того, допустимо использование так называемых вычисляемых полей, содержащих формулы. В нашем примере (рис.1) это возраст. В качестве вычисляемого поля может выступать в зависимости от предметной области, например, стоимость партии товара, время до истечения срока годности продукта, стоимость услуги в у.е. и т.п. При формировании вычисляемых полей необходимо придерживаться двух основных правил ссылки на ячейки внутри списка, а это поля одной и той же записи, должны быть относительными; ссылки на ячейки вне списка должны быть абсолютными.

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

 

Рис. 1 Пример заполненной базы данных

 

Б) Сортировка списков

Под сортировкой списка, как и любого другого набора объектов, принято понимать расположение его записей в определенном порядке. Записи можно располагать в порядке возрастания-убывания числовых полей, в алфавитном (обратном алфавитному) порядке текстовых полей, в хронологическом порядке полей типа дата и время. Поле, по которому производится сортировка, называется ключевым полем, или ключом сортировки. Возможности сортировки реализуются с помощью кнопок Сортировка по возрастанию и Сортировка по убыванию панели инструментов Стандартная, либо через команду меню Данные/Сортировка, которая позволяет отсортировать список за один прием максимум по трем полям (первичный ключ, вторичный и т.д.). В случае необходимости можно произвести сортировку и более чем по трем столбцам. В этой ситуации список сортируется последовательно, начиная с наименее важного поля. Сортировать можно и часть списка, предварительно ее выделив. После сортировки изменяется расположение строк списка, поэтому, если результаты сортировки по каким-либо причинам вас не устраивают, это действие необходимо незамедлительно отменить с помощью кнопки Отменить панели инструментов Стандартная.

 

В) Анализ списков с помощью фильтров

 

В конечном итоге основное назначение любой базы данных - это оперативный поиск необходимой информации по какому-либо запросу. Под запросами принято понимать задачи на поиск информации в базе данных. При этом часть базы данных, удовлетворяющая запросу, называется выборкой. В Excel запросы реализованы с помощью фильтров. Фильтрация списка - это процесс, в результате которого в списке скрываются все строки, не удовлетворяющие критериям фильтрации, а остаются видимыми только те (остается выборка), которые соответствуют условиям запроса. Excel располагает двумя командами фильтрации, которые становятся доступными через пункт меню Данные/Фильтр: Автофильтр и Расширенный фильтр. С помощью автофильтра реализуются простые запросы, содержащие не более двух условий поиска.

Расширенный (усиленный) фильтр позволяет выполнять запросы практически любой сложности. Для установки автофильтра на все поля списка достаточно обратиться к пункту меню Данные/Фильтр/Автофильтр. Можно установить автофильтр и для одного поля. Для этого достаточно его предварительно выделить: активизировав заголовок соответствующего столбца, нажать комбинацию клавиш Shift, Ctrl+, после чего справа от заголовка появится кнопка , щелчок по которой раскрывает список значений данного столбца.

Эти значения можно использовать для фильтрации. Кроме того, можно настроить автофильтр, выбрав из этого списка элемент (Условие…), после чего можно создать критерий (настроить пользовательский автофильтр), состоящий не более чем из двух условий, соединенных знаками операций И, ИЛИ. Каждое из этих условий представляет собой выражение логического типа, содержащее любые операции отношения (<, <=, =, <>, >, >=). Пусть, например, нам необходим список студентов, родившихся в 1971 году. Пользовательский автофильтр для решения этой задачи приведен на рис. 15, а результаты фильтрации - на рис. 16

Рис 15. Критерий для выборки

студентов, родившихся в 1971 году

Рис. 16. Выборка студентов, родившихся в 1971 году.

При создании текстовых критериев можно использовать символы шаблона: "*" - для обозначения последовательности произвольной длины, состоящей из любых символов, и"?" - для обозначения единичного символа, стоящего на определенном месте. Для включения символов шаблона в критерий в качестве обычных символов перед ними надо ставить тильду "~". Пусть, например, нам необходим список студентов, чьи имена начинаются с буквы "А" и заканчиваются буквой "а", или имена состоят из восьми любых букв. Один из возможных вариантов пользовательского автофильтра для решения этой задачи приведен на рис. 17, а результаты фильтрации - на рис. 18

 

Рис. 17. Критерий с использованием

символа шаблона.

Рис.18.Результаты фильтрации

по критерию рис.17.

 

Назначение флажка Только уникальные записи (рис. 19) очевидно. Установка этого флажка при копировании выборки в интервал извлечения позволяет убрать из нее все повторяющиеся записи. При отсутствии диапазона условий с помощью этого флажка можно избавиться от повторяющихся записей в исходном списке. При создании интервала критериев (рис. 20) необходимо помнить о следующих соглашениях:

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

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

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

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

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

При формировании текстовых критериев необходимо помнить о том, что если в ячейке содержится только один символ (рис. 21 - колонка A), то такому условию удовлетворяют любые тексты, начинающиеся с этого символа;

 

Рис.19. Окно диалога Расширенный фильтр

 

Рис.20. Пример интервалов критериев

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

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

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

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

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

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

 

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

 

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

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

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

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

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

 

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

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

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

Г) Сводные таблицы.

 

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

Сводная таблица может быть создана на основании данных находящихся:

в списке или базе данных Microsoft Excel;

во внешнем источнике данных;

в нескольких диапазонах консолидации;

в другой сводной таблице.

 

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

Строка. Уникальные значения полей, помещенных в эту область, используются в качестве заголовков строк в сводной таблице. Если в эту область помещено одно поле, то количество строк в сводной таблице (без учета итогов) равно числу уникальных значений этого поля.

Столбец. Уникальные значения полей, помещенных в эту область, используются в качестве заголовков столбцов в сводной таблице. Если в эту область помещено одно поле, то количество столбцов в сводной таблице (без учета итогов) равно числу уникальных значений этого поля.

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

Страница. Уникальные значения полей, помещенных в эту область, и элемент «все» используются для построения раскрывающихся списков. В поле страницы можно выбрать только одно значение в каждом из списков. В области данных будут отображены итоговые данные, для выбранного значения. Использование этого элемента сводной таблицы позволяет, в некоторой мере, реализовать отображение трехмерной таблицы.