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

Содержание

Создание базы данных в 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 и вводим туда код нашего макроса:

Sub Add_Sell()
    Worksheets("Форма ввода").Range("A20:E20").Copy                         'копируем строчку с данными из формы
    n = Worksheets("Продажи").Range("A100000").End(xlUp).Row                'определяем номер последней строки в табл. Продажи
    Worksheets("Продажи").Cells(n + 1, 1).PasteSpecial Paste:=xlPasteValues 'вставляем в следующую пустую строку
    Worksheets("Форма ввода").Range("B5,B7,B9").ClearContents               'очищаем форму
End Sub

Теперь можно добавить к нашей форме кнопку для запуска созданного макроса, используя выпадающий список Вставить на вкладке Разработчик (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. Таким образом, мы располагаем всей собранной воедино и ясной информацией чтобы использовать её, когда она нам нужна.

Что такое база данных в Excel

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

Образцы базы данных

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

  • База данных для счетов-фактур в Excel
  • База данных для склада в Excel
  • База данных по продажам в Excel
  • База данных для инвентаризации в Excel
  • База данных клиентов в Excel

База данных в Excel для бесплатного скачивания

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

Шаблон базы данных для Excel

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

Создание базы данных в 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 и вводим туда код нашего макроса:

Sub Add_Sell()
    Worksheets("Форма ввода").Range("A20:E20").Copy                         'копируем строчку с данными из формы
    n = Worksheets("Продажи").Range("A100000").End(xlUp).Row                'определяем номер последней строки в табл. Продажи
    Worksheets("Продажи").Cells(n + 1, 1).PasteSpecial Paste:=xlPasteValues 'вставляем в следующую пустую строку
    Worksheets("Форма ввода").Range("B5,B7,B9").ClearContents               'очищаем форму
End Sub

Теперь можно добавить к нашей форме кнопку для запуска созданного макроса, используя выпадающий список Вставить на вкладке Разработчик (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 » 28 Апрель 2011       Дмитрий       90048 просмотров

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

  • таблица должна обязательно содержать заголовки столбцов. Эти заголовки должны располагаться строго в одной строке, не должны содержать объединенных и пустых ячеек.
  • таблица должна быть неделимая, т.е. не должна содержать полностью пустых строк и столбцов, а так же объединенных ячеек
  • в каждом столбце должна содержаться однотипная информация: если в столбце должны содержаться даты, значит кроме дат там не должно быть ничего другого; если в столбце числа(суммы, кол-во) — значит должны быть только числа. Не следует при отсутствии чисел оставлять ячейку пустой или ставить пробел. Вместо этого необходимо ставить 0.

Для разбора функции баз данных возьмем следующий пример таблицы:
Пример таблицы
Данная таблица отвечает всем требованиям для работы с функциями баз данных, однако, чтобы более удобно и гибко работать с функциями баз данных лучше сместить таблицу данных на несколько строк вниз, а выше добавить таблицу критериев, где будут формироваться условия отбора данных из основной таблицы:
Рабочая таблица с критериями
Именно для этой таблицы будут приведены все примеры описаний функций. И критерии заданы следующие: отбирать из поля «Дерево» Яблони с высотой больше 3 и меньше 6 и Вишни, со значением в поле «Возраст» больше 8. Если посмотреть на таблицу данных(из которой будут отбираться данные и производится расчеты функциями), то этим критериям отвечают только две строки: строки 9 и 10 листа.
Как видно, в качестве критериев можно указывать выражение в виде: >6, , 0(не равно нулю), >=7, . Так же знаки равенства и сравнения можно применять и с текстовыми данными, например: «Яблоня».

Все функции из категории баз данных имеют три одинаковых аргумента:
Функция(база_данных; поле; критерий)
база_данных — ссылка на ячейки данных таблицы, включая заголовок(A6:E12).
поле — в данном аргументе можно записать как непосредственно текст с названием столбца в кавычках («Дерево», «Возраст» или «Урожай»), так и число, задающее положение столбца в таблице: 1 — для первого поля(столбца) в таблице «Дерево», 2 — для второго поля «Высота», 3 — для третьего поля «Возраст» и так далее.
критерий — ссылка на диапазон ячеек с условиями отбора(A1:F3). Функция отберет данные из таблицы, которые удовлетворяют условиям, указанным в ячейках критериев. В ссылке на критерии обязательно должны быть включены названия столбцов, для которых выполняется отбор данных.

  • ДСРЗНАЧ(DAVERAGE) — Вычисляет среднее значение выбранных записей базы данных:
    =ДСРЗНАЧ(A6:E12;5;A1:F3)
    =ДСРЗНАЧ(A6:E12;»Прибыль»;A1:F3)
    =DAVERAGE(A6:E12,5,A1:F3)вернет значение 90 000р., т.к. сумма прибыли отобранных записей равна 180 000р., а всего отобрано 2 записи. 180 000/2 = 90 000.
  • БСЧЁТ(DCOUNT) — Подсчитывает количество числовых ячеек в базе данных:
    =БСЧЁТ(A6:E12;5;A1:F3)
    =БСЧЁТ(A6:E12;»Прибыль»;A1:F3)
    =DCOUNT(A6:E12,5,A1:F3)вернет число 2, т.к. только две строки в таблице отвечают критериям
  • БСЧЁТА(DCOUNTA) — Подсчитывает количество непустых ячеек в базе данных:
    =БСЧЁТА(A6:E12;4;A1:F3)
    =БСЧЁТА(A6:E12;»Прибыль»;A1:F3)
    =DCOUNTA(A6:E12,4,A1:F3)вернет 2, т.е. подсчитает в отвечающих критериям строках количество непустых ячеек в столбце «Прибыль»
  • БИЗВЛЕЧЬ(DGET) — Извлекает из базы данных одну запись, удовлетворяющую заданному условию:
    =БИЗВЛЕЧЬ(A6:E12;5;A1:F3)
    =БИЗВЛЕЧЬ(A6:E12;»Прибыль»;A1:F3)
    =DGET(A6:E12,5,A1:F3)для заданных условий вернет значение ошибки #ЧИСЛО!(#NUM!), т.к. этим условиям отвечает более одной записи. Если же указать диапазон для критерия как:
    =БИЗВЛЕЧЬ(A6:E12;5;A1:F2)то функция вернет значение 75 000р., т.е. единственную запись о прибыли для Яблонь с высотой больше 3 и меньше 6 (в данный промежуток попадает лишь строка 10 — Яблона, высота 5)
  • ДМАКС(DMAX) — Находит максимальное значение среди выделенных записей базы данных:
    =ДМАКС(A6:E12;5;A1:F3)
    =ДМАКС(A6:E12;»Прибыль»;A1:F3)
    =DMAX(A6:E12,5,A1:F3)вернет сумму 105 000р., т.к. это максимальная прибыль из всех отвечающих критериям строк.
  • ДМИН(DMIN) — Находит минимальное значение среди выделенных записей базы данных:
    =ДМИН(A6:E12;5;A1:F3)
    =ДМИН(A6:E12;»Прибыль»;A1:F3)
    =DMIN(A6:E12,5,A1:F3)вернет сумму 75 000р., т.к. это минимальная прибыль из всех строк, отвечающих критериям
  • БДПРОИЗВЕД(DPRODUCT) — Перемножает значения определенного поля в записях базы данных, удовлетворяющих условию:
    =БДПРОИЗВЕД(A6:E12;3;A1:F3)
    =БДПРОИЗВЕД(A6:E12;»Возраст»;A1:F3)
    =DPRODUCT(A6:E12,3,A1:F3)вернет 210, т.к. будут перемножены все значения столбца «Возраст», отвечающие критериям(14*15=210)
  • ДСТАНДОТКЛ(DSTDEV) — Оценивает стандартное отклонение по выборке из выделенных записей базы данных:
    =ДСТАНДОТКЛ(A6:E12;4;A1:F3)
    =ДСТАНДОТКЛ(A6:E12;»Урожайность»;A1:F3)
    =DSTDEV(A6:E12,4,A1:F3)вернет 0,707107, т.е. оценку стандартного отклонения урожайности по указанным критериям.
  • ДСТАНДОТКЛП(DSTDEVP) — Вычисляет стандартное отклонение по генеральной совокупности из выделенных записей базы данных:
    =ДСТАНДОТКЛП(A6:E12;4;A1:F3)
    =ДСТАНДОТКЛП(A6:E12;»Урожайность»;A1:F3)
    =DSTDEVP(A6:E12,4,A1:F3)вернет 0,5, т.е. точное стандартное отклонение урожайности по указанным критериям, если считать, что данные в базе данных описывают генеральную совокупность всех деревьев в саду.
  • БДСУММ(DSUM) — Суммирует числа в поле для записей базы данных, удовлетворяющих условию:
    =БДСУММ(A6:E12;5;A1:F3)
    =БДСУММ(A6:E12;»Прибыль»;A1:F3)
    =DSUM(A6:E12,5,A1:F3)вернет сумму прибыли всех строк, отвечающих критериям, т.е. 180 000р.
    =БДСУММ(A6:E12;5;A1:A2)
    =DSUM(A6:E12,5,A1:A2)вернет сумму прибыли от всех Яблонь, т.е. 225 000р.
  • БДДИСП(DVAR) — Оценивает дисперсию по выборке из выделенных записей базы данных:
    =БДДИСП(A6:E12;4;A1:A2)
    =БДДИСП(A6:E12;»Урожайность»;A1:A2)
    =DVAR(A6:E12,4,A1:A2)вернет 0,5, что будет оценкой дисперсии урожайности по указанным критериям, если считать, что данные в таблице являются выборкой из генеральной совокупности всех деревьев в саду
  • БДДИСПП(DVARP) — Вычисляет дисперсию по генеральной совокупности из выделенных записей базы данных:
    =БДДИСПП(A6:E12;4;A1:A2)
    =БДДИСПП(A6:E12;»Урожайность»;A1:A2)
    =DVARP(A6:E12,4,A1:A2)вернет 10,66667, т.е. точную дисперсию урожайности Яблонь и Вишень, если считать, что данные в базе данных описывают генеральную совокупность всех деревьев в саду

Функции базы данных (справка) — Служба поддержки Office

Важно: Вычисляемые результаты формул и некоторые функции листа Excel могут несколько отличаться на компьютерах под управлением Windows с архитектурой x86 или x86-64 и компьютерах под управлением Windows RT с архитектурой ARM. Дополнительные сведения об этих различиях.

Чтобы просмотреть подробную справку о функции, перейдите по нужной ссылке в представленном ниже списке.

Функция

Описание

ДСРЗНАЧ

Возвращает среднее значение выбранных записей базы данных.

БСЧЁТ

Подсчитывает количество числовых ячеек в базе данных.

БСЧЁТА

Подсчитывает количество непустых ячеек в базе данных.

БИЗВЛЕЧЬ

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

ДМАКС

Возвращает максимальное значение среди выделенных записей базы данных.

ДМИН

Возвращает минимальное значение среди выделенных записей базы данных.

БДПРОИЗВЕД

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

ДСТАНДОТКЛ

Оценивает стандартное отклонение по выборке из выделенных записей базы данных.

ДСТАНДОТКЛП

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

БДСУММ

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

БДДИСП

Оценивает дисперсию по выборке из выделенных записей базы данных.

БДДИСПП

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

См. также

Функции Excel (по категориям)

Функции Excel (по алфавиту)

Поиск в базе данных Excel — Трюки и приемы в Microsoft Excel

Представим на минуту, что наш журнал контроля изменений содержит много страниц, а количество записей столь велико, что об удобстве поиска интересующей нас информации вообще не приходится говорить. Как, например, узнать, сколько в журнале контроля содержится активных запросов на внесение изменений, не прибегая к физическому просмотру каждой строки (записи) этого журнала? Excel может помочь нам в решении этой задачи. Для этого мы можем воспользоваться встроенной функцией DCOUNTA (БСЧЁТА — в русифицированной версии Excel).

Во-первых, нам придется освежить в памяти фундаментальные знания о базах данных Excel. Например то, что база в Excel состоит из данных, представленных в табличном формате. Каждый столбец такой таблицы представляет собой одно из полей данных, а каждая строка является отдельной записью базы данных. Основные элементы любой базы данных показаны на примере журнала контроля изменений для проекта Grant St. Move.

В данном случае строка заголовков журнала контроля изменений охватывает ячейки с А14 по Н14. Эта строка содержит названия полей (или столбцов) для каждого из элементов данных. Строки 15, 16 и 17 содержат записи базы данных. Каждая строка представляет собой одну запись. Помните: между записями базы данных не должно быть пустых строк!

Воспользуемся встроенной функцией Excel DCOUNTA (БСЧЁТА) для поиска интересующих нас данных в этой базе. Начнем с перехода на вкладку Formulas (Формулы). Как видите, в группе Function Library (Библиотека функций) этой вкладки не предусмотрена кнопка для активизации перечня встроенных функций, предназначенных для работы с базами данных. Чтобы получить доступ к функциям этой категории, щелкните на кнопке Function Wizard (Вставить функцию) (как показано далее, на рис. 2). На экране появится диалоговое окно Function Wizard (Мастер функций). Из раскрывающегося списка Or Select a Category (Категория) выберите элемент Database (Работа с базой данных), а из списка Select a function (Выберите функцию) — элемент DCOUNTA (БСЧЁТА).

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

  1. Совокупность из двух строк. Первая из этих строк содержит точную копию информации в строке заголовка, а вторая строка — информацию о критериях поиска в базе данных.
  2. Формула DCOUNTA (БСЧЁТА).

Вы заметите, что мы уже фактически создали три отдельных диапазона ячеек с критериями поиска в базе данных: А6:Н7, А8:Н9 и А10:Н11. Каждый из них состоит из двух строк реквизитов, которые выполняют роль наших критериев поиска в базе данных (как описано в приведенных выше пунктах 1 и 2). Мы создали три отдельные пары критериев поиска в базе данных, поскольку хотим одновременно вести поиск трех элементов информации.

Обратите внимание и на то, что у нас есть три строки с формулами: А25:В25, А26:В26 и А27:В27. Синтаксис функции DCOUNTA (ячейка А25) отображен в строке формул. Этот механизм действует следующим образом. Строки критериев говорят Excel о том, какую информацию вы хотите отыскать. В данном примере мы пытаемся найти текстовую информацию. В первых строках критериев, А6:Н7, мы ищем слово «Denied» (Отвергнут). Ячейка F7 содержит интересующую нас текстовую информацию (Отвергнут). Однако поскольку мы хотим найти текстовую информацию, то должны использовать два знака равенства, а именно: =»=Отвергнут».

Нам нужно ввести два знака равенства, заключив с двух сторон в кавычки второй знак равенства и собственно текст. Если бы мы ввели «Отвергнут» с одним знаком равенства, то тем самым как бы попросили Excel поместить содержимое диапазона под именем «отвергнут» в эту ячейку. Регистр клавиатуры (верхний или нижний) в данном случае не имеет значения, если набранный вами текст в точности соответствует тексту, который вы хотите найти. При поиске численной информации вам нужно было бы ввести только один знак равенства и число, которое вы хотите найти (=16). В этом случае не требуются ни кавычки, ни двойные знаки равенства.

Теперь нам нужно ввести формулу DCOUNTA (БСЧЁТА). Соответствующая формула в ячейке А25, =DCOUNTA(А14:Н20,"Принятое решение",А6:Н7), говорит следующее: «Войти в базу данных, состоящую из ячеек от А14 до Н20, и найти в поле «Принятое решение» требуемую информацию. В качестве критериев поиска использовать строки от А6 до Н7. Мне нужно подсчитать, сколько строк соответствует указанному критерию, и вывести на экран полученный результат».

В ячейке А25 Excel отображает число 1, поскольку удалось найти только одну запись, которая соответствует указанному критерию поиска (слово «Отвергнут» в столбце «Принятое решение»). Мы ввели «=Отвергнут» в ячейку В25, «=Утвержден» — в ячейку В26 и «=Отменен» — в ячейку В27, чтобы было понятно, какая формула в каком случае использовалась. Обратите внимание: если решение, принятое по запросу на внесение изменения и указанное в строке 15, заменить на «Утвержден», тогда количество записей, в поле «Принятое решение» которых указано «Отвергнут», стало бы равным нулю, тогда как количество записей, в поле «Принятое решение» которых указано «Утвержден», увеличилось бы до двух.

Несколько замечаний по поводу использования функции DCOUNTA

Пользуясь функцией DCOUNTA (БСЧЁТА), а также другими функциями баз данных, следует помнить несколько важных вещей.

  • Во-первых, вам нет необходимости использовать всю строку заголовков в качестве критериев. Мы сделали это для большей ясности, однако в рассмотренном нами примере вы могли бы запросто использовать в качестве критериев ячейки F6:F7.
  • Во-вторых, критерии поиска, база данных и формула DCOUNTA (БСЧЁТА) вовсе необязательно должны находиться на одном и том же рабочем листе. Например, сама формула DCOUNTA (БСЧЁТА) может находиться на одном рабочем листе, а ссылки на эту формулу — на другом.
  • В-третьих, вы могли бы связать эту электронную таблицу со списком в SharePoint. Это дало бы вам возможность создавать фильтры, группы и специализированные представления для решения той же самой задачи без написания каких-либо формул.

Функции Excel

Для отображения списка встроенных функций определенной категории активизируйте вкладку Formulas (Формулы), которая расположена на ленте Excel. Затем в группе Function Library (Библиотека функций) щелкните на соответствующей кнопке. Например, для отображения списка функций, предназначенных для работы с текстовыми фрагментами, щелкните на кнопке Text (Текстовые), как показано на рис. 1.

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

В качестве альтернативы можно щелкнуть на кнопке Function Wizard (Вставить функцию). Это первая из кнопок группы Function Library (Библиотека функций) вкладки Function (см. рис. 1). В результате на экране появится диалоговое окно Insert Function (Мастер функций).

Мастер функций программы Excel особенно удобен тем, что в его первом диалоговом окне предусмотрена возможность поиска интересующей вас функции по ключевому слову. (Отметим, что мастер функций Excel 2007/2010/2013 ничем не отличается от одноименного программного средства предыдущих версий программы.) Обратите внимание на то, что команда Function Wizard (Вставить функцию) также предусмотрена в нижней части каждого меню, которое появляется на экране после щелчка мышью на любой из кнопок группы Function Library (Библиотека функций) вкладки Function (Функции) (см. рис. 2). Первое диалоговое окно мастера функций показано на рис. 1.

Рис. 2. Первое диалоговое окно мастера функций

В программе Excel имеется достаточно много функций для работы с базами данных, которые в качестве критериев выборки используют введенные вами данные в ячейках рабочего листа. Чтобы получить более подробную информацию о функции DCOUNTA (БСЧЁТА), откройте окно справочной системы Excel и выполните поиск по названию этой функции. В результате ваших действий появится очередная страница справочной системы с перечнем ссылок на описания функций, предназначенных для работы с базами данных. Щелкните на ссылке с названием интересующей вас функции, чтобы открыть следующую страницу справочной системы. На этой странице будет приведена подробная информация о функции и примеры ее применения.

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

Заполнение бланков данными из таблицы

Постановка задачи

Имеем базу данных (список, таблицу — называйте как хотите) с информацией по платежам на листе Данные:

order_database1.gif

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

Шаг 1. Создаем бланк

На другом листе книги (назовем этот лист Бланк) создаем пустой бланк. Можно самостоятельно, можно воспользоваться готовыми бланками, взятыми, например, с сайтов журнала «Главный Бухгалтер» или сайта Microsoft. У меня получилось примерно так:

order_database2.png

В пустые ячейки (Счет, Сумма, Принято от и т.д.) будут попадать данные из таблицы платежей с другого листа — чуть позже мы этим займемся.

Шаг 2. Подготовка таблицы платежей

Прежде чем брать данные из таблицы для нашего бланка, таблицу необходимо слегка модернизировать. А именно — вставить пустой столбец слева от таблицы. Мы будем использовать для ввода метки (пусть это будет английская буква «икс») напротив той строки, данные из которой мы хотим добавить в бланк:

order_database5.gif

Шаг 3. Связываем таблицу и бланк

Для связи используем функцию ВПР (VLOOKUP) — подробнее про нее можно почитать здесь. В нашем случае для того, чтобы вставить в ячейку F9 на бланке номер помеченного «x» платежа с листа Данные надо ввести в ячейку F9 такую формулу:

=ВПР(«x»;Данные!A2:G16;2;0)

=VLOOKUP(«x»;Данные!B2:G16;2;0)

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

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

Для вывода суммы прописью я воспользовался функцией Propis из надстройки PLEX.

В итоге должно получиться следующее:

order_database4.png

Шаг 4. Чтобы не было двух «х»…

Если пользователь введет «х» напротив нескольких строк, то функция ВПР будет брать только первое найденное значение. Чтобы не было такой многозначности, щелкните правой кнопкой мыши по ярлычку листа Данные и выберите Исходный текст (Source Code). В появившееся окно редактора Visual Basic скопируйте следующий код:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long
Dim str As String

If Target.Count > 1 Then Exit Sub
If Target.Column = 1 Then
    str = Target.Value
    Application.EnableEvents = False
    r = Cells(Rows.Count, 2).End(xlUp).Row
     Range("A2:A" & r).ClearContents
    Target.Value = str
End If
  Application.EnableEvents = True
End Sub

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

Ну, вот, собственно и все! Пользуйтесь!

Ссылки по теме

 

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *