Как сделать программу с базой данных excel?

Как создать в программном обеспечении Excel базу данных?

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

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

БД – инструмент специалиста

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

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

С тем, что такое БД все понятно, а вот как ее создать в Excel, давайте разбираться дальше.

Где хранятся данные в Excel?

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

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

Иными словами, БД – это совокупная таблица, содержащая множество важной информации.

Формат ячеек Excel

В Excel можно прописывать любую информацию – числа, даты рождения, текст произвольный, проценты, символы. Чтобы в таблицу корректно занести данные, нужно определиться с форматом ячейки и выбрать соответствующий занесенным данным. Меню «Формат ячеек» позволят изменять формат оных вручную. Это можно делать и автоматически, перетаскивая курсор в виде плюса, выделяя нужные ячейки для установки в них нужного формата отражения информации.

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

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

Автоформа Excel, для чего она нужна?

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

Создание заголовков БД в Excel

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

После того, как первая строка БД закреплена, определяются границы ячеек.

Работа над проектом

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

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

Раскрывающиеся списки: как их создать в Excel?

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

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

Далее нужно перейти на тот лист, где были записаны сведения о людях под названием «Родители», открыть окно для создания имени. Дляэтого кликаем по вкладке «Формулы и нажимаем «Присвоить имя». В поле имени записывается «ФИО_родители_выбор». А вот в поле диапазона значений что указать?

Excel – диапазон значений

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

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

Читать еще:  Как в excel сделать надпись за текстом?

Очень важным в определении диапазона ячеек является начало таблицы – координаты верхней левой ячейки. Иными словами эта ячейка именуется А5, а также определяем нижнюю правую ячейку.

Значение последней установим 1 , а значение последней определит формула СЧЕТ3 (Родители!$B$5:$I$5).

Таким образом, в поле диапазона прописываем = СМЕЩ(Родители!$A$5;0;0;СЧЁТЗ(Родители!$A:$A)-1;1). Жмем на клавишу ОКЕЙ и во всех последующих столбцах заменяем А на В, С…

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

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

Красочное оформление базы данных

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

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

Конкурент Excel — новый программный продукт Access

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

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

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

Итак, чтобы выполнить это действие, нужно скопировать базу данных, располагающуюся на листе Эксель, после чего открыть поле в Access – Таблица – Представления – Режим таблицы – Вставить.

Также можно из одной программы в другую импортировать файлы. Для этого нужно открыть Access – Импорт – выбрать Эксель из списка программ – Ок.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читать еще:  Как сделать кнопку в excel 2003?

В ячейке 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) .

Создание базы данных в Excel по клиентам с примерами и шаблонами

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

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

Внешний вид рабочей области программы – таблица. А реляционная база данных структурирует информацию в строки и столбцы. Несмотря на то что стандартный пакет MS Office имеет отдельное приложение для создания и ведения баз данных – Microsoft Access, пользователи активно используют Microsoft Excel для этих же целей. Ведь возможности программы позволяют: сортировать; форматировать; фильтровать; редактировать; систематизировать и структурировать информацию.

То есть все то, что необходимо для работы с базами данных. Единственный нюанс: программа Excel — это универсальный аналитический инструмент, который больше подходит для сложных расчетов, вычислений, сортировки и даже для сохранения структурированных данных, но в небольших объемах (не более миллиона записей в одной таблице, у версии 2010-го года выпуска ).

Структура базы данных – таблица Excel

База данных – набор данных, распределенных по строкам и столбцам для удобного поиска, систематизации и редактирования. Как сделать базу данных в Excel?

Вся информация в базе данных содержится в записях и полях.

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

Поле – столбец в БД, содержащий однотипные данные обо всех объектах.

Записи и поля БД соответствуют строкам и столбцам стандартной таблицы Microsoft Excel.

Если Вы умеете делать простые таблицы, то создать БД не составит труда.

Создание базы данных в Excel: пошаговая инструкция

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

Как создать базу данных клиентов в Excel:

  1. Вводим названия полей БД (заголовки столбцов).
  2. Вводим данные в поля БД. Следим за форматом ячеек. Если числа – то числа во всем столбце. Данные вводятся так же, как и в обычной таблице. Если данные в какой-то ячейке – итог действий со значениями других ячеек, то заносим формулу.
  3. Чтобы пользоваться БД, обращаемся к инструментам вкладки «Данные».
  4. Присвоим БД имя. Выделяем диапазон с данными – от первой ячейки до последней. Правая кнопка мыши – имя диапазона. Даем любое имя. В примере – БД1. Проверяем, чтобы диапазон был правильным.

Основная работа – внесение информации в БД – выполнена. Чтобы этой информацией было удобно пользоваться, необходимо выделить нужное, отфильтровать, отсортировать данные.

Как вести базу клиентов в Excel

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

  1. Выделяем тот диапазон, который нужно отсортировать. Для целей нашей выдуманной компании – столбец «Дата заключения договора». Вызываем инструмент «Сортировка».
  2. При нажатии система предлагает автоматически расширить выделенный диапазон. Соглашаемся. Если мы отсортируем данные только одного столбца, остальные оставим на месте, то информация станет неправильной. Открывается меню, где мы должны выбрать параметры и значения сортировки.

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

Теперь менеджер видит, с кем пора перезаключить договор. А с какими компаниями продолжаем сотрудничество.

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

  1. Одновременным нажатием кнопок Ctrl + F или Shift + F5. Появится окно поиска «Найти и заменить».
  2. Функцией «Найти и выделить» («биноклем») в главном меню.

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

В программе Excel чаще всего применяются 2 фильтра:

  • Автофильтр;
  • фильтр по выделенному диапазону.

Автофильтр предлагает пользователю выбрать параметр фильтрации из готового списка.

  1. На вкладке «Данные» нажимаем кнопку «Фильтр».
  2. После нажатия в шапке таблицы появляются стрелки вниз. Они сигнализируют о включении «Автофильтра».
  3. Чтобы выбрать значение фильтра, щелкаем по стрелке нужного столбца. В раскрывающемся списке появляется все содержимое поля. Если хотим спрятать какие-то элементы, сбрасываем птички напротив их.
  4. Жмем «ОК». В примере мы скроем клиентов, с которыми заключали договоры в прошлом и текущем году.
  5. Чтобы задать условие для фильтрации поля типа «больше», «меньше», «равно» и т.п. числа, в списке фильтра нужно выбрать команду «Числовые фильтры».
  6. Если мы хотим видеть в таблице клиентов, с которыми заключили договор на 3 и более лет, вводим соответствующие значения в меню пользовательского автофильтра.

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

  1. Выделяем те данные, информация о которых должна остаться в базе видной. В нашем случае находим в столбце страна – «РБ». Щелкаем по ячейке правой кнопкой мыши.
  2. Выполняем последовательно команду: «фильтр – фильтр по значению выделенной ячейки». Готово.

Если в БД содержится финансовая информация, можно найти сумму по разным параметрам:

  • сумма (суммировать данные);
  • счет (подсчитать число ячеек с числовыми данными);
  • среднее значение (подсчитать среднее арифметическое);
  • максимальные и минимальные значения в выделенном диапазоне;
  • произведение (результат умножения данных);
  • стандартное отклонение и дисперсия по выборке.

Порядок работы с финансовой информацией в БД:

  1. Выделить диапазон БД. Переходим на вкладку «Данные» — «Промежуточные итоги».
  2. В открывшемся диалоге выбираем параметры вычислений.

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

Готовые образцы шаблонов для ведения клиентской базы по сегментам.

  1. Шаблон для менеджера, позволяющий контролировать результат обзвона клиентов. Скачать шаблон для клиентской базы Excel. Образец:
  2. Простейший шаблон.Клиентская база в Excel скачать бесплатно. Образец:

Шаблоны можно подстраивать «под себя», сокращать, расширять и редактировать.

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