Пример 3. Выбрать из таблицы 3, созданной на листе "Список", записи со студентами группы ЭИУ1, у которых Вид оплаты =1 (обучение за счет бюджета). Результат поместить в отдельный выходной блок, разместив его ниже исходной таблицы.

Технология фильтрации табличных данных с помощью Расширенного фильтра.

1. На листе "Список" сформировать диапазон условий (A17:B18), отделив его от исходного диапазона (A3:D15), хотя бы одной пустой строкой. Чтобы создать диапазон условий необходимо скопировать заголовки полей исходного диапазона (ячейку А3 в А17 и D3 в В17), которые будут ключевыми при отборе записей (для нашего случая - это [Группа] и [Вид оплаты]), и заполнить строки критериев:

в ячейку A18 ввести критерий *1;

в ячейку B18 ввести 1.

2. Сформировать выходной диапазон, скопировав заголовки полей исходной таблицы в диапазон D17:G17, отделив его пустой строкой или столбцом от диапазона исходной таблицы и диапазона условий (смотри рисунок 37).

3. Установить курсор в любую ячейку исходного диапазона.

4. Выберите команду Дополнительно в группе Сортировка и фильтр. В диалоговом окне Расширенный фильтр проделайте следующие действия:

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

· установите курсор в поле Исходный диапазон и введите ссылку на диапазон исходной таблицы, включая шапку (A3:D15). Ввод ссылок во всех полях данного диалогового окна лучше всего осуществлять путем выделения указателем мыши нужного диапазона;

· установите курсор в поле Диапазон условий и введите ссылку на диапазон условий (A17:В18);

так как выбран переключатель Скопировать результат в другое место, перейдите к полю Поместить результат в диапазон и введите ссылку на выходной диапазон (D18:G27)/ Рекомендуется, выделяя выходной диапазон, захватить достаточное количество пустых строк для размещения в них отобранных данных;

· установите флажок Только уникальные записи, если не хотите, чтобы одинаковые записи повторялись (будет выводиться только первая из всех, удовлетворяющих критерию, одинаковых записей); нажмите кнопку [ОК].

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

Рисунок 37 – Работа с расширенным фильтром на примере данных таблицы 3

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

При необходимости выбора из таблицы 3 или всех студентов группы ЭИУ1, или тех студентов, у которых Вид оплаты =1 (независимо от группы), диапазон условий будет иметь вид изображенный на рисунке 38.

Рисунок 38 – Изображение диапазона условий

Использование формы данных

Пример 4. Найти в таблице, представленной на рисунке 36, сформированной на листе Список, тех студентов, у которых фамилия начинается с буквы "В".

Технология поиска данных с помощью формы

1. Выделить таблицу вместе с шапкой (A3:D15).

2. Выполнить команду Форма.

Для того чтобы открыть окно формы необходимо добавить кнопку Форма на панель быстрого доступа . Для этого:

Щелкните стрелку на панели быстрого доступа и выберите элемент Другие команды.

В поле Выбрать команды из щелкните элемент Все команды.

В списке выберите кнопку Форма и щелкните элемент Добавить.

Откроется форма данных.

3. Нажать кнопку [Критерии].

4. В окне Форма произойдет очистка полей и замена названий некоторых кнопок в форме.

5. В поле Фамилия, которое будет участвовать в определении критерия, ввести критерий: В*.

6. Нажать кнопку [Далее] или [Назад], чтобы перейти к записи, удовлетворяющей введенному критерию.

7. Нажать кнопку [Закрыть].

Пример 5. Изменить в таблице “Список студентов ЭИУ 1-го курса”, представленной на листе “Список”, фамилию студентки Володина на новую - Пугачева, полученную после замужества.

Технология редактирования данных с помощью формы

1. Выделить таблицу вместе с шапкой (A3:D15).

2. Выполнить команду [Форма]. Откроется форма данных.

3. Нажать кнопку [Критерии].

4. Произойдет очистка полей и замена некоторых кнопок в форме.

5. Перейти к полю Фамилия, которое будет участвовать в определении критерия и ввести критерий: Володина А.Р.

6. Нажать кнопку [Далее] или [Назад], чтобы перейти к записи, удовлетворяющей введенному критерию.

7. Ввести новую фамилию и инициалы: Пугачева А.Р.

8. Нажать кнопку [Закрыть].

Подведение итогов

Пример 6. Рассчитать средние баллы по всем дисциплинам, каждой из учебных групп (на примере таблицы, представленной на рисунке 32).

Технология подведения частных и общих итогов

1. Выделить диапазон таблицы, включая шапку (A3:F15).

2. Выполнить команду Промежуточные итоги на вкладке [Данные] в группе [Структура].

В поле [При каждом изменении в] из раскрывающегося списка выбрать [Группа];

в поле [Операция] из раскрывающегося списка выбрать [Среднее];

в поле [Добавить итоги по] установить флажки: Математика, Информатика, Философия;

установить флажок [Итоги под данными].

3. Нажать кнопку [ОК].

4. Округлить полученные итоги до двух десятичных знаков с помощью команды [Формат ячеек].

Результат работы команды [Итоги] представлен на рисунке 39.

Рисунок 39 – Результаты выполнения команды Промежуточные итоги

В результате подсчитаны средние баллы по группам и общий средний балл. Если с экрана убрать детали, нажав кнопку с изображением цифры "2" (второй уровень итогов), расположенную левее нумерации строк, то на экране останутся только полученные итоги (смотри рисунок 40).

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

Рисунок 40 – Результат показа второго уровня итогов.

ЛАБОРАТОРНАЯ РАБОТА №4

Содержание работы: построение связанных таблиц - задание формул со ссылками на другие рабочие листы и другие книги, сохранение и открытие связанных книг.

Пример 1. Сформировать в Excel таблицу для расчета сумм стипендий студентам факультета ЭИУ в соответствии с формой, представленной в таблице 5. При этом учесть следующие условия:

· стипендия не начисляется студентам, обучающимся на платной основе;

· студенты получают минимальную стипендию, если средний экзаменационный балл больше 3;

· студенты, имеющие средний балл более 4.5, получают 50 % надбавки к стипендии.

Таблица 5

Ведомость начисления стипендии студентам ЭИУ 1-го курса