Excel: Ссылки на ячейки и книги
Часто при работе с регулярно меняющимися данными, мы вынуждены вручную менять их значения во многих таблицах (отчетах), где они используются для расчета. В такой ситуации ссылки в Excel помогут значительно упростить работу с данными.
К примеру, можно разместить данные о ценах на товары на одном листе, а во всех расчетных таблицах прописать ссылки на ячейки листа с ценами. При изменении цены, необходимо будет изменить данные только на листе цен, на всех других листах, где имеются ссылки, изменение пройдет автоматически.
I. Чтобы создать ссылку на ячейку из другого листа той же книги необходимо:
1. Выделить ячейку, в которую необходимо вставить ссылку.
2. Вставить знак «=»
3. Перейти, с помощью вкладок листов, на лист, из которого будем брать данные.
4. Выделить ячейку с необходимым значением.
Таким образом, мы получим ссылку на ячейку из Листа 2, и в исходной ячейке отобразится нужное значение.
То же самое можно было получить, если вручную ввести в исходную ячейку формулу «=Лист2!В2».
Для заполнения остальных ячеек таблицы, можно протянуть формулу с помощью маркера заполнения,
Теперь при изменении цен на Листе 2 , автоматически будут меняться и значения цен в таблице «Объем продаж».
Если необходимо вставить ссылку на ячейку, находящуюся в текущей книге, но на другом листе, нужно в строке формул ввести ссылку в виде:
= Имя листа ! Адрес ячейки
II. Аналогично можно создать ссылку на ячейку из другой книги.
Первый способ — в ячейку первой книги вносим знак «=», переходим ко второй книге, где выбираем нужную ячейку. Далее нажимаем Enter .
Второй способ — вручную прописать ссылку в ячейку.
Пример ссылки, если книги находятся в одном каталоге: =[Книга1.xlsx]Лист1!A1
=[ Имя книги ] Имя листа! Адрес ячейки
Если книга, на которую вы ссылаетесь, расположена в другом каталоге, то необходимо указать полный путь доступа к ней непосредственно в формуле.
Пример ссылки на ячейку в книге, находящейся в каталоге C:\Мои документы:
=C:\ Мои документы \ [Книга1.xlsx]Лист1!A1
Как добавить данные в ячейку в excel
В приведённых ниже примерах нельзя составить формулу в первой ячейке и протянуть, так как значения остаются внесёнными от руки и в каждой ячейке они разные. Также есть причины, по которым некоторые пользователи не хотят делать расчёты рядом с таблицей.
Почему? Подозреваю, что две таблицы на связях не нужны, но есть необходимость пересчитать данные в таблице и сохранить историю пересчёта, а это возможно только если в ячейке будет формула.
Желание пользователя – закон )) Смотрите ниже подробное описание, пробуйте и пусть всё получится.
Задача
В ячейках просто числа и к ним нужно подставить формулу, умножающую эти числа на конкретное значение либо на ячейку.
Вот стартовая таблица:
А мы хотим, чтобы данные в ячейках выглядели и были посчитаны вот так:
Решение
Самое простое решение состоит в том, чтобы рядом с таблицей в любой ячейке написать =1,2 или ссылку на ячейку с корректирующим числом (в примере =$G$1), скопировать эту вспомогательную ячейку, выделить диапазон таблицы, которые нужно пересчитать, нажать CTRL+ALT+V и в окне специальной вставки выбрать переключатель умножить.
Получите результат, как на скрине ниже. Ячейку справа от таблицы можно почистить, таблица не пострадает.
На курсах я часто слышу вопрос, как сделать наоборот. Например, деление некоторого числа или ячейки на те значения, которые уже есть в таблице? Буду рада узнать, что за бизнес-ситуация требует такого решения, но ловите решение, это тоже возможно.
Решение наоборот
Без временных вспомогательных ячеек не обойтись. Справа от таблицы соберём нужный текст формулы с помощью знака амперсанд & и растянем формулу на нужное количество ячеек (в примере C5:C16):
Теперь скопируем получившиеся тексты формул (в примере из ячеек C5:C16) и вставим значениями вместо исходных данных таблицы (в примере начиная с ячейки B5). После вставки получится так:
Диапазон вспомогательных формул справа от таблицы можно удалить. И останется превратить данные в таблице в формулы. Сейчас это просто текст, так как мы использовали знак &. Смена формата ничем не поможет. Нужно заставить Excel обновить данные ячеек и тогда он сам превратит их в формулы. Поможет в этом окно поиска и замены.
Выбираем ячейки C5:C16, нажимаем CTRL+F, на вкладке Заменить в поле Найти вписываем знак равно, и в поле Заменить на вписываем знак равно, жмём Заменить все, немного ждём. Готово.
А если уже формула?
В ячейке уже написана формула. И в неё нужно подставить ещё некоторые расчёты. Исходная таблица с формулами:
Выбираем диапазон данных для подстановки формулы и для начала убираем из ячеек знак равно, используя окно поиска и замены CTRL+F. Получится вот так:
А дальше работаем по сценарию, который я описала выше. То есть справа от таблицы составляем текст будущей формулы.
Потом копируем результаты значениями на место исходных данных таблицы. И можно сразу после вставки (так как нужный диапазон уже выбран) через CTRL+F обновить данные, а потом и ставшие лишними формулы справа от таблицы удалить
Честно – в большинстве случаев я бы оставила исходные данные, как есть, а формулу написала бы в свободных ячейках. Исходник можно и скрыть, и сгруппировать. Но если всё же нужно именно формулу дописать в ячейках, где уже есть числовые данные или другие формулы, то вы теперь знаете, как это сделать.
Подробные разборки со знаком & и все полезные настройки окна поиска и замены поясняю на курсе Базовые навыки.
Вас ждёт несколько десятков простых способов решения типовых рабочих задач. Подключайтесь к обучению. Всё решаемо. Кнопка с программой и условиями курса чуть ниже.
Как добавить ячейки в таблицу Excel
Вставлять строки и колонки в Excel очень удобно при форматировании таблиц и листов. Но еще больше расширяют возможности программы функция вставки ячеек и целых диапазонов как смежных, так и несмежных.
Рассмотрим на практических примерах как добавить (или удалить) ячейки в таблицу в Excel и их диапазонов на листе. По сути ячейки не добавляются а просто значение одних перемещаются на другие. Данный факт следует учитывать когда лист заполнен более чем на 50%. Тогда ячеек для строк или столбцлов может просто не хватать и данная операция будет удалять данные. В таком случаи рационально содержание одного листа разбить на 2 или 3. Это одна из главных причин почему в новых версиях Excel добавлено больше количество столбцов и строк (их 65 000 строк в старых версиях до 1 000 000 в новых).
Вставка диапазона пустых ячеек
Как вставить ячейки в таблице Excel? Допустим, у нас есть таблица чисел, в которой необходимо вставить две пустые ячейки посередине.
Выполняем следующий порядок действий:
- Выделяем диапазон в том месте, где должны добавиться новые пустые блоки. Переходим на закладку «Главная»-«Вставить»-«Вставить ячейки». Или просто кликаем по выделенной области правой кнопкой мышки и выбираем опцию вставить. Или нажмите комбинацию горячих клавиш CTRL+SHIFT+ «плюс».
- Появится диалоговое окно «Добавление ячеек» где нужно задать необходимые параметры. В данном случае выберем опцию «со сдвигом вниз».
- Нажимаем ОК. После чего в таблицу чисел добавятся новые ячейки, а старые сохранят свои значения и сместятся вниз, уступив свое место.
В данной ситуации можно просто нажать инструмент «Главная»-«Вставить» (без выбора опций). Тогда новые ячейки вставятся, а старые сместятся вниз (по умолчанию), без вызова диалогового окна параметров.
Чтобы добавить ячейки в Excel используйте горячие клавиши CTRL+SHIFT+«плюс» предварительно выделив их.
Примечание. Обратите внимание на диалоговое окно параметров. Два последних параметра позволяют нам вставлять строки и столбцы таким же способом.
Удаление ячеек
Теперь из нашей таблицы чисел удалим этот же диапазон. Просто выделяем нужный диапазон. Кликните правой кнопкой мышки по выделенному диапазону и выбираем «Удалить». Или идем в закладку «Главная»-«Удалить»-«со сдвигом вверх». Результат обратно пропорциональный предыдущему результату.
Чтобы удалить ячейки в Excel используйте горячие клавиши CTRL+«минус» предварительно выделив их.
Примечание. Таким же образом можно удалять строки и столбцы.
Внимание! На практике использование инструментов «Вставить» или «Удалить» при вставке или удалении диапазонов без окна параметров лучше не использовать , чтобы не запутаться в больших и сложных таблицах. Если хотите экономить время, пользуйтесь горячими клавишами. Они вызывают диалоговое окно параметров вставки и удаления, позволяют быстрее справиться с задачей в любом случае.
- Excel Formula Examples
- Создать таблицу
- Форматирование
- Функции Excel
- Формулы и диапазоны
- Фильтр и сортировка
- Диаграммы и графики
- Сводные таблицы
- Печать документов
- Базы данных и XML
- Возможности Excel
- Настройки параметры
- Уроки Excel
- Макросы VBA
- Скачать примеры
Добавляйте текст в начало, в конец или на другую позицию в ячейках Excel
Когда вам нужно вставить некий текст в каждую ячейку Excel, создавать функции или писать VBA может занять довольно много времени. Ещё сложнее, если, к примеру, вам нужно добавить текст в конкретную позицию в ячейке или до конкретного текста.
Инструмент «Добавить знаки» поможет добавить любую текстовую строку (буквы, числа, пробелы, запятые или любые другие знаки) в ячейки за секунды:
Добавить в начало или в конец каждой ячейки
Добавить в середину ячеек после указанного числа знаков
Добавить до или после заданного текста
Перед началом работы добавьте «Добавить знаки» в Excel
«Добавить знаки» – это один из 20+ инструментов в составе надстройки XLTools для Excel. Работает в Excel 2019, 2016, 2013, 2010, десктоп Office 365.
Скачать XLTools для Excel
– пробный период дает 14 дней полного доступа ко всем инструментам.
Как добавить знаки в начало ячеек
Нажмите кнопку Очистка данных на панели XLTools Вы выпадающем списке выберите Добавить знаки Откроется диалоговое окно.
Выберите диапазон, в котором вы хотите добавить знаки.
Совет: кликните по любой ячейке таблицы, и вся таблица будет выбрана автоматически.
Введите текст, который нужно вставить.
Выберите позицию в ячейке: в начало .
Нажмите ОК Готово, текст добавлен в начало каждой ячейки.
Как добавить знаки в конец ячеек
Нажмите кнопку Очистка данных на панели XLTools Вы выпадающем списке выберите Добавить знаки Откроется диалоговое окно.
Выберите диапазон, в котором вы хотите добавить знаки.
Совет: кликните по любой ячейке таблицы, и вся таблица будет выбрана автоматически.
Введите текст, который нужно вставить.
Выберите позицию в ячейке: в конец .
Нажмите ОК Готово, текст добавлен в конец каждой ячейки.
Как добавить знаки в середину ячеек
Нажмите кнопку Очистка данных на панели XLTools Вы выпадающем списке выберите Добавить знаки Откроется диалоговое окно.
Выберите диапазон, в котором вы хотите добавить знаки.
Совет: кликните по любой ячейке таблицы, и вся таблица будет выбрана автоматически.
Введите текст, который нужно вставить.
Выберите позицию в ячейке: после знака номер N Укажите число знаков от начала, куда нужно вставить текст.
Нажмите ОК Готово, текст добавлен в середину каждой ячейки.
Как добавить знаки до или после указанного текста в ячейках
Нажмите кнопку Очистка данных на панели XLTools Вы выпадающем списке выберите Добавить знаки Откроется диалоговое окно.
Выберите диапазон, в котором вы хотите добавить знаки.
Совет: кликните по любой ячейке таблицы, и вся таблица будет выбрана автоматически.
Введите текст, который нужно вставить.
Выберите позицию в ячейке: перед текстом или после текста Введите текст, который нужно найти (по умолчанию с учётом регистра).
Нажмите ОК Готово, текст добавлен перед или после заданного текста в те ячейки, где он был найден.
Задайте параметры вставки знаков в ячейки
По умолчанию инструмент «Добавить знаки» не обрабатывает ячейки с формулами, чтобы не повлиять на расчёты. Вы можете задать дополнительные параметры обработки данных:
Отметьте флажком Пропустить пустые ячейки Отметьте эту опцию, если вы не хотите добавлять знаки в пустые ячейки.
Отметьте флажком Пропустить нетекстовые ячейки Отметьте эту опцию, если вы хотите обработать только ячейки с текстом. Все ячейки с числовым, денежным форматом, форматом даты или другим форматом будут пропущены.
Отметьте флажком Пропустить строку заголовка Отметьте эту опцию, если вы не хотите обрабатывать первую строку с заголовком таблицы.