Используйте Sumproduct в Excel, чтобы найти результат совпадения в строке
У меня есть таблица с данными, как показано ниже. код «aaa» имеет выделенную для него стоимость в размере 200 долларов США и
коду «bbb» присвоена стоимость в размере 500 долларов США.
На другом листе я хотел бы найти стоимость на основе кода. Для кода ccc я могу использовать Sumproduct, чтобы найти стоимость = 500. Но для кода aaa и bbb, какую функцию Excel я могу использовать, чтобы найти стоимость?
Column A | Column B
Code Cost
ccc 600
aaa 200
bbb 500
Я попробовал Sumproduct, чтобы найти стоимость кода ccc. Но для кода bbb и ccc мне не удалось найти способа добиться того же результата.
Потому что данные сохраняются из формы пользовательского ввода. Одна строка = 1 уникальный номер счета, но в этом счете общая сумма счета разбивается на разные коды расходов. Поэтому, когда я сохраняю этот счет, я могу сделать только это. Например: общая сумма счета составляет 700 долларов США, из которых 200 долларов США для расходов с кодом aaa и 500 долларов США для расходов с кодом bbb.
Если один счет-фактура будет относиться только к одному коду расходов, это вообще не будет проблемой. Но в этом случае может ли кто-нибудь посоветовать, есть ли лучший способ структурировать данные?
Я добавил ответ ниже, чтобы показать вам, как это сделать.
Ответы 3
Вот один из способов сделать это, однако на самом деле не рекомендуется и не предлагается создавать нежелательные макеты, что может привести к созданию сложных формул, как уже упоминалось в комментариях выше.
Тем не менее, если у вас уже есть то же самое, вы можете попробовать использовать следующее, создав собственную функцию LAMBDA() и используя ее с функцией XLOOKUP(). Также предполагается, что максимальное количество разделителей в строке будет равно 2.*
=LET(
_Fx, LAMBDA(α, TOCOL(TEXTSPLIT(TEXTAFTER(";"&α,";",{1,2}),";"),2)),
--XLOOKUP(D2:D4,_Fx(Data[Code]),_Fx(Data[Cost Allocation]),""))
Или, если кодов несколько, используйте функцию SUM():
=LET(
_Fx, LAMBDA(α, TOCOL(TEXTSPLIT(TEXTAFTER(";"&α,";",{1,2}),";"),2)),
--SUM((D2=_Fx(Data[Code]))*_Fx(Data[Cost Allocation])))
Обновленная формула:
Если количество разделителей больше 2, используйте следующую формулу:
=LET(
_Fx, LAMBDA(α, TOCOL(TEXTSPLIT(TEXTAFTER(";"&α,";",SEQUENCE(,MAX(LEN(α)-LEN(SUBSTITUTE(α,";",))+1))),";"),2)),
--SUM((D2=_Fx(Data[Code]))*_Fx(Data[Cost Allocation])))
Таким образом, максимальное количество разделителей в каждой строке равно 2? В реальном случае их может быть больше 2. Есть ли способ исправить эту формулу, чтобы она работала с разными разделителями подряд? спасибо
@RyanNguyen, посмотри обновленную формулу!
@RyanNguyen, ты попробовал, и результат вернулся NA. Можете ли вы показать мне, как? Потому что решение основано на контексте вашего ОП и должно работать, если разделители также больше 2!
это была просто моя ошибка при вставке. ваш обновленный код работает как чемпион! очень признателен! Я собирался отказаться от формулы Excel, потому что не мог найти способ преодолеть это без vba и попытаться создать для нее собственную функцию vba, но она отлично работает.
@RyanNguyen нет ничего сложного, если ты научишься это делать. Спасибо!
не могли бы вы показать мне, где я могу научиться создавать лямбда-функцию? Это новое для меня
Конечно здесь Нажмите
Этот код преобразует существующий список в обычный список из двух столбцов:
=DROP(REDUCE("",BYROW(FILTER(A:B,A:A<>""),LAMBDA(rw,TEXTJOIN("|",TRUE,rw))),LAMBDA(arr,val,VSTACK(arr,TRANSPOSE(TEXTSPLIT(val,";","|"))))),1)
Затем вы можете передать это в VLOOKUP:
=VLOOKUP(D1, DROP(REDUCE("",BYROW(FILTER(A:B,A:A<>""),LAMBDA(rw,TEXTJOIN("|",TRUE,rw))),LAMBDA(arr,val,VSTACK(arr,TRANSPOSE(TEXTSPLIT(val,";","|"))))),1), 2, FALSE)
Как это работает: во-первых, мы используем ByRow для преобразования каждой строки исходной таблицы в одно значение с разделителем Pipe:
BYROW(FILTER(A:B,A:A<>""),LAMBDA(rw,TEXTJOIN("|",TRUE,rw)))
Так:
Становится:
Затем мы используем REDUCE, чтобы взять каждое из этих значений, превратить его в массив и сложить массивы друг на друга:
REDUCE("",[LIST],LAMBDA(arr,val,VSTACK(arr,TRANSPOSE(TEXTSPLIT(val,";","|")))))
(N.B. the TRANSPOSE is required because the TEXTSPLIT function can handle Columns within Rows, but not Rows within Columns; we have to build the Array sideways, and then flip it)
Так, например, Code|Cost Allocation становится
и aaa;bbb|200;500 становится
Что (когда все сложено вместе) дает результат
(Тогда Drop(.., 1) просто избавляется от пустой строки вверху, так что «Код» и «Распределение затрат» снова становятся заголовками столбцов)
Чтобы решить основную проблему формата данных, который не идеален для отчетов, вы можете очистить таблицу с помощью Power Query. Это будет более надежно и менее непонятно, чем решение по формуле.
Зеленая нормализованная таблица на снимке экрана ниже была создана исключительно с помощью команд на ленте. Знание специального кода не требуется.
Чтобы получить результат, необходимо использовать два запроса:
Первый запрос — вспомогательная таблица
- загрузите синюю таблицу в редактор Power Query
- удалить столбец распределения затрат
- разделите столбец кода разделителем
;(установитеcolumns to split intoна максимальное количество значений, которое вы ожидаете в одной ячейке, чтобы его можно было адаптировать к изменяющимся наборам данных) - выберите столбец счета и отмените поворот всех остальных столбцов
- добавить индексный столбец
- переименуйте столбец «Значение» в «Код» и удалите столбец «Атрибут».
- сохраните и загрузите этот запрос только как соединение
Второй запрос – нормализованная таблица
- снова загрузите синюю таблицу в редактор Power Query
- удалить столбец кода
- разделите столбец распределения затрат разделителем
;(установитеcolumns to split intoна тот же номер, который вы использовали выше) - выберите столбец счета и отмените поворот всех остальных столбцов
- добавить индексный столбец
- переименуйте столбец «Значение» в «Распределение затрат» и удалите столбец «Атрибут».
- объединить запрос кода с индексным столбцом в качестве ключа в обеих таблицах
- Разверните таблицу, чтобы сохранить только столбец кода.
- удалите ненужные столбцы и переименуйте то, что требует переименования
- сохранить запрос на листе
Теперь вы можете использовать простые функции SumIfs() или сводные таблицы для анализа данных.
Если вы получили новый пакет данных, просто скопируйте/вставьте его в синюю таблицу и обновите Power Query.
Вместо того, чтобы пытаться создать сложную формулу, которая ищет значение в плохо структурированной входной таблице, рассмотрите возможность решения проблемы с плохо структурированной входной таблицей. Почему в одной строке таблицы есть два значения для кода и распределения?