2_Практика Функции БД
.pdfФУНКЦИИ БАЗ ДАННЫХ В ЭЛЕКТРОННЫХ ТАБЛИЦАХ
Цель: Расчет в электронных таблицах с помощью функций баз данных
DCOUNTA, DMIN, DMAX, DGET, DSUM, DAVERAGE.
Скопировать таблицу Медпрепараты в свой документ. Сохранить таблицу под именем Медпрепараты_Ваша фамилия.
Краткие сведения о функциях баз данных
Синтаксис функций баз данных
DFUNCTION(База данных; Поле базы данных; Критерий поиска)
где
База данных – диапазон ячеек, содержащих данные (таблица).
Поле базы данных – название поля (столбика) или номер столбика, к которому применяется функция баз данных.
Используйте 0 (ноль), если функция применяется ко всей таблице.
В случае использования названия поля, записывайте его в кавычках. Кроме того, название поля должно состоять не более, чем из одной строки.
Критерий поиска – диапазон ячеек, содержащий критерии поиска.
Примечание. DFUNCTION – любая функция из категории функций баз данных.
1. Функция DCOUNTA
DCOUNTA – функция, которая считает количество строк в таблице, удовлетворяющих заданному критерию (может включать в себя числовые и тексто-
вые данные).
DCOUNT – функция, которая считает количество строк в таблице, удовлетворяющих заданному критерию (может включать в себя только числовые дан-
ные)
Таким образом, функция DCOUNTA полностью включает в себя функцию DCOUNT, поэтому функцию DCOUNT далее не используем.
Вычислить:
1. У скольких препаратов цена выше 15 грн.? (Сравните с COUNTIF )
В ячейке L1 запишите вопрос: У скольких препаратов цена выше 15 грн. (DCOUNTA)?. Установите выравнивание в ячейке по левому краю.
Создайте таблицу критериев поиска. Для этого выделите и скопируйте диапазон заголовков столбцов таблицы (A1:H1) и вставьте их в ячейку L2. В столбце Цена (Q3) запишите критерий >15. (Такие же таблицы Вы создавали для расширенных фильтров)
Вызовите функцию DCOUNTA. Для этого установите курсор мыши в ячейку J1 и выполните команду Вставка/ Функция/ выберите категорию – База данных/ выберите функцию – DCOUNTA и нажмите Далее.
В появившемся окне Мастер функций введите соответствующие данные:
в окне База данных введите диапазон исследуемой таблицы А1:Н37 (или просто выделите эту таблицу вместе с заголовками столбцов);
в окне Поле базы данных введите номер столбца, к которому применяется функция DCOUNTA. В данном случае может быть взят любой столбец от 1 до 8, где 8 – количество столбцов в исследуемой таблице, а не только столбец с ценой (номер 6), так как функция считает количество строк, для которых выполняются критерии поиска в таблице критериев
L2:S3;
в окне Критерии поиска введите диапазон таблицы критериев поиска L2:S3 (или просто выделите таблицу критериев поиска вместе с заголовками столбцов). Нажмите ОК.
В ячейке J1 появится результат - 22, это и есть количество препаратов по цене выше 15 грн.
Установите курсор мыши в ячейку J2 и самостоятельно посчитайте количество препаратов по цене выше 15 грн. с помощью функции COUNTIF (СЧЕТЕСЛИ). Должно быть так
2.Посчитайте количество препаратов в форме таблеток в исследуемой таблице? (с помощью функций DCOUNTA и COUNTIF)
3.Сколько препаратов в форме таблеток по цене, выше 15 грн.?
Оформление и результат должны быть такими
Полученный результат покажите преподавателю.
2. Функции DMIN и DMAX
DMIN (DMAX) – функция, которая находит минимальное (максимальное) значение в таблице, соответствующее критериям поиска.
1. Найти минимальную цену анальгетиков. Результат поместите в ячейку J12. Вопрос запишите в ячейку L12, таблицу критериев начните с ячейки L13.
В окне Мастера функций в категории База данных выберите функцию DMIN и введите соответствующие данные
Замечание. При заполнении окна Поле базы данных нужно иметь в виду, что функция поиска минимума DMIN применяется к цене и, в отличие от предыдущих примеров с функцией DCOUNTA, никаких других столбцов в этом окне не может быть. Правда, вместо номера столбца (6) можно записать его название в кавычках (“ Цена”), и то только в том случае, если название столбца состоит из одной строчки.
Результат вычислений – минимальная цена анальгетиков равна 2 грн.
2.Найти минимальную цену антисептиков в форме таблеток. Результат поместите в ячейку J16. Вопрос запишите в ячейку L16, таблицу критериев начните с ячейки L17.
3.Найти минимальную цену среди антисептиков и ферментов. Результат поместите в ячейку J20. Вопрос запишите в ячейку L20, таблицу критериев начните с ячейки L21.
Результат должен выглядеть так
Напоминание. Условия, записанные в одной и той же строке, объединяются операцией И (AND), условия, записанные в разных строках, объединяются операцией ИЛИ (OR).
4. Найти минимальную цену антисептиков в форме капель, названия которых начинаются на букву Е. Чтобы выполнить последнее условие в столбце Наименование напишите так Е.* (на английской клавиатуре!!!).
!!! Этот пример не получится, если не будет учтено замечание, сделанное на лекции (см. слайд ниже). Проверьте!!!
Результат поместите в ячейку J26. Вопрос запишите в ячейку L25, таблицу критериев начните с ячейки L26.
Полученный результат покажите преподавателю.
3. Функция DGET
DGET - функция, определяющая содержание ячейки, соответствующее условиям поиска.
1. Найдите наименование анальгетика, имеющего минимальную цену
Вячейке J13 напишите Наименование.
Вкачестве таблицы критериев используйте имеющуюся таблицу из диапазона L13:S14, добавив в нее найденное ранее (в ячейке J12) минимальное значение цены. Для добавления минимальной цены в ячейку Q14, воспользуйтесь формулой =J12, или просто введите вручную 2.
Поставьте указатель мыши в ячейку J14 и вызовите функцию DGET (Вставка/
Функция/ категория Базы данных/ функция DGET/ Далее).
В появившемся окне введите соответствующие данные. Поскольку Вашей задачей является определение наименования анальгетика с минимальной ценой, то функция DGET применяется к столбцу Наименование, т.е. в окне Поле базы данных надо записать название столбца в кавычках, т.е. “ Наименование” или номер этого же столбца, т.е. 2.
Результат должен быть таким (выделите красным шрифтом)
В случае возникновения ошибок, смотрите лекционный слайд
2. Найдите наименование антисептика в форме таблеток, имеющего минимальную цену.
В ячейке J17 напишите Наименование. В ячейку Q18 введите минимальную цену (=J16). В ячейку J18 введите функцию DGET с соответствующими аргументами. В результате должны получить такой результат
3. Найдите наименование антисептика в форме капель, начинающегося на букву Е и имеющего минимальную цену.
В ячейке J27 напишите Наименование. В ячейку Q27 введите минимальную цену (=J26). В ячейку J28 введите функцию DGET с соответствующими аргументами. В результате должны получить такой ответ
Полученный результат покажите преподавателю.
4. Функция DSUM
DSUM - функция, суммирующая все ячейки области данных, содержание которых соответствует условиям поиска.
1. Найдите общую стоимость всех анальгетиков (сравните с результатом, полученным с помощью функции SUMIF в ячейке). Результат вычислений с помощью функции DSUM поместите в ячейку J30. Вопрос запишите в ячейку L29, таблицу критериев начните с ячейки L30.
В окне Мастера функций в категории База данных выберите функцию DSUM и введите соответствующие данные. Функция DSUM применяется к полю Стоимость, которое является восьмым по счету в исследуемой таблице.
Результат должен быть таким
2.Найдите общую стоимость всех анальгетиков с помощью функции SUMIF, результат поместите в ячейку J31.
3.Найдите общую стоимость всех анальгетиков в форме таблеток. Результат вычислений поместите в ячейку J34. Вопрос запишите в ячейку L33, таблицу критериев начните с ячейки L34.
4.Найдите общее количество упаковок на складе недорогих (цена меньше 15 грн.) анальгетиков в форме таблеток. Результат вычислений поместите в ячейку J38. Вопрос запишите в ячейку L37, таблицу критериев начните с ячейки L38. Функция DSUM применяется к полю На складе, которое является седьмым по счету в исследуемой таблице.
5.Найдите общую стоимость всех анальгетиков и антисептиков. Результат вычислений поместите в ячейку J42. Вопрос запишите в ячейку L41, таблицу критериев начните с ячейки L42.
Результаты должны выглядеть так
Полученный результат покажите преподавателю.
5. Функция DAVERAGE
DAVERAGE – функция, вычисляющая среднее значение ячеек, удовлетворяющих критериям поиска.
1. Найдите среднюю цену анальгетиков.
Результат вычислений с помощью функции DAVERAGE поместите в ячейку J47. Вопрос запишите в ячейку L46, таблицу критериев начните с ячейки L47.
В окне Мастера функций в категории База данных выберите функцию DAVERAGE и введите соответствующие данные. Функция DAVERAGE применяется к полю Цена, которое является шестым по счету в исследуемой таблице.
Результат должен быть такой