Работа с базами данных (списками) в Microsoft Excel

Когда данные становятся списками

В процессе обработки информации с помощью компьютера важное место занимают базы данных.

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

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

Данные в столбцах (полях) должны быть одного типа и каждое поле имеет свое уникальное имя.

Таким образом, база данных является множеством записей, состоящих из полей данных.

2. Создание структуры данных, ввод данных

Создание рабочего листа базы данных

В качестве примера построим базу, содержащую данные о продажах фирмы «Старая Пермь», осуществленных разными менеджерами за 1-ый квартал 2006 г., а затеи рассмотрим, как извлекать из нее нужную информацию и представлять ее графически.

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

1. Возьмите чистый рабочий лист Excel, назовите его Продажи.

2. Данные ячеек D2:E2 и данные столбца Сумма,руб являются расчетными, заполним их позже.

3. В ячейки А1, А2 введите текст и отформатируйте его, как показано на рис.1.

4. Значения курса у.е. наберите в отдельных ячейках С4:С6.

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

6. Заполните базу данных. Построчно поле за полем (Дата, Кому, Менеджер, Сумма,у.е) введите записи в таблицу. Надо иметь в виду, что при вводе повторяющихся данных Excel предлагает подстановку ранее введенных данных по первым символам.

Рис. 1. Рабочий лист Продажи

Вычисление суммы продаж в рублях

После заполнения всей таблицы заполним столбец Сумма,руб. с учетом приведенного курса у.е. Для этого используем стандартную функцию ExcelЕСЛИ из категории Логические.Формат функции:

ЕСЛИ (<условие>; <результат, если <условие>=True>;

<результат, если<условие>=False>)

Итак, заполняем столбец (поле) Сумма,руб.Для этого в ячейку E9 введите формулу:

Е9= ЕСЛИ (А9=”январь”;$С$4;ЕСЛИ (А9=”февраль”;$С$5;$С$6))*D9

и скопируйте ее вниз до конца таблицы. Установите «рублевый формат».

Вычисление общей суммы продаж

В ячейке D2 вычислите общую сумму продаж в у.е., для этого:

Ø выделите ячейку D2;

Ø нажмите кнопку Автосумма на панели инструментов;

Ø курсором мышки укажите данные столбца D;

Ø в строке формул сделайте поправку, указав достаточно большой диапазон ячеек, например D9:D100. Это делается для того, чтобы вы смогли в дальнейшем вносить новые записи в базу, общее количество которых заранее не известно;

Ø установите формат «у.е.»в ячейке D2.

Аналогичным образом в ячейке Е2 вычислите общую сумму продаж в рублях и установите «рублевый» формат.

Создание авто фильтра

Наиболее частой задачей при работе с базами данных является поиск нужной информации. Эта задача в Microsoft Excel решается с помощью Авто фильтра. Автофильтр позволяет вывести на экран строки, содержащие только определенную информацию.

Для созданияАвтофильтра выполните следующие действия:

Ø выделите ячейки А8:Е8, содержащие имена полей;

Ø выберите команду: меню Данные\Фильтр\Автофильтр;

Ø в таблице, в каждой из выделенных ячеек, появятся кнопки автофильтра (рис.2).

Нажав на соответствующую кнопку автофильтра можно выбрать «нужное значение» в появившемся списке возможных значений (рис. 2).

Можно, например, произвести фильтрацию полюбому из полей: Менеджер, Комуи т.д.

Рис.2. Автофильтр

Для отмены фильтрации нажмите кнопку автофильтра и выберите в открывающемся списке строку Все.