Работа в ms excel как с базой данных базы данных в excel

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

Как сделать базу данных в Excel, чтобы не было удобно не только хранить, но и обрабатывать данные: формировать отчеты, строить графики, диаграммы и т.д.

Пошаговое создание базы данных в Excel

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

С шапкой определились. Теперь заполняем таблицу. Начинаем с порядкового номера. Чтобы не проставлять цифры вручную, пропишем в ячейках А4 и А5 единицу и двойку, соответственно. Затем выделим их, схватимся за уголок получившегося выделения и продлим вниз на любое количество строк. В небольшом окошечке будет показываться конечная цифра.

Примечание. Данную таблицу можно скачать в конце статьи.

По базе видим, что часть информации будет представляться в текстовом виде (продукт, категория, месяц и т.п.), а часть – в финансовом. Выделим ячейки из шапки с ценой и стоимостью, правой кнопкой мыши вызовем контекстное меню и выберем ФОРМАТ ЯЧЕЕК.

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

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

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

Теперь заполняем таблицу данными.

Важно! При заполнении ячеек, нужно придерживаться единого стиля написания. Т.е. если изначально ФИО сотрудника записывается как Петров А.А., то остальные ячейки должны быть заполнены аналогично. Если где-то будет написано иначе, например, Петров Алексей, то работа с БД будет затруднена.

Таблица готова. В реальности она может быть гораздо длиннее. Мы вписали немного позиций для примера. Придадим базе данных более эстетичный вид, сделав рамки. Для этого выделяем всю таблицу и на панели находим параметр ИЗМЕНЕНИЕ ГРАНИЦ.

Аналогично обрамляем шапку толстой внешней границей.



Функции Excel для работы с базой данных

Теперь обратимся к функциям, которые Excel предлагает для работы с БД.

Работа с базами данных в Excel

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

Выделяем шапку таблицы и во вкладке ДАННЫЕ нажимаем ФИЛЬТР (CTRL+SHIFT+L).

У каждой ячейки в шапке появляется черная стрелочка на сером фоне, куда можно нажать и отфильтровать данные. Нажимаем ее у параметра ПРИНИМАЛ ТОВАР и снимаем галочку с фамилии КОТОВА.

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

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

Можно произвести дополнительную фильтрацию. Определим, какие крупы принял Петров. Нажмем стрелочку на ячейке КАТЕГОРИЯ ПРОДУКТА и оставим только крупы.

Вернуть полную БД на место легко: нужно только выставить все галочки в соответствующих фильтрах.

Сортировка данных

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

К примеру, мы хотим отсортировать продукты по мере увеличения цены. Т.е. в первой строке будет самый дешевый продукт, в последней – самый дорогой. Выделяем столбец с ценой и на вкладке ГЛАВНАЯ выбираем СОРТИРОВКА И ФИЛЬТР.

Т.к. мы решили, что сверху будет меньшая цена, выбираем ОТ МИНИМАЛЬНОГО К МАКСИМАЛЬНОМУ. Появится еще одно окно, где в качестве предполагаемого действия выберем АВТОМАТИЧЕСКИ РАСШИРИТЬ ВЫДЕЛЕННЫЙ ДИАПАЗОН, чтобы остальные столбцы тоже подстроились под сортировку.

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

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

Сортировка по условию

Нам нужно извлечь из БД товары, которые покупались партиями от 25 кг и более. Для этого на ячейке КОЛ-ВО нажимаем стрелочку фильтра и выбираем следующие параметры.

В появившемся окне напротив условия БОЛЬШЕ ИЛИ РАВНО вписываем цифру 25. Получаем выборку с указанием продукты, которые заказывались партией больше или равной 25 кг. А т.к. мы не убирали сортировку по цене, то эти продукты расположились еще и в порядке ее возрастания.

Промежуточные итоги

И еще одна полезная функция, которая позволит посчитать сумму, произведение, максимальное, минимальное или среднее значение и т.п. в имеющейся БД. Она называется ПРОМЕЖУТОЧНЫЕ ИТОГИ. Отличие ее от обычных команд в том, что она позволяет считать заданную функцию даже при изменении размера таблицы. Чего невожнможно реалиловать в данном случаи с помощью функции =СУММ(). Рассмотрим на примере.

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

Функция ПРОМЕЖУТОЧНЫЕ ИТОГИ имеет 30 аргументов. Первый статический: код действия. По умолчанию в Excel сумма закодирована цифрой 9, поэтому ставим ее. Второй и последующие аргументы динамические: это ссылки на диапазоны, по которым подводятся итоги. У нас один диапазон: F4:F24. Получилось 19670 руб.

Теперь попробуем снова отсортировать кол-во, оставив только партии от 25 кг.

Видим, что сумма тоже изменилась.

Скачать пример

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

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

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

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

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

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

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

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

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

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



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



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



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

На этом создание каркаса БД закончено.

Урок: Как сделать таблицу в Excel

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

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

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





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



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



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

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

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

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

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

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

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



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

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

   

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



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



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

   Как видим, после этого, строки, где содержатся значения, с которых мы сняли галочки, были скрыты из таблицы.

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



7. Для того, чтобы полностью убрать фильтрацию, жмем на кнопку «Фильтр» на ленте.

Урок: Сортировка и фильтрация данных в Excel

Поиск

При наличии большой БД поиск по ней удобно производить с помощь специального инструмента.

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



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



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

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

Урок: Как сделать поиск в Экселе

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

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

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



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

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

Урок: Как закрепить область в Экселе

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

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

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



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



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



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



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

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

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

Урок: Как сделать выпадающий список в Excel

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

При упоминании баз данных (БД) первым делом, конечно, в голову приходят всякие умные слова типа 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).

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

• Как использовать функцию ВПР (VLOOKUP) для поиска и подстановки значений
• Как заменить ВПР функциями ИНДЕКС и ПОИСКПОЗ
• Автоматическое заполнение форм и бланков данными из таблицы
• Создание отчетов с помощью сводных таблиц

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

​Смотрите также​ заголовком. Вспомогательная табличка​Шаблон для менеджера, позволяющий​ должны выбрать параметры​ то что стандартный​ с помощью сводной​ модуль через меню​Клиенты​ формула все равно​ во вкладке ДАННЫЕ​ строить графики, диаграммы​ полями, меняя название​ учится 25 детей,​ в которой будет​, которая расположена на​ открывшемся окне напротив​ будут урезаны. Ниже​В пакете Microsoft Office​

​ критериев поискового запроса​ контролировать результат обзвона​

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

​ и значения сортировки.​ пакет MS Office​ таблицы. Установите активную​Insert — Module​- город и​

​ будет находиться в​ нажимаем ФИЛЬТР (CTRL+SHIFT+L).​​ и т.д.​​ источника на соответствующее​ значит, и родителей​ вся нужная информация.​

​ ленте в блоке​​ всех пунктов устанавливаем​​ мы разберем, как​ есть специальная программа​ к базе данных,​

​ клиентов. Скачать шаблон​Данные в таблице распределились​ имеет отдельное приложение​ ячейку в таблицу​

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

​и вводим туда​ регион (адрес, ИНН,​

1. ​ ячейке F26.​У каждой ячейки в​

   

2. ​Для начала научимся создавать​ данным ячейкам. Работа​

   

3. ​ будет соответствующее количество.​ Прежде чем приступить​

   

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

​ для создания и​Продажи​

​ код нашего макроса:​​ банковские реквизиты и​Функция ПРОМЕЖУТОЧНЫЕ ИТОГИ имеет​

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

​ шапке появляется черная​ БД с помощью​ над выпадающими списками​ Чтобы не нагромождать​ к решению вопроса,​«Работа с данными»​ на кнопку​

1. ​ функциональной.​​ данных и работы​​ сформулирована, чтобы критерии​

   

2. ​ Excel. Образец:​ договора.​ ведения баз данных​и выберите на​Sub Add_Sell() Worksheets(«Форма​​ т.п.) каждого из​​ 30 аргументов. Первый​

   

3. ​ стрелочка на сером​​ инструментов Excel. Пусть​​ почти завершена. Затем​ базу данных большим​ как сделать базу​.​«OK»​Работа с базами данных,​ с ними –​ однозначно и точно​Простейший шаблон.Клиентская база в​Теперь менеджер видит, с​​ – Microsoft Access,​​ ленте вкладку​ ввода»).Range(«A20:E20»).Copy ‘копируем строчку​ них.​ статический: код действия.​ фоне, куда можно​ мы – магазин.​ выделяем третью ячейку​ числом записей, стоит​ данных в Excel,​Открывается окно проверки видимых​.​ прежде всего, предусматривает​ Access. Тем не​ определяли данные, которые​​ Excel скачать бесплатно.​​ кем пора перезаключить​

   

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

​ возможность упорядочивания, отбора​ менее, многие пользователи​ нужно найти в​ Образец:​ договор. А с​ Microsoft Excel для​ (Insert — Pivot​ формы n =​Продажи​ Excel сумма закодирована​ данные. Нажимаем ее​ данных по поставкам​ через всю таблицу.​

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

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

1. ​ БД. Если же​Шаблоны можно подстраивать «под​ какими компаниями продолжаем​ этих же целей.​ Table)​ Worksheets(«Продажи»).Range(«A100000»).End(xlUp).Row ‘определяем номер​​будет использоваться нами​​ цифрой 9, поэтому​​ у параметра ПРИНИМАЛ​​ различных продуктов от​

   

   ​ База данных в​ информацию, а когда​

   • ​ взаимодействии с ней.​
   • ​выставляем переключатель в​
   • ​ на кнопку​
2. ​ Подключим эти функции​ этих целей более​ функция БИЗВЕЧЬ возвращает​ себя», сокращать, расширять​ сотрудничество.​ Ведь возможности программы​. В открывшемся окне​ последней строки в​​ впоследствии для занесения​​ ставим ее. Второй​

   

3. ​ ТОВАР и снимаем​ разных поставщиков.​​ Excel почти готова!​​ она снова потребуется,​Горизонтальные строки в разметке​ позицию​
   • ​«Фильтр»​​ к нашей БД.​​ знакомое им приложение​ ошибку #ЗНАЧ! –​ и редактировать.​БД в процессе деятельности​​ позволяют: сортировать; форматировать;​​ Excel спросит нас​
   • ​ табл. Продажи Worksheets(«Продажи»).Cells(n​​ в нее совершенных​​ и последующие аргументы​ галочку с фамилии​№п/п​Красивое оформление тоже играет​ они услужливо предоставят​ листа «Эксель» принято​«Список»​на ленте.​Выделяем информацию того поля,​​ – Excel. Нужно​​ значит в базе​​Выполнение поиска по огромным​​ фирмы разрастается до​ фильтровать; редактировать; систематизировать​​ про источник данных​​ + 1, 1).PasteSpecial​​ сделок.​​ динамические: это ссылки​
   • ​ КОТОВА.​Продукт​​ немалую роль в​​ ее опять.​ называть записями, а​. В поле​

   ​Урок:​ по которому собираемся​ отметить, что у​​ данных нет записей,​​ таблицам с тысячами​

   

   ​ невероятных размеров. Найти​ и структурировать информацию.​ (т.е. таблицу​ Paste:=xlPasteValues ‘вставляем в​Само-собой, можно вводить данные​ на диапазоны, по​Таким образом, у нас​

   

4. ​Категория продукта​ создании проекта. Программа​Копируем названия полей и​ вертикальные колонки –​«Источник»​Сортировка и фильтрация данных​ провести упорядочивание. Кликаем​​ этой программы имеется​​ соответствующих критериям поискового​​ позиций информации о​​ нужную информацию становится​

   

5. ​То есть все то,​Продажи​ следующую пустую строку​ о продажах непосредственно​ которым подводятся итоги.​ остаются данные только​Кол-во, кг​ Excel может предложить​ переносим их на​ полями. Можно приступать​устанавливаем знак​ в Excel​ по кнопке «Сортировка»​ весь инструментарий для​ запроса. Если же​​ товарах или объемах​​ все сложнее. Чтобы​

   

   ​ что необходимо для​) и место для​ Worksheets(«Форма ввода»).Range(«B5,B7,B9»).ClearContents ‘очищаем​ в зеленую таблицу​ У нас один​ по Петрову.​

6. ​Цена за кг, руб​ на выбор пользователя​ пустой лист, который​ к работе. Открываем​«=»​При наличии большой БД​ расположенной на ленте​ создания полноценной базы​ возвращена ошибка #ЧИСЛО!​ продаж – это​​ отыскать конкретный текст​​ работы с базами​

   

7. ​ выгрузки отчета (лучше​ форму End Sub​Продажи​​ диапазон: F4:F24. Получилось​​Обратите внимание! При сортировке​

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

Поиск

​ программу и создаем​и сразу после​ поиск по ней​ во вкладке​

1. ​ данных (БД). Давайте​ – значит в​​ непростой вызов для​​ или цифры, можно​ данных. Единственный нюанс:​​ на новый лист):​​Теперь можно добавить к​​, но это не​​ 19670 руб.​

   

2. ​ данных сохраняются не​Месяц поставки​ оформления базы данных.​ необходимо назвать. Пусть​​ новую книгу. Затем​​ него без пробела​​ удобно производить с​​«Данные»​

   

3. ​ выясним, как это​ базе данных более​ большинства пользователей Excel.​ воспользоваться одним из​

   

   ​ программа Excel -​Жизненно важный момент состоит​ нашей форме кнопку​

​ всегда удобно и​​Теперь попробуем снова отсортировать​ только все позиции​

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

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

1. ​ для запуска созданного​ влечет за собой​ кол-во, оставив только​ в столбцах, но​Принимал товар​ очень богатое, нужно​ примеру, «Родители». После​

   

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

​ появление ошибок и​ партии от 25​ и номера соответствующих​С шапкой определились. Теперь​ только выбрать подходящую​ того как данные​ названия полей.​

​ дали ему чуть​​ вкладку​.​

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

​ Excel​В нашем случаи функция​ вынуждены комбинировать многоэтажные​ + F или​ подходит для сложных​ флажок​ список​ опечаток из-за «человеческого​​ кг.​​ строк на листе​ заполняем таблицу. Начинаем​ по душе расцветку.​

1. ​ будут скопированы, под​Полезно узнать о том,​ выше. После этого​«Главная»​Сортировку можно проводить практически​База данных в Экселе​ БИЗВЕЧЬ вернула одно​

   

2. ​ формулы из поисковых​ Shift + F5.​ расчетов, вычислений, сортировки​Добавить эти данные в​Вставить​​ фактора». Поэтому лучше​​Видим, что сумма тоже​

   

3. ​ (они подсвечены синим).​ с порядкового номера.​ Кроме того, совсем​ ними записываем в​ как правильно оформлять​ жмем на кнопку​и на ленте​

   

4. ​ по любому параметру:​ представляет собой структурированный​ значение – без​ функций, которым нужно​ Появится окно поиска​​ и даже для​​ модель данных (Add​​на вкладке​​ будет на отдельном​ изменилась.​ Эта особенность пригодится​​ Чтобы не проставлять​​ необязательно выполнять всю​

   

5. ​ пустые ячейки все​ содержимое ячейки. Если​​«OK»​​ в блоке инструментов​имя по алфавиту;​​ набор информации, распределенный​​ ошибок. Эту функцию​​ еще вычислить соответствующие​​ «Найти и заменить».​​ сохранения структурированных данных,​​ data to Data​Разработчик (Developer — Insert​ листе сделать специальную​Скачать пример​ нам позже.​ цифры вручную, пропишем​ базу данных в​​ необходимые сведения.​​ ваша база данных​

​.​«Редактирование»​дата;​ по столбцам и​ можно так же​ адреса и значения​Функцией «Найти и выделить»​ но в небольших​

​ Model)​ — Button)​ форму для ввода​Получается, что в Excel​Можно произвести дополнительную фильтрацию.​ в ячейках А4​ едином стиле, можно​Для того чтобы база​

​ в Excel будет​​Теперь при попытке ввести​жмем на кнопку​

​число и т.д.​ строкам листа.​ использовать для вывода​ для их аргументов.​ («биноклем») в главном​ объемах (не более​в нижней части​:​ данных примерно такого​ тоже можно создавать​ Определим, какие крупы​ и А5 единицу​ раскрасить одну колонку​ данных MS Excel​ содержать какие-либо денежные​ данные в диапазон,​«Найти и выделить»​В следующем появившемся окне​Согласно специальной терминологии, строки​

​ целой строки за​

lumpics.ru

База данных в Excel: особенности создания, примеры и рекомендации

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

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

​Посредством фильтрации данных программа​ одной таблице, у​ понял, что мы​ ее нарисуете, удерживая​В ячейке B3 для​ легко работать с​ стрелочку на ячейке​ Затем выделим их,​ другую – в​ данных из раскрывающегося​ сразу в соответствующих​ ограничение, будет появляться​Открывается окно, в котором​ ли для сортировки​«записями»​ копирования функции в​ нагружать вычислительные ресурсы​ прячет всю не​ версии 2010-го года​ хотим строить отчет​ нажатой левую кнопку​ получения обновляемой текущей​ ними. При больших​ КАТЕГОРИЯ ПРОДУКТА и​ схватимся за уголок​ зеленый и т.​ списка, необходимо создать​

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

Создание хранилища данных в Excel

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

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

​ТДАТА (NOW)​ очень удобно и​Вернуть полную БД на​ продлим вниз на​Не только лишь Excel​ этого нужно присвоить​ после запятой будет​ между четко установленными​ жмем на кнопку​ её. Выбираем автоматическое​ отдельном объекте.​

Особенности формата ячеек

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

​ В небольшом окошечке​ данных. Microsoft выпустила​ родителях диапазон значений,​ А если где-либо​Если же вы попытаетесь​или​ на кнопку​«полями»​ каждой ее копии​ с базами данных.​ их можно восстановить.​ для удобного поиска,​После нажатия на​ назначить — выбираем​ТДАТА​ (БД) первым делом,​ галочки в соответствующих​ будет показываться конечная​ еще один продукт,​ имена. Переходим на​

Что такое автоформа в «Эксель» и зачем она требуется?

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

Фиксация «шапки» базы данных

​ ячейках произвольные символы,​.​.​ располагается отдельный параметр​ в которую добавим​ данных, которая экспортированная​ всего применяются 2​ Как сделать базу​в правой половине​Add_Sell​СЕГОДНЯ (TODAY)​ приходят всякие умные​В нашем примере БД​Примечание. Данную таблицу можно​ с этим непростым​ записаны все данные​ место и также​ то будет появляться​В первом случае первая​Открывается окно настройки сортировки.​ всех записей.​ функцию СТОЛБЕЦ. Для​

​ в Excel так​ фильтра:​ данных в Excel?​ окна появится панель​. Текст на кнопке​.​

Продолжение работы над проектом

​ слова типа SQL,​ заполнялась в хронологическом​ скачать в конце​ делом. Название ему​ под названием «Родители»​ установить для него​ сообщение об ошибке.​

​ ячейка, в которой​ В поле​То есть, каркасом любой​ этого:​ как показано ниже​Автофильтр;​Вся информация в базе​Поля сводной таблицы​ можно поменять, щелкнув​В ячейке B11 найдем​ Oracle, 1С или​ порядке по мере​ статьи.​ – Access. Так​ и открываем специальное​ соответствующий формат. Таким​ Вам придется вернутся​ имеется указанное значение,​«Сортировать по»​ базы данных в​

Как создать раскрывающиеся списки?

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

​ образом, все ваши​ и внести корректную​ становится активной.​указываем имя поля,​ Excel является обычная​ следующую формулу:​Наша задача найти всю​Автофильтр предлагает пользователю выбрать​ записях и полях.​ по ссылке​ кнопкой мыши и​ в третьем столбце​ Безусловно, это очень​ магазин. Но если​ часть информации будет​ более адаптирована под​ имени. К примеру,​ данные будут оформлены​ запись.​Во втором случае открывается​ по которому она​ таблица.​Скопируйте ее во все​ информацию (номер фактуры,​ параметр фильтрации из​Запись – строка в​

Диапазон значений в Excel

​Все​ выбрав команду​ умной таблицы​ мощные (и недешевые​ нам нужно отсортировать​ представляться в текстовом​ создание базы данных,​ в Excel 2007​ правильно и без​Урок:​ весь перечень ячеек,​ будет проводиться.​

​Итак, прежде всего нам​ ячейки диапазона A3:E3.​ номер клиента, сумма​ готового списка.​ базе данных (БД),​, чтобы увидеть не​Изменить текст​Прайс​ в большинстве своем)​ данные по другому​ виде (продукт, категория,​ чем Excel, то​ это можно сделать,​ ошибок. Все операции​Как сделать выпадающий список​ содержащих это значение.​В поле​ нужно создать таблицу.​Выбрана целая строка информации​ и т.д.), которая​На вкладке «Данные» нажимаем​ включающая информацию об​ только текущую, а​.​с помощью функции​ программы, способные автоматизировать​ принципу, Excel позволяет​ месяц и т.п.),​ и работа в​ кликнув на «Формулы»​ с пустыми полями​

​ в Excel​Урок:​«Сортировка»​Вписываем заголовки полей (столбцов)​ по конкретной фамилии​ относится к одной​ кнопку «Фильтр».​

​ одном объекте.​ сразу все «умные​Теперь после заполнения формы​ВПР (VLOOKUP)​ работу большой и​ сделать и это.​ а часть –​

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

​ контекстное меню «Формат​ своим возможностям специализированным​ Экселе​ она будет выполняться.​Заполняем наименование записей (строк)​Принцип действия формулы​ клиента. Для этой​ таблицы появляются стрелки​ БД, содержащий однотипные​ в книге.А затем​ на нашу кнопку,​

Внешний вид базы данных

​ ней не сталкивались,​ кучей данных. Беда​ отсортировать продукты по​ ячейки из шапки​Но как же сделать​ имени записываем: ФИО_родителя_выбор.​ ячеек».​ программам для создания​Удобно при создании БД​ Для БД лучше​ БД.​ для вывода целой​ цели рекомендуем воспользоваться​ вниз. Они сигнализируют​ данные обо всех​ можно, как и​ и введенные данные​ то сначала почитайте​ в том, что​ мере увеличения цены.​

Как перенести базу данных из Excel в Access

​ с ценой и​ так, чтобы получилась​ Но что написать​Также немаловажно и соответствующее​ баз данных. Тем​ закрепить ячейки с​ всего выбрать параметр​Переходим к заполнению базы​ строки из базы​ функцией Excel для​ о включении «Автофильтра».​ объектах.​ в классической сводной​ будут автоматически добавляться​ и посмотрите видео​

​ иногда такая мощь​ Т.е. в первой​ стоимостью, правой кнопкой​ база данных Access?​ в поле диапазона​ оформление проекта. Лист,​ не менее, у​

​ наименованием записей и​«Значения»​ данными.​ данных:​ работы с базами​Чтобы выбрать значение фильтра,​Записи и поля БД​ таблице, просто перетащить​ к таблице​ тут.​ просто не нужна.​ строке будет самый​ мыши вызовем контекстное​ Excel учитывает такое​ значений? Здесь все​ на котором находится​ него имеется инструментарий,​ полей. При работе​.​После того, как БД​В конструкции функции БИЗВЕЧЬ​

​ данных – БИЗВЛЕЧЬ.​ щелкаем по стрелке​ соответствуют строкам и​ мышью нужные нам​Продажи​В ячейке B7 нам​ Ваш бизнес может​ дешевый продукт, в​ меню и выберем​ желание пользователя. Это​

​ сложнее.​ проект, нужно подписать,​ который в большинстве​ с большой базой​В поле​ заполнена, форматируем информацию​ изменили мы только​ Данная функция на​ нужного столбца. В​ столбцам стандартной таблицы​ поля из любых​, а затем форма​ нужен выпадающий список​ быть небольшим и​ последней – самый​ ФОРМАТ ЯЧЕЕК.​ можно сделать несколькими​Существует несколько видов диапазонов​ чтобы избежать путаницы.​ случаев удовлетворит потребности​ – это просто​«Порядок»​ в ней на​

​ второй аргумент, значение​ основе критериев поискового​

​ раскрывающемся списке появляется​

fb.ru

Создание базы данных в Excel и функции работы с ней

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

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

Пошаговое создание базы данных в Excel

​ прайс-листа. Для этого​ бизнес-процессами, но автоматизировать​ с ценой и​ выберем формат –​• Можно выделить всю​ которым мы работаем,​ система могла отличать​ БД. Учитывая тот​

​ можно использовать команду​ его тоже хочется.​ на вкладке ГЛАВНАЯ​ финансовый. Число десятичных​ информацию, содержащуюся на​ называется динамическим. Это​ простое содержание от​ факт, что возможности​ время на пролистывание​ сортировка. Для разных​ расположение текста относительно​ числа 1. Данная​ отдельности выберите все​ какие-то элементы, сбрасываем​ создать БД не​,​

​ наши таблицы между​Данные — Проверка данных​ Причем именно для​

​ выбираем СОРТИРОВКА И​ знаков поставим 1.​ листе Excel, скопировать​ означает, что все​ заголовков и подписей,​ Эксель, в сравнении​ листа, чтобы посмотреть,​ типов информации в​ ячейки и т.д.).​ функция возвращает номер​ соответствующие строки из​ птички напротив их.​ составит труда.​

​Строк​ собой, чтобы потом​ (Data — Validation)​ маленьких компаний это,​ ФИЛЬТР.​ Обозначение выбирать не​ ее и перенести​ проименованные ячейки в​ следует выделять их​ со специализированными приложениями,​

​ какой строке или​ этом окне высвечиваются​На этом создание каркаса​ текущего столбца для​

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

​ разные значения. Например,​

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

​ жирным шрифтом, при​ намного лучше, то​ значение.​ для текстовых данных​Урок:​Бесспорное преимущество использования функции​ предназначены для работы​ с которыми заключали​ в Excel. Перед​или​ регионам, клиентам или​Список (List)​

​ ТЗ. В большинстве​ цена, выбираем ОТ​

​ уже указано, что​

Функции Excel для работы с базой данных

​ данные, предназначенные для​ Их изменение происходит​ этом не забывая​ в этом плане​

Работа с базами данных в Excel

​Выделяем ячейку, области сверху​ – это будет​Как сделать таблицу в​ БИЗВЛЕЧЬ заключается в​ с базами данных​ договоры в прошлом​ нами стоит задача​Значений​ категориям. В старых​и ввести затем​ случаев база данных​ МИНИМАЛЬНОГО К МАКСИМАЛЬНОМУ.​ цена и стоимость​ копирования, и щелкните​

​ в зависимости от​ помещать названия в​ у разработки компании​

​ и слева от​ значение​ Excel​ автоматизации. Достаточно лишь​ обладают одним общим​ и текущем году.​ – сформировать клиентскую​- и Excel​ версиях Excel для​ в поле​

​ для учета, например,​ Появится еще одно​ в рублях.​

​ правой кнопкой мышки.​ количества значений в​ отдельные, не объединенные​ Microsoft есть даже​ которой нужно закрепить.​«От А до Я»​Для того, чтобы Excel​ изменить критерий и​ свойством. Все они​

​Чтобы задать условие для​ БД. За несколько​ моментально построит любой​ этого потребовалось бы​Источник (Source)​ классических продаж должна​

​ окно, где в​Аналогично поступаем с ячейками,​ В контекстном меню​ определенном диапазоне.​ поля. Это стоит​

Сортировка данных

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

​ использовать несколько функций​ссылку на столбец​ уметь:​ качестве предполагаемого действия​ куда будет вписываться​ нажимайте «Копировать». Затем​Чтобы получился динамический диапазон,​ делать для возможности​Автор: Максим Тютюшев​ сразу под шапкой​«От Я до А»​ просто как диапазон​

​ получаем уже новую​ диапазон запросов к​ «больше», «меньше», «равно»​ компании появилось несколько​ на листе:​ВПР (VLOOKUP)​Наименование​хранить​ выберем АВТОМАТИЧЕСКИ РАСШИРИТЬ​ количество. Формат выбираем​ переключитесь на Access,​

​ необходимо использовать формулу​ использования таких инструментов,​

​Excel является мощным инструментом,​ и справа от​, а для числовых​ ячеек, а именно​ строку информации из​ базе, который необходимо​

Сортировка по условию

​ и т.п. числа,​ десятков постоянных клиентов.​Не забудьте, что сводную​для подстановки цен,​из нашей умной​в таблицах информацию​ ВЫДЕЛЕННЫЙ ДИАПАЗОН, чтобы​ числовой.​ выберите вкладку «Таблица»,​

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

Промежуточные итоги

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

​ Т.к. стоимость рассчитывается​ смело кликайте на​ были заданы аргументы,​Создание базы данных в​ нужных пользователям функций.​«Вид»​или​ атрибуты.​ данные теперь по​ Поэтому в первую​ «Числовые фильтры».​ Знать контактных лиц,​ данных) обновлять, щелкнув​ таблицу​

​:​ клиентам и связывать​Видим, что данные выстроились​ как цена, помноженная​ кнопку «Представление». Выбирайте​ возвращает ссылку на​ Excel – занятие​ К ним относятся​кликаем по кнопке​«По убыванию»​Переходим во вкладку​ номеру клиента 58499.​ очередь мы должны​Если мы хотим видеть​

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

​ эти таблицы между​ по увеличивающейся цене.​

​ на количество, можно​

​ пункт «Режим таблицы»​ исходные данные. В​ трудное и кропотливое.​ графики, таблицы, диаграммы,​«Закрепить области»​.​«Данные»​ Удаляем старый критерий​

exceltable.com

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

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

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

• ​ значения​​Выделяем весь диапазон таблицы.​ сразу же получаем​ к базе. Для​ договор на 3​ клиентов в Excel:​Обновить (Refresh)​
• ​ нас, а также​​ уже:​​формы ввода​ параметра можно через​
• ​ Для этого записываем​ мышки и выбрав​​ не должно встречаться​​ может предложить ему​ функций и так​
• ​«Окно»​​«Мои данные содержат заголовки»​​ Кликаем правой кнопкой​ результат.​ этого:​

​ и более лет,​Вводим названия полей БД​, т.к. автоматически она​ «кушает» немало ресурсов​=ДВССЫЛ(«Клиенты[Клиент]»)​

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

​данных (с выпадающими​ автофильтр. При нажатии​ в ячейке F4​ «Вставить».​ пустых значений. С​ автоматическое заполнение ячеек​ далее. Из этой​. В выпадающем списке​стояла галочка. Если​ мыши. В контекстном​Данную задачу можно было​Выше базы данных добавим​ вводим соответствующие значения​ (заголовки столбцов).​​ этого делать не​​ Excel. Начиная с​​Функция​ списками и т.п.)​​ стрелочки тоже предлагается​ формулу и протягиваем​​• Можно импортировать лист​ ​ этим как раз​​ заранее заданной информацией.​ статьи мы узнаем,​​ выбираем значение​​ её нет, то​

​ меню жмем на​ бы решить и​ 4 пустых строки.​

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

​ Для этого достаточно​​ автофильтра.​​ БД. Следим за​Также, выделив любую ячейку​ можно реализовать существенно​нужна, в данном​

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

​ какие-то​Нам нужно извлечь из​ ячейки в этом​​ Откройте Access, предварительно​​ диапазон. Он задается​ высота строки, размер​ данных в Excel,​.​После ввода всех нужных​«Присвоить имя…»​ формул с комбинациями​ выделить 4 заголовка​Готово!​ форматом ячеек. Если​ в сводной и​

​ проще, просто настроив​ случае, потому что​печатные бланки​​ БД товары, которые​​ столбце. Так, стоимость​ закрыв Excel. В​​ двумя координатами ячеек:​​ и тип шрифта,​​ для чего она​​Теперь наименования полей и​

​ параметров жмем на​.​ функций ИНДЕКС, ПОИСКПОЗ,​ строк листа Excel​​Поэкспериментируем с фильтрацией данных​​ числа – то​​ нажав кнопку​​ связи между таблицами.​ Excel, к сожалению,​(платежки, счета и​ покупались партиями от​ будет подсчитываться автоматически​

​ меню выберите команду​ верхней левой и​ цвет поля и​ нужна, и какие​ записей будут у​​ кнопку​В графе​​ ВПР, ПРОСМОТР, но​ и щелкнуть правой​​ по выделенным ячейкам.​​ числа во всем​Сводная диаграмма (Pivot Chart)​​Для этого на вкладке​​ не понимает прямых​​ т.д.)​​ 25 кг и​ при заполнении таблицы.​​ «Импорт», и кликните​​ правой нижней, словно​

​ т. д. –​ советы помогут нам​ вас всегда перед​«OK»​

​«Имя»​

​ зачем изобретать велосипед?​​ кнопкой мышки. Из​​ Допустим, нам нужно​ столбце. Данные вводятся​на вкладке​Данные (Data)​ ссылок на умные​выдавать необходимые вам​ более. Для этого​Теперь заполняем таблицу данными.​ на нужную версию​​ по диагонали. Поэтому​​ все может в​ облегчить с ней​ глазами, как бы​.​указываем то наименование,​ Функция БИЗВЛЕЧЬ прекрасно​

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

​ контекстного меню выбрать​ оставить в таблице​ так же, как​Анализ (Analysis)​​нажмите кнопку​​ таблицы в поле​отчеты​ на ячейке КОЛ-ВО​Важно! При заполнении ячеек,​

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

​После этого информация в​ которым мы хотим​ справляется с поставленной​ вставить. Или после​ только те компании,​ и в обычной​или​​Отношения (Relations)​​ Источник. Но та​​для контроля всего​​ нажимаем стрелочку фильтра​​ нужно придерживаться единого​​ будете импортировать файл.​ на место, откуда​ за вас автоформа,​Это специальная структура, содержащая​​ прокручивали лист с​ БД будет отсортирована,​ назвать базу данных.​ задачей и при​​ выделения строк по​ которые работают в​ таблице. Если данные​Параметры (Options)​​. В появившемся окне​​ же ссылка «завернутая»​ бизнес-процесса с точки​

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

​ в какой-то ячейке​можно быстро визуализировать​ нажмите кнопку​ в функцию​ зрения руководителя​​ параметры.​​ если изначально ФИО​​• Можно связать файл​ а точнее, на​​ настроить. Удобно, правда?​

​ себе информационные материалы​Урок:​ В этом случае​ то, что наименование​​ горячих клавиш CTRL+SHIFT+=.​Выделяем те данные, информация​ – итог действий​ посчитанные в ней​​Создать (New)​​ДВССЫЛ​Со всем этим вполне​В появившемся окне напротив​ сотрудника записывается как​ Excel с таблицей​​ координаты верхней левой​​Кроме этого, не нужно​

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

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

​работает при этом​ может справиться Microsoft​ условия БОЛЬШЕ ИЛИ​ Петров А.А., то​ в программе Access.​ ячейки. Пусть табличка​ забывать о закреплении​ Говоря простым языком,​ Экселе​ по именам сотрудников​​ буквы, и в​​ новый прибыльный клиент​ столбцов базы данных​ остаться в базе​ ячеек, то заносим​​Еще одной типовой задачей​​ выпадающих списков таблицы​ «на ура» (подробнее​ Excel, если приложить​ РАВНО вписываем цифру​ остальные ячейки должны​ Для этого в​ начинается в месте​ первой строки. В​ это набор организованных​

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

​ быть заполнены аналогично.​ «Экселе» нужно выделить​ А5. Это значение​ Excel 2007 это​​ и разложенных по​​ оптимально будет организовать​Одним из наиболее удобных​ быть пробелов. В​​ именем «Василий». Нам​​ в первую строку​ случае находим в​Чтобы пользоваться БД, обращаемся​​ автоматическое заполнение различных​​ по которым они​ в статье про​ попробуем это реализовать.​ с указанием продукты,​ Если где-то будет​​ диапазон ячеек, содержащих​​ и будет верхней​ можно совершить следующим​

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

​ печатных бланков и​ должны быть связаны:​ создание выпадающих списков​Информацию о товарах, продажах​ которые заказывались партией​

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

​ написано иначе, например,​ необходимую информацию, и,​ левой ячейкой диапазона.​ образом: перейти на​ человек учатся в​ пользователи, добавляя новые​ в базе данных​​«Диапазон»​​ только имя и​ таблицы критериев.​​ «РБ». Щелкаем по​ «Данные».​ форм (накладные, счета,​​Важный момент: таблицы нужно​ с наполнением).​ и клиентах будем​ больше или равной​​ Петров Алексей, то​​ кликнув на них​ Теперь, когда первый​ вкладку «Вид», затем​

​ классе, их характеристики,​ записи, могли указывать​ Excel является автофильтр.​можно изменить адрес​​ фамилия «Василий Великий».​Пространство для заполнения критериев​ ячейке правой кнопкой​Присвоим БД имя. Выделяем​​ акты и т.п.).​ задавать именно в​После заполнения формы нужно​ хранить в трех​ 25 кг. А​ работа с БД​ правой кнопкой мыши,​ искомый элемент найден,​

​ выбрать «Закрепить области»​​ даты рождения и​​ только определенные параметры.​ Выделяем весь диапазон​​ области таблицы, но​​ На именины в​ запросов выше данных​​ мыши.​​ диапазон с данными​ Про один из​ таком порядке, т.е.​ введенные в нее​ таблицах (на одном​ т.к. мы не​ будет затруднена.​ задать имя диапазона.​ перейдем ко второму.​ и в контекстном​ табель успеваемости –​ Это актуально, например,​​ БД и в​​ если вы её​​ день Василия нам​​ базы.​​Выполняем последовательно команду: «фильтр​​ – от первой​​ способов это сделать,​​ связанная таблица (​ данные добавить в​ листе или на​ убирали сортировку по​

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

​ ячейки до последней.​ я уже как-то​Прайс​​ конец таблицы​​ разных — все​​ цене, то эти​​ она может быть​​ закройте Excel. Откройте​​ такие аргументы, как​ «Закрепить верхнюю строку».​ совокупности представляет собой​

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

​«Пол»​«Сортировка и фильтр»​ ничего тут менять​ 1 подарок для​ фактуры по фамилии​ значению выделенной ячейки».​ Правая кнопка мыши​ писал. Здесь же​) не должна содержать​Продажи​ равно). Принципиально важно,​ продукты расположились еще​ гораздо длиннее. Мы​

​ «Аксесс», на вкладке​ ширина и высота.​ Это требуется, чтобы​ базу данных. Она​​. Ведь тут возможно​​кликаем по кнопке​ не нужно. При​ более прибыльного клиента​ клиента:​ Готово.​​ – имя диапазона.​​ реализуем, для примера,​​ в ключевом столбце​​. Сформируем при помощи​

planetaexcel.ru

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

​ превратить их в​ и в порядке​ вписали немного позиций​ под названием «Внешние​ Значение последней пусть​ зафиксировать «шапку» работы.​ нужна везде –​ всего два варианта:​«Фильтр»​

​ желании в отдельном​ фирмы. Мы должны​В ячейке D2 введите​Если в БД содержится​ Даем любое имя.​ заполнение формы по​ (​ простых ссылок строку​ «умные таблицы» с​ ее возрастания.​ для примера. Придадим​ данные» выберите пункт​ будет равно 1,​ Так как база​ на промышленных и​ мужской и женский.​.​ поле можно указать​ выбрать кому отдать​

​ фамилию Антонова.​ финансовая информация, можно​ В примере –​ номеру счета:​Наименование​ для добавления прямо​ автоподстройкой размеров, чтобы​И еще одна полезная​ базе данных более​ «Электронная таблица Эксель»​ а первую вычислит​ данных Excel может​ технических предприятиях, в​Создаем дополнительный список. Удобнее​Как видим, после этого​ примечание, но этот​ предпочтение: Василию Веселому​

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

​В ячейке A3 введите​ найти сумму по​ БД1. Проверяем, чтобы​Предполагается, что в ячейку​) повторяющихся товаров, как​ под формой:​ не думать об​

​ функция, которая позволит​ эстетичный вид, сделав​ и введите ее​

​ формула СЧЁТ3(Родители!$B$5:$I$5).​ быть достаточно большой​ образовательных и медицинских​ всего его будет​

​ в ячейках с​ параметр не является​ или Василию Великому.​ следующую формулу:​

​ разным параметрам:​ диапазон был правильным.​ C2 пользователь будет​ это происходит в​

​Т.е. в ячейке A20​ этом в будущем.​ посчитать сумму, произведение,​ рамки. Для этого​

​ название. Затем щелкните​

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

​Итак, в поле диапазона​ по объему, то​ учреждениях, в силовых​ разместить на другом​ наименованием полей появились​ обязательным. После того,​ Для этого сравниваем​Сразу же получаем готовый​сумма (суммировать данные);​Основная работа – внесение​ вводить число (номер​ таблице​ будет ссылка =B3,​

​ Это делается с​ максимальное, минимальное или​

1. ​ выделяем всю таблицу​ по пункту, который​
2. ​ записываем =СМЕЩ(Родители!$A$5;0;0;СЧЁТЗ(Родители!$A:$A)-1;1). Нажимаем​ при пролистывании вверх-вниз​ структурах и даже​ листе. В нём​ пиктограммы в виде​ как все изменения​ их суммы транзакций:​ результат как показано​счет (подсчитать число ячеек​ информации в БД​ строки в таблице​Продажи​ в ячейке B20​ помощью команды​
3. ​ среднее значение и​ и на панели​ предлагает создать таблицу​
4. ​ клавишу ОК. Во​ будет теряться главная​ в заведениях общественного​ указываем перечень значений,​ перевернутых треугольников. Кликаем​ внесены, жмем на​Расширьте диапазон для просматриваемой​ ниже на рисунке:​ с числовыми данными);​ – выполнена. Чтобы​

​Продажи​. Другими словами, связанная​ ссылка на =B7​Форматировать как таблицу​ т.п. в имеющейся​ находим параметр ИЗМЕНЕНИЕ​ для связи с​

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

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

1. ​ кнопку​ таблицы $A$5:$E$18 в​Формула нашла соответствующий номер​среднее значение (подсчитать среднее​ этой информацией было​, по сути), а​
2. ​ таблица должна быть​ и т.д.​на вкладке​ БД. Она называется​ ГРАНИЦ.​ источником данных, и​ букву A меняем​ полей, что неудобно​ блюд, их рецептура​ в выпадающем списке.​ столбца, значение которого​

​«OK»​ параметрах формул, так​ фактуры для клиента​

​ арифметическое);​ удобно пользоваться, необходимо​ затем нужные нам​ той, в которой​Теперь добавим элементарный макрос​

​Главная (Home — Format​ ПРОМЕЖУТОЧНЫЕ ИТОГИ. Отличие​Аналогично обрамляем шапку толстой​ укажите ее наименование.​ на B, C​ для пользователя.​ с калорийностью, а​Выделяем этот список и​ собираемся отфильтровать. В​

1. ​.​ как у нас​ с фамилией Антонова.​максимальные и минимальные значения​ выделить нужное, отфильтровать,​
2. ​ данные подтягиваются с​ вы искали бы​ в 2 строчки,​

​ as Table)​ ее от обычных​ внешней границей.​Вот и все. Работа​ и т. д.​После того как верхняя​ также описание тоже​

​ кликаем по нему​ открывшемся окошке снимаем​Кликаем по кнопке​

• ​ добавился новый клиент​
• ​Разбор принципа действия функции​

​ в выделенном диапазоне;​ отсортировать данные.​ помощью уже знакомой​

1. ​ данные с помощью​ который копирует созданную​
2. ​. На появившейся затем​ команд в том,​​ готова!​
3. ​Работа с базой данных​ строка закреплена, выделяем​ является вместилищем данных.​ правой кнопкой мыши.​ галочки с тех​«Сохранить»​ и на одну​ БИЗВЛЕЧЬ для работы​
4. ​произведение (результат умножения данных);​Чтобы упростить поиск данных​ функции​ВПР​ строку и добавляет​
5. ​ вкладке​ что она позволяет​Теперь обратимся к функциям,​Автор: Анна Иванова​ в Excel почти​ первые три строки​Здесь мы разобрались. Теперь​
6. ​ В появившемся меню​ значений, записи с​в верхней части​ запись стало больше:​ с базами данных​стандартное отклонение и дисперсия​ в базе, упорядочим​ВПР (VLOOKUP)​

​, если бы ее​

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

1. ​ нужно узнать, что​ выбираем пункт​ которыми хотим скрыть.​ окна или набираем​Теперь функция возвращает​ в Excel:​ по выборке.​ их. Для этой​и функции​
2. ​ использовали.​ Продажи. Для этого​(Design)​ даже при изменении​

​ для работы с​ – это сводная​ первый лист и​ данных и добавляем​

• ​ представляет собой база​
• ​«Присвоить имя…»​ После того как​
• ​ на клавиатуре сочетание​ ошибку #ЧИСЛО! так​
• ​БИЗВЛЕЧЬ – главная функция​Выделить диапазон БД. Переходим​
• ​ цели подойдет инструмент​
• ​ИНДЕКС (INDEX)​Само-собой, аналогичным образом связываются​

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

1. ​ жмем сочетание​присвоим таблицам наглядные​ размера таблицы. Чего​
2. ​ БД.​ таблица с параметрами​

​ создаем раскрывающиеся списки​ границы ячеек.​ данных в Excel,​.​ выбор сделан, жмем​ клавиш​ как в базе​ базы данных в​ на вкладку «Данные»​

​ «Сортировка».​.​ и таблица​

1. ​Alt+F11​ имена в поле​ невожнможно реалиловать в​Пример: нам нужно узнать​ и информацией. Программа​
2. ​ на соответствующих ячейках.​Для того чтобы продолжить​ и как ее​

​Открывается уже знакомое нам​ на кнопку​Ctrl+S​

exceltable.com

БИЗВЛЕЧЬ работа с функциями базы данных в Excel

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

Примеры работы функции базы данных БИЗВЛЕЧЬ в Excel

​Visual Basic​для последующего использования:​ помощью функции =СУММ().​ принимал Петров А.А.​ создание БД в​

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

Функция БИЗВЛЕЧЬ примеры в Excel

​ будет содержать в​ простой и без​ нашему диапазону, согласно​Как видим, после этого,​ жестком диске или​В поле критериев «Имя»​ данных вместе с​Инструменты на вкладке «Данные»​ компании – столбец​ редакции. Для удобного​по общему столбцу​Разработчик (Developer)​ получиться три «умных​Предварительно придадим нашей БД​ пробежаться по всем​ и Excel имеет​

1. ​ под полем «ФИО​ себе база данных​ изысков. Настоящие же​ условиям, о которых​ строки, где содержатся​ съемном носителе, подключенном​ вводим значение «Василий»,​ заголовками. Во втором​ позволяют сегментировать БД.​ «Дата заключения договора».​ просмотра информации и​Клиент​
2. ​. Если эту вкладку​ таблицы»:​ полный вид. Затем​ строкам, где фигурирует​ все возможности для​ родителей». Туда будет​

​ в Excel. Пример​ вместилища данных -​ уже шла речь​

​ значения, с которых​ к ПК.​ а потом в​

1. ​ аргументе функции указываем​ Сгруппировать информацию с​
2. ​ Вызываем инструмент «Сортировка».​ получения полного контроля​

​:​ не видно, то​Обратите внимание, что таблицы​

​ создадим формулу для​ эта фамилия, и​ формирования простых баз​

​ вводиться информация. В​ ее приведен ниже.​ довольно громоздкие и​ выше.​

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

​ соответствующие критериям поискового​ Выделение групп покупателей​ диапазон. Соглашаемся. Если​ с программой.​ закрыть, повторять эту​Файл — Параметры -​ например, наш​ F26. Параллельно вспоминаем​ если наша БД​Как сделать базу данных​ под названием «Параметры»​ средств с родителей​ внутренним «ядром», которое​ к которому будет​Для того, чтобы вернуть​ данных. С ней​ БИЗВЛЕЧЬ с базой​ запроса. Третьим аргументом​

1. ​ услуг и товаров​ мы отсортируем данные​
2. ​Внешний вид рабочей области​ процедуру уже не​

​ Настройка ленты (File​Прайс​ особенность сортировки БД​

​ будет состоять из​ в Excel, чтобы​ записываем в «Источник»​ в фонд школы.​

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

​ придется.​ — Options -​содержит дополнительно информацию о​ в Excel: номера​ нескольких сотен позиций?​ не было удобно​ =ФИО_родителя_выбор. В меню​ Размер суммы не​ строк программного кода​ Переходим во вкладку​ экран, кликаем на​ в таком состоянии,​В результате мы видим,​ содержащий следующие условия:​ продукта.​

​ остальные оставим на​ А реляционная база​Теперь для анализа продаж​ Customize Ribbon)​ категории (товарной группе,​ строк сохраняются. Поэтому,​ На помощь приходит​ не только хранить,​ «Тип данных» указываем​ ограничен и индивидуален​ и написано специалистом.​

​«Данные»​

Обработка баз данных в Excel по нескольким критериям

​ пиктограмму того столбца,​ как она представлена​ что подарок получит​ заголовок столбца БД​Готовые образцы шаблонов для​ месте, то информация​ данных структурирует информацию​ и отслеживания динамики​. В открывшемся окне​ упаковке, весу и​ даже когда мы​ ФИЛЬТР.​ но и обрабатывать​ «Список».​ для каждого человека.​Наша работа будет представлять​. Жмем на кнопку​ по которому проводилась​

1. ​ сейчас, но многие​ более активный клиент​ и диапазон для​ ведения клиентской базы​ станет неправильной. Открывается​ в строки и​ процесса, сформируем для​​ редактора Visual Basic​ т.п.) каждого товара,​ будем делать фильтрацию,​Выделяем шапку таблицы и​ данные: формировать отчеты,​Аналогично поступаем с остальными​
2. ​ Пусть в классе​ собой одну таблицу,​«Проверка данных»​ фильтрация, и в​ возможности при этом​

​ Василий Великий.​ поиска под этим​ по сегментам.​

​ меню, где мы​ столбцы. Несмотря на​ примера какой-нибудь отчет​ вставляем новый пустой​

exceltable.com

​ а таблица​

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

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

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

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

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

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

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

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

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

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

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

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

На этом создание каркаса БД закончено.

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

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

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

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

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

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

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

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

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

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

• имя по алфавиту;
• дата;
• число и т.д.

При наличии большой БД поиск по ней удобно производить с помощь специального инструмента.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Методы работы с базами данных в приложении Microsoft Excel

СОДЕРЖАНИЕ

2. Теоретические положения.

2.1. Общие положения.

2.2. Списки Excel как база данных.

2.3. Проверка данных при вводе.

2.4. Сортировка данных.

2.5. Промежуточные итоги в базе данных.

2.7. Расширенный фильтр.

3. Порядок выполнения работы.

4. Контрольные вопросы.

5. Список рекомендуемой литературы.

1. ЦЕЛЬ РАБОТЫ

Цели:

• повышение уровня понимания темы «Базы данных в приложении Microsoft Excel»;
• овладение специальными техническими умениями конструирования и использования реляционной базы данных на уровне их свободного использования;
• развитие навыков самостоятельной работы и способности применить полученные знания на практике при разработке собственной базы данных.

2. ТЕОРЕТИЧЕСКИЕ ПОЛОЖЕНИЯ

2.1. Общие положения

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

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

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

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

2.2. Списки Excel как база данных

Приложение Microsoft Excel обладает богатыми встроенными средствами для обработки и анализа данных. Аналогом простой базы данных в Excel служить список.

Список — это группа строк таблицы, содержащая связанные данные.

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

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

2.3. Проверка данных при вводе

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

1. Выделить ячейки столбца, для которого устанавливается проверка ввода.
2. На ленте Данные в группе Работа с данными выбрать команду Проверка данных.
3. На вкладке Параметры в области Условие проверки выбрать Тип данных: Любое значение (используется для отмены проверки ввода), Целое число, Действительное, Список, Дата, Время, Длина текста и Другой (формат, для которого можно задать собственную формулу, например, «м»or«ж»). При выборе значения внизу окна появляются дополнительные поля для ввода условий или ограничений – например, минимального и максимального допустимого значения.
4. На вкладке Сообщение для ввода можно установить флажок Отображать подсказку, если ячейка является текущей и ввести сообщение, чтобы оно появлялось на экране при выделении ячеек.
5. На вкладке Сообщение об ошибке можно установить флажок Выводить сообщение об ошибке, чтобы задать тип сообщения об ошибке, которое появится при вводе в ячейку недопустимого значения.

2.4. Сортировка данных

Команда Сортировка позволяет переставить записи в другом порядке на основании значений одного или нескольких столбцов. Записи сортируются возрастанию/убыванию или по выбранному пользователю порядке (например, по дням недели).

Чтобы отсортировать список надо:

1. Установить курсор в ячейку списка.
2. Выполнить команду Сортировка на ленте Данные в группе Сортировка и Фильтр.
3. В диалоговом окне Сортировка выбрать поле, по которому будет происходить сортировка; тип сортировки (по значению, цвет ячейки, цвет шрифта, значок ячейки) и порядок (по возрастанию, убыванию, настраиваемый).

Примечание. Выбор настраиваемого порядка позволяет задать нестандартный порядок сортировки. Для этого надо в диалоговом окне Списки (рис. 2) выбрать НОВЫЙ СПИСОК, в поле Элементы списка ввести значения, образующие пользовательский порядок сортировки (например, АОП, ФЭО, ИВЦ, ИТО, МПО), после чего последовательно выбрать кнопки Добавить и ОК.

2.5. Промежуточные итоги в БД

Для организации списков используют команду Промежуточные итоги на ленте Данные в группе Структура, которая позволяет:

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

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

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

Рис. 3. Просмотр списка в режиме структуры

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

Чтобы удалить промежуточный и окончательные итоги, надо повторно выполнить команду Промежуточные итоги, а затем щелкнуть по кнопке Убрать все.

2.6. Автофильтр

Отфильтровать список — значит показать только те записи, которые удовлетворяют заданному критерию.

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

Показать все записи по отфильтрованному полю, не убирая фильтр, можно выбрав в списке фильтра критерий Снять фильтр с….

Показать все записи по всем полям, не убирая фильтр, команда Очистить.

Для данных разного типа существуют дополнительные автофильтры, которые находятся в списке критериев Текстовые фильтры, Числовые фильтры, Фильтры по дате и т.д.

Если выделить какое-то числовое поле (например, Возраст), а в списке критериев выбрать Числовые фильтры, то появится список дополнительных фильтров (рис. 4), которые позволяют:

• задать критерий в виде неравенства – критерии равно, не равно, больше, больше илиравно, меньше, меньшеилиравно, между;
• вывести первые N значений – критерий Первые 10: после выбора в списке Числовых фильтров команду Первые 10…, необходимо в появившемся окне указать число значений (N), а также способ вычисления: количество элементов списка, % от количества элементов;
• определить условие по среднему значению в указанном столбце – критерии Выше среднего, Ниже среднего;
• самостоятельно задаваемый фильтр – критерий Настраиваемый фильтр.

Рис. 4. Дополнительные числовые фильтры

Настраиваемый фильтр позволяет задать критерии из одного или двух условий.

Простое условие состоит: из имени поля (атрибута); варианта условия (равно, не равно, больше, меньше, больше или равно, меньше или равно; начинается с, не начинается с, заканчивается на или не заканчивается на; содержит, не содержит); слова или числа для сравнения.

Сложное условие состоит из двух простых, соединенных союзами И или ИЛИ.

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

Работа с базами данных в MS Excel (стр. 1 из 6)

СОДЕРЖАНИЕ

1. БАЗЫ ДАННЫХ В EXCEL.

1.1. Как создать базу данных.

1.2. Как выполнить поиск, изменение и удаление записей.

1.3. Обработка данных в БД.

1.4. Обмен данными.

2.1. Проектирование базы данных.

2.1.1. Структура базы данных

2.1.2. Определение формул для вычисляемой части базы данных.

2.2. Создание базы данных.

2.2.1 Создание заголовка таблицы и первой строки.

2.2. Заполнение таблицы с помощью Мастера форм.

2.3. Ведение базы данных

2.3.1. Редактирование полей.

2.2. Редактирование записей

2.4.Начальная обработка данных.

2. 4.1. Добавление суммы по столбцам

2.4.2. Добавление суммирования по критерию.

2.5. Сортировка базы данных.

2.5.1. Простая сортировка по полю.

2.5.2 Сортировка по нескольким полям.

2.6. Формы представления информации, содержащейся в базе данных.

2.6.1. Добавление промежуточных итогов.

2.6.2. Работа со структурой

2.7. Анализ информации, содержащейся в базе данных.

2.7.1 Вычисление статистических характеристик

2.7.2. Работа с функциями из раздела Базы данных

2.8. Выборочное использование данных

2.8.1. Пользовательский автофильтр.

2.8.2 Расширенный фильтр.

2.9. Графическое представление данных.

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

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

Примерами пакетов ведения электронных таблиц являются Supercalc , Lotus 1-2-3 и MS Excel. В данной работе мы будем рассматривать работу с базами данных в MS Excel.

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

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

(б) качество документации, которая поставляется с пакетом;

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

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

1. БАЗЫ ДАННЫХ В EXCEL.

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

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

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

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

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

Как сделать базу данных в Excel, чтобы не было удобно не только хранить, но и обрабатывать данные: формировать отчеты, строить графики, диаграммы и т.д.

Пошаговое создание базы данных в Excel

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

С шапкой определились. Теперь заполняем таблицу. Начинаем с порядкового номера. Чтобы не проставлять цифры вручную, пропишем в ячейках А4 и А5 единицу и двойку, соответственно. Затем выделим их, схватимся за уголок получившегося выделения и продлим вниз на любое количество строк. В небольшом окошечке будет показываться конечная цифра.

Примечание. Данную таблицу можно скачать в конце статьи.

По базе видим, что часть информации будет представляться в текстовом виде (продукт, категория, месяц и т.п.), а часть – в финансовом. Выделим ячейки из шапки с ценой и стоимостью, правой кнопкой мыши вызовем контекстное меню и выберем ФОРМАТ ЯЧЕЕК.

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

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

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

Теперь заполняем таблицу данными.

Важно! При заполнении ячеек, нужно придерживаться единого стиля написания. Т.е. если изначально ФИО сотрудника записывается как Петров А.А., то остальные ячейки должны быть заполнены аналогично. Если где-то будет написано иначе, например, Петров Алексей, то работа с БД будет затруднена.

Таблица готова. В реальности она может быть гораздо длиннее. Мы вписали немного позиций для примера. Придадим базе данных более эстетичный вид, сделав рамки. Для этого выделяем всю таблицу и на панели находим параметр ИЗМЕНЕНИЕ ГРАНИЦ.

Аналогично обрамляем шапку толстой внешней границей.

Функции Excel для работы с базой данных

Теперь обратимся к функциям, которые Excel предлагает для работы с БД.

Работа с базами данных в Excel

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

Выделяем шапку таблицы и во вкладке ДАННЫЕ нажимаем ФИЛЬТР (CTRL+SHIFT+L).

У каждой ячейки в шапке появляется черная стрелочка на сером фоне, куда можно нажать и отфильтровать данные. Нажимаем ее у параметра ПРИНИМАЛ ТОВАР и снимаем галочку с фамилии КОТОВА.

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

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

Можно произвести дополнительную фильтрацию. Определим, какие крупы принял Петров. Нажмем стрелочку на ячейке КАТЕГОРИЯ ПРОДУКТА и оставим только крупы.

Вернуть полную БД на место легко: нужно только выставить все галочки в соответствующих фильтрах.

Сортировка данных

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

К примеру, мы хотим отсортировать продукты по мере увеличения цены. Т.е. в первой строке будет самый дешевый продукт, в последней – самый дорогой. Выделяем столбец с ценой и на вкладке ГЛАВНАЯ выбираем СОРТИРОВКА И ФИЛЬТР.

Т.к. мы решили, что сверху будет меньшая цена, выбираем ОТ МИНИМАЛЬНОГО К МАКСИМАЛЬНОМУ. Появится еще одно окно, где в качестве предполагаемого действия выберем АВТОМАТИЧЕСКИ РАСШИРИТЬ ВЫДЕЛЕННЫЙ ДИАПАЗОН, чтобы остальные столбцы тоже подстроились под сортировку.

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

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

Сортировка по условию

Нам нужно извлечь из БД товары, которые покупались партиями от 25 кг и более. Для этого на ячейке КОЛ-ВО нажимаем стрелочку фильтра и выбираем следующие параметры.

В появившемся окне напротив условия БОЛЬШЕ ИЛИ РАВНО вписываем цифру 25. Получаем выборку с указанием продукты, которые заказывались партией больше или равной 25 кг. А т.к. мы не убирали сортировку по цене, то эти продукты расположились еще и в порядке ее возрастания.

Промежуточные итоги

И еще одна полезная функция, которая позволит посчитать сумму, произведение, максимальное, минимальное или среднее значение и т.п. в имеющейся БД. Она называется ПРОМЕЖУТОЧНЫЕ ИТОГИ. Отличие ее от обычных команд в том, что она позволяет считать заданную функцию даже при изменении размера таблицы. Чего невожнможно реалиловать в данном случаи с помощью функции =СУММ(). Рассмотрим на примере.

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

Функция ПРОМЕЖУТОЧНЫЕ ИТОГИ имеет 30 аргументов. Первый статический: код действия. По умолчанию в Excel сумма закодирована цифрой 9, поэтому ставим ее. Второй и последующие аргументы динамические: это ссылки на диапазоны, по которым подводятся итоги. У нас один диапазон: F4:F24. Получилось 19670 руб.

Теперь попробуем снова отсортировать кол-во, оставив только партии от 25 кг.

Видим, что сумма тоже изменилась.

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

СОДЕРЖАНИЕ

1. Цель работы.

2. Теоретические положения.

2.1. Общие положения.

2.2. Списки Excel как база данных.

2.3. Проверка данных при вводе.

2.4. Сортировка данных.

2.5. Промежуточные итоги в базе данных.

2.6. Автофильтр.

2.7. Расширенный фильтр.

3. Порядок выполнения работы.

4. Контрольные вопросы.

5. Список рекомендуемой литературы.

Приложения.

Приложение 1. Технологические карты № 1, 2, 3 к лабораторным работам.

Приложение 2. Готовый файл со списком (документ Excel).

Приложение 3. Технология автоматизированного создания списков в Excel.

1. ЦЕЛЬ РАБОТЫ

Цели:

• повышение уровня понимания темы «Базы данных в приложении Microsoft Excel»;
• овладение специальными техническими умениями конструирования и использования реляционной базы данных на уровне их свободного использования;
• развитие навыков самостоятельной работы и способности применить полученные знания на практике при разработке собственной базы данных.

2. ТЕОРЕТИЧЕСКИЕ ПОЛОЖЕНИЯ

2.1. Общие положения

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

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

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

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

2.2. Списки Excel как база данных

Приложение Microsoft Excel обладает богатыми встроенными средствами для обработки и анализа данных. Аналогом простой базы данных в Excel служить список.

Список — это группа строк таблицы, содержащая связанные данные.

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

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

Рис. 1. Фрагмент списка «Сотрудники»

2.3. Проверка данных при вводе

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

1. Выделить ячейки столбца, для которого устанавливается проверка ввода.
2. На ленте Данные в группе Работа с данными выбрать команду Проверка данных.
3. На вкладке Параметры в области Условие проверки выбрать Тип данных: Любое значение (используется для отмены проверки ввода), Целое число, Действительное, Список, Дата, Время, Длина текста и Другой (формат, для которого можно задать собственную формулу, например, «м» or «ж»). При выборе значения внизу окна появляются дополнительные поля для ввода условий или ограничений – например, минимального и максимального допустимого значения.
4. На вкладке Сообщение для ввода можно установить флажок Отображать подсказку, если ячейка является текущей и ввести сообщение, чтобы оно появлялось на экране при выделении ячеек.
5. На вкладке Сообщение об ошибке можно установить флажок Выводить сообщение об ошибке, чтобы задать тип сообщения об ошибке, которое появится при вводе в ячейку недопустимого значения.

2.4. Сортировка данных

Команда Сортировка позволяет переставить записи в другом порядке на основании значений одного или нескольких столбцов. Записи сортируются возрастанию/убыванию или по выбранному пользователю порядке (например, по дням недели).

Чтобы отсортировать список надо:

1. Установить курсор в ячейку списка.
2. Выполнить команду Сортировка на ленте Данные в группе Сортировка и Фильтр.
3. В диалоговом окне Сортировка выбрать поле, по которому будет происходить сортировка; тип сортировки (по значению, цвет ячейки, цвет шрифта, значок ячейки) и порядок (по возрастанию, убыванию, настраиваемый).

Рис. 2. Создание пользовательского порядка сортировки

Примечание. Выбор настраиваемого порядка позволяет задать нестандартный порядок сортировки. Для этого надо в диалоговом окне Списки (рис. 2) выбрать НОВЫЙ СПИСОК, в поле Элементы списка ввести значения, образующие пользовательский порядок сортировки (например, АОП, ФЭО, ИВЦ, ИТО, МПО), после чего последовательно выбрать кнопки Добавить и ОК.

2.5. Промежуточные итоги в БД

Для организации списков используют команду Промежуточные итоги на ленте Данные в группе Структура, которая позволяет:

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

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

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

Рис. 3. Просмотр списка в режиме структуры

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

Чтобы удалить промежуточный и окончательные итоги, надо повторно выполнить команду Промежуточные итоги, а затем щелкнуть по кнопке Убрать все.

2.6. Автофильтр

Отфильтровать список — значит показать только те записи, которые удовлетворяют заданному критерию.

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

Показать все записи по отфильтрованному полю, не убирая фильтр, можно выбрав в списке фильтра критерий Снять фильтр с….

Показать все записи по всем полям, не убирая фильтр, команда Очистить.

Для данных разного типа существуют дополнительные автофильтры, которые находятся в списке критериев Текстовые фильтры, Числовые фильтры, Фильтры по дате и т.д.

Если выделить какое-то числовое поле (например, Возраст), а в списке критериев выбрать Числовые фильтры, то появится список дополнительных фильтров (рис. 4), которые позволяют:

• задать критерий в виде неравенства – критерии равно, не равно, больше, больше или равно, меньше, меньше или равно, между;
• вывести первые N значений – критерий Первые 10: после выбора в списке Числовых фильтров команду Первые 10…, необходимо в появившемся окне указать число значений (N), а также способ вычисления: количество элементов списка, % от количества элементов;
• определить условие по среднему значению в указанном столбце – критерии Выше среднего, Ниже среднего;
• самостоятельно задаваемый фильтр – критерий Настраиваемый фильтр.

Рис. 4. Дополнительные числовые фильтры

Настраиваемый фильтр позволяет задать критерии из одного или двух условий.

Простое условие состоит: из имени поля (атрибута); варианта условия (равно, не равно, больше, меньше, больше или равно, меньше или равно; начинается с, не начинается с, заканчивается на или не заканчивается на; содержит, не содержит); слова или числа для сравнения.

Сложное условие состоит из двух простых, соединенных союзами И или ИЛИ.

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

Таблица 1

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

2.7. Расширенный фильтр

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

Перед вызовом команды Расширенный фильтр, необходимо сформировать критерии. Для удобства лучше формировать критерии на отдельном листе (можно дать ему имя, например, Критерии) и давать критериям имена Кр1, Кр2 и т.д.

Основное правило: если критерии связаны между собой операцией И, то они должны располагаться в одной строке, а если ИЛИ, то в разных.

После формирования критерия, вызывают расширенный фильтр: на ленте Данные в группе Сортировка и фильтр команда Дополнительно.

Восстановить исходный список можно выбрав на ленте Данные в группе Сортировка и фильтр команду Очистить.

3. ПОРЯДОК ВЫПОЛНЕНИЯ РАБОТЫ

1. Ознакомиться с теоретическими положениями.

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

Примечание. Технология создания базы данных конфигурации любой сложности и объема представлена в приложении 3. Заполнить базу данных правдоподобными непротиворечивыми данными.

3. Получить технологические карты (прил. 1).     Технологическая карта – руководство, описывающее алгоритм деятельности по созданию базы данных, — последовательность действий, которые необходимо выполнить, чтобы создать авторский продукт.

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

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

5. Представить преподавателю отчет о проделанной работе в электронном или печатном виде.
Работа рассчитана на 6 академических часов.

4. КОНТРОЛЬНЫЕ ВОПРОСЫ

1. Для каких целей применяются электронные таблицы?
2. Для чего необходимы базы данных?
3. Какие возможности предоставляет программа Microsoft Excel для работы с базами данных?
4. Что такое запись?
5. Что такое поле?
6. Что такое тип данных? Зачем необходимо указывать типы полей?
7. Данные каких типов могут быть записаны в ячейку?
8. Как установить проверку вводимых в список значений?
9. Как записываются абсолютные и относительные адреса ячеек?
10. Что такое сортировка? Как отсортировать список по двум и более ключам?
11. Что такое фильтр?
12. Какие виды фильтров вы знаете?
13. В чем отличие сортировки списка от фильтрации списка?
14. Что такое расширенный фильтр?
15. Как сформировать критерий для расширенного фильтра?

5. СПИСОК РЕКОМЕНДУЕМОЙ ЛИТЕРАТУРЫ

1. Кошелев В. Excel 2007. Эффективное использование. — М.: Бином. Лаборатория знаний, 2008 — 544 стр.
2. Слетова Л. Excel 2007 — М.: «ЭКСМО», 2007 — 336 стр.
3. Сурядный А., Глушаков С. Microsoft Excel 2007: самоучитель, 2-е издание. – М.: АСТ, 2008 — 416 стр.

Бывают ситуации, когда на рабочей станции отсутствуют такие средства взаимодействия с БД как: MS SQL Server Management Studio, Aquafold Aqua Data Studio, DBeaver и т.п., а вероятность их установки в краткосрочной перспективе близка к нолю. В то же время, присутствует острая необходимость подключения к этой самой БД и работы с данными. Как оказалось, на помощь может прийти старый добрый MS Excel.

В моем случае требовалось подключиться к MS SQL Server, однако, MS Excel умеет устанавливать соединение не только с ним, но и с большинством современных БД: MySQL, PostgreeSQL, IBM DB2 и даже Oracle и Teradata, а также с файлами данных CSV, XML, JSON, XLS(X), MDB и другими.

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

В новой книге на ленте выбираем «(1) Данные» -> «(2) Получение внешних данных» -> «(3) Из других источников» -> «(4) С сервера SQL Server».

Далее, в окне Мастера подключения к данным, заполняем «(1) Имя сервера» -> «(2) Учетные сведения»[ -> «(3) Имя пользователя» и «Пароль»]. Таким образом, мы сообщаем MS Excel, с каким сервером мы хотим установить соединение и какой метод аутентификации хотим использовать. Я использовал «проверку подлинности Windows», но возможно также указать учетные данные отличные от установленных в Windows.

Выбираем целевую «(1) Базу данных» -> «(2)(3) Определенную таблицу» или «Несколько таблиц» или же базу в целом (тогда оба «чекбокса» оставляем пустыми).

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

Теперь выбрать созданное подключение можно будет следующим образом: «(1) Данные» -> «(2) Получение внешних данных» -> «(3) Существующие подключения».

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

Определив таблицы, MS Excel предложит выбрать «(1) Способ представления данных» и «(2) Куда следует поместить данные». Для простоты я выбрал табличное представление и размещение на уже имеющемся листе, чтобы не плодить новые. Далее следует нажать на «(3) Свойства».

В свойствах подключения, нужно перейти на вкладку «(1) Определение». Здесь можно выбрать «(2) Тип команды». Даже если требуется выгружать лишь одну таблицу без каких-либо связей, настоятельно рекомендую выбрать SQL команду, чтобы иметь возможность ограничить размер выгружаемой таблицы (например, с помощью TOP(n)). Так, если вы попытаетесь выгрузить целиком таблицу базы, это может привести в лучшем случае к замедлению работы MS Excel, а в худшем к падению программы, к тому же – это необоснованная нагрузка на сам сервер базы данных и на сеть. После того как «(3) Текст команды» будет введен и нажата кнопка «ОК», MS Excel предложит сохранить изменения запроса – отвечаем положительно.

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

Теперь наше подключение будет находиться в книге и доступно в меню «(1) Данные» -> «(2) Подключения».

Как видно из кнопок формы, здесь мы можем:

«(1) Добавить» в книгу новое подключение из уже существующих;

«(2) Удалить» подключение из книги;

Отредактировать «(3) Свойства» подключения;

«(4) Обновить» данные, предоставляемые подключением.

Подытоживая весь вышеописанный опыт, хочется сказать, что конечно же по функционалу и кругу решаемых задач MS Excel не сравнится с такими системами как MS SQL Server Management Studio, Oracle SQL Developer и подобными. Но, в условиях ограниченного времени и доступных инструментов, при необходимости быстро получить доступ к данным из базы, MS Excel вполне справляется.

Для управления большими
массивами данных используются специальные
программы, предназначенные для работы
с базами данных. В Excel также имеются
средства для обработки данных,
организованных по аналогичному принципу,
– это, так называемые, функции списка.
В виде списка можно представить таблицу,
строки которой содержат однородную
информацию. Список характеризует не
содержимое таблицы, а способ ее
организации. Отдельные записи (комплекты
данных списка) должны быть однородны
по строкам и/или столбцам. В
таблице не должно быть объединенных
ячеек! Однако только
однородность данных списка по строкам
делает возможным применение функции
фильтров (если данные однородны по
столбцам, таблицу следует транспонировать).
Примером базы данных является список
адресов, в котором указаны названия и
адреса различных фирм или фамилии людей.

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

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

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

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

Основными возможностями при работе с
базами данных являются:

        организация
ввода данных;

        просмотр
данных;

        поиск
данных по заданному критерию;

        сортировка
данных;

        фильтрация
данных;

        подведение
итогов.

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

Удобным средством для работы
с таблицей является форма
(окно формы открывается
по команде Данные►Форма.
В окне формы (рис.5.15) отображаются все
поля одной записи базы данных: слева
располагаются названия полей, рядом с
ними их значения, доступные для
редактирования. Если поле вычисляемое,
то оно не доступно для редактирования.

Работа с формой предполагает:

1.     
Перемещение по полям
записи мышью или клавишей [Tab];

2.     
Перемещение по записям
с помощью полосы прокрутки и кнопок
[Назад] и [Далее];

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

4.     
Удаление и добавление
записей с помощью кнопок [Добавить] и
[Удалить].
При добавлении новой записи, она всегда
добавляется в конец таблицы.

5.     
Редактирование
значений полей в записях.

6.     
Поиск данных по
критерию с помощью кнопки [Критерии],
после нажатия которой в появившемся
окне в соответствующих полях задаются
критерии. Задание критериев позволяет
просматривать через окно Формы
только те записи, которые удовлетворяют
некоторым условиям поиска (критериям).

Перед тем как начать поиск записей по
критерию, рекомендуется сделать текущей
первую запись базы данных. Просмотр
отобранных записей по критерию
осуществляется с помощью кнопок [Назад]
и [Далее]. При задании критериев поиска
можно использовать символы подстановки:

*– для обозначения произвольного
количества символов;

?– для обозначения одного символа.

Например, при необходимости
выбора всех записей с фамилиями студентов,
начинающихся с буквы «К», в качестве
критерия поиска следует ввести К*.
А при задании критерия К?рсанов,
будут выбираться фамилии Кирсанов,
Керсанов, Корсанов и т.п.

В критериях при поиске числовых
значений можно использовать операторы
сравнения: =,
<, >,
<>,
<=, >=.

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

Упорядочение табличных данных
в выделенных строках по алфавиту,
величине или дате называется сортировкой.
Столбец, определяющий порядок данных,
называются ключом
сортировки. Сортировка
производится в убывающем или возрастающем
порядке. Для сортировки в Excel используется
команда Данные►Сортировка.
Можно задавать три уровня сортировки
одновременно: за одну сортировку можно
выполнить сортировку сначала по первому
уровню, потом в полученном списке – по
второму, а затем – по третьему (рис.
5.16).

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

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

В Excel
существует возможность фильтрации
данных с помощью Автофильтра
и Расширенного фильтра.

Использование
автофильтра

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

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

Автофильтрпредоставляет несколько
видов фильтрации данных:

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

2.     
Выбор записей по
условию производится
командой Условие,
в результате чего открывается диалоговое
окно Пользовательский
автофильтр, где задают
критерии с участием одного или двух
условий с использованием шаблонов(”?”
и “*”)
, операций сравнения и логических
операций И,
ИЛИ.

3.     
Выбор первых
наибольших или наименьших n
значений позволяет
выполнить команда Первые
10.

4.     
Команда Все
восстанавливает на экране все скрытые
фильтром строки таблицы.

Использование
расширенного фильтра

Расширенный фильтр предоставляет
широкие возможности поиска и фильтрации.
Он позволяет не только применять операции
И, ИЛИ,
но и составлять вычисляемые критерии.
Кроме того, отфильтрованные данные
могут быть скопированы в заданный
диапазон рабочего листа.

При работе с расширенным фильтром
создаются три области:

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

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

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

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

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

        Для
объединения критериев с помощью
логического И
нужно указать задаваемые критерии в
одной строке, а для
объединения критериев с помощью
логического ИЛИ
следует представить критерии в
разных строках. Следует
также учитывать, что в случае необходимости
вместе с критерием в ячейку надлежит
ввести оператор сравнения. Для обозначения
точного соответствия поля записи
заданному критерию при задании критериев
знак равенства (=) не используется.

3.     
Выходной
диапазон. Область,
в которую Excel копирует выбранные из
таблицы данные. Этот диапазон должен
быть расположен на том же листе, что и
исходный. Извлеченную информацию можно
поместить на другой лист копированием
результата фильтрации. Задание выходного
диапазона необязательно, т. к. существует
опция “Фильтровать список на месте”.
В этом случае фильтрация преобразует
исходную таблицу в набор записей,
удовлетворяющих условиям выбора.

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

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

В диалоговом окне Промежуточные
итоги поле При
каждом изменении в
используется для указания столбца, по
которому следует сгруппировать данные
для подведения итогов; поле Операция
позволяет использовать
различные функции; поле Добавить
итоги по: позволяет
отметить все поля, по которым будут
подводиться итоги. Установка флажка
Заменить текущие итоги
позволяет заменить в таблице уже
существующие итоги. Установленный
флажок Конец страницы
между группами
автоматически вставляет конец страницы
перед каждой группой данных, для которой
вычисляются итоги. Флажок Итоги
под данными
дает возможность
поместить строки промежуточных и общих
итогов под соответствующими данными.
Кнопка [Убрать все]
позволяет восстановить
исходный вид экрана до подведения
итогов.

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

Имя_листа!Адрес_ячейки

Например, Товары!В5.
Ссылка на ячейку, находящуюся в другой
книге имеет вид:

Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]

• #
• #
• #
• #
• #
• #
• #
• #
• #
• #
• #