Как сделать формат ячейки в excel чтоб вписать номер счета

Обновлено: 07.07.2024

В MS Excel предусмотрено множество форматов, в которых можно представить свои данные. Но что делать, когда ни один из стандартных форматов не подходит? Ответ прост - создайте свой формат.

Как это сделать?

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

Для вызова этого окна используйте сочетание клавиш "Ctrl+1" или нажмите правую клавишу мышки и в контекстном меню выберите пункт "Формат ячеек. ":

Теперь ваш формат сохранен в Excel и его можно использовать для форматирования. Выделите ячейки, к которым надо применить пользовательский формат, и вызовите диалоговое окно Формат ячеек. В списке форматов найдите свой (он будет последним), выделите его и нажмите "Оk".

Как это работает?

На самом деле все очень просто. Excel использует несколько спецсимволов в масках форматов:

0 (ноль) - один обязательный разряд, т.е. это место в маске формата будет заполнено цифрой из числа, которое пользователь введет в ячейку. Если для этого разряда нет числа, то будет выведен ноль. Например, если к числу 23 применить маску 000, то получится 023, а если к дробному числу 23,032016 применить маску 00,000 - получится 23,032.

[] - в квадратных скобках, перед маской формата, указывает цвет шрифта. Разрешено использовать следующие цвета: черный, белый, красный, синий, зеленый, жёлтый, голубой.

"" - любой пользовательский текст (млн, уп., м и тому подобные) или любые символы (например, пробелы) - надо обязательно заключать в кавычки.

Числовое форматирование Excel - это одна из самых недооценённых пользователями функций программы.

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

Но перед тем, как приступим, хочу обратить ваше внимание на один важный момент.

Суть числового форматирования

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

ВАЖНО понимать, что это НЕ ТАК!

Числовое форматирование влияет только на отображение значения. Само значение при этом никак не изменяется.

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

Числовой формат позволяет изменить внешний вид и сделать данные более аккуратными и легко воспринимаемыми.

Ну а теперь поговорим о синтаксисе числового форматирования.

Синтаксис числового форматирования

Обычно числовое форматирование применяется следующим образом: выделяется ячейка или диапазон ячеек (1), к которому нужно числовой формат применить, и затем из выпадающего списка (2) выбирается готовый шаблон (3).

Как правило, пользователи не интересуются, как именно этот шаблон работает, а просто применяют готовые форматы в своей работе.

Также все готовые шаблоны доступны и в окне Формат ячеек .

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

Именно об этом я и расскажу в этой заметке.

Итак, код числового формата в общем случае состоит из четырех разделов:

ПОЛОЖИТЕЛЬНЫЕ; ОТРИЦАТЕЛЬНЫЕ; НОЛЬ; ТЕКСТ

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

При этом необязательно в коде должны присутствовать все четыре раздела.

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

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

Символ 0 в числовом формате

Давайте рассмотрим на примере.

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


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

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

Преобразование чисел в текст — один из таких сценариев.

В этом уроке я покажу вам несколько простых способов быстрого преобразования чисел в текст в Excel .

Зачем преобразовывать числа в текст в Excel?

При работе с числами в Excel лучше всего использовать их только как числа. Но в некоторых случаях наличие номера может стать проблемой.


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

Сохранение ведущих нулей

Например, если вы введете 001 в ячейку в Excel, вы заметите, что Excel автоматически удаляет ведущие нули (поскольку считает, что они не нужны).

Хотя в большинстве случаев это не проблема (поскольку вы не стали бы начинать нули), в случае, если они вам действительно нужны, одно из решений — преобразовать эти числа в текст.


Таким образом, вы получите именно то, что вводите.

Один из распространенных сценариев, когда вам это может понадобиться, — это когда вы работаете с большими числами, такими как SSN или идентификаторы сотрудников с ведущими нулями.

Ввод больших числовых значений

Знаете ли вы, что в Excel можно ввести только числовое значение, состоящее из 15 цифр? Если вы введете 16-значное число, оно изменит 16-ю цифру на 0.

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


И что еще хуже, вы не получаете никаких подсказок или ошибок. Он просто меняет цифры на 0 после 15-й цифры.

Опять же, об этом позаботятся, если преобразовать число в текст.

Преобразование чисел в даты

Это порадовало многих (в том числе и меня).

Попробуйте ввести 01-01 в Excel, и он автоматически изменит его на дату (01 января текущего года).

Поэтому, если вы введете что-либо, что является допустимым форматом даты в Excel, оно будет преобразовано в дату.

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

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

Преобразование чисел в текст в Excel

В этом разделе я расскажу о четырех различных способах преобразования чисел в текст в Excel.

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

Добавление апострофа

Если вы вручную вводите данные в Excel и не хотите, чтобы ваши числа автоматически меняли формат, вот простой трюк:

Добавьте апостроф (‘) перед числом

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


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

Когда вы добавляете апостроф перед числом, он сообщает Excel, что все последующее следует рассматривать как текст.

Быстрый способ визуально проверить, отформатированы ли ячейки как текст или нет, — это увидеть, выровнены ли числа слева направо. Когда числа отформатированы как текст, они по умолчанию выравниваются по правому краю (так как они идут влево).


Добавление апострофа изменяет выравнивание ячеек влево

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

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

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

Преобразование формата ячейки в текст

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

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

Вы можете продолжить ввод данных, как обычно, и Excel позаботится о том, чтобы ваши числа не изменились.

Вот как это сделать:




Вышеупомянутые шаги изменили бы форматирование ячеек по умолчанию с общего на текстовое.

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

Это означает, что Excel не будет автоматически изменять вводимый вами текст (например, усекать начальные нули или преобразовывать введенные числа в даты).

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

Но помните, что если вы уже ввели числа, которые были изменены в Excel (например, удалили ведущие нули или заменили текст датами), это не вернется. Вам придется снова ввести эти данные.

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

Использование функции TEXT

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

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

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


Хотя Excel удаляет все начальные нули в числах, вы можете использовать приведенную ниже формулу, чтобы получить это:

Вы можете сделать гораздо больше с помощью функции TEXT — например, добавить символ валюты, добавить префикс или суффикс к числам или изменить формат, чтобы он содержал запятую или десятичные дроби.

Использование этого метода может быть полезно, если у вас уже есть данные в Excel и вы хотите отформатировать их определенным образом.

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

Использование текста в столбцы

Еще один быстрый способ преобразования чисел в текст в Excel — использование мастера Text to Columns ( Текст в столбцы).

Хотя цель Text to Columns — разбить данные на несколько столбцов, у него есть настройка, которая также позволяет нам быстро выбирать диапазон ячеек и преобразовывать все числа в текст с помощью нескольких щелчков мышью.

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


Ниже приведены шаги для этого:




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


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

Итак, это четыре простых способа, которые вы можете использовать для быстрого преобразования чисел в текст в Excel .

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

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

Совет: Чтобы быстро применить денежный формат, выберите необходимую ячейку или диапазон ячеек и нажмите сочетание клавиш CTRL+SHIFT+$. Как и денежный формат, финансовый формат используется для отображения денежных значений.

Как сделать денежный в Excel?

Как поменять формат ячейки в Excel на телефоне?

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

Как задать формат вводимых чисел?

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

Чем денежный формат отличается от финансового?

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

Как в Эксель поменять валюту?

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

Что представляет собой диапазон ячеек?

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

Почему не меняется формат ячейки в Экселе?

Возможная причина : в какой-то момент при работе с файлом в ячейках возник конфликт форматов и стиль ячеек был заблокирован. Как правило это происходит при работе с файлом в версиях Excel 2007 и выше.

Какие существуют форматы ячеек?

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

Как обновить формат ячейки в Excel?

Как сделать свой формат ячейки?

  1. Нажмите Ctrl+1, чтобы открыть диалоговое окно Формат ячеек.
  2. Щелкните на вкладке Число и выберите на ней категорию (все форматы).
  3. Введите ваш собственный формат в поле Тип.

Как изменить формат числа?

  1. Выделите ячейку или диапазон ячеек.
  2. На вкладке Главная выберите в раскрывающемся списке формат Числовой. Можно также выбрать один из следующих вариантов: Нажмите клавиши CTRL+1 и выберите формат Числовой. .
  3. Выберите нужный формат.

Как сделать один формат ячеек?

Выберите ячейку (ячейки) со значениями, которые вы хотите отформатировать. Нажмите сочетание клавиш Ctrl + 1 > Число > Все форматы Введите код формата и просмотрите в поле как будет выглядеть значение в ячейке. Нажмите OK, чтобы сохранить и применить только что созданный формат

Какие форматы записи числовых данных используются в Excel?

Excel содержит множество встроенных числовых форматов. Для просмотра полного списка форматов выберите команду Ячейки в меню Формат и перейдите на вкладку Число. Форматы объединены в категории: денежный, дата, время, дробный, текстовый или экспоненциальный.

Что такое экспоненциальный формат числа?

Экспоненциальный формат отображает число в экспоненциальном виде, заменяя часть числа на E+n, в котором E (показатель экспоненты) умножает предыдущее число на 10 до n-йточки. Например, в научном формате с 12345678901 12345678901 отображается 1,23E+10, т. е. от 1,23 до 10-й энергии.

Что значит текстовый формат?

Те́кстовые да́нные (также те́кстовый форма́т) — представление информации строкового типа (то есть, последовательности печатных символов) в вычислительной системе. В MIME закодированным таким образом данным соответствует тип text/plain .

Пытаюсь настроить прямой экспорт отчетов из Диасофт ВФ в ексель. В АБС создается отчет в виде текстового файла с разделителями-табуляциями, который передается сразу на вход ехелю. Вроде все нормально разделяется на колонки, однако в отчете встречается стандартный 20-изначный счет, который по умолчанию ехель воспринимает как число, переводит его в экспоненциальную форму и теряет напрочь хвост счета.
Вопрос -- можно ли каким-нибудь образом объяснить ехелю, что это не число, а текст. Или просто увеличить его разрядность, чтобы номер счета не переводился по умолчанию в нечто типа 4.07Е19.

2 Модераторы

Просьба не переносить в группу Диасофт, проблема не в нем, а в невизуальном открытии txt екселем.

Комментарий

Комментарий

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

Комментарий

Serg_FSB у меня пользователей не напрягает - они его просто не видят. А так - надо с форматами ячеек возиться - ексель умный, он поймёт

Комментарий

Комментарий

Комментарий

Serg_FSB У Вас настолько продвинутые юзеры, что смотрят на содержимое ячеек. Ну и объясните им, что это - формула в екселе, называется "принудительное форматирование в текстовый формат ячейки"

Комментарий

Комментарий

Serg_FSB Стоп. В ячейках они видеть - то вряд ли могут - только в строке формул. Если только ексель как-то нестандартно настроен.

Комментарий

Комментарий

Serg_FSB
А ты как его в экселе открываешь своим макросом или стандартным визардом? в первом случае - всегда сам формат ячейки задать можешь, а во втором - просто указываешь, что столбец текстовый. вообще проблемы не вижу. только что и тот и др. способ попробовал - все работает. Без всяких кавычек.

Комментарий

Shuric
Никак не открываю. excell.exe t123456764.txt

vmc
Вижу и в ячейке и в строке и на принтере. Ехель никак не настроен. Т.е. как поставлен, так и стоит. Может мы имеем в виду разные символы? У меня стоит тот, который на кнопке с русской "Э"

Комментарий

ГЫ! действительно при таком открытии и там и сям видит.
Shuric дык макросом бывает такая фишка, сталкивался.
Serg_FSB апостроф тот же. Придётся видимо открывать файлину по-другому

Комментарий

А для решения этой проблемы лучше завести файлик с правильным форматированием ячеек, а затем импортировать туда текстовый файл (надеюсь структура отчета у ваc не меняется ?) при помощи макроса.

Комментарий

Комментарий

Кстати если Эксел XP и выше то там есть еще XML. А если не брезгуете то попробуйте OO совместно с UNO. Можно организовать такой сервис (легко). На сервере висит UNO демон (он гораздо меньше чем весь OO) И получает в сокет данные. Вам по почте прилетает XLS.

Комментарий

Dem_aka_Deep НУУ, это круто. Я бы если из макроса низя ячейками управлять, на VBA бы макрос написал, это проще было бы - типа открываешь текстовый файл, обрабатываешь, и т.п. ( проще по-моему, и к серваку незачем привязываться)

Комментарий

Dem_aka_Deep
Вы будете смеяться, но ваш Data Interchabge Format преспокойно влетел в ехель со счетами в экспоненциальной форме.

А насчет макроса -- как передать этому макросу имя текстового файла?

Комментарий

Комментарий

Комментарий

А вот здесь вступают в действие механизмы воркфлы (DiasoftBank Workflow). Файл с отчетом создается во tmp - каталоге и имя у него жутко непроизносимое. Напоминаю, что задача -- загнать файл в ексель для нешибко грамотного пользователя. Грамотный сохранит отчет в свой каталог, откроет потом ручками и укажет формат столбца.

А вот мысли про DIF меня заинтересовали. Только пока не работают они.

Комментарий

Serg_FSB Да. похоже буду смеяться. Проверил - хваленый эксел действительно делает этот финт ушами. Хорошо что я на ОО уже больше года сижу.
Прошу прощения за такой (как-бы это сказать. ) лопух.

vmc Не не проще. Я вот что скажу. Есть 100 компьютеров. Вопервых требуется обновлять новые версии макроса на 100 (можно и по сети конечно). Во вторых требуется 100 ЛИЦЕНЗИЙ на офис. (А так и XLSView хватит). В третьих если это отчет то обычно скажем из Диасофта формируем отчет. Потом ждать 5 минут пока эксел запустится? А потом еще лезть в печать? А тут сразу сформировался и полез печатать (т.к. сервер-то ВСЕГДА в памяти).

Комментарий

Serg_FSB Могу порекомендоввать сделать ДРАЙВЕР принтера (в терминологии Diasoft 4x4) который читает текстовик и по OLE Automation засовывает все в Excell Могу даже пример намылить. Но т.к. он на питоне думаю это мало поможет вам.

Это я заглаживаю вину

Комментарий

Dem_aka_Deep Против слова ЛИЦЕНЗИЯ в этой стране мало у кого найдутся аргументы, тем более если оное слово употребляется во множественном числе

Комментарий

Комментарий

Комментарий

Комментарий

Serg_FSB Во а в этом батнике я предлагаю написать не excel.exe %f, а нечто вроде следующего.

import sys
import win32com.client
from win32com.client import Dispatch

xl = Dispatch("Excel.Application")
document = xl.Workbooks.Open(xls_name)
l = 0
for i in open(sys.argv[1],'r').readlines():
xl.Range("A"+str(l)).Value = i
xl.Visible = 1


Тоесть данный скрипт запульнет все только в первый столбец. Но в цикле нам никто не мешает делать разбор и зафигаривать в нужный столбец. АКроме того мы можем сделать заголовок в отчете который нам скажет тип и ширину каждого столбца. И можно еще шаблон (шапки там и прочая).

Тут конечно много упрощений. Но работать будет. Кроме того сразу предостерегаю попдбные скрипты и привели меня к xml т.к. OLE штука довольно капризная в момент активного девелопмента Excell даже машину перезагружал. Но когда сделал работает неплохо (только юзеру права сильно обрезать нельзя - под гостем не работает )

Читайте также: