5347

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

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

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

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

Русский

2012-12-07

55.5 KB

106 чел.

Работа с базами данных в 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


 

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

46500. Понятие и методы калькуляции затрат 17.86 KB
  Калькуляция служит основой для определения средних издержек производства и установления себестоимости продукции. Методы калькуляции это методы расчёта издержек производства себестоимости продукции объёма незавершённого производства основанные на калькуляции затрат. Попередельный метод калькуляции это метод исчисления себестоимости применяемый на предприятиях где исходный материал в процессе производства проходит ряд переделов или где из одних исходных материалов в одном технологическом процессе получают различные виды продукции....
46501. Техническое диагностирование. Этапы комплексной диагностики участков МТ. 17.87 KB
  Основными задачами контроля и диагностики МТ являются определение технического состояния на основе комплексного мониторинга в процессе создания и эксплуатации системы оценка и прогнозирование динамики технического состояния с целью обеспечения надежной и безопасной эксплуатации газотранспортной системы. Контроль и мониторинг технического состояния трубопроводных систем включает: получение информации в предэксплуатационный период ранняя диагностика из проектных материалов включая материалы изысканий лабораторных исследований грунтов...
46502. Диаграммы UML 17.91 KB
  Диаграммы UML. UML определяет следующие диаграммы: 1. Диаграммы применения use cse Или диаграммы вариантов использования Представляют собой граф из действующих лиц ctors и их взаимодействие с системой представленное сценариями применения. Диаграммы классов Cодержат набор статических декларативных элементов как например классы типы их связи объединенные в граф.
46503. Обеспечение электробезопасности техническими способами и СЗ 17.91 KB
  При случайном прикосновении для обеспечения электробезопасности применяют: защитные оболочки защитные ограждения временные или стационарные безопасное расположение токоведущих частей изоляцию этих частей и РМ малое U защитное отключение предупредительную сигнализацию блокировку и знаки безопасности; а при прикосновении к нетоковедущим металлическим частям защитное заземление зануление выравнивание потенциала защитное отключение изоляцию нетоковедущих частей электроразделение сети малое U контроль электроизоляции и СИЗ....
46504. Формы производственной деятельности фирмы 17.98 KB
  Различают три основные формы организации производства: Специализация Кооперирование Комбинирование Специализация производства Специализация производства выражается в том что каждое производство ограничивается изготовлением определённого вида конструктивной и технологически однородной продукции. Соответственно этому различают четыре вида специализации предприятий: предметную; подетальную иногда называют узловая; технологическую; по услугам вспомогательного производства. Подетальная специализация характеризуется...
46505. Природа грамматического значения: общая характеристика, отношение к лексическому значению, функциональный статус 18.04 KB
  Природа грамматического значения: общая характеристика отношение к лексическому значению функциональный статус. Большинство слов обладает двумя значениями: лексическим и грамматическим. В области морфологии это общие значения слов как частей речи напр. значения предметности у существительных процессуальное у глаголов а также частные значения словоформ и слов в целом противопоставляемые друг другу в рамках морфологических категорий например значения того или иного времени лица числа рода.
46506. Поверхностное упрочнение детали. Выбор метода поверхностного упрочнения 18.07 KB
  При обработке поверхности шлифованием и полированием устраняющей неровности которые служат концентраторами напряжений повышается усталостная прочность детали. Назначение метода упрочняющей обработки зависит от условий работы детали в машине и ее технологических особенностей. Деталь помещают внутри спирали индуктора или под проводником по которому пропускается переменный ток большой частоты; он вызывает появление вихревых токов на поверхности детали и быстро разогревает слой с наибольшей плотностью индуцированного тока.
46507. Метод сравнительного анализа продаж в оценке недвижимости 18.08 KB
  Метод сравнения продаж базируется на информации о недавних сделках с аналогичными объектами на рынке и сравнении оцениваемой недвижимости с аналогами. Исходной предпосылкой применения метода сравнения продаж является наличие развитого рынка недвижимости. Недостаточная же развитость данного рынка а также то что оцениваемый объект недвижимости является специализированным либо обладает исключительными выгодами или обременениями не отражающими общее состояние рынка делают применение этого подхода нецелесообразным.
46508. ДХШ и ее место в системе художественного образования. Цели, содержание и методика обучения в ДХШ 18.08 KB
  Цели содержание и методика обучения в ДХШ. План: определение ДХШ место ДХШ в системе художественного образования цели содержание методика обучения ДХШ – это учреждение дополнительного образования цель которого реализация творческого потенциала детей в области пластических искусств через реализацию доп. Содержание обучения включает обязательные предметы: рисунок жив комп плэнеристория ИЗО дпи или декор. дизайн Приём происходит в 1011 лет срок обучения обычно 4 года.