Эксель заменить точку на запятую в формуле

Эксель заменить точку на запятую в формуле

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

Процедура замены

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

Способ 1: инструмент «Найти и заменить»

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

  1. Производим выделение области на листе, где нужно трансформировать запятые в точки. Выполняем щелчок правой кнопкой мышки. В запустившемся контекстном меню отмечаем пункт «Формат ячеек…». Те пользователи, которые предпочитают пользоваться альтернативными вариантами с применением «горячих клавиш», после выделения могут набрать комбинацию клавиш Ctrl+1.

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

Опять выделяем целевой диапазон. Это важный нюанс, ведь без предварительного выделения трансформация будет произведена по всей области листа, а это далеко не всегда нужно. После того, как область выделена, передвигаемся во вкладку «Главная». Щелкаем по кнопке «Найти и выделить», которая размещена в блоке инструментов «Редактирование» на ленте. Затем открывается небольшое меню, в котором следует выбрать пункт «Заменить…».

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

    Способ 2: применение функции

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

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

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

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

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

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

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

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

  • После этих действий значения будут вставлены в соответствующий диапазон. При этом запятые будут трансформированы в точки. Чтобы удалить уже не нужную нам область, заполненную формулами, выделяем её и щелкаем правой кнопкой мыши. В появившемся меню выбираем пункт «Очистить содержимое».
  • Преобразование данных по смене запятых на точки выполнено, а все ненужные элементы удалены.

    Читайте также:  Как пользоваться телефонной книгой

    Способ 3: Использование макроса

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

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

      Перемещаемся во вкладку «Разработчик» и щелкаем по кнопке «Visual Basic», которая размещена в блоке инструментов «Код» на ленте.

    Открывается редактор макросов. Производим вставку в него следующего кода:

    Sub Макрос_трансформации_запятых_в_точки()
    Selection.Replace What:=",", Replacement:="."
    End Sub

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

    Далее выделяем диапазон, в котором следует произвести трансформацию. Щелкаем по кнопке «Макросы», которая расположена все в той же группе инструментов «Код».

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

    Способ 4: настройки Эксель

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

      Находясь во вкладке «Файл», щелкаем по наименованию блока «Параметры».

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

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

    Отблагодарите автора, поделитесь статьей в социальных сетях.

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

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

    Как в Excel заменить точку, на запятую?

    Выделите и скопируйте данные из ниже приведенной таблицы:

    № п/п Валютная пара Пересчет Продажа Покупка Спрэд
    1 EUR/USD 1 1.1347 1.1350 0.0003
    2 GBP/USD 1 1.5438 1.5442 0.0004
    3 USD/CHF 1 0.9543 0.9547 0.0004
    4 USD/JPY 100 1.1948 1.1945 -0.0003

    Теперь перейдите на рабочий лист и щелкните правой кнопкой мышки по ячейке A1. Из появившегося контекстного меню выберите опцию «Специальная вставка». В диалоговом окне выберите «Текст в кодировке Unicode» и нажмите ОК.

    Как видно Excel распознает числа только в колонке C. Значения в этой колонке выровнены по правой стороне. В других колонках по левому краю. Во всех ячейках формат по умолчанию «Общий», а в ячейках D3, D5, E3, E5 вообще отображается формат «Дата». Мы копировали данные через специальную вставку и все форматы исходной таблицы устранены. Причина только одна – вместо запятой стоит точка. Такой тип данных не подготовлен и его нельзя использовать для вычислений.

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

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

    Чтобы заменить точку, на запятую в импортированных данных можно использовать 4 способа:

    Способ 1 замена точки на запятую в Excel через Блокнот

    Программа Блокнот Windows не требует использования сложных настроек и функций, а лишь выступает посредником в копировании и предварительной подготовки данных.

    1. Скопируйте данные из исходной таблички на этой странице. Откройте программу Блокнот Windows («Пуск»-«Все программы»-«Стандартные»-«Блокнот») и вставьте в него скопированные данные для подготовки.
    2. Выберите в меню «Правка» опцию «Заменить» (или комбинацию горячих клавиш CTRL+H). В появившимся диалоговом окне, введите в поле: «Что» точку (.), а в поле «Чем» запятую (,). И нажмите кнопку «Заменить все».
    Читайте также:  Как откатиться до точки восстановления windows 10

    Программа Блокнот заменила все точки, на запятые. Теперь данные готовы для копирования и вставки на лист.

    Это весьма простой, но очень эффективный способ.

    Способ 2 временно меняем настройки Excel

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

    Для этого нужно открыть «Файл»-«Параметры»-«Дополнительно». В разделе «Параметры правки» следует временно убрать галочку «Использовать системные разделители». А в поле «Разделитель целой и дробной части» следует удалить запятую и ввести точку.

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

    Внимание! Данный способ сработает, если сделать все изменения до импорта данных, а не после.

    Способ 3 временно меняем системные настройки Windows

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

    Открываем «Пуск»-«Панель управления»-«Языки и региональные стандарты». Нажимаем на кнопку «Дополнительно». В появившимся окне изменяем в первом поле «Разделитель целой и дробной части» — вводим нужное нам значение. Дальше ОК и ОК.

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

    Способ 4 используем функцию найти и заменить в Excel.

    Данный способ похож на первый. Только здесь мы используем ту же функцию из Блокнота, но уже в самом Excel.

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

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

    1. Предварительно выделите столбцы где будут находится дробные числа с точкой в качестве разделителя. В данном случаи это 3 столбца D:F.
    2. Установите для выделенного диапазона текстовый формат ячеек, чтобы заранее избежать автоматического преобразования в формат даты некоторых чисел. Для этого выберите текстовый формат из выпадающего списка на закладке «Главная» в разделе «Число». Или нажмите CTRL+1 , в появившимся окне «Формат ячеек» выберите закладку «Число», а в разделе «Числовые форматы» укажите «Текстовый».
    3. Скопируйте таблицу и щелкните правой кнопкой мышки по ячейке A1. Из контекстного меню выберите опцию «Специальная вставка». Выберите «Текст в кодировке Unicode» и нажмите ОК. Обратите внимание, как теперь отображаются значения в ячейках: D3, D5, E3, E5, в отличии от самого первого копирования таблицы.
    4. Нажмите на инструмент «Главная»-«Найти и выделить»-«Заменить» (или нажмите комбинацию CTRL+H).
    5. В появившемся окне введите в поле «Найти» — точку, а в во второе поле введите запятую. И нажмите «Заменить все».
    6. Снова выделите 3 столбца D:F и измените формат ячеек на «Числовой» CTRL+SHIFT+1. Не забудьте увеличить разрядность до 4-х, как описано в пункте №2.

    Все точки сменились на запятые. А текст автоматически преобразился в число.

    Вместо 4-го и 5-го пункта можно использовать в отдельной колонке формулу с функциями:

    Например, выделите диапазон ячеек G2:I5, введите эту формулу и нажмите CTRL+Enter. А потом переместите значения ячеек диапазона G2:I5 в диапазон D2:F5.

    Эта формула находит в тексте точку с помощью функции НАЙТИ. Потом вторая функция меняет ее на запятую. А функция ЗНАЧЕН преобразует полученный результат в число.

    Доброго времени суток друзья!

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

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

    Изменение параметров Excel

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

    Читайте также:  Дирк джентли сериал отзывы

    Замечание! Уже выгруженные в Excel данные не изменяются и не преобразуются в числовое значение, для текущих изменений нужно использовать совсем другие способы, к примеру:

    • 1 способ – это простое копирование в блокнот, к примеру, Notepad++, вставить их в него, а потом снова скопировать в Excel с обычной вставкой.
    • 2 способ – простое копирование и вставка сюда же. Когда вы увидите в ячейках зелёные треугольники в левом верхнем углу, то знайте, что Excel сохранил числа как текст. Вы выбираете диапазон для изменений, вызываете контекстное меню и выбираете пункт «Преобразовать в число».

    Изменение системных настроек Windows

    Этот способ, заменить точки на запятые в Excel, также не является сложным в исполнении и как было сказано выше нужен, если у вас в Excel стоят настройки на систему. Системные изменения нужно внести по следующему адресу «Пуск» — «Панель управления» — «Часы, язык и регион», далее выбираете в разделе «Язык и региональные стандарты» пункт «Изменение форматов даты, времени и чисел», в открывшемся окне выбираем системную вкладку «Форматы» — «Дополнительные настройки», в открывшемся окне «Настройка формата», на вкладке «Числа», в поле «Разделитель целой и дробной части» вам необходимо заменить «точку» на «запятую» либо наоборот и нажать «ОК».

    Внимание! После этого вы можете выгружать отчёты с нужными вам знаками, но для их корректной работы в исходном варианте, вам нужно будет вернуть настройки на свое место!

    Изменение с помощью пунктов «Найти» и «Заменить»

    Рассмотрим также такой тривиальный способ, по замене точкек на запятые в Excel, как использование возможности «Найти и заменить». Этот способ также можно применить в нашем случае, но только при условии, что у нас только одни голые цифры и больше ничего нет, так как замена работает для всего листа и меняет не только то что нам нужно и то что нам не нужно, одним словом изменят всё к чему дотянется. Так что будьте внимательны при её использовании. Для изменения данных вам нужно перейти во вкладку «Главная», выбрать раздел «Редактирование», нажать иконку с изображением бинокля и в выпадающем из него меню выбрать пункт «Заменить» или просто выделить весь диапазон где вам нужно заменить знак «точка» на знак «запятая», нажимаете сочетание горячих клавиш Ctrl+H. В открывшемся окне, в поле «Найти», ставим «.», а в поле «Заменить» ставим «,», ну или наоборот в зависимости от рабочей необходимости и нажимаем кнопку «Заменить всё». Все «точки» в нашей рабочей книге или диапазоне будут заменены на «запятые».

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

    Изменить с помощью функции ПОДСТАВИТЬ и функции ЗНАЧЕН

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

    =ПОДСТАВИТЬ((A1;«.»;«,»;1), где,

    • А1 – это число которое вы будете изменять,
    • «.» — вторым аргументом идет знак который вы будете искать,
    • «,» — третий аргумент, это знак на который вы замените,
    • четвертым аргументом случит число знаков с которого происходит поиск, это нужно если в аргументах встречаются более 1 похожего знака.

    Поскольку полученные данные всё еще остаются текстовыми, вам необходимо конвертировать полученный результат в числовой, это вы сделаете с помощью функции ЗНАЧЕН.

    =ЗНАЧЕН(ПОДСТАВИТЬ((A1;«.»;«,»;1)), вот так подставив функцию подставления в формулу, на выходе вы получите нужное вам число.

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

    Изменить с помощью макроса VBA

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

    Ссылка на основную публикацию
    Что такое экранное время в ios
    Экранное время – одна из лучших функций iOS 12, позволяющая следить за тем, как часто вы берёте в руки свой...
    Что делать если отключился звук на компьютере
    Мы зарегистрировали подозрительный трафик, исходящий из вашей сети. С помощью этой страницы мы сможем определить, что запросы отправляете именно вы,...
    Что делать если полетели драйвера видеокарты
    Распространенная ошибка в Windows 7 и реже в Windows 10 и 8 — сообщение «Видеодрайвер перестал отвечать и был успешно...
    Что такое эмодзи клавиатура на телефоне
    Современное общение сложно представить без мессенджеров, чатов и социальных сетей, но только текстом бывает сложно передать все эмоции. В этом...
    Adblock detector