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

Вход

Регистрация

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

 

= Мир MS Excel/Сумма уникальных значений из видимого диапазона - Мир MS Excel

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

Всем доброго времени суток!
Подскажите пожалуйста, можно ли какой-нибудь формулой суммировать уникальные значения в видимом (фильтруемом) диапазоне?
Спасибо!
 
Ответить
СообщениеВсем доброго времени суток!
Подскажите пожалуйста, можно ли какой-нибудь формулой суммировать уникальные значения в видимом (фильтруемом) диапазоне?
Спасибо!
Автор - Chicony
Дата добавления - 04.02.2022 в 00:34
DrMini Дата: Пятница, 04.02.2022, 05:05 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 1946
Репутация: 292 ±
Замечаний: 0% ±

Excel LTSC 2024 RUS
Здравствуйте Chicony
Цитата Chicony, 04.02.2022 в 00:34, в сообщении № 1 ()
суммировать уникальные значения в видимом (фильтруемом) диапазоне

Так:
Код
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;A2:A10)
К сообщению приложен файл: 0651969.xlsx (9.3 Kb)
 
Ответить
СообщениеЗдравствуйте Chicony
Цитата Chicony, 04.02.2022 в 00:34, в сообщении № 1 ()
суммировать уникальные значения в видимом (фильтруемом) диапазоне

Так:
Код
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;A2:A10)
Автор - DrMini
Дата добавления - 04.02.2022 в 05:05
bmv98rus Дата: Пятница, 04.02.2022, 07:51 | Сообщение № 3
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4142
Репутация: 772 ±
Замечаний: 0% ±

Excel 2013/2016
Chicony, можно, и DrMini, показал как суммировать видимые, но Chicony, не показал примера с уникальными.

Замечательный Временно просто медведь , процентов на 20.
 
Ответить
СообщениеChicony, можно, и DrMini, показал как суммировать видимые, но Chicony, не показал примера с уникальными.
Автор - bmv98rus
Дата добавления - 04.02.2022 в 07:51
DrMini Дата: Пятница, 04.02.2022, 08:17 | Сообщение № 4
Группа: Друзья
Ранг: Старожил
Сообщений: 1946
Репутация: 292 ±
Замечаний: 0% ±

Excel LTSC 2024 RUS
Цитата bmv98rus, 04.02.2022 в 07:51, в сообщении № 3 ()
не показал примера с уникальными

Я решил, что уникальными будут считаться те значения, которые попали под критерий отбора фильтра. И этого будет достаточно.
Если под уникальным подразумевается, что-то другое - тогда файл и дополнительные комментарии не помешали бы.
 
Ответить
Сообщение
Цитата bmv98rus, 04.02.2022 в 07:51, в сообщении № 3 ()
не показал примера с уникальными

Я решил, что уникальными будут считаться те значения, которые попали под критерий отбора фильтра. И этого будет достаточно.
Если под уникальным подразумевается, что-то другое - тогда файл и дополнительные комментарии не помешали бы.
Автор - DrMini
Дата добавления - 04.02.2022 в 08:17
Chicony Дата: Пятница, 04.02.2022, 08:47 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

Я постарался сформулировать свою мысль в файле примера:
К сообщению приложен файл: 1743435.xlsx (10.4 Kb)
 
Ответить
СообщениеЯ постарался сформулировать свою мысль в файле примера:
Автор - Chicony
Дата добавления - 04.02.2022 в 08:47
Nic70y Дата: Пятница, 04.02.2022, 09:07 | Сообщение № 6
Группа: Друзья
Ранг: Экселист
Сообщений: 9065
Репутация: 2383 ±
Замечаний: 0% ±

Excel 2010
Код
=СУММПРОИЗВ((ПОИСКПОЗ(C4:C10;C4:C10;)=СТРОКА(C4:C10)-3)*ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;СМЕЩ(C4;СТРОКА(C4:C10)-4;)))

ЮMoney 41001841029809
 
Ответить
Сообщение
Код
=СУММПРОИЗВ((ПОИСКПОЗ(C4:C10;C4:C10;)=СТРОКА(C4:C10)-3)*ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;СМЕЩ(C4;СТРОКА(C4:C10)-4;)))
Автор - Nic70y
Дата добавления - 04.02.2022 в 09:07
Chicony Дата: Пятница, 04.02.2022, 13:24 | Сообщение № 7
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

Цитата Nic70y, 04.02.2022 в 09:07, в сообщении № 6 ()
=СУММПРОИЗВ((ПОИСКПОЗ(C4:C10;C4:C10;)=СТРОКА(C4:C10)-3)*ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;СМЕЩ(C4;СТРОКА(C4:C10)-4;)))

решение классное и подходит, то что надо. но только почему то повторяя формулу в другом файле, она выдает "0". То ли я туплю то ли не понимаю, подскажите в чем может быть проблема?
 
Ответить
Сообщение
Цитата Nic70y, 04.02.2022 в 09:07, в сообщении № 6 ()
=СУММПРОИЗВ((ПОИСКПОЗ(C4:C10;C4:C10;)=СТРОКА(C4:C10)-3)*ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;СМЕЩ(C4;СТРОКА(C4:C10)-4;)))

решение классное и подходит, то что надо. но только почему то повторяя формулу в другом файле, она выдает "0". То ли я туплю то ли не понимаю, подскажите в чем может быть проблема?
Автор - Chicony
Дата добавления - 04.02.2022 в 13:24
Nic70y Дата: Пятница, 04.02.2022, 13:27 | Сообщение № 8
Группа: Друзья
Ранг: Экселист
Сообщений: 9065
Репутация: 2383 ±
Замечаний: 0% ±

Excel 2010
Цитата Chicony, 04.02.2022 в 13:24, в сообщении № 7 ()
в чем может быть проблема?

1- не изменен диапазон под другой файл
2- в другом файле, в диапазоне не числа
3- да мало ли что

ЮMoney 41001841029809
 
Ответить
Сообщение
Цитата Chicony, 04.02.2022 в 13:24, в сообщении № 7 ()
в чем может быть проблема?

1- не изменен диапазон под другой файл
2- в другом файле, в диапазоне не числа
3- да мало ли что
Автор - Nic70y
Дата добавления - 04.02.2022 в 13:27
Chicony Дата: Пятница, 04.02.2022, 13:50 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

Цитата Nic70y, 04.02.2022 в 13:27, в сообщении № 8 ()
1- не изменен диапазон под другой файл
2- в другом файле, в диапазоне не числа
3- да мало ли что

Диапазон состоит из координат ячеек столбца и смещения относительно первой строки екселя (-4), верно?
 
Ответить
Сообщение
Цитата Nic70y, 04.02.2022 в 13:27, в сообщении № 8 ()
1- не изменен диапазон под другой файл
2- в другом файле, в диапазоне не числа
3- да мало ли что

Диапазон состоит из координат ячеек столбца и смещения относительно первой строки екселя (-4), верно?
Автор - Chicony
Дата добавления - 04.02.2022 в 13:50
Nic70y Дата: Пятница, 04.02.2022, 13:55 | Сообщение № 10
Группа: Друзья
Ранг: Экселист
Сообщений: 9065
Репутация: 2383 ±
Замечаний: 0% ±

Excel 2010
Цитата Nic70y, 04.02.2022 в 09:07, в сообщении № 6 ()
(ПОИСКПОЗ(C4:C10;C4:C10;)=СТРОКА(C4:C10)-3)
здесь -3 т.к. диапазон начинается с 4 строки, а нам надо сравнивать начиная с 1-ой строки
Цитата Nic70y, 04.02.2022 в 09:07, в сообщении № 6 ()
СМЕЩ(C4;СТРОКА(C4:C10)-4
здесь -4, т.к. нам нужно смещать начиная с нуля

ЮMoney 41001841029809
 
Ответить
Сообщение
Цитата Nic70y, 04.02.2022 в 09:07, в сообщении № 6 ()
(ПОИСКПОЗ(C4:C10;C4:C10;)=СТРОКА(C4:C10)-3)
здесь -3 т.к. диапазон начинается с 4 строки, а нам надо сравнивать начиная с 1-ой строки
Цитата Nic70y, 04.02.2022 в 09:07, в сообщении № 6 ()
СМЕЩ(C4;СТРОКА(C4:C10)-4
здесь -4, т.к. нам нужно смещать начиная с нуля
Автор - Nic70y
Дата добавления - 04.02.2022 в 13:55
Chicony Дата: Пятница, 04.02.2022, 14:03 | Сообщение № 11
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

Цитата Nic70y, 04.02.2022 в 13:55, в сообщении № 10 ()
здесь -4, т.к. нам нужно смещать начиная с нуля

сместил таблицу, изменил диапазоны и смещения, но все равно не получается... файлик приложил.
К сообщению приложен файл: 1743435-3-.xlsx (9.9 Kb)
 
Ответить
Сообщение
Цитата Nic70y, 04.02.2022 в 13:55, в сообщении № 10 ()
здесь -4, т.к. нам нужно смещать начиная с нуля

сместил таблицу, изменил диапазоны и смещения, но все равно не получается... файлик приложил.
Автор - Chicony
Дата добавления - 04.02.2022 в 14:03
Nic70y Дата: Пятница, 04.02.2022, 14:18 | Сообщение № 12
Группа: Друзья
Ранг: Экселист
Сообщений: 9065
Репутация: 2383 ±
Замечаний: 0% ±

Excel 2010
теперь понятно, не те уникальные подтягиваются, не та формула, так:
Код
=СУММ((ПОИСКПОЗ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;СМЕЩ(C12;СТРОКА(C12:C18)-12;));ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;СМЕЩ(C12;СТРОКА(C12:C18)-12;));)=СТРОКА(C12:C18)-11)*ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;СМЕЩ(C12;СТРОКА(C12:C18)-12;));)
формула массива
К сообщению приложен файл: 400.xlsx (9.4 Kb)

ЮMoney 41001841029809
 
Ответить
Сообщениетеперь понятно, не те уникальные подтягиваются, не та формула, так:
Код
=СУММ((ПОИСКПОЗ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;СМЕЩ(C12;СТРОКА(C12:C18)-12;));ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;СМЕЩ(C12;СТРОКА(C12:C18)-12;));)=СТРОКА(C12:C18)-11)*ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;СМЕЩ(C12;СТРОКА(C12:C18)-12;));)
формула массива
Автор - Nic70y
Дата добавления - 04.02.2022 в 14:18
Chicony Дата: Пятница, 04.02.2022, 14:29 | Сообщение № 13
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

Спасибо огромное!!! Все заработало! Очень выручили!!!
 
Ответить
СообщениеСпасибо огромное!!! Все заработало! Очень выручили!!!
Автор - Chicony
Дата добавления - 04.02.2022 в 14:29
bmv98rus Дата: Пятница, 04.02.2022, 16:32 | Сообщение № 14
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4142
Репутация: 772 ±
Замечаний: 0% ±

Excel 2013/2016
Альтернативочка массивная
Код
=SUM(IFERROR(SUBTOTAL(9;OFFSET(C12;ROW(C12:C18)-12;))/MMULT(--(C12:C18=TRANSPOSE(C12:C18));SUBTOTAL(103;OFFSET(C12;ROW(C12:C18)-12;)));))
но краткость не значит скорость. Этот вариант проигрывает тому что выше. Почти незаметно, но есть.

Не удержался, потестил с разными диапазонами. MMULT жрет ресурсы много сильнее и с ростом диапазона скорость падает в квадратичной зависимости.
Чего не скажешь про другие варианты, при этом с ростом диапазона в лидеры выбивается вариант , тот что ниже постом. обгоняя первый из предложенных.
К сообщению приложен файл: example2593.xlsx (9.5 Kb)

Замечательный Временно просто медведь , процентов на 20.

Сообщение отредактировал bmv98rus - Суббота, 05.02.2022, 10:13
 
Ответить
СообщениеАльтернативочка массивная
Код
=SUM(IFERROR(SUBTOTAL(9;OFFSET(C12;ROW(C12:C18)-12;))/MMULT(--(C12:C18=TRANSPOSE(C12:C18));SUBTOTAL(103;OFFSET(C12;ROW(C12:C18)-12;)));))
но краткость не значит скорость. Этот вариант проигрывает тому что выше. Почти незаметно, но есть.

Не удержался, потестил с разными диапазонами. MMULT жрет ресурсы много сильнее и с ростом диапазона скорость падает в квадратичной зависимости.
Чего не скажешь про другие варианты, при этом с ростом диапазона в лидеры выбивается вариант , тот что ниже постом. обгоняя первый из предложенных.
Автор - bmv98rus
Дата добавления - 04.02.2022 в 16:32
Egyptian Дата: Пятница, 04.02.2022, 17:27 | Сообщение № 15
Группа: Проверенные
Ранг: Ветеран
Сообщений: 530
Репутация: 193 ±
Замечаний: 0% ±

Excel 2013/2016
Еще вариант:
Код
=SUM(IF(FREQUENCY(IF(SUBTOTAL(3;OFFSET(C12;ROW($C$12:$C$18)-ROW($C$12);));MATCH("~"&$C$12:$C$18;$C$12:$C$18&"";0));ROW($C$12:$C$18)-ROW($C$12)+1)>0;$C$12:$C$18))
К сообщению приложен файл: 6052320.xlsx (9.5 Kb)
 
Ответить
СообщениеЕще вариант:
Код
=SUM(IF(FREQUENCY(IF(SUBTOTAL(3;OFFSET(C12;ROW($C$12:$C$18)-ROW($C$12);));MATCH("~"&$C$12:$C$18;$C$12:$C$18&"";0));ROW($C$12:$C$18)-ROW($C$12)+1)>0;$C$12:$C$18))
Автор - Egyptian
Дата добавления - 04.02.2022 в 17:27
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Сумма уникальных значений из видимого диапазона (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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