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

Вход

Регистрация

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

 

= Мир MS Excel/Подсчет одинаковых деталей - Мир MS Excel

Старая форма входа
  • Страница 1 из 2
  • 1
  • 2
  • »
Модератор форума: _Boroda_, китин  
Мир MS Excel » Вопросы и решения » Excel и другие приложения » Google Docs » Подсчет одинаковых деталей (Формулы/Formulas)
Подсчет одинаковых деталей
Faraway Дата: Понедельник, 15.11.2021, 21:51 | Сообщение № 1
Группа: Пользователи
Ранг: Участник
Сообщений: 72
Репутация: 0 ±
Замечаний: 20% ±

Excel 2016
Здравствуйте. Не могу дойти до решения такого вопроса:
Есть список деталей, а сбоку необходимое количество таких деталей. Некоторые детали повторяются.
Задача в том, чтоб написать формулу, которая выведет список уникальных деталей + возле каждой детали укажет необходимую сумму таких деталей.
Список уникальных деталей элементарно получается вывести, а вот как посчитать их количество - никак не допру.
Подскажите пожалуйста, как можно получить такой результат?
Табличка
 
Ответить
СообщениеЗдравствуйте. Не могу дойти до решения такого вопроса:
Есть список деталей, а сбоку необходимое количество таких деталей. Некоторые детали повторяются.
Задача в том, чтоб написать формулу, которая выведет список уникальных деталей + возле каждой детали укажет необходимую сумму таких деталей.
Список уникальных деталей элементарно получается вывести, а вот как посчитать их количество - никак не допру.
Подскажите пожалуйста, как можно получить такой результат?
Табличка

Автор - Faraway
Дата добавления - 15.11.2021 в 21:51
прохожий2019 Дата: Понедельник, 15.11.2021, 22:23 | Сообщение № 2
Группа: Проверенные
Ранг: Ветеран
Сообщений: 955
Репутация: 262 ±
Замечаний: 0% ±

365 Beta Channel
можно сводной, можно QUERY [vba]
Код
=QUERY(A2:C26;"select A,B, sum(C) GROUP BY A,B";1)
[/vba]
[p.s.]формулу смотрите в файле, а то на официальном сайте майкрософт нашлась функция ГРУППА() - из макрофункций вестимо; SPLIT, кстати, оттуда же[/p.s.]
[admin]Думаю, в таких случаях в этом разделе можно использовать тег кода[/admin]


Сообщение отредактировал Pelena - Вторник, 16.11.2021, 08:08
 
Ответить
Сообщениеможно сводной, можно QUERY [vba]
Код
=QUERY(A2:C26;"select A,B, sum(C) GROUP BY A,B";1)
[/vba]
[p.s.]формулу смотрите в файле, а то на официальном сайте майкрософт нашлась функция ГРУППА() - из макрофункций вестимо; SPLIT, кстати, оттуда же[/p.s.]
[admin]Думаю, в таких случаях в этом разделе можно использовать тег кода[/admin]

Автор - прохожий2019
Дата добавления - 15.11.2021 в 22:23
Kashimirush Дата: Вторник, 16.11.2021, 08:34 | Сообщение № 3
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 240
Репутация: 40 ±
Замечаний: 0% ±

Excel 2010
Цитата прохожий2019, 15.11.2021 в 22:23, в сообщении № 2 ()
майкрософт
Малыш Билли мало имеет отношения к гугл таблицам.


Работа, работа, перейди на Федота...
 
Ответить
Сообщение
Цитата прохожий2019, 15.11.2021 в 22:23, в сообщении № 2 ()
майкрософт
Малыш Билли мало имеет отношения к гугл таблицам.

Автор - Kashimirush
Дата добавления - 16.11.2021 в 08:34
прохожий2019 Дата: Вторник, 16.11.2021, 08:51 | Сообщение № 4
Группа: Проверенные
Ранг: Ветеран
Сообщений: 955
Репутация: 262 ±
Замечаний: 0% ±

365 Beta Channel
Малыш Билли
спасибо,кэп. Речь вообще-то о работе формульного тэга, администрация немножко в курсе
 
Ответить
Сообщение
Малыш Билли
спасибо,кэп. Речь вообще-то о работе формульного тэга, администрация немножко в курсе

Автор - прохожий2019
Дата добавления - 16.11.2021 в 08:51
прохожий2019 Дата: Вторник, 16.11.2021, 09:31 | Сообщение № 5
Группа: Проверенные
Ранг: Ветеран
Сообщений: 955
Репутация: 262 ±
Замечаний: 0% ±

365 Beta Channel
Цитата прохожий2019, 15.11.2021 в 22:23, в сообщении № 2 ()
можно использовать тег кода
понял, принято
 
Ответить
Сообщение
Цитата прохожий2019, 15.11.2021 в 22:23, в сообщении № 2 ()
можно использовать тег кода
понял, принято

Автор - прохожий2019
Дата добавления - 16.11.2021 в 09:31
Faraway Дата: Среда, 17.11.2021, 12:38 | Сообщение № 6
Группа: Пользователи
Ранг: Участник
Сообщений: 72
Репутация: 0 ±
Замечаний: 20% ±

Excel 2016
Спасибо за решение.
А без QUERY и сводной можно такое сделать, используя функции типа сумма если..?
QUERY не редко у меня ведет себя "странно".


Сообщение отредактировал Faraway - Среда, 17.11.2021, 12:48
 
Ответить
СообщениеСпасибо за решение.
А без QUERY и сводной можно такое сделать, используя функции типа сумма если..?
QUERY не редко у меня ведет себя "странно".

Автор - Faraway
Дата добавления - 17.11.2021 в 12:38
Kashimirush Дата: Среда, 17.11.2021, 12:45 | Сообщение № 7
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 240
Репутация: 40 ±
Замечаний: 0% ±

Excel 2010
А без QUERY


Можно, тогда 2 формулы нужно будет использовать, сначала вывести уникальные:

Код
=UNIQUE($A$3:$B$26)


Затем подсчитать сумму по уникальным:

Код
=ArrayFormula(sumif($A$3:$A$26&$B$3:$B$26;I15:I20&J15:J20;$C$3:$C$26))


Смотрите ячейку I15 и K15 в вашем примере.


Работа, работа, перейди на Федота...
 
Ответить
Сообщение
А без QUERY


Можно, тогда 2 формулы нужно будет использовать, сначала вывести уникальные:

Код
=UNIQUE($A$3:$B$26)


Затем подсчитать сумму по уникальным:

Код
=ArrayFormula(sumif($A$3:$A$26&$B$3:$B$26;I15:I20&J15:J20;$C$3:$C$26))


Смотрите ячейку I15 и K15 в вашем примере.

Автор - Kashimirush
Дата добавления - 17.11.2021 в 12:45
Kashimirush Дата: Среда, 17.11.2021, 12:47 | Сообщение № 8
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 240
Репутация: 40 ±
Замечаний: 0% ±

Excel 2010
Знак амперсанда & - очень полезный инструмент, когда нужно считать сумму или повторения по нескольким критериям.


Работа, работа, перейди на Федота...

Сообщение отредактировал Kashimirush - Среда, 17.11.2021, 12:47
 
Ответить
СообщениеЗнак амперсанда & - очень полезный инструмент, когда нужно считать сумму или повторения по нескольким критериям.

Автор - Kashimirush
Дата добавления - 17.11.2021 в 12:47
Faraway Дата: Среда, 17.11.2021, 12:57 | Сообщение № 9
Группа: Пользователи
Ранг: Участник
Сообщений: 72
Репутация: 0 ±
Замечаний: 20% ±

Excel 2016
Можно, тогда 2 формулы нужно будет использовать

Да, этот вариант на много проще и понятнее. Вот я например вставил QUERY и у меня пустая строка вылезла. Подскажете как пустую строку убрать?
К сообщению приложен файл: 7664445.jpg(48.4 Kb)


Сообщение отредактировал Faraway - Среда, 17.11.2021, 13:03
 
Ответить
Сообщение
Можно, тогда 2 формулы нужно будет использовать

Да, этот вариант на много проще и понятнее. Вот я например вставил QUERY и у меня пустая строка вылезла. Подскажете как пустую строку убрать?

Автор - Faraway
Дата добавления - 17.11.2021 в 12:57
Kashimirush Дата: Среда, 17.11.2021, 13:07 | Сообщение № 10
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 240
Репутация: 40 ±
Замечаний: 0% ±

Excel 2010
Faraway, Попробуй [vba]
Код
"select M,N, sum(O) where M is not null ..."
[/vba]


Работа, работа, перейди на Федота...

Сообщение отредактировал Kashimirush - Среда, 17.11.2021, 13:08
 
Ответить
СообщениеFaraway, Попробуй [vba]
Код
"select M,N, sum(O) where M is not null ..."
[/vba]

Автор - Kashimirush
Дата добавления - 17.11.2021 в 13:07
Faraway Дата: Среда, 17.11.2021, 14:14 | Сообщение № 11
Группа: Пользователи
Ранг: Участник
Сообщений: 72
Репутация: 0 ±
Замечаний: 20% ±

Excel 2016
Kashimirush, а, понял, оно подтянуло еще одну пустую строку как уникальную )
Но я не понимаю, почему оно берет только уникальные значения, а не список как он есть? Нет же в условии, что надо только уникальные значения? Или это функция sum() делает такую выдачу?
 
Ответить
СообщениеKashimirush, а, понял, оно подтянуло еще одну пустую строку как уникальную )
Но я не понимаю, почему оно берет только уникальные значения, а не список как он есть? Нет же в условии, что надо только уникальные значения? Или это функция sum() делает такую выдачу?

Автор - Faraway
Дата добавления - 17.11.2021 в 14:14
Kashimirush Дата: Среда, 17.11.2021, 14:22 | Сообщение № 12
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 240
Репутация: 40 ±
Замечаний: 0% ±

Excel 2010
Faraway, Интернет , конечно, помойка та еще. Но про QUERY материала полно. Изучите, для начала, основы этой функции, если вам сейчас объяснить только этот вопрос, общей картины у вас всё равно не будет...
СТАТЬЯ


Работа, работа, перейди на Федота...

Сообщение отредактировал Kashimirush - Среда, 17.11.2021, 14:24
 
Ответить
СообщениеFaraway, Интернет , конечно, помойка та еще. Но про QUERY материала полно. Изучите, для начала, основы этой функции, если вам сейчас объяснить только этот вопрос, общей картины у вас всё равно не будет...
СТАТЬЯ

Автор - Kashimirush
Дата добавления - 17.11.2021 в 14:22
прохожий2019 Дата: Среда, 17.11.2021, 22:25 | Сообщение № 13
Группа: Проверенные
Ранг: Ветеран
Сообщений: 955
Репутация: 262 ±
Замечаний: 0% ±

365 Beta Channel
Нет же в условии, что надо только уникальные
есть
Цитата прохожий2019, 15.11.2021 в 22:23, в сообщении № 2 ()
GROUP BY A,B
ну и ссылку для ознакомления уже дали
 
Ответить
Сообщение
Нет же в условии, что надо только уникальные
есть
Цитата прохожий2019, 15.11.2021 в 22:23, в сообщении № 2 ()
GROUP BY A,B
ну и ссылку для ознакомления уже дали

Автор - прохожий2019
Дата добавления - 17.11.2021 в 22:25
Gustav Дата: Четверг, 18.11.2021, 00:47 | Сообщение № 14
Группа: Друзья
Ранг: Старожил
Сообщений: 2211
Репутация: 893 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Можно, тогда 2 формулы нужно будет использовать, сначала вывести уникальные:
Затем подсчитать сумму по уникальным:
Смотрите ячейку I15 и K15 в вашем примере.

Подход, в целом, неплохой - как простая и понятная альтернатива QUERY и сводной таблице. Особенно учитывая, что вроде бы более напрашивающаяся здесь множественная по критериям функция SUMIFS не работает в формулах массива, а однокритериальная (с одним условием) SUMIF - работает. Правда, в случае многих условий приходится, как и продемонстрировано выше, эти условия оформлять с помощью конкатенации.

Имеет смысл сразу заточиться на саморасширяемость второй формулы, чтобы не править адреса второго аргумента SUMIF всякий раз при изменении количества уникальных элементов. Ну, а первая формула и так саморасширяемая - по самой сути функции UNIQUE. Только где уникальность, там обычно и сортировка - при помощи SORT (ниже добавим, обернём).

Имеет смысл также не планировать нахождения данных другой природы ниже обрабатываемых диапазонов данных - чтобы можно было применить открытые диапазоны типа A3:B (т.е. сэкономить на втором индексе) и, соответственно, добавлять новые строки в конец таблицы, а не вставлять их в середину диапазона. Ну, и абсолютные ссылки здесь (в одной-единственной, непротягиваемой формуле) не являются необходимыми (т.е. можно сэкономить и на признаках абсолютности - знаках доллара)

Итого имеем:
1-я формула, введенная в ячейку I15:
[vba]
Код
=SORT(UNIQUE(A3:B))
[/vba]
2-я формула, введенная в ячейку K15 (через ячейку от I15, поскольку таблица уникальных значений состоит из двух столбцов):
[vba]
Код
=ArrayFormula( SUMIF( A3:A & B3:B;
I15:INDEX(I15:I;ROWS(UNIQUE(A3:B)))
&
J15:INDEX(J15:J;ROWS(UNIQUE(A3:B)))
;C3:C))
[/vba]
Думаю, к месту будет полезно разобрать подробнее радикал:
[vba]
Код
I15:INDEX(I15:I;ROWS(UNIQUE(A3:B)))
[/vba]
При текущей ситуации в таблице имеем 6 строк уникальных пар значений, поэтому выражение:
[vba]
Код
ROWS(UNIQUE(A3:B)) = 6
[/vba]
Упрощаем (в учебных целях) радикал, вставляя в него константу:
[vba]
Код
I15:INDEX(I15:I;6)
[/vba]
Шестая ячейка в диапазоне I15:I это ячейка I20 (поскольку I15 - первая) и, таким образом, ссылочная форма функции INDEX, используемая здесь, возвращает нам адрес ячейки I20:
[vba]
Код
INDEX(I15:I; 6) = I20
[/vba]
, окончательно упрощая наш радикал до того вида, в котором он и продемонстрирован выше в примере от Kashimirush:
[vba]
Код
I15:I20
[/vba]
Форма функции INDEX самоопределяется автоматически и в зависимости от контекста использования внутри формулы возвращает либо адрес ячейки, либо значение ячейки по этому адресу. В начале освоения Excel я, думаю, как и многие, "признавал" от функции INDEX только значение ячейки, не очень понимая и недоумевая по поводу ее ссылочной формы - типа "зачем она вообще нужна". И только придя на наш Форум в мае 2012, т.е. относительно недавно, я, наконец, "вкурил" ссылочную форму INDEX, чем теперь очень горжусь и применяю ее направо и налево - с благодарностью Форуму! yes


МОИ: Ник, Tip box: 41001663842605
 
Ответить
Сообщение
Можно, тогда 2 формулы нужно будет использовать, сначала вывести уникальные:
Затем подсчитать сумму по уникальным:
Смотрите ячейку I15 и K15 в вашем примере.

Подход, в целом, неплохой - как простая и понятная альтернатива QUERY и сводной таблице. Особенно учитывая, что вроде бы более напрашивающаяся здесь множественная по критериям функция SUMIFS не работает в формулах массива, а однокритериальная (с одним условием) SUMIF - работает. Правда, в случае многих условий приходится, как и продемонстрировано выше, эти условия оформлять с помощью конкатенации.

Имеет смысл сразу заточиться на саморасширяемость второй формулы, чтобы не править адреса второго аргумента SUMIF всякий раз при изменении количества уникальных элементов. Ну, а первая формула и так саморасширяемая - по самой сути функции UNIQUE. Только где уникальность, там обычно и сортировка - при помощи SORT (ниже добавим, обернём).

Имеет смысл также не планировать нахождения данных другой природы ниже обрабатываемых диапазонов данных - чтобы можно было применить открытые диапазоны типа A3:B (т.е. сэкономить на втором индексе) и, соответственно, добавлять новые строки в конец таблицы, а не вставлять их в середину диапазона. Ну, и абсолютные ссылки здесь (в одной-единственной, непротягиваемой формуле) не являются необходимыми (т.е. можно сэкономить и на признаках абсолютности - знаках доллара)

Итого имеем:
1-я формула, введенная в ячейку I15:
[vba]
Код
=SORT(UNIQUE(A3:B))
[/vba]
2-я формула, введенная в ячейку K15 (через ячейку от I15, поскольку таблица уникальных значений состоит из двух столбцов):
[vba]
Код
=ArrayFormula( SUMIF( A3:A & B3:B;
I15:INDEX(I15:I;ROWS(UNIQUE(A3:B)))
&
J15:INDEX(J15:J;ROWS(UNIQUE(A3:B)))
;C3:C))
[/vba]
Думаю, к месту будет полезно разобрать подробнее радикал:
[vba]
Код
I15:INDEX(I15:I;ROWS(UNIQUE(A3:B)))
[/vba]
При текущей ситуации в таблице имеем 6 строк уникальных пар значений, поэтому выражение:
[vba]
Код
ROWS(UNIQUE(A3:B)) = 6
[/vba]
Упрощаем (в учебных целях) радикал, вставляя в него константу:
[vba]
Код
I15:INDEX(I15:I;6)
[/vba]
Шестая ячейка в диапазоне I15:I это ячейка I20 (поскольку I15 - первая) и, таким образом, ссылочная форма функции INDEX, используемая здесь, возвращает нам адрес ячейки I20:
[vba]
Код
INDEX(I15:I; 6) = I20
[/vba]
, окончательно упрощая наш радикал до того вида, в котором он и продемонстрирован выше в примере от Kashimirush:
[vba]
Код
I15:I20
[/vba]
Форма функции INDEX самоопределяется автоматически и в зависимости от контекста использования внутри формулы возвращает либо адрес ячейки, либо значение ячейки по этому адресу. В начале освоения Excel я, думаю, как и многие, "признавал" от функции INDEX только значение ячейки, не очень понимая и недоумевая по поводу ее ссылочной формы - типа "зачем она вообще нужна". И только придя на наш Форум в мае 2012, т.е. относительно недавно, я, наконец, "вкурил" ссылочную форму INDEX, чем теперь очень горжусь и применяю ее направо и налево - с благодарностью Форуму! yes

Автор - Gustav
Дата добавления - 18.11.2021 в 00:47
Faraway Дата: Четверг, 18.11.2021, 12:41 | Сообщение № 15
Группа: Пользователи
Ранг: Участник
Сообщений: 72
Репутация: 0 ±
Замечаний: 20% ±

Excel 2016
Цитата прохожий2019, 17.11.2021 в 22:25, в сообщении № 13 ()
GROUP BY A,B

Не использовал этот ключ никогда и спутал с "order by", свою ошибку понял, а заодно понял работу GROUP BY. А статью знаю, она у меня в закладках. Это то, с чего я начал знакомство с QUERY.
 
Ответить
Сообщение
Цитата прохожий2019, 17.11.2021 в 22:25, в сообщении № 13 ()
GROUP BY A,B

Не использовал этот ключ никогда и спутал с "order by", свою ошибку понял, а заодно понял работу GROUP BY. А статью знаю, она у меня в закладках. Это то, с чего я начал знакомство с QUERY.

Автор - Faraway
Дата добавления - 18.11.2021 в 12:41
Gustav Дата: Четверг, 18.11.2021, 16:06 | Сообщение № 16
Группа: Друзья
Ранг: Старожил
Сообщений: 2211
Репутация: 893 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
понял работу GROUP BY. А статью знаю, она у меня в закладках

Интересно, что GROUP BY автоматически также и сортирует возвращаемый набор данных по колонкам группировки, т.е. по колонкам, перечисленным после GROUP BY. Сортировка выполняется по возрастанию. Поэтому ORDER BY в явном виде нужен после GROUP BY только в случае, если требуется иная сортировка, например, по колонкам группировки, но по убыванию (DESC), либо нужна сортировка по какому-нибудь агрегирующему полю, например, по возрастанию суммирующей колонки.

В статье по ссылке, кстати, этот момент не упомянут. Там в примере по GROUP BY написано:
[vba]
Код
SELECT
       B,
       avg(G),
       max(G),
       min(G),
       count(G),
       sum(G)
GROUP BY B

ORDER BY B
[/vba], из чего делаем вывод, что автору той статьи этот нюанс про неявную автоматическую сортировку при группировке пока не известен.


МОИ: Ник, Tip box: 41001663842605
 
Ответить
Сообщение
понял работу GROUP BY. А статью знаю, она у меня в закладках

Интересно, что GROUP BY автоматически также и сортирует возвращаемый набор данных по колонкам группировки, т.е. по колонкам, перечисленным после GROUP BY. Сортировка выполняется по возрастанию. Поэтому ORDER BY в явном виде нужен после GROUP BY только в случае, если требуется иная сортировка, например, по колонкам группировки, но по убыванию (DESC), либо нужна сортировка по какому-нибудь агрегирующему полю, например, по возрастанию суммирующей колонки.

В статье по ссылке, кстати, этот момент не упомянут. Там в примере по GROUP BY написано:
[vba]
Код
SELECT
       B,
       avg(G),
       max(G),
       min(G),
       count(G),
       sum(G)
GROUP BY B

ORDER BY B
[/vba], из чего делаем вывод, что автору той статьи этот нюанс про неявную автоматическую сортировку при группировке пока не известен.

Автор - Gustav
Дата добавления - 18.11.2021 в 16:06
Faraway Дата: Четверг, 18.11.2021, 17:18 | Сообщение № 17
Группа: Пользователи
Ранг: Участник
Сообщений: 72
Репутация: 0 ±
Замечаний: 20% ±

Excel 2016
Kashimirush, Gustav, вы первые приходите отвечать на вопросы, по крайней мере на мои. Я конечно же благодарен и всем остальным которые мне помогали и всегда стараюсь нажать плюсик.
У меня ко всем знатокам данного форума вопрос-предложение.
А почему бы на форуме не создать раздел базу знаний (энциклопедию) по каждой из функций таблицы.
Я понимаю, что в интернете много всего есть уже описано, взять ту же гугл справку... Но везде все скудно и не полностью.
Можно было бы первым постом размещать описание функции, что делает, как использовать с примерами.
Далее идет обсуждение по каждой из функций, если в обсуждении появилось что-то новое и интересное, что было упущено в изначальном описании, то оно добавляется в первый пост.


Сообщение отредактировал Faraway - Четверг, 18.11.2021, 17:20
 
Ответить
СообщениеKashimirush, Gustav, вы первые приходите отвечать на вопросы, по крайней мере на мои. Я конечно же благодарен и всем остальным которые мне помогали и всегда стараюсь нажать плюсик.
У меня ко всем знатокам данного форума вопрос-предложение.
А почему бы на форуме не создать раздел базу знаний (энциклопедию) по каждой из функций таблицы.
Я понимаю, что в интернете много всего есть уже описано, взять ту же гугл справку... Но везде все скудно и не полностью.
Можно было бы первым постом размещать описание функции, что делает, как использовать с примерами.
Далее идет обсуждение по каждой из функций, если в обсуждении появилось что-то новое и интересное, что было упущено в изначальном описании, то оно добавляется в первый пост.

Автор - Faraway
Дата добавления - 18.11.2021 в 17:18
прохожий2019 Дата: Четверг, 18.11.2021, 22:48 | Сообщение № 18
Группа: Проверенные
Ранг: Ветеран
Сообщений: 955
Репутация: 262 ±
Замечаний: 0% ±

365 Beta Channel
Но везде все скудно и не полностью
Group By
The group by clause is used to aggregate values across rows. A single row is created for each distinct combination of values in the group-by clause. The data is automatically sorted by the grouping columns, unless otherwise specified by an order by clause.

куда уж полнее - обращаю внимание - это просто официальная справка, благополучно доступная прям из гугловской таблицы


Сообщение отредактировал прохожий2019 - Четверг, 18.11.2021, 22:50
 
Ответить
Сообщение
Но везде все скудно и не полностью
Group By
The group by clause is used to aggregate values across rows. A single row is created for each distinct combination of values in the group-by clause. The data is automatically sorted by the grouping columns, unless otherwise specified by an order by clause.

куда уж полнее - обращаю внимание - это просто официальная справка, благополучно доступная прям из гугловской таблицы

Автор - прохожий2019
Дата добавления - 18.11.2021 в 22:48
Gustav Дата: Пятница, 19.11.2021, 00:05 | Сообщение № 19
Группа: Друзья
Ранг: Старожил
Сообщений: 2211
Репутация: 893 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
2-я формула, введенная в ячейку K15 :
=ArrayFormula( SUMIF( A3:A & B3:B;
I15:INDEX(I15:I;ROWS(UNIQUE(A3:B)))
&
J15:INDEX(J15:J;ROWS(UNIQUE(A3:B)))
;C3:C))

В моем посте выше второй аргумент функции SUMIF в саморасширяющейся формуле при наличии даже одной конкатенации, согласитесь, выглядит несколько тяжеловесно (функцию INDEX в ссылочной форме приходится ведь "приставлять" к каждому из двух соединяемых диапазонов):
[vba]
Код
I15:INDEX(I15:I;ROWS(UNIQUE(A3:B)))
&
J15:INDEX(J15:J;ROWS(UNIQUE(A3:B)))
[/vba]А если надо будет соединить три или четыре диапазона? А если еще больше? В общем, с каждой добавляемой операцией конкатенации второй аргумент будет превращаться в трудновоспринимаемое "месиво" из функций INDEX. В общем, задумался, как бы облегчить ситуацию...

А дальше, как говорил Ростислав Плятт в роли завхоза Бубенцова в фильме "Весна" про метод работы ученых: "Сел, задумался... Открыл!" И придумалась мне конструкция, в которой число уникальных элементов, вычисляемое фрагментом:
[vba]
Код
ROWS(UNIQUE(A3:B))
[/vba]рассчитывается только один раз. Вот она:
[vba]
Код
ARRAY_CONSTRAIN( I15:I & J15:J; ROWS(UNIQUE(A3:B)); 1)
[/vba]
И тогда вся 2-я формула в новой редакции приобретает гораздо более прозрачный вид:
[vba]
Код
=ArrayFormula( SUMIF( A3:A & B3:B;
ARRAY_CONSTRAIN( I15:I & J15:J;
ROWS(UNIQUE(A3:B)); 1)
;C3:C))
[/vba]И ведь что приятно - конкатенируемые диапазоны второго аргумента SUMIF можно теперь также записать в простой открытой форме, при необходимости легко дополняя составное условие другими диапазонами:
[vba]
Код
I15:I & J15:J & ... & ...
[/vba]


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

Сообщение отредактировал Gustav - Пятница, 19.11.2021, 00:49
 
Ответить
Сообщение
2-я формула, введенная в ячейку K15 :
=ArrayFormula( SUMIF( A3:A & B3:B;
I15:INDEX(I15:I;ROWS(UNIQUE(A3:B)))
&
J15:INDEX(J15:J;ROWS(UNIQUE(A3:B)))
;C3:C))

В моем посте выше второй аргумент функции SUMIF в саморасширяющейся формуле при наличии даже одной конкатенации, согласитесь, выглядит несколько тяжеловесно (функцию INDEX в ссылочной форме приходится ведь "приставлять" к каждому из двух соединяемых диапазонов):
[vba]
Код
I15:INDEX(I15:I;ROWS(UNIQUE(A3:B)))
&
J15:INDEX(J15:J;ROWS(UNIQUE(A3:B)))
[/vba]А если надо будет соединить три или четыре диапазона? А если еще больше? В общем, с каждой добавляемой операцией конкатенации второй аргумент будет превращаться в трудновоспринимаемое "месиво" из функций INDEX. В общем, задумался, как бы облегчить ситуацию...

А дальше, как говорил Ростислав Плятт в роли завхоза Бубенцова в фильме "Весна" про метод работы ученых: "Сел, задумался... Открыл!" И придумалась мне конструкция, в которой число уникальных элементов, вычисляемое фрагментом:
[vba]
Код
ROWS(UNIQUE(A3:B))
[/vba]рассчитывается только один раз. Вот она:
[vba]
Код
ARRAY_CONSTRAIN( I15:I & J15:J; ROWS(UNIQUE(A3:B)); 1)
[/vba]
И тогда вся 2-я формула в новой редакции приобретает гораздо более прозрачный вид:
[vba]
Код
=ArrayFormula( SUMIF( A3:A & B3:B;
ARRAY_CONSTRAIN( I15:I & J15:J;
ROWS(UNIQUE(A3:B)); 1)
;C3:C))
[/vba]И ведь что приятно - конкатенируемые диапазоны второго аргумента SUMIF можно теперь также записать в простой открытой форме, при необходимости легко дополняя составное условие другими диапазонами:
[vba]
Код
I15:I & J15:J & ... & ...
[/vba]

Автор - Gustav
Дата добавления - 19.11.2021 в 00:05
Serge_007 Дата: Пятница, 19.11.2021, 09:59 | Сообщение № 20
Группа: Админы
Ранг: Местный житель
Сообщений: 15571
Репутация: 2559 ±
Замечаний: ±

Excel 2016
[offtop]
почему бы на форуме не создать раздел базу знаний (энциклопедию) по каждой из функций таблицы
Нечто подобное я начинал делать более десяти лет назад по функциям Excel и VBA
Вопрос упирается во время
На написание хорошего материала его уходит много, а польза - небольшая (в инете есть много подобного, хоть и неполного)[/offtop]


ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
Сообщение[offtop]
почему бы на форуме не создать раздел базу знаний (энциклопедию) по каждой из функций таблицы
Нечто подобное я начинал делать более десяти лет назад по функциям Excel и VBA
Вопрос упирается во время
На написание хорошего материала его уходит много, а польза - небольшая (в инете есть много подобного, хоть и неполного)[/offtop]

Автор - Serge_007
Дата добавления - 19.11.2021 в 09:59
Мир MS Excel » Вопросы и решения » Excel и другие приложения » Google Docs » Подсчет одинаковых деталей (Формулы/Formulas)
  • Страница 1 из 2
  • 1
  • 2
  • »
Поиск:

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