Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel (архив)
Страницы: 1
RSS
Снова сумма по нескольким критериям
 
#1
18.09.2009 22:11:25
Доброго всем вечера. Тема заезжена до невозможности, но все же в поиске ничего путного не нашел. В общем сабж: Нужно, чтобы формула считала сумму в столбце С, проверяя при этом критерии по столбцам А и В. Все бы просто на массивах, да не тут то было. Момент такой: Критерии отбора строк для суммирования могут содержаться в диапазоне. То есть проверка может быть такая: (В2:В30=В3:В5). Однако это не совсем правильная форма записи - нужна проверка, есть ли в первом диапазоне вхождения из второго. То есть второй диапазон -это набор условий. Конечно такое можно провернуть через СУММПРОИЗВ(СУММЕСЛИ())  ... но критерия то два. Просто не хочется утяжелять формулу проверкой отдельно нескольких условий, а загнать в критерий именно диапазон с набором этих самых условий.  
Конечные формулы должны находиться в диапазоне G2:J4  
Надеюсь хоть кто-то понял этот набор букв и откликнется. Третий день бьюсь...
 
 
 
#2
18.09.2009 22:20:13
А где, собственно, списки критериев групп?  
Т е что, к примеру, сырьё и материалы это металолом, ветошь и прочее.
 
 
 
#3
18.09.2009 22:29:34
Ну да. Там в Диапазоне G2:J3вбиты формулы. Но это все руками подтянуто. Хотелось бы все же одну формулу и протянуть. А выбор цеха по какому отображать все это хозяйство сделать в раскрывающемся списке. Дело в том, что у каждого цеха этих самых статей может быть в исходной таблице разное количество, поэтому нельзя тупо забить руками и выбирать по номеру позиции в общем списке (а хорошо бы было), поэтому вот и пляшем с бубуном...
 
 
 
#4
18.09.2009 22:31:43
Саша оказался быстрее.  
   
Заведите отдельный столбец с критериями, к примеру ремонт - 1р,2р,3р, Хознужды - 1х,2х,3х, Материалы - 1м,2м,3м. Чтобы красиво было скройте столбец. Далее СУММЕСЛИ с одним критерием.  
 
Критерий - это уникальный набор символов. Уважаемый Билл Гейц еще не научил Excel аналогово мыслить на русском языке...
 
 
 
#5
18.09.2009 22:36:14
Да нет, оба варианта решаемы.  
Если ручками вводить, т е в формуле через {}, то используем суммпроизв. Если диапазоном, то через, к примеру, сумм(суммесли()). Буквально сегодня оба варианта использовал в теме про выборку по нескольким условиям, тема на первой странице форума.
 
 
 
#6
18.09.2009 22:40:31
А, собственно, к чему это я:  
Вы просто пока задачу не поставили, в связи с чем непонятно что решать то надо.  
Вы условия дайте подробно, т е в том виде, в котором вы хотели бы получить их.  
Если не хотите доп столбцов/таблиц, то это нужно учитывать в формуле и, соответственно, мы должны знать, что мы должны учитывать.
 
 
 
#7
18.09.2009 22:58:25
Может проще таблицу переделать, чем изгаляться с формулами?
 
 
 
#8
18.09.2009 23:37:13
Нет, таблицу переделать не проще. Исходная таблица будет выгрузка из программы. В столбце А будут названия цехов (благо там хоть выгрузку можно настроить), в строке В названия бюджетных статей, ну и в С сумма по этим статьям. Фишка в том, что есть группы затрат, в которые входит несколько бюджетных статей. Например в группу "Расходы на ремонт" будут входить бюджетные статьи "Расходы на ремонт", "Расходы на капитальный ремонт", "Расходы на текущий ремонт" и т.д. Также по остальным группам. Из этой выгрузки хотелось бы получать собственно уже табличку с цехами, группами затрат и суммой по каждой группе. Так как исходная табличка может быть ооооочень большая, то вводить дополнительные столбцы с критериями не хочется, ибо я замучаюсь проставлять эти критерии. (даже если их через ЕСЛИ() подтягивать, то формулы будут большие и теряется смысл всей задумки). В общем хотелось бы без дополнительных манипуляций. Ищу простые формулы. А по какому цеху выводить инфу можно в раскрывающемся списке сделать. При выгрузке каждый месяц максимум что хотел бы, это менять размеры диапазона (хотя и его можно сделать динамическим, но это не сейчас).
 
 
 
#9
19.09.2009 00:33:04
=СУММПРОИЗВ($C$2:$C$30*($A$2:$A$30=$F2)*($B$2:$B$30={"ремонт";"оборудование"}))  
критерии тут в качестве примера.  
Неужели так сложно просто ответить на чётко поставленные вопросы?
 
 
 
#10
19.09.2009 00:47:43
Александр, спасибо за помощь. Не ругайтесь сильно. Не привык я формулировать вопросы (это дело практики), привык сам все делать, поэтому не сразу понятно сформулировал просьбу.  
Ваше решение оказалось довольно элегантным, хотя вот от набивания ручками условий ({"ремонт";"оборудование"}) я как раз и хотел уйти - хотел просто указать ссылки на них. Однако Эксель как оказалось такого не позволяет. Тут я стал сильно мудрить и придумал громоздкую формулу, которая как оказалось делает массу ненужных вычислений:  
{=СУММПРОИЗВ(СУММЕСЛИ(ДВССЫЛ(СЦЕПИТЬ("B";НАИБОЛЬШИЙ(ЕСЛИ(A2:A30=F2;СТРОКА(A2:A30);" ");СУММ((A2:A30=F2)*1));":B";НАИМЕНЬШИЙ(ЕСЛИ(A2:A30=F2;СТРОКА(A2:A30);" ");СУММ((A2:A30=F2)*1))));B2:B5;ДВССЫЛ(СЦЕПИТЬ("C";НАИБОЛЬШИЙ(ЕСЛИ(A2:A30=F2;СТРОКА(A2:A30);" ");СУММ((A2:A30=F2)*1));":C";НАИМЕНЬШИЙ(ЕСЛИ(A2:A30=F2;СТРОКА(A2:A30);" ");СУММ((A2:A30=F2)*1))))))}  
Потом посмотрел Вашу последнюю реализацию и заменил то, что мне в ней не нравится (набивание массива критериев ручками). В итоге пришел к вот такой формуле:  
{=СУММПРОИЗВ($C$2:$C$30*($A$2:$A$30=$F2)*($B$2:$B$30=ТРАНСП(B2:B5)))}  
Теперь диапазон условий можно указать прямо на листе ссылкой на соответствующие ячейки, при условии конечно что они смежные.  
Вроде концепцию понял. В понедельник на работе будет уже большой краш-тест на реальных данных.
 
 
 
#11
19.09.2009 03:56:46
А такой вариант чем не устраивает?
 
 
 
#12
19.09.2009 12:17:11
Тем, что плодятся лишние таблицы, которые фактически повторяют предыдущие, но перевернуто. С тем же успехом я мог и сводную сделать. Только хочется то одну единственную формулул, которая будет решать все проблемы. Этакая мифическая кнопка "шедевр" на фотоаппарте. В общем в предыдущем моем посте такая формула общими усилиями почти найдена. Последний вопрос: как собрать в массив в формуле несмежные ячейки? То естьпримерно так: должна быть формула, аргументами которой являются адреса несмежных ячеек. На выходе же мы должны получить что то вроде {A1;B5;C8;D24}. Возможно ли такое? То есть я хочу таким образом собирать аргументы условия для функции СУММЕСЛИ()
 
 
 
#13
19.09.2009 13:43:35
{quote}{login=Dachnik}{date=19.09.2009 12:47}{thema=}{post}Потом посмотрел Вашу последнюю реализацию и заменил то, что мне в ней не нравится (набивание массива критериев ручками). В итоге пришел к вот такой формуле:  
{=СУММПРОИЗВ($C$2:$C$30*($A$2:$A$30=$F2)*($B$2:$B$30=ТРАНСП(B2:B5)))}{/post}{/quote}  
Если используете функцию ТРАНСП() функция СУММПРОИЗВ() теряет смысл:  
 
{=СУММ($C$2:$C$30*($A$2:$A$30=$F2)*($B$2:$B$30=ТРАНСП(B2:B5)))}
 
 
 
#14
19.09.2009 13:44:23
последний пост - мой :-)
KL
 
 
 
#15
19.09.2009 14:33:28
Dachnik, как правильно уже заметил vovak, Excel аналогово мыслить на русском языке не обучен.  
Массив несмежных можно, в принципе, получить. Через, к примеру, двссыл. Адреса задаём формулами, количество ограничиваем, скажем, диапазоном a1:индекс(a:a;подсчёт критериев), который потом обрабатывается вышеупомянутыми формулами.  
Но там свои заморочки. Выложите файл, в котором подробно описана задача и даны исходные данные, в том числе критерии, в котором они будут в последствии, а мы подумаем уже над реализацией.  
Более того, скорее всего вам уже был бы дан ответ, если вы так сделали с самого начала. Тот же KL, вместо небольшой поправки, вполне мог написать тройку другую элегантных формул, максимально подходящих для решения данной задачи по разным критериям(т е по длине формулы, по производительности и т п).  
 
Учтите, что кнопка шедефр на фотоаппарате не всегда идеально подходит для создания шедефра. Т к вы можете тупо устать ждать пока она родит шедевр после нажатия на оную.
 
 
 
#16
20.09.2009 22:51:09
Я хочу встроенную функцию (комбинацию функций), которая будет давать тот же результат, что и вот такая моя ползовательская:  
Function МАССИВ(ParamArray x())  
Dim Massiv() As Variant  
ReDim Massiv(0 To UBound(x))  
For i = 0 To UBound(x)  
Massiv(i) = x(i)  
Debug.Print Massiv(i)  
Next i  
МАССИВ = Application.Transpose(Massiv)  
End Function  
 
 
Тобишь чтобы через точку с запятой перечислить адреса параметров, а она на выходе массив дала. Вот что мне надо. Остальное разобрался как реализовать.
 
 
 
#17
21.09.2009 02:31:38
Не уверен, что это Вам поможет, а вдруг?  
 
1. Пусть в ячейке А1 - текстовый список, например:  
Один;Два;Три  
 
2. Массив, который нужно получить:  
={"Один";"Два";"Три"}  
 
3. Создаем новое имя, например, Массив1 с такой формулой:  
=ВЫЧИСЛИТЬ("{"""&ПОДСТАВИТЬ(Лист1!$A$1;";";""";""")&"""}")  
 
4. Теперь можно работать  с именем Массив1, как с массивом:  
=ИНДЕКС(Массив1;1;2) выдаст 2-й элемент массива, т.е. "Два"
 
 
 
#18
21.09.2009 04:49:26
А что? Перед первым шагом, получаем текстовый список "Один;Два;Три" в ячейке с помощью & или СЦЕПИТЬ() :-)  
 
Еще можно так:  
=ДВССЫЛ({"A1";"A5";"A12"})  
 
или так:  
=СМЕЩ(A1;{0;4;11};)  
 
От конкретной задачи зависит :-)
KL
 
 
 
#19
21.09.2009 21:25:21
{quote}{login=ZVI}{date=21.09.2009 02:31}{thema=}{post}  
...  
3. Создаем новое имя, например, Массив1 с такой формулой:  
=ВЫЧИСЛИТЬ("{"""&ПОДСТАВИТЬ(Лист1!$A$1;";";""";""")&"""}")  
...{/post}{/quote}  
 
Извиняюсь, а можете пояснить пожалуйста что это за функция "вычислить"? Пользовательская?  
У меня такой нет и краткий поиск в гугле ничего не прояснил. Русский Excel-2007, все пакеты подкючены. Так же кратко глянул непереведённые с английского функции, тоже вроде бы ничего похожего по смыслу нету...  
Заранее спасибо.
 
 
 
#20
21.09.2009 21:36:50
2 САША, если не ушло в песок, то ZVI  уже приводил пример. Можно по сайту поискать. Работает в ИМЕНАх-?, и это еще макроязык EXCEL4?  
Вот немного по теме EXCEL4  
http://www.planetaexcel.ru/forum.php?thread_id=3405  
Вычислить было в другом месте, но сразу и не найдешь...
 
 
 
#21
21.09.2009 22:55:35
Игорь, спасибо, что сохранили ссылку!  
Саша, функция ВЫЧИСЛИТЬ(текст_формулы) и английский её вариант EVALUATE(текст_формулы) являются функциями листа макросов MS Excel 4.0 и Междун. листа мкросов соответсвенно. Если в книгу добавить эти листы, то в списке функций добавляются функции и команды макросов XLM, которые можно использовать и формулах имен Excel.
 
 
 
#22
21.09.2009 23:30:39
Вот тут есть кое-что про макрофункции Excel4:  
 
http://www.jkp-ads.com/Articles/ExcelNames08.asp  
http://www.rondebruin.nl/international.htm  
http://www.microsoft.com/downloads/details.aspx?displaylang=ru&FamilyID=c09bf7f7-d30e-4ce9-8930-5d03748ca5cd
KL
 
 
 
#23
21.09.2009 23:59:03
{quote}{login=ZVI}{date=21.09.2009 02:31}{thema=}{post}Не уверен, что это Вам поможет, а вдруг?  
 
1. Пусть в ячейке А1 - текстовый список, например:  
Один;Два;Три  
 
2. Массив, который нужно получить:  
={"Один";"Два";"Три"}  
 
3. Создаем новое имя, например, Массив1 с такой формулой:  
=ВЫЧИСЛИТЬ("{"""&ПОДСТАВИТЬ(Лист1!$A$1;";";""";""")&"""}")  
 
4. Теперь можно работать  с именем Массив1, как с массивом:  
=ИНДЕКС(Массив1;1;2) выдаст 2-й элемент массива, т.е. "Два"{/post}{/quote}  
 
При всех достоинствах этого решения, есть одна проблема: длинна формулы в имени не может превышать 256 символов :-)  
 
Т.е. если в ячейке Лист1!$A$1 например такое:  
 
Иванов;Смирнов;Кузнецов;Попов;Васильев;Петров;Соколов;Михайлов;Новиков;Федоров;Морозов;Волков;Алексеев;Лебедев;Семенов;Егоров;Павлов;Козлов;Степанов;Николаев;Орлов;Андреев;Макаров;Никитин;Захаров;Зайцев  
 
то решение засбоит из-за последней буквы фамилии Зайцев, т.к. длинна строки представляющей массив - 257 символов:  
 
={"Иванов";"Смирнов";"Кузнецов";"Попов";"Васильев";"Петров";"Соколов";"Михайлов";"Новиков";"Федоров";"Морозов";"Волков";"Алексеев";"Лебедев";"Семенов";"Егоров";"Павлов";"Козлов";"Степанов";"Николаев";"Орлов";"Андреев";"Макаров";"Никитин";"Захаров";"Зайцев"}
KL
 
 
 
Страницы: 1
Читают тему
Наверх