Цель работы:

· Изучить возможности для организации данных в Excel в виде списка или базы данных.

· Освоить технологию обработки cписков в Excel.

· Научиться извлекать определенные записи и поля из баз данных.

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

Для учета данных о сотрудниках на предприятиях используют самые разнообразные методы. В одних организациях существуют журналы учета, куда информация вносится вручную, в других применяются классические базы данных для учета кадров, в третьих используются СУБД Access. Но в большинстве случаев на небольших предприятиях учет данных о сотрудниках ведется в электронных таблицах Microsoft Excel.

Приложение Microsoft Excel обладает богатыми встроенными средствами для обработки и анализа данных. Аналогом простой базы данных в Excel служит список.

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

Рис.5.1. База данных. Пищевая фабрика.

Если провести аналогию между списком и табличной базой данных, то столбцы списка являются полями базы данных, а его строки - записями. Считается, что первая строка списка является его заголовком и содержит названия столбцов списка. Заголовок должен иметь на листе электронных таблиц горизонтальную ориентацию. Заголовки применяются Excel при составлении отчетов, а также при поиске и организации данных. Шрифт, размер шрифта, выравнивание и другие параметры форматирования, присвоенные заголовкам столбцов списка, должны отличаться от параметров, назначенных для строк данных. В списке не должно быть пустых строк и столбцов.

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

1. Выделить ячейки столбца, для которого устанавливается проверка ввода.

2. На ленте Данные в группе Работа с данными выбрать команду Проверка данных.

3. На вкладке Параметры в области Условие проверки выбрать Тип данных: Любое значение (используется для отмены проверки ввода), Целое число, Действительное, Список, Дата, Время, Длина текста и Другой (формат, для которого можно задать собственную формулу, например,"м"or"ж"). При выборе значения внизу окна появляются дополнительные поля для ввода условий или ограничений – например, минимального и максимального допустимого значения.

4. На вкладке Сообщение для ввода можно установить флажок Отображать подсказку, если ячейка является текущей и ввести сообщение, чтобы оно появлялось на экране при выделении ячеек.

5. На вкладке Сообщение об ошибке можно установить флажок Выводить сообщение об ошибке, чтобы задать тип сообщения об ошибке, которое появится при вводе в ячейку недопустимого значения.

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

Чтобы отсортировать список надо:

1. Установить курсор в ячейку списка.
2. Выполнить команду Сортировка на ленте Данные в группе Сортировка и Фильтр.

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

Выбор настраиваемого порядка позволяет задать нестандартный порядок сортировки. Для этого надо в диалоговом окне Списки выбрать НОВЫЙ СПИСОК, в поле Элементы списка ввести значения, образующие пользовательский порядок сортировки, после чего последовательно выбрать кнопки Добавить и ОК (рис.5.2.).

Рис. 5.2. Создать свой список сортировки.

Промежуточные итоги в БД. Для организации списков используют команду Промежуточные итоги на ленте Данные в группе Структура, которая позволяет:

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

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

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

Рис. 3. Просмотр списка в режиме структуры.

Кнопки, расположенные в верхнем левом углу, определяют количество выводимых уровней данных. Кнопки со значками "+" и "-" предназначены для свертывания \ развертывания отельных групп.

Чтобы удалить промежуточный и окончательные итоги, надо повторно выполнить команду Промежуточные итоги, а затем щелкнуть по кнопке Убрать все.

Автофильтр. Отфильтровать список - значит показать только те записи, которые удовлетворяют заданному критерию.Чтобы установить или убрать автофильтр надо на ленте Данные в группе Сортировка и фильтр выбрать команду Фильтр. После этого нажать кнопку со стрелкой возле названия какого-либо поля, чтобы раскрыть список его элементов и выбрать отображаемые значения или задать условие отбора. На экране появятся только те записи, которые отвечают заданному условию. В случае необходимости можно продолжить фильтрацию, нажимая кнопки со стрелками на других полях.

Показать все записи по отфильтрованному полю, не убирая фильтр, можно выбрав в списке фильтра критерий Снять фильтр с….

Показать все записи по всем полям, не убирая фильтр, команда Очистить.

Для данных разного типа существуют дополнительные автофильтры, которые находятся в списке критериев Текстовые фильтры, Числовые фильтры, Фильтры по дате и т.д.

Если выделить какое-то числовое поле (например, Цена), а в списке критериев выбрать Числовые фильтры, то появится список дополнительных фильтров (рис.5.4), которые позволяют:

· задать критерий в виде неравенства – критерии равно, не равно, больше, больше или равно, меньше, меньше или равно, между;

· вывести первые N значений – критерий Первые 10: после выбора в списке Числовых фильтров команду Первые 10…, необходимо в появившемся окне указать число значений (N), а также способ вычисления: количество элементов списка, % от количества элементов;

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

· самостоятельно задаваемый фильтр – критерий Настраиваемый фильтр.

Рис.5.4. Дополнительные числовые фильтры.

Настраиваемый фильтр позволяет задать критерии из одного или двух условий. Простое условие состоит: из имени поля (атрибута); варианта условия (равно, не равно, больше, меньше, больше или равно, меньше или равно; начинается с, не начинается с, заканчивается на или не заканчивается на; содержит, не содержит); слова или числа для сравнения. Сложное условие состоит из двух простых, соединенных союзами И или ИЛИ. При написании значений в условиях сравнения в фильтрах можно использовать подстановочные знаки (Таблица 5.1).

Таблица 5.1. Подстановочные знаки.

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

Задание 1. Создать базу данных (рис.5.5.).

Рис.5.5. База данных.

Задание 2. Выполнить задания по проверке данных (рис.5.6.).

Рис.5.6. Задания. Проверка ввода данных.

Задание 3. Создать базу данных о работе пищевой фабрики за один месяц в соответствии с таблицей 5.2.:

Таблица 5.2.

Задание4. Выполнить задания в соответствии с вариантом, выданным преподавателем (табл.5.3.).

Таблица 5.3.

Задания:

1. Подсчитать процент выполнения плана.

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

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

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

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

6. Подсчитать суммарную стоимость изготовленной продукции

7. Найти максимальное количество по плану.

8. Найти минимальную цену за единицу продукции.

9. Подсчитать средний процент выполнения плана.

10. Используя автофильтр вывести на экран сведения о цехах, процент выполнения плана в которых больше 100% и меньше 130%.

11. Используя автофильтр вывести на экран сведения о трех цехах, план выполнения которых наибольший.

12. Используя автофильтр вывести на экран сведения о двух цехах, значение выполнения плана которых было наименьшим.

13. Используя расширенный фильтр, выдать сведения о той продукции макаронного цеха, план производства которой был перевыполнен, а стоимость была меньше 100.

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

14. Используя расширенный фильтр, выдать сведения о той продукции консервного цеха, план производства которой был недовыполнен или цена продукции меньше 2.

(цех, продукция, процент выполнения плана, цена продукции)

15. Используя расширенный фильтр, выдать сведения о той продукции консервного цеха, план производства которой был недовыполнен или цена единицы продукции была больше 3.

(цех, продукция, процент выполнения плана, цена продукции)

16. Используя расширенный фильтр, выдать сведения о той продукции цехов, план производства которой был недовыполнен или цена единицы продукции была больше 2,5.

(цех, продукция, процент выполнения плана, цена продукции)

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

(цех, продукция, процент выполнения плана, фактическое количество)

18. Используя расширенный фильтр, выдать сведения о той продукции кондитерского цеха, план производства которой был перевыполнен или фактическое количество больше 150.

(цех, продукция, процент выполнения плана, фактическое количество)

19. Используя расширенный фильтр, выдать сведения о той продукции консервного цеха, план производства которой был перевыполнен или количество по плану больше 450.

(цех, продукция, процент выполнения плана, количество по плану)

20. Используя расширенный фильтр, выдать сведения о той продукции консервного цеха, план производства которой был недовыполнен или цена продукции меньше 2.

(цех, продукция, процент выполнения плана, цена продукции)

Задание 5. Создать таблицу 5.4.

Таблица 5.4.

Задание 6. Выполнить задания по сортировке значений таблицы 5.6.

1. Отсортировать по фамилии товароведа по возрастанию сортировать по, по наименованию товара по возрастанию затем по, по количеству товара по убыванию в последнюю очередь по

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

3. Отсортировать по номеру магазина по убыванию сортировать по, по фамилии товароведа по возрастанию затем по, по цене единицы товара по убыванию в последнюю очередь по.

4. Отсортировать по наименованию товара по убыванию сортировать по, по дате по возрастанию затем по, по общей цене товара по убыванию в последнюю очередь по.

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

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

7. Отсортировать по цене единицы товара по возрастанию, затем по фамилии товароведа по алфавиту в последнюю очередь по дате по убыванию.

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

1. Для каких целей применяются электронные таблицы?

2. Для чего необходимы базы данных?

3. Какие возможности предоставляет программа Microsoft Excel для работы с базами данных?

4. Что такое запись?

5. Что такое поле?

6. Что такое тип данных? Зачем необходимо указывать типы полей?

7. Данные каких типов могут быть записаны в ячейку?

8. Как установить проверку вводимых в список значений?

9. Как записываются абсолютные и относительные адреса ячеек?

10. Что такое сортировка? Как отсортировать список по двум и более ключам?

11. Что такое фильтр?

12. Какие виды фильтров вы знаете?

13. В чем отличие сортировки списка от фильтрации списка?

14. Что такое расширенный фильтр?

15. Как сформировать критерий для расширенного фильтра?

Литература[1, 5, 6].

Поиск по сайту: