Отдельные ячейки таблиц Excel могут включать в себя не только обычные числа, текст или данные прочих форматов, но и результаты вычисления формул. При обычном копировании и последующей вставке таких значений копируется не только содержимое ячейки, но и формула, примененная к этим данным. В итоге скопированные числа после обычной вставки их в другое место будут иметь измененное значение. Однако, есть множество методов, позволяющих скопировать рассчитанные результаты работы формул без изменения их значений при вставке в другую таблицу или в другое место исходной таблицы.
Специальная вставка из главного меню
Сочетание клавиш Alt+E+S
Сочетание клавиш Ctrl +Alt+V
Сочетание клавиш Alt +H+V+V
Вставка из меню правой кнопкой мыши
Вставка из панели быстрого доступа
Перетаскивание значения ячейки мышью
Копирование при помощи параметров вставки
Копирование с помощью расширенных фильтров
Начнем с того, как задать сочетания клавиш макросу вручную: 1. На панели инструментов Visual Basic нажать кнопку ‘Выполнить макрос’ 2. Выбрать из списка интересующий Вас макрос 3. Нажать кнопку ‘Параметры’ 4. Ввести символ, поиграть с Shift-ом 5. Нажать Ok
Чтобы задать сочетания клавиш макросу программно из VBA, нужно: 1. Назначить, например, Ctrl-Shift-V на макрос PasteValues()
‘ Автозапуск Sub Auto_Open() Call SetOnKeys End Sub
2. Написать код макроса PasteValues, например, так:
‘ Новый обработчик для Ctrl-Shift-V = копирование значений / преобразование в значения Sub PasteValues() With Application If . CutCopyMode Then Selection. PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False Else Selection. Value = Selection. Value End If End With End Sub
В режиме копирования этот макрос копирует значения, а в обычном режиме – преобразовывает выделенные ячейки с формулами в значения.
3. Программно привязку к макросу отключается так:
— ZVI
Вставка или Ctrl+V, пожалуй, самый эффективный инструмент доступный нам. Но как хорошо вы владеете им? Знаете ли вы, что есть как минимум 14 различных способов вставки данных в листах Ecxel? Удивлены? Тогда читаем этот пост, чтобы стать пэйст-мастером.
Данный пост состоит из 2 частей:
— Основные приемы вставки
— Вставка с помощью обработки данных
Вставить значения
Если вы хотите просто вставить значения с ячеек, последовательно нажимайте клавиши Я, М и З, удерживая при этом клавишу Alt, и в конце нажмите клавишу ввода. Это бывает необходимо, когда вам нужно избавиться от форматирования и работать только с данными.
Начиная с Excel 2010, функция вставки значений отображается во всплывающем меню при нажатии правой клавишей мыши
Вставить форматы
Нравиться этот чудный формат, который сделал ваш коллега? Но у вас нет времени, чтобы так же оформить свою таблицу. Не беспокойтесь, вы можете вставить форматы (включая условное форматирование) из любой скопированной ячейки. Удерживая клавишу Alt, последовательно нажимайте Я, М, Ф, Ф, Ф и в конце нажмите клавишу Ввода.
Те же самые действия можно произвести с помощью меньшего количества операций, воспользовавшись меню, которое выпадает при нажатии правой кнопки мыши (начиная с Excel 2010).
Вставить формулы
Иногда возникает необходимость скопировать несколько формул в новый диапазон. Для этого, удерживая клавишу Alt, последовательно нажимаем Я, М, Ф и в конце нажмите клавишу Ввода. Вы можете достичь того же эффекта, путем перетаскивания ячейки, содержащей формулу, в новый диапазон, если диапазон находится рядом.
Вставить проверку данных
Вашему боссу понравилась, созданная вами, табличка по отслеживанию покупок и он попросил создать еще одну, для отслеживания продаж. В новой таблице вы хотите сохранить ширину столбцов. Для этого вам нет необходимости измерять каждый столбец первой таблицы, а просто скопировать их и с помощью специальной вставки задать «Ширина столбцов».
В этом нам помогут сочетания клавиш Ctrl+V или Alt+Я+М или клавиша вставки на панели инструментов.
Сочетание клавиш Alt+H+V+V
Данная команда позволяет использовать горячие клавиши, вызываемые с клавиатуры кнопкой Alt. При нажатии этой кнопки появляется лента со всевозможными горячими клавишами. Скопируйте нужные ячейки, установите курсор в нужном месте и затем наберите комбинацию Alt+H+V+V. После этого исходные значения ячеек будут вставлены в указанном месте. Нажатие этих клавиш активирует команду вставки неформатированных значений.
Преобразование формул в значения
Формулы – это хорошо. Они автоматически пересчитываются при любом изменении исходных данных, превращая Excel из “калькулятора-переростка” в мощную автоматизированную систему обработки поступающих данных. Они позволяют выполнять сложные вычисления с хитрой логикой и структурой. Но иногда возникают ситуации, когда лучше бы вместо формул в ячейках остались значения. Например:
В любой подобной ситуации можно легко удалить формулы, оставив в ячейках только их значения. Давайте рассмотрим несколько способов и ситуаций.
Способ 1. Классический
Этот способ прост, известен большинству пользователей и заключается в использовании специальной вставки:
Способ 2. Только клавишами без мыши
При некотором навыке, можно проделать всё вышеперечисленное вообще на касаясь мыши:
Способ 3. Только мышью без клавиш или Ловкость Рук
Этот способ требует определенной сноровки, но будет заметно быстрее предыдущего. Делаем следующее:
После небольшой тренировки делается такое действие очень легко и быстро. Главное, чтобы сосед под локоть не толкал и руки не дрожали 😉
Способ 4. Кнопка для вставки значений на Панели быстрого доступа
Ускорить специальную вставку можно, если добавить на панель быстрого доступа в левый верхний угол окна кнопку Вставить как значения. Для этого выберите Файл – Параметры – Панель быстрого доступа (File – Options – Customize Quick Access Toolbar). В открывшемся окне выберите Все команды в выпадающем списке, найдите кнопку Вставить значения и добавьте ее на панель:
Теперь после копирования ячеек с формулами будет достаточно нажать на эту кнопку на панели быстрого доступа:
Кроме того, по умолчанию всем кнопкам на этой панели присваивается сочетание клавиш Alt + цифра (нажимать последовательно). Если нажать на клавишу , то Excel подскажет цифру, которая за это отвечает:
Способ 5. Макросы для выделенного диапазона, целого листа или всей книги сразу
Если вас не пугает слово “макросы”, то это будет, пожалуй, самый быстрый способ.
Макрос для превращения всех формул в значения в выделенном диапазоне (или нескольких диапазонах, выделенных одновременно с Ctrl) выглядит так:
Sub Formulas_To_Values_Selection()
‘преобразование формул в значения в выделенном диапазоне(ах)
Dim smallrng As Range
For Each smallrng In Selection. Areas
smallrng. Value = smallrng. Value
Next smallrng
End Sub
Если вам нужно преобразовать в значения текущий лист, то макрос будет таким:
Sub Formulas_To_Values_Sheet()
‘преобразование формул в значения на текущем листе
ActiveSheet. UsedRange. Value = ActiveSheet. UsedRange. Value
End Sub
И, наконец, для превращения всех формул в книге на всех листах придется использовать вот такую конструкцию:
Sub Formulas_To_Values_Book()
‘преобразование формул в значения во всей книге
For Each ws In ActiveWorkbook. Worksheets
ws. UsedRange. Value = ws. UsedRange. Value
Next ws
End Sub
Код нужных макросов можно скопировать в новый модуль вашего файла (жмем + чтобы попасть в Visual Basic, далее Insert – Module). Запускать их потом можно через вкладку Разработчик – Макросы (Developer – Macros) или сочетанием клавиш +. Макросы будут работать в любой книге, пока открыт файл, где они хранятся. И помните, пожалуйста, о том, что действия выполненные макросом невозможно отменить – применяйте их с осторожностью.
Способ 6. Для ленивых
Если ломает делать все вышеперечисленное, то можно поступить еще проще – установить надстройку PLEX, где уже есть готовые макросы для конвертации формул в значения и делать все одним касанием мыши:
В этом случае:
Ссылки по теме
Этот метод является отличной альтернативой предыдущему способу. Он очень прост и удобен.
В некоторых случаях после нажатия клавиш Ctrl+Alt+V появляется окно «Специальная вставка». Выберите в этом окне в блоке «Вставить» опцию «Значения» и нажмите кнопку «ОК».
В результате этих действий значения ячеек будут скопированы и вставлены в нужное место. Причем, исходные формулы и форматирование не будут перенесены.
Копирование с помощью расширенных фильтров
Это довольно экзотический способ копирования значений ячеек и представлен здесь скорее как иллюстрация широких возможностей Microsoft Excel. Этот метод удаляет формулы, примечания и прочее, но оставляет неизменным форматирование копируемых ячеек.
В результате этих действий будет скопировано значение выбранных ячеек без формул. Исходное форматирование при необходимости можно удалить при помощи команды «Очистить форматы», которая расположена в главном меню Excel в блоке «Редактирование».
Как видите, существует немало способов копирования значений ячеек Excel. Возможно, существуют и другие методы решения этой задачи. Каждый пользователь выбирает тот способ, который ему максимально удобен.
При работе с отчетами, выполненными в MS Excel, не забывайте об обеспечении конфиденциальности важных документов. Офисные приложения позволяют достаточно надежно шифровать данные. Даже если вы потеряете шифр к таким документам, специальные программы помогут вам найти забытый пароль к Excel файлам.
Копирование при помощи параметров вставки
Это один из самых быстрых и удобных способов копирования значений ячеек Excel.
После этих действий в указанном месте таблицы будут помещены значения ячеек без формул и форматирования.
Вставка с помощью обработки данных
К примеру, у вас имеется строка 1 со значениями 1, 2, 3, и строка 2 со значениями 4, 5, 6. И вам необходимо сложить обе строки, чтобы получить 5, 7, 9. Для этого копируем первую строку, жмем правой кнопкой мыши по строке 2, выбираем Специальная вставка, ставим переключатель на «Сложить» и жмем ОК.
Те же самые операции необходимо будет проделать, если вам требуется вычесть, умножить или разделить данные. Отличием будет, установка переключателя на нужной нам операции.
Вставка с учетом пустых ячеек
Если у вас имеется диапазон ячеек, в котором присутствуют пустые ячейки и необходимо вставить их другой диапазон, но при этом, чтобы пустые ячейки были проигнорированы.
В диалоговом окне «Специальная вставка» установите галку «Пропускать пустые ячейки»
Транспонированная вставка
К примеру, у вас имеется колонка со списком значений, и вам требуется переместить (скопировать) данные в строку (т.е. вставить их поперек). Как бы вы это сделали? Ну конечно, вам следует воспользоваться специальной вставкой и в диалоговом окне установить галку «Транспонировать». Либо воспользоваться сочетанием клавиш Alt+Я, М и А.
Эта операция позволит транспонировать скопированные значения прежде, чем вставит. Таким образом, Excel преобразует строки в столбцы и, наоборот, столбцы в строки.
Вставить ссылку на оригинальную ячейку
Если вы хотите создать ссылки на оригинальные ячейки, вместо копипэйстинга значений, этот вариант, то, что вам нужно. Воспользуйтесь специальной вставкой, как примерах выше, и вместо кнопки «ОК» , нажмите «Вставить связь». Либо воспользуйтесь сочетанием клавиш Alt+Я, М и Ь, что создаст автоматическую ссылку на скопированный диапазон ячеек.
Вставить текст с разбивкой по столбцам
Есть еще много других скрытых способов вставки, таких как вставка XML-данных, изображений, объектов, файлов и т.д. Но мне интересно, какими интересными приемами вставки пользуетесь вы. Напишите, какой ваш любимый способ вставки?
Перетаскивание значения ячейки мышью
Этим неочевидным способом копирования ячеек пользуются лишь немногие пользователи. Однако, он достаточно прост и удобен для тех пользователей, которые предпочитают работать мышкой.
Выбранные значения будут скопированы в указанное место без исходного форматирования и формул после выполнения этих действий.
Вставка из меню правой кнопкой мыши
Во многих версиях MS Excel, начиная с версии 2007, скопировать значения ячеек без примененных к ним формул можно достаточно просто при помощи мыши.
После этих действий скопированные значения будут вставлены в новую ячейку.
Специальная вставка из главного меню
Если вы хотите просто скопировать рассчитанные значения и не копировать при этом формулы, примененные к этим числам, вы можете использовать команду «Специальная вставка» из Главного меню Excel. В этом случае значения ячеек будут скопированы без формул и без форматирования, примененного к данным ячейкам.
После выполнения всех манипуляций щелкните по одной из скопированных ячеек и убедитесь, что значения ячеек скопировались без формул.
Вставка из панели быстрого доступа
Если вы часто используете вставку скопированных значений ячеек в таблицы Excel, удобно было бы использовать специально назначенную кнопку на панели быстрого доступа. Достоинством этого метода является еще и то, что вы сможете использовать свое сочетание горячих клавиш для этой операции.
Что такое панель быстрого доступа? Эта панель представляет собой несколько значков, которые позволяют быстро получить доступ к часто используемым командам. Изначально на панели быстрого доступа расположены только четыре значка, но размещенные там команды можно подобрать в соответствии со своими потребностями.
Для того, чтобы вынести кнопку вставки значения на панель быстрого доступа, сделайте следующее.
После этих действий в панели быстрого доступа появится кнопка «Добавить значение». Теперь для того, чтобы вставить значение скопированной ячейки достаточно установить курсор в нужное место таблицы и нажать эту кнопку в панели быстрого доступа.
Можно также вставить скопированное значение, нажав сочетание клавиш Alt и порядкового номера этой кнопки в панели быстрого доступа. Например, если кнопка «Вставить значения» расположена пятой по счету на панели быстрого доступа, для вставки нужно нажать Alt+5.
Сочетание клавиш Alt+E+S
Этот способ является устаревшим и срабатывает не на всех компьютерах. Для его использования скопируйте нужные ячейки в буфер обмена, затем установите курсор в том месте, где будет вставлено содержимое ячеек. Наберите на клавиатуре сочетание клавиш Alt+E+S. После этого может появиться сообщение о том, что вы используете сочетание клавиш из более ранних версий Microsoft Office. Затем откроется Меню «Специальная вставка». Нажмите клавишу V и выберите нужные значения.
Пожалуй, единственным достоинством данного метода является то, что вы можете вызвать меню специальной вставки одной рукой. Не следует расстраиваться, если этот метод не работает на вашем компьютере. Существует много других способов решения данной задачи.
