Электронные таблицы в Excel позволяют выполнять различные вычислительные действия, включая суммирование ячеек. При работе с внушительным объемом данных, иногда требуется проделывать математические операции с ячейками, попадающими по конкретные условия. Для таких задач в Excel предусмотрены специальные функции для суммирования по условию, которые упрощают работу с таблицами.
- 1 Простое сложение в Excel
- 2 Суммирование ячеек по условию
- 3 Функция «СУММЕСЛИ»
- 4 Функция «СУММЕСЛИМН»
- 5 Итог
Простое сложение в Excel
Складывать значения ячеек можно с помощью простой функции «СУММ», которая суммирует выбранные значения без определенного условия. Она используется, когда есть ячейки, массив или несколько массивов для сложения. В этом случае достаточно выделить нужные элементы электронной таблицы и применить функцию. Она играет роль обычного калькулятора со знаком «+».
Суммирование ячеек по условию
Excel также предусматривает суммирование с учетом условия. Функция применяется, когда необходимо просуммировать только те значения, которые соответствуют заданному условию. Для нее возможно задать три аргумента:
- Диапазон – обязательный аргумент, представляющий собой массив, в котором происходит проверка заданного условия;
- Критерий – еще один обязательный аргумент, которое является условием для отбора значений в ячейках. При равенстве определенному числу, необходимо ввести его без кавычек, в других случаях необходимы кавычки: например, если значение больше числа 5, то его нужно прописать, как
">5". Также работают текстовые значения: если нужно суммировать выручку продавца Иванова в таблице, то прописывается условие"Иванов"; - Диапазон суммирования – массив значений, которые нужно сложить.
Для удобной работы с вычислительными операциями в таблицах Excel есть две функции, которые суммируют ячейки, если задать условия: «СУММЕСЛИ» и «СУММЕСЛИМН». Рассмотрим каждую из них более подробно.
Функция «СУММЕСЛИ»
СУММЕСЛИ подходит, когда нужно суммировать значения ячеек с одним условием. Чтобы применить ее в действии, следуйте инструкции:
- По свободной ячейке кликните левой клавишей мыши, чтобы выбрать место, где будет отображаться результат операции.
- Кликните по иконке, расположенной рядом со строкой ввода формул, чтобы вызвать меню с функциями.
- Выберите категорию «Математические» и найдите в списке функций «СУММЕСЛИ».
- Кликните по кнопке «ОК», чтобы подтвердить свой выбор.
- В появившемся окне «Аргументы функции» нужно заполнить обязательные поля: «Диапазон» и «Критерий». Например, нужно узнать, сколько килограмм фруктов продал продавец Иванов. Для этого выделите диапазон столбца «Продавец» полностью.
- В строке «Критерий» пропишите фамилию продавца: «Иванов». Кавычки можно не указывать, программа вставляет их автоматически.
- Поставьте курсор мыши в поле диапазона суммирования и выделите столбец «Количество (кг)» в таблице.
- Нажмите на кнопку «ОК» внизу, чтобы произвести суммирование с условием, или из данной таблицы узнать, сколько килограмм фруктов продал Иванов.
В выделенной ячейке отобразится результат сложения, а в верхней строке – функция. Вычислить значение можно без вызова окна «Аргументы функции», если прописать все вручную. При этом базовая запись должна выглядеть так: =СУММЕСЛИ(X; «Условие»; Y), где X – это диапазон, а Y – диапазон суммирования. Значения не обязательно вписывать, их достаточно выделить с помощью мышки в таблице. Все элементы функции разделены знаком «;».
Следует отметить, что если третий аргумент явно не выделили, то диапазон, указанный как первый аргумент, также становится диапазоном для сложения.
Функция «СУММЕСЛИМН»
«СУММЕСЛИМН» позволяет рассчитать результат суммирования с использованием нескольких условий. Функция предоставляет больше возможностей для задания параметров математического вычисления. Для расчета можно использовать сразу несколько критериев суммирования, причем условий может быть задано до 127. На примере данной таблицы рассмотрим, как найти, сколько килограмм яблок купил Евдокимов, ведь он приобретал также и бананы.
Чтобы суммировать ячейки с несколькими условиями, действуйте согласно следующей инструкции:
- Выделите пустую ячейку, в которой будет отображаться конечный результат, затем нажмите на кнопку fx, которая находится рядом со строкой функций.
- В разделе «Математические» в окне «Вставка функций» нажмите «СУММЕСЛИМН», затем подтвердите выбор, нажав на кнопку «ОК».
- В появившемся окне в строке «Диапазон суммирования» введите ячейки, который находятся в столбце «Количество».
- В «Диапазон условия» выделите все ячейки в столбце «Товар».
- В качестве первого условия пропишите значение «Яблоки».
- После этого необходимо задать второе условие и диапазон для него. В данной таблице столбец «Покупатели» является значением для диапазона. Выделите его в строку, затем в втором условии пропишите фамилию Евдокимов.
- Нажмите на кнопку «ОК», чтобы программа посчитала, сколько яблок купил Евдокимов.
Функцию «СУММЕСЛИМН» возможно прописать вручную в строке формул, но это сложно, поскольку используется слишком много условий. В данной таблице результат равен 8, а вверху отображается функция полностью.
Итог
Встроенные инструменты программы Excel позволяют эффективно работать с большими электронными таблицами. С помощью функций суммирования возможно быстро сложить определенные ячейки, а с дополнительными условиями – подобрать критерии выбора для сложения. Мы рассмотрели, как пользоваться «СУММЕСЛИ» и «СУММЕСЛИМН» на примере простой таблицы, но эти функции применимы и для объемных баз данных.
17 авг. 2022 г.
читать 2 мин
Вы можете использовать следующие формулы в Excel для суммирования диапазона ячеек, если значение в соответствующем диапазоне не является пустым:
Формула 1: Сумма, если не пусто (один столбец)
=SUMIF( A:A , "<>", B:B )
Эта формула суммирует значения в столбце B только в том случае, если значения в столбце A не являются пустыми.
Формула 2: Сумма, если не пусто (несколько столбцов)
=SUMIFS( C:C , A:A , "<>", B:B , "<>")
Эта формула суммирует значения в столбце C только в том случае, если значения в столбцах A и B не являются пустыми.
В следующих примерах показано, как использовать каждую формулу на практике.
Пример 1: Сумма, если не пусто (один столбец)
На следующем снимке экрана показано, как суммировать значения в столбце « Очки », только если значения в столбце « Команда » не являются пустыми:
Сумма значений в столбце Очки для строк, где команда не пуста, равна 138 .
Мы можем убедиться, что это правильно, вручную рассчитав сумму очков для команд, которые не являются пустыми:
Сумма очков: 22 + 17 + 28 + 30 + 12 + 11 + 18 = 138 .
Это соответствует значению, которое мы рассчитали по формуле.
Пример 2. Сумма, если она не пуста (несколько столбцов)
На следующем снимке экрана показано, как суммировать значения в столбце « Очки », только если значения в столбцах « Конференция» и « Команда» не пусты:
Сумма значений в столбце « Очки » для строк, в которых столбцы « Конференция» и « Команда» заполнены, равна 90 .
Мы можем убедиться, что это правильно, вручную посчитав сумму баллов, где конференция и команда не являются пустыми:
Сумма очков: 22 + 17 + 28 + 12 + 11 = 90 .
Это соответствует значению, которое мы рассчитали по формуле.
Дополнительные ресурсы
В следующих руководствах объясняется, как выполнять другие распространенные задачи в Excel:
Как использовать СУММЕСЛИ с ИЛИ в Excel
Как суммировать, если ячейки содержат текст в Excel
Как использовать подстановочный знак в функции СУММЕСЛИМН в Excel
Простые логические функции такие как ЕСЛИ обычно предназначены для работы с одним столбцом или одной ячейкой. Excel также предлагает несколько других логических функций служащих для агрегирования данных. Например, функция СУММЕСЛИ для выборочного суммирования диапазона значений по условию.
Примеры формулы для суммы диапазонов с условием отбора в Excel
Ниже на рисунке представлен в таблице список счетов вместе с состоянием по каждому счету в виде положительных или отрицательных чисел. Допустим нам необходимо посчитать сумму всех отрицательных чисел для расчета суммарного расхода по движению финансовых средств. Этот результат будет позже сравниваться вместе с сумой положительных чисел с целью верификации и вывода балансового сальдо. Узнаем одинаковые ли суммы доходов и расходов – сойдется ли у нас дебит с кредитом. Для суммирования числовых значений по условию в Excel применяется логическая функция =СУММЕСЛИ():
Функция СУММЕСЛИ анализирует каждое значение ячейки в диапазоне B2:B12 и проверяет соответствует ли оно заданному условию (указанному во втором аргументе функции). Если значение меньше чем 0, тогда условие выполнено и данное число учитывается в общей итоговой сумме. Числовые значения больше или равно нулю игнорируются функцией. Проигнорированы также текстовые значения и пустые ячейки.
В приведенном примере сначала проверяется значения ячейки B2 и так как оно больше чем 0 – будет проигнорировано. Далее проверяется ячейка B3. В ней числовое значение меньше нуля, значит условие выполнено, поэтому оно добавляется к общей сумме. Данный процесс повторяется для каждой ячейки. В результате его выполнения суммированы значения ячеек B3, B6, B7, B8 и B10, а остальные ячейки не учитываются в итоговой сумме.
Обратите внимание что ниже результата суммирования отрицательных чисел находится формула суммирования положительных чисел. Единственное отличие между ними — это обратный оператор сравнения во втором аргументе где указывается условие для суммирования – вместо строки «<0» (меньше чем ноль) используется строка «>0» (больше чем ноль). Теперь мы можем убедиться в том, что дебет с кредитом сходится балансовое сальдо будет равно нулю если сложить арифметически в ячейке B16 формулой =B15+B14.
Пример логического выражения в формуле для суммы с условием
Другой пример, когда нам нужно отдельно суммировать цены на группы товаров стоимости до 1000 и отдельно со стоимостью больше 1000. В таком случае одного оператора сравнения нам недостаточно (<1000) придется использовать знак «меньше или равно» (<=1000) иначе цены со значением ровно 1000 не будут включены в расчеты. Более того в таком случае мы уже не можем использовать в другой формуле «больше или равно» (>=1000) иначе мы просуммируем сумму ровно в 1000 – 2 раза, что приведет к ошибочным итоговым результатам:
Это очень распространенная ошибка пользователей Excel при работе с логическими функциями!
Внимание! В первом примере нулевые значения нам необязательно было учитывать, так как на балансовое сальдо это никак не повлияло бы, но во втором случаи нужно составлять критерий условия суммирования иначе, чтобы не допустить ошибочных просчетов.
Второй аргумент функции СУММЕСЛИ, то есть условие, которое должно быть выполнено, записывается между двойными кавычками. В данном примере используется символ сравнения – «меньше» (<). По синтаксису функции необходимо представлять такие записи как строка, то есть в двойных кавычках. Иначе Excel выдаст предупреждение об ошибке в формуле.
Синтаксис формулы суммирования по условию в Excel
В синтаксисе функции СУММЕСЛИ присутствует необязательный для заполнения третий аргумент – диапазон суммирования. В предыдущем примере условие проверялось по тому же диапазону, который нужно было суммировать. Благодаря третьему аргументу появляется возможность проверять условия по одному диапазону, а суммировать другой.
Ниже на рисунке представлена таблица отчета продаж по клиентам за месяц. Напротив, каждого клиента указана сумма, на которую совершил покупки клиент на протяжении месяца. Некоторые клиенты за месяц совершили по несколько покупок, поэтому их наименование дублируется в списке. Необходимо узнать на какую общую сумму купил товара «Клиент3» в данном месяце. Здесь весьма уместно использовать формулу с заполнением третьего аргумента функции СУММЕСЛИ:
Как видно на всех выше приведенных примерах во втором аргументе указывается строка текста с условием, а не логическое выражение как в функции ЕСЛИ. На практике использования функции СУММЕСЛИ этот второй логический аргумент с условием может содержать:
- сроки с текстовым значением;
- числовые значения;
- логические выражения;
- формулы с функциями;
- ссылки на ячейки.
Далее рассмотрим больше примеров составления условий для второго логического аргумента функции СУММЕСЛИ более детально.
Создание критериев условий для функции СУММЕСЛИ
Второй аргумент функции называется «Критерий». Данный логический аргумент используется и в других подобных логических функциях: СУММЕСЛИМН, СЧЁТЕСЛИ, СЧЁТЕСЛИМН, СРЗНАЧЕСЛИ и СРЗНАЧЕСЛИМН. В каждом случаи аргумент заполняется согласно одних и тех же правил составления логических условий. Другими словами, для всех этих функций второй аргумент с критерием условий является логическим выражением возвращающим результат ИСТИНА или ЛОЖЬ. Это значит, что выражение должно содержать оператор сравнения, например: больше (>) меньше (<) равно (=) неравно (<>), больше или равно (>=), меньше или равно (<=). За исключением можно не указывать оператор равно (=), если должно быть проверено точное совпадение значений.
Создание сложных критериев условий может быть запутанным. Однако если придерживаться нескольких простых правил описанных в ниже приведенной таблице, не будет возникать никаких проблем.
Таблица правил составления критериев условий:
| Чтобы создать условие | Примените правило | Пример |
| Значение равно заданному числу или ячейке с данным адресом. | Не используйте знак равенства и двойных кавычек. | =СУММЕСЛИ(B1:B10;3) |
| Значение равно текстовой строке. | Не используйте знак равенства, но используйте двойные кавычки по краям. | =СУММЕСЛИ(B1:B10;»Клиент5″) |
| Значение отличается от заданного числа. | Поместите оператор и число в двойные кавычки. | =СУММЕСЛИ(B1:B10;»>=50″) |
| Значение отличается от текстовой строки. | Поместите оператор и число в двойные кавычки. | =СУММЕСЛИ(B1:B10;»<>выплата») |
| Значение отличается от ячейки по указанному адресу или от результата вычисления формулы. | Поместите оператор сравнения в двойные кавычки и соедините его символом амперсант (&) вместе со ссылкой на ячейку или с формулой. | =СУММЕСЛИ(A1:A10;»<«&C1) или =СУММЕСЛИ(B1:B10;»<>»&СЕГОДНЯ()) |
| Значение содержит фрагмент строки | Используйте операторы многозначных символов и поместите их в двойные кавычки | =СУММЕСЛИ(A1:A10;»*кг*»;B1:B10) |
Во втором аргументе критериев условий можно использовать разные функции и формулы. Ниже на рисунке изображен список дат и присвоенных им значений. Важно отметить что сегодня на момент написания статьи дата – «03.11.2018». Чтобы суммировать числовые значения только по сегодняшней дате используйте формулу:
Чтобы суммировать только значения от сегодняшнего дня включительно и до конца периода времени воспользуйтесь оператором «больше или равно» (>=) вместе с соответственной функцией =СЕГОДНЯ(). Формула c операторам (>=):
Суммирование по неточному совпадению в условии критерия отбора
Во втором логическом аргументе критериев условий функции СУММЕСЛИ можно применять многозначные символы – (?)и(*) для составления относительных неточных запросов. Знак вопроса (?) – следует читать как любой символ, а звездочка (*) – это строка из любого количества любых символов или пустая строка. Например, нам необходимо просуммировать только защитные краски-лаки с кодом 3 английские буквы в начале наименования:
Суммируются все значения ячеек в диапазоне B2:B16 в соответствии со значениями в ячейках диапазона A2:A16, в которых после третьего символа фрагмент строки «-защита».
Таким образом удалось суммировать только определенную группу товаров в общем списке отчета по складу. Данный фрагмент наименования товара должен встречаться в определенном месте – 3 символа от начала строки. Нет необходимости использовать сложные формулы с функцией =ЛЕВСИМВ() и т.д. Достаточно лишь воспользоваться операторами многозначных символов чтобы сформулировать простой и лаконичный запрос к базе данных с минимальными нагрузками на системные ресурсы.
|
Сумма если текст не содержит |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
Объединение текста и чисел
Смотрите также Randy Ortonпросто попытайтесь в использовать пожалуй вариантЕсли у кого-то несколько раз в ‘собираем текст из в одном - «7»). При его — текст из + муж = значения с одним грамматические ошибки. Для эффективно объединять диапазоны. текст и больше косой черты () образом. Можно отформатироватьПримечание:Суммировать же буквы них разобраться с макросом, который еще есть варианты
одной ячейке. ячеек Next rCell кнопка использовании необходимо помнить, всех ячеек всех любовь!» условием. Например, когда нас важно, чтобыTEXTJOIN не функция числа в начале. ячейки, содержащие 15,Мы стараемся как нельзя. Буквы можно
Используйте числовой формат для отображения текста до или после числа в ячейке
Алексей матевосов (alexm) прислал решения — милостиПример (все это Application.DisplayAlerts = FalseОбъединить и поместить в что: диапазонов будет объединенВ категории необходимо для суммирования эта статья былав Excel 2013 становятся как числовыеПримечание: 100 и 200, можно оперативнее обеспечивать
СЦЕПИТЬ. Типа А: Допустим в ячейкахsnipe просим))) — в одной ‘отключаем стандартное предупреждение центре (Merge andЭтот символ надо ставить
в одно целое:
-
Текстовые определенного продукта total
-
вам полезна. Просим и более ранние значения. Это означает,изменение встроенного формата чтобы они отображались
-
вас актуальными справочными и В сидели столбца с А1.snipe ячейке, в А2): о потере текста Center)
-
в каждой точкеДля массового объединения такжеесть функция sales. вас уделить пару версии не поддерживается.
что больше не не приводит к на листе как материалами на вашем на трубе. по А10 числа,Благодарю!: Public Function iSumma(Текст
154 р. - .Merge Across:=False ‘объединяемв Excel объединять-то соединения, т.е. на
|
удобно использовать новую |
СЦЕПИТЬ (CONCATENATE) |
При необходимости суммирование значений |
|
секунд и сообщить, |
Примеры различных на рисунке |
может выполнять любые удалению формат. 15 # продукт, языке. Эта страницаВ ячейке А1 текст и пустыеSvsh2015 As String) As |
|
булочки; 550 р. ячейки Application.DisplayAlerts = |
ячейки умеет, а |
всех «стыках» текстовых функцию, которая соединяет содержимое с помощью нескольких помогла ли она |
|
ниже. математических операций наДля отображения |
продукт #100 и |
переведена автоматически, поэтому буква А, в ячейки.: Floyd73,добрый день,вариант функции Double Dim a() — мясо; 120 True .Item(1).Value = вот с текстом строк также, какОБЪЕДИНИТЬ (TEXTJOIN) нескольких ячеек (до |
Объединение текста и чисел из разных ячеек в одной ячейке с помощью формулы
условий, используйте функцию вам, с помощьюВнимательно посмотрите на использование них.Используйте код 200 # продукта. ее текст может ячейке В1 букваФормула суммирования =СУММ uuu в A1
As String a() р. — молоко; Mid(sMergeStr, 1 + сложность — в вы ставите несколько, появившуюся начиная с
255) в одно СУММЕСЛИМН . Например
-
кнопок внизу страницы. функцииДля объединения чисел сПринцип действияИспользование пользовательского числового формата содержать неточности и В (A1:A10)Function uuu(t$) Dim = Split(Текст, «;») 65 р. - Len(sDELIM)) ‘добавляем к живых остается только плюсов при сложении Excel 2016. У целое, позволяя комбинировать нужно добавить вверх Для удобства также
-
текст помощью функции СЦЕПИТЬ12 как Продукт №12 для отображения номера грамматические ошибки. ДляФормулыФормула пропускает пустые i% With CreateObject(«VBScript.RegExp»): For i = яблоки. объед.ячейке суммарный текст текст из верхней нескольких чисел (2+8+6+4+8) нее следующий синтаксис: их с произвольным
Примеры
total sales определенного приводим ссылку на
во втором примере или функции ОБЪЕДИНЕНИЯ,»Продукт № » 0 с текстом, не нас важно, чтобы=A1&B1 ( точно ячейки и с .Pattern = «d+»: 0 To UBound(a())Далее, из ячейки End With End левой ячейки.Если нужно приклеить произвольный=ОБЪЕДИНИТЬ(Разделитель; Пропускать_ли_пустые_ячейки; Диапазон1; Диапазон2 текстом. Например, вот продукта в рамках оригинал (на английском на рисунке. При текст и TEXTJOINТекст, заключенный в кавычки изменяя порядок сортировки эта статья была как в считалке) текстом. Суммирует только
См. также
-
.Global = True
-
asd = Trim(a(i))
-
с такими данными
-
Sub
support.office.com
Способы добавления значений на листе
Чтобы объединение ячеек происходило текст (даже если … ) так: определенной области продаж. языке) . присоединении к числа и амперсанд (&) (включая пробел) отображается число. Таким образом вам полезна. Просим=СЦЕПИТЬ (A1;B1) числовые значения. For i = asd = Mid(asd, нужно вытащить иТеперь, если выделить несколько с объединением текста это всего лишьгдеНюанс: не забудьте оОбщие сведения о том,
Один быстрый и простой в строку текста оператор. в ячейке перед изменение способа отображения вас уделить паруRandy ortonKleom 0 To .Execute(t).Count 1, InStr(1, asd, сложить все числа, ячеек и запустить (как в таблицах точка или пробел,Разделитель пробелах между словами как сложение и для добавления значений с помощью операторПримечания: числом. В этом
номера без изменения
секунд и сообщить,: Вот: =СЧЁТЗ (A1:A3): что значит буквенное? — 1 uuu » «) - в ячейке, например, этот макрос с
Добавление на основе условий
-
Word) придется использовать не говоря уж- символ, который — их надо вычитание дат можно в Excel всего объединения, используйте функцию
-
коде «0» обозначает значения. помогла ли она В скобках диапазон если числа написаны = uuu + 1) iSumma =
Сложение или вычитание дат
А1. помощью сочетания клавиш макрос. Для этого о целом слове), будет вставлен между прописывать как отдельные найти Добавление и воспользоваться функцией Автосумма.текст
Сложение и вычитание значений времени
В Excel 2016Excel Mobile и число, которое содержитсяВыполните следующие действия. вам, с помощью укажи. словами, то придется .Execute(t)(i) Next End iSumma + CDbl(asd)Т.е. в А1
support.office.com
3 способа склеить текст из нескольких ячеек
Alt+F8 или кнопкой откройте редактор Visual то этот текст фрагментами аргументы и заключать
вычитание дат. Более Выделите пустую ячейку, чтобы управлять Excel Online с
в ячейке (например,Выделите ячейки, формат которых кнопок внизу страницы.
Pulse сначала их вручную With End Function
Способ 1. Функции СЦЕПИТЬ, СЦЕП и ОБЪЕДИНИТЬ
Next i End должно получиться числоМакросы Basic на вкладке надо заключать вВторой аргумент отвечает за в скобки, ибо сложные вычисления с непосредственно под столбцом способом отображения чисел. помощью функции
12). требуется изменить. Для удобства также: Нужно, чтобы складывались ввести в числовомSvsh2015 Function
889.на вкладке РазработчикРазработчик - кавычки. В предыдущем то, нужно ли текст. датами, читайте в данных. На вкладке В формуле используетсяОБЪЕДИНЕНИЯ12:00 как 12:00 центральноевропейскоеНа вкладке приводим ссылку на только те ячейки виде, а потом: добавлю еще вариантAlexMПолучается, у нас(Developer — Macros)Visual Basic (Developer - примере с функцией игнорировать пустые ячейкиОчевидно, что если нужно статье даты и « базовое значение иззаменена время
Главная оригинал (на английском в которых содержится складывать, иначе никак… функции,в файл примере: Можно массивной формулой ячейка А1 -
, то Excel объединит Visual Basic)
СЦЕПИТЬ о кавычках
- (ИСТИНА или ЛОЖЬ) собрать много фрагментов, операций со временем.формулы
- ячейки, на которыйфункции СЦЕПИТЬч:мм «центральноевропейское время»в группе
- языке) . числа. Т.е еслиDemetry uuu (и есть
Код =СУММ(—ЕСЛИ(ПРАВБ(ПСТР(A1;СТРОКА($1:$99);6);2)=»р.»;ПСТР(A1;СТРОКА($1:$99);3)))
Способ 2. Символ для склеивания текста (&)
итого, а ячейка выделенные ячейки вили сочетанием клавиш заботится сам Excel
Диапазон 1, 2, 3 то использовать этуОбщие сведения о том,» нажмите кнопку указывает ссылка (в. Несмотря наТекущее время показано вчислоПредположим, что нужно создать в ячейке текст,: =СЧЁТЕСЛИ (A1:A100;»*н*») uuu1)Floyd73 А2 — детализация. одну, слив туда Alt+F11, вставим в
- — в этом… — диапазоны функцию уже не как сложение иАвтосумма данном примере.4) — то, что функция формате даты/времени ч:мм
- щелкните стрелку. предложение грамматически правильные нужно, чтобы онаСчитает в диапазонеFunction uuu#(t$) Dim: Спасибо за стольПри небольшом гуглении же и текст нашу книгу новый же случае их ячеек, содержимое которых очень удобно, т.к. вычитание значений времени> не форматированное значение,
СЦЕПИТЬ AM/PM, а текстВ списке из нескольких столбцов не учитывалась. Справку
от А1 до i%, s# With разнообразные варианты решений! варианта решения не через пробелы. программный модуль (меню надо ставить вручную. хотим склеить
Способ 3. Макрос для объединения ячеек без потери текста.
придется прописывать ссылки отображается Добавление иСумма отображаемое в ячейкепо-прежнему доступен для «московское время» отображаетсякатегории данных для подготовки смотрел, даже пример А100 количество ячеек, CreateObject(«VBScript.RegExp»): .Pattern =Теперь думаю, какой нашел, может бытьFloyd73Insert — ModuleВот, например, как можноНапример: на каждую ячейку-фрагмент
вычитание значений времени.. Excel автоматически будут (40%). Чтобы восстановить обеспечения обратной совместимости, после времени.выберите категорию, например массовой рассылки. Или, есть, но почему в тексте которых «d+»: .Global = из них удобнее неправильно формулирую запрос,: День добрый!) и скопируем туда собрать ФИО вЭто универсальный и компактный по отдельности. Поэтому, Другие вычисления времени,
определения диапазона, который форматов чисел используйте следует использовать-12 как -12р. дефицитнастраиваемые возможно, вам нужно то он у есть буква «н». True For i будет использовать))) конечно…Имеется небольшая задачка, текст такого простого одну ячейку из способ сцепки, работающий начиная с 2016 можно просмотреть даты необходимо суммировать. (Автосумма функциюОБЪЕДИНЕНИЯ и 12 каки нажмите кнопку встроенный форматирование чисел с меня не работает.Алексей матевосов (alexm) = 0 ToКазанскийПодозреваю, что можно
которую нужно реализовать макроса: трех с добавлением абсолютно во всех версии Excel, на и операций со также можно работатьтекст, так как 12р. избыток формат, который похож текстом, не затрагивая_Boroda_
planetaexcel.ru
Вытащить из ячейки числа и сложить их
: После двух часового .Execute(t).Count — 1
: сделать скриптом, но в экселе.
Sub MergeToOneCell() Const пробелов:
версиях Excel. замену функции временем.
по горизонтали при.функции СЦЕПИТЬ0.00р. «избыток»;-0.00р. «дефицит» на то, которое формулы, которые могут: =СУММ(I3;I5;I7;I10) раздумья по данному
s = sFloyd73 работать с ними
Суть задачки вот sDELIM As StringЕсли сочетать это с
ДляСЦЕПИТЬНадпись на заборе: «Катя выборе пустую ячейкуФункция СЦЕПИТЬмогут быть недоступны
Это значение отображается в вы хотите. использовать эти числа.Итог 43 вопросу появилась желание + CDbl(.Execute(t)(i)) Next
, до кучи Function не умею =( в чем.
= » « функцией извлечения изсуммированияпришла ее более
+ Миша + справа от ячейки,СЦЕП в будущих версиях формате «Денежный». Кроме
В поле В Excel существуетСправка вражеская. Там предложить организаторам проекта
uuu = s Fl(x) As Double
Можно решить задачуВ ячейке, например,
‘символ-разделитель Dim rCell текста первых буквсодержимого нескольких ячеек совершенная версия с Семён + Юра чтобы суммировать.)
Функция ТЕКСТ Excel.
того, если вТип несколько способов для разделитель — запятая.
«Вопросы и ответы» End With End Dim y For обычными формулами? А2, записываются данные, As Range Dim - используют знак плюс похожим названием и + Дмитрий Васильевич»Сумма».» />Функция TEXTJOINTEXTJOIN ячейке находится положительноеизмените коды числовых объединения текста и
А у нас усовершенствовать систему начисления Function
Each y InПодскажите, пожалуйста, люди по следующему шаблону:
sMergeStr As StringЛЕВСИМВ (LEFT) «
тем же синтаксисом +Автосумма создает формулу дляПримечание:Объединение текста из значение (или 0), форматов в формате, чисел. — точка с баллов.snipe
Split(Replace(x, «,», «.»), добрые!число, пробел, текст,
If TypeName(Selection) <>, то можно получить+ — функциятоварищ Никитин + вас, таким образом,Мы стараемся как
нескольких диапазонах и/или
после него будет который вы хотитеЕсли столбец, который вы
запятой.Только за прочтения: «;») Fl =OLEGOFF пробел, тире, пробел, «Range» Then Exit фамилию с инициалами», а дляСЦЕП (CONCAT)
рыжий сантехник + чтобы вас не можно оперативнее обеспечивать строки, а также показан текст «(излишек)»,
создать. хотите отсортировать содержитPulse таких вопросов начислятьFloyd73 Fl + Val(y): текст, точка с Sub ‘если выделены одной формулой:склеивания. Ее принципиальное отличие
Витенька + телемастер требуется вводить текст. вас актуальными справочными разделитель, указанный между а если ячейкаДля отображения текста и
числа и текст: баллы, а за
, все присланные вам Next End Function
CyberForum.ru
Подскажите пожалуйста как в excel посчитать сумму если значение в ячейке буквенное но нужно сложить все ячейки!???
Floyd73 запятой (или точка), не ячейки -Имеем текст в несколькихсодержимого ячеек используют в том, что
Жора + Однако при желании
материалами на вашем каждой парой значений, содержит отрицательное значение, чисел в ячейке,
— 15 #_Boroda_ ответ баллы умножать примеры заслуживают изученияAlexM, с помощью опции пробел (ставится после выходим With Selection
ячейках и желание знак «
теперь в качествесволочь Редулов + введите формулу самостоятельно языке. Эта страница который будет добавляться
Excel. Как сложить ячейки в excel , если в ячейках забита буква а не цифра ?
после него будет заключите текст в продукта, продукт #100, Спасибо! Выходит в на коэффициент. с целью понимания: Вариант с макросом » Текст по
точки с запятой, For Each rCell — объединить эти& аргументов можно задавать
не вспомнить имени, просматривать функцию сумм. переведена автоматически, поэтому текст. Если разделитель
показан текст «(недостаток)». двойные кавычки (» и 200 # справке ошибка?!И так. Ячейки,
помимо моей функции универсальный столбцам» можно так после точки не
In .Cells sMergeStr
ячейки в одну,» (расположен на большинстве
не одиночные ячейки,
длинноволосый такой +Используйте функцию СУММЕСЛИ , ее текст может пустую текстовую строку,
Сложение ячеек содержащих текст (Сложение ячеек содержащих текст)
При объединении чисел и «), или чисел продукта — неPulse содержащие значения, можно вам предложены болееFloyd73Floyd73 ставится). = sMergeStr & слив туда же клавиатур на цифре
а целые диапазоныещё 19 мужиков
если нужно суммировать
содержать неточности и эта функция будет текста в ячейке, с помощью обратной может сортировать должным
: Капец! считать, как показал красивые решения: Всем спасибо, буду: Спасибо за решение.
И так далее, sDELIM & rCell.Text
excelworld.ru
их текст. Проблема
Skip to content
В таблицах Excel можно не просто находить сумму чисел, но и делать это в зависимости от заранее определённых критериев отбора. Хорошо знакомая нам функция ЕСЛИ позволяет производить вычисления в зависимости от выполнения условия. Функция СУММ позволяет складывать числовые значения. А что если нам нужна формула ЕСЛИ СУММ? Для этого случая в Excel имеется специальная функция СУММЕСЛИ.
Мы рассмотрим, как правильно применить функцию СУММЕСЛИ (Sumif в английской версии) в таблицах Excel. Начнем с самых простых случаев, как можно использовать при этом знаки подстановки, назначить диапазон суммирования, работать с числами, текстом и датами. Особо остановимся на том, как использовать сразу несколько условий. И, конечно, мы применим новые знания на практике, рассмотрев несложные примеры.
- Как пользоваться СУММЕСЛИ в Excel – синтаксис
- Примеры использования функции СУММЕСЛИ в Excel
- Сумма если больше чем, меньше, или равно
- Критерии для текста.
- Подстановочные знаки для частичного совпадения.
- Точная дата либо диапазон дат.
- Сумма значений, соответствующих пустым либо непустым ячейкам
- Сумма по нескольким условиям.
- Почему СУММЕСЛИ у меня не работает?
Хорошо, что функция СУММЕСЛИ одинакова во всех версиях MS Excel. Еще одна приятная новость: если вы потратите некоторое время на ее изучение, вам потребуется совсем немного усилий, чтобы понять другие «ЕСЛИ»-функции, такие как СУММЕСЛИМН, СЧЕТЕСЛИ, СЧЕТЕСЛИМН и т.д.
Как пользоваться СУММЕСЛИ в Excel – синтаксис
Её назначение – найти итог значений, которые удовлетворяют определённым требованиям.
Синтаксис функции выглядит следующим образом:
=СУММЕСЛИ(диапазон, критерий, [диапазон_суммирования])
Диапазон – это область, которую мы исследуем на соответствие определённому значению.
Критерий – это значение или шаблон, по которому мы производим отбор чисел для суммирования.
Значение критерия может быть записано прямо в самой формуле. В этом случае не забывайте, что текст нужно обязательно заключать в двойные кавычки.
Также он может быть представлен в виде ссылки на ячейку таблицы, в которой будет указано требуемое ограничение. Безусловно, второй способ является более рациональным, поскольку позволяет гибко менять расчеты, не редактируя выражение.
Диапазон_суммирования — третий параметр, который является необязательным, однако он весьма полезен. Благодаря ему мы можем производить поиск в одной области, а суммировать значения из другой в соответствующих строках.
Итак, если он указан, то расчет идет именно по его данным. Если отсутствует, то складываются значения из той же области, где производился поиск.
Чтобы лучше понять это описание, рассмотрим несколько простых задач. Надеюсь, что они будут понятны не только «продвинутым» пользователям, но и подойдут для «чайников».
Примеры использования функции СУММЕСЛИ в Excel
Сумма если больше чем, меньше, или равно
Начнем с самого простого. Предположим, у нас есть данные о продажах шоколада. Рассчитаем различные варианты продаж.
В I3 записано:
=СУММЕСЛИ(D2:D21;I2)
D2:D21 – это координаты, в которых мы ищем значение.
I2 – ссылка на критерий отбора. Иначе говоря, мы ищем ячейки со значением 144 и складываем их.
Поскольку третий параметр функции не указан, то мы сразу складываем отобранные числа. Область поиска будет одновременно являться и диапазоном суммирования.
Кроме того, в качестве задания для отбора нужных значений можно указать текстовое выражение, состоящее из знаков >, <, <>, <= или >= и числа.
Можно указать его прямо в формуле, как это сделано в I13
=СУММЕСЛИ(D2:D21;«<144»)
То есть подытоживаем все заказы, в которых количество меньше 144.
Но, согласитесь, это не слишком удобно, поскольку нужно корректировать саму формулу, да и условие еще нужно не забыть заключить в кавычки.
В дальнейшем мы будем стараться использовать только ссылку на критерий, поскольку это значительно упрощает возможные корректировки.
Критерии для текста.
Гораздо чаще встречаются ситуации, когда поиск нужно проводить в одном месте, а в другом — суммировать данные, соответствующие найденному.
Чаще всего это необходимо, если необходимо использовать отбор по определённым словам. Ведь текстовые значения складывать нельзя, а вот соответствующие им числа – можно.
Как простой прием использования формулы СУММ ЕСЛИ в Эксель таблицах, рассчитаем итог по выполненным заказам.
В I3 запишем выражение:
=СУММЕСЛИ(F2:F21;I2;E2:Е21)
F2:F21 – это область, в которой мы отбираем подходящие значения.
I2 – здесь записано, что именно отбираем.
E2:E21 – складываем числа, соответствующие найденным совпадениям.
Конечно, можно указать параметр отбора прямо в выражении:
=СУММЕСЛИ(F2:F21;”Да”;E2:Е21)
Но мы уже договорились, что так делать не совсем рационально.
Важное замечание. Не забываем, что все текстовые значения необходимо заключать в кавычки.
Подстановочные знаки для частичного совпадения.
При работе с текстовыми данными часто приходится производить поиск по какой-то части слова или фразы.
Вернемся к нашему случаю. Определим, сколько всего было заказов на черный шоколад. В результате, у нас есть 2 подходящих наименования товара. Как учесть их оба? Для этого есть понятие неточного соответствия.
Мы можем производить поиск и подсчет значений, указывая не всё содержимое ячейки, а только её часть. Таким образом мы можем расширить границы поиска, применив знаки подстановки “?”, “*”.
Символ “?” позволяет заменить собой один любой символ.
Символ ”*” позволяет заменить собой не один, а любое количество символов (в том числе ноль).
Эти знаки можно применить в нашем случае двумя способами. Либо прямо вписать их в таблицу –
=СУММЕСЛИ(C2:C21;I2;E2:Е21) , где в E2 записано *[слово]*
либо
=СУММЕСЛИ(C2:C21;»*»&I2&»*»;E2:E21)
где * вставлены прямо в выражение и «склеены» с нужным текстом.
Давайте потренируемся:
- “*черный*” — мы ищем фразу, в которой встречается это выражение, а до него и после него – любые буквы, знаки и числа. В нашем случае этому соответствуют “Черный шоколад” и “Супер Черный шоколад”.
- “Д?” — необходимо слово из 2 букв, первая из которых “Д”, а вторая – любая. В нашем случае подойдет “Да”.
- “???” — найдем слово из любых 3 букв
=СУММЕСЛИ(F2:F21;”???”;E2:E21)
Этому требованию соответствует “Нет”.
- “???????*” — текст из любых 7 и более букв.
=СУММЕСЛИ(B2:B21;“???????*”;E8:E28)
Подойдет “Зеленый”, “Оранжевый”, “Серебряный”, “Голубой”, “Коричневый”, “Золотой”, “Розовый”.
- “З*” — мы выбираем фразу, первая буква которой “З”, а далее – любые буквы, знаки и числа. Это “Золотой” и “Зеленый”.
- “Черный*” — подходит фраза, которая начинаются именно с этого слова, а далее – любые буквы, знаки и числа. Подходит “Черный шоколад”.
Примечание. Если вам необходимо в качестве задания для поиска применять текст, который содержит в себе * и ?, то используйте знак тильда (~), поставив его перед этими символами. Тогда * и ? будут считаться обычными символами, а не шаблоном:
=СУММЕСЛИ(B2:B21;“*~?*”;E8:E28)
Важное замечание. Если в вашем тексте для поиска встречается несколько знаков * и ?, то тильду (~) нужно поставить перед каждым из них. К примеру, если мы будем искать текст, состоящий из трех звездочек, то формулу ЕСЛИ СУММ можно записать так:
=СУММЕСЛИ(B2:B21;“~*~*~*”;E8:E28)
А если текст просто содержит в себе 3 звездочки, то можно наше выражение переписать так:
=СУММЕСЛИ(B2:B21;“*~*~*~**”;E8:E28)
Точная дата либо диапазон дат.
Если нам нужно найти сумму чисел, соответствующих определённой дате, то проще всего в качестве критерия указать саму эту дату.
Примечание. При этом не забывайте, что формат указанной вами даты должен соответствовать региональным настройкам вашей таблицы!
Обратите внимание, что мы также можем здесь вписать ее прямо в формулу, а можем использовать ссылку.
Рассчитываем итог продаж за сегодняшний день – 04.02.2020г.
=СУММЕСЛИ(A2:A21;I1;E2:E21)
или же
=СУММЕСЛИ(A2:A21;СЕГОДНЯ();E2:E21)
Рассчитаем за вчерашний день.
=СУММЕСЛИ(A2:A21;СЕГОДНЯ()-1;E2:E21)
СЕГОДНЯ()-1 как раз и будет «вчера».
Складываем за даты, которые предшествовали 1 февраля.
=СУММЕСЛИ(A2:A21;»<«&»01.02.2020»;E2:E21)
После 1 февраля включительно:
=СУММЕСЛИ(A2:A21;»>=»&»01.02.2020″;E2:E21)
А если нас интересует временной интервал «от-до»?
Мы можем рассчитать итоги за определённый период времени. Для этого применим маленькую хитрость: разность функций СУММЕСЛИ. Предположим, нам нужна выручка с 1 по 4 февраля включительно. Из продаж после 1 февраля вычитаем все, что реализовано после 4 февраля.
=СУММЕСЛИ(A2:A21;»>=»&»01.02.2020″;E2:E21) — СУММЕСЛИ(A2:A21;»>=»&»04.02.2020″;E2:E21)
Сумма значений, соответствующих пустым либо непустым ячейкам
Случается, что в качестве условия суммирования нужно использовать все непустые клетки, в которых есть хотя бы одна буква, цифра или символ.
Рассмотрим ещё один вариант использования формулы СУММ ЕСЛИ в таблице Excel, где нам необходимо подсчитать заказы, в которых нет отметки о выполнении, а также сколько было вообще обработанных заказов.
Если критерий указать просто “*”, то мы учитываем для подсчета непустые ячейки, в которых имеется хотя бы одна буква или символ (кроме пустых).
=СУММЕСЛИ(F2:F21;»*»;E2:E21)
Точно такой же результат даёт использование вместо звездочки пары знаков «больше» и «меньше» — <>.
=СУММЕСЛИ(F2:F21;»<>»;E2:E21)
Теперь рассмотрим, как можно находить сумму, соответствующую пустым ячейкам.
Для того, чтобы найти пустые, в которых нет ни букв, ни цифр, в качестве критерия поставьте парные одинарные кавычки ‘’, если значение критерия указано в ячейке, а формула ссылается на неё.
Если же указать на отбор только пустых ячеек в самой формуле СУММ ЕСЛИ, то впишите двойные кавычки.
=СУММЕСЛИ(F2:F21;«»;E2:E21)
Сумма по нескольким условиям.
Функция СУММЕСЛИ может работать только с одним условием, как мы это делали ранее. Но очень часто случается, что нужно найти совокупность данных, удовлетворяющих сразу нескольким требованиям. Сделать это можно как при помощи некоторых хитростей, так и с использованием других функций. Рассмотрим все по порядку.
Вновь вернемся к нашему случаю с заказами. Рассмотрим два условия и посчитаем, сколько всего сделано заказов черного и молочного шоколада.
1. СУММЕСЛИ + СУММЕСЛИ
Все просто:
=СУММЕСЛИ($C$2:$C$21;»*»&H3&»*»;$E$2:$E$21)+СУММЕСЛИ($C$2:$C$21;»*»&H4&»*»;$E$2:$E$21)
Находим сумму заказов по каждому виду товара, а затем просто их складываем. Думаю, с этим вы уже научились работать :).
Это самое простое решение, но не самое универсальное и далеко не единственное.
2. СУММ и СУММЕСЛИ с аргументами массива.
Вышеупомянутое решение очень простое и может выполнить работу быстро, когда критериев немного. Но если вы захотите работать с несколькими, то она станет просто огромной. В этом случае лучшим подходом является использование в качестве аргумента массива критериев. Давайте рассмотрим этот подход.
Вы можете начать с перечисления всех ваших условий, разделенных запятыми, а затем заключить итоговый список, разделенный точкой с запятой, в {фигурные скобки}, который технически называется массивом.
Если вы хотите найти покупки этих двух товаров, то ваши критерии в виде массива будут выглядеть так:
СУММЕСЛИ($C$2:$C$21;{«*черный*»;»*молочный*»};$E$2:$E$21)
Поскольку здесь использован массив критериев, то результатом вычислений также будет массив, состоящий из двух значений.
А теперь воспользуемся функцией СУММ, которая умеет работать с массивами данных, складывая их содержимое.
=СУММ(СУММЕСЛИ($C$2:$C$21;{«*черный*»;»*молочный*»};$E$2:$E$21))
Важно, что результаты вычислений в первом и втором случае совпадают.
3. СУММПРОИЗВ и СУММЕСЛИ.
А если вы предпочитаете перечислять критерии в какой-то специально отведенной для этого части таблицы? Можете использовать СУММЕСЛИ в сочетании с функцией СУММПРОИЗВ, которая умножает компоненты в заданных массивах и возвращает сумму этих произведений.
Вот как это будет выглядеть:
=СУММПРОИЗВ(СУММЕСЛИ(C2:C21;H3:H4;E2:E21))
в H3 и H4 мы запишем критерии отбора.
Но, конечно, ничто не мешает вам перечислить значения в виде массива критериев:
=СУММПРОИЗВ(СУММЕСЛИ(C2:C21;{«*черный*»;»*молочный*»};E2:E21))
Результат, возвращаемый в обоих случаях, будет идентичен тому, что вы наблюдаете на скриншоте.
Важное замечание! Обратите внимание, что все перечисленные выше три способа производят расчет по логическому ИЛИ. То есть, нам нужны продажи шоколада, который будет или черным, или молочным.
Почему СУММЕСЛИ у меня не работает?
Этому может быть несколько причин. Иногда ваше выражение не возвращает того, что вы ожидаете, только потому, что тип данных в ячейке или в каком-либо аргументе не подходит для нее. Итак, вот что нужно проверить.
1. «Диапазон данных» и «диапазон суммирования» должны быть указаны ссылками, а не в виде массива.
Первый и третий атрибуты функции всегда должны быть ссылкой на область таблицы, например A1: A10. Если вы попытаетесь передать что-нибудь еще, например, массив {1,2,3}, Excel выдаст сообщение об ошибке.
Правильно: =СУММЕСЛИ(A1:A3, «цвет», C1:C3)
Неверно : =СУММЕСЛИ({1,2,3}, «цвет», C1:C3)
2. Ошибка при суммировании значений из других листов или рабочих книг.
Как и любая другая функция Excel, СУММЕСЛИ может ссылаться на другие листы и рабочие книги, если они в данный момент открыты.
Найдем сумму значений в F2: F9 на листе 1 книги 1, если соответствующие данные записаны в столбце A, и если среди них содержатся «яблоки»:
=СУММЕСЛИ([Книга1.xlsx]Лист1!$A$2:$A$9,»яблоки»,[Книга1.xlsx]Лист1!$F$2:$F$9)
Однако это перестанет работать, как только Книга1 будет закрыта. Это происходит потому, что области, на которые ссылаются формулы в закрытых книгах, преобразуются в массивы и хранятся в таком виде в текущей книге. А поскольку в аргументах 1 и 3 массивы не допускаются, то формула выдает ошибку #ЗНАЧ!.
3. Чтобы избежать проблем, убедитесь, что диапазоны данных и поиска имеют одинаковый размер.
Как отмечалось в начале этого руководства, в современных версиях Microsoft Excel они не обязательно должны иметь одинаковый размер. Но вот в Excel 2000 и более ранних версиях это может вызвать проблемы. Однако, даже в самых последних версиях Excel сложные выражения, в которых диапазон сложения имеет меньше строк и/или столбцов, чем диапазон поиска, являются капризными. Вот почему рекомендуется всегда иметь их одинакового размера и формы.
Примеры расчета суммы:
Сумма в Excel — одна из самых частых операций. Однако зачастую недостаточно просто просуммировать все ячейки, а нужно сделать сумму ячеек, соответствующих определенному условию или нескольким условиям. В этой статье рассмотрим, как сделать суммирование ячеек в Excel по условию и по нескольким условиям.
- Функция СУММЕСЛИ в Excel для суммирования по одному условию
- Суммирование по нескольким условиям в Excel при помощи функции СУММЕСЛИМН
- Суммирование по текстовому условию
- Суммирование по числовому условию
- Суммирование по условию даты
- Почему СУММЕСЛИ или СУММЕСЛИМН не работает
- СУММЕСЛИ или СУММЕСЛИМН в Excel — что лучше использовать?
Чтобы сделать суммирование ячеек в Excel по одному условию, можно использовать функцию СУММЕСЛИ (SUMIF в англ. версии).
Синтаксис функции:
=СУММЕСЛИ(диапазон;критерий;[диапазон_суммирования])
диапазон — область ячеек, в которых будет осуществляться поиск критерия суммирования
критерий — значение, по которому будет осуществляться поиск в диапазоне. Может быть текстом, числом, ссылкой на ячейку или шаблоном.
[диапазон_суммирования] — необязательный параметр, указывается в том случае, когда совпадение с критерием ищем в одном диапазоне,а суммируем значения из другого. Если этот параметр не указан, то будут суммироваться значения из первой области диапазон (если они могут быть просуммированы).
Рассмотрим функцию СУММЕСЛИ в Excel на примере.
У нас есть таблица с данными о сотрудниках.
Получим сумму заработной платы у всех сотрудников мужского пола.
Способ 1. С указанием критерия напрямую в формуле.
В ячейке, в которой нужно получить результат суммирования по условию, напишем формулу:
=СУММЕСЛИ(D3:D12;»муж»;G3:G12)
D3:D12 — диапазон, в котором будет происходить поиск совпадения с критерием. В нашем примере это столбец Пол.
«муж» — критерий поиска. Поскольку это текстовый критерий, он заключен в кавычки.
G3:G12 — диапазон, значения из которого будут просуммированы при наличии совпадений диапазона с критерием. В примере это столбец Заработная плата.
Способ 2. С указанием критерия ссылкой на ячейку
Этот способ наиболее распространен и универсален.
В ячейке слева от рабочей укажем критерий поиска (без кавычек!).
Формула будет выглядеть так:
=СУММЕСЛИ(D3:D12;I4;G3:G12)
где первый и третий аргументы — такие же, как в предыдущем способе.
I4 — это ссылка на ячейку, в которой содержится критерий суммирования значений.
Если формулу нужно “протянуть” (скопировать) в другие ячейки — не забывайте закреплять диапазоны и критерий абсолютными или относительными ссылками (знак $).
Суммирование по нескольким условиям в Excel при помощи функции СУММЕСЛИМН
Если критерием для суммирования больше одного, то на помощь придет функция СУММЕСЛИМН (SUMIFS в англ. версии).
Синтаксис функции:
=СУММЕСЛИМН(диапазон_суммирования; диапазон_условия 1; условие 1;…;диапазон_условия n; условие n)
У функции СУММЕСЛИМН такие же аргументы, как у СУММЕСЛИ, но немного другой их порядок — на первом месте указывает диапазон суммирования (область значений, которые должны быть просуммированы), а затем — диапазон условий и условие поиска.
Рассмотрим на примере: нужно просуммировать заработную плату всех женщин бухгалтеров.
Соответственно, условия суммирований будут:
Должность: Бухгалтер
Пол: жен
Формула суммирования по нескольким условиям в Excel будет такая:
=СУММЕСЛИМН(G3:G12;C3:C12;I4;D3:D12;J4)
где G3:G12 — диапазон суммирования
C3:C12 — первый диапазон условия, в котором будет искаться первое условие
I4 — ссылка на ячейку, содержащую первое условие поиска
D3:D12 — второй диапазон условия, в котором будет искаться второе условие
J4 — ссылка на ячейку, содержащую второе условие поиска
Как видите, Excel верно просуммировал заработную плату всех сотрудников с полом жен и должностью Бухгалтер.
Обратите внимание:
- Функцию СУММЕСЛИМН можно использовать только для одного условия поиска (не обязательно их должно быть несколько)
- для функции СУММЕСЛИМН действуют те же правила, как и для функции СУММЕСЛИ (регистр критерия поиска, абсолютные и относительные ссылки)
- условия поиска можно располагать в любом порядке. В примере мы указали сначала условие Должность, затем условие Пол — можно сделать наоборот.
Главное правило: диапазон условия и условие, которое идет следом за ним, должны соответствовать друг другу.
- условия поиска также можно указывать как в самой формуле, так и ссылкой на ячейку.
- критерии поиска в стандартном варианте должны находиться в разных столбцах. Если несколько критериев суммирования находятся в одном столбце, то нужно использовать формулу массива.
Суммирование по текстовому условию
Пример суммирования по текстовому условию мы рассмотрели в первом пункте этой статьи.
Текстовые значения суммировать сами по себе нельзя, но можно получить сумму соответствующих им чисел.
Рассмотрим простой пример использования функции СУММЕСЛИ в эксель таблице: просуммируем заработную плату всех кассиров в таблице из предыдущего примера.
Точно так же можно сделать сумму значений по условию, указав критерий прямо в формуле. Текстовый критерий обязательно нужно заключить в кавычки!
Обратите внимание:
- функции СУММЕСЛИ и СУММЕСЛИМН не чувствительны к регистру критерия. Например, сейчас критерий поиска Кассир
можно указать критерий с большой буквы или даже всеми большими буквами, например:
Как видите, формула работает и выдает тот же результат.
Но если добавить любой знак, например пробел или точку в конце — формула не будет работать, т.к. нет точного совпадения критерия со значениями диапазона для поиска.
- в качестве критерия всегда лучше использовать ссылку на ячейку, а не указывать его непосредственно в формуле. Так будет проще корректировать формулу, и меньше вероятности совершить ошибку при указании критерия. К тому же, в этом случае не нужно заключить в кавычки критерий поиска.
Поэтому в следующих примерах будем использовать только вариант со ссылкой на ячейку — лучше сразу приучить себя к такому способу.
СУММЕСЛИ или СУММЕСЛИМН по части текста
А вы знали, что можно сделать суммирование ячеек в Excel по одному или нескольким текстовым условиям, указав только часть текста? Подробно этот вариант рассмотрен в статье.
Например, получим сумму заработных плат сотрудниц по имени Елена. Критерием поиска в данном случае будет имя Елена — но ведь в нашей таблице нет столбца, в котором находится имя. Только ФИО. Однако, не нужно создавать дополнительных столбцов и разделять ФИО на компоненты.
Напишем следующую формулу:
=СУММЕСЛИ(B3:B12;»*елена*»;G3:G12)
Здесь критерием поиска будет «*елена*».
Используются два значка-оператора — кавычки и звездочка.
- звездочка * — заменяет любое количество символов. Т.е. перед и после слова елена может находиться любое количество любых символов.
- кавычки «» — обязательно ставятся для текстового аргумента поиска в функциях excel.
Вместо звездочки можно использовать знак вопроса ?. Отличие — символ * заменяем любое количество символов, а ? — только один символ.
Суммирование по числовому условию в Excel
Как правило, нужно определить сумму если больше чем, меньше чем или равно определенному числу.
Сумма чисел, если больше или меньше чем заданное число
Задача: получить сумму заработных плат, если величина заработной платы больше 50000 руб.
Формула будет такая:
=СУММЕСЛИ(G3:G12;»>50000″)
где G3:G12 — диапазон для поиска заданных значений и одновременно диапазон суммирования, поэтому третий критерий (не обязательный) в формуле не указывается.
«>50000» — критерий поиска.
Обратите внимание, что, несмотря на то, что критерий не текстовый, а числовой, используются кавычки.
Обязательно использовать кавычки, если в формуле применяются знаки больше > или меньше < ( а также >= и <=)
Если в качестве критерия используется знаки >, <, >=, <= и ссылка на ячейку, содержащую критерий (например >50000), то критерий поиска будет записан таким образом:
Знак сравнения должен быть заключен в кавычки, далее ставится амперсанд & и указывается ссылка на ячейку с критерием.
Сумма чисел, если равно заданному числу
В этом случае кавычки для критерия не используются.
Получим сумму заработных плат, равных 45000 руб.
Критерий поиска 45000 указывается без кавычек.
Также, если критерий указывается в ячейке, то формула будет как для текстового критерия:
Суммирование по условию даты
Чтобы сделать суммирование ячеек в excel по условию даты — равно определенной дате или больше или меньше определенной даты — то нужно указать эту дату в качестве критерия.
При этом дату также можно указывать непосредственно в формуле, а также ссылкой на ячейку, содержащую дату.
Для примера определим сумму заработных плат сотрудников, трудоустроенных после 1 января 2018 г.
Формула:
=СУММЕСЛИ(F3:F12;»>=»&I4;G3:G12)
Обратите внимание, как указан критерий — знак сравнения “больше или равно” заключен в кавычки и используется знак амперсанд &.
Если дату указывать напрямую в формуле, то ее нужно также заключить в кавычки:
Почему СУММЕСЛИ или СУММЕСЛИМН не работает
Формула СУММЕСЛИ иногда может выдавать ошибку #ЗНАЧ, если диапазон суммирования и диапазон условий имеют разный размер. Эта проблема решена в новых версиях Excel, но в ранних версиях она присутствует.
Также ошибка #ЗНАЧ может возникать, если формула ссылается на данные из другого файла, а этот файл в момент пересчета формулы закрыт. Необходимо просто открыть файл-источник, и ошибка исчезнет.
Если функция СУММЕСЛИ или СУММЕСЛИМН выдает значение 0 (при условии, что значение суммы никак не может быть равно 0), необходимо проверить критерий — не закрались ли в него лишние пробелы или знаки препинания.
СУММЕСЛИ или СУММЕСЛИМН в Excel — что лучше использовать?
Как вы могли заметить, эти функции похожи между собой.
В своей практике, я часто замечаю, что пользователи разграничивают действие этих функций: для суммирования по одному критерию используют строго функцию СУММЕСЛИ, по нескольким критериям — СУММЕСЛИМН.
Но обратите внимание:
Функцию СУММЕСЛИМН можно использовать для суммирования как по одному критерию, так и по нескольким критериям.
Другими словами, функция СУММЕСЛИМН универсальная. И функцию СУММЕСЛИ таким образом можно вообще не использовать, заменив ее СУММЕСЛИМН.
Почему СУММЕСЛИМН удобнее при суммировании по одному критерию?
Потому что условия задачи всегда могут измениться. Сейчас вы суммируете по одному критерию (например, по должности), а в следующий момент понадобилось добавить еще одно условие (пол). В случае с функцией СУММЕСЛИМН вы просто внесете дополнительное условие в ту же формулу, а функция СУММЕСЛИ здесь вовсе не подойдет (придется все равно переписывать на СУММЕСЛИМН).
Поэтому совет практика: использовать только функцию СУММЕСЛИМН.
Однако, окончательное решение в выборе функции всегда остается за вами.
В этой статье мы узнали, как сделать суммирование ячеек в excel по условию и познакомились с возможностью сделать суммирование по нескольким условиям.
Сообщество Excel Analytics | обучение Excel
Канал на Яндекс.Дзен
Вам может быть интересно:
На чтение 8 мин. Просмотров 36.3k.
Содержание
- Сумма, если ячейки содержат звездочку
- Сумма, если ячейки содержат X и Y
- Сумма, если ячейки содержат либо x, либо Y
- Сумма, если ячейки содержат определенный текст
- Сумма, если ячейка заканчивается определенным текстом
- Сумма, если равен х или у
- Сумма, если ячейки не пустые
Сумма, если ячейки содержат звездочку
=СУММЕСЛИ(диапазон;»*~**»;ранг_суммы)
Чтобы посчитать сумму, если ячейки содержат звездочку, можно использовать функцию СУММЕСЛИ со специальным символом «тильда» — «~».
В примере показано, ячейка G6 содержит эту формулу:
=СУММЕСЛИ(С5:С11;»*~**»;D5:D11)
Эта формула суммирует суммы в столбце D, когда значение в столбце C содержит «*».
Функция СУММЕСЛИ поддерживает подстановочные знаки. Звездочка (*) означает «один или более символов», а вопросительный знак (?) означает «любой один символ».
Эти шаблоны позволяют создавать такие критерии, как «начинается с», «кончается на», «содержит 3 символов» и так далее.
Потому как звездочки и вопросительные знаки, сами по себе являются подстановочными знаками, если вы хотите искать эти символы в частности, вы должны защитить их с тильдой (~). Тильда причины Excel следует рассматривать следующий символ буквально.
В этом случае мы используем «~*», чтобы соответствовать символу звездочки, но он окружен звездочками по бокам, для того чтобы соответствовать звездочкам в любом месте ячейки. Если вы просто хотите, чтобы соответствовало звездочкам на конце ячейки, используйте: «*~**» по критериям.
Альтернатива с СУММЕСЛИМН
Вы также можете использовать функцию СУММЕСЛИМН. СУММЕСЛИМН может обрабатывать несколько критериев, и порядок аргументов отличается от СУММЕСЛИ. С СУММЕСЛИМН, диапазон сумма всегда стоит на первом месте в списке аргументов, затем пара/диапазон критериев:
=СУММЕСЛИМН(D5:D11;С5:С11;»*~**»)
Сумма, если ячейки содержат X и Y
=СУММЕСЛИМН(диап1;диап2;»*кошка*»;диап2;»*крыса*»)
Чтобы посчитать сумму, если ячейки содержат X и Y (т. е. содержат «кошка» и «крыса», в одной ячейке) можно использовать функцию СУММЕСЛИМН.
В показанном примере, формула в F5:
=СУММЕСЛИМН(С5:С9;В5:В9;»*кошка*»;В5:В9;»*крыса*»)
Функция СУММЕСЛИМН основана на логике и такое поведение является автоматическим. Нам просто нужно поставить два диапазона/критерии пары, работающие на одном диапазоне (В5:В9).
Для обоих критериев (содержит «крыса», содержится «кошка») мы используем звездочку, которая является подстановочным, что означает «один или несколько символов». Мы ставим звездочку в начале и в конце, чтобы позволить формуле проверить, чтобы соответствовало «кошка» и «крыса» везде, где они появляются в ячейке.
Когда оба критерия рассчитывает Истина в том же ряду, СУММЕСЛИМН суммирует значения в столбец C.
Обратите внимание, что СУММЕСЛИМН не чувствителен к регистру.
Сумма, если ячейки содержат либо x, либо Y
=СУММПРОИЗВ(—((ЕЧИСЛО(ПОИСК(«кошка»;диап1)) + ЕЧИСЛО(ПОИСК(«крыса»;диап1)))>0);диап2)
Чтобы посчитать сумму, если ячейки содержат либо одну текстовую строку или другую (т. е. содержащие «кошка» или «крыса») вы можете использовать функция СУММПРОИЗВ.
Когда вы суммируете ячейки с критерием «или», вы должны быть осторожны, чтобы не произошел двойной счет, когда существует вероятность того, что оба критерия будет рассчитывать Истина. В показанном примере, мы хотим просуммировать значения в столбце C, когда ячейки в столбце B содержат слово «кошка» или «крыса». Мы не можем использовать СУММЕСЛИМН по двум критериям, т. к. СУММЕСЛИМН основан на логике. И если мы попытаемся использовать две функции СУММЕСЛИМН (т.е. СУММЕСЛИМН + СУММЕСЛИМН) у нас будет двойной счет, потому что есть клетки, которые содержат как «кошка», так и «крыса»
Одно из решений-использовать функцию СУММПРОИЗВ с ЕЧИСЛО + ПОИСК или НАЙТИ.
=СУММПРОИЗВ(—((ЕЧИСЛО(ПОИСК(«кошка»;В5:В9)) + ЕЧИСЛО(ПОИСК(«крыса»;В5:В9)))>0);С5:С9)
Эта формула основана на формуле, что находит текст внутри ячейки:
ЕЧИСЛО(ПОИСК(«Азбука»;В4:В10)
При заданном диапазоне ячеек, этот фрагмент будет рассчитывать массив значений Истина или Ложь, одно значение для каждой ячейки диапазона. Поскольку мы делаем это дважды (один раз для «кошки» и один раз для «крысы»), мы получим два массива.
Далее, мы добавляем эти массивы вместе (с +), которая создает новый единый массив чисел. Каждое число в этом массиве является результатом добавления истинных и ложных значений в исходных двух массивах вместе. В показанном примере, массив выглядит так:
{2;0;2;1;0}
Нам нужно добавить эти цифры, но мы не хотим, чтобы удвоился счет. Поэтому мы должны убедиться, что любое значение больше нуля, всего лишь раз пересчитать. Чтобы сделать это, мы превращаем все значения Истина или Ложь, проверяя массив с «>0». Это рассчитывает Истина или Ложь:
{ИСТИНА;ЛОЖЬ;ИСТИНА;ИСТИНА;ЛОЖЬ}
Которые мы затем преобразовываем в 1/0 с помощью двойного отрицания (—):
{1;0;1;1;0}
Параметр чувствителен к регистру
Функция поиска не учитывает регистр. Если вам нужен чувствительный вариант, замените поиск на найти.
Сумма, если ячейки содержат определенный текст
=СУММЕСЛИ(диапазон;»*текст*»;ранг_суммы)
Чтобы посчитать сумму, если ячейки содержат определенный текст, вы можете использовать функцию СУММЕСЛИ с подстановкой.
В примере показано, ячейка G4 содержит эту формулу:
=СУММЕСЛИ(С5:С11;»*футболка*»; D5:D11)
Эта формула суммирует суммы в столбце D, когда значение в столбце C содержит «футболка». Обратите внимание, что СУММЕСЛИ-это не регистр.
Функция СУММЕСЛИ поддерживает подстановочные знаки. Звездочка (*) означает «один или более символов», а вопросительный знак (?) означает «любой один символ».
Эти шаблоны позволяют создавать такие критерии, как «начинается с», «кончается на», «содержит 3 символа» и так далее.
Нужно, чтобы соответствовали все элементы, которые содержат слово «футболка», критериям»*футболка*». Обратите внимание, что вы должны заключить текст и подстановочные знаки в двойные кавычки («»).
Альтернатива с СУММЕСЛИМН
Вы также можете использовать функцию СУММЕСЛИМН. СУММЕСЛИМН может обрабатывать несколько критериев, и порядок аргументов отличается от СУММЕСЛИ. Эквивалентная формула СУММЕСЛИМН:
=СУММЕСЛИМН(D5:D11;С5:С11;»*футболка*»)
Обратите внимание, что диапазон суммирования всегда стоит первым в функции СУММЕСЛИМН.
Сумма, если ячейка заканчивается определенным текстом
=СУММЕСЛИ(диапазон;»текст»;ранг_суммы)
В сумме, если ячейки заканчиваются определенным текстом, можно использовать функцию СУММЕСЛИ.
В примере показано, ячейка G5 содержит эту формулу:
=СУММЕСЛИ(C5:C11; «*шапка»; D5:D11)
Эта формула суммирует ячейки именованного диапазона сумма (D5:D11), только если ячейки именованного диапазона (С5:С11) оканчиваются на «шапка».
Обратите внимание, что СУММЕСЛИ не поддерживает регистр. Критерию «*шапка» соответствует любой текст, который заканчивается «Шапка» или «шапка».
Функция СУММЕСЛИ поддерживает подстановочные знаки. Звездочка (*) означает «один или более символов», а вопросительный знак (?) означает «любой один символ».
Эти шаблоны позволяют создавать такие критерии, как «начинается с», «кончается на», «содержит 3 символа» и так далее.
Чтобы соответствовали все элементы, которые оканчиваются на «шапка» перед текстом нужно поставить звездочку (*):
пункт;»*шапка»
Обратите внимание, что вы должны заключить текст и подстановочные знаки в двойные кавычки («»).
Альтернатива с СУММЕСЛИМН
Вы также можете использовать функцию СУММЕСЛИМН в сумме, если клетки начинаются с. СУММЕСЛИМН может обрабатывать несколько критериев, и порядок аргументов отличается от СУММЕСЛИ. Эквивалентная формула СУММЕСЛИМН:
=СУММЕСЛИМН(объем; номенклатура;»*шапка»)
Обратите внимание, что диапазон суммирования всегда стоит первым в функции СУММЕСЛИМН.
Сумма, если равен х или у
Если вам необходимо суммировать числа, основанные на других ячейках равных либо одному значению или другому (х или у), вы можете использовать функцию СУММЕСЛИ.
В примере, мы подсчитываем все продажи либо от Западного или Северного региона. Формула в ячейке G6 является:
=СУММЕСЛИ(C5:C14; «Западный»; E5:E14)+СУММЕСЛИ(C5:C14; «Северный»; E5:E14)
Каждый экземпляр СУММЕСЛИ обеспечивает промежуточный итог, один для продаж на Западе, один для продаж на Севере. Формула просто добавляет эти два результата вместе.
СУММЕСЛИ с аргументом массива
Более элегантное решение, чтобы дать функции СУММЕСЛИ более одного значения для критериев, использовать константу массива. Чтобы сделать это, постройте нормальный СУММЕСЛИ, но пакет критериев в синтаксисе массива — фигурные скобки, с отдельными элементами, разделенные запятыми. И, наконец, обернуть всю функцию СУММЕСЛИ в функцию СУММ. Это необходимо, потому что СУММЕСЛИ будет рассчитывать один результат для каждого элемента массива критериев. Они должны быть добавлены вместе, чтобы получить один результат.
Формула:
= СУММ(СУММЕСЛИ( область ; { «Западный» ; «Северный» } ; сумма ))
СУММПРОИЗВ альтернатива
Вы можете также использовать СУММПРОИЗВ для подсчета ячеек с логикой ИЛИ. Формула:
=СУММПРОИЗВ( количество * (( регион = «Западный» ) + ( регион = «Северный» )))
Это также может быть записано в виде:
=СУММПРОИЗВ( количество * ( регион = { «Западный» ; «Северный» } ))
СУММПРОИЗВ не так быстра, как СУММЕСЛИ, но разница в скорости не заметна с меньшими наборами данных.
Сумма, если ячейки не пустые
=СУММЕСЛИ( диапазон ; «<>» ; суммарный_диапазон )
Для подсчита ячеек, когда определенные значения не являются пустыми, вы можете использовать функцию СУММЕСЛИ.
В показанном примере, ячейка G4 содержит следующую формулу:
=СУММЕСЛИ( C5: C11 ; «<>» ; D5: D11 )
Эта формула суммирует суммы в колонке D только тогда, когда значение в столбце С не пусто
Функция СУММЕСЛИ поддерживает все стандартные операторы Excel, в том числе не равно к, который вводится в <>.
При использовании оператора в критериях функции как СУММЕСЛИ, необходимо заключить его в двойные кавычки ( «»). При использовании только «<>» в качестве критерия, вы можете думать о значении как «не равно пустой», или «не пусто».
Альтернатива с СУММЕСЛИМН
Вы можете также использовать функцию суммы СУММЕСЛИМН, если ячейки не являются пустыми. СУММЕСЛИМН может обрабатывать несколько критериев, и порядок аргументов отличается от СУММЕСЛИ. Эта формула СУММЕСЛИМН эквивалентна формуле СУММЕСЛИ выше:
=СУММЕСЛИМН( D5: D11 ; C5: C11 ; «<>» )
С СУММЕСЛИМН диапазон сумма всегда стоит первым.
Многие работая с электронными таблицами EXCEL в определённый момент сталкиваются с вопросом «как в EXCEL сложить числа в ячейках по определённому условию». В один из солнечных дней я также столкнулся с данным вопросом, но на просторах интернета не нашёл ответ на данный вопрос, однако я настойчивый и решил изучить справку программы, которая меня и натолкнула на мысль, а далее методом проб и ошибок я составил формулу, и как оказалось формула элементарная.
Всё началось с того, что я решил учитывать свои ежемесячные расходы и для этого создал таблицу, которую приложил к данной статье, ведь и вам она может пригодиться.
Теперь постараюсь подробно расписать принцип создания формулы. У меня есть в отчёте детальная статистика и сводный отчёт. В детальной статистике я вписываю свои ежедневные расходы, а в сводном отчёте считается сумма расходов по определённым категориям и общая сумма расходов.
Для примера возьмём категорию расходов «Покупки в магазинах». Нам надо, чтобы EXCEL находил все затраты по данной категории в детальной статистике, суммировал расходы по данной категории и записывал полученную сумму в ячейку D10.
Сначала запишем готовую формулу, которую вставляем в ячейку D10, а потом начнём разбираться в деталях. Готовая формула выглядит следующим образом (только для нашей статьи):
Цветом выделены различные условия, чтобы было наглядней. Разберём по порядку. В процессе описания смотрите на картинку выше, чтобы было понятней. Делая снимок специально были захвачены буквы столбцов и цифры строк. Итак, приступаем.
- СУММЕСЛИ – этим условием мы говорим, что в ячейку надо записывать сумму значений определённых ячеек, если они соответствуют определённым условиям;
- $G$5:$G$300 – здесь мы указываем EXCEL, в каком столбце нам надо искать условие для выборки. В нашем случае поиск происходит в столбце G начиная со строки 5 и заканчивая строкой 300;
- («Покупки в магазинах») – здесь мы указываем искомое условие и по этому условию будут суммироваться значения ячеек, которые мы указываем далее…;
- $H$5:$H$300 – здесь мы указываем столбец, из которого будут браться числа для суммирования. В нашем случае значения берутся в столбце H начиная со строки 5 и заканчивая строкой 300.
Подводя итог можно сказать, что EXCEL суммирует только те значения из диапазона H5:H300, для которых соответствующие значения из диапазона G5:G300 равны «Покупки в магазинах» и записывает результат в ячейку D10.
Соответствующим образом можно в EXCEL сложить числа в ячейках по любому условию.
Знак $ в формуле используется для того, чтобы при копировании формулы с ячейки D10 в другие ячейки не происходило смещение. Рассмотрим пример формулы без знака $. К примеру, в ячейке D10 у нас вписана формула:
Далее мы хотим выводить сумму обедов в ячейке D11. Чтобы нам не переписывать формулу, нам можно копировать ячейку D10 и вставить в ячейку D11. Благодаря этому формула будет вставлена в D11, но тут мы можем заметить, что формула изменила значения заменив 5 на 6 и 300 на 301:
Произошло смещение. Если мы скопируем формулу в D12, то увидим уже смещение на 2 и так далее. Чтобы этого избежать мы формулу пишем со знаком $. Такие особенности EXCEL.
Скачать таблицу учёта ежемесячных расходов