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

Вход

Регистрация

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

 

= Мир MS Excel/Сумма по последним значениям - Страница 2 - Мир MS Excel

Старая форма входа
  • Страница 2 из 2
  • «
  • 1
  • 2
Модератор форума: китин  
Мир MS Excel » Работа и общение » Мозговой штурм » Сумма по последним значениям
Сумма по последним значениям
ZORRO2005 Дата: Воскресенье, 23.09.2012, 13:58 | Сообщение № 21
Группа: Друзья
Ранг: Обитатель
Сообщений: 382
Репутация: 148 ±
Замечаний: 0% ±

Excel2010
Миша, задача хорошая!
Дай еще время подумать.
Пустышки в B6,B7, C9, D9 все усложняют, а формула ниже требует дополнительных ресурсов:
Code
=СУММ(НАИБОЛЬШИЙ(СМЕЩ($B1:E1;СТРОКА(A2:A9)-1;);1))

Интересно, только у меня?
 
Ответить
СообщениеМиша, задача хорошая!
Дай еще время подумать.
Пустышки в B6,B7, C9, D9 все усложняют, а формула ниже требует дополнительных ресурсов:
Code
=СУММ(НАИБОЛЬШИЙ(СМЕЩ($B1:E1;СТРОКА(A2:A9)-1;);1))

Интересно, только у меня?
Автор - ZORRO2005
Дата добавления - 23.09.2012 в 13:58
kim Дата: Воскресенье, 23.09.2012, 16:52 | Сообщение № 22
Группа: Друзья
Ранг: Обитатель
Сообщений: 371
Репутация: 160 ±
Замечаний: 0% ±

Excel 2010
Без "=" 110
На этом пока приторможу
Файл переложил, пароль - когда начнем вскрываться.
К сообщению приложен файл: SumPosl2.xlsx (18.0 Kb)

Сцяна - гэта тая ж прыступка. Проста трэба дарасьці да яе.
 
Ответить
СообщениеБез "=" 110
На этом пока приторможу
Файл переложил, пароль - когда начнем вскрываться.
Автор - kim
Дата добавления - 23.09.2012 в 16:52
DV Дата: Воскресенье, 23.09.2012, 18:30 | Сообщение № 23
Группа: Друзья
Ранг: Обитатель
Сообщений: 286
Репутация: 194 ±
Замечаний: 0% ±

Excel 2010 RUS
У меня 128 с "="


Сообщение отредактировал DV - Воскресенье, 23.09.2012, 19:22
 
Ответить
СообщениеУ меня 128 с "="
Автор - DV
Дата добавления - 23.09.2012 в 18:30
MCH Дата: Понедельник, 24.09.2012, 01:59 | Сообщение № 24
Группа: Админы
Ранг: Старожил
Сообщений: 2004
Репутация: 752 ±
Замечаний: ±

Quote (kim)
Без "=" 54 символа
...
в мои расчеты закралась серьезная ошибка
...
данные располагались по возрастанию

Судя по всему это формула:
Code
=СУММ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(4;СМЕЩ($B1:B1;СТРОКА(1:8);)))

Но она не верная, т.к. расчитана на числа расположенные по возрастанию

у меня - 149, "оптимизированная" - 105
 
Ответить
Сообщение
Quote (kim)
Без "=" 54 символа
...
в мои расчеты закралась серьезная ошибка
...
данные располагались по возрастанию

Судя по всему это формула:
Code
=СУММ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(4;СМЕЩ($B1:B1;СТРОКА(1:8);)))

Но она не верная, т.к. расчитана на числа расположенные по возрастанию

у меня - 149, "оптимизированная" - 105
Автор - MCH
Дата добавления - 24.09.2012 в 01:59
MCH Дата: Понедельник, 24.09.2012, 13:18 | Сообщение № 25
Группа: Админы
Ранг: Старожил
Сообщений: 2004
Репутация: 752 ±
Замечаний: ±

Я тут на Планете случайно узнал, что АГРЕГАТ может работать с массивами, а не только с ссылками, возможно ее можно прикрутить к задаче, и формула должна получиться меньше, жаль что 2010 у меня нигде нет, проверить не могу.
 
Ответить
СообщениеЯ тут на Планете случайно узнал, что АГРЕГАТ может работать с массивами, а не только с ссылками, возможно ее можно прикрутить к задаче, и формула должна получиться меньше, жаль что 2010 у меня нигде нет, проверить не могу.
Автор - MCH
Дата добавления - 24.09.2012 в 13:18
vikttur Дата: Вторник, 25.09.2012, 10:34 | Сообщение № 26
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2941
Репутация: 526 ±
Замечаний: 0% ±

Меня не ждите. Уже неделю как очень загружен.
Скоро перегружусь smile
 
Ответить
СообщениеМеня не ждите. Уже неделю как очень загружен.
Скоро перегружусь smile
Автор - vikttur
Дата добавления - 25.09.2012 в 10:34
ZORRO2005 Дата: Вторник, 25.09.2012, 11:19 | Сообщение № 27
Группа: Друзья
Ранг: Обитатель
Сообщений: 382
Репутация: 148 ±
Замечаний: 0% ±

Excel2010
Вскрывайтесь! Я пас.
 
Ответить
СообщениеВскрывайтесь! Я пас.
Автор - ZORRO2005
Дата добавления - 25.09.2012 в 11:19
MCH Дата: Вторник, 25.09.2012, 11:24 | Сообщение № 28
Группа: Админы
Ранг: Старожил
Сообщений: 2004
Репутация: 752 ±
Замечаний: ±

Собственно мое решение
149:
Code
=СУММ(Ч(СМЕЩ($B2;СТРОКА($B2:B9)-СТРОКА($B2:B2);ABS(LOG(МУМНОЖ(($B2:B9<>"")*2^(СТОЛБЕЦ($B2:B9)-СТОЛБЕЦ($B2:$B9));ТРАНСП(СТОЛБЕЦ($B2:B9)^0))+3/4;2)))))


и с "оптимизацией" - 105:
Code
=СУММ(Ч(СМЕЩ($B1;СТРОКА(1:8);LOG(МУМНОЖ(($B2:B9<>"")*2^СТОЛБЕЦ($B:B)/4;ТРАНСП(СТОЛБЕЦ($B:B)^0))+3/4;2))))
К сообщению приложен файл: SumPosl-.xls (30.5 Kb)
 
Ответить
СообщениеСобственно мое решение
149:
Code
=СУММ(Ч(СМЕЩ($B2;СТРОКА($B2:B9)-СТРОКА($B2:B2);ABS(LOG(МУМНОЖ(($B2:B9<>"")*2^(СТОЛБЕЦ($B2:B9)-СТОЛБЕЦ($B2:$B9));ТРАНСП(СТОЛБЕЦ($B2:B9)^0))+3/4;2)))))


и с "оптимизацией" - 105:
Code
=СУММ(Ч(СМЕЩ($B1;СТРОКА(1:8);LOG(МУМНОЖ(($B2:B9<>"")*2^СТОЛБЕЦ($B:B)/4;ТРАНСП(СТОЛБЕЦ($B:B)^0))+3/4;2))))
Автор - MCH
Дата добавления - 25.09.2012 в 11:24
kim Дата: Вторник, 25.09.2012, 19:33 | Сообщение № 29
Группа: Друзья
Ранг: Обитатель
Сообщений: 371
Репутация: 160 ±
Замечаний: 0% ±

Excel 2010
У меня что-то наподобие, пароль "111kim111" без кавычек.

Сцяна - гэта тая ж прыступка. Проста трэба дарасьці да яе.
 
Ответить
СообщениеУ меня что-то наподобие, пароль "111kim111" без кавычек.
Автор - kim
Дата добавления - 25.09.2012 в 19:33
MCH Дата: Вторник, 25.09.2012, 19:58 | Сообщение № 30
Группа: Админы
Ранг: Старожил
Сообщений: 2004
Репутация: 752 ±
Замечаний: ±

Quote (kim)
У меня что-то наподобие, пароль "111kim111" без кавычек.

Игорь, выложи незапороленный файл, или напиши свою формулу, а то я не могу в 2003 открыть
 
Ответить
Сообщение
Quote (kim)
У меня что-то наподобие, пароль "111kim111" без кавычек.

Игорь, выложи незапороленный файл, или напиши свою формулу, а то я не могу в 2003 открыть
Автор - MCH
Дата добавления - 25.09.2012 в 19:58
kim Дата: Вторник, 25.09.2012, 20:05 | Сообщение № 31
Группа: Друзья
Ранг: Обитатель
Сообщений: 371
Репутация: 160 ±
Замечаний: 0% ±

Excel 2010
Вот формула:
[vba]
Code
=СУММ(Ч(СМЕЩ($A1;СТРОКА(1:9);ABS(ДЛСТР(МУМНОЖ(ЕЧИСЛО($A2:B10)*10^СТОЛБЕЦ($A:B);ТРАНСП(СТОЛБЕЦ($A:B)^0)))-2);)))
[/vba]
АГРЕГАТ() заюзать пока не получилось. С аргументами 14-наибольший и 15-наименьший действительно работает с массивами, но нужный массив возвращать отказывается...
P.S. Миша, я бы все-таки поправил твою формулу, не с целью увеличения количества символов, но универсальности токма ради smile - вдруг текст какой в диапазон затешется. Удлинится то всего на пару символов...
Формулу положил в личную копилку. В своем старом файле (2009г) тоже поправил smile

Сцяна - гэта тая ж прыступка. Проста трэба дарасьці да яе.
 
Ответить
СообщениеВот формула:
[vba]
Code
=СУММ(Ч(СМЕЩ($A1;СТРОКА(1:9);ABS(ДЛСТР(МУМНОЖ(ЕЧИСЛО($A2:B10)*10^СТОЛБЕЦ($A:B);ТРАНСП(СТОЛБЕЦ($A:B)^0)))-2);)))
[/vba]
АГРЕГАТ() заюзать пока не получилось. С аргументами 14-наибольший и 15-наименьший действительно работает с массивами, но нужный массив возвращать отказывается...
P.S. Миша, я бы все-таки поправил твою формулу, не с целью увеличения количества символов, но универсальности токма ради smile - вдруг текст какой в диапазон затешется. Удлинится то всего на пару символов...
Формулу положил в личную копилку. В своем старом файле (2009г) тоже поправил smile
Автор - kim
Дата добавления - 25.09.2012 в 20:05
MCH Дата: Среда, 26.09.2012, 07:26 | Сообщение № 32
Группа: Админы
Ранг: Старожил
Сообщений: 2004
Репутация: 752 ±
Замечаний: ±

Quote (kim)
Миша, я бы все-таки поправил твою формулу

Кто же спорит, но в погоне за символы, все методы хороши.
Для чистоты можно еще в первой моей формуле ABS заменить на ОТБР, дабы не СМЕЩаться на дробные величины, при этом ОТБР(LOG(3/4;2)) даст нужное нулевое значение, если в строке нет чисел, но и с ABS будет работать нормально

PS: немного потестировал функции:
=ДЛСТР(10^СТОЛБЕЦ()) - даст сбой уже на 20 столбце
нужно подставлять костыль - =ДЛСТР(ТЕКСТ(10^СТОЛБЕЦ();0)) - будет работать до 253 столбца

=LOG10(10^СТОЛБЕЦ()) - сбоит на 309 столбце
=LOG(2^СТОЛБЕЦ();2) - сбоит на 1024 столбце

Думаю, что степень двойки самое оптимальное решение

Интересно, что там у Сергея и Дмитрия припасено?


Сообщение отредактировал MCH - Среда, 26.09.2012, 10:40
 
Ответить
Сообщение
Quote (kim)
Миша, я бы все-таки поправил твою формулу

Кто же спорит, но в погоне за символы, все методы хороши.
Для чистоты можно еще в первой моей формуле ABS заменить на ОТБР, дабы не СМЕЩаться на дробные величины, при этом ОТБР(LOG(3/4;2)) даст нужное нулевое значение, если в строке нет чисел, но и с ABS будет работать нормально

PS: немного потестировал функции:
=ДЛСТР(10^СТОЛБЕЦ()) - даст сбой уже на 20 столбце
нужно подставлять костыль - =ДЛСТР(ТЕКСТ(10^СТОЛБЕЦ();0)) - будет работать до 253 столбца

=LOG10(10^СТОЛБЕЦ()) - сбоит на 309 столбце
=LOG(2^СТОЛБЕЦ();2) - сбоит на 1024 столбце

Думаю, что степень двойки самое оптимальное решение

Интересно, что там у Сергея и Дмитрия припасено?
Автор - MCH
Дата добавления - 26.09.2012 в 07:26
DV Дата: Среда, 26.09.2012, 15:52 | Сообщение № 33
Группа: Друзья
Ранг: Обитатель
Сообщений: 286
Репутация: 194 ±
Замечаний: 0% ±

Excel 2010 RUS
У меня так:
Code
=СУММ(Ч(СМЕЩ(B1;СТРОКА(1:8);ПОИСКПОЗ(МУМНОЖ(--($B2:B9<>"");ТРАНСП(10^(СТОЛБЕЦ($A:A)-1)));{0:1:10:100:1000:10000})-СТОЛБЕЦ(B1))))
 
Ответить
СообщениеУ меня так:
Code
=СУММ(Ч(СМЕЩ(B1;СТРОКА(1:8);ПОИСКПОЗ(МУМНОЖ(--($B2:B9<>"");ТРАНСП(10^(СТОЛБЕЦ($A:A)-1)));{0:1:10:100:1000:10000})-СТОЛБЕЦ(B1))))
Автор - DV
Дата добавления - 26.09.2012 в 15:52
MCH Дата: Среда, 26.09.2012, 18:11 | Сообщение № 34
Группа: Админы
Ранг: Старожил
Сообщений: 2004
Репутация: 752 ±
Замечаний: ±

Quote (DV)
У меня так:

Формула в том же направлении, а вот подсмотренная у Дмитрия мысль позволяет ужать мою формулу до 89 знаков (все таки не достаточно хорошо я владею МУМНОЖ):
Code
=СУММ(Ч(СМЕЩ($B1;СТРОКА(1:8);LOG(МУМНОЖ(($B2:B9<>"")/4;ТРАНСП(2^СТОЛБЕЦ($B:B)))+3/4;2))))


или в универсальном исполнении:
Code
=СУММ(Ч(СМЕЩ($B2;СТРОКА($B2:B9)-СТРОКА($B2:B2);ABS(LOG(МУМНОЖ(--ЕЧИСЛО($B2:B9);ТРАНСП(2^(СТОЛБЕЦ($B2:B9)-СТОЛБЕЦ($B2:$B9))))+3/4;2)))))


Сообщение отредактировал MCH - Среда, 26.09.2012, 18:18
 
Ответить
Сообщение
Quote (DV)
У меня так:

Формула в том же направлении, а вот подсмотренная у Дмитрия мысль позволяет ужать мою формулу до 89 знаков (все таки не достаточно хорошо я владею МУМНОЖ):
Code
=СУММ(Ч(СМЕЩ($B1;СТРОКА(1:8);LOG(МУМНОЖ(($B2:B9<>"")/4;ТРАНСП(2^СТОЛБЕЦ($B:B)))+3/4;2))))


или в универсальном исполнении:
Code
=СУММ(Ч(СМЕЩ($B2;СТРОКА($B2:B9)-СТРОКА($B2:B2);ABS(LOG(МУМНОЖ(--ЕЧИСЛО($B2:B9);ТРАНСП(2^(СТОЛБЕЦ($B2:B9)-СТОЛБЕЦ($B2:$B9))))+3/4;2)))))
Автор - MCH
Дата добавления - 26.09.2012 в 18:11
ZORRO2005 Дата: Четверг, 27.09.2012, 02:25 | Сообщение № 35
Группа: Друзья
Ранг: Обитатель
Сообщений: 382
Репутация: 148 ±
Замечаний: 0% ±

Excel2010
Ну и я докачал. smile
Code
=СУММ((СТРОКА($B2:B9)*10+СТОЛБЕЦ($B2:B9)*($B2:B9<>"")=НАИМЕНЬШИЙ(СТРОКА($B2:B9)*10+СТОЛБЕЦ($B2:B9)*($B2:B9<>"");(СТРОКА($B2:B9)-1)*СТОЛБЕЦ(A1)))*$B2:B9)
К сообщению приложен файл: SumPosl-Z.xls (29.5 Kb)
 
Ответить
СообщениеНу и я докачал. smile
Code
=СУММ((СТРОКА($B2:B9)*10+СТОЛБЕЦ($B2:B9)*($B2:B9<>"")=НАИМЕНЬШИЙ(СТРОКА($B2:B9)*10+СТОЛБЕЦ($B2:B9)*($B2:B9<>"");(СТРОКА($B2:B9)-1)*СТОЛБЕЦ(A1)))*$B2:B9)
Автор - ZORRO2005
Дата добавления - 27.09.2012 в 02:25
Светлый Дата: Вторник, 02.08.2016, 08:38 | Сообщение № 36
Группа: Друзья
Ранг: Старожил
Сообщений: 1851
Репутация: 523 ±
Замечаний: 0% ±

Excel 2013, 2016
К своему позору сам не смог построить универсальную формулу без ошибок. Делал формулу для последнего столбца и копировал в предыдущие. Всё хорошо считалось, но в тексте формул появлялась #ССЫЛКА! 95 символов:
Код
=СУММ(ЕСЛИ(F2:F9="";ЕСЛИ(E2:E9="";ЕСЛИ(D2:D9="";ЕСЛИ(C2:C9="";B2:B9;C2:C9);D2:D9);E2:E9);F2:F9))

Можно сократить до 86, но в первом столбце обязательно должны быть числовые значения:
Код
=СУММ(ЕСЛИ(F2:F9="";ЕСЛИ(E2:E9;E2:E9;ЕСЛИ(D2:D9;D2:D9;ЕСЛИ(C2:C9;C2:C9;B2:B9)));F2:F9))

Как всегда восхитился формулой MCH и "причесал" её до 84 символов:
Код
=СУММ(Ч(СМЕЩ($A1;СТРОКА(1:8);LOG(МУМНОЖ(Ч($B2:B9<>"");10^ТРАНСП(СТОЛБЕЦ($A:A)))+1))))

Если в Заголовке перед пустым значением окажется не текст, а число, в формуле надо +1 заменить на +10.
Формула по основанию 2 получилась на символ длиннее:
Код
=СУММ(Ч(СМЕЩ($A1;СТРОКА(1:8);LOG(МУМНОЖ(Ч($B2:B9<>"");2^ТРАНСП(СТОЛБЕЦ($A:A)))+1;2))))

Программировать проще, чем писать стихи.

Сообщение отредактировал Светлый - Вторник, 02.08.2016, 08:41
 
Ответить
СообщениеК своему позору сам не смог построить универсальную формулу без ошибок. Делал формулу для последнего столбца и копировал в предыдущие. Всё хорошо считалось, но в тексте формул появлялась #ССЫЛКА! 95 символов:
Код
=СУММ(ЕСЛИ(F2:F9="";ЕСЛИ(E2:E9="";ЕСЛИ(D2:D9="";ЕСЛИ(C2:C9="";B2:B9;C2:C9);D2:D9);E2:E9);F2:F9))

Можно сократить до 86, но в первом столбце обязательно должны быть числовые значения:
Код
=СУММ(ЕСЛИ(F2:F9="";ЕСЛИ(E2:E9;E2:E9;ЕСЛИ(D2:D9;D2:D9;ЕСЛИ(C2:C9;C2:C9;B2:B9)));F2:F9))

Как всегда восхитился формулой MCH и "причесал" её до 84 символов:
Код
=СУММ(Ч(СМЕЩ($A1;СТРОКА(1:8);LOG(МУМНОЖ(Ч($B2:B9<>"");10^ТРАНСП(СТОЛБЕЦ($A:A)))+1))))

Если в Заголовке перед пустым значением окажется не текст, а число, в формуле надо +1 заменить на +10.
Формула по основанию 2 получилась на символ длиннее:
Код
=СУММ(Ч(СМЕЩ($A1;СТРОКА(1:8);LOG(МУМНОЖ(Ч($B2:B9<>"");2^ТРАНСП(СТОЛБЕЦ($A:A)))+1;2))))
Автор - Светлый
Дата добавления - 02.08.2016 в 08:38
Мир MS Excel » Работа и общение » Мозговой штурм » Сумма по последним значениям
  • Страница 2 из 2
  • «
  • 1
  • 2
Поиск:

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