Работа с базами данных в excel задания

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

Тема: «Создание БД в MS Excel»

Цель: познакомиться с правилами оформления БД в MS Excel

Оборудование: АРМ.

Задание: В центральный банк г.NNN от филиалов по локально-вычислительной сети (ЛВС) поступают счета оплаты населением города коммунальных услуг. Поступающая информация фиксируется в ЭТ таблице Excel, состоящей из следующих столбцов (полей).

Сформировать в Excel таблицу поступлений счетов за коммунальные услуги от населения за 1 месяц

Учесть следующее:

• Даты поступления счетов от районов фиксируются в произвольном порядке, т.е. поздние даты могут быть впереди начальных дат месяца;
• Наименование районов   формируются в произвольном порядке;
• Категория услуг формируется последовательно (газ, свет, кв.м);
• Сумма-случайно распределенные числа в диапазоне,соответственно:

Требования:

1. Для выполнения задания потребуется 5 рабочих листов Excel.

На 1-ом листе оформляется в виде списка все сведения о поступивших счетах оплаты коммунальных услуг по районам города(рис.1).

На 2-ом листе сформировать таблицу отфильтрованную по категории оплаты за газ(рис.2)

На 3-м листе сформировать таблицу отфильтрованную по оплте за свет в первую десятидневку месяца с построением диаграммы(рис.3).

На 4-ом листе сформировать таблицу в соответствии с условием фильтрации списка по варианту самостоятельной работы.

1. Оглавление таблицы должно содержать: название города, месяца;
2. Указаны реквизиты банка (наименование банка, адрес, расчетный счет);

Порядок выполнения задания.

1. Создать новую книгу в Excel, дополнить ее необходимым количеством

рабочих листов для выполнения задания.

Рис. 1 База данных «Коммунальные платежи»

Выделить пять листов и заполнить оглавление, шапку таблицы Рис1.

Рис.2 Заполнение базы данных

3.в ячейки С10,С11,С12 ввести текст соответственно газ, свет, кв.м, выделить диапазон ячеек С10:С12, установить указатели мыши на квадратик в нмжнем правом углу (маркер заполнения) ячейки С12, пока не появится черный крестик и протянуть обрамляющую рамку вниз до ячейки С55.

4. В ячейку D10 ввести формулу =ОКРУГЛ(СЛУЧМЕЖДУ(1;10);2) и нажать клавишу; [Enter].

5.  В ячейку D11 ввести формулу =ОКРУГЛ(СЛУЧМЕЖДУ(10;25);2) и нажать клавишу; [Enter].

6. В ячейку D12 ввести формулу =ОКРУГЛ(СЛУЧМЕЖДУ(50;500);2) и нажать клавишу; [Enter].

7.Выделить  диапазон ячеек D10: D12 и установить указатель мыши на маркер заполнения, скопировать формулы до ячейки D55.

8. В ячейку E10 ввести формулу ОКРУГЛ(СЛУЧМЕЖДУ(1;12);2) и нажать клавишу [Enter].

9. заполнить формулами диапазон ячеек Е11:Е55 методом АВТОЗАПОЛНЕНИЯ.  В результате выполнения вычислений по формулам, значения результатов не будут совпадать с результатами на образце, т.к для расчетов применилась функция датчика случайных чисел.

10. В ячейку F10 ввести формулу =D10*E1/100 и нажать клавишу [Enter]. Заполнить этой формулой диапазон ячеек F11:F55.

11. В ячейку  G10 ввести формулу =D10+F10 и нажать клавишу [Enter]. заполнить этой формулой диапазон ячеек G11:G55.

12. Выделить числовые значения таблицы (диапазон D10:G55). Установить указатель мыши на выделенной области и нажать правую клавишу мыши. в появившемся контекстном меня выбрать команду Копировать. Выделить ячейку D10 и выбрать команду Правка=>Специальная вставка. В появившемся диалоговом окне Специальная вставка установить переключатель Значения, нажать кнопку ОК.

Этими действиями в скопированном блоке ячеек зафиксированы только числовые значения.

13. Выделить только первый лист.

14.переименовать лист 1, присвоить ему имя ПЛАТЕЖИ.

15. на втором листе произвести фильтрацию по полю Услуги. Для этого выделить ячейку С9 и выбрать команду данные=>фильтр=>Автофильтр. Установить  указатель мыши на появившемся квадратике с черным треугольником (список) и выбрать из появившегося списка-газ. В итоге, будет получен результат фильтра См рис2.

Рис 3

16.перейти на Лист 3 и произвести фильтрацию таблицы по полю Дата (условие…<11.05.00) и Услуги (Условие…=свет).

17. построить диаграмму для данных столбцов Дата и Всего отфильтрованной таблицы (Рис4)

Рис 4 Задание на Лист 3

18.Перейти на четвертый лист, отфильтровать  данные с помощью Расширенного фильтра и скопировать результат в другое место рабочего листа Excel. Расширенный фильтр распознает три специальные имени диапазонов: «База данных», «Критерии», «Извлечь» (рис.4.). Оформить область критериев по образцу (рис.4). критерий отбора записей формируется так:

Критерий 1-й строки –Извлечь из базы данных платежи за кв.м в 1-ю половину месяца, непревосходящие сумму (столбец всего )350

ИЛИ

Критерий 2-й строки— Извлечь  платежи по центральному району за свет.

Несколько критериев одной строки связаны логической функцией –И

Критерии на разных строках связаны логической функцией-ИЛИ

• Активизировать расширенный фильтр по схеме данные=>фильтр=>расширенные фильтр
• Заполнить поля окна расширенного фильтра по образцу:

Заполнять диапазоны базы данных критериев и извлеченных данных можно выделением. Для этого следует предварительно щелкнуть на красной стрелке поля, которая находится справа в полях ввода, а затем выделить мышью соответствующие диапазоны для  Базы данных, критериев, извлечь. В группе переключателей Обработка следует установить переключатель Скопировать результат в другое место.

20. На пятом листе выполнить самостоятельную работу .

Сформировать таблицу-результат фильтрации данных из исходной таблицы ПЛАТЕЖИ по условию варианта(1-15)и построить диаграмму типа обычная гистограмма результата для столбцов :

• Района и Всего( если фильтр по полю Всего, или по – Дата ,или по— Услуге ).
• Услуги и Всего (если фильтр по полю Всего, или по – Дата ,или по— Район).

Варианты фильтров для поля Всего :

1.   Счета для газа во 2-ю десятидневку месяца

1. Счета для света в 3-ю десятидневку месяца
2. Счета для света в 1-ю десятидневку месяца
3. Счета для кв м в  1-ю неделю  месяца
4. Счета для кв м в  3-ю неделю  месяца
5. Счета в диапазоне 50/100 тыс.руб.
6. Счета в диапазоне 15/50 тыс.руб.
7. Счета в диапазоне 250/350тыс.руб.
8. Счета в диапазоне 100/200 тыс.руб.
9. Счета в диапазоне 350/500 тыс.руб.
10. Счета Центрального района за свет
11. Счета Центрального района за газ
12. Счета Центрального района за кв.м
13. Счета Индустриального района в 1-ю половину месяца
14. Счета Торгового  района во  2-ю половину месяца.

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

По практической работе сделать отчёт, написать вывод.

Практическая работа «Создание базы
данных в Excel»

Цель работы: создание базы данных в Excel, которая будет содержать сведения о продажах в продуктовом магазине.

Для этого нам надо будет создать три таблицы: Прайс (в ней будут
храниться наименования товаров, их цена), Клиенты (в ней будут храниться
данные о клиентах), Продажи (в эту таблицу будут заноситься данные о
продажах с учетом сведений из первых двух таблиц). Так же будет создан лист Форма
ввода, с помощью которой можно будет заполнять таблицу Продажи, используя
уже введенные ранее данные в таблицы Прайс и Клиенты.

Методические
рекомендации по выполнению практической работы

Шаг 1. Исходные данные в виде таблиц.

Создаем таблицу Прайс:

1)       
Создаем в Excel новый лист с
названием Прайс.

2)       
Создаем три столбца: Наименование, Категория,
Цена. Заполняем 20 строк в созданной таблице по следующему образцу:

3)       
 

4)       
Превращаем созданную таблицу в «умную таблицу». Для
этого выделяем все заполненные ячейки, нажимаем Главная – Форматировать как
таблицу. Из выпавшего списка выбираем тот стиль оформления, который нам
понравился.

5)       
Далее идем на вкладку Работа с таблицами — Конструктор.
В окошке Имя таблицы меняем наименование на Прайс.

6)       
Создаем в Excel новый лист с
названием Клиенты.

7)       
Создаем два столбца: Клиент, Город.
Заполняем 20 строк в созданной таблице по следующему образцу:

       
Аналогично предыдущем листу превращаем созданную
таблицу в «умную таблицу». Для этого выделяем все заполненные ячейки, нажимаем Главная
– Форматировать как таблицу. Из выпавшего списка выбираем тот стиль
оформления, который нам понравился.

9)       
Далее идем на вкладку Работа с таблицами — Конструктор.
В окошке Имя таблицы меняем наименование на Клиенты.

10)    Создаем в Excel новый лист с названием Продажи.

11)   
Создаем пять столбцов: Дата, Товар, Кол-во,
Стоимость, Клиент. Не заполняем!!!

12)   
Аналогично предыдущем листу превращаем созданную
таблицу в «умную таблицу». Для этого выделяем все заполненные ячейки, нажимаем Главная
– Форматировать как таблицу. Из выпавшего списка выбираем тот стиль
оформления, который нам понравился.

Шаг 2. Создаем форму для ввода данных

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

Создаем Форму ввода:

1)   Создаем в Excel новый лист с названием Форма
ввода.

2)   Оформляем лист следующим образом:

3)   В ячейке Клиент найдем нужное значение из созданной ранее «умной
таблицы». Для этого выделяем ячейку, используем команду Данные – Проверка
данных:

4)   Откроется диалоговое окно:

5)   В поле Тип данных выбираем Список.

6)  
Поставить курсор в появившееся поле Источник
данных, перейти на лист Клиенты, выделить диапазон ячеек с фамилиями,
нажать ОК. После этого, в появившемся выпадающем списке выбираем любую
фамилию.

7)  
Аналогичным образом поступаем с ячейкой Товар.
В результате произведенных действий Форма ввода должна иметь следующий вид
(дата, количество заполняются вручную):

   Для того, чтобы подставить цену товара в форму ввода, необходимо
воспользоваться функцией ВПР, для этого в ячейке Цена введем
следующую формулу:

В
скобках первым в кавычках указывается наименование позиции, которая была
выбрана в поле Товар. После точки с запятой указывается наименование
таблицы, откуда будут подставляться значения (наименование Прайс мы задали в
Шаге 1, п. 5). Далее через точку с запятой идет номер столбца в таблице Прайс,
где содержится нужный нам параметр.

После
нажатия кнопки Enter нужная цена появится автоматически.

9)   В поле Стоимость вводим формулу, для вычисления стоимости
данного товара при выбранных цене и количестве.

Шаг 3. Добавляем макрос ввода продаж

1)     
После заполнения формы нужно введенные в нее данные
добавить в конец таблицы Продажи. Сформируем при помощи простых ссылок
строку для добавления прямо под формой (обратите внимание, ячейки формируются в
той последовательности, в какой они идут в таблице Продажи, т.е. в
ячейке A20 будет ссылка =B3, в ячейке B20 ссылка на =B7 и т.д.):

2)       
Теперь создадим макрос, который копирует созданную
строку и добавляет его в таблицу Продажи. Для этого нажимаем Разработчик
— Visual Basic. Если вкладку Разработчик
не видно, то включите ее сначала в настройках Файл — Параметры — Настройка
ленты. Поставить галочку напротив меню Разработчик:

3)       
После этого откроется окно Microsoft Visual Basic for Applications:

4)  
В открывшемся окне редактора Visual Basic вставляем
новый пустой модуль через меню Insert — Module и вводим туда код нашего
макроса (обратите внимание, названия листов должны полностью совпадать с
вашими):

Sub Add_Sell()

      Worksheets("Форма ввода").Range("A20:E20").Copy

      n
= Worksheets("Продажи").Range("A100000").End(xlUp).Row

      Worksheets("Продажи").Cells(n + 1, 1).PasteSpecial Paste:=xlPasteValues

      Worksheets("Форма ввода").Range("B5,B7,B9").ClearContents

End Sub

5)  
Закрываем окно редактора Visual Basic (никаких
сохранений это действие не потребует).

6)  
Теперь можно добавить к нашей форме кнопку для
запуска созданного макроса используя выпадающий список Вставить на
вкладке Разработчик:

7)  
После того, как вы ее нарисуете, удерживая нажатой
левую кнопку мыши, Excel сам спросит вас — какой именно макрос нужно на нее
назначить — выбираем наш макрос Add_Sell. Текст на кнопке можно поменять,
щелкнув по ней правой кнопкой мыши и выбрав команду Изменить текст.

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

9)  
Сохранять созданный файл нужно следующим образом: Файл
– Сохранить как – Тип файла: Книга Excel с
поддержкой макросов.

Цель
работы:
Научиться создавать базы данных в MS
Excel.
Изучить возможности работы с базами
данных.

Задание:

1. Оформление
   базы данных.

2. Создание
   формы.

3. Сортировка
   элементов базы данных.

4. Фильтрация
   данных.

5. Суммирование
   чисел в базе данных.

Пример
выполнения задания:

Создать
базу данных сотрудников предприятия,
заголовок которой имеет вид:

Заполнить базу
данных на 15 сотрудников.

1. Оформление
   базы данных производится так же, как и
   оформление любой таблицы в MS
   Excel.
   Только следует помнить, что таблица
   для базы данных не должна иметь пустых
   строк и пустых столбцов, а также не
   допускается объединение ячеек для
   данной таблицы.

В нашем случае
заголовок имеет вид:

Теперь
в ячейку A4
запишем:

=СТРОКА(А4)-3

и
растянем эту формулу до ячейки А18.

Далее
заполняем ячейки B4:F18
данными,
получим:

1. Рассмотрим
   работу с формой базы данных, для этого
   добавим при помощи формы еще двух
   сотрудников. Выполним: ДанныеФорма…

получим
диалоговое окно вида:

Теперь если мы
хотим добавить запись в базу данных, то
нажмем кнопку «Добавить» и получим:

Заметим,
что поле ввода номера не доступно, так
как там заложена формула. Мы заполняем
только доступные нам поля и получаем:

Заметим,
что для перехода из одного поля ввода
в другое необходимо нажимать клавишу
TAB.

Теперь для того,
чтобы добавить еще одного сотрудника,
снова нажмите кнопку «Добавить» и
введите данные этого сотрудника.

Для завершения
добавлений в базу данных необходимо
нажать клавишу «Закрыть». Произойдет
выход из режима «Форма», а также добавление
сотрудников в базу данных.

Обращаем
ваше внимание на то, что сотрудники
добавляются не по алфавиту, а в конце
списка.

В результате наша
база данных приобретет вид:

1. Теперь рассмотрим
   сортировку базы данных.

Сортировать
базу данных в MS
Excel
можно по столбцу любого типа в порядке
возрастания или убывания. Допускается
задание от одного до трех критериев
сортировки.

Установите курсор
в любую ячейку базы данных.
В меню
«Данные» выберите пункт «Сортировка…»:

Получим диалоговое
окно вида:

Теперь заполним
поле «Сортировать по». Для начала
отсортируем нашу базу данных по «Фамилиям»
в порядке возрастания, получим:

Обратите
внимание, что заданная нами формула для
«№» позволяет сохранять правильную
нумерацию независимо от сортировки.

Теперь
отсортируем нашу базу данных по двум
столбцам. Сначала отсортируем по
«Должности», а потом по «Коду». Для этого
в диалоговом окне сортировки наберем:

В результате
получим:

В
результате такой сортировки на первые
позиции встали все «Координаторы»,
причем между собой они отсортированы
по «Коду». В конце у нас «Менеджеры»,
которые тоже отсортированы между собой
по «Коду».

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

Для
извлечения информации из базы данных
по заданному критерию установите курсор
в любую ячейку базы данных и выполните:

ДанныеФильтрАвтофильтр

В результате вы
получите:

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

Теперь
с помощью фильтра оставим всех «Менеджеров»
с «Кодом» из диапазона от 3755 до 3760. Для
этого нажмем кнопку со стрелочкой в
столбце «Должность» и выберем «Менеджер»:

Далее нажмем кнопку
со стрелочкой в столбце «Код» и выберем
«Условие», в результате получим диалоговое
окно вида:

Заполнив
это окно так, как показано на рисунке,
получим:

Обратите
внимание на то, что вы не удаляли ничего,
а просто выбрали лишь те элементы,
которые вам необходимы. Стрелочки в
столбцах, по которым задавались критерии
для фильтрации, выделены цветом, так
что если вам необходимо отменить данную
фильтрацию, достаточно у выделенных
стрелочек выбрать раздел «Все».

Если вы хотите
снять фильтр, то выберите:

ДанныеФильтрАвтофильтр

Примечание.
Для того
чтобы не только извлечь, но и переместить
данные, выполните команду: Данные
ФильтрРасширенный фильтр…
В открывшемся диалоговом окне включите
опцию «Скопировать результат в другое
место» и укажите, куда копировать
результат.

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

Для
того чтобы изучить эту возможность,
добавим в нашу базу данных еще один
столбец «Зарплата» и отсортируем ее по
«Фамилиям», получим:

Установим
курсор в любую ячейку базы данных и
выполним:

ДанныеИтоги…

получим
диалоговое окно вида:

Укажем
подводить промежуточные итоги при
каждом изменении «Должности», операция
«Сумма», а итоги подводить только по
«Зарплате» и нажмем кнопку «ОК», получим:

Теперь
отменим итоги, для чего выполним
ДанныеИтоги…
и в появившемся диалоговом окне нажмем
кнопку «Убрать все». Отсортируем базу
данных по «Должности» и вновь установим
итоги по тому же принципу, что и ранее,
получим:

Для
того чтобы вывести только промежуточные
итоги, необходимо щелкнуть по кнопке
со знаком «-» (),
которая расположена в левой части окна.
В результате получим:

Оставшийся
минус в левой части окна позволяет вам
увидеть только итоговый результат.

Зачетное
задание:

Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]

• #
• #
• #
• #
• #
• #
• #
• #
• #
• #
• #

Урок №9

«Работа с таблицей Excel как с базой данных»

С помощью Microsoft Excel можно создавать и обрабатывать базы данных. База данных в Microsoft Excel – таблица, состоящая из однотипных записей (строк). Столбцы таблицы являются полями записи в базе данных. Под имена полей выделяется первая строка базы данных.

Список представляет собой электронную таблицу с большим объемом взаимосвязанной информации (список товаров на складах или список номеров телефонов и адресов абонентов).

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

К спискам в Excel предъявляются более строгие требования, чем к обычным электронным таблицам. Количество столбцов в списке должно быть постоянным, а количество строк переменным. Это позволяет добавлять, удалять или переставлять строки таблицы или записи списка (базы данных).

К средствам, которые предназначены для обработки и анализа данных в списке относятся команды из меню Данные: Сортировка, Фильтр, Форма, Итоги, Проверка. При выполнении этих команд, редактор автоматически распознает список как базу данных и осуществляет обработку и анализ данных в списке как в базе данных.

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

Для добавления новых записей в список, удаления и поиска существующих записей в списках применяется команда Форма. Для проверки данных при вводе используется средство, которое называется проверкой ввода (команда Проверка).

При создании списка необходимо выполнить определенные требования:

• чтобы редактор автоматически распознавал список как базу данных и обрабатывал данные при выполнении команд обработки необходимо на рабочем листе располагать один список;

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

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

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

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

Рассмотрим пример.

Если базой данных считать телефонный справочник, то полями записи будут: фамилия, имя, номер телефона абонента (рис.1).

Рисунок 1.

Существует ряд ограничений накладываемых на структуру базы данных:

1. первый ряд базы данных должен содержать уникальные (неповторяющиеся) имена полей;

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

3. таблицу следует отделить от других данных рабочего листа пустой строкой и столбцом;

4. информация по полям должна быть однородной, т.е. только числа или только текст.

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

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

Сортировка позволяет переупорядочить строки в таблице по любому полю. Например, по фамилии. Для сортировки данных следует выделить одну из ячеек таблицы и вызвать команду Сортировка меню Данные (рис. 2).

Рисунок 2.

В поле списка Сортировать по (рис.2) выбирается поле, по которому будут отсортированы данные, и тип сортировки:

При сортировке по возрастанию используется следующий порядок:

Числа сортируются от наименьшего отрицательного до наибольшего положительного числа.

При сортировке алфавитно-цифрового текста сравниваются значения по знакам слева направо. Например, если ячейка содержит текст «Дом100», она будет поставлена после ячейки, содержащей запись «Дом1», и перед ячейкой, содержащей запись «Дом11».

Текст, в том числе содержащий числа, сортируется в следующем порядке:

0 1 2 3 4 5 6 7 8 9 (пробел) ! » # $% ( ) *,. /:; ? @ [ ] ^ _ ` { | } ~ + A B C D E F G H I J K L M N O P Q R S T U V W X Y Z А Б В Г Д Е Ё Ж З И Й К Л М Н О П Р С Т У Ф Х Ц Ч Ш Щ Ъ Ы Ь Э Ю Я.

Апострофы (‘) и дефисы (-) игнорируются с единственным исключением: если две строки текста одинаковы, не считая дефиса, текст с дефисом ставится в конец.

Если предназначенный для сортировки столбец содержит как числа, так и числа с текстом (например, 1, 1 а, 2, 2 а), все они должны быть отформатированы как текст. В противном случае после сортировки первыми будут располагаться числа, а за ними — числа с текстом.

По убыванию – сортировка в обратном порядке.

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

Результат сортировки вы видите на рисунке 3.

Рисунок 3.

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

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

Обработка базы данных.

При просмотре, изменении, добавлении и удалении записи в базе данных, а также при поиске записей по определенному критерию удобно использовать формы данных. При обращении к команде Форма меню Данные Microsoft Excel считывает данные и создает диалоговое окно формы данных (рис.4).

Рисунок 4.

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

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

Имена столбцов становятся именами полей в форме данных. Поле соответствует каждому столбцу таблицы.

С помощью полосы прокрутки можно прокручивать записи базы данных.

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

Передвигаться по полям формы можно с помощью мыши и клавиш Tab (вниз), Shift+Tab (вверх).

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

Добавить – очищает поля для ввода новой записи базы данных. Если снова щелкнуть кнопку Добавить, то введенные данные будут добавлены, как новая запись, в конец базы данных.

Удалить – удаляет выведенную запись, другие записи базы данных сдвигаются. Удаленные записи не могут быть восстановлены.

Вернуть – восстанавливает отредактированные поля в выведенной записи, удаляя сделанные изменения. Чтобы восстановить запись, необходимо нажать Вернуть перед нажатием клавиши Enter или перед переходом к другой записи.

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

Далее – выводит следующую запись базы данных.

Критерии – очищает поля перед вводом критериев сравнения с операторами сравнения для поиска необходимого подмножества записей.

Правка – служит для выхода из режима ввода критериев. Доступна только после нажатия кнопки Критерии.

Очистить – удаляет существующий критерий из окна диалога. Доступна только после нажатия кнопки Критерии.

Закрыть – закрывает форму данных.

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

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

2. в меню Данные выбрать команду Форма;

3. щелкнуть кнопку Добавить;

4. заполнить поля новой записи;

5. для перемещения к следующему полю нажать клавишу Тab;

6. после ввода данных нажать клавишу Enter для добавления записи;

7. после добавления всех необходимых записей, щелкнуть кнопку Закрыть.

Новые записи будут добавлены в конец базы данных.

Поиск данных.

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

1. выделить ячейку в таблице;

2. в меню Данные выбрать команду Форма;

3. щелкнуть кнопку Критерии;

4. в полях редактирования ввести критерии для поиска данных, например, ввести Иванов в поле Фамилия;

5. для вывода на экран первой записи, отвечающей критерию, щелкнуть кнопку Далее;

6. для вывода на экран предыдущей записи, отвечающей критерию, щелкнуть кнопку Назад;

7. для поиска записей в списке по другим критериям щелкнуть кнопку Критерии и ввести новые критерии;

8. по окончанию щелкнуть кнопку Закрыть.

Команда Фильтр меню Данные позволяет отыскивать и использовать нужное подмножество данных в списке. В отфильтрованном списке выводятся на экран только те строки, которые содержат определенное значение или отвечают определенным критериям, при этом другие строки скрываются. Для фильтрации данных используются команды Автофильтр и Расширенный фильтр пункта Фильтр меню Данные.

Команда Автофильтр устанавливает кнопки скрытых списков (кнопки со стрелками) непосредственно в строку с именами столбцов (рис.5).

Рисунок 5.

С их помощью можно выбирать записи базы данных, которые следует вывести на экран. После выделения элемента в открывшемся списке, строки, не содержащие данный элемент, будут скрыты. Например, если в скрытом списке поля Фамилия выбрать Иванов, то будут выведены только записи, у которых в поле Фамилия содержится значение Иванов. (рис. 6)

Рисунок 6.

Если в поле списка выбрать пункт Условие … , то появится окно Пользовательский автофильтр (рис.7).

Рисунок 7.

В верхнем правом списке следует выбрать один из операторов (равно, больше, меньше и др.), в поле справа – выбрать одно из значений. В нижнем правом списке можно выбрать другой оператор, и в поле по левую сторону – значение. Когда включен переключатель И, то будут выводиться только записи, удовлетворяющие оба условия. При включенном переключателе ИЛИ будут выводиться записи, удовлетворяющие одному из условий.

Чтобы вывести все данные таблицы, необходимо вызвать команду Отобразить все или отменить команду Автофильтр меню Данные, подменю Фильтр.

Управление данными в 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. В пятой таблице подведите итоги: Какое количество видов товаров поступило на склад?

Лабораторная работа 7. 

 РАБОТА С БАЗАМИ ДАННЫХ В MICROSOFT EXCEL

Цель работы: освоить основные приемы работы
с базами данных в Microsoft Excel:

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

Пример

Задание. Создайте в Microsoft Excel таблицу,
как на рисунке 1.8. Рассчитайте стаж работы. Просмотрите данные о продавцах
женского пола, работающих на предприятии больше 4-х лет. Отсортируйте данные
таким образом, чтобы работники располагались по возрастанию разряда, а те, в
свою очередь, по алфавиту. Выведите на экран список сотрудников, проживающих в
Пятигорске. Сформируйте список сотрудников – женщин, проживающих в Пятигорске,
имеющих 3-ий разряд. 

Решение. Переименуйте лист. Для этого дважды
щелкните мышью по названию текущего рабочего листа. Название листа выделится.
Введите База данных – ОК.  В строке «2» наберите шапку таблицы как на
рисунке 1.8. 

Рис. 1.8. Сведения о сотрудниках

Установите в столбце Ф.И.О. формат ячейки текстовый
(выделите столбец – нажмите левой кнопкой мыши на обозначение столбца 
(например D), нажмите ФОРМАТ, выберите «ячейки» — текстовый), установите
формат ячейки – текстовый – для столбцов «должность», «адрес», «пол».
Для столбца «телефон» таким же образом установите тип «номер телефона» из
формата дополнительный. Для столбцов содержащих даты – установите формат
«дата», для столбцов №п/п, Разряд – числовой формат, 0 знаков
после запятой. 

Справа от столбца «стаж работы» вставьте новый столбец. Для
этого выделите столбец справа от «стажа работы», наведите на выделенную область
курсор, нажмите правую кнопку мыши и в МЕНЮ выберите ДОБАВИТЬ ЯЧЕЙКУ.
Появился новый пустой столбец. Введите название «стаж работы – округленный».

Выделите ячейку А1 и введите текст «текущая дата», в ячейку
рядом введите текущую дату, задайте формат ячейке «дата».

Рассчитайте стаж работы. Для этого в столбце «стаж работы»
введите формулу «(текущая дата — дата найма)/365». Установите ячейку «текущая
дата» в формуле — абсолютной (используйте знак $). «Протяните» формулу. В
столбце «стаж работы округленный» необходимо округлить полученный результат
так, чтобы учитывались только полные годы работы. Для этого выделите ячейку в
столбце «стаж работы округленный», вызовите список функций, в Математических
найдите формулу ОКРУГЛВНИЗ. Откройте окно формулы, в ячейку число
введите ту ячейку, число в которой надо округлить, количество цифр – 0,
ОК. Проделайте то же самое для всей таблицы.     

Поиск необходимых сведений в базе данных. 

Выделите всю таблицу. Задайте ей имя «Штат_сотрудников».
Для этого нажмите ВСТАВКА, ИМЯ, ПРИСВОИТЬ (рис. 1.9). Затем войдите в меню
ДАННЫЕ, выберите ФОРМА. Появится окно, отражающее аргументы созданной таблицы.
В появившемся окне просмотрите данные о продавцах женского пола работающих на
предприятии больше 4-х лет. Для этого нажмите кнопку КРИТЕРИИ и введите
в соответствующие ячейки заданные условия (в «стаж работы» — >4). Нажмите ДАЛЕЕ.
Просмотрите список полученных результатов отбора (рис. 1.10). Закройте окно.

Рис. 1.10. Просмотр
данных с помощью приложения  Microsoft Excel ФОРМА Для того, чтобы
расположить информацию в таблице в определенном порядке используйте команду СОРТИРОВКА.
Для этого выделите ячейку «Ф.И.О.», выберите в строке меню ДАННЫЕ,
СОРТИРОВКА, в появившемся окне установите «Сортировать по» — разряду,
«Затем по» Ф.И.О. – по возрастанию. Просмотрите отсортированные
данные.

При необходимости выделить из таблицы данные, отвечающие
определенному условию, воспользуйтесь командой Фильтрация. Для этого
активизируйте ячейку «Ф.И.О.». Выберите ДАННЫЕ, ФИЛЬТР, АВТОФИЛЬТР. В ячейках
с названиями столбцов появились стрелочки. Нажмите на такую стрелку в столбце Адрес.
В появившемся списке выберите УСЛОВИЕ, в окне Пользовательский
автофильтр введите Пятигорск (рис. 1.11). На экране появится список
работников проживающих в Пятигорске. Повторно нажмите стрелку в столбце Адрес,
выберите ВСЕ. Самостоятельно сформируйте список сотрудников принятых на
работу после 01.01.1999 года. Скопируйте полученный список сотрудников в нижнюю
часть страницы. Первоначальную таблицу верните к исходному виду.

Рис. 1.11. Использование приложения АВТОФИЛЬТР
для обработки данных

Если необходимо найти информацию, отвечающую двум и более
условиям, используйте команду Расширенный фильтр. Для этого скопируйте
шапку таблицы и вставьте ее в нижнюю свободную часть листа. В столбце Адрес
запишите условие Пятигорск, в столбце Разряд — 3, в столбце Пол —
жен. Затем в меню ДАННЫЕ выберите ФИЛЬТР — РАСШИРЕННЫЙ ФИЛЬТР,
в появившемся окне задайте аргументы: Исходный диапазон – диапазон
исходной таблицы, Диапазон условий – таблица с условиями, в ОБРАБОТКЕ
выберите Скопировать результат в другое место (рис. 1.12), в строке Поместить
результат в другое место укажите пустой диапазон ниже таблиц. Нажмите ОК. 

Рис. 1.12. Использование
приложения РАСШИРЕННЫЙ ФИЛЬТР для обработки данных Появилась таблица с
работниками, отвечающими заданным условиям.    

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

Какие способы существуют в программе Microsoft Excel для
просмотра и редактирования данных? 

В чем различие между приложением Microsoft Excel
АВТОФИЛЬТР И РАСШИРЕННЫЙ ФИЛЬТР?

Что необходимо сделать, прежде чем воспользоваться
РАСШИРЕННЫМ ФИЛЬТРОМ?

ЗАДАНИЯ ДЛЯ САМОСТОЯТЕЛЬНОЙ РАБОТЫ ПО
ЛАБОРАТОРНОЙ РАБОТЕ 7

Задача 1. Создайте таблицу, содержащую
следующие сведения о сотрудниках организации (см.: рисунок 1.13).

Отсортируйте записи по: 1). алфавиту фамилий, 2).
уменьшению разряда. Выдайте список сотрудников организации:

а). Проживающих в городе Пятигорске,

б). Чей телефон начинается на 34.

в). Проживающих в Пятигорске, старше 25 лет
на момент осуществления поиска

информации, принятых после 16.04.2000. 

Задача 2. Предприятие «Альфа» осуществляет
оптовую реализацию бытовой техники со складов. Имея сведения о количестве
проданной продукции в феврале, определите суммы выручки предприятия за месяц.
Создайте таблицу, отражающую реализацию кофеварок и миксеров за месяц. Создайте
таблицу, показывающую все поставки предприятия «Бета» ООО «Авангард» с 15.02.03
на сумму превышающую 2000000 руб. 

Рис. 1.14 Продажи предприятия «Альфа» за
февраль