Многие работая с электронными таблицами EXCEL в определённый момент сталкиваются с вопросом «как в EXCEL сложить числа в ячейках по определённому условию». В один из солнечных дней я также столкнулся с данным вопросом, но на просторах интернета не нашёл ответ на данный вопрос, однако я настойчивый и решил изучить справку программы, которая меня и натолкнула на мысль, а далее методом проб и ошибок я составил формулу, и как оказалось формула элементарная.
Всё началось с того, что я решил учитывать свои ежемесячные расходы и для этого создал таблицу, которую приложил к данной статье, ведь и вам она может пригодиться.
Теперь постараюсь подробно расписать принцип создания формулы. У меня есть в отчёте детальная статистика и сводный отчёт. В детальной статистике я вписываю свои ежедневные расходы, а в сводном отчёте считается сумма расходов по определённым категориям и общая сумма расходов.
Для примера возьмём категорию расходов «Покупки в магазинах». Нам надо, чтобы EXCEL находил все затраты по данной категории в детальной статистике, суммировал расходы по данной категории и записывал полученную сумму в ячейку D10.
Сначала запишем готовую формулу, которую вставляем в ячейку D10, а потом начнём разбираться в деталях. Готовая формула выглядит следующим образом (только для нашей статьи):
=СУММЕСЛИ($G$5:$G$300;("Покупки в магазинах");$H$5:$H$300)
Цветом выделены различные условия, чтобы было наглядней. Разберём по порядку. В процессе описания смотрите на картинку выше, чтобы было понятней. Делая снимок специально были захвачены буквы столбцов и цифры строк. Итак, приступаем.
- СУММЕСЛИ – этим условием мы говорим, что в ячейку надо записывать сумму значений определённых ячеек, если они соответствуют определённым условиям;
- $G$5:$G$300 – здесь мы указываем EXCEL, в каком столбце нам надо искать условие для выборки. В нашем случае поиск происходит в столбце G начиная со строки 5 и заканчивая строкой 300;
- («Покупки в магазинах») – здесь мы указываем искомое условие и по этому условию будут суммироваться значения ячеек, которые мы указываем далее…;
- $H$5:$H$300 – здесь мы указываем столбец, из которого будут браться числа для суммирования. В нашем случае значения берутся в столбце H начиная со строки 5 и заканчивая строкой 300.
Подводя итог можно сказать, что EXCEL суммирует только те значения из диапазона H5:H300, для которых соответствующие значения из диапазона G5:G300 равны «Покупки в магазинах» и записывает результат в ячейку D10.
Соответствующим образом можно в EXCEL сложить числа в ячейках по любому условию.
Знак $ в формуле используется для того, чтобы при копировании формулы с ячейки D10 в другие ячейки не происходило смещение. Рассмотрим пример формулы без знака $. К примеру, в ячейке D10 у нас вписана формула:
=СУММЕСЛИ(G5:G300;("Покупки в магазинах");H5:H300)
Далее мы хотим выводить сумму обедов в ячейке D11. Чтобы нам не переписывать формулу, нам можно копировать ячейку D10 и вставить в ячейку D11. Благодаря этому формула будет вставлена в D11, но тут мы можем заметить, что формула изменила значения заменив 5 на 6 и 300 на 301:
=СУММЕСЛИ(G6:G301;("Покупки в магазинах");H6:H301)
Произошло смещение. Если мы скопируем формулу в D12, то увидим уже смещение на 2 и так далее. Чтобы этого избежать мы формулу пишем со знаком $. Такие особенности EXCEL.
Таблица «Учёт ежемесячных расходов» — скачать
Спасибо!
Опять я, думалось комментарий не будет отправлен по причине обычных заморочек типа зайти с ВК и тому подобное…
Хорошая статья, искал давеча как с кабельного журнала метраж извлечь, считая себя знатоком экселя и… видимо ошибался :(.
А тут вот-раз и готово! У нас девочки по полдня с калькулятором сидят. Спасибо еще раз!
спасибо
спасибо.Четко,емко,понятно!!!! СПАСИБО!!!!!!
Отличная статья!
а возможно ли тоже самое провернуть, но только чтобы эти «покупки в магазинах» складывались из нескольких таблиц с результатом в одну ячейку???
Скорее всего можно, но тут надо думать как. Сразу решения в голове нет.
Вот поддерживаю. Как раз нужно такое сделать, но пока не знаю, как.
Добрый день!
Пожалуйста, подскажите, что нужно применить (формулу или макрос) для подсчета в таблице буквенные обозначения, например, Р, Ф, Д. При этом Р=3, Ф=1, Д=4.
Т.е. в таблице будут стоять буквы (в одной строке и столбце могут располагаться все буквы), а подсчитываться должно значение
Доступно и понятно,благодарю!)
Как раз тоже считаю бюджет и задалась вопросом как категории посчитать быстрее. Ваша статья пришлась кстати. Благодарю!
Добрый день! Копируя историю операций в Сбербанк онлайн, к числам автоматом добавляются буквы «руб.», бьюсь не первый месяц не могу избавится от руб., так как с ними автосумма в эксель всегда равна 0, то есть формула не работает. Может кто сталкивался с такой проблемой и знает решение?
Заменой по всему документу можно. Заменить «руб.» на ничто.
Спасибо за ответ, но именно в этом и состоял вопрос-как это сделать. Я не очень хорошо знаю нюансы экселя и не смог найти решение этого вопроса.
В открытом документе нажмите CTRL + H.
Откроется форма замены текста в документе.
В верхнем поле введите «руб.» (если надо, то с начальным пробелом).
В нижнем поле ничего не вводите.
Нажмите на кнопку «Заменить всё».
Программа заменит «руб.» на «ничто».
Спасибо! То, что искала)
ДД, Нужна помощь, есть такая таблица, где есть регион, адрес, месяц:
МО Краснопрудная, 20 Декабрь
МО Краснопрудная, 3 Июнь
МО Краснопрудная, 3 Июнь
Москва Краснопрудная, 20 Декабрь
МО Краснопрудная, 20 Июнь
задача, сделать формулу, чтоб выбрав критерий «МО», а затем месяц «Июнь», одинаковые адреса превращались в значение 1 и к ним добавлялись уникальные адреса. Таким образом получим, что в МО в Июне было 2 уникальных адреса.
Здравствуйте.Как модно посчитать сумму чисел ищ одной ячейки?
Например в ячейке 35 и нужно найти сумму этих чисел
3+ 5=8