Здравствуйте. Не могу дойти до решения такого вопроса: Есть список деталей, а сбоку необходимое количество таких деталей. Некоторые детали повторяются. Задача в том, чтоб написать формулу, которая выведет список уникальных деталей + возле каждой детали укажет необходимую сумму таких деталей. Список уникальных деталей элементарно получается вывести, а вот как посчитать их количество - никак не допру. Подскажите пожалуйста, как можно получить такой результат? Табличка
Здравствуйте. Не могу дойти до решения такого вопроса: Есть список деталей, а сбоку необходимое количество таких деталей. Некоторые детали повторяются. Задача в том, чтоб написать формулу, которая выведет список уникальных деталей + возле каждой детали укажет необходимую сумму таких деталей. Список уникальных деталей элементарно получается вывести, а вот как посчитать их количество - никак не допру. Подскажите пожалуйста, как можно получить такой результат? ТабличкаFaraway
=QUERY(A2:C26;"select A,B, sum(C) GROUP BY A,B";1)
[/vba] [p.s.]формулу смотрите в файле, а то на официальном сайте майкрософт нашлась функция ГРУППА() - из макрофункций вестимо; SPLIT, кстати, оттуда же[/p.s.] [admin]Думаю, в таких случаях в этом разделе можно использовать тег кода[/admin]
можно сводной, можно QUERY [vba]
Код
=QUERY(A2:C26;"select A,B, sum(C) GROUP BY A,B";1)
[/vba] [p.s.]формулу смотрите в файле, а то на официальном сайте майкрософт нашлась функция ГРУППА() - из макрофункций вестимо; SPLIT, кстати, оттуда же[/p.s.] [admin]Думаю, в таких случаях в этом разделе можно использовать тег кода[/admin]прохожий2019
Сообщение отредактировал Pelena - Вторник, 16.11.2021, 08:08
Kashimirush, а, понял, оно подтянуло еще одну пустую строку как уникальную ) Но я не понимаю, почему оно берет только уникальные значения, а не список как он есть? Нет же в условии, что надо только уникальные значения? Или это функция sum() делает такую выдачу?
Kashimirush, а, понял, оно подтянуло еще одну пустую строку как уникальную ) Но я не понимаю, почему оно берет только уникальные значения, а не список как он есть? Нет же в условии, что надо только уникальные значения? Или это функция sum() делает такую выдачу?Faraway
Faraway, Интернет , конечно, помойка та еще. Но про QUERY материала полно. Изучите, для начала, основы этой функции, если вам сейчас объяснить только этот вопрос, общей картины у вас всё равно не будет... СТАТЬЯ
Faraway, Интернет , конечно, помойка та еще. Но про QUERY материала полно. Изучите, для начала, основы этой функции, если вам сейчас объяснить только этот вопрос, общей картины у вас всё равно не будет... СТАТЬЯKashimirush
Работа, работа, перейди на Федота...
Сообщение отредактировал Kashimirush - Среда, 17.11.2021, 14:24
Можно, тогда 2 формулы нужно будет использовать, сначала вывести уникальные: Затем подсчитать сумму по уникальным: Смотрите ячейку I15 и K15 в вашем примере.
Подход, в целом, неплохой - как простая и понятная альтернатива QUERY и сводной таблице. Особенно учитывая, что вроде бы более напрашивающаяся здесь множественная по критериям функция SUMIFS не работает в формулах массива, а однокритериальная (с одним условием) SUMIF - работает. Правда, в случае многих условий приходится, как и продемонстрировано выше, эти условия оформлять с помощью конкатенации.
Имеет смысл сразу заточиться на саморасширяемость второй формулы, чтобы не править адреса второго аргумента SUMIF всякий раз при изменении количества уникальных элементов. Ну, а первая формула и так саморасширяемая - по самой сути функции UNIQUE. Только где уникальность, там обычно и сортировка - при помощи SORT (ниже добавим, обернём).
Имеет смысл также не планировать нахождения данных другой природы ниже обрабатываемых диапазонов данных - чтобы можно было применить открытые диапазоны типа A3:B (т.е. сэкономить на втором индексе) и, соответственно, добавлять новые строки в конец таблицы, а не вставлять их в середину диапазона. Ну, и абсолютные ссылки здесь (в одной-единственной, непротягиваемой формуле) не являются необходимыми (т.е. можно сэкономить и на признаках абсолютности - знаках доллара)
Итого имеем: 1-я формула, введенная в ячейку I15: [vba]
Код
=SORT(UNIQUE(A3:B))
[/vba] 2-я формула, введенная в ячейку K15 (через ячейку от I15, поскольку таблица уникальных значений состоит из двух столбцов): [vba]
[/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, чем теперь очень горжусь и применяю ее направо и налево - с благодарностью Форуму!
Можно, тогда 2 формулы нужно будет использовать, сначала вывести уникальные: Затем подсчитать сумму по уникальным: Смотрите ячейку I15 и K15 в вашем примере.
Подход, в целом, неплохой - как простая и понятная альтернатива QUERY и сводной таблице. Особенно учитывая, что вроде бы более напрашивающаяся здесь множественная по критериям функция SUMIFS не работает в формулах массива, а однокритериальная (с одним условием) SUMIF - работает. Правда, в случае многих условий приходится, как и продемонстрировано выше, эти условия оформлять с помощью конкатенации.
Имеет смысл сразу заточиться на саморасширяемость второй формулы, чтобы не править адреса второго аргумента SUMIF всякий раз при изменении количества уникальных элементов. Ну, а первая формула и так саморасширяемая - по самой сути функции UNIQUE. Только где уникальность, там обычно и сортировка - при помощи SORT (ниже добавим, обернём).
Имеет смысл также не планировать нахождения данных другой природы ниже обрабатываемых диапазонов данных - чтобы можно было применить открытые диапазоны типа A3:B (т.е. сэкономить на втором индексе) и, соответственно, добавлять новые строки в конец таблицы, а не вставлять их в середину диапазона. Ну, и абсолютные ссылки здесь (в одной-единственной, непротягиваемой формуле) не являются необходимыми (т.е. можно сэкономить и на признаках абсолютности - знаках доллара)
Итого имеем: 1-я формула, введенная в ячейку I15: [vba]
Код
=SORT(UNIQUE(A3:B))
[/vba] 2-я формула, введенная в ячейку K15 (через ячейку от I15, поскольку таблица уникальных значений состоит из двух столбцов): [vba]
[/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, чем теперь очень горжусь и применяю ее направо и налево - с благодарностью Форуму! Gustav
Не использовал этот ключ никогда и спутал с "order by", свою ошибку понял, а заодно понял работу GROUP BY. А статью знаю, она у меня в закладках. Это то, с чего я начал знакомство с QUERY.
Не использовал этот ключ никогда и спутал с "order by", свою ошибку понял, а заодно понял работу GROUP BY. А статью знаю, она у меня в закладках. Это то, с чего я начал знакомство с QUERY.Faraway
понял работу 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], из чего делаем вывод, что автору той статьи этот нюанс про неявную автоматическую сортировку при группировке пока не известен.
понял работу 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
Kashimirush, Gustav, вы первые приходите отвечать на вопросы, по крайней мере на мои. Я конечно же благодарен и всем остальным которые мне помогали и всегда стараюсь нажать плюсик. У меня ко всем знатокам данного форума вопрос-предложение. А почему бы на форуме не создать раздел базу знаний (энциклопедию) по каждой из функций таблицы. Я понимаю, что в интернете много всего есть уже описано, взять ту же гугл справку... Но везде все скудно и не полностью. Можно было бы первым постом размещать описание функции, что делает, как использовать с примерами. Далее идет обсуждение по каждой из функций, если в обсуждении появилось что-то новое и интересное, что было упущено в изначальном описании, то оно добавляется в первый пост.
Kashimirush, Gustav, вы первые приходите отвечать на вопросы, по крайней мере на мои. Я конечно же благодарен и всем остальным которые мне помогали и всегда стараюсь нажать плюсик. У меня ко всем знатокам данного форума вопрос-предложение. А почему бы на форуме не создать раздел базу знаний (энциклопедию) по каждой из функций таблицы. Я понимаю, что в интернете много всего есть уже описано, взять ту же гугл справку... Но везде все скудно и не полностью. Можно было бы первым постом размещать описание функции, что делает, как использовать с примерами. Далее идет обсуждение по каждой из функций, если в обсуждении появилось что-то новое и интересное, что было упущено в изначальном описании, то оно добавляется в первый пост.Faraway
Сообщение отредактировал Faraway - Четверг, 18.11.2021, 17:20
В моем посте выше второй аргумент функции SUMIF в саморасширяющейся формуле при наличии даже одной конкатенации, согласитесь, выглядит несколько тяжеловесно (функцию INDEX в ссылочной форме приходится ведь "приставлять" к каждому из двух соединяемых диапазонов): [vba]
[/vba]А если надо будет соединить три или четыре диапазона? А если еще больше? В общем, с каждой добавляемой операцией конкатенации второй аргумент будет превращаться в трудновоспринимаемое "месиво" из функций INDEX. В общем, задумался, как бы облегчить ситуацию...
А дальше, как говорил Ростислав Плятт в роли завхоза Бубенцова в фильме "Весна" про метод работы ученых: "Сел, задумался... Открыл!" И придумалась мне конструкция, в которой число уникальных элементов, вычисляемое фрагментом: [vba]
Код
ROWS(UNIQUE(A3:B))
[/vba]рассчитывается только один раз. Вот она: [vba]
[/vba]И ведь что приятно - конкатенируемые диапазоны второго аргумента SUMIF можно теперь также записать в простой открытой форме, при необходимости легко дополняя составное условие другими диапазонами: [vba]
В моем посте выше второй аргумент функции SUMIF в саморасширяющейся формуле при наличии даже одной конкатенации, согласитесь, выглядит несколько тяжеловесно (функцию INDEX в ссылочной форме приходится ведь "приставлять" к каждому из двух соединяемых диапазонов): [vba]
[/vba]А если надо будет соединить три или четыре диапазона? А если еще больше? В общем, с каждой добавляемой операцией конкатенации второй аргумент будет превращаться в трудновоспринимаемое "месиво" из функций INDEX. В общем, задумался, как бы облегчить ситуацию...
А дальше, как говорил Ростислав Плятт в роли завхоза Бубенцова в фильме "Весна" про метод работы ученых: "Сел, задумался... Открыл!" И придумалась мне конструкция, в которой число уникальных элементов, вычисляемое фрагментом: [vba]
Код
ROWS(UNIQUE(A3:B))
[/vba]рассчитывается только один раз. Вот она: [vba]
[/vba]И ведь что приятно - конкатенируемые диапазоны второго аргумента SUMIF можно теперь также записать в простой открытой форме, при необходимости легко дополняя составное условие другими диапазонами: [vba]
почему бы на форуме не создать раздел базу знаний (энциклопедию) по каждой из функций таблицы
Нечто подобное я начинал делать более десяти лет назад по функциям Excel и VBA Вопрос упирается во время На написание хорошего материала его уходит много, а польза - небольшая (в инете есть много подобного, хоть и неполного)[/offtop]
почему бы на форуме не создать раздел базу знаний (энциклопедию) по каждой из функций таблицы
Нечто подобное я начинал делать более десяти лет назад по функциям Excel и VBA Вопрос упирается во время На написание хорошего материала его уходит много, а польза - небольшая (в инете есть много подобного, хоть и неполного)[/offtop]Serge_007
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
