Автоматическое преобразование формата ячейки в EXCEL при вводе ТЕКСТовых данных (Часть 1)
При вводе пользователем данных, EXCEL пытается определить тип вводимых данных. Если данные можно перевести в формат даты или числа, то EXCEL производит соответствующее преобразование и форматирование. Преобразование производится «на лету» после нажатия клавиши ENTER . Часто текстовые данные действительно имеют формат дат (1-05, 2.12) или чисел (000123456), но на самом деле ими не являются. В этом случае необходимо запретить EXCEL выполнять автоматическое преобразование и форматирование.
При вводе текстовых данных определенного вида, например: 1-05, 2.12, 1 e 2, 00012456 , EXCEL автоматически меняет формат ячейки и преобразовывает текст в другой, как ему кажется, подходящий тип данных. Это приводит к тому, что 1-05 и 2.12 будут преобразованы в даты ( 01.
Допустим, мы вводим текст 1-05 (пусть это будет артикул товара) и хотим, чтобы EXCEL сохранил в ячейке именно текст, а не что-либо другое.
Что же происходит при вводе значения в ячейку? Если ячейка имеет формат Общий (формат ячейки по умолчанию), EXCEL преобразует текст 1-05 в дату 01.май : формат ячейки изменяется с Общий на формат Дата (точнее все форматы /ДД.МММ). Если мы вернем ячейке формат Общий , то в ячейке увидим число 40299 (если сейчас 2010 год), что соответствует 01.05.2010 . Вернуть исходное значение 1-05 простым преобразованием формата уже не представляется возможным — EXCEL интерпретировал 1-05 , как дату 01.
05.2010 и вставил в ячейку соответствующее ей число 40299 .
Универсальным способом отмены автоматического преобразования формата является явное указание желаемого формата перед вводом данных. Т.е. выделяем ячейки, в которые необходимо ввести заведомо текстовые данные и устанавливаем им текстовый формат. Для вызова формата ячейки нажмите CTRL+1 .
Есть и другой способ. Чтобы EXCEL автоматически при вводе текстовых данных назначал ячейкам текстовый формат, перед вводимым текстом можно ввести знак апострофа (‘), т.е. в нашем случае необходимо ввести 
Использование апострофа — не единственный способ отмены автоматического преобразования формата при вводе значений. Можно вместо апострофа ввести перед словом пробел или после слова точку. Например, если нужно ввести 2.3 (что превращается 02.мар ), то вводим 2.3. (после 3 стоит точка).
Другой пример об автоматическом преобразовании формата читайте в Части 2 .
СОВЕТ: Неправильный формат значения – это частый тип ошибки, к тому же, который трудно найти. Подсказкой может служить выравнивание значения в ячейке: если значение выровнено по правой стороне, то это число, а если по левой, то текст. Если ранее было сделано другое выравнивание в ячейке, то через Формат ячеек / Вкладка Выравнивание / По горизонтали установите параметр « По Значению ».
Продолжение статьи
читайте в Части 2
, где будем с помощью формул бороться с автоматическим преобразованием формата ячейки.
Формат ячеек в Excel? Виды форматов. Разрядность
В этой статье вы узнаете, как изменить формат ячеек в Excel, какие бываю форматы и как изменить количество знаков после запятой?
Несмотря на то, что кнопки групп Шрифт, Выравнивание и Число вкладки Главная открывают быстрый доступ к большинству часто используемых команд форматирования, они не отражают весь набор команд форматирования, доступных в Excel.
Чтобы получить доступ ко всем командам форматирования, откройте диалоговое окно Формат ячеек одним из следующих способов.
- Щелкните на пункте Другие числовые форматы в нижней части раскрывающегося меню кнопки Числовой формат.
- Щелкните на маркере открытия диалогового окна в правом нижнем углу группы Число.
- Нажмите комбинацию клавиш <Ctrl+l>.
Диалоговое окно Формат ячеек, которое открывается после выбора этих команд, содержит шесть вкладок: Число, Выравнивание, Шрифт, Граница, Заливка и Защита.
Учтите, что в комбинации клавиш <Ctrl+l> используется цифра 1, а не функциональная клавиша <F1>.
Формат ячеек в Excel: Числовыми форматы
Способ ввода значений в ячейки определяет тип получаемого числового формата. Приведем несколько примеров.
- Если вы вводите значение, дополненное знаком доллара (или другой валюты) и содержащее два десятичных знака, то Excel назначит ему финансовый числовой формат.
- Если вы вводите целочисленное значение, дополненное символом процента, то Excel назначит ему процентный формат.
- Если вводится значение в одном из стандартных форматов дат Excel, например 06/11/19 или 6-ноя-19, то ему будет назначен формат даты. Данные в формате дат хранятся в виде числовых значений.
Значения можно форматировать как в процессе ввода в ячейку (как в случае с вводом дат), так и после завершения ввода. Обычно применяют “позднее” форматирование, поскольку оно предполагает выполнение всего лишь двух действий.
- Выделите все ячейки, содержащие значения, которые нужно отформатировать.
- Выберите для группы числовой формат с помощью либо раскрывающегося меню соответствующей кнопки вкладки Главная, либо вкладки Число диалогового окна Формат ячеек.
Даже если вы предпочитаете форматировать значения заранее, вам все равно придется повторять эту операцию по отношению к результатам формул. Дело в том, что Excel применяет формат Общий ко всем вычисляемым значениям, равно как и к вводимым в ячейку значениям, которые не соответствуют ни одному стандартному числовому формату. Одна из самых больших проблем, связанных с применением формата Общий, заключается в отбрасывании ведущих и завершающих нулей. В результате усложняется выравнивание чисел в ячейках по десятичным запятым.
Эта проблема проиллюстрирована на скриншоте ниже, на котором представлен калькулятор стоимости ремонта. Как видите, столбец со стоимостью напоминает зигзаг, так как он не выровнен по десятичным запятым.
Чтобы исправить ситуацию, к этим значениям придется применить другой, более подходящий числовой формат.
Формат ячеек в Excel: Финансовый числовой формат
Поскольку Excel обычно применяется для выполнения финансовых расчетов, вам чаще придется иметь дело с финансовым числовым форматом. Чтобы применить этот формат к выделенным ячейкам, щелкните на кнопке Финансовый числовой формат, находящейся на вкладке Главная.
На скриншоте выделены ячейки с итогами (диапазон ЕЗ:Е17). Данные ячейки были отформатированы с помощью кнопки Финансовый числовой формат, находящейся в группе Число вкладки Главная. Отличительный признак этого формата — знак валюты (рубля в данном случае).
Финансовый числовой формат подразумевает наличие двух десятичных знаков и символа валюты, добавленного ко всем числам выделенного диапазона. Финансовый числовой формат можно заменить форматом Денежный, выбрав его в меню Числовой формат или в диалоговом окне Формат ячеек.
Если вы попытаетесь отформатировать все числа в таблице с использованием финансового числового формата, то даже небольшая таблица будет перенасыщена символами валюты. Поэтому в приведенном примере данный формат был применен только к итоговым значениям.
Формат ячеек в Excel: Числовой формат с разделителями
Формат с разделителями является отличной альтернативой денежному формату. Подобно последнему, он предусматривает наличие в больших числах разделителей, позволяющих визуально отделять тысячи, миллионы и т.д.
В формате с разделителями отображаются два десятичных разряда, а отрицательные значения заключаются в круглые скобки. Поэтому данный формат хорошо подходит для форматирования таблиц, в которых представлены доллары или центы (либо другие денежные единицы) или же большие числовые значения, которые никак не связаны с валютой.
Формат с разделителями удобен для создания отчета о продажах за первый квартал. Обратите внимание на скриншоте ниже, на котором представлены стоимость работ за 1 м2, отформатированные с помощью данного формата.
Для создания примера сначала был выделен диапазон B3:D16, а затем выполнен щелчок на кнопке Формат с разделителями, обозначенной тремя нулями. Эта кнопка находится в группе Число вкладки Главная.
Формат ячеек в Excel: Процентный формат
На многих рабочих листах используются процентные значения для отображения годовых ставок, процента инфляции, прибыльности и т.п. Чтобы вставить процентное значение в ячейку, введите числовое значение, а затем укажите символ процента. Например, чтобы ввести годовую ставку в 12 процентов, следует ввести в ячейку значение 12%. В ответ на это Excel автоматически назначит ячейке процентный формат и в то же время разделит введенное значение на 100. В данном случае в ячейке мы увидим 0,12.
Не все проценты вводятся в рабочий лист вручную таким способом. Некоторые могут вычисляться по формуле в виде обычных десятичных значений. К подобным ячейкам нужно применить процентный формат (Percent), чтобы преобразовать десятичные значения в проценты (это выполняется путем умножения результата на 100 и добавления символа процента).
На скриншоте ниже к значениям в столбце F применен процентный формат. Для этого следует выделить соответствующие ячейки, а затем щелкнуть на кнопке Процентный стиль, находящейся в группе Число вкладки Главная.
Как изменить количество знаков после запятой в Excel?
Для того чтобы увеличить или уменьшить количество отображаемых десятичных знаков во всех числовых стилях (финансовом, с разделителем и процентном), щелкните на соответствующих кнопках группы Число вкладки Главная. После каждого щелчка на кнопке Увеличить разрядность (на ней стрелка указывает влево) программа добавляет к числовому формату дополнительный десятичный разряд. Если же щелкнуть на кнопке Уменьшить разрядность, то десятичный разряд будет удален.
За форматами скрываются обычные числа
Все многообразие числовых форматов — это всего лишь отражение обычных чисел, хранящихся на рабочем листе.
Подобно хорошему иллюзионисту, числовой формат просто изменяет внешний вид чисел, не затрагивая их значения. Рассмотрим пример формулы, которая возвращает значение 25, 6456 в определенной ячейке.
Теперь предположим, что для данной ячейки изменяется формат после щелчка на кнопке Финансовый числовой формат вкладки Главная. Исходное значение примет вид 25,65р.
Подобное изменение может навести на мысль о том, что программа округлила результат до двух десятичных знаков. На самом деле программа округлила только представление вычисленного значения — в ячейке остается все то же значение 25,6456. Если вы будете использовать данную ячейку в другой формуле, то имейте в виду, что Excel подставит в нее истинное значение, а не отображаемое в ячейке.
У многих пользователей возникает вопрос о том, что же делать, чтобы отображаемые на рабочем листе значения соответствовали числам, введенным в ячейки? Добиться этого можно, установив единственный флажок, который приведет к преобразованию истинных значений в ячейках в числа, отображаемые на листе.
Но при этом следует учитывать, что отмена установки этого флажка не приведет к возврату прежних значений.
Итак, чтобы преобразовать исходные числовые значения в отображаемые на рабочем листе, выполните следующие действия.
- Убедитесь, что на рабочем листе использован числовой формат с требуемым количеством десятичных знаков.
Это действие следует предпринять прежде, чем приступить к преобразованию истинных значений в их отображаемую форму.
- Выберите команду Файл→Параметры→Дополнительно, чтобы перейти на вкладку Дополнительно диалогового окна Параметры Excel.
- В группе При пересчете этой книги установите флажок Задать указанную точность и щелкните на кнопке ОК.
Откроется окно с предупреждением о том, что данные потеряют свою точность.
- Щелкните на кнопке ОК или нажмите клавишу <Enter>.
Все данные изменятся в соответствии с экранным представлением.
Все данные изменятся в соответствии с экранным представлением.Рекомендуется предварительно сохранить рабочую книгу с вычисленными значениями. После завершения преобразования всех значений листа путем установки флажка Задать указанную точность откройте диалоговое окно Сохранить как. Измените имя файла, отображаемое в поле Имя файла перед щелчком на кнопке Сохранить или нажатием клавиши <Enter>. Для изменения имени файла можно к текущему имени файла добавить слова как на экране. В вашем распоряжении окажутся две копии файла: исходный файл книги, включающий введенные вами значения и значения, подсчитанные Excel, а также новый файл книги, содержащий отображаемые значения (с добавленными к названию словами как на экране).
Формат ячеек в Excel: Формат даты
В Excel можно создать формулу, вычисляющую разницу между значениями даты и времени, введенными в ячейки рабочего листа.
Единственная проблема состоит в том, что Excel вычитает одну дату из другой или одно время из другого и автоматически преобразует результат вычислений в соответствующий числовой формат даты или времени. Например, если ввести в ячейку В4 15-8-19, в ячейку С4 — 15/4/19, а в ячейку D4 — формулу, вычисляющую количество дней между этими датами (=В4-С4), то получится результат 122 (количество дней). Это значение отображается с помощью формата Общий, назначенного ячейке D4. Учтите, что при создании формул, подсчитывающих разницу между двумя значениями времени на листе, ячейке результата следует назначить формат Общий. Предположим, что в ячейке В5 указано значение 8:00 AM, а в ячейке С5 — 4:00 РМ. Для вычисления разницы двух показаний времени в ячейке D5 определяется следующая формула: =С5-В5.
Результат вычисления формулы, который отображается в ячейке D5 в виде 8:00 AM, нужно преобразовать в формат Общий. В результате вместо значения 8:00 AM в ячейке D5 появится дробное значение 0,333333, которое представляет собой результат деления 8 (значение времени) на 24 (количество часов в сутках).
Чтобы преобразовать это дробное число в эквивалентное количество часов, следует умножить его на 24, а к результату применить формат Общий.
Continue Reading
Преобразование чисел-как-текст в нормальные числа
Если для каких-либо ячеек на листе был установлен текстовый формат (это мог сделать пользователь или программа при выгрузке данных в 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. В появившемся окне редактора добавьте новый модуль через меню
Sub Convert_Text_to_Numbers()
Selection.NumberFormat = "General"
Selection.Value = Selection.Value
End Sub
Теперь после выделения диапазона всегда можно открыть вкладку Разрабочик — Макросы (Developer — Macros), выбрать наш макрос в списке, нажать кнопку Выполнить (Run) — и моментально преобразовать псевдочисла в полноценные.
Также можно добавить этот макрос в личную книгу макросов, чтобы использовать позднее в любом файле.
P.S.
С датами бывает та же история. Некоторые даты тоже могут распознаваться Excel’ем как текст, поэтому не будет работать группировка и сортировка. Решения — те же самые, что и для чисел, только формат вместо числового нужно заменить на дату-время.
Ссылки по теме
Как в Excel создать пользовательский формат чисел — Трюки и приемы в Microsoft Excel
Несмотря на то что Excel предлагает большое разнообразие встроенных числовых форматов; вы можете прийти к выводу, что ни один из них не соответствует вашим потребностям. В таком случае, вероятно, вы захотите создать собственный формат чисел. Это делается на вкладке Число диалогового окна Формат ячеек (рис. 49.1).
Самый простой способ вызвать это окно — нажать Ctrl+1. Вы также можете нажать кнопку его запуска в группе Число вкладки Главная (маленькая кнопка со стрелкой расположена справа от слова Число).
Рис. 49.1. Пользовательские (настраиваемые) числовые форматы можно создать на вкладке Число окна Формат ячеек
Многие пользователи Excel, даже те, кого принято считать продвинутыми, впрочем и любой инженер, избегают создания собственных числовых форматов, поскольку считают этот процесс слишком сложным.
В действительности пользовательские числовые форматы, как правило, только кажутся куда более сложными, чем они есть на самом деле.
Вы создаете числовой формат, указывая серию кодов в виде строки формата числа. Для того чтобы ввести новый числовой формат, выполните следующие действия.
- Нажмите Ctrl+1 для вызова диалогового окна Формат ячеек.
- На вкладке Число выберите категорию (все форматы).
- Введите свой числовой формат в поле Тип. В табл. 49.1 и 49.2 представлены примеры кодов, которые вы можете использовать для создания собственных пользовательских форматов чисел.
- Нажмите кнопку ОК, чтобы закрыть диалоговое окно Формат ячеек.
Части строки формата числа
Пользовательская строка формата позволяет вам задавать различные коды формата для четырех категорий значений: положительных чисел, отрицательных чисел, нулевых значений и текста.
Это можно сделать путем разделения кодов для каждой категории точкой с запятой:Положительный формат: отрицательный формат; нулевой формат: строковый формат
Следующие рекомендации помогут вам определить, какие из этих четырех разделов необходимо указывать.
- Если ваша строка формата использует только один раздел, то строка с форматом применяется ко всем значениям.
- При использовании двух разделов первый применяется к положительным значениям и нулям, а второй относится к отрицательным значениям.
- При использовании трех разделов первый относится к положительным значениям, второй — к отрицательным, а третий раздел относится к нулям.
- Если вы указываете все четыре раздела, последний относится к тексту, хранящемуся в ячейке.
Следующий пример пользовательского числового формата задает различные форматы для каждого из этих типов:[3еленый]0сновной;[Красный]-Основной;[Черный]Основной;[Синий]Основной
В этом примере используются преимущества того факта, что у цветов есть специальные коды. Ячейка, отформатированная таким пользовательским форматом, отобразит свое содержимое в разных цветах в зависимости от значения. Когда ячейка отформатирована с применением такого числового формата, положительное число будет зеленым, отрицательное — красным, нуль будет черным, а текст — синим. Но стоит отметить, что использование условного форматирования Excel — это гораздо лучший способ назначать цвета элементам на основе их содержимого.
Когда вы создаете пользовательские числовые форматы, не забывайте поле Образец на вкладке Число диалогового окна Формат ячеек. Здесь выводится значение активной ячейки, отформатированное тем способом, что задан в поле Тип.
Обязательно проверьте свой пользовательский числовой формат следующими данными: положительным значением, отрицательным значением, нулевым значением и текстом. Часто создание пользовательских числовых форматов требует нескольких попыток. Каждый раз, когда вы редактируете строку формата, она добавляется в список. Когда вы, наконец, получите корректную строку формата, откройте диалоговое окно Формат ячеек еще раз и удалите свои предыдущие ошибочные форматы.
Как преобразовать число в текст и обратно в Excel
Одной из частых задач, с которыми сталкиваются пользователи программы Эксель, является преобразования числовых выражений в текстовый формат и обратно. Этот вопрос часто заставляет потратить на решение много времени, если юзер не знает четкого алгоритма действий. Давайте разберемся, как можно решить обе задачи различными способами.
Конвертация числа в текстовый вид
Все ячейки в Экселе имеют определенный формат, который задает программе, как ей рассматривать то или иное выражение. Например, даже если в них будут записаны цифры, но формат выставлен текстовый, то приложение будет рассматривать их, как простой текст, и не сможет проводить с такими данными математические вычисления. Для того, чтобы Excel воспринимал цифры именно как число, они должны быть вписаны в элемент листа с общим или числовым форматом.
Для начала рассмотрим различные варианты решения задачи конвертации чисел в текстовый вид.
Способ 1: форматирование через контекстное меню
Чаще всего пользователи выполняют форматирование числовых выражений в текстовые через контекстное меню.
- Выделяем те элементы листа, в которых нужно преобразовать данные в текст. Как видим, во вкладке «Главная» на панели инструментов в блоке «Число» в специальном поле отображается информация о том, что данные элементы имеют общий формат, а значит, цифры, вписанные в них, воспринимаются программой, как число.
- Кликаем правой кнопкой мыши по выделению и в открывшемся меню выбираем позицию «Формат ячеек…».
- В открывшемся окне форматирования переходим во вкладку «Число», если оно было открыто в другом месте. В блоке настроек «Числовые форматы» выбираем позицию «Текстовый». Для сохранения изменений жмем на кнопку «OK» в нижней части окна.
- Как видим, после данных манипуляций в специальном поле высвечивается информация о том, что ячейки были преобразованы в текстовый вид.
- Но если мы попытаемся подсчитать автосумму, то она отобразится в ячейке ниже. Это означает, что преобразование было совершено не полностью. В этом и заключается одна из фишек Excel. Программа не дает завершить преобразование данных наиболее интуитивно понятным способом.
- Чтобы завершить преобразование, нам нужно последовательно двойным щелчком левой кнопки мыши поместить курсор в каждый элемент диапазона в отдельности и нажать на клавишу Enter. Чтобы упростить задачу вместо двойного щелчка можно использовать нажатие функциональной клавиши F2.
- После выполнения данной процедуры со всеми ячейками области, данные в них будут восприниматься программой, как текстовые выражения, а, следовательно, и автосумма будет равна нулю. Кроме того, как видим, левый верхний угол ячеек будет окрашен в зеленый цвет. Это также является косвенным признаком того, что элементы, в которых находятся цифры, преобразованы в текстовый вариант отображения. Хотя этот признак не всегда является обязательным и в некоторых случаях такая пометка отсутствует.
Урок: Как изменить формат в Excel
Способ 2: инструменты на ленте
Преобразовать число в текстовый вид можно также воспользовавшись инструментами на ленте, в частности, использовав поле для показа формата, о котором шел разговор выше.
- Выделяем элементы, данные в которых нужно преобразовать в текстовый вид. Находясь во вкладке «Главная» кликаем по пиктограмме в виде треугольника справа от поля, в котором отображается формат. Оно расположено в блоке инструментов «Число».
- В открывшемся перечне вариантов форматирования выбираем пункт «Текстовый».
- Далее, как и в предыдущем способе, последовательно устанавливаем курсор в каждый элемент диапазона двойным щелчком левой кнопки мыши или нажатием клавиши F2, а затем щелкаем по клавише Enter.
Данные преобразовываются в текстовый вариант.
Способ 3: использование функции
Ещё одним вариантом преобразования числовых данных в тестовые в Экселе является применение специальной функции, которая так и называется – ТЕКСТ. Данный способ подойдёт, в первую очередь, если вы хотите перенести числа как текст в отдельный столбец. Кроме того, он позволит сэкономить время на преобразовании, если объем данных слишком большой. Ведь, согласитесь, что перещелкивать каждую ячейку в диапазоне, насчитывающем сотни или тысячи строк – это не самый лучший выход.
- Устанавливаем курсор в первый элемент диапазона, в котором будет выводиться результат преобразования. Щелкаем по значку «Вставить функцию», который размещен около строки формул.
- Запускается окно Мастера функций. В категории «Текстовые» выделяем пункт «ТЕКСТ». После этого кликаем по кнопке «OK».
- Открывается окно аргументов оператора ТЕКСТ. Данная функция имеет следующий синтаксис:
=ТЕКСТ(значение;формат)Открывшееся окно имеет два поля, которые соответствуют данным аргументам: «Значение» и «Формат».
В поле «Значение» нужно указать преобразовываемое число или ссылку на ячейку, в которой оно находится. В нашем случае это будет ссылка на первый элемент обрабатываемого числового диапазона.
В поле «Формат» нужно указать вариант отображения результата. Например, если мы введем «0», то текстовый вариант на выходе будет отображаться без десятичных знаков, даже если в исходнике они были. Если мы внесем «0,0», то результат будет отображаться с одним десятичным знаком, если «0,00», то с двумя, и т.д.
После того, как все требуемые параметры введены, щелкаем по кнопке «OK».
- Как видим, значение первого элемента заданного диапазона отобразилось в ячейке, которую мы выделили ещё в первом пункте данного руководства. Для того, чтобы перенести и другие значения, нужно скопировать формулу в смежные элементы листа. Устанавливаем курсор в нижний правый угол элемента, который содержит формулу. Курсор преобразуется в маркер заполнения, имеющий вид небольшого крестика. Зажимаем левую кнопку мыши и протаскиваем по пустым ячейкам параллельно диапазону, в котором находятся исходные данные.
- Теперь весь ряд заполнен требуемыми данными. Но и это ещё не все. По сути, все элементы нового диапазона содержат в себе формулы. Выделяем эту область и жмем на значок «Копировать», который расположен во вкладке «Главная» на ленте инструментов группе «Буфер обмена».
- Далее, если мы хотим сохранить оба диапазона (исходный и преобразованный), не снимаем выделение с области, которая содержит формулы. Кликаем по ней правой кнопкой мыши. Происходит запуск контекстного списка действий. Выбираем в нем позицию «Специальная вставка». Среди вариантов действий в открывшемся списке выбираем «Значения и форматы чисел».
Если же пользователь желает заменить данные исходного формата, то вместо указанного действия нужно выделить именно его и произвести вставку тем же способом, который указан выше.
- В любом случае, в выбранный диапазон будут вставлены данные в текстовом виде. Если же вы все-таки выбрали вставку в исходную область, то ячейки, содержащие формулы, можно очистить. Для этого выделяем их, кликаем правой кнопкой мыши и выбираем позицию «Очистить содержимое».
На этом процедуру преобразования можно считать оконченной.
Урок: Мастер функций в Excel
Конвертация текста в число
Теперь давайте разберемся, какими способами можно выполнить обратную задачу, а именно как преобразовать текст в число в Excel.
Способ 1: преобразование с помощью значка об ошибке
Проще и быстрее всего выполнить конвертацию текстового варианта с помощью специального значка, который сообщает об ошибке. Этот значок имеет вид восклицательного знака, вписанного в пиктограмму в виде ромба. Он появляется при выделении ячеек, которые имеют пометку в левом верхнем углу зеленым цветом, обсуждаемую нами ранее. Эта пометка ещё не свидетельствует о том, что данные находящиеся в ячейке обязательно ошибочные. Но цифры, расположенные в ячейке имеющей текстовый вид, вызывают подозрения у программы в том, что данные могут быть внесены некорректно. Поэтому на всякий случай она их помечает, чтобы пользователь обратил внимание. Но, к сожалению, такие пометки Эксель выдает не всегда даже тогда, когда цифры представлены в текстовом виде, поэтому ниже описанный способ подходит не для всех случаев.
- Выделяем ячейку, в которой содержится зеленый индикатор о возможной ошибке. Кликаем по появившейся пиктограмме.
- Открывается список действий. Выбираем в нем значение «Преобразовать в число».
- В выделенном элементе данные тут же будут преобразованы в числовой вид.
Если подобных текстовых значений, которые следует преобразовать, не одно, а множество, то в этом случае можно ускорить процедуру преобразования.
- Выделяем весь диапазон, в котором находятся текстовые данные. Как видим, пиктограмма появилась одна для всей области, а не для каждой ячейки в отдельности. Щелкаем по ней.
- Открывается уже знакомый нам список. Как и в прошлый раз, выбираем позицию «Преобразовать в число».
Все данные массива будут преобразованы в указанный вид.
Способ 2: конвертация при помощи окна форматирования
Как и для преобразования данных из числового вида в текст, в Экселе существует возможность обратного конвертирования через окно форматирования.
- Выделяем диапазон, содержащий цифры в текстовом варианте. Кликаем правой кнопкой мыши. В контекстном меню выбираем позицию «Формат ячеек…».
- Выполняется запуск окна форматирования. Как и в предыдущий раз, переходим во вкладку «Число». В группе «Числовые форматы» нам нужно выбрать значения, которые позволят преобразовать текст в число. К ним относится пункты «Общий» и «Числовой». Какой бы из них вы не выбрали, программа будет расценивать цифры, введенные в ячейку, как числа. Производим выбор и жмем на кнопку. Если вы выбрали значение «Числовой», то в правой части окна появится возможность отрегулировать представление числа: выставить количество десятичных знаков после запятой, установить разделителями между разрядами. После того, как настройка выполнена, жмем на кнопку «OK».
- Теперь, как и в случае преобразования числа в текст, нам нужно прощелкать все ячейки, установив в каждую из них курсор и нажав после этого клавишу Enter.
После выполнения этих действий все значения выбранного диапазона преобразуются в нужный нам вид.
Способ 3: конвертация посредством инструментов на ленте
Перевести текстовые данные в числовые можно, воспользовавшись специальным полем на ленте инструментов.
- Выделяем диапазон, который должен подвергнуться трансформации. Переходим во вкладку «Главная» на ленте. Кликаем по полю с выбором формата в группе «Число». Выбираем пункт «Числовой» или «Общий».
- Далее прощелкиваем уже не раз описанным нами способом каждую ячейку преобразуемой области с применением клавиш F2 и Enter.
Значения в диапазоне будут преобразованы из текстовых в числовые.
Способ 4: применение формулы
Также для преобразования текстовых значений в числовые можно использовать специальные формулы. Рассмотрим, как это сделать на практике.
- В пустой ячейке, расположенной параллельно первому элементу диапазона, который следует преобразовать, ставим знак «равно» (=) и двойной символ «минус» (—). Далее указываем адрес первого элемента трансформируемого диапазона. Таким образом, происходит двойное умножение на значение «-1». Как известно, умножение «минус» на «минус» дает «плюс». То есть, в целевой ячейке мы получаем то же значение, которое было изначально, но уже в числовом виде. Даная процедура называется двойным бинарным отрицанием.
- Жмем на клавишу Enter, после чего получаем готовое преобразованное значение. Для того, чтобы применить данную формулу для всех других ячеек диапазона, используем маркер заполнения, который ранее был применен нами для функции ТЕКСТ.
- Теперь мы имеем диапазон, который заполнен значениями с формулами. Выделяем его и жмем на кнопку «Копировать» во вкладке «Главная» или применяем сочетание клавиш Ctrl+C.
- Выделяем исходную область и производим щелчок по ней правой кнопкой мыши. В активировавшемся контекстном списке переходим по пунктам «Специальная вставка» и «Значения и форматы чисел».
- Все данные вставлены в нужном нам виде. Теперь можно удалить транзитный диапазон, в котором находится формула двойного бинарного отрицания. Для этого выделяем данную область, кликом правой кнопки мыши вызываем контекстное меню и выбираем в нем позицию «Очистить содержимое».
Кстати, для преобразования значений данным методом совсем не обязательно использовать исключительно двойное умножение на «-1». Можно применять любое другое арифметическое действие, которое не ведет к изменению значений (сложение или вычитание нуля, выполнение возведения в первую степень и т.д.)
Урок: Как сделать автозаполнение в Excel
Способ 5: применение специальной вставки
Следующий способ по принципу действия очень похож на предыдущий с той лишь разницей, что для его использования не нужно создавать дополнительный столбец.
- В любую пустую ячейку на листе вписываем цифру «1». Затем выделяем её и жмем на знакомый значок «Копировать» на ленте.
- Выделяем область на листе, которую следует преобразовать. Кликаем по ней правой кнопкой мыши. В открывшемся меню дважды переходим по пункту «Специальная вставка».
- В окне специальной вставки выставляем переключатель в блоке «Операция» в позицию «Умножить». Вслед за этим жмем на кнопку «OK».
- После этого действия все значения выделенной области будут преобразованы в числовые. Теперь при желании можно удалить цифру «1», которую мы использовали в целях конвертации.
Способ 6: использование инструмента «Текст столбцами»
Ещё одним вариантом, при котором можно преобразовать текст в числовой вид, является применение инструмента «Текст столбцами». Его есть смысл использовать тогда, когда вместо запятой в качестве разделителя десятичных знаков используется точка, а в качестве разделителя разрядов вместо пробела – апостроф. Этот вариант воспринимается в англоязычном Экселе, как числовой, но в русскоязычной версии этой программы все значения, которые содержат указанные выше знаки, воспринимаются как текст. Конечно, можно перебить данные вручную, но если их много, это займет значительное количество времени, тем более что существует возможность гораздо более быстрого решения проблемы.
- Выделяем фрагмент листа, содержимое которого нужно преобразовать. Переходим во вкладку «Данные». На ленте инструментов в блоке «Работа с данными» кликаем по значку «Текст по столбцам».
- Запускается Мастер текстов. В первом окне обратите внимание, чтобы переключатель формата данных стоял в позиции «С разделителями». По умолчанию он должен находиться в этой позиции, но проверить состояние будет не лишним. Затем кликаем по кнопке «Далее».
- Во втором окне также оставляем все без изменений и жмем на кнопку «Далее».
- А вот после открытия третьего окна Мастера текстов нужно нажать на кнопку «Подробнее».
- Открывается окно дополнительной настройки импорта текста. В поле «Разделитель целой и дробной части» устанавливаем точку, а в поле «Разделитель разрядов» — апостроф. Затем делаем один щелчок по кнопке «OK».
- Возвращаемся в третье окно Мастера текстов и жмем на кнопку «Готово».
- Как видим, после выполнения данных действий числа приняли привычный для русскоязычной версии формат, а это значит, что они одновременно были преобразованы из текстовых данных в числовые.
Способ 7: применение макросов
Если вам часто приходится преобразовывать большие области данных из текстового формата в числовой, то имеется смысл в этих целях записать специальный макрос, который будет использоваться при необходимости. Но для того, чтобы это выполнить, прежде всего, нужно в своей версии Экселя включить макросы и панель разработчика, если это до сих пор не сделано.
- Переходим во вкладку «Разработчик». Жмем на значок на ленте «Visual Basic», который размещен в группе «Код».
- Запускается стандартный редактор макросов. Вбиваем или копируем в него следующее выражение:
Sub Текст_в_число()
Selection.NumberFormat = "General"
Selection.Value = Selection.Value
End SubПосле этого закрываем редактор, выполнив нажатие стандартной кнопки закрытия в верхнем правом углу окна.
- Выделяем фрагмент на листе, который нужно преобразовать. Жмем на значок «Макросы», который расположен на вкладке «Разработчик» в группе «Код».
- Открывается окно записанных в вашей версии программы макросов. Находим макрос с наименованием «Текст_в_число», выделяем его и жмем на кнопку «Выполнить».
- Как видим, тут же происходит преобразование текстового выражения в числовой формат.
Урок: Как создать макрос в Экселе
Как видим, существует довольно много вариантов преобразования в Excel цифр, которые записаны в числовом варианте, в текстовый формат и в обратном направлении. Выбор определенного способа зависит от многих факторов. Прежде всего, это поставленная задача. Ведь, например, быстро преобразовать текстовое выражение с иностранными разделителями в числовое можно только использовав инструмент «Текст столбцами». Второй фактор, который влияет на выбор варианта – это объемы и частота выполняемых преобразований. Например, если вы часто используете подобные преобразования, имеет смысл произвести запись макроса. И третий фактор – индивидуальное удобство пользователя.
Мы рады, что смогли помочь Вам в решении проблемы.Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.
Помогла ли вам эта статья?
ДА НЕТНастраиваем формат ячеек Эксель
Вы уже разобрались с типами данных, внесли нужную информацию на рабочий лист, и даже воспользовались расширенными возможностями ввода, что очень облегчило работу. Осталось задать формат числовым данным и можно проводить какие-то расчёты. Давайте же скорее отформатируем ячейки в соответствии с их назначением.
Вы уже знаете, что форматирование никак не изменяет данных, а лишь корректирует их отображение на листе. Давайте посмотрим, как выглядят данные, отформатированные в различных форматах:
Данные в различных форматахЧтобы отформатировать значения, нужно выбрать ячейку или группу ячеек, для которых проводится форматирование. Практически всегда мы видим в адресной строке исходное значение, а в ячейке на листе – отформатированное. Исключения составляют «Процентный» формат, «Дата» и «Время».
Автоформатирование чисел в Эксель
Иногда форматирование делать и не нужно вовсе. Excel может сам понимать формат написанного числа. Например, если ввести в ячейку значение 15% (число со знаком процента), для него автоматически будет выбран Процентный формат. Та же история с вводом дат. Введите дату в привычном для всех формате (например, 15.02.2016) и программа это поймёт, установив формат «Дата». Для значения 12:30 будет выбран формат «Время», а для 1 1/25 — «Дробный». Этого помогает значительно сократить время, затрачиваемое на форматирование.
Форматирование с помощью ленточных команд
На вкладке ленты Главная (блок Число») вынесены самые популярные команды форматирования данных:
Форматирование числа командами на ленте- В выпадающем списке представлены 11 часто применяемых форматов данных
- Кнопка Финансовый числовой формат — формат с разделителями разрядов, двумя знаками после запятой и выбранным знаком валюты
- Процентный формат – число умножается на 100 и добавляется символ %
- Формат с разделителями – разделители разрядов, два знака после запятой
- Кнопки Увеличить разрядность и Уменьшить разрядность — добавляют и уменьшают количество десятичных знаков после запятой
Диалоговое окно «Формат ячеек» Microsoft Excel
Диалоговое окно Формат ячеек позволяет выполнить глубокую настройку форматирования. Чтобы вызвать это окно, используйте один из перечисленных способов:
- Нажмите комбинацию клавиш Ctrl+1
- На ленте в блоке Число нажмите на пиктограмме со стрелкой в нижнем правом углу блока
- На ленте в блоке Число в раскрывающемся списке выберите Другие числовые форматы…
- Кликните правой кнопкой мыши на ячейке и в контекстном меню выберите Формат ячеек…
Формат данных выбираем на вкладке Число диалогового окна. Вкладка разделена на две части. В левой мы выбираем формат числа из приведенного списка, а в правой – делаем дополнительные настройки. Вы можете выбрать следующие форматы:
- Общий – устанавливается в ячейках по умолчанию. Формат не имеет настроек, внешний вид данных определяется MS Excel автоматически
- Числовой – настройка для десятичных чисел. Настраивается количество знаков после запятой, отображение разделителя разрядов и вид отрицательного числа
- Денежный – название говорит само за себя. Настраивается количество знаков после запятой и значок денежной единицы
- Финансовый – аналогичен денежному, но значок валюты имеет отступ от числа. При записи в строках, значки располагаются друг под другом
- Дата – форматы отображения даты, настраивается макет отображения и регион для подбора вариантов
- Время – Аналогично «Дата», но отображается только время
- Процентный – исходное число умножается на 100 и добавляется значок %. Настраивается количество знаков после запятой
- Дробный – настраиваются варианты отображения числа в виде простой дроби
- Экспоненциальный – настраивается количество знаков после запятой
- Текстовый – заставляет Excel интерпретировать содержимое, как текст. Формат не имеет настроек
- Дополнительный – содержит четыре дополнительных формата, которые используются реже: Почтовый индекс, Индекс, Номер телефона, Табельный номер.
- Все форматы – позволяет сделать пользовательские настройки отображения. Этому очень мощному инструменту я, пожалуй, посвящу отдельный пост.
Мы с вами рассмотрели настройки форматов чисел, и теперь ваши расчёты будут оформлены с учётом особенностей данных. Если все данные имеют корректные форматы – лист хорошо читается, с ним проще работать.
В следующих постах мы рассмотрим работу с окнами, листами, ячейками Excel.
У вас остались вопросы по теме поста? Пишите комментарии, постараюсь помочь!
Как изменить формат даты в Excel и создать собственное форматирование
Первая часть нашего руководства посвящена форматированию дат в Excel и объясняет, как установить форматы даты и времени по умолчанию, как изменить формат даты в Excel, как создать собственное форматирование даты и преобразовать даты в другой языковой стандарт.
Наряду с числами наиболее распространенными типами данных, которые люди используют в Excel, являются дата и время. Однако работать с ними может быть довольно сложно, во-первых, потому что одна и та же дата может отображаться в Excel различными способами, а во-вторых, потому что Excel всегда внутренне хранит даты в одном и том же формате, независимо от того, как вы отформатировали дату в данную ячейку.
Более глубокое знание форматов дат в Excel может помочь вам сэкономить массу времени. И это как раз цель нашего подробного руководства по работе с датами в Excel. В первой части мы сосредоточимся на следующих функциях:
Формат даты Excel
Прежде чем вы сможете воспользоваться преимуществами мощных функций даты Excel, вы должны понять, как Microsoft Excel хранит дату и время, потому что это основной источник путаницы. Хотя вы ожидаете, что Excel запомнит день, месяц и год для даты, это работает не так…
Excel хранит даты как последовательные числа, и только форматирование ячейки приводит к отображению числа как даты, времени или даты и времени.
Даты в Excel
Все даты хранятся в виде целых чисел , представляющих количество дней с 1 января 1900 года, которое сохраняется как число 1, до 31 декабря 9999 года сохраняется как 2958465.
В системе:
- 2 — 2 января 1900
- 3 — 3 января 1900 г.
- 42005 — 1 января 2015 г. (потому что это 42 005 дней после 1 января 1900 г.)
Время в Excel
раз хранятся в Excel как десятичные числа, между.0 и .99999, которые представляют собой долю дня, где .0 — 00:00:00, а .99999 — 23:59:59.
Например:
- 0,25 — 06:00
- 0,5 — 12:00
- 0.541655093 это 12:59:59
Дата и время в Excel
Excel сохраняет дату и время в виде десятичных чисел, состоящих из целого числа, представляющего дату, и десятичной части, представляющей время.
Например:
- 1,25 — 1 января 1900 г. 6:00
- 42005.5 — 1 января 2015 г., 12:00
Как преобразовать дату в число в Excel
Если вы хотите узнать, какой серийный номер представляет определенную дату или время, отображаемые в ячейке, вы можете сделать это двумя способами.
1. Диалоговое окно «Форматирование ячеек»
Выберите ячейку с датой в Excel, нажмите Ctrl + 1, чтобы открыть окно Формат ячеек и переключитесь на вкладку Общие .
Если вы просто хотите узнать серийный номер за датой, без фактического преобразования даты в число, запишите число, которое вы видите под Образец , и нажмите Отмена , чтобы закрыть окно.Если вы хотите заменить дату числом в ячейке, нажмите ОК.
2. Функции Excel DATEVALUE и TIMEVALUE
Используйте функцию ДАТАЗНАЧ () для преобразования даты Excel в серийный номер, например = ДАТАЗНАЧ ("1/1/2015") .
Используйте функцию TIMEVALUE (), чтобы получить десятичное число, представляющее время, например = TIMEVALUE ("6:30 AM") .
Чтобы узнать дату и время, объедините эти две функции следующим образом:
= ДАТАЗНАЧЕНИЕ («01.01.2015») И ВРЕМЯЗНАЧЕНИЕ («6:00 AM»)
Примечание. Поскольку серийные номера Excel начинаются с 1 января 1900 года, а отрицательные числа не распознаются, даты до 1900 года в Excel не поддерживаются.
Если вы введете такую дату в лист, скажем, 31.12.1899, это будет текстовое значение, а не дата, а это означает, что вы не можете выполнять обычные арифметические операции с датами на ранних датах. Чтобы убедиться, вы можете ввести формулу = ДАТАЗНАЧЕНИЕ ("31.12.1899") в какую-нибудь ячейку, и вы получите ожидаемый результат — # ЗНАЧ! ошибка.
Если вы имеете дело со значениями даты и времени и хотите преобразовать время в десятичное число , ознакомьтесь с формулами, описанными в этом руководстве: Как преобразовать время в десятичное число в Excel.
Формат даты по умолчанию в Excel
Когда вы работаете с датами в Excel, краткий и длинный форматы даты извлекаются из региональных настроек Windows. Эти форматы по умолчанию отмечены звездочкой (*) в диалоговом окне Формат ячейки :
Форматы даты и времени по умолчанию в поле Формат ячейки изменяются, как только вы меняете настройки даты и времени в Панели управления, что приводит нас прямо к следующему разделу.
Как изменить форматы даты и времени по умолчанию в Excel
Если вы хотите установить другой формат даты и / или времени по умолчанию на вашем компьютере, например, изменить формат даты в США на стиль Великобритании, перейдите в Панель управления и щелкните Регион и язык . Если на панели управления открывается вид по категориям, щелкните Часы, язык и регион > Регион и язык > Изменить дату, время или числовой формат .
На вкладке Форматы выберите регион в разделе Формат , а затем установите форматирование даты и времени, щелкнув стрелку рядом с форматом, который вы хотите изменить, и выбрав нужный из раскрывающегося списка:
Если вас не устраивает какой-либо формат времени и даты, доступный на вкладке Formats , нажмите кнопку Additional settings в нижней правой части диалогового окна Region and Language . Откроется диалоговое окно Customize , в котором вы переключаетесь на вкладку Date и вводите пользовательский краткий или / или длинный формат даты в соответствующем поле.
Как быстро применить форматирование даты и времени по умолчанию в Excel
Microsoft Excel имеет два формата даты и времени по умолчанию — краткий и длинный, как описано в формате даты Excel по умолчанию.
Чтобы быстро изменить формат даты в Excel на формат по умолчанию, выполните следующие действия:
- Выберите даты, которые нужно отформатировать.
- На вкладке Home в группе Number щелкните маленькую стрелку рядом с полем Number Format и выберите нужный формат — краткую дату, длинную дату или время.
Если вам нужны дополнительные параметры форматирования даты, выберите Дополнительные числовые форматы из раскрывающегося списка или щелкните панель запуска диалогового окна рядом с Число .Откроется знакомое диалоговое окно Формат ячеек , в котором вы можете изменить формат даты.
Наконечник. Если вы хотите быстро установить формат даты в Excel на дд-ммм-гг , нажмите Ctrl + Shift + #. Просто имейте в виду, что этот ярлык всегда применяет формат dd-mmm-yy , например 01-Jan-15, независимо от настроек вашего региона Windows.
Как изменить формат даты в Excel
В Microsoft Excel даты могут отображаться разными способами.Когда дело доходит до изменения формата даты данной ячейки или диапазона ячеек, самый простой способ — открыть диалоговое окно Формат ячеек и выбрать один из предопределенных форматов.
- Выберите даты, формат которых вы хотите изменить, или пустые ячейки, в которые вы хотите вставить даты.
- Нажмите Ctrl + 1, чтобы открыть диалоговое окно Формат ячеек . Кроме того, вы можете щелкнуть правой кнопкой мыши выбранные ячейки и выбрать Формат ячеек… из контекстного меню.
- В окне Формат ячеек перейдите на вкладку Число и выберите Дата в списке Категория .
- В поле Введите выберите желаемый формат даты. После этого в поле Sample отобразится предварительный просмотр формата с первой датой в выбранных вами данных.
- Если вас устраивает предварительный просмотр, нажмите кнопку OK , чтобы сохранить изменение формата и закрыть окно.
Если формат даты на листе Excel не меняется, скорее всего, ваши даты отформатированы как текст, и вам нужно сначала преобразовать их в формат даты.
Как преобразовать формат даты в другой язык
Если у вас есть файл, полный иностранных дат, вы, скорее всего, захотите изменить их на формат даты, используемый в вашей части мира. Допустим, вы хотите преобразовать американский формат даты (месяц / день / год) в формат европейского стиля (день / месяц / год).
Самый простой способ изменить формат даты в Excel в зависимости от того, как даты отображаются на другом языке, выглядит следующим образом:
- Выберите столбец дат, который нужно преобразовать в другой языковой стандарт.
- Нажмите Ctrl + 1, чтобы открыть ячейки формата
- Выберите нужный язык в разделе «Локаль (местоположение)» и нажмите «ОК», чтобы сохранить изменения.
Если вы хотите, чтобы даты отображались на другом языке, вам нужно будет создать собственный формат даты с кодом локали.
Создание пользовательского формата даты в Excel
Если вам не подходит ни один из предопределенных форматов даты Excel, вы можете создать свой собственный.
- На листе Excel выберите ячейки, которые нужно отформатировать.
- Нажмите Ctrl + 1, чтобы открыть диалоговое окно Формат ячеек .
- На вкладке Number выберите Custom из списка Category и введите нужный формат даты в поле Type .
- Нажмите ОК, чтобы сохранить изменения.
Наконечник. Самый простой способ установить собственный формат даты в Excel — начать с существующего формата, близкого к тому, что вы хотите. Для этого сначала щелкните Дата в списке Категория и выберите один из существующих форматов в поле Тип .После этого щелкните Custom и внесите изменения в формат, отображаемый в поле Type .
При настройке пользовательского формата даты и в Excel вы можете использовать следующие коды.
| Код | Описание | Пример (1 января 2005 г.) |
| м | Номер месяца без нуля в начале | 1 |
| мм | Номер месяца с нулем в начале | 01 |
| ммм | Название месяца, краткая форма | Янв |
| мм мм | Название месяца, полная форма | Январь |
| ммммм | Месяц как первая буква | J (обозначает январь, июнь и июль) |
| д | Номер дня без нуля в начале | 1 |
| дд | Номер дня с ведущим нулем | 01 |
| ddd | День недели, краткая форма | Пн |
| дддд | День недели, полная форма | Понедельник |
| гг | Год (последние 2 цифры) | 05 |
| гггг | Год (4 цифры) | 2005 |
При настройке пользовательского формата времени и в Excel вы можете использовать следующие коды.
| Код | Описание | Отображается как |
| ч | Часы без нуля в начале | 0-23 |
| чч | Часы с ведущим нулем | 00-23 |
| м | Минуты без нуля в начале | 0-59 |
| мм | Минуты с нулем в начале | 00-59 |
| с | Секунды без нуля в начале | 0-59 |
| н.с. | Секунды с ведущим нулем | 00-59 |
| AM / PM | Периоды дня (если не указано, используется 24-часовой формат времени) | AM или PM |
Примечание. Если вы настраиваете собственный формат, который включает значения даты и времени, и вы используете « m » сразу после « hh » или « h » или непосредственно перед «ss» или «s», Microsoft Excel вместо месяца будет отображаться минут .
При создании произвольного формата даты в Excel вы можете использовать запятую (,), тире (-), косую черту (/), двоеточие (:) и другие символы.
Например, одна и та же дата и время, скажем, 13 января 2015 г. 13:03 , могут отображаться по-разному:
| Формат | Отображается как |
| дд-ммм-гг | 13 января 15 |
| мм / дд / гггг | 13.01.2015 |
| м / дд / гг | 13.01.15 |
| дддд, м / д / гг ч: мм AM / PM | Вторник, 13.01.15, 13:03 |
| ддд, мммм дд, гггг чч: мм: сс | вт, 13 января 2015 г. 13:03:00 |
Как создать собственный формат даты Excel для другого языкового стандарта
Если вы хотите отображать даты на другом языке, вы должны создать собственный формат и префикс даты с соответствующим кодом языкового стандарта.Код локали должен быть заключен в [квадратные скобки] и предваряться знаком доллара ($) и тире (-). Вот несколько примеров:
- [-409 $] — английский, без названия
- [$ -1009] — английский, Канада
- [$ -407] — Германия, Германия
- [-807 $] — Германия, Швейцария
- [-804 $] — Бенгали, Индия
- [-804 $] — Китайцы, Китай
- [-404 $] — Китай, Тайвань
Полный список кодов языков можно найти в этом блоге.
Например, вот как вы настраиваете пользовательский формат даты Excel для китайского языка в формате год-месяц-день (день недели), время :
На следующем изображении показано несколько примеров одной и той же даты, отформатированной с использованием разных кодов локали традиционным для соответствующих языков способом:
Формат даты Excel не работает — исправления и решения
Обычно Microsoft Excel очень хорошо понимает даты, и вы вряд ли столкнетесь с какими-либо препятствиями при работе с ними.Если у вас возникла проблема с форматом даты Excel, ознакомьтесь со следующими советами по устранению неполадок.
Ячейка недостаточна для размещения даты целиком
Если вы видите несколько знаков решетки (#####) вместо дат на листе Excel, скорее всего, ваши ячейки недостаточно широки, чтобы вместить все даты.
Решение . Дважды щелкните правую границу столбца, чтобы изменить ее размер в соответствии с датами. Кроме того, вы можете перетащить правую границу, чтобы установить нужную ширину столбца.
Отрицательные числа форматируются как даты
Во всех современных версиях Excel 2013, 2010 и 2007 хеш-метки (#####) также отображаются, когда ячейка, отформатированная как дата или время, содержит отрицательное значение. Обычно это результат, возвращаемый какой-либо формулой, но это также может произойти, когда вы вводите отрицательное значение в ячейку, а затем форматируете эту ячейку как дату.
Если вы хотите отображать отрицательные числа как отрицательные даты, вам доступны два варианта:
Решение 1. Перейти к системе дат 1904 года.
Перейдите в Файл > Параметры > Расширенный , прокрутите вниз до При вычислении раздела книги установите флажок Использовать систему дат 1904 и нажмите ОК .
В этой системе 0 — 1 января 1904 года; 1 — 2 января 1904 г .; а -1 отображается как отрицательная дата -2-янв-1904.
Конечно, такое представление очень необычно и требует времени, чтобы к нему привыкнуть, но это правильный путь, если вы хотите выполнять вычисления с ранними датами.
Решение 2. Используйте функцию ТЕКСТ Excel.
Другой возможный способ отображения отрицательных чисел как отрицательных дат в Excel — использование функции ТЕКСТ. Например, если вы вычитаете C1 из B1 и значение в C1 больше, чем в B1, вы можете использовать следующую формулу для вывода результата в формате даты:
= ТЕКСТ (ABS (B1-C1), «- d-mmm-yyyy»)
Вы можете изменить выравнивание ячеек на выравнивание по правому краю, и, естественно, вы можете использовать любые другие настраиваемые форматы даты в формуле ТЕКСТ.
Примечание. В отличие от предыдущего решения, функция ТЕКСТ возвращает текстовое значение, поэтому вы не сможете использовать результат в других вычислениях.
Даты импортируются в Excel как текстовые значения
Когда вы импортируете данные в Excel из файла .csv или другой внешней базы данных, даты часто импортируются как текстовые значения. Они могут выглядеть для вас как обычные даты, но Excel воспринимает их как текст и обрабатывает соответственно.
Решение .Вы можете преобразовать «текстовые даты» в формат даты с помощью функции Excel DATEVALUE или функции Text to Columns. Подробную информацию см. В следующей статье: Как преобразовать текущий текст в Excel.
Вот как вы форматируете даты в Excel. В следующей части нашего руководства мы обсудим различные способы вставки даты и времени в рабочие листы Excel. Спасибо за чтение и увидимся на следующей неделе!
Вас также может заинтересовать
Доступные числовые форматы в Excel
В Excel вы можете форматировать числа в ячейках для таких вещей, как валюта, проценты, десятичные дроби, даты, номера телефонов или номера социального страхования.
Выберите ячейку или диапазон ячеек.
На вкладке Home выберите Number из раскрывающегося списка.
Или вы можете выбрать один из следующих вариантов:Нажмите CTRL + 1 и выберите Число .
Щелкните правой кнопкой мыши ячейку или диапазон ячеек, выберите Формат ячеек… и выберите Число .
Выберите средство запуска диалогового окна рядом с Номер а затем выберите Номер .
Выберите нужный формат.
Числовые форматы
Чтобы просмотреть все доступные числовые форматы, нажмите кнопку запуска диалогового окна рядом с Number на вкладке Home в группе Number .
Формат | Описание |
|---|---|
Общие | Формат чисел по умолчанию, который Excel применяет при вводе числа.По большей части числа, отформатированные в формате General , отображаются так, как вы их вводите. Однако, если ширина ячейки недостаточна для отображения всего числа, формат General округляет числа с десятичными знаками. В числовом формате General также используется научная (экспоненциальная) запись для больших чисел (12 и более цифр). |
Число | Используется для общего отображения чисел.Вы можете указать количество десятичных знаков, которые вы хотите использовать, хотите ли вы использовать разделитель тысяч и как вы хотите отображать отрицательные числа. |
Валюта | Используется для общих денежных значений и отображает символ валюты по умолчанию с числами.Вы можете указать количество десятичных знаков, которые вы хотите использовать, хотите ли вы использовать разделитель тысяч и как вы хотите отображать отрицательные числа. |
Бухгалтерский учет | Также используется для денежных значений, но выравнивает символы валюты и десятичные точки чисел в столбце. |
Дата | Отображает серийные номера даты и времени в виде значений даты в соответствии с указанным типом и локалью (местоположением). Форматы даты, начинающиеся со звездочки ( * ), реагируют на изменения в региональных настройках даты и времени, указанные в Панели управления.На форматы без звездочки не влияют настройки Панели управления. |
Время | Отображает серийные номера даты и времени в виде значений времени в соответствии с указанным типом и локалью (местоположением). Форматы времени, начинающиеся со звездочки ( * ), реагируют на изменения в региональных настройках даты и времени, указанные в Панели управления.На форматы без звездочки не влияют настройки Панели управления. |
Процент | Умножает значение ячейки на 100 и отображает результат с символом процента (% ). Вы можете указать количество десятичных знаков, которое хотите использовать. |
Дробь | Отображает число в виде дроби в соответствии с указанным типом дроби. |
Научный | Отображает число в экспоненциальной записи, заменяя часть числа на E + n, где E (что означает Exponent) умножает предыдущее число на 10 в n-й степени.Например, в формате Scientific с двумя десятичными знаками 12345678901 отображается как 1,23E + 10, что составляет 1,23 умноженное на 10 в 10-й степени. Вы можете указать количество десятичных знаков, которое хотите использовать. |
Текст | Обрабатывает содержимое ячейки как текст и отображает содержимое точно так, как вы его вводите, даже когда вы вводите числа. |
Особый | Отображает число как почтовый индекс (почтовый индекс), номер телефона или номер социального страхования. |
Custom | Позволяет изменять копию существующего кода числового формата.Используйте этот формат для создания настраиваемого числового формата, который добавляется в список кодов числовых форматов. Вы можете добавить от 200 до 250 пользовательских числовых форматов, в зависимости от языковой версии Excel, установленной на вашем компьютере. Дополнительные сведения о настраиваемых форматах см. В разделе Создание или удаление настраиваемого числового формата. |
Вы можете применять разные форматы к числам, чтобы изменить их внешний вид. Форматы изменяют только способ отображения чисел и не влияют на значения.Например, если вы хотите, чтобы число отображалось как валюта, вы должны щелкнуть ячейку с числовым значением> Валюта .
Применение числового формата изменяет только способ отображения числа и не влияет на значения ячеек, которые используются для выполнения вычислений. Вы можете увидеть фактическое значение в строке формул.
Вот список доступных числовых форматов и способы их использования в Excel в Интернете:
Формат числа | Описание |
|---|---|
Общий | Формат чисел по умолчанию.Если ширина ячейки недостаточна для отображения всего числа, в этом формате число округляется. Например, 25,76 отображается как 26. Кроме того, если число состоит из 12 или более цифр, Общий формат отображает значение в научном (экспоненциальном) представлении. |
Номер | Работает очень похоже на формат General , но по-разному показывает числа с десятичными разделителями и отрицательными числами.Вот несколько примеров того, как в обоих форматах отображаются числа: |
Валюта | Показывает денежный символ с числами. Вы можете указать количество десятичных знаков с помощью Увеличить десятичное значение или Уменьшить десятичное значение . |
Бухгалтерский учет | Также используется для денежных значений, но выравнивает символы валюты и десятичные точки чисел в столбце. |
Краткая дата | Показывает дату в следующем формате: |
Длинная дата | Показывает месяц, день и год в следующем формате: |
Время | Показывает порядковые номера даты и времени в виде значений времени. |
В процентах | Умножает значение ячейки на 100 и отображает результат с символом процента (% ). Используйте Увеличить десятичное число или Уменьшить десятичное значение , чтобы указать необходимое количество десятичных знаков. |
Дробь | Показывает число в виде дроби.Например, 0,5 отображается как ½. |
Scientific | Отображает числа в экспоненциальной системе счисления, заменяя часть числа на E + n , где E (Exponent) умножает предыдущее число на 10 в n -й степени. Например, в формате Scientific с двумя десятичными знаками 12345678901 отображается как 1.23E + 10, что составляет 1,23 умноженное на 10 в 10-й степени. Чтобы указать количество десятичных знаков, которое вы хотите использовать, примените Увеличить десятичное значение или Уменьшить десятичное значение . |
Текст | Обрабатывает значение ячейки как текст и отображает его точно так, как вы его вводите, даже когда вы вводите числа. Подробнее о форматировании чисел как текста. |
Как форматировать числа в Excel
Как форматировать числа в Excel
Excel — это, по сути, программа, которая манипулирует числами: поэтому заставить ваши числа выглядеть так, как вы хотите, является важным навыком. Внешний вид числа, введенного в ячейку Excel, можно изменить разными способами. В этом сообщении блога показано, как это сделать.
Общие форматы
Вы можете изменить числовой формат, выбрав Главная> Число> Форма числа t и выбрав его из раскрывающегося меню.
Excel 2013 предлагает 10 форматов в этом раскрывающемся списке, Excel 2007 предлагает 11, хотя их будет больше, если вы выберете Дополнительные числовые форматы внизу списка. Вы даже можете создать свой собственный. Даже если вы решите придерживаться общего формата по умолчанию (как мы рекомендуем), полезно ознакомиться с некоторыми другими, чтобы иметь возможность распознавать и адаптировать их в таблицах других людей.
Общие
По умолчанию числовые значения отображаются в виде простых чисел, без знаков доллара, цвета, запятых и т.п.
В процентах
При форматировании числа в процентах это число отображается так, как если бы оно было умножено на 100, и добавляется знак процента к отображению. Если вы форматируете ячейку в процентах, то любое введенное число будет обрабатываться как процент, поэтому 30 будет 30% или 0.3. Если вы отформатируете ячейку, которая уже содержит 30 в процентах, она станет 3000%.
Некоторые люди предпочитают вводить проценты как 100-кратное их фактическое значение с символом% возраста в соседней ячейке, а затем делить это значение на 100 во всех последующих вычислениях. Убедитесь, что вы проверили, как настроены используемые проценты.
Бухгалтерский учет
Бухгалтеры и бухгалтеры будут знакомы с этим форматом, который добавляет знак £ (версия для Великобритании — $ в США и т. Д.)) к левому краю ячейки и добавляет два десятичных разряда (чтобы соответствовать пенсу / центам и т. д.). Хотя знакомство с этим рекомендуется, сотни знаков фунта в электронной таблице, в которой есть только одна валюта, могут быть ненужными. В таких случаях мы рекомендуем указывать валюту на видном месте в заголовке и использовать числа без украшений (возможно, с двумя десятичными знаками).
Стиль запятой
У этого полезного стиля есть собственная кнопка:
Стиль запятой добавляет запятые к большим числам и добавляет два десятичных знака (таким образом, 1000 становится 1000.00). Если ваша электронная таблица в основном заполнена целыми числами, два десятичных знака, вероятно, не нужны.
Команда EwB
Числа в формате
с VBA NumberFormat
Число 1000000000 трудно прочитать с первого взгляда, не так ли? Сколько было нулей? Точно так же число 12.349845423084 наверняка содержит много десятичных цифр, но действительно ли все эти числа необходимы, если вы смотрите на цену продукта за единицу? Взгляните на эти цифры и скажите, как бы вы их сравнили.
12,54332 955,3345 1,058383 2342,234
На первый взгляд может показаться, что все они одного порядка величины, но десятичная точка отображается в разных столбцах для каждого числа. Все цифры на самом деле разные по порядку величины.
Все эти примеры иллюстрируют некоторые серьезные проблемы с удобочитаемостью, которые являются большой ловушкой ошибок человеческой деятельности. Чтобы исправить эти проблемы с удобочитаемостью, использует VBA NumberFormat , который является свойством объектов Range (того же объекта Range, который управляет отдельными ячейками в вашей электронной таблице).В Excel основные форматы легко доступны через числовую часть ленты:
Наиболее распространенные параметры форматирования ячеек Excel, отображаемые в раскрывающемся списке
Возможно, вы даже видели загадочные форматы чисел в Custom , например:
Диалоговое окно настраиваемых форматов
В этом руководстве мы будем работать со свойством VBA NumberFormat, которое позволяет автоматизировать форматирование чисел в Excel без необходимости медленно перемещаться по экранам выше.В конце этого руководства вы поймете, как работают эти коды, и сможете написать собственный код в макросах VBA.
Назначение свойства VBA NumberFormat и доступ к нему
Большая часть этого руководства будет посвящена написанию самих кодов. Но поскольку это блог VBA, мы сначала отметим, как программно назначать форматы.
Свойство VBA NumberFormat является свойством объекта диапазона, поэтому вы можете назначить формат своему диапазону следующим образом:
Sub assigning_numberformats ()
Dim cell_to_format As Range
Dim vector_to_format As Range
Dim matrix_to_format As Range
Установить cell_to_format = Range ("A1")
Установить vector_to_format = Range ("B1: B100")
Установить matrix_to_format = Range ("C1: D50")
range_to_format.NumberFormat = "ВАШ КОД ФОРМАТА ЗДЕСЬ" 'замените диапазон и код формата
Концевой переводник Создавайте эффективные макросы с помощью нашего бесплатного комплекта разработчика VBA.
Такой макрос легко скопировать и вставить, но сложнее сделать его самостоятельно. Чтобы помочь вам создавать такие макросы, мы создали бесплатный VBA Developer Kit , полный предварительно созданных макросов, чтобы вы могли управлять вводом-выводом файлов, массивами, строками и многим другим — скачайте копию ниже.
Конечно, вам нужно заменить range_to_format на диапазон, который вам действительно нужно отформатировать.В этом макросе мы предварительно определили три диапазона (cell_to_format, vector_to_format, matrix_to_format), но вы можете использовать все, что захотите.
Вот и все, что нужно для назначения формата диапазона. Это так же просто, как нажимать кнопки в Excel, за исключением того, что с помощью VBA вы можете массово производить желаемые результаты
Если вы хотите получить доступ к существующему свойству NumberFormat ячейки или диапазона, вы можете прочитать его как строку в макросе, например:
the_format = Range («ВАШ ДИАПАЗОН ЗДЕСЬ»).NumberFormat Вот и все. Назначить NumberFormat и получить к нему доступ с помощью VBA действительно довольно просто. Теперь давайте посмотрим на самую сложную часть: написание собственных кодов формата.
Код формата
КодыVBA NumberFormat аналогичны регулярным выражениям в том, что вы определяете шаблон, которому должен следовать компьютер. Это делает формат динамичным и способным принимать множество входных данных.
Выражение типа 0.0 # "на часть" сначала может показаться немного странным, но на самом деле оно обозначает шаблон.Разберем его на части:
The Zero (0): Должна отображаться как минимум одна цифра слева и одна справа от десятичной дроби
Символ фунта / хэштега / числа (#): , если здесь есть значащая цифра, будет отображаться, если нет, то здесь не будет цифры. И наоборот, если справа от десятичного разряда более 3 цифр, число будет округлено до двух десятичных знаков.
Цитаты («»): Текст между кавычками всегда будет добавляться к введенным числам.
Применяя этот код формата с этой строкой:
Диапазон ("A1: A4"). NumberFormat = "0.0 #" "на часть" "" В итоге мы получим следующие результаты: 2018-09-19-first-one.png
Необработанные числа (общий формат) справа, а отформатированные слева
Обратите внимание, что здесь происходит. Если мы введем только 5, мы вернемся к 5.0. Но если мы введем три цифры после десятичной точки, наше отформатированное число будет округлено до 5.58. Важно отметить, что базовое значение, хранящееся в ячейке A3, по-прежнему равно 0,57777. Просто отображается как как 0,58. Вот что такое читабельность. На самом деле мы не меняем цифры; мы просто представляем их таким образом, чтобы их было легче усвоить.
Видите, как слова «по частям» были добавлены к каждой записи? Я хочу, чтобы вы, , отметили двойные кавычки в нашем макросе. Если вам нужен текст в формате через VBA, обязательно отслеживайте свои кавычки, например «Внешний текст» «Внутренний текст» «Внешний текст»." Если вы введете только один набор кавычек вокруг внутреннего текста, VBA сочтет, что строка закончена, и запутается. Эта распространенная ошибка будет вызывать у вас неприятные ошибки, пока вы не выполните его строгие требования к кавычкам. Я обсуждал сложности конкатенации кавычек в VBA раньше. Я рекомендую вам взглянуть, если вам нужны дополнительные указания.
Вот вам быстрый тест: как изменится наш вывод, если мы изменим макрос NumberFormat на
Диапазон ("A1: A4").NumberFormat = "# .0 #" "на часть" "" Единственное отличие — это # перед десятичной дробью.
Ответ: Все останется прежним, кроме A3 . Помните, что # означает «отображать, если есть, но не отображать, если нет». Таким образом, числовая часть A3 изменится с 0,58 на 0,58. Все остальные записи имеют цифры перед десятичной точкой, которые считаются значимыми и всегда будут отображаться.
Принудительное обнуление
Вы можете заставить все ваши числа включать 3 десятичных знака, независимо от того, было ли в исходном вводе 3 десятичных знака.Для этого мы можем использовать «.000». Более 4 десятичных знаков будут округляться, менее трех приведет как минимум к одному завершающему нулю.
Таким образом, 5 станет 5.