Ссылка на ячейку в excel с копированием формата

Обновлено: 04.07.2024

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

Проблема в том, что если скопировать диапазон D2:D8 с формулами куда-нибудь в другое место на лист, то Microsoft Excel автоматически скорректирует ссылки в этих формулах, сдвинув их на новое место и перестав считать:

Задача: скопировать диапазон с формулами так, чтобы формулы не изменились и остались теми же самыми, сохранив результаты расчета.

Способ 1. Абсолютные ссылки

Как можно заметить по предыдущей картинке, Excel сдвигает только относительные ссылки. Абсолютная (со знаками $) ссылка на желтую ячейку $J$2 не сместилась. Поэтому для точного копирования формул можно временно перевести все ссылки во всех формулах в абсолютные. Нужно будет выделить каждую формулу в строке формул и нажать клавишу F4:

При большом количестве ячеек этот вариант, понятное дело, отпадает - слишком трудоемко.

Способ 2. Временная деактивация формул

Выделяем диапазон с формулами (в нашем примере D2:D8)
Жмем Ctrl+H на клавиатуре или на вкладке Главная - Найти и выделить - Заменить (Home - Find&Select - Replace)

Способ 3. Копирование через Блокнот

Этот способ существенно быстрее и проще.

Нажмите сочетание клавиш Ctrl+Ё или кнопку Показать формулы на вкладке Формулы (Formulas - Show formulas) , чтобы включить режим проверки формул - в ячейках вместо результатов начнут отображаться формулы, по которым они посчитаны:

Скопируйте наш диапазон D2:D8 и вставьте его в стандартный Блокнот:

Теперь выделите все вставленное (Ctrl+A), скопируйте в буфер еще раз (Ctrl+C) и вставьте на лист в нужное вам место:

Осталось только отжать кнопку Показать формулы (Show Formulas) , чтобы вернуть Excel в обычный режим.

Примечание: этот способ иногда дает сбой на сложных таблицах с объединенными ячейками, но в подавляющем большинстве случаев - работает отлично.

Способ 4. Макрос

Если подобное копирование формул без сдвига ссылок вам приходится делать часто, то имеет смысл использовать для этого макрос. Нажмите сочетание клавиш Alt+F11 или кнопку Visual Basic на вкладке Разработчик (Developer) , вставьте новый модуль через меню Insert - Module и скопируйте туда текст вот такого макроса:

Для запуска макроса можно воспользоваться кнопкой Макросы на вкладке Разработчик (Developer - Macros) или сочетанием клавиш Alt+F8. После запуска макрос попросит вас выделить диапазон с исходными формулами и диапазон вставки и произведет точное копирование формул автоматически:

Если вы хотите скопировать только диапазон значений и формата ячеек, а не копировать формулу в ячейки, как вы можете быстро ее скопировать? Следующие уловки расскажут, как это сделать.

Скопируйте данные с помощью Kutools for Excel

Скопируйте значения и все форматирование с помощью специальной вставки в Excel

В Excel копировать значения и форматировать можно только следующим образом:

1. Выберите диапазон, который вы хотите скопировать;

2. Нажмите Редактировать > Копировать, или щелкните правой кнопкой мыши и выберите Копировать;

3. Выберите ячейку или диапазон, в который вы хотите вставить значения и форматирование;

4. выберите Редактировать, Специальная вставка или щелкните правой кнопкой мыши и выберите Специальная вставкаи нажмите Значения и исходное форматирование, см. снимок экрана:

5. Затем в выбранную ячейку вставляются только значения и форматирование ячейки.

Скопируйте значения и все форматирование с помощью VBA

Запуск VBA позволяет быстро копировать значения и форматирование.

1. Удерживая кнопку ALT, нажмите F11 на клавиатуре, чтобы открыть Microsoft Visual Basic для приложений окно.

2. Нажмите INSERт> Модуль, и скопируйте VBA в модуль.

VBA: копировать значения и все форматирование

3. Нажмите Запустите или нажмите F5 для запуска VBA. И на экране отображается диалоговое окно, в котором вы должны выбрать ячейки диапазона, значения и форматирование которых вы хотите скопировать. Смотрите скриншот:

5. Нажмите Ok и другое диалоговое окно отображается для вас, чтобы выбрать ячейку для вставки результата. Смотрите скриншот:

6. Нажмите Ok, то значения и форматирование ячеек вставляются в выбранную ячейку. Смотрите скриншот:

Примечание: С помощью этого кода VBA вы также можете вставлять значения и форматирование в другие рабочие листы, которые вам нужны.

Скопируйте данные с помощью Kutools for Excel

Если вы просто хотите скопировать только значения, только форматирование или только значения и форматирование чисел, вы можете использовать Kutools for ExcelАвтора Копировать диапазоны.

После бесплатная установка Kutools for Excel, сделайте следующее:

1. Выберите данные и щелкните Kutools > Копировать диапазоны. А затем выберите нужный вариант.

2. Затем нажмите Ok, и выберите ячейку для вставки данных.

Вставить значения и числовые форматы

Вставить комментарии

Примечание: Если вы хотите сохранить высоту строки или ширину столбца, просто отметьте Iвключая высоту строки or Включая ширину столбца.

Если вы хотите скопировать только форматирование ячейки из диапазона, как это сделать быстро? В этом руководстве представлены быстрые сокращения, позволяющие легко копировать только форматирование.

Копировать форматирование только с помощью Kutools for Excel

Копирование форматирования только с помощью Format Painter в Excel

В Excel вы можете копировать форматирование ячеек только с помощью Формат Painter инструмент.

1. Выберите диапазон, содержащий формат ячеек, который вы хотите скопировать. Смотрите скриншот:

2. Применение Формат Painter щелкнув его под Главная вкладку, см. снимок экрана:

3. Выберите пустую ячейку и щелкните по ней, будет вставлено только форматирование ячеек. Смотрите скриншот:

Внимание: Формат Painter также можно копировать форматирование только на другой лист.

Копировать форматирование только с помощью VBA

Следующие макросы также могут помочь вам скопировать только форматирование ячеек.

1. Держать ALT и нажмите F11 на клавиатуре, чтобы открыть Microsoft Visual Basic для приложений окно.

2. Нажмите Вставить > Модуль, и скопируйте VBA в модуль.

VBA: копировать только форматирование ячеек:

3. Нажмите Запустите или нажмите F5 для запуска VBA. На экране отображается диалоговое окно, и вы должны выбрать ячейки, форматирование которых вы хотите скопировать. Смотрите скриншот:

4. Нажмите Ok и другое диалоговое окно отображается для вас, чтобы выбрать ячейку для вставки форматирования. Смотрите скриншот:

6. Нажмите Ok, то форматирование ячеек вставляется в выбранную ячейку. Смотрите скриншот:

Внимание: С помощью этого кода VBA вы можете вставлять только форматирование в другие рабочие листы, которые вы хотите.

Копировать форматирование только с помощью Kutools for Excel

Есть Kutools for Excel установлен, Копировать диапазоны Функция может помочь вам быстро и легко скопировать только форматирование ячеек.

Пожалуйста, примените Копировать диапазоны функция, нажав Kutools > Копировать диапазоны. Смотрите скриншот:

1. Выделите ячейки, форматирование которых вы хотите скопировать.

2. Нажмите Kutools > Копировать диапазонs, на экране появится диалог, отметьте Форматы вариант под Специальная вставка, см. снимок экрана:

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

4. Нажмите Ok, и в выбранную ячейку вставляется только форматирование. Смотрите скриншот:

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

Работы С Нами Kutools for Excel' Копировать диапазоны, вы также можете копировать только значения, формулы или комментарии.

Копировать форматирование только из диапазона

Наконечник.Если вы хотите подсчитать или суммировать ячейки на основе цветов, попробуйте использовать Kutools for Excel's Считать по цвету как показано на следующем снимке экрана. Полная функция без ограничений в течение 30 дней, пожалуйста, скачайте и получите бесплатную пробную версию сейчас.

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

Как вставить формулу в таблицу Excel и сохранить формат таблицы? Ведь при решении данной задачи можно экономить вагон времени. Для этого будем использовать функцию «Специальная вставка» – это простой инструмент, который позволяет быстро решить сложные задачи и освоить общие принципы его использования. Использование этого простого инструмента позволяет вам быстро приводить все таблицы к однообразному формату и получать тот результат, который необходим.

Как скопировать таблицу с шириной столбцов и высотой строк

Допустим, у нас есть такая таблица, формат которой необходимо сохранить при копировании:

При копировании на другой лист привычным методом Ctrl+C – Ctrl+V. Получаем нежелательный результат:

Поэтому приходится вручную «расширять» ее, чтобы привести в удобный вид. Если таблица большая, то «возиться» придется долго. Но есть способ существенно сократить временные потери.

Способ1:Используем специальную вставку

Выделяем исходную таблицу, которую нам необходимо скопировать, нажимаем на Ctrl+C.
Выделяем новую (уже скопированную) таблицу, куда нам необходимо формат ширины столбцов и нажимаем на ячейку правой кнопкой мыши, после чего в выпадающем меню находим раздел «Специальная вставка».
Выбираем в нем необходимый пункт напротив опции «ширина столбцов», нажимаем «ОК».

Она получила исходные параметры и выглядит идеально точно.

Способ 2: Выделяем столбцы перед копированием

Секрет данного способа в том, что если перед копированием таблицы выделить ее столбцы вместе с заголовками, то при вставке ширина каждого столбца будет так же скопирована.

Для каждого случая рационально применять свой способ. Но стоит отметить, что второй способ позволяет нам не только быстро переносить таблицу вместе с форматом, но и копировать высоту строк. Ведь в меню специальной вставки нет опции «высоту строк». Поэтому для решения такой задачи следует действовать так:

Полезный совет! Самый быстрый способ скопировать сложную и большую таблицу, сохранив ее ширину столбцов и высоту строк – это копировать ее целым листом. О том, как это сделать читайте: копирование и перемещение листов.

Вставка значений формул сохраняя формат таблицы

Специальная вставка хоть и не идеальна, все же не стоит недооценивать ее возможности. Например, как вставить значение формулы в таблицу Excel и сохранить формат ячеек.

Чтобы решить такую задачу следует выполнить 2 операции, используя специальную вставку в Excel.

Так как скопированный диапазон у нас еще находится в буфере обмена после копирования, то мы сразу еще раз вызываем специальную вставку где выбираем опцию «форматы». Жмем ОК.

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

Полезный совет! Чтобы не выполнять вторую операцию можно воспользоваться инструментом «формат по образцу».

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

Читайте также: