получить % эффективной загрузки без доп. вычислений

Старая форма входа

Страница 1 из 1 1
Модератор форума: китин, _Boroda_

получить % эффективной загрузки без доп. вычислений (Формулы/Formulas)

Azaka

Дата: Понедельник, 27.04.2020, 17:00 | Сообщение № 1

Группа: Пользователи

Ранг: Прохожий

Сообщений: 3

Репутация: 0 ±

Замечаний: 0% ±

Excel 2019

Здравствуйте уважаемые Форумчани! Столкнулся с проблемой подсчёта загрузки мощности предприятий по очень большому (свыше 20 тыс.) списку.

Суть в том, что имеется формулы, применить которые требуется только к уникальным строкам.

Проблема в том, что перечень очень и очень большой, есть объединения ячеек, а строить вручную формулы индивидуально под каждое предприятие, чтобы получить % эффективной загрузки (ячейка I4), для которой надо ещё произвести дополнительные расчёты, из-за их количества практически не возможно.

Т.е. вкратце: получить I4 с использованием данных J4 и K4 для всего перечня предприятий.

Можно ли данную проблему разрешить при помощи Excel?

К сообщению приложен файл: 6197525.xlsx (11.7 Kb)

Сообщение отредактировал Azaka - Понедельник, 27.04.2020, 17:05

Ответить

Pelena

Дата: Понедельник, 27.04.2020, 19:38 | Сообщение № 2

Группа: Админы

Ранг: Местный житель

Сообщений: 19404

Репутация: 4555 ±

Замечаний: ±

Excel 365 & Mac Excel

Здравствуйте.
Формула массива

Код

=ЕСЛИ(C4="";"";ЕСЛИОШИБКА(СУММ(F4:ИНДЕКС(F4:F20;АГРЕГАТ(15;6;СТРОКА($1:$16)/(C4:C20<>"");2)-1))/СУММ(ЕСЛИОШИБКА(F4:ИНДЕКС(F4:F20;АГРЕГАТ(15;6;СТРОКА($1:$16)/(C4:C20<>"");2)-1)/E4:ИНДЕКС(E4:E20;АГРЕГАТ(15;6;СТРОКА($1:$16)/(C4:C20<>"");2)-1)*G4:ИНДЕКС(G4:G20;АГРЕГАТ(15;6;СТРОКА($1:$16)/(C4:C20<>"");2)-1);0))*100;0))

Так как на весь диапазон 20000 строк формула будет очень медленная, предположила, что на каждое предприятие приходится не более 16 изделий. Если это не так, напишите максимальное кол-во изделий для предприятия.

К сообщению приложен файл: 2522971.xlsx (13.8 Kb)

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816

Ответить

_Boroda_

Дата: Понедельник, 27.04.2020, 19:41 | Сообщение № 3

Группа: Админы

Ранг: Местный житель

Сообщений: 16715

Репутация: 6504 ±

Замечаний: ±

2003; 2007; 2010; 2013 RUS

Конечно можно.
В целях экономии ресурсов немного увеличил длину формулы (второе ЕСЛИ) и предположил, что одно предприятие не может выпускать более 99 видов продукции. Если это не так (допустим, не 99, а 199), то замените в формуле все 103 на 203)
Столбцы J и К не нужны, все сразу считается

Код

=ЕСЛИ(A4;ЕСЛИ(A5*(D4<>"");H4;СУММ((ПРОСМОТР(СТРОКА(4:103);СТРОКА(4:103)/(A4:A103<>"");B4:B103)=B4)*Ч(+F4:F103))/СУММ(ЕСЛИОШИБКА((ПРОСМОТР(СТРОКА(4:103);СТРОКА(4:103)/(A4:A103<>"");B4:B103)=B4)*F4:F103/E4:E103*G4:G103;))%);"")

Из-за того, что есть незаполненные изделия, формула получилась подлиннее и массивная. Вводится одновременным нажатием Ctrl+Shift+Enter
То же самое, но немного покороче

Код

К сообщению приложен файл: 6197525_2.xlsx (14.6 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995

Ответить

Azaka

Дата: Вторник, 28.04.2020, 08:35 | Сообщение № 4

Группа: Пользователи

Ранг: Прохожий

Сообщений: 3

Репутация: 0 ±

Замечаний: 0% ±

Excel 2019

Pelena, _Boroda_, огромное спасибо Вам за помощь!
Переменил вариант _Boroda_, единственно:
1) в ряде ячеек от чего-то указывается слишком большие цифры, хотя в предыдущем столбце (загрузка по цеху) выводится 100%;
2) в некоторых ячейках, где отсутствуют значения, указывается запись #ДЕЛ/0!, а в некоторых, таких же - ничего.
Но всё вышеприведённое - лишь косметический вид, в целом основная задача выполняется!

Pelena, _Boroda_, ещё раз благодарю Вас за помощь, не думал, что это удастся реализовать средствами Excel!

Данную тему предполагаю можно закрыть.

К сообщению приложен файл: 6547809.jpg (25.1 Kb)

Сообщение отредактировал Azaka - Вторник, 28.04.2020, 08:35

Ответить

_Boroda_

Дата: Вторник, 28.04.2020, 14:09 | Сообщение № 5

Группа: Админы

Ранг: Местный житель

Сообщений: 16715

Репутация: 6504 ±

Замечаний: ±

2003; 2007; 2010; 2013 RUS

Если бы Вы не картинку фильтра нам показали, а данные в файле, то мы бы что-нибудь Вам посоветовали. Наверное (с)

Ответить

Azaka

Дата: Вторник, 28.04.2020, 16:43 | Сообщение № 6

Группа: Пользователи

Ранг: Прохожий

Сообщений: 3

Репутация: 0 ±

Замечаний: 0% ±

Excel 2019

Цитата _Boroda_, 28.04.2020 в 14:09, в сообщении № 5 ()

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

Ещё раз спасибо, но в целом, "и так более чем хорошо"!

Просто данных там слишком много, много "лишних" сведений, т.е. по сути необработанная выгрузка из другой системы.

Ответить

Azaka7609

Дата: Четверг, 26.10.2023, 17:15 | Сообщение № 7

Группа: Пользователи

Ранг: Прохожий

Сообщений: 8

Репутация: 0 ±

Замечаний: 0% ±

Здравствуйте уважаемые Форумчани! Не прошло и трёх лет, как пользуюсь данным решением! Ещё раз огромное спасибо за помощь!

Но сейчас, из-за изменений выгрузки из получаемой базы возникла необходимость в её изменении.

Вопрос, возможно ли сделать тоже самое, что и в оригинале (Лист 1), но в том случае, когда нет объединений (лист 2)? Т.е. хотелось бы, чтобы когда идёт подряд несколько строк с одним кодом, высчитывалось только одно значение (как в листе 1).

P.S. Прошу прощение за такую наглость с подобными вопросами.

К сообщению приложен файл: 6197525_2zapros.xlsx (19.5 Kb)

Ответить

получить % эффективной загрузки без доп. вычислений (Формулы/Formulas)

Страница 1 из 1
1