Excel специальная вставка пропускать пустые ячейки

Обновлено: 07.07.2024

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

Копирование данных можно разделить на два этапа: копирование и вставка. Копировать умеют все, а вот грамотно вставить скопированное могут немногие.

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

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

Также можно воспользоваться кнопкой "Вставка" на вкладке "Главная". Она предложит еще больше вариантов.

Перед нажатием какой-либо команды, если Вы не уверены, что она делает, просто наведите на нее курсор мыши и дождитесь всплывающей подсказки. Перечислять значения каждой кнопки нет смысла, так как все они достаточно прозрачны и выполняют то, что от них и ждут. "Формулы" вставляет формулы, "Значения" - значения, "Транспонировать" - разворачивает строки и столбцы и т.д.

Затруднение может вызвать разве что команда, на иконке которой изображен фрагмент из двух звеньев цепи. Этот способ вставки позволяет создать связь между исходным диапазоном (который Вы скопировали) и конечным (тем, куда Вы вставляете данные). При таком виде вставки любые изменения исходного диапазона (его значений и формул) будут тут же отображены в конечном.

Если Вы хотите освоить специальную вставку в совершенстве, то должны знать о существовании окна "Специальная вставка". Его можно вызвать кликнув на соответствующую команду, которая обычно расположено ниже иконок выбора способа вставки, или же сочетанием клавиш Ctrl+Alt+V, что очень удобно.

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

Видеоверсию данной статьи смотрите на нашем канале на YouTube

Чтобы не пропустить новые уроки и постоянно повышать свое мастерство владения Excel - подписывайтесь на наш канал в Telegram Excel Everyday

Куча интересного по другим офисным приложениям от Microsoft (Word, Outlook, Power Point, Visio и т.д.) - на нашем канале в Telegram Office Killer

Вопросы по Excel можно задать нашему боту обратной связи в Telegram @ExEvFeedbackBot

Вопросы по другому ПО (кроме Excel) задавайте второму боту - @KillOfBot

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

Вставка ссылок на ячейки:

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

Чтобы вставить ссылки на ячейки - выделите ячейки, ссылки на которые необходимо вставить и нажмите сочетание клавиш Ctrl+C или кликните правой клавишей мышки по выделенным ячейкам и в контекстном меню выберите пункт "Копировать":

Если у вас MS Excel 2010 или 2013 выделите ячейки (допустим на другом листе), в которые необходимо вставить ссылки на скопированные ячейки и нажмите сочетание клавиш Ctrl+Alt+V или кликните правой клавишей мышки по выделенным ячейкам и в контекстном меню выберите соответствующую пиктограмму:

Если у вас MS Excel 2003 или 2007 выделите ячейки (допустим на другом листе), в которые необходимо вставить ссылки на скопированные ячейки и нажмите сочетание клавиш Ctrl+Alt+V или кликните правой клавишей мышки по выделенным ячейкам и в контекстном меню выберите пункт "Специальная вставка":

В открывшемся диалоговом окне нажмите "Вставить связь":

В выделенный диапазон ячеек будут вставлены ссылки на скопированные ячейки:

Замена формул на значения:

Чтобы заменить формулы на значения - выделите ячейки с формулами и нажмите сочетание клавиш Ctrl+C или кликните правой клавишей мышки по выделенным ячейкам и в контекстном меню выберите пункт "Копировать":

Если у вас MS Excel 2010 или 2013 не снимая выделения с ячеек нажмите сочетание клавиш Ctrl+Alt+V или кликните правой клавишей мышки по выделенным ячейкам и в контекстном меню выберите соответствующую пиктограмму :

Если у вас MS Excel 2003 или 2007 не снимая выделения с ячеек нажмите сочетание клавиш Ctrl+Alt+V или кликните правой клавишей мышки по выделенным ячейкам и в контекстном меню выберите пункт "Специальная вставка":

В открывшемся диалоговом окне установите переключатель напротив пункта "Значения" и нажмите "ОК":

Все формулы в выделенном диапазоне ячеек будут заменены на значения:

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

Слияние данных из двух диапазонов (текстовые данные):

Эта типичная задача иногда приводит в замешательство даже опытных пользователей MS Excel. А решается опять же с помощью специальной вставки.

Чтобы объединить данные из двух столбцов - выделите первый столбец и нажмите сочетание клавиш Ctrl+C или кликните правой клавишей мышки по выделенным ячейкам и в контекстном меню выберите пункт "Копировать":

Затем выделите второй столбец и нажмите сочетание клавиш Ctrl+Alt+V или кликните правой клавишей мышки по выделенным ячейкам и в контекстном меню выберите пункт "Специальная вставка":

В открывшемся диалоговом окне установите галочку напротив пункта "Пропускать пустые ячейки" и нажмите "ОК":

Данные из первого столбца вставятся в пустые ячейки второго столбца, не повредив данных:

Слияние данных из двух диапазонов (числовые данные):

При слиянии числовых данных специальная вставка может выполнять с ними арифметические действия:

В открывшемся диалоговом окне установите переключатель напротив пункта "Сложить" и нажмите "ОК":

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

Работа со специальной вставкой

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

Для того, чтобы перейти в специальную вставку, прежде всего, нужно выполнить действие по копированию.

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

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

Вставка («Вставить», «Транспонировать», «Формулы», «Формулы и форматы чисел», «Без рамок», «Сохранить ширину столбцов оригинала» и «Сохранить исходное форматирование»);
Вставить значения («Значение и исходное форматирование», «Значения» и «Значения и форматы чисел»);
Другие параметры вставки («Форматирование», «Рисунок», «Вставить связь» и «Связанный рисунок»).

Способ 1: работа со значениями

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

Способ 2: копирование формул

Но существует и обратная ситуация, когда нужно скопировать именно формулы.

В этом случае, выполняем процедуру копирования любым доступным способом.
После этого выделяем область на листе, куда следует вставить таблицу или другие данные. Активируем контекстное меню и выбираем пункт «Формулы». При этом будут вставлены только формулы и значения (в тех ячейках, где формул нет), но при этом будет утрачено форматирование и настройка числовых форматов. Поэтому, например, если в исходной области присутствовал формат даты, то после копирования он будет отражен некорректно. Соответствующие ячейки нужно будет дополнительно отформатировать.

Но существует возможность произвести перенос формул с сохранением формата чисел или даже с полным сохранением исходного форматирования.

«Формулы и форматы чисел»

Способ 3: перенос форматирования

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

Способ 4: копирование таблицы с сохранением размера столбцов

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

Способ 5: вставка рисунка

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

В окне специальной вставки подобную операцию выполнить нельзя.

Способ 6: копирование примечаний

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

Ctrl+C

Способ 7: транспонирование таблицы

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

Способ 8: использование арифметических действий

Посредством описываемого нами инструмента в Excel также можно выполнять распространенные арифметические действия:

Сложение;
Умножение;
Вычитание;
Деление.

Посмотрим, как применяется данный инструмент на примере умножения.

Ctrl+C

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

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

Мы рады, что смогли помочь Вам в решении проблемы.

Отблагодарите автора, поделитесь статьей в социальных сетях.

Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

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

Скопируйте и вставьте пропущенные пустые ячейки с помощью специальной функции «Вставить»

Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%.

Скопируйте и вставьте пропустить пустые ячейки с Специальная функция вставки

Потрясающе! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!

Сэкономьте 50% своего времени и сократите тысячи щелчков мышью каждый день!

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

1 . Выберите диапазон данных, который вы хотите скопировать, а затем нажмите Ctrl + C , чтобы скопировать его..

2 . Затем выберите другой диапазон или ячейку, в которую нужно вставить данные, а затем щелкните правой кнопкой мыши и выберите в контекстном меню Специальная вставка > Специальная вставка , см. Снимок экрана. :

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

4 . Затем нажмите кнопку OK , и ваши скопированные данные из диапазона A будут вставлены в диапазон B без пустых ячеек. См. Снимок экрана:

Читайте также: