Разберем варианты перевода данных в тысячи, миллионы и другие порядки в Excel для сокращения записей (например, запись вида 123 456 руб. в 123,4 тыс. руб.).
Приветствую всех, уважаемые читатели блога TutorExcel.Ru.
Проблема наглядной визуализации данных всегда остро стоит перед любым пользователем программы Excel — сегодня мы остановимся на аспектах отображения чисел с выделением размерности.
К примеру, при виде числа 123 456 789 не сразу сообразишь что это за число, а уж при виде большой таблицы с подобными числами глаза точно устанут и таблица станет не читаемой.
Проблему с округлением чисел мы можем решить 2 способами:
- Формула. Непосредственное деление содержимого ячейки на требуемый делитель (тысяча, миллион, миллиард и т.д.);
- Формат. Изменения отображения ячейки с помощью настройки формата ячейки.
Начнем с более очевидного и простого варианта.
Способ 1. Формула
Чтобы перевести числа в тысячи, нужно эти числа разделить на тысячу.
Достаточно очевидный факт, тем не менее именно в простоте и заключается суть применения варианта с формулами.
Возьмем таблицу с произвольными числами (продублируем данные в соседний столбец B для более понятного пояснения):
Оставим столбец A неизменным, а работать будем со столбцом B.
Запишем в любую неиспользуемую ячейку число 1 000 (или 1 000 000, в зависимости от порядка, который мы хотим показать).
Копируем данную ячейку, а затем выделяем диапазон ячеек (ячейки B2:B10), где будем менять размерность.
Воспользуемся инструментом Специальная вставка — щелкаем правой кнопкой мыши по выделенному диапазону и выбираем Специальную вставку (либо воспользуемся сочетанием клавиш Ctrl+Alt+V):
В появившемся окошке в разделе Операция выбираем Разделить и щелкаем на ОК, в результате получаем (для наглядности я добавил еще 1 знак после запятой):
Минус данного способа в том, что операцией деления мы меняем содержимое ячеек, что не всегда удобно и возможно.
Теперь перейдем к рассмотрению более сложного, однако более гибкого и красивого варианта.
Способ 2. Формат ячеек
Помимо изменения самих ячеек, мы можем менять и их формат отображения.
К тому же, если перед нами стоит задача, например, перевести рубли в тысячи рублей, то мы сможем это сделать только данным способом (так как предыдущий способ меняет само число, но подпись оставляет неизменной).
Рассмотрим такую же таблицу, что и в примере выше, аналогично выделяем диапазон с данными (B2:B10), щелкаем правой кнопкой мыши и переходим в Формат Ячеек.
Во вкладке Число в разделе Числовые форматы выбираем (все форматы), в правой части окна показывается как будет отображаться ячейка, в моем случае маска отображения «# ##0».
Для того, чтобы сделать число в тысячах добавим 1 пробел в конце записи числа в маске — «# ##0 » (для миллиона 2 подряд пробела и т.д.), нажимаем ОК и получаем аналогичный результат.
Если нужно добавить в запись «тыс. руб.», то в формат маски записываем «# ##0,0 » тыс. руб.»», т.е. ставим пробел именно после записи формата числа (до добавления текстовой записи, а не после):
Скачать файл с примером.
Спасибо за внимание!
Если у вас есть мысли или вопросы по теме статьи — делитесь в комментариях.
Большинство людей используют программу «Excel» для расчёта и аналитики экономических показателей процессов, происходящих на предприятии, то есть
фактически производят подсчет денег (рублей, гривен, долларов, тенге и т.д.).
Для красивого и понятного отображения денежных величин в программе «Excel» есть специальный финансовый формат , который делит число на разряды и ставит после числа единицу измерения (наименование валюты, в которой произведен расчет).
Присвоить ячейке такой формат можно нажав соответствующую иконку в меню быстрого доступа либо выбрав в настройках формата ячейки финансовый формат.
Этот формат удобен, когда вычисляемые суммы невелики — пяти-шестиразрядные числа (до пяти-шести нулей).
Если расчеты ведутся глобальные, на сотни тысяч или миллионов рублей, например, приведенный выше формат неудобен.
Необходимо, чтобы числа отображались в виде тысяч или миллионов рублей.
Рассмотрим пример того, как преобразовать формат ячеек, чтобы числа отображались в виде тысяч.
Следует выполнить последовательность действий:
1) Выделить нужные ячейки.
2) Кликом правой кнопки по выделению вызвать контекстное меню.
3) Кликнуть по надписи: «Формат ячеек…»
4) В появившемся окне выбрать «Все форматы»
5) Записать в строку «Тип» нужный формат: 0,0 » тыс. руб»
Полученный результат перевода в тысячи
Если ячейка содержит значение больше 1000, то часто не требуется указывать точное значение, а достаточно указать число тысяч или миллионов, один-два знака после запятой и соответственно сокращение: тыс. или млн.
Если необходимо, чтобы вводимые числовые значения отображались в формате миллионов и тысяч с нотацией соответственно млн. или тыс. (например, 551 200 отобразить в виде 551,2 тыс., а 5 567 634 в виде 5,6 млн.), то для реализации этого существует 2 принципиально разных подхода.
Использование формулы
Очевидным подходом для получения желаемого результата является использование простой формулы и, соответственно, дополнительной ячейки, куда эта формула вводится (см. файл примера ):
=ЕСЛИ(A1
Результат формулы – текстовое значение. Надо также иметь ввиду, что из-за округления число 999 990,00, например, будет отражено в виде 1000,0 тыс.
В файле пример а также создана формула на отрицательных значений.
Пользовательский формат
Если нет желания использовать дополнительную ячейку, то с помощью пользовательского формата можно изменить, лишь отображение значения в ячейке, а не само значение.
Пользовательский формат вводим через диалоговое окно Формат ячеек.
- для вызова окна нажмите CTRL+1;
- выберите (все форматы),
- удалите все из поля Тип;
- в поле Тип введите [>1000000]#,0 » млн»;[>1000]#,0 » тыс.»;# ##0,00
Очень важно ввести правильно все пробелы, т.к. пробелы разделяют числовые разряды. Для корректного отображения ниже приведена формула с явным указанием местонахождения пробела (пробел заменен словом Space).
[>1000000]#,0SpaceSpace» млн»;[>1000]#,0Space» тыс.»;# ##0,00
При применении такого пользовательского формата будет произведено округление, т.е. число 1 290 000 будет отображено как 1,3 млн., а 1 240 000 как 1,2 млн.
