Работа со списками в Microsoft Excel 2003

Дрига Е.С., Хрусталева Л.С.

 

Работа со списками в Microsoft Excel 2003

Практикум по выполнению индивидуальных

лабораторных заданий для студентов 1-го курса

Всех специальностей всех форм обучения

 

Хабаровск 2007

ББК У.в6

Х12

 

 

Работа со списками в Microsoft Excel 2003: практикум по выполнению индивидуальных лабораторных заданий для студентов 1-го курса всех специальностей всех форм обучения / сост. Е. С. Дрига, Л. С. Хрусталева. – Хабаровск: РИЦ ХГАЭП, 2007. – 48 с.

 

Рецензент Д. В. Тимошенко, кандидат технических наук, доцент кафедры ДВС ТОГУ

 

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

 

Елена Сергеевна Дрига

Луиза Салиховна Хрусталева

 

 

Работа со списками в Microsoft Excel 2003

практикум по выполнению индивидуальных лабораторных заданий

для студентов 1-го курса всех специальностей и форм обучения

 

Редактор Г.С. Одинцова

 

 

_______________________________________________________________

Подписано к печати Формат 60х84/16.

Бумага писчая. Офсетная печать. Усл.печ.л. 2,8. Уч.-изд.л. 2,0.

Тираж 300 экз. Заказ №___________________

_______________________________________________________________

680042, г.Хабаровск, ул.Тихоокеанская, 134, ХГАЭП, РИЦ

 

© Хабаровская государственная академия экономики и права, 2007

Введение

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

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

Списком в Microsoft Excel являются связанные данные, например, база данных счетов или набор адресов и телефонов клиентов.

Лабораторный практикум содержит основные понятия и средства для работы со списками в Excel 2003.

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

Библиографический список поможет студентам найти ответы на контрольные вопросы.

 

Работа со списками

 

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

Создание списка

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

Рекомендации по организации и созданию списка.

1. Желательно расположить на рабочем листе только один список, ведь в рабочей книге допустимо иметь много листов.

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

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

4. В каждом столбце должна содержаться однотипная информация.

5. Данные в списке должны быть оформлены одинаково, но не так, как заголовки столбцов.

6. Список не должен содержать строки с пустыми ячейками или пунктирной линией. Пустая строка означает конец списка.

7. Чтобы положение заголовков столбцов не изменялось при просмотре таблицы, необходимо активизировать команду Закрепить области меню Окно.

 

 

Рис. 1. Структура элементов списка

Для размещения имени поля списка в одной ячейке необходимо:

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

2) выполнить команду Формат, Ячейки;

3) в диалоговом окне открыть вкладку Выравнивание и установить следующие параметры:

· из списка поля по горизонтали выбрать пункт по значению;

· из списка поля по вертикали выбрать пункт по центру;

· в поле Отображение установить флажок на опции переносить по словам.

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

Ввод, редактирование и форматирование данных в списке

Данные в список можно ввести несколькими способами.

 

Непосредственный ввод

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

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

Диалоговое окно Форма позволяет вводить и обрабатывать информацию списка. Это окно содержит несколько полей ввода, названия которых соответствуют заголовкам столбцов списка. В окне формы данных можно вести поиск информации, дополнять список новыми записями, удалять ненужные и редактировать имеющиеся данные. Для открытия окна выполнить следующее:

1) поместить указатель мыши в любую позицию списка;

2) выполнить команду Данные, Форма.

Откроется диалоговое окно, в строке заголовка которого будет указано имя текущего рабочего листа. В нём приведено общее количество записей в списке и указана позиция текущей записи. С помощью полосы прокрутки, кнопок Далее и Назад, клавиш Page Up и Page Down, и клавиш управления курсором можно перейти к нужной записи списка.

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

Для ввода данных выполнить следующее:

1) нажать кнопку Добавить: таким образом вы очистите все поля;

2) ввести данные в соответствующие поля. Для быстрого перемещения от поля к полю используется клавиша Tab. Нажатие клавиши Enter эквивалентно нажатию кнопки Добавить, поэтому после ввода последнего поля просто нажать эту клавишу;

3) для ввода следующих данных повторить предыдущие пункты;

4) нажать кнопку Закрыть, чтобы вернуться к рабочему листу.

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

Изменения, внесённые в запись, сохраняются при переходе к другой записи или в результате нажатия клавиши Enter. Кнопка Вернуть и клавиша Esc предназначены для отмены внесённых изменений. Если текущая запись не изменялась, то кнопка Вернуть недоступна, а нажатие на клавишу Esc приводит к закрытию диалогового окна формы данных.

Кнопка Удалить позволяет удалить из списка текущую запись.

Проверка вводимых значений

Для упрощения ввода данных и автоматической проверки их правильности выполнить следующее:

1) выделить диапазон ячеек;

2) выполнить команду Данные, Проверка. Откроется диалоговое окно Проверка вводимых значений.

На вкладке Параметры указывается тип и интервалзначений, которые разрешается вводить. Выбор типа данных вводимых значений в списке Тип данных позволяет определить, какие условия можно накладывать на значения ячеек. Если для определения допустимых значений требуется ввести формулу, выражение или ссылку на вычисления в другой ячейке, выбрать в списке строку Другой.

Вкладка Сообщение для ввода позволяет создать подсказку пользователю о том, какие данные можно вводить.

На вкладке Сообщения об ошибке указывается сообщение, которое будет появляться на экране при вводе некорректных данных.

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

В некоторых случаях порядок строк в списке не имеет значения. Но иногда возникает необходимость, чтобы строки имели определённую последовательность. Изменение порядка строк в списке называется сортировкой.

В Excel используется следующий порядок сортировки данных по умолчанию:

1) числа от наименьшего отрицательного до наибольшего положительного числа;

2) текст – по алфавиту;

3) логическое значение ложь;

4) логическое значение истина;

5) значения ошибок;

6) пустые значения.

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

2.1. Простая сортировка

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

1) выделить ячейку в столбце, который следует использовать в качестве ключа сортировки;

2) нажать кнопку Сортировка по возрастанию, либо Сортировка по убыванию.

Таким способом можно упорядочить данные только по одному полю.

2.2. Сортировка по нескольким столбцам

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

Для сортировки данных выполнить следующее:

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

2) выполнить команду Данные, Сортировка. Откроется диалоговое окно Сортировка диапазона;

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

4) в раскрывающемся списке Сортировать по выбрать первое поле, по которому следует упорядочить данные. Это поле называется первым ключом сортировки;

5) выбрать положение переключателя по возрастанию или по убыванию;

6) выбрать имена полей, если требуется, в раскрывающихся списках Затем и В последнюю очередь, по. Второе поле сортировки используется, если возникает повторение значения первого поля, а третье поле – если повторяются значения и первого, и второго полей;

7) нажать кнопку ОК.

Вычисление итогов

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

1) активизировать одну из ячеек таблицы;

2) выполнить команду Данные, Итоги. Появится диалоговое окно Промежуточные итоги;

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

4) в поле списка Операция установитьфункцию (табл. 2);

5) в поле списка Добавить итоги выводятся названия всех полей списка. Активизировать поле или поля, для которых надо вычислить промежуточные итоги;

6) щёлкнуть кнопку ОК.

В диалоговом окне Промежуточные итоги имеются следующие опции:

1. Заменить текущие итоги. Любые существующие формулы итогов заменяются новыми.

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

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

4. Убрать все. Из списка удаляются все строки с итоговыми значениями.

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

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

Таблица 2

Функция	Назначение
Сумма	Суммирует все значения и выдает общий итог
Количество значений	Определяет количество элементов ряда
Среднее	Вычисляет среднее арифметическое для ряда значений
Максимум	Определяет наибольшее значение в ряде значений
Минимум	Определяет наименьшее значение в ряде значений
Произведение	Вычисляет произведение всех значений ряда
Количество чисел	Устанавливает количество ячеек, содержащих числовые значения, в выделенной области

 

Контрольные вопросы

1. Для чего используется команда Итоги?

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

3. Какие встроенные функции могут использоваться при подведении промежуточных итогов?

4. Что появляется слева от таблицы после выполнения команды Итоги?

5. Как скрыть и отобразить вложенные уровни списка?

 

Фильтрация данных

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

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

Функция Автофильтра

Функция Автофильтра позволяет производить отбор записей непосредственно на рабочем листе. Для выбора данных можно задать целый ряд критериев.

Чтобы воспользоваться Автофильтром:

1) выделить ячейку внутри списка или выделить список целиком;

2) выполнить команду Данные, Фильтр, Автофильтр;

3) ячейки с названиями полей превращаются в раскрывающиеся списки (рис. 3).

 

 

Рис. 3. Вид полей списка после активизации Автофильтра

 

Чтобы отфильтровать записи:

1) раскрыть список, соответствующий полю, которое следует включить в критерий (рис. 3);

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

3) задать параметры в диалоговом окне Пользовательский автофильтр, если выбран критерий Условие.

Диалоговое окно Пользовательский автофильтр предоставляет возможность задать комплексные критерии, объединяя их с помощью условных операторов И, ИЛИ.

Таблица 4

Критерии Автофильтра

Критерии	Описание
Все	Вывод на экран всех записей (снятие ограничений на поле)
Первые 10	Вывод на экран заданного числа или процента первых или последних элементов списка
Условие	Вывод на экран диалогового окна Пользовательский автофильтрдлязаданияодного или двух условий с использованием шаблонов, операцийсравненияи логических операций И, ИЛИ
Точное значение	Вывод на экран записей, поля которых в точности совпадают с выбранным значением

Результаты работы Автофильтра будутмоментально отображены. Не отвечающие критерию записи будут скрыты. Чтобы запись осталась на экране, она должна удовлетворять условиям, наложенным на все поля, т. е. условия на различные поля соединяются операцией И.

Для снятия критерия выполнить команду Данные, Фильтр, Отобразить все. Для отмены режима Автофильтра выполнить команду Данные, Фильтр, Автофильтр.

Расширенный фильтр

Расширенный фильтр требует большей работы, но и предоставляемые им возможности поиска и фильтрации шире. Можно более свободно применять операции И, ИЛИ, а также составлять вычисляемые критерии. Отфильтрованные данные могут быть скопированы в другой диапазон ячеек. Основой Расширенного фильтра является множество задаваемых критериев.

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

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

Создание макросов

Если какие-то операции со списком производятся часто, то удобнее сгруппировать несколько команд в один макрос и назначить его кнопкой на панели инструментов или комбинацией клавиш.

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

 

Создание макросов

Для создания макроса необходимо:

1) выполнить команду Сервис, Параметры, Безопасность, Безопасность макросов, на вкладке Уровень безопасности установить переключатель Низкая;

2) сделать активной первую ячейку списка;

3) выполнить команду Сервис, Макрос, Начать запись, в поле Имя макроса диалогового окна Запись макроса указать имя создаваемого макроса (имя макроса должно начинаться с буквы и без пробелов), например, Итог1_Запись.

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

4) нажать кнопку ОК.

Начинается запись макроса.

О начале записи свидетельствует появившаяся на экране панель Остановить запись с двумя кнопками – Остановить запись (левая) и Относительная ссылка (правая). Нажатие правой кнопки приводит к тому, что при записи макроса будут использоваться относительные ссылки.

1. Выполнить всю последовательность действий, которую необходимо сохранить.

Если были допущены лишние операции, то они тоже будут сохранены.

2. Для окончания записи нажать кнопку Остановить запись или выполнить команду Сервис, Макрос, Остановить запись.

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

Задание 1. Сортировка и подведение итогов в списке

 

1. Создать в своей папке новую рабочую книгу.

2. В рабочую книгу добавить два листа. Переименовать названия листов: Лист1 – «Исходная таблица», Лист2 – «Сортировка», Лист3 – «Форма», Лист4 – «Итоги», Лист5 – «Формат пользователя». Для ярлычков листов задать различные цвета.

3. Построить на листе «Исходная таблица» исходную таблицу, соответствующую Вашему варианту (Приложение 1).

4. Выполнить вычисления в последнем столбце таблицы согласно предложенной формуле в Приложении 1.

5. Отформатировать таблицу по своему усмотрению.

6. Скопировать таблицу на лист «Сортировка» четыре раза и выполнить сортировку данных (Задание 1.1 Приложения 1). Для сортировки по первому ключу воспользоваться командами: Сервис, Параметры, Список и Данные, Сортировка, Параметры.

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

8. Скопировать таблицу с листа «Исходная таблица» на лист «Форма». Выполнить с помощью команды Форма, следующие действия:

- просмотреть последовательно все записи в таблице;

- добавить одну запись в таблицу (на своё усмотрение);

- удалить последнюю запись в таблице;

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

 

9. Скопировать таблицу с листа «Исходная таблица» на лист «Итоги».

10. Для подведения итогов создать макросы «Итог_Запись_1», «Итог_Запись_2» и «Итог_Удаление». Добавить итоги по столбцам, используя команду Данные, Итоги (Задание 1.3 Приложения 1):

̶ макросы сохранить в текущей книге;

̶ в описании макросов добавить фамилию студента и номер группы;

̶ назначить комбинации клавиш для ускоренного вызова макросов;

̶ кнопки для вызова макросов расположить на специально созданной панели инструментов.

11. Скопировать таблицу с листа «Форма» на лист «Формат пользователя».

12. Создать пользовательские числовые форматы (Задание 1.4 Приложения 1).

13. Добавить в рабочую книгу новые листы: «Автофильтр», «Расширенный фильтр». Изменить цвет ярлычков листов.

14. Сохранить рабочую книгу.

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

 

Задание 2. Фильтрация данных в списке

 

1. Скопировать таблицу с листа «Исходная таблица» на лист «Автофильтр».

2. Выполнить последовательно отбор данных, используя функцию Автофильтр (Задание 2.1 Приложения 1). Отфильтрованные строки копировать ниже исходной таблицы.

3. Скопировать таблицу с листа «Исходная таблица» на лист «Расширенный фильтр».

4. Выполнить последовательно отбор данных, используя Расширенный фильтр (Задание 2.2 Приложения 1). Диапазоны условий и отфильтрованные строки копировать ниже исходной таблицы.

5. Сохранить рабочую книгу.

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

 

Приложение 1

Вариант 1

Стоимость билетов = Цена билета * (Число взрослых билетов + Число детских билетов * 50%)

Задание 1.1. Сортировка:

а) дата вылета – по возрастанию (в первой таблице);

б) № рейса – по возрастанию, а внутри группы число взрослых билетов – по возрастанию (во второй таблице);

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

г) № рейсов – по первому ключу: 117, 673, 45 (в четвёртой таблице).

Задание 1.2. Форма:

а) класс полёта – Эконом;

б) дата вылета – после 1 июля 2005 года;

в) цена билета – свыше 10 тыс. руб. и число взрослых билетов более 20;

г) дети, летящие бизнес-классом.

Задание 1.3. Итоги:

а) для каждого рейса определить общую стоимость билетов и максимальную цену билета (лист «Итоги_1»);

б) для каждой даты найти количество производимых рейсов, число взрослых и детских билетов (лист «Итоги_2»).

Задание 1.4. Пользовательский формат:

а) число взрослых билетов – «шт»;

б) число детских билетов– «шт».

Задание 2.1. Автофильтр:

а) № рейса – 117;

б) дата вылета – июль;

в) цена билета – от 15 000 руб. до 20 000 руб. и класс полёта – бизнес;

г) № рейса – 45 и класс полёта – эконом;

д) стоимость билетов – менее 100 000 руб. или более 500 000 руб.;

е) цена билета – наибольшая;

ж) стоимость билетов – три наименьших.

Задание 2.2. Расширенный фильтр:

а) № рейса – 673 или 45;

б) № рейса – 117, дата вылета – февраль 2006;

в) цена билета – от 15 000 руб. до 20 000 руб., класс полёта – бизнес;

г) № рейса – 45 и класс полёта – эконом;

д) стоимость билетов – менее 100 000 руб. или более 500 000 руб.;

е) № рейса – кроме 673 (функция НЕ ());

ж) цена билета – наибольшая (функция МАКС ()) или дата вылета – кроме 20.07.2005 (функция НЕ ());

з) стоимость билетов – наименьшая (функция МИН ()) и наибольшая (функция МАКС ());

и) цена билета – больше среднего (функция СРЗНАЧ ());

к) цена билета – больше среднего на 1 000 руб.

Вариант 2

Заработано = (Отработано часов в рабочие дни + Отработано часов в выходные дни) * Стоимость часа работы

Задание 1.1. Сортировка:

а) Ф. И. О. – по возрастанию (в первой таблице);

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

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

г) должность – по первому ключу: плотник, электрик, столяр (в четвёртой таблице).

Задание 1.2. Форма:

а) дата рождения – до 1972 г.;

б) Ф. И. О. – начинается на букву «К»;

в) отработано часов в рабочие дни – более 30 часов и отработано часов в выходные дни – более 10;

г) плотники с заработком более 5 500 руб.

Задание 1.3. Итоги:

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

б) для каждой стоимости часа работы найти количество сотрудников и средний заработок.

Задание 1.4. Пользовательский формат:

а) отработано часов в рабочие дни – «часов».

б) отработано часов в выходные дни – «часов».

Задание 2.1. Автофильтр:

а) должность – столяр;

б) дата рождения – 1979 год;

в) отработано часов в рабочие дни – от 30 часов до 40 часов и стоимость часа работы – 150 руб.;

г) Ф. И. О. сотрудников – на букву «К» или должность – электрик;

д) заработано – менее 5 000 руб. или более 7 000 руб.;

е) заработано – минимальное;

ж) отработано часов в выходные дни – три наибольших.

Задание 2.2. Расширенный фильтр:

а) должность – столяр, отработано в выходные дни – более 10 часов;

б) отработано часов в выходные дни – 8, 10 или 12 часов;

в) дата рождения – с 1980 года, отработано часов в рабочие дни от 30 до 40;

г) должность – плотник или электрик и заработано – более 5 500 руб.;

д) Ф. И. О. сотрудников – на букву «К» или должность – электрик;

е) заработано – менее 5 000 руб. или более 7 000 руб.;

ж) должность – кроме плотника (функция НЕ ()) или отработано часов в рабочие дни – наибольшее (функция МАКС ());

з) отработано часов в выходные дни – наименьшее (функция МИН ()) и наибольшее (функция МАКС ());

и) стоимость часа работы – больше среднего (функция СРЗНАЧ ());

к) заработано – больше среднего на 1 000 руб.

 

Вариант 3

Ежемесячный взнос = Сумма страховки * 0,1%

Задание 1.1. Сортировка:

а) автовладелец – по возрастанию (в первой таблице);

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

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

г) фирма-страховщик – по первому ключу: РОАЛ, АВТО, ЛЮКС (в четвёртой таблице).

Задание 1.2. Форма:

а) пол – мужской;

б) сумма страховки – более 300 000 руб.;

в) год получения прав – до 2000 года, начато страхование – после 2000 года;

г) женщины, застраховавшиеся в фирме «РОАЛ».

Задание 1.3. Итоги:

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

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

Задание 1.4. Пользовательский формат:

а) год получения прав – «год»;

б) начато страхование – «год».

Задание 2.1. Автофильтр:

а) пол – мужской;

б) начато страхование – 2000 год;

в) сумма страховки – от 300 000 руб. до 500 000 руб.;

г) фирма-страховщик – «АВТО» и начато страхование – с 2002 года;

д) ежемесячный взнос – менее 100 руб. или более 500 руб.;

е) сумма страховки – три наибольших;

ж) сумма страховки – минимальная.

Задание 2.2. Расширенный фильтр:

а) фирма-страховщик – «АВТО», ежемесячный взнос – более 150 руб.;

б) начато страхование – после 2000 год и сумма страховки – от 200 000 руб. до 500 000 руб.;

в) год получения прав – 2000, 2002 или 2004;

г) ежемесячный взнос – менее 100 руб. или более 500 руб.;

д) пол – женский, ежемесячный взнос – от 100 до 500 руб.;

е) автовладелец – на букву «М» или «К» и сумма страховки – более 100 000 руб.;

ж) фирма – кроме ЛЮКС (функция НЕ ()) или сумма страховки – наибольшая (функция МАКС ());

з) год получения прав – наименьший (функция МИН ()) и наибольший (функция МАКС ());

и) ежемесячный взнос – больше среднего (функция СРЗНАЧ ());

к) ежемесячный взнос – больше среднего на 100 руб.

 

Вариант 4

Стоимость партии = (Стоимость товара + Транспортные расходы + Стоимость товара * Налог) * Количество товара

Задание 1.1. Сортировка:

а) наименование товара – по возрастанию (в первой таблице).

б) фирма-производитель – по возрастанию, а внутри группы стоимость товара – по возрастанию (во второй таблице).

в) наименование товара – по убыванию, а внутри группы количество товара и стоимость партии – по убыванию (в третьей таблице).

г) фирма-производитель – по первому ключу: “Престиж”, “Новая”, “Татьяна” (в четвёртой таблице).

Задание 1.2. Форма:

а) наименование товара – компьютерный стол;

б) количество товара – более 5 штук;

в) наименование товара – начинается на букву «К», стоимость товара – более 10 000 руб.;

г) товар стоимостью менее 20 тыс. руб., произведённый фирмой «Престиж».

Задание 1.3. Итоги:

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

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

Задание 1.4. Пользовательский формат:

а) количество товара – «шт».

Задание 2.1. Автофильтр:

а) наименование товара – компьютерный стол;

б) налог – 1% или 2%;

в) количество товара – от 5 до 10;

г) фирма-производитель – «Престиж» и стоимость партии менее 100 000 руб.;

д) стоимость товара – менее 10 000 руб. или более 40 000 руб.;

е) транспортные расходы – минимальные;

ж) стоимость партии – три наибольших.

Задание 2.2. Расширенный фильтр:

а) фирма-производитель – «Новая» и стоимость партии менее 100 000 руб.;

б) стоимость товара – более 10 000 руб. и стоимость партии – от 100 000 руб. до 300 000 руб.;

в) количество товара – 3, 5 или 7;

г) стоимость товара – менее 10 000 руб. или более 40 000 руб.;

д) наименование товара – мягкая мебель, транспортные расходы – от 1 000 до 3 000 руб.;

е) фирма- производитель – «Татьяна» и «Новая», и наименование товара – на букву «К»;

ж) фирма – кроме «Новая» (функция НЕ ()) или стоимость партии – наибольшая (функция МАКС ());

з) стоимость товара – наименьшая (функция МИН ()) и наибольшая (функция МАКС ());

и) стоимость партии – больше среднего (функция СРЗНАЧ ());

к) стоимость партии – меньше среднего на 50 000 руб.

 

Вариант 5

Количество кв. м на 1 человека = Площадь квартиры / Количество проживающих

Задание 1.1. Сортировка:

а) улица – по алфавиту (в первой таблице);

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

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

г) улица – по первому ключу: Мира, Ленина, Малиновского (в четвёртой таблице).

Задание 1.2. Форма:

а) этаж – третий;

б) количество проживающих – более двух;

в) улица – Мира, площадь квартиры – более 50 кв. м;

г) количество проживающих менее 3 человек на втором этаже.

Задание 1.3. Итоги:

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

б) для каждого этажа определить количество квартир и общую площадь квартир.

Задание 1.4. Пользовательский формат:

а) площадь квартиры – «кв. м»;

б) количество проживающих – «чел».

Задание 2.1. Автофильтр:

а) этаж – третий;

б) улица – Мира и количество проживающих – три;

в) площадь квартиры – от 50 кв. м до 60 кв. м;

г) этаж – второй и улица – на букву «М»;

д) площадь квартиры – менее 30 кв. м или более 60 кв. м;

е) площадь квартиры – минимальная;

ж) количество кв. м на 1 человека – три наибольших.

Задание 2.2. Расширенный фильтр: