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

Обновлено: 08.07.2024

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Всем известен способ подсчёта данных в Excel путём элементарного сложения ячеек =C2+C4+C6, либо с помощью функции =СУММ(C2;C4;C6), но если таблица большая, то такой способ выведет из себя даже самого терпеливого. Поэтому предлагаю рассмотреть способ суммирования по условию: =СУММЕСЛИ(B2:B15;"Доход";C2:C15)

=СРЗНАЧЕСЛИ(B2:B15;"План";C2:C15)

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

=СУММ(ЕСЛИ(ОСТАТ(СТРОКА(B2:B15);2)=0;

=МИН(ЕСЛИ(ОСТАТ(СТРОКА(B2:B15);2)=0;

=МАКС(ЕСЛИ(ОСТАТ(СТРОКА(B2:B15);2)=1;

Если же нужно суммировать каждую третью, четвертую и т.д. ячейки, то изменяется только делитель номера строки:

=СУММ(ЕСЛИ(ОСТАТ(СТРОКА(C2:C17);4)=1;

В качестве второго варианта можно использовать функцию СУММПРОИЗВ, которая вводится как обычная формула (без Ctrl+Shift+Enter), но работает также:

=СУММПРОИЗВ(--(ОСТАТ(СТРОКА(C2:C17);4)=1);C2:C17)

Для подсчёта суммы, находящейся в столбцах, вместо СТРОКА используем СТОЛБЕЦ, затем протягиваем формулу вниз:

=СУММПРОИЗВ(--(ОСТАТ(СТОЛБЕЦ(B3:F3);2)=0);B3:F3)

MS, Libreoffice & Google docs

484 поста 12.9K подписчиков

Правила сообщества

2. Публиковать посты соответствующие тематике сообщества

3. Проявлять уважение к пользователям

4. Не допускается публикация постов с вопросами, ответы на которые легко найти с помощью любого поискового сайта.

По интересующим вопросам можно обратиться к автору поста схожей тематики, либо к пользователям в комментариях

Важно - сообщество призвано помочь, а не постебаться над постами авторов! Помните, не все обладают 100 процентными знаниями и навыками работы с Office. Хотя вы и можете написать, что вы знали об описываемом приёме раньше, пост неинтересный и т.п. и т.д., просьба воздержаться от подобных комментариев, вместо этого предложите способ лучше, либо дополните его своей полезной информацией и вам будут благодарны пользователи.

Утверждения вроде "пост - отстой", это оскорбление автора и будет наказываться баном.

А для англоязычных excel кто переводить формулы будет, еклмн? Как мне конвертировать теперь?

Если надо сумировать, например, каждую третью клетку первого столбца, то можно в втором столбце написать в первых трех клетках 0, 0, 1 и протянуть вниз. Получится стобец вида 0, 0, 1, 0, 0, 1, 0, 0, 1. В третий столбец пишем формулу произвдения первого на второй и третий сумируем (в сумму будут входить только нужные значния, так как ненужные будут 0). ИМХО так проще, чем рзбиратся как работают те функции.

P. s. С екслем давно не игрался и это написал по памяти. Может не работать если я что-то забыл.

Интересно, но нихера не понятно)))

Для чего в формулах два минуса ?Что это дает?

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

А также, что нужно, чтобы все уникальные значения вынести в отельный столбец?

А есть в екселе возможность посчитать ячейки определенного цвета без макросов и сторонних надстроек?

Спасибо за посты об Экселе.

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

Есть столбец с наименованиями деталей. Рядом с ним столбец с их стоимостью соответственно.

В третьем столбце количество этих деталей соответственно.

Иногда список деталей расширяется, столбцы удлинняются.

Мне нужно как-то считать общую стоимость этих деталей. Сделать дополнительный столбец в котором будет перемножаться второй и третий, а в нужной ячейке всё суммироваться - не вариант, будет громоздко (там ещё куча других столбцов, не имеющим отношения к делу).

По сути сейчас в ячейке с общей стоимостью стоит формула, вбитая вручную, где суммируются произведения 2 и 3 столбца.

Может как-то можно не обновлять не каждый раз и сделать проще?

Человечище. Я искал этот способ полгода назад, нигде нормального описания нет. А тут всё понятно. Благодарю безмерно!

нет ничего хуже для екцеля, чем суммпризв и суммеслиМН. 16гб памяти, винт твердотельный, а файл булет подвисать уже при нескольких таких сотнях формул, даже если юзать динамические рейеджи. проще прогу написать, которая просто +а1+а3+а6 сделает или суммесли с ключрм, чем это говно юзать.

Добрый день. Подскажите, есть 2 таблицы, в одной два столбца (наименование и код) В другой только наименование. Наименования в обоих совпадают. Возможно ли как нибудь автоматически подставить коды из первой таблицы во вторую?

На месте ТСа я бы подумал, можно ли данную таблицу сделать на фанкциях AGGREGATE и SUBTOTAL

Я вот как о них узнал (и о возможности игнора этих самых AGGREGATE и SUBTOTAL )- перешел на них с функции SUM насовсем. Рекомендую ТСу заиметь такую же привычку.

Подскажите пожалуйста. Есть задача: есть много столбцов с разными значениям, как сделать чтобы при пересечении в нужных столбцах выводил например ОК. Например, как на картинке. Пересекается любые значения из зеленого, серого и фиолетового столбца - значит выводит ОК, А если пересекается значения из зеленого, коричневого и фиолетового, то выводит ошибку. То есть нужно сделать чтобы при совпадении трех значений из трех столбцов выводил нужное мне значение

Как сделать суммеслимн с условиями находящимися:
условие1 в диапазон= столбец, следом условие2=диапазон строка

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

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

Эхх вот у меня задачка , всё не могу догнать , есть 2 столбца с цифрами , в обоих надо найти 0 и по 10 символов от нуля скопировать в новые столбцы )) всё время ерунда выходит

Два вопроса: "Что за символ --?" и "Как вбить формулы в ячейку, чтобы они отображались текстом?" (т.е. текст самой формулы, как у тебя сделано справа)

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

Кто знает как в либре кальк произвести сквозное суммирование ячейки со всех листов?

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

БигДата по екселевски. Кмк такие неудобные вычисления в питоне/R будут в 10 раз быстрее. Вот у друга в банке всех с екселя на R переводят.

Где Фергана где Фергана

Вопрос есть у меня, не знаю стоит ли создавать отдельную тему так спрошу здесь.
Есть таблица на 40 тысяч строк.
1 столбец это порядковый номер строки и 2 столбец уникальные данные. Как я могу вывести список порядковых номеров одинаковых уникальных данных со всей базы .? К этому списку постоянно будет обращение и нужна возможность выбора, скажем, 4 позиции от последней записи

Сохранил на всякий случай, хоть это мне и не пригодится))

Наркоманы. В экселе давно есть пауер квери и сводные таблицы, в которых все можно вертеть и крутить как тебе надо. А вывсе еще считаете через суммесли, тьфу

А че, на том, что постишь школьную программу можно плюсов срубить?

А меня такая задача.
Есть ячейка. В ней число, допустим 10. Я веошу в эту ячейку число 2. В этой ячейке стало 12. Тоесть суммируется. Завтра я опять в эту ячейку вношу число и оно суммируется с последним

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

1. Белое стирать только с белым! И нет, МОЛОЧНОЕ - это не белое. Я заметила, что если молочное и белое стирать вместе, то белое моментально перестает быть белым, в нем появляется левый, совершенно ненужный, оттенок. Так что, сюрприз! - молочное тоже линяет. И цвет слоновой кости. И кремовый. И вообще любой, который не белый. Особо упоротым предлагаю разделить белое на простое белое и кипенно-белое (с голубым оттенком) и тоже не смешивать их между собой. Мне проще, я кипенно-белый на дух не переношу, этот оттенок меня бесит. Поэтому все мои белые вещи именно белые.

2. Долой отбеливающие гранулы! Это такие синенькие штучки в порошке, особенно их много в порошках "для белых вещей". По сути, это синька. При одноразовом использовании вещь визуально выглядит белее. Но если пользоваться этой хренью регулярно, одежда приобретает серо-голубой оттенок, весьма похабный. То же касается гранул розового, оранжевого, да вообще любого цвета. Они подкрашивают ткань. Ну их нафиг. Я методом научного тыка нашла порошок, который просто тупо белый. Причем ашановский, марки "каждый день". Слишком дешевый, чтобы производители стали добавлять внутрь какие-то цветные приблуды. То что надо, короче.

3. Кислородный отбеливатель - при каждой стирке! Эта штука хорошо выбивает из ткани загрязнения, а одежда пачкается не только там, где какое-то пятно, но и вся целиком тоже, просто не так заметно. Но раз за разом невыполосканная грязь накапливается, и шмотка становится серой. Кислородный отбеливатель тоже должен быть без цветных включений! Я подсела на Frau Schmidt белее белого (не уверена, что правильно написала). Дороговат, но обходится дешевле, чем покупка новых рубашек. А когда он исчез из ближайшего магазина и я в отчаянии стала пробовать другие, ниче так оказался золушка (в пакетиках). Насчет длительного использования пока ничего не могу сказать. Но он дешевый и сходу работает вроде так же хорошо, как и "Фрау". Подумываю над сменой религии.

4. Кондиционер для белья - белого цвета! Ага, прикиньте. Я с художественным образованием, и тренированный глаз хорошо различает оттенки. В общем, когда после розового кондиционера вся стирка белья стала отдавать розовым, я перестала пользоваться кондиционерами с красителями (они ж не сами по себе такие радужные). А впрочем, потом я вообще перестала ими пользоваться. Мне, вроде как, и так норм. Все что мягкое и пушистое, я стираю ласками, а все остальное и без кондиционера нормально себя чувствует. Может я не права. Но бутылка кондиционера для белья нехило так утяжеляет сумку с покупками. Этот аргумент оказался решающим.

5. Ласка для белого это просто ласка для белого. Я, как фанат ласок, не нашла никакой разницы между лаской для белого и лаской обычной. Ну, кроме запаха (у "белой" чет не понравился). Если сравнивать стирку ласка плюс кислородный отбеливатель и дешманский порошок плюс тот же отбеливатель, разницы в белизне одежды я не заметила. Эксперимент был длительный. После ласки ткань мягче, факт. Но хлопковым рубашкам на это начхать, имхо. А батистовые я еще и подкрахмаливаю вообще.

6. Пятна. Я для себя делю пятна на красители, жир и. не знаю, как сказать. Застревающие? Когда загрязнитель в виде микрогранул застревает в волокнах ткани. Это земля, пепел, глина, грязь уличная - изначально оно может быть в виде каши, но в итоге высыхает и именно застревает в ткани, не окрашивая ее. Вот о таком виде загрязнений хочется рассказать в первую очередь. Худшее, что можно сделать с таким свежим пятном - потереть или размазать. Или попытаться смыть сразу (если грязюка имеет свойство только застревать, но не окрашивает волокна, это важно) - так эта хрень распределится по большей площади и глубже засядет. Вляпался - не трогай. Когда грязюка высыхает, нужно стряхнуть сначала все масштабное, а потом мягкой щеткой (я использую ультрамягкую зубную, гарантированно не повредит волокна ткани) "вылущиваем" всю эту пыль из ткани. Хорошо помогает потереть ткань в руках. такое характерное движение, когда пятна выстирывают. Только насухую. Когда вытряслось все что можно и что нельзя, и прогресса больше не наблюдается, можно добавлять воду. Таким пятнам плевать на хлорку, зато они отлично реагируют на кислородный отбеливатель. Сделать из него кашку и намазать на влажную вещь в районе пятна - прекрасно помогает. (Если не стирали с кислородным отбеливателем вещь на регулярной основе и она потемнела, то на этом месте может образоваться светлое пятно, имейте ввиду!) Минут через 5-10 потереть щеткой еще. Так же хорошо выбивает загрязнения из волокон струя воды - просто подставить под кран, чтобы вода била сквозь пятно. Можно совместить с щеткой.

Красители. Эти пятна лучше отмывать как можно скорее. Чем дольше краситель в контакте с тканью, тем сильнее въедается. Вино, кофе, морковка - это все оно. Если в падлу стирать немедленно, хотя бы в тазик с водой закиньте, причем прохладной. Красители лучше всего реагируют на хлорку - она просто их обесцвечивает. Но сначала все-таки лучше потереть с обычным средством типа порошка или ласки, а потом уже хлорить. Хлорный отбеливатель нужно наносить только на пятно - хлорка не только разрушает волокна ткани, но и делает белую вещь желтой, если злоупотреблять ею. Я лью ее чистоганом, но только на пятно (иногда кисточкой наношу точечно), и прям на месте смотрю, обычно секунд через 10 уже виден результат, иногда требуется до 5 минут, в зависимости от нахальности пятна. Если пятно не яркое, лучше развести хлорку водой чутка. Красящие пятна не любят очень горячую воду - не даром при окрашивании ткань кипятят. Лучше въедается. Просто теплой воды достаточно.

Жир. Тоже лучше отстирывать сразу. Хуже жирных пятен только жирные застывшие. Если лениво капец - под струю горячей воды а потом в тазик с горячей водой, жир "плывет" от высокой температуры. Жирные пятна хорошо отстирываются горячей водой плюс фейри. Только прозрачным фейри, который Pure, или как его там. Он бесцветный. Зеленый фейри оставляет после себя зеленые пятна. Так же посуху часть жира можно снять мелом/тальком. Пятна от мела потом удалять по схеме застревающей грязи XD Комбинированные пятна выводятся по наитию и путем незамутненного творчества.

7. Температура стирки. Это чисто эмпирическое наблюдение, я хз чем это объяснить, но после стирки при 60 градусах белье приобретает сероватый оттенок, особенно нижнее, а при 30-40 градусах нет такого эффекта, при том, что порошок и кислородный отбеливатель одинаковые. Не знаю, почему так. Поэтому на высокой температуре стираю только тогда, когда дезинфекция и выведение пятен приоритетнее. "Посерение" обратимо и почти полностью снимается за 3-4 стирки при 40 градусах с кислородным отбеливателем. Кроме нижнего белья. Оно может остаться серым навсегда.

8. Кровь отстирывается в холодной воде. Да, в горячей ее тоже можно оттереть. Но я специально ставила эксперимент, оттирая два одинаковых кровавых пятна в холодной и в горячей воде. В первом случае процесс шел куда веселее. Тока руки замерзли. наверное "холодная вода" я поняла слишком буквально. Оттирайте кровищу содой, хорошо получается. Где-то вычитала, способ прям зашел мне.

9. Глажка утюгом на максимальной температуре делает ткань желтой. А при отпаривании могут остаться пятна от накипи и ржавчины, только "девственный" утюг не имеет в резервуаре для воды какой-нибудь херни. Глажка на 2-2,5 с максимально мощным паром ЧЕРЕЗ ПРОУТЮЖИЛЬНИК - гарантия что и отгладится отлично, и не попортится ничего. А сушка на плечиках или в расправленном виде (перед развешиванием хорошо бы еще встряхнуть вещь) в половине случаев вообще снимает необходимость в утюжке.

Вот как-то так. Я не химик, не алхимик, не химчистик, но портной, но все равно - данный манускрипт основан на личных наблюдениях и экспериментах и вообще ни на что не претендует.

П.С. если вы закинули в стирку с белым красный носок, шмотки можно спасти! Стирки с кислородным отбеливателем в дозировке х2 плюс пара заходов на замачивание в разведенной хлорке. Я так постирала скопом ВСЕ свои белые шмотки. И да. Там был носок. Зеленый. Первые две недели шмотки были разной степени цветности, потом постепенно одуплились. Кислородный отбеливатель выбил из них лишнюю краску, а остатки осветлила хлорка. Вещи теперь не вот прям снежно-белые, но белые точно. Кто не художник, не спалит, что они были зелеными когда-то. Да и я уже, наверное, тоже.

Начинаем серию статей для новичков в Excel. Сегодня речь пойдет о самой нужной и часто используемой функции в Excel, а именно - суммирование чисел.

Есть несколько способов как вычислить сумму двух и более чисел.

Вариант 1. Самый простой

Напечатайте в любой ячейке число, в соседней ячейке второй число. В третьей ячейке наберите = затем кликните на первую ячейку, далее наберите + и кликните на вторую ячейку, а затем Enter. Готово. Вы получили сумму двух чисел.

Вариант 2.Использование функции Автоматическое сложение

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

Вариант 3.Использование функции СУММ()

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

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