Работа в access как в excel

Учимся работать с базами данных Access из макросов Excel

Кратко о структуре базы данных. База состоит из таблиц. Таблица — из строк. Строка — из полей. В полях могут храниться данные разных форматов. Форматы определяются в момент создания таблицы. Создать базу и таблицы в ней можно в Access, а можно и средствами макроса прямо из Excel.

Чтобы Access смог обращаться к базам Access, нужно подключить библиотеку DAO. Для этого в редакторе Visual Basic выбираем меню «Tools->References», открывается окошко, в котором нужно поставить галочку напротив Microsoft DAO 3.6 Object Library.

Теперь делаем так:

‘Объявляем переменные
Dim dbs As DAO.Database ‘База данных
Dim rs As DAO.Recordset ‘Запрос к базе данных

‘Открываем базу
Set dbs = DAO.OpenDatabase(«c:database.mdb»)
‘Выполняем запрос к базе данных
Set rs = dbs.OpenRecordset(«SELECT * FROM тбл_тов WHERE ID_тов>2»)

Тут объясню подробнее. Запросы к базе выполняются на языке SQL. Пока рассмотрим только одну команду этого языка — SELECT. Эта команда выбирает из таблицы строки, удовлетворяющие заданному условию. Там, где у нас стоит звездочка, можно указать названия полей, которые будут присутствовать в ответе. В нашем случае будет возвращаться вся строка полностью. После слова FROM идет название таблицы, из которой идет выбор строк. Таблиц в базе может быть много. В нашем случае это тбл_тов — таблица товаров. После слова WHERE указывается условие, по которому отбираются строки. У нас условие такое: если поле ID_тов > 2, то строка включается в ответ. Ответ получается в переменной rs, которая имеет тип Recordset. Recordset — это временная таблица, сформированная из строк, удовлетворяющих условию запроса.

‘Если ничего не найдено — выдаем сообщение и выходим

If RS.RecordCount = 0 Then

    MsgBox («По вашему запросу ничего не найдено»)

    Exit Sub

End If

Do While Not RS.EOF    ‘цикл выполняется пока не достигнута последняя строка в таблице ответа

    ‘Присваиваем ячейкам таблицы значения из базы

    Cells(i, 2).Value = RS.Fields(«назв_тов»)

    Cells(i, 1).Value = RS.Fields(«ID_тов»)

    i = i + 1

    ‘переходим к следующей строке во временной таблице ответа

    RS.MoveNext

Loop

‘Закрываем временную таблицу

RS.Close

‘Очищаем память. Если этого не сделать, то таблица так и останется в памяти до закрытия рабочей книги.

Set RS = Nothing

‘Закрываем базу

dbs.Close

‘Очищаем память

Set dbs = Nothing

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

Создание новой базы данных

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

Откройте программу и перейдите в главное меню, нажав на команду «Файл», а там выберите пункт «Создать». Создавая новую базу, вам на выбор будет представлена пустая страница, которая будет иметь в себе одну таблицу либо же веб-база данных, позволяющая вам пользоваться встроенными инструментами программы для, например, ваших публикаций в интернете.

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

Наполнение базы данных информацией

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

1. Сейчас самым удобным и распространённым видом структуризации информации является таблица. По своим возможностям и виду, таблицы в Access не сильно отличаются от таких же в Excel, что, в свою очередь, значительно упрощает перенос данных из одной программы в другую.
2. Вторым способом внесения информации являются формы, они чем-то похожи на таблицы, однако, обеспечивают более наглядное отображение данных.
3. Для подсчёта и вывода информации из вашей базы предусмотрены отчёты, которые позволят вам проанализировать и высчитать, например, ваши доходы или количество контрагентов, с которыми вы работаете. Они очень гибки и позволяют производить любые расчёты, в зависимости от внесённых данных.
4. Получение и сортировка новых данных в программе осуществляется посредством запросов. С их помощью вы сможете найти определённые данные среди нескольких таблиц, а также создать или обновить данные.

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

Создание базы данных и импортирование информации

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

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

Все настройки импортирования находятся в отдельно вынесенной в панель управления вкладке, которая называется «Внешние данные». Здесь, в области «Импорт и связи», перечислены доступные форматы, среди которых документы Excel, Access, текстовые и XML-файлы, интернет-страницы, папки Outlook и т. д. Выбрав необходимый формат, с которого будет перенесена информация, вам потребуется указать путь к месторасположению файла. Если же она размещена на каком-либо сервере, программа потребует от вас ввести адрес сервера. По мере импорта вы столкнётесь с различными настройками, которые предназначены для корректного переноса данных в Access. Следуйте инструкциям программы.

Основные ключи и взаимосвязи таблиц

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

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

Подобные взаимосвязи облегчат читаемость вашей базы данных и наверняка увеличат её удобство и эффективность.

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

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

Создание и виды запросов

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

1. Выборный запросы, благодаря которым программа достаёт определённую информацию и делает по ней расчёты.
2. Запросы действия, добавляющие информацию в базу либо удаляющие её.

Выбрав «Мастер запросов» во вкладке «Создание», программа проведёт процесс создания определённого типа запроса. Следуйте инструкциям.

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

Например, вы можете создать выборный запрос по определённым параметрам. Если вы хотите увидеть информацию по конкретной дате или дню таблицы «День» за весь промежуток времени, вы можете настроить подобный запрос. Выберите пункт «Конструктор запросов», а в нём нужную вам таблицу. По умолчанию запрос будет выборным, это становится понятным, если глянуть на панель инструментов с выделенной там кнопкой «Выборка». Для того чтобы программа искала именно ту дату или день, который вам нужен, найдите строку «Условие отбора» и введите туда фразу [какой день?]. Запомните, запрос должен быть помещён в квадратные дужки [] и заканчиваться на знак вопроса либо на двоеточие.

Это лишь один из вариантов использования запросов. На самом деле, с их помощью также можно создавать новые таблицы, отбирать данные по критериям и т. д.

Настройка и использование форм

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

Откройте вкладку «Создание» и найдите пункт «Форма», нажав на который появится стандартная форма, основанная на данных вашей таблицы. Появившиеся поля с информацией подвергаются всяческим изменениям, включая высоту, ширину и т. д. Обратите внимание, что если в приведённой таблице присутствуют взаимосвязи, вы увидите их и сможете перенастроить в этом же окне. В нижней части программы вы увидите стрелки, которые позволят последовательно открывать каждый столбец вашей таблицы либо же сразу переместиться на первый и последний. Теперь каждая из них является отдельной записью, поля которой вы можете настроить, кликнув мышью на кнопку «Добавить поля». Изменённые и внесённые таким образом сведения отобразятся в таблице и во всех присоединённых к ней таблицах. После настройки форма, необходимо сохранить её, нажав комбинацию клавиш «Ctrl+S».

Создание отчёта

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

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

1. Отчёт — будет создан автоотчёт, использующий всю предоставленную в таблице информацию, однако, данные не будут сгруппированы.
2. Пустой отчёт — не заполненная форма, данные для которой вы сможете выбрать сами из необходимых полей.
3. Мастер отчётов — поможет вам пройти процесс создания отчёта и проведёт группирование и форматирование данных.

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

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

В современном мире нужны инструменты, которые бы позволяли хранить, систематизировать и обрабатывать большие объемы информации, с которыми сложно работать в Excel или Word.

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

Продукт от Microsoft Office представляет собой упрощенную версию в функциональном плане и более понятную для неопытных пользователей. Давайте рассмотрим пошагово создание базы данных в Access 2007.

Microsoft Access 2007 – это система управления базами данных (СУБД), реализующая полноценный графический интерфейс пользователя, принцип создания сущностей и связей между ними, а также структурный язык запросов SQL. Единственный минус этой СУБД – невозможность работать в промышленных масштабах. Она не предназначена для хранения огромных объемов данных. Поэтому MS Access 2007 используется для небольших проектов и в личных некоммерческих целях.

Но прежде чем показывать пошагово создание БД, нужно ознакомиться с базовыми понятиями из теории баз данных.

Определения основных понятий

Без базовых знаний об элементах управления и объектах, использующихся при создании и конфигурации БД, нельзя успешно понять принцип и особенности настройки предметной области. Поэтому сейчас я постараюсь простым языком объяснить суть всех важных элементов. Итак, начнем:

1. Предметная область – множество созданных таблиц в базе данных, которые связаны между собой с помощью первичных и вторичных ключей.
2. Сущность – отдельная таблица базы данных.
3. Атрибут – заголовок отдельного столбца в таблице.
4. Кортеж – это строка, принимающая значение всех атрибутов.
5. Первичный ключ – это уникальное значение (id), которое присваивается каждому кортежу.
6. Вторичный ключ таблицы «Б» – это уникальное значение таблицы «А», использующееся в таблице «Б».
7. SQL запрос – это специальное выражение, выполняющее определенное действие с базой данных: добавление, редактирование, удаление полей, создание выборок.

Теперь, когда в общих чертах есть представление о том, с чем мы будем работать, можно приступить к созданию БД.

Создание БД

Для наглядности всей теории создадим тренировочную базу данных «Студенты-Экзамены», которая будет содержать 2 таблицы: «Студенты» и «Экзамены». Главным ключом будет поле «Номер зачетки», т.к. данный параметр является уникальным для каждого студента. Остальные поля предназначены для более полной информации об учащихся.

Итак, выполните следующее:

1. Запустите MS Access 2007.
2. Нажмите на кнопку «Новая база данных».
3. В появившемся окне введите название БД и выберите «Создать».
   

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

Создание и заполнение таблиц

После успешного создания БД на экране появится пустая таблица. Для формирования ее структуры и заполнения выполните следующее:

1. Нажмите ПКМ по вкладке «Таблица1» и выберите «Конструктор».
   
2. Теперь начинайте заполнять названия полей и соответствующий им тип данных, который будет использоваться.
   

   Внимание! Первым полем принято устанавливать уникальное значение (первичный ключ). Для него предпочтительно числовое значение.

1. После создания необходимых атрибутов сохраните таблицу и введите ее название.
2. Снова нажмите ПКМ по вкладке с уже новым название и выберите «Режим таблицы».
   
3. Заполните таблицу необходимыми значениями.
   

Совет! Для тонкой настройки формата данных перейдите на ленте во вкладку «Режим таблицы» и обратите внимание на блок «Форматирование и тип данных». Там можно кастомизировать формат отображаемых данных.

Создание и редактирование схем данных

Перед тем, как приступить к связыванию двух сущностей, по аналогии с предыдущим пунктом нужно создать и заполнить таблицу «Экзамены». Она имеет следующие атрибуты: «Номер зачетки», «Экзамен1», «Экзамен2», «Экзамен3».

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

1. Перейти во вкладку «Работа с базами данных».
2. Нажать на кнопку «Схема данных».
3. Если схема не была создана автоматически, нужно нажать ПКМ на пустой области и выбрать «Добавить таблицы».
   
4. Выберите каждую из сущностей, поочередно нажимая кнопку «Добавить».
5. Нажмите кнопку «ОК».

Конструктор должен автоматически создать связь, в зависимости от контекста. Если же этого не случилось, то:

1. Перетащите общее поле из одной таблицы в другую.
2. В появившемся окне выберите необходимы параметры и нажмите «ОК».
   
3. Теперь в окне должны отобразиться миниатюры двух таблиц со связью (один к одному).
   

Выполнение запросов

Что же делать, если нам нужны студенты, которые учатся только в Москве? Да, в нашей БД только 6 человек, но что, если их будет 6000? Без дополнительных инструментов узнать это будет сложно.

Именно в этой ситуации к нам на помощь приходят SQL запросы, которые помогают изъять лишь необходимую информацию.

Виды запросов

SQL синтаксис реализует принцип CRUD (сокр. от англ. create, read, update, delete — «создать, прочесть, обновить, удалить»). Т.е. с помощью запросов вы сможете реализовать все эти функции.

На выборку

В этом случае в ход вступает принцип «прочесть». Например, нам нужно найти всех студентов, которые учатся в Харькове. Для этого нужно:

1. Перейти во вкладку «Создание».
2. Нажать кнопку «Конструктор запросов» в блоке «Другие».
3. В новом окне нажмите на кнопку SQL.
   
4. В текстовое поле введите команду: SELECT * FROM Студенты WHERE Адрес = “Харьков”; где «SELECT *» означает, что выбираются все студенты, «FROM Студенты» – из какой таблицы, «WHERE Адрес = “Харьков”» – условие, которое обязательно должно выполняться.
5. Нажмите кнопку «Выполнить».
   
6. На выходе мы получаем результирующую таблицу.
   

А что делать, если нас интересуют студенты из Харькова, стипендии у которых больше 1000? Тогда наш запрос будет выглядеть следующим образом:

SELECT * FROM Студенты WHERE Адрес = “Харьков” AND Стипендия > 1000;

а результирующая таблица примет следующий вид:

На создание сущности

Кроме добавления таблицы с помощью встроенного конструктора, иногда может потребоваться выполнение этой операции с помощью SQL запроса. В большинстве случаев это нужно во время выполнения лабораторных или курсовых работ в рамках университетского курса, ведь в реальной жизни необходимости в этом нет. Если вы, конечно, не занимаетесь профессиональной разработкой приложений. Итак, для создания запроса нужно:

1. Перейти во вкладку «Создание».
2. Нажать кнопку «Конструктор запросов» в блоке «Другие».
3. В новом окне нажмите на кнопку SQL, после чего в текстовое поле введите команду:

CREATE TABLE Преподаватели
(КодПреподавателя INT PRIMARY KEY,
Фамилия CHAR(20),
Имя CHAR (15),
Отчество CHAR (15),
Пол CHAR (1),
Дата_рождения DATE,
Основной_предмет CHAR (200));

где «CREATE TABLE» означает создание таблицы «Преподаватели», а «CHAR», «DATE» и «INT» – типы данных для соответствующих значений.

1. Кликните по кнопке «Выполнить».
2. Откройте созданную таблицу.
   

Внимание! В конце каждого запроса должен стоять символ «;». Без него выполнение скрипта приведет к ошибке.

На добавление, удаление, редактирование

Здесь все гораздо проще. Снова перейдите в поле для создания запроса и введите следующие команды:

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

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

1. Откройте интересующую таблицу.
2. Перейдите во вкладку «Создание».
3. Нажмите на необходимый формат формы из блока «Формы».
   

   Совет! Рекомендуется использовать «Разделенную форму» – кроме самого шаблона, в нижней части будет отображаться миниатюра таблицы, которая сделает процесс редактирования еще более наглядным.

4. С помощью навигационных кнопок переходите к следующей записи и вносите изменения.
   

Все базовые функции MS Access 2007 мы уже рассмотрели. Остался последний важный компонент – формирование отчета.

Формирование отчета

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

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

1. Перейдите во вкладку «Создание».
2. Нажмите на кнопку «Мастер отчетов» в блоке «Отчеты».
   
3. Выберите интересующую таблицу и поля, нужные для печати.
   
4. Добавьте необходимый уровень группировки.
   
5. Выберите тип сортировки каждого из полей.
   
6. Настройте вид макета для отчета.
   
7. Выберите подходящий стиль оформления.
   

   Внимание! В официальных документах допускается только стандартный стиль оформления.

8. Просмотрите созданный отчет.
   

Если отображение вас не устраивает, его можно немного подкорректировать. Для этого:

1. Нажмите ПКМ на вкладке отчета и выберите «Конструктор».
2. Вручную расширьте интересующие столбцы.
   
3. Сохраните изменения.

Вывод

Итак, с уверенностью можно заявить, что создание базы данных в MS Access 2007 мы разобрали полностью. Теперь вам известны все основные функции СУБД: от создания и заполнения таблиц до написания запросов на выборку и создания отчетов. Этих знаний хватит для выполнения несложных лабораторных работ в рамках университетской программы или использования в небольших личных проектах.

Для проектирования более сложных БД необходимо разбираться в объектно-ориентированном программировании и изучать такие СУБД, как MS SQL и MySQL. А для тех, кому нужна практика составления запросов, рекомендую посетить сайт SQL-EX, где вы найдете множество практических занимательных задачек.

Удачи в освоении нового материала и если есть какие-либо вопросы – милости прошу в комментарии!

Что делать, если объем данных слишком велик для Excel? Существуют ли другие инструменты, которые могут заменить Excel, но просты в использовании?

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

Большой объем данных для отчетности или анализа данных

• Более 100 мегабайт, сотни тысяч строк данных в формате Excel: база данных ACCESS + SQL

• Если данные не достигают сотен миллионов уровней, используйте инструменты BI для непосредственного анализа

• Независимо от того, насколько он большой, он не похож на двоюродного брата или двоюродного брата, его может решить аналитик данных.

Принимая во внимание общее использование Excel в повседневной жизни, эта статья будет в основном рассказывать о программах первого типа и предлагает полный учебник, универсальный и практичный. Используемый инструмент — база данных ACCESS.

Что касается ACCESS, его брата по Excel, того же Microsoft Office, то нетрудно начать, получить основную операцию за неделю.

Это может решить следующие жалобы небольших партнеров, которые выполняют операции с данными:

Хранилище таблиц данных Excel, которые будут обрабатываться в бизнесе, становится все больше и больше, и оно становится медленным, как улитка, когда оно превышает 50 МБ. В это время, если в таблице больше функций IF и VLOOKUP, компьютер будет работать напрямую, если он сталкивается с размером, подобным этому Таблицы Excel, компьютеры уровня сервера не могут быть перегружены, не говоря уже об обработке данных и анализе данных.

Данные Excel с большим объемом данных трудно открыть

При обнаружении таблицы данных Excel с таким большим объемом памяти, как показано на рисунке выше, карта неизбежна. Когда возникают такие трудности, существует ли какое-либо программное обеспечение для анализа данных, которое может обрабатывать файлы большой емкости, в то же время простое в использовании и простое в использовании?

Ответ, конечно: ДА, и тот же брат Excel, ДОСТУП фракции Microsoft Office.

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

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

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

1. Введение в базу данных ACCESS

1. Основные понятия операторов ACCESS и SQL

Access, полное имя «Microsoft Office Access», является членом Microsoft Office, системы управления реляционными базами данных, выпущенной Microsoft. Он сочетает в себе функции Microsoft Jet Database Engine и графический интерфейс пользователя и является одной из системных программ Microsoft Office. (Из энциклопедии Baidu)

Когда дело доходит до ACCESS, вы должны упомянуть SQL. Только овладев SQL, вы сможете максимизировать функции ACCESS. Полное имя SQL — «Язык структурированных запросов», который является декларативным языком.

Во-первых, помните об этой концепции: «Декларация». По сравнению с языками программирования, известными всем в прошлом, язык SQL является примером для компьютера, который объявляет, какие результаты вы хотите получить из исходных данных, вместо того, чтобы сообщать компьютеру, как получить результаты. Другими словами, настоящее ядро ​​SQL — это ссылка на таблицу.

SELECT first_name, last_name FROM employees WHERE age> =25

Приведенный выше пример прост для понимания. Нам не нужно заботиться о том, откуда берутся данные о сотрудниках. Все, что нам нужно, это данные о сотрудниках в возрасте 25 лет и старше (возраст> = 25).

2. Преимущества ДОСТУПА

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

3. Общие предложения ДОСТУПА

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

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

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

2. Практика анализа данных ACCESS

1. Импорт данных

Следующая таблица является исходным файлом анализа данных ACCESS в этой статье. Объем данных составляет почти 230 МБ. Excel нужно подождать несколько минут, чтобы открыть, и это зависит от настроения компьютера … В целях коммерческой конфиденциальности в этой статье будут использоваться некоторые данные для анализа и работы, а также Сделайте некоторую обработку.

Сначала импортируйте файл в Excel в ACCESS, как показано стрелкой на рисунке ниже:

После выполнения описанных выше шагов автоматически генерируется первичный ключ (т. Е. Идентификатор) и получается следующий результат:

2. Анализ времени заказа пользователя

Для анализа периода времени для размещения заказа необходимо преобразовать время, когда пользователь разместил заказ, в час «время». Используемый здесь оператор SQL — это формат, а функция — форматировать выбранное поле. Синтаксис:

формат (поле ссылки, «формат данных»)

Среди них «формат данных» обычно выбирает H (час), D (день), M (месяц) или Y (год) во времени.

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

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

Создайте новый «Query Design» в «Create», нажмите «SQL» в правом нижнем углу, а затем введите следующий оператор в диалоговом окне SQL:

Формат SELECT (время заказа, «ч») AS период, количество (UserID) количество заказа AS

ОТ метаданных

Формат GROUP BY (время заказа, «ч»);

Затем нажмите «Выполнить» в разделе «Дизайн», чтобы получить следующий результат:

Возьмите пример, чтобы объяснить, как интерпретировать вышеуказанные результаты: если клиент размещает заказ в 12:23, момент времени попадает в период «12», а «12» представляет время 12 ~ 13 часов. сегмент. Вы можете использовать функции в статье «Практика работы | Как использовать фоновые данные WeChat для оптимизации операций WeChat для оптимизации операций WeChat», чтобы превратить их в отображение периода времени.

3. Анализ распределения заказов в диапазоне оплаты

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

грамматика:

ПЕРЕКЛЮЧАТЕЛЬ (условие 1, результат 1, условие 2, результат 2, условие 3, результат 3, …, условие N, результат N)

Условие 1, Условие 2 и Условие 3: представляют вычисляемое выражение.Если Условие 1 истинно, возвращаемое значение результата 1, если Условие 2 истинно, возвращаемое значение результата 2 и т. Д.

В соответствии с описанным выше способом создайте «Дизайн запроса» в «Создать», нажмите «SQL» в правом нижнем углу и введите следующую инструкцию:

ВЫБЕРИТЕ ИД пользователя, сумму платежа, переключатель (сумма платежа <= 10, «1 ~ 10 юаней»,

Сумма платежа <= 20, «11 ~ 20 юаней»,

Сумма платежа <= 50, «21 ~ 50 юаней»,

Сумма платежа <= 80, «51 ~ 80 юаней»,

Сумма платежа <= 150, «81 ~ 150 юаней»,

Оплата> 150, «151 ~ 220 юаней»), как диапазон потребления

ОТ метаданных;

После нажатия «Выполнить» получаются следующие результаты:

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

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

Оператор SQL, который необходимо ввести здесь:

ВЫБЕРИТЕ диапазон потребления, количество (UserID) AS количество заказа

ОТ платежного интервала

Диапазон потребления GROUP BY;

После нажатия «Выполнить» результат будет следующим:

Затем скопируйте вышеуказанные данные в таблицу Excel и создайте следующую процентную веерную диаграмму, которая может интуитивно анализировать пропорцию заказов в каждом интервале потребления, а затем посмотреть, каков общий уровень потребления пользователями. Разумная оценка операции.

4. Анализ количества заказов, количества пользователей и объема продаж по регионам

(1) Количество пользователей в каждой области

Это немного хлопотно: количество пользователей рассчитывается косвенно, используя счет «userID», но, поскольку большинство пользователей размещают заказ не менее 2 раз, поэтому, если вы посчитаете его напрямую, результатом будет количество заказа. Ввиду этой ситуации мы должны изменить свое мышление и составить таблицу информации о заказах пользователя, которая не будет дублироваться, то есть таблицу частот заказов, размещаемых каждым идентификатором пользователя.

Создайте новый «дизайн запроса» и назовите его «частота потребления пользователями». Введите следующий оператор в диалоговом окне SQL:

ВЫБЕРИТЕ UserID, COUNT (UserID) AS время потребления, регион

ОТ метаданных

GROUP BY UserID, площадь;

После нажатия «Выполнить» результат будет следующим:

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

Создайте новый «дизайн запроса» и назовите его «количество пользователей в каждой области». Введите следующий оператор в диалоговом окне SQL:

Область SELECT, count (UserID) AS общее количество пользователей

ОТ пользовательской частоты

GROUP BY область;

После нажатия «Выполнить» результат будет следующим:

(2) Статус заказов и объемов потребления в различных регионах

Создайте новый «Дизайн запроса» и назовите его «Статус заказа по регионам». Введите следующий оператор в диалоговом окне SQL:

Поле SELECT, count (UserID) AS общее количество заказов, сумма (оплата) AS общая сумма, avg (оплата) AS средняя сумма потребления

ОТ метаданных

GROUP BY область;

После нажатия «Выполнить» результат будет следующим:

Затем интегрируйте количество пользователей в вышеуказанных регионах в эту таблицу, чтобы получить полный обзор работы этих трех регионов. Смотрите таблицу ниже:

5. Анализ потребительской ценности

Анализ потребительской ценности здесь основан на модели RFM, но он был дополнительно улучшен. На основе первоначального «совокупного объема потребления» введены три «минимальный объем потребления», «максимальный объем потребления» и «средний объем потребления». Этот показатель стремится полностью отражать покупательскую способность потребителей.

Создайте новый «Дизайн запроса» и назовите его «Потребление пользователя». Введите следующий оператор в диалоговом окне SQL:

ВЫБЕРИТЕ userID, min (сумма платежа) AS минимальная сумма потребления,

max (сумма платежа) AS максимальная сумма потребления,

avg (оплата) AS средняя сумма потребления,

сумма (оплата) как общая сумма потребления,

рассчитать (оплатить) AS частоту потребления,

datediff («d», max (дата размещения заказа), # 2015-9-15 #) AS количество дней с момента последнего потребления

ОТ метаданных

GROUP BY userID;

После нажатия «Выполнить» результат будет следующим:

После получения таблицы вы можете выполнить кластерный анализ и классифицировать пользователей в соответствии с тремя измерениями R, F и M. Подробнее см. В разделе «[Практика обработки данных] Как использовать анализ данных для выполнения проекта пробной операции» «Нет тупика» обзор? »Эта статья.

Наконец, мы также можем получить общий объем заказов и продаж в этих трех регионах:

Создайте новый «Дизайн запроса» и назовите его «Обзор продаж по регионам». Введите следующий оператор в диалоговом окне SQL:

SELECT count (userID) AS общее количество заказов,

сумма (сумма платежа) как общая сумма платежа,

avg (сумма платежа) AS средняя сумма заказа

ОТ метаданных;

После нажатия «Выполнить» результат будет следующим:

Вывод

Как видно из вышеприведенного случая, если операторы SQL используются немного более умело, ACCESS не будет уступать Excel в обработке данных, и его сильной стороной является обработка больших объемов данных.

Была ли эта статья полезной?