1655

Суммирование в Excel через одну, две, три ячейки

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

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

Суммирование в Excel через одну, две, три ячейки Excel, Таблица, Сумма, Лайфхак, Полезное, Длиннопост

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

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

B2:B15))

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

B2:B15))

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

B2:B15))

Суммирование в Excel через одну, две, три ячейки Excel, Таблица, Сумма, Лайфхак, Полезное, Длиннопост

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

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

C2:C17))


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

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

Суммирование в Excel через одну, две, три ячейки Excel, Таблица, Сумма, Лайфхак, Полезное, Длиннопост

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

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

Суммирование в Excel через одну, две, три ячейки Excel, Таблица, Сумма, Лайфхак, Полезное, Длиннопост

Примечание: для красивого отображения цифр устанавливаем формат ячеек «Числовой» – Число десятичных знаков «0» – галочка на «Разделитель групп разрядов». Для процентов «Процентный» – Число десятичных знаков «0».

Найдены дубликаты

+12

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

раскрыть ветку 27
+16

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

раскрыть ветку 13
+4

Спасибо!

Я понимаю, что это вопрос привычки, но с английских формул на "=ВВПНПМЦЧТЬ" не перейду никогда.

+4

буду признателен

+2
Зачем использовать СУММЕСЛИ, когда в новом офисе СУММЕСЛИМН?
раскрыть ветку 1
0

Вопрос не по теме. Есть ли в Excel возможность суммировать значения только в помеченных ячейках/строках?

раскрыть ветку 2
0

Поддерживаю предыдущего комментатора.

0

Ну как там? Мне тоже надо на английском

0

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

раскрыть ветку 1
0

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

раскрыть ветку 1
0
В сети лежат таблици соответствия, погугли. Я так Excel на курсах ботанил.
-5

Нахуя англоязычные формулы? Если есть нормальная русская версия


Я могу понять, когда пользуются английским Adobe Lightroom или Sony Vegas. Но зачем пользоваться продуктами для американского рынка компании Microsoft (встречаю скриншоты с кнопкой Start регулярно) - для меня загадка.

раскрыть ветку 11
+7

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

раскрыть ветку 6
+4

Потому что английские формулы выглядят логичней и привычней.

Многие русскоязычные  пользователи используют английскую версию именно из-за этого. Более того английские формулы интуитивно понятны (vlookup vs впр например)

раскрыть ветку 2
0
Ну, как минимум, чтоб учить не узко Excel, а табличный процессор, для которого как минимум есть аналог LibreOffice, в котором формулы на английском даже с русской локализацией.
ещё комментарии
+5

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

раскрыть ветку 3
+3

Спасибо! Я думала, я одна тупень)) рада, что нас двое)))

раскрыть ветку 1
0
Я третий, я с вами.
Во втором предложении и далее по тексту: буквы понятные, слова знакомые, а вот что все это значит...
0

вроде понятно

+5

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

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

раскрыть ветку 1
0
И зачем нужны два лишних столбца?
+2

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

раскрыть ветку 4
+3

Чтобы преобразовать логические значения ИСТИНА и ЛОЖЬ, получившиеся в результате проверки формулой ОСТАТ(СТРОКА(C2:C17);4)=1, в единицы и нули, используется двойной минус – он равносилен двукратному умножению на -1, т.е. не меняет значения, но конвертирует ИСТИНУ в 1, а ЛОЖЬ в 0.

раскрыть ветку 3
0

А почему бы тогда не использовать просто суммирование по условию?

0
Задачка для интереса если есть к примеру длинный столбец с значениями расхода Х за пол часа а нужно получить столбец без пустых строк расходов за час. Т.е по сути сложить каждые два значения в столбце. Как это сделать без доп. столбцов? Есть как минимум 2 способа
0

А умножение ИСТИНА/ЛОЖЬ на число напрямую дает ошибку?

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

А также, что нужно, чтобы все уникальные значения вынести в отельный столбец?
раскрыть ветку 5
+2
Первое, что на ум пришло - сделать сводную таблицу и вынести значения в группировку по строкам. Получишь все различные значения, а формулой "счет" по получившемуся списку их количество.
0
ВПР функция, если я верно уловил мысль
0

Если данную операцию нужно сделать один раз, то можно воспользоваться advanced filter. Перенести только уникальные значения в отдельный столбик и там уже посчитать. Еще, возможно, подойдет remove duplicates.

0

Вы про сравнение списков?

-1

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

+2

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

раскрыть ветку 3
+11

Есть такая возможность, я выложу это отдельным постом

раскрыть ветку 2
0
Епт, неужели? Жду!
0

Спасибо, подпишусь

+1

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

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

Вот например:

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

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

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

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

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

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

раскрыть ветку 3
0

Сам задал вопрос, и полез в Гугл. Оказывается, есть просто такая функция, как сумма произведений:))) Не знал, теперь знаю

раскрыть ветку 2
0
Интересуюсь таблицами давно, но не эксперт.посоветуйте что-то для систематизации знаний?
0
А есть Power Query и PowerPivot. Надстройки в экселе. С выводом в сводную таблицу. Вообще минимум усилий, максимум скорости и удобства использования
+1
Поздравляю, ты изобрел сводную таблицу
+1

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

0

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

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

0

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

раскрыть ветку 1
0
ВПР
0

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

0
Майкрософт живёт за счёт экселя.
0
Не проще ли добавить столбец со значениями 1,2,3,1,2,3... и сделать суммесли(...;"3";...)?
0
Спасибо, сделаю бабке на работе, а то она достала звать меня каждый раз когда график ломается после добавления в таблицу новых людей
0
Эксель - убийца 1С. 😁
раскрыть ветку 3
+1

Говорят, любой язык программирования проще эксэля

раскрыть ветку 2
0

Это не совсем так, возможности языков гораздо шире, но... с тех пор, как я освоил связку PHP+MySQL, я забыл про ексель, как страшный сон. То есть, я ВООБЩЕ перестал им пользоваться в своей работе, НАВСЕГДА! Хотя раньше слыл среди коллег в нём едва ли не экспертом.

Конечно, тут есть свои «подводные камни» – сами таблички в браузере придётся рисовать самому, и интерфейсы для ввода данных, так что и HTML маст хэв, как говорится, и владение стилями лишним не будет. Но эти умения дадут намного больше возможностей и раскроют гораздо более широкие горизонты приложения своих усилий, чем убогие функции типа СУММЕСЛИ.

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

раскрыть ветку 1
0
Пипец. А не лучше ли запилить макрофункцию типа СУММ_КАЖДЫЙ_ЭННЫЙ(диапазон, шаг) ?
раскрыть ветку 1
0

А это уже включение макросов и гемор при отправке файла кому-нибудь еще.

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

Пишешь число, далее тыкаешь на ячейке с числом правой кнопкой, из раскрывающегося списка выбираешь "формат ячеек", вкладка "число", там ставишь число знаков после запятой = 0, ставишь галочку "разделитель групп разрядов"

раскрыть ветку 1
0
Это я думаю всем понятно, а как сделать, чтобы в таком формате всегда осуществлялись расчёты и копирования
0

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

0

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

раскрыть ветку 3
0

-- это два минуса, второй вопрос - формат ячеек текстовый

раскрыть ветку 2
0

А зачем два минуса?

раскрыть ветку 1
0

Фергана...План...


Долина солнца

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

Банкует планом

Мудрец Ходжа

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

Получается данные не уникальные, если есть одинаковые значения с разными порядковыми номерами?  Попробуйте через Data - remove duplicates. Он должен одинаковые данные объединить в общую ячейку, при этом порядковые номера должны остаться. Если я правильно понял, то что вам нужно.

раскрыть ветку 1
-1
Вот здесь уникальные данные 8.10.A.J отсортированы фильтром вручную чтобы знать номер строки в базе ( обведено красным). Надо автоматизировать процесс - ввел нужный параметр 8.10.A.J. - получил список номеров строк. (в красном). Как то так..
Иллюстрация к комментарию
0

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

раскрыть ветку 8
0

буду брать сотрудника в договорной отдел на управленческий учет, обязательно напишу в требованиях "знание Pyton обязательно"))

раскрыть ветку 5
0

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

раскрыть ветку 2
0

Думаете, ваш сотрудник будет знать всю эту магию в экселе или просто будет сидеть и вручную вписывать?

0
А по ЗП-то потянете? ))
0
Шо за питон?
раскрыть ветку 1
0

Python - язык программирования.

Очень удобен для обработки данных

0

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

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

0

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

Иллюстрация к комментарию
0

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

раскрыть ветку 7
+4

Теперь в резюме можешь добавить "умею программировать на эксель"

раскрыть ветку 5
+3
Я всегда думал что это шутка
раскрыть ветку 4
0

Я думал я один сохраню.. и не читаю

-1

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

-1

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

раскрыть ветку 1
+3
Школьную программу? Люди после универа до сих пор сумму ячеек калькулятором считают, а ты тут такую фантастику говоришь.
-2
невозможно воспринимать перевод операторов на русский. просто жесть.
-3
А меня такая задача.
Есть ячейка. В ней число, допустим 10. Я веошу в эту ячейку число 2. В этой ячейке стало 12. Тоесть суммируется. Завтра я опять в эту ячейку вношу число и оно суммируется с последним
раскрыть ветку 8
+3

Так это же вы про калькулятор говорите, его уже давно изобрели

раскрыть ветку 6
-4
Не ёрничайте. Прекрасно понимаете о чем я. Дак есть ли такая возможность? Показать сумму в той же ячейке, в которую вводится одно слогаемое, а вторым выступает сумма от предыдущей операции сложения
раскрыть ветку 5
0

Формы в екселе. И таблица. Как раз то, что вам хочется.

Похожие посты
Возможно, вас заинтересуют другие посты по тегам: