Elettracompany.com

Компьютерный справочник
7 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Бизвлечь в excel

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

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

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

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

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

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

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

    ДСРЗНАЧ (DAVERAGE) — Вычисляет среднее значение выбранных записей базы данных:
    =ДСРЗНАЧ( A6:E12 ;5; A1:F3 )
    =ДСРЗНАЧ( A6:E12 ;»Прибыль»; A1:F3 )
    =DAVERAGE( A6:E12 ,5, A1:F3 ) вернет значение 90 000р. , т.к. сумма прибыли отобранных записей равна 180 000р., а всего отобрано 2 записи. 180 000/2 = 90 000 .

БСЧЁТ (DCOUNT) — Подсчитывает количество числовых ячеек в базе данных:
=БСЧЁТ( A6:E12 ;5; A1:F3 )
=БСЧЁТ( A6:E12 ;»Прибыль»; A1:F3 )
=DCOUNT( A6:E12 ,5, A1:F3 ) вернет число 2 , т.к. только две строки в таблице отвечают критериям

БСЧЁТА (DCOUNTA) — Подсчитывает количество непустых ячеек в базе данных:
=БСЧЁТА( A6:E12 ;4; A1:F3 )
=БСЧЁТА( A6:E12 ;»Прибыль»; A1:F3 )
=DCOUNTA( A6:E12 ,4, A1:F3 ) вернет 2, т.е. подсчитает в отвечающих критериям строках количество непустых ячеек в столбце «Прибыль»

БИЗВЛЕЧЬ (DGET) — Извлекает из базы данных одну запись, удовлетворяющую заданному условию:
=БИЗВЛЕЧЬ( A6:E12 ;5; A1:F3 )
=БИЗВЛЕЧЬ( A6:E12 ;»Прибыль»; A1:F3 )
=DGET( A6:E12 ,5, A1:F3 ) для заданных условий вернет значение ошибки #ЧИСЛО! (#NUM!) , т.к. этим условиям отвечает более одной записи. Если же указать диапазон для критерия как:
=БИЗВЛЕЧЬ( A6:E12 ;5; A1:F2 ) то функция вернет значение 75 000р. , т.е. единственную запись о прибыли для Яблонь с высотой больше 3 и меньше 6 (в данный промежуток попадает лишь строка 10 — Яблона, высота 5)

ДМАКС (DMAX) — Находит максимальное значение среди выделенных записей базы данных:
=ДМАКС( A6:E12 ;5; A1:F3 )
=ДМАКС( A6:E12 ;»Прибыль»; A1:F3 )
=DMAX( A6:E12 ,5, A1:F3 ) вернет сумму 105 000р. , т.к. это максимальная прибыль из всех отвечающих критериям строк.

ДМИН (DMIN) — Находит минимальное значение среди выделенных записей базы данных:
=ДМИН( A6:E12 ;5; A1:F3 )
=ДМИН( A6:E12 ;»Прибыль»; A1:F3 )
=DMIN( A6:E12 ,5, A1:F3 ) вернет сумму 75 000р. , т.к. это минимальная прибыль из всех строк, отвечающих критериям

БДПРОИЗВЕД (DPRODUCT) — Перемножает значения определенного поля в записях базы данных, удовлетворяющих условию:
=БДПРОИЗВЕД( A6:E12 ;3; A1:F3 )
=БДПРОИЗВЕД( A6:E12 ;»Возраст»; A1:F3 )
=DPRODUCT( A6:E12 ,3, A1:F3 ) вернет 210 , т.к. будут перемножены все значения столбца «Возраст», отвечающие критериям( 14*15=210 )

ДСТАНДОТКЛ (DSTDEV) — Оценивает стандартное отклонение по выборке из выделенных записей базы данных:
=ДСТАНДОТКЛ( A6:E12 ;4; A1:F3 )
=ДСТАНДОТКЛ( A6:E12 ;»Урожайность»; A1:F3 )
=DSTDEV( A6:E12 ,4, A1:F3 ) вернет 0,707107 , т.е. оценку стандартного отклонения урожайности по указанным критериям.

ДСТАНДОТКЛП (DSTDEVP) — Вычисляет стандартное отклонение по генеральной совокупности из выделенных записей базы данных:
=ДСТАНДОТКЛП( A6:E12 ;4; A1:F3 )
=ДСТАНДОТКЛП( A6:E12 ;»Урожайность»; A1:F3 )
=DSTDEVP( A6:E12 ,4, A1:F3 ) вернет 0,5 , т.е. точное стандартное отклонение урожайности по указанным критериям, если считать, что данные в базе данных описывают генеральную совокупность всех деревьев в саду.

БДСУММ (DSUM) — Суммирует числа в поле для записей базы данных, удовлетворяющих условию:
=БДСУММ( A6:E12 ;5; A1:F3 )
=БДСУММ( A6:E12 ;»Прибыль»; A1:F3 )
=DSUM( A6:E12 ,5, A1:F3 ) вернет сумму прибыли всех строк, отвечающих критериям, т.е. 180 000р.
=БДСУММ( A6:E12 ;5; A1:A2 )
=DSUM( A6:E12 ,5, A1:A2 ) вернет сумму прибыли от всех Яблонь, т.е. 225 000р.

БДДИСП (DVAR) — Оценивает дисперсию по выборке из выделенных записей базы данных:
=БДДИСП( A6:E12 ;4; A1:A2 )
=БДДИСП( A6:E12 ;»Урожайность»; A1:A2 )
=DVAR( A6:E12 ,4, A1:A2 ) вернет 0,5 , что будет оценкой дисперсии урожайности по указанным критериям, если считать, что данные в таблице являются выборкой из генеральной совокупности всех деревьев в саду

  • БДДИСПП (DVARP) — Вычисляет дисперсию по генеральной совокупности из выделенных записей базы данных:
    =БДДИСПП( A6:E12 ;4; A1:A2 )
    =БДДИСПП( A6:E12 ;»Урожайность»; A1:A2 )
    =DVARP( A6:E12 ,4, A1:A2 ) вернет 10,66667 , т.е. точную дисперсию урожайности Яблонь и Вишень, если считать, что данные в базе данных описывают генеральную совокупность всех деревьев в саду
  • Функция БИЗВЛЕЧЬ() в EXCEL

    Функция БИЗВЛЕЧЬ() , английский вариант DGET(), извлекает из столбца (списка) или таблицы отдельное значение, удовлетворяющее заданным условиям.

    Функция БИЗВЛЕЧЬ() относится к наиболее редко используемым функциям и этому есть объективные причины. Рассмотрим синтаксис этой функции и причины ее непопулярности.

    Читать еще:  Текстовый формат в excel

    Синтаксис функции БИЗВЛЕЧЬ()

    Для использования этой функции требуется чтобы:

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

    БИЗВЛЕЧЬ( база_данных;поле;условия ) База_данных представляет собой диапазон ячеек с данными связанными логически, т.е. таблицу. Верхняя строка таблицы должна содержать заголовки всех столбцов. В Базе_данных строки называются записями, а столбцы — полями. Поле — заголовок столбца, из которого выводится значение, если выполняется условие. Аргумент Поле можно заполнить введя:

    • текст, представляющий собой название одного из заголовков Базы_данных . Текст указывается в двойных кавычках, например «Возраст» или «Урожай»,
    • число (без кавычек), задающее положение столбца в Базе_данных : 1 — для первого столбца, 2 — для второго и т.д.
    • ссылку на заголовок столбца.

    Условия — интервал ячеек, который содержит задаваемые условия (т.е. таблица критериев). Структура таблицы с критериями отбора для БИЗВЛЕЧЬ() аналогична структуре для Расширенного фильтра и, например, функции БДСУММ() .

    Если ни одна из записей не удовлетворяет условию, функция БИЗВЛЕЧЬ() возвращает значение ошибки #ЗНАЧ!

    Если условию удовлетворяет более чем одна запись, функция БИЗВЛЕЧЬ() возвращает значение ошибки #ЧИСЛО!

    Задачи

    Предположим, что в диапазоне A 10:С15 имеется таблица продаж ( База_данных ), содержащая поля (столбцы) Товар , Продавец и Продажи (см. файл примера ).

    Сформулируем задачи в виде вопросов.

    Вопрос 1 (Продал ли Мясо Продавец Белов ? Если продал, то за сколько?). Найдем строку, в которой в столбце Товар содержится значение Мясо , а столбце Продавец содержится значение Белов . Если такая строка есть в таблице, то выведем соответствующее значение из столбца Продажи .

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

    • Условия отбора должны быть записаны в специальном формате: =»=Мясо» (будет производиться поиск в столбце Товар только значений точно совпадающих со словом Мясо (или мясо , МЯСО , т.е. без учета регистра). Если написать условие не в виде =»=Мясо», а просто ввести в ячейку значение Мясо , то условию будут удовлетворять текстовые строки, которые содержат слово Мясо , например, « СвежееМЯСО», «Мясо барана» и пр.)
    • Предполагаем, что база_данных (исходная таблица) находится в A10:C15 . С10 – это ссылка на заголовок столбца, из которого выводится значение, если выполняется условие. B4:С5 – ссылка на табличку критериев (см. рисунок выше) Итоговая формула выглядит так =БИЗВЛЕЧЬ(A10:C15;C10;B4:C5)

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

    Вроде функция не плохо справляется, но что произойдет, если в таблице нет удовлетворяющих критерию записей? Функция вернет значение ошибки #ЗНАЧ! A что произойдет, если в таблице 2 и более записи удовлетворяющие критерию? Функция вернет значение ошибки #ЧИСЛО! К сожалению, в EXCEL нет функции, умеющей различать эти ошибки: для ЕОШ() , ЕОШИБКА() , ЕСЛИОШИБКА() эти ошибки неразличимы. Т.е. пользователю нужно помнить какой вид ошибки возвращается в каждом случае и принимать в зависимости от этого решение — это не удобно.

    Таким образом, функция БИЗВЛЕЧЬ() НЕ возвращает ошибку только в случае, если удовлетворяющая критерию запись единственная. Более логичным решением выглядит формула =СУММПРОИЗВ((A11:A15=»Мясо»)*((B11:B15)=»Белов»)*C11:C15) , которая возвращает ошибку только в случае, если запись удовлетворяющая критерию не обнаружена. Если обнаружено 2 и более записи, то соответствующие значения суммируются.

    Вопрос 2 (Есть ли товар ФРУКТЫ (с учетом регистра)?). Выполним поиск в столбце Товар слова ФРУКТЫ с учетом регистра (например, слово фрукты не будет удовлетворять условию поиска).

    В качестве условия отбора можно использовать значение, вычисляемое при помощи формулы. Формула должна возвращать результат ИСТИНА или ЛОЖЬ. Для этого введем в ячейку С3 файла примера формулу =СОВПАД(«ФРУКТЫ»;A11) , а в С2 вместо заголовка введем произвольный поясняющий текст, например, « ФРУКТЫ с учетом регистра » (заголовок не должен повторять заголовки исходной таблицы).

    Записать формулу можно так =БИЗВЛЕЧЬ(A10:A15;A10;C2:C3) Результат — слово ФРУКТЫ (значит такое слово есть в диапазоне А10:А15 и оно единственное).

    Альтернативная формула: =ЕСЛИ(СУММПРОИЗВ(—СОВПАД(«ФРУКТЫ»;A11:A15));»ФРУКТЫ»;»Нет»)

    Вопрос 3 (Есть ли продавец с фамилией начинающейся на Ро ?). Выполним поиск в столбце Продавец с использованием подстановочного знака *.

    В качестве условия можно записать в ячейке B3 формулу =»=Ро*»

    Формула =БИЗВЛЕЧЬ(B10:B15;B10;B2:B3) вернет слово Рощин (значит в диапазоне B10:B15 имеется такая фамилия и она единственная начинается на Ро ).

    В этом случае гораздо предпочтительнее выглядит формула =ВПР(«Ро*»;B11:B15;1;ЛОЖЬ) , т.к. не требуется создавать отдельную табличку с критериями и в случае наличия нескольких фамилий начинающихся на Ро , будет выведена первая фамилия, а не ошибка #ЧИСЛО!, как в случае с функцией БИЗВЛЕЧЬ() .

    Вопрос 4 (Есть ли продавец с фамилией длиной 5 букв?). Выполним поиск в столбце Продавец с использованием подстановочного знака ?.

    В качестве условия можно записать в ячейке А3 формулу =»=. «

    Формула =БИЗВЛЕЧЬ(B10:B15;B10;A2:A3) вернет ошибку #ЧИСЛО! (т.к. таких фамилий несколько).

    В этом случае гораздо предпочтительнее выглядит формула =ВПР(«. «;B11:B15;1;ЛОЖЬ) , по тем же причинам, что и в предыдущей задаче.

    Вывод : забудьте про функцию БИЗВЛЕЧЬ() , если только Вы не на 100% уверены в том, что в таблице гарантировано имеются данные удовлетворяющие критериям, причем в единственном экземпляре.

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

    Функции баз данных Excel, например, БДСУММ (DSUM), БСЧЁТ (DCOUNT) и другие могут работать вместо, возможно, тысяч функций, сокращая, таким образом, и время пересчета, и объем рабочей книги. При работе с функциями баз данных Excel можно указать до 256 различных критериев.

    Может понадобиться, например, суммировать значения в столбце А, только если соответствующие значения в столбце В больше 100, а соответствующий возраст в столбце С меньше 40. Если, однако, вы хотите суммировать те значения, для которых соответствующие значения в столбце В меньше 50, понадобится использовать другую функцию и другой диапазон критерия. Было бы намного проще, если бы была только одна функция, для которой можно быстро и просто изменить критерий! Если вы раньше никогда не работали с функциями баз данных Excel, настоятельно рекомендуем познакомиться с ними, так как они прекрасно подходят для выделения статистической информации из базы данных или таблицы Excel.

    Читать еще:  Видео с экрана андроид без рут

    Чтобы увидеть, как это работает, задайте данные. Укажите те же заголовки столбцов, но в самих столбцах могут быть любые фиктивные данные. Присвойте этой таблице данных, включая все заголовки столбцов, имя AllData. Назовите лист Data. Добавьте еще один рабочий лист и назовите его Results. В ячейке А2 введите следующую формулу: =Data!A1.

    Скопируйте ее до ячейки F2, чтобы получить зеркальное отображение заголовков вашей таблицы. В ячейке A3 введите любое имя, присутствующее в таблице на листе с данными, например, John D. Затем в ячейке ВЗ введите формулу =DGET(AllData;В2;$А$2:$А$3), в русской версии Excel =БИЗВЛЕЧЬ(AllData;В2;$А$2:$А$3). Скопируйте эту формулу до ячейки F3 и отформатируйте ячейки C3:F3 нужным образом.

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

    Можно продвинуться еще дальше и использовать функцию БСЧЁТ (DCOUNT), чтобы узнать количество человек, для которых значение Full Cost больше одного указанного числа, а значение Percent Paid меньше другого. Для этого сначала необходимо создать динамический именованный диапазон для столбцов Full Cost и Percent Paid. В поле Имя (Names in workbook) введите FullCost. В поле Формула (Refers to) введите следующую формулу и щелкните на кнопке Добавить (Add): =OFFSET($С$2;0;0;COUNTA($С$2:$С$1000);1), в русской версии Excel =СМЕЩ($С$2;0;0;СЧЁТЗ($С$2:$С$1000);1). После этого в поле Имя (Names in workbook) введите PercentPaid. В поле Формула (Refers to) введите следующую формулу и щелкните на кнопке Добавить (Add): =OFFSET($E$2;0;0;COUNTA($E$2:$E$1000);1), в русской версии Excel =СМЕЩ($Е$2;0;0;СЧЁТЗ($Е$2:$Е$1000);1).

    Перейдите на лист Results, выделите ячейку АИ и выберите команду Данные → Проверка (Data → Validation). В поле Тип данных (Allow) выберите Список (List), а в поле Источник (Source) введите =Full_Cost. Щелкните на кнопке ОК. Выделите ячейку АИ и выберите команду Данные → Проверка (Data → Validation). В поле Тип данных (Allow) выберите Список (List), а в поле Источник (Source) введите =Percent_Paid. Щелкните на кнопке ОК.

    В ячейке А12 введите формулу =Data!C1. Выделите ячейку В12 и введите следующую формулу: =Data!E1. Выделите ячейку А13 и введите следующую формулу: =»>»&А11. Выделите ячейку В13 и введите следующую формулу: =» =, >, Базы данных • Формулы • Функции

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

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

    БДДИСП

    Результат: Дисперсия [рассеяние] генеральной совокупности, определенная путем оценки выборки относительно всех выбранных значений.

    Аргументы:

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

    БДДИСПП

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

    См. описание функции БДДИСП.

    БДПРОИЗВЕД

    Результат: Произведение всех выбранных значений.

    См. описание функции БДДИСП.

    БДСУММ

    Результат: Сумма всех выбранных значений.

    См. описание функции БДДИСП.

    БИЗВЛЕЧЬ

    Результат: Возвращает найденное в поле базы данных значение (удовлетворяющее заданному критерию), если оно единственное; возвращает значение ошибки #ЧИСЛО!, если критериям поиска удовлетворяет более одной записи данных; возвращает значение ошибки #ЗНАЧ!, если ни одна запись данных не удовлетворяет критериям поиска.

    См. описание функции БДДИСП.

    БСЧЕТ

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

    См. описание функции БДДИСП.

    БСЧЕТА

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

    См. описание функции БДДИСП.

    ДМАКС

    Результат: Наибольшее число в соответствующих критериям поиска записях данных поля поле в базе данных.

    См. описание функции БДДИСП.

    Результат:
    Наименьшее число в соответствующих критериям поиска записях данных поля поле в базе данных.

    См. описание функции БДДИСП.

    ДСРЗНАЧ

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

    См. описание функции БДДИСП.

    ДСТАНДОТКЛ

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

    См. описание функции БДДИСП.

    ДСТАНДОТКЛП

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

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

    Пример 6. На основе исходного списка (рис. 1) с использованием функций работы с базой данных:

    1) определить максимальную сумму в январе;

    2) извлечь из базы данных фамилию продавца, который совершил максимальную по стоимости сделку в следующую таблицу:

    Прежде чем воспользоваться функциями работы с базой данных необходимо задать область критериев (рис. 40). Затем в ячейку B24 нужно ввести формулу для вычисления максимальной суммы в январе месяце. Для этого необходимо в Мастере функций выбрать категорию функций Работа с базой данных. В списке функций этой категории выбрать функцию ДМАКС(). В диалоговом окне функции ДМАКС() задать необходимые аргументы функции: База_данных, Поле, Критерий (рис. 41).

    Рис. 40. Пример задания области критерии

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

    Рис. 41. Указание аргументов функции ДМАКС()

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

    Рис. 42. Область вывода результатов расчетов

    В ячейку С28 нужно ввести формулу извлечения фамилии продавца. Для этого необходимо в Мастере функций выбрать категорию функцийРабота с базой данных и в списке функций этой категории выбрать функцию БИЗВЛЕЧЬ(). В диалоговом окне функции БИЗВЛЕЧЬ() задатьнеобходимые аргументы функции: База_данных, Поле, Критерий (рис. 43).

    Рис. 43. Указание аргументов функции БИЗВЛЕЧЬ()

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

    Читать еще:  График тангенс в excel

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

    Рис. 44. Результат определения максимального

    значения и извлечения данных

    На основе электронных таблиц можно создавать несложные базы данных, в которых удобно хранить сведения, например, о сотрудниках предприятия, прайс-листы, информацию о заказах. Аналогом таблицы базы данных в программе MS Ехсеl служит Список.

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

    74. Финансовые функции: для анализа инвестиций

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

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

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

    Платеж (аргумент плт) – это размер одноразовой выплаты или одноразовой выплаты с процентами.

    Процентная годовая ставка (аргумент ставка) – это процентное выражение начальной суммы.

    Срок (аргумент кпер) – общее время действия вклада или погашения займа (измеряется в месяцах или годах).

    Период (аргумент период) – это время, на протяжении которого проводятся выплаты или начисления процентов.

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

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

    75. Финансовые функции: для вычисления скорости оборота.

    Для решения этой задачи необходимо использовать финансовую функцию СТАВКА, которая имеет следующий синтаксис:

    Для этого запустите редактор электронных таблиц MS EXCEL и введите исходные данные так, как приведено на рис. 3.1 (исходные данные выделены серым цветом).

    Рис. 3.1. Исходные данные и результат решения задачи 1

    Следует обратить внимание:

    · периодические выплаты должны происходить ежемесячно, поэтому необходимо перевести значение аргумента кпер, так же как и в задаче 2;

    · аргумент плт отрицателен, так как получатель кредита, выплачивая ежемесячный платеж, «отдает» деньги;

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

    · аргумент бс равен нулю, так как кредит должен быть полностью погашен;

    · аргумент тип равен единице, так как оплата кредита происходит в начале каждого месяца;

    · аргумент предположение задает предполагаемое значение ставки, если этот аргумент опущен, как в этой задаче, то он полагается равным 10 %.

    После ввода всех необходимых данных, нужно воспользоваться функцией СТАВКА и произвести вычисление по следующей формуле: =СТАВКА(В1;В2;В4;В3;В4;В5;1).

    Полученный результат – ежемесячная процентная ставка, но по условию задачи требуется найти годовую процентную ставку. Это можно сделать по формуле =В7*12. Результат расчета приведен на рис. 3.1.

    76. Финансовые функции: для расчета амортизации.

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

    1) равномерным, функция АПЛ (SLN);

    2) суммы чисел (лет), функция АСЧ (SYD);

    3) фиксированного уменьшения остатка с использованием функции ФУО (DB);

    4) уменьшающегося остатка или двойного процента, функция ДДОБ (DDB).

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

    Общие аргументы функций представлены в табл. 1.

    Аргументы функций Excel для расчета амортизации

    Прикладные программы: Базы данных. Назначение и основные функции. Проектирование баз данных. СУБД.

    77. Основные понятия и краткая характеристика Microsoft Access Архитектура Microsoft Access

    База данных — это средство сбора и организации информации. В базах данных могут содержаться сведения о людях, продуктах, заказах и т. д. Многие базы данных изначально представляют собой список в текстовом процессоре или электронной таблице. По мере того как список разрастается, в нем накапливаются излишние и противоречивые данные. В форме списка эти данные становится все труднее понять, а возможности поиска или извлечения подмножеств данных для просмотра весьма ограничены. Когда возникают подобные проблемы, полезно перенести информацию в базу данных, созданную с помощью системы управления базами данных (СУБД), например Office Access 2007.

    Компьютерная база данных представляет собой хранилище объектов. В одной базе данных может содержаться несколько таблиц. Например, система складского учета, в которой используются три таблицы, — это не три базы данных, а одна, содержащая три таблицы. В базе данных Access таблицы сохраняются в одном файле вместе с другими объектами, такими как формы, отчеты, макросы и модули, если только база данных не предназначена специально для использования данных или кода из другого источника. Базы данных, созданные в формате Access 2007, имеют расширение имени файла ACCDB, а базы данных, созданные в более ранних форматах Access, — расширение MDB. Приложение Access 2007 можно использовать для создания файлов в более ранних форматах файлов (например, Access 2000 и Access 2002-2003).

    Приложение Access предоставляет следующие возможности:

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

    Ниже приведен список основных объектов базы данных Access.

    78. Начало и окончание работы с Microsoft Access

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

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