|
Выбор уникальных значений из списка, подсчет их количества и суммы
Вложений: 1
Помогите народ. У меня такая зада нужно автоматизировать. Файл Exсel прилагается. Версия MS Office не имеет значение. Думаю обойдется без скрипта, только с формулами было бы лучше. Гурманам, фанатам спасибо.
|
В ячейку Н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) |
Цитата:
|
|
Delirium, для столбца 1 ячейка g4 выдает ошибку. По этому я спрашиваю!!!
|
Для I4
|
okshef, у меня I4 и так стоит формула, только у меня проще. Мне б g4 надо автоматизировать было бы еще лучше.
|
Вложений: 1
Переименовал вашу тему, обратился к спецам за формулой для G4
Код:
=НАИМЕНЬШИЙ(ЕСЛИ(ПОИСКПОЗ(D$3:D$27;D$3:D$27;)=СТРОКА(D$3:D$27)-2;D$3:D$27);СТРОКА(A1))Протягиваете ее вниз до тех пор, пока не появится сообщение об ошибке. Остальное мы уже обсуждали. В файле выполнена автоматизация с помощью сводной таблицы - мне нравится :) |
Цитата:
|
Delirium, это самая оптимальная :) Но согласись - работает красиво!
Поэтому я люблю сводные |
okshef, Да, огромное вам спасибо! Вы мне очень помогли. А вот строка
Цитата:
Все-таки в Excel-е неограниченные возможности. Не боюсь этого слова, всю жизнь человек находит в нем чего-то новое для себя, так значить одной жизни мало для освоения Excel. |
Цитата:
Цитата:
|
Цитата:
|
Farrukhjon, загрузите файл еще раз, я его несколько раз изменял. По файлу: верхняя таблица ваша, средняя - сводная, нижняя, составленная с помощью формул, пощелкайте по ячейкам... :)
По поводу функции "Функция СТРОКА([ссылка])". В этой формуле она выступает в двух ипостасях. Первая: Как вы заметили, для построения ранжированного списка используется функция "НАИМЕНЬШИЙ", аргументами которой - "НАИМЕНЬШИЙ(массив, 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 Как-то так. |
Цитата:
|
okshef, Вот что я выбрал
1 столбец Код:
=НАИМЕНЬШИЙ(ЕСЛИ(ПОИСКПОЗ(D$3:D$27;D$3:D$27;)=СТРОКА(D$3:D$27)-2;D$3:D$27);СТРОКА(A1))3 столбец =СУММПРОИЗВ(($D$3:$D$27=G4)*G4) Если во время создания еще возникнуть вопросы непременно вам обращусь. Я начал уже создавать думаю за месяц успею создать... Iska, просто название темы изначально было другая и потом okshef изменил название. |
Цитата:
Обрамляйте формулы тэгом [code] или устанавливайте флажок «[*] Отключить смайлики в тексте» в параметрах поста (первое предпочтительнее). |
Вложений: 1
Столкнулся с такой проблемой. Постараюсь объяснить. Если удалить хоть одну строку цены все результаты выдают н\д. А для меня так не пойдет. Каждый раз изменить формулу не хочется. Много времени отнимает. Ведь каждый день одинаково не продаются же диски...
|
Все правильно. К сожалению при отсутствии данных вы будете получать ошибку. Поэтому либо удаляйте всю строку целиком, либо нужно нагромождать формулу для обработки ошибки.
В сводной таблице такой проблемы нет :) |
okshef, Мне в этой таблице нужно изменить формулу чтобы не были ошибки.
|
Вопрос: у вас это поле так и останется пустым, или будет когда-то чем-то заполнено?
То есть пустота временная? А "0" поставить нельзя? |
okshef, 0 поставил не получается. Мне чтобы если пусто не брал если заполнено то брал. не всегда строки заполняются.
|
Вы не ответили на вопрос: пустоты зарезервированы? То есть в будущем планируется их заполнить или нет?
|
okshef, Иногда могут заполниться и иногда вообще пустоты остаются.
|
Код:
=НАИМЕНЬШИЙ(ЕСЛИ(ЕСЛИ(ЕНД(ПОИСКПОЗ(D$3:D$27;D$3:D$27;));0;ПОИСКПОЗ(D$3:D$27;D$3:D$27;))=СТРОКА(D$3:D$27)-2;D$3:D$27);СТРОКА(A1))Но учтите. Некоторые значения у вас встречаются только один раз. И если исчезнут они - будут новые ошибки, в частности, отсутствие числа. Тогда придется изменять формулы сумм. |
Цитата:
Ведь в моем случае не всегда встречаются некоторые суммы. |
Farrukhjon, в этом случае я перехожу от намеков к настоятельной рекомендации использовать сводную таблицу. Поверьте, это гораздо проще.
|
okshef, Если честно ничего такого я в вашей сводной таблицы не нашел. Оно не изменяется если я изменяю данные. Мне нужно добить того чего я уже начал думаю остался всего один шаг.
|
Цитата:
А как вы хотели? Да, сводная таблица статична, и приходится обновлять, но зато нет головной боли писать формулы в 100 знаков. |
okshef, тогда я попробую обратиться на планету или через VBA, если можно сделать, надо бы.
|
Farrukhjon, чтобы составить любую программу, необходим ее алгоритм. Как вы его себе представляете в этом случае?
В целях познавательных или для время убиения - это достойная задача, практического же смыла у нее нет никакого. Лучше уж формулы... Попытаюсь набросать алгоритм в общих чертах. ================= 1. Мы люди ленивые, и программа должна запускаться при изменениях на листе 2. Формирование массива и его сортировка 3. Удаление всей имеющейся старой таблицы для подсчетов. 4. Заполнение столбцов а) данными б) формулами 2-го столбца в) формулами 3-го столбца г) суммами и количествами ================= Ну как? И это вместо одной строчки кода при наличии сводной Код:
RefreshAllВпрочем, уговаривать больше не буду. Вопрос достаточно проработан, чтобы размусоливать его еще постов на 10-20. |
okshef, честно говоря я вообще не мог использовать вашу сводную таблицу. Вообще не понял?
|
|
| Время: 03:36. |
Время: 03:36.
© OSzone.net 2001-