Я пытаюсь обновить таблицу предложений/цен для внутренней оценки стоимости работ. Наши руководители проектов оценивают часы по сотрудникам по месяцам, а затем к этой оценке применяется ставка, чтобы получить общую цену. Не каждая работа будет использовать каждого сотрудника.
| Сотрудник/часы | Янв/21 | 21 февр. | 21 марта |
|---|---|---|---|
| Джон Смит | 10 | 15 | 20 |
| Джейн Доу | 20 | 20 | 10 |
Таблица ставок находится на другой вкладке и перечисляет всех сотрудников в столбце A, а их почасовую ставку в месяц в столбцах B и далее, с теми же заголовками дат, что и оценка часов. Таблица ставок может также включать месяцы раньше или позже месяцев на вкладке часов.
| Таблица цен | Дек/21 | Янв/21 | 21 февр. | 21 марта |
|---|---|---|---|---|
| Джон Смит | 12 долларов | 12 долларов | 12 долларов | 13 долларов |
| Джим Доу | 13 долларов | 13 долларов | 13 долларов | 13 долларов |
| Джейн Доу | 12 долларов | 12 долларов | 13 долларов | 13 долларов |
Раньше у меня была таблица часов в верхней части вкладки оценки часов, а затем еще одна таблица прямо под ней, которая использует INDEX MATCH MATCH для поиска соответствующей ставки по сотрудникам по месяцам, а затем умножает ее на часы для этого сотрудника выше. Это работает, но тогда это означает, что мне нужно показать общую стоимость для каждого сотрудника по месяцам, что мне на самом деле не нужно. Я бы хотел просто иметь строку под каждым столбцом месяца с общей стоимостью за этот месяц, избегая при этом написания формулы много раз
=(B2 * INDEXMATCHMATCH)+(B3 * INDEXMATCHMATCH)+(B4 * INDEXMATCHMATCH)
[очевидно, что это не настоящая формула, потому что она была бы очень длинной].
Есть ли способ умножить часы в столбце B на соответствующую ставку для этих сотрудников за этот месяц и суммировать их в одной ячейке?
| Сотрудник/часы | Янв/21 | 21 февр. | 21 марта |
|---|---|---|---|
| Джон Смит | 10 | 15 | 20 |
| Джейн Доу | 20 | 20 | 10 |
| Общая стоимость | 360 долларов | 380 долларов | 390 долларов |
решение1
Вот способ, который, я думаю, вы захотите попробовать:
=SUM(XLOOKUP($G$2:$G3,$A$2:$A$4,INDEX($B$2:$E$4,,MATCH(H$1,$B$1:$E$1,0)))*H$2:H$3)
(Я нашел несколько способов, некоторые из них интересные, но они длиннее в плане написания, чем эта формула, и вы, похоже, чувствуете себя комфортно с этим INDEX/MATCHподходом, поэтому вам, вероятно, будет легко его использовать и поддерживать.)
Итак, для формулы, как она написана, я скопировал ваши таблицы данных в A1:E4 для "Таблицы ставок" и G1:J3 для таблицы "Сотрудник/Часы". Адресация в формуле работает для этих диапазонов. Вам нужно будет скорректировать ее, чтобы она соответствовала вашей конкретной ситуации.
Дочитайте до конца: Не беспокойтесь, что я глупо говорю о выходной таблице.(Я только тогда, перечитывая вопрос и ответ, заметил, что вы хотели бы видеть результаты под строками данных таблицы «Сотрудник/Часы».) Формула выше работает для этого желания, поскольку я исправил ссылки, которые сделал при создании формальной выходной таблицы, так что вы можете просто вставить их по мере необходимости.)
Для построения выходной таблицы я просто использовал формулы, например, =H1для заполнения левого и верхнего заголовков выходных таблиц (имена и месяцы). Они, очевидно, вам известны, и поэтому их легко настроить по мере необходимости для любого расчета, который вы настроите. Аналогично я использовал стандартные =SUM(M2#)формулы суммирования, которые имеют то преимущество, что не требуют настройки, поскольку они ссылаются на все массивы SPILL, которые создают формулы в первой строке.
Настоящая трудность всех подходов, которые я пробовал, заключалась в том, что я в конечном итоге получал формулу, котораяДВАSPILL-массивы в своем конечном выводе, а Excel НЕ вознаграждает такие вещи. Поскольку у них были разные применения в каждом, я не мог изящно свести их в один массив SPILL, который позволил бы вам иметь одну формулу в верхней левой ячейке блока ячеек, составляющих область затрат выходной таблицы. После того, как я решил делать SPILL-массивы вниз, а не поперек, я был готов к "мясу" проблемы, этим ежемесячным расходам на одного сотрудника.
Для «мяса», как можно было бы сказать, стоимости в долларах в месяц для каждого сотрудника на любой конкретной работе, вы используете формулу.
Сначала INDEX/MATCH: (первый столбец месяца на данный момент) он сопоставляет месяцы выходной таблицы со столбцами "Таблица ставок" и обнаруживает, что необходим столбец 2 долларовой области. ИНДЕКСИРОВАНИЕ долларовой области дает выходной массив размером один столбец на три строки, что соответствует внешнему XLOOKUP()массиву поиска размером один столбец на три строки. Если бы они не совпадали таким образом, вы бы получили ошибку. Поэтому затем ищет XLOOKUP()имя сотрудника, для чего вы используете массив ( $L2#в данном случае), чтобы получить выходные данные для всех сотрудников, перечисленных в выходной таблице. Они SPILL вниз, как и требуется.
Затем скопируйте формулу в каждый столбец выходной таблицы, и ваши расходы на всех сотрудников за каждый месяц заполнятся, а суммы обновятся.
(Кстати, сумма выходных данных вашего примера для «21 февраля» неверна, поэтому формула выдает 440 долларов (15*12=180 долларов, 20*13=260 долларов, 180+260=440 долларов вместо 380 долларов.)
Итак, чтобы внести исправление, мне просто нужно было выбрать первую формулу в выходной таблице, нажать F2-Edit, выделить и скопировать формулу, затем перейти к таблице "Сотрудник/Часы", первой пустой строке после данных о часах, и ввести "=SUM(", вставить скопированную мной формулу, ввести ")" и нажать Enter. После корректировки ссылок для соответствия данным в таблице "Сотрудник/Часы" ячейка имела сумму информации, рассчитанной для ненужной выходной таблицы. Копирование ячейки и вставка справа дали желаемый результат.
Последняя мысль: это не помогло использовать один массив SPILL по всем ячейкам общей стоимости. Грустно говорить. Вам все равно придется копировать и вставлять, сколько бы столбцов ни использовалось.