Опубликован: 26.05.2021 | Доступ: платный | Студентов: 20 / 3 | Длительность: 14:25:00
Лекция 5:

Средства работы с базами данных (списками). Сортировка и фильтрация данных

< Лекция 4 || Лекция 5 || Лекция 6 >
Аннотация: Цель работы: научиться использовать электронные таблицы Excel для создания списков, сортировки данных в списке, фильтрации данных. Содержание работы: Создание базы данных (списка) в Excel. Сортировка данных в списке. Фильтрация данных в списке с использованием Афтофильтра. Фильтрация данных в списке с использованием Расширенного фильтра. Задание множественного критерия сравнения и вычисляемого критерия. Просмотр записей, поиск и фильтрация данных списка с помощью форм данных.

МЕТОДИЧЕСКИЕ УКАЗАНИЯ

Понятие о списке (базе данных Excel)

Электронные таблицы Excel можно использовать для организации работы с небольшими реляционными базами данных. В этом случае электронную таблицу называют списком или базой данных Excel (рис. 5.1 рис. 5.1) и используют соответствующую терминологию:

  • строка списка – запись базы данных;
  • столбец списка – поле базы данных.
Структурные элементы списка Excel

Рис. 5.1. Структурные элементы списка Excel

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

Внимание!

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

Список (база данных Excel) – электронная таблица, в которой строки (записи) имеют фиксированную структуру, а имена столбцов (полей) занимают одну строку.

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

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

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

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

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

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

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

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

В среде Excel предусмотрены три уровня сортировки, которые определяются в диалоговом окне Сортировка диапазона (рис. 5.2 рис. 5.2, а) параметром Сортировать по.

Диалоговые окна для операции сортировки: а – сортировка диапазона; б – параметры сортировки

Рис. 5.2. Диалоговые окна для операции сортировки: а – сортировка диапазона; б – параметры сортировки

Сначала осуществляется сортировка в столбце 1-го уровня, затем сортируются одинаковые записи 1-го столбца по столбцу 2-го уровня, затем сортируются одинаковые записи 2-го столбца по столбцу 3-го уровня.

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

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

Кнопка <Параметры> выводит диалоговое окно "Параметры сортировки", в котором задаются дополнительные установки сортировки (рис. 5.2 б рис. 5.2): с учетом регистра или без учета; по столбцам или по строкам; порядок сортировки – обычный или специальный, выбранный из предлагаемого списка. Этот список можно сформировать самостоятельно с помощью Сервис, Параметры, вкладка Списки.

Таблица 5.1.
Действие Содержание действия
Сортировка списка
  1. Установить курсор в области списка
  2. Выполнить команду Данные, Сортировка
  3. Указать порядок и направление сортировки для каждого ключа сортировки
  4. Нажать кнопку <Параметры> и выбрать параметры сортировки (порядок по первому ключу, учет регистра, направление сортировки – по строкам или по столбцам)
Создать новый список для сортировки
  1. Выполнить команду Сервис, Параметры, вкладка Списки
  2. Нажать кнопку <Добавить>
  3. Сформировать элементы списка
Изменить список для сортировки
  1. Выполнить команду Сервис, Параметры, вкладка Списки
  2. Выделить в окне Списки начало редактируемого списка
  3. Перейти к элементам списка и отредактировать их (добавить, удалить, отредактировать)
  4. Нажать кнопку <ОК>
Удалить список для сортировки
  1. Выполнить команду Сервис, Параметры, вкладка Списки
  2. Выделить в окне Списки начало редактируемого списка
  3. Нажать кнопку <Удалить>

Фильтрация данных в списке – это выбор данных по заданному критерию (условию). Осуществляется эта операция с помощью команды Данные > Фильтр.

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

Автофильтрация

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

Список с автофильтром

Рис. 5.3. Список с автофильтром

По отдельному столбцу в списке критериев отбора предусматриваются следующие варианты:

  • все – выбираются все записи без ограничений;
  • первые 10 – данный пункт позволяет во вновь появляющемся диалоговом окне "Наложение условия по списку" (рис. 5.4 рис. 5.4) выбрать определенное количество наибольших или наименьших элементов списка, которые необходимо отобразить;
  • значения – будут выбраны только те записи, которые в данном столбце содержат указанное значение;
  • условие – выбираются записи по формируемому пользователем условию в диалоговом окне "Пользовательский фильтр" (рис. 5.5 рис. 5.5).

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

Каждая часть условия включает:

  • оператор отношения: = (равно), <> (не равно), > (больше), >= (больше или равно), < (меньше), <= (меньше или равно), начинается с, содержит и т.п.;
  • значение, которое может выбираться из списка или содержать шаблонные символы *,?.

Пример. Для Кода предмета можно сформировать условия:

  • >=п* – отобрать все записи, которые содержат код предмета, начинающийся с буквы п;
  • >= п1 И <=п2 – отобрать все записи, которые содержат коды предметов п1 и п2;
  • <>п1 – отобрать все записи, которые не содержат кода предмета п1.
Диалоговое окно "Наложение условия по списку"

Рис. 5.4. Диалоговое окно "Наложение условия по списку"
Диалоговое окно "Пользовательский фильтр"

Рис. 5.5. Диалоговое окно "Пользовательский фильтр"

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

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

Команда Данные, Фильтр, Расширенный фильтр обеспечивает использование двух типов критериев для фильтрации записей списка:

  • критерий сравнения;
  • вычисляемый критерий.

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

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

Технология использования расширенного фильтра состоит из двух этапов:

  • этап 1 – формирование области критериев поиска;
  • этап 2 – фильтрация записей списка.

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

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

Критерий сравнения формируется при соблюдении следующих требований:

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

Правила формирования множественного критерия:

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

Пример1. Условие выбора записей о сдаче экзаменов студентами группы 133 по предмету п1 на оценки 4 или 5 можно записать несколькими способами:

1-й способ. Множественный критерий сравнения- все условия находятся в одной строке, связка И. Номер группы, код предмета заданы как точные значения, оценка- оператор сравнения со значением константы.

Номер группы Код предмета Оценка
133 п1 >3

2-й способ. Множественный критерий сравнения – все условия (точные значения полей) находятся в одной строке, столбец Оценка используется дважды, связка И.

Номер группы Код предмета Оценка Оценка
133 п1 4 5

3-й способ. Множественный критерий сравнения – условия (точные значения полей) записаны в двух строках, связка ИЛИ.

Номер группы Код предмета Оценка
133 п1 4
133 п1 5

Вычисляемый критерий представляет собой формулу, записанную в строке области условий, которая возвращает логическое значение ИСТИНА или ЛОЖЬ.

Формула строится с использованием: адресов ячеек, встроенных функций, констант различных типов (числа, текст, дата, логическая константа), операторов отношения.

Внимание! Имя столбца, содержащего формулу вычисляемого критерия, должно отличаться от имени столбца в списке.

Пример2. Выбрать записи о сдаче экзаменов студентами группы 133 с оценкой ниже общего среднего балла или записи с оценкой 5:

Номер группы Оценка1
133 =ИЛИ(G2<=CP3HAЧ($G$2:$G$I7);G2=5)

В области критериев присутствуют столбцы с заголовками: Номер группы, Оценка1.

Последовательность действий по созданию данного вычисляемого критерия:

  • присвоить отличное от имен полей списка новое имя столбцу, куда будет введен вычисляемый критерий;
  • установить курсор в ячейку ввода;
  • вызвать Мастер функций - команда Вставка, Функция, выбор категории - Логические и выбор функции - ИЛИ;
  • ввод параметров функции ИЛИ:

Логическое1: G2<=CP3HAЧ($G$2:$G$17) (при вводе формулы использовать курсорный указатель на ячейки таблицы, вызов встроенной функции СРЗНАЧ, указание на абсолютные ссылки с помощью клавиши <F4>)

Логическое2: G2=5

После завершения ввода вычисляемого критерия в ячейке должна появиться логическая константа ИСТИНА или ЛОЖЬ – результат применения сформированного вычисляемого критерия по отношению к первой записи списка; формулу критерия можно просмотреть лишь в строке формул. Этот же критерий можно было записать по-другому:

Номер группы Оценка1
133 =G2<=CP3HAЧ($G$2:$G$ 17)
133 =G2=5

либо в комбинированном виде:

Номер группы Оценка1 Оценка
133 =G2<=CPЗHAЧ($G$2:$G$17)
133 5

Этап 2. Фильтрация записей расширенным фильтром

После подготовки области критерия курсор устанавливается в список и выполняется команда Данные, Фильтр, Расширенный фильтр (рис. 5. рис. 5.6).

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

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

Диалоговое окно Расширенный фильтр

Рис. 5.6. Диалоговое окно Расширенный фильтр

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

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

Фильтрация с помощью формы данных

Excel позволяет работать с отдельными записями списка с помощью экранной формы (рис. 5.7 рис. 5.7).

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

Добавление кнопки "Форма" на панель быстрого доступа

Щелкните стрелку, расположенную рядом с панелью быстрого доступа, и выберите пункт Другие команды.

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

В списке выберите кнопку Форма Кнопка "Форма" и нажмите кнопку Добавить.

При установке курсора в область списка и выполнении команды Данные, Форма на экран выводится форма, в составе которой имена полей – названия столбцов списка.

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

Для создания новой записи нажимается кнопка <Добавить>, выполняется заполнение пустых полей экранной формы; для перехода между полями формы используются курсор мыши, либо клавиша <Таb>.

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

С помощью экранной формы задаются критерии сравнения. Для этого нажимается кнопка <Критерии>, форма очищается для ввода условий поиска в полях формы с помощью кнопки <0чистить>, а название кнопки <Kритерии> заменяется на название <Правка>. После ввода критериев сравнения нажимаются кнопки <Назад> или <Далее> для просмотра отфильтрованных записей в нужном направлении. При просмотре можно удалять и корректировать отфильтрованные записи списка. Для возврата к форме нажимается кнопка <Правка>, для выхода из формы – кнопка <3акрыть>.

Экранная форма для работы со списком записей

Рис. 5.7. Экранная форма для работы со списком записей

ЗАДАНИЕ

  1. Создать в Excel cписок согласно варианту задания (не менее 10 записей), в правом столбце ввести формулы, содержащие ссылки на ячейки для расчетов соответствующих показателей.
  2. Отсортировать данные списка по трём полям (поля выбираются самостоятельно).
  3. Выполнить фильтрацию данных списка тремя способами:
    • с помощью автофильтра,
    • с помощью расширенного фильтра по критерию сравнения,
    • с помощью расширенного фильтра по вычисляемому критерию, который задать самостоятельно, (см. пример 2).
Варианты задания (№ варианта – №компьютера в аудитории)

1 вариант.

Таблица 1. Учебники
Код Авторы Название учебника Город Издательство Год издания Коли-чество Цена учебника Общая стоимость
1 Макарова Н.В. Информатика Москва Финансы 2002 30 180
2 Выгодский В.Н. Высшая математика Киев Высшая школа 1989 22 190
3 Данилюк С.А. Философия Краснодар ABF 1998 32 230
4 Грызлов М.И. Информатика Киев Высшая школа 2003 40 220
5 Носов И.И. Общая химия Москва Финансы 2000 34 300
6 Сомов П.Н. Философия Краснодар ABF 1997 54 250
7 Кайшева А.И. Общая химия Москва Финансы 2004 38 290
8 Кириченко Г.С. Информатика Киев Высшая школа 2002 29 200
9 Вдовин О.Н. Философия Краснодар ABF 2001 20 240
10 Симонов П.А. Общая химия Москва Финансы 1998 15 310

2 вариант.

Таблица 2. Врачи
Код Ф.И.О. Специальность Должность Отделение Зарплата Налог Получаемая сумма
1 Панов Н.В. Отоляринголог Глав.врач Терапевтическое 6300 800
2 Петров В.Н. Стоматолог Зав.отделением Стоматологическое 4850 650
3 Громов П.А. Хирург Врач Хирургическое 3800 480
4 Тарасов Г.П. Стоматолог Глав.врач Стоматологическое 4500 850
5 Рогов О.Л. Хирург Врач Хирургическое 3970 600
6 Рокотов В.Е. Отоляринголог Врач Терапевтическое 4120 580
7 Колосов И.А. Отоляринголог Зав.отделением Терапевтическое 5500 700
8 Соколин И.Г. Стоматолог Глав.врач Стоматологическое 5230 900
9 Радионов В.П. Хирург Врач Хирургическое 4300 420
10 Попов Г.А. Стоматолог Врач Стоматологическое 4380 430

3 вариант.

Таблица 3. Больные
Код Ф.И.О. больного Болезнь № палаты Лечащий врач Дата поступления Доза Количество приемов Всего
1 Ванин Н.В. Сахар. диабет 6 Вавлова А.А. 26.03.2004 2 1
2 Петров В.Н. Перитонит 6 Селезнев П.П. 10.03.2004 1 3
3 Рогов А.Н. Аппендицит 12 Харатьян С.Г. 12.03.2004 1 2
4 Ломов Г.И. Перитонит 6 Селезнев П.П. 28.02.2004 1 3
5 Попов И.И. Перитонит 6 Селезнев П.П. 15.03.2004 3 3
6 Винник К.Ю. Аппендицит 12 Харатьян С.Г. 18.03.2004 1 2
7 Васин Г.С. Сахар. диабет 6 Вавлова А.А. 22.03.2004 4 1
8 Котов И.А. Аппендицит 12 Харатьян С.Г. 27.03.2004 2 2
9 Лосев К.Н. Сахар. диабет 6 Вавлова А.А. 25.03.2004 3 1
10 Попович Г.А. Перитонит 6 Селезнев П.П. 28.03.2004 4 3

4 вариант.

Таблица 4. Спортсмены
Код Ф.И.О. Вид спорта Дата рождения Тренер Разряд Премия Налог Получаемая сумма
1 Витаанен Н.В. Тяж.атлетика 06.12.1990 Власов А.А. Мастер 4000 800
2 Провский В.Н. Бокс 15.03.1998 Родин П.Э. ЗМС 4500 850
3 Васютин Н.Г. Таэквондо 24.04.1999 Логин Л.И. МСМК 3900 780
4 Петров А.Ю. Тяж.атлетика 30.07.2000 Васин А.П. ЗМС 5000 900
5 Фомин С.Ю. Таэквондо 24.06.1998 Суслов И.А. МСМК 3800 760
6 Конев Г.О. Бокс 17.01.1999 Воля Г.С. Мастер 4400 840
7 Носов В.И. Таэквондо 22.08.1997 Ким А.А. Мастер 3900 790
8 Генин И.А. Тяж.атлетика 23.09.2001 Перов Г.Н. ЗМС 5200 930
9 Симаков Р.Э. Бокс 29.05.2000 Миров Н.П. МСМК 4400 860
10 Гуревич Г.А. Бокс 12.10.1997 Второв В.Д. ЗМС 6000 1000

5 вариант.

Таблица 5. Маршруты
Код Водитель Время Марка автобуса Маршрут Дата Количество билетов Цена билета Общая сумма
1 Ветров Н.В. 6:40 "Икарус" Краснодар-Сочи 06.12.2004 42 120
2 Вронский В.Н. 10:15 "ЛиАЗ" Армавир-Ростов 07.12.2004 37 100
3 Васин Г.П. 11:20 "Мерседес" Краснодар-Крымск 25.11.2004 54 90
4 Власов С.П. 12:10 "ЛиАЗ" Гулькевичи-Сочи 17.12.2004 37 140
5 Громов С.П. 14:00 "Мерседес" Краснодар-Сочи 13.11.2004 53 120
6 Мохов А.П. 15:30 "ЛиАЗ" Краснодар-Крымск 28.12.2004 36 90
7 Жданов К.Ю. 17:15 "Икарус" Армавир-Ростов 18.11.2004 41 100
8 Нона О.Г. 20:20 "Икарус" Гулькевичи-Сочи 28.11.2004 40 140
9 Пирогов Ю.А. 19:40 "Мерседес" Краснодар-Крымск 19.11.2004 51 90
10 Гуров Г.А. 20:35 "ЛиАЗ" Гулькевичи-Сочи 12.10.2004 33 140

6 вариант.

Таблица 6. Рейсы
Борт № № рейса Аэропорт назначения Дата Время Тип самолета Количество пассажиров Цена билета Сумма
1 Ю-1138 Домодедово 06.12.04. 7:30 Як-42 65 990
2 Ю-1142 Внуково 15.03.04. 15:40 Ил-62 70 1000
3 Ю-1134 Домодедово 15.04.04. 12:30 Ту-154 98 1300
4 Ю-1123 Домодедово 23.08.04. 11:10 Ил-62 76 1100
5 Ю-1143 Домодедово 20.07.04. 17:40 Ту-154 120 1400
6 Ю-1132 Внуково 22.10.04. 18:50 Як-42 68 1200
7 Ю-1145 Внуково 23.06.04. 14:45 Як-42 56 980
8 Ю-1135 Домодедово 28.09.04. 13:40 Ту-154 121 1100
9 Ю-1140 Домодедово 12.08.04. 17:05 Ил-62 78 999
10 Ю-1136 Домодедово 12.10.04. 20:20 Ту-154 117 1300

7 вариант.

Таблица 7. Работники
Код Ф.И.О. Должность Стаж Профессия Зарплата Налог Получаемая сумма
1 Витаанен Н.В. Мастер 13 Токарь 8200 1500
2 Провский В.Н. Рабочий 4 Электрик 9650 1600
3 Власов Г.А. Нач. цеха 12 Механик 10200 2000
4 Чижов А.И. Мастер 6 Электрик 10000 1780
5 Рогов П.Е. Мастер 7 Механик 9960 1700
6 Городов С.К. Рабочий 11 Токарь 8700 1670
7 Фомин Ю.К. Нач. цеха 14 Механик 10120 1900
8 Мирский Г.О. Нач. цеха 3 Электрик 10190 1980
9 Ромов Г.С. Мастер 11 Токарь 9700 1610
10 Гуревич Г.А. Нач. цеха 14 Электрик 11000 2100

8 вариант.

Таблица 8. Штат
Код Ф.И.О. Должность Звание Уч.степень Дата рождения Зарплата Налог Получаемая сумма
1 Азаров Н.В. Декан Профессор д.т.н. 25.05.1949 15000 2500
2 Ржевский В.Н. Зам. Декана Доцент к.т.н. 20.02.1959 12300 2100
3 Власов П.С. Секретарь Инженер 22.10.1960 9800 1500
4 Гребнев А.А. Зам. Декана Доцент к.т.н. 25.07.1958 11500 2000
5 Симонов Г.Н. Секретарь Инженер 30.12.1963 10000 1900
6 Ломов И.П. Секретарь Инженер 12.02.1961 10100 1920
7 Мохов В.К. Декан Профессор д.т.н. 17.08.1965 14800 2460
8 Хомяков Н.В. Зам. Декана Доцент к.т.н. 19.01.1959 12000 1999
9 Маслов П.О. Зам. Декана Доцент к.т.н. 12.03.1960 12300 2010
10 Кудасова Г.А. Секретарь Инженер -– 22.03.1980 10400 1960

9 вариант.

Таблица 9. Команда
Код Ф.И.О. Специализация Дата рождения Тренер Разряд Зарплата Налог Получаемая сумма
1 Витин Н.В. Вратарь 06.12.1990 Власов А.А. Мастер 15000 2500
2 Провский В.Н. Нападающий 15.03.1990 Родин П.Э. ЗМС 17900 3100
3 Ломо Г.А. Полузащитник 12.07.1987 Михеев М.А. МСМК 18000 3400
4 Силин О.П. Нападающий 14.09.1989 Сомов К.Ю. ЗМС 16850 2970
5 Нилов Е.А. Полузащитник 17.10.1990 Фомин А.И. Мастер 14900 2340
6 Лосев А.И. Вратарь 22.10.1987 Минин Г.К. Мастер 15100 2550
7 Середа И.И. Полузащитник 13.05.1989 Жоров Р.Э. ЗМС 17650 3080
8 Ремин А.В. Полузащитник 18.10.1990 Ромов А.А. ЗМС 17000 2990
9 Демин К.Ю. Нападающий 23.09.1987 Тамилин П.П. МСМК 18200 3470
10 Вуйкич Г.А. Вратарь 12.10.1988 Власов А.А. МСМК 15970 2750

10 вариант.

Таблица 10. Поезда
Код № поезда Дата отправления Время отправления Маршрут Вагон Стоимость билета Продано билетов Всего
1 39 06.12.2004 23:40 Москва-Курск П 520 120
2 140 15.03.2004 15:30 Москва-Сочи СВ 1080 290
3 54 24.05.2004 16:10 Москва-Харьков К 775 300
4 62 12.10.2004 13:15 Москва-Харьков СВ 1200 220
5 139 28.09.2004 21:15 Москва-Сочи СВ 1150 340
6 150 25.12.2004 20:35 Москва-Курск К 830 180
7 87 14.07.2004 12:45 Москва-Харьков К 850 250
8 90 19.07.2004 10:15 Москва-Сочи П 490 170
9 76 23.10.2004 11:40 Москва-Курск К 790 320
10 52 12.10.2004 6:20 Москва-Харьков СВ 1200 290

11 вариант.

Таблица 11. Телефоны
Код Ф.И.О.абонента Адрес № телефона Район Дата устан. Цена за мин Кол.мин сумма
1 Ванин Н.В. Красная 32-34 135-14-56 Централ. 26.03.2004 5 7
2 Петров В.Н. Калинина 78-1 135-23-36 Централ. 10.03.2004 4 12
3 Васин М.И Гимназическая 5-8 165-89-23 Централ. 12.05.2004 5 14
4 Машков П.И. Промышленная 2-5 124-56-89 Централ. 26.03.2004 5 5
5 Игнатов В.О. Левоневского 45-6 145-86-97 Централ. 14.12.2004 4 6
6 Тимофеев О.А. Гаврилова 45-8 186-54-78 Централ. 10.03.2004 3 2
7 Синицин М.И. Атарбекова 58-7 142-76-34 Прикуб. 12.05.2004 6 3
8 Уваров С.Е. Ковалева 10-12 156-12-13 Прикуб. 15.06.2004 5 6
9 Комаров Б.Ю. Воровского 85-88 135-94-52 Прикуб. 14.12.2004 4 12
10 Попович Г.А. Крымская 63-3 123-45-67 Прикуб. 22.03.2004 3 8

12 вариант.

Таблица 12. Спортсмены
Код Ф.И.О. Вид спорта Дата рожд. Разряд Тренер Зарпл. налог сумма
1 Таанен Н.В Легкая атлетика 06.12.1990 Мастер Власов А.А. 2456 200
2 Шровская В.Н Теннис 15.03.1998 ЗМС Родин П.Э. 5369 500
3 Вовочкин М.Т. Бокс 12.03.1998 1 Булгаков С.Е. 4400 400
4 Понькин С.Л. Таэквондо 02.03.1986 МСМК Шункевич С.Б. 8900 800
5 Яцкевич П.О. Плавание 06.12.1995 2 Ветров А.Д. 9600 900
6 Гуденко Л.С. Теннис 16.05.1998 ЗМС Тимофеев С.Б. 7896 700
7 Максименко П.Л. Плавание 01.12.1984 1 Уваров П.О. 5469 550
8 Егоров К.Ю. Легкая атлетика 02.11.1994 Мастер Кабылкин П.Д. 15230 1100
9 Синкевич А.Д. Бокс 23.08.1992 2 Вербойд Ф. З. 14802 900
10 Гуревич Г.А. Таэквондо 12.10.1997 МСМК Второв В.Д. 5600 800

13 вариант.

Таблица 13. Автобусы
Код Водитель Маршрут Дата Время Марка автобуса Кол-во пассаж. Цена бил. сумма
1 Петров Н.В. Ростов-Сочи 06.12.2004 6:40 "Икарус" 40 1400
2 Троян В.Н. Киев-Ростов 07.12.2004 10:15 "ЛиАЗ" 35 1600
3 Макушкин Р.Н. Ростов-Москва 03.12.2004 15:10 "Икарус" 32 1700
4 Капустин Р.Д. Тихорецк-Армавир 06.12.2004 14:32 "Мерседес" 42 1650
5 Морковкин Г.С. Краснодар-Анапа 07.12.2004 15:38 "Пазик" 33 2000
6 Фролов О.Ч. Сочи-Майкоп 05.янв 20:00 "ЛиАЗ" 42 1520
7 Воронин Р.Ж. Сочи-Белореченск 06.12.2004 21:07 "Газель" 41 1220
8 Синяк А.Д. Ростов-Москва 03.12.2004 17:02 "Мерседес" 23 1900
9 Куропаткин Г.Е. Киев-Ростов 12.10.2004 16:02 "Пазик" 26 1600
10 Дуров Г.А Ростов-Сочи 12.10.2004 20:35 "Газель" 31 1000

14 вариант.

Таблица 14. Аэропорт
Борт № № рейса Аэропорт назначения Дата Время Тип самолета Кол.пассаж. Цена.билета сумма
1 Ю-1138 Шереметьево 06.12.2004 7:30 Як-42 200 1100
2 Ю-1142 Внуково 06.12.2004 15:40 Ил-62 100 12000
3 Ю-2101 Краснодар 12.10.2004 12:10 ТУ-134 60 4000
4 Ю-1105 Шереметьево 25.02.2004 15:40 Боинг 50 5000
5 Ю-1145 Новый Уренгой 06.07.2005 13:45 Ил-62 80 12000
6 Ю-2100 Домодедово 12.11.2004 19:08 ТУ-134 150 3000
7 Ю-2213 Краснодар 25.02.2004 14:23 ТУ-154 123 9000
8 Ю-1124 Внуково 06.07.2005 23:05 Ан-32 130 15000
9 Ю-2116 Новый Уренгой 19.02.2004 11:16 Боинг 140 7000
10 Ю-1136 Домодедово 12.10.2004 20:20 ТУ-154 105 5200

15 вариант.

Таблица 15. Цех
Код Ф.И.О. Должность Профессия Зарплата,руб. Стаж лет налог сумма
1 Виталин Н.В. Мастер Токарь 8 200 13 800
2 Прованский В.Н. Рабочий Электрик 9 650 4 900
3 Потапович К.А. Зам. начальника Механик 10 800 5 1000
4 Сонькин Л.В. Мастер Инженер 5 600 10 500
5 Левич. В.С. Рабочий Токарь 3 430 7 300
6 Коньков П.Р. Начальник цеха Экономист 17 520 8 700
7 Раневский А.О. Секретарь Инженер 6 280 3 600
8 Наумов Д.И. Зам.начальника Электрик 11 690 11 200
9 Твардов Н.П. Секретарь Экономист 7 230 6 300
10 Пуревич Г.А. Начальник цеха Механик 16 800 14 400

16 вариант.

Таблица 16. Кафедра
Код Ф.И.О. Должность Звание Уч.степень Дата рождения зарплата налог сумма
1 Назаров В.Н. Декан Профессор Д.т.н. 25.05.1949 15000 900
2 Ряжевский Н.В. Зам. Декана Доцент К.т.н. 20.02.1959 13000 700
3 Мищук В.С. Декан Инженер Д.т.н. 12.08.1965 16000 600
4 Иракин Л.А. Секретарь Профессор К.т.н. 12.08.1981 6000 400
5 Полтавский П.Т. Зам. Декана Инженер Д.т.н. 12.05.1974 7000 700
6 Окензи Ю. В. Ректор Доцент К.т.н. 04.11.1953 20000 950
7 Далнов Б.М. Секретарь Профессор Д.т.н. 08.12.1980 5000 300
8 Выц А.С. Декан Доцент К.т.н. 19.05.1965 14000 400
9 Сандрович Р.У. Ректор Профессор К.т.н. 17.03.1950 19000 900
10 Кудасова Г.А. Секретарь Инженер Д.т.н. 22.03.1980 5500 100

17 вариант.

Таблица 17. Футболисты
Код Ф.И.О. Специализация Дата рожд. Разряд Тренер зарплата налог сумма
1 Ватинин Н.В. Вратарь 06.12.1990 Мастер Власов А.А. 4500 100
2 Проворов В.Н. Нападающий 15.03.1998 ЗМС Родин П.Э. 5500 200
3 Роктович Г.О. Вратарь 28.02.1992 Мастер Роэлти П.О. 5000 150
4 Дикин Т.Н. Нападающий 15.09.1993 ЗМС Эдов Э.Э. 4000 320
5 Остапов Д.С. Полузащитник 17.12.1994 МСМК Польман Р.Л. 3900 450
6 Колноев М.В. Нападающий 26.11.1992 КМС Бобров А.Н. 6000 800
7 Букин В.К. Нападающий 13.10.1997 Мастер Випорг Ц.С. 6500 650
8 Роктович Ф.Н. Полузащитник 07.06.1998 КМС Актенович С.Т. 5200 350
9 Зуев А.Г. Вратарь 03.08.1991 ЗМС Жамов Г.Р. 4200 120
10 Зуйкович Г.А. Полузащитник 12.10.1997 МСМК Власов А.А. 7200 530

18 вариант.

Таблица 18. Железная дорога
Код № поезда Маршрут Время отправления Дата отправления Вагон,место Стоим.билета Кол.пассаж Сумма
1 139 Москва-Курская 23:40 06.12.04. П 1 080р. 220
2 140 Москва-Павелец 15:30 15.03.2004 СВ 1 080р. 300
3 154 Москва-Тюмень 14:23 12.10.2004 П 890р. 150
4 14 Ростов-Нальчик 13:58 15.06.2004 К 450р. 210
5 78 Краснодар-Москва 2:00 06.12.2004 П 1 500р. 321
6 162 Ростов-Нальчик 17:56 15.03.2004 СВ 360р. 420
7 46 Москва-Курская 6:00 05.03.2004 П 600р. 254
8 89 Владивосток-Норильск 19:06 15.06.2004 П 2 000р. 684
9 53 Краснодар-Москва 22:05 05.03.2004 К 2 500р. 354
10 52 Москва-Курская 6:12 12.10.2004 К 775р. 250

19 вариант.

Таблица 19. Военкомат
Код Ф.И.О. Адрес № телефона Звание Род войск Зарплата Налог Сумма
1 Ванин Н.В. Красная 32-34 135-14-56 Полковник Авиация 15000 100
2 Петров В.Н. Калинина 78-1 135-23-36 Полковник Артиллерия 14000 500
3 Баранов И.М. Горького 22-3 154-23-01 Майор Авиация 13500 600
4 Ленков С.П. Герцена 15-65 156-55-28 Подполковник Авиация 16300 250
5 Жуков П.Р. Длинная 1-96 148-56-92 Полковник Артиллерия 17800 320
6 Роялц Г.Т. Нефтяников 12-78 166-48-27 Майор Минометн 13200 420
7 Дымков О.И. Красная 65-1 132-54-87 Лейтенант Артиллерия 14620 600
8 Энокли А.К. Мира 78-56 128-57-43 Подполковник Минометн 14560 530
9 Молиев Р.В. Орджоникидзе 19-35 134-72-91 Лейтенант Авиация 12450 420
10 Попович Г.А. Крымская 63-3 123-45-67 Майор Минометн 11200 180

20 вариант.

Таблица 20. Салон
Код Ф.И.О.мастера Специальность Дата рождения Разряд № зала зарплата налог сумма
1 Ртищева Н.В. Макияж 06.12.1190 1 3 4000 100
2 Перовская В.Н. Парикмахер 15.03.1998 1 2 5200 500
3 Рогин В.А. Педикюр 15.02.1995 2 5 3000 600
4 Ломатин В.В. Педикюр 14.05.1990 3 2 3600 400
5 Понаморев А.А. Макияж 17.09.1998 2 6 5200 150
6 Доктерев Д.И. Парикмахер 16.03.1992 1 3 3500 220
7 Живник О.Г. Маникюр 17.08.1996 2 6 3100 380
8 Шонекин Д.П. Педикюр 25.06.1997 1 5 4500 460
9 Восточный К.Е. Парикмахер 27.12.1991 3 2 6200 520
10 Гуревич В.А. Маникюр 12.10.1997 3 2 3450 100

КОНТРОЛЬНЫЕ ВОПРОСЫ

  1. Что называют списками (базами данных) Excel?
  2. Назовите структурные элементы списка Excel.
  3. Какие основные правила необходимо соблюдать при создании списка?
  4. Как исключить заголовки полей из области сортировки?
  5. Какие уровни сортировки предусмотрены в программе Excel, и как осуществляется сортировка данных в списке?
  6. Что такое множественный критерий, назовите правила формирования множественного критерия?
  7. Как формируется диапазон условий для расширенного фильтра?
  8. Объясните, что понимают под вычисляемым критерием, и какие основные правила используются при формировании вычисляемого критерия?
  9. Как с помощью экранной формы задаются критерии сравнения при отборе данных?
< Лекция 4 || Лекция 5 || Лекция 6 >
Арсен Никифоров
Арсен Никифоров

Есть такие задания, и они никак не принимаются. Притом ошибки только по этим заданиям, в какой бы последовательности я их не заполнял. Как их заполнять??? Инструкций в заданиях нет. Там через запятые, подряд как число, через пробел, или надо текст весь писать через запятую или точку?

Задание: Пронумеруйте шаги Создание имени путем выделения ячеек на листе:
​1) На вкладке Формулы в группе Присвоенные имена выберите команду Создать из выделенного.
2) В диалоговом окне Создание имен из выделенного диапазона укажите место, содержащее заголовки.
3) Выберите диапазон, которому нужно присвоить имя.