Как сделать число в excel
Добавил пользователь Евгений Кузнецов Обновлено: 05.10.2024
Проблема наглядной визуализации данных всегда остро стоит перед любым пользователем программы Excel — сегодня мы остановимся на аспектах отображения чисел с выделением размерности.
К примеру, при виде числа 123 456 789 не сразу сообразишь что это за число, а уж при виде большой таблицы с подобными числами глаза точно устанут и таблица станет не читаемой.
Поэтому с помощью подобного отображения чисел можно существенно улучшить внешний вид отображаемых данных.
Проблему с переводом чисел мы можем решить 2 способами:
Начнем с более очевидного и простого варианта.
Способ 1. Формула
Чтобы перевести числа в тысячи, нужно эти числа разделить на тысячу.
Достаточно очевидный факт, тем не менее именно в простоте и заключается суть применения варианта с формулами.
Возьмем таблицу с произвольными числами (продублируем данные в соседний столбец B для более понятного пояснения):
Оставим столбец A неизменным, а работать будем со столбцом B.
Запишем в любую неиспользуемую ячейку число 1 000 (или 1 000 000, в зависимости от порядка, который мы хотим показать).
Копируем данную ячейку, а затем выделяем диапазон ячеек (ячейки B2:B10), где будем менять размерность.
Воспользуемся инструментом Специальная вставка — щелкаем правой кнопкой мыши по выделенному диапазону и выбираем Специальную вставку (либо воспользуемся сочетанием клавиш Ctrl + Alt + V):
В появившемся окошке в разделе Операция выбираем Разделить и щелкаем на ОК, в результате получаем (для наглядности я добавил еще 1 знак после запятой):
Минус данного способа в том, что операцией деления мы меняем содержимое ячеек, что не всегда удобно и возможно.
Например, если какая-нибудь из данных измененных ячеек участвует в вычислении для других, то значения этих других ячеек могут поменяться, что математически верно, но логически совсем не нужно.
Теперь перейдем к рассмотрению более сложного, однако более гибкого и красивого варианта.
Способ 2. Формат ячеек
Помимо изменения самих ячеек, мы можем менять и их формат отображения.
К тому же, если перед нами стоит задача, например, перевести рубли в тысячи рублей, то мы сможем это сделать только данным способом (так как предыдущий способ меняет само число, но подпись оставляет неизменной).
Рассмотрим такую же таблицу, что и в примере выше, аналогично выделяем диапазон с данными (B2:B10), щелкаем правой кнопкой мыши и переходим в Формат Ячеек.
Спасибо за внимание!
Если у вас есть мысли или вопросы по теме статьи — делитесь в комментариях.
Числовое форматирование Excel - это одна из самых недооценённых пользователями функций программы.
В этой заметке я расскажу о принципах создания пользовательского числового формата , и по ходу мы разберем примеры, которые продемонстрируют всю мощь этой, на первый взгляд, не особо нужной функции Excel.
Но перед тем, как приступим, хочу обратить ваше внимание на один важный момент.
Суть числового форматирования
Не раз сталкивался с заблуждением пользователей, которые считают, что числовое форматирование как-то влияет на значение, находящееся в ячейке.
ВАЖНО понимать, что это НЕ ТАК!
Числовое форматирование влияет только на отображение значения. Само значение при этом никак не изменяется.
То есть если в ячейке находится цифра с двумя знаками после запятой (1), а мы с помощью числового форматирования уберем дробную часть (2), то визуально кажется, что значение было округлено, но это не так. В ячейке по-прежнему хранится значение с дробной частью (3) и в формулах будет использоваться именно оно.
Числовой формат позволяет изменить внешний вид и сделать данные более аккуратными и легко воспринимаемыми.
Ну а теперь поговорим о синтаксисе числового форматирования.
Синтаксис числового форматирования
Обычно числовое форматирование применяется следующим образом: выделяется ячейка или диапазон ячеек (1), к которому нужно числовой формат применить, и затем из выпадающего списка (2) выбирается готовый шаблон (3).
Как правило, пользователи не интересуются, как именно этот шаблон работает, а просто применяют готовые форматы в своей работе.
Также все готовые шаблоны доступны и в окне Формат ячеек .
Для создания формата нужно понимать синтаксис написания кода, то есть нужно изучить правила, по которым он формируется и узнать, какие вспомогательные символы в коде используются.
Именно об этом я и расскажу в этой заметке.
Итак, код числового формата в общем случае состоит из четырех разделов:
ПОЛОЖИТЕЛЬНЫЕ; ОТРИЦАТЕЛЬНЫЕ; НОЛЬ; ТЕКСТ
Сначала указывается правило форматирования положительных значений, затем отрицательных, далее нулевых и в конце текста. Все четыре раздела отделяются друг от друга точкой с запятой.
При этом необязательно в коде должны присутствовать все четыре раздела.
Если задан только один раздел, то он будет применяться ко всем числовым значениям вне зависимости от их величины и знака. Если указано два раздела, то первый будет использоваться для форматирования положительных числовых значений и нулей, а второй для отрицательных чисел. И только указание всех четырех разделов в коде позволит форматировать текстовые значения.
Код каждого раздела числового формата задается с помощью специальных символов. Давайте их рассмотрим на примерах и пока будем использовать самый простой шаблон - зададим только один раздел для всех числовых значений.
Символ 0 в числовом формате
Давайте рассмотрим на примере.
В первом столбце находятся значения в общем формате, а в последующих к этим же значениями применяется пользовательский формат ( шаблон в заголовке).
Сформируем последовательность 1, 2, 3, . Пусть в ячейке A2 введен первый элемент последовательности - значение 1 . В ячейку А3 , вводим формулу =А2+1 и копируем ее в ячейки ниже (см. файл примера ).
Так как в формуле мы сослались на ячейку выше с помощью относительной ссылки , то EXCEL при копировании вниз модифицирует вышеуказанную формулу в =А3+1 , затем в =А4+1 и т.д., тем самым формируя числовую последовательность 2, 3, 4, .
Если последовательность нужно сформировать в строке, то формулу нужно вводить в ячейку B2 и копировать ее нужно не вниз, а вправо.
Чтобы сформировать последовательность нечетных чисел вида 1, 3, 7, . необходимо изменить формулу в ячейке А3 на =А2+2 . Чтобы сформировать последовательность 100, 200, 300, . необходимо изменить формулу на =А2+100 , а в ячейку А2 ввести 100.
Другим вариантом создания последовательности 1, 2, 3, . является использование формулы =СТРОКА()-СТРОКА($A$1) (если первый элемент последовательности располагается в строке 2 ). Формула =СТРОКА(A2)-СТРОКА($A$1) позволяет создать вертикальную последовательность, в случае если ее первый элемент последовательности располагается в любой строке. Тот же результат дают формулы =ЧСТРОК($A$1:A1) , =СТРОКА(A1) и =СТРОКА(H1) . Формула =СТОЛБЕЦ(B1)-СТОЛБЕЦ($A$1) создает последовательность, размещенную горизонтально. Тот же результат дают формулы =ЧИСЛСТОЛБ($A$1:A1) , =СТОЛБЕЦ(A1) .
Чтобы сформировать последовательность I, II, III, IV , . начиная с ячейки А2 , введем в А2 формулу =РИМСКОЕ(СТРОКА()-СТРОКА($A$1))
Сформированная последовательность, строго говоря, не является числовой, т.к. функция РИМСКОЕ() возвращает текст. Таким образом, сложить, например, числа I+IV в прямую не получится.
Другим видом числовой последовательности в текстовом формате является, например, последовательность вида 00-01 , 00-02, . Чтобы начать нумерованный список с кода 00-01 , введите формулу =ТЕКСТ(СТРОКА(A1);"00-00") в первую ячейку диапазона и перетащите маркер заполнения в конец диапазона.
Выше были приведены примеры арифметических последовательностей. Некоторые другие виды последовательностей можно также сформировать формулами. Например, последовательность n2+1 ((n в степени 2) +1) создадим формулой =(СТРОКА()-СТРОКА($A$1))^2+1 начиная с ячейки А2 .
Создадим последовательность с повторами вида 1, 1, 1, 2, 2, 2. Это можно сделать формулой =ЦЕЛОЕ((ЧСТРОК(A$2:A2)-1)/3+1) . С помощью формулы =ЦЕЛОЕ((ЧСТРОК(A$2:A2)-1)/4+1)*2 получим последовательность 2, 2, 2, 2, 4, 4, 4, 4. , т.е. последовательность из четных чисел. Формула =ЦЕЛОЕ((ЧСТРОК(A$2:A2)-1)/4+1)*2-1 даст последовательность 1, 1, 1, 1, 3, 3, 3, 3, .
Примечание . Для выделения повторов использовано Условное форматирование .
Формула =ОСТАТ(ЧСТРОК(A$2:A2)-1;4)+1 даст последовательность 1, 2, 3, 4, 1, 2, 3, 4, . Это пример последовательности с периодически повторяющимися элементами.
Используем клавишу CTRL
Пусть, как и в предыдущем примере, в ячейку A2 введено значение 1 . Выделим ячейку A2 . Удерживая клавишу CTRL , скопируем Маркером заполнения (при этом над курсором появится маленький плюсик), значение из A 2 в ячейки ниже. Получим последовательность чисел 1, 2, 3, 4 …
ВНИМАНИЕ! Если на листе часть строк скрыта с помощью фильтра , то этот подход и остальные, приведенные ниже, работать не будут. Чтобы разрешить нумерацию строк с использованием клавиши CTRL , выделите любую ячейку с заголовком фильтра и дважды нажмите CTRL + SHIFT + L (сбросьте фильтр).
Используем правую клавишу мыши
Пусть в ячейку A2 введено значение 1 . Выделим ячейку A2 . Удерживая правую клавишу мыши, скопируем Маркером заполнения , значение из A2 в ячейки ниже. После того, как отпустим правую клавишу мыши появится контекстное меню, в котором нужно выбрать пункт Заполнить . Получим последовательность чисел 1, 2, 3, 4 …
Используем начало последовательности
Если начало последовательности уже задано (т.е. задан первый элемент и шаг последовательности), то создать последовательность 1, 2, 3, . можно следующим образом:
Получаем результат как в предыдущем случае. Если в ячейке А3 введено значение 3 , т.е. задан шаг последовательности равный 2, то мы получим последовательность нечетных чисел.
Создадим последовательность вида 1, 2, 3, 1, 2, 3. для этого введем в первые три ячейки значения 1, 2, 3, затем маркером заполнения , удерживая клавишу CTRL , скопируем значения вниз.
Использование инструмента Прогрессия
Воспользуемся стандартным средством EXCEL для создания прогрессий, в то числе и арифметических.
- вводим в ячейку А2 значение 1 ;
- выделяем диапазон A2:А6 , в котором будут содержаться элементы последовательности;
- вызываем инструмент Прогрессия ( Главная/ Редактирование/ Заполнить/ Прогрессия. ), в появившемся окне нажимаем ОК.
Использование в работе : Подходы для создания числовых последовательностей можно использовать для нумерации строк , сортировки списка с числами , разнесения значений по столбцам и строкам .
СОВЕТ: О текстовых последовательностях вида первый, второй, . 1), 2), 3), . можно прочитать в статье Текстовые последовательности . О последовательностях значений в формате дат (и времени) вида 01.01.09, 01.02.09, 01.03.09, . янв, апр, июл, . пн, вт, ср, . можно прочитать в статье Последовательности дат и времен . О массивах значений, содержащих последовательности конечной длины, используемых в формулах массива , читайте в статье Массив значений (или константа массива или массив констант) .
Написать число или сумму прописью кажется довольно тривиальной задачей. Но если это нужно делать десятки раз на дню и с множеством документов, нужна автоматизация. Идеальным решением была бы формула в Excel.
Можно ли сделать такую формулу? Можно! А главное, что вам делать ничего не придется (только скопировать), потому что я уже сделал её за вас! Но обо всем по порядку.
Сопоставление чисел и их эквивалентов прописью
Если попытаться описать алгоритм, который будет учитывать все нюансы, окажется, что все на так просто, как кажется:
- в зависимости от того, на какую цифру заканчивается число, его падеж и число могут быть:
- единственное, именительный (двадцать один рубль)
- единственное, родительный (двадцать два/три/четыре рубля)
- множественное, родительный (двадцать, двадцать пять/шесть/семь/восемь/девять рублей)
И это далеко не все нюансы. Есть ли варианты, которые их все учитывают?
Число прописью в Excel — формула
Большинство руководств в интернете предлагают решения на VBA и надстройки. Также предлагаются формулы на основе именованных диапазонов.
В случае с именованными диапазонами — нужно будет создавать их заново (а их около десятка!).
![]()
Число прописью в Excel — примеры сопоставления
Возможна ли формула без применения VBA, макросов и надстроек?
Черт возьми, да! Я долго размышлял, прежде чем ее сделать, но мне это удалось.
Вот эта формула! Вставьте ее в любую ячейку, кроме A1, т.к. она преобразует значение этой самой ячейки:
Тройной клик по формуле выделит ее целиком.
Формула работает с любыми целыми числами от 1 до миллиарда, не включая его. Если дано число с десятичными знаками после запятой, формула берет только целую часть, иными словами, округляет всегда вниз.
Если вы хотите округлять такие числа обычным округлением по математическим правилам, замените процедурой найти и заменить во всей формуле
Сумма прописью с копейками в Excel
Для записи суммы прописью с копейками по всем канонам, нужно соблюсти ряд условий:
![Пример написания суммы прописью с копейками в Excel]()
Сумма прописью с копейками в Excel — пример таблицы соответствия суммы и текста
Трудно в это поверить, но и такая формула тоже существует. Аналогично, без применения VBA, макросов и надстроек.
Формула огромна, но замечательно справляется с любыми суммами до миллиарда. Работать будет в любой версии Excel от 2007 и старше — в 2003 не была доступна такая глубина вложений функций.
Вот и она, просто скопируйте ее, и она преобразует числовое значение ячейки A1 в сумму прописью с копейками.
В русских рублях, все буквы строчные
Формула выводит сумму в рублях, копейки оставляет цифрами. Если регистр первой буквы не важен, можно использовать её.
В русских рублях, первая буква заглавная
Стоит захотеть сделать первую букву заглавной — и формула становится в два раза длиннее, т.к. в Excel нет простой функции, которая бы позволила сделать только первую букву ячейки заглавной — нужны функции ЛЕВСИМВ и ПСТР, обращающиеся каждая к одной и той же огромной формуле.
Тройной клик по формуле выделит ее целиком.
В русских рублях с копейками прописью
Если нужно, чтобы копейки тоже были написаны прописью, то формула будет еще чуть длиннее, такой:
В белорусских рублях
Тройной клик по формуле выделит ее целиком.
Сумма прописью в Excel — UDF-функции
В ней для решения задачи преобразования числа и суммы в их прописной вариант есть 2 лаконичные функции:
Они доступны абсолютно бесплатно безо всяких ограничений в любой версии надстройки, приобретать полную версию не обязательно.
Единственное ограничение — эти формулы будут работать только на тех компьютерах, где установлена надстройка.
Сумма и число прописью в Гугл таблицах
Прелесть формул выше в том, что они с минимальными модификациями портируются в Google Spreadsheets, где их замечательно можно использовать.
Ниже пример таблицы с указанными формулами:
Посмотреть сам файл и скопировать к себе можно по ссылке:
Сумма прописью — формула для Openoffice Calc
У формулы выше были проблемы с импортом в Openoffice, т.к. тот в отличие от Excel (2007 и старше), поддерживает максимум 42 уровня вложенности в формулах, а не 64.
Поэтому пришлось полностью переделать логику формулы и сделать ее сложной формулой массивов.
Так она выглядит в Excel:
А так — после импорта в Openoffice Calc:
Макрос
Для тех, кто ценит время и не готов прибегать к функциям, в надстройке для Excel !SEMTools доступны макросы преобразования в 1 клик.
Функционал доступен в полной версии надстройки.
Перевод чисел прописью в другие падежи
Зачастую после получения числа или суммы прописью нужно указать его в другом падеже.
У этой записи 75 комментариев
Нужно доработать функцию для другой валюты? Нашли недочеты или есть предложения? Оставляйте в комментариях!
Отличное решение! Совершенно неожиданное.
Вы избавились от кучи именованных диапазонов.
А с числом 16 Вы специально сделали провокацию?
Большое спасибо за что-то свежее.Не специально, но идея неплохая :)
Спасибо, поправил.А можно в этой формуле сделать, так чтобы сумма начиналась с маленькой буквы и вместо 00 копеек было просто ноль копеек?
Можно!
Формула даже станет существенно короче, т.к. изначально формула предполагается с маленькой буквы, и весь синтаксис до копеек повторяется только ради одной заглавной буквы вначале.большое спасибо, очень помогло. для долларов США и евро было бы отлично!)
Читайте также:
