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

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

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

1) определить максимальную сумму в январе;

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

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

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

Критерий отбора

База данных

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

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

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

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

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

Область вывода данных

Критерий отбора

База данных

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

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

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

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

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

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

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

Вопросы для самопроверки

1. Дать определение понятию «список».

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

3. Что является полем в базе данных MS Excel?

4. Что является записью в базе данных MS Excel?

5. Какие области выделяют на рабочем листе MS Excel?

6. Перечислите средства для работы со списком MS Excel.

7. Основные правила ведения списка MS Excel.

8. Дать определение понятию сортировка списка MS Excel.

9. Какие виды сортировок бывают?

10. Как отсортировать список MS Excel по одному полю?

11. Как отсортировать список MS Excel по нескольким полям?

12. Что такое пользовательский порядок сортировки списка MS Excel?

13. Как отсортировать список MS Excel с помощью пользовательского порядка сортировки?

14. Что такое фильтрация списка MS Excel?

15. Объясните способы фильтрации списка MS Excel.

16. В каких случаях используется Автофильтр для базы данных MS Excel?

17. Как установить Автофильтр в базе данных MS Excel?

18. Как отфильтровать данные в списке MS Excel по одному из значений, встречающихся в столбце?

19. Как отфильтровать информацию в базе данных MS Excel по двум или более значениям, встречающимся в столбце?

20. Что такое пользовательский автофильтр в базе данных MS Excel?

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

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

23. Что включает в себя удаление автофильтра из базы данных?

24. Как отменить фильтрацию столбца в списке MS Excel?

25. Как отменить фильтрацию всего списка MS Excel?

26. Как удалить автофильтр из списка MS Excel?

27. В каких случаях используется Расширенный фильтр для списка MS Excel?

28. Какой командой выполняется фильтрация базы данных MS Excel с помощью расширенного фильтра?

29. Дать определение понятию диапазон условий.

30. Что включает в себя диапазон условий?

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

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

33. Назовите правила ввода условий отбора информации из базы данных MS Excel.

34. Назовите правила фильтрации списка MS Excel с помощью расширенного фильтра.

35. Что представляет собой анализ данных списка MS Excel?

36. Что относится к средствам анализа данных списка MS Excel?

37. Что такое промежуточные итоги в базе данных MS Excel?

38. Какой порядок подведения итогов в базе данных MS Excel.

39. Дать определение понятию сводная таблица для базы данных MS Excel.

40. Для чего используются сводные таблицы?

41. Какой командой создается сводная таблица?

42. Что определяется на первом шаге мастера сводных таблиц и диаграмм?

43. Что определяется на втором шаге построения сводной таблицы?

44. Как формируется макет сводной таблицы для базы данных MS Excel?

45. Что такое страница в макете сводной таблицы?

46. Что такое строка в макете сводной таблицы?

47. Что такое данные в макете сводной таблицы?

48. Как изменить функцию для поля Данные в макете сводной таблицы?

49. Что такое столбец в макете сводной таблицы?

50. Что указывается на третьем шаге мастера сводных таблиц и диаграмм?

51. Как добавляют поля в созданную сводную таблицу для базы данных MS Excel?

52. Как группируются данные в созданной сводной таблице для базы данных MS Excel?

53. Функции какой категории используют для анализа данных из списка MS Excel?

54. Какие аргументы использует каждая из функций работы с базой данных MS Excel?

55. Дать определение аргументу База_данных.

56. Дать определение аргументу Поле.

57. Дать определение аргументу Критерий.

58. Правила использования функций работы с базой данных MS Excel.

59. Какая функция позволяет извлечь отдельное значение из столбца списка, которое удовлетворяет заданным условиям?

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

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

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