Как сделать перекрестный запрос в excel
Обновлено: 06.07.2024
На этом шаге будут рассмотрены перекрестные запросы.
В Access имеется возможность создания запросов, в которых используются групповые операции, в виде электронных таблиц, т.е. в качестве названий строк и столбцов такой "таблицы" используются те или иные поля таблиц, а в ее "ячейках" будут отображаться вычисляемые значения, которые соответствуют значениям, указанным в названии строки и столбца. Например, в качестве примера подобной электронной таблицы можно рассмотреть набор оценок, которые получил каждый студент по всем предметам. Другими словами, заголовками строк такой таблицы будут фамилии студентов, заголовками столбцов - названия предметов, а на пересечении строк и столбцов будет отображаться оценка того или иного студента по данному предмету, аналогично расположению данных в рабочих листах Excel.
Примечание. Вычисляемые значения не могут быть отредактированы, поэтому перекрестный запрос нет смысла использовать с целью изменения данных. Однако перекрестные запросы могут оказаться весьма полезными при создании отчетов.
Для создания перекрестного запроса, отображающего таблицу оценок студентов по предметам, следует, вначале создать новый запрос в режиме конструктора. Затем нужно в макет запроса добавить таблицы, в которых содержатся исходные данные запроса: Студенты, Успеваемость и Предметы.
После этого необходимо выполнить команду Запрос | Перекрестный, в результате чего в макете запроса среди параметров появится строка Перекрестная таблица, в которой для полей запроса можно с помощью разворачивающегося списка выбрать одно из следующих значений.
- Заголовки строк - выбирается для поля запроса, значения которого нужно использовать в качестве заголовков строк. В качестве заголовков строк необходимо определить хотя бы одно поле, при этом в строке Групповые операции необходимо выбрать значение Группировка, одну из итоговых функций или Выражение.
- Заголовки столбцов - выбирается для поля запроса, значения которого нужно использовать в качестве заголовков столбцов. С этой целью используется только одно поле, требования к которому аналогичны требованиям к полю, в котором установлено значение Заголовки Строк.
- Значение - выбирается для поля запроса, в котором вычисляется итоговое значение, отображаемое в "ячейках" перекрестного запроса. Такое поле должно быть единственным, при этом в строке Групповые операции для него необходимо выбрать одну из итоговых функций или задать выражение, в котором используются итоговые функции.
- (не отображается) - выбирается для того поля, значения которого не должны отображаться в перекрестном запросе.
В первом столбце запроса нужно выбрать поле Фамилия таблицы Студенты, при этом следует задать для него значение Группировка в поле Групповая операция, а также Заголовки строк в поле Перекрестная таблица. Также можно выбрать направление сортировки по возрастанию.
Для второго столбца запроса необходимо выбрать поле Название таблицы Предметы и установить для него значение Группировка в поле Групповая операция и Заголовки столбцов в поле Перекрестная таблица.
В третьем столбце запроса нужно выбрать поле Оценка таблицы Успеваемость и задать для него функцию Sum в поле Групповая операция, а также Значение в поле Перекрестная таблица.
Созданный запрос можно сохранить под именем ОценкиПоПредметам (рис. 1).
Рис. 1. Макет перекрестного запроса ОценкиПоПредметам
В результате выполнения перекрестного запроса ОценкиПоПредметам будет получена электронная таблица, содержащая оценки студентов по всем предметам (рис. 2). Данный запрос может быть использован, например, в отчете, который должен отображать итоги прошедшей сессии.
Рис. 2. Результат выполнения запроса ОценкиПоПредметам
Перекрестный запрос — это запрос, в котором происходит статистическая обработка данных, результаты которой выводятся в виде таблицы, очень похожей на сводную таблицу Excel.
Перекрестный запрос подсчитывает сумму, среднее, число значений или выполняет другие статистические расчеты, после чего результаты группируются в виде таблицы по двум наборам данных, один из которых определяет заголовки столбцов, а другой заголовки строк.
Мы будем создавать перекрестный запрос, в котором в строках выводятся должности работников , а в столбцах — пол работника. А в пересечении столбцов и строк — средний стаж для каждого работника. Для этого:
Откройте закладку Creatе (Создать) и щелкните по кнопке (Мастер запросов).
Появляется окно New Query (Новый запрос), в котором выбираем Crosstab Query Wizard (Создание перекрестного запроса). Нажать кнопку ОК.
Примените то, что вы узнали, - используйте функции для достижения перекрестного запроса между номером столбца и именем столбца в Excel (перекрестный запрос между номером столбца и именем столбца в Excel)
Нашел более простую и интуитивно понятную формулу преобразования имен столбцов в имена столбцов:
Где D36 - это метка столбца (например, D36 - 1688, результат преобразования: BLX)
Для сравнения, следующий метод немного неуклюжий.
Чтобы реализовать взаимную проверку меток столбцов и имен столбцов, сначала необходимо сгенерировать сравнительную таблицу меток столбцов и имен столбцов.
Методы, указанные ниже:
1. После ввода 1 в ячейку A1 пустого листа Excel нажмите Enter, выберите ячейку A1 и используйте функцию автозаполнения Excel, чтобы заполнить по горизонтали до самой большой метки столбца, которая равна 16384.
3. Выберите ячейку A2, нажмите CTRL + C, чтобы скопировать, затем нажмите SHIFT + CTRL + стрелка вправо (->), чтобы выделить все вправо, а затем нажмите CTRL + R, вы обнаружите, что горизонтальная копия (CTRL + R - новый трюк)
4. Скопируйте первую и вторую строки, создайте новый рабочий лист, выберите вставку значений и используйте функцию транспонирования.
На данный момент сравнительная таблица завершена.
Затем создайте функцию запроса, как показано на рисунке ниже.
Постскриптум: Хотя это очень простая и редко встречающаяся функция, при реализации этой функции используются многие важные функции (ЕСЛИОШИБКА, ВПР, ТОЧНО, ПОИСКПОЗ, ИНДЕКС, СРЕДНЕЕ, ЯЧЕЙКА, НАЙТИ). Поэтому, если вы хотите проверить способность человека применять функции Excel, это хороший вопрос.
Создавая перекрестный запрос в режиме конструктора, можно использовать произвольное число источников записей (таблиц и запросов). Однако для упрощения структуры рекомендуется сначала создать запрос на выборку, возвращающий все требуемые данные, а затем использовать этот запрос в качестве единственного источника записей для перекрестного запроса.
При построении перекрестного запроса в режиме конструктора используются строки бланка запроса Групповая операция и Перекрестная таблица, в которых указывается, какие значения поля станут заголовками столбцов, какие — заголовками строк, а какие будут использоваться для вычисления суммы, среднего, количества или других расчетов.
1. Значения в этих строках определяют, служит ли поле заголовком строки, заголовком столбца или сводным значением.
2. Этот параметр задает отображение значений поля как заголовков строк.
3. Этот параметр задает отображение значений поля как заголовков столбцов.
4. Эти параметры задают получение сводных значений.
1. На вкладке Создание в группе Другие щелкните Конструктор запросов.
2. В диалоговом окне Добавление таблицы дважды щелкните каждую таблицу или запрос, которые следует использовать в качестве источников записей.
Если выбирается несколько источников записей, убедитесь, что эти таблицы и запросы соединены по имеющимся в них общим полям.
3. Закройте диалоговое окно Добавление таблицы.
4. На вкладке Конструктор в группе Тип запроса выберите команду Перекрестный.
5. В окне конструктора запроса дважды щелкните каждое поле, которое следует использовать как источник заголовков строк. Можно выбрать до трех полей для создания заголовков строк.
6. В бланке запроса в строке Перекрестная таблица для каждого поля заголовков строк выберите Заголовки строк.
Можно ввести условие в строке Условие отбора, чтобы ограничить число результатов для этого поля. Можно также использовать строку Сортировка, чтобы указать порядок сортировки для поля.
7. В окне конструктора запросов дважды щелкните поле, которое необходимо использовать в качестве источника заголовков столбцов. Для этой цели можно выбрать только одно поле.
8. В бланке запроса в строке Перекрестная таблица для каждого поля заголовков столбцов выберите Заголовки столбцов.
9. В окне конструктора запроса дважды щелкните поле, которое следует использовать для расчета сводных значений. Для размещения сводных значений можно выбрать только одно поле.
10. В бланке запроса в строке Групповая операция для поля сводных значений выберите статистическую функцию, с помощью которой следует вычислять значения.
11. В строке Перекрестная таблица для поля сводных значений выберите Значение.
Условия отбора или сортировки для поля сводных значений задавать нельзя.
12. На вкладке Конструктор в группе Результаты выберите команду Запуск.
Читайте также: