Добавление итогов в базу данных

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

 

Формирование БД

 

Довольно часто возникает необходимость хранить и обрабатывать данные представленные в виде таблиц.

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

Т.о., можно сказать, что База данных (БД) – это совокупность различных записей, обладающих определенными свойствами.

В первой строке любой базы данных обязательно должны быть указаны имена полей. Максимальный размер базы данных в MS Excel определяется возможностями версии Excel (число строк и число столбцов в листе).

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

Задание 1. Сформировать БД на первом листе MS Excel, после чего лист переименовать, как показано на рисунке 1.

Рисунок 1 – База данных «Поставщики товара»

 

Сформируем поле Прибыль.

 

Прибыль = (Отпускная цена – Закупочная цена)*Количество –

– Транспортные расходы

Для этого в ячейку I2 введем формулу:

 

=(F2-E2)*H2-G2

 

С помощью маркера заполнения скопируем эту формулу в ячейки I2:I18.

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

Рисунок 2 – Формирование поля «Прибыль»

 

Сортировка баз данных

 

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

Для сортировки данных выполните команду Данные - Сортировка..., выберите столбец, по которому нужно отсортировать данные.

Задание 2. Отсортировать таблицу по полю Наименование товара – по возрастанию (рисунок 3).

Рисунок 3 – База данных «Поставщики товара» отсортированная по полю Наименование товара по возрастанию

 

Добавление итогов в базу данных

 

- Убедитесь, что данные для подсчета итогов представлены в формате списка: первая строка каждого столбца содержит подпись, остальные строки — однотипные данные, пустые строки или столбцы в списке отсутствуют.

- Укажите ячейку в столбце для итогов.

- Перед добавлением Итогов необходимо соответствующее поле отсортировать. Нажмите кнопку Сортировка по возрастанию или Сортировка по убыванию.

- В меню Данные выберите команду Итоги.

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

- В поле Операция выберите функцию для вычисления итогов.

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

- Чтобы за каждым итогом следовал разрыв страницы, установите флажок Конец страницы между группами.

- Чтобы итого отображались над строками данных, а не под ними, снимите флажок Итоги под данными.

- Нажмите кнопку OK.

Задание 3. Посчитать сумму прибыли по каждому товару суммарную прибыль при продаже всех продуктов. Переименовать Лист с итогами в Итоги.

Для суммирования прибыли по каждому из товаров сделаем следующее: отсортируем БД по наименованию товаров (это мы уже сделали – рисунок 3) и выполним команду Данные- Итоги. В появившемся диалоговом окне выберем:

При каждом изменении в: Наименование товара,

Операция: Сумма,

Добавить итоги по: Прибыль.

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

Рисунок 4 – Итоги по базе данных «Поставщики товара» - сумма прибыли по каждому товару суммарная прибыль при продаже всех продуктов

 

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

 

Процесс поиска и отбора информации в базе данных MS Excel называется фильтрацией. В MS Excel есть два вида фильтра: Автофильтр и Расширенный фильтр. Поиск будем осуществлять используя Автофильтр.

Для включения автофильтра необходимо:

1. Выделить необходимый диапазон данных базы данных.

2. Выполнить команду Данные → Фильтр → Автофильтр.

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

Задание 4. Используя автофильтр выведите на экран данные о Наименовании товара – Колбаса. Переименуйте лист с отфильтрованными данными – Автофильтр.

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

Рисунок 5- База данных после применения команды Автофильтр

 

Выберем в качестве условия в поле Наименование товара значение Колбаса.

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

Рисунок 6 – Фрагмент отфильтрованной БД по полю Наименование товара, с значением Колбаса

 

Результат будет тем же, если из списка предложенных фильтров выбрать Условие…, и в открывшемся диалоговом окне, ввести в качестве условия " равно Колбаса ".

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

 

Чтобы отменить Автофильтр и вывести на экран все данных таблицы из выпавшего списка выбрать – Все.