Очень часто при работе с таблицами Excel возникает необходимость заменить точки запятыми. Часто это связано с тем, что в англоязычных странах для разделения дробной и целой частей в числе используется точка, а для этой цели используется запятая.
И все бы хорошо, но проблема в том, что в русифицированной версии Excel данные с точкой не воспринимаются как числа, что делает невозможным их дальнейшее использование в расчетах. И чтобы решить эту проблему, вам нужно заменить точку запятой. Как именно это можно сделать в Excel, мы рассмотрим в этой статье.
Метод 1: использование инструмента “Найти и заменить”
Мы начнем с, пожалуй, самого простого метода, который включает в себя использование инструмента «Найти и заменить», при работе с которым нужно быть предельно осторожным, чтобы случайно не заменить точки запятыми в ваших данных там, где этого не следовало делать (например, , по датам). Итак, вот как это работает:
- Перейдите на вкладку «Главная» и нажмите кнопку «Найти и выбрать» (значок лупы) в блоке «Редактировать». Откроется список, в котором выбираем команду «Заменить». Как вариант, вы можете просто нажать комбинацию клавиш Ctrl + H.
- На экране появится окно «Найти и заменить”:
- в поле для ввода значения напротив пункта «Найти» напишите символ «.» (точка);
- в поле «Заменить на» напишите знак «,» (запятая);
- нажмите кнопку «Параметры”.
- Отображаются дополнительные параметры поиска и замены. Нажмите кнопку «Форматировать» для параметра «Изменить на”.
- В появившемся окне укажите правильный формат ячейки (тот, который мы получаем в результате). Исходя из нашей задачи, выбираем формат «Число», затем нажимаем ОК. При желании вы можете установить количество знаков после запятой, а также отдельные группы цифр, установив соответствующий флажок.
- В результате мы снова окажемся в окне «Найти и заменить». Здесь мы должны обязательно выделить ту область ячеек, в которой будет производиться поиск точек и их дальнейшая замена запятыми. В противном случае операция замены будет выполняться по всему листу, и это может повлиять на данные, которые не нужно было изменять. Выбор диапазона ячеек осуществляется с помощью удерживаемой левой кнопки мыши. Когда будете готовы, нажмите «Заменить все”.
- Все готово. Операция прошла успешно, о чем свидетельствует информационное окно с количеством произведенных замен.
- Закрываем все окна (кроме самого Excel), после чего можем продолжить работу с преобразованными данными в таблице.
Примечание: Чтобы не выделять диапазон ячеек при настройке параметров в окне «Найти и заменить», вы можете сделать это заранее, т.е сначала выделить ячейки, а затем запустить соответствующий инструмент с помощью кнопок на ленте программы или с помощью сочетание клавиш Ctrl + H.
Метод 2: функция “ПОДСТАВИТЬ”
Давайте теперь посмотрим на функцию REPLACE, которая также позволяет заменять точки запятыми. Но в отличие от метода, который мы рассмотрели выше, замена значений не производится в исходных, а отображается в отдельных ячейках.
- Перейдите в верхнюю ячейку столбца, в котором мы собираемся отображать данные, затем нажмите кнопку «Вставить функцию» (fx) слева от строки формул.
- В открывшемся окне мастера выбираем категорию — «Текст», в которой находим оператор «ЗАМЕНА», выбираем его и нажимаем ОК.
- Мы окажемся в окне с аргументами компилируемой функции:
- в значении аргумента «Текст» укажите координаты первой ячейки столбца, в которой нужно заменить точки запятыми. Это можно сделать вручную, введя адрес с помощью клавиш клавиатуры. Или вы можете сначала щелкнуть внутри поля, чтобы ввести информацию, а затем щелкнуть нужную ячейку в таблице.
- в значении аргумента «Star_text» напишите символ «.» (точка).
- для аргумента «Новый_текст» мы указываем символ «,» (запятая) в качестве значения).
- значение аргумента «Entry_Number» можно оставить пустым.
- когда будете готовы, нажмите ОК.
- Получаем требуемый результат в выделенной ячейке.
- Осталось только распространить эту функцию на оставшиеся строки столбца. Конечно, вам не нужно делать это вручную, поскольку в Excel есть удобная функция автозаполнения. Для этого переместите курсор в правый нижний угол ячейки с формулой, когда указатель изменится на черный знак плюса (маркер заполнения), удерживая левую кнопку мыши, перетащите ее в последнюю строку, участвующую в преобразовании данных.
- Остается только переместить преобразованные данные туда, где они должны быть в таблице. Для этого выделите ячейки столбца с результатами (если после предыдущего действия выделение снято), щелкните в любом месте выделенного диапазона правой кнопкой мыши и выберите «Копировать» (или нажмите комбинацию клавиш Ctrl + C).
- Затем выберите аналогичный диапазон ячеек в исходном столбце, данные которого были преобразованы. Щелкните правой кнопкой мыши выбранную область и в открывшемся контекстном меню выберите «Значения» в параметрах для вставки”.
- После вставки скопированных данных рядом с ними появляется значок восклицательного знака. Щелкаем по нему и в выпадающем списке выбираем «Преобразовать в число”.
- Все готово, у нас есть столбик, в котором все точки заменены запятыми.
- Рабочий столбец, используемый для работы с функцией ЗАМЕНИТЬ, больше не нужен и может быть удален через контекстное меню. Для этого щелкните правой кнопкой мыши обозначение столбца на горизонтальной панели координат и выберите команду «Удалить» из отображаемого списка”.
- При необходимости указанные выше действия можно выполнить с другими столбцами исходной таблицы.
Метод 3: использование макроса
Макросы также позволяют заменять точку запятыми. Вот как это делается:
- Сначала вам нужно убедиться, что вкладка «Разработчик» включена, которая по умолчанию отключена в Excel. Чтобы включить нужную вкладку, перейдите в меню «Файл”.
- В списке слева перейдите в раздел «Параметры”.
- В параметрах программы нажмите на раздел «Настроить ленту», затем в правой части окна поставьте галочку напротив пункта «Разработчик» и нажмите ОК.
- Переходим на вкладку «Разработчик», где нажимаем кнопку «VisualBasic”.
- В редакторе щелкаем по листу, на котором хотим произвести замену, в открывшемся окне вставляем код ниже, затем закрываем редактор:
Sub Macro_replace_point_to_comma()
Selection.Replace What: = «.», Replacement: = «.», LookAt: = xlPart, _
SearchOrder: = xlByRows, MatchCase: = False, SearchFormat: = False, _
ReplaceFormat: = False
Конец подзаголовка
- Теперь выберите диапазон ячеек на листе, который мы планируем заменить, затем нажмите кнопку «Макрос» на той же вкладке «Разработчик”.
- Откроется окно со списком макросов, в котором выбираем «Macro_replace_point_to_comma» и нажимаем «Выполнить”.
- В результате мы получим ячейки с преобразованными данными, где точки заменены запятыми, что нам и требовалось.
Метод 4: применение Блокнота
Этот метод реализуется путем копирования данных в редактор Блокнота, встроенный в операционную систему Windows, для последующего редактирования. Порядок действий следующий:
- Сначала выбираем диапазон ячеек, в значениях которых нужно заменить точки запятыми (рассмотрим пример столбца). Затем щелкните правой кнопкой мыши в любом месте выбранной области и выберите команду «Копировать» из раскрывающегося меню (или вы можете использовать сочетание клавиш Ctrl + C).
- Запустите Блокнот и вставьте скопированную информацию. Для этого щелкните правой кнопкой мыши и выберите в раскрывающемся меню команду «Вставить» (или используйте комбинацию Ctrl + V).
- В строке меню вверху нажмите на пункт «Редактировать». Откроется список, в котором нажимаем на команду «Заменить» (или нажимаем сочетание клавиш Ctrl + H).
- На экране появится небольшое окно замены:
- в поле для ввода значения параметра «Что» печатаем «.» (точка);
- в качестве значения параметра «Что» ставим символ «,» (запятая);
- нажмите «Заменить все”.
- Закройте окно замены. Выделите преобразованные данные, затем щелкните их правой кнопкой мыши и в открывшемся контекстном меню выберите команду «Копировать» (вы также можете использовать Ctrl + C).
- Вернитесь в Excel. Мы отмечаем область, куда вы хотите вставить измененные данные. Затем щелкаем по выделенному диапазону правой кнопкой мыши и выбираем команду «Сохранить только текст» в параметрах вставки (или нажимаем Ctrl + V).
- Осталось только установить формат ячейки «Числовой». Вы можете выбрать его в панели инструментов «Число» (вкладка «Главная»), щелкнув текущий формат и выбрав нужный.
- Задание выполнено успешно.
Метод 5: настройка параметров Эксель
Реализуя этот метод, нам нужно изменить некоторые настройки программы.
- Переходим в меню «Файл», где нажимаем на раздел «Параметры”.
- В параметрах программы в списке слева нажмите раздел «Еще». В блоке настроек «Параметры редактирования» снимите флажок рядом с параметром «Использовать системные разделители». Впоследствии активируются поля для ввода символов в качестве разделителей. Пишем символ «.» Как разделитель для целых и дробных частей. (точка) и сохраните настройки, нажав кнопку ОК.
- Визуальных изменений в таблице не будет. Поэтому идем дальше. Для этого скопируйте данные и вставьте их в Блокнот (рассмотрим для примера один столбец).
- Выберите данные из Блокнота и вставьте их обратно в таблицу Excel в то же место, где вы их скопировали. При этом выравнивание данных изменилось слева направо. Это означает, что программа теперь обрабатывает эти значения как числовые.
- Переходим снова в параметры программы (раздел «Дополнительно»), где восстанавливаем галочку рядом с пунктом «Использовать системные разделители» и нажимаем кнопку ОК.
- Как видите, программа автоматически заменила точки запятыми. Не забудьте изменить формат данных на «Числовой», и вы сможете дальше работать с ними.
Метод 6: системные настройки
И, наконец, рассмотрим другой метод, аналогичный описанному выше, но с изменением настроек не Excel, а операционной системы Windows.
- Зайдем в Панель управления любым удобным способом. Например, это можно сделать через Поиск, набрав желаемое имя и выбрав найденный вариант.
- Установите для отображения маленькие или большие значки, затем щелкните раздел «Региональные правила”.
- Появится окно настройки региона, где, находясь во вкладке «Форматы», нажмите кнопку «Дополнительные настройки”.
- В следующем окне с настройками формата мы видим параметр «Разделитель целых и дробных частей» и установленное для него значение. Вместо запятой напишите точку и нажмите ОК.
- Подобно пятому методу, описанному выше, давайте скопируем данные из Excel в Блокнот и наоборот.
- Возвращаем настройки формата в исходное положение. Это действие обязательно, иначе возможны ошибки в работе других программ и утилит.
- Все точки в столбце, над которым мы работали, были автоматически заменены запятыми.
Заключение
Поэтому в Excel предусмотрено 5 различных методов, с помощью которых вы можете заменить точки запятыми, если такая необходимость возникнет во время работы. Также можно использовать другой метод, который предполагает изменение настроек самой операционной системы Windows, в которой установлена программа Excel.