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

Вход

Регистрация

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

 

= Мир MS Excel/Стандартизация данных массивов - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, Manyasha, SLAVICK, китин  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Стандартизация данных массивов (Формулы/Formulas)
Стандартизация данных массивов
VR Дата: Вторник, 15.01.2019, 20:42 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 0 ±
Замечаний: 0% ±

Excel 2003
Приветствую Вас участники форума и гости читающие тему. Помогите пожалуйста решить задачу.

Даны массивы с четырьмя переменными. В данных массивах указаны интервалы по возрастанию (Start, End), разница или высота интервала (+/-), и его оценка (price).

Количество строк интервалов непостоянное и может меняться случайным образом, как и количество массивов. Необходимо привести в порядок всё это хозяйство под общий знаменатель с дискретностью 10 пунктов, для этого высота каждого "price" умножается на свой "Вес" и делится на сумму всех высот входящих в интервал (в нашем случае строго 10). Величина "Вес" может меняться, что также изменяет результаты всех предыдущих расчетов.

Подскажите пожалуйста, как можно автоматизировать данный процесс "стандартизации" (просто не знаю как назвать)?

В файле примере вручную посчитаны 20 пунктов, справа даны рисунки формул и значения весов для привязки к "price"


P.s.: первая тема, надеюсь правил не нарушил
К сообщению приложен файл: 0195239.xls(47.5 Kb)


Сообщение отредактировал VR - Вторник, 15.01.2019, 20:44
 
Ответить
СообщениеПриветствую Вас участники форума и гости читающие тему. Помогите пожалуйста решить задачу.

Даны массивы с четырьмя переменными. В данных массивах указаны интервалы по возрастанию (Start, End), разница или высота интервала (+/-), и его оценка (price).

Количество строк интервалов непостоянное и может меняться случайным образом, как и количество массивов. Необходимо привести в порядок всё это хозяйство под общий знаменатель с дискретностью 10 пунктов, для этого высота каждого "price" умножается на свой "Вес" и делится на сумму всех высот входящих в интервал (в нашем случае строго 10). Величина "Вес" может меняться, что также изменяет результаты всех предыдущих расчетов.

Подскажите пожалуйста, как можно автоматизировать данный процесс "стандартизации" (просто не знаю как назвать)?

В файле примере вручную посчитаны 20 пунктов, справа даны рисунки формул и значения весов для привязки к "price"


P.s.: первая тема, надеюсь правил не нарушил

Автор - VR
Дата добавления - 15.01.2019 в 20:42
jakim Дата: Вторник, 15.01.2019, 20:56 | Сообщение № 2
Группа: Друзья
Ранг: Ветеран
Сообщений: 890
Репутация: 232 ±
Замечаний: 0% ±

Excel 2010
Откуда Вы взяли данные для своей формулы

Код
=(2,4*1+6,9*2+0,7*3)/10
 
Ответить
Сообщение
Откуда Вы взяли данные для своей формулы

Код
=(2,4*1+6,9*2+0,7*3)/10

Автор - jakim
Дата добавления - 15.01.2019 в 20:56
bmv98rus Дата: Вторник, 15.01.2019, 21:16 | Сообщение № 3
Группа: Проверенные
Ранг: Старожил
Сообщений: 2023
Репутация: 320 ±
Замечаний: 20% ±

Excel 2013/2016
приблизительно так,
Код
=(SUMIFS($D$6:$D$42;$E$6:$E$42;"плохой";$B$6:$B$42;">="&$Q6-10;$C$6:$C$42;"<"&$Q6)+SUMIFS($D$6:$D$42;$E$6:$E$42;"частичный";$B$6:$B$42;">="&$Q6-10;$C$6:$C$42;"<"&$Q6)*2+SUMIFS($D$6:$D$42;$E$6:$E$42;"сплошной";$B$6:$B$42;">="&$Q6-10;$C$6:$C$42;"<"&$Q6)*3)/10

но если б вы пояснили почему взяты такие цифры в примере расчета Price10 и 20, а то я не понимаю от куда 6,9 и 0,9
К сообщению приложен файл: Copy_of_574.xlsx(25.8 Kb)


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

Сообщение отредактировал bmv98rus - Вторник, 15.01.2019, 21:17
 
Ответить
Сообщениеприблизительно так,
Код
=(SUMIFS($D$6:$D$42;$E$6:$E$42;"плохой";$B$6:$B$42;">="&$Q6-10;$C$6:$C$42;"<"&$Q6)+SUMIFS($D$6:$D$42;$E$6:$E$42;"частичный";$B$6:$B$42;">="&$Q6-10;$C$6:$C$42;"<"&$Q6)*2+SUMIFS($D$6:$D$42;$E$6:$E$42;"сплошной";$B$6:$B$42;">="&$Q6-10;$C$6:$C$42;"<"&$Q6)*3)/10

но если б вы пояснили почему взяты такие цифры в примере расчета Price10 и 20, а то я не понимаю от куда 6,9 и 0,9

Автор - bmv98rus
Дата добавления - 15.01.2019 в 21:16
VR Дата: Среда, 16.01.2019, 07:08 | Сообщение № 4
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 0 ±
Замечаний: 0% ±

Excel 2003
Откуда Вы взяли данные для своей формулы

=(2,4*1+6,9*2+0,7*3)/10


Берём первый массив. Нас интересуют первые 10 пунктов.
Ближайшее значение в ячейке С8, но там "10,1", получается 0,1 уйдет в следующий расчет с 10 до 20 пунктов. В выделенном интервале присутствуют: "плохой" высотой 2,4 (умножаем на его вес "1), "частичный" высотой 6,9 (почему не 7? потому что хвост 0,1 уйдет в следующий расчёт), и наконец 0,7 "сплошной".

Колонка "+/-" как раз дана чтобы видеть высоту интервала. Там и видно в первом массиве, 0,7; 2,4; 7 (для десятки 6,9).

Проверка 0,7+2,4+6,9=10

Результат расчета пишется в R6

0,1 который мы "отрезали" уходит в следующий расчёт.

но если б вы пояснили почему взяты такие цифры в примере расчета Price10 и 20, а то я не понимаю от куда 6,9 и 0,9


Берём вторую десятку
Там последнее значение 20,4 в ячейке С11. Последние 0,4 срезаются и идут в следующий расчёт. В итоге высота интервала для расчета не 1,2 (ячейка D11) а 0,8 (1,2-0,4=0,8).

Теперь рассчитываем R7 (0,9*2+9,1*3)/10
где 0,9 - интервал "частичный". Как получили: 0,1 с предыдущего интервала + 0,8 с того что нашли выше.
9,1 - интервал "сплошной" складывается из двух высот 7+2,1 (D9 и D10).

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


Сообщение отредактировал VR - Среда, 16.01.2019, 07:20
 
Ответить
Сообщение
Откуда Вы взяли данные для своей формулы

=(2,4*1+6,9*2+0,7*3)/10


Берём первый массив. Нас интересуют первые 10 пунктов.
Ближайшее значение в ячейке С8, но там "10,1", получается 0,1 уйдет в следующий расчет с 10 до 20 пунктов. В выделенном интервале присутствуют: "плохой" высотой 2,4 (умножаем на его вес "1), "частичный" высотой 6,9 (почему не 7? потому что хвост 0,1 уйдет в следующий расчёт), и наконец 0,7 "сплошной".

Колонка "+/-" как раз дана чтобы видеть высоту интервала. Там и видно в первом массиве, 0,7; 2,4; 7 (для десятки 6,9).

Проверка 0,7+2,4+6,9=10

Результат расчета пишется в R6

0,1 который мы "отрезали" уходит в следующий расчёт.

но если б вы пояснили почему взяты такие цифры в примере расчета Price10 и 20, а то я не понимаю от куда 6,9 и 0,9


Берём вторую десятку
Там последнее значение 20,4 в ячейке С11. Последние 0,4 срезаются и идут в следующий расчёт. В итоге высота интервала для расчета не 1,2 (ячейка D11) а 0,8 (1,2-0,4=0,8).

Теперь рассчитываем R7 (0,9*2+9,1*3)/10
где 0,9 - интервал "частичный". Как получили: 0,1 с предыдущего интервала + 0,8 с того что нашли выше.
9,1 - интервал "сплошной" складывается из двух высот 7+2,1 (D9 и D10).

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

Автор - VR
Дата добавления - 16.01.2019 в 07:08
_Boroda_ Дата: Среда, 16.01.2019, 10:44 | Сообщение № 5
Группа: Модераторы
Ранг: Местный житель
Сообщений: 14861
Репутация: 5879 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Так нужно?
Код
=СУММПРОИЗВ(ТЕКСТ(Q$6-ТЕКСТ(Q6-C$6:C$99;",0;\0")-ТЕКСТ(B$6:B$99-Q5;",0;\0");",0;\0")*СУММЕСЛИ($AA$4:$AA$8;E$6:E$99&"*";$AB$4:$AB$8)/СУММ($AB$4:$AB$8))

Или попроще, но подлинее и формула массива (вводится одновременным нажатием Контрл Шифт Ентер)
Код
=СУММ(ЕСЛИ(B$6:B$99>Q6;;ЕСЛИ(C$6:C$99>Q5;ЕСЛИ(C$6:C$99>Q6;Q6;C$6:C$99)-ЕСЛИ(B$6:B$99>Q5;B$6:B$99;Q5);))*СУММЕСЛИ($AA$4:$AA$8;E$6:E$99&"*";$AB$4:$AB$8)/СУММ($AB$4:$AB$8))
К сообщению приложен файл: 0195239_1.xls(59.5 Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеТак нужно?
Код
=СУММПРОИЗВ(ТЕКСТ(Q$6-ТЕКСТ(Q6-C$6:C$99;",0;\0")-ТЕКСТ(B$6:B$99-Q5;",0;\0");",0;\0")*СУММЕСЛИ($AA$4:$AA$8;E$6:E$99&"*";$AB$4:$AB$8)/СУММ($AB$4:$AB$8))

Или попроще, но подлинее и формула массива (вводится одновременным нажатием Контрл Шифт Ентер)
Код
=СУММ(ЕСЛИ(B$6:B$99>Q6;;ЕСЛИ(C$6:C$99>Q5;ЕСЛИ(C$6:C$99>Q6;Q6;C$6:C$99)-ЕСЛИ(B$6:B$99>Q5;B$6:B$99;Q5);))*СУММЕСЛИ($AA$4:$AA$8;E$6:E$99&"*";$AB$4:$AB$8)/СУММ($AB$4:$AB$8))

Автор - _Boroda_
Дата добавления - 16.01.2019 в 10:44
bmv98rus Дата: Среда, 16.01.2019, 12:44 | Сообщение № 6
Группа: Проверенные
Ранг: Старожил
Сообщений: 2023
Репутация: 320 ±
Замечаний: 20% ±

Excel 2013/2016
_Boroda_, Александр, нервно мну цигарку в сторонке :-) ТЕКСТ(Q6-C$6:C$99;",0;\0") взял на заметку.

вес я правда так делал ($E$6:$E$42={"плохой"\"частичный"\"сплошной"})*{1\2\3} но остальное было через IF и длинннее сильно, Объединенный
Код
=SUMPRODUCT(TEXT(Q$6-TEXT(Q6-C$6:C$99;",0;\0")-TEXT(B$6:B$99-Q5;",0;\0");",0;\0")*($E$6:$E$99={"плохой"\"частичный"\"сплошной"})*{1\2\3})/10


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

Сообщение отредактировал bmv98rus - Среда, 16.01.2019, 12:44
 
Ответить
Сообщение_Boroda_, Александр, нервно мну цигарку в сторонке :-) ТЕКСТ(Q6-C$6:C$99;",0;\0") взял на заметку.

вес я правда так делал ($E$6:$E$42={"плохой"\"частичный"\"сплошной"})*{1\2\3} но остальное было через IF и длинннее сильно, Объединенный
Код
=SUMPRODUCT(TEXT(Q$6-TEXT(Q6-C$6:C$99;",0;\0")-TEXT(B$6:B$99-Q5;",0;\0");",0;\0")*($E$6:$E$99={"плохой"\"частичный"\"сплошной"})*{1\2\3})/10

Автор - bmv98rus
Дата добавления - 16.01.2019 в 12:44
_Boroda_ Дата: Среда, 16.01.2019, 13:05 | Сообщение № 7
Группа: Модераторы
Ранг: Местный житель
Сообщений: 14861
Репутация: 5879 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
А это
Величина "Вес" может меняться, что также изменяет результаты всех предыдущих расчетов.
?


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеА это
Величина "Вес" может меняться, что также изменяет результаты всех предыдущих расчетов.
?

Автор - _Boroda_
Дата добавления - 16.01.2019 в 13:05
bmv98rus Дата: Среда, 16.01.2019, 14:42 | Сообщение № 8
Группа: Проверенные
Ранг: Старожил
Сообщений: 2023
Репутация: 320 ±
Замечаний: 20% ±

Excel 2013/2016
А это

Это мелочи :-)


Замечательный медведь, процентов на 20.
 
Ответить
Сообщение
А это

Это мелочи :-)

Автор - bmv98rus
Дата добавления - 16.01.2019 в 14:42
VR Дата: Среда, 16.01.2019, 22:02 | Сообщение № 9
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 0 ±
Замечаний: 0% ±

Excel 2003
Спасибо всем за ответы

_Boroda_, попробовал Вашу реализацию. Но расчет неправильный при смене весов. Как пример берём ячейку R6. Если изменить значение в ячейке AB8, то изменяются ответы везде в т.ч. в интервалах где нет значения "не определён (НО)", хотя логично что должны меняться только ячейки (в столбце R для первого массива) только те, в которые попадает интервал "не определён (НО)".

Попробую кое-что сам подправить, что получится выложу.

приблизительно так,

=(СУММЕСЛИМН($D$6:$D$42;$E$6:$E$42;"плохой";$B$6:$B$42;">="&$Q6-10;$C$6:$C$42;"<"&$Q6)+СУММЕСЛИМН($D$6:$D$42;$E$6:$E$42;"частичный";$B$6:$B$42;">="&$Q6-10;$C$6:$C$42;"<"&$Q6)*2+СУММЕСЛИМН($D$6:$D$42;$E$6:$E$42;"сплошной";$B$6:$B$42;">="&$Q6-10;$C$6:$C$42;"<"&$Q6)*3)/10

но если б вы пояснили почему взяты такие цифры в примере расчета Price10 и 20, а то я не понимаю от куда 6,9 и 0,9
К сообщению приложен файл: Copy_of_574.xlsx(25.8 Kb)


ответ не совпадает с ручным расчётом, но все равно спасибо :D
 
Ответить
СообщениеСпасибо всем за ответы

_Boroda_, попробовал Вашу реализацию. Но расчет неправильный при смене весов. Как пример берём ячейку R6. Если изменить значение в ячейке AB8, то изменяются ответы везде в т.ч. в интервалах где нет значения "не определён (НО)", хотя логично что должны меняться только ячейки (в столбце R для первого массива) только те, в которые попадает интервал "не определён (НО)".

Попробую кое-что сам подправить, что получится выложу.

приблизительно так,

=(СУММЕСЛИМН($D$6:$D$42;$E$6:$E$42;"плохой";$B$6:$B$42;">="&$Q6-10;$C$6:$C$42;"<"&$Q6)+СУММЕСЛИМН($D$6:$D$42;$E$6:$E$42;"частичный";$B$6:$B$42;">="&$Q6-10;$C$6:$C$42;"<"&$Q6)*2+СУММЕСЛИМН($D$6:$D$42;$E$6:$E$42;"сплошной";$B$6:$B$42;">="&$Q6-10;$C$6:$C$42;"<"&$Q6)*3)/10

но если б вы пояснили почему взяты такие цифры в примере расчета Price10 и 20, а то я не понимаю от куда 6,9 и 0,9
К сообщению приложен файл: Copy_of_574.xlsx(25.8 Kb)


ответ не совпадает с ручным расчётом, но все равно спасибо :D

Автор - VR
Дата добавления - 16.01.2019 в 22:02
bmv98rus Дата: Среда, 16.01.2019, 23:08 | Сообщение № 10
Группа: Проверенные
Ранг: Старожил
Сообщений: 2023
Репутация: 320 ±
Замечаний: 20% ±

Excel 2013/2016
VR, #3 был до вашего пояснения
Если изменить значение в ячейке AB8,
Возможно _Boroda_, переусердствовал, он понял, что знаменатель 10 - это сумма весов. Естественно изменение одного влияет на итоговое значение всех расчетов. Если это константа, исходя из этого
делится на сумму всех высот входящих в интервал (в нашем случае строго 10).
это именно так, то тогда просто /SUM($AB$4:$AB$8)) замените на /10.
Код
=SUMPRODUCT(TEXT(Q$6-TEXT(Q6-C$6:C$99;",0;\0")-TEXT(B$6:B$99-Q5;",0;\0");",0;\0")*SUMIF($AA$4:$AA$8;E$6:E$99&"*";$AB$4:$AB$8)/10)


Замечательный медведь, процентов на 20.
 
Ответить
СообщениеVR, #3 был до вашего пояснения
Если изменить значение в ячейке AB8,
Возможно _Boroda_, переусердствовал, он понял, что знаменатель 10 - это сумма весов. Естественно изменение одного влияет на итоговое значение всех расчетов. Если это константа, исходя из этого
делится на сумму всех высот входящих в интервал (в нашем случае строго 10).
это именно так, то тогда просто /SUM($AB$4:$AB$8)) замените на /10.
Код
=SUMPRODUCT(TEXT(Q$6-TEXT(Q6-C$6:C$99;",0;\0")-TEXT(B$6:B$99-Q5;",0;\0");",0;\0")*SUMIF($AA$4:$AA$8;E$6:E$99&"*";$AB$4:$AB$8)/10)

Автор - bmv98rus
Дата добавления - 16.01.2019 в 23:08
_Boroda_ Дата: Четверг, 17.01.2019, 00:01 | Сообщение № 11
Группа: Модераторы
Ранг: Местный житель
Сообщений: 14861
Репутация: 5879 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
он понял, что знаменатель 10 - это сумма весов
Да, именно так я и понял
Тогда СУММ меняем, да, но только не на 10 (интервал же может меняться), а на Q$6
Код
=СУММПРОИЗВ(ТЕКСТ(Q$6-ТЕКСТ(Q6-C$6:C$99;",0;\0")-ТЕКСТ(B$6:B$99-Q5;",0;\0");",0;\0")*СУММЕСЛИ($AA$4:$AA$8;E$6:E$99&"*";$AB$4:$AB$8)/Q$6)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
он понял, что знаменатель 10 - это сумма весов
Да, именно так я и понял
Тогда СУММ меняем, да, но только не на 10 (интервал же может меняться), а на Q$6
Код
=СУММПРОИЗВ(ТЕКСТ(Q$6-ТЕКСТ(Q6-C$6:C$99;",0;\0")-ТЕКСТ(B$6:B$99-Q5;",0;\0");",0;\0")*СУММЕСЛИ($AA$4:$AA$8;E$6:E$99&"*";$AB$4:$AB$8)/Q$6)

Автор - _Boroda_
Дата добавления - 17.01.2019 в 00:01
VR Дата: Четверг, 17.01.2019, 07:35 | Сообщение № 12
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 0 ±
Замечаний: 0% ±

Excel 2003
Все верно господа yes поменял знаменатель в конце на 10 и все пошло. Можно изменять веса а также их количество. Протестирую и вечером отпишусь с готовой формулой и файлом. Ещё бы от 10 избавиться как от константы, потому что это добавляет уязвимость.

Например я не хочу учитывать в расчете интервал "не определен" и тогда уже надо будет делить не на 10. Я объяснял откуда взялось именно 10, это сумма всех интервалов которые входят в 10 пунктов, а теперь один интервал нужно не учитывать, как итог знаменатель не равен 10, а в числлителе данный интервал просто исчезает с его весом.

Также это видно в хвосте массива, там конечное число не круглое, в итоге пустой кусок интервала (например если массив заканчивается 289 - 294) некорректный потому что расчет ведётся до 300 пунктов. Для правильности нужно делить уже на 4
 
Ответить
СообщениеВсе верно господа yes поменял знаменатель в конце на 10 и все пошло. Можно изменять веса а также их количество. Протестирую и вечером отпишусь с готовой формулой и файлом. Ещё бы от 10 избавиться как от константы, потому что это добавляет уязвимость.

Например я не хочу учитывать в расчете интервал "не определен" и тогда уже надо будет делить не на 10. Я объяснял откуда взялось именно 10, это сумма всех интервалов которые входят в 10 пунктов, а теперь один интервал нужно не учитывать, как итог знаменатель не равен 10, а в числлителе данный интервал просто исчезает с его весом.

Также это видно в хвосте массива, там конечное число не круглое, в итоге пустой кусок интервала (например если массив заканчивается 289 - 294) некорректный потому что расчет ведётся до 300 пунктов. Для правильности нужно делить уже на 4

Автор - VR
Дата добавления - 17.01.2019 в 07:35
bmv98rus Дата: Четверг, 17.01.2019, 11:38 | Сообщение № 13
Группа: Проверенные
Ранг: Старожил
Сообщений: 2023
Репутация: 320 ±
Замечаний: 20% ±

Excel 2013/2016
это сумма всех интервалов которые входят в 10 пунктов, а теперь один интервал нужно не учитывать, как итог знаменатель не равен 10, а в числлителе данный интервал просто исчезает с его весом.
бррр, слова русские, но …..
Вам бы снова на примере показать что и как.


Замечательный медведь, процентов на 20.
 
Ответить
Сообщение
это сумма всех интервалов которые входят в 10 пунктов, а теперь один интервал нужно не учитывать, как итог знаменатель не равен 10, а в числлителе данный интервал просто исчезает с его весом.
бррр, слова русские, но …..
Вам бы снова на примере показать что и как.

Автор - bmv98rus
Дата добавления - 17.01.2019 в 11:38
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Стандартизация данных массивов (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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