Практическая работа "Управление данными в Excel"

Управление данными в Excel

База данных – это список заданной структуры, где:

столбцы – это поля – описывают категорию информации,

с

Например:

Телефонный справочник – это база данных, которая содержит следующие поля:

• ФИО,

• Адрес,

• Номер телефона.

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

При создании БД необходимо соблюдать два правила:

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

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

Практическая работа

1. Создайте простейшую базу данных (На первом листе новой книги. Книгу назовите БАЗЫ)

Существует два способа создания и заполнения БД:

• непосредственно заполняя ячейки таблицы Excel

• и
  ли с помощью окна формы данных (Данные  Форма). Информация заносится в каждое поле, нажимая Tab для перехода в следующее. После заполнения строки нужно нажать кнопку Добавить. Поле Стоимость не заполняется, т.к. оно расчетное.

В данной базе записи упорядочены по дате поступления, т.е. по полю Дата. Если необходимо упорядочить записи по другому полю, это значит необходимо выполнить СОРТИРОВКУ.

Сортировка – это упорядочивание записей в определенном порядке.

Для выполнения сортировки необходимо:

1. выделить всю БД,

2. Данные  Сортировка,

3. в диалоговом окне указать поле для сортировки,

4. ОК.

В телефонном справочнике данные отсортированы по фамилиям. Это называется первичной сортировкой. При совпадении фамилий необходима вторичная сортировка – по имени. Можно задавать до трех уровней сортировки.

1. Скопируйте базу на второй лист в двух экземплярах.

2. В первой выполните сортировку по полю откуда.

3. Во второй – по алфавиту вид товара, затем по возрастанию цена.

4. Первый лист назовите база, второй – сортировка.

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

Во многих случаях удобнее работать с подмножеством базы данных - огра­ниченной группой записей. Могут понадобиться сведения о всех поставках опре­деленного товара, о всех заказах определенного клиента, о всех заказчиках, которые ничего не купили в этом году.

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

В

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

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

1. О

   • Записи, удовлетворяющие условию остались, остальные скрылись.

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

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

2. Отфильтрованный список скопируйте на третий лист. Лист назовите Запросы.

3. Чтобы вернуть в список все записи, необходимо в раскрывающемся списке поля Откуда выбрать пункт Все.

4. Чтобы оставить в списке записи бумаги и ватмана необходимо в раскрывающемся списке поля Вид выбрать пункт Условие… и указать: в первой строке - равно - бумага, во второй строке - равно - ватман, установить переключатель или. Отфильтрованный список скопируйте на третий лист.

5. Чтобы оставить в списке три самых дорогих товара необходимо в раскрывающемся списке поля Цена выбрать пункт Первые 10… и указать три наибольших элемента списка. Отфильтрованный список скопируйте на третий лист.

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

7. Создайте список, в котором будут товары с ценой от 10 до 50 рублей.

8. Создайте список, в котором будут пять видов товаров самых малых по количеству партий .

Промежуточные итоги

Например, нужно узнать:

• Какое количество каждого товара было привезено на склад?

• На какую сумму каждого товара было привезено на склад?

• Какое количество товаров было привезено на склад из городов?

• На какую сумму в каждом месяце было привезено товаров на склад? и т.д.

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

Чтобы вычислить промежуточные итоги, необходимо:

1. Отсортировать БД по соответствующему полю (например, если нужны промежуточные итоги по виду товара, сначала необходимо отсортировать по полю Товар и т.д.)

2. Данные  Итоги…

3. О
   ткроется диалоговое окно «Промежуточные итоги».

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

5. Во второй строке - математическая операция.

6. В третьей - поля, по которым нужен итог.

7. О

   Структура БД при этом изменяется, слева появляется поле структуры промежуточных итогов.

   С помощью кнопок «+» и «-» можно разворачивать и сворачивать разделы.

   К.

8. Чтобы удалить промежуточные итоги необходимо в диалоговом окне «Промежуточные итоги» выбрать пункт Убрать все.

1. Скопируйте БД на четвертый лист в 5 экземплярах и каждое задание выполняйте в отдельной БД. Лист назовите Итоги.

2. Чтобы вычислить Какое количество каждого товара было привезено на склад, необходимо выполнить сортировку БД по полю Вид, в диалоговом окне «Промежуточные итоги» в первой строке необходимо выбрать поле Вид, во второй строке - Сумма, в третьей - установить флажок у поля Количество, ОК.

3. Во второй таблице подведите итоги: На какую сумму каждого товара было привезено на склад?

4. В третьей таблице подведите итоги: Какое количество товаров было привезено на склад из городов?

5. В четвертой таблице подведите итоги: На какую сумму в каждом месяце было привезено товаров на склад?

6. В пятой таблице подведите итоги: Какое количество видов товаров поступило на склад?