Как в эксель изменить сумму
Обновлено: 06.07.2024
В этой статье описываются принципы отображения в Excel чисел в процентах, а также предлагаются примеры вычисления процентов на листах.
В этой статье
Как Excel обрабатывает проценты
Форматирование чисел в качестве процентов не вызывает сложностей, однако получаемые результаты могут зависеть от того, содержатся ли эти числа в книге.
Форматирование ячеек, уже содержащих числа. При применении процентного формата к существующим числам в книге Excel эти числа умножаются на 100. Например, если ячейка содержит число 10, оно будет умножено на 100, т. е. после применения процентного формата в ячейке будет отображаться значение 1000,00%, что может отличаться от ожидаемого результата. Чтобы процентные значения отображались точно, перед применением процентного формата следует убедиться в том, что числа вычислены как проценты и отображаются в десятичном формате. Проценты вычисляются с использованием формулы сумма /итог = процент. Например, если ячейка содержит формулу =10/100,результатом вычисления будет 0,1. Если применить к ячейке с числом 0,1 процентный формат, число будет правильно отображено как 10%. Дополнительные сведения о вычислении процентов см. в разделе Примеры вычисления процентов.
Форматирование пустых ячеек. Если применить процентный формат к пустым ячейкам, пересчет вводимых чисел в проценты выполняется по другим правилам. Числа, большие или равные 1, преобразуются в проценты по умолчанию, а числа, меньшие 1, для пересчета в проценты умножаются на 100. Например, если ввести 10 или 0,1, результат будет одинаковым — 10,00%. (Если отображение двух нулей после десятичной запятой не требуется, от них легко избавиться, выполнив действия, описанные в приведенной ниже процедуре.)
Отображение чисел в процентах
Чтобы быстро применить процентное форматирование к выбранным ячейкам, нажмите кнопку Процентный в группе Число на вкладке Главная или нажмите CTRL+SHIFT+%. Если необходимо более точно задать формат или изменить другие параметры форматирования для выделенного фрагмента, выполните действия, описанные ниже.
На вкладке Главная в группе Число щелкните значок рядом с полем Число, чтобы открыть диалоговое окно Формат ячеек.
В диалоговом окне Формат ячеек в списке Числовые форматы выберите пункт Процентный.
В поле Число десятичных знаков введите требуемое число знаков после запятой. Например, чтобы ячейка содержала значение 10%, а не 10,00%, введите в поле Число десятичных знаков0.
Советы по отображению процентов
Чтобы сбросить формат чисел в выбранных ячейках, в списке Числовые форматы выберите пункт Общий. Ячейки, к которым применен формат Общий, не имеют специального числового формата.
Если необходимо выделить отрицательные процентные значения (например, отображать их красным цветом), можно создать пользовательский числовой формат (диалоговое окно Формат ячеек, вкладка Число, категория (все форматы)). Формат должен иметь приблизительно следующий вид: 0,00%;[Красный]-0,00%. После применения этого формата к ячейкам положительные процентные значения будут отображаться цветом текста по умолчанию, а отрицательные — красным цветом. Часть формата после точки с запятой обозначает форматирование, которое применяется к отрицательным значениям.
Подобным образом можно настроить отображение отрицательных процентных значений в скобках, создав, например, следующий пользовательский формат: 0,00%_);(0,00%). Подробнее об этом см. в статье Создание и удаление пользовательских числовых форматов.
Для настройки отображения отрицательных процентных значений в книге можно также использовать условное форматирование (вкладка Главная, группа Стили, Условное форматирование). Созданное правило условного форматирования должно выглядеть примерно так же, как правило, приведенное в примере ниже. Данное правило предписывает приложению Excel применять формат (текст красного цвета) к ячейкам, значения в которых меньше нуля. Дополнительные сведения об условном форматировании см. в статье Добавление, изменение, поиск и удаление условного форматирования.
Примеры вычисления процентов
В данном разделе описаны некоторые простые способы вычисления процентов.
Пример 1. Увеличение или уменьшение числа на заданное количество процентов
Сценарий Если вы тратите на питание в среднем 25 долларов США в неделю и хотите сократить еженедельные расходы на питание на 25 %, сколько вы можете потратить? Или, если вы хотите увеличить недельный размер питания в 25 долларов США на 25 %, каков ваш новый недельный размер?
Если ячейка B2 — это расходы на питание, а C2 — процент, на который требуется сократить расходы, чтобы получить результат, введите в ячейку D2 формулу =B2*(1-C2).
В этой формуле 1 используется для представления 100 %. Подобным образом, если нужно увеличить расходы на определенный процент, следует ввести в ячейку D2 формулу =B2*(1+C2).
Пример 2. Вычисление значения на основе процента
Сценарий Если вы приобретаете компьютер за 800 рублей и у вас есть налог с продаж в размере 8,9 %, то сколько нужно платить за налог с продаж? В этом примере нужно найти 8,9 % от 800.
Если ячейка B2 — это цена, а C2 — налог с продаж, введите в ячейку D2 формулу =B2*C2, как показано ниже.
В этой формуле 800 умножается на 0,089 (соответствующий процент в десятичной форме), что дает величину налога с продаж, который необходимо уплатить.
Пример 3. Вычисление процента на основе двух значений
Пример 4. Вычисление значения на основе другого значения и процента
Сценарий Например, продажная цена футболки составляет 15 долларов США, что на 25 % меньше исходной цены. Какова исходная цена? В этом примере нужно найти 75 %, из которых число равно 15.
Если ячейка B2 — это отпускная цена, а в ячейке C2 содержится число 0,75 (т. е. 100 % за вычетом скидки в 25 % в десятичном формате), чтобы узнать первоначальную цену, введите в ячейку D2 формулу =B2/C2.
В этой формуле отпускная цена делится на проценты, что дает первоначальную цену.
Пример 5. Вычисление разницы между двумя числами и ее отображение в процентах
Сценарий Например, заработная плата вашего отдела в ноябре составляет 2 342 долларов США, а в декабре — 2500 рублей. Как меняется процентная доля заработной платы за эти два месяца? Для этого используйте операторы вычитания (-) и деления (/) в одной формуле.
Если ячейка B2 — это прибыль за ноябрь, а C2 — за декабрь, чтобы найти разницу, используйте в ячейке D2 формулу =(C2-B2)/ (B2).
Эта формула делит разницу между вторым и первым числами на значение первого числа, чтобы получить процентное изменение. (В показанном здесь примере разница отформатирована в процентах с двумя десятичных знаками.)
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Функция СУММ относится к категории: «Математические». Нажмите комбинацию горячих клавиш SHIFT+F3 для вызова мастера функций, и вы быстро ее там найдете.
Использование этой функции существенно расширяет возможности процесса суммирования значений ячеек в программе Excel. На практике ее рассмотрим возможности и настройки при суммировании нескольких диапазонов.
Как в таблице Excel посчитать сумму столбца?
Просуммируем значение ячеек A1, A2 и A3 с помощью функции суммирования. За одно и узнаем, для чего используется функция сумм.
- После ввода чисел перейдите на ячейку A4. На закладке инструментов «Главная» выберите инструмент «Сумма» в разделе «Редактирование» (или нажмите комбинацию горячих клавиш ALT+=).
- Диапазон ячеек распознан автоматически. Адреса ссылок уже введенные в параметры (A1:A3). Пользователю остается только нажать Enter.
В результате в ячейке A4 отображается результат вычисления. Саму функцию и ее параметры можно увидеть в строке формул.
Примечание. Вместо использования инструмента «Сумма» на главной панели, можно в ячейку A4 сразу ввести функцию с параметрами вручную. Результат будет одинаковый.
Поправка автоматического распознания диапазонов
Допустим, нам нужно просуммировать несколько диапазонов ячеек, как показано на рисунке:
- Перейдите в ячейку D1 и выберите инструмент «Сумма».
- Удерживая клавишу CTRL дополнительно выделите мышкой диапазон A2:B2 и ячейку A3.
- После выделения диапазонов нажмите Enter и в ячейке D4 сразу отобразиться результат суммирования значений ячеек всех диапазонов.
Обратите внимание на синтаксис в параметрах функции при выделении нескольких диапазонов. Они разделены между собой (;).
В параметрах функции СУММ могут содержаться:
- ссылки на отдельные ячейки;
- ссылки на диапазоны ячеек как смежные, так и несмежные;
- целые и дробные числа.
В параметрах функций все аргументы должны быть разделены точкой с запятой.
Для наглядного примера рассмотрим разные варианты суммирования значений ячеек, которые дают один и тот же результат. Для этого заполните ячейки A1, A2 и A3 числами 1, 2 и 3 соответственно. А диапазон ячеек B1:B5 заполните следующими формулами и функциями:
- =A1+A2+A3+5;
- =СУММ(A1:A3)+5;
- =СУММ(A1;A2;A3)+5;
- =СУММ(A1:A3;5);
- =СУММ(A1;A2;A3;5).
При любом варианте мы получаем один и тот же результат вычисления – число 11. Последовательно пройдите курсором от B1 и до B5. В каждой ячейке нажмите F2, чтобы увидеть цветную подсветку ссылок для более понятного анализа синтаксиса записи параметров функций.
Одновременное суммирование столбцов
В Excel можно одновременно просуммировать несколько смежных и несмежных столбцов.
Заполняете столбцы, так как показано на рисунке:
Под каждым столбцом автоматически добавилась функция СУММ(). Теперь в ячейках A4; B4 и D4 отображается результат суммирования каждого столбца. Это самый быстрый и удобный метод.
Примечание. Данная функция автоматически подставляет формат ячеек которых она суммирует.
Как вычислить сумму в таблице Excel быстро и просто? Попробуйте различные способы: взгляните на сумму выбранных ячеек в строке состояния, используйте автосумму для сложения всех или только нескольких отдельных ячеек, примените функцию СУММ или же преобразуйте ваш диапазон в «умную» таблицу для простоты расчетов. Все это мы покажем на примерах.
Как посчитать сумму одним кликом.
Предположим, имеется таблица, в которой записана выручка по нескольким отделам. Нужно быстро посмотреть общие продажи по одному из них.
Просто нажмите на букву столбца и взгляните на строку состояния Microsoft Excel, чтобы увидеть общее количество выбранных ячеек и сумму их значений.
Будучи очень быстрым, этот метод не позволяет копировать итоговые цифры – можно только посмотреть.
Что такое автосумма в таблице Excel?
Вторым по простоте способом является автосуммирование. Установите курсор в то место, где вы хотите увидеть расчет, а затем используйте кнопку «Автосумма» на вкладке Главная, или комбинацию клавиш ALT + =.
В активной ячейке появится функция СУММ со ссылками на смежные ячейки. Эксель попытается угадать, что же именно вы хотите сделать. Нажмите ENTER, чтобы закончить ввод формулы.
А если подходящих для расчета чисел рядом не окажется, то просто появится предложение самостоятельно указать область подсчета.
=СУММ()
Этот метод быстрый и позволяет автоматически получать и сохранять результат.
Кроме того, ALT + = можно просто нажать, чтобы быстро поставить формулу в ячейку Excel и не вводить ее руками. Согласитесь, этот небольшой хак в Экселе может сильно ускорить работу.
А если вам нужно быстро сосчитать итоги по вертикали и горизонтали, то здесь также может помочь автосуммирование. Посмотрите это короткое видео, чтобы узнать, как это сделать.
Под видео на всякий случай дано короткое пояснение.
Итак, выберите диапазон ячеек и дополнительно ряд пустых ячеек снизу и справа (то есть, В2:Е14 в приведенном примере).
Ввод функции СУММ вручную.
Самый традиционный способ создать формулу в MS Excel – ввести функцию с клавиатуры. Как обычно, выбираем нужную ячейку и вводим знак =. Затем начинаем набирать СУММ. По первой же букве "С" раскроется список доступных функций, из которых можно сразу выбрать нужную.
Теперь определите диапазон с числами, которые вы хотите сложить, при помощи мыши или же просто введите его с клавиатуры. Если у вас большая область данных для расчетов, то, конечно, руками указать её будет гораздо проще (например, B2:B300). Нажмите Enter.
Итак, ваш диапазон просуммирован, в выбранной ячейке вы видите результат. Думаю, вы понимаете, что совершенно то же нужно выполнить, чтобы вычислить его и по строке.
Правила использования формулы суммы в Excel (синтаксис):
У нее имеется один обязательный аргумент: число1.
Есть еще и необязательные аргументы (заключенные в квадратные скобки): [число2], ..
Они могут быть числами, ссылками на ячейки, диапазонами, массивами и константами в любой комбинации. Допускается обрабатывать до 255 отдельных аргументов.
К примеру, в выражении =СУММ(D2:D13) только один аргумент - ссылка на ячейки D2:D13.
Кстати, в этом случае также имеется возможность воспользоваться автосуммированием.
Накопительное значение, или нарастающий итог.
Задача: По прошествии каждого месяца нужно автоматически определять прибыль, заработанную с начала года. Каждый месяц она будет меняться в зависимости от достигнутого результата.
Посмотрите это небольшое видео, а далее поясним все наши действия.
В ячейке С2 записываем формулу
Обратите внимание, что мы использовали в первом адресе абсолютную ссылку на строку 2. То есть, при копировании ячейки с формулой мы всегда будем начинать со 2-й строки.
Устанавливаем курсор в правый нижний угол ячейки и «протаскиваем» до С13. В результате наша формула будет скопирована по всему выделению. И при этом начальный адрес B$2 не изменится. К примеру, в С13 это выглядит так: =СУММ(B$2:B13).
Как видите, нарастающий итог то увеличивается, то уменьшается в зависимости от результата прошедшего месяца.
Использование для проверки введенных значений.
Чтобы разрешить только значения, которые не превышают установленный порог, вы можете использовать проверку данных с пользовательской формулой, основанной на функции СУММ.
Посмотрите это видео, в котором используется инструмент «Проверка данных».
В условии проверки используем формулу
Правила проверки данных запускаются, как только пользователь добавляет или изменяет значение ячейки.
В этом случае нам нужно условие, которое возвращает ЛОЖЬ до тех пор, пока затраты в смете меньше 3500. Мы используем функцию сложения для обработки фиксированного диапазона, а затем просто сравниваем результат с 3500.
Подобное же условие допускается использовать и в условном форматировании, когда ячейки с числами могут быть закрашены красным цветом, если их общий итог превысит установленный порог. Более подробно об условном форматировании читайте -
Надеемся, что теперь посчитать сумму области данных или же отдельных ячеек вам будет гораздо проще.
Формула ПРОМЕЖУТОЧНЫЕ ИТОГИ — основные функции с примерами. - В статье объясняются особенности функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ в Excel и показано, как использовать формулы промежуточных итогов для суммирования данных в видимых ячейках. В предыдущей статье мы обсудили автоматический способ вставки промежуточных… Промежуточные итоги в Excel - В руководстве объясняется, как использовать инструмент промежуточных итогов Excel для автоматического суммирования, подсчета или усреднения различных групп ячеек. Вы также узнаете, как отображать или скрывать детали промежуточных итогов, копировать только строки… Как посчитать количество пустых и непустых ячеек в Excel - Если ваша задача - заставить Excel подсчитывать пустые ячейки на листе, прочтите эту статью, чтобы найти 3 способа для этого. Узнайте, как искать и выбирать среди них нужные с помощью стандартных… Как сравнить два столбца на совпадения и различия - На прочтение этой статьи у вас уйдет около 10 минут, а в следующие 5 минут (или даже быстрее) вы легко сравните два столбца Excel на наличие дубликатов и выделите найденные… Функция СЧЁТЕСЛИМН в Excel с несколькими условиями — объясняем на примерах. - В этом руководстве объясняется, как использовать функцию СЧЕТЕСЛИМН с несколькими критериями в Excel на основе логики И и ИЛИ. Вы найдете примеры для разных типов данных - числа, даты, текст,… СЧЕТЕСЛИ в Excel - примеры функции с одним и несколькими условиями - В этой статье мы сосредоточимся на функции Excel СЧЕТЕСЛИ (COUNTIF в английском варианте), которая предназначена для подсчета ячеек с определённым условием. Сначала мы кратко рассмотрим синтаксис и общее использование, а затем я… Функция СУММЕСЛИМН - как суммировать ячейки в Excel, когда много условий? - В этом руководстве объясняется различие между функциями СУММЕСЛИ (SUMIF) и СУММЕСЛИМН (SUMIFS) с точки зрения их синтаксиса и использования, а также приводятся примеры формул для суммирования значений с несколькими критериями… 7 примеров использования формулы СУММЕСЛИ в Excel с несколькими условиями - В таблицах Excel можно не просто находить сумму чисел, но и делать это в зависимости от заранее определённых критериев отбора. Мы рассмотрим, как правильно применить функцию СУММЕСЛИ (Sumif) в таблицах…
В Microsoft Excel очень просто отображать значения в виде процентов. Чтобы применить этот формат к конкретной ячейке или к области данных, выберите их, а затем нажмите кнопку «Процентный стиль» в группе «Число» на вкладке «Главная»:
Еще более быстрый способ – нажать комбинацию клавиш Ctrl + Shift +% (Excel будет напоминать вам об этой возможности всплывающей подсказкой каждый раз, когда вы наводите курсор на кнопку «Процентный стиль»).
Хотя для форматирования чисел в виде процентов в Excel требуется всего лишь один щелчок мыши, результаты могут различаться в зависимости от того, делаете ли вы это применительно к существующим числам или к пустым ячейкам.
Форматирование существующих значений.
Когда вы применяете формат «Процент» к ячейкам, которые уже содержат числа, Excel сразу умножает эти числа на 100 и добавляет в конце знак (%). С точки зрения программы, это правильный подход, поскольку 1% – это одна сотая часть.
Однако этот способ не всегда работает корректно. Например, у вас в ячейке A1 записано число 45, и вы примените к нему процентный формат. В результате вы получите 4500%, а не 45%, как могли бы ожидать.
Возможные обходные пути:
- Перед применением процентного формата делайте предварительные вычисления. Например, если исходные числа находятся в столбце A, вы можете ввести формулу =A2/100 в B2, а затем скопировать ее вниз по столбцу. Затем выделите весь столбец B и примните к нему процентный стиль. Вы получите примерно такой результат:
Теперь вы можете заменить формулы значениями в столбце B, скопировать их обратно в столбец A и удалить столбец B, если он вам больше не нужен. Для этого используйте Специальная вставка – Значения. Быстрые клавиши: сначала Ctrl+C , затем для вставки Ctrl+Alt+V .
- Если вы хотите применить процентное форматирование только к нескольким числам, вы можете ввести число в десятичной форме прямо в ячейку. Например, чтобы в ячейке A2 было 15%, введите 0,15 и затем примените формат.
Применение процентного формата к пустым ячейкам
Microsoft Excel ведет себя по-другому, когда вы вводите числа в пустые ячейки, предварительно отформатированные как процент:
- Любое число, равное или больше 1, автоматически преобразуется в процент. Например, 2 превращается в 2%, 20 - в 20%, дробное число 7,15 в 7,15% и так далее.
- Числа меньше 1 без предшествующего нуля в целой части автоматически умножаются на 100. Например, если вы введете .2 в ячейку в процентном формате, вы увидите в этой ячейке 20%. Однако, если вы запишете 0.2 в этой же ячейке, то 0.2% будет отображаться именно так, как и должно быть.
Отображение чисел в процентах при их вводе
Если вы введете 20% (со знаком %) прямо в ячейку, Эксель поймет, что именно вы вводите, и автоматически применит к ней процентное форматирование.
Важное замечание. Применяя формат процента, имейте в виду, что это не более чем визуальное представление реального значения, хранящегося в ячейке. Базовое значение всегда хранится в десятичном виде.
Другими словами, 20% сохраняется как 0,2, 2% на самом деле 0,02, 0,2% равно 0,002 и т.д. При выполнении вычислений Excel всегда имеет дело с десятичными значениями. Помните об этом факте, когда ссылаетесь на ячейки с процентным форматом в формулах.
Чтобы увидеть реальное значение, скрытое под форматированием, щелкните ячейку правой кнопкой мыши, выберите Формат ячеек (или нажмите Ctrl + 1 ) и посмотрите в поле Образец на вкладке Число.
Советы по отображению процентов в Excel
Отображение процентов в Excel кажется одной из самых простых задач, верно? Но опытные пользователи знают, что путь к цели почти никогда не бывает гладким :)
1. Десятичные знаки.
При применении этого форматирования к числам Excel отображает их округлёнными без десятичных знаков. Это может вызвать некоторую путаницу. Например, мы выше рассматривали, как установили процентный формат в пустой ячейке, а затем вводили в нее 0.2. Что мы там видели? В моем случае я увидел 0%, хотя я точно знаю, что это должно быть 0,2%. И к этому нужно быть готовым.
Чтобы увидеть фактическое значение вместо округленного числа, вам просто нужно увеличить количество отображаемых десятичных разрядов. Для этого откройте диалоговое окно Формат ячеек, нажав Ctrl + 1 . Убедитесь, что выбран числовой формат «Процент», и укажите желаемое количество разрядов в поле «Десятичные знаки».
Когда закончите, нажмите кнопку ОК, чтобы сохранить настройки.
Кроме того, вы можете управлять количеством отображаемых десятичных знаков, щелкнув на ленте значки «Увеличить разрядность» или «Уменьшить разрядность»:
2. Отрицательные числа.
Если вы хотите выделить отрицательные проценты особым способом, например красным цветом, вы можете создать собственный числовой формат. Откройте диалоговое окно Формат ячеек и перейдите на раздел > Все форматы. Введите один из ниже форматов в поле Тип:
0.00%; [Красный] -0.00% – закрасить отрицательные числа красным цветом и отображать 2 десятичных знака.
0%; [Красный] -0% – изобразить отрицательные значения красным без десятичных знаков.
Как выделить отрицательные проценты с помощью условного форматирования.
По сравнению с предыдущим методом, условное форматирование Excel более универсально и позволяет отображать отрицательные проценты, например уменьшение на сколько-то процентов, в любом формате по вашему выбору.
Самый быстрый способ создать правило условного форматирования для отрицательных чисел – это щелкнуть Условное форматирование> Правила выделения ячеек> Меньше чем и поставить 0 в поле «Форматировать ячейки меньше»:
Затем вы выбираете один из вариантов форматирования из раскрывающегося списка справа или щелкаете Пользовательский. в конце списка, чтобы настроить свой вариант.
Дополнительные сведения о создании правил условного форматирования см. в разделе Как использовать условное форматирование в Excel .
Вот как можно работать с процентным форматом в Excel. Надеюсь, эти знания помогут вам избежать головной боли в будущем. Следите за обновлениями и благодарим за чтение!
Как умножить число на процент и прибавить проценты - Ранее мы уже научились считать проценты в Excel. Рассмотрим несколько случаев, когда известная нам величина процента помогает рассчитать различные числовые значения. Чему равен процент от числаКак умножить число на процентКак… Как считать проценты в Excel — примеры формул - В этом руководстве вы познакомитесь с быстрым способом расчета процентов в Excel, найдете базовую формулу процента и еще несколько формул для расчета процентного изменения, процента от общей суммы и т.д.…
Читайте также: