Несколько вложенных функций - Microsoft Office Excel

alexsampler · 01.11.2011, 13:24

Добрый день, Господа!
Нужна Ваша помощь
Excel 2003 SP3
Необходимо произвести суммирование по нескольким критериям, при чем в одном из них нужно искать только по определенной его части, словом все в примере.
Пытался связать СУММПРОИЗВ и ЛЕВСИМВ, но ничего путного из этого не вышло.
Все в примере...

И еще один параллельный вопрос: есть массив информации цифры и текст около 12 столбцов и 6 000 строк, как только пытаюсь воспользоваться автофильтром – excel безбожно зависает, приходиться снимать задачу после 30 мин ожидания.
Это нормально?
Пока выхожу из положения сортировкой, но на это уходит масса времени.
В чем может быть проблема?

Спасибо!

vikttur · 01.11.2011, 13:51

Суммировать данные какого столбца? Берем Н:

Код:

=СУММПРОИЗВ(--(C1:C23="А");--(N1:N23="нерез");--(B1:B23=840);--(ЛЕВСИМВ(A1:A23;3)="301");H1:H23)

Так быстрее (формула массива):

Код:

{=СУММ(ЕСЛИ(C1:C23="А";ЕСЛИ(N1:N23="нерез";ЕСЛИ(B1:B23=840;ЕСЛИ(ЛЕВСИМВ(A1:A23;3)="301";H1:H23)))))}

Serge 007 · 01.11.2011, 14:03

Чуть покороче

Код:

=СУММПРОИЗВ((--ЛЕВСИМВ(A1:A23;3)=301)*(C1:C23="А")*(B1:B23="840")*(N1:N23="нерез")*F1:F23)

По второму вопросу - нормально.
Сортировкой пользоваться желательно (это ускоряет работу как формул, так и фильтров).
А для таких массивов лучше сводная.

vikttur · 01.11.2011, 14:43

"короче" не значит "шустрее"

alexsampler · 01.11.2011, 16:30

Огромное спасибо!!!
vikttur, чем ваш вариант шустрее?
СУММПРОИЗВ - функция слишком трудоемкая для excel-я?

alexsampler · 01.11.2011, 16:31

А как на счет второго моего вопроса?

alexsampler · 01.11.2011, 16:32

пардон, на второй вопрос ответ ни сразу углядел....
спасибо!

alexsampler · 01.11.2011, 16:36

Цитата:

Сообщение от Serge 007

Чуть покороче

Код:

=СУММПРОИЗВ((--ЛЕВСИМВ(A1:A23;3)=301)*(C1:C23="А")*(B1:B23="840")*(N1:N23="нерез")*F1:F23)

По второму вопросу - нормально.
Сортировкой пользоваться желательно (это ускоряет работу как формул, так и фильтров).
А для таких массивов лучше сводная.

Как применить сводную? У меня массив идет из АБС Банка?
Я его преобразую в exl...

vikttur · 01.11.2011, 16:42

Второй вопрос нужно было размещать в другой теме.

По поводу скорости. СУММ() и СУММПРОИЗВ() в даннной задаче практически одинаковы. Разница в том, что "в коробочке".
СУММПРОИЗВ((усл1)*(усл2)*(усл3)) работает дольше, чем преобразование логического значения в число:
СУММПРОИЗВ(--(усл1);--(усл2);--(усл3))
В обоих случаях функция просматривает и обрабатывает все условия.

ЕСЛИ() позволяет исключить пересчет функций, что уменьшает время пересчета:
ЕСЛИ(усл1;ЕСЛИ(усл2;ЕСЛИ(усл3...
Т.е. если не выполняется, например, усл2, все условия, которые ниже (усл3...), игнорируются.

Разница в скорости неощутима, если таких формул немного. Но всегда нужно стремиться к оптимальности

gluk_fm · 01.11.2011, 19:58

Насчёт сводной таблицы Вам говорили, Вы просто наверно ни разу с ними не работали:
Вставка - сводная таблица

она позволяет быстро фильтровать данные.

01.11.2011, 13:51	#2
vikttur Участник клуба Регистрация: 16.05.2010 Сообщений: 1,249	Суммировать данные какого столбца? Берем Н: Код: `=СУММПРОИЗВ(--(C1:C23="А");--(N1:N23="нерез");--(B1:B23=840);--(ЛЕВСИМВ(A1:A23;3)="301");H1:H23)` Так быстрее (формула массива): Код: `{=СУММ(ЕСЛИ(C1:C23="А";ЕСЛИ(N1:N23="нерез";ЕСЛИ(B1:B23=840;ЕСЛИ(ЛЕВСИМВ(A1:A23;3)="301";H1:H23)))))}` Последний раз редактировалось vikttur; 01.11.2011 в 13:54.

01.11.2011, 14:03	#3
Serge 007 Участник клуба Регистрация: 15.12.2009 Сообщений: 1,448	Чуть покороче Код: `=СУММПРОИЗВ((--ЛЕВСИМВ(A1:A23;3)=301)(C1:C23="А")(B1:B23="840")(N1:N23="нерез")F1:F23)` По второму вопросу - нормально. Сортировкой пользоваться желательно (это ускоряет работу как формул, так и фильтров). А для таких массивов лучше сводная. Бесплатная помощь: www.excelworld.ru Платная помощь: serge_007.planetaexcel@mail.ru https://yoomoney.ru: 41001419691823

Похожие темы
Тема	Автор	Раздел	Ответов	Последнее сообщение
В графике закрасить определенными цветами несколько вложенных друг в друга полигонов (Delphi)	hunter03	Помощь студентам	4	02.10.2011 10:49
Программирование вложенных циклов	vanek1	Помощь студентам	2	28.11.2010 12:11
Несколько функций в DLL	Alex Cones	Общие вопросы Delphi	7	23.05.2009 22:36
Несколько функций в одной процедуре?	nePirat	Помощь студентам	2	04.05.2009 11:42
с использованием вложенных циклов	вкусняшка	Помощь студентам	4	31.03.2009 17:22

01.11.2011, 14:43	#4
vikttur Участник клуба Регистрация: 16.05.2010 Сообщений: 1,249	"короче" не значит "шустрее"

01.11.2011, 16:30	#5
alexsampler Пользователь Регистрация: 29.03.2011 Сообщений: 86	Огромное спасибо!!! vikttur, чем ваш вариант шустрее? СУММПРОИЗВ - функция слишком трудоемкая для excel-я?

01.11.2011, 16:31	#6
alexsampler Пользователь Регистрация: 29.03.2011 Сообщений: 86	А как на счет второго моего вопроса?

01.11.2011, 16:32	#7
alexsampler Пользователь Регистрация: 29.03.2011 Сообщений: 86	пардон, на второй вопрос ответ ни сразу углядел.... спасибо!

01.11.2011, 16:42	#9
vikttur Участник клуба Регистрация: 16.05.2010 Сообщений: 1,249	Второй вопрос нужно было размещать в другой теме. По поводу скорости. СУММ() и СУММПРОИЗВ() в даннной задаче практически одинаковы. Разница в том, что "в коробочке". СУММПРОИЗВ((усл1)(усл2)(усл3)) работает дольше, чем преобразование логического значения в число: СУММПРОИЗВ(--(усл1);--(усл2);--(усл3)) В обоих случаях функция просматривает и обрабатывает все условия. ЕСЛИ() позволяет исключить пересчет функций, что уменьшает время пересчета: ЕСЛИ(усл1;ЕСЛИ(усл2;ЕСЛИ(усл3... Т.е. если не выполняется, например, усл2, все условия, которые ниже (усл3...), игнорируются. Разница в скорости неощутима, если таких формул немного. Но всегда нужно стремиться к оптимальности

01.11.2011, 19:58	#10
gluk_fm Форумчанин Регистрация: 09.06.2011 Сообщений: 515	Насчёт сводной таблицы Вам говорили, Вы просто наверно ни разу с ними не работали: Вставка - сводная таблица она позволяет быстро фильтровать данные.