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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Операция I:

Операция II:

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

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

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

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

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

Примечание: В Microsoft Excel установлены следующие ограничения на количество строк и столбцов: 16 384 столбца в ширину и 1 048 576 строк в высоту.

Вставка и удаление столбца

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

Чтобы удалить столбец, выделите его, а затем на вкладке Главная нажмите кнопку Вставить и выберите пункт Удалить столбцы с листа .

Можно также щелкнуть правой кнопкой мыши в верхней части столбца и выбрать команду Вставить или Удалить .

Вставка и удаление строки

Чтобы вставить строку, выделите ее, а затем на вкладке Главная нажмите кнопку Вставить и выберите пункт Вставить строки на лист .

Чтобы удалить строку, выделите ее, а затем на вкладке Главная нажмите кнопку Вставить и выберите пункт Удалить строки с листа .

Можно также щелкнуть правой кнопкой мыши выделенную строку и выбрать команду Вставить или Удалить .

Вставка ячейки

Выделите одну или несколько ячеек. Щелкните правой кнопкой мыши и выберите команду Вставить .

В окне Вставка выберите строку, столбец или ячейку для вставки.

Например, чтобы вставить новую ячейку между ячейками "Лето" и "Зима":

Щелкните ячейку "Зима".

На вкладке Главная Вставить и выберите команду Вставить ячейки (со сдвигом вниз) .

Новая ячейка добавляется над ячейкой "Зима":

Вставка строк

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

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

вставку столбцов

Чтобы вставить один новый столбец, выполните указанные ниже действия. Щелкните правой кнопкой мыши весь столбец справа от того места, куда вы хотите добавить новый столбец. Например, чтобы вставить столбец между столбцами B и C, щелкните правой кнопкой мыши столбец C и выберите команду Вставить столбцы.

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

Удаление ячеек, строк и столбцов

Если вам больше не нужны какие-либо ячейки, строки или столбцы, вот как удалить их:

Выделите ячейки, строки или столбцы, которые вы хотите удалить.

На вкладке Главная щелкните стрелку под кнопкой Удалить и выберите нужный вариант.

При удалении строк или столбцов следующие за ними строки и столбцы автоматически сдвигаются вверх или влево.

Совет: Если вы передумаете сразу после того, как удалите ячейку, строку или столбец, просто нажмите клавиши CTRL+Z, чтобы восстановить их.

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

Ctrl+C копирование данных;
Ctrl+X вырезать;
Ctrl+V вставить информацию из буфера обмена.

Так вот, в Excel есть более расширенная версия данной возможности.

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

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

Комментарии;
Формат ячейки;
Значение;
Формулу;
Оформление.

Как работает специальная вставка

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

Также, открыть специальную вставку можно на вкладке Главная . В самом начале нажимаем на специальную стрелку, которая находится под кнопкой Вставить .

Окно со всеми функциями выглядит следующим образом.

Теперь будем разбираться по порядку и начнем с блока "Вставить".

Все - это обычная функция, которая позволяет полностью скопировать все данные ячейки в новое место;
Формулы - будет перенесена только формула, которая использовалась в копируемой ячейке;
Значения - позволяет скопировать результат, который получается в ходе выполнения в ячейке формулы;
Форматы - переносится только формат ячейки. Также, будет скопировано оформление ячейки, например, заливка фона;
Примечания - копирование примечания ячейки. В этом случае данные (формула, значения и т.д.) не копируются;
Условия на значения - с помощью этой опции можно скопировать, например, критерий допустимых данных (выпадающий список);
С исходной темой - ячейка копируется с сохранением оформления. Например, если вы используете заливку фона в ячейке, она тоже будет скопирована. В этом случае формула будет скопирована;
Без рамки - если у ячейки с любой из сторон есть рамка, то при копировании она будет удалена;
Ширины столбцов - будет скопирована ширина столбца из одной ячейки в другую. Эту функцию очень удобно использовать, когда вы копируете данные с одного листа на другой. Переноситься только ширина столбцов;
Формулы и форматы чисел - переносится формула и формат чисел;
Значения и форматы чисел - переноситься результат и формат чисел.

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

Для того, чтобы заменить формулу результатами:

Копируем колонку ФИО;
Нажимаем на самой верхней ячейке правой клавишей мыши и выбираем Специальную вставку;
Ставим активным пункт Значение и нажимаем клавишу Ок .

Теперь в столбце вместо формулы занесены результаты.

Рассмотрим еще один пример. Для этого скопируем и вставим уже имеющуюся таблицу рядом.

Как видите, таблица не сохранила ширину столбцов. Наша задача сейчас перенести ширину столбцов в новую таблицу.

Копируем полностью исходную таблицу;
Становимся на верхнюю левую ячейку новой таблицы и щелкаем правой клавишей мыши. Далее выбираем пункт Специальная вставка;
Ставим активным пункт Ширины столбцов и нажимаем клавишу Ок .

Теперь таблица выглядит точно также как и исходная.

Теперь перейдем к блоку Операции .

Сложить - вставляемая информация будет складываться с уже имеющимися в ячейке значениями;
Вычесть - вставляемая информация будет вычитаться из уже имеющихся в ячейке значений;
Умножить - значение в ячейке будет умножено на вставляемое;
Разделить - значение в ячейке будет разделено на вставляемое.

Давайте разберем пример. Есть таблица, в которой есть колонка с числовыми значениями.

Задача: умножить каждое из чисел на 10. Что для этого нужно сделать:

В новой ячейке необходимо поставить цифру 10 и скопировать ее в буфер;
Выделить все ячейки столбца, в которых мы будем умножать;
Щелкаем правой клавишей по любой из выделенных ячеек и выбираем пункт Специальная вставка;
Ставим активным Умножить .

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

Рассмотрим еще одну задачу. Необходимо уменьшить полученные в предыдущем примере результаты на 20%.

В новой ячейке ставим 80% и копируем ее;
Выделяем все ячейки столбца, в которых мы будем вычислять процент;
Щелкаем правой клавишей мыши по любой из выделенных ячеек и выбираем пункт Специальная вставка;
Ставим активным Умножить .

В итоге получаем значения, уменьшенные на 20% от первоначальных.

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

Остались последние две опции, которые можно активировать внизу окна:

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

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

В этой статье мы покажем Вам ещё несколько полезных опций, которыми богат инструмент Специальная вставка , а именно: Значения, Форматы, Ширины столбцов и Умножить / Разделить. С этими инструментами Вы сможете настроить свои таблицы и сэкономить время на форматировании и переформатировании данных.

Если Вы хотите научиться транспонировать, удалять ссылки и пропускать пустые ячейки при помощи инструмента Paste Special (Специальная вставка) обратитесь к статье Специальная вставка в Excel: пропускаем пустые ячейки, транспонируем и удаляем ссылки .

Вставляем только значения

Возьмём для примера таблицу учёта прибыли от продаж печенья на благотворительной распродаже выпечки. Вы хотите вычислить, какая прибыль была получена за 15 недель. Как видите, мы использовали формулу, которая складывает сумму продаж, которая была неделю назад, и прибыль, полученную на этой неделе. Видите в строке формул =D2+C3 ? Ячейка D3 показывает результат этой формулы – $100 . Другими словами, в ячейке D3 отображено значение. А сейчас будет самое интересное! В Excel при помощи инструмента Paste Special (Специальная вставка) Вы можете скопировать и вставить значение этой ячейки без формулы и форматирования. Эта возможность иногда жизненно необходима, далее я покажу почему.

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

Упс! Это совсем не то, что Вы ожидали? Как видите, привычное действие скопировать и вставить в результате скопировало только формулу из ячейки? Вам нужно скопировать и выполнить специальную вставку самого значения. Так мы и поступим! Используем команду Paste Special (Специальная вставка) с параметром Values (Значения), чтобы все было сделано как надо.

Заметьте разницу на изображении ниже.

Применяя Специальная вставка > Значения , мы вставляем сами значения, а не формулы. Отличная работа!

Возможно, Вы заметили ещё кое-что. Когда мы использовали команду Paste Special (Специальная вставка) > Values (Значения), мы потеряли форматирование. Видите, что жирный шрифт и числовой формат (знаки доллара) не были скопированы? Вы можете использовать эту команду, чтобы быстро удалять форматирование. Гиперссылки, шрифты, числовой формат могут быть быстро и легко очищены, а у Вас останутся только значения без каких-либо декоративных штучек, которые могут помешать в будущем. Здорово, правда?

На самом деле, Специальная вставка > Значения – это один из моих самых любимых инструментов в Excel. Он жизненно необходим! Часто меня просят создать таблицу и представить её на работе или в общественных организациях. Я всегда переживаю, что другие пользователи могут привести в хаос введённые мной формулы. После того, как я завершаю работу с формулами и калькуляциями, я копирую все свои данные и использую Paste Special (Специальная вставка) > Values (Значения) поверх них. Таким образом, когда другие пользователи открывают мою таблицу, формулы уже нельзя изменить. Это выглядит вот так:

Обратите внимание на содержимое строки формул для ячейки D3 . В ней больше нет формулы =D2+C3 , вместо этого там записано значение 100 .

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

Появляется эта надоедливая ошибка #REF! (#ССЫЛ!). Она появилась потому, что значение в этой ячейке рассчитывается по формуле, которая ссылается на ячейки, находящиеся выше. После того, как мы удалили эти ячейки, формуле стало не на что ссылаться, и она сообщила об ошибке. Используйте вместо этого команды Copy (Копировать) и Paste Special (Специальная вставка) > Values (Значения) поверх исходных данных (так мы уже делали выше), а затем удалите лишние строки. Отличная работа:

Специальная вставка > Значения: основные моменты

Выделите данные
Скопируйте их. Если данные не скопировать, а вырезать, то команда Paste Special (Специальная вставка) будет недоступна, поэтому обязательно используйте копирование.
Выделите ячейку, в которую нужно вставить скопированные данные.
Нажмите Paste Special (Специальная вставка). Это можно сделать:
- кликнув правой кнопкой мыши и выбрав в контекстном меню Paste Special (Специальная вставка).
- на вкладке Home (Главная), нажмите маленький треугольник под кнопкой Paste (Вставить) и в выпадающем меню выберите Paste Special (Специальная вставка).
Отметьте опцию Values (Данные).
Нажмите ОК .

Вставляем только форматы

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

Я наткнулся на шаблон Winter 2010 schedule и мне понравились в нем форматирование, шрифт, цвет и дизайн.

Мне не нужно само расписание, а тем более для зимы 2010 года, я хочу просто переделать шаблон для своих целей. Что бы Вы сделали на моём месте? Вы можете создать черновой вариант таблицы и вручную повторить в ней дизайн шаблона, но это займёт очень много времени. Либо Вы можете удалить весь текст в шаблоне, но это тоже займёт уйму времени. Гораздо проще скопировать шаблон и сделать Paste Special (Специальная вставка) > Formats (Форматы) на новом листе Вашей рабочей книги. Вуаля!

Теперь Вы можете вводить данные, сохранив все форматы, шрифты, цвета и дизайн.

Специальная вставка > Форматы: основные моменты

Выделите данные.
Скопируйте их.
Выделите ячейку, в которую хотите вставить данные.
Нажмите Paste Special (Специальная вставка).
Отметьте опцию Formats (Форматы).
Нажмите ОК .

Копируем ширину столбцов на другой лист

Вы когда-нибудь теряли уйму времени и сил, кружа вокруг своей таблицы и пытаясь отрегулировать размеры столбцов? Мой ответ – конечно, да! Особенно, когда нужно скопировать и вставить данные из одной таблицы в другую. Существующие настройки ширины столбцов могут не подойти и даже, несмотря на то, что автоматическая настройка ширины столбца – это удобный инструмент, местами он может работать не так, как Вам хотелось бы. Специальная вставка > Ширины столбцов – это мощный инструмент, которым должны пользоваться те, кто точно знает, чего хочет. Давайте для примера рассмотрим список лучших программ US MBA.

Как такое могло случиться? Вы видите, как аккуратно была подогнана ширина столбца по размеру данных на рисунке выше. Я скопировал десять лучших бизнес-школ и поместил их на другой лист. Посмотрите, что получается, когда мы просто копируем и вставляем данные:

Содержимое вставлено, но ширина столбцов далеко не подходящая. Вы хотите получить точно такую же ширину столбцов, как на исходном листе. Вместо того чтобы настраивать ее вручную или использовать автоподбор ширины столбца, просто скопируйте и сделайте Paste Special (Специальная вставка) > Column Widths (Ширины столбцов) на ту область, где требуется настроить ширину столбцов.

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

Кроме этого, Вы можете настраивать ширину пустых ячеек, чтобы задать им формат, прежде чем вручную вводить текст. Посмотрите на столбцы E и F на картинке выше. На картинке внизу я использовал инструмент Специальная вставка > Ширины столбцов , чтобы расширить столбцы. Вот так, без лишней суеты Вы можете оформить свой лист Excel так, как Вам угодно!

Специальная вставка > Ширина столбцов: основные моменты

Выделите данные.
Скопируйте выделенные данные.
Поставьте курсор на ту ячейку, ширину которой Вы хотите настроить.
Нажмите Paste Special (Специальная вставка).
Отметьте опцию Column Widths (Ширины столбцов).
Нажмите ОК .

Специальная вставка: разделить и умножить

Помните наш пример с печеньем? Хорошие новости! Гигантская корпорация узнала о нашей благотворительной акции и предложила увеличить прибыль. После пяти недель продаж, они вложатся в нашу благотворительность, так что доход удвоится (станет в два раза больше) по сравнению с тем, какой он был в начале. Давайте вернёмся к той таблице, в которой мы вели учёт прибыли от благотворительной распродажи печенья, и пересчитаем прибыль с учётом новых вложений. Я добавил столбец, показывающий, что после пяти недель продаж прибыль удвоится, т.е. будет умножена на 2 .

Весь доход, начиная с 6-й недели, будет умножен на 2 .Чтобы показать новые цифры, нам нужно умножить соответствующие ячейки столбца C на 2 . Мы можем выполнить это вручную, но будет гораздо приятнее, если за нас это сделает Excel с помощью команды Paste Special (Специальная вставка) > Multiply (Умножить). Для этого скопируйте ячейку F7 и примените команду на ячейки C7:C16 . Итоговые значения обновлены. Отличная работа!

Как видите, инструмент Специальная вставка > Умножить может быть использован в самых разных ситуациях. Точно так же обстоят дела с Paste Special (Специальная вставка) > Divide (Разделить). Вы можете разделить целый диапазон ячеек на определённое число быстро и просто. Знаете, что ещё? При помощи Paste Special (Специальная вставка) с опцией Add (Сложить) или Subtract (Вычесть) Вы сможете быстро прибавить или вычесть число.

Специальная вставка > Разделить / Умножить: основные моменты

Выделите ячейку с числом, на которое нужно умножить или разделить.
Скопируйте выделенные данные.
Поставьте курсор на те ячейки, в которых Вы хотите выполнить умножение или деление.
Нажмите Paste Special (Специальная вставка).
Отметьте опцию Divide (Разделить) или Multiply (Умножить).
Нажмите ОК .

Итак, в этом уроке Вы изучили некоторые очень полезные возможности инструмента Специальная вставка , а именно: научились вставлять только значения или форматирование, копировать ширину столбцов, умножать и делить данные на заданное число, а также прибавлять и удалять значение сразу из диапазона ячеек.

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

Чтобы скопировать таблицу в Excel ее естественно нужно предварительно выделить. Для копирования быстрее всего использовать комбинацию клавиш «Ctrl + C» или использовать соответствующий пункт меню «Копировать» на вкладке «Главная» .

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

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

Будем считать, что таблицу скопировать нам удалось, теперь пришло время разобраться, как вставить таблицу в Excel. Тут тоже может быть все очень просто, так как можно воспользоваться комбинацией клавиш «Ctrl + V» . Но при вставке таблицы в Эксель, как и при вставке любых других данных, следует учитывать тот факт, что вставляемые данные будут начиная с выделенной ячейки на листе занимать необходимое пространство, замещая собой всю информацию, размещенную в необходимых для данной таблицы ячейках. Вставляются данные начиная с левой верхней ячейки. И еще следует помнить, что если после копирования таблицы или других данных будет произведена любая манипуляция на листе, кроме перемещения курсора, то данные из буфера обмена будут удалены. Это значит, что вставлять таблицу в Эксель нужно сразу же после ее копирования.

Также для вставки можно воспользоваться пунктом меню «Вставить» на вкладке «Главная» .