Тема 1.2: Создание, ввод и обработка баз данных в MS Excel.
Цель: Рассмотреть этапы создания, способы ввода и методы обработки баз данных в MS Excel.
Максимальный размер списка, создаваемого в Excel, ограничен размером одного рабочего листа. Он может содержать не более 256 полей и не более 65535 записей (одна строк предназначена для имен полей). Список такого размера требует огромного количества памяти и, иногда, его даже невозможно отобразить.
Существует несколько правил для создания списка:
- Рабочий лист должен иметь свое уникальное имя.
- Каждый столбец должен содержать информацию одного типа. В списке сотрудников, например, один столбец отводится для фамилии, второй для имени, третий для отчества, четвертый для даты приема на работу и т. д.
- Одна или две верхние строки списка должны содержать заголовки, каждый из которых описывает содержимое расположенного ниже столбца.
- Информация в списках должна иметь постоянную структуру.
- Нельзя включать в список пустые строки и столбцы.
- Лучше всего, если для списка отводится отдельный лист. Если это невозможно, то список должен быть отделен от других данных рабочего листа по крайней мере одной пустой строкой и одним пустым столбцом, чтобы программа могла автоматически определить границы списка.
- Нельзя размещать данные слева или справа от списка, поскольку они могут быть скрыты в процессе фильтрации списка.
- Не следует вводить лишние пробелы или какие-либо знаки перед данными в ячейке.
- Первую строку с именами полей желательно закрепить, выполнив команду Окно – Закрепить область.
Для работы с базами данных в Excel используется пункт меню Данные.
2. Ввод данных в список.
Для непосредственного ввода информации в список можно поступать стандартно: выделить ячейку, ввести данные и нажать клавишу Enter.
Tab
Shift+Tab
Enter
Esc
Ctrl+PgUp
Ctrl+PgDn
PgUp или кнопка Предыдущая
PgDn или кнопка Следующая
Полоса прокрутки
Перемещение к следующему полю
Перемещение к предыдущему полю
Конец ввода очередной записи
Выход из диалогового окна Форма
Перемещение к первой записи
Перемещение за последнюю запись
Перемещение к предыдущей записи
Перемещение к следующей записи
Перемещение от записи к записи
- Выполнить команду Данные — Форма.
- Нажать кнопку Критерии.
- Ввести условие поиска в соответствующие поля. Необходимо заполнить те поля, по которым будет осуществляться поиск.
- Нажать Enter для того, чтобы вернуться к исходной Форме.
Чтобы снова получить возможность просмотра всех записей, необходимо нажать на кнопке Критерии и затем — на кнопке Очистить, в результате чего все критерии будут отменены. После этого нажать на кнопке Вернуть. Теперь можно, используя кнопки Далее и Назад, просматривать все записи в списке.
5. Сортировка данных в списках (самостоятельная работа).
6. Создание пользовательского списка (самостоятельная работа).
9. Использование при фильтрации данных символов подстановки (самостоятельная работа).
Рисунок 7 – Критерии для сложного отбора
Для быстрого ввода уже существующих значений и критериев в диалоговом окне Пользовательский автофильтри можно выбрать их из раскрывающихся списков.
При вводе в диалоговом окне Пользовательский автофильтр двух критериев порядок их следования не имеет значения.
- При задании критериев для примера был выбран режим И (рис. 8). Это означает, что Excel будет искать записи, удовлетворяющие обоим критериям одновременно. Если запись будет удовлетворять только одному критерию, то программа уберет ее с экрана.
Если нужно отобрать записи, удовлетворяющие хотя бы одному из указанных критериев, то в этом случае выбирается режим ИЛИ. Например, можно выбрать студентов, обучающихся на 11 баллов по Зарубежной или Украинской литературам.
Рисунок 9 – Результаты отбора.
- Что в MS Excel понимается под базой данных? (Определение БД — это база данных. )
- Какие правила необходимо соблюдать при создании баз данных в MS Excel?
- Как можно вводить новые данные в списки?
- Можно ли изменять рассчитываемые значения в списке с помощью формы?
- Какие операции можно выполнять над базами данных с помощью форм?
- Какими способами можно отобрать данные в списке с помощью автофильтра?
- Какие логические операции используются при фильтрации данных и в чем между ними разница?
Домашнее задание:
Дополнительный материал для самостоятельного изучения по следующим вопросам: сортировка данных в списках; создание пользовательского списка; отбор данных с помощью операторов; использование при фильтрации данных символы подстановки.
Записать параметры фильтрации для решения следующих задач:
Как Сделать Запрос к Базе Данных Excel
Вставляет результат выполнения запроса по базе данных в таблицу Word . Если число столбцов превышает 62, результат выполнения запроса оформляется в виде столбцов, разделенных знаками табуляции. Поле DATABASE содержит все сведения, необходимые для подключения к базе данных и выполнения запроса SQL. Для повторного выполнения запроса следует обновить это поле.
Структура базы данных
Прежде чем мы начнем разбирать запросы, нужно, чтобы вы поняли иерархию базы данных.
База данных SQL — это набор взаимосвязанных сведений, хранящихся в таблицах. В каждой таблице есть столбцы, описывающие хранящиеся в них данные, и строки, в которых эти данные хранятся. Поле — это отдельный кусочек данных в строке. Чтобы найти нужные данные, мы должны написать, что именно мы хотим получить.
Возьмем для примера некую компанию, штат которой разбросан по всему миру. Допустим, у этой компании есть много баз данных. Чтобы увидеть их полный список, нужно набрать SHOW DATABASES;
Все таблицы состоят из различных столбцов, описывающих данные.
Таблицы также состоят из строк — отдельных записей. В нашем примере в строках будут указаны id, имена, фамилии, email, зарплата и страны проживания сотрудников. Каждая строка будет касаться одного сотрудника, допустим, из команды Engineering.
SQL для анализа данных | SQL
Ну и раз уж это база данных для ведения кассы, в любую ячейку закрепленной строки, правее наименований полей, вставьте формулу: =СУММ(C:C)-СУММ(E:E), чтобы всегда видеть текущий остаток денег в кассе.
