Специальная вставка в Excel. Вставка и удаление ячеек, строк и столбцов Убрать перенос с помощью функции и символа переноса

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

Кликните по кнопке ниже для загрузки файла с примерами выпадающих списков в Excel:

Видеоурок

Как создать выпадающий список в Экселе на основе данных из перечня

Представим, что у нас есть перечень фруктов:

Для создания выпадающего списка нам потребуется сделать следующие шаги:

Перейти на вкладку “Данные ” => раздел “Работа с данными ” на панели инструментов => выбираем пункт “Проверка данных “.

Во всплывающем окне “Проверка вводимых значений ” на вкладке “Параметры ” в типе данных выбрать “Список “:

В поле “Источник ” ввести диапазон названий фруктов =$A$2:$A$6 или просто поставить курсор мыши в поле ввода значений “Источник ” и затем мышкой выбрать диапазон данных:

Если вы хотите создать выпадающие списки в нескольких ячейках за раз, то выберите все ячейки, в которых вы хотите их создать, а затем выполните указанные выше действия. Важно убедиться, что ссылки на ячейки являются абсолютными (например, $A$2 ), а не относительными (например, A2 или A$2 или $A2 ).

Как сделать выпадающий список в Excel используя ручной ввод данных

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

Например, представим что в выпадающем меню мы хотим отразить два слова “Да” и “Нет”. Для этого нам потребуется:

Выбрать ячейку, в которой мы хотим создать выпадающий список;
Перейти на вкладку “Данные ” => раздел “Работа с данными ” на панели инструментов => выбрать пункт “Проверка данных “:

Во всплывающем окне “Проверка вводимых значений ” на вкладке “Параметры ” в типе данных выбрать “Список “:

В поле “Источник ” ввести значение “Да; Нет”.
Нажимаем “ОК “

После этого система создаст раскрывающийся список в выбранной ячейке. Все элементы, перечисленные в поле “Источник “, разделенные точкой с запятой будут отражены в разных строчках выпадающего меню.

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

Как создать раскрывающийся список в Эксель с помощью функции СМЕЩ

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

Например, у нас есть список с перечнем фруктов:

Для того чтобы сделать выпадающий список с помощью формулы необходимо сделать следующее:

Выбрать ячейку, в которой мы хотим создать выпадающий список;
Перейти на вкладку “Данные ” => раздел “Работа с данными ” на панели инструментов => выбрать пункт “Проверка данных “:

Во всплывающем окне “Проверка вводимых значений ” на вкладке “Параметры ” в типе данных выбрать “Список “:

В поле “Источник ” ввести формулу: =СМЕЩ(A$2$;0;0;5)
Нажать “ОК “

Система создаст выпадающий список с перечнем фруктов.

Как эта формула работает?

На примере выше мы использовали формулу =СМЕЩ(ссылка;смещ_по_строкам;смещ_по_столбцам;[высота];[ширина]).

Эта функция содержит в себе пять аргументов. В аргументе “ссылка ” (в примере $A$2) указывается с какой ячейки начинать смещение. В аргументах “смещ_по_строкам ” и “смещ_по_столбцам” (в примере указано значение “0”) – на какое количество строк/столбцов нужно смещаться для отображения данных. В аргументе “[высота] ” указано значение “5”, которое обозначает высоту диапазона ячеек. Аргумент “[ширина] ” мы не указываем, так как в нашем примере диапазон состоит из одной колонки.

Используя эту формулу, система возвращает вам в качестве данных для выпадающего списка диапазон ячеек, начинающийся с ячейки $A$2, состоящий из 5 ячеек.

Как сделать выпадающий список в Excel с подстановкой данных (с использованием функции СМЕЩ)

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

Для создания списка потребуется:

Выбрать ячейку, в которой мы хотим создать выпадающий список;
Перейти на вкладку “Данные ” => раздел “Работа с данными ” на панели инструментов => выбрать пункт “Проверка данных “;
Во всплывающем окне “Проверка вводимых значений ” на вкладке “Параметры ” в типе данных выбрать “Список “;
В поле “Источник ” ввести формулу: =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
Нажать “ОК “

В этой формуле, в аргументе “[высота ]” мы указываем в качестве аргумента, обозначающего высоту списка с данными – формулу , которая рассчитывает в заданном диапазоне A2:A100 количество не пустых ячеек.

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

Как создать выпадающий список в Excel с автоматической подстановкой данных

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

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

На панели инструментов нажимаем пункт “Форматировать как таблицу “:

Из раскрывающегося меню выбираем стиль оформления таблицы:

Нажав клавишу “ОК ” во всплывающем окне, подтверждаем выбранный диапазон ячеек:

Затем, выделим диапазон данных таблицы для выпадающего списка и присвоим ему имя в левом поле над столбцом “А”:

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

Выбрать ячейку, в которой мы хотим создать список;
Перейти на вкладку “Данные ” => раздел “Работа с данными ” на панели инструментов => выбрать пункт “Проверка данных “:

Во всплывающем окне “Проверка вводимых значений ” на вкладке “Параметры ” в типе данных выбрать “Список “:

В поле источник указываем =”название вашей таблицы” . В нашем случае мы ее назвали “Список “:

Готово! Выпадающий список создан, в нем отображаются все данные из указанной таблицы:

Для того чтобы добавить новое значение в выпадающий список – просто добавьте в следующую после таблицы с данными ячейку информацию:

Таблица автоматически расширит свой диапазон данных. Выпадающий список соответственно пополнится новым значением из таблицы:

Как скопировать выпадающий список в Excel

В Excel есть возможность копировать созданные выпадающие списки. Например, в ячейке А1 у нас есть выпадающий список, который мы хотим скопировать в диапазон ячеек А2:А6 .

Для того чтобы скопировать выпадающий список с текущим форматированием:

нажмите левой клавишей мыши на ячейку с выпадающим списком, которую вы хотите скопировать;
CTRL+C ;
выделите ячейки в диапазоне А2:А6 , в которые вы хотите вставить выпадающий список;
нажмите сочетание клавиш на клавиатуре CTRL+V .

Так, вы скопируете выпадающий список, сохранив исходный формат списка (цвет, шрифт и.т.д). Если вы хотите скопировать/вставить выпадающий список без сохранения формата, то:

нажмите левой клавишей мыши на ячейку с выпадающим списком, который вы хотите скопировать;
нажмите сочетание клавиш на клавиатуре CTRL+C ;
выберите ячейку, в которую вы хотите вставить выпадающий список;
нажмите правую кнопку мыши => вызовите выпадающее меню и нажмите “Специальная вставка “;

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

Нажмите “ОК “

После этого, Эксель скопирует только данные выпадающего списка, не сохраняя форматирование исходной ячейки.

Как выделить все ячейки, содержащие выпадающий список в Экселе

Иногда, сложно понять, какое количество ячеек в файле Excel содержат выпадающие списки. Есть простой способ отобразить их. Для этого:

Нажмите на вкладку “Главная ” на Панели инструментов;
Нажмите “Найти и выделить ” и выберите пункт “Выделить группу ячеек “:

В диалоговом окне выберите пункт “Проверка данных “. В этом поле есть возможность выбрать пункты “Всех ” и “Этих же “. “Всех ” позволит выделить все выпадающие списки на листе. Пункт “этих же ” покажет выпадающие списки схожие по содержанию данных в выпадающем меню. В нашем случае мы выбираем “всех “:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Комбинации горячих клавиш:
Ctrl+C - скопировать
Ctrl+X - вырезать
Ctrl+V - вставить

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

Чтобы эта команда стала доступной, необходимо:

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

1. Вставка информации из программы Excel

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

Группа переключателей Вставить:

Все - выбор этой опции эквивалентен использованию команды Вставить. При этом копируется содержимое ячейки и формат.

Формулы - выбор этой опции позволяет вставить только формулы в том виде, в котором они вводились в строку формул.

Значения - выбор данной опции позволяет скопировать результаты расчетов по формулам.

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

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

Условия на значения - если для конкретной ячейки был создан критерий допустимости данных (с помощью команды Данные | Проверка), то этот критерий можно скопировать в другую ячейку или диапазон, воспользовавшись данной опцией.

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

Ширины столбцов - можно скопировать информацию о ширине столбца из одного столбца в другой.

Совет! Эту функцию удобно использовать при копировании готовой таблицы с одного листа на другой.

Часто после вставки скопированной таблицы на новый лист приходится корректировать ее размеры.

Лист 1 Исходная таблица

Лист 2 Вставка

Чтобы этого избежать, воспользуйтесь вставкой Ширины столбцов. Для этого:

Скопируйте исходную таблицу.

Перейдите на новый лист и выделите ячейку для вставки.

Откройте диалоговое окно Специальная вставка, отметьте опцию Ширины столбцов и нажмите ОК.

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

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

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

Переключатели из группы Операция позволяют выполнять арифметические операции.

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

Задача 1. Прибавить 5 к каждому значению в ячейках А3:А12

Вносим значение «5» в любую ячейку(в данном примере - С1).

Копируем значение ячейки С1.

Выделяем диапазон А3:А12 и открываем диалоговое окно Специальная вставка

4. Выбираем операцию Сложить и нажимаем ОК.

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

Задача 2 . Уменьшить на 10% цены на товары, находящиеся в диапазоне Е4:Е10 (не пользуясь формулами).

С учетом скидки новая цена будет составлять 90% прежней, следовательно, вносим значение 90% в любую из ячеек (в данном примере - F2).

Скопируем содержимое ячейки F2 в буфер обмена.

Выделим диапазон Е4:Е10 и откроем диалоговое окно Специальная вставка

Вставить только в видимые строки в Excel числа, формулы, текст можно несколькими способами. Когда нужно вставить числа, формулы, текст не во все строки таблицы, можно воспользоваться фильтром. Как установить фильтр и как фильтровать в Excel, смотрите в статье «Фильтр в Excel ». Но, чтобы вставить данные только в видимые ячейки, нужны свои способы, особенно, если много строк.
Первый способ - обычный.
Возьмем такую таблицу. Таблица будет одна для всех примеров.
Уберем фильтром все цифры 2 из таблицы. В оставшиеся видимые ячейки поставим цифру 600. В ячейку В2 ставим число 600, затем копируем его вниз по столбцу (тянем за правый нижний угол ячейки В2). Значения скопировались только в видимые ячейки. Точно также можно вставлять и формулы. Мы в ячейке С2 пишем такую формулу. =А2*10
Получилось так.
Отменим фильтр. Получилась такая таблица.
Формула и цифры вставились только в отфильтрованные строки.
Второй способ.
Также отфильтруем данные. В первой ячейке пишем число, формулу, текст, т.д. Теперь, если строк тысячи, то выделяем ячейки так: нажимаем клавиши «Ctrl» + «Shift» + кнопку (стрелочку) вниз (или кнопку вверх, зависит от того, где хотим выделить ячейки – ниже или выше ячейки, в которой написали число).
Теперь, или нажимаем сочетание клавиш «Ctrl» + G, или клавишу F5. Выйдет диалоговое окно «Переход». Нажимаем кнопку «Выделить…». И, в новом диалоговом окне «Выделение группы ячеек» ставим галочку у слов «Только видимые ячейки». Нажимаем «ОК». Затем вставляем как обычно.

Ещё один способ вызвать диалоговое окно "Выделение группы ячеек". На закладке «Главная» в разделе «Редактирование» нажимаем на кнопку «Найти и выделить». В появившемся списке нажимаем на функцию «Выделение группы ячеек».

Чтобы заполнить видимые ячейки в выделенных столбцах Excel , нажимаем сочетание клавиш «Ctrl» + D. И все выделенные столбцы заполнятся данными или формулой, как в первой ячейке. В нашем примере, мы написали число800 в ячейку D2 столбца D.

Третий способ.
В новом столбце (в нашем примере – столбец Е) выделяем ячейки. Нажимаем клавишу F5. Выйдет диалоговое окно «Переход». Нажимаем кнопку «Выделить…». И, в новом диалоговом окне «Выделение группы ячеек», ставим галочку у слов «Только видимые ячейки». Нажимаем «ОК». Теперь, не отменяя выделения, в первой ячейке столбца (у нас – Е2) вводим формулу, цифру, т.д. Нажимаем сочетание клавиш «Ctrl» + «Enter».

Возможно, будет полезно почитать: