|
Формула суммирования через одну ячейку |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
Содержание
- 0.1 Постановка задачи
- 0.2 Способ 1. Функция СУММЕСЛИ (SUMIF) и ее аналоги для выборочного суммирования по условию
- 0.3 Способ 2. Формула массива для суммирования каждой 2-й, 3-й … N-й строки
- 0.4 Способ 3. Функция БДСУММ и таблица с условием
- 0.5 Способ 4. Суммирование каждой 2-й, 3-й… N-й строки
- 0.6 Ссылки по теме
- 1 Суммирование значений в строке
- 1.1 Способ 1: арифметическая формула
- 1.2 Способ 2: автосумма
- 1.3 Способ 3: функция СУММ
- 1.4 Способ 4: массовое суммирование значений в строках
- 1.5 Помогла ли вам эта статья?
- 2 Простое сложение
- 3 Сложение каждой ячейки
- 4 Суммирование столбца
- 5 Использование формулы с помощью параметра «СУММ»
- 6 Суммирование с разных листов
Постановка задачи
Предположим, что у нас имеется вот такая таблица с данными по выручке и выполнению плана для нескольких городов:
Необходимо просуммировать выручку по всем городам или, что примерно то же самое, подсчитать средний процент выполнения плана по всем городам.
Если решать проблему «в лоб», то придется вводить длинную формулу с перебором всех ячеек, т.к. сразу весь диапазон одним движением выделить не получится:
Если количество городов в таблице больше пары десятков, то такой способ начинает нагонять тоску, да и ошибиться при вводе формулы можно запросто. Как же поступить? Есть способы сделать это изящнее.
Способ 1. Функция СУММЕСЛИ (SUMIF) и ее аналоги для выборочного суммирования по условию
Если в таблице есть столбец с признаком, по которому можно произвести выборочное суммирование (а у нас это столбец В со словами «Выручка» и «План»), то можно использовать функцию СУММЕСЛИ (SUMIF):
Первый аргумент этой функции — диапазон проверяемых ячеек, второй — критерий отбора (слово «Выручка«), третий — диапазон ячеек с числами для суммирования.
Начиная с версии Excel 2007 в базовом наборе появилась еще и функция СРЗНАЧЕСЛИ (AVERAGEIF), которая подсчитывает не сумму, а среднее арифметическое по условию. Ее можно использовать, например, для вычисления среднего процента выполнения плана. Подробно про все функции выборочного суммирования можно почитать в этой статье с видеоуроком. Минус этого способа в том, что в таблице должен быть отдельный столбец с признаком, а это бывает не всегда.
Способ 2. Формула массива для суммирования каждой 2-й, 3-й … N-й строки
Если удобного отдельного столбца с признаком для выборочного суммирования нет или значения в нем непостоянные (где-то «Выручка«, а где-то «Revenue» и т.д.), то можно написать формулу, которая будет проверять номер строки для каждой ячейки и суммировать только те из них, где номер четный, т.е. кратен двум:
Давайте подробно разберем формулу в ячейке G2. «Читать» эту формулу лучше из середины наружу:
- Функция СТРОКА (ROW) выдает номер строки для каждой по очереди ячейки из диапазона B2:B15.
- Функция ОСТАТ (MOD) вычисляет остаток от деления каждого полученного номера строки на 2.
- Функция ЕСЛИ (IF) проверяет остаток, и если он равен нулю (т.е. номер строки четный, кратен 2), то выводит содержимое очередной ячейки или, в противном случае, не выводит ничего.
- И, наконец, функция СУММ (SUM) суммирует весь набор значений, которые выдает ЕСЛИ, т.е. суммирует каждое 2-е число в диапазоне.
- Данная формула должна быть введена как формула массива, т.е. после ее набора нужно нажать не Enter, а сочетание Ctrl+Alt+Enter. Фигурные скобки набирать с клавиатуры не нужно, они добавятся к формуле автоматически.
Для ввода, отладки и общего понимания работы подобных формул можно использовать следующий трюк: если выделить фрагмент сложной формулы и нажать клавишу F9, то Excel прямо в строке формул вычислит выделенное и отобразит результат. Например, если выделить функцию СТРОКА(B2:B15) и нажать F9, то мы увидим массив номеров строк для каждой ячейки нашего диапазона:
А если выделить фрагмент ОСТАТ(СТРОКА(B2:B15);2) и нажать на F9, то мы увидим массив результатов работы функции ОСТАТ, т.е. остатки от деления номеров строк на 2:
И, наконец, если выделить фрагмент ЕСЛИ(ОСТАТ(СТРОКА(B2:B15);2)=0;B2:B15) и нажать на F9, то мы увидим что же на самом деле суммирует функция СУММ в нашей формуле:
Значение ЛОЖЬ (FALSE) в данном случае интерпретируются Excel как ноль, так что мы и получаем, в итоге, сумму каждого второго числа в нашем столбце.
Легко сообразить, что вместо функции суммирования в эту конструкцию можно подставить любые другие, например функции МАКС (MAX) или МИН (MIN) для вычисления максимального или минимального значений и т.д.
Если над таблицей могут в будущем появляться новые строки (шапка, красивый заголовок и т.д.), то лучше слегка модернизировать формулу для большей универсальности:
Способ 3. Функция БДСУММ и таблица с условием
Формулы массива из предыдущего способа — штука красивая, но имеют слабое место — быстродействие. Если в вашей таблице несколько тысяч строк, то подобная формула способна заставить ваш Excel «задуматься» на несколько секунд даже на мощном ПК. В этом случае можно воспользоваться еще одной альтернативой — функцией БДСУММ (DSUM). Перед использованием эта функция требует небольшой доработки, а именно — создания в любом подходящем свободном месте на нашем листе миниатюрной таблицы с условием отбора. Заголовок этой таблицы может быть любым (слово «Условие» в E1), лишь бы он не совпадал с заголовками из таблицы с данными. После ввода условия в ячейку E2 появится слово ИСТИНА (TRUE) или ЛОЖЬ (FALSE) — не обращайте внимания, нам нужна будет сама формула из этой ячейки, выражающая условие, а не ее результат. После создания таблицы с условием можно использовать функцию БДСУММ (DSUM):
Способ 4. Суммирование каждой 2-й, 3-й… N-й строки
Если нужно просуммировать не отдельные ячейки, а целые строки, то можно это легко реализовать формулой массива, аналогичной способу 2:
Поскольку функция СУММПРОИЗВ (SUMPRODUCT) автоматически преобразует свои аргументы в массивы, то в этом случае нет необходимости даже нажимать Ctrl+Shift+Enter.
Ссылки по теме
- Подсчет выборочной суммы, среднего, количества и т.д. по одному или нескольким условиям.
- Что такое формулы массива, как их вводить, как они работают
Работая с таблицами, часто приходится подбивать общие итоги по конкретному наименованию. В качестве данного наименования может выступать название контрагента, фамилия работника, номер подразделения, дата и т.д. Нередко эти наименования являются заглавием строк и поэтому, чтобы подсчитать общий итог по каждому элементу, приходится суммировать содержимое ячеек конкретной строки. Иногда сложение данных в строках производят и в иных целях. Давайте разберем различные способы, как это можно сделать в программе Excel.
Читайте также: Как посчитать сумму в Экселе
Суммирование значений в строке
По большому счету, просуммировать в Экселе значения в строке можно тремя основными способами: использование арифметической формулы, применение функций и автосуммы. При этом, данные способы могут делиться ещё на ряд более конкретизированных вариантов.
Способ 1: арифметическая формула
Прежде всего, разберем, как при помощи арифметической формулы можно подсчитать сумму в строчке. Давайте посмотрим, как действует данный способ на конкретном примере.
Имеем таблицу, в которой указана выручка пяти магазинов по датам. Наименования магазинов являются названиями строк, а даты – названиями столбцов. Нам нужно рассчитать общую сумму выручки первого магазина за весь период. Для этого нам придется произвести сложение всех ячеек строчки, которая относится к данной торговой точке.
- Выделяем ячейку, в которую будет выводиться готовый результат подсчета итога. Ставим туда знак «=». Кликаем левой кнопкой мыши по первой ячейке в данной строке, которая содержит числовые значения. Как видим, её адрес тут же отобразится в элементе для вывода суммы. Ставим знак «+». Затем кликаем по следующей ячейке в строке. Таким способом чередуем знак «+» с адресами ячеек строчки, которая относится к первому магазину.
В итоге в конкретно нашем случае получается следующая формула:
=B3+C3+D3+E3+F3+G3+H3Естественно, при использовании других таблиц вид её будет отличаться.
- Для выведения общей суммы выручки по первой торговой точке жмем на кнопку Enter на клавиатуре. Результат выводится в ту ячейку, в которой была расположена формула.
Как видим, данный способ довольно простой и интуитивно понятный, но у него имеется один существенный недостаток. На его осуществление нужно потратить много времени, если сравнивать с теми вариантами, которые мы рассмотрим ниже. А если в таблице очень много столбцов, то временные затраты увеличатся ещё больше.
Способ 2: автосумма
Гораздо более быстрым способом сложить данные в строчке является применение автосуммы.
- Выделяем все ячейки с числовыми значениями первой строчки. Выделение проводим, зажав левую кнопку мыши. Перейдя во вкладку «Главная», жмем на значок «Автосумма», который расположен на ленте в блоке инструментов «Редактирование».
Другим вариантом вызова автосуммы является переход во вкладку «Формулы». Там в блоке инструментов «Библиотека функций» на ленте следует щелкнуть по кнопке «Автосумма».
Если же вы вообще не хотите перемещаться по вкладкам, то после выделения строчки можно просто набрать сочетание горячих клавиш Alt+=.
- Какое бы действие из вышеописанных манипуляций вы не выбрали, справа от выделенного диапазона отобразится число. Оно и будет составлять сумму значений строчки.
Как видим, данный способ позволяет произвести подсчет суммы в строке гораздо быстрее, чем предыдущий вариант. Но у него тоже имеется недостаток. Он состоит в том, что сумма будет выводиться только справа от выделенного горизонтального диапазона, а не в том месте, где захочет пользователь.
Способ 3: функция СУММ
Преодолеть недостатки двух вышеописанных способов может вариант с применением встроенной функции Excel под названием СУММ.
Оператор СУММ принадлежит к группе математических функций Эксель. Его задачей является суммирование чисел. Синтаксис этой функции имеет такой вид:
=СУММ(число1;число2;…)
Как видим, аргументами этого оператора являются числа или адреса ячеек, в которых они располагаются. Их количество может быть до 255.
Посмотрим, как можно просуммировать элементы в строке при помощи данного оператора на примере нашей таблицы.
- Выделяем любую пустую ячейку на листе, куда предполагаем выводить итог вычисления. При желании можно её выбрать даже на другом листе книги. Но подобное бывает все-таки редко, так как в большинстве случаев более удобно традиционно располагать ячейку для вывода итогов в той же строке, в которой находятся расчетные данные. После того, как выделение произведено, щелкаем по значку «Вставить функцию» слева от строки формул.
- Запускается инструмент, который носит наименование Мастер функций. Переходим в нем в категорию «Математические» и из открывшегося списка операторов выбираем название «СУММ». Затем кликаем по кнопке «OK» внизу окошка Мастера функций.
- Производится активация окна аргументов оператора СУММ. В этом окне может располагаться до 255 полей, но для решения нашей задачи понадобится всего одно поле – «Число1». В него нужно ввести координаты той строки, значения в которой следует сложить. Для этого ставим курсор в указанное поле, а затем, произведя зажим левой кнопкой мыши, выделяем курсором весь числовой диапазон нужной нам строчки. Как видим, адрес данного диапазона тут же будет отображен в поле окна аргументов. Затем щелкаем по кнопке «OK».
- После того, как мы произвели указанное действие, сумма значений строки тут же отобразится в той ячейке, которую мы выделили ещё на самом первом этапе решения задачи данным способом.
Как видим, данный способ довольно гибкий и относительно быстрый. Правда, не для всех пользователей он интуитивно понятен. Поэтому те из них, которые не знают о его существовании из различных источников, редко когда находят его в интерфейсе Excel самостоятельно.
Урок: Мастер функций в Экселе
Способ 4: массовое суммирование значений в строках
Но что делать, если нужно просуммировать не одну и не две строчки, а, скажем 10, 100 или даже 1000? Неужели для каждой строки требуется в отдельности применять вышеописанные действия? Как оказывается, совсем не обязательно. Для этого нужно просто скопировать формулу суммирования в другие ячейки, в которые вы планируете выводить сумму по остальным строчкам. Сделать это можно при помощи инструмента, который носит наименование маркера заполнения.
- Производим сложение значений в первой строке таблицы любым из тех способов, которые были описаны ранее. Ставим курсор в нижний правый угол ячейки, в которой отображается результат применяемой формулы или функции. При этом курсор должен изменить свой внешний вид и преобразоваться в маркер заполнения, который выглядит, как небольшой крестик. Затем зажимаем левую кнопку мыши и перетаскиваем курсор вниз, параллельно ячейкам с наименованиями строк.
- Как видим, все ячейки были заполнены данными. Это и есть сумма значений отдельно по строкам. Такой результат удалось получить потому, что по умолчанию все ссылки в Excel относительны, а не абсолютны, и при копировании изменяют свои координаты.
Урок: Как сделать автозаполнение в Экселе
Как видим, в Экселе существует три основных способа подсчитать сумму значений в строчках: арифметическая формула, автосумма и функция СУММ. Каждый из данных вариантов имеет свои достоинства и недостатки. Наиболее интуитивно простой способ – это применение формулы, самый быстрый вариант – автосумма, а самый универсальный – использование оператора СУММ. Кроме того, с помощью маркера заполнения можно проводить массовое суммирование значений по строкам, выполненное одним из трех способов, которые были перечислены выше.
Мы рады, что смогли помочь Вам в решении проблемы.
Задайте свой вопрос в комментариях, подробно расписав суть проблемы. Наши специалисты постараются ответить максимально быстро.
Помогла ли вам эта статья?
Да Нет
Доброго всем времени суток, мои дорогие друзья и гости моего блога. Как всегда и как обычно с вами я, Дмитрий Костин. И сегодня я бы хотел продолжить наше общение с Экселем, потому что это действительно нужная вещь и в хозяйстве всегда пригодится. Я, например, себе уже не представляю жизнь без этого табличного редактора.
Я на данный момент веду несколько таблиц для разных целей, это не считая тех, которые мы ведем совместно с ребятами из отдела с помощью сервиса Google Docs. У меня есть табличка, которая суммирует мои доходы по сравнению с расходами для блога, также я веду финансовый учет планирования расходов и семейного бюджета, и кучу других документов.
Да, конечно для таких целей есть специальные программы, но если честно, то они меня далеко не во всем устраивают в плане функционала, а в excel я могу настроить всё под себя, как я этого пожелаю. Просторы для этого просто громадные. Тем более сами документы можно защитить паролем, что делает работу в этом редакторе еще надежнее, проще и удобнее. В общем не буду вас томить, а как раз расскажу, как в экселе посчитать сумму ячеек
Простое сложение
Начем с легенько разминки. Если вам нужно посчитать сyмму каких-то чиcел, то самое простое — это сделать обычный пример. Для этого встаньте на любую ячейку и напишите знак равенства (=), после чего начинайте складывать нужные цифры (=15+6+94+3-10+2). Последним штрихом вам нужно будет жмахнуть клавишу Enter, тогда весь этот математический пример молниеносно преобразуется в решенный ответ.
Сложение каждой ячейки
Для начала мы с вами научимся складывать просто несколько чисел, которые могут находиться в разных местах
- Откройте документ excel, где у вас записаны какие-либо значения.
- Теперь выберите какое-нибудь свободное место и поставьте туда знак «Равно» (=). В этот момент у вас активировалась функция решения примеров, и неважно каких, сложения или вычитания.
- Теперь нажмите на ячeйку, где у вас записано первое число, которое вы хотите использовать для сложения. В том месте, где мы ставили знак «=», должна появится координата этой цифры, т.е. C6 или D2, и т.д. Отлично. Это то, что нам нужно.
- Теперь поставьте знак «+» и нажмите на следующую ячейкy с данными, которое вы хотите использовать для сложения. Видите, как меняется значение в том месте, где мы ставили «=»? Да. Теперь там стоят уже две координаты, объединенные плюсом.
- Таким же образом продолжайте искать оставшиеся цифры и когда они закончатся, вам останется нажать Enter и значения, которые вы выбрали суммируются, а сама сумма покажется в ячейке, в которой мы изначально ставили знак равенства.
Суммирование столбца
Теперь перейдем к самому простому и вкусному. Да, сейчас вы узнаете как суммировать столбец.
Выделите столбeц или его часть, после чего переходите во вкладку «Формулы» и выбирайте там пункт «Автосумма». После этой несложной манипуляции вы увидите сумму всех выделенных вами чисел. Она автоматически встанет в ближайшую свободную ячейку в вашем столбце.
На самом деле даже необязательно, чтобы это был столбец. Это может быть как и строка, так и отдельные ячейки (можно выделить с помощью зажатой клавиши CTRL). Только место появление результата будет другое.
Использование формулы с помощью параметра «СУММ»
Этот способ особенно хорош тогда, когда вам нужно занести результат в определенную ячейку. По крайней мере я пользуюсь этим именно так и доволен как слон. Давайте покажу.
- Поставьте курсор в то место, где вы хотите, чтобы отображался ваш результат и поставьте туда уже знакомый нам знак равенства.
- Теперь нам нужно выполнить функцию «СУММ». Ее вы можете выбрать в левом верхнем углу листа, нажав на него. Либо вы можете увидеть ее во вкладке «Формулы», а затем выбрать «Математические», и уже там, среди множества других функций, искать заветную СУММ. В общем жмахайте.
- Вам откроется окно с аргументами функции. Здесь вы должны будете выбрать диапазон чисел, которые будете складывать. Например я хочу сложить столбик от А1 до А7. Тогда поставьте курсор в графу «Число 1», а затем выделите нужные ячейки.
- Теперь нажмите Enter и вы увидите, что всё у вас отлично посчиталось.
Кстати этим же способом вы можете воспользоваться, чтобы посчитать сумму ячеек с разных листов. Давайте расскажу чуть подробнее.
Суммирование с разных листов
- Откройте (создайте) новую таблицу, где разные значения будут находиться на разных листах. Сделали: Молодцы. И тогда сразу же поставьте в любую ячейку на любом лиcте знакомый значок «=» и снова выберите функцию «СУММ», как я рассказывал выше.
- Теперь, когда у вас открылось окно «Аргументы функции», поставьте курсор в строку «Число 1», после чего выделите нужные значения на первом листe.
- Теперь ставьте курсор в строку «Число 2», переходите на другой лист, и выделяйте все числа там. После этого нажмите ОК.
Всё, теперь несмотря на то, что числа находятся разных местах, сумма посчитана. Эта функция делает данный табличный редактор еще удобнее.
Чем мне особенно нравится excel, так это тем, что можно менять параметры формулы на лету. Например, если нам надо одно из участвующих числе уменьшить на две единицы, то соответственно уменьшится на два и вся сумма. Такую штуку частенько использую в повседневной жизни.
Ну на этом у меня вроде бы всё. Если остались какие-либо непонятки, то спрашивайте. С удовольствием отвечу на интересующие вас вопросы. Так же рекомендую вам подписаться на обновления моего блога, чтобы всегда быть в курсе обо всем новом и интересном. Увидимся с вами в других статьях. Пока-пока!
С уважением, Дмитрий Костин.
Постановка задачи
Предположим, что у нас имеется вот такая таблица с данными по выручке и выполнению плана для нескольких городов:
Необходимо просуммировать выручку по всем городам или, что примерно то же самое, подсчитать средний процент выполнения плана по всем городам.
Если решать проблему «в лоб», то придется вводить длинную формулу с перебором всех ячеек, т.к. сразу весь диапазон одним движением выделить не получится:
Если количество городов в таблице больше пары десятков, то такой способ начинает нагонять тоску, да и ошибиться при вводе формулы можно запросто. Как же поступить? Есть способы сделать это изящнее.
Способ 1. Функция СУММЕСЛИ (SUMIF) и ее аналоги для выборочного суммирования по условию
Если в таблице есть столбец с признаком, по которому можно произвести выборочное суммирование (а у нас это столбец В со словами «Выручка» и «План»), то можно использовать функцию СУММЕСЛИ (SUMIF):
Первый аргумент этой функции — диапазон проверяемых ячеек, второй — критерий отбора (слово «Выручка«), третий — диапазон ячеек с числами для суммирования.
Начиная с версии Excel 2007 в базовом наборе появилась еще и функция СРЗНАЧЕСЛИ (AVERAGEIF), которая подсчитывает не сумму, а среднее арифметическое по условию. Ее можно использовать, например, для вычисления среднего процента выполнения плана. Подробно про все функции выборочного суммирования можно почитать в этой статье с видеоуроком. Минус этого способа в том, что в таблице должен быть отдельный столбец с признаком, а это бывает не всегда.
Способ 2. Формула массива для суммирования каждой 2-й, 3-й … N-й строки
Если удобного отдельного столбца с признаком для выборочного суммирования нет или значения в нем непостоянные (где-то «Выручка«, а где-то «Revenue» и т.д.), то можно написать формулу, которая будет проверять номер строки для каждой ячейки и суммировать только те из них, где номер четный, т.е. кратен двум:
Давайте подробно разберем формулу в ячейке G2. «Читать» эту формулу лучше из середины наружу:
- Функция СТРОКА (ROW) выдает номер строки для каждой по очереди ячейки из диапазона B2:B15.
- Функция ОСТАТ (MOD) вычисляет остаток от деления каждого полученного номера строки на 2.
- Функция ЕСЛИ (IF) проверяет остаток, и если он равен нулю (т.е. номер строки четный, кратен 2), то выводит содержимое очередной ячейки или, в противном случае, не выводит ничего.
- И, наконец, функция СУММ (SUM) суммирует весь набор значений, которые выдает ЕСЛИ, т.е. суммирует каждое 2-е число в диапазоне.
- Данная формула должна быть введена как формула массива, т.е. после ее набора нужно нажать не Enter, а сочетание Ctrl+Alt+Enter. Фигурные скобки набирать с клавиатуры не нужно, они добавятся к формуле автоматически.
Для ввода, отладки и общего понимания работы подобных формул можно использовать следующий трюк: если выделить фрагмент сложной формулы и нажать клавишу F9, то Excel прямо в строке формул вычислит выделенное и отобразит результат. Например, если выделить функцию СТРОКА(B2:B15) и нажать F9, то мы увидим массив номеров строк для каждой ячейки нашего диапазона:
А если выделить фрагмент ОСТАТ(СТРОКА(B2:B15);2) и нажать на F9, то мы увидим массив результатов работы функции ОСТАТ, т.е. остатки от деления номеров строк на 2:
И, наконец, если выделить фрагмент ЕСЛИ(ОСТАТ(СТРОКА(B2:B15);2)=0;B2:B15) и нажать на F9, то мы увидим что же на самом деле суммирует функция СУММ в нашей формуле:
Значение ЛОЖЬ (FALSE) в данном случае интерпретируются Excel как ноль, так что мы и получаем, в итоге, сумму каждого второго числа в нашем столбце.
Легко сообразить, что вместо функции суммирования в эту конструкцию можно подставить любые другие, например функции МАКС (MAX) или МИН (MIN) для вычисления максимального или минимального значений и т.д.
Если над таблицей могут в будущем появляться новые строки (шапка, красивый заголовок и т.д.), то лучше слегка модернизировать формулу для большей универсальности:
Способ 3. Функция БДСУММ и таблица с условием
Формулы массива из предыдущего способа — штука красивая, но имеют слабое место — быстродействие. Если в вашей таблице несколько тысяч строк, то подобная формула способна заставить ваш Excel «задуматься» на несколько секунд даже на мощном ПК. В этом случае можно воспользоваться еще одной альтернативой — функцией БДСУММ (DSUM). Перед использованием эта функция требует небольшой доработки, а именно — создания в любом подходящем свободном месте на нашем листе миниатюрной таблицы с условием отбора. Заголовок этой таблицы может быть любым (слово «Условие» в E1), лишь бы он не совпадал с заголовками из таблицы с данными. После ввода условия в ячейку E2 появится слово ИСТИНА (TRUE) или ЛОЖЬ (FALSE) — не обращайте внимания, нам нужна будет сама формула из этой ячейки, выражающая условие, а не ее результат. После создания таблицы с условием можно использовать функцию БДСУММ (DSUM):
Способ 4. Суммирование каждой 2-й, 3-й… N-й строки
Если нужно просуммировать не отдельные ячейки, а целые строки, то можно это легко реализовать формулой массива, аналогичной способу 2:
Поскольку функция СУММПРОИЗВ (SUMPRODUCT) автоматически преобразует свои аргументы в массивы, то в этом случае нет необходимости даже нажимать Ctrl+Shift+Enter.
Ссылки по теме
- Подсчет выборочной суммы, среднего, количества и т.д. по одному или нескольким условиям.
- Что такое формулы массива, как их вводить, как они работают
Суммирование ячеек – базовая функция в программе электронных таблиц Excel. При работе с большим объемом информации может возникнуть необходимость проделать математическое действие с определенными данными.
Однако отбирать информацию вручную или с помощью функции «ЕСЛИ» в отдельный столбец, а потом суммировать эти ячейки довольно кропотливо, а также забирает большое количество времени.
Но если нужно отобрать данные по нескольким условиям? В программе все эти действия можно соединить в одно и не тратить драгоценно время. В этой статье вы узнаете, как просуммировать ячейки по условиям.
Суммирование ячеек в Excel по условию
Если вы хотите узнать подробную инструкцию, как создать таблицу в Excel 2013 с точными размерами в см., вы можете прочитать статью об этом на нашем портале.
Функция «СУММ»
Данной функцией можно воспользоваться при обработке больших чисел, которые просто нужно сложить, не отделяя ячейки по каким-то критериям. Буквально, как «+» в калькуляторе. Для выполнения обычного сложения необходимо:
- Выделить нужную вам ячейку, нажав на нее один раз левой кнопкой мыши.
Щелкаем левой кнопкой мышки по пустой ячейке, где будем вводить функцию
- Выбрать «fx» и в списке найти действие «СУММ», это можно сделать в категории «Полный алфавитный перечень» или в «Математических».
Нажимаем по значку функции «fx»
В списке «Категория» выбираем «Полный алфавитный перечень»
Находим и щелкаем по функции «СУММ», нажимаем «ОК»
- Кликните на «ОК», появится окно «Аргументы функции». В нем можно сложить значения ячеек или вбить дополнительные цифры.
Ставим курсор мышки в поле «Число1», щелкаем по ячейке со значением
Ставим курсор мышки поочередно в поля «Число2» и «Число3», щелкаем по ячейкам со значением, нажимаем «ОК»
Важно! Знайте, что программа проигнорирует логическое или текстовое значения.
- Нажмите «ОК».
В выделенной ранее ячейке появится результат функции «СУММ»
При суммировании чисел с одним условием применяйте функцию «СУММЕСЛИ»
Для этого:
- Выберите ячейку, где вы хотите вывести результат. Кликните по ней единожды левой кнопкой мыши.
Щелкаем левой кнопкой мышки по пустой ячейке, где будем вводить функцию
- Нажмите на кнопку «fx» («Вставить функцию»), которая расположена перед строкой формул.
Нажимаем по значку функции «fx»
- Возникнет «Мастер функций». Выберите «СУММЕСЛИ», это можно сделать в категориях: «Полный алфавитный перечень» или в «Математические».
В списке «Категория» выбираем «Математичсекие», находим и щелкаем по функции «СУММЕСЛИ», затем «ОК»
- Проявится всплывающее меню «Аргументы функции».
Окно «Аргументы функции»
- Заполните диапазон и критерий. Предположим, в нашем случае нам нужно узнать, на какую сумму продал товара Васечкин. Выделим ячейки с продавцами, большим плюсом нажмем на первую и, не отпуская, доведем до последней ячейки в диапазоне.
Большим плюсом нажмем на первую ячейку и, не отпуская, доведем до последней ячейки в диапазоне
- В строку «Критерий» впишем «Васечкин» (можно писать как с кавычками, так и нет, программа их выставит автоматически).
В поле «Критерий» вписываем «Васечкин»
Примечание! Можно вводить также математическое выражение.
- Отмечаем диапазон суммирования из столбца «Сумма».
В поле «Диапазон суммирования», вводим ячейки из столбца «Сумма», выделив первую ячейку и, не отпуская левую кнопку мышки, протягиваем до последней ячейки
- Нажмите «ОК». Результат действия появится в выбранной ранее ячейке.
Проверяем данные, нажимаем «ОК»
В ячейке появится результат, заданной функции
На заметку! Эту функцию также можно ввести вручную, используя базовую запись: «=СУММЕСЛИ(x), где х – диапазон, критерий и диапазон суммирования, которые перечисляются через «;». Например, «=СУММЕСЛИ(А1:А2;«Условие»;В1:В2)».
Вводим вручную данные, в выбранную ячейку, нажимаем «Enter»
Однако, если нужно отобрать информацию по нескольким разным критериям?
Если вы хотите узнать, как написать формулу в Excel, а также обучиться азам работы в Excel, вы можете прочитать статью об этом на нашем портале.
Функция «СУММЕСЛИМН»
Данная функция работает, как и «СУММЕСЛИ», но дает больше возможностей в задании параметров математической операции. Для применения этой функции, вам нужно выполнить следующие шаги:
- Отметьте ячейку.
Щелкаем левой кнопкой мышки по пустой ячейке, где будем вводить функцию
- Нажмите на «fx» или «Вставить функцию».
Нажимаем по значку функции «fx»
- Появится окно, где из перечня выберите функцию «СУММЕСЛИМН».
Выбираем функцию «СУММЕСЛИМН»
- Кликните на «ОК» или два раза нажмите на «СУММЕСЛИМН».
Щелкаем по функции дважды или нажимаем «ОК»
- Проявится меню «Аргументы функции».
В поле «Диапазон суммирования», вводим ячейки из столбца «Сумма», протянув левую кнопку мышки от первой до последней ячейки
Важно! Обратите внимание – в отличие от «СУММЕСЛИ», в данном окне сначала задается диапазон суммирования, а потом уже условия. Также можно ввести до 127 условий.
- Заполните диапазоны условий и сами условия.
В поле «Диапазон условия1» вводим ячейки из столбца «Товар», выделив их левой кнопкой мышки
В поле «Условие1» вводим «яблоки» или другое значение, результат которого мы хотим получить
- Нажмите «ОК».
Щелкаем по кнопке «ОК»
Примечание! Более подробную инструкцию вы можете найти в этой статье чуть выше.
- Предположим, нам нужно узнать, на какую сумму Васечкин продал яблок. У нас есть всего 2 условия – продавец должен быть Васечкин, а товар – яблоки. В нашем случае, аргументы функции будут выглядеть следующим образом.
Вводим данные в соответствующие поля, задаем необходимые условия в кавычках, щелкаем по кнопке «ОК»
Результат, высчитанный функцией «СУММЕСЛИМН»
Эту функцию также можно ввести вручную, однако это будет довольно сложно, поскольку присутствует много условий. Чтобы программа работала корректно, и вы не потеряли время, пытаясь найти ошибку в длинной записи, рекомендуем все же воспользоваться действием через «fx».
С наглядной инструкцией вы также можете ознакомиться в видео.
Видео — Суммирование по условию в Excel, функция «СУММЕСЛИМН»
Источник: https://pc-consultant.ru/rabota-na-komp-yutere/summirovanie-jacheek-v-excel-po-usloviju/
Как в Excel сделать формулу
Создавая математические формулы в Excel мы указываем какие действия нужно сделать и где должен отображаться результат.
С помощью использования формул в Excel можно производить различные расчёты такие как сложение, вычитание, умножение, деление, а также сложные комбинированные расчёты.
Составление формул в Excel производится вручную и состоит из адресов конкретных ячеек или из ряда ячеек или столбца ячеек.
Имеют некоторые секреты эксель формулы
Записывать формулы в Excel можно прямо в ячейке или в строке формул, а также можно в Excel записать формулу прямо в ячейке с помощью функций. С помощью ввода формул в Excel также высчитывают даты, время и проценты.
Обозначаются в Excel столбцы буквами такими как A, B. C и так далее, а нумерация строк в excel произведена цифрами 1, 2, 3 и так далее.
При пересечении столбца и строки получаете адрес ячейки в Excel который обозначается буквой столбца и цифрой строки например самая первая ячейка будет обозначаться A1.
Чтобы работать в эксель нужно знать названия ячеек
Любые формулы в экселе для расчета начинается со знака равенства =. Количество символов в ячейке Excel формула может содержать не более 1024. Делая какое-нибудь вычисление в excel в итоговой ячейке отобразится результат и если навести на эту ячейку с результатом курсор и нажать левую кнопку мыши то вы увидите в строке формул полученную формулу по которой производился расчёт в этой ячейке.
Сложение и вычитание
Если вам нужно в Excel сложить ячейки то нужно навести курсор на пустую ячейку в которой должен отображаться результат и нажать левую кнопку мыши. Затем на клавиатуре нажимаете клавишу =. Потом наводите курсор на первую ячейку которую будем суммировать с другими, нажимаем левую кнопку мыши. На клавиатуре нажимаете клавишу +.
Наводите курсор на вторую ячейку которую будете суммировать с другими, нажимаете левую кнопку мыши. Если нужно будет ещё в Excel сложить числа из каких-нибудь ячеек то поочерёдно добавьте их в формулу нажимая на +, а затем на ячейку которую нужно прибавить.
Когда будут зафиксированы все ячейки в формуле Excel нужно нажать на клавиатуре клавишу Enter и сразу в ячейке отобразится результат.
Очень легко в экселе сделать формулу сложения
Точно таким же способом создаётся формула в Excel для вычитания чисел. Только чтобы в Excel сделать вычитание нужно на клавиатуре вместо + нажимать минус -. При этом методе не важно в каком столбце и в какой строке находятся суммируемые и вычитаемые ячейки.
Пример как посчитать сумму в экселе в столбце
Если вам нужно будет посчитать в экселе сумму столбца или строки то нужно навести курсор на первую ячейку, нажать левую кнопку на мышке и не отпуская кнопки довести курсор до последней ячейки. Затем отпускаете кнопку мышки и нажимаете кнопку автосуммирование которая находится на панели инструментов Excel.
Умножение
Формула умножения в Excel составляется следующим способом. Например вам нужно перемножить в Excel ячейки A1 и A2, а результат чтобы отображался в ячейке A3.
Легко в Excel сделать формулу умножения
Сначала вам нужно нужно навести курсор на ячейку A3 и нажать левую кнопку мыши. На клавиатуре нажимаете клавишу =. Наводите курсор на ячейку A1 и нажимаете левую кнопку мыши. Затем на клавиатуре нажимаете знак умножения в Excel * который находится в левой части клавиатуры или на цифре 8.
Чтобы отобразить знак умножения в экселе * находящийся на цифре 8 нужно сначала нажать на клавиатуре кнопку Shift и удерживая её нажать на * находящуюся на цифре 8. Потом наводите курсор на ячейку A2 и нажимаете левую кнопку мыши.
В завершении нажимаете на клавиатуре клавишу Enter и сразу отобразится итог.
Деление
Формула деления в Excel составляется следующим образом. Например вам нужно разделить в Excel ячейку A1 на ячейку A2 и чтобы результат отображался в ячейке A3.
Легко в Excel ввести формулу деления
Сначала вам нужно нужно навести курсор на ячейку A3 и нажать левую кнопку мыши. На клавиатуре нажимаете клавишу =. Наводите курсор на ячейку A1 и нажимаете левую кнопку мыши. Затем на клавиатуре нажимаете знак деления в экселе / который находится в левой части клавиатуры или на клавише с вопросительным знаком.
Чтобы поставить знак деления в Excel который находится на клавише с вопросительным знаком нужно сначала клавиатуру перевести на английскую раскладку и только потом нажать эту клавишу. Потом наводите курсор на ячейку A2 и нажимаете левую кнопку мыши.
В завершении нажимаете на клавиатуре клавишу Enter и получаете результат деления.
Сложные формулы
Писать сложные формулы в Excel нужно точно такими же как и в математике. Например у вас заполнены ячейки данными и нужно чтобы в ячейке A4 отобразился результат исходя из формулы =A1+A2/(A3-A2)*A1.
Можно как писать формулы в Excel или вставлять уже готовые
Первым делом копируете эту формулу в буфер обмена чтобы потом вставить формулу в эксель. Наводите курсор на ячейку A4 и нажимаете левую кнопку мыши. Наводите курсор на строку формул в Excel и нажимаете левую кнопку мыши.
Затем нужно в эту строку в Excel вставить формулу из буфера обмена, а для этого нажмите на клавиатуре клавишу Ctrl и удерживая её нажмите клавишу V. Формула пропишется в строке формул, а нажав на клавишу Enter сразу отобразится результат вычисления по этой формуле.
Чтобы удалить формулу из ячейки в excel достаточно навести курсор на ячейку, нажать левую кнопку мыши и удалить формулу в строке формул.
Источник: https://kaknakomputere.ru/sovety/kak-v-excel-sdelat-formulu
Как в Экселе посчитать сумму?
Рубрика Excel
Также статьи о формулах в Экселе:
Одна из самых распространенных операций, производимых в Экселе, это суммирование значений. В расчетах могут участвовать как отдельные ячейки, так и целые строки или столбцы, и далее мы рассмотрим, как в Экселе посчитать сумму чисел несколькими способами.
Формула суммы в Excel
Сделать сумму в Экселе значений из отдельно взятых ячеек можно только составив соответствующую формулу, в которой будут перечислены все суммируемые ячейки через знак плюс. Если в расчетах участвует довольно много ячеек, то составление формулы суммы в Экселе будет довольно трудоемким процессом.
Как в Экселе посчитать сумму столбца или строки?
Для подсчета суммы ячеек в столбце или строке в Экселе существует специальная функция «СУММ», в которой достаточно указать первую и последнюю ячейку диапазона данных в столбце или строке. Эта функция избавляет пользователей от необходимости перечисления всех ячеек в формуле и допускает участие в расчетах пустые ячейки.
Также в Excel на вкладке «Главная» имеется пункт меню в виде значка суммы, при нажатии на который Эксель автоматически выделяет диапазон ячеек в столбце над текущей выделенной ячейкой для суммирования. При этом выделяться будет диапазон до первой пустой ячейки.
Также можно выделить самостоятельно необходимый диапазон ячеек и нажать данный пункт меню для подсчета суммы, в итоге сумма ячеек появится в следующей ячейке после диапазона данных.
Сумма ячеек в Эксель
В Экселе есть возможность подсчета суммы выделенных ячеек даже без составления различных формул и применения пунктов меню. Для этого просто выделяем необходимый диапазон ячеек с числовыми данными, и смотрим общую сумму значений всех выделенных ячеек на нижней панели справа.
Источник: http://officeprogs.ru/excel/kak-v-e-ksele-poschitat-summu.html
Трюк №69. Как в Excel 2010 просуммировать каждую вторую, третью или n-ую строку или ячейку
Часто бывает, что нужно суммировать каждую вторую, третью, четвертую и т. д. ячейку в электронной таблице. Теперь, благодаря следующему трюку, это можно сделать.
В Excel не предусмотрено стандартной функции, которая может суммировать каждую n-ю ячейку или строку. Однако можно выполнить эту задачу несколькими различными способами.
Все эти подходы основаны на функциях СТРОКА (ROW) и ОСТАТ (MOD).
Функция СТРОКА (ROW) возвращает номер строки для заданной ссылки на ячейку: ROW(reference), в русской версии Excel СТРОКА(ссылка).
Функция ОСТАТ (MOD) возвращает остаток от деления числа на делитель: MOD(number;divisor), в русской версии Excel ОСТАТ(число;делитель).
Поместите функцию СТРОКА (ROW) в функцию ОСТАТ (MOD) (чтобы передать числовой аргумент), разделите на 2 (чтобы суммировать каждую вторую ячейку) и проверьте, не равен ли результат нулю. Если это так, ячейка суммируется.
Эти функции можно использовать разнообразнейшими способами — некоторые обеспечат лучший результат, чем другие.
Например, формула массива для суммирования каждой второй ячейки в диапазоне $А$1:$А$100 могла бы выглядеть так: =SUM(IF(MOD(ROW($A$1:$А$500);2)=0;$А$1:$А$500;0)), в русской версии Excel =СУММ(ЕСЛИ(ОСТАТ(СТРОКА($А$1:$А$500);2)=0;$А$1:$А$500;0)).
Хотя цель достигнута, такой способ отрицательно влияет на дизайн электронной таблицы. Это ненужное применение формулы массива. Чтобы еще ухудшить дела, в эту длинную формулу вложена пересчитываемая функция СТРОКА (ROW), превращающая большую формулу также в пересчитываемую. Это означает, что она будет постоянно пересчитываться, что бы вы ни сделали в рабочей книге. Это очень плохой способ!
Вот другая формула, представляющая собой чуть лучший выбор: =SUMPRODUCT((MOD(ROW($A$1:$A$500);2)=0)*($A$1:$A$500)), в русской версии Excel =СУММПРОИЗВ((ОСТАТ(СТРОКА($А$1:$А$500);2)=0)*($А$1:$А$500)).
Нужно, однако, помнить, что эта формула вернет ошибку #ЗНАЧ! (#VALUE!), если какие-либо ячейки диапазона содержат не числа, а текст. Эта формула, хотя и не является фактически формулой массива, также замедляет работу Excel, если использовать ее слишком много раз или если каждый раз она ссылается на большой диапазон.
К счастью, есть лучший способ, представляющий собой не только более эффективное, но и намного более гибкое решение. Он требует применения функции БДСУММ (DSUM). В этом примере мы использовали диапазон А1:А500 как диапазон, в котором необходимо суммировать каждую n-ю ячейку.
В ячейку Е1 введите слово Criteria. В ячейку Е2 введите следующую формулу: =MOD(ROW(A2)-$C$2-1;$C$2)=0, в русской версии Excel =ОСТАТ(СТРОКА(А2)-$С$2-1;$С$2)=0. Выделите ячейку С2 и выберите команду Данные → Проверка (Data → Validation).
В поле Тип данных (Allow) выберите пункт Список (List), а в поле Источник (Source) введите 1, 2, 3, 4, 5, 6, 7, 8, 9, 10.
Удостоверьтесь, что установлен флажок Список допустимых значений (In-Cell), и щелкните на кнопке ОК. В ячейке С1 введите текст SUM every….
В любой ячейке, кроме строки 1, введите следующую формулу: =DSUM($A:$A;1;$E$1:$E$2), в русской версии Excel =БДСУММ($А:$А;1;$Е$1:$Е$2).
В ячейке непосредственно над той, где вы ввели функцию БДСУММ (DSUM), введите текст =»Summing Every» & $С$2 & CHOOSE($C$2;»st»;»nd»;»rd»;»th»;»th»;»th»;»th»;»th»;»th»;»th») & «Cell». Теперь осталось только выбрать нужное число в ячейке С2, а остальное сделает функция БДСУММ (DSUM).
При помощи функции БДСУММ (DSUM) можно суммировать ячейки через указанный вами интервал. Функция БДСУММ (DSUM) намного эффективнее, чем формула массива или функция СУММПРОИЗВ (SUMPRODUCT). Хотя настройка занимает немного больше времени, это тот случай, когда тяжело в ученье, легко в бою.
Источник: http://Excel2010.ru/tryuk-69-kak-v-excel-2010-prosummirovat-kazhduyu-vtoruyu-tretyu-ili-n-uyu-stroku-ili-yachejku.html
Иногда даже такие расчеты могут вызвать определенные затруднения у начинающих пользователей пакета MS Office с программами Word и Excel. Например, далеко не все знают даже то, как в Экселе посчитать сумму выделенных ячеек. Не говоря уже о действиях, необходимых для сложения чисел в одинаковых диапазонах на разных страницах. На самом деле складывать числа в табличном процессоре очень легко, и применяются для этого всего 3 функции и один математический знак.
Простое сложение
Самый простой способ, как в Экселе посчитать сумму определенных ячеек — использование знака «плюс». Он подходит при необходимости сложить небольшое количество чисел или суммировать диапазоны, расположенные в произвольном порядке на одном или нескольких листах.
Так, например, использование плюса будет целесообразным при необходимости сложить всего 3 значения, пусть даже расположенных совсем рядом. Математический знак в этом случае позволит сократить время на поиски или набор функции с расстановкой скобок. Перед формулой обязательно ставится знак «равно».
Не обойтись без такого способа и в тех случаях, когда ячейки расположены в произвольном порядке друг относительно друга. Например, пользователь точно знает (определив это способом, который невозможно заменить формулой), что сложить нужно именно ячейки G8, H16, J7 и K18. Для получения суммы достаточно просто написать в ячейке сначала знак равенства, а затем номера ячеек, между которыми ставится плюс.
Если складывать приходится большое количество данных, применение такой методики отнимет слишком много времени. Уже даже сумма 4-5 значений требует потратить время на расстановку плюсов и постоянного перевода курсора в строку формул. Потому быстрее и проще применять для этого функцию СУММ, специально разработанную для таких подсчетов.
Применение функции СУММ
Синтаксис математической функции достаточно простой. После того как в строке появится название СУММ следует ввести нужное количество аргументов. Причем их необязательно должно быть много — функция считает сумму даже для одного числа. Хотя, естественно, использование СУММ имеет смысл только при большом количестве аргументов — хотя бы 5.
Функцию можно применять, складывая отдельные значения или целые диапазоны. Причем от пользователя не требуется ставить какие-то знаки — даже точка с запятой и двоеточие устанавливаются автоматически. В одной формуле можно указать до 255 аргументов. Каждый из них может быть положительным, отрицательным, дробным. Округление происходит в соответствии с настройками ячейки, в которой записывается формула.
Записывать слово «СУММ» можно вручную (кириллицей), не забывая ставить открывающую и закрывающую скобки. Хотя оно есть и в списке функций, открывающемся при нажатии на значок слева от строки формул. В большинстве случаев «СУММ» можно найти среди 10 недавно использовавшихся функций. Если нет — искать придется среди математических.
Автосумма
Перед тем как посчитать в Эксель сумму нескольких ячеек в одном диапазоне, например, в одном столбце или строке или нескольких соседних колонках, стоит познакомиться с функцией «Автосумма». Она находится в группе «Редактирование» на главной вкладке и позволяет автоматически определять, какие числа будут складываться.
Определение не всегда бывает правильным, но если верно выбрать ячейку, где пишется формула, вероятность будет достаточно высокой:
- если использовать автосумму, выделив ячейку под столбцом со значениями, в формулу попадут все значения в этой колонке;
- если установить курсор в конце строки с числами и использовавть функцию, автосуммирование произойдет для этого диапазона;
- выделяя сразу несколько ячеек, можно получить такое же количество и автосумм.
Если автоматическое определение диапазона не сработало, пользователь может скорректировать его вручную. В любом случае, обычно это позволяет сэкономить время.
Кроме того, в выпадающем списке, который появляется при нажатии стрелки у значка автосуммы, есть и несколько других полезных функций — например, определение минимума или среднего значения. И даже счетчик, который показывает точное количество складываемых значений.
Особенности определения суммы в разных ситуациях
Один из самых простых расчетов, для которых применяется функция СУММ – складывание чисел в столбце. Для определения суммы достаточно при указании аргумента выделить верхнюю ячейку, нажать правую кнопку мыши и отпустить, выбрав необходимый диапазон.
Если результат сложения должен стоять в первой же строке после суммируемых значений, можно вообще не указывать функцию, а просто нажать на знак автосуммы. Значение будет установлено в нужное место.
Примерно так же можно суммировать количество ячеек с определенным значением для строки. Выбирается функция, выделяется нужный диапазон при указании аргумента. А если результат нужно поставить в следующем столбце той же строки, сэкономить время можно с помощью автосуммы.
Определить результат сложения может понадобиться и для одного и того же диапазона, расположенного сразу на нескольких страницах. Например, для определения суммы расходов по одинаковым статьям каждого месяца. В этом случае формула =СУММ(Январь:Апрель!B4) выдаст число, получившееся путем сложения ячейки B4 на каждом из 4 указанных листов.
Еще одна интересная функция сложения позволяет учитывать при расчетах только те ячейки таблицы, которые выведены на экран. Значения из скрытого диапазона не учитываются. Называется она «Промежуточные итоги» и предназначена для более сложных расчетов, чем простое сложение. Но использовать ее можно и для получения суммы.
Например, ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109; G5:G19) покажет сумму в диапазоне от G5 до G19 без учета ячеек, которые пользователь скрыл вручную (например, 11 и 12). Если строки скрыты при фильтрации, вместо аргумента 109 нужен ввод цифры 9.
Читайте также:
- Лучшие бюджетные планшеты: какой купить в 2021 году=«content_internal_link»>
- Как контролировать местонахождение ребенка через iPhone=«content_internal_link»>
Excel поможет считать быстрее калькулятора
Excel одна из самых известных программ Microsoft. Область применения Excel не ограничивается построением элементарных таблиц.
Сложение в Excel
Изучение программы следует начать с примера сложения. Рассмотрим, как можно складывать в excel:
- Для этого возьмём простой пример сложения чисел 6 и 5.
Берётся ячейка и в неё записывается пример«=6+5»
- После нажать клавишу Enter.
- Сумма«11»,отразится в той же ячейке. А строка формул покажет само равенство«=6+5».
Второй способ как в excel сложить числа:
Попробуем в excel сложить ячейки, разбросанные по таблице. Для этого выберем пример:
Нужно найти итоговое значение прибыли за 3 месяца за работы.
- Следует выделить задуманные значения и нажать на значок ∑ автосумма в excel на вкладке ФОРМУЛЫ.
Результат отразится в пустой ячейке в столбце Итого.
Чтобы найти значение остальных данных, нужно копировать формулу«=СУММ(C2:E2)», и поставить её в остальные ячейки столбика Итого. Таким способом можно посчитать в экселе сумму столбца автоматически. Точно также, можно в excel посчитать сумму времени.
Значения в таблицах могут поменяться, но формула в ячейке сохраняется прежней, что поможет посчитать формулу в экселе, не прилагая усилий и экономя время на дальнейшие расчёты.
Работа с функциями Excel
С помощью формул можно не только сделать сумму в экселе, но и делать выборку определённых значений, подчинённых конкретным условиям. Например, дана таблица с указанием наименования фруктов, их цены и веса.
Нужно подсчитать сумму ячеек где 1кг фруктов стоит меньше 100. Вручную этот процесс займёт время, ноExcel предлагает воспользоваться замечательной формулой–СУММЕСЛИМН. Что означает: найти сумму данных, если совпадает множество значений.
Задать алгоритм можно через вкладку Формулы, выбрать список математических функций и кликнуть мышкой по СУММЕСЛИМН. В ячейку нужно задать эту функцию и указать диапазон расчётов. Получим итоговое число 50. А в строке формул видим значение СУММЕСЛИМН (С2:С;В2:В5;« 90»).
Может потребоваться изменить значения, и выбрать дополнительные критерии. Например:В той же таблице требуется посчитать наименование фруктов со стоимостью больше 90 и определить их количество на складе по весу менее 20 кг.
После выборки формула изменится. Она примет вид«=СЧЁТЕСЛИМН (В2:В6;«>90»;С2:С6;«
А2–значение которое нужно заменить, $D$2:$E$5 –означает полное выделение таблицы, «$»—знак, который ограничивает копирование не нужной информации во втором столбике D2:Е5.
Мы написали формулу только в одну ячейку точно также можно провести нужную выборку, просто скопировав функцию и поставив во все ячейки.
Вертикальный просмотр ведётся только в первой таблице по всем позициям столбика. В этом случае, поиск слова «яблоки» пройдёт по столбику D.
Когда ячейка А2 будет обнаружена, программа определит из какой колонки выбрать нужное значение. В данной таблице это колонка Е ,в которой помещены изменённые цены. Если колонок в таблице 10 и более, то для поиска опять берётся первая колонка, а вы записываете нужную вам формулу для выборки.
В программу заложено множество функций, чтобы разобраться со всеми потребуется много времени. Рассмотрим основные из них:
- СУММ–используют для нахождения суммарного значения столбцов и ячеек
- ЕСЛИ–используют для выявления сравнения нескольких показателей. Определяется значением Больше и Меньше.
- ПРОСМОТР–используют для нахождения нужного значения или для выборки определенного столбца.
- ДАТА–используется для возвращения числа дней между задуманными датами
- ВПР–используют как вертикальный просмотр в поиске значений.
- СЦЕПИТЬ–используют для соединения нескольких столбцов в один.
- РУБЛЬ–используют для преобразования числа в текст, используя денежный эквивалент рубль.
- СОВПАД–используют вовремя нахождения одинаковых текстовых значений
- СТРОЧН–используют для преобразования всех заглавных букв в строчные
- ПОВТОР–используют при повторе текста нужное число раз.
Статья содержит основные этапы работы с таблицами в excel. Рассмотрены методы сложения и выборки по заданным критериям.
Считаем сумму ячеек в Microsoft Excel
Microsoft Excel – мощный программный инструмент для работы с электронными таблицами. В программе можно составлять таблицы и отчеты, производить расчеты любой сложности, строить диаграммы, графики и многое другое. Довольно часто Эксель используют для того, чтобы вычислить итоговую сумму в столбцах и строках таблицы, или, например, чтобы узнать сумму нескольких ячеек. В этом уроке мы с вами научимся считать сумму ячеек в программе Microsoft Excel благодаря пошаговой инструкции.
Данная функция является одной из самых популярных и комфортных для расчета суммы значений в ячейках таблицы. Для этого нужно сделать следующее:
- Переходим в вкладку “Главная”, левой кнопкой мыши (далее – ЛКМ) нажимаем на последнюю пустую ячейку столбца или строки, по которой нужно посчитать итоговую сумму и нажимаем кнопку “Автосумма”.
- Затем в ячейке автоматически заполнится формула расчета суммы.
- Чтобы получить итоговый результат, нажимаем клавишу “Enter”. Чтоб посчитать сумму конкретного диапазона ячеек, ЛКМ выбираем первую и последнюю ячейку требуемого диапазона строки или столбца. Далее нажимаем на кнопку “Автосумма” и результат сразу же появится в крайней ячейке столбца или ячейки (в зависимости от того, какой диапазон мы выбрали). Данный способ достаточно хорош и универсален, но у него есть один существенный недостаток – он может помочь только при работе с данными, последовательно расположенными в одной строке или столбце, а вот большой объем данных подсчитать таким образом невозможно, равно как и не получится пользоваться “Автосуммой” для отдаленных друг от друга ячеек.
Допустим, мы выделяем некую область ячеек и нажимаем на “Автосумма”.В итоге мы получим не итоговое значение по всем выделенным ячейкам, а сумму каждого столбца или строки по отдельности (в зависимости от того, каким образом мы выделили диапазон ячеек).
Чтоб посчитать сумму конкретного диапазона ячеек, ЛКМ выбираем первую и последнюю ячейку требуемого диапазона строки или столбца. 
Далее нажимаем на кнопку “Автосумма” и результат сразу же появится в крайней ячейке столбца или ячейки (в зависимости от того, какой диапазон мы выбрали). 
Данный способ достаточно хорош и универсален, но у него есть один существенный недостаток – он может помочь только при работе с данными, последовательно расположенными в одной строке или столбце, а вот большой объем данных подсчитать таким образом невозможно, равно как и не получится пользоваться “Автосуммой” для отдаленных друг от друга ячеек. 
В итоге мы получим не итоговое значение по всем выделенным ячейкам, а сумму каждого столбца или строки по отдельности (в зависимости от того, каким образом мы выделили диапазон ячеек). 
Функция “Сумм”
Данная функция в Microsoft Excel позволяет суммировать значения большого массива данных. Для этого необходимо сделать следующее:
- ЛКМ выделяем ячейку, в которую планируем вывести итоговый результат, далее нажимаем кнопку «Вставить функцию», которая находится с левой стороны строки формул.
- В открывшемся списке “Построителя формул” находим функцию “СУММ” и нажимаем “Вставить функцию” (или “OK”, в зависимости от версии программы). Чтобы быстро найти нудную функцию можно воспользоваться полем поиском.
- В появившемся окне по настройке функции указываем имена ячеек, которые необходимо сложить. Разумеется, самостоятельно набирать адрес ячеек можно, но вовсе не обязательно. Вместо этого можно выбрать нужный нам диапазон ячеек, выделив его ЛКМ.
- Если нужно выбрать еще один диапазон данных, щелкаем ЛКМ на поле “Число 2” и выбираем его аналогично процедуре, описанной выше. Добавлять новые диапазоны можно практически бесконечно, каждый раз нажимая кнопку “+” под последним.
- После того, как все нужные ячейки выделены, нажимаем кнопку “Готово”.
- После этого мы видим результат в ранее выбранной ячейке.
Работа с формулами
В программе Эксель можно посчитать сумму с помощью простой формулы сложения. Делается это следующим образом:
- ЛКМ выделяем ячейку, в которой хотим посчитать сумму. Затем, либо в самой ячейке, либо перейдя в строку формул, пишем знак “=”, ЛКМ нажимаем на первую ячейку, которая будет участвовать в расчетах, после нее пишем знак “+”, далее выбираем вторую, третью и все требуемые ячейки, не забывая между ними проставлять знак “+”.
- После того, как формула готова, нажимаем “Enter” и получаем результат в выбранной нами ячейке. Основным минусом данного способа является то, что сразу отобрать несколько ячеек невозможно, и необходимо указывать каждую по отдельности.
Основным минусом данного способа является то, что сразу отобрать несколько ячеек невозможно, и необходимо указывать каждую по отдельности.Просмотр суммы в программе Excel.
Помимо расчета суммы в отдельной ячейке, в программе Microsoft Excel можно просто просматривать сумму значений выбранных ячеек. Но это работает только при условии, что все ячейки располагаются рядом.
- Выделяем диапазон ячеек, сумму по которым мы хотим посмотреть.
- Смотрим вниз на результат, который отображается в строке состояния программы. Помимо общей суммы, мы также видим количество выделенных ячеек и среднее значение по ним.
Помимо общей суммы, мы также видим количество выделенных ячеек и среднее значение по ним.Как Вы могли заметить, программа Эксель позволяет решать задачу суммирования разными способами. Каждый из них имеет свои достоинства и недостатки, свою сложность и продуктивность в зависимости от поставленной задачи и ее специфики.
Как посчитать сумму в Excel с помощью формул
Многие пользователи даже не догадываются о всей мощи Excel. Ну да, слышали, что программа для работы с таблицами, да используют ее, просматривают кое какие документы. Признаюсь, подобным пользователем был и я, пока случайно не наткнулся на простую, казалось бы задачу: посчитать сумму ячеек в одной из своих таблиц в Excel. Раньше я это делал на калькуляторе (сейчас смешно :-P), а в этот раз таблица было очень большой, и решил, что пора изучить хотя бы простые одну-две формулы…
В этой статье расскажу о формуле суммы, чтобы было легче понять, рассмотрим пару простых примеров.
1) Чтобы посчитать какую-нибудь сумму простых чисел, можно нажать на любую ячейку в Excel и написать в ней, например, «=5+6», далее нажать просто Enter.
2) Результат не заставит себя ждать, в ячейке в которой вы написали формулу появиться результат «11». Кстати, если нажать на эту ячейку (где написано число 11) — в строке формулы (см. скриншот выше, стрелка №2, справа) — вы увидите не число 11, а все те же «=6+5».
3) Теперь попробуем посчитать сумму чисел из ячеек. Для этого первым делом перейдем в раздел «ФОРМУЛЫ» (меню сверху).
Далее выделите несколько ячеек, сумму значений которых хотите посчитать (на скриншоте ниже выделены зеленым цветом три вида прибыли). Затем нажмите левой кнопкой мышки на вкладку «Автосумма».
4) В результате, в рядом стоящей ячейки появиться сумма трех предыдущих ячеек. См. скриншот ниже.
Кстати, если перейти на ячейку с результатом, то мы увидим и саму формулу: «=СУММ(C2:E2)», где C2:E2 — последовательность ячеек, которые нужно сложить.
5) Кстати, если вы хотите в таблице посчитать сумму во всех оставшихся строк, то просто скопируйте формулу (=СУММ(C2:E2)) во все остальные ячейки. Excel посчитает все сам автоматически.
Даже такая, казалось бы, простая формула — делает Excel мощным инструментом при подсчете данных! А теперь представьте, что в Excel не одна, а сотни самых различных формул (кстати, о работе с самыми популярными я уже рассказывал). Благодаря ним можно вести расчет чего угодно и как угодно, при этом, экономить кучу своего времени!
Суммирование в Excel через одну, две, три ячейки
Всем известен способ подсчёта данных в Excel путём элементарного сложения ячеек =C2+C4+C6, либо с помощью функции =СУММ(C2;C4;C6), но если таблица большая, то такой способ выведет из себя даже самого терпеливого. Поэтому предлагаю рассмотреть способ суммирования по условию: =СУММЕСЛИ(B2:B15;»Доход»;C2:C15)
Если же столбец с ключевым словом для выборочного суммирования отсутствует, то можно использовать формулу массива, которая будет проверять номер строки для каждой ячейки и суммировать только те из них, где номер четный.
Если же нужно суммировать каждую третью, четвертую и т.д. ячейки, то изменяется только делитель номера строки:
В качестве второго варианта можно использовать функцию СУММПРОИЗВ, которая вводится как обычная формула (без Ctrl+Shift+Enter), но работает также:
Для подсчёта суммы, находящейся в столбцах, вместо СТРОКА используем СТОЛБЕЦ, затем протягиваем формулу вниз:
Примечание: для красивого отображения цифр устанавливаем формат ячеек «Числовой» – Число десятичных знаков «0» – галочка на «Разделитель групп разрядов». Для процентов «Процентный» – Число десятичных знаков «0».
А для англоязычных excel кто переводить формулы будет, еклмн? Как мне конвертировать теперь?
Я позже в комментарии выложу английскую версию формул, специально для вас
Я понимаю, что это вопрос привычки, но с английских формул на «=ВВПНПМЦЧТЬ» не перейду никогда.
Вопрос не по теме. Есть ли в Excel возможность суммировать значения только в помеченных ячейках/строках?
А2+В2 и так далее
Поддерживаю предыдущего комментатора.
Ну как там? Мне тоже надо на английском
Как сделать случайные значения чтоб они не изменялись при любом другом действии?
спасибо! не только для него, если не сложно, можете дублировать формулы для англ версий, многие понятны, но возможно и ошибусь.
Впредь я буду английский вариант тоже указывать
Нахуя англоязычные формулы? Если есть нормальная русская версия
Я могу понять, когда пользуются английским Adobe Lightroom или Sony Vegas. Но зачем пользоваться продуктами для американского рынка компании Microsoft (встречаю скриншоты с кнопкой Start регулярно) — для меня загадка.
может это будет тебе удивительно слышать, но есть винды полностью англоязычные, как в моем случае. следовательно, весь софт в основной своей массе, а в особенности, весь офис тоже англоязычный.
Я тебя понял, могу и я тогда попросить о небольшой услуге? Можно мне на русском ресурсе продублировать тогда и немецкие формулы заодно? Просто есть Windows и Excel на немецком языке.
если тебя не затруднит, то обратись к автору поста, пожалуйста, возможно, он сможет.
Он один а нас таких много. Я конвертировать не могу пока он формулы для тебя переводит.
В любой локализованной версии экселя можно вводить английский вариант формулы, он поймёт правильно и переведёт в локализованный вариант.
Ну и плюс — MS Office не единственный табличный процессор в мире. LibreOffice, WPS Office используют те же названия функций, только не локализуют их вместе с интерфейсом.
Потому что английские формулы выглядят логичней и привычней.
Многие русскоязычные пользователи используют английскую версию именно из-за этого. Более того английские формулы интуитивно понятны (vlookup vs впр например)
ВПР я примерно имею представление за что отвечает, знаю что заменяется конструкцией ИНДЕКС и ПОИСКПОЗ, а vlookup впервые увидал пол-минуты назад. Мб и интуитивно понятно, но не в российских реалиях. Я бы вряд ли правильно назвал российский аналог этой функции. Просто потому что в России пользуется абсолютное большинство русским офисом.
Я имел ввиду другое:
Первый раз видишь формулу ВПР и даже примерно не представляешь что она делает.
VLOOKUP — уже понятно, что это как минимум поиск.
Большинство пользователей даже сводными таблицами не пользуются, чего уж там говорить про VBA и многоуровневые формулы.Таковы реалии.
Поэтому «большинство» совсем не показатель.
Интересно, но нихера не понятно)))
Спасибо! Я думала, я одна тупень)) рада, что нас двое)))
Если надо сумировать, например, каждую третью клетку первого столбца, то можно в втором столбце написать в первых трех клетках 0, 0, 1 и протянуть вниз. Получится стобец вида 0, 0, 1, 0, 0, 1, 0, 0, 1. В третий столбец пишем формулу произвдения первого на второй и третий сумируем (в сумму будут входить только нужные значния, так как ненужные будут 0). ИМХО так проще, чем рзбиратся как работают те функции.
P. s. С екслем давно не игрался и это написал по памяти. Может не работать если я что-то забыл.
Так примитивнее. Но по моему опыту, так гораздо удобнее. Особенно когда баг ищешь.
Для чего в формулах два минуса ?Что это дает?
Чтобы преобразовать логические значения ИСТИНА и ЛОЖЬ, получившиеся в результате проверки формулой ОСТАТ(СТРОКА(C2:C17);4)=1, в единицы и нули, используется двойной минус – он равносилен двукратному умножению на -1, т.е. не меняет значения, но конвертирует ИСТИНУ в 1, а ЛОЖЬ в 0.
А почему бы тогда не использовать просто суммирование по условию?
А умножение ИСТИНА/ЛОЖЬ на число напрямую дает ошибку?
Подскажите, пожалуйста, как можно формулой найти количество уникальных значений в столбце?
А также, что нужно, чтобы все уникальные значения вынести в отельный столбец?
Если данную операцию нужно сделать один раз, то можно воспользоваться advanced filter. Перенести только уникальные значения в отдельный столбик и там уже посчитать. Еще, возможно, подойдет remove duplicates.
Вы про сравнение списков?
Можно, у меня есть формулы решающие именно эти задачи. Нужны функции Если, суммесли, макс. Можно в Гугл вбить «вывести уникальные значения таблицы Эксель» буквально первая ссылка предлагает решение
А есть в екселе возможность посчитать ячейки определенного цвета без макросов и сторонних надстроек?
Есть такая возможность, я выложу это отдельным постом
Спасибо за посты об Экселе.
Сам пользуюсь очень плотно, неплохо его вцелом знаю. Но иногда бывают моменты, когда приходится делать вручную, и как автоматизировать, пока не придумал.
Есть столбец с наименованиями деталей. Рядом с ним столбец с их стоимостью соответственно.
В третьем столбце количество этих деталей соответственно.
Иногда список деталей расширяется, столбцы удлинняются.
Мне нужно как-то считать общую стоимость этих деталей. Сделать дополнительный столбец в котором будет перемножаться второй и третий, а в нужной ячейке всё суммироваться — не вариант, будет громоздко (там ещё куча других столбцов, не имеющим отношения к делу).
По сути сейчас в ячейке с общей стоимостью стоит формула, вбитая вручную, где суммируются произведения 2 и 3 столбца.
Может как-то можно не обновлять не каждый раз и сделать проще?
Сам задал вопрос, и полез в Гугл. Оказывается, есть просто такая функция, как сумма произведений:))) Не знал, теперь знаю
Человечище. Я искал этот способ полгода назад, нигде нормального описания нет. А тут всё понятно. Благодарю безмерно!
На месте ТСа я бы подумал, можно ли данную таблицу сделать на фанкциях AGGREGATE и SUBTOTAL
Я вот как о них узнал (и о возможности игнора этих самых AGGREGATE и SUBTOTAL )- перешел на них с функции SUM насовсем. Рекомендую ТСу заиметь такую же привычку.
Добрый день. Подскажите, есть 2 таблицы, в одной два столбца (наименование и код) В другой только наименование. Наименования в обоих совпадают. Возможно ли как нибудь автоматически подставить коды из первой таблицы во вторую?
нет ничего хуже для екцеля, чем суммпризв и суммеслиМН. 16гб памяти, винт твердотельный, а файл булет подвисать уже при нескольких таких сотнях формул, даже если юзать динамические рейеджи. проще прогу написать, которая просто +а1+а3+а6 сделает или суммесли с ключрм, чем это говно юзать.
Говорят, любой язык программирования проще эксэля
Это не совсем так, возможности языков гораздо шире, но. с тех пор, как я освоил связку PHP+MySQL, я забыл про ексель, как страшный сон. То есть, я ВООБЩЕ перестал им пользоваться в своей работе, НАВСЕГДА! Хотя раньше слыл среди коллег в нём едва ли не экспертом.
Конечно, тут есть свои «подводные камни» – сами таблички в браузере придётся рисовать самому, и интерфейсы для ввода данных, так что и HTML маст хэв, как говорится, и владение стилями лишним не будет. Но эти умения дадут намного больше возможностей и раскроют гораздо более широкие горизонты приложения своих усилий, чем убогие функции типа СУММЕСЛИ.
Ни один самый навороченный табличный процессор не сравнится с изящной простотой и мощностью реляционных баз данных и любого языка программирования.
Много лишних действий. Особенно по засовыванию данных в MySQL.
Все таки PHP+MySQL и Ексель инструменты для совершенно разных действий. Как и 1С и ексель, например.
Работа с БД в екселе имеется, VBA тоже есть. И интерфейсы отрисовывать не нужно.
А это уже включение макросов и гемор при отправке файла кому-нибудь еще.
Пишешь число, далее тыкаешь на ячейке с числом правой кнопкой, из раскрывающегося списка выбираешь «формат ячеек», вкладка «число», там ставишь число знаков после запятой = 0, ставишь галочку «разделитель групп разрядов»
Суммирование в Excel, используя функции СУММ и СУММЕСЛИ
В этом уроке мы не будем рассматривать, как посчитать сумму в Excel при помощи оператора сложения, автосуммы и прочих инструментов. Сегодня мы разберем всего две функции: СУММ и СУММЕСЛИ. Спешу Вас обрадовать, их функционала достаточно, чтобы решать практически все возможные вопросы суммирования в Excel.
Функция СУММ – простое суммирование ячеек в Excel
Функция СУММ вычисляет сумму всех своих аргументов. Она является наиболее часто используемой функцией в Excel. К примеру, нам необходимо сложить значения в трех ячейках. Мы, конечно же, можем воспользоваться обычным оператором суммирования:
Но мы также можем воспользоваться функцией СУММ и записать формулу следующим образом:
Поскольку функция СУММ поддерживает работу не только с отдельными ячейками, но и целыми диапазонами, то вышеприведенную формулу можно модифицировать:
Истинная мощь функции СУММ раскрывается, когда необходимо сложить большое количество ячеек в Excel. В примере ниже требуется просуммировать 12 значений. Функция СУММ позволяет сделать это несколькими щелчками мышью, если же использовать оператор сложения, то провозиться придется долго.
В следующем примере функция СУММ складывает весь столбец А, а это 1048576 значений:
Следующая формула подсчитывает сумму всех ячеек, содержащихся на рабочем листе Лист1. Чтобы данная формула не вызвала циклической ошибки, ее необходимо использовать на другом рабочем листе Excel (отличном от Лист1).
Функция СУММ может принимать до 255 аргументов и суммировать сразу несколько несмежных диапазонов или ячеек:
Если среди суммируемых значений содержится текст, то функция СУММ их игнорирует, т.е. не включает в расчет:
Если же текстовые значения попытаться сложить оператором суммирования, то формула вернет ошибку:
Функция СУММ достаточно универсальна и позволяет использовать в качестве своих аргументов не только ссылки на ячейки и диапазоны, но и различные математические операторы и даже другие функции Excel:
Если интересно узнать, как посчитать накопительную сумму в Excel, обратитесь к этому уроку.
СУММЕСЛИ – условная сумма в Excel
Функция СУММЕСЛИ позволяет подсчитать условную сумму в Excel, т.е. сумму ячеек, которые удовлетворяют определенному критерию. Функция СУММЕСЛИ может содержать только один критерий.
Например, следующая формула суммируем только положительные числа диапазона A1:A10. Обратите внимание, что условие заключено в двойные кавычки.
В качестве условия можно использовать значение ячейки. В этом случае, поменяв условие, изменится и результат:
Меняем условие, меняется и результат:
Условие можно комбинировать, используя оператор конкатенации. В примере ниже формула вернет сумму значений, которые больше значения в ячейке B1.
Во всех примерах, приведенных ранее, мы производили суммирование и проверяли условие по одному и тому же диапазону. А что делать, если необходимо просуммировать один диапазон, а условие проверять по-другому?
На этот случай функция СУММЕСЛИ припасла третий необязательный аргумент, который отвечает за диапазон, который необходимо просуммировать. Т.е. по первому аргументу функция проверяет условие, а третий подлежит суммированию.
В следующем примере мы просуммируем общую стоимость всех проданных фруктов. Для этого воспользуемся следующей формулой:
Нажав Enter получим результат:
Если одного условия Вам не достаточно, то Вы всегда можете воспользоваться функцией СУММЕСЛИМН, которая позволяет осуществлять условное суммирование в Excel на основе нескольких критериев.
Суммирование – это одно из основных действий, которое пользователь выполняет в Microsoft Excel. Функции СУММ и СУММЕСЛИ созданы, чтобы облегчить эту задачу и дать пользователям максимально удобный инструмент. Надеюсь, что этот урок помог Вам освоить базовые функции суммирования в Excel, и теперь Вы сможете свободно применять эти знания на практике. Удачи Вам и успехов в изучении Excel!