Ref-sgain.ru

Программы и сервисы
4 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как скопировать ячейки, если столбец содержит определенное значение / текст в Excel?

Вставка текста в ячейку с формулой в Microsoft Excel

Текст вместе с формулой в Microsoft Excel

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

Процедура вставки текста около формулы

Если вы просто попытаетесь вставить текст в ячейку с функцией, Excel сообщит об ошибке в формуле и не разрешит вставку. Однако существует два способа вставить текст рядом с формульным выражением. Первый — это использование амперсанда, а второй — использование функции SHIFT.

Ошибка в формуле в Microsoft Excel

Способ 1: использование амперсанда

Самый простой способ сделать это — использовать символ амперсанда (&). Этот символ устанавливает логическое разделение между данными формулы и текстовым выражением. Давайте посмотрим, как этот метод можно применить на практике.

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

Таблица с формулой в Microsoft Excel

  1. Активируйте ячейку, содержащую выражение формулы. Для этого дважды щелкните левой кнопкой мыши или выделите его и нажмите функциональную клавишу F2. Можно также просто выделить ячейку, а затем поместить курсор в строку формул.

Ячейка активирована в Microsoft Excel

Текст дописан в Microsoft Excel

Текст выведен в Microsoft Excel

Попытка поставить пробел вручную в Microsoft Excel

Введение пробела в Microsoft Excel

Формула и текст разделены пробелом в Microsoft Excel

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

При написании текста перед формулой соблюдайте следующий синтаксис. Заключите текст сразу после знака «=» в кавычки. После этого закройте кавычки. Поставьте знак амперсанда. Затем, если нужно сделать пробел, откройте кавычки, поставьте пробел и закройте кавычки. Нажимаем на клавишу Enter.

Написание текста перед формулой в Microsoft Excel

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

Запись текста c функцией в Microsoft Excel

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

Ссылка на ячейку содержащую текст в формуле в Microsoft Excel

Способ 2: применение функции СЦЕПИТЬ

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

Этот оператор может иметь в общей сложности от 1 до 255 аргументов. Каждый аргумент представляет собой текст (включая числа и любые другие символы) или ссылки на ячейки, содержащие текст.

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

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

Переход в Мастер функций в Microsoft Excel

Перемещение в окно аргументов функции СЦЕПИТЬ в программе Microsoft Excel

Поместите курсор в поле «Текст1». Введите слово «Всего». Текстовые фразы можно набирать без инвертированных запятых, так как программа сама их вставляет.

Затем перейдите к полю «Текст2». Установите там курсор. Здесь нам нужно указать значение, которое выводит формула, то есть дать ссылку на ячейку, в которой она содержится. Ввод адреса вручную — это один из методов, но лучше навести курсор на ячейку с формулой и щелкнуть по ней. Вы автоматически увидите адрес в поле аргумента.

Читайте так же:
Как скопировать формулу ячейки только без форматирования в Excel?

Введите слово «руб.» в поле «Текст3».

Окно аргументов функции СЦЕПИТЬ в Microsoft Excel

Результат обработки данных функцией СЦЕПИТЬ в Microsoft Excel

Между кавычками должен быть пробел. В строке функции обычно должно быть следующее выражение:

=СЦЕПИТЬ(«Итого»;» «;D2;» «;»рублей»)

Значения разделены пробелами в Microsoft Excel

Скрытие столбца в Microsoft Excel

Столбец скрыт в Microsoft Excel

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

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

Помимо этой статьи, на сайте еще 12677 инструкций.
Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам.

Поблагодарите автора, поделившись этой статьей в социальной сети.

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

Найти и заменить в Excel

Поиск и замена данных — одна из наиболее часто используемых операций в Excel. Им пользуются даже новички. На ленте есть большая кнопка.

Кнопка поиска на ленте

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

Выберите Найти и Выбрать * Найти (кнопка на рисунке выше), затем нажмите Главная * Редактировать * Найти. Кроме того, функцию поиска можно запустить с клавиатуры, нажав комбинацию Ctrl+F. Вы увидите диалоговое окно «Найти и заменить».

Диалоговое окно Найти и Заменить

В отдельном поле указывается информация (комбинация символов), по которой будет производиться поиск. Если не используется подстановочный знак или панель инструментов (см. ниже), Excel будет искать строгое совпадение указанных символов. Есть два варианта получения результатов поиска: получить все результаты сразу — кнопка Найти все; или получать по одному значению за раз — кнопка Найти далее.

После запуска поиска Excel быстро сканирует содержимое листа (или указанного диапазона) на предмет наличия нужной комбинации символов. Если такая комбинация найдена, Excel очистит все найденные ячейки, нажав кнопку Найти все.

Найти все

Если щелкнуть любое значение в нижней части окна и нажать Ctrl+A, то соответствующие ячейки в диапазоне поиска будут выделены.

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

Поиск нестрогого соответствия символов

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

Для решения этих проблем очень хорошо подходят подстановочные знаки, которые сообщают Excel о сомнительных местах. Различные символы могут быть скрыты под подстановочными знаками, и Excel видит только их относительное положение в поисковой фразе. Существует два таких знака подстановки: звездочка «*» (любое количество неизвестных символов) и вопросительный знак «? (символ «?» означает неизвестный символ).

Читайте так же:
Как скопировать данные в следующую пустую строку другого листа в Excel?

Так, если в большой базе клиентов нужно найти человека по фамилии Иванов, то поиск может выдать несколько десятков значений. Это явно не то, что вам нужно. К поиску можно добавить имя, но оно может быть внесено самым разным способом: И.Иванов, И. Иванов, Иван Иванов, И.И. Иванов и т.д. Используя джокеры, можно задать известную последовательно символов независимо от того, что находится между. В нашем примере достаточно ввести и*иванов и Excel отыщет все выше перечисленные варианты записи имени данного человека, проигнорировав всех П. Ивановых, А. Ивановых и проч. Секрет в том, что символ «*» сообщает Экселю, что под ним могут скрываться любые символы в любом количестве, но искать нужно то, что соответствует символам «и» + что-еще + «иванов». Этот прием значительно повышает эффективность поиска, т.к. позволяет оперировать не точными критериями.

Если с пониманием искомой информации совсем туго, то можно использовать сразу несколько звездочек. Так, в списке из 1000 позиций по поисковой фразе мол*с*м*уход я быстро нахожу позицию «Мол-ко д/сн мак. ГАРНЬЕР Осн.уход д/сух/чув.к. 200мл» (это сокращенное название от «Молочко для снятия макияжа Гараньер Основной уход….»). При этом очевидно, что по фразе «молочко» или «снятие макияжа» поиск ничего бы не дал. Часто достаточно ввести первые буквы искомых слов (которые наверняка присутствуют), разделяя их звездочками, чтобы Excel показал чудеса поиска. Главное, чтобы последовательность символов была правильной.

Есть еще один джокер — символ «? Он может скрывать только один неизвестный символ. Следующая формула найдет все ячейки, содержащие последовательность 106, 116, 126, 136 и т.д., если в качестве критерия поиска указать 1?6. А если вы укажете 1? 6, то Excel найдет содержимое ячеек, содержащих 1006, 1016, 1106, 1236, 1486 и т.д. Таким образом, джокер «?» накладывает более строгие ограничения на поиск, при котором учитывается количество пропущенных символов (равное количеству вопросов «?»).

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

Продвинутый поиск

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

Параметры в поиске

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

Поиск форматов

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

Выбрать формат

Так, например, можно найти все объединенные клетки, что очень сложно найти каким-либо другим способом.

Форматный поиск — это хорошо, но чаще всего приходится искать конкретные значения. Здесь Excel предоставляет дополнительные возможности для расширения и уточнения параметров поиска.

Читайте так же:
Как создать динамический список имен листов в Excel?

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

Искать на листе

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

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

В следующем выпадающем списке находится замечательная возможность поиска по формулам, значениям, а также примечаниям. По умолчанию Excel производит поиск в формулах либо, если их нет, в содержимом ячейки. Например, если искать фамилию Иванов, а фамилия эта есть результат формулы (копируется из соседнего листа), то поиск нечего не даст, т.к. в ячейке нет искомого перечня символов. По той же причине не удастся отыскать число, являющееся результатом работы какой-либо функции. Поэтому бывает смотришь в упор на ячейку, видишь искомое значение, а Excel его почему-то не видит. Это не глюк, это настройка поиска. Измените данный параметр на Значения и поиск будет осуществляться по тому, что отражено в ячейке, независимо от содержимого. Например, если в ячейке содержится результат вычисления 1/6 (как значение, а не формула) и при этом формат отражает только 3 знака после запятой (т.е 0,167), то поиск символов «167» при выборе параметра Формулы эту ячейку не обнаружит (реальное содержимое ячейки — это 0,166666…), а при выборе Значения поиск увенчается успехом (искомые символы совпадают с тем, что отражается в ячейке). И последний пункт в данном списке – Примечания. Поиск осуществляется только в примечаниях. Очень может помочь, т.к. примечания часто скрыты.

Кроме того, диалоговое окно поиска предоставляет два дополнительных флажка: Проверить регистр и Вся ячейка. В Excel по умолчанию регистры игнорируются, но вы можете изменить «Иванов» на «Иванов». Кроме того, флажок Целая ячейка полезен, если вы ищете не ячейку с указанным фрагментом, а ячейку, полностью состоящую из искомых символов. Например, как найти ячейки, содержащие только 0? Обычный поиск не сработает, потому что вы получите и 10, и 100. Проверка ячейки целиком все исправит.

Поиск и замена данных

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

Заменить

В Excel можно также применить горячую клавишу найти и заменить Ctrl+H.

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

Диалоговое окно Заменить

Как и в случае простого поиска, вы можете изменить формат.

Заменить формат

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

Читайте так же:
Как скрыть заголовки строк и столбцов со всех листов?

Напоследок рассмотрим один классный трюк с поиском и заменой. Многие знают, что в ячейку можно вставить разрыв строк с помощью комбинации Alt+Enter.
Разделение строк в ячейке
А как быстро удалить все разрывы строк? Обычно это делают вручную. Однако ловкое использование поиска и замены сэкономит много времени. Вызываем команду поиска и замены с помощью комбинации Ctrl+H. Теперь в строке поиска нажимаем Ctrl+J — это символ разрыва строки — на экране появится точка. В строке замены указываем, например, пробел.

Поиск и замена разрывов строк в ячейке

При нажатии кнопки OK все переводы строк будут заменены пробелами.

Замена разделителей строк пробелами

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

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

Как скопировать ячейки, если столбец содержит определенное значение / текст в Excel?

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

  • Копирование ячеек, если столбец содержит определенное значение/текст с помощью команды Фильтр.
  • Копирование ячеек, если столбец содержит определенное значение/текст, с помощью команды Найти
  • Копирование ячеек, если столбец содержит определенное значение/текст, с помощью Kutools for Excel
  • Повторное использование чего угодно: добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
  • Более 20 функций текста: извлечение числа из Текстовая строка; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
  • Инструменты слияния: несколько книг и листов в одну; Объединить несколько ячеек/строк/столбцов без потери данных; Объедините повторяющиеся строки и суммируйте.
  • Инструменты разделения: разделение данных на несколько листов в зависимости от значения; Из одной книги в несколько файлов Excel, PDF или CSV; Один столбец в несколько столбцов.
  • Вставить пропуск скрытых/отфильтрованных строк; Подсчет и сумма по цвету фона; Массовая отправка персонализированных писем нескольким получателям.
  • Суперфильтр: создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделе, дню, частоте и т. Д. Фильтр жирным шрифтом, формулами, комментарием …
  • Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.
Копировать ячейки, если столбец содержит определенное значение/текст с помощью команды «Фильтр»

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

1 . Выберите столбец, в который вы будете копировать ячейки, если столбец содержит определенное значение или текст, а затем нажмите Data >Фильтр .

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

3 . В открывшемся диалоговом окне Custom AutoFilter введите конкретный текст в поле за полем contains и нажмите кнопку OK .

Теперь все ячейки, содержащие определенный текст, отфильтровываются сразу.

Читайте так же:
Как создать / сделать общую книгу в Excel?

4 . Выделите все отфильтрованные ячейки в определенном столбце и скопируйте их, нажав Ctrl + C .

5 . Выделите одну пустую ячейку и вставьте эти ячейки, нажав Ctrl + V.

Легко выбирать/выделять/копировать всю строку, если столбец содержит то же значение, что и значения в другом списке в Excel

Kutools для Excel Утилита Выбрать одинаковые и разные ячейки позволяет пользователям Excel сравнивать два столбца а затем легко выбрать/скопировать/выделить все строки на основе тех же значений или разных значений. 30-дневная бесплатная пробная версия полнофункциональной версии!

Копировать ячейки, если столбец содержит определенное значение/текст, с помощью команды поиска

Используя команду «Найти», вы можете легко найти все ячейки, содержащие одинаковое значение или текст, и легко скопировать все эти ячейки.

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

2 . Откройте диалоговое окно «Найти и заменить», одновременно нажав клавиши Ctrl + F , затем в диалоговом окне «Найти и заменить» введите конкретный текст в поле Найдите что и нажмите кнопку Найти все .

Примечание . Вы также можете открыть диалоговое окно» Найти и заменить «, нажав Home >Найти & Выберите >Найти .

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

3 . Выделите все найденные ячейки, нажав Ctrl + A , и закройте диалоговое окно Найти и заменить. Выделенные ячейки в определенном столбце включают все ячейки, содержащие определенный текст.

4 . Скопируйте всю выделенную ячейку, нажав Ctrl + C , а затем выделите пустую ячейку и вставьте их, нажав Ctrl + A .

Копировать ячейки, если столбец содержит определенное значение/текст с помощью Kutools for Excel

Kutools for Excel — включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Получить сейчас

1 . Выберите указанный столбец и нажмите Kutools >Выбрать >Выбрать специальные ячейки .

2 . В открывшемся диалоговом окне «Выбор специальных ячеек»

(1) В В разделе Тип выбора установите флажок Ячейка ;
(2) В разделе Определенный тип нажмите на первое поле и выберите Содержит из раскрывающегося списка, а затем введите указанный текст (в нашем случае введите sum) в следующее поле:
(3) Нажмите кнопку ОК . Затем появляется вторая строка Select Specific Cells, показывающая, сколько ячеек выбрано. Нажмите кнопку OK , чтобы закрыть его.

Ячейки, содержащие указанное значение/текст, теперь выделены. Нажмите Ctrl + C, чтобы скопировать их; выделите ячейку, куда вы хотите вставить их, и нажмите Ctrl + V .

Kutools for Excel — включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Получить сейчас

Демонстрация: копирование ячеек, если столбец содержит определенное значение/текст в Excel

Возможно, вы заметили, что Microsoft Excel не поддерживает одновременное копирование нескольких непоследовательных ячеек (которые остаются в разных столбцах). Но копировать эти ячейки/выделения по одной — трудоемко и утомительно. Copy Ranges Kutools for Excel может облегчить вам эту задачу, как показано на скриншоте ниже. Бесплатная 30-дневная пробная версия полной версии.

Статьи по теме

Как проверить или узнать, содержит ли ячейка определенную строку / текст / слово в Excel?

Как в Excel скопировать строки, содержащие определенный текст или значения?

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector