Excel: создать ссылку на другую электронную таблицу

Пользователи Microsoft Excel ценят возможность создавать ссылки на другие электронные таблицы в активной папке. Особенно при работе с большими электронными таблицами с большим количеством формул, данных и функций полезно создавать ссылки, чтобы можно было эффективно работать со всеми данными. Это избавляет от утомительного ручного двойного ввода или копирования и вставки, что отнимает много времени и подвержено ошибкам.

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

Как создать ссылку на другую таблицу?

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

В нашем примере в Таблице 1 показано, сколько сотрудников имеет компания в разных местах в Германии и других странах Европы. Во вторую таблицу следует вывести только количество сотрудников филиала в Пассау. Связь между двумя таблицами гарантирует, что если данные в таблице 1 изменятся, информация в таблице 2 будет автоматически обновлена.

Чтобы создать ссылку на другую таблицу, сначала откройте обе таблицы в Microsoft Excel. Затем в целевой таблице щелкните целевую ячейку, в которой вы хотите отобразить данные из исходного файла. В нашем практическом примере это целевая ячейка B1.Формула ссылки всегда начинается со знака равенства. Введите "="в ранее выбранной ячейке.

На следующем шаге переключитесь на таблицу с данными о сотрудниках и выделите число 55 в ячейке C9. Это количество сотрудников в филиале в Пассау.

Нажмите Enter, чтобы закрыть ярлык. Число 55 сотрудников теперь появляется в Таблице 2, так как вы эффективно связали ячейки обоих рабочих листов друг с другом.

Если вы хотите создать ссылку вручную с помощью клавиатуры, используйте следующий синтаксис:

=имялиста!ячейка

Для имени листа введите имя листа, к ячейке которого вы хотите обратиться. В нашем примере имя листа - «Лист1».

Как выполнять вычисления в нескольких таблицах Excel?

Часто бывает, что расчеты приходится проводить по нескольким таблицам. В нашем образце рабочей тетради есть несколько электронных таблиц с одинаковой структурой. В таблице приведены затраты на электроэнергию различных заводов компании в течение одного года с разбивкой по месяцам. Рабочая тетрадь содержит данные за 2016–2021 годы. Итоговые и максимальные значения должны быть рассчитаны в сводном листе.

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

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

В следующем примере необходимо суммировать затраты на электроэнергию за последние несколько лет. На рисунке показана структура отдельных листов:

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

На следующем рисунке показано расположение рабочих листов в образце рабочей книги:

После завершения подготовки вы можете начать вводить формулы.

Как рассчитать сумму нескольких листов в Excel?

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

Формула, необходимая для суммирования, имеет следующую структуру:

=СУММ('ПервыйЛист:ПоследнийЛист'!Диапазон)

" С помощью FirstSheet вы передаете имя первого листа, который должен быть включен в расчет. Аргумент lastsheet определяет имя последнего листа для расчета. Учитываются также все листы, лежащие между этими двумя листами.Площадь определяет площадь, которую вы хотите суммировать."

Используйте следующую формулу для расчета суммы значений в ячейках H16 рабочих листов с 2016 по 2021 год:

=СУММ('2016:2021'!H16)

На следующем рисунке показано использование этой формулы в таблице примеров:

Адаптируя эту простую формулу, можно запрашивать и другие ячейки. Например, если вы хотите узнать, сколько электроэнергии было потреблено на заводе во Франкфурте в апреле с 2016 по 2021 год, замените в формуле ссылку на ячейку H16 на ячейку F7.

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

Как рассчитать максимальное значение нескольких листов в Excel?

В нашей примерной таблице, в которой сравниваются затраты на электроэнергию различных станций в период с 2016 по 2021 год, необходимо определить абсолютное максимальное значение всех отдельных значений за все годы. Отдельные значения находятся в таблицах с 2016 по 2021 год в ячейках B4:G15.

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

=МАКС('2016:2021'!B4:G15)

На следующем рисунке показано использование этой формулы в таблице примеров:

Как рассчитать среднее значение по разным таблицам?

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

=СРЗНАЧ('начальный лист:конечный лист'!диапазон ячеек)

С формулой вы передаете всего три аргумента:

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

Для нашего образца таблицы формула и результат выглядят так:

=СРЕДНИЙ('2016:2021'!B4:G15)

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

Если вы часто работаете с Excel, вам иногда нужно связать ссылки на ячейки из электронной таблицы, которые не находятся в том же документе.Это работает без проблем, добавляя название документа к описанной выше ссылке на ячейку. Это отмечено квадратными скобками (" [" или "]" ).

Пример:=[Документ1]Происхождение!G9

Используйте этот вариант, если открыта другая папка с именем «Документ1». С другой стороны, если документ не открыт, используйте путь, по которому он был сохранен.

Пример:=C:\Files\ИмяПапки\[Документ1]Происхождение!G9

Как связать ссылки на ячейки с помощью мыши?

Ссылки на другие документы не обязательно вводить вручную, так как вы можете легко связать ячейки всего несколькими щелчками мыши. (ВСТАВЬТЕ ИЗОБРАЖЕНИЕ)

Как исправить ошибку ссылки на ячейку?

При связывании электронных таблиц и использовании ссылок Microsoft Excel может отображать ошибку в ссылке на ячейку. В этом случае формула содержит ошибку или произошла ошибка при выделении области мышью

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

Итак, часто имеет смысл работать со ссылками в Excel. Основное преимущество работы со ссылками заключается в том, что вы можете выполнять свою работу более эффективно и быстро.

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