Как сложить и вычесть процент от числа или к числу в Excel
Одно из часто выполняемых задач в Excel – это вычитание или добавление процентов к значению. Например, при увеличении цен на товары обычно повышается изначальная цена на определенный процент. При предоставлении клиентам скидки, уменьшается цена на несколько процентов.
Как сложить число и процент в Excel
Ниже на рисунке наглядно показан пример: как добавить или отнять процент в Excel. Для этого используется простая формула. В ячейке D2 к цене товара 1 добавлено 10% процентов. А в ячейке D5 Клиенту А предоставлена скидка в размере 20%.
Чтобы добавить к значению заданный процент, достаточно умножить это же значение на 1+заданый % в ячейке с процентным форматом для отображения. Например, на рисунке увеличена цена товара 1 на 10%. Для этого сначала добавляем число 1+10% в результате получаем 110%. После чего умножаем исходную цену 100$ на 110%. Вычисленная цена таким способом равна 110$. Чтобы в первую очередь была выполнена операция сложения единицы с процентами, следует взять ее в скобки.
Программа Excel всегда придерживается правил порядка математических операций – действие умножения всегда первое! Наличие скобок повышает приоритет выполнения операции сложения перед умножением.
Как вычесть процент от числа в Excel
Чтобы уменьшить значение на заданный процент, следует снова умножить исходное значение на число 1, только на этот раз -20%. В примере на рисунке клиенту А предоставлена скидка -20%. Сначала вычитаем 20% от единицы и в результате получаем 80%. После чего следует умножить изначальную цену 1000$ (до скидки) на 80%. Вычисленная таким методом новая цена по акции составила 800$. Как и в предыдущем примере, чтобы в первую очередь была выполнена операция вычитания процентов от единицы, следует взять ее в скобки.
Если в формулах сложения и вычитания процентов не использовать скобки, тогда результаты их вычислений будут ошибочны!
- Создать таблицу
- Форматирование
- Функции Excel
- Формулы и диапазоны
- Фильтр и сортировка
- Диаграммы и графики
- Сводные таблицы
- Печать документов
- Базы данных и XML
- Возможности Excel
- Настройки параметры
- Уроки Excel
- Макросы VBA
- Скачать примеры
покупка
Если у вас есть данные, отформатированные в процентах, и вы не хотите, чтобы в таблице отображался 0%, как вы можете скрыть этот нулевой процент только в Excel? Этот учебник расскажет вам о методе.
Скрыть ноль процентов
Чтобы скрыть ноль процентов, нужно всего 3 шага.
1. Выберите ячейки процентов и щелкните правой кнопкой мыши, чтобы выбрать Формат ячеек из контекстного меню.
2. В появившемся диалоговом окне щелкните Номер регистрации > На заказ, и введите это 0%; — 0%; «» в Тип текстовое окно.
3. Нажмите OK. Теперь вы можете видеть, что нулевой процент скрыт.
 |
 |
 |
Функции: Если процент имеет десятичное число, вы можете ввести это 0.00%; — 0.00%; «» в Тип текстовое окно.
Внимание: Если вы измените форматирование ячейки на другое, нули больше не будут скрыты.
Относительная статья:
Функция ТЕКСТ
С помощью функции ТЕКСТ можно изменить представление числа, применив к нему форматирование с кодами форматов. Это полезно в ситуации, когда нужно отобразить числа в удобочитаемом виде либо объединить их с текстом или символами.
Примечание: Функция TEXT преобразует числа в текст, что может затруднить ссылку в последующих вычислениях. Лучше сохранить исходное значение в одной ячейке, а затем использовать функцию TEXT в другой ячейке. Затем, если потребуется создать другие формулы, всегда ссылайтесь на исходное значение, а не на результат функции ТЕКСТ.
Технические сведения
ТЕКСТ(значение; формат)
Аргументы функции ТЕКСТ описаны ниже.
Имя аргумента
Числовое значение, которое нужно преобразовать в текст.
Текстовая строка, определяющая формат, который требуется применить к указанному значению.
Общие сведения
Самая простая функция ТЕКСТ означает следующее:
- =ТЕКСТ(значение, которое нужно отформатировать; «код формата, который требуется применить»)
Ниже приведены популярные примеры, которые вы можете скопировать прямо в Excel, чтобы поэкспериментировать самостоятельно. Обратите внимание: коды форматов заключены в кавычки.
=ТЕКСТ(1234,567;«# ##0,00 ₽»)
Денежный формат с разделителем групп разрядов и двумя разрядами дробной части, например: 1 234,57 ₽. Обратите внимание: Excel округляет значение до двух разрядов дробной части.
=ТЕКСТ(СЕГОДНЯ();«ДД.ММ.ГГ»)
Сегодняшняя дата в формате ДД/ММ/ГГ, например: 14.03.12
=ТЕКСТ(СЕГОДНЯ();«ДДДД»)
Сегодняшний день недели, например: понедельник
=ТЕКСТ(ТДАТА();«ЧЧ:ММ»)
Текущее время, например: 13:29
=ТЕКСТ(0,285;«0,0 %»)
Процентный формат, например: 28,5 %
Дробный формат, например: 4 1/3
=СЖПРОБЕЛЫ(ТЕКСТ(0,34;«# ?/?»))
Дробный формат, например: 1/3 Обратите внимание: функция СЖПРОБЕЛЫ используется для удаления начального пробела перед дробной частью.
=ТЕКСТ(12200000;«0,00E+00»)
Экспоненциальное представление, например: 1,22E+07
Дополнительный формат (номер телефона), например: (123) 456-7898
=ТЕКСТ(1234;«0000000»)
Добавление нулей в начале, например: 0001234
=ТЕКСТ(123456;«##0° 00′ 00»»)
Пользовательский формат (широта или долгота), например: 12° 34′ 56»
Примечание: Функцию ТЕКСТ можно использовать для изменения форматирования, но это не единственный способ. Вы можете изменить формат без формулы, нажав клавиши CTRL+1 (или
+1 на компьютере Mac), а затем выберите нужный формат в диалоговом окне Формат ячеек > число .
Скачивание образцов
Предлагаем скачать книгу, в которой содержатся все примеры применения функции ТЕКСТ из этой статьи и несколько других. Вы можете воспользоваться ими или создать собственные коды форматов для функции ТЕКСТ.
Другие доступные коды форматов
С помощью диалогового окна Формат ячеек можно найти другие доступные коды форматирования:
-
Нажмите клавиши CTRL+1 (
Коды форматов по категориям
Ниже приведены некоторые примеры того, как можно применить различные числовые форматы к значениям с помощью диалогового окна Формат ячеек , а затем с помощью параметра Custom скопировать эти коды форматирования в функцию TEXT .
Выбор числового формата
- Выбор числового формата
- Нули в начале
- Разделитель групп разрядов.
- Числовые, денежные и финансовые форматы
- Даты
- Значения времени
- Проценты
- Дроби
- Экспоненциальное представление
- Дополнительные форматы
Почему программа Excel удаляет нули в начале?
Excel воспринимает последовательность цифр, введенную в ячейку, как число, а не как цифровой код, например артикул или номер SKU. Чтобы сохранить нули в начале последовательностей цифр, перед вставкой или вводом значений примените к соответствующему диапазону ячеек текстовый формат. Выделите столбец или диапазон, в который нужно поместить значения, нажмите клавиши CTRL+1, чтобы открыть диалоговое окно Формат ячеек, и выберите на вкладке Число пункт Текстовый. Теперь программа Excel не будет удалять нули в начале.
Если вы уже ввели данные и Excel удалил начальные нули, вы можете снова добавить их с помощью функции ТЕКСТ. Создайте ссылку на верхнюю ячейку со значениями и используйте формат =ТЕКСТ(значение;»00000″), где число нулей представляет нужное количество символов. Затем скопируйте функцию и примените ее к остальной части диапазона.
Если по какой-либо причине потребуется преобразовать текстовые значения обратно в числа, можно умножить их на 1 (например: =D4*1) или воспользоваться двойным унарным оператором (—), например: =—D4.
В Excel группы разрядов разделяются пробелом, если код формата содержит пробел, окруженный знаками номера (#) или нулями. Например, если используется код формата «# ###», число 12200000 отображается как 12 200 000.
Пробел после заполнителя цифры задает деление числа на 1000. Например, если используется код формата «# ###,0 «, число 12200000 отображается в Excel как 12 200,0.
- Разделитель групп разрядов зависит от региональных параметров. Для России это пробел, но в других странах и регионах может использоваться запятая или точка.
- Разделитель групп разрядов можно применять в числовых, денежных и финансовых форматах.
Ниже показаны примеры стандартных числовых (только с разделителем групп разрядов и десятичными знаками), денежных и финансовых форматов. В денежном формате можно добавить нужное обозначение денежной единицы, и значения будут выровнены по нему. В финансовом формате символ рубля располагается в ячейке справа от значения (если выбрать обозначение доллара США, то эти символы будут выровнены по левому краю ячеек, а значения — по правому). Обратите внимание на разницу между кодами денежных и финансовых форматов: в финансовых форматах для отделения символа денежной единицы от значения используется звездочка (*).
Чтобы получить код формата для определенной денежной единицы, сначала нажмите клавиши CTRL+1 (на компьютере Mac —
+1) и выберите нужный формат, а затем в раскрывающемся списке Обозначение выберите символ.
После этого в разделе Числовые форматы слева выберите пункт (все форматы) и скопируйте код формата вместе с обозначением денежной единицы.
Примечание: Функция ТЕКСТ не поддерживает форматирование с помощью цвета. Если скопировать в диалоговом окне «Формат ячеек» код формата, в котором используется цвет, например «# ##0,00 ₽;[Красный]# ##0,00 ₽», то функция ТЕКСТ воспримет его, но цвет отображаться не будет.
Способ отображения дат можно изменять, используя сочетания символов «Д» (для дня), «М» (для месяца) и «Г» (для года).
В функции ТЕКСТ коды форматов используются без учета регистра, поэтому допустимы символы «М» и «м», «Д» и «д», «Г» и «г».
Минда советует.
Если вы предоставляете общий доступ к файлам и отчетам Excel пользователям из разных стран, скорее всего, потребуется, чтобы они были на разных языках. Минда Триси (Mynda Treacy), Excel MVP, предлагает отличное решение этой задачи в своей статье Отображение дат Excel на разных языках (на английском). В ней также есть пример книги, который вы можете скачать.
Способ отображения времени можно изменить с помощью сочетаний символов «Ч» (для часов), «М» (для минут) и «С» (для секунд). Кроме того, для представления времени в 12-часовом формате можно использовать символы «AM/PM».
Если не указывать символы «AM/PM», время будет отображаться в 24-часовом формате.
В функции ТЕКСТ коды форматов используются без учета регистра, поэтому допустимы символы «Ч» и «ч», «М» и «м», «С» и «с», «AM/PM» и «am/pm».
Для отображения десятичных значений можно использовать процентные (%) форматы.
Десятичные числа можно отображать в виде дробей, используя коды форматов вида «?/?».
Экспоненциальное представление — это способ отображения значения в виде десятичного числа от 1 до 10, умноженного на 10 в некоторой степени. Этот формат часто используется для краткого отображения больших чисел.
В Excel доступны четыре дополнительных формата:
- «Почтовый индекс» («00000»);
- «Индекс + 4» («00000-0000»);
- «Номер телефона» («[
- «Табельный номер» («000-00-0000»).
Дополнительные форматы зависят от региональных параметров. Если же дополнительные форматы недоступны для вашего региона или не подходят для ваших нужд, вы можете создать собственный формат, выбрав в диалоговом окне Формат ячеек пункт (все форматы).
Типичный сценарий
Функция ТЕКСТ редко используется сама по себе, а чаще применяется в сочетании с чем-то еще. Предположим, что вы хотите объединить текст и числовое значение, например, чтобы получить строку «Отчет напечатан 14.03.12» или «Еженедельный доход: 66 348,72 ₽». Такие строки можно ввести вручную, но суть в том, что Excel может сделать это за вас. К сожалению, при объединении текста и форматированных чисел, например дат, значений времени, денежных сумм и т. п., Excel убирает форматирование, так как неизвестно, в каком виде нужно их отобразить. Здесь пригодится функция ТЕКСТ, ведь с ее помощью можно принудительно отформатировать числа, задав нужный код формата, например «ДД.ММ.ГГГГ» для дат.
В примере ниже показано, что происходит, если попытаться объединить текст и число, не применяя функцию ТЕКСТ. Мы используем амперсанд (&) для сцепления текстовой строки, пробела (» «) и значения: =A2&» «&B2.
Вы видите, что значение даты, взятое из ячейки B2, не отформатировано. В следующем примере показано, как применить нужное форматирование с помощью функции ТЕКСТ.
Вот обновленная формула:
- ячейка C2:=A2&» «&ТЕКСТ(B2;»дд.мм.гггг») — формат даты.
Вопросы и ответы
Как преобразовать числа в текст, например 123 в «сто двадцать три»?
К сожалению, вы не можете сделать это с помощью функции TEXT; необходимо использовать код Visual Basic для приложений (VBA). Следующая ссылка содержит метод: Как преобразовать числовое значение в слова на английском языке в Excel.
Можно ли изменить регистр текста?
Да, вы можете использовать функции ПРОПИСН, СТРОЧН и ПРОПНАЧ. Например, формула =ПРОПИСН(«привет») возвращает результат «ПРИВЕТ».
Можно ли с помощью функции ТЕКСТ добавить новую строку (разрыв строки) в ячейке, как при нажатии клавиш ALT+ВВОД?
Да, но для этого необходимо выполнить несколько действий. Сначала выберите ячейку или ячейки, в которых это произойдет, и нажмите клавиши CTRL+1, чтобы открыть диалоговое окно Формат > ячеек, а затем элемент управления Выравнивание > текст > проверка параметр Обтекать текстом. После этого добавьте в функцию ТЕКСТ код ASCII СИМВОЛ(10) там, где нужен разрыв строки. Вам может потребоваться настроить ширину столбца, чтобы добиться нужного выравнивания.
В этом примере использована формула =»Сегодня: «&СИМВОЛ(10)&ТЕКСТ(СЕГОДНЯ();»ДД.ММ.ГГ»).
Почему Excel преобразует введенные числа во что-то вроде «1,22E+07»?
Это называется научной нотацией, и Excel автоматически преобразует числа, превышающие 12 цифр, если ячейки форматируются как общие, и 15 цифр, если ячейки отформатированы как число. Если вам нужно ввести длинные числовые строки, но не нужно их преобразовывать, отформатируйте ячейки, о которой идет речь, как Текст , прежде чем вводить или вставлять значения в Excel.
Даты на разных языках
Минда советует.
Если вы предоставляете общий доступ к файлам и отчетам Excel пользователям из разных стран, скорее всего, потребуется, чтобы они были на разных языках. Минда Триси (Mynda Treacy), Excel MVP, предлагает отличное решение этой задачи в своей статье Отображение дат Excel на разных языках (на английском). В ней также есть пример книги, который вы можете скачать.
3 метода установки данных в процентном формате на вашем листе
Существует 3 различных метода изменения формата. Но вам также может понадобиться обратить внимание на некоторые детали. На этом изображении ниже нам нужно вручную ввести темпы роста объема продаж.
Есть много данных, которые нам нужно ввести. Таким образом, если Excel может автоматически преобразовать форму в процентный формат, задача может быть намного проще.
Способ 1: используйте комбинацию клавиш быстрого доступа
Комбинация клавиш быстрого доступа очень проста в использовании. Здесь комбинация клавиш быстрого доступа — «Ctrl + Shift +%». Фактически, это то же самое, что нажать кнопку на панели инструментов.
Но использование этой комбинации перед вводом числа или после ввода даст разные результаты. И мы обсудим это подробно.
(1) Используйте после ввода номера
- Введите число «0.12» в поле tarполучить сотовый.
- Теперь нажмите «Ввод» или щелкните другую ячейку на листе.
- Затем щелкните ячейку еще раз, чтобы поместить курсор в ячейку.
Вышеупомянутые два шага выйдут из режима редактирования. В противном случае сочетание клавиш не сработает. Здесь вы также можете заметить, что категория ячейки «Общая».
- Теперь нажмите клавиши «Ctrl+Shift+%» на клавиатуре. И знак процента появится сразу. Число в ячейке станет «12%».
В это время категория ячейки превращается в «Процент». Поэтому, если вам нужно использовать комбинацию клавиш после ввода чисел, вам нужно разделить число на 100.
С другой стороны, когда речь идет о десятичных дробях, все будет иначе. Excel автоматически округлит число, введенное в ячейку.
Число будет умножено на 100 после того, как вы воспользуетесь клавишами быстрого доступа. Если в числе нет десятичной точки, Excel добавит знак процента напрямую.
В противном случае, если первое число после запятой больше или равно 5, последнее число перед запятой будет плюс 1. Или не будет плюс 1.
Таким образом, результат, который появится в ячейке, будет целое число все время. И вы можете проверить исходную десятичную форму в строке формул. Если у вас все еще есть трудности с правилом, вы можете обратиться к некоторым примерам в таблице ниже:
| Число, которое вы вводите | Используйте комбинацию клавиш быстрого доступа после ввода номера. | Число умножается на 100 | Результат в ячейке | Результат в строке формул | Описание |
| 2 | 200 | 200% | 200% | \ | |
| 0.54 | 54 | 54% | 54% | \ | |
| 0.543 | 54.3 | 54% | 54.3% | Первое число после запятой меньше 5. | |
| 0.545 | 54.5 | 55% | 54.5% | Первое число после запятой равно 5. | |
| 0.0081 | 0.81 | 1% | 0.81% | Первое число после запятой больше 5. | |
| 0.0021 | 0.21 | 0% | 0.21% | Первое число после запятой меньше 5. |
(2) Используйте перед вводом номера.
- Щелкните ячейку. На этом этапе категория ячейки по-прежнему «Общая».
- Теперь нажмите «Ctrl + Shift +%». После этого действия категория ячейки изменится на «Процент».
- Затем введите в ячейку число «12». Excel автоматически добавит знак процента после числа.
Следовательно, в этом порядке вам не нужно делить 100 перед вводом числа.
В этом порядке Excel также будет округлять число, если вы вводите десятичное число. Здесь число, которое вы вводите, не будет умножаться на 100. Таким образом, если вы введете целое число, изменений не будет. Что касается десятичного числа, первое число после запятой больше или равно 5, последнее число перед запятой будет плюс 1. И оно не будет плюс 1, если число после запятой меньше 5. Вы также можете обратиться к в таблице ниже приведены некоторые примеры.
| Используйте клавиши быстрого доступа перед вводом номера | Число, которое вы вводите | Результат в ячейке | Результат в строке формул | Описание |
| 12 | 12% | 12% | \ | |
| 5.2 | 5% | 5.2% | Первое число после запятой меньше 5. | |
| 5.8 | 6% | 5.8% | Первое число после запятой больше 5. | |
| 0.81 | 1% | 0.81% | Первое число после запятой больше 5. | |
| 0.23 | 0% | 0.23% | Первое число после запятой меньше 5. |
Выше приведены различия в порядке использования сочетания клавиш. Вы можете применить любой из двух заказов в соответствии с вашими потребностями. Кроме того, вам нужно быть более осторожным с функцией округления в Excel.
(3) Отключить автоматический ввод процентов
В Excel есть функция автоматического ввода процентов.
- Нажмите «Файл» на ленте.
- А затем нажмите «Параметры».
- В окне «Параметры Excel» нажмите «Дополнительно».
- А затем снимите флажок «Включить автоматический ввод процентов».
- Далее нажмите «ОК». Сейчас эта функция отключена.
Теперь, если вы используете комбинацию клавиш быстрого доступа после введя номер, вы получите тот же результат, как если бы эта функция все еще была доступна.
Но если вы используете другой порядок, результат будет другим. Введенное вами число также будет умножаться на 100 перед добавлением знака процента. Кроме того, Excel также будет округлять число. Таким образом, Excel выдаст тот же результат, что и порядок использования сочетания клавиш после ввода числа.
Способ 2: изменить числовой формат на панели инструментов
В этом методе вы можете изменить числовой формат на панели инструментов.
(1) Измените формат перед вводом номера
- Введите в ячейку число 0.12.
- Затем щелкните маленькую стрелку числового формата.
- А затем выберите вариант «Процент» в раскрывающемся списке. Таким образом, формат изменится. Число, которое вы видите в ячейке, будет 12.00%.
Здесь число умножится на 100, а также добавит к числу два десятичных знака. Если в числах, умножающих на 2, больше двух знаков после запятой, Excel округлит число.
Если третий десятичный знак больше или равен 5, второй десятичный знак добавит 1. В противном случае он не добавит 1. Вы можете обратиться к таблице ниже:
| Число, которое вы вводите | Выберите опцию | Число, которое умножает 100 | Результат в ячейке | Результат в строке формул | Описание |
| 12 | 1200 | 1200.00% | 1200.00% | \ | |
| 7.23 | 723 | 723.00% | 723.00% | \ | |
| 0.7234 | 72.34 | 72.34% | 72.34% | \ | |
| 0.72343 | 72.343 | 72.34% | 72.343% | Число третьего десятичного знака меньше 5. | |
| 0.72345 | 72.345 | 72.35% | 72.345% | Число третьего десятичного знака равно 5. |
Кроме того, даже если вы снимите флажок «Включить автоматический ввод процентов», вы получите тот же результат.
(2) Измените формат после ввода номера
Теперь вы меняете порядок, и здесь число не будет умножаться на 100. А что касается правила округления числа, то оно тоже зависит от третьего десятичного числа. В таблице ниже также перечислены некоторые примеры.
| Выберите опцию | Число, которое вы вводите | Результат в ячейке | Результат в строке формул | Описание |
| 12 | 12.00% | 12.00% | \ | |
| 2.1 | 2.10% | 2.10% | \ | |
| 2.13 | 2.13% | 2.13% | \ | |
| 2.134 | 2.13% | 2.134% | Число третьего десятичного знака меньше 5. | |
| 2.135 | 2.14% | 2.135% | Число третьего десятичного знака равно 5. |
Кроме того, если опция «Включить автоматический ввод процентов» отключена, вы получите тот же результат, что и порядок изменения формата перед вводом числа.
Способ 3: изменить категорию ячейки в ячейках формата
Этот способ имеет некоторое сходство с предыдущим. Но место для его изменения другое.
Предложения ниже показывают шаги для изменения категории.
- Сначала щелкните маленькую стрелку в части «Выравнивание».
- В окне «Формат ячейки» отметьте вкладку «Число».
- А затем выберите опцию «Процент».
- А затем установить десятичную точку в «Десятичные знаки».
- После этого нажмите «ОК». Таким образом, категория ячейки изменится на «Процент».
Вы можете изменить категорию до ввода номера или после входа. Предположим, что десятичный знак равен 2, результат точно такой же, как и при изменении формата числа. Этот же результат также включает изменение «Включить автоматический ввод процентов». Кроме того, если десятичный разряд отличается, например, 3, Excel будет ссылаться на четвертое число при округлении числа. Таким образом, вы узнаете, как здесь работает десятичный разряд.
Вышеупомянутые три метода дадут разные результаты, если вы измените порядок или измените параметр «Включить автоматический ввод процентов». Вы также можете попробовать и использовать most подходящий метод в вашем рабочем листе.
Не паникуйте, когда Excel повреждается
Иногда вы столкнетесь с повреждением файла Excel. В этот момент не паникуйте. Действия, которые вы выполняете в начале, определят, сможет ли файл сохраниться. Вы можете немедленно связаться с нашими экспертами по Excel и получить Исправление Excel инструмент для восстановления Excel. Если вы попытаетесь отремонтировать его самостоятельно, не выяснив причину, никто не может гарантировать результат.