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

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

Выделяем нужную область и используем для этих целей «Формат ячеек»:

В закладке «Число» выбираем (все форматы) и находим в окошке «Тип» выделенный формат:

Далее в окошке «Тип» ставим курсор после первого нуля перед запятой и делаем пробел. Как видим, число, указанное в образце, преобразилось:

Нажимаем ОК и получаем желаемый результат:

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

Большинство людей используют программу «Excel» для расчёта и аналитики экономических показателей процессов, происходящих на предприятии, то есть

фактически производят подсчет денег (рублей, гривен, долларов, тенге и т.д.).

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

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

Этот формат удобен, когда вычисляемые суммы невелики — пяти-шестиразрядные числа (до пяти-шести нулей).

Если расчеты ведутся глобальные, на сотни тысяч или миллионов рублей, например, приведенный выше формат неудобен.

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

Рассмотрим пример того, как преобразовать формат ячеек, чтобы числа отображались в виде тысяч.

Следует выполнить последовательность действий:

1) Выделить нужные ячейки.

2) Кликом правой кнопки по выделению вызвать контекстное меню.

3) Кликнуть по надписи: «Формат ячеек…»

4) В появившемся окне выбрать «Все форматы»

5) Записать в строку «Тип» нужный формат: 0,0 » тыс. руб»

Полученный результат перевода в тысячи

6) Нажать «Ок».

Разберем варианты перевода данных в тысячи, миллионы и другие порядки в 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 » тыс. руб.»», т.е. ставим пробел именно после записи формата числа (до добавления текстовой записи, а не после):

Скачать файл с примером.

Спасибо за внимание!
Если у вас есть мысли или вопросы по теме статьи — делитесь в комментариях.

Удачи вам и до скорых встреч на страницах блога TutorExcel.Ru!

Дата: 28 января 2017 Категория: Excel Поделиться, добавить в закладки или статью

Здравствуйте, друзья. В статье о форматировании данных в Эксель мы рассмотрели, какие форматы существуют и как их применить. Но что, если их недостаточно? Если нам нужно самим, вручную задать, как должны отображаться значения в ячейке? Конечно, есть решение! В этом посте учимся делать пользовательские форматы и значительно упрощать себе жизнь. Представьте, Вы абсолютно точно говорите программе, как отображать данные, а она безотказно выполняет. Это то, к чему мы с Вами стремимся – полный контроль и разумная автоматизация!
Если Вы внимательно читали статью о форматировании чисел, там я рассказывал все способы вызова меню «Формат ячеек». Самый простой из них – выделить ячейки для форматирования и нажать Ctrl+1. На вкладке «Число» Вы увидите все доступные форматы данных. Последней строкой здесь будет «все форматы». Как раз в этом пункте меню, в строке «Тип» записываются пользовательские форматы.

Как это работает? С помощью специальных символов, которые я опишу ниже, мы говорим программе, как отображать данные. Более того, мы можем через точку с запятой задать форматы для:
1. Положительного числа
2. Отрицательного числа
3. Нуля
4. Текста
В зависимости о того, сколько вариантов формата мы зададим, Эксель по-разному их трактует:
1. Задаем один формат – он применится для всех видов значений;
2. Два формата – первый применится для положительных и нулевых значений, второй – для отрицательных;
3. Три формата: первый – для отрицательных, второй – для положительных, третий – для нулевых;
4. Четыре формата: последовательно – положительные, отрицательные, нулевые, текст.
А вот какие служебные символы используются для форматирования данных:

Символ	Обозначение
Общий	Применяется формат «Общий»
#	Значащее числовое значение. Незначащие числа не отображаются
0 (ноль)	Значащее число. Несуществующие разряды заменяются нулями слева в старших разрядах
?	Значащее число. Несуществующие разряды заменяются пробелами слева в старших разрядах
,	Десятичный разделитель (запятая)
Пробел	Разделяет тысячные разряды
%	Проценты
	Повторяет символы, следующие за ним
*	Повторяет символы, следующие за ним до конца ячейки
_ (подчеркивание)	Пропуск на ширину следующего за ним символа
“”	Вставляет текст, заданный в кавычках
@	Текст
	Выберите цвет текста из списка: черный, синий, голубой, зеленый, фиолетовый, красный, белый, желтый
	n – номер цвета из палитры Excel

Так же, есть служебные символы форматирования дат:

Символ	Обозначение
ГГ	Год двузначным числом
ГГГГ	Год четырехзначным числом
М	Номер месяца без первого нуля
ММ	Номер месяца с первым нулем
МММ	Месяц аббревиатурой
ММММ	Месяц (полное название)
МММММ	Первая буква месяца
Д	День месяца без первого нуля
ДД	День месяца с первым нулем
ДДД	День недели аббревиатурой
ДДДД	День недели (полное название)
ч	Час без первого нуля
чч	Час с первым нулем
м	Минуты без первого нуля
мм	Минуты с первым нулем
с	Секунды без первого нуля
сс	Секунды с первым нулем

Здесь я привел самые полезные служебные символы, полный перечень можно найти в справке Excel.
Повторюсь, строка с форматом записывается в поле «Тип» меню «Формат ячеек», вкладка «Все форматы»
Комбинируя обычные и служебные символы между собой, можно добиться того вида, который нужен. Давайте посмотрим как это работает на нескольких примерах:

Интересным решением станет условный формат, когда способ форматирования зависит от содержимого ячейки. Это очень похоже на условное форматирование. Например, мне приходилось получать в работу прайс-лист, где в поле «Остатки» было указано не количество, а «много», «мало» и т.д. Отличный вариант, чтобы не дать точных цифр, а лишь подтолкнуть клиента сделать заказ побольше. Вот как это можно реализовать:

Пользовательские форматы – действительно простая тема, но очень мощная и полезная в работе. Напомню, что при установке формата, значение в ячейке не изменяется. Изменяется лишь способ его отображения.
Пользовательский формат сохраняется вместе с рабочей книгой. Чтобы перенести его в другую – переопределите такой же формат и в ней, либо скопируйте в нее одну из отформатированных ячеек.
Проведите пару экспериментов с пользовательскими форматами, и увидите, это может даже быть увлекательным! Часто мои знакомые и ученики пишут огромные форматы один раз, чтобы потом в разы быстрее получать читаемые результаты своих вычислений. Берите на вооружение этот подход и будьте еще на один шаг эффективнее!
Заканчиваю пост об условном форматировании, и обещаю следующую статью – о проверке данных. Для своих постоянных читателей, да и для новичков назову ее обязательной. Читайте и узнаете почему. До новых встреч!

Поделиться, добавить в закладки или статью