3.5. Функции для работы с базами данных
3.5.1. Общие сведения
Библиотека Excel содержит тринадцать встроенных функций, позволяющих получить информацию из БД или произвести над ней необходимые вычисления. Все они находятся в категории Работа с базой данных.
Назначение функций очевидно из их названий. Например:
БДСУММ(База_данных; Поле; Критерий поиска);
БСЧЕТ(База_данных; Поле; Критерий поиска);
ДМИН(База_данных; Поле; Критерий поиска);
ДМАКС(База_данных; Поле; Критерий поиска);
ДСРЗНАЧ(База_данных; Поле; Критерий поиска).
Все функции имеют один и тот же формат:
– первый параметр представляет собой ссылку на диапазон ячеек, в котором расположены данные;
– второй параметр - ссылку на адрес, имя или содержимое ячейки с названием столбца в списке, к данным которого применяется данная функция;
– третий параметр представляет собой ссылку на критерии поиска.
Расчетные формулы, содержащие функции баз данных необходимо вводить в ячейки на той области рабочего листа, которая не будет в дальнейшем мешать дополнению и расширению списка.
Для удобства работы с функциями баз данных следует заранее присвоить имена диапазонам ячеек, содержащим данные списка (включая заглавную строку) и область критериев.
Порядок присвоения имен:
-
С помощью мыши выделить все ячейки, содержащие базу данных.
-
В строке формул в ячейку адреса текущей ячейки ввести имя базы данных (рис. 3.2):
Рис. 3.2. Порядок присвоения имени БД
Пример 1.
Имеется база данных «Кадры». Рассчитать среднюю заработную плату работников отдела снабжения.
Для решения в произвольном месте рабочего листа записывается условие отбора записей для расчетов:
| M | N | O |
9 |
|
|
|
10 |
| Отдел |
|
11 |
| Снабжения |
|
12 |
|
|
|
13 |
| 12181,81 |
|
А в ячейку N13 ввести формулу:
=ДСРЗНАЧ(Данные;G5;N10:N11),
где G5 – адрес заголовка «Оклад»;
N10:N11 – адрес критерия фильтрации.
Пример 2.
Имеется база данных «Кадры». Определить количество пенсионеров, работающих в организации.
При решении задач, связанных возрастом, рекомендуется создать поле «Возраст». Для этого в ячейку L5 ввести название поля, т.е. – «Возраст», а в ячейку L6 ввести формулу: =2009-H6, которая затем копируется на весь столбец L.
Непосредственно для решения в свободном месте листа вводится условие фильтрации:
| M | N | O | P |
15 |
|
|
|
|
16 |
| Пол | Возраст |
|
17 |
| м | >=60 |
|
18 |
| ж | >=55 |
|
19 |
|
|
|
|
20 |
| 18 |
|
|
А в ячейку N20 ввести формулу:
=БСЧЁТ(Данные;;N16:O18)
Примечание. Для функции БСЧЕТ в качестве заголовка поля можно указывать любое поле или даже просто не вводить его.
-
2. Варианты заданий
Дана база данных «Кадры». С функций работы с базами данных рассчитать:
1 вариант
а) Общее количество мужчин в плановом и производственном отделах.
б) Количество работников планового отдела, проживающих на улице Хевешская и по проспекту Мира.
2 вариант
а) Среднюю заработную плату женщин не пенсионеров.
б) Средний возраст мужчин с именами Алексей и Андрей.
3 вариант
а) Средний возраст женщин с именами Ольга и Мария.
б) Количество детей у мужчин в плановом и производственных отделах.
4 вариант
а) Среднюю заработную плату у пенсионеров мужчин.
б) Среднее количество детей в организации, приходящееся на одного работника.
5 вариант
а) Суммарную заработную плату у мужчин.
б) Максимальное количество детей у мужчин с именами Олег и Сергей.
6 вариант
а) Среднее количество детей у женщин, проживающих на ул. Водопроводная.
б) Максимальную заработную плату у мужчин в отделе сбыта.
7 вариант
а) Общее количество детей у мужчин, проживающих на ул. Горького.
б) Минимальную заработную плату у женщин в производственном отделе.
8 вариант
а) Среднюю заработную плату у женщин с двумя детьми.
б) Средний возраст у мужчин в производственном отделе.
9 вариант
а) Максимальную заработную плату у мужчин в отделе сбыта.
б) Минимальный возраст у женщин в плановом отделе.
10 вариант
а) Минимальную заработную плату мужчин без детей.
б) Самого молодого мужчину на ул. Лебедева.
11 вариант
а) Общую сумму заработной платы в плановом отделе.
б) Самую старшую женщину в отделе сбыта.
12 вариант
а) Общий фонд заработной платы для работников с одним ребенком.
б) Самого старого мужчину на ул. Володарского.
13 вариант
а) Суммарную заработную плату у мужчин пенсионеров в производственном отделе.
б) Количество мужчин, у которых нет детей.
14 вариант
а) Максимальную заработную плату у женщин пенсионеров.
б) Средний возраст женщин на ул. Яковлева.
15 вариант
а) Среднюю заработную плату у мужчин без детей.
б) Количество работников с двумя детьми.
- Содержание
- VII. Статистические методы 167
- Введение
- I. Общие методы работы
- 1.1. Работа с формулами
- 1.1.1. Общие сведения
- Вычисления сложных выражений
- 1.1.2. Задание
- 1.2. Математические функции
- 1.2.1. Общие сведения
- 1.2.2. Пример
- 1.2.3. Варианты заданий
- 1.3. Вычисления с условиями
- 1.3.1. Общие сведения
- 1.4. Работа со справочниками
- 1.4.1. Общие сведения
- 1.4.2. Варианты заданий
- «Разносортица»
- «Маршрутное такси»
- «Гостиница»
- «Автовокзал»
- «Книжное издательство»
- «Продукты»
- «Коттеджи»
- «Гастроли»
- «Туристическое агентство»
- «Комплектующие»
- «Авиаперевозки»
- «Винный погребок»
- «Сберкасса»
- «Мебельная фабрика»
- 16. «Сага о таре»
- 1.5. Работа с диаграммами
- 1.5.1. Общие сведения
- 1.5.2. Задание на построение диаграммы
- 1. Изменение настроек параметров диаграммы:
- 3. Форматирование рядов данных и их элементов:
- 4. Форматирование осей диаграммы:
- 5. Форматирование сетки, стен и основания:
- 6. Форматирование легенды:
- 1.5.3. Варианты заданий
- 1.6. Собственные функции
- 1.6.1. Общие сведения
- 1.6.2. Общие сведения о Visual Basic for Excel
- Математические операции
- Математические функции
- 1.6.3. Варианты заданий
- II. Численные методы
- 2.1. Решение алгебраических уравнений Средство «Подбор параметра»
- 2.1.1. Общие сведения
- 2.1.2. Пример
- 2.1.3. Варианты заданий
- 2.2. Решение систем уравнений
- 2.2.1. Общие сведения
- 2.2.2. Реализация расчетов в Excel
- 2.2.3. Варианты заданий
- 2.3. Задачи оптимизации
- 2.3.1. Общие сведения
- 2.3.2. Пример
- 2.3.3. Варианты заданий
- III. Базы данных в ms Excel
- Каждое из последующих заданий необходимо выполнять на отдельном листе!!!
- Сортировка
- 3.1.1. Общие сведения
- 3.1.2. Варианты заданий
- Фильтрация данных
- 3.2.1. Общие сведения
- Варианты заданий
- Средство «Итоги»
- 3.3.1. Общие сведения
- Сводные таблицы
- 3.5. Функции для работы с базами данных
- 3.6. Консолидация данных
- 3.6.2. Варианты заданий
- 3.7. Контрольная работа по теме «Базы данных в Excel»
- 3.7.1. Указания
- 2. Скопируйте указанный файл в свою рабочую папку и вся дальнейшая работа должна производиться только с этой копией.
- 3.7.2. Варианты заданий
- Вариант 12
- Вариант 13
- Вариант 14
- Вариант 15
- IV. Макросы в ms Excel
- 4.1. Макросы для автоматизации работ
- 4.1.1. Пример
- 4.2. Вычислительные макросы
- 4.2.1. Пример 1. Расчет точки безубыточности
- 4.2.2. Пример 2. Моделирование процесса налогообложения [8]
- 4.3. Использование макросов для создания интерфейса
- V. Технология создания информационной системы средствами ms Excel
- 5.1. Постановка задачи
- 5.2. Требования к системе
- 5.3. Общая архитектура ис
- 5.3.1. Проектирование общей архитектуры
- 5.3.2. Создание общей архитектуры
- 5.3.2.1. Создание объектов ис
- 5.3.2.2. Организация переходов между объектами
- 5.3.2.3. Этапы создания интерфейса
- 5.4. Организация работы с базой данных
- 5.4.1. Заполнение таблиц модельными данными
- 5.4.2. Работа с данными
- 5.4.3. Сортировка
- 5.4.4. Поиск данных
- 5.4.5. Отчеты
- 5.4.5.1. Использование функций
- 5.4.5.2. Использование сводных таблиц
- 5.4.5.3. Использование элементов управления
- 5.4.5.5. Использование встроенных функций
- Функция должна быть в англоязычном варианте.
- 5.4.5.6. Варианты заданий
- 5.4.6. Расчет заработной платы
- 5.4.6.1. Постановка задачи
- 5.4.6.2. Интерфейс расчета заработной платы
- 5.4.6.3. Реализация расчетов
- VI. Экономические расчеты
- 6.1. Задачи на проценты
- 6.1.1. Общие сведения
- 6.1.2. Пример.
- 6.1.3. Варианты заданий
- 6.2. Финансовые функции
- 6.2.1. Общие сведения
- Бс(Ставка, Кпер, Плт, Пс, Тип).
- 6.2.3. Варианты заданий
- 6.3. Анализ межотраслевого баланса (модель Леонтьева)
- Основные понятия
- Математическая модель межотраслевого баланса
- 6.3.4. Варианты заданий
- 6.4. Задача об эквивалентности ставок [1]
- 6.4.1. Основные формулы
- 6.4.2. Постановка задачи
- 6.4.3. Варианты заданий
- 6.5. Методы анализа проектов (использование средства «Подбор параметра»)
- 6.5.1. Термины и определения
- 6.5.2. Примеры
- Варианты заданий
- 6.6. Выбор оптимального портфеля инвестиций
- 6.6.1. Основные определения
- 6.6.2. Пример
- 6.6.3. Варианты заданий
- 6.7. Вычисление налогов
- 6.7.1. Предварительные замечания
- 6.7.2. Пример.
- 6.7.3. Варианты заданий
- 6.8. Моделирование динамических процессов
- 6.8.1. Общие сведения
- 6.8.2. Порядок выполнения работы
- 6.8.3. Пример
- Результаты должны отражать основные закономерности процесса
- 6.8.4. Варианты заданий
- Производство в условиях постоянного спроса
- Конкуренция
- Сезонное производство
- Рыночные отношения
- Взаимопоставки
- Цены в условиях ограниченного объема выпуска
- Северный завоз
- Два пароходства
- Последовательные перевозки
- Антимонопольная система
- Конъюнктура
- Количество информации в Интернет
- Валютная интервенция
- Реклама
- VII. Статистические методы
- 7.1. Определение характеристик случайных величин
- 7.1.1. Содержание работы
- 7.1.2. Варианты заданий
- 7.2. Дисперсионный анализ
- 7.2.1. Общие сведения
- 7.2.1. Пример
- 7.2.3. Методы, применяемые после дисперсионного анализа
- 7.2.4. Варианты заданий
- 7.3. Регрессионный анализ
- 7.3.1.Общие сведения
- 7.3.2. Порядок выполнения работы
- 7.3.3. Проверка уравнения регрессии на адекватность
- 7.3.4. Использование уравнения для прогноза
- 7.4. Кластерный анализ
- 7.4.1. Общие положения.
- 7.4.2. Примеры
- 7.4.3. Формализация процесса кластеризации
- 7.4.4. Порядок выполнения работы
- 7.4.5. Задания
- 7.5. Анализ временных рядов
- 7.5.1. Общие сведения
- 7.5.2. Пример
- Литература
- Приложения
- Технология генерации модельных данных
- Приложение 2 Районы и города Чувашии в цифрах [9]
- Тексты макросов Текст макроса для кластерного анализа
- Текст макроса для решения систем дифференциальных уравнений
- Текст макроса для генерации временного ряда
- Приложение 4 Транспорт и связь
- Статистические данные по регионам рф [5]