double arrow

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

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

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

Поле определяет столбец, используемый функцией. Аргумент "поле" может быть задан как текст с названием столбца в двойных кавычках, например "БАЛАНС" в приведенном ниже примере базы данных, или как число, задающее положение столбца в списке: 1 — для первого поля, 2 — для второго, 7 – для столбца "БАЛАНС".

Критерий — это интервал ячеек, который содержит задаваемые условия. Любой интервал, который содержит, по крайней мере, одно название столбца и, по крайней мере, одну ячейку под названием столбца с условием, может быть использован как аргумент критерий.

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

Синтаксис функций баз данных имеет следующий вид (за исключением функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ):

БДФункция (база_данных; поле; критерий)

Функции баз данных Таблица 6.1

Наименование функции БД Назначение функции БД
ДСРЗНАЧ Усредняет значения в столбце списка или базы данных, удовлетворяющих заданным условиям.
БСЧЁТ Подсчитывает количество ячеек в столбце списка или базы данных, содержащих числа, удовлетворяющие заданным условиям. Примечание. Если аргумент «поле» опущен, то функция БСЧЁТ подсчитывает количество записей в базе данных, отвечающих критериям.
БСЧЁТА Подсчитывает непустые ячейки в столбце списка или базы данных, которые удовлетворяют заданным условиям. Примечание. Если аргумент «поле» опущен, то функция БСЧЁТА подсчитывает количество записей в базе данных, отвечающих критериям.
БИЗВЛЕЧЬ Извлекает отдельное значение из столбца списка или базы данных, которое удовлетворяет заданным условиям. Примечание. Если ни одна из записей не удовлетворяет критерию, то функция БИЗВЛЕЧЬ возвращает значение ошибки #ЗНАЧ!. Если более чем одна запись удовлетворяет критерию, то функция БИЗВЛЕЧЬ возвращает значение ошибки #ЧИСЛО!.
ДМАКС Возвращает наибольшее число в столбце списка или базы данных, которое удовлетворяет заданным условиям.
ДМИН Возвращает наименьшее число в столбце списка или базы данных, которое удовлетворяет заданным условиям.
БДПРОИЗВЕД Перемножает значения в столбце списка или базы данных, которые удовлетворяют заданным условиям.
ДСТАНДОТКЛ Оценивает стандартное отклонение на основе выборки из генеральной совокупности, используя числа в столбце списка или базы данных, которые удовлетворяют заданным условиям.
ДСТАНДОТКЛП Вычисляет стандартное отклонение генеральной совокупности, используя числа в столбце списка или базы данных, которые удовлетворяют заданным условиям.
БДСУММ Суммирует числа в столбце списка или базы данных, которые удовлетворяют заданным условиям.
БДДИСП Оценивает дисперсию генеральной совокупности по выборке, используя числа в столбце списка или базы данных, которые удовлетворяют заданным условиям.
БДДИСПП Вычисляет дисперсию генеральной совокупности, используя числа в столбце списка или базы данных, которые удовлетворяют заданным условиям.

6.1. Сформируйте и заполните накопительную ведомость "Переоценка основных средств производства" (таб. 6.2) и выполните необходимые вычисления.

· Используя значения балансовой стоимости (БС) и износа объекта (ИО), рассчитайте:

- остаточную стоимость объекта (ОС): ОС = БС – ИО

- восстановительную полную стоимость объекта (ВП) и

- восстановительную остаточную стоимость объекта (ВО):

ВП = БС * К, ВО = ОС * К,

где К = 3.0, если БС> 500 млн. руб., и К = 2.0, если БС<= 500 млн. руб.

· Создайте новый лист Excel и назовите его Функции БД. · Расположите таблицу таким образом, чтобы заголовок столбца "Наименование объекта" располагался в ячейке А1. Расчетная часть таблицы выглядит следующим образом - рис. 6.1.

Рис. 6. 1. Результаты расчетов таблицы «Переоценка основных средств производства»

6.2. На основе исходной накопительной ведомости с использованием функций базы данных БДСУММ(), ДСРЗНАЧ() и БСЧЕТ() рассчитать и сформировать таблицу 6.3:

Таблица 6.3.

  Основные объекты Вспомогательные объекты
Суммарный износ    
Средняя балансовая стоимость    
Средняя остаточная стоимость    
Количество объектов    

· Введите таблицу 6.3 в ячейки A18:C22.

· Рассчитайте суммарный износ основных объектов с помощью функции БДСУММА (база_данных; поле; критерий) – рис. 6.2.

· Вначале сформируйте критерий отбора, скопировав название поля Вид объекта в ячейку B15. Ниже в ячейку B16 введите Основной.

· аргумент функции база_данных ссылается на интервал ячеек, формирующих список A1:G11

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

· Критерий — интервал ячеек B15:B16

· В ячейку B19 введите функцию (рис. 6.2).

Рис. 6. 2. Окно Аргументы функции БДСУМ

· Сформируйте критерий отбора для вспомогательных видов объектов в ячейках C15:C16. Рассчитайте остальные параметры таблицы по аналогии (рис. 6.4). Результаты вычислений показаны на рис. 6.3.

Рис. 6. 3. Результаты вычислений задания 6.2

Рис. 6. 4. Расчет параметров таблицы для задания 6.2

6.3. На основе исходной накопительной ведомости с использованием функций базы данных ДМАКС(),и БИЗВЛЕЧЬ() рассчитать и сформировать таблицу 6.4:

Таблица 6.4

  Основные объекты Вспомогательные объекты
  Износ, млн. руб. Наименование объекта Износ, млн. руб. Наименование объекта
Максимальный износ        

· Введите таблицу в ячейки A29:E31.

· Вычислите максимальный износ для основных и вспомогательных объектов по аналогии с предыдущим заданием, введя функцию ДМАКС() в ячейки B31 и D31 соответственно.

· Найдите наименование объекта с максимальным износом в группе основных объектов с помощью функции БИЗВЛЕЧЬ (база_данных; поле; критерий)

· аргумент функции база_данных ссылается на интервал ячеек, формирующих список A1:G11

· аргумент поле определяет столбец с наименованиями объектов ячейка А1

· Критерий — интервал ячеек, определяющий значение максимального износа B30:B31

· В ячейку С31 введите функцию БИЗВЛЕЧЬ (рис. 6.5).

Рис. 6. 5. Окно Аргументы функции БИЗВЛ

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

Рис. 6. 6. Расчет параметров таблицы для задания 6.3

· Рассчитайте остальные параметры таблицы по аналогии (рис. 6.7).

Рис. 6. 7. Результаты вычислений задания 6.3

6.4. На основе исходной накопительной ведомости для объектов, балансовая стоимость которых больше 500 млн. руб., с использованием функций базы данных ДСРЗНАЧ(), ДМАКС(), ДМИН() и БСЧЕТ() рассчитайте и сформируйте таблицу 6.5:

Таблица 6.5

  Основные объекты Вспомогательные объекты
Средний износ    
Минимальный износ    
Минимальная восстановительная полная стоимость    
Максимальная восстановительная полная стоимость    
Средняя балансовая стоимость    
Средняя восстановительная остаточная стоимость    
Количество объектов    

Критерий отбора для функций этого задания содержит два условия: вид объекта и его балансовая стоимость.

· Сформируйте критерий отбора для основных объектов в ячейках А34:В35 и для вспомогательных объектов в ячейках С34:D35, как показано на рис. 6.8.

Рис. 6. 8. Критерий отбора для задания 6.4

Результаты вычислений показаны на рис. 6.9.

Рис. 6. 9. Результаты вычислений задания 6.4


7. РАСЧЕТ ДЕБИТОРСКОЙ ЗАДОЛЖНОСТИ
(с использованием функций базы данных)

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

Для этой цели составлена таблица дебиторской задолженности за первую половину 2005 года – лист Дебиторская задолженность (рис. 7.1).

В столбце Код сделки занесены два числа 1 или -1.

1 означает, что товары нашей фирмы находятся на реализации.

-1 означает, что ваша фирма приняла на реализацию товары.

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


Понравилась статья? Добавь ее в закладку (CTRL+D) и не забудь поделиться с друзьями:  



Сейчас читают про: