СУММПРОИЗВ() и неточное совпадение строки

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

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

СУММПРОИЗВ() и неточное совпадение строки (Замена СУММПРОИЗВ() на формулу с подсчётом вариантов)

СУММПРОИЗВ() и неточное совпадение строки

Hugo

Дата: Среда, 03.08.2011, 13:48 | Сообщение № 1

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

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

Сообщений: 3690

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

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

365

Замена СУММПРОИЗВ() на формулу с подсчётом вариантов "строка"&"*"

Добрый день!
Прошу помощи у знатоков формул - чтот-то у самого не получается решить задачку...
Необходимо подсчитать по базе, сколько каких терминалов по фирмам.
1. сколько всего не GSM
2. сколько всего GSM
3. сколько всего GSM c признаком LL в колонке C.
База в пределах тысячи строк.
На втором листе есть попытка посчитать LL, но без учёта GSM, т.к. с этим проблема - СУММПРОИЗВ не хочет считать "GSM*" - там

есть два варианта этого признака, но может быть и больше.

Формула массива
=SUM(([BAZA.xls]Sheet1!$A$2:$A$36=A7)*([BAZA.xls]Sheet1!$B$2:$B$36="GSM*")*([BAZA.xls]Sheet1!$C$2:$C$36="LL"))
тоже не хочет работать с "*"...

Хочется сделать дело без допстолбцов.
С допстолбцом легко - выбрать туда GSM, потом c помошью СУММПРОИЗВ или даже массивной СУММ или СЧЕТЕСЛИ посчитать, сколько по фирме терминалов всего, сколько GSM - в С, разница - в B, сколько GSM+LL - в D.

Можно конечно написать макрос или UDF - но хочется обойтись стандартными формулами, вроде решение где-то рядом...

К сообщению приложен файл: New_Folder.rar (6.1 Kb)

webmoney: E265281470651 Z422237915069
USDT TRC20: TN8XeEF17o5KPBD9pNwYzNyruycuAc2mVD

Ответить

MCH

Дата: Среда, 03.08.2011, 14:06 | Сообщение № 2

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

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

Сообщений: 2004

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

Замечаний: ±

Вместо "GSM*" можно использовать
ЕОШ(ПОИСК("GSM";диапазон))
ЕЧИСЛО(ПОИСК("GSM";диапазон))
или
ЛЕВСИМВ(диапазон;3)="GSM"

=СУММПРОИЗВ(($A2=[BAZA.xls]Sheet1!$A$2:$A$36)*ЕОШ(ПОИСК("GSM";[BAZA.xls]Sheet1!$B$2:$B$36)))
=СУММПРОИЗВ(($A2=[BAZA.xls]Sheet1!$A$2:$A$36)*ЕЧИСЛО(ПОИСК("GSM";[BAZA.xls]Sheet1!$B$2:$B$36)))
=СУММПРОИЗВ(($A2=[BAZA.xls]Sheet1!$A$2:$A$36)*ЕЧИСЛО(ПОИСК("GSM";[BAZA.xls]Sheet1!$B$2:$B$36))*([BAZA.xls]Sheet1!$C$2:$C$36="LL"))

Сообщение отредактировал MCH - Среда, 03.08.2011, 14:11

Ответить

Hugo

Дата: Среда, 03.08.2011, 14:17 | Сообщение № 3

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

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

Сообщений: 3690

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

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

365

Спасибо, попробуем.
Хотя пока решили просто через Ctrl+H поменять в столбце все "GSM*" на "GSM", таким образом не городя допстолбцы smile

и сделать на формулах массива
=SUM(([BAZA.xls]Sheet1!$A$2:$A$36=A2)*([BAZA.xls]Sheet1!$B$2:$B$36="GSM"))
и даже разницу считает:
=(SUM(--([BAZA.xls]Sheet1!$A$2:$A$36=A2)))-(SUM(([BAZA.xls]Sheet1!$A$2:$A$36=A2)*([BAZA.xls]Sheet1!$B$2:$B$36="GSM")))
Но ЕОШ(ПОИСК(" попробую.

P.S. что-то не получается перевести:)
Можно в файле попросить - только свод?

PP.S.

Получилось!
Всего:
=SUMPRODUCT(--($A2=[BAZA.xls]Sheet1!$A$2:$A$36))
С GSM:
=SUMPRODUCT(($A2=[BAZA.xls]Sheet1!$A$2:$A$36)*ISNUMBER(SEARCH("GSM";[BAZA.xls]Sheet1!$B$2:$B$36)))
С GSM+LL:
=SUMPRODUCT(($A2=[BAZA.xls]Sheet1!$A$2:$A$36)*ISNUMBER(SEARCH("GSM";[BAZA.xls]Sheet1!$B$2:$B$36))*([BAZA.xls]Sheet1!$C$2:$C$36="LL"))
Терминалов не-GSM:
=SUMPRODUCT(--($A2=[BAZA.xls]Sheet1!$A$2:$A$36))-SUMPRODUCT(($A2=[BAZA.xls]Sheet1!$A$2:$A$36)*ISNUMBER(SEARCH("GSM";[BAZA.xls]Sheet1!$B$2:$B$36)))

webmoney: E265281470651 Z422237915069
USDT TRC20: TN8XeEF17o5KPBD9pNwYzNyruycuAc2mVD

Ответить

СУММПРОИЗВ() и неточное совпадение строки (Замена СУММПРОИЗВ() на формулу с подсчётом вариантов)

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