Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Суммы с ограничением по условию - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин  
Мир MS Excel » Работа и общение » Мозговой штурм » Суммы с ограничением по условию (Решение формулой)
Суммы с ограничением по условию
AlexM Дата: Воскресенье, 04.05.2014, 10:55 | Сообщение № 1
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4517
Репутация: 1129 ±
Замечаний: 0% ±

Excel 2003
На днях решал задачу на другом форуме.
Не уверен, что название темы точно отражает суть задачи, но ничего лучше не придумывается.
В файле примере видно, что надо сделать.
Формула должна охватывать диапазон A2:A999, чтобы была возможность добавлять новые значения.
Условие ограничения суммы в ячейке В2.
В моем решении нулевые значения в столбце С скрыты форматом ячейки тип "0;;"
Формула для Excel2003 – 287 знаков
Формула для старших версий Excel – 160 знаков
Длина формул без учета знака "="

Вопрос. Можно ли сделать формулу короче?
К сообщению приложен файл: 11111.xls (15.0 Kb)


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.

Сообщение отредактировал AlexM - Воскресенье, 04.05.2014, 11:32
 
Ответить
СообщениеНа днях решал задачу на другом форуме.
Не уверен, что название темы точно отражает суть задачи, но ничего лучше не придумывается.
В файле примере видно, что надо сделать.
Формула должна охватывать диапазон A2:A999, чтобы была возможность добавлять новые значения.
Условие ограничения суммы в ячейке В2.
В моем решении нулевые значения в столбце С скрыты форматом ячейки тип "0;;"
Формула для Excel2003 – 287 знаков
Формула для старших версий Excel – 160 знаков
Длина формул без учета знака "="

Вопрос. Можно ли сделать формулу короче?
Автор - AlexM
Дата добавления - 04.05.2014 в 10:55
MCH Дата: Воскресенье, 04.05.2014, 12:35 | Сообщение № 2
Группа: Админы
Ранг: Старожил
Сообщений: 2004
Репутация: 752 ±
Замечаний: ±

Получилась формула 103 101 97 с "="
Нормально работает в 2003


Сообщение отредактировал MCH - Воскресенье, 04.05.2014, 17:19
 
Ответить
СообщениеПолучилась формула 103 101 97 с "="
Нормально работает в 2003
Автор - MCH
Дата добавления - 04.05.2014 в 12:35
MCH Дата: Воскресенье, 04.05.2014, 17:26 | Сообщение № 3
Группа: Админы
Ранг: Старожил
Сообщений: 2004
Репутация: 752 ±
Замечаний: ±

ужал до 72 с "="
 
Ответить
Сообщениеужал до 72 с "="
Автор - MCH
Дата добавления - 04.05.2014 в 17:26
ZORRO2005 Дата: Воскресенье, 04.05.2014, 19:04 | Сообщение № 4
Группа: Друзья
Ранг: Обитатель
Сообщений: 382
Репутация: 148 ±
Замечаний: 0% ±

Excel2010
107 103 101


Сообщение отредактировал ZORRO2005 - Понедельник, 05.05.2014, 12:10
 
Ответить
Сообщение107 103 101
Автор - ZORRO2005
Дата добавления - 04.05.2014 в 19:04
vikttur Дата: Понедельник, 05.05.2014, 02:33 | Сообщение № 5
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2941
Репутация: 526 ±
Замечаний: 0% ±

=+167 для молодых, 222 для 2003.
Чем Михаил прессует?...


Сообщение отредактировал vikttur - Понедельник, 05.05.2014, 02:42
 
Ответить
Сообщение=+167 для молодых, 222 для 2003.
Чем Михаил прессует?...
Автор - vikttur
Дата добавления - 05.05.2014 в 02:33
AlexM Дата: Понедельник, 05.05.2014, 11:30 | Сообщение № 6
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4517
Репутация: 1129 ±
Замечаний: 0% ±

Excel 2003
Есть 101 100 96 знака без знака "=" для Excel2003.
Формат ячейки тип "0;;" скрывает нулевые значения.


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.

Сообщение отредактировал AlexM - Понедельник, 05.05.2014, 13:25
 
Ответить
СообщениеЕсть 101 100 96 знака без знака "=" для Excel2003.
Формат ячейки тип "0;;" скрывает нулевые значения.
Автор - AlexM
Дата добавления - 05.05.2014 в 11:30
MCH Дата: Среда, 07.05.2014, 12:00 | Сообщение № 7
Группа: Админы
Ранг: Старожил
Сообщений: 2004
Репутация: 752 ±
Замечаний: ±

Будут еще желающие, а то до понедельника ждать долго и интерес к задаче пропадает?
Цитата vikttur, 05.05.2014 в 02:33, в сообщении № 5
Чем Михаил прессует?...

ВПРом

Думаю, что решения ~100 знаков у всех схожее
СУММ/СМЕЩ/ПОИСКПОЗ/СУММЕСЛИ/СМЕЩ/СТРОКА
 
Ответить
СообщениеБудут еще желающие, а то до понедельника ждать долго и интерес к задаче пропадает?
Цитата vikttur, 05.05.2014 в 02:33, в сообщении № 5
Чем Михаил прессует?...

ВПРом

Думаю, что решения ~100 знаков у всех схожее
СУММ/СМЕЩ/ПОИСКПОЗ/СУММЕСЛИ/СМЕЩ/СТРОКА
Автор - MCH
Дата добавления - 07.05.2014 в 12:00
AlexM Дата: Среда, 07.05.2014, 14:59 | Сообщение № 8
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4517
Репутация: 1129 ±
Замечаний: 0% ±

Excel 2003
Цитата MCH, 07.05.2014 в 12:00, в сообщении № 7
Будут еще желающие
Я не знаю как это определить.

Цитата MCH, 07.05.2014 в 12:00, в сообщении № 7
СУММ/СМЕЩ/ПОИСКПОЗ/СУММЕСЛИ/СМЕЩ/СТРОКА
Типа подсказка.
У меня тогда так
СУММ/ПРОМЕЖУТОЧНЫЕ.ИТОГИ/СТРОКА/СУММ

В задаче суммы превышают или равны ограничению.
Если меньше ограничения формула получается короче - 77 символов.
Но это уже другая задача.


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
Сообщение
Цитата MCH, 07.05.2014 в 12:00, в сообщении № 7
Будут еще желающие
Я не знаю как это определить.

Цитата MCH, 07.05.2014 в 12:00, в сообщении № 7
СУММ/СМЕЩ/ПОИСКПОЗ/СУММЕСЛИ/СМЕЩ/СТРОКА
Типа подсказка.
У меня тогда так
СУММ/ПРОМЕЖУТОЧНЫЕ.ИТОГИ/СТРОКА/СУММ

В задаче суммы превышают или равны ограничению.
Если меньше ограничения формула получается короче - 77 символов.
Но это уже другая задача.
Автор - AlexM
Дата добавления - 07.05.2014 в 14:59
MCH Дата: Среда, 07.05.2014, 17:39 | Сообщение № 9
Группа: Админы
Ранг: Старожил
Сообщений: 2004
Репутация: 752 ±
Замечаний: ±

Цитата AlexM, 07.05.2014 в 14:59, в сообщении № 8
В задаче суммы превышают или равны ограничению

Я решал когда превышают, т.к. в условиях было указано ">70" а не ">=70"
Цитата AlexM, 07.05.2014 в 14:59, в сообщении № 8
У меня тогда так
СУММ/ПРОМЕЖУТОЧНЫЕ.ИТОГИ/СТРОКА/СУММ

а СМЕЩ при этом нет?
 
Ответить
Сообщение
Цитата AlexM, 07.05.2014 в 14:59, в сообщении № 8
В задаче суммы превышают или равны ограничению

Я решал когда превышают, т.к. в условиях было указано ">70" а не ">=70"
Цитата AlexM, 07.05.2014 в 14:59, в сообщении № 8
У меня тогда так
СУММ/ПРОМЕЖУТОЧНЫЕ.ИТОГИ/СТРОКА/СУММ

а СМЕЩ при этом нет?
Автор - MCH
Дата добавления - 07.05.2014 в 17:39
AlexM Дата: Среда, 07.05.2014, 19:25 | Сообщение № 10
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4517
Репутация: 1129 ±
Замечаний: 0% ±

Excel 2003
Цитата MCH, 07.05.2014 в 17:39, в сообщении № 9
Я решал когда превышают
Описался, и я так же.
Цитата MCH, 07.05.2014 в 17:39, в сообщении № 9
СМЕЩ при этом нет
Есть. Не хотел все показывать. ;)


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
Сообщение
Цитата MCH, 07.05.2014 в 17:39, в сообщении № 9
Я решал когда превышают
Описался, и я так же.
Цитата MCH, 07.05.2014 в 17:39, в сообщении № 9
СМЕЩ при этом нет
Есть. Не хотел все показывать. ;)
Автор - AlexM
Дата добавления - 07.05.2014 в 19:25
ZORRO2005 Дата: Среда, 07.05.2014, 19:29 | Сообщение № 11
Группа: Друзья
Ранг: Обитатель
Сообщений: 382
Репутация: 148 ±
Замечаний: 0% ±

Excel2010
Вскрываюсь:
Код
=-СУММ(-СМЕЩ(A$2;;;ПОИСКПОЗ(СУММ(B$2;C$1:C1);СУММЕСЛИ(СМЕЩ(A$2;;;СТРОКА(A$1:A$999));">0"))+1);C$1:C1)


Сообщение отредактировал ZORRO2005 - Среда, 07.05.2014, 19:30
 
Ответить
СообщениеВскрываюсь:
Код
=-СУММ(-СМЕЩ(A$2;;;ПОИСКПОЗ(СУММ(B$2;C$1:C1);СУММЕСЛИ(СМЕЩ(A$2;;;СТРОКА(A$1:A$999));">0"))+1);C$1:C1)
Автор - ZORRO2005
Дата добавления - 07.05.2014 в 19:29
AlexM Дата: Среда, 07.05.2014, 19:31 | Сообщение № 12
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4517
Репутация: 1129 ±
Замечаний: 0% ±

Excel 2003
ну и я
Код
=-СУММ(-(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;СМЕЩ(A$1;;;СТРОКА($1:$999)))-СУММ(C$1:C1)<B$2)*A$2:A$1000;C$1:C1)
Массивная, 95 знаков


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
Сообщениену и я
Код
=-СУММ(-(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;СМЕЩ(A$1;;;СТРОКА($1:$999)))-СУММ(C$1:C1)<B$2)*A$2:A$1000;C$1:C1)
Массивная, 95 знаков
Автор - AlexM
Дата добавления - 07.05.2014 в 19:31
MCH Дата: Среда, 07.05.2014, 20:34 | Сообщение № 13
Группа: Админы
Ранг: Старожил
Сообщений: 2004
Репутация: 752 ±
Замечаний: ±

Формула в 97, почти как у ZORRO2005
Код
=-СУММ(-СМЕЩ(A$2;;;ПОИСКПОЗ(СУММ(C$1:C1)+B$2;СУММЕСЛИ(СМЕЩ(A$1;;;СТРОКА($1:$999));"<>")));C$1:C1)

72:
Код
=ВПР(B$2;СУММЕСЛИ(СМЕЩ(A$1;{0;1};;СТРОКА($1:$999));"<>")-СУММ(C$1:C1);2)
 
Ответить
СообщениеФормула в 97, почти как у ZORRO2005
Код
=-СУММ(-СМЕЩ(A$2;;;ПОИСКПОЗ(СУММ(C$1:C1)+B$2;СУММЕСЛИ(СМЕЩ(A$1;;;СТРОКА($1:$999));"<>")));C$1:C1)

72:
Код
=ВПР(B$2;СУММЕСЛИ(СМЕЩ(A$1;{0;1};;СТРОКА($1:$999));"<>")-СУММ(C$1:C1);2)
Автор - MCH
Дата добавления - 07.05.2014 в 20:34
AlexM Дата: Четверг, 08.05.2014, 00:33 | Сообщение № 14
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4517
Репутация: 1129 ±
Замечаний: 0% ±

Excel 2003
Михаил, класс!!!
Заметил, если в твоей формуле в ВПР() использовать первый столбец, то суммы получатся меньше ограничения.


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
СообщениеМихаил, класс!!!
Заметил, если в твоей формуле в ВПР() использовать первый столбец, то суммы получатся меньше ограничения.
Автор - AlexM
Дата добавления - 08.05.2014 в 00:33
vikttur Дата: Четверг, 08.05.2014, 01:30 | Сообщение № 15
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2941
Репутация: 526 ±
Замечаний: 0% ±

МолодцЫ, мОлодцы.
А я как ни крутил, не дотянул. Не сообразил, как найти большее
Код
=ВПР(B$2;ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;СМЕЩ(A$1;;;СТРОКА($1:20);))-СУММ(C$1:C1);1)
 
Ответить
СообщениеМолодцЫ, мОлодцы.
А я как ни крутил, не дотянул. Не сообразил, как найти большее
Код
=ВПР(B$2;ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;СМЕЩ(A$1;;;СТРОКА($1:20);))-СУММ(C$1:C1);1)
Автор - vikttur
Дата добавления - 08.05.2014 в 01:30
AlexM Дата: Четверг, 08.05.2014, 10:23 | Сообщение № 16
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4517
Репутация: 1129 ±
Замечаний: 0% ±

Excel 2003
Обнаружил некорректную работу формул Михаила и Сергея.
В приложении файл, в котором есть все формулы из темы.
Увидеть ошибку можно так. В А4 вставить число 12, вместо 15.
К сообщению приложен файл: Sum_limit.xls (36.5 Kb)


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
СообщениеОбнаружил некорректную работу формул Михаила и Сергея.
В приложении файл, в котором есть все формулы из темы.
Увидеть ошибку можно так. В А4 вставить число 12, вместо 15.
Автор - AlexM
Дата добавления - 08.05.2014 в 10:23
MCH Дата: Четверг, 08.05.2014, 11:47 | Сообщение № 17
Группа: Админы
Ранг: Старожил
Сообщений: 2004
Репутация: 752 ±
Замечаний: ±

Ну так условие было ">70", а не ">=70", что и решают формулы (о чем я уже писал)
Но да, Алексей, исправить (в зависимости от условия) твою формулу значительно легче, чем формулы мою и Сергея (т.к. в них заложен одинаковый алгоритм).

PS: ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9 можно заменить на СУММЕСЛИ, что немного сократит формулу.
 
Ответить
СообщениеНу так условие было ">70", а не ">=70", что и решают формулы (о чем я уже писал)
Но да, Алексей, исправить (в зависимости от условия) твою формулу значительно легче, чем формулы мою и Сергея (т.к. в них заложен одинаковый алгоритм).

PS: ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9 можно заменить на СУММЕСЛИ, что немного сократит формулу.
Автор - MCH
Дата добавления - 08.05.2014 в 11:47
AlexM Дата: Четверг, 08.05.2014, 12:34 | Сообщение № 18
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4517
Репутация: 1129 ±
Замечаний: 0% ±

Excel 2003
Исправил свою формулу.
К сообщению приложен файл: Sum_limit_new.xls (40.0 Kb)


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
СообщениеИсправил свою формулу.
Автор - AlexM
Дата добавления - 08.05.2014 в 12:34
MCH Дата: Четверг, 08.05.2014, 22:06 | Сообщение № 19
Группа: Админы
Ранг: Старожил
Сообщений: 2004
Репутация: 752 ±
Замечаний: ±

Ограничение ">=" в моей формуле можно сделать так
Код
=ВПР(B$2;СУММЕСЛИ(СМЕЩ(A$1;{0;1};;СТРОКА($1:$999));"<>")-СУММ(C$1:C1)+{0,1;0};2)

Не совсем корректно, но для данной задачи (с целыми числами) подходит
 
Ответить
СообщениеОграничение ">=" в моей формуле можно сделать так
Код
=ВПР(B$2;СУММЕСЛИ(СМЕЩ(A$1;{0;1};;СТРОКА($1:$999));"<>")-СУММ(C$1:C1)+{0,1;0};2)

Не совсем корректно, но для данной задачи (с целыми числами) подходит
Автор - MCH
Дата добавления - 08.05.2014 в 22:06
Мир MS Excel » Работа и общение » Мозговой штурм » Суммы с ограничением по условию (Решение формулой)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!