Elettracompany.com

Компьютерный справочник
39 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Сумма строк в access

IT IS MY LIFE…

Уверен, кому-то приведенная здесь задача покажется простой, ну а для тех, кто как я пользуется MS Access от случая к случаю, может и пригодиться.

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

В первом столбце буквы (letter), во втором — цифры (number). Теперь разберемся есть ли случаи, когда одной и той же букве соответствует два и больше разных значений цифр.

Затем необходимо перейти в режим SQL-запросов и после SELECT указать оператор DISTINCT должно получиться вот так:

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

В контекстном меню выбираем Итоги :

Делаем группировку по первому столбцу, подсчет ( Count ) по второму и для второго же столбца в качестве условия отбора ставим «больше единицы»:

Запускаем запрос и получаем решение задачи:

Т.е. в нашем примере для BBB и CCC есть два различных значения во втором столбце. Если бы у нас была потребность определить разные значения какого-либо параметра не в рамках значений какого-то одного столбца, а в рамках группы значений разных столбцов, то, соответственно, группировку следовало бы указать для всех этих столбцов, а Count, как и в данном примере, только для подсчитываемого параметра.

В общем-то на этом все. Можно еще вывести информацию о том, какие именно разные значения второго столбца соответствуют BBB и CCC . Объединяем последний запрос с исходной таблицей:

Подсчет данных при помощи запроса

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

Выберите нужное действие

Способы подсчета данных

Подсчитать количество элементов в поле (столбце значений) можно с помощью функции Число. Функция Число принадлежит к ряду функций, называемых агрегатными. Агрегатные функции выполняют вычисления со столбцами данных и возвращают единственное значение. Кроме функции Число, в Access есть следующие агрегатные функции:

Сумма для суммирования столбцов чисел;

Среднее для вычисления среднего значения в столбце чисел;

Максимум для нахождения наибольшего значения в поле;

Минимум для нахождения наименьшего значения в поле;

Стандартное отклонение для оценки разброса значений относительно среднего значения;

Дисперсия для вычисления статистической дисперсии всех значений в столбце.

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

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

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

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

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

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

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

Общие сведения о типах данных см. в статье Изменение типа данных для поля.

Подсчет данных с помощью строки итогов

Чтобы добавить в запрос строку итогов, откройте его в режиме таблицы, добавьте строку, а затем выберите функцию Число или другую агрегатную функцию, например Сумма, Минимум, Максимум или Среднее. В этом разделе объясняется, как создать простой запрос на выборку и добавить строку итогов.

Создание простого запроса на выборку

На вкладке Создать в группе Другое нажмите кнопку Конструктор запросов.

В диалоговом окне Добавление таблицы дважды щелкните таблицу или таблицы, которые вы хотите использовать в запросе, а затем нажмите кнопку Закрыть.

Читать еще:  База данных договоров access скачать

Выбранные таблицы отображаются в виде окон в верхней части конструктора запросов. На рисунке показана типичная таблица в конструкторе запросов.

Дважды щелкните поля таблицы, которые вы хотите использовать в запросе.

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

Каждое поле отображается в столбце в бланке запроса.

На вкладке Конструктор в группе Результаты нажмите кнопку Выполнить.

Результаты запроса отображаются в режиме таблицы.

При необходимости переключитесь в Конструктор и скорректируйте запрос. Для этого щелкните правой кнопкой мыши вкладку документа для запроса и выберите команду Конструктор. После этого можно изменить запрос, добавив или удалив поля таблицы. Чтобы удалить поле, выберите столбец в бланке запроса и нажмите клавишу DELETE.

При необходимости вы можете сохранить запрос.

Добавление строки итогов

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

Если используется база данных в формате MDB, созданная в более ранней версии Access, на вкладке Главная в группе Режимы щелкните стрелку под кнопкой Режим и выберите значение Режим таблицы.

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

На вкладке Главная в группе Записи нажмите кнопку Итоги.

Под последней строкой данных в таблице появится новая строка Итог.

В строке Итог щелкните поле, по которому вы хотите выполнить подсчет, и выберите в списке функцию Count.

Скрытие строки итогов

На вкладке Главная в группе Записи нажмите кнопку Итоги.

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

Подсчет данных с помощью итогового запроса

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

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

Подсчет всех записей в запросе

На вкладке Создать в группе Другое нажмите кнопку Конструктор запросов.

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

Таблица появится в окне в верхней части конструктора запросов.

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

На вкладке Конструктор в группе Показать или скрыть нажмите кнопку Итоги.

В бланке появится строка Итог, а в строке для каждого поля запроса будет указано Группировка.

В строке Итог щелкните поле, по которому вы хотите выполнить подсчет, и выберите в списке функцию Count.

На вкладке Конструктор в группе Результаты нажмите кнопку Выполнить.

Результаты запроса отображаются в режиме таблицы.

При необходимости вы можете сохранить запрос.

Подсчет записей в группе или категории

На вкладке Создать в группе Другое нажмите кнопку Конструктор запросов.

В диалоговом окне Добавление таблицы дважды щелкните таблицу или таблицы, которые вы хотите использовать в запросе, а затем нажмите кнопку Закрыть.

Таблица (или таблицы) появится в окне в верхней части конструктора запросов.

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

На вкладке Конструктор в группе Показать или скрыть нажмите кнопку Итоги.

В бланке появится строка Итог, а в строке для каждого поля запроса будет указано Группировка.

В строке Итог щелкните поле, по которому вы хотите выполнить подсчет, и выберите в списке функцию Count.

На вкладке Конструктор в группе Результаты нажмите кнопку Выполнить.

Результаты запроса отображаются в режиме таблицы.

При необходимости вы можете сохранить запрос.

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

В следующей таблице перечислены и отписаны агрегатные функции Access, которые можно использовать в строке итогов и в запросах. Помните, что в Access предусмотрено больше агрегатных функций для запросов, чем для строки итогов. Кроме того, при работе с проектом Access (внешней базой данных Access, которая подключается к базе данных Microsoft SQL Server) можно использовать расширенный набор агрегатных функций, предоставляемый SQL Server. Дополнительные сведения о них см. в электронной документации Microsoft SQL Server.

Практическая работа. Создание перекрестных запросов в MS Access

Как организовать дистанционное обучение во время карантина?

Помогает проект «Инфоурок»

Практическая работа №5

Тема: «Создание перекрестных запросов»

для студентов 2 курса специальности 10.02.03

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

Цели работы: научиться создавать перекрестные запросы на выборку данных с помощью мастера и конструктора запросов.

Перекрестные запросы — это запросы, в которых происходит статистическая обработка данных, результаты которой выводятся в виде таблицы, очень похожей на сводную таблицу Excel. Перекрестные запросы обладают следующими достоинствами:

возможностью обработки значительного объема данных и вывода их в формате, который очень хорошо подходит для автоматического создания графиков и диаграмм;

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

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

Читать еще:  Ключ access 2020

Создать запрос с помощью мастера форм: Создание/Мастер запросов

Создать с помощью конструктора: Создание/ Конструктор запросов

Изменить запрос с помощью конструктора: Режим/Конструктор

Задание 1. Создать запрос Выручка по месяцам

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

1. Создать запрос с помощью мастера запросов: Создание/Другие/Мастер запросов/Перекрестный запрос

2. Для создания перекрестного запроса использовать данные таблицы Заказы

3. Выбрать поля, значения которых использовать в качестве заголовков строкСотрудник.

4. Выбрать поля, значения которых использовать в качестве заголовков столбцовДата заказа .

5. Выбрать интервал, с которым необходимо сгруппировать столбец данных типа даты и времени — Месяц

6. Выбрать, какие исчисления необходимо провести для каждой ячейки на пересечении строк и столбцов. Выбрать поле Сумма и применить функцию C УММА.

7. Задать имя перекрестного запроса Выручка по месяцам .

Задание 2. Создать перекрестный запрос «Количество заказов по сотрудникам»

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

1. Создать запрос с помощью конструктора: Создание/Другие/Конструктор запросов

2. Для создания запроса использоватьтаблицы и поля:

таблица Заказы (поля ФИО клиента и Сумма )

таблица Сотрудники (поле ФИО сотрудника ).

3. Чтобы подключить групповые функции , нажать на кнопку

4. Для поля Сумма выбирать из выпадающего списка функцию Count – считать количество записей.

5. Чтобы создать перекрестный запрос, нажать на кнопку .

6. Провести настройку перекрестной таблицы. Использовать:

Поле ФИО сотрудника для заголовков столбцов ,

Поле ФИО клиента для заголовков строк ,

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

5. Для создания запроса нажимаем кнопку Выполнить!

6. Сохраните запрос под именем Количество заказов по сотрудникам .

Итоговый запрос из базы данных Access

Запросы позволяют не только выбирать записи из таблиц Access, но и вычислять различные статистические параметры. Например, можно подсчитать суммарное количество контактов и вывести даты первого и последнего контакта с каждым из людей, включенных в таблицу Контакты. Чтобы построить такой запрос в режиме конструктора, выполните следующие действия.

1. В окне базы данных щелкните на кнопке Запросы.

2. Дважды щелкните на значке Создание запроса в режиме конструктора.

3. В открывшемся окне диалога (рис. 17.6) выделите строку Контакты.

4. Щелчком на кнопке Добавить добавьте выбранную таблицу в верхнюю область конструктора запроса.

5. Выделите пункт Список и снова щелкните на кнопке Добавить.

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

7. Щелкните на кнопке Групповые операции панели инструментов. В бланке запроса появится дополнительная строка Групповая операция, позволяющая выполнять статистические операции со значениями конкретных полей.

Рис. 17.6 . Добавление таблицы

8. Перетащите поле Фамилия в ячейку Поле первого столбца конструктора.

9. В ту же ячейку второго столбца перетащите поле Имя таблицы Контакты.

10. В третий, четвертый и пятый столбцы бланка запроса перетащите поле Дата таблицы Список (рис. 17.7).

Рис. 17.7. Запрос с групповыми операциями

11. В раскрывающемся списке ячейки Групповая операция третьего столб ца бланка запроса выберите пункт Min.

12. В той же ячейке четвертого столбца выберите пункт Мах.

13. В пятом столбце задайте групповую операцию Count. Групповые операции построенного запроса обработают все записи таблицы Список, соответствующие конкретному человеку из таблицы Контакты, и вместо самих данных таблицы Список выведут в соответствующее поле результата запроса только значение величины, вычисляемой по определенной формуле. Доступные групповые операции перечислены в табл. 17.1.

ТАБЛИЦА 17.1 . Групповые операции

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

14. Щелкните на кнопке Вид, чтобы выполнить запрос. Появится таблица с пятью столбцами. Два первых столбца содержат фамилии и имена людей. По ним выполняется группировка, то есть расчет значений остальных полей запроса выполняется для записей таблицы Список, сопоставляемых с одним человеком. Как уже говорилось выше, соответствие контакта таблицы Список и человека из таблицы Контакты определяется полями Код_Контакты, с помощью которых осуществляется связь этих двух таблиц. Третий и четвертый столбцы запроса выводят соответственно дату первого (функция Min) и последнего (функция Мах) контакта с данным человеком. Пятый столбец содержит количество записей в таблице Список (функция Count), соответствующих. данному человеку, то есть число контактов с ним. Единственный недостаток построенного запроса — это непонятные имена столбцов. Давайте скорректируем их.

15. Щелчком на кнопке Вид вернитесь в конструктор запроса.

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

17. В ячейке Поле четвертого столбца введите Дата последнего контакта: Дата.

18. В первой строке пятого столбца бланка запроса введите Число контактов: Дата.

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

19. Снова щелкните на кнопке Вид.

20. Закройте запрос.

21. Для сохранения изменений структуры щелкните на кнопке Да.

Читать еще:  Анимированные графики в powerpoint

22. В окне диалога Сохранение введите имя Итоговый запрос и щелкните на кнопке ОК.

Суммирование по множеству условий функцией БДСУММ (DSUM)

Для выборочного подсчета по нескольким условиям в больших таблицах можно использовать несколько способов: фильтры, сводные таблицы, функции СУММЕСЛИ и СУММЕСЛИМН и т.д.

Еще одним, относительно экзотическим, но весма мощным инструментом является функция БДСУММ (DSUM) из категории Работа с базой данных (Database) . При внешней простоте, она позволяет гибко фильтровать списки по нескольким сложным и связанным между собой условиям и подсчитывает сумму найденных записей по заданному столбцу. Синтаксис функции таков:

=БДСУММ( Исходные_данные ; Столбец_результата ; Диапазон_условий )

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

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

Чтобы удобнее было ссылаться эту таблицу в будущем, конвертируем ее в «умную» командой Форматировать как таблицу на вкладке Главная (Home — Format as Table) или сочетанием клавиш Ctrl + T . На появившейся затем вкладке Конструктор (Design) зададим ей имя — например БазаДанных.

Простая сумма по одному условию

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

Обратите внимание на следующие моменты:

  • Не достаточно просто написать имя умной таблицы БазаДанных в первом аргументе, т.к. в этом случае ссылка не включает шапку, а для функции БДСУММ она необходима. Поэтому к имени добавляется тег [#Все] или, в английском варианте [#All].
  • Столбец, по которому нужно просуммировать данные можно задать либо названием («Стоимость»), либо номером (было бы 5).
  • Название столбца в желтом диапазоне условий должно один-в-один совпадать с названием в исходной таблице.
  • Функция БДСУММ не различает регистр символов (добрый = ДОБРЫЙ = Добрый = ДоБрЫй и т.д.)
  • Чтобы критерий в желтой ячейке А2 Excel не начал понимать как формулу (т.к. формулы обычно начинаются со знака равно) можно использовать текстовый формат или просто начать ввод в ячейку с апострофа:


Приблизительный и точный текстовый поиск

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

  1. Если нужен поиск точного соответствия, то используем конструкцию ‘= (апостроф и знак равно).
  2. Если нужен поиск подстроки, т.е. всех ячеек, которые содержат нужное значение, то его надо заключить в звездочки. В нашем случае будут просуммированы все варианты Абакана (с «г.», без «г.», с пробелами перед-после и т.п.)
  3. Если просто ввести значение без равно и звездочек, то будут найдены и просуммированны все строки, где содержимое начинается с указанного значения, т.е. это равноценно звездочке в конце.

Несколько условий со связками «И» — «ИЛИ»

Если нужно просуммировать данные по нескольким условиям, связанным друг с другом логическим оператором И (AND), то ячейки с этими условиями должны быть в одной строке. Например, если нужно просуммировать все продажи Fanta по Абакану (в любом виде его написания), то это будет выглядеть так:

Если же нужно связать несколько условий логическим оператором ИЛИ (OR), то их нужно расположить в разных строчках. Например, если нужно просуммировать деньги по всем вариантам написания «города на Неве», коих великое множество:

И конечно же, можно комбинировать оба подхода, сочетания в одном запросе условия со связками И и ИЛИ одновременно:

В этом случае вычисляется сумма продаж Fanta в Абакане и Burn у Дубинина.

Суммирование по интервалу дат

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

В данном случае вычисляется сумма продаж Fanta за 2016 год и Фруктайм до 2016 года.

Условия для чисел

Для отбора по числовым критериям можно смело использовать обычные знаки неравенств >, =, » обозначающие «не равно» в синтаксисе Excel. Допустим, нам нужно просуммировать все данные по Fanta кроме Самары и по Квасу кроме Пензы — это будет выглядеть так:

Обратите внимание, что если нужно просуммировать Fant’у И Квас по всем городам, кроме Самары И Пензы, то конструкция таблицы условий будет уже немного другая — для каждого товара нужно исключить каждый город:

Заключение

Надеюсь, вы уже поняли, что функция БДСУММ является очень неплохим инструментом и, зачастую, более удобной альтернативой классическим функциям выборочного подсчета типа СУММЕСЛИ (SUMIF) и СУММЕСЛИМН (SUMIFS) . Кроме того, в той же категории Работа с базой данных (Database) можно найти ее «подруг», вычисляющих не только сумму:

  • БСЧЁТ (DCOUNTA) — количество непустых ячеек (в нашем случае — строк заказов)
  • ДМИН (DMIN) — минимальное (например, худшая сделка)
  • ДМАКС (DMAX) — максимальное (лучшая сделка)
  • ДСРЗНАЧ (DAVERAGE) — среднее арифметическое (например, средний чек)

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

Ссылка на основную публикацию
ВсеИнструменты 220 Вольт
Adblock
detector