Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Автоматическое сумирование и вычитание по критериям - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Автоматическое сумирование и вычитание по критериям (Формулы/Formulas)
Автоматическое сумирование и вычитание по критериям
DJ Дата: Четверг, 28.03.2024, 16:42 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 14
Репутация: 0 ±
Замечаний: 0% ±

Добрый день.
Есть таблица(файл прикладываю)
Как мы видим в ней есть 4 типа данных:
1. Материал на начало месяца
2. Материал приход
3. Материал расход
4. Материал остаток на конец месяца
Вот в четвертом я считаю в ручную, это не очень удобно. Как мне сделать функции чтоб он считал, или вычитал только по условиям которые произошли во 2 и 3 строке, основывая на данных 1 строки.
Забыл сказать, в 1 и 4 данные всегда одинаковые (материалы), а вот в 2 и 3 не всегда одинаковы
К сообщению приложен файл: summ_esli.xlsx (9.7 Kb)


Сообщение отредактировал DJ - Четверг, 28.03.2024, 16:45
 
Ответить
СообщениеДобрый день.
Есть таблица(файл прикладываю)
Как мы видим в ней есть 4 типа данных:
1. Материал на начало месяца
2. Материал приход
3. Материал расход
4. Материал остаток на конец месяца
Вот в четвертом я считаю в ручную, это не очень удобно. Как мне сделать функции чтоб он считал, или вычитал только по условиям которые произошли во 2 и 3 строке, основывая на данных 1 строки.
Забыл сказать, в 1 и 4 данные всегда одинаковые (материалы), а вот в 2 и 3 не всегда одинаковы

Автор - DJ
Дата добавления - 28.03.2024 в 16:42
NikitaDvorets Дата: Четверг, 28.03.2024, 17:35 | Сообщение № 2
Группа: Авторы
Ранг: Ветеран
Сообщений: 557
Репутация: 126 ±
Замечаний: 0% ±

Excel 2019
DJ, добрый день.

Может быть достаточно по-другому организовать данные?
К сообщению приложен файл: ew_avtomatizacija_sklada_28_03.xlsx (14.5 Kb)
 
Ответить
СообщениеDJ, добрый день.

Может быть достаточно по-другому организовать данные?

Автор - NikitaDvorets
Дата добавления - 28.03.2024 в 17:35
Gustav Дата: Четверг, 28.03.2024, 17:36 | Сообщение № 3
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2742
Репутация: 1137 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Если я правильно всё понял, то для ячейки C17 такая формула без затей:
Код
=СУММЕСЛИ($A$2:$A$6; A17; $C$2:$C$6) + СУММЕСЛИ($A$8:$A$10; A17; $C$8:$C$10) - СУММЕСЛИ($A$12:$A$14; A17; $C$12:$C$14)


Или даже короче, если объединить идущие друг за другом с одним знаком "начало месяца" и "приход":
Код
=СУММЕСЛИ($A$2:$A$10; A17; $C$2:$C$10) - СУММЕСЛИ($A$12:$A$14; A17; $C$12:$C$14)


МОИ: Ник, Tip box: 41001663842605

Сообщение отредактировал Gustav - Четверг, 28.03.2024, 17:39
 
Ответить
СообщениеЕсли я правильно всё понял, то для ячейки C17 такая формула без затей:
Код
=СУММЕСЛИ($A$2:$A$6; A17; $C$2:$C$6) + СУММЕСЛИ($A$8:$A$10; A17; $C$8:$C$10) - СУММЕСЛИ($A$12:$A$14; A17; $C$12:$C$14)


Или даже короче, если объединить идущие друг за другом с одним знаком "начало месяца" и "приход":
Код
=СУММЕСЛИ($A$2:$A$10; A17; $C$2:$C$10) - СУММЕСЛИ($A$12:$A$14; A17; $C$12:$C$14)

Автор - Gustav
Дата добавления - 28.03.2024 в 17:36
DJ Дата: Четверг, 28.03.2024, 17:45 | Сообщение № 4
Группа: Пользователи
Ранг: Новичок
Сообщений: 14
Репутация: 0 ±
Замечаний: 0% ±

NikitaDvorets, да, так было проще, но есть установленная форма, и там материал разбивается по разным работам.
 
Ответить
СообщениеNikitaDvorets, да, так было проще, но есть установленная форма, и там материал разбивается по разным работам.

Автор - DJ
Дата добавления - 28.03.2024 в 17:45
Gustav Дата: Четверг, 28.03.2024, 18:25 | Сообщение № 5
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2742
Репутация: 1137 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
=СУММЕСЛИ($A$2:$A$10; A17; $C$2:$C$10) - СУММЕСЛИ($A$12:$A$14; A17; $C$12:$C$14)

А можно по Ctrl+F3 создать динамические именованные диапазоны:
началоИприход
Код
=ИНДЕКС(Лист1!$A:$A; ПОИСКПОЗ("*начало месяца*"; Лист1!$A:$A;)) : ИНДЕКС(Лист1!$C:$C; ПОИСКПОЗ("*расход*"; Лист1!$A:$A;)-1)

расход
Код
=ИНДЕКС(Лист1!$A:$A; ПОИСКПОЗ("*расход*"; Лист1!$A:$A;)) : ИНДЕКС(Лист1!$C:$C; ПОИСКПОЗ("*конец месяца*"; Лист1!$A:$A;)-1)


И тогда формула для C"17" не будет зависеть от изменчивости по количеству строк разделов "начало месяца", "приход", "расход" в разных месяцах - только саму строку "17" надо будет подправить:
Код
=СУММЕСЛИ(ИНДЕКС(началоИприход;;1); A17; ИНДЕКС(началоИприход;;3)) - СУММЕСЛИ(ИНДЕКС(расход;;1); A17; ИНДЕКС(расход;;3))
К сообщению приложен файл: summ_esli_03.xlsx (9.6 Kb)


МОИ: Ник, Tip box: 41001663842605

Сообщение отредактировал Gustav - Четверг, 28.03.2024, 18:36
 
Ответить
Сообщение
=СУММЕСЛИ($A$2:$A$10; A17; $C$2:$C$10) - СУММЕСЛИ($A$12:$A$14; A17; $C$12:$C$14)

А можно по Ctrl+F3 создать динамические именованные диапазоны:
началоИприход
Код
=ИНДЕКС(Лист1!$A:$A; ПОИСКПОЗ("*начало месяца*"; Лист1!$A:$A;)) : ИНДЕКС(Лист1!$C:$C; ПОИСКПОЗ("*расход*"; Лист1!$A:$A;)-1)

расход
Код
=ИНДЕКС(Лист1!$A:$A; ПОИСКПОЗ("*расход*"; Лист1!$A:$A;)) : ИНДЕКС(Лист1!$C:$C; ПОИСКПОЗ("*конец месяца*"; Лист1!$A:$A;)-1)


И тогда формула для C"17" не будет зависеть от изменчивости по количеству строк разделов "начало месяца", "приход", "расход" в разных месяцах - только саму строку "17" надо будет подправить:
Код
=СУММЕСЛИ(ИНДЕКС(началоИприход;;1); A17; ИНДЕКС(началоИприход;;3)) - СУММЕСЛИ(ИНДЕКС(расход;;1); A17; ИНДЕКС(расход;;3))

Автор - Gustav
Дата добавления - 28.03.2024 в 18:25
DJ Дата: Пятница, 29.03.2024, 08:49 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 14
Репутация: 0 ±
Замечаний: 0% ±

Gustav, это идеальное решение. Но так как я не знаю как пользоваться выражениями. Подскажите, а если я хочу например перед столбцом «количество» поставить еще два столбца, просто с данными которые не нужно считать. А после столбца «количество» добавить такой же столбец но уже с другими данными (остатками) подскажите как сделать пожалуйста
 
Ответить
СообщениеGustav, это идеальное решение. Но так как я не знаю как пользоваться выражениями. Подскажите, а если я хочу например перед столбцом «количество» поставить еще два столбца, просто с данными которые не нужно считать. А после столбца «количество» добавить такой же столбец но уже с другими данными (остатками) подскажите как сделать пожалуйста

Автор - DJ
Дата добавления - 29.03.2024 в 08:49
DrMini Дата: Пятница, 29.03.2024, 09:16 | Сообщение № 7
Группа: Друзья
Ранг: Старожил
Сообщений: 1647
Репутация: 208 ±
Замечаний: 0% ±

Excel LTSC 2024 RUS
Здравствуйте DJ,
а если я хочу например перед столбцом «количество» поставить еще два столбца

Либо (если правильно понял) в формуле замените 3 на 5 либо так:
Код
=СУММЕСЛИ(ИНДЕКС(началоИприход;;1); A17; ИНДЕКС(началоИприход;;СТОЛБЕЦ())) - СУММЕСЛИ(ИНДЕКС(расход;;1); A17; ИНДЕКС(расход;;СТОЛБЕЦ()))
К сообщению приложен файл: 2163729.xlsx (10.6 Kb)


Сообщение отредактировал DrMini - Пятница, 29.03.2024, 09:17
 
Ответить
СообщениеЗдравствуйте DJ,
а если я хочу например перед столбцом «количество» поставить еще два столбца

Либо (если правильно понял) в формуле замените 3 на 5 либо так:
Код
=СУММЕСЛИ(ИНДЕКС(началоИприход;;1); A17; ИНДЕКС(началоИприход;;СТОЛБЕЦ())) - СУММЕСЛИ(ИНДЕКС(расход;;1); A17; ИНДЕКС(расход;;СТОЛБЕЦ()))

Автор - DrMini
Дата добавления - 29.03.2024 в 09:16
DJ Дата: Пятница, 29.03.2024, 09:52 | Сообщение № 8
Группа: Пользователи
Ранг: Новичок
Сообщений: 14
Репутация: 0 ±
Замечаний: 0% ±

DrMini, спасибо, а как сделать, чтоб еще один столбец был после количества? Но там было другое количество
 
Ответить
СообщениеDrMini, спасибо, а как сделать, чтоб еще один столбец был после количества? Но там было другое количество

Автор - DJ
Дата добавления - 29.03.2024 в 09:52
DJ Дата: Пятница, 29.03.2024, 09:56 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 14
Репутация: 0 ±
Замечаний: 0% ±

DrMini, точнее там будет не один, а 12 , 12 месяцев
 
Ответить
СообщениеDrMini, точнее там будет не один, а 12 , 12 месяцев

Автор - DJ
Дата добавления - 29.03.2024 в 09:56
DrMini Дата: Пятница, 29.03.2024, 09:59 | Сообщение № 10
Группа: Друзья
Ранг: Старожил
Сообщений: 1647
Репутация: 208 ±
Замечаний: 0% ±

Excel LTSC 2024 RUS
как сделать, чтоб еще один столбец был после количества?

Мне кажется делайте сколько угодно. Они на расчёты вроде не влияют.
 
Ответить
Сообщение
как сделать, чтоб еще один столбец был после количества?

Мне кажется делайте сколько угодно. Они на расчёты вроде не влияют.

Автор - DrMini
Дата добавления - 29.03.2024 в 09:59
DrMini Дата: Пятница, 29.03.2024, 10:02 | Сообщение № 11
Группа: Друзья
Ранг: Старожил
Сообщений: 1647
Репутация: 208 ±
Замечаний: 0% ±

Excel LTSC 2024 RUS
еще один столбец был после количества? Но там было другое количество

Какое количество и чего? Непонятно.
 
Ответить
Сообщение
еще один столбец был после количества? Но там было другое количество

Какое количество и чего? Непонятно.

Автор - DrMini
Дата добавления - 29.03.2024 в 10:02
Gustav Дата: Пятница, 29.03.2024, 10:03 | Сообщение № 12
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2742
Репутация: 1137 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
если я хочу например перед столбцом «количество» поставить еще два столбца

Если делаете это после создания именованных диапазонов, т.е. допустим в моем файле, то ничего не делаете: диапазоны сами перестроятся с колонок A и C на колонки C и E соответственно. Тоже самое и со столбцом "после" - он же после колонки E получится, поэтому тоже ничего не делаете.

Ну, а если хотите суммирования как-то "переключить", то покажите сначала окончательный вариант шаблона - сообразим что и как.

[p.s.]Ааа, виноват. Вы же перед "Количеством" хотите,[/p.s.]
тогда как DrMini говорит делайте :)


МОИ: Ник, Tip box: 41001663842605

Сообщение отредактировал Gustav - Пятница, 29.03.2024, 10:07
 
Ответить
Сообщение
если я хочу например перед столбцом «количество» поставить еще два столбца

Если делаете это после создания именованных диапазонов, т.е. допустим в моем файле, то ничего не делаете: диапазоны сами перестроятся с колонок A и C на колонки C и E соответственно. Тоже самое и со столбцом "после" - он же после колонки E получится, поэтому тоже ничего не делаете.

Ну, а если хотите суммирования как-то "переключить", то покажите сначала окончательный вариант шаблона - сообразим что и как.

[p.s.]Ааа, виноват. Вы же перед "Количеством" хотите,[/p.s.]
тогда как DrMini говорит делайте :)

Автор - Gustav
Дата добавления - 29.03.2024 в 10:03
DJ Дата: Пятница, 29.03.2024, 10:44 | Сообщение № 13
Группа: Пользователи
Ранг: Новичок
Сообщений: 14
Репутация: 0 ±
Замечаний: 0% ±

Gustav, я предложил файл, как я хочу видеть это, но чтоб это было как сделали с одним столбцом, а у меня данные по месяца
К сообщению приложен файл: 2163729_2.xlsx (9.3 Kb)
 
Ответить
СообщениеGustav, я предложил файл, как я хочу видеть это, но чтоб это было как сделали с одним столбцом, а у меня данные по месяца

Автор - DJ
Дата добавления - 29.03.2024 в 10:44
Gustav Дата: Пятница, 29.03.2024, 11:22 | Сообщение № 14
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2742
Репутация: 1137 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
я предложил файл, как я хочу видеть это

А как предполагается добавление новых материалов в эту таблицу? Возможно ли удаление материалов? Или список материалов постоянен и неизменен (по крайней мере, в течение года) ?


МОИ: Ник, Tip box: 41001663842605
 
Ответить
Сообщение
я предложил файл, как я хочу видеть это

А как предполагается добавление новых материалов в эту таблицу? Возможно ли удаление материалов? Или список материалов постоянен и неизменен (по крайней мере, в течение года) ?

Автор - Gustav
Дата добавления - 29.03.2024 в 11:22
DJ Дата: Пятница, 29.03.2024, 11:34 | Сообщение № 15
Группа: Пользователи
Ранг: Новичок
Сообщений: 14
Репутация: 0 ±
Замечаний: 0% ±

Gustav, материалы просто добавятся 50 штук, вот по ним и будет расход , и приход
 
Ответить
СообщениеGustav, материалы просто добавятся 50 штук, вот по ним и будет расход , и приход

Автор - DJ
Дата добавления - 29.03.2024 в 11:34
DJ Дата: Пятница, 29.03.2024, 11:38 | Сообщение № 16
Группа: Пользователи
Ранг: Новичок
Сообщений: 14
Репутация: 0 ±
Замечаний: 0% ±

Gustav, просто в ручную буду строчки добавлять в расходе и приходе, если они нужны будут
 
Ответить
СообщениеGustav, просто в ручную буду строчки добавлять в расходе и приходе, если они нужны будут

Автор - DJ
Дата добавления - 29.03.2024 в 11:38
Gustav Дата: Пятница, 29.03.2024, 11:51 | Сообщение № 17
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2742
Репутация: 1137 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
просто в ручную буду строчки добавлять

С точки зрения формул с функциями СУММЕСЛИМН имеет смысл содержимое колонки "Расход" (а лучше бы назвать ее как-нибудь более общО, например, "Тип" или "Операция" или "Направление" или "Поток" или "Категория") изменить, избавившись от объединенных ячеек и прописав эти "категории" в каждую строку (подобно тому как это сделано для "шт" и "сумма"). Если такой столбец из повторяющихся "категорий" будет раздражать, то его всегда можно скрыть.

Если этого не сделать, то предстоят утомительные, никому особо не нужные, усложнения формул.

[p.s.]Я предполагаю, что, прежде, чем мы пойдём дальше, Вы это осознаете и подправите в своем файле из сообщения № 13 :) [/p.s.]

P.P.S. Если так сильно нравятся объединенные ячейки, то так и быть - сделайте 2 колонки "категорий"- в одной с объединенными ячейками (и ее оставьте на виду), а в другой - с повторяющимися в каждой строке (и ее скройте).


МОИ: Ник, Tip box: 41001663842605

Сообщение отредактировал Gustav - Пятница, 29.03.2024, 12:03
 
Ответить
Сообщение
просто в ручную буду строчки добавлять

С точки зрения формул с функциями СУММЕСЛИМН имеет смысл содержимое колонки "Расход" (а лучше бы назвать ее как-нибудь более общО, например, "Тип" или "Операция" или "Направление" или "Поток" или "Категория") изменить, избавившись от объединенных ячеек и прописав эти "категории" в каждую строку (подобно тому как это сделано для "шт" и "сумма"). Если такой столбец из повторяющихся "категорий" будет раздражать, то его всегда можно скрыть.

Если этого не сделать, то предстоят утомительные, никому особо не нужные, усложнения формул.

[p.s.]Я предполагаю, что, прежде, чем мы пойдём дальше, Вы это осознаете и подправите в своем файле из сообщения № 13 :) [/p.s.]

P.P.S. Если так сильно нравятся объединенные ячейки, то так и быть - сделайте 2 колонки "категорий"- в одной с объединенными ячейками (и ее оставьте на виду), а в другой - с повторяющимися в каждой строке (и ее скройте).

Автор - Gustav
Дата добавления - 29.03.2024 в 11:51
DJ Дата: Пятница, 29.03.2024, 12:13 | Сообщение № 18
Группа: Пользователи
Ранг: Новичок
Сообщений: 14
Репутация: 0 ±
Замечаний: 0% ±

Gustav, меня устраивает ваша таблица, просто не пойму как добавить еще столбы, много столбцов с количеством. Чтоб так же считал
 
Ответить
СообщениеGustav, меня устраивает ваша таблица, просто не пойму как добавить еще столбы, много столбцов с количеством. Чтоб так же считал

Автор - DJ
Дата добавления - 29.03.2024 в 12:13
Gustav Дата: Пятница, 29.03.2024, 13:36 | Сообщение № 19
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2742
Репутация: 1137 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
просто не пойму как добавить еще столбы, много столбцов с количеством

Вы не поймете? Вот и я не пойму. Я понимал, когда делал формулу для первоначального варианта таблицы из сообщения № 1. Однако, с тех пор "собачка" явно "подросла за время пути" и для варианта таблицы из сообщения № 13 формулы первоначального варианта, если и реализуемы, то крайне неоптимальны. Поэтому о них забываем.

В прилагаемом файле я добавил столбец, который предложил в сообщении № 17. Формулы стали простыми, понятными и протягиваемыми, без динамических именованных диапазонов:

для итоговых строк разделов (для ячеек H3, H7, H10, H13 соответственно):
Код
=СУММЕСЛИМН(H:H;$F:$F;"остаток")

Код
=СУММЕСЛИМН(H:H;$F:$F;"приход")

Код
=СУММЕСЛИМН(H:H;$F:$F;"расход")

Код
=СУММЕСЛИМН(H:H;$F:$F;"на конец")

для строк материалов раздела "На конец" (для ячейки H14):
Код
=СУММЕСЛИМН(H:H;$F:$F;"остаток";$B:$B;$B14) + СУММЕСЛИМН(H:H;$F:$F;"приход";$B:$B;$B14) - СУММЕСЛИМН(H:H;$F:$F;"расход";$B:$B;$B14)


P.S. Последнюю формулу можно даже симпатично уплотнить (и при этом она не станет формулой массива!):
Код
=СУММ({1;1;-1} * СУММЕСЛИМН(H:H;$F:$F; {"остаток";"приход";"расход"};$B:$B;$B14))

Эта уплотнительная правка в файл не вошла, но ее можно применить самостоятельно. И можно даже наименования "категорий" не писать полностью:
Код
=СУММ({1;1;-1} * СУММЕСЛИМН(H:H;$F:$F; {"ост*";"при*";"рас*"};$B:$B;$B14))
К сообщению приложен файл: 6373602.xlsx (13.6 Kb)


МОИ: Ник, Tip box: 41001663842605

Сообщение отредактировал Gustav - Пятница, 29.03.2024, 14:05
 
Ответить
Сообщение
просто не пойму как добавить еще столбы, много столбцов с количеством

Вы не поймете? Вот и я не пойму. Я понимал, когда делал формулу для первоначального варианта таблицы из сообщения № 1. Однако, с тех пор "собачка" явно "подросла за время пути" и для варианта таблицы из сообщения № 13 формулы первоначального варианта, если и реализуемы, то крайне неоптимальны. Поэтому о них забываем.

В прилагаемом файле я добавил столбец, который предложил в сообщении № 17. Формулы стали простыми, понятными и протягиваемыми, без динамических именованных диапазонов:

для итоговых строк разделов (для ячеек H3, H7, H10, H13 соответственно):
Код
=СУММЕСЛИМН(H:H;$F:$F;"остаток")

Код
=СУММЕСЛИМН(H:H;$F:$F;"приход")

Код
=СУММЕСЛИМН(H:H;$F:$F;"расход")

Код
=СУММЕСЛИМН(H:H;$F:$F;"на конец")

для строк материалов раздела "На конец" (для ячейки H14):
Код
=СУММЕСЛИМН(H:H;$F:$F;"остаток";$B:$B;$B14) + СУММЕСЛИМН(H:H;$F:$F;"приход";$B:$B;$B14) - СУММЕСЛИМН(H:H;$F:$F;"расход";$B:$B;$B14)


P.S. Последнюю формулу можно даже симпатично уплотнить (и при этом она не станет формулой массива!):
Код
=СУММ({1;1;-1} * СУММЕСЛИМН(H:H;$F:$F; {"остаток";"приход";"расход"};$B:$B;$B14))

Эта уплотнительная правка в файл не вошла, но ее можно применить самостоятельно. И можно даже наименования "категорий" не писать полностью:
Код
=СУММ({1;1;-1} * СУММЕСЛИМН(H:H;$F:$F; {"ост*";"при*";"рас*"};$B:$B;$B14))

Автор - Gustav
Дата добавления - 29.03.2024 в 13:36
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Автоматическое сумирование и вычитание по критериям (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!