Наверное, многие неопытные пользователи пытались скопировать в Экселе какие-нибудь данные, но в результате действий у них на выходе получалось или совсем другое значение, или ошибка. Это связано с тем, что в первичном диапазоне копирования находилась формула, и именно она была вставлена, а не значение. Подобных проблем удалось бы избежать, если бы эти пользователи были знакомы с таким понятием, как «Специальная вставка» . С её помощью можно выполнять также много других задач, в том числе арифметических. Давайте разберемся, что собой представляет данный инструмент и как с ним работать.
Специальная вставка, прежде всего, предназначена для того, чтобы вставить определенное выражение на лист Excel в том виде, в каком это нужно пользователю. С помощью этого инструмента можно вставить в ячейку не все скопированные данные, а только отдельные свойства (значения, формулы, формат и т.д.). Кроме того, используя инструменты, можно производить арифметические действия (сложение, умножение, вычитание и деление), а также транспонировать таблицу, то есть, менять в ней местами строки и столбцы.
Для того, чтобы перейти в специальную вставку, прежде всего, нужно выполнить действие по копированию.
Способ 1: работа со значениями
Если вам нужно перенести значения ячеек, результат в которых выводится с помощью вычислительных формул, то специальная вставка как раз предназначена для такого случая. Если вы примените обычное копирование, то скопируется формула, а значение, выводящиеся в ней, может оказаться совсем не тем, которое вам нужно.
Способ 2: копирование формул
Но существует и обратная ситуация, когда нужно скопировать именно формулы.
Но существует возможность произвести перенос формул с сохранением формата чисел или даже с полным сохранением исходного форматирования.
Способ 3: перенос форматирования
Если пользователю не нужно переносить данные, а он только хочет скопировать таблицу, чтобы заполнить её совершенно другой информацией, то в этом случае можно воспользоваться определенным пунктом специальной вставки.
Способ 4: копирование таблицы с сохранением размера столбцов
Не секрет, что если мы выполним простое копирование таблицы, то не факт, что все ячейки новой таблицы смогут вместить в себя всю информацию исходника. Исправить данную ситуацию при копировании можно также с помощью специальной вставки.
Способ 5: вставка рисунка
Благодаря возможностям специальной вставки можно произвести копирование любых данных, отображаемых на листе, в том числе и таблицы, как рисунка.
В окне специальной вставки подобную операцию выполнить нельзя.
Способ 6: копирование примечаний
Посредством специальной вставки можно осуществлять быстрое копирование примечаний.
Способ 7: транспонирование таблицы
С помощью специальной вставки можно производить операцию транспонирования таблиц, матриц и других объектов, в которых нужно поменять местами столбцы и строки.
Способ 8: использование арифметических действий
Посредством описываемого нами инструмента в Excel также можно выполнять распространенные арифметические действия:
- Сложение;
- Умножение;
- Вычитание;
- Деление.
Посмотрим, как применяется данный инструмент на примере умножения.
По такому же принципу можно выполнять деление, сложение и вычитание. Только для этого в окне нужно будет переставить переключатель соответственно в позицию «Разделить» , «Сложить» или «Вычесть» . В остальном все действия аналогичны вышеописанным манипуляциям.
Как видим, специальная вставка является очень полезным инструментом для пользователя. С его помощью можно производить копирование не только всего блока данных в ячейке или в диапазоне, а разделив их на различные слои (значения, формулы, форматирование и т.д.). При этом существует возможность комбинирования этих слоев друг с другом. Кроме того, посредством этого же инструмента можно выполнять арифметические действия. Безусловно, приобретение навыков работы с данной технологией значительно поможет пользователям на пути освоения программы Excel в целом.
Довольно часто возникает вопрос, как сделать перенос на другую строку внутри ячейки в Экселе? Этот вопрос возникает когда текст в ячейке слишком длинный, либо перенос необходим для структуризации данных. В таком случае бывает не удобно работать с таблицами. Обычно перенос текста осуществляется с помощью клавиши Enter. Например, в программе Microsoft Office Word. Но в Microsoft Office Excel при нажатии на Enter мы попадаем на соседнюю нижнюю ячейку.
Итак нам требуется осуществить перенос текста на другую строку. Для переноса нужно нажать сочетание клавиш Alt+ Enter . После чего слово, находящееся с правой стороны от курсора перенесется на следующую строку.
Автоматический перенос текста в Excel
В Экселе на вкладке Главная в группе Выравнивание есть кнопка «Перенос текста». Если выделить ячейку и нажать эту кнопку, то текст в ячейке будет переноситься на новую строку автоматически в зависимости от ширины ячейки. Для автоматического переноса требуется выполнить простое нажатие кнопки.
Убрать перенос с помощью функции и символа переноса
Для того, что бы убрать перенос мы можем использовать функцию ПОДСТАВИТЬ.
Функция заменяет один текст на другой в указанной ячейке. В нашем случае мы будем заменять символ пробел на символ переноса.
Синтаксис формулы:
ПОДСТАВИТЬ (текст;стар_текст;нов_текст;[номер вхождения])
Итоговый вид формулы:
ПОДСТАВИТЬ(A1;СИМВОЛ(10); » «)
- А1 – ячейка содержащая текст с переносами,
- СИМВОЛ(10) – символ переноса строки,
- » » – пробел.
Если же нам наоборот требуется вставить символ переноса на другую строку, вместо пробела проделаем данную операцию наоборот.
ПОДСТАВИТЬ(A1; » «;СИМВОЛ(10))
Что бы функция работало корректно во вкладке Выравнивание (Формат ячеек) должен быть установлен флажок «Переносить по словам».
Перенос с использование формулы СЦЕПИТЬ
Для решения нашей проблемы можно использовать формулу СЦЕПИТЬ.
Синтаксис формулы:
СЦЕПИТЬ (текст1,[текст2]…)
У нас имеется текст в ячейках A1 и B1. Введем в B3 следующую формулу:
СЦЕПИТЬ(A1;СИМВОЛ(10);B1)
Как в примере, который я приводил выше, для корректной работы функции в свойствах требуется установить флажок «переносить по словам».
Выпадающий список в 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 содержат выпадающие списки. Есть простой способ отобразить их. Для этого:
- Нажмите на вкладку “Главная ” на Панели инструментов;
- Нажмите “Найти и выделить ” и выберите пункт “Выделить группу ячеек “:
- В диалоговом окне выберите пункт “Проверка данных “. В этом поле есть возможность выбрать пункты “Всех ” и “Этих же “. “Всех ” позволит выделить все выпадающие списки на листе. Пункт “этих же ” покажет выпадающие списки схожие по содержанию данных в выпадающем меню. В нашем случае мы выбираем “всех “:
Всем известна возможность копирования и вставки данных через Буфер обмена операционной системы.
Комбинации горячих клавиш:
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.
Вставка статической информации
Один из способов получить данные из веб-страницы на лист - просто выделить текст в браузере, нажать Ctrl+C , чтобы скопировать его в буфер обмена, а затем вставить текст в таблицу. Результат может быть разным, в зависимости от того, какой браузер вы используете. Если это Internet Explorer, то вставленные данные будут, вероятно, очень похожи на оригинал - в комплекте с настройками форматирования, гиперссылками и графикой.
Если вы используете браузер, отличный от Internet Explorer, то, выбрав Главная Буфер обмена Вставить , можно вставить все, что вы скопировали с веб-страницы, в одну ячейку, а это, скорее всего, не то, чего вы хотите. Решение состоит в том, чтобы выбрать команду Главная Буфер обмена Вставить Специальная вставка , а затем пробовать различные варианты вставки.
Вставка обновляемой информации
Если вы хотите регулярно получать доступ к обновленным данным из веб-страницы, создайте веб-запрос. На рис. 176.1 показан сайт, который содержит курсы валют в таблице с тремя столбцами. Выполнив следующие шаги, можно создать веб-запрос, позволяющий извлекать эту информацию, а затем обновлять ее в любое время одним щелчком кнопкой мыши.
- Выберите Данные Получение внешних данных Из Интернета Создание веб-запроса .
- В поле Адрес введите URL сайта и нажмите кнопку Пуск . Для этого примера URL-адрес веб-страницы, показанной на рис. 176.1, будет таким: http://cbr.ru. Обратите внимание, что диалоговое окно Создание веб-запроса содержит мини-браузер (Internet Explorer). Вы можете переходить по ссылкам и посещать сайты, пока не найдете данные, которые вас заинтересуют. Когда веб-страница отображается в окне Создание веб-запроса , вы видите одну или несколько желтых стрелок, которые соответствуют различным элементам на веб-странице.
- Щелкните на желтой стрелке, и она превратится в зеленый флажок, который указывает, что данные этого элемента будут импортированы. Вы можете импортировать столько элементов, сколько нам нужно. Для этого
примера я щелкну на стрелке рядом с таблицей курсов. - Нажмите кнопку Импорт для открытия диалогового окна Импорт данных .
- В окне Импорт данных укажите место для импортированных данных. Это может быть ячейка в существующем или новом листе.
- Нажмите кнопку ОК , и Excel импортирует данные (рис. 176.2).
По умолчанию импортированные данные - это веб-запрос. Чтобы обновить информацию, щелкните правой кнопкой мыши на любой ячейке импортированного диапазона и выберите в контекстном меню команду Обновить . Если вы не хотите создавать обновляемый запрос, укажите это в шаге 5 предыдущего списка действии. В окне Импорт данных нажмите кнопку Свойства и снимите флажок сохранить определение запроса.
Открытие веб-страницы напрямую
Еще один способ получить данные веб-страницы на лист - открыть URL-адрес напрямую, с помощью команды Файл Открыть . Просто введите полный URL-адрес в поле Имя файла и нажмите кнопку Открыть . Результат будет отличаться в зависимости от того, какая разметка у веб-страницы, но в большинстве случаев он вас удовлетворит. Иногда таким способом извлекается довольно много посторонней информации.
