Возврат суммы нескольких просмотров

Question

Итак, у меня есть проблема, которую я пытаюсь решить в одной формуле.

У меня есть настройки таблиц:

Сколько NFPI (Количество фруктов х Процент интенсивности) у меня есть для яблок?

Мне нужно отсканировать столбец G таблицы 3 по столбцу B таблицы 1, чтобы увидеть, в каких строках есть вид Apple.

Затем мне нужно найти общее количество фруктов для каждого случая Apple:

(Number of trees * Tree fruit) + ground fruit

И найдите это общее количество фруктов в таблице 2 с надлежащей интенсивностью в процентах и сложите все случаи

Так это будет выглядеть так:

(1 * 3) + 2 = 5 Red Apples. NFPI of 5 fruits at 97% intensity = 1.67  
(2 * 2) + 0 = 4 Green Apples. NFPI of 4 fruits at 98% intensity = 2  
(1 * 2) + 1 = 3 Yellow Apples. NFPI of 3 fruits at 97% intensity = 1  
**total NFPI = (1.67 + 2 + 1) = 4.67**

Я пытаюсь сделать все это в одной формуле. Формула, которую я пытался использовать, включает vlookup в формулу массива, но продолжает давать мне неправильный ответ. Вот формула, которую я пробовал:

{=SUM(IF(ISERROR(MATCH(G12:G16,B3:B5,0))=FALSE,VLOOKUP(H12:H16,F4:K8,(I12:I16*J12:J16)+K12:K16+1),0))}

Я не могу понять, почему это не работает или способ заставить это работать. Я думал, что, возможно, формула SUMPRODUCT может помочь, но я не мог понять это также. Я знаю, что могу просто найти NFPI каждой записи яблока и ввести его в другой столбец рядом с столбцом Ground Fruit а затем просто поместить формулу SUM внизу, чтобы сложить все, но я пытаюсь найти сумма, не делая этого, если это возможно.

Любая помощь будет оценена!

Answer 1

РЕДАКТИРОВАТЬ: В недавнем ответе @ScottCraner использовалась формула INDEX() отсутствующими ссылками, и его ответ заставил меня принять решение еще раз взяться за эту проблему. Тот же самый подход, который я пробовал (безуспешно), прежде чем работал отлично во второй раз Я опишу решение ниже.

Справочная информация о формуле INDEX(), на которую есть ссылки:

Шон, ты сделал замечательную попытку использовать формулы массива, чтобы сделать то, что тебе нужно. Ваши проблемы связаны с тем, как Excel обрабатывает массивы. Некоторые формулы могут использовать массивы в качестве аргументов, а некоторые нет.

Я немного покопался в этом и узнал некоторые очень странные, загадочные вещи об использовании массивов в формуле Excel INDEX() , которых раньше не знал. Чтобы понять, как работает эта формула, давайте начнем с конца.

Самое последнее, что сделает ваша формула, - это сумма трех (прерывистых) значений из двумерного массива, который является вашей таблицей 2.

INDEX(array,row_num,col_num) может возвращать одно значение из 2D-массива, а также может возвращать целый столбец или строку. Похоже, он должен иметь возможность вернуть список значений. Итак, давайте проверим это.

Эта формула (в идеальном мире) вернет сумму, которую вы ищете из таблицы 2:

=SUM(INDEX(G4:K8,{3,2,3},{5,4,3}))

Это должно добавить элементы из строки 3, столбца 5 плюс строки 2, столбца 4 плюс строки 3, столбца 3. Но это не так, он просто возвращает 1.67, на который ссылается первый элемент.

При поиске в Интернете создаются ссылки (в том числе здесь, в StackOverflow), в которых говорится, что INDEX() вернет массив, но только если вы отмените ссылку на формулу (это "странная" часть). "Тайная" часть - как это сделать. Это формула с разыменованными ссылками:

=SUM(INDEX(G4:K8,N(IF(1,{3,2,3})),N(IF(1,{5,4,3}))))

Эта формула дает правильный ответ: 4.67.

В формуле IF() обрабатывает 1 как True , поэтому возвращает массив чисел, а N() возвращает массив чисел, если они являются числами, которыми они являются. Почему IF() и N() требуются для правильной работы формулы, можно только догадываться. В формуле Скотта он должен был также умножить свой массив (это был диапазон) на 1.

Но теперь у нас есть формула, которая дает правильный ответ. И, надеюсь, все, что нам нужно сделать, это заменить константы массива на вычисленные массивы, используя другие ваши данные.

Новая информация начинается здесь.

Для чисел row_num в формуле выше {3,2,3} нам нужны позиции процентных интенсивностей в F4:F8, связанных с выбранными сортами фруктов. Во-первых, мы получим массив позиций яблок в G12:G16 вашей таблицы 3:

=MATCH(B3:B5,G12:G16,0)

Это формула массива и должна вводиться с помощью клавиши CTRL Shift Enter, а не просто Enter.

Эта формула ищет список сортов яблок из таблицы 1 в столбце G таблицы 3 и возвращает массив их позиций.

Если вы выберете формулу в строке формул и нажмете F9, вы увидите, что значением формулы является массив {1,3,4} , позиции яблок в столбце G таблицы 3.

Теперь нам нужны PI, связанные с этими позициями. Эта формула INDEX() выглядит в столбце H и использует вышеуказанный массив как row_num. Здесь row_num должны быть "разыменованы":

=INDEX(H12:H16,N(IF(1,MATCH(B3:B5,G12:G16,0))))

Эта формула возвращает массив {0.97,0.98,0.97} , PI's яблок. Все идет нормально. Затем мы используем этот массив в качестве значений поиска в формуле MATCH() которая выглядит как F4:F8, индекс PI вашей таблицы 2:

=MATCH(INDEX(H12:H16,N(IF(1,MATCH(B3:B5,G12:G16,0)))),F4:F8,0)

Эта формула возвращает массив {3,2,3} , и это те, которые необходимы row_num для окончательной формулы.

Далее нам нужно col_num's {5,4,3} , то есть общее количество фруктов для каждого из сортов яблок. Мы получим это из таблицы 3, но сначала нам нужно рассчитать общее количество фруктов для всех сортов фруктов. Этот (рассчитанный) массив представляет собой список этих итогов:

(I12:I16*J12:J16)+K12:K16

Чтобы получить общее количество фруктов для сортов Apple, мы будем использовать этот массив в INDEX() с теми же (без ссылки) row_num, что и раньше:

=INDEX((I12:I16*J12:J16)+K12:K16,N(IF(1,MATCH(B3:B5,G12:G16,0))))

Эта формула возвращает массив {5,4,3} , и это столбец col_num, необходимый для окончательной формулы.

Собирая все это вместе, список NFPI:

=INDEX(G4:K8,MATCH(INDEX(H12:H16,N(IF(1,MATCH(B3:B5,G12:G16,0)))),F4:F8,0),INDEX((I12:I16*J12:J16)+K12:K16,N(IF(1,MATCH(B3:B5,G12:G16,0))))

Эта формула возвращает массив {1.67;2;1} . Это NFPI для яблок, и теперь нам нужно просто добавить их.

Но еще не совсем, есть небольшая проблема, о которой нужно позаботиться в первую очередь. Все три сорта яблок можно найти в таблице 3, но это не относится к апельсинам. Приведенные выше формулы возвращают массивы с #N/A в которых сорт Small Orange не может быть найден. Это не вызывает никаких проблем, пока не придет время сложить значения.

Поэтому, прежде чем взять сумму, мы конвертируем #N/A's в 0 с помощью IFERROR() . Вот последняя формула:

=SUM(IFERROR(INDEX(G4:K8,MATCH(INDEX(H12:H16,N(IF(1,MATCH(C3:C5,G12:G16,0)))),F4:F8,0),INDEX((I12:I16*J12:J16)+K12:K16,N(IF(1,MATCH(C3:C5,G12:G16,0))))),0))

Эта формула возвращает 4,67 для яблок и 5,75 для апельсинов.

Шон, надеюсь, это все еще может быть полезным. Извините за долгую задержку.

Answer 2

РЕДАКТИРОВАТЬ Смотрите мой другой ответ на этой странице. Оказывается, что INDEX() МОЖЕТ вернуть список (прерывистых) значений из массива.

Вот еще один способ выполнить то, что вы пытаетесь сделать, хотя формула оказывается очень длинной.

Следующая формула ищет каждый из трех NFPI в вашей Таблице 2 и складывает их вместе.

=INDEX(G$4:K$8,MATCH(INDEX(H$12:H$16,MATCH(B3,G$12:G$16,0)),F$4:F$8,0),MATCH(INDEX(L$12:L$16,MATCH(B3,G$12:G$16,0)),G$3:K$3,0))+INDEX(G$4:K$8,MATCH(INDEX(H$12:H$16,MATCH(B4,G$12:G$16,0)),F$4:F$8,0),MATCH(INDEX(L$12:L$16,MATCH(B4,G$12:G$16,0)),G$3:K$3,0))+INDEX(G$4:K$8,MATCH(INDEX(H$12:H$16,MATCH(B5,G$12:G$16,0)),F$4:F$8,0),MATCH(INDEX(L$12:L$16,MATCH(B5,G$12:G$16,0)),G$3:K$3,0))

Как это работает: Каждый член в сумме является функцией INDEX() которая возвращает элемент из таблицы 2, указав row_num и column_num. Для первого члена row_num сначала определяется с помощью INDEX() который ищет B3 (Red Apple) в таблице 3 и возвращает соответствующий% Intensity:

INDEX(H$12:H$16,MATCH(B3,G$12:G$16,0))

Затем этот PI используется в MATCH() для возврата правильной строки таблицы 2:

MATCH(INDEX(H$12:H$16,MATCH(B3,G$12:G$16,0)),F$4:F$8,0)

Столбец_num определяется путем нахождения правильного количества фруктов. Я добавил дополнительный столбец в вашу Таблицу 3, в котором подсчитано общее количество фруктов в L12:L16 . Если это не вариант, вы можете рассчитать количество фруктов "на лету", заменив L$12:L$16 на (I$12:I$16)*(J$12:J$16)+(K$12:K$16):

INDEX(L$12:L$16,MATCH(B3,G$12:G$16,0))

Что касается row_num, это используется в MATCH() для возврата правильной строки таблицы 2:

MATCH(INDEX(L$12:L$16,MATCH(B3,G$12:G$16,0)),G$3:K$3,0)

Теперь, когда строка и столбец известны для Red Apple, первый термин в сумме:

=INDEX(G$4:K$8,MATCH(INDEX(H$12:H$16,MATCH(B3,G$12:G$16,0)),F$4:F$8,0),MATCH(INDEX(L$12:L$16,MATCH(B3,G$12:G$16,0)),G$3:K$3,0))

Оставшиеся два термина для «Зеленого яблока» и «Желтого яблока» являются одной и той же формулой, но с B4 и B5 заменяющими B3 .

Я надеюсь, что это помогает и удачи.

Answer 3

На основании информации, предоставленной OP и образцом формулы, я нашел решение для извлечения названия фруктов, общего количества фруктов и NFPI.

Формула в клетке A12 для извлечения фруктов Тип:

=IFERROR(VLOOKUP(A2,$D$2:$H$6,1,0),"")

Формула в D12 для создания таблицы яблок:

{=VLOOKUP(A2,$D$2:$H$6,{1,2,3,4,5},FALSE)}

NB. Завершите формулу нажатием Enter, затем перетащите формулу до столбца H и нажмите F2, затем завершите эту формулу с помощью клавиш Clrt+Shift+Enter, а затем перетащите ее вниз. Вы получаете таблицу яблок с другими ценностями.

Формула в ячейке B12, чтобы найти общую ценность фруктов (как ОП предложила образец формулы):

`=IF(VLOOKUP(A2,$D$2:$H$6,1,0)=$D12,((F12*G12)+H12),0)`

Для NFPI формула в ячейке C12:

=(E12*IF(VLOOKUP(A2,$D$2:$H$6,1,0)=$D12,((F12*G12)+H12),0))

NB: Это предварительная формула, которую я создал на основе информации, которую нашел в OP. (Если вы покажете мне, как вы нашли 1,67, тогда я просто изменю уравнение).

Я верю, что это поможет вам.