Все мы привыкли к тому, что мы можем скопировать и вставить данные из одной ячейки в другую с помощью стандартных команд операционной системы Windows. Для этого нам понадобится 3 сочетания клавиш:
- Ctrl+C копирование данных;
- Ctrl+X вырезать;
- Ctrl+V вставить информацию из буфера обмена.
Так вот, в Excel есть более расширенная версия данной возможности.
Специальная вставка - это универсальная команда, которая позволяет выполнить вставку скопированных данных из одной ячейки в другую по отдельности.
Например, можно отдельно вставить из скопированной ячейки:
- Комментарии;
- Формат ячейки;
- Значение;
- Формулу;
- Оформление.
Для начала давайте найдем где расположена команда. После того, как вы скопировали ячейку, открыть специальную вставку можно несколькими способами. Можно кликнуть правой клавишей мыши по той ячейке, куда необходимо вставить данные, и выбрать из выпадающего меню пункт "Специальная вставка". В этом случае у вас есть возможность воспользоваться быстрым доступом к функциям вставки, а также, кликнув на ссылку внизу списка, открыть окно со всеми возможностями. В разных версиях этот пункт может быть разным, поэтому не пугайтесь, если у вас не дополнительного выпадающего меню.
Также, открыть специальную вставку можно на вкладке Главная . В самом начале нажимаем на специальную стрелку, которая находится под кнопкой Вставить .
Окно со всеми функциями выглядит следующим образом.
Теперь будем разбираться по порядку и начнем с блока "Вставить".
- Все - это обычная функция, которая позволяет полностью скопировать все данные ячейки в новое место;
- Формулы - будет перенесена только формула, которая использовалась в копируемой ячейке;
- Значения - позволяет скопировать результат, который получается в ходе выполнения в ячейке формулы;
- Форматы - переносится только формат ячейки. Также, будет скопировано оформление ячейки, например, заливка фона;
- Примечания - копирование примечания ячейки. В этом случае данные (формула, значения и т.д.) не копируются;
- Условия на значения - с помощью этой опции можно скопировать, например, критерий допустимых данных (выпадающий список);
- С исходной темой - ячейка копируется с сохранением оформления. Например, если вы используете заливку фона в ячейке, она тоже будет скопирована. В этом случае формула будет скопирована;
- Без рамки - если у ячейки с любой из сторон есть рамка, то при копировании она будет удалена;
- Ширины столбцов - будет скопирована ширина столбца из одной ячейки в другую. Эту функцию очень удобно использовать, когда вы копируете данные с одного листа на другой. Переноситься только ширина столбцов;
- Формулы и форматы чисел - переносится формула и формат чисел;
- Значения и форматы чисел - переноситься результат и формат чисел.
Рассмотрим несколько примеров. Есть таблица, в которой столбец ФИО собирается с помощью функции Сцепить . Нам необходимо вместо формулы вставить готовые значения.
Для того, чтобы заменить формулу результатами:
- Копируем колонку ФИО;
- Нажимаем на самой верхней ячейке правой клавишей мыши и выбираем Специальную вставку;
- Ставим активным пункт Значение и нажимаем клавишу Ок .
Теперь в столбце вместо формулы занесены результаты.
Рассмотрим еще один пример. Для этого скопируем и вставим уже имеющуюся таблицу рядом.
Как видите, таблица не сохранила ширину столбцов. Наша задача сейчас перенести ширину столбцов в новую таблицу.
- Копируем полностью исходную таблицу;
- Становимся на верхнюю левую ячейку новой таблицы и щелкаем правой клавишей мыши. Далее выбираем пункт Специальная вставка;
- Ставим активным пункт Ширины столбцов и нажимаем клавишу Ок .
Теперь таблица выглядит точно также как и исходная.
Теперь перейдем к блоку Операции .
- Сложить - вставляемая информация будет складываться с уже имеющимися в ячейке значениями;
- Вычесть - вставляемая информация будет вычитаться из уже имеющихся в ячейке значений;
- Умножить - значение в ячейке будет умножено на вставляемое;
- Разделить - значение в ячейке будет разделено на вставляемое.
Давайте разберем пример. Есть таблица, в которой есть колонка с числовыми значениями.
Задача: умножить каждое из чисел на 10. Что для этого нужно сделать:
- В новой ячейке необходимо поставить цифру 10 и скопировать ее в буфер;
- Выделить все ячейки столбца, в которых мы будем умножать;
- Щелкаем правой клавишей по любой из выделенных ячеек и выбираем пункт Специальная вставка;
- Ставим активным Умножить .
В конце получаем необходимый результат.
Рассмотрим еще одну задачу. Необходимо уменьшить полученные в предыдущем примере результаты на 20%.
- В новой ячейке ставим 80% и копируем ее;
- Выделяем все ячейки столбца, в которых мы будем вычислять процент;
- Щелкаем правой клавишей мыши по любой из выделенных ячеек и выбираем пункт Специальная вставка;
- Ставим активным Умножить .
В итоге получаем значения, уменьшенные на 20% от первоначальных.
Здесь есть одно единственное замечание. Когда вы работаете с блоком Операции , старайтесь выставлять в блоке Вставить опцию Значение , иначе при вставке будет скопировано форматирование той ячейки, которую вставляем и потеряется то форматирование, которое было изначально.
Остались последние две опции, которые можно активировать внизу окна:
- Пропускать пустые ячейки - позволяет не вставлять пустые ячейки из скопированного диапазона. Программа не будет стирать данные в ячейке, в которую вставляется пустая ячейка;
- Транспонировать - изменение ориентации копируемых ячеек, т.е. строки становятся столбцами, а столбцы - строками.
На этом все, если у вас возникли вопросы, то обязательно задавайте их в комментариях ниже.
Команда Специальная вставка (Paste Special) в Excel позволяет легко выполнять определенные манипуляции с данными в электронной таблице. В настоящей заметке показано, как с помощью команды Специальная вставка:
1. Как переместить результаты вычислений (не формулы) в другую часть листа?
На рис. 1 диапазон ячеек Е4:Н9 содержит имена, число проведенных игр, общий счет и счет за игру для пяти 10–11-летних баскетболистов из Блумигнтона, штат Индиана. В диапазоне ячеек Н5:Н9 я использовал данные из диапазона F5:G9, чтобы подсчитать число очков, набранных за игру каждым ребенком.
Скачать заметку в формате или , примеры в формате
Предположим, мы хотим скопировать эти данные и подсчитанные очки за игру - но не формулы, которые выполняют вычисления, - в другой диапазон ячеек (например, Е13:Н18). Все, что вам нужно сделать - выделить диапазон Е4:Н9, скопировать его в буфер (например, нажимая Ctrl+C), перейти к верхнему левому углу диапазона, куда собираетесь скопировать данные (в нашем случае это ячейка Е13). После этого кликните правой кнопкой мыши, в контекстном меню выберите команду Специальная вставка, и в открывшемся диалоговом окне установите параметры, как показано на рис. 2. После того как вы щелкнете ОК, диапазон Е13:Н18 будет содержать данные, но не формулы, из диапазона ячеек Е4:Н9. Чтобы убедиться в этом, перейдите в ячейку Н16. Вы увидите значение (7), но не формулу для вычисления среднего числа очков Грегори за игру. Обратите внимание: если воспользоваться командой Специальная вставка, установить переключатель Значения , и затем вставить данные в тот же диапазон, из которого они копировались, формулы исчезнут и из него.
Рис. 2. Диалоговое окно Специальная вставка с установленным переключателем Значения . При установке этого переключателя копируются только значения, без формул
2. У меня список имен перечислен в отдельном столбце. Как сделать так, чтобы список отображался в строке, а не в столбце?
Чтобы перестроить данные из строки в столбец (или наоборот), воспользуйтесь командами Копировать и Специальная вставка с опцией Транспонировать . По сути, переключатель Транспонировать диалогового окна Специальная вставка «переворачивает» выбранные ячейки вокруг оси таким образом, что первая строка скопированного диапазона становится первым столбцом диапазона, в который вставляются данные, и так далее.
Допустим, вы хотите вывести имена игроков одной строкой (начиная с ячейки Е13). Просто выделите диапазон Е5:Е9, скопируйте его, выберите команду Специальная вставка и затем в открывшемся диалоговом окне установите переключатели Значения и Транспонировать . Когда вы щелкнете ОК, имена игроков отобразятся в строке (рис. 3).
Рис. 3. Опция Транспонировать диалогового окна Специальная вставка перемещает строку данных в столбец данных и наоборот
Предположим, вам требуется переместить содержимое ячеек электронной таблицы Е4:Н9 в диапазон, начинающийся с ячейки Е17. Прежде всего выделите диапазон Е4:Н9, скопируйте его, перейдите в верхний левый угол диапазона, куда собираетесь вставить транспонированные данные (Е17). Выберите команду Специальная вставка, установите переключатель Транспонировать (оставьте опцию Все ) и затем щелкните ОК. Содержимое диапазона Е4:Н9 будет транспонировано (повернуто) (рис. 4). Обратите внимание на диапазон F20:J20: Excel достаточно умен, чтобы изменить формулу подсчета очков за игру - теперь среднее значение для каждого игрока вычисляется на основе данных соответствующего столбца, а не строки.
Попробуйте выбрать команду Специальная вставка и вместо ОК щелкните Вставить связь . Новые ячейки окажутся связанными с исходными ячейками, и изменения, которые вы вносите в исходные ячейки, отображаются в копиях. Если изменить значение ячейки F5 на 7, значение в ячейке F18 также изменится на 7 и в ячейке F20 среднее число очков Дэна за игру станет равным 4 (рис. 5).
Рис. 5. Команда Вставить связь
3. Я загрузил с Web-узла Казначейства США в Excel список процентных ставок по векселям. Ставка в 5% в списке обозначена цифрой 5, ставка в 8% – цифрой 8 и так далее. Как мне быстро разделить результаты на 100, чтобы ставка в 5% отображалась, скажем, как 0,05?
На рис. 6 перечислены годичные процентные ставки по векселям Казначейства США с трехмесячным погашением для каждого месяца между январем 1970 г. и февралем 1987 г.
В январе 1970 г. годичная процентная ставка по векселю казначейства составляла 8,01%. Предположим, мы хотим получить годовой доход с $1, который инвестировали в покупку векселя казначейства с такой процентной ставкой. Формула для вычисления дохода: 1 + (годовая процентная ставка)/100. Подсчитать полученный доход было бы гораздо легче, если все данные о годовых процентных ставках в столбце поделить на 100.
Возможности раздела Операция диалогового окна Специальная вставка позволяют складывать, вычитать, умножать и делить каждое число из диапазона на заданное число, а значит, нам удастся легко разделить все годовые процентные ставки на 100. В данном случае нам требуется разделить все числа в столбце D. Для начала введите наше число (100). Его можно ввести в любую ячейку электронной таблицы. Выделив ячейку с этим числом и скопируйте его в память. Затем выделите диапазон чисел, которые требуется изменить. Чтобы выбрать все данные в столбце D, щелкните ячейку D10, нажмите комбинацию клавиш Ctrl+Shirt и затем - «стрелку вниз». Это клавиатурное сокращение полезно для выбора «высокого» диапазона ячеек (чтобы выделить «широкий» набор данных, находящихся в одной строке, щелкните первую ячейку данных, нажмите комбинацию клавиш Ctrl+Shirt и затем - «стрелку вправо»). Теперь выберите команду Специальная вставка и установите переключатель Разделить (рис. 7).
Рис. 7. Опция разделить раздела Операция окна Специальная вставка
По щелчку OK Excel разделит каждое из выделенных чисел в столбце D на 100 (рис. 8). При выборе операции сложить в ячейке D10 отобразилось бы значение 108,01; при выборе вычесть - значение –91,99; умножить - значение 801.
Рис. 8. Результат установки переключателя разделить в диалоговом окне Специальная вставка
Контрольные задания
1. На рис. 9 показана статистика игр баскетбольной команды «Даллас Маверикс» за сезон 2002-2003 гг. Имена игроков перечислены в столбце А, названия разделов – в строке 3, а результаты – в строках 4–20.
а) Измените электронную таблицу таким образом, чтобы имена игроков были перечислены в одной строке, а разделы статистики - в одном столбце.
б) Процент попаданий с поля, процент попаданий при штрафном броске и процент попаданий при трехочковом броске указаны как десятичные числа. Например, Стив Нэш при штрафном броске попадает 91,9 раз из ста, что указано как 0,919. Измените электронную таблицу таким образом, чтобы все проценты попаданий указывались в виде числа между 1 и 100.
2. На рис. 10 показаны данные о квартальных продажах четырех товаров. Скопируйте эти данные в другой диапазон ячеек. Свяжите скопированные данные с исходными, чтобы в скопированных ячейках при вычислении годовых продаж отражались изменения, вносимые в строки 2–5.
Ответы в Excel-файле
При написании заметки использованы материалы книги Уэйн Л. Винстон. Microsoft Excel. Анализ данных и построение бизнес-моделей, глава 13.
Специальная вставка в Excel - это возможность обработки содержимого буфера обмена. При копировании какого-либо объекта Excel позволяет его как-то видоизменить, сделано это для упрощения операций. Те же операции возможно сделать и без специальной вставки, но с ней это делается в один-два клика.
Как это работаетНапример, вы скопировали какую-то таблицу. И Вы хотите ее транспонировать, т.е., поменять строки и столбцы местами. Для этого вы кликаете на то место, куда будете вставлять таблицу, правой кнопкой мыши и вы увидите окно специальной вставки. Делаете свои манипуляции, например, ставите галочку на пункте "транспонировать" и "Ок".
Что можно поменятьЧтобы было проще, давайте разберем, что находится в конкретной ячейке.
Ячейка может содержать формулу, результат которой визуально показан (значение), имеет определенный формат как ячейки, так и текста или числа. Она может иметь примечания, атрибутику (например, или ). Она может быть частью таблицы, которая имеет свою структуру, состоящую из строк и столбцов.
Специальная вставка позволяет оперировать с любым из этих свойств ячейки или таблицы.
Конкретнее давайте посмотрим на примерах:
4-х минутное видео:
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. Вы можете изменить значок для вставленного содержимого.
Объемы информации, хранящиеся в Excel иногда достигают очень внушительных размеров, имеют различные источники и могут частично себя копировать. При этом, помимо данных, важную роль играет форматирование и формулы таблиц. Всем этим полезно уметь правильно управлять относительно копирования и вставки.
Вставка с веб-страницТекст страниц интернета почти всегда имеет индивидуальный стиль, который сохраняется в буфере обмена и распознается приложением. Но листы Excel могут иметь свое оформление, которое требуется сохранить.
Для примера скопируем курсы валют ЦБ РФ с главной страницы Яндекса на рабочий лист. Изображение показывает, что приложение совсем немного изменило вид, но в общем все сохранилось, как в исходнике.
Повторим вставку, но теперь с учетом конечного форматирования. Найдите на ленте вкладки «Главная» область «Буфер обмена» и раскройте меню параметров вставки, нажав стрелку под иконкой «Вставить».
Будут предоставлены 2 варианта. Первый называется «Сохранить исходное форматирование». Он аналогичен простой вставке. Второй способ именуется «Использовать конечное форматирование», является противоположным первому, но сохраняет расположение элементов относительно друг друга. Т.е. единственное его отличие – это удаление гиперссылок, стиля шрифта, границ, объединения ячеек и прочего.
Еще в меню параметров вставки содержится 2 дополнительных пункта:
- Обновляемый веб-запрос – доступен только при копировании элементов из интернета и позволяет сохранить данные всей веб-страницы по заданным параметрам;
- Специальная вставка – доступна всегда, но имеет разные параметры, в зависимости от источника данных.
Приложение Excel предоставляет гораздо больше способов вставки при внутреннем копировании, по сравнению с переносом из внешних источников. Ниже приведен скрин этих параметров и их описание (порядок сверху слева на право):
- Вставить – простая вставка.
- Вставить формулы – вставляются только формулы. Относительные ссылки сдвигаются.
- Формулы и форматы чисел – аналогичен предыдущему пункту, но сохраняет числовой формат, как в исходных данных.
- Сохранить исходное форматирование – аналогичен первому пункту, т.к. копирует и формулы, и формат, и условное форматирование.
- Без рамок – удаляет границы для скопированных данных, если они имеются. Если границы заданы условным форматированием, то они остаются.
- Сохранить ширину столбцов оригинала – при вставке учитывается ширина исходных ячеек.
- Транспонирование – меняет ориентацию данных таким образом, что столбцы становятся строками, а строки столбцами.
- Объединить условное форматирование – аналогичен простой вставке, но при наличии условного форматирования в исходном и конечном диапазонах, объединяет их.
- Последующие 3 параметра («значения», «значения и форматы чисел» и «значения и исходное форматирование») вставляют результаты ячеек, но, при желании, можно сохранить числовой формат либо все форматирование. Это удобно при копировании диапазонов с формулами;
- Форматирование – вставка только форматирования, включая условное;
- Вставить связь – устанавливает ссылки на исходный диапазон;
- Рисунок – Excel создаст изображение скопированных данных;
- Связанный рисунок – отличается от предыдущего пункта обновлением при изменении исходных данных.
Для отображения содержимого буфера обмена на главной вкладке кликнете по стрелочке, расположенной в нижнем правом углу одноименной области. Откроется панель, в которой будут отображены скопированные элементы, если таковые имеются. Скорее всего, там будут показаны последние данные.
