Все мы привыкли к тому, что мы можем скопировать и вставить данные из одной ячейки в другую с помощью стандартных команд операционной системы Windows. Для этого нам понадобится 3 сочетания клавиш:
Так вот, в Excel есть более расширенная версия данной возможности.
Специальная вставка - это универсальная команда, которая позволяет выполнить вставку скопированных данных из одной ячейки в другую по отдельности.
Например, можно отдельно вставить из скопированной ячейки:
Для начала давайте найдем где расположена команда. После того, как вы скопировали ячейку, открыть специальную вставку можно несколькими способами. Можно кликнуть правой клавишей мыши по той ячейке, куда необходимо вставить данные, и выбрать из выпадающего меню пункт "Специальная вставка". В этом случае у вас есть возможность воспользоваться быстрым доступом к функциям вставки, а также, кликнув на ссылку внизу списка, открыть окно со всеми возможностями. В разных версиях этот пункт может быть разным, поэтому не пугайтесь, если у вас не дополнительного выпадающего меню.
Также, открыть специальную вставку можно на вкладке Главная . В самом начале нажимаем на специальную стрелку, которая находится под кнопкой Вставить .
Окно со всеми функциями выглядит следующим образом.
Теперь будем разбираться по порядку и начнем с блока "Вставить".
Рассмотрим несколько примеров. Есть таблица, в которой столбец ФИО собирается с помощью функции Сцепить . Нам необходимо вместо формулы вставить готовые значения.
Для того, чтобы заменить формулу результатами:
Теперь в столбце вместо формулы занесены результаты.
Рассмотрим еще один пример. Для этого скопируем и вставим уже имеющуюся таблицу рядом.
Как видите, таблица не сохранила ширину столбцов. Наша задача сейчас перенести ширину столбцов в новую таблицу.
Теперь таблица выглядит точно также как и исходная.
Теперь перейдем к блоку Операции .
Давайте разберем пример. Есть таблица, в которой есть колонка с числовыми значениями.
Задача: умножить каждое из чисел на 10. Что для этого нужно сделать:
В конце получаем необходимый результат.
Рассмотрим еще одну задачу. Необходимо уменьшить полученные в предыдущем примере результаты на 20%.
В итоге получаем значения, уменьшенные на 20% от первоначальных.
Здесь есть одно единственное замечание. Когда вы работаете с блоком Операции , старайтесь выставлять в блоке Вставить опцию Значение , иначе при вставке будет скопировано форматирование той ячейки, которую вставляем и потеряется то форматирование, которое было изначально.
Остались последние две опции, которые можно активировать внизу окна:
На этом все, если у вас возникли вопросы, то обязательно задавайте их в комментариях ниже.
Всем известна возможность копирования и вставки данных через Буфер обмена операционной системы.
Комбинации горячих клавиш:
Ctrl+C - скопировать
Ctrl+X - вырезать
Ctrl+V - вставить
Команда Специальная вставка - универсальный вариант команды Вставить.
Специальная вставка позволяет осуществить раздельную вставку атрибутов скопированных диапазонов. В частности, можно вставить в новое место рабочего листа только комментарии, только форматы или только формулы из скопированного диапазона.
Чтобы эта команда стала доступной, необходимо:
В результате на экране появится диалоговое окно Специальная вставка, которое будет различным в зависимости от источника скопированных данных.
Если копирование диапазона ячеек было проведено в том же приложении, то окно Специальная вставка будет выглядеть следующим образом:
Группа переключателей Вставить:
Все - выбор этой опции эквивалентен использованию команды Вставить. При этом копируется содержимое ячейки и формат.
Формулы - выбор этой опции позволяет вставить только формулы в том виде, в котором они вводились в строку формул.
Значения - выбор данной опции позволяет скопировать результаты расчетов по формулам.
Форматы - при использовании данной опции, в ячейку или диапазон будет вставлен только формат скопированной ячейки.
Примечания - если нужно скопировать только примечания к ячейке или диапазону, то можно воспользоваться данной опцией. Эта опция не копирует содержимое ячейки или атрибуты ее форматирования.
Условия на значения - если для конкретной ячейки был создан критерий допустимости данных (с помощью команды Данные | Проверка), то этот критерий можно скопировать в другую ячейку или диапазон, воспользовавшись данной опцией.
Без рамки - часто возникает необходимость скопировать ячейку без рамки. Например, если у Вас есть таблица с рамкой, то при копировании граничной ячейки будет скопирована также и рамка. Чтобы избежать копирования рамки можно выбрать эту опцию.
Ширины столбцов - можно скопировать информацию о ширине столбца из одного столбца в другой.
Совет! Эту функцию удобно использовать при копировании готовой таблицы с одного листа на другой.
Часто после вставки скопированной таблицы на новый лист приходится корректировать ее размеры.
Лист 1 Исходная таблица
Лист 2 Вставка
Чтобы этого избежать, воспользуйтесь вставкой Ширины столбцов. Для этого:
В результате Вы получите точную копию исходной таблицы на новом листе.
Опция пропускать пустые ячейки не позволяет программе стирать содержимое ячеек в области вставки, что может произойти, если в копируемом диапазоне есть пустые ячейки.
Опция транспонировать меняет ориентацию копируемого диапазона. Строки становятся столбцами, а столбцы — строками. Подробнее об этой опции можно прочитать в Фишке Excel «Транспонирование».
Разберем примеры выполнения математических операций с помощью диалогового окна Специальная вставка.
Задача 1. Прибавить 5 к каждому значению в ячейках А3:А12
4. Выбираем операцию Сложить и нажимаем ОК.
В результате все значения в выделенном диапазоне будут увеличены на 5.
Задача 2 . Уменьшить на 10% цены на товары, находящиеся в диапазоне Е4:Е10 (не пользуясь формулами).
В этой статье мы покажем Вам ещё несколько полезных опций, которыми богат инструмент Специальная вставка , а именно: Значения, Форматы, Ширины столбцов и Умножить / Разделить. С этими инструментами Вы сможете настроить свои таблицы и сэкономить время на форматировании и переформатировании данных.
Если Вы хотите научиться транспонировать, удалять ссылки и пропускать пустые ячейки при помощи инструмента 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 (Значения) поверх исходных данных (так мы уже делали выше), а затем удалите лишние строки. Отличная работа:
Специальная вставка > Форматы это ещё один очень полезный инструмент в Excel. Мне он нравится тем, что позволяет достаточно легко настраивать внешний вид данных. Есть много применений для инструмента Специальная вставка > Форматы , но я покажу Вам наиболее примечательное. Думаю, Вы уже знаете, что Excel великолепен для работы с числами и для выполнения различных вычислений, но он также отлично справляется, когда нужно представить информацию. Кроме создания таблиц и подсчёта значений, Вы можете делать в Excel самые различные вещи, такие как расписания, календари, этикетки, инвентарные карточки и так далее. Посмотрите внимательнее на шаблоны, которые предлагает Excel при создании нового документа:
Я наткнулся на шаблон Winter 2010 schedule и мне понравились в нем форматирование, шрифт, цвет и дизайн.
Мне не нужно само расписание, а тем более для зимы 2010 года, я хочу просто переделать шаблон для своих целей. Что бы Вы сделали на моём месте? Вы можете создать черновой вариант таблицы и вручную повторить в ней дизайн шаблона, но это займёт очень много времени. Либо Вы можете удалить весь текст в шаблоне, но это тоже займёт уйму времени. Гораздо проще скопировать шаблон и сделать Paste Special (Специальная вставка) > Formats (Форматы) на новом листе Вашей рабочей книги. Вуаля!
Теперь Вы можете вводить данные, сохранив все форматы, шрифты, цвета и дизайн.
Вы когда-нибудь теряли уйму времени и сил, кружа вокруг своей таблицы и пытаясь отрегулировать размеры столбцов? Мой ответ – конечно, да! Особенно, когда нужно скопировать и вставить данные из одной таблицы в другую. Существующие настройки ширины столбцов могут не подойти и даже, несмотря на то, что автоматическая настройка ширины столбца – это удобный инструмент, местами он может работать не так, как Вам хотелось бы. Специальная вставка > Ширины столбцов – это мощный инструмент, которым должны пользоваться те, кто точно знает, чего хочет. Давайте для примера рассмотрим список лучших программ US MBA.
Как такое могло случиться? Вы видите, как аккуратно была подогнана ширина столбца по размеру данных на рисунке выше. Я скопировал десять лучших бизнес-школ и поместил их на другой лист. Посмотрите, что получается, когда мы просто копируем и вставляем данные:
Содержимое вставлено, но ширина столбцов далеко не подходящая. Вы хотите получить точно такую же ширину столбцов, как на исходном листе. Вместо того чтобы настраивать ее вручную или использовать автоподбор ширины столбца, просто скопируйте и сделайте Paste Special (Специальная вставка) > Column Widths (Ширины столбцов) на ту область, где требуется настроить ширину столбцов.
Видите, как все просто? Хоть это и очень простой пример, но Вы уже можете представить, как будет полезен такой инструмент, если лист Excel содержит сотни столбцов.
Кроме этого, Вы можете настраивать ширину пустых ячеек, чтобы задать им формат, прежде чем вручную вводить текст. Посмотрите на столбцы E и F на картинке выше. На картинке внизу я использовал инструмент Специальная вставка > Ширины столбцов , чтобы расширить столбцы. Вот так, без лишней суеты Вы можете оформить свой лист Excel так, как Вам угодно!
Помните наш пример с печеньем? Хорошие новости! Гигантская корпорация узнала о нашей благотворительной акции и предложила увеличить прибыль. После пяти недель продаж, они вложатся в нашу благотворительность, так что доход удвоится (станет в два раза больше) по сравнению с тем, какой он был в начале. Давайте вернёмся к той таблице, в которой мы вели учёт прибыли от благотворительной распродажи печенья, и пересчитаем прибыль с учётом новых вложений. Я добавил столбец, показывающий, что после пяти недель продаж прибыль удвоится, т.е. будет умножена на 2 .
Весь доход, начиная с 6-й недели, будет умножен на 2 .Чтобы показать новые цифры, нам нужно умножить соответствующие ячейки столбца C на 2 . Мы можем выполнить это вручную, но будет гораздо приятнее, если за нас это сделает Excel с помощью команды Paste Special (Специальная вставка) > Multiply (Умножить). Для этого скопируйте ячейку F7 и примените команду на ячейки C7:C16 . Итоговые значения обновлены. Отличная работа!
Как видите, инструмент Специальная вставка > Умножить может быть использован в самых разных ситуациях. Точно так же обстоят дела с Paste Special (Специальная вставка) > Divide (Разделить). Вы можете разделить целый диапазон ячеек на определённое число быстро и просто. Знаете, что ещё? При помощи Paste Special (Специальная вставка) с опцией Add (Сложить) или Subtract (Вычесть) Вы сможете быстро прибавить или вычесть число.
Итак, в этом уроке Вы изучили некоторые очень полезные возможности инструмента Специальная вставка , а именно: научились вставлять только значения или форматирование, копировать ширину столбцов, умножать и делить данные на заданное число, а также прибавлять и удалять значение сразу из диапазона ячеек.
Вставляйте и удаляйте строки, столбцы и ячейки для оптимального размещения данных на листе.
Примечание: В Microsoft Excel установлены следующие ограничения на количество строк и столбцов: 16 384 столбца в ширину и 1 048 576 строк в высоту.
Чтобы вставить столбец, выделите его, а затем на вкладке Главная нажмите кнопку Вставить и выберите пункт Вставить столбцы на лист .
Чтобы удалить столбец, выделите его, а затем на вкладке Главная нажмите кнопку Вставить и выберите пункт Удалить столбцы с листа .
Можно также щелкнуть правой кнопкой мыши в верхней части столбца и выбрать команду Вставить или Удалить .
Чтобы вставить строку, выделите ее, а затем на вкладке Главная нажмите кнопку Вставить и выберите пункт Вставить строки на лист .
Чтобы удалить строку, выделите ее, а затем на вкладке Главная нажмите кнопку Вставить и выберите пункт Удалить строки с листа .
Можно также щелкнуть правой кнопкой мыши выделенную строку и выбрать команду Вставить или Удалить .
Выделите одну или несколько ячеек. Щелкните правой кнопкой мыши и выберите команду Вставить .
В окне Вставка выберите строку, столбец или ячейку для вставки.
Например, чтобы вставить новую ячейку между ячейками "Лето" и "Зима":
Щелкните ячейку "Зима".
На вкладке Главная Вставить и выберите команду Вставить ячейки (со сдвигом вниз) .
Новая ячейка добавляется над ячейкой "Зима":
Чтобы вставить одну строку : щелкните правой кнопкой мыши всю строку, над которой требуется вставить новую, и выберите команду Вставить строки .
Чтобы вставить несколько строк, выполните указанные ниже действия. Выделите одно и то же количество строк, над которым вы хотите добавить новые. Щелкните выделенный фрагмент правой кнопкой мыши и выберите команду Вставить строки.
Чтобы вставить один новый столбец, выполните указанные ниже действия. Щелкните правой кнопкой мыши весь столбец справа от того места, куда вы хотите добавить новый столбец. Например, чтобы вставить столбец между столбцами B и C, щелкните правой кнопкой мыши столбец C и выберите команду Вставить столбцы.
Чтобы вставить несколько столбцов, выполните указанные ниже действия. Выделите то же количество столбцов, справа от которых вы хотите добавить новые. Щелкните выделенный фрагмент правой кнопкой мыши и выберите команду Вставить столбцы .
Если вам больше не нужны какие-либо ячейки, строки или столбцы, вот как удалить их:
Выделите ячейки, строки или столбцы, которые вы хотите удалить.
На вкладке Главная щелкните стрелку под кнопкой Удалить и выберите нужный вариант.
При удалении строк или столбцов следующие за ними строки и столбцы автоматически сдвигаются вверх или влево.
Совет: Если вы передумаете сразу после того, как удалите ячейку, строку или столбец, просто нажмите клавиши CTRL+Z, чтобы восстановить их.
Выпадающий список в Excel это, пожалуй, один из самых удобных способов работы с данными. Использовать их вы можете как при заполнении форм, так и создавая дашборды и объемные таблицы. Выпадающие списки часто используют в приложениях на смартфонах, веб-сайтах. Они интуитивно понятны рядовому пользователю.
Кликните по кнопке ниже для загрузки файла с примерами выпадающих списков в Excel:
Представим, что у нас есть перечень фруктов:
Для создания выпадающего списка нам потребуется сделать следующие шаги:
Если вы хотите создать выпадающие списки в нескольких ячейках за раз, то выберите все ячейки, в которых вы хотите их создать, а затем выполните указанные выше действия. Важно убедиться, что ссылки на ячейки являются абсолютными (например, $A$2 ), а не относительными (например, A2 или A$2 или $A2 ).
На примере выше, мы вводили список данных для выпадающего списка путем выделения диапазона ячеек. Помимо этого способа, вы можете вводить данные для создания выпадающего списка вручную (необязательно их хранить в каких-либо ячейках).
Например, представим что в выпадающем меню мы хотим отразить два слова “Да” и “Нет”. Для этого нам потребуется:
После этого система создаст раскрывающийся список в выбранной ячейке. Все элементы, перечисленные в поле “Источник “, разделенные точкой с запятой будут отражены в разных строчках выпадающего меню.
Если вы хотите одновременно создать выпадающий список в нескольких ячейках – выделите нужные ячейки и следуйте инструкциям выше.
Наряду со способами описанными выше, вы также можете использовать формулу для создания выпадающих списков.
Например, у нас есть список с перечнем фруктов:
Для того чтобы сделать выпадающий список с помощью формулы необходимо сделать следующее:
Система создаст выпадающий список с перечнем фруктов.
На примере выше мы использовали формулу =СМЕЩ(ссылка;смещ_по_строкам;смещ_по_столбцам;[высота];[ширина]).
Эта функция содержит в себе пять аргументов. В аргументе “ссылка ” (в примере $A$2) указывается с какой ячейки начинать смещение. В аргументах “смещ_по_строкам ” и “смещ_по_столбцам” (в примере указано значение “0”) – на какое количество строк/столбцов нужно смещаться для отображения данных. В аргументе “[высота] ” указано значение “5”, которое обозначает высоту диапазона ячеек. Аргумент “[ширина] ” мы не указываем, так как в нашем примере диапазон состоит из одной колонки.
Используя эту формулу, система возвращает вам в качестве данных для выпадающего списка диапазон ячеек, начинающийся с ячейки $A$2, состоящий из 5 ячеек.
Если вы используете для создания списка формулу на примере выше, то вы создаете список данных, зафиксированный в определенном диапазоне ячеек. Если вы захотите добавить какое-либо значение в качестве элемента списка, вам придется корректировать формулу вручную. Ниже вы узнаете, как делать динамический выпадающий список, в который будут автоматически загружаться новые данные для отображения.
Для создания списка потребуется:
В этой формуле, в аргументе “[высота ]” мы указываем в качестве аргумента, обозначающего высоту списка с данными – формулу , которая рассчитывает в заданном диапазоне A2:A100 количество не пустых ячеек.
Примечание: для корректной работы формулы, важно, чтобы в списке данных для отображения в выпадающем меню не было пустых строк.
Для того чтобы в созданный вами выпадающий список автоматически подгружались новые данные, нужно проделать следующие действия:
Таблица с данными готова, теперь можем создавать выпадающий список. Для этого необходимо:
В Excel есть возможность копировать созданные выпадающие списки. Например, в ячейке А1 у нас есть выпадающий список, который мы хотим скопировать в диапазон ячеек А2:А6 .
Для того чтобы скопировать выпадающий список с текущим форматированием:
Так, вы скопируете выпадающий список, сохранив исходный формат списка (цвет, шрифт и.т.д). Если вы хотите скопировать/вставить выпадающий список без сохранения формата, то:
После этого, Эксель скопирует только данные выпадающего списка, не сохраняя форматирование исходной ячейки.
Иногда, сложно понять, какое количество ячеек в файле Excel содержат выпадающие списки. Есть простой способ отобразить их. Для этого: