Работа с базами данных в ms excel презентация

Скачать материал

Скачать материал

Слайд 1

Работа с данными. Имена ячеек. Разработала Преподаватель ПУ – 197 Бондаренко Л.В.

Слайд 2

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

Слайд 3

Отбор данных

Слайд 4

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

Слайд 5

После выполнения этой команды таблица изменит вид — в ячейках с названиями полей появятся стрелки, при щелчке на которых открывается доступ к раскрывающемуся списку. В списке обозначены как постоянные критерии, так и зависящие от конкретного списка. Настраиваемый фильтр — открывает диалоговое окно Пользовательский автофильтр — указываются условия фильтрации, которых может быть два. С помощью переключателей И и ИЛИ определяют, должны соблюдаться оба условия или только одно из них. Данные, отсеянные в результате фильтрации, не удаляются, а скрываются.

Слайд 6

Пример : Отбор данных в списке

Слайд 7

Выделите одну из ячеек списка. Нажмите кнопку Фильтр в группе Сортировка и фильтр на вкладке Данные . При этом заголовки столбцов таблицы изменят вид.

Слайд 8

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

Слайд 9

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

Слайд 10

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

Слайд 11

СОРТИРОВКА ДАННЫХ

Слайд 12

Целью сортировки является упорядочение информации на основе выбранного критерия. Ее также используют, если данные набирались в таблицу постепенно и каждая следующая запись добавлялась в нижнюю строку. Для вызова окна Сортировка , которое предназначено для сортировки списка, выполните одно из следующих действий. Перейдите на вкладку Данные и нажмите кнопку Сортировка в группе Сортировка и фильтр . Перейдите на вкладку Главная , нажмите кнопку Сортировка и фильтр в группе Редактирование и выберите команду Настраиваемая сортировка в меню.

Слайд 13

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

Слайд 14

Excel упорядочивает данные в следующем порядке: числа — начиная с наименьшего отрицательного и заканчивая наибольшим положительным; текст — начиная с первой буквы алфавита и заканчивая последней; логические значения типа ЛОЖЬ ; логические значения типа ИСТИНА ; значения ошибок — они приводятся в том же порядке, что и до сортировки; пустые значения — после сортировки они всегда занимают место в конце списка, независимо от выбранного значения по возрастанию или по убыванию.

Слайд 15

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

Слайд 16

ПРИМЕР : СОРТИРОВКА ДАНЫХ В СПИСКЕ

Слайд 17

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

Слайд 18

Теперь используем параметры вторичной сортировки. Снова вызовите окно Сортировка с помощью одноименной кнопки на ленте. Нажмите кнопку Добавить уровень . В списке Затем по выберите поле Фамилия. Установите переключатель в положение от А до Я. Нажмите кнопку ОК.

Слайд 19

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

Слайд 20

ИМЕНА ЯЧЕЕК

Слайд 21

Назначая имена диапазонам ячеек, можно впоследствии вставлять их в формулы вместо заголовков столбцов и названий строк. При создании имен нужно учитывать следующее: имя не должно содержать пробелов; имя должно начинаться с буквы, символа подчеркивания (_) или обратного слеша (); имя не должно совпадать со ссылкой на какую-либо ячейку (например, S 4); имя не может быть обозначено буквами R и С.

Слайд 22

Для присвоения имени можно использовать один из способов: 1. Введите имя ячейки или диапазона в поле Имя 2. Нажмите кнопку Писвоить имя в группе Определение имени на вкладке Формулы. При выполнении этой команды откроется диалоговое окно Создание имени

Слайд 23

Чтобы присвоить имя формуле, выполните следующие действия. Нажмите кнопку Присвоить имя в группе Определенные имена на вкладке Формулы . В окне Создание имени наберите формулу в поле Диапазон . Введите имя формулы в поле Имя . Нажмите кнопку ОК , чтобы подтвердить присвоение имени формуле. Присвоение имен формулам

Слайд 24

Для использования имени формулы нажмите кнопку Использовать в формуле в группе Определенные имена на вкладке Формулы и выберите нужное имя в списке.

Слайд 25

ПРИМЕР : ПРИСВОЕНИЕ ИМЕН ЯЧЕЙКАМ

Слайд 26

1. Выделим строку, в которой записаны данные о товаре Помидоры . 2. Щелкнем на поле Имя и введем в него название диапазона – Помидоры . 3. Выделим строку, в которой записаны данные о товаре Огурцы . 4. Нажмем кнопку Присвоить имя в группе Определенные имена на вкладке Формулы . В окне Создание имени для выделенного диапазона будет предложено имя Огурцы . Нажмем кнопку ОК, для подтверждения .

Слайд 27

5. Снимем выделение и снова выделим диапазон Помидоры. Убедимся, что имя диапазона отражается в поле Имя . 6. Нажмем кнопку в виде стрелки и выберем диапазон Огурцы.

Слайд 28

Как использовать таблицы Excel в качестве баз данных? Как осуществлять поиск по базам данных? Как отбирать и сортировать данные? Для чего используются названия ячеек и диапазонов ячеек? Как присваивать имя ячейке или диапазону? ВОПРОСЫ:

1


Базы данных В EXCEL

2


Общие понятия Формы Сортировка данных Отбор данных Автофильтр Расширенный фильтр Подведение промежуточных итогов Подведение промежуточных итогов Сводные таблицы

3


Общие понятия Таблица в Еxcel, состоящая из строк одинаковой структуры и имеющая строку заголовков может использоваться и обрабатываться как база данных. При этом строки называются записями, а столбцы полями. Поля, однозначно характеризующие строку являются ключевыми.

4


Работа с формами Формы предназначены для ввода, редактирования и просмотра базы данных в удобном для пользователя виде Выделить список(таблицу) и выбрать: Данные — форма

5


Сортировка данных Выделить список(таблицу) или диапазон и выбрать: Данные — сортировка Сортировка – изменение последовательности записей (строк) в таблице по возрастанию или убыванию одного из полей, называемому ключом. Т.е. ключ сортировки – поле, по которому упорядочиваются записи в списке данных. Если поле ключа – текстовое, то данные упорядочиваются по алфавиту Если поле ключа – числовое, то данные в этом поле упорядочиваются по значению

6


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

7


Отбор данных ОТБОР — Выбор записей, удовлетворяющих заданным условиям. Существуют 2 способа отбора:

8


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

9


Условия отбора Для каждого поля может быть задано не более двух условий, выполняющихся одновременно (И), или дополняющих друг друга (ИЛИ)

10


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

11


Диапазон условий Состоит из 2-х или более строк Первая строка содержит имена полей для которых заданы условия отбора Вторая и последующие строки содержит условия для отбора Условия, находящиеся в одной строке выполняются одновременно для отобранных записей Каждая новая строка условий добавляет группу отобранных записей Например: такой диапазон позволяет найти поставки молока с доходом от 100 до 1000(тыс.руб)

12


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

13


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

14


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

15


Построение диаграмм на базе промежуточных итогов Выделить данные, необходимые для построения диаграмм (используйте клавишу CTRL для выделения несвязанного диапазона) Выбрать Вставка-диаграмма

16


Выбор типа диаграммы и ее параметров

17


Пример круговой диаграммы

18


Сводные таблицы Сводная таблица — это таблица, обобщающая и анализирующая данные из одной или нескольких таблиц. Исходные данные для сводной таблицы могут находиться в списке на одном листе рабочей книги, на нескольких листах, во внешней базе данных или в другой сводной таблице. Меняя структуру таблицы, можно получать различные сводные ведомости одних и тех же исходных таблиц.

19


Части сводной таблицы.

20


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

21


Мастер сводных таблиц. Выделить список(таблицу) или диапазон и выбрать: Данные – сводная таблица

22


Первый шаг Мастера сводных таблиц Выбор источника данных для создания сводных таблиц

23


Второй шаг Мастера сводных таблиц Указание диапазона данных

24


Третий шаг Мастера сводных таблиц Задание макета (структуры полей сводной таблицы) Выбор параметров Выбор размещения на отдельном или существующем листе

25


Задание макета (структуры полей сводной таблицы) Двойным щелчком мыши можно выбрать операцию для вычисления поля сводной таблицы

26


Выбор параметров сводной таблицы

27


Пример получившейся сводной таблицы

1. Работа с базами данных в MS Excel

Технологии баз данных

2. Темы лекции:

Списки. Основные понятия
Правила списков
Проверка ввода данных
Формы
Поиск данных и замена
Сортировка
Фильтрация
Итоги
Консолидация
Сводные таблицы. Сводные диаграммы
Стандартные функции работы с БД
26.09.2012
Технологии баз данных.
Пивоварчик В.А.
2

3. Списки в MS Excel

Заголовок
1
Данное 1
…
…
Заголовок
2
Данное 2
…
…
Заголовок
3
Данное 3
…
…
Заголовок
4
Данное 4
…
…
Заголовок
5
Данное 5
…
…
Совокупность всех записей базы данных называется телом списка.
Значения полей определенной строки называются записью.
Строка заголовков располагается возле верхней границы рабочего
листа и содержит имена полей списка.
Данное, находящееся в ячейке, называют значением поля определенной
записи.
26.09.2012
Технологии баз данных.
Пивоварчик В.А.
3

4. Правила списков

заголовки столбцов должны
располагаться в верхней строке списка;
рекомендуется использование только
одного списка на листе;
рекомендуется помещать подобные
объекты в один столбец;
отделять данные списка от
вспомогательных расчетов;
располагать важные дополнительные
данные сверху или снизу от списка;
26.09.2012
Технологии баз данных.
Пивоварчик В.А.
4

5. Правила списков

отображение всех строк и столбцов
списка;
использование форматированных
подписей столбцов;
использование границ ячеек;
отсутствие пустых строк и столбцов;
отсутствие начальных и конечных
пробелов.
26.09.2012
Технологии баз данных.
Пивоварчик В.А.
5

6. Проверка ввода данных

Для
предотвращения
ошибок в MS Excel
предусмотрены
встроенные
средства контроля
данных.
Операция
осуществляется
через меню
Данные команду
Проверка…
26.09.2012
Технологии баз данных.
Пивоварчик В.А.
6

7. Проверка ввода данных

Некорректный ввод может сопровождаться
предупреждениями об ошибке: Останов,
Предупреждение, Сообщение.
26.09.2012
Технологии баз данных.
Пивоварчик В.А.
7

8. Формы

С помощью Формы можно осуществлять
добавление в список новых данных или
удаление существующих, поиск записей по
заданному критерию и удалять записи.
Кнопка Критерии
позволяет на каждое
поле наложить критерий
26.09.2012
Технологии баз данных.
Пивоварчик В.А.
8

9. Поиск и замена данных

Операции поиска и
замены можно
осуществлять через
меню Правка
(команда Найти,
команда Заменить).
Эти команды могут
быть применены к
любым данным на
листе книги.
26.09.2012
Технологии баз данных.
Пивоварчик В.А.
9

10. Поиск и замена данных

шаблоны для
поиска: * и ?. Знак
* (звездочка)
подразумевает
несколько
символов; знак ?
(вопрос)
подразумевает
один символ.
26.09.2012
Технологии баз данных.
Пивоварчик В.А.
10

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

Сортировка данных в БД
предполагает
переупорядочивание данных
по алфавиту (по возрастанию)
или в обратном порядке.
Активный столбец является
критерием (ключом)
сортировки. При сортировке
БД заголовки не участвуют в
сортировке.
26.09.2012
Технологии баз данных.
Пивоварчик В.А.
11

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

Сортировка по возрастанию предполагает
следующий порядок:
1. числа от наименьшего отрицательного до
наибольшего положительного;
2. текст, включая текст с числами;
3. логические значения (ложь, затем истина);
4. значения ошибок;
5. пустые ячейки.
26.09.2012
Технологии баз данных.
Пивоварчик В.А.
12

13. Фильтрация

Существует два способа
фильтрации: автофильтр и
расширенный фильтр.
Отменить фильтрацию можно с
помощью команды
Данные/Фильтр/Отобразить
все.
26.09.2012
Технологии баз данных.
Пивоварчик В.А.
13

14. Фильтрация

При использовании автофильтра
критерии формируются автоматически
на основе значений в столбцах списка.
При использовании расширенного
фильтра критерии формируются
пользователем.
26.09.2012
Технологии баз данных.
Пивоварчик В.А.
14

15. Фильтрация

При автофильтрации ее результаты
отражаются непосредственно в
исходной таблице.
При использовании расширенного
фильтра результаты фильтрации можно
отобразить отдельно от исходного
списка.
26.09.2012
Технологии баз данных.
Пивоварчик В.А.
15

16. Итоги

Для подведения итогов используется команда
Данные/Итоги.
С помощью кнопки Убрать все можно
отменить ранее выполненную операцию
подведения итогов.
26.09.2012
Технологии баз данных.
Пивоварчик В.А.
16

17. Итоги

При подведении итогов список
предварительно требуется
отсортировать по некоторому
критерию. После этого подводить
итоги для данных, которые были
критерием сортировки.
26.09.2012
Технологии баз данных.
Пивоварчик В.А.
17

18. Итоги

Результатом подведения итогов является
промежуточные и общие итоги, параллельно
с которыми формируется область
структуры слева от заголовков строк. С
помощью кнопок структура данных может
быть отражена в свернутом и развернутом
виде.
26.09.2012
Технологии баз данных.
Пивоварчик В.А.
18

19. Консолидация

Консолидация подразумевает объединение данных
из различных диапазонов. Диапазоны могут
располагаться на одном листе, на разных листах
одной книги или в разных книгах.
26.09.2012
Технологии баз данных.
Пивоварчик В.А.
19

20. Консолидация

В меню Данные имеется встроенная команда
Консолидация…. Для консолидации по
расположению необходимо, чтобы все исходные
диапазоны были одинаковой структуры
Флажок Создавать связи с исходными данными
обеспечивает изменение данных в
консолидированной таблице при каждом их
изменении в исходных таблицах.
Если консолидация создавалась со связями с
исходными данными, то на листе консолидации
организуется структура
26.09.2012
Технологии баз данных.
Пивоварчик В.А.
20

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

Сводная таблица является мощным и
динамичным средством обработки данных.
Сводная таблица включает в себя
несколько уже известных операций:
сортировку, подведение итогов,
фильтрацию.
26.09.2012
Технологии баз данных.
Пивоварчик В.А.
21

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

Результат зависит от того, где будут
размещены заголовки полей – в области
страниц, в области строк, в области столбцов
или в области данных. В итоге формируется
вид выходного документа.
Двойным щелчком по заголовку столбца
сводной таблицы вызывается диалоговое
окно Вычисление поля сводной таблицы.
26.09.2012
Технологии баз данных.
Пивоварчик В.А.
22

23. Сводные диаграммы

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

24. Стандартные функции работы с БД

Стандартные функции баз
данных позволяют получать
те же результаты, что и при
выполнении операций из
меню Данные.
26.09.2012
Технологии баз данных.
Пивоварчик В.А.
24

25. Стандартные функции работы с БД

Однако результатом функции является
значение, которое возвращается в
ячейку, в которой вызывалась функция.
В дальнейшем это значение может быть
использовано при анализе базы данных.
26.09.2012
Технологии баз данных.
Пивоварчик В.А.
25

26. Стандартные функции работы с БД

Все функции для работы с базами данных:
БДДИСП, БДДИСПП, БДПРОИЗВЕД,
БДСУММ, БИЗВЛЕЧЬ, БСЧЕТ,
БСЧЕТА, ДМАКС, ДМИН, ДСРЗНАЧ,
ДСТАНДОТКЛ, ДСТАНДОТКЛП имеют
одинаковый синтаксис и используют три
аргумента:
26.09.2012
Технологии баз данных.
Пивоварчик В.А.
26

27. Стандартные функции работы с БД

26.09.2012
Технологии баз данных.
Пивоварчик В.А.
27

28. Стандартные функции работы с БД

база_данных – диапазон ячеек, формирующих
список; может задаваться диапазоном или именем;
поле – столбец списка, если значение этого
аргумента не указано, функция оперирует со всеми
столбцами списка;
критерий – ссылка на диапазон ячеек, задающих
условия для функции. Функция возвращает данные
из списка, которые удовлетворяют условиям,
заданным аргументом критерий. Диапазон
критериев должен включать заголовок и условия.
При задании аргумента критерий, можно указывать
диапазон критерия или заголовок критерия.
26.09.2012
Технологии баз данных.
Пивоварчик В.А.
28

29.

СПАСИБО!
ВОПРОСЫ?
26.09.2012
Технологии баз данных.
Пивоварчик В.А.
29