Работа с табличным процессором MS Excel.

Освоение приемов работы с электронными таблицами

Практическое занятие 12.

Использование форм; поиск в таблицах

Цель работы:

1.                 Освоение работы с формами в Excel.

2.                 Изучение способов поиска данных в документах Excel

3.                 Контроль данных ввода в Excel.

1.     Подготовка к работе

Скопируйте в свою папку файл с ведомостью результатов тестирования, составленной на практическом занятии 10.

Откройте рабочую книгу с ведомостью, вставьте в книгу новый лист, назовите его «Форма» и скопируйте на этой лист все данные с листа «Исходные данные 10». Если после первой строки с заголовками столбцов есть строка с разделителями, то удалите ее, между заголовком и данными не должно быть пустых строк. Проверьте, что между таблицей и результатами расчетов под ней есть хотя бы одна пустая строка.

Все строки электронной таблицы на листе «Форма» содержат однородную информацию, такая таблица может рассматриваться как база данных или список. Многие операции Excel применяются только к спискам.

2.     Применение форм для просмотра, редактирования и поиска записей
Команда Данные – Форма позволяет добавлять строки, изменять и удалять строки в списке, ее можно также использовать для отыскания записей, удовлетворяющих заданным условиям.

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

  Просмотрите таблицу используя:

- кнопки навигации «Далее», «Назад»;

- клавиши «Вниз», «Вверх», «PageUp», «PageDown»;

- щелчки по полосе прокрутки.

Измените какую-либо запись в списке, вводя новые значения в поля, для перехода к следующему полю используйте клавишу Tab. Редактирование заканчивается при переходе к следующей записи -  клавишами «Вниз», «Вверх» или Enter. Обратите внимание, что восстановить измененные данные кнопкой «Вернуть» можно только до перехода к следующей записи!

Добавьте несколько новых записей в список.
Щелкните по кнопке «Добавить» или прокрутите список до последней записи, чтобы открыть форму «Новая запись». Введите информацию в соответствующие Поля. После ввода информации в последнее поле нажмите Enter или щелкните «Добавить». Нажмите клавишу «Вверх», чтобы закрыть форму «Новая запись». Обратите внимание на изменение значений в вычисляемых полях.

Удалите новые записи с помощью кнопки «Удалить».

 

Форму можно использовать для поиска и просмотра отдельных записей, удовлетворяющих заданным условиям. Условия задаются с помощью операндов отношения: “<”, ”>”, ”=’,  “<=”, ”>=”. Переход между найденными записями – с помощью кнопок «Далее», «Назад». Если условия задаются по нескольким полям, то условия объединяются операцией И. С помощью формы нельзя отобрать записи, удовлетворяющие хотя бы одному условию ИЛИ! Для этого используется фильтр.

Задание.  Найдите в списке

- запись испытуемого Кузнецов;

- все записи, в которых оценка равна 3;

- все записи в которых оценка равна 4 и сумма баллов больше 45.

3.     Команда «Найти»

Команда «Правка - Найти» используется для поиска на листе заданной информации. Если нужно выполнить поиск по всему рабочему листу, выделите одну ячейку, поиск начнется с нее. Для поиска только в определенной части листа выделите соответствующий диапазон.

Для выполнения команды необходимо  задать:

- искомое значение в поле «Найти»;

- направление просмотра (по строкам или по столбцам)  в поле «Просматривать»;
- область поиска (формулы, значения, примечания);

- дополнительные условия:

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

флажок Ячейка целиком для поиска полного и точного совпадения со строкой знаков, введенной в поле Найти.

Задание. Активизируйте первую ячейку рабочего листа. Найдите последовательно все записи, в которых оценка равна 3.
Какие дополнительные установки в форме «Найти и Заменить» пришлось выполнить?

 

4       Контроль ввода данных

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

Задание.
Вставьте новый лист и назовите его «Контроль». В первой строке листа введите имена столбцов: «ФИО», «Тест1», «Тест3», «Тест3».

Выделите последовательно каждый из столбцов  «Тест1», «Тест3», «Тест3» и командой меню Данные – Проверка  задайте на вкладке «Параметры» интервалы допустимых значений.  Укажите эти интервалы на вкладке «Сообщение для ввода» в форме подсказки.. Для всех трех столбцов укажите разные типы «Сообщения об ошибке» (Останов, Предупреждение, Сообщение).
Проверьте действие контроля ввода данных.

Установите для столбца «ФИО» на вкладке «Параметры» тип данных «Список», в поле  «Источник» введите список фамилий:

Иванов; Петров; Сидоров

Задайте Сообщение для ввода и Сообщение об ошибке.

Введите данные в столбец «ФИО», используя список.

Введите в столбец G рабочего листа три фамилии, например, Зайцев, Волков, Медведев.
Измените параметры контроля данных для столбца «ФИО», задав в качестве источника диапазон ячеек с фамилиями из столбца G. Введите данные в столбец «ФИО», используя новый список.

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

На листе «Форма» выделите диапазон  ячеек с фамилиями и задайте ему имя, например, «ФИО».

На листе «Контроль» выделите первый столбец «ФИО» и выполните команду  меню Данные – Проверка. На вкладке «Параметры» в поле «Источник» сверните диалоговое окно и с помощью команды «Вставка»-«Имя»-«Вставить» задайте назначенное имя диапазона источников списка.

Введите данные в столбец «ФИО», используя список фамилий из листа Форма.

 

 

 

5.  Некоторые функции для работы со списками

Функция РАНГ возвращает для заданного значения его порядковый номер в упорядоченном ряду входных данных:
=РАНГ(число;диапазон;порядок)

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

Задание. На рабочем листе «Форма» добавьте три столбца, содержащие место, занятое каждым испытуемым в каждом из тестов.