SUM – список остатков на счетах, за исключением определенного диапазона учетных записей

Как я могу суммировать большое количество счетов GL (4000 строк около 100 уникальных учетных записей), исключая определенный диапазон учетных записей (около 40 уникальных учетных записей).

Я хочу иметь возможность сделать это, не вручную выписывая «<> XXX» 40 раз для каждой учетной записи. В идеале у меня есть таблица учетных записей, которые я не хочу включать в сумму, на которую ссылаются.

Снимок экрана, который я хочу суммировать в столбце H, исходя из счетов в столбце F, за исключением учетных записей в столбце K.

введите описание изображения здесь

введите описание изображения здесь

Используйте эту формулу как формулу массива. Нажмите CTRL + SHIFT + ENTER после ввода формулы.

=SUM(IF(A2:A25=H13,IF(ISERROR(MATCH(B2:B25,F2:F5,0)),C2:C25,0),0)) Column A - Your profit centers Column B - Your accounts Column C - Amounts to sum Column F - Accounts you want to exclude Cell H13 - Profit center that you want to sum up 

Примечание. После каждого редактирования формулы нажмите CTRL + SHIFT + ENTER .

Вы можете использовать массив, данные в A: B и исключения в e1: e3

=SUM((ISERROR(MATCH($A$1:$A$4000,$E$1:$E$3,0)))*($B$1:$B$4000))

Это даст вам общую сумму – исключение, и это будет для определенных счетов в столбце J

=SUM(($A$1:$A$4000=$J4)*(ISERROR(MATCH($A$1:$A$4000,$E$1:$E$3,0)))*($B$1:$B$4000))

сначала напишите в J2 :

 =iferror(vlookup(F2,K:K,1,false),1) 

Затем скопируйте эту формулу до конца таблицы.

После этого в ячейке, где вы хотите суммировать,

 =sumif(J:J,"1",H:H) 

Надеюсь, что это поможет вам

Рассмотрим совокупный SQL-запрос, который можно запустить в Excel с помощью Jet / ACE SQL Engine (файлы Windows .dll), если вы используете Excel для ПК. С SQL вы можете фильтровать в соответствии с Account number и группой с помощью Profit Center . Никаких массивов или петель не требуется.

Для ваших нужд вы можете выбрать регионы внутри рабочего листа, которые будут использоваться в качестве таблиц. Обязательно добавьте заголовки столбцов и ссылку в инструкции SQL соответственно. Основные столбцы будут основной таблицей и не будут содержать в качестве таблицы подзапросов. Ниже настройки запрашивает данные на листе с именем DATA и выводит результаты в пустой лист с именем EMPTY .

SQL (встроенный ниже в VBA как строка)

 SELECT d.[Profit Center], Sum(d.Amount) As [Sum Amount] FROM [DATA$A:D] d WHERE NOT EXISTS (SELECT 1 FROM [DATA$I:J] sub WHERE sub.[DNI_Account number] = d.[Account number]) GROUP BY d.[Profit Center] 

VBA

 Public Sub RunSQL() Dim conn As Object, rst As Object, fld As Object Dim strConnection As String, strSQL As String Dim i As Integer: i = 0 Set conn = CreateObject("ADODB.Connection") Set rst = CreateObject("ADODB.Recordset") strConnection = "DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" _ & "DBQ=C:\Path\To\Workbook.xlsm;" strSQL = " SELECT d.[Profit Center], Sum(d.Amount) As [Sum Amount]" _ & " FROM [DATA$A:D] d " _ & " WHERE NOT EXISTS (SELECT 1 FROM [DATA$G:H] sub" _ & " WHERE sub.[Account number] = d.[Account number])" _ & " GROUP BY d.[Profit Center]" ' OPEN DB CONNECTION AND RECORDSET conn.Open strConnection rst.Open strSQL, conn Worksheets("RESULTS").Activate Worksheets("RESULTS").Range("A1").Activate ' COLUMN HEADERS For Each fld In rst.Fields ActiveCell.Offset(0, i) = fld.Name i = i + 1 Next fld ' DATA ROWS Worksheets("RESULTS").Range("A2").CopyFromRecordset rst rst.Close conn.Close End Sub 
  • sumif считает «3.1» и «3.10» равным по критериям
  • Excel Sum Если на основе диапазона дат даты?
  • Суммировать только согласованные значения из двух столбцов
  • Vlookup и Sumif?
  • Использование месячной квалификации в статусе SUMIFS
  • Вычислять один столбец на основе значений в другом?
  • Найти критерии в нескольких столбцах и суммировать смещение каждого согласованного значения
  • Можете ли вы расширить формулу sumifs, зависящую от оператора if?
  • Функция SUMIF не работает DAX лучше?
  • Сумма варьируется до и после определенного цвета или значения ячейки?
  • Использование SUMIFS в Excel-vba для проверки нескольких условий
  • Давайте будем гением компьютера.