Таким образом вы можете создать индивидуальное представление для комбинаций цифр и букв в ячейках Excel.
Установка индивидуальных числовых форматов в Excel - пример: номер статьи
Таблица содержит обширный список данных об элементах. В первом столбце артикул дается шестизначным числом.
Из-за изменений в управлении запасами каждому номеру позиции должен предшествовать идентичный буквенный код. Буквы и всегда три цифры также должны быть разделены точками. Таким образом, 123456 становится ABC.123.456.
Вы можете решить эту проблему в мгновение ока с помощью определяемого пользователем числового формата. Для этого действуйте следующим образом:
- Выделите ячейки, содержащие номера деталей, которые нужно преобразовать. Затем используйте комбинацию клавиш CTRL 1, чтобы вызвать диалоговое окно FORMAT CELLS. Это работает во всех версиях Excel.
- Активируйте вкладку ОПЛАТА.
- В разделе КАТЕГОРИЯ щелкните ПОЛЬЗОВАТЕЛЬСКИЙ ОПРЕДЕЛЕННЫЙ в диалоговом окне.
- Затем введите следующую инструкцию форматирования в поле ТИП: "ABC." ### "." ###
- Подтвердите это определяемое пользователем форматирование с помощью кнопки OK.
После этого числа появятся в вашей таблице в нужной вам форме. На рисунке справа показаны номера артикулов в стандартном формате с описанным пользовательским форматированием.
Обратите внимание, что настраиваемое форматирование фактически не добавляет к числам текст и точки. Они отображаются только при сохранении исходного числа в ячейке.
Преимущество этого заключается в том, что вы можете использовать числа для вычислений, которые больше не будут работать при преобразовании в текст.
Как преобразовать контент с помощью табличных функций
Если буквы и точки действительно должны появиться в ячейке, их невозможно преобразовать в текст. Для этого действуйте следующим образом:
Добавьте вспомогательный столбец рядом с столбцом с номерами артикулов. Здесь номера статей снабжены текстом и пунктами с использованием формулы.
Если первое число находится в ячейке A2, а C - вспомогательный столбец, введите следующую формулу в ячейку C2:
= "ABC." & LEFT (A2; 3) & "." & RIGHT (A2; 3)
Эта формула показывает номера артикулов в нужной форме в таблице, однако это имеет тот недостаток, что числовые значения теряются, и вы больше не можете проводить с ними расчеты.
На рисунке справа показано использование формулы для генерации номеров артикулов в таблице примеров.
Скопируйте формулу достаточно далеко вниз во вспомогательном столбце. Затем скопируйте вспомогательный столбец в буфер обмена и вставьте его снова, используя команду ВСТАВИТЬ СОДЕРЖАНИЕ с опцией ЗНАЧЕНИЯ.
Формулы заменяются фактическими номерами артикулов. Вы можете удалить столбец со старыми номерами артикулов, если он вам больше не нужен.
Второй пример: успешное форматирование номеров кредитных карт в Excel
К сожалению, в Excel нет интегрированного формата для правильного отображения номеров кредитных карт. Пользовательское форматирование тоже не дает желаемого результата. Добиться желаемого результата можно с помощью формулы во вспомогательной ячейке.
Номера кредитных карт состоят из шестнадцати цифр и часто отображаются блоками по четыре в следующей форме. 1234 1234 1234 1234
Спонтанно желаемое отображение должно быть легко реализовано с использованием следующего определяемого пользователем числового формата.
Этот формат приводит к отображению блоков в таблице. Однако последняя цифра заменяется нулем. Изображение справа показывает, как это выглядит.
Причина нуля в том, что Excel обрабатывает не более 15 значащих цифр. Таким образом, Excel всегда будет устанавливать последнюю цифру равной нулю, если вы вводите номер кредитной карты в виде числа.
Поэтому вы должны вводить номера кредитных карт в виде текста. Вы можете сделать это с помощью предшествующего апострофа или заранее назначив текстовый формат.
Конечно, это не решает проблему отображения номеров кредитных карт в виде блоков по четыре. К сожалению, текстовым значениям нельзя присвоить числовой формат.
Решением здесь также является вставка дополнительного столбца, в котором вы можете использовать формулу для вывода желаемого представления.
Например, если вы ввели номер кредитной карты в ячейку B1, вы можете использовать следующую формулу в ячейке C1, чтобы отобразить ее блоками по четыре:
= LEFT (B1; 4) & "" & PART (B1; 5; 4) & "" & PART (B1; 9; 4) & "" & RIGHT (B1; 4)
На рисунке справа показано использование этой формулы в примере таблицы.
