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

Вход

Регистрация

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

 

= Мир MS Excel/Подсчёт положительных значений диапазона с разных листов - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Подсчёт положительных значений диапазона с разных листов (Формулы/Formulas)
Подсчёт положительных значений диапазона с разных листов
DrMini Дата: Среда, 30.03.2022, 09:28 | Сообщение № 1
Группа: Проверенные
Ранг: Старожил
Сообщений: 1609
Репутация: 195 ±
Замечаний: 0% ±

Excel LTSC 2024 RUS
Доброго времени суток форумчане и гости форума.
Нужно подсчитать только положительные значения диапазона A1:C1 на трёх листах.
У меня получилась вот такая формула:
Код
=СУММ(СУММЕСЛИ(Январь!A1:C1;">0");СУММЕСЛИ(Февраль!A1:C1;">0");СУММЕСЛИ(Март!A1:C1;">0"))

На вид какая то несуразная.
Может можно её как то укоротить (упростить)?
К сообщению приложен файл: 20220330-0.xlsx (10.4 Kb)
 
Ответить
СообщениеДоброго времени суток форумчане и гости форума.
Нужно подсчитать только положительные значения диапазона A1:C1 на трёх листах.
У меня получилась вот такая формула:
Код
=СУММ(СУММЕСЛИ(Январь!A1:C1;">0");СУММЕСЛИ(Февраль!A1:C1;">0");СУММЕСЛИ(Март!A1:C1;">0"))

На вид какая то несуразная.
Может можно её как то укоротить (упростить)?

Автор - DrMini
Дата добавления - 30.03.2022 в 09:28
_Boroda_ Дата: Среда, 30.03.2022, 10:47 | Сообщение № 2
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Такой вариант
Код
=СУММПРОИЗВ(СУММЕСЛИ(ДВССЫЛ(ТЕКСТ("1-"&СТРОКА(1:3);"ММММ")&"!A1:C1");">0"))

Сейчас допишу и выложу формулу для каждого месяца еще
Добавлено
Вот формула для произвольного месяца
Код
=СУММПРОИЗВ(СУММЕСЛИ(ДВССЫЛ(ТЕКСТ("1-"&СТРОКА(ДВССЫЛ("1:"&ТЕКСТ("1-"&ПСТР(ЯЧЕЙКА("filename";A1);ПОИСК("]";ЯЧЕЙКА("filename";A1))+1;9);"М")));"ММММ")&"!A1:C1");">0"))
К сообщению приложен файл: 20220330-0_1.xlsx (12.0 Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995


Сообщение отредактировал _Boroda_ - Среда, 30.03.2022, 10:53
 
Ответить
СообщениеТакой вариант
Код
=СУММПРОИЗВ(СУММЕСЛИ(ДВССЫЛ(ТЕКСТ("1-"&СТРОКА(1:3);"ММММ")&"!A1:C1");">0"))

Сейчас допишу и выложу формулу для каждого месяца еще
Добавлено
Вот формула для произвольного месяца
Код
=СУММПРОИЗВ(СУММЕСЛИ(ДВССЫЛ(ТЕКСТ("1-"&СТРОКА(ДВССЫЛ("1:"&ТЕКСТ("1-"&ПСТР(ЯЧЕЙКА("filename";A1);ПОИСК("]";ЯЧЕЙКА("filename";A1))+1;9);"М")));"ММММ")&"!A1:C1");">0"))

Автор - _Boroda_
Дата добавления - 30.03.2022 в 10:47
DrMini Дата: Среда, 30.03.2022, 11:26 | Сообщение № 3
Группа: Проверенные
Ранг: Старожил
Сообщений: 1609
Репутация: 195 ±
Замечаний: 0% ±

Excel LTSC 2024 RUS
Такой вариант

Думаю оставлю свой вариант. Попроще будет.
Да и из за вот этого куска:
Код
"!A1:C1"

формула не протягивается.
По любому большое спасибо за Ваш вариант.
Очень интересно "поковыряться" в том, чего никогда не видел.
СПАСИБО.
 
Ответить
Сообщение
Такой вариант

Думаю оставлю свой вариант. Попроще будет.
Да и из за вот этого куска:
Код
"!A1:C1"

формула не протягивается.
По любому большое спасибо за Ваш вариант.
Очень интересно "поковыряться" в том, чего никогда не видел.
СПАСИБО.

Автор - DrMini
Дата добавления - 30.03.2022 в 11:26
_Boroda_ Дата: Среда, 30.03.2022, 11:50 | Сообщение № 4
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
формула не протягивается.

Дык как всегда, какой пример - такой ответ. Можно и протягиваемую формулу написать, не проблема.
Код
=СУММПРОИЗВ(СУММЕСЛИ(ДВССЫЛ(ТЕКСТ("1-"&СТРОКА(ДВССЫЛ("1:"&ТЕКСТ("1-"&ПСТР(ЯЧЕЙКА("filename";RC[-4]);ПОИСК("]";ЯЧЕЙКА("filename";RC[-4]))+1;9);"М")));"ММММ")&"!RC1:RC3";);">0"))


Думаю оставлю свой вариант. Попроще будет.

А в декабре попроще будет?
К сообщению приложен файл: 20220330-0_2.xlsx (13.8 Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
формула не протягивается.

Дык как всегда, какой пример - такой ответ. Можно и протягиваемую формулу написать, не проблема.
Код
=СУММПРОИЗВ(СУММЕСЛИ(ДВССЫЛ(ТЕКСТ("1-"&СТРОКА(ДВССЫЛ("1:"&ТЕКСТ("1-"&ПСТР(ЯЧЕЙКА("filename";RC[-4]);ПОИСК("]";ЯЧЕЙКА("filename";RC[-4]))+1;9);"М")));"ММММ")&"!RC1:RC3";);">0"))


Думаю оставлю свой вариант. Попроще будет.

А в декабре попроще будет?

Автор - _Boroda_
Дата добавления - 30.03.2022 в 11:50
NikitaDvorets Дата: Среда, 30.03.2022, 12:14 | Сообщение № 5
Группа: Авторы
Ранг: Ветеран
Сообщений: 550
Репутация: 123 ±
Замечаний: 0% ±

Excel 2019
Еще один вариант, с одной функцией:
Код
=СУММПРОИЗВ((Январь!A1:C1>0)*(Январь!A1:C1)+(Февраль!A1:C1>0)*(Февраль!A1:C1)+(Март!A1:C1>0)*(Март!A1:C1))


Сообщение отредактировал NikitaDvorets - Среда, 30.03.2022, 12:17
 
Ответить
СообщениеЕще один вариант, с одной функцией:
Код
=СУММПРОИЗВ((Январь!A1:C1>0)*(Январь!A1:C1)+(Февраль!A1:C1>0)*(Февраль!A1:C1)+(Март!A1:C1>0)*(Март!A1:C1))

Автор - NikitaDvorets
Дата добавления - 30.03.2022 в 12:14
DrMini Дата: Среда, 30.03.2022, 14:36 | Сообщение № 6
Группа: Проверенные
Ранг: Старожил
Сообщений: 1609
Репутация: 195 ±
Замечаний: 0% ±

Excel LTSC 2024 RUS
Цитата NikitaDvorets, 30.03.2022 в 12:14, в сообщении № 5 ()
Еще один вариант

Большое спасибо за помощь.
 
Ответить
Сообщение
Цитата NikitaDvorets, 30.03.2022 в 12:14, в сообщении № 5 ()
Еще один вариант

Большое спасибо за помощь.

Автор - DrMini
Дата добавления - 30.03.2022 в 14:36
_Boroda_ Дата: Среда, 30.03.2022, 14:38 | Сообщение № 7
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Забыл стиль поменять в последней формуле своей, которая протягиваемая
Вот так должно быть
Код
=СУММПРОИЗВ(СУММЕСЛИ(ДВССЫЛ(ТЕКСТ("1-"&СТРОКА(ДВССЫЛ("1:"&ТЕКСТ("1-"&ПСТР(ЯЧЕЙКА("filename";A1);ПОИСК("]";ЯЧЕЙКА("filename";A1))+1;9);"М")));"ММММ")&"!RC1:RC3";);">0"))
К сообщению приложен файл: 20220330-0_3.xlsx (13.5 Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеЗабыл стиль поменять в последней формуле своей, которая протягиваемая
Вот так должно быть
Код
=СУММПРОИЗВ(СУММЕСЛИ(ДВССЫЛ(ТЕКСТ("1-"&СТРОКА(ДВССЫЛ("1:"&ТЕКСТ("1-"&ПСТР(ЯЧЕЙКА("filename";A1);ПОИСК("]";ЯЧЕЙКА("filename";A1))+1;9);"М")));"ММММ")&"!RC1:RC3";);">0"))

Автор - _Boroda_
Дата добавления - 30.03.2022 в 14:38
DrMini Дата: Среда, 30.03.2022, 14:40 | Сообщение № 8
Группа: Проверенные
Ранг: Старожил
Сообщений: 1609
Репутация: 195 ±
Замечаний: 0% ±

Excel LTSC 2024 RUS
в декабре попроще будет?

В Декабре будет так же. Попросили просуммировать по кварталам. Про весь год пока не заикались... но думаю им захочется и за год.
Сейчас начну разбираться в Вашей формуле. Как прикрепить на квартала и на весь год.
СПАСИБО!


Сообщение отредактировал DrMini - Среда, 30.03.2022, 14:40
 
Ответить
Сообщение
в декабре попроще будет?

В Декабре будет так же. Попросили просуммировать по кварталам. Про весь год пока не заикались... но думаю им захочется и за год.
Сейчас начну разбираться в Вашей формуле. Как прикрепить на квартала и на весь год.
СПАСИБО!

Автор - DrMini
Дата добавления - 30.03.2022 в 14:40
DrMini Дата: Среда, 30.03.2022, 14:52 | Сообщение № 9
Группа: Проверенные
Ранг: Старожил
Сообщений: 1609
Репутация: 195 ±
Замечаний: 0% ±

Excel LTSC 2024 RUS
Александр подскажите пожалуйста, за что отвечает вот этот кусок формулы в конце:
Код
"!RC1:RC3"

Это для стиля ссылок R1C1 ???
Хотя работает и при обычных ссылках.
 
Ответить
СообщениеАлександр подскажите пожалуйста, за что отвечает вот этот кусок формулы в конце:
Код
"!RC1:RC3"

Это для стиля ссылок R1C1 ???
Хотя работает и при обычных ссылках.

Автор - DrMini
Дата добавления - 30.03.2022 в 14:52
_Boroda_ Дата: Среда, 30.03.2022, 15:18 | Сообщение № 10
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
1 ЯЧЕЙКА("filename";A1) - дает полный путь к файлу (включая название листа)
2 ПОИСК("]";п.1) - номер позиции закрывающейся квадратной скобки в этом пути (после нее как раз будет имя листа)
3 ПСТР(п.1;п.2+1;9) - от номера позиции из п.2 + 1 символ берем 9 символов вправо. Самое длинное название месяца Сентябрь - 8 букв, значит, 9 точно хватит. Получаем название листа, у нас это название месяца
4 "1-"&п.3 - прилепляем слева текст "1-"
5 ТЕКСТ(п.4;"М") - п.4 автоматически преобразуется в 1-е число найденного месяца текущего года. ТЕКСТом преобразуем его в цифру месяца (для мая, например, в 5)
6 "1:"&п.5 - прилепляем слева текст "1:". Получается для мая 1:5
7 ДВССЫЛ(п.6) - получаем ссылку на строки, найденные в п.6
8 СТРОКА(п.7) - дает массив номеров указанных строк. Для мая - 1:2:3:4:5
9 "1-"&п.8 - прилепляем слева текст "1-"
10 ТЕКСТ(п.9;"ММММ") - п.9 автоматически преобразуется в 1-е число найденного месяца текущего года. ТЕКСТом преобразуем его в месяц буквами (для мая, например, в Май). Получаем массив названий месяцев
11 п.10&"!RC1:RC3" - прилепляем справа текст "!RC1:RC3". RC1:RC3 - это то же самое, что и $A1:$C1, только стиль ссылок не "А1", а "RC" (см. Файл - Параметры - Формулы - Стиль ссылок). Получаем массив диапазонов для каждого листа
12 ДВССЫЛ(п.11;) - у ДВССЫЛ есть два аргумента (см. справку). Если второй ИСТИНА или отсутствует, то подразумевается стиль ссылок А1, а если ЛОЖЬ, то стиль RC. Но ЛОЖЬ можно не писать, достаточно просто поставить точку с запятой, это как бы 0, а 0 - это ЛОЖЬ. Получаем массив ссылок для каждого листа
13 СУММЕСЛИ(п.12);">0") - обычная СУММЕСЛИ, но для каждого листа своя. Получаем массив результатов СУММЕСЛИ для каждого листа по отдельности
14 СУММПРОИЗВ(п.13) - складываем полученное в п.13


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995


Сообщение отредактировал _Boroda_ - Среда, 30.03.2022, 15:24
 
Ответить
Сообщение1 ЯЧЕЙКА("filename";A1) - дает полный путь к файлу (включая название листа)
2 ПОИСК("]";п.1) - номер позиции закрывающейся квадратной скобки в этом пути (после нее как раз будет имя листа)
3 ПСТР(п.1;п.2+1;9) - от номера позиции из п.2 + 1 символ берем 9 символов вправо. Самое длинное название месяца Сентябрь - 8 букв, значит, 9 точно хватит. Получаем название листа, у нас это название месяца
4 "1-"&п.3 - прилепляем слева текст "1-"
5 ТЕКСТ(п.4;"М") - п.4 автоматически преобразуется в 1-е число найденного месяца текущего года. ТЕКСТом преобразуем его в цифру месяца (для мая, например, в 5)
6 "1:"&п.5 - прилепляем слева текст "1:". Получается для мая 1:5
7 ДВССЫЛ(п.6) - получаем ссылку на строки, найденные в п.6
8 СТРОКА(п.7) - дает массив номеров указанных строк. Для мая - 1:2:3:4:5
9 "1-"&п.8 - прилепляем слева текст "1-"
10 ТЕКСТ(п.9;"ММММ") - п.9 автоматически преобразуется в 1-е число найденного месяца текущего года. ТЕКСТом преобразуем его в месяц буквами (для мая, например, в Май). Получаем массив названий месяцев
11 п.10&"!RC1:RC3" - прилепляем справа текст "!RC1:RC3". RC1:RC3 - это то же самое, что и $A1:$C1, только стиль ссылок не "А1", а "RC" (см. Файл - Параметры - Формулы - Стиль ссылок). Получаем массив диапазонов для каждого листа
12 ДВССЫЛ(п.11;) - у ДВССЫЛ есть два аргумента (см. справку). Если второй ИСТИНА или отсутствует, то подразумевается стиль ссылок А1, а если ЛОЖЬ, то стиль RC. Но ЛОЖЬ можно не писать, достаточно просто поставить точку с запятой, это как бы 0, а 0 - это ЛОЖЬ. Получаем массив ссылок для каждого листа
13 СУММЕСЛИ(п.12);">0") - обычная СУММЕСЛИ, но для каждого листа своя. Получаем массив результатов СУММЕСЛИ для каждого листа по отдельности
14 СУММПРОИЗВ(п.13) - складываем полученное в п.13

Автор - _Boroda_
Дата добавления - 30.03.2022 в 15:18
_Boroda_ Дата: Среда, 30.03.2022, 15:22 | Сообщение № 11
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
RC1:RC3 - это текущая строка, столбцы 1:3. Поэтому такую формулу можно протягивать - текущая-то строка - это та строка, в которой сама формула и находится
Можно и стилем А1, но тогда пришлось бы писать не "!A1:C1", а
Код
"!A"&СТРОКА()&":C"&СТРОКА()

гораздо больше, чем просто
Код
RC1:RC3


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995


Сообщение отредактировал _Boroda_ - Среда, 30.03.2022, 15:24
 
Ответить
СообщениеRC1:RC3 - это текущая строка, столбцы 1:3. Поэтому такую формулу можно протягивать - текущая-то строка - это та строка, в которой сама формула и находится
Можно и стилем А1, но тогда пришлось бы писать не "!A1:C1", а
Код
"!A"&СТРОКА()&":C"&СТРОКА()

гораздо больше, чем просто
Код
RC1:RC3

Автор - _Boroda_
Дата добавления - 30.03.2022 в 15:22
DrMini Дата: Среда, 30.03.2022, 15:47 | Сообщение № 12
Группа: Проверенные
Ранг: Старожил
Сообщений: 1609
Репутация: 195 ±
Замечаний: 0% ±

Excel LTSC 2024 RUS
гораздо больше, чем просто

Спасибо за то, что всё разложили по полчкам.
Чем дальше тем больше понимаю, что я знаю так мало.
БОЛЬШОЕ СПАСИБО!
 
Ответить
Сообщение
гораздо больше, чем просто

Спасибо за то, что всё разложили по полчкам.
Чем дальше тем больше понимаю, что я знаю так мало.
БОЛЬШОЕ СПАСИБО!

Автор - DrMini
Дата добавления - 30.03.2022 в 15:47
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Подсчёт положительных значений диапазона с разных листов (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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