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

Как в Excel добавить запятую перед числом?

Подстановочные знаки (символы *, ? и

Доброе утро, дорогие читатели блога TutorExcel.ru!

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

  • * (звездочка); Обозначает любое произвольное количество символов. Например, поиск по фразе «*ник» найдет слова типа «понедельник», «всадник», «источник» и т.д.
  • ? (вопросительный знак); Обозначает один произвольный символ. К примеру, поиск по фразе «ст?л» найдет «стол», «стул» и т.д.

(тильда), за которой следует *, ? или ?

. Обозначает конкретный символ *, ? или

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

» и искать по фразе «хор

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

Фильтрация данных

Рассмотрим пример. Предположим, что у нас имеется список сотрудников компании и мы хотим отфильтровать только тех сотрудников, у которых фамилии начинаются на конкретную букву (к примеру, на букву «п»):

Список сотрудников

Для начала добавляем фильтр на таблицу (выбираем вкладку Главная ->Редактирование ->Сортировка и фильтр или нажимаем сочетание клавиш Ctrl + Shift + L).
Для фильтрации списка воспользуемся символом звездочки, а именно введем в поле для поиска «п*» (т.е. фамилия начинается на букву «п», после чего идет произвольный текст):

Применение фильтра

Фильтр определил 3 фамилии удовлетворяющих критерию (начинающиеся с буквы «п»), нажимаем ОК и получаем итоговый список из подходящих фамилий:

Фильтрация данных

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

Применение в функциях

Как упоминалось выше, подстановочные знаки Excel могут использоваться в качестве критерия при сравнении текста в различных функциях Excel (например, СЧЁТЕСЛИ, СУММЕСЛИ, СУММЕСЛИН, ВПР, ВПР и других).

Повторим задачу из предыдущего примера и подсчитаем количество сотрудников компании, фамилии которых начинаются на букву «п».
Воспользуемся функцией СЧЁТЕСЛИ, которая позволяет посчитать количество ячеек соответствующих указанному критерию.
В качестве диапазона данных укажем диапазон с сотрудниками (A2:A20), а в качестве критерия укажем запись «п*» (т.е. любая фраза начинающаяся на букву «п»):

Сравнение текста

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

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

Функция НАЙТИ

Другим примером является аналогичная функция ПОИСК, где необходимо четко указать, что речь идет о знаке обслуживания, по которому ведется поиск:

Функция ПОИСК

Как вы видите, результат функции тот же, но использование подстановочных знаков отличается.

Инструмент «Найти и заменить»

Подстановочные знаки в Excel также можно использовать для поиска и замены текстовых значений в инструменте «Найти и заменить» (комбинация клавиш Ctrl + F для поиска и Ctrl + H для замены).

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

Чтобы несколько раз не искать данные по словам «молоко» или «малоко», при поиске воспользуемся критерием «м?локо» (т.е. вторая буква — произвольная):

Инструмент "Найти и заменить"

При этом не стоит забывать, что с помощью данного инструмента можно не только искать текст, но и заменять его (к примеру, заменить «м?локо» на «молоко»).

Как заменить звездочку «*» в Excel?

Практически наверняка каждый сталкивался со следующей ситуацией — в тексте присутствует символ звездочки, который необходимо удалить или заменить на какой-либо другой текст.
Однако при попытке заменить звездочку возникают трудности — при замене меняются абсолютно весь текст, что естественно и логично, так как Excel воспринимает символ «*» как любой произвольный текст.
Но мы теперь уже знаем как с этим бороться, поэтому в поле Найти указываем текст «

(явно показываем, что звездочка является специальным символом), а в поле Заменить на указываем на что заменяем звездочку, либо оставляем поле пустым, если хотим удалить звездочку:

Удаление звездочки

Аналогичная ситуация и при замене или удалении вопросительного знака и тильды.
Производя замену «

(для тильды — «

») мы также без проблем сможем заменить или удалить спецсимвол.

Замена запятых на точки в Excel: 5 методов

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

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

  • Метод 1: Используйте «Найти и заменить»
  • Метод 2: Используйте «Вставить»
  • Метод 3: Настройте параметры Excel
  • Метод 4: Используйте специальные макросы
  • Метод 5: Измените системные настройки компьютера
  • Выход

Метод 1: применяем инструмент “Найти и заменить”

Этот метод является наиболее популярным и включает в себя использование инструмента «Найти и заменить»:

Инструмент Найти и заменить в Эксель

  1. Любым удобным способом выделите диапазон ячеек, в которых необходимо заменить все запятые на полные точки. На основной вкладке в блоке «Правка» нажмите на значок «Найти и выделить» и остановитесь на опции «Заменить». Вы также можете использовать комбинацию клавиш Ctrl+H для запуска этого инструмента. Примечание: Если не выбрать перед использованием инструмента, поиск будет произведен по всему содержимому листа, а запятые будут заменены полными остановками, что не всегда необходимо.

Метод 2: используем функцию “Подставить”

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

  1. Встаем в незаполненную ячейку рядом с той, которая содержит запятую (в той же строке, но не обязательно именно в соседней). Затем кликаем по значку “Вставить функцию” с левой стороны от строки формул.Вставка функции в ячейку таблицы Эксель
  2. В открывшемся окне Вставки функции щелкаем по текущей категории и выбираем “Текстовый” (также подойдет “Полный алфавитный перечень”). В предложенном списке отмечаем оператор “ПОДСТАВИТЬ”, после чего жмем OK.Выбор оператора ПОДСТАВИТЬ в Excel
  3. Появится окно, в котором требуется заполнить аргументы функции:
    • “Текст”: указываем ссылку на исходную ячейку, содержащую запятую. Сделать это можно вручную, напечатав адрес с помощью клавиатуры. Либо, находясь в поле для ввода информации, щелкаем по нужному элементу в самой таблице.

Метод 3: настраиваем параметры Excel

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

Обратите внимание, что формат ячеек, в которых вы хотите выполнить замену, должен быть выбран как Числовой (или Общий), чтобы программа воспринимала их содержимое как числа и применяла к ним указанные настройки. Итак, приступим:

  1. Переходим в меню “Файл”.Переход в меню Файл в Эксель
  2. В перечне слева выбираем пункт “Параметры”.Переход в Параметры Excel
  3. В подразделе “Дополнительно” убираем галочку напротив опции “Использовать системные разделители” (группа параметров “Параметры правки”), после чего активируется поле напротив “Разделителя целой и дробной части”, в котором указываем знак “точка” и щелкаем OK.Отключение использования системных разделителей в Эксель
  4. Таким образом, запятые заменятся на точки во всех ячейках, содержащих числовые значения. Действие будет выполнено во всей книге, а не только на данном листе. Замена запятых на точки в Excel

Метод 4: используем специальный макрос

Этот метод не очень популярен, но он существует, и мы опишем его здесь.

Первый шаг — включить режим разработчика (по умолчанию он выключен). Если вы решили это сделать, то в настройках программы в подразделе «Настроить ленту» необходимо установить флажок напротив пункта «Разработчик». Подтвердите изменения, нажав кнопку OK.

Активация режима Разработчика в параметрах Эксель

Теперь давайте приступим к нашей главной задаче:

  1. Переключившись в появившуюся вкладку “Разработчик” кликаем в левой части ленты по значку “Visual Basic” (группа инструментов “Код”).Запуск Visual Basic в Эксель
  2. На экране отобразится окно Редактора Microsoft VB. В левой части дважды щелкаем по любому листу или книге. В отрывшемся поле вставляем код ниже и закрываем редактор.
    Sub Макрос_замены_запятой_на_точку()
    Selection.Replace What:=»,», Replacement:=».», LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
    End Sub Макрос для замены запятых точками в Excel
  3. Выполняем выделение ячеек, в содержимом которых требуется сделать замену. Затем кликаем по значку “Макросы”.Применение макроса к выделенному диапазону в Эксель
  4. В появившемся окне отмечаем наш макрос и подтверждаем выполнение команды нажатием соответствующей кнопки. Обращаем внимание, что отменить данное действие невозможно.Выбор и выполнение макроса в Excel
  5. В результате, все запятые в выделенных ячейках будут заменены на точки.Замена запятых на точки в Эксель

Этот метод работает, только если в программе в качестве десятичного разделителя используется точка, например, если отключена опция «Использовать системные разделители» (обсуждалась выше).

Метод 5: меняем системные настройки компьютера

Наконец, мы представим метод, предполагающий внесение изменений в настройки самой операционной системы (на примере Windows 10).

  1. Запускаем Панель управления (например, через строку Поиска).Запуск Панели управления через строку Поиска в Windows 10
  2. В режиме просмотра “Маленькие/крупные значки” кликаем по апплету “Региональные стандарты”.Переход в раздел Региональные стандарты в Панели управления Windows 10
  3. В открывшемся окне мы окажемся во вкладке “Форматы”, в которой жмем кнопку “Дополнительные параметры”.Переход к дополнительным параметрам региона в Панели управления Windows 10
  4. В следующем окне во вкладе “Числа” мы можем указать символ разделителя, который хотим установить по умолчанию для системы и программы Excel, в частности. В нашем случае – это точка. По готовности жмем OK.Назначение разделителя для целой и дробной части в Панели управления Windows 10
  5. После этого все запятые в ячейках таблицы, которые содержат числовые данные (с форматом – Числовой или Общий), будут заменены на точки.

Заключение

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

голоса
Рейтинг статьи
Читайте так же:
Как вставить / добавить пробел после каждых x символов в ячейках?
Ссылка на основную публикацию
Adblock
detector