Главные секреты числового форматирования в excel. часть 1

Экспоненциальный формат ячеек Excel

Экспоненциальный формат используется для отображения числа в экспоненциальном представлении. При этом происходит замена части числа на E + n, где E обозначает экспоненциальное представление, то есть умножение предшествующего числа на 10 в степени n.

Например, экспоненциальный формат с двумя знаками после запятой отображает 12345678901 как 1,23E + 10, то есть 1,23, умноженное на 10 в 10й степени. В его настройках можно задать количество отображаемых знаков после запятой.

Как задать в ячейке экспоненциальный формат?

Этот формат отображает число в экспоненциальном представлении, заменяя часть числа на E + n, где E обозначает экспоненциальное представление, то есть умножение предшествующего числа на 10 в степени n.

Например, экспоненциальный формат с двумя знаками после запятой отображает 12345678901 как 1,23E+10, то есть 1,23, умноженное на 10 в 10й степени. В настройках можно задать количество отображаемых знаков после запятой.

При этом экспоненциальный формат числа не влияет на фактическое значение, используемое в Excel при расчетах. Это фактическое значение отображается в строке формул.

Как сделать, чтобы данные в таблице Эксель округлялись

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

Кнопки в ленте

Наиболее простой метод, как сделать, чтобы Excel снова округлял, выделить одну / несколько ячеек, а после пройти такие шаги:

1. Войдите в «Главная».
2. Жмите на ленте кнопку «Увеличить / уменьшить разрядность». В первом случае количество знаков увеличивается, а во втором уменьшается на одну цифру.

Округление через форматирование

Выше мы разобрали, как не округлять в Экселе с помощью форматирования. Этот же метод можно использовать для включения опции. Сделайте следующие шаги:

Выделите диапазон ячеек на листе, жмите правой кнопкой мышки и кликните «Формат …».

• Перейдите в раздел «Число». Если выбран не числовой вариант, выберите его. В ином случае сделать так, чтобы Эксель округлял, не получится.
• В «Число десятичных знаков» внесите нужные данные.

Задание точности расчетов

Рассмотренные выше методы в Эксель оказывают влияние только на внешнее отображение в Эксель, а при расчетах использовались только точные показатели (до 15-ти знаков). При необходимости можно поменять точность вычислений. Сделайте следующее:

Войдите в «Файл», а далее «Параметры».

• Найдите «Дополнительно», отыщите блок настроек «При пересчете этой книги».
• Поставьте отметку возле параметра «Задать точность как на экране».

После выполнения этой задачи система будет округлять величину и использовать ее для вычислений.

Изменение функции

Для изменения размера округления при расчете одной, двух или более ячеек без уменьшения точности расчетов нужно использовать функцию округления «ОКРУГЛ» и разные ее варианты. К наиболее популярным относится «ОКРУГЛ» или округление до указанного числа десятичных знаков, «ОКРУГЛВВЕРХ» — до ближайшего верхнего, а «ОКРУГЛВНИЗ» — до ближайшего нижнего. Также можно сделать, чтобы Эксель округлял с заданной точностью с помощью «ОКРУГЛТ», или до ближайшего четного / нечетного параметра — «ЧЕТН», «НЕЧЕТН». Интересующую функцию можно вводить как в ячейку, так и в строку.

Дополнительный метод

Можно выделить еще один метод, как сделать, чтобы Excel округлял числа. Он подходит для случаев, когда в таблице находятся параметры, требующие преобразования. Сделайте следующие шаги:

• Войдите во вкладку «Формулы».
• Жмите на «Математические».
• В появившемся списке выберите «ОКРУГЛ».
• В окне аргументов в поле «Число» введите вручную нужный параметр.
• Жмите по ячейке, где нужно, чтобы Эксель округлял цифры.
• Откройте окно аргументов в поле «Число разрядов».
• Укажите разрядность, до которой нужно сократить дробь.

Теперь вы знаете, как сделать, чтобы Excel не округлял, какие методы для этого существуют, и как вернуть нужные настройки обратно.

Изменение формата ячейки

Самый простой и быстрый способ преобразования текста в числа в Excel – это просто изменить форматирование ячейки из главного меню.

Сделать это:

1. Выберите все ячейки, которые вы хотите преобразовать. Вы можете выбрать весь столбец (не включая заголовок), если хотите преобразовать все ячейки в столбце.
2. Выберите меню «Главное» и в группе «Число» на ленте выберите раскрывающийся список с текстом в нем.

1. Вы увидите список форматов на выбор. Выберите Общие, чтобы преобразовать в числовой формат. Или вы можете выбрать Число, Валюта, Учет или Процент, если вы хотите, чтобы эти конкретные числовые форматы применялись к вашим числовым данным.

Geography (География)

В этом примере столбец A содержит ячейки с типом данных geography. Это означает, что значок »

Введите текст в ячейки. Если вы хотите, чтобы биржевая информация была указана в каждой ячейке, введите символ, название компании или название фонда. Если вы хотите использовать географические данные, введите название страны, края, территории или города в каждую ячейку.

Затем выделите ячейки.

Хотя это необязательно, рекомендуем создать таблицу Excel. Это упростит получение сведений из Интернета. Чтобы создать таблицу, перейдите в раздел вставка > таблице.

Выделив ячейки, перейдите на вкладку данные и выберите пункт акции или География.

Если в Excel обнаружено соответствие между текстом в ячейках и Интернет-источниками, текст будет преобразован в тип данных «биржевые или географические данные». Вы сможете определить то, что преобразование выполнено, по следующим значкам:

Выделите одну или несколько ячеек с типом данных, после чего появится кнопка Вставить данные

Нажмите кнопку Вставить данные еще раз, чтобы добавить другие поля. Если вы используете таблицу, вот как подсказка: введите имя поля в строке заголовка. Например, введите изменить в строке заголовка для Stocks, и на экране появится колонка изменить в столбце Цена.

Примечание: Если вы видите

Формат ячеек в Excel: Формат даты

В Excel можно создать формулу, вычисляющую разницу между значениями даты и времени, введенными в ячейки рабочего листа. Единственная проблема состоит в том, что Excel вычитает одну дату из другой или одно время из другого и автоматически преобразует результат вычислений в соответствующий числовой формат даты или времени. Например, если ввести в ячейку В4 15-8-19, в ячейку С4 — 15/4/19, а в ячейку D4 — формулу, вычисляющую количество дней между этими датами (=В4-С4), то получится результат 122 (количество дней). Это значение отображается с помощью формата Общий, назначенного ячейке D4. Учтите, что при создании формул, подсчитывающих разницу между двумя значениями времени на листе, ячейке результата следует назначить формат Общий. Предположим, что в ячейке В5 указано значение 8:00 AM, а в ячейке С5 — 4:00 РМ. Для вычисления разницы двух показаний времени в ячейке D5 определяется следующая формула: =С5-В5.

Результат вычисления формулы, который отображается в ячейке D5 в виде 8:00 AM, нужно преобразовать в формат Общий. В результате вместо значения 8:00 AM в ячейке D5 появится дробное значение 0,333333, которое представляет собой результат деления 8 (значение времени) на 24 (количество часов в сутках). Чтобы преобразовать это дробное число в эквивалентное количество часов, следует умножить его на 24, а к результату применить формат Общий.

голоса

Рейтинг статьи

Форматы данных вводимых в ячейки электронной таблицы

Диапазон ячеек A2:A7 заполните цифрой 2 и отформатируйте все ячейки так как показано выше на рисунке.

Решение задачи:

Какие возможности предоставляет диалоговое окно формат ячеек? Функции всех инструментов для форматирования, которые содержит закладка «Главная» можно найти в этом диалоговом окне (CTRL+1) и даже больше.

В ячейке A5 мы воспользовались финансовым форматом, но есть еще и денежный, их очень часто путают. Эти два формата отличаются способом отображения:

• финансовый формат при отображении чисел меньше чем 0 ставит минус с левой стороны ячейки, а денежный ставит минус перед числом;
• денежный формат по умолчанию отображает отрицательные значения красным цветом шрифта (например, введите в ячейку значение -2р. и денежный формат присвоится автоматически);
• в финансовом формате после сокращения валют добавляется 1 пробел при отображении значений.

Если нажать комбинацию горячих клавиш: CTRL+SHIFT+4, то ячейке присвоится денежный формат.

Что касается даты в ячейке A6, то здесь стоит упомянуть о правилах Excel. Формат даты считаются как последовательность дней от 1 января 1900 года. То есть если ячейка содержит значение – число 2, то это число в формате даты должно отображаться как 02.01.1900 и так далее.

Время для Excel – это значение чисел после запятой. Так как у нас в ячейке A7 целое число, время там отображено соответственно.

Пользовательский ЧИСЛОвой формат в EXCEL (через Формат ячеек)

В Excel имеется множество встроенных числовых форматов, но если ни один из них не удовлетворяет пользователя, то можно создать собственный числовой формат. Например, число -5,25 можно отобразить в виде дроби -5 1/4 или как (-)5,25 или 5,25- или, вообще в произвольном формате, например, ++(5)руб.###25коп. Рассмотрены также форматы денежных сумм, процентов и экспоненциального представления.

Для отображения числа можно использовать множество форматов. Согласно российским региональным стандартам ( Кнопка Пуск/ Панель Управления/ Язык и региональные стандарты ) число принято отображать в следующем формате: 123 456 789,00 (разряды разделяются пробелами, дробная часть отделяется запятой). В EXCEL формат отображения числа в ячейке можно придумать самому. Для этого существует соответствующий механизм – пользовательский формат. Каждой ячейке можно установить определенный числовой формат. Например, число 123 456 789,00 имеет формат: # ##0,00;-# ##0,00;0

Пользовательский числовой формат не влияет на вычисления, меняется лишь отображения числа в ячейке. Пользовательский формат можно ввести через диалоговое окно Формат ячеек , вкладка Число , ( все форматы ), нажав CTRL+1 . Сам формат вводите в поле Тип , предварительно все из него удалив.

Рассмотрим для начала упомянутый выше стандартный числовой формат # ##0,00;-# ##0,00;0 В дальнейшем научимся его изменять.

Точки с запятой разделяют части формата: формат для положительных значений; для отрицательных значений; для нуля. Для описания формата используют специальные символы.

• Символ решетка (#) означает любую цифру.
• Символ пробела в конструкции # ##0 определяет разряд (пробел показывает, что в разряде 3 цифры). В принципе можно было написать # ###, но нуль нужен для отображения 0, когда целая часть равна нулю и есть только дробная. Без нуля (т.е. # ###) число 0,33 будет отражаться как ,33.
• Следующие 3 символа ,00 (запятая и 00) определяют, как будет отображаться дробная часть. При вводе 3,333 будут отображаться 3,33; при вводе 3,3 – 3,30. Естественно, на вычисления это не повлияет.

Вторая часть формата – для отображения отрицательных чисел. Т.е. можно настроить разные форматы для отражения положительных и отрицательных чисел. Например, при формате # ##0,00;-###0;0 число 123456,3 будет отображаться как 123 456,30, а число -123456,3 как -123456. Если формата убрать минус, то отрицательные числа будут отображаться БЕЗ МИНУСА.

Третья часть формата – для отображения нуля. В принципе, вместо 0 можно указать любой символ или несколько символов (см. статью Отображение в MS EXCEL вместо 0 другого символа ).

Есть еще и 4 часть – она определяет вывод текста. Т.е. если в ячейку с форматом # ##0,00;-# ##0,00;0;»Вы ввели текст» ввести текстовое значение, то будет отображено Вы ввели текст .

Например, формат 0;\0;\0;\0 позволяет заменить все отрицательные, равные нулю и текстовые значения на 0. Все положительные числа будут отображены как целые числа (с обычным округлением).

В создаваемый числовой формат необязательно включать все части формата (раздела). Если заданы только два раздела, первый из них используется для положительных чисел и нулей, а второй — для отрицательных чисел. Если задан только один раздел, этот формат будут иметь все числа. Если требуется пропустить какой-либо раздел кода и использовать следующий за ним раздел, в коде необходимо оставить точку с запятой, которой завершается пропускаемый раздел.

Преобразование типов данных

Для преобразования типов данных предназначены следующие функции:

CBool( ) — преобразует число или строку в логический тип данных. Если преобразование невозможно возбуждается исключение. Пример:

CByte( ) — преобразует значение в число типа Byte . Если преобразование невозможно возбуждается исключение. Пример:

CInt( ) — преобразует значение в число типа Integer . Если преобразование невозможно возбуждается исключение. Пример:

CLng( ) — преобразует значение в число типа Long . Если преобразование невозможно возбуждается исключение. Пример:

CSng( ) — преобразует значение в число типа Single . Если преобразование невозможно возбуждается исключение. Пример:

CDbl( ) — преобразует значение в число типа Double . Если преобразование невозможно возбуждается исключение. Пример:

CCur( ) — преобразует значение в число типа Currency . Если преобразование невозможно возбуждается исключение. Пример:

CDec( ) — преобразует значение в число типа Decimal . Если преобразование невозможно возбуждается исключение. Пример:

CVar( ) — преобразует значение в тип Variant . Пример:

CStr( ) — преобразует значение в строку. Пример:

str( ) — преобразует число в строку. Первый символ в строке резервируется под знак числа. Если число является положительным, то первый символ будет пробелом, а если отрицательным — то первым символом будет знак минус

Обратите также внимание на то, что при выводе десятичный разделитель отображается в виде точки, а не в виде запятой, как это было при использовании функции CStr(). Пример:

CDate( ) — преобразует значение в тип Date . Если преобразование невозможно возбуждается исключение. Пример:

В качестве примера рассмотрим возможность сложения двух чисел, введенных пользователем. Как вы уже знаете, вводить данные позволяет функция InputBox() . Воспользуемся этой функцией для получения чисел от пользователя (листинг 2.4).

Листинг 2.4. Получение данных от пользователя

Результатом выполнения этого кода будет не число, а строка «512». Таким образом, следует запомнить, что функция InputBox() возвращает результат в виде строки. Чтобы просуммировать два числа, необходимо преобразовать строку в число (листинг 2.5).

Листинг 2.5. Преобразование строки в число

В этом случае мы получим число 17, как и должно быть. Однако если пользователь вместо числа введет строку, то программа завершится с фатальной ошибкой. Поэтому прежде чем преобразовывать значение в число необходимо проверить его на допустимость. Выполнить проверку возможности преобразования позволяют следующие функции:

IsNumeric( ) — возвращает значение True , если значение можно преобразовать в число, и False — в противном случае:

IsObject( ) — возвращает значение True , если переменная является объектной, и False — в противном случае:

IsEmpty( ) — возвращает значение True , если переменной не было присвоено значение, и False — в противном случае:

IsNull( ) — возвращает значение True , если переменная содержит значение Null , и False — в противном случае:

IsDate( ) — возвращает значение True , если значение может быть преобразовано в тип Date , и False — в противном случае:

Переделаем предыдущий пример (листинг 2.5) и используем функцию IsNumeric() для проверки возможности преобразования введенного пользователем значения в число (листинг 2.6). Код оформим в виде процедуры с названием Сумма() .

Листинг 2.6. Использование функции IsNumeric()

Статьи по Visual Basic for Applications (VBA)

Помощь сайту

ПАО Сбербанк: Счет: 40817810855006152256 Реквизиты банка: Наименование: СЕВЕРО-ЗАПАДНЫЙ БАНК ПАО СБЕРБАНК Корреспондентский счет: 30101810500000000653 БИК: 044030653 КПП: 784243001 ОКПО: 09171401 ОКОНХ: 96130 Скриншот реквизитов

Как в офисе.

Идем дальше по ленте. Группа Число (рис. 2.24). Если вы в этой группе нажмете кнопку , то опять откроется вкладка Число уже знакомого вам окна Формат ячеек (рис. 2.25).

Числа в Excel могут отображаться в различных форматах.

Я уже предвижу ужас по этому поводу: мало того, что числа (уже страшно), так еще и формат у них разный! Но вы не пугайтесь, мы со всем сейчас разберемся.

По умолчанию в Excel установлен общий формат ячеек. Это значит, что в любую чистую ячейку вы можете смело вводить любые данные: текст, числа или формулы. Вас поймут и данные запомнят. А сейчас я перечислю те особенные способы введения чисел, для которых Excel автоматически меняет формат.

• Если вы вводите число через запятую, Excel трактует это как десятичную дробь.
• При вводе чисел через точку, слеш (слеш — это косая палочка / или ), дефис в ячейке устанавливается формат даты и данные отображаются в виде даты.
• Если вы вводите число через двоеточие, автоматически устанавливается формат времени.
• Если вы хотите представить введенное число в процентном формате, добавьте после него знак %. При его выборе число, находящееся в ячейке, будет умножено на 100 и к нему добавится знак %.
• Чтобы представить число в денежном формате (в рублях), наберите после него р. В денежном формате к числу добавляется знак денежной единицы, а каждые три цифры числа отделяются друг от друга пробелами для вашего же удобства.
• Если вы отделите пробелами в числе по три цифры, например 36 258, 2 739, то переведете его в числовой формат. Он аналогичен денежному, за исключением того, что на экран не выводится знак денежной единицы.
• Если в ячейку нужно поместить простую дробь, например 3/5, или 11/2, поступите следующим образом. Введите целую часть дроби (для дробей меньше единицы нужно набрать ноль), затем нажмите Пробел и наберите дробную часть, используя слеш, например 1 4/5. В результате в ячейке будет установлен дробный формат и запись отобразится без изменений (не превращаясь в десятичную дробь).

Но изменить формат числа можно не только по-разному набирая числа, но и с помощью специальных инструментов. Во-первых, с помощью вкладки Число окна Формат ячеек (см. рис. 2.25). Во-вторых, используя меню кнопки Числовой формат , расположенной на ленте. Вы можете выбрать формат чисел для выделенной ячейки или диапазона. Новыми в этом списке являются экспоненциальный и текстовый форматы. Коротко о каждом из них.

Любое число можно представить в виде десятичной дроби, умноженной на 10 в степени, равной количеству знаков после запятой. Так, число 1230 можно записать в виде 1,23 × 103, а число 0,00015 как 1,5 × 10–4. Точно так же поступит и Excel, если вы выберете экспоненциальный формат. Только он использует разделитель Е в записи вместо ×10. Так, число 1230 в экспоненциальном формате будет выглядеть как 1,23Е+03, а число 0,00015 как 1,5Е-04.

• Кнопка переводит содержимое ячейки в финансовый формат. По умолчанию выбран российский рубль. Если вам нужен доллар или евро, щелкните на стрелке этой кнопки и выберите нужный вариант.
• Кнопка переводит содержимое выделенных ячеек в процентный формат.
• С помощью кнопки можно задать для ячеек числовой формат с разделителями-пробелами по три знака.
• С помощью кнопок Увеличить разрядность и Уменьшить разрядность можно увеличить или уменьшить количество знаков, отображаемых после запятой.

Будете вводить данные, обратите внимание: текст при вводе выравнивается по левому краю ячейки, а числовые данные — по правому. Есть такая хитрость, если вы хотите, чтоб какое-то число не принимало участия в расчетах, присвойте этой ячейке текстовый формат

Если вы зададите ячейке с числом текстовый формат, то она будет выровнена в ячейке как текст, а не как число

Есть такая хитрость, если вы хотите, чтоб какое-то число не принимало участия в расчетах, присвойте этой ячейке текстовый формат. Если вы зададите ячейке с числом текстовый формат, то она будет выровнена в ячейке как текст, а не как число.

Формат ячейки — одно из самых важных ее свойств. Если у ячейки задан формат текста, то, даже если вы будете вводить туда число, программа посчитает это число текстом! Если у ячейки формат даты, то, что бы вы туда ни вводили, программа будет считать это датой. Если вы боитесь запутаться, то пользуйтесь общим форматом.

Зачем все эти форматы? Зачем все эти сложности? Я думаю, вы смиритесь с ними, если я вам расскажу про автозаполнение.

Тип данных: Текстовые значения MS Excel

Текстовые значения – самый общий тип данных в экселе. По большому счету этим типом данных является всё, что программа не может определить как формулу или число. Любое значение помеченное как текстовое, используется excel “как есть” – его нельзя умножить, сложить или поделить как число, а также как-то изменить с помощью вычислений.

Все текстовые ячейки выравниваются по левому краю, все числовые по правому

Текстовые значения после их ввода в пустую ячейку автоматически выравниваются по левой границе ячейки. Именно так можно проще всего определить, что введенные вами данные воспринимаются MS Excel как текстовое значение.

Если ваше текстовое значение (к примеру фраза) не поместилось в ячейку, то оно размещается поверх соседних, расположенных справа ячеек при условии, что они свободны. Если в соседние ячейки введены данные, то длинная строка текста будет обрываться на границе занятой ячейки. Текст от этого, конечно же, никуда не денется и станет виден целиком, если вы поставите курсор на эту ячейку.

Если текст вылезает за пределы ячейки, имеет смысл “упаковать” его с помощью инструмента Перенос текста

Впрочем, вы можете воспользоваться инструментом “Перенести текст” (на вкладке “Главная” в группе “Выравнивание”) щелкнув его, когда выделена ячейка с длинным текстом, и тогда он поместится в неё целиком, просто “не влезающая” его часть будет перенесена на следующую строку.

Именованные выражения форматов

Использование текста в столбце

Еще один простой способ преобразования текста в числа в Excel – это одновременное преобразование целого столбца значений. Вы можете сделать это, используя функцию Text to Column.

1. Выберите весь столбец данных, которые вы хотите преобразовать из текста в числа.

1. Выберите «Данные» в меню, а затем выберите «Текст в столбцы» в разделе «Инструменты данных» на ленте.

1. В окне мастера оставьте выбранным разделитель по умолчанию и выберите Далее.

1. На следующей странице мастера оставьте выбранную вкладку по умолчанию и снова выберите Далее.

1. Наконец, на последней странице мастера убедитесь, что в разделе Формат данных столбца выбрано Общее. В поле «Место назначения» вы можете выбрать новый столбец, в который нужно перенести числовые данные, или просто оставить текущий выбранный столбец без изменений. Выберите Готово.

Теперь все ваши данные будут преобразованы в числовые значения, которые вы можете использовать в формулах и вычислениях Excel.

Примечание. Вы заметите, что фактическое форматирование ячейки не меняется с Текст на Общее, даже если сами значения теперь можно использовать в качестве чисел. Однако, если вы установите выходной столбец в новый столбец, вы заметите, что форматирование нового столбца установлено на Общие. Это только косметическая проблема, и она не влияет на поведение чисел в столбце в формате «Текст».

Смешанная ссылка в Excel

Смешанная ссылка — это ссылка вида $A1 или A$1. Знак доллара ($) служит фиксированием столбца или строки. Иными словами, если мы поставим $ перед буквой столбца (например, $B5), то ссылка не будет изменяться по столбцам, но будет изменяться по строкам (при протягивании формула сместится на $B5, $B6, $B7 и т.д.). Аналогично, если знак $ поставить перед номером строки (например, B$5), то ссылка не будет изменяться по строкам, но будет изменяться по столбцам (при перемещении формула сдвинется на C$5, D$5, E$5 и т.д.). Разберем использование смешанных ссылок на построении стандартной таблицы умножения:

столбца Aстроки 2$G$2*$A8

Формат ячеек в Excel: Числовой формат с разделителями

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

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

Формат с разделителями удобен для создания отчета о продажах за первый квартал

Обратите внимание на скриншоте ниже, на котором представлены стоимость работ за 1 м 2 , отформатированные с помощью данного формата. Для создания примера сначала был выделен диапазон B3:D16, а затем выполнен щелчок на кнопке Формат с разделителями, обозначенной тремя нулями

Эта кнопка находится в группе Число вкладки Главная.