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

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

Таблица (база данных) для удобства использования функций должна быть соответствующим образом оформлена. Пример оформления базы данных приведен на рис. 7.19.

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

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

• А – база данных – интервал ячеек, формирующих список или базу данных. База данных представляет собой список связанных данных, в котором строки данных являются записями, а столбцы – полями. Верхняя строка списка содержит названия всех столбцов. На рис. 7.19 база данных – интервал ячеек А5:H11;
• В – поле – столбец, используемый функцией. Аргумент "поле" может быть задан как ячейка или как текст с названием столбца в двойных кавычках. На рис. 7.19 поля – ячейки А1, В1, С1, D1 и т. д. или названия столбцов: "Товар", "Партия", "Цена", "Затраты" и т. д.;
• С – критерий – интервал ячеек, который содержит задаваемые условия. Любой интервал, который содержит по крайней мере одно название столбца и по крайней мере одну ячейку под названием столбца с условием, может быть использован как аргумент "критерий". В таблице на рис. 7.19 для критериев выделен интервал ячеек А1:H3.

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

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

Использование функций для работы с базами данных можно рассмотреть на примере суммирования. Для суммирования выбранных данных из базы данных используют функцию БДСУММ.

Например, в базе данных (см. рис. 7.19) необходимо суммировать затраты только на товары, объем партии которых меньше 40. При создании формулы (рис. 7.20) следует указать ячейки базы данных (А5:Н11), ячейку поля, по которому суммируются данные (D5) и ячейки критерия (В1:В2).

увеличить изображение
Рис. 7.20. Суммирование с одним условием по одному столбцу

Можно использовать более сложные критерии. Например, в базе данных на рис. 7.21 суммированы затраты только на товар "Марс", объем партии которого меньше 40.

увеличить изображение
Рис. 7.21. Суммирование с двумя условиями по двум столбцам

Или, например, в базе данных на рис. 7.22 суммированы затраты только на товары, объем партии которых меньше 40, но больше 30.

увеличить изображение
Рис. 7.22. Суммирование с двумя условиями по одному столбцу

Аналогично функции БДСУММ можно использовать и другие функции для работы с базами данных.

Упражнение 7

Запустите Microsoft Excel 2007.

Откройте файл exercise_07.xlsx.

Задание 1

1. Перейдите к листу Лист 1.
2. В ячейке В11 рассчитайте сумму ячеек В2:Е6.
3. Перейдите к листу Лист 2.
4. В ячейке В19 рассчитайте сумму ячеек в диапазоне В2:В17, значения в которых превышают 30.
5. Перейдите к листу Лист 3.
6. В ячейке В19 рассчитайте сумму ячеек в диапазоне В2:В17 для товара Мечта.
7. Перейдите к листу Лист 4.
8. В ячейке С2 рассчитайте цену товара, указанную в ячейке В2, округленно до двух знаков после запятой. Скопируйте формулу на ячейки С3:С4.
9. Перейдите к листу Лист 5.
10. В ячейке С2 рассчитайте цену товара, указанную в ячейке В2, округленно в большую сторону до двух знаков после запятой. Скопируйте формулу на ячейки С3:С4. В ячейке D2 рассчитайте цену товара, указанную в ячейке В2, округленно в меньшую сторону до двух знаков после запятой. Скопируйте формулу на ячейки D3:D4.
11. Перейдите к листу Лист 6.
12. В ячейке С2 рассчитайте температуру, указанную в ячейке В2, округленно до целого числа. Скопируйте формулу на ячейки С3:С4.
13. Перейдите к листу Лист 7.
14. В ячейке С2 рассчитайте температуру, указанную в ячейке В2, округленно до с точностью 0,2. Скопируйте формулу на ячейки С3:С4.
15. Перейдите к листу Лист 8.
16. В ячейке В3 рассчитайте синус угла, указанного в ячейке А3. Скопируйте формулу на ячейки В4:В9.
17. Перейдите к листу Лист 9.
18. С использованием функций в ячейке В1 отобразите абсолютное значение ячейки А1. С использованием функций в ячейке В2 отобразите в радианах угол, указанный в ячейке А2. С использованием функций в ячейке В3 преобразуйте в римское число, указанное в ячейке А3.

Задание 2

1. Перейдите к листу Лист 10.
2. В ячейке Е1 с использованием функций рассчитайте средний процент брака. В ячейке Е2 с использованием функций рассчитайте средний процент брака без учета 20 % самых больших и самых малых значений. В ячейке Е3 с использованием функций найдите наиболее часто встречающийся процент брака. В ячейке Е4 с использованием функций найдите максимальный процент брака. В ячейке Е5 с использованием функций найдите минимальный процент брака.
3. Перейдите к листу Лист 11.
4. В ячейке Е1 с использованием функций определите общее количество партий товара. В ячейке Е2 с использованием функций определите количество отгруженных партий товара (указан объем отгрузки). В ячейке Е3 с использованием функций определите количество партий товара, для которых нет данных. В ячейке Е4 использованием функций определите количество партий товаров объемом более 50. В ячейке Е5 использованием функций определите количество партий товара Мечта.

Задание 3

1. Перейдите к листу Лист 12.
2. В ячейке B28 с использованием функций для работы с базами данных рассчитайте сумму затрат на товары, у которых объем поставки больше 50.
3. Перейдите к листу Лист 13.
4. В ячейке B28 с использованием функций для работы с базами данных рассчитайте сумму затрат на партии товара Мечта с объемом поставки больше 50.

Сохраните файл под именем Lesson_07.

Закройте Microsoft Excel 2007.

Не нашли, что искали? Воспользуйтесь поиском по сайту: