Как в экселе работать с данными с другого листа
Обновлено: 15.05.2024
Интерфейс
Настраиваем панель быстрого доступа
Опции, перенесенные на панель быстрого доступа, будут доступны при работе со всеми вашими книгами Excel (хотя можно ее настроить и отдельно для любой книги). Так что если пользуетесь какими-то командами и инструментами постоянно — добавляйте их туда.
Перемещаемся по ленте без мышки
Нажмите на Alt. На ленте инструментов появились цифры и буквы — у каждого инструмента на панели быстрого доступа и у каждой вкладки на ленте соответственно:
Нажмите на клавиатуре любую из букв — попадете на соответствующую вкладку на ленте, а там каждый инструмент в свою очередь тоже будет подписан. Так можно быстро вызвать нужные опции, не трогая мышку.
Ввод данных
Теперь давайте рассмотрим несколько инструментов для быстрого ввода данных.
Автозамена
Если вам часто нужно вводить какое-то словосочетание, адрес, емейл и так далее — придумайте для него короткое обозначение и добавьте в список автозамены в Параметрах:
Прогрессия
Если нужно заполнить столбец или строку последовательностью чисел или дат, введите в ячейку первое значение и затем воспользуйтесь этим инструментом:
Протягивание
Представьте, что вам нужно извлечь какие-то данные из целого столбца или переписать их в другом виде (например, фамилию с инициалами вместо полных ФИО). Задайте Excel одну ячейку с образцом — что хотите получить:
Выделите все ячейки, которые хотите заполнить по образцу, — и нажмите Ctrl+E. И магия случится (ну, в большинстве случаев).
Проверка ошибок
Проверка данных позволяет избежать ошибок при вводе информации в ячейки.
Какие бывают типовые ошибки в Excel?
Инструмент проверки данных
Еще неверные данные можно обвести, чтобы точно увидеть, где есть ошибки:
Удаление пробелов
Для удаления лишних пробелов (в начале, в конце и всех кроме одного между слов) используйте функцию СЖПРОБЕЛЫ / TRIM. Ее единственный аргумент — текст (ссылка на ячейку с текстом, как правило).
Если после очистки данных функцией СЖПРОБЕЛЫ или другой обработки вам не нужен исходный столбец, вставьте данные, полученные в отдельном столбце с помощью функций, как значения на место исходных данных, а столбец с формулой удалите:
Дата и время
За любой датой в Excel скрывается целое число. Датой его делает формат.
Аналогично со временем: одна единица — это день, а часть единицы (число от 0 до 1) — время, то есть часть дня.
Это не значит, что так имеет смысл вводить даты и время в ячейки, вводите их в любом из стандартных форматов — Excel сразу отформатирует их как даты:
Вычесть из одной даты другую, чтобы получить разницу в днях (результатом вычитания будет число — количество дней.
Прибавить к дате число — и получить дату, которая наступит через соответствующее количество дней.
Поиск и подстановка значений
Функция ВПР / VLOOKUP
У нее есть два режима работы: интервальный просмотр и точный поиск.
Интервальный просмотр — это поиск интервала, в который попадает число. Если у вас прогрессивная шкала налога или скидок, нужно конвертировать оценку из одной системы в другую и так далее — используется именно этот режим. Для интервального просмотра нужно пропустить последний аргумент ВПР или задать его равным единице (или ИСТИНА).
Функции ПОИСКПОЗ / MATCH и ИНДЕКС / INDEX
В реальных условиях структура таблиц бывает разной и не всегда возможно изменить порядок столбцов. Поэтому важно уметь работать с любой структурой.
Функция ПОИСКПОЗ / MATCH определяет порядковый номер значения в диапазоне. Ее синтаксис:
=ПОИСКПОЗ (что ищем; где ищем ; 0)
На выходе — число (номер строки или столбца в рамках диапазона, в котором находится искомое значение).
ИНДЕКС / INDEX выполняет другую задачу — возвращает элемент по его номеру.
=ИНДЕКС(диапазон, из которого нужны данные; порядковый номер элемента)
Соответственно, мы можем определить номер строки, в котором находится искомое значение, с помощью ПОИСКПОЗ. А затем подставить этот номер в ИНДЕКС на место второго аргумента, чтобы получить данные из любого нужного нам столбца.
Получается следующая конструкция:
=ИНДЕКС(диапазон, из которого нужны данные; ПОИСКПОЗ (что ищем; где ищем ; 0))
Оформление
Microsoft Excel — очень мощный многоцелевой инструмент, который может использовать каждый. Но если вы работаете с электронными таблицами каждый день, вам может потребоваться знать больше, чем просто основы использования Excel. Знание нескольких простых трюков может иметь большое значение с Excel. Хорошим примером является знание того, как связать ячейки в Excel между листами и книгами.
Изучение этого сэкономит много времени и путаницы в долгосрочной перспективе.
Зачем связывать данные ячейки в Excel
Возможность ссылаться на данные на разных листах является ценным навыком по нескольким причинам.
Во-первых, это упростит организацию ваших электронных таблиц. Например, вы можете использовать один лист или рабочую книгу для сбора необработанных данных, а затем создать новую вкладку или новую рабочую книгу для отчетов и / или суммирования.
После того, как вы свяжете ячейки между ними, вам нужно всего лишь изменить или ввести новые данные в одну из них, а результаты автоматически изменятся в другой. Все без необходимости перемещаться между различными таблицами.
Во-вторых, этот прием позволит избежать дублирования одних и тех же чисел в нескольких таблицах. Это уменьшит ваше рабочее время и возможность совершать ошибки в расчетах.
В следующей статье вы узнаете, как связать отдельные ячейки в других листах, связать диапазон ячеек и как связать ячейки из разных документов Excel.
Как связать две отдельные клетки
Давайте начнем со связывания двух ячеек, расположенных на разных листах (или вкладках), но в одном файле Excel. Для этого выполните следующие действия.
- В Sheet2 введите символ равенства (=) в клетку.
- Перейти на другую вкладку (Лист1) и щелкните ячейку, на которую вы хотите сослаться.
- Нажмите Войти завершить формулу.
Теперь, если вы нажмете на ячейку в Sheet2вы увидите, что Excel записывает для вас путь в строке формул.
Например, = Лист1! C3, где Лист1 это имя листа, C3 является ли ячейка, на которую вы ссылаетесь, и восклицательный знак (!) используется как разделитель между ними.
Используя этот подход, вы можете связать вручную, не выходя из исходного листа вообще. Просто введите формулу ссылки прямо в ячейку.
Примечание. Если имя листа содержит пробелы (например, Лист 1), то при вводе ссылки в ячейку необходимо указать имя в одинарных кавычках. подобно = ’Лист 1 ′! C3, Поэтому иногда проще и надежнее позволить Excel написать для вас справочную формулу.
Как связать диапазон ячеек
Другой способ связать ячейки в Excel — связать целый ряд ячеек из разных вкладок Excel. Это полезно, когда вам нужно хранить одни и те же данные на разных листах, не редактируя оба листа.
Чтобы связать несколько ячеек в Excel, выполните следующие действия.
- В исходной вкладке с данными (Лист1), выделите ячейки, на которые вы хотите сослаться.
- Скопируйте ячейки (Ctrl/команда + С, или щелкните правой кнопкой мыши и выберите копия).
- Перейти на другую вкладку (Sheet2) и нажмите на ячейку (или ячейки), где вы хотите разместить ссылки.
- Щелкните правой кнопкой мыши по ячейке (ям) и выберите Специальная паста…
- В левом нижнем углу меню выберите Вставить ссылку,
При нажатии на новые связанные ячейки в Sheet2 вы можете увидеть ссылки на ячейки из Sheet1 на вкладке формулы. Теперь, когда вы меняете данные в выбранных ячейках в Sheet1, они автоматически изменяют данные в связанных ячейках в Sheet2.
Как связать ячейку с функцией
Связывание с кластером ячеек может быть полезно, когда вы выполняете суммирование и хотите хранить их на листе отдельно от исходных необработанных данных.
Допустим, вам нужно написать функцию SUM в Sheet2, которая будет связывать несколько ячеек из Sheet1. Для этого перейдите на Sheet2 и нажмите на ячейку, где вы хотите разместить функцию. Напишите функцию как обычно, но когда дело доходит до выбора диапазона ячеек, перейдите на другой лист и выделите их, как описано выше.
У вас будет = SUM (Лист1 C3: C7)где функция SUM суммирует содержимое ячеек C3: C7 в Sheet1. Нажмите Войти завершить формулу.
Как связать ячейки из разных файлов Excel
Процесс связывания между различными файлами Excel (или рабочими книгами) практически такой же, как указано выше. За исключением случаев, когда вы вставляете ячейки, вставьте их в другую электронную таблицу вместо другой вкладки. Вот как это сделать в 4 простых шага.
- Откройте оба документа Excel.
- Во втором файле (Мастер службы поддержки), выберите ячейку и введите символ равенства знак равно
- Переключиться на оригинальный файл (Интернет Tech Советы) и нажмите на ячейку, на которую хотите сослаться.
- Нажмите Войти завершить формулу.
Теперь формула для связанной ячейки также имеет другое имя книги в квадратных скобках.
Если вы закроете исходный файл Excel и снова посмотрите на формулу, вы увидите, что теперь он также имеет местоположение всего документа. Это означает, что если вы переместите исходный файл, который вы указали в другом месте, или переименуете его, ссылки перестанут работать. Вот почему надежнее хранить все важные данные в одном файле Excel.
Станьте профессиональным пользователем Microsoft Excel
Связывание ячеек между листами — это только один из примеров того, как вы можете фильтровать данные в Excel и организовывать свои электронные таблицы. Проверьте некоторые другие Советы и рекомендации по Excel что мы собрали вместе, чтобы помочь вам стать продвинутым пользователем.
Какие еще изящные лайфхаки Excel вы знаете и используете? Знаете ли вы какие-либо другие творческие способы связать ячейки в Excel? Поделитесь ими с нами в разделе комментариев ниже.
Читайте также: