Формула Excel превышает ограничение в 8000 символов

У меня есть формула, состоящая из 8433 символов, и кажется, что предел для Excel составляет 8192. Это длинное выражение «если» с более чем 70 условиями. Есть ли более эффективный способ оценки критериев, чтобы получить меньше символов в приведенной ниже формуле?

=IF(ISBLANK(AD3),"",IF(
SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Buy",Transactions!$F$3:$F,"Stock",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"DRIP",Transactions!$F$3:$F,"Stock",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Transfer Deposit",Transactions!$F$3:$F,"Stock",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Buy",Transactions!$F$3:$F,"ETF",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"DRIP",Transactions!$F$3:$F,"ETF",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Transfer Deposit",Transactions!$F$3:$F,"ETF",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Buy",Transactions!$F$3:$F,"Commodity",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"DRIP",Transactions!$F$3:$F,"Commodity",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Transfer Deposit",Transactions!$F$3:$F,"Commodity",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Buy",Transactions!$F$3:$F,"Managed Fund",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"DRIP",Transactions!$F$3:$F,"Managed Fund",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Transfer Deposit",Transactions!$F$3:$F,"Managed Fund",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Buy",Transactions!$F$3:$F,"REIT",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"DRIP",Transactions!$F$3:$F,"REIT",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Transfer Deposit",Transactions!$F$3:$F,"REIT",Transactions!$D$3:$D,AD3)

-SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Sell",Transactions!$F$3:$F,"Stock",Transactions!$D$3:$D,AD3)
-SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Transfer Send",Transactions!$F$3:$F,"Stock",Transactions!$D$3:$D,AD3)
-SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Sell",Transactions!$F$3:$F,"ETF",Transactions!$D$3:$D,AD3)
-SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Transfer Send",Transactions!$F$3:$F,"ETF",Transactions!$D$3:$D,AD3)
-SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Sell",Transactions!$F$3:$F,"Commodity",Transactions!$D$3:$D,AD3)
-SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Transfer Send",Transactions!$F$3:$F,"Commodity",Transactions!$D$3:$D,AD3)
-SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Sell",Transactions!$F$3:$F,"Managed Fund",Transactions!$D$3:$D,AD3)
-SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Transfer Send",Transactions!$F$3:$F,"Managed Fund",Transactions!$D$3:$D,AD3)
-SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Sell",Transactions!$F$3:$F,"REIT",Transactions!$D$3:$D,AD3)
-SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Transfer Send",Transactions!$F$3:$F,"REIT",Transactions!$D$3:$D,AD3)

+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Buy",Transactions!$F$3:$F,"Crypto",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Crypto Interest",Transactions!$F$3:$F,"Crypto",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Transfer Deposit",Transactions!$F$3:$F,"Crypto",Transactions!$D$3:$D,AD3)
-SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Transfer Send",Transactions!$F$3:$F,"Crypto",Transactions!$D$3:$D,AD3)
-SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Sell",Transactions!$F$3:$F,"Crypto",Transactions!$D$3:$D,AD3)

+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Buy",Transactions!$F$3:$F,"Stablecoin",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Crypto Interest",Transactions!$F$3:$F,"Stablecoin",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Transfer Deposit",Transactions!$F$3:$F,"Stablecoin",Transactions!$D$3:$D,AD3)
-SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Transfer Send",Transactions!$F$3:$F,"Stablecoin",Transactions!$D$3:$D,AD3)
-SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Sell",Transactions!$F$3:$F,"Stablecoin",Transactions!$D$3:$D,AD3)
-SUMIFS(Transactions!$M$3:$M,Transactions!$H$3:$H,"Buy",Transactions!$B$3:$B,AD3)=0,"Sold All",

SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Buy",Transactions!$F$3:$F,"Stock",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"DRIP",Transactions!$F$3:$F,"Stock",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Transfer Deposit",Transactions!$F$3:$F,"Stock",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Buy",Transactions!$F$3:$F,"ETF",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"DRIP",Transactions!$F$3:$F,"ETF",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Transfer Deposit",Transactions!$F$3:$F,"ETF",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Buy",Transactions!$F$3:$F,"Commodity",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"DRIP",Transactions!$F$3:$F,"Commodity",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Transfer Deposit",Transactions!$F$3:$F,"Commodity",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Buy",Transactions!$F$3:$F,"Managed Fund",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"DRIP",Transactions!$F$3:$F,"Managed Fund",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Transfer Deposit",Transactions!$F$3:$F,"Managed Fund",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Buy",Transactions!$F$3:$F,"REIT",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"DRIP",Transactions!$F$3:$F,"REIT",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Transfer Deposit",Transactions!$F$3:$F,"REIT",Transactions!$D$3:$D,AD3)

-SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Sell",Transactions!$F$3:$F,"Stock",Transactions!$D$3:$D,AD3)
-SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Transfer Send",Transactions!$F$3:$F,"Stock",Transactions!$D$3:$D,AD3)
-SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Sell",Transactions!$F$3:$F,"ETF",Transactions!$D$3:$D,AD3)
-SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Transfer Send",Transactions!$F$3:$F,"ETF",Transactions!$D$3:$D,AD3)
-SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Sell",Transactions!$F$3:$F,"Commodity",Transactions!$D$3:$D,AD3)
-SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Transfer Send",Transactions!$F$3:$F,"Commodity",Transactions!$D$3:$D,AD3)
-SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Sell",Transactions!$F$3:$F,"Managed Fund",Transactions!$D$3:$D,AD3)
-SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Transfer Send",Transactions!$F$3:$F,"Managed Fund",Transactions!$D$3:$D,AD3)
-SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Sell",Transactions!$F$3:$F,"REIT",Transactions!$D$3:$D,AD3)
-SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Transfer Send",Transactions!$F$3:$F,"REIT",Transactions!$D$3:$D,AD3)

+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Buy",Transactions!$F$3:$F,"Crypto",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Crypto Interest",Transactions!$F$3:$F,"Crypto",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Transfer Deposit",Transactions!$F$3:$F,"Crypto",Transactions!$D$3:$D,AD3)
-SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Transfer Send",Transactions!$F$3:$F,"Crypto",Transactions!$D$3:$D,AD3)
-SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Sell",Transactions!$F$3:$F,"Crypto",Transactions!$D$3:$D,AD3)

+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Buy",Transactions!$F$3:$F,"Stablecoin",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Crypto Interest",Transactions!$F$3:$F,"Stablecoin",Transactions!$D$3:$D,AD3)
+SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Transfer Deposit",Transactions!$F$3:$F,"Stablecoin",Transactions!$D$3:$D,AD3)
-SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Transfer Send",Transactions!$F$3:$F,"Stablecoin",Transactions!$D$3:$D,AD3)
-SUMIFS(Transactions!$I$3:$I,Transactions!$H$3:$H,"Sell",Transactions!$F$3:$F,"Stablecoin",Transactions!$D$3:$D,AD3)
-SUMIFS(Transactions!$M$3:$M,Transactions!$H$3:$H,"Buy",Transactions!$B$3:$B,AD3)))

Я попытался сохранить книгу в двоичном формате книги Excel, но макросы не работают

Если формула такая длинная, вероятно, это неправильная формула. Можете ли вы сказать нам, что он должен делать? Возможно также предоставить образцы данных и ожидаемый результат

cybernetic.nomad 20.02.2023 20:41

Это не эксель, а гугл, это не одно и то же

P.b 20.02.2023 20:43

В Excel вы можете реализовать LET не уверен, есть ли это на листах.

P.b 20.02.2023 20:44

Вы можете реализовать пользовательскую функцию LAMBDA для всех вызовов SUMIFS с параметром в качестве входных данных, который принимает разные значения, например: Купить, Продать и т. д..

David Leal 20.02.2023 20:50

Предполагается рассчитать оставшееся количество в ячейке C3 «Лист1» из входных данных листа «Транзакции» путем вычитания продаж и оттоков из покупок и притоков. Он отлично работает в таблицах Google, потому что gs не имеет ограничения на количество символов. Вот пример данных docs.google.com/spreadsheets/d/…

Constark 20.02.2023 21:24
Преобразование HTML-таблицы в профессиональный документ Excel
Преобразование HTML-таблицы в профессиональный документ Excel
Это самый простой способ создания Excel из HTML-таблицы.
Импорт excel в laravel в базу данных
Импорт excel в laravel в базу данных
Здравствуйте, дорогой читатель, в этой статье я расскажу практическим и быстрым способом, как импортировать файл Excel в вашу базу данных с помощью...
0
5
52
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Хотя LET() является оптимальным, он делает предположение о вашей версии Excel, которое вы не указали, может быть сделано - альтернативный подход, допустимый в устаревшем Excel, заключается в использовании именованных диапазонов для 4 разных диапазонов, дублированных (69 раз) в вашей формуле , например (при использовании показанных более коротких именованных диапазонов длина формулы была уменьшена более чем вдвое)

Более прагматичный подход может состоять в том, чтобы ознакомиться с константами массива , например. суммирование первых 15 SUMIFS() функций также может быть достигнуто с помощью этой более краткой формулы массива:

=SUM(SUMIFS(Transactions!$I$3:$I10000,Transactions!$H$3:$H10000,{"Buy";"Drip";"Transfer Deposit"},Transactions!$F$3:$F10000,{"Stock","ETF","Commodity","Managed Fund","REIT"},Transactions!$D$3:$D10000,AD3))

(В обоих случаях я сделал предположение, что последняя строка ваших данных равна 10 000 — вы должны настроить это в соответствии с вашим контекстом, поскольку в Excel следует избегать ссылок на полный столбец)

Другие вопросы по теме