#excel #nested #formula
#excel #вложенный #формула
Вопрос:
Я работаю в инженерной организации и располагаю большим набором данных (50 000) деталей и узлов. Моя главная цель — проработать материальную часть сборок и, в конечном счете, комплекты, которые продаются клиентам. Например, часть 1 состоит из 50% алюминия, 25% стали и 25% меди.
Прикрепленное изображение является извлечением данных, с которыми я работаю.
Столбцы A и B показывают уровень частей (здесь есть два примера).
Столбец U показывает значение в процентах от сборки более высокого уровня.
Мне нужна динамическая формула, которая заполнит строку 2 от до AZ процентным содержанием материала, составляющего комплект (например, алюминий в AT2 должен указывать 77%), но я также требую, чтобы формула была динамической и работала для части 2, где есть только 2 части, составляющие сборку.
Формула, которую я использовал, ошибочна (или я не могу использовать ее дальше, чтобы заставить ее работать), и у меня нет идей о том, как создать формулу, которая была бы достаточно динамичной для ввода необходимых мне данных. К вашему сведению, у меня есть программа VBA, которая введет формулу в требуемые строки, как только у меня будет хорошая формула.
Заранее благодарю вас за помощь, TJ
Комментарии:
1. Практика на этом форуме заключается в предоставлении вашей попытки (формулы, которую вы пробовали). Это часто помогает понять вашу проблему и избежать подобных ошибок других. Также это не бесплатный сайт для кодирования, но он существует, чтобы помочь другим пользователям с кодом или формулами, которые они пытаются разработать. Сказав это, из вашего макета данных кажется, что простая
SUMIFSформула должна делать то, что вам нужно.SUMIFSможно использовать подстановочные знаки в критериях, чтобы вы могли использовать их для подбора всех подразделов. Если вы не можете заставить ее работать, отправьте ответ с вашими попытками.
Ответ №1:
Самый простой способ, который я могу придумать, — добавить несколько столбцов и использовать SUMIF
-
Добавьте дополнительный столбец для каждого материала, который является вкладом в деталь для этого материала для каждой сборки. Например. для союзника добавьте Союз1, который будет равен U * AT, это будет пустым для строки 2, но будет .37 в строке 3, .40 в строке 4
-
Вставьте новый столбец в B, в котором отображается название детали для каждой подсборки, чтобы в вашем примере оно было пустым для строки 2, показывало «Часть 1» для строк 3-9, пустым для строки 10 и «Часть 2» для строк 11 и 12. Надеюсь, вы можете сделать это с помощью формулы или она уже есть в данных.
-
Теперь вы можете создать свою формулу в столбцах исходного материала для каждой детали, просто sumif:
- Диапазон — это наш новый столбец B (который содержит соответствующую часть для каждой подсборки)
- Условие — это ячейка в A (для которой мы пытаемся вычислить)
- Sum_Range — это новый столбец, который мы создали для каждого материала на шаге 1, поэтому для Ally это будет Ally1.
Затем это должно дать вам ответы, которые вы хотите.
Если вы не можете добавить столбцы, то вам нужно будет использовать матричную формулу с IF и SUMPRODUCT, я думаю. Я всегда нахожу это болезненным для выполнения и сложным для понимания, поэтому предлагаю более простой подход, описанный выше.
В сторону
Если бы это был я, я бы не использовал VBA для заполнения формул — просто потому, что мне нравится, когда в столбце либо все значения, либо одна и та же формула. Причина в том, что если мне позже понадобится изменить формулу, я могу просто скопировать ее во все ячейки ниже.
Чтобы сделать это в этом случае, я бы вставил еще один столбец для каждого материала и использовал оператор IF, чтобы либо ввести текущее значение материалов (когда это подсборка), либо использовать формулу на шаге 3 (когда это деталь).