В русской версии excel разделителем целой и дробной части является

Обновлено: 06.01.2025

Предыдущие ветки топика: Часть 1 | Часть 2 | Часть 3 | Часть 4

Все вопросы по программированию (макросы, скрипты, пользовательские функции и т.п.) обсуждаются в теме Excel VBA в разделе Прикладное программирование.

При необходимости выложить скриншот, пользуйтесь сервисом ImageShack® (ограничение на размер файла <1.5 Mb)

kimtan
спасибо, оно самое.

Есть массив данных такого вида (вероятности). Необходимо в каждой строке осуществить поиск слева направо значения, большего или равного 0,75. Когда такое значение найдено, проверить, является ли среднее арифметическое следующих вслед за ним двух чисел также большим или равным 0,75. Если это условие выполняется, то формула должна возвращать порядковый номер ячейки с найденным значением. Если условие не выполняется, то ищем дальше слева направо.
Например, в данном массиве для первой строчки нужное значение - 3, для второй - тоже 3, для третьей - 4, а вот для пятой - не 3, а 6 (одно из условий не выполняется).
Формулы массива нужны, что ли. Подскажите хотя бы, в какую сторону копать.
Заранее премного благодарен за любую помощь.

К слову, формула, кроме сомнительного "изящества" больше не блещет никакими достоинствами (это к ответу на второй вопрос).
Во-первых, формулы массива создают дополнительную головную боль вашему процессору, иногда огромные массивы данных хранятся и пересчитываются в виртуальной памяти. Конечно все индивидуально и зависит от размеров обрабатываемых массивов. Можно обычной формулой повесить комп, а формула массива очень шустро отработает (при небольших массивах)
Во-вторых, в данной формуле применена летучая (пересчитываемая) функция OFFSET(), которая будет инициировать пересчет всех зависимых ячеек при каждом изменении любой ячейки в книге, что создаст ненужную нагрузку на процессор и увеличит время пересчета.
Так что, если есть такая возможность, не стоит гонятся за изяществом, а написать простую формулу (если конечно ее длина и сложность ввода будут адекватными). Эксперементируйте.

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

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

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

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

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

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

Как изменить разделитель в Excel 2007

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

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

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

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

Применение функции

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

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

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

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

Использование макроса

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

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

«Разработчик»

«Visual Basic»

«Код»

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

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

Выполняется преобразование. Запятые будут трансформированы в точки.

Правила замены точек и запятых в программе Excel

Как заменить точку с запятой во всем документе Excel

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

Временно меняем настройки Excel

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

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

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

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

Временно меняем системные настройки Windows

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

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

Использование функции ПОДСТАВИТЬ

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

«Вставить функцию»

На этом преобразование данных закончено.

Использование Блокнота

Следующий способ предполагает копирование данных в стандартный текстовый редактор Windows Блокнот, и изменение их в этой программе.

«Копировать»

Как поменять точки на запятые в Excel в определенном месте

Нередки ситуации, когда работая с отчетом Excel, где используются в качестве разделителя, например, запятые, а вам присылают данные с точками и стоит задача поменять запятые на точки или наоборот. Самый простой способ – это выделить все данные с точками, нажать одновременно Ctrl+H (либо необходимо перейти (Excel 2007-2013) во вкладку Главная, раздел Редактирование, выбрать бинокль и в выпадающем меню выбрать пункт Заменить

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

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

Макрос замены точки на запятую в выделенных ячейках

Макрос замены запятой на точку в выделенных ячейках

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

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

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

История этого формата насчитывает не менее 30 лет. Но даже сейчас, в эпоху повального использования XML, для выгрузки и загрузки больших объемов данных по-прежнему используют CSV. И, несмотря на то, что сам формат довольно неплохо описан в RFC, каждый его понимает по-своему.

В этой статье я попробую обобщить существующие знания об этом формате, указать на типичные ошибки, а также проиллюстрировать описанные проблемы на примере кривой реализации импорта-экспорта в Microsoft Office 2007. Также покажу, как обходить эти проблемы (в т.ч. автоматическое преобразование типов Excel-ом в DATETIME и NUMBER) при открытии .csv.

Начнем с того, что форматом CSV на самом деле называют три разных текстовых формата, отличающихся символами-разделителями: собственно сам CSV (comma-separated values — значения, разделенные запятыми), TSV (tab-separated values — значения, разделенные табуляциями) и SCSV (semicolon separated values — значения, разделенные точкой с запятой). В жизни все три могут называться одним CSV, символ-разделитель в лучшем случае выбирается при экспорте или импорте, а чаще его просто «зашивают» внутрь кода. Это создает массу проблем в попытке разобраться.

Как иллюстрацию возьмем казалось бы тривиальную задачу: импортировать в Microsoft Outlook данные из таблицы в Microsoft Excel.

В Microsoft Excel есть средства экспорта в CSV, а в Microsoft Outlook — соответствующие средства импорта. Что могло быть проще — сделал файлик, «скормил» почтовой программе и — дело сделано? Как бы не так.

Создадим в Excel тестовую табличку:

… и попробуем экспортировать ее в три текстовых формата:

«Текст Unicode»	Кодировка — UTF-16, разделители — табуляция, переводы строк — 0×0D, 0×0A, объем файла — 222 байт
«CSV (разделители — запятые)»	Кодировка — Windows-1251, разделители — точка с запятой (не запятая!), во второй строке значение телефонов не взято в кавычки, несмотря на запятую, зато взято в кавычки значение «01;02», что правильно. Переводы строк — 0×0D, 0×0A. Объем файла — 110 байт
«Текстовые файлы (с разделителями табуляции)»	Кодировка — Windows-1251, разделители — табуляция, переводы строк — 0×0D, 0×0A. Значение «01;02» помещено в кавычки (без особой нужды). Объем файла — 110 байт

Какой вывод мы делаем из этого. То, что здесь Microsoft называет «CSV (разделители — запятые)», на самом деле является форматом с разделителями «точка с запятой». Формат у Microsoft — строго Windows-1251. Поэтому, если у вас в Excel есть Unicode-символы, они на выходе в CSV отобразятся в вопросительные знаки. Также то, что переводами строк является всегда пара символов, то, что Microsoft тупо берет в кавычки все, где видит точку с запятой. Также то, что если у вас нет Unicode-символов вообще, то можно сэкономить на объеме файла. Также то, что Unicode поддерживается только UTF-16, а не UTF-8, что было бы сильно логичнее.

Теперь посмотрим, как на это смотрит Outlook. Попробуем импортировать эти файлы из него, указав такие же источники данных. Outlook 2007: Файл -> Импорт и экспорт… -> Импорт из другой программы или файла. Далее выбираем формат данных: «Значения, разделенные запятыми (Windows)» и «Значения, разделенные табуляцией (Windows)».

«Значения, разделенные табуляцией(Windows)»	Скармливаем аутлуку файл tsv, с разделенными табуляцией значениями и. — чтобы вы думали. Outlook склеивает поля и табуляцию не замечает. Заменяем в файле табуляцию на запятые и, как видим, поля уже разбирает, молодец.
«Значения, разделенные запятыми (Windows)»	А вот аутлук как раз понимает все верно. Comma — это запятая. Поэтому ожидает в качестве разделителя запятую. А у нас после экселя — точка с запятой. В итоге аутлук распознает все неверно.

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

Мы помним, что Microsoft Excel умеет работать с текстовыми файлами, импортировать данные из CSV, но в версии 2007 он делает это очень странно. Например, если просто открыть файл через меню, то он откроется без какого-либо распознавания формата, просто как текстовый файл, целиком помещенный в первую колонку. В случае, если сделать дабл-клик на CSV, Excel получает другую команду и импортирует CSV как надо, не задавая лишних вопросов. Третий вариант — вставка файла на текущий лист. В этом интерфейсе можно настраивать разделители, сразу же смотреть, что получилось. Но одно но: работает это плохо. Например, Excel при этом не понимает закавыченных переводов строк внутри полей.

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

Стандарта CSV как такового, к сожалению, нет, но, между тем, существует т.н. memo. Это RFC 4180 года, в котором описано все довольно толково. За неимением ничего большего, правильно придерживаться хотя бы RFC. Но для совместимости с Excel следует учесть его собенности.

Вот краткая выжимка рекомендаций RFC 4180 и мои комментарии в квадратных скобках:

между строками — перевод строки CRLF [на мой взгляд, им не стоило ограничивать двумя байтами, т.е. как CRLF (0×0D, 0×0A), так и CR 0×0D]
разделители — запятые, в конце строки не должно быть запятой,
в последней строке CRLF не обязателен,
первая строка может быть строкой заголовка (никак не помечается при этом)
пробелы, окружающие запятую-разделитель, игнорируются.
если значение содержит в себе CRLF, CR, LF (символы-разделители строк), двойную кавычку или запятую (символ-разделитель полей), то заключение значения в кавычки обязательно. В противном случае — допустимо.
т.е. допустимы переводы строк внутри поля. Но такие значения полей должны быть обязательно закавычены,
если внутри закавыченной части встречаются двойные кавычки, то используется специфический квотинг кавычек в CSV — их дублирование.

Вот в нотации ABNF описание формата:

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

строковое значение из цифр, не заключенное в кавычки может быть воспринято программой как числовое, из-за чего может быть потеряна информация, например, лидирующие нули,
количество значений в каждой строке может отличаться и необходимо правильно обрабатывать эту ситуацию. В одних ситуациях нужно предупредить пользователя, в других — создавать дополнительные колонки и заполнять их пустыми значениями. Можно определиться, что количество колонок задается заголовком, а можно добавлять их динамически, по мере импорта CSV,
Квотить кавычки через «слэш» не по стандарту, делать так не надо.
Поскольку типизации полей нет, нет и требования к ним. Разделители целой и дробной частей в разных странах разные, и это приводит к тому, что один и тот же CSV, сгенрированный приложением, в одном экселе «понимается», в другом — нет. Потому что Microsoft Office ориентируется на региональные настройки Windows, а там может быть что угодно. В России там указано, что разделитель — запятая,
Если CSV открывать не через меню «Данные», а напрямую, то Excel лишних вопросов не задает, и делает как ему кажется правильным. Например, поле со значением 1.24 он понимает по умолчанию как «24 января»
Эксель убивает ведующие нули и приводит типы даже тогда, когда значение указано в кавычках. Делать так не надо, это ошибка. Но чтобы обойти эту проблему экселя, можно сделать небольшой «хак» — значение начать со знака «равно», после чего поставить в кавычках то, что необходимо передать без изменения формата.
У экселя есть спецсимвол «равно», который в CSV рассматривается как идентификатор формулы. То есть, если в CSV встретится =2+3, он сложит два и три и результат впишет в ячейку. По стандарту он это делать не должен.

Пример валидного CSV, который можно использовать для тестов:

точно такой же SCSV:

Первый файлик, который реально COMMA-SEPARATED, будучи сохраненным в .csv, Excel-ом не воспринимается вообще.

Второй файлик, который по логике SCSV, экселом воспринимается и выходит вот что:

Учлись пробелы, окружающие разделители
Последний столбец вообще толком не распознался, несмотря на то, что данные в кавычках. Исключение составляет строка с «Петровым» — там корректно распозналось 1,24.
В поле индекс Excel «опустил» ведущие нули.
в самом правом поле последней строки пробелы перед кавычками перестали указывать на спецсимвол

Если же воспользоваться функционалом импорта (Данные -> Из файла) и обозвать при импорте все поля текстовыми, то будет следующая картина:

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

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

И вот что случаеся, если мы открываем этот файлик в экселе:

ТЕКСТ И ФОРМУЛЫ
Любой произвольный набор символов, если он не представляет собой число или дату, будет воспринят как текст: « привет », « 12 штук ».
Если данных в ячейке начинаются со знака =, -, + - Excel попытается вычислить выражение, следующее за знаком, т.к. посчитает это формулой.

ЧИСЛА, ПРОЦЕНТЫ, ДАТА, ВРЕМЯ

ЧИСЛА
Для представления чисел в Excel используется 15 цифр. Если введено число длиной более 15 цифр (как до, так и после десятичной запятой), Excel сохранит его с точностью до 15 цифр, заменив оставшиеся разряды нулями.
Очень большие числа автоматически представляются в экспоненциальном формате: 1,2Е+19 = 1,2*1019 или 1,2Е-19 = 1,2*10-19 . Наибольшее положительное число 9,9Е+307 ; наименьшее положительное число 1 Е-307 (числа, большие 9,9Е+307 становятся текстом, меньшие 1Е-307 – нулем)
Для определения целой и дробной частей чисел используется разделитель: в российском языковом стандарте это запятая; в английском – точка
Возможность Excel работать только с числами не более 15-ти знаков и такого числа разрядов после запятой порождает некоторые недопонимания в расчетах со стороны пользователей: Excel неправильно считает. Почему?
ПРОЦЕНТЫ
Главное здесь помнить, что 100% это по сути 1. Отсюда вывод - проценты это те же числа, только при вводе в ячейку значения со знаком процента(%) Excel сам автоматом делит введенное число на 100 и присваивает формат "Процентный", который визуально уже обратно умножает на 100. Но это только виртуальное умножение, на самом деле в ячейке хранится уже поделенное число. Т.е. если мы введем в ячейку значение 20%, то Excel запомнит его как значение 0,2 и применит процентный формат. И дальнейшие расчеты будут вестись именно со значением 0,2.

Есть и другой вариант: когда вы открываете файл, в котором уже есть подобные "числа"( 2.1 , 5.10 и т.п.). Они уже в момент открытия в большинстве случаев будут преобразованы в даты и менять формат ячеек уже поздно. Но можно попробовать схитрить и установить на время открытия таких файлов разделитель целой и дробной части - точка. Сделать это можно через Параметры:

Excel 2003: меню Сервис -Параметры. -вкладка Международные
Excel 2007: Кнопка Офис -Параметры (Excel Options) -Дополнительно (Advanced)
Excel 2010 и выше: Файл (File) -Параметры (Options) -Дополнительно (Advanced)

Снять галку с "Использовать системные разделители (Use system separators) " и установить в полях ниже свои. Как правило помогает установка в качестве разделителя целой и дробной части (decimal separator) точки вместо запятой.

Читайте также: