Тема 4. База данных в Excel 2007

План

1. Средства для работы с базой данных

2. Форма

3. Сортировка

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

5. Функции для автоматизации работы

6. Средсовт "Итоги"

7. Свдные таблицы

Специальные средства

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

Сортировка - упорядочение (сортировка) данных по одному или нескольким полям.

Фильтр - выборка (фильтрация) данных.

Промежуточные итоги - составление промежуточных отчётов.

Сводная таблица - создание сводных отчетов.

Таблица (база данных) должна соответствовать следующим условиям:

1. Все столбцы (поля) должны иметь заголовок.

2. Справочные данные размещают на отдельном рабочем листе

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

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

5. При выборе действия активная ячейка должна находиться в области базы данных.

Форма

1. Сформировать заглавную строку. В каждом столбце этой строки ввести название соответствующего поля записи.

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

- Щелкнуть на любой из ячеек заглавной строки, а затем на Панели быстрого доступа пиктограмму Форма.

- Щелкнуть на любой из ячеек заглавной строки, а затем на ПБД

В Excel 2007 нужно команду для ввода и работы с формой внести на панель быстрого доступа.

Офис - меню-Параметры Excel - Настройка - Все команды-Форма - Добавить- Ок.

Сортировка

Сортировка - это расстановка элементов в заданном порядке.

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

Имена ячеек и диапазонов.

- Выделит ячейку или диапазон ячеек, ввести её имя в поле "Имя" надать Enter (присвоить имя)

2 способ создания имени ячейки.

1. Выделить ячейку

2. Выполнить: Формулы --> Определеныые имена-->присвоить имя

3. Ввести в окне "Создание имени" в поле Имя ввести имя ячейки

4. Выбрать область применения имени ячейки в списке поля Область

5. Выбрать кнопку ОК

Правила присвоения имен блокам и ячейкам.

• первый символ имени блока - буква или символ подчеркивания

• запрещены пробелы и спецсимволы в имени блока

• максимальная длинна имени - 255 символов

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

• имя блока отличается от ссылок на ячейки рабочего листа

• в качестве имен могут использоваться одиночные буквы за исключением R и C

Поле со списком.

Выполнить команду Данные / Работа с данными/проверка данных

Тип данныъ /список

Источник - имя именованного блока, где находятся данные (F3)

(Значения списка разделенные знаком ";")

Для установления

Данные /сортировка и фильтр / фильтр

В этих списках находятся:

- команда Фильр по цвету

- команда Снять фильтр с:

- команда открытия меню команд для установки условий фильтрации

- Числовые фильтры (если в столбце числовые данные), или

Текстовые фильтры (если в столбце текстовые данные ), или ФИльтры по дате (если в столбце даты)

- флажок Выделить все

- флажки для значений в ячейках данного столбца.

Расширенный фильтр.

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

Для установки расширенного фильтра и выполнения фильтрации с таим фильтром необходимо:

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

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

3. выполнить Данные /Сортировка и фильтр/ дополнительно.

4. В окне Расширенный фильтр:

- Выбрать один из переключателей для выбора области расположения результата фильтрации

- ввести в поле Исходный диапазон адрес диапазона ячеек, данные в которых должны фильтроваться.

- Ввести в поле Диапазон условий адрес диапазона ячеек, в которых расположены скопированные назания столбцов и условия

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

- Выбрать ОК

Функции для автоматизации работы.

1. Функция ПРОСМОТР.

Просмотр возвращает значение из строки столбца или массива.

Функция ВПР

ВПР - поиск по ключевым значениям по столбцам.

=ВПР( искомое значение(ячейка); Заказчики; ложь)

Логические функции:

-ЕСЛИ

-ИЛИ

-И

=ЕСЛИ (A2>=70;"сдал"; "не сдал")

И- одновременное выполнение всех условий

ИЛИ - ыполнение хотя бы из одного из условий

ЕПУТСТО - проверяет, ссылается ли данная ячейка на пустую ячейку и возвращает значение ИСТИНА или ЛОЖЬ.

Функции для анализа даннх из БД:

ДСРЗНАЧ- усредняет значения в столбце списка или БЗ, удовлетворяющих заданному условию.

БСЧЕТ - Подсчитывает количество ячеек в столбце списка или БД, содержащим числа, удовлетворяющие заданным условиям.

БСЧЕТА - подсчитывает непустые ячейки в столбце списка или базы данных, которые удовлетворяют заданным условиям.

БИЗВЛЕЧЬ - извлекает отдельные значения из столбца, списка или БД, которые удовлетворяют заданным условиям.

ДМАКС - Возвращает наибольшее число в столбце списка или БД, которое удовлетворяет заданным условиям.

ДМИН - возвращает наименьшее число в столбце, списке или БД, которое удовлетворяет заданным условиям.

БДСУММ - суммирует числа в столбце списка или БД, которые удовлетворяют заданным условиям.

БДФункция (база_данных; поле; критерий)

База данных - это интервал ячеек, формирующих список или базу данных.

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

Критерий - это интервал ячеек, который содержит задаваемые условия.

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

Промежуточные итоги - это итоги не по всем данным, а по частям ( по указанным полям группировки )

Чтобы найти промежуточные итоги, нужно:

1. Отсортировать список по столбцам, для которых необходимо подвести итоги.

2. Указать ячейку в этом списке

3. Выполнить (Данные --> Структура --> Промежуточные итоги)

4. Выбрать столбец, содержащий группы, по которым необходимо подвести итоги, в поле При каждом изменении в. Это должен быть столбец, по которому проводилась сортировка списка на шаге 1.

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

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

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

Сводные таблицы могут использоваться:

1. для обобщения большого количества однотипных данных

2. реорганизации данных ( с помощью перетасиквания)

3. отбора и группировки

4. построения диаграмм

Источники данных для формирования сводной таблицы:

- список (база данных) EXCEL

- внешний источник данных в любом конвертируемом формате (текстовый файл, содержащий табличные данные, реляционная база данных)

- несколько диапазонов ячеек одного или нескольких рабочих листов одной или нескольких рабочих книг

- другая сводная таблица

Методика создания сводной таблицы:

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

2. Выполнить Вставка ---Таблицы --- Сводная таблица

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

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

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

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

2. Из списка реквизитов исключаем повторяющиеся, номера по порядку и групповые ( итоговые операции)

3.

1. TTH №

2. Дата

3. Грущоотправители

4. Грузополчатели

5. Адрес грузополучателей

6. Код продукции

7. Наименование продукции

8. Едлиница измерения

9. Цена

10. Количество

11. ИНН грузополучателя

12. Код грузополучателя

14. Телефон грузопол.

15. Расчетный счет грузополуч.

16. Адрес грузоотправителя

17. Тел. грущоотправителя

18. Расчетн. грузоотправителя

19. ИНН грущзоотправ.

20. Ставка НДС

21. Сумма

22. Стоимость продаж, включая НДС

23. Сумма НДС

Модель данных - плоская таблица.

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

Преимущества плоской таблицы

1. Простота и наглядность

2. Удобно хранить неструктурированые данные

3. Компактность

4. Возможность использования электронных таблиц