Ошибки формулы в Excel при неправильном наборе сепаратора списков

Office 365 ProPlus переименован в Майкрософт 365 корпоративные приложения. Для получения дополнительной информации об этом изменении прочитайте этот блог.

Симптомы

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

Причина

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

• Сепаратор списка в Windows — Региональные параметры не совпадает с типом формулы Excel.
• Параметр «Использование системных сепараторов» задаваем в Excel Advanced Options и не соответствует типу формулы Excel.

Решение

Чтобы обойти сообщение об ошибке, выполните следующие действия:

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

Если включено «Использование системных сепараторов», посмотрите параметры Region (Regional) в панели управления Windows. Сепаратор списка указывается в дополнительных параметрах.

При создании формулы (ы) используйте тот же символ, что и назначенный сепаратор списка в настройках Windows.

Используйте кнопку Сброс, чтобы сбросить параметры, чтобы соответствовать по умолчанию для выбранной страны или изменить сепаратор списка Windows на другой символ (некоторые специальные символы не могут использоваться).

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

Запятая — это сепаратор списка по умолчанию для США — английский язык.

Изменение сепаратора списка в параметрах Региона Windows повлияет на делимитер, используемый при открытии или сохранении файла, разделенного запятой(csv), так как Excel использует символ разделитель списка Windows для делимитера в файлах csv.

Что делать, если программа умножает все числа на 1000

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

Дело тут не в ошибках программы, а в настройках Windows. Как вы, наверное, знаете, в разных странах принято по-разному писать числа с дробной частью. Например, в Советском Союзе было принято при записи числа отделять целую часть от дробной запятой, например: «1,23», тогда как в американской системе для этих же целей служит точка: «1.23». В настройках операционной системы Windows вы можете указать, какой символ вы хотите использовать в качестве разделителя; программа Тирика-Магазин умеет работать и в том случае, если в качестве разделителя целой и дробной части используется запятая, и в случае, если используется точка.

В Windows есть еще одна настройка: разделитель групп разрядов. Человеку сложно прочитать число с большим количеством знаков, например, «1234765785678», и для того, чтобы сделать такую запись более читаемой, его часто записывают так: «1 234 765 785 678» либо так: «1,234,765,785,678» разделяя группы разрядов (единицы, тысячи, миллионы) каким-либо символом, например, пробелом или запятой. Программа Тирика-магазин понимает такую запись числа и может правильно прочитать это число в любом из трех приведенных вариантов записи.

Проблемы начинаются в случае, если, например, в качестве разделителя групп разрядов в Windows указана запятая, а в качестве разделителя целой и дробной части — точка. В этом случае программа Тирика-Магазин не может понять, где у числа целая и где дробная часть, и может посчитать число «1,023» как «одна тысяча двадцать три», тогда как имеется в виду «одна целая двадцать три тысячных». В этом случае вам нужно перенастроить Windows т.о., чтобы у нее в настройках не было указано в качестве разделителя групп разрядов запятая, а в качестве разделителя целой и дробной части — точка (или наоборот). Хорошей идеей будет использовать в качестве разделителя групп разрядов пробел, а в качестве разделителя целой и дробной части точку либо запятую — как вам будет удобнее.

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

Настройка Windows 7

Нажмите кнопка Пуск, далее Панель Управления (см. снимок окна ниже):

В панели управления зайдите по ссылке в секцию «Часы, язык и регион» и вы попадёте в следующее окно:

В этом окне выберите ссылку «Изменение форматов даты, времени и чисел» и вы попадёте в окно «Язык и региональные стандарты»:

Далее в этом окне нажать кнопку «Дополнительные Параметры» и вы попадёте в окно, где нужно внести правильные настройки:

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

• Разделитель целой и дробной части — выставить то, к чему привыкли, например, либо запятую, либо точку
• Разделитель групп разрядов — поставить пробел

После внесения изменений в настройки нужно нажать кнопку ОК для сохранения изменений. Для того, чтобы новые параметры вступили в силу, перезапустите программу Тирика-магазин.

Настройки для Windows 8

В Windows 8 нет привычной нам по предыдущим версиям кнопки Пуск, поэтому в Панель Управления Windows нужно попасть другим способом:

• Опустить мышь в правый нижний угол экрана
• Справа выдвинется панель — нажать кнопку с шестерёнкой по имени «параметры»
• Выдвинется другая панель — на ней выбрать пункт «Панель Управления»
• В Панели Управления в секции «Часы, язык и регион» выбрать ссылку «Изменение форматов даты, времени и чисел», нажать кнопку «дополнительные параметры»
• Далее аналогично инструкциям для Windows 7 выше

После внесения изменений в настройки нужно нажать кнопку ОК для сохранения изменений. Для того, чтобы новые параметры вступили в силу, перезапустите программу Тирика-магазин.

Настройки для Windows XP

Зайдите, пожалуйста, в меню Пуск, далее выберите Панель Управления, и в панели управления выберите раздел «Дата, время, язык и региональные стандарты»:

В открывшемся окне выберите «Изменение формата отображения чисел, даты и времени»:

После этого вы попадёте в окно «Язык и региональные стандарты». В этом окне на закладке «Региональные параметры» нажмите кнопку «Настройка»:

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

• Разделитель целой и дробной части — выставить то, к чему вы привыкли, например, запятую либо точку
• Разделитель групп разрядов — поставить пробел:

После внесения изменений в настройки нужно нажать кнопку ОК для сохранения изменений. Для того, чтобы новые параметры вступили в силу, перезапустите программу Тирика-Магазин.

Практическое руководство. Отображение списка элементов, разделенных запятыми How to: Display an item list separated with commas

При работе со списками элементов в Microsoft Build Engine (MSBuild) иногда бывает полезно отобразить содержимое этих списков в удобном для чтения виде. When you work with item lists in Microsoft Build Engine (MSBuild), it is sometimes useful to display the contents of those item lists in a way that is easy to read. Либо у вас может быть задача, принимающая список элементов, разделенных специальной строкой. Or, you might have a task that takes a list of items separated with a special separator string. В обоих случаях вы можете указать строку разделителя для списка элементов. In both of these cases, you can specify a separator string for an item list.

Разделение элементов в списке запятыми Separate items in a list with commas

По умолчанию MSBuild разделяет элементы в списке точками с запятой. By default, MSBuild uses semicolons to separate items in a list. Например, рассмотрим элемент Message со следующим значением: For example, consider a Message element with the following value:

Когда список элементов @(TXTFile) содержит элементы App1.txt, App2.txt и App3.txt, сообщение имеет вид: When the @(TXTFile) item list contains the items App1.txt, App2.txt, and App3.txt, the message is:

This is my list of TXT files: App1.txt;App2.txt;App3.txt

Если вы хотите изменить поведение по умолчанию, можете задать свой разделитель. If you want to change the default behavior, you can specify your own separator. Для этого используется следующий синтаксис: The syntax for specifying an item list separator is:

Разделитель может быть отдельным знаком или строкой и должен быть заключен в одиночные кавычки. The separator can be either a single character or a string and must be enclosed in single quotes.

Вставка запятой и пробела между элементами To insert a comma and a space between items

Используйте нотацию элемента, аналогичную следующей: Use item notation similar to the following:

Пример Example

В этом примере задача Exec запускает средство findstr для поиска указанных текстовых строк в файле Phrases.txt. In this example, Exec task runs the findstr tool to find specified text strings in the file, Phrases.txt. В команде findstr искомые литеральные строки обозначены параметром -c:, поэтому между элементами в списке @(Phrase) вставляется разделитель элементов -c: . In the findstr command, literal search strings are indicated by the -c: switch, so the item separator, -c: is inserted between items in the @(Phrase) item list.

Для этого примера эквивалентная команда в командной строке имеет вид: For this example, the equivalent command-line command is:

Как обойти ограничение Excel и сделать выпадающий список зависимым

Недавно дочь обратилась с вопросом, нельзя ли в Excel выпадающий в ячейке список сделать контекстным, например, зависящим от содержания ячейки, находящейся слева от ячейки со списком (рис. 1)? Я довольно давно не использовал в работе выпадающие списки, поэтому для начала решил освежить свои знания по вопросу проверки данных в Excel.

Рис. 1. Состав выпадающего списка зависит от содержания соседней ячейки

Скачать заметку в формате Word или pdf, примеры в формате Excel2007

Команда Проверка данных находится на вкладке Данные, область Работа с данными.

Примечание. Иногда команда Проверка данных может быть недоступна:

• Возможно, в настоящее время вводятся данные. Во время ввода данных в ячейку команда Проверка данных недоступна. Чтобы завершить ввод данных, нажмите клавишу ВВОД или ESC.
• Возможно, лист защищен или является общим. Если лист защищен или является общим, изменить параметры проверки данных невозможно. Снимите защиту или отмените режим «общий».
• Возможно, таблица Excel связана с узлом SharePoint. Невозможно добавить проверку данных в таблицу Excel, которая связана с узлом SharePoint. Чтобы добавить проверку данных, необходимо удалить связь таблицы Excel или преобразовать ее в диапазон.

К сожалению, Excel в своем стандарте позволяет делать списки только на основе:

• имени массива
• диапазона ячеек
• прямого перечисления элементов списка (рис. 2).

Примечание. Элементы списка вводите через стандартный разделитель элементов списка Microsoft Windows (в русском Excel по умолчанию это точка с запятой).

Рис. 2. Возможные источники списка: вверху – имя массива; посередине – диапазон ячеек; внизу – элементы списка

Попытка ввести формулу в поле Источник диалогового окна Проверка вводимых значений заканчивается неудачей (рис. 3). Видно, что Excel не воспринял значение ячейки D2 ( » цвет » ), как имя массива, и просто включил это значение в качестве единственного элемента списка.

Рис. 3. Недопустимый источник списка – формула

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

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

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

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

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

И всё же в Excel есть одна функция непрямого действия. На английском языке у нее говорящее название – INDIRECT. На русском – название функции ни о чем – ДВССЫЛ… В чем же заключается непрямое действие? В отличие от других функций Excel, ДВССЫЛ возвращает не значение, хранящееся в ячейке, а ссылку, хранящуюся в ячейке. Непонятно? Сам «продирался» через это с трудом 🙂 Попробую пояснить. Вот что написано в справке Excel: ДВССЫЛ – возвращает ссылку, заданную текстовой строкой. Ссылки немедленно вычисляются для вывода их содержимого (рис. 4).

Рис. 4. Как работает функция ДВССЫЛ: вверху – формулы, внизу – значения

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

Итак, еще раз, ДВССЫЛ возвращает ссылку, а не значение, хранящееся в ячейке. Ссылка немедленно вычисляется, и выводится ее значение (или содержимое). Именно это свойство позволит нам ввести непрямую ссылку на соседнюю ячейку так, что вернется не значение, хранящееся в этой соседней ячейке (как на рис. 3), а ссылка, хранящаяся в ячейке, эта ссылка тут же вычисляется, и получается имя массива (рис. 5).

Рис. 5. Формирование списка, зависящего от значения в левой ячейке

Примечание. Ссылка в формуле =ДВССЫЛ(D2) должна быть относительной (D2), а не абсолютной ($D$2). Подробнее об этом см. раздел Тип ссылок на ячейки в формулах для проверки данных заметки Excel. Проверка данных.

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