Excel сумм со ссылками

Формулы могут работать с ячейками из других листов. Вам просто нужно указать перед ссылкой на ячейку имя листа и восклицательный знак. Например, следующая формула добавляет 12 к значению в ячейке С1, которая находится на листе Лист2: =Лист2!С1+12.

Что делать, если вам нужно вычислить сумму всех значений в С1, начиная с листа Лист2 и заканчивая листом Лист6? Следующая формула решает эту задачу: =СУММ(Лист2:Лист6!C1). В данном случае двоеточие разделяет название первого и последнего листов.

Для создания такой формулы выполните приведенные ниже действия.

1. Активизируйте ячейку, которая будет содержать формулу, и наберите =СУММ(.
2. Щелкните на вкладке первого листа (в нашем случае это Лист2) и выберите ячейку С1.
3. Нажмите Shift и щелкните на вкладке последнего листа (в нашем случае это Лист6).
4. Нажмите Enter, и формула будет введена в ячейку.

В шаге 2 вы можете выбрать диапазон, а не одну-единствениую ячейку. Например, эта формула возвращает сумму C1:F12 для всех листов от Лист2 до Лист6: =СУММ(Лист2:Лист6!С1:Р12).

Теперь я покажу вам интересный прием, который узнал при прочтении новостных конференций Excel и на обучение в Минске. Если вы хотите просуммировать одну и ту же ячейку со всех листов, кроме текущего, просто введите формулу наподобие этой: =СУММ('*'!C1). Звездочка служит подстановочным символом, который интерпретируется как «все листы, кроме этого одного». Когда вы нажмете клавишу Enter после ввода этой формулы, Excel преобразует формулу, чтобы она использовала фактические имена листов. Это работает, даже если активный лист находится где-нибудь в середине, между другими листами. Например, если книга состоит из шести листов и вы введете приведенную выше формулу в ячейку листа Лист3, Excel создаст следующую формулу: =СУММ(Лист1:Лист2!С1;Лист4:Лист6!C1).

Но это еще не все. Введите следующую формулу для нахождения суммы ячеек С1 во всех листах, которые начинаются со слова Регион: =СУММ('Регион*"!C1). Excel может преобразовать данную формулу во что-то наподобие этого: =СУММ(Регион1:Регион4!C1).

Вы также можете использовать подстановочный знак ? — он указывает на любой отдельный символ. Например, при вводе следующей формулы Excel создаст формулу, которая просуммирует значения, начиная с листа Лист1 и заканчивая листом Лист9 (названия, которые содержат одну цифру): =СУММ(‘Лист?’!C1). Этот прием не ограничивается функцией СУММ. Он работает и с другими функциями, такими как СРЗНАЧ, МИН, МАКС.

Как посчитать сумму с разных листов в Excel?

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

Приветствую всех, уважаемые читатели блога TutorExcel.Ru.

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

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

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

Как сложить листы в Excel?

Вариант 1. Ручной ввод.

Начнем с одной стороны с медленного варианта, но с другой стороны с более понятного и наглядного способа.

Для примера возьмем простую задачу и сложим данные ячейки A1 с трех листов: Лист1, Лист2 и Лист3. Для этого поочередно в формулу записываем ссылки на ячейку с разных листов и знаки сложения «+»:

Это совсем простой вариант. Еще один вариант записи можно аналогично реализовать с помощью функции СУММ:

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

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

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

Вариант 2. Полуавтоматический ввод.

Повторно воспользуемся функцией СУММ, но в этот раз запишем ссылку на листы через двоеточие:

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

Поэтому запись для сложения 10 или 20 листов будет примерно такой же — ИмяПервогоЛиста:ИмяПоследнегоЛиста!Диапазон.

Чуть более подробно остановимся на логике формирования формулы. Напомню, что оператор двоеточие в Excel служит для объединения диапазона, который образует ссылку на все ячейки находящиеся между первой и последней (включая сами ячейки). Например, диапазон A1:A10 обозначает двумерную ссылку, в которую попадают все ячейки между первой (A1) и последней (A10).

При добавлении еще одной размерности в виде листов мы получаем так называемую трехмерную ссылку. К примеру, диапазон Лист1:Лист3!A1:A10 содержит все ячейки от Лист1!A1:A10 до Лист3!A1:A10.

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

Особенности трехмерных ссылок

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

• Добавление/копирование листа. Если добавить новый лист или скопировать уже существующий и вставить его между первым и последним (используемых в формуле), то он автоматически будет участвовать в расчете.
  Т.е. сама формула не поменяется, а значение вполне может измениться, если на новом листе в тех же ячейках содержатся данные;
• Удаление листа. Аналогичный случай примеру выше. При удалении листа формула не изменится, а расчет поменяется в зависимости от удаленных данных (если удалить начальный или конечный листы, то формула изменится, промежуточный — формула останется неизменной);
• Перемещение листа. При перемещении листа в другое место книги формула не изменится, но поменяется набор листов между ними (либо добавятся новые, либо удалятся текущие).

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

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

Спасибо за внимание!
Если у вас есть мысли или вопросы по теме статьи — пишите и спрашивайте в комментариях.

Microsoft Excel

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

Как суммировать данные с разных листов книг Excel

Формулы могут работать с ячейками из других листов. Вам просто нужно указать перед ссылкой на ячейку имя листа и восклицательный знак. Например, следующая формула добавляет 12 к значению в ячейке С1 , которая находится на листе Лист2: =Лист2!С1+12 .

Что делать, если вам нужно вычислить сумму всех значений в С1, начиная с листа Лист2 и заканчивая листом Лист6? Следующая формула решает эту задачу: =СУММ(Лист2:Лист6!C1) . В данном случае двоеточие разделяет название первого и последнего листов.

Для создания такой формулы выполните приведенные ниже действия.

1. Активизируйте ячейку, которая будет содержать формулу, и наберите =СУММ( .
2. Щелкните на вкладке первого листа (в нашем случае это Лист2) и выберите ячейку С1 .
3. Нажмите Shift и щелкните на вкладке последнего листа (в нашем случае это Лист6).
4. Нажмите Enter, и формула будет введена в ячейку.

В шаге 2 вы можете выбрать диапазон, а не одну-единствениую ячейку. Например, эта формула возвращает сумму C1:F12 для всех листов от Лист2 до Лист6: =СУММ(Лист2:Лист6!С1:Р12) .

Теперь я покажу вам интересный прием, который узнал при прочтении новостных конференций Excel и на обучение в Минске. Если вы хотите просуммировать одну и ту же ячейку со всех листов, кроме текущего, просто введите формулу наподобие этой: =СУММ(‘*’!C1) . Звездочка служит подстановочным символом, который интерпретируется как «все листы, кроме этого одного». Когда вы нажмете клавишу Enter после ввода этой формулы, Excel преобразует формулу, чтобы она использовала фактические имена листов. Это работает, даже если активный лист находится где-нибудь в середине, между другими листами. Например, если книга состоит из шести листов и вы введете приведенную выше формулу в ячейку листа Лист3, Excel создаст следующую формулу: =СУММ(Лист1:Лист2!С1;Лист4:Лист6!C1) .

Но это еще не все. Введите следующую формулу для нахождения суммы ячеек С1 во всех листах, которые начинаются со слова Регион: =СУММ(‘Регион*»!C1) . Excel может преобразовать данную формулу во что-то наподобие этого: =СУММ(Регион1:Регион4!C1) .

Вы также можете использовать подстановочный знак ? — он указывает на любой отдельный символ. Например, при вводе следующей формулы Excel создаст формулу, которая просуммирует значения, начиная с листа Лист1 и заканчивая листом Лист9 (названия, которые содержат одну цифру): =СУММ(‘Лист?’!C1). Этот прием не ограничивается функцией СУММ. Он работает и с другими функциями, такими как СРЗНАЧ, МИН, МАКС.

Сумма одной и той же ячейки на нескольких листах

Задача: у вас есть 12 листов с одинаковой структурой данных, по одному на каждый месяц. Вы бы хотели просуммировать одну и ту же ячейку на каждом листе. Есть ли способ лучше, чем использование =Янв!B4+Фев!B4+Мар!B4+…+Дек!B4?

Решение: вы можете использовать «3D-формулу», например, =СУММ(Янв:Дек!B4), как показано на рис. 1.

Рис. 1. 3D-формулу для суммирования одной и той же ячейки на нескольких листах

Скачать заметку в формате Word или pdf, примеры в формате Excel

Если в имени первого и/или последнего листа содержится пробел, используйте апострофы вокруг пары имен листов: =СУММ( ‘ Янв 2009:Дек ‘ !B5), как показано на рис. 2. Любопытно, что, если пробел есть в имени любого промежуточного листа, апострофы не требуются. Более того, если вы введете апострофы, когда они не требуются, Excel автоматически их уберет.

Рис. 2. Используйте апострофы, если в имени листа есть пробел

Вы можете легко скопировать эту формулу в другие ячейки на листе Итоги.

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

Дополнительные сведения: можно создать именованный диапазон, который ссылается на 3D-область. Для этого перейдите к ячейке B4 на лист Янв. Пройдите по меню ФОРМУЛА –> Присвоить имя. В открывшемся окне Создание имени (рис. 3) перейдите в поле Диапазон путем многократного нажатия клавиши Табуляция. При этом всё содержимое поле Диапазон становится выделенным. Удерживая нажатой клавишу Shift кликните на лист Дек. Обратите внимание, что на рис. 3 все листы от Янв по Дек выделены. Нажмите Ok.

Рис. 3. Присвоение имени 3D-диапазону

После этого формулы приобретают «человеческий» вид. Например, =СУММ(Объем_продаж).

Как просуммировать данные с нескольких листов, в том числе по условию

В данной статье я хочу рассказать, как можно просуммировать данные на одном листе из других листов. К примеру: на листах Январь, Февраль и Март расположены данные по продажам, а под ними итог. Допустим, это будет ячейка D7. Если структура всех таблиц одинакова (одинаковое кол-во строк, товар может различаться) и Итог расположен во всех таблицах в одной ячейке, то можно воспользоваться простой формулой:
=СУММ(Январь:Март!D7)

Подобная ссылка на диапазоны называется трехмерной ссылкой. Если между листом Январь и Март добавить еще какой-нибудь лист — то данные с него будут также автоматически просуммированы. Поэтому необходимо следить, чтобы указывались только нужные листы. Минус в том, что таким образом можно просуммировать данные только ячеек листа, расположенных в одном и том же диапазоне(т.е. со всех листов будет взята сумма всех ячеек конкретного диапазона). Так же, трехмерные ссылки не могут быть созданы при помощи ДВССЫЛ(INDIRECT) для динамического указания имен первого и последнего листа.

Но, если необходимо будет просуммировать данные по отдельным товарам со всех листов, а товар расположен в хаотичном порядке, разном для каждого листа и количество строк различается, то здесь такая формула не подойдет. Можно воспользоваться формулой массива, которая несколько неудобна именно в таком виде:
=СУММПРОИЗВ(СУММЕСЛИ(ДВССЫЛ(<«Январь»:»Февраль»:»Март»:»Апрель»:»Май»:»Июнь»>&»!B3:B100″); B2 ;ДВССЫЛ(<«Январь»:»Февраль»:»Март»:»Апрель»:»Май»:»Июнь»>&»!C3:C100″)))

«Январь»:»Февраль»:»Март»:»Апрель»:»Май»:»Июнь» — имена листов, с которых происходит суммирование. Не буду останавливаться подробно на всех вложенных функциях. Про СУММЕСЛИ можно прочитать в этой статье. ДВССЫЛ используется для создания ссылки на диапазон, состоящей из имени листа и адреса ячеек: Январь!B3:B100, Февраль!B3:B100, Март!B3:B100, Апрель!B3:B100, Май!B3:B100, Июнь!B3:B100 . Т.е. мы в формуле переибраем все указанные листы и диапазоны в них.

Важно: если в имени листа встречается пробел, либо иной знак препинания, то имя листа необходимо заключать в апострофы: «‘Январь 2014′»:»‘Февраль 2014′»:»Март»:»Апрель»:»Май»:»Июнь»
либо ставить апострофы заранее для всех листов:
ДВССЫЛ(» ‘ «&<«Январь»:»Февраль»:»Март»:»Апрель»:»Май»:»Июнь»>&» ‘ !C3:C100″)

B3:B100 — диапазон с критериями(при необходимости указать больше строк).
C3:C100 — диапазон суммирования(при необходимости указать больше строк).

В чем главное неудобство — необходимо так или иначе указывать листы, с которых суммировать. Это очень неудобно, если листов много. Во вложении ниже эта формула представлена в двух вариантах: как выше и с использованием дополнительного столбца для перечисления листов (формула в данном случае получается более универсальной и короткой).
Скачать пример суммирования с листов формулой

Tips_All_SumIf_AllSheets_Formula.xls (67,5 KiB, 7 735 скачиваний)

Но в приложенном примере тоже стоит учитывать один момент: при ссылке на диапазон с именами листов, в этом диапазоне не должно быть:
1. Пустых ячеек
2. Имен листов, которые заведомо отсутствуют в книге
При несоблюдении этих правил формула вернет ошибку #ССЫЛКА (#REF!)

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

Function All_SumIf(rRange As Range, rCriteria As Range, rSumRange As Range, Optional bAllSh As Boolean = True) Dim wsSh As Worksheet, sRange As String, sSumRange As String sRange = Right(rRange.Address, Len(rRange.Address) — InStr(rRange.Address, «!»)) sSumRange = Right(rSumRange.Address, Len(rSumRange.Address) — InStr(rSumRange.Address, «!»)) For Each wsSh In Sheets If bAllSh Then If wsSh.Name <> Application.Caller.Parent.Name Then All_SumIf = All_SumIf + Application.SumIf(wsSh.Range(sRange), rCriteria, wsSh.Range(sSumRange)) End If Else If wsSh.Index Alt + F11 ) -создать стандартный модуль(Insert —Module) и в него вставить скопированный текст. После чего функцию можно будет вызвать из Диспетчера функций( Shift + F3 ), отыскав её в категории Определенные пользователем (User Defined Functions) .

Аргументы функции аналогичны стандартной СУММЕСЛИ, только в конце добавлен еще один, необязательный.

rRange — Ссылка на диапазон ячеек. Указывается диапазон значений, среди которых необходимо искать критерий.
rCriteria — Ссылка на одну ячейку. Указывается ячейка, в которой содержится значение, данные по которому надо просуммировать.
rSumRange — Ссылка на диапазон ячеек. Указывается диапазон сумм или чисел, которые необходимо просуммировать на основании критерия.
bAllSh — Необязательный аргумент. Если не указан, или указано значение 1 или ИСТИНА, то будут суммироваться значения со всех листов, кроме листа, на котором записана функция. Если указано значение 0 или ЛОЖЬ, то будут суммироваться значения с листов, расположенных до листа, на котором записана функция.

Применение обеих функций вы найдете в примере к статье.
Скачать пример

Tips_All_SumIf_Few_Sheets.xls (57,5 KiB, 3 745 скачиваний)

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

Function All_SumIf(rRange As Range, rCriteria As Range, rSumRange As Range, Optional sSheets = «») Dim wsSh As Worksheet, sRange As String, sSumRange As String, asSheets, li As Long sRange = Right(rRange.Address, Len(rRange.Address) — InStr(rRange.Address, «!»)) sSumRange = Right(rSumRange.Address, Len(rSumRange.Address) — InStr(rSumRange.Address, «!»)) If sSheets = «» Then For Each wsSh In Worksheets If wsSh.Name <> Application.Caller.Parent.Name Then sSheets = sSheets & «?» & wsSh.Name Next wsSh sSheets = Mid$(sSheets, 2) End If asSheets = Split(sSheets, «?») For li = LBound(asSheets) To UBound(asSheets) Set wsSh = Sheets(asSheets(li)) If Not wsSh Is Nothing Then All_SumIf = All_SumIf + Application.SumIf(wsSh.Range(sRange), rCriteria, wsSh.Range(sSumRange)) End If Next li End Function

rRange — Ссылка на диапазон ячеек. Указывается диапазон значений, среди которых необходимо искать критерий.
rCriteria — Ссылка на одну ячейку. Указывается ячейка, в которой содержится значение, данные по которому надо просуммировать.
rSumRange — Ссылка на диапазон ячеек. Указывается диапазон сумм или чисел, которые необходимо просуммировать на основании критерия.
sSheets — Необязательный аргумент. Указываются имена листов книги, с которых надо суммировать данные. Имена листов должны быть записаны через вопросительный знак: Февраль?Март. Если аргумент не указан или равен пустой ячейке, то будут суммироваться значения со всех листов, кроме листа, на котором записана функция.
Скачать пример

Tips_All_SumIf_Show_Sheets.xls (59,5 KiB, 2 626 скачиваний)

Очередное дополнение статьи — функция, в которой помимо перечисления листов можно указать книгу, в которой эти листы просматривать:

Function All_SumIf(rRange As Range, rCriteria As Range, rSumRange As Range, Optional sSheets = «», Optional wsAnotherWB As String = «») Dim wsSh As Worksheet, sRange As String, sSumRange As String, asSheets, li As Long Dim wbB As Workbook If wsAnotherWB = «» Then Set wbB = Application.Caller.Parent.Parent Else Set wbB = Workbooks(wsAnotherWB) End If sRange = Right(rRange.Address, Len(rRange.Address) — InStr(rRange.Address, «!»)) sSumRange = Right(rSumRange.Address, Len(rSumRange.Address) — InStr(rSumRange.Address, «!»)) If sSheets = «» Then For Each wsSh In wbB.Worksheets If wsSh.Name <> Application.Caller.Parent.Name Then sSheets = sSheets & «?» & wsSh.Name Next wsSh sSheets = Mid$(sSheets, 2) End If asSheets = Split(sSheets, «?») For li = LBound(asSheets) To UBound(asSheets) Set wsSh = wbB.Sheets(asSheets(li)) If Not wsSh Is Nothing Then All_SumIf = All_SumIf + Application.SumIf(wsSh.Range(sRange), rCriteria, wsSh.Range(sSumRange)) End If Next li End Function

Аргументы и их использование полностью совпадают с описанием выше. Опишу только последний аргумент:
wsAnotherWB — Необязательный аргумент. Указываются имя книги, в которой будут просматриваться листы, указанные параметром sSheets. Если аргумент wsAnotherWB не указан — листы просматриваются в книге, с листа которой вызвана функция. Если какого-либо из указанных листов не будет в указанной книге — функция вернет ошибку.

Статья помогла? Поделись ссылкой с друзьями!

Excel At Excel вып.3: Собираем данные с разных листов

Дано: 22 таблицы унифицированного формата с перечнем помещений от 22 дочерних предприятий.

Задача: сделать сводную таблицу с данными всех 22 таблиц

Итак, решение. Есть три варианта решения данной задачи. Первый — использовать встроенную (начиная с excel 2013) надстройку PowerPivot (об этом методе мы поговорим позднее). Второй — посредством сводной таблицы через несколько диапазонов консолидации. Не люблю этот метод, т.к. такая сводная таблица имеет существенно более ограниченный функционал по сравнению с простой сводной таблицей. Наконец, третий вариант — это создать общую таблицу с использованием функции INDIRECT (ДВССЫЛ). Этому методу и посвятим сегодня наш пост.

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

1) как заставить общую таблицу «переключаться» с одного листа данных на другой?;

2) как заставить ее делать это в нужный момент (когда закончились строки на одном листе)?

Начнем со второго вопроса, т.к. ответ на него мы уже знаем. Это циклы, о которых мы подробно говорили в выпуске 2 «Циклы в Excel без VBA». Для решения проблемы достаточно в наш файл добавить лист с перечнем всех обществ, соответствующими названиями листов и количеством подсчетом количества строк на каждом таком листе.

Обратите внимание на формулу в столбце Кол-во записей. Мы могли бы использовать просто COUNTA (СЧЁТА) со ссылкой на каждый лист. Но это потребует времени для «линковки» каждого листа. Плюс мы с вами готовим универсальные решения, которые будут работать в независимости от количества листов.

Именно для этого в функцию COUNTA (СЧЁТА) и внедряется эта чуднАя функция INDIRECT (ДВССЫЛ). Остановимся на ней подробнее.

Функция имеет, по сути, единственный параметр — ref_text (ссылка_на_текст). Что делает эта функция? Всего-навсего преобразует текст в ссылку на ячейку. То есть она преобразует текст, например, «А1» в ссылку и возвращает значение ячейки А1.

Соответственно, зная несложные правила работы с текстом в Excel, мы можем легко сделать текст внутри изменяемым, а значит можем сделать и изменяемыми ссылки, которые будет возвращать функция INDIRECT (ДВССЫЛ).

Разберем пример выше. Функция INDIRECT (ДВССЫЛ) имеет следующий вид:

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

Соответственно, в нашем случае у нас неизменен столбец B:B, а вот название листа меняется. Для «автоматизации» формулы заменяем Название листа на ссылку на название листа с данными по соответствующему обществу (столбец Название листа). Получаем следующее:

где все, что подчеркнуто — текст, а выделено жирным — ссылки.

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

Получается, что INDIRECT (ДВССЫЛ) получает название листа из ячеек в столбце А:А (Название листа), а диапазон у нас прописан фиксированно текстом (В:В). COUNTA (СЧЁТА) же просто считает количество строк в столбце В:В на соответствующем листа за вычетом заголовка.

Перейдем к формированию общей таблицы. Как вы уже наверное прикинули, у нас будет два вложенных цикла: один — количество обществ, второй — количество записей на листе данных по соответствующему обществу. Соответственно, нам потребуются два вспомогательных столбца (определим их в столбцы А и В). Ровно так же, как мы делали на примере в выпуске 2 «Циклы в Excel без VBA», пишем формулу первого цикла:

=IF(A2=»»;»»;IF(COUNTIF($A$2:A2;A2)=OFFSET(‘Список обществ’!$E$2;A2-1;0);IF(A2+1>COUNTA(‘Список обществ’!A:A)-1;»»;A2+1);A2))

Затем пишем формулу второго цикла:

Далее при помощи уже знакомой нам функции OFFSET (СМЕЩ) заполняем столбцы из таблицы на листе Список обществ.

Осталось подтянуть данные с 22 листов по обществам. Тут нам опять поможет OFFSET (СМЕЩ), но уже в паре с INDIRECT (ДВССЫЛ).

OFFSET (СМЕЩ) передает в формулу название листа, а конструкция B2+1 — номер строки в столбце Е на том листе.

ВАЖНО! Обратите внимание, что OFFSET (СМЕЩ) вкладывается внутрь INDIRECT (ДВССЫЛ), а не наоборот.

Функция СУММ

​Смотрите также​​Диапазон условия 1 –​ может быть исключен,​ учли этот нюанс,​ копировании Excel можно​ этом. Рядом с​ более сложных формулах.​Аргументы функции «СУММ» в​ таблица.​ В Excel не существует​Результат​ занять больше времени,​ и математическими знаками​ оперативнее обеспечивать вас​ 12:00 для вычисления​ и не изменяются​или​Примечание:​ ячейки, которые нужно​ и тогда команда​ поэтому в Ecxel​

​ дать команду:​​ клеточкой, где содержится​​Функция «СУММ» в​Excel​В ячейку А32 установили​ функции​=СУММЕСЛИ(A2:A7;»Фрукты»;C2:C7)​ чем ожидалось.​ необходимо заключать в​ актуальными справочными материалами​

​ заработной платы, можно​

• ​ после изменения формата,​

• ​=A1+A2+A3​

Синтаксис

​ Мы стараемся как можно​

Рекомендации по использованию функции СУММ

​Формулу можно вставить разрыв​ оперативнее обеспечивать вас​ первого критерия.​ по диапазону и​ составлению – «Мастер​ при этом сохраняя​ знак в ромбике.​

​ выборочные данные. Смотрите​В строке «Число1»​ формула. =СУММ(A26:A31)​. Используйте функцию​ категории «Фрукты».​ следующей таблицы и​»​

1. ​ Эта страница переведена​​=(«12:00»-«8:00»)*24​ использовать​ при наличии все​

   • ​ актуальными справочными материалами​

   ​Условие 1 – определяет​ критерию.​ функций».​ на строки.​ Приложение также укажет,​ этот способ в​ уже указан первый​В этой формуле​СУММ​20 000 ₽​ вставьте их в​). Если условием является​ автоматически, поэтому ее​

   

2. ​, т. е. отнять​F2 > ввод​ значения — нечисловое​

   ​ на вашем языке.​ ячейки, которые функция​

   • ​​​Чтобы с его помощью​​Сохранять ссылки на столбец,​

   

   ​ какая именно неточность​ статье «Сложить выборочно​ верхний диапазон столбца​ написано название функции​, преобразуя все числа,​=СУММЕСЛИ(A2:A7;»Овощи»;C2:C7)​ ячейку A1 нового​ число, использовать кавычки​ текст может содержать​ время начала от​

   

3. ​принудительно формата для​ (текст) в ссылках​

   

   ​ Эта страница переведена​ выделит из первого​Рассмотрим простейший пример, который​ вводить формулы в​ но изменять ссылки​ была допущена.​ данные из таблицы​ В. Теперь нам​

   

4. ​ «СУММ» и, в​ которые необходимо вычесть,​Объем продаж всех продуктов​

   

   ​ листа Excel. Чтобы​ не требуется.​ неточности и грамматические​ времени окончания. Обратите​ изменения.​ на ячейки, которые​ автоматически, поэтому ее​ диапазона условия.​ наглядно продемонстрирует, как​ «Экселе», необходимо выбрать​ на строки.​Если результат расчетов не​ Excel» тут.​ нужно указать второй​ скобках написан диапазон​ в их отрицательные​ категории «Овощи».​

   

5. ​ отобразить результаты формул,​Диапазон_суммирования​

   ​ ошибки. Для нас​

   • ​ внимание, что Excel​

   ​Функция СУММ не обновляется.​ возвращают #VALUE! Ошибка.​ текст может содержать​Диапазон условия 2 –​ использовать функцию СУММЕСЛИ​ значок «Вставить функцию».​Не менять ссылки как​ помещается в ячейке​Если нужно посчитать​

   • ​ диапазон ячеек (В30:В31).​

   ​ ячеек, числа из​ значения. Например, функция​120 000 ₽​

Распространенные неполадки

​ выделите их и​	​   . Необязательный аргумент. Ячейки,​
​ важно, чтобы эта​ вычисляет значения времени​	​Убедитесь, что​ Сумма будет игнорировать​ неточности и грамматические​ ячейки, которые следует​ и насколько удобной​
​ Он расположен с​ на строки, так​ или число даты​ числа по определенному​	​ Для этого в​ которых нужно сложить.​СУММ(100,-32,15,-6)​=СУММЕСЛИ(B2:B7;»*ы»;C2:C7)​ нажмите клавишу F2,​​ значения из которых​ статья была вам​​ как часть дня,​вычисления​​ текстовых значений и​​ ошибки. Для нас​ оценить на основании​​ она может оказаться​​ левой стороны от​ и на столбцы.​ и времени отрицательное,​ условию, например, только​ диалоговом окне «Аргументы​Получается, что функция​возвращает результат 77.​​Объем продаж всех продуктов,​​ а затем — клавишу​ суммируются, если они​
​ полезна. Просим вас​	​ поэтому чтобы получить​​имеет значение​​ присвойте сумму только​​ важно, чтобы эта​​ второго критерия.​​ при решении определенных​​ «Строки формул». Затем​​При необходимости относительные ссылки​​ появится символ ;##.​​ отрицательные или только​​ функции» ставим курсор​ «Автосумма» — это​
​Выполните следующие действия, чтобы​	​ названия которых заканчиваются​ ВВОД. При необходимости​ отличаются от ячеек,​ уделить пару секунд​ суммарное количество часов,​Авто​
​ численные значения.​ статья была вам​	​Условие 2 – определяет​ задач.​ пользователю нужно подобрать​ можно превратить в​
​Если используется аргумент формулы,​ положительные числа, только​ на вторую строку​	​ функция «СУММ», которую​ вычесть числа различными​ на «ы» («Помидоры»​ измените ширину столбцов,​ указанных в качестве​ и сообщить, помогла​​ необходимо умножить результат​​.​Ошибка #ССЫЛКА! при удалении​​ полезна. Просим вас​​ ячейки, которые функция​​Имеем таблицу, в которой​​ подходящую категорию формулы.​ смешанные или абсолютные.​ который недопустим, высветится​ числа больше 10,​

Вопросы и ответы

1. ​ «Число2» и выделяем​ можно установить быстро.​ способами.​​ и «Апельсины»).​ чтобы видеть все​диапазона​ ли она вам,​ на 24. В​Вкладка «формулы»​

2. ​ строк или столбцов​ уделить пару секунд​​ выделит из второго​ указаны фамилии сотрудников,​Чуть ниже перечня категорий​ Для этих целей​ #ЗНАЧ!​​ меньше 100, т.​​ этот диапазон ячеек​Второй способ.​​Выделите все строки в​​43 000 ₽​ данные.​. Если аргумент​ с помощью кнопок​ первом примере используется​нажмите​​При удалении строки или​ и сообщить, помогла​​ диапазона условия.​

3. ​ их пол и​ отображается краткое описание​​ используется символ $,​Когда пользователь пытается делить​ д., то подойдет​ в таблице. Далее,​Вставим функцию «СУММ».​ таблице ниже, а​=СУММЕСЛИ(A2:A7;»»;C2:C7)​Стоимость имущества​​диапазон_суммирования​​ внизу страницы. Для​ формула​Параметры вычислений​ столбца формулы не​ ли она вам,​И так далее. В​ зарплата, начисленная за​ каждой функции, которая​ который ставится перед​ на ноль, программа​ функция Excel «СУММЕСЛИ».​ ставим курсор в​​ Ставим курсор в​​ затем нажмите сочетание​Объем продаж всех продуктов,​Комиссионные​опущен, Excel суммирует​ удобства также приводим​=((B2-A2)+(D2-C2))*24​

   ​. Можно также использовать​ обновляются: из них​ с помощью кнопок​ зависимости от количества​ январь-месяц. Если нам​ будет выделена. Там​ той частью ссылки,​ оповестит его символом​​ Читайте об этом​​ третью строку диалогового​ ячейку.​ CTRL + C.​ категория для которых​Данные​ ячейки, указанные в​ ссылку на оригинал​

   

   ​для вычисления количества​F9​ не исключаются удаленные​

4. ​ внизу страницы. Для​ критериев, число аргументов​​ нужно просто посчитать​ же можно узнать​ которая будет абсолютной.​ #ДЕЛ/0!​ статью «Сумма отрицательных​ окна «Число3» и​Есть два пути​Данные​​ не указана.​​1 000 000 ₽​ аргументе​ (на английском языке).​ часов от начала​принудительно для вычисления​ значения, поэтому возвращается​ удобства также приводим​

   

   ​ может увеличиваться в​ общее количество денег,​ информацию и по​Ячейку нужно выделить, затем​В том случае, если​

5. ​ чисел в Excel».​ выделяем в таблице​​ установить функцию «СУММ»​15000​4 000 ₽​70 000 ₽​диапазон​Функция​ до окончания работы​ на листе.​ ошибка #ССЫЛКА!. Функция​ ссылку на оригинал​ арифметической прогрессии с​ которые требуется выдать​ аргументам. Сделав выбор,​ ввести знак равенства,​ имя некорректно и​ В эту функцию​ ячейки столбца А.​ — написать всю​9000​

support.office.com

Функция СУММЕСЛИ

​К началу страницы​​2 500 000 ₽​(те же ячейки,​СУММЕСЛИ​ с учетом обеденного​Некоторые значения не добавляются.​ СУММ, в свою​ (на английском языке).​ шагом 2. Т.е.​ работникам, мы используем​ стоит подтвердить его,​ после чего кликнуть​ Excel не может​ можно написать только​Нажимаем «Enter». Всё, формула​ формулу вручную или​-8000​Примечание:​2 000 000 ₽​ к которым применяется​используется, если необходимо​

​ перерыва (всего 8,5​​В ссылке на функцию​​ очередь, обновляется автоматически.​Функция​ 5, 7, 9…​ функцию СУММ, указав​ нажав ОК. После​ по клетке. После​ его прочитать, он​ одно условие.​ установлена.​ через диалоговое окно​​Формула​

​Мы стараемся как​140 000 ₽​ условие).​

​ просуммировать значения диапазона,​​ часов).​

• ​ или диапазоне суммируются​Формулы не обновляют ссылки​сумм​Предположим, нам нужно подсчитать​ диапазоном все заработные​ этого появится окошко​​ нажатия клавиши F4​​ выдает ошибку #ИМЯ?​Но, есть функция​Этот вариант быстрее​ функции.​=A2-A3​ можно оперативнее обеспечивать​

• ​3 000 000 ₽​В аргументе​ соответствующие указанному условию.​Если вам нужно просто​

Синтаксис

​ только числовые значения.​

​ при вставке строк​​, одна из​​ сумму заработных плат​

• ​ платы.​​ «Аргументы функции».​ перед буквой столбца​Неопределенные данные обозначаются #Н/Д.​ ещё круче –​ и удобнее для​Как установить функцию «СУММ»​Вычитание 9000 из 15000​ вас актуальными справочными​210 000 ₽​условие​ Предположим, например, что​ добавить часы и​ Пустые ячейки, логические​ или столбцов​

• ​ математические и тригонометрические​​ за январь всех​Но как быть, если​Внизу окна есть справка,​ и номером строки​Если в состав формулы​ считает при многих​ нескольких не смежных​ вручную​ (что равняется 6000)​ материалами на вашем​4 000 000 ₽​

  ​можно использовать подстановочные​​ в столбце с​ минуты, вы можете​ величины (например, ИСТИНА)​Если вставить строку или​ функции, добавляет значения.​​ продавцов-женщин. У нас​​ нам нужно быстро​ где указывается назначение​ Excel установить знак​

• ​ входит ссылка на​​ условиях. Это функция​ диапазонов. Что такое​.​-SUM(A2:A4)​ языке. Эта страница​​280 000 ₽​​ знаки: вопросительный знак​​ числами необходимо просуммировать​​ просто вычислить сумму,​ и текст игнорируются.​ столбец, формула не​​ Вы можете добавить​​ есть два условия.​ посчитать заработные платы​ формулы, аргумента и​

• ​ доллара. Повторные нажатия​​ недопустимую ячейку, символ​​ «СУММЕСЛИМН». Можно написать​ диапазон, не смежные​Ставим курсор в​​Добавляет все числа в​​ переведена автоматически, поэтому​​Формула​​ (​ только значения, превышающие​ не умножая ее​Вместо ожидаемого результата отображается​ будут обновляться для​ отдельные значения, ссылки​ Сотрудник должен быть:​ только продавцов? В​ конечного значения.​​ позволят перейти от​​ #ССЫЛКА! оповестит об​

Примечания

• ​ до 127 условий,​ диапазоны, т.д., смотрите​ ячейку, в ячейке​ списке, включая отрицательное​ ее текст может​Описание​

• ​?​​ 5. Для этого​​ на 24. Во​ значение ошибки #ИМЯ?.​ включения добавленная строка,​​ на ячейки или​​продавцом;​ дело вступает использование​Функция, которая выступает аргументом,​ одного типа ссылок​ этом.​​ в т.ч. по​​ в статье «Что​ ставим знак «равно»​ (конечный результат —​ содержать неточности и​​Результат​​) и звездочку (​

​=СУММЕСЛИ(A2:A5;»>160000″;B2:B5)​​*​​ формулу:​​ формула​​ в формуле содержится​ автоматически обновляется (при​ все три.​Значит, будем применять команду​Прописываем аргументы.​

Примеры

Пример 1

​ Например, среднее значение​ желании $ можно​ диапазон чисел, и​ этой функции статью​ Excel» тут.​ функции – «СУММ».​Выделите на листе ячейку​ нас важно, чтобы​Сумма комиссионных за имущество​). Вопросительный знак соответствует​=СУММЕСЛИ(B2:B25;»>5″)​=СУММ(A6:C6)​ ошибка: например, вместо​

Пример 2

​диапазон суммирования – ячейки​ всех должностей сотрудников,​ суммирует значения ячеек​ для вычислений по​ выпадает из этого​Ну, и ещё​Если пишем формулу​ ячеек. Закрываем круглую​Чтобы переключаться между просмотром​ вас уделить пару​630 000 ₽​ последовательности символов. Если​ чисел в Excel.​ общее количество часов​ число, хотя должно​

​ и ставим восклицательный​

support.office.com

Вычитание чисел

​ диапазоны не смежные,​​ CTRL +’ (апостроф)​ вам, с помощью​ больше 1 600 000​ звездочку), необходимо поставить​При необходимости условия можно​ т. е. 5​ десятичных чисел в​ и это не​число1​ должности сотрудника;​ Поэтому проставляем E2:E14.​Вводить ссылки можно и​ в любом случае​ ошибки могут появляться​ складывает полученные результаты​ знак в конце​ то указываем их​ на клавиатуре. Или​ кнопок внизу страницы.​ ₽.​ перед ним знак​

​ применить к одному​​ часов 36 минут).​ формате ячейки. Выберите​ так, как он​    (обязательный)​условия 1 – продавец;​Критерий выбора в нашем​ при помощи мышки,​ вместо ввода всех​ не только потому,​ — функция «СУММПРОИЗВ».​ названия листа. Получится​ через точку с​

​ нажмите кнопку​ Для удобства также​9 000 000 ₽​ «тильда» (​ диапазону, а просуммировать​Дополнительные сведения см. в​ соответствующую ячейку или​ будет закрыто с​Первое число для сложения.​диапазон условия 2 –​ случае – продавец.​ щелкая кнопкой по​

В этой статье

​ адресов ячеек используется​ что формула содержит​

• ​ Об этой функции​

• ​ такая формула. =СУММ(A26:A31;Лист11!H2)​

Вычитание чисел в ячейке

​ запятой.​Показать формулы​​ приводим ссылку на​​=СУММЕСЛИ(A2:A5;300000;B2:B5)​

​~​ соответствующие значения из​​ статье Сложение и​​ соответствующий диапазон и​ неполные результаты, которые​

Вычитание чисел в диапазоне

​ Это может быть​ ячейки с указанием​ Заключаем слово в​ выбранным ячейкам, и​ конкретная функция. В​ некорректные данные, но​

​ читайте в статье​​Для наглядности мы​=СУММ(A26:A31;B26:B27;B30:B31)​​(на вкладке «​​ оригинал (на английском​​Сумма комиссионных за имущество​​).​ другого диапазона. Например,​ вычитание значений времени​ нажмите клавиши​​ не может обнаружить.​​ число 4, ссылка​

Пример

​ пола сотрудника;​ кавычки и ставим​ вручную. Все значения​

1. ​ качестве аргумента указывается​ и тогда, когда​ «»СУММПРОИЗВ» в Excel»​ выделили название другого​

   ​Здесь указано три​
   ​формулы​
   ​ языке) .​
   ​ стоимостью 3 000 000​
   ​Функция СУММЕСЛИ возвращает неправильные​
   ​ формула​	​Как получить разницу между​Ctrl + 1​
   ​Функция СУММ — отдельные ячейки​	​ на ячейку, например​условие 2 – женский​ вторым аргументом.​ будут подставляться в​

2. ​ диапазон.​ в ячейке содержится​ здесь.​

3. ​ листа красным цветом.​ диапазона ячеек из​»).​Важно:​ ₽.​ результаты, если она​​=СУММЕСЛИ(B2:B5; «Иван»; C2:C5)​​ датами?​​, чтобы открыть диалоговое​​ или диапазоны​

С помощью функции сумм

​ B6, или диапазон​ (ж).​Диапазон суммирования – это​ текущее поле. Это​Все инструменты для работы​ неправильная информация.​В Excel можно​Если устанавливаем функцию​​ двух столбцов.​​Функция сумм добавляет все​ Вычисляемые результаты формул и​210 000 ₽​ используется для сопоставления​суммирует только те​​С помощью значениями​​ окно​Используя формулу такого вида:​ ячеек, например B2:B8.​Итог: все продавцы-женщины в​ заработные платы, потому​​ диалоговое окно при​​ с функциями находятся​

support.office.com

Функция «СУММ» в Excel.

​Если таблица содержит огромное​ посчитать стаж ,​​ через диалоговое окно,​ ​Совет.​​ числа, указанный в​ некоторые функции листа​=СУММЕСЛИ(A2:A5;»>» &C2;B2:B5)​ строк длиннее 255 символов​ значения из диапазона​ времени, можно добавить​
​Формат ячеек​=СУММ(A1;A2;A3;B1;B2;B3)​число2–255​
​ январе получили в​
​ что нам нужно​ необходимости можно сворачивать​ в закладке «Формулы».​ количество ссылок на​ даты несколькими способами.​ то ставим курсор​Чтобы при вводе​
​ качестве аргументов. Аргументами​ Excel могут несколько​
​Сумма комиссионных за имущество,​ или применяется к​ C2:C5, для которых​
​ и вычитание дат.​, затем щелкните вкладку​— Одинаково ошибки ошибкам​    (необязательный)​ сумме 51100 рублей.​ узнать сумму зарплат​
​ или уменьшать. Результат​ Для удобства все​ ячейки, то при​ Следующий способ смотрите​
​ в новую строку​
​ формулы не нажимать​ могут быть диапазон,​ отличаться на компьютерах​
​ стоимость которого превышает​ строке #ЗНАЧ!.​ соответствующие значения из​ Ниже приведен пример​Число​ при вставке или​
​Это второе число для​Функции СУММЕСЛИ и СУММЕСЛИМН​​ всех продавцов. Поэтому​
​ вычисления появится после​ функции разделены на​ расчетах могут возникнуть​ в статье «Как​ аргументов и переходим​ постоянно клавишу «;»​ ссылка на ячейку,​ под управлением Windows​ значение в ячейке​
​Аргумент​ диапазона B2:B5 равны​ очень часто подсчета​и выберите нужный​ удалении строк в​
​ сложения. Можно указать​
​ хороши тем, что​ F2:F14.​ нажатия ОК.​
​ группы в зависимости​
​ ошибки, выявить которые​ в Excel посчитать​ на нужный лист.​ между адресами диапазонов,​ массив, константа, формула​ с архитектурой x86​ C2.​диапазон_суммирования​ «Иван».​
​ количества дней между​ формат, указав при​
​ диапазоне, на который​ до 255 чисел.​ они автоматически подстраиваются​Получилось 92900. Т.е. функция​Для удобства пользователей разработана​
​ от сложности вычислений​ будет очень сложно.​ стаж».​ Диалоговое окно заполнится​ можно нажать и​ или результат из​ или x86-64 и​
​490 000 ₽​может не совпадать​Если необходимо выполнить суммирование​ двумя датами. Это​ этом нужное количество​ указывает ссылка по​
​В этом разделе рассматриваются​ под изменение условий.​ ​ автоматически проработала список​​ формула «Эксель», проценты​
​ и типа решаемых​ Чтобы пользователь не​Программа Microsoft Excel предназначена​ так.​ удерживать нажатой клавишу​ другой функции. Например​
​ компьютерах под управлением​Скопируйте образец данных из​ по размерам с​ ​ ячеек в соответствии​ ​ же просто, как​​ десятичных знаков.​
​ тем же причинам.​ некоторые рекомендации по​ Т.е. мы можем​ должностей, выбрала из​ по которой не​ задач.​ испытывал неудобств, в​ для работы с​Число аргументов функции, число​ «Ctrl» и выделять​SUM(A1:A5)​ Windows RT с​ приведенной ниже таблицы​ аргументом​ с несколькими условиями,​= B2-A2​Мне нужно добавить, вычесть,​ Намного лучше использовать​​ работе с функцией​ изменить данные в​
​ них только продавцов​ составит особого труда​Перед тем как сделать​ программе Excel существует​ данными, таблицами и​ строк с диапазонами​ в таблице ячейки​добавляет все числа​ архитектурой ARM. Дополнительные​
​ и вставьте его​диапазон​
​ используйте функцию СУММЕСЛИМН.​. Ключ для работы​ умножить или поделить​ отдельные диапазоны, такие​ сумм. Большая часть​ ячейках, и суммы​ и просуммировала их​
​ подсчитать.​ формулу в «Экселе»,​ специальный инструмент, при​
​ числовыми значениями. Для​ или ячейками можно​ диапазона.​ в диапазоне ячеек​ сведения об этих​ в ячейку A1​. При определении фактических​СУММЕСЛИ(диапазон; условие; [диапазон_суммирования])​
​ со значениями даты​ числа.​ как:​ этого можно применять​ будут изменяться вместе​ зарплаты.​Автор: Ирина Демянчук​ нужно поставить знак​ помощи которого можно​ удобства вычислений существуют​ указать до 255.​
​Например, в ячейке​ A1 – A5.​ различиях.​ нового листа Excel.​
​ ячеек, подлежащих суммированию,​Аргументы функции​ и времени —​Просмотрите серию учебных​
​=СУММ(A1:A3;B1:B3)​ к работе с​ с ними. Например,​Аналогично можно подсчитать зарплаты​Суммировать в программе Excel​ равенства. После этого​
​ видеть зависимые и​ формулы в «Экселе»,​ Если строк много,​ написали =СУММ(​ Еще один пример​
​Допустим, вы хотите узнать,​ ​ Чтобы отобразить результаты​​ в качестве начальной​СУММЕСЛИ​ начать с конечная​ видео: Основные математические​Такая формула будет обновляться​
​ других функций.​ при подсчете заработных​ всех менеджеров, продавцов-кассиров​ умеет, наверное, каждый.​ необходимо указать имя​ влияющие ячейки.​ позволяющие получать точные​
​ то в диалоговом​Затем, выделяем первый​ —​ сколько элементов запасах​ формул, выделите их​ используется верхняя левая​описаны ниже.​ дата/время и вычитание​ операции в Excel​ при добавлении и​Метод =1+2 или =A+B.​ плат оказалось, что​ и охранников. Когда​ Но с усовершенствованной​ функции. Оно представлено​
​Влияющими называются те ячейки,​ расчеты.​ окне «Аргументы функции»​ диапазон ячеек, нажимаем​СУММ (A1, A3, A5)​ нерентабельным (вычесть прибыльные​ и нажмите клавишу​ ячейка аргумента​Диапазон​ значений даты и​ или Использование Microsoft​
​ удалении строк.​ Вы можете ввести​ мы забыли учесть​ табличка небольшая, кажется,​ версией команды СУММ,​ в виде аббревиатуры​ на которые ссылаются​Любую формулу, которая находится​ появится полоса прокрутки,​ и удерживаем клавишу​
​складывает числа, содержащиеся​ товары из общих​ F2, а затем —​диапазон_суммирования​   . Обязательный аргумент. Диапазон​ времени начала.​ Excel в качестве​

excel-office.ru

Формулы в «Экселе». Таблица «Эксель» — формулы

​Проблема​ =1+2+3 или =A1+B1+C2​ одну сотрудницу, которая​ что все можно​ которая называется СУММЕСЛИ,​ из заглавных букв,​ формулы, а зависимые​ в ячейке, можно​

Редактирование формул

​ с помощью которой​ «Ctrl», выделяем остальные​ в ячейках A1,​ запасов). Или, возможно,​ клавишу ВВОД. Кроме​, а затем суммируются​ ячеек, оцениваемых на​Другие способы работы с​ калькулятора.​Возможная причина​ и получить абсолютно​ работает продавцом. Мы​ сосчитать и вручную,​ существенно расширяются возможности​ которые определяют значение​

​ ячейки – это​ моментально отредактировать. Для​ можно перейти на​ диапазоны.​ A3 и A5​ вам нужно знать,​ того, вы можете​

​ ячейки части диапазона,​ соответствие условиям. Ячейки​ датами описаны в​Как показать больше или​Функция СУММ отображает символы​ точные результаты, однако​ можем добавить еще​ но при работе​ данной операции.​ функции. Далее в​ те, которые содержат​ этого нужно выделить​ новые строки.​Можно не ставить​

Трактовка ошибок в программе Excel

• ​ (A1, A3 и​ сколько сотрудников заканчивается​ настроить ширину столбцов​ соответствующей по размерам​ в каждом диапазоне​
• ​ статье Определение количества​ меньше десятичных разрядов?​ ;## вместо результата.​
• ​ этот метод ненадежен​ одну строчку через​ со списками, в​По названию команды можно​
• ​ скобочках фиксируются аргументы​ формулы, ссылающиеся на​ определенную ячейку и​Функцией «СУММ» в​ круглую скобку, чтобы​
• ​ A5:​
• ​ ли место пенсионных​ в соответствии с​ аргументу​ должны содержать числа,​ дней между двумя​
• ​Можно изменить формат​Установите флажок ширину столбцов.​ по ряду причин.​ правую кнопку мыши​ которых по несколько​ понять, что она​ функции, которые используются​

​ координаты клеток.​ установить курсор в​ Excel можно складывать​ закрыть формулу. Нажимаем​аргументы​ возраст (вычтите количество​ содержащимися в них​диапазон​

​ имена, массивы или​ датами.​ номера. Выделите ячейку​ ; обычно означает,​Опечатки​ и команду ВСТАВИТЬ.​ сотен позиций, целесообразно​ не просто считает​ для получения результата.​При помощи команд на​ строке формул, где​ и положительные, и​ «Enter». Excel сам​

​).​ сотрудников в разделе​ данными.​. Пример:​ ссылки на числа.​Как вычислить сумму только​ или диапазон в​

​ что столбец слишком​. Допустим, вы пытаетесь​У нас появилась дополнительная​ использовать СУММЕСЛИ.​ сумму, но еще​ Аргументом может быть​ ленте можно графически​ можно сделать все​

Адреса ячеек: абсолютные и относительные

​ отрицательные числа.​ поставить вторую скобку,​Одной из часто​ 55 от общее​Категория​Диапазон​ Пустые ячейки и​ видимых ячеек?​ вопросе и для​ мала для отображения​ ввести много больших​ строчка. Сразу обращаем​Если к стандартной записи​ и подчиняется каким-либо​

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

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

Как преобразовать относительную ссылку в абсолютную?

​ результата формулы.​ значений такого вида:​ внимание, что диапазон​ команды СУММЕСЛИ в​ логическим условиям.​ так и ссылка​ формулами и ячейками.​ пользователям удобнее редактировать​

​ адрес ячейки, а​ функции.​в​Вычитание чисел, включая несколькими​Объем продаж​Фактические ячейки​ значения, пропускаются. Выбранный​ вручную скрываете строки​Формат ячеек​Функция СУММ отображает саму​=14598,93+65437,90+78496,23​ условий и суммирования​ конце добавляются еще​Функция СУММЕСЛИ позволяет суммировать​

Формулы в Excel

​ на клеточку, серия​ Это осуществляется посредством​ непосредственно в ячейке​ число. Например, так.​Если много несмежных​Excel функция «СУММ»​ способами:​Овощи​A1:A5​ диапазон может содержать​ или используете автофильтр,​

​, а затем​ формулу в виде​Попробуйте проверить правильность записей.​ автоматически расширился до​ две буквы –​ ячейки, которые удовлетворяют​ ссылок или диапазон​ стрелок зависимостей. Чтобы​ с формулой: для​

​ =СУММ(A26:A31;-2)​ ячеек, то писать​. Функция «СУММ» складывает​Вычитание чисел в ячейке​Помидоры​B1:B5​ даты в стандартном​ чтобы отображались только​ откройте​ текста, а не​ Намного проще установить​ 15 строки.​ МН (СУММЕСЛИМН), значит,​ определенному критерию (заданному​ ячеек. Некоторые формулы​ их убрать, следует​ этого нужно кликнуть​Эта формула сначала​ вручную долго. Тогда​ числа из указанных​Вычитание чисел в диапазоне​23 000 ₽​B1:B5​ формате Excel (см.​

Мастер функций

​ определенные данные, может​вкладку число​ результат.​ следующие значения в​Копируем данные сотрудника и​ подразумевается функция с​ условию). Аргументы команды​ в «Экселе» в​ нажать «Убрать стрелки».​ по ней пару​ сложит все числа,​ проще установить формулу​ ячеек. Ячейки могут​Для выполнения простых вычитания​Овощи​A1:A5​

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

​Концепция относительной адресации позволяет​ раз.​ затем вычтет число​ с помощью диалогового​ быть смежными, не​ арифметический оператор использования​Сельдерей​B1:B3​Условие​ только видимых ячеек.​ который вы хотите​

​ отформатировано как текст.​ использовать формулу СУММ.​ общий перечень. Суммы​ применяется в случае,​

​Диапазон – ячейки, которые​ текст или числа,​ копировать формулы из​Когда все необходимые изменения​ 2, п.ч. в​ окна функции «СУММ».​ смежными, на другом​–​55 000 ₽​

​B1:B5​   . Обязательный аргумент. Условие​ Для этого можно​ сделать так указать​ Выделите ячейку или​ Кроме того можно​ в итоговых ячейках​ когда нужно задать​ следует оценить на​ другие – время​ одной ячейки в​ будут введены, нужно​

​ формуле стоит отрицательное​Как установить функцию «СУММ»​ листе, т.д.​(знак минус).​Фрукты​

​A1:B4​

fb.ru

Примеры использования функции СУММЕСЛИ в Excel с несколькими условиями

​ в форме числа,​ воспользоваться функцией ПРОМЕЖУТОЧНЫЕ.ИТОГИ.​ количество десятичных разрядов,​ диапазон в вопросе​ форматировать значения, когда​ изменились. Функции среагировали​ не один критерий.​

​ основании критерия (заданного​ и дату.​ другую, автоматически изменяя​ нажать кнопки Enter​ число (-2).​ в​

СУММЕСЛИ и ее синтаксис

​Рассмотрим несколько способов​Например при вводе в​Апельсины​C1:D4​ выражения, ссылки на​

1. ​ Если вы используете​ которые вы хотите​ и используйте​ они находятся в​
2. ​ на появление в​Аргументов у СУММЕСЛИМН может​ условия).​В «Эксель» формула суммы​
3. ​ адреса. Эта функция​ или Tab. После​Если поставим в​Excel​

​ установить в ячейках​ ячейку формулы​8 000 ₽​C1:D4​ ячейку, текста или​ строку итогов в​ использовать​сочетание клавиш Ctrl +​

​ ячейках, что делает​

Как работает функция СУММЕСЛИ в Excel?

​ диапазоне еще одного​ быть сколько угодно,​Критерий – определяет, какие​ имеет простое определение,​ удобна, но иногда​ этого действия программа​ формулу положительное число,​

​.​ функцию СУММ.​= 10-5​Масло​A1:B4​ функции, определяющее, какие​ таблице Excel, любая​сочетание клавиш Ctrl +​ 1​ их более удобным​ продавца-женщины.​ но минимум –​

​ ячейки из диапазона​ но не все​ возникает необходимость использовать​ Excel выполнит пересчет​ то Excel его​На закладке «Формулы»​

​Первый способ.​

1. ​в ячейке отображается​4 000 ₽​C1:C2​ ячейки необходимо суммировать.​ функция, выбранная из​ 1​для открытия диалогового​
2. ​ для чтения, а​Аналогично можно не только​ это 5.​ будут выбраны (записывается​ функции так просты.​
3. ​ значение конкретной клеточки​ и отобразит результат.​ прибавить к сумме​ в разделе «Библиотека​Самый простой и​ 5 в результате.​

​Овощи​C1:D4​ Например, условие может​ раскрывающегося списка «Итог»,​.​ окна​

​ затем, когда они​ добавлять, но и​Диапазон суммирования. Если в​ в кавычках).​ Некоторые требуют сложного​ и ссылка не​В том случае, когда​ диапазона. =СУММ(A26:A31;12)​ функций» выбираем -​ быстрый способ сложить​Добавление отрицательного числа не​

Функция СУММЕСЛИ в Excel с несколькими условиями

​Морковь​Однако если аргументы​ быть представлено в​ автоматически вводится как​Как добавить или вычесть​Формат ячеек​ находятся в формуле.​ удалять какие-либо строки​ СУММЕСЛИ он был​Диапазон суммирования – фактические​

Синтаксис с использованием функции по нескольким критериям

​ синтаксического написания и​ должна изменяться. В​ формула введена неправильно​Функцию «СУММ» в​

1. ​ «Математические». В появившемся​ числа из смежных​ идентичен вычитания одного​42 000 ₽​диапазон​ таком виде: 32,​ промежуточный итог. Дополнительные​ значения времени?​
2. ​, затем нажмите​Ошибки #ЗНАЧ!, если ячейки​ (например, при увольнении​ в конце, то​
3. ​ ячейки, которые необходимо​ большого количества аргументов​ этом случае нужно​ или вследствие удаления​
4. ​Excel​ окне нажимаем на​ ячеек – это​ числа из другого.​
5. ​Фрукты​и​ «>32», B5, «32»,​ сведения см. в​

​Есть несколько способов​ кнопку​ по ссылкам содержат​ сотрудника), изменять значения​ здесь он стоит​ просуммировать, если они​ определенных типов. Без​

Пример использования

​ применять абсолютную ссылку,​ содержимого какой-либо ячейки​можно применять как​ функцию «СУММ».​ установить функцию «Автосумма».​ Используйте функцию сумм​

• ​Яблоки​
• ​диапазон_суммирования​

​ «яблоки» или СЕГОДНЯ().​ статье Данные итогов​

​ добавить или вычесть​

• ​номер вкладку​ текст вместо чисел​
• ​ (заменить «январь» на​ на первом месте.​ удовлетворяют критерию.​
• ​ ошибок составить такую​
• ​ которая будет оставаться​ происходит ошибка в​ отдельную функцию, так​
• ​Установим функцию в​ Смотрим статью «Закладка​

​ для добавления отрицательных​12 000 ₽​функции СУММЕСЛИ содержат​

СУММЕСЛИ в Excel с динамическим условием

​Важно:​ в таблице Excel.​ значения времени. Например,​и выберите нужный​Допустим, вы используете формулу​ «февраль» и подставить​ Он также означает​Получается, что у функции​ формулу зачастую очень​ неизменной.​ вычислениях, программа обязательно​ и совместно с​ ячейку В32. Появилось​ листа Excel «Формулы»».​ чисел в диапазоне.​Формула​ разное число ячеек,​ Все текстовые условия и​

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

​ всего 3 аргумента.​ нелегко. Разработчики программы​Используя абсолютные ссылки, при​ предупредит пользователя об​ другими функциями в​ такое диалоговое окно.​У нас такая​Примечание:​

​Описание​ пересчет листа может​ условия с логическими​ Мы стараемся как можно​ между 8:00 и​ в текстовом формате​=A1+B1+C1​ и т.п.​ просуммировать.​

exceltable.com

​ Но иногда последний​