Как исправить ошибку microsoft excel «слишком много разных форматов ячеек»

Исправление чисел, превратившихся в даты

При импорте в 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) . Все испорченные числа будут немедленно исправлены.

Не изменяется формат ячейки

В который раз на одном из форумов увидел вопрос с описанием непонятного бага: при попытке изменить формат ячеек или вызывать диалоговое окно Формат ячеек(правая кнопка мыши-Формат ячеек (Cell Format) ничего не происходит. Что делает невозможным изменить визуальный стиль ячеек. При этом происходить данная проблема может не для всех ячеек, а выборочно для любой ячейки листа. Если кто-то не понял проблему: для некоторых ячеек невозможно назначить или изменить границы ячеек, цвет заливки, цвет и стиль шрифта и т.п.

Возможная причина : в какой-то момент при работе с файлом в ячейках возник конфликт форматов и стиль ячеек был заблокирован. Как правило это происходит при работе с файлом в версиях Excel 2007 и выше. Я предполагаю, что связано это с тем, что в новых форматах файлов данные о форматах ячеек хранятся в XML-схемах и порой при изменениях происходит конфликт стилей и Excel не может определить текущий формат ячейки и как следствие изменить. Не могу сказать 100% действительно ли в этом причина, но других вариантов у меня пока нет. Надо узнавать у Microsoft, но никто из «владельцев» проблемы не может сказать в какой момент причина появилась, а без этого вряд ли получиться что-то нарыть. Сам я смоделировать подобную ситуацию не смог.

Решение : я пока знаю два возможных варианта решения данной проблемы. Какой из них использовать, если вы столкнулись с данной проблемой — выбирать вам.

1. Если книга в формате .xls — сохраняете книгу в формате 2007(Сохранить как-Книга Excel (*.xlsx) (Save As- Excel Workbook (*.xlsx))
2. закрываете книгу
3. меняете расширение для книги на .zip или .rarЕсли не отображается расширение: Панель управления-Свойства папки(для Win 7 – Параметры папок)- вкладка Вид- Снять галочку с «Скрывать расширение для зарегистрированных типов файлов»
4. открываете любым архиватором
5. в архиве будет три папки: _rels, docProps и xl
6. открываете папку xl и удаляете файл Styles.xml
7. закрываете архив
8. меняете расширение архива на первоначальное — .xlsx
9. открываете книгу, согласившись с восстановлением содержимого: после чего получить небольшое сообщение об удаленных стилях, которые не удалось восстановить:

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

1. копируете любую ячейку, формат для которой изменяется без проблем
2. выделяете «непослушные» ячейки-правая кнопка мыши-Специальная вставка (PasteSpecial) — Форматы (Formats)

Данный вариант (в книгах, любезно предоставленных носителями проблемы) у меня срабатывал довольно неплохо. В чем его очевидное преимущество перед первым вариантом — вы не теряете все форматы в книге. Однако есть и другой нюанс — нельзя будет с достоверностью сказать, что проблема в очень скором будущем не повторится. Хотя нельзя сказать и того, что она повторится.

Формат ячеек в Excel: Числовыми форматы

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

• Если вы вводите значение, дополненное знаком доллара (или другой валюты) и содержащее два десятичных знака, то Excel назначит ему финансовый числовой формат.
• Если вы вводите целочисленное значение, дополненное символом процента, то Excel назначит ему процентный формат.
• Если вводится значение в одном из стандартных форматов дат Excel, например 06/11/19 или 6-ноя-19, то ему будет назначен формат даты. Данные в формате дат хранятся в виде числовых значений.

Значения можно форматировать как в процессе ввода в ячейку (как в случае с вводом дат), так и после завершения ввода. Обычно применяют “позднее” форматирование, поскольку оно предполагает выполнение всего лишь двух действий.

1. Выделите все ячейки, содержащие значения, которые нужно отформатировать.
2. Выберите для группы числовой формат с помощью либо раскрывающегося меню соответствующей кнопки вкладки Главная, либо вкладки Число диалогового окна Формат ячеек.

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

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

Формат ячеек в Excel: Процентный формат

На многих рабочих листах используются процентные значения для отображения годовых ставок, процента инфляции, прибыльности и т.п. Чтобы вставить процентное значение в ячейку, введите числовое значение, а затем укажите символ процента. Например, чтобы ввести годовую ставку в 12 процентов, следует ввести в ячейку значение 12%. В ответ на это Excel автоматически назначит ячейке процентный формат и в то же время разделит введенное значение на 100. В данном случае в ячейке мы увидим 0,12.

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

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

Как отформатировать ячейки с помощью мыши

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

1. Выберите ячейки, содержащие даты, формат которых вы хотите изменить.

2. Щелкните правой кнопкой мыши выделение и выберите « Формат ячеек» . Либо нажмите Ctrl + 1, чтобы открыть диалоговое окно «Формат ячеек».

   Либо выберите « Домой» > « Номер» , нажмите стрелку и выберите « Формат номера» в правом нижнем углу группы. Или в группе « Число » вы можете выбрать раскрывающийся список, а затем выбрать « Дополнительные форматы номеров» .

3. Выберите « Дата» или, если вам нужен более индивидуальный формат, выберите « Пользовательский» .

4. В поле «Тип» выберите параметр, который наилучшим образом соответствует вашим потребностям форматирования. Это может занять немного проб и ошибок, чтобы получить правильное форматирование.

5. Выберите OK, когда вы выбрали формат даты.

Независимо от того, используется ли категория «Дата» или «Пользовательский», если вы видите один из типов со звездочкой ( * ), этот формат будет меняться в зависимости от выбранной локали (местоположения).

Автоматическое преобразование формата ячейки в EXCEL при вводе ТЕКСТовых данных (Часть 1)

history 25 апреля 2013 г.

Преобразование в Числовой формат

При вводе пользователем данных, EXCEL пытается определить тип вводимых данных. Если данные можно перевести в формат даты или числа, то EXCEL производит соответствующее преобразование и форматирование. Преобразование производится «на лету» после нажатия клавиши ENTER . Часто текстовые данные действительно имеют формат дат (1-05, 2.12) или чисел (000123456), но на самом деле ими не являются. В этом случае необходимо запретить EXCEL выполнять автоматическое преобразование и форматирование.

При вводе текстовых данных определенного вида, например: 1-05, 2.12, 1 e 2, 00012456 , EXCEL автоматически меняет формат ячейки и преобразовывает текст в другой, как ему кажется, подходящий тип данных. Это приводит к тому, что 1-05 и 2.12 будут преобразованы в даты ( 01.май и 02.дек ), а 1 e 2 и 000123456 в числа (в 1,00 E +02 , т.е. 100 и в 123456 ).

Допустим, мы вводим текст 1-05 (пусть это будет артикул товара) и хотим, чтобы EXCEL сохранил в ячейке именно текст, а не что-либо другое.

Что же происходит при вводе значения в ячейку? Если ячейка имеет формат Общий (формат ячейки по умолчанию), EXCEL преобразует текст 1-05 в дату 01.май : формат ячейки изменяется с Общий на формат Дата (точнее все форматы /ДД.МММ). Если мы вернем ячейке формат Общий , то в ячейке увидим число 40299 (если сейчас 2010 год), что соответствует 01.05.2010 . Вернуть исходное значение 1-05 простым преобразованием формата уже не представляется возможным — EXCEL интерпретировал 1-05 , как дату 01.05.2010 и вставил в ячейку соответствующее ей число 40299 .

Универсальным способом отмены автоматического преобразования формата является явное указание желаемого формата перед вводом данных. Т.е. выделяем ячейки, в которые необходимо ввести заведомо текстовые данные и устанавливаем им текстовый формат. Для вызова формата ячейки нажмите CTRL+1 .

Есть и другой способ. Чтобы EXCEL автоматически при вводе текстовых данных назначал ячейкам текстовый формат, перед вводимым текстом можно ввести знак апострофа (‘), т.е. в нашем случае необходимо ввести ‘1-05 (давайте введем его на новом листе в ячейку А1 ). Почему именно апостроф? Есть две причины: этот символ не отображается в ячейке (если он введен первым) и он игнорируется формулами. Например, после ввода формулы =ЛЕВСИМВ(A1;4) получим 1-05 , а не ‘1-0 , как вроде следовало бы ожидать.

Использование апострофа — не единственный способ отмены автоматического преобразования формата при вводе значений. Можно вместо апострофа ввести перед словом пробел или после слова точку. Например, если нужно ввести 2.3 (что превращается 02.мар ), то вводим 2.3. (после 3 стоит точка).

Другой пример об автоматическом преобразовании формата читайте в Части 2 .

СОВЕТ: Неправильный формат значения – это частый тип ошибки, к тому же, который трудно найти. Подсказкой может служить выравнивание значения в ячейке: если значение выровнено по правой стороне, то это число, а если по левой, то текст. Если ранее было сделано другое выравнивание в ячейке, то через Формат ячеек / Вкладка Выравнивание / По горизонтали установите параметр « По Значению ».

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

Метод 3: использование горячих клавиш

Комбинации клавиш позволяют пользователям быстрее выполнять популярные команды или запускать распространенные функции. Для смены формата ячеек также предусмотрено отдельное сочетание.

1. Как и в методах выше, для начала выделяем ячейку или диапазон.
2. Затем нажимаем клавиши Ctrl+1.
3. Откроется уже хорошо знакомое окно, где можно выбрать тип данных и настроить их отображение.

Примечание: Чаще всего, комбинации Ctrl+1 достаточно. Для тех, кто еще больше хочет ускорить работу, предусмотрены следующие горячие клавиши, позволяющие выбрать формат (основной вид) без открытия соответствующего окна:

• Ctrl+Shift+1 – числовой (с разделителем и двумя цифрами после запятой);
• Ctrl+Shift+4 – дата (ДД.ММ.ГГГГ);
• Ctrl+Shift+5 – процентный (без десятичных знаков);
• Ctrl+Shift+6 – время (Часы:Минуты).

Использование поиска и замены для форматирования дат

Этот метод лучше всего использовать, если вам нужно изменить формат с тире (-), косой черты (/) или периодов (.) Для разделения месяца, дня и года. Это особенно удобно, если вам нужно изменить большое количество дат.

1. Выберите ячейки, для которых нужно изменить формат даты.

2. Выберите « Домой» > « Найти и выбрать» > « Заменить» .

3. В поле «Найти» введите свой оригинальный разделитель даты (тире, косая черта или точка).

4. В поле «Заменить на» введите то, что вы хотите изменить в качестве разделителя формата (тире, косая черта или точка).

5. Затем выберите один из следующих вариантов:

• Заменить все : который заменит все первые записи поля и заменит их на ваш выбор из поля Заменить на .
• Заменить : Заменяет только первый экземпляр.
• Найти все : только поиск всех исходных записей в поле « Найти» .
• Find Next : только находит следующий экземпляр из вашей записи в поле Find what .

Почему в Excel пишет дату?

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

Почему в Экселе вместо числа пишет дату?

Чтобы в excel ваше число не превратилось в дату – например вместо 1.01 не появилось 1 янв, вам нужно просто ввести число правильно

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

Как отключить замену на дату в Excel?

Автозамена числа на дату в Excel 2003

1. Нажмите на круглую кнопку меню Excel 2007 (в левом верхнем углу программы).
2. Нажмите на «Параметры Excel». …
3. Выберите пункт меню Правописание и, справа, нажмите на кнопку Параметры автозамены:
4. В появившемся окне снимите отметку с чекбокса «Заменять при вводе» и нажмите на ОК:

Почему в Екселе пишет на формуле знач?

Ошибка #ЗНАЧ! в Excel означает: «Формула вводится неправильно. Или что-то не так с ячейками, на которые указывают ссылки».

Как убрать решетку в Excel?

Щелчком правой кнопки мыши по ней открываем меню, в котором кликаем по пункту “Формат ячеек”. В появившемся окне “Формат ячеек” переключаемся во вкладку “Выравнивание”. В блоке параметров “Отображение” ставим галочку напротив опции “автоподбор ширины”, после чего жмем OK.

Почему в Excel меняет цифры?

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

Как отключить Автозамену в Excel 2010?

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

Как отключить замену чисел на дату?

Выберите ячейки, в которые необходимо ввести числа. Щелкните Главная > Числовой формат > Текст.…Отмена автоматической замены чисел датами

1. Пробел перед вводом числа. …
2. Апостроф (‘) перед вводом числа, например ’11-53 или ‘1/47.

Как отключить Автоформатирование в Excel?

Одновременное настройка всех параметров автоматического форматирования

1. Выберите Файл > Параметры.
2. В окне «Параметры Excel»щелкните «Параметры >».
3. На вкладке «Автоформат при типе» отведите флажки для форматирования, которые вы хотите использовать.

Как преобразовать число в дату в Excel?

В пустой ячейке сделайте следующее.

1. Введите = ДАТАЗНАЧ (
2. Щелкните ячейку, содержащую дату в текстовом формате, которую следует преобразовать.
3. Введите )
4. Нажмите клавишу ВВОД, а функция ДАТАЗНАЧ возвращает порядковый номер даты, которая была представлена в текстовом формате. Что такое числовом Excel?

Как убрать источник ошибки Excel?

в меню выберите Главная/ Стили/ Условное форматирование/ Создать правило… ); в появившемся окне выберите Форматировать только ячейки, которые содержат ; в выпадающем списке выберите Ошибки (см.

Почему Впр выдает знач?

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

Как исправить ошибку в Excel?

Редактирование формул в Excel

1. Выделите ячейку, формулу в которой необходимо изменить. …
2. Щелкните по Строке формул, чтобы приступить к редактированию формулы. …
3. Все ячейки, на которые ссылается формула, будут выделены разноцветными границами. …
4. По окончании нажмите Enter на клавиатуре или воспользуйтесь командой Ввод в Cтроке формул.

Как поменять цифры на буквы в Excel?

Если вы пользуетесь Excel 2003, то для того чтобы поменять цифры на буквы вам нужно открыть меню «Сервис – Параметры» и в открывшемся окне перейти на вкладку «Общие». Здесь будет доступна функция «Стиль ссылок R1C1», отключите ее, и вы вернетесь к обычному стилю.

Как сделать чтобы в Excel не менялись числа?

Для этого необходимо задать формат ячейки – что в ней будет находиться число, а не цифра. Для этого, щелкаем правой кнопкой мыши по ячейке и выбираем вкладку “Формат ячеек”. В ней выбираем вариант “Текстовый”. Все, после этого в данной ячейке Excel даже набрав цифры с точкой у вас не появится число и месяц.

Третий способ – заливка, зависящая от наполнения ячеек (условная).

Заливка по условиям наполнения ячейки выполняется при помощи опции «Условное форматирование». Кнопка условного форматирования находится на вкладке «Главная».

Рассмотрим условное форматирование по следующим критериям:

Ячейки, имеющие значение меньше «5» залить зеленым цветом, выше «5» – красным.

Чтобы настроить условное форматирование ячеек следует выполнить следующие действия:

1. Выделить нужные ячейки левой кнопкой мыши;
2. Выбрать и нажать кнопку «Условное форматирование» во вкладке «Главная» панели быстрого доступа;
3. Выбрать правило форматирования — указать условие и цвет заливки. (для нашего примера следует создать два правила: 1) меньше пяти – зеленая заливка; 2) больше пяти — красная)
4. Нажать «Enter».

Источник

Метод 1: через контекстное меню

Данный метод является самым распространенным среди пользователей. Вот что мы делаем:

1. Щелкаем правой кнопкой мыши по ячейке, формат которой требуется задать/изменить, и в появившемся списке команд выбираем “Формат ячеек”.Если данное действие требуется выполнить для диапазона ячеек, сначала любым удобным способом выделяем его, после чего, также, правой кнопкой мыши кликаем по любому месту внутри выделенной области и выбираем нужный пункт.
2. На экране отобразится окно “Формат ячеек”. Автоматически должна быть выбрана вкладка “Число” (если по каким-то причинам этого не произошло, переключаемся в нее). В перечне слева определяемся с форматом, после чего справа задаем детальные параметры (если предлагаются). По готовности щелкаем OK.
3. В результате нам удалось сменить формат ячеек (в нашем случае – числовой, с разделителем разрядов, две цифры после запятой).

Причины, почему не меняется форматирование в Excel

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

Одна из причин, почему не меняется формат ячейки в Excel — появление конфликта в этом секторе, из-за чего стиль блокируется. В большинстве случаев проблема актуальна для документов в Эксель 2007 и более. Зачастую это обусловлено тем, что в новых форматах документов данные о форматировании ячеек находятся в схеме XML, а иногда при изменении происходит конфликт стилей. Excel, в свою очередь, не может установить и, как следствие, он не меняется.

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

Использование текста в столбцах для преобразования в формат даты

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

1. Выберите ячейки, для которых вы хотите изменить формат даты.

2. Убедитесь, что они отформатированы как текст. (Нажмите Ctrl + 1, чтобы проверить их формат).

3. Выберите вкладку « Данные

4. В группе «Инструменты данных» выберите « Текст в столбцы» .

5. Выберите «С разделителями» или « Фиксированная ширина» , затем нажмите « Далее» .

   Большую часть времени следует выбирать с разделителями, так как длина даты может колебаться.

6. Снимите флажки со всех разделителей и выберите Далее .

7. В области « Формат данных столбца» выберите « Дата» , выберите формат строки даты в раскрывающемся меню, затем нажмите « Готово» .