Как вставить значения в excel. Специальная вставка в Excel. Форматы: основные моменты

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Для примера скопируем курсы валют ЦБ РФ с главной страницы Яндекса на рабочий лист. Изображение показывает, что приложение совсем немного изменило вид, но в общем все сохранилось, как в исходнике.

Повторим вставку, но теперь с учетом конечного форматирования. Найдите на ленте вкладки «Главная» область «Буфер обмена» и раскройте меню параметров вставки, нажав стрелку под иконкой «Вставить».

Будут предоставлены 2 варианта. Первый называется «Сохранить исходное форматирование». Он аналогичен простой вставке. Второй способ именуется «Использовать конечное форматирование», является противоположным первому, но сохраняет расположение элементов относительно друг друга. Т.е. единственное его отличие – это удаление гиперссылок, стиля шрифта, границ, объединения ячеек и прочего.

Еще в меню параметров вставки содержится 2 дополнительных пункта:

• Обновляемый веб-запрос – доступен только при копировании элементов из интернета и позволяет сохранить данные всей веб-страницы по заданным параметрам;
• Специальная вставка – доступна всегда, но имеет разные параметры, в зависимости от источника данных.

Приложение Excel предоставляет гораздо больше способов вставки при внутреннем копировании, по сравнению с переносом из внешних источников. Ниже приведен скрин этих параметров и их описание (порядок сверху слева на право):

• Вставить – простая вставка.
• Вставить формулы – вставляются только формулы. Относительные ссылки сдвигаются.
• Формулы и форматы чисел – аналогичен предыдущему пункту, но сохраняет числовой формат, как в исходных данных.
• Сохранить исходное форматирование – аналогичен первому пункту, т.к. копирует и формулы, и формат, и условное форматирование.
• Без рамок – удаляет границы для скопированных данных, если они имеются. Если границы заданы условным форматированием, то они остаются.
• Сохранить ширину столбцов оригинала – при вставке учитывается ширина исходных ячеек.
• Транспонирование – меняет ориентацию данных таким образом, что столбцы становятся строками, а строки столбцами.
• Объединить условное форматирование – аналогичен простой вставке, но при наличии условного форматирования в исходном и конечном диапазонах, объединяет их.
• Последующие 3 параметра («значения», «значения и форматы чисел» и «значения и исходное форматирование») вставляют результаты ячеек, но, при желании, можно сохранить числовой формат либо все форматирование. Это удобно при копировании диапазонов с формулами;
• Форматирование – вставка только форматирования, включая условное;
• Вставить связь – устанавливает ссылки на исходный диапазон;
• Рисунок – Excel создаст изображение скопированных данных;
• Связанный рисунок – отличается от предыдущего пункта обновлением при изменении исходных данных.

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

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. Вы можете изменить значок для вставленного содержимого.

Команда Специальная вставка (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.