Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Создать формулу обработки массива
 
#1
01.03.2013 18:14:10
Уважаемые ГУРУ, привествую Вас!

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

{=СУММ(ЕСЛИ('[Открытые ПУ.xls]База'!$H$2:$H$10001=C4;ЕСЛИ('[Открытые ПУ.xls]База'!$A$2:$A$10001>=$A$5;ЕСЛИ('[Открытые ПУ.xls]База'!$A$2:$A$10001<=$B$5;ЕСЛИ('[Открытые ПУ.xls]База'!$G$2:$G$10001>0;1;0);0);0);0))}

Какое-то время я этим не пользовался, а сейчас возникла необходимость.
Руками эту формулу я прописать могу и все работает, но это очень много занимает времени.
Помню это как-то очень просто делалось: выделялся диапазон данных, выделялись условия, Ctrl+Shift+Enter и все готово, дальше растягиваешь/клпируешь ее куда надо и все работает.

Взываю Вас о помощи!!!
Как (пошагово) создается именно эта формула обработки массива?

Заранее благодарен.

Илья

P.S.
В формуле закладывается несколько условий, например:
- менеджер,
- период,
- проданный продукт,
- конкретный клиент.
 
 
 
#2
01.03.2013 19:16:58
А Вы уверены, что хотите именно массивную формулу? Может подойдет СУММЕСЛИМН?
А лучше покажите пример.
Изменено: Алексей К - 01.03.2013 19:17:49
 
 
 
#3
01.03.2013 22:58:28
Так много слов, вместо одного маленького примера... Грустно.
"..Сладку ягоду рвали вместе, горьку ягоду я одна."
 
 
 
#4
02.03.2013 13:56:05
Вот пример, надо было сразу его приложить..

Например, нужно в ячейку F4 посчитать количество клиентов:
- которые принесли КД (комиссионного дохода) от 0 до 30 долларов
- в октябре 2012 года,
- привлеченные Дмитрием Петровичем Тимошкиным,
- в период с 01.01.2012 по 31.10.2013.

Почему использую массивную формулу? Когда-то показали - очень удобно работает, не требует определенного порядка задания критериев отбора (помещаешь их в любые, удобные для тебя ячейки)..

Если есть другие, более простые варианты, буду очень признателен, если их подскажите.

Но основной вопрос, на который хотелось бы получить ответ: Как (пошагово) не прописывая руками создается именно такая формула массива.

Заранее спасибо!
 
 
 
#5
02.03.2013 14:16:50
Цитата
GIO пишет:
не прописывая руками
присоединяюсь к вопросу!
мне тоже любопытен способ прописывания формул ногами или какими-нибудь другими частями тела.

пс. щелкать мышкой по ячейкам и, мышкой же, выделять диапазоны, переключаться между листами и книгами не пробовали?
Изменено: ikki - 02.03.2013 14:18:15
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#6
02.03.2013 14:24:26
ikki,

в этом то и вопрос: КУДА (в какое меню) зайти, чтоб "щелкать мышкой по ячейкам и мышкой же выделять диапазоны"?
 
 
 
#7
02.03.2013 14:27:08
ячейки у вас где?
на листах.
по ним и щелкайте.
поставили знак равно в строке формул - и айда.
можно и вперемешку - часть мышкой, часть с клавиатуры.
какое вам еще меню надо? :)
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#8
02.03.2013 14:43:17
ОК.

Например:
с клавиатуры =СУММ(ЕСЛИ
дальше мышкой выбрал диапазон, условие
с клавиатуры ;ЕСЛИ
дальше мышкой диапазон, условие
и т.д.
получается вот такая недоделанная формула,

=сумм(ЕСЛИ(КД!D:D='КМ отчет'!A4;ЕСЛИ(КД!B:B>='КМ отчет'!B1;ЕСЛИ(КД!B:B<='КМ отчет'!C1

в ней не хватает ;1;0);0);0);0))

Это тоже от руки?

Это все делалось как-то проще...
 
 
 
#9
02.03.2013 14:47:09
GIO, если Вы про ввод формулы массива, то заканчивать ввод/редактирование следует нажатием комбинации клавиш Ctrl+Shift+Enter. Об этом, кстати, есть во встроенной справке.
 
 
 
#10
02.03.2013 14:58:24
Юрий, с  Ctrl+Shift+Enter все понятно

Вопрос в другом.
нужно создать формулу
=СУММ(ЕСЛИ...;ЕСЛИ...;ЕСЛИ... и т.д. с кучей условий
можно все это прописать с клавиатуры, т.е. пишем = и поехали, выбирая диапазоны и значения и в конце так же с клавиатуры дописывая хвостик  ;1;0);0);0);0)) , необходимый, чтоб формула заработала, жмем Ctrl+Shift+Enter и вуаля... работает

Но есть более простой путь, при котором Excel сам проставляет нужные символы между условиями (скобки, толки с запятой)

Это как например:
1. можно прописать =ЕСЛИ(1=1;1;0)
2. а можно в строке формул нажать fx, откроется мастер формул (отдельное диалоговое окно), где можно проставить агрументы функции, нажать ОК, и формула пропишется автоматически.

Так вот в моем случае, путь 1 мне известен, я не могу найти путь 2, которые более простой.

Спасибо!
 
 
 
#11
02.03.2013 15:26:26
Что-то мне подсказывает,  Вам нужно СЧЁТЕСЛИМН исходя из
Цитата
GIO пишет:
нужно в ячейку F4 посчитать количество клиентов:
, а не СУММ(ЕСЛИ...

Посмотрите вариант.
 
 
 
#12
02.03.2013 15:31:32
В предыдущем примере не хватает фильтров по датам (упустил). Кстати, в Вашем примере не нашел значений, удовлетворяющих Вашим условиям. Здесь добавил условия с датами.
Изменено: Алексей К - 02.03.2013 15:32:06
 
 
 
#13
02.03.2013 15:37:31
Алексей, гениально!!!

Пытаюсь разобраться с тем как Вы это сделали, главное есть пример формулы, дальше а ее разложу

СПАСИБО!

Можно Вас попросить еще сделать такую же формулу, но чтоб считалась не сумма, а количество клиентов..
 
 
 
#14
02.03.2013 15:38:16
вот файл примера
 
 
 
#15
02.03.2013 15:42:24
В ячейке ниже таблицы, где значение 0 как раз формула для подсчета количества. Формулу для суммы проверьте на условия, я ее не удалил просто
Изменено: Алексей К - 02.03.2013 15:45:10
 
 
 
#16
02.03.2013 15:45:42
Цитата
GIO пишет:
есть более простой путь, при котором Excel сам проставляет нужные символы между условиями (скобки, толки с запятой)
есть.
мастер функций называется.
каждый аргумент - в отдельном поле.
скобки и разделители аргументов Excel поставит сам.
в строке формул - иконка с символом функции.
или F3.
но я не пользуюсь.
неудобно.
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#17
02.03.2013 15:46:09
Алексей,

у Вас прописано ">=01.01.2012"
а ставлю вместо текста ссылку на ячейку ">=B1" с датой

и формула перестает считать..
 
 
 
#18
02.03.2013 15:49:40
в строке формул - иконка с символом функции.
или F3.

Только он не дает написать такую многоуровневую формулу, ну или я просто не умею это сделать
 
 
 
#19
02.03.2013 15:51:32
Код
">=" & B1


Цитата
GIO пишет:
он не дает написать такую многоуровневую формулу
находясь в поле-аргументе мастера, снова нажимаете иконку и выбираете нужную функцию.
откроется форма мастера для этой вложенной функции.
в строке формул эта часть общей формулы будет отображаться полужирным шрифтом.
закончив работу с этой "вложенной" функцией, в строке формул в тексте формулы щелкаете в нужном месте - отобразится мастер и аргументы для "внешней" функции.
при большом кол-ве вложенных функций - немудрено и запутаться  :)
поэтому я и говорю - неудобно.
а самое главное - долго.
Изменено: ikki - 02.03.2013 15:58:00
фрилансер Excel, VBA - контакты в профиле
"Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
 
 
 
#20
02.03.2013 15:58:16
СПАСИБО ВАМ ОГРОМНОЕ!!!!!

ikki,  Алексей К,   респект ВАМ
 
 
 
Страницы: 1
Читают тему
Наверх