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

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

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

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

См также: «Как поменять местами столбцы в электронной таблице Excel”

Начнем, пожалуй, с самого простого способа, который предполагает использование инструмента «Найти и заменить», при работе с которым нужно быть очень внимательным, чтобы случайно не заменить точки запятыми в данных там, где этого делать не следует (например, в датах). Вот как это работает:

1. Перейдите на вкладку «Главная» и нажмите кнопку «Найти и выбрать» (значок увеличительного стекла) в блоке «Редактировать». Откроется список, где выбираем команду «Заменить». А можно просто нажать комбинацию клавиш Ctrl+H.

    

   

    

2. На экране появится окно «Найти и заменить”:
   • в поле для ввода значения перед пунктом «Поиск» введите символ «.» (точка);
   • в поле «Заменить на» введите знак «,» (запятая);
   • нажмите кнопку «Настройки”.

      

     

      

3. Появятся дополнительные параметры для выполнения поиска и замены. Нажмите кнопку «Формат» для параметра «Заменить на”.

    

   

    

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

    

   

    

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

    

   

    

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

    

   

    

7. Закрываем все окна (кроме Excel), после чего можем продолжить работу с преобразованными данными в таблице.

    

   

    

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

Метод 2: функция “ПОДСТАВИТЬ”

Читайте также: «Автофильтр в Excel: как сделать, использовать, отменить”

Давайте теперь посмотрим на функцию «ПОДСТАВИТЬ», которая также позволяет заменять точки запятыми. Но в отличие от метода, который мы обсуждали ранее, замена значений не производится на исходные, а выводится в отдельные ячейки.

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

    

   

    

2. В открывшемся окне Мастера функций выбираем категорию — «Текст», в которой находим оператор «ПОДСТАВИТЬ», выбираем его и нажимаем ОК.

    

   

    

3. Мы окажемся в окне с аргументами функции, которые необходимо выполнить:
   • в значении аргумента «Текст» укажите координаты первой ячейки столбца, в которой точки должны быть заменены запятыми. Вы можете сделать это вручную, введя адрес с помощью клавиш клавиатуры. Либо можно сначала щелкнуть мышью внутри поля для ввода информации, а затем щелкнуть нужную ячейку в таблице.
   • в значение аргумента «Star_text» пишем символ «.» (точка).
   • для аргумента «Новый_текст» укажите в качестве значения символ «,» (запятая).
   • значение аргумента «Entry_Number» можно оставить пустым.
   • когда будете готовы, нажмите OK.

      

     

      

4. Получаем нужный результат в выбранной ячейке.

    

   

    

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

    

   

    

6. Остается только переместить преобразованные данные в то место в таблице, где они должны быть. Для этого выделите ячейки столбца с результатами (если выделение снимается после вышеуказанного действия), щелкните правой кнопкой мыши в любом месте выделенного диапазона и выберите пункт «Копировать» (или нажмите сочетание клавиш Ctrl+C).

    

   

    

7. Затем мы выбираем аналогичный диапазон ячеек в исходном столбце, данные которого были преобразованы. Щелкаем правой кнопкой мыши по выделенной области и в открывшемся контекстном меню в параметрах вставки выбираем «Значения”.

    

   

    

8. После вставки скопированных данных рядом с ними появится значок восклицательного знака. Нажмите на него и выберите «Преобразовать в число» из выпадающего списка”.

    

   

    

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

    

   

    

10. Рабочий столбец, используемый для работы с функцией ЗАМЕНИТЬ, больше не нужен и может быть удален через контекстное меню. Для этого щелкните правой кнопкой мыши по обозначению столбца в горизонтальной координатной линейке и выберите из появившегося списка команду «Удалить”.

     

    

     

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

Метод 3: использование макроса

Макросы также позволяют заменять точку запятой. Вот как вы это делаете:

1. Сначала вам нужно убедиться, что вкладка «Разработчик» включена, которая по умолчанию отключена в Excel. Чтобы включить нужную вкладку, перейдите в меню «Файл”.

    

   

    

2. В списке слева перейдите в раздел «Настройки”.

    

   

    

3. В опциях программы нажмите раздел «Настроить ленту», после чего в правой части окна поставьте галочку напротив пункта «Разработчик» и нажмите ОК.

    

   

    

4. Переключаемся на вкладку «Разработчик», в которой нажимаем кнопку «VisualBasic”.

    

   

    

5. В редакторе кликаем по листу, который хотим заменить, в открывшемся окне вставляем код ниже, после чего закрываем редактор:
   
   Подмакрос_replace_dot_by_comma()
   
   Selection.Replace What:=».», Замена:=».», LookAt:=xlPart, _
   
   SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
   
   Заменить формат: = false
   
   Под конец

    

   

    

6. Теперь выделяем диапазон ячеек на листе, где планируем произвести замену, после чего нажимаем кнопку «Макросы», все на той же вкладке «Разработчик”.

    

   

    

7. Откроется окно со списком макросов, в котором выбираем «Macro_replace_dot_to_coma» и нажимаем «Выполнить”.

    

   

    

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

    

   

    

Метод 4: применение Блокнота

Этот метод реализуется путем копирования данных во встроенный в операционную систему Windows редактор «Блокнот» для дальнейшего редактирования. Процедура показана ниже:

1. Для начала выделяем диапазон ячеек, в значениях которых нужно заменить точки запятыми (на примере столбца рассмотрим). После этого щелкните правой кнопкой мыши в любом месте выделенной области и в открывшемся меню выберите команду «Копировать» (либо можно воспользоваться сочетанием клавиш Ctrl+C).

    

   

    

2. Запустите Блокнот и вставьте скопированную информацию. Для этого щелкните правой кнопкой мыши и выберите в открывшемся меню команду «Вставить» (или воспользуйтесь комбинацией Ctrl+V).

    

   

    

3. В верхней строке меню нажмите «Изменить». Откроется список, в котором нажимаем на команду «Заменить» (или нажимаем горячие клавиши Ctrl+H).

    

   

    

4. На экране появится небольшое окно замены:
   • в поле для ввода значения параметра «Что» введите символ «.» (точка);
   • в качестве значения параметра «Чем» установить символ «,» (запятая);
   • нажмите «Заменить все”.

      

     

      

5. Закройте окно замены. Выделите преобразованные данные, затем щелкните по ним правой кнопкой мыши и в открывшемся контекстном меню выберите команду «Копировать» (также можно использовать Ctrl+C).

    

   

    

6. Вернемся к Экселю. Отмечаем область, куда нужно вставить измененные данные. Затем щелкаем правой кнопкой мыши по выделенному диапазону и выбираем команду «Сохранить только текст» в параметрах вставки (или нажимаем Ctrl + V).

    

   

    

7. Остается только установить формат ячейки «Числовой». Выбрать его можно в панели инструментов «Число» (вкладка «Главная»), нажав на текущий формат и выбрав нужный.

    

   

    

8. Задача выполнена успешно.

    

   

    

Метод 5: настройка параметров Эксель

При реализации этого метода нам необходимо изменить некоторые настройки программы.

1. Заходим в меню «Файл», где нажимаем на раздел «Параметры”.
2. В параметрах программы в списке слева нажмите на раздел «Дополнительно». В блоке настроек «Параметры редактирования» снимите флажок рядом с параметром «Использовать системные разделители». После этого активируются поля для ввода символов в качестве разделителей. В качестве разделителя между целой и дробной частью пишем символ «.» (точка) и сохраните настройку, нажав кнопку OK.

    

   

    

3. Визуально изменений в таблице не будет. Поэтому идем дальше. Для этого скопируйте данные и вставьте в блокнот (разберем на примере столбца).

    

   

    

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

    

   

    

5. Снова заходим в настройки программы (раздел «Дополнительно»), где возвращаем галочку напротив пункта «Использовать системные разделители» на место и нажимаем кнопку ОК.

    

   

    

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

    

   

    

Метод 6: системные настройки

И, наконец, рассмотрим еще один способ, аналогичный вышеописанному, но предполагающий изменение настроек не Excel, а операционной системы Windows.

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

    

   

    

2. Задаем отображение в виде мелких или крупных значков, а затем нажимаем на раздел «Региональные стандарты”.

    

   

    

3. Появится окно настроек региона, в котором, находясь на вкладке «Форматы», нажмите кнопку «Дополнительные настройки”.

    

   

    

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

    

   

    

5. Аналогично рассмотренному выше пятому способу мы копируем данные из Excel в Блокнот и наоборот.
6. Возвращаем настройки формата в исходное положение. Это действие критично, так как в противном случае могут возникнуть ошибки в работе других программ и утилит.

    

   

    

7. Все точки в столбце, над которым мы работали, были автоматически заменены запятыми.

Заключение

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