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

Вход

Регистрация

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

 

= Мир MS Excel/Деление значений вертикального и горизонтального диапазонов - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Деление значений вертикального и горизонтального диапазонов (Формулы/Formulas)
Деление значений вертикального и горизонтального диапазонов
Lyova Дата: Среда, 13.03.2019, 20:18 | Сообщение № 1
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 137
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
Здравствуйте!

Во вложенном файле пример с подробным описанием того, что нужно.

Заранее огромное спасибо!
К сообщению приложен файл: 3027133.xlsx (10.4 Kb)
 
Ответить
СообщениеЗдравствуйте!

Во вложенном файле пример с подробным описанием того, что нужно.

Заранее огромное спасибо!

Автор - Lyova
Дата добавления - 13.03.2019 в 20:18
bmv98rus Дата: Среда, 13.03.2019, 20:33 | Сообщение № 2
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4099
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016
массивно и летуче
Код
=SUM(IFERROR(SUBTOTAL(4;OFFSET(E6:G6;ROW(A1:A8);))/TRANSPOSE(D3:K3);))
К сообщению приложен файл: Copy_of_721.xlsx (10.4 Kb)


Замечательный Временно просто медведь , процентов на 20.

Сообщение отредактировал bmv98rus - Среда, 13.03.2019, 20:35
 
Ответить
Сообщениемассивно и летуче
Код
=SUM(IFERROR(SUBTOTAL(4;OFFSET(E6:G6;ROW(A1:A8);))/TRANSPOSE(D3:K3);))

Автор - bmv98rus
Дата добавления - 13.03.2019 в 20:33
krosav4ig Дата: Среда, 13.03.2019, 20:47 | Сообщение № 3
Группа: Друзья
Ранг: Старожил
Сообщений: 2346
Репутация: 989 ±
Замечаний: 0% ±

Excel 2007,2010,2013
не летуче
Код
=СУММПРОИЗВ(ЕСЛИОШИБКА(ТРАНСП(ОСТАТ(НАИМЕНЬШИЙ(СТРОКА(E7:G14)/1%+E7:G14;ЧИСЛСТОЛБ(E7:G14)*(СТРОКА(E7:E14)-6));100))/D3:K3;))


email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460
 
Ответить
Сообщениене летуче
Код
=СУММПРОИЗВ(ЕСЛИОШИБКА(ТРАНСП(ОСТАТ(НАИМЕНЬШИЙ(СТРОКА(E7:G14)/1%+E7:G14;ЧИСЛСТОЛБ(E7:G14)*(СТРОКА(E7:E14)-6));100))/D3:K3;))

Автор - krosav4ig
Дата добавления - 13.03.2019 в 20:47
Lyova Дата: Среда, 13.03.2019, 21:06 | Сообщение № 4
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 137
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
bmv98rus, Спасибо!
 
Ответить
Сообщениеbmv98rus, Спасибо!

Автор - Lyova
Дата добавления - 13.03.2019 в 21:06
Lyova Дата: Среда, 13.03.2019, 21:06 | Сообщение № 5
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 137
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
krosav4ig, Спасибо!
 
Ответить
Сообщениеkrosav4ig, Спасибо!

Автор - Lyova
Дата добавления - 13.03.2019 в 21:06
bmv98rus Дата: Среда, 13.03.2019, 21:09 | Сообщение № 6
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4099
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016
krosav4ig, Андрей, про ограничение забыли написать. Про целые числа.


Замечательный Временно просто медведь , процентов на 20.
 
Ответить
Сообщениеkrosav4ig, Андрей, про ограничение забыли написать. Про целые числа.

Автор - bmv98rus
Дата добавления - 13.03.2019 в 21:09
Lyova Дата: Вторник, 19.03.2019, 13:02 | Сообщение № 7
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 137
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
bmv98rus, Здравствуйте! Подскажите, пожалуйста, нельзя ли в Вашей формуле заменить ПРОМЕЖУТОЧНЫЕ.ИТОГИ на что-то другое. Дело в том, что результаты расчетов по Вашей формуле находятся в строках к которым применяется фильтр и необходимо считать сумму отфильтрованных строк с помощью той же ПРОМЕЖУТОЧНЫЕ.ИТОГИ, но она их не считает, потому что в суммируемых ячейках находится формулы ПРОМЕЖУТОЧНЫЕ.ИТОГИ

Заранее Спасибо!
 
Ответить
Сообщениеbmv98rus, Здравствуйте! Подскажите, пожалуйста, нельзя ли в Вашей формуле заменить ПРОМЕЖУТОЧНЫЕ.ИТОГИ на что-то другое. Дело в том, что результаты расчетов по Вашей формуле находятся в строках к которым применяется фильтр и необходимо считать сумму отфильтрованных строк с помощью той же ПРОМЕЖУТОЧНЫЕ.ИТОГИ, но она их не считает, потому что в суммируемых ячейках находится формулы ПРОМЕЖУТОЧНЫЕ.ИТОГИ

Заранее Спасибо!

Автор - Lyova
Дата добавления - 19.03.2019 в 13:02
Lyova Дата: Вторник, 19.03.2019, 19:51 | Сообщение № 8
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 137
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
bmv98rus, ?
 
Ответить
Сообщениеbmv98rus, ?

Автор - Lyova
Дата добавления - 19.03.2019 в 19:51
bmv98rus Дата: Среда, 20.03.2019, 00:36 | Сообщение № 9
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4099
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016
Lyova, ну а решение krosav4ig, ?
Код
=ROUND(SUM(IFERROR(MOD(LARGE(ROW(E7:G14)*10^6+E7:G14;(ROWS(D7:D14)-ROW(C1:C8))*3+1)*10^-6;1)*10^6/TRANSPOSE(D3:K3);0));6)
К сообщению приложен файл: 4801886.xlsx (10.6 Kb)


Замечательный Временно просто медведь , процентов на 20.

Сообщение отредактировал bmv98rus - Среда, 20.03.2019, 00:59
 
Ответить
СообщениеLyova, ну а решение krosav4ig, ?
Код
=ROUND(SUM(IFERROR(MOD(LARGE(ROW(E7:G14)*10^6+E7:G14;(ROWS(D7:D14)-ROW(C1:C8))*3+1)*10^-6;1)*10^6/TRANSPOSE(D3:K3);0));6)

Автор - bmv98rus
Дата добавления - 20.03.2019 в 00:36
Lyova Дата: Среда, 20.03.2019, 01:27 | Сообщение № 10
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 137
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
bmv98rus, Спасибо!

Решение krosav4ig подходит тоже, но оно получается более громоздким (по сравнению с Вашим первым вариантом). Дело в том, что диапазон E7:G14 в оригинальном файле определяется путем смещения в зависимости от разных условий и в результате формула получается "трехкилометровой". В Вашем первом варианте диапазон всего один, поэтому она получается менее громоздкой, но проблема в ПРОМЕЖУТОЧНЫЕ.ИТОГИ ()
 
Ответить
Сообщениеbmv98rus, Спасибо!

Решение krosav4ig подходит тоже, но оно получается более громоздким (по сравнению с Вашим первым вариантом). Дело в том, что диапазон E7:G14 в оригинальном файле определяется путем смещения в зависимости от разных условий и в результате формула получается "трехкилометровой". В Вашем первом варианте диапазон всего один, поэтому она получается менее громоздкой, но проблема в ПРОМЕЖУТОЧНЫЕ.ИТОГИ ()

Автор - Lyova
Дата добавления - 20.03.2019 в 01:27
bmv98rus Дата: Среда, 20.03.2019, 07:29 | Сообщение № 11
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4099
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016
Lyova, так может надо решать не середину задачи, а от и до? Размер формулы можно спрятать в имена, будет компактно, и если факт использования ПРОМЕЖУТОЧНЫЕ.ИТОГИ () понятен, то что там за
E7:G14 в оригинальном файле определяется путем смещения в зависимости от разных условий
не понятно. Может надо менять подход.
ну и конечно использование доп. столбцов и прочего порой не только делают проще формулы , но и делают вычисления в целом Более оптимальными.
Файл с более близким к реальности примером покажите.


Замечательный Временно просто медведь , процентов на 20.
 
Ответить
СообщениеLyova, так может надо решать не середину задачи, а от и до? Размер формулы можно спрятать в имена, будет компактно, и если факт использования ПРОМЕЖУТОЧНЫЕ.ИТОГИ () понятен, то что там за
E7:G14 в оригинальном файле определяется путем смещения в зависимости от разных условий
не понятно. Может надо менять подход.
ну и конечно использование доп. столбцов и прочего порой не только делают проще формулы , но и делают вычисления в целом Более оптимальными.
Файл с более близким к реальности примером покажите.

Автор - bmv98rus
Дата добавления - 20.03.2019 в 07:29
Светлый Дата: Среда, 20.03.2019, 10:31 | Сообщение № 12
Группа: Друзья
Ранг: Старожил
Сообщений: 1823
Репутация: 507 ±
Замечаний: 0% ±

Excel 2013, 2016
Для имеющихся в примере значений будет работать такая массивная формула:
Код
=МУМНОЖ(ЕСЛИОШИБКА(1/D3:K3;);ОТБР(LOG(МУМНОЖ(10^E7:G14;ТРАНСП(СТОЛБЕЦ(E:G)^0)))))
Естественно, у неё куча ограничений.
И ещё. Если будет включаться фильтр, то деление на горизонтальную строку будет затруднительно, потому что число строк будет изменяться, а число столбцов - нет.
*С такими же ограничениями:
Код
=СУММ(ЕСЛИОШИБКА(ОТБР(LOG(МУМНОЖ(10^E7:G14;ТРАНСП(СТОЛБЕЦ(E:G)^0))))/ТРАНСП(D3:K3);))
**Ещё вариант:
Код
=СУММ(ЕСЛИ(D3:K3;ТРАНСП(ОТБР(LOG(МУМНОЖ(10^E7:G14;ТРАНСП(СТОЛБЕЦ(E:G)^0)))))/D3:K3))
***Или первую можно заменить:
Код
=МУМНОЖ(ЕСЛИ(D3:K3;1/D3:K3;);ОТБР(LOG(МУМНОЖ(10^E7:G14;ТРАНСП(СТОЛБЕЦ(E:G)^0)))))


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

Сообщение отредактировал Светлый - Среда, 20.03.2019, 11:19
 
Ответить
СообщениеДля имеющихся в примере значений будет работать такая массивная формула:
Код
=МУМНОЖ(ЕСЛИОШИБКА(1/D3:K3;);ОТБР(LOG(МУМНОЖ(10^E7:G14;ТРАНСП(СТОЛБЕЦ(E:G)^0)))))
Естественно, у неё куча ограничений.
И ещё. Если будет включаться фильтр, то деление на горизонтальную строку будет затруднительно, потому что число строк будет изменяться, а число столбцов - нет.
*С такими же ограничениями:
Код
=СУММ(ЕСЛИОШИБКА(ОТБР(LOG(МУМНОЖ(10^E7:G14;ТРАНСП(СТОЛБЕЦ(E:G)^0))))/ТРАНСП(D3:K3);))
**Ещё вариант:
Код
=СУММ(ЕСЛИ(D3:K3;ТРАНСП(ОТБР(LOG(МУМНОЖ(10^E7:G14;ТРАНСП(СТОЛБЕЦ(E:G)^0)))))/D3:K3))
***Или первую можно заменить:
Код
=МУМНОЖ(ЕСЛИ(D3:K3;1/D3:K3;);ОТБР(LOG(МУМНОЖ(10^E7:G14;ТРАНСП(СТОЛБЕЦ(E:G)^0)))))

Автор - Светлый
Дата добавления - 20.03.2019 в 10:31
bmv98rus Дата: Среда, 20.03.2019, 12:28 | Сообщение № 13
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4099
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016
Цитата Светлый, 20.03.2019 в 10:31, в сообщении № 12 ()
Естественно, у неё куча ограничений

по этому и написал
Может надо менять подход.

Может оказаться что бубен лишний. ну и конечно если это только часть и объем данных больше , то вложенные MMULT - не самый быстрый метод.


Замечательный Временно просто медведь , процентов на 20.
 
Ответить
Сообщение
Цитата Светлый, 20.03.2019 в 10:31, в сообщении № 12 ()
Естественно, у неё куча ограничений

по этому и написал
Может надо менять подход.

Может оказаться что бубен лишний. ну и конечно если это только часть и объем данных больше , то вложенные MMULT - не самый быстрый метод.

Автор - bmv98rus
Дата добавления - 20.03.2019 в 12:28
Lyova Дата: Среда, 20.03.2019, 13:35 | Сообщение № 14
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 137
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
bmv98rus, Сделал пример, максимально приближенный к реальному файлу. Сделал расчет по формуле, предложенной krosav4ig. Вашу формулу, к сожалению, не смог адаптировать. Понимаю, что значение "6" - это порядковый номер строки над массивом данных, но не понимаю, что такое "10" и "3". Предполагаю, что тоже связано со строками, но не понимаю логику.
К сообщению приложен файл: 2437938.xlsx (18.6 Kb)
 
Ответить
Сообщениеbmv98rus, Сделал пример, максимально приближенный к реальному файлу. Сделал расчет по формуле, предложенной krosav4ig. Вашу формулу, к сожалению, не смог адаптировать. Понимаю, что значение "6" - это порядковый номер строки над массивом данных, но не понимаю, что такое "10" и "3". Предполагаю, что тоже связано со строками, но не понимаю логику.

Автор - Lyova
Дата добавления - 20.03.2019 в 13:35
Lyova Дата: Среда, 20.03.2019, 13:41 | Сообщение № 15
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 137
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
Светлый, Спасибо за ответ! Правильно ли я понимаю, что предложенные Вами варианты не применимы, если будет сортировка?
 
Ответить
СообщениеСветлый, Спасибо за ответ! Правильно ли я понимаю, что предложенные Вами варианты не применимы, если будет сортировка?

Автор - Lyova
Дата добавления - 20.03.2019 в 13:41
bmv98rus Дата: Среда, 20.03.2019, 17:08 | Сообщение № 16
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4099
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016
ну если говорить о соответствии проектов , то это можно обойти, но формула проще не станет.TRANSPSE(D3:K3) заменится на HLOOKUP( ….. )


Замечательный Временно просто медведь , процентов на 20.
 
Ответить
Сообщениену если говорить о соответствии проектов , то это можно обойти, но формула проще не станет.TRANSPSE(D3:K3) заменится на HLOOKUP( ….. )

Автор - bmv98rus
Дата добавления - 20.03.2019 в 17:08
Светлый Дата: Среда, 20.03.2019, 19:31 | Сообщение № 17
Группа: Друзья
Ранг: Старожил
Сообщений: 1823
Репутация: 507 ±
Замечаний: 0% ±

Excel 2013, 2016
варианты не применимы, если будет сортировка?
Не понял вопроса. Причём тут сортировка? Я писал
Цитата Светлый, 20.03.2019 в 10:31, в сообщении № 12 ()
Если будет включаться фильтр
То есть, если строки будут скрыты. Даже при этом будет работать, но в сумму будут входить скрытые строки. А сортировать можно сколько угодно.


Программировать проще, чем писать стихи.
 
Ответить
Сообщение
варианты не применимы, если будет сортировка?
Не понял вопроса. Причём тут сортировка? Я писал
Цитата Светлый, 20.03.2019 в 10:31, в сообщении № 12 ()
Если будет включаться фильтр
То есть, если строки будут скрыты. Даже при этом будет работать, но в сумму будут входить скрытые строки. А сортировать можно сколько угодно.

Автор - Светлый
Дата добавления - 20.03.2019 в 19:31
Lyova Дата: Среда, 20.03.2019, 19:38 | Сообщение № 18
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 137
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
Светлый, Извиняюсь, не правильно выразился. Под "сортировкой" я подразумевал "фильтр".
 
Ответить
СообщениеСветлый, Извиняюсь, не правильно выразился. Под "сортировкой" я подразумевал "фильтр".

Автор - Lyova
Дата добавления - 20.03.2019 в 19:38
Lyova Дата: Среда, 20.03.2019, 19:39 | Сообщение № 19
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 137
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
bmv98rus, ОК, понял, спасибо!
 
Ответить
Сообщениеbmv98rus, ОК, понял, спасибо!

Автор - Lyova
Дата добавления - 20.03.2019 в 19:39
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Деление значений вертикального и горизонтального диапазонов (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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