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

Регистрация

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

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

Старая форма входа

Страница 1 из 1 1
Модератор форума: китин, _Boroda_

Мир MS Excel » Вопросы и решения » Вопросы по Excel » Автоматическое сумирование и вычитание по критериям (Формулы/Formulas)

Автоматическое сумирование и вычитание по критериям

Дата: Четверг, 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

Ответить

Дата: Четверг, 28.03.2024, 17:35 | Сообщение № 2

Группа: Авторы

Ранг: Ветеран

Сообщений: 557

Репутация: 126 ±

Замечаний: 0% ±

Excel 2019

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

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

К сообщению приложен файл: ew_avtomatizacija_sklada_28_03.xlsx (14.5 Kb)

Ответить

Дата: Четверг, 28.03.2024, 17:36 | Сообщение № 3

Группа: Друзья

Ранг: Участник клуба

Сообщений: 2747

Репутация: 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

Ответить

Дата: Четверг, 28.03.2024, 17:45 | Сообщение № 4

Группа: Пользователи

Ранг: Новичок

Сообщений: 14

Репутация: 0 ±

Замечаний: 0% ±

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

Ответить

Дата: Четверг, 28.03.2024, 18:25 | Сообщение № 5

Группа: Друзья

Ранг: Участник клуба

Сообщений: 2747

Репутация: 1137 ±

Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1

Цитата Gustav, 28.03.2024 в 17:36, в сообщении № 3 (

)

=СУММЕСЛИ($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

Ответить

Дата: Пятница, 29.03.2024, 08:49 | Сообщение № 6

Группа: Пользователи

Ранг: Новичок

Сообщений: 14

Репутация: 0 ±

Замечаний: 0% ±

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

Ответить

Дата: Пятница, 29.03.2024, 09:16 | Сообщение № 7

Группа: Друзья

Ранг: Старожил

Сообщений: 1647

Репутация: 208 ±

Замечаний: 0% ±

Excel LTSC 2024 RUS

Здравствуйте DJ,

Цитата DJ, 29.03.2024 в 08:49, в сообщении № 6 (

)

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

Либо (если правильно понял) в формуле замените 3 на 5 либо так:

Код

=СУММЕСЛИ(ИНДЕКС(началоИприход;;1); A17; ИНДЕКС(началоИприход;;СТОЛБЕЦ())) - СУММЕСЛИ(ИНДЕКС(расход;;1); A17; ИНДЕКС(расход;;СТОЛБЕЦ()))

К сообщению приложен файл: 2163729.xlsx (10.6 Kb)

Сообщение отредактировал DrMini - Пятница, 29.03.2024, 09:17

Ответить

Дата: Пятница, 29.03.2024, 09:52 | Сообщение № 8

Группа: Пользователи

Ранг: Новичок

Сообщений: 14

Репутация: 0 ±

Замечаний: 0% ±

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

Ответить

Дата: Пятница, 29.03.2024, 09:56 | Сообщение № 9

Группа: Пользователи

Ранг: Новичок

Сообщений: 14

Репутация: 0 ±

Замечаний: 0% ±

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

Ответить

Дата: Пятница, 29.03.2024, 09:59 | Сообщение № 10

Группа: Друзья

Ранг: Старожил

Сообщений: 1647

Репутация: 208 ±

Замечаний: 0% ±

Excel LTSC 2024 RUS

Цитата DJ, 29.03.2024 в 09:52, в сообщении № 8 (

)

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

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

Ответить

Дата: Пятница, 29.03.2024, 10:02 | Сообщение № 11

Группа: Друзья

Ранг: Старожил

Сообщений: 1647

Репутация: 208 ±

Замечаний: 0% ±

Excel LTSC 2024 RUS

Цитата DJ, 29.03.2024 в 09:52, в сообщении № 8 (

)

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

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

Ответить

Дата: Пятница, 29.03.2024, 10:03 | Сообщение № 12

Группа: Друзья

Ранг: Участник клуба

Сообщений: 2747

Репутация: 1137 ±

Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1

Цитата DJ, 29.03.2024 в 08:49, в сообщении № 6 (

)

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

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

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

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

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

Сообщение отредактировал Gustav - Пятница, 29.03.2024, 10:07

Ответить

Дата: Пятница, 29.03.2024, 10:44 | Сообщение № 13

Группа: Пользователи

Ранг: Новичок

Сообщений: 14

Репутация: 0 ±

Замечаний: 0% ±

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

К сообщению приложен файл: 2163729_2.xlsx (9.3 Kb)

Ответить

Дата: Пятница, 29.03.2024, 11:22 | Сообщение № 14

Группа: Друзья

Ранг: Участник клуба

Сообщений: 2747

Репутация: 1137 ±

Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1

Цитата DJ, 29.03.2024 в 10:44, в сообщении № 13 (

)

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

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

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

Ответить

Дата: Пятница, 29.03.2024, 11:34 | Сообщение № 15

Группа: Пользователи

Ранг: Новичок

Сообщений: 14

Репутация: 0 ±

Замечаний: 0% ±

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

Ответить

Дата: Пятница, 29.03.2024, 11:38 | Сообщение № 16

Группа: Пользователи

Ранг: Новичок

Сообщений: 14

Репутация: 0 ±

Замечаний: 0% ±

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

Ответить

Дата: Пятница, 29.03.2024, 11:51 | Сообщение № 17

Группа: Друзья

Ранг: Участник клуба

Сообщений: 2747

Репутация: 1137 ±

Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1

Цитата DJ, 29.03.2024 в 11:38, в сообщении № 16 (

)

просто в ручную буду строчки добавлять

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

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

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

[/p.s.]

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

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

Сообщение отредактировал Gustav - Пятница, 29.03.2024, 12:03

Ответить

Дата: Пятница, 29.03.2024, 12:13 | Сообщение № 18

Группа: Пользователи

Ранг: Новичок

Сообщений: 14

Репутация: 0 ±

Замечаний: 0% ±

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

Ответить

Дата: Пятница, 29.03.2024, 13:36 | Сообщение № 19

Группа: Друзья

Ранг: Участник клуба

Сообщений: 2747

Репутация: 1137 ±

Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1

Цитата DJ, 29.03.2024 в 12:13, в сообщении № 18 (

)

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

Вы не поймете? Вот и я не пойму. Я понимал, когда делал формулу для первоначального варианта таблицы из сообщения № 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))

Цитата DJ, 29.03.2024 в 12:13, в сообщении № 18 (

)

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

Вы не поймете? Вот и я не пойму. Я понимал, когда делал формулу для первоначального варианта таблицы из сообщения № 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

Ответить

Сообщение

Цитата DJ, 29.03.2024 в 12:13, в сообщении № 18 (

)

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

Вы не поймете? Вот и я не пойму. Я понимал, когда делал формулу для первоначального варианта таблицы из сообщения № 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 обязательна!