Excel 2010 региональные настройки
Опытные пользователи Excel весьма активно используют возможность Microsoft Excel настраивать интерфейс "под себя" - добавляют в него новые кнопки, убирают ненужные, создают целые панели или вкладки со своими авторскими наборами инструментов. Но есть одна проблема. При переходе на другой компьютер или после переустановки Windows / Office все эти ручные настройки часто теряются и приходится настраивать все заново. Давайте разберем, как можно подстраховаться и сохранить их на будущее - для восстановления или переноса на другой ПК.
Excel 2003 и старше
В старых версиях настройка интерфейса происходила через команду меню Сервис - Настройка (Tools - Customize) . Используя это диалоговое окно, можно:
- создавать дополнительные панели инструментов, наполняя их нужными лично Вам кнопками - вкладка Панели инструментов - кнопка Создать (Toolbars - New)
- добавлять недостающие и удалять ненужные кнопки - перетаскивание мышью с вкладки Команды (Commands) прямо на панель и обратно
На самом деле конфигурацию панелей инструментов Excel 2003 хранит в специальном файле, обновляя в нем данные после каждого сеанса работы с Excel. Файл располагается (скорее всего) по адресу C:\Documents and Settings\имя\Application Data\Microsoft\Excel и называется:
- Excel10.xlb - для версии Excel 2002
- Excel11.xlb - для версии Excel 2003
Вы можете легко скопировать этот файл на другой компьютер (с той же версией Excel) или сделать резервную копию этого файла на случай переустановки Microsoft Office.
Excel 2007
В этой версии Excel, к сожалению, не позволял пользователю настраивать ленту - добавлять и удалять кнопки можно было только на панель быстрого доступа (Quick Access Toolbar) в левом верхнем углу экрана. Настройки этой панели сохраняются в файле Excel.qat, лежащем в папке C:\Documents and Settings\имя\Local Settings\Application Data\Microsoft\Office
Excel 2010 и новее
В этой версии все сделано максимально удобно. Настраивать можно все - и ленту и панель быстрого доступа. При этом все эти нестандартные настройки можно просто экспортировать в небольшой текстовый файл, откуда их потом можно легко восстановить импортом. Чтобы это сделать выберите Файл - Параметры - Настройка ленты - Импорт-экспорт (File - Options - Customize Ribbon - Import-Export) :
В разных регионах (также называемых регионами) данные можно отображать разными способами. Например, в некоторых регионах используется формат даты по умолчанию дд.мм.дд.y, а в других — дд.мм.yyy. Региональный стандарт Power Query основан на региональных параметрах операционной системы. Дополнительные сведения см.в Windows региональных параметров для изменения внешнего вида некоторых типов данных и Изменение языка & региональных параметров на Компьютере Mac.
Заметка Может быть до трех параметров локализованного параметров: операционная система, Power Query или изменение типа. В случае конфликта порядок разрешения составляет 1), параметр Тип изменения, (2) Power Query и (3) операционная система.
Вы можете настроить шаг "Источник файла" для импортируемого CSV-файла или текстового файла, обновив формулу.
Чтобы открыть запрос, найдите ранее загруженную из редактора Power Query, выберем ячейку в данных и выберите запрос> изменить. Дополнительные сведения см. в этойExcel.
Импортируйте CSV-файл или текстовый файл. Дополнительные сведения см. в документе Импорт и экспорт текстовых файлов (.txt или .csv файлов).
В области Параметры запроса в области Примененныешаги выберите значок Параметры рядом с исходным шагом.
В диалоговом окне Значения, разделенные запятой выберите в списке локализованный.
Нажмите ОК.
При создании книги Excel с запросами в Power Query в качестве стандарта по умолчанию используется текущий локализованный операционной системы. Этот параметр определяет, как Power Query интерпретирует данные, импортируемые из текстовых, числовых и значений даты и времени. При отправке книги Excel, содержавшей запросы Power Query, другому пользователю, локализованный запрос Power Query будет сохранен в качестве локали, заданного автором (или последним пользователем, который сохранил документ). Это обеспечивает согласование результатов Power Query независимо от текущих параметров локализованной операционной системы.
Чтобы изменить параметры региональных стандартов запроса в Excel:
Выберите Параметры запроса> получить >данных.
В диалоговом окне Параметры запроса в поле ТЕКУЩАЯ КНИГАвыберите региональныеПараметры.
Выберите в списке локализованный список.
Помимо стандартного параметра Локализованный в книге, можно указать нестандартный параметр локализования, который будет применяться при применении операции Изменения типа к одному или несколько столбцов. Это полезно при импорте данных с различными региональными настройками в нескольких запросах, но при этом вы хотите, чтобы для всех запросов в книге был один из стандартных параметров.
С помощью операции Тип изменения вы можете указать конечный тип данных, а также языковой стандарт, который нужно использовать для преобразования.
Чтобы открыть запрос, найдите ранее загруженную из редактора Power Query, выберем ячейку в данных и выберите запрос> изменить. Дополнительные сведения см. в этойExcel.
Щелкните правой кнопкой мыши заглавную кнопку столбца и выберите изменить тип > с помощью локализованного.
В диалоговом окне Изменение типа с локализованным выберите тип данных и локализованный.
Всем доброго дня, я с программированием не знаком попробовал сам определить проблему мне её выделило пробовал подбирал но увы без успешно.Искал на форуме но увы из за нехватки знаний не знаю с какой стороны копать. Код писал не я и не крал человек его выложил в свободный доступ. Теперь более конкретно. При попытке загрузить данные выдает ошибку
[vba]
DATE_VALUE(i) = Worksheets(WorksheetsNumber).Cells(i + 2, DateColumn).Value
При смене региональных настроек на английский при русском вот так
HIGH_VALUE(i) = Worksheets(WorksheetsNumber).Cells(i + 2, HighColumn).Value * PipsPointer
В целом выглядет вот так
'Перенос значений котировок с листа в массивы
ReDim HIGH_VALUE(FullCounter)
ReDim LOW_VALUE(FullCounter)
ReDim DATE_VALUE(FullCounter)
For i = 0 To (FullCounter - 1)
HIGH_VALUE(i) = Worksheets(WorksheetsNumber).Cells(i + 2, HighColumn).Value * PipsPointer
LOW_VALUE(i) = Worksheets(WorksheetsNumber).Cells(i + 2, LowColumn).Value * PipsPointer
DATE_VALUE(i) = Worksheets(WorksheetsNumber).Cells(i + 2, DateColumn).Value
Next
2011.12.27 00:00 1.30600 1.30630 1.30500 1.30580 393 0
2011.12.27 00:15 1.30570 1.30620 1.30520 1.30520 278 0
2011.12.27 00:30 1.30530 1.30590 1.30520 1.30560 183 0
Кому не сложно посмотрите пожалуйста подскажите как вылечить или хоть отправьте в нужное место для поиска Если нужно скину файл с данными. У меня 2007 стоит
Заранее благодарен
Всем доброго дня, я с программированием не знаком попробовал сам определить проблему мне её выделило пробовал подбирал но увы без успешно.Искал на форуме но увы из за нехватки знаний не знаю с какой стороны копать. Код писал не я и не крал человек его выложил в свободный доступ. Теперь более конкретно. При попытке загрузить данные выдает ошибку
[vba]
DATE_VALUE(i) = Worksheets(WorksheetsNumber).Cells(i + 2, DateColumn).Value
При смене региональных настроек на английский при русском вот так
HIGH_VALUE(i) = Worksheets(WorksheetsNumber).Cells(i + 2, HighColumn).Value * PipsPointer
В целом выглядет вот так
'Перенос значений котировок с листа в массивы
ReDim HIGH_VALUE(FullCounter)
ReDim LOW_VALUE(FullCounter)
ReDim DATE_VALUE(FullCounter)
For i = 0 To (FullCounter - 1)
HIGH_VALUE(i) = Worksheets(WorksheetsNumber).Cells(i + 2, HighColumn).Value * PipsPointer
LOW_VALUE(i) = Worksheets(WorksheetsNumber).Cells(i + 2, LowColumn).Value * PipsPointer
DATE_VALUE(i) = Worksheets(WorksheetsNumber).Cells(i + 2, DateColumn).Value
Next
2011.12.27 00:00 1.30600 1.30630 1.30500 1.30580 393 0
2011.12.27 00:15 1.30570 1.30620 1.30520 1.30520 278 0
2011.12.27 00:30 1.30530 1.30590 1.30520 1.30560 183 0
Кому не сложно посмотрите пожалуйста подскажите как вылечить или хоть отправьте в нужное место для поиска Если нужно скину файл с данными. У меня 2007 стоит
Заранее благодарен Chifa
DATE_VALUE(i) = Worksheets(WorksheetsNumber).Cells(i + 2, DateColumn).Value
При смене региональных настроек на английский при русском вот так
HIGH_VALUE(i) = Worksheets(WorksheetsNumber).Cells(i + 2, HighColumn).Value * PipsPointer
В целом выглядет вот так
'Перенос значений котировок с листа в массивы
ReDim HIGH_VALUE(FullCounter)
ReDim LOW_VALUE(FullCounter)
ReDim DATE_VALUE(FullCounter)
For i = 0 To (FullCounter - 1)
HIGH_VALUE(i) = Worksheets(WorksheetsNumber).Cells(i + 2, HighColumn).Value * PipsPointer
LOW_VALUE(i) = Worksheets(WorksheetsNumber).Cells(i + 2, LowColumn).Value * PipsPointer
DATE_VALUE(i) = Worksheets(WorksheetsNumber).Cells(i + 2, DateColumn).Value
Next
2011.12.27 00:00 1.30600 1.30630 1.30500 1.30580 393 0
2011.12.27 00:15 1.30570 1.30620 1.30520 1.30520 278 0
2011.12.27 00:30 1.30530 1.30590 1.30520 1.30560 183 0
Кому не сложно посмотрите пожалуйста подскажите как вылечить или хоть отправьте в нужное место для поиска Если нужно скину файл с данными. У меня 2007 стоит
Заранее благодарен Автор - Chifa
Дата добавления - 15.04.2015 в 09:28
Добрый день!
Естественно, файл с данными нужно прикладывать. Иначе, как проверять?
[p.s.]Проверял на приложенном файле EURUSDM5.xlsx, который заполнил по аналогии с приведенным вами форматом, все работает. По крайней мере, ошибок не выдает
Добрый день!
Естественно, файл с данными нужно прикладывать. Иначе, как проверять?
[p.s.]Проверял на приложенном файле EURUSDM5.xlsx, который заполнил по аналогии с приведенным вами форматом, все работает. По крайней мере, ошибок не выдает KSV
Добрый день!
Естественно, файл с данными нужно прикладывать. Иначе, как проверять?
[p.s.]Проверял на приложенном файле EURUSDM5.xlsx, который заполнил по аналогии с приведенным вами форматом, все работает. По крайней мере, ошибок не выдает Автор - KSV
Дата добавления - 15.04.2015 в 11:52
[/vba]
Вот тут пришлось дописать .xlsm
Далее не смотрел - нет всех нужных файлов, вникать некогда. Hugo
[/vba]
Вот тут пришлось дописать .xlsm
Далее не смотрел - нет всех нужных файлов, вникать некогда. Автор - Hugo
Дата добавления - 15.04.2015 в 12:01
Hugo, я сделал проще.
Все Workbooks("MainBook.xlsm") заменил на ThisWorkbook. А зачем обращаться к текущей книге по имени?
И дописал функцию, которая возвращает нужную книгу (по имени, указанному на листе настроек, не добавляя явного расширения файла, т.к нужно учитывать, что расширение может быть и старым - .xls), если она уже открыта, а если нет, то открывает ее, если файл книги уже есть, или создает новую книгу и сохраняет ее с указанным именем.
Hugo, я сделал проще.
Все Workbooks("MainBook.xlsm") заменил на ThisWorkbook. А зачем обращаться к текущей книге по имени?
И дописал функцию, которая возвращает нужную книгу (по имени, указанному на листе настроек, не добавляя явного расширения файла, т.к нужно учитывать, что расширение может быть и старым - .xls), если она уже открыта, а если нет, то открывает ее, если файл книги уже есть, или создает новую книгу и сохраняет ее с указанным именем. KSV
KSV, Hugo, Спасибо что откликнулись сейчас буду играться, если не сложно то в двух словах скажите в чем причина чтоб знать где начинать копаться в случае такого коллапса
KSV, Hugo, Спасибо что откликнулись сейчас буду играться, если не сложно то в двух словах скажите в чем причина чтоб знать где начинать копаться в случае такого коллапса Chifa
Лучше всегда дописывать расширение - т.к. с расширением сработает всегда, а без - только если соотв. настроена Windows (как я понял - у Вас она именно так и настроена )
А ещё лучше писать код как рассказал KSV - я тоже в своих кодах никогда не использую такой подход как в Вашем файле, я всегда использую объекты и объектные переменные.
Лучше всегда дописывать расширение - т.к. с расширением сработает всегда, а без - только если соотв. настроена Windows (как я понял - у Вас она именно так и настроена )
А ещё лучше писать код как рассказал KSV - я тоже в своих кодах никогда не использую такой подход как в Вашем файле, я всегда использую объекты и объектные переменные. Hugo
Причина чего?
Причина возникновения ошибок - это неверно написанный код, даже если просто пользователь ввел не те данные, которые ожидались, или подсунул пустой лист котировок, неважно, все равно, правильно написанный код, должен учитывать возможность возникновения таких ситуаций, тем более, если этот код выкладывается как готовое решение.
В вашем случае, первая ошибка с которой я столкнулся (и не только я - Hugo вам тоже об этом написал), это неверное обращение к книге по имени (без расширения)
Дальнейшие ошибки обсуждать бессмысленно, т.к. код изначально написан "криво" - такое впечатление, что его записали макрорекордером, а потом чуть подправили
Причина чего?
Причина возникновения ошибок - это неверно написанный код, даже если просто пользователь ввел не те данные, которые ожидались, или подсунул пустой лист котировок, неважно, все равно, правильно написанный код, должен учитывать возможность возникновения таких ситуаций, тем более, если этот код выкладывается как готовое решение.
В вашем случае, первая ошибка с которой я столкнулся (и не только я - Hugo вам тоже об этом написал), это неверное обращение к книге по имени (без расширения)
Дальнейшие ошибки обсуждать бессмысленно, т.к. код изначально написан "криво" - такое впечатление, что его записали макрорекордером, а потом чуть подправили
Причина чего?
Причина возникновения ошибок - это неверно написанный код, даже если просто пользователь ввел не те данные, которые ожидались, или подсунул пустой лист котировок, неважно, все равно, правильно написанный код, должен учитывать возможность возникновения таких ситуаций, тем более, если этот код выкладывается как готовое решение.
В вашем случае, первая ошибка с которой я столкнулся (и не только я - Hugo вам тоже об этом написал), это неверное обращение к книге по имени (без расширения)
Дальнейшие ошибки обсуждать бессмысленно, т.к. код изначально написан "криво" - такое впечатление, что его записали макрорекордером, а потом чуть подправили
Chifa, так у вас данные должны грузиться из текстового файла? Я то вашу инструкцию не читал, просто посмотрел по коду, что ему нужно (а ему нужен был файл Excel), так и сделал
Так, а если исходные данные в текстовом формате, то нафига заставлять пользователя конвертировать его в Excel, если это можно делать автоматом.
[p.s.]Чуть позже напишу вам функцию.
Chifa, так у вас данные должны грузиться из текстового файла? Я то вашу инструкцию не читал, просто посмотрел по коду, что ему нужно (а ему нужен был файл Excel), так и сделал
Так, а если исходные данные в текстовом формате, то нафига заставлять пользователя конвертировать его в Excel, если это можно делать автоматом.
[p.s.]Чуть позже напишу вам функцию. KSV
Chifa, Кладите этот файл и файл котировок (EURUSDMonthly.prn) в одну папку (никакие другие файлы больше не нужны), открывайте этот файл и нажимайте кнопку "Построить график". Будет создан файл книги для графиков (на листе настроек его имя XOchartEURUSD, но можете поменять. ) и сохранен в этом же каталоге, для дальнейшего использования.
[p.s.]Пришлось вникать в код. До чего же гадко он написан.
И еще. А для чего файл котировок сохранять в юникоде? (ведь в нем нет ни одного символа Unicode) Чтобы размер файла был вдвое больше? Или Есть и другие файлы котировок, которые тоже подсовуются этому MainBook?
Chifa, Кладите этот файл и файл котировок (EURUSDMonthly.prn) в одну папку (никакие другие файлы больше не нужны), открывайте этот файл и нажимайте кнопку "Построить график". Будет создан файл книги для графиков (на листе настроек его имя XOchartEURUSD, но можете поменять. ) и сохранен в этом же каталоге, для дальнейшего использования.
[p.s.]Пришлось вникать в код. До чего же гадко он написан.
И еще. А для чего файл котировок сохранять в юникоде? (ведь в нем нет ни одного символа Unicode) Чтобы размер файла был вдвое больше? Или Есть и другие файлы котировок, которые тоже подсовуются этому MainBook? KSV
Но человек, который ответил на мой предыдущий вопрос, заставил меня обнаружить, что шаблон даты изменяется в соответствии с региональными настройками Windows.
Тем не менее, моя ОС (Windows 7) на английском языке, а также пакет Office. При просмотре в моих региональных настройках он показывает даже шаблон с использованием английского обозначения (дд.MM.yyyy)
Я хочу знать, есть ли способ отключить такое поведение в Excel, то есть я хочу всегда использовать английские шаблоны, а не локализованные, потому что я не хочу, чтобы поведение моего листа Excel изменялось в зависимости от локализации читателя.
Простым случаем будет переформатирование некоторого поля даты в компьютерно-ориентированный способ, например: "yyyymmdd_hhss", это распознается повсеместно и может быть легко отсортировано вверх и вниз. Но, поскольку я нахожусь во французской части Швейцарии, я должен написать "aaaammjj_hhss", и если я отправлю этот Excel коллеге в Цюрихе, он не сможет увидеть правильную дату, поскольку он получил швейцарскую немецкую локализацию (его превосходство будет ожидать "jjjjmmtt_hhss")
Мы были достаточно умны, чтобы установить все окна и офис на английском языке, но мы все еще сталкиваемся с такой проблемой, потому что эта ссылка на региональные настройки ОС.
Для меня изменение настроек Windows не вариант, потому что все остальные программы используют эти настройки.
Этот вопрос немного старый, но на сегодняшний день, похоже, нет ответа. Я также видел похожие вопросы на ряде сайтов, но пока не нашел ответа, который бы касался только встроенных функций Excel. Тем не менее, это довольно легко решить с VBA. Вам даже не нужно знать, как программировать, чтобы сделать это, потому что требуемая функция очень проста.
Открыв книгу, просто нажмите Alt+F11 (чтобы открыть редактор VBA). Затем щелкните пункт меню Вставить > Модуль. В новом модуле VBA введите следующее:
Это просто использует собственную функцию Format VBA вместо функции TEXT в Excel. Это именно то, что вы хотите.
Эта новая функция будет форматировать любую дату (или число) в соответствии с любой заданной вами форматной строкой. Он будет интерпретировать строку формата, используя стандартную (en-US) нотацию, независимо от региональных настроек операционной системы.
Чтобы использовать это в своей книге, просто введите =FMT(A1, "yyyymmdd_hhss") вместо =TEXT(A1, "yyyymmdd_hhss") . (Конечно, при необходимости вы можете изменить ссылку на ячейку и форматную строку.)
Кстати, вы можете назвать функцию как хотите. Я выбрал FMT потому что он был коротким и потому что функция может форматировать как числа, так и даты. Но вы можете выбрать что-то более описательное, если хотите. Просто не забудьте использовать то же имя в вашем рабочем листе, что и в коде VBA.
Обратите внимание, что строки формата VBA не совсем совпадают со строками пользовательского формата Excel (например, используйте "n" вместо "m" для минут), но они очень похожи. Посмотрите здесь для получения дополнительной информации или поиск MSDN для «Функция форматирования (Visual Basic для приложений)». Прокрутите вниз, чтобы увидеть различные спецификаторы формата даты.
Способ 2
Другой подход, который также использует VBA, но на самом деле может быть проще в обслуживании, заключается в следующем:
- Примените желаемый формат даты к ячейке, используя обычный диалог «Формат ячейки». Эта ячейка может быть на скрытом листе, если вы предпочитаете - она не должна отображаться для конечного пользователя. Предположим, вы применили формат yyyymmdd\_hhss к ячейке $ A $ 1 (обратите внимание, что подчеркивание должно быть экранировано, как показано на рисунке).
- Добавьте функцию GetFormat (показанную ниже) к модулю VBA в вашей книге.
- Введите =GetFormat($A$1, TRUE) в другую ячейку (например, $ B $ 1)
- Эта функция вернет локализованную версию строки формата. Таким образом, даже если вы изначально отформатировали $ A $ 1 с помощью yyyymmdd\_hhss , когда вы открываете книгу на компьютере, используя французский язык (например), функция покажет aaaammjj\_hhss .
- Теперь просто назовите вторую ячейку во всех ваших функциях TEXT . Например: =TEXT(F22, $B$1) .
Это позволяет нам "проверять" ячейку, которую вы первоначально отформатировали ($ A $ 1), чтобы получить локализованную строку формата. Эта строка будет представлять тот же формат, который вы применили, но она будет использовать правильные буквы для ТЕКСТА для интерпретации (например, "j" вместо "d"), поэтому отображаемое значение дат будет постоянным во всех локалях. Если вы хотите использовать только один формат даты для всей книги, вам нужно будет выполнить шаги 1-4 один раз. Затем повторите шаг 5 (функция TEXT) во всех ячейках, где вы в данный момент используете его, но вместо жесткого кодирования формата вы просто ссылаетесь на ячейку, содержащую локализованную строку формата ($ B $ 1 в инструкциях примера) ,
Обратите внимание, что второй аргумент GetFormat указывает функции, возвращать ли локализованную версию (которая зависит от региональных настроек) или "стандартную" версию (которая всегда основана на en-US).
Вот некоторые скриншоты, которые могут сделать это более ясным.
- На рисунке в столбце 1 перечислены несколько представлений одной даты с применением разных форматов.
- Обратите внимание, что строки 2 и 3 используют форматы даты "по умолчанию" в Excel. (Они отмечены звездочкой в диалоговом окне "Формат" и указывают на то, что должен использоваться формат даты по умолчанию). Также обратите внимание, что в строке 5 используется LCID в квадратных скобках, что делает язык, используемый для названий месяцев и дней, английским (разные LCID могут использоваться для указания других языков).
- Во втором столбце показан результат GetFormat(Cell, FALSE) для каждой ячейки в столбце 1. (Напомним, что FALSE для второго параметра заставляет функцию возвращать не локализованные форматы).
- В третьем столбце показано, что GetFormat(Cell, TRUE) для каждой ячейки в столбце 2. (т.е. в локализованных форматах).
- В четвертом столбце показан результат функции TEXT с использованием исходного необработанного значения даты и локализованного результата GetFormat для повторного создания формата, показанного в столбце 1. Однако обратите внимание, что к этому столбцу не применяется форматирование чисел. Значения являются прямым результатом функции TEXT.
Приведенные выше результаты для случая с английским (США) не очень интересны, но вы можете сравнить их со следующими результатами, которые были получены путем изменения региональных настроек моей операционной системы на различные другие локали. Важно отметить, что с помощью GetFormat в сочетании с TEXT мы можем сохранить постоянный результат для числовых форматов (тех, которые не включают названия дня или месяца) во всех локалях. И, ограничивая язык с помощью LCID (как в строке 5), мы можем даже сохранить постоянный формат, включив также названия дней и месяцев.
Этот метод работает для большинства языковых стандартов, однако следует отметить, что сценарии, используемые для представления индусско-арабских чисел, НЕ одинаковы во всех языковых стандартах. Поэтому региональные настройки, такие как настройки в непальском (Индия) регионе, приведут к форматам дат, которые "выглядят" иначе, чем даты в США. Но на самом деле они находятся в одном и том же "формате" с точки зрения позиций чисел - они просто используют разные символы для чисел.
При импорте в Excel данных из внешних программ, иногда возникает весьма неприятная проблема - дробные числа превращаются в даты:
Так обычно происходит, если региональные настройки внешней программы не совпадают с региональными настройками Windows и Excel. Например, вы загружаете данные с американского сайта или европейской учётной системы (где между целой и дробной частью - точка), а в Excel у вас российские настройки (где между целой и дробной частью - запятая, а точка используется как разделитель в дате).
При импорте Excel, как положено, пытается распознать тип входных данных и следует простой логике - если что-то содержит точку (т.е. российский разделитель дат) и похоже на дату - оно будет конвертировано в дату. Всё, что на дату не похоже - останется текстом.
Давайте рассмотрим все возможные сценарии на примере испорченных данных на картинке выше:
- В ячейке A1 исходное число 153.4182 осталось текстом, т.к. на дату совсем не похоже (не бывает 153-го месяца)
- В ячейке A2 число 5.1067 тоже осталось текстом, т.к. в Excel не может быть даты мая 1067 года - самая ранняя дата, с которой может работать Excel - 1 января 1900 г.
- А вот в ячейке А3 изначально было число 5.1987, которое на дату как раз очень похоже, поэтому Excel превратил его в 1 мая 1987, услужливо добавив единичку в качестве дня:
Вот такие варианты. И если текстовые числа ещё можно вылечить банальной заменой точки на запятую, то с числами превратившимися в даты такой номер уже не пройдет. А попытка поменять их формат на числовой выведет нам уже не исходные значения, а внутренние коды дат Excel - количество дней от 01.01.1900 до текущей даты:
Лечится вся эта история тремя принципиально разными способами.
Способ 1. Заранее в настройках
Если данные ещё не загружены, то можно заранее установить точку в качестве разделителя целой и дробной части через Файл - Параметры - Дополнительно (File - Options - Advanced) :
Снимаем флажок Использовать системные разделители (Use system separators) и вводим точку в поле Разделитель целой и дробной части (Decimal separator) .
После этого можно смело импортировать данные - проблем не будет.
Способ 2. Формулой
Если данные уже загружены, то для получения исходных чисел из поврежденной дата-тексто-числовой каши можно использовать простую формулу:
=--ЕСЛИ( ЯЧЕЙКА("формат";A1)="G" ; ПОДСТАВИТЬ(A1;".";",") ; ТЕКСТ(A1;"М,ГГГГ") )
В английской версии это будет:
=--IF (CELL ("format ";A1)="G"; SUBSTITUTE (A1;".";","); TEXT (A1;"M ,YYYY "))
Логика здесь простая:
- Функция ЯЧЕЙКА (CELL) определяет числовой формат исходной ячейки и выдаёт в качестве результата "G" для текста/чисел или "D3" для дат.
- Если в исходной ячейке текст, то выполняем замену точки на запятую с помощью функции ПОДСТАВИТЬ (SUBSTITUTE) .
- Если в исходной ячейке дата, то выводим её в формате "номер месяца - запятая - номер года" с помощью функции ТЕКСТ (TEXT) .
- Чтобы преобразовать получившееся текстовое значение в полноценное число - выполняем бессмысленную математическую операцию - добавляем два знака минус перед формулой, имитируя двойное умножение на -1.
Способ 3. Макросом
Если подобную процедуру лечения испорченных чисел приходится выполнять часто, то имеет смысл автоматизировать процесс макросом. Для этого жмём сочетание клавиш Alt + F11 или кнопку Visual Basic на вкладке Разработчик (Developer) , вставляем в нашу книгу новый пустой модуль через меню Insert - Module и копируем туда такой код:
Останется выделить проблемные ячейки и запустить созданный макрос сочетанием клавиш Alt + F8 или через команду Макросы на вкладке Разработчик (Developer - Macros) . Все испорченные числа будут немедленно исправлены.
Читайте также: