Добавление итогов в базу данных
Перед выполнением каждого следующего задания копируйте первоначальную таблицу БД на следующий лист книги.
Формирование БД
Довольно часто возникает необходимость хранить и обрабатывать данные представленные в виде таблиц.
Информация, хранящаяся в таблицах, организована в виде строк и столбцов. Каждая строка таблицы, называемая записью, содержит данные об одном объекте. В столбце, называемом полем, содержатся сведения о каком-либо свойстве всех объектов хранящихся в таблице.
Т.о., можно сказать, что База данных (БД)– это совокупность различных записей, обладающих определенными свойствами.
В первой строке любой базы данных обязательно должны быть указаны имена полей. Максимальный размер базы данных в 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 – Фрагмент отфильтрованной БД по полю Наименование товара, с значением Колбаса
Результат будет тем же, если из списка предложенных фильтров выбрать Условие…, и в открывшемся диалоговом окне, ввести в качестве условия "равно Колбаса".
Кроме того, диалоговое окно Пользовательский автофильтр, позволяет устанавливать различные условия фильтрации, выбрав их из предлагаемого списка.
Чтобы отменить Автофильтр и вывести на экран все данных таблицы из выпавшего списка выбрать – Все.
