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

Вход

Регистрация

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

 

= Мир MS Excel/Пропорциональное распределение значений параметра - Мир MS Excel

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

Excel 2016
Добрый день, участники Форума.
Помогите пжл составить формулу.
Имеется 3 таблицы.
В 1-й: список запчастей, использованных при сервисе оборудования (за определенный период), с указанием их стоимости.
Во 2-й: список запчастей и список приборов, для обсулуживания которых данные запчасти используются (в моем случае список "запчасть-прибор" общий - некоторые запчасти не были использованы в течение рассматриваемого периода)
В 3-й: список приборов и количество операционных часов, в течение которых каждый прибор был использован в течение рассматриваемого периода времени.
Мне необходимо в 3-й таблице посчитать общую стоимость запчастей, потребленных по каждому прибору, в пропорции от операционных часов использования каждого прибора (за рассматриваемый период).

P.S. Файл прикладывается. Просьба помочь в сосотавлении формулы, без макросов пжл. В последствии мне необходимо будет перенести данную калькуляцию в Power BI
Буду премного благодарен!
К сообщению приложен файл: ___.xlsx (82.7 Kb)
 
Ответить
СообщениеДобрый день, участники Форума.
Помогите пжл составить формулу.
Имеется 3 таблицы.
В 1-й: список запчастей, использованных при сервисе оборудования (за определенный период), с указанием их стоимости.
Во 2-й: список запчастей и список приборов, для обсулуживания которых данные запчасти используются (в моем случае список "запчасть-прибор" общий - некоторые запчасти не были использованы в течение рассматриваемого периода)
В 3-й: список приборов и количество операционных часов, в течение которых каждый прибор был использован в течение рассматриваемого периода времени.
Мне необходимо в 3-й таблице посчитать общую стоимость запчастей, потребленных по каждому прибору, в пропорции от операционных часов использования каждого прибора (за рассматриваемый период).

P.S. Файл прикладывается. Просьба помочь в сосотавлении формулы, без макросов пжл. В последствии мне необходимо будет перенести данную калькуляцию в Power BI
Буду премного благодарен!
Автор - Andreas
Дата добавления - 25.06.2019 в 09:37
_Boroda_ Дата: Вторник, 25.06.2019, 10:14 | Сообщение № 2
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16674
Репутация: 6479 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Цитата Andreas, 25.06.2019 в 09:37, в сообщении № 1 ()
посчитать общую стоимость запчастей, потребленных по каждому прибору, в пропорции от операционных часов
Это как? Приведите пример расчета в файле
Вообще затраты так можно
Код
=СУММ(СУММЕСЛИ(I$2:I$6;ЕСЛИ(A2=F$2:F$16;G$2:G$16);J$2:J$6))

Формула массива. Вводится одновременным нажатием Ctrl+Shift+Enter
В пропорции так, но какая-то непонятность получается
Код
=СУММ(СУММЕСЛИ(I$2:I$6;ЕСЛИ(A2=F$2:F$16;G$2:G$16);J$2:J$6))*B2/СУММ(B$2:B$6)


Для простоты все данные перенес на один лист
К сообщению приложен файл: -31-1.xlsx (83.2 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
Цитата Andreas, 25.06.2019 в 09:37, в сообщении № 1 ()
посчитать общую стоимость запчастей, потребленных по каждому прибору, в пропорции от операционных часов
Это как? Приведите пример расчета в файле
Вообще затраты так можно
Код
=СУММ(СУММЕСЛИ(I$2:I$6;ЕСЛИ(A2=F$2:F$16;G$2:G$16);J$2:J$6))

Формула массива. Вводится одновременным нажатием Ctrl+Shift+Enter
В пропорции так, но какая-то непонятность получается
Код
=СУММ(СУММЕСЛИ(I$2:I$6;ЕСЛИ(A2=F$2:F$16;G$2:G$16);J$2:J$6))*B2/СУММ(B$2:B$6)


Для простоты все данные перенес на один лист
Автор - _Boroda_
Дата добавления - 25.06.2019 в 10:14
Andreas Дата: Вторник, 25.06.2019, 10:45 | Сообщение № 3
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
_Boroda_, добрый день.
Приложил упрощенный файл (все три таблицы на одной вкладке) с примером расчёта того что мне требуется вручную.
Делал на отдельных вкладках, поскольку впоследствии это будут громоздкие отчеты и они буду автоматически подгружаться в PowerBI, соответственно и формула жеательно чтобы ссылалась на различные вкладки или файлы..
Про формулу массива слышал, но к сожалению не получислоь ее применить для необходимого мне решения.
К сообщению приложен файл: ___-__.xlsx (73.1 Kb)
 
Ответить
Сообщение_Boroda_, добрый день.
Приложил упрощенный файл (все три таблицы на одной вкладке) с примером расчёта того что мне требуется вручную.
Делал на отдельных вкладках, поскольку впоследствии это будут громоздкие отчеты и они буду автоматически подгружаться в PowerBI, соответственно и формула жеательно чтобы ссылалась на различные вкладки или файлы..
Про формулу массива слышал, но к сожалению не получислоь ее применить для необходимого мне решения.
Автор - Andreas
Дата добавления - 25.06.2019 в 10:45
sboy Дата: Вторник, 25.06.2019, 11:08 | Сообщение № 4
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2566
Репутация: 724 ±
Замечаний: 0% ±

Excel 2010
Добрый день.
Цитата Andreas, 25.06.2019 в 10:45, в сообщении № 3 ()
поскольку впоследствии это будут громоздкие отчеты и они буду автоматически подгружаться в PowerBI

А почему не делаете расчеты в самом PBI?

Яндекс: 410016850021169
 
Ответить
СообщениеДобрый день.
Цитата Andreas, 25.06.2019 в 10:45, в сообщении № 3 ()
поскольку впоследствии это будут громоздкие отчеты и они буду автоматически подгружаться в PowerBI

А почему не делаете расчеты в самом PBI?
Автор - sboy
Дата добавления - 25.06.2019 в 11:08
Andreas Дата: Вторник, 25.06.2019, 12:36 | Сообщение № 5
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
sboy, день добрый!
Потому что пока мало знаком с функционалом Power BI, сам не составлял в нем никакие анализы, только пользовался данными из них.
Но насколько известно функции те же что и в Excel, лишь некоторые немного отличаются по названию.
На данный момент у меня практически готов весь анализ (калькулятор) в Excel, ввиду того что изменился один из источников данных, появилась необходимость переделать один из расчётов. С ним затык и произошёл.
Не хочется не доделав в Excel перепрыгивать в Power BI.
 
Ответить
Сообщениеsboy, день добрый!
Потому что пока мало знаком с функционалом Power BI, сам не составлял в нем никакие анализы, только пользовался данными из них.
Но насколько известно функции те же что и в Excel, лишь некоторые немного отличаются по названию.
На данный момент у меня практически готов весь анализ (калькулятор) в Excel, ввиду того что изменился один из источников данных, появилась необходимость переделать один из расчётов. С ним затык и произошёл.
Не хочется не доделав в Excel перепрыгивать в Power BI.
Автор - Andreas
Дата добавления - 25.06.2019 в 12:36
Светлый Дата: Вторник, 25.06.2019, 15:38 | Сообщение № 6
Группа: Друзья
Ранг: Старожил
Сообщений: 1829
Репутация: 510 ±
Замечаний: 0% ±

Excel 2013, 2016
Формула массива для ячеек C2:C4 для файла из сообщения №3:
Код
=СУММ(ЕСЛИОШИБКА(Ч(СМЕЩ(J$1;ПОИСКПОЗ(Т(СМЕЩ(G$1;ЕСЛИОШИБКА(НАИБОЛЬШИЙ((A2=F$2:G$7)*СТРОКА($1:$6);СТРОКА($1:$3)););));I$1:I$4;)-1;))*B2/МУМНОЖ(ТРАНСП(СУММЕСЛИ(A$2:A$4;ЕСЛИ(Т(СМЕЩ(G$1;ЕСЛИОШИБКА(НАИБОЛЬШИЙ((A2=F$2:G$7)*СТРОКА($1:$6);СТОЛБЕЦ(A:C)););))=G$2:G$7;F$2:F$7);B$2:B$4));СТРОКА(2:7)^0);))
Или что-то другое надо посчитать?
*Можно чуть-чуть сократить:
Код
=СУММ(ЕСЛИОШИБКА(Ч(СМЕЩ(J$1;ПОИСКПОЗ(Т(СМЕЩ(G$1;НАИБОЛЬШИЙ((A2=F$2:G$7)*СТРОКА($1:$6);СТРОКА($1:$3));));I$1:I$4;)-1;))*B2/МУМНОЖ(ТРАНСП(СУММЕСЛИ(A$2:A$4;ЕСЛИ(Т(СМЕЩ(G$1;НАИБОЛЬШИЙ((A2=F$2:G$7)*СТРОКА($1:$6);СТОЛБЕЦ(A:C));))=G$2:G$7;F$2:F$7);B$2:B$4));СТРОКА(2:7)^0);))

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

Сообщение отредактировал Светлый - Вторник, 25.06.2019, 15:46
 
Ответить
СообщениеФормула массива для ячеек C2:C4 для файла из сообщения №3:
Код
=СУММ(ЕСЛИОШИБКА(Ч(СМЕЩ(J$1;ПОИСКПОЗ(Т(СМЕЩ(G$1;ЕСЛИОШИБКА(НАИБОЛЬШИЙ((A2=F$2:G$7)*СТРОКА($1:$6);СТРОКА($1:$3)););));I$1:I$4;)-1;))*B2/МУМНОЖ(ТРАНСП(СУММЕСЛИ(A$2:A$4;ЕСЛИ(Т(СМЕЩ(G$1;ЕСЛИОШИБКА(НАИБОЛЬШИЙ((A2=F$2:G$7)*СТРОКА($1:$6);СТОЛБЕЦ(A:C)););))=G$2:G$7;F$2:F$7);B$2:B$4));СТРОКА(2:7)^0);))
Или что-то другое надо посчитать?
*Можно чуть-чуть сократить:
Код
=СУММ(ЕСЛИОШИБКА(Ч(СМЕЩ(J$1;ПОИСКПОЗ(Т(СМЕЩ(G$1;НАИБОЛЬШИЙ((A2=F$2:G$7)*СТРОКА($1:$6);СТРОКА($1:$3));));I$1:I$4;)-1;))*B2/МУМНОЖ(ТРАНСП(СУММЕСЛИ(A$2:A$4;ЕСЛИ(Т(СМЕЩ(G$1;НАИБОЛЬШИЙ((A2=F$2:G$7)*СТРОКА($1:$6);СТОЛБЕЦ(A:C));))=G$2:G$7;F$2:F$7);B$2:B$4));СТРОКА(2:7)^0);))
Автор - Светлый
Дата добавления - 25.06.2019 в 15:38
Andreas Дата: Среда, 26.06.2019, 15:46 | Сообщение № 7
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Светлый, добрый день.
Формулу подставил. Не считает пока. Изучу, сообщу получается то что требовалось или нет.
Спасибо!
 
Ответить
СообщениеСветлый, добрый день.
Формулу подставил. Не считает пока. Изучу, сообщу получается то что требовалось или нет.
Спасибо!
Автор - Andreas
Дата добавления - 26.06.2019 в 15:46
Светлый Дата: Среда, 26.06.2019, 18:17 | Сообщение № 8
Группа: Друзья
Ранг: Старожил
Сообщений: 1829
Репутация: 510 ±
Замечаний: 0% ±

Excel 2013, 2016
Цитата Andreas, 26.06.2019 в 15:46, в сообщении № 7 ()
Формула массива
вводится одновременным нажатием Ctrl+Shift+Enter
*Файл из сообщения №3

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

Сообщение отредактировал Светлый - Среда, 26.06.2019, 18:26
 
Ответить
Сообщение
Цитата Andreas, 26.06.2019 в 15:46, в сообщении № 7 ()
Формула массива
вводится одновременным нажатием Ctrl+Shift+Enter
*Файл из сообщения №3
Автор - Светлый
Дата добавления - 26.06.2019 в 18:17
Andreas Дата: Вторник, 02.07.2019, 08:44 | Сообщение № 9
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Светлый, большое спасибо.
Только никак у меня не получается переделать формулу для случая расположения данных на отдельных вкладках, как в Файле из письма №1. В данном случае совсем другие функции требуются.
 
Ответить
СообщениеСветлый, большое спасибо.
Только никак у меня не получается переделать формулу для случая расположения данных на отдельных вкладках, как в Файле из письма №1. В данном случае совсем другие функции требуются.
Автор - Andreas
Дата добавления - 02.07.2019 в 08:44
Светлый Дата: Вторник, 02.07.2019, 11:09 | Сообщение № 10
Группа: Друзья
Ранг: Старожил
Сообщений: 1829
Репутация: 510 ±
Замечаний: 0% ±

Excel 2013, 2016
Цитата Andreas, 02.07.2019 в 08:44, в сообщении № 9 ()
переделать формулу для случая расположения данных на отдельных вкладках
Код
=СУММ(ЕСЛИОШИБКА(Ч(СМЕЩ('1st Table'!B$1;ПОИСКПОЗ(Т(СМЕЩ('2nd Table'!B$1;НАИБОЛЬШИЙ((A2='2nd Table'!A$2:B$30)*СТРОКА($1:$29);СТРОКА($1:$6));));'1st Table'!A$1:A$6;)-1;))*B2/МУМНОЖ(ТРАНСП(СУММЕСЛИ(A$2:A$6;ЕСЛИ(Т(СМЕЩ('2nd Table'!B$1;НАИБОЛЬШИЙ((A2='2nd Table'!A$2:B$30)*СТРОКА($1:$29);СТОЛБЕЦ(A:F));))='2nd Table'!B$2:B$30;'2nd Table'!A$2:A$30);B$2:B$6));СТРОКА(2:30)^0);))
массивная. Вводится одновременным нажатием Ctrl+Shift+Enter.
*Для лучшего редактирования:
Код
=СУММ(ЕСЛИОШИБКА(Ч(СМЕЩ('1st Table'!B$1;ПОИСКПОЗ(Т(СМЕЩ('2nd Table'!B$1;НАИБОЛЬШИЙ((A2='2nd Table'!A$2:B$30)*СТРОКА($1:$29);СТРОКА($1:$6));));'1st Table'!A$1:A$6;)-1;))*B2/МУМНОЖ(ТРАНСП(СУММЕСЛИ(A$2:A$6;ЕСЛИ(Т(СМЕЩ('2nd Table'!B$1;НАИБОЛЬШИЙ((A2='2nd Table'!A$2:B$30)*СТРОКА($1:$29);ТРАНСП(СТРОКА($1:$6)));))='2nd Table'!B$2:B$30;'2nd Table'!A$2:A$30);B$2:B$6));СТРОКА(2:30)^0);))
К сообщению приложен файл: Andreas.xlsx (83.1 Kb)

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

Сообщение отредактировал Светлый - Вторник, 02.07.2019, 12:11
 
Ответить
Сообщение
Цитата Andreas, 02.07.2019 в 08:44, в сообщении № 9 ()
переделать формулу для случая расположения данных на отдельных вкладках
Код
=СУММ(ЕСЛИОШИБКА(Ч(СМЕЩ('1st Table'!B$1;ПОИСКПОЗ(Т(СМЕЩ('2nd Table'!B$1;НАИБОЛЬШИЙ((A2='2nd Table'!A$2:B$30)*СТРОКА($1:$29);СТРОКА($1:$6));));'1st Table'!A$1:A$6;)-1;))*B2/МУМНОЖ(ТРАНСП(СУММЕСЛИ(A$2:A$6;ЕСЛИ(Т(СМЕЩ('2nd Table'!B$1;НАИБОЛЬШИЙ((A2='2nd Table'!A$2:B$30)*СТРОКА($1:$29);СТОЛБЕЦ(A:F));))='2nd Table'!B$2:B$30;'2nd Table'!A$2:A$30);B$2:B$6));СТРОКА(2:30)^0);))
массивная. Вводится одновременным нажатием Ctrl+Shift+Enter.
*Для лучшего редактирования:
Код
=СУММ(ЕСЛИОШИБКА(Ч(СМЕЩ('1st Table'!B$1;ПОИСКПОЗ(Т(СМЕЩ('2nd Table'!B$1;НАИБОЛЬШИЙ((A2='2nd Table'!A$2:B$30)*СТРОКА($1:$29);СТРОКА($1:$6));));'1st Table'!A$1:A$6;)-1;))*B2/МУМНОЖ(ТРАНСП(СУММЕСЛИ(A$2:A$6;ЕСЛИ(Т(СМЕЩ('2nd Table'!B$1;НАИБОЛЬШИЙ((A2='2nd Table'!A$2:B$30)*СТРОКА($1:$29);ТРАНСП(СТРОКА($1:$6)));))='2nd Table'!B$2:B$30;'2nd Table'!A$2:A$30);B$2:B$6));СТРОКА(2:30)^0);))
Автор - Светлый
Дата добавления - 02.07.2019 в 11:09
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Пропорциональное распределение значений параметра (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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