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

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

· таблицу базы данных рекомендуется помещать на одном рабочем листе;

· следует отделять таблицу от других данных (условия, коэффициенты) на листе хотя бы одной строкой и одним столбцом;

· имена столбцов должны располагаться в первой строке таблицы.

· каждый столбец в таблице должен содержать во всех строках однотипные данные (текстовые или числовые и др.);

· не следует вводить дополнительные пробелы в начале ячеек при вводе данных, поскольку они влияют на сортировку и поиск;

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

Поле со списком

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

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

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

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

Значения для поля со списком можно также задать непосредственно,введя в окно Источник значения списка, разделяя их знаком «;» (точка с запятой). Такой способ задания значений списка используется в том случае, когда значений в списке мало.

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

Создание таблицы «Ведомость»

На новом листе Ведомость создать таблицу:

· Обеспечить ввод данных в поле Код заказчика для ячеек А6:А16 через список Код_зак.

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

· Поставить курсор в ячейку А6; выделить ячейки А6:А16;

· Команда Данные, в разделе Работа с данными кликнуть Проверка данных;

· В открывшемся окне Проверка вводимых значений выбрать в поле «Тип данных» – Список. Задать в строке «Источник» – Код_зак. Для этого нажать функциональную клавишу F3 и выбрать из списка Код_зак.

· Перейти на вкладку Сообщение об ошибке и ввести текст, который будет появляться в случае несоответствия введённого с клавиатуры значения списку.

· Нажать ОК.

 

В таблице Ведомость в столбце Код заказчика справа от ячейки А6 появится стрелка (поле со списком). С помощью стрелки раскрыть список и выбрать значение Кода заказчика, в соответствии с данными первичного документа.Перейти в ячейку А7 и заполнить её.

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

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

Функции ПРОСМОТР и ВПР

Для автоматизации заполнения столбцов Наименование заказчика и Адрес таблицы из справочников можно применить функции ВПР или ПРОСМОТР. До настройки этих функций необходимо выполнить сортировку справочника Заказчики по полю Код заказчика ( команда сортировка: главная/ раздел редактирования/ сортировка и фильтр/ сортировка от А до Я)

Применение функции ПРОСМОТР

Вставить в таблицу Ведомость значения поля Наименование заказчика соответствующие коду заказчика из справочной таблицы Заказчики с помощью функции ПРОСМОТР.

Наименование заказчика находится в прямой зависимости от Кода заказчика, введённого из первичного документа. Последовательность настройки функции ПРОСМОТР:

· Установить курсор в ячейку В6 и вызвать функцию ПРОСМОТР.

· В окне «Мастер функций шаг 1 из 2» нажать ОК.

· В следующем окне «Аргументы функции» установить все параметры:

 

- В окно «Искомое_значение» поместить А6 (щелчком по ячейке А6 таблицы Ведомость ).

- В окно «Просматриваемый_вектор» с помощью функциональной клавиши F3 вызвать имя блока ячеек Код_зак.

- В окно «Вектор_результатов» с помощью функциональной клавиши F3 вызвать имя блока ячеек Наим_зак.

- Нажать ОК в главном окне функции ПРОСМОТР.

- Скопировать полученную формулу в ячейки B7:B16 таблицы Ведомость. В результате столбец Наименование заказчика будет заполнен. Аналогично заполнить столбец Адрес (только в поле Вектор_результатов вызвать имя блока Адрес)

 

Применение функции ВПР

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

Для этого выполнить: действия:

· Установить курсор в ячейку С6 и вызвать функцию ВПР.

· В окне «Искомое_значение» поместить А6 (щелчком по ячейке А6 таблицы Ведомость).

· Для заполнения окна «Таблица» нажать функциональную клавишу F3 и выбрать из списка таблицу Заказчики.

· Для заполнения окна «Номер_столбца» ввести цифру 2, (номер столбца в справочной таб. Заказчики).

· При заполнении окна Интервальный просмотр надо учитывать диапазон просмотра. Если просматривать надо последовательно каждое значение, то следует ввести ноль. Нажать ОК в главном окне функции ВПР. Еслив справочной таблице можноискать ближайшее к искомому значение, ввести 1. Значению по умолчанию соответствует 0.

· Окно функции ВПР примет вид:

· Скопировать полученную формулу в ячейки В7:В16. В результате столбец Адрес будет заполнен.

Создание списка для поля Период

· Период создать в виде списка значений: 1 кв;2кв;3кв. Для этого установить курсор в ячейку D6 столбца Период. В пункте меню Данные в разделе Работа с данными кликнуть Проверка данных. В диалоговом окне Проверка вводимых значений в окне «Тип данных» выбрать Список.

· В окне «Источник». Так как список состоит всего из 3-х значений, то их можно ввести прямо в окне Источник, разделяя знаком точка с запятой«;». Нажать ОК.

· Скопировать настройку и заполнить данными поле Период согласно таблице.

Заполнить данными поля Сумма к выплате и Оплачено в соответствии с исходной таблицей.

Расчёт полей Разница и Долг.

Рассчитать поле Разница = Сумма к выплате - Оплачено. Активизировать ячейку G6. Ввести знак = (равно), затем щёлкнуть ячейку Е6, затем знак – (минус), щёлкнуть ячейку F6. В ячейке получится выражение = Е6 - F6.. Нажать Enter. Затем протащить маркер по всем ячейкам столбца, в которых надо получить результат, это ячейки G6:G16.

Рассчитать значения поля Долг, используя логическую функцию ЕСЛИ. При значении поля Разница больше нуля Долг равен Разнице, в противном случае Долг равен нулю.

Логические функции

Логические функции ЕСЛИ, НЕ, И, ИЛИ используют логические выражения для определения истинности заданного условия. Например, каждая из приведённых формул является логическим выражением:

= А1>A2 = СРЗНАЧ(В1:В6) =СУММ(6;7;8) = С2=”Среднее’

Любое логическое выражение должно содержать, по крайней мере, один оператор сравнения, который определяет отношение между элементами логического выражения. Например, в логическом выражении А1>А2 оператор больше (>). В качестве операторов сравнения могут быть: =, >, <, >=, <=, <> (не равно). Результатом логического выражения является логическое значение ИСТИНА или логическое значение ЛОЖЬ.