Чтобы рассчитать количество всех базовых ресурсов во вложенных наборах графики, отделите прямые ресурсы от содержимого наборов, перемножьте данные на каждом уровне вложенности и сложите результаты. В этом руководстве по Excel для Mac создается полная матрица потребностей и точный поиск по ресурсу и набору.
Задача — определить, сколько экземпляров каждого изображения требуется для одного набора или выбранной партии наборов, если в наборы входят другие наборы. Все приведенные ниже названия и количества условны. Расчет учитывает экземпляры изображений, но не объединяет повторно используемые исходные файлы.
Какие версии Excel для Mac поддерживают этот процесс?
Используйте Excel для Microsoft 365 для Mac, Excel 2024 для Mac или Excel 2021 для Mac. Microsoft указывает эти версии для функций матриц и поиска, используемых здесь. Microsoft также прямо документирует динамические массивы в Excel 2021 для Mac. В руководстве используются обычные диапазоны листа и формулы, подтвержденные клавишей Enter. Функция MMULT, Функция INDEX, Что нового в Excel 2021 для Mac.
Формула динамического массива автоматически заполняет соседние ячейки — это называется разливом. Вводите каждую формулу массива только в указанную начальную ячейку, оставляя диапазон вывода пустым. Не размещайте эти формулы в таблицах Excel: по документации Microsoft, разлив формул массивов в таблицах не поддерживается. Формулы динамических массивов и поведение разлива.
Приведенные вычисления проверены с помощью небольшой детерминированной программы на Python. Формулы не запускались в настольном приложении Excel.
Как записать прямые ресурсы и вложенные наборы?
Начните с пустого листа. Создайте две входные матрицы: одну для базовых изображений, напрямую добавленных в каждый набор, и другую для наборов, напрямую включенных в другие наборы. Разделение этих входных данных не позволяет повторно учитывать уже раскрытые количества.
Введите матрицу прямых базовых ресурсов
Вставьте этот блок с разделителями табуляцией в A1. Его числовая область B2:D5 содержит четыре строки ресурсов и три столбца наборов.
Ресурс DotKit StripeKit StudioKit
Dot 2 1 0
Arc 1 0 2
Grid 0 3 1
Tile 0 1 0
Каждый столбец описывает прямое графическое содержимое одного набора. В DotKit напрямую входят два экземпляра Dot и один Arc. В StripeKit напрямую входят один Dot, три Grid и один Tile. Вложенные наборы будут записаны отдельно.
Для каждого отсутствующего компонента укажите числовой ноль. Microsoft указывает, что входные данные для MMULT должны содержать числа; пустые ячейки или текст могут привести к ошибке #VALUE!. Не включайте метки в числовые диапазоны, передаваемые функции. Функция MMULT.
Введите матрицу вложенности наборов
Вставьте следующий блок в A7. Здесь строка указывает набор-компонент, а столбец — родительский набор. Числовая матрица вложенности занимает диапазон B8:D10.
Набор-компонент DotKit StripeKit StudioKit
DotKit 0 2 1
StripeKit 0 0 3
StudioKit 0 0 0
Матрица описывает следующие рецепты:
- DotKit не содержит других наборов.
- StripeKit содержит два DotKit.
- StudioKit содержит один DotKit и три StripeKit.
Указывайте только непосредственное содержимое. Один непосредственно входящий в StudioKit DotKit указывается в D8; шесть дополнительных DotKit внутри трех StripeKit нужно вычислить. Если ввести семь в D8, эти шесть будут учтены дважды.
Используйте одинаковый порядок наборов в B1:D1, B7:D7 и A8:A10: DotKit, StripeKit, StudioKit. При умножении матриц сопоставляются позиции, поэтому одинаковые метки не исправят разный порядок входных данных.
В этом примере предполагаются неотрицательные целые количества, а рецепты в конечном итоге приводят к базовым ресурсам. Набор не может содержать себя через цепочку других наборов.
Как рассчитать полную матрицу потребностей?
В приведенном ниже объяснении обозначим матрицу прямых ресурсов как B, а матрицу вложенности — как N. Это математические обозначения, их не нужно задавать в книге. Умножение B на N дает ресурсы, добавленные через один переход вложенности. Умножение этого результата на N добавляет еще один переход.
Для этих трех наборов самая глубокая цепочка — StudioKit, содержащий StripeKit, который содержит DotKit. Следовательно, полная матрица равна сумме прямых ресурсов, ресурсов на один уровень вложенности ниже и ресурсов на два уровня ниже:
Полные потребности = B + BN + BN²
Верхний индекс обозначает повторное умножение матриц. В Excel рассчитайте его с помощью MMULT; обычный оператор ^2, примененный к диапазону, возводит в квадрат отдельные значения, а не матрицу.
Рассчитайте вклад каждого уровня вложенности
Введите эту формулу в G2, затем нажмите Enter. Она заполнит G2:I5 ресурсами, достигаемыми через один переход вложенности.
=MMULT($B$2:$D$5,$B$8:$D$10)
Введите следующую формулу в L2. Она заполнит L2:N5 ресурсами, достигаемыми через два перехода вложенности.
=MMULT(G2:I5,$B$8:$D$10)
Порядок умножения определяется размерностями: матрица ресурсов размером четыре на три, умноженная на матрицу вложенности размером три на три, дает матрицу ресурсов размером четыре на три. По документации Microsoft, число столбцов первого массива должно совпадать с числом строк второго. Функция MMULT.
Для промежуточной проверки столбец StudioKit в G2:I5 должен содержать 5, 1, 9 и 3. Его столбец в L2:N5 должен содержать 12, 6, 0 и 0 в порядке ресурсов Dot, Arc, Grid, Tile.
Сложите прямой и вложенный вклад
Введите DotKit, StripeKit и StudioKit в ячейки Q1:S1. Введите Dot, Arc, Grid и Tile в P2:P5. Затем поместите эту формулу в Q2:
=$B$2:$D$5+G2:I5+L2:N5
В заполненной матрице Q2:S5 должны получиться следующие значения:
| Базовый ресурс | DotKit | StripeKit | StudioKit |
|---|---|---|---|
| Dot | 2 | 5 | 17 |
| Arc | 1 | 2 | 9 |
| Grid | 0 | 3 | 10 |
| Tile | 0 | 1 | 3 |
Вручную проверьте количество Dot для StudioKit. В нем нет прямых Dot, два входят через непосредственно включенный DotKit, еще пятнадцать — через три StripeKit, каждый из которых содержит по пять Dot. Всего получается семнадцать. Эта проверка прослеживает все пути к одному ресурсу, в том числе через общий вложенный набор.
Как убедиться, что учтены все уровни вложенности?
Правдоподобная сумма не доказывает, что раскрытие завершено. Используйте саму матрицу вложенности, чтобы проверить наличие еще одного уровня.
Введите эту формулу в G8, чтобы рассчитать матрицу вложенности за два перехода в G8:I10:
=MMULT($B$8:$D$10,$B$8:$D$10)
Затем введите эту формулу в L8, чтобы рассчитать матрицу вложенности за три перехода в L8:N10:
=MMULT(G8:I10,$B$8:$D$10)
Для приведенных данных в G8:I10 будет одна ненулевая запись: шесть DotKit, достигаемых через StripeKit внутри StudioKit. Все ячейки L8:N10 будут равны нулю. Пути из трех переходов нет, поэтому вкладов сверх двух уже учтенных уровней вложенности быть не может.
Это правило остановки следует из матричной модели. При неотрицательных количествах полностью нулевая следующая матрица вложенности означает отсутствие любых более длинных путей вложенности. Это более надежная проверка, чем выяснение, получает ли дополнительный вклад какой-либо отдельный ресурс.
В большой книге с k разными наборами и без циклов вложенности самая длинная возможная цепочка содержит не более k−1 переходов. Продолжайте умножать каждую матрицу вклада ресурсов на матрицу вложенности и складывать результаты, пока следующая степень матрицы вложенности не станет полностью нулевой. Приведенная формула из трех слагаемых полностью учитывает этот пример; если дополнительные наборы создают более длинные цепочки, ее нужно расширить.
Если расположить наборы от самых простых к самым вложенным, их будет легче проверять. В приведенной матрице вложенности все ненулевые записи находятся выше главной диагонали. Положительное значение на диагонали означает прямое включение набора в себя. Если более длинная цепочка возвращается к исходному набору, исправьте этот рецепт, прежде чем использовать конечное разложение.
Как найти количество одного ресурса в одном наборе?
Укажите W1 как «Ресурс», X1 как «Набор» и Y1 как «Количество». Введите Arc в W2, а StudioKit — в X2. В Y2 введите:
=INDEX($Q$2:$S$5,MATCH(W2,$P$2:$P$5,0),MATCH(X2,$Q$1:$S$1,0))
Результат должен быть 9. Первый MATCH находит строку ресурса, второй — столбец набора. INDEX возвращает значение на их пересечении. По документации Microsoft, MATCH с 0 запрашивает точное совпадение без необходимости сортировать метки. Функция INDEX, Функция MATCH.
Метки в каждой оси должны быть уникальными, поскольку точный MATCH возвращает первую найденную позицию. Отсутствующая метка приводит к ошибке #N/A. Оставьте эту ошибку видимой, пока проверяете названия, вместо того чтобы заменять ее нулем: ноль уже имеет конкретный смысл в матрице потребностей. Функция MATCH.
Как рассчитать партию из нескольких наборов?
Введите DotKit, StripeKit и StudioKit в Q7:S7. Введите 2, 1 и 3 в Q8:S8. Это независимо запрошенные количества: два DotKit, один StripeKit и три StudioKit.
Поместите «Общее количество экземпляров» в U1, затем введите эту формулу в U2:
=MMULT($Q$2:$S$5,TRANSPOSE($Q$8:$S$8))
TRANSPOSE преобразует горизонтальную строку потребностей для трех наборов в вертикальный столбец. Умножение дает общее количество для каждого базового ресурса и заполняет U2:U5. По документации Microsoft, TRANSPOSE меняет строки и столбцы местами. Функция TRANSPOSE.
| Базовый ресурс | Расчет | Всего экземпляров |
|---|---|---|
| Dot | 2×2 + 5×1 + 17×3 |
60 |
| Arc | 1×2 + 2×1 + 9×3 |
31 |
| Grid | 0×2 + 3×1 + 10×3 |
33 |
| Tile | 0×2 + 1×1 + 3×3 |
10 |
Не добавляйте внутренне необходимые DotKit и StripeKit в строку потребностей для партии. Их содержимое уже включено в полную матрицу. Дополнительные записи в потребностях указывают только на отдельно запрошенные наборы.
Что проверить, если результат выглядит неверным?
Перед изменением формул выполните следующие проверки:
- В массиве отображается
#SPILL!: проверьте целевой диапазон вывода на наличие заполненных ячеек или объединенных ячеек. Удалите препятствие или переместите формулу. По документации Microsoft, оба условия блокируют разлив. Как исправить ошибку #SPILL!. - При умножении отображается
#VALUE!: проверьте входные данные на пустые ячейки, текст, включенные заголовки и несовместимые размерности. Функция MMULT. - Итоги слишком высокие: проверьте, не содержат ли прямые матрицы уже раскрытые данные вложенных наборов и не добавлены ли внутренние наборы в потребности для партии.
- Итоги слишком низкие: проверьте следующую степень матрицы вложенности. Ненулевой результат может означать, что нужно учесть еще один уровень.
- Числа выглядят правдоподобно, но стоят не под теми заголовками: сравните порядок наборов в обеих входных матрицах и заголовках результата.
При расширении книги обновите все затронутые входные диапазоны, вспомогательные вычисления, заголовки вывода, диапазоны поиска и диапазоны потребностей. В формулах используются фиксированные адреса; ввод нового набора рядом с существующими входными данными не включит его автоматически.
Оставьте прямые рецепты в качестве редактируемых входных данных, держите рядом с расчетом проверку глубины вложенности и после каждого структурного изменения выборочно проверяйте полный путь одного ресурса. Эти проверки позволяют использовать готовую матрицу как для поиска отдельных наборов, так и для расчета итогов партии на основе одних и тех же записанных рецептов.
Источники
- Функция MMULT — служба поддержки Microsoft.
- Что нового в Excel 2021 для Mac — служба поддержки Microsoft.
- Формулы динамических массивов и поведение разлива — служба поддержки Microsoft.
- Функция INDEX — служба поддержки Microsoft.
- Функция MATCH — служба поддержки Microsoft.
- Функция TRANSPOSE — служба поддержки Microsoft.
- Как исправить ошибку #SPILL! — служба поддержки Microsoft.