Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Счет значений в поле с объединенными и отдельными ячейками по условиям в поле с отдельными ячейками
 
#1
22.08.2019 08:25:25
Здравствуйте! Помогите, пожалуйста. Нужно формулой сосчитать количество значений в поле "Значение" (диапазон "Значение") с объединенными и отдельными ячейками, если в поле "Отметка" (диапазон "Отметка") с отдельными ячейками заполнены все ячейки в пределах диапазона строк считаемой ячейки.
 
 
 
#2
22.08.2019 09:03:10
Павел Запивахин, объединные ячейки это ЗЛО)
Не бойтесь совершенства. Вам его не достичь.
 
 
 
#3
22.08.2019 09:35:52
Присоединяюсь. Могу предложить решение с дополнительным столбцом. Обратите внимание, что в дополнительном столбце в самой последней ячейке стоит 1. Иначе не правильно считает последнюю группу. Не нашёл как это обойти. Там 1 или 0 придётся ставить руками или в последней ячейке или под таблицей.
Изменено: Wiss - 22.08.2019 20:49:47
Я не волшебник, я только учусь.
 
 
 
#4
22.08.2019 10:00:28
Спасибо за ответы. Я сам категорически против объединенных ячеек и допускаю их присутствие только в непоследних строках заголовков таблицы, но в данном случае без объединенных ячеек будет некрасиво. С дополнительным столбцом, макросом или с UDF я сосчитать могу, а хочется сосчитать формулой без дополнительных столбцов.
 
 
 
#5
22.08.2019 10:35:47
Павел Запивахин, у меня нет вариантов без дополнительного столбца.
Есть какой-то хитрый способ, чтобы ячейки были объединены, но всё-равно имели значения. Могу его поискать, но:
а. Он делается макросом, а у Вас таблица будет расти.
б. Навскидку не могу прикинуть формулу, которая поможет, если столбец "значения" всё-таки будет полностью заполнен.
Я не волшебник, я только учусь.
 
 
 
#6
22.08.2019 11:00:36
Цитата
Wiss написал:
Есть какой-то хитрый способ, чтобы ячейки были объединены, но всё-равно имели значения.
В примере сделал, чтобы все ваши объединенные ячейки имели значения. Можете применить свое решение без доп.столбца
 
 
 
#7
22.08.2019 11:23:34
Цитата
StatuS написал:
Можете применить свое решение без доп.столбца
Нету у меня пока решения без доп столбца и искать его я не стал из-за того, что автор, не сможет без макросов постоянно приводить таблицу к такому. О чём я собственно и писал.
Я не волшебник, я только учусь.
 
 
 
#8
22.08.2019 12:11:22
Есть решение массивной (Ctrl+Shift+Enter) формулой, но в конце списка обязательно должно быть непустое значение:
Код
=СЧЁТЕСЛИ(B:B;E3)-СУММПРОИЗВ(Ч(МУМНОЖ(Ч(ЕСЛИОШИБКА(ТРАНСП(ЕСЛИ(ИНДЕКС(СЧЁТЕСЛИ(СМЕЩ(B$1;;;СТРОКА($1:$999));"<>");НАИМЕНЬШИЙ(ЕСЛИ(E3=B$1:B$999;СТРОКА($1:$999));Ч(ИНДЕКС(СТОЛБЕЦ(A:E);))))=СЧЁТЕСЛИ(СМЕЩ(B$1;;;СТРОКА($1:$999));"<>");C$1:C$999));"")=0);СТРОКА($1:$999))>0))

*Немного ошибся. Формулу исправил, файл перезалил.

**Ещё раз ошибся. Формулу исправил, файл перезалил.

***Формулу сократил:

Код
=СЧЁТЕСЛИ(B:B;E3)-СУММ(Ч(МУМНОЖ(Ч(ЕСЛИОШИБКА(ТРАНСП(ЕСЛИ(СЧЁТЕСЛИ(СМЕЩ(B$1;;;НАИБОЛЬШИЙ((E3=B$1:B$999)*СТРОКА($1:$999);СТОЛБЕЦ(A:E)));"<>")=СЧЁТЕСЛИ(СМЕЩ(B$1;;;СТРОКА($1:$999));"<>");C$1:C$999));"")=0);СТРОКА($1:$999))>0))
Изменено: Светлый - 22.08.2019 13:16:23
 
 
 
#9
22.08.2019 17:28:31
Сам не пойму как, но работает. Уже все мозги себе свернул.
Код
=9-СУММ(Ч(МУМНОЖ(Ч(ТРАНСП(ЕСЛИ(СЧЁТЕСЛИ(СМЕЩ(B$1;;;НАИБОЛЬШИЙ(ЕСЛИ(E3=B$1:B$999;СТРОКА($1:$999);1);СТОЛБЕЦ(A:I)));"<>")=СЧЁТЕСЛИ(СМЕЩ(B$1;;;СТРОКА($1:$999));"<>");C$1:C$999))=0);СТРОКА($1:$999))>0))

Разобрался:

Код
=СУММ(Ч(МУМНОЖ(Ч(ТРАНСП(ЕСЛИ(СЧЁТЕСЛИ(СМЕЩ(B$1;;;НАИБОЛЬШИЙ(ЕСЛИ(E3=B$1:B999;СТРОКА($1:999);1);СТОЛБЕЦ(A:I)));"*")=СЧЁТЕСЛИ(СМЕЩ(B$1;;;СТРОКА($1:999));"*");C$1:C999))=0);СТРОКА($1:999))=0))
Изменено: Светлый - 22.08.2019 18:03:27
 
 
 
#10
22.08.2019 17:55:22
StatuS, поделитесь, пожалуйста, секретом добавления значений во все ячейки объединенной ячейки.
 
 
 
#11
22.08.2019 18:11:54
Цитата
Павел Запивахин написал:
секретом добавления значений во все ячейки
Вычисляю порядковый номер непустого значения. Все последующие ПУСТЫЕ ячейки будут иметь тот же порядковый номер.
Код
=СЧЁТЕСЛИ(СМЕЩ(B$1;;;СТРОКА($1:999));"*")
 
 
 
#12
22.08.2019 18:16:19
Светлый, если Вам не трудно, поясните, пожалуйста, как это работает:
Код
=СУММ(Ч(МУМНОЖ(Ч(ТРАНСП(ЕСЛИ(СЧЁТЕСЛИ(СМЕЩ(B$1;;;НАИБОЛЬШИЙ(ЕСЛИ(E3=B$1:B999;СТРОКА($1:999);1);СТОЛБЕЦ(A:I)));"*")=СЧЁТЕСЛИ(СМЕЩ(B$1;;;СТРОКА($1:999));"*");C$1:C999))=0);СТРОКА($1:999))=0))
 
 
 
#13
22.08.2019 18:58:17
Цитата
Павел Запивахин написал:
как это работает
Сначала находим номера строк, в которых нужное нам значение. Остальным значениям присваиваем 1, чтобы при СМЕЩ() не выдавало ошибку. Функция НАИБОЛЬШИЙ() формирует массив этих номеров, дополненный единицами. Вычисляю порядковые номера непустых значений. Все последующие ПУСТЫЕ ячейки будут иметь тот же порядковый номер. Также вычисляю порядковые номера искомых значений. Сравниваю их между собой. Получается массив, в котором в каждом столбце идут подряд значения ИСТИНА, соответствующие искомому значению и последующим пустым ячейкам. Через ЕСЛИ() заменяю их значениями из второго столбца и сравниваю с нулём. Транспонирую и с помощью МУМНОЖ() нахожу, в каком столбце нет ни одного нуля. То есть, все значения данной группы непустые. Подсчитываю, сколько таких столбцов, это и есть ответ.
 
 
 
#14
22.08.2019 19:13:26
Спасибо, Светлый. Сложно, но попытаюсь разобраться.
 
 
 
#15
22.08.2019 21:50:13
накурилось такое не летучие не матричное и …
=SUM(--IF(Значение=E3;ISERROR(MATCH(ROW(Значение);IFERROR(LOOKUP(ROW(Отметка);IF(Значение=E3;ROW(Значение)))/((Отметка="")*(LOOKUP(ROW(Отметка);IF(Значение=E3;ROW(Значение)))=LOOKUP(ROW(Отметка);IF(Значение<>"";ROW(Значение))))););))))
что сокращается до
=SUM((Значение=E3)*ISERROR(MATCH(ROW(Значение);LOOKUP(ROW(Отметка);IF(Значение=E3;ROW(Значение)))/((Отметка="")*(LOOKUP(ROW(Отметка);IF(Значение=E3;ROW(Значение)))=LOOKUP(ROW(Отметка);IF(Значение<>"";ROW(Значение)))));)))
Изменено: БМВ - 22.08.2019 21:58:30
По вопросам из тем форума, личку не читаю.
 
 
 
#16
23.08.2019 08:03:42
Код
=СУММ((Значение=E3)*ЕОШИБКА(ПОИСКПОЗ(СТРОКА(Значение);ПРОСМОТР(СТРОКА(Отметка);ЕСЛИ(Значение=E3;СТРОКА(Значение)))/((Отметка="")*(ПРОСМОТР(СТРОКА(Отметка);ЕСЛИ(Значение=E3;СТРОКА(Значение)))=ПРОСМОТР(СТРОКА(Отметка);ЕСЛИ(Значение<>"";СТРОКА(Значение)))));)))
Спасибо, БМВ, формула просто замечательная - все расчеты в пределах именованных диапазонов.
Изменено: Павел Запивахин - 23.08.2019 13:46:34
 
 
 
#17
23.08.2019 08:27:10
Павел Запивахин, в любом столбце делаете ссылку на ваш столбец с объединенными ячейками, с формулой, которая выдаст ошибку, если ячейка пуста (я использую =Если(епусто(А1));#Н/Д;А1)
Выделяете получившийся диапазон, F5 ->выделить -> формулы (ошибки). Заменяете ошибки на предыдущие значения (для А2 формула =А1 -> Ctrl+Enter). Форматируете как исходный столбец.
При необходимости промежуточно, конечно, можно сохранить это все как значения, чтобы связи не сохранились. Но это по необходимости. После этого ячейки будут иметь вид объединенных, но при этом иметь значения
 
 
 
#18
23.08.2019 12:39:36
Подумал я, подумал... Подглядел кое у кого (не будем указывать пальцем на медведя) и выродил такое решение:
Код
=СЧЁТЕСЛИ(Значение;E3)-СЧЁТ(ПОИСКПОЗ(ЕСЛИ(Значение=E3;СТРОКА(Отметка));ПРОСМОТР(СТРОКА(Отметка);ЕСЛИ(Значение<>"";СТРОКА(Отметка)))*(Отметка="");))
 
 
 
#19
23.08.2019 13:15:19
Светлый, только IF лeчше вынуть за Match, незачем лишний раз считать все)
=COUNTIF(Значение;E3)-COUNT(IF(Значение=E3;MATCH(ROW(Значение);LOOKUP(ROW(Отметка);IF(Значение<>"";ROW(Значение)));)))
=COUNTIF(Значение;E3)-COUNT(IF(Значение=E3;MATCH(ROW(Значение);LOOKUP(ROW(Отметка);IF(Значение<>"";ROW(Значение)))*(Отметка="");)))
А так, согласен , ум хорошо, а прокуренный светлый - лучше :-)
Изменено: БМВ - 23.08.2019 14:25:32
По вопросам из тем форума, личку не читаю.
 
 
 
#20
23.08.2019 13:48:34
Цитата
БМВ написал:
IF лeчше вынуть за Match
Не работает.  :(
*У меня ЕСЛИ для правильного формирования образцов для поиска позиции.
**БМВ, забыл или зря выбросил *(Отметка="")
***Вот так работает:
Код
=СЧЁТЕСЛИ(Значение;E3)-СЧЁТ(ЕСЛИ(Значение=E3;ПОИСКПОЗ(СТРОКА(Отметка);ПРОСМОТР(СТРОКА(Отметка);ЕСЛИ(Значение>0;СТРОКА(Отметка)))*(Отметка=0);)))
Изменено: Светлый - 23.08.2019 14:20:50
 
 
 
#21
23.08.2019 13:58:13
StatuS, спасибо. Немного усовершенствовал Ваш метод. Например, выделить А1:А4, в строке формул набрать =ЕСЛИ(ИСТИНА;2), нажать ctrl+Enter, выделить другие четыре ячейки по вертикали, объединить их, скопировать формат полученной объединенной ячейки в А1:А4, СУММ(A1:A4)=8.
 
 
 
#22
23.08.2019 14:24:46
Цитата
Светлый написал:
Не работает.
Работате просто я недороперевел :-)

=COUNTIF(Значение;E3)-COUNT(IF(Значение=E3;MATCH(ROW(Значение);LOOKUP(ROW(Отметка);IF(Значение<>"";ROW(Значение)))*(Отметка="");)))
Изменено: БМВ - 23.08.2019 14:25:03
По вопросам из тем форума, личку не читаю.
 
 
 
#23
23.08.2019 16:41:56
Любую формулу или программу можно сократить хотя бы на один символ. Интересно, до какого количества символов удастся сократить эту формулу? Прямо "МШ" какой-то!
Код
=СЧЁТЕСЛИ(Значение;E3)-СЧЁТ(ПОИСКПОЗ(СТРОКА(Отметка);ПРОСМОТР(СТРОКА(Отметка);ЕСЛИ(Значение>0;СТРОКА(Отметка)))*(Отметка=0);)/(Значение=E3))
 
 
 
#24
23.08.2019 16:54:50
Светлый, Ну, дело не в длине, мой подход принципиально отличался от первого предложенного. Я и правда зациклился на подсчете нужных забыв про то, что это все за исключением лишних :-), ну а оптимизация по скорости- это мелочи. Ведь главное не длинна , а результат :-)
По вопросам из тем форума, личку не читаю.
 
 
 
#25
23.08.2019 18:01:26
Цитата
Светлый написал:
удастся сократить эту формулу?
Иногда ошибки очень помогают, надо только понять, как их использовать. И количество вычислений уменьшилось.
Код
=СУММ(ЕНД(ПОИСКПОЗ(СТРОКА(Отметка);ПРОСМОТР(СТРОКА(Отметка);ЕСЛИ(Значение>0;СТРОКА(Отметка)))*(Отметка=0);))*(Значение=E3))
 
 
 
Страницы: 1
Читают тему
Наверх