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

Версия для слабовидящих

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

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

Автоматическое сумирование и вычитание по критериям (Формулы/Formulas)

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

Дата: Четверг, 28.03.2024, 16:42 | Сообщение № 1

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

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

Сообщений: 17

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

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

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

Сообщений: 625

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

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

Excel 2019

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

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

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

Ответить

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

Группа: Админы

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

Сообщений: 2843

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

Замечаний: ±

начинал с 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

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

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

Сообщений: 17

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

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

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

Ответить

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

Группа: Админы

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

Сообщений: 2843

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

Замечаний: ±

начинал с 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

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

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

Сообщений: 17

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

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

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

Ответить

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

Группа: Модераторы

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

Сообщений: 2044

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

Замечаний: 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

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

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

Сообщений: 17

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

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

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

Ответить

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

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

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

Сообщений: 17

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

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

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

Ответить

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

Группа: Модераторы

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

Сообщений: 2044

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

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

Excel LTSC 2024 RUS

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

писал(а)):

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

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

Ответить

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

Группа: Модераторы

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

Сообщений: 2044

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

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

Excel LTSC 2024 RUS

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

писал(а)):

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

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

Ответить

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

Группа: Админы

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

Сообщений: 2843

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

Замечаний: ±

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

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

писал(а)):

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

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

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

P.S.Ааа, виноват. Вы же перед "Количеством" хотите,

тогда как DrMini говорит делайте

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

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

Ответить

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

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

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

Сообщений: 17

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

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

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

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

Ответить

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

Группа: Админы

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

Сообщений: 2843

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

Замечаний: ±

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

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

писал(а)):

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

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

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

Ответить

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

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

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

Сообщений: 17

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

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

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

Ответить

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

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

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

Сообщений: 17

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

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

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

Ответить

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

Группа: Админы

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

Сообщений: 2843

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

Замечаний: ±

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

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

писал(а)):

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

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

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

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

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

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

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

Ответить

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

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

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

Сообщений: 17

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

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

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

Ответить

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

Группа: Админы

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

Сообщений: 2843

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

Замечаний: ±

начинал с 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

Автоматическое сумирование и вычитание по критериям (Формулы/Formulas)

Страница 1 из 1
1

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