Remontnouta.ru

ПК Ремонт техники
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Что делать, если вместо цифр отображаются решетки в Эксель

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

Решетки вместо цифр в таблице Эксель

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

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

Решение проблемы отображения числа как даты

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

Способ 1: контекстное меню

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

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

как сделать чтобы в excel число на дату не менялось

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

Способ 2: изменение форматирования на ленте

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

  1. Выделяем ячейку или диапазон с форматом даты.
  2. Находясь во вкладке «Главная» в блоке инструментов «Число» открываем специальное поле форматирования. В нём представлены самые популярные форматы. Выбираем тот, который наиболее подходит для конкретных данных.
  3. Если среди представленного перечня нужный вариант не был найден, то жмите на пункт «Другие числовые форматы…» в этом же списке.
  4. Открывается точно такое же окно настроек форматирования, как и в предыдущем способе. В нём расположен более широкий перечень возможного изменения данных в ячейке. Соответственно, и дальнейшие действия тоже будут точно такими же, как и при первом варианте решения проблемы. Выбираем нужный пункт и жмем на кнопку «OK».

как сделать чтобы в excel число на дату не менялось

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

Читайте так же:
Можно ли ставить брагу в пластиковой таре

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

Мы рады, что смогли помочь Вам в решении проблемы.

Задайте свой вопрос в комментариях, подробно расписав суть проблемы. Наши специалисты постараются ответить максимально быстро.

Помогла ли вам эта статья?

23 Ноя 2018 07:16:10

29 Янв 2017 17:28:40

28 Июн 2018 15:25:11

13 Ноя 2018 16:52:22

22 Ноя 2018 17:18:37

21 Ноя 2018 09:22:29

22 Ноя 2018 18:55:46

16 Ноя 2018 10:24:33

Сейчас на форуме (гостей: 677, пользователей: 8, из них скрытых: 1) , , , , , ,

Сегодня отмечают день рождения (58), (58), (34), (35), (43), (28), (28), (28), (28)

Всего зарегистрированных пользователей: 84265

Приняло участие в обсуждении: 32317

Всего тем: 107274

2 метода:Изменение стандартного формата датыИзменение формата даты для области данных

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

Метод 1 Изменение стандартного формата даты

как сделать чтобы в excel число на дату не менялось

  1. Перейдите к настройкам даты и времени.

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

  • При использовании Windows Vista или Windows 8 следует открыть «Панель управления». Далее выберите «Часы, язык и регион». В Windows 8 в это меню также можно войти другим способом: вызвать боковую панель, выбрать «Настройки», затем «Изменение параметров компьютера» и далее «Время и язык».
  • При использовании Windows XP: откройте «Панель управления» и выберите «Языки и региональные стандарты»

Здесь дальнейшие шаги зависят от версии операционной системы.

  • Если вы используете Windows 8: в папке «Часы, язык и регион» выберите «Изменение форматов даты, времени и чисел», который находится под заголовком «Региональные стандарты».
  • Если вы используете Windows Vista: откройте диалоговое окно «Язык и региональные стандарты». Затем выберите вкладку «Форматы».
  • Если вы используете Windows XP: откройте «Язык и региональные стандарты». Затем выберите вкладку «Региональные параметры».

Если вы используете Windows 8: убедитесь, что открыта вкладка «Форматы». Если вы используете Windows Vista: нажмите по «Изменить этот формат…». Если вы используете Windows XP: нажмите «Настройка».

Вы откроете меню настройки для короткой и длинной даты. Короткий формат даты, к примеру, выглядит так: 6/12/2015. Длинный формат даты подразумевает использование словесных обозначений, и выглядит это так: 31 декабря 1999 г. Выбранный вами формат будет применен ко всей системе Windows и ко всем ее приложениям, в том числе и к Excel. Нажмите «ОK» для внесения изменений.

  • Ниже приведен список вариантов формата короткой даты. Так бы в коротком виде выглядит дата 2 июня 2015 г.:
    • M/d/yyyy: 6/2/2015
    • M/d/yy: 6/2/15
    • MM/dd/yy: 06/02/15
    • MM/dd/yyyy: 06/02/2015
    • yy/MM/dd: 15/06/02
    • yyyy-MM-dd: 2015-06-02
    • dd-MMM-yy: 02-Июн-15
    • dddd, MMMM dd, yyyy: Пятница, Июнь 02, 2015
    • dddd, MMMM d, yyyy: Пятница, Июнь 2, 2015
    • MMMM d, yyyy: Июнь 2, 2015
    • dddd, d MMMM, yyyy: Пятница, 2 Июня, 2015
    • d MMMM, yyyy: 2 Июня, 2015

    Метод 2 Изменение формата даты для области данных

    как сделать чтобы в excel число на дату не менялось

    1. Откройте электронные таблицы и выделите необходимые ячейки.

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

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

    Перейдите на вкладку «Главная». Меню «Формат» расположен в секции «Ячейки» и находится между «Стили» и «Редактирование».

    • Также вы можете просто нажать правой кнопкой по выделенной области ячеек, выделенному столбцу или строке, и выбрать «Формат ячеек». Откроется окно, в котором можно будет изменить формат даты для выделенных ячеек.

    Выберите пункт «Формат ячеек». Он расположен в самой нижней части меню.

    Перейдите на вкладку «Число». Она находится слева от вкладок «Выравнивание», «Шрифт», «Граница», «Заливка», и «Защита». Эта вкладка открывается по умолчанию.

    Выберите «Дата» в меню «Числовые форматы». Это откроет настройки формата даты.

    Выберите желаемый формат даты. Выделите этот формат и нажмите «ОК» для внесения изменений. Далее сохраните файл, чтобы сохранить измененный формат даты

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

    При импорте в 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) :

    Настройка разделителей в окне параметров Excel

    Снимаем флажок Использовать системные разделители (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) . Все испорченные числа будут немедленно исправлены.

    Как поставить неизменную отметку времени автоматически (формулами)

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

    Если же потом, основываясь на этих данных вы собираетесь что-то еще делать и использовать получившийся результат в формулах, то лучше тогда использовать сразу две функции: =ТДАТА()-СЕГОДНЯ()

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

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

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

    Какие действия можно предпринять, чтобы достичь этой цели? Например, можно попробовать воспользоваться функцией ЕСЛИ, в которой также будет содержаться эта же функция, но с данными, зависящими от значения другой ячейки. Гораздо проще это продемонстрировать на примере. Формула будет выглядеть так: =ЕСЛИ(B2=»Да»; ЕСЛИ(C2=»»;ТДАТА(); C2); «»)

    Расшифруем эту формулу.

    • B – это колонка, в которой нам надо записывать подтверждение доставки.
    • C2 – это та ячейка, в которой будет выводиться отметка времени после того, как мы запишем в ячейку B2 слово «Да».

    Описанная выше формула работает следующим образом. Она проверяет, есть ли слово «Да» в ячейке B2. Если да, то выполняется вторая проверка, которая проверяет, является ли ячейка C2 пустой. Если да, то тогда возвращается текущая дата и время. Если же ни одна из описанных выше функций ЕСЛИ содержат иные параметры, то тогда ничего не меняется.

    Если же нужно, чтобы критерий был «если содержится хотя бы какое-то значение», то тогда нужно использовать в условии оператор «не равно» <>. В таком случае формула будет выглядеть так: =ЕСЛИ(B2<>»»; ЕСЛИ(C2=»»;ТДАТА(); C2); «»)

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

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

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

    В Excel каждой дате соответствует определенное число дней, прошедших с принятой точки отсчета – 1 января 1900 года. Функция ДАТАЗНАЧ возвращает число, соответствующее числовому представлению даты, которая указана в виде текста, с учетом указанной выше особенности хранения дат в Excel. Формат возвращаемого значения зависит от настроек формата ячейки, в которой будет выведен результат вычислений.

    Зачастую даты в Excel записывают без использования функции ДАТА. Табличный редактор определяет такие значения как обычные текстовые строки. Поэтому процедуры форматирования, сортировки по дате, а также различные вычисления (например, разница дат) приводят к некорректным результатам или появлению ошибок. Поэтому функция ДАТАЗНАЧ полезна для преобразования текстовых значений к данным формата Дата.

    Пример 1. В таблице Excel находится столбец, в котором хранятся даты как текстовые строки, при этом записи имеют вид: «28 сентября 2018 года». Преобразовать эти значения в данные формата Дата.

    Вид таблицы данных:

    excel отключить преобразование в дату

    Для получения даты в формате, поддерживаемом Excel, используем следующую функцию:

    Единственный аргумент состоит из подстрок, склеенных амперсандами (&):

    1. Функция ЛЕВСИМВ возвращает номер дня (первые два символа строки, содержащейся в ячейке A2). Очень важно, чтобы однозначные номера дней (например, 8 апреля) записывались как 08 апреля (имели нуль в начале), иначе будет возникать ошибка.
    2. Комбинация функций ПСТР и ЛЕВСИМВ выделяет из строки три первых символа названия месяца и возвращает их.
    3. Комбинация функций ПСТР и ПРАВСИМВ выделяет 4 символа, соответствующие числовому представлению года.

    Растянем формулу вниз по столбцу, чтобы рассчитать остальные значения:

    excel отключить преобразование в дату

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

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

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

    1. Положительные числа
    2. Отрицательные числа
    3. Нулевые значения
    4. Текстовый формат

    Для этого мы можем в поле Тип указать следующую конструкцию:

    Формат положительных значений ; отрицательных ; нулевых ; текстовых

    Соответственно для каждой категории можно применять формат уже описанного нами вида:

    [цвет]"любой текст"КодФормата"любой текст"

    В итоге конечно может получится длинная строка с форматом, но если приглядеться подробнее, то сложностей никаких нет.

    Смотрите какой эффект это дает. В зависимости от значения, меняется форматирование, а если вместо числа указано текстовое значения, то Excel выдает "нет данных".

    Пользовательский формат. Пример

    Вам также могут быть интересны следующие статьи

    Можно ли сздать формулу дата не используя в ней информацию из других ячеек (=ДАТА(A2;B2;C2)), а используя в ней числа которые не ссылаються на другие ячейки (=ДАТА(2014;08;26))?

    или обязательно нужны данные из других ячеек?

    Не обязательно использовать ссылки на другие ячейки, абсолютные значения тоже подойдут

    А как преобразовать в формат гггг-мм-дд уже внесенные даты рождения (20 тыс шт) в формате дд.мм.гггг ? На установку формата ячеек, в т.ч. и с установкой фолрмата системного времени через часы) не реагирует. Информация нужна срочно.
    28/05/2015

    голоса
    Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector