Как создать свой формат данных в Excel
Формат данных в Microsoft Office Excel — это тип отображения символов в ячейках табличного массива. В самой программе много стандартных вариантов форматирования. Однако иногда требуется создать пользовательский формат. О том, как это сделать, будет рассказано в данной статье.
Прежде чем приступать к созданию собственного формата, необходимо ознакомиться с принципами его изменения. Поменять один тип отображения информации в ячейках таблицы на другой можно по следующей схеме:
- Кликнуть левой клавишей манипулятора по нужной ячейке с данными, чтобы выделить ее.
- По любому месту выделенной области щёлкнуть ПКМ.
- В контекстном меню нажать по строке «Формат ячеек…».
- В открывшемся окне перейти в раздел «Число» и в блоке «Числовые форматы» выбрать один из подходящих вариантов, нажав по нему дважды ЛКМ.
- Щёлкнуть на «ОК» внизу окошка для применения действия.
Обратите внимание! После смены формата числа в табличных ячейках будут отображаться по-другому.
Надстрочный индекс в Excel.
- Выделить пустую ячейку рабочего листа и по указанной выше схеме перейти в окно «Формат ячеек…».
- Чтобы создать свой формат, надо прописать определённый набор кодов в строчку. Для этого нужно выбрать пункт «Все форматы» и в следующем окне в поле «Тип» ввести собственный формат, зная его кодировку в Эксель. При этом каждый раздел кода разделяется от предыдущего точкой с запятой.
- Проверить, как Microsoft Office Excel кодирует тот или иной формат. Для этого надо выбрать любой вариант кодирования из списка доступных в окне и нажать на «ОК».
- Теперь в выбранную ячейку надо ввести любое число, к примеру единицу.
- По аналогии войти в меню формата ячеек и кликнуть по слову «Числовой» в списке представленных значений. Теперь, если ещё раз перейти в раздел «Все форматы», то выбранное форматирование «Числовой», уже будет отображаться в виде кодировки, состоящей из двух разделов: разделитель и точка с запятой. В поле «Тип» будут показываться разделы, причем первый из них характеризует положительное число, а второй применяется для отрицательных значений.
- На этом этапе, когда пользователь уже разобрался в принципе кодирования, он может приступать к созданию собственного формата. Для этой цели ему сначала требуется закрыть меню «Формат ячеек».
- На рабочем листе Excel создать исходный табличный массив, представленный на изображении ниже. Эта таблица рассмотрена для примера, на практике можно создать любую другую табличку.
- Вставить дополнительный столбец между двумя исходными.
Никакие изменения формата на Числовой, Общий или Дата ситуацию не исправляют, но если кликнуть в строку формул (или нажать F2), а затем Enter, то число становится числом, а дата датой. При большом количестве таких чисел вариант, согласитесь, неприемлемый.
Delphi Excel Как Узнать Формат Ячейки
Достаточно часто в Excel выгружаются данные из сторонних программ для их дальнейшей обработки, и нередко дальнейшее использование этих данных в формулах даёт непредсказуемый результат: числа не суммируются, между датами невозможно рассчитать количество дней и т.д.
В этой статье рассматриваются причины появления таких проблем и различные способы их устранения
Причина первая . Число сохранено как текст
В этом случае видно, что числа или даты прижаты к левому краю ячейки (как текст) и, как правило, в левом верхнем углу ячейки имеется маркер ошибки (зелёный треугольник) и тег, который при наведении мышки поясняет, что число сохранено как текст.
Никакие изменения формата на Числовой, Общий или Дата ситуацию не исправляют, но если кликнуть в строку формул (или нажать F2), а затем Enter, то число становится числом, а дата датой. При большом количестве таких чисел вариант, согласитесь, неприемлемый.
- С помощью маркера ошибки и тега. Если в левом верхнем углу ячеек виден маркер ошибки ( зелёный треугольник) и тег, то выделяем ячейки, кликаем мышкой по тегу и выбираем вариант Преобразовать в число
- С помощью операции Найти/Заменить. Предположим, в таблице есть числа с десятичной запятой, сохраненные как текст. Выделяем диапазон с числами — нажимаем Ctrl+h (либо находим на вкладке Главная или в меню Правка для версий до 2007 команду Заменить) — в поле Найти вводим , (запятую) — в поле Заменить на тоже вводим , (запятую) — Заменить все. Таким образом, делая замену запятой на запятую, мы имитируем редактирование ячейки аналогично F2 — Enter
Подобную операцию можно осуществить и с датами с тем лишь отличием, что менять нужно точку на точку.
Кроме того, сторонние программы могут выгружать числа с точкой в качестве десятичного разделителя, тогда поможет замена точки на запятую.
Аналогичную замену можно проделать и формулой (см. ниже), используя функцию ПОДСТАВИТЬ()
- С помощью Специальной вставки. Этот способ более универсальный, так как работает и с дробными числами, и с целыми, а также с датами. Выделяем любую пустую ячейку — выполняем команду Копировать — выделяем диапазон с проблемными числами — Специальная вставка — Сложить — ОК. Таким образом, мы к числам (или датам) прибавляем 0, что никак не влияет на их значение, зато переводит в числовой формат
Вариантом этого приёма может быть умножение диапазона на 1
- С помощью инструмента Текст по столбцам. Этот приём удобно использовать если преобразовать нужно один столбец, так как если столбцов несколько, то действия придётся повторять для каждого столбца отдельно. Итак, выделяем столбец с числами или датами, сохраненными как текст, устанавливаем формат ячейки Общий (для чисел можно установить, к примеру, Числовой или Финансовый). Далее выполняем команду Данные — Текст по столбцам — Готово
- С помощью формул. Если таблица позволяет задействовать дополнительные столбцы, то для преобразования в число можно использовать формулы. Чтобы перевести текстовое значение в число, можно использовать двойной минус, сложение с нулём, умножение на единицу, функции ЗНАЧЕН(), ПОДСТАВИТЬ(). Более подробно можно почитать здесь. После преобразования полученный столбец можно скопировать и вставить как значения на место исходных данных
- С помощью макросов. Собственно, любой из перечисленных способов можно выполнить макросом. Если Вам приходится часто выполнять подобное преобразование, то имеет смысл написать макрос и запускать его по мере необходимости.
Причина вторая . В записи числа присутствуют посторонние символы.
Чаще всего этими посторонними символами являются пробелы. Они могут располагаться как внутри числа в качестве разделителя разрядов, так и до/после числа. В этом случае, естественно, число становится текстом.
Для неразрывных пробелов: =—ПОДСТАВИТЬ(B4;СИМВОЛ(160);»»)
Сразу для тех и других пробелов: =—ПОДСТАВИТЬ(ПОДСТАВИТЬ(B4;СИМВОЛ(160);»»);» «;»»)
Иногда, чтобы добиться желаемого результата, приходится комбинировать перечисленные способы. Например, сначала удалять пробелы, а затем преобразовывать формат ячеек
Условное форматирование в Excel — ЭКСЕЛЬ ХАК
Microsoft Excel предлагает множество полезных функций, с помощью которых можно легко и быстро обрабатывать различные данные. Однако их применение не ограничивается только числами, они могут быть использованы с ячейками, содержащими текст. Ниже представлены основные функции для работы с текстом.