[решено] Выбор уникальных значений из списка, подсчет их количества и суммы

[Farrukhjon]

Помогите народ. У меня такая зада нужно автоматизировать. Файл Exсel прилагается. Версия MS Office не имеет значение. Думаю обойдется без скрипта, только с формулами было бы лучше. Гурманам, фанатам спасибо.

[okshef]

В ячейку Н4 формулу:
=СЧЁТЕСЛИ($D$3:$D$27;G4)
и протяните по диапазону

Можно и больше, но если вам нужен только 1 столбец...
Нет, не удержался... Например, для столбца I
=СУММЕСЛИ($D$3:$D$27;G4) или
=СУММПРОИЗВ(($D$3:$D$27=G4)*G4) или формула массива
=СУММ(($D$3:$D$27=G4)*G4)

[Farrukhjon]

Нет, не удержался... Например, для столбца I
Код: »
это для какой ячейки?

[Delirium]

это для какой ячейки? »
для столбца I »

[Farrukhjon]

Delirium, для столбца 1 ячейка g4 выдает ошибку. По этому я спрашиваю!!!

[okshef]

Для I4

[Farrukhjon]

okshef, у меня I4 и так стоит формула, только у меня проще. Мне б g4 надо автоматизировать было бы еще лучше.

[okshef]

Переименовал вашу тему, обратился к спецам за формулой для G4
=НАИМЕНЬШИЙ(ЕСЛИ(ПОИСКПОЗ(D$3:D$27;D$3:D$27;)=СТРОКА(D$3:D$27)-2;D$3:D$27);СТРОКА(A1))
Вводится как формула массива (http://office.microsoft.com/ru-ru/excel-help/HA010228458.aspx#BM1) (Ctrl + Shift + Enter)
Протягиваете ее вниз до тех пор, пока не появится сообщение об ошибке. Остальное мы уже обсуждали.

В файле выполнена автоматизация с помощью сводной таблицы - мне нравится :)

[Delirium]

=НАИМЕНЬШИЙ(ЕСЛИ(ПОИСКПОЗ(D$3:D$27;D$3:D$27;)=СТРОКА(D$3:D$27)-2;D$3:D$27);СТРОКА(A1)) »
ЖЖЖЕСТЬ :)

[okshef]

Delirium, это самая оптимальная :) Но согласись - работает красиво!

Поэтому я люблю сводные

[Farrukhjon]

okshef, Да, огромное вам спасибо! Вы мне очень помогли. А вот строка СТРОКА(A1)) » чо тут делает не понял... А в вашем файле ни чего такого не нашел или я не понял?!...
Все-таки в Excel-е неограниченные возможности. Не боюсь этого слова, всю жизнь человек находит в нем чего-то новое для себя, так значить одной жизни мало для освоения Excel.

[Iska]

Переименовал вашу тему, »
Спасибо. Я только теперь начал понимать, что же хотел автор получить на самом деле.

Ей-ей, иной раз смотришь на вопрос и вспоминается карикатура времён холодной войны: за трибуной стоит президент, на трибуне микрофоны, из-за спины к микрофонам тянется представитель из его окружения, поясняющий для прессы: «После речи президента последует пресс-конференция о том, как следует понимать то, что он хотел сказать».

ЖЖЖЕСТЬ :) »
Ничуть. У меня была (и, как обычно, сплыла) книжка по пятому Excel'ю, где целый раздел был посвящён вопросам, решаемым сложными формулами, в том числе использующих массивы.

[Farrukhjon]

Я только теперь начал понимать, что же хотел автор получить на самом деле. » Не очень-то понял что вы имеете введу?

[okshef]

Farrukhjon, загрузите файл еще раз, я его несколько раз изменял. По файлу: верхняя таблица ваша, средняя - сводная, нижняя, составленная с помощью формул, пощелкайте по ячейкам... :)
По поводу функции "Функция СТРОКА([ссылка]) (http://office.microsoft.com/ru-ru/excel-help/redir/HA102752879.aspx?queryid=07fd50c3%2Dd8fc%2D4706%2Da38f%2Df2cb63e1ff41&respos=0&CTT=1)". В этой формуле она выступает в двух ипостасях.

Первая:

Как вы заметили, для построения ранжированного списка используется функция "НАИМЕНЬШИЙ (http://office.microsoft.com/ru-ru/excel-help/HP010342904.aspx)", аргументами которой - "НАИМЕНЬШИЙ(массив, k)" - является массив и некое число, которое показывает какое по счету из ряда чисел вам нужно. Например, в вашем случае есть числа 4, 5, 6, 7, 8
Если k=2, то функция вернет 5, потому что это второе наименьшее число из всех, если k=4, то 7.
Строка(А1)=1. При протягивании функции вниз Excel автоматически изменит А1 на А2 и аргумент функции "НАИМЕНЬШИЙ" изменится на 2 (СТРОКА(А2)=2) и вы получите второе наименьшее число. Таким образом получите ранжированный список.

Вторая:

В справке написано
Если значение аргумента «ссылка» является диапазоном ячеек, а функция СТРОКА введена как вертикальный массив, функция СТРОКА возвращает номера строк, указанных в аргументе «ссылка», в виде вертикального массива.
А они в свою очередь нужны для осуществления векторного произведения
ПОИСКПОЗ(D$3:D$27;D$3:D$27;)=СТРОКА(D$3:D$27)-2

Как-то так.

[Iska]

Не очень-то понял что вы имеете введу? »
Я имел в виду, что я по Вашему первому посту изначально толком не понял Ваших потребностей.

[Farrukhjon]

okshef, Вот что я выбрал
1 столбец =НАИМЕНЬШИЙ(ЕСЛИ(ПОИСКПОЗ(D$3:D$27;D$3:D$27;)=СТРОКА(D$3:D$27)-2;D$3:D$27);СТРОКА(A1))
2 столбец =СЧЁТЕСЛИ($D$3:$D$27;G4)
3 столбец =СУММПРОИЗВ(($D$3:$D$27=G4)*G4)

Если во время создания еще возникнуть вопросы непременно вам обращусь. Я начал уже создавать думаю за месяц успею создать...
Iska, просто название темы изначально было другая и потом okshef изменил название.

[Iska]

Iska, просто название темы изначально было другая и потом okshef изменил название. »
Вот и я про то же: когда okshef сменил название темы, я начал понимать.

Обрамляйте формулы тэгом [code] (http://forum.oszone.net/misc.php?do=bbcode#code) или устанавливайте флажок « Отключить смайлики в тексте» в параметрах поста (первое предпочтительнее).

[Farrukhjon]

Столкнулся с такой проблемой. Постараюсь объяснить. Если удалить хоть одну строку цены все результаты выдают н\д. А для меня так не пойдет. Каждый раз изменить формулу не хочется. Много времени отнимает. Ведь каждый день одинаково не продаются же диски...

[okshef]

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

В сводной таблице такой проблемы нет :)

[Farrukhjon]

okshef, Мне в этой таблице нужно изменить формулу чтобы не были ошибки.