Как синхронно вводить или выбирать ячейки на нескольких листах?
В Excel выбрать или ввести значение в одну ячейку очень просто, но как насчет синхронного выбора или ввода значения в одни и те же ячейки на разных листах в книге? Здесь я предлагаю быстрые приемы решения этой проблемы.
Синхронизировать ячейки ввода на листах
Синхронизировать выбор одинаковых ячеек на листах 
Синхронизировать ячейки ввода на листах
Синхронизировать ячейки ввода на листахВ Excel для синхронного ввода ячеек на нескольких листах выполните следующие действия:
1. Включите нужную книгу и удерживайте Ctrl Клавиша для выбора вкладок листа, на которых вы хотите синхронно вводить значения, см. снимок экрана:
2. Затем выберите ячейку на активном листе и введите желаемое значение. Смотрите скриншот:
Затем вы можете увидеть, что в каждую ячейку B10 на выбранных листах было введено одинаковое значение. Смотрите скриншот:
Наконечник: Если вы хотите автоматически обновлять ячейки, вы можете сделать следующее:
1. Выберите лист, например Лист 1, и выберите одну ячейку, например, B10, а затем введите нужное значение, см. Снимок экрана:
2. Держать Ctrl нажмите клавишу, чтобы выбрать все вкладки листа, вы синхронизируете ячейки, за исключением Sheet1, перейдите к одному выбранному листу и введите эту формулу = Лист1! B10 в ячейку B10, см. снимок экрана:
3. Нажмите Enter Клавиша, теперь все B10 на выбранных листах будут изменены, как B10 на Sheet1.
Синхронизировать выбор одинаковых ячеек на листах
Если вы хотите синхронно выбирать одни и те же ячейки на листах в книге, вы можете применить Kutools for ExcelАвтора Синхронизировать рабочие листы утилита.
После бесплатная установка Kutools for Excel, пожалуйста, сделайте следующее:
Выберите ячейку, которую вы хотите синхронно выбирать между листами на активном листе, например F1, и нажмите Кутулс Плюс > Рабочий лист > Синхронизировать рабочие листы. Смотрите скриншот:
Теперь каждый F1 на каждом листе был выбран.
Лучшие инструменты для работы в офисе
Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%
- Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма …
- Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон…
- Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы… Предотвращение дублирования ячеек; Сравнить диапазоны…
- Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор …
- Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое …
- Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии…
- Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом …
- Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF…
- Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.
Вкладка Office: интерфейс с вкладками в Office и упрощение работы
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
Комментарии (0)
Оценок пока нет. Оцените первым!
Вы можете синхронизировать электронные таблицы Microsoft Excel, чтобы изменения в одном автоматически отражались в другом. Можно создавать ссылки между различными рабочими листами, а также отдельными рабочими книгами Excel. Давайте рассмотрим три способа, как это сделать.
Функция вставки ссылок в Excel обеспечивает простой способ синхронизации электронных таблиц Excel. В этом примере мы хотим создать сводный лист итогов продаж по нескольким различным листам.
Начните с открытия электронной таблицы Excel, кликнув ячейку, на которую вы хотите сослаться, и затем выберите кнопку «Копировать» на вкладке «Главная».
Выберите ячейку, с которой вы ссылаетесь, нажмите стрелку списка «Вставить», затем выберите «Вставить ссылку».
Адрес, по которому ячейка синхронизируется, отображается на панели формул. Он содержит имя листа, за которым следует адрес ячейки.
Синхронизировать таблицы Excel с помощью формулы
Другой подход — создать формулу самостоятельно, не используя кнопку «Вставить ссылку».
Синхронизация ячеек на разных листах
Сначала кликните ячейку, из которой вы создаете ссылку, и введите «=».
Затем выберите лист, содержащий ячейку, на которую вы хотите сослаться. Ссылка на лист отображается в панели формул.
Наконец, нажмите на ячейку, на которую хотите сослаться. Заполненная формула отображается на панели формул. Нажмите клавишу «Ввод».
Синхронизация ячеек на отдельных рабочих книгах
Вы также можете полностью связать ячейку на листе другой рабочей книги. Для этого необходимо сначала убедиться, что другая рабочая книга открыта, прежде чем начинать формулу.
Нажмите на ячейку, с которой хотите связать, и введите «=». Переключитесь на другую книгу, выберите лист, затем нажмите на ячейку для ссылки. Имя книги предшествует имени листа в строке формул.
Если книга Excel, на которую вы ссылаетесь, закрыта, в формуле будет показан полный путь к файлу.
И когда рабочая книга, содержащая ссылку на другую рабочую книгу, будет открыта, вам, вероятно, будет предложено обновить ссылки. Это зависит от ваших настроек безопасности.
Нажмите «Включить содержимое», чтобы обновления в другой книге автоматически отображались в текущей.
Синхронизация таблиц Excel с использованием функции поиска
Предыдущие способы синхронизации двух листов или рабочих книг используют ссылки на определенную ячейку. Иногда это может быть недостаточно, потому что ссылка вернет неправильное значение, если данные отсортированы и перемещены в другую ячейку. В этих сценариях использование функции поиска является хорошим подходом.
Существует множество функций поиска, но чаще всего используется VLOOKUP, поэтому давайте воспользуемся этим.
В этом примере у нас есть простой список данных о сотрудниках.
На другом листе мы храним учебные данные о сотрудниках. Мы хотим найти и вернуть возраст сотрудников для анализа.
Эта функция требует четырех частей информации: что искать, где искать, номер столбца с возвращаемым значением и какой тип поиска вам нужен.
Была использована следующая формула VLOOKUP.
=VLOOKUP(A2,Employees!A:D,4,FALSE)
A2 содержит идентификатор сотрудника для поиска на листе сотрудников в диапазоне A: D. Столбец 4 этого диапазона содержит возраст для возвращения. И False указывает точный поиск по идентификатору.
Метод синхронизации электронных таблиц Excel во многом определяется тем, как структурированы ваши данные и как они используются.
Данная статья о том, как связать таблицы, чтобы данные из одной таблицы автоматически переносились во вторую.
Приведу три способа Автоматического переноса данных с одного листа программы «Эксель» в другой.
Первый, самый простой и примитивный способ связи двух таблиц на разных листах документа -вставка данных при помощи опции специальной вставки.
Рассмотрим, как соединить две таблицы по шагам.
Первый шаг.
Необходимо выделить ту таблицу, из которой будут транслироваться данные во вторую таблицу.
Второй шаг.
Копировать информацию сочетанием клавиш ctrl+C или вызвав контекстное меню правой кнопкой мыши и кликнув по пункту меню «Копировать»
Третий шаг.
Перейти на лист документа «Excel», в который Вы планируете транслировать информацию из первой таблицы.
Четвертый шаг.
Поставить курсор в первую (левую верхнюю) ячейку таблицы и выбрать в меню «Вставка» пункт «Вставить связь». В некоторых версиях программы «Excel» этот пункт находится в меню «Специальная вставка»
После вставки связи следует отформатировать вид ячеек – привести их к надлежащему виду.
Второй способ переноса данных из одной таблицы в другую — это использование сводных таблиц в программе «Excel».
При использовании данного метода роль второй таблицы («реципиента») играет сама сводная таблица.
При клике правой кнопкой мыши по сводной таблице и нажатии на пункт «Обновить» сводная таблица автоматически перенесет все данные из связанного массива информации («таблицы донора»).
О том, как в «Эксель» создавать сводные таблицы подробно написано в статье:
Как делать сводные таблицы в программе «Excel» и для чего они нужны.
Третий способ самый эффективный и наиболее автоматизированный — это использование меню надстройки «Power Query».
Правда нужно отметить, что этот способ подходит только пользователям Excel 2016 и пользователям Excel 2013и выше с установленной надстройкой «Power Query».
Смысл способа в следующем:
Необходимо открыть вкладку «Power Query». В разделе «Данные Excel» нажимаем кнопку (пиктограмму) «Из таблицы».
Далее нужно выбрать диапазон ячеек, из которых нужно «притянуть» информацию и нажимаем «Ок».
После выбора области данных появится окно настройки вида новой таблицы. В этом окне Вы можете настроить последовательность вывода столбцов и удалить ненужные столбцы.
После настройки вида таблицы нажмите кнопку «Закрыть и загрузить»
Обновление полученной таблицы происходит кликом правой кнопки мыши по названию нужного запроса в правой части листа (список «Запросы книги»). После клика правой кнопкой мыши в выпадающем контекстном меню следует нажать на пункт «Обновить»
Russian (Pусский) translation by Andrey Rybin (you can also view the original English article)
По мере того как вы используете и создаете больше рабочих документов Excel, у вас может возникнуть необходимость связать их между собой. Может быть вы захотите написать формулу, которая использует данные с разных Листов. Или может даже вы напишете формулу, которая будет использовать данные из разных Книг (стандартное название рабочего документа Excel — workbook (анг.))
Я понял, что если я хочу, чтобы мои файлы были понятными и аккуратными, лучше разделять большие Листы с данными от формул, которые их обрабатывают. Я часто использую одну Книгу или Лист, который обрабатывает все что мне нужно.
В этом уроке, вы узнаете как добавлять ссылки на данные в Excel. Сначала, мы научимся связывать данные из разных Листов в пределах одной Книги. Затем, мы перейдем к связыванию нескольких Книг Excel, чтобы импортировать и синхронизировать данные между файлами.
Как Можно Быстро Организовать Ссылку Между Данными в Книгах Excel (Смотри и Учись)
Я покажу вам два примера, как можно связать данные из ваших электронных таблиц. Вы узнаете как извлекать данные из другой таблицы в Excel и как организовать связь между двумя Книгами. Мы также рассмотрим основные примеры написания формул, использующих данные из различных Листов в пределах одного рабочего документа.
Давайте посмотрим иллюстрированное руководство по связыванию Листов и Книг в Excel.
Основы: Как Добавить Ссылку между Листами в Excel
Давайте начнем с того, что научимся создавать формулы, которые используют данные с других Листов. Вы возможно уже знаете, что рабочий документ (Книга) Excel, может содержать несколько рабочих страниц (Листов). Каждый лист имеет свою вкладку, и вы можете переключаться между ними щелкая по вкладкам в нижней части документа Excel.
Сложные документы могут содержать большое количество листов. И в какое-то время, вам придется написать формулу которая будет использовать данные из разных листов.
Может вы будете использовать один лист в документе для всех ваших формул, которые обрабатывают данные, а данные будете распределять по отдельным листам.
Давайте разберемся как написать многостраничную формулу, которая работает с данными из разных Листов в пределах одного рабочего документа.
1. Создайте в Excel новую Формулу
Многие формулы в Excel начинаются со знака равно (=). Дважды щелкните или начните печатать в ячейке, и вы начнете создавать формулу, в которую вы хотите вставить ссылку. Например, я собираюсь написать формулу, которая будет суммировать значения из разных ячеек.
Я открываю запись знаком =, и затем щелкаю по первой ячейке на текущем листе, чтобы создать первую часть формулы. Затем, я напечатаю знак +, чтобы добавить значение из второй ячейки в этой формуле.
Пока не завершайте ввод формулы и не жмите Enter! Вам нужно оставить формулу открытой перед тем как вы переключитесь на другой Лист.
2. Переключитесь между Листами в Excel
Пока ваша формула не закончена, щелкните на вкладке другого листа в нижней части программы. Очень важно, чтобы вы не закрывали формулу, перед тем как вы щелкните по другой ячейке, которую вы хотите вставить в формулу.
После того, как вы переключитесь на другой лист, щелкните по ячейке которую вы хотите добавить в формулу. Как вы можете видеть на картинке ниже, Excel автоматически запишет часть формулы, которая будет обращаться к ячейке на другом Листе.
Заметьте на картинке ниже, что для ссылки на ячейку на другом Листе, Excel добавил надпись «Sheet2!B3», которая ссылается на ячейку В3 на листе с названием Sheet2. Вы можете написать это вручную, но когда в щелкаете по ячейке, Excel делает эту надпись автоматически за вас.
3. Закройте Excel формулу
Теперь, вы можете нажать enter, чтобы завершить создание мульти-страничной формулы. Когда вы это делаете, Excel вернется туда, где вы начали создавать формулу и покажет вам результат.
Вы можете продолжать набирать формулу, включая в нее ячейки с других листов и другие ячейки на этом же листе. Продолжайте комбинировать ссылки со всего рабочего документа, на все данные, которые вам нужны.
Повышаем Уровень Сложности: Как Создать Связь Между Несколькими Книгами Excel
Давайте узнаем, как извлечь данные из другого рабочего документа. С помощью этого навыка, вы сможете писать формул, которые связывают данные из разных рабочих документов Excel.
Для этого раздела урока, вам понадобится два рабочих документа, которые вы можете скачать в виде бесплатного приложения к этому уроку. Откройте их оба в Excel, и следуйте дальнейшим указаниям.
1. Откройте Две Рабочие Книги
Давайте начнем создавать формулу, которая будет использовать данные из двух различных документов.
Самый простой способ сделать это, это открыть два документа Excel, одновременно и расположить их рядом друг с другом. Я использую для этого функцию Windows Snap, чтобы каждое окно занимало пол экрана. Вам нужно иметь две книги перед собой, чтобы писать формулу, которая будет взаимодействовать с двумя Книгами.
На рисунке ниже, я открыл два рабочих документа один рядом с другим, для которых я буду создавать формулы. В моем примере, я веду бизнес, и покупаю различные продукты, и продаю их в разные страны. Итак, я использую разные рабочие документы, чтобы отслеживать покупки/продажи и данные по стоимости.
2. Начните Создавать Формулу в Excel
Цена за которую я покупаю товары, может измениться, а так же может измениться соотношение курсов к валюте в которой я получаю выплаты. Поэтому мне нужно иметь список в котором отслеживаются курсы валют и умножать их на стоимость моих закупок. Это подходящая возможность, чтобы использовать информацию из двух файлов и записать формулу, которая будет использовать данные обоих файлов.
Давайте рассмотрим число баррелей нефти которую я покупаю каждый месяц и цену за баррель. Сначала в ячейке из колонки Cost (ячейка C3), я начинаю создавать формулу, щелкнув в ней и нажав знак равно (=), а затем щелкаю по ячейке В3, чтобы взять значение для количества. Теперь, я жму знак *, чтобы уможножить количество на курс.
Итак, ваша формула, пока должна выглядеть так:
=B3*
Не закрывайте пока вашу формулу. Убедитесь, что она осталась открытой перед тем как перейти к следующему шагу; нам нужно указать Excel данные по цене, на которую будет умножаться количество.
3. Переключитесь на Другой Рабочий Документ Excel
Пришло время переключиться на другой рабочий документ, вот почему так важно иметь данные из двух документов между собой, когда вы работаете с двумя Книгами.
Пока ваша формула открыта, щелкните по вкладке другой книги. Затем, щелкните по ячейке в вашей второй Книге, чтоб связать данные из двух файлов Excel.
Excel автоматически добавит ссылку на другой рабочий документа, как часть формулы:
=B3*[Prices.xlsx]Sheet1!$B$2
После того как вы нажмете Enter, Excel автоматически рассчитает окончательную стоимость умножив количество в первой книге на цену из второй книги.
Потренируйте свои навыки работы в Excel, умножая количество товара или объем на соответствующую ему цену из документа «Prices«.
Главное, расположить книги одну рядом с другой, и создавать формулу переключаясь между ними.
При этом, ничего не мешает вам использовать больше, чем два рабочих документа. Вы можете открыть много рабочих документов, на которые вы хотите ссылаться, и создавать формулы, связывающие данные между множеством листов.
Как Обновлять Данные При Работе с Несколькими Книгами
Когда вы используете формулы, которые ссылаются на разные книги Excel, вам нужно позаботится о том, как будут обновляться данные.
Итак, что же произойдет, если данные в Книге, на которую вы ссылаетесь, поменяются? Будут ли выполнены автоматические изменения в вашей Книге, или вам нужно обновлять ваши файлы, чтобы извлечь последние изменения и импортировать их?
Ответ такой, «зависит», и в основном зависит от того, открыты ли два документа одновременно.
Пример 1: Открыты Обе Книги Excel
Давайте проверим пример используя те же книги, что мы использовали раньше. Обе книги открыты. Давайте посмотрим, что произойдет, если мы изменим цену нефти за баррель с 45$ на 75$:
На сриншоте ниже, вы можете видеть, что когда мы изменили цену на нефть, результаты в другом документе автоматически поменялись.
Важно помнить: если оба рабочих документа открыты одновременно, изменения будут внесены автоматически в реальном времени. Когда вы меняете переменную, то информация в другом документе будет автоматически изменена или пересчитана, на основании новых данных.
Пример 2: Если Один Рабочий Документ Закрыт
А что если вы открыли только один рабочий документ? Например,
каждое утро мы обновляем цены на наши товары и валюты, а вечером мы
рассматриваем влияние изменений на наши покупки и продажи.
В следующий раз, когда вы откроете документ, который ссылается на другие таблицы, вы должны увидить сообщение подобное тому, что приведено ниже. Вы можете щелкнуть по кнопке Обновить (Update), чтобы извлечь полседние данные из документа, на который ссылается формула.
Вы так же можете видеть меню, где можно нажать на копку Включить содержимое (Enable Content), чтобы автоматически обновлять данные между файлами Excel.
Подводим итоги и продолжаем обучаться работе в Excell
Написание формул которые берут данные из разных Листов и Книг, важный навык, при работе в Excel. Тем более, что это совсем не сложно, если вы знаете как это делать.
Посмотрите эти дополнительные уроки, чтобы узнать больше о том как работать с программой Excel и как обрабатывать в ней данные. Эти уроки, хорошо подойдут для того, чтобы продолжить обучение.
Дайте мне знать в комментариях, если у вас остались какие-то вопросы, по поводу того как организовать связь между различными рабочими документами Excel.
Вы можете синхронизировать электронные таблицы Microsoft Excel, чтобы изменения в одной из них автоматически отражались в другой. Можно создавать связи между разными листами, а также отдельными книгами Excel. Давайте рассмотрим три способа сделать это.
Функция «Вставить ссылку» в Excel обеспечивает простой способ синхронизации электронных таблиц Excel. В этом примере мы хотим создать сводный лист с итогами продаж из нескольких разных листов.
Начните с открытия электронной таблицы Excel, щелкнув ячейку, на которую вы хотите создать ссылку, а затем нажмите кнопку «Копировать» на вкладке «Главная».
Выберите ячейку, из которой вы устанавливаете ссылку, щелкните стрелку списка «Вставить», затем выберите «Вставить ссылку».
Адрес ячейки, с которой она синхронизируется, отображается в строке формул. Он содержит имя листа, за которым следует адрес ячейки.
Синхронизация таблиц Excel с помощью формулы
Другой подход — создать формулу самостоятельно, не используя кнопку «Вставить ссылку».
Синхронизировать ячейки на разных листах
Сначала щелкните ячейку, из которой вы создаете ссылку, и введите «=».
Затем выберите лист, содержащий ячейку, на которую вы хотите создать ссылку. Ссылка на лист отображается на панели формул.
Наконец, щелкните ячейку, на которую хотите создать ссылку. Заполненная формула отображается на панели формул. Нажмите клавишу «Enter».
Синхронизация ячеек в отдельных книгах
Вы также можете полностью указать ссылку на ячейку на листе другой книги. Для этого вы должны сначала убедиться, что другая книга открыта, прежде чем начинать формулу.
Щелкните ячейку, из которой вы хотите создать ссылку, и введите «=». Переключитесь на другую книгу, выберите лист, затем щелкните ячейку, на которую нужно создать ссылку. Имя книги предшествует имени листа в строке формул.
Если книга Excel, с которой вы связались, закрыта, в формуле будет показан полный путь к файлу.
И когда книга, содержащая ссылку на другую книгу, будет открыта, вы, вероятно, увидите сообщение, разрешающее обновление ссылок. Это зависит от ваших настроек безопасности.
Нажмите «Включить содержимое», чтобы обновления из другой книги автоматически отражались в текущей.
Синхронизация таблиц Excel с помощью функции поиска
Предыдущие методы синхронизации двух листов или книг используют ссылки на определенную ячейку. Иногда этого может быть недостаточно, потому что ссылка вернет неверное значение, если данные будут отсортированы и перемещены в другую ячейку. В этих сценариях хорошим подходом является использование функции поиска.
Существует множество функций поиска, но чаще всего используется ВПР, поэтому давайте воспользуемся ею.
В этом примере у нас есть простой список данных о сотрудниках.
На другом листе мы храним данные обучения сотрудников. Мы хотим найти и вернуть возраст сотрудников для анализа.
Эта функция требует четырех частей информации: что искать, где искать, номер столбца с возвращаемым значением и какой тип поиска вам нужен.
Использовалась следующая формула ВПР.
=VLOOKUP(A2,Employees!A:D,4,FALSE)
A2 содержит идентификатор сотрудника, который нужно искать на листе «Сотрудники» в диапазоне A: D. Столбец 4 этого диапазона содержит возвращаемый возраст. А False указывает точный поиск по идентификатору.
Метод, который вы выбираете для синхронизации электронных таблиц Excel, во многом зависит от того, как структурированы ваши данные и как они используются.