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

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

· база_данных:

· поле.

· критерий.

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

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

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

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

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

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

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

Таблица 1

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

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

1. Определить максимальную сумму в январе.

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

	Январь
	Сумма, руб.	Продавец
Максимальная сумма

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

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

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

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

Месяц	Сумма
январь	=ДМАКС(A1:F21.F1.A23:A24)

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

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

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

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

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

	январь
	Сумма	Продавец
Максимальная сумма		=БИЗВЛЕЧЬ(A1:F21.B1.B27:B28)

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

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

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

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

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

Лабораторная работа № 3. Анализ и обобщение данных в электронных таблицах

ВАРИАНТ № 1

В супермаркете имеется три склада с разными товарами: продукты питания, хозтовары, косметика. Ежегодно торговый склад производит уценку хранящейся продукции. Если продукция хранится на складе дольше 6 месяцев, то она уценивается в 1,5 раза, если продукция хранится на складе дольше 10 месяцев, то она уценивается в 2 раза.

Составьте ведомость уценки товара по каждому складу, которая должна содержать следующую информацию: наименование и дата поступления товара, срок хранения на текущий день, цена товара до уценки, цена товара после уценки.

Рассчитайте по каждому складу количество залежавшегося товара (срок хранения более 10 месяцев).

Постройте для одного из складов диаграмму, показывающую количество залежавшегося товара по отношению ко всему товару.

ВАРИАНТ № 2

Имеются сведения о студентах (10 человек) группы (фамилия, имя, отчество, номер зачетной книжки) и результатах экзаменационной сессии по 4 предметам: математике, физике, истории и информатике.

Необходимо определить:

· средний балл группы.

· максимальный средний балл в группе.

· минимальный средний балл в группе.

· количество студентов, имеющих средний балл больше 4.

· количество студентов, имеющих средний балл больше 3, но меньше 4.

· количество студентов, имеющих средний балл меньше 3.

· долю студентов, имеющих средний балл больше 4 от среднего балла группы.

Постройте диаграмму, показывающую средний балл студентов.

ВАРИАНТ № 3

В сельскохозяйственном кооперативе работают 8 сезонных рабочих. Оплата труда происходит по количеству собранных огурцов. Известна дневная норма сбора и стоимость одного килограмма огурцов. Сбор каждого килограмма сверх нормы оплачивается в 2 раза дороже.

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

Проведите ранжирование каждого рабочего по объему собранных овощей в каждом месяце.

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

ВАРИАНТ № 4

Услугами телефонной компании пользуются 8 клиентов. Оплата зависит от количества минут в месяц. Известна норма потребления и стоимость 1 минуты. Потребление сверх нормы оплачивается в 2 раза дороже. Оплата должна производиться до десятого числа следующего месяца. Если оплата задерживается, то за каждый день просрочки взимаются штрафные санкции в размере 1% от стоимости.

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

Постройте диаграмму, показывающую среднюю продолжительность разговоров каждого клиентом за летний период.

ВАРИАНТ № 5

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

· если средний балл 5, выплачивается 50%-ная надбавка к минимальной стипендии.

· если средний балл не менее 4,5, выплачивается 30%-ная надбавка к минимальной стипендии.

· если средний балл от 4 до 4,5 (включительно), выплачивается минимальная стипендия.

· если средний балл меньше 4, стипендия не выплачивается.

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

ВАРИАНТ № 6

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

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

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

ВАРИАНТ № 7

Билет на пригородном поезде стоит 5 руб., если расстояние до станции не больше 20 км. 13 руб., если расстояние больше 20 км, но не больше 75 км. 20 руб., если расстояние больше 75 км.

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

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

ВАРИАНТ № 8

Для шести рабочих, занятых на двух видах работ, введена почасовая система оплаты труда, зависящая от вида выполняемых работ. Основной заработок начисляется исходя из общего объема отработанного времени. Определены различные премиальные в зависимости от выполненного вида работы, начисляемые как процент от основного заработка за вычетом подоходного налога 13%. Составьте следующие таблицы.

· Почасовая оплата и подоходный налог.

· Отработанное время, включая виды работ.

· Начисление премиальных.

· Начисление денежных сумм и суммы к выплате.

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

ВАРИАНТ № 9

Имеются данные об объемах реализации одного и того же товара в пяти магазинах торговой фирмы «Кировский» за первый квартал года по определенной цене. Проведите ранжирование магазинов по объему выручки в каждом месяце и в целом за квартал. Определите:

· ежемесячную выручку каждого магазина.

· итоговую выручку каждого магазина за квартал.

· итоговую выручку всех магазинов за месяц и за квартал.

· минимальную и максимальную выручку каждого магазина за квартал.

· среднюю выручку каждого магазина за квартал.

· среднюю выручку всех магазинов за месяц и за квартал.

Постройте диаграмму, показывающую распределение средней выручки каждого магазина за квартал.

ВАРИАНТ № 10

Имеются данные об объемах реализации шести товаров по различным ценам в супермаркете «Купец» за первый квартал года. Проведите ранжирование товаров по объему полученной от их реализации выручки в каждом месяце и в целом за квартал. Определите:

· ежемесячную выручку от реализации каждого товара.

· минимальную и максимальную выручку от реализации всех товарам за квартал.

· процент месячной выручки от реализации всех товаров в общей суммарной выручке магазина за квартал.

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

· среднюю выручку магазина за месяц и за квартал.

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

ВАРИАНТ № 11

Сотрудникам кафедры университета зарплата начисляется с использованием электронных ведомостей. Типовые ведомости для профессорско- преподавательского состава создаются с учетом оклада по тарифной сетке, надбавки за должность (40% от оклада) и надбавки за степень. Типовые ведомости для учебно-вспомогательного персонала создаются с учетом оклада по тарифной сетке и индивидуальной надбавки.

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

Постройте диаграмму, показывающую соотношение заработной платы учебно-вспомогательного персонала.

ВАРИАНТ № 12

Имеются данные о планируемых в соответствии с бюджетом и фактических расходах администрации Кировского района за первое полугодие. Проверьте соответствие бюджета и расходов каждого месяца, генерируя в ячейках сообщения типа: «бюджет превышен на» или «имеется резерв в». В соседнем столбце укажите соответствующее превышение (резерв) бюджета. Рассчитайте следующие итоговые величины за полугодие:

· число месяцев, в течение которых расходы превышали бюджет.

· общую сумму превышения расходов над бюджетом (только по тем месяцам, когда это происходило).

· общую сумму резерва (только по тем месяцам, когда это происходило).

· суммарные расходы и суммарный бюджет за полугодие.

Отобразите графически динамику изменения расходов по месяцам.

ВАРИАНТ № 13

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

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

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

ВАРИАНТ № 14

Два цеха производят приборы трех видов – прибор «Альфа» артикулов А1 и А2, прибор «Бета» артикулов Б1 и Б2, прибор «Гамма» артикулов В1 и В2. Цех 1 выпускает приборы «Альфа» и «Бета». Цех 2 выпускает приборы «Бета» и «Гамма». Имеются данные о количестве приборов, произведенных каждым цехом за январь, февраль и март. Известны также издержки, приходящиеся на единицу продукции каждого артикула.

Определите

· общие издержки каждого цеха по приборам каждого артикула в январе, феврале и марте.

· количество и долю приборов артикула Б2, изготовленных вторым цехом за три месяца.

· количество приборов артикула Б2, изготовленных обоими цехами за три месяца.

· количество приборов «Бета», изготовленных обоими цехами за три месяца.

· долю издержек второго цеха по изготовлению приборов «Бета» в общих издержках по изготовлению приборов «Бета» обоими цехами за три месяца.

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

ВАРИАНТ 15

Два цеха производят приборы трех видов – прибор «Альфа» артикулов А1 и А2, прибор «Бета» артикулов Б1 и Б2, прибор «Гамма» артикулов В1 и В2. Цех 1 выпускает приборы «Альфа» и «Бета». Цех 2 выпускает приборы «Бета» и «Гамма». Имеются данные о количестве приборов, произведенных каждым цехом за январь, февраль и март. Известны также издержки, приходящиеся на единицу продукции каждого артикула.

Определите:

· общие издержки каждого цеха по приборам каждого артикула в январе, феврале и марте.

· средние издержки по изготовлению приборов «Бета» первым цехом за январь, февраль и март.

· средние издержки по изготовлению приборов «Бета» первым цехом за три месяца.

· издержки по изготовлению приборов артикула Б2 обоих цехов за три месяца.

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

средние издержки по изготовлению приборов «Бета» вторым цехом за три месяца.

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

ВАРИАНТ № 16

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

число цехов, превышающих план.

общую сумму превышения фактического выпуска по цехам, которые перевыполнили план.

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

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

ВАРИАНТ № 17

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

· ежемесячную выручку от реализации каждого товара.

· минимальную и максимальную выручку от реализации всех товарам за квартал.

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

· среднюю выручку магазина за месяц и за квартал.

Постройте диаграмму, показывающую объем реализации каждого товара.

ВАРИАНТ № 18

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

· число цехов, сэкономивших материал.

· общую сумму превышения фактического расхода по цехам.

· общую сумму сэкономленного расхода по цехам.

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

ВАРИАНТ № 19

Имеются данные об объемах реализации двух товаров с трех складов в течение шести текущих месяцев. В каждом из складов работает три работника. Все склады принадлежат одной фирме.

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

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

ВАРИАНТ № 20

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

· ежемесячную выручку от реализации каждого изделия.

· максимальную выручку от реализации всех изделий за квартал.

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

· среднюю выручку завода за месяц и за квартал.

Постройте диаграмму, показывающую объем выпуска каждого изделия.

ВАРИАНТ № 21

Имеются данные о планируемых в соответствии с бюджетом и фактических расходах администрации Центрального района за второе полугодие. Проверьте соответствие бюджета и расходов каждого месяца, генерируя в ячейках сообщения типа: «бюджет превышен на» или «имеется резерв в». В соседнем столбце укажите соответствующее превышение (резерв) бюджета. Рассчитайте следующие итоговые величины за полугодие:

· суммарные расходы и суммарный бюджет за полугодие.

· общую сумму превышения расходов над бюджетом (только по тем месяцам, когда это происходило).

· общую сумму резерва (только по тем месяцам, когда это происходило).

· долю резерва от суммарных расходов.

Отобразите графически динамику изменения расходов по месяцам.

ВАРИАНТ № 22

Два цеха производят приборы трех видов – прибор «Альфа» артикулов А1 и А2, прибор «Бета» артикулов Б1 и Б2, прибор «Гамма» артикулов В1 и В2. Цех 1 выпускает приборы «Альфа» и «Гамма». Цех 2 выпускает приборы «Бета» и «Альфа». Имеются данные о количестве приборов, произведенных каждым цехом за июнь, июль и август. Известны также издержки, приходящиеся на единицу продукции каждого артикула.

Определите:

· общие издержки каждого цеха по приборам каждого артикула в июне, июле и августе.

· средние издержки по изготовлению приборов «Альфа» первым цехом за июнь, июль и август.

· количество и долю приборов артикула А2, изготовленных первым цехом за три месяца.

· количество приборов артикула А2, изготовленных обоими цехами за три месяца.

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

ВАРИАНТ № 23

Два цеха производят приборы трех видов – прибор «Альфа» артикулов А1 и А2, прибор «Бета» артикулов Б1 и Б2, прибор «Гамма» артикулов В1 и В2. Цех 1 выпускает приборы «Альфа» и «Гамма». Цех 2 выпускает приборы «Бета» и «Альфа». Имеются данные о количестве приборов, произведенных каждым цехом за июнь, июль и август. Известны также издержки, приходящиеся на единицу продукции каждого артикула.

Определите:

· общие издержки каждого цеха по приборам каждого артикула в июне, июле и августе.

· общие издержки каждого цеха по приборам каждого артикула в июне, июле и августе.

· количество приборов «Альфа», изготовленных второго цеха за три месяца.

· количество приборов «Альфа», изготовленных обоими цехами за три месяца.

· долю издержек второго цеха по изготовлению приборов «Альфа» в общих издержках по изготовлению приборов «Альфа» обоими цехами за три месяца.

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

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

ВАРИАНТ № 24

Имеются данные об объемах реализации одного и того же товара в пяти магазинах торговой фирмы «Арбат» за первое полугодие по определенной цене. Проведите ранжирование магазинов по объему выручки в каждом месяце и в целом за квартал. Определите:

· ежемесячную выручку каждого магазина.

· итоговую выручку каждого магазина за полугодие.

· итоговую выручку всех магазинов за месяц и за полугодие.

· среднюю выручку каждого магазина за полугодие.

· долю выручки каждого магазина от выручки всех магазинов за полугодие.

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

ВАРИАНТ № 25

В супермаркете имеется три отдела с разными товарами: одежда, хозтовары, парфюмерия. Ежегодно производится уценку продукции. Если продукция находится дольше 9 месяцев, то она уценивается в 1,3 раза, если продукция находится дольше 11 месяцев, то она уценивается в 1,5 раза.

Составьте ведомость уценки товара по каждому отделу, которая должна содержать следующую информацию: наименование и дата поступления товара, срок хранения на текущий день, цена товара до уценки, цена товара после уценки.

Рассчитайте по каждому отделу количество залежавшегося товара (срок хранения более 11 месяцев).

Постройте для одного из складов диаграмму, показывающую количество залежавшегося товара по отношению ко всему товару.

Методические указания

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

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

Логическая функция ЕСЛИ().

Функция имеет следующую структуру: