5347

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

Лабораторная работа

Информатика, кибернетика и программирование

Работа с базами данных в MSEXCEL Цель: Приобрести навыки использования встроенных функций МS Ехсеl для работы со списками. Краткая теория Информационная технология обработки данных в информационных системах предполагает их хранение и обработку...

Русский

2012-12-07

55.5 KB

108 чел.

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

Цель: Приобрести навыки использования встроенных функций МS Ехсеl для работы со списками.

Краткая теория

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

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

Для достижения наибольшей эффективности при работе со списками необходимо следовать некоторым правилам.

Правила формирования базы данных в МS Ехсеl

  •  Имена полей создаются в первой строке списка
  •  Название поля не должно превышать 255 символов, однако по возможности следует использовать короткие названия.
  •  Для имен полей лучше использовать шрифт, размер, выравнивание, начертание и т.д., отличные от тех, которые назначены данным списка.
  •  Каждое поле (столбец) должно содержать во всех записях информацию одного типа.
  •  Не допускается включение пустых строк или столбцов в список.
  •  Не следует вводить дополнительные пробелы в начале ячейки, так как это может в дальнейшем повлиять на сортировку и поиск.
  •  Желательно для списка отводить отдельный лист Рабочей книги. Если это невозможно, то список от других элементов рабочего листа или других списков должен быть отделен хотя бы одной пустой строкой или одним пустым столбцом.
  •  Для всех элементов одного поля должен быть использован один формат.

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

ФУНКЦИИ РАБОТЫ С БАЗАМИ ДАННЫХ

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

Функция БДСУММ возвращает сумму значений указанного столбца базы данных для  записей, удовлетворяющих критериям отбора.

Формат

БСУММ(база_данных; поле; критерий)

  •  база_данных – интервал ячеек БД;
  •  поле   определяет столбец, используемый функцией. Аргумент поле может быть задан как текст с названием столбца в двойных кавычках, например «Стаж» или как число, задающее положение столбца в списке: 1 - для первого поля, 2 - для второго поля и так далее.
  •  критерий – интервал ячеек, который содержит критерий поиска (таблица критериев).

Функция ДСРЗНАЧ возвращает среднее значение в указанном столбце базы данных для  записей, удовлетворяющих критериям отбора.

Формат

ДСРЗАЧ(база_данных; поле; критерий)

  •  база_данных – интервал ячеек БД;
  •  поле – номер столбца, для которого вычисляется среднее значение;
  •  критерий – интервал ячеек, который содержит критерий поиска (таблица критериев).

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

Формат

ДМАКС(база_данных; поле; критерий)

  •  база_данных – интервал ячеек БД;
  •  поле – номер столбца, для которого находят максимальное значение;
  •  критерий – интервал ячеек, который содержит критерий поиска (таблица критериев).

Функция ДМИН возвращает минимальное значение в указанном столбце базы данных для  записей, удовлетворяющих критериям отбора.

Формат

ДМИН(база_данных; поле; критерий)

  •  база_данных – интервал ячеек БД;
  •  поле – номер столбца, для которого находят минимальное значение;
  •  критерий – интервал ячеек, который содержит критерий поиска (таблица критериев).

Функция БСЧЕТ подсчитывает количество ячеек, содержащих числа, в столбце списка или базы данных указанном поле всех записей, удовлетворяющих заданным условиям.

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

Формат

БСЧЕТ(база_данных; поле; критерий)

Функция БСЧЕТА возвращает количество непустых записей базы данных, которые удовлетворяют критериям поиска, в зоне, ограниченной аргументом поле. При отсутствии аргумента Поле функция возвращает количество непустых записей, соответствующих критерию, из всей базы данных.

Формат

БСЧЕТА(база_данных; поле; критерий)

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

Если таких записей несколько функция выдает значение ошибки #ЧИСЛО!, если записей, соответствующих условиям поиска в списке нет, то функция выдаст значение ошибки #ЗНАЧ!

Формат

БИЗВЛЕЧЬ(база_данных; поле; критерий)

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

Формат

БДДИСП (база_данных; поле; критерий)

Задание 1:

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

ИСХОДНЫЙ СПИСОК

      A                     B                           C                             D                           E                     F

Порядок выполнения

  1.  Создайте БД (заполните исходный список произвольными значениями в диапазоне A2:F21)
  2.  Создайте таблицу критериев (диапазон H2:H3). Для этого скопируйте заголовок столбца таблицы БД, по которому будет осуществляться выбор и группировка записей (в нашем примере это поле «Время открытия счета»). Под заголовком критерия должна оставаться свободная ячейка.

H

2

Время открытия счета

3

  1.  Постройте таблицу чувствительности с одной переменной: Для этого
    •  Ниже исходного списка в столбце В создайте вектор значений критериев.
    •  В смежном столбце на ячейку выше вектора- столбца критериев отбора .введите формулу расчета суммы вкладов (см. формат функции).

                                                         

B

C

24

 

=БДСУММ(A2:F21;6;H2:H3)

25

1 квартал

 

26

2 квартал

 

27

3 квартал

 

28

4 квартал

 

  •  Выделите диапазон таблицы чувствительности вместе с формулой (B24:C28).
    •  Воспользуйтесь командой меню Данные/ Таблица подстановки. В качестве ячейки подстановки по строкам укажите адрес свободной ячейки под заголовком таблицы критериев.
    •  Нажмите ОК и ознакомьтесь с результатом.

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

Задание 1:

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

Задание 2:

Самостоятельно решите следующую задачу: Рассчитать среднюю величину вкладов в каждом квартале для каждого типа вклада. Используйте технологию таблиц подстановок с двумя переменными (см. Лаб.раб. №4).

Содержание отчета

  1.  Титульный лист с постановкой задач
  2.  Результаты выполнения задания 1 (с формулами)
  3.  Результаты выполнения задания 1 (с результатами вычислений)
  4.  Результаты выполнения задания 2 (с формулами)
  5.  Результаты выполнения задания 2 (с результатами вычислений)
  6.  Гистограмма по результатам задания 1
  7.  Гистограмма по результатам задания 2


 

А также другие работы, которые могут Вас заинтересовать

78952. Философия техники (фт), ее генезис, предмет и задачи 47 KB
  Философия техники фт ее генезис предмет и задачи. Проблема возникновения техники с разделением труда была поставлена Гегелем. Он показал важную роль техники в возникновении капитализма показал разрушительное воздействие машинного производства на человека.Ленк назвал Маркса первым философом техники.
78953. Становление, развитие и специфика технических наук 56.5 KB
  Становление развитие и специфика технических наук. Техника большую часть своей истории была мало связана с наукой; люди могли делать и делали устройства не понимая почему они так работают. Инженеры провозглашая ориентацию на науку в своей непосредственной практической деятельности руководствовались ею незначительно. После многих веков такой автономии наука и техника соединились в XVII веке в начале научной революции.
78954. Сущность и природа техники 47.5 KB
  Сущность и природа техники. Существует 5 основных подходов сущности техники. отношением: Техника и бытие Техника и человек Техника и природа Техника и социокультурный мир Техника и Бог Даются следующие трактовки техники: Онтологическая Хайдеггер. Это характерно для создания техники ремесленным трудом не преминим к современной технике.
78955. Технология и ее связь с техникой 47 KB
  Впервые термин «технология» появился в 70-х годах 18 века в Западной Европе. Если техники – «это», то технология – «как это сделано». Если техника – способ, то технология – способ способа. Если техника – есть ставшее, то технология – способ становления, если техника- способ преобразования мира, то технология – организация использование чел. этих средств. Если техника – артефакт, то технология – метод создания артефактов и учения о нем.
78956. Природа и техника, законы их функционирования и развития 59 KB
  Природа и техника законы их функционирования и развития. Закон – необходимое существенное устойчивое повторяющееся отношение связь между явлениями в природе и обществе. Это понятие закона родственно понятию сущности при данных условиях. Закономерность – объективно существующая повторяющая существенная связь явлений проявляющаяся в виде тенденций.
78957. Инновации в традиционном и техногенном обществах 29.5 KB
  Инновации в традиционном и техногенном обществах Традиционные общества является исторически первыми. Данный тип общества возник в глубокой древности распространен он и сейчас. Некоторые традиционные общества были поглощены техногенными другие приобрели гибридные черты балансируя между техногенными и традиционными ориентациями. При характеристике традиционных типов общества очевиден тот факт что они обладая замедленным темпом развития придерживаются устойчивых стереотипов своего функционирования.
78958. Ценности классической, неклассической, постнеклассической науки 39.5 KB
  Существуют социальные и внутренние ценности. Социальные ценности делятся на материальные и духовные. удовлетворение своих материальных потребностей научные ценности – истина добро зло итд Социальные ценности: частная собственность рыночная экономика деньги итд социальнополитические: свобода слова собраний критики различные права эстетические и философские ценности Внутри научные 1. Социальные и внутринаучные ценности диалектически связаны между собой.
78959. Социальная оценка техники 29 KB
  Социальная оценка техники Введение Узкий смысл понятия техники: под техникой понимается техническое устройство артефакт созданное человеком из элементов природы для решения конкретных культурных задач. Широкий смысл понятия техники: искусственный или организованный прием усиливающий улучшающий или облегчающий действие техника письма техника плавания техника вопросов и т. При изучении вопроса о последствия техники и технологии следует иметь в виду двойственный характер техники. Общая часть Проблемы негативных социальных и других...
78960. Техника и этика. Этика и профессиональная ответственность инженера 41 KB
  Это относится не только к использованию техники для целенаправленного уничтожения людей но также к повседневной эксплуатации инженернотехнических устройств. Проблемы негативных социальных и других последствий техники проблемы этического самоопределения инженера возникли с самого момента появления инженерной профессии. Однако сегодня человечество находится в принципиально новой ситуации когда невнимание к проблемам последствий внедрения новой техники и технологии может привести к необратимым негативным результатам для всей цивилизации и...