Как сделать базу данных в access из таблицы excel?

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

В пакете Microsoft Office есть специальная программа для создания базы данных и работы с ними – Access. Тем не менее, многие пользователи предпочитают использовать для этих целей более знакомое им приложение – Excel. Нужно отметить, что у этой программы имеется весь инструментарий для создания полноценной базы данных (БД). Давайте выясним, как это сделать.

Процесс создания

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

Согласно специальной терминологии, строки БД именуются «записями». В каждой записи находится информация об отдельном объекте.

Столбцы называются «полями». В каждом поле располагается отдельный параметр всех записей.

То есть, каркасом любой базы данных в Excel является обычная таблица.

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

Итак, прежде всего нам нужно создать таблицу.

  1. Вписываем заголовки полей (столбцов) БД.

Заполняем наименование записей (строк) БД.

Переходим к заполнению базы данными.

  • После того, как БД заполнена, форматируем информацию в ней на свое усмотрение (шрифт, границы, заливка, выделение, расположение текста относительно ячейки и т.д.).
  • На этом создание каркаса БД закончено.

    Присвоение атрибутов базы данных

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

      Переходим во вкладку «Данные».

    Выделяем весь диапазон таблицы. Кликаем правой кнопкой мыши. В контекстном меню жмем на кнопку «Присвоить имя…».

    В графе «Имя» указываем то наименование, которым мы хотим назвать базу данных. Обязательным условием является то, что наименование должно начинаться с буквы, и в нём не должно быть пробелов. В графе «Диапазон» можно изменить адрес области таблицы, но если вы её выделили правильно, то ничего тут менять не нужно. При желании в отдельном поле можно указать примечание, но этот параметр не является обязательным. После того, как все изменения внесены, жмем на кнопку «OK».

  • Кликаем по кнопке «Сохранить» в верхней части окна или набираем на клавиатуре сочетание клавиш Ctrl+S, для того, чтобы сберечь БД на жестком диске или съемном носителе, подключенном к ПК.
  • Можно сказать, что после этого мы уже имеем готовую базу данных. С ней можно работать и в таком состоянии, как она представлена сейчас, но многие возможности при этом будут урезаны. Ниже мы разберем, как сделать БД более функциональной.

    Сортировка и фильтр

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

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

    Сортировку можно проводить практически по любому параметру:

    • имя по алфавиту;
    • дата;
    • число и т.д.
  • В следующем появившемся окне будет вопрос, использовать ли для сортировки только выделенную область или автоматически расширять её. Выбираем автоматическое расширение и жмем на кнопку «Сортировка…».

  • Открывается окно настройки сортировки. В поле «Сортировать по» указываем имя поля, по которому она будет проводиться.
    • В поле «Сортировка» указывается, как именно она будет выполняться. Для БД лучше всего выбрать параметр «Значения».
    • В поле «Порядок» указываем, в каком порядке будет проводиться сортировка. Для разных типов информации в этом окне высвечиваются разные значения. Например, для текстовых данных – это будет значение «От А до Я» или «От Я до А», а для числовых – «По возрастанию» или «По убыванию».
    • Важно проследить, чтобы около значения «Мои данные содержат заголовки» стояла галочка. Если её нет, то нужно поставить.

    После ввода всех нужных параметров жмем на кнопку «OK».

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

    Одним из наиболее удобных инструментов при работе в базе данных Excel является автофильтр. Выделяем весь диапазон БД и в блоке настроек «Сортировка и фильтр» кликаем по кнопке «Фильтр».

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

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

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

      Для этого переходим во вкладку «Главная» и на ленте в блоке инструментов «Редактирование» жмем на кнопку «Найти и выделить».

    Открывается окно, в котором нужно указать искомое значение. После этого жмем на кнопку «Найти далее» или «Найти все».

    В первом случае первая ячейка, в которой имеется указанное значение, становится активной.

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

    Закрепление областей

    Удобно при создании БД закрепить ячейки с наименованием записей и полей. При работе с большой базой – это просто необходимое условие. Иначе постоянно придется тратить время на пролистывание листа, чтобы посмотреть, какой строке или столбцу соответствует определенное значение.

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

  • Находясь во вкладке «Вид» кликаем по кнопке «Закрепить области», которая расположена в группе инструментов «Окно». В выпадающем списке выбираем значение «Закрепить области».
  • Теперь наименования полей и записей будут у вас всегда перед глазами, как бы далеко вы не прокручивали лист с данными.

    Выпадающий список

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

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

    Выделяем этот список и кликаем по нему правой кнопкой мыши. В появившемся меню выбираем пункт «Присвоить имя…».

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

    Возвращаемся на лист с БД. Выделяем диапазон, к которому будет применяться выпадающий список. Переходим во вкладку «Данные». Жмем на кнопку «Проверка данных», которая расположена на ленте в блоке инструментов «Работа с данными».

  • Открывается окно проверки видимых значений. В поле «Тип данных» выставляем переключатель в позицию «Список». В поле «Источник» устанавливаем знак «=» и сразу после него без пробела пишем наименование выпадающего списка, которое мы дали ему чуть выше. После этого жмем на кнопку «OK».
  • Теперь при попытке ввести данные в диапазон, где было установлено ограничение, будет появляться список, в котором можно произвести выбор между четко установленными значениями.

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

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

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

    Импорт базы данных Excel в Access

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

    • 1. Мы перенесем (импортируем) Список Excel Книги в Access и поработаем с формами, запросами и отчетами. Формы Access создаются Мастерами буквально несколькими кликами мыши. Запросы позволяют манипулировать информацией: отбирать, преобразовывать, вычислять. Это больше и легче, чем фильтрация Excel. Отчеты Access позволяют создавать на основе информации базы данных, часто, используя запросы, выходные документы с колонтитулами, названиями, заголовками и другими атрибутами документа.
    • 2. Мы научимся использовать шаблон Microsoft Access электронного каталога Biblio и модифицируем его в соответствии с нашими потребностями. Серьезные специалисты работают с текстами, делают выписки. Но нужен инструмент, который бы позволил среди этих многочисленных выписок находить полезные именно для текущей работы. Делать это можно с использованием ключевых слов. Мы сделаем такую базу данных. Легко. Действительно легко.
    • 3. Модифицировать — это часто, как носить костюм, сшитый для другого человека. Можно походить и в таком, но все же приятнее, когда вещь сделана именно для тебя. То есть, если это не очень сложно для вас, было бы полезно научиться делать несложные базы данных самостоятельно. Мы обозначим некоторый минимум знаний, который позволит создавать базы данных в 3—5 таблиц с отношениями многие-ко-многим, подстановками и т.д. И научимся с ними работать.
    Читать еще:  Как сделать книгу в excel?

    Таблицы Google, облако, таблицы Excel — это все очень хорошо. Но мы хотим показать, что манипулировать данными о книгах много проще в Access. Предварительно определим, что

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

    Мы об этом говорили, но подчеркнем еще раз: очень важно определить перечень вопросов, на которые должны быть получены ответы с помощью базы данных и задач, которые необходимо решать. Также полезно определить некоторые рамки. Например, насколько большой будет эта база данных, на каком временном интервале эта база данных будет использоваться. Мне думается, что 3—5 тыс. книг это более чем приличная библиотека. Если человек активно использует несколько сотен книг — это уже очень много. Но в каталог библиотеки может попасть в несколько раз больше книг, поскольку далеко не все заинтересовавшие вас книги станут вашим рабочим инструментом.

    Наши задачи следующие.

    • 1. Определить наличие той или иной книги в домашней библиотеке.
    • 2. Подготовить требование для книги из библиотеки Университета (вы работали с этой книгой и в вашем каталоге есть реквизиты книги).
    • 3. Составить список литературы по учебной дисциплине.
    • 4. Составить список литературы по проблеме, которой вы занимаетесь.
    • 5. Составить список литературы по определенной проблеме за определенный период времени.
    • 6. И, может быть главное, обеспечить возможность создания выписок из изученных книг и их систематизацию по ключевым словам.

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

    При исключении повторяющихся записей можно использовать один из двух подходов. Во-первых, Расширенный фильтр. Если выполнить команду Данные / Дополнительно, то появится окно Расширенный фильтр (рис. 4.1). В этом окне нужно выставить флаг Только уникальные записи.

    Рис. 4.1. Фильтрация только в уникальных записях

    Во-вторых, на вкладке Данные в группе Работа с данными есть команда Удалить дубликаты. После выполнения команды появится окно оповещения (рис. 4.2). [1] [2]

    Рис. 4.2. Окно оповещения об удалении дубликатов записей

    Рис. 43. Шаблоны баз данных Access

    A/c. 4.4. Начальная страница работы с пустой базой данных

    Чтобы импортировать в Access таблицу Excel из Excel, необходимо выполнить команду Внешние данные / Excel. В результате появится окно Внешние данные (рис. 4.5).

    Рис. 4.5. Импорт таблицы в Access таблицы Excel

    Через кнопку Обзор нужно выбрать файл для импорта данных. Это также файл Книги80. Заголовки столбцов таблицы Excel будут полями таблицы Access. Лучше, если будут удалены пробелы между словами в этих заголовках. Система, конечно, укажет на ошибки, но чтобы потом с ними не возиться, следует перед импортом внимательно просмотреть таблицу Excel. Вот что получится, если этого не сделать (рис. 4.6).

    Рис. 4.6. Пример ошибки при импорте

    В нашем случае просто отсутствовал один из заголовков. Хорошо, если названия будут из одного слова: ГодИздания, но не Год издания. Первое начертание упростит дальнейшие манипуляции с именами полей (их не нужно будет брать в кавычки).

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

    Рис. 4.7. Работа с мастером Импорта

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

    Чтобы работать в режиме конструктора, необходимо вызвать Контекстное меню (рис. 4.9). Работать с ним предпочтительнее, нежели обращаться к командам ленты.

    Рис. 4.8. Импортированная таблица в режиме конструктора

    Рис. 4.9. Работа с Контекстным меню

    • [1] Экспериментируйте. Попробуйте оба варианта. Мне второй кажетсяболее наглядным. Но если необходимо еще и осуществлять фильтрацию,то предпочтительным может оказаться первый. Хотя, конечно, дело вкуса. Список книг, с которым мы работали (Книги70), имел еще одну неправильность: в поле Ключевые слова через запятую могли быть записанынесколько ключевых слов. В Access и это недопустимо. Чтобы получитьправильную таблицу, необходимы следующие действия:
    • [2] лист Книги70 копируется в лист с именем одного из ключевых слов,например Инн; 2) в листе Инн фильтруются записи с ключевым словом Инн; 3) но среди этих записей будут и такие, где помимо ключевого словаИнн будут через запятую и другие. Размножением в поле КлСлова остается только слово Инн; 4) далее фильтруются все записи, которые не содержат ключевого словаИнн. Строки, удовлетворяющие фильтру, уничтожаются. Именно строки; 5) подобная процедура проделывается для каждого ключевого слова; 6) все вновь созданные листы копируются в лист Итог. В результате мы получили новый список книг, который стал более чемна 10% больше. Назовем его Книги80 (по числу записей). Запустим приложение Microsoft Access из пакета Microsoft Office.Чтобы импортировать Список Excel в базу данных Access, необходимо создать базу данных. Создадим базу данных на основе Пустой базы данныхна рабочем столе (рис. 4.3) и назовем ее Книги. Автоматически будет создана Таблица 1, которую несколько позжеможно будет удалить (рис. 4.4).

    Сводная из базы данных Access

    Предположим, что у вас есть большая база данных. Назовем её «products». И под большая я подразумеваю порядка 3млн записей(строк) или больше. Непосредственно на один лист Excel такое количество данных точно не поместится. Можно, конечно, хранить и на разных листах. Тогда можно воспользоваться статьей Сводная таблица из нескольких листов. Но во-первых, данный метод работает не очень стабильно и может требовать изменений в зависимости от версии Excel и так же требует разрешения выполнения макросов, а во-вторых, для такого количества записей это не лучшее решение, т.к. хранить такое количество данных в книгах Excel не совсем правильно. Поэтому даже если у вас есть несколько книг/листов, забитых нужными данными по полной и надо эти данные объединить для дальнейшего анализа сводной таблицей — то самое лучшее на мой взгляд решение, это объединить их через MS Access в одну таблицу и потом уже построить сводную на основании таблицы не составит труда.

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

    • все таблицы должны содержать одинаковое количество столбцов с полностью идентичными заголовками
    • заголовки не должны содержать переносов строк, тире, дефисов, точек, запятых. Лучше вообще отказаться от любых знаков препинания и сомнительных символов — оставьте только пробелы между словами(и то даже их лучше заменить нижним подчеркиванием)
    • если в таблицах присутствуют числовые данные, которые впоследствии необходимо будет суммировать — убедитесь, что все данные именно числовые и нет текстовых. Это поможет избежать ошибок импорта
    • необходимо убедиться, что таблицы не содержат пустых строк и столбцов, а так же объединенных ячеек

    Теперь, когда все таблицы готовы можно приступить к импорту данных в Access. Открываете Access и выбираете Создать (New)Новая база данных (Blank database) . Указываете имя базы и месторасположение(папку):

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

    1. переходим на вкладку Внешние данные (External Data) -группа Импорт и связи (Import & Link)Excel.
    2. Выбираем файл, данные из которого необходимо перенести в Access
    3. Указываем Импортировать данные источника в новую таблицу в текущей базе данных (Import the source data into a new table in the current database) :
    4. на следующем шаге будет предложено выбрать лист или именованный диапазон для импорта (Show worksheets, Show named ranges) :

      я выбрал лист «products», т.к. именно так у меня называется лист с данными. Жмем Далее (Next)
    5. на этом шаге просто убеждаемся, что галка Первая строка содержит заголовки столбцов (First Row Contains Column Heading) установлена. Если нет — устанавливаем
    6. жмем Готово (Finish)

    Первая часть базы наполнена. Теперь необходимо дополнить созданную в Access таблицу данными других листов или книг. Для этого повторяем все описанные выше шаги, но на 2-м шаге выбираем Добавить копию записей в конец таблицы (Append a copy of the record to the table) . Тогда данные будут дополнены в уже созданную нами таблицу из первого листа, а не будут записаны в новую(чего нам не надо).
    Все, теперь можно приступать к созданию сводной таблицы.

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

    • Excel 2010Файл (File)Параметры (Options)Панель быстрого доступа (Quick Access Toolbar)
    • Excel 2007Кнопка офисПараметры Excel (Excel options)Панель быстрого доступа (Quick Access Toolbar)
    Читать еще:  Как сделать выпадающий список с условием в excel?

    или непосредственно с панели быстрого доступа:

    Выбрать команды из: Все команды (All Commands) . Ищем там Мастер сводных таблиц и диаграмм (PivotTable and PivotChart Wizard) и переносим на панель быстрого доступа:

    Теперь жмем на эту кнопку и на первом шаге появившегося окна Мастера выбираем во внешнем источнике данных (external data source) :

    на втором шаге жмем кнопку Получить данные (Get Data) :

    В появившемся окне необходимо выбрать MS Access Database или База данных MS Access.

    Тут есть важный момент. Галочка Использовать мастер запросов (Use the Query Wizard to create/edit queries) должна быть включена. Жмем ОК.

    Далее выбираем в правом окне папку, в которой расположена наша база данных. В левом окне выбираем сам файл Базы данных:

    Подтверждаем выбор нажатием кнопки ОК.
    Далее необходимо создать запрос выборки. По сути можно просто нажать на имя таблицы Базы данных и после этого на значек «>»

    Но если вам необходимо будет работать только с некоторыми столбцами из всей таблицы — можно последовательно перенести их в правое поле(предварительно в левом развернув плюсик рядом с именем таблицы). Убрать лишние столбцы из правого поля можно кнопочкой » (Return Data to Microsoft Excel) и жмем Готово. При этом в окне второго шага мастера сводных таблиц и диаграмм правее кнопки Получить данные должна появиться надпись Данные получены (Data fields have been retrieved) :

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

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

    Статья помогла? Поделись ссылкой с друзьями!

    Перемещение данных из Excel в Access

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

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

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

    При перемещении данных из Excel в Access существует три основных этапов процесса.

    Примечание: Подробнее о моделирования данных и связи в приложении Access: Основные сведения о создании баз данных.

    Шаг 1: Импорт данных из Excel в Access

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

    Перед импортом очистки данных

    Перед импортом данных в Access, в Excel это хороший способ:

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

    С помощью команды СЖПРОБЕЛЫ для удаления начальные, конечные и нескольких пробелы.

    Удалите непечатаемые символы.

    Обнаружение и исправление ошибок правописания и знаки пунктуации.

    Удаление повторяющихся строк или повторяющиеся поля.

    Убедитесь, что столбцы данных не содержат смешанные форматов, особенно числа, отформатированные как текст или числа в формате даты.

    Дополнительные сведения в следующих разделах Excel справки:

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

    Выбор оптимального типа данных при импорте

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

    Числового формата Excel

    Тип данных Access

    Текстовое поле Memo

    Тип данных текст Access сохраняет буквенно-цифровые данные до 255 знаков. Тип данных МЕМО Access сохраняет буквенно-цифровые данные до 65535 знаков.

    Выберите команду Записка во избежание ошибок усечения все данные.

    Число, процент, дроби, научных

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

    Выберите двойной во избежание ошибок преобразования данных.

    Access и Excel для хранения дат и используется то же число последовательных даты. В приложении Access, больше диапазон дат: от от -657434 (1 января 100 г. н.э.) до 2 958 465 (31 декабря 9999 г. н.э.).

    Так как Access не распознает система дат 1904 (используется в Excel для компьютеров Макинтош), необходимо преобразование дат в Excel или Access, чтобы избежать путаницы.

    Access и Excel оба содержат значения времени с помощью одинаковый тип данных.

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

    В Access тип данных Currency хранит данные в виде числа 8-байтовое с точностью до четырех десятичных разрядов и используется для хранения финансовых данных и предотвратить округления значений.

    Выберите параметр Денежный, обычно используется по умолчанию.

    Доступ к использует -1 для всех значений Да и значение 0 для без значения, тогда как Excel использует 0 и 1 для всех TRUE значения для всех значений FALSE.

    Выберите Да/Нет, которая автоматически преобразует исходные.

    Гиперссылки в Excel и Access содержит URL-адрес или веб-адрес, который можно щелкнуть и следуйте.

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

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

    Дополнительные сведения приведены в разделе справки Access Импорт или связывание данных в книге Excel.

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

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

    Лучше всего использовать Access, где можно легко импортировать и добавлять данные в одной таблице с помощью мастера импорта электронной таблицы. Кроме того можно добавить большой объем данных в одной таблице. Сохранение операции импорта, добавьте их в качестве задачи, запланированные Microsoft Outlook и даже с помощью макросов для автоматизации процесса.

    Шаг 2: Нормализация данных с помощью мастера анализа таблиц

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

    1. Перетащите выделенные столбцы в новую таблицу и автоматическое создание связей

    2. с помощью кнопки команды переименовать таблицы, Добавление первичного ключа, сделать существующего столбца первичного ключа и Отмена последнего действия

    Этот мастер можно использовать для выполнения следующих действий:

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

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

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

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

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

    Шаг 3: Подключение к данным Access из Excel

    После были нормализовать данных в Access и запрос или таблица была создана, восстанавливает исходные данные, это просто о соединении для доступа к данным из Excel. Теперь в Access как внешнего источника данных и данных, могут быть подключены к книге через подключение к данным, — это контейнер, который используется для поиска информации, войдите в систему и получить доступ к внешнему источнику данных. Сведения о подключении хранится в книге, а также могут быть сохранены в файл подключения, например файл подключения к данным Office (ODC) (.odc с расширением) или имя источника данных (с расширением .dsn). После подключения к внешним данным, можно также автоматического обновления (или) книгу Excel из Access при каждом обновлении данных в Access.

    Читать еще:  Как сделать сообщение об ошибке в excel?

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

    Получение данных в Access

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

    Пример данных в форму без нормализовать

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

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

    При упоминании баз данных (БД) первым делом, конечно, в голову приходят всякие умные слова типа SQL, Oracle, 1С или хотя бы Access. Безусловно, это очень мощные (и недешевые в большинстве своем) программы, способные автоматизировать работу большой и сложной компании с кучей данных. Беда в том, что иногда такая мощь просто не нужна. Ваш бизнес может быть небольшим и с относительно несложными бизнес-процессами, но автоматизировать его тоже хочется. Причем именно для маленьких компаний это, зачастую, вопрос выживания.

    Для начала давайте сформулируем ТЗ. В большинстве случаев база данных для учета, например, классических продаж должна уметь:

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

    Со всем этим вполне может справиться Microsoft Excel, если приложить немного усилий. Давайте попробуем это реализовать.

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

    Информацию о товарах, продажах и клиентах будем хранить в трех таблицах (на одном листе или на разных — все равно). Принципиально важно, превратить их в «умные таблицы» с автоподстройкой размеров, чтобы не думать об этом в будущем. Это делается с помощью команды Форматировать как таблицу на вкладке Главная (Home — Format as Table) . На появившейся затем вкладке Конструктор (Design) присвоим таблицам наглядные имена в поле Имя таблицы для последующего использования:

    Итого у нас должны получиться три «умных таблицы»:

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

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

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

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

    В ячейке B3 для получения обновляемой текущей даты-времени используем функцию ТДАТА (NOW) . Если время не нужно, то вместо ТДАТА можно применить функцию СЕГОДНЯ (TODAY) .

    В ячейке B11 найдем цену выбранного товара в третьем столбце умной таблицы Прайс с помощью функции ВПР (VLOOKUP) . Если раньше с ней не сталкивались, то сначала почитайте и посмотрите видео тут.

    В ячейке B7 нам нужен выпадающий список с товарами из прайс-листа. Для этого можно использовать команду Данные — Проверка данных (Data — Validation) , указать в качестве ограничения Список (List) и ввести затем в поле Источник (Source) ссылку на столбец Наименование из нашей умной таблицы Прайс:

    Аналогичным образом создается выпадающий список с клиентами, но источник будет уже:

    Функция ДВССЫЛ (INDIRECT) нужна, в данном случае, потому что Excel, к сожалению, не понимает прямых ссылок на умные таблицы в поле Источник. Но та же ссылка «завернутая» в функцию ДВССЫЛ работает при этом «на ура» (подробнее об этом было в статье про создание выпадающих списков с наполнением).

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

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

    Т.е. в ячейке A20 будет ссылка =B3, в ячейке B20 ссылка на =B7 и т.д.

    Теперь добавим элементарный макрос в 2 строчки, который копирует созданную строку и добавляет ее к таблице Продажи. Для этого жмем сочетание Alt+F11 или кнопку Visual Basic на вкладке Разработчик (Developer) . Если эту вкладку не видно, то включите ее сначала в настройках Файл — Параметры — Настройка ленты (File — Options — Customize Ribbon) . В открывшемся окне редактора Visual Basic вставляем новый пустой модуль через меню Insert — Module и вводим туда код нашего макроса:

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

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

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

    Шаг 4. Связываем таблицы

    Перед построением отчета свяжем наши таблицы между собой, чтобы потом можно было оперативно вычислять продажи по регионам, клиентам или категориям. В старых версиях Excel для этого потребовалось бы использовать несколько функций ВПР (VLOOKUP) для подстановки цен, категорий, клиентов, городов и т.д. в таблицу Продажи. Это требует времени и сил от нас, а также «кушает» немало ресурсов Excel. Начиная с Excel 2013 все можно реализовать существенно проще, просто настроив связи между таблицами.

    Для этого на вкладке Данные (Data) нажмите кнопку Отношения (Relations) . В появившемся окне нажмите кнопку Создать (New) и выберите из выпадающих списков таблицы и названия столбцов, по которым они должны быть связаны:

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

    Само-собой, аналогичным образом связываются и таблица Продажи с таблицей Клиенты по общему столбцу Клиент:

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

    Шаг 5. Строим отчеты с помощью сводной

    Теперь для анализа продаж и отслеживания динамики процесса, сформируем для примера какой-нибудь отчет с помощью сводной таблицы. Установите активную ячейку в таблицу Продажи и выберите на ленте вкладку Вставка — Сводная таблица (Insert — Pivot Table) . В открывшемся окне Excel спросит нас про источник данных (т.е. таблицу Продажи) и место для выгрузки отчета (лучше на новый лист):

    Жизненно важный момент состоит в том, что нужно обязательно включить флажок Добавить эти данные в модель данных (Add data to Data Model) в нижней части окна, чтобы Excel понял, что мы хотим строить отчет не только по текущей таблице, но и задействовать все связи.

    После нажатия на ОК в правой половине окна появится панель Поля сводной таблицы, где нужно щелкнуть по ссылке Все, чтобы увидеть не только текущую, а сразу все «умные таблицы», которые есть в книге.А затем можно, как и в классической сводной таблице, просто перетащить мышью нужные нам поля из любых связанных таблиц в области Фильтра, Строк, Столбцов или Значений — и Excel моментально построит любой нужный нам отчет на листе:

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

    Также, выделив любую ячейку в сводной и нажав кнопку Сводная диаграмма (Pivot Chart) на вкладке Анализ (Analysis) или Параметры (Options) можно быстро визуализировать посчитанные в ней результаты.

    Шаг 6. Заполняем печатные формы

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

    Предполагается, что в ячейку C2 пользователь будет вводить число (номер строки в таблице Продажи, по сути), а затем нужные нам данные подтягиваются с помощью уже знакомой функции ВПР (VLOOKUP) и функции ИНДЕКС (INDEX) .

    Ссылка на основную публикацию
    Adblock
    detector