MS Excel предлагает специальное средство, позволяющее проверить, удовлетворяют ли заданным условиям вводимые в список значения. Проверке подвергаются только значения, вводимые пользователем непосредственно в ячейки. Поэтому список может содержать некорректные данные, если они оказались там, в результате операций копирования и вставки.
Чтобы задать условия проверки данных, нужно выделить диапазон ячеек, к которому должны применяться эти условия, затем воспользоваться командой Данные\Проверка. На экране появится диалоговое окно Проверка вводимых значений, содержащее три вкладки: Параметры, Сообщение для ввода, Сообщение об ошибке.
Задание типа данных и допустимых значений
Вкладка Параметры позволяет задать тип и интервал значений, которые разрешается вводить. На рис. 24 приведен пример определения типа и интервала вводимых значений.
Рис. 24. Пример определения типа и интервала вводимых значений
Чтобы задать список допустимых значений, его нужно сначала сформировать на рабочем листе, а потом, в раскрывающемся списке Тип данных выбрать вариант Список (рис. 25) и в поле Источник указать диапазон, в котором хранится список допустимых значений.
Чтобы для проверки данных Excel использовал формулу, в раскрывающемся списке Тип данных нужновыбрать вариант Другой и затем ввести нужное выражение в поле Формула.
Рис. 25. Пример задания списка допустимых значений
Сообщение для ввода
Чтобы задать подсказку, которую Excel будет выводить при вводе значений в заданный диапазон, в окне Проверка вводимых значений нужно воспользоваться вкладкой Сообщение для ввода. Здесь можно ввести заголовок и текст сообщения (рис. 26). Когда проверяемая ячейка будет выделена, это сообщение появится рядом с ней как примечание.
Рис. 26. Пример задания сообщения для ввода
Задание сообщения об ошибке
Если в проверяемую ячейку введено неправильное значение, Excel выводит стандартное сообщение об ошибке и предлагает повторить или отменить ввод. Вместо стандартного сообщения можно задать пользовательское. Для этого на вкладке Сообщение об ошибке (рис. 27) диалогового окна Проверка вводимых значений нужно ввести заголовок и текст сообщения.
Кроме того, в раскрывающемся списке Вид можно выбрать тип сообщения об ошибке:
- Останов;
- Предупреждение;
- Сообщение.
Эти три варианта отличаются значками, которые выводятся рядом с текстом сообщения, а также набором кнопок.
Рис. 27. Задание сообщения об ошибке
Рекомендуемая методика выполнения РАБОТ
Варианты индивидуальных заданий по работе со списками в MS Excel
В соответствии с вариантом выберите из табл. 2 предметную область. Создайте на отдельном листе список, который должен содержать не менее 60-80 записей. Затем над созданным списком необходимо выполнить следующие действия:
· сортировку;
· поиск информации с помощью автофильтра;
· поиск информации с помощью расширенного фильтра;
· подведение итогов;
· анализ списка с помощью функций для анализа списка;
· проверку вводимых значений.
Каждое задание выполнять на отдельном листе; листы именовать в соответствии с выполняемым заданием (например, "Автофильтр", "Сортировка в особом порядке" и т.п.). Для этого потребуется копировать список на нужное количество листов.
Формулировка заданий для поиска информации с помощью автофильтра и расширенного фильтра, а также для анализа списка с помощью функций дана в общем виде. Например: "Найти всех сотрудников с фамилией на букву Буква ". При решении задачи вместо слова Буква нужно подставить конкретное значение в соответствии с данными в списке.
1. Сортировка (табл. 3). Это задание состоит из двух пунктов: 1) сортировка по 4-м и более полям и 2) сортировка в особом порядке. Во втором столбце таблицы указаны поля, по которым нужно осуществить сортировку. В третьем столбце указано поле, для которого нужно осуществлять сортировку в особом порядке. Порядок сортировки задать самостоятельно, но этот порядок должен отличаться от порядка "по убыванию" и "по возрастанию".
2. Автофильтр (табл. 4).
3. Расширенный фильтр (табл. 5). При формировании некоторых критериев отбора следует использовать вычисляемые условия.
4. Подведение промежуточных итогов (табл. 6). Итоги во многих вариантах нужно проводить в несколько этапов. При этом заменять текущие итоги не нужно.
5. Функции для анализа списка (табл. 7).
6. Проверка вводимых значений (табл. 8). В таблице указано поле, для которого требуется задать проверку водимых значений. В некоторых вариантах даны рекомендации для реализации заданий. В других нужно самостоятельно определить допустимые значения для указанного поля.
При выполнении задания, необходимо зафиксировать строку с именами полей, чтобы строка заголовков всегда оставалась видимой.
При вводе информации в список лучше воспользоваться наиболее простым способом ввода информации в список - автоматически создаваемой формой данных.
Таблица 2. Предметные области
| № | Предметная область | Пояснения |
| 1 - 4 | Отдел кадров (Фамилия, Имя, Отчество, Отдел, Оклад, Пол, Дата рождения, Возраст, Дата приема на работу) | Поле Возраст необходимо рассчитывать по формуле |
| 5 - 8 | Деканат (Фамилия, Имя, Отчество, Дата рождения, Группа, Факультет, Предмет, Дата сдачи экзамена, Оценка) | Значения поля Оценка: Отлично, Хорошо и т.д. |
| 9 - 12 | Нагрузка преподавателя (ФИО, Ученая степень, Должность, Кафедра, Название предмета, Специальность, Группа, Факультет, Вид занятия, Количество часов) | Значения поля Вид занятия: лекции, лабораторные работы, курсовая работа и т.д. |
| 13 - 16 | Продажи (Менеджер, Клиент, Вид сделки, Товар, Количество, Цена, Сумма, Дата) | Значения поля Вид сделки: поставка, продажа |
| 17 - 20 | Поставки (Дата поставки, Поставщик, Количество поставленной продукции, Способ перевозки, Транспортные издержки на единицу товара, Цена единицы продукции без транспортных издержек, Стоимость перевозимого товара, Общие транспортные расходы) | Значения поля Способ перевозки: ж/д., самолет и т.п. Поле Общие транспортные расходы необходимо рассчитывать по формуле |
Таблица 3. Сортировка
| № | Сортировка по 4-м и более полям | Сортировка в особом порядке |
| Фамилия, Имя, Отчество, Дата рождения | Отдел | |
| Отдел, Фамилия, Имя, Отчество | Фамилия | |
| Дата рождения, Фамилия, Имя, Отчество | Отдел | |
| Оклад, Фамилия, Имя, Отчество, Отдел | Возраст | |
| Фамилия, Имя, Отчество, Дата рождения, Факультет | Факультет | |
| Предмет, Дата сдачи экзамена, Фамилия, Имя, Отчество | Предмет | |
| Предмет, Оценка, Фамилия, Имя, Отчество | Группа | |
| Факультет, Предмет, Оценка, Группа, Фамилия, Имя, Отчество | Оценка | |
| Кафедра, Должность, Ученая степень, ФИО | Ученая степень | |
| Кафедра, ФИО, Факультет, Группа | Должность | |
| Название предмета, Кафедра, Должность, Ученая степень, ФИО | Вид занятия | |
| Вид занятия, Название предмета, Факультет, Группа, ФИО | Название предмета | |
| Менеджер, Клиент, Товар, Количество | Товар | |
| Клиент, Менеджер, Товар, Дата | Клиент | |
| Товар, Менеджер, Клиент, Сумма | Менеджер | |
| Дата, Менеджер, Товар, Клиент, Количество | Товар | |
| Поставщик, Способ перевозки, Стоимость перевозимого товара, Дата поставки | Способ перевозки | |
| Способ перевозки, Поставщик, Дата поставки, Общие транспортные расходы | Поставщик | |
| Поставщик, Способ перевозки, Дата поставки, Транспортные издержки на единицу товара | Способ перевозки | |
| Дата поставки, Способ перевозки, Поставщик, Общие транспортные расходы, Количество поставленной продукции | Поставщик |
Таблица 4. Автофильтр
| № | Запрос |
| Получить информацию о сотрудниках двух конкретных отделов, родившихся в период [ Дата1; Дата2 ] и принятых на работу позднее даты Дата3 | |
| Получить информацию о мужчинах, имя которых начинается на букву Буква, отчество - " Иванович ", с окладом ниже значения Оклад | |
| Получить информацию о женщинах, фамилии которых заканчиваются на " их " или " ко ", в возрасте от 35 до 40 лет, работающих либо в отделе Отдел1, либо в отделе Отдел2 | |
| Определить, есть ли в отделах Отдел1 и Отдел2 мужчины, размеры окладов которых относятся к пяти наибольшим на всем предприятии | |
| Отобразить информацию о студентах групп Группа1 и Группа2 по предмету Предмет с оценками Хорошо и Отлично | |
| Найти информацию о студентах, сдавших экзамены по предметам Предмет1 и Предмет2 на оценку Отлично либо раньше даты Дата1, либо позже даты Дата2 | |
| Найти студентов - отличников с двух факультетов Факультет1 и Факультет2, родившихся в период [ Дата1; Дата2 ] | |
| Найти информацию о студентах групп Группа1 и Группа2, сдавших экзамен по предмету Предмет либо на оценку Неудовлетворительно, либо на оценку Отлично | |
| Определить, читают ли лекции по предмету Предмет на факультетах Факультет1 и Факультет2 профессора | |
| Определить, в каких группах читает лекции и ведет лабораторные работы преподаватель Преподаватель | |
| Найти информацию о доцентах и ассистентах с фамилией Фамилия, которые проводят занятия по предмету Предмет на факультетах Факультет1 и Факультет2 | |
| Найти всех преподавателей с кафедры Кафедра, которые ведут лабораторные работы и практические занятия в группах Группа1 и Группа2 | |
| Найти информацию о деятельности менеджере Менеджер в период [ Дата1; Дата2 ] | |
| Определить клиентов, покупающих или поставляющих товары Товар1 и Товар2 в количестве больше Количество | |
| Найти информацию, связанную с покупкой или продажей товаров Товар1 и Товар2 клиентом Клиент на сумму Сумма и выше | |
| Определить 4 самые крупные сделки за последний месяц | |
| Найти информацию о поставках от поставщика Поставщик в период с Дата1 по Дата2 | |
| Получить информацию о поставках от поставщика Поставщик способом перевозки Способ_перевозки после даты Дата | |
| Определить, какими способами перевозки поставлялся товар от поставщиков Поставщик1 и Поставщик2 в период с Дата1 по Дата2 | |
| Определить, какие поставщики использовали способы перевозки Способ_перевозки1 и Способ_перевозки2 с общими транспортными расходами меньше Сумма |
Таблица 5. Расширенный фильтр
| № | Запрос |
| Найти работников отделов Отдел1 и Отдел2 с фамилиями, начинающимися на буквы Буква1 и Буква2, и окладами выше среднего оклада на предприятии | |
| Найти информацию о мужчинах из отдела Отдел1 в возрасте от Возраст1 и Возраст2 и о женщинах из отдела Отдел2 в возрасте от Возраст3 до Возраст4 | |
| Определить, принимались ли на работу в отделы Отдел1 и Отдел2 несовершеннолетние | |
| Найти женщин из отдела Отдел1, родившихся в период [ Дата1; Дата2 ], и мужчины из отдела Отдел2, родившихся в период [ Дата3; Дата4 ] | |
| Найти информацию о студентах факультетов Факультет1 и Факультет1, сдавших экзамены в период с Дата1 по Дата2 | |
| Определить студентов факультетов Факультет1 и Факультет2, сдавших экзамены по предмету Предмет на оценки Удовлетворительно или Хорошо | |
| Найти информацию о студентах в возрасте от Возраст1 до Возраст2, сдавших экзамены по предметам Предмет1 и Предмет2 на оценку Отлично | |
| Найти информацию о студенте Фамилия, сдавшим экзамен по предмету Предмет на оценку выше средней оценки по этому предмету по вузу | |
| Отобразить лекционные курсы, которые обеспечивает кафедра Кафедра, на которые отводится количество часов больше среднего количества часов, отводимых на лекционный курс | |
| Найти информацию о доцентах и ассистентах кафедр Кафедра1 и Кафедра2, которые проводят практические занятия и лабораторные работы на факультетах Факультет1 и Факультет2 | |
| Найти дисциплины, изучаемые на факультете Факультет с минимальным количеством часов, отводимых на практические задания | |
| Найти дисциплины, изучаемые на факультетах Факультет1 и Факультет2 с максимальным количеством часов, отводимых на практические задания | |
| Отобразить информацию о сделках, проведенных менеджером Менеджер, с суммой, превышающей среднюю сумму сделки | |
| Найти информацию о деятельности менеджера Менеджер1 по товару Товар1 и Менеджера2 по товару Товар2 в период [ Дата1; Дата2 ] | |
| Найти поставки от клиентов Клиент1 и Клиент2 на суммы, равные средней сумме поставки +N рублей или -N рублей | |
| Отобразить информацию о сделках за период с Даты1 по Дата2, проведенных менеджерами Менеджер1, Менеджер2 и Менеджер3 по товарам Товар1, Товар2 и Товар3 на сумму, превышающую Сумма | |
| Найти поставки от поставщиков Поставшик1, Поставшик2 и Поставшик3 в период от Даты1 до Дата2 на суммы, превышающие среднюю сумму поставки в 1,2 раза | |
| Найти поставки способами перевозки Способ _ первозки1 и Способ _ перевозки2 от поставщиков Поставщик1, Поставщик2 и Поставщик3 со стоимостью перевозимого товара от Сумма1 до Сумма2 рублей | |
| Пусть самые крупными поставки являются те, у которых количество поставленной продукции находятся в пределах: максимальное количество поставленной продукции минус минимальное количество поставленной продукции. Определить, производились ли крупные поставки в период с Дата1 по Дата2 способами перевозки Способ _ перевозки1 и Способ _ перевозки2 | |
| Для каждого способа перевозки в период с Дата1 по Дата2 найти поставки для соответствующего способа перевозки |
Таблица 6. Подведение промежуточных итогов
| № | Задание |
| Определить средний оклад и сумму всех окладов в каждом отделе | |
| Определить количество и средний возраст сотрудников в каждом отделе | |
| Определить количество мужчин и женщин на предприятии и средний оклад мужчин и женщин | |
| Определить минимальный и максимальный оклад в каждом отделе | |
| Определить среднюю оценку в каждой группе по каждому предмету | |
| Определить количество студентов в каждой группе и на каждом факультете | |
| Определить количество экзаменов, сданных каждым студентом, и средний балл студента | |
| Определить, сколько оценок Отлично, Хорошо, Удовлетворительно и Неудовлетворительно в каждой групп по каждому предмету | |
| Определить, сколько часов отводится на каждый предмет в каждой группе | |
| Определить, сколько сотрудников на каждой кафедре и сколько на каждой кафедре ассистентов, доцентов и профессоров | |
| Определить общую нагрузку в часах и нагрузку по видам занятий для каждого преподавателя | |
| Определить, сколько предметов ведет каждый преподаватель, и подсчитать его общую нагрузку в часах | |
| Определить, на какую сумму каждый менеджер провел сделок и на какую сумму каждый менеджер провел сделок с каждым клиентом | |
| Определить общую сумму сделок каждого менеджера, а также сумму поставок и продаж, проведенных каждым менеджером | |
| Определить, сколько каждого товара поставлено и отпущено | |
| Определить, какое количество товара поставил и закупил каждый клиент | |
| Определить общее количество поставленной продукции от каждого поставщика, а также количество поставленной продукции каждым способом перевозки | |
| Определить количество поставленной продукции каждым способом перевозки и среднюю стоимость транспортных расходов | |
| Определить транспортные расходы для каждого способа перевозки, а также транспортные расходы каждого поставщика | |
| Определить общую стоимость перевозимого товара от каждого поставщика и стоимость перевозимого товара каждым способом перевозки |
Таблица 7. Функции для анализа списка
| № | Задание |
| Подсчитать средний оклад мужчин старше 50 лет | |
| Подсчитать минимальный оклад у женщин, работающих в отделе Отдел | |
| Подсчитать количество человек, принятых на работу после даты Дата | |
| Подсчитать количество сотрудников отдела Отдел | |
| Подсчитать количество студентов, обучающихся на факультете Факультет | |
| Подсчитать, сколько студентов группы Группа по предмету Предмет оценку Оценка | |
| Подсчитать средний балл в группе Группа по предмету Предмет | |
| Подсчитать средний балл студента Фамилия по всем предметам | |
| Подсчитать, сколько курсовых работ у группы Группа | |
| Подсчитать общую нагрузку преподавателя Преподаватель | |
| Определить, сколько лекционных курсов у преподавателя Преподаватель | |
| Подсчитать, какой объем времени отводится преподавателю Преподаватель на проведение курсовых работ | |
| Определить, на какую сумму был поставлен товар Товар от клиента Клиент | |
| Определить, на какую сумму был отпущен товар Товар клиенту Клиент | |
| Определить среднюю цену, по которой поставлялся товар Товар | |
| Определить максимальную цену, по которой был продан товар Товар | |
| Определить общую стоимость товара, перевозимого от поставщика Поставщик способом перевозки Способ _ перевозки | |
| Определить среднюю стоимость транспортных расходов для поставщика Поставщик | |
| Определить среднюю стоимость транспортных расходов для способа перевозки Способ _ перевозки | |
| Определить максимальную стоимость товара, перевозимого от поставщика Поставщик |
Таблица 8. Проверка вводимых значений
| № | Поле | Вид сообщения об ошибке |
| Отдел: список значений | Останов | |
| Пол: список значений | Предупреждение | |
| Дата рождения | Сообщение | |
| Оклад: неотрицательное число | Останов | |
| Факультет: список значений | Предупреждение | |
| Оценка: список значений | Сообщение | |
| Дата сдачи экзамена | Останов | |
| Дата рождения | Предупреждение | |
| Ученая степень: список значений | Сообщение | |
| Должность: список значений | Останов | |
| Факультет: список значений | Предупреждение | |
| Вид занятий: список значений | Сообщение | |
| Менеджер: список значений | Останов | |
| Вид сделки: список значений | Предупреждение | |
| Количество | Сообщение | |
| Дата | Останов | |
| Способ перевозки: список значений | Предупреждение | |
| Количество поставленной продукции | Сообщение | |
| Дата поставки | Останов | |
| Общие транспортные расходы | Предупреждение |
