Что такое специальная вставка в excel. Специальная вставка в Excel: значения, форматы, ширина столбцов

Я часто сожалел, что в MS Excel нет такого удобного сочетания клавиш для вставки значения, как сочетания Ctrl+Shift+V , которое используется в Calc . Как ни странно, но в Excel действительно нет НИКАКИХ штатных и работающих по умолчанию сочетаний клавиш для вставки значений. Однако альтернативные способы есть. Ниже мы их рассмотрим.

Способ первый , самый известный, самый распространённый и... самый времязатратный.

В ячейке А1 находится формула, которая суммирует значения ячеек В1 (1) и С1 (2). Вместо неё мы хотим вставить результат её работы (3). Что мы для этого делаем?

1. Выделяем ячейку А1
2. Правой кнопкой мыши вызываем контекстное меню
3. В меню выбираем пункт Копировать

4. Опять правой кнопкой мыши вызываем контекстное меню (потому что оно пропадает после выбора любой команды из него)
5. Выбираем пункт Специальная вставка

6. В появившемся окне выбираем значения

7. Жмём ОК

Всё, теперь у нас вместо формулы =В1+С1 значение 3

Не кажется ли Вам, что семь шагов для такого простого действия, как вставка значения, многовато?

Способ второй (работает во всех версиях Excel) , малоизвестный, но выполняется гораздо быстрее.

1. Выделяем ячейку А1

2. Мышью наводим на нижнюю границу ячейки, пока не получим курсор в виде крестика, каждый из лучей которого, увенчан стрелочками
3. Нажимаем правую кнопку мыши и перетаскиваем ячейку куда-нибудь (достаточно даже в соседнюю) и возвращаем назад
4. Отпускаем правую кнопку мыши и в появившемся меню выбираем пункт Копировать только значения

Готово. Но и это долго, к тому же не подходит тем кто привык пользоваться "горячими клавишами", а не мышкой.

Способ третий (работает в версиях Excel, вышедших после Excel 2003 ) , неизвестный (в интернете я его не нашел), но выполняется ещё быстрее, хотя и требует разовой предварительной настройки Excel.

Итак, сначала добавим кнопку вставки значений на панель быстрого доступа (ПБД).

1. Нажимаем на кнопку настройки ПБД и из появившегося списка выбираем пункт Другие команды...

2. В меню Выбрать команды из: выбираем пункт Все команды и из появившегося списка выбираем иконку помеченную как Вставить значения .

3. Жмём кнопку Добавить и перемещаем появившуюся в списке справа иконку вверх списка.

4. На ПБД появилась наша кнопка

5. Нажимаем Alt и видим номер нашей кнопки (в данном случае это номер 1)

Всё, настройка закончена.

А как теперь пользоваться этой кнопкой?

Можно по прямому назначению:
1. Выделяем ячейку А1
2. Копируем (можно мышью, можно сочетанием клавиш)
3. Нажимаем кнопку Вставить значения

Но помните, я говорил о том что можно обойтись без мыши? Тогда процесс будет выглядеть так:
1. Выделяем ячейку А1
2. Ctrl+C
3. Alt+1 , где 1 - это номер нашей кнопки на ПБД.

Быстро? Удобно? Думаю любители хоткеев оценят этот способ:)

Способ четвёртый , макросом.

Вставьте этот код в стандартный модуль личной книги макросов и при запуске Excel Вам всегда будет доступна быстрая вставка значений назначенным сочетанием клавиш (в примере это сочетание Ctrl+Shift+V ).

Sub CopyPasteValue()
Selection.PasteSpecial Paste:=xlPasteValues
End Sub

У этого способа есть два недостатка:
1. После применения макроса стек истории изменений очищается и отмена последних действий становится невозможна.
2. Макросы должны быть разрешены.

Способ пятый , самый экзотический и самый... бесполезный Я упоминаю о нём только для полноты картины, ни в коем случае не претендуя на то что кто-то им будет пользоваться.

Этот способ основан на том, что при обмене данными между приложениями формулы не сохраняются. Соответственно, если Вы будете копировать данные из Excel одной версии и вставлять в другую, то вставка будет производится значениями а не формулами. На видео я копирую формулу из Excel 2010 в Excel 2003 и наоборот. И в том и в том случае вставляется значение.

А как изменить данные под нужные условия из буфера обмена перед вставкой в ячейку? Для этого применяется инструмент специальная вставка в Excel. Т.е. если вам нужно вставить данные без форматирования, или вставить только формулы, то инструмент для вас. Функция крайне полезна — настоятельно рекомендую присмотреться.

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

Если вы скопировали данные с расчетами и хотите вставить только значения этих данных, уже без формул. Сделайте — Копировать нужные ячейки — выберите ячейку/диапазон для вставки — кликаем правой кнопкой мыши и выбираем Специальную вставку () .

Затем появиться таблица с картинки номер 1. Выбираем галочку «только значения».

Это был пример, а теперь разберем все предлагаемые варианты.

Вариант «Вставить:»

-формулы -значения -форматы -примечания

Здесь вроде ясно, можно вставить отдельно то, что содержится в ячейке. Вставляли значения мы в примере выше.

— вставить условия на значения. Понимаем буквально, поверх значений вставляем условия. Это либо проверка данных () или .

Т.е. если нужно наложить дополнительные условия.

— вставить с исходной темой. Тема в Excel это формат всей книги или листа. Применяется для единообразной цветой схемы или форматов диаграмм. Если для этого листа применена одна тема, а вставить нужно другую. применяется этот пункт. Честно говоря, я пользуюсь редко этой функцией.

— вставить без рамки . Вставляем без формата границ

— вставить ширины столбцов . Вставляем форматирование Ширина столбца.

— формулы и форматы чисел. Вставляются и формулы и форматы

— значения и форматы чисел. Очень удобно. Вставляется не только значения, но и форматы чисел.

Раздел Операция (сложить, вычесть, разделить, умножить)

Классная штука! Например, у вас есть два диапазона

А вам нужно разделить данные из столбца D на данные из столбца B (см. картинку). Без формул. Здесь нам и пригождается Специальная вставка в Excel.

Выделяем диапазон B3:B5. Копируем. Выделяем диапазон D3:D5. Нажимаем Специальная вставка. И отмечаем галочку «разделить»

Вуаля. Попробуйте сами — понравится.

Напоследок три бомбы специальной вставки

Гарантирую эти возможности сохранят вам кучу времени

Word для Office 365 Outlook для Office 365 PowerPoint для Office 365 Access для Office 365 Word 2019 Outlook 2019 PowerPoint 2019 Access 2019 Word 2016 Outlook 2016 PowerPoint 2016 Access 2016 Visio 2013 Word 2013 Outlook 2013 PowerPoint 2013 Access 2013 Word 2010 Outlook 2010 PowerPoint 2010 Access 2010 Visio 2010 Word 2007 Outlook 2007 PowerPoint 2007 Access 2007 Word Starter 2010 Меньше

Вы можете задать форматирование при вставке слайдов, текста, рисунков, объектов и таблиц из других программ или Интернета в приложения Office, например Access, Word, PowerPoint, Excel и Outlook.

С помощью специальной вставки

Как рисунки и другие объекты, текст в презентации с собственный форматированием - например, размер шрифта, цвета и шрифта. При копировании текста, который имеет разное форматирование в приложении Office, программы, например PowerPoint или Word, автоматически изменяет формат этот текст в соответствии с текстом назначения. Тем не менее воспользуйтесь Специальная вставка сохранить исходное форматирование и вставить их в виде ссылки или изображения, например.

Вставка

Чтобы задать формат для вставленного слайда, текста или объекта, нажмите кнопку Вставьте , а затем выберите одно из значений, указанных в списке Формат ниже.

Формат

Дата и время:

Форматированный текст
Неформатированный текст	Вы хотите использовать форматирование программы Office, в которую вставляется текст.
Графический объект Microsoft Office	Вы хотите, чтобы содержимое буфера обмена выводилось как объект-рисунок Microsoft Office.
Картинка (GIF)	Вы хотите, чтобы содержимое буфера обмена выводилось как рисунок GIF. В формате файлов GIF поддерживается только 256 цветов, поэтому он больше подходит для отсканированных изображений, например иллюстраций, и меньше для цветных фотографий. Формат GIF также хорошо подходит для чертежей, черно-белых изображений, мелкого текста и анимаций.
Рисунок (JPEG)	Вы хотите, чтобы содержимое буфера обмена выводилось как рисунок JPEG. Формат файлов JPEG поддерживает 16 млн цветов и лучше всего подходит для фотографий и сложной графики
Рисунок (PNG)	Вы хотите, чтобы содержимое буфера обмена выводилось как рисунок PNG. Формат файлов PNG похож на GIF, но обеспечивает лучшую поддержку цвета. PNG сжимает сплошные участки цвета при сохранении четкости деталей в набросках, логотипах или иллюстраций с текстом. Сохранение, восстановление и повторное сохранение изображения PNG происходят без потери качества. В отличие от GIF, формат PNG не поддерживает анимацию. Кроме того, некоторые устаревшие браузеры и приложения не поддерживают PNG.
Рисунок (метафайл Windows)	Вы хотите, чтобы содержимое буфера обмена выводилось как рисунок WMF.
Рисунок (расширенный метафайл)	Вы хотите, чтобы содержимое буфера обмена выводилось в формате EMF.
Рисунок (DIB)	Вы хотите, чтобы содержимое буфера обмена выводилось в формате DIB. Например, вы можете сохранить слайд как графический объект, чтобы использовать его на веб-страницах. Формат DIB - это представление графического объекта в памяти компьютера в виде строк и столбцов точек. Значение каждой точки (заполнена оно или нет) записывается в одном или нескольких битах данных.
Точечный рисунок	Вы хотите, чтобы содержимое буфера обмена выводилось как точечный рисунок. Формат Microsoft Windows BMP может отображать миллионы цветов. Так как он поддерживается многими программами, рекомендуется использовать его, если у человека, которому вы предоставляете рисунок, может не быть приложения, в котором вы создали изображение.

Вставить связь

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

Чтобы значок приложения OLE вместо фактического содержимого, установите флажок В виде значка . Вы можете щелкнуть значок, чтобы открыть приложение и просмотреть содержимое. Флажок В виде значка доступен только при использовании параметра Связать или при вставке содержимого в качестве объекта OLE. Вы можете изменить значок для вставленного содержимого.

В статье описываются возможности использования буфера обмена Windows и Microsoft Office, а также особенности копирования и вставки данных в Excel. Понимание и правильное использование этих операций позволяет существенно ускорить выполнение рутинных операций при обработке данных.

Буфер обмена операционной системы

В терминах информационных систем буфер обмена (англ. clipboard ) - это общедоступная для разных приложений область оперативной памяти. Операционная система предоставляет низкоуровневый программный интерфейс для перемещение данных в и из буфера обмена по запросу пользователя. Корректное применение этого программного интерфейса является стандартом при разработке Windows-приложений. То есть, любая программа должна предоставлять пользователю возможности использования буфера обмена при использовании одних и тех же сочетаний клавиш или пунктов меню. Далее будем говорить о Windows, хотя, в принципе, описание принципа работы буфера обмена идентично для любой современной операционной системы персональных компьютеров или мобильных устройств.

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

• Ctrl+c – скопировать (англ. copy )
• Ctrl+x – вырезать (англ. cut )
• Ctrl+v – вставить (англ. paste )

Часто также упоминаются аналогичные по функциональности сочетания клавиш: Ctrl+Ins – скопировать, Shift+Ins – вставить, Shift+Del – вырезать. Однако, мы не рекомендуем использовать эти сочетания, так как некоторые приложения заменяют их стандартное поведение на другое. Например, нажатие Shift+Del в Проводнике Windows вместо ожидаемого вырезания перемещаемого файла вызовет его удаление в обход корзины. То есть вместо перемещения может случиться безвозвратная потеря данных.

Если вы предпочитаете использовать мышь вместо клавиатуры, то стандартные поля ввода Windows-приложений обычно поддерживают контекстное меню с операциями копирования, вырезания и вставки текста.

Скопированный текст или другой блок данных может быть вставлен в другое приложение, в зависимости от возможностей последнего. Например, скопированный в Блокноте текст не получится затем вставить в графический редактор Paint. Однако же, тот же текст, набранный в Word, успешно вставляется в Paint в виде точечного рисунка. Такая возможность реализуется на программном уровне за счет перемещения данных в буфер обмена в нескольких форматах одновременно. Если набрать в Word полужирным шрифтом слово Example, затем его скопировать, то в буфере обмена появится несколько блоков информации:

Example	Текст как набор символов без форматирования
	Текст с форматированием в формате HTML
{\rtf1\ansi\ansicpg1252\uc1 {\b Example}{\par }}	Текст с форматированием в формате RTF
	Точечный рисунок блока экрана

Теперь, если попытаться вставить данные в Блокнот, то программа выберет из буфера обмена единственный доступный для себя вариант информации – текст без форматирования. Если то же самое сделать в Paint’е, то будет обработана последняя область – рисунок. Набор доступных форматов для копирования и вставки зависит от возможностей конкретной программы. Если приложение поддерживает несколько форматов информации (рисунки, текст, сложные объекты), то оно позволяет выбрать вариант вставки. Например, в Microsoft Word эта процедура реализована через пункт меню Специальная вставка :

Если использовать обычную вставку данных, то автоматически будет выбираться самый подходящий для этой программы формат. Excel также поддерживает операцию вставки данных в различных форматах по принципу Word, если информация в буфер обмена попала из другого приложения. Если же копирование диапазона ячеек было проведено в том же приложении, то специальная вставка заменяется внутренней операцией Excel (раздельная вставка значений, формул, форматов и пр.), при которой не задействуется буфер обмена операционной системы.

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

Буфер обмена Microsoft Office

Как уже отмечалось выше, за операции со стандартным буфером обмена отвечает операционная система. Одной из задач при этом является корректное использование оперативной памяти. Операционная система, в частности, заботится о своевременной очистке области буфера обмена. В текущей реализации стандартный буфер обмена Windows позволяет хранить только один блок скопированной информации. При вызове процедуры нового копирования этот блок предварительно очищается, а зарезервированная за ним область памяти становится доступной для использования в качестве буфера обмена.

Для улучшения возможностей работы с пользовательским интерфейсом в Microsoft Office, начиная с версии 2000 (9.0), реализован расширенный буфер обмена с возможностью одновременного хранения нескольких (до 24х) скопированных блоков информации. Пользователю предоставляется интерфейс выбора и вставки любого из этих блоков в любое открытое приложение Office (Excel, Word, PowerPoint, OneNote и др.). Возможно, более логично было бы реализовать подобную функциональность на уровне операционной системы (Windows), хотя это и потребует изменения стандартов для всех приложений. Сейчас получается, что множественный буфер обмена работает до тех пор, пока открыто хотя бы одно приложение Office. Если оно закрывается, то становится доступным только буфер обмена Windows с единственным блоком скопированной информации.

Интерфейс множественного буфера обмена в Office 2010 открывается и настраивается на ленте «Главная» в одноименном блоке (стрелка в нижнем правом углу).

Если говорить о полезности и удобстве работы с множественным буфером обмена, то здесь имеются различные мнения. Я лично никогда не использую эту функциональную возможность – проще еще раз скопировать. Но, скорее всего, это сила привычки.

Копирование и вставка в Excel

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

Здесь следует заметить, что повторное использование объектов через копирование и вставку является одним из определяющих факторов ускорения обработки информации при использовании электронных таблиц Excel.

Что же в действительности происходит в Excel при нажатии кнопки «копировать» при выделении диапазона ячеек?

Во-первых, как и в прочих Windows-приложениях, набор информации помещается в буфер обмена операционной системы в нескольких форматах: простой текст, форматированный текст, точечный рисунок и др. Таким образом, вы, например, можете воспользоваться графическим редактором и вставить туда экранное отображение блока выделенных ячеек. Если вставить этот же блок обратно в Excel, то вставится рисунок:

Во-вторых (и это главное), при копировании Excel выполняет внутреннюю операцию для работы с ячейками электронной таблицы. По нажатию сочетания клавиш Ctrl+C, пункта контекстного меню либо кнопки копирования в памяти сохраняются ссылки на выделенные ячейки. Этих ячеек может быть огромное количество. Они могут располагаться одном прямоугольном диапазоне, либо в нескольких несвязанных диапазонах (для выделения таких диапазонов надо при выделении мышью удерживать клавишу Ctrl). Теоретически имеется возможность копирования ячеек на разных листах (несколько листов можно выделять также через удержание клавиши Ctrl на ярлыке листа), но эти ячейки должны располагаться по одному и тому же адресу, при этом последующая вставка возможна также только на этих же выделенных листах. На практике лучше отказаться от копирования-вставки на нескольких листах одновременно, так как эта операция не очень наглядна и часто приводит к потере данных.

Доступно также копирование ссылок между разными, но открытыми в одном приложении Excel, файлами. Типичной ситуацией, вызывающей непонимание со стороны пользователя, является обработка данных в нескольких одновременно открытых приложениях Excel. При попытке скопировать данные из одного файла в другой программа вставляет результат только в виде отформатированных значений без формул. Это не ошибка, просто несколько одновременно открытых программ Excel занимают различные области памяти и никаких ссылок между ними быть не может. При копировании и вставке в этом случае используется только буфер обмена Office. Для исправления ситуации откройте файлы в одном приложении Excel.

Еще раз обращаем внимание, что при запуске операции копирования, в память программы записываются не данные (текст, формулы, форматы), а только ссылки на адреса выделенных ячеек. Для наглядности интерфейс Excel обводит скопированные ячейки анимированной рамкой.

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

Вставка доступна до тех пор, пока пользователь не произвел действий, приводящих к изменению данных электронной таблицы. Можно выделять ячейки и диапазоны, перемещаться между листами и файлами. Также не отменяет область копирования сама операция вставки. Это позволяет копировать ячейки несколько раз подряд для разных диапазонов. Любые другие операции с пользовательским интерфейсом, например, ввод данных, группировка, сортировка, форматирование, приводят к сбросу скопированной ранее ссылки. Принудительно сбросить область копирования можно по нажатию клавиши Esc.

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

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

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

По умолчанию при вызове операции вставки на выделенный диапазон будут распространены все атрибуты исходного диапазона, а именно: формула, формат, значение, примечание, условия. Иногда приводится сложное описание правил копирования формул, так как они вроде бы автоматически преобразуются при изменении адресов диапазона-приемника. На самом деле формулы копируются в формате R1C1 и при этом остаются неизменными (можете проверить, переключив вид листа Excel в R1C1). Отображение в привычном A1-формате просто преобразует формулу в новых координатах.

Операция «вырезания», в отличие от копирования, очищает исходный диапазон после проведения вставки. Если вставка не была выполнена, то никаких действий произведено не будет.

Специальная вставка

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

• значение
• формат
• формула
• примечание
• условия на значение (проверка данных)

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

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

Еще одна возможность специальной вставки – это транспонирование диапазона. После выполнения этой операции результирующий диапазон будет повернут на 90 градусов – данные из строк попадут в столбцы и наоборот.

Перетаскивание при помощи мыши

Начинающие пользователи Excel быстрее всего осваивают копирование данных через перетаскивание ячеек. Для этого имеется специальный указатель на рамке выделенного диапазона. Кстати, эту возможность можно отключить в общих параметрах Excel.

Операция перетаскивания ячеек при помощи мыши в большинстве случаев является аналогом копирования и вставки для смежных диапазонов ячеек. С технической точки зрения основное отличие заключается в том, что при перетаскивании мыши никакие данные в буфере обмена не сохраняются. Excel выполняет только внутреннюю процедуру вставки, после чего очищает информацию об источнике копирования. С точки зрения пользовательского интерфейса отличительной особенностью перетаскивания является возможность заполнения ячеек на основе автоматически определяемого числового ряда в выделенном диапазоне. Многие думают, что Excel умеет продолжать только последовательно возрастающий ряд, прибавляя единицу. Это не так, программа сама формирует коэффициент увеличения как среднее значение в выделенном диапазоне. На картинках примера это число 2.

Если во всех выделенных ячейках перед началом перетаскивания содержатся формулы, то процедура будет полностью идентична операциям копирования и вставки. Кроме того, используя специальный указатель, можно явно запустить операцию копирования без изменения значений (опция «Копировать ячейки»):

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

Команда "Меню " - "Правка " - "Специальная вставка " (либо через контекстное меню, вызываемой правой кнопкой мыши) позволяет расширить возможности обычного копирования.

Рассмотрим несколько примеров.

Чтобы заменить формулы в ячейках на конечные значения, выделяем необходимый диапазон, копируем его, выделяем куда нужно скопировать значения, запускаем "Меню " - "Правка " - "Специальная вставка ", отмечаем пункт "значения " и щелкаем ОК.

Чтобы скопировать формулы и вставить их, при этом не изменив формат конечных ячеек, используем пункт "формулы ".

Часто бывает, что таблица содержит числовые значения, сохраненные в виде текста.
Чтобы исправить это, выделяем пустую ячейку, щелкаем скопировать, затем выделяем диапазон, который нужно преобразовать в числа, запускаем "Специальную вставку ", выбираем операцию "сложить " и щелкаем ОК.

Вообще, можно каждое значение выделенного диапазона изменить в соответствии с выбранным пунктом "операция ". Пусть нам нужно каждое значение диапазона уменьшить в 100 раз. Пишем в пустой ячейке 100, копируем ее, выделяем диапазон, который нужно изменить, запускаем "Специальную вставку ", выбираем "операцию " "разделить " и щелкаем ОК.

Чтобы операция производилась только над непустыми ячейками дополнительно можно поставить галочку "пропускать пустые ячейки ".

Если необходимо транспонировать таблицу, т.е. поменять строки и столбцы местами, делаем следующее. Копируем исходную таблицу, выбираем куда вставить конечные данные, запускаем "Специальную вставку ", отмечаем галочкой "транспонировать " и щелкаем ОК.