Перекрестные запросы в excel
Обновлено: 07.07.2024
С помощью перекрестной ссылки можно создать связь с другой частью документа. Например, можно использовать перекрестную ссылку для связи с диаграммой или рисунком, который находится в другом месте документа. Перекрестная ссылка имеет вид ссылки, по которой пользователь может перейти к целевому элементу.
Если вы хотите создать связь с другим документом, создайте гиперссылку.
Сначала создайте элемент, на который должна указывать ссылка
Невозможно создать перекрестную ссылку на несуществующий объект, поэтому сначала убедитесь в том, что у вас уже есть диаграмма, заголовок, номер страницы или другой элемент, на который она будет указывать. В диалоговом окне для вставки перекрестной ссылки будут перечислены все элементы, на которые она может указывать. На рисунке ниже показан пример.
Вставьте перекрестную ссылку
В документе введите текст, который начинает перекрестную ссылку. Например, на рисунке 2 вы можете найти пояснение к восходящей тенденции.
На вкладке Вставка выберите команду Перекрестная ссылка.
В поле Тип ссылки щелкните раскрывающийся список, чтобы выбрать тип элемента, на который будет указывать ссылка. Список возможных вариантов зависит от типа элемента (заголовок, номер страницы и т. п.), с которым нужно создать связь.
В поле Добавить ссылку на выберите сведения, которые нужно вставить в документ. Набор доступных вариантов зависит от того, что вы выбрали на шаге 3.
В поле Для какого. выберите конкретный элемент, на который должна указывать перекрестная ссылка.
Чтобы разрешить пользователям переходить к элементу, на который указывает ссылка, установите флажок Вставить как гиперссылку.
Если флажок Добавить слово "выше" или "ниже" активен, установите его, чтобы включить относительное положение целевого элемента.
Перекрестные ссылки вставляются в виде полей
Перекрестные ссылки вставляются в документ как поля. Поле — это набор сведений, которые автоматически вставляют в документ текст, рисунки, номера страниц и другие материалы. Например, поле DATE вставляет текущую дату. Преимуществом использования полей является то, что вставляемый контент (дата, номер страницы, рисунок и т. д.) обновляется при изменении. Например, если вы пишете документ в течение определенного периода времени, дата будет меняться каждый день при его открыть и сохранить. Кроме того, если обновить рисунок, который хранится в другом месте, но на который ссылается поле, обновление будет включено автоматически, без необходимости повторной вставки рисунка.
Если вы вставили перекрестную ссылку и она похожа на , word отображает коды полей вместо результатов полей. При печати документа или скрытие кодов полей их результаты заменяют коды полей. Чтобы вместо кодов полей видеть их результаты, нажмите клавиши ALT+F9 или щелкните код поля правой кнопкой мыши и выберите в меню пункт "Коды полей".
Используйте главный документ
Если вам нужно создать перекрестные ссылки на элементы, которые находятся в отдельном документе, но вы не хотите использовать гиперссылки, то сначала объедините документы в один главный документ, а затем добавьте перекрестные ссылки. Главный документ — это контейнер для набора отдельных файлов (вложенных документов). Его можно использовать для создания документа, состоящего из нескольких частей (например, книги с несколькими главами), и работы с ним.
Итак, напомню, у нас есть таблица Кадры, которая содержится в файле Excel Сотрудники, к которому мы и будем обращаться. Обращаться откуда? Правильно, из другого файла Excel, в который и будет переноситься информация. Для начала разберемся, каким образом можно перекачать из файла в файл информацию в принципе, а потом будем смотреть, как этим процессом управлять.
Как видно, Excel просто создан для того, чтобы обращаться за данными из различных баз данных (Access, Oracle) и прочих структурированных источников. Это закономерно, ведь так удобнее переносить для обработки большие массивы данных, которые уже хранятся в базах данных.
При активации команды для нас открывается окно Выбор источника данных, в котором на вкладке Базы данных выбираем пункт Excel Files. Посмотрите, чтобы внизу была установлена галочка Использовать мастер запросов.
Нажатие кнопки Ok запускает мастер запросов Microsft Query и отображает окно Создание запроса, выбор столбцов, где в левой панели видим перечень заголовков столбцов, принадлежащих выбранной таблице (в виде выпадающего списка). Да, как видно, панелей две с кнопками по центру. При помощи этих кнопок слева на право выбираютсятся те столбцы, данные из которых следует перенести и отобразить в нашем файле. Хоть все сразу, хоть по отдельности.
Для иллюстрации выберем такие столбцы:
Это будет как бы краткий список сотрудников для каких-то учетов. Вот какой вид будет иметь наше окно с выбранными столбцами:
В итоге будем иметь вот такую таблицу , которая была получена на основе нашей большой таблицы с множеством данных.
Повторюсь, тут было рассказано, как при помощи мастера запросов Microsft Query получать выборки интересующих данных из других, более полных, таблиц.
Подчеркну ещё один момент, чтобы не говорили: «Оооой, это ж такооой головняк, каждый раз отакое творииить…». Увы, есть такие люди с такими заявлениями.
Если данные в исходной (большой) таблице как-то изменились, добавились новые строки или удалились какие-то, а может в строках произошли правки (поменяли данные о сходивших в отпуск), то достаточно на нашей таблице-выборки выполнить щелчок правой кнопкой мыши и в контекстном меню выбрать команду Обновить. При этом данные будут считаны повторно и все изменения будут применены к нашей выборке. Данные актуальны!
Совет Чтобы упросить, сформированы ли данные на этом плане с помощью Power Query, выберем ячейку с данными и, если появится вкладка ленты Контекст запроса, данные будут загружены из Power Query.
Знать, в какой среде вы сейчас? Power Query хорошо интегрирован с пользовательским интерфейсом Excel, особенно при импорте данных, работе с подключениями и редактировании таблиц, Excel таблиц и именовых диапазонов. Чтобы избежать путаницы, важно знать, в какой среде вы находитесь, в какой Excel или Power Query в любой момент времени.
Знакомые Excel, лента и сетка
Лента редактора Power Query и предварительный просмотр данных
Например, работа с данными на Excel значительно отличается от Power Query. Кроме того, подключенные данные, которые вы видите на Excel, могут не работать с Power Query для их обработки. Это происходит только при загрузке данных на таблицу или модель данных из Power Query.
Переименование я вкладок на таблицах Ямы лучше переименовывать по своему смыслу, особенно если их много. Особенно важно пояснить разницу между данными и данными, загруженными из редактора Power Query. Даже если у вас всего два листа: с таблицей Excel , которая называется Лист1,и запросом, созданным путем импорта таблицы Excel Таблица1,ее можно легко запутать. Всегда имеет смысл изменить названия ярлыков по умолчанию на более понятное. Например, переименуйте Лист1 в Таблицу данных и Таблицу1 в Таблицу запросов. Теперь понятно, какая вкладка с данными, а какая вкладка с запросом.
Вы можете создать запрос из импортируемых данных или пустой запрос.
Создание запроса из импортируемых данных
Это самый распространенный способ создания запроса.
Выберем ячейку в данных и выберите запрос> Изменить.
Создание пустого запроса
Вы можете начать с нуля. Это можно сделать двумя способами.
Выберите Данные> Получить данные >из других источников > пустой запрос.
Выберите Данные> получить данные >запустить редактор Power Query.
На этом этапе вы можете вручную добавлять шаги и формулы, если язык формул Power Query M хорошо разный.
Кроме того, можно выбрать команду На главная в группе Новый запрос. Выполните одно из указанных ниже действий.
Выберите Новый источник, чтобы добавить источник данных. Эта команда выглядит так же, как> "Получить данные" на Excel ленте.
Выберите Последние источники, чтобы выбрать источник данных, с помощью который вы работали. Эта команда выглядит так же, как> последние источники на Excel ленте.
Чтобы ввести данные вручную, выберите Ввести данные. Вы можете выбрать эту команду, чтобы попробовать редактор Power Query независимо от внешнего источника данных.
Предположим, что запрос действителен и не имеет ошибок, его можно загрузить обратно на таблицу или в модель данных.
Загрузка запроса из редактора Power Query
В редакторе Power Query сделайте следующее:
Чтобы загрузить на таблицу, выберите Главная> Закрыть & Загрузить > Закрыть & загрузить.
Чтобы загрузить данные в модель данных, выберите Главная > закрыть & Загрузить > Закрыть & Загрузить в.
В диалоговом окне Импорт данных выберите добавить эти данные в модельданных.
Совет Иногда команда "Загрузить в" неатривна или отключена. Это может произойти при первом создании запроса в книге. В этом случае нажмите кнопку Закрыть & Загрузить, на новом > запросы данных & Connections > Запросы, щелкните запрос правой кнопкой мыши и выберите загрузить в . Кроме того, на ленте редактора Power Query выберите Запрос > Загрузить в.
Загрузка запроса из области "Запросы и подключения"
В Excel может потребоваться загрузить запрос на другой таблицу или в модель данных.
В Excel выберите Запросы> запросы & Connections, а затем выберите вкладку Запросы.
В списке запросов найдите запрос, щелкните его правой кнопкой мыши и выберите загрузить в. Появится диалоговое окно Импорт данных.
Выберите, как вы хотите импортировать данные, а затем выберите ОК. Чтобы получить дополнительные сведения об использовании этого диалогового окна, выберите знак вопроса (?).
Существует несколько способов редактирования запроса, загруженного на таблицу.
Изменение запроса на Excel данных
Чтобы изменить запрос, найдите ранее загруженную из редактора Power Query, выберем ячейку в данных и выберите запрос> Изменить.
Изменение запроса в области "Запросы & подключения"
Возможно, вы & области "Запросы и подключения" удобнее всего использовать, если в одной книге много запросов и вы хотите быстро найти их.
В Excel выберите Запросы> запросы & Connections, а затем выберите вкладку Запросы.
В списке запросов найдите запрос, щелкните его правой кнопкой мыши и выберите изменить.
Изменение запроса в диалоговом окне "Свойства запроса"
В Excel выберите Data > Data & Connections > Запросы, щелкните запрос правой кнопкой мыши и выберите Свойства ,выберите вкладку Определение в диалоговом окне Свойства и нажмите кнопку Изменить запрос.
Совет Если вы работаете с запросом на > данных, в диалоговом окнеСвойства выберите вкладку Определение, а затем — Изменить запрос.
Модель данных обычно содержит несколько таблиц, расположенных в связи. Запрос загружается в модель данных с помощью команды Загрузить, чтобы отобразить диалоговое окно Импорт данных, а затем в поле Добавить эти данные в режим данныхl. Дополнительные сведения о моделях данных см. в дополнительных сведениях о том, какие источники данных используются в модели данных книги,Создание модели данных в Excelи Создание таблиц с помощью нескольких таблиц.
Чтобы открыть модель данных, выберите Power Pivot > Управление.
В нижней части окна Power Pivot выберите вкладку нужной таблицы.
Подтвердим, что отображается правильная таблица. Модель данных может иметь много таблиц.
Обратите внимание на имя таблицы.
Чтобы закрыть окно Power Pivot, выберите файл> Закрыть. Чтобы освободить память, может потребоваться несколько секунд.
Выберите > подключения & свойства >Запросы, щелкните запрос правой кнопкой мыши и выберите изменить.
Завершив внесение изменений в редакторе Power Query, выберите файл> Закрыть & загрузить.
Запрос на этом и в таблице в модели данных обновляются.
Загрузка запроса в модель данных занимает необычно много времениЕсли вы заметили, что загрузка запроса в модель данных занимает намного больше времени, чем загрузка на лист, проверьте действия Power Query, чтобы узнать, фильтруется ли текстовый столбец или структурированный столбец списка с помощью оператора Contains. Это действие приводит Excel повторно прогонять весь набор данных для каждой строки. Более того, Excel не могут эффективно использовать многопрочитанные выполнения. В качестве обходного решения попробуйте использовать другой оператор, например Равно или Начинает с.
Корпорация Майкрософт знает об этой проблеме и находится в стадии исследования.
Вы можете загрузить Power Query:
На один из них. В редакторе Power Query выберите Home> Close & Load > Close & Load (&).
В модель данных. В редакторе Power Query выберите Home> Close & Load > Close & Load To.
По умолчанию Power Query загружает запросы на новый таблицу при загрузке одного запроса и одновременно загружает несколько запросов в модель данных. Вы можете изменить поведение по умолчанию для всех книг или только текущей книги. При настройке этих параметров Power Query не меняет результаты запроса на месте или в данных модели данных и примечаниях.
Вы также можете динамически переопределять стандартные параметры запроса с помощью диалогового окна Импорт, которое отображается после выбора параметра Закрыть & Загрузкав.
Глобальные параметры, которые применяются во всех книгах
В редакторе Power Query выберите Параметры > параметры и параметры >запроса.
В диалоговом окне Параметры запроса в левой части в разделе GLOBAL выберите Загрузка данных.
В разделе Загрузка запроса Параметры по умолчанию сделайте следующее:
Выберите Использовать стандартные параметры загрузки.
Выберите Указать настраиваемые параметры загрузкипо умолчанию , а затем выберите или сберем загрузить на таблицу или Загрузить в модель данных.
Совет В нижней части диалогового окна можно выбрать восстановить параметры по умолчанию, чтобы вернуться к настройкам по умолчанию.
Параметры книги, которые применяются только к текущей книге
В диалоговом окне Параметры запроса в левой части в разделе ТЕКУЩАЯ КНИГА выберите Загрузка данных.
Выполните одно или несколько из указанных ниже действий:
В области Обнаружение типоввыберите или сберем для обнаружения типов столбцов и их заглавных колонок для неструктурированных источников.
По умолчанию они обнаруживаются. Если вы предпочитаете формировать данные самостоятельно, отоставьте этот параметр.
В области Связивыберите или сберем создать связи между таблицами при первом добавлении в модель данных.
По умолчанию перед загрузкой в модель данных можно найти существующие связи между таблицами, например внешние ключи в реляционной базе данных, и импортировать их вместе с данными. Если вы предпочитаете делать это самостоятельно, сделайте это самостоятельно.
В области Связивыберите или отоберете update relationships when refreshing queries loaded to the Data Model (Связи) при обновлении запросов, загруженных в модель данных.
По умолчанию отношения не обновляются. При обновлении запросов, уже загруженных в модель данных, Power Query находит существующие связи между таблицами, например внешние ключи, в реляционной базе данных и обновляет их. Это может привести к удалению связей, созданных вручную после импорта данных, или к новым связям. Тем не менее, если вы хотите сделать это, выберите этот параметр.
В области Фоновыеданные выберите или отпустите разрешение предварительного просмотра данных для скачивания в фоновом режиме.
По умолчанию предварительный просмотр данных загружается в фоновом режиме. Если вы хотите сразу же видеть все данные, отобираем этот параметр.
См. также
Excel для Интернета включает в себя технологию Power Query (также называемую функцией & преобразования), которая обеспечивает больше возможностей при работе с импортируемыми источниками данных. Вы можете просматривать запросы и связанные с ними сведения в области задач Запросы.
Примечание: Следите за дальнейшими объявлениями об улучшениях Excel интеграции с Веб-сайтом и Power Query.
Просмотр области задач "Запросы"
Убедитесь, что вы в режиме правки (выберите Просмотр> редактирование).
Выберите Запросы > данных.
Excel отображает области задач Запросы и все запросы в текущей книге, а также сведения о запросе, такие как количество строк, дата последнего обновления, расположение и состояние загрузки. Если запрос загружается в модель данных, а не в сетку, Excel отображается "Только подключение".
В Microsoft Excel распространенной задачей является обращение к ячейкам на других листах или даже в разных файлах Excel. Сначала это может показаться немного сложным и запутанным, но как только вы поймете, как это работает, это не так сложно.
В этой статье мы рассмотрим, как ссылаться на другой лист в том же файле Excel и как ссылаться на другой файл Excel. Мы также расскажем о том, как ссылаться на диапазон ячеек в функции, как упростить работу с определенными именами и как использовать VLOOKUP для динамических ссылок.
Как сослаться на другой лист в том же файле Excel
Базовая ссылка на ячейку записывается в виде буквы столбца, за которой следует номер строки.
Таким образом, ссылка на ячейку B3 относится к ячейке на пересечении столбца B и строки 3.
При ссылке на ячейки на других листах этой ссылке на ячейку предшествует имя другого листа. Например, ниже приведена ссылка на ячейку B3 в названии листа «Январь».
Восклицательный знак (!) Отделяет имя листа от адреса ячейки.
Если имя листа содержит пробелы, то вы должны заключить имя в одинарные кавычки в ссылке.
Чтобы создать эти ссылки, вы можете ввести их непосредственно в ячейку. Однако проще и надежнее позволить Excel написать для вас ссылку.
Введите знак равенства (=) в ячейку, щелкните вкладку «Лист», а затем щелкните ячейку, на которую вы хотите сделать перекрестную ссылку.
Когда вы это сделаете, Excel запишет для вас ссылку на панели формул.
Нажмите Enter, чтобы завершить формулу.
Как сослаться на другой файл Excel
Вы можете ссылаться на ячейки другой книги, используя тот же метод. Просто убедитесь, что у вас есть другой файл Excel, прежде чем начать вводить формулу.
Введите знак равенства (=), переключитесь на другой файл и затем щелкните ячейку в этом файле, на которую вы хотите сослаться. Нажмите Enter, когда вы закончите.
Заполненная перекрестная ссылка содержит другое имя книги, заключенное в квадратные скобки, за которым следуют имя листа и номер ячейки.
Если имя файла или листа содержит пробелы, вам необходимо заключить ссылку на файл (включая квадратные скобки) в одинарные кавычки.
В этом примере вы можете увидеть знаки доллара ($) среди адресов ячейки. Это абсолютная ссылка на ячейку ( Узнайте больше об абсолютных ссылках на ячейки ).
При ссылках на ячейки и диапазоны в разных файлах Excel ссылки становятся абсолютными по умолчанию. Вы можете изменить это на относительную ссылку, если требуется.
Если вы посмотрите на формулу, когда указанная рабочая книга закрыта, она будет содержать полный путь к этому файлу.
Хотя создание ссылок на другие книги не вызывает затруднений, они более подвержены проблемам. Пользователи, создающие или переименовывающие папки и перемещающие файлы, могут разорвать эти ссылки и вызвать ошибки.
Хранение данных в одной книге, если это возможно, более надежно.
Как сделать перекрестную ссылку на диапазон ячеек в функции
Ссылка на одну ячейку достаточно полезна. Но вы можете написать функцию (например, SUM), которая ссылается на диапазон ячеек на другом листе или рабочей книге.
Запустите функцию как обычно, а затем нажмите на лист и диапазон ячеек — так же, как вы делали в предыдущих примерах.
В следующем примере функция SUM суммирует значения из диапазона B2: B6 на листе с именем Sales.
Как использовать определенные имена для простых перекрестных ссылок
В Excel вы можете назначить имя ячейке или диапазону ячеек. Это более значимо, чем адрес ячейки или диапазона, когда вы оглядываетесь на них. Если вы используете много ссылок в своей электронной таблице, присвоение им имен может значительно облегчить просмотр того, что вы сделали.
Более того, это имя уникально для всех таблиц в этом файле Excel.
Например, мы можем назвать ячейку «ChicagoTotal», и тогда перекрестная ссылка будет выглядеть так:
Это более значимая альтернатива стандартной ссылке, подобной этой:
Создать определенное имя легко. Начните с выбора ячейки или диапазона ячеек, которые вы хотите назвать.
Щелкните в поле «Имя» в верхнем левом углу, введите имя, которое хотите назначить, и нажмите клавишу «Ввод».
При создании определенных имен вы не можете использовать пробелы. Поэтому в этом примере слова были объединены в имени и разделены заглавной буквой. Вы также можете разделить слова с помощью символов, таких как дефис (-) или подчеркивание (_).
В Excel также есть менеджер имен, который упрощает мониторинг этих имен в будущем. Нажмите Формулы> Диспетчер имен. В окне диспетчера имен вы можете увидеть список всех определенных имен в книге, где они находятся и какие значения они хранят в данный момент.
Затем вы можете использовать кнопки вверху для редактирования и удаления указанных имен.
Как отформатировать данные в виде таблицы
При работе с обширным списком связанных данных использование функции «Формат как таблицы» в Excel может упростить обращение к данным в ней.
Возьмите следующую простую таблицу.
Это может быть отформатировано как таблица.
Нажмите на ячейку в списке, перейдите на вкладку «Главная», нажмите кнопку «Формат таблицы» и выберите стиль.
Убедитесь, что диапазон ячеек указан правильно и что у вашей таблицы есть заголовки.
Затем вы можете присвоить значимое имя вашей таблице на вкладке «Дизайн».
Затем, если бы нам нужно было суммировать продажи в Чикаго, мы могли бы обратиться к таблице по ее имени (с любого листа), за которой следовала бы квадратная скобка ([), чтобы увидеть список столбцов таблицы.
Выберите столбец, дважды щелкнув его в списке, и введите закрывающую квадратную скобку. Полученная формула будет выглядеть примерно так:
Вы можете увидеть, как таблицы могут упростить ссылки на данные для функций агрегирования, таких как SUM и AVERAGE, по сравнению со стандартными ссылками на листы.
Эта таблица мала для демонстрации. Чем больше таблица и чем больше листов в книге, тем больше преимуществ вы увидите.
Как использовать функцию VLOOKUP для динамических ссылок
Ссылки, используемые в примерах, до сих пор были привязаны к конкретной ячейке или диапазону ячеек. Это здорово и часто достаточно для ваших нужд.
Однако что, если ячейка, на которую вы ссылаетесь, может измениться при вставке новых строк или кто-то отсортирует список?
В этих сценариях вы не можете гарантировать, что требуемое значение будет по-прежнему находиться в той же ячейке, на которую вы изначально ссылались.
Альтернативой в этих сценариях является использование функции поиска в Excel для поиска значения в списке. Это делает его более устойчивым к изменениям листа.
В следующем примере мы используем функцию VLOOKUP, чтобы найти сотрудника на другом листе по его идентификатору сотрудника, а затем вернуть дату его начала.
Ниже приведен пример списка сотрудников.
Функция VLOOKUP просматривает первый столбец таблицы и затем возвращает информацию из указанного столбца справа.
Следующая функция VLOOKUP ищет идентификатор сотрудника, введенный в ячейку A2 в приведенном выше списке, и возвращает дату, объединенную из столбца 4 (четвертый столбец таблицы).
Ниже приведена иллюстрация того, как эта формула выполняет поиск в списке и возвращает правильную информацию.
Отличительной особенностью VLOOKUP по сравнению с предыдущими примерами является то, что сотрудник будет найден, даже если список изменится по порядку.
Примечание. VLOOKUP — невероятно полезная формула, и в этой статье мы только коснулись ее значения. Вы можете узнать больше о том, как использовать VLOOKUP из нашей статьи на эту тему .
В этой статье мы рассмотрели несколько способов перекрестных ссылок между электронными таблицами Excel и рабочими книгами. Выберите подход, который подходит для вашей задачи и с которым вам удобно работать.
Читайте также: