У меня есть несколько «Смесей», для которых я хочу создать стоимость. В настоящее время каждая смесь отображается в виде компонента в ячейке для ясности, количество единиц находится в отдельном столбце в таблице, а стоимость за единицу разбита на отдельные компоненты на отдельном листе в рабочей книге.
Пример На листе 1 у меня есть следующее: ячейка C2 - "3" (количество единиц), C3 - "2", а C4 - "4". Ячейка D2 - "1 стакан муки, .25 стакана молока, 1 яйцо". Ячейка D3 - "2 стакана муки, .33 стакана масла, 2 яйца". Ячейка D4 - "1 стакан муки, 1 яйцо". Я хочу, чтобы в столбце E была формула для расчета себестоимости на основе цен на листе 2, в которой стоимость муки в ячейке B2 равна ".05", стоимость молока в ячейке B3 равна ".08", стоимость яйца в ячейке B4 равна ".10" и стоимость масла равна ".04". У меня также есть общая стоимость смешивания ".25", которую я хочу применить к единице, которая не указана нигде, кроме ячейки B16 листа 2.
В настоящее время я вручную настраиваю каждую формулу в столбце E, что занимает много времени и приводит к потенциальной ошибке. Пример этого для ячейки E2: "=3*(1*.05+.25*.08+1*.1+.25)"
Два варианта, которые я придумал, — это создать столбец «Смесь», который показывает «Смесь 1», «Смесь 2» и «Смесь 3», затем определить себестоимость за единицу, на которую я могу умножить и создать уникальную смесь для каждой комбинации (в настоящее время около 15 смесей и их число растет). Пример столбца F — это стоимость смеси, поэтому формула в E2 становится «=C2*F2». Мой вопрос в том, могу ли я использовать оператор IF() для автоматического заполнения этого столбца на основе данных в столбце D, поскольку я хочу, чтобы столбец F был скрыт при печати данных, и также было бы проще перемещаться по листу, если столбец скрыт при вводе данных.
Другой вариант — создать формулу, которая разделит столбец D на отдельные компоненты, а затем умножить их, используя отдельные значения, а затем скрыть все данные отдельных столбцов, и формула будет выглядеть примерно так: «=Единицы*(точка данных 1+точка данных 2+точка данных 3+точка данных смешивания)». Меня беспокоит такой подход, что когда я вычисляю значения смешивания 3-4 раза на строку, и у меня появляется 5-6 ингредиентов, таблица становится очень большой и неудобной для навигации.
Любые другие идеи или подходы были бы полезны.
решение1
Ваше описание действительно громоздкое,
но я думаю, что следующее даст вам представление о том, как это можно сделать...
--- сохранить как файл .csv и открыть в Excel или LibreOffice ---
,,,, ,Элемент A,Элемент B,Элемент C,Сумма ,100,10,1, ,,,, Смешивание,,,, "=DEC2BIN(СТРОКИ($A$6:A6),СТОЛБЦЫ($B$2:$D$2))","=ЗНАЧЕНИЕ(СРЕДНЕ($A6,СТОЛБЦЫ($B$2:B$2),1))","=ЗНАЧЕНИЕ(СРЕДНЕ($A6,СТОЛБЦЫ($B$2:C$2),1))","=ЗНАЧЕНИЕ(СРЕДНЕ($A6,СТОЛБЦЫ($B$2:D$2),1))","=СУММПРОИЗВ($B$3:$D$3,B6:D6)"--- конец файла ---
«Скопируйте» строку 6 (последнюю строку), чтобы получилось семь строк (шесть копий).
Измените цифры в строке 3 и посмотрите, что произойдет.
Таблица ниже "Mix" делает это;
Столбец A - создает двоичное число на основе позиции строки (001 для первой строки, 111 для 7-й строки).
Столбцы B, C и D - выбирают двоичную цифру в позиции, соответствующей столбцу, делают ее "числом", чтобы мы могли выполнить над ней расчет.
Столбец E - используется SUMPRODUCT()для расчета стоимости "mix".
SUMPRODUCT(Array1, Array2, ...)
берет первый элемент в массивах Array1 и Array2, умножает их, делает то же самое с элементом 2 и добавляет его, затем элемент 3 и так далее...
Он может обрабатывать несколько массивов, а не только два, хотя есть ограничение.
В приведенном выше CSV-файле третья строка является массивом «стоимости», а строки, начиная со строки 6, используются в качестве массивов «выбора» для создания семи возможных смешанных стоимостей для трех позиций (строки 2 в степени три для трех позиций, при этом выбрана хотя бы одна).
*Формулы:
столбец можно изменить для обработки большего количества элементов, просто изменив аргумент COLUMNS.
Столбец B можно скопировать в «любое» количество столбцов (три из них присутствуют в файле csv).
«любой» ограничен «DEC2BIN» в столбце A, хотя он может выдавать только десять цифр в Excel.
Столбец E тоже нуждается в корректировке, соответственно — я оставлю это как последнюю задачу, которую вы сможете выполнить самостоятельно; на самом деле не очень сложно.
Когда у вас будет эта шестая строка, вы можете скопировать ее вниз для необходимого количества строк, последняя из которых должна будет содержать все «1» в таблице смешивания.