Как в дробные числа в excel

Microsoft Excel

трюки • приёмы • решения

Как в Excel правильно работать с дробями

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

Для ввода целого числа и дроби в ячейку оставьте пространство между целым числом и дробной частью. Например, для отображения 6 7/8 наберите 6 7/8 и нажмите клавишу Enter. При выборе ячейки на строке формул появляется значение 6.875 и запись в ячейке выглядит как дробь.

Если у вас есть только дробь (например, 7/8), то необходимо ввести нулевую целую часть таким образом: 0 1/8. В противном случае Excel, вероятно, предположит, что вы вводите дату. При выборе ячейки посмотрите на строку формул — там будет значение 0.125. В ячейке вы увидите 1/8.

Если числитель больше знаменателя, то Excel преобразует его в целое число и дробь. Например, если вы введете 0 65/8, редактор преобразует это число в 8 1/8. После ввода дроби откройте диалоговое окно Формат ячеек и посмотрите на числовой формат ячейки (рис. 36.1). Вы увидите, что Excel автоматически применил один из форматов дробных чисел.

Рис. 36.1. Список встроенных в Excel числовых форматов дробей

На рис. 36.2 показан лист, который отображает дробные данные. Значения в столбце С выражаются 4-ми, 8-ми и 16-ми долями, а значения в столбце D — все 16-ми долями.

Рис. 36.2. Отображение значений в виде дробей

Следующая строка числового формата показывает значение в терминах «дробных» долларов. Например, значение 154.87 отображается как 154 и 87/100 долларов: 0″ и «??/100″ долларов»

В следующем примере значение отображается 16-ми долями с кавычками справа. Эта строка формата полезна, когда вы имеете дело с дюймами (например, 2/16″): #??/16″

Отображение чисел в виде дробей

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

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

Выделите ячейки, которые нужно отформатировать.

На вкладке Главная нажмите кнопку вызова диалогового окна рядом с именем группы Число.

В списке Категория выберите пункт дробь.

В списке тип выберите нужный тип дроби.

Этот формат отображает 123,456 как

123 1/2, округление до ближайшего однозначного дробного значения

123 26/57, округление до ближайшего двойного десятичного дробного значения

Тройная дробь с тремя цифрами

123 57/125, округление до ближайшего тройного десятичного значения

Дробь как половины

Дробь по кварталам

Дробь как восьмые

Дробь в шестнадцать

Дробь как десятые значения

Дробь в сотых долях

Число в активной ячейке выделенной ячейки на листе отображается в поле образец , чтобы можно было просмотреть выбранные параметры форматирования чисел.

Советы по отображению дробей

После того как вы примените дробный формат к ячейке, десятичные числа и фактические дроби, которые вы вводите в этой ячейке, будут отображаться в виде дроби. Например, при вводе значений 0,5 или 1/2 в 1/2 , если ячейка была отформатирована с помощью дробной части, содержащей не более одной цифры.

Если к ячейке не применен ни один из дробей, то при вводе дробной части, например 1/2, будет использоваться формат даты. Чтобы отобразить его в виде дроби, примените Дробный формат, а затем повторно введите дробь.

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

Чтобы сбросить числовой формат, выберите пункт Общие в поле Категория (диалоговое окноФормат ячеек ) или в поле числовой формат (вкладкаГлавная, Группа число ). В ячейках с форматом Общий форматирование к числам не применяется.

Простые (или обыкновенные) дроби в Excel

В настоящее время в большинстве вычислений используются десятичные дроби. Однако в некоторых случаях вы можете столкнуться с простыми (обыкновенными) дробями.[1] Это может быть учебный пример, или диаметр вентиля (в долях дюйма), или, наконец, вес золотого слитка (в долях унции).

Excel предлагает 9 стандартных форматов обыкновенных дробей (рис. 1). Кроме того, вы можете создать пользовательский формат по своему усмотрению (об этом чуть позже).

Рис. 1. Стандартные форматы обыкновенных дробей в Excel

Скачать заметку в формате Word или pdf, также доступны примеры в формате Excel2013

Если вы уже ввели в ячейку число, например, 0,5, и хотите его отобразить в виде простой дроби, кликните на ячейке правой кнопкой мыши, выберите «Формат ячеек…», далее «Дробный» и «Простыми дробями» (рис. 2).

Рис. 2. Выбор формата «Дробный»

Excel так же, как и мы знает, что простые дроби используются редко, поэтому при попытке ввести в ячейку 1/2, Excel решит, что вы вводите дату и покажет «01.фев». Чтобы отобразить в ячейке дробь, введите 0, пробел, а затем 1/2. Аналогично вводится и составная дробь: наберите целую часть, пробел и дробную часть. Например, 1 2/3. Если вы выделите эту ячейку, в строке формул появится значение 1,666666666667, а запись в этой ячейке будет представлена как дробь.

Если числитель больше знаменателя, то Excel преобразует такую дробь в целую и дробную часть. Например, если вы введете 0 25/4, то Excel выразит это число как 6 1/4.

Если ни один из встроенных дробных форматов вам не подходит, создайте собственный! Например, на одном из форумов, я увидел вопрос, как отобразить 8/20? Наберите 0, пробел, 8/20. Не огорчайтесь, что Excel отобразил 2/5. Выделите ячейку, нажмите Ctrt+1, вызывая окно «Формат ячеек» (рис. 3а) и в открывшемся диалоговом окне в области «Числовые форматы» выберите «(все форматы)» (рис. 3б). А затем в области «Тип» отредактируйте шаблон формата, заменив # » » ??/?? на # » » ??/20.

Рис. 3. Пользовательский дробный формат

Несколько слов о коде формата # » » ??/20. Первый знак # – означает любое число, включающее одну или несколько цифр; ноль отображаться не будет (если хотите, чтобы ноль отображался, замените формат на следующий 0 » » ??/20). Далее идет пробел, заключенный с двух сторон в двойные кавычки » » ; вообще говоря, в ячейке будет отображаться любой набор символов, заключенный в шаблоне формата с двух сторон в двойные кавычки; мы еще раз столкнемся с этим ниже. Два знака вопроса означают, что допускается одна или две цифры. Ну а 20 в знаменателе будет отображаться в любом случае. Excel автоматически пересчитает дробь по основанию 20. Если при этом числитель дроби должен выражаться не целым числом, Excel округлит его до целых. Например, в выбранном выше формате, и 0,4, и 0,42 будут отображаться, как 8/20.

В контрактах с иностранными компаниями я сталкивался с записью центов в виде простой дроби; что-то типа: 20 и 7/100 долларов. Начните с ввода в ячейку: 20, «пробел», 1/100. Выберите формат «Сотыми долями» (рис. 4а). Далее выберите «(все форматы)», и дополните шаблон двумя словами: «и», «долларов» (рис. 4б).

Рис. 4. Формат для отображения долларов и центов

В заключительном примере значение отображается в 16-х долях; за числом следует знак дюймов (рис. 5). Поскольку кавычки являются служебным символом для шаблона, чтобы отобразить сами кавычки, нужно перед ними набрать косую черту: «

Рис. 5. Формат для отображения дюймов

Числа в Excel отображаются, как сотые десятичной дроби

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

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

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

Чтобы исправить ситуацию достаточно выполнить довольно несложную последовательность действий:

• Нажать на вкладку «Файл» (Кликнуть по ней левой кнопкой мыши)
• Выбрать кнопку «Параметры»;
• В появившемся окне выбрать вкладку «Дополнительно»;
• На вкладке дополнительно убрать флажок («Галочку») с надписи «Автоматическая вставка десятичной запятой»;
• Кликнуть по кнопке «ОК».

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

Функция ОТБР позволяет отбросить дробную часть в Excel

Функция ОТБР в Excel используется для отбрасывания дробной части числового значения, переданного в качестве первого аргумента, до определенного количества знаков после запятой, которые указаны в качестве второго аргумента, и возвращает усеченное целое число.

Как найти частное от деления с помощью функции ОТБР в Excel

Операция отбрасывания дробной части отличается от округления чисел, тем, что итоговое значение не увеличивается или уменьшается в соответствии с правилами округления чисел. Например, при округлении числа 10,9 до целого будет возвращено число 11. Функция ОТБР вернет число 10.

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

Вид таблицы данных:

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

С помощью функции ЕСЛИ выполняется проверка наличия остатка от деления (функция ОСТАТ). Если остаток от деления больше нуля, отбрасывается дробная часть с помощью функции ОТБР, иначе – просто выполняется деление числа на 5.

В результате вычисления формулы мы нашли частное от деления в Excel для исходных чисел, разделенных на 5.

Как преобразовать числа десятичной дроби в дату в Excel

Пример 2. В таблице имеется столбец с дробными числами (с разным количеством знаков после запятой). Это закодированная информация, из которой нам нужно раскодировать дату – число дня и месяца для 2020-го года по следующему алгоритму:

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

Вид таблицы данных:

Для расчета значений используем следующую формулу:

Для получения данных требуемого формата из кодов используем функцию ДАТА. Чтобы вычислить значение номера месяца с помощью функции ОТБР отбрасываем дробную часть оставляя целое число – номер месяца. Для получения номера дня нам следует вычесть от исходного числа его целое число. Таким образом получим дробную часть, для которой следует переместить запятую вправо на 2 знака с помощью умножением дробной части на 100.

Результат вычислений для всех ячеек:

Как видно, некоторые ячейки содержат код ошибки поскольку 54 и 33 превышают допустимое число дней в месяце. Или 13 и 114 превышают количество месяцев в году. Обработаем это исключение с помощью формулы с проверкой на ошибки:

12;(A2-ОТБР(A2))*100>ДЕНЬ(КОНМЕСЯЦА(ДАТА(2020;ОТБР(A2);1);0)));»нет даты в коде»;ДАТА(2020;ОТБР(A2);(A2-ОТБР(A2))*100))’ class=’formula’>

С помощью функции ИЛИ в первом аргументе ЕСЛИ проверяем значения при двух условиях:

1. Проверяем не превышает ли число 12 количество месяцев в исходном коде. Результат вычисления функции ОТБР возвращает целое число – если больше 12-ти, условие выполняется (число больше, чем количество месяцев в году), выводится сообщение об ошибке из второго аргумента ЕСЛИ, иначе – результат вычисления указанной выше формулы (в третьем аргументе функции ЕСЛИ).
2. Если число, полученное из первых двух цифр дробной части (сотая часть) больше, чем максимально возможное количество дней в этом месяце условие выполняется и получаем сообщение об ошибке «нет даты в коде». Иначе выполняется предыдущая формула раскодирования дат из исходных кодов.

Определим значения с помощью новой формулы и запишем в соседнем столбце:

Особенности использования функции ОТБР в Excel

Функция имеет следующую синтаксическую запись:

=ОТБР( число; [число_разрядов])

Описание аргументов функции:

• число – обязательный, принимает числовое значение, дробную часть которого требуется отбросить;
• [число_разрядов) – не обязательный, принимает числовое значение, указывающее на число знаков, до которых требуется произвести отсечение дробной части. Если явно не указан, принимается значение 0.

1. В качестве аргумента число могут быть указаны числа, текстовые представления числовых значений и данные логического типа. Если в качестве этого аргумента указано значение, которое не может быть преобразовано к данным числового типа, функция вернет код ошибки #ЗНАЧ!. Примеры допустимых значений: =ОТБР(“1,25”;0), ОТБР(ИСТИНА;0). Запись типа =ОТБР(“текст”;0) вернет код ошибки.
2. Если в качестве второго аргумента указано дробное число, оно будет отброшено. Например, =ОТБР(10,7345;2,5) и =ОТБР(10,7345;2) вернут одинаковый результат.
3. Если второй аргумент принимает значение, которое не может быть преобразовано к числовым данным, рассматриваемая функция вернет код ошибки #ЗНАЧ!
4. Функция может быть использована для округления чисел в меньшую сторону. Например, для округления числа 9999 до сотых используют следующую запись: =ОТБР(9999;-2). Результат вычисления – 9900. То есть, отрицательные значения второго аргумента указывают на операцию округления в меньшую сторону до определенного числа знаков перед запятой.
5. Для отбрасывания отрицательных чисел следует использовать функцию ОТБР вместо еще одной функции Excel – ЦЕЛОЕ, которая возвращает аналогичные результаты при работе с числами из диапазона положительных значений. Например, =ЦЕЛОЕ(-10,5) вернет ближайшее меньшее целое число (-11), в то время как =ОТБР(-10,5) просто отбросит дробную часть (-10).