Как просуммировать только отрицательные числа в excel

Как просуммировать только отрицательные числа в excel

Argument ‘Topic id’ is null or empty

Сейчас на форуме

© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru

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

ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949

ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071

Изменение способа отображения отрицательных чисел

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Еще. Меньше

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

1. Выделите ячейку или диапазон ячеек, к которым вы хотите применить стиль отрицательных чисел.
2. В Windows нажмите клавиши CTRL+1. Если вы используете Компьютер Mac, нажмите +1
3. В поле Категория выберите число или валюту.
4. В разделе Отрицательные числа выберите вариант для отрицательных чисел.

Где находится параметр скобок? Если вы не видите скобки для отрицательных чисел, это может быть связано с настройками операционной системы. Узнайте, как исправить эту проблему.

Суммирование в Excel

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

Знание о том, как произвести суммирование позволит Вам более эффективно работать в Excel.

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

Возможно, для кого-то это банальность, но, тем не менее, следует сказать о том, что для получения корректного (подчеркну – «корректного») результата суммирования обязательно должно быть выполнено одно существенное условие: наличие данных пригодных для суммирования.

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

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

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

Хочется подчеркнуть, что получение суммы в ячейке немыслимо, без применения формулы, которая, как известно, должна начинаться со знака «равно». Эта азбучная истина, но мы начнем именно с нее: любая формула в Excel (и суммирование тут не исключение) начинается с «=», т.е. со знака «равно». Кавычки, конечно, в самой формуле не нужны. Непосредственно после них идет формула, о чем и пойдет речь далее.

Самый лучший способ понять как что-то «работает» это рассмотреть примеры, которые демонстрируют различные вариации проведения операции суммирования.

Далее рассмотрим примеры суммирования в Excel констант, ячеек, диапазонов.

Все примеры пронумерованы, по ходу текста иногда идет ссылка на номер. Номер формулы с примером суммирования указан в квадратных скобках [] и он не является частью формулы, то есть при копировании (если Вы захотите попробовать эту формулу у себя в Excel) следует выделять и копировать до символов » [» (не включительно).

=1+22 [1]

=22+3+»14″+108 [2]

=0,25+3,348+200+0,0048 [3]

=-4+25 [4]

=78+-18,6+14+(-45) [5]

Комментарии к приведенным выше примерам с [1] по [5]:

— количество суммируемых чисел может быть любым;

— суммировать можно целые, нецелые числа при этом они могут быть и отрицательные и положительные (как видно в примере [5] несколько чисел — отрицательные);

— числа можно брать в скобки (делается это, например, для того, чтобы визуально отделить отрицательные числа от положительных);

— следует обратить внимание на пример в котором одно из чисел взято в кавычки. Несмотря на это Excel, тем не менее, корректно посчитает сумму с числом 14 включительно.

Отдельно необходимо сказать, что после суммирования Вы можете получить не совсем тот результат, который ожидали увидеть. Так, в примере [3] вместо ожидаемого 203,6028 в ячейке может отображаться число 203,60. Это связано с форматами ячеек, однако, в рамках этой статьи о суммировании, мы не будем на этом останавливаться.

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

=A2+F2+B5 [6]

(важно: ячейки участвующие в расчетах в такого рода формуле должны содержать числа, если это условие не будет соблюдено, то формула вернет значение ошибки: #ЗНАЧ!, #Н/Д, #ССЫЛКА!, а также может быть даже #ИМЯ?)

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

=D3+E4+-F7+H10 [7]

В этой формуле от суммы ячеек D3, E4, H10 будет отминусовано значение ячейки F7

Суммировать можно ячейки не только в пределах одного листа, но также используя данные находящиеся на других листах или даже в других книгах…

Пример суммирования значений текущего листа и других листов

=H6+H8+H11+Лист2!B2+Лист3!B3 [8]

Пример суммирования значений из другой книги:

=K5+5+[Книга3]Лист1!D2 [9]

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

=B2+16+-N4+Лист2!D2+[Книга10]Лист8!F15 [10]

То есть суммируется константа (число 16), а также еще 4 значения — два из которых находится на текущем листе, одно на Лист2 текущей книги и одно на Лист8 в Книга10 (Книга10 –это некий файл, который в момент написания формулы открыт одновременно с текущим файлом).

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

Так, например, формула с порядковым номером [7] преобразуется в такую:

(предполагается, что формула находится в ячейке H14)

В рассмотренных примерах суммирование производилось с использованием математического оператора – знака «плюс» («+»), однако, в большинстве случаев, при проведении больших расчетов используют формулы (функции).

Базовая функция для суммирования ячеек это функция СУММ()

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

=СУММ(D1:D11) [12]

Такая формула просуммирует все числовые значения из диапазона D1:D11. При этом, если в диапазоне встретятся текстовые значения, то они будут проигнорированы. Это очень важное свойство функции СУММ()

Еще вариации использования функции СУММ

=СУММ(B2:B9;50) [13]

(суммирует данные диапазона и константу)

=СУММ(A1:B6;D1:D11) [14]

(суммируется два диапазона, один из которых двумерный, а второй одномерный)

Допустимо и такое использование функции СУММ

=СУММ(2+14+25+36+88;100;-A10;D7:D10) [15]

Формула представляет из себя симбиоз суммы констант, значения из ячейки (причем значение как бы умножается на -1), а также 4 значений из диапазона D7:D10

Несмотря на то, что Excel предлагает довольно широкие возможности просуммировать данные с помощью формул с использованием оператора суммирования или же используя функцию (функция СУММ) существует также способ узнать сумму ячеек не прибегая ни к плюсованию, ни к формулам.

Делается это довольно просто: выделяем нужные ячейки и смотрим вниз, в так называемую «Строку состояния». Там будет показана общая сумма всех выделенных (числовых) ячеек.

Какие еще есть нюансы, связанные с суммированием

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

Суммирование по условию (функция СУММЕСЛИ), например, требуется просуммировать только те ячейки, значения в которых больше 100, или просуммировать ячейки у которых соседний столбец содержит необходимое слово (вариантов всевозможных великое множество, мы приведем только некоторые из них «равно», «не равно», «больше», «меньше», «больше или равно» (и все остальные вариации)) «содержит», «не содержит», «заканчивается на», «начинается с»

Суммирование только видимых ячеек (игнорируя скрытые ячейки).

Так называемое трехмерное суммирование, когда функция СУММ() собирает данные не с одного, а с нескольких листов, находящихся рядом.

Суммирование ячеек в динамическом диапазоне.

Иногда бывает необходимо также:

Просуммировать каждое второе (третье) значение.

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

Только уникальные значения.

Суммирование времени (часов, минут, секунд).

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

А сколько способов получить сумму чисел в Excel знаете и используете Вы.

Форматирование чисел в виде денежных единиц

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Еще. Меньше

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

В этой статье

• Форматирование чисел в виде денежных единиц
• Удаление форматирования в виде денежных единиц
• Разница между денежным и финансовым форматами
• Создание шаблона книги с конкретными параметрами форматирования в виде денежных единиц

Форматирование чисел в виде денежных единиц

Чтобы отобразить число с символом валюты по умолчанию, выберем ячейку или диапазон ячеек и нажмите кнопку в группе Число на вкладке Главная. (Если вы хотите вместо этого применить формат «Валюта», вы выберите ячейки и нажмите CTRL+SHIFT+$.)

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

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

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

Совет: Чтобы открыть диалоговое окно Формат ячеек, можно также нажать сочетание клавиш CTRL+1.

В диалоговом окне Формат ячеек в списке Числовые форматы выберите пункт Денежный или Финансовый.

В поле Обозначение выберите нужное обозначение денежной единицы.

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

В поле Число десятичных знаков введите нужное число знаков после запятой. Например, чтобы в ячейке вместо 138 690,63 ₽ отображалось 138 691 ₽, в поле Число десятичных знаков введите 0.

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

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

Примечание: Поле Отрицательные числа не доступно для числовых форматов Accounting. Это потому, что в обычной бухгалтерии отрицательные числа высвеются в скобки.

Чтобы закрыть диалоговое окно Формат ячеек, нажмите кнопку ОК.

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

Удаление форматирования в виде денежных единиц

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

1. Выберите ячейки с форматированием в виде денежных единиц.
2. На вкладке Главная в группе Число выберите пункт Общий. В ячейках с форматом Общий форматирование к числам не применяется.

Разница между денежным и финансовым форматами

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

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

Совет: Чтобы быстро применить денежный формат, выберите необходимую ячейку или диапазон ячеек и нажмите сочетание клавиш CTRL+SHIFT+$.

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

Совет: Чтобы быстро применить финансовый формат, выделите ячейку или диапазон ячеек, которые необходимо отформатировать. На вкладке Главная в группе Число нажмите кнопку Финансовый числовой формат . Если нужно использовать обозначение денежной единицы, отличное от применяемого по умолчанию, щелкните стрелку рядом с кнопкой Финансовый числовой формат и выберите другое обозначение.

Создание шаблона книги с конкретными параметрами форматирования в виде денежных единиц

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

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

Чтобы выделить	Выполните следующие действия
Один лист	Щелкните ярлычок листа. Если ярлычок нужного листа не виден, найдите его с помощью кнопок прокрутки листов, а затем щелкните.
Несколько смежных листов	Щелкните ярлычок первого листа. Затем, удерживая нажатой клавишу SHIFT, щелкните ярлычок последнего листа в диапазоне, который требуется выделить.
Несколько несмежных листов	Щелкните ярлычок первого листа. Затем, удерживая нажатой клавишу CTRL, щелкните ярлычки других листов, которые нужно выделить.
Все листы книги	Щелкните правой кнопкой мыши ярлычок листа и выберите в контекстном меню (контекстное меню) команду Выделить все листы.

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

Сохранение книги в виде шаблона

1. Если вы впервые сохраняете книгу в виде шаблона, сначала укажите расположение личных шаблонов по умолчанию.
   1. Откройте вкладку Файл и выберите команду Параметры.
   2. Нажмите Сохранение, а затем в разделе Сохранение книг укажите путь к месту расположения личных шаблонов в поле Расположение личных шаблонов по умолчанию. Обычно этот путь выглядит так: C:\Пользователи\Общие\Мои шаблоны.
   3. Нажмите кнопку ОК. Если этот параметр настроен, все настраиваемые шаблоны, сохраненные в папке «Мои шаблоны», автоматически отображаются в разделе Личные на странице Создать (Файл >Создать).
2. Нажмите Файл >Экспорт.
3. В разделе Экспорт нажмите кнопку Изменить тип файла.
4. В поле Типы файлов книг дважды щелкните пункт Шаблон.
5. В поле Имя файла введите имя шаблона.
6. Нажмите кнопку Сохранить, а затем закройте шаблон.

Создание книги на основе шаблона

1. Откройте вкладку Файл и выберите команду Создать.
2. Выберите пункт Личные.
3. Дважды щелкните только что созданный шаблон. В приложении Excel будет создана книга на основе шаблона.