При замене символов меняется формат эксель
В Excel 2016 иногда происходит самопроизвольное изменение формата ячеек на финансовый. Происходит это после обычного сохранения файла. При последующем открытии уже формат изменен.
Создал простую таблицу, в ней есть поля с форматом Дата, обычный, число. После нескольких дней работы с файлом (добавление данных вручную, без копирований с других таблиц) формат числовых ячеек меняется на финансовый и появляется доллар в ячейках. Приходится менять формат этих колонок вручную, по истечение некоторого времени, формат колонок снова переключается на финансовый. Причем все колонки, которые находятся справа от таблицы так же формат в них становится финансовый. Хотя изначально, формат в них был Обычный.
Почему появляется данная проблема и как сделать, чтобы в будущем формат ячеек не менялся?
Переустановка Office 2016 не помогла. Создание нового чистого файла и переноса данных из старого файла в новый - не помогло.
Эта цепочка заблокирована. Вы можете просмотреть вопрос или оставить свой голос, если сведения окажутся полезными, но вы не можете написать ответ в этой цепочке.
Оскорбление — это любое поведение, которое беспокоит или расстраивает человека или группу лиц. К угрозам относятся любые угрозы самоубийством, насилием, нанесением ущерба и др. Любое содержимое для взрослых или недопустимое на веб-сайте сообщества. Любое изображение, обсуждение наготы или ссылка на подобные материалы. Оскорбительное, грубое или вульгарное поведение и другие проявления неуважения. Любое поведение, нарушающее лицензионные соглашения, в том числе предоставление ключей продуктов или ссылок на пиратское ПО. Незатребованная массовая рассылка или реклама. Любые ссылки или пропаганда сайтов с вирусным, шпионским, вредоносным или фишинговым ПО. Любое другое неуместное содержимое или поведение в соответствии с правилами использования и кодексом поведения. Любое изображение, ссылка или обсуждение, связанные с детской порнографией, детской наготой или другими вариантами оскорбления или эксплуатации детей.
Добрый день, форумчане!
Такая проблема - замучила уже совсем.
Суть:
Создаю книгу, заполняю таблицу, сохраяю, закрываю.
При последующем открытии формат ВСЕХ ячеек на ВСЕХ листах - денежный, проставлены значки евро, 2 знака после запятой.
Исправляю форматы, сохраняю, закрываю.
При открытии - опять - денежный формат. Причем как-то выборочно - то не при каждом открытии, то форматируются в "денежный формат" часть ячеек.
Очень редко - формат даты, или денежный, но в рублях.
Это происходит только на рабочекм ноутбуке.
Файлы пересылаю коллегам по работе - отчеты, таблицы и т.д. - очень всем это не нравится, а мне -уже ой как надоело.
Подскажите - что можно сделать?
Предполагаю, что решение может быть очень простым, но моих знаний не хватает.
Добрый день, форумчане!
Такая проблема - замучила уже совсем.
Суть:
Создаю книгу, заполняю таблицу, сохраяю, закрываю.
При последующем открытии формат ВСЕХ ячеек на ВСЕХ листах - денежный, проставлены значки евро, 2 знака после запятой.
Исправляю форматы, сохраняю, закрываю.
При открытии - опять - денежный формат. Причем как-то выборочно - то не при каждом открытии, то форматируются в "денежный формат" часть ячеек.
Очень редко - формат даты, или денежный, но в рублях.
Это происходит только на рабочекм ноутбуке.
Файлы пересылаю коллегам по работе - отчеты, таблицы и т.д. - очень всем это не нравится, а мне -уже ой как надоело.
Подскажите - что можно сделать?
Предполагаю, что решение может быть очень простым, но моих знаний не хватает. A138
Суть:
Создаю книгу, заполняю таблицу, сохраяю, закрываю.
При последующем открытии формат ВСЕХ ячеек на ВСЕХ листах - денежный, проставлены значки евро, 2 знака после запятой.
Исправляю форматы, сохраняю, закрываю.
При открытии - опять - денежный формат. Причем как-то выборочно - то не при каждом открытии, то форматируются в "денежный формат" часть ячеек.
Очень редко - формат даты, или денежный, но в рублях.
Это происходит только на рабочекм ноутбуке.
Файлы пересылаю коллегам по работе - отчеты, таблицы и т.д. - очень всем это не нравится, а мне -уже ой как надоело.
Подскажите - что можно сделать?
Предполагаю, что решение может быть очень простым, но моих знаний не хватает. Автор - A138
Дата добавления - 11.12.2015 в 15:46
Здравствуйте!
Имеется таблица с данными в формате "ДД.ММ.ГГГГ". Подскажите, пожалуйста, как изменить отображение уже введенных дат на формат "ГГГГ". Т.е. чтобы изменились уже введенные данные.
Заранее спасибо!
Здравствуйте!
Имеется таблица с данными в формате "ДД.ММ.ГГГГ". Подскажите, пожалуйста, как изменить отображение уже введенных дат на формат "ГГГГ". Т.е. чтобы изменились уже введенные данные.
Заранее спасибо! korantole
, то, выделить данный, нажать правую кнопку мыши, выбрать "Формат ячейки", вкладка "Число", все форматы, в поле "Тип" написать ГГГГ
, то, выделить данный, нажать правую кнопку мыши, выбрать "Формат ячейки", вкладка "Число", все форматы, в поле "Тип" написать ГГГГ _Boroda_
, то, выделить данный, нажать правую кнопку мыши, выбрать "Формат ячейки", вкладка "Число", все форматы, в поле "Тип" написать ГГГГ Автор - _Boroda_
Дата добавления - 10.05.2014 в 12:55
, то, выделить данный, нажать правую кнопку мыши, выбрать "Формат ячейки", вкладка "Число", все форматы, в поле "Тип" написать ГГГГ
формат ячеек я, конечно, поменял на "ГГГГ", только вот уже введенные данные не изменились и остались в формате "ДД.ММ.ГГГГ"
, то, выделить данный, нажать правую кнопку мыши, выбрать "Формат ячейки", вкладка "Число", все форматы, в поле "Тип" написать ГГГГ
формат ячеек я, конечно, поменял на "ГГГГ", только вот уже введенные данные не изменились и остались в формате "ДД.ММ.ГГГГ" korantole
, то, выделить данный, нажать правую кнопку мыши, выбрать "Формат ячейки", вкладка "Число", все форматы, в поле "Тип" написать ГГГГ
формат ячеек я, конечно, поменял на "ГГГГ", только вот уже введенные данные не изменились и остались в формате "ДД.ММ.ГГГГ" Автор - korantole
Дата добавления - 10.05.2014 в 13:01
Спасибо откликнувшимся, я сам разобрался: после изменения формата всего столбца нужно было изменить (отредактировать) данные во всех уже заполненных ячейках. Для этого заменил (CTRL+H) все точки "." на точки ".", таким образом были отредактированы данные в каждой ячейке с датой.
Если кто-то знает способ проще, напишите, пожалуйста.
Спасибо откликнувшимся, я сам разобрался: после изменения формата всего столбца нужно было изменить (отредактировать) данные во всех уже заполненных ячейках. Для этого заменил (CTRL+H) все точки "." на точки ".", таким образом были отредактированы данные в каждой ячейке с датой.
Если кто-то знает способ проще, напишите, пожалуйста. korantole
При импорте в 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) . Все испорченные числа будут немедленно исправлены.
Если для каких-либо ячеек на листе был установлен текстовый формат (это мог сделать пользователь или программа при выгрузке данных в Excel), то введенные потом в эти ячейки числа Excel начинает считать текстом. Иногда такие ячейки помечаются зеленым индикатором, который вы, скорее всего, видели:
Причем иногда такой индикатор не появляется (что гораздо хуже).
В общем и целом, появление в ваших данных чисел-как-текст обычно приводит к большому количеству весьма печальных последствий:
- перестает нормально работать сортировка - "псевдочисла" выдавливаются вниз, а не располагаются по-порядку как положено:
- функции типа ВПР (VLOOKUP) не находят требуемые значения, потому как для них число и такое же число-как-текст различаются:
- при фильтрации псевдочисла отбираются ошибочно
- многие другие функции Excel также перестают нормально работать:
- и т.д.
Особенно забавно, что естественное желание просто изменить формат ячейки на числовой - не помогает. Т.е. вы, буквально, выделяете ячейки, щелкаете по ним правой кнопкой мыши, выбираете Формат ячеек (Format Cells) , меняете формат на Числовой (Number) , жмете ОК - и ничего не происходит! Совсем!
Способ 1. Зеленый уголок-индикатор
Если на ячейке с числом с текстовом формате вы видите зеленый уголок-индикатор, то считайте, что вам повезло. Можно просто выделить все ячейки с данными и нажать на всплывающий желтый значок с восклицательным знаком, а затем выбрать команду Преобразовать в число (Convert to number) :
Все числа в выделенном диапазоне будут преобразованы в полноценные.
Если зеленых уголков нет совсем, то проверьте - не выключены ли они в настройках вашего Excel (Файл - Параметры - Формулы - Числа, отформатированные как текст или с предшествующим апострофом).
Способ 2. Повторный ввод
Если ячеек немного, то можно поменять их формат на числовой, а затем повторно ввести данные, чтобы изменение формата вступило-таки в силу. Проще всего это сделать, встав на ячейку и нажав последовательно клавиши F2 (вход в режим редактирования, в ячейке начинает мигаеть курсор) и затем Enter. Также вместо F2 можно просто делать двойной щелчок левой кнопкой мыши по ячейке.
Само-собой, что если ячеек много, то такой способ, конечно, не подойдет.
Способ 3. Формула
Можно быстро преобразовать псевдочисла в нормальные, если сделать рядом с данными дополнительный столбец с элементарной формулой:
Двойной минус, в данном случае, означает, на самом деле, умножение на -1 два раза. Минус на минус даст плюс и значение в ячейке это не изменит, но сам факт выполнения математической операции переключает формат данных на нужный нам числовой.
Само-собой, вместо умножения на 1 можно использовать любую другую безобидную математическую операцию: деление на 1 или прибавление-вычитание нуля. Эффект будет тот же.
Способ 4. Специальная вставка
Этот способ использовали еще в старых версиях Excel, когда современные эффективные менеджеры под стол ходили зеленого уголка-индикатора еще не было в принципе (он появился только с 2003 года). Алгоритм такой:
- в любую пустую ячейку введите 1
- скопируйте ее
- выделите ячейки с числами в текстовом формате и поменяйте у них формат на числовой (ничего не произойдет)
- щелкните по ячейкам с псевдочислами правой кнопкой мыши и выберите команду Специальная вставка (Paste Special) или используйте сочетание клавиш Ctrl+Alt+V
- в открывшемся окне выберите вариант Значения (Values) и Умножить (Multiply)
По-сути, мы выполняем то же самое, что и в прошлом способе - умножение содержимого ячеек на единицу - но не формулами, а напрямую из буфера.
Способ 5. Текст по столбцам
Если псеводчисла, которые надо преобразовать, вдобавок еще и записаны с неправильными разделителями целой и дробной части или тысяч, то можно использовать другой подход. Выделите исходный диапазон с данными и нажмите кнопку Текст по столбцам (Text to columns) на вкладке Данные (Data) . На самом деле этот инструмент предназначен для деления слипшегося текста по столбцам, но, в данном случае, мы используем его с другой целью.
Пропустите первых два шага нажатием на кнопку Далее (Next) , а на третьем воспользуйтесь кнопкой Дополнительно (Advanced) . Откроется диалоговое окно, где можно задать имеющиеся сейчас в нашем тексте символы-разделители:
После нажатия на Готово Excel преобразует наш текст в нормальные числа.
Способ 6. Макрос
Если подобные преобразования вам приходится делать часто, то имеет смысл автоматизировать этот процесс при помощи несложного макроса. Нажмите сочетание клавиш Alt+F11 или откройте вкладку Разработчик (Developer) и нажмите кнопку Visual Basic. В появившемся окне редактора добавьте новый модуль через меню Insert - Module и скопируйте туда следующий код:
Теперь после выделения диапазона всегда можно открыть вкладку Разрабочик - Макросы (Developer - Macros) , выбрать наш макрос в списке, нажать кнопку Выполнить (Run ) - и моментально преобразовать псевдочисла в полноценные.
Также можно добавить этот макрос в личную книгу макросов, чтобы использовать позднее в любом файле.
С датами бывает та же история. Некоторые даты тоже могут распознаваться Excel'ем как текст, поэтому не будет работать группировка и сортировка. Решения - те же самые, что и для чисел, только формат вместо числового нужно заменить на дату-время.
Читайте также: